特定の文字が含まれるセルの場所探す

質問

特定の文字が含まれるセルの場所を探す関数はありますか
例えば　　A1　　　　B1
　　　　　宮崎良子　B35

回答No.12

kkkkkm · ベストアンサー

> 収支明細のシートのH列には１名の場合（尾﨑）も３名の場合（小川　鈴木　大江）もあります

1名だと取得できているという事ですので
下記のようにMATCHも部分一致で検索するように変更してみてください。

=IF(B5="","",IF(COUNTIF(収支明細!$H$5:$H$128,"*"&B5&"*")<>1,"×",INDIRECT(IFERROR("収支明細!" & ADDRESS(MATCH("*" & B5 & "*",収支明細!$H$5:$H$128,0)+ROW($B$4),COLUMN(D5),4),""))))

COLUMN(収支明細!D5)はCOLUMN(D5)でいけます。単にD列の列番号の「4」を取得しているだけですので。

お礼

言葉足らずですみませんでした、有難うございます COLUMN(収支明細!D4)ここだけこうしましたすみません また何かあったらよろしくお願いします助かりました、これで入力間違いがなくなります

Powered by GRATICA

回答No.11

kkkkkm

収支明細のD列参照なら

=IF(I5="","",IF(COUNTIF(収支明細!$H$5:$H$128,"*"&I5&"*")<>1,"×",INDIRECT(IFERROR("収支明細!" & ADDRESS(MATCH(I5,収支明細!$H$5:$H$128,0)+ROW($I$4),COLUMN(D1),4),""))))

補足

本当にありがとうございます
収支明細のシートのH列には１名の場合（尾﨑）も３名の場合（小川　鈴木　大江）もあります下記数式に変更し入力しましたが
=IF(B5="","",IF(COUNTIF(収支明細!$H$5:$H$128,"*"&B5&"*")<>1,"×",INDIRECT(IFERROR("収支明細!" & ADDRESS(MATCH(B5,収支明細!$H$5:$H$128,0)+ROW($B$4),COLUMN(収支明細!D5),4),""))))
１名入力の欄で日付を読んできますが、複数入力してあると（＃REF!）になります（複数入力時は１名ごとスペースを入れています）たぶん数式の（"*"&B5&"*"）の部分かと思いますが、どうかよろしくお願いいたします

回答No.10

kkkkkm

> 現状何を試してもうまくいかず

質問する方がこのフレーズをよく使うのですが、何を試したのかこちらには分からないので何を試しても駄目なら解決策はありませんよ。
ということになります。

全体像が分からないので、たとえ「これが駄目でした」と説明されてもそもそもやってることが駄目なのかやり方の問題なのかもわかりません。

> 参照したいセル番地も関数検索でもヒットしません

どのような状態で何をやったのか分からないのでこれもなんの参考にもなりません。

> 収支明細のH列を参照しているのでその手前のD列の値（日付）がほしいのですがよろしくお願いいたします

そのように一部だけ説明されても全体像が不明なので前回までのように
こうでしたらこのようなやり方がありますとしか回答ができません。

上記の説明と以前の補足での説明

> =IF(I5="","",IF(COUNTIF(収支明細!$H$6:$H$128,"*"&I5&"*")<>1,"×","○"))
> 上記の式を入れていますがこれだと（○と×)が表示されます、これで（○）の場合にそのセルの番地を表示できますか？
> 出た答えの２列前に入金日が入力されていますのでその値を参照したい

同じ事を説明してるとは思えません。

I5と同じデータが1個しかない場合にD列のデータ参照したいのでしたらMATCHでD列の該当する番地を取得して参照すればいいのではないですか。

これも想像ですが。今の式を
=IF(I5="","",IF(COUNTIF(収支明細!$H$5:$H$128,"*"&I5&"*")<>1,"×",INDIRECT(IFERROR(ADDRESS(MATCH(I5,収支明細!$H$5:$H$128,0)+ROW($I$4),COLUMN(D1),4),""))))

5行目からの式なので ROW($I$4)を始まりの行からマイナス1にしてください。I5と$H$5:$H$128の中で一個だけあったデータは完全一致で無ければ駄目です。

とりあえず、収支明細シートと町内会費シートの詳細なフォーマットとどこに何を転記するのかが分かるように説明がないと、一つの式やD列の値がとか 説明してもらっても上記の回答のような想像上での回答しかできません。

回答No.9

kkkkkm

結果を表示したいセルに
=IF(I5="","",IF(COUNTIF(収支明細!$H$6:$H$128,"*"&I5&"*")<>1,"×","○"))
の"○"のところに参照したいセル番地を書いた式を入れておけばいけそうな気もしますがどうでしょう

補足

ｋｋｋｋｋｍさん
有難うございます

現状何を試してもうまくいかず
=IF(I5="","",IF(COUNTIF(収支明細!$H$6:$H$128,"*"&I5&"*")<>1,"×","○"))
で○と×を表示させ、×の時にセルの色を変えて注意を喚起するくらいが精いっぱいです、参照したいセル番地も関数検索でもヒットしません
収支明細のH列を参照しているのでその手前のD列の値（日付）がほしいのですがよろしくお願いいたします

