BÀI TẬP EXCEL NÂNG CAO (DÙNG THI CCUD CNTT NÂNG CAO)

 BÀI TẬP EXCEL NÂNG CAO

Bài 1:

Tập tin DLThiExcel.xlsx

Hướng dẫn

1. Xác thực dữ liệu: 

        Tháng: nhập số nguyên từ 1 đến 12

         Số ngày nghỉ phép: Nhập số nguyên không quá 5.

2. Tên công việc: chọn trong danh sách trên sheet Bangdo. Lưu ý: danh sách công việc được cập nhật thêm tự động.

3.Hệ số lương (Dùng công thức mảng): Thâm niên từ 5 đến 9 năm là 1.25; Thâm niên từ 10 năm trở lên là 1.3; Còn lại là 1.15. Trong đó thâm niên được tính từ ngày vào làm cho đến hiện tại

Excel 2019:

 =IF(DATEDIF(D4,DATE(2013,$F$2,1),"y")>=10,1.3,IF(DATEDIF(D4,DATE(2013,$F$2,1),"y")<5,1.15,1.25))

Excel 2016:

=IF(DATEDIF(D4:D18,DATE(2013,F2,1),"y")>=10,1.3,IF(DATEDIF(D4:D18,DATE(2013,F2,1),"y")<5,1.15,1.25))

4. Đơn giá công (dùng công thức mảng): Dựa vào thông tin Tên công việc và bậc để dò tìm trong các bảng trên sheet Bangdo. Trong đó, ký tự cuối của Mã nhân viên là bậc.

=INDEX(bangdo!$B$3:$D$7,MATCH(chitiet!$E$4:$E$18,bangdo!$A$3:$A$7,0),MATCH(RIGHT(chitiet!$B$4:$B$18)*1,bangdo!$B$2:$D$2,0))

5.  Số ngày làm (Dùng hàm Weekday & EOMONTH): được tính bằng số ngày trong tháng trừ chosố ngày chủ nhật trong tháng. Trong đó: Int((Ngày cuối tháng – ngày đầu tháng – Weekday(cuối tháng)+8)/7) = số ngày chủ nhật trong tháng

=DAY(EOMONTH(D4,0)-D4)+1-INT((EOMONTH(D4,0)-D4-WEEKDAY(EOMONTH(D4,0))+8)/7)

6.  Thưởng / phạt: Nếu không nghỉ ngày nào thưởng 200,000; Nghỉ từ 1 đến 3 ngày phạt 50,000 * số ngày nghỉ; Nghỉ >3 ngày phạt 100,000 * số ngày nghỉ. Trong đó: Thưởng: số dương, Phạt: số âm.

=IF(H4=0,200000,IF(H4>3,-100000*H4,-50000*H4))

7. Lương: Số ngày làm * Đơn giá công * Hệ số lương + Thưởng / Phạt. Định dạng tất cả các cột tiền là 1,000 đồng.

=I4*G4*F4+J4

8. Dùng Conditional Formatting tô nền tím – chữ trắng cho những dòng được thưởng, nền vàng – chữđỏ những dòng bị phạt.

9.

10.

 Bài 2:

Dữ liệu Excel

1.

Loại hàng

Tên hàng

=INDIRECT(INDEX(LoaiHang!$A$3:$B$7,MATCH($B4,LoaiHang!$B$3:$B$7,0),1))

Ngày bán

=AND(MONTH(D5=11),YEAR(D5)=2011,D5>=D4)

Số lượng bán:

=SUMIF($C$4:$C$15,C4,$G$4:$G$15)<=VLOOKUP(C4,DanhMucHang!$B$5:$E$78,4,0)

Định dạng theo điều kiện: Tô màu nền xanh chữ đỏ cho những dòng không nhập ngày bán;

Đơn vị tính

=VLOOKUP(C4,DanhMucHang!$B$5:$D$78,3,0)

Xuất xứ

=VLOOKUP(C4,DanhMucHang!$B$5:$C$78,2,0)

Đơn giá

=VLOOKUP(C4,DanhMucHang!$B$5:$G$78,IF(BaiThi!F4="Việt Nam",5,6),0)

Thành tiền

