エクセルで日付の個数を判定する。

質問

エクセルでの日付の個数を判定したいです。
具体的には
月日 走行時間 走行距離 平均速度 最高速度 累積距離
10月13日 1:12:14 17.84 14.8 28.2 4,046.3
というようにデータが入っていて、1行1回の走行データが入っています。
なので、月日欄が2行、もしくは3行同じ値がある場合、これを1とカウントし、1日あたりの走行時間、走行距離を計算したいのですが、たまに
07月12日 1:37:35 28.28 17.4 42.0 2,592.2
07月12日 0:13:55 2.33 10.0 19.4 2,594.6
07月13日 1:14:07 21.16 17.1 34.6 2,615.7
というように、1日2回記録がある場合、これを7月12日は1日とカウントしたいのですが、この場合関数などどのようなものを使えばいいでしょうか？
データの個数＝行数は今のところ235行でどんどん増やしていきたいのですが。
どうかよきアドバイスをお願いします。

回答No.7

tom04 · ベストアンサー

No.4・5・6です！
ほんとぉ～！に何度もごめんなさい。

投稿した後でもう一度質問文を読み返してみたところ
一番最初の
>エクセルでの日付の個数を判定したいです。

を見落としていたみたいです。

もしかして単純に違う日付が何日あるか？という数だけ表示できればよかったのですかね？

もしそうであれば、
かなり余計なお世話の回答をしていたみたいです。

Sheet1の作業列A列はそのまま生かしてもらい、
表示させたいセルに
=COUNT(A:A)
としてみてください。

これで画像の場合は「3」という数値が表示されるはずです。

以上、参考になれば良いのですが・・・
今回も大外れならごめんなさいね。m(__)m

回答No.6

tom04

No.4・5です！
返信が遅くなってごめんなさい。

前回は平均速度や最高速度等も一緒に処理しようとして、
配列数式があったために余計に判りづらくなったみたいでごめんなさいね。

改めて回答させていただきます。
今回は、走行時間と走行距離の日付ごとの集計だけを回答してみます。
（配列数式はありませんので、この画面から数式をそのままコピー＆ペーストしても大丈夫だと思います。）

↓の画像で前回同様Sheet1に作業列を設けさせていただいています。
A列を作業用の列としていますので、
A2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
としてオートフィルで1000行くらいまでコピーしてください。

そして、Sheet2の「日付」のA2セルに
=IF(COUNT(Sheet1!$A$2:$A$1000)>=ROW(A1),INDEX(Sheet1!$B$2:$B$1000,SMALL(Sheet1!$A$2:$A$1000,ROW(A1))),"")

「走行時間」のB2セルに
=IF(A2="","",SUMIF(Sheet1!$B$2:$C$1000,A2,Sheet1!$C$2:$C$1000))

「走行距離」のC2セルに
=IF(A2="","",SUMIF(Sheet1!$B$2:$D$1000,A2,Sheet1!$D$2:$D$1000))

という数式を入れます。

最後にA2～C2セルを範囲指定し、C2セルのフィルハンドルでオートフィルで下へコピーすると
画像のような感じになります。

今度はなんとか希望に近い形にならないでしょうか？

以上、お役にたてば幸いですが、
今回も的外れならごめんなさいね。m(__)m

回答No.5

tom04

No.4です！
たびたびごめんなさい。

前回の回答の中で配列数式になるセルがありましたので
その説明をしていませんでした。
この画面からそのままコピー＆ペーストしただけでは正確な値を返さないと思いますので、
再び顔を出してしまいました。

前回の表そのまま利用させていただいて、
Sheet2のE2・F2セルは配列数式になりますので

E2セルに数式を貼り付けた後、F2キーを押すか、数式バー内で一度クリックします。
（数式は前回のままで大丈夫だと思います）

そうすると編集可能になりますので
Shift＋Ctrl＋Enterキーを押してください。
数式の前後に｛　｝マークが入り配列数式になります。
それをF2セルにオートフィルでコピーします。

最後の範囲指定　→　オートフィルで下へコピーは前回のままでOKかと思います。

どうも何度も失礼しました。m(__)m

補足

どうも、返事が遅くなってすいません。
いろいろ試しましたが、
どうも関数を理解できてないので？？です。
最初の関数でかなりいいところまで行きましたが、2行続けて同じ日付が入ったとき、期待値と違う値が帰ってくるようです。
具体的には2列目に日付値が帰ってこない、という現象でした。

回答No.4

tom04

こんばんは！
余計なお世話かも知れませんが・・・

>1日あたりの走行時間、走行距離を計算したいのですが・・・

とありましたので
勝手に↓の画像のように表を作ってみました。

Sheet1のA列に作業用の列を挿入させてもらっています。
（別にA列でなくても構いません）

