エクセル 家計簿より抽出

添付データの様に打ち込んだデータが年間あります。項目別、１月～１２月までの、明細（適用、金額）を、シート２に並べたいのですが・・・。フィルターを使えばすぐできますが、データを打ち込んだ時に、すぐにシート２に取りまとめられるという仕組みを作りたいのですが、関数が解りません。トータル金額は=SUMIFでできますが項目、適用、金額も転記したいのです。お力を・・・

１１：肥料　　　　　　　　　　　　　　　　　　２３：利子割引
　　月日　　項目名　　金額　　　　　　　　　　　月日　　項目名　　金額

　１月６日　ａ　　　　　１０２７５　　　　　　　　　１月６日　　　　　　　30000
　　　・　　　　・　　　　　　・
　　　・　　　　・　　　　　　・
　　　・　　　　・　　　　　　・
１２月３１日　・　　　　　　・

No.1 回答者： tom04 回答日時： 2011/03/01 21:30

こんばんは！

一例です。

↓の画像で右側がSheet2としています。
とりあえず項目「11」（肥料）の場合での数式です。
他の項目も同様の数式でOKですので、別列で試してみてください。

Sheet2のA3セルに
=IF(COUNTIF(Sheet1!$B:$B,$A$1)<ROW(A1),"",INDEX(Sheet1!$A$1:$E$1000,SMALL(IF(Sheet1!$B$1:$B$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1)),COLUMN(A1)*2-1))
これは配列数式になってしまいますので、この画面からコピー＆ペーストする場合は
A3セルに貼り付け後、数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
このA3セルを列方向と行方向にオートフィルでコピーすると画像のような感じになります。
（これ以上、データはない！という位まで下へコピーしておいても構いません）

尚、A列はシリアル値が表示されますので表示形式は日付にします。

以上、参考になれば良いのですが・・・m(__)m

この回答へのお礼

早速の回答ありがとうございます。
コピペで直ぐ使えるのは良かったのですが、シート２への行の追加が上手く行きませんでした。楽な設定だと思います！！

お礼日時：2011/03/02 20:45

No.2 回答者： imogasi 回答日時： 2011/03/01 22:55

エクセル関数は、「条件による行の抜き出し」は不得手です。

配列数式を使ったり（処理が重くなる恐れ有り）、複雑な式の組み合わせ（初心者中級者には式の意味の理解が難しい）を使ったりする。Googleでimogasi方式で照会すれば、その質問例や回答タイプが判る。
ーー
こういうのはＶＢＡで処理するのは相応しい。
ーー
imogasi方式は、作業列を余分に使いますが、式が比較的わかりやすいと思う。
以下は手抜きをして最小限の記述にしている。もし考えるなら類推して考えてください
例データ
Ｓｈｅｅｔ１
Ａ２：Ｇ１３　Ｆｒｅｔｕは空白。Ｇ列が作業列です。
日付　　　　コード　　　コード内容　コメント　　　係数　　　　　　　　　　　　上からのコードごと連番
2010/1/211ax11111
2010/1/411a23112
2010/1/512b42121
2010/1/613c12131
2010/1/723d14231
2010/1/611a25113
2010/1/713c6132
2010/1/814e7141
2010/1/811a18114
2010/2/311a33115　　＜－第１１行目
0
0
Ｇ２の式は　=B2&COUNTIF($B$1:B2,B2)
うえの行からコードごとに出現の（連続の）順番を振っている。
第１２行目以下も入力する前に、入力の見込みデータ数以上行に、式を複写しておく。０の表示が気になるならＩＦ関数をかぶせて空白にしておく。Ａ列が空白ならＧ列は空白とする式。
ーーー
Ｓｈｅｅｔ２で　Ｂ１に　１１＝肥料のコードを入れておく
Ａ２に　=INDEX(Sheet1!$A$1:$F$100,MATCH($B$1&(ROW()-1),Sheet1!$G$1:$G$100,0),COLUMN())
と入れて右方向にＦ列まで式を複写。
これはＳｈｅｅｔ１のデータ行数を１００行目までにしているが適宜増やしてください。
そのがＡ２：Ｆ２の式を下方向に式を複写

