Đề Xuất 2/2023 # Cách Sử Dụng Hàm Index Và Hàm Match Trong Excel # Top 7 Like | Thaiphuminh.com

Đề Xuất 2/2023 # Cách Sử Dụng Hàm Index Và Hàm Match Trong Excel # Top 7 Like

Cập nhật nội dung chi tiết về Cách Sử Dụng Hàm Index Và Hàm Match Trong Excel mới nhất trên website Thaiphuminh.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

Hướng dẫn kêt hợp 2 hàm Index và hàm match, hàm trả về giá trị dòng và cột, Là hàm dò tìm có cú pháp sử dụng đơn giản hơn hàm vlookup

Hôm nay mình xin giới với các bạn 2 cách sử dụng của hàm index và hàm Match trong excel.

Hàm Index và hàm match thường được kết hợp với nhau để lấy giá trị của dòng và cột thuộc những bảng khác nhau. Hàm Index sẽ được coi là hàm nâng cao của hàm vlookup, tùy cùng là hàm dò tìm nhưng hàm index sẽ sử dụng tốt hơn trong nhiều trường hợp phức tạp.

Tại sao vậy? Bởi hàm Vlooup trong nhiều trường hợp viết công thứ sẽ rất rắc rối, nhưng chuyển sang hàm Index và hàm match thì lại hoàn toàn đơn giản.

Hướng dẫn sử dụng hàm Index và hàm match trong excel

Hàm Index

Là hàm do tìm giá trị và sẽ trả về giá trị giao nhau của dòng và cột

Cấu trúc hàm Index

INDEX(Bảng;vị trí dòng, vị trí cột)

Hàm Match

Là hàm có chức năng cho ra vị trí đứng của giá trị cần dò

Cấu trúc hàm Match

MATCH(Giá trị dò;Danh sách Dò;Cách Dò)

Note: Giá trị dò là giá trị giống nhau giữa 2 bảng

Vì dụ cho 2 hàm Index và hàm match

Ta có bảng dữ liệu sau:

Ta sẽ áp dụng hàm index để tìm và ta sẽ có công thức như sau:

=INDEX(C14:E16;1;1)

Giải thích:

C14:E16: là bảng dữ liệu

1: vì máy tính nằm ở dòng 1 (theo mắt quan sát)

1: Cột thứ 1 (theo mắt quan sát)

Các bạn Enter để có được kết quả là: 3000000

Tương tự ta cũng có công thức như sau:

=INDEX(C14:E16;3;2)

Giải thích:

C14:E16: là bảng dữ liệu

3: vì tủ lạnh nằm ở dòng 3 (theo mắt quan sát)

2: Cột thứ 2 (theo mắt quan sát)

Các bạn Enter để có được kết quả là: 6000000

NOTE: Có thể các bạn thấy khi chúng ta sử dụng hàm Index thì các ví dụ trên vị trí dòng và cột chúng ta toàn đánh vị trí theo quan sát trên bảng, nhưng nếu bảng dữ liệu nhiều, mà tìm vị trí cột dòng như thế này là điều không khả thi, do đó khi sử dụng hàm Index chúng ta luôn luôn kết hợp với Hàm Match để xác đị vị trí dòng và cột.

Ví dụ Hàm Match sẽ được giới thiệu ngày sau đây cho các bạn hiểu.

Để tìm vị trí của Máy tính ta sẽ sử dụng hạm Match như sau:

=MATCH(LEFT(B6;2);A14:A16;0)

Giải thích:

LEFT(B6;2): là giá trị dò và mình lấy cột Mã SP làm giá trị dò giống nhau với bảng Mã SP ở bên dưới, nhưng Mã SP ở bảng trên có Mã dài hơn và không giống với Mã Sp ở bảng dưới, Do đó ta sử dụng hàm Left để lấy ra 2 ký tự giống nhau.

A14:A16: Là danh sách dò

0: là cách dò trính xác

Enter và ta sẽ có kết quả là vị trí của máy tính là 1

Ví dụ cuối cũng sẽ là sự kết hợp của 2 hàm index và hàm match để cho các bạn hiểu rõ hơn về 2 hàm này.

Dựa vào 2 bảng dữ liệu, ta điền giá trị vào ô đơn giá theo tháng

Nhìn vào bảng tính này các bạn cũng sẽ hiểu đơn giá của máy tính tại thời điểm tháng 3 sẽ là 6000000, mặt giặt tháng 2 là 8900000…

Đó là theo chúng ta nhìn còn trong công thức thì áp dụng như thế nào:

Ta sẽ có công thức như sau:

=INDEX($C$14:$E$16;MATCH(LEFT(B6;2);$A$14:$A$16;0);MATCH(D6;$C$13:$E$13;0))

Đầu tiên ta sử dụng hàm INDEX để dò tìm giá dự trên 2 bảng

$C$14:$E$16: Là Bảng giá trị cần dò (mấy ký tự $ chính là cố định bảng bằng phím F bằng phím 4 để khi copy công thức không bị nhảy bảng tạo ra giá trị lỗi)

Tiếp đến ta xác định vị trí dòng và cột với hàm Match

MATCH(LEFT(B6;2);$A$14:$A$16;0) : Xác định vị trí dòng của các mặt hành Máy tính, Máy giặt, Tủ Lạnh.

LEFT(B6;2) là giá trị dò, );$A$14:$A$16 là vùng dò các bạn cũng phải cố định lại, 0 là Kiều dò chính xác

MATCH(D6;$C$13:$E$13;0): Xác định vị trí cột theo từng tháng

D6: là giá trị dò

$C$13:$E$13: là vùng dò và cũng phải cố định để không bị nhảy cột

0: Kiều dò chính xác

Video cách sử dụng hàm Index và hàm match trong excel

Cách Sử Dụng Hàm Match Trong Excel (Match + Index / Vlookup)

Hướng dẫn chi tiết cách dùng Hàm match từ đơn giản tới nâng cao như: Hàm match kết hợp hàm Index, hàm vlookup. Khi biết và hiểu rõ cách dùng, bạn sẽ thấy các hàm kết hợp này mạnh mẽ hơn các hàm tìm kiếm thông thường như thế nào.

