エクセルについてお聞きします。
過去に来店して、現在までずっと通っている顧客の数を月別に区分して出したいと思っております。
・2009年10月に来店～現在まで・・・５人
・2009年11月に来店～現在まで・・・２人
・2009年12月に来店～現在まで・・・１０人
現在期間に関するデータとして、S2のセルに「=DATE(G2,H2)」、
U2のセルに「=DATE(I2,J2)」のように
他のセルから数値を引用してS(2009/10/15)T(～)U(2009/12/15)と表示させており、(全て2行目)
それをV2のセルで「=DAYS360(S2,U2)」で期間内の来店頻度(一週間区切り)を出しています。
このデータを元に何かの関数を使うことで
「初来店から現在まで(2010/5/31とします)通って来ている顧客の月毎の人数」を出したいと
思っているのですが、どのような関数を使えばよろしいでしょうか？
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。
ログインして回答する
みんなの回答
-
SALINGER
満足150pt
まず、おかしなところを２点。
=DAYS360(S,U)
これは１年を３６０日で計算するので、正確にするなら、
=DATEDIF(S,U,"d")
もしくは、
=U-S
それと、
=SUM(K/(V/7))
は、SUMは複数の要素を合計する関数で要素は１つしかないので
=K/(V/7)
でいいです。
それで実際の方法ですが、別のシートに表を作るとしてこんな感じでどうでしょうか。
A B C 1 年 月 月毎 2 2009 7 数式 3 2009 8 ↓ 4 2009 9 ↓ 5 2009 10 ↓ 6 2009 11 ↓ 7 2009 12 ↓ 8 2010 1 ↓ 9 2010 2 ↓ 10 2010 3 ↓
数式のところに次の数式を入れて、Ctrl+Shift+Enterで配列数式にして下にコピーです。
=SUM(IF(IF(DATE(A2,B2+1,1)-1>Sheet1!U$2:U$100,Sheet1!U$2:U$100,DATE(A2,B2+1,1)-1)-IF(DATE(A2,B2,1)>Sheet1!S$2:S$100,DATE(A2,B2,1),Sheet1!S$2:S$100)+1>0,(IF(DATE(A2,B2+1,1)-1>Sheet1!U$2:U$100,Sheet1!U$2:U$100,DATE(A2,B2+1,1)-1)-IF(DATE(A2,B2,1)>Sheet1!S$2:S$100,DATE(A2,B2,1),Sheet1!S$2:S$100)+1)/(Sheet1!U$2:U$100-Sheet1!S$2:S$100)*Sheet1!K$2:K$100,0))
式中の100は顧客名簿100行までということなので、それ以上多い場合は数を増やしてください。
１つの数式にまとめているので、わかりづらいですが、配列数式については、こちらを参考に。
- その他の回答を読む
MAYARANこの質問へのコメント
質問の情報
- 登録日時
- 2010-05-31 11:31:35
- 終了日時
- 2010-06-02 07:05:02
- 回答条件
- 1人2回まで
この質問のカテゴリ
この質問に含まれるキーワード
人気の質問
-
この英文どうなってますか？ The boy seemed to be waiting for his mother to come.（その少年は母親が来…
匿名質問者
3
1
-
不定詞の形容詞的用法らしいんですけど、副詞的用法と書いたら✖️ですか？ I want to buy a book to read …匿名質問者11
-
日向陽葵という○V女優が好きなのですが似ている○V女優はいますか匿名質問者
-
共通テストで使われるプログラミング言語は共通テスト特有の言語と聞いたのですが、受験勉強で身につけたプ…匿名質問者21
-
内定してほしいポケモンはいますか？匿名質問者
DATE関数の引数が一つ足りないですし、DAYS360関数は来店頻度をだす関数ではないので
実際にどこにどういう関数でどういうデータが入っているのかが伝わっていないと思われます。
A=VLOOKUP参照 1を入れるとBに〇がつく(sheet2) ■手入力■
B=他店舗との行き来 〇の表示
C=顧客NO 1～1500まで ■手入力■
D=VLOOKUP参照 1を入れると男、2を入れると女(sheet2) ■手入力■
E=性別 男 女の表示
F=年代 0、10、20、と10代区切りで90まで ■手入力■
G～H=数式１ sの月、日にちの参照元 ■手入力■
I～J=数式２ uの月、日にちの参照元 ■手入力■
K=回数 来店の回数 ■手入力■
L=VLOOKUP参照 111から3313まで番号を入力(sheet2) ■手入力■
M=住所 住所番号に応じた住所を表示 121=～市～町丁目
N～R=担当者欄 初回から５回目までの顧客担当者名の入力 ■手入力■
S=GとHとXの引用先 例・・・S=2009/1/10 G=1 H=10 X=2009
T=「～」 「～」
U=IとJとZの引用先 例・・・S=2010/1/10 G=1 H=10 X=2010
V=初日来店からの期間 「=DAYS360(S,U)」
W=日 Vの表示の補足(3.5「日」←この部分)
Y=来店頻度 =SUM(K/(V/7)) 一週間に何回来ているか
X=2009
→Tの2009/の参照元 2010に変更する際は目視チェック、手入力で修正
Z=2009
→Uの2009/の参照元 2010に変更する際は目視チェック、手入力で修正
※すいません、Vは来店頻度ではなく、T～Uの期間でした。。。
入力する際、別々のクリアファイルに入った顧客リストを見ながら手入力を行なうのですが
一件につき30秒以上かけられないので、なるべくかかる時間が少ないよう
上記のような並びにしました。
現在、上記のデータ入力で時間をかけてしまっているので
もし次回以降「このようにすると早い」「この項目を入れたほうがいい」等の
アドバイスがございましたら是非ご教示ください。
他に足りない点はございますでしょうか？
エクセルはこの前の質問で始めたばかりですので
正直、なんにもわかっておりません。。。
お手数をおかけ致します・・・
↑すいません、「S～Uの期間」です・・・
この数式は、全体の月毎の平均来店人数を出すものです。
この中で「現在まで通っている顧客の分の」ということになるのでしょうか？
その場合はどこでそれがわかりますか？
「10月に新規来店した顧客・・・計50人」
「11月に新規来店した顧客・・・計40人」
「12月に新規来店した顧客・・・計45人」
「1月に新規来店した顧客・・・計37人」
「2月に新規来店した顧客・・・計45人」
とした場合、この中で現在まで通ってきている顧客の数を
(「現在まで」というのは、例えば2010.4.30の日にちを入力して
2009.10月に来店された新規顧客が2010.4.30以降の日付で来店しているか
というニュアンスです)
「10月に新規来店した顧客で2010.4.30以降に来店記録のある顧客数・・・計15人」
「11月に新規来店した顧客で2010.4.30以降に来店記録のある顧客数・・・計10人」
「12月に新規来店した顧客で2010.4.30以降に来店記録のある顧客数・・・計17人」
「1月に新規来店した顧客で2010.4.30以降に来店記録のある顧客数・・・計20人」
「2月に新規来店した顧客で2010.4.30以降に来店記録のある顧客数・・・計15人」
のように出してみたいんです。
具体的にどのように使用するかと言えば
「その月毎に設定したスタッフの対応の仕方(毎月スタッフの顧客への対応方法を変えています)
によってどのくらい再来率が変わるのか」
「どの対応方法が効果的だったのか」を測る目安として
数字を出したいと思っております。
一番いいのは「月毎の新規顧客数」と「月毎の新規顧客数の中で現在まで通っている顧客数」が
出せるのが一番いいのですが、両方を別のセルで算出するのは手間がかかりますか？
=DATEDIF(S,U,"d")
の関数の中の「"d"」というのは
「D=VLOOKUP参照 1を入れると男、2を入れると女」
の項目を示すのでしょうか？
DATEDIFは「DAYS360関数」の360日ではなく、
もっと正確に「365日」を算出する関数なのでしょうか？
第３引数の"d"はday即ち日数の間隔を返します。年なら"y"です。
ただし、Excelは日付を内部でシリアル値という１日を１とする値を使っているので、
U-Sだけでも間隔の日数を返します。
S列とU列の日付は何を意味しますか？
S列が初回来店日でU列は最終来店日ということでしょうか。
1行につき1回の来店の記録であり、S列は初回来店日、U列はその来店日、K列は何回目の来店か。
行毎にデータが増えていき同じ顧客が複数の行にある。
ありがとうございます。
より正確に日数がでますね。
顧客NOと来店回数が同じ数値になることはあるかもしれません。
C列に同じ番号は出てきません。
<S列が初回来店日でU列は最終来店日ということでしょうか
→その通りです。
<1行につき1回の来店の記録であり、S列は初回来店日、U列はその来店日、K列は何回目の来店か。
<行毎にデータが増えていき同じ顧客が複数の行にある。
いえ、一人の顧客毎に番号は一つです。
(顧客NO13の人が13回来店されても行は一つです)
「更新」という側面を全く考えていないので
今度データを更新する際は、また顧客NO１から手入力になります。
<S列は初回来店日、U列はその来店日、K列は何回目の来店か。
→こちらについてはその通りです。
他に足りない項目等ございませんでしょうか？
便宜的にSheet1にしてるので、違う名前例えばaaaならば、数式はaaa!X$2:X$1000のようにしてください。
それで数式で参照している、セルのどこかで０や空白で除算していることになる。
例えばY列の参照頻度がずれてX列になってるとか。
データのある行数は501までなので、教えていただいた数式の1000まである数字を
501に変換しました。
A～Zまで上記コメントの通りです。
=SUMPRODUCT((Sheet1!X$2:X$1000=A1)*(Sheet1!G$2:G$1000=C1)*(Sheet1!U$2:U$1000>=DATE(E1,G1,I1)))
→上記数式によるとSHEET1のデータ参照先は
X=2009
A=VLOOKUP参照 1を入れるとBに〇がつく
G=sの月、日にちの参照元
C=顧客NO 1～1500まで
U=IとJとZの引用先 例・・・S=2010/1/10 I=1 J=10 Z=2010
E=性別 男 女の表示
G=sの月、日にちの参照元
I=uの月、日にちの参照元
となりますが、提示していただいた数式は2010-06-01 19:15:17 に回答していただいた
シートに準ずるものでしょうか？
こちらのシートはSHEET3に打ち込んであります。
SHEET1については500件、既にデータ打ち込みが終わっており
改変することができません。
もし可能であれば、
=SUMPRODUCT((Sheet1!X$2:X$1000=A1)*(Sheet1!G$2:G$1000=C1)*(Sheet1!U$2:U$1000>=DATE(E1,G1,I1)))
の数式がどの列を参照して、どのような計算をして
、どのような答えを出そうとした数式なのか
お伝えいただけないでしょうか？
知識が至らぬばかりに説明ベタになり
大変申し訳ありません。。。
これは1000行まで対応なので、1000のままでもいいです。
私のサンプルではずっと少ない数で試しています。
＞こちらのシートはSHEET3に打ち込んであります。
どこのシートでも動作します。
数式の意味ですが、
SUMPRODUCT((条件１)*(条件２)*(条件３))
となっていまして、３つの条件に合う個数を数えるという意味です。
条件１は、初回来店日の年が同じかどうか。回答表のA列とSheet1のX列です。
条件２は、初回来店日の月が同じかどうか。回答表のC列とSheet1のG列です。
条件１と２が合えばその月に初回来店していることになります。
条件３は、～日以降と最終来店日の比較です。回答表のE列G列I列で日付を作って、Sheet1のU列と比較です。
最終来店日の方が大きければ、～日以降来店しているということになります。
この関数では３つの条件を満たす個数を求めています。
丁寧に説明していただいたおかげで、次回以降に同じような関数を使用するときも
なんとか自分で再現できそうです。
非常に助かりました。
感謝してます！