複数シートからのデータ検索、コピー

質問

シートから条件に合致するデータを検索して、別ファイルの新しいシートにそれぞれ必要なデータ部分だけをコピーしたいのですが、検索するシートも複数で、また検索条件も複数のためか、過去の質問等を見ましても良く分かりませんでした。（マクロ等は余り使ったことが無い初心者です。）そこで、どなたかお詳しい方に教えて頂ければ、と思いまして投稿致しました。
シートは”貸借対照表”と”損益計算書”の２つあります。
抽出データは４行目からなのですが（A4から）、A列：コード（数字）、Ｂ列：勘定科目（文字）、C列：繰越額（数字）、Ｄ列：借方額（数字）、E列：貸方額（数字）、Ｆ列：残高（数字）、Ｇ列：比率（％）となっております。
各シートの構成として、A列は三桁または四桁の数字なのですが、（A4から）A列が三桁の数字の行を選んで、なおかつ、Ｄ列とE列が両方とも０で無い行のＢ列、Ｄ列、E列の値を二つのシートそれでれで検索し、別シートにコピーしたいのです。（どちらのシートからでも構いません。A列のコードの値はシート毎ではダブらないので、両方で抽出されたＢ、Ｄ、E列の値が列記できれば良いです。）
ただ、２つのシートの列数は決まっておらず、一方のシートの抽出データともう一方のシートの抽出データがダブってコピーされない様に、
したいな、とは思っております。

桁数での検索はLEN関数があるらしいのですが、複数のシートが対象となっており、マクロという機能を使った方が良いのでしょうか？

当方も初心者ゆえ、分かりやすく教えて頂ける方、よろしくお願い致します。

回答No.4

MackyNo1 · ベストアンサー

>このような、検索、書き出し等が学べるサイトがありましたら
教えてください。

一般的に、一定の条件の複数データを抽出するには、通常の関数ではなく配列数式を利用する必要があります。

配列数式については、以下のページが参考になると思います。

http://pc.nikkeibp.co.jp/pc21/special/hr/

さらに、今回の質問にある2つのシートのデータを1つのシートにまとめて表示させる場合には、さらに複雑な数式を利用することになりますが、これらは関数のいろいろな利用法を１つ１つ積み重ねて覚えていくしかありません。

そのような意味で、このようなQ&Aサイトのいろいろな回答者の回答を見て（自分でもチャレンジして）、実戦的な使い方を覚えていくのもよいのではないかと思います。

複数の該当データを表示するには、そもそも複雑な配列数式を使う必要があるので、今回のように2つのシートから抽出する条件がついたさらに複雑なケースでは、無理に1つの式にするのではなく、作業列を使って該当データを抽出しやすくする関数を入力して対応するなど、順次段階を踏んでレベルアップしていくのがよいと思います。

回答No.3

MackyNo1

ご希望の表示をするには以下のようなきわめて複雑な配列数式を利用する必要があります。

たとえば、Sheet1とSheet2の1行目が項目名で2行目から20行目までの範囲にデータが入力されているなら、以下の式をSheet3のA2セルに入力して下方向にオートフィルします。

=IF(SUMPRODUCT((LEN(Sheet1!$A$2:$A$20)=3)*(Sheet1!$D$2:$D$20&Sheet1!$E$2:$E$20<>"00"))<ROW(A1),INDEX(Sheet2!B:B,SMALL(INDEX(((LEN(Sheet2!$A$2:$A$20)=3)*(Sheet2!$D$2:$D$20&Sheet2!$E$2:$E$20="00")+(LEN(Sheet2!$A$2:$A$20)=4))*1000+ROW($A$2:$A$20),),ROW(A1)-SUMPRODUCT((LEN(Sheet1!$A$2:$A$20)=3)*(Sheet1!$D$2:$D$20&Sheet1!$E$2:$E$20<>"00"))))&"",INDEX(Sheet1!B:B,SMALL(INDEX(((LEN(Sheet1!$A$2:$A$20)=3)*(Sheet1!$D$2:$D$20&Sheet1!$E$2:$E$20="00")+(LEN(Sheet1!$A$2:$A$20)=4))*1000+ROW($A$2:$A$20),),ROW(A1)))&"")

B2セルには以下の式を入力して右方向に３つ、下方向に適当数オートフィルします。

=IF(A2="","",IF(SUMPRODUCT((LEN(Sheet1!$A$2:$A$20)=3)*(Sheet1!$D$2:$D$20&Sheet1!$E$2:$E$20<>"00"))<ROW(A1),VLOOKUP($A2,Sheet2!$B:$E,COLUMN(B1),0),VLOOKUP($A2,Sheet1!$B:$E,COLUMN(B1),0)))

ちなみに、上記の数式は無理やり1つのセルで計算するようにしましたが、「SUMPRODUCT((LEN(Sheet1!$A$2:$A$20)=3)*(Sheet1!$D$2:$D$20&Sheet1!$E$2:$E$20<>"00"))」の部分は、どこかのセルに計算しておき、このセルを参照するようにしたほうが何度も同じ計算をしないので計算効率がよくなります。

