Ví dụ báo cáo Excel. Cách tạo một báo cáo chuyên nghiệp trong Excel
Giao diện Bảng Pivot đầu tiên, còn được gọi là Báo cáo Pivot, được đưa vào Excel vào năm 1993 ( Các phiên bản Excel 5.0). Mặc dù có nhiều hữu ích chức năng, nó thực tế không được sử dụng trong công việc bởi đa số Người dùng Excel. Thậm chí người dùng có kinh nghiệm Thuật ngữ “báo cáo tóm tắt” thường có nghĩa là một cái gì đó được xây dựng bằng các công thức phức tạp. Hãy cố gắng phổ biến việc sử dụng bảng tổng hợp trong công việc hàng ngày của các nhà kinh tế. Bài viết thảo luận cơ sở lý thuyết tạo báo cáo tóm tắt được đưa ra khuyến nghị thiết thực về việc sử dụng chúng và cũng cung cấp ví dụ về cách truy cập dữ liệu dựa trên một số bảng.
Thuật ngữ phân tích dữ liệu đa biến
Hầu hết các nhà kinh tế đều đã nghe đến các thuật ngữ “dữ liệu đa chiều”, “khối ảo”, “công nghệ OLAP”, v.v. Nhưng một cuộc trò chuyện chi tiết thường khiến hầu hết mọi người không biết họ đang nói về điều gì. Chúng ta đang nói về. Nghĩa là, mọi người muốn nói đến một điều gì đó phức tạp và thường không liên quan đến hoạt động hàng ngày của họ. Trên thực tế, điều này là không đúng sự thật.
Dữ liệu, đo lường đa chiều
Có thể nói rằng các nhà kinh tế học hầu như liên tục gặp phải dữ liệu đa chiều, nhưng hãy cố gắng trình bày chúng dưới dạng định trước bằng cách sử dụng bảng tính. Tính đa chiều ở đây có nghĩa là khả năng nhập, xem hoặc phân tích cùng một thông tin có thay đổi vẻ bề ngoài, sử dụng các nhóm và sắp xếp dữ liệu khác nhau. Ví dụ: một kế hoạch bán hàng có thể được phân tích bằng các tiêu chí sau:
- loại, nhóm hàng hóa;
- nhãn hiệu hoặc chủng loại sản phẩm;
- kỳ (tháng, quý, năm);
- người mua hoặc nhóm người mua;
- khu vực bán hàng
- và như thế.
Mỗi tiêu chí trên được gọi là một “chiều” trong phân tích dữ liệu đa chiều. Chúng ta có thể nói rằng một phép đo mô tả thông tin theo một bộ nhất định các giá trị. Một loại thước đo đặc biệt của thông tin đa chiều là “dữ liệu”. Trong ví dụ của chúng tôi, dữ liệu kế hoạch bán hàng có thể là:
- khối lượng bán hàng;
- Gia ban;
- giảm giá cá nhân
- và như thế.
Về mặt lý thuyết, dữ liệu cũng có thể được đo lường tiêu chuẩn thông tin đa chiều (ví dụ: bạn có thể nhóm dữ liệu theo giá bán), nhưng thông thường dữ liệu vẫn loại đặc biệt các giá trị.
Như vậy, có thể nói rằng ở công việc thực tế Các nhà kinh tế sử dụng hai loại thông tin: dữ liệu đa chiều ( số thực tế và số kế hoạch có nhiều đặc điểm) và sách tham khảo (đặc điểm hoặc dữ liệu đo lường).
OLAP
Viết tắt OLAP (trực tuyến xử lý phân tích) được dịch theo nghĩa đen có vẻ như “ xử lý phân tích trong thời gian thực". Định nghĩa này không cụ thể lắm; hầu hết mọi báo cáo về bất kỳ sản phẩm phần mềm nào đều có thể được tóm tắt theo nó. Qua ý nghĩa của OLAP ngụ ý một công nghệ để làm việc với các báo cáo đặc biệt, bao gồm cả phần mềm, để thu thập và phân tích dữ liệu có cấu trúc đa chiều. Một trong những sản phẩm phần mềm phổ biến triển khai công nghệ OLAP là Máy chủ SQL Máy chủ phân tích. Thậm chí có người còn lầm tưởng ông là người đại diện duy nhất triển khai phần mềm Khái niệm này.
Khối dữ liệu ảo
"Khối ảo" (khối đa chiều, khối OLAP) là một thuật ngữ đặc biệt được đề xuất bởi một số nhà cung cấp chuyên ngành phần mềm. Các hệ thống OLAP thường chuẩn bị và lưu trữ dữ liệu trong cấu trúc riêng của chúng và các giao diện phân tích đặc biệt (chẳng hạn như báo cáo tóm tắt Excel) truy cập dữ liệu trong các khối ảo này. Hơn nữa, việc sử dụng kho lưu trữ chuyên dụng như vậy hoàn toàn không cần thiết để xử lý thông tin đa chiều. TRONG trường hợp chung, khối ảo– đây là mảng dữ liệu đa chiều được tối ưu hóa đặc biệt dùng để tạo báo cáo tóm tắt. Nó có thể được lấy thông qua phần mềm chuyên dụng hoặc thông qua truy cập đơn giản vào các bảng cơ sở dữ liệu hoặc bất kỳ nguồn nào khác, chẳng hạn như bảng tính Excel.
Bảng tổng hợp
"Báo cáo tổng hợp" ( bảng tổng hợp, Pivot Table) là giao diện người dùng dùng để hiển thị dữ liệu đa chiều. Bằng cách sử dụng của giao diện này bạn có thể nhóm, sắp xếp, lọc và sắp xếp lại dữ liệu để thu được các mẫu phân tích khác nhau. Báo cáo đang được cập nhật bằng những cách đơn giản giao diện người dùng, dữ liệu được tự động tổng hợp theo các quy tắc được chỉ định mà không yêu cầu nhập thêm hoặc lặp lại bất kỳ thông tin nào. Giao diện tổng hợp bảng excel có lẽ là phổ biến nhất sản phẩm phần mềmđể làm việc với dữ liệu đa chiều. Nó hỗ trợ cả nguồn dữ liệu bên ngoài (khối OLAP và cơ sở dữ liệu quan hệ) và phạm vi bảng tính nội bộ dưới dạng nguồn dữ liệu. Bắt đầu từ phiên bản 2000 (9.0), Excel cũng hỗ trợ dạng đồ họa hiển thị dữ liệu đa chiều - Pivot Chart.
Giao diện PivotTable của Excel cho phép bạn sắp xếp các kích thước của dữ liệu đa chiều trong một vùng trang tính. Để đơn giản, bạn có thể coi bảng tổng hợp như một báo cáo nằm trên một phạm vi ô (trên thực tế, có một ràng buộc nhất định về định dạng ô với các trường của bảng tổng hợp). PivotTable Excel có bốn vùng để hiển thị thông tin: bộ lọc, cột, hàng và dữ liệu. Kích thước dữ liệu được gọi Các trường của bảng tổng hợp. Các trường này có thuộc tính và định dạng hiển thị riêng.
Một lần nữa, tôi muốn chỉ ra rằng bảng tổng hợp Excel chỉ nhằm mục đích phân tích dữ liệu mà không có khả năng chỉnh sửa thông tin. Một ý nghĩa gần gũi hơn sẽ là việc sử dụng rộng rãi thuật ngữ “báo cáo tổng hợp” và đây chính xác là giao diện này được gọi cho đến năm 2000. Nhưng vì lý do nào đó, trong các phiên bản tiếp theo, các nhà phát triển đã bỏ nó.
Chỉnh sửa PivotTable
Theo định nghĩa của nó, về nguyên tắc, công nghệ OLAP không ngụ ý khả năng thay đổi dữ liệu nguồn khi làm việc với các báo cáo. Tuy nhiên, cả một giai cấp đã hình thành trên thị trường hệ thống phần mềm, nhận ra khả năng của cả phân tích và chỉnh sửa trực tiếp dữ liệu trong bảng nhiều chiều. Về cơ bản, những hệ thống như vậy tập trung vào việc giải quyết các vấn đề về ngân sách.
Sử dụng các công cụ tích hợp Tự động hóa Excel, bạn có thể giải được nhiều bài toán không chuẩn. Bạn có thể tìm thấy ví dụ về cách triển khai chỉnh sửa cho các bảng tổng hợp Excel dựa trên dữ liệu bảng tính trên trang web của chúng tôi.
Chuẩn bị dữ liệu đa chiều
Hãy đến với ứng dụng thực tế Bảng tổng hợp. Hãy thử phân tích dữ liệu bán hàng theo nhiều hướng khác nhau. Tài liệu Pivottableexample.xls bao gồm một số tờ. Tờ giấy Ví dụ chứa thông tin cơ bản về doanh số bán hàng cho Thời kỳ nhất định. Để đơn giản hóa ví dụ, chúng tôi sẽ phân tích một chỉ báo số duy nhất – khối lượng bán hàng tính bằng kg. Các thứ nguyên dữ liệu chính là: sản phẩm, khách hàng và nhà cung cấp dịch vụ ( công ty vận chuyển). Ngoài ra, có một số thứ nguyên dữ liệu bổ sung là đặc điểm của sản phẩm: loại, nhãn hiệu, danh mục, nhà cung cấp và khách hàng: loại. Dữ liệu này được thu thập trên trang Danh mục. Trong thực tế, có thể có nhiều phép đo như vậy hơn.
Tờ giấy Ví dụ chứa biện pháp khắc phục tiêu chuẩn phân tích dữ liệu - bộ lọc tự động. Nhìn vào ví dụ điền vào bảng, rõ ràng dữ liệu bán hàng theo ngày (chúng được sắp xếp theo cột) phù hợp với việc phân tích thông thường. Ngoài ra, bằng cách sử dụng bộ lọc tự động, bạn có thể thử tóm tắt dữ liệu dựa trên sự kết hợp của một hoặc nhiều tiêu chí chính. Hoàn toàn không có thông tin về thương hiệu, chủng loại và chủng loại. Không có cách nào để nhóm dữ liệu bằng tính năng tổng hợp tự động theo một khóa cụ thể (ví dụ: theo khách hàng). Ngoài ra, tập hợp ngày là cố định và sẽ không thể xem thông tin tóm tắt trong một khoảng thời gian nhất định, chẳng hạn như 3 ngày, bằng các phương tiện tự động.
Nói chung, có một vị trí ngày được xác định trước trong trong ví dụ này – nhược điểm chính những cái bàn. Bằng cách sắp xếp ngày theo cột, chúng tôi đã xác định trước kích thước của bảng này, do đó tước đi cơ hội sử dụng phân tích bằng bảng tổng hợp.
Đầu tiên, chúng ta cần loại bỏ khuyết điểm này - tức là. xóa vị trí được xác định trước của một trong các thứ nguyên dữ liệu nguồn. Ví dụ về bảng - sheet đúng Việc bán hàng.
Bảng có dạng nhật ký nhập thông tin. Ở đây, ngày là một chiều dữ liệu bằng nhau. Cũng cần lưu ý rằng để phân tích tiếp theo trong các bảng tổng hợp, vị trí tương đối của các hàng so với nhau (nói cách khác là sắp xếp) là hoàn toàn không quan trọng. Hồ sơ trong Cơ sở dữ liệu quan hệ dữ liệu. Giao diện của bảng tổng hợp chủ yếu nhằm mục đích phân tích khối lượng lớn cơ sở dữ liệu. Do đó, bạn phải tuân thủ các quy tắc này khi làm việc với nguồn dữ liệu ở dạng phạm vi ô. Đồng thời, không ai cấm sử dụng các công cụ giao diện Excel trong công việc của bạn - bảng trụ chỉ phân tích dữ liệu và việc định dạng, bộ lọc, nhóm và sắp xếp các ô nguồn có thể tùy ý.
Từ bộ lọc tự động đến báo cáo tóm tắt
Về mặt lý thuyết, có thể thực hiện phân tích theo ba chiều bằng cách sử dụng dữ liệu từ bảng Bán hàng: hàng hóa, khách hàng và nhà vận chuyển. Dữ liệu về đặc tính sản phẩm và người mua trên tờ này bị thiếu, do đó, sẽ không cho phép chúng hiển thị trong bảng tóm tắt. TRONG chế độ bình thường tạo bảng tổng hợp cho nguồn Dữ liệu Excel không cho phép bạn liên kết dữ liệu từ nhiều bảng bằng các trường nhất định. Bạn có thể vượt qua giới hạn này phần mềm– xem phần bổ sung ví dụ cho bài viết này trên trang web của chúng tôi. Để không phải dùng đến phương pháp phần mềm xử lý thông tin (đặc biệt vì chúng không phổ biến), cần thêm vào đặc điểm bổ sung trực tiếp vào biểu mẫu nhập nhật ký - xem bảng Phân tích bán hàng.
Việc sử dụng hàm VLOOKUP giúp dễ dàng bổ sung các đặc điểm còn thiếu vào dữ liệu gốc. Bây giờ, bằng cách sử dụng Bộ lọc Tự động, bạn có thể phân tích dữ liệu trong kích thước khác nhau. Nhưng vấn đề về nhóm vẫn chưa được giải quyết. Ví dụ: chỉ theo dõi số lượng theo thương hiệu trong một số ngày nhất định là khá khó khăn. Nếu bạn giới hạn bản thân Công thức Excel, thì bạn cần xây dựng các mẫu bổ sung bằng hàm SUMIF.
Bây giờ hãy xem giao diện bảng trụ cung cấp những khả năng gì. Trên một tờ giấy Tóm tắtPhân tíchđã xây dựng một số báo cáo dựa trên một phạm vi ô có dữ liệu trang tính Phân tích bán hàng.
Bảng phân tích đầu tiên được xây dựng bằng cách sử dụng Giao diện Excel 2007 Ribbon\Chèn\PivotTable(trong trình đơn Excel 2000-2003 Dữ liệu\PivotTable).
Bảng thứ hai và thứ ba được tạo thông qua việc sao chép và cấu hình tiếp theo. Nguồn dữ liệu cho tất cả các bảng đều giống nhau. Bạn có thể kiểm tra điều này bằng cách thay đổi dữ liệu nguồn, sau đó bạn cần cập nhật dữ liệu báo cáo tóm tắt.
Theo quan điểm của chúng tôi, những lợi thế trong khả năng hiển thị thông tin là rõ ràng. Bạn có thể hoán đổi các bộ lọc, cột và hàng cũng như ẩn các nhóm giá trị nhất định thuộc bất kỳ thứ nguyên nào, sử dụng tính năng kéo và thả thủ công cũng như sắp xếp tự động.
Thuộc tính và định dạng
Ngoài việc hiển thị trực tiếp dữ liệu, còn có rất nhiều tùy chọn để hiển thị hình thức của bảng tổng hợp. Bạn có thể ẩn dữ liệu không cần thiết bằng cách sử dụng bộ lọc. Đối với một thành phần hoặc trường đơn lẻ, việc sử dụng mục menu ngữ cảnh sẽ dễ dàng hơn Xóa bỏ(trong phiên bản 2000-2003 Trốn).
Bạn cũng nên đặt hiển thị các thành phần khác của bảng tổng hợp không phải thông qua định dạng ô mà thông qua việc đặt trường hoặc thành phần của bảng tổng hợp. Để thực hiện việc này, bạn cần di chuyển con trỏ chuột đến phần tử mong muốn, đợi hình thức đặc biệt con trỏ (ở dạng mũi tên), sau đó nhấp một lần để chọn phần tử đã chọn. Sau khi được chọn, bạn có thể thay đổi chế độ xem qua dải băng, danh mục hoặc gọi hộp thoại định dạng ô tiêu chuẩn:
Ngoài ra, Excel 2007 còn giới thiệu nhiều kiểu hiển thị PivotTable được xác định trước:
Lưu ý rằng các bộ lọc điều khiển và vùng kéo đang hoạt động trong biểu đồ.
Truy cập dữ liệu bên ngoài
Như đã lưu ý, có lẽ hiệu quả lớn nhất từ việc sử dụng bảng tổng hợp có thể đạt được khi truy cập dữ liệu nguồn lực bên ngoài– Khối OLAP và truy vấn cơ sở dữ liệu. Những nguồn như vậy thường lưu trữ một lượng lớn thông tin và cũng có cấu trúc quan hệ, giúp dễ dàng xác định thứ nguyên của dữ liệu đa chiều (các trường bảng tổng hợp).
Excel hỗ trợ nhiều loại nguồn dữ liệu ngoài:
Hiệu quả lớn nhất từ việc sử dụng các nguồn thông tin bên ngoài có thể đạt được bằng cách sử dụng các công cụ tự động hóa ( chương trình VBA) cả để thu thập dữ liệu và xử lý trước dữ liệu trong bảng tổng hợp.
Bạn sẽ học được gì khi xem video này?
Bạn sẽ có thể sử dụng bảng tổng hợp và báo cáo chúng trong Excel.
Mô tả video:
Hãy xem một ví dụ. Chúng tôi có nó trong Excel cái bàn lớn, trong đó mỗi hàng thể hiện một giao dịch, bán hàng hoặc giao hàng. Bảng này cũng chứa thông tin về người quản lý bán hàng chịu trách nhiệm về giao dịch này và tất nhiên cả số lượng lô hàng.
Nhiệm vụ: xây dựng một báo cáo cuối cùng hàng năm bằng cách sử dụng bảng này, từ đó có thể thấy rõ hàng hóa nào và ở thành phố nào, với số lượng bao nhiêu và chính xác là chúng tôi đã bán cho ai.
Hãy tạo một bảng động (thông minh) ngay lập tức. Để thực hiện việc này, hãy nhấp vào bất kỳ ô nào trong bảng, sau đó chuyển đến tab, đưa ra lệnh “Định dạng dưới dạng bảng”, chọn bất kỳ kiểu nào và nhấp vào “Ok” trong cửa sổ mở ra.
Sau khi chuyển đổi bảng, tab "Nhà thiết kế" xuất hiện và trong nhóm đầu tiên "Thuộc tính", "Tên bảng" xuất hiện, bạn có thể thay đổi tùy ý. Theo mặc định, “Bảng 1” được viết ở đó. Đó là tên sẽ được sử dụng để xây dựng báo cáo PivotTable. Hãy đặt tên cho bảng là "Sales".
Bây giờ hãy chuyển đến tab và sử dụng lệnh “Bảng tổng hợp”, chúng ta sẽ bắt đầu tạo bảng tổng hợp. Cửa sổ “Tạo bảng tổng hợp” xuất hiện.
Bạn cần đánh dấu vào ô "Bật lá mới"để bắt đầu tạo phần tử bạn cần trên một trang tính mới. Nhấp vào “Ok” và bạn sẽ được đưa đến một cái mới Tờ giấy trắngđã có sẵn với cấu trúc hiện có. Nửa bên trái sẽ chứa cấu trúc của bảng mà chúng ta cần và nửa bên phải sẽ chứa danh sách các trường của bảng. Trong danh sách này, bạn có thể tìm ra tất cả các tiêu đề từ tiêu đề của bảng chính. Bên dưới có 4 khu vực tượng trưng cho 4 yếu tố chính.
Tất cả của chúng tôi công việc tiếp theo là kéo và thả đơn giản trường chuột từ danh sách vào 4 vùng này. Bảng tổng hợp sẽ được tạo tự động. Hãy bắt đầu nào.
Nhấn và giữ trường “Tên” và kéo nó vào khu vực “Tên dòng”.
Chú ý những gì xảy ra. Việc xây dựng bảng bắt đầu trong bảng tính.
Bây giờ hãy kéo trường “Thành phố” vào khu vực “Tên cột”.
Trường “Số tiền” được chuyển hướng đến khu vực “Giá trị”.
Kéo trường “Khách hàng” vào “Bộ lọc báo cáo”.
Đã thực hiện những điều này các bước đơn giản, chúng ta nhận được một bảng tổng hợp gần như đã hoàn thành. Để sắp xếp tất cả các giá trị trong bảng, bạn cần nhấp chuột phải vào bất kỳ giá trị nào trong bảng và nhấp vào “Trường định dạng số”, cho phép bạn chỉ định ngay định dạng cho tất cả các số trong bảng. Trong cửa sổ xuất hiện, bạn cần chọn “Định dạng số”. Đặt số 0 vào cột “Số vị trí thập phân”. Hãy biểu thị "Dấu tách nhóm bit". Nhấp vào “Được”. Như bạn có thể thấy, toàn bộ bảng trụ ngay lập tức có diện mạo đẹp đẽ.
Để thiết kế báo cáo hoàn thiện, hãy sử dụng tab “Nhà thiết kế” và chọn bất kỳ kiểu thiết kế nào. Và để thể hiện trực quan các con số, bạn có thể sử dụng biểu đồ. Để thực hiện việc này, hãy chọn một số số và chuyển đến tab “Trang chủ”, sử dụng nút “Định dạng có điều kiện” và chọn
Để phân tích lớn và bảng phức tạp thường sử dụng . Các công thức cũng có thể được sử dụng để nhóm và phân tích dữ liệu hiện có. Hãy tạo các báo cáo đơn giản bằng cách sử dụng các công thức.
Chúng tôi sẽ sử dụng ( Chèn/Bảng/Bảng), chứa thông tin về doanh số của lô sản phẩm. Các hàng của bảng cung cấp dữ liệu về việc phân phối một lô sản phẩm và doanh số bán của nó. Một bảng tương tự đã được sử dụng trong bài viết .
Bảng có các cột sau:
- Sản phẩm– tên lô hàng, ví dụ: “ Những quả cam»;
- Nhóm– nhóm sản phẩm, ví dụ: “ Những quả cam» là thành viên của nhóm « trái cây»;
- ngày giao hàng– Ngày giao hàng của Nhà cung cấp;
- Khu vực bán hàng– Khu vực bán lô hàng;
- Việc bán hàng– Giá thành có thể bán được lô hàng;
- Việc bán hàng– thời gian bán Hàng hóa thực tế trong Khu vực (tính bằng ngày);
- Lợi nhuận– một ghi chú cho biết lô hàng đã bán có tạo ra lợi nhuận hay không.
Bởi vì Người quản lý tên Hãy điều chỉnh các bảng thành " bảng nguồn"(xem tệp ví dụ).
Bằng cách sử dụng các công thức, chúng ta sẽ tạo 5 báo cáo đơn giản, chúng ta sẽ đặt chúng trên các trang riêng biệt.
Báo cáo số 1 Tổng doanh số sản phẩm
Hãy tìm tổng doanh số của từng Sản phẩm.
Vấn đề có thể được giải quyết khá đơn giản bằng cách sử dụng hàm SUMIF(), nhưng việc xây dựng báo cáo đòi hỏi một số kỹ năng nhất định khi làm việc với một số công cụ EXCEL.
Vậy hãy bắt đầu. Đầu tiên chúng ta cần tạo danh sách tên các Sản phẩm. Bởi vì Trong cột Sản phẩm của bảng nguồn, các tên được lặp lại, khi đó chúng ta chỉ cần chọn các giá trị từ đó. Điều này có thể được thực hiện theo nhiều cách: công thức (xem bài viết), thông qua menu Dữ liệu/Làm việc với dữ liệu/Xóa trùng lặp hoặc sử dụng . Nếu bạn sử dụng phương pháp đầu tiên thì khi thêm Sản phẩm mới vào bảng nguồn, tên mới sẽ tự động được đưa vào danh sách. Nhưng ở đây, để đơn giản, chúng ta sẽ sử dụng phương pháp thứ hai. Đối với điều này:
- Đi tới trang tính có bảng gốc;
- Gọi ( Dữ liệu/ Sắp xếp & Lọc/ Nâng cao);
- Điền vào các trường như trong hình bên dưới: đặt nút chuyển sang vị trí Sao chép kết quả sang vị trí khác; trong trường Phạm vi Nguồn, nhập $A$4:$A$530; Kiểm tra hộp Chỉ những mục duy nhất.
- Sao chép danh sách kết quả vào trang tính sẽ đặt báo cáo;
- Sắp xếp danh sách sản phẩm ( Dữ liệu/ Sắp xếp và lọc/ Sắp xếp từ A đến Z).
Bạn sẽ nhận được danh sách sau đây.
Trong một tế bào B6 Nhập công thức sau, sau đó sao chép nó xuống cuối danh sách:
SUMIF(Nguồn_Bảng[Sản phẩm],A6,Bảng_nguồn[Doanh số])
COUNTIF(Bảng_gốc[Sản phẩm],A6)
Báo cáo số 2 Doanh thu sản phẩm theo khu vực
Hãy tìm tổng doanh số của từng Sản phẩm trong Khu vực.
Hãy sử dụng danh sách Sản phẩm được tạo cho Báo cáo số 1. Theo cách tương tự, chúng ta sẽ có được danh sách tên Khu vực (trong trường Phạm vi ban đầu, nhập $D$4:$D$530).
Sao chép phạm vi dọc kết quả vào Bảng nhớ tạm và nó nằm ngang. Đặt phạm vi kết quả chứa tên của Khu vực vào tiêu đề báo cáo.
Trong một tế bào B 8 Hãy nhập công thức sau:
SUMIFS(Original_Table[Doanh số];
Source_Table[Sản phẩm];$A8;
Source_Table[Khu vực bán hàng];B$7)
Công thức sẽ trả về tổng doanh số của Sản phẩm có tên được đặt trong ô A8 , trong Vùng từ ô VÀO LÚC 7 GIỜ . Hãy lưu ý cách sử dụng (tham khảo $A8 và B$7), bạn sẽ cần đến nó khi sao chép công thức cho các ô trống còn lại của bảng.
Sẽ không thể sao chép công thức trên vào các ô bên phải bằng cách sử dụng (điều này đã được thực hiện cho Báo cáo số 1), bởi vì trong trường hợp này trong ô C8 công thức sẽ trông như thế này:
SUMIFS(Original_Table[Doanh số, ngày];
Source_Table[Group];$A8;
Source_Table[Doanh số];C$7)
Báo cáo số 3 Lọc sản phẩm theo lợi nhuận
Hãy quay trở lại bảng ban đầu. Mỗi lô Hàng đều có lãi hoặc không có lãi (xem cột Lợi nhuận ở bảng gốc). Hãy tính doanh thu theo Nhóm sản phẩm tùy theo khả năng sinh lời. Để thực hiện việc này, chúng tôi sẽ lọc các bản ghi của bảng nguồn theo trường Lợi nhuận bằng các công thức.
TẦN SỐ(Nguồn_Bảng[Doanh số, ngày];A7:A12)
Để nhập công thức, hãy chọn phạm vi C6:C12 , sau đó vào Thanh công thức nhập công thức trên và nhấp vào ĐIỀU KHIỂN+ SỰ THAY ĐỔI+ ĐI VÀO.
Có thể thu được kết quả tương tự bằng cách sử dụng hàm SUMPRODVEL() thông thường:
=SUMPRODVEL((Original_Table[Doanh số, ngày]>A6)*
(Nguồn_Bảng[Doanh số, ngày]<=A7))
Báo cáo số 5 Thống kê cung ứng hàng hóa
Bây giờ chúng ta sẽ chuẩn bị một báo cáo về việc cung cấp Hàng hóa trong tháng.
Đầu tiên, hãy tạo một danh sách các tháng theo năm. Trong bảng gốc, ngày giao hàng sớm nhất là 11/07/2009. Bạn có thể tính toán nó bằng công thức:
=MIN(Nguồn_Bảng[Ngày giao hàng])
Hãy tạo một danh sách ngày - bắt đầu từ ngày giao hàng sớm nhất. Để làm điều này, chúng tôi sử dụng công thức:
=THÁNG($C$5,-1)+1
Kết quả là chúng ta nhận được danh sách các ngày - những ngày đầu tiên của tháng:
Bằng cách áp dụng định dạng ô thích hợp, hãy thay đổi cách hiển thị ngày tháng:
Công thức tính số lô hàng xuất trong tháng:
TỔNG HỢP((Original_Table[Ngày giao hàng]>=B9)* Bây giờ hãy thêm các dòng để tính tổng số đợt cho mỗi năm. Để thực hiện việc này, hãy thay đổi bảng một chút, đánh dấu năm thực hiện giao hàng trong một cột riêng bằng cách sử dụng hàm YEAR(). Bây giờ, để hiển thị theo năm, hãy tạo cấu trúc thông qua mục menu: Sau khi nhấn OK bảng sẽ được sửa đổi như sau: Tổng phụ theo năm sẽ được tạo. Bản tóm tắt : Các báo cáo tương tự như báo cáo được tạo có thể được thực hiện một cách tự nhiên bằng hoặc sử dụng Lọc vào bảng nguồn hoặc sử dụng các hàm khác BDSUMM(), BIZULT(), BCOUNT(), v.v. Việc lựa chọn phương pháp tiếp cận tùy thuộc vào tình huống cụ thể. Xin chào các bạn. Bạn có thường xuyên phải tóm tắt một lượng lớn dữ liệu không? Nhận tổng phụ? Nếu thường xuyên thì bảng tổng hợp Excel chính là thứ bạn cần gấp! Việc tạo bảng tổng hợp chỉ mất vài phút và kết quả có vẻ như bạn đã làm việc với nó cả tuần. Hấp dẫn? Cùng đọc nào! Bảng tổng hợp là một công cụ Microsoft Excel mạnh mẽ giúp giải quyết nhiều vấn đề và quan trọng nhất là trả lời nhiều câu hỏi về các quy trình được mô tả bằng các con số trong tệp của bạn. Tôi sẽ cho bạn một ví dụ. Hình ảnh bên dưới hiển thị danh sách doanh số bán hàng của các cửa hàng bán lẻ ở nhiều khu vực khác nhau, chi tiết theo ngày trong năm: Có đúng là bảng này không có nhiều thông tin và ở dạng này không hữu ích? Và đây là bảng tổng hợp được hình thành từ dữ liệu này: Tại đây, tất cả doanh số bán hàng được hệ thống hóa theo khu vực và người quản lý theo hàng, theo nhóm sản phẩm theo cột. Dữ liệu như vậy ít nhất đã phù hợp cho việc phân tích tiếp theo. Chúng ta thấy rõ rằng bảng tổng hợp tóm tắt một lượng lớn dữ liệu một cách hiệu quả và như tôi sẽ thảo luận sau, không đòi hỏi nỗ lực và thời gian đáng kể để xây dựng. Không phải mọi phạm vi dữ liệu trong Excel đều có thể được sử dụng để tạo bảng tổng hợp. Dữ liệu phải được chuẩn hóa. Trong trường hợp của chúng tôi, điều này có nghĩa là mỗi hàng phải mô tả một sự kiện và tất cả các cột phải được điền cho sự kiện đó. Mỗi hàng của một cột phải chứa dữ liệu của một . Hãy nhìn lại xem nó trông như thế nào trong bức ảnh đầu tiên của bài viết. Mỗi cột phải có một tiêu đề mang tính thông tin, tức là nắp phải đầy. Để tạo bảng tổng hợp dựa trên dữ liệu của bạn, hãy làm theo các bước sau: Một vùng trống của PivotTable và menu bố cục dữ liệu sẽ mở ra. Cái sau bao gồm năm cửa sổ: Các vùng 2-5 chứa đầy dữ liệu bằng cách kéo các tiêu đề từ bước 1. Ví dụ: bạn cần tìm hiểu số tiền bán hàng hàng năm của người quản lý ở tất cả các khu vực là bao nhiêu. Điều này có nghĩa là chúng ta sẽ có các khu vực và người quản lý trong các dòng cũng như số tiền bán hàng trong các giá trị. Kéo tên cột tương ứng từ khu vực đầu tiên của menu bố cục vào “Hàng” và “Giá trị”. Đây là những gì xảy ra: Nếu bây giờ chúng ta muốn dữ liệu trong các cột được chia thành các nhóm sản phẩm. Kéo trường “Nhóm sản phẩm” vào “Cột” và nhận kết quả: Và nếu đột nhiên chúng tôi quyết định rằng chúng tôi chỉ cần dữ liệu cho vùng đầu tiên, chúng tôi sẽ thêm trường “Vùng” vào “Bộ lọc” và vùng bộ lọc sẽ xuất hiện phía trên bảng tổng hợp. Mở danh sách thả xuống trong khu vực này và chỉ chọn khu vực đầu tiên. Đối với tôi, có vẻ như đây là một công cụ rất đơn giản và bạn nhất định phải thành thạo nó. Hãy tưởng tượng, danh sách của tôi, làm ví dụ cho bài đăng này, có 9.883 hàng và tôi xử lý chúng mà không cần nỗ lực, chỉ bằng vài cú click chuột. Và một bảng như bảng chúng ta vừa làm đã trông giống như một báo cáo chuyên nghiệp. Và bây giờ, chẳng hạn, chúng tôi muốn biết người quản lý nào bán được nhiều hàng nhất. Hãy xóa tất cả các bộ lọc và xóa hộp kiểm “Vùng” khỏi các hàng. Chúng tôi nhận được một danh sách các nhà quản lý và doanh số bán hàng của họ. Nhấp chuột phải vào bất kỳ dòng “doanh số” nào trong cột “Tổng cộng” và chọn từ menu ngữ cảnh Sắp xếp – Sắp xếp giảm dần. Đương nhiên, người quản lý có doanh thu cao nhất sẽ ở trên cùng và người quản lý có ít nhất sẽ ở cuối. Trong trường “Giá trị”, bạn không chỉ có thể tóm tắt dữ liệu. Ví dụ: bạn có thể đếm số lượng giá trị, hiển thị giá trị tối thiểu hoặc tối đa, v.v. Để thực hiện việc này, hãy nhấp chuột phải vào bất kỳ ô nào của cột mong muốn, nhấp vào “Tổng theo” trong menu ngữ cảnh, sau đó chọn hàm bạn cần. Bạn có thể tùy chỉnh bố cục của bảng trụ theo logic xây dựng. Chọn bất kỳ ô nào của nó và tìm chúng trên ribbon Hàm tạo – Bố cục. Tại đây bạn có thể thực hiện cài đặt trên bốn điểm: Vì bảng tổng hợp được cho là một công cụ phổ biến để chạy báo cáo nên chúng phải linh hoạt trong việc tùy chỉnh giao diện của mình. Để đăng ký bạn có thể: Và áp dụng cho chúng các thao tác định dạng mà bạn đã quen thuộc. Theo quy định, điều này là đủ để làm cho báo cáo hoàn chỉnh có nhiều thông tin và dễ hiểu. Để tùy chỉnh giao diện chi tiết hơn nữa, hãy nhấp chuột phải vào bất kỳ ô nào của bảng tổng hợp và chọn “Tùy chọn bảng tổng hợp” từ menu ngữ cảnh. Dưới đây là một số cài đặt hữu ích. Ví dụ: đặt nội dung sẽ hiển thị thay vì hoặc định cấu hình mức độ chi tiết của bảng tổng hợp. Nếu bảng của bạn không hoàn toàn làm bạn hài lòng và bạn muốn thay đổi một chút nội dung của nó về nội dung hàng, cột và dữ liệu cơ bản, bạn có thể thực hiện việc này bất kỳ lúc nào. Kéo các khối tiêu đề vào menu cài đặt bảng trụ, xóa và thêm, thay đổi bộ lọc. Chương trình sẽ ngay lập tức phản hồi những thay đổi bạn thực hiện. Đó là tất cả về việc tạo bảng tổng hợp nhưng chủ đề vẫn chưa khép lại và trong bài viết tiếp theo tôi sẽ nói đến. Tôi khuyên bạn nên đọc và sử dụng nó, vì không có gì tốt hơn là nhận được kết quả nhanh chóng và dễ dàng! Như mọi khi, tôi rất mong nhận được câu hỏi và nhận xét của bạn, chúng ta hãy cùng nhau trở thành những người chuyên nghiệp!
(Nguồn_Bảng[Ngày giao hàng] 
Bằng cách nhấn vào các nút nhỏ ở góc trên bên trái của trang tính, bạn có thể điều khiển việc hiển thị dữ liệu trong bảng.
Dữ liệu nào phù hợp cho bảng trụ?
Cách tạo một bảng tổng hợp
Cách thay đổi bố cục của bảng tổng hợp
Định dạng bảng tổng hợp Excel
