[發問] 棘手的格式化問題，再麻煩大大多指教

帖子: 147
主題: 46
精華: 0
積分: 205
點名: 0
作業系統: windows xp
軟體版本: office 2003
閱讀權限: 20
性別: 女
註冊時間: 2012-3-19
最後登錄: 2026-9-22

17^#

發表於 2013-4-20 23:36 | 只看該作者

回復 15# Hsieh

Dear超版大大：

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

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

Bodhidharma

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

16^#

發表於 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)

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 142
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2026-7-16

2025年全勤獎章 2024年全勤獎章 2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

15^#

發表於 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欄整欄

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

學海無涯_不恥下問

帖子: 147
主題: 46
精華: 0
積分: 205
點名: 0
作業系統: windows xp
軟體版本: office 2003
閱讀權限: 20
性別: 女
註冊時間: 2012-3-19
最後登錄: 2026-9-22

14^#

發表於 2013-4-20 23:22 | 只看該作者

回復 12# Bodhidharma

Dear大大：

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

PS感激再感激

帖子: 147
主題: 46
精華: 0
積分: 205
點名: 0
作業系統: windows xp
軟體版本: office 2003
閱讀權限: 20
性別: 女
註冊時間: 2012-3-19
最後登錄: 2026-9-22

13^#

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

格式問題3.rar (54.1 KB)

Bodhidharma

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

12^#

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

其它都是相同的原理

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 142
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2026-7-16

2025年全勤獎章 2024年全勤獎章 2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

11^#

發表於 2013-4-20 22:49 | 只看該作者

回復 8# jj369963

格式化問題.rar (22.53 KB)
2010版本寫的，試試看在2003版本是否適用
因為是利用定義名稱來定位，可將新資料複製貼上值來測試看看

學海無涯_不恥下問

帖子: 147
主題: 46
精華: 0
積分: 205
點名: 0
作業系統: windows xp
軟體版本: office 2003
閱讀權限: 20
性別: 女
註冊時間: 2012-3-19
最後登錄: 2026-9-22

10^#

發表於 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是求餘數，覺得大大用的太進階了)

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

Bodhidharma

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

9^#

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

格式化2_sol.rar (43.33 KB)