2017年8月23日 星期三

Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)

國際標準書號(International Standard Book Number)簡稱 ISBN,是由 13 碼所組成。參考維基百科(https://zh.wikipedia.org/wiki/國家標準書號)上的說明:
國際標準書號號碼由 13 個碼的數字所組成,並以『-』或空格加以分隔,每組數字有其義涵。
○第一組:978或979。
○第二組:國家、語言或區位代碼
○ 第三組:出版社代碼:由各國家或地區的國際標準書號分配中心,分給各個出版社。
○ 第四組:書序碼,該出版物代碼,由出版社建立。
○ 第五組:檢驗碼,一位數,從 0 到 9。

檢驗碼為為了用來驗證 ISBN 是否正確之用,如何使用試算表來加以驗證?
ISBN 範例:9789863088363
前規則是:
1. 取前12碼計算加權的和(S)
依據下圖將每個位元乘上不同的加權(1、3)
Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)
2. 計算 S 除以 10 的餘數
147 ÷ 10 的餘數是 7。
3. 求 10 減掉餘數 = 檢驗碼
若餘數為 0,則檢驗碼=0。
本例 10-7=3,檢驗碼=3,兩者相符。
Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)
只要有一個數字變動,其檢驗碼就應該會不一樣。(下圖檢驗碼:0)
Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)

【公式設計與解析】
1. 計算數字加權和(S)
儲存格C5:=SUMPRODUCT(B1:M1*B2:M2)

2. 計算檢驗碼
儲存格C7:=MOD(10-MOD(SUMPRODUCT(B1:M1*B2:M2),10),10)
(1) MOD(SUMPRODUCT(B1:M1*B2:M2),10)
利用 MOD 函數計算加權和除以 10 的餘數。
(2) MOD(10-第(1)式,10)
10-第(1)式:計算 10 減餘數的結果。
再利用 MOD 函數計算除以 10 的餘數。
讓第(1)式結果為 0 者,本式傳回 0,否則傳回第(2)的結果。

如果你想試試一個儲存格就寫出公式,可以這樣做:
Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)
儲存格B2:
{=MOD(10-MOD(SUM((MOD(ROW(1:12),2)=1)*MID(B1,ROW(1:12),1)*1)+
SUM((MOD(ROW(1:12),2)=0)*MID(B1,ROW(1:12),1)*3),10),10)}
(1) SUM((MOD(ROW(1:12),2)=1)*MID(B1,ROW(1:12),1)*1)
計算加權乘積中要乘以 1 者的和。
(2) SUM((MOD(ROW(1:12),2)=0)*MID(B1,ROW(1:12),1)*3)
計算加權乘積中要乘以 3 者的和。

2017年8月22日 星期二

Excel-刪除每個題目的題號(FIND,MID)

網友問到:在 Excel 中,如果想要將一大堆有題號的題目要刪除題號,該如何處理?
參考下圖:在A欄中的題目每一題都有題號,題號有2碼、3碼、4碼,之後還有『.』。
Excel-刪除每個題目的題號(FIND,MID)

【公式設計與解析】
儲存格B2:=MID(A2,FIND(".",A2)+1,999)
(1) FIND(".",A2)
利用 FIND 函數找出儲存格A2中的內容第 1 個『.』。
(2) MID(A2,FIND(".",A2)+1,999)
利用 MID 函數找出『.』之後的文字,其參數『999』只是一個很大的數,只要比儲存格中的字數多即可。
複製儲存格B2,貼至儲存格B2:B14。

日本自由行從規劃到旅行結束之數位科技的應用

日前有六天的日本京阪神自由行,回顧整個行程對數位科技的應用及其帶來的幫助,以下就跟著我一起來探討。(文中的伙伴其實都是家人,一行人都是伙伴。)
○ 人:5人(含家庭成員兩大兩小+外甥一人)
○ 時:2017/8/9~2017/8/14
○ 地:途經日本京都、大阪、神戶等地
日本自由行從規劃到旅行結束之數位科技的應用

