[EXCEL] 今天放假嗎？要補班啦！

　
　　之前有一篇文章，「今天放假嗎？」是在討論如何判斷某一天是不是假日，或是某一段時間要上班幾天。但是那篇文章的前提是，週六週日一律算放假。

　　問題來了，如果週六週日要補上班呢？那就得參考本篇文章中的公式加強版了。

##CONTINUE##

　　如上圖，C 欄是原先的公式，單純使用 NETWORKDAYS() 來計算工作天，週六週日一律算放假。
　　C2 =NETWORKDAYS(A2,A2,$F$2:$F$10)　－1 表示要上班，0 表示放假。
　　C11 =NETWORKDAYS(A2,A10,$F$2:$F$10)　－A2~A10 這段時間要上 5 天班。

　　以此為基礎，如果考慮週六週日要補上班的情況，可以在 G 欄加一個列表，個別列出所有週六日要補上班的日子。然後修改公式
　　D2 =NETWORKDAYS(A2,A2,$F$2:$F$10)+SUMPRODUCT(--($G$2:$G$10>=A2),--($G$2:$G$10<=A2))
　　D11 =NETWORKDAYS(A2,A10,$F$2:$F$10)+SUMPRODUCT(--($G$2:$G$10>=A2),--($G$2:$G$10<=A10))

　　新的公式，主要在計算 A2~A10 這段時間中，是否包含了 G2:G10 列表中的日期？如果有，表示是要補班的日子，得把這些日子再加回去才行。

相關文章：

• 今天放假嗎？：不考慮週六週日要補班的計算公式。
• 多重條件查表法：介紹利用 SUMPRODUCT() 處理多重條件，以及 -- 的用法。

[EXCEL] 製作月曆

　
　　瞭解了和日期以及星期相關的公式之後 ([EXCEL] 日期，星期，週數)，我們可以試著來做一件事：自動產生月曆表。


　　如上圖，如果以星期日為每週的第一天，只要給定年份和月份，就可以利用下列公式自動產生該月份的月曆表。

##CONTINUE##

　　=DAY($A$1-(WEEKDAY($A$1,1)-1)+COLUMN(A1)-1+(ROW(A1)-1)*7)

　　首先，在月曆表最左上方的第一個位置，輸入上面公式。$A$1 必須是該月份的第一天，例如，2008 年 11 月 1 日。

　　整個公式的重點在於，求出第一個位置的正確日期。我們先利用 WEEKDAY($A$1,1) 找出該月份第一天的星期數，再從該月份第一天倒算回去即可。例如 2008/11/1 是星期六，WEEKDAY($A$1,1) 會傳回 7，則左上方第一個位置的日期就是 2008/11/1 倒推 (7-1)=6 天，也就是 2008/10/26。

　　找出左上方的日期後，剩下的就簡單了：每往右一格就加一天，公式是 +COLUMN(A1)-1；每往下一列就加 7 天，公式是 +(ROW(A1)-1)*7；因為左上方的日期不必加，但是又必須從 A1 起算 ROW() 和 COLUMN()，所以要 -1 做補償。

　　好了，接下來，只要把這個公式直接複製到整個該月份的月曆表就完成了。

[EXCEL] 交集，聯集，差集

　
　　兩組資料，如何求它們的交集，聯集，和差集？（什麼叫差集？維基百科有詳細說明。）

　　如上圖，有兩組資料，集合Ａ是九月請假名單，集合Ｂ是十月請假名單，

• 它們的交集，就是「兩個月都請假的名單」，如Ｃ欄；
• Ａ對Ｂ的差集，就是「只有九月請假，十月沒有請假的人」，如Ｄ欄；
• Ｂ對Ａ的差集，就是「只有十月請假，九月沒有請假的人」，如Ｅ欄。

　　怎麼算出來的？且聽我慢慢道來。

##CONTINUE##

　　之前我有一篇文章，叫做「排除重複的資料」，它主要是在過濾同一組資料，讓重複的資料只列出一次。這次的公式原理和它大同小異，但是會使用網友夏日大大修改過的進階版本，說明如下。

　　求交集的公式是 C3

　　=INDEX($A:$A,SMALL(IF(COUNTIF($B$3:$B$6,$A$3:$A$11)>0,ROW($A$3:$A$11),65536),ROW(A1)))&""

　　這是一個陣列公式，請用 CTRL+SHIFT+ENTER 完成輸入。公式可往下複製。出現 #NUM! 時，表示已經超出合理範圍；出現空白時，表示名單已經列完了。

　　這個公式的原理，是以Ａ集合為主，利用 COUNTIF($B$3:$B$6,$A$3:$A$11)>0 找出Ａ欄的資料是否在 $B$3:$B$6 的範圍中出現過，如果出現過，表示兩邊都出現過，是交集的一部份，則傳回Ａ欄資料的列數。反之，則傳回 65536，這是最大的列數，其內容通常沒有值。

　　A 欄資料的列數會形成陣列，再用 SMALL() 做排序，INDEX() 把列數轉為資料，就可以得到 C 欄的名單了。

