2017年12月13日 星期三

Excel-日期轉換並計算相差日數(SUBSTITUTE,RIGHT,MID,LEFT)

(網友提問)在 Excel 的工作表中有一個原始日期清單,其格式為105.07.03,如何轉換為1050703,並且能計算二個日期相差的日數?
參考下圖,利用轉換後的日期,計算上下兩個儲存格相差的日數。
Excel-日期轉換並計算相差日數(SUBSTITUTE,RIGHT,MID,LEFT)

【公式設計與解析】
1. 轉換日期格式
儲存格C2:=SUBSTITUTE(A2,".","")
利用 SUBSTITUE 函數將儲存格A2中的『.』取代為『』(空白),相當於刪除『.』。

2. 計算轉換為的兩個日期相差的日數
儲存格D3:=DATE(LEFT(C3,3)+1911,MID(C3,4,2),RIGHT(C3,2))-
DATE(LEFT(C2,3)+1911,MID(C2,4,2),RIGHT(C2,2))
其中:DATE(LET(C3,3)+1911,MID(C3,4,2),RIGHT(C3,2))
LEFT(C3,3)+1911:利用 LEFT 函數取出儲存格C3內容的第1~3碼,即為民國年,再加上 1911,即可轉換為西洋年。
MID(C3,4,2):利用 MID 函數取出儲存格C3內容的第4~5碼,即為月。
RIGHT(C3,2):利用 RIGHT 函數取出儲存格C3內容的第6~7碼,即為日。
將以上三個年月日的參數置入 DATE 函數即可取得日期的數值。
將兩個日期的數值相減,即可得相距的日數。

2017年12月10日 星期日

手機利用Google的Files Go執行檔案對傳

Google 推出的 Files Go App,可以協助你清理手機儲存空間,其中內含一個不錯的工具可以用來在兩隻手機之間傳輸檔案,而且在沒有網際網路連線的環境下還是可行(沒有數據連線或WiFi連線也能分享檔案)。
在連線時 Files Go 會自動開啟你的手機藍牙,並且會對連線加密。所以連線手機的傳輸距離有一定的限制。
兩隻手機都要先安裝 Files Go,Files Go by Google:Google Play 下載
Files Go 可以傳輸各種檔案格式,包含應用程式也可以,例如A手機中已安裝的 App,也可以直接傳輸到另一隻B手機中安裝。
在 Files Go App 中切換至「檔案」之下,即可看到:傳送和接收。
手機利用Google的Files Go執行檔案對傳
以下用 vincent 和 lib 兩隻手機為例來使用 Files Go 傳輸檔案。
vincent:點選「傳送」,顯示「請你的好友開啟應用程式,然後輕觸 [接收]」。
(參考下圖左)。
lib:出現「正在等待傳送者」(參考下圖右)。
手機利用Google的Files Go執行檔案對傳 手機利用Google的Files Go執行檔案對傳
vincent:顯示「正在與lib連線」,正在產生連線ID。(參考下圖左)
lib:顯示「vincent想建立連線」。(參考下圖右)
lib:點選「連線」。(參考下圖右)
手機利用Google的Files Go執行檔案對傳 手機利用Google的Files Go執行檔案對傳
lib:顯示「正在與vincent連線」。(參考下圖左)
lib:顯示「已連線,等待檔未中…」。(參考下圖右)
手機利用Google的Files Go執行檔案對傳 Screenshot_20171210-221116_resize
vincent:選取一個檔案,並點選「傳送」。(參考下圖左)
vincent:檔案立即傳送,顯示「vincent傳送 1 檔案給 lib」。(參考下圖右)
手機利用Google的Files Go執行檔案對傳 手機利用Google的Files Go執行檔案對傳
vincent:可繼續傳送檔案給 lib。(參考下圖左和右)
手機利用Google的Files Go執行檔案對傳 手機利用Google的Files Go執行檔案對傳
相同的,因為連線已經建立了,所以:
lib:也可傳送檔案給 vincent。(參考下圖左和右)
手機利用Google的Files Go執行檔案對傳 手機利用Google的Files Go執行檔案對傳
利用 Files Go 接收的檔案位於手機系統的「Files Go」資料夾之下,其中有二個子資料夾,分別存放圖片和文件:Files Go Docs 和 Files Go Images。
手機利用Google的Files Go執行檔案對傳 手機利用Google的Files Go執行檔案對傳

2017年12月8日 星期五

Excel-根據同一欄有資料的儲存格對應標題依序顯示(OFFSET,COLUMN)

(網友提問)在 Excel 的工作表有下圖(上)的資料表,如何轉換為下圖(下)的資料表。
參考下圖,在每個姓名列中,要將有數字的儲存格,對照日期名稱後依序列在同一列中。
Excel-根據同一欄有資料的儲存格對應標題依序顯示(OFFSET,COLUMN)

【公式設計解析】
儲存格B13:{=IFERROR(OFFSET($B$1,0,SMALL(IF($B2:$K2<>"",
COLUMN($B2:$K2),""),COLUMN(A:A))-2),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加「{}」。
複製儲存格B13,貼至儲存格B13:K20。
(1) IF($B2:$K2<>"",COLUMN($B2:$K2),"")
判斷儲存格B2:K2的內容中,如果不是空白內容,則傳回所在的欄號,否則傳回空字串。
(2) SMALL(第(1)式,COLUMN(A:A))
根據第(1)式傳回的欄號,利用 SMALL 函數由小至小依序取出。當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。
(3) OFFSET($B$1,0,第(2)式-2)
根據第(2)式取出的欄號,代入 OFFSET 函數中,取出對應的儲存格內容。
(4) IFERROR(第(3)式,"")
當公式向右複製時,第(2)式可能會傳回錯誤訊息,所以利用 IFERROR 函數將錯誤訊息顯示為空白。

檢視其他文章

好康東東