EXCEL 文字列条件抽出

質問

特定の文字列を検索条件として別シートに抽出する方法を御教授願います。
使用する関数はLEN,MID,LEFT,ISERR,もしくはワイルドカードなどを使用すればいいのかと考えておるのですがいまいちやりたいような数式を組めず悩んでおります。

やりたいことを言葉で表現するならば以下のような感じとなります。

「sheet1のF列に<１０７>という文字が含まれていた場合」
・顧客別シートのD列に顧客ごとに分けて抽出
・含まれていない場合、抽出しない

と、単純なことなのです。が、制約がある為にうまくいきません。

制約は以下となります。
・検索対象の文字列が以下のように決まった形ではない。
(1)A-BB-107○#-----
(2)A-BB-107○#-----
(3)ABB107○C #-----
(4)ュウリABB-107○#-----
(5)ュウリABB107○#-----
(6)BB-107○C-D9#-----　
など、その他にも数種類存在。（○部分には違う数字が入ります）

・上記でも分かるように<107>が始まる文字列の位置が決まっていない。

という具合です。

操作しているブック構成は下記のとおりです。
・sheet1・・・参照用シート

4行目 /D列・・・顧客No / E列・・・顧客名 / C列・・・製品仕様 (A列,B列,G～AD列まで使用中）
5行目 / 1 / A / A-BB-107○#-----
6行目 / 2 / B / ュウリABB-107○#-----
7行目 / 1 / A / BB-107○C-D9#-----
8行目 / 3 / C / A-BB-107○#-----
9行目 / 3 / C / BB-107○C-D9#-----　
10行目 / 4 / D / A-BB-107○#-----

（会社のサーバーで管理している情報がそのままエクセルに打ち出されたシート。
情報量として約1000行ほど）

・sheet2,3,4,5（開始行,項目列は同じ位置）・・・特定の顧客別ごとに分け、特定の製品仕様だけを抽出したシート（参照用から引用）。

4行目/ B列・・・顧客No / C列・・・顧客名 / D列・・・製品仕様
5行目 / / A-BB-107○#-----
6行目 / / ュウリABB-107○#-----
7行目 1 / A / BB-1234C-D9#-----
8行目 / / A-BB-1115#-----
9行目 / / BB-107○C-D9#-----　
10行目 / / A-BB-3498○#-----

特定の顧客NoをB列(一つのセルになってます。C列も同様一つのセルになってます）に入力すると、製品仕様すべてを抽出するように作成したシート。このシート内のG列から新たに表を作成し<107>の文字列が含む製品仕様を抽出しようと考えております。

出来れば、このような一度段階を踏んでから特定の仕様を抽出するのではなく各シートに対象となる顧客Noを入力すると特定の仕様が一回で抽出できるようなシートを作成したいのですが、関数初心者の為、うまく組めずこのような形となりました。（現在、参照用シートに作業列を作り対応。作業列ばかりが増えて正直困っているのも確かなのです）

【御教授頂きたいこと】
・<107>の文字列を条件として別表に抽出する関数の数式
です。

また、出来ればでいいのですが、
・顧客Noを入力すると特定の仕様が一回で別表に抽出できるような方法が御座いましたら、そちらも合わせてお願いしたいと思っております。

長々と書き綴り、大変申し訳御座いませんが、宜しくお願い致します。

回答No.7

kagakusuki

>=Z列・・・顧客No. AA列・・・顧客名 AB列・・・総台数 AC列・・・合計総額

>上記、4列が保障されており、同じデータが存在していないことが保障される列且つ何らかのデータは入力されている列となります。

　済みません、実際のデータがどの様になっているのかは判りませんが、重複していない事が保証されているという事は、Z列やAA列には、1行ごとに異なる顧客No.や顧客名が入力されているという意味になりますし、AB列やAC列の値も必ず1行ごとに異なる値となっていて、総台数や合計総額の数値が偶然にも同じ値となる事はり得ないという意味になりますが、その様に考えて宜しいのでしょうか？
　それに、

>総台数及び合計総額に於いてのデータは対象となるデータが無い場合、必ず『0』が入力されています。

という事は、AB列とAC列の値は全て同じデータであり、全て重複したデータのみであるという事になりますから、仰る事が矛盾しているのではないでしょうか？
　それと、それらのZ列～AC列の値は、抽出結果を表示する表に表示するデータなのでしょうか？

>=下記でお答えしたとおり、『はい』で問題御座いません。

　前にお答え頂いた内容は、

>=はい。必ず上記3点のどれかは必ずあります。

というものなのですから、前回の内容の通りですと、『#』、か『. 』か『 』の何れかが、SERIAL.Noの直前の部分以外の箇所にも表れているという意味になってしまい、「はい」というお答えとは矛盾してしまいますので、「『#』、か『. 』か『 』の何れかが、SERIAL.Noの直前の部分以外の箇所にも表れる事があるのかないのか」という事を確認しなければ、どの様な状況となっているのか判断する事が出来ない状況となりましたので、お尋ねした訳です。
　そして、今回御補足頂いた内容から考えますと、「はい」の方が正しく、「必ず上記3点のどれかは必ずあります。」の方は誤っている事になりますから、「下記でお答えしたとおり」という事にはならない事を御断り致しておきます。

>『. 』や『 』を『#』に置換することはなんら問題となりません。

　それでしたら、Excelの置換機能を使用して、『. 』や『 』の全てを『#』に置換してしまった後、『.#』や『 .』、『 #』等の「仕様の部分とSERIAL.Noの部分を区切っている記号」が重複している部分を置換する事によって生じた『##』を全て『#』に置換してしまい、元データの形式を統一してしまわれる事をお勧め致します。
　その方がデータ処理を行うための関数が簡単になるという事もありますが、何より、データの形式が統一されていないのは、非常に見苦しい事であるからです。

>因みに、◆←これってどういう意味ですか？

　質問者様が考えられたSheet6のJ列の関数は、「Sheet1のD列が、Sheet6のH5の値と等しく、尚且つ、Sheet1のE列が、Sheet6のI5の値と等しいという条件を満たしている行のSheet1のG列の数値を合計する」という関数となっています。
　ここで、作業列を設けておき、作業列のデータが、例えばSheet1のD列とSheet1のE列という2つの列のデータを同時に併せ持つデータとなる様にしますと、作業列のデータを基準として、SUMIF関数を使って「Sheet1のD列が、Sheet6のH5の値と等しく、尚且つ、Sheet1のE列が、Sheet6のI5の値と等しい」という条件を満たしている行のみのSheet1のG列の数値の合計を求める事が出来ます。
　そのために作業列の関数は、Sheet1のD列の値を表している文字列の後に、Sheet1のE列の値を表している文字列を繋げた文字列を、作業列に表示する様な関数としている訳です。
　その際、単純に

Sheet1!$D:$D,ROW())&INDEX(Sheet1!$E:$E,ROW())

等としてしまいますと、例えば万が一、Sheet1のD列の値が「123」で、E列の値が「456」となっている行と、Sheet1のD列の値が「12」で、E列の値が「3456」となっている行があった場合、作業列上に表示される文字列データは、どちらも「123456」という同じ値となってしまい、D列が「123」の場合のデータなのか、それともD列の値が「12」場合のデータなのか、区別する事が出来なくなってしまいます。
　「123456」の場合に限らず、その様な区別する事の出来ない組合せが他にも出来てしまう恐れがありますから、その様な事態になる事を避けるため、Sheet1のD列の値の中に含まれている事が無く、尚且つSheet1のE列の値の中に含まれている事もあり得ない様な文字（記号）を、間に挟む事で、区別がつく様にしています。
　ですから、必ずしも「◆」である必要はなく、「■」や「★」、「゛」、ハートマーク、等々の品名や仕様等に使われる事が無い記号や文字でさえあれば、何でも構いません。
　但し、「*」、「?」、「~」だけは、ワイルドカードと言って、検索やカウントを行う際に特別な意味を持つ記号であるため、単なる区切りのためには使用を避けた方が良いと思います。
　それと、良く見直してみました処、Sheet1のD列のデータは顧客Noであり、Sheet1のE列のデータは顧客名なのですから、「顧客Noが同じであっても顧客名は行によっては異なっている場合もあり得る」という事でもなければ、「Sheet1のD列が、Sheet6のH5の値と等しい」という条件を満たしてさえいれば、自動的に「Sheet1のE列が、Sheet6のI5の値と等しい」という条件も満たす事が出来る筈（Sheet6のI5に誤った顧客名を入力した場合は別ですが）ですので、Sheet6のJ列の

=SUMPRODUCT((Sheet1!$D$5:$D$1000=$H5)*(Sheet1!$E$5:$E$1000=$I5),Sheet1!$G$5:$G$1000)

という関数の中の(Sheet1!$E$5:$E$1000=$I5)という条件は不要ではないでしょうか？
　そうしますと、条件付きで合計値を求める際の条件は「Sheet1のD列が、Sheet6のH5の値と等しい」という1つだけになりますから、SUMPRODUCT関数や作業列を使うまでもなく、

=SUMIF(Sheet1!$D:$D,$ H5, Sheet1!$G:$G)

という関数でも、総台数を求める事が出来るのではないかと思いますが、如何でしょうか？

> =データ件数が20～30件程度であれば目視で確認作業を行い訂正を試みることは可能ですが、データ件数が多い場合、工数の無駄となり、理に適う作業ではないと判断した為、御教授願いました。

　目視で確認とは、一体何を確認されるのでしょうか？
　「だ」が本来は「A」とすべき所以外にも存在している場合もあるため、その様な仕様が入力されているセルを、置換を行う範囲から除外するためなのでしょうか？
　もし、仕様データに含まれている「だ」の中に、「A」とすべき所に誤入力してしまったものではなく、「だ」としたままで間違いではないものもあるという事でしたら、全ての「だ-BB」や「だBB」を、それぞれ一括して「A-BB」に置換してしまえば良いと思います。
　同様に、もしも、「ュウリABB」や「ュウリA-BB」の中の「ュウリ」を、元データであるSheet1のC列の中から削除してしまっても構わないのでしたら、「ュウリABB」や「ュウリA-BB」を、それぞれ一括して「A-BB」に置換してしまわれた方が宜しいのではないでしょうか？

>=御記入されている文字は「だ」だけではなく「け」や「が」、「び」と色々ありました。それらの誤記入を一括で「A」に置換することは可能なのでしょうか。

　前回までの話では「だ-BB」の事しか仰っておられなかったので、間違いは「だ-BB」の場合のみかと思ったのですが、他にも色々なパターンがあるとなりますと、それらの「だ」、「け」、「が」、「び」等々は、誤記ではなく、必要なデータの一部であるという恐れはないのでしょうか？
　おそらく既にやっておられる事とは思いますが、念のために、バックアップとして変更前のExcelファイルのコピーファイルを作成しておかれる事をお勧め致します。
　尚、もし、「-BB」という文字列が1つのセル内に入力されている文字列の中に複数回現れているという場合がないのでしたら、検索する文字列として「*-BB」を指定し、置換後の文字列として「A-BB」を指定して、Sheet1のC列を一括して置換しますと、「だ-BB」、「け-BB」、「が-BB」、「び-BB」、「ュウリA-BB」等々が、一括して「A-BB」に置換されます。

>=ABB107〇C,A-BB107〇C,ABB-107〇C・・・など色々なパターンがあります。これらを正式な形の『A-BB-107〇C』で表示させたい為に御教授願いました。

　もしも、1つのセル内に入力されている文字列の中に、「BB」という文字列が複数回現れているという様な事が無い場合には、まずSheet1のC列を全て選択し、Excelの置換機能を使用して全ての「BB」を一括して「-BB-」に置換してから、「--」を一括して「-」に置換されると良いと思います。

回答No.6

kagakusuki

【1点目】

>『#』はあくまでSERIALを記述する為のマークのようなものである為、表示させず、A-BB-107〇だけの表示とさせたい場合、どのようにしたら宜しいでしょうか。

　それは、回答No.3でお尋ねした

>それは、製品仕様に入力されている文字列データの中で、『#』『. 』『 』が現れるのはSERIAL.Noの直前の1箇所のみであり、SERIAL.Noの直前以外には『#』『. 』『 』が現れるの事は皆無であると考えて宜しいのでしょうか？

という補足要求に対する御答えによって方法が変わって参りますので、上記の補足要求に御答え頂く様御願い致します。

【2点目】
>C列に於いて…だ-BB-107〇C #SERIAL No.

>上記のような表示が数箇所程あり、入力ミスされた『だ』を抽出時に正式な『A』に正して抽出するというような方法は御座いますでしょうか。

　それは元データに入力されている内容が誤っているのですから、抽出時に訂正される様にするのではなく、元データであるSheet1のデータを訂正されるべきだと思います。
　もし、Sheet1のC列において、本来は「A」とすべきところ以外には「だ」という文字が使用されているものが皆無の場合には、Excelの置換機能を使用して、Sheet1のC列上にある全ての「だ」を、一括して「A」に置換してしまわれては如何でしょうか？

【3点目】
>C列に於いて…ABB107〇C#SERIAL No.

>上記のような表示に於いて『-』を入れ込みい。
>入れ込む箇所は『A』の後ろ、『BB』の後ろの2箇所で『A-BB-107〇C』のような表示方法でF列に表示させたいのですが、可能でしょうか。

　これもまた元データに入力されている内容が誤っているのですから、抽出時に訂正される様にするのではなく、元データであるSheet1のデータを訂正されるべきだと思います。
　もし、Sheet1のC列において、『-』が必要な所についていない箇所というのが、「A-BB」の後だけであり、尚且つ「ABBC-107〇C#SERIAL No.」などといった「A-BB」と「ハイフォン付きの数字」との間に他の文字が挟まっている様な形式の文字列が存在していない場合には、一旦全ての「A-BB」を「A-BB-」に置換してしまってから、「A-BB--」を「A-BB-」に置換してしまわれては如何でしょうか？

補足

>kagakusukiさん

御返信有難うございます。
前述した内容について補足回答させて頂きます。

【1点目】
下記にてお答えさせて頂きましたので一読願います。

【2点目】
>それは元データに入力されている内容が誤っているのですから、抽出時に訂正される様にするのではなく、元データであるSheet1のデータを訂正されるべきだと思います。

=データ件数が20～30件程度であれば目視で確認作業を行い訂正を試みることは可能ですが、データ件数が多い場合、工数の無駄となり、理に適う作業ではないと判断した為、御教授願いました。

>Sheet1のC列において、本来は「A」とすべきところ以外には「だ」という文字が使用されているものが皆無の場合には、Excelの置換機能を使用して、Sheet1のC列上にある全ての「だ」を、一括して「A」に置換してしまわれては如何でしょうか？

=御記入されている文字は「だ」だけではなく「け」や「が」、「び」と色々ありました。それらの誤記入を一括で「A」に置換することは可能なのでしょうか。

【3点目】
>これもまた元データに入力されている内容が誤っているのですから、抽出時に訂正される様にするのではなく、元データであるSheet1のデータを訂正されるべきだと思います。

=【2点目】でお答えしたことと同じお答えになります。

>『-』が必要な所についていない箇所というのが、「A-BB」の後だけであり、尚且つ「ABBC-107〇C#SERIAL No.」などといった「A-BB」と「ハイフォン付きの数字」との間に他の文字が挟まっている様な形式の文字列が存在していない場合には、一旦全ての「A-BB」を「A-BB-」に置換してしまってから、「A-BB--」を「A-BB-」に置換してしまわれては如何でしょうか？

=ABB107〇C,A-BB107〇C,ABB-107〇C・・・など色々なパターンがあります。これらを正式な形の『A-BB-107〇C』で表示させたい為に御教授願いました。

回答No.5

kagakusuki

追加情報有難う御座います。
　只、意思の疎通に幾つかの齟齬がある様で、未だ十分とは申せません。

>=はい、あります。重複除去し、重複しているデータを集計する為に加減したデータベースをsheet6にて作成してあり、そのデータベースからsheet1の下記列に展開・表示させております。

　私の言葉が足らず、誤解させてしまい申し訳御座いません。
　「重複データがある場合には空欄にしている列」という意味ではなく、
「『抽出結果を表示する表において個々の行ごとにデータを表示しなければならないデータ』が入力されているSheet1の列の中で、Sheet1に何らかの元データが入力されている行においては、その列のセル内に必ず何らかのデータが入力されていて、尚且つ、そのデータと同じデータが、同じ列内の他の行のセルには存在していない事が保障されている列」
という意味です。
　その様な列がある場合には、その列の列番号を御教え願います。

>SERIAL.Noの直前以外には『#』『. 』『 』が現れる事は皆無であると考えて宜しいのでしょうか？

>=はい。必ず上記3点のどれかは必ずあります。

　済みません、私が尋ねた事とは関係の無い事を御答えになられている様ですが、私は「SERIAL.Noの直前に『#』、『. 』、『 』が必ずあるのか」という事を尋ねてはおりません。
　SERIAL.Noの直前の所よりも前の、製品仕様の途中の所や製品仕様の前の所、及び、SERIAL.Noの直前の所よりも後の、SERIAL.Noの途中の所やSERIAL.Noの後の所、等にも『#』、『. 』、『 』の何れかが現れている場合は皆無なのでしょうか？
　（日本語では、「～という事は無いのか？」という形式で質問された場合、「現れている」事があるのかないのかで、「はい」か「いいえ」が決まるのではなく、「～という事は無い」と言い切った場合に、その言葉が事実を言い表しているのか、それとも誤った事を言い表しているのか、という事で、「はい」か「いいえ」が決まりますので、上記の質問の場合は、SERIAL.Noの直前以外の所にも『#』、『. 』、『 』の何れかが現れる事もある場合には「いいえ」、SERIAL.Noの直前以外の所に『#』、『. 』、『 』の何れかが現れる事はあり得ない場合には「はい」となります）

>>Excelの置換機能を利用して、元データであるSheet1のC列のデータの中身を書き換えて、『. 』と『 』の全てを『#』に置き換えてしまうという方法を考えたのですが、この様な元データの一部を書き換えてしまう事には、何か問題があるか？

>=はい。『. 』に関してはそれも有効ですが『 』に関して言えば、問題が御座います。問題というのは、『#』の前にあったりなかったりすること。また、『 』を『#』と同じ使い方をしている場合とが御座います。

　ちょっと、私が尋ねた事の趣旨を誤解しておられる様ですが、私が知りたかった事は、Sheet1のC列のデータの中で、仕様の部分とSERIAL.Noの部分を区切っている、『. 』や『 』を『#』に置換してしまう事で、Sheet1のC列のデータが、元々入力されていたものとは（仕様の部分とSERIAL.Noの部分を区切る記号の部分だけが）若干変わってしまう事が問題となるのか否かという事です。
　それと、『#』の直前に『 』がある事が何故問題になるのでしょうか？
　「 #」を「#」に置換してしまってから、「 」を「#」に置換する様にされれば宜しいのではないでしょうか？

　後、意思の疎通とは別の話になりますが、J列の関数が少し気になります。

>J列…総台数(=SUMPRODUCT((Sheet1!$D$5:$D$1000=$H5)*(Sheet1!$E$5:$E$1000=$I5),Sheet1!$G$5:$G$1000)

　SUMPRODUCT関数を使った場合、対象範囲の1行ごとに繰り返し計算が行われます。
　質問者様が使っておられる関数の場合は、

(Sheet1!$D$5=$H5)*(Sheet1!$E$5=$I5)*Sheet1!$G$5
　　　　　　　　　　　　　+
(Sheet1!$D$6=$H5)*(Sheet1!$E$6=$I5)*Sheet1!$G$6
　　　　　　　　　　　　　+
(Sheet1!$D$7=$H5)*(Sheet1!$E$7=$I5)*Sheet1!$G$7
　　　　　　　　　　　　　+
　　　　　　　　　　　　　・
　　　　　　　　　　　　　・
　　　　　　　　　　　　　・
　　　　　　　　　　　　　+
(Sheet1!$D$1000=$H5)*(Sheet1!$E$1000=$I5)*Sheet1!$G$1000

という具合に、同じ様な計算処理が996回も繰り返されます。
　このため、SUMPRODUCT関数の対象範囲が広くなりますと、計算処理を行う際のコンピーュータの負荷が大きくなり、計算結果が出るまでに時間が掛かる様になるという難点があります。
　パソコンの処理速度によって差はあるものの、2000行程度まででしたら、処理に要する時間は未だなんとか我慢できる程度に収まるかも知れませんが、もしも、今後もデータが増えて行く様な事があるのでしたら、SUMPRODUCT関数を使わない方法に変更された方が良いと思います。
　例えば、作業列に次の様な関数

=IF(OR(INDEX(Sheet1!$D:$D,ROW())="",INDEX(Sheet1!$E:$E,ROW())=""),"",INDEX(Sheet1!$D:$D,ROW())&"◆"&INDEX(Sheet1!$E:$E,ROW()))

を入力しておいてから、J列の総台数を求める関数を

=SUMIF(作業列,INDEX(Sheet1!$H:$H,ROW())&"◆"&INDEX(Sheet1!$I:$I,ROW()),Sheet1!$G:$G)

とする等の様にされた方が、計算処理に要する負荷が格段に小さくなります。

補足

>「重複データがある場合には空欄にしている列」という意味ではなく、

=空欄になっておりません。何かしらの値は入力されております。

>『抽出結果を表示する表において個々の行ごとにデータを表示しなければならないデータ』が入力されているSheet1の列の中で、Sheet1に何らかの元データが入力されている行においては、その列のセル内に必ず何らかのデータが入力されていて、尚且つ、そのデータと同じデータが、同じ列内の他の行のセルには存在していない事が保障されている列

=Z列・・・顧客No. AA列・・・顧客名 AB列・・・総台数 AC列・・・合計総額

上記、4列が保障されており、同じデータが存在していないことが保障される列且つ何らかのデータは入力されている列となります。総台数及び合計総額に於いてのデータは対象となるデータが無い場合、必ず『0』が入力されています。

>SERIAL.Noの直前の所よりも前の、製品仕様の途中の所や製品仕様の前の所、及び、SERIAL.Noの直前の所よりも後の、SERIAL.Noの途中の所やSERIAL.Noの後の所、等にも『#』『. 』『 』の何れかが現れている場合は皆無なのでしょうか？
(SERIAL.Noの直前以外の所に『#』、『. 』、『 』の何れかが現れる事はあり得ない場合には「はい」となります)

=下記でお答えしたとおり、『はい』で問題御座いません。
SERIAL No.直前以外の箇所で現れることは御座いません。

>Sheet1のC列のデータの中で、仕様の部分とSERIAL.Noの部分を区切っている、『. 』や『 』を『#』に置換してしまう事で、Sheet1のC列のデータが、元々入力されていたものとは（仕様の部分とSERIAL.Noの部分を区切る記号の部分だけが）若干変わってしまう事が問題となるのか否か

=すいません。、『. 』や『 』を『#』に置換することはなんら問題となりません。

>J列の関数について

=SUMPRODUCT関数はそういうデメリットがあるとは知りませんでした。御教授頂いた数式を入力してみたいと思います。因みに、◆←これってどういう意味ですか？

回答No.4

kagakusuki

>【文字列を操作する為に必要な列】
>C列（この列にsheet1から参照・抽出した仕様が表示されています）…A-BB-107〇C#SERIAL No.←このように表示

>F列…=IF(C5="","",SUBSTITUTE(LEFT(C5,FIND("#",C5)-1),"ュウリ",""))

>G列…=IF(C5="","",RIGHT(C5,LEN(C5)-FIND("#",C5)))

>上記の数式ですと、#value!が表示されてしまいます。

　『#』や『. 』や『 』が同じ文字列内に複数回現れている場合には、どの様にすれば良いのかという条件が不明です。
　取り敢えずの話として、『#』や『. 』や『 』が同じ文字列内に複数回現れている場合には、『#』、『. 』、『 』の中で、最も後ろの方に現れているものが、仕様とSERIAL No.の境目であるものと考えますと、以下の様な関数にされては如何でしょうか？

【G5セルに入力する関数】

=IF($C5="","",IF(LEN($C5)*3-LEN(SUBSTITUTE($C5,"#",))-LEN(SUBSTITUTE($C5,".",))-LEN(SUBSTITUTE($C5," ",)),REPLACE($C5,1,MAX(FIND(CHAR(1),SUBSTITUTE("#"&$C5,"#",CHAR(1),LEN($C5)-LEN(SUBSTITUTE($C5,"#",))+1)),FIND(CHAR(1),SUBSTITUTE("."&$C5,".",CHAR(1),LEN($C5)-LEN(SUBSTITUTE($C5,".",))+1)),FIND(CHAR(1),SUBSTITUTE(" "&$C5," ",CHAR(1),LEN($C5)-LEN(SUBSTITUTE($C5," ",))+1)))-1,),""))

【F5セルに入力する関数】

=IF($C5="","",SUBSTITUTE(IF($G5="",$C5,LEFT($C5,LEN($C5)-LEN($G5)-1)),"ュウリ",))

補足

>kagakusUkiさん

ご教授有難う御座います。
ご教授頂いた数式を使わせて頂いたところ、『.』はもちろんのことながら、拾い切れていなかったデータも拾うことが出来ました。すごいです。流石の一言に尽きます。感謝の一言です。

この回答を持ってベストアンサーとさせて頂きたいところなのですが、3点程、追記でご教授頂いてもよろしいでしょうか。

【1点目】
C列に於いて…A-BB-107〇#
*SERIAL No.記載無で107〇と『#』の間にスペース無。

上記に於いて、御教授頂いた数式を入力し、F列・G列に抽出したところ・・・

F列・・・A-BB-107〇# ←このように表示
G列・・・空白

のように表示されてしまいました。『#』はあくまでSERIALを記述する為のマークのようなものである為、表示させず、A-BB-107〇だけの表示とさせたい場合、どのようにしたら宜しいでしょうか。

【2点目】
C列に於いて…だ-BB-107〇C #SERIAL No.

上記のような表示が数箇所程あり、入力ミスされた『だ』を抽出時に正式な『A』に正して抽出するというような方法は御座いますでしょうか。

【3点目】
C列に於いて…ABB107〇C#SERIAL No.

上記のような表示に於いて『-』を入れ込みい。
入れ込む箇所は『A』の後ろ、『BB』の後ろの2箇所で『A-BB-107〇C』のような表示方法でF列に表示させたいのですが、可能でしょうか。

以上、3点、御教授下さいます様、宜しくお願い申し上げます。

回答No.3

kagakusuki

済みません、回答No.2において、

>顧客Noを表示する列がB列になる様にした上で

と書いてしまいましたがこれは間違いで、正しくは、

「Sheet1のA列から抽出したデータを表示する列がB列になる様にした上で」

という事です。

　後それから、確認したいのですが、Sheet1の元データの表において、例えば連番の様に「その列に同じ値を持つデータが重複して現れる事が絶対に無い事が保障されているデータ」は存在するのでしょうか？
　もし、重複がない事が保障されているデータがある場合には、そのデータを利用して、作業列を使わずに済ます事が出来る可能性がありますので、それがどの列のデータなのかを御教え頂く訳には参りませんでしょうか？
　或いは、その様な重複がない事が保障出来るデータなど存在しない場合には、列を1列増やして、行ごとに連番を付ける訳には参りませんでしょうか？

　それから、御質問の件とは少し外れてしまいますが、

>その目的とは【sheet1の参照用シートからsheet2（3,4,5）へ顧客別（正確には顧客No.)に展開する、尚且つ、その顧客毎の製品仕様を全て抽出する為の数式】が『O列～V列』に入っております。1社分のデータを別シートに抽出する為に、作業列を2列使用。

という点に関してアドバイスをさせて下さい。
　単に、顧客番号ごとのデータを抽出するだけでしたら、作業列を1列設けるだけで全ての顧客番号に対応させる事が可能ですから、

>1社分のデータを別シートに抽出する為に、作業列を2列使用。

などという事を行う必要は御座いません。
　又、元データのシート上に作業列を設けていますと、元データのシートを印刷する際に印刷範囲を定めておかなければ、作業列の所まで印刷されてしまい、見苦しくなったり、印刷用紙を無駄にしてしまう恐れがありますから、主に見栄えの観点から、作業列は別のシート上に設けた方がベターではないかと、"私個人は"考えております。

　さて、作業列を全顧客共通の1行のみで済ませる方法は以下の様なものとなります。
　今仮に、作業用シートのB列（A列としても良いのですが、回答No.2で使用している列と同じ列を使用して説明したのでは、話が紛らわしくなってしまいますので、B列を使う事にします）を作業列として使用するものとし、Sheet2のD5セルに顧客番号を入力すると、その顧客番号のデータのみをSheet1から抽出した結果をSheet2（のA列～AD列）に表示するものとします。

　まず、作業用シートのB5セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$D:$D,ROW())="","",INDEX(Sheet1!$D:$D,ROW())&"◆"&COUNTIF(Sheet1!$D$4:INDEX(Sheet1!$D:$D,ROW()),INDEX(Sheet1!$D:$D,ROW())))

