技巧區‎ > ‎

中級程度

討論一些較深入的題目,但不涉及巨集(Macro)。

兩層的下拉式選單 [2005年7月]

張貼者:2010年5月29日 上午6:56Carson Cheng   [ 已更新 2010年5月29日 上午6:58 ]

也許你知道利用「資料|驗證|清單」,可以在儲存格內做到下拉式選單的效果:


但是要是你希望用戶輸入兩個數據,這兩個數據都是在預設的選單裡,但第二個選單有很多個,選哪一個則由第一個數據來決定...... 舉個例子吧!你要用戶輸入一個電腦產品的種類及型號,型號由產品決定,資料庫如下:
電腦產品 主機型號 滑鼠型號  喇叭型號
主機   B0123  M234   S9234
滑鼠   B3434  M355   S8998
喇叭   B2343  M323   S0943
          M236   S0248
                 S9348

如果用戶在第一個下拉式選單選滑鼠,則第二個下拉式選單應給用戶從M234、M355、M323、M236裡選型號,如此類推。

你不知道Excel可以做到這樣的效果?想學嗎??耐心看下去吧!

這裡需要幾個技巧:
● 相對和絕對位址的分別
● 範圍名稱裡可以定義函數的特性(當然你要知道什麼叫範圍名稱)
● CHOOSE()和MATCH()函數

(我好像在介紹小菜的材料那樣。)

步驟如下:

  1. 首先要定義好四個範圍名稱。身為Excel高手的你,又怎可以一個一個那樣子去定義呢?所以我們要把資料表選起來,然後選「插入|名稱|建立」,只勾選「頂端列」,按「確定」。

  2. 你可以在左上方的範圍名稱框內找到四個新定義好的名稱:

  3. 現在可以定義第一層的選單:

    1. 選取需要第一層下拉式選單的儲存格(A2:A11)
    2. 選「資料|驗證|設定|(儲存格內允許)清單」,來源輸入「=電腦產品」,記得一定要在前面加上=號(公式的意思):


  4. 接下來我們要定義一個動態的範圍名稱,其範圍可根據公式所計算出來的值而改變。

    1. 首先選B2(這一步很重要,不然相對位址的設定會錯誤)
    2. 選「插入|名稱|定義」,「現有名稱」輸入「選項」,「參照到」輸入以下的公式:
        =CHOOSE(MATCH(Sheet1!A2,電腦產品,FALSE),主機型號,滑鼠型號,喇叭型號)
        要注意的是A2一定要為相對位址,不能是絕對位址$A$2。公式的意思是,按照左方儲存格的數值,查是「電腦產品」範圍中的第幾列,如果是1,則返回「主機型號」的範圍地址,如此類推。
    3. 按「確定」


  5. 現在要定義第二層下拉式選單了!

    1. 選取需要第二層下拉式選單的儲存格(B2:B11)
    2. 選「資料|驗證|設定|(儲存格內允許)清單」,來源輸入「=選項」
    3. 按「確定」


  6. 最後,當然是要測試囉!在第一層選「滑鼠」,在第二層順利看到滑鼠的型號:

  7. 大功告成!


注意:
● 在第1步定義範圍的方法很方便,但如果每欄的記錄筆數不一樣,選單的末端可能會有空列,而用戶是可以選擇那些空白的項目。
● 除了使用資料驗證,還可以用表單工具列裡的工具:
  
  詳細情況,可參考附檔。


用棒型圖畫進展表 [2005年11月]

張貼者:2010年5月29日 上午6:54Carson Cheng

假設我們有一些專案(A到D),每個專案都需要完成一些工作(開發系統、測試、用戶測試),而每個工作都有開始與結束的日期。


我們如何用圖表來顯示這些數據呢?用Project當然可以,不過不是每個人都有Project;可以考慮用Excel!Excel可以做到嗎?是的,這個圖表正正是用Excel做的,只是要很多步驟.... 不過說透了,也不難:


