贊助廠商

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

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

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

手機上有攝影機,所以掃描 QR Code 是一件很容易的事,用來掃描「超連結網址、文字、郵件地址、電話號碼、地理座標、WIFI存取」等,實用且方便。很多的 App 也陸續結合掃描 QR Code 功能變成內建功能,讓使用者能更方便來使用。以下例舉數個實例。

image

 

1. 在Edge 瀏覽器中使用掃描 QR Code 來連結網址或是搜尋文字

當開啟 Edeg 瀏覽器時,在新增內容時,可以利用「搜尋或輸入網址」中的 QR Code 掃器。對準條碼或 QR Code 加以掃描,掃描得到的網址或文字都會被複製到剪貼簿中。

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

或是選取功能表中的「影像搜尋」來掃 QR Code。

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

掃描到的網址會直接連結至該網址,若是掃描到一般文字,則會對該文字進行搜尋。

存在剪貼簿中的 QR Code 內容也可以在其他地方貼上使用。 

QR Code在各種軟體中的使用

 

2. 利用 Line 的內建 QR Code 掃描器

開啟 Line 軟體時,點選在「搜尋」框中掃描圖示,對著 QR Code 掃描。(本例掃描 WiFi 設定的 QR Code、網址、純文字)

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

點選右上角的「分享」圖示,並將選取分享的對象或軟體(例如:剪貼簿),即可取得該內容。

Screenshot_20200222-203804125_resize QR Code在各種軟體中的使用

如果你切換至「轉為文字」,則是要對著文字拍照,即可對鏡頭下的文字加以文字辨識,並可複製到剪貼簿中。

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

 

3. 使用 Googel Lens 中的 QR Code 掃描功能

例如,我的手機鏡頭只要對照 QR Code,不用按下拍照鍵,即可即時辨識 QR Code 的內容,如果是網址,即會出現「超連結」圖示,點選該圖示即可在瀏覽器中看到該網頁。

也可以點選「複製」圖示,將內容複製到剪貼簿中,再進一步使用。

我的相機也會 Google Lens 連結,點選左下角的 Google Lens 圖示,即可進入 Google Lens。

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

選取「複製文字」或是「搜尋資料」圖示,再掃描 QR Code 來使用。

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

 

4. 掃描手機中的 QR Code 相片(以 Google 助理的Google Lens 為例)

例如,進入 Google 助理後,點選左下角 Google Lens 圖示。再點選右上角的「圖片」圖示。

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

選取手機中的一張圖片,即刻會掃描該 QR Code,再進入後續操作(網站、複製網址、分享等)。

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

 

5. 使用 Google PhotoScan 軟體來保存 QR Code 圖片

開啟 Google PhotoScan 軟體,並對照 QR Code 圖片拍照。

掃描 QR Code 圖片時,程式會顯示四個圓點,你必須移動手機將圓框往四個方向對準圖點。

QR Code在各種軟體中的使用 QR Code在各種軟體中的使用

完成後,點選右下角的圖片檢視圖示,再按「調整邊角」,並自行調整四個角落。再看是否圖片方向是否需要旋轉,當按下「完成」後,即可將不規則四邊形,轉換成矩形了。

Screenshot_20200222-194920072_resize Screenshot_20200222-194941341_resize

利用Google搜尋本部落格全部「QR Code」文章

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

文章標籤

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

一個班級的導師都有掌握學生的基本資料,以便了解學生和快速和家長取得連繫。通常這個資料表是以 Excel 的檔案來儲存,老師們可以拿它來做什麼?

基本資料不能只拿檢視,改變其顯示的方式,可以擴大資料表格的效用。

(以下的學生資料是虛構的)

Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

 

1. 資料表格

這是傳統的試算表表格,以表格形式呈現,大家應該都很習慣這樣的閱讀方式。

Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

 

2. 表格中顯示相片

如果要放入學生的相片呢?該如何處理?

參考這篇:Excel-將學生相片藏在資料表中(註解)

將學生的相片利用註解的格式設定,將其底圖設定為學生的相片。

Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

 

3. 以地圖模式顯示

還可以將學生的資料在地圖中顯示,再點選標記以檢視學生資料。

參考這篇:Google-自製住家附近健保特約藥局地圖(Excel)

Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

 

4. 由儲存格點選在地圖中顯示位置 

在資料表中建立地址的超連結,即可在地圖中顯示。

儲存格K2:

=HYPERLINK("https://www.google.com.tw/maps/place/"&J2,D2&"住家")

Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

 

5. 標示當天生日的學生

每天打開資料表,就會標示當天生日的學生。

Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

(1) 選取儲存格A2:J33。

(2) 選取「常用/樣式」功能表中「設定格式化的條件/新增規則」。

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

(4) 編輯規則:

=(MID($I2,4,2)*1=MONTH(TODAY()))*(MID($I2,7,2)*1=DAY(TODAY()))

(5) 設定格式:黃色儲存格底色。

Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

公式說明:

(1) 條件一:MID($I2,4,2)*1=MONTH(TODAY())

