Hiểu các ô Excel so với các hàm phạm vi trong VBA
Excel rất mạnh mẽ. Nếu bạn sử dụng nó nhiều, có thể bạn đã biết nhiều thủ thuật sử dụng công thức hoặc tự động định dạng, nhưng sử dụng hàm Ranges và Cells trong VBA, bạn có thể nâng phân tích Excel của mình lên một tầm cao mới.
Vấn đề với việc sử dụng các chức năng Ô và Phạm vi trong macro VBA là ở các cấp độ nâng cao, hầu hết mọi người gặp khó khăn trong việc hiểu cách các chức năng này thực sự hoạt động. Sử dụng chúng có thể rất khó hiểu. Đây là cách bạn có thể tận dụng chúng theo những cách mà bạn có thể chưa bao giờ tưởng tượng được.
Mục Lục
Chức năng ô Visual Basic
Các hàm Ô và Phạm vi cho phép bạn cho tập lệnh VBA của mình biết chính xác vị trí bạn muốn lấy hoặc đặt dữ liệu trên trang tính của mình. Sự khác biệt chính giữa hai ô là những gì chúng tham chiếu.
Hàm ô VBA thường tham chiếu đến một ô duy nhất tại một thời điểm, trong khi Phạm vi tham chiếu đến một nhóm ô cùng một lúc.
Đây là định dạng cho hàm ô:
Cells(row, column)Điều này tham chiếu đến mọi ô trong toàn bộ trang tính. Đây là một trong những ví dụ như vậy trong đó chức năng ô macro VBA không tham chiếu đến một ô đơn lẻ:
Worksheets("Sheet1").CellsNgược lại, đoạn mã này tham chiếu đến ô thứ ba từ bên trái, trong hàng số một, tức là Ô C1:
Worksheets("Sheet1").Cells(3)Hãy mạo hiểm một chút, bằng cách giới thiệu ô D15 sử dụng chức năng ô VBA:
Worksheets("Sheet1").Cells(15,4)Nếu bạn muốn, bạn cũng có thể tham chiếu ô D15 với các ô sau có chức năng mã VBA:
Cells(15,"D")"— bạn được phép sử dụng ký tự cột.
Có rất nhiều sự linh hoạt khi sử dụng hàm ô VBA, vì bạn có thể tham chiếu một ô bằng cách sử dụng một số cho cột và ô, đặc biệt với các tập lệnh có thể vòng qua nhiều ô (và thực hiện các phép tính trên chúng) rất nhanh chóng.
Hàm Phạm vi
Theo nhiều cách, hàm Phạm vi mạnh hơn nhiều so với việc sử dụng Ô, vì nó cho phép bạn tham chiếu một ô đơn lẻ hoặc một phạm vi ô cụ thể, tất cả cùng một lúc. Bạn sẽ không muốn lặp qua một hàm Phạm vi, vì các tham chiếu cho ô không phải là số (trừ khi bạn nhúng hàm ô VBA Excel vào bên trong nó).
Định dạng cho chức năng này như sau:
Range(Cell #1,Cell #2)Mỗi ô có thể được chỉ định bằng một số chữ cái. Hãy xem một vài ví dụ.
Đoạn mã sau là một ô tham chiếu hàm phạm vi A5:
Worksheets("Sheet1").Range("A5")Ở đây, hàm phạm vi đang tham chiếu đến tất cả các ô từ A1 đến E20:
Worksheets("Sheet1").Range("A1:E20")Như đã đề cập ở trên, bạn không phải sử dụng các phép gán ô số-chữ cái. Bạn thực sự có thể sử dụng hai hàm ô VBA bên trong một hàm Phạm vi để xác định một phạm vi trên trang tính, như sau:
With Worksheets("Sheet1")
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With
Mã này tham chiếu cùng một phạm vi với Phạm vi (“A1: E20”) chức năng nào. Giá trị của việc sử dụng nó là nó sẽ cho phép bạn viết mã hoạt động động với các phạm vi bằng cách sử dụng vòng lặp.
Bây giờ bạn đã hiểu cách định dạng các hàm Ô và Dải ô, hãy đi sâu vào cách bạn có thể sử dụng sáng tạo các hàm này trong mã VBA của mình.
Xử lý dữ liệu với chức năng ô
Chức năng ô Visual Basic khá hữu ích khi bạn có một công thức phức tạp mà bạn muốn sử dụng trên nhiều phạm vi ô. Các phạm vi này cũng có thể tồn tại trên nhiều trang tính.
Hãy lấy một ví dụ đơn giản. Giả sử bạn quản lý một nhóm bán hàng gồm 11 người và hàng tháng bạn muốn xem xét hiệu suất của họ.
Bạn có thể có Sheet1 theo dõi số lượng bán hàng và khối lượng bán hàng của họ.
Trên Trang tính 2, bạn có dữ liệu để theo dõi xếp hạng phản hồi của họ trong 30 ngày qua từ khách hàng của công ty bạn.
Nếu bạn muốn tính tiền thưởng trên trang tính đầu tiên bằng cách sử dụng các giá trị từ hai trang tính, có nhiều cách để thực hiện việc này. Bạn có thể viết công thức trong ô đầu tiên thực hiện phép tính bằng cách sử dụng dữ liệu trên hai trang tính và kéo nó xuống. Điều đó sẽ hiệu quả.
Một giải pháp thay thế cho điều này là tạo một tập lệnh VBA chạy bất cứ khi nào bạn mở trang tính hoặc bạn kích hoạt nó thông qua một nút lệnh trên trang tính để bạn có thể kiểm soát khi nào nó tính toán. Bạn vẫn có thể sử dụng tập lệnh VBA để lấy tất cả dữ liệu bán hàng từ một tệp bên ngoài.
Vì vậy, tại sao không chỉ kích hoạt các tính toán cho cột tiền thưởng trong cùng một tập lệnh tại thời điểm đó?
Chức năng ô macro Excel đang hoạt động
Nếu bạn chưa bao giờ viết mã VBA trong Excel trước đây, bạn sẽ cần bật Nhà phát triển mục menu. Để làm điều này, hãy truy cập Tập tin > Tùy chọn. Bấm vào Tuy Biên ruy-băng. Cuối cùng, hãy chọn Nhà phát triển từ ngăn bên trái, Thêm vào nó vào khung bên phải và đảm bảo rằng hộp kiểm đã được chọn.
Bây giờ, khi bạn nhấp vào VÂNG và quay lại trang tính chính, bạn sẽ thấy tùy chọn menu Nhà phát triển.
Bạn có thể dùng Chèn để chèn một nút lệnh hoặc chỉ cần nhấp vào Xem mã để bắt đầu viết mã.
Trong ví dụ này, bạn sẽ thiết lập tập lệnh để chạy mỗi khi sổ làm việc mở ra. Để làm điều này, chỉ cần nhấp vào Xem mã từ menu nhà phát triển và dán hàm mới sau vào cửa sổ mã.
Private Sub Workbook_Open()
End SubCửa sổ mã của bạn sẽ trông giống như thế này.
Bây giờ bạn đã sẵn sàng để viết mã để xử lý phép tính. Sử dụng một vòng lặp duy nhất, bạn có thể bước qua tất cả 11 nhân viên và với hàm ô VBA trong Excel, hãy kéo vào ba biến cần thiết cho phép tính.
Nhớ cái gì đó worksheets.cells hàm có hàng và cột làm tham số để xác định từng ô riêng lẻ. Bạn cần thay thế x với tham chiếu hàng và sử dụng một số để yêu cầu dữ liệu của mỗi cột. Số hàng là số lượng nhân viên, vì vậy giá trị này sẽ từ 1 đến 11. Định danh cột sẽ là 2 cho Số lượng bán hàng, 3 cho Số lượng bán hàng và 2 từ Trang tính 2 cho Điểm phản hồi.
Phép tính cuối cùng sử dụng các tỷ lệ phần trăm sau để cộng lên đến 100 phần trăm tổng điểm thưởng. Nó dựa trên doanh số bán hàng lý tưởng là 50, doanh số bán hàng là 50.000 đô la và điểm phản hồi là 10.
-
(Sales Count/50) x 0.4 -
(Sales Volume/50,000) x 0.5 -
(Feedback Score/10) x 0.1
Cách tiếp cận đơn giản này mang lại cho nhân viên bán hàng một khoản tiền thưởng có trọng số. Với số lượng 50, khối lượng 50.000 đô la và điểm 10 — họ nhận được toàn bộ tiền thưởng tối đa trong tháng. Tuy nhiên, bất cứ điều gì dưới mức hoàn hảo trên bất kỳ yếu tố nào đều làm giảm tiền thưởng. Bất cứ điều gì tốt hơn lý tưởng sẽ tăng tiền thưởng.
Bây giờ chúng ta hãy xem cách tất cả logic đó có thể được thực hiện trong một tập lệnh VBA ngắn, rất đơn giản:
Private Sub Workbook_Open()
For x = 2 To 12
Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub
Đây là kết quả đầu ra của tập lệnh này sẽ như thế nào.
Nếu bạn muốn cột Tiền thưởng hiển thị tiền thưởng đô la thực tế hơn là tỷ lệ phần trăm, bạn có thể nhân nó với số tiền thưởng tối đa. Tốt hơn, hãy đặt số tiền đó vào một ô trên trang tính khác và tham chiếu nó trong mã của bạn. Điều này sẽ giúp bạn dễ dàng thay đổi giá trị hơn sau này mà không cần phải chỉnh sửa mã của bạn.
Cái hay của phương pháp ô trong VBA là bạn có thể xây dựng một số logic khá sáng tạo để lấy dữ liệu từ nhiều ô trên nhiều trang tính và thực hiện một số phép tính khá phức tạp với chúng.
Bạn có thể thực hiện tất cả các loại hành động trên các ô bằng cách sử dụng chức năng Cells — những việc như xóa ô, thay đổi định dạng phông chữ và hơn thế nữa.
Để khám phá mọi thứ bạn có thể làm thêm, hãy xem trang MSDN của Microsoft cho đối tượng Cells.
Định dạng ô với chức năng phạm vi
Khi lặp qua nhiều ô một lúc, chức năng của các ô là hoàn hảo. Nhưng nếu bạn muốn áp dụng một cái gì đó cho toàn bộ phạm vi ô cùng một lúc, thì hàm Phạm vi sẽ hiệu quả hơn nhiều. Trong quá trình so sánh liên tục giữa phạm vi và ô của VBA, có rất nhiều điều để mong đợi.
Một trường hợp sử dụng cho việc này có thể là định dạng một dải ô bằng cách sử dụng script nếu các điều kiện nhất định được đáp ứng.
Ví dụ: giả sử nếu tổng số doanh số bán hàng của tất cả nhân viên bán hàng vượt quá tổng số 400.000 đô la, bạn muốn đánh dấu tất cả các ô trong cột tiền thưởng bằng màu xanh lục để biểu thị rằng nhóm đã kiếm được thêm tiền thưởng cho nhóm.
Hãy xem cách bạn có thể làm điều đó với câu lệnh IF.
Private Sub Workbook_Open()
If Worksheets("Sheet1").Cells(13, 3).Value > 400000 Then
ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4
End If
End Sub
Khi điều này chạy, nếu ô vượt quá mục tiêu của đội, tất cả các ô trong phạm vi sẽ được tô màu xanh lục.
Đây chỉ là một ví dụ đơn giản về nhiều hành động bạn có thể thực hiện trên các nhóm ô bằng cách sử dụng hàm Phạm vi. Những điều khác bạn có thể làm bao gồm:
- Áp dụng một dàn ý xung quanh nhóm
- Kiểm tra chính tả của văn bản bên trong một dải ô
- Xóa, sao chép hoặc cắt các ô
- Tìm kiếm trong một phạm vi với Tìm thấy phương pháp
Đảm bảo đọc trang MSDN của Microsoft cho đối tượng Phạm vi để xem tất cả các khả năng.
Đưa Excel lên cấp độ tiếp theo
Bây giờ bạn đã hiểu sự khác biệt giữa các chức năng Ô và Phạm vi, đã đến lúc đưa tập lệnh VBA của bạn lên cấp độ tiếp theo. Bài viết của Dann về cách sử dụng các hàm Đếm và Thêm trong Excel sẽ cho phép bạn tạo các tập lệnh nâng cao hơn nữa có thể tích lũy các giá trị trên tất cả các tập dữ liệu của bạn rất nhanh chóng.
Đọc tiếp
Thông tin về các Tác giả