2018年5月30日 星期三

宜蘭伯朗大道感受稻香!

日前學校校慶補假,天氣也不錯,正好到宜蘭的伯朗大道,感受一下「稻香」!
目前(5月底)還是綠油油的一片,不久之後,應該就會轉成金黃色。
宜蘭伯朗大道感受稻香!
行車路線如下圖,建議把車停在「奉尊宮」,該處設有停車場。走路至伯朗大道,很近!不要從伯朗大道的另一頭過來,以免和人潮發生相遇。
宜蘭伯朗大道感受稻香!
奉尊宮不只有停車場,一旁還有廁所喔!
宜蘭伯朗大道感受稻香!
沿著指標2分鐘可抵達高台,二樓有遮蔭,三樓可看更遠。
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
親近這些稻子,讓都市人有了難得的感受。
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
沿著道路漫步,一路有稻草人相伴。這些稻草人這麼的可愛,麻雀們根本不怕他們吧!
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
宜蘭伯朗大道感受稻香! 宜蘭伯朗大道感受稻香!
2018年6月30日前的交通管制措施:
宜蘭伯朗大道感受稻香!

2018年5月29日 星期二

Excel-根據日期判斷學年學期(YEAR,MONTH,VLOOKUP)

有網友問到:如何根據一個日期判斷該日期是在那個學年、那個學期?
假設:每一學年裡,1月:上學期/2月-7月:下學期/8月-12月:上學期。
方法一:
Excel-根據日期判斷學年學期(YEAR,MONTH,VLOOKUP)
【公式設計與解析一】
儲存格B2:=IF(MONTH(A2)>7,YEAR(A2)-1911&"上",IF(MONTH(A2)<2 br="" style="letter-spacing: 0px;">YEAR(A2)-1912&"上",YEAR(A2)-1912&"下"))
將月份切成三份,判斷後顯示不同結果。

方法二:
Excel-根據日期判斷學年學期(YEAR,MONTH,VLOOKUP)

【公式設計與解析二】
儲存格B2:=(YEAR(A2)-1912+(MONTH(A2)>7)&VLOOKUP(MONTH(A2),
{0,"上";2,"下";8,"上"},2,TRUE))
利用 VLOOKUP 函數,以查表方式來來處理。
其中:YEAR(A2)-1912+(MONTH(A2)>7),在運算時(MONTH(A2)>7)會傳回 TRUE/FALSE,運算時會轉換為1/0。

2018年5月27日 星期日

Excel-在日期清單中取得日期區間裡最後一個日期對應的數值(VLOOKUP,LARGE,陣列公式)

在 Excel 中有一個日期清單(如下圖),如何在日期清單中取得指定日期區間裡最後一個日期對應的數值?
參考下圖,根據起始日期和結束日期的日期區間,想要找出最後日期所對應的數值,該如何處理?(下圖的日期已排序,由舊至新排序)
Excel-在日期清單中取得日期區間裡最後一個日期對應的數值(VLOOKUP,LARGE,陣列公式)

【公式設計與解析】
首先,定義儲存格名稱。
選取儲存格A1:A26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
接著,輸入公式,儲存格D7:
{=VLOOKUP(LARGE(IF((日期>=D2)*(日期<=D4),日期),1),資料,2,FALSE)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
(1) IF((日期>=D2)*(日期<=D4),日期)
在陣列公式中判斷日期陣列中大於或等於儲存格D2並且小於或等於儲存格D4的日期者,傳回這些日期。
(2) LARGE(第(1)式,1)
根據第(1)式傳回的日期陣列,利用 LARGE 函數取出取大值者。(日期數值愈大,表示日期愈新。)
(3) VLOOKUP(第(2)式,資料,2,FALSE)}
將第(2)式傳回的日期代入 VLOOKUP 函數,以查表方式找出對應應數值。

如果日期清單是尚未排序者,套用相同公式,亦可得正確結果。
image

Excel-多條件查詢(INDEX,INDIRECT,SUMPRODUCT)

在 Excel 中的資料表(如下圖)中,如何執行多個條件的查詢?
例如:給予類別、項目和重量等三個變項,要查詢對應的數值,該如何設計公式?
Excel-多條件查詢(INDEX,INDIRECT,SUMPRODUCT)

【公式設計與解析】
首先,定義名稱:
選取儲存格A1:G20,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:類別、項目、100kgs、200kgs、300kgs、400kgs、500kgs。
(特別注意:其中數字為首的名稱會被加上「_」,例如:100kgs→_100kgs。)
接著,輸入公式:
儲存格J4:=INDEX(INDIRECT("_"&J3),SUMPRODUCT((類別=J1)*(項目=J2)*
ROW(項目))-1,0)
假設多條件的查詢結果具唯一性。
(1) SUMPRODUCT((類別=J1)*(項目=J2)*ROW(項目))
利用 SUMPRODUCT 函數求得同時符合「類別」和「項目」二個條件者,傳回其在項目陣列中的位置(傳回一個數字)。
(2) INDIRECT("_"&J3)
利用 INDIRECT 函數將儲存各J3的內容轉換為儲存格範圍。(因為名稱定義時,數字為首的名稱會被加上「_」,例如:100kgs→_100kgs。)
(3) INDEX(第(2)式,第(1)式-1,0)
再利用 INDEX 函數查表得到對應的結果。