MID($I2,4,2)*1:利用 MID 函數取出儲存格I2中第4個字元開始的2個字元(月)。

其「*1」運算是要將 MID 函數取得的文字轉換為數值。

MONTH(TODAY()):利用 MONTH 函數取得 TODAY 函數傳回今天日期中的月份。

MID($I2,4,2)*1=MONTH(TODAY()) 會依據條件是否成立,傳回 TRUE/FASLE

(2) 條件二:MID($I2,7,2)*1=DAY(TODAY())

MID($I2,7,2)*1:利用 MID 函數取出儲存格I2中第7個字元開始的2個字元(日)。

DAY(TODAY()):利用 DAY 函數取得 TODAY 函數傳回今天日期中的日數。

MID($I2,7,2)*1=DAY(TODAY()) 會依據條件是否成立,傳回 TRUE/FASLE

(3) 條件一*條件二

其中「*」相當於執行邏輯 AND 運算,即兩個條件都成立時,會傳回 TRUE,否則傳回 FALSE

 

6. 上傳至Google雲端硬碟

將資料表上傳至Google雲端硬碟,還有一些方便的操作,例如:

(1) 檢視時常按電話號碼的儲存格,還可以有通話和傳送訊息的功能。

Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日) Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

Screenshot_20200221-112051019 Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

 

(2) 檢視時常按地址的儲存格,也可以在地圖中顯示。

Screenshot_20200221-111518192 Excel-運用學生基本資料表(顯示相片、地圖顯示、標示生日)

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

文章標籤

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

網友陸續問到一些關於 Excel 中資料驗證的做法,以下提供三個例子參考:

1.限定輸入特定數字限定

2.只能輸入大寫字

3.限定大寫英文和全部8碼

Excel-資料驗證的應用

 

要建立儲存格輸入的資料驗證:

(1) 選取要設定資料驗證的儲存格。

(2) 選取「資料/資料驗證」的功能表,再選取「資料驗證」。

 

1.限定輸入特定數字

儲存格允許:清單

來源:575,635,700,730,787(範例)

Excel-資料驗證的應用

 

2.限定只能輸入大寫字

儲存格允許:自訂

公式:=EXACT(B2,UPPER(B2))

EXACT函數:

用以比較二個文字字串,字串完全相同時,會傳回 TRUE,否則會傳回 FALSE。 EXACT 函數會區分大小寫,但也忽略格式設定上的差異。

Excel-資料驗證的應用

 

3.限定大寫英文和全部8碼

儲存格允許:自訂

公式:=(EXACT(C2,UPPER(C2)))*(LEN(C2)=8)

LEN 函數:傳回文字字串中的字元數。

條件一:EXACT(C2,UPPER(C2)),限定輸入大寫英文字。

條件二:LEN(C2)=8,限定輸入共 8 碼。

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

結果:條件一 AND 條件二

Excel-資料驗證的應用

結果如下:

Excel-資料驗證的應用

 

【延伸學習】

Excel-研習練習範例(15個巧妙應用的資料驗證準則)

Excel-利用資料驗證來除錯

利用Google搜尋本部落格全部「資料驗證」文章

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

文章標籤

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

在 Excel 的工作表中,取得一個以小時為單位的網頁瀏覽量統計表,如何將其轉換為天報表、週報表?(參考下圖)

Excel-將統計表中的時報表轉為天報表和週報表(OFFSET)

 

【公式設計與解析】

1. 將時報表轉為天報表

儲存格E2:=SUM(OFFSET($B$2,(ROW(1:1)-1)*24,0,24,1))

(1) (ROW(1:1)-1)*24

每24小時為1天,公式向下複製時,會產生0, 24, 48, …。

(2) OFFSET($B$2,(ROW(1:1)-1)*24,0,24,1)

公式向下複製時,OFFSET 函數取得儲存位址 B2:B25、B26:B49、B50:B73、...。

即A欄中每間隔24筆要加總,最後透過 SUM 函數加總。

 

2. 將天報表轉為週報表

儲存格H2:=SUM(OFFSET($E$2,(ROW(1:1)-1)*7,0,7,1))

(1) (ROW(1:1)-1)*7

每7天為1週,公式向下複製時,會產生0, 7, 14, …。

(2) OFFSET($E$2,(ROW(1:1)-1)*7,0,7,1)

公式向下複製時,OFFSET 函數取得儲存位址 E2:E8、E9:E15、E16:E22、...。

即E欄中每間隔7筆要加總,最後透過 SUM 函數加總。

 

【延伸練習】

如何將時報表轉為週報表?

儲存格H2:=SUM(OFFSET($B$2,(ROW(1:1)-1)*24*7,0,24*7,1))

每24*7小時為一週,所以修改公式的間隔數即可。

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

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

 

有同仁問到:在收 Gmail 郵件時,有些郵件來源總是會被 Google 判定為垃圾郵件,不勝其擾,該如何處理呢?

如果把某些郵件位址設定為白名單,Google 就不會將其放入垃圾郵件處理了。

作法如下:(建立該郵件的篩選器)

1. 開啟會被放入垃圾郵件的郵件。

2. 在選單中選取「篩選這類的郵件」。

Google-指定郵件位址不要被認定為垃圾郵件(設定白名單)

3. 按一下「建立篩選單」。

Google-指定郵件位址不要被認定為垃圾郵件(設定白名單)

4. 勾選「不要將它傳送到圾垃郵件」。

5. 點選「建立篩選器」。

Google-指定郵件位址不要被認定為垃圾郵件(設定白名單)

 

【延伸練習】

如果你想指定郵件位址(垃圾郵件)直接拒收該郵件,要設定成黑白名單,該如何處理?

例如:在篩選器中指定郵件「刪除它」。

Google-指定郵件位址不要被認定為垃圾郵件(設定白名單)

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

文章標籤

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

在 Excel 中,相信很多人都使用過「資料驗證」來讓資料輸入時能提高正確性。其實,你還可以利用資料驗證來除錯。

Excel-利用資料驗證來除錯

例如,下圖中設定讓「性別」欄位只能輸入「男、女」。但是,問題是如果資料是由外部匯入的,就無法由輸入受到資料驗證的把關,該怎麼辦呢?

Excel-利用資料驗證來除錯

你可以選取要檢查的欄位,再選取「資料/資料工具」功能表中的「圈選錯誤資料」。

Excel-利用資料驗證來除錯

Excel 會幫你圈選出來發生錯誤的儲存格。

如果要清掉圈選,可以再選取「資料/資料工具」功能表中的「清除錯誤圈選」。

Excel-利用資料驗證來除錯

利用這個方法,可以來找「資料」欄位中不是整數者。例如,在「資料」欄位中設定儲存格為 1~100 的整數。

Excel-利用資料驗證來除錯

結果圈選了二個看似整數的儲存格,這是怎麼一回事?

原來是儲存格輸入非整數,但卻以設定了小數點位數為 0 的格式。所以,看似整數,其實不然。還好,使用資料驗證工具,一下子就找出問題點。

Excel-利用資料驗證來除錯

你也來想想,還可以用在那裡?

利用Google搜尋本部落格全部「資料驗證」文章

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

文章標籤

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

在 Excel 中的一天以「1」表示,所以:

1小時=1/24;1分鐘=1/24/60;1秒鐘=1/24/60/60

【間隔1小時】儲存格A3=A2+1/24

【間隔1分鐘】儲存格B3=B2+1/(24*60)

【間隔1秒鐘】儲存格C3=C2+1/(24*60*60)

複製儲存格A3:C3,貼至儲存格A3:C25。

image

 

【間隔2小時】儲存格A3:=A2+2/24

【間隔3分鐘】儲存格B3:=B2+3/(24*60)

【間隔4秒鐘】儲存格C3:=C2+4/(24*60*60)

複製儲存格A3:C3,貼至儲存格A2:C25。

image

 

【自行練習】產生固定間隔1小時2分3秒

儲存格A3:=A2+1/24+2/(24*60)+3/(24*60*60)

Excel-產生固定間隔的時、分、秒

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

文章標籤

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

延續這一篇:Excel-認識SUMPRODUCT函數

SUMPRODUCT 函數中,要計算乘積和的儲存格範圍,必須符合相同的陣列長度。

1. 1欄垂直陣列 X 1欄垂直陣列

兩個垂直陣列中的個數必須相同。

認識SUMPRODUCT函數

 

2. 1欄垂直陣列 X 多欄垂直陣列

每一欄垂直陣列中的個數必須相同。

認識SUMPRODUCT函數

 

3. 1列水平陣列 X 1列水平陣列

每一列水平陣列中的個數必須相同。

認識SUMPRODUCT函數

 

4. 1列水平陣列 X 多列水平陣列

每一列水平陣列中的個數必須相同。

認識SUMPRODUCT函數

 

5. 矩形陣列 X 矩形陣列

每一欄垂直陣列中的個數必須相同;每一列水平陣列中的個數必須相同。

認識SUMPRODUCT函數

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

文章標籤

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

在 Excel 的公式中,SUMPRODUCT 函數應該可以算得上首屈一指的重要了。其「乘積和」的概念,在日常生活中十分常見。練習以下的例子。

1. 計算甲和乙的乘積和

甲:A2:A5;乙:B2:B5

透過 SUMPRODUCT 函數計算「乘積和

公式:=SUMPRODUCT(A2:A5,B2:B5)

公式:=SUMPRODUCT({1;3;5;7},{2;4;6;8})

垂直陣列({1;3;5;7}和{2;4;6;8})以「;」隔開。

公式:=SUMPRODUCT(A2:A5*B2:B5)

公式:=SUMPRODUCT({2;12;30;56})

公式:=2+12+30+56=100

Excel-認識SUMPRODUCT函數

 

甲:B1:E1;乙:B2:E2

