同一儲存格內含有文字及函數

如果要在同一儲存格內含有文字及函數, 例如 MTD (6/28) , 其中的6/28為當天的日期, 可以用以方式表示
="MTD("&TEXT(today(),"mm/dd")&")"

"文字": ""內可放入文字
&: 連接字串
(TEXT(value,format_text):  Value    可以是數值、一個會傳回數值的或者是一個參照到含有數值資料的儲存格位址。

用Word來列印大量標籤

想要用標籤貼紙來大量製作地址條，可以用Word及Exel快速的完成，只要資料庫完整，不到10分鐘便可搞定。

1. 首先把地址及相關資訊，以Excel整理好，一列一筆資料，例如：
2. 開啟Word ，新增一份文件，並打開「檢視／工具列／合併列印」。
3. 在出現的功能列上，點選「主文件設定」，在出現的對話框中，勾選「標籤」。
4. 按「確定」後，在出現的標籤選項對話框中，選擇標籤的格式，通常我們會選擇「其他／自訂」，依購買的標籤貼紙大小，做出符合我們貼紙的標籤設定。自訂的標籤格式，可儲存下來，下一次只要選它便可立即使用。
5. 設定完標籤格式，會回到文件的畫面，此時在功能列上點選「開啟資料來源」，載入剛才準備好的Excel檔。
   
6. 點選功能表上的「插入合併欄位」，並點選想要加入標籤中的資料欄位。
7. 此時也可在Word文件中，設定資料字體的大小．．等格式
8. 按下「檢視合併資料」，可再重複修正資料的顯示格式
9. 點選「散佈標籤」，此時，便會讀入Excel的所有資料
10. 最後再點選「合併列印到印表機」，便大功告成了!

條件式與萬用字元的運用

不論是countif或是If函數, 很多時候都會運用到條件式來判斷, 例如

• > 大於
  
• < 小於
• >= 大於等於
  
• <> 不等於
• .....

若是運用在Countif 函數上, 還可以這麼運用
例如:

Countif (B2: B5, "<>"&2)
會計算範圍內不等於2的儲存格個數

Countif (B2: B5, "<>"&B3)
會計算範圍內不等於B3儲存格的儲存格個數


Countif (B2: B5, "<>")
會計算範圍內非空格的儲存格個數, 相當於 Counta函數

Countif (B2: B5, "*ann")
會計算範圍內結尾為ann的儲存格個數

Countif (B2: B5, "???ant")
會計算範圍內字數為6個字元且結尾為ant的儲存格個數

Countif (B2: B5, "*")
會計算範圍內含有文字的儲存格個數

Countif (B2: B5, "<>"&"*")
會計算範圍內不包含任何文字的儲存格個數

二階層的清單做法

使用清單的好處, 在於若儲存格的內容是固定從某一list中選擇時, 將儲存列設定好清單,便可以直接用選的, 而不必一一去想該填什麼內容。有時候, 清單會不只一層, 也就是清單之下還有一層清單, 在Excel中, 也可以設定二階層的清單。做法如下:
A欄為第一層的清單, B-C....為第二層的子清單。清單的內容可以單獨放在獨立的工作表。

1. 選取A2:A7, 使用「插入>名稱>定義」將A2:A7定義名稱為「Level1」
2. 選取A2:C2, 利用「插入>名稱>建立>勾選最左欄」, 將B2:C2的名稱設定為A2的值。
3. 重複步驟2, 設定好每一個子清單的名稱。
4. 移至資料工作表, 選取要填入第一層清單的儲存格(假設為A1), 再執行「資料>驗證>儲存格內允許選清單>來源輸入=Level1>」。
5. 選取第二層子清單儲存格(假設為B1), 執行「資料>驗證>儲存格內允許選清單>來源輸入=indirect($A1)」

設定完畢後, 點選A1儲存格, 便會出現清單供你選擇, 點B1後, 出現的選單內容, 便會依據A1的內容而顯現不同的清單。

水平轉直欄的方法

使用Transpose(Array) 函數

這個函數可以將指定陣列的資料反轉, 所以不管是水平欄轉直欄或是直欄轉水平欄, 都適用。

例如，將B1:G2的水平矩陣（2Rx6C）要反轉為垂直矩陣（6Rx2C）

Step 1：選取預計反轉後的範圍（即6Rx2C的區間）

Step 2：輸入Transpose（B1:G2）

Step 3：按下Ctrl+Shift+Enter即可

Ctrl+Shift+Enter表示可以套用函數到所選的全部範圍內，或是直接輸入{=Transpose（B1:G2）}再按Enter也是一樣。

由於此函數屬於參照類型的函數, 所以若原始資料打算移除, 記得要把轉換後的新陳列, 以選擇性貼上的方式, 貼上 "值", 以免原始資料刪除後, 白做工一場。

Excel--設定格式化的條件

Excel的「格式」選單下有一個「設定格式化的條件」的指令，可以依數值或公式來自動判定某些儲存格的格式，還頗有用處的，應用起來也是千變萬化
例如，若將儲存格設定如下的條件：


只要是輸入我所設的關鍵字，便會自動改變該儲存格的樣式，十分方便。所得結果如下圖：


這種以顏色來管理進度的方式，對我還很管用，一目了然。若刪除掉內容，格式又回復到乾淨的原始設定。
這個功能還能配合公式，配合公式，確實能做出千變萬化的設定，例如：

則會把最右邊含P字母的儲存格底色變成黃色。
選擇依公式來判別時，記得以　”＝”開頭，再加入判別式，像是＝，＞，＜之類的。
這個指令目前對我而言唯一的缺點是，條件的判斷式不能像sumif一樣，test某一列的儲存格，但變動的是其相對應的另一列儲存格。残念．．．

參照公式的救星 IS函數

使用Vlookup之類的參照函數，有時對應不到項目式時，往往會出現醜醜的 #N/A，還要手動一一消除，如果希望參照不到的項目，就不要出現任何文字，或是標示出「無此項目 」，此時便可以運用IS函數。

用以測試數值或參照類型函數共有九種，統稱為 IS 函數。這類函數會檢查數值的類型，並且根據結果傳回 TRUE 或 FALSE。例如，如果數值引數參照到一個空白儲存格時，ISBLANK 函數會傳回邏輯值 TRUE，否則便傳回 FALSE。

根據要檢測的類別，選用要用的是什麼IS函數：

• ISBLANK (value)：測是否為空白
• ISERR (value)：測是否為 #N/A 之外的任何一種錯誤值。
• ISERROR (value) 測是否為任何一種錯誤值 (#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!)。
• ISLOGICAL (value)：測是否為邏輯值
• ISNA (value)：測是否為錯誤值 #N/A (無法使用的數值)。
• ISNONTEXT (value)：測是否為任何非文字的項目。(請注意：如果數值參照到空白儲存格，則此函數也會傳回 TRUE)。
• ISNUMBER (value)：測是否為數字
• ISREF (value)：測是否為參照
• ISTEXT (value)：測是否為文字

對我來說，便會常用像以下的公式：

IF(ISNA(VLOOKUP(A7,推薦書目!$A$2:$H$600,4,0))=FALSE, "EDU", "")



隨機數的取用

我最喜歡用隨機值來從一大堆不想做的事項中pick out出一種來做，所以Randon函數便是首選，更自動化一點，可以這麼做

ROUND(COUNTA($B$2:$B$9)*RAND(),0)

不過，好像還是會出現0，不過暫時夠用了！

日期的比較

終於摸索到如何利用If函數來比較日期的大小，先前直接利用 yyyy/mm/dd的格式，完全不買帳，今天靈機一動，設了以下IF函數，結果，可行耶～以後再來細細研究日期的用法

IF(I2>DATE(2006,1,1), IF( I2>DATE(2006,10,1), "近期新書", "2006出版"), " ")

更進階的用法，還可以搭配Today()函數使用。

IF(I2>DATE(2006,1,1), IF( (Today()-I2)<90, "近期新書", "2006出版"), " ")

Countif & Sumif 函數

常常需要統計一堆資料中，某種條件下的項目有幾個 , 或是其加總是多少，這個時候，這兩個函數就很好用了，兩者的用法類似，但其中最讓我遇到障礙的就是條件式的設計，後來無意間總算試出來了，條件式若是要大於或小於之類的，必須在條件外加上雙引號，否則函數就不合法，例如：

COUNTIF(C2:C335, ">2006/1/1")　正確
COUNTIF(C2:C335, >2006/1/1) 　 錯誤

解決了這種明明很簡單，卻無法使用的函數後，心裡真是爽快啊～

另外再補充 sumif 函數的進階用法，它可以判斷某一區間的數值是否符合條件，再將另一區間的數值加總，例如：
SUMIF($I$2:$I$21,$C24,H$2:H$21)
第一個區間指的是判斷的區間，第二個是條件值，第三個區間則是指實際執行加總的區間，例用這個函數，便能輕易計算某位業務的業績加總。

Vlookup函數對不到Item時該怎麼辦?

Vlookup這個函數的使用率太頻繁了，但三不五時，還是會遇到明明參照表有某個項目，但顯示出來的數值卻是可恨的#N/A ，此時，多半是參照表或檢索的儲存格中含有隱藏的符號，以肉眼看明明一模一樣，但硬是對應不到，解決方法很簡單：

1. Copy要參照的儲存格到「記事本」上
2. 將多餘的隱形字元，用find & replace功能全數去除
3. 將結果貼回Excel

大功告成!!　「記事本」我愛你！！