20+ bài tập excel từ cơ bản đến nâng cao có đáp án!

Phần 1:Các dạng bài tập với các Hàm Cơ Bản

1 Bài tập về phép cộng nhân cơ bản
Cho ví dụ sau:

 

Yêu cầu: 
1 Dựa vào Số Báo Danh của mỗi thí sinh và tra ở Bảng Tra Điểm Thi hãy xác định giá trị cho các cột điểm toán lý hóa
2 Xác định Điểm ưu tiên cho mỗi thí sinh, biết rằng: 
 – Nếu thuộc diện chính sách là CLS (Con liệt sỹ) thì được 1,5 điểm
– Nếu thuộc diện chính sách là CTB (Con thương binh) thì được 1 điểm
– Nếu thuộc diện chính sách là MN (Miền núi) thì được 0,5 điểm
– Còn ngoài ra không thuộc các diện thì 0 điểm
3 Tính Tổng Điểm biết rằng Tổng Điểm = Điểm Toán + Điểm Hoá  + Điểm Lý + Điểm ưu tiên
4 Xác định giá trị cho cột Kết Quả, biết rằng : Nếu: Tổng Điểm >= Điểm Chuẩn thì ghi là “Đậu”, còn ngược lại thì ghi là “Rớt”, trong đó Điểm Chuẩn của mỗi trường thì tra theo Bảng Điểm Chuẩn,dựa vào giá trị ở cột Mã Trường

2 Bài tập sử dụng hàm ABS

Câu hỏi: Tính giá trị tuyệt đối của điểm số khi nhập vào
Cách giải: Sử dụng hàm ABS để lấy giá trị tuyệt đối của số, tại ô D8 ta gõ công thức =ABS(C8)
3 Bài tập sử dụng hàm ACCRINTM

 

Yêu cầu: Tính tiền lãi cộng dồn cho chứng khoán trả lãi khi đáo hạn
Giải: Sử dụng hàm ACCRINTM nhập công thức =ACCRINTM(C3,C4,C5,C6,C7)
4 Bài tập sử dụng hàm AND

Yêu cầu: Kiểm tra giới tính và họ tên của từng người có chính xác không
Giải: Sử dụng hàm AND để kiểm tra giới tính của từng người, ta gõ công thức =AND(B8=”Lê Xuân Lâm”,C8=”Nam”).
Nếu đúng trả về kết quả TRUE, nếu sai là FALSE
Tương tự kiểm tra các tên khác.
5 Bài tập sử dụng hàm AVERAGE

 

Yêu cầu: Tính điểm trung bình của từng học sinh
Giải: Sử dụng hàm AVERAGE để tính điểm trung bình, tại ô I8 ta gõ công thức =AVERAGE(C8:H8)
6 Bài tập sử dụng hàm CONFIDENCE

Yêu cầu: Tính độ tin cậy của bảng trên
Giải: Tại ô ghi kết quả bạn nhập công thức  =CONFIDENCE(C8,C9,C10).

7 Bài tập sử dụng hàm COUNT

Yêu cầu: 
1.Tìm số học sinh thi đậu trong danh sách trên
2. Tìm số ô trống trong bảng
3. Tìm số ô chứa dữ liệu trong bảng
4. Tìm số học sinh Nam trong bảng
Giải: 
1.Sử dụng hàm COUNT để tìm số học sinh thi đậu, ta có công thức =COUNT(C9:C15)
2. Dùng hàm COUNTBLANK để đếm số ô trống trong bảng=COUNTBLANK(A8:C15)
3. Dùng hàm  Tính thời hạn hiệu lực của một trái phiếu có ngày kết toán là 01/01/2008, ngày đáo hạn là 01/01/2016, biết lãi suất hằng năm là 8%, lợi nhuận hằng năm là 9%, trả lãi 6 tháng một lần, với cơ sở để tính ngày là bình thường (theo thực tế ngày tháng năm)COUNTA để đếm ô chứa dữ liệu trong bảng=COUNTA(A8:C15)
4. Dùng hàm COUNTIF để đếm số học sinh Nam=COUNTIF(B9:B15,”Nam”)
8 Bài tập sử dụng hàm COUPPCD

Yêu cầu: Tính ngày phiếu lãi trước đó trước ngày kết toán
Giải: Nhập hàm =COUPPCD(C5,C6,C7,C8) tại ô cần ghi kết quả. 
9 Bài tập sử dụng hàm CUMIPMT

Yêu cầu: tính số tiền lãi cộng dồn trong tháng thứ nhất
Giải: Tại ô cần ghi kết quả, nhập câu lệnh hàm như sau: =CUMIPMT(C5,C6,C7,1,1,0)
10 Bài tập sử dụng hàm CUMPRINC

 

