đào tạo kế toán cấp tốc




truy Lượt Xem:10088
GIẢM 50% LỚP HỌC KẾ TOÁN THỰC HÀNH, TỔNG HỢP xem ngay
NHẬN GIA SƯ KẾ TOÁN THEO YÊU CẦU MỌI TRÌNH ĐỘ xem ngay
NHẬN LÀM DỊCH VỤ KẾ TOÁN TRỌN GÓI, LÀM BCTC xem ngay

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

Công thức chung 
 
= IF ( kiểm tra hàng , INDEX ( dữ liệu , MATCH ( rownum , helper , 0 ), column ), "" )
 
Mời bạn xem thêm:
 
 
 
 
Giải trình 
 
trich-xuat-du-lieu-voi-cot-tro-giup-trong-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.
 
Trong ví dụ được hiển thị, công thức trong H6 là:
 
= IF ( $ G6 <= ct , INDEX ( dữ liệu , MATCH ( $ G6 , trợ giúp , 0 ), 1 ), "" )
 
Cách thức hoạt động của công thức này
 
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ư VLOOKUPINDEX + 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ông thức trong cột helper trông như sau:
 
= SUM ( E2 , VÀ ( C3 = $ I $ 3 , D3 = $ J $ 3 ))
 
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.
 
Quay trở lại khu vực khai thác, công thức tra cứu cho Tên trong cột H trông giống như sau:
 
= IF ( $ G6 <= ct , INDEX ( dữ liệu , MATCH ( $ G6 , trợ giúp , 0 ), 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 ( dữ liệu , MATCH ( $ G6 , trợ giúp , 0 ), 1 )
 
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 ("").
 
Công thức trong G3 (có tên là phạm vi "ct") rất đơn giản:
 
= MAX ( trợ giúp )
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 .
 

trích xuất dữ liệu từ sheet khác

trích xuất dữ liệu từ sheet này sang sheet khác

rút trích dữ liệu trong excel 2010

rút trích dữ liệu ngày tháng trong excel

hàm trích xuất dữ liệu trong excel

cách lọc dữ liệu trong excel 2003

lọc dữ liệu trong excel có điều kiện

trích xuất dữ liệu có điều kiện trong excel

 

 


Tags: Chua co du lieu
Bình luận

Bình luận

Các bài viết mới

Các tin cũ hơn





HÃY LIKE PAGE HỮU ÍCH

HÃY LIKE PAGE HỮU ÍCH