Khớp CHỈ SỐ GIÁN TIẾP trên nhiều trang tính

… và B3 có thể chứa các thành phần tham chiếu khác như tên trang tính/đường dẫn tệp, sử dụng ký hiệu ‘&’ để nối các thành phần chuỗi để tạo tham chiếu trang tính+ô đầy đủ

=INDIRECT(B3&”. $C$4″)

Xem chủ đề của chúng tôi, nơi chúng tôi trình bày cách sử dụng hàm INDIRECT của Excel để thiết lập tham chiếu ô tĩnh, để nhanh chóng tạo bảng tóm tắt dữ liệu từ nhiều trang tính

Trong bài viết trên trang này, chúng tôi trình bày cách kết hợp INDIRECT với INDEX MATCH để tạo tham chiếu ô động và giúp đối chiếu dữ liệu hiệu quả và mạnh mẽ hơn

công thức cơ bản INDEX MATCH

• Chúng tôi có hồ sơ bài kiểm tra của học sinh trong một bảng tính, mỗi học sinh một bảng tính
• Sáu mô-đun kiểm tra 1-6 có thể được thực hiện theo bất kỳ thứ tự nào
• Điểm số của học sinh nằm trong phạm vi $A$2. $D$9 trên mỗi trang tính, nhưng thứ tự mô-đun được liệt kê trong các hàng của bảng có thể khác nhau

• Chúng tôi muốn có một bảng tóm tắt như hình bên dưới đối chiếu điểm của từng học sinh
• Tra cứu sẽ đề cập đến hàng chính xác cho từng mô-đun trên bảng tính của sinh viên
• Tham chiếu ô động sẽ giúp tạo bảng nhanh chóng
• Lưu ý rằng tiêu đề bảng tóm tắt cho học sinh khớp với tên bảng tính

Trong ví dụ hoạt động này, chúng tôi sẽ sử dụng INDEX MATCH

Công thức cơ bản cho INDEX MATCH là

=INDEX(phạm vi, MATCH(lookupValue, columnRange, matchType), colNum)

Công thức INDEX MATCH tĩnh mà chúng ta cần tra cứu dấu của Bob cho Học phần 1 được hiển thị bên dưới

=INDEX(Bob. $A$2. $D$9,MATCH ($B4,Bob. $A$2. $A$9,0),4)

Các thành phần của công thức INDEX MATCH ở trên

• Bob. $A$2. $D$9 – Phạm vi tuyệt đối của INDEX A2. D9 trên trang tính có tên Bob
• MATCH giá trị trong ô $B4 trên bảng tóm tắt (chứa '1' cho mô-đun 1)
• Bob. $A$2. $A$9,0 – Tra cứu giá trị ‘1’ trong phạm vi cột tuyệt đối A2. A9 trên trang tính Bob;
• Tìm giá trị chúng tôi muốn trong cột 4 (D) của phạm vi chỉ mục (A2. D9), là 94, điểm của Bob cho Học phần 1

Nếu chúng tôi sử dụng các tham chiếu tĩnh, chúng tôi sẽ phải sao chép, dán và chỉnh sửa từng công thức

Thay vào đó, chúng tôi sử dụng hàm INDIRECT để tạo các tham chiếu ô + trang tính động. Chúng tôi sẽ tra cứu các hàng chính xác trên mỗi trang tính của học sinh và sẽ có thể kéo-sao chép các công thức trong bảng tóm tắt. Chúng ta cũng có thể sử dụng INDIRECT để đọc trong phạm vi INDEX và MATCH, vì mỗi trang tính của học sinh có cùng một bố cục

Ví dụ hoạt động của Excel INDIRECT INDEX MATCH

để biết mô tả về tác vụ ví dụ mà chúng tôi đang sử dụng để minh họa bằng hàm INDIRECT của Excel với INDEX MATCH

