F.2 MS Excel 2003 (1) 範圍(位址) Cell Address儲存格位址 可以輸入 數字 12345

Presentation on theme: "F.2 MS Excel 2003 (1) 範圍(位址) Cell Address儲存格位址 可以輸入 數字 12345"— Presentation transcript:

1 256 65536 F.2 MS Excel 2003 (1) 範圍(位址) Cell Address儲存格位址 可以輸入 數字 12345
=ROWS(H:H) =COLUMNS(1:1) 256 A B C AA AB IU IV 1 2 3 4 5 6 7 8 A1 B1 C1 AA1 AB1 A2 B2 C2 AA2 AB2 共有多少欄? columns 共有多少行/列? rows Cell Address儲存格位址 可以輸入 數字 12345 文字 Chan Tai Man 公式 =A1*10 試一試： =gcd(18,24) =roman(1234,0) =min(-4,-6) =2^10 65536 F.2 Excel 2003

2 (a) 英文姓名 (e) 人數 (b) 總和 (f) 1A05 (c) 鄭子程 (g) 性別 (d) Chan Siu Chun (h)
班別 英文姓名 中文姓名 性別 校社 社 人數 2 1A01 Chan Chun Ho 陳進豪 M 222 3 1A02 Chan Siu Chun 陳兆俊 L 223 4 1A03 Cheng Tsz Ching 鄭子程 5 1A04 CHOI KWOK WAI 蔡國威 S 232 6 1A05 KWOK HO FUNG 郭浩豐 總和 900 7 1A06 LI KA WING 李嘉榮 1. 請分別寫出下列資料所在的儲存格位址 資料 儲存格位址 (a) 英文姓名 (e) 人數 (b) 總和 (f) 1A05 (c) 鄭子程 (g) 性別 (d) Chan Siu Chun (h) 900 B1 G6 C4 B3 H1 A6 D1 H6 F.2 Excel 2003

3 A1:A6 C5:E15 A1:B6 G1:H5 A2:E4 H:H B1:C5 2:2
D E F G H I 1 2 3 4 5 6 7 8 9 2. 試數以下範圍(試算表地址)有多少個儲存格？ (a) A1:A6 (e) C5:E15 (b) A1:B6 (f) G1:H5 (c) A2:E4 (g) H:H (d) B1:C5 (h) 2:2 16=6 26=12 53=15 25=10 311=33 25=10 65536 256 F.2 Excel 2003

4 3. 試一試：啟動Excel，任意輸入數字，選取部分數字， 在下面按右鍵，選加總或平均。 另存新檔D:\s2x99
F.2 Excel 2003

5 F.2 MS Excel 2003 (2) 範圍(位址) A B C D E F G H I 1 班別 班號 性別 中文 英文 數學 總分
平均 名次 2 2A M 48 61 49 158 52.7 84 3 47 71 68 186 62.0 30 4 31 51 112 37.3 149 5 21 39 43 103 34.3 153 : 161 2D 40 65 139 46.3 124 4. 寫出以下範圍的試算表地址。(設每班人數為40人) (a) 全級所有資料/數據 (連標題) (b) 全級中文科分數 (連標題) (c) 全級英文科分數 (連標題) (d) 全級中英數三科分數 (連標題) (e) 2A全班的數學科分數 (不連標題) (f) 學生2A(2) 的班別、班號、性別、 中英數三科分數、總分、平均分、名次 A1:I161 D1:D161 E1:E161 D1:F161 F2:F41 A3:I3 F.2 Excel 2003

6 5. 寫出以下範圍的試算表地址： 位址 儲存格數目 (a) 所有包含數字1的儲存格 (b) 所有包含數字2的儲存格 (c)
D E F G H I 1 4 2 3 45 5 13 34 345 12 123 234 2345 245 6 7 23 235 25 8 9 35 5. 寫出以下範圍的試算表地址： 位址 儲存格數目 (a) 所有包含數字1的儲存格 (b) 所有包含數字2的儲存格 (c) 所有包含數字3的儲存格 (d) 所有包含數字4的儲存格 (e) 所有包含數字5的儲存格 A2:C7 36=18 B5:F8 54=20 C4:E9 36=18 D1:H6 56=30 E3:I9 57=35 F.2 Excel 2003

7 小測: 範圍_____________內有多少個儲存格? =countblank(A1:C10)
小測: 範圍_____________內有多少個儲存格? =countblank(A1:C10) F.2 Excel 2003

