教えてください。
エクセル2010を使用しております。
社員の入退社日から毎月の月末時における入社数、退社数、在籍者数を把握できる表を作成したいと考えております。
各スタッフの氏名、入社日、退職日、在籍年数のデータが入力された表があり、行を変えてC列に年月日(月末)、D列に入社数、E列に退社数、F列に在籍者数を表示させたいと考えております。
また、B列に日付を入力して、エンド・オブ・マンスを使って翌月末日をC列に表示させました。月末の日付を表示させるには、他に良い方法があるでしょうか。
よろしくお願いいたします。
No.2ベストアンサー
- 回答日時:
入社数、退社数とは、当月1ヶ月間の人数ですね。
以下、その前提での一例です。B列に入社日、C列に退職日、明細行が101行まであり、集計を103行目に表示するものとします。
C103は当月の月末日です。
入社数 入社日が当月1日から当月末日までの人数をカウント
D103 =COUNTIFS(B2:B101,"<="&$C103,B2:B101,">="&DATE(YEAR($C103),MONTH($C103),1))
退社数 退職日が当月1日から当月末日までの人数をカウント
E103 =COUNTIFS(C2:C101,"<="&$C103,C2:C101,">="&DATE(YEAR($C103),MONTH($C103),1))
在籍者数 (入社日が当月末日までの人数) - (退職日が当月末日までの人数)
F103 =COUNTIF(B2:B101,"<="&$C103)-COUNTIF(C2:C101,"<="&$C103)
なお、月末日の求め方はEOMONTHの他前のご回答にもあるとおり
DATE関数でも求められます。
=DATE(YEAR(A1),MONTH(A1),0) 先月末日
=DATE(YEAR(A1),MONTH(A1)+1,0) 当月末日
ご回答ありがとうございます。
入社数、退社数、在籍者数の計算式もわかりやすく、初心者の私でも簡単にできました。
各関数の意味をもっと理解して、どうして導き出されるのか勉強してみます。
本当に助かりました。
ありがとうございました。
No.3
- 回答日時:
行を変えてとのことで別のシートに表示させるのではないということでしたら例えば回答1の例でシート1の99行目までにデータが有るとして100行目にはC100セルに年月日(月末)がD100セルに入社数、E100セルに退社数、F100セルに在籍者数の文字が入力されるとしたらC101セルには例えば2010年1月31日からの月の末日を表示させるのでしたら201/1/31のように入力しC102セルには次の式を入力して下方にドラッグコピーします。
=DATE(YEAR(C101),MONTH(C101)+2,0)
D101セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。
=IF($C101="","",IF(COLUMN(A1)<=2,COUNTIF(Sheet1!B$1:B$99,">="&DATE(YEAR($C101),MONTH($C101),1))-COUNTIF(Sheet1!B$1:B$99,">"&$C101),IF(COLUMN(A1)=3,COUNTIF(Sheet1!$B$1:$B$99,"<="&$C101)-COUNTIF(Sheet1!$C$1:$C$99,"<="&$C101),"")))
度々のご回答ありがとうございます。
おかげさまで、No.2 のご回答者様の数式で、必要としていたデータが導き出されました。
苦労してご回答いただけました数式ですが、初心者の私には複雑で理解できず申し訳ございません。
ご回答いただけましたことに、改めて感謝申し上げます。
No.1
- 回答日時:
例えばシート1には元のデータが有ってA列には氏名、B列には入社日、C列には退職日、D列には在籍年数が入力されているとします。
そこでお求めの表ですがシート2に作成するとしてC1セルには年月日(月末)、D1セルには入社数、E1セルには退社数、F1セルには在籍者数とそれぞれ文字が入力されているとします。
そこでC2セルですがこのセルは表を作成するうえでの最初の日付として2010/1/31のように統計を取りたい年月の末日を入力します。C3セルには次の式を入力して下方にドラッグコピーすることで1カ月ごとの末日が表示されます。
=DATE(YEAR(C2),MONTH(C2)+2,0)
D2セルには次の式を入力してF2セルまで右方向にドラッグコピーしたのちに下方にもドラッグコピーします。下方にドラッグコピーします。
=IF($C2="","",IF(COLUMN(A1)<=2,COUNTIF(Sheet1!B:B,">="&DATE(YEAR($C2),MONTH($C2),1))-COUNTIF(Sheet1!B:B,">"&$C2),IF(COLUMN(A1)=3,COUNTIF(Sheet1!$B:$B,"<="&$C2)-COUNTIF(Sheet1!$C:$C,"<="&$C2),"")))
ご回答ありがとうございます。
基準日となる月末の年月日を入力して、数式を入れてドラッグコピーで毎月の末日が簡単に入力できました。
入社数の数式ですが、ちょっと複雑で私には理解できませんでした。申し訳ございません。
毎月末日の自動入力は助かりました。
本当にありがとうございました!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
あるあるbotに投稿したけど採用されなかったあるある募集
あるあるbotに投稿したけど採用されなかったあるあるをこちらに投稿してください
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
エクセルで期間別の在籍人数をカウントする方法を教えてください
Excel(エクセル)
-
Excel 入退職月から任意の月末に在籍する部署ごとの人数を算出
Excel(エクセル)
-
エクセルで社員数把握(部署ごと、職種ごと、日ごと)
Excel(エクセル)
-
-
4
エクセルで、条件をつけて人数の集計をしたいです!
その他(ビジネス・キャリア)
-
5
勤続年数の平均を求めたい時の関数
Windows Vista・XP
-
6
エクセルで指定期間内に在職する対象者を抽出したいのです。
Excel(エクセル)
-
7
EXCELの年月変換について
Excel(エクセル)
-
8
EXCELで2つの数値のうち大きい方を採択する数式
Excel(エクセル)
-
9
休業損害証明書の記入方法について
損害保険
-
10
Excelで社歴管理をしており4年以上を黄色枠、5年以上を赤枠としたいのですが上手くいきません。 入
Excel(エクセル)
-
11
エクセルで日数から年月への変換
Excel(エクセル)
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの入力規則で2列表示したい
-
SUMに含まれる範囲から特定のセ...
-
複数の文字列のいずれかが含ま...
-
エクセルで表示されている数字...
-
数式が入ったセルを含めて、数...
-
スペースとスペースの間の文字...
-
エクセル:横長の表を改行して...
-
エクセルで、毎日の走行距離(...
-
Excel上でのデータ数字が連番で...
-
列の数字に100をかけたい
-
エクセルで住所の番地順に並べ...
-
時間を「昼間」と「夜間」に分...
-
Excelでセル同士のうちで、一部...
-
「エクセル」特定の行(セル)...
-
エクセルのsumifでかけ算してか...
-
EXCELのハイホン区切りの数字並...
-
エクセルでセルの値分の個数の...
-
入退社日より各月末の在籍者数...
-
【EXCEL】指定したセルの値を他...
-
エクセルで小数を含む数値の抽出
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの入力規則で2列表示したい
-
複数の文字列のいずれかが含ま...
-
SUMに含まれる範囲から特定のセ...
-
数式が入ったセルを含めて、数...
-
Excel上でのデータ数字が連番で...
-
スペースとスペースの間の文字...
-
エクセルで表示されている数字...
-
エクセル:横長の表を改行して...
-
列の数字に100をかけたい
-
別のセルに値が入力されたら、...
-
エクセルで1列全部10倍したい
-
エクセルでセルの値分の個数の...
-
Excelのセル内文字の並び替えに...
-
エクセルで曜日に応じた文字を...
-
エクセルで小数を含む数値の抽出
-
エクセルのsumifでかけ算してか...
-
時間を「昼間」と「夜間」に分...
-
エクセルで、毎日の走行距離(...
-
アルファベットを含む数をエク...
-
入退社日より各月末の在籍者数...
おすすめ情報