Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
重複するセルに色をつける
投稿日時: 19/12/08 03:22:26
投稿者: カンナ

こんにちは。
 
重複セルを抽出する対象が、3列以上ある場合はどうすればいいのでしょうか。
 
     C I O
     A社    B社      C社
11    1 1 1
12 2 3 4
13 4 5 7
14 6 9 10
15 8 10
16 10
 
 
上のような表で3社に共通する 1と10に色をつけ、重複する数の合計(2個)を表示するように
したいのですが、うまくいきません。
カウントするために以下のような式を入れてみましたが、だめでした。
   ↓
COUNTIFS($I$11:$I$20,C11,$O$11:$O$20,C11)
 
どのようにすればいいのでしょうか
 
 
 

回答
投稿日時: 19/12/08 08:54:40
投稿者: Mike

  C  D  E F G H  I
10 A社 B社 C社  1 1
11  1  1  1  2
12  2  3  4  3
13  4  5  7  4
14  6  9 10  5
15  8 10    6
16 10      7
17        8
18        9
19       10 1
20         2 ←合計
 
[条件付き書式]設定の内容
ルールの適用先: =$D$12:$F$17
ルールの種類:“数式を…を決定”
ルールの内容↓
 ̄ ̄数式→ =COUNTIF($D$12:$F$17,D12)=3
 ̄ ̄書式→ お好みの「色をつけ」る
 
「重複する数の合計」(G、H列)
1.範囲 G10:G19 の上から数値 1、2、3、…、10 を入力
2.式 =IF(COUNTIF(C$11:E$16,G11)=3,1,"") を入力したセル H1 のフィルハンドルを「エイヤッ!」とダブルクリック
3.セル H20 に式 =SUM(H10:H19) を入力

回答
投稿日時: 19/12/08 08:55:25
投稿者: WinArrow
投稿者のウェブサイトに移動

課題を2つに分けましょう
 
(1)例示では[1]と[10]に色を付け
 どこのセルに色を付けるのですか?
 多分、条件付き書式を考えていると思います。
 例示の数式は、C列セルと思います。
 →C列セルに条件付き書式を設定できればよいのですか?
 
 
(2)重複する数の合計(2個)を表示するように
は、全く推測できません。
 

回答
投稿日時: 19/12/08 10:42:05
投稿者: 半平太

Countifsは、同じ行にあるもの同士のカウントです。
 
1.サンプルの各列データに重複がないですが(未入力の空白を除く)、
    必ずそうなっているんですか?
 
  重複がある場合、どんな数え方になるんですか?
  (例えば、C14セルも10だった場合)
 
2.データに、0 が含まれることがありますか?
    ※未入力セルと0の区別を考慮しないとならないので、お聞きしています。

回答
投稿日時: 19/12/08 18:49:18
投稿者: んなっと

C列,I列,O列と、飛び飛びなのが面倒ですね。
 
   C D E F G H  I J K L M N  O   P
10 A社         B社         C社 個数
11  1          1          1   2
12  2          3          4   
13  4          5          7   
14  6          9         10   
15  8         10             
16  10                       
 
C11
条件付書式:数式が▼
=COUNT(1/(COUNTIF(OFFSET($C$11:$C$20,,6*(COLUMN($A:$C)-1)),C11)=0))=0
 
ここからは縦方向には重複がないという前提で...
P11
=SUMPRODUCT(1*(FREQUENCY((C11:C20,I11:I20,O11:O20),(C11:C20,I11:I20,O11:O20))=3))
またはCtrlキーを併用しながらC11:C20,I11:I20,O11:O20を選択し、数式→名前定義でNumと名前を付けておいて
=SUMPRODUCT(1*(FREQUENCY(Num,Num)=AREAS(Num)))

投稿日時: 19/12/09 01:54:55
投稿者: カンナ

たくさんのご回答を頂き、ありがとうございました。
下手な図と説明で、ご迷惑をおかけしました。
 
Mikeさんの回答が私のイメージするものにぴったりでした。
 
ただ、他の作業の都合上対象となる列が飛び飛びになってしまうので、
 
=COUNTIF($C$11:$C$20,C11)+COUNTIF($I$11:$I$20,C11)+COUNTIFS($O$11:$O$20,C11)=3
3社共通の数字に色をつけ、
 
=IF(COUNTIF($C$11:$C$20,C11)+COUNTIF($I$11:$I$20,C11)+COUNTIF($O$11:$O$20,C11)=3,1,0)として
3社共通の数字の個数を集計することができました。
 
 
みなさまご回答ありがとうございました。