Bài tập Tin học đại cương - Bài tập Excel

t: LỢI NHUẬN là số tiền thu được hàng năm, CÒN LẠI là giá trị còn lại của dự án sau khi hết thời hạn đầu tư. Dùng hàm PV hoặc RATE tìm ra các phương án đầu tư có lợi, biết rằng lãi suất ngân hàng là 10% năm. BÀI 19: Giả sử có các phương án đầu tư sau: Đơn vị: Triệu đồng MÃ DỰ ÁN LÃI SUẤT VỐN BAN ĐẦU KỲ HẠN ĐẦU TƯ (NĂM) SỐ TIỀN THU VỀ/NĂM A1 6,25% -15 5 5 A2 6,55% -55 10 4,5 A3 7,05% -45 10 5,5 A4 7,15% -100 12 15 A5 7,25% -120 10 10,5 Dùng hàm PV hoặc RATE chọn ra các phương án đầu tư tốt, đầu tư không tốt; giải thích tại sao? BÀI 20: Giả sử có các phương án đầu tư sau, sử dụng hàm NPV hoặc IRR tính toán, tìm ra phương án nào là phương án đầu tư tốt nhất, tại sao? Đơn vị: triệu đồng MÃ DỰ ÁN LÃI SUẤT VỐN BAN ĐẦU NĂM 1 NĂM 2 NĂM 3 NĂM 4 A 10% -50 5 15 23 18 B 10% -50 10 10 25 20 C 10% -50 10 15 10 25 D 10% -50 0 0 0 70 E 10% -50 15 15 20 20 BÀI 21: Giả sử có các phương án đầu tư sau, dùng hàm NPV hoặc IRR cho biết những phương án nào là phương án đầu tư tốt, tại sao? Đơn vị: Triệu đồng MÃ DỰ ÁN LÃI SUẤT VỐN BAN ĐẦU NĂM 1 NĂM 2 NĂM 3 NĂM 4 A 10% -50 5 15 23 28 B 10% -40 10 10 25 20 C 10% -30 5 15 10 18 D 10% -50 0 0 0 65 E 10% -50 16 17 25 20 BÀI 22: Công ty liên doanh Asia Pacific muốn thuê của công ty X một mảnh đất để xây dựng một trung tâm thương mại. Trị giá quyền sử dụng của mảnh đất trên theo đánh giá của Sở nhà đất là 19.776 USD. Theo hợp đồng thuê tài chính, cuối mỗi năm công ty liên doanh phải thanh toán cho công ty X 2.600 USD trong suốt 15 năm. Yêu cầu: Anh (chị) hãy dùng hàm RATE xác định lãi suất của khoản vay tài chính của công ty liên doanh. BÀI 23: Một công ty liên doanh có nhu cầu mua một tài sản cố định. Giá hiện tại của tài sản cố định này là 100 triệu đồng. Biết lãi suất ngân hàng là 8% năm. Có các phương thức thanh toán sau đặt ra tuỳ công ty lựa chọn: Cho thanh toán chậm sau 1 năm phải trả 109 triệu đồng. Cho thanh toán chậm trong vòng 3 năm, cuối mỗi năm phải trả 42 triệu đồng. Cho thanh toán chậm, sau 4 năm phải trả số tiền là 139,2 triệu đồng. Cho thanh toán chậm trong vòng 4 năm, cuỗi mỗi năm phải trả 28,2 triệu đồng. Thanh toán ngay 100 triệu đồng. Yêu cầu: Anh (chị) dùng hàm PV để chọn phương án thanh toán có lợi nhất cho công ty. BÀI 24: Một doanh nghiệp muốn huy động vốn kinh doanh bằng cách bán trái phiếu hoàn trả một lần khi hết hạn. Thời hạn của trái phiếu là 2 năm, mệnh giá trái phiếu là 10 triệu đồng (số tiền trên mệnh giá sẽ được hoàn lại cho người mua sau khi hết hạn), lãi suất sinh lời dự kiến là 14% năm. Sử dụng hàm PV, anh (chị) tính giúp cho doanh nghiệp sẽ bán trái phiếu trên với giá hiện tại là bao nhiêu. BÀI 25: Một doanh nghiệp muốn huy động vốn kinh doanh bằng cách bán trái phiếu hoàn trả một lần khi hết hạn. Thời hạn của trái phiếu là 5 năm, mệnh giá trái phiếu là 10 triệu đồng (số tiền sẽ được hoàn lại sau khi hết hạn) và công ty bán nó với giá là 5.200.000 đồng. Dùng hàm RATE tính lãi suất của việc bán trái phiếu trên. BÀI 26: Một công ty A mời bạn đầu tư 40 triệu VND, với kỳ hạn 5 năm, lãi suất tiết kiệm lúc này là 7% năm. a) Bạn có đồng ý không nếu cuối mỗi năm bạn sẽ được nhận một khoản thanh toán là 10 triệu VND (sử dụng hàm PV hoặc RATE)? b) Nếu giả sử công ty A đề nghị thanh toán một lần 50 triệu VND vào cuối năm thứ 5 thì bạn có nên đầu tư không (sử dụng hàm PV, RATE hoặc FV)? BÀI 27: Một nhà đầu tư dự định trong 6 năm tới kể từ bây giờ cần phải tích luỹ được 9 triệu USD để mở rộng xí nghiệp. Dùng hàm PV cho biết vậy nhà đầu tư phải cho ngân hàng vay từ bây giờ là bao nhiêu với lãi suất 7,5% năm? BÀI 28: Bạn đang dự định đầu tư xây dựng trang trại. Dự kiến trong năm đầu tiên bạn có thể chịu lỗ 55 triệu VND, nhưng sau đó sẽ thu lại 95 triệu VND, 140 triệu VND, 185 triệu VND vào cuối năm thứ 2 thứ 3 thứ 4, và sẽ phải trả chi phí đầu tư ban đầu là 250 triệu VND, với tỷ lệ lãi suất là 12% năm. Hãy dùng hàm NPV hoặc IRR đánh giá việc đầu tư này. BÀI 29: Bạn đang cân nhắc mở một tài khoản đầu tư giáo dục cho một em bé 1 tuổi với kế hoạch gửi số tiền là 1 triệu VND vào đầu mỗi năm suốt trong 18 năm. Dùng hàm FV tính toán sau 18 năm số tiền sẽ tích luỹ được bao nhiêu; giả sử lãi suất tiết kiệm là 7,2% năm kéo dài suốt kỳ hạn 18 năm. BÀI 30: Bạn làm văn tự thế chấp nhà trong 10 năm để lấy 400 triệu VND đầu tư vào xây dựng trang trại. Giả sử mức lãi suất là 0,67% / tháng. Dùng hàm PMT, cho biết: a) Đầu mỗi tháng bạn phải trả bao nhiêu tiền? b) Cuối mỗi tháng bạn phải trả bao nhiêu tiền? BÀI 31: Bạn đang cân nhắc về một khoản đầu tư 40 triệu VND, khoản đầu tư này dự kiến đem lại cho bạn mỗi năm 10 triệu VND kéo dài trong 6 năm. Giả sử lãi suất là 10%/năm. Sử dụng hàm RATE hãy xác định mức lãi thực của khoản đầu tư này. BÀI 32: Một nhà đầu tư có ý định thuê một căn hộ chung cư với giá 120 triệu VND trong vòng 5 năm. Sau đó, bạn sẽ cho người khác thuê lại căn nhà; số tiền kỳ vọng thu về trong 5 năm lần lượt như sau: 40 triệu VND, 38 triệu VND, 35 triệu VND, 27 triệu VND, 25 triệu VND. Giả sử lãi suất là 10% năm. Dùng hàm NPV hoặc IRR đánh giá giúp NĐT có nên thực hiện việc dự án này không? BÀI 33: Một nhà đầu tư thực hiên một hợp đồng tài chính. Khoản tiền đầu tư là 1 tỷ đồng. Cuối mỗi tháng NĐT tiếp tục phải bỏ ra 100 triệu đồng trong suốt kỳ hạn. Kết thúc hợp đồng NĐT thu về 10 tỷ đồng. Giả sử lãi suất là 1%/tháng. Hỏi nhà đầu tư phải thực hiện hợp đồng trong thời gian là bao nhiêu thì thu hồi được vốn? BÀI 34: Một một xí nhiệp vay 250.000 USD để sản xuất, lãi suất là 5% một năm. Cuối mỗi năm xí nghiệp phải thanh toán là 32.000 USD. Tính số năm xí nghiệp sẽ phải trả nợ? BÀI 35: Bạn làm văn tự cầm cố nhà để lấy 400 triệu VND đầu tư vào xây dựng trang trại. Giả sử mức lãi suất là 0,67% tháng. Cuối mỗi tháng bạn phải trả là 4.861.562 đồng. Hỏi thời gian bạn phải trả là bao nhiêu năm? BÀI 36: Một nhà đầu tư đang cân nhắc mở một tài khoản bảo hiểm hưu trí bằng việc gửi tiền tiết kiệm. Dự kiến mỗi năm gửi 6 triệu đồng vào ngày 10/12 hàng năm, lãi suất tiết kiệm là 8,4% năm liên tục trong suốt kỳ hạn. Dùng hàm FV anh (chị) cho biết số tiền của nhà đầu tư khi kết thúc kỳ hạn: 1 năm là bao nhiêu? 20 năm là bao nhiêu? CƠ SỞ DỮ LIỆU & BÀI 37: Lọc tự động (Auto Filter) Lọc nâng cao (Advanced Filter) DANH SÁCH SINH VIÊN TT HỌ VÀ TÊN GIỚI LỚP NGOẠI NGỮ TIN HỌC ĐC TOÁN CC BÌNH QUÂN 1 Trần Ngọc Đông Nam A1 5 7 5 2 Trần Vũ Thuỷ Nữ A2 7 10 9 3 Lê Hoàng Anh Nam A1 6 8 7 4 Nguyễn Văn Lục Nam A3 8 9 4 5 Phạm Vũ Anh Nữ A2 4 7 3 6 Lý Thu Hằng Nữ A1 8 9 9 7 Hoàng Đức Thọ Nam A3 6 6 6 8 Vũ Thu Trang Nữ A3 7 8 5 9 Trần Anh Minh Nam A1 6 7 9 10 Lê Hoà Anh Nữ A2 4 8 10 Yêu cầu: 1) Tính bình quân = (Ngoại ngữ + Tin + Toán CC)/3 2) Sử dụng Auto Filter hoặc Advanced Filter đưa ra: Danh sách các sinh viên có điểm bình quân >=8 Danh sách các sinh viên nam có điểm Tin học >=7 Danh sách các sinh viên nam Danh sách các sinh viên họ Trần Danh sách các sinh viên có tên là Anh Danh sách các sinh viên nữ của lớp A1 hoặc A2 Danh sách các sinh viên phải thi lại môn Ngoại ngữ Danh sách các sinh viên phải thi lại (có điểm thi một môn <5) Danh sách các sinh viên có điểm bình quân >=8 Danh sách các sinh viên có điểm bình quân >=7 và một môn bất kỳ >=9 & Bài 38: Hàm Sumif, Subtotals, If, Vlookup CÔNG TY LƯƠNG THỰC MIỀN BẮC Bộ phận bán hàng –––––––––––––– CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập – Tự do – Hạnh phúc ––––––––––––––––––––––– BÁO CÁO KINH DOANH THÁNG 3 – 2010 NGÀY MÃ HĐ KHÁCH HÀNG LOẠI HÀNG SỐ LƯỢNG ĐƠN GIÁ TIỀN HÀNG PHỤ PHÍ TỶ LỆ THUẾ TT THUẾ TIÊU THỤ CỘNG TIỀN TỶ LỆ 1/3/05 H01/KD VAFACO Gạo 520 1/3/05 H03/KD SGVNC Đường 450 1/3/05 H01/NB DOBESC Sữa 742 2/3/05 H02/KD IMEXCO Bột mỳ 348 2/3/05 H03/XK LOTUS Gạo 256 2/3/05 H04/KD SGVNC Cafê 346 3/3/05 H01/KD VAFACO Sữa 134 3/3/05 H02/NB DOBESC Đường 267 3/3/05 H03/XK IMXCO Bột mỳ 159 4/3/05 H06/KD SGVNC Đường 357 4/3/05 H05/NB LOTUS Gạo 428 5/3/05 H06/XK VAFACO Cafê 359 5/3/05 H09/KX LOTUS Sữa 759 6/3/05 H07/KD IMEXCO Bột mỳ 351 6/3/05 H08/XK DOBESC Đường 157 7/3/05 H09/KD SGVNC Gạo 254 7/3/05 H08/NB VAFACO Cafê 365 8/3/05 H04/NB LOTUS Sữa 451 8/3/05 H07/XK IMEXCO Bột mỳ 652 TỔNG CỘNG BẢNG ĐƠN GIÁ - PHỤ PHÍ Loại hàng Đơn giá % Phụ phí Gạo 5500 2.5% Đường 6000 1.3% Sữa 8000 0.5% Cafê 5000 3.5% Bột mỳ 5000 4.5% BẢNG SO SÁNH Tỷ lệ thuế TT Tổng thuế TT Tổng cộng tiền 12% ... ... 5% ... ... 10% ... ... 30% ... ... 20% ... ... BẢNG TỔNG HỢP LOẠI HÀNG Loại hàng Cộng tiền Tỷ lệ Gạo ... ... Đường ... ... Sữa ... ... Cafê ... ... Bột mỳ ... ... Tổng ... ... BẢNG TỔNG HỢP KHÁCH HÀNG Tên khách Cộng tiền Tỷ lệ VAFACO ... ... IMEXCO ... ... DOBESC ... ... LOTUS ... ... SGVNC ... ... Tổng ... ... Yêu cầu: Dựa vào Bảng đơn giá để điền giá tương ứng với từng loại hàng Tiền hàng = Đơn giá * Số lượng Phụ phí=Tiền hàng*Tỷ lệ% tương ứng với từng loại hàng trong bảng đơn giá (% Phụ phí) Sử dụng hàm điền thông tin vào cột Tỷ lệ thuế TT (Tỷ lệ thuế tiêu thụ) Thuế tiêu thụ phụ thuộc vào Mã HĐ Nếu Hợp đồng là NB (nội bộ) thì thuế tiêu thụ = 0 Ngược lại Thuế tiêu thụ = (Tiền hàng + Phụ phí) * Tỷ lệ thuế TT Cộng tiền = Tiền hàng + Phụ phí + Thuế tiêu thụ Tính tổng cộng cho tất cả các cột Tiền hàng, Phụ phí, Thuế tiêu thụ, Cộng tiền Tính cột Tỷ lệ = Công tiền của từng hợp đồng so với tổng cộng tiền của tất cả các hợp đồng Sao chép bảng đã tính toán ở trên và sử dụng chức năng Subtotals để phân tích theo loại hàng (hoàn thành bảng Tổng hợp loại hàng) Tính tổng cộng tiền, tỷ lệ của từng Khách hàng (hoàn thành bảng Tổng hợp khách hàng) Hoàn thành Bảng so sánh & Bài 39: Pivot Table Hàm: Vlookup, Hlookup, Left CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập – Tự do – Hạnh phúc ––––––––––––––––––––––– BẢNG THỐNG KÊ BÁO CÁO THU THUẾ ĐẾN NGÀY 31/1/2010 SỐ ĐKKD TÊN CHỢ MÃ HÀNG TÊN HÀNG VỐN KINH DOANH NGÀY QĐ NỘP THUẾ NGÀY NỘP THUẾ TIỀN PHẠT THUẾ PHẢI NỘP 005/TC TP 10.000.000 13/01/09 29/01/09 001/HO QA 2.000.000 15/01/09 19/01/09 004/HO MP 5.000.000 12/01/09 15/01/09 002/MO TP 15.000.000 20/01/09 29/01/09 004/TC QA 2.000.000 18/01/09 13/01/09 003/HD GD 1.500.000 02/01/09 04/01/09 005/CG MP 9.000.000 10/01/09 12/01/09 008/TC VKT 8.000.000 13/01/09 15/01/09 009/HD TP 900.000 25/01/09 29/01/09 003/MO GD 5.000.000 04/01/09 15/01/09 004/TC QA 15.000.000 19/01/09 21/01/09 BẢNG TÊN CHỢ Mã Tên chợ TC Thành Công HO Chợ Hôm MO Chợ Mơ CG Cầu Giấy HD Hàng Da BẢNG HÀNG HÓA Mã TP MP GD QA VKT Tên hàng Thực phẩm Mỹ phẩm Giầy dép Quần áo Vải Kate Thuế (%) 8% 5% 10% 9% 6% Yêu cầu: Điền tên chợ dựa vào bảng tên chợ và 2 ký tự bên phải của Số ĐKKD Điền tên hàng dựa vào Mã hàng và Bảng hàng hóa Chèn thêm cột và tính Tiền thuế = Vốn kinh doanh * Thuế (%) (thuế lấy từ bảng hàng hóa) Tiền phạt: Nếu nộp thuế đúng hạn hoặc chậm nhất là sau 3 ngày thì không bị phạt. Còn lại thì lấy số ngày quá hạn * 2% của tiền thuế Thuế phải nộp = Tiền phạt + Tiền thuế Trích ra những chợ bị phạt Lập bảng thống kê tổng số thuế thu được của từng chợ Lập bảng thống kê tổng số thuế thu được của từng mặt hàng. & BÀI 40: Biểu đồ - đồ thị. TỔNG KẾT SẢN PHẨM (Đơn vị: Tấn) QUÍ I QUÍ II QUÍ III QUÍ IV Tổ 1 180 150 100 165 Tổ 2 80 140 170 80 Tổ 3 75 130 90 150 Yêu cầu: 1) Lập biểu đồ biểu diễn số lượng sản phẩm theo quí trong năm của từng tổ (Hình 1, 2, 3). 2) Lập biểu đồ so sánh số lượng sản phẩm của cả ba tổ (Hình 4). Hình 1 Hình 2 Hình 3 Hình 4 TÌNH HÌNH SẢN XUẤT CỦA CÔNG TY VAPHACO Giai đoạn 2001-2005 Đơn vị: Tấn Yêu cầu: Tính tổng. Vẽ biểu đồ so sánh tình hình sản xuất của các xí nghiệp với nhau trong công ty giai đoạn 2001–2005 (Hình 5) Vẽ biểu đồ so sánh tình hình sản xuất trong các năm cho từng xí nghiệp trong giai đoạn 2001–2005 (Hình 6, 7, 8) Hình 5 Hình 6 Hình 7 Hình 8 & Bài 41: Biểu đồ phức tạp TÌNH HÌNH NHẬP KHẨU MỘT SỐ MẶT HÀNG Đơn vị tính: Triệu USD Năm Nguyên liệu 2006 2007 2008 2009 2010 Dầu thô 120 145 180 180 160 Đồng 150 123 160 150 180 Chì 100 200 140 120 170 Sắt 120 140 200 170 150 Thép 110 100 150 140 200 CHỈ SỐ PHÁT TRIỂN Đơn vị tính: % Năm Nguyên liệu 2007 2008 2009 2010 Dầu thô ... ... ... ... Đồng ... ... ... ... Chì ... ... ... ... Sắt ... ... ... ... Thép ... ... ... ... Yêu cầu: - Tính chỉ số phát triển: Số liệu năm sau/Số liệu năm trước (tính %) - Vẽ các biểu đồ theo mẫu: MỘT SỐ BÀI TOÁN QUẢN LÝ BÀI 42: Quản lý sinh viên TRƯỜNG ĐH NGOẠI THƯƠNG ------------***----------- CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập - Tự do - Hạnh phúc ---------------o0o--------------- DANH SÁCH SINH VIÊN KHOÁ 44 Năm học: 2005 – 2006 H1 H2 H3 STT HỌ VÀ TÊN GIỚI ĐOÀN SỐ THẺ LỚP DM1 DM2 DM3 BQ XT 1 Trần Nam Anh Nam C K44001 ANH1 8 9 7 2 Nguyễn Thị Bình Nữ K K44002 ANH1 9 7 8 3 Phạm Mai Chi Nữ C K44003 ANH1 3 6 6 4 Nguyễn Chí Hùng Nam C K44004 ANH1 7 8 10 5 Cao Việt Thắng Nam C K44005 ANH1 5 4 3 1 Nguyễn Vân Anh Nữ K K44006 ANH2 9 9 10 2 Đặng Thái Anh Nữ C K44007 ANH2 6 7 10 3 Nguyễn Thi Lan Nữ C K44008 ANH2 7 5 4 4 Bùi Minh Hà Nam K K44009 ANH2 8 6 5 5 Nguyễn Văn Vinh Nam K K44010 ANH2 3 5 6 6 Trần Thị Xuân Nữ C K44011 ANH2 4 3 5 1 Phạm Hoà Bình Nữ C K44012 ANH3 9 8 8 2 Nguyễn Mai Chi Nữ C K44013 ANH3 8 7 9 3 Trần Văn Hà Nam C K44014 ANH3 7 5 4 Yêu cầu: 1) Ghi tên, lớp của mình, điền số trang vào tiêu đề đầu, tiêu đề cuối của bảng tính 2) Mỗi câu làm trên một trang giấy (sử dụng dấu ngắt trang cứng Insert/Page break). Câu 1: Tính điểm BQ = (DM1*H1+DM2*H2+DM3*H3)/(H1+H2+H3) (Biết: H1,H2,H3 là các hệ số được nhập vào từ bàn phím tại 3 ô trên mẫu biểu); Hãy lọc dữ liệu để đưa ra kết quả học tập của từng lớp theo mẫu: TRƯỜNG ĐH NGOẠI THƯƠNG ------------***----------- KẾT QUẢ HỌC TẬP HỌC KỲ: ... Lớp: ... ---------------o0o--------------- STT HỌ VÀ TÊN DM1 DM2 DM3 BQ ... ... ... ... ... ... Câu 2: Dùng hàm RANK tìm thứ tự của các sinh viên trong danh sách theo điểm bình quân (xếp thứ). In kết quả học tập của sinh viên theo chiều giảm dần của BQ với mẫu: TRƯỜNG ĐH NGOẠI THƯƠNG ------------***----------- KẾT QUẢ HỌC TẬP HỌC KỲ: ... ---------------o0o--------------- STT HỌ VÀ TÊN LỚP DM1 DM2 DM3 BQ XẾP THỨ ... ... ... ... ... ... ... ... Câu 3: Dùng hàm IF tính học bổng cho các sinh viên. Đưa ra danh sách những sinh viên được cấp học bổng theo lớp và chiều tăng dần của học bổng trong mỗi lớp với mẫu sau: TRƯỜNG ĐH NGOẠI THƯƠNG ------------***----------- DANH SÁCH HỌC BỔNG NĂM : ... ---------------o0o--------------- STT HỌ VÀ TÊN LỚP DM1 DM2 DM3 BQ HB ... ... ... ... ... ... ... ... Biết: Học bổng được cấp theo 3 mức sau: Nếu BQ ³ 9 Nếu 8 £ BQ < 9 Nếu 7,5 < BQ < 8 Câu 4: Dùng Subtotal tổng hợp học bổng theo lớp, đưa ra kết quả theo mẫu: TRƯỜNG ĐH NGOẠI THƯƠNG ------------***----------- CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập - Tự do - Hạnh phúc ---------------o0o--------------- HỌC BỔNG NĂM HỌC 2005 - 2006 Khoá 44 STT LỚP TỔNG SỐ TIỀN HB KÝ NHẬN ... ... ... ... Cộng dọc: ... Câu 5: Sử dụng AUTO FILTER lọc dữ liệu để đưa ra danh sách những sinh viên đạt danh hiệu sinh viên tiên tiến theo mẫu: TRƯỜNG ĐH NGOẠI THƯƠNG ------------***----------- CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập - Tự do - Hạnh phúc ---------------o0o--------------- DANH SÁCH SINH VIÊN ĐẠT DANH HIỆU TIÊN TIẾN Năm học: ... STT HỌ VÀ TÊN LỚP DM1 DM2 DM3 BQ ... ... ... ... ... ... Biết: Tiêu chuẩn sinh viên tiên tiến không có môn nào có điểm dưới 5 và có BQ ³ 7. Câu 6: Thêm cột Phân loại ở bảng dữ liệu gốc và tính toán cho dữ liệu của cột này (xem tiêu chuẩn phân loại ở dưới). Dùng Subtotal/Count hoặc dùng hàm CountIf để thống kê số lượng theo mẫu: TRƯỜNG ĐH NGOẠI THƯƠNG ------------***----------- CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập - Tự do - Hạnh phúc ---------------o0o--------------- KẾT QUẢ PHÂN LOẠI SINH VIÊN Học kỳ: ..... Khoá: ...... STT XẾP LOẠI SỐ LƯỢNG TỶ LỆ % 1 A ... ... 2 B ... ... 3 C ... ... 4 D ... ... Tổng cộng .... .... Biết tiêu chuẩn phân loại như sau: Loại “A” nếu BQ ³ 8 Loại “B” nếu 7 £ BQ < 8 Loại “C” nếu 5 £ BQ < 7 Loại “D” nếu BQ < 5 Hãy lập biểu đồ biểu diễn số lượng phân loại sinh viên theo mẫu sau: Câu 7: Hãy lọc dữ liệu để đưa ra danh sách đoàn viên theo từng lớp như mẫu sau: TRƯỜNG ĐH NGOẠI THƯƠNG ĐOÀN THANH NIÊN ------------***----------- CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập - Tự do - Hạnh phúc ---------------o0o--------------- DANH SÁCH ĐOÀN VIÊN CHI ĐOÀN Lớp:... SỐ TT HỌ VÀ TÊN GIỚI TÍNH ... ... ... Câu 8: Dùng Subtotal/Count hoặc dùng hàm CountIf để thống kê số sinh viên nam, nữ, tỷ lệ so với tổng số sinh viên, kết quả đưa ra theo mẫu: TRƯỜNG ĐH NGOẠI THƯƠNG ------------***----------- CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập - Tự do - Hạnh phúc ---------------o0o--------------- THỐNG KÊ GIỚI TÍNH Giới tính SỐ LƯỢNG TỶ LỆ % Nam ... ... Nữ ... ... Tổng số: ... ... Câu 9: Hãy lọc dữ liệu để đưa ra danh sách thi lần 2 của từng môn học theo mẫu: TRƯỜNG ĐH NGOẠI THƯƠNG ------------***----------- CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập - Tự do - Hạnh phúc ---------------o0o--------------- DANH SÁCH THI MÔN: .... Lần thi: 2 STT HỌ VÀ TÊN LỚP ĐIỂM SỐ TỜ KÝ NHẬN ... ... ... ... ... ... BÀI 43: Quản lý nhân sự DANH SÁCH NHÂN VIÊN HỌ TÊN GIỚI TÍNH NĂM SINH SỐ THẺ ĐƠN VỊ NĂM TĂNG LƯƠNG HỆ SỐ TUỔI Nguyễn Thu Hà Nữ 1955 NT001 KTNT 2009 4,14 Trần Văn Thắng Nam 1950 NT003 KTNT 2009 5,12 Nguyễn Mai Trang Nữ 1949 NT002 QTKD 2010 3,63 Nguyễn Thị Hồng Nữ 1987 NT005 CB 2009 1,95 Mai Anh Thơ Nữ 1985 NT004 KTNT 2009 3,63 Trần Trung Hiếu Nam 1986 NT006 CB 2010 3,14 Nguyễn Thu Thuỷ Nữ 1948 NT010 QTKD 2009 5,12 Lê Xuân Thuỷ Nữ 1949 NT007 CB 2010 3,63 Hoàng Thị Oanh Nữ 1987 NT009 KTNT 2010 1,95 Hồ Thị Thuý Hà Nữ 1979 NT008 CB 2009 3,14 Trần Việt Hùng Nam 1988 NT011 QTKD 2010 2,16 Yêu cầu: 1) Ghi tên, lớp của mình, điền số trang vào tiêu đề đầu, tiêu đề cuối của bảng tính 2) Mỗi câu làm trên một trang giấy (sử dụng dấu ngắt trang cứng Insert/Page break). Câu 1: Tính Tuổi = Năm hiện tại – Năm sinh. Hãy lọc dữ liệu để đưa ra danh sách những cán bộ trong độ tuổi cần thực hiện nghĩa vụ quân sự theo mẫu: DANH SÁCH CÁN BỘ THỰC HIỆN NGHĨA VỤ QS STT HỌ VÀ TÊN ĐƠN VỊ NĂM SINH ... ... ... ... Những cán bộ nam có độ tuổi từ 18 đến 28 là những người cần đăng ký nghĩa vụ quân sự. Câu 2: In danh sách những cán bộ trong độ tuổi nghỉ hưu theo mẫu: DANH SÁCH NHỮNG CÁN BỘ TRONG ĐỘ TUỔI NGHỈ HƯU Năm:... STT HỌ VÀ TÊN ĐƠN VỊ NĂM SINH ... ... ... ... Điều kiện nghỉ hưu là những cán bộ nam có tuổi ³ 60 và những cán bộ nữ có tuổi ³ 55. Câu 3: In danh sách những cán bộ nữ theo từng đơn vị với mẫu: DANH SÁCH CÁN BỘ NỮ Đơn vị: ... STT HỌ VÀ TÊN NĂM SINH HỆ SỐ ... ... ... ... Câu 4: Dùng Sutotal/Count hoặc hàm Countif thống kê số lượng cán bộ theo giới tính, kết quả đưa ra theo mẫu: BẢNG THỐNG KÊ CÁC BỘ THEO GIỚI TÍNH GIỚI TÍNH SỐ LƯỢNG TỶ LỆ % Nam ... ... Nữ ... ... Tổng cộng: ... ... BÀI 44: Quản lý doanh nghiệp BẢNG GIÁ HÀNG STT MÃ HÀNG TÊN HÀNG GIÁ NHẬP GIÁ XUẤT 1 M01 Bút máy Cửu long 13.500 15.000 2 M02 Giấy thếp Bãi bằng 2.200 2.500 3 M03 Hộp đựng bút 35.000 41.000 4 M04 Vở học sinh 2.700 3.300 QUẢN LÝ HÀNG NHẬP STT NGÀY MÃ HÀNG SỐ LƯỢNG TIỀN 1 15/2/2010 M01 101 2 2/3/2010 M02 134 3 12/3/2010 M03 178 4 13/5/2010 M04 123 5 01/10/2010 M01 123 6 01/10/2010 M02 56 7 1/11/2010 M03 234 QUẢN LÝ HÀNG XUẤT STT NGÀY MÃ HÀNG SỐ LƯỢNG TIỀN 1 13/4/2010 M03 22 2 22/5/2010 M01 34 3 13/6/2010 M03 45 4 13/6/2010 M04 31 5 1/8/2010 M04 22 6 19/9/2010 M01 34 7 22/11/2010 M01 98 8 08/12/2010 M02 34 9 08/12/2010 M03 51 Yêu cầu: 1) Ghi tên, lớp của mình, điền số trang vào tiêu đề đầu, tiêu đề cuối của bảng tính 2) Mỗi câu làm trên một trang giấy (sử dụng dấu ngắt trang cứng Insert/Page break). Câu 1: Tính tiền biết đối với những mặt hàng xuất: Tiền = Số lượng * Giá xuất; với những mặt hàng nhập: Tiền = Số lượng * Giá nhập. Câu 2: Dùng Subtotal tổng hợp số lượng, tiền theo từng mặt hàng xuất, kết quả đưa ra theo mẫu: BÁO CÁO LƯỢNG HÀNG BÁN STT MÃ HÀNG TÊN HÀNG SỐ LƯỢNG TIỀN ... ... ... ... ... Tổng cộng: ... Câu 3: Dùng Subtotal tổng hợp lượng hàng nhập vào theo từng mã hàng, kết quả đưa ra theo mẫu: BÁO CÁO HÀNG NHẬP STT MÃ HÀNG TÊN HÀNG SỐ LƯỢNG TIỀN TỶ LỆ % ... ... ... ... ... ... Tổng cộng ... ... Câu 4: Tính lượng hàng còn lại theo từng mã hàng, kết quả đưa ra theo mẫu BÁO CÁO KIỂM KÊ HÀNG TỒN KHO STT MÃ HÀNG TÊN HÀNG SỐ LƯỢNG NHẬP SỐ LƯỢNG XUẤT TỒN ... ... ... ... ... ... Câu 5: Dựa vào số liệu đã tính ở câu trên, dùng hàm VLOOKUP cho biết số liệu còn lại của một mã hàng bất kỳ, mã hàng là dữ liệu nhập vào từ bàn phím; kết quả đưa ra theo mẫu: THÔNG TIN HÀNG HÓA MÃ HÀNG TÊN HÀNG SỐ LƯỢNG CÒN LẠI ... ... ... Câu 6: Dùng lọc dữ liệu cho biết số liệu chi tiết của từng mặt hàng xuất ra khỏi kho. Kết quả đưa ra theo mẫu: LƯỢNG HÀNG XUẤT CỦA MẶT HÀNG: ...(TÊN HÀNG) STT NGÀY SỐ LƯỢNG TIỀN ... ... ... ... Tổng cộng: ... ... Câu 7: Thêm cột THÁNG vào bảng Quản lý hàng xuất, điền dữ liệu cho cột này. Dùng Subtotal tổng hợp lượng tiền theo tháng, kết quả đưa ra theo mẫu: BÁO CÁO KIỂM KÊ LƯỢNG TIỀN THEO THÁNG STT THÁNG TIỀN TỶ LỆ % ... ... ... ... Tổng cộng: ... Lập biểu đồ thể hiển số tiền bán ra của các tháng. Câu 8: Cho biết đơn giá bán của một mã hàng bất kỳ, mã hàng là dữ liệu nhập vào từ bàn phím; kết quả đưa ra theo mẫu: MÃ HÀNG TÊN HÀNG ĐƠN GIÁ ... ... ... Câu 9: Lọc dữ liệu để đưa ra bảng chi tiết về các mặt hàng bán ra trong từng ngày: LƯỢNG HÀNG XUẤT NGÀY... STT MÃ HÀNG TÊN HÀNG SỐ LƯỢNG XUẤT ... ... ... ... & Bài 45: Hàm Vlookup, Mid, Left, If, Or Các hàm Cơ sở dữ liệu CỬA HÀNG NÔNG SẢN TT MÃ HĐ NGÀY HĐ TÊN HÀNG SỐ LƯỢNG LOẠI HÀNG TÊN K.HÀNG ĐƠN GIÁ THÀNH TIỀN GN0123K1 05/21/2010 1.500 DK0123K5 05/21/2010 150 MK0225K1 05/21/2010 400 GN0126K1 05/24/2010 100.000 BĐ0128K4 05/24/2010 50 BĐ0229K5 05/26/2010 700 ĐX0120K0 05/27/2010 600 GN0234K0 05/27/2010 19.000 BĐ0165K4 05/29/2010 750 DK0234K5 05/29/2010 900 BẢNG KHÁCH HÀNG BẢNG TÊN HÀNG-ĐƠN GIÁ MKH TÊN KH K0 Khách lẻ K1 CT TNHH Hà Hải K2 CT CP Lê Hoàng K3 Cửa hàng HT K4 CT Hưng Thịnh K5 Cửa hàng Phát Đạt MÃ SP TÊN SP ĐƠN GIÁ 1 ĐƠN GIÁ 2 DN Dừa nước 200 220 DK Dừa khô 250 300 GN Gạo 5.000 5.500 MK Mì lát khô 2.000 2.200 BĐ Bắp 2.200 2.500 ĐX Đậu xanh 5.000 5.500 Yêu cầu: 1. Lập công thức điền Tên hàng dựa vào 2 ký tự đầu tiên của Mã hóa đơn và Bảng Tên hàng-Đơn giá 2. Lập công thức điền Loại hàng dựa vào ký tự thứ 4 của mã hóa đơn 3. Lập công thức điền Tên khách hàng dựa vào 2 ký tự cuối của Mã hóa đơn và Bảng khách hàng 4. Lập công thức điền Đơn giá dựa vào Tên hàng và Bảng Tên hàng-Đơn giá. Biết Loại hàng 1 thì Đơn giá 1, Loại hàng 2 thì Đơn giá 2. 5. Lập công thức điền Thành tiền = Số lượng * Đơn giá; biết: Nếu mua hàng là Khách lẻ hoặc số lượng < 200 thì giảm 5%. Hãy làm tròn kết quả đến 1 chữ số thập phân. 6. Sắp xếp bảng tính tăng dần theo cột Mã hóa đơn 7. Sử dụng AutoFilter lọc ra các hóa đơn lập cho Gạo hoặc Đậu xanh 8. Sử dụng các hàm cơ sở dữ liệu điền thông tin vào các bảng sau: TÊN HÀNG SỐ LẦN BÁN SỐ LƯỢNG LỚN NHẤT SỐ LƯỢNG NHỎ NHẤT SỐ LƯỢNG TRUNG BÌNH TỔNG SỐ LƯỢNG Gạo ... ... ... ... ... & Bài 46: Liên kết các bảng dữ liệu từ nhiều Sheet Hàm: Vlookup, If Tạo File bảng tính có tên TSCD.XLS (Lập công thức tại những ô có dấu ...) 1. Trong Sheet 1 Nhóm tài sản 1 Nhà cửa kho tàng 2 Máy móc thiết bị 3 Phương tiện vận tải 2. Trong Sheet 2 BẢNG KÊ TÀI SẢN CỐ ĐỊNH ĐẾN NGÀY 31/03/2010 Mã TS Tên TS Nguyên giá Còn lại Tỷ lệ KH/năm Nhóm 001 Văn phòng Cty 1.000.000.000 500.000.000 10% 1 002 Nhà kho 1 400.000.000 400.000.000 8% 1 003 Xe Toyota 300.000.000 300.000.000 10% 3 004 Xe Deawoo 100.000.000 20.000.000 7% 3 005 Máy vi tính 20.000.000 10.000.000 20% 2 006 Nhà kho 2 100.000.000 80.000.000 10% 1 007 Máy photocopy 25.000.000 20.000.000 20% 2 Tổng cộng ... ... 3. Trong Sheet 3 BẢNG TÍNH KHẤU HAO THÁNG 04/2010 Mã TS Tên TS Nguyên giá Tiền khấu hao Khấu hao tháng Nhóm Tên nhóm 001 ... ... ... ... ... ... 002 ... ...