2016年5月31日 星期二

Word-建立長文件之多層次清單

在 Word 中有一個「多層次清單」的功能,可以來自動建立像下圖中的「節、小節」的編號。本篇文章就來練習這個操作。
Word-建立長文件之多層次清單
先建立一個範例文字,在 Word 文件中輸入如下的內容,其中紅色字部分要當為「節」的標題,藍色字部分要當為「小節」的標題。
Word-建立長文件之多層次清單
因為多層次清單的設定過程和段落的大綱階層有關,所以首先在段落的格式設定中將「節」標題的大綱階層設定為「階層1」,將「小節」標題的大綱階層設定為「階層2」。
Word-建立長文件之多層次清單
利用「功能窗格」來檢視,可以明顯看到階層1和階層2的結構,你可以藉此建立節和小節的樣式。
Word-建立長文件之多層次清單
接著,開始來建立多層次清單。
在[常用/段落]功能表中選取「多層次清單」,在選單中選取「定義新的多層次清單」。
Word-建立長文件之多層次清單
(1) 設定節
在[定義新的多層次清單]對話框中設定:(目前[按一下要修改的階層]已預設在「1」,即選取第1階層。)
Word-建立長文件之多層次清單
在[輸入數字的格式設定]文字方塊中,調整為:在功能變數(目前顯示1)前輸入「第」、在功能變數後輸入「節」,再按一下空白鍵。(結果如下圖)
Word-建立長文件之多層次清單
(2) 設定小節
接著,在[按一下要修改的階層]清單中選取「2」,並將[輸入數字的格式設定]文字方塊中的文字全數刪除。
Word-建立長文件之多層次清單
在[輸入數字的格式設定]文字方塊中輸入「第」,然後在[包含的階層編號]下拉式清單中選取「階層1」。(目前結果如下圖)
Word-建立長文件之多層次清單
接著在[輸入數字的格式設定]文字方塊中繼續入「-」,然後在[這個階層的數字樣式]下拉式清單中選取「1,2,3, ...」樣式(此為階層2的數字樣式)。(結果如下圖)
Word-建立長文件之多層次清單
繼續在[輸入數字的格式設定]文字方塊中輸入「節」,再按一下空白鍵。(結果如下圖)
Word-建立長文件之多層次清單
最後,按下[確定]按鈕,完成了定義這個新的二層清單。
然後,將所有藍色字段落(小節),利用「增加縮排」按鈕,使其增加一個縮排。
Word-建立長文件之多層次清單
接著,選取所有文字,在[多層次清單]下拉式清單中選取剛剛自行定義的樣式。
Word-建立長文件之多層次清單
結果如下圖:
Word-建立長文件之多層次清單
你如果輸入新的標題文字,再套用先前設定的多層次清單,結果如下:
Word-建立長文件之多層次清單
如果按一下「增加縮排」按鈕,即設定為「小節」的樣式
Word-建立長文件之多層次清單
如果你有設定樣式,則可以同時達成樣式和多層次清單的動作。
(本例選「節」樣式時,自動產生「第5節」。)
Word-建立長文件之多層次清單
(本例選「小節」樣式時,自動產生「第4-3節」。)
Word-建立長文件之多層次清單

Excel-在多個欄位中查詢同一個內容(MATCH,OFFSET)

有網友想要在一個 Excel 資料表中查詢,根據下圖中的左表,在右表中查詢到對應的結果,該如何處理。
在下圖左表中,戶別對應二個車位的欄位(一對多),在下圖右表中,每個車位只會對應一個戶別(一對一)。
Excel-在多個欄位中查詢同一個內容(MATCH,OFFSET)

【公式設計與解析】
儲存格F2:=IFERROR(OFFSET($A$1,MATCH(E2,$B$2:$B$11,0),0),"") &
IFERROR(OFFSET($A$1,MATCH(E2,$C$2:$C$11,0),0),"")
第1式:IFERROR(OFFSET($A$1,MATCH(E2,$B$2:$B$11,0),0),"")
(1) MATCH(E2,$B$2:$B$11,0)
利用 MATCH 函數找出儲存格E2在儲存格B2:B11中的對應位置。(傳回一個數值,本例傳回2)
(2) OFFSET($A$1,MATCH(E2,$B$2:$B$11,0),0)
根據(1)的傳回值利用 OFFSET 函數以儲存格A1為起點,查詢相對位置的內容。(本例傳回A1-2F)
(3) IFERROR(OFFSET($A$1,MATCH(E2,$B$2:$B$11,0),0),"")
若 OFFSET 函數傳回錯誤訊息,則利用 IFERROR 函數將其轉換為空字串("")。
同理:
第2式:IFERROR(OFFSET($A$1,MATCH(E2,$C$2:$C$11,0),0),"")
最後,公式:=第1式 & 第2式,其中第1式或第2式,同時間只會有一個傳回內容,可能其中一個傳回空字串,或是兩個都傳回空字串。