　次に、作業用シートのB5セルをコピーして、作業用シートのB6以下に貼り付けて下さい。

　次に、Sheet2のA5セルに次の関数を入力して下さい。

=IF(AND(ROWS($5:5)<=COUNTIF(作業用!$B:$B,$D$5&"◆*"),COUNTIF(Sheet1!$A$4:$AC$4,A$4)),IF(INDEX(Sheet1!$A:$AC,MATCH($D$5&"◆"&ROWS($5:5),作業用!$B:$B,0),MATCH(A$4,Sheet1!$A$4:$AC$4,0))="","",INDEX(Sheet1!$A:$AC,MATCH($D$5&"◆"&ROWS($5:5),作業用!$B:$B,0),MATCH(A$4,Sheet1!$A$4:$AC$4,0))),"")

　次に、Sheet2のA5セルをコピーして、Sheet2のB5～C5の範囲と、Sheet2のE5～AD5の範囲と、Sheet2のA6～AD6の範囲に貼り付けて下さい。
　次に、Sheet2のA6～AD6の範囲をコピーして、同じ列の7行目以下に貼り付けて下さい。
　次に、Sheet2のコピーシートを顧客の数だけ作成して下さい。
　これで準備は完了で、後は、それらの各コピーシートのD5セルに顧客Noを入力して下さい。

　これで、顧客ごとのデータを抽出する事が出来ます。

>・#より以降がSERIAL No.の為、#を境に仕様とSERIAL.Noで分けて表示させている。

>F列…仕様 , G列・・・SERIAL No.

>(1)上記に於いて、#ではなく『. 』の場合、また、#を含めて記載がなく記載無（スペース）の場合、#と同じように仕訳する為の数式はどのように入れ子したら宜しいのでしょうか。(#が.に置き換わった場合。『#』『. 』『 』が混在）

　それは、製品仕様に入力されている文字列データの中で、『#』『. 』『 』が現れるのはSERIAL.Noの直前の1箇所のみであり、SERIAL.Noの直前以外には『#』『. 』『 』が現れるの事は皆無であると考えて宜しいのでしょうか？（もしも、SERIAL.Noの直前の所よりも更に前の所に『#』『. 』『 』が現れる事もある場合には、『#』『. 』『 』以外の何かの目印になる様なものが無ければ、SERIAL.Noを求める事が出来なくなります）
　それと、Excelの置換機能を利用して、元データであるSheet1のC列のデータの中身を書き換えて、『. 』と『 』の全てを『#』に置き換えてしまうという方法を考えたのですが、この様な元データの一部を書き換えてしまう事には、何か問題が御座いますでしょうか？