Sheet1のA2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
という数式を入れ、オートフィルで下へコピーします
Sheet2の数式が10000行まで対応するようにしていますので、
10000行くらいまでコピーしても構いません。

次にSheet2のA2セルに
=IF(COUNT(Sheet1!A:A)>=ROW(A1),INDEX(Sheet1!$B$2:$B$10000,SMALL(Sheet1!$A$2:$A$10000,ROW(A1))),"")

B2セルに
=IF($A2="","",SUMPRODUCT((Sheet1!$B$2:$B$10000=Sheet2!$A2)*(Sheet1!C$2:C$10000)))
として、C2セルまでオートフィルでコピーします。
（B2・C2セルともにセルの表示形式は修正しておいてください）

そして平均速度に関しては合計してはまずいので
D2セルに
=IF(A2="","",SUMIF(Sheet1!$B$2:$E$10000,Sheet2!A2,Sheet1!$E$2:$E$10000)/COUNTIF(Sheet1!$B$2:$B$10000,A2))

E2セルに
=IF($A2="","",INDEX(Sheet1!F$2:F$10000,MAX(IF(Sheet1!$B$2:$B$10000=Sheet2!$A2,ROW($A$1:$A$9999)))))
とし、E2セルのフィルハンドルでF2セルまでコピーします

最後にA2～F2セルを範囲指定し、
F2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、長々と書き込みましたが、
参考になれば幸いです。
他に良い方法があれば読み流してくださいね。m(__)m

回答No.3

某HN クロメート(Chromate)（@CoalTar）

#1です
>対象範囲を可変、の部分がよくわからなかったので教えていただけないでしょうか？

[Ctrl]+[F3] 名前の定義
名前 PT範囲
参照範囲 =$A$1:INDEX($F:$F,COUNTA($A:$A))

ピボットテーブルウィザードの2/3
範囲 : PT範囲

ピボットテーブルの更新は ピボットテーブルを右クリックして
データの更新

補足

解説ありがとうございます。
とりあえず作ってみました。
が、できれば入力したら即値が帰ってくるような方法が好みです。

意味を理解してテストするのに時間がかかってしまい、お返事が遅れました。
どうもありがとうございます。

回答No.2

KURUMITO

シート1にお示しのような記録があるとして、シート1ではＡ列からＦ列までに入力されており、1行目には項目名が2行から下にデータが並んでいる、あるいはこれからデータがどんどん追加されていくとします。
Ｈ列は作業列としてＨ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(A1<>A2,MAX(H$1:H1)+1,""))

シート2にはお求めのデータを表示させるとします。
1行目にはシート1と同じ項目名をコピーして貼り付けます。
Ａ2セルには次の式を入力します。

=IF(COUNTIF(Sheet1!$H:$H,ROW(A1))=0,"",INDEX(Sheet1!$A:$F,MATCH(ROW(A1),Sheet1!$H:$H,0),1))

Ｂ2セルには次の式を入力し、Ｃ2セルまでオートフィルドラッグします。

=IF($A2="","",SUMIF(Sheet1!$A:$A,$A2,Sheet1!B:B))

Ｄ2セルには次の式を入力します。

=IF($B2="","",C2/B2/24)

Ｅ2セルには次の式を入力しＦ2セルまでオートフィルドラッグします。

=IF($B2="","",MAX(INDEX(Sheet1!$A:$F,MATCH($A2,Sheet1!$A:$A,0),COLUMN(E2)):INDEX(Sheet1!$A:$F,IF(COUNTIF(Sheet1!$A:$A,$A2+1)>0,MATCH($A2+1,Sheet1!$A:$A,0)-1,MATCH($A2+1,Sheet1!$A:$A,1)),COLUMN(E2))))

Ａ2セルからＦ2セルまでを選択してからＦ2右下隅のフィルハンドルを下方にドラッグします。

最後にＡ列の書式の表示形式を日付に、Ｂ列を時刻に、その他の列の表示形式を標準にします。これでシート1にデータが追加されてもシート2では1日ごとのデータとして自動的に追加されていきます。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

>1日あたりの走行時間、走行距離を計算したいのですが、
なら ピボットテーブルを使われたらいかがですか？
添付図参照。

＞データの個数＝行数は今のところ235行でどんどん増やしていきたい
名前の定義を使い、対象範囲を可変として、ピボットテーブルの範囲とすれば良いです。更新の必要があります。
=$A$1:INDEX($F:$F,COUNTA($A:$A))

本来の質問かな
＞これを7月12日は1日とカウントしたいのですが、
＞この場合関数などどのようなものを使えばいいでしょうか？
=COUNT(1/FREQUENCY(A2:A5,A2:A5))
添付図参照

補足

早速の回答ありがとうございます。
なるほど、ピボットテーブルですか。
対象範囲を可変、の部分がよくわからなかったので教えていただけないでしょうか？