条件に合うシートを串刺し集計して別のブックに抽出する方法

質問

ご覧いただきありがとうございます。「条件に合うシートを串刺し集計して別のブックに抽出する方法」を教えてください。

　毎月、１件の請求につき１枚のシート、計80枚程度のシートを含む請求○月.xlsというブックを作っています。
　現在は、その後の入金や未収の管理は、手作業で別ブック（入金○月.xls や 未収○月.xls）にシートを移すという非効率的な方法に頼っているのですが、この請求4月.xls や 請求5月.xls の各シートに「入金済み」のフラグを立てるなどして、これらのブックから、入金○月.xls が（半）自動的に生成されるようにしたいのです。
　言い換えれば、入金された月別に 請求○月.xls からデータを別ブックに抽出したいのです。

　具体的には、
　　請求4月.xls シート1、シート2、・・・、シート80、合計額シート（請求計）
　　請求5月.xls シート1、シート2、・・・、シート80、合計額シート（請求計）
　　　　　　　　（入金のあった分のシートを串刺し集計して抽出）
　　　　　　　　↓　　　　　　　　　↓
　　入金6月.xls 請求4月入金6月シート、請求5月入金6月シート

上記のように抽出したいと思っています。（串刺し集計のほか、シートを抽出する方法もお教えいただければ幸いです）

　なにとぞご指導よろしくお願いいたします。

回答No.2

miwaki · ベストアンサー

入金○月.xls の1行目のA列から順に各列に取り出したい項目名を並べます。
ただし、入金管理をするシートですから、入金日をトップ（セルA1)に持ってくるようにしましょう。そして、使用しない列（仮にK列としましょう）から次の通りの関数を入力してください。
L1:$E$2 （各シートのセルE2に入金日が入力されているとします）
M1～ｎ1:取り出したい各シートのデータの入っているセル名。上記のように＄マークをつけてください。8項目あったとして、n=T　とします。）
K2：[4月請求.xls]シート1!　（請求5月シートには[5月請求.xls]として以下同様に）
K3～K81:K2をコピーして張り付ける。
L2：=$K2&L$1
M2～T2:L2をコピーして張り付ける。
L3～T81；L2～T2をコピーして張り付ける。

次に、データを表示するA列から次の関数を入れてください
A2:=IF(MONTH(INDIRECT(L2))=ｍ,INDIRECT(L2),"")　（ｍは、シート毎の月の数とします）
B2:=IF(A2="","",INDIRECT(M2))
C2～J2：B2をコピーして張り付ける。
A3～J81：A2～J2をコピーして張り付ける。

関数の入力は以上ですが、これでは、未入金または他月入金の行が空白で混在しますので、入金日もしくは任意の項目でソートするなりフィルタするなりして利用してください。

補足

早速のご回答、ありがとうございます。仕事から帰ってきて、ファイルを作ってみました。その結果、いくつか（いくつも?）分からない点が出てきましたので、重ねてご指導お願いいたします。