2018年5月20日 星期日

Google Earth-在地圖上查詢地點的高度

在 Google 地圖上可以輕鬆的查詢到各個地點的經度和緯度,但是如果是要查詢地點的高度,該如何處理?
目前看來 Google 地圖好像沒有提供高度的資訊,除了「地形」的圖層中有像等高線這類的資訊,但是這只能查詢到近似的高度。而你可以藉助 Google Earth 來查詢高度!
以網頁版的 Google Earht 為例來求取地點的高度資訊:
以下圖為例:將滑鼠移至你想要知道高度的地點(下圖游標處),再觀察視窗右下角狀態列的資訊,即會看到所顯示該地圖的高度。
Google Earth-在地圖上查詢地點的高度
上下二圖比較,可以知道石門水庫的大霸二端是那邊高、那邊低了。
Google Earth-在地圖上查詢地點的高度
因此,很容易就可以得知擎天崗大草原(停車場附近)的海拔高度了。顯示:769公尺。
Google Earth-在地圖上查詢地點的高度
對照陽明山國家公園的擎天崗介紹(http://www.ymsnp.gov.tw/index.php?option=com_tourmap&view=tourmap&id=7&gp=0&Itemid=128),其中提到「海拔高度僅約770公尺的擎天崗」,看來是不謀而合的。
Google Earth-在地圖上查詢地點的高度

2018年5月16日 星期三

Excel-利用VLOOKUP函數查詢二個不連續的區域

在 Excel 的工作表中,如何在二個不連續的儲存格範圍中使用 VLOOKUP 函數來查詢?
參考下圖,如果想要在儲存格A2:B11和A15:B26中查詢某一個人員的數值。(人員在二個不連續範圍中不會重覆)
image

【公式設計與解析】
因為 VLOOKUP 函數查詢時,必須在連續範圍裡,所以在二個儲存格範圍中要使用二個VLOOKUP 函數來查詢。
儲存格E2:=IFERROR(VLOOKUP(D2,A2:B11,2,FALSE),"")&IFERROR(VLOOKUP
(D2,A15:B26,2,FALSE),"")
(1) VLOOKUP(D2,A2:B11,2,FALSE)
利用 VLOOKUP 函數查詢儲存格D2在儲存格範圍A12:B11中的「數值」。
若儲存格D2並沒有在儲存格範圍A12:B11中,則會傳回錯誤訊息。
(2) IFERROR(VLOOKUP(D2,A2:B11,2,FALSE),"")
將第(1)式傳回的錯誤以 IFERROR 函數轉換為空字串「""」(空白)。
(3) VLOOKUP(D2,A15:B26,2,FALSE)
利用 VLOOKUP 函數查詢儲存格D2在儲存格範圍A15:B26中的「數值」。
若儲存格D2並沒有在儲存格範圍A15:B26中,則會傳回錯誤訊息。
(4) IFERROR(VLOOKUP(D2,A15:B26,2,FALSE),"")
將第(3)式傳回的錯誤以 IFERROR 函數轉換為空字串「""」(空白)。
(5) 第(2)式&第(4)式
因為第(2)式和第(4)式中,只有一個式子會傳回數值,另一個式子會傳回空字串(空白)。所以利用「&」予以串接。

2018年5月15日 星期二

Excel-將資料表中相同地址的姓名集合在一起(OFFSET,SMALL,COLUMN)

參考下圖,在 Excel 中有一個資料表,如何將相同地址的姓名集合在一起?
Excel-將資料表中相同地址的姓名集合在一起(OFFSET,SMALL,COLUMN)

【公式設計與解析】
首先,選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、地址。
接著,輸入公式:
儲存格E2:{=IFERROR(OFFSET($A$1,SMALL(IF(地址=$D2,ROW(姓名),""),
COLUMN(A:A))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格E2,貼至儲存格E2:K7。
(1) IF(地址=$D2,ROW(姓名),"")
在陣列公式中,判斷址址陣列中是否和儲存格D2(地址A)相同,若是,則傳回對應姓名儲存格的列號:若否,則傳回空字串("")。
(2) SMALL(第(1)式,COLUMN(A:A))
利用 SMALL 函數,由小至大取出對應的數值(列號)。當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→....。
(3) OFFSET($A$1,第(2)式-1,0)
將第(2)式的結果帶入 OFFSET 函數,取得對應的儲存格內容(姓名)。
(4) IFERROR(第(3)式,"")
若公式傳回錯誤訊息,則以 IFERROR 函數改顯示空字串(空白)。

將手機內容投影至Windows 10系統的畫面中

在學校上課時,老師們常常有需要將手機內容投影出來。如果不想透過硬體裝置或安裝應用軟體,則可以試試 Windows 內建的連線功能。你的電腦或筆電必須具備無線上網功能(電腦可以使用 USB 無線網卡達到無線上網功能)。
參考以下步驟:
1. 尋找「連線」應用程式。
將手機內容投影至Windows 10系統的畫面中
2. 開「連線」應用程式。目前 Windows 10 已準備好接收手機的投影了。
將手機內容投影至Windows 10系統的畫面中
3. 開啟你的手機中的「投放」功能。
(每雙手機使用的名稱不同,通常是位在「連接」項下)
將手機內容投影至Windows 10系統的畫面中
4. 啟用無線螢幕分享。
5. 點選想要的連線(本例:OFFICE)。手機會顯示:已連線。
將手機內容投影至Windows 10系統的畫面中 將手機內容投影至Windows 10系統的畫面中
當你滑動手機畫面時,電腦上的視窗會同步顯示手機中的內容。
將手機內容投影至Windows 10系統的畫面中
在投放的位置可以中斷連線。
將手機內容投影至Windows 10系統的畫面中

2018年5月12日 星期六

計算Google試算表中核取方塊勾選的數量

最近 Google 新增了 Google 試算表的功能:核取方塊,要如何計算核取方塊被勾選的數量?
要使用核取方塊,則先選取儲存格,再選取「插入/核取方塊」功能。
計算Google試算表中核取方塊勾選的數量
參考下圖,新增的核取方塊預設為「未選取」,其值為FALSE,若是核取方塊被選取,則值為TRUE。要如何計算下圖中每一欄有幾個核取方塊被選取。
計算Google試算表中核取方塊勾選的數量
例如,在儲存格A18中,輸入公式:
(1)=ArrayFormula(SUM(A2:A17*1))
(2) =SUMPRODUCT(A2:A17*1)
計算Google試算表中核取方塊勾選的數量
將儲存格A18複製到儲存格A18:J18。

2018年5月11日 星期五

Excel-多條件查詢(SUMPRODUCT,OFFSET)

在 Excel 中,你會使用 VLOOKUP 函數來執行查詢的工作,但是如果是要查詢多個條件時,VLOOKUP 函數無法滿足這個需求,該如何處理?
以下圖為例,如果要以雙條件在下圖左的資料表中查詢,如何設計公式?
Excel-多條件查詢(SUMPRODUCT,OFFSET)

【公式設計與解析】
選取儲存格A1:F14,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:單號、序號、料號、數量、進貨單號、倉庫。
輸入公式,儲存格H8:
=OFFSET($F$1,SUMPRODUCT((單號=H2)*(料號=H4)*ROW(倉庫))-1,0)
(1) SUMPRODUCT((單號=H2)*(料號=H4)*ROW(倉庫))
在 SUMPRODUCT 函數使用雙條件:
條件一:單號=H2:單號和儲存格H2相同者
條件二:料號=H4:料號和儲存格H4相同者
在 SUMPRODUCT 函數中,(單號=H2)*(料號=H4)*ROW(倉庫)會傳回合於條件者的列號。
(2) OFFSET($F$1,第(1)式-1,0)
將第(1)式傳回的列號代入 OFFSET 函數,取得對應的儲存格內容。
如果你要增加條件,則在 SUMPRODUCT 函數中擴增條件,其餘不用變更。

2018年5月7日 星期一

Excel-設定多個條件的格式設定

參考下圖,在 Excel 中如何根據到期日距今日的日數:30天以內、60天以內、90天以內,給予不同的儲存格色彩?
很明顯的可以利用設定格式化的條件來處理!
Excel-設定多個條件的格式設定
參考以下步驟:
1. 選取儲存格A2:C23。
2. 在設定格式化的條件規則理員中設定:
規則類型:使用公式來決定要格式化哪些儲存格
編輯規則:=$B2-TODAY()<=90
設定格式:儲存格底色為青色
Excel-設定多個條件的格式設定
重覆相同步驟,設定不同規則
規則:=$B2-TODAY()<=60;格式:儲存格底色為藍色
規則:=$B2-TODAY()<=30;格式:儲存格底色為澄色
設定完成的結果如下:
Excel-設定多個條件的格式設定
如果你做出錯誤結果,有可能是規則的順序弄錯了。(如下錯誤結果)
Excel-設定多個條件的格式設定
你可以調整規則順序(上移/下移):
Excel-設定多個條件的格式設定

檢視其他文章

好康東東