贊助廠商

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

搜尋本部落格文章資料

目前分類:講義資料 (2171)

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

教學上,我常使用 Evernote 來做研習課程的講義,通常只要透過共用指令,取得一個URL,即可將一個記事轉換為網頁,並且使用瀏覽器來檢視這個記事。

這是 Evernote 上的記事,必須透過 Evernote 軟體來檢視:

這是網頁上的記事,可以用瀏覽器在任何地點檢視:

現在 Evernote 的更新版本,已可以在 Evernote 上直接來播放簡報了。只要開啟這個記事後,點選工具列上的「簡報」:

Evernote 的視窗會轉為全螢幕,滑鼠游標會變成彩帶般,在此模式下來播放簡報:

也可以點選一張圖片,進入圖片檢視模式,:

將滑鼠移至螢幕左右兩側,即可切換上一張/下一張圖片:

如此一來,即可透過 Evernote 軟體直接將記事當成簡報來播放了,只是目前播放功能還很陽春,期待播放效果會愈來愈好。

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

母親節剛過,有人問到如何在 Excel 的工作表中列出每年母親節的日期?

因為母親節是固定在5月的第2個星期日,它是個明確且固定的規則,所以只要使用公式,即可輕鬆取得每年的母親節日期。(參考下圖)

儲存格B2:=DATE(A2,5,1)+14-WEEKDAY(DATE(A2,5,1),2)

DATE(A2,5,1):取得儲存格A2所代表年份的5月1日的數值,例如:2014/5/1的數值為41760。

WEEKDAY(DATE(A2,5,1),2):取出該年5月1日為星期幾的數值,在此使用參數「2」,代表傳回值和星期幾的對照關係為傳回1表示星期一、…、傳回7表示星期日。

14-WEEKDAY(DATE(A2,5,1),2):計算第二個星期日距5月1日的天數。本例為:10

將上面二個式子的結果相加即為所求,例如: 41760+10 = 41770,即為 2014/5/11。

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

 

【同場加映】

你也來練習把某一年每個月第二個星期日的日期通通找出來?

儲存格B3:=DATE($B$1,ROW(1:1),1)+14-WEEKDAY(DATE($B$1,ROW(1:1),1),2)

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

有網友問到,在一個 Excel 的資料表中含有日期和數量的清單,如何分年分月的統計加總結果?(參考下圖)

【準備工作】

選取A欄和B欄中含有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數量。

【輸入公式】

本例可使用 SUMPRODUCT 函數執行多條件 AND 結果的加總運算。

儲存格E2:=SUMPRODUCT((YEAR(日期)=E$1)*(MONTH(日期)=ROW(1:1))*數量)

YEAR(日期)=E$1:條件一,判斷日期陣列中每個日期的「年份」是否和儲存格E1相同,結果傳回 TRUE/FALSE 陣列。

MONTH(日期)=ROW(1:1):條件二,判斷日期陣列中每個日期的「月份」是否和ROW(1:1)(=1)相同,即為一月。如果公式往下複製時,ROW(1:1)=1 → ROW(2:2)=2 → ROW(2:3)=3 …,如此可以判斷每一個月。

將上述二個條件的 TRUE/FALSE 的結果相乘,再乘以「數量」的陣量,然後加總結果,即為所求。

複製儲存格E2,貼至儲存格E2:J13。

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

有網友問到,在 Excel 中如何產生數個固定星期幾順序的數列,例如星期二三五日。(參考下圖)

首先在儲存格A2中,先輸入第一個日期(必須為星期二三五日其中之一)。

接著在儲存格A3中輸入公式:

儲存格A3:=A2+VLOOKUP(WEEKDAY(A2,2),{2,1;3,2;5,2;7,2},2,FALSE)

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

WEEKDAY(A2,2):參數2乃指定傳回傳星期一到星期日對應為1到7。

image

{2,1;3,2;5,2;7,2}:在 VLOOUP 函數中使用二維陣列(注意陣列中的逗號和分號的使用),其中星期二(2)對應至數值1、星期三(3)對應至數值2、星期五(5)對應至數值2、星期日(7)對應至數值2。

例如:若儲存格A2為星期三(3)即加2,傳回星期五。儲存格A2為星期日(7)即加2,傳回星期二。

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

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

【準備工作】

選取儲存格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 發表在 痞客邦 留言(4) 人氣()

有網友問到在 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 發表在 痞客邦 留言(2) 人氣()

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

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

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

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

(1) 儲存格F2:=SUM(OFFSET(A2,ROW(1:1)-1,0,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 發表在 痞客邦 留言(7) 人氣()

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

1. 選取儲存格A1:I4。

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

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

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

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

vincent 發表在 痞客邦 留言(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 發表在 痞客邦 留言(14) 人氣()

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

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

1. 選取儲存格A1:C9。

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

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

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

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

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

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

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

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

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

vincent 發表在 痞客邦 留言(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 發表在 痞客邦 留言(1) 人氣()

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

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

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

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

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

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

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

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

vincent 發表在 痞客邦 留言(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 發表在 痞客邦 留言(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 發表在 痞客邦 留言(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 發表在 痞客邦 留言(2) 人氣()

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

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

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

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

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

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

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

這是以 APP 掃描後的結果:

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

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

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

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

  

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

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

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

 

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

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

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

 

vincent 發表在 痞客邦 留言(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 發表在 痞客邦 留言(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 發表在 痞客邦 留言(32) 人氣()

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