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

標題: 設定格式化條件-多值 [打印本頁]

作者: LITTLEFAT7048 時間: 2015-11-18 20:14 標題: 設定格式化條件-多值

本帖最後由 LITTLEFAT7048 於 2015-11-18 20:16 編輯

各位前輩好，小弟又來請教問題了！

這次想請問的是設定格式化條件有辦法設定多值嗎？
（除了一個一個設之外，一個一個設定的小弟有成功）
因為看說明設定格式化的好像要OFFICE2010的才可以用到三個以上。

小弟有定義RNG1的儲存格範圍，

還有一個範圍是沒有設整儲存格範圍的，
但是需要用到裡面的值。

舉例來說：
A1:E3為RNG1

A4:E6為需要對照的值

而A4:E6為變值，就是從其他SHEETS裡參照過來的值，可自行輸入的值。
（不過都是整數，不會有文字或是小數之類的）

小弟需要的功能是：

RNG1裡面有一個的值「等於」A4:E6裡面值的時候，
其RNG1儲存格裡的那格就會變成紅字黃底之類的，其他RNG1範圍內的格式不會變

然A4:E6可能全部都輸入，或者只輸入一個，這個不一定，
端看要輸入者想要輸入多少個都行，但是就是不能超過A4:E6這些儲存格以外。

小弟目前的想法只有一格給一個代碼：
DIM I as Integer, J as Integer...........一直寫下去
I = RANGE("A4") J=RANGE("A5") ...........
IF RNG1=I Then .....
RNG1=J Then .....

之類的一直寫下去的。

請問有更好的方式可以提供的嗎？

作者: 准提部林 時間: 2015-11-18 22:21

用COUNTIF,MATCH, 純數值也可用RANK,
估計用一個格式化公式即可,
最好能上傳附件看看~~

作者: LITTLEFAT7048 時間: 2015-11-19 01:38

本帖最後由 LITTLEFAT7048 於 2015-11-19 01:40 編輯

回復 2# 准提部林

准大您好：
小弟需要的功能，
檔案裡面有範圍名稱RNG1,RNG2的在裡面了，

而小弟希望
RNG1（也就是所有「人1」）那ROW的值，
如果在B52:F54裡面的值有和人1那行有一樣的，就和9的顏色和底色一樣
（顏色和底色的修改比較簡單，那個小弟可以自行處理XD）

RNG2（也就是所有「人2」）那ROW的值，
如果在I52:N54裡面的值有和人2那行有一樣的，就和9的顏色和底色一樣

最後就是星期那行（RNG3），如果是六、日，改變顏色而已。
目前需要的是這三個的設定格式化條件。

因為像B52:F54總共有15格，所以不知道怎樣一次寫進去，
分次寫個15次這種簡單的小弟是會處理，但是希望可以多學一點！
謝謝指教！

底下是附件：
[attach]22489[/attach]

作者: 准提部林 時間: 2015-11-19 12:24

格式化條件公式：
Ｂ３：=RANK(B3,$B$52:$F$54)　＞　向右格式複製
Ｂ４：=RANK(B4,$J$52:$N$54)　＞　””
Ｂ５：=OR(B5="六",B5="日")　　＞　””

３．４．５列完成，再格式複製至其它位置

作者: LITTLEFAT7048 時間: 2015-11-20 04:34

回復 4# 准提部林

謝謝准大，成功了。

不過小弟查了一下rank函數的描述，目前想不出來為何要用rank函數去帶？(Orz....)

如果是多文字的條件的話，可能就不能用rank函數了？要用or是嗎？
因為小弟看rank說明他說非數值的會被忽略。

RANK 函數
本文將說明 Microsoft Excel 中 RANK 函數的公式語法及使用方式。

描述
傳回數字在一數列中的排名。數字排名是數字相對於數列中其他數值的大小 (如果您為數列排序，數字的排名就是其位置)。
重要  此函數已經由一個或多個新函數取代，新函數可能提供更佳的準確性，而且其名稱更能反映其用途。雖然基於回溯相容性還是有提供這些函數，但是您應該考慮從現在開始使用新函數，因為這些函數在將來的 Excel 版本中可能不會提供。
如需新函數的詳細資訊，請參閱 RANK.AVG 函數及 RANK.EQ 函數。

語法

RANK(number,ref,[order])

RANK 函數語法具有下列引數：

Number    必要。這是要找出其排名的數字。
Ref    必要。這是數列的陣列或參照。會忽略 ref 中的非數值。
Order    選用。這是指定排列數值方式的數字。
如果 order 為 0 (零) 或被省略，則 Microsoft Excel 把 ref 當成以遞減順序排序的數列來為 number 排名。
如果 order 不是 0，則 Microsoft Excel 會將 ref 當成以遞增順序排序的數列來來為 number 排名。

註解

RANK 會給重複的數字相同的排名。然而，重複的數字會影響後續數字的排名。例如，在一個以遞增順序排序的整數數列中，若數字 10 出現兩次，並且排名為 5，則 11 的排名將是 7 (如此就沒有排名 6 的數字)。
基於某些目的，應該採用將等數列入考量的排名定義。在上例中，應該把數字 10 的排名修正為 5.5。將下列修正因素加入 RANK 所傳回的值，即可達成此目的。此修正因素對於以遞減順序計算 (order = 0 或省略) 或以遞增順序計算 (order = 非零的值) 兩種案例都適用。
相同的修正因素 ranks=[COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref, 1)]/2。
在下列範例中，RANK(A2,A1:A5,1) 等於 3。修正因素為 (5 + 1 – 2 – 3)/2 = 0.5，而將等數列入考量的修正排名為 3 + 0.5 = 3.5。如果 number 在 ref 中只出現一次，則修正因素會是 0，因為 RANK 不需要針對等數做調整。

作者: 准提部林 時間: 2015-11-20 09:37

回復 5# LITTLEFAT7048

=OR(B3=$B$52:$F$54)　也是可用，但這是〔陣列〕公式；　或：=COUNTIF($B$52:$F$54,B3)
這兩者〔文字．數值〕都可以檢測，但若參照範圍大，公式格也多，運算時就較耗資源！

純數值時，改用 RANK，在大範圍時，速度快很多！

學公式就是要自己先去找函數測試！

作者: LITTLEFAT7048 時間: 2015-11-21 23:47

回復 6# 准提部林

每次看到有不知道的函數都會去查一下，
不過可能理解力不是很好，所以有些看不懂，
還請見諒！

再請教一個問題，
同一個檔案，
如果像人1和人2因為輪休的番號不一樣，
可以計算出2個人每個月共同放假的天數嗎？

目前有寫一個公式：

=COUNTIFS($B$3:$AF$3,RANK(M3,$B$52:$F$54),$B$4:$AF$4,RANK(M4,$J$52:$N$54))

這是因為設定的番號剛好M3，和M4都剛好放假，
所以選這格做基準，上述那行答案會出現1。

但是M3沒有辦法用範圍的方式（例：B3:AF3，只能用一格一格輸入）
所以變成要拉一整個row才會出現有共同放假的"1"，然後再去加總"1"，
結果雖然是要的，不過請問是否有別的方式？
謝謝准大撥冗指教！

作者: 准提部林 時間: 2015-11-22 10:27

本帖最後由准提部林於 2015-11-22 10:34 編輯

回復 7# LITTLEFAT7048

每月兩個人〔同一天同時放假〕天數
陣列公式：
=SUM(COUNTIF(B52:F54,B3:AF3)*COUNTIF(J52:N54,B4:AF4))
=COUNT(RANK(B3:AF3,B52:F54)*RANK(B4:AF4,J52:N54))

一般公式：
=SUMPRODUCT(COUNTIF(B52:F54,B3:AF3)*COUNTIF(J52:N54,B4:AF4))
=SUMPRODUCT(1-ISNA(RANK(B3:AF3,B52:F54)*RANK(B4:AF4,J52:N54)))

＜陣列公式＞輸入法
輸入或修改公式完成後，游標留在〔編輯列〕，先不要按Enter，
同時按住〔Shift + Ctrl〕不放，再按〔Enter〕。

若對RANK用法還不熟悉，建議先做參考，COUNTIF較好理解～～

另：
１．B52 公式須改為：=IF(輸入區!B4="","",輸入區!B4)，其它格亦然，否則〔空格及０值〕會誤判為相同！
２．輸入區號碼不可重覆

作者: LITTLEFAT7048 時間: 2015-11-22 22:21

本帖最後由 LITTLEFAT7048 於 2015-11-22 22:23 編輯

回復 8# 准提部林

謝謝准大，目前是成功了，

這個公式看得懂：（比較簡單!!，這兩個都成功）
=SUMPRODUCT(COUNTIF($B$56:$F$58,B7:AF7)*COUNTIF($J$56:$N$58,B8:AF8))
{=SUM(COUNTIF($B$56:$F$58,B3:AF3)*COUNTIF($J$56:$N$58,B4:AF4))}

不過還有一個小問題，
小弟已經把B56:F58的輸入了IF的那個公式，（本來B52的往下移，因為空出來的要留給計算的答案）
這個部份沒問題，
但是二月就有問題了，
因為二月只有28天，或者是說只要是不是31天的計算都會有點問題，
要把沒有31天的儲存格內容全部刪除就會成功了。

上述二月的計算，直接從一月往下拉的，
這個答案算出來會是401，如果刪掉29日儲存格內容，會變成269，
再刪30日得137，再刪31日得5，（等於刪一日就會減132）
5是正解。因為這個月剛好計算的是5天。

接著小弟就把底下的IF的去掉，直接是參照的（=輸入區!B4）
然後就成功了，問題出在哪裡目前不確定，不過答案是正確的。
但是RANK函數的那2個好像有點錯誤，
沒關係，那個有點難，慢點再研究好了，先研究會的...
報告完畢！以上是小弟測試的結果！

謝謝准大指導！

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