公式:=SUMPRODUCT(B1:E1,B2:E2)

公式:=SUMPRODUCT({1,3,5,7},{2,4,6,8})

水平陣列({1,3,5,7}和{2,4,6,8})以「,」隔開。

公式:=SUMPRODUCT({2,12,30,56}) 

公式:=2+12+30+56=100

Excel-認識SUMPRODUCT函數

 

2.計算男生的數量總和

公式=SUMPRODUCT(1*(A2:A5="男"),B2:B5)

條件:A2:A5="男",會傳回 TRUE/FALSE 陣列。

執行「1*」運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

公式=SUMPRODUCT(1*{TRUE;FALSE;TRUE;FALSE},B2:B5)

公式=SUMPRODUCT({1;0;1;0},{2;4;6;8})

公式=SUMPRODUCT({2;0;6;0})

公式:=2+6=8

Excel-認識SUMPRODUCT函數

 

3. 計算丙級通過的人數

公式=SUMPRODUCT(1*(A2:A5="丙級"),1*(B2:B5="通過"))

公式=SUMPRODUCT(1*{FALSE;TRUE;TRUE;FALSE},1*(B2:B5="通過"))

公式=SUMPRODUCT({0;1;1;0},1*{FALSE;TRUE;TRUE;FALSE})

公式=SUMPRODUCT({0;1;1;0},{0;1;1;0})

公式=0+1+1+0=2

公式=SUMPRODUCT((A2:A5="丙級")*(B2:B5="通過"))

執行「*」運算,相當於邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

Excel-認識SUMPRODUCT函數

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

文章標籤

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

1. 不因在第一列插入資料改變公式

在 Excel 的公式中,當你在插入一列(欄)後,Excel 會自動幫你調整相對的公式內容。

雖然方便實用,但有時你就是不想讓它自動調整。

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

【例】如果儲存格F5公式:=(B3-B6)/(A3-A6)

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

當在第3列新增一列時,公式會自動被修正為:=(B4-B7)/(A4-A7)

這是錯誤的結果。因為第 3 列裡預備要輸入資料,公式應為:=(B3-B7)/(A3-A7)

該如何處理?

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

公式修正:=(INDIRECT("B3")-B7)/(INDIRECT("A3")-A7)

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

INDIRECT("B3")將文字「B3」轉換為儲存格B3位址。

轉成字串就不會因插入動作而自動改變參照位址。

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

輸入資料後,結果是正確的。

不論插入幾列,都是會保持儲存格B3不變。

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

 

2. 不因在最後一列新增資料而改變公式

如果是在最後一個儲存格之後再新增資料,如何不修公式?

儲存格F2:=(OFFSET(B3,COUNT(B3:B1000)-1,0)-B3)/(OFFSET(A3,COUNT
(A3:A1000)-1,0)-A3)

(1) COUNT(B3:B1000)

利用 COUNT 函數計算儲存格B3:B1000中已輸入幾筆資料。

(2) OFFSET(B3,COUNT(B3:B1000)-1,0)

透過 OFFSET 函數,以儲存格B3為起點,取出相對第(1)式傳回值所對應的儲存格位址。

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

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

文章標籤

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

在 Excel 中要輸入各種符號,有時不是很直覺和方便,但是總要完成輸入工作啊!

通常我會使用以下的方式來輸入。(這只是個人習慣,你會如何輸入?)

 

(1) 利用 Excel 的「符號」功能表,在「符號」對話框中選取想要的符號。

快速鍵:按著Alt鍵+N→U,即可開啟「符號」對話框。

Excel-在工作表中輸入各種符號

 

(2) 利用輸入法(例如:微軟注音)來開啟輸入法整合器

切換至「微軟注音」輸入法,

快速鍵:按 Ctrl+Alt+「,」鍵,即可開啟「輸入法整合器」對話框。

Excel-在工作表中輸入各種符號

 

(3) 利用 Emoji 圖示輸入

按 WinKey+「.」鍵,開啟 Emoji 輸入框。切換至符號,再選取想要的符號。

Excel-在工作表中輸入各種符號

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

文章標籤

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

有人問到:身份證的第一碼為英文字母,常會用到要將其轉換為數字,例如:A→1、B→2、C→3、….、Z→26。如何能利用 Excel 來建立這個轉換的對照表?

Excel-建立字母和數字轉換的對照表(CHAR,ROW)

 

【公式設計與解析】

儲存格A1=CHAR(64+ROW(A1))

複製儲存格A1,貼至儲存格A1:A26。

說明:

CHAR:傳回數字指定的字元。

使用 CHAR 將數字代碼轉換成字元。

ROW:傳回參照的列號。

ROW(A1)=1、ROW(B2)=2

ROW(1:1)=1、ROW(2:2)=2

因為「A」 的ASCII碼是「65」,

所以 64+ROW(A1)=65。

 

【練習】建立全部小寫英字母

儲存格A1=CHAR(96+ROW(A1))

複製儲存格A1,貼至儲存格A1:A26。

因為「a」 的ASCII碼是「97」,

所以 96+ROW(A1)=97。

Excel-建立字母和數字轉換的對照表(CHAR,ROW)

 

【參考】Excel-製作ASCII碼對照表

Excel-建立字母和數字轉換的對照表(CHAR,ROW)

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

文章標籤

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

歡度春節之後,利用寒假開學前,來讓學校同仁,再次有機會親近 Excel,看看能否利用試算表工具來讓工作效能提升,所以製作了研習的範例。本篇是關於「陣列+SUM+IF、SUMIF、SUMIFS、SUMPRODUCT」的綜合應用。

延續上一篇文章:Excel-陣列公式初探

在使用陣列在執行加總計算時,利用 SUM+IF、SUMIF、SUMIFS、SUMPRODUCT等公式可以得到相同結果,可謂異曲同工,以下希望能融會貫通,使用時如魚得水。

下載練習檔案:點我下載

 

1. 計算「A*B」的總和

儲存格E3:{=SUM(A2:A7*B2:B7)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{、}」。

以下陣列公式做法相同,所以步驟說明加以省略。

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

陣列公式相當於執行:

{=SUM(A2:A7*B2:B7)}=SUM({4*9;7*2;8*4;6*5;2*1;8*5})

{=SUM(A2:A7*B2:B7)}=SUM({36;14;32;30;2;40})

 

2. 計算「甲*乙」的總和

選取儲存格A1:B7,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:甲、乙。

「甲」代表儲存格A1:A7;「乙」代表儲存格B1:B7。

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

(1) 儲存格E2:{=SUM(甲*乙)}

{=SUM(甲*乙)}=SUM({8;7;2;3;2;8}*{3;8;6;5;6;4})

{=SUM(甲*乙)}=SUM({24;56;12;15;12;32})

(2) 儲存格E2:=SUMPRODUCT(甲,乙)

公式=SUMPRODUCT({8;7;2;3;2;8},{3;8;6;5;6;4})

(3) 儲存格E2:=SUMPRODUCT(甲*乙)

公式=SUMPRODUCT({8;7;2;3;2;8}*{3;8;6;5;6;4})

公式=SUMPRODUCT({8*3;7*8;2*6;3*5;2*6;8*4})(執行相乘)

公式=SUMPRODUCT({24;56;12;15;12;32})

公式=24+56+12+15+12+32=151(執行相加)

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

 

3. 計算「性別為男」者的數量總和

選取儲存格A1:B9,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:性別、數量。

(1) 儲存格D2:{=SUM(IF(性別="男",數量,))}

性別="男":{TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}

數量:{75;61;55;99;36;58;56;42},))

在運算過程中 TRUE 會轉換為 1;FALSE 會轉換為 0。

=SUM(IF({1;1;0;0;0;1;1;0},{75;61;55;99;36;58;56;42},))

=SUM({75;61;0;0;0;58;56;0})

(2) 儲存格D2:{=SUM(IF(性別="男",數量,0))}

(3) 儲存格D2:=SUMIF(性別,"男",數量)

性別:{"";"";"女";"女";"女";"";"";"女"}

數量:{75;61;55;99;36;58;56;42})

取出「性別=男」者對應的數值予以加總:75+61+58+56=250

(4) 儲存格D2:=SUMPRODUCT((性別="男")*數量)

=SUMPRODUCT({1;1;0;0;0;1;1;0}*{75;61;55;99;36;58;56;42})

=SUMPRODUCT({75;61;0;0;0;58;56;0})

=75+61+58+56=250

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

 

4. 計算「已繳交」者的金額總和

選取儲存格B1:C9,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:金額、繳交。

(1) 儲存格E2:{=SUM(IF(繳交="V",金額,))}

(2) 儲存格E2:{=SUM(IF(繳交="V",金額,0))}

(3) 儲存格E2:=SUMIF(繳交,"V",金額)

(4) 儲存格E2:=SUMPRODUCT((繳交="V")*金額)

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

 

5. 計算「丙+丁」為正數者的總和

選取儲存格A1:B9,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:丙、丁。

(1) 儲存格E2:{=SUM(IF(丙+丁>0,丙+丁,0))}

公式=SUM(IF({-20;61;-7;-16;5;29;46;-48}>0,{-20;61;-7;-16;5;29;46;-48},0))

公式=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE},丙+丁,0))

公式=SUM(IF({0;1;0;0;1;1;1;0},{-20;61;-7;-16;5;29;46;-48},0))

公式=SUM({0;61;0;0;5;29;46;0})=141

(2) 儲存格E2:{=SUM(IF(丙*丁>0,丙+丁,))}

(3) 儲存格E2:=SUMPRODUCT((丙+丁>0)*(丙+丁))

=SUMPRODUCT(({-20;61;-7;-16;5;29;46;-48}>0)*(丙+丁))

=SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE})*(丙+丁))

=SUMPRODUCT(({0;1;0;0;1;1;1;0})*({-20;61;-7;-16;5;29;46;-48}))

