エクセルで時間まで含んでいる日付のセルの重複を除いた個数を出したい

質問

エクセルで時間まで含んでいる日付のセルの重複を除いた個数を出したいのですが

B列の行に日付が8/3と言う表示形式でずっと並んでいます。しかしこのセルは日付だけでなく訳あって実際には時間も含んでいます。 標準表示でみると「40028.1234」みたいな感じになっているわけです・・・

それで、B列の行にある日付は重複している日付もあります。それを重複を除いて数えたいのですが・・・
色々な関数で試したのですがLEN(B3:B100,5)などを入れると使えなくなってしまう関数だったりして結局できませんでした・・・

表は下記のようになっています。

　　　　Ａ　　　B　　　C　　　D　・・・
３　　　8/3
４　　　8/3
５　　　8/4
６　　　8/6
７　　　8/6
８　　　8/6
９　　　8/8
・
・
・

上記の結果を重複を除いて「4」と出したいのですが・・・

３より上の行には日付とは関係ないものが入力されており
９より下の行にはこれから日々増えていく行があり現時点では未入力です
何行まで増えるかわからないのでB3:B100みたいな感じで指定しておきたいのですが・・・

よろしくお願いします。

回答No.8

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

>-(INT(MAX(シート名!B11:B100))<MAX(シート名!B11:B100))
>部分を削除すると正しい値になるのですが・・・・
もう一度、検証してみました
=COUNT(1/FREQUENCY(INT(B11:B20),INT(B11:B20)))
のように書けばよさそうです。
つまり、
=COUNT(1/FREQUENCY(INT(シート名!B11:INDEX(シート名!B:B,10+COUNT(シート名!B11:B100))),
INT(シート名!B11:INDEX(シート名!B:B,10+COUNT(シート名!B11:B100)))))

をC11:C21のセル範囲を選択してから
数式バーに =FREQUENCY(INT(B11:B20),INT(B11:B20))
を入力し[Ctrl]+[Shft] ＋[Enter] で確定します
添付図参照

お礼

ばっちりできました！！
本当にお手数おかけして申し訳ありませんでした。
これでやっと問題が解決しました。
本当にありがとうございました。
ペコリ(o_ _)ｏ))
また、質問することがあるかもしれませんがその時はよろしくお願いいします。
σ(^◇^;)ヒヤアセ

回答No.7

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

検討していてさらに間違ってました m(_ _)m
=COUNT(1/FREQUENCY(シート名!B11:INDEX(シート名!B:B,10+COUNT(シート名!B11:B100)),
INT(シート名!B11:INDEX(シート名!B:B,10+COUNT(シート名!B11:B100)))))
-(INT(MAX(シート名!B11:B100))<MAX(シート名!B11:B100))
ROUNDじゃなくてINTでした。

＞2を10にしたのですがよろしいのでしょうか？
OK

＞「Ctrl+Shift+Enter」を押して配列にしても、
[Ctrl]+[Shft] ＋[Enter] で確定は、配列として認識させるための操作です。つまり、不要です。

たとえば B11～B20セルまで日付が入っているなら
=FREQUENCY(B11:B20,INT(B11:B20))
を C11～C21セルまで選択後、上記を数式バーにコピペ。
[Ctrl]+[Shft] ＋[Enter] で確定すると、頻度が算出されます。
ここで、一つ多く算出されることを忘れていたため、最後のMAX関数を使った数式を追加しました。詳しくはヘルプを参照してください。
また、D11セルに=INT(B11)として下へオートフィルしておくと少しわかりやすいかも

補足

度々ありがとうございます。
上記の式で試してみたのですが・・・結果は、前回同様実際の値より１足りない値が表示されてしまいました・・・

・・・ちなみに、最後の
-(INT(MAX(シート名!B11:B100))<MAX(シート名!B11:B100))
部分を削除すると正しい値になるのですが・・・・
これでは、問題あるでしょうか？そのうちズレが起きてしまうのでしょうか？・・・自分の場合は、1つ多く算出されていないってことでしょうか？

ちなみに、
=FREQUENCY(B11:B20,INT(B11:B20))
を試してみたところでた値は、
　・・　　B　　　C　　　D　・・・
11　　　8/3　　　2
12　　　8/3　　　1
13　　　8/4　　　1
14　　　8/6　　　3
15　　　8/6　　　2
16　　　8/6　　　1
17　　　8/8　　　1
・
こんな感じでした。（C列）

知識が足りないため本当にお手数おかけして申し訳ありません。

回答No.6

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

