エクセル日にち間隔で日曜日を除いた値
- エクセル日にち間隔で日曜日を除いた値を求めたいです。
- B列に不特定日を入力し、日曜日だけを除いた日にち間隔の数値をD列に出したいです。
- でも、B列に入力した日付が日曜日の場合は、その日も含みます。
- ベストアンサー
エクセル日にち間隔で日曜日を除いた値
B列3行目以降 C列 D列 E列 (日付) (日にち間隔数) (日曜日除く間隔数) (データ値) H23年6月22日(水) H23年6月30日(木) 8 7 H23年7月1日(金) 1 1 H23年8月1日(月) 31 26 H23年8月28日(日) 27 24(28日の日曜日は含む) H23年9月30日(金) 33 28 日付入力は2011/6/21 B列表示形式はge"年"m"月"d"日"(aaa) 日付間隔は=IF(OR(ISBLANK(B3),ISBLANK(B4)),"",DATEDIF(B3,B4,"d")) の式を ネットで調べ、B列に不特定日を入力し、各、日にち間隔の数値を出しています。 ・行いたいのは データの記録表を作成していますが、B列に不特定日を入力し、日曜日だけを除いた 日にち間隔の数値をD列に出したいです。 但し、B列に入力した日付が日曜日の場合は、その日も含みます。 エクセル素人につき、 <WEEKDAY関数は、曜日に対応した数値を返します。数値と曜日の対応> などをどのように利用して良いか分かりません。 エクセルは2000です。 アドバスの程よろしくお願いします。 過去(質問番号:6992865)で同じような質問させていただきましたが、質問内容が 悪く、締め切らせていただいてます。 (ご回答頂いた方々ありがとうございました。)
- hirota3310
- お礼率45% (51/113)
- オフィス系ソフト
- 回答数13
- ありがとう数3
- みんなの回答 (13)
- 専門家の回答
質問者が選んだベストアンサー
回答番号ANo.5です。 >・・・・ご回答いただいた式をコピーして貼り付けし、下へオートフィルとは意味が違うでしょうか? 幾つかの異なるやり方がありますので、必ずしも同じ意味という訳では御座いませんが、そのやり方でもOKです。 それから、ANo.5の方法は、期間中に存在する日曜日以外の日を、1つずつカウントして行くというやり方ですが、日にち間隔数から、「期間中に存在する日曜日の日数」を差し引くというやり方では、D4セルに入力する関数は次の様なものとなります。 =IF(AND(COUNT($B3:$B4,1/0)=2,$B3<$B4),$C4-INT(($C4-(WEEKDAY($B4)=1))/7)-(WEEKDAY($B3+1,2)>WEEKDAY($B4,2)),"") 尚、Excelの関数は、普通では、セルの切取り&貼付けや、挿入、削除、等の編集作業を行ないますと、参照先のセルの位置関係がずれてしまい、正常に動作しなくなりますが、上記の関数を基にした、次の様な関数としますと、セルの切取り&貼付けや、挿入、削除、等の編集作業で、セルの位置が上下方向に移動しても、正しい結果を表示させる事が出来ます。(左右にずれる場合には対応していません) =IF(AND(COUNT(INDEX($B:$B,ROW()-1):INDEX($B:$B,ROW()),1/0)=2,INDEX($B:$B,ROW()-1)<INDEX($B:$B,ROW())),INDEX($C:$C,ROW())-INT((INDEX($C:$C,ROW())-(WEEKDAY(INDEX($B:$B,ROW()))=1))/7)-(WEEKDAY(INDEX($B:$B,ROW()-1)+1,2)>WEEKDAY(INDEX($B:$B,ROW()),2)),"") それから、動作チェックに関してですが、関数が入力されている行の1つ上の行に入力されている日の曜日が、土曜日、日曜日、月曜日である場合の3パターンと、関数が入力されている行に入力されている日の曜日が、土曜日、日曜日、月曜日である場合の3パターンが、それぞれ微妙な状況となりますから、 1つ上が土曜日で同じ行が土曜日というパターン、 1つ上が土曜日で同じ行が日曜日というパターン、 1つ上が土曜日で同じ行が月曜日というパターン、 1つ上が日曜日で同じ行が土曜日というパターン、 1つ上が日曜日で同じ行が日曜日というパターン、 1つ上が日曜日で同じ行が月曜日というパターン、 1つ上が月曜日で同じ行が土曜日というパターン、 1つ上が月曜日で同じ行が日曜日というパターン、 1つ上が月曜日で同じ行が月曜日というパターン、 の9パターンに関しては、しっかり確認しておいた方が宜しいかと思います。
その他の回答 (12)
- KURUMITO
- ベストアンサー率42% (1835/4283)
回答No8です。遅くなってごめんなさい。 次のURLを参考にしてください。 http://www.geocities.jp/chiquilin_site/data/071101_specific_day.html
お礼
お礼が大変遅くなりました。 ご回答頂いたURLを覗いてみます。 ありがとうございました。
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
ありゃりゃ 「3度目の正直ならぬ」、「2度あることは3度ある」でまた違いましたかorz ということで正解も出たようなので退散します~~~(ToT)/~~~
お礼
ご回答頂きありがとうございました。
[No.7補足]へのコメント、 まだ検証結果を示されていないけど、私の提示した式は正答を返すのですね? 「日付未入力時」に何も表示されないようにするには、 D5: =IF(B5="","",提示式) で如何でしょうか? (そのくらいはお出来になると思ってました) 「日付未入力時など」の「など」は他にどんなことを含みますか? 何れにしても情報の後出しは勘弁願いたい、です。
お礼
ご無理を言いました。 検証結果、正答です。 >「日付未入力時」に何も表示されないようにするには、 D5: =IF(B5="","",提示式) で如何でしょうか? ・・・・D5: =IF(B5="","-",提示式)にしました。 >「日付未入力時など」の「など」は他にどんなことを含みますか? ・・・・・必ずしも日付とは限らない(誤入力)意味です。余計なこと書きました。 ありがとうございました。
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
#2,#4です。両方とも違っていました 日付関係は苦手。。。 > データの記録表を作成していますが、B列に不特定日を入力し、日曜日だけを除いた > 日にち間隔の数値をD列に出したいです。 > 但し、B列に入力した日付が日曜日の場合は、その日も含みます。 出た数字が日曜日の箇所だけ、1日多くなりました。 なぜなら、「日曜日の場合は、その日も含みます」と書かれているから +(WEEKDAY(B4)=1) で当日(B4セル)が日曜日なら+1するってこと >H23年12月31日(土) >H24年1月1日(日) 2・・・・本来なら1 日曜日の場合って当日含め、2じゃないの? また、日曜日から始まるときの計算は? 正確なサンプルもほしい。 今度こそは!? =IF(C4="","",C4-INT((6-WEEKDAY(B4)+C4)/7)+(WEEKDAY(B4)=1))
補足
補足回答ありがとうございます。 解りづらい質問と条件不足でした。 >正確なサンプルもほしい。 ・・・・・以下をご覧ください。 B列3行目以降 C列 D列 E列 H23年9月10日(土) 基本 No.9さん 正解 H23年9月20日(火) 10 9 8 ・なぜか「9」になります。 H23年9月30日(金) 10 9 9 H23年10月13日(木) 13 11 11 H23年10月31日(月) 18 15 15 H23年11月19日(土) 19 17 17 H23年11月20日(日) 1 2 1 ・日にち間隔なので「1」が 正解になります。 H23年11月21日(月) 1 1 1 H23年12月11日(日) 20 18 18 H23年12月18日(日) 7 7 7 ・日曜日間隔は正解です。 H23年12月26日(月) 8 7 7 H23年12月31日(土) 5 5 5 H24年1月1日(日) 1 2 1 ・日にち間隔なので「1」が 正解になります。 H24年12月31日(月) 365 313 313 H25年1月1日(火) 1 1 1 H25年1月4日(金) 3 3 3 H25年12月31日(火) 361 309 309 H26年1月1日(水) 1 1 1 条件1.日にちの間隔数のうち日曜日を除く数を出したいです。 2.入力日付は不特定日です。 3.入力日付に日曜日の時もあります。 4.2.3.の条件により日付は土曜日、明くる日の日曜日とつづく場合があります。 5.2.3。の条件により日付は日曜日から次週の日曜日もあります。 6.入力日付記入時の最大間隔日は3年です。 >また、日曜日から始まるときの計算は? ・・・・条件3,4になります。 >なぜなら、「日曜日の場合は、その日も含みます」と書かれているから +(WEEKDAY(B4)=1) で当日(B4セル)が日曜日なら+1するってこと ・・・・条件4,5です。 ・日曜日を除く日にち間隔のなかで、たまたま入力日付が日曜日だった場合はその日も含みます。 ここがややこしくしていますね。 以上でお分かりいただけましたでしょうか? よろしければお願いします。
- KURUMITO
- ベストアンサー率42% (1835/4283)
回答No1です。前の式は無かったことにしてください。 C4セルには次の式を入力して下方にオートフィルドラッグします。 =IF(B4="","",B4-B3-INT((B4-(B3-WEEKDAY(B3,3))+1)/7)+IF(WEEKDAY(B3,3)=6,1,0)+IF(WEEKDAY(B4,3)=6,1,0))
補足
お礼が大変遅くなりました。 ご指摘の式を入れたらうまく出来ました。 感動ものです。 よろしければ、関数式の意味をアドバイス頂きたいのですが・・。 あるいは、解りやすいサイト等を紹介していただきますと幸いです。
[No.3補足]へのコメント、 》 カレンダーを見て数えてますので間違いかもです 「かも」なんて仰らずに、貴方自身で検証すべきです。 8/28~09/30間に除外すべき日曜が何回あるかを数えるだけで好い! それが、質問文を正しくするための、質問者のあるべき態度です。 「エラー表示」はそれからのこと!
補足
質問者側の怠慢でした。 大変失礼しました。
- MackyNo1
- ベストアンサー率53% (1521/2850)
日曜日のデータ処理が一部不明なところがありますが、休日を無視して日曜だけ除いた日付の差を計算するなら以下のような数式になります(最大50日後のデータまで計算できるようにしました)。 =SUMPRODUCT((WEEKDAY((B3+ROW($A$1:$A$50)-1))<>1)*(B3+ROW($A$1:$A$50)-1<B4))
- kagakusuki
- ベストアンサー率51% (2610/5101)
H23年8月28日(日)~H23年9月30日(金)の中で、日曜日ではない日数は29日ではないでしょうか。 それは兎も角、D4セルに次の関数を入力してから、D4セルをコピーして、D5以下に貼り付けると良いと思います。 =IF(COUNT($B3:$B4)=2,SUMPRODUCT((WEEKDAY($B3+ROW(INDIRECT("Z1:Z"&INT($B4)-INT($B3))))>1)*1)+(WEEKDAY($B4)=1),"") ※「ROW(INDIRECT("Z1:Z"&」の部分は、1から始まる「日にち間隔数」分の数値を自動的に作成するために、ROW関数を利用しただけのもので、Z列に特に意味がある訳ではなく、A列でもIV列でも、好きな列に変えて構いません。
補足
ご回答頂きましてありがとうございます。 >H23年8月28日(日)~H23年9月30日(金)の中で、日曜日ではない日数は29日ではないでしょうか。 ・・・・カレンダーをみて数えてますので間違いだと思います。すいません。 適当に日付、日にち間隔をとり 式を入力したら上手く日数が出ました。 >D4セルに次の関数を入力してから、D4セルをコピーして、D5以下に貼り付けると良いと思います。 ・・・・ご回答いただいた式をコピーして貼り付けし、下へオートフィルとは意味が違うでしょうか? セル内のエラーチェックオプションは「エラーを無視する」でよろしいでしょうか? よろしくお願いします。
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
#2です。#2の回答は、無視てください D4セルは =IF(C4="","",C4-INT((WEEKDAY(B4)+C4-1)/7)+(WEEKDAY(B4)=1)) 下へオートフィル
補足
ご回答頂きましてありがとうございます。 又、親切に画像までつけていただき。 画像箇所で気づきましたが、 適当に日付入力、日付間隔を取り、式を入力させてもらい、 出た数字が日曜日の箇所だけ、1日多くなりました。 式の入力箇所等が間違っていますでしょうか?
「H23年9月30日(金)」の行の「日曜日除く間隔数」が「29」でなく、「28」になっている理由は? もし「29」の間違いであるならば… =C3-INT((B3-1)/7)+INT((B2-1)/7)+(MOD(B3,7)=1)
補足
ご回答頂きましてありがとうございます。 また、親切に画像までつけていただき、解りやすいです。 >「H23年9月30日(金)」の行の「日曜日除く間隔数」が「29」でなく、「28」になっている理由は? もし「29」の間違いであるならば… ・・・・・カレンダーを見て数えてますので間違いかもです。すいません。 式を入力後、うまく出来たように思えます(実際の日数を未だ数えていないので) エラー表示(日付未入力時など)を回避出来ますか? 又は「0」を表示。 よろしければお願いします。
- 1
- 2
関連するQ&A
- エクセルで日付から特定曜日を除いた数(稼働日数等)
B列3行目以降 C列 日付 日付間隔 H23年6月21日(火) - H23年6月22日(水) 1 ・・・B4-B3 H23年8月2日(火) 41 ・・・B5-B4 H23年8月31日(水) 29 ・・・B6-B5 ・ ・ ・・・・今は単純に日付の間隔 ・ ・ ・ ・ 日付入力は2011/6/21 B列表示形式はge"年"m"月"d"日"(aaa) 日付間隔は=IF(OR(ISBLANK(B3),ISBLANK(B4)),"",DATEDIF(B3,B4,"d")) の式をネットで調べて いれています。 行いたいのは、B5---B4(8月2日から6月22日)間などの各上下間の日曜日などの特定曜日を除いた 数値(ここでは実稼働日数)を出したいです。 特定曜日が日曜日以外に土曜日と日曜日とか祝日とかもありますが、 ・・・・かなり複雑かなと思います(別シートにカレンダー表などが必要かと)。 とりあえずは毎週の日曜日です。 日付の入力日は不特定日です。行間に空白はありません。 エクセル素人につき<WEEKDAY(シリアル値,種類)>などをどのように利用して 良いか分かりません。エクセル2000です。 質問内容がお分かりにくいかと思いますが アドバスの程よろしくお願いします。
- 締切済み
- オフィス系ソフト
- Excelでカレンダーを作成。日にちを自動的に・・・
Excel2000でカレンダーを作成中なのですが質問です。 Sheet1に2006年1月、Sheet2に2月・・・とシートごとにひと月。 日にちはB列、曜日を右隣のC列に縦に入力。 E2に○月と月の数字を入力したたけで、B/C列の日付け・曜日を自動的に入力させる方法はありますか? 出来れば、土曜日の日にち・曜日を紺・日曜日はピンクと書式の色を変えたいので、その作業も自動的にしてくれると大変ありがたいのです。 ヘルプやサイト検索などしてみましたが、よく分かりませんでした・・・。 出来れば夕方までには完成させたいです。 お時間のある方、宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- 日にちの隣の列を書き出すには?
A列に日付(西暦、月、日)、B列には曜日(祝日は祝と反映)が一年分入力されています。(2009年とは限りません) C1セルにある日にちを月と日だけで入力(たとえば1月1日)したときにD1セルに対応する日付の曜日の欄のセル番号(1月1日の場合は B1、2月1日の場合はB32)と表示させる方法を教えて下さい。(なぜB1と表示させたいのかというと、 B1 をindex関数で参照して別の作業をしたいからです) 以下は私がチャレンジしてダメだった内容です。 難しいのは、A列の日付が過去の日付という事です。C1セルにはただ単に1月1日、とだけ入力するようにしたいので、セルの日にちは自動的に「今年の」1月1日になってしまいます。A列が昨年以前の日付だとすると、C1=A列にはならないので、簡単なlookup関数などでは処理できません。 E列に=month(A1)、F列に=day(A1) 、として下までコピーし、month(C1)とday(C1)と同じものを探す、なんていうやり方は頭にあるのですが、それを表現できません。 また、A列でヒットした場合、B列のセル番号を返す、(A123が同じだった場合にB123と返す)というのが私の知識では及びません。(1つ下のセル、なら、row+1ですが、1つ右のセル、だと、column+1ではできない) いろいろなやり方があると思いますが、これを使って1年分の売り上げ分析を一枚のシートでやろうと思っているので、なるべくこの部分で複雑な展開にはしたくありません(ファイルが重たくなる)。 皆さんのお知恵をお願いいたします。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル、日曜日だけ除いた平均値の求め方。
よろしくお願いします。 Aには日付、BにはTEXT(曜日)Cには数字 8/1 日 1 8/2 月 10 8/3 火 3 : : そこで、日曜日だけを除いた、平均値と 月曜日だけの平均値を出したいと思っています。 TRIMMEAN関数を使いなんとかやろうとしてるのですが・・なかなかできません。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excelの計算式について
Excelを使って色々な計算式を作成したいと考えております。 うまく、説明ができない為にExcelファイルを簡易的では御座いますが 作成してみました。 よろしければご覧になって見てください。 http://technocompany.web.fc2.com/excel.jpg A列に日付を入力した場合に自動的に曜日が入るようにしたいのですが このような事は可能でしょうか? A列に入力した日付が月曜日~金曜日の場合には、その右側のC列に¥35000円と入力 A列に入力した日付が土曜日の場合には、その右側のC列に¥22200円と入力 A列に入力した日付が日曜日、祝祭日の場合には、その右側のC列に¥18800円と 自動的に入力をできる様にしたいのですが、このような事は可能でしょうか? B列には、異なる数値を日々入力して、D列にB-Cの数値を表示したいと考えております。 D列に表示された、数値に0.53を駆けた数値を、E列、若しくはF列に表示したいと考えております。 次にE列、若しくはF列に表示された数値に¥15500円を足した金額をG列に表示したいと考えております。 初心者では御座いますがご指導の程よろしくお願い致します。
- ベストアンサー
- その他([技術者向] コンピューター)
- カレンダーで日曜のセルを赤にする方法
エクセルで月予定表を作成しています。 A列:日にち B列:曜日 C,D,E列:予定を記入。 日曜日のC,D,E列のセルをを赤に塗りつぶすのはどう設定すればいいか教えて下さい。よろしくお願いします。
- 締切済み
- オフィス系ソフト
- 日曜日の色の変更
初心者ですので、よろしくお願いいたします。 早速ですが、日常点検の表を作成しております。 A1セルに2007年、B1セルに1月と入力。 A2セルに日付表示のDATE関数設定【=DATE(A1,B1,1)】 A3セルに曜日設定【ユーザー定義aaa】 個々のセルに日付・曜日を表示した場合 『日曜日の場合(日付と曜日)の色変更』の仕方がわかりません。 一つのセルに月・日・曜日表示の場合(WEEKDAY設定・・)は 出来るのですが・・ 条件付き書式もうまくいきません。(設定方法が間違っているのかわかりませんが・・) 説明が不器用ですみません。 どなたかご存知の方、よろしくご伝授ください。
- ベストアンサー
- オフィス系ソフト
- 1991年10月の日曜日の日にちを知りたいのですが
タイトルどうりです、1991年10月の日曜日ということは覚えています。 でも日にちが分かりません。たしか、20日前後の日にちで日曜日だとは、思ったのですが、、、、 それと、10月の祝日は、15年前も体育の日だけですか?最近祝日が新しく出来たり、名前が変わったり、移動したり、、よくわかりません なんか昔はもうひとつあったような・・・?気のせいかな??
- ベストアンサー
- その他(生活・暮らし)
- エクセルで2013年分の曜日の表示がズレてしまう。
エクセルについての質問です。 B1に年(2013) D1に月(1) を入力し、 A10から縦に日付けが入るようにし、 その横のB10以降B列に曜日が入るような表を作っています。 表示は A10・・・1 B10・・・月 A11・・・2 B11・・・火 A12・・・3 B12・・・水 というような形です。 数式は A10に =DATE(B1,D1,1) B10に =A10 A11に =A10+1 B11に =A11 セルの書式設定はA列が ユーザー定義 d B列が ユーザー定義 aaa にしています。 この方法で2012年までは曜日の自動入力(?)にズレはなかったのですが、 2013年分からは曜日がズレてしまい、 本来2013年1月1日は火曜日なのですが、表示されるのは日曜日になってしまいます。 どのようにしたら直りますか? ちなみにエクセルが2010を使っています。 どうぞよろしくお願いします。
- ベストアンサー
- その他MS Office製品
- excelで特定範囲の特定数値を指定する方法について
excelで特定範囲の特定数値を指定する方法について A列に曜日を入れて、B列、C列に数字を入れるとします。例えば、ここで日曜日のB列の数値を基準として、「日曜日以外の曜日の中で、B列の値が日曜日より高く、さらにその曜日のC列より高い」という条件を、何ヶ月分もまとめてD列に呼びだすことは可能でしょうか? どうぞ宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
補足
ご親切、ご丁寧にアドバイス頂き、ありがとうございます。 式の内容については、一度には理解が出来ないですが、 まずはご指摘の通り、動作確認をしてみます。 とりあえず、お礼までとさせていただき、 わかないことがあれば、再度補足質問させて頂きます。