贊助廠商

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

搜尋本部落格文章資料

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

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

在 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 發表在 痞客邦 留言(2) 人氣()

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

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

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

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

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

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

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

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

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

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

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

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

文章標籤

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 陣列。

文章標籤

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

文章標籤

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)

文章標籤

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

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

文章標籤

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 函數,分別求取固定條件:「星期一、星期三、星期三」的結果,但是該結果沒有彈性,條件更改時,要修改參數。

 

文章標籤

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

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

image

 

【格式設定】

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

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

2. 分數(指定分母 10000)

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

3. 分數(指定分母 1000)

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

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

4. 分數(指定分母 25000)

文章標籤

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為例

例如:翻譯工具。

文章標籤

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 會自動加上「{}」。

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

文章標籤

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

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

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

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

範例一:

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

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

設定如下:

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

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

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

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

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)

文章標籤

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

在 Excel 的數值清單中,如何將阿拉伯數字轉換為國字數字的幣值表示(而且要含元、角、分)?

參考下圖,每個數值都要轉換為元、角、分,該如何處理?

轉換數值顯示國字數字的元、角、分(TEXT,INT,RIGHT,LEFT)

 

【公式設計與解析】

在 Excel 中如果將數值的格式設定為:文字/壹萬貳仟參佰肆拾五,即可阿拉伯數字轉換數值為國字數字。

轉換數值顯示國字數字的元、角、分(TEXT,INT,RIGHT,LEFT)

相當於自訂格式的:[DBNum2]

轉換數值顯示國字數字的元、角、分(TEXT,INT,RIGHT,LEFT)

但是以上做法無法針對角和分的數值來處理。只好以公式自行來訂定:

儲存格B2:=TEXT(INT(A2),"[DBNum2]")&"元"&TEXT(LEFT(RIGHT(A2*100,2),1),
"[DBNum2]")&"角"&TEXT(RIGHT(A2*100,1),"[DBNum2]")&"分"

文章標籤

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

在 Excel 的使用上,偶有同仁問到為何儲存格顯示的是公式而非運結果?

Excel-為何儲存格顯示的是公式內容而非公式運算的結果

推測可能的原因:

1. 不小心按到了:Ctrl+~ 鍵

按下 Ctrl+~ 鍵,可以切換儲存顯示公式或是結果。

解決方案:再按一次 Ctrl+~ 鍵。

 

2. 在已被設定數值格式為:文字的儲存格中輸入公式。

所輸入的公式被認為是文字來顯示,所以沒有執行公式的運算。

解決方案:將儲存格的數值格式設定為「一般」,再重新輸入公式。

(若不想重新輸入公式,可以在公式最後輸入一個空白即可。)

Excel-為何儲存格顯示的是公式內容而非公式運算的結果

文章標籤

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

多位網友問到:在 Excel 中如何使用下拉式清單時,已經選過的項目不再顯示,即如何才能不選到重覆的項目?

參考下圖,已被選過的項目,不會在下拉式清單中出現,所以不會再被選到。

Excel-在下拉式清單中顯示未被選取項目(OFFSET,COUNTIF,ROW,SMALL)

 

【公式設計與解析】

我沒有好的解決方案,只能利用一個輔助欄位(D欄)來將未被選取者,列出資料清單。

儲存格D2:{=IFERROR(OFFSET($C$1,SMALL(IF(COUNTIF($A$2:$A$11,
$C$2:$C$11)=0,ROW($C$2:$C$11),""),ROW(1:1))-1,0),"")}

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

複製儲存格D2,貼至儲存格D2:D11。

(1) COUNTIF($A$2:$A$11,$C$2:$C$11)

在陣列公式中,利用 COUNTIF 函數計算原始資料中的每一個項目,在已選欄位中出現的次數。(若為 0,表示尚未被選,若為 1,表示已經被選取。 )

文章標籤

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

根據 Excel 資料表中(參考下左)的資料清單,想要篩選出合於類別並且和指定數量接近的資料,該如何處理?

本例要依兩個條件:類別、數量,來篩選資料。以指定數量-99~+99為接近值。

Excel-多條件篩選資料(OFFSET,SMALL,ROW)

 

【公式設計與解析】

選取儲存格A1:E100,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、類別、編號、數量、狀態。

儲存格H2:{=IFERROR(OFFSET($A$1,SMALL(IF((類別=$G$2)*(數量<=$G$4+99)*
(數量>=$G$4-99),ROW(編號),""),ROW(1:1))-1,0),"")}

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

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

(1) IF((類別=$G$2)*(數量<=$G$4+99)*(數量>=$G$4-99),ROW(編號),"")

條件一:(類別=$G$2),

文章標籤

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

在 Excel 的工作表中,如果想要以公式來取出分隔符號(例如:*,",',~,?)內的文字,該如何處理?

參考下圖,以「*,",',~,?」為例:

Excel-取出分隔符號內的文字(SUBSTITUTE,REPT)

 

【公式設計與解析】

為了方便說明,先建立輔助欄位。

儲存格B2:=SUBSTITUTE(A2,"*",REPT(" ",20))

先利用 SUBSTITUTE 函數將分隔符號置換為20個空白字元。(20只是一個遠大於儲存格內字數的數值。而空白字元是因為原字串中都沒有空白字元。)

REPT 函數可以產生多個重覆的指定字元。

儲存格B3:=SUBSTITUTE(A3,"""",REPT(" ",20))

特別注意:若要置換「"」為20個空白字元,則公式要使用「""""」(4個")。

儲存格C2:=SUBSTITUTE(MID(B2,20,20)," ","")

文章標籤

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

在 Excel 中有一個日期的清單,當要將日期加2天並且要跳過星期六、日,該如何處理?例如:

星期四加2天,應是星期六,則要調整為星期一。

星期五加2天,應是星期日,則要調整為星期二。

星期六加2天,應是星期一,則要調整為星期二。

Excel-計算隔2天日期並跳過星期六和星期日(WEEKDAY,CHOOSE)

 

【公式設計與解析】

儲存格E2:=A2+2+CHOOSE(WEEKDAY(A2,2),0,0,0,2,2,1,0)

使用 WEEKDAY 函數來傳回是星期幾。再利用 CHOOSE 函數微調日期。

Excel-計算隔2天日期並跳過星期六和星期日(WEEKDAY,CHOOSE)

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