Re: [算表] excel計算特殊符號@

看板Office作者Tenka (Tenka)時間13年前 (2010/10/16 00:22)推噓1(1推 0噓 5→)

留言6則, 3人參與討論串2/2 (看更多)

※ 引述《wfdlm (指尖的星光)》之銘言： : 軟體: : EXCEL 2003 / 2007 版 : ( 以下文章有點長，再加上不專業的敘述敬請見諒 ) : 背景故事： : 由於想要從google docs 問卷調查表中直接統計人數 : 為了避免重覆投票（google 問卷似乎沒有防止此項功能） : 因此想說請投票者填入E-MAIL，以及 "同意" 或 "不同意" : 問卷回收後只要 countifs(range,"同意",range,"@")　 : 就大膽假設可以直接計算有填 "E-MAIL" 和 "同意" 的人數 : （因為e-mail中都至少有"@"符號，其他的方式我目前也沒有想到或是無法辦到） : 可是試了幾次以後，發現無法計算"@" 這樣的符號 : 請問版上高手，如何解決像這樣的問題？ : 或是如果改填 "身分證字號"，以及 "同意" 或 "不同意" : EXCEL 可以判別這組身分證字號是否有效嗎？ : (google有高手用EXCEL寫身分證產生器，可是不知道有沒有辦法靠語法來辨認) : 假設可以的話，　 : 請問要怎麼計算：有填 "身分證字號" 且 "同意" 的人數呢？ : 感謝看完的人，更感謝大家指教！ =IFERROR(IF(LEN(A1)<>10,"不符",IF(AND(--MID(A1,2,1)<>1,--MID(A1,2,1)<>2), "不符",IF(MOD(10-MOD(MID(LOOKUP(LEFT(A1,1),{"A","B","C","D","E","F","G", "H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y", "Z"},{10,11,12,13,14,15,16,17,34,18,19,20,21,22,35,23,24,25,26,27,28,29, 32,30,31,33}),1,1)*1+MID(LOOKUP(LEFT(A1,1),{"A","B","C","D","E","F","G", "H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y", "Z"},{10,11,12,13,14,15,16,17,34,18,19,20,21,22,35,23,24,25,26,27,28,29, 32,30,31,33}),2,1)*9+MID(A1,2,1)*8+MID(A1,3,1)*7+MID(A1,4,1)*6+MID(A1,5, 1)*5+MID(A1,6,1)*4+MID(A1,7,1)*3+MID(A1,8,1)*2+MID(A1,9,1)*1,10),10)=-- RIGHT(A1,1),"符合","不符"))),"不符") -- just for fun... -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 61.216.4.145 ※ 編輯: Tenka 來自: 61.216.4.145 (10/16 02:38)

推

wfdlm

10/16 09:26, , 1^F

10/16 09:26, 1^F

→

pbntt

10/16 21:52, , 2^F

10/16 21:52, 2^F

→

soyoso

02/17 16:06, , 3^F

02/17 16:06, 3^F