=G4:G15*H4:H15*IF((G4:G15>10)+(DAY(D4:D15)<=15),0.9,1)

 Bài 3:                                                                    

Tập tin DuLieuThiExcel.xlsx

✅ Fomulas                                                                            

✅ Nhóm thuốc: là danh sách chọn từ List nhóm thuốc trong Sheet NhomThuoc;

✅ Tên thuốc: là danh sách chọn dựa trên từng nhóm thuốc trong từng sheet tương ứng;

=INDIRECT(INDEX(NhomThuoc!$A$3:$A$6,MATCH($B4,NhomThuoc,0),1))

✅ Ngày bán: sáu tháng cuối năm 2011, ngày nhập sau phải lớn hơn ngày nhập trước.

=AND(MONTH(E5)>=6,MONTH(E5)<=12,YEAR(E5)=2011,E5>E4)

✅ Số lượng: được nhập sao cho tổng số lượng nhỏ hơn số lượng từng loại tương ứng.

=SUMIF($C$4:$C$14,C4,$G$4:$G$14)<VLOOKUP(C4,INDIRECT("Bang"&INDEX(NhomThuoc!$A$3:$A$6,MATCH($B4,NhomThuoc,0),1)),5,0)

✅ Định dạng có điều kiện: Tô màu cam những dòng không nhập ngày bán;

✅ Nhà sản xuất, đơn vị, đơn giá bán: dựa trên Tên thuốc tra trong từng sheet tương ứng. Riêng đối với đơn giá bán thì tăng 30%.

Nhà sản xuất 

=VLOOKUP(C4,INDIRECT("Bang"&INDEX(NhomThuoc!$A$3:$A$6,MATCH($B4,NhomThuoc,0),1)),3,0)

đơn vị 

=VLOOKUP(C4,INDIRECT("Bang"&INDEX(NhomThuoc!$A$3:$A$6,MATCH($B4,NhomThuoc,0),1)),4,0)

đơn giá bán

=VLOOKUP(C4,INDIRECT("Bang"&INDEX(NhomThuoc!$A$3:$A$6,MATCH($B4,NhomThuoc,0),1)),6,0)*130%

✅ Thành tiền (dùng công thức mảng):Số lượng*Đơn giá. Nếu SL trong kho còn >100 thì giảm 10%, Nếu số lượng bán lớn hơn 5 hoặc thuộc nhóm thuốc tim mạch thì giảm 5%. Ngược lại không giảm.( chú ý: nếu đơn hàng thỏa cả 2 điều kiện giảm thì chỉ được nhận giảm 1 lần cao nhất)

=G4:G14*H4:H14*IF(K4:K14>100,0.9,IF((G4:G14>5)+(B4:B14="Tim mạch"),0.95,1))

✅ Dùng Consolidate thống kê Tổng Số lượng và Tổng Thành tiền theo Nhóm thuốc (cho phép cập nhật lạikhi dữ liệu nguồn thay đổi).

✅ Dựa trên số liệu thống kê vẽ biểu đồ theo mẫu trên.

 Bài 4:

DỮ LIỆU EXCEL

1) Tạo list động cho Tên loại trong sheet DanhMuc. (Tạo Define Name)

2) Ngày nhập: Chỉ cho nhập ngày trong quí 2/2015.

3) Số lượng: được nhập số trong khoảng từ 100 đến 1000.

4) Tên loại: Chỉ được chọn trong bảng Tên loại; Tên rượu: Cho chọn tương ứng với Tên loại

Tên loại

Tên rượu

5) Đơn giá: dựa vào Tên rượu tra trong sheet DanhMuc (yêu cầu dùng Index và công thức mảng)

=IF(ISNA(INDEX(INDIRECT("DG"&B3:B17),MATCH(C3:C17,INDIRECT(B3:B17),0),1)),"",INDEX(INDIRECT("DG"&B3:B17),MATCH(C3:C17,INDIRECT(B3:B17),0),1))

6) Che lỗi cho những ô có lỗi #N/A

Hàm ISNA

7) Thành tiền = Số lượng * Đơn giá. Nếu Tên loại là VangUc và có số lượng dưới 150 thì tăng 3%. (yêu cầu dùng công thức mảng)

=E3:E17*F3:F17*IF(E3:E17<150,103%,1)

