[[20090304204742]] 『費目を抽出し表を作りたい』(たいちゃん) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『費目を抽出し表を作りたい』(たいちゃん)

sheet1から費目ごとに抽出しsheet2に作成したい

sheet1

     A    B   C                   D             E 
1   4/5  支出  会議費	   お菓子3/27		1,570 
2   4/5  支出  福利厚生費    御挨拶菓子		2,100 
3   4/5  支出  事務費	   ノート・ファイル	7,652 
3   4/8  支出  式典費	   花束3000×7        21,000 
4   4/16 支出  事務費         コピー機メンテナンス	5,791 
5   4/17 支出  事務費         ノート・ファイル	2,006 
6   4/23 支出  諸分担金      派遣費1件2名	           840
7   4/25 支出  事務費         クリアーホルダー	2,260 
8   5/1  支出  事務費          名札用の用紙		 500 
9   5/14 支出  式典費          記念品	        19,000 
10  5/15 支出  会議費      お菓子                 1,285 
11  6/5  支出  福利厚生費    親睦ボーリング大会    23,000 
12  6/9  支出  諸分担金      分担金	        13,200 

                      を 
sheet2
     A    B       C                     D                E
1   4/5  支出  事務費	   ノート・ファイル	7,652 
2   4/16 支出  事務費         コピー機メンテナンス	5,791 
3   4/17 支出  事務費         ノート・ファイル	2,006     
4   4/25 支出  事務費         クリアーホルダー	2,260 
5   5/1  支出  事務費          名札用の用紙		 500 
           合計                18,209
なかんじにしたいのですが・・・


 こんにちは。かみちゃん です。

 > なかんじにしたいのですが・・・

 オートフィルタはおわかりになりますでしょうか?

 (かみちゃん)
 2009-03-04 21:25


 ┌─┬───┬──┬─────┬────────┬───┐
 │  │   A  │  B │     C    │        D       │   E  │
 ├─┼───┼──┼─────┼────────┼───┤
 │ 1│  日付│収支│      費目│            内容│  金額│
 │ 2│4月5日│支出│    会議費│      お菓子3/27│ 1,570│
 │ 3│4月5日│支出│福利厚生費│      御挨拶菓子│ 2,100│
 │ 4│4月5日│支出│    事務費│ノート・ファイル│ 7,652│
 │ 5│4月8日│支出│    式典費│花束3000×7│21,000│
 └─┴───┴──┴─────┴────────┴───┘
 オートフィルターを選択するまえに、リストの1行目に1行挿入してフィールド名(項目名)を付けて下さい。
 リストの中の1セルのみアクティブセルにしてから、データ > フィルタ > オートフィルターを選択して下さい。
 オートフィルターを設定したら、C列の費目の右の▽をマウスで押し下げてリストから「事務費」を選択すると
 事務費のみが抽出されます。

ありがとうございます。オートフィルターにするとsheet1で行うことになりますよね。sheet1の項目はどんどん増えていくのでsheet2に、それにともない自動的に追加されるようにしたいのです。使ったお金を自動的に費目別に振り分けて表を作成できないでしょうか。

 品目分シートがあるとして、振り分けるだけなら
 この前作ったコードが
[[20081218145320]]『データを品名別に別シートに自動的に入力したい』(山田)

 合計も入れたいなら、データベースクエリ を
 使ってみるのが良いかもしれません。

 データベースクエリをWeb検索してもらうと
 色々なページが見つかると思いますが Googleで調べた際
 最初に出てきた二つのページを参考にリンクしておきます。

 よねさんのWordとExcelの小部屋 より「Excel(エクセル)基本講座:データベース クエリ(データ抽出)」
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter2.htm
 Excel豆知識 より「データを扱う時のいろいろな操作 2」
http://www11.plala.or.jp/koma_Excel/contents6/mame6041/mame604101.html

 最終行に合計を追加しておくと、
 更新をした際合計行までの間で追加・削除されます。

 (HANA)

 そう言えば、この前スレで紹介した先に
 項目を指定して(過去ログでは 日付でしたが)
 参照する数式も載せられていたと思いますが・・・。
 ご覧になられました?

 (HANA)

はい、数式をいれてみたんですが、"0"とでてしまって・・・。

なにせ、超初心者なので、数式をまる写ししかできなくて、

どこが間違っているかわからないんです。

(たいちゃん)


 >どこが間違っているかわからないんです。 

 設定した数式を提示されてみてはいかがでしょう。(gon-2)


教えていただいた数式は

