Top 8 # Xem Nhiều Nhất Hướng Dẫn Cách Dùng Hàm If Mới Nhất 3/2023 # Top Like

Trước hết, bạn cần hiểu rõ hai hàm cơ bản này trước khi kết hợp chúng với nhau.

Hàm Điều kiện If

Hàm điều kiện IF được hiểu như mệnh đề Nếu… thì…

Công thức: =IF(logical_test,”Value_IF_TRUE”,”Value_IF_FALSE”)

logical_test là điều kiện xét. Nếu thỏa điều kiện thì hàm sẽ trả về giá trị đúng là Value_IF_TRUE, nếu không thỏa điều kiện thì hàm trả về giá trị sai tức là Value_IF_FALSE.

Hàm tìm kiếm Vlookup

VLOOKUP trong Excel được hiểu là một hàm mà nó cho phép bạn có thể tìm (tra cứu) một giá trị từ một cột dữ liệu và sau đó trả về giá trị thích hợp hoặc giá trị tương ứng từ một cột khác. Hàm VLOOKUP được tích hợp sẵn trong Excel 2013, Excel 2010, Excel 2007, Excel 2007, Excel XP và Excel 2000.

Công thức: =VLOOKUP (lookup_value, Table_array, Col_index_num, Range_lookup).

Giải thích ý nghĩa

Lookup_value: giá trị tìm kiếm.

Table_array: Bảng tham chiếu.

Col_index_num: cột lấy giá trị trả về trong bảng tham chiếu.

Range_lookup: Đây là phương pháp dò tìm

Quy ước “0”: Dò tìm tuyệt đối

Quy ước “1”: Dò tìm tương đối

Công thức hàm kết hợp

Hàm IF là 1 hàm thông dụng trong Excel. Chúng ta có thể kết hợp hàm VLOOKUP với hàm IF theo cách sau đây:

IF(điều kiện 1, VLOOKUP 1(lookup_value, Table_array, Col_index_num, Range_lookup), VLOOKUP 2(lookup_value, Table_array, Col_index_num, Range_lookup))

Bạn có thể hiểu công thức này như sau: Xét điều kiện, nếu điều kiện đúng, thực hiện lệnh Vlookup 1, nếu điều kiện sai sẽ thực hiện lệnh vlookup 2.

Ta có công thức ở ô phân loại =IF(B2=”Mỹ”, VLOOKUP(C2,$A$15:$C$20,3,TRUE), VLOOKUP(C2,$B$15:$C$20,2,TRUE))

Ở công thức này ta có thể hiểu cách tính của hàm sẽ là: nếu bệnh nhân là người châu Mỹ thì sẽ thực hiện tìm kiếm ở bảng A15:C20, trả kết quả ở cột 3 là cột phân loại (bảng tham chiếu gồm 3 cột).

Còn nếu bệnh nhân không phải là người châu Mỹ (trường hợp ngược lại sẽ cho châu Á) thì dò tìm ở bảng B15:C20, đối chiếu trả kết quả ở cột 2 là cột phân loại (bảng tham chiếu gồm 2 cột).

Hoặc ta có công thức khác như sau:

= VLOOKUP(C2, IF(B2=”mỹ”,$A$15:$C$20,$B$15:$C$20), IF(B2=”mỹ”,3,2),TRUE)

Bạn có sẽ gặp phải các lỗi sau

Xuất hiện báo lỗi này sau khi sao chép toàn bộ các ô, có hai trường hợp xảy ra:

– Tất cả các ô đều xuất hiện lỗi này: Bạn nên kiểm tra Giá trị và thông tin ở cột đầu tiên của bảng tham chiếu.

– Chỉ một vài ô có lỗi #N/A: Bạn kiểm tra dữ liệu cột Giá trị.

– Ô đầu tiên hàm cho kết quả đúng, nhưng sao chép công thức bị báo lỗi.

Ta khắc phục bằng cách cố định Bảng tham chiếu ( nhấn F4 khi nhập bảng tham chiếu trong công thức).

Trong công thức này, hàm cũng cho kết quả tương tự. Khác ở chỗ hàm này là hàm kết hợp, lồng hàm IF vào trong hàm VLOOKUP.

Tài liệu kèm theo bài viết

 Hàm INDEX là hàm trả về giá trị hoặc tham chiếu của 01 ô trong Excel là giao nhau giữa dòng và cột được xác định bởi các thông số cho trước. Hàm này cũng được coi là một hàm tìm kiếm với khả năng vận dụng và kết hợp cùng các hàm khác khá mạnh mẽ, có thể áp dụng để giải quyết nhiều hơn các vấn đề phức tạp.

Đăng ký ngay khoá Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ

Cú pháp của hàm Index trong Excel

Hàm INDEX có 2 dạng:

Một là Hàm INDEX Dạng Mảng: Kết quả của hàm INDEX sẽ trả về giá trị của một ô (Khi bạn đã cho các giá trị tham chiếu kèm theo)

Hai là Hàm INDEX Dạng Tham Chiếu: Kết quả sẽ trả về một dải ô tham chiếu, được quy định bởi [area_num]

Cách viết hàm INDEX dạng mảng trong Excel

Cú Pháp: (Array,Row_num,[Column_num])

Trong đó:

– Array: bắt buộc Phạm vi ô hoặc một hằng số mảng.

– Row_num: Thứ tự dòng cần tìm (tính từ dòng đầu tiên của Array đến dòng chứa giá trị cần tìm kiếm).

– Column_num: Thứ tự cột cần tìm (tính từ cột của Array đến cột chứa giá trị cần tìm kiếm).

Phải có ít nhất một trong hai đối số Row_num, Column_num. Nếu đối số nào để trống sẽ được hiểu là chọn vị trí cột đầu hoặc hàng đầu.

Ví dụ 1: Cho danh sách nhóm học sinh,tìm tên học sinh biết tên học sinh đó ở hàng 2 cột 2.

Ô C9 Công thức: =INDEX(B4:C7,2,2)

Cú Pháp: INDEX(Reference,Row_num,[Column_num],[Area_num])

Trong đó:

– Reference: bắt buộc Vùng tham chiếu,.

– Row_num: Thứ tự dòng cần tìm (tính từ dòng đầu tiên của Array đến dòng chứa giá trị cần tìm kiếm).

– Column_num: Thứ tự cột cần tìm (tính từ cột của Array đến cột chứa giá trị cần tìm kiếm).

– Area_num: tùy chọn Số của vùng ô sẽ trả về giá trị trong reference.

Nếu Area_num được bỏ trống thì hàm INDEX dùng vùng 1.

Tiếp ví dụ 1:

Ô C9 Công thức: =INDEX(B4:C7,2,1,1)

KẾT HỢP HÀM INDEX VÀ HÀM MATCH TRONG EXCEL

Hãy tìm hiểu hàm Index kết hợp Match với yêu cầu sau:

Tìm doanh số của cửa hàng D

Tìm tên cửa hàng khi biết trước doanh số

Để giải quyết yêu cầu này sử dụng Cú pháp hàm INDEX kết hợp hàm MATCH sau:

=INDEX($B$2:$C$7,MATCH(C9,$B$2:$B$7,0),2)

Trong đó:

– $B$2:$C$7: Array/ vùng dữ liệu

– MATCH(C9,$B$2:$B$7,0): Kết quả của hàm MATCH sẽ trả về số thứ tự dòng của cửa hàng D trong Array

– 2: Là cột thứ 2 tính từ cột đầu tiên mà cột đó chứa giá trị tìm kiếm = Doanh số

Kết quả ta nhận được như sau:

Khi thay đổi giá trị trong ô C9, kết quả trong ô C10 sẽ tự động đổi theo đó bạn.

Hoặc thay đổi tương ứng doanh thu bạn sẽ thấy kết quả thay đổi theo.

YÊU CẦU SỐ 2: TÌM TÊN CỬA HÀNG KHI BIẾT TRƯỚC DOANH SỐ

Tìm vị trí của Doanh số biết trước số bằng hàm MATCH trong cột doanh

= MATCH(C12,$C$2:$C$7,0)

Tìm trong cột tên cửa hàng: cửa hàng đang có doanh số tương ứng

$B$2:$B$7: Array/ Vùng chứa Tên cửa hàng cần tìm.

Kết hợp lại:

=INDEX($B$2:$B$7,MATCH(C12,$C$2:$C$7,0))

Cụ thể cách sử dụng hàm trong hình sau:

KẾT HỢP HÀM INDEX CÙNG HÀM SUM

Bài toán cũ, nhưng đổi yêu cầu:

Tính doanh số cột C:

=SUM(C2:C7)

=SUM(INDEX(A2:C7,,3))

Tính doanh số 3 cửa hàng trên cùng

=SUM(C2:C4)

=SUM(C2:INDEX(A2:C7,3,3))

=SUM(INDEX(A2:C7,1,3):INDEX(A2:C7,3,3))

KẾT HỢP HÀM INDEX CÙNG HÀM MIN/ MAX

Bài toán cũ, nhưng đổi yêu cầu khi kết hợp MIN/ MAX

Tìm cửa hàng doanh số thấp nhất:

=INDEX(B2:B7,MATCH(MIN(C2:C7),C2:C7,0))

Tìm cửa hàng doanh số cao nhất:

=INDEX(B2:B7,MATCH(MAX(C2:C7),C2:C7,0))

Tổng kết

Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ

Hướng dẫn cách sử dụng hàm COUNTIFS trong Excel

Hàm COUNTIFS là hàm cơ bản nên bạn có thể sử dụng nó 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. Trong bài viết này mình sẽ sử dụng Excel 2016 để làm ví dụ, nếu bạn đang dùng phiên bản Excel khác thì cũng làm tương tự.

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

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Các giá trị trong hàm COUNTIFS

criteria_range1: Vùng chọn đầu tiên cần thống kê. Đây là giá trị bắt buộc

criteria1: Điều kiện áp dụng cho vùng chọn criteria_range1. Giá trị này có thể là một số, tham chiếu ô, biểu thức hoặc văn bản. Đây là giá trị bắt buộc.

[criteria_range2, criteria2]: Các cặp vùng chọn và điều kiện bổ sung. Hàm COUNTIFS cho phép tối đa 127 cặp vùng chọn và điều kiện. Đây là giá trị tùy chọn.

Lưu ý khi sử dụng hàm COUNTIFS

Mỗi vùng chọn bổ sung (criteria_range2, criteria_range3,…) phải có cùng số hàng và cột với vùng chọn 1 (criteria_range1) và các vùng chọn không nhất thiết phải liền kề nhau.

Điều kiện áp dụng của mỗi vùng chọn sẽ được áp dụng cho một ô mỗi lần.

Nếu điều kiện của vùng chọn tham chiếu đến 1 ô trống thì hàm COUNTIFS coi ô trống là giá trị 0.

Trong hàm COUNTIFS, bạn có thể dùng các ký tự đại diện như dấu hỏi (?) thay cho bất kỳ ký tự đơn nào và dấu sao (*) thay cho bất kỳ chuỗi ký tự nào trong điều kiện. Khi bạn cần tìm 1 dấu chấm hỏi (?) hay dấu sao (*) thực thì hãy gõ dấu ngã (~) ở trước ký tự đó.

Để thực hành các ví dụ về hàm COUNTIFS chúng ta sẽ thực hành với bảng số liệu như sau

Ví dụ 1: Cách dùng hàm COUNTIFS với nhiều điều kiện

Để sử dụng công thức hàm COUNTIFS với nhiều điều kiện thì yêu cầu đặt ra là: Thống kê số lượng nhà cung cấp không bán được sản phẩm Samsung A5 nào.

Để giải bài toán này chúng ta sẽ có 2 điều kiện:

Điều kiện 1: Tìm số lượng nhà cung cấp bán sản phẩm Samsung A5

Dựa vào bảng đã cho thì ta sẽ có vùng chọn là C4:C13 và điều kiện là ” Samsung A5 “

Điều kiện 2: Tìm số lượng nhà cung cấp không bán được sản phẩm nào. Tức là số lượng bán bằng 0

Dựa vào bảng ta có vùng chọn là F4:F13 và điều kiện là ” 0 “

Dựa vào công thức hàm COUNTIFS và những phân tích ở trên ta sẽ có công thức tính như sau:

=COUNTIFS(C4:C13,”Samsung A5″,F4:F13,”0″)

Kết quả thống kê bằng 1. Bạn hoàn toàn có thể đếm bằng mắt thường để kiểm tra lại kết quả.

Ví dụ 2: Kết hợp hàm SUM, hàm COUNTIFS với hằng mảng

