學不完．教不停．用不盡

上一篇介紹了：Excel-求數列中的每個數值經運算後的平均(陣列公式)

網址：http://isvincent.pixnet.net/blog/post/39452901

運用相同觀念來讓學生練習：計算多個儲存格之字元個數總和。參考下圖的多個儲存格中，每個儲存格的字元個數都不相同，如何計算各種狀況的字元個數總和呢？

【實作】

基本上，使用 SUMPRODUCT 函數即可完成所有計算：

有網友問到，在 Excel 的資料表中有二個數列：數列 A、數列 B (如下圖)，如果想要求取數列 A 中每個數字減 5 後的數值平均，和數值 A 減數值 B 後的平均，該如何處理呢？

網友不想要一個一個數字加以運算後再求平均，有無更簡單的方法呢？這個問題要藉助「陣列」的觀念來運算。

(1) 求數列 A 中每個數字減 5 後的數值平均

儲存格C16：{=AVERAGE(A2:A15-5)}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

有人想要用 Excel 產生一個年曆(12個月)，並且能將星期六日以儲存格底色標示(參考下圖)，並且調整年份時，星期六日底色會隨之改變，該如何處理？

將圖放大來看，每個月都以1日起始，連續產生日期至最後一天，並且只要是星期六日的日期，則以橙色底色顯示，而且每一個月跨二欄表示。

步驟1：

先在［開發人員］功能表中加入一個[微調按鈕]控制項，並將其格式設定如下：(儲存格連結於A1)

在唸國中的女兒問了一個數學題：要解計算 1 到 100 的平方和為多少，一時傻眼不知如何列式？

自從不用準備考試之後，這些數學運算式就離我越來越遠了！但是我卻可以很輕鬆的以 Excel 的公式，在幾秒鐘內即可獲得答案(338350)，女兒也覺得很神奇。

列舉以下二種做法：

(1) 使用 SUMPRODUCT 函數

公式：=SUMPRODUCT(ROW(1:100)^2)

有人問到，在 Excel 中有一個記錄起始時間和結束時間的資料表，想要計算所經過的時間。但其中有可能會有跨日的問題，該如何處理呢？

觀察下圖，只要結束時小時起始時間者，應該就是跨日了。而 Excel 很容易就可以處理經過的時間，只要將兩個時間相減即可。所以公式可以定為：

儲存格C2：=B2-A2+(A2>B2)*24

A2>B2)*24：只要儲存格A2大於儲存格B2，則會傳回 True，否則傳回 False。在運算時 True=1、False=0，意思為如果有跨日則加上24小時。

複製儲存格C2，往下各列貼上。

有個網友問到：在下圖中有一個業務員和客戶的對照表(下圖左)，如果想要摘要出業務員的客戶數(下圖右)，注意到有些客戶是重覆的，該如何處理呢？

【準備工作】

選取儲存格A1:B12，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：業務員、客戶。

【輸入公式】

儲存格E2：=SUMPRODUCT((業務員=D2)*(1/COUNTIF(客戶,客戶)))

在 Excel 中，常常在記錄資料時的方向，可能會和最後的統計報表方向不一樣(橫列相反)，如果再透過轉置的動作再來進行統計運算，或許有些不方便。通常透過 OFFSET 函數即可解決這種轉置的運算。

以下圖中的 A 區和 B 區二個資料為例，如果想要計算甲、乙的平均，並且將兩個表合併為一個表，列在下方的統圖表中，公式如何處理？

1. 計算 A 區各月平均

儲存格B13：=AVERAGE(OFFSET($C$2,COLUMN(A1)-1,,,2))

COLUMN(A1)=1，往右複製時，公式自動會轉換為COLUMN(B1)=2、COLUMN(C1)=3、…

有網友問到：在一個 Excel 的資料表中(參考下圖左)，含有「日期、單號、數量」三個欄位，要如何找出第 3 個單號 25 (由上而下)所對應的日期呢？

【準備工作】

