エクセルの関数、セルの参照について

質問

いつもお世話になっております。

添付ファイルのような表を作成しました。

やりたい事は、
(1)累計の欄に数字を直接記載する。
(2)単日の欄に当日と前日の数字の差を記載する（E10はD10-D5）

という簡単な表なのですが、土曜日と日曜日は数字の変動がないため、見た目を考えて空欄にしたいと思ってます。
そうすると、4日のE20はD20-D5になるので、数式のコピーではうまく行きません。
今の私の知識だと、IF関数でダラダラと数式を作って行くしか思いつきません。

そこで、土日は金曜日の数字を記載して、条件付き書式で土日は白文字にして見た目だけ空欄にしようかなと考えました。
　=weekday(B3)=1のときは白文字　　
　=weekday(B3)=7のときは白文字　セル範囲はD5：E7　
としたのですが、これだとD6とD7も白文字になってしまいます。
B3を絶対参照にすると、解決しますが、コピーした場合、また、セルの参照をするしかありません。

スミマセン分かりずらい文章をダラダラと書きましたが、要は、
(1)土曜日、日曜日は計算の対象から外したい場合は、どのような数式が最適でしょうか？
(2)セルの参照方法として、B3が●●だったら、D5～D7を○○したい場合、B3セルを絶対参照すればいいわけですが、コピーした場合は、個別に参照しなおすしかないのでしょうか？

どなたかご教示をお願いします。

回答No.4

kagakusuki · ベストアンサー

回答No.2です。

　不明な点に関する情報を未だに補足頂けない様ですので、取り敢えずとの話として、

●数字を表示しないのは土日だけであり、祝日や振替休日には平日と同様に数字を表示しなければならない

●初日が土日であるか否かには関わらず、D5セルには前月からの累計が必ず入力されている

という条件の下で話を進めさせて頂きます。

　数字が表示されないのは土日だけであり、火曜日～金曜日の場合は特に問題が無いのは無論の事、土日の日の場合もD列とE列に何も表示しないのですから、計算が問題になるのは月曜日だけであり、月曜日の場合にのみ3日前のデータを参照すれば良いという事になります。
　尚、祝日にも数字が入力されるという条件なのですから、金曜日の日の所に数字が入力されていないという事を心配する必要は無い筈です。
　そして、2日目か3日目が月曜日である場合には、1日目のデータを参照する様にすれば良い訳です。
　ですから、E10セルに入力する関数の一例は次の様なものとなります。

=IF(AND(ISNUMBER($D10),ISNUMBER(INDEX($B:$B,ROW()-MOD(ROW()-ROW($A$3),5)))),IF(WEEKDAY(INDEX($B:$B,ROW()-MOD(ROW()-ROW($A$3),5)),2)>5,"",$D10-INDEX($D:$D,MAX(ROW()-5*(1+2*(WEEKDAY(INDEX($B:$B,ROW()-MOD(ROW()-ROW($A$3),5)))=2)),ROW($D$4)+MOD(ROW()-ROW($D$4),5)))),"")

　もし、E11セルやE12セルの値も、E10セルの場合と同様に、D列の同じ行にあるセルの値から前日の累計（但し、E11の参照先はE10の参照先の1つ下で、E12の参照先はE10の参照先の2つ下）を差し引いた値とする場合には、上記の関数をE10セルに入力してから、E10セルをコピーして、E11～E12の範囲に貼り付けて、その上で、E8～E12の範囲をまとめて範囲コピーして、E13～E157の範囲に貼り付けて下さい。

お礼

ありがとうございます

回答No.3

tom04

こんにちは！

一案です。
A列・B列をすべてシリアル値としてみてはどうでしょうか？
A1（結合セル）の表示形式はユーザー定義から
0年
としておいて、2013と数値のみを入力
同様にD1セルの表示形式もユーザー定義から
0月
としておき　11　のように数値のみを入力

A3～A7セルを結合し　→　セルの表示形式はユーザー定義から
d
としておき、
=IF(MONTH(DATE(A$1,D$1,ROW(A5)/5))=D$1,DATE(A$1,D$1,ROW(A5)/5),"")
という数式を入れておきます。
B3～B7セルを結合　→　セルの表示形式はユーザー定義から
aaa
としておき
=IF(A3="","",A3)
としてA3・B3セルを選択　→　B3セルのフィルハンドルで31日分オートフィルで下へコピー！

次にE5セルを選択　→　条件付き書式　→　新しいルール　→　「数式を使用して・・・」　→　数式欄に
=AND(A3<>"",WEEKDAY(A3,2)>5)
として　→　書式　→　フォントの「白」を選択しOK
（ここでは条件好き書式だけ設定し、セルに数式は入力しません）

D3～E7を範囲指定　→　E7セルのフィルハンドルで下へ5行だけコピー！
ここでE10セルに数式を入れます。
=D10-E5
と入れてOK

最後にD8～E12を範囲指定　→　E12セルのフィルハンドルで最終行（31日）まで
5行ずつ下へコピー！

これで数式もコピーされますし、条件付き書式で土日のフォントは白になり見えなくなると思います。

※　その月の初日だけは手入力となります。

こんな感じではどうでしょうか？m（_ _）m

お礼

ありがとうございます

回答No.2

kagakusuki

確認したいのですが、

>そうすると、4日のE20はD20-D5になるので、数式のコピーではうまく行きません。

と書かれている所から考えて、4日の日は振替休日であるにもかかわらず数字を表示するという事だと思われます。
　そうしますと、数字を表示しないのは土日だけであり、祝日や振替休日には数字を表示しなければならないという事なのでしょうか？

　それから、1日の日が土日の場合には、前日の日のデータを、一体どうやって知れば良いのでしょうか？

回答No.1

mshr1962

土日を白文字でいいなら
=WEEKDAY(INDIRECT("B"&FLOOR(ROW()+2,5)-2),2)>5

でD3:D7はB3を参照、D8:D12はB8を参照します。
あとWEEKDAY(参照セル,2)にすれば土=6,日=7になるので、>5でまとめられます。

お礼

ありがとうございます