どうもお世話になります。
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 です。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
- 回答日時:
#3です。
補足ありがとうございました。B1:G1で、0が3回目に現れる列は
例データB1:G1
001056
=MIN(IF(COUNTIF(OFFSET($B$1,0,0,1,COLUMN(B1:G1)-1),0)=3,COLUMN(B1:G1),""))
と入れて、SHIFT+CTRL+ENTERを3つ押す(配列数式)
結果
5(E列)
B1を起点に範囲を1列づつB1->C1->D1・・と広げて、それぞれの列で、COUNTIFで0が3になる列をもとめ、そのうちの最左列を採る、
ということを式にしています。
ご回答有難うございました。
頂いた内容を試しました。他の方の回答にも書きましたが、実は基準値($A$1)が可変でして、恐らく私のやり方がまずいと思いますが、実現できませんでした。ただ、回答にある配列数式というものを初めて知ることができました。これはこれでいろいろと応用できそうなテクニック(常識なんだと思いますが)ですね。ホント奥深いです。
どうも有難うございました。
No.4
- 回答日時:
こんにちは。
#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
- 回答日時:
>対象文字が検索文字列でn番目に出現した位
文字桁番目数ならFind、Searchなどで解決する。
Instrと似ている。
>⇒○(例えば1)行目を検索し、□(例えば0)が△(例えば3)回目に出てくる位置を返す。
の意味がわからない。
第1行目で0が出てくるセルの列番号は、単純にMATCH関数で
できるのでは。A列に出てきているのでA1か1列ではないの?
>【答え】E1 とは?質問例では、A列ではないの?
>数字でなく色々な文字(ABとか)を使ってもいいです
「いいです」ではなくーー>「場合もあります。」でしょう。
>OracleのInstr関数のような使い方と言えばよいでしょうか
オラクルの使用者<<エクセルの使用者だと思うので、例が適当でないと思う。普通は特殊例をあげて一般例を想起に使わない。
http://oracle.se-free.com/dml/05_instr.html
などに夜と、VBのInstrと同じで、文字列の中での話しのようですね。
本質問は「行目を検索し」といっているから、セルの位置(列番号)を発見して返す質問ではないの。
この点は非常に回答を左右します。
ーー
「部分一致で文字列を含む最初のセルの列番号を探せ」ということなのですか?
ーー
例データ 列データの例
a
as
ghfg
dfg
ase
dfgr
=MIN(IF(ISERROR(FIND("fg",A1:A6)),"",ROW(A1:A6)))
と入れて、SHIFT+CTRL+ENTER(配列数式)
結果
3
行データにするとうまくいかない。この点は考えて見ます。
ご回答有難うございました。
確かに仰るとおり、位置を知りたいと言ってるのに、文字列検索とかInstr関数とか話をして質問内容が非常に意味不明でした。。申し訳ありません。
要は、対象範囲の中で、指定した値が左から数えて指定した回数登場してきたところの位置を知りたいのです。
ここで、例えば対象範囲の文字列(0とか1)を、文字列連結し、
Instr関数のように、前から数えて何番目かという値を取得することができれば、INDEX関数を使ってセルの位置を確認できるのではないかという考えを持っていました。この思いがあったので対象範囲なのか文字列なのかわからない質問をしてしまっていました。
ちなみに
>数字でなく色々な文字(ABとか)を使ってもいいです
「いいです」ではなくーー>「場合もあります。」でしょう。
の部分ですが、対照表の各セルに、IF関数を使って「~だったら0、~だったら1」という指定をしている為、極端な話0か1だろうが、AかBだろうが、指定しているのは自分なのでどっちでも可、なのです。説明不足でした。。
No.2
- 回答日時:
こんにちは。
あまり、マクロとワークシート関数の区分けというのは、ユーザーのスキルの問題であって、ある程度のスキルのある人から教われば、関数の解決方法も、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
- 回答日時:
=SMALL(IF(INDEX(A:G,○,0)=□,COLUMN(A:G)),△)
Ctrl + Shift + Enterで確定
エラー対策してません。実務に使うのなら マクロか作業列で対処すべき
でしょう。
ご回答有難うございました。
SMALL関数は使ったことがなかったのですが、
範囲の中で、大小のある値について、小さい順にみて△番目の値を
返すということですね。
事前に範囲の部分を大小ある値にしておく必要があるということだと思いますが、
その部分が分かりませんでした。。スミマセン。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Access レポート印刷するときに...
-
Accessレポートのチェックボッ...
-
access2021 強制終了してしまう
-
Access Error3061 パラメータが...
-
Access VBA を利用して、フォル...
-
Microsoft365にAccessってあり...
-
【Access】Dcount関数の複数条...
-
access2019の起動が遅い
-
Accessでフォームに自動入力し...
-
Accessのスプレッドシートエク...
-
実行時エラー3131 FROM 句の構...
-
Accessが強制終了する理由はな...
-
AccessVBAについて テーブルの...
-
【至急・画像あり】建物or住所...
-
AccessVBAで任意の複数リンクテ...
-
Accessのクエリの結果を、既存...
-
Vba Userformを前面に出すについて
-
アクセスのレコードをフォーム...
-
ACCESS VBA でのエラー解決の根...
-
accessデータを指定したExcel、...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access レポート印刷するときに...
-
access2021 強制終了してしまう
-
Accessのクエリで、replace関数...
-
accessでlaccdbファイルが削除...
-
【Access】Dcount関数の複数条...
-
Access Error3061 パラメータが...
-
Accessのスプレッドシートエク...
-
ACCESS VBA でのエラー解決の根...
-
Accessレポートのチェックボッ...
-
実行時エラー3131 FROM 句の構...
-
access2019の起動が遅い
-
Vba Userformを前面に出すについて
-
Microsoft365にAccessってあり...
-
教えてください! アクセスの書...
-
Access 複数条件検索の設定が上...
-
【至急・画像あり】建物or住所...
-
Access VBA [リモートサーバー...
-
access2021 レコードロックの使...
-
Access VBA を利用して、フォル...
-
Accessのリンクテーブルのパス...
おすすめ情報