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)

Excel-列出資料中沒有重覆內容的項目清單(COUNTIF,OFFSET,SMALL)

在 Excel 的工作表中有一個資料清單(參考下圖左),其中的內容有部分是重覆的,如何能列出已剔除相同內容(只留下第一個)的清單(參考下圖右)?
Excel-列出資料中沒有重覆內容的項目清單(COUNTIF,OFFSET,SMALL)

【公式設計與解析】
這個例子,要用到一個輔助欄位(B欄)。
儲存格B2:=IF(COUNTIF($A$2:A2,A2)>1,"",ROW(A2))
複製儲存格B2,貼至儲存格B2:B19。
(1) COUNTIF($A$2:A2,A2)
利用 COUNTIF 函數計算每個儲存格由儲存格A2起始的儲存格範圍裡,共有幾個相同內容的儲存格數量。
(2) IF(第(1)式>1,"",ROW(A2))
如果傳回數值大於 1,則傳回空白,否則(=1)傳回該儲存格的列號。

儲存格C2:=IFERROR(OFFSET($A$1,SMALL($B$2:$B$19,ROW(1:1))-1,0),"")
複製儲存格C2,貼至儲存格C2:C19。
(1) SMALL($B$2:$B$19,ROW(1:1))
利用 SMALL 函數由小至大依序找出對應的數值。
(2) OFFSET($A$1,第(1)式-1,0)
將第(1)式傳回的數值代入 OFFSET 函數,找出對應的儲存格內容。
(3) IFERROR(第(2)式,"")
若公式傳回了錯誤訊息,則以空白顯示。

2018年7月11日 星期三

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

