Bài viết này diễn giải ưu điểm chính của hàm INDEX và hàm MATCH trong Excel – các ưu điểm khiến nó phù hợp hơn so với hàm VLOOKUP. Bạn sẽ tìm thấy nhiều ví dụ công thức – những ví dụ giúp bạn dễ dàng xử lý các nhiệm vụ phức tạp khi hàm VLOOKUP không làm được.
Trong những bài viết gần đây tôi đã nỗ lực hết mình để lý giải những điểm cơ bản của hàm VLOOKUP trong Excel cho người mới mở màn và phân phối những ví dụ công thức hàm VLOOKUP nâng cao. Và giờ đây, tôi sẽ cố gắng nỗ lực nếu đã không nói về việc sử dụng hàm VLOOKUP, thì tối thiểu sẽ phân phối cách thay thế sửa chữa để thực thi phép tìm kiếm theo cột trong Excel .
“ Tôi cần nó để làm gì ? ” bạn hoàn toàn có thể hỏi tôi như thế. Bởi vì hàm VLOOKUP không phải là hàm tìm kiếm duy nhất có sẵn trong Excel, và việc có nhiều hạn chế hoàn toàn có thể ngăn cản bạn đạt được hiệu quả mong ước trong nhiều trường hợp. Mặt khác, hàm INDEX và hàm MATCH trong Excel thì linh động hơn và có những tính năng nhất định khiến chúng tiêu biểu vượt trội hơn hàm VLOOKUP trên nhiều phương diện .
Tóm Tắt
HÀM INDEX VÀ HÀM MATCH TRONG EXCEL – NHỮNG ĐIỀU CƠ BẢN
Vì mục đích của bài viết này là để nói về cách tìm kiếm thay thế trong Excel bằng cách kết hợp hàm INDEX với hàm MATCH, nên chúng ta sẽ không nói nhiều về cú pháp và cách sử dụng. Chúng ta sẽ chỉ nói đủ để hiểu ý tưởng chung rồi nói kỹ hơn về các ví dụ công thức – các ví dụ cho thấy ưu điểm của việc sử dụng hàm INDEX và hàm MATCH thay cho hàm VLOOKUP.
CÚ PHÁP VÀ CÁCH DÙNG CỦA HÀM INDEX:
Hàm INDEX trong Excel trả về giá trị từ mảng dựa trên số thứ tự hàng và cột bạn xác lập. Cú pháp hàm INDEX rất đơn thuần :
INDEX(array, row_num, [column_num])
Dưới đây là cách lý giải đơn thuần cho mỗi thông số kỹ thuật :
array – đây là dải ô nơi bạn muốn giá trị được trả về.
row_num – số thứ tự của hàng trong mảng mà bạn muốn giá trị được trả về. Nếu bỏ trống, thì colum_num là bắt buộc.
colum_num – số thứ tự của cột trong mảng mà bạn muốn giá trị được trả về. Nếu bỏ trống, thì row_num là bắt buộc.
Nếu cả thông số kỹ thuật row_num và colum_num đều được sử dụng, thì hàm INDEX sẽ trả về giá trị ở ô là giao điểm của hàng và cột xác lập .
Và đây là ví dụ đơn giản nhất của hàm INDEX:
=INDEX(A1:C10,2,3)
Công thức tìm kiếm dải ô A1 : C10 rồi trả về giá trị của ô ở hàng thứ hai và cột thứ ba, đơn cử là ô C2 .
Rất dễ, đúng không ? Tuy nhiên, khi giải quyết và xử lý tài liệu thực, bạn hầu hết không biết được mình muốn hàng nào cột nào, đây là nguyên do bạn cần sự giúp sức của hàm MATCH .
CÚ PHÁP VÀ CÁCH DÙNG CỦA HÀM MATCH:
Hàm MATCH trong Excel tìm kiếm giá trị cần tìm trong dải ô, rồi trả về vị trí tương đối của ô đó trong dải ô .
Ví dụ, nếu dải ô B1 : B3 chứa những giá trị “ New-York ”, “ Paris ”, “ London ”, rồi công thức = MATCH ( “ London ”, B1 : B3, 0 ) trả về số 3 chính do “ London ” là mục thứ ba trong dải ô .
Cú pháp hàm MATCH có dạng dưới đây :
MATCH(lookup_value, lookup_array, [match_type])
lookup_value – đây là chữ sô hay chuỗi ký tự mà bạn tìm kiếm. Đây có thể là một giá trị, một tham chiếu ô hay một giá trị lô gic.
lookup_array – dải ô được tìm kiếm.
match_type – thông số này nói cho hàm MATCH biết bạn muốn trả về sự phù hợp tuyệt đối hay sự phù hợp tương đối.
1 hay bỏ trống – tìm giá trị lớn nhất – giá trị nhỏ hơn hay bằng giá trị cần tìm. Các giá trị trong mảng cần tìm phải được lọc theo thứ tự tăng dần, cụ thể là từ nhỏ nhất đến lớn nhất.
0 – tìm giá trị đầu tiên bằng giá trị cần tìm. Trong sự kết hợp của hàm INDEX và hàm MATCH, bạn hầu như luôn luôn cần sự phù hợp tuyệt đối, vì thế câu lệnh thứ ba của hàm MATCH là “0”.
-1 – tìm giá trị nhỏ nhất lớn hơn hay bằng giá trị cần tìm. Các giá trị trong mảng cần tìm phải được sắp xếp theo thứ tự giảm dần, cụ thể là từ lớn nhất đến nhỏ nhất.
Lúc đầu, tính có ích của hàm MATCH hoàn toàn có thể không rõ ràng. Ai chăm sóc đến vị trí của giá trị trong dải ô chứ ? Điều ta thật sự muốn biết đó chính là giá trị .
Hãy để tôi nhắc bạn nhớ rằng vị trí tương đối của giá trị cần tìm ( đơn cử là số thứ tự của cột hay / và hàng ) chính là tổng thể những gì bạn cần để nhập vào câu lệnh row_num hay / và column_num của hàm INDEX. Có thể bạn vẫn còn nhớ, hàm INDEX hoàn toàn có thể trả về giá trị ở giao điểm của hàng và cột xác lập, nhưng nó không hề xác lập một cách đúng mực bạn muốn cột nào hàng nào .
Xem thêm : Hướng dẫn học Excel cơ bản
CÁCH SỬ DỤNG HÀM INDEX MATCH TRONG EXCEL:
Bây giờ bạn đã biết những điều cơ bản của hai hàm trên, tôi tin rằng bạn hoàn toàn có thể mường tượng hai hàm này sẽ phối hợp như thế nào .
Hàm MATCH xác lập vị trí tương đối của giá trị cần tìm trong dải ô xác lập. Từ đó, hàm INDEX sử dụng số lượng đó, hay những số lượng đó, rồi trả về giá trị ở ô tương ứng .
Vẫn gặp khó khăn vất vả trong việc mường tượng ra đúng không ? Hãy nghĩ về hàm INDEX / MATCH theo cách này :
=INDEX (column to return a value from, MATCH (lookup value, column to lookup against, 0))
Tôi tin rằng việc xem ví dụ sẽ dễ hiểu hơn. Giả sử bạn có một list những Thành Phố Hà Nội như thế này :
Hãy tìm dân số của một Thành Phố Hà Nội nào đó, giả sử thủ đô hà nội của Nhật Bản đi, hãy dùng công thức INDEX / MATCH dưới đây :
= INDEX ( USD D USD 2 : USD D USD 10, MATCH ( “ Nhật Bản ”, USD B USD 2 : USD B $ 10,0 ) )
Bây giờ, hãy nghiên cứu và phân tích xem mỗi thành phần của công thức có tính năng gì :
Hàm MATCH tìm kiếm giá trị cần tìm “ Nhật Bản ” ở cột B, đúng chuẩn hơn là dải ô B2 : B10, rồi trả về số 3, do tại “ Nhật Bản ” nằm thứ ba trong list .
Hàm INDEX lấy số 3 từ thông số kỹ thuật thứ hai ( row_num ), thông số kỹ thuật này chỉ rõ bạn muốn trả về giá trị từ hàng nào, rồi trở thành công thức đơn thuần như sau = INDEX ( USD D USD 2 : USD D $ 10,3 ). Nói một cách đơn thuần, công thức được đọc như sau : tìm trong dải ô D2 : D10 rồi trả về giá trị của ô ở hàng thứ ba, đơn cử là ô D4, do tại tất cả chúng ta mở màn đếm từ hàng thứ hai .
Và đây là hiệu quả bạn có được trong Excel :
Quan trọng! Số hàng và số cột trong mảng INDEX nên lần lượt khớp với số hàng và số cột trong thông số row_num hay/và column_num trong hàm MATCH. Nếu không thì, công thức sẽ trả về kết quả không chính xác.
Đợi đã … tại sao tất cả chúng ta không dùng công thức VLOOKUP sau ? Mục đích của việc tiêu tốn lãng phí thời hạn cố gắng nỗ lực tìm kiếm sự sửa chữa thay thế huyền bí của hàm INDEX / MATCH trong Excel là gì ?
= VLOOKUP ( “ Nhật Bản ”, USD B USD 2 : USD D $ 2,3 )
Trong trường hợp này, chẳng có mục tiêu nào cả 🙂 Ví dụ đơn thuần này chỉ dành cho việc diễn giải mà thôi, để bạn thấy được cách hàm INDEX và hàm MATCH tích hợp với nhau. Các ví dụ khác bên dưới sẽ cho bạn thấy năng lực thật sự của sự phối hợp hàm MATCH / INDEX – điều này giúp bạn thuận tiện giải quyết và xử lý nhiều viễn cảnh phức tạp khi hàm VLOOKUP không giải quyết và xử lý được .
TẠI SAO SỰ KẾT HỢP HÀM INDEX VÀ HÀM MATCH TRONG EXCEL LẠI TỐT HƠN HÀM VLOOKUP
Khi quyết định hành động công thức dùng tìm kiếm theo cột, phần đông những chuyên viên Excel cho rằng hàm INDEX phối hợp với hàm MATCH thì hữu dụng hơn hàm VLOOKUP rất nhiều. Tuy nhiên, rất nhiều người dùng Excel vẫn viện đến hàm VLOOKUP vì nó đơn thuần. Điều này diễn ra vì rất ít người hiểu toàn vẹn toàn bộ những quyền lợi của việc đổi từ hàm VLOOKUP thành hàm INDEX tích hợp với hàm MATCH, và vì nguyên do này nên chẳng ai chịu dành thời hạn học một công thức phức tạp hơn .
Dưới đây, tôi sẽ cố gắng nỗ lực chỉ ra những quyền lợi chính của việc phối hợp hàm INDEX với hàm MATCH trong Excel, rồi để bạn quyết định liệu bạn vẫn dùng hàm VLOOKUP hay dùng hàm INDEX tích hợp với hàm MATCH .
4 LỢI ÍCH CHÍNH CỦA VIỆC KẾT HỢP HÀM INDEX VỚI HÀM MATCH TRONG EXCEL:
- Tìm kiếm từ phải sang trái. Bất cứ người dùng thông thạo nào cũng biết, hàm VLOOKUP trong Excel không thể tìm kiếm phía bên trái, có nghĩa là giá trị cần tìm phải luôn nằm ở cột cận trái của dải ô cần tìm. Với hàm INDEX MATCH, cột cần tìm của bạn có thể nằm ở phía bên phải của bảng.
- Chèn hay xóa cột một cách an toàn. Công thức VLOOKUP bị lỗi hay trả về kết quả không chính xác khi xóa hay thêm cột mới vào bảng cần tìm. Với hàm VLOOKUP, bất cứ cột nào được chèn hay xóa cũng có thể thay đổi kết quả trả về bởi công thức bởi vì cú pháp hàm VLOOKUP yêu cầu định rõ toàn bộ mảng bảng và một số nhất định để chỉ bạn muốn lấy dữ liệu từ cột nào.
Ví dụ: nếu bạn có bảng A1:C10 và muốn trả về giá trị từ cột B, bạn sẽ đặt số “2” ở thông số thứ ba (col_index_num) của công thức VLOOKUP, ví dụ =VLOOKUP(“lookup value”, A1:C10, 2). Nếu sau đó, bạn chèn một cột mới giữa cột A và cột B, thì bạn phải thay đổi “2” thành “3” trong công thức, nếu không thì nó sẽ trả về giá trị từ cột mới được chèn vào.
- Với hàm INDEX MATCH, bạn có thể xóa hay chèn cột mới vào bảng cần tìm mà không thay đổi kết quả bởi vì bạn định rõ cột chứa giá trị cần tìm một cách trực tiếp. Và đây là một lợi ích rất lớn, nhất là khi xử lý chuỗi dữ liệu lớn, vì bạn có khả năng chèn hay xóa mà không cần lo lắng về việc cập nhật mỗi công thức tìm kiếm liên quan.
- Không đặt giới hạn cho kích thước dữ liệu cần tìm. Khi sử dụng hàm VLOOKUP, hãy nhớ rằng tổng chiều dài của tiêu chuẩn tìm kiếm không nên vượt quá 255 ký tự, nếu không thì bạn sẽ nhận được lỗi #VALUE!. Vì thế, nếu chuỗi dữ liệu của bạn chứa một chuỗi dài, thì hàm INDEX MATCH là giải pháp khả thi duy nhất.
Giả sử, bạn sử dụng công thức VLOOKUP dưới đây để tìm kiếm từ ô B5 đến ô D10 cho giá trị ở ô A2 :
= VLOOKUP ( A2, B5 : D10, 3, FALSE )
Công thức sẽ không chạy nếu giá trị cần tìm ở ô A2 vượt quá 255 ký tự. Thay vào đó, bạn nên sử dụng hàm INDEX / MATCH tựa như :
= INDEX ( D5 : D10, MATCH ( TRUE, INDEX ( B5 : B10 = A2, 0 ), 0 ) )
- Tốc độ xử lý nhanh hơn. Nếu bạn đang xử lý một bảng tương đối nhỏ, thì điểm khác biệt trong việc xử lý của Excel sẽ hầu như không thể nhận thấy, nhất là ở các phiên bản gần đây. Nhưng nếu bạn sử dụng bảng tính lớn có hàng ngàn hàng và hàng ngàn công thức cần tìm, Excel sẽ làm việc nhanh hơn nhiều nếu bạn sử dụng hàm INDEX/MATCH thay vì hàm VLOOKUP. Nói chung, việc sử dụng công thức INDEX/MATCH sẽ tăng tốc độ xử lý của Excel lên 13 % so với công thức VLOOKUP tương tự.
Ảnh hưởng của hàm VLOOKUP lên việc giải quyết và xử lý của Excel hoàn toàn có thể sẽ rõ ràng hơn nếu sổ thao tác của bạn chứa hàng trăm công thức mảng phức tạp như VLOOKUP và SUM. Điểm mấu chốt đó là việc kiểm tra mỗi giá trị trong mảng nhu yếu một hàm VLOOKUP khác. Vì thế, mảng càng có nhiều giá trị và sổ thao tác của bạn càng có nhiều công thức mảng, thì Excel giải quyết và xử lý càng chậm .
Mặt khác, với hàm INDEX / MATCH, Excel chỉ phải xem xét cột tìm kiếm và cột trả về, nên nó giải quyết và xử lý công thức nhanh hơn nhiều .
HÀM INDEX VÀ HÀM MATCH TRONG EXCEL – VÍ DỤ CÔNG THỨC
Bây giờ, bạn đã biết nguyên do nên học hàm MATCH / INDEX, nên hãy đi vào phần mê hoặc nhất và xem xem bạn hoàn toàn có thể vận dụng nguyên tắc hay không .
CÁCH TÌM KIẾM GIÁ TRỊ SANG TRÁI VỚI HÀM INDEX VÀ HÀM MATCH
Như đã được nhắc đến trong bất kể bài hướng dẫn VLOOKUP nào, hàm Excel này không hề tìm kiếm bên trái của nó. Vì vậy, nếu cột cần tìm của bạn không phải là cột cận trái trong dải ô cần tìm, thì không khi nào công thức VLOOKUP trả về hiệu quả mà bạn mong ước .
Hàm INDEX MATCH trong Excel thì linh động hơn và thật sự không chăm sóc việc cột trả về nằm ở đâu. Ví dụ, tất cả chúng ta sẽ dùng một bảng – bảng này liệt kê thủ đô hà nội và dân số. Lần này, hãy lập một công thức INDEX MATCH tìm xem Hà Nội Thủ Đô của Nga, Matxcơva, xếp thứ mấy dựa theo dân số .
Như bạn hoàn toàn có thể thấy từ ảnh chụp màn hình hiển thị bên dưới, công thức sau đây hoạt động giải trí một cách trôi chảy :
= INDEX ( $ A $ 2 : USD A $ 10, MATCH ( “ Russia ”, USD B USD 2 : USD B $ 10,0 ) )
Bây giờ, có lẽ rằng bạn chẳng gặp chút khó khăn vất vả nào trong việc hiểu công thức hoạt động giải trí như thế nào :
Đầu tiên, bạn lập công thức MATCH đơn thuần tìm vị trí của Nga :
= MATCH ( “ Russia ”, USD B USD 2 : USD B $ 10,0 ) )
Rồi, bạn xác lập thông số kỹ thuật mảng cho hàm INDEX, trong trường hợp này là cột A ( A2 : A10 ) .
Cuối cùng, bạn phối hợp hai phần lại và có công thức :
=INDEX($A$2:$A$10,MATCH(“Russia”,$B$2:$B$10,0))
Mẹo. Việc sử dụng tham chiếu ô tuyệt đối luôn là một ý tưởng hay trong công thức INDEX và MATCH để dải ô cần tìm của bạn không bị phá hỏng khi bạn sao chép công thức sang ô khác.
TÍNH TOÁN VỚI HÀM INDEX MATCH TRONG EXCEL (AVERAGE, MAX, MIN)
Bạn hoàn toàn có thể lồng ghép những hàm Excel khác vào hàm INDEX MATCH, giả sử, để tìm giá trị nhỏ nhất hay lớn nhất, hay giá trị gần với giá trị trung bình nhất trong dải. Sau đây là vài ví dụ công thức cho bảng được dùng trong ví dụ trước :
| Chức năng | Ví dụ công thức | Miêu tả | Kết quả được trả về |
| Min | =INDEX($C$2:$C$10, MATCH(MIN($D$2:I$10), $D$2:D$10, 0)) | Tìm kiếm giá trị nhỏ nhất trong cột D rồi lấy một giá trị từ cột C trên cùng một hàng. | Bắc Kinh |
| Max | =INDEX($C$2:$C$10, MATCH(MAX($D$2:I$10), $D$2:D$10, 0)) | Tìm kiếm giá trị lớn nhất ở cột D rồi lấy một giá trị từ cột C trên cùng một hàng. | Lima |
| Average | =INDEX($C$2:$C$10, MATCH(AVERAGE($D$2:D$10), $D$2:D$10, 1)) | Tính giá trị trung bình trong dải ô D2:D10, tìm giá trị gần với giá trị trung bình nhất, rồi lấy giá trị tương ứng từ cột C. | Matx-cơ-va |
NHỮNG ĐIỀU CẦN NHỚ KHI SỬ DỤNG HÀM AVERAGE VỚI HÀM INDEX MATCH TRONG EXCEL
Khi phối hợp hàm AVERAGE với hàm INDEX / MATCH, thường thì bạn sẽ nhập “ 1 ” hay “ – 1 ” trong câu lệnh thứ ba ( match_type ) của hàm MATCH, nếu bạn không chắc mảng cần tìm có chứa giá trị bằng giá trị trung bình hay không. Nếu bạn không chắc, thì bạn hoàn toàn có thể nhập “ 0 ” cho sự tương thích tuyệt đối .
Nếu bạn setup 1, thì những giá trị trong cột cần tìm phải được sắp xếp theo thứ tự tăng dần, và công thức sẽ trả về giá trị lớn nhất – giá trị này hoàn toàn có thể nhỏ hơn hay bằng giá trị trung bình .
Nếu bạn setup – 1, thì những giá trị trong cột cần tìm phải được sắp xếp theo thứ tự giảm dần, và công thức sẽ trả về giá trị nhỏ nhất – giá trị này hoàn toàn có thể lớn hơn hay bằng giá trị trung bình .
Trong ví dụ của tất cả chúng ta, những giá trị ở cột D được sắp xếp theo thứ tự tăng dần, nên ta dùng “ 1 ” làm match type, và công thức Match + Index Match sẽ trả về “ Matxcơva ” vì dân số của nó ( 11,541,000 ) là giá trị nhỏ hơn nhưng gần với giá trị trung bình nhất ( 12,269,006 ) .
CÁCH SỬ DỤNG HÀM INDEX MATCH ĐỂ KIẾM GIÁ TRỊ THEO HÀNG VÀ THEO CỘT
Công thức này tương tự công thức VLOOKUP hai chiều – những công thức này được cho phép bạn tìm giá trị ở giao điểm của cột và hàng .
Trong ví dụ này, hàm INDEX MATCH trong Excel của bạn sẽ rất giống với những công thức khác mà tất cả chúng ta đã bàn trong bài hướng dẫn này, ngoại trừ một điểm độc lạ. Đoán xem nào ?
Có lẽ bạn vẫn còn nhớ, cú pháp hàm INDEX được cho phép bạn triển khai cả hai điều đó : INDEX ( array, row_num, [ column_num ] ). Và tôi xin chúc mừng những bạn đã đoán đúng 🙂
Để khởi đầu, hãy lập công thức tổng quát để xứ lý việc tìm kiếm ma trận này. Chúng ta chỉ cần dùng công thức INDEX / MATCH mà bạn đã biết rồi bổ trợ thêm một hàm MATCH vào nó, việc này sẽ trả về số thứ tự của cột .
= INDEX ( lookup table, MATCH ( vertical lookup value, column to lookup against, 0 ), MATCH ( horizontal lookup value, row to lookup against, 0 ) )
Xin hãy quan tâm rằng bạn phải định rõ hàng loạt bảng trong câu lệnh mảng của hàm INDEX trong trường hợp tìm kiếm theo hai cách .
Và giờ đây, hãy vận dụng mẫu công thức này. Dưới đây, bạn thấy list những thành phố đông dân nhất trên quốc tế. Giả sử, bạn muốn biết số dân của Mỹ năm năm ngoái :
Được rồi, hãy khởi đầu từ công thức nào. Bất cứ khi nào tôi cần tạo ra một công thức Excel phức tạp có gộp một hai vài hàm khác, thì tôi luôn viết từng hàm ra trước .
Vì vậy, bạn khởi đầu viết hai hàm MATCH – những hàm này sẽ trả về số thứ tự hàng và cột cho hàm INDEX của bạn .
Vertical match – bạn dò cột B, đúng chuẩn là từ ô B2 đến ô B11, để kiếm giá trị ở ô H2 ( “ Mỹ ” ), và hàm MATCH tương ứng có dạng như sau : = MATCH ( $ H USD 2, USD B USD 1 : USD B $ 11,0 ). Công thức MATCH này trả về giá trị 4 vì “ Mỹ ” là giá trị thứ tư ở cột B ( gồm có cột tiêu đề ) .
Horizontal match – bạn tìm giá trị ở cột H3 ( “ năm ngoái ” ) từ hàng 1, đơn cử là từ ô A1 đến ô E1 : = MATCH ( $ H $ 3, $ A $ 1 : USD E $ 1,0 ). Công thức MATCH này trả về số “ 5 ” chính do “ năm ngoái ” nằm ở cột thứ năm .
Xem thêm : Khóa học excel nâng cao TP. Hà Nội
Bây giờ, hãy đặt những công thức trên vào hàm INDEX, và tất cả chúng ta có :
= INDEX ( $ A $ 1 : USD E $ 11, MATCH ( $ H USD 2, USD B USD 1 : USD B $ 11,0 ), MATCH ( $ H $ 3, $ A $ 1 : USD E $ 1,0 ) )
Nếu bạn sửa chữa thay thế những hàm MATCH bằng những số được trả về, thì công thức sẽ dễ hiểu hơn rất nhiều : = INDEX ( $ A $ 1 : USD E $ 11, 4, 5, 0 ) )
Có nghĩa là, nó trả về giá trị ở giao điểm của hàng thứ tư và cột thứ năm trong dải ô A1 : E11, giá trị đó nằm ở ô E4. Dễ, đúng không nào ? Đúng rồi ! 🙂
SỬ DỤNG HÀM INDEX MATCH TÌM KIẾM KÈM THEO NHIỀU ĐIỀU KIỆN
Trong bài hướng dẫn sử dụng hàm VLOOKUP trong Excel, tôi đã diễn giải một ví dụ công thức tìm kiếm kèm theo nhiều điều kiện kèm theo. Tuy nhiên, hạn chế đáng kể của cách đó chính là cần phải thêm một cột trợ giúp. Tin tốt là hàm INDEX MATCH trong Excel cũng hoàn toàn có thể tìm kiếm giá trị ở hai cột, mà không cần cột trợ giúp nào cả !
Giả sử, bạn có một list đơn hàng và bạn muốn tính tổng dựa trên hai tiêu chuẩn “ Tên người mua ” và “ Sản phẩm ”. Một tác nhân phức tạp đó là một người mua hoàn toàn có thể mua nhiều loại sản phẩm và tên người mua được liệt kê một cách ngẫu nhiên trong bảng cần tìm :
Công thức mảng INDEX MATCH sau đây sẽ giúp ích rất nhiều :
= INDEX ( ‘ Lookup table ’ ! $ A $ 2 : USD C USD 13, MATCH ( 1, ( A2 = ’ Lookup table ’ ! $ A $ 2 : USD A $ 13 ) * ( B2 = ’ Lookup table ’ ! USD B USD 2 : USD B USD 13 ), 0 ), 3 )
Công thức này thì phức tạp hơn nhiều so với bất kỳ công thức nào – những công thức tất cả chúng ta đã bàn luận ngày thời điểm ngày hôm nay, nhưng hãy tận dụng kiến thức và kỹ năng về hàm INDEX MATCH trong Excel mà bạn có, tôi tin rằng bạn hoàn toàn có thể giải quyết và xử lý nó .
Phần khó nhất đó là hàm MATCH, vì vậy hãy nghiên cứu và phân tích nó trước :
MATCH ( 1, ( A2 = ’ Lookup table ’ ! $ A $ 2 : USD A $ 13 ), 0 ) * ( B2 = ’ Lookup table ’ ! USD B USD 2 : USD B USD 13 ), 0 )
Những gì tất cả chúng ta có đó là 3 câu lệnh dưới đây :
Lookup_value : 1
Lookup_array : ( A2 = ’ Lookup table ’ ! $ A $ 2 : USD A $ 13 ), 0 ) * ( B2 = ’ Lookup table ’ ! USD B USD 2 : USD B $ 13 )
Match_type : 0
Thông số thứ nhất và thứ ba thì rõ ràng rồi – hàm tìm kiếm “ 1 ”, rồi trả về giá trị tìm được tiên phong .
Bây giờ, câu hỏi chính là – tại sao tất cả chúng ta lại tìm “ 1 ” ? Để có được câu vấn đáp, hãy xem xét kỹ hơn mảng cần tìm của tất cả chúng ta .
Việc tất cả chúng ta làm ở đây đó là tìm kiếm giá trị tiên phong ( A2 ) trong cột “ Tên người mua ” trong bảng chính và tìm tên đó trong tổng thể tên người mua ở bảng cần tìm ( A2 : A13 ). Nếu tìm thấy sự trùng khớp, thì phương trình trả về TRUE, nếu không thì sẽ trả về FALSE. Rồi tất cả chúng ta làm tương tự như với những giá trị ở cột B ( “ Sản phẩm ” ) .
Để hiểu rõ hơn những gì tôi đang nói, bạn hoàn toàn có thể chọn mảng trong thanh công thức, rồi nhấn phím F9 để xem mỗi phần được chọn được nhìn nhận như thế nào :
Có lẽ bạn đã biết, trong công thức Excel, giá trị lô gic TRUE tương tự với 1, và FALSE tương tự với 0. Và vì dấu hoa thị ( * ) đóng vai trò làm toán tử AND trong công thức mảng, nên tất cả chúng ta nhận được “ 1 ” chỉ khi sự tương thích được tìm thấy ở cả hai cột, nếu không thì tất cả chúng ta nhận được “ 0 ”. Vì thế, tác dụng của thao tác này là một mảng gồm 1 và 0, trong đó “ 1 ” là giá trị cung ứng cả hai điều kiện kèm theo xác lập. Nếu bảng cần tìm của bạn không có bất kể hàng nào trùng lặp nhau, thì sẽ chỉ có một giá trị “ 1 ” trong mảng. Và vì “ 1 ” là giá trị cần tìm của tất cả chúng ta, nên hàm MATCH trả về vị trí tương đối của hàng đó – việc này được diễn giải trong ảnh chụp màn hình hiển thị dưới đây :
Đồng thời, xin hãy quan tâm rằng bạn phải sử dụng thông số kỹ thuật tùy chọn thứ ba ( colum_num ) của hàm INDEX. Đây là vì bạn đã định rõ hàng loạt bảng ở thông số kỹ thuật thứ nhất ( array ), và bạn cần phải để hàm biết bạn muốn lấy giá trị của cột nào. trong trường hợp của tất cả chúng ta, đó là cột C ( “ Tổng ” ), nên tất cả chúng ta nhập 3 trong câu lệnh thứ ba của hàm INDEX .
Và sau cuối, vì tất cả chúng ta cần kiểm tra mỗi ô của mảng, nên công thức INDEX MATCH nên là công thức mảng. Dấu hiệu phân biệt rõ ràng nhất của công thức mảng đó là { dấu ngoặc nhọn } trong đó nó được đóng khung bởi thanh công thức. Hãy nhớ nhấn Ctrl + Shift + Enter để triển khai xong công thức sau khi bạn đã nhập xong .
Nếu mọi thứ đều được triển khai đúng chuẩn, thì bạn sẽ có hiệu quả tựa như như những gì bạn nhìn thấy dưới đây :
Xem thêm : Học lập trình VBA trong Excel ở đâu
SỬ DỤNG HÀM INDEX/MACTH VỚI HÀM IFERROR TRONG EXCEL
Có lẽ bạn đã nhận ra ( hơn một lần 🙂 nếu bạn nhập một giá trị vô hiệu, đơn cử là giá trị không sống sót trong mảng cần tìm, thì hàm INDEX / MATCH sẽ trả về lỗi # N / A hay lỗi # VALUE. Nếu bạn sửa chữa thay thế nó bằng một cái gì đó có nghĩa, thì bạn hoàn toàn có thể lồng hàm INDEX / MATCH trong hàm IFERROR .
Cú pháp của hàm IFERROR rất đơn thuần :
IFERROR ( value, value_if_error )
Trong đó câu lệnh value chính là giá trị được kiểm tra lỗi ( tác dụng của công thức INDEX MATCH trong trường hợp của tất cả chúng ta ) ; và value_if_error chính là giá trị cần trả về nếu công thức có lỗi .
Ví dụ, bạn hoàn toàn có thể lồng công thức trong ví dụ trước vào hàm IFERROR theo cách này :
=IFERROR(INDEX($A$1:$E$11, MATCH($G$2,$B$1:$B$11,0), MATCH($G$3,$A$1:$E$1,0)),
“ Không tìm thấy. Xin hãy thử lại ! ” )
Và giờ đây, nếu ai đó nhập một mục vô hiệu, thì công thức sẽ cho hiệu quả như trong ảnh chụp màn hình hiển thị dưới đây :
Nếu bạn muốn có một ô trống khi lỗi được trả về, thì bạn hoàn toàn có thể sử dụng dấu ngoặc kép ( “ ” ) trong thông số kỹ thuật thứ hai của hàm IFERROR, giống như thế này :
Tôi kỳ vọng tối thiểu có một công thức trong bài hướng dẫn này sẽ có ích so với bạn. Nếu bạn gặp phải trách nhiệm tìm kiếm khác mà bạn không tìm thấy giải pháp cho trách nhiệm đó ở đây, thì hãy cứ phản hồi ở đây rồi tất cả chúng ta sẽ cùng nhau tìm hiểu và khám phá nó .
– – – – –
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel:
Một số hàm cơ bản thường gặp như :
- SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
- COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
- Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
- Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…
Một số công cụ hay sử dụng như :
- Định dạng theo điều kiện với Conditional formatting
- Thiết lập điều kiện nhập dữ liệu với Data Validation
- Cách đặt Name và sử dụng Name trong công thức
- Lập báo cáo với Pivot Table…
Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: Học Excel Online
Source: https://final-blade.com
Category : Kiến thức Internet
