Excel 特定日での在籍者にチェックをつけたい

質問

Excelの人事データ（派遣・請負契約）として次のようなデータがあります。
A列　稟議番号
B列　会社名
C列　要員名
D列　所属部署
E列　所属課
F列　管轄担当者
G列　契約種別（派遣or請負）
H列　開始日
I列　終了日

上記のようなExcelデータがあり300件ほどのデータです。
１）この中で2017/5/1時点での在籍者にフラグを立てたいです。
２）また部署ごと且契約種別ごとに何名いるかも出したいです。

２）についてはピポットテーブルで集計する形かなと思っていますが１）の方が
どうしても出せません。sumproductを使って出そうとしたのですがフラグは立てられず合計数でしか出ないのであまり意味はなく、またそれでも０になってしまいました。
初心者なので詳しいご説明をいただけると幸いです。宜しくお願いいたします。

回答No.6

tsubu-yuki · ベストアンサー

> やり方が違いますかね
いや、根本的に考え方がおかしいです。

> 上の方からこういうデータが作れたらいいんだけどいい方法あるかな？という質問を受け
> Excelにあまり詳しいほうではないという自身の認識
社会人として、自力でできないなら引き受けたら駄目でしょう(笑)。
私が上司なら
「おまえさん、できるゆーたんちゃうん？？
　できもせんのにひきうけるなや、ほんまに。」
で大笑いしているところですよ(笑)。
３００行ほどの派遣・請負登録をしている会社でソレは
本当に（悪い意味で）ヤバいですよ。
気を付けた方が良いです。

> 入社したばかりということもあり、周りに詳しく聞ける相手がいない
それを「聞ける」人間関係を築くのが、新入社員の最初の仕事です。
今後も仕事で行き詰まったら先輩・上司ではなく、
ＷＥＢで相談するおつもりですか？
迷わず「エクセルが分かる先輩にお尋ねなさい」。
最終的な回答は本当はソレです。

なお。
> 開始日が未来日のももあったり（2017/7/1など）5/2開始日のものがあり、
そういうのを「情報の小分け、後出し」というんです。
やはりあったでしょ(笑)？というのが正直なところ。

まぁ、コレを採用するなら、考え方が変わります。
条件を複数（２個以上）重ねたいなら、ＡＮＤ関数・ＯＲ関数を使うのが近道です。
　※関数の詳細は別途お調べください。
つまり条件は
　　終了日が５／１（含む）以降である
　　　　　　　　「且つ」
　　開始日が５／２（含まず）以前である　
場合に「フラグを立てる（？）」という事ですね？
それならば、ＡＮＤ関数がちょうどいいようです。なので
　　式：=IF(AND(I3>="2017/5/1"*1,H3<"2017/5/2"*1),1,0)
とでもしてみますか。
おそらく、対象に「１」、対象外に「０」が入ってるでしょ。
　※終了・開始にちゃんと日付が入っているのが条件です。

で、あとで部署ごとにカウントするなら
ＳＵＭＰＲＯＤＵＣＴの出番かもしれませんね。
尤も、この程度なら
もっと簡単なＳＵＭＩＦとかＣＯＵＮＴＩＦＳでもいけますけどね。
というか、ＳＵＭＩＦのための「１」ですわ、私としては。

３００件程度、指折り数えれば良いんですけどね、ほんとは(笑)。

お礼

お返事遅くなり申し訳ありません。
回答、ありがとうございます。上記の方法でできました。
今回、上からの指示は単純に指定日付時点での在籍者数がエクセルでわかるといいな。ということでしたが私の方で契約種別ごとに出ていたらさらにいいのではと思い、一度そういったことが出来るか確認してみたいと思い投稿いたしました。今回の事、自分の力だけでできたという気はさらさらありませんが皆様の意見も受け入れつつ今後につなけていきます。
ありがとうございました。

回答No.8

imogasi

