Nội dung
Bảng tổng hợp phù hợp với tất cả mọi người - chúng tính toán nhanh chóng và được cấu hình linh hoạt và thiết kế có thể được đưa vào chúng một cách trang nhã, nếu cần. Nhưng cũng có một số ít trong thuốc mỡ, cụ thể là không có khả năng tạo tóm tắt, trong đó vùng giá trị không được chứa số mà là văn bản.
Chúng ta hãy cố gắng vượt qua hạn chế này và nghĩ ra “một vài chiếc nạng” trong tình huống tương tự.
Giả sử công ty của chúng tôi vận chuyển các sản phẩm của mình trong các container đến một số thành phố ở Nước ta và Kazakhstan. Các thùng chứa được gửi không quá một lần một tháng. Mỗi vùng chứa có một số chữ và số. Theo dữ liệu ban đầu, có một bảng tiêu chuẩn liệt kê các lần giao hàng, từ đó bạn cần thực hiện một số loại tóm tắt để thấy rõ số lượng container được gửi đến từng thành phố và từng tháng:
Để thuận tiện, hãy tạo bảng với dữ liệu ban đầu "thông minh" trước bằng cách sử dụng lệnh Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng) và đặt cho cô ấy một cái tên Giao hàng chuyển hướng xây dựng (Thiết kế). Trong tương lai, điều này sẽ đơn giản hóa cuộc sống, bởi vì. nó sẽ có thể sử dụng tên của bảng và các cột của nó trực tiếp trong các công thức.
Phương pháp 1. Sử dụng Power Query đơn giản nhất
Power Query là một công cụ siêu mạnh để tải và chuyển đổi dữ liệu trong Excel. Phần bổ trợ này đã được tích hợp vào Excel theo mặc định từ năm 2016. Nếu bạn có Excel 2010 hoặc 2013, bạn có thể tải xuống và cài đặt riêng (hoàn toàn miễn phí).
Toàn bộ quá trình, để rõ ràng, tôi đã phân tích từng bước trong video sau:
Nếu không thể sử dụng Power Query, thì bạn có thể thực hiện các cách khác - thông qua bảng tổng hợp hoặc các công thức.
Phương pháp 2. Tóm tắt bổ trợ
Hãy thêm một cột nữa vào bảng ban đầu của chúng tôi, nơi sử dụng một công thức đơn giản, chúng tôi tính số lượng của mỗi hàng trong bảng:
Rõ ràng, -1 là cần thiết, bởi vì chúng tôi có tiêu đề một dòng trong bảng của chúng tôi. Nếu bảng của bạn không nằm ở đầu trang tính, thì bạn có thể sử dụng một công thức phức tạp hơn một chút, nhưng phổ biến hơn để tính toán sự khác biệt về số lượng của hàng hiện tại và tiêu đề bảng:
Bây giờ, theo cách tiêu chuẩn, chúng tôi sẽ xây dựng bảng tổng hợp của loại mong muốn dựa trên dữ liệu của chúng tôi, nhưng trong trường giá trị, chúng tôi sẽ bỏ trường Số dòng thay vì những gì chúng ta muốn chứa:
Vì chúng tôi không có nhiều container trong cùng một thành phố trong cùng một tháng, trên thực tế, bản tóm tắt của chúng tôi sẽ không đưa ra số lượng mà là số dòng của các container mà chúng tôi cần.
Ngoài ra, bạn có thể tắt tổng phụ và tổng trên tab Khối mã lệnh - Tổng số chung и Subtotals (Thiết kế - Tổng tổng, Tổng phụ) và ở cùng một vị trí, chuyển tóm tắt sang bố cục bảng thuận tiện hơn bằng nút Báo cáo mô hình (Bố cục Báo cáo).
Như vậy, chúng ta đã đi được nửa chặng đường đến kết quả: chúng ta có một bảng trong đó, ở giao điểm của thành phố và tháng, có một số hàng trong bảng nguồn, nơi chứa mã vùng chứa mà chúng ta cần.
Bây giờ, hãy sao chép bản tóm tắt (sang cùng một trang tính hoặc trang tính khác) và dán nó dưới dạng các giá trị, sau đó nhập công thức của chúng ta vào vùng giá trị, vùng này sẽ trích xuất mã vùng chứa theo số dòng được tìm thấy trong bản tóm tắt:
Chức năng IF (NẾU), trong trường hợp này, hãy kiểm tra xem ô tiếp theo trong bản tóm tắt có trống không. Nếu trống, thì xuất một chuỗi văn bản trống “”, tức là để trống ô. Nếu không trống, sau đó trích xuất từ cột Container bảng nguồn Giao hàng nội dung ô theo số hàng bằng cách sử dụng hàm INDEX (MỤC LỤC).
Có lẽ điểm duy nhất không rõ ràng ở đây là từ kép Container trong công thức. Một hình thức viết kỳ lạ như vậy:
Quân nhu[[Thùng đựng hàng]:[Thùng đựng hàng]]
… Chỉ cần thiết để tham chiếu cột Container là tuyệt đối (giống như một tham chiếu có dấu $ cho các bảng “không thông minh” thông thường) và không trượt sang các cột lân cận khi sao chép công thức của chúng tôi sang bên phải.
Trong tương lai, khi thay đổi dữ liệu trong bảng nguồn Giao hàng, chúng ta phải nhớ cập nhật bản tóm tắt bổ trợ của mình với số dòng bằng cách nhấp chuột phải vào nó và chọn lệnh Cập nhật & Lưu (Làm mới).
Phương pháp 3. Công thức
Phương pháp này không yêu cầu tạo bảng tổng hợp trung gian và cập nhật thủ công, nhưng sử dụng "vũ khí hạng nặng" của Excel - hàm TÓM TẮT (SUMIFS). Thay vì tìm kiếm số hàng trong bản tóm tắt, bạn có thể tính toán chúng bằng công thức sau:
Trên thực tế, với một số cồng kềnh bên ngoài, đây là trường hợp sử dụng tiêu chuẩn cho hàm tổng hợp có chọn lọc TÓM TẮTA tính tổng các số hàng cho thành phố và tháng nhất định. Một lần nữa, vì chúng ta không có nhiều container ở cùng một thành phố trong cùng một tháng, nên trên thực tế, hàm của chúng ta sẽ không đưa ra số lượng, mà chính là số dòng. Và sau đó hàm đã quen thuộc từ phương pháp trước INDEX Bạn cũng có thể trích xuất mã vùng chứa:
Tất nhiên, trong trường hợp này, bạn không cần phải suy nghĩ về việc cập nhật bản tóm tắt nữa, nhưng trên các bảng lớn, hàm TỔNG HỢP có thể chậm đáng kể. Sau đó, bạn sẽ phải tắt tính năng tự động cập nhật công thức hoặc sử dụng phương pháp đầu tiên - bảng tổng hợp.
Nếu hình thức của bản tóm tắt không phù hợp lắm với báo cáo của bạn, thì bạn có thể trích xuất số hàng từ nó vào bảng cuối cùng không trực tiếp, như chúng tôi đã làm, nhưng bằng cách sử dụng hàm GET.PIVOT.TABLE.DATA (NHẬN.PIVOT.DATA). Làm thế nào để làm điều này có thể được tìm thấy ở đây.
- Cách tạo báo cáo bằng bảng tổng hợp
- Cách thiết lập tính toán trong bảng tổng hợp
- Đếm có chọn lọc với SUMIFS, COUNTIFS, v.v.