VBA 別シートにカウントした結果を表示したい

質問

VBA初心者です。

シート１で指定した単語をシート２にカウントした数を表示させたいです。

シート１のA列に100種類近い単語が縦に並んでいます。（重複している）
「りんご」「バナナ」「メロン」様々な単語があるとします。

シート２にはA列を横にした「りんご」「バナナ」「メロン」が並んでいます。（重複削除している）
そのりんごなどの下に、リンゴがいくつか、バナナがいくつかをシート１からカウントした結果の数字を入れたいです。

今のところ、COUNTIFで単語を指定しているのですが、文言が増える可能性もあります。何かほかに案がありますでしょうか。

よろしくお願いいたします。

回答No.6

masnoske

悩みそうなポイントとして

・重複しない単語を抽出する方法
フィルタの詳細設定を使って重複しないリストを作成します．
シート上では同じシートにしか抽出できない仕様ですが，マクロでは別のシートにも抽出できます．
とりあえず同じシートにマクロ記録して抽出すると，以下のようなコードが記録されると思います（A列から重複しないデータをB1セル以下に書き出した場合）．

Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=True

このコードの"B1"の部分を"Sheet2!A1"にすれば Sheet2の A列に重複しないデータが抽出されます．

次に以下の操作をマクロ記録します（A2セルにはCOUNTIF関数が入力されているものとします）．

Sheet2の A1セルを選択．
Ctrl + Shift + ↓
Ctrl + C
B1セルを選択．
行列を入れ替えて貼り付け．
Sheet2の A列を削除．
B2セルを選択．
Ctrl + C
↑
Ctrl + →
↓
Ctrl + Shift + ←
Ctrl + V

この記録結果と先の抽出マクロを継ぎ合わせれば完成です．

回答No.5

SI299792

データがA2から始まっているとします。

関数で、
Sheet1
B2: =B1+(COUNTIF(A$2:A2,A2)=1)
下へコピペ。ワークエリアなので目障りなら非表示にして下さい。
Sheet2
A2: =IFERROR(INDEX(Sheet1!A:A,MATCH(ROW()-1,Sheet1!B:B,0)),"")
B2: =IF(A2>"",COUNTIF(Sheet1!A:A,A2),"")
下へコピペ。
できます。

かなり複雑ですがワークエリアを使わない方法もあります。
A2: =Sheet1!A2
A3: =IF(ROW(Sheet1!A1)>SUMPRODUCT((Sheet1!$A$2:$A$100<>"")/COUNTIF(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$100&""))-1,"",INDEX(Sheet1!$A$2:$A$100,MATCH(0,INDEX(0/ISERROR(MATCH(Sheet1!$A$2:$A$100,$A$2:A2,0)),),0)))
あまりお勧めしません。

VBA も載せておきます。
Option Explicit
'
Sub Macro1()
　　Dim Row As Long
'
　　Sheets("Sheet2").Select
　　Row = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
　　Range("A1:A" & Row) = [Sheet1!A:A].Value
　　Range("A1:A" & Row).RemoveDuplicates 1
　　Row = Cells(Rows.Count, "A").End(xlUp).Row
　　Range("B2:B" & Row) = "=COUNtIF(Sheet1!A:A,A2)"
End Sub

回答No.4

kon555

「COUNTIFで単語を指定している」という形であれば、シート関数を用いた作り方としては最適解だと思います。文言が増えてもその分カウント用の表も増やせばいいだけです。

　VBAで対応する事も可能ですが、正直あまり意味はありません。どこかでカウントしたい文言を指定してやる必要があり、現状の方法と似たような手間がかかります。
　メンテ性などを考えても、シート関数で対応すべき業務のように思います。

　これが「それぞれバラバラの項目のカウントを、１日で何十種類も行わなければいけない」とか「テキストファイルで指定される項目を1つ1つコピーしてカウント表を作っている」などであれば、この部分についてVBAで効率化を図る事はできます。
　しかし純粋にカウントするだけであれば、わざわざVBAに手を出す必要のない案件に思えます。

補足

お返事ありがとうございます。

実際はCSVファイルが1万を超え、シート数も膨大に増えるのです。
それを一つ一つやるには時間がかかると思いましたので、VBAでどのようにやればいいのだろうとの思いでした。

回答No.3

imogasi

どの点で悩んでいるか、判らないか、はっきりしない。
サンプルデータ　Ｓｈｅｅｔ１　Ａ１：Ａ７
単語
りんご
なし
バナナ
ブドウ
りんご
なし
Ｓｈｅｅｔ２のＢ１：Ｅ１
りんご ばなな ブドウ なし
が出来上がっているとする
Ｂ２セルに、式
=COUNTIF(Sheet1!$A$2:$A$1000,B$1)　
を入れて、右方向に式を複写する。
これで、勘違いや不十分な点は、どの点か？　課題は＄を付ける有無と、着ける位置ぐらいか？
全く質問に値しない課題だと思ったが、果たして？
ユニークなデータをＳｈｅｅｔ２に持ってくる・作るやり方の質問か？

例データと結果ぐらいサンプルを挙げて質問してよ。

回答No.2

m5048172715

私が昔作ったVBAのコードは、質問の例で言うとシート2の項目を、シート1から自動で作るものでしたね。

必要なステートメントや文法は、for、任意のセル読み書き、文字列判定、・・・くらいじゃないか？それらを1個1個分かっていればあとは組み合わせるだけです。

回答No.1

kkkkkm

文言が少なければ単純に

=COUNTIF(A1:A1000,"りんご")+COUNTIF(A1:A1000,"ばなな")
や
=SUMPRODUCT((A1:A1000="りんご")+(A1:A1000="ばなな"))
などでもできると思います。