Excel技巧（行ごとに数値が上位項目を抽出）

質問

添付画像のような数値のみの表（上）があり、各行から数値が大きい上位３位までの数値とその項目を抽出して別表を作りたいです。
項目を抽出した結果イメージは添付画像の下の表です。
できれば、結果の表で、1つのセルに「項目：数値」のように両方表示できればいいですが、まずは数値、項目を別の表で作成できればと思います。

大量の行／列がある場合、関数を駆使して効率的に操作できないでしょうか。

回答No.4

bunjii · ベストアンサー

>できれば、結果の表で、1つのセルに「項目：数値」のように両方表示できればいいですが
1つのセルに文字列と数値を連結して表示するには数値を文字列として連結する必要があります。
従って、項目（文字列）を抽出する数式と数値を抽出する式を&演算子で連結すれば目的に合うでしょう。
項目と数値の間に:を含めるには固定の文字列として&演算子で連結します。

B9=INDEX($B$1:$H$1,1,MATCH(LARGE($B2:$H2,COLUMNS($B9:B9)),$B2:$H2,0))&":"&LARGE($B2:$H2,COLUMNS($B9:B9))
B9セルをD9セルまでオートフィルでコピーし、下へ必要数コピーすれば目的通りです。

貼付画像はExcel 2013で検証した結果です。
他のバージョンでも同様な結果になると思います。

お礼

ありがとうございます！

回答No.3

noname#204879

講釈は抜きにして、
B9: =INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2,COLUMN(A1)),$B2:$H2,0))

お礼

ありがとうございます！

回答No.2

minosennin

Large関数を使う一例です。

まず、9行目～13行目に数値を抽出
B9に　=LARGE($B2:$H2,COLUMN()-1)　と入力し、右へ2列、下へ4行ドラッグ

次に、16行目～20行目に項目を抽出
B16に　=INDEX($B$1:$H$1,MATCH(B9,$B2:$H2,0)) 　と入力し、右へ2列、下へ4行ドラッグ

最後に23行目～27行目に項目と数値を抽出
B23に　=B16&B9　と入力し、右へ2列、下へ4行ドラッグ

A列の各行には「あいうえお」の一文字を入力

お礼

ありがとうございます！

回答No.1

tom04

こんばんは！

＞大量の行／列がある場合・・・
とありますが、本当にかなりの量がある場合はVBAをおススメします。

ただ、今回は
＞数を駆使して効率的に操作できないでしょうか
というコトですので、一例です。
画像の配置通りだとするとB9セルに
=INDEX($B$1:$H$1,,MATCH(LARGE($B2:$H2,COLUMN(A1)),$B2:$H2,0))
という数式を入れ列・行方向にフィルハンドルでコピーしてみてください。

※　上記数式の場合、同順位がある場合は重複されて同じデータが表示されてしまいます。
おそらく同順位もあるかと思いますので、その場合はB9セルに
=INDEX($B$1:$H$1,,MATCH(LARGE($B2:$H2+1/COLUMN($B2:$H2),COLUMN(A1)),$B2:$H2+1/COLUMN($B2:$H2),0))
これは配列数式になりますので、Ctrl+Shift+Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　B9セルを選択　→　数式バー内に貼り付け　→　そのまま（編集かのうなまま）
Ctrl+Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
これを列・行方向にオートフィルでコピーしてみてください。

尚、最終的なご希望として
＞できれば、結果の表で、1つのセルに「項目：数値」のように両方表示できればいいですが
とありますので、↓の画像のような感じでやってみました。
（同順位があっても対応できるようにしています）

画像ではB9セル（配列数式です）に
=INDEX($B$1:$H$1,,MATCH(LARGE($B2:$H2+1/COLUMN($B2:$H2),COLUMN(B1)/2),$B2:$H2+1/COLUMN($B2:$H2),0))

C9セル（配列数式ではありません）に
=INDEX($B$2:$H$6,MATCH($A9,$A$2:$A$6,0),MATCH(B9,$B$1:$H$1,0))

としてB9・C9セルを範囲指定　→　C9セルのフィルハンドルで2列ずつ右へコピー　→　そのまま下へコピー！
これで画像のような感じになります。m(_ _)m

お礼

ありがとうございます！