こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

投稿日時 - 2013-01-21 21:01:04

QNo.7903563

すぐに回答ほしいです

質問者が選んだベストアンサー

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

投稿日時 - 2013-01-23 15:26:42

お礼

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

投稿日時 - 2013-01-23 21:40:00

ANo.5

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

>番号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行目までとなっていますが、数式セルが多いと再計算に時間がかかりシートの動きが重くなるので、必要最小限のデータ範囲にしてください。

投稿日時 - 2013-01-22 22:57:48

補足

おはようございます。
早々のお返事ありがとうございます。
取り急ぎ、
>実際のデータを見ないと正確な判断はできませんが、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」と表示されています。
会社でもう一度やってみて、夜にもう一度お返事させていただきたいと思います。

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

投稿日時 - 2013-01-23 07:16:49

お礼

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

投稿日時 - 2013-01-23 21:41:22

ANo.3

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

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

=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)))

投稿日時 - 2013-01-21 22:53:25

補足

ご教示ありがとうございます。
教えていただいたようにやってみました。
数点、教えていただきたいのですが、
番号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)))

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

投稿日時 - 2013-01-22 22:20:31

ANo.2

(2)の表の項目名が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」の表示をしないようにしてください(すべての0値を表示しないならExcelのオプションでゼロ値を表示しない設定にしてもOKです)。

投稿日時 - 2013-01-21 22:38:50

ANo.1

こんばんは!
一例です。

↓の画像のように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

投稿日時 - 2013-01-21 21:49:37

補足

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

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

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


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

投稿日時 - 2013-01-22 21:51:03

あなたにオススメの質問