エクセル データ 比較

質問

エクセル　２つのデータの差異

エクセル初心者です。

今週の顧客リストを先週のものと比較し、
新たにあらわれた人、逆にいなくなった人の
数を簡単に集計するにはどういう方法が一番
よいでしょうか？

またそのあらわれた人、いなくなった顧客の数
を同時に10個の支店別に集計する方法があれば
ご教示いただけると助かります。顧客リストには
全ての支店の顧客があわせてのっており、支店番号
と会員番号２つのセルの情報でユニークの顧客を
認識しています。

回答No.5

MarcoRossiItaly

No.4 です。ごめんなさい、間違った情報を書いてしまいました。次のとおり訂正いたします。

×
また、入退会のフラグとして「入会は 1、退会は 0」というものを採用した場合は、その列の合計がそのまま在籍している会員数に一致することになるので、何かと便利です。

↓

○
また、入退会のフラグとして「入会は 1、退会は -1」というものを採用した場合は、その列の合計がそのまま在籍している会員数に一致することになるので、何かと便利です。

なお、入会と退会の列を分けてもいいですね。入会が 1 なら退会は空白、入会が空白なら退会は 1、という感じに記入していきます。

回答No.4

MarcoRossiItaly

データベース型の表として元データを整備し、そこからピボットテーブルの機能により、必要な表を抽出する方法をお勧めします。Access でもいいのですが、行数が数千・列数が数十といった小規模な表 1 つなどであれば、Excel のほうが簡単に扱えると思います。ピボットテーブルの使い方については、インターネット上に無数の情報があります。

この元データの表では、週ごと、支店ごとなどに分割せず、過去の全データを一体のものとしておき、そこに新規データを追加していきます。つまり何週間もいる顧客であっても、元データの中に登場するのは、現れたときと去ったときの 2 行のみという形にします。週ごと・支店ごとのリストについては、抽出時に日付・支店で絞り込むことにより、得ます。

ピボットテーブルでは個数（人数）も数えられるし、金額などの数値の合計を求めることも可能です。そのための数式をセルに記入する必要もありません。

また、入退会のフラグとして「入会は 1、退会は 0」というものを採用した場合は、その列の合計がそのまま在籍している会員数に一致することになるので、何かと便利です。

お礼

ご回答、ありがとうございます。
明日さっそく会社で試してみます。
ありがとうございました。

回答No.3

nishi6

添付図のように３支店で解答します。

添付図では、シート「先週」とシート「今週」を作っています。

各々、照合用に、E列で「支店番号」と「会員番号」を結合しています。
　セルE3：=C3&"_"&D3　　、下にコピーします。

シート「先週」のB列で今週いなくなった人「脱退」に「×」を付けています。
　セルB3：=IF(ISERROR(VLOOKUP(E3,今週!$E$3:$E$15,1,0)),"×","")

シート「今週」のB列で今週新たに表れた人「加入」に「○」を付けています。
　セルB3：=IF(ISERROR(VLOOKUP(E3,先週!$E$3:$E$14,1,0)),"〇","")

各々下にコピーします。

集計はフィルターでもいいと思いますが、表にする場合、
添付図のように支店番号を記入しておき、

　先週のセルD17：=COUNTIFS($C$3:$C$14,C17,$B$3:$B$14,"×")
　今週のセルD18：=COUNTIFS($C$3:$C$15,C18,$B$3:$B$15,"〇")

とします。下にコピーします。
集計用の表の位置は添付図の場所である必要はありません。コンパクトにまとまるために添付図の位置にしているだけです。

上の２つの式はExcel2007以降用です。それより前ならば、

　先週のセルD17：=SUMPRODUCT(($C$3:$C$14=C17)*($B$3:$B$14="×"))
　今週のセルD18：=SUMPRODUCT(($C$3:$C$15=C18)*($B$3:$B$15="〇"))

にしてください。

お礼

わかりやすい表まで作成いただき、
ありがとうございます。

会社使用にパソコンは７になっていますので
ご教示いただいた関数を入れてトライして
みます。ありがとうございました。

回答No.2

noname#205029

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1428859469
で紹介されているツールを使うのがトータルで最も早いでしょう。

数式で片方にあるデータがのこりの片方にもあるかどうかを確認するなら
http://www.moug.net/tech/exopr/0040056.html

お礼

さっそくのご回答、ありがとうございます。
明日会社でダウンロードして使えるか試して
みます。ありがとうございました。

回答No.1

MackyNo1

先週の顧客リストは保存されている（週ごとに保存している）のでしょうか。

Ｅｘｃｅｌのバージョンが記載されていないので、Ｅｘｃｅｌ2007以降の場合で説明すると、もし保存されていない場合は「変更履歴の記録」の機能を利用することになります。

先週のデータが別ファイルであるなら、該当データをデータタブの「詳細設定」でリスト範囲を今週のデータ範囲、検索条件範囲を先週のデータ範囲に設定して「ＯＫ」すれば先週のデータが抽出されますので、このフィルタされたデータを削除してフィルタボタンをクリックすれば、新たに今週追加されたデータが残っています。
この空白行交じりのデータ全体をドラッグして選択して「並べ替え」でたとえばＡ↓Ｚアイコンをクリックすれば今週のデータ一覧が取得できます。
逆にリスト範囲と検索条件を逆にすれば、削除したデータの一覧を表示することができます。

関数で対応する場合はＣＯＵＮＴＩＦ関数で今週のデータの会員番号が先週の会員番号セルの範囲に含まれているか調べて、この数字が0の場合、変更があったということになりますので、これをオートフィルタで抽出し、新規シートにコピー貼り付けします。
同様に先週のデータが今週のデータ範囲に含まれているか調べて逆のパターンを表示させます。

支店別に集計するのはＣＯＵＮＴＩＦ関数を利用します。

さらに複雑な数式を駆使すれば変更分だけの会員番号などを自動的に表示することも可能です。

上記の回答の具体的な関数などが知りたい場合は、ご使用のＥｘｃｅｌのバージョンと元データシートのレイアウトをお知らせください。

お礼

データ機能でそのような便利な使い方が
あるとは知りませんでした、ありがとうございました。

明日試してみてまた質問がありましたら再度
投稿をさせていいただきたいと思います。

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