Excel2016 行間を詰めたい＆同じカテゴリなら上位２つだけを表示したい

お世話になります。

項目、数量、金額、カテゴリ　の入ったデータがあります。
これは他の場所からVLOOKUPで引っ張ってきたもので、順番もバラバラ。
何も入っていない行もあります。(0が入っています）

これを別の箇所に行間を詰めて表示したい。それから
カテゴリが同じものなら金額が上位２つの行だけを反映させたいです。
反映先の順番は特に気にしません。

ちなみに画像の真ん中はその後の計算に使うための表になっていますので
合計値は今回の質問とは関係ありません。

出来るだけマクロを使用せず、関数や設定だけで行いたいと思っています。
でも不可能ならマクロの使用も検討しています。

何か良い方法はないでしょうか？
よろしくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2022/06/03 14:23

こんにちは

詰めるだけなら、比較的容易にできますけれど・・
＞カテゴリが同じものなら金額が上位２つの行だけを反映させたいです。
というのが、関数で処理将とするとかなり面倒ですね。
マクロを用いれば、これもさほどではないのですが・・・

＞出来るだけマクロを使用せず、関数や設定だけで行いたいと思っています。
とのことですので、以下は、関数で作業列を用いる方法です。

作業列はどこでも良いので、空いている列を利用してください。
（邪魔にならない離れた列でも良いですし、非表示にしておくなどもありかと）
添付図ではとりあえずP列に設定してあります。

1）P2セルに、
=IF($A2=0,"",IFERROR((AGGREGATE(15,6,RANK($C$2:$C$20,$C$2:$C$20)/($D2=$D$2:$D$20),2)>=RANK($C2,$C$2:$C$20))*1,1))
の式を入力し、下方に十分にフィルコピーします。
これにより、抽出すべき行だけに1が表示されます。

2）L2セルに、
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$20)/($P$2:$P$20=1),ROW(A1))),"")
の式を入力し、L2:O2までフィルコピー
更に、L2:O2を下方にフィルコピーします。

以上で、ご提示の内容と表示順序は異なりますが、同じ内容になると思います。
（表示の順序は、元の表での出現順序になります。）

なお、同じカテゴリーで、同じ金額のものが多数存在する場合は、全て抽出されますのでご注意ください。
同金額が複数あっても、2つだけに絞り込みたい場合には、上記の1）の式を
=IF($A2=0,"",IFERROR((AGGREGATE(15,6,(RANK($C$2:$C$20,$C$2:$C$20)+ROW($A$2:$A$20)/100)/($D2=$D$2:$D$20),2)>=(RANK($C2,$C$2:$C$20)+ROW($A2)/100))*1,1))
に代えてください。
（同金額の場合でも、行番号の小さいデータを優先して、2つだけを抽出するようになります）

この回答へのお礼

うわーー！！すごい！！すごいです！！
私もちょっとはExcelが出来るつもりでいましたが、ほとんどが使ったことのないか名前も知らない関数のオンパレードです！
Excelの開発者の方ですか？(笑)
書いていただいた関数を入れたら本当に理想通りの表が出来ました！
本当にありがとうございます。これからひとつひとつ関数を紐解いて意味を理解していこうと思います。
ありがとうございました！！

お礼日時：2022/06/03 14:42

No.5 回答者： Belltreeriver 回答日時： 2022/06/04 19:57

№4です。

P2セル
一般数式に変更
SUMPRODUCT((D2=$D$2:$D$9)*(C2<$C$2:$C$9))+1<3

L2セル（２つ目のINDEXが余分でした）
=IFERROR(INDEX(A:A,SMALL(IF($P$2:$P$9*$B$2:$B$9<>0,ROW($A$2:$A$9)),ROW(A1)),0),"")

No.4 回答者： Belltreeriver 回答日時： 2022/06/04 18:35

№1さん同様、作業列（P列）を使用するパターンです。

大変ローテクで少々長いですが

P2セル
=SUM(IF((D2=$D$2:$D$9)*(C2<$C$2:$C$9),1,0))+1<3
配列(Ctr+Shift+Enter)数式　確定
P9までフィルコピー

L2セル
=IFERROR(INDEX(A:A,SMALL(INDEX(IF($P$2:$P$9*$B$2:$B$9<>0,ROW($A$2:$A$9)),0),ROW(A1)),0),"")
配列(Ctr+Shift+Enter)数式　確定

L2:O2までフィルコピー
更に、L2:O2を下方にフィルコピー

この回答へのお礼

ありがとうございます！！
NO.1さんとまた違ったやり方なのですね！
参考にさせていただきます。

お礼日時：2022/06/07 15:03

No.3 回答者： mike_g 回答日時： 2022/06/03 21:33

添付図参照(Excel 2019)

Sheet1 において、
１．配列(SCE)数式の
=IF(AND($D2=S$1,$C2>=LARGE(($D$2:$D$9=S$1)*$C$2:$C$9,2)),$C2,"")
￣￣を入力したセル S3 を右方４列＆下方７行オートフィル
２．式 =OFFSET(S$1,,ROW(A2)-1) を入力したセル Y1 を下方４行
￣￣オートフィル
３．配列(SCE)数式の
=SMALL(IF(INDIRECT($Y1)<>"",ROW(INDIRECT($Y1))),COLUMN(A1))
￣￣を入力したセル Z1 を右隣り＆下方４行オートフィル
Sheet2 において、
４．式 =OFFSET(Sheet1!$S$1,,(ROW(A1)-1)/2) を入力したセル A1 を
￣￣下方９行にオートフィル
￣￣式↓
=OFFSET(Sheet1!$Z$1,(ROW(Sheet1!A1)-1)/2,MOD(ROW(A1)-1,2))
￣￣を入力したセル C1 を下方９行にオートフィル
５．範囲 A1:C10 を選択⇒Ctrl+Cを実行⇒選択範囲上でマウスの右クリック
￣￣⇒[貼り付けのオプション]直下の[123](値)アイコンをツン⇒選択範囲範
￣￣囲上で、今度は Ctrl+G を実行⇒[セル選択]⇒“定数”に目玉入れ⇒下方に
￣￣ある“エラー値”以外に付いているチェック外し⇒[OK]⇒選択状態になっ
￣￣た #NUM!エラーの何れかのセル上でマウスの右クリック⇒[削除]⇒
￣￣“行全体”に目玉入れ⇒[OK]をパシーッ⇒その結果を緑枠の添付図
￣￣Sheet2 の右側に示した⇒その範囲 A1:C6 を選択⇒Ctrl+C を実行し後
￣￣で、…
Sheet1 に戻って、
６．セル O2 を選択⇒Ctrl+V をパシーッ⇒式 =INDEX(A:A,$Q2) を入力し
￣￣たセル L2 を右方２列＆下方５行オートフィル
オ・シ・マ・イ

この回答へのお礼

とても詳しくありがとうございます！！
ちょっと理解が追いつかず、まだ試していませんが、参考にさせていただきます！
ありがとうございました！

お礼日時：2022/06/07 15:01

No.2 回答者： d-q-t-p 回答日時： 2022/06/03 15:57

> 出来るだけマクロを使用せず

数式でやってもごちゃごちゃと意味の分からないものを並べる以外に
ないのですからマクロを避けるのが得策とは思えません。
かえって処理が重く複雑になるだけです。

というより数式かマクロかという二択がそもそもどうかと思います。
Excel2016なら PowerQueryが使えます。

左の表を見出し非表示のテーブル1として設定しておけばクエリは

行の抽出 = Table.SelectRows(テーブル1, each [Column4] <> null),
行のグループ化 = Table.Group(行の抽出, {"Column4"}, {"グループ", each Table.FirstN(Table.Sort(_,{"Column3",1}),2)}),
グループ列の展開 = Table.ExpandTableColumn(行のグループ化, "グループ", {"Column1", "Column2", "Column3"}),
列の並べ替え = Table.ReorderColumns(グループ列の展開,{"Column1", "Column2", "Column3", "Column4"})

これで済む話です。

この回答へのお礼

マクロをなるべく使用しないのは、私自身の知識が乏しくメンテナンスが容易ではないためです。
勉強不足は否めません汗
PowerQueryについても初めて聞きました。
詳しくありがとうございます！
勉強してみます！

お礼日時：2022/06/07 14:59