Trích xuất dữ liệu với cột trợ giúp trong excel

 

 

 

>> sơ đồ tài khoản 632

 

>> tra mã số thuế cá nhân

 

>> biên bản đối chiếu công nợ excel

 

 

 

Một cách để trích xuất dữ liệu trong Excel là sử dụng INDEX và MATCH với cột trợ giúp đánh dấu dữ liệu phù hợp. Điều này tránh sự phức tạp của một công thức mảng tiên tiến hơn.

 

 

 

 

Thử thách với công thức trích xuất là quản lý các bản sao (nghĩa là nhiều kết quả phù hợp). Các công thức tra cứu như VLOOKUP và INDEX MATCH có thể dễ dàng tìm thấy kết quả phù hợp đầu tiên, nhưng khó tìm kiếm hơn “tất cả các kết quả phù hợp” khi tiêu chí chọn nhiều hơn một kết quả phù hợp.

 

Công thức này đề cập đến thử thách này bằng cách sử dụng cột trợ giúp trả về một giá trị số có thể được sử dụng để dễ dàng trích xuất nhiều kết quả phù hợp.

 

 

 

Cột trình trợ giúp kiểm tra từng hàng trong dữ liệu để xem liệu Bộ trong cột C có khớp với giá trị trong I3 và Tòa nhà trong cột D khớp với giá trị trong J3 hay không. Cả hai phép thử logic phải trả về TRUE để AND trả về TRUE.

 

Đối với mỗi hàng, kết quả của AND được thêm vào “giá trị ở trên” trong cột trình trợ giúp để tạo số lượng. Hiệu quả thực tế của công thức này là một bộ đếm gia tăng chỉ thay đổi khi tìm thấy một kết quả (mới). Sau đó, giá trị vẫn giữ nguyên cho đến khi kết quả khớp tiếp theo được tìm thấy. Điều này làm việc vì kết quả TRUE / FALSE trả về bởi AND được ép buộc thành 1/0 giá trị như là một phần của phép tính tổng. Kết quả FALSE không thêm gì và kết quả TRUE thêm 1.

 

 

 

Làm việc từ trong ra ngoài, phần INDEX MATCH của công thức tìm kiếm tên cho kết quả khớp đầu tiên được tìm thấy, sử dụng số hàng trong cột G làm giá trị khớp:

 

 

INDEX nhận tất cả 3 cột dữ liệu dưới dạng mảng (có tên là “dữ liệu”) và MATCH được cấu hình để khớp với số hàng bên trong cột trợ giúp (phạm vi có tên “helper”) trong chế độ đối sánh chính xác (đối số thứ 3 được đặt thành 0) .

 

Đây là nơi sự thông minh của công thức trở nên rõ ràng. Cột helper rõ ràng chứa các bản sao, nhưng nó không quan trọng, vì MATCH sẽ chỉ khớp với giá trị đầu tiên. Theo thiết kế, mỗi “giá trị đầu tiên” tương ứng với hàng chính xác trong bảng dữ liệu.

 

Các công thức trong các cột I và J đều giống như H, ngoại trừ số cột, được tăng lên trong từng trường hợp một.

 

Câu lệnh IF kết thúc tốt công thức INDEX / MATCH thực hiện một hàm đơn giản – nó kiểm tra từng số hàng trong vùng khai thác để xem số hàng có nhỏ hơn hay bằng giá trị trong G3 (có tên là “ct”), tổng số của tất cả các bản ghi phù hợp. Nếu vậy, logic INDEX / MATCH được chạy. Nếu không, IF sẽ xuất ra một chuỗi rỗng (“”).

 

 

Vì giá trị lớn nhất trong cột trợ giúp giống với tổng số đối sánh, hàm MAX là tất cả những gì chúng ta cần. 

 

Lưu ý: khu vực khai thác cần được cấu hình thủ công để xử lý nhiều dữ liệu khi cần thiết (nghĩa là 5 hàng, 10 hàng, 20 hàng, v.v.). Trong ví dụ này, nó được giới hạn trong 5 hàng duy nhất để giữ cho trang tính gọn nhẹ.

 

Tôi đã học được kỹ thuật này trong   cuốn sách Control Shift Enter của Mike Girvin .