Tự viết hàm Xếp Loại trong Excel bằng VBA

Tự viết hàm Xếp Loại trong Excel bằng VBA

Với lập trình VBA, các bạn có thể tạo ra các hàm tính toán theo nhu cầu công việc của bản thân sao cho thuận lợi và phù hợp nhất.

Giả sử chúng ta có 1 bài toán như sau:

Căn cứ vào tổng điểm để xếp loại học sinh như sau

Dưới 10: Kém

Từ 10 đến 13.9: Trung bình

Từ 14 đến 16.9: Khá

Từ 17 đến 18.9: Giỏi

Ngược lại là Xuất Sắc

 

 

Để tạo ra một hàm tính tổng điểm theo những điều kiện trên, các bạn làm theo các bước sau:

Bước 1: Các bạn vào môi trường lập trình của Excel (phím tắt ALT+F11), tạo một module và copy code dưới đây vào trong module đấy:

 

Function XepLoai(tongdiem As Double) As String

    If tongdiem < 10 Then

        XepLoai = "Kem"

    ElseIf tongdiem >= 10 And tongdiem <= 13.9 Then

        XepLoai = "Trung binh"

    ElseIf tongdiem >= 14 And tongdiem <= 16.9 Then

        XepLoai = "Kha"

    ElseIf tongdiem >= 17 And tongdiem <= 18.9 Then

        XepLoai = "Gioi"

    Else

        XepLoai = "Xuat Sac"

    End If

End Function

 

 

Bước 2: Các bạn trở về môi trường bảng tính, tại cột tổng điểm bạn gõ hàm như sau: H2=XepLoai(G2)

 

 

Sau khi gõ xong, các bạn nhấn Enter để nhận kết quả.

 

 

Bước 3: Tiếp theo các bạn copy công thức cho các ô còn lại để nhận két quả.

 

 

Với Excel thuần túy, để giải quyết các bài toán như trên, chúng ta phải gõ nguyên đầy đủ cả công thức, khi gửi công thức cho người khác, nhiều người sẽ bị rối.  Vậy chúng ta nên tự tạo ra một bộ hàm riêng để xử lý công việc của chúng ta. Các nhân viên chỉ việc gõ tên hàm và các đối số là đã có những bản báo cáo, số liệu chính xác mà nhìn vào sẽ rất ngắn gọn, dễ hiểu.
File ví dụ: bluesofts.net/Baiviet/Laptrinh/VB_VBA/phong/hamxeploai/ham xep loai.xlsm
Để tìm hiểu thêm nhiều kiến thức về VBA cơ bản, các bạn tham khảo thêm tại khóa đào tạo VBA cơ bản của công ty.
Link khóa học: http://bluesofts.net/daotaothuchanh/daotaovbacoban.html

Tự viết hàm tính tổng điểm có điều kiện bằng VBA

Với lập trình VBA, các bạn có thể tạo ra các hàm tính toán theo nhu cầu công việc của bản thân sao cho thuận lợi và phù hợp nhất.

Giả sử chúng ta có 1 bài toán như sau:

Yêu cầu:  Tính tổng điểm với điều kiện như sau:

  Tổng điểm = Toán + Tin

  Nếu đạo đức là A thì tổng điểm sẽ được cộng thêm 1

  Nếu đạo đức là B thì tổng điểm sẽ được cộng thêm 0.5

  Nếu đạo đức là D thì tổng điểm sẽ được trừ đi 1

  Còn lại thì giữ nguyên tổng điểm

Để tạo ra một hàm tính tổng điểm theo những điều kiện trên, các bạn làm theo các bước sau:

Bước 1: Các bạn vào môi trường lập trình của Excel (phím tắt ALT+F11), tạo một module và copy code dưới đây vào trong module đấy:

Function TinhTongDiem(toan As Double, tin As Double, DD As String) As Double

    If DD = "A" Then

        TinhTongDiem = toan + tin + 1

    ElseIf DD = "B" Then

        TinhTongDiem = toan + tin + 0.5

    ElseIf DD = "D" Then

        TinhTongDiem = toan + tin - 1

    Else

        TinhTongDiem = toan + tin

    End If

End Function

Bước 2: Các bạn trở về môi trường bảng tính, tại cột tổng điểm bạn gõ hàm như sau: G2=TinhTongDiem(D2,E2,F2)

Sau khi gõ xong, các bạn nhấn Enter để nhận kết quả.

