冬の風邪予防は「鼻○○○」が新常識!?>>

教えてください。

エクセル2010を使用しております。
社員の入退社日から毎月の月末時における入社数、退社数、在籍者数を把握できる表を作成したいと考えております。

各スタッフの氏名、入社日、退職日、在籍年数のデータが入力された表があり、行を変えてC列に年月日(月末)、D列に入社数、E列に退社数、F列に在籍者数を表示させたいと考えております。

また、B列に日付を入力して、エンド・オブ・マンスを使って翌月末日をC列に表示させました。月末の日付を表示させるには、他に良い方法があるでしょうか。

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

A 回答 (3件)

入社数、退社数とは、当月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) 当月末日
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

入社数、退社数、在籍者数の計算式もわかりやすく、初心者の私でも簡単にできました。
各関数の意味をもっと理解して、どうして導き出されるのか勉強してみます。

本当に助かりました。
ありがとうございました。

お礼日時:2012/08/25 14:03

行を変えてとのことで別のシートに表示させるのではないということでしたら例えば回答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),"")))
    • good
    • 0
この回答へのお礼

度々のご回答ありがとうございます。

おかげさまで、No.2 のご回答者様の数式で、必要としていたデータが導き出されました。
苦労してご回答いただけました数式ですが、初心者の私には複雑で理解できず申し訳ございません。

ご回答いただけましたことに、改めて感謝申し上げます。

お礼日時:2012/08/25 14:16

例えばシート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),"")))
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

基準日となる月末の年月日を入力して、数式を入れてドラッグコピーで毎月の末日が簡単に入力できました。
入社数の数式ですが、ちょっと複雑で私には理解できませんでした。申し訳ございません。

毎月末日の自動入力は助かりました。
本当にありがとうございました!

お礼日時:2012/08/25 13:59

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで、条件をつけて人数の集計をしたいです!

エクセルを使って人数の集計をしたいのですが、
勤続年数1年未満は何人、1年以上3年未満は何人、3年以上5年未満は何人と表示したいのですが、どうすればいいのでしょうか?

Aベストアンサー

条件付きのカウントは一般的にはCOUNTIF関数を使用します
 =COUNTIF(範囲,条件)

データの範囲がA1:A100として、勤続年数1年未満なら
 =COUNTIF(A1:A100,"<1")
勤続年数1年以上3年未満なら
 =COUNTIF(A1:A100,"<3")-COUNTIF(A1:A100,"<1")
になります(条件は1つしか書けません)

もう一つはSUMPRODUCT関数を利用する方法です。こちらは複数条件を指定できます。
 =SUMPRODUCT((A1:A100>=3)*(A1:A100<5)*1)
で3年以上5年未満の人数をカウントできます。

Q勤続年数の平均を求めたい時の関数

エクセルで会社の従業員の勤続年数とその平均を求めたいです。
勤続年数のセルに
=DATEDIF(A1,TODAY(),"y")&"年"&DATEDIF(A1,TODAY(),"ym")&"ケ月"
といれて勤続年数を出してます。
それで次に従業員の勤続年数の平均を出したいのですが、AVERAGEを使うとエラー#DIV/0!が出ます。
どうすれば勤続年数とその平均が求められますか?
どんな方法でも良いので教えて下さい!!非常に困ってます(ー_ー)!!

Aベストアンサー

そりゃそうでしょう。計算対象が文字列扱いのデータなんですから、
エラーが出てアタリマエ、平均値が計算できワケがないです。

ですので、勤続年数の「数値」を持つセルを別に作る必要があります。
当然年/月のままじゃダメで勤続延べ何ヶ月という「月」の値にする
必要があります。ということで、上記のセルとは別に、

=DATEDIF(A1,TODAY(),"y")*12+DATEDIF(A1,TODAY(),"ym")

こうすれば、勤続延べ何ヶ月の数字が出てきます。この式を含むセル
についてAVARAGE関数で平均を取ってください。

で、出てくるのは勤続延べ何ヶ月ですから、こいつを12で割って「年」
余りを「月」にする必要があります。平均月が出たセルをB1として、

="平均"&INT(B1/12)&"年"&(B1-INT(B1/12)*12)&"ケ月"

こんな感じで表示できるはずです。
キモは「計算に使うセルと表示するセルを分ける」ということです。

