Mặc dù chúng là hai chức năng riêng biệt nhưng khi kết hợp chúng sẽ tạo ra giá trị tìm kiếm hiệu quả nhất. Hàm Index Match được sử dụng thường xuyên trong Excel và mang lại nhiều ưu điểm hữu ích cho người dùng. Điều gì sau đó phục vụ mục đích này?
Chức năng Kết hợp chỉ mục. Nó là gì?
Để hiểu chức năng này là gì, trước tiên hãy kiểm tra từng loại riêng biệt. Chức năng Index Match là hai chức năng riêng biệt kết hợp với nhau để tạo ra hiệu quả trong khám phá dữ liệu. Đặc biệt
Cuộc thi đấu. hàm số
Hàm Match là một hàm tìm kiếm một dải ô cho một giá trị được xác định trước, sau đó trả về vị trí tương đối của giá trị trong dải ô đó. Công thức của hàm cú pháp là
=MATCH(Giá trị_tìm_kiếm,Mảng_tìm_kiếm,[Kiểu_so khớp])
Trong đó
- Phạm vi giá trị tìm kiếm được đại diện bởi lookup_value trong lookup_array. Giá trị tìm kiếm có thể là một chuỗi văn bản, giá trị logic, số hoặc tham chiếu ô đến một số trong một mảng. Ngoài ra, nó có thể là một giá trị dữ liệu hoặc một tham chiếu văn bản
- Lookup_array tìm kiếm một mảng hoặc một dải ô cho giá trị đã chỉ định
- Loại so khớp. Loại tìm kiếm có được tính đến không
chức năng chỉ số
Các giá trị trong một ô cụ thể giao nhau giữa cột và hàng có thể được lấy bằng cách sử dụng hàm Chỉ mục, một loại hàm trả về một mảng. Công thức cú pháp hàm
=INDEX(Mảng,Số_hàng,[Số_cột])
Trong đó
- Phạm vi ô hoặc một hàng số mảng cụ thể phải được tìm kiếm được gọi là một mảng
- Row_num được sử dụng để chọn hàng nào trong mảng tìm kiếm sẽ trả về giá trị từ đó
- cột_num. được sử dụng để chọn cột mảng tìm kiếm để trả về một giá trị từ
Phép tính cho cú pháp của hàm INDEX làm tài liệu tham khảo như sau
=INDEX(Tham chiếu,Số_hàng,[Số_cột],[Số_khu_vực])
Trong đó
- Khu vực tham chiếu cần thiết có được xem xét không?
- Chỉ mục hàng tìm kiếm để trả về một tham chiếu là row_num
- Chỉ mục cột tìm kiếm để trả về tham chiếu là cột_num
- Số của tập hợp các ô dữ liệu được gọi là Area_num sẽ trả về giá trị trong ReferenceArea 1 sẽ được sử dụng bởi hàm Index nếu Area_num bị bỏ qua
Cách kết hợp hàm Index và Match trong Excel
Bạn có thể dễ dàng hiểu cách sử dụng hàm Index Match trong Excel bằng cách sử dụng ví dụ được cung cấp bên dưới
Chẳng hạn, sử dụng các hàm Index và Match của Excel để tìm kiếm địa chỉ theo tên trong bảng dữ liệu. bước
Bước 1. Gõ công thức =INDEX(C3 trong bảng dữ liệu Excel. C8,TRẬN ĐẤU(C10,B3. B8,0))Bạn sẽ nhập công thức này vào ô tham chiếu mà bạn muốn xem kết quả
Bước 2. Sau khi nhập công thức, nhấn Enter để hiển thị kết quả tính toán. Do đó, bạn đã kết hợp hai chức năng đó để tìm kết quả dữ liệu
Cách kết hợp nhiều điều kiện và các hàm chỉ mục và khớp
Sau đây là một số ví dụ cụ thể về cách sử dụng chức năng Kết hợp chỉ mục để khớp với nhiều điều kiện khác nhau
Chẳng hạn, bạn có thể xác định đơn giá của hàng hóa và nhà sản xuất bằng chức năng Index và chức năng Match. Giả sử rằng theo đơn giá của sản phẩm và nhà sản xuất trong bảng 2 (B15) mà bạn sẽ cần sử dụng $ trong công thức để cố định giá trị của phạm vi. E18), sau đó bạn phải hoàn thành trường đơn giá trong bảng 1 (B3. D12)
Các hàm Index và Match có thể được kết hợp theo các cách sau để xác định kết quả giá trị cho ô D4
Bước 1. Để xác định vị trí của CDRom sản phẩm tương ứng, hãy sử dụng chức năng Match. Phạm vi giá trị của Bảng 2 từ B15 đến B18, công thức
=MATCH(B4,$B$15. $B$18. 0) Khi xem xét hàng 4 của bảng 2, kết quả "4" tương ứng. Kết quả “4” tương ứng với hàng số 4 trong bảng 2 khi xem xét.
Bước 2. Tiếp theo, bạn sử dụng chức năng Match để tìm cột nhà sản xuất Samsung phù hợp. Phạm vi dữ liệu đang được xem xét là B15 đến E15. công thức đầu vào
=MATCH(C4,$B$15. $E$15. 0)Kết quả hiển thị "2," tương ứng với cột số 2 trong bảng 2
Bước 3. Sau khi kết hợp hai hàm Match đã tính ở bước 1 và 2, bạn sử dụng hàm Index. Sau đó vào bảng 2 xác định giá trị theo hàng, cột
=INDEX($B$15. $E$18,MATCH(B4,$B$15. $B$18. 0),TRẬN ĐẤU(C4,$B$15. $E$15. 0))
Kết quả trả về 5, tương ứng với hàng 4 của bảng 2 (hàng chuột) và cột 2 (Samsung)
Bước 4. Để hoàn thành bảng số liệu cần xét, copy công thức vừa tính sang các ô khác
Cách kết hợp hàm Vlookup và Index Match
Sẽ có một công thức tính khác để kết hợp hàm Index Match Vlookup, cụ thể như sau
=VLOOKUP(INDEX(tham chiếu;[hàng];[cột]);table_array;col_index_num;[range_lookup])
Trong đó
- Giá trị của INDEX(tham chiếu; [hàng]; [cột]) được sử dụng để xác định điều kiện
- Bảng chứa giá trị mà bạn muốn tìm kiếm trong dữ liệu được gọi là Table_array
- Vị trí của cột chứa giá trị bạn muốn tìm kiếm được chỉ định bởi Col_index_num
- Có phải phạm vi mà dữ liệu đang tìm kiếm trong [range_lookup] không?Nếu có giá trị 0 hoặc 1, thì việc phát hiện tương ứng là tương đối
Hình minh họa
Điểm khu vực của từng học sinh được xác định theo khu vực bằng bảng dữ liệu
Công thức áp dụng
=VLOOKUP(INDEX(A2. ). F9;1;6);$I$2. $J$9;2;0)
Trong đó.
- CHỈ SỐ(A2. F9;1;6)F9 lấy giá trị ở hàng 1 cột 6 trong vùng phạm vi A2
- $I$2. $J$9Cell bắt đầu tại I2 và J9 có giá trị phát hiện
- 2. Vị trí của cột điểm khu vực có chính xác không?
- Nó sử dụng một phương pháp phát hiện tương đối
Hướng dẫn sử dụng hàm If và Index Match cùng nhau
Công thức sẽ được tính như sau để kết hợp hàm If và hàm Index Match
=IF(INDEX(dữ_liệu_mảng);MATCH(khóa_tìm_kiếm, phạm vi, loại_tìm_kiếm);số_cột;giá_trị nếu_; giá_trị_if_false)
Trong đó
- Array_data có phải là số mảng hay vùng ô bắt buộc không?
- Giá trị bạn muốn tìm kiếm trong dữ liệu là key_search
- Vùng dữ liệu mà bạn muốn tìm search_key có nằm trong dãy_ô không?Dải ô này chỉ có thể có 1 hàng hoặc 1 cột
- Phương pháp tìm kiếm type_search có ba loại chính. bao gồm 0 cho kết quả khớp hoàn hảo và 1 làm giá trị mặc định. Dạng thứ ba -1 là một phạm vi được sắp xếp theo thứ tự giảm dần trả về giá trị nhỏ nhất lớn hơn hoặc bằng từ khóa tìm kiếm
- Trả về giá trị cho số lượng cột trong dữ liệu đã được truy xuất
- Value_if_true. Hàm này trả về giá trị nếu điều kiện và hàm INDEX là đúng
- Giá trị sẽ được trả về trong trường hợp này nếu hàm INDEX và điều kiện đều sai
Hình minh họa.
Để có thể đạt chỉ tiêu KPI, số lượng bánh bán ra phải lớn hơn 10 sản phẩm;
Công thức áp dụng.
=IF(INDEX(A2. ). F5;MATCH(“Bánh quy”;A2. A5;0);3)>10;"Đạt";"Không đạt"
Hàm Match trả về địa chỉ hàng của ô chứa giá trị cookie trong đó. Kiểm tra xem có nhiều hơn 10 sản phẩm hay không bằng cách so sánh hàm Index trả về số lượng cookie đã bán. Nếu giá trị true lớn hơn giá trị đó, kết quả là "Đạt", ngược lại, kết quả là "Không đạt. "
Một số lỗi thường mắc phải khi sử dụng hàm Index Match của Excel
Sau đây là một số lỗi điển hình mà bạn có thể gặp phải khi kết hợp hàm Index và hàm Match của Excel
Lỗi #REF
Một trong những lỗi thường gặp nhất khi kết hợp hàm Index và Match là lỗi #REF, thường xảy ra khi phạm vi trả về của hàm Index khác với phạm vi tra cứu của hàm Match.
Cách tốt nhất để giải quyết lỗi #REF này là sửa đổi phạm vi của hàm Index. Nếu tiêu chí dữ liệu đã cho không được đáp ứng, sử dụng hàm Index và Match sẽ trả về #REF
Lỗi #N/A
Khi không thể xác định được giá trị cho bảng dữ liệu được đề cập, lỗi #N/A đã thấy trước đó xuất hiện. Lỗi này cũng xảy ra tương tự như hàm Vlookup điển hình
Nên dùng chức năng Index Match thay cho Vlookup và Hlookup vì lý do gì?
Hàm Hlookup sẽ hiệu quả hơn nhiều nếu kết hợp hàm Index và hàm Match, thay vì chỉ sử dụng riêng hàm Vlookup, vì lý do đơn giản là làm như vậy sẽ mang lại nhiều kết quả tích cực hơn
- Khó sử dụng hàm Vlookup và hàm Hlookup cho dữ liệu cần tra cứu 2 chiều vì chỉ truy xuất được dữ liệu theo một chiều
- Nếu bạn thêm bớt các cột trong bảng tính khi sử dụng hàm Hlookup sẽ xảy ra lỗi
phần kết
Chắc hẳn bạn đọc đã hiểu rõ hơn về Index Match là gì cũng như cách sử dụng hai chức năng này một cách đơn giản nhất nhờ những thông tin được cung cấp ở trên. Vui lòng truy cập Sforum ngay nếu bạn có bất kỳ thắc mắc nào về các chi tiết nói trên hoặc nhiều cập nhật khác. Kết hợp chỉ mục sau đó. Bên cạnh đó, bạn cũng biết cách sử dụng hai chức năng này một cách đơn giản nhất. Nếu còn thắc mắc về những thông tin trên hoặc cập nhật nhiều vấn đề khác, hãy đến với Sforum ngay.
Thông thường, một công thức INDEX MATCH được định cấu hình với MATCH được đặt để xem qua phạm vi một cột và cung cấp kết quả khớp dựa trên tiêu chí đã cho. Nếu không ghép nối các giá trị trong cột trợ giúp hoặc trong chính công thức, thì không có cách nào để cung cấp nhiều hơn một tiêu chí
Công thức này khắc phục hạn chế này bằng cách sử dụng logic boolean để tạo một mảng gồm các số 1 và 0 để biểu thị các hàng khớp với cả 3 tiêu chí, sau đó sử dụng MATCH để khớp với 1 tiêu chí đầu tiên được tìm thấy. Mảng tạm thời của những cái và số không được tạo bằng đoạn mã này
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)Ở đây chúng tôi so sánh mục trong H5 với tất cả các mục, kích thước trong H6 với tất cả các kích thước và màu trong H7 với tất cả các màu. Kết quả ban đầu là ba mảng kết quả TRUE/FALSE như thế này
{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}*{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}Mẹo. sử dụng F9 để xem những kết quả này. Chỉ cần chọn một biểu thức trong thanh công thức và nhấn F9
Phép toán (phép nhân) biến đổi các giá trị TRUE FALSE thành 1 và 0
{1;1;1;0;0;0;1}*{0;0;1;0;0;1;0}*{1;0;1;0;0;0;1}Sau khi nhân, chúng ta có một mảng như thế này
{0;0;1;0;0;0;0}được đưa vào hàm MATCH dưới dạng mảng tra cứu, với giá trị tra cứu là 1
MATCH(1,{0;0;1;0;0;0;0})Tại thời điểm này, công thức là một công thức INDEX MATCH tiêu chuẩn. Hàm MATCH trả về 3 cho INDEX
=INDEX(E5:E11,3)và INDEX trả về kết quả cuối cùng là $17. 00
Trực quan hóa mảng
Các mảng được giải thích ở trên có thể khó hình dung. Hình ảnh dưới đây cho thấy ý tưởng cơ bản. Các cột B, C, D tương ứng với dữ liệu trong ví dụ. Cột F được tạo bằng cách nhân ba cột với nhau. Đây là mảng được chuyển giao cho MATCH
Phiên bản không mảng
Có thể thêm một INDEX khác vào công thức này, tránh phải nhập dưới dạng công thức mảng với control + shift + enter
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))Hàm INDEX có thể xử lý các mảng một cách nguyên bản, do đó, hàm INDEX thứ hai chỉ được thêm vào để "bắt" mảng được tạo bằng phép toán logic boolean và trả lại cùng một mảng về MATCH. Để thực hiện việc này, INDEX được định cấu hình không có hàng và một cột. Thủ thuật hàng không khiến INDEX trả về cột 1 từ mảng (dù sao cũng đã là một cột)
Tại sao bạn muốn phiên bản không có mảng? . Vì vậy, một công thức không có mảng sẽ "chống đạn" hơn. Tuy nhiên, sự đánh đổi là một công thức phức tạp hơn
Tôi làm cách nào để viết một công thức có khả năng kiểm tra trên nhiều trang tính để tìm một ngày cụ thể và sau đó lấy dữ liệu từ trang tính cụ thể có ngày đó?
Tôi đã tìm kiếm khắp nơi trên nhiều trang web và diễn đàn và không thể tìm thấy bất kỳ thứ gì phù hợp với hóa đơn
Tôi đang sử dụng excel 2010. Dữ liệu được kéo chỉ là số đến một điểm thập phân
Tôi có gì
Trang tính đầu tiên (31 ngày qua) là nơi tôi lấy dữ liệu để. Tôi có một công thức cập nhật ngày hiển thị mỗi ngày để các ô riêng lẻ trong cột B hiển thị 31 ngày qua
Tờ thứ hai (2022) chứa dữ liệu cho năm 2022 được phân tách thành từng tháng (dữ liệu thực và giả)
Tờ thứ ba (2023) chứa dữ liệu cho năm 2023 được phân tách thành từng tháng (dữ liệu giả)
Cả 3 sheet đều có định dạng và bố cục giống hệt nhau
Điều tôi muốn công thức thực hiện là kiểm tra trang tính "2022" để biết ngày cần thiết và lấy dữ liệu từ trang tính đó nếu tìm thấy, nhưng nếu nó không tìm thấy ngày cần thiết, thì hãy chuyển sang trang tính "2023" để tìm
Các công thức tôi có để hoạt động riêng lẻ
=INDEX('2022'. $C$3. $C$389, TRẬN ĐẤU(B3, '2022'. $B$3. $B$389, 0))
=INDEX('2023'. $C$3. $C$389, TRẬN ĐẤU(B3, '2023'. $B$3. $B$389, 0))
B3 - ô chứa ngày trên trang tính "31 ngày qua"
'2022'. $B$3. $B$389 - phạm vi ô mà ngày đang được tìm kiếm trên trang tính "2022"
'2022'. $C$3. $C$389 - phạm vi ô mà dữ liệu đang được lấy từ trang tính "2022"
'2023'. $B$3. $B$389 - phạm vi ô mà ngày đang được tìm kiếm trên trang tính "2023"
'2023'. $C$3. $C$389 - phạm vi ô mà dữ liệu đang được lấy từ trang tính "2023"
Cảm ơn bạn trước
Trả lờiNicholas Moss nói.
9 Tháng Mười, 2021 lúc 10. 33 giờ chiều
Xin chào
Tôi đang gặp khó khăn khi áp dụng điều này. Tôi đang cố khớp một hàng dữ liệu từ một trang tính để khớp với tập hợp chính xác các biến từ một trang tính khác và chọn mục nhập thứ 8 được liên kết với nó, số tiền đặt hàng
Ví dụ: tôi muốn tìm mục khớp chính xác với các chi tiết này từ trang 1 trong trang 2 và nơi chúng khớp chính xác, lấy số lượng đặt hàng được chỉ định trong trang 2. Đây là các cột và hàng. Vì vậy, nếu mục tôi đang xem là "Sheet1. A3. G3" và tìm mục nhập chính xác này trong "Sheet2. "A1. G1000". Giả sử chúng khớp với hàng 15 của trang 2, sau đó tôi muốn chọn mục nhập trong "Trang 2. H15
Trang tính 1 1 mục nhập
sản phẩm, cân nặng, kiểu, người trồng trọt, nhà cung cấp, có sẵn, giá cả, gọi món
bí xanh, 8kg, hộp, hữu cơ, vikram, vâng, 30, (cần thông tin)
tờ 2
sản phẩm, cân nặng, kiểu, người trồng trọt, nhà cung cấp, có sẵn, giá cả, gọi món
bí xanh, 8kg, hộp, hữu cơ, vikram, vâng, 30, 3
Vì vậy, tôi muốn lấy số lượng đặt hàng là 3 từ Sheet2 bằng cách sử dụng phương pháp này
Tôi đã thử nhưng tôi không hiểu đúng. Liệu bạn có thể giúp mình không?
Cảm ơn rất nhiều,
Trân trọng
Nicholas
Trả lờiVanessa nói.
Tháng Chín 16, 2021 tại 8. 56 giờ sáng
Đây là tiêu chí của tôi
1. khoảng thời gian. từ Khoảng 1 đến 288
2. Thực vật. Cây 1, 2, 3 (đây là tiêu chí tôi muốn tính tổng các giá trị, có những cây trùng lặp trong một ngày)
Ngày
Dưới đây là dữ liệu tôi nhận được các giá trị áp dụng tra cứu Ma trận với nhiều tiêu chí
Khoảng thời gian 26/7/2020 26/7/2020 27/7/2020 27/7/2020 28/7/2020 28/7/2020 28/7/2020
(cứ sau 5 phút. ) Nhà máy 2 Nhà máy 1 Nhà máy 1 Nhà máy 1 Nhà máy 2 Nhà máy 3 Nhà máy 1
1 0 0. 0517 0 0. 0532 0 0 0. 0530
2 0 0. 0522 0 0. 0521 0 0 0. 0532
3 0 0. 0514 0 0. 0523 0 0 0. 0529
4 0 0. 0519 0 0. 0522 0 0 0. 0531
5 0 0. 0515 0 0. 0518 0 0 0. 0532
.
.
288
Tôi đã sử dụng tra cứu ma trận với nhiều tiêu chí. Tuy nhiên, INDEX là một hàm tra cứu và sẽ chỉ trả về một giá trị từ một ô duy nhất. Mặc dù các giá trị là 0, nhưng tôi muốn lấy tổng số cây cho mỗi ngày và khoảng thời gian
Đây là công thức của tôi
=IFERROR(INDEX('Nhà máy A'. $C$6. $BV$293,MATCH('Summary_Nhà máy A'. C6,'Nhà máy A'. $B$6. $B$293,0),MATCH('Summary_Plant A'. B6&'Summary_Nhà máy A'. $D$5,'Nhà máy A'. $C$4. $BV$4&'Nhà máy A'. $C$5. $BV$5,0)),0)
Tôi đã thử thêm một hàm tính tổng nhưng kết quả nếu không bằng 0 (không có giá trị), kết quả sai, #value hoặc kết quả không thay đổi. Làm cách nào để kết hợp hàm sum/sumif/sumifs trong công thức? . Hy vọng mẫu trên giúp hình dung nhiệm vụ. Cảm ơn bạn
Trả lờiAlan nói.
Tháng Tám 27, 2021 tại 11. 53 giờ chiều
Tôi đang cố gắng tìm ra cách sử dụng điều này để khớp với 2 tiêu chí, với chỉ mục trả về giá trị tối đa của tiêu chí thứ hai. Dữ liệu được định dạng trong 3 cột
Cột A - Tên nhân viên
Cột B - Tên người quản lý
Cột C - Ngày được giao cho người quản lý đó
Một nhân viên có thể có nhiều bản ghi trong bảng nếu họ ở dưới quyền của những người quản lý khác nhau vào những thời điểm khác nhau
Tôi muốn xác định tên của người quản lý mà nhân viên đã được chỉ định vào một ngày cụ thể. Vì vậy, tôi muốn khớp tên của nhân viên với Cột A, sau đó tìm ngày tối đa sau ngày tìm kiếm của tôi - và trả về tên của người quản lý
Ví dụ: nếu dữ liệu của tôi trông như thế này
Nhân viên A -- Quản lý Z -- 1/1/2008
Nhân viên A -- Quản lý Y -- 1/8/2015
Nhân viên A -- Quản lý X -- 28/11/2018
Tôi muốn có thể tìm kiếm Nhân viên A vào một ngày tùy ý, giả sử là 25/12/2017 - và nhận được câu trả lời từ Người quản lý Y
Trả lờiMichael nói.
8 Tháng Tư, 2021 tại 4. 33 giờ chiều
Tôi đang cố lấy giá trị được trả về dựa trên bảng thông tin có phạm vi. Dưới đây là bảng tôi đang làm việc với. Đó là các yêu cầu về túi đối với móng nhà dựa trên diện tích vuông của tấm nhà và chiều cao trung bình của tấm. Tôi muốn có thể trả về một giá trị dựa trên hai đầu vào riêng biệt và trả về đúng hàng và cột
Thí dụ. sàn nhà là 3073 sf (đây là B7 trong bảng tính) và chiều cao trung bình là 4. 08 ft (đây là B12 trong bảng tính). Đó sẽ là 15 (cột thứ ba, hàng thứ tư)
Chiều cao 2000-2500 2500-3000 3000-3500 3500-4000 4000-4500 4500-5000 5000-5500
1. 0-2. 0 8 9 12 14 15 16 17
2. 0-3. 0 9 11 13 15 16 17 19
3. 0-4. 0 10 13 14 15 17 19 20
4. 0-5. 0 11 14 15 16 19 20 22
5. 0-6. 0 12 15 16 17 20 22 22
6. 0-7. 0 13 16 17 18 22 22 23
7. 0-8. 0 15 17 18 19 22 23 24
8. 0-9. 0 16 18 19 19 23 24 26
9. 0-10. 0 17 19 20 20 24 26 26
10. 0-11. 0 18 20 21 22 26 26 27
11. 0-12. 0 19 21 22 22 26 27 28
Ghi chú. Bảng trong trang tính của tôi kéo dài G24. N35
Vấn đề tôi gặp phải là tôi không có các giá trị cụ thể mà tôi đang kiểm tra dọc theo các hàng và cột, nhưng có phạm vi ở cả hai. Công thức hiện tại tôi có ở bên dưới, nhưng tôi nhận được #VALUE. lỗi do 4. 08 chiều cao trung bình bị bỏ sót trong công thức của tôi. Có cách nào tốt hơn để định dạng công thức này nhằm quản lý tất cả các phạm vi của hai giá trị mà tôi cần kiểm tra trong bảng hay tôi thực sự bị mắc kẹt với tất cả các IF lồng nhau?
=@INDEX(H25. N35,NẾU(B12=2. 1,B12=3. 1,B12=4. 1,B12=5. 1,B12=6. 1,B12=7. 1,B12=8. 1,B12=9. 1,B12=10. 1,B12=11. 1,B12