Trong công việc họ thường gặp gỡ phải yêu ước về lập báo cáo chi tiết để hoàn toàn có thể theo dõi được nội dung cố kỉnh thể, chỉ tương quan riêng cho tới 1 đối tượng ví dụ cần báo cáo. Mục đích để tách bóc riêng dữ liệu liên quan tới đối tượng cần báo cáo để giúp thống kê, phân tích các dữ liệu đó. Kỹ thuật lập báo cáo chi máu cũng là một kỹ thuật cao, đòi hỏi các yếu tố về:
Trình bày báo cáo khoa học, đẹp mắt trong excelCách để lấy nội dung báo cáo từ dữ liệu ban đầu ở bảng kê theo đúng điều kiện lập báo cáoSau đây họ cùng tò mò về 1 phương pháp giúp vấn đề lập báo cáo chi máu trở nên rất dễ dàng và thuận tiệnthông qua lấy ví dụ như sau:
Chúng ta có 1 bảng kê thông tin bán sản phẩm của từng sản phẩm theo các ngày (Bảng A2:E12), trong đó đã được thu xếp theo sản phẩm công nghệ tự cột Tên mặt hàng.
Bạn đang xem: Tạo báo cáo động trong excel
Xem nhanh
Cách thực hiện
Yêu cầu
Lập report chi tiết mang đến từng món đồ (vùng G1:J12), biết rằng khi thay đổi tên hàng thì báo cáo sẽ tự động thay đổi câu chữ tương ứng với phương diện hàng đó.
Cách thực hiện
Bước 1: Tạo danh sách chọn tên phương diện hàng
Ở bảng kê bán sản phẩm có 3 sản phẩm là sản phẩm A, hàng B, mặt hàng C. Do đó chúng ta cũng có thể tạo list chọn tên món đồ tại ô H2 như sau:
Chọn tab Data / Data Validation
Dù đã tất cả phần mềm, nhưng kỹ năng Excel vẫn rất là quan trọng cùng với kế toán, bạn đã vững vàng Excel chưa? Hãy để tôi giúp bạn, đk khoá học Excel:
Trong bảng Data Validation:
Mục Allow bọn họ chọn ListMục Data nhập văn bản như sau: mặt hàng A, mặt hàng B, mặt hàng CBấm ok
Khi đó chúng ta tại ô H2 chúng ta có thể chọn tên sản phẩm theo danh sách gồm 3 sản phẩm này.
Bước 2: Công thức lấy tài liệu vào báo cáo
Căn cứ vào vị trí tên mặt hàng xác định được ở trên, lấy những nội dung gồm: Ngày, tên nhân viên, số lượng, thành tiền tương ứng theo khía cạnh hàng đó => áp dụng hàm OFFSET
Bởi vì kết quả trả về là 1 trong những mảng dữ liệu nên chúng ta cần sử dụng tới công thức mảng.
Xem thêm: Tình Khúc Bạch Dương
Khi viết phương pháp mảng, họ chọn toàn cục vùng vẫn thể hiện công dụng (G5:J12), sau đó nhập cách làm và kết thúc công thức này họ sẽ dùng tổng hợp phím Ctrl + Shift + Enter
Cấu trúc hàm OFFSET gồm:
=OFFSET(reference, rows, cols,
+) Height (số dòng): bằng số dòng có Tên sản phẩm tại H2 trùng với cột Tên hàng => áp dụng hàm COUNTIF để đếm số lần mở ra tên mặt hàng này
+) Weight (số cột): ở đây report chi tiết đề xuất 4 cột, do đó rước 4 cột tương ứng theo tên sản phẩm này.
Trong đó:
Hàm Index + Match được viết như sau:INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0))
Hàm COUNTIF được viết như sau:COUNTIF($A$3:$A$12,$H$2)
Công thức hoàn hảo là:
=OFFSET(INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0)),0,1,COUNTIF($A$3:$A$12,$H$2),4)
Chúng ta có hiệu quả như sau:
Bước 3: Cách bả lỗi
Khi áp dụng công thức OFFSET đến vùng bảng G5:J12, tên sản phẩm là hàng A, bọn họ thu được 4 dòng hiệu quả tương ứng theo bảng kê. Do vậy là hiệu quả đã đúng.
Nhưng vùng bảng G5:J12 gồm tới 8 dòng. Vậy hồ hết dòng thừa sẽ lộ diện lỗi #N/A
Để bả lỗi này, không để lỗi hiển thị thì bọn họ làm như sau:
Sử dụng công dụng Conditional Formatting:
Chọn tổng thể bảng tính trường đoản cú G5:J12Trong tab Home > lựa chọn Conditional formatting > chọn New RuleMục Select a Rule Type chọn Format only cells that conainMục Format only cells with chọn ErrorsChúng ta sẽ thiết lập định dạng cho các ô bao gồm chứa lỗi trong vùng bảng này trong mục Format > lựa chọn màu chữ là color trắng
Bấm ok và họ sẽ thu được công dụng như sau:
Những điều đề xuất lưu ý
Do hàm OFFSET đem ra tác dụng là 1 mảng ngay thức thì nhau, do đó dữ liệu trong bảng kê (phần nguyên liệu để lập báo cáo) đã phải được sắp xếp theo vật dụng tự theo tên hàngCác cột hiệu quả trong report chi tiết đang lấy tương ứng theo địa chỉ trong bảng kê tương ứng theo địa chỉ cột thương hiệu hàng. Do đó tùy thuộc vào vị trí các vùng dữ liệu cần đem trong báo cáo chi ngày tiết mà bạn cũng có thể thiết lập những tham số: Row, Cols, Height, Weight vào hàm offset mang lại phù hợp. Để tiện lợi hơn khi thao tác này thì bọn chúng ta đặt các cột công dụng liền kề nhau, tránh câu hỏi cột điều kiện lập report (tên hàng) nằm xen giữa các cột kết quả.Tải về tệp tin mẫu: http://bit.ly/2IhmiYq
Video hướng dẫn chi tiết
Hướng dẫn phương pháp lập report tổng hợp tự động với Excel và VBA
Nếu bạn muốn bài viết liên quan các kỹ năng và kiến thức khác về Excel, hãy thâm nhập ngay khóa học Excel tự cơ bản tới cải thiện dành mang lại người đi làm. Đây là 1 trong những khóa học tập rất đầy đủ kiến thức và kỹ năng và hỗ trợ rất giỏi vời cho bạn trong câu hỏi làm thân quen với Excel, sử dụng những công cụ, những hàm trong Excel để giúp bạn thao tác trên Excel được giỏi hơn, hiệu quả hơn. Chi tiết xem tại: