Mẹo hay Google trang tính: Cách sử dụng bảng tổng hợp để báo cáo

Bảng tổng hợp cho phép bạn phân tích cú pháp dữ liệu bảng tính thô để hiển thị thông tin cụ thể ở định dạng ngắn gọn, dễ hiểu. Dưới đây là cách sử dụng chúng trong Google Trang tính.

Một trong những cách tốt nhất để làm chủ Google Trang tính – ứng dụng bảng tính mạnh mẽ mà bạn sử dụng trong trình duyệt web – là tìm hiểu cách tạo và sử dụng bảng tổng hợp.

Để mô tả nó một cách tổng quát, bảng tổng hợp lấy dữ liệu từ một nhóm ô trong bảng tính của bạn và trình bày dữ liệu đó theo những cách dễ hiểu và thú vị hơn.

Bảng tổng hợp chủ yếu được sử dụng để biên dịch dữ liệu số trong các ô, nhưng chúng cũng có thể thao tác dữ liệu văn bản. Và chúng hữu ích nhất khi chúng được sử dụng để trích xuất dữ liệu ô từ bảng tính có nhiều cột hoặc hàng.

Tại sao nên sử dụng bảng tổng hợp?

Cách tốt nhất để giải thích chức năng của bảng tổng hợp và lý do bạn muốn tạo một bảng tổng hợp là đưa ra các ví dụ. Dưới đây là bảng tính dữ liệu ô “thô” chưa được sắp xếp. Lưu ý hai cột liệt kê tên sản phẩm và số lượng của từng sản phẩm đã được bán. Doanh số bán hàng cho cùng một tên sản phẩm đã được nhập nhiều lần, vì vậy không thể biết ngay tổng doanh số bán hàng cho từng loại sản phẩm.
 

Dữ liệu thô 

Đây là một bảng tổng hợp đã được tạo từ bảng tính này. Nó được thiết kế để tóm tắt tổng số lượng của từng loại sản phẩm đã bán:
 

Bảng tổng hợp này cộng số lượng của từng sản phẩm đã bán

Bảng tổng hợp tất cả dữ liệu từ cột TÊN SẢN PHẨM và tất cả dữ liệu từ cột SỐ LƯỢNG ĐÃ BÁN, sắp xếp chúng gọn gàng với nhau để có chế độ xem tập trung về số lượng từng sản phẩm đã bán – dữ liệu rất khó nhìn thấy trong bảng tính gốc.

Trong ví dụ về bảng tổng hợp thứ hai của chúng tôi, bên dưới, chúng tôi thấy mức trung bình của tổng doanh số bán sản phẩm đã được ghi có cho mỗi nhân viên bán hàng. Một lần nữa, bảng tổng hợp lấy thông tin không thể thu thập nhanh chóng từ bảng tính gốc.
 

Bảng tổng hợp này tính toán giá trị bán hàng trung bình cho mỗi nhân viên bán hàng

Chuẩn bị bảng tính của bạn cho các bảng tổng hợp

Để tạo các bảng tổng hợp hữu ích trong Google Trang tính, tốt nhất bạn nên chuẩn bị bảng tính của mình cho chúng. Bảng tính phải có các yếu tố lý tưởng sau trong định dạng của nó:

• Nó chứa dữ liệu “thô” – nghĩa là bảng tính thiếu biểu đồ, đồ họa, bảng hoặc các yếu tố linh tinh khác.
• Không có cột hoặc hàng trống giữa các cột/hàng chứa dữ liệu bên trong các ô của chúng.
• Các cột hoặc hàng mà bạn chọn cho bảng tổng hợp để biên dịch dữ liệu từ đó có tiêu đề rõ ràng. Mặc dù bạn có thể chọn các cột hoặc hàng cụ thể theo cách thủ công, nhưng tốt nhất là bảng tổng hợp được phép trích xuất tất cả dữ liệu ô xuất hiện trên bảng tính. Các tiêu đề rõ ràng làm cho điều đó dễ dàng hơn.

Về cơ bản, bảng tính của bạn càng đơn giản và dễ hiểu thì càng tốt.