建立一個輔導欄位，在儲存格D2輸入公式「=COUNTIF($B$2:B2,B2)」，複製儲存格D2，往下各列貼上。該公式用以計算由第一個儲存格B2開始，每個單號出現的次數。

選取儲存格B2:D16，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：單號、輔助。

【輸入公式】

有網友問到：在 Excel 的資料表中常會遇到運算結果產生錯誤訊息的情形，例如執行了除數為 0 的除法時，將會產生「#DIV/0!」錯誤訊息(參考下圖)。如果想要避開這個錯誤訊息，而顯示一段說明文字，通常在 Excel 2007 之後的版本，可以使用 IFERROR 函數。

儲存格D2：=IFERROR(A2/B2,"除數為0")

複製儲存格D2，往下各列貼上。

當因儲存格B2為 0 時產生了錯誤訊息，改以顯示「除數為 0」字樣，否則顯示運算的結果。

但是在 Excel 2003 版中並沒有 IFERROR 函數，該如何處理運算發生的錯誤訊息呢？

有網友問到：在 Excel 的資料表中，某些儲存格含有一些以逗號隔開的數字，要如何計算每個儲存格中的數字總和及每個數字出現的次數？(參考下圖)

這個題目要用到陣列的觀念，不過使用 SUMPRODUCT 函數很容易就可以解決了。本例以每個數字的大小為 0 ~ 9 之範圍來說明，每個儲存格固定有 9  個數字。

(1) 計算儲存格中所有數字的總和

儲存格B3：=SUMPRODUCT(VALUE(MID(A3,ROW($1:$9)*2-1,1)))

ROW($1:$9)*2-1：產生 1, 3, 5, … ,15, 17 的數字陣列。

又有人在問在公式中的使用的多條件 AND 邏輯運算相關問題，這是日常生活中最常被利用的觀念。因為很多人一看到邏輯運算，頭都快昏了，而且除了 AND 之外，還有 NOT、OR、XOR 等邏輯運算。

但是公式要用的好，了解這些邏輯運算會有很大的幫助，所以這是在學習過程中要必的功課。

在說明之前，先來了解 AND 邏輯的運算概念。在下圖中可以發現當 A 和 B 這二個條件都為 True 時，結果 Y 才會得到 True 的結果。

以 IF 函數來練習，參考下圖中，如果以三個條件將全部符合者以「V」表示，未符合者以「X」表示。

最近學校中常會討論到個資和資訊安全的議題，也發現同仁對於基本的 Word 文件加密，好像也不是很熟悉，特別在此做些簡單的介紹。

Word 文件是一般行政人員最常接觸的文件，很多時候是需要將文件內容加以保護的。而你在儲存檔案時，就可以順便處理一下文件資訊，並且指定是否要為文件加密。

當你為文件另存新檔時，參考下圖，先將文件資訊的顯示區加大(將分隔線往上拖曳)。

每個文件資訊的欄位你都可以修改，如果有不想外露的資訊，可以就此刪除。

在一個 Excel 的工作表中，有人想要將一個含有年月日的日期轉換成另一種表示法：月日的日期+星期幾，該如何撰寫公式呢？

儲存格C2：=TEXT(A2,"mm/dd")&"("&RIGHT(TEXT(A2,"[$-404]aaaa;@"),1)&")"

複製儲存格C2，往下各列貼上。

TEXT(A2,"mm/dd")：利用 TEXT 函數，將儲存格A2中的日期轉換為顯示月日的日期。

TEXT(A2,"[$-404]aaaa;@")：利用 TEXT 函數，將儲存格A2中的日期轉換為顯示星期幾。

RIGHT(TEXT(A2,"[$-404]aaaa;@"),1)：利用 RIGHT 函數，取出上述星期幾的最右邊一個字元。

和同事談到了在 Word 中編輯長文件的一個現象，例如要將在第 1 頁中的某一段文字想要搬移至第 4 頁的位置。如果使用滑鼠直接拖曳，往往會在第 4 頁附近來回擺動，才能準確的拖曳至正確的點上。

