贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

目前日期文章:201511 (33)

瀏覽方式: 標題列表 簡短摘要

在 Windows 7 中大家已經習慣檢視檔案的詳細資訊,突然改用 Windows 10 之後,可能一時間看不到檔案的詳細資訊,而且這個詳細資訊顯示的位置也不一樣了。以下來比較一下。

在 Windows 7 要檢視檔案資訊,可以在[組合管理]功能表中的[版面版置]選項中勾選「詳細資料窗格」來切換。檔案的詳細資訊會顯示在視窗的最下方。

Windows7/10檢視和修改檔案的詳細資訊

如果你已在使用 Windows 10 系統,則可以直接在[檢視]功能表中的[窗格]區,點選「詳細資料窗格」。檔案的詳細資訊會顯示在視窗的右側。

Windows7/10檢視和修改檔案的詳細資訊

因為在 Windows 10 中『預覽窗格』和『詳細資料窗格』中共用同一個區域,所以只能擇一顯示。不過,在 Windows 7 中這兩者是同時顯示的。

Windows7/10檢視和修改檔案的詳細資訊

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

讀者問到:在 Excel 中已有一個資料檔(如下圖例子,具有多個欄位的資料表),如果要由 Word 文件中合併列印取用這個資料檔,並且篩選符合條件的項目來合併,該如何處理?

以下例子要在 Word 文件中篩選學校名稱中有『桃園』二字者來合併列印。

Word-合併列印Excel資料檔並且篩選符合條件的列印項目

先以合併列印的「信件」模式,假設你在 Word 的文件中已插入要合併的欄位:

Word-合併列印Excel資料檔並且篩選符合條件的列印項目

先預覽結果:

Word-合併列印Excel資料檔並且篩選符合條件的列印項目

當你要篩選學校名稱中有『桃園』二字者,可以按一下「編輯收件者清單」,然後再按一一下「篩選」。

Word-合併列印Excel資料檔並且篩選符合條件的列印項目

進行以下設定:

欄位:學校;邏輯比對:包含;比對值:桃園。

Word-合併列印Excel資料檔並且篩選符合條件的列印項目

如此,篩選出來的學校都是包含『桃園』二字者:

Word-合併列印Excel資料檔並且篩選符合條件的列印項目

如果你是以『標籤』模式來信併列印,做法和上述完全相同。也是在「編輯收件者清單」對話框中進行「篩選」動作。並設定為「欄位:學校;邏輯比對:包含;比對值:桃園。」

Word-合併列印Excel資料檔並且篩選符合條件的列印項目

預覽結果即可看到正確結果。

Word-合併列印Excel資料檔並且篩選符合條件的列印項目


【思考一下】

你先前在 Word 的合併列印中篩選資料來源,那麼為何不直接在 Excel 的資料檔中篩選呢?在 Excel 使用篩選工具,篩選學校包含『桃園』二字者。

Word-合併列印Excel資料檔並且篩選符合條件的列印項目

在 Word 文件中就不需要再篩選一次了!

Word-合併列印Excel資料檔並且篩選符合條件的列印項目

你要使用那一種方法,應該都是可以的,就讓你靈活運用吧!

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

大家有遇到這種現象嗎?例如:在 Excel 中的 A 檔工作表1的儲存格A1,關聯到另一個 B 檔工作表1的儲存格A1,當你儲存檔案後,如果再複製到USB或是郵寄給別人時,他人打開檔案,會發現其中的公式變的不一樣,可能很複雜且很亂,但是顯示的結果卻是正確的。該如何不讓公式隨之改變呢?

以下是讀者的原始問題:

假設有A和B兩個檔案,B內有儲存格是設公式連結A中的數值,但是一但我搬動或複製或把B檔案傳給別人的時候,雖然數字不會動,但是裡面的公式都會變成很長
例如: 'C:\Users\222\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\NHVAA9JM\[test1.xlsx]Sheet1'!$B$1+'C:\Users\1030502\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\NHVAA9JM\[test1.xlsx]Sheet1'!$B$2之類的,我試過單一檔案移動 和A檔案一起移動都沒有用, 我現在被要求要想辦法可以移動檔案公式不會跑掉,但是又不是把A併入B檔案當作B檔案的一個Sheet。因為工作多有移動和寄送(MAIL)需求, 請問該怎麼做呢?