1. Giới thiệu về hàm tìm kiếm số thứ tự trong excel

Hàm match dùng để tìm vị trí của một tham số trong một vùng dữ liệu. Vùng dữ liệu này thường là một hàm hay một cột. Hàm match thường ít khi ứng dụng độc lập. Match thường được sử dụng kết hợp với các hàm vlookup, if, index để dò tìm giá trị.

Hàm Match kết hợp với các hàm khác được ứng dụng nhiều trong kế toán excel, báo cáo kinh doanh, báo cáo kho, thẻ kho …

2. Công thức của hàm match (cú pháp):

2.1. Cú pháp hàm

=Match(lookup value,lookup array,match type)

Diễn giải

= Tìm vị trí (điều kiện tìm kiếm x, vùng tìm kiếm alpha, kiểu tìm kiếm abc)

Công thức của hàm match được hiểu đơn giản như sau:

Tìm vị trí của x trong vùng alpha theo kiểu tìm abc

Trong đó:

X là điều kiện hay tham số mà ta cần tìm vị trí.

Alpha là vùng chứa tham số cần tìm (chứa x)

Abc là kiểu tìm kiếm. Có 3 kiểu tìm kiếm, Gồm:

-1: less than (nhỏ hơn giá trị tìm kiếm)

0: exact match (tìm chính xác giá trị tìm kiếm) kiểu này được dùng nhiều hơn cả

1: greater than (tìm các giá trị lớn hơn giá trị tìm kiếm)

2.2. Ứng dụng của hàm match nâng cao trong thực tế công việc

Người ta thường dùng kết hợp hàm match với hàm index để tìm một giá trị nào đó. Vì index chỉ tìm khi biết địa chỉ hàng và cột trong vùng bảng tính.

Lập phiếu nhập/ Phiếu xuất: Người ta thường kết hợp match với offset để làm các phiếu nhập xuất kho, phiếu thu chi, thẻ kho ….

Kiểm tra giá bán: Người ta cũng kết hợp match với vlookup/ hlookup để tìm kiếm trong việc kiểm tra giá bán …

Lập Thẻ kho: Hàm match kết hợp với index thường được dùng trong kế toán để làm thẻ kho

Tạo phiếu lương: Trong lĩnh vực nhân sự để làm phiếu lương, trong kinh doanh để lập báo cáo bán hàng …

3. Ví dụ minh họa cách dùng hàm Match Đơn giản

(1) Tìm vị trí của cột giá bán, thuế suất, tỷ lệ chiết khấu trong hàng tiêu đề.

(2) Tìm vị trí của hàng hóa có mã BBTL027001 trong cột mã hàng.

Ta cùng đi phân tích bài toán và các thành phần của hàm match.

Như vậy, đi phân tích bài toán số 1 ta có:

Điều kiện dò tìm hay tham số cần tìm: “Giá bán”, “Thuế suất”, “Tỷ lệ chiết khấu”.

Vùng tìm kiếm chính là từ A6:F6

Dạng tìm kiếm: tìm chính xác (0)

=MATCH(I2,$A$6:$F$6,0)

Tham số hay điều kiện tìm kiếm chính là ô I2 (viền màu xanh trên hình) hay “Giá bán”.

Vùng tìm kiếm $A$6:$F$6 (vùng có viền màu đỏ trên hình). Ký tự $ là để cố định địa chỉ dòng, cột. Mục đích là khi copy công thức xuống dưới hoặc sang bên, vùng tìm kiếm không bị thay đổi. (Nếu vùng tìm kiếm thay đổi, kết quả có thể không chính xác nữa).

Dạng tìm: chính xác tuyệt đối.

Và đây là kết quả:

Điều kiện dò tìm hay tham số cần tìm: hàng hóa có mã.

Vùng tìm kiếm chính là từ A6:F6

Dạng tìm kiếm: tìm chính xác (0)

Ngoài ra, Hàm Match nhiều điều kiện còn được ứng dụng trong việc lên các báo cáo của hành chính nhân sự, sale …

Đương nhiên khi dùng nhiều điều kiện thì phải kết hợp với công thức mảng.

4. Cách dùng Hàm match kết hợp vlookup trong excel

Trước tiên ta cần đi tìm hiểu tại sao lại có sự kết hợp này.

Như vậy, trong nhiều trường hợp ta sẽ dùng match để tìm số thứ tự cho hàm vlookup.

Đây là một cách kết hợp rất hữu ích trong công việc hàng ngày.

Ví dụ như sau:

Trên thực tế không nhiều người dùng excel biết tới hàm index.

Thông thường để tìm kiếm thông tin, người dùng sẽ ưu tiên dùng vlookup. Tuy nhiên hàm vlookup chỉ tìm được 1 chiều từ trái qua phải.

Khi biết sử dụng hàm match kết hợp index bạn sẽ tìm được cả 2 chiều bạn ạ.

Hàm vlookup: Tìm tên hàng dựa trên Mã hàng thì OK. Ngược lại, Tìm mã hàng của mặt hàng Xi măng thì hàm vlookup chịu luôn.

Hàm Index kết hợp match có thể tìm được Mã hàng dựa trên Tên hàng một cách dễ dàng.

Ý nghĩa của hàm:

Hàm match sẽ giúp tìm kiếm số thứ tự của mặt hàng Xi măng trong list Tên hàng

Hàm index giúp tham chiếu tới Mã hàng tương ứng với số thứ tự của mặt hàng đó

Khi các bạn thực hành nhiều về cách kết hợp 2 hàm này với nhau sẽ thấy nó Vi diệu thế nào.

Hi vọng, sau khi đọc xong bài viết ngắn này, các bạn có thể áp dụng hàm MATCH phục vụ công việc của mình một cách hiệu quả và nhanh chóng.

Cảm ơn các bạn

Cách Dùng Hàm Index Và Match Trong Excel

