標題:
[發問]
判定欄位日期區間，同一日期最多只能三次，請問如何設定
[打印本頁]
作者:
p6703
時間:
2013-5-31 15:28
標題:
判定欄位日期區間，同一日期最多只能三次，請問如何設定
A欄位會key in每個日期的區間，例：6/1~6/10，筆數會逐筆增加，請問如何以公式自動判定，同一日期最多累積三次，如果有同一區間日期超過四次，即於當下輸入秀出錯誤訊息???:dizzy:
例附件三筆日期各為：6/1~6/7,6/2~6/8,6/4~6/6，如果我於第四筆中key in6/5即會出現錯誤訊息（因為以上三筆日期均已有包含6/5了，如果第四筆中又key in時即已超過三筆）
[attach]15119[/attach]
作者:
Bodhidharma
時間:
2013-6-1 02:43
本帖最後由 Bodhidharma 於 2013-6-1 02:46 編輯
回復
1#
p6703
這一題有點給他複雜，而且日期的格式又很不正規
附檔加了一大堆輔助列，用了一個很土法煉鋼的方式：
先將日期正規化
然後以最後一列的區間，一天一天跟前面所有的區間比對，計算出現次數，如果超過3次(含)，就以資料驗證的方式顯示錯誤
整個公式寫得很醜，方法也很暴力，僅供參考…
[attach]15126[/attach]
作者:
sunnyso
時間:
2013-6-1 05:27
回復
2#
Bodhidharma
不需要用VBA
1. 選定 D3:D50
2. 開啓資料驗證
3. 輸入公式 =(COUNTIF(D$1:D2,D3) + COUNTIF(D4:D$50,D3))<3
4. 選定 D2, 開啓資料驗證, 輸入公式 =COUNTIF(D3:D$50,D2)<3
[attach]15128[/attach]
[attach]15129[/attach]
作者:
sunnyso
時間:
2013-6-1 10:06
回復
2#
Bodhidharma
Sorry, 只看了標題, 沒看清楚樓主具體問題。請略過上面的回復。
作者:
Bodhidharma
時間:
2013-6-1 11:11
回復
3#
sunnyso
是說如果是單一日期(而非日期區間)的話
D欄儲存格資料驗證公式=COUNTIF(D:D,D1)<=3
好像就可以了，似乎不需要分該資料的前段跟後段
作者:
ML089
時間:
2013-6-4 00:52
[attach]15156[/attach]
作者:
Bodhidharma
時間:
2013-6-4 14:24
本帖最後由 Bodhidharma 於 2013-6-4 14:30 編輯
回復
6#
ML089
=IF(A2="","",MAX(MMULT(TRANSPOSE(((COLUMN(INDIRECT("C1:C"&F2-E2+1,))+E2-1)>=E$2:E2)*((COLUMN(INDIRECT("C1:C"&F2-E2+1,))+E2-1)<=F$2:F2)),E$2:E2^0)))
複製代碼
這個公式真是了不起，我當初也試圖寫類似的東西，但是怎麼都轉不過來，所以只好僅處理最後一個輸入的資料
筆記：
COLUMN(INDIRECT("C1:C"&F2-E2+1,))
複製代碼
：使用R1C1格式，會得出{1,2,3…n}的1*n單列矩陣，n=該日期區間的天數，再+E2-1，即是該日期區間所有日期的單列矩陣
(COLUMN(INDIRECT("C1:C"&F2-E2+1,))+E2-1)>=E$2:E2
複製代碼
，前面是單列矩陣，後面是單欄矩陣，假設該公式下拉到第k+1列，即會是{Day1,Day2,…,Dayn}>={E2;E3;…;Ek+1}，會得出一個k列n欄的矩陣，{X11,X12,…,X1n;X21,X22,…,X2n;X31,…,;…Xk1,Xk2,…Xkn}
Xkn的值即是「該日期區間的第n天，是否大於等於(從前往後算)第k個開始日期」
COLUMN(INDIRECT("C1:C"&F2-E2+1,))+E2-1)<=F$2:F2
複製代碼
一樣的道理，得出k列n欄的矩陣{Y11,Y12,…,Y1n;Y21,Y22,…,Y2n;Y31,…,;…Yk1,Yk2,…Ykn}
Ykn的值即是「該日期區間的第n天，是否小於等於(從前往後算)第k個結束日期」
X*Y得出{Z11,Z12,…,Z1n;Z21,Z22,…,Z2n;Z31,…,;…Zk1,Zk2,…Zkn}，Zkn即是「該日期區間的第n天，是否存在於(從前往後算)第k個日期區間中」
現在需要{Z11+Z21+…+Zk1,Z12+Z22+…+Zk2,…,Z1n+Z2n+…+Zkn}的矩陣，就是「該日期區間的第n天，在(從前往後算)第k個日期區間中出現的次數」如果這n個值中有任意一個大於3，即不符合驗證標準。
要製造這個矩陣有兩種方式，一種是製造一個1*k的單位矩陣跟k*n的Z矩陣相乘，即可得出單列矩陣，也就是
MMULT(TRANSPOSE(E$2:E2^0),((COLUMN(INDIRECT("C1:C"&F4-E4+1,))+E4-1)>=E$2:E4)*((COLUMN(INDIRECT("C1:C"&F4-E4+1,))+E4-1)<=F$2:F4))
複製代碼
另外一種方式是先將k*n的Z矩陣轉置為n*k，再乘一個k*1的單位矩陣，則會得出一個單欄矩陣。因為我們需要的只是矩陣的最大值，因此單列矩陣或單欄矩陣都可以，因此就有原公式
MAX(MMULT(TRANSPOSE(((COLUMN(INDIRECT("C1:C"&F2-E2+1,))+E2-1)>=E$2:E2)*((COLUMN(INDIRECT("C1:C"&F2-E2+1,))+E2-1)<=F$2:F2)),E$2:E2^0))
複製代碼
有種把高中矩陣乘法重新拿出來溫習的感覺……
作者:
p6703
時間:
2013-6-10 08:39
站上果然臥虎藏龍，太感謝各位的回覆，解決了小弟的問題，雖然Bodhidharma兄有解釋了各程式的用法，但看來自己還是功力不足，不太能理解，再多研究看看，再次感謝各位^^
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
