Bài tập excel - Tin học đại cương (IT1110) | Trường Đại học Bách khoa Hà Nội
Nhập và định dạng dữ liệu như bảng tính sau
Preview text:
lOMoAR cPSD| 27879799 Bài tập 1
1. Nhập và định dạng dữ liệu như bảng tính sau:
BẢNG KÊ HÀNG NHẬP KHO Ngày: S CƯỚC T TÊN SỐ ĐƠN TRỊ THUẾ CHUYÊN CỘNG T HÀNG LƯỢNG GIÁ GIÁ CHỞ 1 Video 100 4.000.000 2 Ghế 50 150.000 3 Giường 58 1.200.000 4 Tủ 79 850.000 5 Nệm 92 200.000 6 Tivi 220 2.500.000 7 Bàn 199 600.000 TỔNG CỘNG:
Yêu cầu tính toán:
2. Đánh số thứ tự cho cột STT (sử dụng Fill handle).
3. Định dạng cột đơn giá có dấu phân cách hàng ngàn.
4. Trị giá = Số lượng * Đơn giá. 5. Thuế = Trị giá * 5%. lOMoAR cPSD| 27879799 Bài tập 2
6. Cước chuyên chở = Số lượng * 1500.
7. Cộng = Trị giá + Thuế + Cước chuyên chở.
8. Hãy tính tổng cộng các cột TRỊ GIÁ, THUẾ, CƯỚC CHUYÊN CHỞ và CỘNG. (Dùng hàm SUM) 9.
Ngày: Dùng hàm lấy ra ngày, tháng, năm hiện tại. lOMoAR cPSD| 27879799 Bài tập 3
CÁC HÀM XỬ LÝ CHUỖI (TEXT FUNTIONS)
Mặt hàng Mã HĐ Mã loại STTHĐ Đơn giá
Số lượng Giảm giá Thành tiền Kaki 2 K1252 15 80.000 Jean 1 J2011 2 24 00.000 Jean 2 J0982 1 12 50.000 Kaki 1 K5801 1 30 20.000 Yêu cầu:
1. Lập công thức cho cột Mã Loại, biết rằng: Ký tự cuối cùng của Mã HĐ là Mã Loại 2.
Lập công thức cho cột STT HĐ, biết rằng: 3 ký tự giữa của Mã HĐ là STT của hoá đơn.
3. Tính cột giảm giá sao cho: nếu Mã loại là 1 thì Giảm giá = 30%*Đơn
giá * Số lượng nếu Mã loại là 2 thì Giảm giá = 50%*Đơn giá * Số
lượng 4. Tính cột thành tiền = Đơn giá * Số lượng - Giảm giá Lưu ý:
*Kết quả trong cột mã loại là kiểu Text nên phải dùng
hàm Value(text) để đổi thành giá trị số hoặc phải so
sánh với dạng ký tự như "1", "2". lOMoAR cPSD| 27879799 Bài tập 4
BẢNG XẾP LOẠI HỌC SINH STT Họ & tên Windows Winword Excel ĐTB Xếp loại Thưởng 1 Bành Đức Dũng 10 9 10 2 Bùi Đức Hoàng 7 6 8 3 Bùi Hữu Nam 6 8 9 4 Bùi Vĩnh Phát 8 5 9 5 Đặng Quang Thái 8 8 9 6 Đặng Thành Trí 5 4 4 7 Đậu Xuân Công 4 6 5 8 Đinh Tấn Nhật 7 9 9 9 Đỗ Duy An 7 9 9 10 Đỗ Duy Anh 3 4 7 Điểm TB cao nhất
Điểm TB thấp nhất Bình quân điểm TB lOMoAR cPSD| 27879799 Bài tập 5
Tổng số học viên được thưởng:
Yêu cầu tính toán:
1. Đánh số thứ tự cho cột STT (sử dụng Fill handle).
2. Tính cột điểm ĐTB theo công thức sau :
Điểm Windows hệ số 1, Winword hệ số 2, Excel hệ số 3 thể hiện ở dạng một số lẻ
3. Xếp loại cho học viên (b) theo tiêu chuẩn sau: (Sử dụng hàm IF)
Điểm trung bình <5: xếp loại "Yếu"
Điểm trung bình <6,5: xếp loại "TB"
Điểm trung bình <8: xếp loại "Khá"
Điểm trung bình >=8: xếp loại "Giỏi"
4. Đánh dấu + ở cột thưởng cho những học viên xếp loại Giỏi và điểm Excel >=9 (Sử dụng hàm IF kết hợp AND)
5. Thực hiện các thống kê ĐTB (Sử dụng các hàm MAX, MIN, AVERAGE)
6. Đếm tổng số học sinh được thưởng (Sử dụng hàm COUNTIF)
7. Highlight những học sinh đạt loại giỏi (Sử dụng Condiionnal Formating) lOMoAR cPSD| 27879799 Bài tập 6
BẢNG KÊ HÀNG HÓA TỒN KHO QUÝ 3 NĂM 2000 BẢNG THUẾ SUẤT Tên hàng Khu vực Số lượng
Đơn giá Trị giá
Thuế Tổng cộng
Khu vực Thuế suất Ghế đệm 1 345 235 1 8% Bàn gỗ 1 232 165 2 5% Bàn bureau 1 123 112 BẢNG THỐNG KÊ 3 3% Ghế salon 1 120 196 4 2% 1 T otal Ghế đôn 2 234 118 KHU Ghế mây 2 321 229 Khu vực Trị giá Thuế Tổng cộng Bàn mica 2 232 200 1 2 Total Ghế đẩu 3 145 VỰC 220 Bàn tròn 2 3 244 245 3 Total Bàn dài 4 250 283 3 Bàn cắt 4 244 237 4 Total Grand Total 4 YÊU CẦU:
1. Tính các cột trị giá, thuế và tổng cộng, cho biết
- Trị giá = Số lượng * Đơn giá.
- Thuế = Trị giá * Thuế suất. Thuế suất cho mỗi khu vực là khác nhau và được cho cụ thể trong bảng thuế suất (Sử dụng hàm VLOOKUP) - Tổng
cộng = Trị giá + Thuế (Làm tròn lên hàng trăm)
2. Xác định Trị giá, Tiền thuế và Tổng cộng của từng khu vực vào Bảng thống kê. (Sử dụng hàm SUMIF)
3. Định dạng bảng tính đã cho theo mẫu. Định dạng các ô chứa giá trị số có dấu phân cách hàng nghìn. Làm tròn cột Tổng cộng đến hàng trăm.
4. Tổng hợp (Subtotal) các cột Trị giá, Thuế và Tổng cộng theo khu vực. lOMoAR cPSD| 27879799 Bài tập 5
BẢNG KÊ HÀNG HÓA XUẤT NHẬP Tỉ giá: 25.300 STT MÃ TÊN
NGÀY SX NHẬP XUẤT SỐ ĐƠN TRỊ GIÁ KHUYẾN HÀNG HÀNG LƯỢNG GIÁ BẢNG HÀNG HÓA: (VNĐ) MÃI (USD) 1 MDT 28/1/05 x 15 Đ ơn giá (USD) 2 MPT 20/1/05 x 50 Mã hàng Tên hà ng Nhập Xuất TV Ti vi 200 220 ML Máy lạnh 250 270 3 TL 20/1/05 x 8 MPT Máy photo 450 480 TL Tủ lạnh 280 300 MDT Máy điện thoại 150 180 4 TV 18/1/05 x 50 5 TL 17/1/05 x 30 6 MDT 12/1/05 x 45 7 ML 6/1/05 x 10 8 TV 5/1/05 x 15 9 ML 3/1/05 x 25 10 MPT 2/1/05 x 30 Tổng cộng:
THỐNG KÊ XUẤT HÀNG Mô tả: Tên hàng Số lượng
- Cột Nhập và cột Xuất: Tùy theo cột nào có đánh dấu x để biết được là hàng Nhập hay Xuất. Ti vi Yêu cầu:
1. Tên hàng: Dựa vào Mã hàng, tra trong Bảng Hàng hóa. (Sử dụng hàm VLOOKUP)
2. Đơn giá (USD): Dựa vào Mã hàng và Nhập hay Xuất, tra trong Bảng Hàng hóa. (Sử dụng hàm VLOOKUP và hàm IF) Máy lạnh
3. Trị giá (VNĐ): Số lượng * Đơn giá (USD) * Tỉ giá. (Lưu ý sử dụng địa chỉ tuyệt đối)
4. Khuyến mãi: Nếu hàng sản xuất trong khoảng từ ngày 10 đến 20 của tháng 1 thì ghi là "Có khuyến mãi ", ngược lại để trống.
5. Tổng cộng: Tính tổng Số lượng, Tổng trị giá, Có bao nhiêu mặt hàng khuyến mãi. Số lượng hàng xuất 6. Máy photo
Lâp bảng thống kê số lượng hàng đã xuất theo từng tên hàng (Sử dụng hàm SUMIFS)
7. Vẽ biểu đồ dạng cột thống kê số lượng hàng đã xuất 8.
Sắp xếp lại bảng tính theo thứ tự tăng dần của Mã hàng, nếu trùng 1,2
thì sắp giảm dần theo Số lượng. 1 Tủ lạnh 0,8 0 ,6 0,4 Máy điện thoại 0,2 0 Ti vi Máy lạnh Máy photo Tủ lạnh Máy điện thoại