贊助廠商

學不完.教不停.用不盡文章列表

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

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

網友問到:在 Excel 的資料表裡(參考下圖),如何依數值在對應的欄位上標示「*」?

儲存格B2:=IF($A2=B$1,"*","")

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

Excel-依數值在對應的欄位上標記

學不完.教不停.用不盡文章列表

文章標籤

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

與人討論到圓形圖製作,大家對於圓形圖的第一個扇形區的起始角度有點討論。其實在 Excel 裡,微軟設計了圓形圖第一個扇形區域的起始角度預設為0度,但不代表一定要從0度開始。

Excel-自訂圓形圖第一個扇形區域的起始角度

只要點選圓形圖,再進入數列資料格式設定裡,於「數列選項」即可自定第一扇區起始角度了。同樣的環圈圖裡的第一扇區起始角度也都可以調整。

Excel-自訂圓形圖第一個扇形區域的起始角度

學不完.教不停.用不盡文章列表

文章標籤

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

現在新興科技時代,教學環境中愈來愈多的設備可以使用,例如雷雕機。要使用雷雕機來刻字,你必須先準備好圖檔。例如,如果你刻個英文名字來當鑰匙圈的圖案,你可以使用 PowerPoint 的軟體功能來製作。

例如:

1. 將想要雕刻的英文字母,每一個字都分開在個別的文字方塊中。

2. 組合這些字彼此重疊,各個筆劃都不能獨立而沒有和其他筆劃連接。

3. 選取這個重排好的字型,並設定「格式/合併圖案」,並選取「聯集」。

4. 為這個產生的圖案,將圖案填滿白色,只設定圖案的框線。(雕刻機會延著框線切割)

PowerPoint-製作給雕刻機使用的文字圖案

5. 為圖案中加入一個圓形框,為了要雕出一個洞以安置掛鑰匙的環。

PowerPoint-製作給雕刻機使用的文字圖案

6. 例如,複製該圖案至Inkspace,再調整長度和寬度。

PowerPoint-製作給雕刻機使用的文字圖案

7. 另存新檔為「繪圖交換格式(AutoCAD DXFR14)」,再配合雕刻機把它刻出來。

PowerPoint-製作給雕刻機使用的文字圖案

雕刻後如下圖類似範例:

PowerPoint-製作給雕刻機使用的文字圖案

學不完.教不停.用不盡文章列表

文章標籤

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

在 PowerPoint 中如果要製作文字設定多層色彩(如下圖),該如何處理?

PowerPoint-製作文字多層色彩效果

通常需要用到「合併圖案」的邏輯效果。

1. 先新增一個文字和一個白色矩形(為了顯示白色區域,所以加上框線)。

2. 先選文字再選矩形。

3. 設定「格式/合併圖案/交集」。(得到下圖結果)

PowerPoint-製作文字多層色彩效果

4. 再新增一個文字和一個白色矩形(為了顯示白色區域,所以加上框線)。

5. 先選文字再選矩形。

6. 設定「格式/合併圖案/減去」。(得到下圖結果)

PowerPoint-製作文字多層色彩效果

將以上兩個結果合併在一起,並設定不同色彩。

PowerPoint-製作文字多層色彩效果

學不完.教不停.用不盡文章列表

文章標籤

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

在 PowerPoint 中有時需要製作圖案拼貼而成的造形圖案,其中包含了文字和圖片。例如:拼貼菱形物件,該如何製作如下的圖片較簡單易行?

PowerPoint-拼貼菱形物件

 

【參考做法】

1. 在投影片中產生一組數個矩形的物件。

PowerPoint-拼貼菱形物件

2. 選取這些物件,並選取「格式」功能表的「編輯圖案/變更圖案」,選取「菱形」。

PowerPoint-拼貼菱形物件

3. 複製並移動菱形物件如下圖。

PowerPoint-拼貼菱形物件

4. 刪除一些物件,如下圖。

PowerPoint-拼貼菱形物件

5.可以修改格式(例如:填滿色彩)。

