返回列表 上一主題 發帖

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

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
1# 跳轉到 »
發表於 2013-6-1 02:43 | 顯示全部帖子
本帖最後由 Bodhidharma 於 2013-6-1 02:46 編輯

回復 1# p6703

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

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

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

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
2#
發表於 2013-6-1 11:11 | 顯示全部帖子
回復 3# sunnyso

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

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
3#
發表於 2013-6-4 14:24 | 顯示全部帖子
本帖最後由 Bodhidharma 於 2013-6-4 14:30 編輯

回復 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))
複製代碼
有種把高中矩陣乘法重新拿出來溫習的感覺……

TOP

        靜思自在 : 成功是優點的發揮，失敗是缺點的累積。
返回列表 上一主題