2015年2月28日 星期六

Excel-身分證字號驗證

網友想要驗證身分證字號是否正確,要如何使用 Excel 來幫這個忙呢?

首先要來理解中華民國國民身份證字號的編碼規則,你可以參考維基百科:

http://zh.wikipedia.org/wiki/中華民國國民身分證

其中第一碼的對應轉換字元:

有些目前已不使用:

其驗證規則:

根據以上的規則,設計以下的公式:

【設計公式】

儲存格E5:=IF(MOD(INT(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE)/10)+
MOD(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE),10)*9+SUMPRODUCT(VALUE(
MID(E2,ROW(1:9)+1,1)),{8;7;6;5;4;3;2;1;1}),10)=0,"正確","錯誤")

公式很長很嚇人,分解來看:

(1)

VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE):利用 VLOOKUP 函數來查詢第一個字母所對應的數字為多少。

(2)

INT(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE)/10):將上式求得的數字取其十位數。

(3)

MOD(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE),10):將上式求得的數字取其個位數。

(4)

(INT(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE)/10)+MOD(VLOOKUP(LEFT
(E2,1),A2:B27,2,FALSE),10)*9

根據規則,上式執行:(2)X1+(3)X9

(5)

SUMPRODUCT(VALUE(MID(E2,ROW(1:9)+1,1)),{8;7;6;5;4;3;2;1;1}):依序取出身分證字號中的每個數字,並對應乘以 8, 7, 6, 5, 4, 3, 2, 1, 1,並且予以加總。

(6)

公式:=IF(MOD(第(4)式+第(5)式,10)=0,"正確","錯誤")

將第(4)式和第(5)式的和除以 10,若餘數為0表示為正確的身分證編碼,否則為錯誤編碼。

2015年2月27日 星期五

Excel-查詢公式使用「名稱」更方便(名稱的介紹)

最近在輔導學生和教職員工參加 GLAD 的 BAP 認證時,同仁問到為何要使用「名稱」這個概念。因為大多數人在操作 Excel 時都沒有用過「名稱」,所以無法體會其用意。

以下圖中的統計表為例,要查詢季別和人員的交叉對應的內容:

儲存格D8:=INDEX(B2:E6,MATCH(C8,A2:A6,0),MATCH(B8,B1:E1,0))

公式正確的求得結果。接著來設定名稱:

選取儲存格A1:E6,按 Ctrl+Shift+F3 鍵,勾選「頂端列、最左欄」(預設),定義名稱:第一季、第二季、第三季、第四季和甲、乙、丙、丁、戊。

當你定義好名稱,如果選取「季報表」,則會自動幫你選取季報表的儲存格範圍。也就是你不用再自行用滑鼠選取儲存格範圍。

如果要套用在公式上,可以讓公式變的很精簡:

如果要計算甲第一季至第四季的和,公式:=SUM(甲)

如果要計算第一季中甲至戊的和,公式:=SUM(第一季)

看來使用名稱可以讓公式更易解讀,並且可以縮短公式。

所以,如果要求丁的第三季內容:

儲存格D8:=第三季 丁

藉著第三季和丁之間的空格(表示兩者的交集),即可求得內容,十分方便。你可以在公式中選取「第三季」:

按一下 F9 鍵,即可以看到「第三季」這個名稱所代表的資料陣列(內容以「;」隔開):

以下是「丁」這個名稱的資料陣列(內容以「,」隔開):

如果你使用「資料驗證」方式設定清單以方便選取,則公式修改為:

儲存格D8:=INDIRECT(B8) INDIRECT(C8)

你也要善用名稱來讓你使用 Excel 更方便。

2015年2月26日 星期四

Google-在Google地圖中新增自己拍攝的照片

新春期間是旅遊的旺季,每個人都留下了美好的回憶。如何將自己拍攝的漂亮照片整合到Google 地圖中,讓他人也能分享這些美景,做為旅遊的參考呢?

你可以分享照片,也可以分享環景圖。而要上傳照片得先登入個人帳號,然後找到你要分享的地點。

在地點資訊的下方按一下「新增相片」:

接著要上傳你的照片,可以由電腦中選取檔案,也可以由你的Google相簿中選取照片。

看到回應訊息要示已上傳成功,但是不會馬上顯示 在Google 地圖上,要一段時間才會出現。

另外也可以在觀看別人加入相片的位置來新增相片:

用以下這兩張照片來當例子:

 

一段時間過後(約要一天的時間),先前加入的照片已出現在Google地圖上了。

2015年2月25日 星期三

接合多張照片(微軟免費的Image Composite Editor)

剛放完連假,之前大家都趁著好天氣,到處玩、到處拍,累積了不少的照片。

整理照片時,發現有些照片場景,能夠接合成一大張,處理成環景圖的樣子。有人的手機裝有各種App,可以在拍照時即可拍攝大張的環景照片,如果沒有,也可以靠事後加工的方式來處理。

手邊剛好有微軟的 Image Composite Editor 應用程式,這是一套免費使用的程式。

網址:http://research.microsoft.com/en-us/um/redmond/projects/ice/

就像該網站展示的,你可以合成各種圖:

image

下載網址:http://research.microsoft.com/en-us/downloads/730cd6bb-6450-4e66-8101-a94e71cb0779/default.aspx

下載你要的版本後,安裝並啟動:

把你手邊的想要接合的照片匯入:(本例使用三張照片)

進行接合動作:

剪裁你要範圍:

儲存這張接合的照片:

這是匯出的結果:

通常照片不會這麼的完美,因為你照相的位置和拍攝的角度,在接合時會產生一些偏差。例如用另外三張來接合,結果像這樣:

相同的三張照片,透過Google相簿自動產生合成的效果如下:

這個軟體,還能處理影片,留給你自行練習了。

2015年2月21日 星期六

台南七股觀海樓

每次過年期間我都會經過台南七股,一大堆要去七股鹽山的車輛常常造成台17線塞車。其實七股鹽山附近的觀海樓也是不錯的選擇喔!

交通路線和要去七股鹽山一樣,只是要更往海邊一點。

路上遠遠的就可以看到觀海樓:

七股的潟湖在此水面平靜,欣賞成排的蚵架和之字型的定置型漁網,形成狀觀的景像。

在此稍坐片刻,心也會跟著寧靜下來。

如果,讓太陽在沉下去一點的落日餘睴更是無比美麗。

台南奇美博物館

座落在台南都會公園中的奇美博物館,新館已經正式營運。趁著回南部過年,早早就先預約成功,可以好好的參觀。

要來台南都會公園,交通是十分更利的,停車場也很方便。

大多數人是慕這個雄偉建築之名而來(下圖由Google相簿自動合成):

DSC_0221-PANO_resize

每個人拿到的門票,其背後的圖案都不相同,進去要好好參觀!不要被人多到像菜市場所影響喔!(裡面無法拍照,背包要朝前背!)

DSC_0001_resize

DSC_0187_resize

DSC_0212_resize

DSC_0211_resize

DSC_0221_resize

DSC_0199_resize

DSC_0243_resize

DSC_0250_resize

除了奇美博物館,也別忘了在公園中走走。

DSC_0238_resize

DSC_0237_resize

好康東東