贊助廠商

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

搜尋本部落格文章資料

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

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

網友問到:(如下圖)如何標示兩個員工工作時間重疊區域?

在 Excel 的工作表中記錄了 A 和 B 員工的起迄工作時間,如何依據這個時間繪出二人重疊的時間區域?

Excel-根據兩個員工的起迄工作時間繪出兩人重疊的時間區域

 

【公式設計與解析】

1.產生每隔30分鐘的時間數列

儲存格A4:=TIME(8+INT((ROW(1:1)-1)/2),MOD((ROW(1:1)-1),2)*30,0)

複製儲存格A4,貼至儲存格A4:A40。

產生 08:00 AM→08:30 AM→09:00 AM→09:30 AM→10:00 AM→... 數列。

 

2. 產生 A 和 B 工作時間範圍圖示

儲存格B4:=($A4>=B$2)*($A4<B$3)

文章標籤

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

網友問到:在 Excel 的工作表中記錄了每一天所出現的多個號碼(如下圖),如果想要計算星期一至星期日裡每個數字分別出現的次數,該如何處理?。

image

 

【公式設計與解析】

儲存格K2:=SUMPRODUCT((WEEKDAY($A$2:$A$365,2)=COLUMN(A:A))*($C$2:$H$365=$J2))

複製儲存格K2,貼至儲存格Q50。

在 SUMPRODUCT 函數,利用以下二個條件判斷完全相符者的個數。

(1) 條件一:WEEKDAY($A$2:$A$365,2)=COLUMN(A:A)

利用 WEEKDAY 函數配合參數「2」,判斷傳回值是否為「1 (=COLUMN(A:A))」。

(在此,傳回值 1 表示星期一。)

image

當公式向右複製,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→…。

文章標籤

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

學生問到:利用 Google 表單讓人填答後,會自動產生回覆結果的統計圖表(如下圖),如何能取用這些已經製作好的統計圖?

做法很簡單,參考以下的做法:

先把滑鼠移至圖表上,視窗右側會出現「複製」圖示,點選這個圖示。(進行複製工作)

將Google表單產生的統計圖表放到Word文件中使用

接著,在網址列輸入:doc.new,用以產生一個新的 Google 文件。

在文件中按一下 Ctrl+V 鍵,會貼上剛剛複製的圖表。

將Google表單產生的統計圖表放到Word文件中使用

再選取「檔案/下載格式/Microsoft Word(.docx)」,以下載圖表所在的文件。

將Google表單產生的統計圖表放到Word文件中使用

打開下載好的 Word 文件,即可看到圖表置於 Word 中了。(不過,放到 Word 文件中的統計圖是一張一張的圖片,無法進一步進行編輯。)

將Google表單產生的統計圖表放到Word文件中使用

文章標籤

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

工作、教學時使用YouTube播放影片,有時會需要用到子母畫面的方式。例如播放影片時想要同時顯示講義、教材。目前 Google Chrome 70版已有內置了播放YouTube的子母畫面功能了。

當你播放一個YouTube影時:

利用Chrome顯示YouTube的子母畫面

在影片有一下右鍵,會顯示如下的選單:

利用Chrome顯示YouTube的子母畫面

連續再按一下右鍵,則會出現另一個選單,其中就有「子母畫面」:

利用Chrome顯示YouTube的子母畫面

使用子母畫面時,當你在做別的工作時,這個子畫面會置於視窗的最上層。子畫面可以改變大小和任意移動位置。

利用Chrome顯示YouTube的子母畫面

同時間,只能有一個YouTube影片被設為子畫面。而Chrome的標籤會顯示那個標籤正在播放子母畫面的內容。

利用Chrome顯示YouTube的子母畫面

文章標籤

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

在 Outlook 中收到一封郵件,裡頭有大量的收件人 Email 地址,如果想要將其備份至 Excel 的工作表中以方便保存,該如何處理?(參考下圖)

Excel-如何將Outlook收件者Email位址轉換至試算表

參考以下步驟操作:

1. 開啟 Outlook 的 Email 郵件,複製所有的收件人。

