Bảng tổng hợp Excel từ nhiều nguồn dữ liệu. Thiết lập dữ liệu ban đầu

Hợp nhất dữ liệu là một cách thuận tiện để kết hợp dữ liệu từ nhiều nguồn vào một báo cáo. Ví dụ: nếu mỗi chi nhánh khu vực của công ty bạn có bảng tóm tắt chi phí, bạn có thể sử dụng hợp nhất dữ liệu để kết hợp các giá trị đó vào báo cáo chi phí của công ty. Báo cáo như vậy có thể chứa tổng số và doanh thu trung bình, mức tồn kho hiện tại và các sản phẩm phổ biến nhất trong toàn tổ chức.

Để tóm tắt và tóm tắt kết quả dữ liệu trong các phạm vi riêng lẻ, bạn có thể hợp nhất chúng trong PivotTable trên trang tính chính. Các phạm vi có thể nằm trong cùng sổ làm việc với trang tính chính hoặc trong các sổ làm việc khác. Dữ liệu hợp nhất dễ dàng cập nhật và tóm tắt một cách thường xuyên hoặc khi cần thiết.

Báo cáo PivotTable hợp nhất thu được có thể chứa các trường sau trong khu vực Danh sách các trường của bảng tổng hợpđược thêm vào PivotTable: Hàng, Cột và Giá trị. Bạn cũng có thể bao gồm tối đa bốn trường bộ lọc trong báo cáo của mình, được đặt tên là Trang1, Trang2, Trang3 và Trang4.

Thiết lập dữ liệu ban đầu

Mỗi phạm vi dữ liệu phải được chuyển đổi sang định dạng chéo bảng với tên hàng và cột phù hợp cho các mục bạn muốn nối. Dữ liệu đã chọn không được bao gồm tổng số hàng hoặc tổng số cột. Ví dụ dưới đây hiển thị bốn phạm vi ở định dạng chéo bảng.

Trường trang khi hợp nhất dữ liệu

Khi hợp nhất dữ liệu, bạn có thể sử dụng các trường trang chứa các phần tử đại diện cho một hoặc nhiều phạm vi nguồn. Ví dụ: nếu bạn đang hợp nhất dữ liệu ngân sách từ bộ phận tiếp thị, bộ phận bán hàng và bộ phận sản xuất thì trường trang có thể chứa một phần tử riêng biệt chứa dữ liệu cho từng bộ phận này cũng như một phần tử chứa dữ liệu tóm tắt. Ví dụ bên dưới hiển thị bảng tổng hợp với một trường trang và nhiều mục được chọn.

Sử dụng dãy được đặt tên

Nếu có khả năng vào lần tiếp theo bạn hợp nhất dữ liệu, phạm vi dữ liệu nguồn sẽ thay đổi (ví dụ: số hàng sẽ thay đổi), bạn nên chỉ định tên cho từng phạm vi nguồn trên các trang tính khác nhau. Những tên này có thể được sử dụng khi hợp nhất các phạm vi trên một bảng tính chính. Nếu bạn đang mở rộng phạm vi ban đầu, bạn có thể cập nhật phạm vi trên một trang tính riêng cho tên được chỉ định để bao gồm dữ liệu mới trước khi làm mới PivotTable.

Các cách khác để hợp nhất dữ liệu

Excel cũng cung cấp các phương pháp hợp nhất dữ liệu khác cho phép bạn làm việc với dữ liệu ở các định dạng và bố cục khác nhau. Ví dụ: bạn có thể tạo công thức có tham chiếu hàng loạt hoặc sử dụng lệnh Hợp nhất(có sẵn trên tab Dữ liệu trong nhóm Làm việc với dữ liệu).

Hợp nhất nhiều phạm vi

Để hợp nhất nhiều phạm vi, bạn có thể sử dụng Trình hướng dẫn PivotTable và PivotChart. Tại đây bạn có thể chỉ định số lượng trường trang sẽ được sử dụng: không có, một hoặc nhiều trường.

Hợp nhất dữ liệu mà không cần sử dụng trường trang

Để kết hợp dữ liệu từ tất cả các phạm vi và tạo một phạm vi hợp nhất không có lề trang, hãy làm như sau:

1. Lệnh bổ sung.

   Trên danh sách Chọn lệnh từ chọn mục Tất cả các đội.

   Chọn một mục từ danh sách và nhấp vào nút Thêm vào, sau đó là nút ĐƯỢC RỒI.

Nhấp vào biểu tượng trình hướng dẫn trên Thanh công cụ truy cập nhanh.

Trên trang Bước 1 trình hướng dẫn, chọn một tùy chọn rồi bấm vào Hơn nữa.

Trên trang Bước 2a chọn tùy chọn Tạo trường trang và sau đó nhấp vào nút Hơn nữa.

Trên trang Bước 2b làm như sau:

1. Thêm vào.

   Khuyên bảo:

   Trong phần này, nhập 0 và sau đó nhấp vào nút Hơn nữa.

Trên trang Bước 3 Sẵn sàng.

Hợp nhất dữ liệu bằng một trường trang duy nhất

Để bao gồm một trường trang chứa phần tử cho từng phạm vi ban đầu cũng như phần tử để hợp nhất tất cả các phạm vi, hãy làm như sau:

Thêm Trình hướng dẫn Biểu đồ và PivotTable vào Thanh công cụ Truy nhập Nhanh. Đối với điều này:

1. Bấm vào mũi tên bên cạnh thanh công cụ và chọn Lệnh bổ sung.

   Trên danh sách Chọn lệnh từ chọn mục Tất cả các đội.

   Chọn một mục từ danh sách Trình hướng dẫn biểu đồ và bảng tổng hợp và nhấn nút Thêm vào, sau đó là nút ĐƯỢC RỒI.

Trong sổ làm việc, hãy bấm vào ô trống không phải là một phần của PivotTable.

Trên trang Bước 1 tùy chọn chọn thuật sĩ trong một số phạm vi hợp nhất và sau đó nhấp vào nút Hơn nữa.

Trên trang Bước 2a chọn tùy chọn Tạo một trường trang và sau đó nhấp vào nút Hơn nữa.

Trên trang Bước 2b làm như sau:

1. Đi tới sổ làm việc và chọn một phạm vi ô, sau đó quay lại trình hướng dẫn PivotTable và Biểu đồ rồi bấm vào Thêm vào.

   Khuyên bảo: Nếu phạm vi ô nằm trong sổ làm việc khác, trước tiên hãy mở sổ làm việc đó để chọn dữ liệu dễ dàng hơn.

Trên trang Bước 3 chỉ định thêm bảng tổng hợp vào một trang tính mới hay vào một trang tính hiện có và nhấp vào nút Sẵn sàng.

Hợp nhất dữ liệu bằng nhiều trường trang

Bạn có thể tạo nhiều trường trang và gán tên thành phần cho từng phạm vi nguồn. Điều này cho phép hợp nhất một phần hoặc toàn bộ. Ví dụ: một trường trên một trang có thể hợp nhất dữ liệu từ bộ phận tiếp thị và bán hàng mà không có dữ liệu từ bộ phận sản xuất và một trường khác có thể hợp nhất dữ liệu từ cả ba bộ phận. Để tạo sự hợp nhất bằng nhiều trường trang, hãy làm như sau:

Thêm Trình hướng dẫn Biểu đồ và PivotTable vào Thanh công cụ Truy nhập Nhanh. Đối với điều này:

1. Bấm vào mũi tên bên cạnh thanh công cụ và chọn Lệnh bổ sung.

   Trên danh sách Chọn lệnh từ chọn mục Tất cả các đội.

   Chọn một mục từ danh sách Trình hướng dẫn biểu đồ và bảng tổng hợp và nhấn nút Thêm vào, sau đó là nút ĐƯỢC RỒI.

Trong sổ làm việc, hãy bấm vào ô trống không phải là một phần của PivotTable.

Trên trang Bước 1 tùy chọn chọn thuật sĩ trong một số phạm vi hợp nhất và sau đó nhấp vào nút Hơn nữa.

Trên trang Bước 2a chọn tùy chọn Tạo trường trang và sau đó nhấp vào nút Hơn nữa.

Trên trang Bước 2b làm như sau:

