Excel「予定表から土日だけ抜いて一覧表示」

質問

Excelで予定表をつくっています。あるセルに西暦、あるセルに月を入れる事で自動生成されるもので、それはできているのですが、その日付から、土日だけを抜き出し別途一覧表にする必要があります。

マクロを使わず実現できるでしょうか。

よろしくお願いいたします。

回答No.6

tom04 · ベストアンサー

こんばんは！

＞あるセルに西暦、あるセルに月を入れる事で自動生成されるもので、それはできているのです・・・

とありますので、一案です。
実際の配置がどのようになっているのか判らないので、勝手に↓のような感じにしてみました。
日付データの隣のセルに「曜日」のセルを設けます。
（画像でA列はシリアル値）

B5セル（セルの表示形式は　aaa　としておきます）に
=IF(A5="","",IF(WEEKDAY(A5,2)>=6,A5,TEXT(A5,"aaa")))
という数式を入れ31日のB35セルまでオートフィルでコピー！

結果の
D5セルに
=IF(COUNT($B$5:$B$35)<ROW(A1),"",INDEX($A$5:$A$35,SMALL(IF(ISNUMBER($B$5:$B$35),ROW($A$1:$A$31)),ROW(A1))))

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合はD5セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これをオートフィルで下へコピーすると
画像のような感じになります。m(_ _)m

お礼

ありがとうございます！

これは・・・汎用性が高い、良い数式かも・・・。

私もそうですが、助かる方がたくさんいるんじゃないかと思います！

回答No.11

tom04

No.6です！

私の投稿が他の回答者様の混乱を招いているような気がします。

普通に考えれば休日（土日）のデータは必要ないはずですので、
他の回答者様が回答されていらっしゃるように
タイトルを額面通りに受け取れば、土・日曜を除いた日を抽出したいのではないかと思います。

当方の勘違いで
＞土日だけを抜き出し別途一覧表にする・・・
は
＞土日だけを抽出して一覧表にする！
という解釈で前回投稿しました。

実際は
＞土日だけを除外して平日を別途一覧表にする！
というコトがご希望なのですよね？

そうであれば他の回答者様の回答で問題ないと思います。

尚、余計なお世話かもしれませんが、
前回の当方の画像を利用して同様の結果を出したい場合を載せておきます。
（前回アップした画像の配置と一緒という前提です）

B5セル（表示形式は　aaa　としておいて）に
=IF(A5="","",IF(WEEKDAY(A5,2)>=6,TEXT(A5,"aaa"),A5))
という数式を入れ31日目までオートフィルでコピー！

結果のD5セル（今回は配列数式ではありません）に
=IF(COUNT($B$5:$B$35)<ROW(A1),"",SMALL($B$5:$B$35,ROW(A1)))
という数式を入れオートフィルでずぃ～～～！っとしてはコピー！
こんなんではどうでしょうか？

※　他の回答者様および質問者様へ！　※
どうも失礼しました。m(_ _)m

お礼

ありがとうございます。

いえいえ当初ので良いんです、まぎらわしくてすみませんでした。

そもそもの経緯は、

(1)西暦と月を指定する事で自動生成させる予定表をつくった(ここまではできた)
(2)他シートに土日含む休日一覧表をつくり、予定表の一致する行に網かけをしたい
(3)だから休日一覧表に土日を表示したい

という事だったんです。

やってみたら、土日だけの表を、抜けのないよう作るのって難しかったんです。

お騒がせしました。でもお一人お一人、とても参考になるご意見でした。
ちょっと急ぎ足でもありましたので、また改めて、全部見直させていただきますね。

とり急ぎお礼まで。

回答No.10

esupuresso

Sheet1セルA1に「年」、セルA2に「月」、セルB1に「日」を入れたと仮定して・・・

Sheet2のA列に「土」、「日」のみを返す式は・・・

Sheet2のセルA1　=IF(WEEKDAY(Sheet1!B1)=1,Sheet1!B1,Sheet1!B1+7-WEEKDAY(Sheet1!B1))
　　 〃　　セルA2　=IF(WEEKDAY(Sheet2!A1)=1,Sheet2!A1+6,Sheet2!A1+1)
　 　〃　 セルA3　=Sheet2!A1+7

の式を入れる方法もございます　<(_ _)>