計算に使うセルが表示上邪魔な場合は「書式」「列」「表示しない」
などで表示上隠す方法と、AA1~などの極端に離れたセルに記入して
おく方法などがあります。

そりゃそうでしょう。計算対象が文字列扱いのデータなんですから、
エラーが出てアタリマエ、平均値が計算できワケがないです。

ですので、勤続年数の「数値」を持つセルを別に作る必要があります。
当然年/月のままじゃダメで勤続延べ何ヶ月という「月」の値にする
必要があります。ということで、上記のセルとは別に、

=DATEDIF(A1,TODAY(),"y")*12+DATEDIF(A1,TODAY(),"ym")

こうすれば、勤続延べ何ヶ月の数字が出てきます。この式を含むセル
についてAVARAGE関数で平均を取ってください。

で...続きを読む

QExcelで入社・退社年月日を入力して、勤続年数表示したい

Excelで入社年月日と退社年月日を入力すると、
勤続年数が表示できるようにしたいのですが、
方法が分かりません。

DateDifという関数があると過去の質問を検索してみたらでてきましたが、どのように使ったらよいのかが分かりません。

入社年月日⇒14年4月1日
退社年月日⇒16年4月20日
勤続年数⇒2年1ヶ月
という具合に、一ヶ月未満の日数がある場合は1ヶ月に繰上げて表示したいです。

そして、休業期間がある場合は同様に休業期間を表示して、勤続年数からマイナスして結果的に
勤続年数-休業期間=在職期間
と表示できるようにしたいです。

ご存知のかた教えてください!よろしくお願いいたします。

Aベストアンサー

○年□ヶ月△日
=DATEDIF(A1,B1,"Y")&"年"&DATEDIF(A1,B1,"YM")&"ヶ月"
&DATEDIF(A1,B1,"MD")&"日"

ですが、
入社年月日⇒14年4月1日
退社年月日⇒14年4月30日
と書いた場合、DATEDIFの仕様として「初日不算入」です
ので、4/2~4/30にちまでを数えることになります。
→【0年0ヶ月29日】
と表示されるはずです。

=DATEDIF(A1,B1+1,"Y")&"年"&DATEDIF(A1,B1+1,"YM")&"ヶ月"
&DATEDIF(A1,B1+1,"MD")&"日"

で多少マシになるかもしれませんが、上記の式は
使わないほうがよいです。なぜなら、

DATEDIF(A1,B1,"MD")

DATEDIF(A1,B1,"YD")
には、うるう年に絡む不具合があり正しく計算されない
ことが知られていますので。

例えば、
A1に
1998/10/31
B1に
2000/03/01
と入力した場合など。

詳しくは角田さんのページの
http://www.h3.dion.ne.jp/~sakatsu/ktfunc_ref0206.htm#DATEDIF
をご覧いただき、【kt関数アドイン】導入することを
お勧めします。

参考URL:http://www.h3.dion.ne.jp/~sakatsu/index.htm

○年□ヶ月△日
=DATEDIF(A1,B1,"Y")&"年"&DATEDIF(A1,B1,"YM")&"ヶ月"
&DATEDIF(A1,B1,"MD")&"日"

ですが、
入社年月日⇒14年4月1日
退社年月日⇒14年4月30日
と書いた場合、DATEDIFの仕様として「初日不算入」です
ので、4/2~4/30にちまでを数えることになります。
→【0年0ヶ月29日】
と表示されるはずです。

=DATEDIF(A1,B1+1,"Y")&"年"&DATEDIF(A1,B1+1,"YM")&"ヶ月"
&DATEDIF(A1,B1+1,"MD")&"日"

で多少マシになるかもしれませんが、上記の式は
使わないほうがよいです。なぜなら、

DATEDI...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Q離職率・入職率の計算方法

離職率・入職率の計算方法を教えてください。
「離(入)職者÷全就業者」なのか
「その年度の入職者のうちの離職した人の割合」なのか
どなたかご存知合ったら教えてください。
自社の就業状況をまとめている者です。
どこを見てもデータはありますが明確なものがありません。

また、定着率とはどのように算出するものでしょうか?

あわせて教えていただけると大変助かります。
よろしくお願いします。

Aベストアンサー

一般的には次のように計算します。

入職率= 増加労働者数÷年初の全労働者数×100

離職率= 減少労働者数÷年初の全労働者数×100

定着率については判りません。

