EXCEL2010で一つのワークシート上に様々な文字列が散らばっているとします。
この中でたとえばAAAという文字列が何個あるかを抽出してAAA 1、またBBBという文字列が何個あるかを抽出してBBB 3というように表示させたいのですが、方法をご存じの方、よろしくお願いいたします。VBAを使わずに解決して下さると嬉しいです。
ちなみに、COUNTIFなどの関数は、あらかじめ探したい文字列が分かっている場合には使えますが、本件はそうではなく、ワークシート上にどんな文字列があるのか一目ではわからないくらいたくさんあります。
理想は、その文字列が入っている全てのセルを範囲選択しておいて、何か関数で抽出させ、
AAA 3
ABC 19
BBB 21
DCF 3
などといったリストが作成されることです。
よろしくお願いいたします。
No.6ベストアンサー
- 回答日時:
実際のブックを操作するので、コピーしたブックを使用してください。
Sheet1にたくさんの文字列が散らばっているとします。Sheet2で集計します。
添付図を見てもらえば、何となくわかるかもしれません。
1.データが入力された範囲をセルA1から大まかに選択します。
ファンクションキーF5>セル選択>空白セル>OK
選択された空白セルを右クリック>削除>左方向にシフト>OK
2.再度データが入力された範囲をセルA1から大まかに選択します。上の処理で狭くなっているはずです。
ファンクションキーF5>セル選択>空白セル>OK
選択された空白セルを右クリック>削除>上方向にシフト>OK
この2つの操作で、広範囲に散らばった文字列をセルA1を起点に集めます。ここで、集めた「データの行数」を覚えておきます。添付図では17行です。
3.Sheet2のセルA1に「まとめ」と入力し、黄色く塗っておきます。このセルをExcelに表題だと自動判定させるためです。
4.Sheet2のセルA2に
=OFFSET(Sheet1!$A$1,MOD((ROW()-2),17),INT((ROW()-2)/17))&""
と入力します。この算式の2カ所の「17」が覚えていた「データの行数」です。覚えていた数値に変更してください。この式を多目に下にコピーします。これで矩形のデータを1列にしています。
5.算式のままだと操作できないので、算式をすべてコピーして、同じ箇所に値貼り付けします。
算式は消えてしまうので、1つ、邪魔にならない横のセルにコピーしておくといいでしょう。
6.値貼り付けしたA列を逆順で並べ替えます。これで空白セルが下に行きます。
7.A列の空白セルを除いた文字列をすべて選んで、昇順に並べ替えします。もう少しです。
8.A列の任意のセルを選択(クリック)した状態で、データタブ>小計をクリックしOK。
9.アウトラインの[2]をクリックして終了。(A列を少し広くします)
書くと長いですが、操作すると簡単なはずです。質問に「その文字列が入っている全てのセルを範囲選択しておいて」とあるので、入力してある範囲はおおよそ(大きめに)分かるとしています。
ありがとうございました。矩形のデータを一列にするというところが本当に助かりました。(ただ、どうしてこの式でそのような変換ができるのか、私のアタマでは難しすぎて理解できていませんが(^_^;)助かりました!
No.5
- 回答日時:
返信や補足回答がないようなので、参考意見を述べたいと思います。
質問内容があいまいなため、具体的な回答がしにくいのですが、目的によっては非常に簡単な処理で(関数を使わないでも)ご希望の集計を行うことができる場合もあります。
たとえば、文字列というのが英単語で、セルに英文が入力されている(すなわちスペースで単語が区切られている)ような場合は、簡単な処理で単語を分割し、それらのデータの重複をなくして集計することができます(例:英単語の出現頻度を出したい場合)。
皆さんから的確な回答を得たいなら、もう少し具体的に、どのセルにどのようなデータが入力されていて、どのような目的で分割したいのか補足説明されたほうが良いと思います。
No.4
- 回答日時:
こんばんは!
一例です。
実際のレイアウトが判らないのでやり方だけ・・・
↓の画像で左側が元データの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
- 回答日時:
複雑で計算負荷の高い数式を使って重複のない文字の一覧を作成し、この文字の個数をカウントすることができますが、ピボットテーブルを使用すればご希望の集計が一発でできます。
挿入タブのピボットテーブルで、文字列の入ったセルの項目名を行ラベルとΣ値に配置すればご希望の集計になっています。
どうしても関数で表示したいなら、数式例を提示しますので、具体的なリストのレイアウトをお知らせください。
この回答への補足
いろいろ説明不足で申し訳ありません。リストのレイアウトはどうでもいいです。皆様、本当に申し訳ありません。エクセルのシート上に二次元に広がっている各セルの値をリストアップできればそれでいいです。
補足日時:2014/07/16 13:04No.1
- 回答日時:
>ワークシート上にどんな文字列があるのか一目ではわからないくらいたくさんあります。
新しいシート作って、新しいシートのA1に
="「"Sheet1!A1 & "」は" & COUNTIF(Sheet1!$A$1:$Z$100,Sheet1!A1) & "個"
って入力して、その入力したA1セルをコピーして、A1~Z100にペーストする。
これで、シート1のA1~Z100に入ってる単語がすべてカウントされる。
範囲を変えたい場合は、適当に変えれば良い。
これで文字列と個数が出るから、全体をコピーして、形式を指定して貼り付けで「値」にして、同じ場所に貼り付けする。
値にして張り付ければ、移動しても大丈夫なので、縦1列になるようにカット&ペーストして、ソートすればよい。
ソートすると
「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個
とかって状態になるから、重複している行を削除する。
重複する行を削除するには「1つ上と同じか?」ってのをIF関数で書いて、オートフィルタかければ重複しているのだけ出せるので、フィルタかけて行削除すればよい。
削除後にフィルタを解除すると
「AAA」が3個
「AAB」が12個
「AAC」が2個
「AAD」が1個
「AAE」が4個
っていう結果が出来上がる。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excel VBAでAA(BBB) → BBB.AA に置換したい 2 2022/10/30 13:59
- その他(データベース) カラム上の重複を削除するクエリを教えてください 3 2022/04/12 14:11
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Visual Basic(VBA) 【VBA】特定の文字で改行(次の行)に行きたい。 3 2022/04/11 17:20
- Excel(エクセル) Excel>マクロ>特定のセルで同じ情報が登録されている行を1行にまとめたい(文字連結) 6 2023/01/05 16:30
- Excel(エクセル) Excelの文字列を数字に変換する方法について 6 2023/07/31 21:18
- Excel(エクセル) エクセルの条件付き書式で*を使いたい 4 2022/05/13 16:49
- Excel(エクセル) Excelの空文字判定について 7 2023/01/06 13:25
- Visual Basic(VBA) EXCEL VBA 単語置き換え について質問です ブック名 ぶぶぶ シート名 ししし セル V3〜 3 2023/03/08 01:41
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル 文字を増やしたい。
-
エクセルの計算
-
セルの内容表示が邪魔になる
-
Excel
-
Microsoft365に変えたのですが...
-
エクセル:一覧表に存在する文...
-
エクセルで日付を数字+アルフ...
-
エクセルでの作業計算方法について
-
エクセルで年休を管理する方法...
-
はがきについて。
-
【マクロ】その時、その時で変...
-
excelの不要な行の削除ができな...
-
Microsoft1Officeの互換ソフト...
-
エクセル関数を教えてください
-
Excel ピボットテーブルで日付...
-
【マクロ】読取専用のファイル...
-
【関数】適切な文字数の数字を...
-
時間によってファイル名が変わ...
-
ある列、或いは、ある行のセル...
-
UNIQUE関数が使えないバージョ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報