エクセル ピボット以外のやり方で抽出・合計

質問

以下のような表を作成しています。
(2)の表の番号の入っているものだけを、
番号・区分で抽出して(1)の表のように作成しています。

（(2)の表は57まであります。）

ピボットで(1)の表のように作成できますが、
処理数が大変多いので出来れば段階を踏まずに(1)の表を作成したいと思っています。
関数で出来ないかと思い色々と調べてはみたのですが、
行き詰っております。

Excel2007です。
ご教示よろしくお願いいたします。

回答No.5

tom04 · ベストアンサー

No.1です。
補足の件について・・・

（1）・（2）で、B列およびH列は文字列だということ。
（3）データ範囲が19行目～57行目（18行目は項目行）

ということですので、もう一度画像をアップしてみます。
前回同様↓の画像で左側がSheet1で右側のSheet2の2行目以降に表示するとします。

Sheet1の作業列1のJ19セルに
=IF(AND(B19<>"",H19<>"-"),B19&"_"&H19,"")

作業列2のK19セルに
=IF(AND(J19<>"",COUNTIF(J$19:J19,J19)=1),COUNTIF($J$19:$J$57,"<"&J19)+1,"")

という数式を入れ57行目までオートフィルでコピーしておきます。

Sheet2のA2セルに
=IF(COUNT(Sheet1!$K$19:$K$57)<ROW(A1),"",INDEX(Sheet1!$B$19:$I$57,MATCH(SMALL(Sheet1!$K$19:$K$57,ROW(A1)),Sheet1!$K$19:$K$57,0),MATCH(A$1,Sheet1!$B$18:$I$18,0)))

という数式をC2セルまで列方向にオートフィルでコピー！

D2セルを
=IF(A2="","",SUMIFS(Sheet1!$G$19:$G$57,Sheet1!$B$19:$B$57,A2,Sheet1!$H$19:$H$57,B2))
として
最後にA2～D2セルを範囲指定　→　D2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　画像を見てお判りかと思いますが、
A35・A62などはA144より下の行に表示されてしまいます。
これを回避したい場合は
A035とかA062のようにB列の桁数を揃えるなどの工夫が必要になります。

何とかご希望通りになればよいのですが・・・m(_ _)m

お礼

こんばんは！
ご教示本当に感謝です。
ありがとうございます。
思っていた通りのものが出来上がり、
1つのシートにもまとめることが出来ました。
他の業務にも応用が利きそうで、大変助かりました。
ありがとうございました。

回答No.4

MackyNo1

>番号J35がL列、N列に結果が表示されません。
また、L11に「08」だけ表示されています。
他は正しい結果が求められましたが、どのようにしたらいいでしょうか？

実際のデータを見ないと正確な判断はできませんが、K列およびL列の数式も変更したのでしょうか？

＞また、(2)の表をA18から(1)の表をA6に置きたいと考えてA7に以下のように入れてみましたが、＃N/Aになってしまいました。
教えていただいたように1行目でA2セル以下にデータが入力しないと正しい結果にはならないのでしょうか？

A7に入力する数式を以下のようにしてください(基本的にセル範囲だけを変更し、データが19行目からなら調整値の「-1」の部分を「-18」にしてください)。

=INDEX(B:B,SMALL(INDEX(((MATCH($B$19:$B$500&$H$19:$H$500,$B$19:$B$500&$H$19:$H$500,0)<>ROW($19:$500)-18)+($B$19:$B$500=""))*10000+ROW($19:$500),),ROW(1:1)))

＃上記の数式はデータ範囲が500行目までとなっていますが、数式セルが多いと再計算に時間がかかりシートの動きが重くなるので、必要最小限のデータ範囲にしてください。

お礼

こんばんは！
会社でもう一度やってみたのですが、同じでした。
今回は色々とお世話になりましてありがとうございました。
大変勉強になりました。

補足

おはようございます。
早々のお返事ありがとうございます。
取り急ぎ、
＞実際のデータを見ないと正確な判断はできませんが、K列およびL列の数式も変更したのでしょうか？
の件ですが、最初に教えていただいた位置での検証結果です。
以下の式を入れております。
L10
=INDEX(H:H,SMALL(INDEX((MATCH($B$2:$B$500&$H$2:$H$500,$B$2:$B$500&$H$2:$H$500,0)<>ROW($2:$500)-1)*10000+ROW($2:$500),),ROW(9:9)))
L11
=INDEX(H:H,SMALL(INDEX((MATCH($B$2:$B$500&$H$2:$H$500,$B$2:$B$500&$H$2:$H$500,0)<>ROW($2:$500)-1)*10000+ROW($2:$500),),ROW(10:10)))

L10でJ35の区分が表示されず、L11で区分「08」と表示されています。
会社でもう一度やってみて、夜にもう一度お返事させていただきたいと思います。

よろしくお願いいたします。

回答No.3

MackyNo1

質問内容をよく見たら「番号」のないデータは表示しないのですね。

その場合は、たとえば番号を表示するセルを以下のように変更してください(他の項目を表示するセルも同様に数式を変更してください）。

=INDEX(B:B,SMALL(INDEX(((MATCH($B$2:$B$500&$H$2:$H$500,$B$2:$B$500&$H$2:$H$500,0)<>ROW($2:$500)-1)+($B$2:$B$500=""))*10000+ROW($2:$500),),ROW(1:1)))

補足

ご教示ありがとうございます。
教えていただいたようにやってみました。
数点、教えていただきたいのですが、
番号J35がL列、N列に結果が表示されません。
また、L11に「08」だけ表示されています。
他は正しい結果が求められましたが、どのようにしたらいいでしょうか？
また、(2)の表をA18から(1)の表をA6に置きたいと考えてA7に以下のように入れてみましたが、＃N/Aになってしまいました。
教えていただいたように1行目でA2セル以下にデータが入力しないと正しい結果にはならないのでしょうか？
A7=INDEX(B$19:B$1048576,SMALL(INDEX(((MATCH($B$19:$B$500&$H$19:$H$500,$B$19:$B$500&$H$19:$H$500,0)<>ROW($19:$517)-1)+($B$19:$B$500=""))*10000+ROW($19:$517),),ROW(18:18)))

申し訳ございませんが、可能でしたら再度ご教示いただきたくお願いいたします。

回答No.2

MackyNo1

（２）の表の項目名が1行目でA2セル以下にデータが入力されているなら、例えば番号を表示するセル（K2セル）に以下の式を入力して下方向にオートフィルしてください。

=INDEX(B:B,SMALL(INDEX((MATCH($B$2:$B$500&$H$2:$H$500,$B$2:$B$500&$H$2:$H$500,0)<>ROW($2:$500)-1)*10000+ROW($2:$500),),ROW(1:1)))

同様に区分を表示するセル（L2セル）に以下の式を入力して右方向に1つ、下方向にオートフィルしてください。

=INDEX(H:H,SMALL(INDEX((MATCH($B$2:$B$500&$H$2:$H$500,$B$2:$B$500&$H$2:$H$500,0)<>ROW($2:$500)-1)*10000+ROW($2:$500),),ROW(1:1)))

集計する行（N2セル）には以下の式を入力して下方向にオートフィルしてください。

=SUMIFS($G$2:$G$500,$B$2:$B$500,K2,$H$2:$H$500,L2)

最後に数式範囲を選択して右クリック「セルの書式設定」の表示形式を「ユーザー定義」にして「G標準;;」などと設定して「０」の表示をしないようにしてください（すべての0値を表示しないならExcelのオプションでゼロ値を表示しない設定にしてもOKです）。

回答No.1

tom04

こんばんは！
一例です。

↓の画像のようにSheet1のデータをSheet2に表示するようにしてみました。
Sheet1に作業用の列を2列設けています。

作業列1のJ2セルに
=IF(COUNT(H2),B2&"_"&H2,"")
作業列2のK2セルに
=IF(AND(J2<>"",COUNTIF(J$2:J2,J2)=1),RANK(B2,B:B,1),"")
という数式を入れ、両列ともオートフィルでずぃ～～～！っと下へコピーしておきます。

そして、Sheet2のA2セルに
=IF(COUNT(Sheet1!$K:$K)<ROW(A1),"",INDEX(Sheet1!$B:$I,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0),MATCH(A$1,Sheet1!$B$1:$I$1,0)))
という数式を入れ、「科目」のC2セルまでオートフィルでコピー！
D2セルに
=IF(A2="","",SUMIF(Sheet1!J:J,A2&"_"&B2,Sheet1!G:G))
という数式を入れ、最後にA2～D2セルを範囲指定　→　D2セルのフィルハンドルで下へコピー！

これで画像のような感じになります。m(_ _)m

補足

ご教示ありがとうございます。
同じように作業してみました。
何点か後出しになってしまいますが追記させていただき、
お時間があれば再度教えていただきたくお願いいたします。
1.B列の番号にはアルファベットが付きます。
　特に問題ないかと思い「144」と表記してしまいましたが、「A144」などとなります。
A144とした場合、K列が表示されなくなってしまうのは、どのように回避すればいいでしょうか？

2.区分についてですが、文字列で「07」また「08’」という区分があります。文字列のままだとJ列が表示されず、数値にしなくてはならないようですが、文字列では出来ないでしょうか？

3.J列の式についてですがRANK(B2,B:B,1),"")の部分を本来置きたい位置（B19,B18:B57,1）に変えることは無理なのでしょうか？

知識不足の為、質問ばかりになってしまい申し訳ないのですが、再度教えていただきたくお願いいたします。