PowerPoint-拼貼菱形物件

PowerPoint-拼貼菱形物件

6.複製圖片並填滿某些菱形物件。

PowerPoint-拼貼菱形物件

自行變化各種組合吧!

PowerPoint-拼貼菱形物件

學不完.教不停.用不盡文章列表

文章標籤

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

在 PowerPoint 中,如何讓文字能穿透至下層圖片(如下圖右),以讓文字能融合?

PowerPoint-設定文字能穿透至下層圖片 PowerPoint-設定文字能穿透至下層圖片

要達到這樣的效果,只要想辦法將文字設定為「透明」即可。

但是,如果你將文字物件放在圖片上:

PowerPoint-設定文字能穿透至下層圖片

當從功能表裡選取文字色彩來設定,其中並沒有透明的效果。

PowerPoint-設定文字能穿透至下層圖片

你只要改由「設定圖形格式」功能窗格裡設定:

1. 點選:文字選項。

2. 設定文字填滿:實心填滿。

3. 調整透明度:本例設定為40%。

PowerPoint-設定文字能穿透至下層圖片

結果如下,即可讓文字能穿透至下層圖片:

PowerPoint-設定文字能穿透至下層圖片

此時,再從功能表裡選取文字色彩來設定,也是可以保留透明的效果。

PowerPoint-設定文字能穿透至下層圖片

學不完.教不停.用不盡文章列表

文章標籤

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

網友發問:在 Excel 的工作表中,有一個日期清單,如何依距今日的日期區間標示不同色彩?

參考下圖,分別在0-20、21-40、41-60的區間裡顯示不同色彩。

Excel-依距今日的日期區間標示不同色彩

 

【公式設計與解析】

Excel-依距今日的日期區間標示不同色彩

1. 選取D2:D24,設定格式化的條件。

規則類型:使用公式來決定要格式化哪些儲存格

編輯規則:=D2<=TODAY()+20

設定格式:紅色前景字

Excel-依距今日的日期區間標示不同色彩

2. 新增一個規則。

規則類型:使用公式來決定要格式化哪些儲存格

編輯規則:=(D2>TODAY()+20)*(D2<=TODAY()+40)

設定格式:綠色前景字

Excel-依距今日的日期區間標示不同色彩

3. 新增一個規則。

規則類型:使用公式來決定要格式化哪些儲存格

編輯規則:=(D2>TODAY()+40)*(D2<=TODAY()+60)

設定格式:綠色前景字

Excel-依距今日的日期區間標示不同色彩

學不完.教不停.用不盡文章列表

文章標籤

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

在 Excel 裡製作統計分析圖表非常方便、簡單,但有時資訊的安排會讓結果難以解讀。例如:下圖中的折線圖裡有五個數列全都擠在一起,如果能篩選部分數列,則可以清晰易讀。

Excel-使用圖表的篩選工具顯示局部數列,方便比較並且讓圖表變的清爽。

當你做好一個五個數列旳圖表:

Excel-使用圖表的篩選工具顯示局部數列,方便比較並且讓圖表變的清爽。

只要點選圖表,再選取圖表右側的「篩選」圖示:

Excel-使用圖表的篩選工具顯示局部數列,方便比較並且讓圖表變的清爽。

直接篩選想要的數列,記得按下套用。

Excel-使用圖表的篩選工具顯示局部數列,方便比較並且讓圖表變的清爽。

同一圖表很容易再呈現不同的數列:

Excel-使用圖表的篩選工具顯示局部數列,方便比較並且讓圖表變的清爽。

學不完.教不停.用不盡文章列表

文章標籤

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

學校同仁根據這篇:資訊視覺化-Word Art線上文字雲產生器

想要利用 Excel,試著將全校學生的姓氏以文字雲呈現,該如何處理?

Excel-計算多人姓氏數量並製成文字雲(SUMPRODCUT,LEFT)


【公式設計與析】

1. 首先取得全校學生姓名清單,並置入 Excel 的工作表中。(如下圖的A欄)