Để chuẩn bị bảng tính của bạn để nó có thể được sử dụng để tạo bảng tổng hợp một cách dễ dàng, trước tiên hãy tạo một bản sao của tệp bảng tính — hoặc tạo một bản sao của trang tính có dữ liệu ô mà bạn muốn thao tác trong bảng tổng hợp.

Để tạo bản sao bảng tính của bạn: Mở bảng tính trong Google Trang tính. Ở phía trên bên trái, chọn Tệp > Tạo bản sao . Trên bảng điều khiển mở ra, hãy nhập tên mới cho bản sao và nhấp vào Tạo bản sao . Một tab trình duyệt mới sẽ mở ra với bản sao mới của bảng tính của bạn.

Để sao chép một trang tính trong bảng tính của bạn: Mở bảng tính trong Google Trang tính. Dọc theo cuối bảng tính của bạn, nhấp vào mũi tên xuống trên tab của trang tính mà bạn muốn sao chép. Từ menu mở ra, chọn Nhân bản . Sau đó, một bản sao của trang tính này sẽ được thêm vào bảng tính của bạn, được đặt ở bên phải của trang tính gốc.
 

Tạo một trang tính trùng lặp trong bảng tính

Để đặt tên duy nhất cho bản sao trang tính mới, hãy nhấp vào mũi tên xuống trên tab của trang tính đó. Từ menu mở ra, chọn Đổi tên và nhập tên mới.

Sau đó, bạn có thể chỉnh sửa bản sao của bảng tính hoặc trang tính của mình mà không làm thay đổi bản gốc. Xóa tất cả các biểu đồ, đồ họa, bảng và các yếu tố linh tinh khác. Xóa các cột và hàng trống. Và kiểm tra và cập nhật tên của các tiêu đề của cột và hàng.

Sử dụng công cụ Khám phá để tạo bảng tổng hợp

Khi bảng tính của bạn đang mở trong Google Trang tính, biểu tượng Khám phá ở phía dưới bên phải có thể xuất hiện dưới dạng màu xanh lục. Điều này có nghĩa là công cụ Khám phá của Google có bảng tổng hợp được đề xuất cho bạn. Nhấp vào biểu tượng này sẽ mở thanh bên Khám phá ở phía bên phải.

Cuộn xuống phần BẢNG PIVOT của thanh bên để xem hình thu nhỏ của bảng tổng hợp được tạo bởi Google Trang tính. Nhấp vào THÊM (nếu từ này xuất hiện; có thể không) sẽ hiển thị cho bạn nhiều bảng tổng hợp hơn do Google Trang tính tạo.

 

Công cụ Khám phá của Google có thể đề xuất bảng tổng hợp

Di chuyển con trỏ qua hình thu nhỏ của bảng tổng hợp. Hai biểu tượng sẽ xuất hiện phía trên bên phải của hình thu nhỏ:
 

Nhấp vào biểu tượng lưới để chèn bảng tổng hợp vào trang tính của bạn hoặc biểu tượng kính lúp để xem kỹ hơn

Biểu tượng lưới: Biểu tượng này sẽ đặt bảng tổng hợp được đề xuất trên bảng tính của bạn. Bạn có thể chọn đặt bảng tổng hợp trên trang tính hiện tại mà bạn đang xem hoặc trên một trang tính mới.

Trong hầu hết các trường hợp, tốt nhất là đặt một bảng tổng hợp mới vào một trang tính mới — cho dù đó là bảng tổng hợp do công cụ Khám phá này đề xuất hay bảng tổng hợp do bạn tạo từ đầu (hướng dẫn này sẽ đi sâu vào bên dưới). Việc đặt bảng tổng hợp trên một trang tính đã có dữ liệu ô trên đó có thể khiến toàn bộ trang tính trở nên khó hiểu và khó quản lý. Nó cũng có thể làm rối các hoạt động của bảng tổng hợp.

Biểu tượng kính lúp: Thao tác này sẽ mở hình ảnh lớn hơn của hình thu nhỏ bảng tổng hợp trên bảng tính của bạn. Điều này cung cấp cho bạn bản xem trước tốt hơn của bảng tổng hợp được đề xuất.