在下圖中有 A、B 兩個 Excel 檔案,當這個兩個檔案都開啟時,設定 B 檔案中工作表1的儲存格A1=[A.xlsx]工作表1!$A$1。(這是一個相對位址的表示方法)

Excel-如何讓關聯到另一個檔案的公式在傳送給別人時不會被改變

當你把 A 檔案關閉時,B 檔案中的公式即刻變為絶對位址的表示方式,即會列出檔案在磁碟中的真實位址。(本例檔案為於 E:\Google Downloads\temp1\ 資料夾)

Excel-如何讓關聯到另一個檔案的公式在傳送給別人時不會被改變

所以當你把檔案寄給別人時,當他打開檔案時,Excel 會自動將儲存格中的絶對位址改成他當時檔案所儲存的位址。若是由電子郵件中開啟,則看到的絶對位址就會像網友發問時所列的一大串路徑(該路徑是 Windows 中 Email 的暫存路徑)。

該如何解決?

如果不想更改公式內容,可以請對方先『儲存』在某個資料夾後,再開啟 Excel 檔,其中的公式就不會那麼長、那麼亂了。

但是,如果你很堅持,想要公式內容絶對和你的電腦中的檔案100%一樣時,又該如何處理?參考以下的做法:

1. 把 A、B 兩個都開啟。

2. 調整公式,將:

儲存格A1:=[A.xlsx]工作表1!$A$1

改成『=INDIRECT("原來公式")』,

儲存格A1:=INDIRECT("[A.xlsx]工作表1!$A$1")

顯示的結果兩者會相同。

Excel-如何讓關聯到另一個檔案的公式在傳送給別人時不會被改變

當傳給對方時,不管其在電子郵件中直接打開或是儲存檔案後再開啟,看到的公式都是:

儲存格A1:INDIRECT("[A.xlsx]工作表1!$A$1")

其結果和下列公式相同,也就是和你的電腦中的檔案內容相同。

儲存格A1:=[A.xlsx]工作表1!$A$1

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

本範例是延續前三篇文章的研習講義:

1. Excel-SUMPRODUCT函數範例與說明(研習範例)

2. Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

3. Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)

這次來看看 SUMPRODUCT 在多人多項如何統計個別、全體的次數和總計。

參考下圖,每人在三個項目中勾選,要來計算每人金額的小計,各項目的數量,和全體總金額。

Excel-多人多項的金額統計(SUMPRODUCT練習)


【公式設計與解析】

(1) 每人金額的小計

儲存格F3:=SUMPRODUCT((C3:E3="V")*$C$1:$E$1)

C3:E3="V":判斷在儲存格C3:E3中是否有『V』,在判斷過程中,不管使用者輸入『V、v』,該公式將兩者視為一樣。


(2) 各項目的數量

儲存格I3:=SUMPRODUCT((C3:C27="V")*1)&"本"

公式中如果要串接字串,則可以使用運算子『&』。


(3)全體總金額

儲存格I5:=SUMPRODUCT((C3:E27="V")*C1:E1)

C3:E27="V":公式運算時本例傳回 {TRUE,FALSE,FALSE;TRUE,TRUE,TRUE; ... },你可以把C3:E27視為『二維陣列』,所以每三個為一列,其中以『;』作為分隔。

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

在 Excel 中有個日期和時間所構成的資料清單,如何找出在某個時間區間中的筆數?

Excel-計算某一時間區間內的數量(SUMPRODUCT,INT)

【公式設計與解析】

選取儲存格A1:A29,按 Ctrl+Shift+F3 鍵,選取「頂端列」,定義名稱:資料。

因為 Excel 將 1 天(24小時)以 1 表示,也就是說 1 小時為 1/24。

(1) 時段:08:00~12:00

儲存格G2:=SUMPRODUCT((資料-INT(資料)>=8/24)*(資料-INT(資料)<12/24))

資料-INT(資料):取得每個日期的時數部分。(INT(資料)為日期所代表的值)

(資料-INT(資料)>=8/24)*(資料-INT(資料)<12/24):判斷日期是否介於 8 時和 12 時之間。其中『*』運算,相當於執行邏輯 AND 運算,在運算過程式中將判斷結果的 TRUE/FALSE 轉換為 1/0。再由 SUMPRODUCT 函數執行乘積和,把所有的 1 加總即為所求。

