![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
ご覧いただきありがとうございます。「条件に合うシートを串刺し集計して別のブックに抽出する方法」を教えてください。
毎月、1件の請求につき1枚のシート、計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ベストアンサー
- 回答日時:
入金○月.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をコピーして張り付ける。
関数の入力は以上ですが、これでは、未入金または他月入金の行が空白で混在しますので、入金日もしくは任意の項目でソートするなりフィルタするなりして利用してください。
この回答への補足
早速のご回答、ありがとうございます。仕事から帰ってきて、ファイルを作ってみました。その結果、いくつか(いくつも?)分からない点が出てきましたので、重ねてご指導お願いいたします。
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セルの姿にできるでしょうか。
以上、分からないことだらけで申し訳ありませんが、なにとぞよろしくお願いいたします。
No.7
- 回答日時:
お待たせしました。
#5の行番で指定して並べ替える方法を発見しました。
C列から左のデータ部をすべて選択し、「データ」>「並び替え」で並べ替えのプロンプトの左下の「オプション」をクリックしてください。
すると、中ほどに行単位か列単位か指定するチェックマークがあります。ここで列単位を選び、OKをクリック。すると並べ替えの指定キイ欄が列から行に変わります。そこで所望の行を指定して昇順を選び「OK」とすれば、空白列はすべて右端に移動します。
ありがとうございます(^^) 最初うまくいかなかったのですが、C列から「右」のデータ部を選択してご指導のとおりの操作をしてみましたところ、きれいにソートされました。
教えていただいた方法で、未収管理をしていきたいと思います。これで、この業務についてはかなりの合理化が図れます。お礼の申し上げようもありません。親切におつきあいいただき、本当にありがとうございました。
No.6
- 回答日時:
#5のお礼欄の質問にお答えします。
仰せの通り、未集金や他月集金の列は空白になります。
これらの列をソートかフィルタで対処することを考えていたのですが、ソートとかフィルタリングは、特定の行については行えますが、このように行と列が入れ替わった表で、列を指定しては行えませんね。今回実験してみて始めて知りました。私の勘違いでした。申し訳ありません。
それでは空白列を削除する別の方法を考えますので、ちょっとお時間をください。
No.5
- 回答日時:
#4の回答に一部追加
セルC301をコピーしてシート枚数分右へ貼り付けた後、この301行目全体をコピーして、「形式を選択して貼り付け」で「値」を選択して、同じ行に貼り付けてください。これをしておかないとソーティングがうまくゆかないでしょう。
大変丁寧なご説明、ありがとうございます。昼休みにNo.3,4を読みながら、もう一度やってみました。見事にデータが抽出されました。魔法のようです。これで、印刷した一覧表に鉛筆で入金チェックを入れてそれを電卓で足し合わせるという、無益な作業から解放されます。(^o^)
ところで、抽出されたデータは、B列に数値、C列は空白、D列にまた数値、というようなかたちになったのですが、これをソートあるいはフィルタリングするにはどのようにすればよいのでしょうか。何度も恐縮ですが、お教えいただければ幸いです。
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列で適宜ソートまたはフィルタして空白列を見えないところに追いやってご覧ください。
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項目として設計しました現状でお勉強してみてください。
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が見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) vba 同じブック内での転記について 4 2023/01/15 14:42
- Excel(エクセル) フォルダ内の全ブックのシート名を変更したい 7 2022/09/22 21:34
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/06/01 14:45
- Visual Basic(VBA) エクセルのマクロについて教えてください。 5 2023/06/02 08:44
- Excel(エクセル) EXCEL マクロで 同じフォルダ内の複数ファイルの複数行全体を選択して1つのファイルに集約 4 2022/09/27 18:41
- Excel(エクセル) VBAで同フォルダ内の別ブックを開かず参照して条件の一致する行の指定セルを抽出するには? 1 2022/07/21 19:29
- Excel(エクセル) 前の(左隣の)シートを連続参照するように、あとから変更したい 1 2023/02/22 00:51
- Visual Basic(VBA) マクロVBA 1シートをまとめる 閉じ方 初心者 SOS! 1 2022/06/17 14:54
- Excel(エクセル) エクセルシートのデータを1列飛ばしで別ブックのシートに貼り付けるマクロが知りたい 2 2023/06/05 22:37
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel関数-文字列で自動作成さ...
-
エクセルの関数について教えて...
-
Excelデータをコピペして、ペー...
-
職場の人から聞かれており、こ...
-
ユーザー定義関数をアドイン登...
-
Excelで50個のセルに同じ文字を...
-
スプレッドシート、Excelでの数...
-
Microsoft Officeの中古は信用...
-
エクセルで不等号記号(≠)が上に...
-
スプレッドシートで使う数式を...
-
エクセルでの特別な文字を上に...
-
エクセル日付 文字列の関数がエ...
-
A列とB列を参照してC列に連番を...
-
エクセルVBA、別ブックへ転記す...
-
各ページの1番上の表示について
-
エクセルでセルに標準で入力さ...
-
EXCELの質問です 119から足した...
-
pdfの表をexcelにはりつけて計...
-
Excelのif関数で文字が見えなく...
-
【マクロ】アクティブセルにブ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報