Hsieh

回復 8# jj369963
格式化問題.rar (22.53 KB)

下載次數: 8

2013-4-20 22:45

2010版本寫的，試試看在2003版本是否適用
因為是利用定義名稱來定位，可將新資料複製貼上值來測試看看

Bodhidharma

回復 10# jj369963

設定格式化條件，很重要的是「相對參照」的概念，可參考
http://blog.xuite.net/asir63/blog/56090597-%E5%9C%A8excel%E8%A8%AD%E5%AE%9A%E6%A0%BC%E5%BC%8F%E5%8C%96%E6%A2%9D%E4%BB%B6%E4%B8%AD%E4%BD%BF%E7%94%A8%E5%85%AC%E5%BC%8F%3E

A2設定格式化條件公式：
=CHOOSE(MOD(COLUMN(A2),6)+1,A2<>INDEX(IT:IT,MATCH(IU2,IR:IR,0)),IF(A2="",0,COUNTIF(D$2:D$1000,A2)=0),0,A2<>INDEX(D:D,MATCH(IU2,B:B,0)),IF(A2="",0,COUNTIF(IT$2:IT$1000,A2)=0),0)

choose(mod(column(A2,6)+1,......)中，將你的資料分成六種情況來處理
mod(column(A2,6)+1，對於A欄的資料會是2，B欄會是3，C欄4，D欄5，E欄6，F欄1，G欄2...以此類推
對於1的狀況(即F欄、L欄…)，是用A2<>INDEX(IT:IT,MATCH(IU2,IR:IR,0))
對於2的狀況，用IF(A2="",0,COUNTIF(D$2:D$1000,A2)=0)
對於3的狀況，這個不用設，所以直接設成0
以此類推

1的狀況中，A2<>INDEX(IT:IT,MATCH(IU2,IR:IR,0))公式，一樣是相對參照的概念
excel2003最後一欄是IV，所以IT欄相對於A欄是「往左移3欄」的意思，同理IU是「往左移2欄」，IR是「往左移5欄」
因此INDEX(IT:IT,MATCH(IU2,IR:IR,0)，就是index(往左移3欄,match(往左移2欄,往左移5欄,0))
以F2來看，就是index(C:C,match(D2,A:A,0)，也就是C欄中，和F2欄相同變項的相目，所對應的值

2的狀況一樣的原理COUNTIF(D$2:D$1000,A2)=0，即是countif(往右移3欄,A2)=0
對A2來說，就是COUNTIF(D$2:D$1000,A2)=0，即在D2:D1000中，A2沒有出現
(當然D$2:D$1000是假設資料只有1000列以內，如果有更多要改變，或是直接改為COUNTIF(D:D,A2)=0也可以，不過運算會變慢

其它都是相同的原理

jj369963

回復 11# Hsieh
Dear:

謝謝版主的回應，不才的我其實一直在等您的說。


感謝Bodhidharma 大大提供公式，如下也確實成功。

=CHOOSE(MOD(COLUMN(A3),6)+1,A3<>index(IT:IT,match(IU3,IR:IR,0)),IF(A3="",0,COUNTIF(D$3:D$100,A3)=0),0,A3<>index(D:D,match(IU3,B:B,0)),IF(A3="",0,COUNTIF(IT$3:IT$100,A3)=0),0)

不過對版主的設定也很感興趣，所以試著套用在別的資料上，卻沒反應，是因為定義名稱的緣故嗎？如附檔

PS大大的定義名偁，太深澳，研究中


格式問題3.rar (54.1 KB)

下載次數: 5

2013-4-20 23:12

jj369963

回復 12# Bodhidharma

Dear大大：

感激，講解的真得非常清楚，今天又有收穫。而且用的方法也很妙，真是感謝。當初想到的公式只有countif 可是怎麼試都不對。

PS感激再感激

Hsieh

回復 13# jj369963

因為我的參照都是寫在定義名稱中
a=INDIRECT("RC"&INT((COLUMN()-1)/6)*6+4,0)會參照到所屬6欄內的第4欄同列位置
b=INDIRECT("R1C"&INT((COLUMN()-1)/6)*6+1&":R65536C"&INT((COLUMN()-1)/6)*6+1,0)會參照到所屬6欄內的第1欄整欄
w=INDIRECT("RC"&INT((COLUMN()-1)/6)*6+3,0) 會參照到所屬6欄內的第3欄同列位置   
x=INDIRECT("R1C"&INT((COLUMN()-1)/6)*6+4&":R65536C"&INT((COLUMN()-1)/6)*6+4,0)會參照到所屬6欄內的第4欄整欄
y=INDIRECT("RC"&INT((COLUMN()-1)/6)*6+1,0)會參照到所屬6欄內的第1欄同列位置
z=INDIRECT("R1C"&INT((COLUMN()-1)/6)*6+6&":R65536C"&INT((COLUMN()-1)/6)*6+6,0)會參照到所屬6欄內的第6欄整欄

因為所有定義及格式化條件都設好了
你可以將這個檔案作為範本檔案
只需將其他檔案的值複製貼到這個工作表上，然後另存新檔即可

Bodhidharma

回復 11# Hsieh

用iserror(match(...))來取代countif(...)=0，的確比較好

因此我公式可改為A2設定格式化條件：
=CHOOSE(MOD(COLUMN(A2),6)+1,A2<>INDEX(IT:IT,MATCH(IU2,IR:IR,0)),IF(A2="",0,ISERROR(MATCH(A2,D:D,0))),0,A2<>INDEX(D:D,MATCH(IU2,B:B,0)),IF(A2=0,0,ISERROR(MATCH(A2,IT:IT,0))),0)

jj369963

回復 15# Hsieh

Dear超版大大：

太強了，原來您直接做成範本了，試過之後真的可行。

感謝，也謝謝您認真的說明