Chúng tôi đang sử dụng INDIRECT để đọc tên trang tính và phạm vi ô INDEX và MATCH. Như bạn có thể thấy bên dưới, đây là cách công thức sẽ kết hợp INDEX MATCH và INDIRECT

=INDEX(INDIRECT (tên bảng tính. + phạm vi chỉ số), MATCH (số mô-đun, INDIRECT (tên trang tính. + phạm vi đối sánh), loại đối sánh), số cột)

• Chúng tôi tham khảo tên trang tính. từ hàng tiêu đề của bảng tóm tắt (C3 đến G3 trong ảnh chụp màn hình bên dưới)
• Chúng tôi đã nhập phạm vi ô INDEX và MATCH vào các ô I1 và I2
• Lưu ý rằng chúng tôi đã bao gồm một dấu chấm than ở đầu '. ’ của các phạm vi ô để biểu thị văn bản trước đó dưới dạng tên trang tính

=INDEX(INDIRECT(D$3&$I$1),MATCH($B4,INDIRECT(D$3&$I$2),0),4)

Dưới đây là cách các công thức TRÒ CHƠI CHỈ SỐ GIÁN TIẾP trông như thế nào trong Cột G của bảng tóm tắt, để mang lại kết quả của Erica

Chúng tôi cũng đã xây dựng một số kiểm tra xác thực để xác nhận rằng tổng số và tỷ lệ phần trăm trong bảng tóm tắt khớp với các phép tính trong bảng tính

INDEX MATCH giúp đơn giản hóa việc tìm kiếm dữ liệu bạn cần, đặc biệt nếu bạn có nhiều trang tính cần lấy dữ liệu từ trong Excel hoặc Google Trang tính

Đây cũng là một cách tuyệt vời để làm cho việc đọc dữ liệu của bạn thú vị và tương tác hơn cho người dùng

Bước 1. Tổ chức dữ liệu

INDEX MATCH sẽ hoạt động cho dù dữ liệu của bạn được liệt kê trong một tab hay trên nhiều tab trong bảng tính của bạn. Đối với ví dụ này, chúng tôi sẽ chia tất cả dữ liệu của mình thành các tab khác nhau

Quyết định cách bạn muốn dữ liệu của mình xuất hiện. Nếu bạn có nhiều thông tin, việc giữ mọi thứ trên một tab có thể trở nên quá tải. Tuy nhiên, việc tách dữ liệu thành các tab riêng biệt có thể không phải là một tùy chọn nếu

Trong ví dụ này, chúng tôi sẽ làm việc với các tab này

Để bắt đầu xây dựng bảng nơi dữ liệu INDEX MATCH sẽ được hiển thị, hãy bắt đầu với 4 hàng mà chúng tôi muốn xem dữ liệu. Bảng này sẽ được xây dựng trên tab Dữ liệu chính

chủ tịch

phó chủ tịch

Số năm phục vụ

đại hội

Trong trường hợp này, chúng tôi muốn hiển thị dữ liệu dựa trên tên của tổng thống, do đó, chúng tôi cần tạo danh sách thả xuống liệt kê tất cả tên của tổng thống. Sau khi tên của tổng thống được chọn từ danh sách thả xuống, ba hàng dữ liệu sau đây sẽ được điền tương ứng bằng cách sử dụng công thức INDEX MATCH

Bước 1. Thiết lập trường thả xuống (Xác thực dữ liệu)

Thiết lập trường thả xuống trong Excel hoặc Google Trang tính rất dễ dàng và làm cho bảng tính của bạn tương tác hơn nhiều

Đối với mục đích của ví dụ này và để giữ mọi thứ sạch sẽ và có tổ chức nhất có thể, chúng tôi sẽ kéo tất cả tên chủ tịch từ tab Chủ tịch vào trường thả xuống bằng cách sử dụng Xác thực dữ liệu

Cách thiết lập trường thả xuống trong Excel

