エクセルデータの集計、一つのセルに複数のデータがある場合

エクセルとパワーBIで500個程度のデータを分析しようとしています。
今持っているデーターベースがあまり良くなく、添付写真の通り「発生したLot番号」が一つのセルに単独で入力されておらす、複数入力されてしまっています。これを一つ一つ集計し最終的に右のグラフにしたいと考えています。
データが多いため手動で数えていくのは厳しいのですが、どのような方法があるでしょうか。
よろしくお願い致します。

No.7 ベストアンサー 回答者： goomania 回答日時： 2023/01/09 12:27

No.5、6です。

No.5では、関数を使ってグラフ用のデータを作成する方法、No.6ではPower Queryを活用して、1つのセルにあるデータをセル分割して直接ピボットグラフを作成する方法を投稿しました。

まだ、クローズされていなかったので、関数とテキストエディタ(メモ帳etc.)を使って1つのセルにあるデータをセル分割して、ご質問者のいう

>一つのセルに単独で入力されておらす、複数入力されてしまっています。･･･

という課題に対し、複数入力されているセルが「セル内改行」を用いている前提で、セルに単独で入力した形にし、集計をしやすくするという加工を行う方法を投稿します。
※この手順はあくまでも「セル内改行」を用いて複数入力を行っている場合にのみ有効であり、文字間にスペースなどを入れて改行に見せている場合は使えません。

添付画像をご覧ください。手順は以下のとおりです。
(1)まず、元データはA1～C7にあるものとし、これをを適当な位置にコピーする。
(2)コピー先の「発生したLot番号」の見出しの位置に以下の数式を記述し、下方向へコピーする。

=SUBSTITUTE(C1,CHAR(10),CHAR(10)&A1&"♪"&B1&"♪")

(3)すると、添付画像①右表のようになる。この右表全体をコピーしてメモ帳に貼り付ける。
(4)添付画像②のように、メモ帳の置換機能を使って「"」(ダブルコーテーション)を空白に置換(つまり削除)する。
(5)添付画像③のように、メモ帳の置換機能を使って「♪」を「列区切り」に置換する。「列区切り」は「トラブル名」と「工程名」の間をマウスドラッグしてコピーし、「置換後の文字列」に内容に貼付けする。
※「列区切り」はTABの文字コードであるが、「置換後の文字列」に直接入力できないので上記の方法をとる
(6)(5)で置換完了すると添付画像④のようになる
(7)メモ帳で作成された添付画像④のデータ全体をコピーし、新たなEXCELシートに貼付けすると添付画像⑤のようになる

以上で、各データがセルに単独で入力された形になり、ここから、ピボット
テーブルを作成するなどして、容易にグラフ化できるようになります。

この回答へのお礼

丁寧なご回答誠にありがとうございました。
PowerQueryを使って無事に希望のデータを作成することができました。
大変助かりました。

お礼日時：2023/01/09 16:27

No.6 回答者： goomania 回答日時： 2023/01/05 14:39

No.5です。

前回の回答で、「Power Query」はハードルが高いと書いたのですが、そうは言っても「Power Query」を使える環境と実力があるのなら、「Power Query」を使わない手はありませんので、その方法を投稿します。

その前に前回回答で、画像④の説明の場面でタイプミスがありました。
>元データの･･･11個なのに、グラフ用データはデータ個数が13個になっています。
と説明しましたが、「データ個数が13個」の部分は正しくは「データ個数が14個」でした。お詫びして訂正いたします。

さて、Power Queryの活用ですが、以下の手順になります。
(1)元データの表をテーブルにします。元表の範囲全体を選択し、メインメニューの「挿入」→「テーブル」を選択する
(2)メインメニューの「数式」→「名前の管理」と進んで、「テーブル1」の名前が元表の範囲に登録されていることを確認する
(3)メインメニューの「データ」→「データの取得」→「その他のデータソースから」→「空のクエリ」と進んで、Power Queryエディタを起動する
(4)Power Queryエディタのメニューから「詳細エディタ」を選択する
(5)クエリの編集画面で表示されているクエリを全て消去する
(6)消去した後、以下のクエリをコピーして貼り付ける

let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
区切り記号による列の分割 = Table.ExpandListColumn(Table.TransformColumns(ソース, {{"発生したLot番号", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "発生したLot番号")
in
区切り記号による列の分割

(7)完了ボタンを押して、詳細エディタを閉じる
(8)Power Queryエディタ画面で「閉じて読込む」ボタンを押して閉じる(画像①のようになる)。
(9)メインメニューの「データ」→「既存の接続」を選択する
(10)「既存の接続」のダイアログが開くので、このブック内の接続(他のクエリはないはずなのでクエリ1と表示されている)を選択して、「開く」ボタンを押す
(11)「データのインポート」のダイアログが開くので、「ピボットグラフ」を選択し、データを返す先として「既存のワークシート」を選択し、元表とかぶらない場所を指定する。さらに、「このデータをデータモデルに追加する」にチェックを入れてOKを押す(画像➁)。
(12)ピボットグラフのフィールドが表示される(画像③)
(13)各ボックスへフィールドをドラッグすると、グラフが作成される。グラフの種類が希望のものと一致しない場合はグラフを右クリックして種類を変更する(画像④)

これでグラフが完成します。

No.5 回答者： goomania 回答日時： 2023/01/03 20:01

fujillinさんが「元データから、一旦、グラフ用のデータ（＝表）を作成して、そちらの表を元にグラフを作成」と回答されておられますが、同意見です。

その方法については、mygoonicknameさんがお示しになったURLで紹介されているPower Queryを用いるのが最も「手間いらず」だと思います。
ただ、Power QueryはEXCELのバージョンによってはアドインをダウンロードしてからでないと利用できませんし、使い慣れない人にはハードルが高い部分もあります。

というわけで、関数を使って元の表がらグラフ用のデータを作成する方法を検討すると、fujillinさんがお示しになった、

=SUMPRODUCT(($B$2:$B$999=$E2)*(1-ISERROR(SEARCH("*"&F$1&"*",$C$2:$C$999))))･･････①

という数式を用いてグラフ用のデータを作成する方法があります(画像①)。
実は、①の数式の"*"&F$1&"*"の部分はワイルドカードを用いず、

=SUMPRODUCT(($B$2:$B$999=$E2)*(1-ISERROR(SEARCH(F$1,$C$2:$C$999))))･･････➁

という数式にしても結果は同じです(画像➁)。確かにSEARCH関数はワイルドカードが利用できるのですが、このご質問のケースでは使用する効果はないということになります。
別の数式として、

=COUNTIFS($B$2:$B$999,$E2,$C$2:$C$999,"*"&F$1&"*")･･････③

という数式でも同様の結果になります(画像③)。

但し、①➁③の数式には重大な注意事項があります。画像④をご覧ください。
Lot11、Lot12が登場したので、それを加えたグラフ用データを作成しようとしたのですが、データ個数が一致しません。元データのデータ個数は11個なのに、グラフ用データはデータ個数が13個になっています。

このようになってしまうのは、C列の「発生したLot番号」は「Lot」という文字と「番号」を組合せて出来上がっています。
①➁③の数式は関数を使って文字列を検索しているので「Lot11」「Lot12」は「Lot11」「Lot12」だけでなく「Lot1*」にカウントされてしまい「Lot1」の数が増えてしまうからです。

このような事態を避けるには「番号」の桁数を固定するということになります。つまり、Lot11やLot12のような2桁の番号が登場するのであれば、Lot1はLot01、Lot2はLot02のように番号の桁数を頭0埋めにして「2桁固定」にする必要があるということです。

ご質問者がお示しになられた画像はあくまでも、例示であり、実際のロット番号は桁数固定になっている可能性もありますが、
>データーベースがあまり良くなく、･･････複数入力されてしまっています。
と仰っているので、もしかすると既に桁数固定でない番号が入力されてしまっていることも考えられます。

この場合、上記のような問題に遭遇する可能性がありますので注意が必要です。

No.4 回答者： fujillin 回答日時： 2022/12/28 23:10

こんばんは

元データから、一旦、グラフ用のデータ（＝表）を作成して、そちらの表を元にグラフを作成すれば宜しいでしょう。


ご例示のグラフを例にとるなら、積み上げグラフで、工程名とLot番号を軸にした表にすれば良いので・・
添付図では、A:C列を元データと仮定してあります。
1）E列に工程名のリストを作成します。
リストが既にあればそれを利用すれば良いですし、なければ、B列をE列にコピーし、そのまま「データ」-「重複の削除」を行うことで、重複のないリストを作成できます。
2）F1：I1にロット番号を記入（多分、連番かな？）
3）F2セルに、条件に合う数をカウントする関数を入力
（作成する表の縦軸、横軸に合致するデータの数をカウントする）
添付図ではF2セルに
=SUMPRODUCT(($B$2:$B$999=$E2)*(1-ISERROR(SEARCH("*"&F$1&"*",$C$2:$C$999))))
を入力してあります。
4）F2セルをI2セルまでフィルコピーし、右下のフィルハンドルをダブルクリック。
　（これで、E列の最下行迄下方にフィルコピーされます）
5）E2：I4を選択して、グラフを作成。

以上で、添付図のような結果を得ることができます。


※ 例とは異なる集計を行う場合も同様ですが、グラフ化したい数をどのようにカウントするかを考える必要はあります。

※ 上記の例では、元データから直接表を作成していますが、直接読み替えて集計するのが難しい場合は、元データの横に各ロットのリストを付け加えて、そこに関数で1、0（＝あり/なし）でデータ化しておいた方が後の処理は簡単になります。
こうしておくことで、いろいろな形式の集計表も簡単に作成できるはずですので。

No.3 回答者： mygoonickname 回答日時： 2022/12/28 22:05

こんばんは。

検索した記事ですが、参考になるでしょうか？
・記事の下の方にある、『セル内改行も分割できる』が使えるかと。

●行方向へ分割する
http://officetanaka.net/excel/function/GetAndTra …

No.2 回答者： tefu_tefuu 回答日時： 2022/12/28 21:51

トラブル名と工程名の結合したセルをホームタブの検索と選択から調べてから、結合したセルを解除して同じデータはコピーで入力して表にしないと、ピポットテーブルでも集計できないような気がします。

No.1 回答者： サクラノキ 回答日時： 2022/12/28 21:29

・フィルター機能を使って「lot1」だけに絞って数える

・COUNTIF関数を使って「lot1」を数える