Giai đề tin đại cương 1: Các hàm thường gặp trong Excel | Tin học đại cương | Học viện Tài chính

Hà Tĩnh
CÁC HÀM THƯỜNG GẶP TRONG EXCEL 1) HÀM KÍ TỰ ● Hàm RIGHT ● Hàm LEFT ● Hàm MID ● Hàm VALUE ● Hàm LEN 2) HÀM LOGIC ● Hàm IF ● Hàm OR ● Hàm AND 3) HÀM NGÀY THÁNG ● Hàm DATE ● Hàm TODAY ● Hàm NOW 4) HÀM THỐNG KÊ ● Hàm SUM / SUMIF/ SUMIFS
● Hàm COUNT / COUNTIF / COUNTIFS ● Hàm AVERAGE / AVERAGEA ● Hàm MIN / MAX ● Hàm SMALL / LARGER 5) HÀM TÌM KIẾM ● Hàm VLOOKUP ● Hàm HLOOKUP ● Hàm INDEX ● Hàm MATCH
6) HÀM CƠ SỞ DỮ LIỆU
CÁC BÀI TẬP RÈN LUYỆN Hà Tĩnh BÀI 1: CÂU 1: ⇨ HÃNG SẢN XUẤT
C3=VLOOKUP(RIGHT(B3,1),$H$16:$I$19,2,0)
Copy công thức từ ô C3 đến ô C12 ⇨ Phân phối ( CC ) D3= LEFT(B3,3)
Copy công thức từ ô D3 đến ô D12 ⇨ Nhãn hiệu Hà Tĩnh
E3=INDEX($C$21:$F$24,MATCH(C3,$B$21:$B$24,0),MATCH(VALUE(D3),$ C$20:$F$20,TRUE))
Copy công thức từ ô E3 đến ô E12 ⇨ Thời gian bảo hành
H3=HLOOKUP(VALUE(LEFT(B3,2)),$C$15:$F$17,3,0)
Copy công thức từ ô H3 đến ô H12 CÂU 2:
⇨ Ngày hết hạn bảo hành
I3 =DATE(YEAR(F3)+1,MONTH(F3)+6,DAY(F3))
Copy công thức từ ô I3đến ô I12 CÂU 3:
Cách 1: =SUMIF(E3:E12,"Fx",G3:G11) Cách 2: B C 34 Nhãn hiệu Tổng số lượng 35 Fx =DSUM(A2:I12,G2,B34:B35) CÂU 4: B C D 38 Hãng Tháng 10/2018
sản xuất Số lượng hóa đơn mua xe Suzuki trong tháng 10/ 2018 39
=AND(MONTH(10),YEAR(2018)) Suzuki =DCOUNT(A2:I12,G2,B38:C39) Hà Tĩnh CÂU 5: B C 41 Số lượng Mã hóa đơn 42 =MIN(G3:G12) =DGET(A2:I12 B2 B41:B42) BÀI 2: CÂU 1: ⇨ Diễn giải:
● C3=IF(LEFT(RIGHT(B3,4),2)="VM","Vinamilk",IF(LEFT(RIGHT(B3,4),2)=
"TH","TH True Milk","Mộc châu"))&", "&" Loại " & LEFT(RIGHT(B3,2),1)
&", "& IF(RIGHT(B3,1)="L","Bán lẻ","Bán buôn") Hà Tĩnh
● COPY công thức từ ô C3 đến ô C7 CÂU 2: ⇨ Ngày bán :
● D3=DATE(MID(B3,5,4),MID(B3,3,2),LEFT(B3,2))
● Copy công thức từ ô D3 đến ô D7 ⇨ Ngày hết hạn:
● F3=DATE(YEAR(E3),MONTH(E3),DAY(E3)+IF(J3="Vinamilk",10,IF(J3=" TH True Milk",11,12)))
● Copy công thức từ ô F3 đến ô F7 ⇨ Số lượng :
● G3 = VALUE(MID(B3,9,LEN(B3)-12))
● Copy công thức từ ô G3 đến ô G7 CÂU 3: ⇨ ĐƠN GIÁ :
● H3=1000*INDEX($D$11:$F$13,MATCH(LEFT(RIGHT(B3,4),2),$G$11:$G
$13,0),IF(VALUE(LEFT(RIGHT(B3,2),1))=1,1,IF(VALUE(LEFT(RIGHT(B3
,2),1))=2,2,3)))*IF(RIGHT(B3,1)="L",100%,93%)
● Copy công thức từ ô H3 đến ô H7 CÂU 4 : ⇨ Thành tiền:
● I3 =G3*H3*IF(RIGHT(B3,2)="2B",97%,100%)
● Copy công thức từ ô I3 đến ô I7 CÂU 5: B C 27 ĐK Số hóa đơn 28 =DAY(F3)-DAY(D3)=1 =DCOUNTA(A2:J7 B2 B27:B28) Hà Tĩnh BÀI 3: CÂU 1: ⇨ Diễn giải:
● C3=VLOOKUP(LEFT(RIGHT(B3,5),2),$A$20:$B$22,2,0) &" "& RIGHT(B3,1) & "KVA"
● COPY công thức từ ô C3 đến ô C12 ⇨ Thế hệ :
● E3 =IF(LEFT(RIGHT(B3,3),2)="DR","Thế hệ II","Thế hệ I")
● COPY công thức từ ô E3 đến ô E12 ⇨ Số lượng
● F3=VALUE(LEFT(B3,LEN(B3)-5)) Hà Tĩnh
● COPY công thức từ ô F3 đến ô F12 CÂU 2: ⇨ Đơn giá :
● G3=INDEX($C$20:$H$22,MATCH(LEFT(RIGHT(B3,5),2),$A$20:$A$22,0),
MATCH(RIGHT(B3,3),$C$19:$H$19,0))*10^6
● COPY công thức từ ô G3 đến ô G12 CÂU 3: ⇨ Thành tiền :
● H3 =F3*G3*IF(E3="Thế hệ I",90%,80%)
● COPY công thức từ ô H3 đến ô H12 CÂU 4: K L M 18 Mã hợp đồng Thế hệ Tổng tiền 19 *ST* Thế hệ I =DSUM(A2:H12 H2 K18:L19) CÂU 5: K L M 5 Diễn giải Số lượng Mã hợp đồng 6 LIOA 3KVA =DMAX(A2:I12 F2 K5:K6) = DGET(A2:I12 B2 K5:L6) Hà Tĩnh BÀI 4: CÂU 1: ⇨ Diễn giải:
● C3 ="Mã hộ" &" " & MID(B3,3,LEN(B3)-4)&" "&"quận"&" "&
HLOOKUP(RIGHT(B3,2),$C$11:$F$12,2,0)
● COPY công thức từ ô C3 đến ô C8 ⇨ Tháng sử dụng: ● D3 =VALUE(LEFT(B3,2))
● COPY công thức từ ô D3 đến ô D8 CÂU 2: ⇨ Số sử dụng: ● G3= F3 – E3
● COPY công thức từ ô G3 đến ô G8 ⇨ Tiền phải trả:
● H3=G3*IF(G3<=100,1242,IF(G3<=150,1369,IF(G3<=200,1734,IF(G3<=300,
1874,IF(G3<=400,2008,2060)))))*110%
● COPY công thức từ ô H3 đến ô H8 Hà Tĩnh CÂU 3: H I J 13 Tháng sử dụng ĐK Tổng 14 2 =G3>=400 =DSUM(A2:H8 G2 H13:I14) CÂU 4 : G H 16 Tiền phải trả Mã khách hàng 17 =LARGE(H3:H8 2) =DGET(A2:H8 B2 G16:G17) CÂU 5:
⇨ Lập cột phụ I ( Số tiền bình quân ) ● I3=H3/D3
● COPY công thức từ I3 đến I8
⇨ Số lượng hóa đơn có tiền phải trả từ 1 triệu đồng/1 tháng trở lên. E F 19 ĐK Số lượng hóa đơn 20 =I3>10^6 =DCOUNT(A2:I8 I2 E19:E20) Hà Tĩnh BÀI 5: CÂU 1: ⇨ Diễn giải:
● C3=VLOOKUP(LEFT(RIGHT(B3,4),2),$B$11:$C$14,2,0)&"
"&IF(VALUE(LEFT(B3,1))=1,"Loại 1","Loại 2")
● COPY công thức từ ô C3 đến ô C7 CÂU 2: ⇨ Ngày:
● D3 =DATE(2014,11,RIGHT(B3,2))
● COPY công thức từ ô D3 đến ô D7 ⇨ Số lượng
● E3 =VALUE(MID(B3,2,LEN(B3)-5))
● COPY công thức từ ô E3 đến ô E7 ⇨ Đơn vị tính :
● F3 =VLOOKUP(LEFT(RIGHT(B3,4),2),$B$11:$F$14,5,0)
● COPY công thức từ ô F3 đến ô F7 Hà Tĩnh ⇨ Đơn giá :
● G3=22000*INDEX($D$11:$E$14,MATCH(LEFT(RIGHT(B3,4),2),$B$11:$B
$14,0),IF("Loại "&" "&VALUE(LEFT(B3,1))="Loại 1",1,2))
● COPY công thức từ ô G3 đến ô G7 ⇨ Thành tiền :
● H3 =E3*G3*IF(AND(E3>50,LEFT(B3,1)=2,DAY(D3)<=5),95%,100%)
● COPY công thức từ ô H3 đến ô H7 CÂU 3: K L 2 ĐK Tổng thành tiền
3 =AND(VALUE(LEFT(B3 1))=1 D3=DATE(2014 11 5)) =DSUM(A2:H7 H2 K2:K3) CÂU 4 : K L M 5 Diễn giải Thành tiền Mã hóa đơn 6
Quần kaki Loại 2 =DMIN(A2:H7 H2 K5:K6) =DGET(A2:H7 B2 K5:L6) CÂU 5:
Số tiền chênh lệch giữa các hóa đơn bán hàng loại 2 và hóa đơn bán hàng loại 1 :
=SUMIF(B3:B7,"2*",H3:H7)-SUMIF(B3:B7,"1*",H3:H7) Hà Tĩnh BÀI 6:
Ý nghĩa mã hóa đơn: 3 kí tự đầu chỉ mã nhà sản xuất ( tính từ trái sang phải ), tính từ
phải sang trái 3 kí tự đầu chỉ mã loại sản phẩm, 2 kí tự tiếp theo chỉ kích thước chân, các
kí tự còn lại chỉ số lượng YÊU CẦU:
1. Điền vào cột đơn hàng, số lượng, cỡ giày.
Ví dụ : Với đơn hàng có mã ADS1315SPT điền Giày thể thao Adidas vào cột đơn
hàng, 13 vào cột số lượng và L vào cột cỡ giày.
2. Điền vào cột đơn giá biết giá cỡ L và XL lấy từ bảng tham chiếu; còn giày cỡ S và
M thì tăng 3% so với cỡ L và XL cùng loại.
3. Điền vào cột thành tiền biết Thành tiền = Số lượng * Đơn giá,
Những đơn hàng bán từ ngày 15/5 đến 10/6 đều bị phụ thêm 10,000 VNĐ/1 sản phẩm.
4. Tính tổng số lượng giày NIKE đã bán.
5. Tìm thành tiền lớn nhất của đơn giày cỡ S và M trong quý 2. Hà Tĩnh CÂU 1: ⇨ Đơn hàng :
● D3 =HLOOKUP(RIGHT(B3,3),$D$15:$F$16,2,0)&" "&
VLOOKUP(LEFT(B3,3),$B$17:$C$19,2,0)
● COPY công thức từ ô D3 đến ô D12 ⇨ Số lượng
● E3=VALUE(MID(B3,4,LEN(B3)-8))
● COPY công thức từ ô E3 đến ô E12 ⇨ Cỡ giày
● F3=IF(VALUE(LEFT(RIGHT(B3,5),2))<=12,"S",IF(VALUE(LEFT(RIGHT(
B3,5),2))<=15,"M",IF(VALUE(LEFT(RIGHT(B3,5),2))<=18,"L","XL")))
● COPY công thức từ ô F3 đến ô F12 CÂU 2: Đơn giá
● G3=1000*INDEX($D$17:$F$19,MATCH(LEFT(B3,3),$B$17:$B$19,0),
MATCH(RIGHT(B3,3),$D$15:$F$15,0))*IF(OR(F3="L",F3="XL"),1,103%)
● COPY công thức từ ô G3 đến ô G12 CÂU 3: Thành tiền:
● H3=E3*G3+IF(AND(C3>=DATE(2018,5,15),C3<=DATE(2018,6,10)),10000 *E3,0)
● COPY công thức từ ô H3 đến ô H12 J K 2 ĐK TỔNG 3 =LEFT(B3 3)="NKE" =DSUM(A2:H12 E2 J2:J3) CÂU 4: CÂU 5: J K L 5 Đk1 Đk2 Thành tiền
6 =OR(F3="S" F3="M") =AND(MONTH(C3)>=3 MONTH(C3)<=6) =DMAX(A2:H12 H2 J5:K6) Hà Tĩnh BAI 7: YÊU CẦU:
1. Điền dữ liệu cho cột Diễn giải, Ngày bán, Số lượng, Đơn giá.
Ví dụ: Dòng 3 có mã MA3LO27052012 thì Diễn giải điền Loa xuất xứ Malaisia,
Ngày bán điền 27/05/2012, Số lượng điền 3, Đơn giá điền 1290000
2. Tính thành tiền ( đvt:VNĐ), biết:
- Thành tiền = Số lượng * Đơn giá
- Những hợp đồng của các mặt hàng có xuất xứ nước ngoài, với sô lượng lớn
hơn 200 bán trong quí một thì được chiết khấu 5%
3. Điền phí vận chuyển biết phí vận chuyển = tỷ lệ vận chuyển*Thành tiền
Mặt hàng nội địa được miễn phí vận chuyển
4. Tính tổng tiền bán chuột hoặc bàn phím trong 6 tháng đầu tiên
5. Đưa ra mã hợp đồng của mặt hàng nước ngoài có số lượng lớn nhất CÂU 1: ⇨ Diễn giải:
● C3=IF(LEFT(RIGHT(B3,10),2)="LO","Loa",IF(LEFT(RIGHT(B3,10),2)="C
H","Chuột","Bàn phím")) &" xuất xứ "&
HLOOKUP(LEFT(B3,2),$C$10:$E$11,2,0)
● COPY công thức từ ô C3 đến ô C6 ⇨ Ngày bán
● D3=DATE(RIGHT(B3,4),MID(RIGHT(B3,8),3,2),MID(RIGHT(B3,8),1,2)) Hà Tĩnh
● COPY công thức từ ô D3 đến ô D6 ⇨ Số lượng
● E3 =VALUE(MID(B3,3,LEN(B3)-12))
● COPY công thức từ ô E3 đến ô C6 ⇨ Đơn giá
● F3=21500*INDEX($C$12:$E$14,MATCH(LEFT(RIGHT(B3,10),2),$G$12:$
G$14,0),MATCH(LEFT(B3,2),$C$10:$E$10,0))
● COPY công thức từ ô F3 đến ô F6 CÂU 2: Thành tiền
● G3=E3*F3*IF(AND(OR(LEFT(B3,2)="MA",LEFT(B3,2)="TQ"),E3>200, MONTH(D3)<4),95%,100%)
● COPY công thức từ ô G3 đến ô G6 CÂU 3: Phí vận chuyển :
● H3=G3*IF(LEFT(B3,2)="VN",0,IF(LEFT(RIGHT(B3,10),2)="LO",3%,
IF(LEFT(RIGHT(B3,10),2)="CH",0%,1%)))
● COPY công thức từ ô H3 đến ô H6 CÂU 4:
● Lập vùng điều kiện: B 17 ĐK 18
=G3*IF(LEFT(B3,2)="VN",0,IF(LEFT(RIGHT(B3,10),2)="LO",3%,
IF(LEFT(RIGHT(B3,10),2)="CH",0%,1%)))
● Viết hàm: =DSUM(A2:H6,G2,B17:B18) CÂU 5:
● Lập cùng điều kiện: B C 20 ĐK1 Số lượng Hà Tĩnh
21 = OR( LEFT(B3,2)="MA",LEFT(B3,2)="TQ") =DMAX(A2:H6,E2,B20:B22)
● Viết hàm: =DGET(A2:H6,B2,B20:C21) BÀI 8:
CHÚ THÍCH: Tính từ phải sang trái 2 kí tự đầu tiên chỉ khoảng cách, 2 kí tự tiếp theo
chỉ thời điểm giao hàng , 3 kí tự tiếp theo chỉ đối tượng khách hàng, 2 kí tự tiếp theo chỉ
mã loại hàng, các kí tự còn lại chỉ số lượng. YÊU CẦU :
1. Điền vào các cột Loại hàng, Số lượng và Đơn giá
Ví dụ : với mã 5TSVIP0508 Điền Đặc biệt vào cột Loại khách hàng, 5 vào cột Số
lượng và 15 vào cột Đơn giá Hà Tĩnh
2. Điền vào cột thời điểm giao hàng trước 6 giờ và sau 22 giờ thì sẽ không giao hàng
( đánh dấu X vào cột thời điểm xuất phát )
3. Điền vào cột khuyến mại biết những hóa đơn giao hàng ngày 25/11/2017 đến
05/12/2017 thì khuyến mại 10%
4. Thành tiền = Số lượng * Đơn giá + giá ship – khuyến mãi
5. Tính tổng thành tiền của các hóa đơn bán Bánh khúc
6. Tìm số lượng các đơn hàng không phải ship trong tháng hiện tại
7. Tính thành tiền cao nhất của các đơn hàng các loại bánh bán cho sinh viên và giao hàng từ 18h đến 22 h
8. Tìm mã hóa đơn có thành tiền cao nhất trong tháng 11 CÂU 1: ⇨ Loại khách hàng:
● D3 =HLOOKUP(LEFT(RIGHT(B3,7),3),$C$16:$E$17,2,0)
● COPY công thức từ ô D3 đến ô D13 ⇨ Số lượng
● E3 =VALUE(MID(B3,1,LEN(B3)-9))
● COPY công thức từ ô E3 đến ô E13 ⇨ Đơn giá
● F3=1000*INDEX($C$18:$E$22,MATCH(LEFT(RIGHT(B3,9),2),$A$18:$A$
22,0),MATCH(LEFT(RIGHT(B3,7),3),$C$16:$E$16,0))
● COPY công thức từ ô F3 đến ô F13 CÂU 2:
⇨ Thời điểm xuát phát :
● G3=IF(AND(VALUE(LEFT(RIGHT(B3,4),2))>=6,VALUE(LEFT(RIGHT (
B3,4),2))<=22),VALUE(LEFT(RIGHT(B3,4),2)) -
IF(VALUE(RIGHT(B3,2))<15,1,IF(AND(VALUE(RIGHT(B3,2))>=15,
VALUE(RIGHT(B3,2))<30),2,3)),"X")
● COPY công thức từ ô G3 đến ô G13 CÂU 3: ⇨ Khuyến mại:
● H3=E3*F3*IF(OR(AND(MONTH(C3)=11,DAY(C3)>=25),AND(MONTH( C3)=12,DAY(C3)<=5)),10%,0)
● COPY công thức từ ô H3 đến ô H13 Hà Tĩnh CÂU 4: ⇨ Thành tiền : ● I3=E3*F3-
H3+1000*IF(VALUE(RIGHT(B3,2))>30,25,IF(VALUE(RIGHT(B3,2))>15,2
0,IF(VALUE(RIGHT(B3,2))>5,15,IF(VALUE(RIGHT(B3,2))>2,10,0))))
● COPY công thức từ ô I3 đến ô I13 CÂU 5: F 24 ĐK 25 =LEFT(RIGHT(B3,9),2)="BK"
● Lập vùng điều kiện
● Viết hàm : =DSUM(A2:I13,I2,F24:F25) CÂU 6:
⇨ Lập cột phụ J ( tại ô J2 với tên trường là Đơn ship)
● J3=IF(AND(VALUE(LEFT(RIGHT(B3,4),2))>=6,VALUE(LEFT(RIGHT(B3,
4),2))<=22),"ship","không")
● COPY công thức từ ô J3 đến ô J13 B 27 ĐK1
28 =AND(J3="KHÔNG",MONTH(C3)=1)
⇨ Lập vùng điều kiện :
⇨ Viết hàm : =DCOUNTA(A2:J13,E2,B27:B28) Hà Tĩnh CÂU 7:
⇨ Lập vùng điều kiện: B 30 ĐK2 31
=AND(LEFT(RIGHT(B3,7),3)="STD",LEFT(RIGHT(B3,9),1)="B",
VALUE(LEFT(RIGHT(B3,3),2))>=18,VALUE(LEFT(RIGHT(B3,4),2))<=22)
⇨ Viết hàm : =DMAX(A2:J13,I2,B30:B31) CÂU 8 :
⇨ Lập vùng điều kiện : B C 33 ĐK3 Thành tiền 34 =MONTH(C3)=11 =DMAX(A2:J13,I2,B33:B34)
⇨ Viết hàm : =DGET(A2:J13,B2,B33:C34) Hà Tĩnh BÀI 9: YÊU CẦU :
1. Điền dữ liệu cho các cột Tên – Loại hàng, Đơn vị tính, Số lượng và Đơn giá
Ví dụ : Nếu số phiếu có giá trị XMA200 thì giá trị các cột cần điền dữ liệu như
sau: Tên – Loại hàng là Xi măng lọai A; Đơn vị tính: Bao ; Số lượng là 200; Đơn giá là 310000
2. Tính Thành tiền, Biết rằng: Thành tiền = Số lượng * Đơn giá
Biết rằng Các phiếu xuất ngày 10 hàng tháng được giảm 7% giá trị đơn giá
3. Xác định số lần xuất mặt hàng Xi măng
4. Tính tổng số tiền bán Xi măng trong tháng 10 năm 2017
5. Tìm số phiếu có giá trị Thành tiền lớn nhất ( Giả định giá trị là duy nhất ) CÂU 1: ⇨ Tên – Loại hàng :
● D3=VLOOKUP(LEFT(B3,2),$B$18:$C$20,2,0) &" " &
HLOOKUP(MID(B3,3,1),$C$13:$E$14,2,0)