COUNTIFSの条件に日付を指定する方法

質問

Excel 2007 の　COUNTIFS 関数　の条件として　２つの月日で範囲
として　データを絞り込むマクロを記述しています

外部からExcel2007形式(.xlsm) 形式のsheet1を読みこみ

sheet2 に記述した条件値とCOUNTIFSの条件値と比較しよう
としています。
尚、比較対象は　sheet1 のＡ列「出荷時期」のデータで
書式設定は　標準となっていて　文字列データです。

【sheet1】
　　　 [A] 　　　 [B]　　　 　　[C]
ヘッダ　出荷時期　　商品分類　　 サイズ　
[1]　 '2014/04　　一般　　　　 Ｌ
[2] '2014/05　　 一般　　　　 Ｍ
[3] '2014/06　　 特別　　　　 Ｍ

【Sheet2】
・B列に　＝COUNTIFS(　Sheet1!$A$1:$A$3,$A2)のように
　条件値の＄A2の"2"の所を行によって変えて入力してます。

　　　　[Ａ]　　　　[Ｂ]　　　 　　　 　　　　　
[１] 出荷時期　　 件数　　 数式の検証＝Excelの評価結果　　　　　　　　　　　　　
[２]　">=2014/04"　　 0　　 """>=2014/04"""
[３]　>="2014/04"　　 3 　 ">=""2014/04"""　　
[４]　>="2014/05" 　 　3 ">=""2014/05"""　　

＝＝＞＞　 ""をとる、演算子と　文字列の間に半角スペースを入れる　と

[５]　>=2014/04　　 　0 ">=2014/04"　　　　
[６]　>= 2014/04　　 3 ">= 2014/04"　　　　
[７]　>= 2014/05　　 　3 ">= 2014/05"

＝＝＞＞　アスタリスクをつける　等。
[８]　>=2014/04*　　 　2 ">=2014/04*"
[９]　>=2014/05*　　 　1 ">=2014/05*" 　　
[10]　>=2014/06*　 　 0 ">=2014/06*"

＝＝＞＞　 演算子と文字列の間に'(シングルクォーテーション)をつける
[11]　>='2014/04　 　 2 ">='2014/04"
[12]　>='2014/05　 　 1 ">='2014/05"
[13]　>='2014/06　 　　3 ">='2014/06"

＝＝＞＞　 文字列操作関数を入れる
[14]　>= & DATE(2014,4,1) 3 ">= & DATE(2014,4.1)"

(注)　文字列関数を使う方法は
http://www.relief.jp/itnote/archives/excel-countif-date-less-greater-than.php
の例を参照しました。この方法は sheet1 の出荷時期データが日付データである必要
があるようです。

上記のようにいろいろと条件値のシンタックスを変えて試してみましたが期待する結果
になりません。

sheet1から入力する　出荷時期の元のデータが文字列の部分に修正を加え、日付
データに修正すると　sheet2 の件数の値が変わるので元データを修正する方法
もあるかもしれません。　ただし　基本的にには　元データには　手を加えない
方法で対処したいと思います。

条件値の指定を　どう記述すれば、利用者が自然に期待した通りの結果が得られるのか
ご存知の方があれば教えてください

以上

回答No.6

MackyNo1 · ベストアンサー

前の回答の訂正と補足です。

