2018年7月29日 星期日

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

網友常問到在 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內容者。

2018年7月28日 星期六

Google-在Line中傳遞位置資訊(使用Google地圖)

Line 在台灣地區是很受歡迎的通訊軟體,在 Line 中除了傳遞文字、語音、影片等訊息之外,現在已結合了 Google 地圖,可以即時傳遞位置資訊了。這是個相當實用的功能,因為告訴別人你在那裡,或是引導別人到某個地點,在日常中實常會用到。
Google-在Line中傳遞位置資訊(使用Google地圖)
參考以下的做法:
在 Line 中點選「+」圖示,再點選「位置資訊」。
Google-在Line中傳遞位置資訊(使用Google地圖) Google-在Line中傳遞位置資訊(使用Google地圖)
現在會在 Google 地圖上顯示你目前的位置,你也可以滑動地圖改變想要顯示的位置。或者在搜尋框中輸入一個地點(本例:文昌宮)。
Google-在Line中傳遞位置資訊(使用Google地圖) Google-在Line中傳遞位置資訊(使用Google地圖)
地圖隨即顯示該地點,點選這個地點,就可以送傳這個地點的位置資訊。收到訊息的人,可以點選這個位置資訊。
Google-在Line中傳遞位置資訊(使用Google地圖) Google-在Line中傳遞位置資訊(使用Google地圖)
當點選這個位置資訊,隨即會在 Line 裡面顯示該地點在 Google 上的位置,若是點選標示的名稱,你還可以選取:以其他應用程式打開、尋找路徑、傳送至其他聊天室、公開所在位置。
Google-在Line中傳遞位置資訊(使用Google地圖) Google-在Line中傳遞位置資訊(使用Google地圖)
若是選取「以其他應用程式打開」,會顯示你的手機中所安裝和地圖相關的App,再選取你要開啟的 App。若是選取「尋找路徑」,則會開啟 Google 地圖,並且 Google 會幫你規劃路徑。
Google-在Line中傳遞位置資訊(使用Google地圖) Google-在Line中傳遞位置資訊(使用Google地圖)

2018年7月26日 星期四

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

在 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]")&"分"
(1) TEXT(INT(A2),"[DBNum2]")&"元"
INT(A2):用以取得儲存格A2中的整數部分(元)。
再利用 TEXT 函數以參數「[DBNum2]」將其顯示為國字數字。
(2) TEXT(LEFT(RIGHT(A2*100,2),1),"[DBNum2]")&"角"
RIGHT(A2*100,2):將儲存格A2的內容放在100倍,再取得其十位數和個位數。
LEFT(RIGHT(A2*100,2),1):利用 LEFT 函數取得十位數(角)。
再利用 TEXT 函數以參數「[DBNum2]」將其顯示為國字數字。
(3) TEXT(RIGHT(A2*100,1),"[DBNum2]")&"分"
RIGHT(A2*100,1):將儲存格A2的內容放在100倍,再取得其個位數(分)。
再利用 TEXT 函數以參數「[DBNum2]」將其顯示為國字數字。

2018年7月25日 星期三

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

在 Excel 的使用上,偶有同仁問到為何儲存格顯示的是公式而非運結果?
Excel-為何儲存格顯示的是公式內容而非公式運算的結果
推測可能的原因:
1. 不小心按到了:Ctrl+~ 鍵
按下 Ctrl+~ 鍵,可以切換儲存顯示公式或是結果。
解決方案:再按一次 Ctrl+~ 鍵。

2. 在已被設定數值格式為:文字的儲存格中輸入公式。
所輸入的公式被認為是文字來顯示,所以沒有執行公式的運算。
解決方案:將儲存格的數值格式設定為「一般」,再重新輸入公式。
(若不想重新輸入公式,可以在公式最後輸入一個空白即可。)
Excel-為何儲存格顯示的是公式內容而非公式運算的結果

3. 在 Excel 檔案/選項/進階中,設定了:在儲存格顯示公式,而不顯示計算的結果
解決方案:取消勾選「在儲存格顯示公式,而不顯示計算的結果」。
Excel-為何儲存格顯示的是公式內容而非公式運算的結果

2018年7月23日 星期一

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

