(excel)特定の文字列間の数値のsumを出したい

質問

初心者のため表記が分かりにくいところあるかと存じます。どうかご了承下さい。

以下のようなテーブルがあります。

__|_ _ __A_ _ _　|_ _B_ _|_C_
1 | c43t0d0|1540 |
2 | c43t12d|1　　|
3 | c45t0d0|1540 |
4 | c45t12d|6　　|
5 | c49t0d0|209 　 |
6 | c48t0d0|224 　|
7 |tty　　　　|　　　|3520←★B1-B6の合計
8 | c43t0d0|127 　 |
9 | c43t0d7|127 　|
10| c43t12d|6　　　|
11| c45t1d1|127 　 |
12| c49t0d0|253 　 |
13| c48t0d0|253 　 |
14|tty　　　　|　　　|893←★B8-B13の合計
15| c43t0d0|16 　 |
16| c43t12d|1　　　|
17| c45t0d0|16 　 |
18| c45t12d|6 　 |
19|tty　　　　|　　　　|39←★B15-B18の合計
~~~~~~~~~~~~~~~~~~~~~~~~~
<条件>
列Aには不規則にttyという文字列が出現します。
列Aにはtty以外に、cで始まる不規則な文字列cXXXXXXが出現します。
ただし文字列cXXXXXXにttyという文字列が含まれることはありません。
文字列cXXXXXXの列Bには、ランダムな数字が出現します。
列Aがttyの場合、列Bは空欄です。

今回実行したいのは、ttyとttyの間の列Bの合計を、列Cに出力することです。
イメージでは上図のC列のようになります。

行数が少なければ全て手動で行うのですが、表が6万行近くあるため、どうにか自動的に処理したいと思っています。
マクロも数式も不慣れなので四苦八苦中です。
識者の皆様、どうかアドバイス賜りますようお願い申し上げます。

回答No.6

Cupper · ベストアンサー

＃５　Cupperです。

失礼しました。
Ｃ１セルには次の数式を入力する必要がありますね。
　=B1

Ｂ１セルの数値を足していませんでした。

お礼

Cupper様

ご回答ありがとうございます。
他にご回答いただいた中で、Cupper様の方法が一番処理が軽かったため、良回答差し上げました。
見やすくなるようにどうにか加工してみます。

回答No.5

Cupper

めんどくさがりの自分なら、Ｃ列にＢ列のデータを全て累積してしまいます。
ただし、Ａ列にttyと記入された行が出るまで。
ttyと記入された次の行からまた累積させるようにします。
Ｃ２セルから次のような数式を入れ下へコピーします。

　=IF(A1="tty",B2,C1+B2)

結果は見づらいと思いますが結果が得られるのであれば同じかな・・・と。
見た目を気にするのであれば、邪道ですがＡ列の行を参照して文字色を背景色と同じにする条件付書式を設定したり・・・。

お勧めはしません。
こんな方法（考え方）もあると参考まで。

回答No.4

zap35

C2セルに以下の式を貼り付けて下方向にコピーしてください

=IF(A2="tty",IF(COUNTIF(INDIRECT("A$1:A"&ROW()-1),"tty")=0,SUM(INDIRECT("B$1:B"&ROW()-1)),SUM(INDIRECT("B$1:B"&ROW()-1))-SUM(INDIRECT("B$1:B"&MAX(INDEX((A$1:A1="tty")*ROW(A$1:A1),))))),"")

１行目にコピーするとエラーになります。

お礼

zap35様

ご回答ありがとうございます。

アドバイス頂いたとおり入力したところ、私が質問で意図した通り出力されました。
しかし回答頂いておきながら図々しくて恐縮ですが、
6万行コピーすると処理が大変重くなってしまい、パソコンがハングしてしまったため、
同じ現象ながら早い時点でご回答いただいたtelescopeさんへポイント付与させていただきました。
申し訳ございません。

回答No.3

imogasi