１．A1セルはどのように使うものなのでしょうか。
２．式や関数を入力するのは、入金○月.xlsに作成した請求４月、請求５月といったシート上でよろしいでしょうか。
３．A2セル～J4セルには何も表示されず、A5～J81セルには #REF! と表示されましたが、どこを間違ってしまったのでしょうか（４月請求.xls には、B3～I7セルに数値を入れました）。
　「ｍは、シート毎の月の数とします」とのことでしたので、=IF(MONTH(INDIRECT(L2))=4,INDIRECT(L2),"") としたのですが、ここがおかしいのでしょうか。
４．=IF(MONTH(INDIRECT(L2))=ｍ,INDIRECT(L2),"") と B2:=IF(A2="","",INDIRECT(M2)) の式について、どういう働きをしているものかお教えいただけませんか。Webで INDIRECT関数の説明を調べてみましたが、よくわかりませんでした(^^;
５．最初の質問で抜けていたことですが、１枚ずつのシートには30セル×10セルのデータがあります。串刺し集計した抽出データも、30セル×10セルの姿にできるでしょうか。

　以上、分からないことだらけで申し訳ありませんが、なにとぞよろしくお願いいたします。

回答No.7

miwaki

お待たせしました。
＃５の行番で指定して並べ替える方法を発見しました。
C列から左のデータ部をすべて選択し、「データ」＞「並び替え」で並べ替えのプロンプトの左下の「オプション」をクリックしてください。
すると、中ほどに行単位か列単位か指定するチェックマークがあります。ここで列単位を選び、OKをクリック。すると並べ替えの指定キイ欄が列から行に変わります。そこで所望の行を指定して昇順を選び「OK」とすれば、空白列はすべて右端に移動します。

お礼

ありがとうございます(^^) 最初うまくいかなかったのですが、C列から「右」のデータ部を選択してご指導のとおりの操作をしてみましたところ、きれいにソートされました。
　教えていただいた方法で、未収管理をしていきたいと思います。これで、この業務についてはかなりの合理化が図れます。お礼の申し上げようもありません。親切におつきあいいただき、本当にありがとうございました。

回答No.6

miwaki

#5のお礼欄の質問にお答えします。
仰せの通り、未集金や他月集金の列は空白になります。
これらの列をソートかフィルタで対処することを考えていたのですが、ソートとかフィルタリングは、特定の行については行えますが、このように行と列が入れ替わった表で、列を指定しては行えませんね。今回実験してみて始めて知りました。私の勘違いでした。申し訳ありません。

それでは空白列を削除する別の方法を考えますので、ちょっとお時間をください。

回答No.5

miwaki

#4の回答に一部追加
セルC301をコピーしてシート枚数分右へ貼り付けた後、この301行目全体をコピーして、「形式を選択して貼り付け」で「値」を選択して、同じ行に貼り付けてください。これをしておかないとソーティングがうまくゆかないでしょう。

お礼

大変丁寧なご説明、ありがとうございます。昼休みにNo.3,4を読みながら、もう一度やってみました。見事にデータが抽出されました。魔法のようです。これで、印刷した一覧表に鉛筆で入金チェックを入れてそれを電卓で足し合わせるという、無益な作業から解放されます。(^o^)
　ところで、抽出されたデータは、B列に数値、C列は空白、D列にまた数値、というようなかたちになったのですが、これをソートあるいはフィルタリングするにはどのようにすればよいのでしょうか。何度も恐縮ですが、お教えいただければ幸いです。

回答No.4

miwaki

月別請求ブックのシートの中から取り出したい項目が３００もあるなら横並びは無理ですね。縦並びで行きましょう。
入金6月.xlsの請求4月入金6月シートを例にして説明します。
B列は取り出すデータの項目名とし、月別請求ブックのシートのデータ項目名を使いやすい順序で並べてください。仮にB1を「入金日」としB2以下他の項目名を並べてください。
A列にはそのデータのあるセル名を＄マークをつけて記入してください。仮に「入金日」のあるセルがE2ならA1に$E$2と入力してください。以下A2以降B列の項目名にあわせてセル名を続けて入れてください。
これが３００続くとして、使用しない301行目をINDIRECT関数に使う文字を次の通り入力します。
セルC301に　="[請求4月.xls]シート"&COLUMN()-2&"!"
このセルをコピーしてシート数分右に貼り付けてください。
次にデータを参照するセルの関数を次の通りいれます。
セルC1に　=IF(MONTH(INDIRECT(C$301&$A1))=6,INDIRECT(C$301&$A1),"")
セルC2に　IF(C1="","",INDIRECT(C$301&$A2))
セルC2をコピーしてセルC300まで貼り付けてください。
さらにセルC1からC300までをコピーして80シート分右の列に貼り付けてください。
これでひとつのシートの関数入力は終わりです。
入金7月.xlsの請求5月入金7月シートであれば、
セルC301は　="[請求5月.xls]シート"&COLUMN()-2&"!"
となり、セルC1は　=IF(MONTH(INDIRECT(C$301&$A1))=7,INDIRECT(C$301&$A1),"")となります。

こうして出た数値の表は、＃２回答の末尾で述べている通り、未入金や他月入金のシートを参照している列は空白で残ります。従ってB列で適宜ソートまたはフィルタして空白列を見えないところに追いやってご覧ください。

回答No.3

miwaki

当方のミス操作で＃１と＃２でダブってしまい申し訳ありません。＃１は無視してください。
さて、＃２の補足欄のご質問にお答えします。
１．入金○月.xlsの各シートの1行目には項目名の行で、仮にセルA1に「入金日」という名前にして、A列には入金日が当該月になっているデータだけを集めて表示します。
２．仰せの通りです。
３．私の回答の前半にありましたKから始まる作業列にはデータを入れていただいたでしょうか。このあたりのデータを使ってindirect関数を使っています。
４．セルA2の　=IF(MONTH(INDIRECT(L2))=ｍ,INDIRECT(L2),"") では、セルL2には上の３で述べているように、セルL2の関数が　=$K2&L$1　となっているので、[4月請求.xls]シート1!$E$2　という文字列になっているはずであり、4月請求.xlsのシート1のセルE2には入金日が入っていると仮定しています。その入金日から月を取り出して、シートの月数と比較しています。一致すればその日を取り出し、不一致なら空白を入れています。
セルB2の　=IF(A2="","",INDIRECT(M2))は、上記で月が不一致となってセルA2空白になっていれば空白、空白でなければ4月請求.xlsのシート1の該当セルの値を入れています。
５．1件の請求シートには３０＊１０の３００項目もあるんですか。それではL1から入力する項目セル記号はA1から入れることに変更しましょう。
入金○月.xlsの請求月別入金シートのフォームを請求月別シートと同じにすることはできません。なぜなら、８０枚のシートの入金月がたまたま同じになってしまった場合、入金○月.xlsの1枚の請求月別入金シートに80件も書き込まなくてはならなくなります。
従って、請求月別入金シートでは、請求月別ブックの1シート分が1行に入れるように作っています。

長くなりますので、5項のはじめに述べているフォームの変更に伴う関数の変更については、明日お伝えします。とりあえず、8項目として設計しました現状でお勉強してみてください。

回答No.1

miwaki

入金○月.xls の1行目のA列から順に各列に取り出したい項目名を並べます。
ただし、入金管理をするシートですから、入金日をトップ（セルA1)に持ってくるようにしましょう。そして、使用しない列（仮にK列としましょう）から次の通りの関数を入力してください。
L1:$E$2 （各シートのセルE2に入金日が入力されているとします）
M1～ｎ1:取り出したい各シートのデータの入っているセル名。上記のように＄マークをつけてください。8項目あったとして、n=T　とします。）
K2：[4月請求.xls]シート1!　（請求5月シートには[5月請求.xls]として以下同様に）
K3～K81:K2をコピーして張り付ける。
L2：=$K2&L$1
M2～T2:L2をコピーして張り付ける。
L3～T81；L2～T2をコピーして張り付ける。

次に、データを表示するA列から次の関数を入れてください
A2:=IF(MONTH(INDIRECT(L2))=ｍ,INDIRECT(L2),"")　（ｍは、シート毎の月の数とします）
B2:=IF(A2="","",INDIRECT(M2))
C2～J2：B2をコピーして張り付ける。
A3～J81：A2～J2をコピーして張り付ける。

関数の入力は以上ですが、これでは、未入金または他月入金の行が混在しますので、入金日もしくは任意の項目でソートするなりフィルタするなりして利用してください。