學不完．教不停．用不盡

有網友問到：在 Excel 的工作表中，每三列中只有第二列有資料，如何複製第二列的資料至第一列和第三列中？

如下圖，儲存格A2有資料，要將儲存格A1和儲存格A3複製儲存格A2的資料，同欄依此類推的操作，該如何處理？

【公式設計與解析】

1. 原始資料在A欄，在C欄中顯示處理後的結果。

有網友想要在 Excel 的兩個工作表中，使用一個 VLOOKUP 函數查詢，該如何處理？

就我的理解，VLOOKUP 函數無法使用在跨工作表中，所以必須稍加調整公式才能查詢結果。

例如下圖中，工作表1和工作表2含有相同資料範圍但是不同內容的資料，如果想要在一個儲存格中完成查詢的工作表該如何設計公式？

【公式設計與解析】

網友問到：在 Excel 中如何為儲存格內容加上編號？

如下圖A欄為原始內容，在B欄和C欄分別加上不同格式的編號。

【公式設計與解析】

(1) B欄編號格式(例舉三種)

儲存格B2：=ROW(1:1)&"."&A2

網友問到：如下的 Excel 資料表，如何從表格中找出最大值，並且傳回其欄、列名稱？

如下圖，欄標題：甲、乙、…、癸，列標題：子、丑、…、亥。假設資料中的數值，其中沒有重複的內容。

【公式設計與解析】

選取儲存格B2:K13，定義名稱：DATA。

有網友根據下圖左 Excel 的基本資料表，其中包含日期、項目和數值的清單，而項目是由多個不固定的內容所組成。若要轉換成下圖右的資料表，分別依內容和月份計算數值總和，該如何處理？

【公式設計與解析】

選取儲存格A1:C24，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：

日期、項目、數值。

網友問到一個 Excel 的問題，是關於計算累進的乘積和。參考下圖，其中有一個數值區間和比重，如果在儲存格H2輸入一個數值，例如：750，而此數可以分解為：

750=100+100+200+200+100+50

再將每個區間的數量乘以比重：

100*6%+100*6%+200*3%+200*2%+100*5%+50*1%=27.5

輸入：750→輸出：27.5。

延續前一篇文章：Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

如下圖，若在儲存格E3中選取某一個縣市(例如：新北市)，如何能自動列出該縣市的各區名稱？本篇要改良前一篇的公式。

選取儲存格A2:C63，按 Ctrl+Shfit+F3 鍵，勾選「頂端列」，定義名稱：

郵遞區號、縣市、區。

再來，要設定一個名稱：完整區名。並設定其參照到：

網友問到如何在 Excel 中查詢郵遞區號的問題。首先下載郵局提供的郵遞區號對照表：

http://www.post.gov.tw/post/internet/Download/default.jsp?ID=22

稍加整理後，即可用於查詢。(如下圖的A,B,C欄)

【公式設計與解析】

如下圖的 Excel 資料表，其中的表格內容是散亂的資料分佈。要如何取出表格有資料的部分重新排列？

下圖左為原始資料，下圖右為重排後的結果。

【公式設計與解析】

1.

先建立一個輔助欄位，用以計算資料表中每一列有內容儲存格的數量。

網友根據下圖的 Excel 資料表，如何才能求得表一、表二、表三的結果。

【公式設計與解析】

選取儲存格B1:D23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：

條件一、水果、條件二。

有網友問到如下圖的 Excel 資料表中，如何計算各科的加權平均？

下圖中，每個科目都有一個加權和一個分數，要找出各科的加權平權。

【公式設計與解析】

選取儲存格A1:C16，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：

在 Excel 中如果你想要將資料清單中重覆者挑出來並移除，最方便的方式如下：

先選取資料範圍。(本例為儲存格A1:B20)，再使用[資料／資料工具]功能表中的「移除範圍」指令。

Excel 會詢問要列入檢查重覆的欄位：

移除重覆後，會顯示找到幾個重覆，而保留了幾個唯一值。

有同仁問到：在 Excel 中如果開啟 Google 表單下載之填答記錄(如下圖)，當在執行『排序』時，其中有一個條件要根據時間戳記來排序，卻遇到了錯誤結果。

如下圖，當使用「篩選」功能中的『從A到Z排序』：

卻發生了如下圖的錯誤結果。(其中11:31:38卻小於8:05:23)

有網友問到：最近排班時會遇到七休一的問題，如何在 Excel 中如果連續排到 7 天時即給予警示？

以下圖中的排班格式為例(排班的形式可能很多種)，『V』記號表示要排班，在連續 7 天以上被排班時，給予紅色粗體字來識別。

通常這類問題，都只要透過「設定格式化的條件」來處理。假設，整個日期報表是由第 2 列開始，所以從第 8 列開始設定格式化的條件。

1. 選取B8:B27。

2. 選取[常用／樣式]功能表中的「設定格式化的條件／新增規則」選項。

在日常生活中，有時會用到要在一個數值區間中計算含有某個數字的個數，該如何處理？

以下圖的 Excel 例子，要計算 1~500 的數值區間中，共有幾個數含有『4』？例如：4, 140, 403, ...，這些數都含有 4。

【公式設計與解析】

儲存格C2：{=SUM(1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&""))}

網友問到一個 Excel 的問題：

根據前一篇文章：Excel-依據日期區間列出各月人員清單(OFFSET,ROW,COLUMN,陣列公式)，如果日期有跨年時，該如何處理？

本例以2015年7月～12月、2016年1月～6為週期，來列出各月學員的清單。

【公式設計與解析】

網友問到 Excel 的問題：如何將 2015/05/25 轉換為 2015年05月25日？

參考下圖，使用者在輸入日期時，可能會輸入數值格式的 2015/05/25，其數值置於D欄(本例為：42149)，也可能輸入的是文字格式的 2015/05/25 字串。相同公式不一定都適用於數值和文字格式的日期格式。

【公式設計與解析】

儲存格B2：

=YEAR(A2)&"年"&TEXT(MONTH(A2),"00")&"月"&TEXT(DAY(A2),"00")&"日"

網友問到：在 Excel 中有一個學員受訓的記錄表，如何根據下圖左的日期區間，轉換至下圖中各月的清單報表？

【公式設計與解析】

先選取儲存格A1:C23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：學員、開始、結束。

儲存格E2：

你有遇過這樣的問題？在 Excel 產生的統計圖表要貼至 Word 中，但是圖表是一個可以使用下拉式清單選取不同資料的表格所建立，即動態表格共用同一個圖表。

參考下圖，如果你想分別將2013年、2014年、2015年三個資料表建立的圖表複製到 Word 文件中，將會發生以下的問題。

如果你選取了2013年所產生的統計圖：

貼至 Word 文件中：(目前 Excel 文件在開啟狀態)

繼上篇文章：Excel-將日期中月、日第1碼的0去除(LEFT,MID,RIGHT)

如果在 Excel 中，想要將民國年轉換為西元年，並將月、日以2碼表示，該如何處理。

【公式設計與解析】

儲存格C2：=TEXT((LEFT(A2,3)+1911)&MID(A2,4,9),"yyyy/mm/dd")