可以試著採行第一種方式：分割視窗，上半部視窗顯示第 1 頁，下半部視窗切換至第 4 頁。選取第 1 頁中的文字，拖曳至下半部視窗中。(參考下圖)

或是你也可以使用第二種方式，不管是否分割視窗皆可，先選取第 1 頁中的文字，然後移至第 4 頁的位置，按著 Ctrl 鍵，按一下「滑鼠右鍵」，也會執行搬移的動作。

有網友問到：在 Word 中如何能讓小數點的位置對齊？在一段文字中如何對齊？在表格中如何對齊？

在 Word 中有提供「定位點」的工具，讓文字在段落中能對齊某個位置。參考以下的做法：

1. 先在尺規的最左側找到定位點符號，每按一下滑鼠左鍵一下，就會切換一種定位點。

2. 持續切換至「對齊小數點之定位點」。

3. 在尺規上想要對齊小數點的位置按一下滑鼠左鍵，該處即會被設定一個定位點。(參考下圖)

4. 在段落中按一下 Tab 鍵，會跳至第一個定位點處，輸入含有小數點的數字，該數字的小數點會落在你設定的定位點處。再按一下Tab 鍵，會跳至下一個定位點處。(參考下圖)

有網友問到：在 Excel 中如何將多筆含有公式的儲存格，把運算公式去除，只留下運算結果。

例如下圖中的資料表裡，有些儲存格含有運算公式：

按一下 Ctrl+` 鍵，可以切換顯示/隱藏運算公式和運算結果，方便來檢查公式：

如果你想要將某一個儲存格中的運算公式，改為只留下運算結果(去除公式)，則只要在儲存格中進入編輯公式狀態，按一下 F9 鍵：

在 Excel 中常會用到複製／貼上功能，如果是複製文字藝術師，則貼上也是文字藝術師。但是如果你遇到某些電腦中缺少你所用的字型，則設計再漂亮的效果，也會全部顯示不出來。你可以選取在貼上時，以貼上圖片的方式來處理，則可以在後續有很多可再變化的效果。

又例如：你複製了某些儲存格之後，也可以使用選擇性貼上中的貼上圖片選項，則儲存格以圖片顯示，其中的公式就不會被看到，而且數值不會被修改。

以下在文字藝術術師效果、儲存格、統計圖、SmartArt 等四個方面，來比較貼上圖片的結果：

(下圖左側為原始內容，右側為貼上圖片後的效果。)

有人有這樣的需求：基於排版的需要，想要將 Excel 中產生的統計圖旋轉90度，可是不知該怎麼做？

遍尋 Excel 中的圖表工具相關設定，好像都沒有提供這樣的功能，只好來採用間接的方式來完成。我們可以將圖表複製為一張圖片，圖片就可以旋轉了。參考以下步驟：

1. 先產生想要的統計圖。

2. 選取圖表，並按一下 Ctrl+C 鍵，複製這個圖表。

3. 在儲存格上按一下右鍵，選取貼上選項：圖片。

有人問到：在 Excel 中如果選取數個儲存格執行合併(跨欄置中或是合併儲存格)，則只會保留第一個儲存格的內容，是否有辦法保留其他儲存格的內容呢？

在 Excel 的工作表中選取數個儲存格(例如：3個儲存格)，按一下「跨欄置中」或是「合併儲存格」按鈕，會進行合併儲存格的工作。

Excel 會提醒你，合併後的儲存格只會保留左上角儲存格的內容：

有位老師問到：在 Word 文件中如果想要將一張圖(照)片以某種標準尺寸列印出來，要如何操作比較快？而非列印出來後再反覆調整，非常不環保！

以這位老師需要的 6" X 4" 為例來說明：

1. 選取[檔案/選項]指令，再選取[進階]標籤。

2. 在[顯示]區中找到「顯示度量單位」，在下拉式清單中選取「英吋(in)」。

3. 在文件中插入你要的圖(照)片，選取這張圖(照)片。