2015年11月30日 星期一

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

在 Windows 7 中大家已經習慣檢視檔案的詳細資訊,突然改用 Windows 10 之後,可能一時間看不到檔案的詳細資訊,而且這個詳細資訊顯示的位置也不一樣了。以下來比較一下。
在 Windows 7 要檢視檔案資訊,可以在[組合管理]功能表中的[版面版置]選項中勾選「詳細資料窗格」來切換。檔案的詳細資訊會顯示在視窗的最下方。
Windows7/10檢視和修改檔案的詳細資訊
如果你已在使用 Windows 10 系統,則可以直接在[檢視]功能表中的[窗格]區,點選「詳細資料窗格」。檔案的詳細資訊會顯示在視窗的右側。
Windows7/10檢視和修改檔案的詳細資訊
因為在 Windows 10 中『預覽窗格』和『詳細資料窗格』中共用同一個區域,所以只能擇一顯示。不過,在 Windows 7 中這兩者是同時顯示的。
Windows7/10檢視和修改檔案的詳細資訊

2015年11月29日 星期日

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

讀者問到:在 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資料檔並且篩選符合條件的列印項目
你要使用那一種方法,應該都是可以的,就讓你靈活運用吧!

2015年11月27日 星期五

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

大家有遇到這種現象嗎?例如:在 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

2015年11月26日 星期四

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

本範例是延續前三篇文章的研習講義:
這次來看看 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視為『二維陣列』,所以每三個為一列,其中以『;』作為分隔。

2015年11月24日 星期二

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

