Excel LEFT関数がうまく返せないので教えてください

職員の入職日は必ずしも１日ではありません。2019/4/1、2019/4/30、2019/11/3など
まちまちです。
常勤者もあり、パート（常勤換算で0.8などで示す)の人もいます。
常勤換算数の列には、常勤者は1.0、パートは0.8などと入っています。

月毎にある職種の入職者の常勤換算を出すために、
Ｎ列には、縦に2019/4/、2019/5/、・・・・2019/11
と年月を入れて、ここの文字のLEFT　7字が一致する入職月の、職種の職員の常勤換算数の和
を求めたいのです。

　　　　　　常勤換算範囲　職種範囲　 職種　入職日列範囲　年月セル
　=SUMIFS($F$5:$F$54,$D$5:$D$54,$Q$3,$C$5:$C$54,LEFT($N5,7))
という計算式を入れました。

1～9月も10～12月も返せるように、入職日はすべて//で表示してあります。
左から7字までが一致すれば返すのではないかと思ったのですが、
2019/4/1など、１日付けの職員の常勤換算数は返すのに、2日以降では返してくれません。

うーん、なぜなのか…。

ご教授いただければありがたいのですが。

No.1 ベストアンサー 回答者： zongai 回答日時： 2020/07/06 10:37

入職日は【日付】で入力されており、

N列のLEFTでの検索キーになる「2019/4/」は【文字列】だから、
ではないでしょうか？

【日付】って、「2019/4/1」でも実際は　1900/1/1 を「1」とした経過に数の数字。
「43556（日目）」を【日付】表示で「2019/4/1」としているだけですから。

対処法ですが・・・
N列のセルの書式をユーザー定義で
「yyyy/m」
にしましょう。
1桁の月は「2019/4/」とスラッシュまで含め、2桁の月は含めない
というのも美しくないので。

「2019/4」と入力すると、自動的に「2019/4/1」と入力したことになりますが、になりますが、
表示は「2019/4」となります。

計算式ですが、ちょっと長くなります。

=SUMIFS($F$5:$F$54,$D$5:$D$54,$Q$3,$C$5:$C$54,">="&N5,$C$5:$C$54,"<"&DATE(YEAR(N5),MONTH(N5)+1,1))

日付の部分について
N5に「2019/4」と入力してるとして

①　$C$5:$C$54,">="&N5

N5（2019/4/1）と同じかそれより大きい（N5の日付以降）

②　$C$5:$C$54,"<"&DATE(YEAR(N5),MONTH(N5)+1,1)

N5の1ヶ月後の1日より前

この2つで、2019/4/1　～　2019/4/30　という条件としてます。

お試し下さい。

この回答へのお礼

zongai様、その通りに計算式を入れてみると、見事にすべて正しい数字が返されました。
実は、退職リストもあり、同様に出したかったので、同じように入れてみたところ、正しく数字が入りました。

LEFT関数は日付には使えないと、他のサイトでも書かれていました。

ありがとうございました。
とても勉強になりました。

お礼日時：2020/07/06 11:01

No.2 回答者： fujillin 回答日時： 2020/07/06 10:59

こんにちは

エクセルで日付に見える値には二種類あります。
見て目のままの「文字列」と「日付の意味を持つ数値（シリアル値）」です。

ご質問文に記されていないので、以下は推測になってしましますが・・・
Ｃ列、Ｎ列ともシリアル値になっているのではないでしょうか？
試しにＮ列の「2019/4/」と表示されているセルをアクティブにした状態で、数式バーを見ると「2019/4/1」と表示されていたりしませんか？

上記の仮定が当たっているとして（段々、当たるも八卦になってきますが（笑））、質問者様のなさりたいことは『「年」と「月」とが一致しているものを加算』ということかと思います。
文字でLEFTを取る代わりに「YEAR()、MONTH()が同じ」という式にして（SUMIFだと計算しにくいので）以下の式ではいかがでしょうか？

=SUMPRODUCT(($D$5:$D$54=$Q$3)*(YEAR($C$5:$C$54)=YEAR($N5))*(MONTH($C$5:$C$54)=MONTH($N5))*($F$5:$F$54))

※ 職種の「$Q$3」が固定アドレスで良いのかどうか疑問ですが、元の式がそうなっていましたのでそのままです。
※ 私の想定が違っている場合は、エラーなどになると思いますので、スルーしてください。

この回答へのお礼

fujillinさん、こちらもわかりやすいご回答ありがとうございました。
正確な数字、でましたよ!
ベストアンサー、二つにしたいくらいです。

いつもありがとうございます。

お礼日時：2020/07/06 11:26