Home-theater-designers
Excel rất mạnh mẽ. Nếu bạn sử dụng nó nhiều, bạn có thể đã biết nhiều thủ thuật sử dụng công thức hoặc tự động định dạng, nhưng tận dụng Tế bào và Phạm vi trong VBA, bạn có thể nâng cao phân tích Excel của mình lên một cấp độ hoàn toàn mới.
Vấn đề với việc sử dụng các hàm Cells và Range trong 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ác hàm này thực sự hoạt động như thế nào. 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.
Chức năng của tế bào
Các chức năng Ô và Phạm vi cho phép bạn nói tập lệnh VBA của bạn 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.
Tế bào 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 một nhóm ô cùng một lúc. Định dạng cho chức năng này là Ô (hàng, cột) .
Điều này tham chiếu đến mọi ô trong toàn bộ trang tính. Đó là một ví dụ trong đó hàm Cells không tham chiếu đến một ô duy nhất:
Worksheets('Sheet1').CellsĐiều này tham chiếu đến ô thứ ba từ bên trái, của hàng trên cùng. Ô C1:
Worksheets('Sheet1').Cells(3)Đoạn mã sau tham chiếu đến ô D15:
Worksheets('Sheet1').Cells(15,4)Nếu bạn muốn, bạn cũng có thể tham chiếu ô D15 với 'Cells (15,' D ')' --- bạn được phép sử dụng ký tự cột.
Có rất nhiều sự linh hoạt trong việc có thể tham chiếu một ô bằng cách sử dụng một số cho cột và ô, đặc biệt là với các tập lệnh có thể vòng qua một số lượng lớn các ô (và thực hiện các phép tính trên chúng) rất nhanh chóng. Chúng tôi sẽ tìm hiểu điều đó một cách chi tiết hơn bên dưới.
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 Ô, bởi vì nó cho phép bạn tham chiếu đến 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 lại một hàm Range, vì các tham chiếu cho các ô không phải là số (trừ khi bạn nhúng hàm Cell vào bên trong nó).
Định dạng cho chức năng này là Phạm vi (Ô # 1, Ô # 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ụ.
cách tải xuống các video được bảo vệ từ bất kỳ trang web nào
Ở đây, hàm phạm vi đang tham chiếu đến ô 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 cần 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 Ô 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
Đoạn mã trên tham chiếu đến cùng một phạm vi với hàm Range ('A1: E20'). 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à Phạm vi, 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 ô
Hàm Cells hữu ích nhất khi bạn có một công thức phức tạp mà bạn muốn thực hiện 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 Sheet2 là nơi bạn 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ẽ hoạt động.
Một giải pháp thay thế cho điều này là tạo tập lệnh VBA mà bạn có thể kích hoạt để chạy bất cứ khi nào bạn mở trang tính hoặc được kích hoạt bởi một nút lệnh trên trang tính để bạn có thể kiểm soát khi 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ừ 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 của tế bào đang hoạt động
Nếu bạn chưa bao giờ viết VBA trong Excel trước đây, bạn sẽ cần bật mục menu Nhà phát triển. Để 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, 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, chúng tôi sẽ làm cho tập lệnh chạy mỗi khi mở sổ làm việc. Để 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ể vượt qua tất cả 11 nhân viên và với hàm Cells đưa vào ba biến cần thiết cho phép tính.
Hãy nhớ hàm Cells có hàng và cột là các tham số để xác định từng ô riêng lẻ. Chúng tôi sẽ đặt 'x' thành hàng, 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 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.
- (Tổng doanh số / 50) x 0,4
- (Khối lượng bán hàng / 50.000) x 0,5
- (Điểm phản hồi / 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 phầ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.
cách xem instagram mà không cần tài khoản
Nếu bạn muốn cột Tiền thưởng hiển thị tiền thưởng đô la thực tế thay vì 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 hàm Cell 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 khác nhau và thực hiện một số tính toán khá phức tạp với họ.
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
Để lặp qua nhiều ô một lúc, chức năng Cells 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.
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 tập lệnh, 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 một 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 SubKhi đ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 bằng phương pháp 'Tìm'
- Nhiều hơn nữa
Hãy chắc chắn rằng bạn đã đọ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 Cells và Range, đã đế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 xây dựng 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.
Và nếu bạn mới bắt đầu với VBA trong Excel, đừng quên chúng tôi có một hướng dẫn giới thiệu về Excel VBA tất cả những điều tốt nhất cho bạn.
Đăng lại Đăng lại tiếng riu ríu E-mail Canon so với Nikon: Thương hiệu máy ảnh nào tốt hơn?Canon và Nikon là hai tên tuổi lớn nhất trong ngành công nghiệp máy ảnh. Nhưng thương hiệu nào cung cấp dòng máy ảnh và ống kính tốt hơn?
Đọc tiếp Chủ đề liên quan- Năng suất
- Lập trình
- Lập trình Visual Basic
- Microsoft Excel
Ryan có bằng Cử nhân Kỹ thuật Điện. Anh ấy đã làm việc 13 năm trong lĩnh vực kỹ thuật tự động hóa, 5 năm trong lĩnh vực CNTT và hiện là Kỹ sư ứng dụng. Từng là Biên tập viên quản lý của MakeUseOf, ông đã phát biểu tại các hội nghị quốc gia về Trực quan hóa Dữ liệu và đã được giới thiệu trên đài truyền hình và đài phát thanh quốc gia.
Xem thêm từ Ryan DubeTheo dõi bản tin của chúng tôi
Tham gia bản tin của chúng tôi để biết các mẹo công nghệ, đánh giá, sách điện tử miễn phí và các ưu đãi độc quyền!
Bấm vào đây để đăng ký