以前似たよな質問をしましたが、少し違うので許してください。
maron--5さんから
A B C~E F G H
1 番号 家紋名 柏
2 1 いたどり 該当なし 酸漿草
3 2 ひげ丸揚羽蝶 揚羽蝶 桔梗
4 3 ほいのし 該当なし 蛇の目
5 4 むくみ稲妻 稲妻 鷹の羽
6 5 むくみ花菱 花菱 稲妻
7 揚羽蝶
8 花菱
1)H列を作業列として、ジャンル一覧を入力(別に他のシートでもいい)
2)F2の式
F2=IF(SUMPRODUCT(COUNTIF(B2,"*"&$H$1:$H$8&"*")),LOOKUP(1,0/COUNTIF(B2,"*"&$H$1:$H$8&"*"),$H$1:$H$8),"該当なし")
という式を教えていただきました。
B列に並んでいる文字の中から別の列に並んだジャンル一覧の文字が一致すればその文字をF列に抽出するという式です。
教えてほしいことはジャンルがたくさん並んでいるのですが、そのジャンルが重複した場合のことです。現在F列に抽出されたもじが出てきますが、G列に2つ重複した場合はそのジャンル名が出てくるようにできるでしょうか?たとえば「七宝に花菱」という文字がB列にあって、ジャンルには「七宝」と「花菱」がある場合ということです。そのときにF列に「七宝」G列に「花菱」と出したいのです。
maron--5さんはじめ、わかる方教えてください。
No.4ベストアンサー
- 回答日時:
こんにちは。
やっぱり、VBAでしょうね。別の質問の中は、VBAの質問もあるようですから、VBAがお分かりになるという条件で、書きました。
buf(5) は、数を増やせば、もっと入れられます。
'標準モジュール
Function CheckItems(ByVal strText As String, Items As Range, i As Integer)
Dim v As Variant
Dim j As Integer
Dim buf(5) As String '6個の種類が出せる(5+1)
If strText = "" Then CheckItems = "": Exit Function
For Each v In Items
v = Replace(v, Space(1), "", , , 1)
If InStr(1, strText, v, 1) > 0 Then
buf(j) = v
j = j + 1
End If
Next v
If buf(0) = "" Then
buf(0) = "該当なし"
End If
If i <= UBound(buf) Then
CheckItems = buf(i - 1)
Else
CheckItems = ""
End If
End Function
ワークシートの数式は、以下のようになります。
=CheckItems($B2,$H$1:$H$9,COLUMN(A$1))
被検索値, 検索項目範囲,インデックス(1-6 まで)
これを、右にドラックすれば、
=CheckItems($B2,$H$1:$H$9,COLUMN(B$1))
とインデックスが、Bになるので、2が代入されて、2番目の文字が出てきます。
結果
該当なし
揚羽蝶
該当なし
稲妻
花菱
花菱 七宝
No.3
- 回答日時:
関数での回答希望はわかりますが、式が長くなるので、ユーザー関数の定義でやってみました。
VBEの標準モジュールに
Function vl(a, b, n)
Dim cl As Range
vl = ""
k = 0
For Each cl In b
p = InStr(a, cl)
If p <> 0 Then
k = k + 1
If k = n Then
vl = cl
Else
End If
Else
End If
Next
End Function
を貼り付ける。
ーー
例データ
B16:B24
aabb
cc
aacc
cc
bb
ccaa
bbaa
xxbb
xxbbaa
E16:E18にジャンルのリスト
aa
bb
xx
F16に
=vl($B16,$E$16:$E$18,COLUMN()-5)
(=VL(注目セルジャンル表範囲,何番目)の意味です。
F列から右列に出すので、COLUMN()-5が何番目を表せる。)
と入れてF24まで式複写する。
F18:F24までの式をI18:I24まで式を複写する。
結果
F18:I24(4列、最大でジャンル4個まで出すので)
aabb
aa
bb
aa
aabb
bbxx
aabbxx
最大5までならJ列まで式を複写。
No.2
- 回答日時:
かなり力業ですが
=IF(SUMPRODUCT(1-ISERR(FIND(INDIRECT("$H$1:$H$"&COUNTA(H:H)),B2))),,"該当なし")&IF(ISERR(FIND($H$1,B2)),"",$H$1)&IF(ISERR(FIND($H$2,B2)),"",$H$2)&IF(ISERR(FIND($H$3,B2)),"",$H$3)&IF(ISERR(FIND($H$4,B2)),"",$H$4)&IF(ISERR(FIND($H$5,B2)),"",$H$5)&IF(ISERR(FIND($H$6,B2)),"",$H$6)&IF(ISERR(FIND($H$7,B2)),"",$H$7)&IF(ISERR(FIND($H$8,B2)),"",$H$8)
例えば
{"柏";"";"桔梗";"";"";"";"揚羽蝶";""}
と言ったような
文字列の配列数式の各要素を
「柏桔梗揚羽蝶」と言う風に繋ぎたいとするとき
今回の上記のような力業でなく
エレガントな方法でつなぐやり方は
残念ながら私の知る限りでは
まだ発表されていないと思います。
昨年年始に私もトライしたのですが
かなりな難問ですね。
No.1
- 回答日時:
A B C D E F G H I
1 番号 家紋名 柏
2 1 いたどり 酸漿草
3 2 ひげ丸揚羽蝶 揚羽蝶 桔梗
4 3 ほいのし 蛇の目
5 4 むくみ稲妻 稲妻 七宝
6 5 稲妻花菱と蛇の目 蛇の目 稲妻 花菱 稲妻
7 6 七宝に花菱 七宝 花菱 揚羽蝶
8 花菱
F2=IF(COLUMN(A1)>SUMPRODUCT(COUNTIF($B2,"*"&$I$1:$I$8&"*")),"",INDEX($I$1:$I$8,SMALL(INDEX(SUBSTITUTE(COUNTIF($B2,"*"&$I$1:$I$8&"*")*1,0,10^5)*ROW($I$1:$I$8),),COLUMN(A1))))
★右にH2までと下にコピー
★3種類まで可能です、もっと右までコピーすれば何種類でも可能です
★ただし、「該当なし」は表示されません
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel>マクロ>特定のセルで同じ情報が登録されている行を1行にまとめたい(文字連結) 6 2023/01/05 16:30
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
- Excel(エクセル) Excelの列から検索して該当する行を別シートに転記するVBA 2 2022/12/20 09:35
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- Excel(エクセル) Countifよりも早く重複数をカウントする方法ありますか? 18 2022/07/04 13:39
- Excel(エクセル) ある数値に対して、値を返す数式についてです 2 2022/09/13 22:06
- Excel(エクセル) Excelの数式についての質問 1 2022/10/31 15:50
- Excel(エクセル) エクセル関数について 8 2023/06/28 17:04
- Excel(エクセル) エクセル 関数について質問です。 2 2022/10/03 11:14
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
数学 Tan(θ)-1/Cos(θ)について...
-
スマートな関数を教えて下さい。
-
Excel ウインドウ枠の固定をす...
-
EXACT関数とIF関数の組み合わせ...
-
4つのパターンを表示するEXACT...
-
関数を教えて下さい。
-
Excel:一部のフォントでセルの...
-
エクセルで指定した日付、店舗...
-
エクセルでセルに「氏名を入力...
-
Excel 2019 のピボットテーブル...
-
スプレッドシートの関数VLOOKUP...
-
エクセルで日付の入ったセルの...
-
Excelファイルの「数式」タブ→...
-
excelの不要な行の削除ができな...
-
Excelのif関数で文字が見えなく...
-
各ページの1番上の表示について
-
エクセル関数に詳しい方、教え...
-
INDIRECTを使わず excelで複数...
-
Excel 2019 は、SPILL機能があ...
-
UNIQUE関数が使えないバージョ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報