1. Đi tới sổ làm việc và chọn một phạm vi ô, sau đó quay lại trình hướng dẫn PivotTable và Biểu đồ rồi bấm vào Thêm vào.

   Khuyên bảo: Nếu phạm vi ô nằm trong sổ làm việc khác, trước tiên hãy mở sổ làm việc đó để chọn dữ liệu dễ dàng hơn.

   Trong chuong Đầu tiên, chỉ định số trường của trang PivotTable bấm vào số trường bạn muốn sử dụng.

   Trong nhóm Then, chọn một phạm vi từ danh sách và chỉ định nhãn thành phần trong mỗi cửa sổ trường có sẵn. Lặp lại thao tác cho từng phạm vi, đối với từng trường trang, chọn một phạm vi ô, sau đó chỉ định tên của nó.

   Ví dụ

   • Nếu ở phần Đầu tiên, chỉ định số trường của trang PivotTable số đã cho 1 , chọn từng phạm vi rồi nhập tên duy nhất vào trường Trường đầu tiên. Nếu bạn có bốn phạm vi, mỗi phạm vi tương ứng với một phần tư của năm tài chính, hãy chọn phạm vi đầu tiên, nhập tên "Q1", chọn phạm vi thứ hai, nhập tên "Q2" và lặp lại quy trình cho các phạm vi "Q3" và "Q4".

     Nếu ở phần Đầu tiên, chỉ định số trường của trang PivotTable số đã cho 2 , hãy làm theo các bước tương tự trong trường Trường đầu tiên. Sau đó chọn hai phạm vi và nhập vào trường Trường thứ hai cùng tên, ví dụ "Pg1" và "Pg2". Chọn phạm vi đầu tiên và nhập tên "Pg1", chọn phạm vi thứ hai và nhập tên "Pg1", chọn phạm vi thứ ba và nhập tên "Pg2", chọn phạm vi thứ tư và nhập tên "Pg2".

Trên trang Bước 3 chỉ định thêm bảng tổng hợp vào một trang tính mới hay vào một trang tính hiện có và nhấp vào nút Sẵn sàng.

Bảng tổng hợp Excel là gì? Có lẽ, một người chưa quen sẽ cho rằng điều rõ ràng nhất - đây là các bảng trong đó dữ liệu từ một số nguồn khác nhau được thu thập (kết hợp) để trả lời một yêu cầu nhất định. Trên thực tế, kết luận này không hoàn toàn đúng.

Bảng tổng hợp Excel là một công cụ thu thập thông tin, thường xuyên nhất là từ một bảng cơ sở dữ liệu! (Có khả năng sử dụng nhiều phạm vi hợp nhất làm nguồn nhưng sẽ không được thảo luận trong bài viết này.)

Bằng cách sử dụng các bảng tổng hợp từ một tập hợp các hàng cơ sở dữ liệu hỗn loạn, bạn có thể trong vài giây trích xuất những thông tin cần thiết, cắt bỏ những thông tin không cần thiết, thêm những thông tin còn thiếu và trình bày cho người dùng dưới dạng đẹp và dễ đọc.

Nếu bạn thường xuyên làm việc trên MS Excel với lượng thông tin lớn và không biết gì về bảng trụ, thì hãy coi như bạn đang “đóng đinh” bằng máy tính mà không biết mục đích thực sự của nó!

Khi nào bạn nên sử dụng bảng trụ?

Thứ nhất, khi bạn làm việc với một lượng lớn dữ liệu thống kê, việc phân tích bằng cách sắp xếp và lọc sẽ tốn rất nhiều công sức.

Thứ hai, khi cần lặp lại (tính toán lại) các báo cáo theo những khoảng thời gian nhất định do thay đổi nguồn - bảng cơ sở dữ liệu.

Thứ ba, khi phân tích những thay đổi trong dữ liệu cơ sở dữ liệu qua các khoảng thời gian khác nhau hoặc các tiêu chí khác.

Đây là những tình huống chính mà sức mạnh kỳ diệu thực sự của công cụ Excel Pivot Table được bộc lộ.

Việc tạo các bảng tổng hợp và làm việc với chúng không hề ảnh hưởng đến nội dung của nguồn - cơ sở dữ liệu!!! Bảng tổng hợp Excelcho phép bạn xem dữ liệu này từ các góc độ khác nhau mà không thay đổi bất cứ điều gì trong cơ sở dữ liệu!!!

Đây là bài viết cuối cùng và quan trọng nhất trong loạt bài đánh giá về lưu trữ thông tin và quản lý khối lượng lớn dữ liệu dạng bảng.

Ở các bài trước của loạt bài chúng tôi đã sáng tạo, tìm hiểu, áp dụng và giới thiệu.

Tạo mẫu bảng tổng hợp.

Chúng ta sẽ tạo một bảng tổng hợp trong cùng một sổ làm việcExcel, nơi chứa nguồn thông tin - một bảng cơ sở dữ liệu, nhưng trên một trang tính khác.

Chúng tôi tiếp tục làm việc với cơ sở giáo dục nhỏ DB2 “Sản xuất cấu trúc kim loại theo phần số 2,” mà chúng tôi đã làm việc với tất cả các bài viết của loạt bài này.

1. Mở tệp trong MS Excel.

2. Chúng tôi kích hoạt (“nhấp chuột”) bất kỳ ô nào trong bảng cơ sở dữ liệu.

3. Thực hiện lệnh menu chính của chương trình “Dữ liệu” - “Bảng xoay…”. Lệnh này khởi động dịch vụ nhỏ của Pivot Table Wizard.

4. Không cần suy nghĩ lâu về các tùy chọn để chọn vị trí chuyển đổi trong cửa sổ thả xuống “Wizards…”, chúng tôi thiết lập chúng (chính xác hơn là chúng tôi không chạm vào chúng) như hiển thị bên dưới trong ảnh chụp màn hình, di chuyển giữa các cửa sổ bằng cách sử dụng các nút “Tiếp theo”.

Ở bước thứ hai, “Wizard…” sẽ tự chọn phạm vi đó nếu bạn đã chuẩn bị cơ sở dữ liệu đúng cách và hoàn thành bước 2 của phần này của bài viết.

5. Ở bước thứ ba của “Wizards…” chúng ta nhấn nút “Hoàn tất”. Mẫu bảng tổng hợp được tạo và đặt trên một trang tính mới của cùng một tệp chứa cơ sở dữ liệu!

Ngoài mẫu vẫn trống, trên trang tính mới tạo của tệp Excel, bạn sẽ thấy bảng “Bảng tổng hợp” cùng với các công cụ của nó và cửa sổ “Danh sách trường bảng tổng hợp” xuất hiện khi bạn kích hoạt bất kỳ ô nào bên trong bảng tổng hợp. Chúng tự động xuất hiện, tôi chỉ cần đặt chúng bên cạnh mẫu và điều chỉnh kích thước. Nếu bạn không thấy bảng “Bảng xoay”, hãy gọi nó qua menu chính bằng lệnh “Xem” - “Thanh công cụ” - “Bảng xoay”

Trong MS Excel 2007 và các phiên bản mới hơn, “Wizard…” đã bị bãi bỏ vì 99% người dùng không bao giờ thay đổi cài đặt chuyển đổi được đề xuất và thực hiện ba bước này, chỉ đồng ý với các tùy chọn được đề xuất (chúng tôi cũng làm như vậy).

Trong MS Excel 2007, sử dụng lệnh “Pivot Table”, hộp thoại “Tạo bảng Pivot” xuất hiện, trong đó bạn chỉ cần chỉ định nguồn dữ liệu để phân tích và vị trí của bảng tổng hợp sẽ được tạo.

Tạo các bảng tổng hợp Excel khả thi.

Mẫu đã tạo có bốn vùng để tạo bảng tổng hợp, bạn nên đặt tên của các trường cột của bảng cơ sở dữ liệu bằng cách kéo chúng bằng nút chuột trái từ cửa sổ “Danh sách trường bảng tổng hợp”. Hãy để tôi nhắc bạn rằng trong cơ sở dữ liệu, một trường là một cột có tiêu đề.

Chú ý!!!

Các thành phần của cửa sổ “Danh sách trường bảng tổng hợp” là tiêu đề của các trường cơ sở dữ liệu!

