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ベストアンサー
- 回答日時:
関数と作業列を使用する方法です。
今仮に、元データの文字列が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
- 回答日時:
マクロ音痴な私の場合は、複数のワークシート範囲をピボットテーブルにするのが“関(せき)の山”。
添付図に結果だけを示しておくけど、お気に召すかどうか…
今回の「複数のワークシート範囲」は 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))
【備考】ピボットテーブルが複数の範囲を参照する場合、当該範囲は2行、2列以上である必要がありそうなので、止むを得ず「数」の列を設けて各セルに数値の 1 を入力しています。
No.2
- 回答日時:
マクロ(VBA)例です。
データシートをSheet1、抽出シートをSheet2としています。
因みにaa/????の前後に半角スペースが1つ有るものとしています。
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
- 回答日時:
こんにちは!
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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルデーターの並び替え 5 2022/08/06 09:59
- Visual Basic(VBA) マクロを教えてください。 7 2023/06/01 19:47
- Visual Basic(VBA) Excel VBAでAA(BBB) → BBB.AA に置換したい 2 2022/10/30 13:59
- 宇宙科学・天文学・天気 反物質がほとんどなくて、ほぼ物質だけの宇宙になったのは偶然ですか? 3 2022/11/13 14:37
- 宇宙科学・天文学・天気 反物質がほとんどなくて、ほぼ物質だけの宇宙になったのは偶然ですか? 1 2022/07/04 16:02
- 電気・ガス・水道 ソーラーパネル初心者です 1 2023/01/01 13:46
- その他(プログラミング・Web制作) pythonのmap、結果の利用は1度だけ? 5 2022/06/11 12:33
- その他(ネットショッピング・通販・ECサイト) 骨伝導ヘッドセットで この二つは値段が違うだけでは同じですか? 1 2023/02/13 19:13
- 政治 福島の処理水について中国や韓国がいちゃもんをつけてくる問題って…… 9 2023/07/11 17:18
- その他(自転車) この自転車用ヘルメット、安全なヘルメットではないですか。 10 2023/04/16 07:34
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
はがきについて。
-
エクセル 文字を増やしたい。
-
セルの内容表示が邪魔になる
-
Microsoft365に変えたのですが...
-
エクセルの計算
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
Excel ピボットテーブルで日付...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
Excelのチェックボックスの使い...
-
エクセル 白黒印刷で白線を印刷...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの条件付き書式につい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報