Excel 批號與日期的比對，但特定日期另有限制，求解?
原問題網址http://tw.knowledge.yahoo.com/question/question?qid=1013071404023#ooa_hash現在要多一個條件就是，若D欄的第2、3碼&第7、8碼為0331或0531或0830(0831)或1031或1231時，則E欄日期強制為0930或1130或0228(0228)或0430或0630，否則F欄也顯示"錯誤"兩字。問題檔案連結http://www.FunP.Net/536987更新:問題檔案連結-修正版-(又新增了一些限制唷！)http://www.FunP.Net/525421更新2:問題檔案連結-修正版2-(最後了！QQ)http://www.FunP.Net/802093
參考１： =IF(OR(C15={"L000011","L000022"})*ISERR(0/((MIN(DATE(201&LEFT(D15),MID(D15,2,2)+{6,7},DAY(1&LEFT(D15)&"/"&MID(D15,2,2)&"/"&MID(D15,7,2))*{1,0})))=--E15)),"錯誤","") 2013-12-31 13:34:51 補充： 參考２： =IF(OR(C15={"L000011","L000022"})*ISERR(0/((MIN(DATE(201&LEFT(D15),MID(D15,2,2)+{6,7},DAY(TEXT(LEFT(1&D15,4),"0-00-")&MID(D15,7,2))*{1,0})))=--E15)),"錯誤","") 2014-01-02 12:55:37 補充： EXCEL．比對編號的〔日期碼〕，與六個月後的日期是否相符 ＜．准提部林．＞ －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－ ■公式： Ｇ１５： =IF(OR(C15={"L000011","L000022"})*ISERR(0/((MIN(DATE(201&LEFT(D15),MID(D15,2,2)+{6,7},DAY(1&LEFT(D15)&"/"&MID(D15,2,2)&"/"&MID(D15,7,2))*{1,0})))=--E15)),"錯 或： =IF(OR(C15={"L000011","L000022"})*ISERR(0/((MIN(DATE(201&LEFT(D15),MID(D15,2,2)+{6,7},DAY(TEXT(LEFT(1&D15,4),"0-00-")&MID(D15,7,2))*{1,0})))=--E15)),"錯誤","") －－說明－－ １．=DAY(TEXT(LEFT(1&D15,4),"0-00-")&MID(D15,7,2)) 此公式除了可以檢測編號的〔日期碼〕是否〔錯誤〕， 同時提供 DATE 函數第３個參數使用。 例：４／３１．８／３２．非〔閏年〕的２／２９，皆會顯示錯誤。 ２．=MIN(DATE(201&LEFT(D15),MID(D15,2,2)+{6,7},DAY(TEXT(LEFT(1&D15,4),"0-00-")&MID(D15,7,2))*{1,0})) 公式表達為：=MIN(DATE(年, 月+{6,7}, 日*{1,0})) 目的：抓出指定日期往後６個月的日期， 當抓到的日期跨到第７月，則自動回推至６個月的月底， 若６個月後遇〔閏年〕的２月，仍可正確抓出２／２９。 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－ ＜範例檔＞： 檔案名稱：20140102a01(比對日期顯示錯誤).rar 下載位址：http://www.funp.net/159699 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
●九州娛樂網站http://ts777.cc●●●運彩遊戲、真人遊戲、電子遊戲、對戰遊戲、對戰遊戲●●●●新舊會員儲值就送500點●真人百家樂彩金等你拿●線上影片直播、正妹圖、討論區免費註冊歡迎免費體驗交流試玩!●九州娛樂網站http://ts777.cc您的好厲害，本來想說條件越限制越多，如果不行的話，就拆成兩個公式分開限制了！沒想到還是被您弄出來了！而且還貼心的考慮到閏年，真岡心...！我晚點再試看看公式喔！謝謝！對了關於閏年日怎什麼意思?會自動判定那年有無閏年嗎?2013-12-2718:08:21補充：目前是沒這需求，不過002公式倒是可以備而不用，總之謝謝您的貼心！2013-12-2800:58:18補充：003另一個寫法有點問題呢！因為C、D、E欄都空白竟然也會出現錯誤。2013-12-2808:09:49補充：001公式昨天還可以用，可是今天不知道為什麼打開我的報表C、D、E欄數據都對，反而顯示錯誤，於是用另開一個新EXCEL檔測試公式，卻又正常，可見公式沒問題，但我分析工具箱也都打開了說，好奇怪。不過幸好還有002閏年公式可用。2013-12-2808:15:28補充：TOD大：第2、3碼&第7、8碼為0131或0731時，E欄日期則"不需"強制顯示0730(正確應為0731)或0130(正確應為0131)，只有我剛開始說的那幾個(0331或0531或0830或0831或1031或1231時)才需要。2013-12-2808:17:30補充：另外D欄第7、8碼因為是日期的關係，所以不可能會超過31日，所以可以如果超過31日也顯示錯誤嗎?針對以上問題，我再上傳一個檔案方便測試。2013-12-2814:00:50補充：011很接近了，能幫我用成C、D、E欄沒內容時也不顯示錯誤嗎?2013-12-2909:46:34補充：有針對012的意思新增檔案─問題檔案連結-修正版2-2013-12-3101:36:17補充：發現兩個問題1.C欄(產品)不在限制內的號碼也會出現錯誤，正確應不執行比對，故也不會有錯誤。2.D欄(批號)為4月31日、6月31日、9月31日、11月31日等，不會出現錯誤，此四個月分應該不會有大於30號的日期，故應該要顯示錯誤。以上兩問題如修正版3的最下面新增。http://www.funp.net/6923222014-01-0205:41:24補充：准提部林大大的公式沒問題，謝謝您的解答，也謝謝Daniel大契而不捨幫我修改解答，真的非常感謝！最後，請記得上答唷！F15:=IF(OR(AND(C15<>{"L000011","L000022"}),COUNT(0/(LEFT(D15)&MID(D15,2,2)&TEXT(MIN(IF(MONTH(E15)=2,28,30),MID(D15,7,2)),"00")=MID(TEXT(EDATE(E15,-6),"ymmdd"),2,9)))),"","錯誤")試試看!2013-12-2610:37:17補充：若考慮到潤年的2/28為2/29，則F15:=IF(OR(AND(C15<>{"L000011","L000022"}),COUNT(0/(LEFT(D15)&MID(D15,2,2)&TEXT(MIN(IF(MONTH(E15)=2,28+(MOD(YEAR(E15),4)=0),30),MID(D15,7,2)),"00")=MID(TEXT(EDATE(E15,-6),"ymmdd"),2,9)))),"","錯誤")2013-12-2612:29:16補充：另一個寫法，F15:=IF(COUNT(0/OR(AND(C15<>{"L000011","L000022"}),DATE(LEFT(YEAR(E15)-(MONTH(E15)<7),3)&LEFT(D15),MID(D15,2,2)+6,MIN(IF(MID(D15,2,2)-6=2,28,30),MID(D15,7,2)))=--E15)),"","錯誤")2013-12-2623:48:24補充：閏年的意思，是2月有29天，所以如果是閏年，理論上原28可改用到29，但不知版大的需求因為通常閏年是西元年4的倍數者有2月29日，因此在公式裡加入判斷，被4整除時多加1天2013-12-2811:46:44補充：再試試:=IF(COUNT(0/OR(AND(C15<>{"L000011","L000022"}),LEFT(D15)&MID(D15,2,2)&TEXT((--MID(D15,7,2)<32)*MIN(LOOKUP(MONTH(E15),{1,2,3,7,8;31,28,30,31,30}),MID(D15,7,2)),"00")=MID(TEXT(EDATE(E15,-6),"ymmdd"),2,9))),"","錯誤")2013-12-2921:45:44補充：=IF((COUNTA(C15:E15)=0)+COUNT(0/OR(AND(C15<>{"L000011","L000022"}),LEFT(D15)&MID(D15,2,2)&TEXT((--MID(D15,7,2)<32)*接下段2013-12-2921:45:58補充：MIN(LOOKUP(MONTH(E15),{1,2,3,7,8;31,28,30,31,30}),MID(D15,7,2)),"00")=MID(TEXT(EDATE(E15,-6),"ymmdd"),2,9))),"","錯誤")
以上文章來自奇摩知識家，如有侵犯請留言告知
https://tw.answers.yahoo.com/question/index?qid=20131225000010KK02055