エクセルについての質問です。

質問

お世話になります。
エクセルで原価管理をしています。
一つのシートに
日付　　　　コード番号　　　支払先　　　品名　　　金額　　消費税
3/31 405 Ａ社　　　　　○×　　　200,00 10,000
3/31 308 Ｂ社　　　　　△◎　　　300,00 15,000

このような感じでデータを打ち込んでいます。
今やってる方法は、データを打ち込んだ後に、データの並び替えで
コード番号、日付の順で並び替えて、コード番号ごとに別のシートに
貼り付けて支出台帳を作成して集計しています。

このような場合、データを打ち込むシートに入力したら、別シートに
コード番号毎の行だけを返せる方法をご教示願います。

回答No.2

kagakusuki · ベストアンサー

今仮に、元データが入力されているシートがSheet1で、日付と入力されているセルがSheet1のA1セルであるものとします。

　まず、別シートを新たに作成し、そのシートのシート名を

405

や

308

といったコード番号と同じ名称として下さい。
　次に、別シートの
A1セルに　　コード番号
A3セルに　　日付
B3セルに　　支払先
C3セルに　　品名
D3セルに　　金額
E3セルに　　消費税
と入力して下さい。
　次に、別シートのB2セルに次の数式を入力して下さい。

=MID(CELL("filenane",B1),FIND("]",CELL("filenane",B1))+1,99)

　これで、B1セルにシート名（即ちコード番号と同じ文字列）が自動的に表示されます。（或いは、B1セルに、単にコード番号をキーボード入力するだけでも構いません）
　次に、別シートのA4セルに次の数式を入力して下さい。

=IF(OR(ROWS($3:3)>COUNTIF(Sheet1!$B:$B,$B$1),COUNTIF(Sheet1!$1:$1,A$3)=0),"",INDEX(OFFSET(Sheet1!$A:$A,,MATCH(A$3,Sheet1!$1:$1,0)-1),SUMPRODUCT(ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$A:$A)))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$A:$A))&""=$B$1&"")*(COUNTIF(OFFSET(Sheet1!$B$1,,,ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$A:$A)))-ROW(Sheet1!$B$1)+1),$B$1)=ROWS($3:3)))))

　次に、別シートのA4セルをコピーして、別シートのB4～E4の範囲に貼り付けて下さい。
　次に、別シートのA4セルの書式設定を[日付]として下さい。
　次に、別シートのA4～E4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。
　最後に、Sheet1の元データーを、データの並び替えで日付順に並べ替えて下さい。

　これで、コード番号毎に抽出した内容が表示されます。
　尚、別のコード番号の抽出結果を表示させるシートを作成する場合には、1枚目の別シートを複製したシートを作成し、そのシートのシート名を、新しいコード番号と同じ名前に書き換えるだけでOKです。

お礼

詳細な回答ありがとうございます。

「これで、B1セルにシート名（即ちコード番号と同じ文字列）が自動的に表示されます。」
やり方が悪いのか、この部分だけがうまくいきませんでした。

それ以外はうまくいきました。

どのように勉強したらあんな数式が作れるようになるのでしょうか。
感動しました。

ありがとうございました。

回答No.1

chupark

Excelのバージョン等がわかりませんが、
Excelのデータベース機能を使えばできるように思われます。

やり方をずばり解説されているサイトを見つけました。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter2.htm

各コード番号の分だけ作っておけば、各シート側では 右クリック→データの更新だけで、
行を持ってくることができるはずです。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter2.htm

お礼

早速の回答ありがとうございます。
うまくいきました。
勉強になりました。ありがとうございます。