我們其實可以用堆疊橫條圖來畫,開始日和結束日之間的差距就成為棒條的長度,而工作與工作之間的差距則由透明的棒條來填補。


  1. 第一個日期我們予以保留,其他日期則用簡單的公式轉為與在其之前日期的差距,可參考C11的公式:


    另外要注意的是欄位的名稱(B10:G10):工作與工作的間距暫時用1、2、3填補,Excel便不會混淆。

  2. 選擇A10:G14,再選擇圖表精靈,選堆疊橫條圖:


  3. 在資料範圍下,選「數列資料取自|欄」:


  4. 在圖表名稱上,先隨便打一個圖表名稱:


    圖表出來了:


  5. 由於專案的次序相反了,我們在專案軸(Y軸)上按兩下,選「刻度|類別次序反轉」:


    效果如下:


  6. Excel現在會自動設定一個顯示的日期範圍,以及刻度的間距,要手動設定這些值。在日期軸(X軸)上按兩下,在刻度下的「最小值」、「最大值」上輸入適當的日期(對,你可以直接輸入日期,Excel會自動轉換成日期序號),最小值的日期最好是星期一或星期日;在「主要刻度間距」、「次要刻度間距」上輸入7(一個星期):


    在數字下的數字格式,輸入自訂格式「mm/dd」:


    效果如下:


  7. 現在我們把工作與工作之間的棒隱藏:在其上按兩下,把「框線」、「區域」都設為「無」。


    效果如下:


  8. 如果你不想要背景的灰色,只要在其上按兩下,把區域設為「無」就可:


    效果如下:


  9. 最後,做一些修飾的工作:


    包括:
    * 圖表名稱:按一下圖表名稱,在公式欄上輸入工作表上圖表名稱的位址
    * 圖棒顏色:把顏色設為較奪目且容易分辨的顏色
    * 圖例的框線:把圖例的框線刪掉,那樣便不會有不公稱的效果
    * 欄位名稱:把假的欄位名稱(1、2、3)刪掉

  10. 大功告成:



不知道對大家的工作有沒有什麼幫助,請提意見!

永恆的總和(節錄自《晉身200% Excel高手》[2000年7月]

張貼者:2010年5月29日 上午6:51Carson Cheng

「永恆」似乎有點故弄玄虛,沒辦法!不賣弄半點噱頭,就不能吸引你去看看這個不能再簡單的SUM()函數!


像下圖般用上SUM()函數的報表,相信你每天都會接觸得到。
  SUM()的引數是一個範圍,範圍內的數字都會給加起來。


那你一定試過為了加插新的資料,而在SUM()函數的一列上多加幾行空列(如圖)。
  你會發現引數中的範圍不會因此而改變。


但你不用太擔心,因為只要新加的資料是緊貼著舊有的資料,Excel會很聰明地把SUM()引數中的範圍擴大,把新加的數值也包括在總和裡。
  在這例子裡,範圍原來是B2:B6,現在就變成B2:B7。


問題是,如果用戶不是乖乖地貼著舊資料打,而是隔著一空列來打,你的公式就會有問題。
  這則秘訣就是要針對這個問題。



我認為最好的方法就是利用INDIRECT(),再配合R1C1欄名列號表示法。

INDIRECT()函數
首先解釋一下什麼是INDIRECT(),它可以讓你不用直接寫出一個儲存格的位址而使用一個儲存格的數值,只需把位址放在一個文字字串裡就可以。例如B1的數值是10,INDIRECT("B1")就是10。你可能會覺得這函數很無聊,但這個函數的好處是,無論你把INDIRECT()這個公式抄到哪裡去,它永遠都會顯示B1的數值,因為文字"B1"是不變的。

R1C1欄名列號表示法
總和的範圍就是在同一欄中,由某列(資料的第一列)開始至總和公式以上的一列,用A1方式根本不能簡單地寫出來,但用R1C1方式就很容易,只要寫「R2C:R[-1]C」(假定由資料由第二列開始):
R2  代表第二列,是絕對值,相等於A1表示法中的$2。
R[-1] 代表上一列,是相對值,會自動隨SUM()函數的位置而改變。
C    代表同一欄。

永恆的總和
「永恆的總和」的公式就是:

  =SUM(INDIRECT("R2C:R[-1]C",FALSE))

註:INDIRECT()的第二個引數FALSE,代表不用A1表示法,而使用R1C1表示法。


注意:
1)雖然在INDIRECT()函數裡用上R1C1表示法,但你不必剔選「工具|選項|一般|[R1C1]欄名列號表示法」,換句話說,你可以沿用A1表示法寫公式。
2)這個方法的短處是,「稽核工具列」的追縱功能不能用在此公式上。

把星期六和星期日突出顯示出來 [2003年11月]

張貼者:2010年5月29日 上午6:45Carson Cheng

有時候我們會用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)完成。

1-10 of 16