補足

>kagakusukiさん

御返事有難う御座います。
上記内容について御回答致します。

>Sheet1の元データの表において、重複して現れる事が絶対に無い事が保障されているデータは存在するか？

=はい、あります。重複除去し、重複しているデータを集計する為に加減したデータベースをsheet6にて作成してあり、そのデータベースからsheet1の下記列に展開・表示させております。
【sheet1にて】
Y列…顧客No.（=sheet6データベース!H5)
Z列…顧客名(=sheet6データベース!I5)
AA…総台数(=sheet6データベース!J5)
AB…合計総額(=sheet6データベース!K5)

>重複がない事が保障されているデータがある場合は、そのデータを利用して、作業列を使わずに済ます事が出来る可能性がある為、それがどの列のデータなのかを御教え頂く訳には参りませんでしょうか？

=はい。以下に記述致します。
【Sheet6】4行に項目名を、5行目からデータ。H列から表開始。（A～Gまでは使用中）
H列…顧客No.
I列…顧客名
J列…総台数(=SUMPRODUCT((Sheet1!$D$5:$D$1000=$H5)*(Sheet1!$E$5:$E$1000=$I5),Sheet1!$G$5:$G$1000)
K列…合計総額(=SUMIF(Sheet1!$D$5:$D$1000,H5,Sheet1!$J$5:$J$1000)

>製品仕様に入力されている文字列データの中で、『#』『. 』『 』が現れるのはSERIAL.Noの直前の1箇所のみか。

=はい。１箇所のみで御座います。

SERIAL.Noの直前以外には『#』『. 』『 』が現れる事は皆無であると考えて宜しいのでしょうか？

=はい。必ず上記3点のどれかは必ずあります。

>Excelの置換機能を利用して、元データであるSheet1のC列のデータの中身を書き換えて、『. 』と『 』の全てを『#』に置き換えてしまうという方法を考えたのですが、この様な元データの一部を書き換えてしまう事には、何か問題があるか？

=はい。『. 』に関してはそれも有効ですが『 』に関して言えば、問題が御座います。問題というのは、『#』の前にあったりなかったりすること。また、『 』を『#』と同じ使い方をしている場合とが御座います。

以上となります。

お力添えの程、宜しくお願い申し上げます。

回答No.2

kagakusuki

>現在、参照用シートに作業列を作り対応。作業列ばかりが増えて正直困っているのも確かなのです

　何故、参照用シートの作業列が増えるのでしょうか？
　御質問の抽出以外の目的で使っている作業列が数多くあるという事なのでしょうか？

　以下は、作業列を、作業列専用のシートに1列、各「顧客別シート」に1列ずつ設ける方法です。
　尚、「顧客別シート」の雛型として、Sheet2に作業列を設ける際には、将来的にデータを表示しなければならない列が、AD列以上に増える可能性もあるかも知れませんので、A列の前に新たな列を挿入し、顧客Noを表示する列がB列になる様にした上で、新たなA列を作業列として使用するものとします。

　まず、作業列専用のシートを作成し、そのシートの名称を例えば「作業用」等として下さい。
　次に、作業用シートのA4セルに「作業列」等の「数値データでは無い何らかの文字列」を入力して下さい。（該当するデータが無い場合においてもエラーとなる事を防ぐため）
　次に、作業用シートのA5セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$D:$D,ROW())="",INDEX(Sheet1!$F:$F,ROW())=""),"",INDEX(Sheet1!$D:$D,ROW())&"◆"&INDEX(Sheet1!$F:$F,ROW()))

　次に、作業用シートのA5セルをコピーして、作業用シートのA6以下に貼り付けて下さい。

　次に、Sheet2のA4セルに対して、次の様な操作を行い、書式設定を非表示にして下さい。
　　（尚、下の添付画像の例では、非表示にしていたのでは、どんな値となっているのかが判りませんから、敢えて薄い青色で表示する様にしております）

Sheet2のA4セルを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

;;;

と入力
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　次に、Sheet2のA4セルをコピーして、Sheet2のA5セル、H1セル、H2セルの各セルに貼り付けて下さい。
　次に、Sheet2のA4セルに次の関数を入力して下さい。

=ROW(作業用!$A$4)

　次に、Sheet2のA5セルに次の関数を入力して下さい。

=IF(ROWS($A$5:$A5)>COUNTIF(作業用!$A:$A,IF($H$1="","*",$H$1)&"◆*"&$H$2&"*"),"",MATCH(IF($H$1="","*",$H$1)&"◆*"&$H$2&"*",INDEX(作業用!$A:$A,A4+1):INDEX(作業用!$A:$A,MATCH("*?",作業用!$A:$A,-1)),0)+A4)

　次に、Sheet2のB5セルに次の関数を入力して下さい。

=IF($A5="","",IF(ISERROR(1/(INDEX(Sheet1!$A:$AC,$A5,MATCH(B$4,Sheet1!$A$4:$AC$4,0))<>"")/$A5),"",INDEX(Sheet1!$A:$AC,$A5,MATCH(B$4,Sheet1!$A$4:$AC$4,0))))

　次に、Sheet2の4行目のB列～AE列の各セルに、「顧客No」や「製品仕様」といった各項目名を入力して下さい。
　尚、その際入力する項目名は、Sheet1に入力されている項目名と、完全に同じものとして下さい。（個々のセルに入力されている項目名を表す文字列がSheet1のものと同じになってさえいれば、Sheet1と比べて項目名が並んでい順序が異なっていたり、Sheet1にのみ存在していて、Sheet2には無い項目名があったとしても構いません）
　次に、Sheet2のB5セルをコピーして、Sheet2のC5～AE5の範囲に貼り付けて下さい。
　次に、Sheet2のA5～AE5の範囲をコピーして、同じ列範囲の6行目以下に貼り付けて下さい。
　次に、見栄えを良くするために、Sheet2のA列の列幅を適当な幅に調整して下さい。（列幅を0としてしまっても構いません）

　これで準備は完了で、後はH1セルに顧客Noを、H2セルに「107」等の検索対象となるキーワードを入力しますと、H2セルに入力したキーワードが含まれている文字列が製品仕様欄に入力されていて、尚且つ、顧客NoがH1セルに入力したのと同じNo.となっている行のデータが抽出されます。
　因みに、H2セルにキーワードを入力せずに空欄とした場合には、H1セルに入力したNoと同じNoが顧客No欄に入力されている全ての行のデータが抽出されます。
　逆に、H1セルに顧客Noを入力せずに空欄とした場合には、H2セルに入力したキーワードが含まれている文字列が製品仕様欄に入力されている全ての行のデータが抽出されます。
　尚、H1セルを空欄とした場合において表示されるデータは、顧客Noや顧客名が行によって異なっている事が考えられるため、

