贊助廠商

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

搜尋本部落格文章資料

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

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

在 Excel 中的資料處理並非都是單純的數值運算而已,也常需要用到邏輯和比較等運算。而邏輯運算和比較運算的傳回結果,可以用來配合 IF 函數根據不同條件得到不同的結果,試算表跟著從靜態變動態了。

基本上 IF 函數是根據條件是否成立,傳回成立的結果或是不成立的結果。參考以下二個,其條件部分用到了數值、比較和邏輯的運算。其中「非0/0」對照「TRUE/FALSE」。

Excel-關於IF的使用

Excel-關於IF的使用

其他的函數中也含有 IF 的概念,例如:COUNTIF 函數。

語法:COUNTIF(範圍, 條件)。

Excel-關於IF的使用

還有像是 SUMIF 函數,語法:SUMIF(條件範圍, 條件, 計算範圍)。

Excel-關於IF的使用

例如:SUMIFS,語法:SUMIFS(計算範圍, 條件範圍1, 條件1, 條件範圍2, 條件2, …)。

Excel-關於IF的使用

也有用於處理錯誤的函數中含有 IF 的概念,例如:IFERROR 函數和 IFNA 函數。而 IFNA 函數用於判斷是否為傳回值錯誤(#N/A)。

當儲存格內容或是公式傳回錯誤值時,可以轉換為其他字串。而搭配判斷錯誤的函數有:ISERROR 函數、ISERR 函數、ISNA 函數等。其中ISERROR 函數和ISERR 函數的差異有於 IFERR 函數有判斷 #N/A 以外的任何一種錯誤值。

Excel-關於IF的使用

文章標籤

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

Google 提供了免費的線上圖片壓縮工具(https://squoosh.app/),只要將圖片拖曳至網頁中即會立即進行轉換。

利用Google的免費線上工具壓縮圖片或轉換影像格式

以下的例子,原始檔案約有 30 M。(該圖是由4000張照片所組成的圖片檔)

利用Google的免費線上工具壓縮圖片或轉換影像格式

壓縮後的檔案大小減少很多,但是不太影響影像品質(30MB→16MB)。可以拖曳中間的藍線左右移動,比較壓縮後的品質。

利用Google的免費線上工具壓縮圖片或轉換影像格式

在視窗底部的壓縮選單中,可以挑選壓的影像類別:

利用Google的免費線上工具壓縮圖片或轉換影像格式

可以在編輯功能表中選取調整尺寸大小、調色板及影像品質等。

利用Google的免費線上工具壓縮圖片或轉換影像格式

不同的檔案的壓縮結果不盡相同:

利用Google的免費線上工具壓縮圖片或轉換影像格式

利用Google的免費線上工具壓縮圖片或轉換影像格式

當你改變影像的格式,其較換結果也可能會將檔案變大。

利用Google的免費線上工具壓縮圖片或轉換影像格式

影像調整好之後,可以點選右下角的下載圖示,即可在電腦中保存該影像。

文章標籤

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

你有用過 Word 中的圖片去背功能嗎?如果你使用 Windows 10,也可以利用其提供的小畫家 3D 來做去背的功能,做法和在 Word 中的用法差不多。

在 Windows 10 中,將圖片載入小畫家 3D 中。(以下的例子是比較單純的背景)

1. 點選「摩術選取」,再點選視窗右側的「下一步」。

使用Windows 10的小畫家3D為影像去背

2. 該圖已自動魔術選取了有圖案的部分,按一下完成。

使用Windows 10的小畫家3D為影像去背

3. 拖曳這個圖案,中間的部分已被物件化,可移動了!(成功去背了)

使用Windows 10的小畫家3D為影像去背

4. 利用四個旋轉功能來改變物件的立體樣子。

使用Windows 10的小畫家3D為影像去背

調整畫布大小:

使用Windows 10的小畫家3D為影像去背

將圖案加以儲存:

使用Windows 10的小畫家3D為影像去背

這是儲存後的 PNG 檔:

使用Windows 10的小畫家3D為影像去背

以下使用背景比較複雜的圖片來試試!載入圖片後,也是由「魔術選取」開始。

點選:下一步。

使用Windows 10的小畫家3D為影像去背

顯然,系統自動去背的效果並不好,所以要手動使用視窗右側的「新增」和「移除」功能。

使用Windows 10的小畫家3D為影像去背

其中「新增」劃過的部分是要保留的區域,而「移除」劃過的部分是要移除的區域。你可能需要多次的新增/移除,直到滿意後,點選「完成」。

使用Windows 10的小畫家3D為影像去背

調整畫布和儲存後的結果:

使用Windows 10的小畫家3D為影像去背

做其他的應用:

使用Windows 10的小畫家3D為影像去背

文章標籤

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

Google Classroom 最近新增了「問題」和「測驗作業」的功能,方便於作業的實施。

Google Classroom 的問題和測驗作業功能

(一)建立問題

問題可以用來看看學生對於某個問題的想法與回應,所以不一定要計分。如果設定分數,主要是看學生是否回答,有回答才能得分。

當你新增一個問題時,可以選取是否需要評分,題目可以是問答題或選擇題,可以設定學生是否可以查看摘要(別人回答的狀況)等。

Google Classroom 的問題和測驗作業功能

學生的回答的畫面:(本範例有勾選:學生可以查看課程摘要)

Google Classroom 的問題和測驗作業功能

教師檢視學生回答狀況:

Google Classroom 的問題和測驗作業功能

 

(二)建立測驗作業

利用 Google 表單的測驗功能來建立測驗內容,所以可以設定分數和答案。Google 會自動計算分數,唯這是表單測驗的分數,而非該作業的分數。所以作業分數要另外設定。

點選「Blank Quiz」,這是一個測驗的空白表單。

Google Classroom 的問題和測驗作業功能

開始命題:

Google Classroom 的問題和測驗作業功能

即然是作為測驗之用,所以要設定分數和答案,讓 Google 自動為你評分:

Google Classroom 的問題和測驗作業功能

  1. 學生收到這個作業,可以進入表單作答:(也可以附加檔案)

Google Classroom 的問題和測驗作業功能

學生作答完成後,可以查看分數和答案。

Google Classroom 的問題和測驗作業功能

是否給學生作答完立即查看分數和答案,可以由教師自主選擇:

Google Classroom 的問題和測驗作業功能

教師可以檢視學生的回答狀況(各個問題的摘要):

Google Classroom 的問題和測驗作業功能

檢視學生的回答狀況(各個問題的分析):

Google Classroom 的問題和測驗作業功能

檢視學生的回答狀況(每個學生的回答結果):

Google Classroom 的問題和測驗作業功能

教師可以下載學生回答的結果:選取下載回應(.CSV)

Google Classroom 的問題和測驗作業功能

用 Excel 開啟這個 CSV 檔:

Google Classroom 的問題和測驗作業功能

或是利用 Google 試算表查看結果:

Google Classroom 的問題和測驗作業功能

文章標籤

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

大家熟悉的 Excel 產生圖表十分的方便,也可以連結其他資料庫的資料。而Google 最近釋出的 Google Data Studio線上工具,可以用來上製作各種圖表。除了結合各種線上的資料庫,也可以上傳手邊的資料檔來產生圖表。Google Data Studio 可以連結 Google Cloud SQL 與 MySQL 等資料庫的資料,也可以連結來自 Google 試算表、Google Analytics (分析)、YouTube 頻道的資料。

初探利用GOOGLE DATA STUDIO線上製作圖表

今天我要初探這個線上工具。就先由政府資料開放平臺(https://data.gov.tw/)隨意取用一個資料檔(CSV格式)來開始。

初探利用GOOGLE DATA STUDIO線上製作圖表

檔案內容如下:

 

初探利用GOOGLE DATA STUDIO線上製作圖表

現在要利用 Google Cloud 提供的 Google Data Studio 線上產生該報表的統計圖表。

首先連結至網址:https://cloud.google.com/data-studio/

初探利用GOOGLE DATA STUDIO線上製作圖表

由新的空白報表開始:

初探利用GOOGLE DATA STUDIO線上製作圖表

在視窗右下角點選:建立新資料來源。(或點選曾經開啟的資料檔案或各種來源的資料)

初探利用GOOGLE DATA STUDIO線上製作圖表

接著,找到「上傳檔案」,點選:選取。

初探利用GOOGLE DATA STUDIO線上製作圖表

將先前下載的檔案拖曳進來,當顯示狀態為「已上傳」,表示資料來已載入。

接著,點選視窗右上角的「連結」。(上傳的資料是一個 CSV 格式的檔案)

初探利用GOOGLE DATA STUDIO線上製作圖表

連結好的資料可以看到多個欄位名稱,點選右上角的:加入報表。

初探利用GOOGLE DATA STUDIO線上製作圖表

再次點選:加入報表。

初探利用GOOGLE DATA STUDIO線上製作圖表

點選功能表中的「插入圖表」,再選取一個圖表樣式:

初探利用GOOGLE DATA STUDIO線上製作圖表

此時的畫面看起來很像 Excel 中的樞紐分析表和樞紐分析圖。

初探利用GOOGLE DATA STUDIO線上製作圖表

利用拖曳方式,調整維度為二個欄位,和設定排序的欄位:

初探利用GOOGLE DATA STUDIO線上製作圖表

還可以選用多種圖表:

初探利用GOOGLE DATA STUDIO線上製作圖表

也可以將圖表下載為 PDF 檔。

初探利用GOOGLE DATA STUDIO線上製作圖表

可以將報表嵌入網頁來顯示:

初探利用GOOGLE DATA STUDIO線上製作圖表

文章標籤

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

參考下圖,網友問到在 Excel 中要如何計算第 2 次考試比第 1 次考試進步的人數有多少,該如何處理?

Excel-比較二個數值清單符合條件的個數(SUMPRODUCT)

【公式設計與解析】

儲存格F2:=SUMPRODUCT((C2:C24>B2:B24)*1)

SUMPRODUCT 函數中,(C2:C24>B2:B24) 判斷式會傳回 TRUE/FALSE 的陣列,經由「*1」運算後,會轉換為 1/0 陣列。再由 SUMPRODUCT 函數予以加總,即為所求。

也可以使用陣列公式:

儲存格F2:{=SUM((C2:C24>B2:B24)*1)}

輸入完成後要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

文章標籤

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

回答提間:參考下圖中的工作表,每個月都設定了目標金額,當每月輸入實際金額後,希望能以不同色彩來區隔,該如何處理?

設定:(1) 達成:綠色 (2) 未達成:紅色 (3) 最後一個:紫色

Excel-利用設定格式化的條件來顯示不同狀態

 

【公式設計與解析】

首先,來練習產生A欄的月份:

儲存格A2:=DATE(2018,6+ROW(1:1),1)

複製儲存格A2,貼至儲存格A2:A23。

設定儲存格A2:A23的數值格式為自訂:yyyy/mm

image

通常,要設定三種文字色彩,只要二個條件。

接著,選取儲存格C2:C23,設定紅色字。(預設:未達成)

再來,選取儲存格C2:C23,新增一個設定格式化條件:(最後一個顯示紫色)

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

規則:=C3=""

格式:紫色文字色彩

image

最後,選取儲存格C2:C23,新增一個設定格式化條件:(達成顯示綠色)

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

規則:=C2>=B2

格式:綠色文字色彩

文章標籤

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

利用Google表單設計好的問卷調查,透過使用者線上填答,即可在Google試算表或下載至Excel中使用。在使用前要先了解表單問題的選項和被儲存格的資料格式。

單選題:傳回A、B、C的其中一個。(傳回文字)

Google表單-題目選項對照儲存在試算表的資料格式

核取方塊(複選):傳回 X、Y、Z 的組合,例如:X, Y。(傳回文字)

Google表單-題目選項對照儲存在試算表的資料格式

下拉式選單(單選):傳回 D、E、F其中一個。(傳回文字)

Google表單-題目選項對照儲存在試算表的資料格式

線性刻度(單選):本例傳回數字 1 ~ 5 其中一個。(傳回數字)

Google表單-題目選項對照儲存在試算表的資料格式

Google表單-題目選項對照儲存在試算表的資料格式

單選方格(每列單選):每列名稱(A、B、C)獨立一個資料欄位,內容為 X、Y、Z 其中一個。(傳回文字)

Google表單-題目選項對照儲存在試算表的資料格式

核取方塊格(每列複選):每列名稱(O、P、Q)獨立一個資料欄位,內容為 L、M、N 的組合。(傳回文字)

Google表單-題目選項對照儲存在試算表的資料格式

Google表單-題目選項對照儲存在試算表的資料格式

日期:傳回 YYYY/M/D(傳回數字)

Google表單-題目選項對照儲存在試算表的資料格式

時間:傳回 HH:MM:SS AM/PM(傳回數字)

Google表單-題目選項對照儲存在試算表的資料格式

Google表單-題目選項對照儲存在試算表的資料格式

 

若要進一步處理複選的答案,參考下圖。

儲存格L3:=(SUBSTITUTE(A3,E$1,"")<>A3)*1

儲存格M3:=(SUBSTITUTE(A3,G$1,"")<>A3)*1

儲存格N3:=(SUBSTITUTE(A3,G$1,"")<>A3)*1

Google表單-題目選項對照儲存在試算表的資料格式

文章標籤

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

在 Excel 中如果要限制使用者在某些儲存格中輸入的內容,則可以使用「資料驗證」,再設定資料驗證準則,使用者輸入不符準則的內容時,會被挑出來要求重新輸入,否則無法接受。

Google表單的驗證輸入資料的規則運算式

如果是在 Google 試算表中,也可以透過「驗證資料」工具來加以限制輸入的內容。

Google表單的驗證輸入資料的規則運算式

在 Google 表的中也有像資料驗證的概念,在填答者輸入不符規定的內容時,予以限制。除了數字、文字、長度等,還有一項:規則運算式。

Google表單的驗證輸入資料的規則運算式

透過表單設計者自定的規則來檢驗輸入是否合於規定,例如:限制使用者輸入的電話必須符合格式:+886nnn-nnn-nnn。

在 Google 表單的規則運算式中可以使用:

符號:

^:起始符號

$:結尾符號

[]:群集符號

{}:數量符號

範例 :

【例】^[we]:開頭必須是w或是e。

【例】^[a-zA-Z]:開頭只能是一個英文字母(大寫或小寫皆可)。

【例】[0123456789]$:結尾必須是數字,也可表示為:[0-9]$。

【例】{10} :代表正好10個。

【例】{12,}:代表12個或以上。

【例】{1,5}:代表1~5個之間。

 

字元符號:

【例】\w:所有英文字元([a-zA-Z])

【例】\W:所有非英文字元([^a-zA-Z0-9])

【例】\d:所有數字([0-9])

【例】\D:所有非數字([^0-9])

【例】+ :前一個字元顯示至少1次({1,})

【例】? :前一個字元顯示0或1次({0,1})

【例】* :前一個字元顯示0或1次以上({0,})

反斜線「\」還代表跳脫字元的意思。如果要使用 +、?、* 等,要以 \+、\?、\* 表示。

 

如果要檢查是否填入+886-nnn-nnn-nnn 的手機號碼,例如:+886-123-456-789。

規則運算式:^\+(886)-\d{3}-\d{3}-\d{3}

 

關於規則運算式,可以參考 Google 所提供的簡易說明:

https://support.google.com/docs/answer/3378864,以下為摘錄的內容:

運算式

說明

範例

符合

不符合

.

英文句號代表指定位置的任何字元。

d.

do, dog, dg, ads

fog, jog

*

字元後面加上星號時,代表搜尋前面字元重複 0 次以上的情形。

do*g

dog, dg, dooog

dOg, doug

+

字元後面有加號時,代表搜尋這個字元顯示 1 次以上的情形。

do+g

dog, dooog

dg, dOg, doug

?

前一個運算式不一定要出現。

do?g

dg, dog

dOg, doug

^

脫字符號必須放在規則運算式的最前面;該符號代表您要尋找的字串,是以脫字符號後面緊接的字元或序列來開頭。

^[dh]og

dog, hog

A dog, his hog

$

美元符號必須放在規則運算式的最後面;該符號代表您要尋找的字串,是以美元符號前面緊接的字元或序列來結束。

[dh]og$

dog, hog, hot dog

dogs, hog, doggy

{A, B}

前一個運算式重複 A B 次,且 A B 都是數字。

d(o{1,2})g

dog, doog

dg, dooog, dOg

[x], [xa], [xa5]

字元集代表其中一個指定字元應該出現在目前的位置。括弧內的字元通常都有效,包括前面運算式包含的字元:[xa,$5Gg.]

d[ou]g

dog, dug

dg, dOg, dooog

[a-z]

字元集範圍代表搜尋指定字元範圍內的字元。一般範圍包括 a-zA-Z 0-9。您可以將這些範圍合併成一個範圍:[a-zA-Z0-9]。您也可以將這些範圍與字元集 (如前述) 合併:[a-zA-Z,&*]

d[o-u]g

dog, dug, dpg, drg

dg, dOg, dag

[^a-fDEF]

如果字元集以「^」開始,代表您要搜尋不在指定集合內的字元。

d[^aeu]g

dog, dOg, dig, d$g

dg, dag, deg, dug

\s

所有空格字元。

d\sg

d g, d[TAB]g

dg, dog, doug

 

注意:如果您要尋找的字元在規則運算式中有特殊意義 (例如 ^ 和 $),就必須在搜尋查詢中讓這類字元「逸出」,也就是在字元前面加上反斜線。舉例來說,如果您要搜尋試算表中出現的 $ 字元,就必須輸入 \$。

以下是幾個運用規則運算式來搜尋試算表的例子:

搜尋包含美元金額的儲存格

在 [尋找] 列中輸入以下字串:^\$([0-9,]+)?[.][0-9]+

這個字串代表美元金額。第一個數字是出現零次以上的任何 0-9 數字或逗號,後面緊接 [.],然後再重複一次以上的任何 0-9 數字。這項搜尋作業可能會傳回下列結果:$4.666、$17.86、$7.76、$.54、$900,001.00、$523,877,231.56

搜尋包含美國郵遞區號的儲存格

在 [尋找] 列中輸入以下字串:[0-9]{5}(-[0-9]{4})?

這代表美國郵遞區號包含五位數字,並可選擇是否加上連字號以及四碼附加編號。

搜尋的儲存格內容必須是以小寫英文字母開始的名字

在 [尋找] 列中輸入以下字串:^[a-z]。

這代表儲存格內容包含一個小寫英文字母,後面接著另一個出現 0 次以上的字元。這項搜尋作業可能會傳回下列結果:bob、jim、gEORGE、marTin

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

學校同事問到:

在 Excel 中,若要根據學生名條裡的班級和座號二個條件,如何查詢其姓名?

如下圖,想要查詢指定的班級和座號所對應的姓名,該如何處理?

Excel-雙條件查詢(SUMPRODUCT,INDEX,MATCH)

 

【公式設計與解析】

選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,定義名稱:班級、座號、姓名。

1. 使用陣列公式

儲存格G2:{=INDEX(姓名,MATCH(1,(班級=E2)*(座號=F2),0),0)}

輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格G2,貼至儲存格G2:G7。

(1) MATCH(1,(班級=E2)*(座號=F2),0)

在陣列公式中,利用 MACTH 函數裡的雙條件:班級=E2和座號=F2,找尋傳回結果為「1」的位置。因為班級和座號的排列組合具唯一性,其會傳回 0 和 1 數字組成的陣列,而且其中只有一個是 1。

其中 班級=E2 和 座號=F2 會傳回判斷結果的 TRUE/FALSE 陣列,而「*」運算子相當於執行邏輯 AND 運算,運算過程中會將 TRUE/FALSE 陣列,轉換為 1/0。

(2) INDEX(姓名,第(1)式,0)

將式子(1)傳回的位置代入 INDEX 函數,傳回對應的姓名。

 

2. 使用非陣列公式

儲存格G2:=INDEX(姓名,SUMPRODUCT((班級=E2)*(座號=F2)*ROW(姓名))-1,0)

複製儲存格G2,貼至儲存格G2:G7。

(1) SUMPRODUCT((班級=E2)*(座號=F2)*ROW(姓名))-1

SUMPRODUCT 函數中利用雙條件:班級=E2和座號=F2,傳回對應姓名的列號。因為班級和座號的排列組合具唯一性,所以傳回的列號就是班級和座號對應的姓名位置。(-1在此的用意是因為第一個姓名是從第 2 列開始)

(2) INDEX(姓名,第(1)式-1,0)

將式子(1)傳回的位置代入 INDEX 函數,傳回對應的姓名。

文章標籤

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

在學校裡奉命要推廣各單位提供檔案下載時,必須提供 ODF 檔案。而觀察大家的使用習慣,在編輯文件時還是喜歡用 MS Word 、MS Excel、MS PowerPoint 等,如果配合轉成 ODF 文件,例如:PDF、ODT、HTML等檔案格式,還要進行轉換和上傳的動作,十分不方便。如何做比較方便呢?

參考以下做法。例如:要讓師生下載的原始檔案是一個 Word 檔。

1. 先將 Word 檔上傳至雲端硬碟的特定位置。

2. 開放該文件的共用權限:開啟 – 公開在網路上。

https://drive.google.com/open?id=YYYYYYYYYYYYYYY

使用這個超連結,就可以檢視 Word 檔,並且下載這個Word 檔。(目前只能下載 DOCX 格式)

ODF推廣-上傳Word檔,可以直接提供網址下載DOCX,PDF,ODT,HTML,EPUB檔案

3. 若是在雲端硬碟將此檔案以 Google 文件開啟,再設定共享文件為:開啟-公開在網路上。可以取得超連結:

https://docs.google.com/document/d/XXXXXXXXXXXX/edit?usp=sharing

利用此網址,可以透過檔案功能表來下載各種格式的檔案。

ODF推廣-上傳Word檔,可以直接提供網址下載DOCX,PDF,ODT,HTML,EPUB檔案

而你也可以提供各種網址,直接讓使用者下載想要的檔案格式:

(1) 下載 Word 檔

https://docs.google.com/document/d/XXXXXXXXXXXX/export?format=docx

將edit?usp=sharing修改為export?format=docx,以下類推。

(2) 下載 PDF 檔

https://docs.google.com/document/d/XXXXXXXXXXXX/export?format=pdf

(3) 下載 ODF 檔

https://docs.google.com/document/d/XXXXXXXXXXXX/export?format=odf

(4) 下載 HTML 檔

https://docs.google.com/document/d/XXXXXXXXXXXX/export?format=html

(5) 下載電子書檔

https://docs.google.com/document/d/XXXXXXXXXXXX/export?format=epub

如此做法,只要上傳一個檔案,不需要再轉成多個 ODF 檔案才提供下載,十分的方便。

其他如 Excel、PowerPoint等格式也是如此的做法。

特別要注意:要把存取權設定為「可以檢視」,否則A網友修改文件內容,B網友會下載到不是你原先提供的檔案。

ODF推廣-上傳Word檔,可以直接提供網址下載DOCX,PDF,ODT,HTML,EPUB檔案

文章標籤

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

使用雲端工具來共享&共創文件時,應該善用其雲端工作的特性。以下的Google試算表的例子中,有四個人共用了同一個Google試算表,如何讓儲存格內容被填入資料時,通知共享的伙伴?(如下圖左)

1. 選取儲存格B2:B4。

2. 選取「工具/通知規則」選項。

image

3. 設定通知規則,例如:有任何變動時,立即以電子郵件通知。

image

當儲存格被填入資料時,會以電子郵件通知你。

image

文章標籤

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

延續上一篇文章:Excel-從已繳交清單中找出尚未繳交作業的學生(SUMPRODUCT)

如果要將已繳交的學生顯示成績,未繳交的學生標示「X」,該如何處理?

Excel-從已繳交清單中找出繳交作業學生的成績(SUMPRODUCT)

 

【公式設計與解析】

選取儲存格A1:B300,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、座號、成績。

儲存格F2:

=IF(SUMPRODUCT((班級=F$1)*(座號=$E2)),INDEX(成績,
SUMPRODUCT((班級=F$1)*(座號=$E2)*ROW(成績))-1,0),"X")

參考:Excel-從已繳交清單中找出尚未繳交作業的學生(SUMPRODUCT)

公式:INDEX(成績,SUMPRODUCT((班級=F$1)*(座號=$E2)*ROW(成績))-1,0)

(1) SUMPRODUCT((班級=F$1)*(座號=$E2)*ROW(成績))

(班級=F$1)*(座號=$E2)*ROW(成績):在SUMPRODUCT函數中找出符合條件:班級=F$1和座號=$E2的成績列號。

(2) INDEX(成績,第(1)式-1,0)

利用 INDEX 函數,以查表方式根據第(1)式傳回的列號,顯示對應的儲存格內容。

文章標籤

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

問題:

老師們讓學生繳交作業時是透過Google表單上傳資料,Google表單也會在Google試算表中儲存學生的繳交資訊,包含班級和座號等。如果老師教授的班級較多,要在短時間內找出尚未繳交作業的學生,一筆一筆的核對,會造成老師的負擔,有沒有較快的方式?

參考下圖,如果取得了班級和座號的資訊,可以利用這個欄位,在一個班級和座號矩陣中可以尚未繳交的班級、座號上做標記。

Excel-從已繳交清單中找出尚未繳交作業的學生(SUMPRODUCT)

 

【公式設計與解析】

假設共有五個班級,每個班級20個學生,假設學生可能會重覆繳交。

1. 先選取儲存格A1:B300(為何比所有學生總和多?因為學生可能會重覆繳交。),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、座號。

2. 在儲存格E1:J21中,建立班級、座號的矩陣。

3. 輸入公式,儲存格F2:=IF(SUMPRODUCT((班級=F$1)*(座號=$E2)),"","X")

在 SUMPRODUCT 函數使用兩個條件:「班級=F$1」和=座號=$E2」。其中「*」運算子相當於執行邏輯 AND 運算

(1) 若完全符合條件者會傳回數值 1。

(2) 若同一學生繳交多次,則會傳回大於 1 的數值。

(3) 若學生未繳交,則會傳回 0。

最後,利用這個傳回值,只要是大於或等於 1 者表示有繳交,顯示空字串;若是未繳交者,則顯示「X」記號。

相同的操作和公式,可以套用在 Google 試算表中,請自行練習囉!

Excel-從已繳交清單中找出尚未繳交作業的學生(SUMPRODUCT)

文章標籤

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

今天看到媒體報導了熱血青年儲存零錢由一元開始,每天遞增一元,整年下來也存了六萬多元。只是,剛開始很輕鬆,到了後面每天要存的金額就有些壓力了。

以下就來看看每個月要負擔多少錢?(以2019年為例)第1個月存496元,第12個月要存10,850元,的確落差太大。如果可以定額每月存5,566元,應該也是不錯的好方法。以下來看看各月要存的金額如何求得?

Excel-每天遞增存1元,各月分別存多少錢?(ROW,INDIRECT,N,DATE)

 

【公式設計與解析】

1. 計算全年金額

儲存格E17:{=SUM(ROW(1:365))}

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

在陣列公式中,ROW(1:365)代表 1~365 的數值。利用 SUM 函數予以加總,在陣列公式中等同 1+2+….+364+365。

 

2. 計算各月金額

儲存格H2:

{=SUM(ROW(INDIRECT(DATE(2019,ROW(1:1),1)-N(DATE(2019,1,1))+1&":"&
DATE(2019,ROW(1:1)+1,1)-N(DATE(2019,1,1)))))}

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

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

(1) N(DATE(2019,1,1))

計算 2019年1月1日所代表的數值。(本例為:43466)

(2) DATE(2019,ROW(1:1),1)-N(DATE(2019,1,1))+1

計算各月第 1 天的數值。

(3) DATE(2019,ROW(1:1)+1,1)-N(DATE(2019,1,1))

計算各月最後 1 天的數值。

(4) INDIRECT(第(2)式&":"&第(3)式)

利用 INDIRECT 函數將第(2)式和第(3)式的傳回值組合用以轉換為儲存格範圍。

(5) ROW(INDIRECT(第(2)式&":"&第(3)式))

利用 ROW 函數將第(4)式傳回的儲存格範圍轉換為數值範圍。

(6) SUM(ROW(INDIRECT(第(2)式&":"&第(3)式)))

最後利用 SUM 函數求得數值範圍的總和。

 

 

文章標籤

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

網友問到:如何在 MS Office 中繪製下圖中的90度轉彎的線條?因為在預設的線條圖案中,沒有這一種樣式。

Word,Excel,PowerPoint-變化圖案的多樣性

首先,選取「肘型單箭頭接點」圖案,繪出一個圖型。

Word,Excel,PowerPoint-變化圖案的多樣性

接著,將中間的黃色控制點移至左邊,即可做成90度轉彎的線條。

Word,Excel,PowerPoint-變化圖案的多樣性

利用此方法,可以創造各種組合。

Word,Excel,PowerPoint-變化圖案的多樣性

同樣的手法(調整控制點),可以做出外觀落差很大的圖案:

Word,Excel,PowerPoint-變化圖案的多樣性

文章標籤

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

同事問到:如果要讓放在網頁的檔案只想讓人檢視,而不想讓人下載,該如何處理?

只要將檔案放到 Google 雲端硬碟,取得超連結後再分享,即可輕鬆解決!

首先,將檔案傳送至 Google 雲端硬碟(本例以一個 PDF 檔為例):

Google-讓網頁上的檔案只能檢視而無法下載

接著,設定該檔案的共用權限:

(1) 設定「知道連結的使用者皆可檢視」。

(2) 勾選「禁止加註著與檢視者下載、列印及複製」。

Google-讓網頁上的檔案只能檢視而無法下載

將連結在無痕模式中測試,果然!不能下載和列印。

Google-讓網頁上的檔案只能檢視而無法下載

如果取消勾選「禁止加註著與檢視者下載、列印及複製」,則可以下載,也可以新增至自己的雲端硬碟。

Google-讓網頁上的檔案只能檢視而無法下載

文章標籤

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

延續上一篇:Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

如果想要獲得 PowerApps 的相關說明,可以造訪微軟提供的資訊:

https://docs.microsoft.com/zh-tw/powerapps/user/

而且可以點選頁面左下角的「下載PDF」,這個 PDF 檔裡有超過 1500 頁的資料。

下載PowerApps的相關說明(PDF檔)

 

或是經由以下連結來下載:PowerApps說明

下載PowerApps的相關說明(PDF檔)

文章標籤

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

如果你在電腦中建立了一個 Excel 工作表,如何能藉由手機 App 在行動裝置中使用?

Microsoft 推出的 PowerApps 可以讓你即使沒有寫任何手機程式,也可以達到用手機查詢、修改和新增資料的功能。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

首先,要將該資料表轉成「表格」(在「插入」工作表中選取「表格」),並儲存檔案。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

接著,將該資料上傳至雲端(本例為上傳至 Microsoft Office 365 的 OneDrive。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

再來,開啟你 Office 365 中的 PowerApps:

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

選取從資料開始中的「製作此應用程式」。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

然後,選取一個資料來源(本例選取微軟的「商務用 OneDrive」)。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

選取先前上傳的工作表:

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

再選取一個工作表,按一下「連接」:

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

會出現預設的版面設計:(本例共有三個手機畫面,例如:BrowseScreen1、DataScreen1、EditScreen1)

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

你可以善用「首頁」和「插入」功能表下的工作列來新增物件和設定物件格式:

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

當你選取一個物件,視窗右側還有相關工具可供使用來進一步設定。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

然後,選取「儲存」。輸入檔案的名稱,按「儲存」。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

最後還要按下「發行」。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

在手機裡,打開 PowerApps,點選應用程式(本例為:員工資料查詢),可以看到資料表的內容。點選「〉」,可以在另一個面頁看到完整的項目內容,還可以調整排序。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式) Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

可以點選筆型圖示,可以修改資料,點選「+」,還可以新增資料。

Excel-將資料放至雲端並用PowerApps來查詢(手機程式) Excel-將資料放至雲端並用PowerApps來查詢(手機程式)

文章標籤

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

讀者提問:在 Excel 的工作表中,每一列有一些項目,如何找出每一列中,出現最多次的項目?(參考下圖)

我參考了其他網友的做法,挑了一個最精簡的公式。

Excel-找出清單中出現最多次的項目(INDEX,MATCH,MODE)

 

【公式設計與解析】

儲存格B2:{=INDEX(C2:N2,MODE(MATCH(C2:N2,C2:N2,0)))}

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

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

(1) MATCH(C2:N2,C2:N2,0)

在陣列公式中,找出儲存格C2:N2中每一個儲存格在儲存格C2:N2中第一次出現的位置。

本例結果為:{1, 1, 3, 4, 1, 3, 7, 1, 1, 4, 11, 12}

Excel-找出清單中出現最多次的項目(INDEX,MATCH,MODE)

 

(2) MODE(MATCH(C2:N2,C2:N2,0))

藉由 MODE 函數找出第(1)式傳回值中出現最多的數字(第一次出現的位置)。

本例結果為:1

Excel-找出清單中出現最多次的項目(INDEX,MATCH,MODE)

 

(3) INDEX(C2:N2,MODE(MATCH(C2:N2,C2:N2,0)))

在 INDEX 函數中將第(2)式傳回值,利用查表方式找到對應的儲存格內容。

本例結果為:寅

Excel-找出清單中出現最多次的項目(INDEX,MATCH,MODE)

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