Excel-如何將Outlook收件者Email位址轉換至試算表

2. 將其貼至 Word 文件中。

Excel-如何將Outlook收件者Email位址轉換至試算表

3. 將「;」取代為「^p」,即將「;」符號置換為跳行(Enter)。

Excel-如何將Outlook收件者Email位址轉換至試算表

結果如下:

Excel-如何將Outlook收件者Email位址轉換至試算表

4. 將 Word 內容複製到 Excel 中。

文章標籤

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

網友問到:在 Excel 中設定數值格式時,如何顯示繁體中文的數值和簡體中文的國字數值?

Excel-顯示繁體中文和簡體中文的國字數值

在儲存格格式的數值設定中,自訂:

顯示繁體中文國字數值:

(1) [DBNum2][$-zh-TW]G/通用格式、(2) [DBNum2][$-404]G/通用格式

顯示簡體中文國字數值:

(1) [DBNum2][$-zh-CN]G/通用格式、(2)[DBNum2][$-804]G/通用格式

Excel-顯示繁體中文和簡體中文的國字數值

如果在數值之後要加上「元整」,則在儲存格格式的數值設定中,自訂:

顯示繁體中文國字數值:

[DBNum2][$-zh-TW]G/通用格式"元整"、[DBNum2][$-404]G/通用格式"元整"

顯示簡體中文國字數值:

文章標籤

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

最近在研究自己通訊錄上的連絡人時,好奇想要知道每個姓氏的人數份佈比率,所以利用樞紐分析表工具和公式分別處理一次,卻得到不同結果,進一步了解找出了自已在公式設計的錯誤之處。(參考下圖)

Excel-計算姓名清單中每個姓氏的人數(SUMPRODUCT)

在下圖中,A欄是所有連絡人的姓名,先利用公式取出其姓。

儲存格B2:=IF(LEN(A2)=4,LEFT(A2,2),LEFT(A2,1))

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

取用B欄內容執行樞紐分析表操作,得到以下的結果:

Excel-計算姓名清單中每個姓氏的人數(SUMPRODUCT)

選取A欄的全部資料,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名。

可以取用樞紐分析表會產生的不重覆的姓,

設計公式,儲存格D2:

=IF(LEN(C2)=2,SUMPRODUCT((LEFT(姓名,2)=C2)*1),SUMPRODUCT((
LEFT(姓名,1)=C2)*1))

文章標籤

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

今天同仁問了一個實務面上常會踫到的問題:當在 Word 中製作合併列印文件時,資料來源是放在 Excel 的工作表中(如下圖),當資料合併至 Word 文件中的時候,原先是文字的班別,卻顯示為 0,該如何解決?

Word+Excel-解決合併列印文件時文字變成0的問題

在 Excel 中先建立一個資料表當為資料來源:

Word+Excel-解決合併列印文件時文字變成0的問題

當你在 Word 中製作合併列印檔時使用這個資料檔:

Word+Excel-解決合併列印文件時文字變成0的問題

原先是「子一甲、資一甲、資一乙」等的文字卻都顯示為 0:

Word+Excel-解決合併列印文件時文字變成0的問題

這是因為資料表中的班別欄位,被認定為數字格式,所以文字全都變成 0 了。

處理方式:只要將所有欄位的數值格式全都設定為「文字」即可。

Word+Excel-解決合併列印文件時文字變成0的問題

重新製作這個合併列印文件檔,結果就會是正確的:

文章標籤

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

(研習範例)參考下圖,同仁常問到的問題:在 Excel 中使用 VLOOKUP 函數,看起來公式是對的,但是查詢結果卻出錯,到底是那裡有問題?這個情形常發生在用學號、代號、座號等數字在做查詢時。

觀察下圖,在 Excel 中預設在儲存格中輸入文字會靠左對齊、輸入數值會對右對齊。在查表的資料裡雖然看來都是數字,但卻是文字格式。而查詢資料的數值是數字格式,所以查詢結果是錯誤訊息(因為查不到任何相符結果)。