Yêu cầu : Tính số nợ gốc phải trả trong tháng thứ nhất
Giải: Tại ô cần ghi kết quả, nhập câu lệnh hàm như sau: =CUMPRINC(C5,C6,C7,1,1,0)
11: Bài tập sử dụng hàm DAVERAGE

Yêu cầu: 
1. Tính thực lãnh bằng lương cơ bản *4
2. Tính lương trung bình của phòng kỹ thuật, dựa theo bảng điều kiện
3. Tìm giá trị lương cao nhất trong phòng Kỹ thuật
4. Tìm giá trị lương thấp nhất trong phòng Kỹ thuật
Giải:
1. Tại ô G9 ta điền công thức tính là G9= F9*4
2. Tính lương trung bình của nhân viên Kỹ thuật, sử dụng hàm DAVERAGE. Tại ô G19 điền công thức=DAVERAGE($E$8:$G$18,G8,F21:F22)
3. Tại ô G20 điền công thức =DMAX(D8:G19,G8,F22:F23) để tính lương cao nhất phòng Kỹ thuật.
4. Tương tự sử dụng hàm DMIN để tìm lương thấp nhất =DMIN(E8:G18,G8,F22:F23).
12 Bài tập sử dụng hàm DURATION()

Ví dụ:Tính thời hạn hiệu lực của một trái phiếu có ngày kết toán là 01/01/2008, ngày đáo hạn là 01/01/2016, biết lãi suất hằng năm là 8%, lợi nhuận hằng năm là 9%, trả lãi 6 tháng một lần, với cơ sở để tính ngày là bình thường (theo thực tế ngày tháng năm)

Giải: 
Thời hạn hiệu lực là trung bình trọng giá trị hiện tại của dòng luân chuyển tiền mặt và được dùng làm thước đo về sự phản hồi làm thay đổi lợi nhuận của giá trị một chứng khoán.
Cú pháp: = DURATION(settlement, maturity, coupon, yld, frequency, basis)
Settlement: Ngày kết toán chứng khoán, là một ngày sau ngày phát hành chứng khoán, khi chứng khoán được giao dịch với người mua.
Maturity: Ngày đáo hạn chứng khoán, là ngày chứng khoán hết hiệu lực.
Coupon: Lãi suất hằng năm của chứng khoán.
Yld: Lợi nhuận hằng năm của chứng khoán.
Frequency: Số lần trả lãi hằng năm. Nếu trả mỗi năm một lần: frequency = 1; trả mỗi năm hai lần: frequency = 2; trả mỗi năm bốn lần: frequency = 4.
Basis : Là cơ sở dùng để đếm ngày (nếu bỏ qua, mặc định là 0)
= 0 : Một tháng có 30 ngày / Một năm có 360 ngày (theo tiêu chuẩn Bắc Mỹ)
= 1 : Số ngày thực tế của mỗi tháng / Số ngày thực tế của mỗi năm
= 2 : Số ngày thực tế của mỗi tháng / Một năm có 360 ngày
= 3 : Số ngày thực tế của mỗi tháng / Một năm có 365 ngày
= 4 : Một tháng có 30 ngày / Một năm có 360 ngày (theo tiêu chuẩn Châu Âu)
13: Bài tập sử dụng hàm INT

Yêu cầu: Tính điểm trung bình môn của 3 môn thi, kết quả trả về số nguyên.
Giải thích: Sử dụng hàm INT để lấy kết quả số nguyên của phép tính điểm trung bình, tại ô F10 ta gõ công thức=INT((C10+D10+E10)/3).
14 Bài tập sử dụng hàm LEFT

Yêu cầu: Tìm tên lớp của học sinh, biết rằng số lớp là 1 kí tự đầu của số báo danh
Giải: Sử dụng hàm Left để điền tên lớp cho học sinh, tại ô E8 gõ công thức =LEFT(B8,1).
15 Bài tập sử dụng hàm MAX

Yêu cầu: 
Câu 1: Điền vào cột Thực lãnh với lương cơ bản * 4
Câu 2: Tìm giá trị lương được nhận cao nhất
Giải thích:
Câu 1: Tính thực lãnh tại ô E9 điền công thức =D9*4
Câu 2 tìm giá trị lương lớn nhất sử dụng hàm MAX, tại ô E19 điền công thức =MAX(E9:E18).
17 Bài tập sử dụng hàm MIN