Hàm Vlookup không phải là hàm tra cứu duy nhất trong Excel. Hàm Vlookup cũng có nhiều hạn chế có thể khiến bạn không nhận được kết quả mong muốn trong nhiều trường hợp. Thay vào đó, Index Match trong Excel linh hoạt hơn và có một số tính năng vượt trội hơn so với hàm Vlookup ở nhiều khía cạnh.

HÀM INDEX VÀ MATCH TRONG EXCEL – KIẾN THỨC CƠ BẢN

HÀM INDEX – CÚ PHÁP VÀ CÁCH SỬ DỤNG

Hàm INDEX trong Excel trả về một giá trị trong một mảng dựa trên số hàng và cột mà bạn chỉ định. Cú pháp của hàm INDEX rất đơn giản:

INDEX (array, row_num, [column_num])

Giải thích về từng tham số:

Array (mảng) – một dải ô mà bạn muốn trả về một giá trị.

row_num – số hàng trong mảng mà bạn muốn trả về một giá trị. Nếu bỏ qua, thì column_num là bắt buộc.

column_num – số cột trong mảng mà bạn muốn trả về một giá trị. Nếu bỏ qua, row_num là bắt buộc.

Đây là một ví dụ về công thức INDEX ở dạng đơn giản nhất:

=INDEX(A1:C10,2,3)

Công thức tìm kiếm trong các ô từ A1 đến C10 và trả về một giá trị của ô ở hàng thứ 2 và cột thứ 3, tức là ô C2.

Không có gì quá phức tạp. Tuy nhiên, khi làm việc với dữ liệu thực, bạn sẽ khó biết hàng và cột nào bạn muốn, đó là bạn sẽ cần phải sử dụng đến hàm Match.

HÀM MATCH – CÚ PHÁP VÀ CÁCH SỬ DỤNG

Hàm MATCH trong Excel tìm kiếm giá trị tra cứu trong một dải ô và trả về vị trí tương đối của giá trị đó trong dải ô.

Cú pháp của hàm MATCH như sau:

MATCH (lookup_value, lookup_array, [match_type])

Lookup_value – số hoặc giá trị văn bản bạn đang tìm kiếm.

lookup_array – một dải ô đang được tìm kiếm

Match_type – chỉ định trả về kết quả khớp chính xác hay kết quả khớp gần nhất:

1 hoặc bị bỏ qua – tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu. Yêu cầu sắp xếp mảng tra cứu theo thứ tự tăng dần.

0 – tìm giá trị đầu tiên chính xác bằng giá trị tra cứu. Trong kết hợp INDEX / MATCH, bạn hầu như luôn cần một kết hợp chính xác, vì vậy bạn đặt đối số thứ ba của hàm MATCH thành 0.

-1 – tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị lookup_value. Yêu cầu sắp xếp mảng tra cứu theo thứ tự giảm dần.

=MATCH(“London”,B1:B3,0)

Để biết thêm thông tin, vui lòng xem hàm MATCH trong Excel.

Bạn nên nhớ rằng vị trí tương đối của giá trị tra cứu (tức là số hàng và cột) là những gì bạn cần cung cấp cho các đối số row_num và column_num của hàm INDEX. Excel INDEX có thể tìm giá trị tại điểm nối của một hàng và cột nhất định, nhưng nó không thể xác định chính xác hàng và cột nào bạn muốn.

CÁCH SỬ DỤNG HÀM INDEX MATCH TRONG EXCEL

Nói một cách đơn giản INDEX tìm giá trị tra cứu theo số cột và hàng, và MATCH cung cấp các số đó.

Đối với tra cứu theo chiều dọc, bạn chỉ sử dụng hàm MATCH để xác định số hàng và cung cấp phạm vi cột trực tiếp cho INDEX:

INDEX ( cột để trả về giá trị từ , MATCH ( giá trị tra cứu , cột để tra cứu , 0)

Để hình dung rõ hơn chúng ta sẽ thử nghiên cứu qua một ví dụ. giả sử bạn có một danh sách các thủ đô quốc gia và dân số của họ:

Để tìm dân số của một thủ đô nhất định, chẳng hạn như thủ đô của Nhật Bản, hãy sử dụng công thức INDEX MATCH sau:

=INDEX(C2:C10, MATCH(“Japan”, A2:A10, 0))

Bây giờ sẽ phân tích công thức:

Hàm MATCH tìm kiếm giá trị tra cứu “Nhật Bản” trong phạm vi A2: A10 và trả về số 3, vì “Nhật Bản” đứng thứ ba trong mảng tra cứu.

Số hàng chuyển trực tiếp đến đối số row_num của INDEX hướng dẫn nó trả về giá trị từ hàng đó.

Vì vậy, công thức trên biến thành một INDEX đơn giản (C2: C3) tìm kiếm trong các ô từ C2 đến C10 và kéo đến giá trị từ ô thứ 3 trong phạm vi đó, tức là C4 vì chúng ta bắt đầu đếm từ hàng thứ hai.

Nếu bạn không muốn mã hóa thành phố trong công thức thì có thể nhập nó chẳng hạn F1, cung cấp tham chiếu ô cho MATCH và bạn sẽ nhận được công thức tra cứu động:

=INDEX(C2:C10, MATCH(F1,A2:A10,0))

Lưu ý quan trọng: Số hàng trong đối số mảng của INDEX phải khớp với số hàng trong đối số lookup_array của MATCH, nếu không công thức sẽ tạo ra kết quả không chính xác.

Tuy nhiên tại sao không sử dụng công thức Vlookup và phải cố gắng tìm ra những điểm phức tạp của Excel Match Index?Ví dụ này chỉ dành cho mục đích giải thích để bạn hiểu cách hàm INDEX và MATCH hoạt động cùng nhau. Các ví dụ khác sau đây sẽ cho bạn thấy sức mạnh thực sự của sự kết hợp này, dễ dàng xử lý với nhiều tình huống phức tạp khi VLOOKUP gặp sự cố.

=VLOOKUP(F1, A2:C10, 3, FALSE)

SO SÁNH INDEX & MATCH VỚI VLOOKUP

Khi quyết định sử dụng hàm nào để tra cứu theo chiều dọc, hầu hết các chuyên gia Excel đều cho rằng hàm INDEX & MATCH tốt hơn nhiều so với hàm VLOOKUP. Tuy nhiên, nhiều người vẫn chọn hàm VLOOKUP, thứ nhất là vì nó đơn giản hơn và thứ hai vì họ không hiểu đầy đủ tất cả các lợi ích của việc sử dụng công thức INDEX MATCH trong Excel. Nếu không có sự hiểu biết như vậy, không ai sẵn sàng đầu tư thời gian của họ để học một cú pháp phức tạp hơn.

Nhưng nếu tìm hiểu bạn sẽ thấy MATCH INDEX có những ưu điểm vượt trội so với VLOOKUP .

Tra cứu từ phải sang trái. Như bất kỳ người dùng thông thạo nào cũng biết, hàm VLOOKUP không tìm kiếm dữ liệu từ bên trái, có nghĩa là giá trị tra cứu của bạn phải luôn nằm ở cột ngoài cùng bên trái của bảng. INDEX MATCH có thể thực hiện tra cứu bên trái một cách dễ dàng.

Chèn hoặc xóa cột một cách an toàn. Công thức VLOOKUP sẽ bị phá vỡ hoặc đưa ra kết quả không chính xác khi một cột mới bị xóa hoặc được thêm vào bảng tra cứu vì cú pháp của hàm VLOOKUP yêu cầu chỉ định số index của cột bạn muốn lấy dữ liệu. Đương nhiên, khi bạn thêm hoặc xóa cột, số chỉ mục (index) sẽ thay đổi.

Không có giới hạn cho kích thước của giá trị tra cứu. Khi sử dụng hàm VLOOKUP, các tiêu chí tra cứu của bạn không được vượt quá 255 ký tự, nếu không sẽ xuất hiện #VALUE! lỗi. Vì vậy, nếu tập dữ liệu của bạn chứa các chuỗi dài, INDEX MATCH là giải pháp hiệu quả duy nhất.

Tốc độ xử lý cao hơn. Nếu các bảng của bạn nhỏ thì sẽ không có bất kỳ sự khác biệt đáng kể nào về hiệu suất Excel. Nhưng nếu trang tính của bạn chứa hàng trăm hoặc hàng nghìn hàng và sẽ phát sinh hàng trăm hoặc hàng nghìn công thức, MATCH INDEX sẽ hoạt động nhanh hơn nhiều so với hàm VLOOKUP vì Excel sẽ chỉ phải xử lý các cột tra cứu và trả về thay vì toàn bộ mảng bảng.

Tác động của hàm VLOOKUP đối với hiệu suất của Excel có thể đặc biệt đáng chú ý nếu các file làm việc của bạn chứa các công thức mảng phức tạp như VLOOKUP và SUM . Vấn đề là việc kiểm tra từng giá trị trong mảng yêu cầu một lệnh gọi riêng của hàm VLOOKUP. Vì vậy, mảng của bạn càng chứa nhiều giá trị và càng có nhiều công thức mảng trong sổ làm việc, thì Excel càng hoạt động chậm hơn.

EXCEL INDEX MATCH – VÍ DỤ VỀ CÔNG THỨC

CÔNG THỨC INDEX MATCH ĐỂ TRA CỨU TỪ PHẢI SANG TRÁI

Như đã đề cập, hàm VLOOKUP không thể kiểm tra dữ liệu bên trái của nó. Hàm INDEX MATCH trong Excel linh hoạt hơn và không thực sự quan tâm đến vị trí của các cột tra cứu.

Đối với ví dụ này, sẽ tiến hành thêm cột Xếp hạng (Rank) vào bên trái bảng mẫu của mình và cố gắng tìm ra cách thủ đô Moscow của Nga xếp hạng về dân số.

Với giá trị tra cứu trong G1, hãy sử dụng công thức sau để tìm kiếm trong C2: C10 và trả về giá trị tương ứng từ A2: A10:

=INDEX(A2:A10,MATCH(G1,C2:C10,0))

Chú ý: Nếu bạn định sử dụng công thức INDEX MATCH của mình cho nhiều ô, nên nhớ khóa cả hai phạm vi bằng tham chiếu ô tuyệt đối (như $ A $ 2: $ A $ 10 và $ C $ 2: 4C $ 10) để chúng không bị sai lệch khi sao chép công thức.

INDEX MATCH MATCH ĐỂ TÌM KIẾM THEO HÀNG VÀ CỘT

Trong các ví dụ trên, hàm INDEX MATCH sử dụng để thay thế cho hàm VLOOKUP cổ điển để tìm kiếm giá trị từ phạm vi một cột được xác định trước. Nhưng nếu bạn cần tra cứu trong nhiều hàng và nhiều cột thì sao? Nói cách khác, nếu bạn muốn thực hiện cái gọi là ma trận (matrix) hoặc tra cứu hai chiều thì làm như thế nào?

Nghe có vẻ phức tạp, nhưng công thức rất giống với hàm INDEX MATCH cơ bản của Excel, chỉ khác một điểm khác biệt. Đơn giản, sử dụng hai hàm MATCH – 1 MATCH để lấy số hàng và MATCH còn lại để lấy số cột.

Với quốc gia mục tiêu ở G1 (giá trị vlookup) và năm mục tiêu ở G2 (giá trị hlookup), công thức có dạng như sau:

=INDEX(B2:D11, MATCH(G1,A2:A11,0), MATCH(G2,B1:D1,0)) Công thức này hoạt động như thế nào

Để hiểu một công thức phức tạp bạn chỉ cần chia nó thành các phần nhỏ hơn và xem từng hàm riêng lẻ thực hiện những gì:

MATCH(G1,A2:A11,0) – tìm kiếm qua A2: A11 cho giá trị trong ô G1 (“Trung Quốc”) và trả về vị trí của nó, là 2.

MATCH(G2,B1:D1,0)) – tìm kiếm thông qua B1: D1 để lấy vị trí của giá trị trong ô G2 (“2015”), là 3.

Các số hàng và cột ở trên chuyển đến các đối số tương ứng của hàm INDEX:

INDEX(B2:D11, 2, 3)

