エクセルを使って数値を整理していますが、もっと簡単な方法がありますか？

質問

標記の作業をしておりますが、１年間に
1000回以上数式を入替えなければならないため、
賢いエクセルなら別な方法があるのかな？と
お尋ねします。

作業の内容；
１）ある時点；Ｓ1（○月○日○時）の数値が決定されてます。
２）その時点以降の時点；Ｓ2の数値があります。
３）Ｓ1とＳ2間の各時点（空白セル）に等しい差となる数値を入れ込み
　　１年間数値が連続している表をつくりたい。

私が作った関数の方法（原始的ですが）
Ａ列に数値の１～750程度（1ヶ月×24時間分）を表示させる。
Ｂ列に年月日と時間を表す：（○月○日○時）
Ｃ列が決まっている数値が入る欄で、
例として、Ｓ1：1月10日9：00時の数値が30とする。
Ｓ2が：1月日22：00時の数値が100とする。
その間の12の空白セルに、数値を入れるため、
10時のセル（Ｃ11）に以下の式を作った。

=($C$23-$C$10)/($A$23-$A$10)+C10
で35.38を得、次セルは、40.77と差が5.38づつの数値。
これをＣ22までコピーして完成する。

さて、次の箇所(Ｓ2とＳ3間)では、
数式の中の＄記号がある４ヶ所を入れ替え
なくてはならず、この作業を繰り返すのは気が遠くなります。

もっと簡単に作業ができる方法や数式をご存知でしたら、
教えていただけるようお願いします。

回答No.6

nishi6 · ベストアンサー

補足欄を注意してみていませんでした。A列には連続数値が入っているんですね。
D、F、H列を変更して下さい。判定をC列に変えています。

D10 =IF(C9<>0,C9,D9)
E10 =INDIRECT("C"&MIN(IF(C10:$C$751<>"",ROW(C10:$C$751))))
F10 =IF(C9<>0,A9,F9)
G10 =INDIRECT("A"&MIN(IF($C10:C$751<>"",ROW($C10:C$751))))
H10 =IF(C10<>0,C10,(E10-D10)/(G10-F10)+H9)

多分うまくいくと思います。失礼しました。

お礼

nishi6様

ご指導ありがとうございました。
完璧に私がほしい正確な数値を得ることができました。

私は、補完やINDIRECT、MIN、ROW　等の意味も分からずですが
教えていただいた数式を用いて作業をはじめられます。
（落ち着いてから言葉の意味を理解します）

大変貴重なお時間をとらせ、まことにありがとうございました。
追伸；フロッピーに厳重保管しました。貴重品とします。

回答No.5

nishi6

算式は５種類しかありません。
補足によると、その内、E10、G10がうまくいっていないかもしれません。（当方、Excel2000です）

回答をコピーして貼り付けたのなら、
　E10を選択してファンクションキーF2を押して編集モードにしてCtrl+Shift+Enterとします。
　G10についても同じようにします。

思惑通り実行できていれば、
　D10からH10は 0 30 0 9 30　です。
E、G列の算式は配列数式なので算式の窓で、{ }で囲まれて見えるはずです。

D10からH10をコピーしてD11に貼り付ければ、(以下同様にして)
　D11からH11は 30 100 9 22 35.38 <H11は =IF(A11<>0,C11,(E11-D11)/(G11-F11)+H10)
　D12からH12は 30 100 9 22 40.77 <H12は =IF(A12<>0,C12,(E12-D12)/(G12-F12)+H11)
　D13からH13は 30 100 9 22 46.15 <H13は =IF(A13<>0,C13,(E13-D13)/(G13-F13)+H12)

実際使っている補完式を少しアレンジしただけなので動くはずなのですが・・・
算式は５種類ですので確認してください。
Excelのバージョンが違うようですが、計算には関係してこないと思います。

最終行はA751のようなのでE10、G10の750を751にして下さい。修正しても登録はCtrl+Shift+Enterとします。
（最後のデータより1つ前までの計算には影響していないでしょう）

回答No.4

noname#1523