２）だけに絞って回答の１案。
ーー
質問では
＞２）また部署ごと且契約種別ごとに何名いるかも出したいです。とある。
ある日時時点での在籍者に限るのかどうか、はっきりしないが、もしそうなら在籍サインをワーク列に作って下記記述のＣｏｕｎｔｉｆｓ関数の第3条件に加えるとよい。
（別例で言えば、例えば、静岡産のみかんや、青森県産のりんごの購入件数をカウントしたいといったイメージです。）
ある表で部署ごと職種（契約社員か正社員化）別に人数を出すのも、関数では容易でないなと思った次第です。
ーーー
作業は３ステップで、次の３ステップを行う
（１）手操作でフィルタをかけて重複を除く
（２）（１）を使って、ＶＢＡで部署＋職種の組み合わせ一覧を作る
（３）関数を使ってＣＯＵＮＴＩＦＳをつかって該当分類の件数を出す
例データ（Ａ，Ｂ列）
人事部 社員
総務部 嘱託
営業部 派遣
営業所 パート
人事部 アルバイト
総務部 社員
人事部 嘱託
営業所 派遣
ー パート
ー アルバイト
Ｄ列とＥ列に、次の操作をして、部署の一覧と職種の一覧を出す。
操作はデーターフィルター詳細設定ー重複するデータは無視する。
結果
Ｄ列　　E列
人事部 社員
総務部 嘱託
営業部 派遣
営業所 パート
ーー アルバイト
ーーー
サンプルデータで、元データ＝社員データを下記とする。
F列　　G列　H列
氏名　部署　職種
山田 人事部 社員
木之下 総務部 嘱託
栗木 営業部 派遣
川上 営業所 パート
山中 人事部 アルバイト
藤村 総務部 社員
小野 人事部 嘱託
山村 営業所 派遣
小宮 営業部 パート
小山 営業部 アルバイト
遠田 人事部 社員
VBAを使って部署＋職種の組み合わせを作る。
Sub test01()
Set sh1 = Worksheets("Sheet1")
k = 2
For j = 2 To 6
For i = 2 To 5
sh1.Cells(k, "J") = sh1.Cells(i, "D")
sh1.Cells(k, "K") = sh1.Cells(j, "E")
k = k + 1
Next i
Next j
End Sub
結果
J、K、L列（L列は後で説明する関数の結果）
人事部 社員 2
総務部 社員 1
営業部 社員 0
営業所 社員 0
人事部 嘱託 1
総務部 嘱託 1
営業部 嘱託 0
営業所 嘱託 0
人事部 派遣 0
総務部 派遣 0
営業部 派遣 1
営業所 派遣 1
人事部 パート 0
総務部 パート 0
営業部 パート 1
営業所 パート 1
人事部 アルバイト 1
総務部 アルバイト 0
営業部 アルバイト 1
営業所 アルバイト 0
L列の関数
L1に=COUNTIFS($G$2:$G$12,J2,$H$2:$H$12,K2)
下方向に式を複写。
結果は直前の説明の　L列の数字。
もしある時点の在籍者だけをカウントするなら、もしある時点の在籍者にサインをある列に作り、第3条件を上記COUNTIFSの３番目の条件ブロックに指定すれば良い。
ーー
複雑になっているが、質問者が、読み通してくれて、行っていることをわかってもらえるレベルか心配。質問者が、しつこいと断念・放棄しそう。
条件付きカウントは、まず、条件そのものを作っていかないとならないので、むつかしいと思った。今後長期的に、上記以上の簡潔な回答があるか勉強してください。

回答No.7

imogasi

