標記の作業をしておりますが、1年間に
1000回以上数式を入替えなければならないため、
賢いエクセルなら別な方法があるのかな?と
お尋ねします。
作業の内容;
1)ある時点;S1(○月○日○時)の数値が決定されてます。
2)その時点以降の時点;S2の数値があります。
3)S1とS2間の各時点(空白セル)に等しい差となる数値を入れ込み
1年間数値が連続している表をつくりたい。
私が作った関数の方法(原始的ですが)
A列に数値の1~750程度(1ヶ月×24時間分)を表示させる。
B列に年月日と時間を表す:(○月○日○時)
C列が決まっている数値が入る欄で、
例として、S1:1月10日9:00時の数値が30とする。
S2が:1月日22:00時の数値が100とする。
その間の12の空白セルに、数値を入れるため、
10時のセル(C11)に以下の式を作った。
=($C$23-$C$10)/($A$23-$A$10)+C10
で35.38を得、次セルは、40.77と差が5.38づつの数値。
これをC22までコピーして完成する。
さて、次の箇所(S2とS3間)では、
数式の中の$記号がある4ヶ所を入れ替え
なくてはならず、この作業を繰り返すのは気が遠くなります。
もっと簡単に作業ができる方法や数式をご存知でしたら、
教えていただけるようお願いします。
No.6ベストアンサー
- 回答日時:
補足欄を注意してみていませんでした。
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
- 回答日時:
算式は5種類しかありません。
補足によると、その内、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)
実際使っている補完式を少しアレンジしただけなので動くはずなのですが・・・
算式は5種類ですので確認してください。
Excelのバージョンが違うようですが、計算には関係してこないと思います。
最終行はA751のようなのでE10、G10の750を751にして下さい。修正しても登録はCtrl+Shift+Enterとします。
(最後のデータより1つ前までの計算には影響していないでしょう)
No.4
- 回答日時:
S1・S2の部分がどのように入力されているのか理解できないのですが・・・
要はA列に1年時刻が1時間単位で入力されており、
任意の時刻と数値を入力したら、その間の時刻に
数値を按分して増加させた値を求める方法と理解しました。
【入力用シート】シート名:Sheet1
___ A列______ B列 C列 D列
1行目 時刻______ 空白 値
2行目 2002/01/10 09:00 式2 30
3行目 2002/01/10 22:00 式2 100
4行目 2002/01/15 01:00 式2 200
・・・
n行目 2002/01/22 02:00 式2 300
★B列は式をセット後非表示にする
【表示用シート】シート名:Sheet2
___ A列______ B列
__1行目 時刻______ 値
__2行目 2002/01/01 00:00 x <==データなしのためXを表示
__3行目 2002/01/01 01:00 x
・・・
227行目 2002/01/10 09:00 30
228行目 2002/01/10 10:00 35.38461538
229行目 2002/01/10 11:00 40.76923077
・・・・・(5.38461538づつ増加)
239行目 2002/01/10 21:00 94.61538462
240行目 2002/01/10 22:00 100
241行目 2002/01/10 23:00 101.010101
242行目 2002/01/11 00:00 102.020202
・・・・・(1.01010101づつ増加)
338行目 2002/01/15 00:00 198.989899
339行目 2002/01/15 01:00 200
340行目 2002/01/15 02:00 × <==データなしのためXを表示
(以下750行目までデータなしで×をセット)
【設定】
●データ入力:Sheet2
1)1行目にタイトルを適宜入力
2)A列に 2002/1/10 9:00 の様に年月日、時刻を入力
3)B2セルに以下の式を入力してB3~Bnにコピー
=IF(A2="","",VALUE(TEXT(A2,"yyyy/mm/dd hh:mm")))
表示形式は任意設定してください
EXCELの場合時刻値は手入力で入力した場合と
数式などで計算した場合、表示上は同じでも
日付値(時刻値)として微妙な誤差が発生し
検索や比較でマッチしなくなる為に
A1の入力値から年月日・時間と取り出して再セットします
●表示シート:Sheet1
1)参照範囲の特定
A1セルに『検索範囲』と文言を入力
B1セルに="Sheet2!B2:B"&COUNT(Sheet2!B:B)+1 と入力
Sheet2のB列の日付けデータ入力範囲を自動的にセットします
これは、後述の計算式のMATCH関数で検索する範囲を示します
MATCH関数で範囲をSheet2!$B:$Bと指定する事もできますが
処理負荷が増大しますので、この方法をとります
2)最大日付の特定
A1セルに『最大日付』と文言を入力
B1セルに =MAX(Sheet2!A:A) と入力
これも、後述の計算式で個々に指定した場合の処理負荷を軽減する為です
3)年月日・時刻の入力
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の様に誤差が発生します
4)計算式の登録
計算式を簡略にする為に、以下の名前の定義を行います
a)名前:検索0 参照範囲:=MATCH(Sheet1!$A4,INDIRECT(Sheet1!$B$1),0)
**A列の日付けを入力シートの日付けと"同一値"で検索する式です
a)名前:検索1 参照範囲:=MATCH(Sheet1!$A4,INDIRECT(Sheet1!$B$1),1)
**A列の日付けを入力シートの日付けと"同一か内輪の値"で検索する式です
5)数値の計算
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
- 回答日時:
補完しているわけですね。
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様
教えていただきありがとうございます。
早速指示どおりに、セルに数式をインプットしました。
D10~H10に、nishi6様の回答ページの数式をコピーして各セルに貼り付けました。ただし、E10とG10は Ctrl+Shift+Enter で登録したつもりです。
1行下にでる数値は以下のとおりです。
301009220 となり、H列がゼロとなりました。
なお、(セル;E11)と(セル;G11)のセル左上にエラーマークが付いて(XPですので)、内容は、『数式は隣接したセルを使用しません』と出ています。
私のセルへの貼り付け、または登録ミスでしょうか?
確認事項;A10=9,C10=30;A23=22,C23=100です。
A列は上から750までの数値が入っており、A751=750、C751には3000と数値を
入れてあります。
お忙しいと思いますが、よろしくご指導ください。
No.2
- 回答日時:
たとえば、
D10に=(C23-C10)/(A23-A10)と記述し、
各セルに=C10+D$10と書いて各セルにコピーすればいかがですか?
D列に計算させる式もコピーすれば変更はいらないでしょう。(ただし、区間の幅(S1とS2の幅とS2とS3の幅が異なる場合は修正がいりますが。)
S2からS3の作業時も、S2の次のデータ位置に=C24+D$24と記述してからコピーしましょう。
この回答への補足
madman さま
教えていただいたmadmanさまの方法(D10に計算式を書いてから
各セルにコピーをする方が楽でした。
しかし、区間の幅が毎回同一でないため(S1とS2の幅とS2とS3の幅が異なる)
頭が痛いのです。
No.1
- 回答日時:
この説明から何をしたいのかがまったく見えてきません。
結局どうなれば良いのでしょうね?
でも、あるセルに式を入れて、それを別のセルにコピーした時に、計算式中にある計算対象のセル番号を修正するということであれば、コピーすれば自動的に変わりますけど?
でも、そんな式をいれているのですは?
なぜ名前を使わないのですか?
セルに範囲で名前をつけて、それで計算したほうが楽ではないですか?
例えば、
=($C$23-$C$10)/($A$23-$A$10)+C10
ならば、
=(データ列1-データ列2)/(データ列3-データ列4)+補間値
などとすれば式自体もわかりやすくなると思いますけど?
でも、何をしたいのかわかりませんので、まったく的外れかもしれませんね。
質問のポイントが分からないんですよ。
何を計算したいのか(それは文字で説明されても知らない人には理解できない)ではなく、質問のポイントを書かれた方が良いですよ。
この回答への補足
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時間以上経過後確認された場合もあります。
ところで、
セルに範囲で名前をつけて、それで計算したほうが楽ではないですか?
=(データ列1-データ列2)/(データ列3-データ列4)+補間値
今回の作業で以上の2行が必要な時には、どの様にするのでしょうか?
お時間が許せば教えてください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ExcelでASCを使って全角を半角...
-
作成した数式を値として表示し...
-
Excel関数について教えてくださ...
-
Excel関数について教えてくださ...
-
条件付き書式設定で罫線を引き...
-
エクセルのセル内に分数などの...
-
エクセルの質問です。 F列からL...
-
Microsoft 365Excelの見開きペ...
-
ワークシートに出現したこの画...
-
エクセルの文字が途中から消える
-
Excelの警告について
-
タイムスタンプとテキストから...
-
シートの情報を別のシートへま...
-
マクロの処理が遅くなった
-
エクセルの数式バーのフォント...
-
Excelでの文字色
-
エクセルデーターから必要な項...
-
Excelの数字の前に入っている空...
-
excel2003 マクロボタンが押せない
-
エクセルでファイルの最終更新...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報