　　如果交集的名單已經列完了，接下去會列出第 65536 列的資料，Excel 會自動把它轉成 0。因為出現 0 有點奇怪，為了把 0 變成空白，所以在公式最後加上 &""。

　　交集看懂了，差集應該就沒問題了。D3 是利用 COUNTIF()=0，以Ａ集合為主，找出在 $B$3:$B$6 的範圍中沒有出現過的資料，就是Ａ集合有，Ｂ集合沒有的差集了。

　　=INDEX($A:$A,SMALL(IF(COUNTIF($B$3:$B$6,$A$3:$A$11)=0,ROW($A$3:$A$11),65536),ROW(A1)))&""

　　E3 則是把Ａ集合和Ｂ集合對調，以Ｂ集合為主，找出在 $A$3:$A$11 的範圍中沒有出現過的資料，就是Ｂ集合有，Ａ集合沒有的差集了。

　　=INDEX($B:$B,SMALL(IF(COUNTIF($A$3:$A$11,$B$3:$B$6)=0,ROW($B$3:$B$6),65536),ROW(A1)))&""

　　至於聯集呢？聯集就是集合和差集的總和，也就是Ａ欄加Ｅ欄，或是Ｂ欄加Ｄ欄的總合。

[EXCEL] 找零錢

　
　　有 50, 10, 5, 1 塊的銅板，要找零錢 89 塊，要幾個 10 元，幾個 5 元銅板，才能湊出來？當然你可以針對各幣值一個一個去設計公式，不過這裡有更簡單的通用公式，給你參考。

##CONTINUE##

　　如上圖，如果有 1000, 500, 100, 50, 10, 1 六種幣值，要湊出 14,523 元，要怎麼湊？

　　對最大幣值而言，比較簡單，金額直接除以幣值，再取整數即可，B2 =INT(A2/B$1)。公式可直接往下複製。

　　對其他幣值而言，其實也不難，金額先減去較大幣值的金額，再除以幣值，取整數即可。以 D2 的 100 元為例，14523-(1000*14+500*1)=23，INT(23/100)=INT(0)=0，完成。剛好，(1000*14+500*1) 這個算式可以用 SUMPRODUCT() 來計算，所以 D2 的公式就成為

　　=INT(($A2-SUMPRODUCT($B$1:C$1,$B2:C2))/D$1)

　　這個公式已經考慮過相對位址問題，所以可以直接複製到 C2:Gx 的所有位置。

　　這個通用公式的好處是，它可以適用於任何幣值組合，只要遵守由大到小的順序即可。例如，如果有某個幣值剛好沒銅板了，我們只要把那個幣值整欄刪除即可，公式不必修改就可以正常換算。

　　例題中的 A5:F7 是沒有 500 元紙幣的狀況。只要幣值所在列沒變，公式就不須做任何修改，當然，因為 A5:F7 幣值所在列已經從第１列變成第５列，所以公式也要配合修改，C6 的公式為

　　=INT(($A6-SUMPRODUCT($B$5:B$5,$B6:B6))/C$5)

　　這類問題，在台灣相對簡單，因為台灣的幣值都是 5 或 10 倍為基礎，比較好換算。(或者是我們已經習慣這種換算法了？)像上次我去美國出差，美國的銅板有 quarter，也就是美金 0.25 元，害我每次找零錢都會打結。如果用 Excel 來算，同樣的公式，只要更改幣值，一樣可以適用。例題中的 A9:G11 是美金銅板換算的狀況。

[EXCEL] 亂數排班法

　
　　十個人，要亂數排到四個班，每班人數不同，如何設公式？

##CONTINUE##

　　如上圖，在 E2:E11 先設定好各個班次所需人數，如 A 班要排 3 人，就把「A 班」複製 3 次；B 班只排 1 人，就把「B 班」維持 1 次；依次類推，總次數和要排班的人數會相同。

　　然後，C2 =RAND()，B2 =OFFSET($E$1,RANK(C2,$C$2:$C$11),0)，直接往下複製，完成。

　　有沒有很熟悉的感覺？答對了，RANK(C2,$C$2:$C$11) 這個公式，就是我們在另一篇文章
「不重複的亂數」中用到的公式，它會產生不重複的亂數。

　　你可以這樣想，先挖好十個洞 (E 欄的排班設定)，每個洞代表一個班次，然後讓每個人隨機跳進一個洞，不重複的亂數會確保每個人都跳進不同的洞，然後，亂數排班就完成了。這樣做的好處是，不管有多少人要排班，不管有幾種班，不管每班有幾個人，不必改公式，都可以很容易的排好班，只要簡單的挖好洞即可。

　　C 欄是必要的，如果嫌它礙眼，可以把它放到別的工作表，或是按滑鼠右鍵把它「隱藏」起來即可。

[EXCEL] 日期，星期，週數

　
　　Excel 提供了一些有關日期，星期，和週數的轉換公式，配合圖中例題，整理如下：

##CONTINUE##

　　1. 日期轉星期：B2 =WEEKDAY(A2,2)，表示 2008/1/1 是星期二。要注意第二個參數，1 或省略表示星期日當做每週的第一天會傳回 1，星期一會傳回 2；2 表示星期一會傳回 1。