=if(countif(sheet1!$A$1:$A$5,$A$1)<row(A1),"",index(sheet1!A$1:A$5,index(small((
sheet1!$A$1:$A$5<>$A$1*10^5+row($A$1:$A$5),row(A1)),)))


=if(coutif(sheet1!$A$1:$A$10,$A$1)<row(A1),"",index(sheet1!B$1:B$10,small(
if(sheet1!$A$1:$A$10=$A$1,row(A1))))

というものなんですが、どこにどのセルを入れればよいかよくわかりません・・・


 Sheet1
     A      B     C              D               E        F 
1   4/5    支出   会議費	     お菓子3/27		  1,570 
2   4/5    支出   福利厚生費   御挨拶菓子		  2,100 
3   4/5    支出   事務費	     ノート・ファイル	  7,652      3
4   4/8    支出   式典費	     花束3000×7         1,000 
5   4/16   支出   事務費        コピー機メンテナンス	  5,791      5
6   4/17   支出   事務費        ノート・ファイル	  2,006      6
7   4/23   支出   諸分担金     派遣費1件2名             840
8   4/25   支出   事務費        クリアーホルダー	  2,260      8
9   5/1    支出   事務費        名札用の用紙	            500      9
10  5/14   支出   式典費        記念品	          19,000 
11  5/15   支出   会議費    お菓子                  1,285 
12  6/5    支出   福利厚生費   親睦ボーリング大会     23,000 
13  6/9    支出   諸分担金     分担金	          13,200 

 F列を作業列として F1=IF(C1=Sheet2!A$1,ROW(A1),"")
 下方(13行目)へオートフィルします。
 C列の「事務費」と入力されている行(3,5,6,8,9)が表示されます。

 Sheet2 (合計金額はB1へ表示するのが簡単です) → B1=SUM(E:E)
     A       B       C                D                   E
1   事務費  18,209
2   4/5    支出   事務費	     ノート・ファイル	  7,652 
3   4/16   支出   事務費        コピー機メンテナンス	  5,791 
4   4/17   支出   事務費        ノート・ファイル	  2,006     
5   4/25   支出   事務費        クリアーホルダー	  2,260 
6   5/1    支出   事務費        名札用の用紙	            500 

 A1へ費目を入力します。

 A2=IF(COUNT(Sheet1!$F$1:$F$13)<ROW(A1),"",INDEX(Sheet1!A$1:A$13,SMALL(Sheet1!$F$1:$F$13,ROW(A1))))
 E2までオートフィルして、A2:E2をフィルダウン(6行目以降は空白が返ります)

 ※Sheet2のB1へ入力した費目がSheet1のC列何行目にあるかをF列に表示して、それらの行にあるデータをSheet2の2行目以降
   に表示します。
 ※以上を理解した上で配列数式にトライするのがよろしいと思います。

 上記の式を配列にしますと(F列の作業列不要)
 A2=IF(COUNTIF(Sheet1!$C$1:$C$13,$A$1)<ROW(A1),"",
    INDEX(Sheet1!A$1:A$13,SMALL(IF(Sheet1!$C$1:$C$13=$A$1,ROW($A$1:$A$13)),ROW(A1))))
 CtrlとShiftを押しながらEnterで配列を確定します。式が{ }で囲まれればOKです。
 E2までオートフィルして、A2:E2をフィルダウンします。

 別式
 A2=IF(COUNTIF(Sheet1!$C$1:$C$13,$A$1)<ROW(A1),"",
    INDEX(Sheet1!A$1:A$13,INDEX(SMALL((Sheet1!$C$1:$C$13<>$A$1)*10^5+ROW($A$1:$A$13),ROW(A1)),)))
 この式はEnterのみでOKです。
  
[[20090226111729]] ご覧になってください。(gon-2)


 一生懸命取り組んでだいぶ分かってきましたが、セルの位置が変わると
 混乱してしまいます。たとえば下のようになっていた場合

 A2=IF(COUNTIF(Sheet1!$C$1:$C$13,$A$1)<ROW(A1),"",INDEX(Sheet1!A$1:A$13,SMALL(IF(Sheet1!$C$1:$C$13=$A$1,ROW($A$1:$A$13)),ROW(A1))))
 はどのようにかわるでしょうか?

sheet1

      B    C   D                  E             F 
8    4/5  支出  会議費	   お菓子3/27		1,570 
9    4/5  支出  福利厚生費    御挨拶菓子		2,100 
10   4/5  支出  事務費	   ノート・ファイル	7,652 
11   4/8  支出  式典費	   花束3000×7        21,000 
12   4/16 支出  事務費         コピー機メンテナンス	5,791 
13   4/17 支出  事務費         ノート・ファイル	2,006 
14   4/23 支出  諸分担金      派遣費1件2名	  840
15   4/25 支出  事務費         クリアーホルダー	2,260 
16   5/1  支出  事務費          名札用の用紙		 500 
17   5/14 支出  式典費          記念品	        19,000 
18   5/15 支出  会議費      お菓子                1,285 
19   6/5  支出  福利厚生費    親睦ボーリング大会   23,000 
20   6/9  支出  諸分担金      分担金	        13,200 

sheet2

     F    G       H                  I                   J
3   4/5  支出  事務費	   ノート・ファイル	7,652 4 
4   4/16 支出  事務費         コピー機メンテナンス	5,791 
5   4/17 支出  事務費         ノート・ファイル	2,006     
6   4/25 支出  事務費         クリアーホルダー	2,260 
7   5/1  支出  事務費          名札用の用紙		 500 
           合計                18,209


 F3=IF(COUNTIF(Sheet1!$D$8:$D$20,"事務費")<ROW(A1),"",
        INDEX(Sheet1!B$1:B$20,SMALL(IF(Sheet1!$D$8:$D$20="事務費",ROW($A$8:$A$20)),ROW(A1))))

 【事務費】を抽出するのであれば、式に直接組み入れるか別のセルに入力しておく必要があります。
  A1に事務費と入力するのであれば、上記式の"事務費"を$A$1と入れ替えます。(gon-2)


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.