1. Nếu bạn đặt một trong các thành phần Danh sách trường PivotTable ở vùng trên cùng của mẫu, nơi nó sẽ trở thành "Trường trang", sau đó chúng ta sẽ có được một cách thuận tiện lọc các mục cho trường này.

2. Nếu bạn kéo một phần tử (hoặc hai, ít thường xuyên hơn - ba hoặc nhiều hơn) của “Danh sách các trường bảng tổng hợp” sang vùng bên trái của mẫu, nơi nó sẽ trở thành "Trường chuỗi" tiêu đề hàng bảng trụ.

3. Nếu bạn kéo một phần tử (hoặc hai, ít thường xuyên hơn - ba hoặc nhiều hơn) của “Danh sách …” vào vùng phía trên của mẫu, nơi nó sẽ trở thành "Trường cột", khi đó chúng ta sẽ lấy được từ các bản ghi của trường này trong bảng cơ sở dữ liệu tiêu đề cột bảng trụ.

4. Nếu bạn kéo một phần tử (hoặc hai, ít thường xuyên hơn - ba hoặc nhiều hơn) của “Danh sách …” vào khu vực trung tâm của mẫu, nơi nó sẽ trở thành “Phần tử dữ liệu”, khi đó chúng ta sẽ lấy được từ các bản ghi của trường cơ sở dữ liệu này giá trị bảng trụ.

Các giá trị - thành phần dữ liệu - sẽ được định vị theo đúng quy tắc của bảng hai chiều, tức là tại giao điểm của tiêu đề hàng và tiêu đề cột tương ứng!

Không rõ ràng lắm? Hãy chuyển sang các ví dụ thực tế - mọi thứ sẽ trở nên rõ ràng!

Nhiệm vụ số 9:

Có bao nhiêu tấn kết cấu kim loại được sản xuất cho mỗi đơn hàng?

Để trả lời câu hỏi này, chúng ta sẽ thực hiện chỉ hai bước! Sơ đồ của những hành động này được hiển thị trong hình trước với mũi tên màu xanh lam.

1. Kéo phần tử “Đặt hàng” từ cửa sổ “Danh sách trường bảng tổng hợp” vào vùng “Trường hàng” của mẫu trống.

2. Sử dụng chuột, kéo phần tử “Tổng khối lượng, t” từ cửa sổ “Danh sách các trường bảng tổng hợp” vào vùng “Thành phần dữ liệu” của mẫu. Kết quả được hiển thị trong ảnh chụp màn hình bên trái. Tôi nghĩ không cần phải giải thích.

Tiêu đề của “Thành phần dữ liệu” “Tổng theo trường Tổng khối lượng, t” nằm ở vị trí cao hơn tiêu đề “Lề hàng” và ở bên trái nơi có thể đặt tiêu đề “Lề cột”. Hãy chú ý đến điều này!

Nhiệm vụ số 10:

Có bao nhiêu tấn kết cấu kim loại được sản xuất cho mỗi đơn hàng theo ngày?

Chúng tôi tiếp tục làm việc.

3. Để trả lời câu hỏi của nhiệm vụ số 10, chỉ cần thêm phần tử “Ngày” vào bảng tổng hợp của chúng tôi từ cửa sổ “Danh sách trường bảng tổng hợp” trong vùng “Trường cột” của mẫu.

4. Các bản ghi có thể được nhóm lại, ví dụ như theo tháng. Để thực hiện việc này, trong bảng “Bảng tổng hợp”, hãy nhấp vào tab “Bảng tổng hợp” và chọn “Nhóm và cấu trúc” - “Nhóm ...”. Trong cửa sổ “Nhóm”, chúng tôi thực hiện cài đặt theo ảnh chụp màn hình bên trái.

Vì tất cả các bản ghi trong cơ sở dữ liệu của chúng tôi được tạo vào tháng 4, nên sau khi nhóm, chúng tôi chỉ thấy hai cột - “Tháng 4” và “Tổng cộng”. Khi các bản ghi ngày tháng 5 và tháng 6 xuất hiện trong cơ sở dữ liệu nguồn, hai cột tương ứng sẽ được thêm vào bảng tổng hợp.

Nhiệm vụ số 11:

Khi nào và bao nhiêu tấn, miếng tem được sản xuất theo đơn hàng số 3?

5. Trước tiên, hãy kéo trường “Đặt hàng” từ cửa sổ “Danh sách trường bảng tổng hợp” hoặc trực tiếp từ chính bảng tổng hợp lên đầu trang tính vào vùng “Trường trang” của mẫu.

7. Thêm trường “Số lượng, chiếc” vào vùng “Thành phần dữ liệu”.

Cấu hình bảng tổng hợp Excel cần thiết để trả lời câu hỏi đã được tạo.

8. Nhấp vào nút mũi tên xuống ở ô B1 và ​​trong danh sách thả xuống, thay vì mục “(Tất cả)”, hãy chọn “3”, tương ứng với thứ tự số 3 mà chúng tôi quan tâm và đóng danh sách bằng cách nhấp vào “ nút OK”.

Câu trả lời cho câu hỏi vấn đề nằm trong ảnh chụp màn hình bên dưới văn bản này.

Định dạng một bảng tổng hợp.

Nếu bạn cần in ra một bảng tổng hợp và đưa cho ai đó xem, hoặc chỉ để làm hài lòng bản thân một chút, thì nó cần được định dạng - tạo cho nó một diện mạo đẹp mắt và định dạng lại một số tiêu đề “không phù hợp”.

Các tiêu đề “Tổng theo trường Tổng trọng lượng, t” và “Tổng theo trường Số lượng, chiếc” có vẻ dễ hiểu, nhưng không hiểu sao lại không phải bằng tiếng Nga. Hãy đổi tên chúng thành “Tổng khối lượng sản phẩm, t” và “Số lượng sản phẩm, chiếc”.

1. Hãy tiếp tục làm việc với bảng đã tạo. Kích hoạt ô B5 bằng chuột.

2. Trên thanh công cụ “Bảng tổng hợp”, nhấp vào nút “Tùy chọn trường” (được đánh dấu ở bên phải trong hình ảnh bên dưới).

3. Trong cửa sổ “Tính toán trường bảng tổng hợp” bật lên, hãy đổi tên và nhấp vào nút “OK”.

4. Sử dụng thuật toán tương tự, chúng tôi đổi tên tiêu đề trường thứ hai, đầu tiên là “đứng” bằng chuột trên ô B6.

5. Trong bảng “Bảng tổng hợp”, nhấp vào nút “Định dạng báo cáo” (được đánh dấu ở bên trái trong hình trên - điểm 3 phía trên).

6. Trong cửa sổ “Tự động định dạng” xuất hiện, hãy chọn từ các tùy chọn định dạng được đề xuất, ví dụ: “Báo cáo 6” và nhấp vào “OK”.

Giao diện bảng tổng hợp của chúng tôi đã thay đổi đáng kể.

Phần kết luận.

Tôi muốn thu hút sự chú ý của bạn đến một số điểm rất quan trọng!

Khi nguồn thay đổi (ví dụ: thêm một bản ghi khác vào cơ sở dữ liệu), các thay đổi sẽ không tự động xảy ra trong bảng tổng hợp!!!

Để các thay đổi trong cơ sở dữ liệu được phản ánh trong bảng tổng hợp, bạn phải nhấp vào nút “Cập nhật dữ liệu” theo cách thủ công mỗi lần (biểu tượng nút là “ ! ) trong bảng “Bảng tổng hợp”!!!

Đặt các trường có chứa thông tin văn bản, có thể trở thành tiêu đề cho bảng tổng hợp được tạo.

Trong khu vực "Trường hàng" và "Trường cột", đặt các trường có chứa thông tin văn bản, có thể trở thành tiêu đề cho hàng và cột bảng tổng hợp đã tạo.

Trong khu vực “Thành phần dữ liệu”, đặt chủ yếu thông tin số.

Tiến hành bằng cách sắp xếp các trường như bạn sắp xếp trên một tờ giấy! Đừng sợ phạm sai lầm. Mọi thứ đều có thể dễ dàng sửa chữa.

Các bảng tổng hợp được tạo bằng hai hoặc ba lần di chuyển chuột ở một trong các phần trước của bài viết này đã trả lời rất nhanh một số câu hỏi rất khó! Nếu đơn đặt hàng được sản xuất trong vài tháng và số lượng thương hiệu vượt quá vài nghìn, thì bạn sẽ mất bao nhiêu thời gian để giải quyết các vấn đề đã thảo luận ở trên? Giờ? Ngày? Bảng tổng hợp Excel thực hiện việc này ngay lập tức, lặp đi lặp lại và không có lỗi!!!

