Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
条件付き書式
投稿日時: 20/11/15 17:14:22
投稿者: はるん

フィルターでアイテムを絞った結果に、販売量1位と2位と3位と色付けをして目立たせたいです。
何度か試したのですがうまくいきません。エクセルの習熟度は中級者よりの初心者です。
どうぞ知恵を授けてください。
 

  A   B   C   D   E   F   
1 あ社  りんご  50
2 か梶@ みかん  90
3 さ商事 なし   80
4 あ社  ぶどう   5
5 あ社  バナナ  40
6 あ社  パイン  30
 
C列に条件付き書式を設定したいです。数字の大きい順に1位は青とか色付けして
3位まで設定したいです。この状態では、か社のみかんの90が青くなります。
A列のあ社でフィルターをかけると、見た目1位はりんごの50でありこの50が
青くなるような設定方法をさがしています。
よろしくお願いします。
※SUBTOTAL関数で「最大値」設定があったので1位はなんとか・・・。2位、3位が
 どうしたものか・・・という状態です。

回答
投稿日時: 20/11/15 18:24:59
投稿者: WinArrow
投稿者のウェブサイトに移動

OS,Exせlのバージョンを記載してください。
 
Excel2007以降でしたら
 
条件付き書式設定のルールの中に
上位10まで
というルールがあります。
 
上位1まで
上位2まで
上位3まで
の3つのルールを設定(書式:色を変えて)します。
優先順位を上からにすれば
できると思います。

回答
投稿日時: 20/11/15 20:12:09
投稿者: WinArrow
投稿者のウェブサイトに移動

>SUBTOTAL関数で「最大値」設定があったので
順位を求める関数は、SUBTOTAL関数ではありません。
RANK関数を使います。
 
条件付き書式の条件に「数式」を使う場合
 
=RANK(C1,$C$1:$C$10)=1
というように設定すれば、1位のセルに色が設定できます。

回答
投稿日時: 20/11/15 21:32:52
投稿者: 半平太

>例
>  A   B   C   D   E   F   
>1 あ社  りんご 50
> :
>A列のあ社でフィルターをかけると
 
1行目にタイトルがなくても、フィルターを掛けることが出来たのですか?

回答
投稿日時: 20/11/15 21:57:59
投稿者: WinArrow
投稿者のウェブサイトに移動

入力ミス訂正します。
 
>OS,Exせlのバージョンを記載してください。

OS,Excelのバージョンを記載してください。

回答
投稿日時: 20/11/16 08:59:01
投稿者: Suzu

作業列を使用しても良いでしょうか?
 
フィルター抽出後のデータを計算対象にするにはSUBTOTALを使用するのが簡単です。
ですが、残念ながら順位を求めるための引数はありません。
 
抽出後に集計 対象のデータ/非対象のデータ が判かれば良く
集計対象のデータ行のみにC列のデータを表示すれば、その列を元にRANK関数を用いれば・・
 
なので、
D1:=IF(SUBTOTAL(2,C1),C1,"")
E1:=RANK(D1,D:D)
 
後は、
C列に対し条件付書式
  数式を使用し〜〜
  数式:=E1=1
  適用範囲:=$C:$C
 
    1位は上の条件、2位/3位は、=E1=○ の○を書き換え 1位から3位分の条件付書式を設定
 
 
見出しが無くともフィルター設定は可能ですね。

回答
投稿日時: 20/11/16 10:29:54
投稿者: んなっと

もしかしたらこんな方法も。
 
    A    B   C
1  社名  品名  量
2  あ社 りんご  50
3  か梶@みかん  90
4 さ商事  なし  80
5  あ社 ぶどう   5
6  あ社 バナナ  30
7  あ社 パイン  30
 
C2:C列一番下 選択
条件付書式:数式を▼
=SUM(SUBTOTAL(3,INDIRECT("A"&ROW($2:$500)))*($C$2:$C$500>$C2))+1=1
塗りつぶし 青
 
2位以下は、 =1 の部分だけ =2, =3, ...に変更。

回答
投稿日時: 20/11/16 11:15:26
投稿者: んなっと

書き忘れましたが、一番上に必ず見出し(タイトル)を付けてください。
 
    A    B   C
1  社名  品名  量

投稿日時: 20/11/16 20:10:04
投稿者: はるん

んなっと さんの引用:
書き忘れましたが、一番上に必ず見出し(タイトル)を付けてください。
 
    A    B   C
1  社名  品名  量

 
ありがとうございます。うっかりしていました。
気をつけます。

投稿日時: 20/11/16 20:12:39
投稿者: はるん

んなっと さんの引用:
もしかしたらこんな方法も。
 
    A    B   C