　　2. 日期轉週數：C2 =WEEKNUM(A2,1)，表示 2008/1/1 是今年第一週。要注意第二個參數，1 或省略表示每週的第一天是星期日；2 表示每週的第一天是星期一。如果無法使用此函數，且傳回 #NAME? 錯誤，請安裝〔工具〕〔增益集〕〔分析工具箱〕。

　　3. 週數轉日期：這個問題比較有趣，也是本文的重點，因為 Excel 並沒有提供直接的轉換公式，我們要自己兜出來。假設週數是採用「每週的第一天是星期日」的算法，則本週第一天的公式是

　　D2 =DATE(YEAR(A2),1,1)-WEEKDAY(DATE(YEAR(A2),1,1),1)+1+(7*(C2-1))

　　這個公式的思考方向是，先找出第一週的第一天，也就是把一月一日減去一月一日的星期數，再 +1 回來。找出第一週的第一天之後，再以每週七天的規則往後加即可。

　　本週最後一天的公式就簡單了，把第一天加６即可。E2 =D2+6

　　要注意的是，這個轉換公式會跨年，如果和你的需求不符，要根據你的需求再特別處理一下。

[EXCEL] COUNTIF 配合動態條件

　
　　相關函數：COUNTIF() / SUMIF()

　　想知道有多少人分數及格？簡單 =COUNTIF(B2:B6,">=60")

　　想知道有多少人分數高於平均？應該是 =COUNTIF(B2:B6,">=AVERAGE(B2:B6)")

　　Sorry 答錯了！觀念正確，但是用法錯誤。

##CONTINUE##

　　COUNTIF() 的第二個參數 criteria 只能放數字、表示式或文字，但是不能直接放函數。像上面的用法，Excel 會把 ">=AVERAGE(B2:B6)" 當做是單純的文字，而不會把 AVERAGE() 當做函數去計算平均值。正確的用法是

　　=COUNTIF(B2:B6,">="&AVERAGE(B2:B6))

　　把 AVERAGE() 放在引號之外，先算出平均值，& 運算子可以把算出來的平均值和 ">=" 連接成一組文字，再送給 COUNTIF() 去計算。這樣就可以讓 COUNTIF() 使用動態的條件，而不再是固定的數字、表示式或文字了。

　　圖中的 C 欄僅供參考用，實際使用上並不需要。

　　同樣的方法，也可以用在 SUMIF() 上，試試看吧！

　　對了，如果想知道分數高於平均的人名列表，可以參考這篇文章 [EXCEL] 用公式篩選資料。

[EXCEL] 用公式做資料排序

　
　　如果可以用公式來篩選資料，那麼可不可以用公式來排序？也是可以的。

　　要把資料排序，可以在〔資料〕〔排序〕，然後選擇你要的鍵值和排序方法。但是，一樣會有一些不方便的地方。

• 第一，如果資料有所變動，都要重新排序一次，無法自動排序。
• 第二，排序會破壞原始資料的順序。
• 第三，不能只顯示部份欄位，所有欄位都會全部顯示。

　　如果想跳脫上述的限制，我們可以自己設計公式來做排序的動作。

##CONTINUE##

　　如上圖，要從學生的成績單中自動做分數的遞增排序，可以在 E2 輸入陣列公式

=INDEX(B:B,MOD(SMALL($C$2:$C$6*100+ROW($C$2:$C$6),ROW(A1)),100))

　　公式說明：

• $C$2:$C$6*100+ROW($C$2:$C$6): 將分數和所在列數編碼成一個數字，並形成一個陣列。此數字的百位數以上就是分數，百位數以下則是所在列數，將此數字陣列排序後，可以維持分數的正確大小順序，而且可以推算出所在列數。
• SMALL(數字陣列,ROW(...)): 依序從數字陣列中傳回第一小，第二小...的數字。
• MOD(SMALL(...),100): 從數字中回推出所在列數。
• INDEX(顯示資料欄位，SMALL(...)): 從顯示資料欄位中取出所在列數所對應的值。

　　在 F2 輸入陣列公式

=INDEX(C:C,MOD(SMALL($C$2:$C$6*100+ROW($C$2:$C$6),ROW(A1)),100))

　　再直接往下複製即可。這是一個陣列公式，記得用 CTRL+SHIFT+ENTER 來完成輸入。

　　這樣，我們可以任意在 A:C 欄變動資料，E:F 欄會馬上自動顯示出最新的排序結果，而且可以任意指定要顯示那些欄位。

　　如果想遞減排序，只要把 SMALL() 改成 LARGE() 就可以了。如 H2 輸入公式

=INDEX(B:B,MOD(LARGE($C$2:$C$6*100+ROW($C$2:$C$6),ROW(A1)),100))


2008/10/06 補充：

　　原文中的公式，如果分數有小數點，或是資料超過 100 列，就無法正常運作。故修正公式如下：

　　=INDEX(C:C,MOD(SMALL($C$2:$C$6*(10^(X+Y))+ROW($C$2:$C$6),ROW(A1)),10^X))

　　其中，資料最大列數必須小於 10^X；Y 則是分數的小數點位數。

