エクセル2007の行挿入の件で困っています。
行の挿入をすると式がずれてしまいます。
式はHの列の下記の式が入っています。
インターネットで検索をしてVBAに追加をして行挿入しても
式が反映されるようになりましたが行挿入すると式がずれて
しまいます。
どなたかお力をお貸しください。
H4=IF(B4="","",H3+D4-F4)
H5=IF(B5="","",H4+D5-F5)
H6=IF(B6="","",H5+D6-F6)
↓
H4=IF(B4="","",H3+D4-F4)
H5=IF(B5="","",H4+D5-F5)
H6=IF(B6="","",H4+D6-F6)←H4になって、H5にならない。
H7=IF(B7="","",H6+D7-F7)
VBAで設定した項目
------------------------------------------------------
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Rem 挿入行に数式のみコピー
Rem 変数宣言
Dim c As Range
Rem 対象範囲のセル数チェック
If Target.Columns.Count < 256 Then Exit Sub
Rem 対象範囲の列数をチェック
If Target.Rows.Count > 1 Then Exit Sub
Rem 対象範囲の各セルに対しループ
For Each c In Target
Rem 列番号が2及び4のとき一つ上の行の内容を複写
If c.Column = 2 Or c.Column = 4 Then c.Value = c.Offset(-1, 0).Value
Rem 上の行の内容が数式っぽいときはその内容を複写
If c.Offset(-1, 0).Formula Like "=*" Then
c.Offset(-1, 0).Resize(2, 1).Formula = c.Offset(-1, 0).Formula
End If
Next c
End Sub
------------------------------------------------------
A 回答 (2件)
- 最新から表示
- 回答順に表示
No.2
- 回答日時:
やりたいのは「行挿入して、空白になった行に、自動的に式を入れたい」と言う事ですね?
H4=IF(B4="","",H3+D4-F4)
H5=IF(B5="","",H4+D5-F5)
H6=IF(B6="","",H5+D6-F6)
となっている時に、4行目と5行目の間に1行挿入すると
H4=IF(B4="","",H3+D4-F4)
空行空行空行空行空行空行
H6=IF(B6="","",H4+D5-F5)
H7=IF(B7="","",H6+D7-F7)
と言う状態になります。
これは「列挿入や行挿入をしても、セル参照の依存関係は変化しない」ようになっているからです。
H4を参照している式は、何をどう弄り回しても「元のH4だったセルを参照する」のです。
これを回避して「物理的に1行上を見に行く」には、式そのものを変えないといけません。
式そのものを変えて
H4=IF(B4="","",OFFSET(H4,-1,0)+D4-F4)
H5=IF(B5="","",OFFSET(H5,-1,0)+D5-F5)
H6=IF(B6="","",OFFSET(H6,-1,0)+D6-F6)
とすれば、行挿入しても
H4=IF(B4="","",OFFSET(H4,-1,0)+D4-F4)
空行空行空行空行空行空行空行空行空行
H6=IF(B6="","",OFFSET(H6,-1,0)+D6-F6)
H7=IF(B7="","",OFFSET(H7,-1,0)+D7-F7)
となります。
この時、OFFSET(H6,-1,0)は「挿入された空行のセル」を参照します。H4は参照しません。
なお、マクロは単純に
Private Sub Worksheet_Change(ByVal Target As Range)
Rem 挿入行に数式のみコピー
Rem 変数宣言
Dim c As Range
Rem 対象範囲のセル数チェック
If Target.Columns.Count < 256 Then Exit Sub
Rem 対象範囲の列数をチェック
If Target.Rows.Count > 1 Then Exit Sub
Rem 先頭行だったら無視
If Target.Row = 1 Then Exit Sub
Rem 対象範囲の各セルに対しループ
For Each c In Target
If IsEmpty(c) Then c.Formula = c.Offset(-1, 0).Formula
Next c
End Sub
で構いません。
式じゃない値がセルに入っている場合、FormulaプロパティはValueプロパティと同じ動作をします。
つまり「式だろうが何だろうが、1行上のFormulaをコピーしてくれば良い」のです。
No.1
- 回答日時:
それ、根本的に間違ってないかい。
行挿入しても式が成り立つように関数式を作れば問題は解決すると思うんだ。
INDIRECT関数が見た目で分かりやすいと思うのでこれで説明してみます。
INDIRECT関数は文字列で与えられた引数を参照するセルに置き換える関数です。
具体的には
H4=IF(B4="","",H3+D4-F4)
↓
H4=IF(INDIRECT("B4")="","",INDIRECT("H3")+INDIRECT("D4")-INDIRECT("F4"))
こうなります。
この関数式はコピーをしたときに参照するセルが変わりません。
え?どういうこと?意味ないじゃん。それ。
…と思われるかもしれませんが、
ダブルクォーテーションで囲ったセルを示す文字列を、ちょっと変えるだけでうまく動作するようになります。
ROW関数はご存じですか。
指定したセルが何行目にあるのかを返してくれる関数です。
この関数の引数を指定せずに使用すると、関数式が入力された行番号が返ってくるんです。
H4=ROW()
とすると、4が返ってくると言うことです。
…コレ、使えますよね?
H4=IF(INDIRECT("B"&ROW())="","",INDIRECT("H"&ROW()-1)+INDIRECT("D"&ROW())-INDIRECT("F"&ROW()))
こうすればOK。
ポイントは2つ目の "H3" を返すために "H"&ROW()-1 としているところ。
ちょっと見づらい関数になりましたが、これでVBAを使わなくてもよくなります。
行挿入したときに数式が反映されなければ、数式を反映させたいセルを選択して
Ctrl + D
を押してください。
上のセルの内容をそっくりそのままコピーしてくれます。
※ INDIRECT関数以外にもOFFSET関数でも同様なことができますが、
元の関数式とちょっとかけ離れた式になるので、理解が難しいとか…。
でもそっちのほうがスッキリするんですけどね。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル1行おきのセルを隣の...
-
SUMIF関数で、「ブランク以外を...
-
エクセルで、指定の値よりも大...
-
自分の左隣のセル
-
文字列から英数字のみを抽出す...
-
条件付き書式の色付きセルのカ...
-
エクセルで特定のセル内にだけ...
-
EXCELでマイナス値の入ったセル...
-
EXCELのcountif関数での大文字...
-
同一セル内の重複文字を削除し...
-
エクセルで、A2のセルにA3...
-
エクセルでエンターを押すと任...
-
AVERAGE関数とINDIRECT関数を使...
-
excelで、空白を除いてデータを...
-
エクセルに入力後、別シートの...
-
エクセルで年月日から月日のみへ
-
セルを結合した時のエクセル集...
-
エクセルでの会社名の集計
-
エクセル関数/任意の桁数の数...
-
EXECL バーコード生成でBarCode...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SUMIF関数で、「ブランク以外を...
-
文字列から英数字のみを抽出す...
-
エクセル1行おきのセルを隣の...
-
自分の左隣のセル
-
エクセルで、指定の値よりも大...
-
同一セル内の重複文字を削除し...
-
エクセルで、A2のセルにA3...
-
セルを結合した時のエクセル集...
-
エクセルで数字の組み合わせを...
-
エクセルで特定のセル内にだけ...
-
EXCELでマイナス値の入ったセル...
-
条件付き書式の色付きセルのカ...
-
excelで、空白を除いてデータを...
-
EXCELのcountif関数での大文字...
-
エクセルでエンターを押すと任...
-
Excelで大量のセルに一気に関数...
-
エラー「#REF」の箇所を置き換...
-
【Excel】4つとばしで合計する方法
-
エクセルで年月日から月日のみへ
-
EXECL バーコード生成でBarCode...
おすすめ情報