[[20040322111255]]　『カッコ付の数字の計算方法』（てちゃーはん）　ページの最後に飛ぶ

『カッコ付の数字の計算方法』（てちゃーはん）

Excell2000，WindowsMEです。

1 [ 3]

2 [ ]

3 [12]

4 [15]

例えば，上記のようにA1～A3を足してA4に計算結果を出したいのですが，
カッコ付だとどうもうまく計算できません。
どのようにすればカッコ付の数値をカッコ付で計算結果を出せるのでしょうか？
ご教授のほどよろしくお願いいたします。

 [] をセルに入力しないで、表示形式で設定しては如何でしょうか？

  (INA)

 文字列として入力しているのなら、

=SUMPRODUCT(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A3,"[]",0),"[",""),"]","")))

 しかし、お勧めはINAさんの書いているようなセルの表示形式の
ユーザー設定。
「"["#"]"」などとします。
セルに数値だけ入力し、カッコを追加して表示させるほうが
数式が単純になる分管理は楽になると思います。
(KAMIYA)

さっそく，表示形式のユーザー設定で「"["#"]"」としてみましたが，セルにはCOUNTIF関数を用いて結果を出すような数式を入れており，計算結果は出るんですが，[ ]が表示されません。数式を入れてる場合はこの方法は使えないんでしょうか？よろしくお願いします。
（てちゃーはん）

 数式が入っていても出来るはずですが・・・
"["G/標準"]" だとどうなりますか？
COUNTIFの数式は具体的にはどんな数式ですか？（ケン）

"["G/標準"]"でも変わらないみたいです。数式は，「=IF(COUNTIF(一覧!$J$2:$J$292,"い③A")=0," ",IF(COUNTIF(一覧!$J$2:$J$292,"い③A")<10," "&COUNTIF(一覧!$J$2:$J$292,"い③A"),COUNTIF(性質別一覧!$J$2:$J$292,"い③A")))」こんな感じです。数式に問題ありでしょうか？（てちゃーはん）

 " "&COUNTIF(一覧!$J$2:$J$292,"い③A")の部分は文字列扱いになっていますね。
"["@"]"としたら出来ますが、表示形式で[]を表示させている意味がありませんね。
数式の""&は、ない方がいいのでは？
そして表示形式を　"["?#"]"とします。（ケン）

 ケンさんと似ていますが、むしろ、数式を
=COUNTIF(一覧!$J$2:$J$292,"い③A")
だけにしてセルには数値を返すようにします。

 表示形式で
[=0]"[  ]";[<10]"[ "#"]";"["#"]";
であるように思います。
（カッコの中に必ず二桁・・・ですよね）
　
うまく計算できなかったのはCOUNTIFの結果が０や一桁の数値の
時に文字列としてセットしてしまい、SUM関数で計算から回避さ
れてしまうからです。０のときはいいのですが、一桁数値のときは
その分が計算から除外されてしまっていたはずです。
(KAMIYA)

 別シートへの参照があるのを見落としました。数式は

=IF(COUNTIF(一覧!$J$2:$J$292,"い③A")<10,COUNTIF(一覧!$J$2:$J$292,"い③A"),COUNTIF(性質別一覧!$J$2:$J$292,"い③A"))

 こんな感じで。
(KAMIYA)

KAMIYAさんの「別シートへの参照が･･･」の前の方法でうまくいった！と思ったんですが，追加の方法じゃないと間違ってくるんですか？（てちゃーはん）

 「一覧」「性質別一覧」という二つのシートがあるのでは？
(KAMIYA)

あっ･･･そこは私のミスで，「一覧」というシートのみです。（てちゃーはん）

 ならば、
=COUNTIF(一覧!$J$2:$J$292,"い③A")
でOKでしょう。
(KAMIYA)

表示形式のユーザー設定は今まで使ったことがなかったので，とても勉強になりました。最後に，カッコの中に必ず二桁の場合はわかったのですが，三桁の場合を教えていただけないでしょうか。（てちゃーはん）

 3桁だと、表示形式では対応が難しいかも。
表示形式では2条件とその他の表示形式を対応できますが、
3桁の場合は、「0の時、一桁のとき、二桁の時、三桁の時」
と4条件になってしまいます。
関数と表示形式の組み合せでやるしかなさそうです。
数式を下記に。

=IF(COUNTIF(一覧!$J$2:$J$292,"い③A")=0,0,REPT(" ",3-LEN(COUNTIF(一覧!$J$2:$J$292,"い③A")))&COUNTIF(一覧!$J$2:$J$292,"い③A"))

 COUNTIF関数が　0　の時は　0　、それより大きい時は
3桁になるように空白をセットし文字列にします。
表示形式は次のようにして、0　のときは"[   ]"、文字
列の時は"["@"]"。

"[ ]";"["@"]"

 集計の式は

=IF(SUM(TRIM(C1:C4)+0)=0,0,REPT(" ",3-LEN(SUM(TRIM(C1:C4)+0)))&SUM(TRIM(C1:C4)+0))

 と数式バーへ入力して　Ctrl+Shift+Enter　で配列数式として確定。