Nhiều cuốn sách hay về “dày” và “mỏng” đã được viết về bảng tổng hợp Excel. Trước hết, đây là những cuốn sách của Bill Jelen (“Mr. Excel”) và Michael Alexander. Đối với những người quan tâm đến chủ đề này, tôi khuyên bạn nên đọc chúng.

Tất nhiên, rất khó, gần như không thể nói chi tiết về công cụ tuyệt vời này trong một bài viết ngắn. Một mặt, tôi muốn đề cập đến những khía cạnh thực tế quan trọng hơn, nhưng mặt khác, trong bài viết giới thiệu, tôi không muốn “đi sâu vào vấn đề”, khiến người dùng sợ hãi trước khối lượng và độ phức tạp của thông tin. Nếu “công cụ thần kỳ” MS Excel này khơi dậy sự quan tâm của người đọc blog, thì trong tương lai chủ đề này sẽ được tiếp tục bằng cách phát hành một tập tài liệu nhỏ với những khuyến nghị thực tế quan trọng và có giá trị.

Hãy nhớ chọn Bảng Pivot Excel! Giá trị của bạn với tư cách là một chuyên gia sẽ tăng lên gấp nhiều lần!

Điều này kết thúc một loạt sáu bài viết cung cấp một cái nhìn tổng quan chung về chủ đề xử lý lượng lớn thông tin trong Excelhoàn thành.

tôi cầu xin kính trọng tác phẩm của tác giả đặt mua để thông báo bài viết trong cửa sổ nằm ở cuối mỗi bài viết hoặc trong cửa sổ đầu trang!

Bạn đọc thân mến, hãy để lại câu hỏi, đánh giá và nhận xét ở phần bình luận cuối trang.

Bảng tổng hợp là một công cụ giúp bạn phân tích dữ liệu của công ty. Bạn có thể tạo một bảng như vậy trong các trình soạn thảo văn bản khác nhau.

Để tạo, bạn cần chọn tiêu đề cho cả hàng và cột từ báo cáo đã tạo. Thuận tiện nhất là tạo báo cáo trong Excel. Bạn có thể áp dụng bộ lọc cho toàn bộ báo cáo hoặc cho một phần cụ thể của báo cáo.

Cách tạo bảng tổng hợp trong excel

Trước hết chúng ta hãy nhìn vào phiên bản 2007. Một số người dùng vẫn có phiên bản cũ của trình chỉnh sửa. Đầu tiên bạn cần tạo bảngđể xây dựng một báo cáo. Nó không được chứa các ô hoặc dòng trống. Hãy xem quá trình tạo báo cáo bằng ví dụ đơn giản nhất:

1. Chúng tôi khởi chạy Excel 2007. Trên bảng trên cùng, chọn tab “Chèn”.
2. Chọn "Tạo bảng tổng hợp được đề xuất".
3. Một cửa sổ sẽ xuất hiện hiển thị các tùy chọn khung. Bạn có thể xem trước báo cáo của mình sẽ trông như thế nào nếu bạn nhấp vào bố cục. Sau đó bạn có thể chọn cái chính. Bấm vào OK và báo cáo sẽ xuất hiện trên trang tính. Ngoài ra, chính Excel sẽ xác định thứ tự các hàng.
4. Chương trình cho phép bạn lọc và sắp xếp các giá trị. Có một biểu tượng đặc biệt trên bảng điều khiển cho việc này.

Đây là ví dụ đơn giản nhất về cách bạn có thể tạo bảng tổng hợp trong phiên bản Excel cũ.

Bảng tổng hợp trong excel 2010 và 2013 - ví dụ làm việc trong excel

Cách tạo báo cáo trong Excel 2010 và 2013 gần như giống nhau. Quá trình trong các phiên bản này hơi khác so với quá trình tạo trong phiên bản 2007. Nhưng, một số khoảnh khắc tương tự: trước khi bắt đầu, bạn cần tạo một bảng đơn giản trong đó bạn chỉ ra tất cả dữ liệu bạn cần. Dòng trên cùng phải chứa tên của các cột.

Bây giờ bạn có thể tạo báo cáo trong Excel:

Bạn có thể làm gì với tờ giấy này:

• Bạn sẽ có quyền truy cập vào thanh công cụ để quản lý các giá trị. Tất cả các trường và tiêu đề của chúng sẽ được liệt kê trong tab “Chọn các trường để thêm vào báo cáo”. Để thêm các mục bắt buộc, bạn phải chọn các trường bắt buộc và danh sách sẽ được tạo.
• Có thể kiểm soát vị trí của các trường. Bạn có thể kéo các cột vào các cửa sổ mong muốn và cũng có thể sắp xếp chúng theo thứ tự phù hợp với mình. Bạn cũng có thể lọc các mục. Để thực hiện việc này, bạn cần kéo mục đó vào cửa sổ “Bộ lọc”. Nếu bạn cần thực hiện phép tính cho một trường riêng biệt, hãy kéo nó vào cửa sổ “Giá trị”.
• Bạn có thể sử dụng các tùy chọn bổ sung. Bạn có thể tìm thấy chúng bằng cách sử dụng nguyên tắc sau: “Tùy chọn” “Làm việc với bảng trụ”. Có rất nhiều công cụ có số lượng lớn cài đặt và chức năng bổ sung.

Cách tạo PivotTable bằng Trình hướng dẫn PivotTable

Công cụ Trình hướng dẫn PivotTable không xuất hiện trong bảng điều khiển chính. Đó là lý do tại sao nó phải được kích hoạt trước tiên. Để thực hiện việc này, hãy đi tới “Tệp” “Tùy chọn” “Thanh công cụ truy cập nhanh”. Trước mặt bạn một danh sách sẽ mở ra“chọn lệnh từ”, chọn “Lệnh trên dải băng”. Bây giờ hãy chọn Trình hướng dẫn bảng tổng hợp và thêm nó. Sau khi hoàn thành tất cả các thao tác, biểu tượng cho chức năng đã chọn sẽ xuất hiện trên bảng điều khiển chính.

Bây giờ bạn có thể tiến hành quá trình tạo báo cáo tóm tắt bằng trình hướng dẫn:

Nếu nguồn là một báo cáo đơn giản chứa tất cả dữ liệu cần thiết cho báo cáo thì tất cả những gì bạn phải làm là tạo báo cáo đó. Bạn đặt phạm vi, nghĩa là chọn dữ liệu bảng đơn giản. Chọn vị trí đặt vị trí và nhấp vào nút “Xong”.

Nếu bạn chọn một nguồn có dữ liệu bạn muốn hợp nhất từ ​​các nguồn khác thì phải được kiểm trađối diện điểm này. Bây giờ bạn cần chỉ định cách bạn sẽ tạo các trường trang. Bạn cũng sẽ cần phải chỉ định phạm vi. Để thực hiện việc này, trước tiên hãy chọn một phạm vi và nhấp vào nút “thêm”. Các giá trị tiếp theo cũng được thêm vào.

Để tránh nhầm lẫn trong phạm vi, chúng cần được đặt tên. Để làm điều này bạn cần chọn một trong những phạm vi liệt kê và cho biết số lượng trường cho nó, sau đó bạn đặt tên cho mỗi trường. Và như vậy với tất cả các phạm vi.

Bảng tổng hợp Excel 2010 từ nhiều trang tính

Nếu thông tin trong báo cáo có cùng loại thì cách dễ nhất là sử dụng Trình hướng dẫn bảng tổng hợp. Nếu báo cáo có cấu trúc phức tạp thì bạn cần phải hành động khác đi. Ví dụ, có hai cái bàn, một cái chứa biên nhận hàng hóa và cái kia - số lượng hàng hóa được bán ở các cửa hàng khác nhau. Điều chính là phải có các tiêu đề giống nhau, sẽ mất ít thời gian hơn để chuyển chúng vào một báo cáo. Trong trường hợp này, việc báo cáo được thực hiện như sau:

Bây giờ bạn cần tạo một báo cáo tóm tắt:

• Chèn.
• Báo cáo tổng hợp.
• Nhập phạm vi và vị trí.

Nhưng phải làm gì nếu bạn cần tạo báo cáo từ nhiều bảng. Thật khó để làm việc thủ công. Vì điều này bạn có thể dùng một phần bổ trợ EXCEL đặc biệt. Nó được tải xuống từ trang web của nhà phát triển.

Vì vậy, chúng tôi đã tìm ra cách tạo bảng tổng hợp trong excel. Bạn cần thể hiện một chút kiên trì và chú ý thì không lâu nữa sẽ có kết quả.

Bảng tổng hợp trong Excel là một công cụ mạnh mẽ để tạo báo cáo. Nó đặc biệt hữu ích trong trường hợp người dùng không làm việc tốt với các công thức và khó có thể tự mình phân tích dữ liệu. Trong bài viết này, chúng ta sẽ xem xét cách tạo các bảng như vậy một cách chính xác và những cơ hội nào tồn tại cho việc này trong trình chỉnh sửa Excel. Bạn không cần phải tải xuống bất kỳ tập tin nào cho việc này. Đào tạo có sẵn trực tuyến.

Bước đầu tiên là tạo một số loại bảng. Đó là khuyến khích để có một số cột. Trong trường hợp này, một số thông tin phải được lặp lại, vì chỉ trong trường hợp này mới có thể thực hiện một số loại phân tích về thông tin đã nhập.

Ví dụ: hãy xem xét các chi phí tài chính giống nhau trong các tháng khác nhau.

Tạo bảng tổng hợp

Để xây dựng một bảng như vậy, bạn cần làm như sau:

1. Đầu tiên, bạn cần phải chọn nó hoàn toàn.

1. Sau đó chuyển đến tab Chèn. Nhấp vào biểu tượng "Bảng". Trong menu xuất hiện, chọn “Bảng tổng hợp”.

1. Kết quả là một cửa sổ sẽ xuất hiện trong đó bạn cần chỉ định một số tham số cơ bản để xây dựng bảng tổng hợp. Bước đầu tiên là chọn vùng dữ liệu để phân tích. Nếu trước đây bạn đã chọn một bảng, một liên kết tới bảng đó sẽ được chèn tự động. Nếu không, nó sẽ cần phải được phân bổ.

1. Sau đó, bạn sẽ được yêu cầu cho biết chính xác nơi việc xây dựng sẽ diễn ra. Tốt hơn là nên chọn tùy chọn “Trên một trang tính hiện có”, vì sẽ bất tiện khi phân tích thông tin khi mọi thứ nằm rải rác trên nhiều trang tính. Sau đó, bạn cần phải chỉ định phạm vi. Để thực hiện việc này, hãy nhấp vào biểu tượng bên cạnh trường nhập.

1. Ngay sau đó, Pivot Table Wizard sẽ thu gọn về kích thước nhỏ. Ngoài ra, hình thức của con trỏ cũng sẽ thay đổi. Bạn sẽ cần nhấp chuột trái vào bất kỳ nơi nào thuận tiện cho bạn.

1. Kết quả là liên kết đến ô được chỉ định sẽ được chèn tự động. Sau đó, bạn cần nhấp vào biểu tượng ở bên phải cửa sổ để khôi phục nó về kích thước ban đầu.

1. Để hoàn tất cài đặt, hãy nhấp vào nút “OK”.

1. Do đó, bạn sẽ thấy một mẫu trống để làm việc với các bảng tổng hợp.

1. Ở giai đoạn này, bạn cần chỉ định trường nào sẽ là:
   1. cột;
   2. sợi dây;
   3. giá trị để phân tích.

Bạn có thể chọn bất cứ điều gì. Tất cả phụ thuộc vào loại thông tin bạn muốn nhận.

1. Để thêm bất kỳ trường nào, bạn cần nhấp chuột trái vào trường đó và không thả ngón tay ra, hãy kéo trường đó đến khu vực mong muốn. Con trỏ sẽ thay đổi diện mạo của nó.

1. Bạn chỉ có thể thả ngón tay ra khi vòng tròn gạch chéo biến mất. Tương tự, bạn cần kéo và thả tất cả các trường có trong bảng của mình.

1. Để xem toàn bộ kết quả, bạn có thể đóng bảng điều khiển bên cài đặt. Để làm điều này, chỉ cần nhấp vào dấu thập.

1. Kết quả là bạn sẽ thấy như sau. Sử dụng công cụ này, bạn có thể tổng hợp số tiền chi tiêu trong tháng cho từng khoản mục. Ngoài ra, thông tin về kết quả tổng thể có sẵn.

1. Nếu bạn không thích cái bàn, bạn có thể thử xây dựng nó khác đi một chút. Để làm điều này, bạn cần thay đổi các trường trong khu vực xây dựng.

1. Đóng trợ lý xây dựng lại.

1. Lần này chúng ta thấy bảng tổng hợp lớn hơn nhiều vì các cột hiện là danh mục chi phí thay vì tháng.

Nếu bạn không thể tự mình xây dựng bảng, bạn luôn có thể trông cậy vào sự trợ giúp của người biên tập. Trong Excel, có thể tự động tạo các đối tượng như vậy.

Để thực hiện việc này, bạn cần thực hiện như sau, nhưng trước tiên hãy chọn toàn bộ thông tin.

1. Chuyển đến tab "Chèn". Sau đó bấm vào biểu tượng "Bảng". Trong menu xuất hiện, chọn mục thứ hai.

1. Ngay sau đó, một cửa sổ sẽ xuất hiện trong đó có nhiều ví dụ khác nhau để xây dựng. Các tùy chọn tương tự được cung cấp dựa trên một số cột. Số lượng mẫu trực tiếp phụ thuộc vào số lượng của chúng.

1. Khi bạn di chuột qua từng mục, bản xem trước kết quả sẽ có sẵn. Sẽ thuận tiện hơn nhiều khi làm việc theo cách này.

1. Bạn có thể chọn những gì bạn thích nhất.

1. Để chèn tùy chọn đã chọn, chỉ cần nhấp vào nút “OK”.

1. Kết quả là bạn sẽ nhận được kết quả sau.

Xin lưu ý: bảng đã được tạo trên một trang tính mới. Điều này sẽ xảy ra mỗi khi hàm tạo được sử dụng.

Sau khi thêm (bất kể bằng cách nào) PivotTable, bạn sẽ thấy tab Phân tích mới trên thanh công cụ. Nó chứa một số lượng lớn các công cụ và chức năng khác nhau.

Chúng ta hãy xem xét từng người trong số họ chi tiết hơn.

Bằng cách nhấp vào nút được đánh dấu trong ảnh chụp màn hình, bạn có thể thực hiện các thao tác sau:

• đổi tên;

• gọi cửa sổ cài đặt.

Trong cửa sổ tùy chọn bạn sẽ thấy rất nhiều điều thú vị.

Trường hoạt động

Với công cụ này bạn có thể làm như sau:

1. Đầu tiên bạn cần chọn một ô. Sau đó nhấp vào nút "Trường hoạt động". Trong menu xuất hiện, nhấp vào mục “Tùy chọn trường”.

1. Ngay sau đó bạn sẽ thấy cửa sổ sau. Tại đây bạn có thể chỉ định loại thao tác sẽ được sử dụng để tổng hợp dữ liệu trong trường đã chọn.

1. Ngoài ra, bạn có thể tùy chỉnh định dạng số. Để thực hiện việc này, hãy nhấp vào nút thích hợp.

1. Kết quả cửa sổ Format Cells sẽ xuất hiện.

Tại đây bạn có thể chỉ định hình thức bạn muốn hiển thị kết quả phân tích thông tin.

Nhờ công cụ này, bạn có thể thiết lập nhóm theo các giá trị đã chọn.

Chèn lát

Trình chỉnh sửa Microsoft Excel cho phép bạn tạo các bảng tổng hợp tương tác. Trong trường hợp này, bạn không cần phải làm gì phức tạp.

1. Chọn một cột. Sau đó bấm vào nút “Chèn lát”.
2. Ví dụ: trong cửa sổ xuất hiện, hãy chọn một trong các trường được đề xuất (trong tương lai, bạn có thể chọn số lượng không giới hạn trong số đó). Khi một cái gì đó được chọn, nút “OK” sẽ được kích hoạt ngay lập tức. Nhấn vào nó.

