LAB1 Bài t¿p ôn t¿p các hàm trong Excel
CÁC HÀM TÌM KI¾M VÀ THAM CHI¾U Hàm VLOOKUP
 Cú pháp: VLOOKUP(giá trị tìm, vùng tìm, cột trả về giá trị, kiểu tìm)
 Ý nghĩa: tiến hành tìm kiếm giá trị của ô cùng hàng ở  Kiểu tìm:
- False (hoặc 0): các giá trị không cần sắp xếp tăng dần
- True (hoặc 1): các giá trị được sắp xếp tăng dần Hàm HLOOKUP
 Cú pháp: HLOOKUP(giá trị tìm, vùng tìm, hàng trả về giá trị, kiểu tìm)
 Ý nghĩa: tiến hành tìm kiếm lại giá trị của ô cùng cột ở  Kiểu tìm:
- False (hoặc 0): các giá trị không cần sắp xếp tăng dần
- True (hoặc 1): các giá trị được sắp xếp tăng dần BÀI 1
B¾NG 1 – B¾NG KÊ NH¾P HÀNG ST MÃ TÊN NGÀY SÞ THÀN THU CHI¾ TỔN T HÀNG HÀN H T G NH¾P LƯỢN ¾ G G TIÀN
VAT KHÀU TIÀN IP308G 20/10/201 70 B 7 IP416G 15/11/201 80 B 7 IP308G 10/12/201 100 B 7 IP516G 18/11/201 85 B 7 IP432G 11/10/201 60 B 7 IP416G 19/11/201 50 B 7 IP308G 25/12/201 90 B 7 IP316G 17/11/201 80 B 7
B¾NG 2: B¾NG GIÁ NH¾P HÀNG ĐƠN GIÁ (USD) MÃ HÀNG TÊN THU¾ HÀNG 08GB 16GB 32GB VAT IP3 Iphone 3 300 350 400 10% IP4 Iphone 4 400 450 500 12% IP5 Iphone 5 500 550 600 15% Yêu cầu:
1. Xác định tên hàng dựa vào 3 ký tự đầu tiên của mã hàng và tra trong Bảng 2.
2. Thành tiền = số lượng * đơn giá. Đơn giá được tra trong Bảng 2, dựa vào mã hàng
và dung lượng bộ nhớ của sản phẩm (dung lượng bộ nhớ là 4 ký tự cuối của mã hàng).
3. Thuế VAT= thành tiền * thuế VAT (%) (thuế VAT được tra trong Bảng 2).
4. Chiết khấu = 10% * thành tiền (chỉ áp dụng cho những mặt hàng có số lượng>50).
5. Tổng tiền = Thành tiền + Thuế VAT – Chiết khấu.
CÁC HÀM TÌM KI¾M VÀ THAM CHI¾U (tt) Hàm MATCH
 Cú pháp: MATCH(Trị dò tìm, vùng tìm, 0)
 Ý nghĩa: xác định vị trí tìm thấy là bảng 1 dòng hoặc 1 cột
Ví dụ: Để biết được ĐN đứng ở vị trí thứ mấy trong vùng từ A2:D2, ta dùng hàm MATCH =MATCH(<ĐN=,A2:D2)2
 Hàm MATCH thường được kết hợp với hàm INDEX để xác định tọa độ hàng cột
cần lấy dữ liệu trong 1 bảng Hàm INDEX
 Cú pháp: INDEX(Vùng tìm, hàng X, cột Y)
 Ý nghĩa: trả về dữ liệu của ô tại hàng X cột Y Ví dụ:
Để lấy dữ liệu ttrong ô C3 tương ứng với nơi đi ĐN, nơi đến SG, sử dụng hàm INDEX($B$3:$D$5,1,2)600
DANH SÁCH ĐĂNG KÝ THUÊ PHÒNG Sß Sß
TiÁn TiÁn Gi¿m Thanh STT Tên khách Ngày đ¿n Ngày đi phòng
ngày Mã DV Tên dịch vụ dịch vụ phòng giá toán 1 Thanh Thanh 103 03/01/2017 05/01/2017 LA 2 Bình Bình 106 08/03/2017 11/03/2017 FB 3 An An 307 16/04/2017 16/04/2017 FB 4 Tú Tú 207 10/04/2017 21/04/2017 BU 5 Hà Hà 401 09/05/2017 18/05/2017 FB 6 Minh Minh 204 07/05/2017 12/05/2017 LA 7 Nhi Nhi 110 09/05/2017 12/05/2017 BU 8 Loan Loan 308 07/05/2017 09/05/2017 CR 9 Hùng Hùng 205 06/05/2017 07/05/2017 BU 10 Trinh Trinh 210 09/05/2017 10/05/2017 CR BÀI 2
Biểu giá phòng (B¿ng 1) Tầng 1 2 3 4 Giá 600,000 500,000 400,000 300,000
Biểu giá dịch vụ (B¿ng 2) Mã DV 1 2 3 4 BU 50,000 120,000 80,000 20,000 CR 100,000 50,000 50,000 70,000 FB 70,000 90,000 70,000 90,000 LA 30,000 60,000 15,000 40,000
B¿ng tham kh¿o dịch vụ (B¿ng 3)
Mã DV Tên dịch vụ BU Business center CR Car Rental FB Food & Beverage LA Laundry Yêu cầu:
1. Số ngày ở = Ngày đi – Ngày đến +1
2. Tên dịch vụ: Dựa vào mã dịch vụ và dò tìm trong Bảng 3
3. Tiền dịch vụ = Số ngày ở * Giá dịch vụ (dựa vào mã dịch vụ và ký tự đầu tiên của
số phòng, dò tìm trong Bảng 2; yêu cầu: Dùng hàm INDEX, MATCH).
4. Tiền phòng = Số ngày ở * Giá phòng (dựa vào ký tự đầu tiên của số phòng và dò tìm trong Bảng 1)
5. Giảm giá: giảm 10% tiền phòng cho khách có số ngày ở >=3
6. Thanh toán = Tiền phòng + Tiền dịch vụ - Giảm giá. 1 IAC1 80000 2 EMU1 60000 3 FPS2 10000 4 SBA1 80000 5 SRE1 70000 6 GMB2 40000 7 PBI1 700000 MU Manchester-United 10 USD I Ý PS Paris SG 12 USD G RE Real-Madrit 10 USD S Tây Ban Nha AC AC-Milan 7 USD P BA Bercelona 12 USD F Pháp MB Bayem-Munich 10 USD E Anh BI Benfica 10 USD Yêu cầu
1. Dụa vào 02 ký tự giữa của Mã CLB và bảng phụ để điền vào cột tên CLB.
2. Dừa vào 01 ký tự dầu của Mã CLB và bảng phụ để điền vào cột Tên Nước.
3. Nếu ký tự cuối của Mã CLB là 1 tức là đội hạng I, 2 tức là đội hạng II. Dựa vào Mã
CLB và bảng phụ để điền vào cột giá vé, biết rằng nếu là đội hạng nhì thì giá được giảm
5%, ngược lại giá tăng 5%.
4. Doanh thu bằng Số Cổ động viên (CĐV) * Giá vé.
5. Cột Lãi/Lỗ được tính như sau: Là Lãi nếu đội hạng nhất có doanh thu >900.000, hạng
nhì doanh thu >600.000. Ngược lại Lỗ
6. Định dạng cột doanh thu có đơn vị là USD đứng sau số.