Kết quả là bạn nhận được một giá trị tại giao điểm của hàng thứ 2 và cột thứ 3 trong phạm vi B2: D11, là giá trị trong ô D3.

EXCEL INDEX MATCH ĐỂ TRA CỨU NHIỀU TIÊU CHÍ

Nếu bạn đã đọc và tìm hiểu những bài hướng dẫn Excel Vlookup trước thì bạn sẽ biết đến công thức Vlookup với nhiều tiêu chí. Tuy nhiên, một hạn chế đáng kể đó là bạn phải thêm cột trợ giúp. Tuy nhiên hàm INDEX MATCH của Excel cũng có thể tra cứu với 2 tiêu chí trở lên mà không cần sửa đổi hoặc cơ cấu lại dữ liệu đầu vào của bạn.

Đây là công thức INDEX MATCH chung với nhiều tiêu chí:

{= INDEX ( return_range , MATCH (1, ( Criteria1 = RANGE1 ) * ( criteria2 = RANGE2 ), 0))}

Ghi chú. Công thức mảng phải được hoàn thành bằng phím tắt Ctrl + Shift + Enter.

Trong bảng mẫu bên dưới, giả sử bạn muốn tìm số tiền dựa trên 2 tiêu chí Khách hàng (Customer) và Sản phẩm (Product).

Công thức INDEX MATCH hoạt động như sau:

=INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10), 0))

Trong đó C2: C10 là phạm vi trả về giá trị, F1 là tiêu chí 1, A2: A10 là phạm vi để so sánh với tiêu chí 1, F2 là tiêu chí 2 và B2: B10 là phạm vi để so sánh với tiêu chí 2.

Hãy nhớ nhập công thức chính xác bằng cách nhấn Ctrl + Shift + Enter và Excel sẽ tự động đặt nó vào dấu ngoặc nhọn như trong ảnh chụp màn hình:

Nếu bạn không muốn sử dụng công thức mảng trong trang tính của mình, hãy thêm một hàm INDEX nữa vào công thức và kết thúc nó bằng một lần nhấn Enter thông thường:

Cách hoạt động của công thức này:

Các công thức tiếp cận tương tự như hàm INDEX MATCH cơ bản xem xét qua một cột duy nhất. Để đánh giá nhiều tiêu chí, bạn tạo hai hoặc nhiều mảng giá trị TRUE và FALSE đại diện cho các giá trị phù hợp và không khớp cho từng tiêu chí riêng lẻ, sau đó nhân các phần tử tương ứng của các mảng này. Phép nhân chuyển đổi TRUE và FALSE thành 1 và 0 tương ứng và tạo ra một mảng trong đó 1 tương ứng với các hàng khớp với tất cả các tiêu chí. Hàm MATCH với giá trị tra cứu là 1 tìm số “1” đầu tiên trong mảng và chuyển vị trí của nó tới INDEX, hàm này trả về một giá trị trong hàng này từ cột đã chỉ định.

Công thức non- array dựa vào khả năng của hàm INDEX để xử lý mảng nguyên bản. INDEX thứ hai được định cấu hình với 0 row_num để nó sẽ chuyển toàn bộ mảng cột thành MATCH.

EXCEL INDEX MATCH VỚI AVERAGE, MAX, MIN

Microsoft Excel có các hàm đặc biệt để tìm giá trị nhỏ nhất, lớn nhất và trung bình trong một phạm vi. Nhưng nếu bạn cần lấy một giá trị từ một ô khác được liên kết với các giá trị đó thì làm thế nào? Trong trường hợp này, hãy sử dụng hàm MAX , MIN hoặc AVERAGE cùng với INDEX MATCH.

Để tìm giá trị lớn nhất trong cột D và trả về giá trị từ cột C trong cùng một hàng, hãy sử dụng công thức sau:

=INDEX(C2:C10, MATCH(MAX(D2:D10), D2:D10, 0))

Để xác định giá trị nhỏ nhất trong cột D và kéo một giá trị được liên kết từ cột C, hãy sử dụng giá trị sau:

=INDEX(C2:C10, MATCH(MIN(D2:D10), D2:D10, 0))

INDEX MATCH VỚI AVERAGE

Để tính ra giá trị gần nhất với giá trị trung bình trong D2: D10 và nhận giá trị tương ứng từ cột C, đây là công thức để sử dụng:

=INDEX(C2:C10, MATCH(AVERAGE(D2:D10), D2:D10, -1 ))

Tùy thuộc vào cách sắp xếp dữ liệu của bạn mà lựa chọn 1 hoặc -1 cho đối số thứ ba (match_type) của hàm MATCH:

Nếu cột tra cứu của bạn (trong trường hợp trên là cột D) được sắp xếp tăng dần , hãy đặt 1. Công thức sẽ tính giá trị lớn nhất nhỏ hơn hoặc bằng giá trị trung bình.

Nếu cột tra cứu của bạn được sắp xếp giảm dần , hãy nhập -1. Công thức sẽ tính giá trị nhỏ nhất lớn hơn hoặc bằng giá trị trung bình.

Nếu mảng tra cứu của bạn chứa một giá trị chính xác bằng giá trị trung bình, bạn có thể nhập 0 để khớp chính xác. Không cần phân loại.

Trong ví dụ trên, các giá trị trong cột D được sắp xếp theo thứ tự giảm dần, vì vậy sử dụng -1 cho loại đối sánh. Kết quả nhận được là “Tokyo” vì dân số của nó (13.189.000) là đối sánh gần nhất lớn hơn trung bình (12.269.006).

SỬ DỤNG INDEX MATCH VỚI IFNA/IFERROR

Như bạn có thể đã nhận thấy, nếu một công thức INDEX MATCH trong Excel không thể tìm thấy giá trị tra cứu, nó sẽ tạo ra lỗi # N / A. Nếu bạn muốn thay thế ký hiệu lỗi tiêu chuẩn bằng một cái gì đó có ý nghĩa hơn, hãy đưa công thức INDEX MATCH của bạn vào hàm IFNA. Ví dụ:

=IFNA(INDEX(C2:C10, MATCH(F1,A2:A10,0)), “No match is found”)