Yêu cầu: 
Câu 1: Điền vào cột Thực lãnh với lương cơ bản * 4. 
Câu 2: Tìm giá trị lương được nhận thấp nhất
Giải thích: 
Câu 1: Tính thực lãnh tại ô E9 điền công thức =D9*4
Câu 2 tìm giá trị lương lớn nhất sử dụng hàm MIN, tại ô E19 điền công thức =MIN(E9:E18).
17 Bài tập sử dụng hàm MOD

Yêu cầu: Tìm số dư của phép chia số thứ nhất chia cho số thứ 2
Giải thích: Sử dụng hàm MOD để tìm số dư của phép chia, tại ô D9 ta gõ công thức=MOD(B9,C9)
18 Bài tập  sử dụng hàm PRODUCT

Yêu cầu: Tính thực lĩnh của nhân viên biết rằng Thực lĩnh=Ngày công *Lương ngày
Giải thích: Sử dụng hàm    PRODUCT để tính Thực lĩnh, tại ô E9 điền công thức=PRODUCT(C9:D9)

19: Bài tập sử dụng hàm SUM

Yêu cầu: Tính tổng điểm các môn học của từng học sinh
Giải thích: Sử dụng hàm SUM để tính tổng điểm, ta gõ công thức tại ô G10=SUM(C10:F10)
20:Bài tập sử dụng hàm SUMIF 

Yêu cầu: 
Câu 1 Tính thực lãnh theo lương cơ bản*5
Câu 2: Tính lương của từng phòng Kinh doanh, Kỹ Thuật, Kế toán
Giải thích:
1.Tính thực lãnh tại ô G9 ta gõ công thức =F9*5
2. Tính lương theo từng phòng sử dụng hàm SUMIF, tại ô F20 gõ công thức =SUMIF(E9:E18,”Kinh doanh”,G9:G18)
tương tự tính lương cho phòng Kế toán là=SUMIF(E9:E18,”Kế toán”,G9:G18)
lương cho phòng Kỹ thuật là =SUMIF(E9:E18,”Kỹ thuật”,G9:G18)

1 Bài tập hàm HLOOKUP kết hợp hàm IF

Yêu cầu: 
Câu 1: Căn cứ vào Ngày Đăng Ký Học của Học viên hãy xác định Ngày Bắt Đầu Học cho Học viên, biết rằng:
Học viên đăng ký trúng vào buổi học nào (2-4-6 hoặc 3-5-7) thì học vào buổi đó và Ngày Bắt Đầu Học là ngày của buổi kế tiếp.
Ví dụ :  – Ngày Đăng Ký là : 26/03/2014 tức là Thứ Hai thì Ngày Bắt Đầu Học là : 28/03/2014 tức là Thứ Tư.
 – Ngày Đăng Ký là : 20/07/2014 tức là Thứ Năm thì Ngày Bắt Đầu Học là : 22/07/2014 tức là Thứ Bảy.
 – Trường hợp đăng ký trúng Chủ Nhật thì Ngày Bắt Đầu Học là Ngày Thứ Ba của tuần kế tiếp.
