學不完．教不停．用不盡

在 Excel 的工作表中來練習二個數的四則運算(如下圖)，並且套用資料驗證。

首先，我們要將運算符號，以選取清單方式呈現，所以將儲存格C2設定資料驗證：

儲存格允許：清單，來源：＋,－,×,÷,︿(五種運算子：加、減、乘、除、指數)

儲存格C2可以使用選單選取運算符號。

在另一篇文章：Word和OneNote的Microsoft Mathematics 增益集中提到了可以由Microsoft Mathematics增益集來繪製公式的圖形(如下圖)。

http://isvincent.blogspot.com/2011/01/wordonenotemicrosoft-mathematics.html

如果你想要用 Excel 的圖表來繪出這個圖形，該如何處理？而同一個表格可否根據不同選項，顯示不同的圖形呢？

假設基本資料的儲存格範圍如下圖所示：

在 Excel 中提供了多種方便使用的日期函數，如果要列出某年的週六、週日之日期(如下圖)，該如何設計？

首先在儲存格B1中以資料驗證方式，指定儲存格為「清單」，內容為「2010,2011,2012,2013」。

接著設定一個名稱：DATA

DATA：=INDIRECT("A"&DATE($B$1,1,1)&":A"&DATE($B$1,12,31))

公式中的："A"&DATE($B$1,1,1)&":A"&DATE($B$1,12,31)

在 Excel 中儲存格範圍是一個重要的概念，在運算時常用到「：,，,空格」等三個符號。

這次來做個有趣的練習－利用設定格式化條件改變儲存格底色，並且利用表單控制項來設定。

首先插入七個控制項表單－選項按鈕，並輸入如圖所示的名稱。

.

在控制項格式中指定儲存格連結為儲存格A1。

選取儲存格A2:S20，進行七種條件設定：

根據一個日期，試著來練習幾種日期的運算(參考下圖)。

(1) 計算月份

儲存格B2：=MONTH(B1)

(2) 本月天數

儲存格B3：=DAY(DATE(YEAR(B1),MONTH(B1)+1,0))

在 Excel 中取得一個每日資料記錄表，資料已有2008年、2009年、2010年、2011年(如下圖左)，現在希望利用這些資料，分年度計算各月中所有同一日的平均(如下圖中)及每週同一星期幾的平均(如下圖右)。

(1) 設計年度選取之下拉式清單

為了方便使用，所以在儲存格G1中設定資料驗證，其儲存格允許設定為清單，而資料來源輸入：「2008,2009,2010,2011」。

(2) 設定資料的「名稱」

在 Excel 中提供定義名稱方式，可以簡化公式的長度，讓公式變的更具可讀性。以下要以「黃金比例」為例，來練習「名稱」的使用。

如果把一條線段分成長短兩段，若「全段長：長段長＝長段長：短段長」的話，這種分割方式稱為「黃金分割」，而分割出來的兩線段長的比，稱為「黃金比例」。

若把長段長設為X ,短段長設為Y，依據黃金分割的定義，(X+Y)：X＝X：Y。若將Y當為1，則經過運算後，X 約為 1.618；若將X當為1，則Y約為 0.618。而1.618…和0.618…兩數互為倒數。

(1) 定數的名稱

定義名稱golden：=0.618

在 Microsoft Office 的文件中繪圖，如果需要連續繪製幾個相同圖案，如何做比較快？

你可以：

1.選取[插入/圖案]指令。

2.在想要使用的圖案上按一下右鍵。

3. 選取「鎖定繪圖模式」選項。

在 Excel 的工作表中有一欄的英、數字項目，如何找出最後一個數字呢？

儲存格C2：=INDEX(A2:A16,MATCH(9.99999999999999E+307,A2:A16,1))

MATCH(9.99999999999999E+307,A2:A16,1) = 12

MATCH函數中的參數1(也可以省略)，會找尋等於或最接近要搜尋的的值，而且要搜尋的範圍必須先排序好。因為9.99999999999999E+307是Excel能接受的最大值，而A欄也沒有排序過，所以會傳回最後一個數字。

再透過 =INDEX(A2:A16,12)，找到儲存格中的內容。

如果在 Excel 中的資料表會不斷的輸入資料，而只想要計算最近幾天的數據該如何處理呢？請標示資料取用了那些列的資料。

因為資料會不斷的變動所以位址無法以固定位址來計算，所以得藉助OFFSET和COUNTA函數來運算。

儲存格E2：=MAX(OFFSET($A$2,COUNTA($A:$A)-1-$E$1,1,$E$1,1))

COUNTA($A:$A)-1-$E$1為計算A欄中有資料的儲存格有幾格，然後減掉1(第一列)，再減掉儲存格E1。這就是要取用資料的第一列，而儲存格範圍的高度是儲存格E1的內容。

同理：

在 Excel 中輸入多個班級(C301~C308)的成績表，其對應的科目都相同，每個科目最下方有該科目的平均值(如下圖)，如何將這個8個資料表的平均成績整合在一個表(下下圖)中呢？