Và bây giờ, nếu ai đó nhập bảng tra cứu không tồn tại trong phạm vi tra cứu, công thức sẽ thông báo rõ ràng cho người dùng rằng không tìm thấy kết quả phù hợp nào:

Nếu bạn muốn bắt tất cả các lỗi, không chỉ lỗi # N / A, hãy sử dụng hàm IFERROR thay vì IFNA:

=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), “Oops, something went wrong!”)

Cách Sử Dụng Hàm Index Trong Excel

Lượt Xem:11925

Hướng dẫn Excel này giải thích cách sử dụng hàm Excel INDEX với cú pháp và các ví dụ.

Hàm Microsoft Excel INDEX trả về một giá trị trong một bảng dựa trên giao điểm của một hàng và vị trí cột trong bảng đó. Hàng đầu tiên trong bảng là hàng 1 và cột đầu tiên trong bảng là cột 1.

Hàm INDEX là một hàm dựng sẵn trong Excel được phân loại là hàm tra cứu / tham chiếu . Nó có thể được sử dụng như một hàm trang tính (WS) trong Excel. Là một hàm trang tính, hàm INDEX có thể được nhập như một phần của công thức trong một ô của trang tính.

Cú pháp cho hàm INDEX trong Microsoft Excel là:

INDEX( table, row_number, column_number )

Một dải ô chứa bảng dữ liệu.

Vị trí hàng trong bảng nơi có giá trị mà bạn muốn tra cứu. Đây là vị trí hàng tương đối trong bảng chứ không phải số hàng thực tế trong trang tính.

Vị trí cột trong bảng nơi có giá trị mà bạn muốn tra cứu. Đây là vị trí cột tương đối trong bảng chứ không phải số cột thực tế trong trang tính.

Hàm INDEX trả về bất kỳ kiểu dữ liệu nào như một chuỗi, số, ngày tháng, v.v.

Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Ví dụ (dưới dạng hàm bảng tính)

Hãy khám phá cách sử dụng INDEX như một hàm trang tính trong Microsoft Excel.

Dựa trên bảng tính Excel ở trên, các ví dụ INDEX sau đây sẽ trả về:

Result: 10247 ‘Intersection of row1 and col1 (cell A2)

Result: “Apples” ‘Intersection of row1 and col2 (cell B2)

Result: $14.00 ‘Intersection of row1 and col3 (cell C2)

Result: 12 ‘Intersection of row1 and col4 (cell D2)

Result: 10249 ‘Intersection of row2 and col1 (cell A3)

Result: Grapes ‘Intersection of row5 and col2 (cell B6)

Bây giờ, hãy xem ví dụ =INDEX(A2:D6,1,1)t rả về giá trị 10247 và xem xét kỹ hơn tại sao.

Tham số đầu tiên trong hàm INDEX là bảng hoặc nguồn dữ liệu nơi tra cứu sẽ được thực hiện.

Trong ví dụ này, tham số đầu tiên là A2: D6 xác định phạm vi ô chứa dữ liệu.

Tham số thứ hai là số hàng được sử dụng để xác định vị trí giao nhau trong bảng. Giá trị 1 cho biết hàng đầu tiên trong bảng, giá trị là 2 là hàng thứ hai, v.v.

Trong ví dụ này, tham số thứ hai là 1 vì vậy chúng ta biết rằng giao lộ của chúng ta sẽ xuất hiện ở hàng đầu tiên trong bảng.

Tham số thứ ba là số cột được sử dụng để xác định vị trí giao nhau trong bảng. Giá trị 1 cho biết cột đầu tiên trong bảng, giá trị của 2 là cột thứ hai, v.v.

Trong ví dụ này, tham số thứ ba là 1 vì vậy chúng ta biết rằng giao lộ của chúng ta sẽ xuất hiện trong cột đầu tiên trong bảng.

Vì bây giờ chúng ta có các giá trị hàng và cột, chúng ta biết rằng chúng ta đang tìm kiếm giao điểm của row1 và col1 trong bảng dữ liệu. Điều này làm cho điểm giao nhau xảy ra tại ô A2 trong bảng để hàm INDEX trả về giá trị 10247.

Tags: Chua co du lieu

Hàm Mid Và Cách Sử Dụng Hàm Mid Trong Excel

Trình duyệt của bạn không hỗ trợ nghe audio.

Hàm MID trong Excel – Cú pháp và cách sử dụng cơ bản

Nói một cách hàn lâm hàm MID trong Excel được thiết kế để trích một chuỗi dữ liệu nằm ở giữa văn bản gốc. Nói một cách cụ thể hơn thì hàm MID trả về một số ký tự bất kì được chỉ định sẵn bắt đầu từ vị trí bạn thiết lập.

Hàm MID trong Excel có những đối số sau:

MID(text, start_num, num_chars)

Trong đó

Text là chuỗi văn bản gốc

Start_num là vị trí của dữ liệu đầu tiên mà bạn muốn trích xuất.

Num_chars là số ký tự trích xuất

Ví dụ, để lấy ra 3 ký tự từ chuỗi văn bản ở hàng A5, bắt đầu từ ký tự thứ 5, ta sử dụng công thức sau:

=MID(A5,5,3)

Kết quả hiển thị sẽ giống hình vẽ bên dưới:

Các điều cần biết về hàm MID trong Excel

Khi sử dụng hàm MID các bạn không thể vội vàng và cần phải ghi nhớ một số điều để tránh việc mắc phải những lỗi sai phổ biến.

Hàm MID luôn trả về một chuỗi văn bản, ngay cả khi chuỗi văn bản đó chỉ chứa toàn các chữ số. Điều này rất đáng lưu ý nếu bạn muốn sử dụng kết quả của phép tính sử dụng hàm MID trong các phép tính khác. Để chuyển đổi đầu ra của hàm MID là một số thì cần kết hợp hàm MID với hàm VALUE.

Nếu start_num lớn hơn tổng chiều dài của văn bản gốc, công thức tính theo hàm MID sẽ trả về một chuỗi rỗng (“”).

Nếu start_num nhỏ hơn 1, công thức tính của hàm MID trả về sẽ bị lỗi #VALUE.

