在微軟的技術支援中心有提到一些Excel 中不為人所知的秘訣,摘要一些出來。(純為教學使用,無意侵權。)

資料來源:

http://support.microsoft.com/kb/843504/zh-tw?spid=2512&sid=1293

聯結多欄文字

您可以使用 & 運算子或 CONCATENATE 函數來連接或合併多欄文字;例如,假設您在儲存格 A1:C2 中輸入下列資料:

摺疊此表格展開此表格

A1:名字
B1:中間名
C1:姓氏

A2:Tom
B2:Edward
C2:Smith

如果要在儲存格 D2 中放置全名,請輸入下列其中一種公式:

$D$2:=CONCATENATE(A2," ",B2," ",C2)

$D$2:=A1&" "&B2&" "&C2

注意 儲存格之間的空格 (" ") 用於在顯示的文字之間插入空格。

 

剔除清單中重複的項目

如果您所建立的項目清單包含重複的項目,而您希望取得不含重複記錄的清單,這時請用 Excel 的 [進階篩選] 指令。
如果要執行這項操作,請依照下列步驟執行:

  1. 在新活頁簿的儲存格 A1:A10 中輸入下列資料:

    摺疊此表格展開此表格

    A1:水果

    A2:蘋果

    A3:櫻桃

    A4:梨子

    A5:櫻桃

    A6:李子

    A7:蘋果

    A8:蘋果

    A9:梨子

    A10:蘋果

  2. [資料] 功能表上,指向 [篩選],然後按一下 [進階篩選]
  3. [執行] 下方,按一下 [複製到]
  4. [資料範圍] 方塊中,輸入 $A$1:$A$10。
  5. 按一下 [不選重複的記錄],然後在 [複製到] 方塊中輸入 $B$1,再按一下 [確定]
    下列不含重複記錄的清單便會出現於欄 B:

    摺疊此表格展開此表格

    B1:水果

    B2:蘋果

    B3:櫻桃

    B4:梨子

    B5:李子

請注意,這種方法也適用於多欄。您可以使用 [進階篩選] 指令將列隱藏起來。

 

將文字值乘以 1 使文字變成數字

有時候當您從其他來源匯入檔案時,數值資料可能看似數字但實際上卻是文字值。如果要解決這個問題,請將這些值轉換成數字。其中一種可行的方法是將這些文字值乘以 1。
如果要轉換文字值,請依照下列步驟執行:

  1. 按一下工作表中的空白儲存格,確定該儲存格並非文字格式,然後在該儲存格中輸入 1。
  2. 在已選取該儲存格的情況下,從 [編輯] 功能表按一下 [複製]
  3. 選取您要將其值轉換成數字的儲存格範圍。
  4. [編輯] 功能表上,按一下 [選擇性貼上]
  5. [運算] 下方,按一下 [乘],然後按一下 [確定]

此方法可將文字轉換成數字。您可以檢查數字的對齊情形,看看是否已經順利轉換文字值。如果您使用「一般」格式,而且數值靠右對齊,那麼這些值就是數字;文字值會靠左對齊。

 

使用「匯入字串精靈」將文字變成數字

如果要執行這項操作,請依照下列步驟執行:

  1. 選取您要將其值轉換成數字的儲存格範圍。
  2. [資料] 功能表上,按一下 [資料剖析]
  3. 按兩次 [下一步] 跳到精靈的步驟 3。
  4. [欄位的資料格式] 群組方塊中,按一下 [一般],然後按一下 [完成]

此方法可將文字轉換成數字。您可以檢查數字的對齊情形,看看是否已經順利轉換文字值。如果您使用「一般」格式,而且數值靠右對齊,那麼這些值就是數字;文字值會靠左對齊。

 

輸入目前的日期或時間

如果要迅速地在儲存格中輸入目前的日期,請按 CTRL+; 再按 ENTER 鍵。如果要迅速地在儲存格中輸入目前時間,請按 CTRL+: 再按 ENTER 鍵。

 

檢視公式裡的引數

當儲存格包含公式時,您可以按 CTRL+SHIFT+A 查看公式裡的引數。如果您輸入 =RATE 再按 CTRL+SHIFT+A,就能查看 RATE 函數的所有引數,例如 =RATE(nper,pmt,pv,fv,type,guess)。如果您想查看更多細節,請輸入 =RATE 再按 CTRL+A 以顯示「函數引數」精靈。

 

