Top 17 # Xem Nhiều Nhất Bài Tập Hàm Countif Trong Excel Có Lời Giải / 2023 Mới Nhất 11/2022 # Top Like | Beiqthatgioi.com

Bài Tập Mẫu Hàm Countif Và Countifs Trong Excel (Có Lời Giải) / 2023

Ngoài ra, đây còn là một bài tập ôn thi chứng chỉ A tin học văn phòng hiệu quả.

Ở cuối bài, có file Excel XLSX chứa bảng dữ liệu và lời giải cho từng ô, bạn cũng có thể tải về máy mở lên tham khảo.

Bảng dữ liệu bài tập

Yêu cầu tính toán

Lập công thức excel để đếm:

Số nhân viên phòng Kinh Doanh;

Số Nhân viên có nghỉ phép trong tháng;

Số nhân viên họ Trần;

Số Nhân viên phòng kinh doanh không nghỉ phép;

Số Nhân viên phòng kinh doanh nghỉ phép trên 5 ngày.

Khi đề bài yêu cầu đếm số lượng các giá trị nào đó, ta nghĩ ngay đến bộ hàm COUNT.

Nếu đề bài yêu cầu đếm có 1 điều kiện, thì hàm phải dùng là COUNTIF

Nếu đề bài yêu cầu đếm có nhiều điều kiện, thì hàm phải dùng là COUNTIFS

Bây giờ ta bắt đầu ứng dụng hàm COUNTIF và COUNTIFS để tính theo yêu cầu của bài tập Excel này.

Số nhân viên phòng Kinh Doanh:

Yêu cầu đếm có 1 điều kiện là phòng “Kinh doanh”, do đó ta dùng hàm COUNTIF

Tại ô E31, ta gõ hàm như sau:

= COUNTIF(B6:B29,”Kinh doanh”)

Enter sẽ cho kết quả

Số Nhân viên có nghỉ phép trong tháng:

Yêu cầu đếm có 1 điều kiện là có nghỉ phép, tức cột “Số ngày nghỉ” khác 0, do đó ta dùng hàm COUNTIF

Tại ô E32, ta gõ hàm như sau:

Enter sẽ cho kết quả.

Số nhân viên họ Trần:

Yêu cầu đếm có 1 điều kiện là nhận viênc ó họ” Trần”, tức cột “Tên” bắt đầu bằng chữ “Trần”. Do đó ta dùng hàm COUNTIF

Tại ô E33, ta gõ hàm như sau:

Enter sẽ cho kết quả.

Tại ô E35, ta gõ hàm như sau:

= COUNTIFS(B6:B29,”Kinh doanh”,C6:C29,)

Tại ô E36, ta gõ hàm như sau:

Số Nhân viên phòng kinh doanh không nghỉ phép:

Yêu cầu đếm có 2 điều kiện là “Phòng” “Kinh doanh” và “Số ngày nghỉ” là 0, do đó ta dùng hàm COUNTIFS

Tại ô E37, ta gõ hàm như sau:

Tại ô E38, ta gõ hàm như sau:

Video hướng dẫn

Chú ý chọn chế độ xem HD rồi phóng to màn hình để nhìn rõ chữ.

Tải file .XLSX giải đề

Trần Triệu Phú

Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.

Bài Tập Mẫu Hàm Vlookup Trong Excel (Có Lời Giải) / 2023

Bài này còn có ví dụ cho việc áp dụng hàm VLOOKUP kết hợp với các làm trích kí tự LEFT, RIGHT để dò tìm và lấy giá trị từ một bảng cho trước.

Ngoài ra, đây còn là một bài tập ôn thi chứng chỉ A tin học văn phòng hiệu quả.

Ở cuối bài, có file Excel XLS chứa bảng dữ liệu và lời giải cho từng ô, bạn cũng có thể tải về máy mở lên tham khảo.

Bảng dữ liệu bài tập