8) Tính Tổng số lượng VangChiLe nhập trong tháng 4 (yêu cầu dùng công thức mảng)

=SUM(IF((B3:B17="VangChiLe")*(MONTH(D3:D17)=4),E3:E17,0))

9) Tính thống kê Số lượng nhập của từng loại Rượu theo Kho (dùng DataTable kết hợp cùng công thức mảng)

=TABLE(I4,H4)

 Bài 5:

DỮ LIỆU EXCEL

Tạo Define Name

1) Loại hàng: Cho phép chọn những loại hàng có trong bảng danh mục (không nhập trực tiếp).

2) Tên hàng: Cho phép chọn ứng với từng loại hàng.

3) Ngày bán: Cho phép nhập dạng ngày thuộc 6 tháng cuối năm 2015, ngày nhập sau phải lớn hơn ngày nhập trước đó và không được bỏ trống.

Ô đầu

Các ô còn lại

4) Số lượng: Chỉ cho phép nhập số từ 1 --> 10.

5) TG bảo hành: Dùng hàm dò tìm kết hợp với Indirect tra vào các bảng để lấy TG bảo hành tương ứng với sản phẩm của từng Hãng (Sử dụng công thức mảng).

6) Đơn giá : Dùng hàm Index dò tìm kết hợp với Indirect, Match tra vào các bảng để lấy Đơn giá tương ứng với sản phẩm của từng loại (Sử dụng công thức mảng).

=INDEX(INDIRECT("DG"&B3:B12),1,MATCH(C3:C12,INDIRECT("TH"&B3:B12),0))

7) Thành tiền = Số lượng * Đơn giá. Giảm 10% thành tiền cho khách hàng mua loại hàng gia dụng trong ngày 02/9/2014 với số lượng>=2 (sử dụng công thức mảng).

=E3:E12*F3:F12*IF((B3:B12="DOGIADUNG")*(D3:D12=DATE(2015,9,2))*(E3:E12>=2),0.9,1)

8) Sử dụng chức năng Conditional Formatting tô màu nền cho những dòng sản phẩm được bán trong ngày 02/9/2014.

9) Tại Sheet Thong_Ke Sử dụng Data Table 2 biến kết hợp công thức mảng thống kê thành tiền theo mẫu cho sẵn.

=TABLE(K5,J5)

 Bài 6:

DỮ LIỆU EXCEL

Tạo Name Manager

1. Loại: Cho phép chọn những loại hàng có trong bảng danh mục (không nhập trực tiếp).

=Danh_Muc!$C$3:$C$5

2. Mã hàng: Cho phép chọn ứng với từng loại hàng.

=INDIRECT("MS"&$B4)

3. Tên hàng: Dựa vào mã hàng sử dụng hàm dò tìm kết hợp với Indirect để lấy tên hàng tương ứng (sử

dụng công thức mảng).

=HLOOKUP(D4:D12,INDIRECT(B4:B12),2,0)

4. Ngày bán cho phép nhập trong 6 tháng đầu năm 2012. Số lượng nhập kiểu số nguyên từ 1  50

5.Đơn giá: Sử dụng hàm Index kết hợp với Indirect tra tìm lấy đơn giá (sử dụng công thức mảng).

=INDEX(INDIRECT(B4:B12),3,MATCH(D4:D12,INDIRECT("MS"&B4:B12),0))

6. Thành tiền = Số lượng * Đơn giá. Nếu Loại KSKIDS bán với số lượng từ 10 trở lên thì được giảm 5% trên thành tiền (sử dụng công thức mảng).

=F4:F12*G4:G12*IF((B4:B12="KSKIDS")*(F4:F12>=10),0.95,1)

7. Tính tổng thành tiền và dựa vào hàm VND trong tập tin Doiso.Xla hãy đổi Thành tiền ra dạng chữ .

8. Thực hiện thống kê theo mẫu sau:

=DMIN(A3:H12,F3,J3:K4)

=DAVERAGE(A3:H12,F3,J3:K4)

9. Sử dụng Conditional Formatting tô màu nền cho những dòng sản phẩm được giảm giá thành tiền.

 Bài 7:

 Bài 8:

 Bài 9:

 Bài 10:

 Bài 11: