Excel関数を使って、対象文字が検索文字列でｎ番目に出現した位置を知りたい

質問

どうもお世話になります。
Excel関数を使って以下の検索を行いたいのですが、可能でしょうか。
例)以下のような表があったとします。
　A　B　C　D　E　F　G
1　0　0　1　1　0　0　0
2　1　1　0　1　0　0　0
⇒○(例えば1)行目を検索し、□(例えば0)が△(例えば3)回目に出てくる位置を返す。
【答え】E1

表の中の文字列は、数字でなく色々な文字(ABとか)を使ってもいいです。
OracleのInstr関数のような使い方と言えばよいでしょうか。
できればマクロは使いたくありません。
Lookup、match、index関数など色々試しましたがダメでした。
ご教授のほどよろしくお願いします。

回答No.6

Wendy02 · ベストアンサー

こんにちは。Wendy02 です。

H1 を規定セルとするなら、このようになります。

=ADDRESS([行数],MATCH([何番目],INDEX(COUNTIF(OFFSET($H$1,[行数]-1,,,COLUMN($A$1:$G$1)),[検索値]),,),0),4)

このような仕組みになっています。今の段階では、一行の検索しか出来ません。
COLUMN($A$1:$G$1)の部分は、ダミーで数値だけを取り出すために行われていますので、基本的には変更しません。

>例えばB5からH5のような範囲です。

範囲というのは、どのように数えていったらよいのか、今は、考えておりません。
したがって、[行数]は、1行目、2行目というように入れていきます。
序数というのは、[何番目]ということです。

OFFSET は、元の式は、$A$1 を規定セルとして、規定行、規定列を0としますので、1を入れると、2行目になってしまいます。したがって、[行数-1] となっています。

COUNTIFで、左から、[検索値] を1つずつ数えていくと、数が加算されていきます。その加算された最初の数を、MATCH関数で取り出し、それは、何列目か、という考え方をしています。

これで、お分かりになられましたでしょうか？

お礼

本当にご丁寧に教えて頂きまして有難うございました。
教えて頂いた内容で実現できました！
COLUMN($A$1:$G$1)の部分を検索開始位置に合わせて変えてしまうとダメなんですね。
恐らくVBAでやればもっと便利な関数やアルゴリズムを使って簡単にできると思いますが、今回はあえて関数のみで複雑な処理をしてみようと思い質問させて頂きました。
当初は、割とありがちなケースと思い、もっと簡単にできると思いきや、結果的にとても苦労してしまいました。アドバイス頂きました方にはこの場を持って御礼申し上げます。

有難うございました。

回答No.5

imogasi

#3です。補足ありがとうございました。
Ｂ１：Ｇ１で、０が３回目に現れる列は
例データＢ１：Ｇ１
0 0 1 0 5 6
=MIN(IF(COUNTIF(OFFSET($B$1,0,0,1,COLUMN(B1:G1)-1),0)=3,COLUMN(B1:G1),""))
と入れて、ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲを３つ押す（配列数式）
結果
５（Ｅ列）
Ｂ１を起点に範囲を１列づつＢ１－＞Ｃ１－＞Ｄ１・・と広げて、それぞれの列で、ＣＯＵＮＴＩＦで０が３になる列をもとめ、そのうちの最左列を採る、
ということを式にしています。

お礼

ご回答有難うございました。

頂いた内容を試しました。他の方の回答にも書きましたが、実は基準値($A$1)が可変でして、恐らく私のやり方がまずいと思いますが、実現できませんでした。ただ、回答にある配列数式というものを初めて知ることができました。これはこれでいろいろと応用できそうなテクニック(常識なんだと思いますが)ですね。ホント奥深いです。
どうも有難うございました。

回答No.4

Wendy02

こんにちは。#2 の回答者です。

#N/Aということは、たぶん、

I1：1　（行数）
I2: 3　（序数）
I3：0　（検索値）文字列も可

の意味が分からなかったからだと思います。
サンプルどおりの範囲で、以下を貼り付けてください。

=ADDRESS(1,MATCH(3,INDEX(COUNTIF(OFFSET($A$1,I1-1,,,COLUMN($A$1:$G$1)),0),,),0),4)

なお、

OFFSET($A$1,I1-1,,,

の$A$1 は、範囲の左端上の位置。

COLUMN($A$1:$G$1)
は、位置が変っても、その部分は、A1からの位置関係の長さになります。

お礼

Wendy02様
どうも有難うございます。
おっしゃるとおり、自分が示したサンプルデータで試したところ、
思うような検索ができました！検索できた瞬間感動しました。有難うございます。

ただ・・・すみません、実際には検索開始位置が$A$1ではないのです。
例えばB5からH5のような範囲です。
教えて頂いた公式を元に基準位置を直したりしましたが、ダメでした。
関数の内容も精一杯追ってみたのですが。。
MATCH関数の検査値に序数(I2)を指定しているところが理解できなく、このあたりで詰まっているのかなという気がします。

回答No.3

imogasi

＞対象文字が検索文字列でｎ番目に出現した位
文字桁番目数ならＦｉｎｄ、Ｓｅａｒｃｈなどで解決する。
Ｉｎｓｔｒと似ている。
＞⇒○(例えば1)行目を検索し、□(例えば0)が△(例えば3)回目に出てくる位置を返す。
の意味がわからない。
第１行目で０が出てくるセルの列番号は、単純にＭＡＴＣＨ関数で
できるのでは。Ａ列に出てきているのでＡ１か１列ではないの？
＞【答え】E1　　とは？質問例では、Ａ列ではないの？
＞数字でなく色々な文字(ABとか)を使ってもいいです
「いいです」ではなくーー＞「場合もあります。」でしょう。
＞OracleのInstr関数のような使い方と言えばよいでしょうか
オラクルの使用者＜＜エクセルの使用者だと思うので、例が適当でないと思う。普通は特殊例をあげて一般例を想起に使わない。
http://oracle.se-free.com/dml/05_instr.html
などに夜と、ＶＢのＩｎｓｔｒと同じで、文字列の中での話しのようですね。
本質問は「行目を検索し」といっているから、セルの位置（列番号）を発見して返す質問ではないの。
この点は非常に回答を左右します。
ーー
「部分一致で文字列を含む最初のセルの列番号を探せ」ということなのですか？
ーー
例データ　列データの例
a
as
ghfg
dfg
ase
dfgr
=MIN(IF(ISERROR(FIND("fg",A1:A6)),"",ROW(A1:A6)))
と入れて、ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ（配列数式）
結果
３
行データにするとうまくいかない。この点は考えて見ます。

お礼

ご回答有難うございました。
確かに仰るとおり、位置を知りたいと言ってるのに、文字列検索とかInstr関数とか話をして質問内容が非常に意味不明でした。。申し訳ありません。
要は、対象範囲の中で、指定した値が左から数えて指定した回数登場してきたところの位置を知りたいのです。
ここで、例えば対象範囲の文字列(0とか1)を、文字列連結し、
Instr関数のように、前から数えて何番目かという値を取得することができれば、INDEX関数を使ってセルの位置を確認できるのではないかという考えを持っていました。この思いがあったので対象範囲なのか文字列なのかわからない質問をしてしまっていました。

ちなみに
＞数字でなく色々な文字(ABとか)を使ってもいいです
「いいです」ではなくーー＞「場合もあります。」でしょう。
の部分ですが、対照表の各セルに、IF関数を使って「～だったら0、～だったら1」という指定をしている為、極端な話0か1だろうが、AかBだろうが、指定しているのは自分なのでどっちでも可、なのです。説明不足でした。。

回答No.2

Wendy02

こんにちは。

あまり、マクロとワークシート関数の区分けというのは、ユーザーのスキルの問題であって、ある程度のスキルのある人から教われば、関数の解決方法も、VBA ユーザー定義関数も違い自体はありません。

ワークシート関数では、

条件を外にして検索することを想定すれば、

I1：1　（行数）
I2: 3　（序数）
I3：0　（検索値）文字列も可

=ADDRESS(I1,MATCH(I2,INDEX(COUNTIF(OFFSET($A$1,I1-1,,,COLUMN($A$1:$G$1)),I3),,),0),4)

答え： E1

なお、なければ、エラーが返ります。

お礼

ご回答有難うございます。
教えて頂いた内容を確認しましたが、#N/Aエラーが出てしまいます。
検索文字を0にしてるからかもしれません。
自分の力不足のようです。。
もう少し調査し、できるようであれば補足にて回答差し上げます。
取り急ぎお礼申し上げます。

ちなみに対照表の値ですが、0か1しか値はありませんので、
対象文字がないためにエラーが出ることはありません。
(この場合の0か1自体も、自分で指定している文字列ですので、
0か1の値に限った話ではありません)

回答No.1

noname#176215

=SMALL(IF(INDEX(A:G,○,0)=□,COLUMN(A:G)),△)
Ctrl + Shift + Enterで確定

エラー対策してません。実務に使うのなら マクロか作業列で対処すべき
でしょう。

お礼

ご回答有難うございました。
SMALL関数は使ったことがなかったのですが、
範囲の中で、大小のある値について、小さい順にみて△番目の値を
返すということですね。
事前に範囲の部分を大小ある値にしておく必要があるということだと思いますが、
その部分が分かりませんでした。。スミマセン。