贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201404 (22)

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

有網友問到:如果一個工作表中的資料項目含有文字和數字(參考下圖),如何排除其中的文字,並且把同類的項目予以計算加總?

【準備工作】

選取儲存格A1:A20,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目。

儲存格D2:=SUMPRODUCT((RIGHT(項目,3)=C2)*(VALUE(LEFT(項目,LEN(項目)-3))))

RIGHT(項目,3)=C2):取出項目陣列中的最右側三個字元,判斷是否和儲存格C2相同,結果得到一個 TRUE/FALSE 的陣列。

LEFT(項目,LEN(項目)-3)):取出項目陣列中排除最右側三個字元所剩的字元,結果為一個數字字元形成的字串陣列,例如:"86","92","20","147","166", … 。

VALUE(LEFT(項目,LEN(項目)-3))):將上式所得數字字元形成的字串陣列,透過 VALUE 函數轉換成數字陣列。

最後透過 SUMPRODUCT 函數,將 TRUE/FALSE 陣列和數字陣列,執行乘積和,即為所求。在運算過程中, TRUE 會被視為 1,FALSE 會被視為 0。本例為:

TRUE*86+TRUE*90+FALSE*20+FALSE*147+TRUE*166+…

=86+90+166+…

複製儲存格D2,貼至儲存格D2:D4。

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

有網友問到在 Excel 中如何用公式去除儲存格中的「" "」字元?

一般你要消除儲存格中的某些字元,可以使用「尋找/取代」的操作,用手動方式將儲存格中的特定字元置換為空白。或者你也可以用 SUBSTITUTE 函數來執行取代的動作,參考另一篇文章:http://isvincent.pixnet.net/blog/post/38989827

