[[20100916080036]]　『名前をつけた範囲から条件にあった値のセルの個数』（だい）　ページの最後に飛ぶ

 『名前をつけた範囲から条件にあった値のセルの個数を出したい』（だい）

 おはようございます。

 簡単なことのようなのですが、どうしても式ができず
 悩んでいます。全文検索もしたのですが出てきませんでした。

 シート2に「授業日」という名前のついた範囲（A2:A150)があります。
 これは、今年の4月から来年の3月までの年間の授業日だけを
 全て抜き出した（入力した）ものです。

 シート1のB5セルに、7月の授業日の数を出したいのです。

 今自分がやってみたのはこの式ですが答えは0です。

 =COUNTIF(授業日,MONTH(授業日)=7)

 「授業日」は日付で表示形式はm"月"d"日"(aaa)です。

 初心者なもので関数の理論がわかっていないのかもしれません。

 どなたかご教示いただけますでしょうか。

 どうぞよろしくお願いいたします。

 XPでエクセル2003です。

 =SUMPRODUCT(N(MONTH(授業日)=7))
 こんなのでうまく行きますか？
　
（純丸）(o^-')b

 COUNTIFでやるなら、7/1以上から8/1以上を引けばいいのかな？

 =COUNTIF(授業日,">="&DATE(2010,7,1))-COUNTIF(授業日,">="&DATE(2010,8,1))

 純丸サンのがいいわなあ（笑）
 (1111)

 COUNTIF関数で求める場合、

 =SUM(COUNTIF(授業日,{">=2010/7/1",">=2010/8/1"})*{1,-1})
 でも求められます。
 (独覚)

 >純丸サンのがいいわなあ

 そんなことないです。(1111)さんのが好きです。
 （奈々氏）

 おや、ありがとう奈々氏サン。
 わかりやすいことだけが取り柄だぜ(^ー^v
 (1111)

 お返事が大変おそくなりすみませんでした。

 純丸さん、1111さん、独覚さん、奈々氏さん、ありがとうございました。

 ３通りやってみましたが全部正しい答えがでました！！!

 ただ、せっかくわかりやすく書いていただいたのですが
 私にはどうも理解ができないです。

 純丸さんのが数式が短くて自分も使ったMONTHが入っているので
 取り組みやすいかと思ったのですが、

 SUMPRODUCTのヘルプには

 「引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返しま す。」

 とあり、私の理解も足し算をする関数だったのですが
 ここでSUMPRODUCTを使う理由はなんでしょうか。

 また、"N("というのは関数ですか？Ｎ関数というのはないようですが。

 よろしければこの2点についてご説明をいただければ幸いです。

 また、1111さんと独覚さんの、7月以上8月以下という発想は
 なんとなくわかるのですが、「"」や「">="&」とかの使い方や意味が
 わかりません。「{」は配列関数の時に使われるのかなあとは
 思うのですが・・・

 私は「""」は文字列を囲んだり、IFなどでブランクを意味したりという
 くらいしか意味がわからないのです。

 全てを説明してくださいというのは皆様にご迷惑かと思いますが
 少しだけご教示をいただければ嬉しいです。または、"の使い方とか
 &や{などの意味やルールを学べるサイトなどご紹介いただければと
 思います。

 いずれにしましても、そのまま数式を使わせていただくには
 全く問題がないので本当にお礼を申し上げます。

 ありがとうございました。

 お礼が遅くなりすみませんでした。

 だい

 とりあえず自分の分（？）を。

 >「"」や「">="&」とかの使い方や意味がわかりません。

 とりあえずCOUNTIFのヘルプにこう書いてある。
 「検索条件：（中略）式および文字列を指定する場合は、">32"、"Windows" のように、半角の二重引用符 (") で囲む必要があります。」

 つまり今回「>=」を使いたかったので、このルールにのっとって、「"」で囲った。
 で、その「>=」の続きが普通の数値なら数値ごと「"」で囲えばよかった。
 2010/7/1のシリアル値は40360だから、「">=40360"」と書いてもよかったんだけど、わかりにくいから日付を入れたかった。
 でも日付を「"」で囲う、つまり、「">=2010/7/1"」と書くと文字列扱いになってしまい、「2010/7/1」という文字列以上を探しに行ってしまう。
 そこで、DATE関数で日付をシリアル値に直している。
 関数を検索条件に入れるときは「"」で囲う必要がないんで、">=DATE(2010,7,1)"とは書かない。
 そこで「">="」とDATE(2010,7,1)をくっつけるために、＆の演算子を使う。

 というわけで、「">="&DATE(2010,7,1)」という検索条件になったわけ。

 「"」は文字列を囲むわけだけど、関数ごとにいろいろなルールもある。
 COUNTIFはワイルドカードも「"」で囲めば使えるし、面白いよ。

 あと、N関数ってあるよ。
 なんでかヘルプの検索では出てこないけど、「関数の挿入」にちゃんとあるし、その関数のヘルプボタンを押すと、ヘルプも出てくる。
 かわいそーな関数。
 (1111)

 ＞「">=2010/7/1"」と書くと文字列扱いになってしまい、「2010/7/1」という文字列以上を探しに行ってしまう。

 本当？

 (半平太) 2010/09/17 09:40

 衝突(汗

 関数では引数にセル範囲を指定したばあい、そのうちの一つのセルしか対象としないものとセル範囲全体を対象とするものがあります。

 正確には下記のリンク先で説明されている『共通部分参照』が働くかどうかなのですが。

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

 で、SUMPRODUCT関数を使うとその一つのセルしか参照しない関数で配列全体に対して計算を行うようにできます。

 それによって
 MONTH(授業日)=7
 が「授業日」全体の範囲に対して行われ
 MONTH(A2)=7;MONTH(A3)=7;MONTH(A4)=7;…;MONTH(A150)=7
 という配列になります。

 ここで「MONTH(A2)=7」は「TRUE」(真)、「FALSE」(偽)という論理値を返します。
 SUMPRODUCT関数では論理値は計算対象外ですのでN関数を使うことで「TRUE」→「1」、「FALSE」→「0」に変換しています。
 そして最終的にSUMPRODUCT関数で合計することで「MONTH(授業日)=7」が成立する個数が求められます。

 なお、N関数に関してはヘルプの「目次」から「データを処理する」―「関数リファレンス」―「情報関数」で説明があります。

 つぎにCOUNTIF関数の場合ですが、以下はヘルプからの引用です。

 >検索条件
 >  計算の対象となるセルを定義する条件を、数値、式、または文字列で指定します。
 >  式および文字列を指定する場合は、">32"、"Windows" のように、半角の二重引用符 (") で囲む必要があります。
 つまり
 A1からA10セル>=10
 のような場合
 =COUNTIF(A1:A10,">=10")
 と条件を『文字列』で指定することになります。
 なので「"」でくくられているわけです。

 また、私の式の場合ですが{}で囲んだ配列定数を条件に与えることで

 =SUM(COUNTIF(授業日,">=2010/7/1")*1,COUNTIF(授業日,">=2010/8/1")*-1)
 という式とおなじ意味になっています。

 配列式に関しては上でもあげていますが下記のサイトが参考になるかと。

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

 わかりづらい所があれば遠慮せずに質問してください。

 追記
 COUNTIF関数の条件式の場合すでに「"」で囲まれているため日付や数値を表わす文字列は其の表わしている日付や数値に変換されて検索されます。

 追記の2
 >「"」で囲まれているため
 ではなくCOUNTIF関数、SUMIF関数の仕様で、ですね。
 (独覚)

 おや、あれ？　昔日付をそのまま入れたとき、ヘンな数字になったと思ってたんだが・・・勘違い？
 んじゃ
 =COUNTIF(授業日,">=2010/7/1")-COUNTIF(授業日,">=2010/8/1")
 でいいですね。
 すみません、覚え間違いしてました。

 半平太さん、ご指摘ありがとう。
 独覚さん、解説ありがとう。
 (1111)

 みなさん、詳細に教えていただきありがとうございます。

 1111さんと独覚さんの説明はとてもわかりやすかったです。

 これまで式と関数をあまり意識せず混同していたので、違いを再認識しました。

 そして独覚さんの説明から「式」は「""」で囲んで(SUMPRODUCTとSUMIFの場合)「文字列」として
 認識させるという意味もよく理解できました。

 1111さんの「">="&DATE(2010,7,1)」の「&」の使い方もなるほどです。今回はDATE()に直さなくても
 いいので「式」を""に修正しましたが、今後「&」の使用が必要になった場合に助かります。

 N関数に関してもヘルプを見てみましたがヘルプの説明では、いったい何に使えばいいのか
 理解不能でしたが、独覚さんの説明ですっきり分りました。

 以前にこちらでTRUE,FALSEの理論値を計算できるように*1とするという説明を
 拝見しましたが、すぐにこのことだと思い自分なりに以下のように書き直してみました。

 =SUMPRODUCT(N(MONTH(授業日)=7))は
 =SUMPRODUCT((MONTH(授業日)=7)*1)とも書ける！！

 それにしてもヘルプを読み取る能力が必要ですね。

 最後の配列ですが、独覚さん、いいサイトをご紹介いただきありがとうございます。
 配列は全くと言っていいほど初めてですのでこれから全部読んで勉強します。
 確かになぜSUMPRODUCTを使うのかという答えがありました！

 一つ分らないのは独覚さんの

 =SUM(COUNTIF(授業日,">=2010/7/1")*1,COUNTIF(授業日,">=2010/8/1")*-1)
 =SUM(COUNTIF(授業日,{">=2010/7/1",">=2010/8/1"})*{1,-1})

 ですが、">=2010/7/1"と">=2010/8/1"とどちらも等号が「>=」で、2010/7/1以上（以降）で
 >=2010/8/1以上（以降）???と解釈してしまうのですが、これは配列を勉強すれば
 理由がわかるようになるのでしょうか。

 それからもう一つ問題があります。

 この表を毎月使いたいのですが、8月の場合はどうしたらいいのでしょうか。

 純丸さんの

 =SUMPRODUCT(N(MONTH(授業日)=7))

 を使わせていただいた場合

 例えばB4に「８月」と文字列で入力して

 =SUMPRODUCT(N(MONTH(授業日)=B4))

 としてみましたが、答えは0になってしまいます。"B4"としてもだめです。

 B4を標準の数値で8としていれば正しい答えが出るのですが。これではちょっと。

 こうなると、1111さんや独覚さんの式でもどのようにすればいいのか
 わからなくなります。

 もうしわけありませんが、もう一度ご指導いただけますでしょうか。

 よろしくお願いいたします。

 教えていただいたことを確かめながら返事をしたいのでいつも時間がかかって
 すみません。

 だい

 > =SUMPRODUCT(N(MONTH(授業日)=7))

 この数式には注意すべき点があります。
 範囲内に未入力セルを含み、かつ1月をカウントする場合です。

 　　　A
 1　　1/20
 2　　2/20
 3　　3/20
 4
・
・
10　　　　　　　※A4:A10は未入力

 1月の日付は1つしかないのに　=SUMPRODUCT(N(MONTH(A1:A10)=1))　は「8」になります。
 未入力セル　→　シリアル値「0」　→　1900/1/0　なので
 未入力セルを1月としてカウントしてしまうってことですね。

 他に、範囲内に数式の「""」を含む場合、エラーになるということもあります。

 そのことをふまえつつ、さらに
 > 例えばB4に「８月」と文字列で入力して
 ということであれば、TEXT関数を使った方がいいでしょう。

 >「８月」

 数字部分も全角なら
 =SUMPRODUCT(N(JIS(TEXT(授業日,"m月;;"))=B4))　
 =SUMPRODUCT((JIS(TEXT(授業日,"m月;;"))=B4)*1)

 半角なら
 =SUMPRODUCT(N(TEXT(授業日,"m月;;")=B4))
 =SUMPRODUCT((TEXT(授業日,"m月;;")=B4)*1)

 （R）

 Rさん、ご教示ありがとうございます。

 TEXT()を使うのは、B4の「8月」が日付ではなく単なる文字列だから
 「授業日」も文字列に変換して比較するということですね！！！
 4種類全部試して成功しました。

 ただ恐縮ですが、

 TEXTのヘルプを見てやってみました。表示形式に""をつけるということが
 分りましたが、"m月;;"の「;;」の意味を教えていただけますでしょうか。

 それと、月が全角ならJIS（これも初めてです）を使っていますが、
 JISのヘルプを見ると「文字列内の半角 (1 バイト) の英数カナ文字を
 全角 (2 バイト) の文字に変換します。」とありますが、文字列はすでに
 全角なのに「半角を全角に変換」というのが分りません。

 だんだん理屈っぽくなってすみません。お返事をいただければありがたいです。

 だい

 >ですが、">=2010/7/1"と">=2010/8/1"とどちらも等号が「>=」で、2010/7/1以上（以降）で
 >>=2010/8/1以上（以降）???と解釈してしまうのですが、これは配列を勉強すれば
 >理由がわかるようになるのでしょうか。
 これは「">=2010/8/1"」のほうには「-1」をかけている事に注目してください。

 「">=2010/7/1"」の個数と、「">=2010/8/1"」の個数に「-1」をかけたものを足すということは
 「">=2010/7/1"」の個数から「">=2010/8/1"」の個数を引くことになります。
 つまり式の意味そのものは1111さんのお書きになった式と同じことになります。
 (独覚)

 独覚さん、ありがとうございます。

 お恥ずかしいですが、？？？と！！！の繰り返しです。

 SUMで足し算をしているのだから*1と-1で調整をしているのですね！！！

 8月まで考慮に入れないで質問をしてしまいすみませんでした。でも
 すごい勉強になりました。

 みなさまのおかげで本当にすごいことを勉強させていただいています。

 ありがとうございます。

 >「;;」の意味

 例えばですけど、セルの表示形式のユーザー定義が
 ↓ のようになっているとして、意味わかりますか？

 +#,##0;-#,##0;"±"0
　
 これはセミコロン「;」で区切って、3つの書式を設定しています。

 +#,##0;-#,##0;"±"0
 　(1)　　(2)　　(3)

 セルの値に応じて
 (1)　正の数の書式
 (2)　負の数の書式
 (3) 「0」の場合の書式
 ※もう一つ、文字列の場合の書式も指定できる

 セルの値が正の数なら　+1,234
 　　　　　負の数なら　-1,234
 　　　　　0　　 なら　±0
 のように表示されます。

 =TEXT(A1,"m月")　のように「;;」をつけないと
 A1に何も入力されていない場合「1月」になります（理由は前の回答を参照のこと）

 これを　=TEXT(A1,"m月;;")　にすると、負の数の場合と「0」の場合は書式を何も設定しないことになりますので
 A1が未入力の場合（値が「0」の場合）「1月」にはならず、見た目空白になります。
　
　
 > 文字列はすでに全角なのに「半角を全角に変換」というのが分りません。

 B4は「８月」のように数字部分は全角なのでしょうか？

 JIS関数はTEXT関数の戻り値を全角にしているのであって、B4の値にまで及んでいませんよ。
 JIS(TEXT(授業日,"m月;;"))　と　B4　を比較しているということです。

 逆にB4を半角にして　=SUMPRODUCT(N(TEXT(授業日,"m月;;")=ASC(B4)))　でもいいかもしれません。

 ※B4を半角にして、というのは、B4の実際の値は「８月」のように全角のまま
 　数式上で半角にするという意味です。

 （R）

 Rさん、ありがとうございます。

 こちらの表現があいまいですみません。

 Rさんが教えてくださった以下の例は、B4の「８」が全角か
 半角か自分が明確にしていなかったから、どちらにも対応できるように
 それから、両方にN()を使った場合と*1を使った場合で合計4種類式を
 提示していただいたのだと理解しています。

 >数字部分も全角なら
 >=SUMPRODUCT(N(JIS(TEXT(授業日,"m月;;"))=B4))　
 =SUMPRODUCT((JIS(TEXT(授業日,"m月;;"))=B4)*1)
 半角なら
 =SUMPRODUCT(N(TEXT(授業日,"m月;;")=B4))
 =SUMPRODUCT((TEXT(授業日,"m月;;")=B4)*1)

 それで、B4に半角で「8月」とした場合と全角で「８月」とした場合の
 両方をそれぞれ4種類の式で試したみましたが、全てご教示の通りの
 結果でした。

 ただ、

 >数字部分も全角なら
 >=SUMPRODUCT(N(JIS(TEXT(授業日,"m月;;"))=B4))　
 >=SUMPRODUCT((JIS(TEXT(授業日,"m月;;"))=B4)*1)

 のJIS()の目的が
「文字列内の半角 (1 バイト) の英数カナ文字を
 全角 (2 バイト) の文字に変換します。」

 と書いてあったので、この場合・・・

 すみません。今分りました。TEXT()の結果を全角にしてB4と
 比較したいのか、それともTEXT()の結果を半角にしてB4と
 比較したいのか、ということですね。

 勉強が足りないばかりにすみませんでした。

 本当にお世話になりありがとうございました。

 これからもよろしくお願いいたします。

 だい

 Rさん、

 表示形式の詳細な説明もありがとうございました。今まで表示形式を
 みながら意味があまりわからず、分りやすい形式ばかりを使っていましたが
 ;の意味がわかりました。

 関数のなにもいれない引数に,,を入れるのと似ていますね。

 たくさんのことを勉強させていただきました。

 みなさま、丁寧なご指導どうもありがとうございました。感謝です。　だい

コメント返信：

[ 一覧(最新更新順) ]

[[20100916080036]] 『名前をつけた範囲から条件にあった値のセルの個数』（だい） ページの最後に飛ぶ

[[20100916080036]]　『名前をつけた範囲から条件にあった値のセルの個数』（だい）　ページの最後に飛ぶ