8 F.2 MS Excel 2003 (3) SUM, AVERAGE, MIN, MAX
開啟Y:\ex3-4.xls，另存為D:\s2x99 試寫出下列方程式的結果： (先人手計算、再使用Excel2003) A B C D E F G H 1 2 3 4 5 6 7 8 9 10 儲存格 方程式/公式 顯示結果 I1 =A1+A2+A3 I2 =SUM(A1,A2,A3,A4) I3 =SUM(A1:A5) I4 =AVERAGE(A1:A5) I5 =SUM(A1:A5)+SUM(C1:C5) I6 =SUM(A1:A5,C1:C5) I7 =SUM(A6:C10) I8 =SUM(A1:D10) I9 =SUM(E1:H10) I10 =SUM(A1:H10) 6 9 10 2 23 23 34 85 74 159 SUM(和)；AVERAGE(平均)； MIN(最小)；MAX(最大)。 F.2 Excel 2003

9 試寫出下列方程式的結果：(先人手計算、再使用Excel2003)
A B C D E F G H 1 2 3 4 5 6 7 8 9 10 儲存格 方程式/公式 顯示結果 J1 =MIN(A1:H10) 1 J2 =MAX(A1:H10) 3 J3 =MIN(AVERAGE(B2:C3), AVERAGE(D4:E5)) 2 J4 =MAX(MAX(F1:F10), AVERAGE(F1:F10)) J5 =MIN(SUM(F:F),SUM(G:G)) 15 J6 =MAX(C4-D4,0) J7 =MIN(SUM(D7:F10),12) 12 J8 =A1*A2*A3*A4 18 J9 =SUM(E6:E10)/4 2.5 J10 =SUM(F6:H7)^2 100 SUM(和)；AVERAGE(平均)； MIN(最小)；MAX(最大)。 F.2 Excel 2003

10 F.2 MS Excel 2003 (4) (運算子、內建函數)
用途 例子 + 加 = A1+A2  減 = A1A2 * 乘 = A1*A2 / 除 = A1/A2 % 百分率 = A1*20% ^ 次方 = A1^2 在儲存格輸入數字及公式： F.2 Excel 2003

11 開啟Y:\ex3-4.xls，另存為D:\s2x99
A B C D 1 旅行 全班人數 每人付款 總數 2 40 $50 $2,000 3 食物 每人份量 價錢 金額 4 雞翼 $2 $4 5 雞脾 $5 6 牛丸 $0.2 $1 7 燒义 8 飲品 $8 9 $800 10 尚餘 $1,200 =B2*C2 F.2 Excel 2003

12 F.2 MS Excel 2003 (5) (水費單) A B C D E F G 1 耗水量 (m3) 收費 ($/m3) 用水量 65
應付 2 首 10 $0  3 其次 30 $3 4 20 $6 5 其餘 $9 6 共付 收費/耗水量 9 m3 35 m3 45 m3 65 m3 $0 $3 $6 $9 應付水費 9 10 25 0+75 10 30 5 10 30 20 5 F.2 Excel 2003

13 F.2 MS Excel 2003 (5) (水費單) 1. 開啟Y:\water-bill.xls，另存新檔D:\s2x99 A B C
G 1 耗水量 (m3) 收費 ($/m3) 用水量 65 應付 2 首 10 $0  3 其次 30 $3 4 20 $6 5 其餘 $9 6 共付 10 55 $0 $90 $120 $45 $255 30 25 20 5 5 = 右上(F1) 或 左(B2) (選其中最小的一個) 貯存格F2, F3, F4 剩餘=上(F1)－左(E2) 應付 =收費C2 *用水量E2 F.2 Excel 2003

14 2. 計算應付金額：G2 = 收費(C2) *用水量(E2) 並複製貯存格：G2G3G4G5
3. 計算共付金額：G6 = G2+G3+G4+G5 (利用sum(...)) 4. 輸入公式： E2 = 右上貯存格(F1)的值 或 左邊貯存格(B2)的值 (兩者選其中最小的一個) F2 = 上面貯存格(F1)的值－左邊貯存格(E2)的值 5. 複製貯存格： E2E3E4E5， F2F3F4， G2G3G4G5 6. 更改F1的值 (用水量) 來測試一下 7. 計算總分sum、個人平均分average、 各科平均分、最低min及最高max分數 8. 把不合格分數 (B10:F19) 自動轉為紅色。 (格式設定格式化條件小於50...) F.2 Excel 2003

15 F.2 MS Excel 2003 (6) (複製公式) 相對位址(A1)及 絕對位址($A$1) A B C D E F G 1 4 7
8 3 9 =MAX(A1:F1) 2 5 6 12 (g) =SUM(A1:A3) (b) (c) (a) =SUM(A$1:A$3) (e) (f) (d) 向下複製： +數字 123 向右複製： +字母 ABC 儲存格A4及A6所顯示的數值是多少)? 6 儲存格 公式 複製至 新公式 顯示數值 (a) A4 =SUM(A1:A3) A4A5 (b) A4B4 (c) A4C4 (d) A6 =SUM(A$1:A$3) A6A7 (e) A6B6 (f) A6C6 (g) G1 =MAX(A1:F1) G1G3 =SUM(A2:A4) 11 15 24 6 12 =SUM(B1:B3) =SUM(C1:C3) =SUM(A$1:A$3) =SUM(B$1:B$3) =SUM(C$1:C$3) F.2 Excel 2003 =MAX(A3:F3)