假設各班各科的平均置於第22列。

儲存格B2：=INDIRECT($A2&"!"&CHAR(65+COLUMN(A:A))&"22")

COLUMN(A:A)=1，往右複製變為COLUMN(B:B)=2，…

CHAR(65+COLUMN(A:A))，往右複製可以得到A,B,C,D,E,…。

在 Excel 中有一個資料表是由星期一至星期五組成的A,B,C,D五組數據資料，如何根據星期幾統計超過某值的個數有多少？資料會不斷的增加，而量測的某值要能變動。

儲存格B2：=A2，並設定數值格式顯示「星期X」。

由資料會不斷的增加，所以無法取得實際的位址，因此要以定義名稱的方式來取得不固定的位址。

DATA：=OFFSET(工作表1!$A$2,,,COUNTA(工作表1!$B:$B)-1,)

利用COUNTA取得B欄中有幾筆資料，COUNTA(工作表1!$B:$B)-1是因為第一列不是資料。

同理，設定以下四個名稱。

在 Excel 中取得一個資料表，其依日期填入數量(如下圖左)。現在，希望建立一個摘要表，其中只要最近12個月的小計，該如何處理？

(1) 取得年和月的數列

儲存格E2：=YEAR(EDATE(TODAY(),ROW()-13))

因為第13列要放本月的資料，所以透過EDATE函數，以ROW()-13求取之前的月份，再取出年的部分。

儲存格F2：=MONTH(EDATE(TODAY(),ROW()-13))

同上原理，取出月的部分。

在 Excel 的工作表中有一個資料表(如下圖上)，希望利用下拉式選單來挑選姓名，並依不同人員顯示不同的統計圖表內容(如下圖下)，並且將被選取人員的資料以較深的底色標示，該如何處理呢？

1. 選取儲存格B14，設定資料驗證為「資料驗證準則：清單，來源：=$A$2:$A$12」。

2. 選取儲存格A1:G2，製作一個直條圖的統計圖表。

3. 選取儲存格B2:G12，設定格式化規則為「=$A2=$B$14」，將底色改為較深的綠色和文字色彩為紅色。

在 Excel 中有一個資料(如下圖左)，要由這個資料表中查詢對應的數值，並標示其位址，該如何處理？這次要在LOOKUP函數中使用兩個件來進行查詢。

首先定義三個資料範圍：DA：儲存格A3:A26，DB：儲存格B3:B26，DC：儲存格C3:C26。

(1) 查詢資料

儲存格G3：=IFERROR(LOOKUP(1,1/((A3:A26=E3)*(B3:B26=F3)),C3:C26),"X")

LOOKUP(1,1/((A3:A26=E3)*(B3:B26=F3)),C3:C26)

=LOOKUP(1,1/{True,False,True,False,False,…}*{Fasle,False,True,True,False,…},{1,2,3,4,5,…})

在 Excel 中取得一個資料表(如下圖)，其中已經按生日欄位排序，如何讓 Excel 依間隔5歲自動畫出分隔線？即以31-35,36-40,41-45,…為間隔。

只要藉助設定格式化的條件規則，即可自動完成。

(1) 選取儲存格A3:E25。注意：不選取儲存格A2:E2。

(2) 設定條件格式化規則為：使用公式來決定要格式化哪些儲存格，並設定公式為「=INT(($D3-1)/5)<>INT(($D2-1)/5)」。

因為要以1-5為一組，6-10為一組，所以INT(($D3-1)/5)，可以取得(年齡+1)除以5的商。用來判斷自已和上一個儲存格是否不相同。

在 Excel 中有一個單位轉換的函數：CONVERT，可以將多種類型的度量單位做轉換，例如：

=CONVERT(100,"F","C")，可以將100度的華氏溫度轉換為攝氏溫度。

參考微軟網站：http://office.microsoft.com/zh-tw/excel-help/HP010342306.aspx

在 Excel 的儲存格中輸入了多個字元的字串，如何來計算這些字元的數量呢？(參考下圖)

儲存格C2：=LEN($A2)-LEN(SUBSTITUTE($A2,C$1,""))

原理是：將儲存格C2的字串總長度(字元數)，減掉去除某個字元的字串總長度(字元數)。

複製儲存格C2至儲存格C2:L12。

當你有了以上的每個字元的數量，如果要計算總和(參考13列)是很容的事。如果你不想透過先將每一列中每個字元的數量算出來，直接想得到總和，該如何做呢？

有人問到 Excel 2010 的最大欄數是XFD，這個數字代表第幾欄呢？我們可以把Excel的欄名順序看成是一種26進制的表示法，A, ~ , Z, AA ~ AZ, BA ~ ZZ, AAA ~ XFD。

以3個位元字母的第XFD欄(最後一欄)為例：

XFD1：=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),ROW(),"") 

=SUBSTITUTE(ADDRESS(1,16384,4),ROW(),"") [取得列數和欄數]

=SUBSTITUTE("XFD1",ROW(),"")  [將列數和欄數轉成欄名列號字串]