EXCEL2010で同じ文字列のセルの数を数え・・

質問

EXCEL2010で一つのワークシート上に様々な文字列が散らばっているとします。
この中でたとえばAAAという文字列が何個あるかを抽出してAAA　１、またBBBという文字列が何個あるかを抽出してBBB　３というように表示させたいのですが、方法をご存じの方、よろしくお願いいたします。VBAを使わずに解決して下さると嬉しいです。
ちなみに、COUNTIFなどの関数は、あらかじめ探したい文字列が分かっている場合には使えますが、本件はそうではなく、ワークシート上にどんな文字列があるのか一目ではわからないくらいたくさんあります。
理想は、その文字列が入っている全てのセルを範囲選択しておいて、何か関数で抽出させ、
AAA　３
ABC　１９
BBB　２１
DCF　３
などといったリストが作成されることです。
よろしくお願いいたします。

回答No.6

nishi6 · ベストアンサー

実際のブックを操作するので、コピーしたブックを使用してください。
Sheet1にたくさんの文字列が散らばっているとします。Sheet2で集計します。
添付図を見てもらえば、何となくわかるかもしれません。

１．データが入力された範囲をセルA1から大まかに選択します。
　　ファンクションキーF5＞セル選択＞空白セル＞OK
　　選択された空白セルを右クリック＞削除＞左方向にシフト＞OK

２．再度データが入力された範囲をセルA1から大まかに選択します。上の処理で狭くなっているはずです。
　　ファンクションキーF5＞セル選択＞空白セル＞OK
　　選択された空白セルを右クリック＞削除＞上方向にシフト＞OK

この２つの操作で、広範囲に散らばった文字列をセルA1を起点に集めます。ここで、集めた「データの行数」を覚えておきます。添付図では17行です。

３．Sheet2のセルA1に「まとめ」と入力し、黄色く塗っておきます。このセルをExcelに表題だと自動判定させるためです。

４．Sheet2のセルA2に

　　　=OFFSET(Sheet1!$A$1,MOD((ROW()-2),17),INT((ROW()-2)/17))&""

と入力します。この算式の2カ所の「17」が覚えていた「データの行数」です。覚えていた数値に変更してください。この式を多目に下にコピーします。これで矩形のデータを１列にしています。

５．算式のままだと操作できないので、算式をすべてコピーして、同じ箇所に値貼り付けします。
　　算式は消えてしまうので、１つ、邪魔にならない横のセルにコピーしておくといいでしょう。

６．値貼り付けしたA列を逆順で並べ替えます。これで空白セルが下に行きます。

７．A列の空白セルを除いた文字列をすべて選んで、昇順に並べ替えします。もう少しです。

８．A列の任意のセルを選択（クリック）した状態で、データタブ＞小計をクリックしOK。

９．アウトラインの[2]をクリックして終了。（A列を少し広くします）

書くと長いですが、操作すると簡単なはずです。質問に「その文字列が入っている全てのセルを範囲選択しておいて」とあるので、入力してある範囲はおおよそ（大きめに）分かるとしています。

お礼

ありがとうございました。矩形のデータを一列にするというところが本当に助かりました。（ただ、どうしてこの式でそのような変換ができるのか、私のアタマでは難しすぎて理解できていませんが(^_^;)助かりました！

回答No.5

MackyNo1

返信や補足回答がないようなので、参考意見を述べたいと思います。

質問内容があいまいなため、具体的な回答がしにくいのですが、目的によっては非常に簡単な処理で（関数を使わないでも）ご希望の集計を行うことができる場合もあります。

たとえば、文字列というのが英単語で、セルに英文が入力されている（すなわちスペースで単語が区切られている）ような場合は、簡単な処理で単語を分割し、それらのデータの重複をなくして集計することができます（例：英単語の出現頻度を出したい場合）。

皆さんから的確な回答を得たいなら、もう少し具体的に、どのセルにどのようなデータが入力されていて、どのような目的で分割したいのか補足説明されたほうが良いと思います。

補足

説明不足で申し訳ありません。1つのセルには一つの文字列が入っております。例：2G058 BB07

回答No.4

tom04

こんばんは！
一例です。
実際のレイアウトが判らないのでやり方だけ・・・