　　它的原理和原文中的說明是一樣的，只是把它具體化為公式，比較容易運用。如原文中的例子，資料列數 =6 ＜100 (=10^2)，分數都是整數 (小數 0 位)，則 X+Y=2+0=2。

　　如果資料列數 =101 ＜1000 (=10^3)，分數有兩位小數，則 X=3，Y=2，公式就要變成

　　=INDEX(C:C,MOD(SMALL($C$2:$C$6*(10^(3+2))+ROW($C$2:$C$6),ROW(A1)),10^3))

　　或是直接把 10 的次方算出來，變成

　　=INDEX(C:C,MOD(SMALL($C$2:$C$6*100000)+ROW($C$2:$C$6),ROW(A1)),1000))

[EXCEL] 用公式篩選資料

　
　　相關函數：INDEX() / SMALL() / ROW()

　　要從一大堆資料當中篩選出符合條件的資料，最簡單的方法就是〔資料〕〔篩選〕〔自動篩選〕，然後選擇你要的條件或公式。但是，這樣會有一些不方便的地方。

• 第一，進入篩選模式時，不方便輸入新的資料。
• 第二，即使使用的公式都一樣，進入篩選模式時，每次都要重覆輸入篩選公式。
• 第三，不能只篩選部份欄位，所有欄位都會全部顯示。
  

　　如果想跳脫上述的限制，我們可以自己設計公式來做篩選的動作。

##CONTINUE##

　　如上圖，要從學生的成績單中篩選出國文不及格的人的姓名和分數，可以在 F2 輸入陣列公式

=INDEX(B:B,SMALL(IF($C$2:$C$10<60,row($c$2:$c$10),""),ROW(C1)))

　　公式說明：

• IF(...): 設定篩選條件，如果 C 欄分數小於 60，就傳回列數，否則傳回空白，傳回值形成一個陣列。
• SMALL(IF(...),ROW(...)): 依序從陣列中傳回第一小，第二小...的列數。因為不符合條件者傳回空白，在這裡會傳回不合理的列數，導致結果為 0 或 #NUM!。
• INDEX(顯示資料欄位，SMALL(...)): 從 B 欄中依序篩選出列數所對應的值。

　　在 G2 輸入陣列公式

=INDEX(C:C,SMALL(IF($C$2:$C$10<60,row($c$2:$c$10),""),ROW(C1)))

　　再直接往下複製即可。這是一個陣列公式，記得用 CTRL+SHIFT+ENTER 來完成輸入。

　　這樣，我們可以任意在 A:D 欄輸入資料，F:G 欄會馬上自動顯示出最新的篩選結果，而且可以任意指定要顯示那些欄位。

　　如果想一次篩選多個條件，例如，想找國文，數學兩科都不及格的人，只要適當修改篩選條件就可以了。如 I2 輸入公式

=INDEX(A:A,SMALL(
IF((($C$2:$C$10<60)+($d$2:$d$10<60))=2,ROW($C$2:$C$10),""),
ROW(C1)))

[EXCEL] 區間累加

　
　　相關函數：SUMPRODUCT()

　　網友丫霞問了一個問題，是關於不同區間的距離要採用不同的計費單價。這類型的問題應該可以再分為兩類，詳細說明如下。

##CONTINUE##

　　第一種，是不同「距離」採用不同單價。例如，

• 送貨距離在 10 公里內，每公里要價 100 元；
• 送貨距離在 20 公里內，每公里要價 110 元。

　　這種算法，當送貨距離是 10 公里時，總價是 100*10=1000；當送貨距離是 11 公里時，總價是 110*11=1210。

　　第二種，是不同「距離區間」採用不同單價。例如，

• 送貨距離在 10 公里內，每公里要價 100 元；
• 送貨距離在 11-20 公里內，每公里要價 110 元。

　　這種算法，當送貨距離是 10 公里時，總價是 100*10=1000；當送貨距離是 11 公里時，總價是 (100*10)+(110*1)=1110。


　　要採用第一種算法，可以套用之前介紹過的「多重條件查表法」的公式，先建立一個區間單價表，如上圖 F:H 欄，再利用 SUMPRODUCT() 公式找出一個同時符合「距離 >= 區間下限」和「距離 <= 區間上限」的區間，傳回其區間單價，再直接乘上距離即可。如上圖 C 欄，C2 公式為

=SUMPRODUCT(--(B2>=$F$2:$F$6),--(B2<=$G$2:$G$6),$H$2:$H$6)*B2

　　了解了第一種算法後，再做一些變化，就可以算出第二種算法的答案。先用 SUMPRODUCT() 公式找出所有符合「距離 > 區間上限」的區間單價，乘上各個區間的範圍，算出各個區間的價格總和；再用 SUMPRODUCT() 公式找出一個同時符合「距離 >= 區間下限」和「距離 <= 區間上限」的區間單價，乘上距離在這個區間佔的範圍，最後再加總即可。如上圖 D 欄，D2 公式為

=SUMPRODUCT(--(B2>$G$2:$G$6),($G$2:$G$6-$F$2:$F$6+1)*$H$2:$H$6)+
SUMPRODUCT(--(B2>=$F$2:$F$6),--(B2<=$G$2:$G$6),(B2-$F$2:$F$6+1)*$H$2:$H$6)

