- ベストアンサー
Excelで土日・元日を除いた経過日数の計算方法
- Excel2010で、土日・元日を除いた経過日数の計算方法を教えてください。
- 損益計算シートには、各年の元日の表示があり、計算式も入力されています。
- 統計シートの特定のセルに土日・元日を除いた経過日数を表示させる方法を教えてください。
- miya_HN
- お礼率100% (1683/1683)
- Excel（エクセル）
- 回答数8
- ありがとう数8
- みんなの回答 （8）
- 専門家の回答
質問者が選んだベストアンサー
取り敢えず、御質問文に記されている質問者様の関数に「損益計算」シートのY列のデータが使われていない事に関しては無視して >「損益計算」シートの「開始日」であるX行（X3）から、 >「損益計算」シートの「終了日」であるY行の表示してある最終行の日付までの土日・元日を除いた日数です。 という話に従った場合、一例としましては次の様な関数となります。 =IF(損益計算!$A3="","",IF(ISERROR(1/DAY(損益計算!X3)/DAY(VLOOKUP(9E+307,損益計算!$Y:$Y,1)/(損益計算!X3<=VLOOKUP(9E+307,損益計算!$Y:$Y,1)))),"",NETWORKDAYS(損益計算!$X3,VLOOKUP(9E+307,損益計算!$Y:$Y,1))-SUMPRODUCT((WEEKDAY(DATE(ROW(INDIRECT("AZ"&YEAR(損益計算!X3-1)+1&":AZ"&YEAR(VLOOKUP(9E+307,損益計算!$Y:$Y,1)))),1,1),2)<6)*1))) 尚、上記の関数の中のINDIRECT関数においてAZ列が使われている事には特に意味は無く、「開始日の次の年の西暦年」～「最終日の西暦年」に相当する数値をSUMPRODUCT関数内に発生させるために、適当な列をROW関数と組み合わせて使用しているだけですので、別にAZ列である必要は無く、A列～XFD列のどの列を使っても構いません。
その他の回答 (7)
- MackyNo1
- ベストアンサー率53% (1521/2850)
＞大元のデータである「元データ」シートの入力件数によって「損益計算」シートのZ列の数値が入力されている範囲が変わってきてしまうからです。 私の提示した数式は、数式で「””」以外の数値を返している部分だけを範囲にするものですのでご希望の可変範囲を取得しているはずですが、「損益計算!Z:Z」の部分をいずれかの数式に置き換えてもエラーが出たのでしょうか？
お礼
回答ありがとうございます。 回答を頂いて申し訳ないのですが、今回の質問は解決できました。 気を悪くなされたなら大変申し訳ありません。 また、何らかの質問で気が付いたらでいいのでそのときはよろしくお願いします。 ありがとうございました。
- mt2008
- ベストアンサー率52% (885/1701)
ANo.2です。 > ちなみに、下記のような結果となりました。 > > Z3：2011年1月1日 > Z4：2012年1月1日 > Z5： > Z6：1900年1月0日 Z6の1900年1月0日、これが0を日付書式で表した状態です。 これを条件付き書式で見えなくしてしまおうというのが私の回答でした。 ただ、Z5が空白になっているので、式の修正が中途半端でまだ""が残っている様ですね。 条件付き初期を使いたくないのであれば、前の回答は忘れて作業列を使いましょう。 AA3に=IFERROR(Z3*1,0) 以下のセルも同様に入れておいて =IF(損益計算!A3="","",NETWORKDAYS(損益計算!X3,INDEX(損益計算!X:X,COUNT(損益計算!E:E)+2,1),損益計算!AA:AA)) これで如何でしょう
お礼
回答ありがとうございます。 「""」がどこかで残ってたんですね。 >条件付き初期を使いたくないのであれば、前の回答は忘れて作業列を使いましょう。 AA3に=IFERROR(Z3*1,0) 以下のセルも同様に入れておいて >=IF(損益計算!A3="","",NETWORKDAYS(損益計算!X3,INDEX(損益計算!X:X,COUNT(損益計算!E:E)+2,1),損益計算!AA:AA)) ばっちりです！！ 正しい経過日数の数値が表示されました。 ありがとうございました。
補足
今回、ベストアンサーを回答No.7の方とどちらの方にしようか迷いました。 回答No.7の方の計算式は自分には理解できずそのまま完コピで、回答No.6の方の計算式はシンプルで分かりやすかったです。 しかしながら、今回は回答No.7の方をベストアンサーとしたいと思います。 大変申し訳ありませんが、また機会があればそのときはよろしくお願いします。 ありがとうございました。
- kagakusuki
- ベストアンサー率51% (2610/5101)
>非常に惜しいのですが数値が異なっていました。 申し訳御座いません。元日が土日の場合がある事を失念しておりました。 それで回答No.3に対して頂いた質問者様のお礼に書かれている内容のため、返って状況が良く解らなくなってまいりました。 >「損益計算」シートの「終了日」であるY行の最後は2012年10月8日です。 という事がこの御質問の件に何か関わって来るのでしょうか? 御質問にある =IF(損益計算!A3="","",NETWORKDAYS(損益計算!X3,INDEX(損益計算!X:X,COUNT(損益計算!E:E)+2,1),損益計算!Z:Z)) という関数には、Y列(「Y行」等という行はExcelには存在しません)のセルなど全く使われていないというのに、何故Y列の話をしておられるのでしょうか? X列において日時が入力されているセルの中で、最も下にあるセルの日時の値を自動的に取り出し、その日時を「『土日・元日を除いた日数』を求める対象となる期間」の最終日として用いるのでは駄目なのでしょうか? >「損益計算」シートの「終了日」であるY行の表示してある最終行というのは特定できません。 入力されている値が数値や日時のデータである場合に限り、最終行や最終行のセルに入力されている値を、関数を使って自動的に取得する方法があるのです。 ですが、御質問に関する質問者様の御説明では、「土日と元旦を除いた日数」を求める期間の最終日がどのシートのどの列の最終行の値を使えば良いのかという事が、はっきりとしていないために、どのデータを使えば良いのかという事が解りません。 ですから、期間の初日と最終日が、それぞれどのような条件で決まるのかという事を御説明願います。(X列の中で最も下に入力されている値なのか? Y列の中で最も下に入力されている値なのか? E列に数値が入力されている最も下の行と同じ行のX列の値なのか?)
お礼
回答ありがとうございます。 大変申し訳ありません。 最も重要なことを記載するのを忘れていました。 「損益計算」シートの「開始日」であるX3（開始日であるX3は固定）に入力されている、年月日から「損益計算」シートの「終了日」であるY行に表示されている最終行の年月日までの土日・元日を除いた経過日数を求める計算式でした。 「損益計算」シートの「終了日」のY行についての記載を忘れていました。 「2012年10月8日」というのは現在表示されている「損益計算」シートの「終了日」のY行の最終行のことです。 もうすでに回答を頂けたんですね。 ありがとうございます。
- MackyNo1
- ベストアンサー率53% (1521/2850)
>原因は、NETWORKDAYS関数の元日である部分の「損益計算!Z:Z」であることは分かっています。 このZ列の範囲に空白文字列が混ざっているということでしょうか？ その場合は、「損益計算!Z:Z」の部分をZ列の数値が入力されている範囲だけにする必要があります。 ＝損益計算!Z3:INDEX(損益計算!Z:Z,COUNT(損益計算!Z:Z)+2) または ＝OFFSET(損益計算!Z3,0,0,COUNT(損益計算!Z:Z),1)
お礼
回答ありがとうございます。 >このZ列の範囲に空白文字列が混ざっているということでしょうか？ はい、そうです。 大元のデータである「元データ」シートの入力件数によって「損益計算」シートの計算式で余計な表示はしないように「""」で空白にしています。 個人的に余計なものを表示するのは好きではないんです。 >その場合は、「損益計算!Z:Z」の部分をZ列の数値が入力されている範囲だけにする必要があります。 そういうわけにはいかないんです。 大元のデータである「元データ」シートの入力件数によって「損益計算」シートのZ列の数値が入力されている範囲が変わってきてしまうからです。 「元データ」シートの入力件数はそのときによって必ず変わってしまうんです。 したがって、「元データ」シートの入力件数の変更によって対応できる土日・元日を除いた経過日数を「統計」シートのD25に表示しなければいけません。 回答No.3の方にもお願いしたのですが、「統計」シートのD25に表示したい数値は、 「損益計算」シートの「開始日」であるX行（X3）から、 「損益計算」シートの「終了日」であるY行の表示してある最終行の日付までの土日・元日を除いた日数です。 「損益計算」シートの「終了日」であるY行の表示してある最終行というのは特定できません。 大元のデータである「元データ」シートに入力する件数によって変わってきてしまうからです。 「元データ」シートに入力されていない行は、「損益計算」シートの計算式の「""」で全て空白にしています。 大変申し訳ありませんが以上のことを踏まえ、もう一度回答を頂けないでしょうか？ よろしくお願いします。
- kagakusuki
- ベストアンサー率51% (2610/5101)
期間内に元日が含まれているのは、年を跨いでいる場合と、期間の「開始日」が元日である場合だけなのですから、期間内に含まれている元日の日数(個数)は損益計算!Z:Zのリストを使わずとも、「『終了日の前日』の西暦年」から「『開始日の前日』の西暦年」を差し引く事で求める事が出来ます。 それと「損益計算のE列に数値が入力されている最終行」と同じ行の損益計算のX列の値であれば、 VLOOKUP(9E+307,損益計算!$E:$X,20) という関数で求める事が出来ます。 尚、損益計算のX列の中で最後に入力されている数値か日時であれば、 VLOOKUP(9E+307,損益計算!$X:$X,1) という関数で求める事が出来ます。 ですから、損益計算!Z:Zのリストを使わずとも、次の様な関数にされると良いと思います。 =IF(損益計算!$A3="","",IF(ISERROR(1/DAY($X3)/DAY(VLOOKUP(9E+307,損益計算!$E:$X,20))),"",NETWORKDAYS(損益計算!$X3,VLOOKUP(9E+307,損益計算!$E:$X,20))-YEAR(VLOOKUP(9E+307,損益計算!$E:$X,20))+YEAR(損益計算!$X3-1))) 又、「損益計算のE列に数値が入力されている最終行」と「損益計算のX列に数値(或いは日時)が入力されている最終行」が必ず同じ行となっているのであれば、次の様な関数にされると良いと思います。 =IF(損益計算!$A3="","",IF(ISERROR(1/DAY($X3)/DAY(VLOOKUP(9E+307,損益計算!$X:$X,1))),"",NETWORKDAYS(損益計算!$X3,VLOOKUP(9E+307,損益計算!$X:$X,1))-YEAR(VLOOKUP(9E+307,損益計算!$X:$X,1))+YEAR(損益計算!$X3-1)))
お礼
回答ありがとうございます。 自分にはこの計算式の意味が分からなかったため、そのままコピーし表示結果を見てみました。 下記のように入力しました。 =IF(損益計算!$A3="","",IF(ISERROR(1/DAY(損益計算!$X3)/DAY(VLOOKUP(9E+307,損益計算!$E:$X,20))),"",NETWORKDAYS(損益計算!$X3,VLOOKUP(9E+307,損益計算!$E:$X,20))-YEAR(VLOOKUP(9E+307,損益計算!$E:$X,20))+YEAR(損益計算!$X3-1))) すると、非常に惜しいのですが数値が異なっていました。 確認のためカレンダーを見て計算したのですが正しい数値は461です。 この計算式の表示結果は460でした。 「損益計算」シートの「開始日」であるX行の最初は2011年1月3日です。 「損益計算」シートの「終了日」であるY行の最後は2012年10月8日です。 記載し忘れていたかもしれませんので記載しておきます。 「統計」シートのD25に表示したい数値は、 「損益計算」シートの「開始日」であるX行（X3）から、 「損益計算」シートの「終了日」であるY行の表示してある最終行の日付までの土日・元日を除いた日数です。 「損益計算」シートの「終了日」であるY行の表示してある最終行というのは特定できません。 大元のデータである「元データ」シートに入力する件数によって変わってきてしまうからです。 「元データ」シートに入力されていない行は、「損益計算」シートの計算式の「""」で全て空白にしています。 多分もう少し改良してもらえると正しい数値が表示されると思います。 こちらも重要なことを記載していなかった可能性があるため、自分に非があるのですが大変申し訳ありませんが、もう一度回答を頂けないでしょうか？ 貴重な時間を私に割いていただけないでしょうか？ よろしくお願いします。
- mt2008
- ベストアンサー率52% (885/1701)
説明が長くてくじけそうになりましたが。 結局の所、NERWORKDAYS関数の祭日引数に指定されている範囲（損益計算!Z:Z）に文字列が含まれているのが問題です。 セルZ3やZ4以下の式で空白("")にせずに0にして条件付き書式で0の時のフォント色を白にして、0の時は見えない様にしましょう。 例： =IF(元データ!A3="","",DATE(E3,1,1)) ↓ =IF(元データ!A3="",0,DATE(E3,1,1)) そしてD25の式も「元日」と言う項目名を含めない様に祭日の引数を指定しましょう。 例 =IF(損益計算!A3="","",NETWORKDAYS(損益計算!X3,INDEX(損益計算!X:X,COUNT(損益計算!E:E)+2,1),損益計算!Z:Z)) ↓ =IF(損益計算!A3="","",NETWORKDAYS(損益計算!X3,INDEX(損益計算!X:X,COUNT(損益計算!E:E)+2,1),損益計算!Z3:Z10))
お礼
回答ありがとうございます。 「損益計算」シートのX、Y、Z行の計算式の「""」の部分を全て「0」に変更し、Z行の「損益計算!Z:Z」の部分を「損益計算!Z3:Z10002」に変更したのですが、Z行の表示結果がおかしな表示となってしまいました。 ちなみに、下記のような結果となりました。 Z3：2011年1月1日 Z4：2012年1月1日 Z5： Z6：1900年1月0日 Z7： Z8：1900年1月0日 Z9： Z10：1900年1月0日 >セルZ3やZ4以下の式で空白("")にせずに0にして条件付き書式で0の時のフォント色を白にして、0の時は見えない様にしましょう。 この部分は変わったアイデアだなと思いました。 ただ、個人的には純粋に計算式のみで表示したいと思いました。 ちょっと好みの方法ではないです。 回答して頂いたにも関わらず大変申し訳ありません。 ありがとうございました。
- msMike
- ベストアンサー率20% (364/1804)
》 Z3から下に各年の元日の表示をしています。 》 一応、Z10002まで計算式を入れています。 参考までに「Z10002」の計算式を教えてください。それは西暦何年の元旦ですか？ とても興味があるし、これから考える回答の参考にしたいので。
お礼
回答ありがとうございます。 「Z10002」の計算式は、 =IF(Z10001="","",IF(INDEX(E:E,COUNT(E:E)+2,1)-$A$3>=ROW()-3,DATE($A$3+ROW()-3,1,1),"")) です。 「Z10002」は表示結果としては、空白です。 仮に、表示されたとしたら西暦10000年以上になるでしょう＾＾； 一応、他の項目・行に合わせたいので全て10002行目まで計算式を入れています。 個人的に揃えないと気持ちが悪いので。本来なら必要ありませんが。 Excelの仕様や自分の寿命を考えるとあり得ませんがｗ ちなみに、Z3には基本的には2001年の元日が入ります。それより以前は恐らくないと思われます。 引き続き、回答よろしくお願いします。
お礼
度々申し訳ありません。 すでに計算式を考えてくださったんですね。 回答No.5の疑問に対する回答は回答No.5のお礼の通りです。 今回の計算式を入力した結果、正しい値が表示されました。 >尚、上記の関数の中のINDIRECT関数においてAZ列が使われている事には特に意味は無く、「開始日の次の年の西暦年」～「最終日の西暦年」に相当する数値をSUMPRODUCT関数内に発生させるために、適当な列をROW関数と組み合わせて使用しているだけですので、別にAZ列である必要は無く、A列～XFD列のどの列を使っても構いません。 分かりました。 ありがとうございます。 現在まで付き合っていただき大変感謝しております。 ありがとうございました。