エクセル関数

月２回。第2、4週にはいる数字を別の表にまとめたい場合のエクセル関数を教えていただきたいです。

分かりにくくて申し訳ありませんが、
どなたか教えていただけると助かります。

質問者からの補足コメント

• 説明不足で失礼しました。
  １から31日までで数字がはいるのは月に２回。その他のセルは空白です。月曜日始まり。基本は第2、4金曜日ですが祝日などで変更もあり得ます。
  数字を元に請求書を作成したいため空白セルのない表が必要です。本来ならば数字の入ったセルのみを指定して持ってくるのがよいのですが、関数では難しくなってしまうようです。
  何か良いアイディアをお教えいただけると助かります。

    補足日時：2019/04/25 23:29

No.4 ベストアンサー 回答者： 銀鱗 回答日時： 2019/04/26 03:14

まず、第2週の月曜日の求め方です。

これは月曜日から日曜日までのカレンダーを作ると理解しやすいでしょう。

第2月曜日は2日から8日までの日付けになります。
（その月の1日が日曜日なら2日が第2週の初日…月曜日ということ）
どうやってそれを求めるか。
1日が何曜日かを確かめれば良いのです。
　1日が日曜日なら１日後の日付が第2月曜日
　1日が土曜日なら２日後の日付が第2月曜日
　1日が金曜日なら３日後の日付が第2月曜日
　1日が木曜日なら４日後の日付が第2月曜日
　1日が水曜日なら５日後の日付が第2月曜日
　1日が火曜日なら６日後の日付が第2月曜日
　1日が月曜日なら７日後の日付が第2月曜日
ということ。
第2月曜日が何日か分かれば、第4月曜日もおのずと分かりますね。
WEEKDAY関数をここで使います。
　＝WEEKDAY(シリアル値,種類)
のようにして使います。
「シリアル値」は日付を示す値です。（シリアル値について分からないのであれば自身で調べて理解してください）
「種類」は、曜日に対してどのような数字を割り当てるかを決める数値になります。
月曜日を１、日曜日を７とするなら、「2」を指定します。（これもWEEKDAY関数の使い方を自身で調べて確認してください）
　＝WEEKDAY("2019/4/1",2)
とすると「1」という値が返ってきます。
そうです。2019年4月1日は月曜日ですね。
そんなわけで、
　＝8-WEEKDAY("2019/4/1",2)
こうすると、第2月曜日が何日後かを知ることができます。
……面倒なんで……
　＝9-WEEKDAY("2019/4/1",2)
とすると、返ってくる数字が第2月曜日の日付けになります。
第4月曜日なら更に2週間後（14日後）なので、
　＝23-WEEKDAY("2019/4/1",2)
でOK。

さて、このままでは都度数式に日付を入力しなければならなくなります。
どこかに年や月の数値があれば、それを参照しましょう。
　A1セルに年を示す数値「2019」
　A2セルに月を示す数値「4」
が入力されているなら、
　=DATE(A1,A2,1)
で、「2019年4月1日」のシリアル値になります。
　＝9-WEEKDAY(DATE(A1,A2,1),2)
　＝23-WEEKDAY(DATE(A1,A2,1),2)
ということですね。

あとはOFFSET関数などを使って第2週、第4週の範囲を指定して、そこにある数値を拾えばいい。
週に値は1つしかないという前提があるので、ここはSUM関数にOFFSET関数で範囲を与えれば良いでしょう。
（この前提が変わった場合は別の方法を採用する必要があります）
……そしてOFFSET関数の使い方は自身で調べてもらうとして……

　=SUM(OFFSET(B$2,9-WEEKDAY(DATE($A$1,$A$2,1),2),1,7,1))
　=SUM(OFFSET(B$2,23-WEEKDAY(DATE($A$1,$A$2,1),2),1,7,1))

こんな感じでしょうか。
（月曜日から日曜日まで7日間の範囲で合計を求めています）


・・・余談・・・

元の表に入力されている値が「数値」ならSUM関数で良いのですが「文字列」だったらどうするか。
答え：CONCAT関数で文字を繋いでしまいましょう。
　=CONCAT(OFFSET(B$2,9-WEEKDAY(DATE($A$1,$A$2,1),2),1,7,1))
　=CONCAT(OFFSET(B$2,23-WEEKDAY(DATE($A$1,$A$2,1),2),1,7,1))


図は、参照している範囲が分かるようにセルを「セルの書式設定」で塗りつぶしを行っています。

No.3 回答者： tom04 回答日時： 2019/04/25 14:24

No.2です。

前回の投稿は第2・第4月曜と決めつけていました。
ご希望は何曜日の第2・第4なのでしょうか？

F1セルの数式を変えるだけで可能です。
火曜日　→　=DATE(A1,C1,0)-WEEKDAY(DATE(A1,C1,0)-1,3)
水曜日　→　=DATE(A1,C1,0)-WEEKDAY(DATE(A1,C1,0)-2,3)
木曜日　→　=DATE(A1,C1,0)-WEEKDAY(DATE(A1,C1,0)-3,3)
　　・
　　・
日曜日　→　=DATE(A1,C1,0)-WEEKDAY(DATE(A1,C1,0)-6,3)

のようにマイナス部分で調整可能です。m(_ _)m

No.2 回答者： tom04 回答日時： 2019/04/25 14:19

こんにちは！

シリアル値で処理するのが間違いないと思います。
やり方だけ・・・

↓の画像のようにカレンダーを作成します。（A1・C1の数値を入れ替えれば自動で1か月分のカレンダーになります）

まず、F1セルに
=DATE(A1,C1,0)-WEEKDAY(DATE(A1,C1,0),3)
という数式を入れます。
これで画像では2019年3月（ひと月前）の最終月曜日が表示されます。
このシリアル値　＋14　が第2月曜、+28　が第4月曜というコトになります。

画像でA4セル（セルの表示形式はユーザー定義から d(aaa)　としています）に
=IF(MONTH(DATE(A$1,C$1,ROW(A1)))=C$1,DATE(A$1,C$1,ROW(A1)),"")

という数式を入れ31日分のA34セルまでフィル＆コピー！

そして結果のG4セルに
=INDEX(B$4:B$34,MATCH($F$1+ROW(A1)*14,$A$4:$A$34,0))

という数式を入れ、列・行方向にフィル＆コピーしています。m(_ _)m

No.1 回答者： 銀鱗 回答日時： 2019/04/25 13:56

ごめん。

分かり難いどころか全く説明不足です。

まず「週」は何曜日から始まりますか。
　月曜日ですか？
　日曜日ですか？
よく分からない業種指定の発想の
　水曜日ですか？

2週、4週に入す数値をすべて拾うのか、一つだけ拾うのか、
一つだけ拾う場合は週の初めに出た数値なのか、最も大きい数値なのか小さい数値なのか、指定した値に最も近い数値なのか。

それをどのように表示させるのか。

…といった詳細が必要。

・・・
いずれにしても
1日の曜日（WEEKDAY関数）と日の関係から2週目、4週目を判断することになります。
あとはどの値を拾うかという詳細な条件次第。

ということで一つの関数だけで実現できるものではありません。
チョット面倒な処理を複数の関数や演算を行いながら拾い上げます。