Excel関数についての、皆様の知恵をお貸し下さい。


在る時間帯に何人の客がきたかということに関しては「countif」関数でうまく集計できていました。  実は、「日付」も一緒に区別して計算させたいと思っています。 今までは、日付ごとにデータを分けてそれぞれ数えていたのですが、それをいっぺんに行うことは可能でしょうか? 例えば1月に入った客で、午前中に入った客が 1月10日なら○人、1月11日なら○人 あるいは、1月10日に入った客は午前中は○人、午後は○人。1月11日は午前中に○人、午後は○人。 という感じです。

もし、そんな関数がある、あるいは それならこの関数を複合すればよいなどありましたら、是非教えてください。
ちなみに、元のデータは「日付」「時間」だけのデータベースです。

回答の条件
  • 1人2回まで
  • 登録:2008/03/20 13:20:35
  • 終了:2008/03/22 01:56:14

ベストアンサー

id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912008/03/20 15:35:23

ポイント34pt

たとえばデータが下記のようであった場合、

A B
1 3月4日 9:10
2 3月4日 11:30
3 3月5日 10:00
4 3月5日 13:20
5 3月5日 15:20
6 3月5日 10:10
7 3月6日 11:10
8 3月6日 14:10
9 3月6日 15:10
10 3月6日 17:10

集計列を

D E F
1 日付 AM PM
2 3月4日 式1 式2
3 3月5日 式1をコピー 式2をコピー
4 3月5日 式1をコピー 式2をコピー
5 3月5日 式1をコピー 式2をコピー

のようにしたと仮定すると E2 に下記のような数式を記述し Ctl + Shift + Enter を押します。

【式1】
=SUM(($A$1:$A$10=$D2)*($B$1:$B$10<0.5))

同じくF2 に下記のような数式を記述し Ctl + Shift + Enter を押します。

【式2】
=SUM(($A$1:$A$10=$D2)*($B$1:$B$10>=0.5))

E2:F2 をコピーし、E3:F5 にコピーすれば、それぞれの日付の午前、午後を集計できます。


式の意味については、配列数式をご参照ください。

http://t_shun.at.infoseek.co.jp/My_Page/Excel-REF/ref_page4-1.ht...


【応用】

・時間で集計する場合は、0.5 の部分を (時間/24)の数値に置き換えます。

・条件を*()をつなげることで、開始時間と終了時間で指定した集計も可能になります。


VBA が使用できれば、ユーザ関数を作成して日付と時間で集計できる関数を定義するといった

方法も可能かもしれません。


コメントを有効にしていただければ、不明な点は対応します。

id:kiyonorida

なるほど。 配列数式を知ってから少し勉強し、FREQUENCYとIF関数を組み合わせる方法を使ってみましたが、SUM関数は式が長くなりますが、扱いやすいですね。勉強になります。

2008/03/22 01:52:26

その他の回答(3件)

id:psycho24 No.1

psycho24回答回数586ベストアンサー獲得回数512008/03/20 15:18:06

ポイント22pt

配列を使うと上手くいきますよ

http://pc.nikkeibp.co.jp/pc21/special/hr/hr1.shtml

id:kiyonorida

なるほど。配列というものを恥ずかしながら初めて知りました。さっそく読んでみます。ありがとうございました。

2008/03/21 16:12:36
id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912008/03/20 15:35:23ここでベストアンサー

ポイント34pt

たとえばデータが下記のようであった場合、

A B
1 3月4日 9:10
2 3月4日 11:30
3 3月5日 10:00
4 3月5日 13:20
5 3月5日 15:20
6 3月5日 10:10
7 3月6日 11:10
8 3月6日 14:10
9 3月6日 15:10
10 3月6日 17:10

集計列を

D E F
1 日付 AM PM
2 3月4日 式1 式2
3 3月5日 式1をコピー 式2をコピー
4 3月5日 式1をコピー 式2をコピー
5 3月5日 式1をコピー 式2をコピー

のようにしたと仮定すると E2 に下記のような数式を記述し Ctl + Shift + Enter を押します。

【式1】
=SUM(($A$1:$A$10=$D2)*($B$1:$B$10<0.5))

同じくF2 に下記のような数式を記述し Ctl + Shift + Enter を押します。

【式2】
=SUM(($A$1:$A$10=$D2)*($B$1:$B$10>=0.5))

E2:F2 をコピーし、E3:F5 にコピーすれば、それぞれの日付の午前、午後を集計できます。


式の意味については、配列数式をご参照ください。

http://t_shun.at.infoseek.co.jp/My_Page/Excel-REF/ref_page4-1.ht...


【応用】

・時間で集計する場合は、0.5 の部分を (時間/24)の数値に置き換えます。

・条件を*()をつなげることで、開始時間と終了時間で指定した集計も可能になります。


VBA が使用できれば、ユーザ関数を作成して日付と時間で集計できる関数を定義するといった

方法も可能かもしれません。


コメントを有効にしていただければ、不明な点は対応します。

id:kiyonorida

なるほど。 配列数式を知ってから少し勉強し、FREQUENCYとIF関数を組み合わせる方法を使ってみましたが、SUM関数は式が長くなりますが、扱いやすいですね。勉強になります。

2008/03/22 01:52:26
id:pahoo No.3

pahoo回答回数5960ベストアンサー獲得回数6332008/03/20 15:45:46

ポイント17pt

SUMPRODUCT関数で実現できます。

他にもやり方があり、「countifで複数条件」の中程をご覧下さい。

id:kiyonorida

はじめはcountif関数でがんばっていたのですが、応用できるんですね。ありがとうございます。

2008/03/22 01:53:22
id:sankei_antenna No.4

sankei_antenna回答回数7ベストアンサー獲得回数02008/03/21 08:57:57

ポイント17pt

http://www.excel-jiten.net/formula/serial_datetime.htmlによれば、

シリアル値とは、日付と時刻を表す数値のことです。

その数値の整数部分は「日付」を、小数部分は「時刻」を表します。

とされています。このことを利用して、作業用の列を使用する場合を考えてみました。

1 A列に日付のシリアル値、B列に時刻のシリアル値がそれぞれ1行目から100行目まで入力されているデータベースがある場合、C1からC100までのセルに、次のように式を入れます。

=A2+B2 (C2の場合)

2 調べたい日が、例えばG5に入力されているとします。そのとき、

 ① その日の午前の個数を求めるときは、次のように関数を入力します。

=COUNTIF($C$1:$C$100,"<"&(G5+0.5))-COUNTIF($C$1:$C$100,"<"&G5)

   上記式中、"G5+0.5"とは調べたい日の正午を表します(以下同じ)。

 ② ある日の午後の個数を求めるときは、次のように関数を入力します。

=COUNTIF($C$1:$C$100,">="&(G5+0.5))-COUNTIF($C$1:$C$100,">="&(G5+1))

   上記式中、"G5+1"とは調べたい日の翌日を表します。

id:kiyonorida

恥ずかしながら、シリアル値の定義を初めて知りました。 知識がたくさんあると応用のしがいがあるんだと、つくづく感じました。ありがとうございます

2008/03/22 01:55:00

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません