麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 棘手的格式化問題，再麻煩大大多指教 [打印本頁]

作者: jj369963 時間: 2013-4-19 23:29 標題: 棘手的格式化問題，再麻煩大大多指教

問題：

每6欄為1組，做比較。如附檔，如有說明不清，請見諒。煩請個位先進多指教，發問是學習的關鍵。

1.每6欄為1組，把大學及碩士width不一樣的數字標上顏色
2.每6欄為1組，找出大學及碩士缺少的變項

[attach]14718[/attach]

作者: boyao 時間: 2013-4-20 00:28

回復 1# jj369963

確認看看是不是這樣?
[attach]14719[/attach]

作者: Bodhidharma 時間: 2013-4-20 01:22

回復 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組，找出大學及碩士缺少的變項"的意思，就照樓上的邏輯來寫)

[attach]14720[/attach]

作者: jj369963 時間: 2013-4-20 20:28

回復 3# Bodhidharma

回覆大大：
謝謝您的回應，不過使用公式標示出來的顏色似乎有些問題，

A欄與D欄都有@2Q2.3.text，width也都是1000。不過卻被標記顏色。

再麻煩大大回應指教，感激

作者: Bodhidharma 時間: 2013-4-20 21:13

回復 4# jj369963

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

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

作者: jj369963 時間: 2013-4-20 21:34

回復 5# Bodhidharma

Dear:
謝謝大大的回應，很抱歉我的題意不清。造成困擾真抱歉

是的。我要比較大學和碩士「同變項的項目」，其width是否相同(赴同的地方標上顏色)？
以及找出大學及碩士應該有相同一致的變項(位置不同列沒關係)，所以要找出大學及碩士缺少的變項。(每6欄為一組來做對照)

A59是@2Q2.3.text，D58也是@2Q2.3.text
但是width應該比的是C59(1000)和F58(1000)，兩者相同，所以不用標色
[email protected]与[email protected] 另C58(11)和F59(11)兩者相同，所以不用標色

謝謝大大的用心回答

再麻煩大大費心指教

作者: Bodhidharma 時間: 2013-4-20 21:52

回復 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)
基本上你這個資料沒有不同的地方
[attach]14721[/attach]

作者: jj369963 時間: 2013-4-20 22:12

Dear大大:
太感謝您了，確實成功。
因為上面的例子有點簡化，用在另外一個檔案(其實有好多檔案)，標示顏色似乎又有點問題[attach]14722[/attach]
可否再麻煩您幫我看一個檔案呢？因為套用在這一個檔案，似乎怪怪的。

再麻煩回應指教，感激

[attach]14722[/attach]

作者: Bodhidharma 時間: 2013-4-20 22:25

回復 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)
(是說如果你看得懂上面這個公式的話，應該是有能力自己發現問題在哪才對……)

[attach]14723[/attach]

作者: jj369963 時間: 2013-4-20 22:40

回復 9# Bodhidharma

Dear大大:

謝謝您確實成功了，真是不才，確實看不懂公式，還在研究中

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

如果大大願意熱心解說，會很高興，因為看了很久還是沒理出頭緒(mod是求餘數，覺得大大用的太進階了)

另外也很感激您的指教，再次謝謝

作者: Hsieh 時間: 2013-4-20 22:49

回復 8# jj369963
[attach]14724[/attach]
2010版本寫的，試試看在2003版本是否適用
因為是利用定義名稱來定位，可將新資料複製貼上值來測試看看

作者: Bodhidharma 時間: 2013-4-20 23:01

回復 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 時間: 2013-4-20 23:14

回復 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大大的定義名偁，太深澳，研究中

[attach]14725[/attach]

作者: jj369963 時間: 2013-4-20 23:22

回復 12# Bodhidharma

Dear大大：

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

PS感激再感激

作者: Hsieh 時間: 2013-4-20 23:26

回復 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 時間: 2013-4-20 23:35

回復 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 時間: 2013-4-20 23:36

回復 15# Hsieh

Dear超版大大：

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

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

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)