S1・S2の部分がどのように入力されているのか理解できないのですが・・・
要はA列に１年時刻が１時間単位で入力されており、
任意の時刻と数値を入力したら、その間の時刻に
数値を按分して増加させた値を求める方法と理解しました。

【入力用シート】シート名：Sheet1
＿＿＿　Ａ列＿＿＿＿＿＿　Ｂ列　Ｃ列　Ｄ列
１行目　時刻＿＿＿＿＿＿　空白　値
２行目　2002/01/10 09:00　式２　30
３行目　2002/01/10 22:00　式２　100
４行目　2002/01/15 01:00　式２　200
・・・
ｎ行目　2002/01/22 02:00　式２　300
　　　★Ｂ列は式をセット後非表示にする

【表示用シート】シート名：Sheet2
＿＿＿　Ａ列＿＿＿＿＿＿　Ｂ列
＿＿１行目　時刻＿＿＿＿＿＿　値
＿＿２行目　2002/01/01 00:00　ｘ　　<==データなしのためＸを表示
＿＿３行目　2002/01/01 01:00　ｘ
・・・
２２７行目　2002/01/10 09:00　30
２２８行目　2002/01/10 10:00　35.38461538
２２９行目　2002/01/10 11:00　40.76923077
・・・・・（5.38461538づつ増加）
２３９行目　2002/01/10 21:00　94.61538462
２４０行目　2002/01/10 22:00　100
２４１行目　2002/01/10 23:00　101.010101
２４２行目　2002/01/11 00:00　102.020202
・・・・・（1.01010101づつ増加）
３３８行目　2002/01/15 00:00　198.989899
３３９行目　2002/01/15 01:00　200
３４０行目　2002/01/15 02:00　×　　<==データなしのためＸを表示
（以下７５０行目までデータなしで×をセット）

【設定】
●データ入力：Sheet2
１）１行目にタイトルを適宜入力

２）Ａ列に　2002/1/10 9:00　の様に年月日、時刻を入力

３）B2セルに以下の式を入力してB3～Bnにコピー
　　　=IF(A2="","",VALUE(TEXT(A2,"yyyy/mm/dd hh:mm")))
　　　　表示形式は任意設定してください

　　　　EXCELの場合時刻値は手入力で入力した場合と
　　　　数式などで計算した場合、表示上は同じでも
　　　　日付値(時刻値)として微妙な誤差が発生し
　　　　検索や比較でマッチしなくなる為に
　　　　A1の入力値から年月日・時間と取り出して再セットします

●表示シート：Sheet1
１）参照範囲の特定
　　A1セルに『検索範囲』と文言を入力
　　B1セルに="Sheet2!B2:B"&COUNT(Sheet2!B:B)+1　と入力

　　Sheet2のB列の日付けデータ入力範囲を自動的にセットします
　　これは、後述の計算式のMATCH関数で検索する範囲を示します
　　MATCH関数で範囲をSheet2!$B:$Bと指定する事もできますが
　　処理負荷が増大しますので、この方法をとります

２）最大日付の特定
　　A1セルに『最大日付』と文言を入力
　　B1セルに　=MAX(Sheet2!A:A)　と入力

　　これも、後述の計算式で個々に指定した場合の処理負荷を軽減する為です

３）年月日・時刻の入力
　　a)A3セルに　2002/1/1 0:00　と最初の時刻を入力します
　　b)A4セルに以下の式を入力してA5～Anにコピーします
　　　　=DATE(YEAR(A3),MONTH(A3),IF(HOUR(A3)=23,DAY(A3)+1,DAY(A3)))+TIME(HOUR(A3)+1,MINUTE(A3),0)

　　　　　A3に2002/1/1 0:00、A4に2002/1/10 1:00と入力して
　　　　　オートフィルを行うと2002/12/31 23:59の様に誤差が発生します

４）計算式の登録
　　　計算式を簡略にする為に、以下の名前の定義を行います
　　　　a)名前：検索0　　参照範囲：=MATCH(Sheet1!$A4,INDIRECT(Sheet1!$B$1),0)
　　　　　　＊＊A列の日付けを入力シートの日付けと"同一値"で検索する式です
　　　　a)名前：検索1　　参照範囲：=MATCH(Sheet1!$A4,INDIRECT(Sheet1!$B$1),1)
　　　　　　＊＊A列の日付けを入力シートの日付けと"同一か内輪の値"で検索する式です

