Sheet1に以下のDATAがあります
A B C
在籍期間年齢所属
42年0ヶ月13日612
41年0ヶ月3日804
40年10ヶ月12日694
40年9ヶ月2日772
40年5ヶ月2日644
Sheet2で以下の項目を調べたいと思っています
条件1として在籍期間
(1)1ヶ月未満
(2)1ヶ月以上3ヶ月未満
(3)3ヶ月以上6ヵ月未満
(4)6ヵ月以上1年未満
(5)1年以上5年未満
条件2として在籍期間(1)~(5)までの期間で、所属部署で何人該当するのかを
調べていきたいのですが、うまくいきません
教えてください
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.6
- 回答日時:
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
- 回答日時:
回答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
- 回答日時:
No.3です。
たびたびごめんなさい。
前回の回答で間違いがありました。
>Today()-1
は
>Today()+1
です。
これでないと日数が合わないですね。m(_ _)m
No.3
- 回答日時:
こんばんは!
一例です。
↓の画像で左側が元データの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
- 回答日時:
>イメージどおりです!
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
- 回答日時:
>条件2として在籍期間(1)~(5)までの期間で、所属部署で何人該当するのかを調べていきたいのですが、うまくいきません
提示されたデータには目的の(1)から(5)までの条件に合うものがありませんので検証できません。
ダミーデータを追加してください。
尚、Sheet1のデータでは列の区切り位置が分からないので追加データは区切りを明確にしてください。
在籍期間 年齢 所属
42年0ヶ月13日 61 2
41年0ヶ月3日 80 4
提示されたデータの内容をExcelブックに作成してみましたので確認してください。
尚、ご利用のExcelのバージョンも補足してください。
この回答への補足
ありがとうございます。イメージどおりです!
Excelのバージョン2007です。
2つの条件がそろって人数を求める関数が分かりません
しかも、月・年で求められているので混乱します。
教えてください
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 憲法・法令通則 令和4年(2022年)4月1日に,国籍の選択をすべき期限が変更されます。 成年年齢の引き下げ等を内容 2 2023/03/12 12:11
- 健康保険 傷病手当金について 傷病手当金の在職中の分の申請は、退職後でも可能ですか? 調べてみると、退職後は健 2 2023/01/10 21:35
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- その他(家族・家庭) 身内が連れて来た相手かその家族で 絶対に避けたい条件ってありますか? 1)新興宗教の信者 2)借金持 3 2023/08/18 13:00
- 面接・履歴書・職務経歴書 転職の履歴書の不備 1 2022/10/16 00:24
- Excel(エクセル) EXCEL値貼り付け(ある条件のもと自動化) 5 2023/06/06 12:21
- 転職 賞与について詳しい経理の方?教えて下さい。 賞与が年2回支給されます。 支給対象期間6月は(前年10 8 2023/04/17 19:23
- その他(ビジネススキル・経営ノウハウ) 在庫管理のこの問題が分かりません。どなたか解説お願いします 2 2022/04/18 18:35
- 数学 在庫管理のこの問題が分かりません。どなたか解説お願いします 4 2022/04/18 22:19
- 数学 在庫管理のこの問題が分かりません。どなたか解説お願いします 2 2022/04/18 22:21
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
数学 Tan(θ)-1/Cos(θ)について...
-
4つのパターンを表示するEXACT...
-
エクセルの数式で教えてください。
-
エクセルで指定した日付、店舗...
-
Excel:一部のフォントでセルの...
-
Excel初心者です。 詳しい方、...
-
Excel初心者です。 詳しい方、...
-
Excel 2019 のピボットテーブル...
-
スマートな関数を教えて下さい。
-
Excelのグラフ軸について
-
EXACT関数とIF関数の組み合わせ...
-
Excel ウインドウ枠の固定をす...
-
エクセルで日付の入ったセルの...
-
excelの不要な行の削除ができな...
-
INDIRECTを使わず excelで複数...
-
各ページの1番上の表示について
-
スプレッドシートの関数VLOOKUP...
-
エクセルでセルに「氏名を入力...
-
関数を教えて下さい。
-
Excelのif関数で文字が見えなく...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報