贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201106 (42)

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

在 Excel 中取得幾個名字的字串(如下圖儲存格B1:B8),每個名字以「、」隔開,要如何取出姓名,並置於儲存格中呢?(參考下圖)

因為其中每個姓名都是三個字,所以根據這個規則撰寫公式:

儲存格B11:=MID(INDIRECT(ADDRESS(COLUMN(A:A),2)),(ROW(1:1)-1)*4+1,3)

COLUMN(A:A)往右複製,會產生COLUMN(A:A)=1→COLUMN(B:B)=2→ … →COLUMN(I:I)=8。

ROW(1:1)往下複製,會產生ROW(1:1)=1→ROW(2:2)=2→ … →ROW(20:201)。

ROW(1:1)-1)*4+1往下複製,可得1→5→9→ …。

透過INDIRECT函數將ADDRESS所得的位址(例如:ADDRESS(1,2))轉成「欄名列號」的位址(例如:$B$1)。

MID函數為每間隔4個字取出3個字(即為姓名)。

複製儲存格B11,貼至儲存格B11:I11;複製儲存格B11:I11,往下各列貼上。

很輕鬆的轉換一串名字到儲存格中。

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

在 Excel 中如果要計算以下各月的業績中超過40000的次數,只要使用COUNTIF函數即可輕易達到。

儲存格L2:=COUNTIF(F2:K2,$L$1)

複製儲存格L2,往下各列貼即可。

但是,如果1月到6月是分別置於命名為1月、2月、…、6月的工作表中,當使用COUNTIF函數會發生錯誤:

儲存格B2:=COUNTIF('1月:6月'!B2,$B$1)

試圖要將每個月的工作表(1月到6月)中的儲存格B2取出來計算,因為在COUNTIF中無法使用跨工作表的表示法,所以顯示錯誤訊息。

所以要改用不同的作法,例如:

儲存格B2:=SUMPRODUCT(COUNTIF(INDIRECT(ROW($1:$6)&"月!B"&ROW(2:2)),$B$1))

複製儲存格B2,往下各列貼上。

SUMPRODUCT函數可以將ROW($1:$6)&"月!B"&ROW(2:2)公式在往下各列貼上時,產生以下的陣列:

1月!B2、1月!B3、1月!B4、1月!B5、1月!B6、1月!B7、1月!B8、1月!B9、1月!B10、1月!B11

COUNTIF用以計算符合條件的格式。

因此,如果要計算符合條件的總和,可以將公式改為:

儲存格C2:=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$6)&"月!B"&ROW(2:2)),$C$1))

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

最近在Chrome Story網站(http://chromestory.com/2011/06/free-ebook-for-chrome-book-users/)推出了一個免費的Google Chrome的100個Tip,如果你是Google Chrome的愛用者,可以下載看看有那些好用的功能,你還不知道。

電子書網址:http://chromestory.com/100_Chrome_ChromeOS_And_ChromeBook_Tips_Ebook.pdf

其內容分為十大主題,共25頁:

Keyboard Shortcuts That Will Transform Your Browsing Style

Do A Lot More With Your Mouse !

Use Omnibox (addressbar) To Make Life Interesting & Easier

Tabs and Bookmarks Bar Tricks For You

Drag and Drop Tricks - Do Things in Style !

My Top 10 Extensions

10 Cool Chrome Themes !

Know These 10 Things, Go Pro !

Some Cool Chrome OS Tips For You !

Tips For Chromebook Users

再好用的Tip,如果無法適時使用,也是英雄無用武之地。善用要靠常用,好用也要多用,才能真正發揮效用。

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

在 Excel 中可以很方便的由一個數列中找出某數的排名,如何來找出某個排名的內容呢?

首先,將A欄和B欄有資料的儲存格分別命名為「編號」和「數列」。

(1) 找出某數在數列的排名

儲存格C2:=RANK(B2,數列) 

複製儲存格C2,往下貼在儲存格C2:C21。

或是

儲存格C2:=COUNTIF(數列,">"&B2)+1

其原理是找出本身儲存格之前有幾個比自己為大者。

(2) 找出某排名在數列中的數

現在要和(1)相反,由排名找出數列中的數,要注意會有同名次問題,所以試試以下的做法:

儲存格G2:=LARGE(數列,E2)

利用LARGE函數找出由大到小的數列排列。

儲存格F2:{=IF(COUNTIF($G$2:G2,G2)>1,SMALL(IF(G2=數列,編號,FALSE),COUNTIF($G$2:G2,G2)),SMALL(IF(G2=數列,編號,FALSE),1))}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格F2:G2,往下貼在儲存格F2:G21。

SMALL(IF(G2=數列,編號,FALSE),1)):利用陣列公式找出某數和數列相符所對應的編號,其結果類似:Fasle,False,2,False, …, Fasle的陣列,利用SMALL可以取出這個唯一的數值。