16 Q2. 測試題：複製公式 右4 下10 儲存格A51公式 複製A51E51後：新公式 複製A51A61後：新公式 (w) =C3+D4
(x) =C$3+D$4 (y) =$C3+$D4 (z) =$C$3+$D$4 =G3+H4 =C13+D14 =G$3+H$4 =C$3+D$4 =$C3+$D4 =$C13+$D14 =$C$3+$D$4 =$C$3+$D$4 Q3. 應用：複製公式 A B C D E F 1 2 3 10 公式 顯示數值 4 =A3+A4 11 =A$3+A4 5 6 7 8 向下複製 =A4+A5 3 =A5+A6 5 =A6+A7 7 =A7+A8 9 =A$3+A5 12 =A$3+A6 13 =A$3+A7 14 =A$3+A8 15 F.2 Excel 2003

17 向下複製 Q4填充題：複製公式 A B C D E F G 1 耗水量 (m3) 收費 ($/m3) 用水量 65 應付 2 首 10 $0
=MIN(B2,F1) =F1-E2 =C2*E2 3 其次 30 $3 4 20 $6 5 其餘 $9 6 共付 向下複製 =MIN(B3,F2) =F2-E3 =C3*E3 =MIN(B4,F3) =F3-E4 =C4*E4 =MIN(B5,F4) =F4-E5 =C5*E5 F.2 Excel 2003

18 Q5 A B C D 1 旅行 全班人數 每人付款 總數 2 40 $50 =B2*C2 3 食物 每人份量 價錢 金額 複製D2D4 4
雞翼 $2 5 雞脾 $5 6 牛丸 $0.2 7 燒义 8 飲品 $4 9 10 尚餘 F.2 Excel 2003

19 將它向下及向右複製，成為一個正確的乘數表。 A B C D E F
Q6挑戰題： 改寫B2公式($$$)， 將它向下及向右複製，成為一個正確的乘數表。 A B C D E F 1 乘數表 2 3 4 5 =A2*B1 6 7 8 =B2*C1 =C2*D1 =D2*E1 =E2*F1 =A3*B2 =A4*B3 =A5*B4 =A6*B5 F.2 Excel 2003

20 F.2 MS Excel 2003 (7) VLOOKUP 1. 開啟【Y:\課外活動.xls】，另存新檔 D:\s2x99
2. 更改名稱：Students(A1:E900), Award, Society, Rank 選取儲存格 (按Ctrl-Shift-8) 工作表『獎勵』Award 代號 項目 A01 記優點一次 A02 記優點二次 注意： 範圍第1欄 必須是key 工作表『學會』Society 代號 項目 項目 (英文) S01 中文學會 CHINESE SOCIETY S02 英文學會 ENGLISH SOCIETY 工作表『職位』Rank 代號 項目 項目 (英文) R01 主席 Chairman R02 副主席 Vice chairman F.2 Excel 2003

21 輸入公式 =VLOOKUP(A2,Students,3,FALSE)
3. 在工作表『課外活動』儲存格H2， 輸入公式 =VLOOKUP(A2,Students,3,FALSE) =VLOOKUP(尋找目標，搜尋範圍，傳回欄位，近似程度) 尋找目標：A2 (2A01) 搜尋範圍：Students (即A1:E900) 傳回欄位：第3欄 (姓名) 近似程度：FALSE準確 (TRUE近似) 4. 將班號 (A2) 改為自己的班號 (例如：2A01) 看看 5. 在儲存格 H2..L2，輸入適當的公式 (VLOOKUP)，輸出以下資料。 H I J K L 1 姓名 學會 職位 記功原因 優點/小功 2 陳大文 電腦學會 幹事 擔任學會職務 記優點一次 3 F.2 Excel 2003

22 6. 在工作表『學生資料』儲存格H2:H6內，輸入公式，計算各社人數。
=COUNTIF(E:E,"C") 或 =COUNTIF(E:E,G2) A B C D E F G H 1 班別 姓名 (英文) 姓名(中文) 性別 校社 社 人數 2 1A01 Chan Kam Pong 陳錦邦 M 222 3 1A02 Cheng Kai Hong 鄭啟康 L 223 4 1A03 Chow Chun Hei 周竣熹 5 1A04 Fung Hok Wang 馮學弘 S 232 6 1A05 Hau Yu Hin 侯宇軒 總 900 7. 利用COUNTIF數一數 『男女』人數、 各班人數、 各姓氏(陳李張王黄何...)人數。 F.2 Excel 2003

