EXCEL 日曜日の個数を数える

質問

E1に「2004」、E2からE31にはその年の祝日・振替休日・国民の休日が数式により入っています。
E2からE31のうち、日曜日が何日あるかをE36に式で数えたいのですが、どういう式を書けばできますか？
作業列を使えば曜日を取り出してCOUNTIFでできるのはわかるのですが、F列以降にも別の年が入っていますので、今回は作業列は使えません。
また、VBAでユーザ関数を作るのも今回は事情がありできません。
配列数式（？）を使えば出来るのかなとも思うのですが、よくわかりません。
EXCEL2000です。
よろしくお願いします。

回答No.7

mshr1962 · ベストアンサー

#1,3,6のmshr1962です。
>「アイテムの種類のカウント」
1/COUNTIF(範囲,範囲)の部分がポイントです。
あるアイテムが5個あるとしたら
1/COUNTIF(範囲,アイテム)=1/5
でこれが5個存在するので5倍されると1に戻ります。
つまりアイテム毎に個数/個数=1の計算になるので
結果が種類のカウントになります。

お礼

回答ありがとうございます。
な～るほど、すごいですね～
こういう発想は僕にはできそうも無いので、また何かありましたらお知恵をお貸しください。
ありがとうございましたヽ(^。^)ノ

回答No.6

mshr1962

#1,3のmshr1962です。
SUMPRODUCTの解説ですが、この関数は配列の積の和を求めるものです。
配列に条件式を使った場合TRUEを1、FALSEを0として扱います。
=COUNTIF(A1:A100,1)=SUMPRODUCT((A1:A100=1)*1)
=SUMIF(A1:A100,1,B1:B100)=SUMPRODUCT((A1:A100=1)*1,(B1:B100))
に置き換えることができます。
またCOUNTIF,SUMIFでは複数条件の場合に配列関数にするか、二つの数式の和・差にする必要がありますが
SUMPRODUCTはそのまま使える利点があります。
また結果が数値か論理値の配列になれば複雑な条件でもカウント・集計が行えます。
上記でSUMPRODUCT内で"*1"しているのは論理値が単独の場合、数値にならないためです。
複数の配列の場合は"*"はAND条件、"+"はOR条件として計算できます。

例
りんごとみかんの集計
=SUMPRODUCT(((A1:A99="りんご")+(A1:A99="みかん"))*(B1:B99))
アイテムの種類のカウント（ただし範囲内にブランクがあるとエラー）
=SUMPRODUCT(1/COUNTIF(A1:A99,A1:A99))
特定の範囲のカウント（60以上80未満）
=SUMPRODUCT((A1:A99>=60)*(A1:A99<80))

お礼

回答ありがとうございます。
SUMPRODUCTの解説と実例、とってもよくわかりました。勉強になります。
もう一つだけ、教えていただけませんか？

例の中の「アイテムの種類のカウント」なんですが、やってみたら確かにちゃんとカウントしてくるんですが、この式でなぜできるのかが理解できません(>_<)
何度も申し訳ありませんが、よろしくお願いします。

回答No.5

imogasi

#4のお礼部分の、配列数式の「解説またはサイト紹介」
http://pc21.nikkeibp.co.jp/special/hr/
が良いでしょう。
件数カウントのパターンの要点は
　　ＩＦを使って、　真の時１、偽のとき０に置き変える
　　　　偽　　０　　ＩＦ（Ａ１＝ＸＸ，１，０）
　　　　真　　１　　ＩＦ（Ａ２＝ＸＸ，１，０）
　　　　真　　１　　ＩＦ（Ａ３＝ＸＸ，１，０）
　　　　偽　　０　　ＩＦ（Ａ４＝ＸＸ，１，０）
　　　　真　　１　　ＩＦ（Ａ５＝ＸＸ，１，０）
　　＋）真　　１　　ＩＦ（Ａ６＝ＸＸ，１，０）
　　－－－－－－－
　　　　　　　４　＜－－ＳＵＭ
　　上の６行分のＩＦ文は、配列数式では
ＩＦ（Ａ１：Ａ６）＝ＸＸのように、（　）と「：」を使って表現上で、１文に縮約される

お礼

