• Trang chủ
  • Giới thiệu
    • Học gia sư kế toán tại Hà Nội
  • Excel
  • Kế toán
  • Kinh nghỉệm
  • DAX
  • VBA
  • Văn bản
  • Access
  • Liên hệ
    • Chính sách bảo mật
Thứ Hai, Tháng Hai 6, 2023
  • Login
No Result
View All Result
NEWSLETTER
Học gia sư kế toán
  • Trang chủ
  • Giới thiệu
    • Học gia sư kế toán tại Hà Nội
  • Excel
  • Kế toán
  • Kinh nghỉệm
  • DAX
  • VBA
  • Văn bản
  • Access
  • Liên hệ
    • Chính sách bảo mật
  • Trang chủ
  • Giới thiệu
    • Học gia sư kế toán tại Hà Nội
  • Excel
  • Kế toán
  • Kinh nghỉệm
  • DAX
  • VBA
  • Văn bản
  • Access
  • Liên hệ
    • Chính sách bảo mật
No Result
View All Result
Học Gia sư Kế toán
No Result
View All Result

Hướng dẫn cách lọc dữ liệu vào Báo cáo chi tiết trong Excel bằng VBA

by My Lê
24/10/2022
in VBA
0
Hướng dẫn cách lọc dữ liệu vào Báo cáo chi tiết trong Excel bằng VBA

Originally posted on 12/02/2022 @ 21:39

Hướng dẫn cách lọc dữ liệu vào Báo cáo chi tiết trong Excel bằng VBA

  • Chuyên mục:
  • Excel cơ bản∙Excel nâng cao∙Excel VBA∙Kế toán∙VBA

Trong công việc, bạn có từng gặp phải những câu hỏi này không:

  • Lập báo cáo chi tiết như thế nào?
  • Làm thế nào để lấy dữ liệu từ một bảng theo những yêu cầu nhất định?
  • Cách trích xuất dữ liệu theo điều kiện để đưa sang 1 bảng khác?

Bài viết sau đây sẽ giúp bạn trả lời những câu hỏi đó. Đồng thời bạn cũng có thể khám phá ra một cách làm mới rất hay khi sử dụng VBA vào việc lọc dữ liệu vào Báo cáo chi tiết.

* Chúng ta có yêu cầu sau: Dựa vào bảng dưới đây, hãy lấy dữ liệu ở Bảng chi tiết dựa theo điều kiện thay đổi ở các ô từ I2:I4 (Thời gian và nhà cung cấp)

pic2

Bước 1: Ghi macro các thao tác

Tại tab Developer, các bạn chọn nút lệnh Record Macro

pic3

  • Thao tác 1: Chọn chức năng Data / Filter cho bảng dữ liệu
  • Thao tác 2: Lọc cột Nhà cung cấp theo tên Nhà cung cấp ở ô I4
  • Thao tác 3: Lọc ngày ở cột Ngày theo thông tin ngày ở ô I2 và I3

Video record macro loc du lieu

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:


EX101 92019 nho


VBA101 92019 nho

EX101 92019 nho

Hướng dẫn học Excel cơ bản

Bước 2: Đọc nội dung macro

Mở cửa sổ VBA, chúng ta xem nội dung Macro vừa ghi được

pic4

Range(“A2:F2”).Select
Selection.AutoFilter

Nội dung này là : Chọn vùng ô từ A2 đến F2, mở chức năng Auto filter

ActiveSheet.Range(“$A$2:$F$47″).AutoFilter Field:=2, Criteria1:=”Anh Tu?n”

Nội dung này là: Lọc dữ liệu ở cột thứ 2 (cột NCC), điều kiện lọc là “Anh Tuấn” => VBA không hỗ trợ tiếng việt đầy đủ nên có dấu ?

ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=1, Criteria1:= _
“>=01/10/2017″, Operator:=xlAnd, Criteria2:=”<=31/10/2017”

Nội dung này là: Lọc dữ liệu ở cột thứ 1 (cột Ngày), điều kiện lọc là Lớn hơn hoặc bằng ngày 01/10/2017, và nhỏ hơn hoặc bằng 31/10/2017

Đừng bỏ lỡ: lớp học Excel kế toán với các chuyên gia

Bước 3: Tinh gọn macro để sử dụng

