同一キーワードが出たら累計をカウントしてかつその数字とキーワードを合わせた文字列をセルに入れたい


Excelの質問です。
今、Y列の22行目、55行目、79行目、97行目…と、規則性なくランダムに、それらのセルに「バナナ」という文字列が入っているとします。合計5万行以上、2000近く「バナナ」という同じ文字列がある状況です。

そして、隣のZ列はほとんど空白セルが並んでいる状態です。

この状況におきまして、Y列を1行目から見ていき、「バナナ」という単語が初めて出たら、その隣のZセルに「バナナ1」と。
2つ目の「バナナ」が出てきましたら、その隣のZセルに「バナナ2」と、カウントして既存の文字列と合わせてセルに記入していきたいのです。(Y列のセルが空白セルの場合は、隣のZ列のセルも空白セルでよいです)

今回の例ですと、Z列の22行目が「バナナ1」、55行目が「バナナ2」、79行目が「バナナ3」、97行目が「バナナ4」になります。

そういった処理を関数やマクロを活用することで効率的に出来ないでしょうか。
よろしくお願い致します。

回答の条件
  • 1人10回まで
  • 13歳以上
  • 登録:2016/08/11 00:53:24
  • 終了:2016/08/12 10:39:22

ベストアンサー

id:a-kuma3 No.1

a-kuma3回答回数4325ベストアンサー獲得回数17732016/08/11 11:40:05

ポイント1500pt

5万行もあると、計算式では重くなりそうなので、マクロの場合も。

標準モジュールに以下のコードを貼り付けて、カウントしたいシートで countup_keyword サブルーチンを実行してください。

Sub countup_keyword()
    word_col = 25       ' Y列
    count_col = 26      ' Z列
    keyword = "バナナ"
    
    last_row = Cells(Rows.Count, word_col).End(xlUp).Row

    n = 0
    For r = 2 To last_row
        If Cells(r, word_col).Value = keyword Then
            n = n + 1
            Cells(r, count_col).Value = keyword & n
        End If
    Next
    
End Sub

Y列の値が "バナナ" と一致する場合に、同じ行の Z列に "バナナ" + 出現した数を埋めていきます。

部分一致の場合には、For 文の中の If 文の判定を変えます。

    For r = 2 To last_row

'       If Cells(r, word_col).value = keyword Then      ★部分一致にするには、ここを変更します
        If InStr(Cells(r, word_col).Value, keyword) > 0 Then
            n = n + 1
            Cells(r, count_col).Value = keyword & n
        End If
    Next

完全一致でやる場合よりも、遅くなります。

そして、隣のZ列はほとんど空白セルが並んでいる状態です。
Y列のセルが空白セルの場合は、隣のZ列のセルも空白セルでよいです

この扱いがちょっと分かりませんでした。
Y列が "バナナ" ではない場合には、Z列をそのままにしてあります。
空白に置き換えた方が良いのかもとは思ったのですが、Z列に何らかの値が入っていそうなので。

ただ、こうしてしまうと、Y列の値が変わったときに、昔の Z列の値がそのまま残ってしまう、という問題が出てきます。
一発動かして Z列に値が抽出できればお役御免、ということであれば問題ないと思いますが。

id:moon-fondu

すみません、少し質問の書き方が曖昧でしたようで。
Z列はそのままで大丈夫です。
関数でも5分もかかりませんでしたし、マクロだとすごいですね、一瞬でした!
ありがとうございました(^^

2016/08/12 10:38:57
  • id:a-kuma3
    Y列のバナナが完全一致で良いならば、以下の式を Z1 に入れて、下にコピー。

    =IF(AND(COUNTIF(Y$1:Y1,"バナナ")>0,Y1="バナナ"),"バナナ"&COUNTIF(Y$1:Y1,"バナナ"),"")

    でも、5万行もあると、式だと遅いだろうなあ、と思ったりもします。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません