Cách Dùng Hàm If 2 Điều Kiện / TOP 9 # Xem Nhiều Nhất & Mới Nhất 3/2023 # Top View

Hàm Vlookup 2 điều kiện trong Excel là hàm tìm kiếm trá trị trong chuỗi với những yêu cầu cao cấp mà hàm vlookup thông thường không xử lý được. Nó giúp ta dò tìm dữ liệu theo cột với điều kiện dò tìm được kết hợp từ 2 hay nhiều cột. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn sử dụng hàm Vlookup 2 điều kiện qua các ví dụ cụ thể, thực tế và dễ hiểu.

Hàm vlookup 2 điều kiện thực chất là hàm Vlookup thông thường, nhưng để sử dụng được thì ta cần kết hợp một số hàm khác nhằm biến đổi điều kiện dò tìm sao cho hàm Vlookup hiểu và trả về kết quả đúng.

1. Chức năng của hàm Vlookup trong Excel.

Trong Excel chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.

2. Cú pháp hàm Vlookup trong excel.

VLOOKUP( LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP] )

Trong đó:

Lookup_value (bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.

Table_array (bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.

Row_index_num (bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.

Range_lookup (tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.

Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.

Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A

3. Hàm Vlookup 2 điều kiện.

Từ công thức trên, ta có thể thấy được hàm Vlookup thuông thường chỉ tìm kiếm được với điều kiện dò tìm là 1 ô tham chiếu, một giá trị, hoặc một chuỗi văn bản. Vậy nếu điều kiện dò tìm nằm ở nhiều ô, nhiều giá trị khác hoặc từ 2 chuỗi văn bản trở lên thì chắc chắn ta không thể sử dụng hàm Vlookup thông thường được. Ở đây, chúng tôi sẽ giới thiệu cho bạn 2 cách để tìm kiếm với hàm vlookup 2 điều kiện, áp dụng được cho cả nhiều điều kiện.

Cách 1: Dùng cột phụ.

Cách 2: Dùng công thức mảng.

Ưu nhược điểm của 2 cách dùng hàm Vlookup 2 điều kiện trong Excel.

Sử dụng cột phụ:

Ưu điểm: Đơn giản, dễ dùng và dễ áp dụng. Dùng được cho cả hàm Vlookup nhiều điều kiện.

Nhược điểm: Làm tăng lượng dữ liệu và tìm kiếm bởi việc tạo thêm cột phụ.

Sử dụng công thức mảng:

Ưu điểm: Không làm tăng lượng dữ liệu, không gây dư thừa, áp dụng được với cả hàm Vlookup nhiều điều kiện.

Nhược điểm: Khó dùng, khó nhớ, công thức dài, trừu tượng.

4. Hàm Vlookup 2 điều kiện sử dụng cột phụ.

4.1 Cách dùng hàm Vlookup 2 điều kiện sử dụng cột phụ.

Cách đơn giản nhất để tìm kiếm trong Excel với 2 điều kiện là sử dụng cột phụ. Từ 2 hay nhiều điều kiện, mình tổng hợp lại vào 1 cột mới và dùng hàm Vlookup để tìm kiếm với điều kiện dò tìm là cột ta vừa tạo.

Tùy vào từng yêu cầu cụ thể mà ta sẽ ghép các điều kiện bằng các cách khác nhau, nhưng thông thường Cột phụ sẽ được tạo ra bằng các ghép các điều kiện bằng kí hiệu “&”.

VD: A1 chứa giá trị là: NV. B1 chứa giá trị là 10.

Để dễ hình dung hơn về cách sử dụng hàm Vloolup với cột phụ chúng ta cùng tìm hiểu một vài ví dụ minh họa.

4.2 Ví dụ sử dụng hàm Vlookup 2 điều kiện bằng cột phụ.

VD: Bạn có một danh sách sản lượng sản xuất cho từng sản phẩm, từng ca. Làm thế nào để biết được sản lượng của 1 sản phẩm nào đó trong từng ca là bao nhiêu?

Hình 1: Hàm Vlookup sử dụng cột phụ để tìm kiếm.

Với bài toán tìm kiến dữ liệu theo hàng ngang trong Excel chắc chắn bạn sẽ nghĩ ngay đến hàm Vlookup, nhưng ở đây ta cần tìm sản lượng của từng Sản phẩm trong từng Ca (2 điều kiện) mà hàm vlookup thông thường lại chỉ dùng được với 1 điều kiện.

Nên ta cần biến đổi điều kiện đầu vào từ 2 điều kiện thành 1 điều kiện bằng cách tạo ra một cột phụ mới từ việc ghép Sản phẩm và Ca.

Các bước thực hiện:

B1: Tạo cột phụ.

B2: Viết hàm với điều kiện tìm kiếm là cột phụ vừa tạo.

Chi tiết các bước:

B1: Tạo cột phụ:

Ta tạo thêm cột mới, cột này đứng ở trước cột Sản phẩm và được tạo ra bằng cách ghép cột Sản phẩm và cột Ca.

Công thức ghép: [Sản phẩm]&[Ca]

Với hàng đâu tiền thì công thức sẽ là: C5&D5

Sau khi tạo công thức cho hàng đầu ta copy công thức đó cho các hàng tiếp theo để hoàn thành cột.

Sau khi hoàn thành thì cột phụ sẽ có dạng như hình sau:

Hình 2: Hàm Vlookup sử dụng cột phụ.

Bước 2: Viết công thức tìm kiếm với cột phụ vừa tạo.

Để tìm kiến sản lượng của 1 sản phẩm theo ca tại ô H8 ta nhập công thức: =VLOOKUP(H6&H7,$B$5:$E$9,4,0)

Trong đó:

H6&H7: Là giá trị cần đối chiếu.

$B$5:$E$9: Là bảng dò tìm bao gồm cả cột phụ

4: Số thứ tự cột dữ liệu trên bảng dò tìm.

0: Kiểu tìm kiếm chính xác.

Quan sát hình dưới để hiểu hơn về các thành phần của công thức chúng ta vừa tạo.

Hình 3: Hàm Vlookup sử dụng cột phụ.

Nếu muốn tìm sản lượng của sản phẩm khác hoặc ca khác bạn chỉ cần nhập sản phẩm và ca cần tìm vào bảng tìm kiếm như trên hình mà không cần tạo lại công thức.

4.3 Những lưu ý khi sử dụng hàm Vlookup 2 điều kiện bằng cột phụ.

Về bản chất: Hàm Vlookup 2 điều kiện khi dùng cột phụ sẽ trở thành hàm vlookup 1 điều kiện thông thường.

Bằng cách này bạn cũng có thể áp dụng cho nhiều điều kiện một cách dễ dàng. Cột phụ sẽ được tạo bằng cách ghép các điều kiện lại sao cho phù hợp.

Cột phụ được tạo ra phải được đặt ở đầu bảng dò tìm.

Khi tạo cột phụ ta phải ghép các cột lại theo thứ tự giống với thứ tự của điều kiện dò tìm.

5. Hàm Vlookup 2 điều kiện sử dụng công thức mảng.

Công thức mảng là một cách tính nâng cao trong Excel mà không phải ai học cũng chú ý tới nó. Chắc hẳn, trên 90% các bạn đọc bài viết này sẽ không biết tới việc sử dụng công thức mảng để tìm kiếm với nhiều điều kiện. Bởi hầu hết mọi người ít biết tới mảng trong Excel và cách này tính này khá phức tạp với những bạn chưa thành thạo Excel. Nên các bạn có thể sử dụng hàm vlookup 2 điều kiện bằng cách tạo cột phụ.

5.1 Công thức mảng là gì?

Mảng là một hàng giá trị, một cột giá trị hoặc là một kết hợp các hàng và cột giá trị. (ví dụ: {1,2,3,4})

Công thức mảng là công thức được bao bởi cặp dấu ngoặc nhọn {} do excel tự động thêm vào sau khi kết thúc nhập công thức. Công thức mảng là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng. Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất.

Công thức mảng bao gồm nhiều ô được gọi là công thức đa ô và công thức mảng trong một ô duy nhất được gọi là công thức đơn ô.

Nhập công thức mảng:

Chọn ô hoặc vùng ô cần nhập công thức. Nhập công thức cần tính toán

Nhấn tổ hợp phím Ctrl + Shift + Enter.

5.2 Cách dùng hàm Vlookup 2 điều kiện sử dụng công thức mảng.

Khi sử dụng công thức mảng chúng ta sẽ không phải tạo thêm cột, không làm tăng lượng dữ liệu và tìm kiếm.

Để tìm kiếm ta kết hợp sử dụng hàm Vlookup với hàm Choose. Và công thức mảng được lồng trong hàm Choose.

5.3 Ví dụ về hàm Vlookup 2 điều kiện sử dụng công thức mảng.

Như ví dụ bên trên ta tiếp tục tìm kiếm sản lượng của một sản phẩm theo từng ca.

Để tìm kiến sản lượng của 1 sản phẩm theo ca tại ô G7 ta nhập công thức: =VLOOKUP(G5&G6,CHOOSE({1,2},B5:B9&C5:C9,D5:D9),2,0) và nhấn Ctrl + Shift + Enter.

Trong đó:

G5&G6: Là kết hợp 2 điều kiện cần tìm.

CHOOSE({1,2},B5:B9&C5:C9,D5:D9): Là bảng dò tìm, bảng này được tạo từ 1 mảng 2 chiều có 2 cột.

Cột 1: Cột tham chiều.

Cột 2: Cột giá trị trả về.

2: Số thứ tự cột dữ liệu trên bảng dò tìm.

0: Kiểu tìm kiếm chính xác.

Hình 4: Tìm kiếm bằng công thức mảng.

Đọc đến đây chắc hẳn nhiều bạn vẫn chưa hiểu được về hàm Choose mà ta đã sử dụng.

Hình 5: Hình họa giá trị trả về của hàm Choose trong Excel.

Giải thích công thức: =CHOOSE({1,2},B5:B9&C5:C9,D5:D9)

Trong đó:

{1,2}: Công thức này sẽ trả về 1 mảng 2 chiều gồm 2 cột. Cột 1 được lấy ở ngay sau dấu phẩy thứ nhất, cột 2 được lấy ở sau dấu phẩy thứ 2.

B5:B9&C5:C9: Giá trị trả về là 1 cột, các giá trị trong cột này được ghép từ cột Ca và cột Sản phẩm.

D5:D9: Là cột sản phẩm.

Chú ý: Hình 5 chỉ để các bạn hiểu hơn về cách hoạt động của hàm CHOOSE trong Excel, khi dùng với hàm Vlookup các bạn chỉ cần viết hàm CHOOSE lồng trong hàm Vlookup như trong hình 4.

5.4 Lưu ý khi sử dụng hàm Vlookup 2 điều kiện bằng công thức mảng.

Khi nhập xong công thức bạn nhớ nhấn tổ hợp phím Ctrl + Shift + Enter.

Dấu ngoặc nhọn {} bao lại công thức mảng là do excel tự động thêm vào sau khi kết thúc nhập công thức.

Hàm Vlookup 2 điều kiện dùng công thức mảng khá khó dùng nhưng nếu bạn thành thạo nó sẽ hỗ trợ bạn rất nhiều, kể cả với nhiều điều kiện.

Nếu bạn không thực sự thành thạo thì nên bỏ qua cách này để tránh viết sai công thức dẫn đế tính toán sai.

Gợi ý học tập mở rộng.

Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản

Hàm IF nhiều điều kiện là hàm giúp kiểm tra đối chiếu với nhiều nhiều điều kiện và trả về kết quả tương ứng. Bằng cách kết hợp nhiều hàm IF lồng nhau, hoặc kết hợp hàm IF với hàm AND, OR khiến cho hàm IF thông thường có thể kiểm tra đối chiếu với nhiều điều kiện.

Trong bài viết này, Học Excel Cơ Bản sẽ giúp bạn tìm hiểu về cú pháp và cách kết hợp hàm IF với những hàm khác như hàm AND, OR,… để kiểm tra nhiều điều kiện.

1.Chức năng của hàm IF nhiều điều kiện

Hàm IF là một trong những hàm logic giúp người dùng kiểm tra một điều kiện nhất định và trả về giá trị mà bạn chỉ định nếu điều kiện là TRUE hoặc trả về một giá trị khác nếu điều kiện là FALSE.

Hàm IF nhiều điều kiện giúp ta tính toán, kiểm tra và đối chiếu nhiều điều kiện khác nhau và trả về kết quả tương ứng.

2.Cú pháp của hàm IF trong Excel.

Trong đó:

Logical_test (bắt buộc): Là một giá trị hay biểu thức logic trả về giá trị (đúng) hoặc (sai). Bắt buộc phải có. Đối với tham số này, bạn có thể chỉ rõ đó là ký tự, ngày tháng, con số hay bất cứ biểu thức so sánh nào.

Value_if_true (không bắt buộc): Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị hay nói cách khác là điều kiện thỏa mãn.

Value_if_false (không bắt buộc): Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị hay nói cách khác là điều kiện không thỏa mãn.

Trong một số trường hợp bài toán chứa nhiều điều kiện bạn cần sử dụng thêm hàm AND, OR để kết hợp nhiều điều kiện.

Bài toán có nhiều điều kiện, các điều kiện xảy ra đồng thời bạn cần sử dụng thêm hàm AND trong biểu thức điều kiện. Cú pháp hàm AND là: AND(logical1, logical2,…)

Bài toán có nhiều điều kiện, giá trị trả về chỉ cần thỏa mãn 1 trong các điều kiện bạn sử dụng thêm hàm OR trong biểu thức điều kiện. Cú pháp hàm OR: OR(logical1, logical2,..). Trong đó logical là các biểu thức điều kiện.

3.Hướng dẫn sử dụng hàm IF nhiều điều kiện.

3.1. Sử dụng hàm IF chỉ chứa 1 điều kiện cần xét.

Ví dụ: Đưa ra kết quả thi tuyển vào lớp 10 dựa vào kết quả thi 3 môn, nếu tổng điểm lớn hơn hoặc bằng 24 thì học sinh thi đỗ, ngược lại thí sinh thi trượt.

Hình 1: Hàm IF nhiều điều kiện.

Vậy trong trường hợp này chúng ta sẽ sử dụng hàm IF với điều kiện cơ bản nhất là nếu không đúng thì sai. Ở đây chúng ta sẽ gán cho hàm IF điều kiện là nếu tổng điểm lớn hơn hoặc bằng 24 thì “Đỗ” còn tổng điểm nhỏ hơn 24 thì “Trượt”.

Trong đó:

“Đỗ” : Giá trị trả về của hàm IF nếu biếu thức so sánh trả về là đúng.

“Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.

Sau khi nhập công thức cho ô I4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:

Hình 2: Hàm IF nhiều điều kiện.

3.2. Hàm IF nhiều điều kiện kết hợp hàm AND.

Thông thường với những yêu cầu thực tế bạn cần thỏa mãn nhiều điều kiện cùng lúc. Chính vì vậy bạn cần kết hợp hàm AND để đảm bảo các yếu tố điều kiện đều được đảm bảo.

Cú pháp của hàm AND: AND(logical1, logical2,…)

Trong đó logical1 và logical2 là 2 mệnh đề logic.

Kết quả trả về của hàm AND:

TRUE: Khi tất cả các mệnh đề logic đều đúng.

FALSE: Khi có một mệnh đề logic bất kì trong hàm bị sai.

Chúng ta thường đặt hàm AND trong biểu thức logic của hàm IF để xét điều kiện cho hàm IF.

Ví dụ: Đưa ra kết quả thi tuyển vào lớp 10 dựa vào kết quả thi 3 môn, điều kiện xét tuyển là nếu học sinh đỗ phải đạt tổng điểm 3 môn lớn hơn hoặc bằng 18 điểm và không có môn nào đạt điểm 0.

Trong đó:

“Đỗ” : Giá trị trả về của hàm nếu biếu thức so sánh trả về là đúng.

“Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.

Sau khi nhập công thức cho ô I4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:

Hình 4: Hàm IF nhiều điều kiện.

Chú ý: Hàm AND chỉ chả về kết quả TRUE khi tất cả các biểu thực logic bên trong đều được thỏa mãn.

3.3. Hàm IF nhiều điều kiện kết hợp hàm OR.

Cú pháp của hàm OR trong Excel: OR(logical1, logical2,…)

Trong đó logical1 và logical2 là 2 mệnh đề logic.

Kết quả trả về của hàm OR:.

TRUE: Khi có một mệnh đề logic bất kì trong hàm OR là đúng.

FALSE: Khi tất cả các mệnh đề bên trong hàm OR đều sai.

Bạn sử dụng kết hợp các hàm IF và hàm OR theo cách tương tự như với hàm AND ở trên.

Trong đó:

“Đỗ”: Giá trị trả về của hàm nếu biếu thức so sánh trả về là đúng.

“Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.

Sau khi nhập công thức cho ô E4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:

Hình 6: Hàm IF nhiều điều kiện.

3.4. Hàm IF nhiều điều kiện kết hợp hàm AND và hàm OR.

Trong trường hợp bạn phải đánh giá dữ liệu của mình dựa trên nhiều điều kiện, chúng ta sẽ phải sử dụng cả hai hàm AND và OR cùng một lúc.

Ở những ví dụ trên chúng ta đã nắm được cách sử dụng hàm IF kết hợp với hàm AND và hàm IF kết hợp với hàm OR. Nên ở phần này chúng ta chỉ cần kết hợp 2 hàm này lại để đặt điều kiện cho biểu thức logic sao cho khoa học phù hợp với yếu cầu thực tế của bài toán.

Hình 7: Hàm IF nhiều điều kiện.

Với điều kiện trên, ta có thể phân tích thành 2 điều kiện nhỏ:

Điều kiện 1 và điều kiện 2 ta viết bằng hàm AND, cuối cùng sử dụng hàm OR kết hợp 2 kiều kiện trên làm điều kiện kiểm tra logic trong hàm IF và cung cấp các đối số TRUE (Đúng) và FALSE (Sai). Kết quả là bạn sẽ nhận được công thức IF sau với nhiều điều kiện AND/OR:

Trong đó:

“Đỗ” : Giá trị trả về của hàm nếu biếu thức so sánh trả về là đúng.

“Trượt” : Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.

Sau khi nhập công thức cho ô E4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:

Hình 8: Hàm IF nhiều điều kiện. 4. Lưu ý khi sử dụng hàm IF nhiều điều kiện trong Excel.

4.1. Hàm IF không phân biệt chữ hoa và chữ thường.

Cũng giống như phần lớn những hàm khác, hàm IF được mặc định không phân biệt chữ hoa hay chữ thường. Điều này có nghĩa rằng các biểu thức logic có chứa ký tự không thể phân biệt được kiểu chữ hoa hay thường trong công thức hàm IF.

Ví dụ khi so sánh một ô nào đó với “Hà Nội” thì hàm IF sẽ hiểu Hà Nội, hà nội, HÀ NỘI, … là như nhau.

Video hướng dẫn. Gợi ý học tập mở rộng.

Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản

Hàm Countif

Chức năng của hàm Countif

Hàm Countif dùng để đếm các ô trong vùng dữ liệu theo một điều kiện.

Công thức hàm Countif

Countif (range, criterial)

Giải thích:

range – Vùng điều kiện chứa điều kiện cần đếm

criterial – Điều kiện. Điều kiện có thể là số, biểu thức, tham chiếu ô hay chuỗi văn bản để xác định ô sẽ được đếm. Có thể kết hợp các ký tự đặt biệt trong điều kiện: ? đại diện 1 ký tự, * đại diện nhiều ký tự.

Ví dụ về hàm Countif trong Excel

Cho bảng Dữ liệu sau. Hãy thống kê số học sinh trong lớp theo bảng Xếp loại học sinh.

Áp dụng hàm Countif vào trường hợp này, ta có Kết quả tại ô C17 như sau:

C17 = Countif (E5:E14,”Giỏi”)

Trong đó:

E5:E14 là vùng dữ liệu, chứa dữ liệu để đếm. “Giỏi” là điều kiện (Criterial) Hàm Countif sẽ đếm tất cả những ô thỏa mãn điều kiện là “Giỏi” trong vùng dữ liệu từ E5 đến E14 ngược lại sẽ không tính.

Tương tự cho các ô còn lại với trường hợp đếm số học sinh Khá, Trung Bình, Yếu.

C18 = COUNTIF(E5:E14,”Khá”)

C19 = COUNTIF(E5:E14,”Trung Bình”)

C20 = COUNTIF(E5:E14,”Yếu”)

Kết quả dùng hàm Countif để thống kê số Học sinh theo cột Xếp loại

Hàm Countifs

Chức năng của hàm Countifs

Hàm Countifs tương tự như hàm Countif, nhưng đếm tổng số ô theo nhiều điều kiện cùng một lúc trong vùng dữ liệu.

Công thức hàm Countifs

Countifs (range 1, criteria 1, range 2, criteria 2, range 3, criteria 3, …)

Giải thích:

range 1, range 2, range 3, …: Vùng điều kiện 1 chứa điều kiện 1, vùng điều kiện 2 chứa điều kiện 2, vùng điều kiện 3 chứa điều kiện 3,… criteria 1, criteria 2, criteria 3, …: điều kiện 1, điều kiện 2, điều kiện 3, … tương ứng với các vùng điều kiện ở trên. Ví dụ về hàm Countifs trong Excel Cho bảng Dữ liệu sau. Hãy thống kê:

Trong đó:

Trong đó:

Hàm Sumif

Hàm Sumif là một trong những hàm thống kê có điều kiện trong Excel, được sử dụng khá phổ biến trong Excel, được ứng dụng vào học tập, công việc văn phòng,…

Trong bài viết này chúng tôi sẽ hướng dẫn các bạn hiểu rõ nhất về cú pháp cũng như cách sử dụng của hàm Sumif trong Excel.

Chức năng của hàm Sumif trong Excel

Hàm Sumif dùng để tính tổng theo một điều kiện. Tính tổng cho các ô trong vùng cần tính tổng (Sum_range) thỏa mãn điều kiện (Criteria) theo vùng điều kiện (range).

Cú pháp của hàm Sumif trong Excel Sumif (range, criteria, sum_range)

Sumif (Vùng chứa dữ liệu so sánh với điều kiện, Điều kiện, Vùng cần tính tổng)

Giải thích:

Range: Vùng chứa dữ liệu so sánh với điều kiện (Criteria) Criteria: Điều kiện Sum_range: Vùng cần tính tổng Ví dụ hàm Sumif trong Excel Ví dụ 1: Cho bảng tính như mẫu bên dưới. Hãy tính tổng lương cho Phòng Ban là “Kế hoạch” tại ô E15.

Áp dụng hàm Sumif để tính tổng lương cho phòng ban Kế hoạch tại ô E15 ta có. E15=SUMIF(D5:D12,”Kế hoạch”,E5:E12)

Trong công thức này:

D5:D12 – Vùng chứa dữ liệu cần so sánh với điều kiện: Kế hoạch “Kế hoạch” – Điều kiện (Criteria) E5:E12 – Vùng cần tính tổng Kết quả sau khi sử dụng hàm Sumif thống kê như sau:

Hàm Sumifs

Hàm Sumifs là hàm thống kê theo nhiều điều kiện trong Excel, được sử dụng khá phổ biến trong Excel, được ứng dụng vào học tập, công việc văn phòng,…

Trong bài viết này chúng tôi sẽ hướng dẫn các bạn hiểu rõ nhất về cú pháp cũng như cách sử dụng của hàm Sumifs trong Excel.

Chức năng của hàm Sumifs trong Excel

Hàm Sumifs dùng để tính tổng theo nhiều điều kiện. Tính tổng cho các ô trong vùng cần tính tổng (Sum_range) thỏa mãn điều kiện 1 (Criteria 1) theo vùng điều kiện 1 (range 1), điều kiện 2 (Criteria 2) theo vùng điều kiện 2 (range 2),… Chỉ áp dụng cho điều kiện và.

Cú pháp của hàm Sumif trong Excel Sumif (sum_range, range 1, criteria 1, [range 2, criteria 2], … )

Giải thích:

Sum_range: Vùng cần tính tổng

Range 1: Vùng chứa dữ liệu so sánh với điều kiện 1 (Criteria 1)

Criteria 1: Điều kiện 1

Range 2: Vùng chứa dữ liệu so sánh với điều kiện 2 (Criteria 2)

Criteria 2: Điều kiện 2

Ví dụ hàm Sumifs trong Excel

Ví dụ 1: Cho bảng tính như mẫu bên dưới. Hãy tính tổng thành tiền cho sản phẩm “Laptop” có Loại “A” tại ô C14

Áp dụng hàm Sumifs để tính tổng thành tiền cho sản phẩm “Laptop” có Loại “A” tại ô C14=SUMIFS(E2:E7,A2:A7,”A”,B2:B7,”Laptop”)

Trong công thức này:

E2:E7: Vùng cần tính tổng

A2:A7: Vùng chứa dữ liệu cần so sánh với điều kiện 1: “A”

“A”: Điều kiện 1 (Criteria 1)

B2:B7: Vùng chứa dữ liệu cần so sánh với điều kiện 2: “Laptop”

“Laptop”: Điều kiện 2

Kết quả sau khi sử dụng hàm Sumifs thống kê như sau:

Hàm Vlookup nhiều điều kiện

Hàm Vlookup thực sự sẽ cho trả về kết quả chính xác nhất khi các bạn tìm kiếm một giá trị nhất định nào đó trên một bảng cơ sở dữ liệu. Tuy nhiên, như chúng ta đã học qua hàng Vlookup cơ bản thì cú pháp của nó chỉ cho phép chúng ta tra cứu trên một giá trị. Nếu các bạn lại muốn tìm kiếm theo 2 hay nhiều điều kiện thì sao nhỉ? Giải pháp cho các bạn là đây.

Ví dụ về sử dụng hàm Vlookup 2 điều kiện

Một công thức Vlookup cơ bản như đã được học sẽ không thể giải quyết được trường hợp 2 điều kiện này, bởi nó kết quả trả về đầu tiên sẽ giống với giá trị tra cứu khi bạn dùng để chỉ định.

Khi đó thì có giả pháp nào không? Đơn giản là các bạn hãy tạo một cột bổ sung nối ghép tất cả các tiêu chí theo như mong muốn của mình. Hãy luôn chú ý rằng cột liên kết luôn được xem là một cột ngoài cùng ở phía bên trái thuộc phạm vi tra cứu của các bạn vì đây chính là nơi mà hàm Vlookup luôn luôn tìm kiếm được giá trị tra cứu.

Vì vậy, các bạn thêm một cột phụ vào phái bên trái bảng và sao chép một công thức như sau: giả sử cội ngoài cùng là sự kết của cộ B@ và C2 thì ta sẽ có =B2&C2 qua cột đó hoặc cũng có thể là =B2&” “&C2 nếu các bạn muốn tách các giá trị băng một cách khoảng trắng để thuận lợi trong việc dễ đọc dữ liệu hơn.

Khi áp dụng theo cách làm này các bạn phải chú ý, để công thức đó được hoạt động, cột bên trái nhất trong bảng tra cứu phải chứa tất cả các giá trị tra cứu nhằm ghép nối chính xác như trong tiêu chí tra cứu của các bạn.

Như bạn cũng đã biết rằng, chúng ta ghép tất cả các giá trị với nhau bằng một khoảng cách trắng trong bảng tra cứu, vì vậy cần phải làm tương tự như trong tiêu chí tra cứu của công thức hàm VLOOKUP (B2 & “” & C2).

Nói rõ hơn về sự hoạt động công thức

Cột phụ khi kết hợp các điều kiên của bạn giúp nối tất cả giá trị từ cột được sử dụng để làm điều kiện. Tuy nhiên, cột này phải là cột thứ nhất của bảng và nó hoạt động giông như một “key”. Trong hàm Vlookup, các giá trị tìm kiếm sẽ được tạo bằng cách nhập các điều kiện đó giống nhau.

Chúng ta phải thiết lập cách sử dụng hàm Vlookup nhiều điều kiện theo 3 bước:

Thêm một cột phụ và nốt tất cả các giá trị từ những cột mà bạn muốn sử dụng để làm điều kiện.

Sau đó, thiết lập công thức Vlookup tham chiếu đến bảng dữ liệu bao gồm cả cột phụ. Lưu ý cột đầu tiên trong bảng phải là cột phụ.

Đối với các giá trị tra cứu, khi nối cùng với một điều kiện sẽ cùng thứ tự để phù hợp với các giá trị trong cột phụ.

Chúng tôi vừa hướng dẫn các bạn sử dụng hàm Vlookup có nhiều điều kiện. Vlookup có tính ứng dụng cao nên các bạn có thể dùng hàm này trong kế toán hiệu quả mang lại sẽ giúp ích cho công việc, đồng thới việc dùng hàm Vlookup, thì còn có rất nhiều hàm khác trong Excel thường được áp dụng như hàm If Vlookup, hàm Vlookup và If, hàm Vlookup If,…

Bạn Yến Trần – sinh viên đang học văn bằng 2 Cao đẳng Dược TPHCM chia sẻ:”Dù em đang theo học ngành y dược, thế nhưng, việc thành thạo sử dụng phần mềm Microsoft Execl hay các công cụ tin học văn phòng khác là điều rất quan trọng”.

Tuy nhiên, để sử dụng thuần thục các hàm cơ bản có tính ứng dụng cao trong Excel, đặc biệt là hàm Vlookup, các bạn cần phải rèn luyện với rất nhiều dạng bài tập với hàm Vlookup được chia sẻ trên các diễn đàn, facebook hoặc từ những anh chị đã có kinh nghiệm từ đó giúp bạn nâng cao thêm kiến thức khi sử dụng hàm Vlookup.