(2) 時段:12:00~16:00

儲存格G3:=SUMPRODUCT((資料-INT(資料)>=12/24)*(資料-INT(資料)<16/24))

(3) 時段16:00~20:00

儲存格G4:=SUMPRODUCT((資料-INT(資料)>=16/24)*(資料-INT(資料)<20/24))

 

【延伸討論】

如果你想要使用自動篩選工具,會發現其只能處理到『日期』,而沒有辦法處理『時間』。

Excel-計算某一時間區間內的數量(SUMPRODUCT,INT)

若使用自訂篩選也只能篩選日期:

Excel-計算某一時間區間內的數量(SUMPRODUCT,INT)

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

最近又要為校內同仁上 Excel 的研習課程,本篇為課程範例。參考先前範例:

Excel-SUMPRODUCT函數範例與說明(研習範例)

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

本篇要介紹由一個學生升學資料(模擬),每一個學生的資料分成四列呈現,有數百名學生待處理。而要將姓名中間的一個字遮蔽,並且將多列資料轉換為多欄,再統計國立和非國立的人數。

 

(1) 遮蔽姓名中間的一個字

Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)

為了學生的個人隱私及個資法的限制,常會遇到要隱蔽部分文字內容。

儲存格C3:=LEFT(A3,1)&"○"&RIGHT(A3,1)

LEFT(A3,1):用以取出儲存格A3最左邊的 1 個字。

RIGHT(A3,1):用以取出儲存格A3最右邊的 1 個字。

『&』運算子用以串接字串。

儲存格C2:=A2;儲存格C4:=A4;儲存格C5:=A5。

複製儲存格C2:C5,往下各列貼上。

 

(2) 將多列資料轉換為多欄

Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)

儲存格E2:=OFFSET($C$1,4*ROW(1:1)-3,0,1,1)

4*ROW(1:1)-3:ROW(1:1)=1,當公式向下複製時會產生ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。本例會傳回:1。

上式代入 OFFSET 函數可以傳回儲存格C1相對下移 1 列的儲存格內容。

儲存格F2:=OFFSET($C$1,4*ROW(1:1)-2,0,1,1)

可以傳回儲存格C1相對下移 2 列的儲存格內容。

儲存格G2:=OFFSET($C$1,4*ROW(1:1)-1,0,1,1)

可以傳回儲存格C1相對下移 3 列的儲存格內容。

儲存格H2:=OFFSET($C$1,4*ROW(1:1),0,1,1)

可以傳回儲存格C1相對下移 4 列的儲存格內容。

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

 

(3) 統計國立和非國立的人數

Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)

選取儲存格E1:H580(本例的資料範圍),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、姓名、學校、科系。

觀察資料中如果是國立學校,其校名最前方都冠有『國立』字樣。

儲存格K2:=SUMPRODUCT((班級=J2)*(LEFT(學校,2)="國立"))

儲存格L2:=SUMPRODUCT(1*(班級=J2))-K2

複製儲存格K2:L2,往下各列貼上。

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

(先前文章內容有錯誤,目前內容為更正版)

網友根據這篇文章:Excel-計算10位元數以上的乘法,想要了解如果要模擬10位數字的『加法』運算,該如何處理?

如下圖,有二組10個數字組成的數,要執行相加的動作,如何將每個位數的結果模擬出來呢?

Excel-模擬10位數加法運算(SUMPRODUCT,RIGHT,MID,COLUMN)


【公式設計與解析】

1. 求兩數相加的實際總和

為了避免公式過長,說明不易,特別使用輔助儲存格M3。

(1) SUMPRODUCT(B2:K2,(10^(11-COLUMN(A:J))/10))

因為10個位數分別放在10個儲存格中,所以先將儲存格B2:K2中的10個數字組合成一個完整的10位數。

(2) SUMPRODUCT(B1:K1,(10^(11-COLUMN(A:J))/10))

將儲存格B1:K1中的10個數字組合成一個完整的10位數。

將第(1)式和第(2)式相加即為這兩個10位數相加的和。

儲存格M3:=SUMPRODUCT(B2:K2,(10^(11-COLUMN(A:J))/10))+
SUMPRODUCT(B1:K1,(10^(11-COLUMN(A:J))/10))


2. 取出每個位數中的數字