接著前一篇文章:Excel-利用政府資料開放平臺練習資料處理,來看看有那些資料可以拿來練習。
例如:取用「農村地方美食小吃特色料理」所提供的資料,下載XML檔。(本例:https://data.gov.tw/dataset/6037
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
要開啟這個下載的XML檔,你必須啟動「開發人員」表功能。(點選「檔案/選項」,再由「自訂功能區」中勾選「開發人員」。)
接著,選取「開發人員」功能表中的「匯入」,再選取下載的XML檔。
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
開啟檔案後,如下圖:
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
稍加整理,留下想要的資料:
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
利用工作表中的經度和緯度資料,設定公式可以在Google地圖中顯示該地點。
儲存格Y2:=HYPERLINK("https://www.google.com.tw/maps/place/"&V2,B2)
複製儲存格Y2,貼至儲存格Y2:Y100。
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
在工作表中點選一個地點的 Google 地圖超連結,即會在地圖上顯示該地點的位置。
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

例如:取用「颱風消息與警報-颱風消息」所提供的資料,下載KML檔。(本例:https://data.gov.tw/dataset/10063
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
如果你有安裝Google地球(https://www.google.com/intl/zh-TW/earth/desktop/),點選這個KML檔,即可看到颱風的走向預測等資訊。
現在(2018/7/11 10:30),瑪莉亞颱風還在我們的附近,我因放颱風假可以專心寫文章。
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
有興趣可以看看颱風誕生到現在的路徑:
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

例如:取用「新北市電動汽車充電站-新店區」所提供的資料,下載KML檔。(本例:https://data.gov.tw/dataset/79920
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

例如:取用「桃園市河濱自行車道資料」所提供的資料,下載其中一個KMZ檔。(本例:https://data.gov.tw/dataset/26119
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
在 Google 地球中開啟所下載的 KMZ 檔(軌跡檔),
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)
按下「播放遊覽」,你就可以在螢幕上先把這一段的自行車道遊覽一下。顯示的高度變化也可以做為評估。
Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

2018年7月10日 星期二

Excel-利用政府資料開放平臺練習資料處理

如果你想要運用 Excel 來練習資料處理,而找不到可利用的範例,可以進到政府資料開放平臺來取資料。政府資料開放平臺(https://data.gov.tw)提供了各類型的資料供民眾可以下載使用。
Excel-利用政府資料開放平臺練習資料處理
這個平台的各種資料使用了各式各樣的格式,例如:CSV、PDF、TXT等。
Excel-利用政府資料開放平臺練習資料處理
隨意取一個資料,例如:學生體重平均值(6歲-15歲),其提供了「CSV」格式的檔案。下載這個CSV檔。
Excel-利用政府資料開放平臺練習資料處理
開啟這個 CSV 檔,其內容如下圖:
Excel-利用政府資料開放平臺練習資料處理
先刪除不要的資料:
Excel-利用政府資料開放平臺練習資料處理
利用其提供的資料,轉換為下圖右:
儲存格G2:=SUMPRODUCT((學年度=$F3)*(年齡=$G$1)*INDIRECT(G$2))
Excel-利用政府資料開放平臺練習資料處理
另一種轉換:
儲存格G2:=SUMPRODUCT((年齡=$F3)*(學年度=$G$1)*INDIRECT(G$2))
Excel-利用政府資料開放平臺練習資料處理
再轉換為另一種格式:
儲存格G2:=SUMPRODUCT((年齡=$F3)*(學年度=G$2)*INDIRECT($G$1))
Excel-利用政府資料開放平臺練習資料處理
這些數據應該都是真實的,但不一定是最新的。不過,隨意取來練習資料處理,說不定可以讓你分析出令人意外的結果啊!

Excel-找出儲存格範圍內是否有儲存格重覆(SUMPRODUCT,COUNTA,COUNT)

在 Excel 的工作表裡有一個資料表,如何判斷同一列中是否有重覆的內容?
參考下圖,資料的第一列中的「AAAA」有重覆出現,則判定為「O」;若沒有重覆出現,則判定為「X」。
Excel-找出儲存格範圍內是否有儲存格重覆(SUMPRODUCT,COUNTA,COUNT)

【公式設設與解析】
儲存格F2:
=IF(SUMPRODUCT(COUNTIF(A2:D2,A2:D2))>COUNTA(A2:D2),"O","X")
複製儲存格F2,貼至儲存格F2:F9。
(1) COUNTIF(A2:D2,A2:D2)
在 SUMPRODUCT 函數可以執行陣列運算,此公式可以找出儲存格A2:D2裡,每個儲存格內容的數量。傳回陣列:{3, 3, 1, 3}。
(2) SUMPRODUCT(COUNTIF(A2:D2,A2:D2))
在 SUMPRODUCT 函數裡將第(1)式的內容予以加總,本例為:3+3+1+3=10。
(3) IF(第(2)式>COUNTA(A2:D2),"O","X")
COUNTA(A2:D2)為計算儲存格的數量。若第(2)式的傳回值大於儲存格的數量,則必有至少2個儲存格內容重覆。

2018年7月9日 星期一

Excel-找出日期清單中每個人員最後報名日期(OFFSET,MAX,SUBSTITUTE)

參考下圖,在 Excel 中有一個報名日期和報名人員的資料表(下圖左),由於每個人員有多次報名,如何找出每一個人員的最後一次報名日期(下圖右)?
Excel-找出日期清單中每個人員最後報名日期(OFFSET,MAX,SUBSTITUTE)

【公式設計與解析】
儲存格E2:{=OFFSET($A$1,MAX((SUBSTITUTE($B$2:$B$11,D2,"")<>
$B$2:$B$11)*ROW($B$2:$B$11))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格E2,貼至儲存格E2:E11。
(1) SUBSTITUTE($B$2:$B$11,D2,"")
陣列公式中,利用 SUBSTITUTE 函數將每一個儲存格內容,含有儲存格D2內容者,全部置換成空字串。
(2) SUBSTITUTE($B$2:$B$11,D2,"")<>$B$2:$B$11)
判斷第(1)式的傳回結果和原來儲存格陣列內容是否相符,傳回 TRUE/FALSE 陣列。
本例儲存格D2的內容為「甲」,所以傳回 FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE
(3) 第(2)式*ROW($B$2:$B$11)
將第(2)式乘上儲存格陣列中每一個儲存格的列號(例如:ROW(B2)=2、ROW(B3)=3、...、ROW(B11)=11),在運算過程中 TRUE/FALSE 陣列會轉換為 1/0 陣列。
所以,傳回的結果即為含有儲存格D2內容的列號。本例結果傳回 0, 3, 0, 0, 0, 0, 0, 0, 0, 11。
(4) MAX(第(3)式)
利用 MAX 函數將第(3)式傳回的列號取其中的最大值。
(5) OFFSET($A$1,第(4)式-1,0)
將第(4)式傳回的列號最大值,代入 OFFSET 函數求得在第A欄中對應的日期。

2018年7月8日 星期日

Word文件中如何固定表格位置與隱藏表格

在使用 Word 文件時插件表格,有時會希望能固定表格在頁面中的特定位置,或是希望表格暫時不要出現,該如何處理?
如下圖,其中的表格希望能固定於特定位置,參考以下做法:
1. 在表格中按滑鼠右鍵,並選「表格內容」選項。
Word文件中如何固定表格位置與隱藏表格
2. 在「表格內容」對話框中的選取「文繞圖」。
3.再選取「位置」。
Word文件中如何固定表格位置與隱藏表格
4. 在「表格的位置」對話框中,取消勾選「隨段落文字移動」。
5. 然後,在「相對於」中選取「邊界」或「整個頁面」,並設定一個想要的位置。
Word文件中如何固定表格位置與隱藏表格
如果在文件輸入其他文字,即使表格被擠到下一頁,仍會固定於該頁面於先前設定的位置。
Word文件中如何固定表格位置與隱藏表格
如果你想讓表格暫時隱藏,則可以選取整個表格(例如下圖中綠色表格):
Word文件中如何固定表格位置與隱藏表格
1. 按 Ctrl+Shift+H 鍵,設定表格為隱藏內容。
2. 當按 Ctrl+Shitt+8Ctrl+*) 鍵,即可切換這個表格的隱藏/顯示。
Word文件中如何固定表格位置與隱藏表格

2018年7月7日 星期六

Excel-在日期清單中依相同日期加上流水號(TEXT,COUNTIF)

在 Excel 中有一個日期清單,如何能依據相同日期給予流水號?
如下圖,日期是由小至大排列,每個日期的數量不一,希望能將相同日期給予三碼的流水號。
Excel-在日期清單中依相同日期加上流水號(TEXT,COUNTIF)
【公式設計與解析】
儲存格C2:=TEXT(A2,"yyyy/mm/dd")&"-"&TEXT(COUNTIF($A$2:A2,A2),"000")
複製儲存格C2,貼至儲存格C2:C26。
(1) TEXT(A2,"yyyy/mm/dd")
利用 TEXT 函數將儲存格A2的日期以「年4碼/月2碼/日2碼」的格式呈現。
(2) TEXT(COUNTIF($A$2:A2,A2),"000")
先利用 COUNTIF 計算儲存格A2起始至目前儲存格A2中相同日期有幾個。
再利用 TEXT 函數將數值以「3碼補0」的格式呈現。
(3) 第(1)&"-"&第(2)式,即為所求。

如果你的日期並不是相同日期排在一起,該公式仍會得到相同結果。
Excel-在日期清單中依相同日期加上流水號(TEXT,COUNTIF)

檢視其他文章

好康東東