エクセル・条件抽出

質問

エクセルで＜図１＞で各社が過半数を占めている地区を探し出し、
該当地区を各社の「優勢」の列に表示＜図２＞させたいと思います。
どのようにしたら良いでしょうか。ご教示の程、宜しくお願いします。

回答No.6

MackyNo1

>もし地区が４７都道府県、あるいは全市町村（１０００程度）でしたら、別の記述になりますでしょうか。

1000もデータがあるなら、Excel2007以降をご使用だと思うので（2003までは列数が足りませんので）、以下の式を入力して右方向および下方向にオートフィルすることになります（１つのセルに簡単に文字列結合する方法はありません）。

=INDEX($1:$1,SMALL(INDEX(($B2:$E2<50)*1000+COLUMN($B2:$E2),),COLUMN(A:A)))&""

上記の数式はE列までの数式ですので、実際は「$B2:$E2」の部分をデータ範囲の列までに変更してください。

また、配列数式ですので、列数が多くなると（数式入力セルが多くなる場合も含めて）、再計算に時間がかかりますので、実戦的には表示後は数式を値に変更しておくとよいと思います（一番下のセルだけ数式を残しておく）。

＃Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。

回答No.5

kagakusuki

作業列を使わずに全自動(Shift＋Ctrl＋Enterの操作も不要)で行う方法です。(地区の表示は、回答No.1様の場合と同様に、１セル毎に１つの地区を表示し、複数のセルを使う事で全ての「過半数を占めている地区」を表示します)

　今仮に、「シェア(%)」と入力されているセルがA1セルであるものとします。
　まず、F2セルに次の関数を入力して下さい。

=IF(COLUMNS($F:F)>COUNTIF($B2:$E2,">50"),"",INDEX($B$1:$E$1,MATCH(LARGE($B2:$E2,COLUMNS($F:F)),IF(ISNUMBER(1/(LARGE($B2:$E2,COLUMNS($F:F))=LARGE($B2:$E2,COLUMNS($F:F)-1))),INDEX($B2:$E2,MATCH(E2,$B$1:$E$1,0)+1):$E2,$B2:$E2),0)+IF(ISNUMBER(1/(LARGE($B2:$E2,COLUMNS($F:F))=LARGE($B2:$E2,COLUMNS($F:F)-1))),MATCH(E2,$B$1:$E$1,0),0)))

　次に、F2セルをコピーして、G3～I3の範囲に貼り付けて下さい。
　次に、F3～I3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　以上です。

　尚、もし、シェアの各数値を入力するセルの書式設定の表示形式が、「標準」や「数値」等ではなく、「パーセンテージ」となっている場合には、関数の冒頭部分の

