[EXCEL] 部分符合查表法

　
　　相關函數：IF() / LARGE() / INDEX() / 陣列公式

　　之前提到一些查表法，包括「任意鍵值查表法」，「多重條件查表法」等，今天再來一個「部分符合查表法」。

　　一般的查表，鍵值必須完全符合才能查出來，所以沒有辦法用 "book" 當鍵值去查到 "This is a book"。今天的公式，則可以突破這個限制。它的原理是，先將原始資料做一些運算，再把運算結果拿來和鍵值比對。

##CONTINUE##

　　因為會用到陣列公式，所以還不懂陣列公式的朋友，可以先參考另一篇文章：淺談陣列公式。

　　如下圖，是學號和姓名的對照表，利用之前說過的 VLOOKUP()，我們可以用「完整的學號」查出姓名。那麼，可不可以用「部分的學號」來查出姓名？例如，只輸入「05」，來查出 A30305 的「周五」？

　　可以的，E2 公式如下：

=INDEX(B2:B11,LARGE(IF(RIGHT(A2:A11,2)=D2,ROW(A2:A11)-1,""),1))

　　這是陣列公式，記得要用 CTRL+SHIFT+ENTER 來完成輸入。

　　公式說明：

• RIGHT(A2:A11,2)=D2　這是陣列公式，將 A 欄的最右邊兩個字元取出來，和 D2 做比對。
• IF(RIGHT(...),ROW(A2:A11)-1,"")　如果比對成功，則傳回列數-1，否則傳回空白。
• LARGE(IF(...),1)　從比對結果傳回的列數中，找出最大值。如果有多個結果符合，傳回列數最大的；如果沒有人符合，會造成 #NUM! 錯誤。
• INDEX(B2:B11,LARGE(...))　根據傳回的列數，找出相對應的 B 欄資料，此為最後結果。

不要那麼「杜正勝」！！

　
　　教育部把「三隻小豬」列入「成語典」的附錄，引發社會各界的質疑和討論，教育部也從善如流，暫時把有爭議的部份移除。沒想到，我們英明偉大，總是出人意料的杜正勝大部長又出來說話了，這次他說：

　　「成語這個東西會讓人思想懶惰、頭腦昏鈍、一知半解。」「用成語是國文教育的失敗。」

##CONTINUE##

　　？！？？？！！！…………………（我已經不知道接下去該說什麼才好了！）

　　如果，杜大部長的話是對的，那麼全台灣的國文老師都該切腹自殺！

　　如果，杜大部長的話是對的，那麼全台灣的作家都該辭職，出版社都該倒閉！

　　如果，杜大部長的話是對的，那麼教育部花納稅人的錢出版「成語典」，根本就是公然貪污！

　　如果，杜大部長的話是對的，那麼陳水扁說出「罄竹難書」這個成語，就是「思想懶惰」，就是「頭腦昏鈍」，就是「一知半解」！（嗯... 若這樣解釋，杜大部長倒是難得說了一句正確的話！）

　　如果，杜大部長的話是對的，那麼杜大部長自己用了「一知半解」這個成語，根本就是自打嘴巴，自相矛盾！

　　老天爺啊！不要那麼「杜正勝」了！趕快顯顯神跡，救救我們吧！

　　註：依照教育部成語典，本文至少用了五個成語，要刻意不用成語，文章還真難寫。另外，依照社會大眾的普遍認知，則要再加上一個新的成語：「杜正勝」！

[EXCEL] 不跨日的工時計算

　
　　相關函數：IF() / AND() / MAX() / MIN()

　　有些 Excel 的運用，並不需要套用很艱深的函數，只需要清楚的邏輯，和簡單的函數，就可以發揮很大的功用。每日工作時數的計算就是一例。（我是指不跨日的，如果要考慮跨日，情況會更複雜。）

　　工時計算，不外乎時間的加加減減，主要的問題還是在於要扣掉休息時間，及加上加班時間。

##CONTINUE##

　　一般的想法，會先直接把下班時間減掉上班時間，再從中扣掉休息時間。但是因為遲到，早退，請假，加班等因素，要從中正確扣掉休息時間，並不容易。

　　我的想法是，直接把上班時間分為早班，午班，和加班三個連續時段，分別算出三個連續時段的工作時數，再加總即可。因為，每個時段都是連續的，沒有休息時間，所以要計算單一時段的工作時間，是比較單純的。公式原理如下，使用的函數都很簡單吧！

