Chiquilin Site■06.05.04_Excel：抽出データを詰めて表示する２
★その１：参照範囲を名前定義
例えば出納帳に入力した内容を各科目ごとにシートを分けるという場合 簡単なのは「フィルタで抽出して 可視セルをコピー＆該当シートに貼り付け」となります。自動化するなら
これをマクロか もしくはクエリで抽出する方法をお勧めします。最近の Excelでは FILTER関数が追加されているのでこの手の処理はもっと簡単になっています。
元データを 下方向に入力する場合 毎回 最終行の取得が必要になります。これは面倒なので名前の定義を使って 参照範囲を自動で取得できるようにします。下記は参考ファイルの場合です。
[挿入]→[名前]→[定義]
名＿＿前：番号
参照範囲：=仕訳帳!$B$3:INDEX(仕訳帳!$B:$B,COUNTA(仕訳帳!$B:$B))
→[追加]
名＿＿前：借方科目
参照範囲：=仕訳帳!$D$3:INDEX(仕訳帳!$D:$D,COUNTA(仕訳帳!$B:$B))
→[追加]
名＿＿前：貸方科目
参照範囲：=仕訳帳!$E$3:INDEX(仕訳帳!$E:$E,COUNTA(仕訳帳!$B:$B))
→[追加]
名＿＿前：仕訳範囲
参照範囲：=仕訳帳!$B$3:INDEX(仕訳帳!$G:$G,COUNTA(仕訳帳!$B:$B))
→[完了]
やっていることは全部同じです。この手のデータは 空白行を挟むことがありませんので ＣＯＵＮＴＡ関数でデータの個数を数えれば何行データがあるのかは簡単に分かります。
★その２：数式を名前定義
次にシート名を取得します。今回は CELL関数を使いますが CELL関数を使う時は 必ずファイルを保存してから行って下さい。一応 Ｂ１セルにも名前を付けておきます。
Ｂ１セル上で [挿入]→[名前]→[定義]
名＿＿前：科目名
参照範囲：
=REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1)),"")
→[完了]
名＿＿前：科目
参照範囲：=!$B$1
→[完了]
■各科目シートの Ｂ１セル
=科目名
シート名を抽出する場合 ＣＥＬＬ関数は 第二引数まで指定します。
この際 名前の定義では Ａシートで使う時には「Ａシート!A1」 Ｂシートで使う時には「Ｂシート!A1」としたい時に「!A1」とすることで 開いているシートに合わせて共通の名前を使うことができます。
★その３：シート名に応じた科目を抽出
最後に 該当する科目に応じた番号を抽出します。
配列数式になりますが 名前の定義を使うと「
Ctrl + Shift + Enter で確定」という作業が不要になります。今回 番号を数値にしておいたので 抽出は ＳＭＡＬＬ関数だけでできます。文字列の抽出については「
抽出データを詰めて表示する」を参照して下さい。
Ｂ３セル上で [挿入]→[名前]→[定義]
名＿＿前：抽出 (2006.05.18 修正)
参照範囲：=SMALL(IF((借方科目=科目)+(貸方科目=科目),番号),ROW()-2)
→[完了]
■各科目シートの Ｂ３セル
=IF(ISERR(抽出),"",抽出)
必要行まで下方向にオートフィルコピー
これで番号を抽出できます。「(借方科目=科目)+(貸方科目=科目)」の部分は 仕訳帳でない場合はひとつでいいです。番号さえ抽出できれば 後の項目は
VLOOKUP関数で抽出できます。
■各科目シートの Ｃ３セル
=IF(B3="","",VLOOKUP($B3,仕訳範囲,COLUMN()-1))
Ｇ列必要行までオートフィルコピー
以上です。
