2014年12月30日 星期二

Excel-資料表中含有合併儲存格無法排序怎麼辦?

有網友問到:資料表中含有合併儲存格時,無法順利排序怎麼辦?的確,如果你的資料範圍中含有「合併」的儲存格,在排序時 Excel 會警告合併儲存格的大小需一致。

如下圖中資料表的「組別」被置於B欄和C欄,其中部分儲存格已合併,部分沒有合併,所以 Excel 不讓你使用「組別」來排序。

除非你取消合併的儲存格,否則 Excel 無法排序。為了合併儲存格,大家可能習慣使用[常用]功能表上的「跨欄置中」,它會執行合併儲存格並且將資料置中。

如果你不想要取消合併儲存格,則以下提供一個替代方案。可以將原先你要合併的儲存格改設定以下的格式:

在文字對齊方式中選取:跨欄置中;取消勾選:合併儲存格。

設定好後,即可使用「組別」來排序了。

Excel-使用定義名稱和建立表格來運算

在 Excel 的資料表中要執行運算,不外乎是輸入公式,按下 Enter 鍵,即可得到答案。以下用三種方式來比較運算的操作方式。

以下圖為例:在儲存格E2中輸入公式:=C2*D2

立即會得到答案 1500。然後你會複製儲存格E2,再貼至儲存格E2:E11,完成一個資料表的運算。

如果你先定義了資料範圍的名稱:選取儲存格C1:D11,按 Ctrl+Shift+F3 鍵,勾取「頂端列」,定義名稱:數量、單價。

則運算時可以使用名稱來取代儲存格位址,例如儲存格E2中輸入公式:=數量*單價。

然後你會複製儲存格E2,再貼至儲存格E2:E11,完成一個資料表的運算。

如果你先將資料表轉換成表格,例如:選取儲存格A1:E11,按一下[插入/表格]功能選單中的「表格」,則 Excel 會讓你確認資料範圍和是否含有標題列。

轉成表格形式的資料表,使用相同的公式時,例如:儲存格E2輸入公式:=數量*單價。(儲存格E2輸入公式:=C2*D2 亦可)

當按下 Enter 鍵時,Excel 會自動填滿以下儲存格中的公式:

在這個表格中按一下[資料表工具/工具]功能選單中選取「轉換為範圍」,則目前為表格形式的資料會轉換回原來的儲存格範圍。

2014年12月28日 星期日

Excel-運算列表的練習

在 Excel 中如果你要建立一個類似九九乘法表的相乘積(參考下圖),可以透過公式:
儲存格E2:=E$1*$D2,複製儲存格E2,貼至儲存格E2:M10。
這是善用相對位址和絶對位址的良好範例。如果這個範例改用 Excel 的「運算列表」來產生,該如何處理?

1. 建好上圖的基本表,在儲存格D1中輸入公式:=B1*B2
2. 選取[資料/資料工具/模擬分析]功能表中的「運算列表」。
3. 在[運算列表]對話框中,列變數儲存格輸入:$B$1,欄變數儲存格輸入:$B$2。

按下[確定]按鈕,即會產生一個陣列構成的相乘積結果(觀察儲存格中的公式)。可見運算列表讓你不用建立每個儲存格的公式,只要給予一個儲存格公式,其餘公式由 Excel 自動依照變數關係,幫你產生結果(不是公式)。

如果把上述的觀念套用到貸款利息計算,參考下圖:
儲存格A5:=-PMT(B2/12,B3*12,B1)
其中 PMT 函數的使用觀念如下:
PMT 函數:在定額及固定利率情形下,計算貸款付款數額。
語法:PMT(rate, nper, pv, [fv], [type])
Rate:貸款的利率。
Nper:貸款的總付款期數。
Pv:現值,即未來各期付款現值總額;也稱為本金。
Fv:最後一次付款完成後,所能獲得的未來值或現金餘額。 如果省略,則假設其值為 0。
Type:這是數字 0 或 1,指出付款期限。

當按下[確定]按鈕後,即會建立各種條件下的利息:

如果你想利用公式自行求解,可以輸入公式:
儲存格B6:=-PMT(B$5/12,$A6*12,$B$1),複製儲存格B6,貼至儲存格B6:F11。

2014年12月27日 星期六

Excel-合併彙算(整合不同來源的資料表)

在某些工作場域中,很多 Excel 報表是重覆在使用的,而這些一張一張的報表,如何整合成一張呢?例如:在學校裡會有分組教學,一門課會有二個人打成績,但最後只需送出一個成績。在 Excel 中使用「合併彙算」工具,讓這個工作變得很輕鬆。

參考以下的例子來操作,本例中有三張已建立的報表:

如何將三張報表整合在一張呢?