↓の画像で左側が元データのSheet1、右側が表示させるSheet2とします。
Sheet1に作業用の列を設けます。
作業列B2セルに
=IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")
という数式を入れフィルハンドルでダブルクリック！

次にSheet2のA2セルに
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!B:B,ROW(A1))),"")
B2セルに
=IF(A2="","",COUNTIF(Sheet1!A:A,A2))
という数式を入れ、A2・B2セルを範囲指定　→　B2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　一つ気になるのが
＞ワークシート上に様々な文字列が散らばっているとします
です。
この文章から判断すると1Sheetにバラバラに入っているデータが
それぞれ何セル存在するか？という質問であれば関数では厄介だと思います。

仮にそうだとすると、VBAでの一例です。
Sheet1の規則性がないセルに色々なデータが存在するとして、それをSheet2に表示するとします。

Alt+F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim c As Range, r As Range, cnt As Long, wS As Worksheet
Set wS = Worksheets("Sheet1")
With Worksheets("Sheet2")
.Range("A:B").ClearContents
For Each c In wS.Cells.SpecialCells(xlCellTypeConstants)
Set r = .Range("A:A").Find(what:=c, LookIn:=xlValues, lookat:=xlWhole)
If r Is Nothing Then
cnt = cnt + 1
With .Cells(cnt, "A")
.Value = c
.Offset(, 1) = 1
End With
Else
With r.Offset(, 1)
.Value = .Value + 1
End With
End If
Next c
End With
End Sub 'この行まで

※　実データ（手入力）データという前提です。
（数式によって表示されているデータは無視されます）m(_ _)m

お礼

ありがとうございました。
エクセルは難しいですね・・・

回答No.3

MackyNo1

複雑で計算負荷の高い数式を使って重複のない文字の一覧を作成し、この文字の個数をカウントすることができますが、ピボットテーブルを使用すればご希望の集計が一発でできます。

挿入タブのピボットテーブルで、文字列の入ったセルの項目名を行ラベルとΣ値に配置すればご希望の集計になっています。

どうしても関数で表示したいなら、数式例を提示しますので、具体的なリストのレイアウトをお知らせください。

補足

いろいろ説明不足で申し訳ありません。リストのレイアウトはどうでもいいです。皆様、本当に申し訳ありません。エクセルのシート上に二次元に広がっている各セルの値をリストアップできればそれでいいです。

回答No.2

chie65536（@chie65535）

訂正

×　="「"Sheet1!A1 & "」は" & COUNTIF(Sheet1!$A$1:$Z$100,Sheet1!A1) & "個"

○　="「" & Sheet1!A1 & "」は" & COUNTIF(Sheet1!$A$1:$Z$100,Sheet1!A1) & "個"

お礼

ありがとうございました。

回答No.1

chie65536（@chie65535）

＞ワークシート上にどんな文字列があるのか一目ではわからないくらいたくさんあります。

新しいシート作って、新しいシートのA1に

="「"Sheet1!A1 & "」は" & COUNTIF(Sheet1!$A$1:$Z$100,Sheet1!A1) & "個"

って入力して、その入力したA1セルをコピーして、A1～Z100にペーストする。

これで、シート1のA1～Z100に入ってる単語がすべてカウントされる。

範囲を変えたい場合は、適当に変えれば良い。

これで文字列と個数が出るから、全体をコピーして、形式を指定して貼り付けで「値」にして、同じ場所に貼り付けする。

値にして張り付ければ、移動しても大丈夫なので、縦１列になるようにカット＆ペーストして、ソートすればよい。

ソートすると

「AAA」が3個
「AAA」が3個
「AAA」が3個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAB」が12個
「AAC」が2個
「AAC」が2個
「AAD」が1個
「AAE」が4個
「AAE」が4個
「AAE」が4個
「AAE」が4個

とかって状態になるから、重複している行を削除する。

重複する行を削除するには「１つ上と同じか？」ってのをIF関数で書いて、オートフィルタかければ重複しているのだけ出せるので、フィルタかけて行削除すればよい。

削除後にフィルタを解除すると

「AAA」が3個
「AAB」が12個
「AAC」が2個
「AAD」が1個
「AAE」が4個

っていう結果が出来上がる。