エクセルで番号から別のシートにデータ引用

質問

会計処理する上で下記のような方法が出来るかどうか教えて下さい。

「普通預金の明細記入」シートがあり通帳記帳と同じ内容を一覧で書いていきます。

項目には「日付」「摘要」「収入」「支出」「残高」があります。

そこで項目に「番号」を追加し、下記の通りに置き換えます。

会議費＝1
振込＝2
利息＝3

「会議費」「振込」「利息」シートを作成し、「普通預金の明細記入シート」
に記入した分はそれぞれのシートに追加されていくという方法は可能でしょうか？

わかりづらい説明で大変申し訳ありません。
現在の方法だと例えば「振込」で10,000円使用したとすると、

「普通預金の明細記入シート」と「振込シート」

２枚に記入しなくてはならないため間違いも起こりやすくなっています。
「普通預金の明細記入シート」に記入すれば自動的に「振込シート」に反映されるようにしたい
ということです。

よろしくお願いします。

回答No.2

mu2011 · ベストアンサー

一例です。
（１）仮に普通預金の明細記入シートをSheet1として番号列はＦ列、1000行としています。
（２）抽出シートのA2に以下の数式を入力、数式の「Ｘ」部分に各適用番号(1,2,3)を設定し、
　　　入力完了時にshift＋ctrl＋enterキーを同時押下、右方向にE列までコピー
=IF(COUNTIF(Sheet1!$F:$F,X)>=ROW(A1),INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$F$2:$F$1000=X,ROW(Sheet1!$F$2:$F$1000),9999),ROW(A1))),"")
（３）日付列はシリアル値が表示されると思いますので表示形式を日付に設定、
　　　A2:E2を選択（番号列は不要としています）して下方向に必要分コピー

お礼

わかりやすい方法ありがとうございます。
こちらも試してみましたが、回答１番の方にも書いたように新たな問題が発生しました・・・。

sheet1の「通帳明細」の他にもう一つ
sheet2の「現金明細」に記入した物も同じように

「会議費」等の各シートに日付順で記入されていく方法も可能でしょうか？
もし可能であれば是非ご教授いただけると幸いです。

よろしくお願いします。

補足

度々すみません。mu2011さんの回答が私には一番簡単だったので再度教えていただければ幸いです。
こちらに再度わかりやすく質問させていただきましたのでお時間ございますときにお願いできれば幸いです。
よろしくお願いいたします。

http://oshiete.goo.ne.jp/qa/6561668.html

回答No.6

KURUMITO

回答No1です。

=IF(COUNTIF(Sheet1!$G:$G,10000+ROW(A1))=0,"",INDEX(Sheet1!$A:$E,MATCH(10000+ROW(A1),Sheet1!$G:$G,0),COLUMN(A1)))

上の式は会議費の場合です。会議費は1の番号となっていますので10000を使っています。

振り込みは2の番号ですから上の式では10000を20000と変えて使用することで振り込みのデータが表示されます。
利息は3番ですから上の式では10000を30000と変えて使用します。

回答No.5

tom04

No.3・4です！
たびたびごめんなさい。

もう一か所Sheetごとで数値を変更しなければなりませんでした。
も一度数式を載せておきます。

仮に「会議費Sheet」の数式だと
=IF(COUNTIF(普通預金の明細記入!$F:$F,1)<ROW(A1),"",INDEX(普通預金の明細記入!A$1:A$1000,SMALL(IF(普通預金の明細記入!$F$1:$F$1000=1,ROW($A$1:$A$1000)),ROW(A1))))

となり、「利息Sheet」の数式は
=IF(COUNTIF(普通預金の明細記入!$F:$F,3)<ROW(A1),"",INDEX(普通預金の明細記入!A$1:A$1000,SMALL(IF(普通預金の明細記入!$F$1:$F$1000=3,ROW($A$1:$A$1000)),ROW(A1))))

となります。
微妙に「1・2・3」の数値が変わっているので確認してみてください。

