贊助廠商

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

搜尋本部落格文章資料

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

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

(網友提問)在 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,只是一個很大的數字。

(2) OFFSET($B$2,0,0,COUNTA($B$2:$B$199),1)

將第(1)式代入 OFFSET 函數,可以求得含有文字的儲存格範圍。

同理:

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

 

【另解】

如果使用 SUMIF($B$2:$B$19,E2,$C$2:$C$19) 時,能將儲存格範圍盡量放大一些,如此貼上新資料時,也不需要改公式。

文章標籤

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))

儲存格Q2:=TIME(MID(E2,11,2),MID(E2,14,2),0)

儲存格R2:=DATE(MID(E2,18,3)+1911,MID(E2,22,2),MID(E2,25,2))

儲存格S2:=TIME(MID(E2,28,2),MID(E2,31,2),0)

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

注意:前兩個欄位(Subject和Start date)是必要欄位,其他欄位為選用。欄位名稱必須是英文,任何活動詳細資料中含有逗號,要在文字的左右兩端加上引號。

將轉換後的資料儲存格 CSV 檔,然後在 Google 日曆設定中將該 CSV 檔匯人即可。

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

文章標籤

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) 人氣()

在 Excel 中如下圖的資料表,其中每箱有12盒,每次貨物出庫後,希望能自動計算庫存現在的盒數和箱數。

謝謝網友提問,剛好來練習 INT 函數和 MOD 函數。

Excel-箱數和盒數的換算(INT,MOD)

 

【公式設計與解析】

儲存格A4:=INT(((A3*12+B3)-(C3*12+D3))/12)

儲存格B4:=MOD(((A3*12+B3)-(C3*12+D3)),12)

複製儲存格A4:B4,貼至儲存格A15:B15。

(A3*12+B3)-(C3*12+D3):將現有庫存數量減扣出貨數量,得到現有總盒數。

INT 函數可以取得不大於(小於或等於)運算結果除以 12 的商之最大整數。

MOD 函數可以取得運算結果除以 12 的餘數。

文章標籤

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

網友想要將 Excel 的兩個活頁簿,各有一個工作表顯示另一個工作表的內容,該如何處理?

假設第一個活頁簿(A)裡有一個工作表 AAA,第二個活頁簿(B)裡有一個 BBB 工作表,要分別顯示在另一個活頁簿的工作表中。

Excel-讓不同活頁簿的不同工作表內容能連動

以下的做法可以做到,但不知是否有其他更好的方法。參考以下步驟:

1. 在 A 活頁裡,有一個工作表「AAA」,再新增一個工作表「BBB」。

2. 選取「資料/取得外部資料」裡的「現有連線」。

Excel-讓不同活頁簿的不同工作表內容能連動

3. 選取「瀏覽更多」按鈕。

Excel-讓不同活頁簿的不同工作表內容能連動

4. 選取 B 活頁簿,再選取「BBB」工作表。

Excel-讓不同活頁簿的不同工作表內容能連動

5. 在「匯入資料」對話框中,選取「目前工作表的儲存格」,並且點選儲存格A1,再按「確定」按鈕。

Excel-讓不同活頁簿的不同工作表內容能連動

B 活頁簿裡的 BBB 工作表內容會被連接至此。

Excel-讓不同活頁簿的不同工作表內容能連動

相同做法,可以在 B 活頁簿裡連接 A 活頁簿裡的 AAA 工作表。

Excel-讓不同活頁簿的不同工作表內容能連動

如果 B 活頁裡的 BBB 工作表有更新內容:

Excel-讓不同活頁簿的不同工作表內容能連動

則,在 A 活頁簿裡的 BBB 工作表中,選取「重新整理」或是「重新整理/連線內容」:

Excel-讓不同活頁簿的不同工作表內容能連動

按下「確定」按鈕:

Excel-讓不同活頁簿的不同工作表內容能連動

BBB 工作表的內容會隨即更新為 B 活頁簿裡 BBB 工作表的內容:

Excel-讓不同活頁簿的不同工作表內容能連動

文章標籤

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