23 F.2 MS Excel 2003 (8) (VLOOKUP) 1. 資料搜尋VLOOKUP (利用班號作key，來找尋個人資料) A B
D E F G 1 班別 姓名(英文) 姓名(中文) 性別 校社 2 1A01 Chan Kam Pong 陳錦邦 M 班號： 2A01 3 1A02 Cheng Kai Hong 鄭啟康 姓名： 4 1A03 Chow Chun Hei 周竣熹 性別： 5 1A04 Fung Hok Wang 馮學弘 L 校社： 6 1A05 Hau Yu Hin 侯宇軒 S 999 7B30 WONG SO WA 黃素華 注意： 範圍第1欄 必須是key 2. 更改名稱：Students (代表範圍A1:E999) =VLOOKUP(尋找目標，搜尋範圍，傳回欄位，近似程度) 尋找目標：G2 (2A01) 搜尋範圍：Students (即A1:E999) 傳回欄位：第3欄 (中文姓名) 近似程度：FALSE準確 (TRUE近似) F.2 Excel 2003

24 =VLOOKUP(G2,Students,1,FALSE)
B C D E F G 1 班別 姓名(英文) 姓名(中文) 性別 校社 2 1A01 Chan Kam Pong 陳錦邦 M 班號： 2A01 3 1A02 Cheng Kai Hong 鄭啟康 姓名： ? 4 1A03 Chow Chun Hei 周竣熹 性別： 5 1A04 Fung Hok Wang 馮學弘 L 校社： 6 1A05 Hau Yu Hin 侯宇軒 S 999 7B30 WONG SO WA 黃素華 3. 在儲存格G3G5輸入公式 儲存格G2 儲存格G3/G5輸入公式 儲存格G3/G5顯示值 (a) 1A01 =VLOOKUP(G2,Students,1,FALSE) (b) 1A04 =VLOOKUP(G2,Students,2,FALSE) (c) 1A03 =VLOOKUP(G2,Students,3,FALSE) (d) 7B30 =VLOOKUP(G2,Students,4,FALSE) 1A01 Fung Hok Wang 周竣熹 F F.2 Excel 2003

25 4. 應用例子：products (代表範圍A1:E9999)
B C D E 1 產品編號 牌子 型號名稱 規格 價錢 2 CPU-AMD-101 AMD ATHLON SOCKET754 / 2800MHz / 512KB 946 3 CPU-Intel-001 Intel CELERON D 320 SOCKET478 / 2400MHz / 256KB 562 4 MB-AsusTek-008 ASUSTek K8N4-E DELUXE nForce4 SLI / ATX / SOCKET754 1,105 5 DVD-LG-001 LG GSA-4160B BLACK DVD±RW / Internal / IDE 499 寫出G5公式： F產品編號 G產品型號名稱 H數量 I單價 5 CPU-AMD-104 ATHLON (939) 1 $1,541 6 RAM-Kingston-058 PC ECC+REG 2 $590 7 HDD-Hitachi-012 25VLSA80 $1,138 8 Display-Sapphire-013 RADEON X850XT PE $4,200 總金額： $8,059 寫出I8公式： F.2 Excel 2003

26 F.2 MS Excel 2003 (9) (VLOOKUP) 1. 更改名稱：zodiac (代表範圍A2:D14) animals (代表範圍F2:H13) A B C D E F G H 1 月 日 Horoscope 星座 年 Animals 生肖 2 Capricorn 山羊座 Monkey 猴 3 20 Aquarius 水瓶座 Hen 雞 4 19 Pisces 雙魚座 Dog 狗 5 21 Aries 白羊座 Pig 豬 6 Taurus 金牛座 Mouse 鼠 7 Gemini 雙子座 Cow 牛 8 22 Cancer 巨蟹座 Tiger 虎 9 23 Leo 獅子座 Rabbit 兔 10 24 Virgo 處女座 Dragon 龍 11 Libra 天秤座 Snake 蛇 12 Scorpio 天蠍座 Horse 馬 13 Sagittarius 人馬座 Goat 羊 14

27 2. 利用YEAR, MONTH, DAY 將出生日期化為：年、月、日 (C21:C23)
B C D E F 20 出生日期： 1988/10/20 21 年： 1988  8  生肖： 龍 22 月： 10 9  星座： 天秤座 23 日： 儲存格 公式 若在C20輸入=TODAY()，顯示數值 (a) C21 =YEAR(C20) (b) C22 =MONTH(C20) (c) C23 =DAY(C20) (d) D21 =MOD(C21,12) (e) D22 =C22-IF(C23<D23,1,0) 利用VLOOKUP找出星座及生肖 (f) D23 (g) F21 (h) F22