Cách thao tác dữ liệu trong Excel bằng VBA

Microsoft Excel là một công cụ mạnh mẽ có thể được sử dụng để thao tác dữ liệu. Để tận dụng tối đa phần mềm, bạn cần sử dụng VBA. Visual Basic for Application, hay VBA, cung cấp cho người dùng Excel khả năng tạo macro, là các chức năng tùy chỉnh tiết kiệm thời gian mạnh mẽ để thao tác và phân tích dữ liệu.

Các macro xử lý mã VBA để quản lý các tập dữ liệu lớn, nếu không sẽ mất rất nhiều thời gian để sửa đổi. Ví dụ: với VBA, bạn có thể tạo một macro để tự động định dạng các trường nhất định đáp ứng các tiêu chí đã xác định của bạn.

Dưới đây là một ví dụ về tập lệnh VBA được sử dụng trong Excel:

Sub ConfigureLogic ()
Dim qstEntries
Dim dqstEntries
Dim qstCnt, dqstCnt
qstEntries = Phạm vi ("QualifiedEntry"). Đếm
qst = qstEntries - WorksheetFunction.Count If (Phạm vi ("QualifiedEntry"), "")
ReDim QualifiedEntryText (qst)
'MsgBox (qst)
dqstEntries = Phạm vi ("DisQualifiedEntry"). Đếm
dqst = dqstEntries - WorksheetFunction.Count If (Phạm vi ("DisQualifiedEntry"), "")
ReDim bị loại bỏEntryText (dqst)
'MsgBox (dqst)
Đối với qstCnt = 1 đến qst
QualifiedEntryText (qstCnt) = ThisWorkbook.Worksheet ("Qualifier"). Phạm vi ("J" & 8 + qstCnt) .value
'MsgBox (QualifiedEntryText (qstCnt))
ghi nhật ký ("Mục nhập đủ tiêu chuẩn được định cấu hình #" & qstCnt & "là {" & QualifiedEntryText (qstCnt) & "}")
Kế tiếp
Đối với dqstCnt = 1 đến dqst
DisqualifiedEntryText (dqstCnt) = ThisWorkbook.Worksheet ("Vòng loại"). Phạm vi ("M" & 8 + dqstCnt) .value
'MsgBox (Không đủ tiêu chuẩnEntryText (dqstCnt))
ghi nhật ký ("Cấu hình mục nhập DisQualified #" & qstCnt & "là {" & DisqualifiedEntryText (dqstCnt) & "}")
Kế tiếp
includeEntry = ThisWorkbook.Worksheet ("Vòng loại"). Phạm vi ("Bao gồm cả anh chị em"). value
'MsgBox (bao gồmEntry)
ghi nhật ký ("Entrys bao gồm trong tìm kiếm -" & includeEntry)
Kết thúc phụ

Cách phân tích và thao tác các mục trong bảng tính

Để sử dụng VBA để phân tích dữ liệu, bạn sẽ cần kiểm tra cài đặt trong Excel cho công cụ Nhà phát triển. Để tìm thấy nó, định vị Dải băng Excel và tìm kiếm tab Nhà phát triển . Nếu nó không được hiển thị, bạn sẽ cần kích hoạt nó trong menu Cài đặt Excel.

Tiếp theo, tạo một bảng tính mới và đặt tên là "Vòng loại." Chúng tôi sẽ sử dụng bảng này để kiểm tra tất cả những điều đủ điều kiện lựa chọn.

Tiếp theo, thiết lập vòng loại trên trang tính theo mã. Nó phải được nhập bằng tay; cắt và dán sẽ không hoạt động.

ThisWorkbook.Worksheet ("Vòng loại"). Phạm vi ("J" & 8 + qstCnt) .value

Cách định vị phạm vi và xây dựng một mảng

Phạm vi trong chức năng trên là ô J9. Hàm phạm vi ghi chú 8; tuy nhiên, phạm vi thực tế là 9 vì:

 Đối với qstCnt = 1 đến qst 

Câu lệnh trên bắt đầu từ 1, không phải 0. Do đó, danh sách bắt đầu từ 9. Trong trường hợp này, ghi chú (qstCnt = 1).

Để tạo một mảng trong số các mục trên bảng tính Qualifier, hãy đặt các từ ngẫu nhiên trong các ô J9 - J13. Khi các hàng được hoàn thành, chúng ta có thể tiến lên phía trước với việc tìm kiếm và thao tác dữ liệu trong Excel.

 CountSheets riêng ()
Bảng tính Dim
Dim WS dưới dạng trang tính
số đếm = 0
ghi nhật ký ("***** Bắt đầu chà ******")
Đối với mỗi WS trong ThisWorkbook.Worksheet
sheetcount = sheetcount + 1
Nếu WS.Name = "Đã chọn" thì
'cần phải ghi ngày và thời gian vào bảng có tên "Ghi nhật ký"
ActionCnt = ActionCnt + 1
ghi nhật ký ("Bảng gọi:" & WS.Name)
bảng tính (tờ)
Khác
ActionCnt = ActionCnt + 1
ghi nhật ký ("Bỏ qua trang tính:" & WS.Name)
Kết thúc nếu
WS tiếp theo
'MsgBox ("kết thúc")
ActionCnt = ActionCnt + 1
đăng nhập ("**** Chà DONG!")
Ứng dụng.ScreenUpdating = True
Kết thúc phụ 