　　當然，區間上下限及單價內容可以隨時更改，不必修改公式，但是要注意區間應該要連續，才不會發生找不到區間的情形。

[EXCEL] 顯示組合清單

　
　　相關函數：INDEX() / ROW() / MOD() / INT()

　　如下圖，我們有四種水果，三種包裝尺寸，兩種包裝方法，想要列出一個清單能包含各種組合狀況，如何設公式？

##CONTINUE##

　　根據排列組合原理，共有 4*3*2=24 種組合狀況，使用下面的通用公式，我們可以利用 Excel 快速的把這二十四種組合狀況的清單列出來。

　　=INDEX(顯示資料範圍,1,
　　　MOD(INT((ROW(A1)-1)/(低階資料個數乘積)),顯示資料個數)
　　　+1)

　　以水果清單 A6 的公式為例

　　=INDEX($B$1:$E$1,1,MOD(INT((ROW(A1)-1)/(3*2)),4)+1)

• 顯示資料範圍：即為水果種類所在範圍 $B$1:$E$1，因為稍後要直接往下複製，所以必須為絕對位置。
• 低階資料個數乘積：所謂低階資料，就是表格右側的資料，也就是尺寸種類 3，和包裝種類 2 的乘積。
• 顯示資料個數：即為水果種類 4。

　　同理，尺寸清單 B6 的公式如下

　　=INDEX($B$2:$D$2,1,MOD(INT((ROW(A1)-1)/(2)),3)+1)

　　同理，包裝清單 C6 的公式如下，沒有低階資料時，乘積直接填 1

　　=INDEX($B$3:$C$3,1,MOD(INT((ROW(A1)-1)/(1)),2)+1)

　　然後，再把 A6:C6 直接往下複製即可。

　　好了，現在你有了二十四種組合狀況的清單，可以幹活了....幫每種組合狀況定個價錢如何？

2008/5/30 補充說明：

　　針對公式，做較深入的說明。其實，所謂排列組合的清單，說穿了，只是做兩件事：

　　第一，找出這是第幾輪的資料，也就是從資料清單中要挑第幾個資料出來顯示。以範例來說，第一層的第一輪要出現蘋果，第二輪要出現香蕉。

　　第二，找出低一層的資料個數乘積，也就是同一輪的資料要顯示幾次。以範例來說，第一層的第一輪要出現蘋果的次數，是第二層尺寸３＊第三層包裝２＝６次

　　再回頭看公式，MOD()+1，就是做第一件事。INT(ROW(A1)-1/乘積)，就是做第二件事。再進一步看公式：

　　ROW(A1)-1：是要把序號 1-24，變成 0-23，這樣待會兒在做 INT(序號/低階資料個數乘積) 時，才會得到正確的結果。例如，位置 A10 和 A11 是第五和第六個組合，序號 5，6，應該都顯示第一輪蘋果，但是如果做 INT(5/6)=0，INT(6/6)=1，會顯示不同的資料。為了把序號 5 和 6 落在同一個正確的位置，所以要先減一才行。

　　而 MOD(...)+1，則是因為 ROW(A1)-1 後，得到的資料會從 0 算起，0，1，2，3，而 INDEX() 要求的是 1，2，3，4，所以再 +1 補回來。

[EXCEL] 尋找第二組子字串

　
　　相關函數：FIND() / SUBSTITUTE()

　　要在一個字串中找到另一組子字串的位置，例如，在 "This is a book" 中找出第一組 "is" 的位置，只要直接使用 FIND() 函數即可，如下圖 C1。

　　但是，如果要在一個字串中找到第 n 組子字串的位置時，該如何做？

##CONTINUE##

　　Excel 有另一個函數 SUBSTITUTE() 可以把字串中的第 n 組子字串換成任何別的字串。如上圖 B3 把第二組 "is" 換成 "*"。

　 　利用這個特性和 FIND() 結合，就可以找出第 n 組子字串的位置了。其關鍵點是，利用 SUBSTITUTE() 把你要找的字串換成一個在原字串中沒有的字元，再利用 FIND() 找出這個字元即可。如上圖 C3，可以找出第二組 "is" 的起始位置是 6。

　　把 SUBSTITUTE() 的最後一個參數換成別的值，例如，換成 5，就可以找出第五組子字串的位置了。

[EXCEL] 一維轉二維

　
　　相關函數：INDEX() / ROW() / COLUMN()

　　當你從外部匯入原始資料，或是從別人手中承接原始資料，但是資料的排列格式不符合你的要求，這時候，我們會想要做資料排列格式的轉換。

　　問題來了，原始資料可能成千上萬筆，總不能一筆一筆，一格一格的剪貼吧？

##CONTINUE##

　　這時候，只要能找出新舊兩種排列格式的轉換規律，就可以利用簡單的 ROW()，COLUMN() 函數來幫你自動完成轉換工作。

　　舉例來說，如下圖，A 欄為原始資料，我們想把它轉換成 C3:E7 這樣子的三欄格式。