1. Kết quả sẽ xuất hiện một cửa sổ nhỏ có thể di chuyển đi bất cứ đâu. Nó sẽ cung cấp tất cả các giá trị duy nhất có thể có trong trường này. Nhờ công cụ này, bạn sẽ chỉ có thể rút số tiền trong một số tháng nhất định (trong trường hợp này). Theo mặc định, thông tin trong toàn bộ thời gian sẽ được hiển thị.

1. Bạn có thể nhấp vào bất kỳ mục nào. Ngay sau đó, tất cả các giá trị trong trường số tiền sẽ thay đổi.

1. Bằng cách này bạn có thể chọn bất kỳ khoảng thời gian nào.

1. Bất cứ lúc nào, mọi thứ đều có thể trở lại hình dạng ban đầu. Để thực hiện việc này, hãy nhấp vào biểu tượng ở góc trên bên phải của cửa sổ này.

Trong trường hợp này, chúng tôi có thể sắp xếp báo cáo theo tháng vì chúng tôi có trường tương ứng. Nhưng có một công cụ mạnh mẽ hơn để làm việc với ngày tháng.

Nếu bạn nhấp vào nút tương ứng trên thanh công cụ, rất có thể bạn sẽ gặp lỗi như thế này. Thực tế là trong bảng của chúng tôi không có ô nào có định dạng dữ liệu “Ngày” một cách rõ ràng.

Ví dụ: hãy tạo một bảng nhỏ với các ngày khác nhau.

Sau đó, bạn sẽ cần xây dựng một bảng tổng hợp.

Chuyển đến tab “Chèn” một lần nữa. Nhấp vào biểu tượng “Bảng”. Trong menu con xuất hiện, chọn tùy chọn chúng tôi cần.

1. Sau đó chúng tôi sẽ được yêu cầu chọn một phạm vi giá trị.

1. Để làm điều này, chỉ cần chọn toàn bộ bảng.

1. Ngay sau đó, địa chỉ sẽ được nhập tự động. Mọi thứ ở đây rất đơn giản vì nó được thiết kế dành cho người mới bắt đầu. Để hoàn tất việc xây dựng, hãy nhấp vào nút “OK”.

1. Trình chỉnh sửa Excel sẽ chỉ cung cấp cho chúng ta một tùy chọn vì bảng này rất đơn giản (ví dụ: không cần thêm tùy chọn nào nữa).

1. Hãy thử nhấp lại vào biểu tượng “Chèn dòng thời gian” (nằm trên tab “Phân tích”).

1. Lần này sẽ không có sai sót. Bạn sẽ được nhắc chọn một trường để sắp xếp. Chọn hộp và nhấp vào nút “OK”.

1. Nhờ đó, một cửa sổ sẽ xuất hiện trong đó bạn có thể chọn ngày mong muốn bằng thanh trượt.

1. Chúng tôi chọn một tháng khác và không có dữ liệu vì tất cả các chi phí trong bảng chỉ được chỉ định cho tháng Ba.

Nếu bạn đã thực hiện bất kỳ thay đổi nào đối với dữ liệu nguồn và vì lý do nào đó nó không xuất hiện trong bảng tổng hợp, bạn luôn có thể cập nhật dữ liệu đó theo cách thủ công. Để thực hiện việc này, chỉ cần nhấp vào nút tương ứng trên thanh công cụ.

Nếu bạn quyết định thay đổi các trường trên cơ sở quá trình xây dựng sẽ diễn ra, thì việc thực hiện việc này trong cài đặt sẽ dễ dàng hơn nhiều thay vì xóa bảng và tạo lại bảng có tính đến các tùy chọn mới.

Để thực hiện việc này, hãy nhấp vào biểu tượng “Nguồn dữ liệu”. Sau đó chọn mục menu cùng tên.

Kết quả là một cửa sổ sẽ xuất hiện trong đó bạn có thể chọn lại lượng thông tin cần thiết.

hành động

Với công cụ này bạn có thể:

• bảng rõ ràng;
• điểm nổi bật;
• di chuyển nó.

Tính toán

Nếu các phép tính trong bảng không đủ hoặc không đáp ứng được nhu cầu của bạn, bạn luôn có thể tự mình thực hiện các thay đổi. Bằng cách nhấp vào biểu tượng của công cụ này, bạn sẽ thấy các tùy chọn sau.

Bao gồm các:

• trường tính toán;

• đối tượng tính toán;

• thứ tự tính toán (các công thức được thêm vào được hiển thị trong danh sách);

• hiển thị công thức (không có thông tin, vì không có công thức bổ sung).

Tại đây bạn có thể tạo biểu đồ tổng hợp hoặc thay đổi loại bảng được đề xuất.

Sử dụng công cụ này, bạn có thể tùy chỉnh giao diện không gian làm việc của trình soạn thảo.

Nhờ đó bạn sẽ có thể:

• định cấu hình hiển thị thanh bên với danh sách các trường;

• bật hoặc tắt các nút cộng/trừ;

• cấu hình hiển thị các tiêu đề trường.

Khi làm việc với bảng trụ, ngoài tab “Phân tích”, một tab khác cũng sẽ xuất hiện – “Nhà thiết kế”. Tại đây bạn có thể thay đổi diện mạo của đối tượng đến mức không thể nhận dạng được so với tùy chọn mặc định.

Có thể được cấu hình:

• tổng phụ:
  • không hiển thị;
  • hiển thị tất cả các tổng số ở phía dưới;
  • hiển thị tất cả các tổng số trong tiêu đề.

• kết quả chung:
  • vô hiệu hóa cho các hàng và cột;
  • kích hoạt cho các hàng và cột;
  • chỉ bật cho các hàng;
  • chỉ kích hoạt cho các cột.

• bố cục báo cáo:
  • hiển thị ở dạng cô đọng;
  • hiển thị ở dạng cấu trúc;
  • hiển thị dưới dạng bảng;
  • lặp lại tất cả các chữ ký phần tử;
  • không lặp lại chữ ký của các phần tử.

• dòng trống:
  • chèn một dòng trống sau mỗi phần tử;
  • xóa một dòng trống sau mỗi phần tử.

• Tùy chọn kiểu bảng tổng hợp (tại đây bạn có thể bật/tắt từng mục):
  • tiêu đề dòng;
  • tiêu đề cột;
  • dòng xen kẽ;
  • cột xen kẽ.

• tùy chỉnh phong cách thiết kế của các yếu tố.

Để xem thêm các tùy chọn khác nhau, bạn cần nhấp vào hình tam giác ở góc dưới bên phải của công cụ này.

Ngay sau đó một danh sách lớn sẽ xuất hiện. Bạn có thể chọn bất cứ điều gì. Khi bạn di chuột qua từng mẫu, bảng của bạn sẽ thay đổi (điều này nhằm mục đích xem trước). Các thay đổi sẽ không có hiệu lực cho đến khi bạn nhấp vào một trong các tùy chọn được cung cấp.

Ngoài ra, nếu muốn, bạn có thể sáng tạo phong cách thiết kế của riêng mình.

Bạn cũng có thể thay đổi thứ tự hiển thị các hàng. Đôi khi điều này là cần thiết để thuận tiện cho việc phân tích chi phí. Đặc biệt nếu danh sách rất lớn, vì việc tìm vị trí cần thiết theo thứ tự bảng chữ cái sẽ dễ dàng hơn là phải cuộn qua danh sách nhiều lần.

Để làm điều này bạn cần phải làm như sau.

1. Bấm vào hình tam giác bên cạnh trường mong muốn.
2. Kết quả là bạn sẽ thấy menu sau. Tại đây bạn có thể chọn tùy chọn sắp xếp mong muốn (“từ A đến Z” hoặc “từ Z đến A”).

Nếu tùy chọn tiêu chuẩn là không đủ, bạn có thể nhấp vào mục “Tùy chọn sắp xếp bổ sung” trong cùng một menu.

Kết quả là bạn sẽ thấy cửa sổ sau. Để biết thêm cài đặt chi tiết, hãy nhấp vào nút “Nâng cao”.

Ở đây mọi thứ đều được cấu hình tự động. Nếu bạn bỏ chọn hộp này, bạn có thể chỉ định khóa bạn cần.

Bảng tổng hợp trong Excel 2003