Câu 1) Dựa vào kí tự đầu và 2 kí tự cuối của “Mã HĐ”, tìm trong “Bảng Danh Mục” đề lấy tên mặt hàng điền vào cột “Mặt Hàng”.

Câu 2) Tìm trong “Bảng Danh Mục” và điền giá trị vào cột “Đơn vị”

Câu 3) Tìm trong “Bảng Danh Mục” và điền giá trị vào cột “Đơn giá”

Câu 4) Tính cột “Thành tiền” bằng “Đơn giá” nhân “SLượng”

Câu 5) Tính và điền giá trị vào bảng “Tổng số tiền bán được của các mặt hàng”

Câu 1:

Tại ô C4, gõ hàm:

Ý nghĩa là: Lấy bên trái của B4 1 kí tự rồi ghép với 2 kí tự bên phải, đem tìm cụm này trong “Bảng Danh Mục” theo chiều dọc và lấy kết quả là giá trị ở cột thứ 2 của bảng. Tìm chính xác.

Thủ thuật về cách kéo để copy công thức

Thủ thuật về cách kéo để copy công thức

Câu 2:

Tại ô E4, gõ hàm:

Ý nghĩa tương tự như câu 1, nhưng lấy kết quả là giá trị ở cột thứ 3 của bảng (Tương ứng là cột Đơn vị).

Câu 3:

Tại ô G4, gõ hàm:

Ý nghĩa tương tự như câu 1, nhưng lấy kết quả là giá trị ở cột thứ 4 của bảng (Tương ứng là cột Đơn giá).

Câu 4:

Tại ô H4, gõ công thức:

=F4*G4

Ý nghĩa: Lấy giá trị cột F4 nhân cột G4.

Câu 5:

– Tại ô B29, gõ hàm:

= SUMIF(B4:B13, “S*M?”, H4:H13)

Ý nghĩa: Tính tổng doanh số của tất cả các hóa đơn bán đĩa mềm. Đây là tính tổng có điều kiện, vì vậy ta dùng SUMIF. Điều kiện là hóa đơn bán đĩa mềm, tức là các “Mã HĐ” sẽ có dạng

Vì vậy công thức trên có ý nghĩa là tìm trong cột B4:B13, Mã HĐ nào có dạng S*M? thì cộng các giá trị tương ứng trong cột H4:H13

– Tương tự, tại ô C29, gõ hàm:

– Tương tự, tại ô D29, gõ hàm:

Password giải nén là:

Trần Triệu Phú

Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.

Cách Sử Dụng Hàm Vlookup Có Bài Tập Và Lời Giải / 2023

Hàm dò tìm & trả về giá trị trong vùng dò tìm dựa vào mã dò tìm (Chúng ta sẽ hiểu hơn sau khi xem các ví dụ cụ thể)

Cột chứa giá trị cần tìm:

Thứ tự của cột cần lấy dữ liệu trong vùng dò tìm.  Tại công thức trong vd trên cột chứa giá trị cần tìm là cột tên hàng, cột số 2

Kiểu tìm kiếm, tìm tuyệt đối thì chọn 0, tìm tương đối thì chọn 1, chúng ta thường dùng kiểu dò tìm tuyệt đối nên chọn 0.  Tại công thức trong vd trên thì kiểu tìm kiếm là tuyệt đối- chọn 0 III. Cách dùng hàm.

Nếu đọc khái niệm & cú pháp hàm và thực hiện thì chúng ta sẽ làm được các trường hợp dò tìm cơ bản, nhưng trên thực tế có rất nhiều trường hợp khó bắt chúng ta phải kết hợp hàm Vlookup với các hàm khác để thực hiện dò tìm. Tôi sẽ đưa ra một số trường hợp cụ thể như bên dưới để các bạn hình dung ra những trường hợp có thể gặp phải.

Hàm sẽ thực hiện dò tìm theo mã hàng trong bảng tra thông tin và trả về kết quả ở cột số 2 trong bảng tra thông tin dóng theo hàng ngang, tương ứng với X là Xăng.