儲存格A3:=MID(RIGHT("0000000000"&$O$5,11),COLUMN(A:A),1)

"0000000000"&$O$5:先前兩個相加的實際總和其位元數可能是0~10個,所以先在其左側串接10個『0』。

RIGHT("0000000000"&$O$5,11):在左側串接10個『0』的字串中,由右側取出11個字,即可確保取得完整的11個字。(原先相加後的數字不達10位元者,其左側均會被補0。)

最後透過 MID 函數搭配 COLUMN 函數,將儲存格A3,複製到儲存格A3:K3,即可將上式中的11個字,由左至右取出每一個數字。


【延伸練習】

如果對於模擬『乘法』運算有興趣者,可以參考這篇文章:Excel-計算10位元數以上的乘法

Excel-模擬10位數加法運算(MOD,INT)

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

最近又要為校內同仁上 Excel 的研習課程,做一些講義方便同仁課後參閱。本篇是利用INDEX、MATCH、OFFSET、VLOOKUP、HLOOKUP、ADDRESS、INDIRECT等函數來查詢欄列交集的資料。

參考下圖,在本例中的資料表是由『天干的名稱所組成的欄名』和由『地支的名稱所組成的列名』集合而成的資料。

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

【準備工作】

(1)選取儲存格B1:K1,在[公式/定義名稱]功能表中,定義名稱:天干。

(2)選取儲存格A2:A13,在[公式/定義名稱]功能表中,定義名稱:地支。

(3)選取儲存格B2:K13,在[公式/定義名稱]功能表中,定義名稱:資料。

(4)選取儲存格B1:K13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:甲、乙、…、壬、癸。

(4)選取儲存格A2:K13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:子、丑、…、戌、亥。

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

 

【公式設計與解析】

以下分別以5種做法來說明欄、列交叉的查詢做法。其中每種做法都會用到 MATCH 函數,如果想要查詢微軟提供的 MATCH 函數說明,可以在輸入函數時,點選說提示文字的函數名稱。(以下各種函數說明的查詢均相同)

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

你會得到如下的函數說明文件:

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

參考下圖,使用 MATCH 函數,甲在天干的儲存格範圍中會傳回1、乙傳回2、…、壬傳回9、癸傳回10。

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

 

(1) INDEX 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=INDEX(資料,MATCH(N2,地支,0),MATCH(N1,天干,0))

MATCH(N2,地支,0):找尋儲存格N2在地支中的位置,本例傳回5。

MATCH(N1,天干,0):找尋儲存格N1在天干中的位置,本例傳回5。

再透過 INDEX 函數求得第 5 列和第 5 欄交會的資料。
 

(2) OFFSET 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=OFFSET(A1,MATCH(N2,地支,0),MATCH(N1,天干,0))

MATCH(N2,地支,0):找尋儲存格N2在地支中的位置,本例傳回5。

MATCH(N1,天干,0):找尋儲存格N1在天干中的位置,本例傳回5。

再透過 OFFSET 函數求得位移 5 列和位移 5 欄的位置中的資料。

 

(3) VLOOKUUP 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=VLOOKUP(N2,A2:K13,MATCH(N1,天干,0)+1,FALSE)

MATCH(N1,天干,0):找尋儲存格N1在天干中的位置,本例傳回5。

透過 VLOOKUP 函數先查詢儲存格N2位在儲存格A2:K13中第一欄(即為『地支』的範圍)的第幾個,再由 MATCH(N1,天干,0)+1 所求得的結果(=6)對應到該欄(第6欄)取得相同列的資料。

 

(4) HLOOKUP 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=HLOOKUP(N1,B1:K13,MATCH(N2,地支,0)+1,FALSE)

MATCH(N2,地支,0):找尋儲存格N2在地支中的位置,本例傳回5。

透過 HLOOKUP 函數先查詢儲存格N1位在儲存格B1:K13中第一列(即為『天干』的範圍)的第幾個,再由 MATCH(N2,地支,0)+1 所求得的結果(=6)對應到該列(第6列)取得相同欄的資料。

 

(5) ADDRESS+INDIRECT 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=INDIRECT(ADDRESS(MATCH(N2,地支,0)+1,MATCH(N1,天干,0)+1))

MATCH(N2,地支,0):找尋儲存格N2在地支中的位置,本例傳回5。

MATCH(N1,天干,0):找尋儲存格N1在天干中的位置,本例傳回5。

因為資料是由儲存格B2開始,也就是由第2欄第2列開始,因此在 ADDRESS 函數置入:

『MATCH(N2,地支,0)+1』(傳回6)和『MATCH(N1,天干,0)+1)』(傳回6),以取得這個儲存格位址,本例傳回:$F$6。

最後再由 INDIRECT 函數將 ADDRESS 函數傳回的儲存格位址,取得該位址中的儲存格內容。

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

在 Excel 做好一個圖表,也顯示了標籤,如果想要修改其中部分資料的標籤,讓它和其它不一樣,參考下圖,其中有二個資料項目的標籤和其他項目不同。該如何處理?

Excel-在圖表中自訂標籤文字

參考以下的操作步驟:

1. 選取一個已顯示的資料標籤,整個資料數列的標籤每一個都被選取。

2. 點選第 4 個資料標籤,目前只留下這個資料標籤被選取。

3. 在文字方塊中輸入你想要的文字。

4. 對第 10 個資料標籤上重複 1~3 的步驟。

Excel-在圖表中自訂標籤文字

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

在 Excel 中樞紐分析表是一個常用的工具,當我們利用一個資料表的內容產生了一個樞紐分析表後,可以看到一些摘要的結果(已運算後的結果),如何能看到這些摘要結果的細部運算狀況呢?

以下圖為例,當產生了一個樞紐分析表,如果想要看看公假這一欄的細項,你可以在總計欄位中的『11.0』上「按二下」,即會產生一個新的工作表。在這個工作表中會顯示這個欄位的細部內容。而這個新的工作表會進入篩選模式,你可以直接使用篩選的工具。

Excel-檢視樞紐分析表中的個別資料

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

在 Excel 中如果要將數字轉換成國字來表示,例如:『123456』轉換成『壹拾貳萬參仟肆佰伍拾陸』,是相當容易的事,只要在儲存格中設定數值格式即可。但是,如果你要在 Word 中操作呢?

參考下圖,如果要轉換數字為國字,則可以先選取數字,再選取[插入/符號]功能表中的「數字」。

Word-將文件中的數字轉成國字

在開啟的[數字]對話框中選取『壹, 貳, 參, …』項,即可以執行這個轉換工作。

Word-將文件中的數字轉成國字

要特別注意:

1.可轉換的最大數字為『999999』

2,轉換後的結果是一個功能變數,如果要進一步操作,可以在數字上按右鍵,選取想要的指令。

Word-將文件中的數字轉成國字

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

最近又要為校內同仁上 Excel 的研習課程,做一些講義方便同仁課後參閱。本篇是關於 SUMPRODUCT 函數的說明。

下圖是微軟提供的 SUMPRODUCT 函數說明,主要是執行陣列元素的『乘積和』。

Excel-SUMPRODUCT函數範例與說明

下圖是一個實例,要由『姓名、性別、成績』三個欄位中,求取不同性別的人數,和不同性別的及格和不及格人數。(參考下圖)

為了解說方便,選取儲存格B1:C16,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:性別、成績。

Excel-SUMPRODUCT函數範例與說明

【公式設計與解析】

(1)

儲存格K2:=SUMPRODUCT((性別=K1)*1)

(性別=K1):判斷『性別』陣列中和儲存格K1是否相同。(觀察儲存格F2:F16)

本例傳回:{TRUE,FALSE,TRUE,TRUE,TRUE,FALSE, ... , TRUE,FALSE,TRUE,TRUE}

(性別=K1)*1:將上式轉換為{1,0,1,1,1,0, …, 1,0,1,1}

SUMPRODUCT 函數再將上式結果的 1/0 陣列執行『乘積和』,即把所有的 1 予以加總,即為所求。

複製儲存格K2,貼至儲存格K2:L2。

(2)

儲存格K5:=SUMPRODUCT((性別=$J5)*(成績>=60))

(性別=$J5)*(成績>=60):觀察儲存格F2:G16,公式中的『*』乃將F欄和G欄的內容相乘,執行過程會將邏輯值 TRUE/FALSE 轉換為數學值 1/0。(觀察儲存格F2:H16)

本例會將:{TRUE,FALSE,TRUE,TRUE,TRUE,FALSE, ... , FALSE,FALSE,TRUE,FALSE}

