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