Bước 3: Tiếp theo các bạn copy công thức cho các ô còn lại để nhận két quả.

Với Excel thuần túy, để giải quyết các bài toán như trên, chúng ta phải gõ nguyên đầy đủ cả công thức, khi gửi công thức cho người khác, nhiều người sẽ bị rối.  Vậy chúng ta nên tự tạo ra một bộ hàm riêng để xử lý công việc của chúng ta. Các nhân viên chỉ việc gõ tên hàm và các đối số là đã có những bản báo cáo, số liệu chính xác mà nhìn vào sẽ rất ngắn gọn, dễ hiểu.
File ví dụ: http://www.mediafire.com/file/8on9x4p335c9xqu/bai+toan+tong+diem.xlsm
Để tìm hiểu thêm nhiều kiến thức về VBA cơ bản, các bạn tham khảo thêm tại khóa đào tạo VBA cơ bản của công ty.
Link khóa học: http://bluesofts.net/daotaothuchanh/daotaovbacoban.html

TẠI SAO CẦN HỌC VBA TRONG EXCEL?

Học VBA trong Excel giúp bạn tạo ra những Macro - Mã lệnh để tự động tổng hợp, định dạng, tạo báo cáo chỉ bằng cú nhấp chuột như trong các phần mềm mà không phải thủ công lập công thức Excel. Học lớp VBA cơ bản học viên tự tạo thêm được các hàm-Function mà Excel chưa có, tăng tốc độ tính toán, làm báo cáo nhanh. Dù bạn đã biết làm công thức Excel tốt, chắc chắn bạn sẽ ngạc nhiên với khóa đào tạo VBA trong Excel của Bluesofts.

XEM VÀ ĐĂNG KÝ KHÓA HỌC VBA TRONG EXCEL: 
http://www.bluesofts.net/daotaothuchanh/daotaovbacoban.html

Lọc ra danh sách tên sheet trong file vào một cột trong bảng tính

Trong thực tế, những người sử dụng Excel mà sử dụng nhiều sheet để nhập số liệu. Việc thống kê tất cả tên sheet bằng các hàm excel thông thường là một việc rất khó khăn. Vậy mình xin chia sẽ cho các bạn thủ tục hiển thị danh sách tên sheet trong một file bất kỳ vào một cột trong bảng tính một cách đơn giản nhất. 

Giả sử chúng ta có một file Excel gồm các sheet sau: Bluesofts.net1, Bluesofts.net2, Bluesofts.net3, Bluesofts.net4, Bluesofts.net5, Bluesofts.net6. Yêu cầu bài toán là lọc ra tên tất cả các sheet vào một cột bất kỳ trong 1 sheet.

Để giải bài toán trên, các bạn làm theo các bước sau:

Bước 1: Các bạn vào môi trường lập trình (nhấn phím tắt Alt+F11) , rồi bạn vào menu Insert -> chọn vào module để insert module, sau khi tạo xong module, các bạn copy code dưới đây vào trong module:

Sub LayTenSheet()

   Dim Sh As Worksheet

   Dim I As Long

   'Duyet tung sheet trong sheets, ghi ten sheet ra cot A

   For Each Sh In Application.ThisWorkbook.Sheets

      I = I + 1

      Cells(I, 1).Value = Sh.Name

   Next

End Sub

Bước 2: Các bạn vào môi trường bảng tính, vào Menu Developer --> chọn Insert --> Button (Form Control) (Trường hợp trên thanh Menu chưa có thẻ Developer thì thực hiện như sau: File --> Options --> Customize Ribbon rồi tích chọn vào ô Developer và nhấn OK)

Sau khi chọn vào Button, các bạn đặt chuột vào 1 ví trí mà bạn muốn tạo nút bấm, giữ chuột trái và kéo một vùng đủ rộng của nút bấm,rồi thả chuột sẽ hiện ra bảng Assign Macro. Chọn tên Macro "LayTenSheet" rồi click chuột trái vào OK. 

Bước 3: Sau khi Assign xong Macro thì bạn click chuột phải vào nút bấm vừa tạo và sửa tên theo ý của bạn, sau khi sữa tên xong, các bạn click chuột ra bên ngoài bảng tính.

Bước 4: Tiếp theo các bạn click chuột trái vào nút bấm để nhận kết quả.

