2015年7月31日 星期五

Excel-在多個工作表的固定儲存格快速產生一個月的每個日期(CELL,DATE)

網友問到:在 Excel 中有多個工作表,例如:31個,其工作表名稱為 1~31,如何快速在這些工作表的相同儲存格中產生某一個月中每一天的日期?
例如:參考下圖,其中有 5 個工作表(名稱:1~5),每個工作表的儲存格A2要輸入 2015年8月的某一天的日期(日期為工作表名稱之數字)
參考作法:
1. 新增一個活頁簿,並且先命名存檔。
2. 建立 31 個工作表,名稱定為:1~31。
3. 選取這 31 個工作表。(先選工作表:1,按著 Shift 鍵,再選取工作表:31)
4. 按一下儲存格A2。(選取儲存格A2)
5. 輸入公式:=DATE(2015,8,MID(CELL("filename", A1),FIND("]", 
CELL("filename", A1)) + 1, 255))
6. 按下 Enter 鍵後,即會在工作表 1~31 的儲存格A2中,產生 2015/8/1~2015/8/31 的日期。

【公式說明】
CELL("filename", A1):傳回該檔案的完整名稱,格式:[路徑/檔案名稱]工作表名稱。其中參數 A1 沒有特別意義。
FIND("]", CELL("filename", A1)):找出檔案完整路徑中的「]」位置,傳回第幾個字的數字。
MID(CELL("filename", A1),FIND("]", CELL("filename", A1)) + 1, 255):傳回工作表名稱,此名稱代表日期的數字。
最後透過 DATE 函數,代入年:2015、月:8、日:工作表名稱,即可轉換為一個日期。
CELL 函數的參數如下:

【補充資料】
語法:CELL(info_type, [reference])
info_type
傳回
"address"
以文字形式表示 reference 中第一個儲存格的位址。
"col"
Reference 中儲存格的欄名。
"color"
如果儲存格設定為會因負數而改變色彩的格式,則傳回 1;否則傳回 0 ()
"contents"
參照左上角儲存格的數值;不是公式。
"filename"
以文字形式傳回 reference 所屬文件的檔案名稱 (包含完整的路徑名稱)。如果該文件尚未存檔,則傳回空字串 ("")
"format"
對應於數值儲存格格式的文字表示形式。各種格式的文字表示列示於以下的表格中。如果儲存格為會因負數而改變色彩,則傳回的文字值的後面會帶有負號 (-)。如果儲存格被設定為將正數或任何數值放在一組括弧中的格式時,則在傳回的文字值的後面加一組 "()"
"parentheses"
如果儲存格格式設定為將正數或所有數值放在一組括弧中,傳回1;否則傳回0
"prefix"
文字儲存格的「標籤首碼」的文字表示形式。如果該儲存格含有靠左對齊的文字時,傳回單引號 (');如果該儲存格中含有靠右對齊的文字時,傳回雙引號 (");如果該儲存格中含有置中對齊的文字時,傳回脫字符號 (^);如果該儲存格中含有填滿對齊的文字時,傳回反斜線 (\);如果該儲存格含有其他的資料,則傳回空字串("")
"protect"
如果儲存格並未鎖定保護,傳回 0;如果儲存格已鎖定保護,則傳回 1
"row"
參照位址中儲存格的列號。
"type"
儲存格中資料類型的一個對應文字值。如果該儲存格是空白的,傳回「b(代表blank),如果該儲存格含有文字常數,則傳回標籤「l(代表 label);如果該儲存格中含有其他類別的資料,則傳回「v(代表 value)
"width"
儲存格欄寬四捨五入成整數值。每個欄寬單位都等於預設字型大小的一個字元寬度。

在Google地圖中組織你的地點和擴大搜尋功能

Google 地圖的功能愈來愈符合生活中的需求了,而手機是行動裝置,所以在 Google 地圖中,手機和電腦看到的內容有些許不一樣,行動裝置的功能甚至更有用了。

(一) 組織你的地點
在前一篇文章中:在Google地圖時間軸中自訂私房地點和暱稱,你可以自行定義個人看得到的地點名稱,而這些名稱在你使用手機瀏覽 Google 地圖時,也會一一出現在地圖上。
 
如果你想了解自己新增了那些地點,可以在 Google 地圖中的「您的地點」之下找到所有已命名的地點,在此也可以刪除某個地點。由圖片中可以看出,你也可以為某些地點定義一個「名實不符」的地點名稱,而且這些地點只有你看得到。(這些自行命名的地點名稱,目前還無法在電腦上的 Google 地圖上呈現。)
 
在「您的地點」之下也整合了「您去過的地點」和「已儲存的地點」:
 

(二) 擴大搜尋功能
在手機中的 Google 地圖,你可以在搜尋地點時,使用「探索鄰近地區」,在「更多」選項中,會列出其有的完整服務選單。
 
當你習慣使用關鍵字,例如:美食、飯店、景點等等來大範圍搜尋地點時,你可以使用「*」來搜尋(「*」有點像萬用字元的概念),讓這些 Google 地圖已有的各式各樣的地點一次顯示,再透過「篩選」功能來精確搜尋。
 
例如:你可以依據評分高低來篩選,或是以時間點來搜尋各種地點。也可以使用「探索周邊」功能依步行或開車時間、時段等來篩選地點。
 
而在電腦版中,也可以使用「*」來搜尋,列出的地點包羅萬象,吃喝玩樂和各項服務全都露。(注意:你可地點圖版面不可以縮的太小,如果太小則該功能是失效的。)
搜尋「服務」,則各式各樣的商家都會顯示出來。
搜尋「機關」,則和行政機構相關的地點會標示出來。

Google地圖街景檢視還可以看全景相片和內部實景

在 Google 地圖電腦版中,現在不只可以看街景而已!由於網路上很多熱心人士,提供了很多全景相片,而 Google 已將商家的內部實照陸續掛到地圖上。
所以在 Google 地圖上還能使用:Photo Sphere全景照片、瀏覽內部實景。以往你會將小橘人「拖曳」至某個位置看街景,現在你只要「點選」小橘人圖示,即會出現檢視項目的選單,藍色圓圈圖示可以檢視全景相片,橘色圓圈圖示可以檢視內部實景。
使用右上角的開關,可以切換是否顯示各種街景圖片(該選單會固定在地點上)。
瀏覽內部實景:
Photo Sphere全景相片:
而在 Google 地圖上本來就有一些網友掛上的相片和環景圖:
如果你要看更多、更完整的環景圖,連到:https://www.google.com/maps/views,這裡可以看到網友上傳的各個地點的環景圖。

2015年7月30日 星期四

運算時排除篩選掉或被隱藏的內容(STBTOTAL)

在執行 Excel 的各種運算工作時,常會遇到某些列可能會被隱藏掉或被篩選掉(沒有顯示出來),但是你只想要計算被顯示出來的資料,該如何獲得想要的結果呢?
例如下圖中,儲存格B17:=SUM(B2:B16),可以計算AAA的總和。
當有部分資料被隱藏時,如果只想計算被顯示的資料總和,原公式無法獲取正確的結果。例如:下圖中,第 6~9 列被「手動隱藏」了。
建議改用 SUBTOTAL 函數,其中參數 9 和 109 都是用以計算總和(SUM):
在 SUBTOTAL 函數中參數,分為:1~11和101~111兩部分。但是上式中使用 109,結果正確;而使用 9 時,結果錯誤。
 
其中,數字 1-11 或 101-111 指定要用於計算小計的函數。1-11 包括手動隱藏的列,但 101-111 會排除掉手動隱藏的列;篩掉的儲存格一律會排除。所以上式中,如果使用參數 9,則會包括隱藏的資料,所以結果不正確。
再來看看另一種狀況,如果資料被篩選掉而不想被併入計算。當然使用 SUM 函數必然是錯誤的。
再次使用 SUBTOATL 函數,不管參數 9 或 109,結果都是正確的。因為被篩選掉的資料都會被排除計算!
特別注意到:如果在篩選的狀況,再手動隱藏某些列的資料,不管參數 9 或 109,結果都是正確的。
最後要提醒,如果你想要快速看一下只計算顯示資料的和,不妨在選取資料後,直接檢視狀態列的加總項,因為這裡的總和計算,是預設排除被隱藏和被篩選掉的資料。(第 6~9 列為隱藏狀態)

2015年7月28日 星期二

Excel-推算日期(在一個儲存格中執行日期相減運算)

網友問到在 Excel 中如果要給予一個日期後,執行推算若干日數之前的日期,在一個儲存格中該如何設計公式?(參考下圖)
如果根據某個輸入日期的儲存格來推算若干日數之前的日期,可以使用儲存格內容減一個數字的方式來執行。
如果是要在一個儲存格中直接運算出結果,則必須將日期(年,月,日)轉換為一個數字,再來減某個數字,以下圖為例,假設今天日期為2015/7/28。
(1) 儲存格C2:=DATE(2015,7,28)-164
透過 DATE 函數將年,月,日代入,即可轉換為一個數字。
(2) 儲存格C2:=TODAY()-164
透過 TODAY 函數值傳回今天的日期,該傳回值即為一個數字。

在Google地圖時間軸中自訂私房地點和暱稱

最近幾天一直在使用 Google 地圖的新功能:時間軸,發現愈玩愈有趣。
你可以先參考前一篇文章的說明:Google地圖用時間軸展現你的定位紀錄活動
當你在時間軸的某個停留地點上出現了不是你去過的真實地點,或是你想要給予該地點一個暱稱,在此都是可以做到的。你可以把 Google 地圖的時間軸當成一個旅遊動線來走,然而凡走過必留下痕跡,現在我們要為這些痕跡,留下自己的註解。
例如,在下圖的某個停留點標示的名稱並不真確,所以不能按下「我來過這裡」。
你可以在這個地點的下拉式選單中,嘗試找看看有沒有符合者。如果完全沒有或是你想新創一個,則可以選取「新增個人地圖」(此時可以使用放置圖釘方式來定位)。
將圖釘移至正確位置,然後在個人地點中輸入一個名稱。觀察到「新增個人地點」旁的鎖頭圖示了嗎?表示該地點的名稱只有你能檢視。按下「儲存」後,即可選用該地點。
而這個你所自訂的地點,以後可以隨時叫出來使用,而不用每次再重設。
你也可以移除某個建議的停留地點,然後,再新增個人的私房地點。
有興趣的話,也可以用電腦和手機來玩一玩。

2015年7月23日 星期四

Excel-用微調按鈕來調整儲存格中的時間

有網友想要利用 Excel 中的微調按鈕來調整儲存格中顯示的時間,該如何處理?參考下圖。
微調按鈕是開發人員功能表的一個物件:
首先,為「時、分、秒」分別新增各一個微調按鈕。其中的設定:
時→最小值:0、最大值:23,遞增值:1,儲存格連結:D2。
分→最大值:0、最大值:59,遞增值:1,儲存格連結:E2。
秒→最小值:0、最大值:59,遞增值:1,儲存格連結:F2。
接著,輸入公式:
儲存格B2:=B1+D2/24+E2/24/60+F2/24/60/60
其原理乃因 Excel 將一天設定為數值 1,所以每一小時為 1/24,每一分鐘為 1/24/60,每一秒鐘為 1/24/60/60。將儲存格D2、儲存格E2、儲存格F2代入公式,即可使用微調按鈕來控制儲存格中顯示的時間。

Google地圖-新增照片並檢視照片使用狀況

你是否曾上傳照片至 Google 地圖中?你可以檢視這些照片被使用的狀況。
這裡會顯示你上傳的相片和Photo Sphere 全景相片,還可以看到被瀏覽的次數。
如果相片中有出現一個地標圖示者,表示這張圖片有出現在 Google 地圖中。
你可以設定照片出現的順序(最近新增或是瀏覽次數最多):
點選一張照片,即可檢視該照片被瀏覽的次數:
你如果想要在 Google 地圖中新增照片,則可以在街景檢視中,在圖像區中按一下:新增相片。
可以上傳照片或是由雲端直接選取照片:
當你使用手機的 Google 地圖時,也可以在選取一個地點時,看到自己新增的照片。
2015-07-23 09.55.50_resize 2015-07-23 09.55.41_resize
你可以在檢視圖片的時候,也來新增自己的照片(拍攝相片或從圖片庫中選擇)。
 

好康東東