本例只處理單姓的名字,不處理複姓的名字。

2. 利用公式取出每個姓名的姓氏部分。

儲存格C2:=LEFT(A2,1)

利用 LEFT 函數取得儲存格A2內容字串中左邊的第一個字。

Excel-計算多人姓氏數量並製成文字雲(SUMPRODCUT,LEFT)

3. 將取得的學生姓氏利用樞紐分析工具處理。

Excel-計算多人姓氏數量並製成文字雲(SUMPRODCUT,LEFT)

4. 將「學生姓氏」欄位置入樞紐分析欄位的「列」。

如此,可以產生不重覆的姓氏清單。

Excel-計算多人姓氏數量並製成文字雲(SUMPRODCUT,LEFT)

如果將「學生姓氏」欄位置入樞紐分析欄位的「值」,並設定「計數」,就可以直接得到每個姓氏的數量了。

Excel-計算多人姓氏數量並製成文字雲(SUMPRODCUT,LEFT)

5. 利用公式計算每個姓氏的人數。

如果想要手動用公式計算人數,則參考以下公式:

儲存格D2:=SUMPRODUCT((LEFT($A$2:$A$1652)=B2)*1)

Excel-計算多人姓氏數量並製成文字雲(SUMPRODCUT,LEFT)

6. 將工作表中F欄和G欄的內容複製到 Word Art 進行編輯文字雲。產生結果如下:

Excel-計算多人姓氏數量並製成文字雲(SUMPRODCUT,LEFT)

學不完.教不停.用不盡文章列表

文章標籤

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

最近課程中需要用到「文字雲」,使用的是免費且線上即可操作的文字雲產生器 Word Art。使用上十分的方便,產出效果也是十足。

資訊視覺化-Word Art線上文字雲產生器

Word Art:https://wordart.com

資訊視覺化-Word Art線上文字雲產生器

準備工作:

1.資料來源

例如:從 Google Search Console 中下載了讀者查詢關鍵字和點擊的資料。

(取點擊的前100筆)

資訊視覺化-Word Art線上文字雲產生器

例如:從 Google Search Console 中下載了讀者查詢國家/地區和點擊的資料。

(取點擊的前50筆)

資訊視覺化-Word Art線上文字雲產生器

2.中文字型

由於該網站預設使用的是英文字型,因為我想要顯示中文字,所以要自行上傳中文字。

例如:下載免費的「思源黑體」字型。

網頁:https://www.google.com/get/noto/#/family/noto-sans-hant

繁體中文:https://noto-website-2.storage.googleapis.com/pkgs/NotoSansCJKtc-hinted.zip

 

製作文字的步驟如下:

1. 點選「Import」。

2. 複製 Excel 中的 100 筆查詢資料,並且在文字方塊中貼上。

3. 勾選「CSV format」。

4. 點選「Import words」。

資訊視覺化-Word Art線上文字雲產生器

5. 點選「Visualize」,即可產生文字雲。但是,中文字都沒有出現。

資訊視覺化-Word Art線上文字雲產生器

6. 切換至「FONTS」區。

7. 點選「Add Fonts」,上傳先前下載的一種字型。

資訊視覺化-Word Art線上文字雲產生器

8. 點選該字型,再點選「Visualize」,即可看到有中文字的文字雲。

資訊視覺化-Word Art線上文字雲產生器

9. 點選「SHAPES」,再點選一種圖型。

10. 點選「Visualize」,即可看到所選型狀的文字雲。

該網站也支援由你自行上傳形狀的圖片,格式要為PNG、JPG等。

資訊視覺化-Word Art線上文字雲產生器

11. 點選「DOWNLOAD」,再點選「Standard PNG」,即可下載PNG格式的圖片。

如果你點選HQ的圖片,則必須付費使用。

資訊視覺化-Word Art線上文字雲產生器

下載 Standard PNG 如下圖結果:

資訊視覺化-Word Art線上文字雲產生器

其他功能例如你可以調整字的方向:

資訊視覺化-Word Art線上文字雲產生器

或是改變字的色彩:

資訊視覺化-Word Art線上文字雲產生器

使用另一筆資料產生的文字雲:

資訊視覺化-Word Art線上文字雲產生器

學不完.教不停.用不盡文章列表

文章標籤

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

在 Excel 裡,如何計算指定日數特定星期幾的日期?

參考下圖,根據起始日期,再加十天後,要計算最接近指定星期幾的日期。

Excel-計算指定日數後最接近特定星期幾的日期(WORKDAY.INTL)

 

【公式設計與解析】

儲存格C2:=WORKDAY.INTL(A2+10-1,1,D2)

D欄字串:1代表非工作日, 0代表工作日

參數「A2+10-1」:儲存格A2日期+n天-1 (本例n=10)

 

這是一個很特殊的做法,可以參考:WORKDAY.INTL 函數說明

語法:WORKDAY.INTL(start_date, days, [weekend], [holidays])

引數:

Start_date:這是取為整數的開始日期。

Days:這是 start_date 之前或之後的工作日數。 正值表示未來日期;負值表示過去日期;零表示 start_date。 Day-offset 會取為整數。

Weekend :指出一週中屬於週末日而不視為工作日的日子。 Weekend 是指定何時是週末的數字或字串。

Weekend 字串值長度為七個字元,且字串中每個字元會代表一週內的一天,從星期一開始。 1代表非工作日, 0代表工作日。 字串中僅允許字元 1 和 0。 1111111 是無效字串。

學不完.教不停.用不盡文章列表

文章標籤

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

在 Excel 的資料清單中(參考下圖),在欄位中的內容是日期+時間,如果想要比對特定日期來做運算,該如何處理?

Excel-取出日期相同者予以加總(INT,SUMPRODUCT)

 

【公式設計與解析】

儲存格E3:=SUMPRODUCT((INT(A2:A14)=E2)*B2:B14)

在 Excel 裡,每個日期時間的數值中,日期為整數部分,時間為小數部分。

所以,INT(A2:A14)可以取出日期陣列中每個日期。

因此,可以不需使用YEAR、MONTH、 DAY等函數來取出日期。

INT(A2:A14)=E2:在陣列公式中會傳回 TRUE/FALSE 陣列,

(INT(A2:A14)=E2)*B2:B14:在公式中的「*」運算,相當於執行邏輯AND運算,在運算過程中會將 TRUE/FALSE 轉換為 1/0。

學不完.教不停.用不盡文章列表

文章標籤

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

在 Excel 裡,資料有時是分散在多個儲存格範圍裡的,如何在這些範圍中取得最高分或是最低分?

例如,一個老師有多個班級,當要比較多個班級分數的最高分或最低分,該如何處理?

Excel-計算多儲存格範圍的最大值和最小值(LARGE,SMALL)


【公式設計與解析】

(1) 使用 LARGE 函數取出最大值

儲存格J3:=LARGE((B3:B10,E3:E10,H3:H10),1)

儲存格J5:=LARGE((B3:B10,E3:E10,H3:H10),2)

注意:儲存格範圍以逗號隔開並以括號含括。

1. (儲存格範圍1,儲存格範圍2,儲存格範圍3)

以「,(逗號)」分隔,表示儲存格範圍的「聯集」。

2.(儲存格範圍1 儲存格範圍2 儲存格範圍3)

以「 (空格)」分隔,表示儲存格範圍的「交集」。

Excel-計算多儲存格範圍的最大值和最小值(LARGE,SMALL)


(2) 使用 SMALL 函數取出最小值

儲存格J8:=SMALL((B3:B10,E3:E10,H3:H10),1)

儲存格J10:=SMALL((B3:B10,E3:E10,H3:H10),2)

注意:儲存格範圍以逗號隔開並以括號含括

學不完.教不停.用不盡文章列表

文章標籤

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

