標題:
有關自動變色及剩餘資料問題?
[打印本頁]
作者:
jumky
時間:
2011-1-16 22:44
標題:
有關自動變色及剩餘資料問題?
請問大大：
該如何在進貨型號跟統計標籤欄裡設公式?
詳如附件[attach]4414[/attach]
作者:
Hsieh
時間:
2011-1-16 23:07
回復
1#
jumky
問題1：
進貨型號A2設定格式化條件
=COUNTIF(出貨型號!$A:$A,$A2)>0
向下複製格式
問題2：
統計A2陣列公式向下複製
{=INDIRECT("進貨型號!A"&SMALL(IF((COUNTIF(特別型號!A:A,進貨型號!$A$2:$A$7)=0)*(COUNTIF(出貨型號!A:A,進貨型號!$A$2:$A$7)=0),ROW($A$2:$A$7),""),ROW(A1)))}
作者:
jumky
時間:
2011-1-16 23:52
請問Hsieh大大..
是讓游標停於A2欄位,然後再至格式/設定格式化條件裡的條件一選擇公式為...=COUNTIF(出貨型號!$A
A,$A2)>0嗎?..可是它會出現"不能在格式化的準則中參照到其他工作表或活頁簿"的訊息?
另外..在統計輸入陣列公式...上面按完ENTER鍵不會出現結果....就還是公式...??
不好意思..我太笨了....能否請大大說明該如何操作才對...??
作者:
Hsieh
時間:
2011-1-17 10:03
回復
3#
jumky
抱歉!忘記2003版會有此限制
只要把出貨型號!A:A定義名稱
[attach]4420[/attach]
然後在格式化條件引用名義即可
[attach]4421[/attach]
至於陣列公式請看底下連結文章
http://forum.twbts.com/thread-1277-1-1.html
作者:
jumky
時間:
2011-1-17 12:02
回復
4#
Hsieh
Hsieh大大您好：
我已經照您說的定義名稱..可還是一樣出現"不能在格式化的準則中參照到其他工作表或活頁簿"的訊息?
我把用的放在附件...還請大大指點...[attach]4422[/attach]
作者:
Hsieh
時間:
2011-1-17 13:42
回復
5#
jumky
進貨型號!A2被選取狀態下輸入，如圖
[attach]4424[/attach]
然後再用複製格式刷，向下複製格式
作者:
jumky
時間:
2011-1-17 20:13
回復
6#
Hsieh
Hsieh大大您好：我照您說的操作設格式化條件..可當按下確定時就會出現錯誤訊息.如下：
[attach]4438[/attach]
作者:
Hsieh
時間:
2011-1-17 20:49
回復
7#
jumky
力馬幫幫忙!
請你看清楚公式
作者:
jumky
時間:
2011-1-17 21:39
本帖最後由 jumky 於 2011-1-17 22:42 編輯
回復
8#
Hsieh
Hsieh大大..不好意思...因為你第一次po的公式跟第二次的不一樣...我以為都一樣就一直用您第一次po的....現在可以了...謝謝您....不好意思.讓您費心了...
作者:
jumky
時間:
2011-1-17 23:44
Hsieh大大：
能否麻煩您解釋一下{=INDIRECT("進貨型號!A"&SMALL(IF((COUNTIF(特別型號!A:A,進貨型號!$A$2:$A$7)=0)*(COUNTIF(出貨型號!A:A,進貨型號!$A$2:$A$7)=0),ROW($A$2:$A$7),""),ROW(A1)))}裡的公式意思...因為想其他表格也能學以致用....請大大指點...^^...另外裡面的ROW($A2:$A$7)及ROW(A1)是指統計的標籤裡嗎.. ??
作者:
jumky
時間:
2011-1-26 21:23
請問Hsieh大大：
公式裡ROW(A1)是指哪裡..?還有每次開這個檔案時...它都會自動重算儲存格要等很久....請問該如何加快它的速度?
作者:
Hsieh
時間:
2011-1-26 22:48
回復
11#
jumky
ROW(A1)會傳回1
會重算很久應該是使用陣列公式吧
要看整個工作表公式的使用量才知道什麼問題
作者:
jumky
時間:
2011-1-27 17:16
請問Hsieh大大：
ROW(A1)是指進貨型號裡的A1...還是統計標籤裡的A1...?
沒錯是使用陣列公式所以變得緩慢....可它必須使用陣列公式才能重新計算...而每次開此檔...就會跑很久...每做一個動作也會跑很久....連帶其他開啟的EXCEL檔也會變得動作緩慢....請問有什麼方法可以避免此情形發生...??
作者:
Hsieh
時間:
2011-1-27 19:45
回復
13#
jumky
{=INDIRECT("進貨型號!A"&SMALL(IF((COUNTIF(特別型號!A:A,進貨型號!$A$2:$A$7)=0)*(COUNTIF(出貨型號!A:A,進貨型號!$A$2:$A$7)=0),ROW($A$2:$A$7),""),ROW(A1)))}
IF((COUNTIF(特別型號!A:A,進貨型號!$A$2:$A$7)=0)*(COUNTIF(出貨型號!A:A,進貨型號!$A$2:$A$7)=0),ROW($A$2:$A$7),"")
這段是如果進貨型號!$A$2:$A$7沒出現在特別型號!A欄而且進貨型號!$A$2:$A$7也沒在出貨型號!A欄就傳回$A$2:$A$7的列號，否則傳回空白
這會產生一個2~7的數字與空字串的陣列
利用SMALL函數取出由小到大的數字，SMALL會忽略文字
ROW(A1)若向下複製會從A1變為A2，A3，A4......
這就是SMALL的第2個參數
ROW(A1)傳回1就是取2~7的數字與空字串的陣列中的最小值
ROW(A2)傳回2就是取2~7的數字與空字串的陣列中的第二小值
以此類推
最後使用INDIRECT函數指到儲存格
"進貨型號!A"是說要指到進貨型號!A欄
SMALL取得的值就是列號
例如
2~7的數字與空字串的陣列中最小值是3
那麼
SMALL(IF((COUNTIF(特別型號!A:A,進貨型號!$A$2:$A$7)=0)*(COUNTIF(出貨型號!A:A,進貨型號!$A$2:$A$7)=0),ROW($A$2:$A$7),""),ROW(A1))
就傳回3
配合 "進貨型號!A"&
就得到字串"進貨型號!A3"
所以該儲存格就會得到進貨型號!A3的值
作者:
jumky
時間:
2011-1-27 23:37
本帖最後由 jumky 於 2011-1-27 23:45 編輯
嗯...了解..謝謝Hsieh大大詳細解說...^^不好意思,再次請問大大,因為我使用陣列公式的資料有將近200筆..是否因使用了過多陣列公式...導致excel重算儲存格時整個文書處理作業緩慢...就連存檔也像當機一樣....??請問是否有解決方法,能使用該公式又不致於讓excel一開啟就產生作業緩慢現象..??
作者:
Hsieh
時間:
2011-1-27 23:54
回復
15#
jumky
陣列公式的最大壞處就是會造成運算過於複雜
建議使用輔助欄來做，速度會差很多
不然就只有用VBA只取得值
各有利弊，看你要甚麼方式
作者:
jumky
時間:
2011-1-28 13:49
本帖最後由 jumky 於 2011-1-28 13:52 編輯
嗯...謝謝Hsieh大大....^^...那請問如果不用陣列公式用輔助欄...該如何用..??
作者:
Hsieh
時間:
2011-1-28 19:34
回復
17#
jumky
如附件
[attach]4567[/attach]
作者:
jumky
時間:
2011-1-28 20:23
謝謝Hsieh大大......^^
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
A,$A2)>0嗎?..可是它會出現"不能在格式化的準則中參照到其他工作表或活頁簿"的訊息?