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

Hướng Dẫn Sử Dụng Excel Autofill Trong Excel

Lượt Xem:4555

Hướng dẫn sử dụng EXCEL AUTOFILL trong excel

Tính năng Tự động điền Excel ( EXCEL AUTOFILL ) có thể được sử dụng để điền vào một dải ô có giá trị lặp lại, một chuỗi giá trị hoặc chỉ một định dạng ô.

Để sử dụng tính năng Tự động điền Excel đơn giản:

Hộp thoại tùy chọn xử lý điền ô Excel và hộp EXCEL AUTOFILL

Nhập một giá trị vào ô bắt đầu;

Sử dụng chuột để kéo ‘ fill handle‘ (hình vuông nhỏ màu đen ở góc dưới cùng bên phải của ô bắt đầu) trên phạm vi ô được lấp đầy;

Khi bạn kéo ‘fill handle’ trên dãy ô cần điền, Excel sẽ điền vào các ô đã chọn, bằng cách lặp lại giá trị trong ô đầu tiên hoặc bằng cách chèn một chuỗi từ giá trị ô đầu tiên (ví dụ 1, 2, 3 , …);

Nhấp vào hộp ‘ Tuỳ chọn EXCEL AUTOFILL‘, sẽ xuất hiện ở cuối phạm vi ô đã chọn của bạn. Điều này sẽ cung cấp cho bạn các tùy chọn khác nhau sau:

Sao chép ô – sao chép ô ban đầu trên phạm vi đã chọn;

Fill Series – điền vào dãy được chọn với một loạt các giá trị (thường tăng thêm 1), bắt đầu với giá trị ô ban đầu;

Chỉ định dạng điền – điền vào dải ô đã chọn với định dạng, chứ không phải các giá trị của ô ban đầu;

Điền Không có Định dạng – điền vào phạm vi đã chọn với các giá trị, nhưng không sao chép định dạng từ ô ban đầu.

Chọn tùy chọn mà bạn yêu cầu cho các ô được điền.

Tự động điền sử dụng nhiều giá trị di động bắt đầu

Nếu bạn muốn Excel nhận ra một chuỗi không phải là một gia số đơn giản bằng 1, điều này có thể được thực hiện bằng cách nhập hai giá trị đầu tiên của chuỗi của bạn vào ô đầu tiên và thứ hai của một dải ô. Chọn cả hai ô này và một lần nữa, kéo thanh điều khiển điền vào trong phạm vi cần điền. Excel sẽ tự động nhận dạng mẫu từ hai ô ban đầu và tiếp tục điều này qua phạm vi đã chọn. Sử dụng phương pháp này, bạn có thể lấy Excel để điền vào ô theo gia số hoặc số lượng của bất kỳ số nào (ví dụ 2, 4, 6, 8, …).

Ngoài ra, nếu bạn muốn Excel điền vào các ô có giá trị xen kẽ lặp lại ( ví dụ: 1, 2, 1, 2, 1, 2, ...), bạn có thể bắt đầu mẫu trong hai (hoặc nhiều) ô đầu tiên, sau đó, với các ô đầu tiên được tô sáng, kéo thanh công cụ fill và sau đó nhấp vào hộp ”. Trong hộp này, chọn tùy chọn ‘ Sao chép ô‘ để lặp lại các giá trị ô ban đầu trong phạm vi đã chọn.

Ngày tự động điền Excel

Vì ngày tháng và thời gian được lưu trữ trong Excel dưới dạng số, chúng cũng có thể được sử dụng với Excel Autofill.

Theo mặc định, nếu bạn chỉ cần nhập một ngày hoặc thời gian và kéo xử lý điền, ngày và thời gian sẽ hoàn thành trong một chuỗi, bằng cách thêm một ngày (cho ngày) hoặc một giờ (đối với thời gian). Tuy nhiên, cũng như với các số đơn giản, bạn có tùy chọn nhấp vào hộp ‘T uỳ chọn Tự động Điền‘, để chọn một loại tự động điền khác.

Thời gian có bốn tùy chọn EXCEL AUTOFILL tương tự như được hiển thị ở trên, đối với các số đơn giản (ví dụ: Sao chép Ô, Điền Dòng, Chỉ Định dạng Đầy, Điền Không có Định dạng). Tuy nhiên, đối với ngày tháng, có các tùy chọn Tự động điền bổ sung. Cũng như bốn tùy chọn cho số đơn giản và thời gian, cũng có những điều sau đây:

Fill Days – Tìm một mẫu trong ngày khi điền các ô đã chọn;

Điền vào các ngày trong tuần – Tìm một mẫu trong ngày khi điền các ô đã chọn, nhưng không bao gồm thứ Bảy hoặc Chủ Nhật trong chuỗi;

Điền các tháng – Tìm một mẫu trong tháng khi điền các ô đã chọn;

Fill Years – Tìm một mẫu trong năm khi điền các ô đã chọn.

Tự động điền Giá trị Văn bản

Tự động điền Excel thường sẽ điền vào một cột có giá trị văn bản bằng cách lặp lại (các) giá trị trong (các) ô đầu tiên. Tuy nhiên, có một số giá trị văn bản Excel nhận ra là một phần của chuỗi. Đó là:

Các ngày trong tuần (viết tắt hoặc tên đầy đủ)

