職員の入職日は必ずしも1日ではありません。2019/4/1、2019/4/30、2019/11/3など
まちまちです。
常勤者もあり、パート(常勤換算で0.8などで示す)の人もいます。
常勤換算数の列には、常勤者は1.0、パートは0.8などと入っています。
月毎にある職種の入職者の常勤換算を出すために、
N列には、縦に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など、1日付けの職員の常勤換算数は返すのに、2日以降では返してくれません。
うーん、なぜなのか…。
ご教授いただければありがたいのですが。
No.1ベストアンサー
- 回答日時:
入職日は【日付】で入力されており、
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関数は日付には使えないと、他のサイトでも書かれていました。
ありがとうございました。
とても勉強になりました。
No.2
- 回答日時:
こんにちは
エクセルで日付に見える値には二種類あります。
見て目のままの「文字列」と「日付の意味を持つ数値(シリアル値)」です。
ご質問文に記されていないので、以下は推測になってしましますが・・・
C列、N列ともシリアル値になっているのではないでしょうか?
試しにN列の「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さん、こちらもわかりやすいご回答ありがとうございました。
正確な数字、でましたよ!
ベストアンサー、二つにしたいくらいです。
いつもありがとうございます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(就職・転職・働き方) 年末年始休暇を伴うシフト制の休みについて 2 2022/11/04 20:04
- 大人・中高年 非常勤職員 4 2023/06/21 21:58
- Excel(エクセル) エクセルの関数 1 2022/06/08 23:38
- 転職 履歴書について 休職期間や同じ職場の雇用形態の変更は書かないといけないでしょうか?軽度知的障害持ちで 2 2022/09/05 17:50
- 統計学 お酒に強い人の割合について 2 2022/09/10 18:42
- 健康保険 扶養について 4 2022/11/29 12:32
- その他(お金・保険・資産運用) 失業給付額ですが、最後の月の給与は 3 2023/02/21 16:53
- 会社・職場 転職先について 2 2023/06/15 15:28
- 厚生年金 社会保険料改定について詳しい方教えてください。 現在派遣で週4で働いています。 先日社会保険料率改定 1 2022/09/30 18:31
- 雇用保険 年金受取前に失業給付をもらえる退職時期を教えてください(パート勤務) 1 2022/03/26 01:26
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
F9キーについて。
-
Excel 小さくなったスクロール...
-
Excel関数について教えてくださ...
-
計算能力
-
Excelセルを跨いで合計を出す方法
-
Excelに詳しい方! B列が「日...
-
エクセルでファイルの最終更新...
-
Excelのピボットグラフの作り方...
-
Excelファイルが開けません
-
Excel 2019 [オプション]の[リボンのユ...
-
【EXCEL】画像の黄色部分の抽出...
-
スプレッドシートの関数につい...
-
エクセルで 自動的に◯や数字を...
-
excel2013 MonthDays 関数が使...
-
シフト表をエクセルで作るとき...
-
EXCELの散布図で日付が1900年に...
-
特定の文字列を含む、住所を抽...
-
Excel分数の表示について
-
エクセルの関数について教えて...
-
マイクロソフトのPADを使ってい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報