1  社名  品名  量
2  あ社 りんご  50
3  か梶@みかん  90
4 さ商事  なし  80
5  あ社 ぶどう   5
6  あ社 バナナ  30
7  あ社 パイン  30
 
C2:C列一番下 選択
条件付書式:数式を▼
=SUM(SUBTOTAL(3,INDIRECT("A"&ROW($2:$500)))*($C$2:$C$500>$C2))+1=1
塗りつぶし 青
 
2位以下は、 =1 の部分だけ =2, =3, ...に変更。

 
 
試してみます。ありがとうございます。
 

投稿日時: 20/11/16 20:13:31
投稿者: はるん

Suzu さんの引用:
作業列を使用しても良いでしょうか?
 
フィルター抽出後のデータを計算対象にするにはSUBTOTALを使用するのが簡単です。
ですが、残念ながら順位を求めるための引数はありません。
 
抽出後に集計 対象のデータ/非対象のデータ が判かれば良く
集計対象のデータ行のみにC列のデータを表示すれば、その列を元にRANK関数を用いれば・・
 
なので、
D1:=IF(SUBTOTAL(2,C1),C1,"")
E1:=RANK(D1,D:D)
 
後は、
C列に対し条件付書式
  数式を使用し〜〜
  数式:=E1=1
  適用範囲:=$C:$C
 
    1位は上の条件、2位/3位は、=E1=○ の○を書き換え 1位から3位分の条件付書式を設定
 
 
見出しが無くともフィルター設定は可能ですね。

 
 
ありがとうございます。試してみます。

回答
投稿日時: 20/11/17 16:22:53
投稿者: WinArrow
投稿者のウェブサイトに移動

再掲
 
OS,Excelのバージョンを記載してください。
 
 
Excelのバージョンによって、対応が異なるので
敢えて、質問しています。
 
無視しないでください。
 
それから
これも再掲
一般的に、順位を求める関数は、RANK関数があります。
あまり難しく考えない方が、メンテしやすいと思います。

回答
投稿日時: 20/11/17 16:56:49
投稿者: んなっと

WinArrowさん、あなたが間違えていますよ。

回答
投稿日時: 20/11/18 09:11:41
投稿者: WinArrow
投稿者のウェブサイトに移動

んなっと さんの引用:
WinArrowさん、あなたが間違えていますよ。

 
んなっと さん、ご指摘ありがとうございます。
 
最初の質問の中の説明で
>オートフィルタ
の意味が理解できていませんでした。
要するに、「会社名」毎の順位を求めたいということですね?
読解力が不足していました。
 

回答
投稿日時: 20/11/23 17:43:22
投稿者: 抹茶味

解決してそうですが思いついたのでどうぞ。
んなっとさんのフォーマットを前提に、
=AGGREGATE(14,7,$C$2:$C$100,1)=$C2
              ^^^←ここを2,3に変更で順位が変えられます。
でも良いかと思います。

回答
投稿日時: 20/11/23 18:31:06
投稿者: んなっと

例えばこんな状況の場合...
AGGREGATE(14,7,$C$2:$C$100,2) も
AGGREGATE(14,7,$C$2:$C$100,3) も
同じ30を返してしまいます。
 
    A    B  C   D
1  社名  品名 量 順位
2  あ社 りんご 50   1
5  あ社 ぶどう  5   4
6  あ社 バナナ 30   2
7  あ社 パイン 30   2
 
条件付き書式の「適用順」次第で
2位の色になるか3位の色になるか変わってきます。
 
上の例の場合、2位の色より3位の色が優先されないように、上から順に
1位の条件
2位の条件
3位の条件
と並ぶようにすればOKですね。
 
 
 

回答
投稿日時: 20/11/23 18:49:59
投稿者: んなっと

=MATCH($C2,AGGREGATE(14,7,$C$2:$C$100,ROW($1:$3)),0)=1
=MATCH($C2,AGGREGATE(14,7,$C$2:$C$100,ROW($1:$3)),0)=2
=MATCH($C2,AGGREGATE(14,7,$C$2:$C$100,ROW($1:$3)),0)=3
ならうまくいくかもしれません。
あまり試していませんが。

回答
投稿日時: 20/11/23 19:06:23
投稿者: 抹茶味

引用:
条件付き書式の「適用順」次第で
2位の色になるか3位の色になるか変わってきます。

んなっとさん、補足ありがとうございます。
自分では当たり前に順番を設定するため抜けておりました…
 
matchの方の挙動が想像できないので後で試してみます。

回答
投稿日時: 20/11/23 21:53:59
投稿者: んなっと

※質問者の環境がExcel2010以降と仮定して話を進めています。

トピックに返信