Excel-在工作表的每一頁正中央有置入圖片

有網友想要在 Excel 的工作表中置入一個類似浮水印圖片,並且希望圖片能置於紙張的中央,並且每頁重覆,該如何處理?
我其實沒有正確的標準化做法,不過以下的做法只要用 Office 軟體即可完成,大家參考一下吧!
假設下圖是我們要置入工作表的浮水印圖片:
Excel-在工作表的每一頁正中央有置入圖片
【參考步驟】
1. 在 Excel 中先把工作表的版面設定好,下圖中的虛線即描繪出每一個頁面的大小。
2. 在工作表中插入要置入的浮水印圖片,並且放到一個頁面的正中央。
3. 選取含有圖片的「一個頁面」範圍。
4. 選取[常用/剪貼簿]功能表中的「複製成圖片」選項。
Excel-在工作表的每一頁正中央有置入圖片
5. 選取外觀:如螢幕顯示;格式:圖片。(此時已將選取範圍複製成一張圖片)
Excel-在工作表的每一頁正中央有置入圖片
6. 開啟 Word 程式,貼上該圖片。
7. 在圖片上按右鍵,選取「另存圖片」。將該圖儲存成檔案。
Excel-在工作表的每一頁正中央有置入圖片
8. 開啟你要使用的 Excel 工作表,進入[版面設定]對話框,並按「自訂頁首」。
Excel-在工作表的每一頁正中央有置入圖片
9. 按一下「插入圖片」圖示,選取先前儲存格的浮水印圖片。在文字方塊中會置入「&[圖片]」功能變數。
Excel-在工作表的每一頁正中央有置入圖片
回到[頁首/頁尾]標籤下,頁首已呈現「&[圖片]」。
Excel-在工作表的每一頁正中央有置入圖片
10. 當你預覽列印時,即可發現該圖片已在版面的中央位置,而且每頁出現。
Excel-在工作表的每一頁正中央有置入圖片
這個做法其實不完全是浮水印,因為雖然前景內容可以蓋住背景圖片,但是原來的圖片沒有被淡化處理,因此建議自行將原始圖片淡化處理再來當為浮水印圖片。
Excel-在工作表的每一頁正中央有置入圖片

2016年5月26日 星期四

Excel-解決公式中使用含有設定數值格式為文字的儲存格產生的錯誤

網友在 Excel 中使用設定數值格式時遇到了如下的困擾,大家一起來想看看有何方法可以解決?如下圖,有「時間、速度、距離」三個欄位,希望輸入數值後能帶出單位「Sec、Km/Sec、Km」。
Excel-解決公式中使用含有設定數值格式為文字的儲存格產生的錯誤
當你的步驟如下時:
步驟1: A欄設定格式:@"Sec",B欄設定格式:@"Km/Sec",C欄設定格式:@"Km"
步驟2:儲存格C2:=A2*B2
結果顯示的是錯誤結果(參考下圖),該如何調整?
Excel-解決公式中使用含有設定數值格式為文字的儲存格產生的錯誤
我的步驟如下:(A欄和C欄的設定同上,以下只設定C欄。)
步驟1:儲存格C2:=TEXT(A2*B2,'@'&'')
步驟2:C欄設定格式:@"Km"
Excel-解決公式中使用含有設定數值格式為文字的儲存格產生的錯誤
結果是正常的(如下圖)。其中儲存格C2:=TEXT(A2*B2,'@'&'')的作用,我是要將數值轉換為文字,再串接至Km。
Excel-解決公式中使用含有設定數值格式為文字的儲存格產生的錯誤
徵求有無更好的解決方案呢?

Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

網友想要在一個清單中,如下圖是多個 IC 接腳及名稱的清單,如何在挑選一個接腳後,列出含有這個接腳的所有 IC?這類問題已多次有網友詢問過,顯見很多人需要這類問題的解答,所以很樂意再說明一次。
Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW) Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