Các bước trên phù hợp với các biên tập viên hiện đại (2007, 2010, 2013 và 2016). Trong phiên bản cũ, mọi thứ trông khác. Tất nhiên, có ít cơ hội hơn ở đó.

Để tạo bảng tổng hợp trong Excel 2003, bạn cần thực hiện như sau.

1. Đi tới phần menu “Dữ liệu” và chọn mục thích hợp.

1. Kết quả là một trình hướng dẫn tạo các đối tượng như vậy sẽ xuất hiện.

1. Sau khi nhấp vào nút "Tiếp theo", một cửa sổ sẽ mở ra trong đó bạn cần chỉ định một phạm vi ô. Sau đó bấm vào “Tiếp theo” một lần nữa.

1. Để hoàn tất cài đặt, nhấp vào “Hoàn tất”.

1. Kết quả là bạn sẽ thấy như sau. Ở đây bạn cần kéo các trường đến các khu vực thích hợp.

1. Ví dụ, đây có thể là kết quả.

Rõ ràng là việc tạo các báo cáo như vậy sẽ tốt hơn nhiều trong các trình soạn thảo hiện đại.

Phần kết luận

Bài viết này thảo luận về tất cả những điều phức tạp khi làm việc với bảng tổng hợp trong trình chỉnh sửa Excel. Nếu có điều gì đó không ổn với bạn, có thể bạn đang chọn sai trường hoặc có rất ít trường trong số đó - để tạo một đối tượng như vậy, bạn cần một số cột có giá trị lặp lại.

Nếu hướng dẫn này không đủ đối với bạn, bạn có thể tìm thêm thông tin trong trợ giúp trực tuyến của Microsoft.

Video hướng dẫn

Đối với những người vẫn còn câu hỏi chưa được giải đáp, video kèm theo nhận xét về hướng dẫn được mô tả ở trên được đính kèm bên dưới.

Cuộc sống của một người đến từ thế giới công nghệ là sự kết hợp đáng kinh ngạc của những con số và chỉ số đòi hỏi phải hệ thống hóa định kỳ. Để thực hiện được điều sau, bạn phải sử dụng các công cụ kỹ thuật đặc biệt. Bài viết của chúng tôi sẽ chứng minh rằng bảng tổng hợp trong Excel có thể truy cập được ngay cả đối với những người chưa biết.

Một chút lý thuyết

Bảng tổng hợp Excel (dành cho người giả) là một loại sổ đăng ký chứa một phần dữ liệu cụ thể từ một nguồn để phân tích và được mô tả sao cho có thể theo dõi các kết nối logic giữa chúng. Cơ sở cho thiết kế của nó là một danh sách các giá trị cụ thể.

Trước khi bắt đầu công việc, cần chuẩn bị các tài liệu cần thiết để có thể biên soạn để phân tích. Khi tạo phiên bản chuẩn bị, hãy đảm bảo rằng dữ liệu được phân loại, chẳng hạn như các số không bị nhầm lẫn với ký hiệu chữ cái và tất cả các cột đều có tiêu đề.

Bảng tổng hợp trong Excel không thể thiếu đối với những người do tính chất nghề nghiệp phải xử lý số lượng lớn các con số định kỳ cần hệ thống hóa và tạo báo cáo. Excel sẽ giúp bạn tính toán và phân tích một số lượng lớn các giá trị, tiết kiệm thời gian.

Ưu điểm của việc sử dụng kiểu nhóm dữ liệu này:

• trong quá trình vận hành không cần kiến ​​thức lập trình đặc biệt; phương pháp này phù hợp với người giả;
• khả năng kết hợp thông tin từ các nguồn chính khác;
• bạn có thể cập nhật phiên bản cơ bản với thông tin mới bằng cách điều chỉnh một chút các tham số.

Học cách làm việc với bảng tổng hợp trong Excel không mất nhiều thời gian và có thể dựa trên video.

Ví dụ tạo bảng tổng hợp Excel - thuật toán dành cho người mới bắt đầu

Sau khi đã làm quen với các sắc thái lý thuyết cơ bản về bảng tổng hợp trong Excel, hãy chuyển sang áp dụng chúng vào thực tế. Để bắt đầu tạo bảng tổng hợp trong Excel 2016, 2010 hoặc 2007, bạn cần cài đặt phần mềm. Thông thường, nếu bạn sử dụng các chương trình Microsoft Office, Excel đã có sẵn trên máy tính của bạn.

Sau khi khởi động nó, một cánh đồng rộng lớn sẽ mở ra trước mặt bạn, được chia thành một số lượng lớn các ô. Video hướng dẫn ở trên sẽ cho bạn biết thêm về cách tạo bảng tổng hợp trong Excel.

Sử dụng thuật toán sau, chúng ta sẽ xem xét chi tiết một ví dụ về cách tạo bảng tổng hợp trong Excel.
Trong bảng ở đầu cửa sổ, hãy chuyển đến tab “Chèn”, trong đó ở góc bên trái, chúng tôi chọn “Bảng tổng hợp”.


Tiếp theo, một hộp thoại sẽ mở ra trên màn hình nơi bạn cần chỉ định các tham số thích hợp. Có một số điểm quan trọng ở giai đoạn tạo bảng trụ trong Excel này. Nếu trước khi bắt đầu tạo sổ đăng ký, bạn đặt biểu tượng con trỏ trên trang tính thì các dòng trống của cửa sổ sẽ tự động được điền vào. Nếu không, địa chỉ của dải dữ liệu phải được chỉ định độc lập.


Chúng ta hãy xem xét kỹ hơn việc tự điền vào các mục trong hộp thoại.

Chúng ta không để trống dòng đầu tiên, nếu không chương trình sẽ phát sinh lỗi. Nếu có một nguồn mà bạn dự định truyền dữ liệu, hãy chọn nguồn đó trong mục “Sử dụng nguồn dữ liệu ngoài”. Nguồn bên ngoài có nghĩa là một sổ làm việc Excel khác hoặc một tập hợp các mô hình dữ liệu từ DBMS.

Đặt trước cho mỗi cột một tiêu đề.

Chọn vị trí đặt khung tương lai với các ô. Đây có thể là một cửa sổ mới hoặc trang tính này, chúng tôi khuyên bạn nên sử dụng một trang tính khác.


Sau khi bảo mật tất cả các cài đặt, chúng tôi có được một cơ sở làm sẵn. Phía bên trái là khu vực đặt chân đế của khung tương lai. Ở bên phải có cửa sổ cài đặt giúp bạn quản lý sổ đăng ký.


Bây giờ chúng ta cần tìm hiểu xem toàn bộ cấu trúc này được xây dựng như thế nào. Trong cửa sổ cài đặt “Trường bảng miễn phí”, bạn chỉ định dữ liệu sẽ có.


Toàn bộ cấu trúc được xây dựng theo cách dữ liệu văn bản đóng vai trò là yếu tố thống nhất và dữ liệu số hiển thị các giá trị hợp nhất. Ví dụ: hãy kết hợp tất cả các khoản thu theo bộ phận và tìm hiểu xem mỗi người đã nhận được bao nhiêu hàng hóa. Để thực hiện việc này, hãy kiểm tra hai tiêu đề: bộ phận và giá thành hàng hóa trong lô.


Lưu ý cách dữ liệu này nằm ở khu vực phía dưới của bảng cài đặt.


Bộ phận tự động chuyển thành dòng và dữ liệu số thành giá trị. Nếu bạn cố gắng nhấp vào bất kỳ cột nào có số, chúng sẽ xuất hiện trong khu vực này. Và một cột mới sẽ được thêm vào bảng.


Trong trường hợp này, tổng hợp xảy ra. Bây giờ chúng ta có thể biết từ báo cáo của mình có bao nhiêu hàng hóa đã đến từng bộ phận và tổng chi phí của chúng.

Bạn có thể tự do điều chỉnh độ rộng của các cột để định vị dữ liệu một cách tối ưu. Chỉ cần mở rộng chiều rộng cột hoặc chiều cao hàng như bạn đã quen trong Excel.


Nếu không thích hướng này, bạn có thể dùng chuột kéo tên hàng vào vùng cột - chỉ cần di chuột, nhấp chuột trái và kéo.