=VLOOKUP(LEFT(A4;2);$A$12:$B$15;2;0)  Hàm sẽ trả về kết quả là Khang Dân. Chúng ta copy công thức xuống các ô còn lại

Vì yêu cầu là dùng 2 ký tự đầu tiên của Mã SP để dò tìm trong Bảng tra thông tin nên chúng ta phải dùng hàm Left để lấy 2 ký tự đầu tiên trong Mã SP và dò tìm trong bảng tra thông tin

Trường hợp này tôi tách so với trường hợp 3 là vì phải tách ký tự số trong Mã SP, nếu dùng nguyên hàm mid thì vẫn tách được nhưng kết quả là dạng text chứ không phải dạng số, vậy nên cần dùng thêm hàm value để chuyển ký tự đó thành dạng số để tiến hành dò tìm trong bảng thông tin 4

2 , thế nhưng đây lại không phải dạng số để có thể dò tìm tại bảng thông tin 4

Vậy Cần dùng = VALUE(MID(A4;4;1)) Kết quả là 2 , là dạng số để sẵn sang dò tìm trong bảng thông tin 4.

Đáp án : Nhập công thức tại ô C4 như sau.

=VLOOKUP(A4,$A$12:$D$15,IF(B4=1,2,IF(B4=2,3,4)),0) àHàm sẽ trả về kết quả là 23.000

Trường hợp này hàm if có tác dụng chọn cột lấy giá trị, IF(B4=1,2,IF(B4=2,3,4)) Nếu Loại gạo =1 thì sẽ lấy giá trị ở cột 2, nếu loại gạo = 2 thì sẽ lấy giá trị ở cột 3, còn lại lấy giá trị ở cột 4.

Bài viết này là bài khởi đầu của mình, đây chỉ là những trường hợp cơ bản nhất của hàm Vlookup thường dùng, mình sẽ cập nhật thêm các trường hợp khác hàng ngày.

One more thing , Cần phải dành nhiều thời gian cho đam mê của mình hơn nữa. Cảm ơn!

Hải đăng

Hướng Dẫn Cách Dùng Hàm Index Và Match Qua Bài Tập Có Lời Giải / 2023

Bài tập 1: Sử dụng hàm MATCH tìm vị trí theo dòng, cột

Cho bảng tính sau:

Nếu muốn biết thông tin của người có tên “Lê Thị Thủy” thì bạn phai làm thế nào? Chắc hẳn việc đầu tiên bạn cần xác định xem người đó nằm ở vị trí thứ mấy trong bảng tính này.

Bằng cách sử dụng hàm MATCH, bạn sẽ biết được 1 người nào đó nằm ở dòng thứ mấy trong bảng.

Ta có công thức sau:

=MATCH(“Lê Thị Thủy”, cột Họ tên, số 0)

Tại ô D12 nhập tên Lê Thị Thủy

cột Họ tên là vùng C3:C8

Như vậy hàm MATCH cho kết quả là số 3, tương ứng với dòng thứ 3 trong bảng dữ liệu trên.

Vậy việc tìm ra số 3 thì có ý nghĩa gì? Hay nói cách khác biết người đó ở vị trí dòng thứ 3 trong bảng thì có tác dụng gì?

Bài tập 2: Kết hợp hàm INDEX với MATCH tìm Email theo tên

Khi nhìn vào bảng trên, ta thấy tại vị trí dòng 3, trong cột Email sẽ xuất hiện Email cần tìm. Như vậy có thể hiểu: Chỉ cần dóng theo cột Email, tại vị trí dòng 3 trong bảng là chúng ta có kết quả cần tìm.

Điều đó chính là cách kết hợp hàm INDEX với MATCH:

=INDEX(E3:E8,MATCH(D12,C3:C8,0))

Công thức này, hàm INDEX có nhiệm vụ:

