Bài tập excel – số 2

Bài tập excel – số 2 Bài 1: YÊU CẦU TÍNH: 1. Tính SỐ NGÀY THUÊ = NGÀY TRẢ – NGÀY THUÊ (Lưu ý: Có thể gặp dạng “lạ” khi tính toán với dử liệu kiểu ngày (05-02-00 thay vì 36) 2. Tính LoẠI là 1 ký tự cuối của MÃ HÀNG và chuyển qua giá trị số (dùng hàm RIGHT và VALUE) 3. Tính cột TÊN HÀNG dựa theo MÃ HÀNG và BẢNG GIÁ Hướng dẫn: dùng hàm VLOOKUP với giá trị dò là 1 kí tự bên trái của MÃ HÀNG (dùng hàm Left) 4. Tính cột GÍA dựa theo TÊN HÀNG , BẢNG GIÁ VÀ LoẠI GIÁ Hướng dẫn: Dùng hàm VLOOKUP với giá trị dò là TÊN HÀNG, bảng dò, số thừ tự của cột cần đọc thuộc dạng phức tạp phải dùng hàm IF để lựa chọn giữa 2 giá trị cột thứ 2 hay thứ 3) Gợi ý: IF(LoẠI = ?,2,3) 5. Tính cột số lượng tính lại (SL TÍNH LẠI) theo phương thức sau: Nếu SỐ LƯỢNG > 20 thì phần SỐ LƯỢNG >20 được giảm 10% (chỉ tính 90%); ngược lại giữ nguyên SỐ LƯỢNG Gợi ý: IF(SỐ LƯỢNG >?,(SỐ LƯỢNG – 20)*90%+20,?) 6. TIỀN THUÊ = SL TÍNH LẠI * GIÁ * SỐ NGÀY THUÊ Bài 2: YÊU CẦU: 1. Nhập lộ trình vào khối ô C5:C15 Nhập chính xác: Quy Nhơn chứ không phải Quy Nhon… 2. Tính ĐƠN GIÁ: lập công thức lấy đơn giá từ BẢNG ĐƠN GIÁ VÀ THỜI GIAN QUY ĐỊNH (Dùng hàm dò tìm theo cột VLOOKUP với giá trị dò là LỘ TRÌNH) 3. Tính TTAI XE: Dựa vào 2 ký tự đầu của số xe và BẢNG QUY ĐỊNH TRỌNG TẢI Chú ý: Dùng hàm VLOOKUP, với giá trị dò phải là giá trị số của 2 ký tự bên trái của Số xe (dùng hàm VALUE và LEFT) 4. Tính CƯỚC PHÍ: Nếu SL<TTAI XE thì là ĐƠN GIÁ * SL, ngược lại là ĐƠN GIÁ * SL* 105% (tính thêm 5%) 5. Tính Thời gian thực hiện ( TG THỰC HiỆN ) : Nếu NGÀY ĐI = NGÀY ĐẾN thì thời gian thực hiện được tính là 1, ngược lại thì NGÀY ĐẾN – NGÀY ĐI 6. Tính TG QUY ĐỊNH: Dùng VLOOKUP với giá trị dò là LỘ TRÌNH, dò trong BẢNG ĐƠN GIÁ VÀ THỜI GIAN QUY ĐỊNH 7. Tính THƯỞNG: Nếu TG THỰC HiỆN < TG QUY ĐỊNH thì tính là 5% * CƯỚC PHÍ, ngược lại thưởng là 0. 8. Lập BẢNG TỔNG KẾT về SỐ ĐẦU XE và TỔNG CƯỚC PHÍ Bài 3: YÊU CẦU 1. Tính số Kw Tiêu Thụ (Tiêu Thụ ) = Số mới – Số cũ 2. Tính Đơn Gía dựa vào số điện kế (SO DK) và Bảng đơn giá (Ý nghĩa SO DK: Ký tự đầu tiên biểu thị cho Khu vực. Ký tự cuối cho biết Loại đã đăng ký; VD: A001 có khu vực là A, loại 1) Gợi ý: Dùng hàm dò tìm theo hàng, HLOOKUP với giá trị dò là 1 ký tự bên trái của SO DK, Số hàng lấy thuộc dạng phức tạp phải dùng IF(RIGHT(SO DK)=”1″,..,..) 3. Tính tiền trong định mức ( TRONG DM) = Nếu TIEU THU > DINH MUC thì lấy DINH MUC * DON GIA; ngược lại thì lấy TIEU THU * DON GIA ( Có thể dung MIN thay IF) 4. Tính SO LAN VDM : (TIEU THU – DINH MUC) / DINH MUC (không cần làm tròn) 5. Tính HE SO VDM: Được tính dựa vào SO LAN VDM: Có 2 cách: C1: Nếu TIEU THU< DINH MUC thì HE SO VDM = 0, ngược lại Nếu SO LAN VDM =2), hệ số 3 C2: Dò tìm dựa vào Bảng Hệ Số Vượt Định Mức – giá trị dò là SO LAN VDM với kiểu dò là = 1 6. Tính TIEN VDM = (TIEU THU – DINH MUC) * DON GIA * HE 7. Tính Tổng = TRONG DM + TIEN VDM 8. Sắp xếp bảng tính theo DINH MUC tăng dần, trùng DINH MUC thì sắp xếp theo Tổng giảm dần 9. Dùng Advanced Filter lần lượt tạo các vùng dữ liệu chỉ gồm những dòng: a) Có ký tự cuối của SO DK là “1″ b) Có ký tự đầu của SO DK là “A” hay “B” và có TIEN VDM. Bài 4: YÊU CẦU: 1 Tính THANH TIEN = SO LUONG * DON GIA 2. QUY RA USD = THANH TIEN / TY GIA (TỶ GIÁ được dò tìm trong BẢNG TỶ GIÁ VỚI USD ; với giá trị dò là MA NT) 3. Tính TY GIA USD VOI VND: biết: (Nếu NGAY MUA trước ngày 15/10/1992 thì tỷ giá là 11000, ngược lại tỷ giá là 11500) Gợi ý: Giải bằng 1 trong 3 cách sau: a. Dùng hàm DATE: = IF(NGAY MUA<DATE( , , ),…,…) b. Dò tìm tỷ giá với kiểu dò = 1 trong BANG TY GIA USD VOI VND với giá trị dò là NGAY MUA C. So sánh NGAY MUA với NGAY CHUAN: =IF(NGAY MUA<NGAY CHUAN,…,…); cố định ô NGAY CHUAN 4. Tính TTVN ( Thành tiền VN) = QUY RA USD * TY GIA USD VOI VND 5. Tính THUẾ = Tỷ lệ theo loại hàng * TTVN (Tỷ lệ theo loại hàng được dò tìm dựa vào 1 ký tự bên trái của MA SO và BẢNG TỶ LỆ THUẾ) 6. Sử dụng chức năng Advanced Filter, lần lượt tạo vùng dữ liệu chỉ gồm những dòng có: a. Mã ngoại tệ là JPY và có ký tự cuối của Mã số là 2 (dùng ký tự đại diện là *) b. 1 ký tự bên trái của Mã số là B hoặc Mã ngoại tệ là FF C. Ngày mua trước 15/10/1992 và số lượng >10 7. Sắp xếp danh sách theo NGAY MUA tăng dần, trùng NGAY MUA sắp theo THANH TIEN tăng dần Bài 5: YÊU CẦU TÍNH: 1. TÊN HÀNG: Dựa vào 2 ký tự đầu của MÃ HĐ, dò tìm trong BẢNG 1 2. SỐ LƯỢNG (SL): Là các ký tự số sau ký tự “-” của MÃ HĐ và chuyển sang kiểu số 3. Sắp xếp: Sắp xếp BẢNG THANH TOÁN theo cột TÊN HÀNG tăng dần, nếu trùng thì sắp theo cột SỐ LƯỢNG giảm dần 4. ĐƠN GIÁ: Dựa vào TÊN HÀNG, dò tìm trong BẢNG 1, biết nếu NGÀY MUA = NGÀY KHAI TRƯƠNG thì lấy giá trong cột GIÁ 2, ngược lại lấy giá trong cột GÍA 1 5. THÀNH TiỀN = ĐƠN GIÁ * SL TÍNH LẠI SL TÍNH LẠI = Nếu SL > 100 thì phần SL trên 100 được tính còn 90%, ngược lại giữ nguyên là SL 6. Lập công thức điền dữ liệu vào cột TỔNG THÀNH TiỀN – BẢNG 3 7. THUẾ = THÀNH TiỀN * THUẾ SuẤT với : Nếu TÊN HÀNG là BIA CHAI hoặc BIA LON, thì thuế suất được dò tìm trong BẢNG 2, với giá trị dò là THÀNH TiỀN, Ngược lại các mặt hàng khác thuế suất bằng 0 8. Dùng Advanced Filter để trích ra các dòng có TÊN HÀNG là BÁNH MÌ hay BÁNH NGỌT và có NGÀY MUA là 23/8/2002 9. Dùng Advanced Filter để trích ra các dòng có đơn giá được lấy trong cột GIÁ 2 Bài 6: YÊU CẦU 1. Ý nghĩa của MÃ NV: Ký tự đầu là MÃ CV, ký tự thứ 2,3 chỉ số năm công tác, ký tự thứ 4 (D hay T) D là dài hạn, T tạm tuyển. 2. a) Đổi tên sheet thành TINH THUE b) Điền thứ tự cột cho cột STT 3. CHỨC VỤ: dựa vào ký tự đầu của MÃ NV và dò trong BẢNG 1 4. PHỤ CẤP: dựa vào ký tự đầu của MÃ NV và dò trong BẢNG 1 5. HS LƯƠNG: nếu ký tự thứ 4 của MÃ NV là “T” thì HS LƯƠNG là 0.75, ngược lại dò tìm trong BẢNG 2, với giá trị dò là 2 ký tự ( thứ 2, thứ 3 từ trái qua của MÃ NV) 6. THU NHẬP: = LƯƠNG + PHỤ CẤP trong đó LƯƠNG = LCB * HS LƯƠNG * NGÀY CÔNG Nếu NGÀY CÔNG >22 thì phần NGÀY CÔNG vượt trên 22 được tính gấp đôi 7. Sắp xếp bảng lương theo TÊN tăng dần, trùng TÊN thì sắp xếp theo THU NHẬP giảm dần 8. Lập BẢNG 3 – THỐNG KÊ 9. Trích ra các dòng có THU NHẬP >= 1500000 và làm việc dài hạn 10. THUẾ: Nếu THU NHẬP >=4000000 thì phần THU NHẬP vượt trên 4000000 thuế phải nộp là 25%, nếu THU NHẬP >= 2000000 thì phần THU NHẬP vượt trên 2000000 thuế phải nộp là 10%, THU NHẬP <2000000 thì không phải nộp thuế