Cách Dùng Excel Trong Word / Top 9 # Xem Nhiều Nhất & Mới Nhất 3/2023 # Top View | Utly.edu.vn

1️⃣【Cách Sử Dụng Thanh Thước Ruler Trong Word 】™ Excel

Hướng dẫn kích hoạt thước kẻ Ruler trong Word

Đầu tiên, bạn phải xem nó Bố trí in, sau đó chuyển sang thẻ Lượt xem.

Sau đó, chọn hộp cái thước để cho phép căn chỉnh lề.

Ghi chú: Thước ngang bao gồm hai phần tử, Thước ngang có thể được hiển thị ở cả hai chế độ Bố trí Web và Chế độ nháp, nhưng Thước dọc thì không.

Hướng dẫn sử dụng thước căn chỉnh trong Word

Đặt lề trên thước

Lề trên thước được hiển thị bằng màu trắng và xám:

Vùng màu xám ở hai đầu thước là lề mà bạn đã đặt.

Vùng màu trắng là vùng hoạt động nơi văn bản được lưu trữ.

Ghi chú: Đơn vị này sẽ áp dụng cho tất cả các tính năng đo lường khác trong Word offline.

Bạn hoàn toàn có thể căn chỉnh khoảng cách lề trực tiếp trên thước này bằng cách: Di chuột giữa khu vực thước kẻ màu xám và trắng lợi nhuận muốn điều chỉnh, sau đó kéo sang trái hoặc phải (lên hoặc xuống).

Với cài đặt khổ giấy 8,5 x 11 inch mặc định của Word, thước ngang bắt đầu ở 1 (lề 1 inch), sau đó trở về 0 khi hết lề trái và tiếp tục chạy đến 7,5 cho vùng ngang. nghỉ ngơi. Tương tự với thước dọc, bắt đầu từ 1 đối với lề 1 inch, quay trở lại 0 khi lề kết thúc, sau đó chạy đến 10 đối với diện tích dọc còn lại.

Đặt thụt lề trên thước

Trên thước có một bộ gồm 2 hình tam giác và 1 hình chữ nhật, chịu trách nhiệm kiểm soát độ thụt đầu dòng cho các đoạn văn riêng lẻ. Chỉ nhấp chuột text (Bấm Ctrl để chọn nhiều đoạn văn hoặc Ctrl + A để chọn toàn bộ tài liệu), sau đó kéo để chỉnh sửa.

Bộ phận chỉnh lý lớn này có 3 bộ phận nhỏ đảm nhận các công việc sau:

Kéo hình chữ nhật dưới cùng: Sẽ điều chỉnh thụt lề (căn trái) cho cả đoạn văn. Khi kéo mảnh đó thì 2 mảnh kia cũng chuyển động theo.

Kéo hình tam giác ở giữa: Sẽ thụt lề (căn trái) tất cả các dòng trong đoạn ngoại trừ dòng đầu tiên.

Kéo hình tam giác trên cùng: Sẽ thụt lề (căn trái) cho dòng đầu tiên trong đoạn văn.

Đối với màu trắng ở ngoài cùng bên phải, hãy kéo nó để căn phải toàn bộ đoạn văn.

Thêm các điểm dừng tab (Tab Stop)

Theo mặc định, trong tài liệu Word sẽ không có điểm tab, mỗi lần nhấn tab, con trỏ sẽ tiến tới 8 ký tự. Đặt điểm dừng tab cho phép bạn sắp xếp và kiểm soát văn bản tốt hơn. Nhìn ở góc trên cùng bên trái của tài liệu (ngay trên thước dọc) và bạn sẽ thấy nút Dừng tab.

Nhấp vào nút để tùy chỉnh các điểm dừng tab mà bạn sẽ đặt, mỗi lần nhấn là một kiểu Dừng tab khác nhau:

Left (Trái): Văn bản căn trái bắt đầu từ điểm dừng tab được đặt.

Giữa (Giữa): Văn bản được căn giữa xung quanh điểm dừng tab được đặt.

Đúng: Văn bản được căn phải, kết thúc tại điểm dừng tab đã đặt.

Số thập phân: Sắp xếp các số thập phân với nhau, rất phù hợp nếu bạn cần thống kê nhiều số.

Thanh tab: Kiểu tab này không tạo ra một điểm dừng tab thực sự, nhưng thêm một dòng nơi đặt nó. Có thể dùng để chia cột theo chiều dọc trong những trường hợp không cần bảng.

Thiết lập trang truy cập

Nhấn đúp chuột Nhấp vào bất kỳ vùng trống nào (màu xám) trên thước để mở cửa sổ Thiết lập trangBạn cũng có thể mở Thiết lập trang từ thẻ Bố trí trên Ribbon.

Cửa sổ Thiết lập trang xuất hiện, trên thẻ Lợi nhuận bạn có thể thiết lập bố cục cho trang văn bản:

Lợi nhuận: Cho phép điều chỉnh lề trên, dưới, trái và phải.

Mương nước: Đây là khoảng cách cho liên kết tài liệu, để trống theo mặc định.

Sự định hướng: Chọn hướng giấy dọc hoặc ngang.

Thẻ bên trái Giấy cho phép điều chỉnh kích thước và khổ giấy phù hợp. Bộ nhiều nhất Khổ giấy Mặc định (khổ giấy) trong Word là Lá thư (đối với giấy in của Mỹ) với kích thước 215,9 x 279,4mm.

Mẹo sử dụng thước kẻ Ruler trong Word

Khi bạn đang duyệt qua các điểm dừng tab mà không biết nghĩa là gì, bạn chỉ cần rê chuột ra ngoài và nhập lại để hộp thoại mô tả xuất hiện.

Để chèn Tab Stop, chỉ cần nhấp vào vị trí bạn muốn đặt trên vùng màu trắng của thước ngang. Biểu tượng Tab Stop sẽ xuất hiện, cho bạn biết rằng mỗi lần bạn nhấn phím Tab, con trỏ sẽ chạy đến nơi gần nhất có biểu tượng đó.

Bạn có thể thêm các Tab Stop khác nhau và kéo để di chuyển chúng. Bạn thậm chí có thể nhấp đúp vào bất kỳ Tab Stop nào để thiết lập chúng theo ý muốn.

Để xóa một Điểm dừng tab, hãy kéo và thả nó khỏi thước.

Cách Dùng Hàm Len Trong Excel

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

Hàm LEN là gì?

Trong tất cả các hàm Excel có lẽ hàm LEN được coi là hàm dễ và đơn giản nhất. Tên hàm cũng rất dễ nhớ, không có gì ngoài ba ký tự đầu của từ “length”. Và đó cũng chính là tác dụng thật sự của hàm LEN trả về chiều dài của chuỗi ký tự, hay chiều dài của một ô.

Nói cách khác, bạn có thể sử dụng hàm LEN trong Excel để đếm tất cả ký tự trong một ô, bao gồm chữ cái, chữ số, ký tự đặc biệt, và tất cả khoảng trống (trắng).

Tác dụng của hàm LEN trong Excel

Hàm LEN trong Excel đếm tất cả ký tự trong một ô, rồi trả về chiều dài chuỗi.

Cú pháp hàm LEN

=LEN(text)

Text chính là chuỗi ký tự nơi mà bạn muốn đếm số ký tự hoặc ô trong Excel

Bạn sẽ tìm thấy một vài công thức đơn giản bên dưới nhằm giúp bạn hiểu sơ qua công dụng của hàm LEN trong Excel.

=LEN(135) – trả về 3, bởi vì 3 chữ số được nhập vào câu lệnh text.

=LEN(“NAMXUYEN”) – trả về 8 bởi vì từ NAMXUYEN có 8 chữ cái. Giống như bất cứ công thức Excel nào khác, hàm LEN bắt buộc bạn phải đặt chuỗi ký tự bên trong dấu ngoặc kép, và đương nhiên rồi dấu ngoặc kép này sẽ không được đếm.

Khi sử dụng công thức LEN, bạn có thể cần phải nhập tham chiếu ô thay vì chữ số hay chuỗi ký tự , để đếm số ký tự trong một ô cụ thể hay một dải ô.

Ví dụ

Để tính được chiều dài chuỗi ký tự trong ô A5, bạn sẽ dùng công thức này:

=LEN(A5)

Cách đếm tất cả các ký tự trong một ô ( bao gồm cả khoảng trắng)

Như đã nói lúc trước, hàm LEN trong Excel đếm tất cả ký tự trong một ô cụ thể một cách tuyệt đối, bao gồm tất cả khoảng trống hay thụt đầu dòng, thụt cuối dòng, và cả khoảng trống giữa các từ.

Ví dụ, để tính chiều dài của ô A3, bạn sử dụng công thức này:

=LEN(A3)

Đếm số ký tự trong nhiều ô

Để đếm số ký tự trong nhiều ô, hãy chọn ô có công thức LEN rồi sao chép sang ô khác, ví dụ bằng cách kéo ô vuông nằm dưới góc bên phải .

Ngay khi sao chép công thức, hàm LEN sẽ trả về số ký tự đếm được trong mỗi ô.

Và một lần nữa, hãy lưu ý rằng hàm LEN đếm tất cả mọi thứ một cách tuyệt đối bao gồm chữ cái, chữ số, khoảng trống, dấu phẩy, dấu ngoặc kép, dấu ngoặc đơn, và còn nhiều nữa:

Lưu ý. Khi sao chép một công thức xuống các cột khác, hãy chắc rằng bạn sử dụng tham chiếu ô tương đối chẳng hạn như LEN(A3), hay một tham chiếu hỗn hợp như LEN($A3) chỉ dùng cố định cho cột, nên công thức LEN của bạn sẽ điều chỉnh một cách thích hợp với vị trí mới.

Đếm tất cả ký tự trong nhiều ô

Cách rõ ràng nhất để đếm tất cả ký tự trong vài ô là sử dụng nhiều hàm LEN.

Ví dụ

Hoặc có thể sử dụng hàm SUM để tính tổng số ký tự được trả về bởi hàm LEN:

=LEN(A2)+LEN(A3)+LEN(A4)

Cách nào cũng được, công thức đều đếm số ký tự trong mỗi ô xác định rồi trả về tổng chiều dài chuỗi:

=SUM(LEN(A2),LEN(A3),LEN(A4))

Cách đếm số ký tự không tính khoảng trống dầu dòng và cuối dòng

Khi thao tác với bảng tính lớn thì vấn đề phổ biến chính là khoảng cách đầu dòng và cuối dòng, ví dụ: khoảng trống ở đầu hay ở cuối ký tự. Thong thường chúng ta hầu như không thấy chúng trên bảng tính, nhưng sau khi bạn gặp chúng một vài lần, thì bạn sẽ dễ nhận thấy chúng hơn.

Nếu bạn nghi ngờ có vài khoảng trống vô hình trong các ô thì hàm LEN trong Excel sẽ giúp ích rất nhiều. Hy vọng bạn còn nhớ, nó đếm tất cả khoảng trống trong phép đếm ký tự:

Để tính được chiều dài chuỗi mà không đếm khoảng trống đầu dòng và cuối dòng, hãy nhớ lồng ghép hàm TRIM vào hàm LEN trong Excel:

Cách đếm số ký tự trong một ô không tính các khoảng trống

=LEN(TRIM(A5))

Nếu mục đích của bạn chỉ là đếm số ký tự mà không tính các khoảng trống cho dù đó là khoảng trống đầu dòng, cuối dòng hay giữa các từ, thì bạn sẽ cần một công thức phức tạp hơn:

Chúng ta sử dụng hàm SUBSTITUTE thay thế ký tự này với ký tự khác. Trong công thức trên, bạn thay thế khoảng trống (” “) bằng một ký tự rỗng, ví dụ một chuỗi ký tự rỗng (“”). Và bởi vì bạn lồng ghép hàm SUBSTITUTE vào hàm LEN nên phép thế thật sự không được thực hiện trong các ô, mà nó chỉ hướng dẫn công thức LEN tính chiều dài chuỗi mà không tính các khoảng trống.

=LEN(SUBSTITUTE(A5,” “,””))

Cách đếm số ký tự trước hay sau một ký tự xác định

Thông thường, bạn cần phải biết chiều dài của một phần nhất định của chuỗi ký tự, thay vì đếm tổng số ký tự trong một ô.

Giả sử, bạn có một danh sách mã hàng hóa như sau:

Các mã hàng được ngăn cách bởi dấu “-” và tất cả mã hàng hóa hợp lệ đều có chính xác 5 ký tự trong nhóm đầu tiên. Vậy làm thế nào để chúng ta chỉ ra được mã hàng hóa nào là không hợp lệ? Cách giải quyết đơn giản là đếm số ký tự trong nhóm đầu tiên

Công thức áp dụng cho bài toán trên có dạng như sau:

Và bây giờ, hãy chia nhỏ công thức ra để bạn có thể hiểu quy luật của nó:

=LEN(LEFT($A2, SEARCH(“-“, $A2) -1))

Bạn sử dụng hàm SEARCH để trả về vị trí của dấu gạch ngang đầu tiên (“-“) trong ô A2:

Sau đó, bạn sử dụng hàm LEFT để trả về các ký tự phía bên trái của chuỗi ký tự, rồi lấy kết quả trừ 1 bởi vì bạn không muốn tính luôn dấu gạch ngang:

SEARCH(“-“, $A2)

LEFT($A2, SEARCH(“-“, $A2) -1)

Và cuối cùng, bạn có hàm LEN để trả về chiều dài của chuỗi đó.

Hay, bạn có thể xác định mã hàng hóa không hợp lệ bằng cách lồng ghép công thức LEN ở trên vào hàm IF:

Ví dụ, trong một danh sách tên, có thể bạn muốn biết họ của một người có bao nhiêu ký tự. Công thức LEN sau đây sẽ giải quyết việc này:

=LEN(RIGHT(A2, LEN(A2) -SEARCH(” “,A2)))

Công thức hoạt động như thế nào:

Xin hãy lưu ý rằng, để công thức hoạt động một cách chính xác, mỗi ô nên có chỉ một khoảng trống, ví dụ chỉ có tên và họ, không có tên lót, danh hiệu hay hậu tố.

Đầu tiên, bạn xác định vị trí của khoảng trống ” ” trong chuỗi ký tự bằng cách sử dụng hàm SEARCH: SEARCH(” “,A2)))

Sau đó, bạn đếm có bao nhiêu ký tự phía sau khoảng trống. Về việc này, bạn lấy tổng chiều dài chuỗi trừ đi vị trí khoảng trống:

LEN(A2) -SEARCH(” “, A2)))

Sau đó, bạn sử dụng hàm RIGHT để trả về tất cả ký tự phía sau khoảng trống.

Và cuối cùng, bạn sử dụng hàm LEN để tính chiều dài chuỗi được trả về bởi hàm RIGHT.

Hàm Or Và Cách Dùng Hàm Or 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

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