>B列(一つのセルになってます。C列も同様一つのセルになってます）

というレイアウトには敢えてしておりません。

補足

>kagakusukiさん

返信有難う御座います。
前回もご教授頂き有難う御座いました。
また、前回のご教授頂いた内容の結果ですが、時間がなくトライすること出来ていない為、今しばらく結果をお待ち頂きたく。

また、今回の御質問の件は前回の続きとなっております。

さて、上記、御質問についてお答え致します。

>何故、参照用シートの作業列が増えるのか？質問の抽出以外の目的で使っている作業列が数多くあるという事なのか？

=はい、質問以外の別目的でsheet1の参照用シートに作業列を設けています。

その目的とは【sheet1の参照用シートからsheet2（3,4,5）へ顧客別（正確には顧客No.)に展開する、尚且つ、その顧客毎の製品仕様を全て抽出する為の数式】が『O列～V列』に入っております。1社分のデータを別シートに抽出する為に、作業列を2列使用。特定の顧客は現在、4社。4社×2列で8列。その8列分が『O列～V列』というわけです。

ここから、顧客が増えればsheet1の参照用シートには作業列が2列ずつ随時増えていくこととなる為、作業列が増えると記載致しました。

私ながら、分からない状態ながらも試行錯誤して数式を入力し、kagakusukiさんと同じように顧客No.検索欄へNoを入力すると全ての項目が表示対応することが可能なシートが8割ほど完成したのですが、残り2割ほどがうまくいかず悩んでおります。その2割分をご教授頂けるとありがたく。