多位網友問到:在 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,表示已經被選取。 )
(2) IF(第(1)式=0,ROW($C$2:$C$11),"")
在陣列公式中,利用 ROW 函數將第(1)式的結果為 0 者,傳回其列號。(若為 1 者,傳回空字串。)
(3) SMALL(第(2)式,ROW(1:1))
當公式向下複製時,利用 SMALL 函數依序取出由小至大列號。(其中 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。)
(4) OFFSET($C$1,第(3)式-1,0)
將第(3)式傳回的列號代入 OFFSET 函數取得對應的儲存格內容。
(5) IFERROR(第(4)式,"")
若有傳回錯誤訊息,則以 IFERROR 函數將錯誤訊息轉換為空字串。
接著,要來設計下拉式清單。
先選取儲存格A2:A11,進入「資料驗證」對話框中,設定:
資料驗證準則:清單。
來源:=OFFSET($E$2,0,0,COUNTA($E$2:$E$11)-COUNTIF($E$2:$E$11,""),1)
Excel-在下拉式清單中顯示未被選取項目(OFFSET,COUNTIF,ROW,SMALL)
(1) COUNTA($E$2:$E$11)-COUNTIF($E$2:$E$11,"")
COUNTA($E$2:$E$11):利用 COUNTA 函數計算E欄中的全部資料數量;
COUNTIF($E$2:$E$11,""):利用 COUNTIF 函數計算E欄中內容為空字串的數量;
兩者相減的結果,即為有內容(非空字串)的項目數量。
(2) OFFSET($E$2,0,0,第(1)式,1)
將第(1)式代入 OFFSET 函數以取得未選資料項目的儲存格範圍。

使用Microsoft Outlook程式收發Gmail郵件

最近同仁的公務信箱即將要移轉至 G Suite 的 Gmail 帳號,但是很多人仍喜歡使用或習慣 Microsoft Outlook 來收發電子郵件,如何能兩全其美的使用?
只要加以設定,就可以在 Microsoft Outlook 上直接收發 Gmail 的信件了。請參考以下步驟來的設定。
1. 首先,來到 Gmail 設定的「轉寄和 POP/IMAP」中。
2. 因為你想要讓 Outlook 可以收信,所以在 POP 下載區中勾選其中一個:
(1) 對所有郵件啟用 POP 功能:Outlook 會收下 Gmail 原有及現在開始收到的信件。
(2) 對現在起所收到的郵件啟用 POP 功能:只會收下現在開始 Gmail 收到的信件。
使用Microsoft Outlook程式收發Gmail郵件
3. 記得按下視窗下方的「儲存變更」按鈕。
4. 開啟 Outlook 程式,選取「檔案/資訊」,進入帳戶資訊區。
5. 點選「新增帳戶」,開啟「新增帳戶」對話框。
使用Microsoft Outlook程式收發Gmail郵件
6. 選取:手動設定或其他伺服器類型,按「下一步」。
使用Microsoft Outlook程式收發Gmail郵件
7. 選取:POP 或 IMPA,按「下一步」。
使用Microsoft Outlook程式收發Gmail郵件
8. 輸入以下的設定:
您的名稱:輸入給收件者能辨識你的名稱
電子郵件地址:輸入給收件者能回覆郵件的地址
帳戶類型:選取 POP3(本例只介紹POP)
內送郵件伺服器:輸入 pop.gmail.com
外寄郵件伺服器:輸入 smtp.gmail.com
使用者名稱:輸入Gmail 的ID(如果使用 G Suite 帳號,要輸入完整的 Email 地址。)
密碼:如有需要,輸入和上述使用者名稱配對的密碼。
傳送新郵件到:選取你要儲存收到的郵件所要存放的 Outlook 資料庫。(也可以選取新增 Outlook 資料庫)
使用Microsoft Outlook程式收發Gmail郵件
9. 接著點選「其他設定」。
10. 在「一般」設定中,為這個帳戶設定輸入一個名稱。(本例:myWork)
使用Microsoft Outlook程式收發Gmail郵件
11. 在「外寄伺服器」設定中:
(1) 勾選:我的外寄伺服器 (SMTP) 需要驗證。
(2) 選取:使用與內送郵件伺服器相同的設定。
使用Microsoft Outlook程式收發Gmail郵件
12. 在「進階」設定中:
(1) 在內送伺服器(POP3)方塊中:輸入 995。
(2) 勾選:此伺服器需要加密連線(SSL)
(3) 在外寄伺服器(SMTP)方塊中:輸入 465。
(4) 在使用下列加密連線類型中:選取 SSL。
(5) 勾選:在伺服器上保留一份郵件複本。
(當 Outlook 收下郵件後,在 Gmail 信箱中仍保有這些郵件。)
使用Microsoft Outlook程式收發Gmail郵件
13. 當你要使用這個 Gmail 帳戶寄信時,在「寄件者」中選取先前設定的帳戶名稱。
使用Microsoft Outlook程式收發Gmail郵件
14. 要收信時,也可以選取先前設定的帳戶名稱來收取該帳戶的郵件。
使用Microsoft Outlook程式收發Gmail郵件
如此,就可以不需使用瀏覽器登入 Gmail 帳戶才能進行收發電子郵件。