因為同一個數值有可能在數列中出現多次,所以以「COUNTIF($G$2:G2,G2)>1」來判斷,如果超過1個,則在SMALL函數中取第COUNTIF($G$2:G2,G2))個最小值(如果某數為第2個重覆,則(COUNTIF($G$2:G2,G2))=2)。

因為使用SMALL函數,所以相同數值中,編號較小者,會先被列出來,編號較大者會被較後被列出。

註:以上儲存格F2公式可以簡化為:

儲存格F2:{=SMALL(IF(G2=數列,編號,FALSE),COUNTIF($G$2:G2,G2))}

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

在 Excel 中可以有多種方式產生數列清單(1,2,3,…),例如以下三種以公式產生數列的方式:

第1種:利用將前一格儲存格值加1的方式,複製儲存格A2,往下貼上。

第2種:利用ROW()函數在複製公式時會自動加1的特性,複製儲存格B1,往下貼上。

第3種:計算前面儲存格有幾個數字,再加1,即為本儲存格的數值,複製儲存格C2,往下貼上。

如果我們做了刪除的動作,例如:將第5列刪除,則會發生以下的錯誤狀況(參考下圖):只有第3種維持數列的變化。

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

台北市公眾區免費無線上網,現在已級開放申請,無論你是否為台北市人,只要你在台北市內而且在其服務範圍內者,都可以申請。服務範圍以台北市主要公共場所為主,其中室內公共場所部分,包括市府市政大樓、12個區行政中心、市立圖書館及各分館、市立聯合醫院各院區、臺北捷運車站及捷運地下街等場所;室外公共場所部分,則包括本市主要幹道、主要住商區域及人口密集區之公共場所,將透過「熱點(Hotspot)」方式提供服務。

申請網址:http://www.tpe-free.taipei.gov.tw/newaccountstep1.php

申請時,需要先輸入行動電話號碼,便於以手機收取認證碼:

image

輸入手機簡訊中的認證碼:

再以設定密碼和指定一個Email即可完成申請。

詳細說明可參閱:http://www.tpe-free.taipei.gov.tw/faq.php

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

在 Excel 2010 中有進制轉換的函數:BIN2DEC、BIN2HEX、BIN2OCT、DEC2BIN、DEC2HEX、DEC2OCT等,非常方便好用。這次來自行以公式模擬將二進制、四進制、八進制轉成十進制,來練習SUMPRODUCT、INDIRECT等函數。

儲存格B2:=SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),2^(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))))

LEN(A2):取得儲存格A2中字串長度。(本例以字串長度6來練習)

INDIRECT("1:"&LEN(A2)):依儲存格A2中字串長度,產生一段列的範圍。

ROW(INDIRECT("1:"&LEN(A2)):例如儲存格A2中字串長度為6,則產生ROW(1:6)。

MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1):相當於MID(A2,1,1)、MID(A2,2,1)、…、MID(A2,6,1)。即可以將儲存格A2中的每一個字取出,變成一個6個元素的陣列。

2^(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))):可以產生2的5次方、2的4次方、…、2的0次方。

透過SUMPRODUCT函數,將儲存格A2中的第1個數X2的5次方、第2個數X2的4次方、…、第6個數X2的0次方,並且全部加總即為十進制的結果。

同理:

儲存格D2:=SUMPRODUCT(--MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1),4^(LEN(C2)-ROW(INDIRECT("1:"&LEN(C2)))))

儲存格F2:=SUMPRODUCT(--MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),8^(LEN(E2)-ROW(INDIRECT("1:"&LEN(E2)))))

詳細函數說明,請參閱微軟網站:

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

 

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

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

在 Excel 中有提供自訂清單功能,如果想要以公式產生想要的清單,該如何處理呢?

儲存格A2:=CHOOSE(MOD(ROW(1:1)-1,7)+1,"星期日","星期一","星期二","星期三","星期四","星期五","星期六 ")