　　=IF(AND(上班時間<表定時段結束時間,下班時間>表定時段開始時間),
　　　MIN(下班時間,表定時段結束時間)-MAX(上班時間,表定時段開始時間),
　　　0)

　　為了靈活運用，我把表定時間在第二列另外列出，方便不同的使用者自由設定自己的上班時間，格式如下圖：

　　C4 輸入下列公式，再往下複製即可。

　　=IF(AND(A4<$B$2,B4>$A$2),MIN(B4,$B$2)-MAX(A4,$A$2),0)+
　　IF(AND(A4<$D$2,B4>$C$2),MIN(B4,$D$2)-MAX(A4,$C$2),0)+
　　IF(B4>$E$2,B4-MAX(A4,$E$2),0)

　　E3:H13 是用來驗證用的，驗證各時段的計算是否正確，實際運用時，可以直接刪除。

• E4 =IF(AND(A4<$B$2,B4>$A$2),MIN(B4,$B$2)-MAX(A4,$A$2),0)
• F4 =IF(AND(A4<$D$2,B4>$C$2),MIN(B4,$D$2)-MAX(A4,$C$2),0)
• G4 =IF(B4>$E$2,B4-MAX(A4,$E$2),0)
• H4 =E4+F4+G4

[軟體] 我的 Firefox 外掛

　
　　Firefox 是個很有彈性的瀏覽器，你可以自由加上各式外掛程式或是附加元件（或叫擴充套件），來滿足個人的獨特需求。我整理了一下我個人的需求，以及使用的附加元件，給大家參考。

##CONTINUE##

• Firefox 版本：v2.0.0.1 (隨時更新)
• 瀏覽器核心：Gecko
• 分頁顯示：不需外掛
• 阻擋彈出視窗：不需外掛
• 阻擋廣告圖檔：Adblock Plus
• 快速查詢網頁上關鍵字（滑鼠拖拉）：Super DragAndGo
• 快速開啟網頁上網址（滑鼠拖拉）：Super DragAndGo
• 簡繁轉換：新同文堂
• 記憶並管理登入密碼：不需外掛
• 收集網頁並允許註解：ScrapBook
• 一次開啟多個網頁：不需外掛
• 預設關閉 activeX / JavaScript：不需外掛
  

　　上面的所有軟體和附加元件，都可以在 Firefox 中文版網站上輕鬆找到，並且免費下載安裝。使用操作上也都很方便。有任何問題，也有專用討論區可以發問，各方高手雲集喔。

　　總之，把 IE 淘汱掉吧！

2008/8/1 補充：

　　Firefox 升級到 3.0 版之後，有部份外掛 (正式名稱為「附加元件」) 沒有更新，不能再用了。不過，沒關係，還有其他類似的附加元件可以用：

• Super DragAndGo 可以改用 QuickDrag
• 新同文堂可以改用 Gb2Big (注意，Gb2Big 安裝之後，表面上沒有任何改變，要自己在工具列按滑鼠右鍵，選「自訂」，再把「簡繁」或「繁簡」的圖示拉到工具列上，才方便使用。)
  

　　新的附加元件，可以在 Firefox 的附加元件網站上找到並下載。
https://addons.mozilla.org/zh-TW/firefox/

綠光森林－富野綿羊牧場

　
　　2006 年 12 月，繼上次的「大溪花海農場」之後，這次往更深山走，來到了北橫公路上的「綠光森林－富野綿羊牧場」。

##CONTINUE##

　　「綠光森林」因為偶像劇而聲名大噪，有民宿，餐廳，歐式主體建築，庭園造景。幾乎每個山上的民宿或農場，都差不多是這個樣子，可能因為看多了，有點麻木，不再覺得驚豔。比較特別的，則是一小片香菇種植場，漂亮而開闊的山景，和小型的綿羊牧場。

　　本來對香菇種植場沒啥興趣的，意外的是，五歲的丫丫極有興趣，拉著我們一定要去看「好吃的香菇」，我們只好抱著野外教學的心情，和丫丫在一堆木頭中尋找香菇。可能是剛被收成過吧？香菇很少，好不容易才找到一個比較大的，總算滿足了丫丫的求知慾！