>Sheet2 のA2　が　「>=2014/04」では　カウント０
となります。 　なので　>= で比較した時　
　(Sheet1のA1の文字列)　「’2014/04」< (Sheet2のA2の数字や日付シリアル値）である「2014/04」
が成りたっているのでしょうか？　

再度検証したところ、COUNTIF関数では、以下のような挙動をしているようです。

「>=2014/04」のような条件を入力すると、条件式の2014/04の部分が日付（2014/4/1）と自動判定され（この数式の場合は分数と判定されない）、Sheet1のデータの中の数値部分(日付）部分だけの大小を判定するため、文字列データだと該当データがないことになり「0」が返ることになります(2014/4/1以降の日付を入力すると1とカウントされます）。　

一方、「>=2014/04＊」のような検索値が文字列（数値と認識できない形）になっている場合、文字列データだけの大小を判定しますので、数値やシリアル値が入っていても「以下」の条件でもヒットしないことになります。

＞ただ実機では以下のようになり、等号が使えないので見た目が通常の以上や以下の形で表現できないので惜しいですが以下の指定方法でも何とか納得できるレベルだと思います。

たとえば、検索値は「’2014/04」のように入力し、数式を以下のようにすることですっきりした数式にすることができます。 　
=COUNTIF(Sheet1!A:A,">"&A2&"*")

ただし、「以上」の条件の場合に（以下の時は問題ない）1日前の日付を入力できないなら、COUNTIF関数でその値そのもののカウントを加える数式にすることでご希望の処理ができます（開始月と終了月をセルに入力）。

＞この場合は2014/03* とすると文字列同士の比較と評価され、"2014/04 > "2014/03 "となって　カウント３となる　という訳ですよね？

その通りですが、以上の場合は条件値のほうが大きいとみなされるため、上記のような工夫が必要となりますが以下の場合は問題なく計算できます。

回答No.5

mshr1962

Q1　まず基本的な考え方として
　＜中略＞
セルの個数が返される　というこ理解で正しいでしょうか？
A1　その通りです。

Q2　この方法は凄いテクニックだと思います。
重ねてお聞きして恐縮ですがもし演算子も含めて、取り込ませる
ようにするにはどう修正したらよいかご存知でしたら教えてください。

Ａ2　SUMPRODUCT関数では出来ませんね。
　やるのだとしたら、VBAでユーザー定義関数を作ったほうが簡単かもしれません。

Q3　今回のテクニックは　指定できる条件は2個だけでなく＊でつない
　でいくらでも指定できるのでしょうか？

A3　条件は複数つなげるのも可能です。ただ計算にメモリーを使うため
　条件が多かったり、同様の数式を多量に使う場合、EXCELが重くなり
　正常に動かなくなる可能性があることを、考える必要があります。

回答No.4

mt2008

ANo.1です。

> (3)最後にWORK列を削除

削除してしまうと、条件を変更した時にもう一度作業列を作るところからやり直す必要がありますので、非表示にして隠すことをお勧めします。

回答No.3

MackyNo1

文字列として入力した「2014/04」のような文字列はExcelが日付あるいは分数として認識するため（今回のケースは分数と判断します）COUNTIF系の関数で大小を判定する場合は、この数字が基準となります。
一方、COUNTIF系の関数では、文字列の大小を比較することもできますが、この場合は基本的に並べ替えの順で大小が判定されます(文字コード順に大小が判定されますが、今回のように桁数の決まった数字なら問題ありません）。
このような仕様になっているため、文字列は数字や日付シリアル値よりも大きいと判定されるため、今回のような結果になります。

具体的な解決策ですが、「2014/04」を文字列として認識させるために「>=2014/04a」のように適当な文字（空白はダメ）を追加すれば、この条件で大小関係を判定できるようになります。
同様に「'」を適当な場所（例：2014の前）に追加した場合も同じです。
しかし、このように末尾に何か追加した場合は「2014/04」の文字列よりも大きいデータと認識されるため、「2014/04」のデータは含まれない数字になります。

すなわち簡便に対応するなら「以上」の条件ではなく「>2014/04a」のように「よりも大きい」条件にすれば通常の数式で計算することができます。

補足

早速の回答頂き有難うございます。ご教示頂き有難うございます。
恐縮ですが以下の点確認させてください。
Q1　
>文字列は数字や日付シリアル値よりも大きいと判定されるため、
>今回のような結果になります。
　この点は　実機で確認すると
sheet1 のA1：A3が「文字列の数字」で　'2014/04
Sheet2 のA2　が　「>=2014/04」では　カウント０
となります。 　なので　>= で比較した時　
　(Sheet1のA1の文字列)　「’2014/04」　
< (Sheet2のA2の数字や日付シリアル値）である「2014/04」
が成りたっているのでしょうか？　ここがよくわかっていません。

ただ実機では以下のようになり、等号が使えないので見た目が
通常の以上や以下の形で表現できないので惜しいですが
以下の指定方法でも何とか納得できるレベルだと思います。
条件　　　　件数
>2014/03* 3
>2014/04* 2
>2014/05* 1
となります。
　この場合は2014/03* とすると文字列同士の比較と評価され、
"2014/04 > "2014/03 "
となって　カウント３となる　という訳ですよね？

以上

回答No.2

mshr1962

COUNTIFS関数にこだわらないなら

=SUMPRODUCT((DATEVALUE(Sheet1!$A$1:$A$3&"/1")>=$A2)*1)

上記でかつサイズがMなら

=SUMPRODUCT((DATEVALUE(Sheet1!$A$1:$A$3&"/1")>=$A2)*(Sheet1!$A$1:$A$3="M"))

補足

まず　早速の回答有難うございます。ご支援頂き非常に感謝しております。

Q1　まず基本的な考え方として
SUMPRODUCT関数を使って複数条件に合致するセルをカウント
式　SUMPRODUCT((配列1=条件1)*(配列2=条件2))
ということなのですね
&”/1” の所が何をしているのかわかりにくかったのですが
出荷時期のデータ　が　2014/04 という形式なので
"2014/04" & "/1" で　"2014/04/1" にしてから
DATEVALUE関数で　シリアル値にしてそれが　sheet2のA2セル
の日付データ以上かどうか判定　して　*1で　日付以上のデータは
すべて条件2でも真になるということでしょうか
この場合sheet1 のセル範囲というか配列　A1:A3 のなかで条件にあう
セルの個数が返される　というこ理解で正しいでしょうか？

Q2　この方法は凄いテクニックだと思います。ただ今回は
　sheet2 の条件値$A$2の所で　>=の演算子も含めて以上、
　以下など指定させてそれに応じてカウントしたいと思っています
　実機で「>=2014/04」 と入れてみますと個数は0になってしま
　いました。他にも色々値のシンタックス変えてみましたが期待
　するようには評価されませんでした。
重ねてお聞きして恐縮ですがもし演算子も含めて、取り込ませる
　ようにするにはどう修正したらよいかご存知でしたら教えてください。

Q3　今回のテクニックは　指定できる条件は2個だけでなく＊でつない
　でいくらでも指定できるのでしょうか？

上記の点　教えて頂きたくよろしくお願い致します

回答No.1

mt2008

Sheet1に出荷時期を日付のシリアル値で表す作業列を作ってみては？
添付の図では、Sheet1のD列を作業列として使い、D2セルには↓の式を入れて、書式を日付にしています。
=DATEVALUE(RIGHT(A2,7)&"/1")

Sheet2のA列出荷時期は　>=2014/04　の様に記入し、B2セルの式を↓のようにしてあります。
=COUNTIFS(Sheet1!D:D,A2&"/01")

補足

早速の回答頂き有難うございます。ご教示頂き有難うございます。

(１)sheet1 をチェックして、「出荷時期」を格納した
作業列があったら列の最後に作業列を作ってDateValue で日付データ
を入れておく。
(2)実行時に、列を識別し、出荷時期の列だけは最後のWORK 列を
COUNIFSの抽出範囲にして、各行の値と比較
(3)最後にWORK列を削除
ということですね？

有難うございます。　条件値を工夫する方法でやろうとすると無理
がありそうなので、この方式で切り抜けたいと思います

有難うございました

すべての処理が終わったら作業列WORKを削除する