Tạo một bảng tổng hợp từ đầu

Nếu bạn không nhìn thấy bảng tổng hợp mà bạn muốn tạo trong thanh bên Khám phá, thật dễ dàng để tạo bảng tổng hợp của riêng bạn. Bạn có thể chọn các cột hoặc hàng trong bảng tính mà bạn muốn bảng tổng hợp trích xuất dữ liệu từ đó (bao gồm cả tiêu đề của chúng) hoặc bạn có thể thử bỏ qua bước này, đặc biệt nếu bạn đã chỉnh sửa bảng tính theo định dạng đề xuất được mô tả trước đó .

Sau đó, trên thanh menu trên bảng tính của bạn, hãy chọn Chèn > Bảng tổng hợp . Cửa sổ “Tạo bảng tổng hợp” xuất hiện. Nếu bạn đã chọn một số ô nhất định trong trang tính để trích xuất dữ liệu, thì lựa chọn đó sẽ xuất hiện trong trường “Phạm vi dữ liệu”. Nếu bạn không chọn bất kỳ dữ liệu nào, trường đó sẽ hiển thị tập dữ liệu bảng tính đầy đủ.
 

Tạo một bảng tổng hợp mới

Như đã đề cập ở trên, tốt hơn hết bạn nên chèn bảng tổng hợp của mình vào một trang tính mới, vì vậy hãy chọn “Trang tính mới”, sau đó nhấp vào Tạo .

Bạn sẽ được đưa đến một trang tính mới với thanh bên “Trình chỉnh sửa bảng tổng hợp” mở dọc theo bên phải. Trình chỉnh sửa có hai cột. Cột bên phải có hộp tìm kiếm dọc theo đầu cột. Bên dưới này, nó liệt kê các tiêu đề của cột hoặc hàng mà bạn đã chọn cho bảng tổng hợp này để trích xuất dữ liệu ô — hoặc, nếu bạn không chọn bất kỳ cột hoặc hàng nào, nó sẽ liệt kê tất cả các tiêu đề trong trang tính. Cột tìm kiếm này hữu ích để tìm một tiêu đề cụ thể trên bảng tính mà bạn muốn đưa vào bảng tổng hợp của mình, đặc biệt nếu bảng tính của bạn cực lớn.
 

Trình chỉnh sửa bảng tổng hợp

Ở cột bên trái của trình chỉnh sửa bảng tổng hợp, bạn có thể sẽ thấy một vài đề xuất bảng tổng hợp mà Google Trang tính đã phân tích cú pháp từ tập dữ liệu của bạn. Đây có thể sẽ là những gợi ý tương tự đã xuất hiện trong thanh bên Khám phá trước đó. Trong ví dụ về doanh số bán sản phẩm, Trang tính gợi ý “MỖI CHI PHÍ TRUNG BÌNH cho mỗi NGƯỜI BÁN”, “Tổng SỐ TIỀN ĐÃ BÁN cho mỗi TÊN SẢN PHẨM” và “Trung bình của TỔNG DOANH THU cho mỗi NGƯỜI BÁN”. Nhấp vào bất kỳ trong số chúng để xem bảng tổng hợp hoàn chỉnh đó.

Dưới đây, bạn sẽ tìm thấy bốn loại phần tử mà bạn có thể thêm vào bảng tổng hợp theo cách thủ công:

Hàng và cột

Khi bạn nhấp vào nút Thêm ở bên phải của các mục “Hàng” hoặc “Cột”, một menu sẽ mở ra liệt kê các tiêu đề của cột và hàng trong phạm vi dữ liệu cho bảng tổng hợp này. Dữ liệu ô bên dưới tiêu đề bạn chọn sẽ được thêm vào bảng tổng hợp của bạn dưới dạng hàng hoặc cột.

Hơi khó hiểu, nếu bạn thêm phần tử Hàng, một cột sẽ được tạo cho tiêu đề bạn chọn và dữ liệu ô của cột đó sẽ xuất hiện trong các hàng bên dưới. Nếu bạn thêm phần tử Cột, một hàng sẽ được tạo cho tiêu đề bạn chọn, với dữ liệu ô xuất hiện dưới dạng các cột ở bên phải.