2018年7月18日 星期三

利用Google地圖規劃路線的步行選項來查詢路線的高度資訊

當你使用 Google 地圖來規劃路線時,如果選取「步行」,除了顯示路線之外,現在還會顯示高度的變化。這對於開車、徒步或是騎自行車的人,都可以當為參考的數據。(目前僅在電腦版的地圖上會顯示高度,在手機上尚未看到該功能。)
利用Google地圖規劃路線的步行選項來查詢路線的高度資訊
當你將滑鼠移至路線上,左側窗格也會對應點出該地點的高度位置。如果拖曳改變路線,高度也會隨之變化。
利用Google地圖規劃路線的步行選項來查詢路線的高度資訊
對於山路,也可以當為重要參考:
利用Google地圖規劃路線的步行選項來查詢路線的高度資訊
當有不同路線時,可以同時比較其高度變化,再決定要走那一條路線。
利用Google地圖規劃路線的步行選項來查詢路線的高度資訊

2018年7月16日 星期一

Google和Bing的以圖搜尋功能



Microsoft 近來推出了 Bing 「以圖搜圖」的功能,但是該功能在 Google 已行之有年了!
要使用 Bing 的以圖搜圖功能,必須先連結到 Bing 圖片搜尋面頁,然後輸入一個關鍵字來搜尋。其搜尋框會出現照相機圖示,而且找到的圖片中也可以執行圖片搜尋功能。
Google和Bing的以圖搜尋功能
當當按下相機圖示,則可以輸入圖片的 URL 或是上傳一個圖片檔。
Google和Bing的以圖搜尋功能
這是找到的相關圖片:
Google和Bing的以圖搜尋功能
若是上傳一張圖片,其會找到類似的圖片及其網頁:
Google和Bing的以圖搜尋功能
Google和Bing的以圖搜尋功能
Google的以圖搜圖,也是大同小異。使用時,先直接來到 Google 圖片:
Google和Bing的以圖搜尋功能
按下相機圖示,你也是可以選擇以圖片網址或是上傳圖片來搜尋。
Google和Bing的以圖搜尋功能
若是點選顯示更多結果,則會進一步以目前圖片來搜尋圖片。
image
Google和Bing的以圖搜尋功能
若是上傳和前例相同一張圖片來搜尋,則其列出的頁面和 Bing 不相同。Google 會列出搜尋到的網頁及相似的圖片。
Google和Bing的以圖搜尋功能

2018年7月15日 星期日

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

根據 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),
因為指定數量-100~+100為接近值,所以:
條件二:(數量<=$G$4+99)*(數量>=$G$4-99)
其中的「*」運算相當於執行邏輯 AND 運算。
在陣列公式中,當合於二個條件者,會傳回對應的儲存格列號,否則傳回空字串。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數根據第(1)式傳回的列號,由小至大取出列號。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
(3) OFFSET($A$1,第(2)式-1,0)
將第(2)式取出的列號代入 OFFSET 函數,可以取得對應的儲存格內容。
(4) IFERROR(第(3)式,"")
當公式向下複製時,若傳回錯誤訊息,則以 IFERROR 函數轉換為空字串。
同理,
儲存格I2:{=IFERROR(OFFSET($C$1,SMALL(IF((類別=$G$2)*(數量<=$G$4+99)*
(數量>=$G$4-99),ROW(編號),""),ROW(1:1))-1,0),"")}
儲存格J2:{=IFERROR(OFFSET($D$1,SMALL(IF((類別=$G$2)*(數量<=$G$4+99)*
(數量>=$G$4-99),ROW(編號),""),ROW(1:1))-1,0),"")}

2018年7月14日 星期六

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

 在 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)," ","")
若想要將兩個公式合而為一:
儲存格C2:=SUBSTITUTE(MID(SUBSTITUTE(A2,"*",REPT(" ",20)),20,20)," ","")

2018年7月13日 星期五

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

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

檢視其他文章

好康東東