=SUMPRODUCT({0;61;0;0;5;29;46;0})

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

 

6. 計算「男生已繳交」者的金額總和

選取儲存格B1:D13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:性別、金額、繳交。

(1) 儲存格F2:{=SUM(IF((性別="男")*(繳交="V"),數量,))}

(2) 儲存格F2:=SUMIFS(金額,性別,"男",繳交,"V")

SUMIF 語法:SUMIFS(加總範圍, 條件1範圍, 條件1, [條件2範圍, 條件2], ...)

在此,不管參數中有幾個條件,都會執行邏輯 AND 運算。

符合:條件1 AND 條件2 AND 條件3 AND

將對應的加總範圍予以加總。

(3) 儲存格F2:=SUMPRODUCT((性別="男")*(繳交="V")*數量)

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

 

7. 計算「一年級女生未繳交」者的金額總和

選取儲存格B1:E14,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:年級、性別、金額、繳交。

(1) 儲存格G2:{=SUM(IF((年級="一")*(性別="女")*(繳交<>"V"),數量,))}

(2) 儲存格G2:=SUMIFS(數量,年級,"一",性別,"女",繳交,"<>V")

(3) 儲存格G2:=SUMPRODUCT((年級="一")*(性別="女")*(繳交<>"V")*數量)

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

 

8. 轉換摘要表格

選取儲存格B1:E14,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:年級、性別、金額、繳交。

(1) 儲存格H2:=SUMPRODUCT((年級=H$1)*(性別=$G2)*金額)

(2) 儲存格H9:=SUMPRODUCT((年級=H$1)*(性別=$G2)*(繳交="V")*金額)

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

 

【自行練習】

陣列公式(SUM+IF)、SUMIF、SUMIFS、SUMPRODUCT

 

【延伸學習】

Excel-研習練習範例(排序)

Excel-研習練習範例(篩選)

Excel-研習練習範例(進階篩選)

Excel-研習練習範例(樞紐分析)

Excel-研習練習範例(15個巧妙應用的資料驗證準則)

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

文章標籤

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

網友問到:在 Excel 的工作表中若要將西元年以民國年顯示,並標示星期N,以「(N)」顯示,該如何處理?(參考下圖)

Excel-將西元年以民國年和星期N顯示(TEXT,RIGHT)

 

【公式設計與解析】

(1) 2020/02/22→109/02/22

儲存格D2:=TEXT(A2,"[$-zh-TW]e/mm/dd;@")

TEXT 函數中利用「,"[$-zh-TW]e/mm/dd;@」參數,將日期以民國年顯示。

 

(2) 2020/02/22→星期六

儲存格B2:=TEXT(A2,"[$-404]aaaa;@")

TEXT 函數中利用「[$-404]aaaa;@」參數,將日期以星期N顯示。

 

(3) 2020/02/22→六

儲存格C2:=RIGHT(TEXT(A2,"[$-404]aaaa;@"),1)

將第(1)式的傳回值以 RIGHT 函數取儲存格內容的右邊第一個字。

 

(4) 2020/02/22→109/02/22(六)

儲存格E2:=TEXT(A2,"[$-zh-TW]e/mm/dd;@")&"("&RIGHT(TEXT(A2,
"[$-404]aaaa;@"),1)&")"

利用第(1),(2),(3)式,並以「&」串接,將西元年組合成民國年和星期N的顯示格式。

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

文章標籤

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

在如下圖的 Excel 工作表裡,每個學生的各科成績可能是:A++、A+、A、B++、B+、B、C。如何計算每個學生 A、B、C 分別的個數?(內容亂數產生)

Excel-計算A++,A+,A,B++,B+,B,C的個數(SUMPRODUCT,SUBSTITUE)

 

【公式設計與解析】

儲存格H2:=SUMPRODUCT(1*(SUBSTITUTE($C2:$G2,H$1,"")<>$C2:$G2))

複製儲存格H2,貼至儲存格H2:J26。

本例利用 SUMPRODUCT 函數來計算,所以可以使用陣列來處理。

(1) SUBSTITUTE($C2:$G2,H$1,"")

利用 SUBSTITUTE 函數將儲存格C2:G2中的內容,以空字串("")取代「A」(儲存格H1)。

(2) SUBSTITUTE($C2:$G2,H$1,"")<>$C2:$G2

判斷第(1)式的傳回值是否和原儲存格C2:G2的內容是否不相同(代表含「A」),傳回 TRUE/FALSE,共 5 個。

(3) 1*(SUBSTITUTE($C2:$G2,H$1,"")<>$C2:$G2)

將第(2)式的傳回值「*1」,可以將 TRUE/FALSE 轉換為 1/0

(4) SUMPRODUCT(1*(SUBSTITUTE($C2:$G2,H$1,"")<>$C2:$G2))

最後利用 SUMPRODUCT 函數予以加總。

 

如果改為以下的形式來表示:(內容亂數產生)

Excel-計算A++,A+,A,B++,B+,B,C的個數(SUMPRODUCT,SUBSTITUE)