Các phần tử Hàng và Cột không thực hiện các phép tính khi chúng được thêm vào bảng tổng hợp, nhưng chúng sẽ loại bỏ các phần tử trùng lặp.

Trong ví dụ bán hàng, đi tới Hàng , nhấp vào Thêm bên cạnh và chọn TÊN SẢN PHẨM từ menu bật lên sẽ thêm cột TÊN SẢN PHẨM với tên sản phẩm xuất hiện dưới dạng các hàng bên dưới. Chỉ có một hàng cho mỗi tên sản phẩm.

 

Bảng tổng hợp hiện có cột TÊN SẢN PHẨM với một hàng cho mỗi sản phẩm

Bạn cũng sẽ thấy các hộp cài đặt cho các thành phần bạn đã thêm xuất hiện bên dưới “Hàng” hoặc “Cột” trong trình chỉnh sửa bảng tổng hợp. Có một số cách bạn có thể điều chỉnh các yếu tố này:

Thứ tự: Nhấp vào menu thả xuống này cho phép bạn chọn Tăng dần hoặc Giảm dần . Ví dụ: nếu dữ liệu ô dưới tiêu đề cho hàng hoặc cột này bao gồm các số, thì giá trị của chúng sẽ được sắp xếp theo thứ tự tăng dần (tăng dần) hoặc giảm dần (giảm dần). Nếu các ô chứa văn bản, thì chúng sẽ được liệt kê theo thứ tự bảng chữ cái (tăng dần) hoặc theo thứ tự bảng chữ cái đảo ngược (giảm dần).

Sắp xếp theo: Nếu có hai hoặc nhiều phần tử hàng hoặc cột trong bảng tổng hợp của bạn, bạn có thể chọn phần tử nào được ưu tiên cho chức năng Đặt hàng và các hàng hoặc cột khác sẽ theo sau phần tử đó. Tại thời điểm này trong ví dụ, chỉ có một cột, vì vậy sẽ quay lại chức năng này sau.

Hiển thị tổng số: Nếu dữ liệu ô trong tiêu đề này bao gồm các số và bạn chọn hộp này, thì một hàng hoặc cột sẽ được thêm vào bảng tổng hợp liệt kê tổng số của các số này.

Để xóa một phần tử hàng hoặc cột, hãy nhấp vào X ở phía trên bên phải của hộp cài đặt.

Giá trị

Để thực hiện các phép tính trên dữ liệu trong bảng tổng hợp, bạn cần thêm phần tử Giá trị. Khi bạn nhấp vào nút Thêm ở bên phải phần “Giá trị”, một menu sẽ mở ra liệt kê các tiêu đề của cột và hàng trong phạm vi dữ liệu cho bảng tổng hợp này. Trong hầu hết các trường hợp, bạn sẽ muốn chọn tiêu đề cho cột hoặc hàng có giá trị số.

Khi bạn chọn một tiêu đề, một cột có tiêu đề này sẽ được thêm vào bảng tổng hợp của bạn. Dữ liệu ô bên dưới tiêu đề đó sẽ được tính bằng công thức mà bạn chọn.

Khi bạn thêm một cột giá trị vào bảng tổng hợp, hộp cài đặt cho cột đó sẽ xuất hiện trong phần “Giá trị” của trình chỉnh sửa bảng tổng hợp, với các chức năng sau:

Tóm tắt bằng cách: Nhấp vào trình đơn thả xuống này sẽ mở ra một trình đơn gồm nhiều loại tính toán. Những cái bạn có thể sẽ sử dụng nhiều nhất là SUM và AVERAGE. Nếu dữ liệu ô bên dưới tiêu đề bạn đã chọn bao gồm các số, thì SUM sẽ cộng các số này. AVERAGE sẽ tính trung bình cộng của những con số này.