轉換為:{1,0,1,1,1,1,0, … , 0,0,1,0}

SUMPRODUCT 函數再將上式結果的 1/0 陣列執行『乘積和』,即把所有的 1 予以加總,即為所求。

複製儲存格K5,貼至儲存格K5:K6。

同理,

儲存格L5:=SUMPRODUCT((性別=$J5)*(成績<60))

複製儲存格L5,貼至儲存格L5:L6。

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

有同仁問到:

在 Wrod 中處理文件時,如下圖的內容中,想要在已設定項目符號的段落中,為了要使某些文字對齊,所以先設定了定位點符號(參考下圖),當插入點移至下圖的第三列的第1個字,然後按一下 Tab 鍵,卻沒有產生內縮的結果,反而是執行預設之「增加縮排」的動作。

該如何解決呢?(要把第三列的起始位置移至冒號(:)後)

Word-在設定項目符號的段落中使用Tab鍵

答案很簡單:

改按 Ctrl+Tab 鍵即可,參考下圖(不解釋)。

Word-在設定項目符號的段落中使用Tab鍵


【延伸練習】

如果是在表格中使用 Tab 鍵,也是預設會向右向下跳至下一個儲存格中,所以如果要在表格的儲存格中使用 Tab 鍵,也是要改用 Ctrl+Tab 鍵。

Word-在設定項目符號的段落中使用Tab鍵

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

在 Excel 中使用 VLOOKUP 函數來檢索資料是常手的工具,在下圖中可以發現,要使用「學號」來查詢各欄的資料,公式如下:

儲存格I2:=VLOOKUP($I$1,$A$2:$F$24,ROW(2:2),FALSE)

VLOOKUP 函數必須要將查詢的值在資料第1欄中查詢。

複製儲存格I2,貼至儲存格I2:I6。

Excel-資料檢索時用LOOKUP取代VLOOKUP(INDIRECT)

但是,如果你的資料表如果是像下圖這樣,用以檢索的欄位(學號)並非是資料的第1欄,所以無法使用 VLOOKUP 函數。該如何來正確查詢?

我們試著使用 LOOKUP 函數來處理。首先選取儲存格A1:F24,按 Ctrl+Shift+F3 鍵,定義名稱:班級、座號、學號、性別、姓名、報名。

儲存格I2:=LOOKUP($I$1,學號,INDIRECT(H2))

複製儲存格I2,貼至儲存格I2:I6。

Excel-資料檢索時用LOOKUP取代VLOOKUP(INDIRECT)


【延伸練習】

(1) 如果要使用 INDEX 函數,該如何設計?

儲存格I2:=INDEX($A$2:$F$24,MATCH($I$1,學號,0),MATCH(H2,$A$1:$F$1,0))

複製儲存格I2,貼至儲存格I2:I6。

(2) 如果要使用 OFFSET 函數,該如何設計?

儲存格I2:=OFFSET($A$2,MATCH($I$1,學號,0)-1,MATCH(H2,$A$1:$F$1,0)-1,,)

複製儲存格I2,貼至儲存格I2:I6。


【補充資料】

參考微軟網站的說明:VLOOKUP函數LOOKUP函數

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

在 Excel 中如果要將一個資料數列四捨五入至千位數或萬位數,該如何處理?

(1) 四捨五入

如下圖,要根據原始資料,轉換為四捨五入至百位、千位、萬位和10萬。通常要四捨五入,你會使用 ROUND 函數,例如:ROUND(A1,3),表示要將儲存格A1的內容,取四捨五入至小數第3位。

Excel-四拾五入至千位、萬位(ROUND,ROUNDUP,ROUNDDOWN)

一樣也是使用 ROUND 函數,如果要轉換為四捨五入至百位、千位、萬位和10萬等,只要將參數改為『負數』即可。

四捨五入至百位數,儲存格C3:=ROUND(A3,-2)

四捨五入至千位數,儲存格D3:=ROUND(A3,-3)

四捨五入至萬位數,儲存格E3:=ROUND(A3,-4)

四捨五入至10萬位數,儲存格F3:=ROUND(A3,-5)

 

(2) 無條件進位

Excel-四拾五入至千位、萬位(ROUND,ROUNDUP,ROUNDDOWN)

無條件進位至百位數,儲存格C3:=ROUNDUP(A3,-2)

