Top 11 # Cách Dùng Hàm Upper Trong Excel Xem Nhiều Nhất, Mới Nhất 3/2023 # Top Trend

1 Hàm UPPER

Mô tả:

Hàm Microsoft Excel UPPER cho phép bạn chuyển đổi văn bản từ chữ thường thành tất cả chữ hoa. Hàm UPPER là một hàm tích hợp trong Excel, được phân loại là Hàm Chuỗi / Văn bản . Nó có thể được sử dụng như một hàm bảng tính (WS) trong Excel. Là một hàm trang tính, hàm UPPER 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

Cú pháp của hàm UPPER trong Microsoft Excel là: =UPPER( text ) Trong đó: Text: là chuỗi văn bản trong đó có chữ thường mà bạn muốn biến đổi thành chữ hoa

Chức năng:

Chuyển đổi tất cả các kí tự trong văn bản thành in hoa.

Áp dụng đối với

Excel cho Office 365, Excel 2019, 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 trang tính)

Chúng ta hãy xem một số ví dụ về hàm UPPER của Excel và khám phá cách sử dụng hàm UPPER trong Microsoft Excel:

Ví dụ cách dùng hàm upper và proper trong excel

Dựa trên bảng tính Excel ở trên, các ví dụ UPPER sau sẽ trả về: =UPPER(A1) KẾT QUẢ: “UNICA” =UPPER(A2) KẾT QUẢ: “HỌC CÙNG CHUYÊN GIA

2 Hàm PROPER

Mô tả:

Hàm Microsoft Excel PROPER đặt ký tự đầu tiên trong mỗi từ thành chữ hoa và phần còn lại thành chữ thường. Hàm PROPER là một hàm tích hợp trong Excel được phân loại là Hàm Chuỗi / Văn bản . Nó có thể được sử dụng như một hàm bảng tính (WS) trong Excel. Là một hàm trang tính, hàm PROPER 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

Cú pháp của hàm PROPER trong Microsoft Excel là:=PROPER( text )

Trong đó:

Text: Đối số chuỗi có ký tự đầu tiên trong mỗi từ sẽ được chuyển đổi thành chữ hoa và tất cả các ký tự còn lại được chuyển đổi thành chữ thường.

Chức năng:

Hàm PROPER trả về giá trị chuỗi / văn bản.

Áp dụng đối với

Excel cho Office 365, Excel 2019, 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 trang tính) Hãy xem xét một số ví dụ về hàm PROPER của Excel trong Microsoft Excel:

Ví dụ cách dùng hàm upper và proper trong excel

Hàm OR là một hàm logic để kiểm tra nhiều điều kiện cùng một lúc. OR trả về kết quả TRUE hoặc FALSE.

Giả sử:để kiểm tra A2 cho “x” hoặc “y”, hãy sử dụng = OR (A2 = “x”, A2 = “y”). Ngoài ta OR thường xuyên kết hợp với hàm điều kiện IF.

Nghĩa là nếu A2 có giá trị lớn hơn 1 HOẶC nhỏ hơn 100 thì kết quả trả về là TRUE, nếu không kết quả trả về là FALSE.

Công thức hàm OR

=OR(logical1, [logical2], …)

Trong đó

Logical 1: Là một giá trị logic đầu tiên hoặc là điều kiện bắt buộc cho đánh giá

Logical 2: Là một giá trị logic thứ 2, tùy chọn

Sử dụng hàm OR để kiểm tra nhiều điều kiện cùng một lúc, tổng cộng tối đa 255 điều kiện.

Làm sao sử dụng hàm OR trong Excel?

Ở phần này chúng ta sẽ làm ví dụ hàm OR thông thường và kết hợp với hàm IF

Ví dụ 1: Sử dụng hàm OR trong Excel để kiểm tra

Ta có bảng sau, thể hiện số lượng học viên các tháng và số lượng học viên đã hoàn thành khóa học qua các tháng. Bây giờ hãy sử dụng công thức OR để kiểm tra

Ví dụ minh họa cách sử dụng hàm Or trong Excel

Giải thích như sau:

=OR(A2=”Bói Tarot”, A2=”Tự học Yoga”)

