2018年8月31日 星期五

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

在 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

在地圖查詢地址(臺灣通用電子地圖)

朋友問到:如果在 Google 地圖上使用地址查詢位置,當在地圖上已被加入地點,並且含有地址資訊者,可以查到精確位置。但是如果像是一般住家,則只能查到約略位置。如何能知道每一個地址的精確位置?
如果想要查詢有被分配地址的位置,可以靠政府建立的「臺灣通用電子地圖」。
臺灣通用電子地圖:https://maps.nlsc.gov.tw/
在地圖查詢地址(臺灣通用電子地圖)
這是 Google 地圖:
在地圖查詢地址(臺灣通用電子地圖)
這是臺灣通用電子地圖,最明顯的差異是地圖上有清楚標示每個地址的所在位置。
在地圖查詢地址(臺灣通用電子地圖)
還可以選不同的底圖:(航照圖、不含門牌等)
在地圖查詢地址(臺灣通用電子地圖)
例如:等高線圖。
在地圖查詢地址(臺灣通用電子地圖)
停車場是預設的顯示項目:
在地圖查詢地址(臺灣通用電子地圖)
這是 Google 搜尋「停車場」的結果:
在地圖查詢地址(臺灣通用電子地圖)
點選地圖上的位置,可以得到這地點的經緯度和地號,還可以查詢土地資訊和地段資訊。
在地圖查詢地址(臺灣通用電子地圖)
可以在地圖上量測面積:
在地圖查詢地址(臺灣通用電子地圖)
視窗右上角可以快速執行底圖切換、圖層設定、定位查詢等。
在地圖查詢地址(臺灣通用電子地圖)
利用手機的瀏覽器來檢視臺灣通用電子地圖(https://maps.nlsc.gov.tw/),也是相當實用。
在地圖查詢地址(臺灣通用電子地圖) 在地圖查詢地址(臺灣通用電子地圖)
在地圖查詢地址(臺灣通用電子地圖) 在地圖查詢地址(臺灣通用電子地圖)

2018年8月30日 星期四

Windows 10-將手機正在瀏覽的網頁傳輸至電腦繼續檢視

日常生活中使用手機來行動上網已是家常便飯了!但是使用電腦的瀏覽網頁仍有其優勢,如何讓兩個裝置無縫接軌呢?讓手機正在檢視的網頁,也能立即在電腦中瀏覽。
在 Windows 10 中可以設定電腦和手機的連接,為電腦和行動裝置搭起橋樑。做法如下:
按 WinKey+I 鍵,開啟「Windwos 設定」視窗,再點選「電話」。
Windows 10-將手機正在瀏覽的網頁傳輸至電腦繼續檢視
在此依指示將 Windows 10 設定要連結至你的哪一隻手機。(設定好會出現手機型號)
Windows 10-將手機正在瀏覽的網頁傳輸至電腦繼續檢視
在使用手機的 Edge 瀏覽器瀏覽網頁時,按一下視窗最下方的傳送圖示,再選取一個電腦名稱(原先已設定好連結的電腦名稱)。
Windows 10-將手機正在瀏覽的網頁傳輸至電腦繼續檢視 Windows 10-將手機正在瀏覽的網頁傳輸至電腦繼續檢視
你的電腦會自動啟動 Edge 瀏覽器,並且開啟相同網頁內容。(不管電腦是否就在你身邊,只要是電腦在開啟狀態下即可。)
Windows 10-將手機正在瀏覽的網頁傳輸至電腦繼續檢視
而如果你是要將手機中的 Chrome 瀏覽器內容傳送至電腦,則可以選取功能選單中的「分享」,再選取「在電腦端繼續」。則電腦依然會自動啟動 Edge 瀏覽器,並且開啟相同網頁內容。
Windows 10-將手機正在瀏覽的網頁傳輸至電腦繼續檢視 Windows 10-將手機正在瀏覽的網頁傳輸至電腦繼續檢視

【延伸學習】
如果你想要將 Chrome 瀏覽器中的網址在 Edge 瀏覽器中開啟,也是可以利用「分享」的功能,再選取「Edge」即可。
Windows 10-將手機正在瀏覽的網頁傳輸至電腦繼續檢視

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

在 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 位。

2018年8月28日 星期二

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

有老師問到:如下圖左所示的配課資料,如何整理成下圖右的資料表?
下圖中,原始的配課資料是由上而下的表格式資料呈現,要改成以矩陣形式的資料呈現方式。
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-將表格資料改以矩陣形式呈現(以課表為例)

2018年8月21日 星期二

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

有老師問到:想要利用 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)

