文字と数字が混じってセルが不連続の数字を連番

質問

例えば、A1セルに「14A 001」 A10セルに「14A002」 と入力されており、また次のシートのA1セルに「14A003」 A10セルに「14A004」と続いているブックがあります。

これを、例えば最初のシートの「14A 001」 を「14A 051」 に変更すると、以下「14A 052」と自動で変わるようにしたいのですが、どうすれば実現できるでしょうか？

ただし、頭の3ケタの14Aも年や月が変われば変更されます。15Aや15Bや151など 様々に変わるので、セルの書式設定は使えないのではないかと思います。

以上の条件でやり方をご教授ください。すいませんがよろしくお願いいたします。

回答No.4

keithin · ベストアンサー

＞sheet名を簡単に変える技とか何かありますか？

「簡単」な技：
ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

public function GetLeft(byval Target as excel.range) as object
set getleft = application.caller.parent.previous.range(target.address)
end function

ファイルメニューから終了してエクセルに戻る

次のシートのＡ１セルに
=LEFT(GetLeft(A1),3)&TEXT(RIGHT(GetLeft(A1),3)+2,"000")
とこれをコピー貼り付けて記入する。
更に次以降のシートの各Ａ１にも全く同じ式を記入する。

＃参考
どこかのセルに
=GetLeft(A1)
と記入すると、左隣のシートの指定のセル番地の値を計算します。
言わずもがなですが「左隣の存在しない」一番左端のシートでこれを使うと、当然ながらエラーになります。

＃参考
エクセルには「シート番号を計算する関数」なんてものはありませんが、回答しているような、マクロを使った「ユーザー定義関数」を仕込んで利用する事はできます。時々自分で仕込んだのを忘れて「ほらできますよ」とか紹介しちゃう、うっかりさんもいらっしゃいます。

＃「ふつう」の方法：
ブックを一度必ず保存する
シート名を必ずSheet1、Sheet2、Sheet3…という名前をつける事を約束する
かつシート名の番号の順に、左からキチンと並べることを約束する

次のシートのＡ１セルは
=LEFT(INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,9)-1&"!A1"),3)&TEXT(RIGHT(INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,9)-1&"!A1"),3)+2,"000")
とし、シート３以降も同じにする。

＃注意
こういうエクセルで「ふつう」にできる方法は、「左隣のシート」を取ってくる「のではない」事を忘れないよう、気を付けてください。
あくまでも「シート２の『前』はシート１である」「シート３の『前』はシート２である」という、シートの名前付けの規則によって「順番」が担保されている事に留意します。

お礼

VBAと普通のやり方両方確認してみました。
VBAで作った関数の隣のsheetを参照するやり方は、制限が少なく良いと思いました。他の事にも応用できそうです。
通常の関数を使うやり方は、制限があるのとかなり複雑な関数になっていて難しく思いました。他の方の回答で2013だとシンプルに作れ応用もできそうなので、そちらを使うと思います。
それ以前ならVBAを使うやり方が、他の事にも応用でき良いと思いました。
どうもありがとうございました。

回答No.5

bunjii

>試してみました。#NAME?のエラーがかえってきました。
申し訳ありません。
Excelのバージョンによっては未定義の関数でした。
最新版のExcel 2013から組み込まれていますのでExcel 2010以前のバージョンでは#NAMEのエラーになるようです。
代案としてはシートの順を考慮して追い番号の加算値を各シート毎に修正するかシート名をシート毎に修正してください。

お礼

了解です。
2013で確認しました。きれいに表示されました。
2013の時は、これが手軽でいいと思いました。
ありがとうございます。

回答No.3

bunjii

元になるシート名をSheet1とし、順にSheet2、Sheet3、・・・・のように増えていくと仮定します。
元のシートのA10へは次の数式で良いと思います。
=LEFT(A1,3)&TEXT(RIGHT(A1,3)+1,"000")
Sheet2のA1セルに次の数式を設定します。
=LEFT(Sheet1!A1,3)&TEXT(RIGHT(Sheet1!A1,3)+(SHEET()-1)*2,"000")の
この式をA10セルにコピーすればSheet2の設定は完了です。
Sheet3以降はSheet2のA1セルをA1セルとA10セルへコピーすれば良いでしょう。

Sheet1のA1セルの文字列を修正すると全てのセルが更新されます。
SHEET関数はシート名を指定しないときその数式が入力されているシートのタブ（インデックス）が左から何番目にあるかを返します。
SHEET関数の性質を利用するとシート間の連番の算出に便利です。

補足

試してみました。#NAME?のエラーがかえってきました。
fxの所で関数の中を見てみると、関数の引数がTEXTで、値に、「　RIGHT(Sheet1!A1,3)+(SHEET()-1)*2　」が入力されていますが、そこが。#NAMEの原因みたいです。RIGHT(Sheet1!A1,3)の所は問題ないようなので、SHEET関数が問題のような気がします。

現在の状況は左から2番目にあるsheet2のA1セルに

=LEFT(Sheet1!A1,3)&TEXT(RIGHT(Sheet1!A1,3)+(SHEET()-1)*2,"000")

を入力した状態でエラーになりました。sheet1のA1セルには14A001と入力してあり、A10セルも関数入力で14A002が取得できています。単純に
=LEFT(Sheet1!A1,3)&TEXT(RIGHT(Sheet1!A1,3)+2,"000")
だと問題なく取得できます。
すいません、教えていただいた関数の使い方で何かエラーにつながる問題はないでしょうか？
よろしくお願いいたします。

回答No.2

mt2008

A1セルに「14A001」と入力されているシートがSheet1、次のシートがSheet2とします。

Sheet2のA1セルには↓
=LEFT(Sheet1!A1,3)&TEXT(RIGHT(Sheet1!A1,3)+2,"000")

Sheet1とSheet2のA10セルには↓
=LEFT(A1,3)&TEXT(RIGHT(A1,3)+1,"000")

こういう事でしょうか

お礼

実現できました。ありがとうございます。

回答No.1

keithin

各シートのＡ１０セルには
=LEFT(A1,3)&TEXT(RIGHT(A1,3)+1,"000")
と入れておきます

次のシートのＡ１セルには
=LEFT(ひとつ前のシート!A1,3)&TEXT(RIGHT(ひとつ前のシート!A1,3)+2,"000")
とでも入れておけばいいですね。

補足

うまくできました。ありがとうございます。ただ、sheet数が多い時にA1セルに入れるsheet名を簡単に変える技とか何かありますか？
なければ、とりあえず実現できたのでこれで問題ないです。