Sẽ trả về: TRUE nếu A2 chứa “Bói Tarot” hoặc “Tự học Yoga”, FALSE nếu không có giá trị nào

Ví dụ minh họa cách sử dụng hàm Or trong Excel Ví dụ minh họa cách sử dụng hàm Or trong Excel

Sẽ trả về: TRUE nếu B2 lớn hơn hoặc bằng 1000 hoặc C2 lớn hơn hoặc bằng 900, FALSE nếu không.

Ví dụ 2: Kết hợp hàm OR và hàm IF để kiểm tra

Kết hợp OR và hàm IF: Ta vẫn sử dụng số liệu ở ví dụ 1

Giải thích như sau:

Công thức trả về ” Kết quả tốt ” nếu một số trong ô B2 lớn hơn 1000 hoặc số trong C2 lớn hơn 900, ” Kết quả không tốt ” nếu không.

– THÀNH THẠO Kế toán thực tế trên phần mềm MISA chỉ sau 24 ngày – Học xong làm ngay

– Thành thạo Excel 2010 trong 3 giờ

– Kế toán thuế của doanh nghiệp

Hướng dẫn tính tổng có điều kiện bằng DSUM

Hàm DSUM là hàm cộng các số trong một trường, cột trong danh sách dữ liệu thỏa mãn với các điều kiện xác định. Cú pháp hàm là DSUM =DSUM(database; field; criteria).

Database là đối số bắt buộc, là cơ sở dữ liệu được tạo từ 1 phạm vi ô. Danh sách dữ liệu này sẽ chứa các dữ liệu là các trường, gồm trường để kiểm tra điều kiện và trường để tính tổng. Danh sách chứa hàng đầu tiên là tiêu đề cột.

Field là đối số bắt buộc chỉ rõ tên cột dùng để tỉnh tổng các số liệu. Có thể nhập tên tiêu đề cột trong dấu ngoặc kép, hoặc dùng 1 số thể hiện vị trí cột trong danh sách không dùng dấu ngoặc kép, hay tham chiếu tới tiêu đề cột muốn tính tổng.

Criteria là đối số bắt buộc, phạm vi ô chứa điều kiện muốn hàm DSUM kiểm tra.

Có thể dùng phạm vi bất kỳ cho criteria nếu phạm vi đó chứa ít nhất 1 nhãn cột và ít nhất 1 ô bên dưới tiêu đề cột mà sẽ xác định điều kiện cho cột đó.

Không đặt phạm vi điều kiện ở phía dưới danh sách vì sẽ không có vị trí thêm các thông tin khác vào danh sách.

1. Tính tổng số tiền đã bán của sản phẩm iPhone

Trước hết chúng ta tạo phạm vi điều kiện cho hàm DSUM để tính tổng số tiền đã bán của các sản phẩm iPhone. Điều kiện cho hàm DSUM là iPhone*.

Tiếp đến người dùng nhập công thức hàm DSUM là =DSUM(A6:E13;”Thành Tiền”;C2:C3).

A6:E13 là phạm vi cơ sở dữ liệu chứa cột cần tính tổng và cột chứa điều kiện cần kiểm tra.

“Thành Tiền” là tiêu đề cột sẽ sử dụng giá trị trong cột đó để tính tổng.

C2:C3 là phạm vi điều kiện chứa tiêu đề cột và 1 giá trị điều kiện.

Nhấn Enter và chúng ta sẽ được kết quả chính xác như hình.

Hoặc người dùng có thể thay giá trị trong Field thành tham chiếu tới cột Thành tiền. Công thức nhập là =DSUM(A6:E13;E6;C2:C3). Kết quả cũng tương tự như khi bạn nhập cột Thành tiền vào công thức.

2. Tính tổng số tiền sản phẩm đã bán có số lượng lớn hơn hoặc bằng 5.

Chúng ta nhập công thức hàm là =DSUM(A6:E13;E6;C2:C3) và nhấn Enter.

Kết quả tổng số tiền sẽ như trong hình.

Nhìn chung cách sử dụng hàm DSUM trên Excel vô cùng đơn giản. Bạn có thể sử dụng hàm để tính tổng các giá trị theo cột với điều kiện đơn giản. Với những điều kiện phức tạp hơn chúng ta có thể sử dụng hàm SUMIF.

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!”)