學不完．教不停．用不盡

有網友問到，在 Excel 中有一個多列的資料表，如何將多列轉換成同一列？參考下圖。

【公式設計】

儲存格A1：
=OFFSET($A$5,INT((COLUMN(A:A)-1)/5),MOD(COLUMN(A:A)-1,5),,)

在 Excel 的資料表中每個儲存格是由英文字、中文字和數字所組成，如何將每個字分離，並且依序置入儲存格中呢？(參考下圖)

【公式設計】

觀察每個儲存格的內容，其中每個字元以「,」分隔，並且包含英文字、中文字和數字。

儲存格C2：=MID(SUBSTITUTE($A2,",",""),COLUMN(A:A),1)

有網友問到在 Excel 的資料表中，如果要分別計算由文字和數字組成的儲存格中，其數字的個數和文字的個數，要如何處理？(參考下圖)

(一) 儲存格中的字數固定

參考上圖，其中儲存格內的字數固定，本例假設每個儲存格中有10個字元。

(1) 儲存格B2：｛=COUNT(MID(A2,ROW($1:$10),1)*1)｝

有些人的確對於 Excel 會有恐懼感！因為要設計公式，並非人人都能勝任愉快！如果不需使用公式就能處理資料該有多好啊！這是你的心聲嗎？

其實 Excel 本來就提供了許多各式各樣的工具，透過操作程序，一步一步的達到想要的結果。所以，不想寫公式，就善用 Excel 的各項工具吧！

以 Microsoft Excel 2013 為例，例如：在下圖中的資料表，如果要根據「性別、年級、認證」等欄位來交叉分析，設計公式對很多人而言是一大負擔。這次要來介紹在樞紐分析表中使用交叉分析篩選器。

檢視：三年級，只要按一下「三年級」，即可只篩選三年級的資料。

在學校的文書處理作業上，大都還是使用 Microsoft Office 的各種軟體來處理文件。學校同仁問到：做了一個簡報檔(PPT)，想要請其他同仁看看是否有要修改之處，由於檔案容量頗大，不適合使用電子郵件傳遞。該如何以簡便的方式讓大家看到，而且最好是在行動裝置也能檢視。

大家一定會想到使用雲端硬碟，沒錯雲端硬碟的使用，現在已經相當普遍了。常用的雲端硬碟不外乎是 Dropbox、Google Drive、Microsoft OneDrive 等，如果把檔案上傳至雲端硬碟，再透過其線上檢視器，可以「不用下載檔案」即可檢視，並且可以在行動裝置上檢視，相檔的方便。

例如：以 Dropbox 為例，當你上傳了一個大容量的簡報檔，並且將檔案分享網址透過通訊軟體號召大家來看。使用者用手機上的瀏覽器開啟你傳送的網址時，Dropbox 的網頁界面會提醒你可以「在應用程式中開啟」。

(如果按下下載的按鈕，可以直接下載至手機或是儲存至你的 Dropbox 空間)

如果你有安裝 Dropbox App，可以選取這個 App 來開啟檔案，或是選取一個瀏覽器來檢視檔案。在此，建議使用 Dropbox Ａpp 來檢視，簡報檔變成由上至下接續的一張一張投影片，用手上下滑動檢視投影片。

有網友根據先前文章：Excel-刪除重覆者(只留下第一個)(COUNTIF)，想詢問如果要只留下最後一個該如何處理？

參考下圖，其中課程編號和上課地點已經把相同者集合在一起了，現在來看看如果只留下相同者的最後一筆。

【設計公式】

儲存格F2：=IF(COUNTIF(A$2:A2,A2)<COUNTIF(A$2:A$26,A2),"",A2)

複製儲存格F2，貼至儲存格F2:I26。

有網友要將在 Excel 中的一個資料表(如下圖左)，摘要成下圖右的各種表示方式，該如何處理？

這類問題，感覺上好像是被問過最多次的問題，不過也是日常生活和工作中最被需要的技巧。但是對於初學者往往不知從何下手，可能也不了解只要使用 SUMPRODUCT 函數就能解決。