在 Excel 裡的日期格式通常為 2020/11/22 或是 2020-11-22 等,但是國人常會有人表達為 20201122。因為 Excel 把 20201122 視為文字,如何轉換讓日期變為數值,以便於運算?

Excel-文字日期轉換為數值日期(TEXT)

 

【公式設計與解析】

儲存格B2:=TEXT(A2,"0-00-00")*1

利用 TEXT 函數,配合參數「0-00-00」將日期轉換為「年-月-日」格式。

例如:1992/5/23、1971/6/6

注意:其中的年和月不固定為一碼或二碼。

儲存格C2:=TEXT(TEXT(A2,"0-00-00")*1,"yyyy/mm/dd")

利用 TEXT 函數,配合參數「 yyyy/mm/dd」將數值日期轉換為「年/月/日」格式的文字串,其中月和日各為2碼。

例如:1992/05/23、1971/06/06

學不完.教不停.用不盡文章列表

文章標籤

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

網友提問:

如果在 Excel 中,要根據多個儲存格範圍的內容來排名,該如何處理?

Excel-多儲存格範圍排名(RANK.EQ)

 

【公式設計與調整】

儲存格C3:=RANK.EQ(B3,($B$3:$B$10,$F$3:$F$10),0)

如果你使用 RANK 函數,則只能在單一儲存格範圍裡進行排序動作。

如果改用 RANK.EQ 函數,則可以在一個以上的範圍來進行排序。

利用以下的語法:RANK.EQ(儲存格,(範圍一,範圍二,範圍三,…),0)

即可產生多範圍的排名。

學不完.教不停.用不盡文章列表

文章標籤

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

網友提問:在 Excel 中,如何根據以下的檢測數值給予對應的指引?

60-80 健康

80-100或20-60 稍加注意生活作習飲食

100-150或0-20 建議健康檢查

>150或<0 立即就醫

Excel-根據檢測值給予建議指引(VLOOKUP)

 

【公式設計與解析】

儲存格B2公式:=VLOOKUP(A2,$F$2:$G$8,2)

利用 VLOOKUP 的查詢功能,可以避免利用巢狀結構的 IF 函數或是複雜的 IFS 公式內容,當數值對應的指引改變了,只要在查詢表中修改,不用再修改公式。

學不完.教不停.用不盡文章列表

文章標籤

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

網友問到:

在 Excel 的工作表中,每個月都有設定一個目標值,參考下圖,在實際值大於目標值時,希望能自動標示出來,該如何處理?

下圖中的例子,是在當實際值大於目標值時,該月份的文字以紅色顯示。

Excel-條件符合時自動改變顯示格式

 

【公式設計與說明】

首先,選取儲存格A2:C13。

接著,進入「設定條式化的條件」。

並且,設定:

規則類型:使用公式來決定要格式化哪些儲存格。

編輯規則:=$C2>$B2  (注意「$」的使用)

設定格式:紅色前景字

結果會在實際值大於目標值時顯示為紅色字。

學不完.教不停.用不盡文章列表

文章標籤

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

在進行大數據分析前,必須要做資料清理。而在 Power BI 或 Excel 的 Power Query 編輯器中,對於資料的格式可以做的處理如下圖。使用工具方便、輕鬆,但是如果想要以 Excel 的公式來處理呢?

Excel-利用公式執行資料清理

這些功能,如何在 Excel 的工作表中以公式呈現來完成?

1. 小寫

所有文字轉換成大寫字,使用UPPER函數,儲存格B2:=UPPER(A2) 。

Excel-利用公式執行資料清理


2. 大寫

所有文字轉換成小寫字,使用LOWER函數,儲存格B2:=LOWER(A2)。

Excel-利用公式執行資料清理


3. 每個單字大寫

所有文字轉換成首字大寫,使用PROPER函數,儲存格B2:=PROPER(A2) 。

Excel-利用公式執行資料清理


4. 修剪(移除開頭和尾端的空白字元)

刪除儲存格內容頭尾空白,使用TRIM函數,儲存格B2:=TRIM(A2)。

