![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.6
- 回答日時:
No.4、5です。
MS365の環境で数式を作成してしまい、「配列数式にする」という説明を漏らしていました。
MS365および最新バージョン以外では配列数式にする必要があります。
前回回答のC1セルに記述する数式
=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($1:$100)/(MMULT(IFERROR(FIND(MID($B1&REPT(" ",2),COLUMN(INDIRECT("R1C1:R1C"&LEN($B1),0)),2),$A$1:$A$100),0),(ROW(INDIRECT("1:"&LEN($B1)))>0)*1)>0),COLUMN(A:A))),"")
は「CTRL+SHIFT+ENTER」で確定してください。
No.5
- 回答日時:
No.4です。
大変失礼しました。前回回答の数式(b)において、終端処理を漏らしていました。前回添付画像の例では問題ないのですが、問題が発生するケースがあります。
添付画像①をご覧ください。最低2文字一致ということは左から開始位置をずらして、2文字づつ抽出するとすれば、添付画像①のようにB2セルの右端に「関東」の文字がある場合、開始位置をずらして右端に到達すると、MID関数で2文字抽出しようとしても1文字しか抽出されず、しかもERRORにもなりません。
従って、「東」の一次が抽出され、これと一致する「東北」が抽出されてしまいます。
つまり、文字列の右終端では1文字にならないよう文字数を確保する処理しなければいけません。これを漏らしていたため、上記のような問題が発生してしまうわけです。
そこで、数式(b)を以下のように訂正します。添付画像➁をご覧ください。ご迷惑をおかけいたしました。
=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($1:$100)/(MMULT(IFERROR(FIND(MID($B1&REPT(" ",2),COLUMN(INDIRECT("R1C1:R1C"&LEN($B1),0)),2),$A$1:$A$100),0),(ROW(INDIRECT("1:"&LEN($B1)))>0)*1)>0),COLUMN(A:A))),"")
この数式で一致文字数を増やすのであれば、上記数式の「~REPT(" ",2)~」「~LEN($B1),0)),2~」の「2」の部分をお好みの数値に変更してください。
![「Excelでセル同士のうちで、一部でも合」の回答画像5](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/5/142736_61d69c2b1a610/M.png)
No.4
- 回答日時:
fujillinさんがユーザー定義関数での回答をお示しになっていますが、fujillinさんの関数は説明にあるとおり、
>最初に見つかった値を返します
ということなので、「全てを返す」を組み込み関数で考えてみました。
前提条件として、データA列、B列とも100行以下とします。
添付画像①をごらんください。C1セルに
=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($1:$100)/(MMULT(IFERROR(FIND(MID($B1,COLUMN(INDIRECT("R1C1:R1C"&LEN($B1),0)),1),$A$1:$A$100),0),(ROW(INDIRECT("1:"&LEN($B1)))>0)*1)>0),COLUMN(A:A))),"")・・・・・・(a)
という数式を記述し、C1~E5にコピーしています。
B1セルを例にとると、B1セルの一部(最低1文字)を含むA列のセルを全てC1セルから右方向へ表示します。
しかし、やってみると判りますが、最低1文字、つまりfujillinさんのユーザー定義関数でいうと、デフォルト状態で使用した場合、意図しないものが抽出されるのではないかという懸念がわきました。
添付画像①をみると、「東北」を含むものを探す意図なのに最低1文字にしたため、D1セルには「東」が一致する「関東」も抽出されてしまいます。
また、「関西」を含むものを探す意図なのに、同様にD2セルには「関」が一致する「関東」も抽出されてしまいます。
さらに、「中国」を含むものを探す意図なのに、同様にC4セルには「国」が一致する「四国」も抽出されてしまいます。
という訳で最低1文字は現実的でないと判断し、最低2文字の数式に修正したものが添付画像➁です。C1セルに、
=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($1:$100)/(MMULT(IFERROR(FIND(MID($B1,COLUMN(INDIRECT("R1C1:R1C"&LEN($B1),0)),2),$A$1:$A$100),0),(ROW(INDIRECT("1:"&LEN($B1)))>0)*1)>0),COLUMN(A:A))),"")・・・・・・(b)
という数式を記述し、C1~E5にコピーしています。
コピー範囲は右方向、下方向とも「空白」が表示されるまでオートフィルでコピーすれば良いと思います。
これで、ご質問者のご希望の状態になるのではないかと思います。
数式(a)と数式(b)を比較いただければ分かると思いますが、一致文字数を増やす場合は(a)の「1」が(b)で「2」となっている部分をお好みの数値に変更してください。
![「Excelでセル同士のうちで、一部でも合」の回答画像4](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/9/142736_61d68329961d7/M.png)
No.3
- 回答日時:
私の備忘録の中の一つの、どこかのサイトで、どなたかの昔の回答を応用したつもりなのですが、該当式は私には難解過ぎて上手に解説できません。
そういう次第なので、応用した数式を呈示するだけです。m(_._)m
貴方の希望通りの結果になるかどうか検証してみてください。
うまく動作しなかったら、その旨をお知らせください。m(_._)m
E2: =INDEX(検索文字,MATCH(TRUE,ISNUMBER(FIND(検索文字,A2)),0))
C2: =INDEX(A:A,MATCH(INDEX(検索文字,MATCH(TRUE,ISNUMBER(FIND(検索文字,B2)),0)),E:E,0))
【お断わり】上式は必何れも配列(CSE)数式として入力のこと
式を入力したら、Ctrl+Shift+Enterで確定操作をする数式です。
![「Excelでセル同士のうちで、一部でも合」の回答画像3](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/6/298588_61d58ee476704/M.jpg)
No.2
- 回答日時:
こんにちは
組み込み関数でやるとかなり面倒そうなので、ユーザ定義関数で処理する一例です。
使い方は、以下のようになります。
=PartialMatch(検索文字列, 対象セル範囲 [, 最低文字数])
※ ご提示の内容であれば、C1セルに
=PartialMatch(B1, A$1:A$10)
などと入力し、下方にフィルコピーすれば良いでしょう。
「対象セル範囲」の中から、「検索文字列」と部分一致する値を探し、最初に見つかった値を返します。
「最低文字数」は、連続するn文字以上のマッチを探すことを意味します。
(デフォルトは1です)
※ 事前準備として以下のコードを標準モジュールにコピペしてください。
Function PartialMatch(ByVal S0 As String, ByRef R As Range, Optional n As Long = 1) As String
Dim V0, v
Dim s() As String, i As Long
PartialMatch = ""
If n < 1 Or Len(S0) < n Then Exit Function
ReDim s(1 To Len(S0) - n + 1)
For i = 1 To UBound(s)
s(i) = Mid(S0, i, n)
Next i
V0 = R.Value
For Each v In V0
For i = 1 To UBound(s)
If InStr(v, s(i)) > 0 Then
PartialMatch = v
Exit Function
End If
Next i
Next v
End Function
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
あなたが好きな本屋さんを教えてください
どのくらいの規模間で、どのような本が並んでいるか、どのような雰囲気なのかなどなど...
-
2つの列を照合させてあいまい検索をしたいのですが
Excel(エクセル)
-
EXCEL) 二つの列を照合して、あいまい検索をしたい
Excel(エクセル)
-
EXCEL関数で部分一致同士を検索する方法
Excel(エクセル)
-
-
4
エクセルの部分一致の抽出について(2つのセルの共通部分の抽出)
Excel(エクセル)
-
5
Excelで[表1]にあって、[表2]にないものを抽出する関数
その他(Microsoft Office)
-
6
【エクセル】関数で「A1が0でないならB1を表示」の式
その他(コンピューター・テクノロジー)
-
7
エクセルで比較 かつ 部分一致について
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・「I love you」 をかっこよく翻訳してみてください
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・昔のあなたへのアドバイス
- ・かっこよく答えてください!!
- ・あなたが好きな本屋さんを教えてください
- ・スタッフと宿泊客が全員斜め上を行くホテルのレビュー
- ・【大喜利】【投稿~8/27】 こんなガソリンスタンド二度と来るか!なぜそう思った?
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・【お題】動物のキャッチフレーズ
- ・【お題】甲子園での思い出の残し方
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・「それ、メッセージ花火でわざわざ伝えること?」
- ・自分用のお土産
- ・人生で一番お金がなかったとき
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・ちょっと先の未来クイズ第1問
- ・ゴリラ向け動画サイト「ウホウホ動画」にありがちなこと
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
複数の文字列のいずれかが含ま...
-
エクセルでセルの値分の個数の...
-
時間を「昼間」と「夜間」に分...
-
エクセルで勝敗の合計を出したい
-
Excelの入力規則で2列表示したい
-
列の数字に100をかけたい
-
SUMに含まれる範囲から特定のセ...
-
数式が入ったセルを含めて、数...
-
エクセルで1列全部10倍したい
-
エクセルで、毎日の走行距離(...
-
エクセルで縦一列を縦三列に並...
-
Excel 平均値負の値を表示したい
-
エクセル、連勝、連敗をカウン...
-
エクセルで公平にチーム分けす...
-
「エクセル」特定の行(セル)...
-
複数のexcelファイル 同一セル...
-
エクセルのVLOOKUP関数について
-
ナンプレ(数独)で解けない問題...
-
エクセル:文字列+全角数字が入...
-
エクセルで表示されている数字...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
複数の文字列のいずれかが含ま...
-
Excelの入力規則で2列表示したい
-
数式が入ったセルを含めて、数...
-
SUMに含まれる範囲から特定のセ...
-
Excel上でのデータ数字が連番で...
-
列の数字に100をかけたい
-
スペースとスペースの間の文字...
-
エクセル:横長の表を改行して...
-
エクセルで表示されている数字...
-
エクセルでセルの値分の個数の...
-
エクセルで1列全部10倍したい
-
別のセルに値が入力されたら、...
-
HYPERLINKとADDRESSとMATCHの組...
-
エクセルで、毎日の走行距離(...
-
Excelのセル内文字の並び替えに...
-
エクセル セル内の文字数を超え...
-
エクセルのsumifでかけ算してか...
-
エクセルで小数を含む数値の抽出
-
時間を「昼間」と「夜間」に分...
-
EXCELの特定セルを編集不可・コ...
おすすめ情報