《行前規劃》
(為了聚焦,所以省略訂房與訂機票等。)由我負責主要行程規劃,大約花了二週時間找資料和規劃路線行程。我是菜鳥一個,所以相當仰賴網路上部落客提供的資料。
人家說「有錢 AND 有閒」才能去旅行,我們全家硬是擠出一點錢和一點閒,當然要好好的規劃,才不會辜負這難得的放空之旅。雖然是自由行的新手,但是只要好好做功課,應該也不會差到那裡去。(回來後才發現,所做的功課全派的上用場。而且給孩子一個「境教」,不管孩子參與規劃程度的多寡,大家都一起共同經歷了這一些。)

●在電腦和手機利用 Evernote 擴充程式儲存搜尋的網頁
平常我就使用 Evernote 來做筆記和搜集資料,所以這次自由行規劃路線,也是採用 Evernote 來完成。透過電腦版和手機版的 Evernote 的結合,資料源源不斷的被收錄。
在手機中安裝了 Evernote App,在瀏覽器上查詢到的資料,透過分享即可傳輸到雲端的筆記本中。透過和雲端同步,電腦上就能看到這些手機搜集的資料。畢竟手機還是比較能隨時、隨地的瀏覽資料,所以如何保存這些資料,相對重要多了。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用
在 Google Chrome 瀏覽器上,透過 Evernote Web Clipper 擴充功能,將查詢到網頁收納於 Evernote 筆記中。
日本自由行從規劃到旅行結束之數位科技的應用
一篇一篇搜集而來的網路文章,就像是做筆記般的整理在 Evernote 中,隨時可以在手機和電腦中查閱並整理。
日本自由行從規劃到旅行結束之數位科技的應用
有些旅遊相關的文章,每日都會透過 Feedly 進到我的閱讀世界,閱讀過後,如果要保留,我也是透過 Evernote 搜集保存下來。網頁內容會自動送來,收錄又如此的方便,所以每天都有一些資料被保留下來。養兵千日,終有一朝會用上。
日本自由行從規劃到旅行結束之數位科技的應用
日本自由行從規劃到旅行結束之數位科技的應用
●利用 Evernote 做旅遊規劃
看了別人提供的那麼多資料,也該是自己要實際規劃路線了!過程中當然是一日數變,改來改去,直到出發了,還是不滿意、不確定。因為規劃內容放在筆記中,在手機可以改,在電腦可以改,在雲端可以改,隨時可以改、隨地可以改,不怕改!
日本自由行從規劃到旅行結束之數位科技的應用
因為要給多人知道規劃的狀況,所以將 Evernote 筆記產生網頁,並透過 Google(https://goo.gl)轉換為短網址,用 Line 傳送給伙伴們知悉。這樣大伙們討論的基礎點才會一致。
日本自由行從規劃到旅行結束之數位科技的應用

●利用 Google Chrome 列印網頁成 PDF 檔放在手機上
因為查詢到的網頁內容有其參考價值,為了減少查詢時間和網路數據用量,也可以加快資料取得速度。(當然不考慮列印出來,即不環保也不好攜帶。廉航重量=費用)直接將在 Google Chrome 中查到的網頁資料列印成 PDF 檔,再儲存至手機中。雖然不見得都用的到,但是有備無患,而且查到資料後,舉手之勞而已!(手機存了很多檔案,但是重量都沒有變重。)
日本自由行從規劃到旅行結束之數位科技的應用
日本自由行從規劃到旅行結束之數位科技的應用

●將檔案上傳 Google 雲端硬碟並設定為離線閱讀
以上所儲存的 PDF 檔如何傳輸至手機呢?方法很多,隨你方便,至少可以用 USB 傳輸到手機上。而我是在電腦中先上傳至 Google 雲端硬碟(https://drive.google.com),再將手機的 Google 雲端硬碟 App 中設定檔案為「離線閱讀」。如此,在手機中開啟這些 PDF 檔,便不用再連線取得檔案來開啟。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

●利用 Backup and Sync from google 將檔案上傳 Google 雲端硬碟
我是利用 Goolge 剛推出的 Backup and Sync from google 工具,透過同步方式來上傳至 Google 雲端硬碟。用此工具,可以同步需端和我的電腦中的檔案,相當方便!
Backup and Sync from google:https://www.google.com/drive/download/
日本自由行從規劃到旅行結束之數位科技的應用
稍加設定,便可輕鬆使用!
日本自由行從規劃到旅行結束之數位科技的應用
日本自由行從規劃到旅行結束之數位科技的應用

●使用 Google 地圖進行路線規劃
關於整個自由行中對於 Google 地圖的相關運用,請參考這篇前面的文章:
 
 《旅行前》
 ●在 Google 地圖上標記想去的地點並且儲存
 ●在 Google 地圖上建立我的地點
 ●在 Google 地圖上量測到達地點的距離
 ●在 Google 地圖上查詢地點、計算時間並規劃路線
 ●將 Google 地圖上的路線新增至主畫面
 ●在 Google 地圖上先利用3D模式檢視景點
 ●在 Google 地圖上先行下載「離線地圖」
 ●在 Google 地圖上先將需要查詢的地點都輸入查詢一次
 《旅行中》
 ●在 Google 地圖上找到自己所處的位置
 ●在 Google 地圖上查詢要去的地點並且使用街景檢視
 ●在 Google 地圖上對要去的地點查詢評價及熱門時段
 ●在 Google 地圖上量測到達地點的距離
 ●在 Google 地圖上標記到達的地點以加入我的時間軸
 ●在 Google 地圖上即時查詢地點並規劃路線
 ●在 Google 地圖上開啟事先已規劃的路線
 ●在 Google 地圖上將已規劃的路線分享給同行伙伴
 ●在 Google 地圖上使用「標籤」來定位飯店或特定地點
 ●在 Google 地圖上使用「儲存停車位」來定位集合地點
 ●在 Google 地圖上分享位置給同行伙伴
 ●在 Google 地圖上「啟動行車模式」來導航路線
 ●在 Google 地圖上檢視訊息
 《旅行後》
 ●在 Google 地圖上調整我的時間軸上的地點和時間
 ●在 Google 地圖上將 Google 相簿中的相片加入地點
 ●在 Google 地圖上檢視「我的貢獻」

●利用 Line App 分享資訊和互相討論對話 
平常伙伴們想到什麼,就用 Line 即時對話,傳遞超連結和照片等。由於有些圖片和內容會有時效的問題,所以先將伙伴們建立一個群組,再將討論資料和照片放在群組的記事本和相簿中。自由行途中,也是都這樣互傳訊息和照片等。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

●利用 Chromecast 和 Chrome 瀏覽器將電腦投放到電視上多人進行討論
為了進行行前會議等討論工作,所以透過 Chrome 瀏覽器,結合 Google 的 Chromecast,將我的電腦上的相關資料內容,投影到家裡的電視機上,以進行討論工作。(家裡PC和電視有段距離)
我一邊在電腦上操作,伙伴們看著電視,七嘴八舌的你一言我一語,要修改的地方,我就直接在電腦上修改,陸陸續續就這樣形成了行程的共識。
當你接好了 Google chromecast 後,再選取 Chrome 瀏覽器中的「投放」。
日本自由行從規劃到旅行結束之數位科技的應用
再選取:投放桌面。(所有電腦的操作都會顯示在電視上)
日本自由行從規劃到旅行結束之數位科技的應用
連線成功,瀏覽器右上角會顯示 chromecast 圖示。
日本自由行從規劃到旅行結束之數位科技的應用
電視畫面已和電腦同步顯示了。
日本自由行從規劃到旅行結束之數位科技的應用
日本自由行從規劃到旅行結束之數位科技的應用

●先行在 Google Translate App 中下載日文語言以離線翻譯
設想面對日文和日語時,應該會用到 Google Translate App 來執行翻譯的工作,為了加快翻譯的速度,所以先行下載日文資料庫,就可以方便進行離線翻譯。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用
文字輸入或語音輸入中文字來翻譯成日文字,或是掃描日文字來翻譯成日文字,甚至唸出來,都不成問題。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用
買東西、看資料時,拿起來掃描一下日文字,答案隨手可得,還真是方便。

《旅行途中》
●使用 Google 地圖查詢景點、路線、時間、票價和交通狀況
關於整個自由行中對於 Google 地圖的相關運用,請參考這篇:
日本自由行從規劃到旅行結束之數位科技的應用

●租用 WiFi 分享器讓伙伴共用網路
出國沒有 WiFi 是不行的!WiFi 分享器極為重要,此行的伙伴共有 5 人,小孩的網路用量高,所以租用了二台 WiFi 分享器,每台每日 99 元。從早上8:00,開到晚上8:00,電力就快沒了,所以常常出動行動電源來救援。
規格如下:
○使用機型 / SoftBank-303ZT、SoftBank-501HW(2款隨機出貨)
○下載速度 / 4G LTE 最高187.5Mbps
○每日使用流量 / 無限制
○使用時間 / 持續使用約12小時
○連線台數 / 5-8台
○訊號涵蓋範圍 / 全日本適用 / 距離12~25公尺

●取另一支沒在使用的手機當作查詢地圖用
你的上一隻手機現在在那裡?
手邊正好有一隻汰換下來幾個月的 HTC M8,自由行的時候順便帶出國來使用(當做備用手機),主要是用在 Google 地圖的使用上,可以減少現用手機的使用電量和其他不時之需。先將出國前製作好的「你的地點中的地圖」分享給備用手機的登入的帳號,相關地點即可以在備用手機中輕鬆取得。
出國前已先將 Google 地圖 App 的版本更新到最新版,查詢路線、班次和時間、票價等,相當的方便。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

●使用 Google Translate 查詢日文
出發前,你已在手機的 Google Translate App 中下載了日文資料庫,當你遇到想要了解的日文字,可以使用 App 對著日文字拍照。現在 Google 提供多元的翻譯方式,無論利用拍照、麥克風輸入或手寫輸入等都能接受。
日本自由行從規劃到旅行結束之數位科技的應用
在拍好的照片中(掃描),用心指選取一些日文字,會即時翻譯成中文。(記得先設定日文→中文)
日本自由行從規劃到旅行結束之數位科技的應用
關於翻譯,你還有別的 App 可以選擇,例如使用 Microsoft Translate App,使用方式也是都可以透過用寫的、用唸的、用拍照的等方法來做翻譯。並且除了翻成文字,還能唸出翻譯好的文字。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用
日本自由行從規劃到旅行結束之數位科技的應用

●使用 Google Fit 記錄活動狀況
自由行中最重要的『行』,如何知道這幾天中,每一天的步行時間、步行距離、消耗熱量、步行步數等數據?你的自由行是都在坐車?還是都在走路?還是...
我的手機裝有 Google Fit App,所以可以隨時隨地記錄我的活動狀況,並且可以在時間軸中回顧每一天的活動狀態。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用
我的小米手環和 App 也隨時在記錄我的運動狀態,都可以提供一些參考。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

●自由行利用 Google 表單記帳
有記帳習慣的人,可以先行利用 Google 表單來簡單設計一個記帳的表單。仔細想想,你真的會這麼認真費工的每一筆消費都加以記錄?你願意花費額外的時間來記帳?
放在 Google 表單的消費記錄,可以轉成 Excel 檔,日後才能統計與分析。
日本自由行從規劃到旅行結束之數位科技的應用

●將相機中的照片利用藍牙傳輸到手機
其他伙伴都是使用手機拍照,只有我是使用單眼相機來拍照!一天下來,每天都拍了超過 300 張照片。我都是啟動相機中的藍牙,配合手機上的 APP,每天晚上將一天的成果,一次傳輸到我的手機上。再由我的手機,挑選照片上傳 Facebook 或是傳到 Line 分享給同行伙伴們。
日本自由行從規劃到旅行結束之數位科技的應用
一躺難得的旅程,只用手機來拍照,對我而言是不夠的。使用單眼相機,就算不是攝影高手,拍出來的影質也都不會太差,上千張照片,總有一些是好的吧!不枉費我每天背相機14小時以上。(不好意思!路人借我用一下。)
日本自由行從規劃到旅行結束之數位科技的應用

●隨時用 Google Keep 記錄所看、所思
自由行過程,有時步調快,有時步調慢,例如搭火車時,就有些空閒時間可用。我會利用手機中的 Google keep App,用唸方式把看到、想到的事情即時記錄下來,之後再微調文字。
Google keep 可以輸入文字之外,還可以附加照片和錄音檔等,也可以利用手寫方式來記錄自己的想法,是個輕巧、實用的好幫手。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用
這些記錄的文字,還可以直接放在 Google 文件中繼續編輯。
日本自由行從規劃到旅行結束之數位科技的應用
日本自由行從規劃到旅行結束之數位科技的應用
日本自由行從規劃到旅行結束之數位科技的應用

《旅行結束》
●利用 Google 相簿整理相片
旅行結束後,大量的照片一定要馬上整理,不然以人的墮性,很快就會遺忘了這件事。
整理的照片除了放在電腦硬碟中,我還會上傳至 Google 雲端硬碟當做備份。如此,可以在電腦上回味,也可以分享給他人看。如果用手機上的 Google 相簿 App 來看,也是相當方便實用。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用
當相片已分類放在雲端相簿中,將來可以運用關鍵字進行智慧搜尋,例如:食物、河…。當然你也可以找『人』,如果你有先設定人的臉孔,只要輸入例如:兒子,Google 會將所有的兒子照片全數列出。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用
如果你拍攝照片的手機或相機有啟動 GPS 記錄,則檢視放在 Google 相簿中的照片時候,也會同時顯示照片所在的位置。
日本自由行從規劃到旅行結束之數位科技的應用
當你在雲端檢視 Google 相簿中的一張有人的照片時,Goolge 也會即時的幫你找出照片中可以辨識的人臉有那些人。
日本自由行從規劃到旅行結束之數位科技的應用

●將相簿中的照片上傳各個地點(我的貢獻)
將拍的還不錯的照片提供給其他網友參考,所謂「取之於網路,用之於網路,回饋於網路」。所以,你可以進一步將雲端相簿中的照片放入各個地點,以供其他網友查看。不論美景、美食,通常照片都會比文字來的更有吸引力。(在地點中加入照片,不一定要從Google雲端相簿選取照片,也可以由手機和電腦硬碟中上傳照片檔。)
日本自由行從規劃到旅行結束之數位科技的應用
日本自由行從規劃到旅行結束之數位科技的應用

●統計 Google Fit 搜集的活動記錄
Google Fit App 已在自由行的過程中,完整的記錄了每一天的步行時間、步行距離、消耗熱量、步行步數等數據,行程結束可以來檢視和統計相關資料了。
日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用
自由行結束了,連上 Google Fit 網站(https://fit.google.com),可以在此擷取自由行這幾天的資訊。
日本自由行從規劃到旅行結束之數位科技的應用
當你點選了一個日期(本例:8/13),可以看到你要的資訊。
日本自由行從規劃到旅行結束之數位科技的應用
逐一將每一天的資訊複製到 Excel 的工作表中,再透過公式來計算六天來總共的:步行時間、步行距離、消耗熱量、步行步數。
日本自由行從規劃到旅行結束之數位科技的應用
計算方式可參考這篇:
日本自由行從規劃到旅行結束之數位科技的應用
旅行也是生活的一部分,旅行會用到的數位工具,也是生活中會用到的數位工具。工具是為幫助我們生活的更快樂,為了幫助我們解決問題的。旅行結束了,你要重新檢視數位工具對於你的生活產生的影響,是否有些數位工具要重新來學習了呢?
孩子們擅長使用的自拍棒,也算是一項數位工具?

日本自由行從規劃到旅行結束之數位科技的應用

好康東東