確かにこういうニーズはありますね。しかし意外に難しいと思います。
とりあえずやってみたのは作業列を使う方法です。
下記例でＢ列の空白セルに合計数字を入れるのは、関数では絶対無理です。
ＶＢＡなら難なくできますが。
Ａ列、Ｂ列が現データです。
Ａ列　　Ｂ列　　Ｃ列（作業列）Ｄ列
w 1 1 1
e 2 1 2
r 3 1 3
tty 6
f 5 2 5
g 6 2 6
h 7 2 7
j 7 2 7
tty 25
r 7 3 7
t 8 3 8
s 9 3 9
d 0 3 0
f 9 3 9
tty 33
Ｃ列の式は
=IF(A1="tty","",COUNTIF($A$1:A1,"tty")+1)
意味は、第１行からその行までのｔｔｙの出現数に＋１をし多数をセットしてます。
Ｄ列の式は
=IF(A1="tty",SUMPRODUCT(($B$1:B1)*($C$1:C1=MAX($C$1:C1))),B1)
意味は、その行のＡ列がｔｔｙであれば、Ｃ列を注目して、その行までの最大値
（ブロックに振った番号で直前の番号と思えばよい）に等しい行だけ加算したもの
をそのセルにセットする。
Ａ列がｔｔｙで無ければＢ列の値をセットする。
作業列Ｃ列を使わない、多分配列数式を使うかと思うが、方法を誰か出してくれる
かもしれない

お礼

imogasi様

ご回答ありがとうございます。

アドバイス頂いたとおり入力したところ、私が質問で意図した通り出力されました。
しかし回答頂いておきながら図々しくて恐縮ですが、
6万行コピーすると処理が大変重くなってしまい、パソコンがハングしてしまったため、
同じ現象ながら早い時点でご回答いただいたtelescopeさんへポイント付与させていただきました。
申し訳ございません。

各式の意味も教えていただいたので、勉強になりました。
今後のエクセル作業で活かさせて頂きます。

回答No.2

Wendy02

こんにちは。

これは、マクロの定番があるようですが、数式でも同じです。ただ、数式は、全体が出来上がると、どのような数式であれ、重たくなってしまいます。数式が出来上がったものは、値コピーで、数式は定数にしたほうがよいと思います。なお、ご質問の「テーブル」という語が気になります。

私は、その部分だけを足すようなものにしましたので、補助列を使いました。

D1 は、0
D2 は、=COUNTIF($A$1:A1,"tty*")　←この式を必要なだけ下にコピー

C1 は、=IF(A1="tty",SUMIF(D:D,D1,B:B),"")　←この式も必要なだけコピー

COUNTIF($A$1:A1,"tty*") のttyに*がつくのは、もしも、その後に空白値が入った場合を考えています。そういうことがなければ、"tty" だけでもよいです。

お礼

Wendy02様

ご回答ありがとうございます。
テーブルという表現は不適切なのでしょうか。

アドバイス頂いたとおり入力したところ、私の意図したとおり出力されました。ありがとうございます。
しかし回答頂いておきながら図々しくて恐縮ですが、
6万行コピーすると処理が大変重くなってしまい、パソコンがハングしてしまったため、
同じ現象ながら早い時点でご回答いただいたtelescopeさんへポイント付与させていただきました。
申し訳ございません。

なおご指摘の通り、数式を大量コピーすると重たくなってしまいました。
Wendy02さんのご指摘がなければ、何故重くなったのか分からなかったと思います。
こちらについてもありがとうございました。

回答No.1

telescope

質問文の条件なら、簡単な式で出来そうに思います。

1行目にttyは無いと思いますので、
2行目に下記の式を入れて下までフィルします。

=IF(A2="tty",SUM($B$1:B1)-SUM($C$1:C1),"")

A列がｔｔｙの場合、B列1行上までの合計から、C列の1行上までの合計を引きます。
質問文を読み間違えていたらご容赦ください。

お礼

telescope様
迅速なご対応ありがとうございました。
早速試してみたところ、私が理想とする形に出力されました。

しかし回答いただいておきながら図々しくて恐縮ですが、
6万行フィルすると処理が大変重くなり、
計算完了までパソコンがハングしたため、10ptとさせていただきました。申し訳ございません。