1. 新增一個工作表。

2. 選取[資料/資料工具]功能表中的「合併彙算」。

3. 選取想要的函數,本例為:平均值。

4. 在參照位址中選取三個工作表中資料,每選取一個工作表,按一次[新增]按鈕。本例共執行三次。

5. 可以勾選:頂端列、最左欄。

6. 按一下[確定]按鈕。

稍加美化後可得三個工作表內容的平均值:

如果想要修改運算的函數,可以再按一下「合併彙算」,挑選想要的函數:(本例為:加總)

合併彙算中可以使用的函數如下圖:

Excel-不改變儲存格格式即可找出想要的儲存格(資料驗證)

在 Excel 中的資料表中,如果想要找出符合某些條件的儲存格,你可能想到的是「設定格式化的條件」這個工具。但是,如果你「不想要改變工作表中的儲存格格式」,只是想要在工作表「檢視」,你可以試試別的手法,例如:資料驗證。

以下圖為例:在以下的資料表中,想要找尋超過 75 的儲存格,利用「資料驗證」來處理。

1. 選取儲存格A2:E17。

2. 選取[資料/資料工具]功能表中的「資料驗證」。

3. 設定資料驗證,儲存格內允許:整數,資料:小於,最大值:=$G$2

(此處必須使用相反的邏輯)

當你選取[資料/資料工具]選單中的「圈選錯誤資料」,即可標示出錯誤之處。(你知道為何要設定相反的邏輯了嗎?)

按一下「清除錯誤圈選」,即可將紅色的圈選去除,在儲存格G2輸入新的數值,則可以再重新圈選。

例如:找出一堆數值資料中被填入文字的儲存格。

例如:找出未來日期:

2014年12月26日 星期五

Evernote-使用更多的維護功能

日前因為使用 Evernote 的問題,寫了一封郵件到 Evernote 詢問,他們給了一個我沒有注意到的功能。平常我們在功能表中的「說明」選單下,看到的功能如下圖:

你如果是先按著 Ctrl 鍵,再按[說明]功能表,則會顯示更多的功能選項,這些大多是和維護相關的功能。先收下,有一天說不定會用到。

2014年12月25日 星期四

Excel-合併和分離日期/星期幾(TEXT,LEN,RIGHT,LEFT)

網友問到這樣的類似題:(1) 在 Excel 中如何將一個日期轉換為日期加上星期幾?(2) 在 Excel 中如何將一個日期加上星期幾分離?參考下圖。

(1) 將一個日期轉換為日期加上星期幾

儲存格C2:=TEXT(A2,"yyyy/mm/dd")&"("&RIGHT(TEXT(A2,"[$-404]aaaa;@"),1)&")"

複製儲存格C2,往下各列貼上。上式可以分為二個部分:

TEXT(A2,"yyyy/mm/dd"):使用 TEXT 函數將儲存格內容轉換為年四碼、月二碼、日二碼。

RIGHT(TEXT(A2,"[$-404]aaaa;@"),1):先使用 TEXT 函數將儲存格內容轉換「週三」格式,最使用 RIGHT 函數取最右邊一個字。

 

(2) 將一個日期加上星期幾分離

儲存格E2:=LEFT(C2,LEN(C2)-3)

利用 LEN 函數計算儲存格C2的字串長度,利用 LEFT 函數由左邊取字串,字數比總長度少 3 個字。

儲存格F2:=RIGHT(C2,3)

利用 RIGHT 函數由右邊取 3 個字的字串。

複製儲存格E2:F2,往下各列貼上。

Excel-表格欄、列、資料的重組(MATCH,OFFSET)

在 Excel 中的資料表是由欄位名稱和記錄所構成,所以包含了三種元素:欄、列、資料。有時需要將一個表格的三個元素重新排列組合,參考以下二個例子。

【範例一】

根據上圖,原來的資料為「A、B、C」為主,要如何調整為「早、中、夜」?

儲存格G2:=IFERROR(OFFSET($B$1,,MATCH(G$1,$B2:$D2,0)-1),"")

MATCH(G$1,$B2:$D2,0):找出儲存格G1的內容在儲存格B2:D2中的位置。

OFFSET($B$1,,MATCH(G$1,$B2:$D2,0)-1):將上式傳回值(1、2、3)代入 OFFSET 函數查詢到對應的「早、中、夜」。

最後利用 OFFSET 函數將上式因查詢不到資料所傳回的錯誤訊息,改以空白顯示。

複製儲存格G2,貼至儲存格G2:L8。

 

【範例二】

第二個範例比較複雜,主要是將範例一的垂直和水平予以交換。