但是如果你要置換的字元是「" "」,在你使用 SUBSTITUTE 函數時將會遭遇問題,因為一般要表示字元(或字串)時,要在字元(或字串)的前後,以「" "」字元含括,Excel 將會認定「"""」這樣的寫法是錯的。

不過,仍有解決的方法,即是將「"」取其 ASCII 碼(34),代入 CHAR 函數,CHAR(34) 相當於「"」。

儲存格B2:=SUBSTITUTE(A2,CHAR(34),"")

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

 

【補充資料】

所有字元 ASCII 碼的 10 進制和 16 進制表示,請參考下表:
(詳細資料可參見:http://zh.wikipedia.org/wiki/ASCII)

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

有網友想要詢問:在 Excel 的工作表中處理資料時,能夠使用公式來處理變動的儲存格範圍。例如下圖中的各欄資料可能會有所增/刪,該如何處理呢?

一般當你計算加總時,例如儲存格F2:=SUM(A2:A18)

當你在Data1的欄位資料中刪除或是插入一筆資料時,公式=SUM(A2:A18)會跟著調整,可是如果你新增的資料是在最後一筆以外的位置,則 SUM 公式中的儲存格範圍,不會自動調整,該如何使用公式讓他可以自動調整呢?

你可以試試 OFFSET 函數和 INDIRECT 函數,假設每欄資料不會超過999列:

(1) 儲存格F2:=SUM(OFFSET(A2,0,ROW(1:1)-1,COUNTA(A2:A999),))

COUNTA(A1:A999):計算 Data1 中含有數字的儲存格個數。

透過 OFFSET 函數配合 COUNTA(A2:A999) 的結果,即可達到變動儲存格範圍。

(2) 儲存格F2:=SUM(INDIRECT("A2:A"&COUNTA(A2:A999)+1))

使用 INDIRECT 函數配合 COUNTA(A2:A999) 的結果,即可達到變動儲存格範圍。


【自行練習】

修改公式,求出 Data2 和 Data3 的總和!

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

Google 地圖最近推出了「街景時光機(Time Machine)」功能,讓你可以探索過往年代的街景,隨著街景翻拍的次數愈多,則可以檢索的年代也愈多。這個功能對於研究歷史或是想要了解自己的環境變遷時,是十分有幫助的。

一棟建築蓋起來前後的街道樣貌、地震/水災/火災等意外發現前後的環境有那些改變、政府在環境上做了那些造景的改變,街道上人們的穿著有何不同,那個年代流行那些衣服造型,大家在那個年代都在開那些車…,有趣的應用待你去開發。

當你進入Google地圖(https://www.google.com.tw/maps),並且進入街景檢視服務後,在視窗左上角會顯示一個新功能。按下時鐘圖示:

在本例中,你可以看到從 2009 年至 2012 年的時間軸,同一地點過去是一棟蓋到一半被荒廢的建築,點選時間軸中的不同年代,你可以窺視這些年來的街景變化:

當你點選某個年代中的照片時,地圖中的街景會切到該年代的街景,而且左上角的縮圖會變成現今的街景,讓你做對照:

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

回答網友提問:在下圖中,如何讓每一欄的總和為4(有四個1)時,底色變為粉紅色?這一定得藉助「設定格式化的條件」來處理!

1. 選取儲存格A1:I4。

2. 選取[常用/樣式/設定格式化的條件]中的「新增規則」。

3. 選取「使用公式來決定要格式化哪些儲存格」,並輸入公式:=SUM(A$1:A$4)=4。

(該公式是對儲存格A1來設定,Excel 會自動複製公式至儲存格A1:I4,所以欄採用相對參照位址,列採用絶對參照位址。)

4. 設定格式:儲存格底色為粉紅色。

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

有網友問到:在一個資料清單中,如果同一個內容出現多次,如何下公式全部找出來?因為其使用 VLOOKUP 函數,每次都只是列出相同內容的第一筆,有沒有其他方法可以使用呢?(參考下圖)

【準備工作】

選取儲存格A1:A9,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號。


假設:要搜尋的內容置於儲存格A11中,要把所有相同內容的儲存格依序列出。

【輸入公式】

儲存格A13:{=IFERROR(OFFSET($A$1,SMALL(IF(編號=$A$11,ROW(編號),FALSE),ROW(1:1))-1,COLUMN(A:A)-1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

IF(編號=$A$11,ROW(編號),FALSE):找出編號陣列中和儲存格A11相同內容的儲存格陣列。本例可得陣列:{2,3,4,5,Fasle,False,False,False,False}

SMALL(IF(編號=$A$11,ROW(編號),FALSE),ROW(1:1)):因為 ROW(1:1)=1,所以結果為2。往下複製時,ROW(1:1)→ROW(2:2)=2,會傳回3,依此類推。

利用 OFFSET 函數以相對位址取得以儲存格A2為起始的相對儲存格,即為所求。

再使用 IFERROR 函數,將查不到資料所傳回的錯誤訊息 #NUM!,以空白顯示。

複製儲存格A13,貼至儲存格A13:D20。

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

回答網友提問:如何在一個清單中(參考下圖),在不同項目類別之間自動加入分隔線,而且在新增資料後,正確分隔不同類別。

大家可能都很熟悉要利用「設定格式化的條件」來達到這個效果。

1. 選取儲存格A1:C9。

2. 選取「常用/設定格式化的條件」中的「新增規則」。

3. 選取「使用公式來決定要格式化哪些儲存格」。

4. 在[編輸規則]對話框中輸入「=$B2<>$B3」。

5. 設定格式為:儲存格下底線為紅色。(參考下圖)

如此便完成了設定。但是如果你新增了一列,填入資料後發現,雖然相同項目,卻在中間也出現了分隔線。

檢查一下設定格式化的條件規則,原來規則中自動產生的公式變得不一樣了。(以儲存格B8為例)

所以,你得藉助「複製格式」按鈕,將其他儲存格的格式複製到新增的儲存格上,才能正確的顯示分隔線。

這樣就正確了!(參考下圖)

網友們也來想想是否可以有不用再複製格式的方法呢?

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

回答網友提問:如果在一個日期清單中,要找出每年日期介於4/1~8/1之間者,依年度的增量來修正(下圖右),結果如下圖左,該如何在 Excel 中設定公式?

(1) 使用 CHOOSE 函數

儲存格C2:=B2+(A2>=DATE(YEAR(A2),4,1))*(A2<DATE(YEAR(A2),8,1))*CHOOSE(YEAR(A2)-1998,1,5,4,2,3,4,2,1,6,7,5)*100

(A2>=DATE(YEAR(A2),4,1))*(A2<DATE(YEAR(A2),8,1)):判斷日期是否介於4/1~8/1,結果為 (TRUE/FALSE)*(TRUE/FALSE),依 AND 運算結果,TRUE=1、FALSE=0。

CHOOSE(YEAR(A2)-1998,1,5,4,2,3,4,2,1,6,7,5)*100:取出儲存格A2中的年份,減掉1988,得到一個數字,對照 CHOOSE 的選項結果,再將此結果乘以 100。

以上結果即可得一個修正值。

(2) 使用 VLOOKUP 函數

儲存格C2:=B2+(A2>=DATE(YEAR(A2),4,1))*(A2<=DATE(YEAR(A2),8,1))*VLOOKUP(YEAR(A2),$E$2:$F$12,2,FALSE)

本公式改以 VLOOKUP 函數,以查表方式來求得符合對照表陣列中的數值。

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

最近因為微軟的 Office OneNote 2013 開放「免費下載」,大家都把焦點放在數位記事本和雲端同步等概念上。不過,我常介紹 OneNote 中有一個辨識圖片中文字的功能給學校老師用來做講義,它真是個好幫手!

假設你已安裝好微軟的 OneNote,參考以下的範例說明:

1. 先掃描某一頁紙本講義的內容(請使用時自行注意尊重智慧財產權!),我們要取出某一段內容做為個人講義的一部分:

(如果沒有掃描器,你可以使用智慧型手機或是相機來拍紙張,但要注意控制手不要晃動)

2. 將掃描檔拖曳至 OneNote 的一個新的筆記中。

3. 在圖片中按一下右鍵,選取「複製圖片的文字」。

4. 新增 Word 文件檔,貼上(Ctrl+V)剛才複製的文字:(你會發現,有少許的字辨識沒有成功,例如:「時」被辨識成「日寺」、「P1」被辨識成「PI」等)

5. 稍加核對、修改文字、畫上表格、剪貼表格中的圖解部分,一下就做出和原圖十分接近的講義了。

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

回答網友提問:如果想要在以下表格的資料中,根據「貨物號碼」最後二碼是否為「99」,如果是就給予售價加上 99,否則維持原售價。

我們試著以 RIGHT 函數取出字串的最後二碼,要注意此時取出的數字被視為文字。

在此提供二種公式運算方式,不同的運算觀念,相同的運算結果:

(1) 儲存格E2:=(RIGHT(C2,2)="99")*99+D2

RIGHT(C2,2)="99":判斷是否儲存格C2未二碼為「99」,得到一個 TRUE/FALSE 的結果,在運算過程中會轉為 1/0

(2) 儲存格E2:=IF(RIGHT(C2,2)="99",99+D2,D2)

這個運算公式是大家比較熟悉的。

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

有人想要知道如果要求取兩個數的最小公倍數和最大公因數,如何使用 Excel 的公式來取得。(參考下圖)通常,你可以透過LCM和GCD兩個函數來直接取得結果,現在我們用公式來模擬這兩個函數的結果。

我參考了一些人的做法,提供建議的公式:

(1) 最小公倍數

儲存格C2:

{=MIN(IF(MOD(A2*ROW(INDIRECT("1:"&B2)),B2)=0,ROW(INDIRECT("1:"&B2))))*A2}

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

(2) 最大公因數

儲存格D2:

{=MAX(IF((MOD(A2,ROW(INDIRECT("1:"&MAX(A2,B2))))=0)*(MOD(B2,ROW(INDIRECT("1:"&MAX(A2,B2))))=0),ROW(INDIRECT("1:"&MAX(A2,B2))),0))}

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

或許你可以找到更簡捷的公式,或更容易理解的公式,歡迎提供分享。

其中用到數個函數的說明,可以參考另一篇文章,這裡有所有函數在微軟網站提供的說明:http://isvincent.pixnet.net/blog/post/31397140

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

如果你要產生一個十六進制的數列,在 Excel 2010 以上版本,只要使用 DEC2HEX 函數即可產生,例如:

儲存格A2:=DEC2HEX(ROW(1:1)-1)

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

參考下圖左,如果你要產生的是兩位數的十六進制(第一碼可能為0)或是你的 Excel 版本無法使用 DEC2HEX 函數,則可以自行以公式來產生:

儲存格A2:

=CHOOSE(MOD((ROW(A1)-1)/16,16)+1,0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F")
&CHOOSE(MOD(ROW(A1)-1,16)+1,0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F")

MOD(ROW(A1)-1,16)+1:用以產生最小位元的十六進制數。

MOD((ROW(A1)-1)/16,16)+1:用以產生最大位元的十六進制數。(參考下圖右)

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

有學校同仁問到:如何在 Excel 的工作表(或是 Word )中直接產生條碼,在螢幕上或是列印後可以直接使用?例如下圖中的號碼,要產生一組對照的條碼。

要在文件中產生條碼,最簡便的方便是使用「條碼字型」。網路上有免費的條碼字型可以下載,將下載的條碼字型安裝在 Windows 的「fonts」資料夾之下,即可使用。

(要搜尋條碼字型可以在 Google 上搜尋「barcode 字型」)

以下載的「3 of 8 Barcode」字型為例,在使用時必須在號碼的前後加上「*」:

儲存格B2:="*"&A2&"*"

再設定儲存格字型為「3 of 9 Barcode」即可。

如果你有條碼掃描器(或是手機APP)即可掃描螢幕或是列印後掃描紙張:

這是以 APP 掃描後的結果:

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

有網友想要了解如何在一個資料表中,刪除固定間隔的那幾列?方法有數種,這次用幾個步驟來刪除固定間隔列。假設要刪除間隔 3 的那幾列,例如第 3, 6, 9, … 列。

1. 在原始資料(下圖左)中插入一輔助欄(本例為A欄),並在儲存格A1和儲存格A2填入「*」,儲存格A3留下空白。

2. 選取儲存格A1:A3,複製後在以下各列貼上。(如下圖右)

  

3. 選取儲存格A1:A23,按一下 Ctrl+G 鍵,開啟「到」對話框。

4. 按一下「特殊」按鈕。

5. 在[特殊目標]對話框中選取「空格」,按一下[確定]按鈕。

 

6. 在被選取的空白儲存格上按一下右鍵,選取「刪除」指令。(如下圖左)

7. 在[刪除]對話框中選取「整列」,按一下[確定]按鈕。

結果如下圖右,再把輔助欄刪除即可。

 

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

回答網友提問:如何在一個日期清單中(如下圖左),計算所有日期中各年的個數(如下圖右)。

儲存格D2:=SUMPRODUCT((YEAR($A$2:$A$25)=C2)*1)

YEAR($A$2:$A$25):在日期陣列中利用 YEAR 函數取出年的部分,組成年的陣列。

(YEAR($A$2:$A$25)=C2)*1:判斷年的陣列是否等於儲存格C2的內容,得到 TRUE/FALSE 組成的陣列,經由「*1」運算,變為 1/0 陣列。

再透過 SUMPRODUCT 函數,將上述 1/0 陣列計算總和,即為所求。

複製儲存格D2,貼至儲存格D2:D6。

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

回答網友提問:在 Excel 中有一個如下圖左的資料表,如何篩選出符合二個條件的資料(如下圖右)?

【準備工作】

選取儲存格A1:E26,按 Ctrl+Shift+F3 鍵,定義名稱:項目、類別、編號、數量、狀態。

選取儲存格A2:E26,進入名稱管理員中,定義名稱:資料。

【題目要求】

要篩選資料的條件置於儲存格G2和儲存格G4,必須符合二個條件者,才能被篩選。


【輸入公式】

儲存格H2:{=IFERROR(INDEX(資料,SMALL(IF((類別=$G$2)*(狀態=$G$4),項目,FALSE),ROW(1:1)),3),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

(類別=$G$2)*(狀態=$G$4):當你有二個條件時,可以使用運算子「*」來執行邏輯 AND 的動作。

IF((類別=$G$2)*(狀態=$G$4),項目,FALSE):若二個條件都成立時(AND結果為TRUE),則傳回項目的編號,例如第 7 列符合以上二個條件,則傳回項目 6。否則,傳回 FALSE。此結果形成了一些編號和 FALSE 的陣列。

SMALL(IF((類別=$G$2)*(狀態=$G$4),項目,FALSE),ROW(1:1)):傳回上述項目中最小的編號,此即為二個條件都符合者的項目編號。其中 ROW(1:1)=1,當公式往下複製時,會產生 ROW(2:2)=2、ROW(3:3)=3、…,可以分別得第2小的值和第3的值…。

透過 INDEX 函數在「資料」的陣列中找到對應的值。

IFFERROR 函數乃在當公式查不到資料時會傳回錯誤訊息,將這個錯誤訊息改以空白顯示。

儲存格I2:{=IFERROR(INDEX(資料,SMALL(IF((類別=$G$2)*(狀態=$G$4),項目,FALSE),ROW(1:1)),4),"")}

本公式的原理同上。

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


【延伸思考】

如果要篩選的條件變為三個、四個時,該如何處理呢?

只要將條件改為「(條件一)*(條件二)*(條件三)*(條件四)」即可。

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

你的手機有使用 Google Now 嗎?個人覺得這是個滿實用的工具,如果想要找尋附近的加油站,只要對著手機喊一下「加油站」,即會列出所在位置附近加油站的相關資訊(下圖左)。它也會提供一些你個人常會用到的小卡片,例如:行車時間等。(下圖右)

 

當然,你最常做的是拿它來搜尋,就好像連到 Google 網站一樣,會列出搜尋結果。不過,它的搜尋功能不只在網際網路上而已,它也可以搜尋手機內部的資料喔!

點選 Google Now 視窗右下角的設定圖示,在[設定]頁面下,點選[手機搜尋]:(下圖右)

 

勾選你要列為搜尋目標的項目,例如:我要它搜尋 Evernote、Play 音樂、聯絡人等 的資料,就在想要的項目上勾選:

 

當你輸入關鍵字時,在視窗最下方切換至「手機」,你就可以看到在手機上搜尋到的結果了:

 

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

最近見到同仁使用 Word 時,遇到的少許困擾,或許你也遇到過。我是這樣解決的:

(1) 當表格位於頁面第一行時,如何插入一行空白行

請將插入點移至表格的第一個儲存格:

按一下 Enter 鍵,即可在表格外插入一個空白行:

(2) 將兩個表格合併為一個

將插入點移至第一個表格的最後一個儲存格之外(最後一個段落記號)處:

按一下 Delete 鍵,即可將兩個表格合併為一個:

(3) 當表格剛好在頁面的最後位置時,如何消除跨面多出的一個空白行

當表格剛好在頁面的最後位置時,Word 無法消除表格後的一個空白行,所以只要將這一行設定較小的行距即可解決。

例如:將這一行的行高設定為固定 4 點:

如此,便不會因為多一行而產生多的一頁。

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

先前一段時間 Evernote 就推出了 PC 版中為圖片加上註記的功能,而且不需要安裝任何其他程式。這是一個實用的功能,直接在圖片中標記文字或圖形,可以增加筆記的實用性。

操作相當簡單,當你在 Evernote 的筆記中插入一張圖片,將滑鼠移至圖片上,右上角會顯示「註記」:

點選「註記」,即可進入註記的編輯模式。視窗左側有多種工具列可以使,操作完成時可以按一下視窗右上角的「done」。

註記的編輯工具有以下幾種:

當你編輯註記完成時,回到筆記時,註記的內容已經和圖片整合在一起,如果再次進入註記編輯模式,則先前的註記內容亦可以再次修改和新增/移除。

如果你試著儲存格這個被註記過的圖片,則註記內容會和原圖片合併成一張圖片:

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

回答網友提問:當資料分處於多個工作表中(例如:99年、100年、101年、102年),如果想要在某一個工作表中查詢各個工作表中的資料,該如何處理。當然各個工作表的格式都是一樣的!(參考以下四個圖)

現在來練習將其他工作表中的資料整合在一個工作表中,如下圖:

因為會使用到工作表的名稱,所以運用 INDIRECT 函數來將工作表名稱轉換為位址,一個公式來取得多個工作表中的多個儲存格資料。

【輸入公式】

儲存格B2:=INDIRECT($A2&"!"&ADDRESS(2,COLUMN(B:B)))

ADDRESS(2,COLUMN(B:B):結果為ADDRESS(2,2),傳回儲存格B2,如果將公式向右複製,COLUMN(B:B)→COLUMN(C:C),則會變為ADDRESS(2,3),傳回儲存格C2,依此類推傳回儲存格D2、儲存格E2、…。其中 ADDRESS 函數中使用參數 2,表示取用各個資料表中第 2 列的內容,

要取用工作表中的某一個儲存格,其公式的格式為「工作表名稱!儲存格」,所以$A2&"!"&ADDRESS(2,COLUMN(B:B))可以取得"99年"&"!"&B2,代入 INDIRECT 函數,即可傳回「99年工作表儲存格B2」的內容。

複製儲存格B2,貼至B2:G5。

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

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