在儲存格範圍內輸入相同的文字或公式

如果要迅速地在儲存格範圍內輸入相同的文字或公式,請依照下列步驟執行:

  1. 選取您要填入資料的儲存格範圍。
  2. 輸入文字或公式,但是不要按 ENTER 鍵。請改按 CTRL+ENTER 鍵。

資料便會出現在您所選取的範圍內。

 

連結文字方塊到儲存格中的資料

如果要執行這項操作,請依照下列步驟執行:

  1. [繪圖] 工具列上,按一下 [文字方塊],再按一下工作表並拖曳滑鼠指標以建立文字方塊。
  2. 如果要用資料編輯列修改資料,請按一下資料編輯列或按 F2 鍵。
  3. 輸入連結公式 (例如,輸入 =A1),然後按 ENTER 鍵。

您在連結的儲存格中輸入的文字便會出現於文字方塊內,譬如 A1。您可以視需要將文字方塊移到任何工作表或活頁簿。

 

連結圖片到儲存格範圍

您可以複製儲存格範圍,再將其圖片結果貼到工作表中。如果這麼做,您就可以很容易地查看工作表中任何一處的儲存格內容。您可以運用此方法,在單一頁面上列印不相鄰的儲存格。圖片將與儲存格範圍相連結,而且會隨著範圍的內容及格式有所變更而進行更新。如果要建立連結圖片,請依照下列步驟執行:

  1. 選取儲存格範圍。
  2. [編輯] 功能表上,按一下 [複製]
  3. 選取您要呈現圖片的儲存格。
  4. 按住 SHIFT 鍵,然後在 [編輯] 功能表上,按一下 [貼上圖片連結]

產生的結果是來源儲存格的快照,因此會隨著其內容或格式有所變更而進行更新。

 

將既有的儲存格內容填入一欄空白儲存格

假設您在欄 A 中輸入下列名稱:

摺疊此圖像展開此圖像

Public/EN-US/Office/Excel/843504B.gif

如果您希望這些名稱能正確排序,請將名稱填入空白儲存格。如果要執行這項操作,請依照下列步驟執行:

  1. 選取儲存格 A1:A10。
  2. [編輯] 功能表上,按一下 [到]
  3. 按一下 [特殊],再按一下 [空格],然後按一下 [確定]
  4. 輸入 =a1,然後按 CTRL+ENTER。
    這個步驟會在您所選取的空白儲存格中填入名稱。
  5. 選取儲存格 A1:A10。
  6. [編輯] 功能表上,按一下 [複製]
  7. [編輯] 功能表上,按一下 [選擇性貼上]
  8. [貼上] 群組下方,按一下 [值],再按一下 [確定]

名稱便會自動往下填滿各儲存格。

 

使用 OFFSET 函數修改插入的儲存格中的資料

假設儲存格 A1:A7 包含下列資料,而您希望在此範圍內使用最後一列減去第一列:

摺疊此表格展開此表格

A1:1

A2:2

A3:3

A4:4

A5:5

A6:

A7:=A5-A1

假設您始終都要在最後一個儲存格底下相隔兩列的位置使用公式,且該公式和含有資料的最後一個儲存格之間有一個空白儲存格。假定您在空白儲存格處新插入一列 (以下範例中的列 6);您希望建立公式用儲存格 A6 的資料減去儲存格 A1 的資料,而不是用儲存格 A5 的資料來減。
請注意此範例中,如果您在 A6 處插入含有資料的新列,公式 =A5-A1 就不會使用列 A6 的資料執行減法。
如果要執行這項操作,請使用 OFFSET 函數。OFFSET 函數會傳回範圍的參照,該範圍與某儲存格或儲存格範圍相距指定的列數和欄數。本範例應該使用下列公式:

=OFFSET(A6,-1,0)-A1

OFFSET 公式不會固定在 A6 的上一列,而是當您插入新列後就會隨之變更位置。

 

使用條件式加總合計資料

假設您在儲存格 A1:A10 中建立資料清單,而且希望加總所有大於 50 小於 200 的值。如果要執行這項操作,請使用下列陣列公式:

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,A1:A10,0),0))

注意 請按 CTRL+SHIFT+ENTER 以確保輸入的公式是陣列。當您按此組合鍵之後,就會看到公式位於大括號 {} 內。切勿嘗試手動輸入大括號。
此公式對範圍內的每個儲存格使用巢狀 IF 函數,並且只在兩項測試條件都符合時才將儲存格值納入加法計算。

 

