エクセルの数式を教えて下さい

質問

エクセルでA1:A1000に顧客名がB1:B1000に金額が入っています。F1:F1000に金額の多い順に顧客名を並べます。金額は重複があり、その場合は上の行の顧客が上に来るようにします。作業列を使わない数式を教えて下さい。（例：A5"田中"B5"1000"、A10"鈴木"B10"1000"の時F3"田中"F4"鈴木"としたいのです。）
C列からE列にも他の項目があり、それらも金額の多い順や他の項目で、並べ替えようと思います。よろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

F1セルに次の数式を入力してから、F1セルをコピーして、F2以下に貼り付けると良いと思います。

=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX($A:$A,SUMPRODUCT(ROW($B$1:$B$1000)*($B$1:$B$1000=LARGE($B:$B,ROWS($1:1)))*(COUNTIF(OFFSET($B$1,,,ROW($B$1:$B$1000)),LARGE($B:$B,ROWS($1:1)))+RANK(LARGE($B:$B,ROWS($1:1)),$B:$B)-1=ROWS($1:1)))))

　尚、表の行数が増え続ける等の理由で、1000行以内に収まらなくなる可能性が高い場合には、数式中の

$B$1:$B$1000

の部分を全て

OFFSET($B$1,,,MATCH(99^9,$B:$B))

に置換して、

=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX($A:$A,SUMPRODUCT(ROW(OFFSET($B$1,,,MATCH(99^9,$B:$B)))*(OFFSET($B$1,,,MATCH(99^9,$B:$B))=LARGE($B:$B,ROWS($1:1)))*(COUNTIF(OFFSET($B$1,,,ROW(OFFSET($B$1,,,MATCH(99^9,$B:$B)))),LARGE($B:$B,ROWS($1:1)))+RANK(LARGE($B:$B,ROWS($1:1)),$B:$B)-1=ROWS($1:1)))))

という数式にすると、行数が増えても、数式を一々変更する必要が無くなります。

　因みに、金額の値自体を、多い順番に並べる数式は、以下の通りです。

=IF(ROWS($1:1)>COUNT($B:$B),"",LARGE($B:$B,ROWS($1:1)))

お礼

kagakusukiさん、ありがとうございました。うまくいきました。他のところでも使いたいと思います。本当にありがとうございました。

回答No.1

tom04

こんにちは！

＞作業列を使わない数式を教えて下さい・・・

とあるのですが作業列なしではかなり難しいと思います。
（できる方法があればごめんなさい。）

そこで一つの案でご希望の方法とは異なりますが、G列に金額の多い順に金額を表示させます。

G1セルは
=LARGE($B$1:$B$1000,ROW(A1))
F1セルは
=INDEX($A$1:$A$1000,SMALL(IF($B$1:$B$1000=G1,ROW($A$1:$A$1000)),COUNTIF($G$1:G1,G1)))
F1セルについては配列数式になってしまいますので、この画面からF1セルにコピー＆ペーストしただけではちゃんと表示されません。
貼り付け後、数式バー内で一度クリック、編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
（どちらのセルもエラー処理はしていません）

最後にF1・G1セルを範囲指定しG1セルのフィルハンドルで下へコピー！

これでF列に金額の多い順に名前が表示されます。

尚、このデータのままソートを行ってもF・G列は数式が入っているだけなので全く変化しません。
F・G列も他の列と同じように並び替えを行いたい場合は
F・G列（G列必要ないので削除しても構いません）を範囲指定　→　コピー　→　F1セルを選択　→　
右クリック　→　「形式を選択して貼り付け」を選択　→　「値」にチェック　→　OK

その後ソートを行えばF列も一緒にソートされます。

以上、長々と書きましたが的外れならごめんなさいね。m(__)m

お礼

tom04さん、ありがとうございました。kagakusukiさんの数式でできました。