為何這查表資料的數字會靠左對齊?通常是因為這些資料在系統的資料庫中的欄位屬性原始就是被設定為文字,所以匯出時就會是文字格式。

Excel-解決使用VLOOKUP函數查詢數字發生的錯誤

如何才能得到想要的結果呢?你可能會這樣做:

將儲存格F2的數值格式也修改為「文字」。

Excel-解決使用VLOOKUP函數查詢數字發生的錯誤

目前看來結果還是錯的(觀察儲存格數字已自動靠左對齊,所以已將格式設定為文字。)

Excel-解決使用VLOOKUP函數查詢數字發生的錯誤

此時,你只要在這個狀態下,重新輸入一次要查詢的內容(本例再key一次71),結果就會正確。

Excel-解決使用VLOOKUP函數查詢數字發生的錯誤

如果你有大量資料要查表,上述的動作將會過多操作,還是改用修正公式的方法:

文章標籤

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

(研習範例)參考下圖,學校老師時常建立一些試題,其中含有題目領域和題型等欄位,如何能快速得知各領域、各題型分配的題數?

Excel-試題領域和題型的數量分析(SUMPRODUCT)

首先,選取 Word 中已出好題目的領域和題型的欄位,並且複製到 Excel 中。再透過 Excel 公式來求取各領域、各題型分配的題數。

Excel-試題領域和題型的數量分析(SUMPRODUCT)

參考下圖,在 Excel 中選取領域和題型欄位有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:領域、題型。

Excel-試題領域和題型的數量分析(SUMPRODUCT)

輸入公式,儲存格E2:=SUMPRODUCT((領域=$D2)*(題型=E$1))

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

你也可以試著使用樞紐分析表工具,手動操作得到相同結果。

Excel-試題領域和題型的數量分析(SUMPRODUCT)

文章標籤

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

教師節為了給學校同仁一個祝福,想要用同仁姓名出現的字組成一個 QR Code,讓大家掃描後可以看到其中的文字(如下圖)。

利用文字做成QR Code

參考以下的做法:

首先,搜集了同仁的姓名,然後用 Excel 取出每一個字(利用公式或是資料剖析工具),然後用樞紐分析表工具,產生了不重覆的文字。

參考:Excel-手動產生不重覆項目(樞紐分析表)

再將每一個字產生一個圖檔:

參考這篇文章:PowerPoint+Word-將100個字製作為100個圖檔

接著,到網站產生這個 QR Code,並下載 QR Code 圖檔:

利用文字做成QR Code

接著,放大這個圖檔:

利用文字做成QR Code

並且,觀察 QR Code 中每一個小方塊的大小(例如:54X54)。

文章標籤

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

如果手邊有 100 個字,想要將每一個字變成一個圖檔(100個圖檔),該如何處理?

PowerPoint+Word-將100個字製作為100個圖檔

試試以下我的做法:

先在 Word 文件中準備好這 100 個字:

PowerPoint+Word-將100個字製作為100個圖檔

選取所有文字,並且在[段落]設定中將大網階層設定為:階層1。並且將此文件存檔。

PowerPoint+Word-將100個字製作為100個圖檔

新增一個 PowerPoint 簡報,並且設定投影片大小:相同的寬度和高度(例:4公分)。

PowerPoint+Word-將100個字製作為100個圖檔

接著,在新增投影片選項中選取:從大綱插入投影片。

PowerPoint+Word-將100個字製作為100個圖檔

選取前面所儲存的文件檔案,插入後如下圖:

文章標籤

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

學校的工作中,有時候需要利用相同底圖繪製多張標示用途的圖片案,這類的操作,你都用什麼工具來製作?如果只是要繪製簡單的圖案,PowerPoint 就可以派上用場。

以下圖為例,根據一張校舎配置圖,再依不同需求於其上標示一些不同內容。

PowerPoint-利用母片繪製相同底圖的圖片

PowerPoint 的環境就是一個方便繪圖的環境,每一張空白的投影片,就像是一張空白的底稿。先將底圖準備好,進入 PowerPoint 的母片中,將此底圖放置在佈景主題的投影片母片裡(記得要將圖片置於最下層)。