Tính ứng dụng của chức năng này: Trong môi trường doanh nghiệp file excel sẽ có rất nhiều sheets, việc sử dụng công thức giữa các sheets hoặc nhớ các tên sheets gặp nhiều khó khăn, đồng thời file excel có thể chia sẻ cho nhiều người sử dụng. Do đó, chức năng này sẽ giúp người dùng kiểm soát và sử dụng file excel hiệu quả hơn.

Ngoài ra, khi tạo ra file excel tự động bằng công thức hoặc bằng VBA thì người dùng sẽ thường ẩn sheets tự động để không tác động vào tên, cấu trúc các sheets trong file excel. Tính năng này sẽ trở nên hữu ích cho người dùng để tạo các thêm các công thức từ các sheets bị ẩn theo cấu trúc ten_sheet!. Ví dụ: =Sum(Bluesofts.net2!$B$5:$B$200).

Link tải file: bluesofts.net/Baiviet/Laptrinh/VB_VBA/phong/loctensheet/loctensheet.xlsm

Đăng ký học: Link đăng ký học.

Quy trình quản lý kho Vật liệu – Sản phẩm – Hàng hóa – Đa đơn vị tính

(Viết tắt: VLSPHH – Vật liệu, Sản phẩm, Hàng hóa)

Đặc điểm:

- Một VLSPHH có thể 1 hoặc nhiều đơn vị tính. Giữa các đơn vị tính có hệ số quy đổi theo từng VLSPHH. Trong đó sẽ có 1 ĐVT là đơn vị tính cơ sở(hệ số quy đổi là 1:1)

- Khi nhập, xuất VLSPHH thì có thể nhập, xuất theo các ĐVT khác nhau. Việc này sẽ phải xử lý quy đổi tự động về ĐVT cơ sở

- Khi lên báo cáo kho (chi tiết, tổng hợp) thì tùy theo tiêu thức lựa chọn lên báo cáo theo đơn vị tính cơ sở và đơn vị tính quy đổi…

Cách xử lý trên Excel thông thường:

- Tạo các Sheets danh mục: ĐVT, VLSPHH và hệ số quy đổi

- Tạo Sheets cập nhật số liệu: Tùy theo tư duy  người dùng sẽ tạo 1 hay nhiều sheets để phản ánh các nghiệp vụ: Số dư đầu kỳ, Nhập, Xuất. Trong các Sheets nhập liệu sẽ phải dùng các kỹ thuật như: Hàm Excel, Data Validation, VBA để kiểm tra tính hợp lệ của dữ liệu, đặc biệt là phần đơn vị tính. Ví dụ: 1 mặt hàng khai báo có 3 đơn vị tính là Kg, Tấm, Cuộn thì không được sử dụng đơn vị tính là Chiếc.

- Tạo các Sheets báo cáo như Thẻ kho, Báo cáo tồn kho, Sổ chi tiết kho, Tổng hợp Nhập – Xuất – Tồn thông qua sử dụng các hàm Excel.

* Ưu điểm: Phù hợp với doanh nghiệp nhỏ, phát sinh ít và ít mã hàng

* Nhược điểm: Sử dụng rất nhiều hàm Excel, Phải tạo nhiều cột phụ, bảng phụ nên dễ gây ra sai sót, khó kiểm tra và đặc biệt sẽ đẩy dung lượng của file Excel lên rất lớn, chậm file và gây nguy hiểm cho số liệu, mất dữ liệu. Sử dụng hình thức này thì file Excel sẽ không dùng chung cho nhiều người được

Cách xử lý Excel – Công cụ Add-in  A-Tools:

- Tạo các Sheets danh mục

- Tạo Sheets nhập liệu: Nên để chung thành 1 Sheets Data và phản ánh các nghiệp vụ thông qua mã giao dịch. Sử dụng công cụ Add-in A-Tools để tạo Names động – Kiểm soát tính hợp lệ của số liệu

- Tạo các Sheets báo cáo kho thông qua việc ứng dụng hàm BS_SQL của Add-in A-Tools. Hàm BS_SQL sẽ hỗ trợ người dùng lên 1 báo cáo chỉ bằng 1 công thức

* Ưu điểm: Không phải tạo thêm cột phụ, bảng phụ, sử dụng ít hàm và đặc biệt các hàm đều được tối ưu về dạng text thông qua kỹ thuật của Add-in  A-Tools nên sẽ tối ưu được dung lượng và tốc độ. Và đặc biệt, khi sử dụng hình thức này thì file excel chia sẽ được cho nhiều người dùng chung trong cùng một thời điểm thông qua ứng dụng Chia sẻ file Excel qua mạng và phân quyền truy cập của Add-in  A-Tools theo cơ chế Clients/Servers

 