回答No.8

msMike

添付図参照(Excel 2019)
￣￣￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓

お礼

参考になりました、有難うございます

Powered by GRATICA

回答No.7

kkkkkm

回答No.6の式で○の番地が取得出来たら

番地が取得できた式
の
COLUMN(〇のある列がB列ならB1)
を
2列前がK列なら
COLUMN(K1)
として
=INDIRECT(番地が取得できた式)
で2列前のセルの値を参照できます。

実際の表がどのようになっているのか殆ど不明なので何とも言えませんが

VLOOKUPで
=VLOOKUP("*"& A1 & "*",A2:B20,2,FALSE)
みたいなかんじでは参照できないんですよね。

回答No.6

kkkkkm

> 上記の式を入れていますがこれだと（○と×)が表示されます、これで（○）の場合にそのセルの番地を表示できますか？

どの列に○があるのか分かりませんが
回答にある
=IFERROR(ADDRESS(MATCH("○",C2:C10,0)+1,COLUMN(B1),4),"")
をその位置に合わせて処理すればいいと思います

=IFERROR(ADDRESS(MATCH("○",〇のある列の範囲,0)+範囲が2行目からだと1を足す,COLUMN(〇のある列がB列ならB1),4),"")
で対応してください。

○はその列でそのセルだけにしかない場合です。

ADDRESS関数の仕様は検索してください。

回答No.5

kkkkkm

該当売る人のそれぞれのデータを表示するだけでしたら
Vlookup関数が使えそうな気もします。

回答No.4

kkkkkm

> IFERROR(ADDRESS(MATCH(A1,B2:B10,0)+1,COLUMN(B1),4), "")こんな感じでセル番地を表示したいのですが
> よろしくお願いします。

具体的にどうなっているのか分からないのですが・・・。
他の方への補足を読むと入力ミスをなくしたいという事のようですが
支明細シートと町内会費シートで名前のミスをなくしたいという事なのですか？
同じExcel2013を使ってますが検索を使えばどうなのでしょう。
探したい名前をCtrl+Fで検索したら該当するセルに飛ぶか一覧で表示されるので番地を表示する必要は無いと思うのですが
検索のダイアログのオプションで完全一致のチェックを外せば部分一致で探せます

回答No.3

SI299792

シート全体からの検索は不可能です。
循環参照になります。
範囲が広いとメモリーオーバー股はフリーズします。
A31 ～D40 を検索する例です。

=TEXTJOIN(",",1,REPT(ADDRESS(ROW(A31:D40),COLUMN(A31:D40),4),A31:D40=A1))
Excel2019 は、Ctrl＋Shift ＋Enter で配列数式にします。数式に{}が付きます。
https://support.microsoft.com/ja-jp/office/%E9%85%8D%E5%88%97%E6%95%B0%E5%BC%8F%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-e43e12e0-afc6-4a12-bc7f-48361075954d

Excel2016 以前だと、制限ができます。（１行のみ、１列のみ、重複禁止）
股はVBA です。（VBA なら、シート全体も可能です。最初に出てきたものか、全て表示か）
どの方法がいいか、変身していただければ作ります。

お礼

一早くから有難うございました

Powered by GRATICA

補足

早速ありがとうございます

ごめんなさい、言葉足らずでした。
私の使っているのはExcel2013でした。
もう定年して仕事していないので更新していません。町内会の会計で使用したいのですが、爺さんでも使えるようにＶＢＡは極力使わず（会計は毎年変わり、たぶん何かあったら使いこなせない）関数で作成したいと思います。
収支明細シートに振込の有った会費を納入した個人名C、振込日A、金額B（列は10～130）が各列に記載しています。それをまとめるのに町内会費シートに転記し一覧表としてまとめます、その町内会費シートに入力間違いの無い様にしたのです。
収支明細シートは入金日順に入力、町内会費シート（個人名A、振込日B、金額C）はフォームが決まっているため２度入力しています、そこでミスが発生します。
TEXTJOIN、REPT、もありません何かいい案があればお願いします。またVBAも参考にお願い致します。
ご迷惑をおかけします、よろしくお願いいたします。

回答No.2

kkkkkm

回答No.1の追加です。

作業列を作って良ければ

たとえばB2からB10まで探すとして
C2に
=IF(AND(COUNTIF($B$2:$B$10,"*" & $A$1 & "*")>0,B2<>""),"○","")
として下までコピーして
B1に
=IFERROR(ADDRESS(MATCH("○",C2:C10,0)+1,COLUMN(B1),4),"")
とすれば部分一致でも大丈夫だと思います。

補足

大変有難うございます
現在
=IF(I5="","",IF(COUNTIF(収支明細!$H$6:$H$128,"*"&I5&"*")<>1,"×","○"))
上記の式を入れていますがこれだと（○と×)が表示されます、これで（○）の場合にそのセルの番地を表示できますか？
出た答えの２列前に入金日が入力されていますのでその値を参照したいのですが、どうでしょうか！