お礼

ありがとうございました。
このような検索、書き出し等が勉強できるサイト等がありましたら
教えてください。

よろしくお願い致します。

回答No.2

KURUMITO

例えばシート1にお示しのデータがあるとして、それと同じようなデータがシート2にもあるとします。
シート3ではお求めの表を作成するとします。
シート1には作業列としてＪ列を使用し、Ｊ4セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A4="","",IF(AND(LEN(A4)=3,OR(D4>0,E4>0)),MAX(J$3:J3)+1,""))
シート2でも作業列としてＪ列を使用するとしてＪ3セルには次の式を入力します。
=MAX(Sheet1!J:J)
Ｊ4セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A4="","",IF(AND(LEN(A4)=3,OR(D4>0,E4>0)),MAX(J$3:J3)+1,""))
まとめのシート3ではＡ3セルに勘定科目、Ｂ3セルに借方額、Ｃ3セルに貸方額と入力します。
Ａ4セルには次の式を入力し、右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(A$3="","",IF(COUNTIF(Sheet1!$J:$J,ROW(A1))>0,INDEX(Sheet1!$A:$H,MATCH(ROW(A1),Sheet1!$J:$J,0),MATCH(A$3,Sheet1!$3:$3,0)),IF(COUNTIF(Sheet2!$J:$J,ROW(A1))>0,INDEX(Sheet2!$A:$H,MATCH(ROW(A1),Sheet2!$J:$J,0),MATCH(A$3,Sheet2!$3:$3,0)),"")))

お礼

ありがとうございました。

このような、検索、書き出し等が学べるサイトがありましたら
教えてください。

回答No.1

MackyNo1

質問内容が今一つ不明確です。

＞（A4から）A列が三桁の数字の行を選んで、なおかつ、Ｄ列とE列が両方とも０で無い行のＢ列、Ｄ列、E列の値を二つのシートそれでれで検索し、別シートにコピーしたいのです。（どちらのシートからでも構いません。

確認ですが、D,E列には空白はなく、必ず数字が入力されるのですね。

また「どちらのシートからでも構いません」とはどのような意味でしょうか？

ちなみに、それぞれのシートの該当データは、配列数式を利用すればそれぞれ別に表示することができます。

＞A列のコードの値はシート毎ではダブらないので、両方で抽出されたＢ、Ｄ、E列の値が列記できれば良いです。）

確認ですが、コードと勘定科目は１：１に対応しているのでしょうか？

「シート毎にダブらない」と書いていますが、この条件があると数式で表示する際に何か関係あるのでしょうか？

＞ただ、２つのシートの列数は決まっておらず、一方のシートの抽出データともう一方のシートの抽出データがダブってコピーされない様にしたいなとは思っております。

２つのシートの列数が決まっていないと、どうして抽出データがダブってコピーされることになるのでしょうか？

単にB,C,D列の値を表示するだけではないのでしょうか？

回答者がわかりよいように、元データや実際にまとめたいリストのレイアウトなどを含めて、具体例をあげて補足説明してください。

補足

ご回答頂きましてありがとうございます。

＞確認ですが、D,E列には空白はなく、必ず数字が入力されるのですね。
　はい、数字が必ず入ります。０か任意の整数です。

＞また「どちらのシートからでも構いません」とはどのような意味でしょうか？
　検索の順番がどちらからでも良い、という意味です。

＞確認ですが、コードと勘定科目は１：１に対応しているのでしょうか？
　はい、対応しています。

＞「シート毎にダブらない」と書いていますが、この条件があると数式で表示する際に何か関係あるのでしょうか？
　＞ただ、２つのシートの列数は決まっておらず、一方のシートの抽出データともう一方のシートの抽出データがダブってコピーされない様にしたいなとは思っております。
　一つ目のシートから検索したデータを別シートにコピーした際に、これが例えば２０個あって２０行までコピーされたとした際に、次のシートからの検索データが２１行目からコピーされる、という意味です。

A　 　 B 　　C D E F G
100 　現金　　　2000 100 200 2100 0.25
9000 流動資産計 5000 200 200 5000 0.65
250 仮払金　　2000 0 0 2000 0.00
260 売掛金　　500 500 0 1000 0.05
　　　　　　　　　　　　・
　　　　　　　　　　　　・

具体的には、上記の様なレイアウトになっております。
会計ソフトからのデータをエクセルデータに変換した際に、
A列が３桁のデータで、なおかつ、Ｄ及びEが０ではない（上記で言えば
A列が100,260のデータのうち、Ｂ、Ｄ、Eの値を別シートにコピーしたい、ということです。

現金　　　2000 100 200
売掛金　　500 500 0
・
　　　　　 ・
・
次のシートに検索データ
　　　　　・
　　　　　・
　　　　　・

このような具体例でお分かりいただけたでしょうか？

お手数お掛け致しますが、よろしくお願い致します。