在利用 PowerPoint 製作簡報的時候,如果能利用 Word 文件中的內容,轉換至 PowerPoint 的投影片中,可以大為提升效率。

可以參考以下的做法:

下圖的文件中,已有一些文字資料。已分別將標題的段落格式設定大綱階層為「第1層」,標題下文字的段落格式設定大綱階層為「第2層」。(看起來像下圖)

將Word文件中的內容放到PowerPoint簡報的投影片中

(大綱階層在「段落」中設定)

將Word文件中的內容放到PowerPoint簡報的投影片中

(記得這個 Word 檔要在關閉的狀態下)然後,在 PowerPoint 中選取「新增投影片/從大綱」:

將Word文件中的內容放到PowerPoint簡報的投影片中

Word 文件內容就會自動乖乖的分派到多張投影片中了:

將Word文件中的內容放到PowerPoint簡報的投影片中

文章標籤

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

在 Excel 中輸入資料時(參考下圖),常常會需要不斷的增加資料,公式該如何處理?而在下圖的清單裡,其中有多個料號,每個料號有各自的前期剩餘,如何計算每個料號的本期剩餘?

Excel-在持續新增的資料中找出各項最後一個數值(SUMPRODUCT,OFFSET)

 

【公式設計與解析】

1. 計算前期剩餘

儲存格B7:

=OFFSET($E$1,SUMPRODUCT(MAX(($A$2:A6=A7)*ROW($E$2:E6)))-1,0)

複製儲存格B7,貼至儲存格B7:B29。

(1) $A$2:A6=A7

SUMPRODUCT 函數中找出符合條件的陣列(由儲存格A2起始至儲存格A6的範圍中和儲存格A7相同者),傳回 TRUE/FALSE 陣列。

(2) ($A$2:A6=A7)*ROW($E$2:E6)

利用 ROW 函數傳回儲存格E2:E6中每個儲存格的列號(本例:2~6),運算子「*」在計算過程中,相當於執行邏輯 AND 運算,並且會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

(3) MAX(($A$2:A6=A7)*ROW($E$2:E6))

SUMPRODCUT 函數中,利用 MAX 函數找出第(2)式傳回的運算結果之最大值,即為最大的列號,也就是符合料號的最後一個儲存格。

(4) OFFSET($E$1,SUMPRODUCT(第(3)式)-1,0)

將第(3)式傳回的列號代入 OFFSET 函數中傳回對應的儲存格內容。

 

2. 計算本期剩餘

儲存格H2:

=OFFSET($E$1,SUMPRODUCT(MAX(($A$2:$A$29=G2)*ROW($E$2:$E$29)))
-1,0)

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

公式運算原理同「1. 計算前期剩餘」。注意其儲存格範圍的差異!

 

3. 持續新增資料但不改公式

因為該資料清單會不斷的新增,所以只要在第30列(淺綠色)上按右鍵,新增一列即可複製公式並新增內容,但是公式中的儲存格範圍會自動調整,使用者不同再更改。

image

文章標籤

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

在 Excel 中輸入一個時間區段,常有人為了求方便而輸入「1030-1345」來表示,如何轉換為「10:30-13:45」?又如何計算時間區段中的小時數?

Excel-時間表示的轉換和計算相距時間(TIME,TEXT,MID,ROUND)

 

【公式設計與解析】

1. 轉換表示方式

儲存格C2:=TEXT(TIME(MID(A2,1,2),MID(A2,3,2),),"hh:mm")&"-"&
TEXT(TIME(MID(A2,6,2),MID(A2,8,2),),"hh:mm")

(1) TIME(MID(A2,1,2),MID(A2,3,2),)

利用 MID 函數取出第 1 段字串的前 2 個數字當為「時」,取出第 1 段字串的後 2 個數字當為「分」。利用 TIME 函數組成標準的時間表示。

(2) TEXT(第(1)式,"hh:mm")

利用 TEXT 函數將第(1)式中的標準時間表示轉換為「時2碼:分2碼」的表示方式。

(3) TEXT(TIME(MID(A2,6,2),MID(A2,8,2),),"hh:mm")