在 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 p="">
資料-INT(資料):取得每個日期的時數部分。(INT(資料)為日期所代表的值)
(資料-INT(資料)>=8/24)*(資料-INT(資料)<12 12="" 8="" nbsp="" strong="">AND
 運算,在運算過程式中將判斷結果的TRUE/FALSE 轉換為 1/0。再由 SUMPRODUCT 函數執行乘積和,把所有的 1 加總即為所求。
(2) 時段:12:00~16:00
儲存格G3:=SUMPRODUCT((資料-INT(資料)>=12/24)*(資料-INT(資料)<16 p="">
(3) 時段16:00~20:00
儲存格G4:=SUMPRODUCT((資料-INT(資料)>=16/24)*(資料-INT(資料)<20 p="">

【延伸討論】
如果你想要使用自動篩選工具,會發現其只能處理到『日期』,而沒有辦法處理『時間』。
Excel-計算某一時間區間內的數量(SUMPRODUCT,INT)
若使用自訂篩選也只能篩選日期:
Excel-計算某一時間區間內的數量(SUMPRODUCT,INT)

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

最近又要為校內同仁上 Excel 的研習課程,本篇為課程範例。參考先前範例:
本篇要介紹由一個學生升學資料(模擬),每一個學生的資料分成四列呈現,有數百名學生待處理。而要將姓名中間的一個字遮蔽,並且將多列資料轉換為多欄,再統計國立和非國立的人數。

(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,往下各列貼上。

2015年11月23日 星期一

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

網友根據這篇文章:Excel-計算10位元數以上的乘法,想要了解如果要模擬10位數字的『加法』運算,該如何處理?
如下圖,有二組10個數字組成的數,要執行相加的動作,如何將每個位數的結果模擬出來呢?
Excel-模擬10位數加法運算(MOD,INT)

【公式設計與解析】
(1)
儲存格K3:=MOD(SUM(K1:K2),10)
利用 MOD 函數求得兩數相加後除以 10 的『餘數』,該結果即為不包含進位數的和。
(2)
儲存格J3:=MOD(SUM(J1+J2)+INT(SUM(K1+K2)/10),10)
利用 INT 函數求得右邊兩個數字之和除以 10 的『商數』,將此商數併入這個位數的加總。再將加總結果以 MOD 函數取得除以 10 的『餘數』,該結果即為不包含進位數的和。
複製儲存格J3,貼至儲存格B3:J3。
(3)
儲存格A3:=INT(SUM(B1+B2)/10)
利用 INT 函數求得右邊兩個數字之和除以 10 的『商數』,此傳回值即為右側數字相加後的進位數。

【延伸練習】
如果對於模擬『乘法』運算有興趣者,可以參考這篇文章:Excel-計算10位元數以上的乘法
Excel-模擬10位數加法運算(MOD,INT)

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

最近又要為校內同仁上 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 函數傳回的儲存格位址,取得該位址中的儲存格內容。

2015年11月21日 星期六

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

在 Excel 做好一個圖表,也顯示了標籤,如果想要修改其中部分資料的標籤,讓它和其它不一樣,參考下圖,其中有二個資料項目的標籤和其他項目不同。該如何處理?
Excel-在圖表中自訂標籤文字
參考以下的操作步驟:
1. 選取一個已顯示的資料標籤,整個資料數列的標籤每一個都被選取。
2. 點選第 4 個資料標籤,目前只留下這個資料標籤被選取。
3. 在文字方塊中輸入你想要的文字。
4. 對第 10 個資料標籤上重複 1~3 的步驟。
Excel-在圖表中自訂標籤文字

2015年11月20日 星期五

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

在 Excel 中樞紐分析表是一個常用的工具,當我們利用一個資料表的內容產生了一個樞紐分析表後,可以看到一些摘要的結果(已運算後的結果),如何能看到這些摘要結果的細部運算狀況呢?
以下圖為例,當產生了一個樞紐分析表,如果想要看看公假這一欄的細項,你可以在總計欄位中的『11.0』上「按二下」,即會產生一個新的工作表。在這個工作表中會顯示這個欄位的細部內容。而這個新的工作表會進入篩選模式,你可以直接使用篩選的工具。
Excel-檢視樞紐分析表中的個別資料

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

在 Excel 中如果要將數字轉換成國字來表示,例如:『123456』轉換成『壹拾貳萬參仟肆佰伍拾陸』,是相當容易的事,只要在儲存格中設定數值格式即可。但是,如果你要在 Word 中操作呢?
參考下圖,如果要轉換數字為國字,則可以先選取數字,再選取[插入/符號]功能表中的「數字」。
Word-將文件中的數字轉成國字
在開啟的[數字]對話框中選取『壹, 貳, 參, …』項,即可以執行這個轉換工作。
Word-將文件中的數字轉成國字
要特別注意:
1.可轉換的最大數字為『999999』
2,轉換後的結果是一個功能變數,如果要進一步操作,可以在數字上按右鍵,選取想要的指令。
Word-將文件中的數字轉成國字

2015年11月19日 星期四

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

最近又要為校內同仁上 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 p="">
複製儲存格L5,貼至儲存格L5:L6。

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

有同仁問到:
在 Wrod 中處理文件時,如下圖的內容中,想要在已設定項目符號的段落中,為了要使某些文字對齊,所以先設定了定位點符號(參考下圖),當插入點移至下圖的第三列的第1個字,然後按一下 Tab 鍵,卻沒有產生內縮的結果,反而是執行預設之「增加縮排」的動作。
該如何解決呢?(要把第三列的起始位置移至冒號(:)後)
Word-在設定項目符號的段落中使用Tab鍵
答案很簡單:
改按 Ctrl+Tab 鍵即可,參考下圖(不解釋)。
Word-在設定項目符號的段落中使用Tab鍵

【延伸練習】
如果是在表格中使用 Tab 鍵,也是預設會向右向下跳至下一個儲存格中,所以如果要在表格的儲存格中使用 Tab 鍵,也是要改用 Ctrl+Tab 鍵。
Word-在設定項目符號的段落中使用Tab鍵

2015年11月18日 星期三

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

在 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)

【延伸練習】
如果要使用 INDEX 函數來設計,該如何設計?
儲存格I2:=INDEX($A$2:$F$24,MATCH($I$1,學號,0),MATCH(H2,$A$1:$F$1,0))
複製儲存格I2,貼至儲存格I2:I6。

2015年11月17日 星期二

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

在 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)

檢視其他文章

好康東東