Có một ví dụ về một bộ đếm tab làm việc.

Bảng tính Dim
Dim WS dưới dạng trang tính
số đếm = 0
ghi nhật ký ("***** Bắt đầu chà ******")
Đối với mỗi WS trong ThisWorkbook.Worksheet
sheetcount = sheetcount + 1 

Sau khi khởi tạo số lượng trang tính, đặt thành 0 để khởi động lại bộ đếm.

Logging () là một chương trình con khác theo dõi tất cả các hành động để kiểm tra các lựa chọn.

Vòng lặp For tiếp theo thiết lập Workbook hoạt động để đếm. WS là khởi tạo và ThisWorkbook. Bảng tính là tab hoạt động trong cuốn sách. Vì chúng tôi chưa đặt tên cho sổ làm việc, mô-đun này sẽ chạy trên bất kỳ sổ làm việc đang hoạt động. Nếu bạn đang làm việc trên nhiều sổ làm việc và kích hoạt sai một sổ làm việc, nó sẽ cố chạy trên nó. Để tránh lỗi, hãy thận trọng để đặt tên cho sổ làm việc cụ thể của bạn hoặc chỉ làm việc trên một tại một thời điểm.

Mỗi khi vòng lặp kích hoạt, nó sẽ thêm một biến vào số trang tính để theo dõi số lượng tab. Sau đó, chúng tôi chuyển đến:

 Nếu WS.Name = "Đã chọn" thì
'cần phải ghi ngày và thời gian vào bảng có tên "Ghi nhật ký"
ActionCnt = ActionCnt + 1
ghi nhật ký ("Bảng gọi:" & WS.Name)
bảng tính (tờ)
Khác
ActionCnt = ActionCnt + 1
ghi nhật ký ("Bỏ qua trang tính:" & WS.Name)
Kết thúc nếu 

Ở đây, chúng tôi tìm kiếm tab đã chọn.

Nếu WS biến bằng với Đã chọn, thì chúng ta sẽ ghi nhật ký và kích hoạt Bảng tính chương trình con. Nếu WS biến không bằng với Đã chọn, nó được ghi lại rằng trang tính đó đã bị bỏ qua và hành động được tính. Đoạn mã trên là một ví dụ về cách đếm số lượng và định vị một tab cụ thể.

Listng sau đây là tất cả các phương thức khác nhau có thể được sử dụng để thao tác dữ liệu!

Chúc vui vẻ!

Cách đếm số trang tính trong sổ làm việc

 Dim TAB
Đối với mỗi TAB trong ThisWorkbook.Worksheet
'một số thói quen ở đây
kế tiếp 

Tìm hàng, cột hoặc ô cuối cùng trên trang tính

 Số lượng tế bào
cellcount = Cells (ThisWorkbook.Worksheet ("sheet"). Rows.Count, 1) .End (xlUp) .Row 

Lọc bằng cách sử dụng tiêu chí nâng cao

 Phạm vi ("A2: Z99"). Sắp xếp khóa1: = Phạm vi ("A5"), order1: = xlAsceinating, Header: = xlNo 

Áp dụng Thuộc tính tự động phù hợp cho Cột

 Cột ("A: A"). EntireColumn.AutoFit 

Nhận giá trị từ bảng tính khác

 giá trị mờ
newvalue = ThisWorkbook.Worksheet ("bảng tính"). Phạm vi ("F1"). value 

Chèn một cột vào một bảng tính

 Hàng Dim, Cột
Các ô (Hàng, Cột) .EntireColumn. Chọn
Lựa chọn.Insert 

Chèn nhiều cột vào bảng tính

 Dim insertCnt
Hàng Dim, Cột
Đối với insertCnt = 1 đến N
ThisWorkbook.Worksheet ("bảng tính"). Chọn
Các ô (Hàng, Cột) .EntireColumn. Chọn
Lựa chọn.Insert
Kế tiếp 

Thêm Phạm vi được Đặt tên vào Trang tính Đặc biệt

 ThisWorkbook.Worksheet ("sheet"). Name.Add Name: = "Status", RefersToR1C1: = "= sheet! C2"

Chèn một hàng toàn bộ vào một bảng tính

 Hàng Dim, Cột
Các ô (Hàng, Cột) .EntireRow. Chọn
Lựa chọn.Insert 

Sao chép toàn bộ hàng để dán

 ActiveSheet.Range ("A1"). EntireRow.Select
Lựa chọn. Bản sao 

Xóa một hàng toàn bộ

 ActiveSheet.Range ("A1"). EntireRow.Select
Lựa chọn. Xóa 

Chọn một tờ đặc biệt

 ThisWorkbook.Worksheet ("bảng tính"). Chọn 

So sánh giá trị của một phạm vi

 Thứ nhất
Log Logestest
Logictest = "một số từ hoặc giá trị"
Nếu (Phạm vi (đầu tiên) .value = Logictest) thì
'một số thói quen ở đây
Kết thúc nếu

Hình: © Microsoft.