將第 2 段字串的前 2 個數字當為「時」,取出第 1 段字串的後 2 個數字當為「分」。並轉換為「時2碼:分2碼」的表示方式。

 

2. 計算相距時間

儲存格E2:=ROUND((TIME(MID(A2,6,2),MID(A2,8,2),)-TIME(MID(A2,1,2),
MID(A2,3,2),))*24,2)

(1) TIME(MID(A2,6,2),MID(A2,8,2),)-TIME(MID(A2,1,2),MID(A2,3,2),)

利用 TIME 函數取出第 2 段和第 1 段的時間並相減。

(2) ROUND(第(1)式*24,2)

利用 ROUND 函數取數值為小數點 2 位。

文章標籤

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

有老師問到:如下圖左所示的配課資料,如何整理成下圖右的資料表?

下圖中,原始的配課資料是由上而下的表格式資料呈現,要改成以矩陣形式的資料呈現方式。

Excel-將表格資料改以矩陣形式呈現(以課表為例)

 

【公式設計與解析】

先將配課資料定義名稱,選取儲存格A1:D25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、節次、教師、科目。

Excel-將表格資料改以矩陣形式呈現(以課表為例)

Excel-將表格資料改以矩陣形式呈現(以課表為例)

假設配課資料放在「資料」工作表中。

儲存格B2:=OFFSET(資料!$D$1,SUMPRODUCT((教師=$A2)*(節次=B$1)*
ROW(科目))-1,0)&CHAR(10)&OFFSET(資料!$A$1,SUMPRODUCT((教師=$A2)*
(節次=B$1)*ROW(科目))-1,0)

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

(1) OFFSET(資料!$D$1,SUMPRODUCT((教師=$A2)*(節次=B$1)*ROW(科目))-1,0)

該式可以取得符合條件者對應的「科目」。

SUMPRODUCT 函數中利用二個條件:(教師=$A2)*(節次=B$1),找出二個條件都符合者,其中運算子「*」,相當於執行邏輯 AND 運算。

SUMPRODUCT((教師=$A2)*(節次=B$1)*ROW(科目))會傳回符合二個條件者的科目之列號。

將上式傳回的列號代入 OFFSET 函數取得該列號對應的儲存格內容。

(2) CHAR(10)

如果要在一個儲存格中要分多列顯示,可以藉助 Alt+Enter 鍵。如果要使用公式來將儲存格內容分列顯示,則可以使用 CHAR(10)

(3) OFFSET(資料!$A$1,SUMPRODUCT((教師=$A2)*(節次=B$1)*ROW(科目))-1,0)

該式可以取得符合條件者對應的「班級」。

Excel-將表格資料改以矩陣形式呈現(以課表為例)

文章標籤

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

有老師問到:想要利用 Excel 的表格來排課程(如下圖),根據教師、日期和上課時間,如何能標示是否把老師的課排成衝堂了?

同一個教師在相同日期的相同時間裡,只能被安排一門課,超過一節就是衝堂。

Excel-檢查課程是否衝堂(SUMPRODUCT)

 

【公式設計與解析】

現在要設計如果有衝堂時,則在該節位置設定淺綠色儲存格底色。

1. 選取儲存格F2:M11。

2. 設定格式化的條件。

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

規則:

=SUMPRODUCT(($B$2:$B$11=$B2)*($E$2:$E$11=$E2)*(F$2:F$11="V"))>1

格式:儲存格底色設定為淺綠色