Cần thay đổi điều kiện lọc trong VBA để liên kết tới vùng điều kiện trong Bảng chi tiết => Khi đó thay đổi dữ liệu trong Bảng chi tiết thì macro sẽ tự động lọc theo nội dung đó.

Chúng ta chú ý vào các vùng điều kiện (Criteria1, Criteria2) ở trong câu lệnh trong VBA

* Câu lệnh lọc NCC

ActiveSheet.Range(“$A$2:$F$47″).AutoFilter Field:=2, Criteria1:=”Anh Tu?n”

Thay “Anh Tu?n” bằng ô I4 (dòng 4, cột 9, sheet 1) trong sheet1 như sau:

ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=2, Criteria1:=Sheet1.Cells(4, 9).value

* Câu lệnh lọc Ngày

ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=1, Criteria1:= _
“>=01/10/2017″, Operator:=xlAnd, Criteria2:=”<=31/10/2017”

Thay các mục “>=01/10/2017” và “<=31/10/2017” như sau:

ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=1, Criteria1:= _
“>=” & CLng(Sheet1.Cells(2, 9).value), Operator:=xlAnd, Criteria2:=”<=” & CLng(Sheet1.Cells(3, 9).value)

Vì giá trị ngày tháng trong Excel về bản chất là dạng số nên có thể chuyển đổi về dạng CLng(…)

* Tinh gọn và hoàn thiện Code VBA

Range(“A2:F2”).Select       +    Selection.AutoFilter   = Range(“A2:F2”).AutoFilter

Range(“B2”).Select  Dòng này có thể bỏ đi

Bước 4: Copy dữ liệu và paste sang bảng chi tiết và hoàn thiện code

Từ bảng dữ liệu đã được lọc, chúng ta copy toàn bộ kết quả đã lọc được rồi dán vào Bảng chi tiết để lấy kết quả.

Code cho bước này như sau: (Các bạn có thể record macro cho thao tác này rồi chọn lọc code)

Vì bảng chi tiết chỉ cần lấy nội dung Tên hàng, số lượng, đơn giá, thành tiền nên nội dung sẽ lấy từ cột C tới cột F, bắt đầu từ ô C3 tới F47 (cuối bảng). Chỉ copy những giá trị xuất hiện sau khi lọc

ActiveSheet.Range(“$C$3:$F$47”).SpecialCells(xlVisible).Copy

Paste dữ liệu: Dán vào Bảng chi tiết, bắt đầu từ ô H6, chỉ dán dữ liệu dạng Value (giá trị)

