學不完．教不停．用不盡

在 Excel 中如果要在多列中產生序號(1,2,3,…)，可以透過ROW函數。

(1) =ROW(A1)

(2) =ROW(1:1)

(3) =ROW()

不管那一列中輸入(1)和(2)這二個公式，當往下複製時，都會自動產生1,2,3, … 的數列。如果改變參數A1為B1,C1,…，或是改變參數1:1為2:2,3:3,…，都可以改變數列的起始數值。而ROW()則以儲存格的位址當為參數，若位於第6列，則會傳回6。

參考下圖，如果你將某一列(例如：第10列)刪除，其數列結果不會受到影響。

在 Excel 中有一個工讀費的資料表，如何計算工讀費所需鈔票及硬幣？這次要運用INT和MOD函數來運算。

儲存格C3：=INT(B3/C$2)

INT函數可以求取指定數字的不大於之最大整數，所以INT(B3/1000)，相當於求取被1000除盡之整數。

儲存格D3：=INT(MOD($B3,C$2)/D$2)

在求100元鈔時，MOD($B3,1000)會計算薪資除以1000所得的餘數，即求得千元鈔以外所需的錢數。再透過INT函數求得所需的100元鈔數量。

複製儲存格D3至儲存格D3:G3，再複製儲存格C3:G3，貼至儲存格C3:G28。

在 Excel 中，如果在一個儲存格中填入一個公式，在複製公式時，Excel 會自動依欄或列的變化，來變換公式中的位址。以實例來練習，將下圖中的左邊的表格轉換成右邊的表格：

撰寫公式時，要先考量好複製公式時所產生的位址變化，例如：

儲存格G3：=IF($B2=G$1,$C2,"")

因為要複製儲存格，所以第一列和B欄和C欄的位址不能改變，因此加上「$」。(使用F4鍵，可以切換四種位址變化，例如：B2、$B$2、$B2、B$2)

複製儲存格G3至儲存格G3:I3，再複製儲存格G3:I3至儲存格G3:I28，便可完成表格的轉換。

善用相對位址和絶對位址的變化，可以一個公式，即可完成一個表格。

在 Excel 提供了排序的函數，例如：RANK，這次來練習不一樣的排序方式。

先定義各種資料名稱：姓名、分數、輔助分別為A欄、B欄、G欄有資料的部分。

(1) RANK排序

儲存格C3：=RANK(B3,分數)

(2) COUNTIF排序

在 Excel 中取得一些姓名的資料，如果想重組這些姓名，例如將姓和名任意組合成新名字，該如何處理呢？

首先，要建立二個輔助欄位，產生一些亂數值：

儲存格B2：=RAND()

儲存格C2：=RAND()

複製儲存格B2:C2到儲存格B2:C27，產生的這些亂數值幾乎不會重覆。

透過這些亂數的排序結果，可以將A欄的姓名打散。

網友問到：「計算年齡時，如果是42Y5M6D則轉換為42Y5M，如果D>=15時，例如42Y5M15D則轉換為42Y6M，不知如何使用excel做這件事情?」

請先參考「Excel-計算實際年齡(年月日)-DATEDIF」的做法。

網址：http://isvincent.blogspot.com/2011/05/excel-datedif.html

儲存格B3：=DATEDIF(A3,TODAY(),"Y")

儲存格C3：=IF(DATEDIF(A3,TODAY(),"MD")>=15,DATEDIF(A3,TODAY(),"YM")+1,DATEDIF(A3,TODAY(),"YM"))

DATEDIF(A3,TODAY(),"MD")>=15：判斷年齡中的「日數」，如果大於15，則月數加1，否則保持原來的月數。

在 Excel 中取得一個部門、產品、業績的資料表，來試著練習資料庫函數中的DSUM函數。

先定義名稱，資料：儲存格A1:D28，再定義姓名、部門、

(1) 計算部門：業務二科的業績小計

使用SUMPRODUCT函數，儲存格G2：=SUMPRODUCT(--(部門=F2),業績)

使用陣列公式，儲存格G2：{=SUM(IF(部門=F2,業績,FALSE))}

輸入完成，請按 Ctrl+Shift+Enter 鍵。

在 Excel 中指定一個日期，要來找出最近的一個星期日，該如何處理？試試WEEKDAY函數。

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

WEEKDAY(A2,2)：根據儲存格A2，參數2，表示星期一傳回1、星期二傳回2、…、星期日傳回7。

7-WEEKDAY(A2,2)：得到和下個星期日的差距天數。

A2+7-WEEKDAY(A2,2)：將指定日期加上差距天數。

複製儲存格C2，往下各個儲存格貼上。

如果知道某人生日，想要知道其已出生了幾年幾月幾日，該如何處計算？如果想要計算距下次生日還有幾天，該如何計算？試試DATADIF函數！

儲存格B3：=DATEDIF(A3,TODAY(),"Y")

儲存格C3：=DATEDIF(A3,TODAY(),"YM")

儲存格D3：=DATEDIF(A3,TODAY(),"MD")

儲存格E3：=IF(DATE(YEAR(TODAY()),MONTH(A3),DAY(A3))>=TODAY(),DATE(YEAR(TODAY()),MONTH(A3),DAY(A3))-TODAY(),DATE(YEAR(TODAY())+1,MONTH(A3),DAY(A3))-TODAY())

其意義是判斷今年的生日是否已超過，如果未超過，則以今年生日減今天日期，如果已經超過，則以明年生日減今天日期。

在 Excel 中有一個依月份列出的收支金額表，根據這個資料表，要來計算收、支及收支相抵的金額各為多少，該如何處理？

以下均為陣列公式，輸入完成請按 Ctrl+Shift+Enter 鍵。

儲存格F2：{=SUM(IF(MOD(ROW(金額),2)=0,金額,FALSE))}

利用MOD函數取得列數除以2的餘數，如果為0，則為偶數列，即為「收」的部分。

儲存格F3：{=SUM(IF(MOD(ROW(金額),2)=1,金額,FALSE))}

利用MOD函數取得列數除以2的餘數，如果為1，則為奇數列，即為「支」的部分。