合於三個條件:(($B$2:$B$11=$B2)*($E$2:$E$11=$E2)*(F$2:F$11="V")者,如果加總結果大於 1,表示有 1 個以上的衝堂。

每一個條件(例如:$B$2:$B$11=$B2)會傳回 TRUE/FALSE 的陣列,而運算式中的「*」運算子,相當於執行邏輯 AND 的運算。運算時會將 TRUE/FALSE 的陣列轉換為 1/0 陣列。

Excel-檢查課程是否衝堂(SUMPRODUCT)

每一個儲存格的運算結果如下:

Excel-檢查課程是否衝堂(SUMPRODUCT)

文章標籤

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

接續上一篇文章:Excel-計算符合多條件的個數(COUNTIFS,SUMPRODUCT)

在多條件的運算中,有些要執行邏輯 AND 運算,有些要執行邏輯 OR 運算。以下的例子中,是要求取符合多個被選取的星期幾者的小計。(參考下圖)

Excel-多條件的OR運算(SUMIFS,SUMPRODUCT,WEEKDAY)

 

【公式設計與解析】

選取A欄至D欄中的資料,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、星期、數量。

 

1. 利用 SUMIF 函數加總多個結果

儲存格F5:=SUMIFS(數量,人員,F$4,星期,"星期一")+SUMIFS(數量,人員,F$4,星期,
"星期三")+SUMIFS(數量,人員,F$4,星期,"星期五")

如果使用三個 SUMIF 函數,分別求取固定條件:「星期一、星期三、星期三」的結果,但是該結果沒有彈性,條件更改時,要修改參數。

 

2. 利用陣列公式加總多個結果

{=SUM(IF(人員=F$4,IF(星期={"星期一","星期三","星期五"},數量),""))}

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

如果利用陣列公式,可簡化公式長度。其中公式「SUM(IF(…」和函數「SUMIF」的觀念相同。

 

3. 利用 SUMPRODUCT 函數加總多個結果

儲存格F5:=SUMPRODUCT((WEEKDAY(日期,2)=($F$2:$L$2="V")*COLUMN
($A:$G))*(人員=F$4)*數量)

如果利用 SUMPRODUCT 函數,則可以達到較為彈性的方式來計算每個人員選取不同星期幾的小計。

條件:WEEKDAY(日期,2)=($F$2:$L$2="V")*COLUMN($A:$G)

WEEKDAY(日期,2):傳回數字 1~7 代表星期一 ~星期日。

Excel-多條件的OR運算(SUMIFS,SUMPRODUCT,WEEKDAY)

其傳回順序,恰好對應圖中的儲存格F1:L1。

Excel-多條件的OR運算(SUMIFS,SUMPRODUCT,WEEKDAY)

COLUMN($A:$G):代表 1~7 的陣列。

($F$2:$L$2="V")*COLUMN($A:$G):在陣列中會傳回 $F$2:$L$2="V" 條件成立者對應的COUMN 數。

 

【延伸練習】

如果要將第2式改為像第3式是只計算有勾選的項目,該如何處理公式?

參考答案:

儲存格F5:{=SUM(IF(人員=F$4,IF(WEEKDAY(日期,2)=($F$2:$L$2="V")*COLUMN
($A:$G),數量),""))}

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

文章標籤

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

在 Excel 中,帶小數的數值有可能會要以分數呈現,該如何做小數和分數格式的轉換?(參考下圖)

image

 

【格式設定】

1. 小數(指定小數點 4 位)

在儲存格格式中設定,格式:0.0000

2. 分數(指定分母 10000)

在儲存格格式中設定,格式:?/10000

3. 分數(指定分母 1000)

在儲存格格式中設定,格式:?/1000

因為小數是 4 位,而分數的分母是 10000,所以分子會四捨五入至最接近值。

4. 分數(指定分母 25000)

在儲存格格式中設定,格式:?/25000

因為小數是 4 位,而分數的分母是 25000,所以分子會四捨五入至最接近值。

Excel-小數和分數的格式轉換

文章標籤

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

在Microsoft Office 2016中有提供增益集功能,以擴充原有功能。以下要以建立QR Code為例來操作。

1. 在「插入」功能表中點選「我的增益集」中的「查看全部」。

在Microsoft Office 2016中使用增益集-以建立QR Code為例

目前我的增益集中是空的,點選:Office市集,從市集來增加。

在Microsoft Office 2016中使用增益集-以建立QR Code為例

有些增益集需要付費,找到你想要的(本例:Office QR)。

在Microsoft Office 2016中使用增益集-以建立QR Code為例

新增成功後,會產生該增益集的功能窗格。

在Microsoft Office 2016中使用增益集-以建立QR Code為例

設定 QR Code 的內容後,即可新增 QR Code 圖片。

在Microsoft Office 2016中使用增益集-以建立QR Code為例

例如:翻譯工具。

在Microsoft Office 2016中使用增益集-以建立QR Code為例

Word to HTML:

在Microsoft Office 2016中使用增益集-以建立QR Code為例

文章標籤

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

在 Excel 中,如果使用 COUNTIF 函數,可以計算儲存格範圍內符合一個條件的個數,如果要在多個範圍內計算符合多個條件的個數,則要利用 COUNTIFS 函數。

Excel-計算符合多條件的個數(COUNTIFS,SUMPRODUCT)

如果你使用的 Excel 版本沒有提供 COUNTIFS 函數,可以使用 SUMPRODUCT 函數取代。例如:

使用 COUNTIFS 函數:

儲存格F2:=COUNTIFS(國文,">=60",英文,">60",數學,">=60")

其中的三個條件「國文,">=60"」、「英文,">60"」、「數學,">=60"」,是以邏輯 AND 執行運算。

SUMPRODUCT 函數取代:

儲存格F2:=SUMPRODUCT((國文>=60)*(英文>=60)*(數學>=60))

其中的「*」運算,也是執行邏輯 AND 運算

文章標籤

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

在 Excel 的日期清單中(如下圖),如何找出每個月、每個星期幾的最大值和最小值?

本篇練習重點:陣列公式、名稱定義、WEEKDAY 函數、MONTH 函數、ROW 函數、MAX 函數、MIN 函數、IF 函數、。

image

 

【公式設計與解析】

選取儲存格A1:C50,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、數值。

 

1. 每個星期幾的最大值

 

儲存格F2:{=MAX(IF(WEEKDAY(日期,2)=ROW(1:1),數值,""))}

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

(以下各個公式都是陣列公式)

複製公式儲存格F2,貼至儲存格F2:F8。

在陣列公式中,利用 WEEKDAY 函數利用參數「2」(值回值1~7對應星期一~星期日),判斷是否為星期一(ROW(1:1)=1),如果是星期一者,傳回對應的數值陣列。

image

當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

根據傳回對應的數值陣列,利用 MAX 函數取得其中的最大值。

2. 每個星期幾的最小值

儲存格G2:{=MIN(IF(WEEKDAY(日期,2)=ROW(1:1),數值,""))}

複製公式儲存格G2,貼至儲存格G2:G8。

根據傳回對應的數值陣列,利用 MIN 函數取得其中的最小值。

3. 每個月的最大值

儲存格F11:{=MAX(IF(MONTH(日期)=ROW(1:1),數值,""))}

複製公式儲存格F11,貼至儲存格F11:F16。

在陣列公式中,利用 MONTH 函數判斷是否為一月(ROW(1:1)=1),如果是一月,傳回對應的數值陣列。

4. 每個月的最小值

儲存格G11:{=MIN(IF(MONTH(日期)=ROW(1:1),數值,""))}

複製公式儲存格G11,貼至儲存格G11:G16。

5. 每個月各個星期幾的最大值

儲存格G19:{=MAX(IF((WEEKDAY(日期,2)=ROW(1:1))*(MONTH(日期)=
COLUMN(A:A)),數值,""))}

複製公式儲存格G19,貼至儲存格G19:K25。

其中「WEEKDAY(日期,2)=ROW(1:1))*(MONTH(日期)=COLUMN(A:A))」的「*」,乃執行邏輯 AND 運算。即要符合二個條件者,傳回其對應的數值陣列。

文章標籤

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

今天為學生上課時,希望學生能在短短的課程中,利用 PowerPoint 的「動畫」功能來製作一些簡單的動畫效果。

利用動畫窗格的時間軸,使用「與前動畫同時」或「接續前動畫」的動作,再配合設定動畫期間及延遲時間,各種排列組合之下,可以巧妙做出許多動畫效果。在動畫窗格中,配合時間軸,可以看到每個動畫播出的時間點和播放的長度。而進入效果、強調效果、結束效果分別以不同色彩表示。

PowerPoint-善用動畫功能製作動畫

範例一:

PowerPoint-善用動畫功能製作動畫

(旋轉物件的旋轉方向,因錄製軟體讓你的視覺而有順時針或逆時針旋轉的差異。)

設定如下:

(1) 向下箭號:飛入;效果選項:自右下角

【動畫設定】開始:接續前動畫/期間:03:00/延遲:00:00

(2) 群組6:陀螺轉;效果選項:順時針

【動畫設定】開始:接續前動畫/期間:03:00/延遲:01:50

PowerPoint-善用動畫功能製作動畫 PowerPoint-善用動畫功能製作動畫

範例二:

PowerPoint-善用動畫功能製作動畫

先利用半框架物件做出一個如果的樣式。配合新增動畫,分別製作「進入」(綠色星號)和「結束」(紅色星號)的動畫,再搭配「與前動畫同時」或「接續前動畫」的動作,再配合設定動畫期間及延遲時間。

設定如下:

(1) 半框架1:進入/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:00:00

(2) 半框架2:進入/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:00:50

(3) 半框架1:結束/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:01:00

(4) 半框架3:進入/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:01:00

(5) 半框架2:結束/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:01:50

(6) 半框架4:進入/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:01:50

(7) 半框架3:結束/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:02:00

(8) 半框架5:進入/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:02:00

(9) 半框架4:結束/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:02:50

(10) 半框架5:結束/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:03:00

PowerPoint-善用動畫功能製作動畫

範例三:

PowerPoint-善用動畫功能製作動畫

1. 先找一張圖片,將圖片執行去背處理(格式/移除背景),產生小狗狗獨立圖片。

2. 複製小狗狗獨立圖片,再產生一張垂直翻轉/改變高度/重新著色的圖片當為陰影。

PowerPoint-善用動畫功能製作動畫

3. 將小狗狗獨立圖片和原始圖片重疊,並設定小狗狗獨立圖片在最上層。

4. 選取原始圖片,設定如下:

(1) Picture2(原圖):結束/淡出

【動畫設定】開始:接續前動畫/期間:04:00/延遲:00:00

(2) Picture2(陰影):進入/淡出

【動畫設定】開始:與前動畫同時/期間:01:00/延遲:03:00

PowerPoint-善用動畫功能製作動畫 PowerPoint-善用動畫功能製作動畫

文章標籤

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

網友常問到在 Excel 中要執行多條件的小計相關問題,以下的例子(參考下圖),是利用 SUMPRODUCT 函數和 SUMIFS 函數來加以比較,以利學習。

Excel-清單中的多條件小計(SUMPRODUCT,SUMIFS)

 

【公式設計與解析】

首先,定義名稱。

選取儲存格A1:C40,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、動作、金額。

Excel-清單中的多條件小計(SUMPRODUCT,SUMIFS)

名稱定義,結果如下:

Excel-清單中的多條件小計(SUMPRODUCT,SUMIFS)

接著,輸入公式。

1. 使用 SUMIFS 函數

儲存格F3:=SUMIFS(金額,日期,$E$1,動作,$E3&"*",動作,"*"&F$2)

其中:

條件一:日期,$E$1

在日期陣列中取得符合儲存格E1內容者。

條件二:動作,$E3&"*"

「$E3&"*"」使用萬用字「*」,取得「動作」陣列中符合儲存格E3內容起始的文字。

條件三:動作,"*"&F$2

「"*"&F$2」使用萬用字「*」,取得「動作」陣列中符合儲存格F2內容結尾的文字。

 

2 使用 SUMPRODUCT 函數

儲存格F3:

=SUMPRODUCT((日期=$E$1)*(LEFT(動作,1)=$E3)*(RIGHT(動作,1)=F$2)*金額)

條件一:日期=$E$1

在日期陣列中取得符合儲存格E1內容者。

條件二:LEFT(動作,1)=$E3

利用 LEFT 函數,取得「動作」陣列中最前一個字元符合儲存格E3內容者。

條件三:RIGHT(動作,1)=F$2

利用 RIGHT 函數,取得「動作」陣列中最後一個字元符合儲存格E3內容者。

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