=IF(COLUMNS($F:F)>COUNTIF($B2:$E2,">50")

という部分の中の

">50"

を

">0.5"

に変更して、

=IF(COLUMNS($F:F)>COUNTIF($B2:$E2,">0.5"),"",INDEX($B$1:$E$1,MATCH(LARGE($B2:$E2,COLUMNS($F:F)),IF(ISNUMBER(1/(LARGE($B2:$E2,COLUMNS($F:F))=LARGE($B2:$E2,COLUMNS($F:F)-1))),INDEX($B2:$E2,MATCH(E2,$B$1:$E$1,0)+1):$E2,$B2:$E2),0)+IF(ISNUMBER(1/(LARGE($B2:$E2,COLUMNS($F:F))=LARGE($B2:$E2,COLUMNS($F:F)-1))),MATCH(E2,$B$1:$E$1,0),0)))

として下さい。

回答No.4

bunjii

>どのようにしたら良いでしょうか。
>もし地区が４７都道府県、あるいは全市町村（１０００程度）でしたら
後出しの条件は揚げ足取りになりますのでご注意を！

作業用のテーブルを使うと良いでしょう。
Sheet2に作業用テーブルを用意すれば同じ配列で優位地区が選ばれます。
元のSheet（Sheet1）の右列に空欄を除いた文字列を表示すれば目的に合うと思います。
下記の式は地域名を1行目、会社名をA列として作成した状態とします。

各社の優位地域を抽出する式は以下の通りです。
=IF(MAX(Sheet1!B$2:B$4,Sheet1!B2)=Sheet1!B2,B$1,"")

Sheet2の右側に文字列の連結式を設けます。
=B2&" "&G2

Sheet1の右側に最終処理の文字列加工を行います。
=TRIM(Sheet2!F2)

最高シェア率が同じの場合は複数選択になります。

回答No.3

tom04

No.1です。

前回の数式の説明について・・・
=IF(COUNTIF($B2:$E2,">=0.5")<COLUMN(A1),"",INDEX($B$1:$E$1,,SMALL(IF($B2:$E2>=0.5,COLUMN($A$1:$D$1)),COLUMN(A1))))
の配列数式ですが

IF関数の中の
＞COUNTIF($B2:$E2,">=0.5")<COLUMN(A1)
は
B2～E2（列の絶対参照）の中で0.5以上の個数が
数式を入れた列からオートフィルでコピーした列数より少ない場合は
そのセルは空白に！というエラー処理です。
COLUMN(A1)=1　となりこれを列方向にコピーすると　COLUMN(B1)=2,→COLUMN(C1)=3・・・と数値が増えていきます。
（行番号はいくつでもよいのですが、入力しやすいため1としているだけです）
画像の2行目の場合「北海道」の1個だけですので、数式を入れた1列目のみがデータ表示で残りのセルは空白となります。

次にINDEX関数の説明です
＞INDEX($B$1:$E$1,,
はB1～E1セルの配列で
列番号のみの取得で
＞IF($B2:$E2>=0.5,COLUMN($A$1:$D$1)),
この部分が配列数式となります。
B2～E2セル内の0.5以上の列が「TRUE」それ以外が「FALSE」となり
それに列番号　COLUMN($A$1:$D$1)　をそれぞれの列に掛け算していますので
「TRUE」の列のみの列番号が取得できます。（1～4の数値）

この配列数式はSMALL関数の中の数式になりますので、
＞SMALL(IF($B2:$E2>=0.5,COLUMN($A$1:$D$1)),COLUMN(A1))
最後の COLUMN(A1)=1番目　となり、INDEX関数で範囲指定した中の0.5以上で一番小さい列番号を返す！
というコトになります。
これを列方向にコピーしていますので、
COLUMN(A1)　のA1部分が　→　B1（2番目）　→　C1（3番目）と変化して
0.5以上の個数分INDEX関数で範囲指定した中で
条件に一致するデータが小さい順（左側の列から）表示されるというコトです。

※　注意点　※
SMALL(IF($B2:$E2>=0.5,COLUMN($A$1:$D$1))
の中の　COLUMN($A$1:$D$1)　はA列から範囲指定した列数分だけにします。
いくらINDEX関数でB列～E列までを配列にしたからといっても
COLUMN($B$1:$E$1) としてはいけません、
そうしてしまうと返す値が1列ずれてしまいます。
もしINDEX関数の配列の列数が多い場合は
ここで調整します。

以上、長々と書きましたがご理解いただけたでしょうか？

回答No.2

MackyNo1

４地区程度しかないなら、以下のようなIF関数を連結するのが簡単です（F2セルに入力して下方向にオートフィル）。

=IF(B2>50,$B$1&" ","")&IF(C2>50,$C$1&" ","")&IF(D2>50,$D$1&" ","")&IF(E2>50,$E$1&" ","")

補足

ありがとうございます。もし地区が４７都道府県、あるいは全市町村（１０００程度）でしたら、別の記述になりますでしょうか。もしご存知でしたらご教示頂けると幸いです。

回答No.1

tom04

こんばんは！

↓の画像のように別セル表示で良いのであれば・・・
一例です。

画像ではF2セルに
=IF(COUNTIF($B2:$E2,">=0.5")<COLUMN(A1),"",INDEX($B$1:$E$1,,SMALL(IF($B2:$E2>=0.5,COLUMN($A$1:$D$1)),COLUMN(A1))))
これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　F2セルを選択　→　数式バー内に貼り付け　→そのまま（編集可能なまま）
→　Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列・行方向にオートフィルでコピー！
これで画像のような感じになります。

※　画像では％表示にしていますので、数値は小数点以下の数値になっています。m(_ _)m

補足

ありがとうございます。
(COUNTIF($B2:$E2,">=0.5")の部分が
B2からE2まで0.5（50%）以上のセルの個数を数える
記述だと分かりますが、それ以下はどのような
意味でしょうか。よろしければご教示頂けないでしょうか。