[發問] 判定欄位日期區間，同一日期最多只能三次，請問如何設定

p6703

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時即已超過三筆）

Book2.rar (1.36 KB)

下載次數: 8

2013-5-31 15:27

Bodhidharma

回復 1# p6703

這一題有點給他複雜，而且日期的格式又很不正規
附檔加了一大堆輔助列，用了一個很土法煉鋼的方式：
先將日期正規化
然後以最後一列的區間，一天一天跟前面所有的區間比對，計算出現次數，如果超過3次(含)，就以資料驗證的方式顯示錯誤

整個公式寫得很醜，方法也很暴力，僅供參考…

判定日期區間_sol.rar (28.18 KB)

下載次數: 5

2013-6-1 02:46

sunnyso

回復 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

下載 (42.5 KB)

2013-6-1 05:27

資料驗證不重復3次-Sonny.rar (6.37 KB)

下載次數: 2

2013-6-1 05:27

sunnyso

回復 2# Bodhidharma

Sorry, 只看了標題, 沒看清楚樓主具體問題。請略過上面的回復。

Bodhidharma

回復 3# sunnyso

是說如果是單一日期(而非日期區間)的話
D欄儲存格資料驗證公式=COUNTIF(D:D,D1)<=3
好像就可以了，似乎不需要分該資料的前段跟後段

ML089

同一日期最多累積三次.rar (43.08 KB)

下載次數: 12

2013-6-4 00:51

Bodhidharma

回復 6# ML089

1. =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)))

複製代碼

這個公式真是了不起，我當初也試圖寫類似的東西，但是怎麼都轉不過來，所以只好僅處理最後一個輸入的資料

筆記：

1. COLUMN(INDIRECT("C1:C"&F2-E2+1,))

複製代碼

：使用R1C1格式，會得出{1,2,3…n}的1*n單列矩陣，n=該日期區間的天數，再+E2-1，即是該日期區間所有日期的單列矩陣

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個開始日期」

1. 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矩陣相乘，即可得出單列矩陣，也就是

1. 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的單位矩陣，則會得出一個單欄矩陣。因為我們需要的只是矩陣的最大值，因此單列矩陣或單欄矩陣都可以，因此就有原公式

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

站上果然臥虎藏龍，太感謝各位的回覆，解決了小弟的問題，雖然Bodhidharma兄有解釋了各程式的用法，但看來自己還是功力不足，不太能理解，再多研究看看，再次感謝各位^^