　　首先就要找出兩種格式的轉換規律，規律就是，把 A 欄的一維資料依照順序變成三
欄的二維資料。這樣的規律，可以在 C3 輸入公式

=INDEX($A:$A,(ROW(A1)-1)*3+COLUMN(A1))

　　再把公式直接複製到 C3:E7 即可。

　　要把規律化成公式，其實只是簡單的數列邏輯轉換成數學公式而已。請參考表格 C10:E16，它列出兩種格式之間欄數和列數的對照關係，只要找出一個數學公式可以滿足這個對照關係，再把數學公式代入 Excel 公式中即可。至於怎麼找出數學公式，嘿！這個要各憑本事，無法言傳了。

　　這個一維轉二維的公式，還可以發展成通用公式，也就是可以通用在不同的二維欄數，以及不同的資料起始位置。通用公式如下：

=INDEX(資料欄絕對位置,
(ROW(A1)-1)*二維欄位寬度+COLUMN(A1)+資料第一列位置-1)

　　以上圖為例，原始資料起始位置 A1，要轉成３欄，新資料起始位置為 C3，則

• 資料欄絕對位置 = $A:$A (A1 的 A)
• 二維欄位寬度 = 3
• 資料第一列位置 = 1 (A1 的 1)
• 公式放在 C3

　　通用公式用上面的值代入後，就是前面的第一個公式了。

　　補充一點，格式轉換成功後，如果想把原始資料刪除，可以先把轉換後的資料〔複製〕〔選擇性貼上〕〔貼上值〕到另一個新的位置即可。

[EXCEL] 工具與公式

　
　　Excel 本身提供了不少好用的工具，來幫助我們分析或過濾資料。最常用的，就是「篩選」和「樞紐分析表」。

　　「篩選」可以用來過濾資料，不管是單一條件，或是多重條件，或是排除重覆資料，都可以做得出來。

　　「樞紐分析表」可以用來分析資料，把一大堆原始資料分類加總，交叉分析，做成清楚易懂的統計圖表。

##CONTINUE##

　　下面這個表，要統計各部門的各項開支，用樞紐分析表拉，不用一分鐘就搞定了，用公式寫的話....可能要半個小時以上，寫出來的公式別人也很難看懂。

　　嗯....那還要「公式」做什麼？

　　我的想法是，公式的優點如下：

1. 即時反應：原始資料一變動，結果會立刻跟著變，不需要再做額外的操作。
2. 格式自由：篩選和樞紐分析表的結果，格式是固定的，而用公式的話，格式是自由的
3. 再次運算：公式計算的結果，因為位置和內容都是我們自己設計決定的，所以比較適合再拿來給另一個公式當做運算的輸入值

　　反過來說，如果以上三點我都不在乎，我只要儘快得到結果就好，那麼，趕快把「篩選」和「樞紐分析表」的操作方法學好吧！

　　快來看想飛大大的「樞紐分析表」操作教學喔！

2007/7/28 補充:

　　再加一篇老年人大大的教學：EXCEL 樞紐分析之應用，快去看喔！

[EXCEL] 問問題，大不易

　
　　透過網路來討論 Excel 題目，很常遇到一個問題：說不清楚題目！

　　一般來說，要說清楚一個 Excel 題目，有幾個要點：

　　1. 原始資料的內容，包括格式和位置
　　2. 想要得到的結果，包括格式和位置
　　3. 中間運算的規則，越完整越好，包括例外狀況
　　4. 適當的範例
　　5. 一次說清楚題目，不要一再補充

##CONTINUE##

　　最近有位網友「王建民加油」在部落格上發問，他發問的方式是我遇過最好的一個，題目不太容易描述，但是我一看就懂，一次就搞定。在徵得對方同意之後，特別提出來和大家分享。

　　我覺得好的地方是，不但完全符合上面五個要點，而且利用 Excel 的註解功能，把題目和需求說的很清楚，最後抓下螢幕圖片，放在他自己的部落格中，留下連結網址，我再過去看，有問題，也可以透過彼此的留言板發問。

　　回答的人很快了解問題，發問的人才能很快得到答案。

　　盡力把題目說清楚，是發問者的誠意和義務。讓回答的人還要花時間去摸索你的問題，只是浪費彼此的時間而已。在 Yahoo 知識＋上面，這種不清不楚的問題越來越多了，肯回答問題的人，也會越來越少吧。

　　對了，想知道如何抓下螢幕圖片嗎？請參考上一篇文章－螢幕抓圖工具 - MWSnap

[EXCEL] 最新報價查表法

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

　　我們曾經討論過很多查表法，其中，「多重條件查表法」可以查出同時符合多個條件的資料。但是，如果同時符合條件的資料有很多筆，而我們只要其中最新的一筆時，應該如何實作？

##CONTINUE##

　　舉例來說，油價一直在變動，假如我們有油價變動的歷史資料，現在想查某一種油品在某一個日期的油價，就可以用下列的方法。

　　如上圖，A:C 欄是油價的歷史記錄，要以日期來遞增排序；E 欄是我們想查詢的油品，F 欄是購買油品的日期，那麼 G2 的油價公式就是