　　綿羊牧場規模不大，大約十幾二十頭綿羊吧，羊毛髒髒的，草皮禿禿的，可以近距離觸摸，可是髒到我不想摸。看過清境農場的綿羊，對這裡的綿羊突然沒有了興趣。曾經滄海難為水啊！綿羊，還是要配上乾淨的羊毛和青翠的草原才是王道！

　　牧場旁邊有一片青翠的草皮，正對著一片開闊的山景，趟在草皮上曬太陽，吹涼風，看藍天白雲，翠綠山景，正！

　　情人散步，來這裡不錯，如果帶著小朋友，因為牧場位於山坡地，坡度很陡，可能要小心一點，別摔跤了。

• 牧場官方網站：http://www.furano.com.tw/，內有地圖，門票，開放時間。

[軟體] 讓數位相機短片動起來

　
　　朋友的電腦重灌成 Windows 2000 之後，就無法觀看數位相機拍下來的 AVI 短片了，用 Microsoft Media Player 打開來看，只有聲音，沒有畫面。

##CONTINUE##

　　一般的數位相機 AVI 短片，是使用 MJPEG (Motion JPEG) 格式來錄製，所以電腦中必須有 MJPEG 的解碼軟體 (codec) 才可以播放。理論上，當 Media Player 找不到解碼軟體時，會自動上網尋找適當的解碼軟體，並自動安裝才是。

　　可是，這次 Media Player 不知為何卻沒有自動上網找，所以，我只好手動上網找了。找到的這一個免費 MJPEG 解碼軟體，安裝之後，Media Player 就可以順利播放短片了。如果你也有類似問題，可以試試。

• 下載網頁：http://www.free-codecs.com/Motion_JPEG_Codec_download.htm
• 下載方法：按網頁上的 Download [ Motion JPEG Codec 3.2.4 ] 即可下載
• 程式大小：1MB
• 安裝方法：用 Winzip 解壓縮後，執行 mjpegcodecv3.2.4.exe 即可。

[EXCEL] 製作賓果表－INDIRECT() 的應用

　
　　相關函數：INDIRECT()

　　Excel 另一個常見的應用，是從一堆原始資料中，按照一定的規則，把符合規則的參照位置的資料抽選出來。而 INDIRECT() 正好可以用來把「文字串」"A1" 轉換成「參照位置」A1，也就是說，只要你能夠組合出你所需要的文字串，就可以抽選出它所對應的資料。

　　舉例來說，你想從 A1:A10 中抽出 A3, A6, A9 三組資料，只要在 B1 輸入公式

##CONTINUE##

=INDIRECT("A"&ROW()*3)

　　再將公式向下複製到 B2:B3 即可。ROW()*3 剛好可以算出 3, 6, 9 三個數字，進而組合出 "A3", "A6", "A9" 三個文字串，再由 INDIRECT() 轉換成參照位置 A3, A6, A9。只要改變這部份的公式，抽選的規則就可以千變萬化。

　　例如，如下表，想把 A2:A6 這五個資料重新亂數排列，可以先在 B:C 欄產生 2~6 的不重覆的亂數，用來代表 A2:A6 的列數，再用 INDIRECT() 轉換成參照位置，就可以在抓到原始資料顯示在 D 欄。

• B2 公式 =RAND()
• C2 公式 =RANK(B2,$B$2:$B$6)+1
• D2 公式 =INDIRECT("A"&C2)
• 三個公式都往下複製到 B3:D6。
• D2 的公式可以合併 C2 成為 =INDIRECT("A"&RANK(B2,$B$2:$B$6)+1) 這樣就不需要 C 欄了

　　再進階一點，把上面方式重覆做五次，就可以得到５＊５的賓果表了。例如，想從 99 個數字或符號中，亂數取出 25 個做成５＊５的賓果表，步驟如下：

1. 在 Sheet2!A1:A99 輸入你想要的數字或符號
2. 在 Sheet2!B1 輸入公式 =RAND()
3. 把 Sheet2!B1 公式往下複製到 Sheet2!B2:B99
4. 在 Sheet1!A1 輸入公式 =INDIRECT("Sheet2!A"&RANK(Sheet2!$B1,Sheet2!$B$1:$B$99))
5. 把 A1 公式往右複製到 B1:E1
6. 修改 B1:E1 公式的紅字部份, B1 公式改為 "B6", C1 公式改為 "B11", D1 公式改為 "B16", E1 公式改為 "B21", 也就是 A 欄取 Sheet2!B1:B5 的亂數, B 欄取 Sheet2!B6:B10 的亂數, 餘此類推.
7. 選取 A1:E1, 往下複製四列到 A2:E5
8. 賓果表完成。按 F9 可以隨機改變排列，產生另一組不同的賓果表

