【エクセル】条件に合うものを別シートに抽出

質問

１、シート１（BC入力）で、ロット番号をバーコード読取りする。
２、そのロット番号に「検査」、「規制」のデータがあるかどうか（●は有）を、シート２～４から参照し、
　シート１に表示させる。

画像はシート２（生鮮食品）です。シート３、４も同様のつくりとなっています。
シート１はバーコードで読取ったロット番号、およびそれに対応した「検査」、「規制」を表示させるシートです。

【シート１略図】
　検査　　　ロット番号　　　規制
　●　　　　　　4A119　　　　　●
　　　　　　　　4B1018　　　　　●
　●　　　　　　4C267　　　　　●
　　　　　　　　　　　・
　　　　　　　　　　　・
　　　　　　　　　　　・

複数のシートにあるデータベースから、別の１つのシートに条件付きでデータを抽出する方法が
わからないでいます。
よろしければ皆様のお知恵を拝借させてください！！

回答No.3

bunjii · ベストアンサー

>条件についてですが、バーコード（ロット番号）がそれにあたります。
シート１、シート２、シート３、シート４はSheet1、Shee2、Sheet3、Sheet4としてExcel 2013で検証してみました。
Sheet1のB列にバーコードが読み込まれたとしてA列に検査のマーク、C列に規制マークをSheet2、Sheet3、Sheet4のマスターテーブルから抽出しました。
マスターテーブルには重複が無いものとします。
Sheet1!A4=IF(COUNTIF(Sheet2!$B$6:$J$10,B4),TRIM(INDEX(Sheet2!$A:$J,MOD(SUMPRODUCT((Sheet2!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet2!$C$6:$I$1005=B4)*ROW($C$6:$C$1005)),1000),INT(SUMPRODUCT((Sheet2!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet2!$C$6:$I$1005=B4)*ROW($C$6:$C$1005))/1000)-1)),IF(COUNTIF(Sheet3!$B$6:$J$10,B4),INDEX(Sheet3!$A:$J,MOD(SUMPRODUCT((Sheet3!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet3!$C$6:$I$1005=B4)*ROW($C$6:$C$1005)),1000),INT(SUMPRODUCT((Sheet3!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet3!$C$6:$I$1005=B4)*ROW($C$6:$C$1005))/1000)-1),IF(COUNTIF(Sheet4!$B$6:$J$10,B4),TRIM(INDEX(Sheet4!$A:$J,MOD(SUMPRODUCT((Sheet4!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet4!$C$6:$I$1005=B4)*ROW($C$6:$C$1005)),1000),INT(SUMPRODUCT((Sheet4!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet4!$C$6:$I$1005=B4)*ROW($C$6:$C$1005))/1000)-1)),"")))
Sheet1!C4=IF(COUNTIF(Sheet2!$B$6:$J$10,B4),TRIM(INDEX(Sheet2!$A:$J,MOD(SUMPRODUCT((Sheet2!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet2!$C$6:$I$1005=B4)*ROW($C$6:$C$1005)),1000),INT(SUMPRODUCT((Sheet2!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet2!$C$6:$I$1005=B4)*ROW($C$6:$C$1005))/1000)+1)),IF(COUNTIF(Sheet3!$B$6:$J$10,B4),TRIM(INDEX(Sheet3!$A:$J,MOD(SUMPRODUCT((Sheet3!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet3!$C$6:$I$1005=B4)*ROW($C$6:$C$1005)),1000),INT(SUMPRODUCT((Sheet3!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet3!$C$6:$I$1005=B4)*ROW($C$6:$C$1005))/1000)+1)),IF(COUNTIF(Sheet4!$B$6:$J$10,B4),TRIM(INDEX(Sheet4!$A:$J,MOD(SUMPRODUCT((Sheet4!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet4!$C$6:$I$1005=B4)*ROW($C$6:$C$1005)),1000),INT(SUMPRODUCT((Sheet4!$C$6:$I$1005=B4)*COLUMN($C$6:$I$1005)*1000+(Sheet4!$C$6:$I$1005=B4)*ROW($C$6:$C$1005))/1000)+1)),"")))
Sheet1!A4とC4を下へ必要数コピーします。
但し、マスターテーブルの最大行数は1005行（データ数1000）としました。
数式が長すぎて難解と思います。
出来ればVBAで処理された方が良いでしょう。
私はVBAが苦手のためコードを提示できません。
読みにくい思いますが参考のために画像を貼付します。

お礼

ご検討頂き、誠にありがとうございました。

試しにロット番号を入力してみたところ、しっかりと検査、規制の印が反応していました！
コードが難解すぎて私には解釈できなかったので、これから関数の学習やVBAを学んでいこうと思います。

回答No.2

bunjii

質問と回答No.1への補足で元になるデータの状況はおぼろげながら理解できますが、抽出したい条件の提示が見当たりません。

>(4)　ロット番号に「検査」もしくは「規制」が登録されていなかった場合、空欄になる（●が表示されない）のでどのロット番号に再登録が必要か判る。
「検査」または「規制」にマークが無いロット番号を抽出したいのであれば複数の関数を組み合わせて条件を満たすロット番号を抽出できます。
具体的には模擬データを提示して頂ければ数式を提示できると思います。
模擬データはカンマ（,）区切りのテキストデータを補足の中に文字列で貼り付けるとタイプ入力が省けますので検証の労力を減らせます。
質問に貼付の画像は拡大しても読めませんので参考になりません。
架空の条件で検証するのは余分な労力となりますので協力致しかねます。
本来は専門のシステム屋さんに有償で依頼すべき内容と考えます。