Trong nhiều trường hợp bạn sẽ phải kết hợp nhiều tiêu chí, khi đó công thức của bạn sẽ rất dài. Để công thức ngắn gọn hơn bạn hãy liệt kê tất cả các tiêu chí trong mảng, sau đó đưa mảng đó vào điều kiện của hàm COUNTIFS. Và để đếm tổng thì chỉ việc kết hợp hàm COUNTIFS với hàm SUM với công thức như sau:

=SUM(COUNTIFS(criteria_range,{“criteria1″,”criteria2″,”criteria3”,…}))

Áp dụng vào trong bảng tính ở trên, chúng ta sẽ đếm tổng số các sản phẩm không bán được và chưa thống kê được với công thức như sau:

=SUM(COUNTIFS(F4:F13,{“0″,”~?”}))

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

Ví dụ 3: Cách dùng hàm COUNTIFS với các ký tự đại diện

Như đã nói trong phần lý thuyết ở đầu bài, trong bảng tính Excel sẽ có lúc có những ô chứ ký tự đơn hoặc chuỗi ký tự. Và để thống kê các ô như vậy với hàm COUNTIFS bạn sẽ sử dụng các ký tự đại diện để đếm.

Dấu hỏi chấm (?) – đại diện cho bất kỳ ký tự đơn nào, sử dụng nó để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.

Dấu sao (*) – đại diện cho bất kỳ chuỗi ký tự nào, bạn dùng nó để đếm ô chứa một từ cụ thể hoặc một ký tự trong ô.

Nếu muốn đếm các ô có chứa dấu hỏi chấm (?) hoặc dấu sao (*) thì bạn chỉ việc thêm dấu ngã (~) ở trước dấu hỏi chấm hoặc dấu sao.

Ví dụ chúng ta có bảng với các ký tự, chuỗi ký tự và số như hình dưới. Dựa vào đó chúng ta sẽ thống kê số lượng ô chứa ký tự đơn, ô chứa chuỗi ký tự và tìm dấu hỏi chấm, dấu sao thực.

Download bài tập về hàm COUNTIFS trong Excel

Tạo danh sách không có dòng trống thừa ở cuối danh sách

Ví dụ bạn có 1 danh sách nhân viên, bạn dự tính danh sách đó sẽ gồm tối đa là 20 người. Thế nhưng tại thời điểm tháng 1, bạn mới chỉ có 5 người trong danh sách, còn lại 15 dòng trống. Và trong những tháng tiếp theo thì số người có thể tăng thêm, giảm đi.

Vậy làm cách nào để danh sách nhân viên đó tự động thay đổi theo số nhân viên hiện có, và những dòng trống sẽ bị loại ra khỏi danh sách?

Hình 1: danh sách nhân viên khi có 5 người Hình 2: danh sách tự động mở rộng khi có thêm 2 người

Để danh sách có thể tự thay đổi được, chúng ta phải sử dụng hàm OFFSET như sau:

Tìm điểm bắt đầu

Danh sách bắt đầu từ ô B3, vì vậy trong hàm Offset chúng ta có thể:

Bắt đầu với tham chiếu B3, không thay đổi về dòng, cột

Bắt đầu với tham chiếu B2, xuống 1 dòng, không đổi về cột

Bắt đầu với tham chiếu A2, xuống 1 dòng, sang phải 1 cột

Việc chọn vị trí bắt đầu chỉ đơn giản là căn cứ để chúng ta tìm được điểm đầu của danh sách. Tùy theo vị trí tham chiếu mà chúng ta có thực hiện di chuyển số dòng, số cột không. Nếu có thì di chuyển bao nhiêu dòng, bao nhiêu cột.

Ví dụ công thức bắt đầu với ô B3

=OFFSET(B3,0,0,độ rộng theo sốdòng, độ rộng theo số cột)

Xác định độ rộng của danh sách

Tiếp đến là xác định danh sách đó có bao nhiêu ô. Để biết điều này, chúng ta dùng hàm COUNTA. Hàm COUNTA có tác dụng đếm những ô có chứa nội dung (không phải ô trống)

Chúng ta đếm trong cột B, từ dòng 3 tới dòng 22 (tương ứng với tối đa 20 nhân viên trong danh sách):

