Bạn đang xem bài viết Hướng Dẫn Giải Bài Tập Excel Có Dùng Hàmvlookup, Hàm If, Hàm Sum được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Bài tập ứng dụng hàm IF, hàm VLOOKUP, hàm SUM trong Excel
(Các bạn có thể tải file bài tập kèm theo đáp án theo link tải ở bên dưới bài viết này)
Xác định đơn giá các sản phẩm trong vùng B2:B7 dựa theo bảng kê sản phẩm
Tính kết quả cột Khuyến mại (vùng E2:E7)
Tính kết quả cột Thành tiền (vùng F2:F7)
Tính tổng tiền phải trả tại ô F8
Trong bài tập này chúng ta có 4 yêu cầu như sau:
Dùng hàm VLOOKUP tìm đơn giá sản phẩm
Bạn phải tìm một cái gì đó
Bạn tìm dựa theo 1 bảng nào đó
Với yêu cầu đầu tiên, bạn sẽ phải xác định đơn giá sản phẩm dựa theo bảng kê các sản phẩm ở phía dưới. Thủ thuật ở đây là:
Cú pháp của hàm VLOOKUP là:
=VLOOKUP( Tìm_dựa_theo_cái_gì, Tìm_ở_đâu, Cột_chứa_kết_quả, Phương_pháp_tìm_kiếm)
Tìm dựa theo cái gì? Đó chính là mã sản phẩm
Tìm ở đâu? Căn cứ theo Bảng kê sản phẩm: vùng bảng A12:C17, trong đó cột đầu tiên trong bảng chứa giá trị tìm kiếm. Chú ý cần phải cố định vùng bảng này.
Cột chứa kết quả là cột thứ mấy trong bảng? Ở đây cần xác định đơn giá, do đó cột chứa kết quả là cột thứ 3 trong bảng
Phương pháp tìm kiếm? Ở đây tìm theo đúng mã sản phẩm, do đó phương pháp tìm kiếm là Exact (tìm chính xác)
Như vậy chúng ta thấy:
Bạn có thể viết công thức VLOOKUP cho ô D2 như sau:
VLOOKUP(A2, $A$12:$C$17, 3, 0)
Kết quả thu được như sau:
Bước 1: Chọn vùng ô D2:D7
Bước 2: nhấn tổ hợp phím Ctrl + D
Khi đó chúng ta chỉ cần kéo công thức tại ô D2 xuống các ô trong vùng D2:D7 bằng cách sử dụng kỹ thuật Filldown như sau:
Kết quả:
Số lượng từ 1 tới 100: không được khuyến mại (bằng 0)
Số lượng từ 101 tới 230: khuyến mại giảm 2% tổng tiền
Số lượng từ 231 tới 300: khuyến mại giảm 3% tổng tiền
Số lượng từ 301 trở lên: khuyến mại giảm 4% tổng tiền
Dùng hàm IF để tính khuyến mãi
Trong câu tiếp theo, các bạn hãy chú ý đề bài yêu cầu xác định kết quả khuyến mại dựa theo một loạt những yêu cầu:
Chúng ta có thể hiểu là: Nếu cột số lượng có giá trị từ 1 tới 100 (hay là nhỏ hơn hoặc bằng 100) thì khuyến mại bằng 0%
Bạn có thể sử dụng hàm IF để biện luận logic này như sau:
hàm IF thứ 1 =IF(C2<=100, 0%, xét trường hợp lớn hơn 100)
Tương tự như vậy chúng ta có ở mốc số lượng nhỏ hơn hoặc bằng 230 như sau:
hàm IF thứ 2 =IF(C2<=230, 2%, xét trường hợp lớn hơn 230)
Trong trường hợp nhỏ hơn hoặc bằng 300, chúng ta có:
hàm IF thứ 3 =IF(C2<=300, 3%, xét trường hợp lớn hơn 300)
hàm IF thứ 1 viết trước
hàm IF thứ 2 sẽ đặt vào vị trí tham số thứ 3 của hàm IF thứ 1
hàm IF thứ 3 sẽ đặt vào vị trí tham số thứ 3 của hàm IF thứ 2
Và trường hợp lớn hơn 300 thì nhận giá trị khuyến mại là 4%
Tới đây bạn có thể lồng ghép các hàm IF vào nhau theo cách:
kết quả:
=IF(C2<=100, 0%, IF(C2<=230, 2%, IF(C2<=300, 3%, 4%)))
Ở đây chúng ta sử dụng 3 hàm IF nên sẽ cần sử dụng 3 dấu đóng ngoặc
Dùng các phép tính toán học để tính cho cột Thành tiền
Ở yêu cầu thứ 3, chúng ta chỉ cần áp dụng các phép tính toán học là cộng, trừ, nhân, chia để viết lại cho biểu thức tính kết quả thành tiền như sau:
Thành tiền = Số lượng * Đơn giá * (1 – Khuyến mại)
Các phép tính nhân chia được thực hiện trước
Các phép tính cộng trừ sau.
Nếu phép tính cộng trừ muốn tính trước thì phải đặt biểu thức tính trong dấu ngoặc đơn.
Viết công thức trong ô F2 như sau:
F2 = C2 * D2 * (1-E2)
Chọn ô F8 (là ô muốn dùng hàm SUM)
Bấm tổ hợp phím tắt ALT và dấu bằng (=)
Chú ý khi viết biểu thức tính này các bạn cần đảm bảo nguyên tắc:
Kết quả là:
Dùng hàm SUM tính kết quả tổng số tiền
Có một cách rất đơn giản để viết hàm SUM đó là các bạn sử dụng chức năng AutoSum bằng cách:
Bạn muốn tìm hiểu thêm kiến thức về Excel và ứng dụng Excel vào công việc? Hãy tham gia ngay khóa học EXG01 – Excel từ cơ bản tới nâng cao dành cho người đi làm của chúng tôi Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống: về các hàm, các công cụ trong excel. Ngoài ra khóa học cũng có nhiều bài tập để bạn thực hành. Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Bạn có thể tải file bài tập sử dụng trong bài viết này tại đường link ở phía cuối bài viết.
Tài liệu kèm theo bài viết
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
Hướng Dẫn Cách Dùng Hàm Index Và Match Qua Bài Tập Có Lời Giải
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
Bài Tập Mẫu Hàm Countif Và Countifs Trong Excel (Có Lời Giải)
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.
Cách Sử Dụng Hàm Vlookup Có Bài Tập Và Lời Giải
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
Cập nhật thông tin chi tiết về Hướng Dẫn Giải Bài Tập Excel Có Dùng Hàmvlookup, Hàm If, Hàm Sum trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!