excel 列1000 行30の中から、重複する言葉を抽出し、重複数を数える方法

関数の知識がありません。
以下の方法で重複する言葉を調べ、重複数を自力で数えていました。
ホーム＞スタイル＞条件付き書式＞セルの強調表示ルール＞重複する値
しかし、
列15000、行33の中でから重複する言葉を探すのは、
上記のやり方では途方もない作業なります。

何かよい方法を教えてください！
1. 重複する言葉を抽出（言葉の例：りんご、みかん、栗…）
2. 重複数を数える（例：りんご　150、栗　289回のように数えたい）

質問者からの補足コメント

• 列10000以上（excelは16000まであると聞いていますが）、行33です。
  言葉はいろんな言葉です。（果物の名前、建物の名前、時事用語、英語表記などが混ざっています）

    補足日時：2022/02/05 11:17

• 

  一つのセルに一つの単語です。単語数は、推定500程度です。
  横にやたらに広い表です（列10000以上）
  単語の例：
  りんご　栗　自転車　欧州　鳥　コンピュータ　みかん　プリンター　…　　
  
  ご提案下さっている関数の作り方、分かり易く説明されているのだと思うのですが、
  私には高度過ぎて理解ができません。
  もう少し分かり易くご説明頂けると有難いです。

    補足日時：2022/02/05 18:14

• 重複している言葉と、重複していない言葉が、列1000以上×行33の表に存在します。
  重複している言葉のみを抽出、その後、重複している言葉の重複数を数えたいのです。
  
  ご提案によると、
  ホーム＞スタイル＞条件付き書式＞セルの強調表示ルール＞重複する値
  で抽出された重複している言葉を、自力で51行目から書き出す。
  
  そしてB51セルに、
  =COUNTIF($A$1:$PGB$33,A51)という関数を入力するとのことですか。
  この関数は、どういう意味ですか？

  No.8の回答に寄せられた補足コメントです。 補足日時：2022/02/05 19:26

• 

  それなら、全ての言葉の個数をカウントして、重複しているもの（個数が1よりも大きいもの）のみを絞り込む方が簡単だと思います。
  ⇒全ての言葉の個数をどうやって簡単にカウントできるのですか？自力でカウントするのですか？方法があれば、教えてください。
  
  私なら、全てのデータを縦一列に並べて重複しているものを削除し、それを#8で説明しているA51に貼り付けます。
  ⇒どうして重複しているものを削除するのですか？重複している言葉を抽出し、その言葉の重複するを数えたいのですが。。。
  
  ②B51セルに
  =COUNTIF($A$1:$PGB$33,A51)
  と入力し、その下のセルに数式をコピー。
  ⇒これは、A51からデータを縦一列に並べた場合関数だと思うのですが、
  ($A$1:$PGB$33,A51)の意味が分かりません。解説していただけますか？すみません！

    補足日時：2022/02/05 21:45

• 

  No.11さんの指示通りにやってみました。
  以下の通りになりました。
  これを見て、列B、C…それぞれに入っている数字の意味が分かりません。
  解説していただけませんか。
  できればNo.11さんの指示通りにやって、
  列１００００以上×行３３の表に入っている重複単語を抽出、そして重複単語それぞれの重複回数を数えたいです！

  
    補足日時：2022/02/06 13:32

• 

  No.11さんの提案について質問
  sheet1に20列5行のデータがあるものとします。
  ・
  ・
  ・
  B1セルからF1セルまでを下の方にコピペしていけばF列にsheet2のデータが呼び出せます。
  ＞＞いくつコピペするのですか？20列×5行＝100回コピペするのですか？
  
  次にsheet2のG1、H1、I1、J1セルにそれぞれ
  =COUNTIF($F$1:F1,F1)
  ・
  ・
  ・
  G1セルからK1セルまでを下の方にコピペしていけばK列に重複データの2個目以降
  と書いて下の方にコピペしていけばカウントできます。
  ＞＞こちらも、いくつコピペするのですか？
  
  私の場合、15000列、33行です。その場合、コピペは何回すればいいのでしょうか？
  すみません、どうしても理解できませんので、教えてください！

    補足日時：2022/02/06 16:08

• 

  No.11様
  20列、50行の表
  インストラクション通りにした結果、
  重複している言葉の抽出できました。
  それぞれ重複している言葉の重複数も分かりました！
  
  15000列、33行の表　の場合でも、
  教えて下さったものを、セルに書き込めばいいのでしょうか？
  教えていただけませんか？
  （試してみたら、エラーが出てしまいました。）

    補足日時：2022/02/06 17:46

• 

  No.11様
  20列、50行の表
  インストラクション通りにした結果、
  重複している言葉の抽出できました。
  それぞれ重複している言葉の重複数も分かりました！
  
  15000列、33行の表　の場合でも、
  教えて下さったものを、セルに書き込めばいいのでしょうか？
  教えていただけませんか？
  （試してみたら、エラーが出てしまいました。）

  No.11の回答に寄せられた補足コメントです。 補足日時：2022/02/06 17:52