【函數設計】

先提一下 SUMPRODUCT 函數，參考微軟網站的說明：

一個同事學習完 Google 表單之後，實際應用在工作上，開始會發掘一些問題了！這是典型的做中學，相信會愈練習愈進步的。

問題是這樣的：做好了一個可以使用的表單。

開始讓填答者填寫，也由回覆內容的試算表中取得結果：

現在，想要加入一些運算公式，以便統計或分析。

學校同仁常在做一些訪視或評鑑的報告時，會遇到表格中要輸入較多文字的狀況，就會有文字被擠到下一頁，常常造成一些困擾。最近又有人提到這個問題了，該如何解決呢？

解決之道是：先選取整個表格(表示選取表格中的每一列)，然後在表格上按一下右鍵，選取「表格內容」，然後在「列」標籤下勾選：允許列超越分隔線，讓一列可以跨頁呈現。

如果能在一開始未輸入資料時，就能注意表格中列的設定，就可以避免日後不斷的補充設定。(Word 的預設值如果是可以跨頁顯示就好了！)

如果在 Excel 中的多個資料表具有相同的外型，即表格一樣資料不一樣，想要製做成動態圖表，那就容易多了。因為網友常會問到用一個圖表就要顯示多個資料表的內容，希望使用表單工具(例如：微調按鈕、下拉式清單等)，即可呈現不同的資料表的統計圖表。該如何製作這樣的圖表呢？

本例以 8 個工作表分別是2015年1,2,3,4月和2014年1,2,3,4月的工作表(如下圖)，其資料表名稱的格式一致(皆為2015-1，表示2015年1月)。

藉由調整按鈕和下拉式清單來改變圖表的資料來源：

首先，你必須使用[開發人員]功能表來新增控制年和月的微調按鈕控制項表單：

網友根據下圖的 Excel 工作表，想要依據起迄日期區間，計算每一天的人數小計，該如何處理呢？下圖中有入院日期和出院日期的日期區間，要小計每一天有多少病人數。

【設計公式】

儲存格G2：=SUMPRODUCT(($B$2:$B$18<=F2)*($C$2:$C$18>=F2))

公式相當於：

繼上次這篇：Excel-對一個資料表執行多個運算(SUMPRODUCT,SUBTOTAL)之後，有網友想要將多個運算以下圖的方法來呈現，該如何處理？

在下圖左中的運算如果是要各種小計得到自行設計多組公式，而下圖右的做法是以下拉式清單的方式來處理，不用設計任何公式。

這個解決方法其實也是結合「篩選功能」和儲存格範圍轉換為表格而來。而一般在工作表中的一個儲存格區塊只能稱為儲存格範圍，在此所稱的「表格」是必須經過定義，並且經過轉換。

請參考以下的步驟來操作：

1. 選取儲存格A1:D17。

在 Excel 中，如果根據一個資料表(如下圖左)，要計算其人員摘要出來的次數和小計(如下圖右)，建議做法是使用 SUMPRODUCT 函數，很方便。

儲存格F2：=SUMPRODUCT(($B$2:$B$21=$E2)*1)

儲存格G2：=SUMPRODUCT(($B$2:$B$21=$E2)*$C$2:$C$21)

複製儲存格F2:G2，貼至儲存格F2:G7。

你也可以選取儲存格B1:C21，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：人員、內容。

我的部落格最近突破了 10,000,000 個點閱，本人非常感謝廣大讀者願意在此駐足停留。有些讀者給予網站文章批評指教，讓我獲益良多，也能佳惠廣大讀者。這其中也讓我思考了：學不完．教不停．用不盡。