エクセルの初心者か。補足も含めて回答者とかみ合っていないのは力不測のように思う。
（１）＞2017/5/1時点での在籍者
質問者がどの項目（列セルのデータ）を使えば判定できるのか考えよ。
それを質問に文章で明記すること。
簡単な少数データ例を質問にあげるべきだ。
それぐらい、本件業務に詳しい（読者回答者にははっきりしない）質問者の仕事
だろう。
（２）＞部署ごと且契約種別ごとに何名
質問のデータから
・契約種別
・部署
が
・具体的データで　かつ
・同一ブックの別シートか同一ブックの「セルの値」として、作れないと
「関数」を使っての作業では難しい。そのセルの値を関数の引数に指定しないといけないからだ。
ＶＢＡを使うと変数という仕組みが使えるので、シートのセルの値にいったん実現しなくても手はあるが。
質問者のレベルなら、質問者がデーターのシートを見て、自分で拾い出して、別セルに入力し、それを使うべきだろう。関数では結構むつかしいから。
フィルタを使う手が簡単だと思う。しかし組み合わせを作るのは難しい。
これができれば、条件該当件数はCOUNTIFSで件数が出せる（Sumproductを使わずとも）のではないか。
Googleなどで「エクセル　COUNTOFS」や「エクセル　SUMIFS関数」で調べたら、本件でこの関数を使うのが適切かどうかわかるだろう。
また、ピボットテーブルがつかえないか、（便利な機能なので）いつも意識しておくとよい。
会社の事務に使うなら、ＶＢＡができないと、いろいろ場面で進まないことが多かろうと思う。
＞フラグは立てられず
確かにワーク列に、分別するための数字コードなどをいったん作る（IF関数などを使って）のは初心者にはお勧めの方法です。嫌がる人が多いが、なくすのは学が進んでからでよいと思う。
また質問者のレベルでは、本件程度以上の課題で、関数でも問題解決がむつかしいように思う。期間をかけて勉強のこと。個人のデータを扱うならともかく、そのレベルで、処理をやられたら会社もリスクを負う。

回答No.5

bunjii

>全部のリストから部署ごと且契約種別ごとに5/1時点の在籍者人数を出したいです。そのため色を塗りつぶして塗りつぶされたセルの横に〇でもなんでもチェックを入れればいいのでしょうがその方法がわからず投稿させていただきました。
貼付画像はExcel 2013で条件付き書式で在籍者の行を塗るつぶすための検証です。
K1セルに「2017/5/1」と日付を入力しました。
必要な項目は開始日と終了日のみですが要員名も模擬データとして入力してあります。
取敢えず、有効データを20行分で検証しました。
条件付き書式では「数式を使用して、書式設定するセルを決定」を選び、次の数式を入力します。
=AND($H2<$K$1,$I2>$K$1)
塗りつぶす色は好みで選べばよいでしょう。
今回の条件付き書式の対象範囲は$A$2:$I$21です。
質問するときは添付画像のような模擬データを添えて検証可能な状態を提示するようにされると分かり易くなります。

>また今回、上の方からこういうデータが作れたらいいんだけどいい方法あるかな？という質問を受け自分では行き詰ったため投稿致しました。
自分の能力を超える要望に対しては「私にはできません。」と回答する勇気が必要です。
他人のアイディアを丸写しで自分が考えたような報告をすると後日になって困ることが増えます。
「上から」とは「上司から」ですよね？
自分の能力を正当に評価して貰うことで社内での学習機会が与えられるかも知れません。（過大評価になると更に難しい処理を頼まれるかも知れません）

回答No.4

bunjii

>１）この中で2017/5/1時点での在籍者にフラグを立てたいです。
「フラグを立てる」と言うのはどのような結果を期待しているのですか？
「要員名」のセルを塗りつぶす（セルの色を変える）方法で良ければ条件付き書式で対応できます。
J列へ特定の記号を代入するのであればJ列へ数式を設定すれば可能です。

>初心者なので詳しいご説明をいただけると幸いです。
「初心者」は用語が理解できないので詳しい説明に長文になるため回答が困難です。
会社の仕事に関する質問のようなので身近な先輩や上司から指導を受けるようにされた方が良いでしょう。
尚、会社の仕事は利益を追求するものなので、あからさまに第三者の援助を無料で受けようとするのは如何なものでしょう？（先輩があなたを指導するとき先輩の時給が会社の負担になりますが第三者が無料で指導すると僅かであっても会社が第三者から利益を受けることになります）

お礼