回答ありがとうございます。
サイト見てきました。配列数式とSUMPRODUCTについて、実例をあげて、段階を踏んで、図をたくさん使って説明されていて、とても良くわかりました。
またわからないことが出ましたら教えてください(^ｏ^)丿

回答No.4

imogasi

＞数式により入っています
これはどう言う意味ですか。式や数（日付シリアル値は、突き詰めると、数ですがそれは別にして）に引っかかります。日付なら分かります。
エクセルの規定路線の2004/9/20などと日付シリアル値ではいっているのですか？
もしそうなら、配列数式で
=SUM(IF(WEEKDAY(A1:A31)=1,1,0))
と入れて、ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲで出そうです。

お礼

回答ありがとうございます。
例えば元日（E2）のセルですと「=DATE(E$1,1,1)」という式が入っています。

教えていただいた式を「=SUM(IF(WEEKDAY(E$2:E$15)=1,1,0))」としましたら、ちゃんと出ました。
ありがとうございますヽ(^。^)ノ

前の方のお礼に書いた事と似ているのですが、配列数式についてもヘルプを見ても意味がいまいち理解できなかったので、もしよろしかったら解説またはサイト紹介していただけませんでしょうか。
よろしくお願いします。

回答No.3

mshr1962

#1のmshr1962です。
>でも#VALUE!になってしまいます(>_<)
E2:E31にあるのは文字列の日付ですか？
"11月 3日"のようなものがある場合は#VALUE!になります。
"1月11日"は計算されますが年度は現在の年で計算されます。

下記の数式の場合はエラーしませんでした。（1部省略）
元旦=DATE(E$1,1,1)
成人の日=DATE(E$1,1,15-WEEKDAY(DATE(E$1,1,7),2))
建国記念日=DATE(E$1,2,11)
春分の日=DATE(E$1,3,INT(0.24242*E$1-INT(E$1/4)+35.84))
・(省略)
海の日=DATE(E$1,7,22-WEEKDAY(DATE(E$1,7,14),2))
敬老の日=DATE(E$1,9,22-WEEKDAY(DATE(E$1,9,14),2))
秋分の日=DATE(E$1,9,INT(0.24204*E$1-INT(E$1/4)+39.01))
体育の日=DATE(E$1,10,15-WEEKDAY(DATE(E$1,10,7),2))
・(省略)
振替休日=IF(WEEKDAY(直上のセル)=1,直上のセル+1,"")
の様なら正常にカウントします。
この場合日曜と重なる可能性のあるのは春分・秋分・ＧＷ・文化・勤労感謝・天皇誕生日だけですが．．．

#2の方へ#1の数式は配列数式ではありません。
SUMPRODUCT自体が配列を計算する関数です。

お礼

回答ありがとうございます。
E2:E31にあるのは、テストしていただいたのとほぼ同じ数式で出したもので、表示形式は「mm/ddaaa」としてはいますが、値としてはシリアル値です（よね？）。

E2:E15には元日から天皇誕生日まで、E16:E29には振替休日、E30:E31には国民の休日があります。
E16:E31は該当日が無い場合をIFで「""」にしているために、これを含んだために#VALUE!になったようです。

教えていただいた数式の範囲を「E$2:E$15」にしましたら、ちゃんとカウントしてくれました。
ありがとうございましたヽ(^。^)ノ

ところで、難しい質問に対してSUMPRODUCTを使った見事な回答をなさっていらっしゃるのをよくお見かけしておりました。
僕も使いこなしたく、SUMPRODUCTのヘルプを見たのですが、いまいち意味が分からないのです。

もしよろしければ簡単な例など挙げて解説していただけませんでしょうか。
またはよくわかるサイトを紹介していただけませんか。
あつかましいですが、よろしくお願いいたします。

回答No.2

BLUEPIXY

#1の補足ですが、
配列数式を入力するには、
該当の式を入力後
SHIFT+CTRL+ENTER
で入力します。
（入力した式に｛｝がつきます）

お礼

回答ありがとうございます。
{}がつきましたが結果は#VALUE!のままです(>_<)

回答No.1

mshr1962

E列に日にち（年月日）があるなら
=SUMPRODUCT((WEEKDAY($E$2:$E$31)=1)*1)
でカウントできます。

お礼

回答ありがとうございます。
でも#VALUE!になってしまいます(>_<)