何度も失礼しました。m(__)m

回答No.4

tom04

No.3です！
補足を読ませていただきました。

会議費・振込・利息　のSheetはそれぞれ準備してあるとして・・・

前回の数式をそのまま使えます。

前回の数式数式
=IF(COUNTIF(普通預金の明細記入!$F:$F,2)<ROW(A1),"",INDEX(普通預金の明細記入!A$1:A$1000,SMALL(IF(普通預金の明細記入!$F$1:$F$1000=2,ROW($A$1:$A$1000)),ROW(A1))))

の中の　・・・普通預金の明細記入!$F$1:$F$1000=2・・・部分で
「2」が「振込」ということでしたので、

会議費Sheetの数式は、「2」の部分を「1」に！
利息Sheetの数式は、「2」の部分を「3」にすればそのまま使えるはずです。

尚、前回同様配列数式になりますので、
Shift＋Ctrlキーを押しながらEnterキーで確定は忘れないでください。

お役に立てば良いのですが・・・m(__)m

回答No.3

tom04

こんばんは！
一例です。

↓の画像で右側が「振込」Sheetとしています。

「振込」SheetのA2セルに
=IF(COUNTIF(普通預金の明細記入!$F:$F,2)<ROW(A1),"",INDEX(普通預金の明細記入!A$1:A$1000,SMALL(IF(普通預金の明細記入!$F$1:$F$1000=2,ROW($A$1:$A$1000)),ROW(A1))))
これは配列数式になってしまいますので、この画面からコピー＆ペーストする場合はA2セルに貼り付け後数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
これをD2セルまでオートフィルでコピーします。
A2セルに表示形式は「日付」にしておきます。
A2～D2セルを範囲指定しD2セルのフィルハンドルで下へコピーすると画像のような感じになります。

尚、「普通預金の明細記入」Sheetのセルが空白の場合は「0」が表示されてしまいますので、
当方使用のExcel2003の場合は
メニュー　→　ツール　→　オプション　→　「表示」タブ　→　「ゼロ値」のチェックを外せば「0」は表示されません。

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

お礼

画像のシートまで付けていただきわかりやすい方法ありがとうございます！！！
こちらも試してみましたが、回答１番の方にも書いたように新たな問題が発生しました・・・。

sheet1の「通帳明細」の他にもう一つ
sheet2の「現金明細」に記入した物も同じように

「会議費」等の各シートに日付順で記入されていく方法も可能でしょうか？
もし可能であれば是非ご教授いただけると幸いです。

よろしくお願いします。

回答No.1

KURUMITO

関数を使って自動的に別のシートに表示させることができます。
便宜上「普通預金の明細記入シート」をSheet1、会議費のシートをSheet2とします。
そこでSheet1ではA1セルに日付、B1セルに摘要、C1セルに収入、D1セルに支出、E1セルに残高、F1セルに番号とそれぞれ項目名が有り、各データはそれぞれ下の行に入力されるとします。
そこで作業列としてG列を使い、G2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(F2<>"",F2*10000+COUNTIF(F$2:F2,F2),"")

そこでシート2ですが1行目にはA列からE列までシート1と同じ項目名を入力します。
A2セルには次の式を入力してE2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF(Sheet1!$G:$G,10000+ROW(A1))=0,"",INDEX(Sheet1!$A:$E,MATCH(10000+ROW(A1),Sheet1!$G:$G,0),COLUMN(A1)))

最後にA列を選択して表示形式を日付に変更します。

このようにしてシート3やシート4を作成すればよいでしょう。

お礼

わかりやすい方法ありがとうございます。
早速実行してみたのですが、新たな問題が発生しました・・・。

sheet1の「通帳明細」の他にもう一つ
sheet2の「現金明細」に記入した物も同じように

「会議費」等の各シートに日付順で記入されていく方法も可能でしょうか？
もし可能であれば是非ご教授いただけると幸いです。

よろしくお願いします。