Preface 序文序 Excel Excel 3 3 3 Excel Excel Excel * Excel... *... * 110/2 1/2... Excel Excel Excel Excel 3 Excel 3 VBA * Excel Excel ii
* 40 文Preface 序* VBA 7 12 Excel Excel * 產業沒有景氣的問題, 只有競爭力的問題 * 今天不努力工作, 明天就得努力找工作 * 從業人員也沒有裁員 減薪的問題, 只有工作能力的問題 10 100 林文恭 2012/02/04 iii
Contents 目錄iv 目錄 PART 1 01 基本作業能力... 003 Theme 01 Excel 2010... 004... 004... 005... 005... 005... 006 1... 006 2... 007... 007 Theme 02... 008 Theme 03... 008 Theme 04... 010 Theme 05...011... 012... 012... 013... 014 Theme 06... 015... 015... 015... 016
Contents 目錄v... 016... 018 Theme 07... 019 02 成績單... 021 Theme 01... 023 Theme 02... 027 Theme 03... 029 Theme 04... 031... 033... 033 Theme 05... 034... 034 2003... 035 2010... 036 Theme 06... 037... 037... 038 03 大型試算表... 039 Theme 01... 040 Theme 02... 043... 043... 044... 044... 044... 045
Contents 目錄vi Theme 03... 045... 045... 045... 046... 046... 047... 047... 048... 050... 051... 051... 052 Theme 04... 052... 053 Theme 05... 055 B3:U3... 055 04 資料處理好工具... 057 Theme 01... 059... 059 1... 060 2... 061 3... 062 Theme 02... 064... 065... 069 Theme 03... 072... 072... 073
Contents 目錄vii... 074... 076 Theme 04... 077... 077... 079... 081 Theme 05... 082... 082... 084... 085 PART 2 05 進階成績單... 099 Theme 01... 102 Theme 02... 104 Theme 03... 107 Theme 04... 109 Theme 05...111...111...112...114 Theme 06...114...115...115...117 Theme 07...118
Contents 目錄viii...118...118...119...119 Theme 08... 120 Theme 09 EXCEL... 121... 122 06 文字遊戲... 123 Theme 01... 126 Theme 02... 128 Theme 03... 129... 130... 130 Theme 04... 132... 132... 134... 135 Theme 05... 138 F5... 139... 139 07 萬年曆... 141 Theme 01 9 9... 144 Theme 02... 145 Theme 03... 146 Theme 04... 148
Contents 目錄ix Theme 05... 150... 153 08 銷貨單... 157 Theme 01... 159 Theme 02... 160 w3... 160 Theme 03... 162 Theme 04... 166 Theme 05... 169... 169... 170... 171 Theme 06... 172 Theme 07... 173... 174 09 大樂透... 175 Theme 01... 178... 179 Theme 02... 181 Theme 03... 184 Theme 04... 187 Theme 05... 190... 194... 195
Contents 目錄x 10 查表... 197 Theme 01... 205... 205 Theme 02... 206... 206 Theme 03... 207... 207 Theme 04... 209 Theme 05... 215 Theme 06... 216... 216... 217... 218 11 題庫系統... 223 Theme 01 PDF EXCEL... 224 PDF EXCEL... 224... 225... 231 Theme 02... 233 Theme 03... 237... 237 Theme 04... 240... 240... 240... 243 Theme 05... 244... 244
Contents 目錄xi... 244... 246 PART 3 VBA 12 VBA 基本邏輯指令... 249 Theme 01 VBA... 250... 250... 252... 255... 256 Theme 02 IF...Then... 259 Theme 03 Worksheets().Select... 261 Theme 04 For...Next... 263 Theme 05 Do...Loop... 264 Theme 06 Select Case...End Select... 265 Theme 07... 268 13 直欄轉橫列... 271 Theme 01... 272 vs.... 273 Theme 02... 274... 274... 274... 274
Contents 目錄xii Theme 03... 275 Theme 04... 277 Theme 05 D() w2... 279 Theme 06 F8... 280 Theme 07... 281 Theme 08... 284... 284 14... 289 Theme 01... 290 Theme 02... 290 Theme 03... 291 Theme 04 read_data... 292... 292 7 7~56... 294 A:E I:M... 294 35... 295... 296 Theme 05 count_page... 296... 296... 298 Theme 06 w2 start_row... 299 Theme 07 w2 write_data... 300 Theme 06 main... 301
Contents 目錄xiii 15... 305 Theme 01... 306... 306... 306 Theme 02... 307 Theme 03 read_data... 308 Theme 04 80 choice... 309 Theme 05 w2 write_data...311 Theme 06 score...311 Theme 07... 313 Theme 08... 316... 316... 317... 318 Theme 09... 320 Theme 10... 321
PART 2 函數篇 f u n c t i o n 05 11 7
PART 2 函數 SUM( 資料範圍 1, 資料範圍 2,... ) 加總運算 02 AVERAGE( 資料範圍 1, 資料範圍 2,...) 平均運算 02 篇096 RANK( 個體資料, 整體資料, 排名方向 ) 排名次 02 NOW() 目前日期時間 04 YEAR( 日期 ) 取出日期的年度 04 MONTH( 日期 ) 取出日期的月份 04 DAY( 日期 ) 取出日期的日數 04 RANDBETWEEN( 最小值, 最大值 ) 產生隨機整數 05 SUMPRODUCT( 數列 1, 數列 2,...) 將多個數列作相乘後相加運算 05 ROUND( 數字, 小數點位數 ) 四捨五入運算 05 MAX( 資料範圍 1, 資料範圍 2,...) 最大值 05 MIN( 資料範圍 1, 資料範圍 2,...) 最小值 05 COUNTIF( 資料範圍, 條件 ) 符合條件的資料筆數 05 INDIRECT( 儲存格 ) 將儲存格內容轉換為範圍名稱 05 LARGE( 資料範圍, 名次 N) 第 N 大 05 SMALL( 資料範圍, 名次 N) 第 N 小 05 ISBLANK( 儲存格 ) 儲存格是否為 空格 06 IF( 條件, 真值, 偽值 ) 2 選 1 條件判斷 06 TEXT( 數字, 文字格式 ) 將數字轉換為文字 06 MID( 字串, 第 A 個字開始, 取 B 個字 ) 取出文字串部分內容 06 VLOOKUP( 查表值, 資料表, 第 A 欄, 查表方式 ) 查表後取出第 N 欄 06 SUBSTITUTE( 文字串, 舊字串, 新字串 ) 將文字串中舊字串替換為新字串 06 COLUMN( 儲存格 ) 取出儲存格的欄數 07 ROW( 儲存格 ) 取出儲存格的列數 07 DATE( 年, 月, 日 ) 將 年 月 日 組合為日期 07 WEEKDAY( 日期 ) 某一日期是 一週中的第幾天 07
OR( 條件 1, 條件 2,...) 或 運算 07 AND( 條件 1, 條件 2,...) 且 的運算 07 TODAY() 取出系統當天日期 08 MATCH( 查表值, 資料欄, 查表方式 ) 查詢資料所在的列數 09 INT( 數值 ) 取整數 09 RAND() 隨機亂數 09 FIND( 部分字串, 完整字串 ) 判斷部分字串位於完整字串的位置 ISERR( 運算式 ) 判斷運算式運算結果是否正確 09 HLOOKUP( 查表值, 資料表, 欄位數, 查表方式 ) 功能與 VLOOKUP() 相同 10 LOOKUP( 查表值, 查詢欄位, 結果欄位 ) 功能與 VLOOKUP() 相近 10 MATCH( 查表值, 查詢欄位, 查表方式 ) 查出第幾筆 10 09 Unit 05 進階成績單097 INDEX( 資料表, 第 A 列, 第 B 欄 ) ISNUMBER( 運算式 ) * 網底為選項參數 取出資料表中第 A 列 第 B 欄的資料 判斷運算式運算結果是否為數字資料 10 11
Theme 05 B4:B23 20 50 200 B4:B23 B4:B23 " " " " 1 B3:E23 + 20 Unit 05 進階成績單111 2 E X P L A I N ª 說明 B3 儲存格內容 國文 就是 B4:B23 範圍的名稱 同理, 數學 C4:C23 英文 D4:D23 自然 E4:E23 需要使用名稱時只要按下 F3 功能鍵, 就可叫出名稱對話方塊
2 函數PART 1 w2 B2 = MAX( ) 篇112 E X P L A I N ª 說明 國文科最高分 B2 原始運算式 :=MAX('w1'!B4:B23), 由於我們做了範圍命名, 因此可直接將 B2:=MAX('w1'!B4:B23) 簡化為 B2: =MAX( 國文 ) 範圍名稱不要以鍵盤輸入, 按 F3 功能鍵叫出 貼上名稱 對話方塊, 可避免不要的錯誤 w2 表的 B2 儲存格統計 w1 表的 B4:B23 範圍, 由於是跨工作表, 因此 B4:B23 儲存格範圍必須以完整名稱表示 :w1! B4:B23 (w1 工作表的 B4:B23 範圍 ) 2 w2 B3 = MIN( ) F3 =MIN( ) 3 w2 B4 = COUNTIF(, ">=60" ) w2 B5 = AVERAGE( ) E X P L A I N ª 說明我們在 B2:B5 完成國文科的 4 項統計 : 最高分 最低分 及格人數 平均分數, B2:B5 範圍可以向右複製到 C2:C5( 數學 ) D2:D5( 英文 ) E2:E5( 自然 ) 嗎?
4 B2:B5 E2:E5 E X P L A I N ª 說明 由上圖可看出 : 複製的結果是錯的! 國文 這個名稱在向右複製時無法作相對移動而自動更改為: 數學 英文 自然, 使用名稱就無法複製公式, 那真是虧大了! 5 B2 = MAX( B1 ) Unit 05 進階成績單113 E X P L A I N ª 說明 國文 這個名稱可不可以由 B1 儲存格來取得呢? 結果顯然也是錯的! = MAX (B1) 解釋為 :B1 範圍內的最大值,B1 內容是文字, 結果當然是 0 如果要取 B1 的內容當名稱, 就必須再套上 INDIRECT() 函數 6 B2 = MAX( INDIRECT(B1) )