學不完．教不停．用不盡

(進階版請參考：http://isvincent.pixnet.net/blog/post/46709338)

在 Excel 中，如下圖的資料表，如果想要根據『人員』和『項目』交叉得到一個數值，只要使用類似 INDEX 函數即可完成，但是觀察這個資料表，其中有的項目具有多個重覆，如果要將某個人員的所有相同項目予以加總，該如何處理？

【公式設與解析】

一般使用 INDEX 函數可以很容易求得『人員：卯、項目：甲』的對應結果(儲存格C5)：

有同仁想要在如下圖的 Excel 資料表中，用勾選的方法來決定某一天是否要列入小計，該如何處理？

要做到勾選動作，可以使用開發人員功能表的「核取方塊表單控制項」，所以你得先在功能表中加入「開發人員」功能表。

【操作與公式設計】

首先，選取[開發人員／控制項]功能表中選取[插入]選項 ，再選取「核取方塊」控制項。

有網友想要將在 Excel 中如下圖左的原始資料表(看起來像電話號碼)轉換為如下圖右的資料格式，該如何處理？

觀察下圖，看起來是要將『(』消掉，然後將『)』轉換為『-』，其餘不變。

以下分為二個部分來設計公式。

1. 轉換為文字型態

儲存格C2：=SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","-")

觀察同事在使用 Excel 時，操作速度一直快不起來，我建議要適時的使用快速鍵，例如：當選取了儲存格範圍後，要執行加總時，除了使用[加總]按鈕之外，你也可以按一下『Alt+=』，由 Excel 幫你填人加總公式。

已有提供的快速鍵，多用就會多記，熟了自然就能生巧。例如：

(1) 要將檔案另存新檔時，按 F12 鍵，會比移動滑鼠來點選功能表選單和選項來的快。

(2) 按下 Ctrl+1 鍵，可以快速開啟[儲存格設定]對話框。

不過，不是你想要的動作，都配有快速鍵，有一個通用的方式，倒是可以試試。

有網友問到：如下圖左半部的資料表，其中是每個學生在不同日期所選修的課，如何根據這個資料表摘要出每門課的學生(參考下圖右半部)？

以下數個部分慢慢來分解。

1. 使用 SUMPRDOCUT+LARGE 函數

有網友問到：在 Excel 中有一個資料表(如下圖左)，其中的『資料』欄位如果順序被打亂，如何讓『數值』欄位正確的對應(如下圖右)？

觀察下圖中的BBB資料順序雖改變，但是對應的數值，其出現順序仍依原順序呈現。

【公式設計與解析】

有網友問到：在 Excel 中有三個工作表：DATA1、DATA2、DATA3，如何才能在DATA3中顯示將DATA1和DATA2中相同位置儲存格的相乘積？

例如，DATA3的儲存格A1：=DATA1!A1*DATA2!A1

因為工作表中的儲存格範圍很大，該如何操作比較快呢？

通常在儲存格A1輸入公式後，再複製其他儲存格即可，也沒有更快的方法，但是在此給的建議做法是這樣：

(1) 將要輸入計算公式的儲存格範圍設定一個名稱。

本例假設將儲存格A1:LU4000，設定名稱：相乘積。

有網友問到：在 Excel 中有一個打卡範例(如下圖)，假設星期一至星期五的 9:00~18:00 列為正常上班時間，其他時間列為加班時間，如何能將不在上班時間內的打卡標示為「加班」？

【公式設計與解析】

儲存格B2：=IF(OR(WEEKDAY(A2,2)>5,TIME(HOUR(A2),MINUTE(A2),0)>

現在的家庭裡的電視機有線電視信號來源，大多已經不是由傳統的同軸電覽而來的電視節目(類比信號)，而是透過數位機上盒(Digital Set-Top-Box，DSTB)，讓你可以收視數位信號的節目。

最近家人反應切換不同台(channel)時，會有聲音忽=忽小而且差異很大的現象，造成不少困擾。研究後才發現：數位機上盒的聲音有放大作用(例如：0~25)，而電視機的聲音也有放大作用(例如：0~100)，以前都是獨立操作電視機的音量，現在兩個連接裝置都能調整音量(如下圖，以我家的兩個裝置為例)，因為調整不當，可能會造成這類現象。

參考下圖，在第一級(機上盒)放大是 0~25時，再經第二級(電視)放大所得的各種放大倍數列表。例如：

第一級設定：5，經第二級放大後可得可能結果為 0, 5, 10, 15, 20, ... , 500。

第一級設定：25，經第二級放大後可得可能結果為 0, 25, 50, 75, 100, ... , 2500。

今天和人談到：利用 Excel 來解數學公式是否是一件困難的事？讓我想到了如果使用 SUMPRODUCT 函數，可以來解一些數字具有規則且和「乘積和」有關的運算。

參考下圖，其中有五個常見的公式，如果你會寫程式，利用「廻圈」的運作，很容易就可以求得解答。但是在 Excel 中如何仿廻圈的效果呢？剛好以下的五個例子，都可以使用 ROW 函數來產生具規則的數列，代入 SUMPRODUCT 函數很容易求得解答。

【公式設計】

學科小老師調查了購買相關補充資料的名單給某個老師，長的像下圖左的內容。老師問說可以轉換為如下圖右的資料表，以方便計算購買數量及分發補充資料。

這是個常見的問題，觀察下圖左，這個小老師也算整理的很工整(格式對齊)，其中「全」表示三種補充資料都要採購。現在就來看看如何轉換為表格式的資料。

【公式設計與解析】

1. 取出座號

網友問到：在 Excel 中有一個如下圖(上)的資料表，要轉換成下圖(下)的格式，該如何處理？參考下圖，上下兩個表格，是要把「進貨、銷貨、收發」由橫向轉換為直向。現在來看看要如何處理。

【公式設計與解析】

1. 查詢每月「期初」的值

有網友問到：在一個工作表中如果使用某些物件便於各種操作，但是有時又不想讓它顯示，該如何處理？

例如如下圖的工作表，其中有三個「微調按鈕」物件，用以調整儲存格B2所顯示的時間。如果不想顯示微調按鈕，以免時間又被改變，該如何操作呢？

.

你可以在[常用／尋找與選取]功能表中，選取「選取窗格」選項。

在每個不想顯示的物件右方，按一下「眼睛」圖示，這個眼睛如果瞇起來了，表示該物件被隱藏了。微調按鈕不見了，自然也就無法被操作了。

有網友問到：如果在 Excel 中有地址或地名的清單，如何製作超連結，可以在點選後直接在 Google 地圖上顯示該地點？

以下由台北市政府教育局網站取得臺北市公立高中校址及電話一覽表，我們要建立「查詢地圖」欄位，以方便點選校名後，即可在 Google 地圖上標示出該學校的位置。

【做法與說明】

1. 使用地址

常有人會問到在 Excel 中以日期為主的每日或分組的數值計算，例如：計算最近幾筆和最近幾天的每日平均，或是計算幾筆和幾天的日期區間的分組平均。本篇就來看看這樣的差異，如何處理？

1. 計算最近幾筆和最近幾天的每日平均

參考下圖，要計算以 8 天為單位來計算最近幾筆和最近幾天的每日平均。

(1) 計算最近 8 筆的每日平均

儲存格D9：=AVERAGE(C2:C9)

最近整理了幾篇和二進制轉換有關的先前文章，請自行點選檢視。

(1) Excel-二進制和十六進制互轉

(2) Excel-以2的補數模擬二進制的減法

在使用 Excel 時，「設定格式化的條件」是個很有用的工具，經由一些巧妙的安排，讓公式產生的結果能更添美化和趣味，以下例舉 6 個以前寫過的應用，拿出來複習一下。

(1) Excel-利用格式化條件設定顯示16X16點陣字

(2) Excel-BMI值計算(格式化的條件規則)

有網友問到：如下圖的 Excel 資料表(右圖)，如何將「報名」欄為「Yes」者取出摘要成左圖的結果，其中每個學員的 Email 和電話分成二列呈現。

為了說明方便，選取F欄中所有「報名」欄的內容，定義名稱：報名。

【公式設計與解析】

1. 列出學員姓名

以前的一位老同事最近問到一個常見的問題：如何在 Word 中插入圖片時，能自動調整圖片大小以符合表格的欄位寬度？

以 Word 2010 為例，下圖是一個 2X2 的表格，想要在這個表格中插入四張圖，可是四張圖片的大小並不相同，希望能插入圖片時，不要影響表格的欄位寬度，該如何處理呢？

在預設狀態下插入圖片時，會改變儲存格的大小。

要解決這樣的問題，可以先調整好你要的表格、欄位的大小，然後選取表格，再選取[表格工具／版面配置]功能表中的[儲存格大小／自動調整]選項中的「固定欄寬」。

有位老師問到：

如果在 Excel 中每一個月有不同內容的多個工作表，其內容是某些課程的時間、地點和攜帶物品的明細表。如何能在輸入某個月份時，即能列出該月份的各個課程之時間、地點和攜帶物品，以方便提醒每位小朋友呢？

【公式設計與解析】

關於這個問題，因為老師想要將各個月的課程內容放在不同的工作表，所以依老師的想法設計公式。