エクセルで1行ごとに異なる計算式が入っているセルをコピーする方法

1行ごとに異なる計算式が入っているセルをコピーする方法

エクセルで図のような表を作成しており、図示のように下方向にコピー（オートフィル）して入力を簡略化したいのですが方法が見つかりません。

シフト表を作成しているのですが従業員が多く入力の簡略化をしたく悩んでいます。
別シートに入力したシフト記号を参照し（E4）、その記号に応じて別シート上の表にある出勤時間(E5)、退勤時間(F5)、勤務時間(F4)を参照するような表を作成しています。
F列のオートフィルは可能なのですが、E列のオートフィルが分からず、もし可能であれば教えて下さい。

No.1 回答者： zongai 回答日時： 2020/11/02 19:11

4,5行目　のときに　4

6,7行目　のときに　5
8,9行目　のときに　6

という数字を作り出す数学的な知識と、
それを式に取り込む関数の知識が必要ですね。

うまく説明できなさそうなので、ので、式だけ書いてみます。


★各人の左上にあたるセルの式
E4　=INDIRECT("別シート!"&ADDRESS(INT((ROW()-4)/2)+4,5))

★各人の左下にあたるセルの式
E5　=VLOOKUP(INDIRECT(ADDRESS(ROW()-1,5)),～～～

★各人の右上にあたるセルの式
F4　=VLOOKUP(INDIRECT(ADDRESS(ROW(),5)),～～～

★各人の右下にあたるセルの式
F5　=VLOOKUP(INDIRECT(ADDRESS(ROW()-1,5)),～～～

上記4セルをまとめて選択した状態で、下にオートフィルしてみて下さい。

あ、F列は不要か…

この回答へのお礼

早速のご回答ありがとうございます。
数学的な知識も必要だったんですね、関数だけしか頭にありませんでした。
試してみた所、見事にできました。
まだ計算式の内容が理解できていないのでゆっくり理解したいと思います。

余談ですが、プロフィール拝見させて頂き、FALSEが0で代替できるのを初めて知りました。
勉強になりました(;^_^A

今回はもうお一方の方をベストアンサーとさせて頂きましたが、
同じくらい勉強になりましたし、瞬時に回答を頂き本当にすごいと思いました。
ご協力ありがとうございました。

お礼日時：2020/11/03 12:33

No.2 ベストアンサー 回答者： fujillin 回答日時： 2020/11/02 21:51

こんばんは

＞E列のオートフィルが分からず、～～
奇数行の式は普通にオートフィルが可能な式になっていると思いますので、問題は偶数行にある式ということと解釈しました。

既に回答があるように、2行進んだ時に参照先が1行分進めばよいので、1/2になるようにすれば良さそうです。
式はいろいろ考えられますが、揮発性関数であるOFFSETやINDIRECTを避けるなら、
　=INDEX(別シート!E:E, INT(ROW()/2)+2)
などとしておけばよいと思われます。

一方で、全ての行（奇数行も偶数行も）を同じ式にしてしまいたければ、式は少し長くなりますが、
　=IF(MOD(ROW(),2), 奇数行の式, 偶数行の式)
のようにしておくことで可能になります。

式としては若干無駄ではありますが、行の位置によらない式にできるので、間違って消してしまったり、行の挿入･削除を行ってしまったりした際にも、上下にあるどの式でもコピペすれば修復が容易にできるという特性があります。

この回答へのお礼

ご回答ありがとうございます。
双方とも試してみましたが無事できました。
奇数偶数行で共通の式に出来る計算式が素晴らいです

また、横方向へのオートフィルも可能だったためこちらをベストアンサーとさせて頂きます。
ご協力ありがとうございました。

お礼日時：2020/11/03 12:32