#1,3,5です
間違いを発見しましたので修正報告です
このままでは配列がひとつ多くなり、その分の調整が必要でした。
=COUNT(1/FREQUENCY(B3:INDEX(B:B,2+COUNT(B3:B100)),ROUND(B3:INDEX(B:B,2+COUNT(B3:B100)),0)))-(INT(MAX(B3:B100))<MAX(B3:B100))

m(_ _)m

補足

ありがとうございます。
試してみたのですが・・・結果は実際の数値より1個足りない数値が表示されてしまいました・・・式が複雑なので修正箇所を間違えているのでしょうか？・・・あと、気なるところとしては、日付部より上にAB列を結合した個所があります。その場合でも B:B の指定で問題ないでしょうか？

実際には日付はB列の11行目から下に記述されています。
そしてこの数式は違うページで集計しようとしています。
修正してみたのが下記になります。

=COUNT(1/FREQUENCY(シート名!B11:INDEX(シート名!B:B,10+COUNT(シート名!B11:B100)),ROUND(シート名!B11:INDEX(シート名!B:B,10+COUNT(シート名!B11:B100)),0)))-(INT(MAX(シート名!B11:B100))<MAX(シート名!B11:B100))

一つ怪しい修正箇所がINDEXの中の+の前部分　2を10にしたのですがよろしいのでしょうか？

そして一つ気になるのですが、「Ctrl+Shift+Enter」を押して配列にしても、普通に「Enter」だけ押してみても結果は同じなのですがやはり配列にした方が良いのでしょうか？

回答No.5

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

#1,#3です
=COUNT(1/FREQUENCY(B3:INDEX(B:B,2+COUNT(B3:B100)),INT(B3:INDEX(B:B,2+COUNT(B3:B100)))))

=B3:INDEX(B:B,2+COUNT(B3:B100))
の数式がB3～数値の入っているセルの範囲までを表します。
つまり、B3,B4セルに日付が入っていれば =B3:B4 と同じです。

回答No.4

KURUMITO

作業列を作って対応するのがよいでしょう。
Ｂ３セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A3="","",DATE(YEAR(A3),MONTH(A3),DAY(A3)))
その後に日付の重複を除いた数は次の式で求めることができます。
=SUMPRODUCT((B3:B100<>"")/COUNTIF(B3:B100,B3:B100&""))
間に空の行が入っても問題はありません。

回答No.3

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

>B3:B100みたいな感じで指定しておきたいのですが・・・
思い当たることは、B列であることかなあ
=COUNT(1/FREQUENCY(B3:B100,INT(B3:B100)))
XL2007でも確認取れました。
たぶん、自分が何かを見落としてるんだろうなあ。。。
添付図の黄色矢印部分が原因で 解が11か10か違いかも

簡単な解説。
整数化した区間配列の頻度を数え逆数にする
既出の区間は0とカウントされるので逆数にするとエラーになる
エラーになっていないものをCOUNT関数で数える

訳わからないものより、作業列を使って納得のいく数式での解決のほうが良いと思います。

補足

ありがとうございます。
自分の場合なぜうまくいかなかったのか判明しました！
説明に記述していなかったのですが、実は日付が記述されている行より下の日付が入っていない行は表示はされていませんが実際には数式が入っています。
この数式を削除してみたらうまく表示されました・・・
=COUNT(1/FREQUENCY(B3:B100,INT(B3:B100)))
この式で数式のセルは除外みたいなことはできないでしょうか？
よろしくお願いします。

回答No.2

a987654

ＮＯ１の方の数式スマートだなと思い自分でやってみたら
私の能力が追いついてないのか？うまく行きませんでした。

オーソドドクスに補助列を使った方法で・・・
＞B列の行にある日付は
とありますがＡ列で宜しいんですよね。
記載にあるようにＡ３からデータが始まっているものとし、
日付昇順でソートされているものとします。（ソートされて
いなかったらソートしてください）

仮にＺ列を補助列にするとして、Ｚ３セルには１を入れます。
Ｚ４セルに
=IF(INT(A3)=INT(A4),"",1)
Ｚ５以降はＺ４をコピー
あとはＣＯＵＮＴ、ＳＵＭのどちらでもお好きな方をどうぞ。

お礼

ありがとうございます。
理想としては補助列は使いたくなかったのですが、他に方法が見つからない場合は問題なく出来たので使わせていただきます。（⌒▽⌒）
ありがとうございました。

回答No.1

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

=COUNT(1/FREQUENCY(A3:A100,INT(A3:A100)))
でいいかな。参考まで。

INT関数を使って整数化してはどうでしょう？

補足

ありがとうございます。
さっそく試してみたのですが、バージョンのせいか何かわかりませんがうまくいきませんでした。
残念です。
ちなみにバージョンは　2007　です。