　　註：有關不重覆的亂數，請參考另一篇文章的介紹。

百元理髮

　
　　對一個追不上流行，沒有時尚品味，只求活的輕鬆自在的老男人而言，理髮，是個不大不小的問題。

　　我的髮型永遠是「修短」，我的要求只是「不要太呆」，這麼卑微的要求，每一兩個月要收我三百塊上下的現大洋，在這個萬物齊漲薪水不漲的時代，著實令人有一點點點的心痛。

##CONTINUE##

　　最近發現一家叫 CO2 的理髮店，標榜男女剪髮一律一百元， 正合我心意，前兩天找了個空檔過去試試，只見約四五坪，整齊明亮的小店面，門口有個投幣機，投入一百元換一張號碼卷，再憑卷理髮。三張椅子，幾位年輕的師 父，輕快的音樂下，很快就理好了頭髮。因為不提供洗髮服務，所以最後是用一根類似吸塵器的管子，在頭上四處吸來吸去，把殘留的頭髮屑屑吸乾淨，算是開了一 次眼界。

　　快速，方便，便宜，不呆，百元理髮，解決了我這個不大不小的問題。下次再去！

2007-3-22 補充：

　　正名，正名，正確的名字是 CQ2 House。

　　這次再去，客人變的很多，光排隊就花了不少時間。而且理髮師父的動作也變慢了，不知道是不是換過師父了？以後得挑冷門時段去才行。

2008-1-21 補充：

　　CQ2 House 有官方網站了，好像連鎖店越開越多了，你可以在網站上找到分店的地址。網址是 http://www.cq2.com.tw/

[EXCEL] 顯示陣列公式的內容－MAXIF 和 MINIF

　
　　相關函數：IF() / MAX() / MIN()

　　陣列公式雖然好用，但是在公式撰寫和除錯時也比較麻煩，最主要的原因，就是因為陣列公式的運算過程是在 Excel 內部進行，如果出了問題，從外面不容易看出錯誤的地方。因此，能夠顯示出陣列公式運算過程中的陣列內容，對於理解陣列公式的原理，撰寫公式和除錯，都有很大的幫助。

##CONTINUE##

　　要顯示陣列內容，有三個步驟，假設陣列大小是 10，則

1. 先反白選擇 10 個空白儲存格，如 D2:D11
2. 滑鼠游標移到上方的編輯列，輸入公式
3. 按 CTRL+SHIFT+ENTER 完成輸入

　　如此，則陣列內容將會依序顯示在 D2 到 D10 內。如果少了第一個步驟，則出現的內容只是陣列的第一個元素而已，第二到第十個元素內容都無法看見。

　　舉例來說，如上表，A2:A11 是原始資料，C2:C3 是 SUMIF() 和 COUNTIF() 的結果。現在，如果我們想做出類似 MAXIF() 或 MINIF() 的功能，也就是先用 IF() 過濾原始資料之後，再選出過濾之後的最大值或最小值，應該怎麼做？

　　利用陣列公式的原理，我們可以建立一個陣列，來暫時存放過濾之後的內容值，再根據陣列內容值找出最大值或最小值即可。上表中的 D2:D11，就是以「小於 10」為條件，過濾出來的結果，公式為

{=IF(A2:A11<10,a2:a11,"")}

　　你可以看見所有十個元素的內容值。

　　整合 MAX() 和上面的陣列公式，在 C3 輸入完整陣列公式

{=MAX(IF(A2:A11<10,a2:a11,""))}

　　就可以達成類似 MAXIF() 的功能了。

　　MINIF() 的公式？看懂了 MAXIF()，MINIF() 一定不成問題的，對吧！

　　注意：D2:D11 一旦形成一個陣列公式之後，就不能單獨修改或刪除某一個元素，而必須全體一併修改或一併刪除才行，否則會出現上表中的警告訊息。