エクセルでセルに入力された複数語をそれぞれ集計したい

質問

へんな日本語ですみません。

例えばある列に

１りんご　ばなな

２りんご　みかん　

などと入力されています。
この場合、データの集計を実行すると

　りんご　ばなな　１
　りんご　みかん　１

　という結果になりますが、

　りんご　２
　ばなな　１
　みかん　１

　という結果が得られるような方法はないでしょうか。よろしくご教示ください。

回答No.5

imogasi · ベストアンサー

●問題のパターン分けに付いて
（１）１セルに語句が１つ（「りんご」だけや「バナナ」だけ）が入っていいる。
多分質問はこのケースでない。
=COUNTIF(A1:A6,"りんご")でカウントできる。
（２）１セルの中に２個所以上ある（重複あり）
多分質問はこのケース。
例　りんご　なし　りんご
（３）結合語もあり（例　焼きりんご　りんごジュース）
質問ではなにも言ってない。
多分別物と捉えるのでしょう。
（４）語句の区分
スペースとかで区切られているのか。
続いた文章のなかに現れるのか。
質問ではスペースで区切られているよう。
（５）「柿」と「かき」りんごと林檎のような表現の違いはないか。
●本質問では（２）、（４）から
データ
区切り位置
既設定の「カンマやタブ・・・を選ぶ。
（空白につられて、「スペースによって・・・」を選ばないこと。半角スペースでないので。）
「次へ」をクリック
「その他」をクリック
「その他」の右のボックスに全角１スペースを入れる
「次へ」をクリック
「完了」をクリック
で語句ごとに列が分かれる。
そこで=COUNTIF(A1:C7,"りんご")を空きのセルにいれると
４となります。
（例データ）
Ａ１に　りんご　みかん　りんご
Ａ２に　なし　バナナ
Ａ３に　バナナ　キウイ
Ａ４に　りんご　かき
Ａ５に　みかん
Ａ６に　イチゴ　りんご
Ａ７に　もも
これが（列挿入の余分なことがあるが）一番判りやすいと思う。
（注意）当初Ｂ、Ｃ・・列に別のデータがある時は
最大語数だけの、列挿入をしてから上記「くぎり位置」の操作をすること。
●（３）、（５）に対応するのはＶＢＡでプログラムが必要でしょう。

回答No.6

nishi6

１．データ→区切り位置　の機能を使って、複合語を分割し、
２．１列にまとめる
３．ソートする
４．集計機能で集計
がオーソドックスな方法でしょうか。

下に質問にあることを行うコードを書いてみました。
分離記号を『Bunri = "　"』に登録します。今は、全角空白です。
集計したいデータ範囲に『dTBL』の範囲名を付けておき、出力セル左上を１箇所指定しSyukeiを実行します。

ツール→マクロ→Visual Basic Editor でVBE画面に移り、挿入→標準モジュール で標準モジュールを挿入します。出てきたコードウインドウに下記マクロをコピーして貼り付けます。

Sub Syukei()
　　Dim dt() As String　　　　'// データ用配列
　　ReDim dt(0) As String　　 '// データ用配列とりあえず確保

　　Const Bunri = "　"　　　　'// 分離記号（実際の分離記号をセットする）　
　　Dim num As Integer　　　　'// カウンタ
　　Dim rg As Range　　　　　 '// セル

　　'// データを取り込む（データ範囲に『dTBL』の範囲名を付けておく）
　　For Each rg In Range("dTBL")
　　　　If InStr(rg, Bunri) Then
　　　　　　num = num + 1: If UBound(dt) < num Then ReDim Preserve dt(num)
　　　　　　　　dt(num) = Left(rg, InStr(rg, Bunri) - 1)　　　　　'// 左の文字を記憶
　　　　　　num = num + 1: If UBound(dt) < num Then ReDim Preserve dt(num)
　　　　　　　　dt(num) = Right(rg, Len(rg) - InStr(rg, Bunri))　 '// 右の文字を記憶
　　　　End If
　　Next

　　'// データをソート（バブルソート）
　　Dim ct1 As Integer, ct2 As Integer　　　'// カウンタ
　　Dim wk As String　　　　　　　　　　　　'// 作業用変数
　　　　For ct1 = 1 To UBound(dt) - 1
　　　　　　For ct2 = UBound(dt) To ct1 + 1 Step -1
　　　　　　　　If dt(ct2 - 1) > dt(ct2) Then
　　　　　　　　　　wk = dt(ct2 - 1): dt(ct2 - 1) = dt(ct2): dt(ct2) = wk
　　　　　　　　End If
　　　　　　Next
　　　　Next

　　'// 集計結果を出力
　　Dim writeRw As Integer　　　　'// 結果を書く行
　　Dim Total As Integer　　　　　'// 個々の集計値
　　Dim allTotal As Integer　　 　'// 個々の総計値
　　ReDim Preserve dt(num + 1)
　　dt(num + 1) = "最終"

　　For ct1 = 1 To UBound(dt) - 1
　　　　Total = Total + 1
　　　　If dt(ct1) <> dt(ct1 + 1) Then
　　　　　　ActiveCell.Offset(writeRw, 0) = dt(ct1)
　　　　　　ActiveCell.Offset(writeRw, 1) = Total
　　　　　　allTotal = allTotal + Total

　　　　　　writeRw = writeRw + 1: Total = 0
　　　　End If
　　Next
　　ActiveCell.Offset(writeRw, 0) = "総計"
　　ActiveCell.Offset(writeRw, 1) = allTotal

End Sub

お礼

みなさん、ありがとうございました。
少し自分には難しいところもありますが、とても参考になりました。

回答No.4

noname#9284

例えばA1に「りんご　ばなな　１ 」が入力されているのでしたらまた違った回答になります。

単純にA列に検索したい文字列が何個あるかを数えたいのでしたら、
まずB1から下に
りんご
ばなな
みかん
と、ありったけの検索したい文字列を入力しておきます。

C1に
=SUMPRODUCT(NOT(ISERROR(SEARCH("*"&B1&"*",$A$1:$A$17,1)))*1)
と入力し、これを下までコピーします。

しかし「りんご　ばなな　１ 」の「１」が重大な意味を持つのでしたらまた数式が違ってきます。

回答No.3

ki-aaa

例えば、
１りんご　ばなな
２りんご　みかん
が、A1:A2セルに入っているとすれば次のようになります。

="りんご　"&COUNTIF(A1:A2,"=*りんご*")
又は
=COUNTIF(A1:A2,"=*みかん*")

回答No.2

noname#9284

統合機能を使われるか、ピボットテーブルではどうでしょうか。
どちらもデータメニューにあります。
統合でしたらデータ範囲を追加、追加としていきます。
ピボットテーブルでしたら最初に「複数のワークシート」をオンにして進みます。
何かご質問がありましたらまた書き込みをお願いいたします。

回答No.1

hinebot

今ひとつ現状が把握できません。

>例えばある列に
>１りんご　ばなな
>２りんご　みかん　
>などと入力されています。

これは具体的にどのセルにどのデータが入っているのでしょうか？

>この場合、データの集計を実行すると（中略）という結果になりますが、

この集計はどうやっているのでしょうか？

お力になれるか分かりませんが、補足いただけると幸いです。

補足

回答ありがとうございます。補足しますと、

(列番号→)　　　　　A 　　　　　　　　　
(行番号↓)
１　　　　　りんご　　ばなな　　
２　　　　　りんご　　みかん

　…と、このような状態を意味しています。
　一つのセルに２語入力されている状態です。

　集計はデータを昇順に並べ替えてから、データメニューの集計を実行しています。