Excel-利用公式執行資料清理


5. 清除(移除不可列印字元)

刪除儲存格內不可視字元,使用CLEAN函數,儲存格B2:=CLEAN(A2)。

結果只清除了不可列印字元,仍有一些不可視字元未能刪除。

Excel-利用公式執行資料清理

註:根據微軟提供的資料,CLEAN 函數是設計來從文字中移除 7 位元 ASCII 碼的前 32 個非列印字元 (值 0 至 31)。 在 Unicode 字元集中,有額外的非列印字元 (127、129、141、143、144 及 157 的值)。 單獨使用 CLEAN 函數不會移除這些額外的非列印字元。


6. 新增首碼

在儲存格內容之前新增文字,使用「&」運算子,儲存格B2:="Microsoft "&A2。

Excel-利用公式執行資料清理


7. 新增尾碼

在儲存格內容之後新增文字,使用「&」運算子,儲存格B2:=A2&"文件"。

Excel-利用公式執行資料清理

學不完.教不停.用不盡文章列表

文章標籤

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

網友根據這一篇:Excel-移除表格中重覆的資料,詢問如何刪除重覆欄?

Excel-移除表格中「欄」的重覆資料

這一篇文章主要是以表格功能裡的「移除重覆項」來操作,但是其主要是刪除重覆的「列」,如果是要刪除「欄」,該如何處理?

Excel-移除表格中「欄」的重覆資料

我好像找不到適用的工具,可以直接刪除重覆欄。但是,可以使用以下的間接方式。

本例假設要刪除「甲」的重覆項目(項四-A)。

1. 將原表格的欄和列轉置。

Excel-移除表格中「欄」的重覆資料

如果如下:

Excel-移除表格中「欄」的重覆資料

2. 將資料範圍轉換為表格

3. 移除重覆項。

Excel-移除表格中「欄」的重覆資料

勾選欄位:甲

Excel-移除表格中「欄」的重覆資料

結果如下:

Excel-移除表格中「欄」的重覆資料

4.將表格轉換為「範圍」。

Excel-移除表格中「欄」的重覆資料

5. 將原表格的欄和列轉置,任務完成。

Excel-移除表格中「欄」的重覆資料

學不完.教不停.用不盡文章列表

文章標籤

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

網友想要根據一個 Excel 的資料表(如下圖左),在輸入一個代碼後查得資料表中的價格,該如何處理?

主要是要根據代碼中的「a」和「b」來查詢價格。

Excel-同時利用二個代碼查詢(SUBSTITUTE,MID,SUMPRODUCT)

 

【公式設計與解析】

首先,要將「b欄位」的資料重建在二欄之中(如下圖)。

Excel-同時利用二個代碼查詢(SUBSTITUTE,MID,SUMPRODUCT)

 

(1) 公式:=SUBSTITUTE(F2,"-",REPT(" ",20))

結果:XYZ         2       61       5       10

將儲存格F2中的「-」字元以20個空格取代。

(2) 公式:=SUBSTITUTE(MID(SUBSTITUTE(F2,"-",REPT(" ",20)),20,20)," ","")*1

結果:2

利用 MID 函數由第20個字取20個字元,再將空格以空字串取代。

「*1」作用乃為將MID函數傳回的文字數字轉換為數值數字。

(3) 公式:=SUBSTITUTE(MID(SUBSTITUTE(F2,"-",REPT(" ",20)),40,20)," ","")*1

結果:61

利用 MID 函數由第40個字取20個字元,再將空格以空字串取代。

「*1」作用乃為將MID函數傳回的文字數字轉換為數值數字。

(4) 公式:

=SUMPRODUCT((B11=A2:A5)*(B13>=B2:B5)*(B13<=C2:C5)*ROW(D2:D5))-1

結果:4

在 SUMPRODUCT 函數中利用三個條件取出完全符合者的列號:

(B11=A2:A5)*(B13>=B2:B5)*(B13<=C2:C5)

其中「*」運算,相當於執行邏輯AND運算。

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