設定格式化條件-多值

帖子: 2798
主題: 10
精華: 0
積分: 2854
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-4-20

2^#

發表於 2015-11-18 22:21 | 只看該作者

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

帖子: 17
主題: 4
精華: 0
積分: 55
點名: 0
作業系統: [略]
軟體版本: [略]
閱讀權限: 20
性別: 男
註冊時間: 2015-3-22
最後登錄: 2022-8-17

3^#

發表於 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次這種簡單的小弟是會處理，但是希望可以多學一點！
謝謝指教！

底下是附件：

輪休表-智慧版(完成版)-雙人版-1.zip (35.21 KB)

帖子: 2798
主題: 10
精華: 0
積分: 2854
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-4-20

4^#

發表於 2015-11-19 12:24 | 只看該作者

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

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

帖子: 17
主題: 4
精華: 0
積分: 55
點名: 0
作業系統: [略]
軟體版本: [略]
閱讀權限: 20
性別: 男
註冊時間: 2015-3-22
最後登錄: 2022-8-17

5^#

發表於 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 不需要針對等數做調整。

帖子: 2798
主題: 10
精華: 0
積分: 2854
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-4-20

6^#

發表於 2015-11-20 09:37 | 只看該作者

回復 5# LITTLEFAT7048

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

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

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

帖子: 17
主題: 4
精華: 0
積分: 55
點名: 0
作業系統: [略]
軟體版本: [略]
閱讀權限: 20
性別: 男
註冊時間: 2015-3-22
最後登錄: 2022-8-17

7^#

發表於 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"，
結果雖然是要的，不過請問是否有別的方式？
謝謝准大撥冗指教！

帖子: 2798
主題: 10
精華: 0
積分: 2854
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-4-20

8^#

發表於 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)，其它格亦然，否則〔空格及０值〕會誤判為相同！
２．輸入區號碼不可重覆

帖子: 17
主題: 4
精華: 0
積分: 55
點名: 0
作業系統: [略]
軟體版本: [略]
閱讀權限: 20
性別: 男
註冊時間: 2015-3-22
最後登錄: 2022-8-17

9^#

發表於 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個好像有點錯誤，
沒關係，那個有點難，慢點再研究好了，先研究會的...
報告完畢！以上是小弟測試的結果！

謝謝准大指導！