贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

目前日期文章:201502 (24)

瀏覽方式: 標題列表 簡短摘要

網友想要驗證身分證字號是否正確,要如何使用 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表示為正確的身分證編碼,否則為錯誤編碼。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

最近在輔導學生和教職員工參加 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 更方便。

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

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

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

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

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

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

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

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

 

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

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

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

整理照片時,發現有些照片場景,能夠接合成一大張,處理成環景圖的樣子。有人的手機裝有各種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相簿自動產生合成的效果如下:

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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

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

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

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

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

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

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

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

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

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

大多數人是慕這個雄偉建築之名而來(下圖由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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

最近台南北門的「水晶教堂」非常火紅,我們也跟著人家趕熱潮來參觀一下。

停車其實是方便的,但是像年假這種連續假期,再多車位也不夠。不過,停遠一點,走幾步路,在溫暖的冬天,也是一種享受。

的確很有吸引力,晚上更是漂亮。

整個區域是以北門遊客中心為主,當天的遊客多到爆:

附近還有「錢來也」這個傳統雜貨店:

DSC_0068_resize

還有鹽田展示:

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

因為要到泰安住溫泉飯店,所以經過苗栗的客家大院,先進去瞧瞧!

還沒到客家大院的園區,先經過一座大橋,橋上頭霸氣的「客家大院」四個字,很是雄偉。

 

客家大院的建築座落在桐花樂活公園園區中,這是特別用來展示客家族群日常生活用具及古傢俱陳設,展現客家精神。

 

 

等四月的時候這裡的桐花就會盛開,即可來賞五月雪了:

春天的花開的佷漂亮。

 

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

以前在電視上有看過介紹福建省的客家土樓已被列為世界文化遺產,是客家族群的代表建築。我想苗栗縣政府應該是想仿土樓的建築特色,來傳承及表現客家的文化吧!不過,參觀後覺得內容稍微不足,應該還要更充實,才能讓旅客不虛此行。或許以後內容會增加吧!

這棟建築位於高鐵後龍特定區(高鐵站附近),交通路線:苗栗縣後龍鎮校椅里新港路295號。(目前Google地圖尚未標示該景點)

圓樓實景:

一些各地的客家建築特色:

 

 

 

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

要回南部過年,所以一路由北往南玩下去!一大清早跑到新竹的南寮漁港。這裡交通十分便利,中山高+68號快速道路兩下就可以到。雖然只是匆匆一撇,稍微漫步一下,拍幾張照片,但也令我印象深刻。

交通路線:

相機隨意拍:

遊客中心尚未完成,所以沒開放:

有魚市場:

市場裡的海鮮零嘴不錯吃!

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

有網友想要在 Excel 的工作表中複製不連續的儲存格,而且在不同的位置依原來的排列來貼上,但是似乎有些困難存在。

參考下圖,已經選取了儲存格A2, A4, A6, A8, A10,想要複製到 I 欄的平行位置。

當按下 Ctrl+C 鍵進行複製,再於儲存格I2中按一下 Ctrl+V 鍵,執行貼上動作,卻發現結果並沒有依照複製儲存格的相對位置來貼上,而是分散的儲存格變成連續了。

所以,要改變做法。

先選取儲存格I2, I4, I6, I8, I10,這是要貼上的位置。(目前作用中儲存格為A10)

在作用中儲存格,輸入「=A10」,按一下 Ctrl+Enter 鍵,被選取的儲存格即會自動填滿公式,得到想要的結果。

vincent 發表在 痞客邦 PIXNET 留言(4) 人氣()

網友想要在 Excel 的工作表中計算多個欄位的乘積,如下圖中每一個商品有A組和B組的數量,如何計算所有A組數量乘以單價加上B組數量乘以單價所得的總金額?

 

【準備工作】

選取儲存格A1:D11,按 Ctrl+Shift+F3 鍵,定義名稱:商品、單價、A組、B組。

 

【公式設計】

(1) 使用 SUMPRODUCT 函數

儲存格F2:=SUMPRODUCT(單價*A組)+SUMPRODUCT(單價*B組)

儲存格F2:=SUMPRODUCT(單價*(A組+B組))

 

(2) 使用 SUM 函數+陣列公式

儲存格F2:{=SUM(單價*A組)+SUM(單價*B組)}

儲存格F2:{=SUM(單價*(A組+B組))}

以上皆陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{ }」

 

不管 (1) 或是 (2) 的方法都是將資料以陣列的方式來處理,在此先定義名稱來代替陣列使公式易讀。如果是擴充為C組、D組、...,你也會處理嗎?自行試試看!

vincent 發表在 痞客邦 PIXNET 留言(3) 人氣()

網友想要在 Excel 的一個含有日期和金額的清單中,分別計算例假日和平日之金額的平均,該如何處理呢?(參考下圖)

這個問題藉助 SUMPRODUCT 函數可以輕易達成。

【準備工作】

選取儲存格A1:C26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、金額。


【公式設計】

(1) 求週六和週日的平均

儲存格F2:=SUMPRODUCT((WEEKDAY(日期,2)>5)*金額)/
SUMPRODUCT(--(WEEKDAY(日期,2)>5))

WEEKDAY(日期,2):找出日期陣列中星期一至星期日對應傳回 1 至 7。

WEEKDAY(日期,2)>5:判斷日期陣列在 WEEKDAY 傳回傳值是否大於5的 TRUE/FALSE 陣列。(星期六為 5、星期日為 7 )

SUMPRODUCT(--(WEEKDAY(日期,2)>5)):在 SUMPRODUCT 函數中使用「--」,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列,即可計算出符合條件的「個數」。

SUMPRODUCT((WEEKDAY(日期,2)>5)*金額):計算符合條件的金額「總和」。

把上式來除以上上式,即可求得符合條件的平均。(總和/個數)


(2) 求週一至週五的平均

儲存格F3:=SUMPRODUCT((WEEKDAY(日期,2)<=5)*金額)/
SUMPRODUCT(--(WEEKDAY(日期,2)<=5))

原理同(1),差別在於 WEEKDAY(日期,2) 的傳回值若為 1 至 5 則為平日。

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

在 Excel 的工作表中有一個如下圖的資料表,其中有些儲存格中沒有輸入數值,要如何取出每一列中有輸入數值的最左邊一個數值呢?(參考下圖)


【公式設計】

儲存格G2:{=INDEX(A2:F2,MIN(IF(A2:F2<>"",COLUMN(A2:F2))))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{ }」

IF(A2:F2<>"",COLUMN(A2:F2)):判斷儲存格A2:F2中不是空白儲存格者,傳回其第幾欄的陣列。

MIN(IF(A2:F2<>"",COLUMN(A2:F2))):取出上式中傳回第幾欄陣列的最小值。

最後再透過 INDEX 函數查表傳回儲存格內容。

複製儲存格G2,貼至儲存格G2:G13。

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

最近到台中時特別到南屯區鎮和巷來看台中地景藝術節的「小熊」。展覽期間是1/30到3/29日。

說牠是小熊,牠又那麼的大隻,霸氣的坐在地上。

由於小熊是在田中,所以要把車子停在外面。再尋著「迷失小熊」指標,即可到達。

附近的田都有在耕作,所以不可以走在田埂路上,以免侵擾了農家,破壞田埂路。

參考下圖了解辦這個展覽的人的用意:

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

最近到台中時經過大雅區,順道繞去大林路上,看看這裡的麥田。

現在麥子才剛翠綠,要一段時間才會熟成,那時的景像又不一樣了。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

藉著送兒子來東湖上音樂課時,不小心發現了附近内溝溪「樂活公園」的整排櫻花開的這麼任性,這一旁的居民會不會太幸福了!在都市裡就能賞花,是城市人的小確幸。

 

在 Google 地圖上台北市加油站背面的內溝溪到更北邊的康樂街61巷之間(不是南邊的61巷),其實是個公園,Google 地圖尚未標示出來,要注意:附近停車不是十分方便。

數十公尺整排的櫻花,一旁的步道整理的很好。愈來愈多人湧入來拍照、賞櫻,要注意哦!不要為了拍照而踐踏草皮。賞櫻要快!現在已是盛開,這裡種的櫻花全是「寒櫻」。

年青男女一起來賞花,真是幸福!

花的美,不容易描述,看看照片,自行體會,遠看、近觀各異其趣!

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

有網友想要將在 Excel 資料表中的兩種民國年的表示法,轉換為西元年的表示法,該如何處理呢?(參考下圖)

觀察這兩種民國年的表示法,對 Excel 而言,其被認為二種「字串」,而非日期。在 Excel 中日期是一種數值,同一個日期不管以何種格式顯示,其背後儲存格的數值都是相同的。

儲存格B2:

=TEXT(DATE(MID(A2,1,3)+1911,MID(A2,5,2),MID(A2,8,2)),"yyyy/mm/dd")

MID(A3,1,3)+1911:取出儲存格A2日期字串的1-3碼再加1911,當為「年」。

MID(A2,5,2):取出儲存格A2日期字串的5-6碼,當為「月」。

MID(A2,8,2):取出儲存格A2日期字串的8-9碼,當為「日」。

將上述的「年、月、日」代入 DATE 函數,轉換為 Excel 可接受的日期格式(其為一個數字)。

再藉由 TEXT 函數轉換為 yyyy/mm/dd 格式,即「年四碼/月二碼/日二碼」的格式。

複製儲存格B2,貼至儲存格B2:B23。

同理,

儲存格E2:

=TEXT(DATE(MID(D2,1,3)+1911,MID(D2,5,2),MID(D2,8,2)),"yyyy/mm/dd")

 

【同場加映】

如果你的民國年是如下圖中字數不規則,則可以參考另一篇文章的說明:

http://isvincent.pixnet.net/blog/post/42874681

儲存格B2:=TEXT(DATE(LEFT(A2,FIND(".",A2)-1)+1911,MID(A2,FIND(".",A2)+1,
FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1),RIGHT(A2,LEN(A2)-FIND(".",A2,
FIND(".",A2)+1))),"yyyy/mm/dd")

image

vincent 發表在 痞客邦 PIXNET 留言(3) 人氣()

在 Excel 的資料表中一個多個項目的類別清單,有網友想要依不同類別編序號,該如何處理?(參考下圖)

【公式設計】

觀察類別清單中,已經將同類的項目集合在一起。

儲存格C2:=A2&TEXT(COUNTIF($A$2:A2,A2),"000")

COUNTIF($A$2:A2,A2):計算從第一個儲存格A2開始,到指定的儲存格之間的範圍共有幾個和儲存格A2相同的內容。

TEXT(COUNTIF($A$2:A2,A2),"000"):使用 TEXT 函數將上式所傳回的數值格式化為三位數。

複製儲存格C2,貼至儲存格C2:C23。

這個公式即使用在類別的項目未排序的狀況,仍可適用。

image

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友想要在 Excel 的資料表中 A, B兩組找出重覆者剔除,也就是說要留下完全沒有重覆者的名單,該如何處理呢?(參考下圖)

 

【準備工作】

1. 將A組資料和B組資料分別複製到C欄的「合併」欄位下。

2. 選取儲存格C1:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:合併。

 

【設計公式】

儲存格D2:{=OFFSET($C$1,SMALL(IF(COUNTIF(合併,合併)=1,ROW(合併),999),ROW(1:1))-1,)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{、}」

COUNTIF(合併,合併):在陣列公式中找出「合併」欄位中的每一個儲存格在C欄中出現幾次,若為 1 則出現一次,若大於 1 代表重覆出現。

IF(COUNTIF(合併,合併)=1,ROW(合併),999):在陣列公式中,若儲存格內容沒有重覆出現,則傳回「合併」的列號(例如:「辛」傳回 2 ),若有重覆則傳回「999」。(在此,999 只是設定一個較大的數待用而已。)

SMALL(IF(COUNTIF(合併,合併)=1,ROW(合併),999),ROW(1:1)):利用 SMALL 函數在公式向下複製時,可以取得第 1, 2, 3, … 小的數值,這是不重覆名單的第 1, 2, 3, … 小的列號。

最後,透過 OFFSET 函數,根據上式求得的列號來取得儲存格內容。

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

1 2
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