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)
という数式を記述し右方向へコピーします。
これで添付画像のような、ご質問者のご希望の表が作成できると思います。
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
エクセルで指定期間内に在職する対象者を抽出したいのです。
Excel(エクセル)
-
5
勤続年数の平均を求めたい時の関数
Windows Vista・XP
-
6
エクセルで社員数把握(部署ごと、職種ごと、日ごと)
Excel(エクセル)
-
7
EXCELで○ヶ月を○年○ヶ月に変換したい。
Excel(エクセル)
-
8
エクセルの関数で「6ヶ月より長い場合」と条件設定したい!
Excel(エクセル)
-
9
参照先セルに値が入っていない時に、「0」や「1900/01/00」などが入らないようにしたいのですが
Excel(エクセル)
-
10
EXCELの年月変換について
Excel(エクセル)
-
11
E列のセルに数値が入れば(空白でなければ)B列の同じ行のセルに色がつく
その他(Microsoft Office)
-
12
エクセル 表の人数を計算したい。(部署ごとに)
Excel(エクセル)
-
13
エクセルで平均年齢や平均勤続年数を求めるには?
Excel(エクセル)
-
14
エクセル:ある時間範囲で在席人数を計算?
Excel(エクセル)
-
15
COUNTIFS関数で日付データを数えたい
Excel(エクセル)
-
16
半年単位で勤続年数を算出する式
Excel(エクセル)
-
17
会社の休業日は退職日にできない?
転職
-
18
excelグラフでデータテーブルを一部分のみ表示できますか。
Excel(エクセル)
-
19
アクセスで入力したデータの順番が変わってしまいます
その他(ビジネス・キャリア)
-
20
受信メールの添付ファイル忘れの指摘方法
Yahoo!メール
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
DATE関数で現在の年齢を出した...
-
エクセルでCtrl+Tでテーブルの...
-
REGEXREPLACE関数について、
-
エクセルのクイックアクセスツ...
-
在庫管理表に使うエクセルの関...
-
職場の人から聞かれており、こ...
-
下記マクロでMsgBox "空白です...
-
Excel関数-文字列で自動作成さ...
-
エクセルでバーコード作成し使...
-
【マクロ】for next構文について
-
ユーザー定義関数をアドイン登...
-
エクセルの関数について教えて...
-
エクセルで表
-
PDFの請求明細をエクセルにしたい
-
Excelデータをコピペして、ペー...
-
スプレッドシート、Excelでの数...
-
エクセルで特定の範囲内から小...
-
Excelで50個のセルに同じ文字を...
-
エクセルで会社の従業員のデー...
-
【マクロ】関数をセルに入力す...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報