(1) 使用『篩選』工具
在資料清單中啟動『篩選』工具,只要勾選想要的接腳名稱,即可篩選出IC的名稱。而篩選結果通常要在其他地方使用,因此,只要複製篩選結果,例如複製儲存格B2:B8,然後在另一個位置貼上,其實只有3個項目會被複製,其中被隱藏的儲存格不會被複製。
這個方法在操作上很簡單,但是如果清單內容改變了,你都要重新篩選一次。
Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

(2) 使用『樞紐分析』工具
你也可以插入一個樞紐表,一次列出所有接腳相關的 IC 名稱,設定如下:
Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)
接著,在接腳欄位中按一下「作用中欄/欄位設定」。
Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)
在[小計與篩選]標籤下,設定小計為:無。
Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)
在[版面配置與列印]標籤下,選取「以列表方式顯示項目標籤」和勾選「重覆項目標籤」選項。
Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)
這個方法在操作上也很簡單,但是如果清單內容改變了,你仍然要重新整理才能得到正確結果。

(3) 設計公式
Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)
儲存格B2:{=IFERROR(OFFSET($B$1,SMALL(IF($A$2:$A$9=$D$2,
ROW($A$2:$A$9),FALSE),ROW(1:1)),0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格B2,貼至儲存格B2:B9。
(1) IF($A$2:$A$9=$D$2,ROW($A$2:$A$9),FALSE)
在陣列公式中,判斷儲存格A2:A9中和儲存格D2是否相符。若成立,則傳回一個列號(例如儲存格A4會傳回4);若不成立,則會傳回 FALSE
(2) SMALL(第(1)式,ROW(1:1))
找出第(1)式中所傳回的所有列號中最小的列號。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...,可以找出第2小、第 3 小、... 的列號。
(3) OFFSET($B$1,第(2)式,0)
將第(2)式傳回的列號代入 OFFSET 函數中,取得對應的B欄內容。
(4) IFERROR(第(3)式,"")
因為公式由儲存格B2向下複製,所以在第(2)式的 SMALL 函數可能傳回錯誤訊息。例如:在儲存格D7中要找尋第4小的列號,因為GND只有三個,所以會傳回錯誤。因此,使用 IFERROR 函數將錯誤訊息顯示為空字串("")。

2016年5月23日 星期一

Excel-計算數個時間的平均值(AVERAGE,LEFT,MID,RIGHT,陣列公式)

有網友問到,如何根據 Excel 的時間清單,來計算這些時間的平均值?如下圖,時間清單可能由數字構成時分秒,也可能由文字構成時分秒,如何來計算其平均時間?
Excel-計算數個時間的平均值(AVERAGE,LEFT,MID,RIGHT,陣列公式)

【公式設計與解析】
1. 時間為數值格式
儲存格B2:=AVERAGE(A2:A18)
如果時間清單中的時間是數值格式,則可以直接以 AVERAGE 函數求得其平均時間,結果也是一個數值。

2. 時間為文字格式
儲存格E2:{=(SUM(VALUE(LEFT(D2:D18,2)))*3600+SUM(VALUE(MID(D2:D18,
4,2)))*60+SUM(VALUE(RIGHT(D2:D18,2))))/(24*60*60)/COUNTA(D2:D18)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
(1) 時換算秒數:SUM(VALUE(LEFT(D2:D18,2)))*3600
在陣列公式中,使用 LEFT 函數取出每個時間的第1,2碼,計算總和後再乘以3600(一小時有3600秒)。(VALUE 函數用以將文字轉成)
(2) 分換算秒數:SUM(VALUE(MID(D2:D18,4,2)))*60
在陣列公式中,使用 MID 函數取出每個時間的第4,5碼,計算總和後再乘以60(一分鐘有60秒)。
(3) 計算秒數:SUM(VALUE(RIGHT(D2:D18,2)))
在陣列公式中,使用 RIGHT 函數取出每個時間的第7,8碼,計算總和。
(4) 計算平均,公式=(1)+(2)+(3)/(24*60*60)/COUNTA(D2:D18)
(1)+(2)+(3)/(24*60*60):將時間總和換算為秒數。
然後,再除以COUNTA(D2:D18)(計算個數),求得平均值。

2016年5月22日 星期日

Excel-依組別在最後一筆資料計算小計(SUMIF,SUMPRODUCT)

有網友想要利用 Excel 在一個已經依照組別排列的清單中,自動計算小計,該如何處理?而小計只出現在該組的最後一筆位置。
觀察下圖中,各個組別最後一筆會有對應的一筆小計。
Excel-依組別在最後一筆資料計算小計(SUMIF,SUMPRODUCT)

【公式設計與解析】
(1) 使用 SUMPRODUCT 函數
透過 SUMPRODUCT 函數運算,當資料內容變動時,小計可以隨之變動。
儲存格D2:=IF(A2<>A3,SUMPRODUCT(($A$2:A2=A2)*$C$2:C2),"")
複製儲存格D2,貼至儲存格D2:D26。
SUMPRODUCT(($A$2:A2=A2)*$C$2:C2):計算從A欄的第一筆資料(組別)和所在儲存格符者,計算對應C欄(銷售量)的和,此為該組的小計。
然後,在 IF 函數中判斷 A2<>A3 是否成立,若成立表示為儲存格A2和儲存格A3為不同組別,則顯示小計算結果,若不成立,則顯示空白。

(2) 使用 SUMIF 函數
除了使用 SUMPRODUCT 函數,你也可以使用 SUMIF 函數,可以得到相同的結果。
儲存格D2:=IF(A2<>A3,SUMIF($A$2:A2,A2,$C$2:C2),"")

(3) 利用小計工具
因為資料已經依組別排序完成,所以你也可以使用 Excel 提供的小計工具,手動得到的結果(如下圖)。使用小計時的設定:
分組小計欄位:組別
使用函數:加總
新增小計位置:銷售量
image

2016年5月21日 星期六

Excel-在儲存格輸入資料時設定輸入順序

有網友問到在 Excel 中如果想要在儲存格輸入資料時,希望能依自己想要的順序來輸入,該如何操作?
以下圖為例:希望的輸入順序是B3:B13→C3:C13→...→F3:F13。
Excel-在儲存格輸入資料時設定輸入順序
你只要先選取B3:F13,每輸入一筆資料後按 Enter 鍵,當輸入到儲存格B13,按下 Enter 鍵後,則會自動跳到儲存格C3,可以繼續輸入資料。
Excel-在儲存格輸入資料時設定輸入順序
如果你想要的輸入順序是:儲存格B2:F2→B3:F3→...→B9:F9。
Excel-在儲存格輸入資料時設定輸入順序
你可以先進入 Excel 選項,切換至[進階]標籤下,找到「按 Enter 鍵後,移動選取範圍﹞,並且在[方向]下拉式清單中選取「右」。
Excel-在儲存格輸入資料時設定輸入順序
當你在儲存格B2輸入資料後按下 Enter 鍵,會跳至儲存格C2,繼續輸入資料後,當在儲存格F2輸入資料後按下 Enter 鍵,則會自動跳至儲存格B3,繼續輸入資料。
Excel-在儲存格輸入資料時設定輸入順序
當你選取的是不連續的儲存格範圍(如下圖),則會依你『選取的順序』來讓你輸入資料,例如下圖中選取的順序是:B2→D3:D6→C5:CB7→E4:E7→F3:F5。所以當儲存格B7輸入完成時會跳至儲存格D3等待輸人。
Excel-在儲存格輸入資料時設定輸入順序

Excel-限定儲存格只能輸入文字(ISTEXT,資料驗證,設定格式化的條件)

網友想要在 Excel 的工作表中的某些儲存格,限制其只能輸入『文字』,該如何處理?一般的作法都是透過設定『資料驗證』的方式來處理。
例如在下圖中,當在儲存格A8輸入數字時,Excel 會顯示提示訊息,表示輸入的值不正確,必須重新輸入。
Excel-限定儲存格只能輸入文字(ISTEXT,資料驗證,設定格式化的條件)
作法如下:
1. 選取儲存格A2:A18。
2. 選取[資料/資料驗證]功能表中的「資料驗證」選項。
3. 在[設定]標籤下,設定資料驗證準則:
儲存格內允許:自訂。
公式:ISTEXT(A2)
Excel-限定儲存格只能輸入文字(ISTEXT,資料驗證,設定格式化的條件)
如果你想使用「設定格式化的條件」來標示錯誤,則可以:
1. 選取儲存格A2:A18。
2. 選取[常用/設定格式化的條件]功能表中的「新增規則」選項。
3. 選取[使用公式來決定要格式化哪些儲存格]選項。
4. 編輯規則:
公式:=NOT(ISTEXT(A2))
格式:=前景色彩為「紅色」、粗體
Excel-限定儲存格只能輸入文字(ISTEXT,資料驗證,設定格式化的條件)

【延伸練習】
你可以進一步練習其他的資料驗證應用:

好康東東