Câu 2: Điền giá trị cho cột Ngày Thi Tốt Nghiệp dựa vào Ngày Bắt Đầu Học và Thời Gian Học, biết rằng Thời Gian Học của mỗi cấp độ thì dựa vào ký tự đầu tiên bên trái của Mã Học Viên và tra trong Bảng Tra Thời Gian và Học Phí.
Câu 3: Điền giá trị cho cột Hạn Cuối Nộp Học Phí biết rằng học viên thi Tốt nghiệp Tháng nào thì Hạn Cuối.
Nộp Học Phí là Ngày cuối cùng của Tháng trước đó.
Ví dụ: Ngày Thi Tốt Nghiệp là : 12/07/2014 thì Hạn Cuối Nộp Học Phí là : 30/06/2014.
Câu 4:
Tạo giá trị cho cột Ghi Chú: Nếu Học viên nào nộp đủ học phí và trước Hạn Cuối Nộp Học Phí thì ghi “Được Thi” ngược lại thì để trống
Trong đó : Học phí của mỗi cấp độ thì dựa vào ký tự đầu tiên bên trái của Mã Học Viên và tra trong Bảng Tra Tra Thời Gian và Học Phí.
Giải:
1: Điền vào công thức cột ngày bắt đầu học, sử dụng hàm IF và hàm WEEKDAY để điền vào cột như 
2: Sử dụng hàm Hlookup và các hàm liên quan để điền ngày thi tốt nghiệp, tại ô E11 ta nhập công thức =EDATE(D11,HLOOKUP(LEFT(B11,1),$D$24:$F$26,2,FALSE))
3 Hạn cuối nộp học phí, sử dụng hàm lấy tháng H11 =EOMONTH(E11,-1)
4 Sử dụng hàm hlookup kết hợp hàm IF để tìm xem những học viên nào được thi tại ô I11 ta điền công thức =IF(AND(HLOOKUP(LEFT(B11,1),$C$24:$F$26,3,FALSE)=F11,G11

2 .Bài tập sử dụng hàm Hlookup kết hợp hàm IF
Ta có bảng 1 và 2 như dưới đây:

Yêu cầu: 
Câu 1: Điền số liệu cho cột Tên Sản Phẩm dựa vào 2 ký tự bên trái của Mã Sản Phẩm ở Bảng 1 và tra ở Bảng 2
Câu 2: Tính Phụ Cấp bằng công thức:
Phụ Cấp = Số Lượng * Đơn Giá Hoàn Thành Một Sản Phẩm
Trong đó : Đơn Giá cho mỗi đơn vị sản phẩm được tra trong Bảng 2 dựa vào Mã Sản Phẩm ở Bảng 1 và ký tự đầu tiên bên phải của Mã Sản Phẩm quy định Loại sản phẩm.
Câu 3 Tính Tiền Thưởng cho mỗi công nhân theo quy định sau: 
Số Lượng Sản Phẩm Hoàn Thành:
Từ 0 đến 500 thưởng 0
Từ 500 đến 750 thưởng 100000
Trên 750 thưởng 200000
Câu 4:Tính Thành Tiền = Mức Lương + Phụ Cấp + Tiền Thưởng
Câu 5:Tính giá trị Tổng Cộng cho các cột Số Lượng, Phụ Cấp, Tiền Thưởng và Thành Tiền
Giải thích:
Câu 1: Sử dụng hàm Hlookup để điền tên sản phẩm, tại ô D14 ta điền công thức =HLOOKUP(LEFT(C14,2),$C$23:$F$24,2,FALSE)
Câu 2:Sử dụng hàm Hlookup kết hợp với Left và Right để điền vào ô G14 =E14*HLOOKUP(LEFT(C14,2),$C$23:$F$26,MATCH(“Loại “&RIGHT(C14,1),$B$23:$B$26,0),FALSE)
Câu 3: Sử dụng các hàm IF lồng nhau kết hợp với hàm AND để tính tiền thưởng, tại ô H14 điền công thức=IF(AND(E14>0,E14<=500),0,IF(AND(E14>500,E14<=750),100000,200000)).
Câu 4:Sử dụng hàm SUM để tính cột thành tiền, tại ô I14 điền công thức =SUM(F14:H14)

3 Bài tập sử dụng hàm Vlookup kết hợp Hlookup

Yêu cầu: 
Câu 1: Căn cứ vào 3 ký tự bên trái và 2 ký tự bên phải của Mã SP trong Bảng 1,hãy tra trong Bảng 2 để điền giá trị cho cột Tên Hàng – Tên Hãng Sản Xuất.
Ví dụ : Mã SP là CDR-SS thì có Tên Hàng – Tên Hãng Sản Xuất là : CDRom – Sam Sung.
Câu 2: Hãy điền Đơn Giá cho mỗi mặt hàng dựa vào Mã SP ở Bảng 1 và tra ở Bảng 2.
Câu 3: Tính Thành Tiền = Số Lượng * Đơn Giá.
Giải thích:
Câu 1: 1. Sử dụng hàm Vlookup kết hợp với Hlookup để điền tên loại hàng hóa ta gõ công thức =+VLOOKUP(LEFT(B9,3),$B$19:$C$24,2,FALSE)&” – “&HLOOKUP(RIGHT(B9,2),$D$20:$F$24,2,FALSE).
Câu 2: 2. Điền vào cột đơn giá, sử dụng hàm Vlookup kết hợp với hàm Match, tại ô E9 gõ công thức =VLOOKUP(LEFT(B9,3),$B$20:$F$24,MATCH(RIGHT(B9,2),$B$20:$F$20,0),FALSE)
Câu 3: 3. Thành tiền G9==E9*F9

Vậy là chúng ta đã có căn bản các dạng bài tập và cách giải chi tiết rồi đó, trong các bài sau chúng ta sẽ cùng tìm hiểu thêm các dạng nữa nếu các bạn chưa hiểu nhé! Cảm ơn vì đã đọc!

Khóa học nổi bật

• 

  THÀNH THẠO Kế toán tổng hợp trên Excel sau 30 ngày – Học xong LÀM NGAY

  Nguyễn Lê Hoàng

  499,000đ800,000đ
• 

  Trở thành cao thủ Excel trong 10 giờ

  Nguyễn Thành Phương

  699,000đ700,000đ
• 

  Thành thạo Excel 2010 trong 3 giờ

  Lê Quang Hải

  599,000đ600,000đ