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,...
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.
Trong doanh nghiệp, việc dự trữ và hoạch định tồn kho mang tính chất quan trọng trong quản lý. Nếu dự trữ tồn kho quá nhiều khiến nguồn lực cho các khoản khác giảm, chi phí lưu trữ tăng, tăng rủi ro trong doanh nghiệp. Ngược lại, nếu dự trữ tồn kho quá ít thì dễ xảy ra tình trạng thiếu hàng, chậm giao hàng….
Chính sách dự trữ được căn cứ vào các yếu tố sau:
- Tính mùa vụ của mặt hàng, nghành hàng
- Kế hoạch kinh doanh, tăng trưởng của công ty
- Sản phẩm thay thế, sản phẩm tương đương
- Nhu cầu, thị hiếu của thị trường đối với sản phẩm
Dự trữ hàng bao gồm 2 cận mức:
- Tồn an toàn: Tức mức quy định tồn kho tối thiểu mà trong kho phải dự trữ
- Tồn tối đa: Là mức tồn kho tối đa trong kho lưu trữ
Nghiệp vụ Dự trữ hàng trong thực tế:
Nhập kho: Khi hàng trong kho đang ở cận mức tối đa, bằng hoặc lớn hơn mức tối đa mà vẫn nhập thêm hàng (Hàng trong kho + Hàng nhập thêm >=Tồn tối đa) thì sẽ cảnh báo hoặc không cho nhập
Xuất kho: Khi hàng trong kho đang ở cận mức an toàn, bằng hoặc nhỏ hơn mức an toàn mà vẫn xuất thêm (Hàng trong kho- Hàng xuất <=Tồn an toàn) thì sẽ cảnh báo hoặc không cho nhập
*Tồn an toàn mức 0 và xuất âm: Trong nhiều tình huống, mặt hàng không khai báo tồn an toàn thì tồn an toàn được hiểu là 0. Khi đó nếu xuất quá thì sẽ trở thành xuất âm (xuất âm có xảy ra trong thực tế với một số trường hợp như: Hàng đang đi trên đường, kiểm kê kho sai…)
Ứng dụng BS Silver trong kiểm soát hàng dữ trữ:
Khai báo Tồn an toàn, tồn tối đa cho từng mã VLSPHH trong danh mục:
Tính năng thiết lập khai báo “Cho phép xuất âm” hay không:
Phần danh mục hàng hóa (List danh sách) có chức năng hiển thị tồn kho tức thời, tồn kho an toàn, cho phép xem tổng số mặt hàng khai báo, tổng số mã hàng còn tồn kho và đặc biệt là báo đỏ mã hàng khi tồn kho dưới an toàn
Khi nhập chứng từ thì BS Silver sẽ cảnh báo người dùng khi xuất dưới mức an toàn, xuất âm (theo thiết lập)
Khi xem báo cáo sẽ có thêm cột Tồn kho an toàn để người dùng dễ so sánh, đánh giá và lên kế hoạch cho dự trữ và quản lý kho:
Để tham khảo chi tiết hơn về tính năng và trải nghiệm sử dụng Giải pháp phần mềm quản lý kho - sản xuất BS Silver xin vui lòng truy cập Tại Đây
Hôm nay tôi xin hướng dẫn các bạn cách tạo danh sách chọn động - Data validation list động trong Excel với giải pháp đơn giản nhất.
Khi chúng ta có một danh sách giá trị nguồn dài, nhiều, làm sao để danh sách này được lọc để thu gọn theo nhóm hàng hay điều kiện lọc mà bạn chọn ở một ô khác. Để làm được việc này chúng ta cần thực hiện 3 bước dưới đây:
Bước 1. Tạo danh sách mã vùng/Mã nhóm rồi đặt NAME
Chọn vùng D4:D6 nơi mà bạn đã tạo danh sách các giá trị như mã nhóm, các mã nhóm này không dấu nhé để bước sau đặt NAME thành công. Để đặt NAME cho vùng chọn, nhấn CTRL+F3 và đặt là "MANHOM".
Bước 2. Từ danh sách giá trị nguồn, tạo NAME cho từng vùng dữ liệu có chung nhóm.
Vùng A3:A14 chứa danh sách các giá trị mà bạn muốn lọc chọn. Hãy thực hiện các bước:
+ Sắp xếp theo trật tự để các giá trị cùng nhóm nằm gần nhau
+ Đặt NAME cho từng vùng có chung mã nhóm. Ví dụ A3:A5 chung nhóm "Addin", ta chọn (bôi đen) rồi nhấn CTRL+F3, đặt NAME là "Addin" ở mục "Name:". Như vậy NAME "Addin" trỏ vào vùng.
+ Đặt NAME tương tự với 2 nhóm còn lại ta có thêm các NAME là "DAOTAO" trỏ vào vùng A:A10; "PHANMEM" trỏ vào vùng A11:A14.
Chú ý: Các NAME bạn đặt tên của nó phải nằm trong vùng "Mã Nhóm" (có NAME là MANHOM nhé.
Bước 3. Tạo Validation tại sheet nhập liệu, nơi chọn giá trị từ danh sách động
+ Vùng A4:A16 để bạn nhập mã nhóm - chính là 3 cái NAME bạn đã tạo bước 2 (Addin, DAOTAO, PHANMEM) là cơ sở để lọc
+ Vùng B4:B16 bạn sẽ chọn giá trị từ danh sách động. Cứ khi bên cột A nhập mã nhóm nào thì cột B sẽ có danh sách giá trị của mã nhóm đó. Thật tuyệt vời phải không các bạn! Chọn vùng B4:B16, nhấn CTRL+F3: Allow: chọn List; Source nhập công thức =INDIRECT(A4).
(Bài viết của Th.S Nguyễn Duy Tuân thuộc Công ty cổ phần BLUESOFTS. Các bạn có thể chia sẻ các bài viết để học tập của BLUESOFTS nhưng KHÔNG ĐƯỢC THAY ĐỔI TÊN FILE, TÊN HÌNH ẢNH, NỘI DUNG bài viết, nội dung file Excel mà chúng tôi đã đăng. KHÔNG SỬ DỤNG TƯ LIỆU CỦA BLUESOFTS cho mục đích kinh doanh!)
Khi lập trình VBA trong Excel chúng ta cần lấy danh sách các máy in tùy ý và chọn một sheet tùy ý để in. Phương pháp làm là lập trình VBA kết hợp phương pháp lập trình Windows API.
Mã nguồn lấy danh sách máy in và in tùy ý lập trình VBA trong Excel như sau:
'Cai tien dua vao userform - Nguyen Duy Tuan - http://bluesofts.net -
duytuan@bluesofts.net
Option Explicit
Private Sub cmdPrint_Click()
Dim sh As Worksheet
Set sh = ActiveWorkbook.Sheets(cbSheets.Value)
sh.PrintOut , , txtCopies.Value, False, cbPrinters.Value
End Sub
Private Sub SpinButton1_Change()
'Tang, giam so ban copy
txtCopies.Value = SpinButton1.Value
End Sub
Private Sub UserForm_Initialize()
Dim PrinterName As Variant, sh As Object, I&
I = 0
For Each PrinterName In EnumeratePrinters
I = I + 1
cbPrinters.AddItem PrinterName
If cbPrinters.ListIndex < 0 And PrinterName = ActivePrinter Then
'Lay may in default
cbPrinters.ListIndex = I - 1
End If
Next
'Hien may in dau tien
If cbPrinters.ListIndex < 0 And cbPrinters.ListCount > 0 Then
cbPrinters.ListIndex = 0
End If
I = 0
For Each sh In ActiveWorkbook.Sheets
I = I + 1
cbSheets.AddItem sh.name
If cbSheets.ListIndex < 0 And sh.name = ActiveSheet.name Then
'Hien sheet dang mo
cbSheets.ListIndex = I - 1
End If
Next
End Sub