Nếu num_chars nhỏ hơn 0 (số âm), công thức MID trả về #VALUE! Nếu num_chars bằng 0 sẽ trả về một chuỗi rỗng (ô rỗng).

Nếu tổng của start_num và num_chars vượt quá tổng chiều dài của chuối văn bản gốc, hàm Excel MID sẽ trả về một chuỗi ký tự bắt đầu từ start_num cho đến ký tự cuối cùng trong chuỗi văn bản gốc đó.

Ví dụ minh họa sử dụng công thức tính của hàm MID

Khi sử dụng hàm MID trong Excel bạn thường sẽ phải kết hợp nó với những hàm khác, như những ví dụ sau đây.

Cách xuất tên và họ

Chúng ta có thể trích xuất họ, tên lần lượt bằng cách sử dụng hàm LEFT và hàm RIGHT. Tuy nhiên, chúng ta cũng có thể thực hiện được bằng phương pháp khác.

Sử dụng hàm MID để trích xuất họ

Giả sử, họ và tên đầy đủ nằm trong ô A5, họ và tên được phân cách với nhau bằng dấu cách, bạn có thể trích xuất họ bằng cách sử dụng công thức sau:

=MID(A5,1,SEARCH(" ",A5)-1)

Chức năng SEARCH được dùng để bỏ dấu cách và trả lại vị trí của nó, vì thế bạn có thể bỏ qua dấu cách đó. Sau đó, bạn dùng hàm MID để trích ra chuỗi con bắt đầu từ ký tự đầu tiên cho đến ký tự đứng trước dấu cách, từ đó ta thu được họ.

Sử dụng hàm MID để trích xuất Tên

Để trích xuất tên từ ô A5, các bạn sử dụng công thức sau:

=TRIM(MID(A5,SEARCH(" ",A5),LEN(A5)))

Lần này, SEARCH được sử dụng để xác định vị trí bắt đầu (từ chỗ dấu cách). Không cần tính chính xác vị trí kết thúc (vì nếu start_num và num_chars cộng lại lớn hơn tổng chiều dài chuỗi thì hàm sẽ trả về tất cả các ký tự còn lại trong chuỗi văn bản gốc). Vì vậy, trong đối số num_chars, bạn chỉ cần cung cấp tổng chiều dài của chuỗi ban đầu được trả về bằng cách sử dụng hàm chức năng LEN. Nếu không dùng hàm LEN thì bạn có thể đặt một số thật dài cho chuỗi ký tự cần trích xuất, ví dụ như 100. Cuối cùng hàm TRIM sẽ loại bỏ phần dấu cách và cho ra kết quả như sau:

Tiếp tục áp dụng công thức trên với ô kết quả chúng ta thu được Tên

Cách trích xuất chuỗi ký tự nằm giữa 2 dấu cách

Như ví dụ ở bên trên, nếu ngoài họ và tên còn chứa tên đệm thì làm thế nào mới trích xuất được tên đệm đó?

Bạn có thể thực hiện theo phương pháp sau đây:

Kết hợp lại với nhau chúng ta có được công thức MID để trích ra chuỗi ký tự đứng giữa hai khoảng không gian tạo bởi hai dấu cách:

Giống như ở ví dụ trước, chúng ta vẫn sử dụng hàm SEARCH để xác định vị trí của dấu cách đầu tiên, cộng thêm 1 vào đó để chuỗi ký tự trích ra bắt đầu bằng ký tự đứng ngay sau dấu cách. Từ đó, bạn sẽ có được tham số start_num từ công thức MID: SEARCH(” “,A5)+1