QExcelで日別に入力されたデータを月別に集計するにはどうしたらいいのでしょうか。

Excelで月別の集計をしたいのですが
例えば
  日付    数量
10月25日  200
10月27日  150
10月30日  120
11月 1日  200
11月 3日  150
のように、日付が飛び飛びで連続していなくい日々の数量を、上の行から順番に入力していった場合に(月が替わる行が決まっていない)月別の集計を取る方法はないでしょうか。


 

Aベストアンサー

どのように集計する月を指定するのかが書かれていませんが、例えば11月分の合計を出したいなら

1)11月までの合計から10月までの合計を引く方法
 =SUMIF(A:A,"<=11/30",B:B)-SUMIF(A:A,"<=10/31",B:B)

2)月が「11」のB列を合計する方法
 =SUMPRODUCT((MONTH(A2:A100)=11)*(B2:B100))

なども考えられます。

でもエクセルなら一番オーソドックスな方法はピボットテーブルでしょう。C列に
 =TEXT(A1,"YYYY/MM")
で「2008/11」のような文字列を生成しておいてピボットテーブルで集計すれば、シート上にある全ての月の合計が一発で求められます。
ピボットテーブルの使い方はGoogleなどで検索したらたくさん見つかります
http://www.viplt.ne.jp/tomy/pibot.html

Q前年比の%の計算式を教えてください

例えば前年比115%とかよくいいますよね?

その計算の仕方が分かりません・・・
例えば 前年度の売り上げ2.301.452円
    今年度の売り上げ2.756.553円
の場合前年比何%アップになるのでしょうか?計算式とその答えを
解りやすく教えて下さい・・・
バカな質問でゴメンなさい(><)

よろしくお願いします。

Aベストアンサー

 
(今年の売上÷前年の売上×100)-100=19.8%の売上増加

 

Qエクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コ...続きを読む

Q健康保険資格喪失証明書について

恐れ入ります。

まず自分は今年の1月末で会社を退職し
現在無職の一人暮らしとなっております。

保険証を退社時に返却していたので、
届出などが必要というのはしっていたのですが、
保険などについては、全くわからないということがあって、
退職してからなにもしておりませんでした。

しかし、最近になって急に昔から放置していた
虫歯が尋常じゃなく痛み出し、
保険証が必要となりインターネットで調べていたのですが、
国民健康保険に変更申請するのに

「健康保険資格喪失証明書」

が必要ということだけはわかりました。

しかし、退職時にはいただいておりません。
退職して約2ヶ月たちますが、自宅に郵送などもされてきておりません。

そこでお聞きしたいのですが、
1.健康保険資格喪失証明書は退職時に渡されるのが普通なのか?
2.問い合わせは前の会社でよいのか?
3.現状で最短で保険証を手に入れる手順は?

以上です。大変申し訳ありませんが宜しくお願いします。


早く歯医者行きたいです。
痛みでよるも眠れません;;

恐れ入ります。

まず自分は今年の1月末で会社を退職し
現在無職の一人暮らしとなっております。

保険証を退社時に返却していたので、
届出などが必要というのはしっていたのですが、
保険などについては、全くわからないということがあって、
退職してからなにもしておりませんでした。

しかし、最近になって急に昔から放置していた
虫歯が尋常じゃなく痛み出し、
保険証が必要となりインターネットで調べていたのですが、
国民健康保険に変更申請するのに

「健康保険資格喪失証明書」

が...続きを読む

Aベストアンサー

喪失証明書は送付義務はありません。
また、勝手に自宅に送られてくるものでもありません。
通常会社は、資格喪失届を出すことで送られる資格喪失通知書の
コピーか、もしくは資格喪失連絡票というものを退職者に渡します。

前の会社に問い合わせして、資格喪失通知書のコピーをもらうことです。
通常は雇用保険の離職票などと一緒に退職後すぐに会社が退職者に渡すべきものです。
保険証については、市役所などで問い合わせしないとここでは分かりません。
健康保険は退職日にさかのぼって加入することになります。
また、届出が遅れた場合は届出日以前に発病した病気については保険が使えない
場合もあります。

このような手続きは通常は退職後すぐに行うべきものです。
誰も教えてくれないから分からない、教えてくれない会社や役所が悪いでは、済まされません。
分からなければ退職した会社の担当者なりに聞けばいいのです。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報