エクセル 複数条件の関数

質問

Sheet1に以下のDATAがあります
　A B C
在籍期間 年齢 所属
42年0ヶ月13日 61 ２
41年0ヶ月3日 80 4
40年10ヶ月12日 69 4
40年9ヶ月2日 77 2
40年5ヶ月2日 64 4

Sheet２で以下の項目を調べたいと思っています
条件１として在籍期間
(1)1ヶ月未満
(2)1ヶ月以上3ヶ月未満
(3)3ヶ月以上6ヵ月未満
(4)6ヵ月以上1年未満
(5)1年以上5年未満

条件２として在籍期間(1)～(5)までの期間で、所属部署で何人該当するのかを
調べていきたいのですが、うまくいきません
教えてください

回答No.6

tom04

No.3です。

補足の
＞Sheet1のA列データがDATEDIF関数で求められている場合はどのようにすればよいのですか？
について・・・

大勢に影響はないと思いますが、
仮に去年の今日から今年の今日までの在籍期間をDATEDIF関数で表すと
C2セルに「開始日」の2013/4/23　としておきA2セルに今日までの在籍期間を表す数式は
=DATEDIF(C2,TODAY(),"y")&"年"&DATEDIF(C2,TODAY(),"ym")&"ヶ月"&DATEDIF(C2,TODAY(),"md")&"日"
となると思います。
その表示結果は
1年0か月0日
と表示されます。
誕生日であればこれで「満1歳」というコトになり、めでたし！めでたし！になりますが、
一般的には「昨日」をもって「満1年の在籍」今日からは「1年と1日」の在籍！というのが普通だと思います。
そういうコトを考慮し、No.4で日にちの調整を投稿しました。

すなわち、上記数式の　today()　部分で「1」をプラスするか、「開始日」に「1」をプラスするか
どちらかでその調整はできると思います。

回答No.5

bunjii

回答No.2の追加です。
Sheet2のB2セル数式に設定し、他のセルへコピーするときは次のような処理になります。

Sheet2のG列に比較値としてG1=0、G2=1、G3=3、G4=6、G5=12、G6=60と月数を入力します。
=SUMPRODUCT((Sheet1!$C$2:$C$10=B$1)*1,((Sheet1!$E$2:$E$10*12+Sheet1!$F$2:$F$10)>=$G1)*1,((Sheet1!$E$2:$E$10*12+Sheet1!$F$2:$F$10)<$G2)*1)

また、COUNTIFS関数で複数条件を定義するときは対象範囲（在籍期間）を延月数に設定して置く必要があります。
Sheet1のG列に延月数を算出するには次の式を使うと良いでしょう。
=LEFT(A2,FIND("年",A2)-1)*12+MID(A2,FIND("年",A2)+1,FIND("ヶ",A2)-FIND("年",A2)-1)
Sheet2のB2セルは次の数式で目的が達成できるはずです。
=COUNTIFS(Sheet1!$C$2:$C$10,B$1,Sheet1!$G$2:$G$10,">="&$G1,Sheet1!$G$2:$G$10,"<"&$G2)

回答No.4

tom04

No.3です。

たびたびごめんなさい。
前回の回答で間違いがありました。

＞Today()-1
は
＞Today()+1
です。

これでないと日数が合わないですね。m(_ _)m

回答No.3

tom04

こんばんは！
一例です。

↓の画像で左側が元データのSheet1、右側がSheet2とします。
（1）～（5）の条件を考えると、Sheet1の○日のデータは不要のような感じがしますので、
月数だけで判断し、表示するようにしてみました。
Sheet1に作業用の列を設け、実質月数を表示させます。
そして、Sheet2のA・C列には月数で区分分けしておきます。

まずSheet1の作業列E2セルに
=IF(A2="","",LEFT(A2,FIND("年",A2)-1)*12+MID(A2,FIND("年",A2)+1,FIND("ヶ月",A2)-FIND("年",A2)-1))
という数式を入れフィルハンドルで下へずぃ～～～！っとコピー！

そしてSheet2のD3セルに
=COUNTIFS(Sheet1!$E:$E,">="&$A3,Sheet1!$E:$E,"<"&$C3,Sheet1!$C:$C,D$2)
という数式を入れ、列・行方向にフィルハンドルでコピー！
これで画像のような感じになります。

※　画像のC8セルにはこれ以上のデータはない！というくらいの数値を入れておきます。

※　Sheet1のA列データがDATEDIF関数で求められている場合、
1日の誤差が出てしまいますので、DATEDIF関数で　Today()　を使用している場合は
Today()-1
といった感じで調整してください。
=DATEDIF(”開始日",TODAY(),"Y")
（だと満日となりますので、1日マイナスするというコトです。）m(_ _)m

補足

※　Sheet1のA列データがDATEDIF関数で求められている場合はどのようにすればよいのですか？

すいません、教えてください。

回答No.2

bunjii

>イメージどおりです！
Sheet1のA列は文字列と解釈しています。
従って、比較するためには作業用に年数と月数の数値を前処理として必要です。

Sheet1のE列、F列に年数と月数をA列の文字列から切り出します。
E2=LEFT(A2,FIND("年",A2)-1)*1
F2=MID(A2,FIND("年",A2)+1,FIND("ヶ",A2)-FIND("年",A2)-1)*1
E2とF2を下へ必要数コピーしてください。
今回はSheet1のデータを2行目から10行目までとしました。

Sheet2のB列に以下のような数式を入力して右へ必要数コピーします。
B2=SUMPRODUCT((Sheet1!$C$2:$C$10=B$1)*1,(Sheet1!$E$2:$E$10=0)*1,(Sheet1!$F$2:$F$10=0)*1)
B3=SUMPRODUCT((Sheet1!$C$2:$C$10=B$1)*1,(Sheet1!$E$2:$E$10=0)*1,(Sheet1!$F$2:$F$10>0)*(Sheet1!$F$2:$F$10<3))
B4=SUMPRODUCT((Sheet1!$C$2:$C$10=B$1)*1,(Sheet1!$E$2:$E$10=0)*1,(Sheet1!$F$2:$F$10>2)*(Sheet1!$F$2:$F$10<6))
B5=SUMPRODUCT((Sheet1!$C$2:$C$10=B$1)*1,(Sheet1!$E$2:$E$10=0)*1,(Sheet1!$F$2:$F$10>5)*1)
B6=SUMPRODUCT((Sheet1!$C$2:$C$10=B$1)*1,(Sheet1!$E$2:$E$10>0)*1,(Sheet1!$E$2:$E$10<5)*1)

Excel 2013で検証しましたがExcel 2007でも同様の結果を得られます。
(1)から(5)の条件に規則性が無く検査値を作業列に用意すれば1つの数式を他のセルにコピーできるかも知れません。
今回は各行に定数の値を使用しました。

回答No.1

bunjii

>条件２として在籍期間(1)～(5)までの期間で、所属部署で何人該当するのかを調べていきたいのですが、うまくいきません
提示されたデータには目的の(1)から(5)までの条件に合うものがありませんので検証できません。
ダミーデータを追加してください。
尚、Sheet1のデータでは列の区切り位置が分からないので追加データは区切りを明確にしてください。

在籍期間 年齢 所属
42年0ヶ月13日 61 ２
41年0ヶ月3日 80 4

提示されたデータの内容をExcelブックに作成してみましたので確認してください。
尚、ご利用のExcelのバージョンも補足してください。

補足

ありがとうございます。イメージどおりです！
Excelのバージョン2007です。
2つの条件がそろって人数を求める関数が分かりません
しかも、月・年で求められているので混乱します。

教えてください