Khi bạn chọn một tiêu đề có dữ liệu số bên dưới khi bạn đang thêm một cột giá trị, Trang tính sẽ chọn hàm SUM theo mặc định. Trong ví dụ về số liệu bán hàng, đi tới Giá trị , nhấp vào nút Thêm của nó và chọn SỐ TIỀN ĐÃ BÁN sẽ thêm cột “TỔNG SỐ TIỀN ĐÃ BÁN” với các số bán hàng cho từng sản phẩm được thêm vào và hiển thị bên cạnh mỗi tên sản phẩm.

Hiển thị dưới dạng: Theo mặc định, Trang tính hiển thị các phép tính trong cột giá trị dưới dạng số. Thay vào đó, trình đơn thả xuống này cho phép bạn hiển thị phép tính giá trị dưới dạng phần trăm – đại diện cho phần trăm của hàng hoặc cột chứa hình hoặc phần trăm của tổng số.
 

Có thể điều chỉnh các giá trị trong cột giá trị để hiển thị dưới dạng phần trăm thay vì số

Để xóa một cột giá trị, hãy nhấp vào X ở phía trên bên phải của hộp cài đặt.

Bây giờ chúng ta có hai cột được hiển thị trong bảng trụ mẫu của mình, hãy quay lại hộp cài đặt cho phần tử Hàng mà chúng ta đã thêm. Các mục trong cột “TÊN SẢN PHẨM” được liệt kê theo thứ tự bảng chữ cái tăng dần. Nhưng nếu bây giờ chúng ta đặt trường “Sắp xếp theo” thành TỔNG SỐ TIỀN ĐÃ BÁN , thì các số trong cột “TỔNG SỐ TIỀN ĐÃ BÁN” sẽ được liệt kê theo thứ tự tăng dần. Thứ tự của các mặt hàng trong cột “TÊN SẢN PHẨM” sẽ thay đổi để tương ứng chính xác với số lượng bán hàng tương ứng của chúng.
 

Thay đổi danh sách thả xuống

Bộ lọc

Bộ lọc cho phép bạn loại trừ các mục hoặc giá trị nhất định trong tập dữ liệu gốc khỏi bảng tổng hợp của mình để trình bày tập dữ liệu rõ ràng hơn. Để có hướng dẫn đầy đủ về cách thức hoạt động của các bộ lọc và cách tận dụng tối đa chúng, hãy xem “ Mẹo hữu ích cho Google Trang tính: Cách sử dụng bộ lọc và bộ cắt”. Nhưng đây là tổng quan nhanh về cách sử dụng chúng trong bảng tổng hợp.

Khi bạn nhấp vào nút Thêm ở bên phải của phần “Bộ lọc”, một menu sẽ mở ra liệt kê các tiêu đề của cột và hàng trong phạm vi dữ liệu cho bảng tổng hợp này. Chọn tiêu đề cho một trong các thành phần (Hàng, Cột hoặc Giá trị) mà bạn đã thêm vào bảng tổng hợp. Khi bạn chọn một tiêu đề từ menu này, hộp cài đặt bộ lọc cho tiêu đề sẽ được thêm vào trình chỉnh sửa bảng tổng hợp. Để chứng minh điều này, sẽ thêm bộ lọc cho tiêu đề TÊN SẢN PHẨM.

Mỗi bộ lọc bạn thêm có một chức năng “Trạng thái” duy nhất trong hộp cài đặt của nó. Nhấp vào menu thả xuống có nội dung Hiển thị tất cả các mục để mở bảng điều khiển cho phép bạn lọc dữ liệu theo tiêu đề mà bạn đã chọn.
 

Hãy nhấp vào Hiển thị tất cả các mục để xem các tùy chọn lọc

Có hai cách để lọc: theo giá trị hoặc theo điều kiện. Lọc theo giá trị cho phép bạn chọn các số hoặc từ cụ thể mà bạn muốn bao gồm hoặc loại trừ khỏi tập dữ liệu. Bạn có thể sử dụng hộp tìm kiếm trên bảng điều khiển bật lên từ hộp cài đặt của bộ lọc để tìm kiếm các số hoặc từ xuất hiện trong các ô bên dưới tiêu đề mà bạn đã chọn. Nếu bạn muốn một số hoặc mục văn bản xuất hiện trong bảng tổng hợp, hãy để nó được chọn. Nếu bạn muốn ẩn nó, hãy bỏ chọn nó. Sau đó bấm OK .

