Nội dung
Nếu bạn không phải là một người dùng mới làm quen, thì bạn hẳn đã nhận thấy rằng 99% mọi thứ trong Excel được thiết kế để hoạt động với các bảng dọc, nơi các tham số hoặc thuộc tính (trường) đi qua các cột và thông tin về các đối tượng hoặc sự kiện được đặt. trong các dòng. Bảng tổng hợp, tổng phụ, sao chép công thức bằng một cú nhấp đúp - mọi thứ đều được điều chỉnh riêng cho định dạng dữ liệu này.
Tuy nhiên, không có quy tắc nào không có ngoại lệ và với tần suất khá đều đặn, tôi được hỏi phải làm gì nếu một bảng có hướng ngữ nghĩa ngang hoặc một bảng mà các hàng và cột có cùng trọng số về ý nghĩa, xuất hiện trong tác phẩm:
Và nếu Excel vẫn biết cách sắp xếp theo chiều ngang (với lệnh Dữ liệu - Sắp xếp - Tùy chọn - Sắp xếp cột), thì tình hình với lọc còn tồi tệ hơn - đơn giản là không có công cụ tích hợp nào để lọc cột, không phải hàng trong Excel. Vì vậy, nếu bạn phải đối mặt với một nhiệm vụ như vậy, bạn sẽ phải đưa ra các giải pháp giải quyết ở các mức độ phức tạp khác nhau.
Phương pháp 1. Chức năng FILTER mới
Nếu bạn đang sử dụng phiên bản Excel 2021 mới hoặc đăng ký Excel 365, bạn có thể tận dụng tính năng mới được giới thiệu LỌC (LỌC), có thể lọc dữ liệu nguồn không chỉ theo hàng mà còn theo cột. Để hoạt động, hàm này yêu cầu một hàng mảng một chiều ngang bổ trợ, trong đó mỗi giá trị (TRUE hoặc FALSE) xác định xem chúng ta hiển thị hay ngược lại, ẩn cột tiếp theo trong bảng.
Hãy thêm dòng sau vào phía trên bảng của chúng tôi và viết trạng thái của mỗi cột trong đó:
- Giả sử chúng ta luôn muốn hiển thị cột đầu tiên và cột cuối cùng (tiêu đề và tổng), vì vậy đối với chúng trong các ô đầu tiên và cuối cùng của mảng, chúng ta đặt giá trị = TRUE.
- Đối với các cột còn lại, nội dung của các ô tương ứng sẽ là công thức kiểm tra điều kiện chúng ta cần bằng cách sử dụng các hàm И (VÀ) or OR (HOẶC LÀ). Ví dụ, tổng số đó nằm trong khoảng từ 300 đến 500.
Sau đó, nó vẫn chỉ để sử dụng chức năng LỌC để chọn các cột trên đó mảng phụ trợ của chúng tôi có giá trị TRUE:
Tương tự, bạn có thể lọc các cột theo một danh sách nhất định. Trong trường hợp này, hàm sẽ giúp COUNTIF (ĐẾM), kiểm tra số lần xuất hiện của tên cột tiếp theo từ tiêu đề bảng trong danh sách được phép:
Phương pháp 2. Bảng tổng hợp thay vì bảng thông thường
Hiện tại, Excel đã tích hợp sẵn tính năng lọc ngang theo cột trong bảng tổng hợp, vì vậy nếu chúng tôi quản lý để chuyển đổi bảng gốc của mình thành bảng tổng hợp, chúng tôi có thể sử dụng chức năng tích hợp sẵn này. Để làm điều này, bảng nguồn của chúng tôi phải đáp ứng các điều kiện sau:
- có dòng tiêu đề một dòng “đúng” không có ô trống và ô đã hợp nhất - nếu không, nó sẽ không hoạt động để tạo bảng tổng hợp;
- không chứa các bản sao trong nhãn của các hàng và cột - chúng sẽ "thu gọn" trong phần tóm tắt thành một danh sách chỉ có các giá trị duy nhất;
- chỉ chứa các số trong phạm vi giá trị (tại giao điểm của các hàng và cột), vì bảng tổng hợp chắc chắn sẽ áp dụng một số loại hàm tổng hợp cho chúng (tổng, trung bình, v.v.) và điều này sẽ không hoạt động với văn bản
Nếu tất cả các điều kiện này được đáp ứng, thì để xây dựng một bảng tổng hợp trông giống như bảng gốc của chúng ta, nó (bảng gốc) sẽ cần được mở rộng từ bảng chữ cái thành một bảng phẳng (chuẩn hóa). Và cách dễ nhất để thực hiện việc này là sử dụng bổ trợ Power Query, một công cụ chuyển đổi dữ liệu mạnh mẽ được tích hợp vào Excel từ năm 2016.
Đó là:
- Hãy chuyển đổi bảng thành một lệnh động "thông minh" Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng).
- Tải vào Power Query bằng lệnh Dữ liệu - Từ Bảng / Phạm vi (Dữ liệu - Từ Bảng / Phạm vi).
- Chúng tôi lọc dòng có các tổng (bản tóm tắt sẽ có các tổng riêng).
- Nhấp chuột phải vào tiêu đề cột đầu tiên và chọn Bỏ thu gọn các cột khác (Bỏ chia các cột khác). Tất cả các cột không được chọn được chuyển thành hai - tên của nhân viên và giá trị của chỉ số của anh ta.
- Lọc cột với các tổng đã đi vào cột đặc tính.
- Chúng tôi xây dựng một bảng tổng hợp theo bảng phẳng (chuẩn hóa) kết quả bằng lệnh Trang chủ - Đóng và tải - Đóng và tải vào… (Trang chủ - Đóng & Tải - Đóng & Tải vào…).
Giờ đây, bạn có thể sử dụng khả năng lọc các cột có sẵn trong bảng tổng hợp - các dấu kiểm thông thường phía trước tên và mục Bộ lọc Chữ ký (Bộ lọc nhãn) or Bộ lọc theo giá trị (Bộ lọc giá trị):
Và tất nhiên, khi thay đổi dữ liệu, bạn sẽ cần cập nhật truy vấn của chúng tôi và phần tóm tắt bằng phím tắt Ctrl+Khác+F5 hoặc đội Dữ liệu - Làm mới tất cả (Dữ liệu - Làm mới tất cả).
Phương pháp 3. Macro trong VBA
Tất cả các phương pháp trước đó, như bạn có thể dễ dàng thấy, không phải là lọc chính xác - chúng tôi không ẩn các cột trong danh sách ban đầu, nhưng tạo một bảng mới với một tập hợp các cột nhất định từ danh sách ban đầu. Nếu bắt buộc phải lọc (ẩn) các cột trong dữ liệu nguồn, thì cần có một cách tiếp cận khác về cơ bản, cụ thể là macro.
Giả sử chúng ta muốn lọc nhanh các cột trong đó tên của người quản lý trong tiêu đề bảng thỏa mãn mặt nạ được chỉ định trong ô màu vàng A4, chẳng hạn, bắt đầu bằng chữ cái “A” (nghĩa là lấy “Anna” và “Arthur Kết quả là).
Như trong phương pháp đầu tiên, trước tiên chúng ta triển khai một hàng phạm vi bổ trợ, trong đó tiêu chí của chúng ta sẽ được kiểm tra bằng một công thức trong mỗi ô và các giá trị logic TRUE hoặc FALSE sẽ được hiển thị cho các cột hiển thị và ẩn, tương ứng:
Sau đó, hãy thêm một macro đơn giản. Nhấp chuột phải vào tab trang tính và chọn lệnh nguồn (Mã nguồn). Sao chép và dán mã VBA sau vào cửa sổ mở ra:
Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$ A $ 4" Then For Each cell In Range ("D2: O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Đúng Kết thúc Nếu Ô tiếp theo Kết thúc Nếu Kết thúc Sub Logic của nó như sau:
- Nói chung, đây là một trình xử lý sự kiện Bảng tính_Thay đổi, tức là macro này sẽ tự động chạy trên bất kỳ thay đổi nào đối với bất kỳ ô nào trên trang tính hiện tại.
- Tham chiếu đến ô đã thay đổi sẽ luôn ở trong biến Mục tiêu.
- Đầu tiên, chúng tôi kiểm tra xem người dùng đã thay đổi chính xác ô có tiêu chí (A4) hay không - điều này được thực hiện bởi nhà điều hành if.
- Sau đó chu kỳ bắt đầu Cho mỗi… để lặp qua các ô màu xám (D2: O2) với các giá trị chỉ báo TRUE / FALSE cho mỗi cột.
- Nếu giá trị của ô màu xám tiếp theo là TRUE (đúng) thì cột đó không bị ẩn, ngược lại chúng ta ẩn nó (thuộc tính Thành viên ẩn danh).
- Các hàm mảng động từ Office 365: FILTER, SORT và UNIC
- Bảng tổng hợp có tiêu đề nhiều dòng bằng cách sử dụng Power Query
- Macro là gì, cách tạo và sử dụng chúng