無條件進位至千位數,儲存格D3:=ROUNDUP(A3,-3)

無條件進位至萬位數,儲存格E3:=ROUNDUP(A3,-4)

無條件進位至10萬位數,儲存格F3:=ROUNDUP(A3,-5)

 

(3) 無條件捨去

Excel-四拾五入至千位、萬位(ROUND,ROUNDUP,ROUNDDOWN)

無條件捨去至百位數,儲存格C3:=ROUNDDOWN(A3,-2)

無條件捨去至千位數,儲存格D3:=ROUNDDOWN(A3,-3)

無條件捨去至萬位數,儲存格E3:=ROUNDDOWN(A3,-4)

無條件捨去至10萬位數,儲存格F3:=ROUNDDOWN(A3,-5)

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

網友問到:Excel 的資料表有個數列資料,網友想要求其中的項目有多少種,把相同數字算一種,要求有多少不同的數字?(參考下圖)

Excel-計算儲存格內容有多少不同項目(SUMPRODUCT,COUNTIF)


【公式設計與解析】

(上圖)儲存格C2:=SUMPRODUCT(1/COUNTIF(A2:A24,A2:A24))

其公式原理是先求出每個數字在數列中出現的次數,然後將每個次數求倒數,再將倒數加總,該數即為項目的個數。(每種項目加總後為1)

公式中使用 COUNTIF 函數計算每個數字在數列中出現的次數。然後在 SUMPRODUCT 函數中計算 1/COUNTIF 的『乘積和』,即為所求。

參考下圖的示意圖。

Excel-計算儲存格內容有多少不同項目(SUMPRODUCT,COUNTIF)

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

在 Excel 的工作表中常會用到要重組資料,例如下圖中,每天會輸入幾筆資料,隨著日期不斷的增加,如何能將每天不同欄位的資料重組在一欄中?

下圖中,每天都要輸入甲、乙、丙、丁、戊、己、庚等項目的資料,要將這些資料重組在一個欄位中。

Excel-多欄資料重組在一欄(OFFSET,COUNT,ROW,MOD)

 

【公式設計與解析】

儲存格J2:=OFFSET($B$2,MOD(ROW(1:1)-1,COUNT(A:A)),COLUMN(A:A)-1+
INT((ROW(1:1)-1)/COUNT(A:A)),,)

COLUMN(A:A):計算日期欄位中含有幾個日期儲存格,本例傳回 7。

ROW(1:1):ROW(1:1)=1,當公式向下複製時會產生 ROW(1:1)=1→ROW(2:2)=2→
ROW(3:3)=3→ ...。

MOD(ROW(1:1)-1,COUNT(A:A)):本例為 MOD(ROW(1:1)-1,7) 傳回 0,當公式往下複製時依序傳回 0, 1, 2, 3, 4, 5, 6, 0, 1, 2, ..., 6, 0, ...。

COLUMN(A:A)-1+INT((ROW(1:1)-1)/COUNT(A:A)):本例為 7-1+INT((ROW(1:1)-1)/7) 傳回 0,當公式往下複製時依序傳回 0,0,0,0,0,0,0, 1, 1, 1, 1, 1, 1, 1, 2, 2,  ...。

將以上各式代入 OFFSET 函數中,即可重組多欄變為一欄。

本例的做法可以在新增一個日期時,不用改變公式也可以重組多欄變為一欄。

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

在 Excel 中繪製圖表時,有時會遇到某些儲存格沒有輸入任何的資料,也就是空白儲存格,但不包含是空字串的儲存格(例如:=""),在圖表中會產生數列資料的折線圖有中斷的現象,該如何來改善?

如下圖,因為幾個空白的儲存格,而導至圖表中的線條產生不連續的狀況。

Excel-繪製統計圖表時如何處理空白儲存格

你可以試著這樣做:

選取折線圖,再按右鍵,選取「選取資料」:

Excel-繪製統計圖表時如何處理空白儲存格

在[選取資料來源]對話框中,按[隱藏和空白儲存格]按鈕。

Excel-繪製統計圖表時如何處理空白儲存格

接著,選取[以零值代表]選項。

Excel-繪製統計圖表時如何處理空白儲存格

如此,圖表中的線條就不會有不連續的狀況。

Excel-繪製統計圖表時如何處理空白儲存格

