Excel特定文字抽出の関数を教えてください。

質問

Excelの特定文字からはじまる文字列を抽出したいです。

aa/bb(aa/は固定ですがbbは文字数を含めて可変します)
また、aa/bbの前後にはほかの文字列(ccccやdddddなど)が入っています。
下記のような感じですが、aa/*****だけを抽出し何件あるか調べたいです。
最終的に結果のようにしたいのですが、関数かマクロを教えていただけますでしょうか？
よろしくお願いします。

・シート
A1 ccc aa/bb ddddd　　　　　B1 gada aa/fasd haadf
A2 rrrr aa/cccc nnaxxx B2 hjhafd aa/bb hfahfdha
・
・
・

・結果　件数
aa/bb 2
aa/cccc 1
aa/fasd 1

回答No.4

kagakusuki · ベストアンサー

関数と作業列を使用する方法です。
　今仮に、元データの文字列がSheet1に入力されていて、Sheet3のA～E列を作業列として使用して、Sheet2のA列に抽出した文字列を、Sheet2のB列に件数を表示させるものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(COUNTIF(OFFSET(Sheet1!$A:$A,,ROW()-ROW(A$2)),"*?"),MATCH("*?",OFFSET(Sheet1!$A:$A,,ROW()-ROW(A$2)),-1),"")

　次に、Sheet3のB1セルに次の関数を入力して下さい。

=SUM($Sheet3A$1:$A1)

　次に、Sheet3のB1セルをコピーして、Sheet3のB2セルに貼り付けて下さい。
　次に、Sheet3のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、Sheet3のC1セルに次の関数を入力して下さい。

=IF(ROW()>SUM($A:$A),"",OFFSET(Sheet1!$A$1,ROW()-1-VLOOKUP(ROW()-1,$B:$B,1),MATCH(ROW()-1,$B:$B)-1))
　次に、Sheet3のE1セルに次の関数を入力して下さい。

=IF(ISNUMBER(FIND(" aa/"," "&$C1)),IF(COUNTIF($C$1:$C1,"*"&MID($C1,FIND(" aa/"," "&$C1),FIND(" ",$C1&" ",FIND(" aa/"," "&$C1)+LEN(" aa/")-1)-FIND(" aa/"," "&$C1))&"*")=1,MID($C1,FIND(" aa/"," "&$C1),FIND(" ",$C1&" ",FIND(" aa/"," "&$C1)+LEN(" aa/")-1)-FIND(" aa/"," "&$C1)),""),"")

　次に、Sheet3のD1セルに次の関数を入力して下さい。

=IF($E1="","",COUNTIF($E:$E,"*?")-COUNTIF($E:$E,">"&$E1))

　次に、Sheet3のC1～E1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet3!$D:$D),"",VLOOKUP(ROWS($2:2),Sheet3!$D:$E,2,FALSE))

　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF($A2="","",COUNTIF(Sheet3!$C:$C,"*"&$A2&"*"))

　次に、Sheet2のA1～B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　以上です。

回答No.3

noname#204879

マクロ音痴な私の場合は、複数のワークシート範囲をピボットテーブルにするのが“関(せき)の山”。
添付図に結果だけを示しておくけど、お気に召すかどうか…
今回の「複数のワークシート範囲」は D1:E3 と F1:G3 です。
E2: =MID(A2,FIND("aa/",A2),FIND(" ",A2,FIND("aa/",A2))-FIND("aa/",A2))
F2: =MID(B2,FIND("aa/",B2),FIND(" ",B2,FIND("aa/",B2))-FIND("aa/",B2))

【備考】ピボットテーブルが複数の範囲を参照する場合、当該範囲は２行、２列以上である必要がありそうなので、止むを得ず「数」の列を設けて各セルに数値の 1 を入力しています。

回答No.2

mu2011

マクロ（ＶＢＡ）例です。
データシートをSheet1、抽出シートをSheet2としています。
因みにaa/????の前後に半角スペースが１つ有るものとしています。
Sheet1シートタブ上で右クリック→コードの表示→サンプルコード貼り付→シート上でAlt＋F8キー押下→sample実行

Sub Sample()
Dim i As Long, db, wk
Set db = CreateObject("Scripting.Dictionary")
For Each a In Sheet1.UsedRange
If InStr(a, "aa/") > 0 Then
wk = Split(a, " ")
db(wk(1)) = db(wk(1)) + 1
End If
Next
With Sheets("sheet2")
.Cells.ClearContents
.Cells(1, 1).Resize(1, 2) = Split("結果,件数", ",")
wk = db.keys
For i = 0 To UBound(wk)
.Cells(i + 2, "A") = wk(i)
.Cells(i + 2, "B") = db(wk(i))
Next
End With
End Sub

回答No.1

tom04

こんにちは！
VBAでの一例です。

Sheet1のデータをSheet2に集計するようにしています。
（1）Sheet1のデータはA・B列のみにあるとします。
（2）検索したい文字列前後は必ずスペースで区切られている
（3）Sheet2のA1セルに「結果」・B1セルに「件数」と入力済み

上記の前提条件で

Alt＋F11キー　→　画面左の「This Workbook」をダブルクリック　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long, j As Long, k As Long, myArray As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1") '←「Sheet1」の部分は実際のSheet名に！
Set ws2 = Worksheets("Sheet2") '←「Sheet2」の部分も実際のSheet名に！
k = ws2.Cells(Rows.Count, 1).End(xlUp).Row
If k > 1 Then
Range(ws2.Cells(2, 1), ws2.Cells(k, 2)).ClearContents
End If
For j = 1 To 2
For i = 1 To ws1.Cells(Rows.Count, j).End(xlUp).Row
If ws1.Cells(i, j) <> "" Then
myArray = Split(StrConv(ws1.Cells(i, j), vbNarrow), " ")
If myArray(1) Like "aa*" Then
If WorksheetFunction.CountIf(ws2.Columns(1), myArray(1)) = 0 Then
With ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = myArray(1)
.Offset(, 1) = 1
End With
Else
k = WorksheetFunction.Match(myArray(1), ws2.Columns(1), False)
ws2.Cells(k, 2) = ws2.Cells(k, 2) + 1
End If
End If
End If
Next i
Next j
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m