Nếu bạn bỏ chọn một số hoặc mục văn bản, hàng cho ô chứa số hoặc văn bản mà bạn đã bỏ chọn sẽ bị xóa khỏi bảng tổng hợp. Đừng lo — hàng này chưa bị xóa. Bộ lọc bạn tạo này đã ẩn hàng này, hiển thị bảng tổng hợp của bạn mà không có nó. Trong ví dụ bên dưới, đã bỏ chọn Thingmabobs để loại trừ những số liệu bán hàng đó khỏi bảng tổng hợp.
 

Hàng Thingmabobs đã được lọc ra khỏi bảng tổng hợp

Lọc theo điều kiện cho phép bạn chỉ hiển thị dữ liệu đáp ứng các tiêu chí nhất định, chẳng hạn như chứa các từ nhất định hoặc có giá trị trên hoặc dưới một số nhất định. Giả sử bạn muốn loại trừ bất kỳ doanh số nào có ít hơn 11 mặt hàng khỏi tổng số trong bảng tổng hợp của mình. Để làm điều này, bạn phải lọc dữ liệu SỐ TIỀN ĐÃ BÁN để chỉ hiển thị các ô chứa các số lớn hơn 10.

Trong phần Bộ lọc, nhấp vào nút Thêm và chọn tiêu đề SỐ TIỀN ĐÃ BÁN từ menu bật lên. Trong hộp bộ lọc SỐ TIỀN ĐÃ BÁN, nhấp vào Hiển thị tất cả các mặt hàng . Trên ngăn xuất hiện, nhấp vào Lọc theo điều kiện , sau đó nhấp vào hộp Không có bên trong. Từ danh sách menu dài gồm các biến bộ lọc mở ra, hãy cuộn xuống và chọn Lớn hơn . Bên trong hộp nhập bên dưới “Lớn hơn”, nhập 10 . Cuộn xuống cuối bảng điều khiển và nhấp vào OK.
 

Bộ lọc này sẽ chỉ bao gồm các số lớn hơn 10 khi tính cột giá trị SỐ TIỀN ĐÃ BÁN

Giờ đây, bảng tổng hợp của bạn hiển thị các phép tính chỉ sử dụng các ô đó từ phạm vi dữ liệu trong bảng tính gốc chứa các số lớn hơn 10. Các ô trong bảng tính gốc chứa các số nhỏ hơn 10 không được sử dụng trong phép tính cho bảng tổng hợp.
 

Bảng tổng hợp hiện hiển thị các số thấp hơn trong cột “SUM of AMOUNT SOLD”

Để xóa bộ lọc, hãy nhấp vào X ở phía trên bên phải của hộp cài đặt.

Quản lý các bảng tổng hợp của bạn

Chỉnh sửa bảng tổng hợp: Bất cứ khi nào bạn cần chỉnh sửa cài đặt của bảng tổng hợp, chỉ cần di chuột qua bảng tổng hợp và nhấp vào nút Chỉnh sửa xuất hiện ngay bên dưới bảng tổng hợp. Thanh bên cho trình chỉnh sửa bảng tổng hợp sẽ mở dọc theo bên phải. Để đóng thanh bên, nhấp vào X ở phía trên bên phải của nó.

 

Di chuột qua bảng tổng hợp và nhấp vào nút Chỉnh sửa

Giữ các bảng tổng hợp trên các trang tính riêng biệt: Hãy nhớ rằng, tốt nhất là đặt mỗi bảng tổng hợp trên một trang tính riêng biệt để giảm sự lộn xộn. Điều này cũng có thể giúp làm cho bảng tính tổng thể của bạn linh hoạt hơn: Bạn có thể tạo một số bảng tổng hợp — mỗi bảng được đặt trên trang tính riêng của nó, biên dịch dữ liệu ô thô từ trang tính chính của bạn khác với các bảng tổng hợp khác. Sau đó, bạn có thể chuyển đổi giữa các bảng tổng hợp khác nhau này bằng cách nhấp qua các tab của chúng.
Nguồn: computerworld.com