• 

  No.11様
  sheet2のF1セル　　=INDEX(Sheet1!$A$1:$T$5,Sheet2!C1,Sheet2!E1)
  ＞＞$T$5の部分を、書き換えるだけでいいですね。
  
  B1セルからF1セルまでを下の方にコピペ
  ＞＞15000列×33行の表の場合、495000回コピペするんですね。
  
  =MATCH(B1,$I$1:$I$100,0)
  ＞＞最後の100,00を495000,0にすればいいんですね。
  
  sheet2のK1セルに
  =INDEX($F$1:$F$100,J1)
  ＞＞最後の100,J1を、495000,J１にすればいいんですね。
  
  G1セルからK1セルまでを下の方にコピペ・・・
  ＞＞495000回コピペすればいいですね。
  
  sheet2のM1セル
  =COUNTIF($F$1:$F$100,K1)
  ＞＞最後の100,K1を、495000,K1にするんですンえ。

    補足日時：2022/02/06 20:11

No.13 ベストアンサー 回答者： cruelman 回答日時： 2022/02/06 21:23

sheet2のF1セル　　=INDEX(Sheet1!$A$1:$T$5,Sheet2!C1,Sheet2!E1)

＞＞$T$5の部分を、書き換えるだけでいいですね。

B1セルからF1セルまでを下の方にコピペ
＞＞15000列×33行の表の場合、495000回コピペするんですね。

=MATCH(B1,$I$1:$I$100,0)
＞＞最後の100,00を495000,0にすればいいんですね。

sheet2のK1セルに
=INDEX($F$1:$F$100,J1)
＞＞最後の100,J1を、495000,J１にすればいいんですね。

G1セルからK1セルまでを下の方にコピペ・・・
＞＞495000回コピペすればいいですね。

sheet2のM1セル
=COUNTIF($F$1:$F$100,K1)
＞＞最後の100,K1を、495000,K1にするんですンえ。

上記についての回答。
基本的にはその通りです。
ただし、M列についてはもっと少なくても構わないはず。あなたの見積が正しければ。全部で500種類とのことでしたっけ。

それと４９５０００回のコピペの話ですが、ctrl+Dというショートカットキーについてお調べください。あなたにとって有益な情報が得られるものと想像します。

No.12 回答者： cruelman 回答日時： 2022/02/06 17:52

F列で使ったINDEX関数についてまず説明します。

INDEX(［検索範囲の指定］,［行の指定］,［列の指定］)
です。具体的には例えば
INDEX($A$1:$T$5,2,3)
と書けばA1セルからT5セルまでの範囲内で上から2番目左から3番目のセルの内容を拾い出すというものになります。
この関数を利用してsheet1のデータをsheet2に一列に書き出しています。
sheet2の1行目にsheet1の上から1番目左から1番目、sheet2の2行目にsheet1の上から1番目左から2番目、……、と規則的に数字を算出する作業をB列からE列にかけて行っています。

使っている関数の詳しい説明に関しては私の拙い説明よりもgoogle検索の方がよっぽど優れているかと思いますので私の方からはここまで。

No.11 回答者： cruelman 回答日時： 2022/02/06 08:29

sheet1に20列5行のデータがあるものとします。

20×5の枠内に空白セルは無く、枠外には何も書かれていないものとします。
このデータについて調べることにします。

sheet2を用意します。
sheet2のA1セルに
=COUNTA(Sheet1!1:1)
と書きます。これで調べたいデータについての列数が関数によって判明します。
次にsheet2のB1、C1、D1、E1セルにそれぞれ
=ROW()
=ROUNDDOWN((B1-1)/$A$1,0)+1
=MOD(B1,$A$1)
=IF(D1=0,$A$1,D1)
と書きます。
次にsheet2のF1セルに
=INDEX(Sheet1!$A$1:$T$5,Sheet2!C1,Sheet2!E1)
と書きます。これでsheet1のA1セルをsheet2に呼び出すことが出来ます。
B1セルからF1セルまでを下の方にコピペしていけばF列にsheet2のデータが呼び出せます。
B列からE列まではF列のindex関数を使うための前振りでした。

次にsheet2のG1、H1、I1、J1セルにそれぞれ
=COUNTIF($F$1:F1,F1)
=IF(G1=1,1,0)
=SUM($H$1:H1)
=MATCH(B1,$I$1:$I$100,0)
と書きます。
次にsheet2のK1セルに
=INDEX($F$1:$F$100,J1)
と書きます。
G1セルからK1セルまでを下の方にコピペしていけばK列に重複データの2個目以降を削除したデータが現れます。
sheet2のM1セルに
=COUNTIF($F$1:$F$100,K1)
と書いて下の方にコピペしていけばカウントできます。

必要に応じてアドレス指定を書き換えれば大きなデータにも対応できるはずです。
もっとスマートな書き方もありますがexcel初級～中級の人にお馴染みの関数のみを使うとこんな感じです。
関数を入れ子にしていけばより少ない列数で処理も可能ですがあえてしませんでした。

No.10 回答者： bari_saku 回答日時： 2022/02/05 22:28

＞全ての言葉の個数をどうやって簡単にカウントできるのですか？自力でカウントするのですか？方法があれば、教えてください。

#8および#9で説明しています。

＞どうして重複しているものを削除するのですか？重複している言葉を抽出し、その言葉の重複するを数えたいのですが。。。
#8および#9の関数の意味がわかれば、わかります。

＞($A$1:$PGB$33,A51)の意味が分かりません。解説していただけますか？すみません！
#8でA列に、表の範囲中に何個あるか数えたい単語を入力して下さいと書いてありますよね？
仮にA51セルに「りんご」と入力されていたら、表中に「りんご」と入っているセルがいくつあるのかが表示されます。

この回答へのお礼

ありがとうございました。全く関数が分かっていない人に、理解できるように説明した下さっていたのだと思います。しっかりとご指導くださったことを理解できるように、これから勉強します。

お礼日時：2022/02/06 21:35

No.9 回答者： bari_saku 回答日時： 2022/02/05 19:46

＞重複している言葉のみを抽出、その後、重複している言葉の重複数を数えたいのです。

それなら、全ての言葉の個数をカウントして、重複しているもの（個数が1よりも大きいもの）のみを絞り込む方が簡単だと思います。

私なら、全てのデータを縦一列に並べて重複しているものを削除し、それを#8で説明しているA51に貼り付けます。

↓重複データを削除し、縦一列に並べる方法

縦1列に並べる方法（自己流です）
①全てのデータをコピーし、別シート（どこでもいいんですが）A列に貼り付け。
貼り付けの際に「形式を選択して貼り付け→行列を入れ替える」。これで、縦長の表になります。
他の列を全部A列にコピーして、

②一列になった全データを範囲選択
　「データ」タブ＞並べ替えとフィルター＞詳細設定＞「重複するレコードは無視する」にチェックを入れてOK

あとは#8の処理。

この回答へのお礼

関数が全く分からない人の質問、疑問を理解してくださいました。本当にありがとうございました。

お礼日時：2022/02/06 21:28

No.8 回答者： bari_saku 回答日時： 2022/02/05 19:03

#4です。

1つのセルに1つの単語しか入っていないなら、簡単ですよ。
A1セルからデータが入っていて、行数33、列数が11000と仮定して

①カウントしたい単語をA列に順番に入力。
51行目からにしましょうか。ここは大変ですが頑張って下さい。

②B51セルに
=COUNTIF($A$1:$PGB$33,A51)
と入力し、その下のセルに数式をコピー。

なお「重複」ではなく、単純に「個数のカウント」ではないでしょうか。

No.7 回答者： banzaiA 回答日時： 2022/02/05 15:43

>1. 重複する言葉を抽出（言葉の例：りんご、みかん、栗…）

質問
抽出すると言うことは、一つのセルに一単語ではないのですか？
たとえば、一つのセルに
今日の買い物はリンゴ・イチゴ・砂糖だった。
という文があり、そのなかから
リンゴ
イチゴ
砂糖
という単語を
＞列15000、行33の中でから重複する言葉を探
して数えるってことですか？

No.6 回答者： zap35 回答日時： 2022/02/05 13:02

Excelでできますが、以下の手順が必要です。

（結構面倒ですよ）

1）#5さんが書かれているように、作業用シートに1列×495,000行のデータを抜き出します。（15,000×33＝495,000）
　この作業はOFFSET関数を使用します。
2)フィルタの詳細設定を利用して495,000件のデータから重複のない文字列のリストを作成します。
3）COUNTIF関数で、それぞれの文字列の出現回数をカウントします。

一番頭を使うのは1)の作業です。これは実際のシートの配置が分からないと関数を組めませんが、少しイメージが湧くようサンプル画像を貼っておきます。

この回答へのお礼

有難うございました。OFFSET関数が理解できるレベルまで達することができるように、これから勉強します。

お礼日時：2022/02/06 21:32

No.5 回答者： cruelman 回答日時： 2022/02/05 11:36

別シートに列1、行15000*33のデータとして呼び出してcountif関数で勘定する。

この回答へのお礼

ありがとうございました。counrif関数を理解するレベルに至るように頑張ります。

お礼日時：2022/02/06 21:31

No.4 回答者： bari_saku 回答日時： 2022/02/05 11:33

＞列10000以上（excelは16000まであると聞いていますが）、行33です。

言葉はいろんな言葉です。（果物の名前、建物の名前、時事用語、英語表記などが混ざっています）

了解です。横に極端に広い表なのですか、レアケースですね。
もっとスマートな方法はあると思いますが、私ならこうします。

①44行目に、1行目～33行目を＆で繋げたデータを作る
A列なら、34行目に
=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14&A15&A16&A17&A18&A19&A20&A21&A22&A23&A24&A25&A26&A27&A28&A29&A30&A31&A32&A33
と入力。

②検索したい言葉が「あ」なら、A列35行目に
=COUNTIF(A1:A33,"*あ*")
と入力。

③34行目と35行目の関数を残りの列にコピー。

④35行目の数字を合計。

1万超えの列でテストするのは大変なので、まずは少ない列を別シートにコピーして検証してみて下さい。