今天，剛好藉由讀者在這篇文章(http://isvincent.pixnet.net/blog/post/44920363)中的留言，特別提出一些說明和澄清，以免造成誤解。

1. [教的角度]本人的文章，例如 Excel 教學，絶大部分都只是提供「一種」解決方法，而 Excel 的解法本來就多種解法而無標準答案，本人並非要提供「最佳」解決方案。因此並不會特意考量資料量的大或小來設計公式，因此也不會特意考量「時間」因素和「空間」因素的最佳化演算法。

2. [教的角度]本人的文章，例如 Excel 教學，絶大部分都只是提供「一個」解決方法，無法窮舉各式各樣的解決方法。

3. [教的角度]本人的文章，例如 Excel 教學，只是我個人的教學平台，而本人是個高中老師，主要對象只是 Excel 的初學者，所以基於教學，會盡量使用這個程度的人能接受的方式來教學，所以設計的公式對某些程度較好或是需求較高的人或許可能無法接受。在我受的專業訓練裡，本來就應該「因材施教」，一般教學、增廣教學和補救教學，相同範圍也是使用不同的教材和教法。

4. [學的角度]在教學原理中，老師都是希望學生能看懂文章內容，當用在自己的問題之上時，要自行設計公式，而非把老師的公式直接套用，那就是死背公式而已。所以讀者要根據自己的問題去變化公式來解決問題，這樣才能長遠的使用和應用 Excel。(盡量不要把公式套用在不適合的問題上，卻怪公式不對。)

網友想要在 Excel 中的一個資料清單(下圖左)，轉換為矩陣計算小計(下圖右)，該如何處理？如果資料範圍會增加時，公式如何設計？

(1) 資料範圍固定

若是資料範圍固定，若要定義名稱，選取儲存格A1:B22，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：部門主管、考績。其定義的內容如下：

部門主管：=工作表1!$A$2:$A$21

參考下圖，網友想要將在 Excel 工作表中儲存格裡的英文字和中文字分離，該如何處理呢？

由圖看來英文字是半型字，中文字是全型字，如果要透過函數來處理，目前我只會處理半型字串接全型字或是全型字串接半型字的字串分離。如果是全型和半型散亂分佈的字串，我也束手無策。

【公式設計】

需要用到以下二個函數：

＊LEN：傳回文字字串中的字元數。

有同仁看到我在 Word 上繪製一張比較大、比較複雜的圖案時(例如以下圖案)，不經意談到繪製時的困擾。根據其描述，我才發現原來他只需要多做一個步驟，就可以改進所有的不方便。

因為在 Word 的版面上插入圖案時，會和版面上的文字段落共用一個編輯區，滑鼠指標預設在 I 字型游標，而將滑鼠移至圖案時，會改為滑鼠指標的游標。所以造成選取圖案時的不方便，而且增減文字時，圖案的位置也會相對移動。我想大多數人都有這樣的類似經驗吧。

其實，你只要在開始插入圖案前，先按[插入／圖案]功能表最下方的「新增繪圖畫布」。

然後，在文件版面中插入一個畫布：

有讀者根據這篇文章：Excel-向下填滿空白儲存格，想要變化為「向上填滿空白儲存格」，該如何處理呢？參考下圖，空白的儲存格要填入向下最靠近的儲存格內容。

【參考步驟】

1. 選取儲存格A2:B22。

2. 按 Ctrl+G 鍵，開啟[到]對話框。

有網友提到這樣的困擾：在 Excel 中有一些各種電話的格式，如果想要把其中的「(、)、-」去除，而且要保留第一碼的「0」。又因為自己的區域號碼是 02，所以想要把 02 開頭的電話號碼，全部去除 02。該如何處理？

這是個很多人需要解決的問題，不過只要使用一個函數：SUBSTITUTE，即可輕鬆去除所有符號。

【公式設計】

1. 去除符號

有網友問到：在下圖中有多個不同系的資料，如何以一個公式，自動列出各系的排名？且積分相同時應列相同名次。

一般人要計算排名，大概都會利用 RANK 函數，不過在此，主要是要使用 SUMPRODUCT 函數即可搞定。

【準備工作】

選取儲存格B1:D29，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：系別、積分。

【設計公式】