學不完．教不停．用不盡

在 Excel 的工作表中，如果表格很大，在公式中選取儲存格範圍時將會非常不方便，而且容易出錯。最好是使用「名稱」，又快又準確。參考以下的範例：

1. 先選取資料範圍(儲存格B1:D16)。

2. 按一下 Ctrl+Shift+F3 鍵，開啟[以選取範圍建立名稱]對話框。

3. 勾選［頂端列］選項，按一下[確定]按鈕。

如此便建立好三個名稱：國文、英文、數學，之後可以很方便的選取這些儲存格範圍。

在 Excel 如果是將兩個數拿來計算其比率，將會得到一個計算值。例如：2和10的比率應該是1:5，但是如果是以計算方式將會得到0.2，如果以分數的格式顯示將會得到1/5。要如何才能得到「1:5」呢？

(一)

先由儲存格E2來看，其內容為16/9，來練習轉變為16:9。(參考下圖)

儲存格F2：=LEFT(E2,FIND("/",E2)-1)&":"&RIGHT(E2,LEN(E2)-FIND("/",E2))

公式中的FIND("/",E2)，找出「/」在第幾個字元位置。

公式中的LEFT(E2,FIND("/",E2)-1)，抓取「/」左邊的字元。

在 Excel 的工作表中輸入一些數字，如果想要這些數字顯示為最接近的千位數，該如何處理？可以直接設定數值格式，不影響原儲存格中的數值內容。

例一：[<=500] "0";00,",000"

低於500者以0顯示，高於500者取最接近的千位數(會四捨五入)，並顯示共五位數(不足者前面補0)。

例二：[<=5000] "0";00,",000"

低於5000者以0顯示，高於500者取最接近的千位數(會四捨五入)，並顯示共五位數(不足者前面補0)。

在 Excel 中的一份資料表，如何選取男或女性別後，能以不同色彩格式區隔資料中的男生和女生？

首先透過「資料驗證」方式，將儲存格H2中加入「男,女」。

接著選取所有的資料內容(本例為儲存格A2:F214)，然後在設定格式化的條件中，輸入二個設定值：

在 Excel 的工作表中取得一個跑步記錄表(如下圖左)，利用B欄的資料要來做統計分析(如下圖右)。B欄中的資料格式固定，透過陣列的應用，只要分別以一行公式即可算出各個統計結果。

假設B欄的資料在儲存格B2:B50中，輸入以下的公式：

(以下公式全為陣列公式，輸入完成要按 Ctrl+Shift+Enter。)

(1) 最多圈

儲存格D3：{=MAX(VALUE(MID(B2:B50,3,2)))}

根據前二篇查表方式的練習：

http://isvincent.blogspot.com/2011/04/excel-indexvlookuphlookup.html

http://isvincent.blogspot.com/2011/04/excel-indexmatch.html

這次要使用OFFSET+MATCH來執行查表。

儲存格G14：=OFFSET(A1,MATCH(F14,A2:A11),MATCH(E14,B1:K1),,)

在 Excel 的工作表中，如果想要輸入一個二進制數、八進制數或十六進制數，然後要產生遞增1的數列(參考下)該如何處理呢？

必須要使用進制轉換的函數：DEC2BIN、BIN2DEC、DEC2OCT、OCT2DEC、DEC2HEX、HEX2DEC。

儲存格B3：=DEC2BIN(BIN2DEC($B$2)+ROW(1:1))

因為要進行加1的運算所以得先把二進制轉成十進制，加1後，再轉回二進制顯示。

八進制和十六進制的加1運算也一樣：

儲存格C3：=DEC2OCT(OCT2DEC($C$2)+ROW(1:1))

前一篇文章：Excel-Index+Match練習(http://isvincent.blogspot.com/2011/04/excel-indexmatch.html)，提到了查表的功能。這次來練習使用VLOOKUP和HLOOKUP函數來融入查表。

以下三種公式可以得到相同查表結果：

(1) 儲存格G14：=INDEX(A1:K11,MATCH(F14,A1:A11,0),MATCH(E14,A1:K1,0))

公式為：INDEX(表格，找尋F14在A1:A11中的第幾個，找尋E14在A1:K1中的第幾個)

此方法以欄、列交叉的位置取得查表結果。

當你在 Word 中選取[檔案/資訊]時，在視窗最右側會顯示該檔案的摘要資訊。按一下最下方的「顯示所有屬性」，可以顯示完整的內容。

在這個區塊中，你可以自行輸入內容。(記得要儲存，這些資料才會寫入。)

如果你選取[內容]功能表中的「顯示文件面板」選項，則會在文件上方顯示檔案資料。

這次要來練習利用 Index 和 Match 函數來查表的功能。

(1) 建立基本表

儲存格A2：=CHAR(ROW(1:1)+64)，複製儲存格A2至儲存格A2:A11。

利用COLUMN(1:1)+64來產生65,66,67 …，再代入CHAR( )產生A,B,C …。

儲存格B1：=CHAR(COLUMN(A:A)+80)，複製儲存格B1至儲存格B1:K1。

利用COLUMN(A:A)+80來產生81,82,83…，再代入CHAR( )產生Q,R,S …。