MOD(ROW(1:1)-1,7)+1:根據不同列得到1,2,3…數字,透過MOD取得和變化量(本例為7)相除的餘數,即可產生1,2,3,4,5,6,7,1,2,3,4,5,6,7,…。

再透過CHOOSE乙數對照得到對應的一個字串。

同理:

儲存格B2:=CHOOSE(MOD(ROW(1:1)-1,10)+1,"甲","乙","丙","丁","戊"," 己","庚","辛","壬","癸")

儲存格C2:=CHOOSE(MOD(ROW(1:1)-1,12)+1,"一年一班","一年二班","一年三班","一年四班","一年五班","一年六班","一年七班","一年八班","一年九班","一年十班","一年十一班","一年十二班")

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

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

在 Excel 中取得一個物品進出料的記錄表,根據這個資料表來練習各種條件式加總的練習。

首先將A欄到E欄中有資料的儲存格,分別命名為:日期、經手人、進出、料號、數量。並將全部資料(儲存格A1:E27)命名為:資料。

(1) 計算經手人的經手次數

儲存格H2:=COUNTIF(經手人,G2)

複製儲存格H2,往下二列貼上。

(2) 計算進料/出料的小計

儲存格H7:=COUNTIF(進出,G7)

儲存格I7:=SUMIF(進出,G7,數量)

複製儲存格H7:I7,往下一列貼上。

(3) 依類別和料號計算進料/出料的小計

儲存格H11:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(進出=H$10)*數量)

儲存格I11:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(進出=I$10)*數量)

複製儲存格H11:I11,往下二列貼上。

MONTH(日期)=ROW(1:1),可以找出月份為1者,往下複製時ROW(1:1)=1 → ROW(2:2)=2 → …。

同理:

儲存格H16:=SUMPRODUCT((LEFT(料號,1)=$G16)*(進出=H$15)*數量)

儲存格I16:=SUMPRODUCT((LEFT(料號,1)=$G16)*(進出=I$15)*數量)

複製儲存格H16:I16,往下二列貼上。

LEFT(料號,1)=$G16:找出料號第1個字元為「A」者。

(4) 依日期查詢當天的進出料資料

儲存格H21:=INDEX(資料,MATCH($G21,日期,0)+1,COLUMN(C:C))

複製儲存格H21,貼至儲存格H21:J21。再複製儲存格H21:J21,往下二列貼上。

COLUMN(C:C)=3,向右複製COLUMN(C:C)=3 → COLUMN(D:D)=4 → COLUMN(E:E)=5。

MATCH($G21,日期,0)+1:找出日期在第幾列。

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

在 Excel 中取得一個通話的起迄時間表,而通話的前2分鐘免費,2分鐘至4分鐘為每12秒1元(不足12秒以12秒計),超過4分鐘的部分每6秒鐘1元(不足6秒以6秒計)。該如何計算全部的通話費用?

先算出免費的秒數、減價時段的秒數和正常計費的秒數,再運算無條件進位的函數來運算。

儲存格G3:=ROUNDUP(E3/12,0)+ROUNDUP(F3/6,0)

複製儲存格G3,往下各列貼上。

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

在 Excel 中根據一個班級基本表,在一個申請夜讀的報表中,自動查詢得到班級基本資料(如下圖)。試著使用INDEX、LOOKUP、VLOOKUP、OFFSET、MATCH、INDIRECT等函數來練習查表。

先定義一些名稱:

班級:儲存格A2:A29;導師:儲存格B2:B29;人數:儲存格C2:C29,資料:儲存格A1:C29。

以下各式都可以得到正確的結果,將儲存格F2和儲存格G2複製後,往下各列貼上。

(1) 使用LOOKUP函數

儲存格F2:=LOOKUP(E2,班級,導師)

儲存格G2:=LOOKUP(E2,班級,人數)

(2) 使用VLOOKUP函數

儲存格F2:=VLOOKUP(E2,資料,2)

儲存格G2:=VLOOKUP(E2,資料,3)

(3) 使用INDEX函數+MATCH函數

儲存格F2:=INDEX(資料,MATCH(E2,班級,0)+1,2)

儲存格G2:=INDEX(資料,MATCH(E2,班級,0)+1,3)

(4) 使用OFFSET函數+MATCH函數

儲存格F2:=OFFSET($A$2,MATCH(E2,班級,0)-1,1,,)

