エクセルについて質問させてください。 下記の表のようなことがしたいです。シート1にあるデータから、大

エクセルについて質問させてください。
下記の表のようなことがしたいです。シート1にあるデータから、大きな影響をもつ数字（数値の絶対値が大きい上位2位まで）とその項目の名前をシート2に表示したいです。

具体例（利益について）
シート１ のデータ。
製品A 30万円
製品B −150万円
製品C 120万円
製品D −10万円

シート2に抽出したいこと。
利益に大きな理由を与えている上位2位の製品名とその値を文書として一つのセルに自動入力したいです。
例
「製品B−150万円、製品C120万円」と自動で記載したい。

No.1 回答者： mike_g 回答日時： 2020/01/17 13:12

添付図参照

Sheet2!A1: ="製品"&INDEX(Sheet1!A:A,MATCH(MIN(Sheet1!B:B),Sheet1!B:B,0))&MIN(Sheet1!B:B)&"万円、製品"&INDEX(Sheet1!A:A,MATCH(MAX(Sheet1!B:B),Sheet1!B:B,0))&MAX(Sheet1!B:B)&"万円"

この回答へのお礼

ご回答ありがとうございます。入力してみたところ、最大値と最小値が抜粋される形になり、影響値の上位2位の抜粋とはならないようです。ただしご回答いただいた内容はとても参考になりました。
本当にありがとうございました、

お礼日時：2020/01/17 17:36

No.2 回答者： fujillin 回答日時： 2020/01/17 13:44

こんにちは

レイアウトがはっきりしないので、添付図の様に仮定しました。
A列に製品名、B列に利益データです。
ご質問文の雰囲気から、B列はどうやら文字列で入力されているみたいなので、負の符号が通常の「-」記号でなく「−」文字の可能性もあり、かつ、単位の「万円」も書式表示ではなく文字として入力されている可能性があるものと仮定しました。

このため、およそ表計算には向かない計算条件となってしまっているので、作成した式もこれに応じて無駄に長いものになっています。
下の式はひとまずA1:B10を計算対象としています。
結果を求めたいセル（添付図ではＤ1セル）に、

=INDEX(A:A,MOD(AGGREGATE(14,6,ABS(SUBSTITUTE(SUBSTITUTE(B$1:B$10,"万円",""),"−","")*1)+(ROW(B$1:B$10)/1000),1)*1000,1000))&INDEX(B:B,MOD(AGGREGATE(14,6,ABS(SUBSTITUTE(SUBSTITUTE(B$1:B$10,"万円",""),"−","")*1)+(ROW(B$1:B$10)/1000),1)*1000,1000))&"、"&INDEX(A:A,MOD(AGGREGATE(14,6,ABS(SUBSTITUTE(SUBSTITUTE(B$1:B$10,"万円",""),"−","")*1)+(ROW(B$1:B$10)/1000),2)*1000,1000))&INDEX(B:B,MOD(AGGREGATE(14,6,ABS(SUBSTITUTE(SUBSTITUTE(B$1:B$10,"万円",""),"−","")*1)+(ROW(B$1:B$10)/1000),2)*1000,1000))

という式を入力してあります。

※ 金額表示を単純な数値だけにできれば（単位等は書式で対応）、関数式は飛躍的に簡略化されたものにできるはずです。

この回答へのお礼

ご回答いただきありがとうございました。入力したところうまくいき、大変助かりました。もしよろしければ、金額を単純に数値だけにし、D1セルの−表示のみを▲にした式をご教示頂かないでしょうか。今回の場合だと、D1セルに「製品B ▲150、製品C120」
としたいです。よろしくお願い致します。

お礼日時：2020/01/17 17:32

No.3 回答者： fujillin 回答日時： 2020/01/18 17:49

No2です。

＞金額を単純に数値だけにし、D1セルの−表示のみを▲にした～
元データはNo1様が仮定している状態と考えて良いということと解釈しました。
現在、エクセル環境がないため（スマホからなので）、テストや確認ができませんが、ひとまず、修正方法の考え方のみの回答になってしまいますが、とりあえず記しておきます。

No2の式は、以下のような構成を4つ&で繋いで文字列を作成する式になっています。
それぞれが、最大値のA列（製品名）、B列（金額）、2番目のA列、B列を参照する式になっています。
INDEX(A:A,MOD(AGGREGATE(14,6,ABS(SUBSTITUTE(SUBSTITUTE(B$1:B$10,"万円",""),"−","")*1)+(ROW(B$1:B$10)/1000),1)*1000,1000))

ご質問の条件であるなら、このうち数字でない文字を除く処理が不要になるので、SUBSTITUTE関数を省くことができます。
INDEX(A:A,MOD(AGGREGATE(14,6,ABS(B$1:B$10)+(ROW(B$1:B$10)/1000),1)*1000,1000))
これをそのまま繋げれば、「製品B -150、製品C120」のような結果が得られるはずです。

これをさらに、ご質問のように負の数値部分を「▲」表示にするには、2番目、4番目の数値部分をText関数を利用して表示形式を変換してあげれば実現可能と考えます。
表示形式には、#;▲#;0 のようなものを設定しておけば良いでしょう。