而你如果選取的是「以線段連結資料點」選項,則會跳過空白儲存格直接連接有資料的數據。

Excel-繪製統計圖表時如何處理空白儲存格

不過,我想可以在未輸入資料的儲存格中輸入『=""』和『N/A或NA』,都會被 Excel 視為 0,就不需要執行以上的步驟。而N/A或NA也是代表一個字串而已,Excel 在此視為 0。

(此想法是源自 #N/A 表示「無可用數值」的錯誤值。所以使用 NA 來標示空白儲存格。)

Excel-繪製統計圖表時如何處理空白儲存格

當你填入『N/A或NA』或『=""』時,在運算時都視為空白儲存格,以計算平均為例:=AVERAGE(B2:B13)

(1)空白儲存格  (2)填入 N/A      (3)填入=""

Excel-繪製統計圖表時如何處理空白儲存格 Excel-繪製統計圖表時如何處理空白儲存格 Excel-繪製統計圖表時如何處理空白儲存格

以計算最小值為例:=SMALL(B2:B13,1)或是=MIN(B2:B13)

(1)空白儲存格  (2)填入 N/A      (3)填入=""

Excel-繪製統計圖表時如何處理空白儲存格 Excel-繪製統計圖表時如何處理空白儲存格 Excel-繪製統計圖表時如何處理空白儲存格

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

在 Excel 中儲存格可能會放有各種,例如:數字、文字、邏輯值、錯誤訊息等,如何能統計在儲存格中這些資料類型的數量?(參考下圖)

Excel-計算各種資料類型的數量(ISBLANK,ISERROR,ISLOGICAL,ISNUMBER,ISTEXT)


【公式設計與解析】

(1) 計算空白儲存格數量

儲存格F2:=SUMPRODUCT(ISBLANK(A2:C24)*1)

SUMPRODUCT 函數中,ISBLANK 函數會傳回 TRUE/FALSE 陣列,公式中的『*1』運算可以將TRUE/FALSE 陣列轉換為 1/0 陣列,再由 SUMPRODUCT 函數執行『乘積和』,即為所求。(以下做法依此類推)

(2) 計算錯誤訊息儲存格數量

儲存格F5:=SUMPRODUCT(ISERROR(A2:C24)*1)

(3) 計算邏輯值儲存格數量

儲存格F8:=SUMPRODUCT(ISLOGICAL(A2:C24)*1)

(4) 計算數值儲存格數量

儲存格F11:=SUMPRODUCT(ISNUMBER(A2:C24)*1)

(5) 計算文字儲存格數量

儲存格F14:=SUMPRODUCT(ISTEXT(A2:C24)*1)


【換個作法】

如果你想試試陣列公式,可以修改如下:

(1) 計算空白儲存格數量

{=SUM((ISBLANK(A2:C24)*1))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。(以下做法依此類推)

(2) 計算錯誤訊息儲存格數量

{=SUM(ISERROR(A2:C24)*1)}

(3) 計算邏輯值儲存格數量

{=SUM(ISLOGICAL(A2:C24)*1)}

(4) 計算數值儲存格數量

{=SUM(ISNUMBER(A2:C24)*1)}

(5) 計算文字儲存格數量

{=SUM(ISTEXT(A2:C24)*1)}

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

有老師在製作 Excel 的講義時,會需要用到顯示某些儲存格中的公式內容,以下使用兩種方式來呈現,希望對老師們的備課有幫助。(參考下圖)

Excel-將儲存格公式放在文字框中(FORMULATEXT)

(1) 使用 FORMULATEXT 函數

在儲存格D2中輸入公式:=FORMULATEXT(C2)

在儲存格D2中會顯示:=SUMPRODUCT(1*(A2:A14>=500))


(2) 將公式放在文字方塊中

你已經先在儲存格D2中輸入公式:=FORMULATEXT(C2)

然後新增一個文字方塊,在選取文字方塊的狀態下,於公式編輯列中輸入「=」,然後點選儲存格D2,公式編輯列會顯示「=$D$2」,按下 Enter 鍵。

Excel-將儲存格公式放在文字框中(FORMULATEXT)

文字方塊中即會顯示儲存格D2中的公式內容(可以再做格式設定):

Excel-將儲存格公式放在文字框中(FORMULATEXT)

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

1 2
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