Tiếp theo, để có được vị trí của khoảng cách thứ 2 thì chúng ta sử dụng hàm SEARCH với việc bắt đầu tìm kiếm từ ký tự đầu tiên sau dấu cách thứ 2: SEARCH(” “,A5,SEARCH((” ” ,A5)+1)

Để có được chuỗi ký tự trả về, cần trừ đi vị trí 2 khoảng không của 2 dấu cách. Từ đó, chúng ta có đối số num_chars: SEARCH (” “, A5, SEARCH (” “,A5)+1) – SEARCH (” “,A5)

Các bạn có thể xem kết quả trong hình ảnh bên dưới:

=MID(A5,SEARCH(” “,A5)+1,SEARCH(” “,A5,SEARCH(” “,A5)+1)-SEARCH(” “,A5)-1)

Tương tự, bạn có thể trích xuất một chuỗi con nằm giữa hai dấu cách bất kỳ:

MID(string,SEARCH(delimiter, string)+1,SEARCH(delimiter, string,SEARCH(delimiter, string)+1)-SEARCH(delimiter, string)-1)

Ví dụ, để trích ra một chuỗi con được ngăn cách bằng dấu phẩy và khoảng cách, bạn sử dụng công thức sau:

=MID(A5,SEARCH(",",A5)+1,SEARCH(",",A5,SEARCH(",",A5)+1)-SEARCH(",",A5)-1)

Xem hình bên dưới để thấy sự hiệu quả của công thức này:

Trích xuất từ thứ N trong một chuỗi văn bản

Ví dụ này là một ví dụ điển hình cho việc kết hợp sáng tạo công thức MID, kết hợp của 5 hàm khác nhau.

Ta có công thức chung như sau:

TRIM(MID(SUBSTITUTE(string," ",REPT(" ",LEN(string))), (N-1)*LEN(string)+1, LEN(string)))

Trong đó:

LEN – để lấy được tổng chiều dài của chuỗi văn bản gốc

REPT – để lặp lại một ký tự cụ thể với số lần nhất định

SUBSTITUTE – thay thế một ký tự bằng một ký tự khác

MID – trích xuất một chuỗi con

TRIM – loại bỏ khoảng không gian dấu cách thêm vào

Ví dụ, để trích xuất từ thứ 2 trong chuỗi văn bản ô A5, ta sử dụng công thức sau:

=TRIM(MID(SUBSTITUTE(A5," ",REPT(" ",LEN(A5))), (2-1)*LEN(A5)+1, LEN(A5)))

Hoặc, bạn có thể nhập số thứ tự từ cần trích xuất (N) trong nhiều ô trong cùng công thức, như hình chụp màn hình bên dưới:

String là chuỗi văn bản ban đầu chứa dữ liệu bạn muốn trích xuất.

N là số thứ tự của từ cần trích xuất

Cách hoạt động của công thức trên như sau:

Công thức trên hoạt động tốt trong hầu hết các trường hợp nhưng nếu có từ 2 khoảng trống sát nhau giữa các từ thì kết quả nhận được sẽ bị sai. Để khắc phục lỗi sai này thì nên lồng ghép hàm TRIM vào hàm SUBSTITUTE để loại bỏ các khoảng trống nằm giữa những khoảng trống khác.

Nếu chuỗi văn bản gốc chứa nhiều khoảng trống giữa các từ và các từ trong đó quá lớn hoặc quá nhỏ thì bạn nên thêm hàm TRIM lồng trong mỗi hàm LEN để tránh xảy ra lỗi.

Công thức này có vẻ khá phức tạp nhưng lại đảm bảo không xảy ra lỗi.

Trích xuất một từ chứa một hoặc nhiều ký tự cụ thể:

Hàm SUBSTITUTE và REPT thay thế mỗi khoảng trống đơn lẻ trong một chuỗi bằng nhiều khoảng trống. Số lượng khoảng trống được thêm vào bằng tổng chiều dài của chuỗi văn bản gốc được trả về bới hàm LEN: SUBSTITUTE(A2,” “,REPT(” “,LEN(A2)))

Bạn có thể coi kết quả mà bạn muốn trích ra từ chuỗi văn bản gốc cũng giống như một “hành tinh nhỏ” trôi dạt trong một không gian rộng lớn gồm: khoảng trống-từ thứ nhất-khoảng trống-từ thứ hai-khoảng trống-từ thứ ba-…. Từ chuỗi văn bản như vậy chúng ta nhận được đối số của công thức MID.

Tiếp đến, bạn tính ra vị trí bắt đầu của chuỗi con mà bạn cần trích xuất (đối số start_num) sử dụng phương trình sau: (N-1) * LEN (A1) +1. Phép tính này trả về ví trí của ký tự đầu tiên trong chuỗi con cần trích xuất hoặc vị trí của một số khoảng trống trong tổng số những khoảng trống trước đó.

Số ký tự trích xuất (đối số num_chars) là phần đơn giản nhất vì chúng ta có thể tìm ra bằng cách lấy tổng chiều dài của chuỗi văn bản gốc: LEN(A2). Qua đó, bạn sẽ loại bỏ được những khoảng trống trong chuỗi dài gồm cả khoảng trống và từ.

Cuối cùng, hàm TRIM sẽ loại bỏ khoảng trống nằm ở đầu và cuối.

TRIM(MID(SUBSTITUTE(string,” “,REPT(” “,99)),MAX(1,FIND(char,SUBSTITUTE(string,” “,REPT(” “,99)))-50),99))

Giả sử văn bản gốc nằm ở ô A5, bạn cần tìm kiếm chuỗi con chứa kí tự “@” (a còng) bạn dùng công thức sau:

Tương tự, bạn có thể trích tên trang web (dựa trên “www”), ….

Cách hoạt động của công thức trên như sau

Giống như trong ví dụ trước, các hàm SUBSTITUTE và hàm REPT biến mọi khoảng trống trong chuỗi văn bản gốc thành nhiều khoảng trống, chính xác hơn là 99 khoảng trắng.

Hàm FIND xác định vị trí của ký tự mong muốn (trong ví dụ này là $), tiếp đó bạn trừ đi 50. Làm như vậy bạn sẽ nhận lại được 50 ký tự và đặt ở giữa khối 99 khoảng trống đứng trước chuỗi con chứa ký tự được chỉ định.

Hàm MAX được sử dụng để xử lý tình huống khi chuỗi con mong muốn xuất hiện ở đầu chuỗi văn bản ban đầu. Trong trường hợp này, kết quả của FIND () – 50 sẽ là số âm, và MAX (1, FIND () – 50) sẽ được thay thế bằng 1.

=TRIM(MID(SUBSTITUTE(A5,” “,REPT(” “,99)),MAX(1,FIND(“@”,SUBSTITUTE(A5,” “,REPT(” “,99)))-50),99))

Qua đó, hàm MID sẽ thu thập 99 ký tự tiếp theo và trả về chuỗi con bạn cần. Hàm TRIM giúp bạn loại bỏ hết những khoảng trống ở xung quanh chuỗi con bạn cần đó.

Mẹo: Nếu chuỗi văn bản gốc quá lớn, bạn có thể thay thế 99 và 50 thành con số lớn hơn, ví dụ như 1000 và 500.

Cách khiến hàm MID trả về 1 số

Giống như những hàm văn bản khác, Excel MID luôn trả về chuỗi văn bản, ngay cả khi nó chứa các chữ số trông giống như một con số. Để chuyển đầu ra thành một số, chúng ta chỉ cần thay đổi hàm MID một chút, sử dụng VALUE để chuyển đổi một giá trị văn bản đại diện cho một số.

Ví dụ, để trích xuất 3 ký tự, bắt đầu từ ký tự thứ 5 và chuyển chuỗi văn bản kết quả thành dạng số thì ta sử dụng công thức:

=VALUE(MID(A5,5,3))

Với cách tiếp cận tương tự áp dụng cho những công thức phức tạp hơn. Như trong ví dụ trên, giả sử các mã lỗi (Erro) có độ dài khác nhau thì bạn vẫn có thể trích xuất chúng bằng cách sử dụng công thức MID để nhận được chuỗi ký tự con nằm giữa hai dấu hai chấm, lồng bên trong hàm VALUE:

=VALUE(MID(A5,SEARCH(“:”,A5)+1,SEARCH(“:”,A5,SEARCH(“:”,A5)+1)-SEARCH(“:”,A5)-1))

Bạn đang đọc nội dung bài viết Cách Sử Dụng Hàm Index Và Hàm Match Trong Excel trên website Thaiphuminh.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!