• ベストアンサー
※ ChatGPTを利用し、要約された質問です(原文:条件に合うシートを串刺し集計して別のブックに抽出する方法)

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

このQ&Aのポイント
  • 請求書のシートを条件に合わせて集計し、別のブックに抽出する方法を教えてください。
  • 毎月作成する請求書のシートから、条件に合うデータを抽出する方法について教えてください。
  • 現在手作業で管理している請求書のデータを効率的に抽出する方法を教えてください。

質問者が選んだベストアンサー

  • ベストアンサー
  • miwaki
  • ベストアンサー率36% (14/38)
回答No.2

入金○月.xls の1行目のA列から順に各列に取り出したい項目名を並べます。 ただし、入金管理をするシートですから、入金日をトップ(セルA1)に持ってくるようにしましょう。そして、使用しない列(仮にK列としましょう)から次の通りの関数を入力してください。 L1:$E$2 (各シートのセルE2に入金日が入力されているとします) M1~n1:取り出したい各シートのデータの入っているセル名。上記のように$マークをつけてください。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))=m,INDIRECT(L2),"") (mは、シート毎の月の数とします) B2:=IF(A2="","",INDIRECT(M2)) C2~J2:B2をコピーして張り付ける。 A3~J81:A2~J2をコピーして張り付ける。 関数の入力は以上ですが、これでは、未入金または他月入金の行が空白で混在しますので、入金日もしくは任意の項目でソートするなりフィルタするなりして利用してください。

akeem2003
質問者

補足

 早速のご回答、ありがとうございます。仕事から帰ってきて、ファイルを作ってみました。その結果、いくつか(いくつも?)分からない点が出てきましたので、重ねてご指導お願いいたします。 1.A1セルはどのように使うものなのでしょうか。 2.式や関数を入力するのは、入金○月.xlsに作成した請求4月、請求5月といったシート上でよろしいでしょうか。 3.A2セル~J4セルには何も表示されず、A5~J81セルには #REF! と表示されましたが、どこを間違ってしまったのでしょうか(4月請求.xls には、B3~I7セルに数値を入れました)。  「mは、シート毎の月の数とします」とのことでしたので、=IF(MONTH(INDIRECT(L2))=4,INDIRECT(L2),"") としたのですが、ここがおかしいのでしょうか。 4.=IF(MONTH(INDIRECT(L2))=m,INDIRECT(L2),"") と B2:=IF(A2="","",INDIRECT(M2)) の式について、どういう働きをしているものかお教えいただけませんか。Webで INDIRECT関数の説明を調べてみましたが、よくわかりませんでした(^^; 5.最初の質問で抜けていたことですが、1枚ずつのシートには30セル×10セルのデータがあります。串刺し集計した抽出データも、30セル×10セルの姿にできるでしょうか。  以上、分からないことだらけで申し訳ありませんが、なにとぞよろしくお願いいたします。

その他の回答 (6)

  • miwaki
  • ベストアンサー率36% (14/38)
回答No.7

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

akeem2003
質問者

お礼

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

  • miwaki
  • ベストアンサー率36% (14/38)
回答No.6

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

  • miwaki
  • ベストアンサー率36% (14/38)
回答No.5

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

akeem2003
質問者

お礼

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

  • miwaki
  • ベストアンサー率36% (14/38)
回答No.4

月別請求ブックのシートの中から取り出したい項目が300もあるなら横並びは無理ですね。縦並びで行きましょう。 入金6月.xlsの請求4月入金6月シートを例にして説明します。 B列は取り出すデータの項目名とし、月別請求ブックのシートのデータ項目名を使いやすい順序で並べてください。仮にB1を「入金日」としB2以下他の項目名を並べてください。 A列にはそのデータのあるセル名を$マークをつけて記入してください。仮に「入金日」のあるセルがE2ならA1に$E$2と入力してください。以下A2以降B列の項目名にあわせてセル名を続けて入れてください。 これが300続くとして、使用しない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),"")となります。 こうして出た数値の表は、#2回答の末尾で述べている通り、未入金や他月入金のシートを参照している列は空白で残ります。従ってB列で適宜ソートまたはフィルタして空白列を見えないところに追いやってご覧ください。

  • miwaki
  • ベストアンサー率36% (14/38)
回答No.3

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

  • miwaki
  • ベストアンサー率36% (14/38)
回答No.1

入金○月.xls の1行目のA列から順に各列に取り出したい項目名を並べます。 ただし、入金管理をするシートですから、入金日をトップ(セルA1)に持ってくるようにしましょう。そして、使用しない列(仮にK列としましょう)から次の通りの関数を入力してください。 L1:$E$2 (各シートのセルE2に入金日が入力されているとします) M1~n1:取り出したい各シートのデータの入っているセル名。上記のように$マークをつけてください。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))=m,INDIRECT(L2),"") (mは、シート毎の月の数とします) B2:=IF(A2="","",INDIRECT(M2)) C2~J2:B2をコピーして張り付ける。 A3~J81:A2~J2をコピーして張り付ける。 関数の入力は以上ですが、これでは、未入金または他月入金の行が混在しますので、入金日もしくは任意の項目でソートするなりフィルタするなりして利用してください。

関連するQ&A

専門家に質問してみよう