Bạn đang xem bài viết Cách Lập Báo Cáo Chi Tiết Tự Động Trong Excel 2022 được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Lọc các giá trị không trùng (hay lọc danh sách không trùng) là một trong những kỹ thuật thường xuyên sử dụng trong Excel. Mục đích của việc này là giúp chúng ta tạo được danh sách chọn một trong các giá trị để lập báo cáo. Một ví dụ thường thấy như sau:
Tự động lọc danh sách không trùng bằng hàm UNIQUE
Hướng dẫn cách sử dụng hàm UNIQUE tự động lọc danh sách không trùng trong Excel
Trong ví dụ trên, chúng ta sẽ viết công thức tại ô F3 như sau:
=UNIQUE(A3:A11)
Thật đơn giản phải không nào? Việc sử dụng hàm UNIQUE tiết kiệm rất nhiều thời gian và thao tác so với các phương pháp cũ trước đây.
Hướng dẫn kỹ thuật loại bỏ giá trị trùng lặp trong danh sách Excel Lọc danh sách không trùng bằng VBA Tự động lọc danh sách không trùng với Advanced Filter VBA cực kỳ đơn giản
Tạo danh sách chọn tên hàng với Data Validation – List
Data Validation là chức năng giúp thiết lập điều kiện nhập dữ liệu trong 1 ô. Trong chức năng này chúng ta có thể tạo List, tức là chọn một giá trị trong 1 danh sách cho sẵn. Cụ thể trong ví dụ trên, chúng ta cần tạo danh sách chọn các mặt hàng đã được lọc ra ở cột F.
Tại ô I4 chúng ta làm như sau:
Bước 2: Trong cửa sổ Data Validation, mục Allow chọn List
Bước 3: Trong Source (nguồn) của danh sách, chúng ta sử dụng hàm OFFSET như sau:
=OFFSET($F$3,0,0,COUNTA($F$3:$F$11))
Ý nghĩa: Lấy danh sách theo vùng từ F3:F11, bỏ qua các dòng trống.
Kết quả thu được chính là danh sách từ F3:F5
Khi dữ liệu gốc có sự thay đổi: dòng 8 thay đổi thành mặt hàng Ổi
Hàm UNIQUE(A3:A11) sẽ cho ra 4 kết quả tương ứng tại F3:F6
Data Validation/List sẽ tự động thay đổi có thêm hàng Ổi mà không cần thay đổi công thức
Hướng dẫn sử dụng Data Validation để nhập nhanh dữ liệu từ select box Tạo data validation với name động trong Excel với hàm Offset và counta Hướng dẫn cách tạo danh sách phụ thuộc nhau trong Excel bằng VBA
Tự động cập nhật kết quả báo cáo chi tiết với hàm FILTER
Trong ví dụ trên, báo cáo chi tiết gồm 3 điều kiện:
Chỉ xét những dữ liệu phát sinh từ ngày 01/08/2018
Chỉ xét những dữ liệu phát sinh đến ngày 31/08/2018
Chỉ xét những dữ liệu phát sinh với tên hàng được chọn (ví dụ chọn mặt hàng Cam)
Ba điều kiện trên xảy ra đồng thời. Do đó chúng ta có thể sử dụng hàm FILTER để xác định kết quả như sau:
Tại ô H7 nhập công thức:
Trong đó:
B3:D11 là vùng bảng dữ liệu chi tiết
“” là trả về giá trị rỗng khi không có nội dung thỏa mãn điều kiện (không có kết quả phù hợp)
Hướng dẫn cách sử dụng hàm FILTER trong Excel Office 365 Hướng dẫn cách lập báo cáo chi tiết tự động với hàm Filter trong Excel
Hướng Dẫn Cách Lập Báo Cáo Chi Tiết Theo Nhiều Điều Kiện Trên Excel
Cấu trúc của báo cáo chi tiết nhiều điều kiện
Báo cáo chi tiết thường là dạng báo cáo mô tả diễn biến, chi tiết từng lần nội dung phát sinh hoặc xảy ra. Do đó báo cáo chi tiết thường có cấu trúc như sau:
Phần điều kiện của báo cáo sẽ nằm bên trên, bên ngoài bảng nội dung chi tiết. Có thể có nhiều hơn 1 điều kiện.
Phần nội dung chi tiết sẽ nằm phía dưới. Trong bảng bao gồm tên tiêu đề của các cột dữ liệu, nội dung tương ứng theo từng cột.
Cách thiết lập vùng điều kiện trong báo cáo chi tiết
Mỗi điều kiện của báo cáo chi tiết đều gắn liền với 1 trường dữ liệu trong bảng dữ liệu gốc. Có 2 dạng cơ bản:
Dạng nhập trực tiếp giá trị: thường gắn với các dữ liệu dạng Ngày tháng, dạng Số
Dạng chọn từ 1 danh sách: thường gắn với các dữ liệu dạng Chuỗi văn bản (Text)
Do đó để đảm bảo điều kiện lập báo cáo là chính xác thì chúng ta cần thiết lập điều kiện nhập (Data validation) cho vùng điều kiện này.
Dạng nhập trực tiếp giá trị: sử dụng Data validation chỉ cho phép nhập dữ liệu dạng Ngày tháng hoặc dạng Số
Dạng chọn từ 1 danh sách: sử dụng Data validation tạo danh sách chọn để chọn 1 đối tượng
Cách thiết lập chỉ nhập dữ liệu dạng ngày tháng trong ô trên Excel Hướng dẫn sử dụng Data Validation để nhập nhanh dữ liệu từ select box
Ví dụ như sau:
Với yêu cầu như trên, vùng điều kiện của báo cáo chi tiết có thể xác định như sau:
Điều kiện 1: Từ ngày 01/05/2018
Điều kiện 2: Đến ngày 31/05/2018
Điều kiện 3: Tên mặt hàng: chọn theo danh sách tên mặt hàng
Nội dung trong báo cáo chi tiết là những thông tin trong bảng dữ liệu thỏa mãn các điều kiện lập báo cáo. Do đó chúng ta có thể kiểm tra nội dung này bằng cách sử dụng chức năng Auto Filter và tiến hành lọc thủ công trên từng trường dữ liệu.
Ví dụ: Thao tác lọc dữ liệu trong cột Ngày
Để lấy kết quả ra báo cáo, chúng ta có thể dùng 3 cách:
Cách thứ 1: Copy kết quả lọc bằng Auto Filter
Các thao tác thực hiện như sau:
Copy dữ liệu sau khi đã lọc bằng Auto Filter (bao gồm cả tiêu đề)
Bỏ chức năng Auto Filter (chọn thẻ Data rồi bấm lại vào mục Filter)
Chỉnh độ rộng cho các cột của báo cáo và hoàn thành
Cách thứ 2: Dùng hàm Logic lọc giá trị phù hợp
Hàm logic là các hàm IF, AND, OR để biện luận tìm ra giá trị phù hợp. Các giá trị không phù hợp sẽ bị loại bỏ thành ô trống
Với cách này chúng ta có thể tùy biến cấu trúc phần nội dung báo cáo: chỉ báo cáo cho 1 số cột nhất định
Ví dụ như sau:
Để lấy giá trị cột ngày, xét nếu từng nội dung ở dòng 2 thỏa mãn đồng thời cả 3 điều kiện (trong hàm AND gồm 3 điều kiện) thì lấy kết quả theo ô A2. Nếu không thỏa mãn thì trả về giá trị rỗng (ô trống)
Như vậy chỉ có 4 giá trị thỏa mãn
Các trường dữ liệu còn lại thì chúng ta chỉ cần xét: Nếu giá trị ngày của báo cáo là ô trống thì không lấy nội dung, còn có giá trị thì lấy tương ứng theo cột đang xét.
Nếu giá trị cột ngày là rỗng thì kết quả là rỗng, nếu không rỗng thì lấy giá trị bất kỳ (ví dụ là “x”)
Sau đó sử dụng Auto filter tại cột lọc này, loại bỏ các giá trị rỗng (blank) đi. Kết quả thu được là báo cáo chi tiết (không bao gồm cột lọc)
Cách thứ 3: Sử dụng Advanced Filter để lập báo cáo chi tiết
Hướng dẫn cách lập báo cáo chi tiết NXT kho bằng Advanced Filter trong Excel
Đây là cách làm khá hay, khi vùng điều kiện được kiểm soát tốt hơn là làm trực tiếp trong công thức, giúp giảm dung lượng file nhờ hạn chế công thức.
Ngoài ra việc kết hợp VBA để làm báo cáo tự động thông qua thao tác Advanced Filter cũng khá dễ dàng.
Như vậy thông qua bài viết này, chúng ta đã có thể biết cách xây dựng 1 mẫu báo cáo chi tiết theo nhiều điều kiện, và có tới 3 cách để hoàn thành báo cáo chi tiết đó.
Cách Sử Dụng Pivottable Trong Excel Để Lập Báo Cáo
Bài này sẽ hướng dẫn chi tiết về cách sử dụng pivot table trong excel, pivot table là một tiện ích tự động của excel nhằm giúp người sử dụng thực hiện nhanh cách thao tác như trích lọc dữ liệu, tạo báo cáo, thống kê…. Có thể nói một cách khác pivot table là một công cụ giúp cho chúng ta phân tích dữ liệu theo một yêu cầu cụ thể nào đó.
Dữ liệu trên thể hiện danh sách chi tiết bán hàng của một công ty bán về sản phẩm hàng rau củ quả, thông tin chi tiết của đơn hàng được mô tả ngắn ngọn như sau: một dòng thể hiện thông tin bán một sản phẩm, ví dụ Banana (chuối), sản phẩm banana thuộc một loại hàng hóa nhất định đó là vegettable (đồ rau củ quả), có một số tiền bán được nhất định (amount) cho đơn hàng này và bán vào ngày (date) ở một đất nước (country).
Bài tập thực hành số 1 về pivot table sẽ tiến hành chèn pivot table vào một sheet khác từ dữ liệu có sẵn.
– Nhấn vào biểu tượng pivot table ở menu Insert
– Một cửa sổ mới hiện lên, bạn nhấn chọn OK để thiết lập tạo một pivot table theo chế độ mặc định của excel.
Bảng pivottable hiện ra. Đầu tiên để tính tổng số tiền bán được của mỗi một sản phẩm, thì bạn làm theo các bước
– Kéo cột Product vào vùng Row Labels
– Kéo cột Amount vào vùng Values area
– Kéo cột Country vào vùng Report
Và đây là kết quả sau khi hoàn thành các bước trên.
Ngoài chức năng là tính tổng của một dòng theo một nhóm nào đó, như kết quả ở trên là tính tổng số tiền theo mỗi một sản phẩm, thì bạn có thể tùy chỉnh thành đếm số lượng hóa đơn có bán sản phẩm đó như bên dưới.
Đếm số lượng hóa đơn có chứa theo mỗi sản phẩm
– Nhấn phải chuột chọn vào giá trị đang được tính tổng Sum of Amout chọn Value Field Setting
Thay đổi cách tính toán theo yêu cầu ở tab Summarize Value by
Sau đó nhấn OK, kết quả hiện ra bên dưới sản phẩm Apple (táo) có 16 hóa đơn trên tổng số 28 hóa đơn
Nếu bạn phát hành thông tin từ website này, vui lòng ghi rõ nguồn bài viết. Xin cảm ơn. Hướng dẫn học word, excel, thủ thuật tin học văn phòng. Các dịch vụ về văn bản.
Bài hướng dẫn bạn nên tham khảo
Khóa học hữu ích bạn nên tham gia
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
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.
Tại tab Developer, các bạn chọn nút lệnh Record Macro
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
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
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ứ 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
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
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:
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 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.
Ở đâ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:
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:
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)
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.
Tải về tài liệu kèm theo bài học
Cập nhật thông tin chi tiết về Cách Lập Báo Cáo Chi Tiết Tự Động Trong Excel 2022 trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!