５）数値の計算
　　　B3セルに以下の式を入力して、B4～Bnにコピーします
　　　=IF(A3>$B$1,"×",IF(NOT(ISERROR(検索0)),OFFSET(Sheet2!$A$1,検索0,2,1,1),IF(NOT(ISERROR(検索1)),OFFSET(Sheet2!$A$1,検索1,2,1,1)+(OFFSET(Sheet2!$A$1,検索1+1,2,1,1)-OFFSET(Sheet2!$A$1,検索1,2,1,1))/((OFFSET(Sheet2!$A$1,検索1+1,1,1,1)-OFFSET(Sheet2!$A$1,検索1,1,1,1))*24)*((A3-OFFSET(Sheet2!$A$1,検索1,1,1,1))*24),"×")))

　　　　　a)A列の日付けがB1の最大値より大きければ計算せず"×"をセット
　　　　　b)同一値で検索しエラーが無い時『NOT(ISERROR(検索0))』には
　　　　　　OFFSET関数で該当の値を取り出します
　　　　　c)同一値がエラーの時は、内輪の値の有無をチェック『NOT(ISERROR(検索1))』し
　　　　　　エラーでなければOFFSET関数で必要な数値を取り出して計算します
　　　　　　　開始値＋(増分*開始時刻からの経過時間)
　　　　　　＝開始値+((終了値-開始値)/((終了時刻-開始時刻)*24)*((A列時刻-開始時刻)*24)
　　　　　　　　　・開始値　：OFFSET(Sheet2!$A$1,検索1,2,1,1)
　　　　　　　　　・終了値　：OFFSET(Sheet2!$A$1,検索1+1,2,1,1)
　　　　　　　　　・開始時刻：OFFSET(Sheet2!$A$1,検索1,1,1,1)
　　　　　　　　　・終了時刻：OFFSET(Sheet2!$A$1,検索1+1,1,1,1)
　　　　　d)内輪の値での検索がヒットしない時は"×"をセット

以上で入力シートにデータを入れることで、
表示シートに自動的に値がセットされます

お礼

xxsadayanxx様

私にとっては、夢のような方法を教えていただきありがとうございます。
（この作業を15年分するので、気が狂いそうでした）
今から、先に教えていただきました方(nishi6様)の方法で
実際の数値を入れてテストします。
今は夜2：00過ぎですので、明日といっても今日ですが、
xxsadayanxx様の方法でもテストさせていただきます。
このため、今夜は、御礼のみで失礼します。
ありがとうございます。

回答No.3

nishi6

補完しているわけですね。A列は1飛びが条件の算式ですね。（確認です）
マクロで計算した方が簡単かもしれませんが、今回は算式で計算してみました。
最初に算式を書くだけで、後での変更は多分不要（余りない）でしょう。

捕外が必要かもしれませんが、要件がはっきりしていないので考慮していません。
捕外などがあればマクロのほうが対応しやすいと思います。

A10= 9、C10= 30
A23=22、C23=100 とあり、以下A、C列に飛び飛びの入力があるとします。
最後にデータが入力されている行を例として750行目とします。10行目から750目までが計算可能になります。

算式はこの最終行が変更になれば、修正する必要があります。（その時は2箇所修正して下にコピーです）

D10　に　=IF(A9<>0,C9,D9)

E10　に　=INDIRECT("C"&MIN(IF(C10:$C$750<>"",ROW(C10:$C$750))))
　　　　　　これは配列数式なので、Ctrl+Shift+Enterで登録します。

F10　に　=IF(A9<>0,A9,F9)

G10　に　=INDIRECT("A"&MIN(IF($C10:C$750<>"",ROW($C10:C$750))))
　　　　　　これは配列数式なので、Ctrl+Shift+Enterで登録します。