=INDEX(C:C,MAX(
IF($A$1:$A$100=E2,

IF($B$1:$B$100<=F2, ROW($C$1:$C$100), 0),

0)))

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

　　我們先用 IF($A$1:$A$100=E2,IF($B$1:$B$100<=F2,ROW($C$1:$C$100),0),0) 來過濾原始資料，只有油品相同，而且日期小於等於採購日期的資料，才會傳回列數，其它不符合條件的資料都回傳零。因為是陣列公式，這些列數和零值會形成一個陣列。

　　這樣形成的陣列，再利用 MAX() 取出列數的最大值。因為原始資料是以日期遞增排序，所以列數最大就代表最接近的日期。

　　最後，再用 INDEX() 配合 MAX() 傳回的列數，找到價格，完成。

[EXCEL] 誰的出現次數最多？

　
　　相關函數：陣列公式 / COUNTIF() / MAX() / ROW()

　　 前一篇文章利用陣列公式在一堆請假清單中列出曾經請過假的人，這次我們來找找，誰是請最多假的「請假大王」？

##CONTINUE##

　　要計算次數，當然要利用函數 COUNTIF()，如上圖 C 欄，C2 公式為

=COUNTIF($A$2:$A$11,A2)

　　把公式直接往下複製，就可以算出每個人的請假次數。C2:C11 形成一個陣列，我們可以在這個陣列上做變化。

　　例如，請假次數的最大值，D5 陣列公式為

=MAX(COUNTIF($A$2:$A$11,A2:A11))

　　那麼，這個請假次數最多的請假大王，到底是誰呢？D2 陣列公式為

=INDIRECT("A"&MAX(

IF
(COUNTIF($A$2:$A$11,A2:A11)=MAX(COUNTIF($A$2:$A$11,A2:A11)),
ROW(A2:A11),
"")))

　　我們來好好剖析這個公式。

• 最內層的 COUNTIF($A$2:$A$11,A2:A11) 就是每個人的請假次數，也就是 C2:C11 這個陣列。
• MAX(COUNTIF($A$2:$A$11,A2:A11))，就是請假次數的最大值。
• IF(COUNTIF(...)=MAX(...), ROW(A2:A11),"") 則是說，如果某個人的請假次數等於最大值，就記下它所在的列數 ROW()，否則就記下空白。經過這個步驟，就可以只留下請假大王的所在位置。
• 最後用外層的 MAX() 把這個列數取出來，再用 INDIRECT() 把位置轉成內容，完成！

　　
　　這個公式的架構，其實和之前高低標的公式很像，先把陣列公式利用

　　IF(條件式，留下想要的值，空白)

　　過濾成另一個陣列二，再從陣列二中取出想要的值，這裡是用 MAX()，高低標那裡是用 AVERAGE()。相同的架構，可以應用在各種地方，發揮你的想像力吧！

　　實用上，C 欄可以整欄去掉，這裡只是用來說明陣列而已。

[EXCEL] 排除重複的資料

　
　　相關函數：MATCH() / ROW() / SMALL() / INDEX()

　　有一堆原始資料，其中可能有重複的部份，要如何才能排除重複的資料，每種資料只留下一筆？

　　第一種方法，是先排序，把重複的部份集中在一起，再一筆一筆過濾排除。這個方法的好處是簡單，不需要任何函數或公式；壞處是，它會破壞掉原來的資料順序，而且必須從頭到尾用人工巡視一遍，可能會有疏漏。

##CONTINUE##

　　第二種方法，是用「格式化條件」把重複的部份變色，再一筆一筆過濾排除。詳細方法請參考這篇「[EXCEL]挑出重複的資料」。它的好處是不破壞原來的資料順序，而且可以一邊輸入新資料，一邊即時檢查新資料是否重複；壞處是，它一樣必須從頭到尾用人工巡視一遍，可能會有疏漏。如下圖 A 欄的紅色資料。

　　第三種方法，也就是現在要介紹的方法，則是利用函數公式，直接挑出不重複的資料，這樣既不會破壞原來的資料順序，也不必從頭到尾用人工巡視一遍。

　　如上圖，有學生的請假記錄，如果想找出「二月份請過假的人」，也就是要把請過二天以上假的人只顯示一次，做法如下。

　　假設名單範圍在 A1:A20，C 欄是用下列公式直接挑出不重複的請假名單：C2 輸入公式

=INDEX(A:A,SMALL(

IF(

IF(ISNA(MATCH($A$1:$A$20,A:A,0)),
"",
MATCH($A$1:$A$20,A:A,0))=ROW($A$1:$A$20),

ROW($A$1:$A$20),
""),

ROW()))

　　這是一個陣列公式，請用 CTRL+SHIFT+ENTER 完成輸入。公式可往下複製。出現 #NUM! 時，表示名單已經列完了。

　　這個公式的原理，是利用 MATCH(A2,A:A,0) 會在 A:A 找出第一個符合 A2 資料的位置列數，如 E 欄所示。如果 MATCH() 的結果和列數相同，如 E2，表示 A2 是第一次出現。如果 MATCH() 的結果和列數不相同，如 E5，表示 A5 不是第一次出現，也就是重複資料。