コメントありがとうございます。
Excelにあまり詳しいほうではないという自身の認識から初心者という言葉を使わせていただきました。
質問の通り、全部のリストから部署ごと且契約種別ごとに5/1時点の在籍者人数を出したいです。そのため色を塗りつぶして塗りつぶされたセルの横に〇でもなんでもチェックを入れればいいのでしょうがその方法がわからず投稿させていただきました。
また今回、上の方からこういうデータが作れたらいいんだけどいい方法あるかな？という質問を受け自分では行き詰ったため投稿致しました。
まだ入社したばかりということもあり、周りに詳しく聞ける相手がいないため、今回に至りました。申し分けありません

回答No.3

tsubu-yuki

フラグ・・うん。
ご提示の条件「だけ」なら、
ＩＦ関数だけで行けると思うんですが、どうなんでしょう？

まず、
「在籍している（いた）かどうか＝フラグ条件」はどのように判断します？
ご提示いただいた情報から（私が）可能性を挙げられるのは２通り。
（１）終了日が「空白」だったら在籍とみなす
　　　これなら例えば
　　　　　式：=IF(I3="","フラグ","")　　または　　=IF(I3>0,"","フラグ")
　　　で可能かと。個人的には後者が好きです。
（２）終了日が「2017/5/1以降」なら在籍とみなす
　　　のであれば、例えば
　　　　　式：=IF(I3>="2017/5/1"*1,"フラグ","")
　　　とかいう感じでしょうか。「＊１」してやることで
　　　「数値」として扱うように変換しています。
　　　比較したい日付（2017/5/1）がどこか任意のセルにあるなら
　　　　　式：=IF(I3>=J1,"フラグ","")
　　　だけで可能です。

他にも「在籍」についての条件があるなら、
小分けにせず一気に出してくださいね。
※SUMPRODUCT関数を使いたがるってことは、
　他にも条件があるんじゃないのかなぁ、という勘繰りです。

> 初心者なので
関係ないですね。
むしろ、関数で解らないところは
自力で調べるほうが覚えやすくて良いですよ。
というか、ピボットテーブルに発想が行くのであれば、
脱初心者で良いと思いますけどね(笑)。

補足

コメントありがとうございます。
すべてのデータに終了日の記入があったため（２）終了日が「2017/5/1以降」なら在籍とみなす　を使ってみました。
ただ、開始日が未来日のももあったり（2017/7/1など）5/2開始日のものがあり、それに対してもフラグが立ってしまいました。
簡単にいうと4/30までで契約が終わったものしか対象外になりませんでした。やり方が違いますかね

回答No.2

Chiquilin

> sumproductを使って出そうとしたのですがフラグは立てられず
何故？　というかフラグを立てるだけなら SUMPRODUCTなんていら
ないはずです。

行ごとに「2017/5/1以前に開始」且つ「2017/5/1以降に終了」もしくは
「まだ終了していない」かどうかを ANDと ORでチェックするだけで
すよね。

補足

回答ありがとうございます。
仮データを自身で作成した際に、 SUMPRODUCTを用いて5/1時点での在籍者人数を出すことはできたのですが、それを部署ごとに且契約種別ごとに出そうとすると SUMPRODUCTではないと考え、お力をお貸しいただければと思い投稿致しました。
＞行ごとに「2017/5/1以前に開始」且つ「2017/5/1以降に終了」もしくは
「まだ終了していない」かどうかを ANDと ORでチェックするだけで
すよね。
この部分、もう少し詳しく教えていただくことは可能でしょうか

回答No.1

aokii

１）2017/5/1時点での在籍者にフラグを立てる場合は、まず初めに2017/5/1時点での稟議番号の在籍者リストを別シートに作り、人事データのシートのJ列にVLOOKUP関数で2017/5/1時点での稟議番号の在籍者リストを抽出してフラグを立ててはいかがでしょう。
２）部署ごと且契約種別ごとに何名いるかは、お考えの通り、ピポットテーブルで集計する形で宜しいと思います。

補足

ご回答いただきありがとうございます。2017/5/1時点での稟議番号の在籍者リストを別シートに作りとの事ですが、その稟議書がいつ起案されたものかというデータはなく、また目視で5/1時点での稟議番号での在籍者リストを出すのが難しい状況でした。
また開始日も2017/7/1という内容のものも存在しております。