學不完．教不停．用不盡

今天一時興起，趁著假期取得本網站(http://isvincent.pixnet.net)的網頁流量來分析一下，想要看看最近五年(超過1800筆數據)中，各星期幾和各月份的相關數據。

一、分析各週每天和各月份的平均值

【公式設計與解析】

先選取所有基本資料的儲存格範圍，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、星期、瀏覽量。

網友根據這篇：Excel-檢驗多個儲存格是否每一個都是空白(COUNTIF,LEN,ISBLANK)

想要進一步設定只判斷指定儲存格是否否都是空白，該如何處理？

如下圖，若只想取用儲存格B1、儲存格C1、儲存格E1、儲存格G1來判斷是否其中任一個儲存格有內容。

【公式設計與解析】

根據前一篇文章：Excel-儲存格內容合併與分離(CHAR(10),FIND,MID)

如果儲存格內容是每個儲存格不相同，如何來設計公式？(參考下圖)

(1) 分離第一列字串

儲存格E2：=LEFT(C2,FIND(CHAR(10),C2)-1)

網友問到：在 Excel 中如何Excel-檢驗多個儲存格是否每一個都是空白？(參考下圖)

通常使用 COUNTIF 函數即可得到結果：

儲存格D4：=IF(COUNTIF(A1:G1,"")=7,"N","Y")

本例中要練習陣列公式，以下提供二種公式來練習。

同事問到：如何在Word、Excel、PowerPoint中將一個矩形的照片，轉變成正圓形(非橢圓)？例如下圖左的矩形照片轉換成下圖右的圓形照片。

的確，這種效果是在文件或簡報中常會被使用到。但是下圖右上的正圓，其中的照片沒有變形；但是，下圖右下的正圓，其中的照片已經變形。原因是因為將橢圓形硬拖曳成正圓形，所導致的照片變形。

如何做一個照片不會變形的正圓？參考以下的步驟來練習。

1. 選取矩形照片。

2. 選取[格式／裁剪]中的「裁剪成圓形」選項，再選取「橢圓」。（因為沒有「正圓」）

(回答學校同事提問)開學了！一學期又重頭開始了。當然一學期要做的事也陸續展開了。.

學校訂定的行事曆和全校師生息息相關，如果能將學校行事曆轉換至 Google 日曆和自己的日曆加以整合，就不會遺漏學校活動。如何將學校行事曆轉換至 Google 日曆？

通常學校的行事曆沒有依照什麼標準來呈現，但至少會有日期、處室組別、工作事項等，並且以表格方式呈現。

而 Google 日曆則有提供可以使用 Excel 檔匯入的方式，相當方便而且實用。你可以先請參考 Google 的說明網頁：https://support.google.com/calendar/answer/37118?hl=zh-Hant

Google 日曆接受 Excel 的 CSV 格式，其檔案內容可以使用的欄位名稱如下所示：

網友問到：在 Excel 中如何快速產生如下圖的字元排列組合？

在下圖中，是由「忠孝仁愛信義和平」字組所排列而成，每一欄／每一列都會移位一個儲存格。

【公式設計與解析】

儲存格A2：

=MID("忠孝仁愛信義和平",MOD(MOD(ROW(1:1)-1,8)+COLUMN(A:A)-1,8)+1,1)

網友提問：下圖是 Excel 中的資料表，如何將下圖(上)的資料轉換為下圖(下)的呈現方式？

該資料表是由 3 組數值合成一列，每組數值有 6 個，共有 3 列的資料：

【公式設計與解析】

儲存格A7：=OFFSET($A$1,INT((ROW(1:1)-1)/3),MOD(INT(ROW(1:1)-1)*6+

​​ ​​

網友問到 Excel 的問題：如何在含有日期清單的資料中，列出指定日期區間內的資料？

如下圖左，有一個「日期、項目、數值」組成的資料清單，想要找出指定的兩個日期之間的資料(參考下圖右)。以下提供三種方式來練習。

1. 手動：使用篩選工具

網友問到：每個箱號有10個序號，如何自動產生一組編號？

參考下圖，在 Excel 中可以輕鬆的藉由公式產生相同邏輯的連續號碼。

【公式設計與解析】

(1) 箱號

網友問到在 Excel 中的問題：如何將每個 25 日後的日期算為下個月？參考下圖，3/30要傳回 4 月，而 12/27 要傳回 1 月。

【公式設計與解析】

儲存格C2：

=MONTH(A2)+(DAY(A2)>25)*1-((MONTH(A2)+(DAY(A2)>25)*1)>12)*12

在 Excel 中有一個數值清單，其中的數值有重覆的清況(如下圖)，如何在其中找出指定第Ｎ大的數值？

在下圖中，最大值是 15，有 3 個，接下來是 14，有 1 個，接下來跳至 12，有 2 個。通常我們會使用 LARGE 或是 SMALL 來找第 Ｎ大或第 N 小的數值，但是這兩個函數均不會忽略重覆的數。該如何解決？

【公式設計與解析】

儲存格C2：=IFERROR(LARGE(IF(FREQUENCY($A$2:$A$25,$A$2:$A$25),

在 Excel 中有一個資料表(如下圖左)，想要根據『座號』來取得姓名、成績和名次的資料，該如何處理？(如下圖右)

通常你會使用 VLOOKUP 函數或 HLOOKUP 函數來執行查詢工作，在本例中先觀察這個資料表，是由姓名、座號、成績、名次等四個欄位所組成，若要使用 VLOOKUP 函數來查詢，當取用資料在A欄~D欄時，因為A欄是姓名而非座號，所以無法使用。若取資料B欄~D欄，可以順利用座號查詢，但是無法查詢姓名，因為姓名欄位不在資料範圍。而且查詢結果的顯示順序和原資料的排列順序並不相同。

如何以一個公式便能完成多個儲存格的查詢？還有其他方式可以達到查詢結果，來練習看看。

【公式設計與解析】

有時你會在網頁上看到資料表，當你要將網頁上的資料放至 Excel 活頁簿中，除了複製／貼上之外，你還有別的選擇。

你也可以直接將網頁儲存為 HTML 格式(*.htm 或 *.html)，再由 Excel 中開啟。例如：以下的網頁中有一個表格，先將該網頁存檔。

存檔後，你會得到一個檔案(*.html)和一個同名資料夾(*_files)：

點入該資料夾，可以看到檔案內容：

網友想要了解如何在 Excel 中快速找出最靠近某日的星期幾日期，例如：在下圖中要找出最靠近 2016/11/19 的星期日、星期五、星期三的日期，該如何處理？

(1) 最近的星期日

儲存格C2：=A2+7-WEEKDAY(A2,2)

本例公式 WEEKDAY 函數使用參數『2』，表示要以數字 1～7 代表傳回星期一～星期日。參考下圖可以發現，指定不同參數可以得到不同的傳回值。

同仁問到一個實用的問題：在 Excel 活頁簿中的每個工作表內容只有一頁大小，當在列印多個工作表時，希望這些工作表的頁碼能連續顯示，該如何處理？

這個操作，其實很多人常會用到。以下圖為例，這個活頁簿中有四個工作表，列印時每個工作表只佔一頁，現在要四個工作表都要列印，並且頁碼也要連續列印。

參考作法：

1. 點選第一個工作表名稱(工作表1)，按著 Shift 鍵，再點選最後一個工作表名稱(工作表4)。如此，可以選取這四個工作表。

2. 開啟[版面設定]對話框，進入[頁首/頁尾]標籤。

有同仁問到：在 Excel 中常會用到一些運算，通常在公式中使用『+、-’*、/』，但是如果是想要在表達乘法或除法時使用『×、÷』符號，該如何輸入比較快速？

其實，不管是不是在 Excel 中，都可能會用到乘法和除法『×、÷』符號。在此，先提供了二種輸入方式：

1. 在英數輸入法之下

在英數輸入法之下，可以按著 Alt 鍵不放，再依序按下Keypad上的 2, 4, 7 鍵。

同理，

網友問到：在 Excel 中如何將三欄的資料轉換為表格形式呈現？如下圖，圖左中每一列的資料都沒有重覆，要將其轉換為左上和左下兩種表格呈現，該如何處理？

網友常問到 SUMPRODUCT 函數和陣列的關係，藉這個例子來比較一下。

【公式設計與解析】

(1) 定義名稱

網友問到計算年資的問題：如何使用 Excel 來分別計算下圖中男生和女生各個年資的人數？如下圖，每個人的資料有性別和年資二個數據，要分男女生來計算不同年資的人數。

【公式設計與解析】

選取儲存格B1:C151，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：性別、年資。

常有網友提到的 Excel 中的問題：取出儲存格中字串中包含的數字，該如何處理？

如下圖，一個儲存格中的內容，數字可能出現的位置：左邊、中間、右邊，如何取出其中的數字。而該公式也適用空字串、全無數字、全是數字的儲存格。

【公式設計與解析】

儲存格B1：{=MAX(IFERROR(MID(A2,ROW($1:$10),COLUMN($A:$J))*1,0))}