儲存格G2:=IFERROR(OFFSET($A$1,,MATCH($F2,INDIRECT("B"&COLUMN(B:B)
&":D"&COLUMN(B:B)),0)),"")

使用的解法和範例一完全相同,只是因為欄列互換,所以使用 INDIRECT 函數來轉換位址。主要的改變在於將:

MATCH(G$1,$B2:$D2,0)

改寫成:

MATCH($F2,INDIRECT("B"&COLUMN(B:B)&":D"&COLUMN(B:B))

其中:INDIRECT("B"&COLUMN(B:B)&":D"&COLUMN(B:B),因為 COLUMN(B:B) 向右複製時 COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4、…,所以會傳回:B2:D2位址→B3:D3位址→B4:D4位址。

其餘請參考範例一的說明。

2014年12月24日 星期三

Excel-根據不同等級的不同條件顯示格式

在 Excel 的資料表中如果要讓儲存格格式隨著某些條件顯示不同的格式,大家都知道要使用「設定格式化的條件」來操作。參考下圖,有讀者想要根據不同等級,將超過安全值的數值顯示不同的格式,該如何處理?

如上圖,選取儲存格B2:B23,在「設定格式化的條件」時,編輯規則:

規則類型:使用公式決定要格式化哪些儲存格

規則:=(A2="A")*(B2>3)+(A2="B")*(B2>7)+(A2="C")*(B2>11)

格式:紅色粗體字

討論一下這個規則:=(A2="A")*(B2>3)+(A2="B")*(B2>7)+(A2="C")*(B2>11)

上式中的「*」相當於執行邏輯 AND 運算,而「+」相當於執行邏輯 OR 運算。若等級分類有增加,只要仿上述公式延伸即可。

Excel-將資料由垂直方向轉水平方向呈現(OFFSET,MATCH)

學校的同仁遇到這樣的問題:一般由系統轉出的 Excel 資料表如下圖這樣,其中一個人的資料分成不同列且數量不一,如果要將每一個人的資料再利用 Word 的合併列印功能來輸出成其他文件,每個學生要印在一張報表上,很顯然這個表格無法直接套用在合併列印當為資料檔。

你可能希望轉換成以下的表格,才能在 Word 文件中做為合併列印的資料表:

公式如何設計呢?

首先要注意到資料表中必須要有一個唯一值,例如本例中的「學號」。接著要將重覆的學生資料去除,留下每人一筆。

1. 選取所有資料的儲存格。

2. 按一下[插入/表格]功能表中的「表格」。

3. 在[建立表格]對話框中,按下[確定]按鈕,將儲存格範圍轉換為表格。

4. 點選[設計/工具]功能表中的「移除重覆」按鈕。

5. 在[移除重覆]對話框中,取消勾選「科目、成績」選項,按下[確定]按鈕。

Excel 會告訴你有多少資料被移除:

在已經去除重覆資料的表格上,再按一下「轉換為範圍」,即可去除表格回到儲存格範圍。

或是選取資料後,按一下[資料/資料工具]功能表中的「移除重複」:

image[2]

只勾選:學號、姓名,按下[確定]按鈕。

image[5]

接著來設定公式:

選取有學號的資料範圍,按一下  Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學號。

(1) 抓取科目

儲存格H2:=IF(OFFSET($A$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0)=$F2,
OFFSET($C$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0),"")

公式看來有點複雜,細部剖開來看:

MATCH($F2,學號,0):利用 MATCH 函數找出儲存格F2的內容在「學號」儲存格範圍的位置,傳回代表第幾個的數字。

INT((COLUMN(A:A)-1)/2):因為 COLUMN(A:A)=1,若向右複製公式時,會自動產生 COLUMN(B:BA)=2、COLUMN(C:CA)=3、…。將 COLUMN(A:A)-1 除以 2,再代入 INT 函數,會在H欄傳回 0、J欄傳回 1、L欄傳回 2、… 。

OFFSET($C$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0):將以上二式代入 OFFSET 函數可以查詢到以儲存格C2為開始的相對位置之儲存格,即會傳回儲存格C2,若向右複製公式時,會在H欄傳回儲存格C2、J欄傳回儲存格C3、L欄傳回儲存格C4、… 。

同理,判斷公式:OFFSET($A$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0)=$F2的結果是否和對應的學號(儲存格F2)是否相同,如果一樣則代表同一學生,所以要顯示科目內容,如果不一樣則代表不同學生,所以顯示空白。

(2) 抓取成績

儲存格I2:=IF(OFFSET($A$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0)=$F2,OFFSET($D$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0),"")

公式改變很小,只是將(1)的 OFFSET($C$2 改成 (2)的 OFFSET($D$2。

最後複製儲存格H2:I2,貼至其他資料範圍,本例為儲存格H2:S32。

好康東東