PowerPoint-利用母片繪製相同底圖的圖片

回到簡報的投影片編輯狀態下,新增一張空白投影片。

PowerPoint-利用母片繪製相同底圖的圖片

接著將你要標示的圖示,在此一一加入:

PowerPoint-利用母片繪製相同底圖的圖片

所有圖示都標示在投影片後,將 PowerPoint 另存新檔,選取一種你要的圖片格式:

PowerPoint-利用母片繪製相同底圖的圖片

如此,即可取得在 PowerPiont 中製作完成相同底圖的多張標示圖片。

文章標籤

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

網友詢問先前一篇文章:Excel-分離資料到對應的表格儲存格中(FIND,LEFT,MID)

參考下圖,如何轉換表格並且互換(由左至右、由右至左)?

Excel-分離資料到對應的表格儲存格中(FIND,LEFT,MID)2

儲存格I2:

=IF(C2="V",$C$1,"")& " "&IF(D2="V",$D$1,"")&" "&IF(E2="V",$E$1,"")

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

Excel-分離資料到對應的表格儲存格中(FIND,LEFT,MID)2

儲存格E2:

=IFERROR(IF(FIND(E$1,$A2),"V",""),"")&IFERROR(IF(FIND("全",$A2),"V",""),"")

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

Excel-分離資料到對應的表格儲存格中(FIND,LEFT,MID)2

文章標籤

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

(網友提問)在 Excel 中要計算符合條件的加總可以使用 SUMIF 函數,例如:

(參考下圖)

儲存格F2:=SUMIF($B$2:$B$19,E2,$C$2:$C$19)

儲存格F3:=SUMIF($B$2:$B$19,E3,$C$2:$C$19)

但是當資料是複製而來,當貼上的資料範圍超過原來公式裡的儲存格範圍,如何可以不改公式而正確的計算結果。

Excel-解決複製而來的資料而需要修改公式

【公式設計與解析】

如果儲存格範圍會變動,則需要藉助 OFFSET 函數和 COUNTA 函數來求得動態範圍。

儲存格F2:=SUMIF(OFFSET($B$2,0,0,COUNTA($B$2:$B$199),1),E2,OFFSET
($C$2,0,0,COUNTA($B$2:$B$199),1))

(1) COUNTA($B$2:$B$199)

利用 COUNTA 函數求取一個儲存格範圍內的文字(非空白)數量,其中參數 199,只是一個很大的數字。

文章標籤

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

同事問到:好容易搜集到的YouTube 影片網址(參考下圖左),如何能轉換為 YouTube 的影片標題,並且建立超連結可以點選播放(參考下圖右)?

如何將YouTube影片網址轉換為含有影片標題的超連結以方便點播?

如果你想用 Word 來試試,則結果會令你失望,因為將網址輸入或拖曳至文件中,只會產生超連結,而沒有影片標題。

如何將YouTube影片網址轉換為含有影片標題的超連結以方便點播?

如果放到 Google Keep 中則會產生超連結,並且有預覽內容,但還是沒有影片標題。

如何將YouTube影片網址轉換為含有影片標題的超連結以方便點播?

我個人的作法是,取用 Evernote 記事軟體,將在 YouTube 播放的影片網址拖曳至 Evernote 的記事中。

如何將YouTube影片網址轉換為含有影片標題的超連結以方便點播?

即可產生影片標題,也有影片超連結了。再將這些內容,複製到 PowerPoint 簡報的投影或是 Word 的文件中使用。

如何將YouTube影片網址轉換為含有影片標題的超連結以方便點播?

文章標籤

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

學校老師想要在上課時,能有實務投影機來配合課程使用。但是,不是每個教室都有配置實物投影機,該如何解決?

其實,我們可以拿手機來當實物投影機,只要電腦是Windows 10作業系統,開啟連線和手機的投放即可。

先參考這篇來解決連線的問題:將手機內容投影至Windows 10系統的畫面中