H10　に　=IF(A10<>0,C10,(E10-D10)/(G10-F10)+H9)

D10:H10 をコピーして、D11:D750 に貼り付けます。
これでA、C列に入力があれば計算されます。A、C列に入力すれば再計算されます。

算式は少なくできると思いますが、ますます分かりづらくなるので分割しています。
見苦しければ、D～G列を非表示にしてしまいます。補完結果はH列に出ます。

ご参考に。

補足

nishi6様

教えていただきありがとうございます。
早速指示どおりに、セルに数式をインプットしました。
Ｄ10～Ｈ10に、nishi6様の回答ページの数式をコピーして各セルに貼り付けました。ただし、Ｅ10とＧ10は　Ctrl+Shift+Enter　で登録したつもりです。

1行下にでる数値は以下のとおりです。
30 100 9 22 0　となり、Ｈ列がゼロとなりました。
なお、（セル；Ｅ11）と（セル；Ｇ11）のセル左上にエラーマークが付いて(ＸＰですので)、内容は、『数式は隣接したセルを使用しません』と出ています。

私のセルへの貼り付け、または登録ミスでしょうか？

確認事項；Ａ10＝9，Ｃ10＝30；Ａ23＝22，Ｃ23＝100です。
Ａ列は上から750までの数値が入っており、Ａ751＝750、Ｃ751には3000と数値を
入れてあります。

お忙しいと思いますが、よろしくご指導ください。

回答No.2

madman

たとえば、
D10に=(C23-C10)/(A23-A10)と記述し、
各セルに=C10+D$10と書いて各セルにコピーすればいかがですか？
D列に計算させる式もコピーすれば変更はいらないでしょう。（ただし、区間の幅（S1とS2の幅とS2とS3の幅が異なる場合は修正がいりますが。）
S2からS3の作業時も、S2の次のデータ位置に=C24+D$24と記述してからコピーしましょう。

補足

madman さま

教えていただいたmadmanさまの方法（Ｄ10に計算式を書いてから
各セルにコピーをする方が楽でした。

しかし、区間の幅が毎回同一でないため（S1とS2の幅とS2とS3の幅が異なる）
頭が痛いのです。

回答No.1

Spur

この説明から何をしたいのかがまったく見えてきません。
結局どうなれば良いのでしょうね？

でも、あるセルに式を入れて、それを別のセルにコピーした時に、計算式中にある計算対象のセル番号を修正するということであれば、コピーすれば自動的に変わりますけど？
でも、そんな式をいれているのですは？
なぜ名前を使わないのですか？
セルに範囲で名前をつけて、それで計算したほうが楽ではないですか？
例えば、
=($C$23-$C$10)/($A$23-$A$10)+C10
ならば、
=(データ列１-データ列２)/(データ列３-データ列４)+補間値

などとすれば式自体もわかりやすくなると思いますけど？
でも、何をしたいのかわかりませんので、まったく的外れかもしれませんね。
質問のポイントが分からないんですよ。
何を計算したいのか（それは文字で説明されても知らない人には理解できない）ではなく、質問のポイントを書かれた方が良いですよ。

補足

Spur様
ご連絡とご指摘ありがとうございます。
私の説明が十分でなく、質問のポイントがご理解できないとのことで
すみませんでした。具体的には以下のようなことです。
1月1日の9：00に数値『30』が確認できてます。
次に確認できたのは1月1日の22：00で『100』でした。
そこで、当日の10時から21時までの各時間毎の数値を、等しい間隔(差)で
埋める。という作業です。
差：100-30＝70、70を13時間(22時-9時)で割った5.38を時間ごとに加えながら、
10時のセルは35.38、11時のセルは40.77、、、、21時のセルは94.62としたいのです。

データは一定の時間間隔ではなく、3時間経過後に確認されたものもあり、100時間以上経過後確認された場合もあります。

ところで、
セルに範囲で名前をつけて、それで計算したほうが楽ではないですか？
=(データ列１-データ列２)/(データ列３-データ列４)+補間値
今回の作業で以上の２行が必要な時には、どの様にするのでしょうか？
お時間が許せば教えてください。