補足

回答、ありがとうございます。

条件についてですが、バーコード（ロット番号）がそれにあたります。
BC入力画面で、用紙からバーコードを読取っていき、そのバーコード（ロット番号）に対応したロット番号、および「検査」、「規制」をデータベースから抽出する形です。

各シートは下記の通りです。
よろしくお願い致します。

【シート１; BC入力画面】
検査,ロット No.,規制
,,
,,
●,4A119,●
●,4D001,●
●,4G33Y,

【シート2; データベース　生鮮食品】
品名,検査,生鮮食品,規制,検査,生鮮食品,規制,検査,生鮮食品,規制,,
食品名,,リンゴ,,,バナナ,,,みかん,,,
番地,,1,,,5,,,10,,,
,,,,,,,,,,,
,,,,,,,,,,,
,●,4A119,●,●,4B1016,●,●,4C266,●,,
,●,4A120,●,●,4B1017,●,●,4C267,●,,
,●,4A121,●,,4B1018,●,,4C268,●,,
,●,4A122,,●,4B1019,●,,4C269,●,,
,●,4A123,●,●,4B1020,●,,4C270,●,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,

【シート3; データベース　飲料】
品名,検査,飲料,規制,検査,飲料,規制,検査,飲料,規制
食品名,,お茶,,,オレンジジュース,,,ビール,
番地,,20,,,25,,,29,
,,,,,,,,,
,,,,,,,,,
,●,4D001,●,●,4E90Y,●,●,4F20Y,●
,●,4D002,●,●,4E91Y,●,●,4F21Y,●
,●,4D003,●,,4E92Y,●,,4F22Y,●
,●,4D004,,●,4E93Y,●,,4F23Y,●
,●,4D005,●,●,4E94Y,●,,4F24Y,●
,●,4D006,●,●,4E95Y,●,●,4F25Y,●
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,

【シート4; データベース　菓子類】
品名,検査,菓子類,規制,検査,菓子類,規制,検査,菓子類,規制
食品名,,ポテトチップス,,,ガム,,,アメ,
番地,,30,,,35,,,39,
,,,,,,,,,
,,,,,,,,,
,●,4G30Y,●,●,4H90Y,●,●,4I20Y,●
,●,4G31Y,●,●,4H91Y,●,●,4I21Y,●
,●,4G32Y,●,,4H92Y,●,,4I22Y,●
,●,4G33Y,,●,4H93Y,●,,4I23Y,●
,●,4G34Y,●,●,4H94Y,●,,4I24Y,●
,●,4G35Y,●,●,4H95Y,●,●,4I25Y,●
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,

回答No.1

keithin

んーと？
まず「シート２」とかはウソ（エクセル上の事実じゃない）で，「生鮮食品」とかが実際のシートの名前なんですね？

たとえばシート１のＢ２セルにあるロット番号が記入されるとして
シート１のＡ２セル（検査）には
=IF((B2<>"")*SUMPRODUCT((生鮮食品!$C$6:$I$100=B2)*(生鮮食品!$B$6:$H$100="●")+(シート３!$C$6:$I$100=B2)*(シート３!$B$6:$H$100="●")+(シート４!$C$6:$I$100=B2)*(シート４!$B$6:$H$100="●")),"●","")
シート１のＣ２セル（規制）には
=IF(B2="","",IF(SUMPRODUCT((生鮮食品!$C$6:$I$100=B2)*(生鮮食品!$D$6:$J$100="●")+(シート３!$C$6:$I$100=B2)*(シート３!$D$6:$J$100="●")+(シート４!$C$6:$I$100=B2)*(シート４!$D$6:$J$100="●")),"●",""))
などのように計算してみます。

＃この手のご相談では，簡略化した数式の回答では無駄に重くて役に立たないケースが非常に多いので，寄せられた回答をよく吟味してから実際に利用するように気をつけて下さい。

補足

すみません。
ファイル、使用状況についての説明が足りなかったです。

(1)　データ（ロット番号）の登録および「検査」「規制」の有無の登録はシート２～４にしていきます。
「生鮮食品」、「飲料」、「菓子類」と分けているのは登録の際に判りやすくするためです。

(2)　別部門からロット番号のバーコードのみが書かれた用紙が送られてきます。
　この用紙には品名、食品名、番地の記載はなく、ロット番号しか書かれていません。

(3)　シート１で、用紙のバーコード（ロット番号）をスキャンしていきます。
　用紙にバーコードが複数あるので、次々にスキャンしていきます。
　シート１にはロット番号と、それに対応する「検査」、「規制」の有無が表示されます（シート１略図）

(4)　ロット番号に「検査」もしくは「規制」が登録されていなかった場合、空欄になる（●が表示されない）　のでどのロット番号に再登録が必要か判る。

このような一連の動作を行いたいと考えています。
再度、皆様の知恵をお借りできないでしょうか。お願いします！