表示形式は前掲の「"[   ]";"["@"]"」と同じで良いでしょう。
（ただし、999　を超えると＃VALUEエラーが出ますが・・・）
(KAMIYA)

REPT関数，LEN関数，TRIM関数と今まで使ったことのない関数ばかりでたいへん勉強になります。ところで，集計の式のSUM(TRIM(C1:C4)+0)の部分がどうもよくわかりません。一応，そのままコピーし，作成しているものに合うように(C1:C4)の部分を(C1,C4)のように変えたら，計算してくれませんでした。ご指導をお願いします。あと，「Ctrl+Shift+Enter」は，数式を入れたあとに操作するだけで良いんでしょうか？（てちゃーはん）

 先に詫びを入れておきます。
=IF(SUM(TRIM・・・の式はこちらでテストしたままを掲載して
しまったので、参照はC1:C4　ではなくて、　てちゃーはんさんの
例にあわせるなら、A1:A3ですね。すみません。
　　
それから、配列数式の入力のときはEnterで確定するのではなくて、
Ctrl+Shift+Enterで確定するという意味です。数式を入力したあとに
Ctrl+Shift+Enterしても何も起こりません。うまく入力できていれば、

{=IF(SUM(TRIM(A1:A3)+0)=0,0,REPT(" ",3-LEN(SUM(TRIM(A1:A3)+0)))&SUM(TRIM(A1:A3)+0))}

 というように中カッコで数式が囲まれます。
　
また、「SUM(TRIM(C1,C4)+0)」のような入力はTRIM関数のほうが受け
付けてくれないので「引数が多すぎる」旨のエラーメッセージが表示さ
れます。C1、C4セルに範囲名をつけて代入しても確定はできますが、
#VALUEエラーになります。
連続した範囲であることを前提として設計したため、不連続範囲では
この式では無理があります。解決策は･･･うーん、ちょっとすぐには
思いつかない･･。ちょっと時間がほしいです。
　
SUM(TRIM(A1:A3)+0)についての解説を入れておきます。
TRIMは通常ひとつの文字列からスペースを取り除きます。
配列数式として扱うことで、セル範囲での文字列を扱うことができま
す。TRIM(A1:A3)は結果として文字列としての数字を返します。
文字列としての数値をSUMで数値として扱うために、+0して数値化し
てから集計します。
(KAMIYA)

いつも丁寧に答えていただきありがとうございます。不連続範囲では難しいんですね･･･何の考えもなく聞いてしまって，ご迷惑をかけているみたいで恐縮です。と言っても，私では解決できないので，気を長くしてお待ちしております。（てちゃーはん）

 お待たせしているみたいですが、なかなかいい案も浮かばないので、
とりあえず、てちゃーはんさんのシートレイアウトの概要を教えてください。
[123]、[ 25]となるようなセルを足し算すればよいのはわかりました。
これらのセル配置に何か特徴はありますか？
C1から始まり、C4、C7と3行おきであるとか、同じ行に集計対象を特定する
なにがしかの特徴あるデータが入っているとか・・・。
何か規則性があれば、集計できそうです。
(KAMIYA)

 KAMIYA師匠、毎度っ！
 ざっと見でなんか重要な事を見落としとるやもしれまへんけど、こんなんどうでっか？

 [Alt]+[F11]でVBEを開く
 「挿入」→「標準モジュール」を選択
 下のコードをコピペ
 [Alt]+[Q]でエクセルに戻る

 集計を出したいセルに[ ]の有る無しにかかわらず範囲を指定
 =tyahan(a1:a30)といった案配に入力
 どないでっか？あきまへんか？
 アカン場合はMojule１を選択して「Mojuleの解放」で綺麗サッパリ忘れられますワ。
 　　ほな...（弥太郎）
 '---------------------
 Function tyahan(ByVal adrs As Range)
    Dim tbl As Range
    Dim i As Integer

    Set tbl = adrs
    For i = 1 To tbl.Rows.Count
        If tbl.Cells(i, 1) <> "" And Left(tbl.Cells(i, 1), 1) = "[" Then
            data = tbl.Cells(i, 1)
            data = Right(data, Len(data) - 1)
            data = Val(data)
            tyahan = tyahan + data
        End If
        Next i
    tyahan = "[" & tyahan & "]"
 End Function

カッコ付のセルは１つおきで５つあり，それをまた１つ空けて小計とします。そして，また１つ空けたカッコ付のセルと小計をまた１つ開けたセルに合計としてます。こんな感じでわかりますか？？（てちゃーはん）

 先週末は終日勤務のためレスできませんでした。
すみませんでした。
下のような感じでしょうか？
     A     B
 1 	項目A
 2 品目1	[  1] 
 3	a 
 4 品目2	[   ]
 5	a 
 6 品目3	[123] 
 7	a 
 8 品目4	
 9	a 
10 品目5	[ 12] 
11 	
12 小計	[136] 
13 
14 その他	[ 12]
15
16 小計	[ 12] 
17
18 合計	[148]  
　
上記の表でA列に書かれた品目をカウントを行う場合は
B2へ

=IF(COUNTIF(一覧!$J$2:$J$292,B2)=0,0,REPT(" ",3-LEN(COUNTIF(一覧!$J$2:$J$292,B2)))&COUNTIF(一覧!$J$2:$J$292,B2))

 と入力して、B4,B6,B8,B10へコピー。
小計のB12へ

=REPT(" ",3-LEN(SUM(IF(MOD(ROW(B2:B11)+1,2)=1,IF(ISNUMBER(VALUE(TRIM(B2:B11))),VALUE(TRIM(B2:B11)))))))&SUM(IF(MOD(ROW(B2:B11)+1,2)=1,IF(ISNUMBER(VALUE(TRIM(B2:B11))),VALUE(TRIM(B2:B11)))))

 と数式バーへ入力して　Ctrl+Shift+Enter　で配列数式として確定。
表示形式は前掲の「"[   ]";"["@"]"」と同じで良いでしょう。
ただ、13行目以下の配置はこれでいいのかちょっと自信がないので、
まだ数式を用意してないんですが、もしかしたら、同じように一行おき
に5項目あって・・・という具合でしょうか？
(KAMIYA)

 いつもありがとうございます。
 しかし，２桁まではけっこう簡単なのに，これが３桁になるとかなり難しいですね。
 と言うか，さらにMOD，ROW，ISNUMBER，VALUE関数と正直なところ全然理解できません･･･

 ちなみに，13行目以下は，16,17行目はなく，小計とその他を足して，合計を出す感じです。

 こんな感じでしょうか。
13行目がどのような値が返っているかが不明ですが、
とりあえず=" 12"のように同様の文字列が返っているとして、
     A     B
 1 	項目A
 2 品目1	[  1] 
 3	a 
 4 品目2	[   ]
 5	a 
 6 品目3	[123] 
 7	a 
 8 品目4	
 9	a 
10 品目5	[ 12] 
11 	
12 小計	[136] 
13 その他	[ 12] 
14
15 合計	[148]
　
B15へ
=REPT(" ",3-LEN(VALUE(TRIM(B12))+VALUE(TRIM(B13))))&SUM(VALUE(TRIM(B12)),VALUE(TRIM(B13)))
これは普通にEnterで確定。

 >しかし，２桁まではけっこう簡単なのに，・・・
そうなんですよね。
[　]の間に「必ず3桁」、つまり、カッコの両端をそろえるというのが
ネックかもしれません。現実問題としては
「計算値を文字列に→文字列を計算してまた文字列に」
という過程が問題をややこしくしていますし、計算式も文字列を数値に
する過程が必要になるので、メンテナンスもそれだけしにくくなります。
そこで、レイアウトを変更できるのであれば、　[  1]　と表示してい
るひとつの列を3列で構成してみると、計算式自体はたいへんシンプルな
ものでまかなえるような気がします。下記では前述のB列の表示をB、C、
D列の3列を使って行います。 
　
     A   B C D
 1 	項目A
 2 品目1	[  1] 
 3	 aaa 
 4 品目2	[   ]
 5	 aaa 
 6 品目3	[123] 
 7	 aaa 
 8 品目4	
 9	 aaa 
10 品目5	[ 12] 
11 	 aaa
12 小計	[136] 
13 その他	[ 12] 
14
15 合計	[148]
　
つまり、B、D列では括弧だけの表示を、
C列では数値だけが返るような計算式を入力しておくだけで、小計の
セルはそれほど複雑でない計算式ですむはず。
(KAMIYA)

 確かに，最初は括弧は別のセルにしていたんですが，
 括弧だけでもけっこうセル幅を取るので，横幅が１ページに収まらなくなり，
 今回，括弧付での計算方法を質問した次第でした。（てちゃーはん）

 うーむ、やはりそういうことでしたか。今回はてちゃーはん
さんの質問の内容に沿って数式を組み立ててみた結果、
文字列を数値に直して計算する方法をご提案してみましたが、
やはり、計算は数値で行ったほうがやはり扱い勝手が良いで
すよね。とりあえず、私の提案した数式が間に合えばそれで
よいのですが、自分でもあまりわかりやすい数式だとは思え
ないので、レイアウトが変わったときなど変更に苦労するか
もしれません。
そのことを考えると、文字列にしてしまったセルをむりやり
合計するのではなく、（合計を含む）計算そのものは他のセ
ルでやらせておいて、セル参照で表示の面だけ最後に文字列
として整える方法が実は正解なのかもしれません。
(KAMIYA)

コメント返信：

[ 一覧(最新更新順) ]

[[20040322111255]] 『カッコ付の数字の計算方法』（てちゃーはん） ページの最後に飛ぶ

[[20040322111255]]　『カッコ付の数字の計算方法』（てちゃーはん）　ページの最後に飛ぶ