![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
No.2ベストアンサー
- 回答日時:
ご希望の月別クラス別在籍者人数の一覧を別シート「月別一覧」に作成するとすると、
まず、月別一覧の月のセル(添付画像のB3~M3)について、添付画像のように月初の日付を入力し、セルの書式設定で表示形式を「m"月"」としておきます。
元データの「クラス」「入日付」「出日付」がB3からD13にあるとすると、
添付画像でいえば、月別一覧シートのB4に、
=SUMPRODUCT((元データ!$B$3:$B$13=$A4)*(元データ!$C$3:$C$13<=EOMONTH(B$3,0))*(元データ!$D$3:$D$13>=B$3))
という数式を記述し、右方向および下方向へオートフィル等でコピーします。
数式中の$B$3:$B$13、$C$3:$C$13、$D$3:$D$13につていは、実際のデータの範囲に従って変更してください。
合計欄はB7に
=SUM(B4:B6)
という数式を記述し右方向へコピーします。
これで添付画像のような、ご質問者のご希望の表が作成できると思います。
![「エクセルで期間別の在籍人数をカウントする」の回答画像2](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/5/142736_601420d1412bb/M.png)
No.4
- 回答日時:
No.3の回答者です。
回答内容を推敲している間に、No.2の回答がされましたね。
SUMPRODUCT関数を使った部分が同じものだったようです。
投稿する前に確認しておくべきでした。
No.3
- 回答日時:
違うかもしれませんが、こういうことでしょうか?
(元データシートの範囲が3行目から100行目だとして)
月別一覧シートのB4セルに
=SUMPRODUCT((元データ!$C$3:$C$100<=EOMONTH(DATE(B$2,B$3,1),0))*1,(元データ!$D$3:$D$100>=DATE(B$2,B$3,1))*1,(元データ!$B$3:$B$100=$A4)*1)
で対応させます。各クラスごとにカウントさせたら、合計を
出すのは簡単ですよね。
個人的には、元データシートのE列以降に作業列を用意して、
E3セルに日付として月初めの2020/4/1を入れて
=AND($C3<=EOMONTH(E$2,0),EOMONTH($D3,-1)+1>=E$2)*1
にすることで、3行目以降それぞれのカウントをさせます。
月別シートでは、この作業列を元にSUMIF関数でクラス別を集計
回答ありがとうございます。SUMPRODUCT関数を使い表を完成させることができました。元データシートに作業列を用意する方法も試してみます。
No.1
- 回答日時:
結構面倒なことになりますよ。
手作業でやる手順をそのまま数式で示してみます。
調べたい期間をMIN関数とMAX関数で比較しましょう。
在籍の最終日がA1セルにあると仮定します。
2020年12月に在籍したかを調べたい場合、
MIN("2021/1/1",A1)
これは 2021年1月1日 以下の値が返ります。
A1の値が2021年1月1日よりも小さければA1の値が返り、
A1の値が2021年1月1日よりも大きければ2021年1月1日の値が返ります。
すなわち、2021年1月1日の値が返らなければ2020年12月31日以前に在籍していると判断できる。
同様に
MAX("2020/11/30",A1)
これで、2020年11月30日の値が返らなければ2020年12月1日以降に在籍していると判断できる。
この2つを条件をAND関数に渡すなどして数えれば良いんじゃないかな。
・・・
調べたい年がB1セル、月がC1セル、日がD1セルにあると仮定するなら、
MIN(DATE(B1,C1,D1+1),A1)
MAX(DATE(B1,C1,D1-1),A1)
みたいにすると楽ができる。
他にもやり方はありますが、あとは工夫してみてください。
ご回答ありがとうございます。
countifs関数を使う事しか考えていなかったので、MINとMAX関数が活用できるとは思ってもみませんでした。参考にさせていただきます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) excelで同日を除いて数えたい 5 2023/01/15 22:08
- 雇用保険 失業保険給付の条件について 1 2022/05/27 14:15
- その他(Microsoft Office) 【スプレッドシート】白色のセルをカウントしたい 2 2023/02/24 07:39
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- 憲法・法令通則 令和4年(2022年)4月1日に,国籍の選択をすべき期限が変更されます。 成年年齢の引き下げ等を内容 2 2023/03/12 12:11
- Excel(エクセル) 【エクセル】参照セルに何も入力が無い場合の空白表示方法 1 2022/05/26 10:01
- 出産 産休と育休の間が空く場合、「育児休業を開始する前6ヵ月間」はいつが対象になりますか? 1 2022/06/02 20:59
- Excel(エクセル) 前の(左隣の)シートを連続参照するように、あとから変更したい 1 2023/02/22 00:51
- Excel(エクセル) Excelでの複数条件のカウントについて 1 2022/09/25 07:40
- 人事・法務・広報 賞与についてです。 入社して半年です。 私の会社は10万×3×在籍係数 らしいのですが、 (在籍係数 4 2022/11/08 11:59
このQ&Aを見た人はこんなQ&Aも見ています
-
「どうして捨てられないの?」前妻の物を捨てられない男性の心理って?
前妻の物を捨てられない理由に加え、捨てるための手段はあるのかを専門家に聞いてみた!
-
入退社日より各月末の在籍者数を把握できる関数
SOHO・在宅ワーク・内職
-
エクセルで、条件をつけて人数の集計をしたいです!
その他(ビジネス・キャリア)
-
Excel 入退職月から任意の月末に在籍する部署ごとの人数を算出
Excel(エクセル)
-
-
4
勤続年数の平均を求めたい時の関数
Windows Vista・XP
-
5
エクセルで指定期間内に在職する対象者を抽出したいのです。
Excel(エクセル)
-
6
エクセルで社員数把握(部署ごと、職種ごと、日ごと)
Excel(エクセル)
-
7
EXCELで○ヶ月を○年○ヶ月に変換したい。
Excel(エクセル)
-
8
参照先セルに値が入っていない時に、「0」や「1900/01/00」などが入らないようにしたいのですが
Excel(エクセル)
-
9
E列のセルに数値が入れば(空白でなければ)B列の同じ行のセルに色がつく
その他(Microsoft Office)
-
10
エクセル 表の人数を計算したい。(部署ごとに)
Excel(エクセル)
-
11
EXCELの年月変換について
Excel(エクセル)
-
12
エクセルの関数で「6ヶ月より長い場合」と条件設定したい!
Excel(エクセル)
-
13
日付が未入力の際はゼロか、空白にしたい
その他(Microsoft Office)
-
14
エクセル:ある時間範囲で在席人数を計算?
Excel(エクセル)
-
15
COUNTIFS関数で日付データを数えたい
Excel(エクセル)
-
16
会社の休業日は退職日にできない?
転職
-
17
エクセルで平均年齢や平均勤続年数を求めるには?
Excel(エクセル)
-
18
受信メールの添付ファイル忘れの指摘方法
Yahoo!メール
-
19
0.5時間などの時間計算の方法
その他(家事・生活情報)
-
20
ExcelのSUMPRODUCTで日付の範囲を指定する方法
その他(Microsoft Office)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IFとIFS関数
-
Excelを無料で使うには? パソ...
-
セルの数を求めたい
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
エクセルで「ページレイアウト...
-
エクセルの数式バーのフォント...
-
エクセルの関数について教えて...
-
エクセルでセルに標準で入力さ...
-
Microsoft1Officeの互換ソフト...
-
シフト表をエクセルで作るとき...
-
ユーザー定義関数をアドイン登...
-
Excel VBAで全ての矢印を赤色に...
-
VBA Private Sub Worksheet_Cha...
-
2列に入っているデータを1列...
-
Excelに詳しい方! B列が「日...
-
excel2013 MonthDays 関数が使...
-
エクセルのクイックアクセスツ...
-
Excelの数式について教えてくだ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報