討論一些較深入的題目,但不涉及巨集(Macro)。 |
張貼者:2010年5月29日 上午6:56Administrator ExcelHelp
[
已更新 2010年5月29日 上午6:58
]
張貼者:2010年5月29日 上午6:53Administrator ExcelHelp
當你要求用戶在一欄裡輸入不重覆的數值時,你如何確保他們輸入的都是獨一無二的數值? 我們可以用驗證。例如我可想確定A欄的值要不重覆: 1)把現存儲存格移到A1(這一步非常重要!),並選擇A欄; 2)選「資料|驗證」;  3)「儲存格內允許」選「自訂」,並確定已勾選「忽略空白」; 4)「公式」輸入 =COUNTIF($A:$A,A1)=1 意思是,要確定在A欄當中,A1數值(現存儲存格)的數目只有一個。注意A1是必須為相對位址,這樣我們便不用逐一定義驗證的條件。$A:$A為絕對位址,因為我們的比較的範圍,雖然在此例中使用相對位址(A:A)也無所謂(因為條件是向下拷的),但還是培養好好習慣,應用絕對位址的時候使用絕對位址; 5)在「錯誤提醒」一頁下,可以這樣輸入:  6)按「確定」; 7)完成! 現在你要是在A欄(我把背景顏色改成黃色,方便理解)輸入重覆的資料,你就會看到錯誤的訊息:  如果你輸入的範圍不是一欄,而是一個範圍,絕對位址就變得非常重要: 
|
|
|
|
|
張貼者:2010年5月29日 上午6:52Administrator ExcelHelp
不久就是聖誕節了,公司都會舉行派對,抽獎是不可缺的。 一般做法都是用抽獎券,不好處就是要對號,麻煩得很。要是另一聯不見了,就會失去拿獎的機會。 為什麼我們不用電腦做呢?電腦直接把名字顯示在畫面上,不是很直接嗎? 不要以為很複雜,只要我們不要求電腦顯示不重複的結果,我們可以不用巨集!做法很簡單,首先當然有一個名單(放在A欄),我們可以用COUNTA()函數算出他們的數目,然後用RAND()就可以產生一個由1到人數數目的數字: =INT(RAND()*COUNTA(A:A))+1 (假設放在C1) RAND()在每次重算時,都會產生一個0到1的數字,乘上人數數目,然後只拿整數的部分(利用INT()函數),加上1,就可以得出一個由1到人數數目的數字。 接下來,我們只要用INDEX()就可以把這個數字轉回姓名: =INDEX(A:A,C1) 現在每次你按<F9>,強迫Excel去重算時,你就會看到一個新的名字(當然有機會是同一個名字)。抽獎時,你可以一直按著<F9>,要停時才放手。 |
|
|
|
|
張貼者:2010年5月29日 上午6:49Administrator ExcelHelp
當你有很多列記錄的時候,會不會覺得很難從左看到右,找到你所需要的資料?一個方法是把儲存格隔列塗色,方便眼睛追踪。 你可以使用format | Autoformat,但缺點是顏色不能控制,而且當你加列後,格式會亂掉。 這裡介紹一個使用Conditional formatting的方法。首先要知道兩個公式: ROW() - 返回所在的列數 MOD(x,y) - 返回 x/y 的餘數 這兩個函數加起來,就可以產生0,1,0,1的數序:
 1)先把有關的列選起來 2)選「format | Conditional formatting」 3)選 formula Is =MOD(ROW(),2)=1 (如果第一列是雙數,則用=MOD(ROW(),2)=0。) 4)選擇適當的格式,例如使用一個較柔和的顏色做背景
 4)就這麼簡單!如果你插入新列,格式會自動拷下來,條件式格式會自動生效:
 |
|
|
|
|
張貼者:2010年5月29日 上午6:46Administrator ExcelHelp
之前已介紹過如何把星期六和星期日突顯出來,這次再多增一個條件,讓我們可以不用Microsoft Project,也可以制作出簡單的棒型圖來表示工作的期間,並把里程碑顯示出來,像這樣:

棒型圖 方法也不複雜(但你要熟悉如何設定相對和絕對位址),條件格式中的條件,除了控制週末兩天的條件外,還多加一個,如果儲存格所代表的一天在該列的開始日期和結束日期中間,那就把儲存格的底色轉為藍色:

注意:需先設定(左上角)一個儲存格的格式,然後拷到其他儲存格去。
里程碑 很簡單,如果儲存格所代表的一天等於里程碑裡的日期,就顯示一個菱型,見第一張圖。 |
|
|
|
|
張貼者:2010年5月29日 上午6:45Administrator ExcelHelp
有時候我們會用Excel來記錄一些事情,一個月一個工作表,每欄就是一天,每行就是一筆記錄,見下圖。如果年份(B1)和月份(B2)都會隨時改變,我們想把星期六和星期日的日子(欄)突出起來,該怎辦呢?

定義範圍名稱 1)首先把B1定義為「Year」、B2定義為「Month」(中文也可以),方便取用;
日子 2)由於每個月都有1日至28日,所以用自動填滿就可以填滿C3:AD3; 3)29日至31日不是每個月都有,故此我就用以下的條件來測試,某一個月是否有29日(例): MONTH(DATE(Year,Month,29)=Month 例如2005年2月是沒有29日的,DATE(2005,2,29)就會變成2005年3月1日,故此條件的左方會變成3,右方當然是2,這就可以知道應不應該顯示29。故此,29日(AE3)的公式是: =IF(MONTH(DATE(Year,Month,29)=Month,29,"") 而30日和31日的公式就分別是: =IF(MONTH(DATE(Year,Month,30)=Month,30,"") 和 =IF(MONTH(DATE(Year,Month,31)=Month,31,"") (只是三個公式,我不會嫌麻煩一個一個把日子改變,但如果很多的話,我就會用COLUMN()函數。) 4)把欄寬按需要調節;
把星期六、日突出 5)選擇需要突出的儲存格(C3:AE22); 6)選「格式|設定格式化條件」,改為「公式為」,輸入公式: =WEEKDAY(DATE(Year,Month,COLUMN()-2),2)>=6 為了方便起見,我沒有用第3行的數字來計算日期,我用COLUMN()。C欄代表1日,故此把COLUMN()-2就可以算出日子。 WEEKDAY()的第二個引數放2,星期六和星期日就分別用6和7代表,那就可以簡單地用>=6去決定是否是星期六或星期天。(沒有引數的話,星期日是1,星期六是7。) 7)按「格式」把合符條件儲存格的格式定義好(背景用米色); 8)按「確定」; 9)完成。 |
|
|
|
|
|