エクセルで重複データから重複を削除して指定の列に抽出したい

大きな表から、重複を削除した値（文字列）を指定の列に抽出したいのですが。

目的はその表の文字列（製品名）の発現頻度を知りたいのです。
例えば「Ａ１２３」という品種が何度その表に記載されているか、を知りたいのです。

「ＡＢＣ１２３」に限定すればカウント関数で簡単に求められますが、関数を使用する前に製品名の一覧が無いのでハタと困ってしまいました。

やりたいことは表中に分散されている製品名を指定の列に抽出して、その列を参照してカウント関数をかければ、表中の製品ごとの数が分かると思うのですが。

No.7 ベストアンサー 回答者： fujillin 回答日時： 2022/05/11 16:26

No4です。

＞適当な列のセルに入れて下に引っ張ってみたのですが、全て０になってしまうのですが。。。
こちらでは質問者様の扱おうとしているシートのレイアウト等が全く不明なので、あくまでも要領を示すための例示の式です。
（具体的な式等が欲しければ、セルの範囲や位置関係を明確にして質問なさらない限り無理です）
ですので「のような感じで」と書いておいたたつもりです。

No4の式はA1セルから100列分（＝1行目）を縦に並べ、続いてA2セルから100列分（＝2行目）を・・という順に全体を1列に表示する場合の例です。
=OFFSET(セル範囲左上の絶対参照,INT((ROW(A1)-1)/列数),MOD(ROW(A1)-1,列数))
という式に替えて、「セル範囲左上の絶対参照」「列数」にそれぞれ該当する値を入れて使ってください。

＞値に変換・・・ということは文字列を数値に換算して、
＞重複削除後の列を元の文字列に戻す、という意味でしょうか？
違います。
セルの内容が関数式の式のままだと、重複を削除した結果が正しく得られないので、一旦、式を値に変換するという意味です。
具体的には、列を「コピー」-「値をペースト」することで、値に変換できます。


No3様の回答にもありますが、VBAを使えば簡単にできることですが、多分、御存じではなさそうですし、セルの位置関係が不明のままでは関数式以上に回答のしようがありません。

ピボットに関しては簡単なテスト済みですが、100×300が全て同じ項目の場合には、多分、上手くいかないと思います。
通常は、表形式のものを扱うことを前提としていますので。
1列に変換してからピボットにすれば、可能かも知れませんけれど・・(笑)

この回答へのお礼

何度もご丁寧な対応に感謝！！

うまく行きました。
これで当面の方針（どの品種から始めるか）が決められそうです。

また、ピボットについても日付と設備名だけなので確かにご指摘のように具体的なイメージが分かりにくいです。（元々使い慣れていないので）

質問に不憫があったにもかかわらず最後まで対応くださりありがとうございました。

お礼日時：2022/05/11 17:04

No.11 回答者： GOMΛFU 回答日時： 2022/05/11 20:06

シーケンシャル番号をどこかの列につけた後、ソートしてcountif

この回答へのお礼

お礼が遅くなりました。

＞シーケンシャル番号をどこかの列につけた後、ソートしてcountif・・
当方にはハードルが高く、残念ですが意味が理解できないレベルです。

お礼日時：2022/05/16 08:06

No.10 回答者： d-q-t-p 回答日時： 2022/05/11 17:07

一応ピボットテーブル単独でもできます。

レガシー機能（Alt + D・P）に「複数のワークシート範囲」がある
ので 手動集計で 指定範囲を読み込んで行フィールドに「値」を入れ
込めば件数を数えられます。

サンプルの表の場合 A1:F5を範囲指定です。

この回答へのお礼

お礼が遅くなりました。

この機能はピボットテーブルのガイド機能のようですね。
ピボットテーブルは非常に有用な機能だと思いながら使いこなせなくて毎度途中でエクセルで処理してしまっていました。

ご回答を参考に再度挑戦してみようかと思います。
今回は取り合えずNo4さんのご回答ですでに解決できました。

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

お礼日時：2022/05/16 08:16

No.9 回答者： d-q-t-p 回答日時： 2022/05/11 16:55

例えば左のテーブルから 右のテーブルを作る場合

「支店名」以外の列を選択して [列のピボット解除]
値列を [列のグループ化]（操作：行のカウント）
（必要なら）値列を昇順にソート

以上

この回答へのお礼

お礼が遅くなりました。

＞列のピボット解除・・
このピボットが使えないレベルです。 トホホ・・です。

お礼日時：2022/05/16 08:00

No.8 回答者： d-q-t-p 回答日時： 2022/05/11 16:46

> 1列に変換してからピボットにすれば

ああ複数列にデータがまたがっているんですかね？

> やりたいことは表中に分散されている製品名を指定の列に抽出して
ということなら PowerQueryを使えばいいでしょう。列のピボット解除
を実行すれば 1列のデータになります。そこからグループ化で個数を集
計すればおしまいです。
https://docs.microsoft.com/ja-jp/power-query/unp …

