[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『特定の文字列を含むセルの転記について』（て〜んつてんて〜ん）
お助け下さい。よろしくお願いします。
２点お教え下さい。
お尋ね その１
Sheet1のG2からN2までのセルにそれぞれ色々な文字列が入っています。このうち [ ] を含む文字列が入っているセルの値を、関数を使ってSheet2のA1に転記したいと思っています。初心者でよく分からないまま、ネットや書籍で調べて
=IFERROR(INDEX(Sheet1!$G2:$N2,MATCH("*[*",Sheet1!$G2:$N2,0)),"")
という関数を使うところまではなんとか辿り着きました（関数をちゃんと理解出来ているかどうかアヤシいのですが、期待通りの結果が得られているので、多分合っているのだと思っています）。MATCH関数の検索値を [ だけにしているのは、 [ と ] は必ずペアで現れるので、 [ だけで用が足りるだろうと考えたからです。
ところが、実際に運用するに当たって、Sheet1のG2からN2までのセルの中に現れる [ ] には半角の場合と全角の場合の両方があることが判明しました。先の関数だと、Sheet1のG2からN2までのセルに入っている [ ] が全角の場合にはSheet2のA1に転記はされません。Sheet1のG2からN2までのセルに入っている [ ] が半角か全角かに関わらず（半角の場合も全角の場合もどちらでも）Sheet2のA1に転記されるようにするには、どうすればよろしいでしょうか。
お尋ね その２
Sheet1のG2からN2までのセルのうち、[ ] が入っているのは必ずしも１つには限らないことが判明しました。Sheet2のA1に転記したい [ ] を含むセルは、Sheet1のG2からN2までのセルに中に必ず１つは含まれており、その数は必ず１つだけです。Sheet1のG2からN2までのセルのうち [ ] が入っているのが２つ以上ある場合は、Sheet2のA1に転記したいセル以外にもう１つかそれ以上の別のセルにも [ ] が入っているということになります。Sheet2のA1に転記したいセル以外にも [ ] が入っているとしても、それは必ずSheet2のA1に転記したいセルよりも後に出てきます。言い換えれば、Sheet1のG2からN2までのセルのうち２つ以上のセルに [ ] が入っているとしても、Sheet2のA1に転記したいのは、そのうち先に出て来る（列名を表すアルファベットが先順位の）セルだということです。
Sheet2のA1に
=IFERROR(INDEX(Sheet1!$G2:$N2,MATCH("*[*",Sheet1!$G2:$N2,0)),"")
という関数を入れておいた場合、Sheet1の先のセル（列名を表すアルファベットが先順位のセル）が必ず優先的に転記されるのでしょうか。今試しにやってみたところでは、Sheet1の列名を表すアルファベットが先順位のセルが転記されたのですが、これはたまたまの結果でしょうか。それとも、例外なくSheet1の先のセル（列名を表すアルファベットが先順位のセル）が必ず転記されるでしょうか。
先の関数に必ずしも拘るわけではありませんので、別の方法でも構いません（出来れば関数で）。Sheet1のG2からN2までのセルのうち２つ以上のセルに [ ] が入っている場合でも、必ず列名を表すアルファベットが先順位のセルが転記されるようにする方法はあるでしょうか。
よろしくお願いします。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
全角は、Substitute関数を使って半角に代えた後、"テスト"すればいいです。 Match関数の第3引数を0にすると先順位のセルがヒットします。
A1セル =IFERROR(INDEX(Sheet1!$G2:$N2,MATCH("*[*",INDEX(SUBSTITUTE(Sheet1!$G2:$N2,"［","["),0),0)),"")
（半平太） 2018/03/30(金) 22:58
＞お尋ね その１
=IFERROR(INDEX(Sheet1!G2:N2,MATCH("*[*",INDEX(ASC(Sheet1!G2:N2),0),0)),"")
~~~~~~~~~~~~~~~~~~~~~~~~~~
とか
＞お尋ね その２
必ず ↓ のようになります。 ＞例外なくSheet1の先のセル（列名を表すアルファベットが先順位のセル）が必ず転記されるでしょうか。
ところで複合参照（$G2:$N2）にしているのは何か理由があるんですか？
以上です （笑） 2018/03/30(金) 23:07
早速にありがとうございます。お礼が遅くなって申し訳ありませんでした。
初心者で関数のことをほとんど分かっていないので教えて頂きたいのですが、ご教示頂いた関数の内の
INDEX(SUBSTITUTE(Sheet1!$G2:$N2,"［","["),0)
の部分はどういう意味になるのでしょうか。
SUBSTITUTE(Sheet1!$G2:$N2,"［","[")
の部分は、Sheet1のG2からN2の範囲内のセルに含まれる全角 ［ を半角 [ に置換するという意味は分かります。
INDEX関数は通常 INDEX(範囲, 行番号, 列番号) のような形で、範囲の部分には$G2:$N2のようなセル番号が入るのだと思っていたのですが、ご教示頂いた関数では範囲の部分が SUBSTITUTE(Sheet1!$G2:$N2,"［","[") になっています。この意味は、【Sheet1の$G2から$N2の範囲内のセルに含まれる全角 ［ を半角 [ に置換したもの】を範囲として指定しているということになるのでしょうか（セル番号を使う以外に、そういう範囲指定の仕方も出来る？）。仮にそうだったとしても、そのあとの引数が 0 一つだけになっているのはどういうことでしょうか。引数の数が足りないように思うのですが・・・。
お手数をおかけして恐縮ですが、ご教示頂けましたら幸いです。よろしくお願いします。
（て〜んつてんて〜ん） 2018/03/31(土) 08:17
早速にありがとうございます。お礼が遅くなって申し訳ありませんでした。
先の半平太様にお尋ねしたのと同じことになってしまうのですが、ご教示頂いた関数の内の
INDEX(ASC(Sheet1!G2:N2),0)
の部分はどういった意味になるのでしょうか。ASC関数は全角文字を半角文字に変換するものだということは、今ネットで調べてみて分かりました。INDEX関数は通常 INDEX(範囲, 行番号, 列番号) のような形で、範囲の部分には$G2:$N2のようなセル番号が入るのだと思っていたのですが、ご教示頂いた関数では範囲の部分が ASC(Sheet1!G2:N2) になっています。この意味は、【Sheet1のG2からN2の範囲内のセルに含まれる文字列のうち記号等を半角に変換したもの】を範囲として指定しているということになるのでしょうか（セル番号を使う以外に、そういう範囲指定の仕方も出来る？）。仮にそうだったとしても、そのあとの引数が 0 一つだけになっているのはどういうことでしょうか。引数の数が足りないように思うのですが・・・。
お尋ね その２に対して、
必ず ↓ のようになります。 ＞例外なくSheet1の先のセル（列名を表すアルファベットが先順位のセル）が必ず転記されるでしょうか。
とご回答頂きましたが、お尋ね その２の疑問に関しては、特に何の処理せず今のままで大丈夫ということになりますでしょうか。
＞ところで複合参照（$G2:$N2）にしているのは何か理由があるんですか？
特に意味はありません。この関数を書いてからかなり時間が経ってしまったので記憶が定かではありませんが、どこかのサイトで見かけた関数をコピーしたか何かで、そのコピー元が複合参照になっていたのをそのまま持って来たとかいったようなことではなかったかと思います。
（て〜んつてんて〜ん） 2018/03/31(土) 08:20
回答した数式は希望通りの結果になったんですか？ なったんだという前提で説明すると･･･
■INDEX関数の「行番号」「列番号」について 必ず2つとも指定しないといけないというものではありません。
あくまで例ですが =INDEX(A1:A10,3) とか =INDEX(A2:F2,3) のように 範囲が「1列または1行」の場合は、行番号（列番号）を省略できます。 前者は「A3」の値、後者は「C2」の値が返ります。
INDEX(ASC(G2:N2),0) のように「0」にすると その範囲全体が配列として返ります。 上の場合だとG2:N2の各セルを半角に変換した値になります。
=IFERROR(INDEX(Sheet1!G2:N2,MATCH("*[*",INDEX(ASC(Sheet1!G2:N2),0),0)),"")
~~~~~~~~~~~~~~~~~~~~~~~~~~
数式バー上で波線部をマウスでドラッグしたあと、F9キーを押してみてください。
配列の結果を確認することができます。
確認後、ESCキーを押すと解除できます。
=IFERROR(INDEX(Sheet1!G2:N2,MATCH("*[*",ASC(Sheet1!G2:N2),0)),"")
~~~~~~~~~~~~~~~~~
↑ でもできると思いますが、この式は「Ctrl＋Shift＋Enter」で確定する必要があります。
INDEXを使えば普通に「Enter」だけでオッケーという点が違います。
詳しくは「配列数式」で検索してみてください。
■ASC関数について ＞【Sheet1のG2からN2の範囲内のセルに含まれる文字列のうち記号等を半角に変換したもの】
カタカナ、英数字など全角・半角があるものはすべて半角に変換されます。 あくまで数式上のことであって元の値には影響しません。
■お尋ね その２について ＞特に何の処理せず今のままで大丈夫ということになりますでしょうか。 「今のままで」とは？ カッコが全角・半角混在に対応した数式（MATCH関数で検索）なら大丈夫です。 つまり先頭のG2セルから探して、最初に見つかったセルの値が返ります。
以上です。
実は提示の数式ではうまくいかなかった･･･ ということではないことを祈ります。 （笑） 2018/03/31(土) 09:48 ちょこっと追記10:24
＞【Sheet1の$G2から$N2の範囲内のセルに含まれる全角 ［ を半角 [ に置換したもの】を範囲として指定しているということになるのでしょうか
$G2:$N2 は範囲ですが、それに処理を施したもの(値化したもの）はもう範囲とは呼べません。 値化した(配列)データが、普通入力で正常処理されるかどうかは誰も予測できません。
INDEX関数は、範囲以外に配列も処理対象にしています。 配列数式として入力しないで済まそうとする時、よく使われます。 多分、配列を受け取ることも想定している関数だからでしょうね。 ・・けど、複雑な関数の組合せになると、必ず成功するとも言えません(念の為)。
行 _A_ ___B___ 1 1 2 2 3 4 4 7 B4セル =SUM(A1:A3) 範囲 普通 入力
5 #VALUE! B5セル =SUM(A1:A3*1) 値化 普通 入力 6 7 B6セル =SUM(A1:A3*1) 〃 C+S+E 入力
7 7 B7セル =SUM(INDEX(A1:A3*1,0)) 〃 普通 入力 INDEX使用 8 7 B8セル =INDEX(SUM(A1:A3*1),0) 〃 普通 入力 INDEX使用(この現象を説明出来る人は居ない)
9 7 B9セル =SUMPRODUCT(A1:A3*1) 〃 普通 入力 配列を初めから想定した関数
（半平太） 2018/03/31(土) 10:55
くどいかもしれませんが、別の言い方でもう少し説明すると･･･
Sheet1の3行目が空いているとして、 Sheet1のG3に =ASC(G2) と入れて、N3までコピー
全角の［ も含めて、3行目は半角に変換されます。 MATCH関数は3行目を検索、INDEX関数の範囲は2行目にする。
=IFERROR(INDEX(Sheet1!G2:N2,MATCH("*[*",Sheet1!G3:N3,0)),"")
~~~~~ ~~~~~
最初に回答した式は、これと同じことを、別の行を使わずに一つの数式でやっているということです。
参考まで （笑） 2018/03/31(土) 11:24
ご丁寧に２度にわたるご回答をありがとうございます。
＞回答した数式は希望通りの結果になったんですか？
希望通りの結果になりました。結果をご報告していませんでしたですね。失礼しました。投稿文を下書きした時には結果のご報告も入れていたのですが、何回か書き直しているうちにその部分を削除してしまっていたみたいです。
＞数式バー上で波線部をマウスでドラッグしたあと、F9キーを押してみてください。
＞配列の結果を確認することができます。
INDEX(ASC(Sheet1!G2:N2),0) という関数は、F9キーを押して確認出来た結果を配列として一々書く代わりに ASC(Sheet1!G2:N2) と表したものだという理解でよろしいのでしょうか。
＞＞特に何の処理せず今のままで大丈夫ということになりますでしょうか。
＞「今のままで」とは？
すみません。質問の意味が不明瞭でしたですね。お尋ねしたかったのは、エクセルに先頭のG2セルから探して最初に見つかったセルの値（必ず最初のもの）を返して欲しい場合、エクセル君は常に先頭のセルから探すヤツだから「必ず最初のものを返せよ」と特別に指示しなくても（それ用の関数等を入れておかなくても）、常に最初に見つかったセルの値を返してくれるのだ（エクセル君とはそういうヤツなんだヨ）という理解でよいのかどうかということでした。頂いたご回答をこのように理解しましたが、それでよろしかったでしょうか？
（て〜んつてんて〜ん） 2018/03/31(土) 11:59
早速にありがとうございます。
30日の22:58に頂いたご回答の数式について、その結果をご報告していませんでしたですね。失礼しました。試してみたところ、希望どおりの結果が得られました。
本日の10:55に頂いた回答を次のように理解しました。このような理解でよろしいでしょうか？
１．INDEX関数は INDEX(配列，行番号，列番号) のような配列形式で書くことも出来る。
２．INDEX(SUBSTITUTE(Sheet1!$G2:$N2,"［","["),0) という関数は、この配列形式で書いたものである。
３．この場合、【SUBSTITUTE(Sheet1!$G2:$N2,"［","[")】の部分が INDEX(配列，行番号，列番号) の「配列」の部分に該当する。
４．検索範囲が１行であるため、行番号は省略されている。
５．列番号は 0 。
如何でしょうか。
（て〜んつてんて〜ん） 2018/03/31(土) 12:09
＞如何でしょうか。
理屈としてはＯＫです。
現実には、C+S+E 入力にして簡潔に書くか、普通入力にするため冗長に書くか、の選択の問題です。
それぞれ一長一短がありますが、後者が用いられる理由の一つとして、 質問者が配列数式の入力を知らないか、慣れていない為、 「正しい結果が出ない」と言ってくる面倒を避ける為、が有ります。
私のレスはこれが最後です。
（半平太） 2018/03/31(土) 12:56
何度もご回答を頂き、大変ありがとうございました。勉強になりました。
（て〜んつてんて〜ん） 2018/03/31(土) 13:10
＞INDEX(ASC(Sheet1!G2:N2),0) という関数は、F9キーを押して確認出来た結果を配列として一々書く ＞代わりに ASC(Sheet1!G2:N2) と表したものだという理解でよろしいのでしょうか。
意味がよくわからないんですけど ↓ の式は試してくれましたか？
＞=IFERROR(INDEX(Sheet1!G2:N2,MATCH("*[*",ASC(Sheet1!G2:N2),0)),"")
Ctrl＋Shift＋Enter（CtrlキーとShiftキーを押しながらEnter）で確定
説明が悪かったかもしれませんが、配列を返すだけなら ASC(Sheet1!G2:N2) だけでいいです。 INDEX関数を使っているのは、普通にEnterだけで確定できるように、です。
■お尋ね その２について
あくまでこの質問の事例に限って、MATCH関数を使った式について回答したんですが、 そんなシバリは一切なしの話ですか？
だったら個々の関数の仕様による、としか言えませんね。
MATCH関数に限っても、左から探して最初の位置を返すと言えるのは、第3引数の「照合の型」が「0」の場合です。
逆に最後の値を返すことも、そのものズバリの関数はありませんが、数式を工夫すれば可能です。
以上です （笑） 2018/03/31(土) 14:02
何度もお付き合いを頂き、恐縮です。感謝に耐えません。
＞意味がよくわからないんですけど ↓ の式は試してくれましたか？
＞=IFERROR(INDEX(Sheet1!G2:N2,MATCH("*[*",ASC(Sheet1!G2:N2),0)),"")
＞Ctrl＋Shift＋Enter（CtrlキーとShiftキーを押しながらEnter）で確定
=IFERROR(INDEX(Sheet1!G2:N2,MATCH("*[*",ASC(Sheet1!G2:N2),0)),"")も試してはみたのですが、数式を入れてCtrlキーとShiftキーを押しながらEnterすると、計算式が{ }で囲まれますが、セルには何も返って来ませんでした（空欄のまま）。ネットで検索してみたところ、計算式が{ }で囲まれるところまでは合っているように思うのですが、何かやり方が間違っているのでしょうね。「配列数式」についてもう少し勉強してみます。
＞■お尋ね その２について
＞だったら個々の関数の仕様による、としか言えませんね。
＞ MATCH関数に限っても、左から探して最初の位置を返すと言えるのは、第3引数の「照合の型」が「0」の場合です。
よく理解出来ました。ありがとうございました。
（て〜んつてんて〜ん） 2018/03/31(土) 15:08
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.