儲存格H2:=SUMPRODUCT(1*($C2:$G2=H$1))

複製儲存格H2,貼至儲存格H2:N26。

 

如果改成要計算計算全班各個成績的總數,該如何處理?

Excel-計算A++,A+,A,B++,B+,B,C的個數(SUMPRODUCT,SUBSTITUE)

儲存格H2:=SUMPRODUCT(1*($C$2:$G$26=H$1))

複製儲存格H2,貼至儲存格H2:N2。

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

文章標籤

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

學過程式設計的使用者,對於「陣列」一定不陌生!

陣列是一群相同資料類型的集合,在運算時透過相同規則可以處理大量的資料,並執行相同的運算。在 Excel 中陣列的觀念和程式設計中的觀念類似,為了讓 Excel 知道你是要使用陣列公式來運算,所以在輸入公式結束後,要按 Ctrl+Shift+Enter 鍵,而 Excel 會將公式以「{  }」來含括。

 

1. 水平一維常數陣列

(1) 選取儲存格A1:F1(共 6 個儲存格)。

(2) 輸入公式:={1,2,3,4,5,6}。(注意:每個數字以「,」分隔)

(3) 按 Ctrl+Shift+Enter 鍵。

儲存格A1:F1中的公式變為相同:{={1,2,3,4,5,6}}

Excel-陣列公式初探

當你想要刪除陣列中其中一個儲存格時,會看到「您不能只改變陣列中的一部分」的錯誤訊息。

Excel-陣列公式初探

 

2. 垂直一維常數陣列

Excel-陣列公式初探

(1) 選取儲存格A1:A6(共 6 個儲存格)。

(2) 輸入公式:={1;2;3;4;5;6}。(注意:每個數字以「;」分隔)

(3) 按 Ctrl+Shift+Enter 鍵。

儲存格A1:A6中的公式變為相同:{={1;2;3;4;5;6}}

 

3. 二維常數陣列

(1) 選取儲存格A1:D3(共 12 個儲存格)。

(2) 輸入公式:={1,2,3,4;5,6,7,8;9,10,11,12}。(注意:數字以「,」和「;」分隔)

(3) 按 Ctrl+Shift+Enter 鍵。

儲存格A1:D3中的公式變為相同:{={1,2,3,4;5,6,7,8;9,10,11,12}}。

{1,2,3,4;5,6,7,8;9,10,11,12} 陣列相當於

三個一維陣列組成:{1,2,3,4}、{5,6,7,8}、{9,10,11,12}

Excel-陣列公式初探

 

4. 公式中使用陣列公式

(1) 水平常數一維陣列運算

儲存格A1:{=SUM({1,2,3,4}*{5,6,7,8})}

Excel-陣列公式初探

 

(1) 選取儲存格E1:E4。

(2) 輸入公式:=C1:C4*D1:D4。

(3) 按 Ctrl+Shift+Enter 鍵。

儲存格E1:E4中的公式變為相同:{=C1:C4*D1:D4}

Excel-陣列公式初探

(2) 水平變數一維陣列運算

儲存格A1:{=SUM(C1:C4*D1:D4)}

Excel-陣列公式初探

(3) 常數垂直一維陣列和變數垂直一維陣列運算

儲存格A1:{=SUM({1;2;3;4}*D1:D4)}

Excel-陣列公式初探

(4) 常數水平一維陣列和變數水平一維陣列運算

儲存格A1:{=SUM({1,2,3,4}*C2:F2)}

Excel-陣列公式初探

(5)為陣列命名

將陣列命名後,在公式中使用顯得比較容易理解,而且可以縮短公式長度。

例如:選取儲存格D1:E6,按 Ctrl+Shift+F3 鍵,勾選「頂端列」。

儲存格D2:D6命名為「單價」;儲存格E2:E6命名為「數量」。

Excel-陣列公式初探

Excel-陣列公式初探

 

【例】計算所有商品販售總金額

儲存格A1:{=SUM(單價*數量)}

Excel-陣列公式初探

 

【例】計算商品販售總金額小於500者

儲存格A5:{=SUM(1*(單價*數量<500))}

單價*數量<500:會傳回 TRUE/FALSE。公式中「1*」運算,可以將 TRUE/FALSE轉換為 1/0。再藉由 SUM 函數計算總和(加計幾個 1,代表幾個小於 500 者。)

Excel-陣列公式初探

 

(6) 使用預設陣列運算的公式

儲存格A5:=SUMPRODUCT(1*(單價*數量<500))

藉由 SUMPRODUCT 函數可以不用在公式輸入完成後按 Ctrl+Shift+Enter 鍵。因為 SUMPRODUCT 函數內建以陣列來運算。

Excel-陣列公式初探

SUMPRODUCT 函數可以看成 SUMPRODUCT

單價*數量:{100*2,200*4,300*3,400*1,500*5}={200,800,900,400,2500}

單價*數量<500:{TRUE,FALSE,FALSE,TRUE,FALSE}