Chỉ ra cột chứa giá trị cần tìm là cột E, vùng E3:E8

Chỉ ra dòng chứa giá trị cần tìm, dòng này đã xác định bởi hàm MATCH ở trên

Giao điểm giữa dòng và cột trong hàm INDEX tìm chính là kết quả email cần tìm.

Bài tập 3: Dò tìm từ phải qua trái với hàm INDEX kết hợp MATCH

Thông thường chúng ta hay dò tìm theo chiều từ Trái qua Phải. Nhưng nếu phải tìm từ Phải qua Trái (ngược lại với thông thường) thì làm thế nào?

Ví dụ như sau: Tìm tên dựa vào thông tin Email?

Như vậy để dò tìm họ tên dựa vào Email tức là chúng ta đang tìm từ Phải qua trái.

Quá trình thực hiện như sau:

Dùng hàm MATCH để tìm vị trí dòng của email trong cột Email =MATCH(Email, vùng chứa Email, 0)

Kết hợp với hàm INDEX trong việc chỉ ra vị trí cột chứa kết quả cần tìm, là cột Họ tên (cột C), vùng C3:C8

=INDEX(C3:C8,MATCH(D12,E3:E8,0))

Rất đơn giản phải không nào.

Bài tập 4: Xác định đơn giá của mặt hàng theo nhiều điều kiện

Ví dụ ta có bảng đơn giá như sau:

Điều này tương đương việc chúng ta phải xét đồng thời 2 điều kiện: Mã hàng và Mã công ty

Để thực hiện yêu cầu này, chúng ta sẽ sử dụng hàm Index kết hợp hàm Match như sau:

Tham chiếu giá trị Mã hàng trong dòng 3 (dòng chứa thông tin mã hàng làm căn cứ đối chiếu)

Hàm MATCH trả về kết quả là 2, tương ứng với cột thứ 2

=MATCH(H2,A3:E3,0)

Tham chiếu giá trị Mã công ty trong cột A (cột chứa thông tin mã công ty làm căn cứ đối chiếu)

Hàm MATCH trả về kết quả là 3, tương ứng với dòng thứ 3

=MATCH(H3,A3:A7,0)

Như vậy giao điểm của dòng 3, cột 2 chính là ô B5 (bởi bảng bắt đầu tính từ dòng 3 trở đi, nên dòng 5 ứng với vị trí dòng thứ 3 của bảng đơn giá)

Bước 3: Xác định kết quả đơn giá cần tìm với hàm INDEX

Việc còn lại là dùng hàm INDEX để kết nối giữa Dòng và Cột đã xác định được ở trên.

Thay vì tham chiếu tới 1 cột cố định tại tham số đầu tiên, chúng ta tham chiếu cho cả vùng bảng A3:E7.

Trong bảng này, số dòng xác định bởi hàm MATCH tại ô I3, số cột xác định bởi hàm MATCH tại ô I2.

Kết quả của hàm INDEX là giao điểm giữa số dòng và số cột đã xác định được.

=INDEX(A3:E7,I3,I2)

Như vậy thông qua 4 bài tập ví dụ, chúng ta đã có thể hình dung được cách sử dụng hàm INDEX kết hợp hàm MATCH để thực hiện các yêu cầu dò tìm, tham chiếu, tìm kiếm trong Excel. Khi ứng dụng tốt cách làm này, bạn hoàn toàn có thể thực hiện được những yêu cầu tìm kiếm phức tạp mà hàm VLOOKUP khó đáp ứng được.

Thay vì phải đắn đo sử dụng hàm VLOOKUP hay HLOOKUP, làm công thức dài dòng khi phải kết hợp VLOOKUP với hàm MATCH, HLOOKUP với hàm MATCH, thì chúng ta chỉ cần dùng INDEX kết hợp MATCH là xong.

Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel Phân biệt hàm VLOOKUP với HLOOKUP và bí quyết sử dụng hàm Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp