Học tập - sống và làm việc!
Đào tạo Excel tư cơ bản đến nâng cao
Đào tạo VBA từ cơ bản đến nâng cao
Thiết kế file, phần mềm theo đặc thù doanh nghiệp
Phần mềm kế toán, quản lý kho,...
ColumnName: Tên cột mà các giá trị của nó làm tiêu thức, điều kiện để tính
Func: Tên hàm dùng để tính (SUM, COUNT, MIN, MAX, AVG)
ColumnCalc:Tên cột được tính giá trị theo hàm khai báo ở Func,
cột này phải là kiểu số.
FormatDate: Là định dạng ngày. Có thể bỏ qua tham số này,
nếu kiểu của ColumnName không phải là kiểu Date, hoặc là kiểu Date nhưng dữ liệu đang ở dạng mm/dd/yy. Nếu ColumnName là kiểu Date mà dữ liệu nhập không phải ở dạng mm/dd/yy thì FormatDate phải là mm/dd/yy
2. Lý do lỗi:
+ Cột ChungKhoan có giá trị mà số ký tự lớn hơn 64 ký tự.
+ Nguyên lý hàm VTC chỉ liệt kê các giá trị trong mà số ký tự <-64.
+ Như vậy công thức như dưới đây là đùng nhưng vì lỗi về số ký tự.
=bs_Sql("Select VTC(ChungKhoan,Count,STT) From DauTu")
3. Cách khắc phục:
Cách 1: xác định các dòng giá trị của cột ChungKhoan, những giá trị nào > 64 ký tự phải thu gọn lại đảm bảo <= 64
Cách 2: dùng hàm LEFT để cắt chuỗi, cách này là tự động nhưng ký tự cắt đôi khi không theo ý người sử dụng:
=bs_Sql("Select VTC(left(ChungKhoan,64),Count,STT) From DauTu")
Bluesofts xin chia sẻ một file Excel Add-In thiết kế bởi Nguyễn Duy Tuân - Công ty cổ phần Bluesofts
"Bluesofts Macbook ControlDelete is Delete in Windows" là một Add-in cho Excel, cho phép Microsoft Excel chạy trên máy MacBook xóa một vùng chọn bằng phím Control+Delete để như phím Delete ở các máy tính chạy hệ điều hành Windows.
(*) Cài đặt Add-in trong Excel2007, 2010, 2013 hoặc cao hơn:
+ Bấm vào nút "Office Button/File" (nút to, tròn ở góc đỉnh bên trái màn hình)
+ Chọn "Excel Options"
+ Chọn "Add-Ins", màn hình phía bên phải, dưới đáy "Manage:" chọn "Excel Add-Ins" cuối cùng chọn "Go".
+ Tại màn hình "Add-Ins" chọn "Browse" và chọn file "Bluesofts_MacBook_ControlDeleteIsDeleteInWindows.xla".
(*) Cài đặt Add-in trong Excel97/2000/2002(XP)/2003:
+ Vào menu Tools, chọn Add-Ins
+ Chọn nút "Browse" và chọn file "Bluesofts_MacBook_ControlDeleteIsDeleteInWindows.xla"
(*) Cơ chế hoạt động trong Excel
Tại máy MacBook. Mở Excel, chọn một vùng bất kỳ nhấn CONTROL+DELETE khi đó vùng sẽ được xóa.
Nếu máy của bạn không phải là MacBook thì việc này chỉ cần phím DELETE
Như chúng ta đã biết, một trong những công tác thường xuyên, quen thuộc của kế toán và quản lý kho là lập thẻ kho. Việc làm này có ý nghĩa để theo dõi số lượng nhập, xuất, tồn kho từng nguyên liệu, vật liệu, công cụ, dụng cụ, sản phẩm, hàng hóa ở từng kho, qua đó làm căn cứ xác định số lượng tồn kho dự trữ vật liệu, dụng cụ, sản phẩm, hàng hóa, đồng thời xác định trách nhiệm vật chất của thủ kho cũng như các thành viên liên quan khác.
Trong Excel, việc lập thẻ kho chính là chúng ta tạo ra các cột dữ liệu gồm : ngày tháng, số lượng nhập, số lượng xuất, số lượng tồn chi tiết theo từng mã hàng, trong đó tồn chi tiết được tính theo phương pháp tính cộng dồn (lũy kế). Công việc này nếu thực hiện tính toán bằng công thức Excel sẽ khá khó khăn và công thức chạy rất chậm vì ta cần phải lấy nguyên dữ liệu từ dữ liệu gốc sang.
Add-in A-Tools sẽ giúp bạn khắc phục hoàn toàn nhược điểm của công thức Excel truyền thống:
+ Theo điều kiện mã hàng cho trước, dữ liệu trích lọc đến đâu thì công thức được thực hiện đến đó. Chúng ta không phải copy công thức cho nhiều dòng dữ liệu trống nên làm nhẹ file Excel rất nhiều so với dùng công thức Excel.
+ Dữ liệu được lọc ra theo điều kiện chỉ với một công thức duy nhất, khi thay đổi điều kiện, các dữ liệu tự động được cập nhật và thay đổi theo.
Video hướng dẫn chi tiết
Cách làm
Bước 1: Đặt tên bảng dữ liệu nguồn
+ Bôi đen bảng dữ liệu nguồn.
+ Chọn "Define Name".
+ Tại mục "Name" bảng thiết lập, ta gõ tên bảng dữ liệu nguồn. Ví dụ: KHO
Bước 2: Dùng công thức A-Tools tạo thẻ kho
1. Lấy ra cột ngày,, số lượng nhập, số lượng xuất từ bảng dữ liệu nguồn
+ Đặt chuột tại ô trống muốn lấy ra dữ liệu, tìm đến menu A-Tools, chọn "SQL_Builder"
+ Kéo bảng "KHO" vào vùng Main của SQL_Builder.
+ Tích chọn tên cột muốn lấy ra dữ liệu và tên cột điều kiện. Ở ví dụ này, ta cần tích chọn các cột: ngày (NGAY), số lượng nhập (SLG N), số lượng xuất (SLG X), mã vật liệu sản phẩm hàng hóa (MA_VLSPHH)
+ Tại mục thiết lập chi tiết cho từng cột, bỏ tích chọn "Output" tại cột điều điện MA_VLSPHH, đồng thời tại mục "Criteria", ta chọn "Range" và trỏ chuột đến ô điều kiện. Ví dụ: 'Range(B3)'
+ Chọn "Tiếp tục".
+ Tại mục "Kết quả truy vấn trả về theo", ta chọn "Hàm BS_SQL".
+ Kết quả, khi ta thay đổi điều kiện mã hàng, các thông tin ngày, số lượng nhập xuất sẽ tự động thay đổi theo điều kiện ta chọn như sau:
2. Sử dụng hàm DATA() để tạo công thức cộng dồn cho cột "TON"
+ Mục đích của hàm DATA() là lấy dữ liệu tại một mảng dữ liệu xác định.
+ Cấu trúc hàm DATA() :
DATA(Row,Col)
Trả về giá trị tại dòng (Row) và cột (Col) trong mảng giá trị.
Row và Col là số nguyên dương (>=0) có thể nhận các biến hệ thống dưới đây:
PreRow: Trả về dòng liền trước.
Row: Trả về dòng đang hoạt động.
PreCol: Trả về cột liền trước.
Col: Trả về cột đang hoạt động.
Lưu ý: Chỉ số cột và chỉ số dòng lấy từ giá trị 0.
+ Kích chuột vào ô có chứa công thức của thẻ kho, công thức BS_SQL hiển thị, chúng ta thêm cột TON sử dụng công thức DATA() theo nguyên tắc "Tính dồn = Tồn trước + Nhập - Xuất" như sau:
Ta đặt tên cột mới bằng cách gõ "AS TON".
+ Nhấn Ctrl+Shift+ Enter để hiển thị kết quả (Vì ta đang sử dụng công thức mảng)
+ Thêm dòng tổng cho cột nhập, xuất và định dạng lại bảng dữ liệu đẹp mắt hơn.
Bước 3: Tạo bảng tìm kiếm cho mã hàng rất nhanh chóng và tiện ích.
+ Tạo bảng danh mục vật liệu sản phẩm hàng hóa với các thông tin chi tiết như sau:
+ Tại sheet thẻ kho, bôi đen 2 ô cần lấy ra dữ liệu điều kiện bao gồm thông tin mã hàng hóa và tên hàng hóa, ví dụ bôi đen 2 ô B3 và C3.
+ Chúng ta chọn đồng ý khi nhận được thông báo "Bạn có muốn thiết lập vùng cho "Nhập nhiều cột"?"
+ Tại mục "Dữ liệu nguồn", ta trỏ chuột chọn bảng danh mục dữ liệu nguồn. A-Tools sẽ gợi ý cho chúng ta tên cột nhập liệu và cột dữ liệu nguồn tương ứng. Ta có thể thay đổi thứ tự tên cột dữ liệu nguồn tương ứng với cột nhập liệu.
+ Tích chọn "Có dòng tiêu đề" để hiển thị tên tiêu đề.
+ Chọn cột nhận/kiểm tra giá trị trong danh sách tên cột trong bảng dữ liệu nguồn.
+ Tích chọn cột cần lấy giá trị chứa duy nhất.
+ Tích chọn "Nhập nhiều cột"
+ Nhấn "Thêm" để lưu lại thông tin.
Nếu muốn sửa lại thông tin ta chỉ cần vào chức năng "Nhập liệu nâng cao". Tại bảng vùng nhập liệu, chúng ta chọn vùng thiết lập cần sửa, sau đó chọn "Sửa" để tiến hành sửa lại.
Như vậy chỉ với thao tác cơ bản , ta đã tạo ra bảng tìm kiếm nhanh cho mã hàng rất nhanh chóng và tiện lợi. Khi mã hàng được chọn, thông tin tên hàng được tự động nhảy theo mà ta không cần phải sử dụng hàm VLOOKUP tìm kiếm khó nhớ.
Như vậy, với việc sử dụng hàm BS_SQL trong A-Tools bạn sẽ tạo được báo cáo có tính cộng trừ dồn, hay tính lũy kế với lệnh SQL kết hợp với hàm DATA trở nên rất dễ dàng. Nắm được phương pháp này các bạn sẽ làm được nhiều dạng báo cáo khác trong doanh nghiệp như sổ quỹ, theo dõi trừ nợ,...
Các bạn tham khảo khóa học "Tạo báo cáo động trong Excel và Add-in A-Tools" tại đây
Những hàm thời gian cơ bản thông dụng nhất gồm: TODAY(), NOW(), YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DATE, TIME, WEEKDAY
Hãy cùng xem hình ảnh động dưới đây và thử tạo ra 1 bảng tương đương để hiểu hơn về cách dùng các hàm thời gian theo cách dễ nhớ nhất, dễ hiểu nhất và dễ vận dụng nhất:
Add-in A-Tools cung cấp giải pháp tạo báo cáo động trong Excel rất mạnh và dễ dàng. Chỉ cần học một hàm duy nhất là BS_SQL với sáu từ khóa SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY bạn sẽ làm chủ công cụ này trong việc tạo báo cáo động.
Phương pháp tạo báo cáo trong Excel bằng Add-in A-Tools là hoàn toàn mới với thế giới người dùng Excel, được tạo theo tư duy mới, không dùng các hàm, các thủ thuật của Excel trước đây vì tính phức tạp, nhiều hạn chế như tốc độ chậm, không linh hoạt.
Làm chủ công nghệ Add-in A-Tools sớm bạn sẽ sớm đạt hiệu quả cũng như cơ hội trong công việc!
Các bạn tham khảo khóa học "Tạo báo cáo động trong Microsoft Excel và Add-in A-Tools" của BluesoftsTại đây
Trong video trước, mình đã hướng dẫn cho các bạn lập báo cáo trong khoảng thời gian. Bài toán hôm nay mình sẽ hướng dẫn lập báo cáo chi tiết của từng mã hàng trong một khoảng thời gian bất kỳ. Dữ liệu sẽ thay đổi và cập nhật theo ba điều kiện là từ ngày, đến ngày và mã hàng. Mỗi lần thay đổi điều kiện từ ngày, đến ngày, các bạn chỉ cần nhấn CTRL+SHIFT+D để cập nhật báo cáo.
Để tải cài đặt và tìm hiểu các chức năng của phần mềm tại các link dưới đây:
Hướng dẫn chuyển giá trị kiểu số sang văn bản chỉ bằng click chuột là một tính năng phụ trong phần mềm Add-in A-Tools. Người dùng chỉ việc cài đặt phần mềm Add-in A-Tools, sau đó bôi đen vùng giá trị kiểu con số mà muốn chuyển sang văn bản. Vào menu A-Tools và bằng 1 click chuột vào "Số-Văn bản" thì toàn bộ dữ liệu bôi đen sẽ được chuyển sang văn bản.
Trong Excel, việc sử dụng name trong Excel là công việc không thể thiếu của các chuyên gia khi thiết kế file Excel để quản trị, làm báo cáo, ... Thông thường, người dùng thường đặt name cố định hoặc rộng hơn phạm vi vùng dữ liệu, việc làm này sẽ làm ảnh hưởng tới dung lượng và dữ liệu trong file. Trong khi đó, Excel đã có một ứng dụng name động mà rất ít người dùng biết đến đó là Table. Mà name của table này chỉ bao quanh vùng dữ liệu, khi nhập thêm dữ liệu thì table tự động mở rộng bao gồm cả vùng dữ liệu vừa nhập.
Cách tạo table và ý nghĩa từng nút lệnh trong table.
Bước 1: Tạo Table: Bôi đen vùng dữ liệu bao gồm cả tiêu đề -> vào menu Insert -> Table (phím tắt là CTRL+ T)
Hướng dẫn dùng 1 công thức để lọc dữ liệu trong khoảng thời gian bất ký chỉ trong vòng 2 phút. Khi thay đổi điều kiện thời gian, chỉ cần đặt chuột vào vùng công thức bấm vào cập nhật hoặc nhấn tổ hợp phím CTRL+SHIFT+A thì dữ liệu sẽ tự động cập nhật. Để sử dụng công thức, các bạn cần cài đặt phần mềm Add-in A-Tools.
Để tải cài đặt và tìm hiểu các chức năng của phần mềm tại các link dưới đây:
Bài viết này chia sẻ đến các bạn phương pháp làm báo cáo Nhập Xuất Tồn trong Microsofts Excel đơn giản, dễ dàng, không cần sử dụng các công thức Excel, ngay cả các bạn mới làm quen với Excel và trình độ Excel cơ bản cũng có thể áp dụng để tự làm báo cáo động linh loạt, nhanh chóng.
Video hướng dẫn thực hiện:
(*) Giả sử ta có một file Excel gồm có 2 sheet :
- Sheet 1 là danh mục hàng (DM VLSPHH) gồm các thông tin về đặc điểm của hàng hóa.
- Sheet 2 là kho (KHO) gồm bảng dữ liệu về nhập xuất kho.
- Chúng ta tiến hành xây dựng bảng tổng hợp Nhập Xuất Tồn.
Trong đó: Mã hàng và tên hàng chúng ta lấy thông tin tại danh mục hàng, số lượng nhập lấy từ bảng kho với loại phiếu là nhập, số lượng xuất với loại phiếu là xuất.
(*) Trình tự các bước làm báo cáo tổng hợp Nhập Xuất Tồn trong Excel bằng công cụ Add-in A-Tools:
- Bước 1: Đặt tên vùng dữ liệu.
+ Tại bảng danh mục hàng, bôi đen vùng dữ liệu từ tiêu đề đến dòng cuối cùng.
+ Sau đó vào “Formulas” trên thanh công cụ A-Tools, chọn “Define Name”.
+ Tại mục “Name” gõ tên bảng định đặt, ví dụ ở đây là “DMVLSPHH”.
+ Nhấn “OK” để đóng.
Ta đặt tên tương tự cho bảng “KHO”
- Bước 2: Đóng và mở lại file Excel để cập nhật tên vùng
- Bước 3: Tính tổng hợp hàng nhập
+ Vào sheet “NHAP”, đặt con trỏ tại ô A4, vào menu “A-Tools”, chọn “SQL Builder”.
+ Từ “Table” trong (curent database), tìm tới kí hiệu bảng “KHO” và kéo sang bảng “Main”.
+ Từ bảng kho vừa kéo sang, ta tích chọn tên cột lấy ra gồm: “MA_VLSPHH”, “SLG”, “LOAI_PHIEU”
Tại thiết lập chi tiết của “SLG”, tại cột “Aggregate”, chúng ta sổ combo box xuống và chọn “Sum"
Tại thiết lập chi tiết của “MA_VLSPHH”, tích chọn “Grouping”, vì theo nguyên tắc, khi ta chọn tính tổng cho một cột nào đó, ở đây ví dụ là cột “SLG”, thì cần đặt ra tiêu chí để nhóm lại.
Tại thiết lập chi tiết của “LOAI_PHIEU”, chúng ta thiết lập loại phiếu bằng “N” tại mục “Criteria” và bỏ tích cột “Output”.
+ Sau khi thiết lập xong, ta chọn “Run Query” để chạy thử, bảng mã hàng và số lượng nhập sẽ được hiển thị, tuy nhiên, cột số lượng do chúng ta chưa dặt tên nên A-Tools tự tạo tên “Expr1001”, chúng ta đặt lại tên như sau:
Vào lại bảng “SQL Buider”.
Tại thiết lập chi tiết phía dưới, tại dòng số lượng, chúng ta tìm đến cột “Alias” và đặt lại tên, ví dụ ở đây là “N”.
+ Chúng ta nhấn “Tiếp tục”, tại bước cuối cùng này chúng ta đặt tên bảng bằng cách tích chọn “Tên bảng kết quả (Define Name)” và đặt tên bảng là “NHAP”.
+ Chọn “Thực hiện” để hiển thị kết quả.
Vậy là chỉ với thao tác đơn giản, ta đã tạo ra được bảng dữ liệu có tên “NHAP” báo cáo về hàng nhập lấy dữ liệu từ “KHO”, khi bảng dữ liệu trên tăng hay giảm mã hàng thì vùng tên luôn luôn được co giãn theo và chúng ta không phải đặt lại tên.
Bạn cũng có thể gõ trực tiếp công thức tại ô địa chỉ A4 như sau:
- Bước 4: Tính tổng hợp hàng xuất (copy từ bước 3)
Đối với báo cáo hàng xuất, các thao tác làm tương tự như đối với báo cáo hàng nhập.
Chúng ta có thể thao tác nhanh bằng cách copy công thức từ báo cáo hàng nhập.
+ Vào báo cáo hàng nhập, đặt con trỏ vào ô công thức, nhấn “Ctrl+C” hoặc chọn “Copy”, sau đó sang báo cáo hàng xuất, đặt con trỏ vào ô cần dán và chọn “Paste”.
+ Sửa lại báo cáo bằng cách đặt con trỏ tại ô công thức, chọn “SQL Builder”.
+ Tại thiết lập chi tiết, sửa dòng “SLG” thành “X” tại cột “Alias”, tiếp tục sửa dòng điều kiện “LOAI_PHIEU” thành “X” tại cột “Criteria”.
+ Nhấn “Run Query” để chạy thử.
+ Chọn “Tiếp tục”.
+ Tại bước cuối cùng, ta chọn đặt lại tên bảng kết quả là "XUAT".
+ Sau đó nhấn "Thực hiện" để hiển thị kết quả.
Chúng ta cũng có thể gõ trực tiếp công thức BS_SQL tại ô địa chỉ cần lấy ra kết quả như sau:
- Bước 5: Tạo công thức tổng hợp Nhập - Xuất - Tồn
Bước này chính là bước dựa vào kết quả từ các bước trước.
+ Chúng ta lưu file, sau đó đóng và mở lại, vì vừa rồi chúng ta vừa tạo báo cáo nhập và báo cáo xuất, trong đó có thao tác tạo ra tên bảng, bất cứ khi nào có tạo ra tên bảng, ta đóng file Excel và mở lại để A-Tools có thể cập nhật lại.
+ Tại sheet “NHAP-XUAT-TON”, đặt con trỏ chuột tại ô A7, vào “A-Tools” trên thanh công cụ, chọn “SQL Builder”.
+ Tại “Tables”, ta chọn kéo bảng “DMVLSPHH”, “NHAP ”, "XUAT"
+ Ta kéo thả để nối các bảng với nhau, ta kéo dòng "MA_VLSPHH" bên bảng "DMVLSPHH" sang dòng "MA_VLSPHH" bên bảng "NHAP", thao tác tương tự để nối với bảng "XUAT".
+ Chúng ta tích chọn để lấy ra các cột thông tin mà chúng ta muốn hiển thị : "MA_VLSPHH", "TEN", "Dvi", "N", "X"
+ Nhấn "Run Query" để chạy thử.
+ Xây dựng thêm cột tồn, ta quay về chế độ thiết kế, tại vùng thiết lập chi tiết, ta đặt con trỏ chuột vào dòng trống tiếp theo của cột “Expression”, gõ công thức “N-X”, bản chất công thức này chính là lấy cột nhập trừ cột xuất.
+ Chọn “Tiếp tục”.
+ Bỏ tích chọn nút "Bảng kết quả gồm dòng tiêu đề”. Chọn “Thực hiện” để lấy ra kết quả.
+ Định dạng lại để bảng dữ liệu trong đẹp mắt và cân đối hơn.
Như vậy với chỉ 5 bước đơn giản thao tác kéo thả chuột của Add-in A-Tools, chúng ta đã tạo ra được báo cáo nhập xuất tồn mà không cần dùng hàm Vlookup, SumIf, If hay công thức mảng.
Tham khảo thêm khóa học "Tạo báo cáo động trong Microsoft Excel và Add-in A-Tools"là khóa học duy nhất chỉ có ở Công ty cổ phần BLUESOFTS. Khi tham gia khóa học các bạn sẽ được tìm hiểu thêm các Kỹ thuật làm báo cáo động giúp bạn tạo ra những báo cáo trích lọc nhiều điều kiện phức tạp trở nên đơn giản, tốc độ chạy nhanh hơn bất kỳ phương pháp làm hàm Excel truyền thống.