結果　Ｓｈｅｅｔ２　肥料シート
11
2010/1/211 ax11
2010/1/411 a023
2010/1/611 a025
2010/1/811 a018
2010/2/311 a033
#N/A#N/A#N/A#N/A#N/A
#N/A#N/A#N/A#N/A#N/A
この＃Ｎ／Ａが目障りならば、Ｓｈｅｅｔ１のコード１１の行数よりＳｈｅｅｔ２の行が下になったら、空白をセルにセットするＩＦ関数の式をかぶせる。
適当行数まで式を埋め込んでおく。
＝＝＝＝
肥料＝１１以外のコード分もシートを分けて、１１＝肥料と同じ式を埋め込む。Ｂ１は費目ごとに変わる。
ーー
すると、Ｓｈｅｅｔす１にデータを追加すると（本例では第１２行目以下）即座に関係のシートの今までのデータの直下にデータが追加される。

No.3 ベストアンサー 回答者： keithin 回答日時： 2011/03/01 23:35

方法１：簡単なVLOOKUP関数だけで解決する

準備：
Ａ列に検索番号列を追加する
A2:　=IF(C2="","",C2&TEXT(COUNTIF($C$2:C2,C2),"-00000"))
以下コピー

Ｂ列　日付
Ｃ列　項目番号
Ｄ列…以降続く


シート２
Ａ１セルに項目番号
Ａ２に「番号」と記入
Ａ３セルに
=IF(ROW(A1)>COUNTIF(Sheet1!C:C,$A$1),"",ROW(A1))
以下コピー
Ｂ２に「日付」と記入
Ｂ３に
=IF(A3="","",VLOOKUP($A$1&TEXT(A3,"-00000"),Sheet1!A:F,2,FALSE))
以下コピー
Ｃ２に「項目名」と記入
Ｃ３に
=IF(A3="","",VLOOKUP($A$1&TEXT(A3,"-00000"),Sheet1!A:F,4,FALSE))
以下コピー
以後同様。



方法２：関数すら使わないで解決する
準備：
シート１のＡ列に，単純に上から下に番号（連番）を記入する

Ａ列から表範囲を列選択
データメニュー（または挿入タブ）からピボットテーブルレポートの作成を開始
ページフィールドに項目番号を投入
行フィールドに番号，月日，項目名，適用を投入
データフィールド（Σフィールド）に金額を投入

「～小計」が出てしまった列は，上にある「項目名▼」をＷクリックして集計を無しに設定
「（空白）」が出てしまった行は，そのセルを一個スペースで書き換える
ページフィールドから希望の項目番号を選択して完成。

元のデータを追記したら，レポートを更新する。

この回答へのお礼

方法１を採用させて頂きました。
自分で、VLOOKUP関数でどうにか理解できる設定です。ひとつひとつ設定して間違いの無いように致します。有難うございました。

お礼日時：2011/03/02 20:51

No.4 回答者： KURUMITO 回答日時： 2011/03/02 07:45

分かり易く、データが多くなっても計算が重くならない方法です。

それには作業列を作って対応するのがよいでしょう。
例えばシート1に家計簿があり、1行目は項目名でE1セルが金額とすればF列を作業列としてE1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",B2*10000+COUNTIF(B$2:B2,B2))

シート2ではA1セルは項目の番号を入力するためのセルとします。
A2セルには月日、B2セルには項目名、C2セルには金額と入力します。
A3セルには次の式を入力してC3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF(Sheet1!$F:$F,$A$1*10000+ROW(A1))=0,"",INDEX(Sheet1!$A:$E,MATCH($A$1*10000+ROW(A1),Sheet1!$F:$F,0),IF(COLUMN(A1)=1,1,IF(COLUMN(A1)=2,3,5))))

このシートを全体を選択して他のシートに貼り付けることで項目番号ごとのデータをそれぞれのシートのA1セルにその番号を入力すことで表示させることができます。