この回答へのお礼

お礼が遅くなり申し訳ありません。

＞・・・ PowerQueryを・・・
＞列のピボット解除・・・
＞・・・そこからグループ化で個数を・・

当方にはハードルが高すぎます。。。トホホ・・・

お礼日時：2022/05/16 07:58

No.6 回答者： t_fumiaki 回答日時： 2022/05/11 16:16

普通にエクセルだけで簡単にやるなら以下で。

元データは作業中になくなるので、シートをコピーしてからです。

①どこかの列にデータを縦1列にコピペして並べます。
　見出しが要るので、適当な見出しをつけます。
　A列に並べたとして、B列・C列を作業列にします。
　データはA2以下の行です。

②A列を昇順に並べ替え

③B列・C列に関数を入れます
・B2：=IF(A1<>A2,1,B1+1)　下までコピペ
・C2：==IF(A2=A3,1,"")　下までコピペ

A,B,C列をコピーして、"形式を指定して貼り付け"で"値"を貼り付ける

④C列フルターで、空白以外で表示し、表示された行を削除
(クリアでは無く削除）

B列には重複していたデータ、C列にはその個数、が残ります。

この回答へのお礼

お礼が遅くなりました。

＞①どこかの列にデータを縦1列にコピペして並べます。
この作業を自動化したいのが一番の期待です。

これが出来れば当方の知識でも何とかできそうなので。

お礼日時：2022/05/16 07:56

No.5 回答者： d-q-t-p 回答日時： 2022/05/11 14:10

> 関数を使用する前に製品名の一覧が無いので

知りたいのが発現頻度ならピボットテーブルで集計すればいいです。

この回答へのお礼

ご回答ありがとうございます。

ピボットテーブルは大昔に使ったことがあるのですがすでに忘却の彼方の感じです。

想像以上に難しいようなら再度ピボットテーブルを勉強してみた方が良いかもですね。

お礼日時：2022/05/11 15:09

No.4 回答者： fujillin 回答日時： 2022/05/11 14:00

No2です

＞例えばこの方法で、100列X300行の表中に点在する文字列ごとの一覧表が作れるのでしょうか？
え～～っと・・
100×300のセル全部を対象に、重複を除いたものを抽出したいってことでしょうか？
（普通はあまりないことなので・・）

もしそうなら、応用すれば良いのではないでしょうか。
1）100列を関数を使って縦に並べる
　　=OFFSET($A$1,INT((ROW()-1)/100),MOD(ROW()-1,100))
　のような感じでフィルコピーすれば可能です。
2）これを値に変換した上で、重複の削除機能を用いる

あるいは、関数を用いて一発で抽出することも可能と思いますが、相当にに複雑で長い式になります。
配列計算でデータ数が30000になるので、計算処理にも時間がかかる可能性があります。
一度だけの処理なら、前記の方法の方が手っ取り早いであろうと思います。

この回答へのお礼

何度ものご回答ありがとうございます。

NETで調べても目的に合致する情報が無かったのはあまり必要ではないからですかね？
確かに処理は取り合えずやってみての話なのでご回答の１）、２）で対応してみようかと思うのですが、この式の使い方が分かりません。

適当な列のセルに入れて下に引っ張ってみたのですが、全て０になってしまうのですが。。。

ご回答の式の関数は使ったことが有るものなのですが情けないことに、組み合わせた式の意味が解らないレベルです。
値に変換・・・ということは文字列を数値に換算して、重複削除後の列を元の文字列に戻す、という意味でしょうか？

回答に解説を求めるようで申し訳ありません。

お礼日時：2022/05/11 15:00

No.3 回答者： t_fumiaki 回答日時： 2022/05/11 13:04

vbaを使わないと無理が有ります。

この回答へのお礼

VBAのコードをかけるレベルではないので質問させてもらった次第です。

割とよくある作業かと思ったのですが、当方の想定以上にハードルが高い要求のようですね。

お礼日時：2022/05/11 15:03

No.2 回答者： fujillin 回答日時： 2022/05/11 12:25

こんにちは

重複の無い一覧を作成したいってことですよね。

手動で良ければ、
1）対象となる列をコピペして
2）「データ」-「データツール」-「重複の削除」
で作成可能です。

Office365環境であれば、対象セル範囲に対して、UNIQUE関数を用いることで、スピルで一覧を作成することも可能です。

この回答へのお礼

早々のご回答ありがとうございます。
当方残念ながらVer,2016です。そもそもUNIQUE関数を全く知らないのであまり残念ではないのですが（トホホ・・）

最初にNETで調べてご回答の方法を試そうとしたのですが、
列ごとの重複削除になってしまうし空白セルになってしまうので各文字列毎の頻度を求める時にCOUNTIF関数が使いづらいので質問させていただいた次第です。

例えばこの方法で、100列X300行の表中に点在する文字列ごとの一覧表が作れるのでしょうか？

お礼日時：2022/05/11 12:35