Chọn ô mà bạn muốn thêm vào trường thả xuống. Trong ví dụ này, bạn sẽ muốn đặt nó một ô ở bên phải của Chủ tịch trong tab Dữ liệu chính

Tiếp theo, nhấp vào tab Dữ liệu ở trên cùng. Trong Công cụ dữ liệu, nhấp vào biểu tượng Xác thực dữ liệu, trông như thế này

Sau khi nhấp vào biểu tượng, bạn sẽ thấy một phương thức bật lên nơi bạn có thể chọn nguồn dữ liệu. Trong Tiêu chí xác thực, trường Cho phép sẽ hiển thị Danh sách và sau khi điều này được chọn, phần Nguồn sẽ khả dụng. Trong phần chọn nguồn, bạn sẽ nhấp vào tab Chủ tịch và chọn tất cả các ô có tên Chủ tịch

Cách thiết lập trường thả xuống trong Google Trang tính

Quy trình thiết lập trường thả xuống trong Google Trang tính thậm chí còn dễ dàng hơn

Chọn ô mà bạn muốn danh sách thả xuống xuất hiện. Nhấp chuột phải vào ô, nhấp vào Xem thêm hành động ô > Xác thực dữ liệu. Từ đây, các tiêu chí sẽ là Danh sách từ một phạm vi. Tiếp theo, nhấp vào bốn ô vuông trong trường tiếp theo để bạn có thể chọn phạm vi từ tab Tổng thống

Bước 3. Thiết lập công thức INDEX MATCH

Công thức giống nhau cho dù bạn đang sử dụng Excel hay Google trang tính. Hãy phá vỡ nó

Trong phần đầu tiên của công thức, như trường xác thực dữ liệu, chúng ta cần lấy "chỉ mục" của dữ liệu. Có nghĩa là, chúng ta muốn lấy dữ liệu từ đâu

Hãy sử dụng phần tiếp theo của bảng của chúng tôi cho ví dụ đầu tiên, chúng tôi muốn lấy danh sách các Phó chủ tịch và sau đó ghép Phó chủ tịch với tổng thống

Tuy nhiên, vì chúng tôi có từng lựa chọn dữ liệu được tách thành các tab khác nhau trên bảng tính của mình, nên chúng tôi cần một cách để khớp từng phần dữ liệu với tổng thống. Vì điểm dữ liệu chính là Chủ tịch và chúng tôi đang khớp tất cả các dữ liệu khác cho phù hợp, nên chúng tôi cần thêm cột Chủ tịch vào mỗi tab của trang tính. Như thế này

Để viết công thức đầu tiên cho Phó chủ tịch, chúng ta sẽ bắt đầu với

=INDEX('Vice Presidents'!B:B)

Điều này có nghĩa là chúng ta có thể đưa INDEX của dữ liệu từ cột B trên tab Phó chủ tịch. Tiếp theo, chúng ta sẽ thêm phần MATCH vào bảng tính

=INDEX('Vice Presdients'!B:B, MATCH(C3,'Vice Presidents'!A:A,0))

Trong phần MATCH, đây là những gì mỗi phần đại diện

​C3

Đây là trường thả xuống mà chúng tôi đã tạo ở Bước 2 của hướng dẫn này

'Phó chủ tịch'. A. A

Điều này cho biết rằng bạn muốn khớp với món quà đã chọn trong danh sách thả xuống (C3) trên tab Vice Presidents

​0

Cái này dành cho loại đối sánh. Số 0 cho biết bạn muốn có kết quả đối sánh chính xác

Cuối cùng, bạn sẽ tạo công thức INDEX MATCH cho tất cả các trường bảng khác mà bạn muốn hiển thị dựa trên dữ liệu

Công thức gián tiếp có hoạt động giữa hai trang tính Excel khác nhau không?

Bạn có thể sử dụng đối sánh INDEX với gián tiếp không?

Làm cách nào để lấy dữ liệu từ nhiều trang tính trong Excel dựa trên tiêu chí?