お忙しい中、ご教授下さった内容ですが、自己解決でどうにかこうにか出来上がりました。大変、申し訳ございません。しかしながら、検索キーワードの入力でも該当するようにしたいので解読を試み有難く使用させて頂きたいと思います。有難う御座います。

【ご教授頂きたいこと / 文字列操作について】
・#より以降がSERIAL No.の為、#を境に仕様とSERIAL.Noで分けて表示させている。

F列…仕様 , G列・・・SERIAL No.

(1)上記に於いて、#ではなく『. 』の場合、また、#を含めて記載がなく記載無（スペース）の場合、#と同じように仕訳する為の数式はどのように入れ子したら宜しいのでしょうか。(#が.に置き換わった場合。『#』『. 』『 』が混在）

【文字列を操作する為に必要な列】
C列（この列にsheet1から参照・抽出した仕様が表示されています）…A-BB-107〇C#SERIAL No.←このように表示

F列…=IF(C5="","",SUBSTITUTE(LEFT(C5,FIND("#",C5)-1),"ュウリ",""))

G列…=IF(C5="","",RIGHT(C5,LEN(C5)-FIND("#",C5)))

上記の数式ですと、#value!が表示されてしまいます。
『. 』並びに『 』の場合の数式が入力されていない為、当たり前の話ですが…。いまいちネストの方法がわからず苦戦してます。

お忙しい中申し訳ありませんが、ご教授のほど、宜しくお願い致します。

回答No.1

Pochi09

全体像がよく見えないので、107の抽出部分を回答します。
（検索対象をF列、1行目は列名が記載されているとしています）

AEに補助列を作成してAE2に
=FIND("107",F2,1)
を入力し、#VALUE! 以外の列（見つかった列）が、対象行です。

例えばフィルタで #VALUE! のチェックを外せば、必要な行のみが抽出されます。
別シートで作業したい場合は、表示された行をコピー＆ペーストしてみては如何でしょうか？

（Excelが2003の場合は、フィルタの複数条件が面倒なので
=ISERROR(FIND("107",F2,1))
としておき、FALSEが表示されてる行を抽出します）

補足

>pochi09さん

御返信有難う御座います。やりたいことと相違があるのですが・・・やはり、作業列を使用して検索し文字数もしくは真偽で表示させた上で抽出するしかないのでしょうか？例えば、『もしsheet2（3,4,5）のB列に特定の顧客No.が入力された場合は、sheet1のD列より一致している顧客No.を探しなさい。一致した場合、同じ行のF列の製品仕様より<107>という文字列が一致しているのを探しなさい。一致した時は、そのセルに表示されている文字列全てをsheet2（3,4,5）のD列に返しなさい。条件一致したものがない時は表示無しとしなさい』のような指示で関数を入れ子して抽出って出来ないのでしょうか？そもそも文字列関数とデータ抽出関数は相性いいのでしょうか。

以上、宜しくお願い致します。

*sheet1・・・参照用シート,C列・・・製品仕様はF列の間違いです。