利用手機的攝影功能和行動裝置的概念,先完成手機與 Windows 10 的連線,打開手機具有照像功能的 App,就可以開始使用無線的實物投影機。

拿手機作為無線實物投影機(Windows 10連線+手機投放)

不管要介紹電腦週邊裝置(USB網卡、RFID)或是變壓器上的各種標示,各個角度都可以輕鬆投影在電腦,完全投放沒死角。

拿手機作為無線實物投影機(Windows 10連線+手機投放) 拿手機作為無線實物投影機(Windows 10連線+手機投放)

如果要展示物體上的文字或是文件,也可以配合手機的固定架,或是放桌上手持相機掃描文字。

拿手機作為無線實物投影機(Windows 10連線+手機投放) 拿手機作為無線實物投影機(Windows 10連線+手機投放)

字的大小視手機和文件的距離而定。現在環保意識強烈,能不印就不印。或是拿到的廣告紙要宣傳一下,也是透過手機的無線實物投影效果,方便秀出內容,不製造任何麻煩。

拿手機作為無線實物投影機(Windows 10連線+手機投放) 拿手機作為無線實物投影機(Windows 10連線+手機投放)

文章標籤

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

同仁在使用 Word 時,將表格的欄位名稱設定為「重覆標題列」,但卻一直失效,跨頁表格並沒有自動重覆標題列。該如何解決?(參考下圖)

Word-如何解決表格跨頁無法重覆標題列的問題

我知道的原因之一是因為:表格內容中被設定成文繞圖

Word-如何解決表格跨頁無法重覆標題列的問題

只要將文繞圖改為:無

Word-如何解決表格跨頁無法重覆標題列的問題

即可實現表格跨頁標題重覆的功能。

Word-如何解決表格跨頁無法重覆標題列的問題


文章標籤

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

學校設有雲端差勤系統,同仁每日上下班要打卡。遇到出差或或請假,也在這個系統上登錄。我是個主管,想要將差勤系統的同仁請假資料放到 Google 日曆上,方便了解同仁請假狀況,該如何處理?

Excel-將差勤系統匯出的請假資料匯入Google日曆

查詢這個差勤系統後發現,其有匯出功能,匯出資料如下:

Excel-將差勤系統匯出的請假資料匯入Google日曆

其中有三個欄位是我需要的:

Excel-將差勤系統匯出的請假資料匯入Google日曆

根據 Google 提供如何利用 CSV 檔匯入 Google 日曆的說明:

https://support.google.com/calendar/answer/37118?hl=zh-Hant

Excel-將差勤系統匯出的請假資料匯入Google日曆

所以必須利用公式,將原始欄位內容轉換為 Google 日曆所需的欄位內容:

儲存格O2:=C2&D2

儲存格P2:=DATE(MID(E2,1,3)+1911,MID(E2,5,2),MID(E2,8,2))

文章標籤

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

網友問到:在 Excel 中有一個數值清單,如何針對這些數值,以固定數量為間隔來計算平圴?

以下圖為例,如何計算每 4 個一組或是 6 個一組來計算平均?

Excel-取固定數量分組的平均(OFFSET,ROW)


【公式設計與解析】

儲存格D2:=AVERAGE(OFFSET($A$1,(ROW(1:1)-1)*4+1,0,4,1))

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

(1) (ROW(1:1)-1)*4+1

當公式向下複製時,會產生 1, 5, 9, 13, 17, 21, 25, …。

ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

(2) OFFSET($A$1,(ROW(1:1)-1)*4+1,0,4,1)

當公式向下複製時,會產生儲存格A2:A5、儲存格A6:A9、儲存格A10:A13、…。

最後透過 AVERAGE 函數取得各個儲存格範圍的平均值。


同理:

儲存格F2:=AVERAGE(OFFSET($A$1,(ROW(1:1)-1)*6+1,0,6,1))

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

當公式向下複製時,會產生儲存格A2:A7、儲存格A8:A13、儲存格A14:A19、…。

最後透過 AVERAGE 函數取得平均值。


參考下圖:

Excel-取固定數量分組的平均(OFFSET,ROW)


文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