Đối với kết quả tính toán, số tiền không phải là chức năng duy nhất. Để xem Excel cung cấp những gì khác, hãy nhấp vào tên trong vùng Giá trị và chọn lệnh mới nhất.


Trong các tùy chọn trường giá trị, bạn sẽ tìm thấy nhiều tùy chọn để phân tích.


Đối với mỗi giá trị, bạn có thể chọn chức năng của riêng mình. Ví dụ: hãy thêm trường "Giá" và tìm giá tối đa của sản phẩm trong mỗi gian hàng. Trên thực tế, chúng ta sẽ tìm hiểu xem chiếc đắt nhất có giá bao nhiêu.


Bây giờ chúng ta thấy rằng bộ phận “Phụ kiện” đã nhận được hàng hóa trị giá 267.660 rúp, trong khi bộ phận đắt nhất có giá 2.700 rúp.
Vùng “Bộ lọc” cho phép bạn đặt tiêu chí để chọn bản ghi. Hãy thêm trường “Ngày nhận” bằng cách chỉ cần chọn hộp bên cạnh.


Ngày nay, bảng tổng hợp trong Excel trông có vẻ khó xử nếu chúng ta cần phân tích theo ngày. Do đó, hãy di chuyển ngày từ các hàng sang bộ lọc - chỉ cần kéo và thả như đã chỉ ra ở trên.


Kết quả của những hành động này là sự xuất hiện của một trường khác ở trên cùng. Để chọn ngày, hãy nhấp vào mũi tên bên cạnh từ “Tất cả”.


Bây giờ chúng ta có thể chọn một ngày cụ thể; để mở danh sách, hãy nhấp vào hình tam giác ở góc bên phải.


Bạn cũng có thể chọn giá trị cho bộ phận.


Bỏ chọn những thứ bạn không quan tâm và bạn sẽ chỉ nhận được thông tin bạn cần.

Trong khi làm việc, bạn có thể gặp thông báo như “Tên PivotTable Excel không hợp lệ”. Điều này có nghĩa là hàng đầu tiên của dải ô mà họ đang cố gắng trích xuất thông tin vẫn còn trống các ô. Để giải quyết vấn đề này, bạn phải điền vào các khoảng trống trong cột.

Làm mới dữ liệu trong PivotTable trong Excel

Một câu hỏi quan trọng là làm thế nào để tạo và cập nhật bảng tổng hợp trong Excel 2010 hoặc phiên bản khác. Điều này có liên quan khi bạn định thêm dữ liệu mới. Nếu quá trình cập nhật chỉ diễn ra trên một cột thì bạn cần nhấp chuột phải vào bất kỳ cột nào trên đó. Trong cửa sổ xuất hiện, nhấp vào “Cập nhật”.


Nếu một hành động tương tự cần được thực hiện với nhiều cột và hàng cùng một lúc, hãy chọn bất kỳ vùng nào và mở tab “Phân tích” trên bảng trên cùng và nhấp vào biểu tượng “Cập nhật”. Tiếp theo, chọn hành động mong muốn.


Nếu bạn không cần bảng tổng hợp trong Excel thì bạn nên tìm cách xóa nó. Nó sẽ không khó khăn. Chọn tất cả các thành phần theo cách thủ công hoặc sử dụng phím tắt “CTRL+A”. Tiếp theo, nhấn phím “DELETE” và trường sẽ bị xóa.

Cách thêm cột hoặc bảng vào PivotTable Excel

Để thêm một cột bổ sung, bạn cần thêm cột đó vào dữ liệu nguồn và mở rộng phạm vi cho sổ đăng ký của chúng tôi.


Chuyển đến tab Phân tích và mở nguồn dữ liệu.


Excel sẽ tự đề xuất mọi thứ.


Làm mới và bạn sẽ nhận được danh sách các trường mới trong khu vực cài đặt.

Bạn chỉ có thể thêm bảng nếu bạn “dán” nó vào bảng gốc. Bạn có thể thay thế một phạm vi trong phạm vi hiện có, nhưng bạn không thể thêm một phạm vi khác một cách nhanh chóng. Nhưng bạn có thể tạo một bảng tổng hợp mới dựa trên một số bảng gốc, thậm chí nằm trên các trang tính khác nhau.

Cách tạo bảng tổng hợp trong Excel từ nhiều trang tính

Để thực hiện việc này, chúng ta cần Trình hướng dẫn Bảng tổng hợp. Hãy thêm nó vào bảng truy cập nhanh (phía trên cùng của cửa sổ - ở bên trái). Nhấp vào mũi tên thả xuống và chọn "Lệnh khác".


Chọn tất cả các lệnh.


Và tìm Trình hướng dẫn Bảng Pivot Excel, nhấp vào nó, sau đó nhấp vào “Thêm” và OK.


Biểu tượng sẽ xuất hiện ở trên cùng.


Bạn nên có hai bảng có các trường giống nhau trên các trang tính khác nhau. Chúng tôi có dữ liệu về doanh thu của các phòng ban trong tháng 5 và tháng 6. Bấm vào phím tắt Trình hướng dẫn PivotTable và chọn Hợp nhất Phạm vi.


Chúng ta cần nhiều trường chứ không chỉ một.


Trong bước tiếp theo, chọn phạm vi đầu tiên và nhấp vào nút “Thêm”. Sau đó chuyển sang một trang tính khác (nhấp vào tên của nó bên dưới) và “Thêm” lần nữa. Bạn sẽ có hai phạm vi được tạo.

Bạn không nên chọn toàn bộ bảng. Chúng tôi cần thông tin về các khoản thu của phòng ban, vì vậy chúng tôi đã đánh dấu một phạm vi bắt đầu bằng cột Phòng ban.
Đặt cho mỗi người một cái tên. Nhấp vào vòng tròn 1, sau đó nhập “May” vào trường, nhấp vào vòng tròn 2 và nhập “Tháng 6” vào trường 2. Đừng quên thay đổi phạm vi trong khu vực. Cái chúng ta gọi phải được đánh dấu.

Nhấp vào “Tiếp theo” và tạo trên một trang tính mới.


Sau khi nhấn “Finish” chúng ta sẽ nhận được kết quả. Đây là một bảng đa chiều nên khá khó quản lý. Đó là lý do tại sao chúng tôi chọn phạm vi nhỏ hơn để không bị nhầm lẫn khi đo.


Xin lưu ý rằng chúng tôi không còn tên trường rõ ràng nữa. Chúng có thể được kéo ra bằng cách nhấp vào các mục ở khu vực trên cùng.


Bằng cách bỏ chọn hoặc chọn các hộp, bạn điều chỉnh các giá trị bạn muốn xem. Điều bất tiện là việc tính toán giống nhau cho tất cả các giá trị.

Như bạn có thể thấy, chúng ta có một giá trị trong vùng tương ứng.

Thay đổi cấu trúc báo cáo

Chúng tôi đã xem xét từng bước một ví dụ về cách tạo bảng tổng hợp trong Excel và chúng tôi sẽ cho bạn biết cách lấy thêm một loại dữ liệu khác. Để làm điều này, chúng tôi sẽ thay đổi bố cục báo cáo. Sau khi đặt con trỏ trên bất kỳ ô nào, hãy chuyển đến tab “Nhà thiết kế” rồi đến “Bố cục báo cáo”.

Bạn sẽ có ba lựa chọn để cấu trúc thông tin:

• Dạng cô đặc

Loại chương trình này được áp dụng tự động. Dữ liệu không bị kéo dài nên hầu như không cần phải cuộn qua hình ảnh. Bạn có thể tiết kiệm không gian trên chữ ký và để lại cho số.

• Dạng có cấu trúc

Tất cả các chỉ số được trình bày theo thứ bậc: từ nhỏ đến lớn.

• Dạng bảng

Thông tin được trình bày dưới dạng một sổ đăng ký. Điều này giúp dễ dàng chuyển ô sang trang tính mới.

Sau khi chọn bố cục phù hợp, bạn đảm bảo các điều chỉnh đã thực hiện.

Vì vậy, chúng tôi đã hướng dẫn bạn cách tạo trường bảng tổng hợp trong MS Excel 2016 (năm 2007, 2010, tiến hành tương tự). Chúng tôi hy vọng thông tin này sẽ giúp bạn nhanh chóng phân tích dữ liệu tổng hợp.

Có một ngày tuyệt vời!