2018年8月14日 星期二

小心!Google小組硬碟成員的權限,至少要有一個具有完整權限。

Google雲端硬碟中的「小組硬碟」是一個十分好用的工具,我在教學過程中建立一個測試用的小組硬碟,在邊講解邊操時,不小心就產生了一個無法處理的狀況。提出來供參考,避免以後類似狀況發生。
一個小組硬碟可以加入其他成員,在管理這些人的權限時,會有四種選項可以選取:「完整權限、編輯權限、註解權限、檢視權限」,權限由大到小,其中完整權限可以管理成員。
Google-小心!小組硬碟成員的權限,至少要有一個具有完整權限。
(如下圖)當新增一位成員後,不小心將自己(最初管理者)的權限設定為「編輯」,第二個成員的權限設定為「註解」。
因為完整權限者才有權刪除成員和該小組硬碟,所以目前的二位成員都不具備此項權限。所以該小組硬碟既無法刪除,也無法移除成員,也無法更改成員的權限。因此該小組硬碟就會像幽靈一般的存在,但卻無法有其他的功能。
其實 Google 應該要在操作過程中,檢查每個小組硬碟是否至少要存在一個成員具有完整權限,不能讓所有人都沒有完整權限的情況發生。
Google-小心!小組硬碟成員的權限,至少要有一個具有完整權限。
Google-小心!小組硬碟成員的權限,至少要有一個具有完整權限。

2018年8月11日 星期六

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

在多條件的運算中,有些要執行邏輯 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 會自動加上「{}」。

2018年8月8日 星期三

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

在 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 運算

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

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

【格式設定】
1. 小數(指定小數點 4 位)
在儲存格格式中設定,格式:0.0000
2. 分數(指定分母 10000)
在儲存格格式中設定,格式:?/10000
3. 分數(指定分母 1000)
在儲存格格式中設定,格式:?/1000
因為小數是 4 位,而分數的分母是 10000,所以分子會四捨五入至最接近值。
4. 分數(指定分母 25000)
在儲存格格式中設定,格式:?/25000
因為小數是 4 位,而分數的分母是 25000,所以分子會四捨五入至最接近值。
Excel-小數和分數的格式轉換

2018年8月7日 星期二

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

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

利用Google Keep和Microsoft OneNote取出圖片中的文字

能夠將圖片中的文字辨識出來,對於文書工作者而言,是一大福音。而 Google Keep 和Microsoft OneNote 都能做到這個功能。
在 Keep 中的記事裡插入一張圖片,再選取「擷取圖片文字」:
利用Google Keep和Microsoft OneNote取出圖片中的文字
圖片裡的文字隨即會產生於記事裡:
利用Google Keep和Microsoft OneNote取出圖片中的文字
測試簡體字,依然可以:
利用Google Keep和Microsoft OneNote取出圖片中的文字
若是日文字或韓文字就不提供擷取文字功能。
利用Google Keep和Microsoft OneNote取出圖片中的文字
利用Google Keep和Microsoft OneNote取出圖片中的文字
若是以 OneNote 來測試相同的內容,只要在圖片上按右鍵,選取「複製圖片的文字」,即可將辨識好的文字置於剪貼簿中。這四種文字都有提供辨識功能,但是也是只有正體字和簡體字得到正確結果。
利用Google Keep和Microsoft OneNote取出圖片中的文字

檢視其他文章

好康東東