Excel 参照数式に数字を足したい。

質問

＝シート1B50
＝シート1B42
＝シート1B5
↓同じように続く。
↓
上のように規則性がなく、参照を表示してる数式すべてに5を一括して足したいのですが、何か方法ありますか？

＝シート1B55
＝シート1B47
＝シート1B10
のように簡単にしたいです。

回答No.7

HohoPapa

=シート1B50
これがA3セルに埋まっている場合

=LEFT(FORMULATEXT(A3),7)&MID(FORMULATEXT(A3),8,LEN(FORMULATEXT(A3)))+5

の計算式によって、文字列の　=シート1!B55　を得ることができます。
このセルをコピーして、形式を選択して貼り付け、『値(V)』を選んでA3に貼り付けます。
その後、A3を選択し、F2キー、エンターキーを押すことで期待の結果になると思います。

回答No.6

imogasi

最終回答ではないですが、思うところを記します。参考に。
Excelの関数にはシート名（だけ）を取得する関数がない。
Cell関数のパラメータ指定に、"filename"があるが、ブック名が主で、フルパスネーム的に出てくる。
簡単な式にするには、余り役立たないと思う。
ーー
そこで（VBAだが）ユーザー関数を作ると、右端（この質問の場合「１B」のあとに続く）数字部分に、５を足した数字は（本件限りですが）
Function bng()
shn = ActiveSheet.Name
x = InStr(shn, "1B")
bng = Mid(shn, x + 3, Len(shn) - (x + 2)) + 5
End Function
で返る。
Sheet名が「１B55」の、セルA5に、=bng()　と入れると、結果は　60　が返ります。
こういうのを関数式の一部に組み入れる方法がある。「1B6」の場合は11。
ーーー
全体をVBAで処理する回答は、すでに出ており、こちらを勧めますが。
またVBAのコードをどこへ書き込むかも経験ないレベルなら、VBAそうの勉強は、総所要勉強時間は相当なものになるので、人間（質問者）が判断（どの部分か）と計算（＋５）をして、変更後のシート名部分などを手入力で、式を組み立てる方法で我慢するのが、ベストでしょう。本件では、無理をしない方がよい。

回答No.5

kkkkkm

A1から下に
55
51
85
とあるとして
B1に
シート1!B
と入力
C1に
=$B$1&A1
として下にコピー
C列をコピーしてそのまま値貼り付け
C列を
置換で
シート1
を
=シート1
に

回答No.4

kkkkkm

No2の補足です。
Selection
のままだと選択しているセルだけが対象になります。

補足

任意の数ですので、5でも10でも大丈夫です。すみません。
マクロありがとうございます。

もしくは、「＝シート1！B」のみを先頭に一括して追加できる方法があれば知りたいです。
別の場所で計算して値をコピーして、

55
51
85
↓
＝シート1！B55
＝シート1！B51
＝シート1！B85
のようにマクロを使わずシンプルなやり方があれば知りたいです。
よろしくお願いします。

回答No.3

kkkkkm

> マクロはどうやって始めればいいのでしょうか？使ったことなくてわかりません。

ALTとF11を同時に押すと
VBE(Visual Basic Editor/VBエディタ)の画面について
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_vbe.html
ここでの説明にある画面が開きます。
「下図は標準モジュールを挿入して、コードを入力している例です。」
のところです。
画像ではModule1を選択した状態になっていますが、開いたばかりだとModule1はありませんのでそこは無視してください。
左のプロジェクトエクスプローラーと書かれたところで、書き替えたい式があるシートを「ダブルクリック」して選択状態にしてください。
その後右にあるコードウィンドウと書かれた所に回答したコードをコピペしてください。
そのまま上の「実行」から実行してもいいですし、表示タブのマクロから実行してもいいです。

> 左列の参照場所に列は固定で行を一定の間隔(全て10プラス)足して、その右側の列に同じように参照したいのですが、伝わりますか？

わるいけど、よくわかりません。質問では5足すなのに10足すとか。
質問のように具体的に記載して下さい。

質問の状態ではないのですか？
＝シート1!B50
を
＝シート1!B55
にする。

とりあえずテストで、質問の状態の式をA列にA1からA10まで作成してNo.2のマクロを実行して5足されるかどうか確認してください。

それでよければ10足すのであれば

Offset(5, 0)の5を10にしてください。

回答No.2

kkkkkm

マクロでよろしければ以下で試してみてください。
選択したセルの式を変更します。
セルが決まっているのでしたら、たとえばA1からA10としたばあい
Selection
を
Range("A1:A10")
に変更してください。

Sub Test()
Dim c As Range
Dim cnt As Long

For Each c In Selection
If c.Value <> "" Then
cnt = InStrRev(c.Formula, "!")
c.Formula = Left(c.Formula, cnt) & Range(Mid(c.Formula, cnt + 1)).Offset(5, 0).Address(False, False)
End If
Next

End Sub

補足

左列の参照場所に列は固定で行を一定の間隔(全て10プラス)足して、その右側の列に同じように参照したいのですが、伝わりますか？

マクロはどうやって始めればいいのでしょうか？使ったことなくてわかりません。
すみません、よろしくお願いします。

回答No.1

watabe007

>＝シート1B50 ⇒ ＝シート1!B50 の間違いだとして

数式の書かれているセル 上5個を上方向にシフトで削除
移動した数式の書かれたセルをコピーして元の位置に貼り付け

補足

回答ありがとうございます。
!マーク抜けてました。
もう少し、操作方法をわかりやすく教えて頂けると、助かります。

少しやりたいことと、違うような気もします。
勘違いならすみません。