エクセルの学校：全文検索：Hyper Estraier： IF

Results of 1 - 1 of about 37701 for IF (0.008 sec.)

[[20071122151028]]: #score: 1591; @digest: 16183589d8cd58782841df64dab13c5e; @id: 34575; @mdate: 2008-02-05T03:40:44Z; @size: 62781; @type: text/plain; #keywords: 報!$ (363862), 本情 (244797), 索期 (141475), 索日 (83225), 職日 (76756), 退職 (63534), 社日 (62115), 統計 (53414), 成度 (51223), 男") (46658), 索開 (43554), 入社 (36385), 基本 (23380), datedif (20724), 達成 (19079), 情報 (18128), 年齢 (15776), 月数 (15446), 計表 (14082), 期間 (10931), 検索 (9642), 了日 (9605), 社員 (9432), 始日 (9343), 正確 (8044), 人数 (7273), sumproduct (6973), 抽出 (5951), の式 (5501), 業列 (5357), 300 (4423), 場合 (4273); 『過去にさかのぼってデータを閲覧する方法』（ゆきだるま）
こんにちは。いつも皆さんの回答を参考にさせていただいております。現在、いろいろな情報を簡単に管理するためにエクセルで表を作成中です。扱っている情報は、氏名誕生日年齢入社日勤続年数住所電話番号などです。これらのものは『○月現在の状況を把握したい』と考えたときでも、指定した日付によって自動的に計算できるものに作ってあります。しかし表の中には、誕生日などと違い、単純に日付から計算することのできない数字もあります。それは、営業成績のような情報の場合です。これらの情報を、指定した日付現在（過去）で表示する方法を探しています。不定期的に情報の一覧を出すことになっていて、情報の管理に困っているので、どうかご教示下さい。また、できれば情報の最終変更日もわかるようにしたいと思っております。 - - - - - 具体的にどのような表を作っているか示していただければ、回答のしようもありますが、漠然としすぎていて適当な答えが見つかりません。とりあえず、一般論だけ。 >指定した日付現在（過去）で表示する方法を探しています。作っている表にその日付のデータが含まれていれば可能と思いますが、前述のごとく具体的な表のレイアウト等がわかりませんのでこれ以上回答のしようがありません。 >できれば情報の最終変更日もわかるようにしたいそのファイルの最終の保存日が最終変更日ではないのであれば、どこかのセルにその履歴を残しておく必要があります。自動で、というならマクロであれば可能かと思いますが、私の得意分野ではありません。また、何を持って最終変更と見做すかの基準もはっきりさせないといけないと思います。とりあえず未回答のままにしておきます。どなたか他の回答を期待しましょう。 (sato) ---- 質問が内容が曖昧で失礼しました。現在、作っている表は下記のようになっています。 A社員a@B氏名 C性別 D生年月日 E満年齢 F目標・・・情報は、A5から入力されています。Eの項目（満年齢）は、A1に日付を入力すれば、計算できるようになっています。同じように、A1に調べたい日付を入力することで、入力した日付時点の目標達成度（Fの項目）が表示されるようにしたいのですが、何か方法はありますか?Fの項目には、"達成"や"5"や"4"などといった値が入力されます。値が不定期に変更されるので、その都度シートを増やさずに記録できたらと思っています。どうかよろしくお願い致します。（ゆきだるま） ---- >入力した日付時点の目標達成度（Fの項目）が表示されるようにしたいのですが Fの目標達成度が逐次変更されるのであれば、無理です。同じ社員でも変更の都度新しいデータを追加していくなど、前のデータを残しておく必要があります。 (例) A社員a@B氏名 C性別 D生年月日 E満年齢 F目標達成度 G日付 1 ○○ 男 (途中略) 5 H19.4.1 2 △△ 女 3 H19.6.15 1 ○○ 男達成 H19.7.1 3 □□ 男 1 H19.8.20 2 △△ 女 4 H19.9.1 このようなデータであれば、何年何月何日現在での表示ができます。つまり、目標を達成度がいつ現在のものであるかを入力し、また、過去のデータを表示したいのであればその過去のデータも同様に残しておく必要があります。これらデータは、必ずしも同一シートに残しておく必要はありません。実際には同じ社員の人は重複して表示したくないのであれば、データは別シートに作っておき、そこから検索して別シートに社員が重複しないように表示させることも可能です。また、上記の様にしておけば、 G列の日付で最も新しい日付が最終更新日ということも分かりますね。 (sato) ---- 具体的な例をあげてのご回答ありがとうございます。とても理想的な案だと思います。 Satoさんの提案してくださったようなかたちに作っていきたいと思いますが、どのように作っていくのがベストなのでしょうか。＞これらデータは、必ずしも同一シートに残しておく必要はありません。実際には同じ社員の人は重複して表示したくないのであれば、データは別シートに作っておき、そこから検索して別シートに社員が重複しないように表示させることも可能です。これは、あるシート1枚を検索のための基本シートとし、社員が重複しても、変更のたびに追加して入力していくということですか。また、そうであれば、どのようにその基本シートから検索し表示するのかなども含め教えていただきたいと思います。（ゆきだるま） ---- 基本的には、前述の内容のものをどこか適当なシートに作っておけばいいです。データを更新するときは前のデータを消さないで、一番下に（別に途中でも構わないですけど）新しいデータを追加すればいいです。あとは、ゆきだるまさんがどんな結果を求めているのかによって異なります。例えば、特定の社員のある日現在の最新のデータをみたいのか、あるいは、日付を入力するとその日現在のすべての社員のデータを見たいのか・・など、によって違ってきます。自分がしたいことをきちんと整理してみてください。そのほか追加機能としては、社員番号を入力すると氏名、性別、生年月日の入力を省略するために、別のシートに社員マスターのようなものを作っておくのもいいでしょう。（氏名は、結婚などにより変わる可能性がありますのでそのへんも考慮しておくこと) VLOOKUPなどで参照できます。一番大事なのはこの学校でもよく言われていることですが、最初のデータを作るときに、先頭行に項目名、その下の行以下に一行一データで入力しておくこと。行や列を結合したり、一つのセルに2つ以上の内容を入力しないことです。そうすれば、後でそのデータを加工して表示しやすくなります。 (sato) ---- 毎度のことなのですが、説明が足りず申し訳ないです。私自身まだ模索中なところがあるのですが、検索に関しては、日付現在の全ての社員データを出したいと思ってます。ただ、もうひとつ質問なのですが、異なる2つのシートから検索をすることは、可能ですか?というのも、最初の考えでは社員リストは在職者と退職者に分けて、在職者シートと退職者シートの情報が連動するようにするつもりでした。在職者と退職者を分けた場合、日付入力で正確なデータを抽出することは不可能ですよね?この退職者に関しても基本シートにまとめておいて、後で必要な時に検索・表示をしたほうが良いのでしょうか。いろいろとご丁寧にお答えいただきありがとうございます。知識不足で何度も質問してしまいすみませんが、どのように情報を抽出すれば良いのか、具体的におしえてください。（ゆきだるま） ---- 私なら、退職者と在職者は同じシートに入力し、退職・在職の別の項目を設けておきます。そうすれば、必要に応じて在職者のみ、退職者のみ、またはその両方、といったふうに後で検索がしやすいと思います。 (sato) ---- Satoさんのアドバイスのとおり、基本になる情報を全てシート1に入力しました。そして在職者・退職者の分け方なのですが、入社日・退職日という項目を設けました。退職日に日付が入力されていれば、退職したものとみなし抽出できるようにしようと思っています。具体的には、H19.8.30現在の男性在職者というような形で抽出したいのですが、他の質問等を見てもよくわかりませんでした。大変お手数をおかけ致しますが、もう一度ご教示願います。 ---- 一見して、オートフィルターかフィルターオプションが使えそうな気がします。フィルターオプションは実際に使ったことがないので、オートフィルターのほうで考えて見ます。 A B C D E F G 1 ここから H19.5.1 2 ここまで H19.10.30 3 社員氏名性別目標達成度日付抽出社員検索 4 1 ○○ 男 5 H19.4.1 5 2 △△ 女 3 H19.6.15 2 2 6 1 ○○ 男達成 H19.7.1 1 1 7 3 □□ 男 1 H19.8.20 3 1 8 2 △△ 女 4 H19.9.1 2 1 9 3 □□ 男 2 H19.11.15 10 3 □□ 男 3 H19.11.30 サンプルとして簡略化した表ですが、B1の日付からB2の日付の間のデータを抽出します。 F4=IF(AND(E4>=$B$1,E4<=$B$2),A4,"") G4=IF(F4="","",COUNTIF(F4:$F$100,F4)) として下にコピーしておきます。 3行目以下にオートフィルターをかけておいて、G列の検索値が 1 のものを抽出します。そうすると、日付の範囲にある個人ごとの最新のデータを表示することができます。 (日付は昇順に並べておく必要があります) あとはオートフィルターでいろいろな条件で抽出できると思います。 (sato) ---- 具体的なご回答ありがとうございました。しかし試してみたのですが、F列・G列ともにセルには何の値も表示されませんでした。何が悪いのかを考えたのですが、式の意味を完全に理解できていないのでわかりませんでした。 ---- もう一度試してみたらできました。あとは確認作業をしてみたいと思います。また何かお尋ねすることになるかもしれませんが、satoさん本当にいろいろとありがとうございました。（ゆきだるま） ---- satoさん、おひさしぶりです。しばらく時間がとれず確認作業の途中だったのですが、再開してまたひとつ疑問が出てきてしまいました。よろしければ、もう一度ご教示下さい。以前教えていただいた方法は、変更日からの抽出と他の項目をオートフィルタにかけるいうものでした。変更日からの抽出のみですと、毎日変更をしているわけではないので情報の正確さに欠けてしまいます。そこで、まず入社日をオートフィルターにかけてみました。その後、退職している方も一緒に抽出されてしまっているので、退職日も同じ検索日を基準に抽出を再度しました。しかし、まだ退職していない方のセルには何も入力されていないため、検索日以外のものと一緒に排除されてしまいます。せっかくここまで教えていただいたので、より正確な抽出を行いたいと思っているのですが、うまくいきません。説明もうまくできていない気がしますが、よろしくお願い致します。（ゆきだるま） ---- オートフィルタの▼をクリックして、下の方に（空白セル）があります。入社日で抽出したものの中で、退職日が空白の行が抽出されます。また、上の方に（オプション...）とあります。そこで指定する入社日以上以下なども指定できます。 AND OR など色々お試し下さい。 (sin) 外したか? ---- sinさん、早速のご回答ありがとうございます。＞入社日で抽出したものの中で、退職日が空白の行が抽出されます。私の説明不足なのだと思いますが、少し目的が異なります。 ○月○日現在の状況の把握ということなので、退職日が入力されている場合でも検索日以前の退職日ですと、その人物の情報は抽出されなければいけません。＞指定する入社日以上以下なども指定できます。こちらの方法の詳しい説明をしていただけますでしょうか。退職日をオートフィルターにかける場合も入社日を指定しての抽出は可能なのでしょうか。よろしくお願い致します。（ゆきだるま） ---- >＞指定する入社日以上以下なども指定できます。 >こちらの方法の詳しい説明をしていただけますでしょうか。こういう事でよいのでしょうか? 氏名入社日 ▼ 退職日 ▼ （※▼:黒色） a 1990/4/1 2007/3/31 b 1990/9/1 2007/3/31 c 1995/4/1 2000/3/31 d 1995/9/1 2000/8/31 e 2000/4/1 f 2000/9/1 g 2005/4/1 2006/3/31 h 2005/9/1 2006/9/1 i 2006/4/1 とあった場合、 ●入社日が1995/9/1以降で、2006/6/30に在職中の者を抽出するには、 1.入社日で（オプション）を選択し、入社日の下に 1995/9/1 を選択（入力）し、右で以上を選択しOK。 2.退職日で（オプション）を選択し、退職日の下に 2006/6/30 と入力（選択）し、右でより大きいを選択、 OR にチェックを入れ、下のBOXには、入力せず、その右で等しいを選択し、OK。すると、氏名入社日 ▽ 退職日 ▽ （※▽:青色） e 2000/4/1 f 2000/9/1 h 2005/9/1 2006/9/1 i 2006/4/1 となります。 (sin) ---- sinさんありがとうございます。正確に抽出することができました。ただ、またひとつ質問があります。最初の予定では、シート2に統計表を作ってシート1とリンクすることによっていつでもすぐに社員の状況を把握できるようにしたいと考えていました。いまシート1に抽出元になるデータをすべて入力しています。そのことで、統計表では正確な数値が表示できない状態です。シート1で抽出を行って、その抽出を統計表に正確に反映させるには、抽出後に一度別シートにコピーなどをして、そこから統計表にリンクさせる方法でよいのでしょうか。他に良い方法があれば教えて下さい。ちなみに統計表は、男女別の人数・男女別の平均年齢・細かい年齢別の人数・達成度別の人数などで構成されています。 (ゆきだるま） ---- 途中の内容を読まずに >まず入社日をオートフィルターにかけてみました。 >その後、退職している方も一緒に抽出されてしまっているので、 >退職日も同じ検索日を基準に抽出を再度しました。しかし、まだ退職していない方の >セルには何も入力されていないため、検索日以外のものと一緒に排除されてしまいます。ここのみに反応して書き込んでしまったので、流れがグチャグチャになっているかもしれません。ただ、読み返すと流れとしても問題ないような気が・・・で、ご質問の意図は、Sheet1が元データ（私の書き込みでは上の表の状態）で Sheet2に抽出データ（下の表の状態）を出し、この表を統計表に使いたいという事でしょうか? ・統計表自体をEXCEL上でコピーし、ここに貼り付ける事は可能でしょうか? 表の構成を知りたいだけなので、中のデータは適当に変えていただいて結構です。抽出データは、そのままSheet1の状態で、Sheet2に統計表を作れないか考えてみます。ただ、時間が取れ次第ですので、お急ぎの際は、コピペで対応された方が早く解決できると思います。 (sin) 取り急ぎの書き込みで申し訳ありませんが・・・ ---- 統計表は、抽出データの集計と思われます。・【データ】-【集計】を使われているとすれば、コピーするほかないと思います。・計算式を使って処理されているのでしたら、 satoさんも書かれている作業列を設けて、SUMPRODUCT関数などで処理できそうです。ライブラリのSUMPRODUCT関数のところリンクしておきます。ご一読下さい。 http://www.excel.studio-kazu.jp/lib/e3h/e3h.html (sin) ---- 丁寧なご対応ありがとうございます。そして質問をなげかけておいて返信が遅くなり失礼しました。＞ここのみに反応して書き込んでしまったので、流れがグチャグチャになっているかもしれません。＞ただ、読み返すと流れとしても問題ないような気が・・・流れとしてはしっかり伝わっていると思うので大丈夫です。＞抽出データは、そのままSheet1の状態で、Sheet2に統計表を作れないか考えてみます。私も本来ならば抽出した状態でシート2の統計表にリンクさせたいと思っています。しかし、抽出した状態といっても、他のデータが隠れているだけにすぎないようで、リンクさせてある統計表は、抽出前と結果がかわりません。統計表は、計算式を使って処理しています。具体的には下記のような表になっております。 SUMPRODUCT関数の方法で可能でしょうか。何度もすみません。（ゆきだるま）「統計表」検索日を入力 → ○○○ から ○○○ まで平均年齢達成度別全体 30 全体 #REF! 男女全体男性 11 男 #REF! 5 0 1 1 女性 19 女 #REF! 4 3 5 8 3 1 6 7 2 1 2 3 年齢別 1 2 3 5 男女全体 2 1 0 1 51以上 2 2 4 1 1 2 46〜50 2 6 8 2 1 3 41〜45 1 3 4 その他 0 0 0 36〜40 3 6 9 11 19 30 31〜35 0 2 2 25〜30 2 0 2 24以下 1 0 1 合計 11 19 30 ---- Sheet1のB列が入社日、C列が退職日とし、Sheet2のD1が検索開始日、D2が検索終了日とした場合、元の表の空いている列（例えばI2セル）に =IF(AND(B2>=Sheet2!$D$1,B2<=Sheet2!$D$2,OR(C2="",C2>Sheet2!$D$2)),1,"") とすれば、入社日が、Sheet2のD1:D2の期間で、退職日がD2以降か空欄の行のI列に 1 と入ります。全体数は、=SUM(Sheet1!$I$2:$I$500) で 1となっている数（人数）が出ます。※行数は適当です。性別が D列にあるとすれば、 =SUMPRODUCT((Sheet1!$I$2:$I$500=1)*(Sheet1!$D$2:$D$500="男"))とすれば男の人数が出ます。年令がE列にあるとすれば、 =SUMPRODUCT((Sheet1!$I$2:$I$500=1)*(Sheet1!$D$2:$D$500="男")*(Sheet1!$E$2:$E$500>=46)*(Sheet1!$E$2:$E$500<=50)) とすれば男で46歳以上50歳以下の人数が出ます。こんな感じですが、アレンジしてみてください。尚、手打ちしてますので、誤記入らしき箇所は修正してください。 (sin) 追記:誤入力修正（まだあるかも?）。 $マークを追加し絶対参照にしました。→コピー編集時に楽になる事を言いたいだけですが・・・ ---- パソコンが使えない状態にあり、返信遅くなりました。すみません。大変わかりやすい説明だったのですが、統計表の平均を出す式はうまくできませんでした。また、それとは別に新たな問題が発生して悩んでいます。入社日・退社日・勤務日数という項目があるのですが、勤務日数をすぐに計算できるにはどのような方法が良いでしょうか。理想としては、指定した検索日をもとに計算して、検索日より以前に退職しているのであれば、退職日から計算したいと思っています。現在の状態としては、入社日順に並んでいます。そのためところどころに退社した人がいるため、上から式をコピーできません。本当に申し訳ないのですが、よろしければご教示下さい。（ゆきだるま） ---- 適当なセルで試しているので、適応するセル位置に変更してください。 H I J K L 1 入社日退職日在籍日数 2002/4/1 2005/9/30 2 2000/4/1 2001/3/31 0 3 2001/4/1 2007/3/31 1644 4 2005/4/1 2005/6/30 91 5 2006/4/1 0 K1が検索開始日、L1が検索終了日として、 J2に =IF(H2>$L$1,0,IF(AND(I2<>"",I2<$K$1),0,IF(I2="",$L$1,MIN(I2,$L$1))-H2+1)) 入社日が、L1より大きい（後の）場合は、0。退職日に入力が有り、しかも、K1より小さい（前の）場合は、0。上記で無い場合、退職日が未入力の場合は L1 、入力が有る場合は、退職日とL1との小さい日から入社日を引き、1日プラス（入社日当日分）。 ※入力が有る場合は、退職日とL1との小さい日 >>>>> IF関数で事前にK1より退職日が小さい場合を除いているので、退職日の入力値は、K1以降となります。また、検索日未入力の場合も処理として入れるなら、 =IF(COUNT(K1,L1)<2,IF(I2="",TODAY(),I2)-H2+1,__上の式__) という感じでしょうか? こういう事でいいのでしょうか? 前回のも、今回のもオートフィルタ関係なくなっちゃいましたね。。。 (sin) ---- ありがとうございます。オートフィルタでなければいけないわけではないので大丈夫です。ただ、満月数で求めて欲しいということを他のものに言われまして・・・せっかくsinさんに式を伺ったのですが、またわからなくなってしまいました。 DATEDIFを使うときみたいに"M"とかを式の中に入れれば良いのでしょうか・・・あと、以前お伺いした統計の出し方なのですが、シート1に変更したものをどんどん下へと追加記入してみたところ、同じ人物でも別々にカウントされてしまい、シート2で正確に統計できなくなってしまいました。何がおかしいのかチェックはしているのですが、まだ見つけられていません。取り急ぎ報告でした。（ゆきだるま） ---- >DATEDIFを使うときみたいに"M"とかを式の中に入れれば良いのでしょうか・・・ IF(I2="",$L$1,MIN(I2,$L$1))-H2+1 この部分を DATEDIF(H2,IF(I2="",$L$1,MIN(I2,$L$1)),"m") でよいのでは? >同じ人物でも別々にカウントされてしまい、シート2で正確に統計できなくなってしまいました。これは、目標達成度の関係で追記された分を指していると思いますが、(この点に関し、見逃していました。m__m）前回書き込んだ作業列を修正（例として:1→ROW()、""→0)し、もう一つ作業列を追加すれば、出来そう??? 以下は、重複がある場合より下の行を優先する例です。あいかわらず、適当な配置です。 A B C D E F 1 氏名入社日退職日在籍月数 2003/9/1 2006/6/30 2 a 1990/4/1 2007/3/31 194 2 1 3 b 1990/9/1 2007/3/31 189 3 1 4 c 1995/4/1 2000/3/31 0 5 d 1995/9/1 2000/8/31 0 6 e 2000/4/1 6 7 e 2000/4/1 7 8 g 2005/4/1 2006/3/31 11 8 1 9 h 2005/9/1 2006/9/1 9 9 1 10 e 2000/4/1 74 10 1 E1が検索開始日、F1が検索終了日。 E2以下で0以外が、検索期間内に在籍している行番号です。 ※前回のものが違ってるような気がするのでするので変更しました。 =IF(OR(B2>$F$1,AND(C2<>"",C2<=$E$1)),0,ROW(A2)) F2以下が、E列が0以外で、重複がある場合は、より下の行のものに 1 を入れてます。 =IF(E2=0,"",IF(COUNTIF($A$2:$A$10,A2)=1,1,IF(E2=MAX(INDEX(($A$2:$A$10=A2)*$E$2:$E$10,0)),1,""))) より上の行の場合は、 =IF(E2=0,"",IF(COUNTIF($A$2:$A$10,A2)=1,1,IF(E2=MATCH(A2,$A$1:$A$10,0),1,""))) としてください。 D2も一応書いておきます。 =IF(F2="","",DATEDIF(B2,IF(C2="",$F$1,MIN(C2,$F$1)),"m")) こんな感じでよいのでしょうか? (sin) ---- 理解力がなく、少し混乱しているので整理をさせてください。＞DATEDIF(H2,IF(I2="",$L$1,MIN(I2,$L$1)),"m") でよいのでは? こちらの式に変えて満月数を出すことができました。式の一番最初に =IF(入社日="","" というものをプラスして、下へ式をコピーした際に余計な値が出ないようにしましたが、式そのものへの影響は大丈夫でしょうか? 検索日を入力しない場合に表示が満月数から日数に戻っているようなのですが、このアレンジの影響??ですか? ＞D2も一応書いておきます。＞=IF(F2="","",DATEDIF(B2,IF(C2="",$F$1,MIN(C2,$F$1)),"m")) こちらの式は在籍月数を出すための式ですか? 今までの流れで教えていただいたとおりに式を作ると、もっと長い式になるのですが・・・＞※前回のものが違ってるような気がするのでするので変更しました。理解できていないのですが、変更したとすると下記の式にはならないのでは・・・ =IF(F2="","",DATEDIF(B2,IF(C2="",$F$1,MIN(C2,$F$1)),"m")) せっかく教えていただいているのですが、理解力が足りないようで混乱しています。表の配置が変わっているので、間違った読み取り方をしているのかもしれません。少し落ち着いて見直してみます。（ゆきだるま） ---- 上の方に書いた >DATEDIF(H2,IF(I2="",$L$1,MIN(I2,$L$1)),"m") は、前のご質問に対し書き込んだものです。これが、混乱の原因ですね。無視しちゃって下さい。下の方は、名前が重複してある場合（目標達成度の関係で追記された分）の略表に対しての書き込みです。（>以下は、重複がある場合より下の行を優先する例です。あいかわらず、適当な配置です。以降です。）検索期間が未記入の場合は、E列が全て0となるので、F列は""が返ります。その結果、在籍月数のD列も""が返ります。各式のセル位置実際の表のセル位置に置き換えると、求められるはずです。 (sin) ---- すみませんが、混乱していて式そのものの確認ができないので、順番にひとつずつ整理をしてよろしいですか?すみません。まずは在職満月数を出す式についての疑問です。 H I J K L 1 入社日退職日在籍日数 2002/4/1 2005/9/30 2 2000/4/1 2001/3/31 0 3 2001/4/1 2007/3/31 1644 4 2005/4/1 2005/6/30 91 5 2006/4/1 0 K1が検索開始日、L1が検索終了日とする在職満月数を出す式（検索日未入力の場合も処理）J2に下記の式を入力 =IF(COUNT(K1,L1)<2,IF(I2="",TODAY(),I2)-H2+1,__上の式__) 上の式と書かれたところに次の式を追加する =IF(H2>$L$1,0,IF(AND(I2<>"",I2<$K$1),0,IF(I2="",$L$1,MIN(I2,$L$1))-H2+1)) そして上記のIFより後半をDATEDIF(H2,IF(I2="",$L$1,MIN(I2,$L$1)),"m")に変更というような意味でよろしかったですか? ＞上の方に書いたDATEDIF(H2,IF(I2="",$L$1,MIN(I2,$L$1)),"m")は、前のご質問に＞対し書き込んだものです。こちらはわかるのですが・・・＞※前回のものが違ってるような気がするのでするので変更しました。＞=IF(OR(B2>$F$1,AND(C2<>"",C2<=$E$1)),0,ROW(A2)) ということは、在職期間を出す最終的な式は次のものでよいですか? =IF(COUNT(K1,L1)<2,IF(I2="",TODAY(),I2)-H2+1,IF(OR(B2>$F$1, AND(C2<>"",C2<=$E$1)),0,ROW(A2)IF(H2>$L$1,0,,0,DATEDIF(H2,IF (I2="",$L$1,MIN(I2,$L$1)),"m") しかしそうすると、次の意味がわからなくなってしまいます。＞D2も一応書いておきます。＞=IF(F2="","",DATEDIF(B2,IF(C2="",$F$1,MIN(C2,$F$1)),"m")) どこか勘違いをしているとすれば、具体的に指示をお願いします。（ゆきだるま） ---- まず、最初の点に関して、 ''検索日未記入[COUNT($K$1,$L$1)<2]の場合の処理''が、''日数計算のまま''ですので、ここも変えましょう。 ※本来は、未記入ですと <0 ですが、検索開始・終了日の両方に入力ないものを未記入扱いとしています。 =IF(COUNT($K$1,$L$1)<2, DATEDIF(H2,IF(I2="",TODAY(),I2),"m"), IF(H2>$L$1,0,IF(AND(I2<>"",I2<$K$1),0, DATEDIF(H2,IF(I2="",$L$1,MIN(I2,$L$1)),"m")))) 改行してます。''2行目''を日数計算→''月数計算に変更''してます。 >※前回のものが違ってるような気がするのでするので変更しました。これは''作業列に設定する式''を指します。 >>Sheet1のB列が入社日、C列が退職日とし、Sheet2のD1が検索開始日、D2が検索終了日とした場合、 >>元の表の空いている列（例えばI2セル）に >>=IF(AND(B2>=Sheet2!$D$1,B2<=Sheet2!$D$2,OR(C2="",C2>Sheet2!$D$2)),1,"") 入社日が、検索開始日以上で、入社日が検索終了日以下で、退社日が未入力か検索終了日より後の場合が1、以外が""。 ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾この点がおかしいと考えたので、 >>=IF(OR(B2>$F$1,AND(C2<>"",C2<=$E$1)),0,ROW(A2)) 入社日が検索終了日より後か退職日に入力がありその日付が検索開始日以前の場合は、0、以外がA列の行番号。前者では、''検索開始日以前に入社した人が含まれない''のです。 >=IF(F2="","",DATEDIF(B2,IF(C2="",$F$1,MIN(C2,$F$1)),"m")) この式は、''直近に記入したE・F列に作業列を設けた表''において在籍月数をもとめる式ですが、検索日が未入力の場合（F2=""の状態）では、""となります。今日までの期間での在籍月数も求めるならば、（これも改行してます） =IF(F2="", DATEDIF(B2,IF(C2="",TODAY(),MIN(C2,TODAY())),"m"), DATEDIF(B2,IF(C2="",$F$1,MIN(C2,$F$1)),"m")) とすることになります。実際面では MIN(C2,TODAY()) → C2 のみでよいでしょう。退職日の入力は今日(TODAY())以前でしょうから、MIN関数における比較が不要です。ご理解いただけましたでしょうか? (sin) ---- ご丁寧に説明いただいてすみませんでした。意味のほうは理解できたと思います。実際に入力しながら試してみて、また結果をご報告致します。（ゆきだるま） ---- 昨日教えていただいたとおりに式を入力してみました。 >>=IF(OR(B2>$F$1,AND(C2<>"",C2<=$E$1)),0,ROW(A2)) >>入社日が検索終了日より後か退職日に入力がありその日付が検索開始日以前の場合は、 >>0、以外がA列の行番号。こちらの式では、値が0以外であれば行番号がかえるということですよね? 行番号でいうと10からデータが入力されているのですが大丈夫ですか? また、こちらの式は空いている列であればどこでも構わないのですか? 統計表への正確なカウントが目的なのですが、こちらの作業列を利用すればよいのですか? >>E2以下で0以外が、検索期間内に在籍している行番号です。 >>※前回のものが違ってるような気がするのでするので変更しました。 >>=IF(OR(B2>$F$1,AND(C2<>"",C2<=$E$1)),0,ROW(A2)) >>F2以下が、E列が0以外で、重複がある場合は、より下の行のものに 1 を入れてます。 >>=IF(E2=0,"",IF(COUNTIF($A$2:$A$10,A2)=1,1,IF(E2=MAX(INDEX(($A$2:$A$10=A2) >>*$E$2:$E$10,0)),1,""))) こちらの式ですが、流れではE列は検索開始日だと思うのですが、$E$2:$E$10という部分で間違いはないでしょうか。また、こちらの式はどこに入力すればよいのでしょうか? >>=IF(F2="","",DATEDIF(B2,IF(C2="",$F$1,MIN(C2,$F$1)),"m")) >>この式は、直近に記入したE・F列に作業列を設けた表において在籍月数をもとめる式ですが、 >>検索日が未入力の場合（F2=""の状態）では、""となります。 >>今日までの期間での在籍月数も求めるならば、（これも改行してます） >>=IF(F2="", >>DATEDIF(B2,IF(C2="",TODAY(),MIN(C2,TODAY())),"m"), >>DATEDIF(B2,IF(C2="",$F$1,MIN(C2,$F$1)),"m")) >>とすることになります。 >>実際面では MIN(C2,TODAY()) → C2 のみでよいでしょう。 >>退職日の入力は今日(TODAY())以前でしょうから、MIN関数における比較が不要です。こちらに関しては、私の質問に答えるために書き加えていただいたのでしょうか? それとも先ほど最初に示していただいた次の式では不完全だということでしょうか? =IF(COUNT($K$1,$L$1)<2, DATEDIF(H2,IF(I2="",TODAY(),I2),"m"), IF(H2>$L$1,0,IF(AND(I2<>"",I2<$K$1),0, DATEDIF(H2,IF(I2="",$L$1,MIN(I2,$L$1)),"m")))) 本当に質問ばかりですみません。読み返してみて、理解できていなかったことに気がつきました。最初の段階で一度にたくさんのことを質問してしまったのがよくなかったと思います。回答がいつのものに対応しているのか、初心者の私には整理できなくなってしまっています。（ゆきだるま）検索日を入力 → からまで氏名性別生年月日満年齢達成度変更日抽出社員検索値入社日退社日入居満月数 1 2 3 4 5 ---- 書き込みながら時間が掛かって・・・・衝突。。。以下は、最後のレイアウトが書き込まれる前に書き込もうとした内容です。ご確認下さい。 =IF(OR(B2>$F$1,AND(C2<>"",C2<=$E$1)),0,ROW(A2)) ‾‾‾‾検索終了日 ====検索開始日のセルに変更。 >こちらの式では、値が0以外であれば行番号がかえるということですよね? IF関数の条件（OR(B2>$F$1,AND(C2<>"",C2<=$E$1)) が、TRUEの場合:0 FALSEの場合:行番号のどちらかが返ります。 ※エラー発生時は、その限りでありませんが、適切にセル位置を変更すれば、エラーは出ないはずです。 >行番号でいうと10からデータが入力されているのですが大丈夫ですか? 大丈夫です。但し、変更済みとは思いますが、B2,C2,A2の2は、10にして下さい。 >また、こちらの式は空いている列であればどこでも構わないのですか? 結構です。X列ならでもBC列でも。但し、行位置は同じにして下さい。 >統計表への正確なカウントが目的なのですが、こちらの作業列を利用すればよいのですか? 正確なの意味合いによります。この式では、検索期間内の行全てに行番号が返るだけです。重複問題を解決するには↓のもう一つの作業列を使います。 =IF(E2=0,"",IF(COUNTIF($A$2:$A$10,A2)=1,1,IF(E2=MAX(INDEX(($A$2:$A$10=A2)*$E$2:$E$10,0)),1,""))) ‾‾ ============= ‾‾ ========== == ‾‾‾‾‾‾‾‾‾‾ 検索期間での結果が0なら"",0でない場合、氏名のデータ範囲での数が1ならば1、 2つ以上の場合、E2セルの式の結果と同名か否か(TRUE またはFALSE）とE列の数値をかけた配列内の最高値が同じならば、1違っていれば""を返す。という意味の式です。（同名の場合:A2=A2→TRUE→1として計算,異名の場合:A3=A2→FALSE→0として計算） ‾‾部分は、上で作った作業列の列位置に変更してください。 ==部分は、氏名もしくは社員番号（一人一人を識別できるもの）が入っている列に変更。行番号の2は、10行目からデータが始まる場合は、10 にし、データとして使用する最後の行番号までの範囲に変更してください。（$A$2:$A$10の範囲に氏名があるという想定にしてました） >こちらの式ですが、流れではE列は検索開始日だと思うのですが、$E$2:$E$10という部分で間違いはないでしょうか。上でも書きましたが、検索開始日は E1セルに入力が前提で、 $E$2:$E$10は、上の（検索期間内に該当するか否か）作業列の範囲です。上でAA列に作業列を設けたら、AA列の範囲で指定してください。 >また、こちらの式はどこに入力すればよいのでしょうか? これも作業列ですので、どこでも良いですが、上で作った作業列とセットで見れるのが良いと思います。上の作業列をAA列に設けたならば、AB列に設けましょう。 ◆この式は、同一人物の重複があった場合（COUNTIF関数で2以上が返った場合）に、データ範囲内の一番下の行を個人の唯一の対象データとするためのものです。最後のDATEDIFに関しては、深く触れません。理由:式の作り方はご理解されたと思いますから、実際のデータでご自身が考える式を入れてみて下さい。もし、上手く行かない場合には、一緒に考えましょう。 (sin) ---- なかなか理解できなくてすみませんでした。丁寧に何度も教えていただいて本当に助かります。現段階のシート1の状況を表示しますので、ご確認下さい。 ※E5=検索開始日,E6=検索終了日とする。 ※実際のデータは、10行目から入力 B C D E F G H I J K L M U V ID 氏名性別誕生日年齢達成度日付抽出a@検索値入社日退社日在職満月数 @ A B C D E F G H I J K L M G=IF(AND(H10>=$E$5,H10<=$E$6),B10,"") H=IF(I10="","",COUNTIF(I10:$I$100,I10)) K=IF(K10="","",IF(COUNT($E$5,$E$6)<2,DATEDIF(K10,IF(L10="",TODAY (),L10),"M"),IF(K10>$E$6,0,IF(AND(L10<>"",L10<$E$5),0,DATEDIF(K10,IF (L10="",$E$6,MIN(L10,$E$6)),"M"))))) L=IF(OR(K10>=$E$6,AND(L10<>"",L10=$E$5)),0,ROW(B10)) M=IF(U10=0,"",IF(COUNTIF($C$10:$C$100,C10)=1,1,IF(U10=MAX(INDEX (($C$10:$C$100,C10)*$U$10:$U$100,0)),1,""))) ☆質問 1 Lの式の最後はIDの列を指してるんですが、大丈夫ですか? データが重複してある人は、同じIDが並ぶ場合がありますが・・・ 2 私は作業列をUに作ったのですが、検索日セルの列につくらなければいけないわけではないですよね? 3 年齢の出し方にもKの式は応用可能ですよね? 4 Mの式ですが、うまくできました。ただ、先ほども目的として述べたのですが、シート1で1を返したものだけをシート2（統計表）でカウントしたいです。いまの式だとVALUEが出てる部分があるからなのか、SUMやSUMPRODUCTで値が拾えず、シート2でもVALUEが出ています。ちなみにシート2（統計表）の状況も書いておきます。 ※D5=検索開始日,D6=検索終了日とする（シート1からのリンク貼り付け） B C D E G H I J 平均年齢達成度別全体 @ 全体 C 男女全体男性 A 男 D 5 J L 女性 B 女 E 4 K M 3 2 年齢別 1 男女全体 2 51以上 F H 46〜50 G I 41〜45 36〜40 31〜35 25〜30 24以下合計 @=SUM(基本情報!$V$10:$V$300) A=SUMPRODUCT((基本情報!$V$10:$V$300=1)*(基本情報!$D$10:$D$300="男")) B=SUMPRODUCT((基本情報!$V$10:$V$300=1)*(基本情報!$D$10:$D$300="女")) C考え中 D考え中 E考え中 F=SUMPRODUCT((基本情報!$V$10:$V$300=1)*(基本情報!$D$10:$D$300="男")* (基本情報!$F$10:$F$300>=51)) G=SUMPRODUCT((基本情報!$V$10:$V$300=1)*(基本情報!$D$10:$D$300="男")* (基本情報!$F$10:$F$300>=46)*(基本情報!$F$10:$F$300<=50)) J=SUMPRODUCT((基本情報!$V$10:$V$300=1)*((基本情報!$D$10:$D$300="男")* (基本情報!$G$10:$G$300=5))) 上記のようなかたちになっております。ご確認よろしくお願い致します。（ゆきだるま） ---- まずは、Sheet1を片付けちゃいましょう。。。・H列の日付ですが、ここにデータ更新する日付を入力するのですね? この日付が、検索開始日〜検索終了日の期間に当たるか否かという事ですよね? →とした場合に、I列の抽出No. は、何に使うのでしょうか? 検索期間に該当するIDを出すだけのものならば、U列で0以外の物と同じ行にIDを出すのみなら不要な気がします。これは、ゆきだるまさんが必要なのだ! という事でしたら、そのままにしてください。同様に、J列の検索値もI列で同じIDが、何個有るかを出されているもので、重複確認に使われているだけなら不要な気がしますが、必要なのだ! という事でしたら、そのままにしてください。・L=IF(OR(K10>=$E$6,AND(L10<>"",L10=$E$5)),0,ROW(B10)) 退職日が検索開始日と同じでよければ、そのまま。開始日以前とするなら L10<=$E$5 です。・Mの式に関しては、もし同姓同名の方がいらっしゃるのであれば、IDの列を使うべきですね。式自体も成り立っていないので、IDの列を使った式を書きます。 =IF(U10=0,"",IF(COUNTIF($B$10:$B$100,B10)=1,1,IF(U10=MAX(INDEX(($B$10:$B$100=B10)*($U$10:$U$100),0)),1,""))) ご質問に関して、 1:行番号を取りたいだけなので、列位置は、どこでもOKです。 2:作業列U・V列は、邪魔にならない位置であれば、どこでもOK〜♪ 3:年齢は、応用でもいいですが、検索期間に関係なく、普通に出してもおいても良いような気がします。 4:式が成り立っていなかったので、上の式に直してください。ここまで書き終えてしまいましたが、途中で気付いた事があります。 Gの式で、検索期間内の該当IDを出して、 Hの式で重複のある場合は、下の行に近い方が 1 になるように作られてますから、 U・V列がなくても、J列が 1 の所が、該当行になってますよ! おめでとうございます。。。 (sin) Sheet2はこれから見させていただきます。 ---- Sheet2見させていただきました。よろしいのでは、ないでしょうか? << 考え中 >> に関して @ABで人数が出てますよね。 Sheet1(基本情報シート）のF列で、検索期間中での年齢を出されようとしてますよね? 年齢の合計を人数で割れば、平均年齢になりませんか? (sin) ---- ひょっとして? と思い、読み返してみると、すでにsatoさんが・・・変な時間を取らせる事になってしまって、申し訳ございませんでした。 (sin) ---- 確認をしてくださってありがとうございます。 sinさんがシート1を確認してくださった後の書き込みに対して返信したつもりだったのですが・・・更新ボタンを押し忘れたのか、書き込みの更新ができていませんでした。失礼しました。なので、既にお気づきかと思いますが、いちおう説明をさせていただきます。まず、H列にはデータを更新した日付を入力します。この日付が、検索期間に当たるものであればI列に抽出番号が表示されます。抽出番号に同じものがあるとすれば、検索期間中で最新のものに1を表示するように J列に設定してあります。それで私もなんとなく重複している気がしていたのですが、I列・J列は正確に機能しているようなので、とりあえずはいじらないでおいたのでした。また、satoさんの書き込みをsinさんが読んでくださった上での回答だと思っていたので、また違う意味をもつのかなぁと考えていました。 satoさんに対しての質問は、不定期に変化する個人のデータを保存管理し、過去の分も簡単に抽出するには、どういった形式でつくるのがベストなのかという内容でした。なので、その時点では、統計表のことまでは話しておらず、抽出するということまでに留まっています。その後、抽出したデータを自動的に統計表に反映させたいということでsinさんのお力を借りていたところでした。ですので、U列・V列が不必要であるとすれば、私の目的が伝わっていないのかもしれません。現に、Mの式を修正してみたのですが、統計表では求める正しい結果が反映されていませんでした。説明が不十分だったようなので、もう一度しっかりと説明させていただきます。 @検索期間に対応した個人のデータを抽出したい。達成度の項目が年齢などと違い、不定期に変動するため計算しづらかった。 →こちらはsatoさんのお考えで全てのデータを一枚に入力し、抽出・検索の作業列を設けることで解決。 A抽出・検索の作業列で抽出された（検索期間内で最新の）データを自動的にシート2の統計表に反映させたい。 →J列にデータの順番を表示するまでは、正確にできている。しかし、その後オートフィルタで検索期間内に既に退去した人を除く作業をするとオートフィルタ機能は正しく機能するが、表が折りたたまれて隠れた状態になったにすぎず、統計表には退去者を除く前のデータが反映される。説明しながら思ったのですが、M番の式ではだめだということですよね? やはりsatoさんの式に重複していますよね・・・? ＞Mの式に関しては、もし同姓同名の方がいらっしゃるのであれば、IDの列を使うべきですねあとこちらに関しては、これから先も実際に同姓同名の社員が在籍することはないでしょう。ただ、個人データを変更する際に書き換えるのではなく、追加して入力していくので、表の中に同姓同名がいるということはあります。ここまで今までの流れを説明しただけにすぎないのですが、伝わりましたでしょうか。もし話をややこしくしていたら、すみません。（ゆきだるま） ---- 結論的にはSheet1のU・V列の意図は、I・J列に組み込まれてますから、不要ですね。という事です。その上で、ちょっと説明です。 >現に、Mの式を修正してみたのですが、統計表では求める正しい結果が反映されていませんでした。これは、U列の検索対象日が、K・L列の入社日・退社日になっているからですね。 H列の日付にすれば、正しい結果が反映されるはずです。 Sheet2で、使っているV列をJ列にすれば、修正完了! となると思います。 J列は、IDが関係してますし。 (sin) ---- ＞結論的にはSheet1のU・V列の意図は、I・J列に組み込まれてますから、不要ですね。それでは、U・V列の作業列は削除しても大丈夫なのですか? ＞これは、U列の検索対象日が、K・L列の入社日・退社日になっているからですね。＞H列の日付にすれば、正しい結果が反映されるはずです。＞Sheet2で、使っているV列をJ列にすれば、修正完了! となると思います。こちらの作業も試しましたが、やはり違うようです。私が入力している式はsinさんと共に確認済みなはずですので、式の入力間違いではないと思われるのですが・・・しつこいと思われるでしょうが、もう一度説明します。今の式ですと、確かにI・J列の作業によって1と表示された人数をカウントすることができます。しかし、その中には既に退去した方のの情報も含まれてしまっているのです。現在、退去者を除く方法としてオートフィルタにかけるという作業のみだからではないかと考えています。オートフィルタで抽出しても、一時的に不要なデータを隠しているだけにすぎないと思うのです。 (ゆきだるま） ---- すみません。シート2（統計表）の式を修正して、正確に統計することができました。例・シート2の式↓ @=SUMPRODUCT((基本情報!$V$10:$V$300=1)*(基本情報!$L$10:$L$300="")) A=SUMPRODUCT((基本情報!$V$10:$V$300=1)*(基本情報!$D$10:$D$300="男")* (基本情報!$L$10:$L$300="")) あとは、もう一度確認をしてみます。（ゆきだるま） ---- おりょ、また衝突。。。式部分を直されたご様子ですが、そのままUPします。 >しかし、その中には既に退去した方のの情報も含まれてしまっているのです。含む・含まないは、H列の日付が検索期間内か否かでしか判断してませんよね。でも、修正済みの式で基本情報!$L$10:$L$300="" が入っているから、退去者は対象外となってます。という事は、退去日に日付のある方は、対象外とするのですね。既に、Sheet2のAの式は修正されていますが、根本のSheet1のJ列（SUMPRODUCTの式ではV列になってますが） >H=IF(I10="","",COUNTIF(I10:$I$100,I10)) を =IF(OR(I10="",L10<>""),"",COUNTIF(I10:$I$100,I10)) とすれば、退去日に入力のある方は、含まれなくなります。ちょっと疑問??? 昨日以前だと、退去日にしても日付にしても、検索期間内に在籍していれば、加算対象でしたよね? まだ、なんか噛み合ってない気もするけど、こちらで読み取れる範囲でカキコしました。 (sin) ---- ＞退去日に日付のある方は、対象外とするのですね。検索期間内で退去しているのであれば、対象外としようと思っています。特別でない限り、退去した人をカウントしなくてよいだろうと・・・ただ、退職者を数えて欲しいと言われた場合は、別の統計表を作らなくては・・・と思ってます。＞SUMPRODUCTの式ではV列になってますがすみません、これはJ列ですね。入力ミスです。でもsinさんの言うとおり、根本のシート1のJ列の式を変更したほうが、簡単ですね・・・＞昨日以前だと、退去日にしても日付にしても、検索期間内に在籍していれば、加算対象でしたよね? 確かにまだ噛み合っていないような、、、退去した人でもその退去日が検索終了日より1日でも後だと、統計に加算はします。書いていて気づいたのですが、 @=SUMPRODUCT((基本情報!$V$10:$V$300=1)*(基本情報!$L$10:$L$300="")) @の式では、誤差が出てしまいます??? (ゆきだるま） ---- >@の式では、誤差が出てしまいます??? でしょうね! Sheet1のHの式を =IF(OR(I10="",AND(L10<>"",L10<=$E$6)),"",COUNTIF(I10:$I$100,I10)) これで、I10が""の場合か L10が""でなく検索終了日(E6セル)以前の場合は、"" となります。また、Sheet2の各式の基本情報!$L$10:$L$300="" 部分は、取ってください。 ※結果は、取っても取らなくても同じでしょうが、演算の負荷を減らしたほうが良いと思いますので。。。 (sin) ---- シート1のHの式は理解しました。OKです。ただ、シート2の各式はご指摘通りに省いたのですが、@だけ値が"0"になってしまいましました。 ??? もう一度確認してみます。（ゆきだるま） ---- =SUM(基本情報!$J$10:$J$300) とするか =SUMPRODUCT(N(基本情報!$J$10:$J$300=1)) としてみてください。 (sin) ---- 考え中の式と他の修正したものの確認をしていました。時間がかかり過ぎですみません。シート2の@の式は、"N"をプラスして解決しました。よろしければ意味的なことも教えて下さい。シート1のDの式 D=IF(E10="","",IF(COUNT($E$5,$E$6)<2,DATEDIF(E10,IF(L10="",TODAY (),L10),"Y"),IF(E10>$E$6,0,IF(AND(L10<>"",L10<$E$5),0,DATEDIF(E10,IF (L10="",$E$6,MIN(L10,$E$6)),"Y"))))) シート2のCの式 C=SUMIF(基本情報!$J$10:$J$300,1,基本情報!$F$10:$F$300)/COUNTIF (基本情報!$J$10:$J$300,1) 上記の式は、手直しを加えたものです。確認してみていただけませんか? シート2のDとEに関しては、うまくできませんでした。（ゆきだるま） ---- むちゃくちゃ忙しくて、遅くなりました。といってもまだまだ片付かないので、尻切れになるかもしれませんが・・・ >シート2の@の式は、"N"をプラスして解決しました。よろしければ意味的なことも教えて下さい。 SUMPRODUCT関数のヘルプを読むと、 ''数値以外の配列要素は、0 であると見なされます。'' とあります。基本情報!$J$10:$J$300=1 の配列で返されるのは、 J10〜J300の各セルが、1 と同じ場合''TRUE'' 同じでない場合''FALSE'' という''論理値''です。この論理値を数値に直すのが、N関数の仕事です。ヘルプで両関数についてご確認してみてください。 Sheet2のCの式から推測すると、Sheet1のDの式は、 =IF(AND(J10=1,COUNT(E10)),DATEDIF(E10,$E$6,"y"),DATEDIF(E10,IF(L10="",TODAY(),L10),"y")) でしょうか? J10が1と出る場合は、検索日等の条件に合致してる場合のはずですから、この条件にしました。 J10が""の場合では、退社日に入力が有る場合は、退社日での年齢という感じになっています。ごめんなさい、時間切れです。また、来週に。。。 (sin) ---- お忙しい時期ですよね・・・本当に何度も何度もすみません。私も仕事の合間に書き込みしているので、質問者側なのに遅くて申し訳ないです。また、諸事情により自宅のパソコンが使えない状態なので、sinさんは気になさらないでください。これから確認をしてみます。それでは、また来週よろしくお願いします。（ゆきだるま） ---- 先週の続きですが・・・シート2のDの式は、Cの式に性別分けをする式をプラスしたものです。ただ、そこから平均を出すにはどうしたらよいか・・・というところでうまくいきません。検索日等の条件内で性別分けした人数までは正確に出せているようなのですが、平均を出す関数を組み込む部分で間違っているようです。お忙しいと思いますが、よろしくお願い致します。（ゆきだるま） ---- まず、【Sheet1のDの式】で、正しい結果は出ましたか? 以下、出ているを前提として・・・ >検索日等の条件内で性別分けした人数までは正確に出せているようなのですがそこに年齢の列を追加すれば、該当条件での年齢の合計が出ますよね? =SUMPRODUCT((基本情報!$V$10:$V$300=1)*(基本情報!$L$10:$L$300="")*基本情報!$F$10:$F$300) ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾こんな感じで。その合計を、今出せている人数で割れば、平均になりませんか? と、書いて見直していたら、Sheet1のJ列がちょっと・・・気になりました。（多分、私の思い込みで、計算結果とその後の処理がゴッチャになっていたのかも...）以下、確認です。同一のIDの方が複数存在する場合は、より下にある行を有効にするのでしたよね? だとしたら、I・J列の式を私が作業列として追加と書いていた式に変更していただいてもいいですか? 後の集計する式が J10=1 がキーになっていると思いますので。今のままでは、より上の行が 1 になりますので、結果が違ってくると思います。 (sin) ---- おはようございます。なんだかまた話がかみ合っていなかったようですね? Sheet1とSheet2を私が読み間違えていたような・・・＞Sheet2のCの式から推測すると、Sheet1のDの式は、＞=IF(AND(J10=1,COUNT(E10)),DATEDIF(E10,$E$6,"y"),DATEDIF(E10,IF(L10="",TODAY(),L10),"y")) ＞でしょうか? いえ、Sheet1のDの式は下記のとおりです。 D=IF(E10="","",IF(COUNT($E$5,$E$6)<2,DATEDIF(E10,IF(L10="",TODAY (),L10),"Y"),IF(E10>$E$6,0,IF(AND(L10<>"",L10<$E$5),0,DATEDIF(E10,IF (L10="",$E$6,MIN(L10,$E$6)),"Y"))))) ＞まず、【Sheet1のDの式】で、正しい結果は出ましたか? 正確に出ているようです。＞その合計を、今出せている人数で割れば、平均になりませんか? 正しい結果が出ているようですが、人数で割るというところでつまづいています。式の最後に"/(基本情報!$V$10:$V$300=1)*(基本情報!$L$10:$L$300="男")"ということですか? そもそもSheet2のCの式が合っているのか自信がないです。・・・ C=SUMIF(基本情報!$J$10:$J$300,1,基本情報!$F$10:$F$300)/COUNTIF(基本情報!$J$10:$J$300,1) ＞同一のIDの方が複数存在する場合は、より下にある行を有効にするのでしたよね? ＞だとしたら、I・J列の式を私が作業列として追加と書いていた式に変更していただいてもいいですか? 確かに同一のIDがある場合は、変更日が降順になているので、下にあるものが最新となります。でも、作業列の式で最新のものに1と表示してあるので、1をカウントする式で良いのではないでしょうか? だめですか? ＞作業列として追加と書いていた式に変更していただいてもいいですか? どちらの式のことかわかりませんでした。 =IF(OR(B2>$F$1,AND(C2<>"",C2<=$E$1)),0,ROW(A2))・・・? =IF(E2=0,"",IF(COUNTIF($A$2:$A$10,A2)=1,1,IF(E2=MAX(INDEX(($A$2:$A$10=A2)*$E$2:$E$10,0)),1,"")))・・・? 現在のSheet1のI列とJ列の式はこのようになっていますが、変更ですか? G=IF(C10="","",IF(AND(H10>=$E$5,H10<=$E$6),B10,"")) H=IF(OR(I10="",AND(L10<>"",L10<=$E$6)),"",COUNTIF(I10:$I$100,I10)) （ゆきだるま） ---- また勘違いの上塗りしてました。 U・V列は、なくて（削除して）大丈夫でした。なにやってんだか... Sheet1のDの式に関しては、 >正確に出ているようです。という事なので、よろしいのでしょう。 ※しかも、私のは理に適ってなかった。orz ←ここ、読み飛ばしてください。。。 Sheet2のCの式は、合ってると思います。この場合、後ろの COUNTIF(基本情報!$J$10:$J$300,1) の部分は、Sheet2の@で求められてますので、そのセル番地を指定されてはどうででょう。(D・Eも同様) 注）誕生日が未入力の場合、年齢の合計には加算されませんが、人数には加算されますよね。誕生日が未入力の場合、平均年齢の算出対象から除外する場合は、 =SUMPRODUCT((基本情報!$J$10:$J$300=1)*(基本情報!$E$10:$E$300<>"")*(基本情報!$F$10:$F$300))/SUMPRODUCT((基本情報!$J$10:$J$300=1)*(基本情報!$E$10:$E$300<>"")) とすれば、解決できます。（この考え方は、↓も同じです） Sheet2のAの式は、ゆきだるまさんが書かれてる式でよいと思います。ここに年齢の範囲を加算すると年齢の合計になります。これを上に書いた人数で割れば平均になります。 Dの式の年齢合計部分は =SUMPRODUCT((基本情報!$J10:$J$300=1)*(基本情報!$D$10:$D$300="男")*(基本情報!$F$10:$F$300)) で求められます。 (sin) また変な勘違いをしてましたので、大幅に修正しました。 ---- すみません。いつも余計なことを書いているせいか、紛らわしくしてしまったようですね・・・それでSheet2の平均年齢ですがDの式の年齢合計部分をセル番地で割ることにしました。が・・・エラーが出てしまうのは、やはり私の式がおかしいのでしょうか? Sheet2のDの式↓ =SUMPRODUCT((基本情報!$J10:$J$300=1)*(基本情報!$D$10:$D$300="男")*(基本情報!$F$10:$F$300))/統計!$C$11 それとも、やはりSheet1のDに問題ありでしょうか? （ゆきだるま） ---- >エラーが出てしまうのは・・・エラーって #DIV/0! ですか? 統計!$C$11に入ってる式は、 =SUM(基本情報!$V$10:$V$300) それとも =SUMPRODUCT((基本情報!$V$10:$V$300=1)*(基本情報!$L$10:$L$300="")) ^^^^^^^^^^^^^^^^^^^^^^^^この部分の意図がわかりませんが? ですか? V列を削除しているなら、J列にして下さい。両方あるなら、どちらかに統一して使うほうが良いと思います。メンテナンスの際にも、楽です。あと、検索日に入力がない場合にも統計表では答えを出すのなら、その部分が必要です。 =IF(COUNT($E$5,$E$6)<2,COUNTIF(基本情報!$D$10:$D$300,"男"),-----------) という感じです。今さらですが、統計表算出の基準は、何に（どこに）置いてますか? 私が思うには、現状ではJ列だと思います。検索日が入ろうが退社日が何時だろうが、 J列が1となるものに対して統計表の計算がされるようにすれば、全体が分かりやすくなると考えます。これは、人それぞれの考え方によりますので、強要は出来ませんが。。。ここまで、こんがらがると一から出直したほうが早い気がしたもので、書いてみました。ほんと今さらで申し訳ありませんが。。。 ※明日になれば、まとまった時間を取れると思いますので、全体を再確認してみます。 (sin) ---- ＞エラーって #DIV/0! ですか? エラーは、#VALUEです。＞統計!$C$11に入ってる式は =SUMPRODUCT((基本情報!$J$10:$J$300=1)*(基本情報!$D$10:$D$300="男")) V列は、削除してあります。＞あと、検索日に入力がない場合にも統計表では答えを出すのなら、その部分が必要です検索日がない場合は、基本的に統計はしなくて良いのですが、もし統計を出したいとすれば "その部分"とは、(基本情報!$L$10:$L$300=""))ですよね? ＞今さらですが、統計表算出の基準は、何に（どこに）置いてますか? sinさんのおっしゃるとおりJ列です。検索日を入力した場合には、検索日が条件として他の何よりも最優先できればよいです。現在の式でも、そのようになっているように思うのですが、全体的な変更が可能でしょうか? 私も、もう一度確認をしてみます。ほんとにすみません。（ゆきだるま） ---- 全体を見直してみました。【基本情報シート】 I10セル =IF(COUNT($E$5:$E$6)<2,B10,IF(AND(H10>=$E$5,H10<=$E$6,OR(L10="",L10>$E$6)),B10,"")) 検索日が未入力の場合、B10 〃に入力の場合、H列の日付が検索期間内で退社日が検索終了日の後の場合、B10 上記以外は、"" これで、該当するもの全てにIDが入るはずです。 J10セル =IF(I10="","",COUNTIF(I10:$I$100,I10)) 同一IDにおいて、より下の行が1となり、これで基準（計算対象:1）を設定できます。ちょっと、仕事が出来たので、まず1弾目ということで、ここまで。追って続けます。。。 (sin) ---- 続きです。以降は、全てJ10が1のものを計算対象としています。【基本情報シート】 F10 =IF(OR(J10<>1,E10=""),"",DATEDIF(E10,MIN($E$6,L10,TODAY()),"y")) M10 =IF(OR(J10<>1,K10=""),"",DATEDIF(K10,MIN($E$6,L10,TODAY()),"m")) 【統計シート】 C10 =COUNTIF(基本情報!$J$10:$J$100,1) C11 =SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100="男")) E10 =SUMIF(基本情報!$J$10:$J$100,1,基本情報!$F$10:$F$100)/C10 または =AVERAGE(基本情報!$F$10:$F$100) E11 ※基本情報シートのJ列で1が入って、年齢が空白もありそうなので年齢欄が""でないものとしました。また、*→, により、#VALUE!もでなくなるはずです。これは、F列に空欄（文字数0の文字列 "" →数字と見なされない文字)が邪魔をしてました。 =SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100="男")*(基本情報!$F$10:$F$100<>""),基本情報!$F$10:$F$100)/SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100="男")*(基本情報!$F$10:$F$100<>"")) H11 =SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100=H$10)*(基本情報!$F$10:$F$100=$G11)) C17 =SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100=C$16)*(基本情報!$F$10:$F$100>=51)) 考え方が、違っている事もありますので、式の意味も踏まえご確認下さい。 (sin) ---- お忙しい中で見直してくださいましてありがとうございます。私事で申し訳ないのですが、まだ内容を確認できておりません。この後、時間が取れ次第確認をしまして、明日またご報告を致します。取り急ぎ、お礼申し上げます。（ゆきだるま） ---- 確認が遅くなりまして申し訳ありませんでした。年明けまでにと思っていたのですが、仕事が片付かずそのままになってしまいました。式の意味も踏まえ全体を確認してみましたが、考え方も理想通りでした。おかげさまで、当初の目的に副ったものができたと思います。最後に念のため少し確認があります。『統計シート』 H11 =SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100=H$10)*(基本情報!$F$10:$F$100=$G11)) 上記の式の最後は、 (基本情報!$G$10:$G$100=$G11)) とさせていただきました。また、式の中で"男"と表記する場合と"H$10"と表記する場合がありましたが、どちらでも構わないということでしょうか? （ゆきだるま） ---- >(基本情報!$G$10:$G$100=$G11)) とさせていただきました。達成度の列ですから、G列でしたね。失礼しました。 >式の中で"男"と表記する場合と"H$10"と表記する場合がありましたが、どちらでも構わないということでしょうか? はい、どちらでも構いません。(H10セルが『男』となっている事が前提ですが） (sin) ちょっと追記。 ---- 返信ありがとうございます。すみませんが、また追加で質問があります。仕事のグループごとに数枚シートがあり、最後のシートを入力中にで気づいたのですが・・・ Sheet2の統計で、男女別の平均年齢を出すところがあります。 1人も該当者がいなかった場合、#DIV/0!というエラーが出てしまいます。グループによっては、男性しかいないところがあったので、できたらエラーを出したくないのですが。お時間のある時で構いませんので、よろしければ教えて下さい。（ゆきだるま） ---- EXCELヘルプで、『エラー値 #DIV/0! を修正する』で検索すると、解決できます。では、ちょっと寂しすぎますが、今後の為にもヘルプを有効に使いましょう。以下、上記の検索によるヘルプの抜粋です。（もっと簡単に検索できるといいのにとは思いますが・・・）【エラー値 #DIV/0! を修正する】数式でゼロ (0) による除算が行われた場合に表示されます。・エラーが表示されるセルをクリックし、「''!''」ボタンをクリックします。表示される場合は、[エラーのトレース] をクリックします。・数式を確認して、エラーの原因と対処方法を調べます。 [原因と対処方法] エラー値が表示されないようにするには、IF ワークシート関数を使用します。たとえば、エラーになる数式が =A5/B5 の場合、「=IF(B5=0,"",A5/B5)」と入力します。この数式により、セル B5 が空白または 0 の場合は空の文字列 ("") が返されます。 >1人も該当者がいなかった場合、#DIV/0!というエラーが出てしまいます。今回の場合も、上の式を真似て、 =SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100="男")*(基本情報!$F$10:$F$100<>""),基本情報!$F$10:$F$100)/SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100="男")*(基本情報!$F$10:$F$100<>"")) この式を ''=IF(SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100="男")*(基本情報!$F$10:$F$100<>""))=0,"",''SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100="男")*(基本情報!$F$10:$F$100<>""),基本情報!$F$10:$F$100)/SUMPRODUCT((基本情報!$J$10:$J$100=1)*(基本情報!$D$10:$D$100="男")*(基本情報!$F$10:$F$100<>""))'')'' のようにすれば、該当人数が0の場合は、""が返ります。 "" を "該当者はいません" としたり、 0 としても良いでしょう。お好みでどうぞ。。。 (sin) ---- 検索して調べるということをすっかり忘れていました。応用すれば使える式がたくさんあることも・・・ sinさんに頼りっぱなしですみません。それで、とても申し上げにくいのですが、また不都合な箇所が出てしまいました。私の確認ミスで今さらなのですが・・・ Sheet1Gの式→I列に入力 =IF(COUNT($E$5:$E$6)<2,B10,IF(AND(H10>=$E$5,H10<=$E$6,OR(L10="",L10>$E$6)),B10,"")) ＞検索日が未入力の場合、B10 ＞〃に入力の場合、H列の日付が検索期間内で退社日が検索終了日の後の場合、B10 ＞上記以外は、"" ＞これで、該当するもの全てにIDが入るはずです。私もこれで大丈夫だと思っていたのですが、表の規則上で問題が出てしまっています。 Sheet1の構成 B C D E F G H I J K L M ID 氏名性別誕生日年齢達成度日付抽出a@検索値入社日退社日在職満月数 @ A B C D E F G H I J K 1 りんご女 3月 1月 1月 2 いちご男 11月 4月 4月 2 いちご男 11月 10月 4月 10月このようなかたちで入力していくので、例えば検索期間が2月〜12月となると、りんごさんは1月入社ですし、いちごさんは10月に退職しているということになります。しかし、現状の式ですと、2列目のいちごさんは検索期間にヒットし抽出されてしまっています。式を変更せず、2列目の退職日欄にも退職日を入力するという方法があるかと思いますが、情報の変更が多い社員の場合は、入力作業でのミスが発生してしまうのではと懸念しています。というのも、情報の更新ごとに並び替えをしているのですが、3つの条件でならびかえただけでは、同じ人物がうまく順番に並ばない時があります。今の並び替え優先順位は入社日・変更日・IDの順番で、同じ入社日の人がいた場合に不都合が出ます。そうするとIDがバラバラになって並び、データが増えるほど探しにくくなってしまっています。確認なのですが、I列の式は、変更日付の並び順は同一人物の中で順番通りであれば構わないのですよね? （ゆきだるま） ---- 先ほどの追記です。そもそも全てに退職日を入力すること自体、統計を不正確にしてしまいますね・・・すみません、別の方法を考えて見ます。（ゆきだるま） ---- 検索期間内に退職した人（ID）は、検索対象から、全ての該当データ（行）を除外する。という事ならば、 =IF(COUNT($E$5:$E$6)<2,B10,IF(AND(H10>=$E$5,H10<=$E$6,OR(L10="",L10>$E$6)), IF(SUMPRODUCT(($B$10:$B$100=B10)*($L$10:$L$100>=$E$5)*($L$10:$L$100<=$E$6))=0,B10,""),"")) 継ぎ足しの式ですが、これでどうでしょう? ※改行してます。検索対象とする条件を明確に（全ての想定条件をクリア)しないと、また不具合が出ると思います。 (sin) ---- ちょっと時間がかかり過ぎてしまったのですが、再度全てのシートを確認してみました。結果、式の問題ではないのですが、不備がでてしまったので、またご教示願います。まずSheet1の勤続月数についての確認です。今まで表自体を部署ごとに作成していたので、部署異動した人に関しては、入社してからの勤続月数が出ていませんでした。それで再度、部署ごとに計算したものと、会社に入社してからの全体のものも計算してほしいと頼まれました。たぶん、ボーナスの関係とかがあるんだと思います。そこで今まで作っていた表にプラスしてV列・W列・X列・Y列に項目を追加しました。式に必要なデータが入力されているのは、V列（会社への入社日）とX列（勤続月数表示）のところだけです。 sinさんが教えてくださったM列に入力したKの式のように、退職した人以外の勤続月数を出したいです。この場合は、K=IF(OR(J10＜＞1,K10=""),"",DATEDIF(K10,MIN($E$6,L10,TODAY()),"m"))を部分的に変えて、K列をV列にすればだいじょうぶですよね? あともうひとつ質問です。 Sheet2の統計表で達成度別の人数を出しましたが、"○○以外"という式を作って自動的にその他に計算されるようにしたいです。つまり、"5" "4" "達成"などの数字や文字は入力が多く予想されるのであらかじめ項目を作っておいて、それ以外が入力された場合は、その他の項目にまとめるような式にしたいですが、可能でしょうか? それとも、その他にまとめるにしても予想される限りの全ての数字や文字を式に組み込まなければムリですか? ご回答よろしくお願い致します。（ゆきだるま） ---- ゆきだるまさん、こんにちわ。 >式の問題ではないのですが、ということは、以前の疑問点等は、解決された! ということですね。・>部分的に変えて、K列をV列にすればだいじょうぶですよね? よろしいのではないでしょうか? J10への条件に退職日が関連していたような気がしますので、 L10はなくてもよいような気もしますが・・・。そのままでも計算できるでしょうから大丈夫だと思います。・>自動的にその他に計算されるようにしたいです。 EXCEL君が判断できるようにするための一例です。基本情報シートに、入力セル（5、4、達成、その他の任意の文字）の隣に分類セル（列を挿入）を設け、 =IF(ISNA(MATCH(G10,統計!______,0)),"その他",INDEX(統計!______,MATCH(G10,統計!______,0))) とすれば、統計シートの範囲（上の式の____部分にその範囲を入れる）にある【5、4、・・・、達成、その他】のどれかに分類できます。（統計シートにないものは全て『その他』になります。) この列を集計用に使ってはどうでしょうか? (sin) ---- 早速の返信ありがとうございました。＞ということは、以前の疑問点等は、解決された! ということですね。以前の質問の件に関しては、例外を除いて解決しました。ただひとつ例外があって、つい最近の話なのですが、以前退職した人が戻ってきました。以前に在職していた時と同じIDを使おうといろいろ考えたのですが、データの統計に誤りが出てしまいました。なので、いちおう保留なのですが、原則として再入社時は新しいIDを使うということにしてみました。以前sinさんがご指摘のとおり、また不具合が出たかたちになりますが、再入社する社員が過去をみてもあまりいなかったので今のところ新たな式は考えていません。＞ =IF(ISNA(MATCH(G10,統計!______,0)),"その他",INDEX(統計!______,MATCH(G10,統計!______,0))) ＞（上の式の____部分にその範囲を入れる）こちらの式を早速ためしてみました。その範囲を入れるというところを理解していないのか、ほとんど"その他"になります。 "達成"のみ間違いなく表示されていますが、ちょっとまだ原因がわかっていません。ちなみに下記のような表の場合・・・ H I J 達成度別男女全体 5 J L 4 K M 3 2 1 H列が範囲ということで大丈夫ですか? H11:H15というようなかんじで今はなっているのですが・・・（ゆきだるま） ---- 出戻りさんは、完全な想定外ですw 多分、数値/文字列の違いもしくは余白などが原因だと思いますが、集計が出来ていたのだとすると $H$11:$H$15 と絶対参照にしていないのかも? どちらにしても、『達成』が含まれていないので、どこか都合のよい空いているセル範囲に5、4、・・・のListを作って、そこを参照させてみてはいかがでしょう。下は、Listシートを設けてA1:A7に 5,4,3,2,1,達成,その他と入力した例です。 =IF(COUNTIF(List!$A$1:$A$7,G10),VLOOKUP(G10,List!$A$1:$A$7,1,0),"その他") ちょっと、式を変えてみました。結果は同じです。 (sin) ---- いつも返信が遅くなって申し訳ないです。原因がわからないのですが、フリーズしたりで確認に時間がかかってしまいました。でもバックアップもあったのでセーフです! それで先日の質問ですが、絶対参照にしていなかったのが原因みたいです。 2つめの方法も参考に試してみましたが、どのシートも絶対参照に直すだけで正確に表示されていたので、1つめの方法で式を直させていただきました。これで全ての問題を解決することができました。また私のことですので、見落としていそうな・・・新たな不具合が出てきそうな・・・そんなかんじもしますが、ひとまず当初考えていたものにすることができました。わかりにくい説明に、sinさんが根気よくご回答くださったおかげです。本当に長い時間お付き合いいただいてありがとうございます。たぶん、、きっとまた質問をさせていただくことになると思いますが、その際には、またご教示下さい。ありがとうございました。（ゆきだるま） ---- >原因がわからないのですが、フリーズしたりで確認に時間がかかってしまいました。継ぎ足し継ぎ足しになっていると思いますので、重複する点や不要な点が多々あるかと思います。落ち着いた時にでも、全体を総点検して改良する事をお勧めします。 (sin) 私だったらACCESSにしたかも?（←余計な独り言です。） ...; https://www.excel.studio-kazu.jp/wiki/kazuwiki/200711/20071122151028.txt - [detail] - similar

PREV NEXT

訪問者：カウンタ