使用條件式加總計算資料項目個數

假設您在儲存格 A1:A10 中建立資料清單,而且希望計算所有大於 50 小於 200 的數值資料項目個數。如果要執行這項操作,請使用下列公式:

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,1,0),0))

注意 請按 CTRL+SHIFT+ENTER 以確保輸入的公式是陣列。當您按此組合鍵之後,就會看到公式位於大括號 {} 內。切勿嘗試手動輸入大括號。
此公式對範圍內的每個儲存格使用巢狀 IF 函數,並且只在兩項測試條件都符合時才將合計個數累加 1。

 

使用 INDEX 函數和 MATCH 函數查詢資料

假設您在儲存格 A1:C5 中建立下列資訊表格,且該表格的儲存格 C1:C5 包含年齡資訊:

摺疊此圖像展開此圖像

Public/EN-US/Office/Excel/843504C.gif

假設您想使用人名來查詢年齡。如果要執行這項操作,請使用 INDEX 函數和 MATCH 函數的組合,如以下範例公式所示:

=INDEX($A$1:$C$5, MATCH("Mary",$A$1:$A$5,),3)

此範例公式使用儲存格 A1:C5 當做表格,並在第三欄查詢 Mary 的年齡。公式將傳回 22。

 

對未排序的資料使用 VLOOKUP 函數

在 Excel 97 for Windows 及後續版本中,您可以對未排序的資料使用 VLOOKUP 函數。但是,您必須為此公式額外增加一個引數。如果您並未指定 Range_Lookup 引數的值,其值則預設為 TRUE。請注意,Range_Lookup 引數是第四個引數。這樣做是為了使函數與舊版的 Excel 相容。
如果要讓 VLOOKUP 函數能正確處理未排序的資料,請將 Range_Lookup 引數改為 FALSE。下列範例函數將在稍早<使用 INDEX 函數和 MATCH 函數查詢資料>一節所建立的資料表格中查詢 Stan 的年齡:

=VLOOKUP("Stan",$A$2:$C$5,3,FALSE)

 

每逢第三個數字便將其傳回

假設您在儲存格 A1:A12 中建立下列資料表格,而且希望在某欄中每逢第三個數字便將該數字取回,然後放入相鄰的欄:

Public/EN-US/Office/Excel/843504D.gif

如果要執行這項操作,請使用 ROW 函數搭配 OFFSET 函數,如以下範例函數所示:

=OFFSET($A$1,ROW()*3-1,0)

此公式取決於其輸入的儲存格位置所在的列。在此公式中,ROW 函數會將輸入該公式的儲存格位置所在的列號傳回。傳回的數字再乘以 3。OFFSET 函數則將作用中儲存格從儲存格 A1 往下移動指定的列數,每逢第三個數字便將其傳回。

 

四捨五入到最接近的貨幣值

假設您在工作表的儲存格 A1:A3 中輸入下列公式:

摺疊此表格展開此表格

A1:=1.23/2

A2:=1.21/2

A3:=SUM(A1:A2)

假定您要處理金額,且計算結果為貨幣格式。傳回值如下所示:

摺疊此表格展開此表格

A1:$0.62

A2:$0.61

A3:$1.22

如您所見,儲存格 A3 中的總計並不正確。其問題在於,儘管數字格式 (貨幣) 已將顯示的值四捨五入,基礎值仍然未能捨入到最接近的貨幣值。您可以使用 ROUND 函數來解決這個問題。例如,請將公式改成這樣:

摺疊此表格展開此表格

A1:=ROUND(1.23/2,2)

A2:=ROUND(1.21/2,2)

A3:=ROUND(SUM(A1:A2),2)

ROUND 函數的第二個引數指示 Excel 應該捨入到第幾位數。在此情況下,2 指示 Excel 捨入到最接近的百位數。

 

按一次鍵盤即可建立新的圖表或工作表

如果要迅速建立圖表,請先選取圖表資料再按 F11 鍵。如果要建立新工作表,則按下 SHIFT+F11。

 

在單一工作表中設定多重列印範圍

您不需要使用巨集,就可以在單一工作表中設定多重列印範圍。如果要執行這項操作,請使用「自訂檢視模式」指令和「列印報表」指令。基本做法是,先定義工作表的檢視模式,再用您所選擇的檢視模式來定義報表。

學不完.教不停.用不盡文章列表

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(17) 人氣()