* Nhược điểm: Mất chi phí đầu tư bản quyền A-Tools, nếu các doanh nghiệp quy mô nhỏ thì có thể tận dụng A-Tools Free. Ngoài ra, đối với doanh nghiệp quy mô rất lớn (có đến hàng nghìn, hàng chục nghìn mã VLSPHH) thì vẫn vấp phải dung lượng lớn của file Excel, bật file Excel lên sẽ chậm

Cách xử lý phần mềm:

- Phần mềm có các danh mục: Nhóm, Loại, VLSPHH, DVT, KHO, Hệ số quy đổi

- Phần nhập liệu: Thực hiện quy đổi tự động theo hệ số khai báo trên các màn hình nhập chứng từ

- Khi lên báo cáo thì sẽ có các tiêu thức lựa chọn theo đơn vị tính, nhóm, loại, mã hàng, kho…Và tất nhiên người dùng không phải dùng hàm, tạo hàm nữa rồi vì phần mềm thực hiện lên báo cáo tự động

* Ưu điểm: Có tính tự động hóa cao trong việc tính toán, chuẩn hóa quy trình nhập liệu và theo dõi, lưu trữ số liệu lớn

* Nhược điểm: Mất phí đầu tư bản quyền phần mềm, phải phụ thuộc theo tư duy thiết kế của nhà cung cấp phần mềm. Phần này thì người dùng sẽ phải xác định tìm phần mềm hoặc nhà cung cấp có giải pháp đáp ứng được yêu cầu - cân đối chi phí hợp lý và phần mềm có tính động để người dùng có khả năng tùy biến, khai thác hiệu quả hơn

• Giải pháp BS Silver

 

 

+ Theo dõi hàng hóa vật tư theo: Lô, hạn sử dụng, đa đơn vị tính, quy cách, thuộc tính, nhóm, loại, tồn tối thiểu, tối đa, kho, vị trí, nhà sản xuất, mã vạch (barcode), hình ảnh,…

+ Theo dõi nhập xuất theo quy trình đầy đủ từ: Lệnh nhập xuất, báo giá, đơn hàng, hợp đồng, phiếu nhập, phiếu xuất, hóa đơn, hàng trả lại, dịch vụ và theo các đối tượng, nhóm đối tượng

+ Tự động tính toán hàng cần mua

+ Khả năng lưu trữ số liệu lớn, tính toán nhanh, tối ưu hóa dữ liệu, phân quyền chi tiết từng thao tác

…

Chi tiết xem tại: Phần mềm quản lý kho chuyên nghiệp BS Silver

Thủ tục ẩn hiện các sheet trừ sheet đầu tiên

Chia sẽ thủ tục ẩn hiện các sheet trừ sheet đầu tiên, khi bạn click vào nút bấm, nếu các sheet còn lại trừ sheet đầu tiên mà chưa ẩn thì nó tự động ẩn, còn nếu những sheet trừ sheet đầu tiên mà ẩn thì nó sẽ hiển thị. Để xem cụ thể bài toán, các bạn làm theo các bước dưới đây:

Bước 1: Các bạn copy code dưới đây vào trong module (Module tạo theo hướng dẫn của các bài trước).

1 2 3 4 5 6 7 8 9 10 11 12

Sub AnHienSheet()    Dim Sh As Worksheet    Dim I As Long    'An cac sheet trong sheets tru sheet dau tien    For Each Sh In ThisWorkbook.Sheets       I = I + 1       If I <> 1 Then          Sh.Visible = Not Sh.Visible       End If    Next End Sub

Bước 2: Các bạn vào môi trường bảng tính, tạo 1 nút bấm và gán Macro bạn vừa tạo vào nút bấm (để tạo và gán macro các bạn xem lại các bài trước), rồi click chuột trái ra ngoài.

Bước 3: Các bạn click chuột vào nút bấm để nhận kết quả.

Bước 4: Sau khi ẩn các sheet trừ sheet đầu tiên, các bạn lại click chuột trái vào nút bấm lần 2 thì sẽ tự động hiển thị lại các sheet.

Để tìm hiểu thêm nhiều hàm và thủ tục trong VBA, các bạn tìm hiểu thêm tại khóa học VBA trong excel cơ bản của công ty. 
Link khóa học: http://bluesofts.net/daotaothuchanh/daotaovbacoban.html