Tháng (tên viết tắt hoặc tên đầy đủ)

Chức năng tự động điền và công thức

Tính năng EXCEL AUTOFILL cũng hoạt động với các hàm và công thức trong Excel. Tuy nhiên, với loại Tự động điền này, không có tùy chọn ‘điền chuỗi’. Thay vào đó, Excel quan sát các quy tắc của Tham chiếu tuyệt đối và tương đối (tức là nếu tham chiếu hàng hoặc cột được bắt đầu bằng dấu $, excel sẽ giữ nguyên tham chiếu khi công thức được sao chép sang các ô khác; được điều chỉnh khi công thức được sao chép sang các ô khác.

Tự động điền ngang và dọc

Tự động điền Excel qua một hàng

Cũng như làm việc xuống một cột, tính năng Tự động điền cũng hoạt động theo chiều ngang, trên các hàng.

Chỉ cần kéo tay cầm điền vào các ô mà bạn muốn điền.

Tự động điền nhiều hàng hoặc cột đồng thời

Nhấp đúp vào Fill Handle

Đối với tốc độ, bạn có thể Tự động điền cột bằng cách nhấp đúp vào nút điều khiển điền của ô được đánh dấu (hoặc phạm vi ô). Nếu các ô bên dưới hoặc liền kề với ô được đánh dấu (hoặc dải ô) chứa giá trị, nhấp đúp vào nút điều khiển điền sẽ làm cho Tự động điền điền vào cột hiện tại cho đến khi nó đến cuối dải dữ liệu hiện tại.

⇒ Công Ty TNHH Dịch Vụ Thương Mại Cốp Pha Hùng Cường ⇒ Công Ty TNHH Thương Mại Dịch Vụ Thực Phẩm Sạch Family Food ⇒ Công Ty TNHH Xd – Tm – Dv Minh Thanh

Tags:

Các bài viết mới

Các tin cũ hơn

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

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.

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 Dùng Hàm Value Trong Excel 2022, 2013, 2010, 2007

Hàm VALUE trong Excel là hàm giúp chuyển đổi một chuỗi văn bản đại diện cho một số thành một số. Về cơ bản thì hàm VALUE này ít được dùng độc lập vì thông thường trong công thức Excel sẽ tự động chuyển đổi văn bản thành số khi cần thiết. Nhưng không phải vì thế mà hàm VALUE này sinh ra là thừa, hàm này thường sẽ mang lại hiệu quả cao khi kết hợp với các hàm Excel khác.

Hướng dẫn cách dùng hàm VALUE trong Excel

Hàm VALUE là một trong số những hàm Excel cơ bản nên có thể sử dụng được trong hầu hết các phiên bản Excel như Excel 2016, Excel 2013, Excel 2010, Excel 2007 và cả Excel cho MAC, Iphone, Android. Ở đây mình sẽ làm trên Excel 2016, nếu bạn đang dùng phiên bản Excel khác thì cũng làm tương tự vì đều cùng 1 cấu trúc cú pháp nên sẽ không có gì khác biệt.

Cú pháp của hàm VALUE

Giá trị trong hàm VALUE

Text: Là chuỗi văn bản được đặt trong dấu ngoặc kép hoặc 1 tham chiếu đến 1 ô có chứa văn bản bạn muốn chuyển đổi. Đây là giá trị bắt buộc.

Lưu ý

Giá trị text có thể là định dạng ngày, thời gian hoặc số hằng định bất kỳ, miễn là được Excel công nhận. Nếu giá trị text không phải là 1 trong các định dạng đó thì hàm VALUE trả về giá trị lỗi #VALUE!

Như bạn thấy trong hình trên, các văn bản đại diện cho 1 số bất kỳ ở cột B sau khi áp dụng hàm VALUE sẽ cho ra kết quả dạng số ở cột D. Về cơ bản hàm VALUE rất dễ sử dụng nên bạn nhìn qua ví dụ trên sẽ hiểu.

Kết hợp hàm VALUE với hàm RIGHT

Trong ví dụ trên bạn sẽ thấy:

– Khi dùng hàm RIGHT nó sẽ cắt 2 ký tự bên tay phải của chuỗi ký tự đã cho và được kết quả là 05 ở dạng text.

– Khi kết hợp hàm VALUE với hàm RIGHT thì chúng ta sẽ thấy, hàm VALUE sẽ chuyển chuỗi ký tự text đại diện cho 1 số đó là 05 thành số 5.

Kết hợp hàm VALUE với hàm RIGHT và hàm IF

Sử dụng lại ví dụ ở trên. Chúng ta sẽ dùng thêm hàm IF để kiểm tra lại kết quả ở ví dụ trên.

– Dùng hàm IF để kiểm tra kết quả của hàm RIGHT: Vì hàm RIGHT trả về giá trị 05 là dạng text nên hàm IF trả về giá trị FALSE (không bằng số 05).

– Dùng hàm IF kiểm tra kết quả hàm VALUE: Hàm VALUE trả về kết quả là 5 ở dạng số nên hàm IF trả về giá trị TRUE (số 05 bằng với số 5).

Đó là các ví dụ khi kết hợp hàm VALUE với các hàm khác. Trong thực tế khi sử dụng bạn sẽ còn kết hợp hàm VALUE với nhiều hàm khác nữa để tăng tính hiệu quả trong công việc.