お礼

ありがとうございます。

そうですね、途中に作業表をかませて行く事で、わかりやすくはなります。
ある意味それがいちばんスマートかも知れないです。

回答No.9

noname#204879

[No.5お礼]へのコメント、
「なるほど」って、私のは的外れな回答だったのですね。
タイトルが「土日だけ抜いて」なので、今回は平日だけを表示するのかと思ってました。
お恥ずかしい(*^_^*)

お礼

ありがとうございます。

いえ、勉強になりました。

タイトルまぎらわしかったですね、すみません。

回答No.8

KURUMITO

回答3です。
シート1に月の日付がA3セルから下方に入力されているとします。そこで例えばF列を作業列としてF3セルには次の式を入力して下方にドラッグコピーします。

=IF(A3="","",IF(WEEKDAY(A3,2)>=6,MAX(F$2:F2)+1,""))

この式の意味ですがA3セルが空白なら空白にしなさい、そうでない場合にはWEEKDAY関数を使ってA3セルので日付が土曜日であるか日曜日であるかを調べなさい、WEEKDAY関数は引数に2が指定されていますので土曜日なら6の値、日曜日なら7の値になります、WEEKDAY関数の値が6以上の場合には、この式が入力されているのはF3セルですからF2からF2までの数値の最大値に1をプラスした数値を表示しなさい、という意味になります。
この式を下方にドラッグして例えばF5のセルでは例えばA5セルが土日であるならばF2（セル番地が絶対参照の形でF$2となっているので式を下方にドラッグしても行番号は変わりません）セルからF4（単にF2のセル番地ですと下方にドラッグすれば行番号が変わってF4になります）セルまでの最大値に1をプラスした数値を表示しなさいとのことになります。結果としてF列の上の行からA列で土日の行が何番目になっているかを表示することになります。
シート2ではその番号を利用してシート1のA列の日付を表示させることになります。

お礼

ありがとうございます。

F2からF2の値+1、という発想が出ませんでした。
勉強になります。

回答No.7

keithin

失礼、土日を抜いた日付を並べるんじゃなく、まだ土日だけを並べたいご質問の継続だったんですね。

(再掲)
シート１のＡ１に年を記入する
シート２のＡ１に
=IF(Sheet1!$A$1="","",MIN(FLOOR(DATE(Sheet1!$A$1,1,-1),7)+8,FLOOR(DATE(Sheet1!$A$1,1,0),7)+7))
を記入する
シート２のＡ２に
=IF(Sheet1!$A$1="","",MAX(FLOOR(DATE(Sheet1!$A$1,1,-1),7)+8,FLOOR(DATE(Sheet1!$A$1,1,0),7)+7))
を記入する

シート２のＡ３に
=A1+7
を記入し、以下コピーする。

お礼

ありがとうございます。

そうです。タイトルがまぎらわしかったですね、すみません。

土日だけを抽出したリストをつくりたいという事なんです。
FLOOR関数を使うというご回答が多いですね。

勉強になります。

回答No.5

noname#204879

添付図参照
A3: =WORKDAY(DATE(OFFSET($A$1,,12*INT((COLUMN(A1)-1)/12)),A$2,0),ROW(A1),holidays)

お礼

ありがとうございます。

WORKDAYを使うのですね・・・。なるほど。
参考になります。

回答No.4

keithin

＃回答しても試してみることもしないご様子ですので、以後はもう回答しませんので悪しからず。

エクセル２００３以前を使っているなら
シート１のＡ１に年を記入
シート２のＡ１に
=IF(AND(YEAR(DATE(Sheet1!$A$1,1,0)+ROW(A1))=Sheet1!$A$1,WEEKDAY(DATE(Sheet1!$A$1,1,0)+ROW(A1),2)<6),DATE(Sheet1!$A$1,1,0)+ROW(A1),"")
と記入、Ａ３６６までコピー

シート１に戻って
=IF(ROW(A1)>COUNT(Sheet2!A:A),"",SMALL(Sheet2!A:A,ROW(A1)))
以下コピー
などのように作った方が簡単です。

お礼

ありがとうございました。

回答が言葉足らずでしたでしょうか、誤解されたら申し訳なかったのですが、前回ご回答いただいた時点で、試させていただいております。