1*(單價*數量<500):{1,0,0,1,0}

最後:1+0+0+1+0=2

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

文章標籤

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

Google 試算表雖然不像 Excel 的功能如此其全,但仍有其優點。例如:在 Google 試算表中就可以直接對文字或句子做翻譯的功能。

利用Google試算表執行多國語言翻譯

在此利用 GOOGLETRANSLATE 函數:

利用Google試算表執行多國語言翻譯

語法:GOOGLETRANSLATE(文字, 原文語言, 譯文語言)

(1) 文字

翻譯的文字。文字的值須輸入在引號內,或為包含適當文字的儲存格參照。

(2) 原文語言

[選用,預設為"auto"],兩個字母語言代碼代表原文語言。

(3) 譯文語言

[選用,系統預設的語言],兩個字母語言代碼代表譯文語言。

語言代碼可參考 ISO 639-1(國際標準化組織 ISO 639語言代碼標準的第一部分)

https://zh.wikipedia.org/wiki/ISO_639-1

利用Google試算表執行多國語言翻譯

如果有需要,可以在 Google 試算表中翻譯完成,再下載至 xcel 中使用。

利用Google試算表執行多國語言翻譯

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

文章標籤

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

同仁問到:如何製作一個含有儲存格內容的物件,並且在儲存格內容變動時連動?

當你選取一個儲存格範圍加以複製(Ctrl+C),貼上時選取「圖片」選項。

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動

此時會產生和選取儲存格內容一致的圖片:

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動

仔細這個圖片,當儲存格內容變動時,圖片內容是不會跟著變動的。

此時,要用不同的方式來處理:

首先,在 Excel 選項中選取「快速存取工具列」標籤,然後在「不在功能的命令」區中找到「攝影」工具。

然後,將「攝影」工具新增至「所有文件(預設)」。

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動

當你選取一個儲存格範圍加以複製(Ctrl+C),再選取「攝影」工具。

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動

在其他空白儲存格任意位置點一下,即可產生選取儲存格範圍的映像圖片。

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動

當你修改了儲存格範圍的內容時,該映像圖片的內容會連動。

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動

這是一個圖片物件,所以可以執行各種圖片的格式設定。

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動

試著玩看看是否能做出以下的效果:

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動

當你將這個映像圖片複製到 Word 文件等其他的應用程式中,即會喪失資料的連動。

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動

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

文章標籤

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

在 Excel 的工作表中,如果在多個欄位中想要比對欄位中的項目有那些不一樣,如何做可以比較快?

Excel-使用快速鍵找出多個欄位中不相同的項目

 

【方式一】

先選取儲存格B2,往右下拖曳選儲存格B2:C9。

Ctrl+\ 鍵,Excel 會選取第二欄(乙項)和第一欄(甲項)不同的儲存格。

Excel-使用快速鍵找出多個欄位中不相同的項目

 

【方式二】

先選取儲存格C2,往右下拖曳選儲存格B2:C9。

Ctrl+\ 鍵,Excel 會選取第一欄(甲項)和第二欄(乙項)不同的儲存格。

Excel-使用快速鍵找出多個欄位中不相同的項目

 

相同的做法,可以適用在多個欄位的項目比較:

Excel-使用快速鍵找出多個欄位中不相同的項目

Excel-使用快速鍵找出多個欄位中不相同的項目

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

文章標籤

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

網友提問:在 Excel 的工作表中,如果輸入當月的一個日期,如何每天自動顯示距月底還有幾天,若是過月了,則顯示「0」,該如何處理?(參考下圖)

Excel-計算當月的日期之剩餘天數(YEAR,NONTH,DAY,TODAY)

 

【公式設計與解析】

儲存格D3:=IF((YEAR(TODAY())=YEAR(A3))*(MONTH(TODAY())=
MONTH(A3)),DAY(DATE(YEAR(A3),MONTH(A3)+1,0))-DAY(A3),0)

複製儲存格D3,貼至儲存格D3:D5。

 

(1) YEAR(TODAY())=YEAR(A3))*(MONTH(TODAY())=MONTH(A3))

利用雙條件來判斷是否為當月,若是,傳回 TRUE,若否,傳回 FALSE

條件一 YEAR(TODAY())=YEAR(A3):比較今日的年份和儲存格A3日期的年份

條件二 MONTH(TODAY())=MONTH(A3):比較今日的月份和儲存格A3日期的月份

 

(2) DAY(DATE(YEAR(A3),MONTH(A3)+1,0))

在 DATE 函數中置入儲存格A3日期的年份和下個月的月份,並且在日期中填入「0」。

可以傳回這個月的最後一天的日期,再以 DAY 函數取出日數,即可知這個月的天數。

 

(3) IF(第(1)式,第(2)式-DAY(A3),0)

若第(1)式判斷為當月,則將第(2)式傳回的當月天數再減掉儲存格A3中的日數;若第(1)式判斷不是當月,則顯示「0」。

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

文章標籤

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