Chúng ta hay hay gặp gỡ phải một số trong những yêu ước về lập báo cáo trên Excel như: báo cáo chi tiết cho món đồ A vào thời điểm tháng 5, báo cáo chi tiết cho nhân viên X phần tử Y… và với đông đảo yêu ước này thì chúng ta xử lý như thế nào? Hãy thuộc Học Excel Online mày mò về cách lập báo cáo chi huyết theo nhiều điều kiện trên Excel nhé. Bạn đang xem: Cách làm báo cáo trên excel
Cấu trúc của report chi tiết nhiều điều kiện
Báo cáo chi tiết thường là dạng report mô tả diễn biến, cụ thể từng lần ngôn từ phát sinh hoặc xảy ra. Do đó report chi tiết hay có cấu trúc như sau:
Phần đk của report sẽ nằm cạnh sát trên, bên ngoài bảng nội dung bỏ ra tiết. Gồm thể có nhiều hơn 1 điểm kiện.
Phần nội dung cụ thể sẽ ở 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 xứng theo từng cột.
Cách thiết lập vùng đk trong report chi tiết
Mỗi điều kiện của report chi tiết đều gắn liền với 1 trường dữ liệu trong bảng tài 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 dạng (Text)
Do đó để bảo vệ điều khiếu nại lập báo cáo là đúng chuẩn thì chúng ta cần thiết lập cấu hình điều khiếu nại nhập (Data validation) mang đến vùng đk này.
Dạng nhập trực tiếp giá trị: sử dụng Data validation chỉ có thể chấp nhận được nhập dữ liệu dạng ngày tháng hoặc dạng SốDạng chọn từ một danh sách: sử dụng Data validation tạo list chọn để chọn một đối tượngCách thiết lập cấu hình 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ằm nhập nhanh dữ liệu từ select box
Ví dụ như sau:
Dựa vào bảng dữ liệu trên lập report chi máu theo tên mặt hàng trong thời gian từ ngày 01/5 cho ngày 31/5
Với yêu ước như trên, vùng đk của report chi tiết rất có thể xác định như sau:
Cách khẳng định nội dung trong báo cáo chi tiết
Nội dung trong báo cáo chi máu là những thông tin trong bảng dữ liệu thỏa mãn nhu cầu các điều kiện lập báo cáo. Bởi đó bạn có thể kiểm tra câu chữ này bằng phương pháp sử dụng chức năng tự động hóa Filter và tiến hành lọc bằng tay thủ công trên từng trường dữ liệu.
Ví dụ: thao tác làm việc lọc tài liệu trong cột Ngày
Kết quả sau thời điểm lọc bằng tự động hóa Filter tại 2 cột Ngày với Tên phương diện hàng
Như vậy nội dung cần report chính là hiệu quả lọc từ bỏ bảng dữ liệu. Mặc dù nhiên thao tác làm việc lọc bằng Auto
Filter chỉ giúp chúng ta kiểm tra kết quả báo cáo, chứ không phải đây là báo cáo.
Để lấy hiệu quả ra báo cáo, bạn có thể dùng 3 cách:
Cách sản phẩm công nghệ 1: Copy kết quả lọc bằng auto Filter
Các thao tác làm việc thực hiện tại như sau:
Copy dữ liệu sau khoản thời gian đã lọc bằng tự động hóa Filter (bao có 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)Đặt con chuột tại ô nên dán kết quả, lựa chọn Paste Special > Value để dán phần giá trịTiếp tục chọn Paste Special > Format cần dán phần định dạng
Chỉnh độ rộng cho các cột của report và trả thành
Cách này tương đối thủ công, phải thực hiện qua nhiều làm việc tay, ko tùy biến chuyển được phần nội dung report nên không khuyến khích áp dụng. Mặc dù nhiên trong những trường hợp túng và chưa tồn tại cách làm sao hay hơn nữa thì đây cũng là một cách cho ra công dụng đúng.
Cách thiết bị 2: cần sử dụng hàm súc tích lọc cực hiếm phù hợp
Hàm ngắn gọn xúc tích là các hàm IF, AND, OR nhằm biện luận tìm ra quý hiếm phù hợp. Những giá trị không cân xứng sẽ bị nockout bỏ thành ô trống
Với cách này bạn có thể tùy biến cấu tạo phần ngôn từ báo cáo: chỉ báo cáo cho một số ít cột tuyệt nhất định
Ví dụ như sau:
Trong cột Ngày:
Để lấy cực hiếm cột ngày, xét nếu từng ngôn từ ở loại 2 thỏa mãn đồng thời cả 3 đk (trong hàm và gồm 3 điều kiện) thì lấy kết quả theo ô A2. Giả dụ không vừa lòng thì trả về quý hiếm rỗng (ô trống)
Như vậy chỉ tất cả 4 cực hiếm thỏa mãn
Các trường dữ liệu còn sót lại thì bọn họ chỉ yêu cầu xét: Nếu giá trị ngày của báo cáo là ô trống thì không rước nội dung, còn tồn tại giá trị thì lấy khớp ứng theo cột vẫn xét.
Để hoàn toàn có thể loại bỏ các dòng trống xen kẽ, bọn họ sẽ tạo thêm 1 cột phụ nhằm lọc vứt dòng trống với hàm logic:
Nếu giá trị cột ngày là trống rỗng thì kết quả là rỗng, nếu không rỗng thì mang giá trị bất kỳ (ví dụ là “x”)
Sau kia sử dụng tự động hóa filter tại cột thanh lọc này, vứt bỏ các cực hiếm rỗng (blank) đi. Công dụng thu được là report chi máu (không bao gồm cột lọc)
Cách này giảm bớt được thao tác làm việc tay lúc lập báo cáo. Tuy nhiên vẫn còn thao tác làm việc tay khi lọc trên cột phụ và xuất hiện thêm cột phụ
Cách trang bị 3: áp dụng Advanced Filter nhằm lập report chi tiết
Cách này các bạn có thể xem tại bài xích viết:
Hướng dẫn bí quyết lập báo cáo chi tiết NXT kho bằng Advanced Filter trong Excel
Đây là phương pháp làm hơi hay, khi vùng điều kiện được điều hành và kiểm soát tốt hơn là làm cho trực tiếp trong công thức, giúp giảm dung lượng file nhờ tiêu giảm công thức.
Ngoài ra việc kết hợp VBA để làm báo cáo tự động hóa thông qua thao tác Advanced Filter cũng rất dễ dàng.
Như vậy thông qua bài viết này, bọn họ đã hoàn toàn có thể biết bí quyết xây dựng 1 mẫu báo cáo chi tiết theo rất nhiều điều kiện, và gồm tới 3 cách để hoàn thành báo cáo chi tiết đó.
Những kỹ năng này chúng ta đều rất có thể học được trong khóa đào tạo và huấn luyện Excel trường đoản cú cơ bạn dạng tới siêng gia của học Excel Online. Trong khóa học chúng ta còn học thêm được không ít kiến thức khác liên quan tới những hàm, những công thay trong Excel để ứng dụng vào các bước được tốt hơn. Thông tin cụ thể mời bạn nhấp chuột hình mặt dưới:
Một giữa những yêu mong thường gặp trong Excel kia là các bạn phải có tác dụng báo cáo. Ngoại trừ nhu cầu báo cáo tổng hợp, trong tương đối nhiều trường hợp bọn họ còn cần phải trích lọc một trong những nội dung cụ thể từ bảng dữ liệu. Đây chính là quá trình chế tác ra report chi tiết. Vậy họ hãy cùng khám phá xem report chi máu là gì? và bởi sao nó lại quan trọng?
Trong bài viết này ova.edu.vn sẽ hướng dẫn chúng ta cách làm report chi ngày tiết trên Excel trường đoản cú A cho Z.
XEM cấp tốc MỤC LỤC
Khái niệm về report chi tiết
Báo cáo cụ thể là quá trình trích lọc phần đa thông tin chi tiết từ bảng dữ liệu ra report theo một vài tiêu chí độc nhất vô nhị định. Tác dụng của báo cáo chi tiết thông thường có sẵn trong bảng dữ liệu, bọn họ chỉ bài toán trích lọc các nội dung đó ra cơ mà thôi.
Trong thực tế có không ít trường hợp report yêu mong vừa tổng hợp, vừa chi tiết trên cùng 1 chủng loại báo cáo. Khi đó bọn họ sẽ cần xác minh rõ trong tía cục report phần như thế nào là tổng hợp, phần như thế nào là chi tiết để có cách thức làm phù hợp.
Để làm được report chi tiết, bọn họ cần tất cả các năng lực sau:
Xây dựng bố cục tổng quan báo cáo: gồm những thành phần và các tính năng của báo cáoĐịnh dạng, trình bày report cho đẹp
Sử dụng nghệ thuật trích thanh lọc (hoặc đối chiếu) phần lớn dữ liệu vừa lòng điều kiện.Kiểm tra tính đúng chuẩn của báo cáo
Tối ưu báo cáo: auto cập nhật report mỗi khi thay đổi điều kiện.
Bố viên của báo cáo chi máu trên Excel
Báo cáo chi tiết thường có bố cục như sau:
Trong đó:
(1): thương hiệu cơ quan, chỗ làm việc có thể kèm theo logo
(2): tên của báo cáo
(3): Điều kiện của báo cáo
(4): Phần tổng số (tổng hợp) của báo cáo
(5): mẫu tiêu đề của bảng báo cáo
(6): những dòng nội dung đưa ra tiết
(7): người ký duyệt câu chữ báo cáo
Báo cáo cụ thể có đặc điểm kết cấu gần tương tự với bảng dữ liệu tiêu chuẩn (gồm phần 5 và 6). Vày đó để gia công được report này phải bảng tài liệu gốc phải đúng cấu trúc tiêu chuẩn này.
Quy trình xây dựng báo cáo chi tiết trên Excel
Quy trình thực hiện report chi tiết được thực hiện qua công việc sau:
Bước 1: khẳng định loại báo cáo
Khi nhận ra yêu mong làm báo cáo, việc thứ nhất bạn đề xuất làm là xác định xem report đó là dạng tổng thích hợp hay chi tiết. Hãy chăm chú đặc điểm là hiệu quả cần biểu thị trong báo cáo có sẵn trong bảng tài liệu không (hay có thể nói là rất có thể trích lọc từ bảng dữ liệu ra được không). Nếu câu trả lời là bao gồm thì report bạn buộc phải làm sẽ sở hữu phần bỏ ra tiết.
Bước 2: khẳng định phần đk và mẫu tiêu đề mang lại báo cáo
Dựa bên trên yêu cầu của báo cáo, chúng ta sẽ phân làm cho 2 yếu tắc chính:
Phần điều kiện của báo cáo: phần này tương ứng với đông đảo cột làm sao của bảng dữ liệu. Đây đang là căn cứ để thanh lọc theo những điều kiện.Phần tiêu chí báo cáo: phần này là đều nội dung cụ thể cần diễn tả trên báo cáo. Số đông nội dung đã đưa vào điều kiện của báo cáo thì thường sẽ không còn cần lặp lại (xuất hiện tại lại) bên trên phần tiêu chuẩn báo cáo.Ví dụ tiếp sau đây thể hiện cách xác định các thành phần của báo cáo:
Phần điều kiện (mục 3 trong tía cục) với phần dòng tiêu chí của báo cáo (mục 5 trong ba cục) là hai thành phần đặc trưng nhất trong báo cáo chi tiết. Các bạn cần xác định rõ 2 nguyên tố này trước, kế tiếp mới thêm các thành phần không giống theo cấu tạo báo cáo.
Bước 3: Lựa chọn cách làm trích lọc kết quả
Có nhiều phương pháp để giúp chúng ta thu được tác dụng cho báo cáo chi tiết. Trong số đó có 3 cách thông dụng như sau:
Cách 1: Copy Paste thủ côngCách này đó là việc các bạn sẽ thực hiện làm việc Lọc + trích xuất dữ liệu hoàn toàn thủ công bằng tay, với các thao tác làm việc lọc trên bộ lọc Filter của Excel và làm việc copy paste dữ liệu. Cụ thể như sau:
Bước 1: Lọc bởi Filter vào bảng dữ liệu theo những điều kiện của báo cáo.Bước 2: Copy các kết quả còn lại trong bảng dữ liệu theo từng cột.Bước 3: Dán công dụng vào vùng tiêu chí báo cáo theo từng cột. Tái diễn đến hết những cột vào bảng báo cáo (Các cột ngay thức thì nhau có thể copy + paste bình thường 1 lượt)Cách 2: thực hiện công thức xúc tích trong báo cáoCách này là việc bạn sẽ biện luận lô ghích để tìm hồ hết giá trị trong bảng tài liệu mà phù hợp với điều kiện của báo cáo. Rõ ràng như sau:
Bước 1: chọn 1 cột làm mốc để đặt công thức lô ghích (lựa lựa chọn cột có không thiếu nội dung độc nhất so với các cột khác)
Bước 2: Công thức ngắn gọn xúc tích tại cột làm mốc tất cả dạng:
Nếu thỏa mãn toàn bộ các điều kiện thì rước theo ngôn từ trong cột có tác dụng mốc (chú ý xác định mối quan hệ giới tính giữa những điều kiện là dạng and hay OR khi xét những điều kiện):
=IF(AND(Điều khiếu nại 1=..., Điều kiện 2=...), Ô kết quả, "")
Thực hiện so sánh điều kiện với ô đầu tiên trong các cột tương ứng; Ô hiệu quả là ô trước tiên trong cột chứa công dụng tương ứng
Bước 3: các cột công dụng khác trong report sử dụng công thức xúc tích và ngắn gọn dạng:
Nếu cột mốc có dữ liệu thì cột khác cũng có dữ liệu
=IF(Ô kết quả cột mốc = "", "", Ô kết quả cột tương ứng)
Bước 4: Sao chép kết quả từ dòng trước tiên tới dòng cuối báo cáo
Nguyên tắc: Tổng số chiếc trong report chi tiết = tổng số dòng trong bảng dữ liệu
Bước 5: Lọc vứt những dòng không có nội dung (kết quả không phù hợp) bởi Auto
Filter
Đây là cỗ lọc gồm sẵn trong Excel. Bộ lọc nâng cấp cho phép chúng ta cũng có thể vừa lọc, vừa trích xuất (hay gọi tắt là trích lọc) trong cùng 1 thao tác. Cách tiến hành Advanced Filter như sau:
Bước 1: bạn cần xác định vùng điều kiện cho Advanced Filter. Vùng điều kiện này có điểm lưu ý khác so với vùng điều kiện thường thì của báo cáo:
Cách tổ chức đk dạng và (AND)
Các đk nằm trên cùng 1 hàng
Nếu các điều khiếu nại nằm trên thuộc 1 cột, tiêu đề cột đó yêu cầu được lặp lại
Cách tổ chức điều kiện được miêu tả qua hình mặt dưới:
Cách tổ chức điều kiện dạng hoặc (OR):Các đk nằm khác mặt hàng (nếu khác tiêu đề thì sẽ đồng thời khác cột)
Bước 2: khẳng định vùng kết quả trích lọc. Đây đó là việc khẳng định dòng tiêu đề của report chi tiết. Ví như tên title của báo cáo không khớp với title trên bảng dữ liệu thì không sử dụng Advanced Filter được. Bạn có thể tạo 1 loại làm title phụ bên dưới dòng tiêu đề báo cáo để khớp với tên tiêu đề của bảng dữ liệu. Chiếc này để giúp đỡ bạn cần sử dụng được chức năng Advanced Filter.
Bước 3: Sử dụng Advanced Filter.
Tại vị trí chứa report (Báo cáo để tại Sheet nào thì các bạn chọn Sheet đó), rồi gọi Advanced Filter bằng cách chọn thẻ Data > Advanced…
Sau đó lộ diện cửa sổ Advanced Filter như sau:
Mục chất lượng records only: chỉ lựa chọn khi bạn muốn loại bỏ những quý giá trùng lặp.Bước 4: Bấm OK để hoàn vớ thao tác. Advanced Filter sẽ tự động trích thanh lọc các tác dụng ra báo cáo.
Lưu ý
Cả 3 bí quyết trên mọi yêu cầu có những thao tác thủ công. Mỗi khi biến đổi điều khiếu nại báo cáo, muốn update lại nội dung báo cáo theo đk mới, các bạn sẽ cần tiến hành lại thao tác làm việc đó. Như vậy bạn sẽ phải lặp đi lặp lại 1 thao tác.
Để khắc phục và hạn chế điều này, bạn sẽ có thể kết hợp với việc sử dụng Macro trong Excel như sau:
Bước 1: chọn thẻ View trên thanh công cụ, tiếp đến chọn tiếp mục Macro > Record Macro
Nếu phiên phiên bản Excel của người tiêu dùng không có tác dụng Macro bên trên thẻ View, bạn vui lòng bài viết liên quan tại bài bác viết:
Hướng dẫn biện pháp mở thẻ Developer bên trên thanh dụng cụ của Excel
Bước 2: Thực hiện làm việc trích lọc dữ liệu (theo một trong 3 biện pháp trên, chú ý nên chọn lựa cách 3)
Bước 3: Bấm Stop Recording để ngừng quá trình ghi Macro
Bước 4: Vẽ 1 hình khối (Shape) bất kỳ trên Sheet để báo cáo
Bước 5: Gán Macro vào hình khối nhằm khi nhấp chuột hình khối này thì macro sẽ auto chạy lại thao tác bạn vừa thực hiện.
Vì văn bản này tương quan nhiều tới thao tác, kỹ thuật sử dụng nên rất khó để giúp đỡ bạn hiểu rõ chỉ trải qua lời nói. Vậy nên các bạn vui lòng xem thêm trong khóa đào tạo Excel EXG01 của ova.edu.vn sẽ được hướng dẫn chi tiết các làm việc này.
Kết luận
Báo cáo chi tiết nặng về tính kỹ thuật: lọc, trích xuất, thực hiện macro… Hãy luyện tập nhiều kỹ thuật để làm một cách chính xác nhất, hạn chế lỗi sai tương tự như các thao tác làm việc thừa. Do những không nên sót hay thao tác thừa khi Record Macro cũng trở nên lặp lại mỗi lúc bạn cập nhật báo cáo.Bạn cần tham khảo thêm về VBA nhằm tăng khả năng tự động hóa hóa khi làm việc trên Excel. VBA rất cần thiết cho toàn thể quá trình có tác dụng việc của người tiêu dùng trên Excel, không riêng gì trong quy trình làm báo cáo.VBA là gì? học VBA có khó không? tìm khóa huấn luyện VBA ở đâu cho phù hợp? tất cả những thắc mắc của công ty đều sẽ kiếm được câu vấn đáp trong khóa đào tạo VBAG01 của ova.edu.vn.
Khóa học tập lập trình VBA vào Excel - tuyệt đỉnh công phu VBA - Viết code trong khoảng tay
Đây là khóa huấn luyện hướng dẫn rất chi tiết và không thiếu thốn các kỹ năng về VBA để bạn có thể ứng dụng ngay lập tức vào trong các bước một biện pháp hiệu quả. Hiện tại ova.edu.vn đang có không ít ưu đãi hấp dẫn cho chính mình khi tham gia khóa đào tạo này.