COUNTA(B3:B20)

Với 5 nhân viên, kết quả hàm COUNTA = 5

Với 7 nhân viên, kết quả hàm COUNTA=7

Khi đặt giá trị này vào tham số Weight của hàm OFFSET, chúng ta sẽ có độ rộng của danh sách là 5 hay 7 ô, tính từ ô B3.

=OFFST(B3,0,0,COUNTA(B3:B22))

Không cần nhập tham số độ rộng theo số cột, vì ở đây chúng ta chỉ áp dụng trên 1 cột B

Tạo danh sách chọn với Data Validation

Sau khi đã viết đúng được hàm OFFSET thì chúng ta chỉ việc copy công thức đó để đưa vào trong mục tạo danh sách chọn trong Data Validation:

Tạo danh sách phụ thuộc nhau, danh sách theo nhóm với hàm OFFSET

Ví dụ về danh sách phụ thuộc nhau, danh sách theo nhóm như sau:

Trong danh sách nhân viên ở trên, chúng ta có thêm cột Bộ phận. Tại đó chia làm 2 bộ phận là Kinh doanh và Kế toán. Khi chọn bộ phận Kinh doanh, chúng ta sẽ có 1 danh sách chỉ có những nhân viên thuộc bộ phận kinh doanh:

Còn khi chọn bộ phận Kế toán, danh sách nhân viên sẽ thay đổi chỉ có nhân viên thuộc bộ phận kế toán:

Để làm được việc này, chúng ta sẽ sử dụng hàm OFFSET như sau:

Xác định điểm bắt đầu làm tham chiếu gốc

Bởi vì bộ phận có thể thay đổi, nên chúng ta sẽ xác định điểm bắt đầu là 1 vị trí cố định. Ví dụ như ô B2

=OFFSET(B2, ….)

Xác định hướng thay đổi điểm bắt đầu theo dòng

Tùy vào bộ phận được chọn ở ô F2 mà chúng ta sẽ xác định từ vị trí B2 sẽ di chuyển bao nhiêu dòng. Việc này xác định một cách khá đơn giản với hàm MATCH như sau:

=MATCH(F2,B3:B22,0)

Dò tìm giá trị F2 ở trong vùng B3:B22. Kết quả sẽ là vị trí dòng thứ mấy trong vùng B3:B22 ứng với giá trị tại F2, nếu có nhiều hơn 1 giá trị đúng thì sẽ trả về vị trí của giá trị đầu tiên.

=OFFSET(B2,MATCH(F2,B3:B22,0), ….)

Xác định hướng thay đổi điểm bắt đầu theo cột

Do danh sách cần tìm là Họ tên nhân viên, cách vị trí ô B2 là 1 cột, nên sẽ thay đổi 1 cột. Chúng ta chọn số 1

=OFFSET(B2,MATCH(F2,B3:B22,0),1, …)

Xác định độ rộng theo dòng

Để xác định độ rộng theo dòng, chúng ta cần đếm xem có bao nhiêu dòng trong cột Bộ phận có giá trị giống với Bộ phận được chọn ở ô F2. Đây là việc đếm theo điều kiện, do đó chúng ta dùng hàm COUNTIF như sau:

=COUNTIF(B3:B22,F2)

=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2), …)

Xác định độ rộng theo cột

Vì danh sách này chúng ta chỉ lấy trên 1 cột, do đó không cần tới điều chỉnh về độ rộng số cột. Có thể không cần nhập tham số này. Chúng ta kết thúc hàm OFFSET với nội dung

=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2))

Tạo danh sách chọn với Data Validation

Cuối cùng chúng ta chỉ việc copy công thức trên và dán vào danh sách chọn Data Validation cho ô F3

Để cho chắc chắn dữ liệu không bị thay đổi, khi đưa công thức vào Data Validation thì chúng ta nên cố định lại tọa độ dữ liệu như sau:

=OFFSET($B$2,MATCH($F$2,$B$3:$B$22,0),1,COUNTIF($B$3:B$22,$F$2))

Hàm OFFSET và các kết hợp hàm của nó trong Excel, công thức ví dụ Tạo Drop-down list có giá trị phụ thuộc một list khác Hướng dẫn cách tạo danh sách nhân viên phụ thuộc theo chi nhánh