Excel 結合＆検索を文字で

質問

【質問１】
　　　 Ａ列　　 Ｂ列
1行　A0001　　　1
2行　A0002　　　1
3行　A0003　　　2
4行　A0005　　　1

上記のようにA2を求めるのにA1とB1を足します。
A3を求めるのにA2とB2を足します。
ここで質問なのですがSUM関数や足し算の計算式を入力しても【#VALUE!】とでてしまいます。
文字と数字が結合している足し算はどのような数式を入れればよいのでしょうか。

【質問２】
尚、上記の表で足すにあたって「A1+B1」という表現ではなく「対象セルの1行前、2列右」といった文字で出来ないでしょうか？

＊以上わかりやすく説明していただけると嬉しいです。
　宜しくお願いします。

回答No.4

Masa2072 · ベストアンサー

No.1です質問２を忘れていました。
OFFSET関数というのがあります。
対象セルC3の1行前、2列右であれば
=OFFSET(C3,-1,2,1,1)
となります。
引数のC3は基準となるセル、-1は行でマイナスなら上、プラスなら下、0なら同じ行です。
次の2は列、指定の方法は行と同じ、残りの1,1は範囲を表します。
セルが1つなら1,1、2行3列なら2,3とします、また単一セルを指定する場合、1,1を省略してもかまいません。
質問の意図とはちょっと違うかもしれませんが・・・

補足

ありがとう御座います！！
そのようなことも出来るのですね☆
わかりやすくて助かりましたー！！
で、これを【質問１】と組み合わせて使うことは出来ますか？

要するにやりたいことは、
数字＆文字が混合しているのを求るがOFFSET関数も一緒に入力することです。説明が下手ですみません・・・

回答No.6

nobita925

まず初めに、A0000が文字列でないと不味いですか？
数字で良ければ
(1)Ａ列の表示をユーザー定義でＡ0000とする
(2)A1には １ と入力→ A0001 と表示される
(3)A2は =A1+OFFSET(A2,-1,1,1,1)
(4)以下コピー

文字列でないと不味い場合は
(1)Ａ列の数字部の桁数を４と決める
(2)A1には A0001 と入力
(3)A2は =LEFT(A1,LEN(A1)-4)&TEXT(VALUE(RIGHT(A1,4))+OFFSET(A2,-1,1,1,1)),"0000")
(4)以下コピー

回答No.5

Masa2072

No1&4です。
回答に誤りがあり、すいません。
もう既に出来たのとのことですが
質問１の式が
="A"&TEXT(RIGHT(A1,4)+B1,"0000")となりますので
これにOFFSET関数を組み合わせることになると、基準をどこに置くかが問題になります。
A1を基準にするのであればA2の式は
="A"&TEXT(RIGHT(OFFSET(A1,0,0),4)+OFFSET(A1,0,1),"0000") となり
A2自身を基準にすれば
="A"&TEXT(RIGHT(OFFSET(A2,-1,0),4)+OFFSET(A2,-1,1),"0000")となります。
また基準となるセル番地を絶対参照（常にA1を基準にするとか）にすると、オートフィルでは望んだ結果になりませんのでOFFSETの行番号部分にROW関数を組み合わせると言った対策が必要になります。
いずれにしても無理にOFFSETを使うとかえって式が見難くなるように感じるのですが・・・

回答No.3

rin01

すみませ～ん。。。

これで良かったですね。。。

A2セルへ
=LEFT(A1)&TEXT(RIGHT(A1,4)*1)+B1,"0000")

。。。Ms.Rin～♪♪

お礼

コチラの数式では出来ませんでした。。。
もう一個の方で出来ましたのでありがとう御座いました！

回答No.2

rin01

こんばんは～♪

A2セルへ
=LEFT(A1)&TEXT(LOOKUP(10^7,RIGHT(A1,ROW($1:$5))*1)+B1,"0000")
下へコピーします。。

ご参考にどうぞ。。。
。。。Ms.Rin～♪♪

お礼

コチラの数式で出来ました！
数式の詳細がよくわかりませんが・・・。
ありがとう御座いました☆

回答No.1

Masa2072

A2に入力する式として
="A"&TEXT(SUM(B$1:B2),"0000")
あとは下にオートフィルでコピーします。

A0002の数字の部分を注目するとB列の先頭から現在の行までの合計値であることが分かります。
あとは、この数字を0002のように4桁になるよう0を付加するのにTEXT関数を使用します。
ポイントとしては合計を求める範囲の先頭が常にB1であるのでB$1（もしくは$B$1）と絶対参照にする必要があります。

補足

>A0002の数字の部分を注目するとB列の先頭から現在の行までの合計値であることが分かります。

そうではありません。
質問した表では3行目がそうとは言えません・・・。
数式も入れましたがデータがめちゃくちゃになります。
あと、合計を求める範囲の先頭はB1とは限りません。
相対アドレス指定な感じなのですが・・・。
他に方法はありませんかね・・・。
あと【質問２】の方も何かお分かりでしたら宜しくです(T_T)