儲存格G2:=OFFSET($A$2,MATCH(E2,班級,0)-1,2,,)

(5) 使用INDIRECT函數+MATCH函數

儲存格F2:=INDIRECT("B" &MATCH(E2,班級,0)+1)

儲存格G2:=INDIRECT("C" &MATCH(E2,班級,0)+1)

相關函數詳細說明,請參閱微軟網站說明:

LOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010342671.aspx

陣列形式的 LOOKUP :在陣列的第一列或第一欄中搜尋指定的值,然後從陣列最後一列或最後一欄的相同位置中傳回值。

語法:LOOKUP(lookup_value, array)

lookup_valueLOOKUP 函數在陣列中搜尋的值。

array:此引數包含文字、數字,或您要與 lookup_value 比較的邏輯值之儲存格範圍。

注意:陣列中的值必須以遞增順序排列,如果 LOOKUP 函數找不到 lookup_value,就會使用陣列中小於或等於 lookup_value 的最大值。

VLOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

 

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

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

在 Google 的圖片搜尋(http://images.google.com/)已開始支援「以圖找圖」功能,當你要找一張圖有可能你完全不知道要使用什麼樣的關鍵字,例如:你看到一張漂亮的風景照、ICON(圖示)等。

以圖找圖讓你可以不需要輸入關鍵字,可以上傳圖片或是直接將圖片拖曳至搜尋框中(Google Chrome支援拖曳,IE並支援),即可自動進行搜尋。

搜尋引擎會自動辨識,並給予一個關鍵字,並且進行搜尋。你會看到它已經推測一個最有可能的關鍵字,並呈現一些最有可能的檔案。

稍微用以下的圖片做一下測試:

(1) 無法描述的圖片,利用以圖找圖可以找到一些相似的圖。

(2) 以前下載過的圖,已經忘記在那個網頁出現。利用以圖找圖功能,又可回到原來的網站了。

(3) 忘記名畫的名稱,仍可順利的找到相關資料。

(4) 不知道名字的明星,Google可以輕易的辨識。

但是你如果隨便丟個非公眾人物來搜尋,還是不容易找到相關資料,因為非公眾人物在網路上的圖片本來就少,而且也不易辨識。不然如果那天要來個人肉搜索,那不就容易多了嗎?

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

在 Excel 中可以利用LEFT、MID、RIGHT等函數,將一個字串中的文字加以重新排列組合(如下圖)。你可以取用這些文字在其他地方來使用。

例如:

儲存格A2:=MID($A$1,ROW(1:1),1)

儲存格B2:=MID($A$1,ROW(1:1),ROW(1:1))

儲存格C2:=LEFT($A$1,ROW(1:1))

儲存格D2:=RIGHT($A$1,ROW(1:1))

複製儲存格A2:D2,貼至儲存格A2:D15。

其中ROW(1:1)=1 → ROW(2:2)=2 → ROW(3:3)=3 → …。

關於函數的詳細說明,請參考微軟網站的說明:

LEFT:http://office.microsoft.com/zh-tw/excel-help/HP010342648.aspx

RIGHT:http://office.microsoft.com/zh-tw/excel-help/HP010342854.aspx

MID:http://office.microsoft.com/zh-tw/excel-help/HP010342690.aspx

ROW:http://office.microsoft.com/zh-tw/excel-help/HP010342861.aspx

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

在 Excel 中的活頁簿中,可能包含多個工作表,或許每個工作表有其特定的功能。如果能透過色彩來加以分組,或是以色彩來區隔其重要性等,將會在使用上帶來一些便利。

以 Excel 2010為例,如果要設定工作表索引標籤的色彩,參考以下步驟:

1. 在工作表名稱上按一下右鍵。

2. 在[索引標籤色彩]中挑選一個色彩。

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

在 Excel 中,如果要計算兩個時間(時:分:秒)的間隔(如下圖),該如何處理?

其實只要將兩個時間(時間Y和時間X)相減即可(參考D欄),例如:儲存格D2=C2-B2。你會發現當時間Y大於時間X時,這個公式會產生錯誤訊息。解決的方式很簡單:

儲存格E2:=IF(C2>B2,C2-B2,C2+1-B2)

因為以24小時來看,後者的時間小於前者時,應該是已經進入下一天的時間了,而Excel將1天切割成24小時,所以每小時為1/24;每小時再切割成60分,所以每分為1/24/60;每分再切割成60秒,所以每秒為1/24/60/60。

所以只要將後者的時間小於前者的部分,先加1再相減即可。

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

在 Excel 中取得某個月份的圖書借閱資料(如下圖,資料為虛擬),根據這些資料來做一些統計與分析的練習。

先選取所有資料範圍,按一下 Ctrl+Shift+F3 鍵,選取「勾選頂端列」選項。定義「日期、登錄號、書名、班級、借書證號」等名稱。並且定義全部資料的儲存格範圍的名稱為「資料」。

(1) 計算各班及各天借閱次數

各班借閱次數

儲存格H2:=SUMPRODUCT(--(班級=G2)),複製儲存格H2,貼在儲存格H2:H16。

各天借閱次數

儲存格H19:=SUMPRODUCT(--(日期=G19)),複製儲存格H19,貼在儲存格H19:H49。

(2) 使用「篩選」功能

在 Excel 的自動篩選中提供了「等於、不等於、大於或等於、小於、小於或等於、開始於、結束於、包含、不包含」等條件設定,並且可以使用兩個條件的邏輯 AND 和 OR 運算,並且可以使用「*、?」等萬用字元。

<A> 找出書名含有「傳說」的借閱記錄

篩選條件:包含「傳說」和篩選條件:等於「*傳說*」的篩選結果是一樣的。

也可以使用進階篩選來做到:

如果你想要挑選指定日期和班級的篩選資料,可以使用進階篩選,並且將兩個條件寫在同一列,即可執行邏輯AND運算的篩選。

以下的例子為篩選「(日期=05-06-200 AND 班級=三年六班) OR (日期=05-09-200 AND 班級=三年四班)」的資料。

將條件寫在同一列執行的是邏輯AND的運算,寫在不同列執行的是邏輯OR的運算。

相關函數說明,請參閱微軟網站:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

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

在 Excel 中有一個物品維修的記錄表(如下圖),由於報表跨越數年,如何指定只列出某年、某月的記錄呢?

首先,要先定義一些「名稱」:

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

(2) 按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,按一下[確定]按鈕。

可得「編號、班級、項目、報修日、完成日、損壞原因」等名稱。

(3) 選取所有有資料的儲存格,在[名稱管理員]中新增名稱:「資料」。

在儲存格H2和儲存格I2中,利用「資料驗證」方式,讓年和月可以使用清單方式挑選。

儲存格J2:{=IFERROR(INDEX(資料,SMALL(IF((YEAR(報修日)=$H$2)*(MONTH(報修日)=$I$2),編號,FALSE),ROW(1:1)),2),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製這個儲存格,往下各列貼上。

(A) IF((YEAR(報修日)=$H$2)*(MONTH(報修日)=$I$2),編號,FALSE):取得符合指定年和月的「編號」陣列。公式中的「*」為執行邏輯「AND」的運算,即兩個條件須都符合時,條件才成立。

(B) SMALL((A),ROW(1:1):取得編號陣列中第1小者,往下複製公式時,可以取得第2小者,依此類推。

(C) INDEX(資料,(B),2):根據編號陣列中的編號和第2欄(班級欄)的交點,即為班級名稱。

(D) IFERROR((C),""):如果公式結果有錯(找不到對應的值),即以空字串顯示,避免顯示錯誤訊息。

同理可以建立以下公式:

儲存格K2:{=IFERROR(INDEX(資料,SMALL(IF((YEAR(報修日)=$H$2)*(MONTH(報修日)=$I$2),編號,FALSE),ROW(1:1)),3),"")}

儲存格L2:{=IFERROR(INDEX(資料,SMALL(IF((YEAR(報修日)=$H$2)*(MONTH(報修日)=$I$2),編號,FALSE),ROW(1:1)),6),"")}

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

一個老師將學生分成多組輪流上台報告,每個學生都要為各組給一個分數,而自己所在的組別不評分,每組成員的分數是相同的,都是全班其他組所給分數的平均。如何來設計這個表格呢?(參考下圖)

儲存格J2 :=AVERAGE(OFFSET($D$2,,$C2-1,COUNT($C$2:$C$25),))

複製儲存格J2,往下貼在儲存格J2:J25。

COUNT($C$2:$C$25):計算所有人員共有幾列。

OFFSET($D$2,,$C2-1,COUNT($C$2:$C$25),):取得各組的位址,例如第1組為儲存格D2:D25,第2組為E2:E25,…。

再使用AVERAGE函數計算平均。

現在,要利用格式化規則來設定自己組別的儲存格為較深的綠色,以提醒輸入成績時不要輸入。

先選取儲存格D2:I25,設定格式化規則為:

選取「使用公式來決定要格式化哪些儲存格」,輸入公式:「=$C2=COLUMN(A:A)」,格式為較深的綠色。

如果還是怕不小心輸入,則可以使用「資料驗證」方式,將不小心輸入的數值,顯示錯誤訊息(如下圖)。

先選取儲存格D2:I25,設定資料驗證為:

儲存格允許:自訂,公式:=$C2<>COLUMN(A:A)

並設錯誤提醒訊息,如下所示:

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

在 Excel 中取得一個物品領用的數量統計表,現在要利用這個資料表以公式運算方式列出(1)整年領取數為0者,(2)每個月都有被領取者。

因為需要用到一個「輔助」欄位,所以先輸入公式:

儲存格Q2:=COUNTIF(D2:O2,"<>0"),複製儲存格Q2,往下各列貼上。

將A欄有資料的部分定義名稱為「編號」;將B欄有資料的部分定義名稱為「請領物品」;將P欄有資料的部分定義名稱為「小計」;將Q欄有資料的部分定義名稱為「輔助」。

(1) 整年領取數為0者

儲存格S2:{=IFERROR(SMALL(IF(小計=0,編號,FALSE),ROW(1:1)),"")}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格S2,往下各列貼上。以下公式做法雷同。

(A) IF(小計=0,編號,FALSE):得到小計為0者的編號陣列。

(B) SMALL((A),ROW(1:1):取得(A)陣列中的最小值(最小的編號)。往下複製時ROW(1:1)=1 → ROW(2:2)=2 → ‥‥,可取得第2小、第3小‥‥的編號

(C) IFERROR((B),""):因為當找不到編號時會傳回錯誤值,所以藉由IFERROR函數,將其顯示為空白。

儲存格T2:{=IFERROR(LOOKUP(SMALL(IF(小計=0,編號,FALSE),ROW(1:1)),編號,請領物品),"")}

原理同上,再將編號利用LOOKUP函數以查表方式找到對應的「請領物品」名稱。

(2)每個月都有被領取者

試著根據(1)的做法,藉助「輔助」欄位,練習每個月都有被領取者。

儲存格V2:{=IFERROR(SMALL(IF(輔助=12,編號,FALSE),ROW(1:1)),"")}

其先找到輔助欄位為12者(12個月都不為0)的陣列,再取出其編號。

儲存格W2:{=IFERROR(LOOKUP(SMALL(IF(輔助=12,編號,FALSE),ROW(1:1)),編號,請領物品),"")}

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

在 Excel 中取得一串資料(如下圖A欄),因為每筆資料中包含了「"」字元,所以當你使用 Excel 的排序工具時,將會變成2" → 21" → 3" → ‥‥,而非2" → 3" → ‥‥。如何解決這樣的問題呢?

這是因為 Excel 將 2" 視為文字而非數字來排序所造成的問題。

先將儲存格A2:A21定義名稱為「資料」

儲存格C2:{=SMALL(VALUE(SUBSTITUTE(資料,"""","")),ROW(1:1))&""""}

這是陣列公式,輸入完成時要按 Ctrl+Shift6+Enter 鍵。複製儲存格C2,貼至儲存格C2:C21。

SUBSTITUTE(資料,"""",""):將資料中的「"」字元消除。

VALUE(SUBSTITUTE(資料,"""","")):將消除「"」字元的文字轉成數字。

利用ROW(1:1)=1、ROW(2:2)=2、‥‥,透過SMALL函數來取得第1,2,3,‥‥的數值,由上而下即為排序的結果。

相關函數的詳細說明,請參閱微軟網站:

SUBSTITUTEhttp://office.microsoft.com/zh-tw/excel-help/HP010342927.aspx

SUBSTITUTE:將文字字串中的 old_text 部分以新字串 new_text 取代。

語法:SUBSTITUTE(text, old_text, new_text, [instance_num])

text:要取代字元的文字,或含有該文字之儲存格的參照。

old_text:這是要取代的文字。

new_text:是要用來取代 old_text 的文字。

instance_num:指定要將第幾個 old_text 取代為 new_text

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

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