Range(“H6”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sau khi Paste dữ liệu xong, chúng ta sẽ bỏ lệnh Copy và Filter đi bằng 2 dòng lệnh:

Application.CutCopyMode = False   ‘Hủy bỏ chế độ Cut Copy trong excel (khi bạn Cut/Copy thì Excel sẽ lưu nội dung đó trong bộ nhớ, và tô đường viền nhấp nháy ở nội dung đó. Khi không dùng đến bạn có thể bỏ đi)

Range(“A2:F2”).AutoFilter  ‘Hủy bỏ chế độ Filter. Lặp lại thao tác này để hủy bỏ chế độ filter khi không dùng đến nữa (trả về trạng thái ban đầu khi chưa dùng Filter)

Bước 5: Kiểm tra code bằng phím F8

Sau khi hoàn thành xong code trong VBA, chúng ta bấm nút F8 để kiểm tra xem code đó hoạt động ra sao

Nếu code hoạt động đúng thì sẽ ra kết quả như sau:

pic5

Bước 6: Gán macro vào sự kiện thay đổi điều kiện ở vùng ô I2:I4

Trong cửa sổ VBA, bạn double click vào Sheet1

pic6

Trong cửa sổ VBA làm việc với Sheet1, chọn sự kiện Change (thay đổi nội dung). Khi chúng ta thay đổi nội dung trong sheet này thì sẽ có điều gì xảy ra.

pic7

Ở đây chúng ta quan tâm tới sự thay đổi dữ liệu ở ô I2:I4 mới làm ảnh hưởng tới báo cáo chi tiết. Do đó chúng ta chỉ xét sự thay đổi ở vùng này.

Thao tác như sau:

pic8

Dòng lệnh “If Not Application.Intersect(Range(“I2:I4”), Range(Target.Address)) Is Nothing Then” được hiểu là: Nếu có sự thay đổi dữ liệu ở trong vùng I2:I4 xảy ra thì…

Khi thay đổi dữ liệu ở vùng I2:I4 thì chúng ta muốn cập nhật nội dung của báo cáo chi tiết. Do đó chúng ta sẽ gọi ra Macro vừa hoàn thành ở phần trên.

* Bổ sung:

Do mỗi điều kiện sẽ cho kết quả nhiều / ít khác nhau, do đó để có thể xác định rõ kết quả của Bảng chi tiết chỉ đúng với điều kiện được chọn, chúng ta cần làm sạch vùng Bảng chi tiết trước khi dán dữ liệu vào.

Đặt dòng Code xóa dữ liệu lên đầu Macro:

pic9

range(“H6:K100”).ClearContents   là làm sạch dữ liệu trong vùng H6:K100 (là vùng kết quả dữ liệu của bảng chi tiết)

Kết  luận

Những nội dung học được qua bài này là:

  • Cách Record macro và tinh gọn code từ thao tác Record
  • Cách đọc hiểu code trong VBA
  • Cách gán Macro vào sự kiện xảy ra trong Sheet (ví dụ với sự kiện thay đổi một số nội dung trong sheet)
  • Trình tự logic của câu lệnh trong VBA

Trong công việc chúng ta sẽ gặp phải những việc này rất nhiều, được ứng dụng nhiều trong thực tế.

Bài viết này sẽ tạo tiền đề cho các bạn làm quen với VBA, cách học VBA dễ dàng và làm quen dần với kỹ thuật VBA giúp tự động hóa khi sử dụng Excel.

Cảm ơn các bạn đã theo dõi.

Xem ngay: khóa học lập trình VBA trong excel

Tải về tài liệu kèm theo bài học

 


Tác giả: duongquan211287

·
·
·
Bài viết khác của cùng tác giả

  • Tất cả
  • Python
  • SQL
  • VBA

3.000.000VND 1.800.000VND
PYXL101
Xem chi tiết

3.000.000VND 1.600.000VND
SQL100
Xem chi tiết

499.000VND 199.000VND
PY100
Xem chi tiết

Bài viết liên quan

Tìm chuỗi gần giống trong Excel với Levenshtein distance

Cách tính lịch trả nợ với dư nợ giảm dần trong Excel

Cách sử dụng hàm PMT, tính toán số tiền trả góp trong Excel

Cách sử dụng hàm Lambda trong Excel để viết hàm tự tạo

Bài viết tập trung tài liệu từ kênh YouTube Học Gia Sư Kế Toán

Tạo công cụ theo dõi Portfolio tiền điện tử với Excel và Power Query


Khóa học liên quan

Khóa học Tự động hóa Excel với lập trình VBA cho người mới bắt đầu
Chi tiết khóa học

Khóa học Lập trình VBA nâng cao trong Excel
Chi tiết khóa học

  

© Học Gia Sư Kế Toán. All rights reserved.

My Lê

My Lê

My Lê tên thật là Mê Ly đã có 10 năm kinh nghiệm trong ngành kế toán.

Next Post
Hướng dẫn về tài khoản 352 – Dự phòng phải trả theo Thông tư 200

Hướng dẫn về tài khoản 352 – Dự phòng phải trả theo Thông tư 200

Recommended

Thiết lập lệnh in tự động trong Excel với VBA

Thiết lập lệnh in tự động trong Excel với VBA

3 tháng ago
Công chức nhà nước học lập trình VBA để không tụt hậu trong Cuộc cách mạng Công nghệ 4.0

Công chức nhà nước học lập trình VBA để không tụt hậu trong Cuộc cách mạng Công nghệ 4.0

4 tháng ago

Tin mới nhất

    Fanpage

    • Liên hệ
    • Giới thiệu
    • Đặt quảng cáo
    Liên hệ chúng tôi qua email : hocgiasuketoan.com@gmail.com

    © 2022 Hocgiasuketoan Premium magazine by Hocgiasuketoan

    No Result
    View All Result
    • Trang chủ
    • Giới thiệu
      • Học gia sư kế toán tại Hà Nội
    • Excel
    • Kế toán
    • Kinh nghỉệm
    • DAX
    • VBA
    • Văn bản
    • Access
    • Liên hệ
      • Chính sách bảo mật

    © 2022 Hocgiasuketoan Premium magazine by Hocgiasuketoan

    Welcome Back!

    Login to your account below

    Forgotten Password?

    Retrieve your password

    Please enter your username or email address to reset your password.

    Log In