Bodhidharma

回復 2# boyao


直接寫在一個式子，套用到整個工作表
A3設定格式化條件：
=CHOOSE(MOD(COLUMN(A3),6)+1,A3<>IT3,IF(A3="",0,COUNTIF(D$3:D$100,A3)=0),0,A3<>D3,IF(A3="",0,COUNTIF(IT$3:IT$100,A3)=0),0)

(不太確定"2.每6欄為1組，找出大學及碩士缺少的變項"的意思，就照樓上的邏輯來寫)

格式化問題_sol.rar (22.88 KB)

下載次數: 5

2013-4-20 01:21

Bodhidharma

回復 4# jj369963


A59是@2Q2.3.text，D58也是@2Q2.3.text
但是width我比的是C58(11)和F58(1000)，兩者不同，所以有標色
另C59(1000)和F59(11)兩者也不同，所有標色

所以你要比的是什麼？大學和碩士「同變項的項目」 ，其width是否相同？

Bodhidharma

回復 6# jj369963

A3設定格式化條件：
=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)
基本上你這個資料沒有不同的地方
格式化問題_sol.rar (22.97 KB)

下載次數: 5

2013-4-20 21:52

Bodhidharma

回復 8# jj369963

你這個資料沒有上個資料的第二列，所以整個都要從A2開始
另外上個資料我是假設你資料只有100列以內，但是你這個資料更多，所以countif函數內要增加(我是假設你資料到1000筆)
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)
(是說如果你看得懂上面這個公式的話，應該是有能力自己發現問題在哪才對……)

格式化2_sol.rar (43.33 KB)

下載次數: 7

2013-4-20 22:24

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也可以，不過運算會變慢

其它都是相同的原理

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)