ただ、自分のつくったものの都合で、他の方の回答の方が検証しやすかったため、そちらを重点的に使わせていただいておりますが、ご回答いただいた内容はひとまずいただきました。

かさねてお礼申し上げます。お気を害されたのでしたらすみませんでした。

回答No.3

KURUMITO

分かり易く簡単な方法は作業列を作って対応することでしょう。
シート1に月の日付がA3セルから下方に入力されているとします。そこで例えばF列を作業列としてF3セルには次の式を入力して下方にドラッグコピーします。

=IF(A3="","",IF(WEEKDAY(A3,2)>=6,MAX(F$2:F2)+1,""))

そこでシート2のA3セル以降に土日の日付を表示させるとしたらA3セルに次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!F:F),"",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!F:F,0)))

A列を選択して右クリックして「セルの書式設定」から「表示形式」で「日付」から選んでOKします。

補足

ありがとうございます。

すみません、かさねて教えていただけたら幸いです。

入れ子の中のIFに、「日付の最初のセルに何か入っていて、それがしかも土曜か日曜にあたる時」を第一引数に設定しているのはわかるんですが、第二引数に設定されている部分が読み解けません。

ご解説いただければありがたいです。

回答No.2

MarcoRossiItaly

恐らくは明日くらいに、前回のご質問（質問番号7759193、そのページのアドレス参照）に No.2 の回答が掲載されますので、そちらをご覧ください。

お礼

ありがとうございます。

Excel「その年の最初の土・日、どちらか早い方」
http://okwave.jp/qa/q7759193.html

の事だと思いますが、こちら、ご迷惑になるかと思い、早々にしめてしまいました。
こちらにご回答いただければ、私は見られますが・・・。

新しい質問を立てた方が良ければそういたしますが、今はとりあえずこのままにします。

かさねて本当にありがとうございます。

補足

「その年の最初の土・日、どちらか早い方」を取得する事を続けて考えてみたらですね、土曜を取得して+7ずらずら、日曜を取得して+7ずらずら、でもこの場合は良い訳ですね。それが一番簡単かな!?

回答No.1

keithin

こちらもご利用のエクセルのバージョンは幾つですか。
簡単のためエクセル２００７以降で、Ａ１に年を記入してあるとすると

=IF(YEAR(DATE($A$1,1,0)+ROW($A$1:$A$366))<>$A$1,"",IFERROR(SMALL(IF(WEEKDAY(DATE($A$1,1,0)+ROW($A$1:$A$366),2)<6,DATE($A$1,1,0)+ROW($A$1:$A$366)),ROW(A1)),""))
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、下向けにコピー。

＃エクセル２００３以前を使っているなら
シート１のＡ１に年を記入
シート２のＡ１に
=IF(AND(YEAR(DATE(Sheet1!$A$1,1,0)+ROW(A1))=Sheet1!$A$1,WEEKDAY(DATE(Sheet1!$A$1,1,0)+ROW(A1),2)<6),DATE(Sheet1!$A$1,1,0)+ROW(A1),"")
と記入、Ａ３６６までコピー

シート１に戻って
=IF(ROW(A1)>COUNT(Sheet2!A:A),"",SMALL(Sheet2!A:A,ROW(A1)))
以下コピー
などのように作った方が簡単です。

＃オマケ
年の最初の土日：
=IF(A1="","",MIN(FLOOR(DATE(A1,1,-1),7)+8,FLOOR(DATE(A1,1,0),7)+7))
次の土日：
=IF(A1="","",MAX(FLOOR(DATE(A1,1,-1),7)+8,FLOOR(DATE(A1,1,0),7)+7))

お礼

ありがとうございます！

Excelは、私が使用しているのが2002、実際に動かすのが2003でございます。

そう、お察しのとおり、年の最初の土日を取得しようとして失敗した訳でして、本当にありがとうございました。

おそらく前質問を追っかけてご回答いただいたのだと思いますが、調べ物でご覧になる方もいらっしゃると思うので一応事情をここに説明しますと、
(1)西暦と月を指定する事で自動生成させる予定表をつくった(ここまではできた)
(2)他シートに土日含む休日一覧表をつくり、予定表の一致する行に網かけをしたい
(3)だから休日一覧表に土日を表示したい

という流れで、別の質問もしており、それを踏まえてご回答いただきました。