　　利用 E 欄這個陣列，再加上 IF() 把陣列中的重複資料清為空白，就可以得到一個不重複名單列數的陣列了。再用 SMALL() 做排序，INDEX() 把列數轉為資料，就可以得到 C 欄的名單了。

　　為了讓公式能使用在大小不同的變動名單上，我們允許名單範圍 A1:A20 中有空白。注意 E12 的 #N/A，就是因為 A12 為空白造成的。公式中的 ISNA() 就是為了把 #N/A 清為空白以免公式出錯才加的。

　　實用上，可以把名單範圍設稍微大一點，當名單大小變動時，就不必每次更改公式了。

2008/10/21 補充：

　　夏日大大提供了一個更簡便的公式，

　　=T(INDEX(A:A,MIN(IF(COUNTIF(C$1:C1,$A$2:$A$20),4^8,ROW($A$2:$A$20)))))

　　小弟大力推薦，有興趣的朋友可以研究一下。

[EXCEL] 多重單位數值的運算

　
　　相關函數：INT() / MOD()

　　１小時２３分４５秒，加上２小時３４分５６秒，答案是？簡單，直接相加，３小時５８分４１秒。

　　１分２３秒４５，加上２分３４秒５６，答案是？呃....

##CONTINUE##

　　２打又７瓶，加上３打又８瓶，答案是？呃....用個 IF(瓶數加總>=12,打數加1,打數不加1) 公式，可以算出６打又３瓶。

　　２打又７瓶，加上３打又８瓶，加上４打又９瓶，加上５打又....？啊....不要再加了啦，IF() 公式中只用 >=12 算不出來了啦！

　　類似這種多重單位數值的運算，很實用，但是無法在 Excel 中直接做到，必須做點手腳。我的想法是，先把所有不同單位的數值，換算成最小單位的數值，直接加減運算後，再換算回多重單位。

　　如下圖，把上面的兩個例子實作出來。

• C2 公式 =A2*12+B2，可往下複製，將多重單位換算成最小單位，１打＝１２瓶。
• C5 公式 =SUM(C2:C4)，只是單純做加總運算。
• D5 公式 =INT(C5/12)，把瓶換算成打。
• E5 公式 =MOD(C5,12)，取得換算成打剩下的餘數。

　　公式重點在於 INT() 和 MOD() 的應用。其實這比較像數學問題，而不是單純 Excel 的問題。

　　進階一點，如果是三重單位的話，如上圖 G1:M5，也是類似的做法。

• J2 公式 =G2*6000+H2*100+I2，可往下複製，將多重單位換算成最小單位，１分＝６０秒，１秒＝１００百分之一秒。
• J5 公式 =SUM(J2:J4)，只是單純做加總運算。
• K5 公式 =INT(J5/6000)，把百分之一秒換算成分。
• L5 公式 =INT(MOD(J5,6000)/100)，把換算成分的餘數，再換算成秒。
• M5 公式 =MOD(J5,100)，取得剩下的餘數。

　　相同的公式模式，只要適當填入各單位之間的換算數量 (12, 6000, 100 等)，就可以完成多重單位的轉換和運算了。

[EXCEL] 今天放假嗎？

　
　　相關函數：WEEKDAY() / NETWORKDAYS()

　　如果你想用 Excel 來計算工時或工資，有個訊息你一定會想知道，今天放假嗎？

　　假日分為兩種，一種是週休二日，一種是國定假日或各其他特別的日子。週休二日可以用星期函數 WEEKDAY() 來判斷，那麼國定假日呢？

##CONTINUE##

　　Excel 提供了一個工作日函數 NETWORKDAYS(開始日期，結束日期，假日列表)，來計算工作日。它會計算兩個日期之間的天數，然後扣掉星期六，星期日和假日列表中的日子，結果就是工作日的天數。如果無法使用此函數，且傳回 #NAME? 錯誤，請安裝〔工具〕〔增益集〕〔分析工具箱〕。

　　運用這個函數，我們只要把開始日期和結束日期設為同一天，就可以知道那一天是不是假日了。傳回１表示是工作日，傳回０表示是假日。

　　如上圖，B2 公式 =WEEKDAY(A2,2)，會傳回星期數，當它等於６或７時，表示是週休二日。

　　C2 公式 =NETWORKDAYS(A2,A2,$G$2:$G$10)，會傳回工作天數，假日列表放在 G2:G10。請注意第９列和第１０列，這兩天不是週休二日，但是因為在假日列表中有這兩天，所以回傳的工作天數仍是０，表示放假。

　　C11 公式 =NETWORKDAYS(A2,A10,$G$2:$G$10)，會傳回 A2 ~ A10 中間的工作天數，它的值會和 C2:C10 個別計算的結果相同。

　　E2 公式 =IF(C2=0,200,100)*D2，用來計算加班費，它假設平日加班每小時一百元，假日加班每小時兩百元。當然，你可以把 C2 的 NETWORKDAYS() 公式直接代入 E2 公式，這樣就不需要 C 欄了。

相關文章：

• 今天放假嗎？要補班啦！：考慮週六週日要補班的計算公式。