Excel（エクセル） 同じ列の空白でないセルの数を数える際、数えるセル範囲を流動的にしたい

質問

S,A,B,C,Dと評価をして、それぞれ2,1,0,-1,-2という点数にします。
その点数の合計を評価と同じ列に表示されるようにしたいのです。
以下に具体的な例をあげます。

[B3]セル
=SUM(COUNTIF(B$5:B$11,"=S")*2,COUNTIF(B$5:B$11,"=A")*1,COUNTIF(B$5:B$11,"=B")*0,COUNTIF(B$5:B$11,"=C")*-1,COUNTIF(B$5:B$11,"=D")*-2)

[B5]～[B11]セル
評価（S,A～D）を入力

ここまではいいのですが、したいのはB12セル以降に新しく値を入れたら、自動的にB12セル以降の点数も加えてB3セルに表示されるようにしたいのです。B3セルに「B$5:B$11」の「11」代わりにもっと大きな値を入れれば可能なのですが、それはしたくありません。また名前をつけてもいいのですが、オートフィル機能を利用してC列以降にも数式を入れたいため、どうしていいかわかりません。
どなたか解決策がお分かりになられる方がおられましたら、どうかお知恵を拝借願えないでしょうか？

※管理者の方へ
訂正して再投稿ました。前回の書き込みは削除していただけると幸いです。

回答No.7

imogasi

前の質問で考えてました。締め切られてしまったが。
列のデータの最終行を捕まえることは関数では案外難しい。最終行のデータはLOOKUP関数で捉えられますが、同じ値が悦内に別に有るかも知れず、最終行が捉えにくい。VBAではコードが常套かしていてたやすい。
それで一例ですが、ユーザー関数を作る。
標準モジュールに
Function wgt(A)
d = Cells(65536, A.Column).End(xlUp).Row
For i = 2 To d
If Cells(i, A.Column) = "" Then
Else
s = s + Application.WorksheetFunction.VLookup(Cells(i, A.Column), Range("E1:F5"), 2, False)
End If
Next i
wgt = s
End Function
シートのA1に
=wgt(A1)と入れる。
またE1:F5に対応表を作る
今回の質問では変わったらしいが。
a 2
b 1
c 0
d -1
e -2
--
S
S とは何点何点ですか？スペース？
＞S,A,B,C,Dと評価をして、それぞれ2,1,0,-1,-2という点数にします。
前の質問と変わったね。
E,F列をそのように組み替えてください。
出来るだけ前の質問で、捕足で修正し、続けてほしかった。
なお対応表はVBAでなら何とでもなる（プログラム内に入れるとか）
VLOOKUPの第2引数も配列化できるかもしれない。VLOOKUPを使わなくても出来る。

回答No.6

zap35

先ほど解答した者です。
＞前回の書き込みは削除していただけると幸いです。
とは随分ですね。データと同じ列で集計したいならこんな式でも出来ますよ。達人maron--5さんの回答とややかぶりますが、ごめんなさい

　=SUMPRODUCT((OFFSET(B$5,0,0,COUNTA(B$5:B$65536),1)={"A","B","C","D","E"})*({2,1,0,-1,-2}))

回答No.5

tom04

No.1です。
たびたびごめんなさい。

cpdqp454様が使っていらっしゃる数式もすでに関数ですが・・・

もっと簡単な関数という事でしたら、
すでに他の方の良い方法が出ていると思います。
私もそれ以上の良い方法はちょっと思いつきません。

顔を出したついでに・・・と言っては失礼なのですが、

maron--5様の数式を利用させていただいて、
AVEの行のB4セルの数式を書いておきます。
すでに実行されていると思いますが、余計なお世話として

B4セルを
=B3/COUNTA(B5:B100)
として列方向にオートフィルでコピー！
今回もエラー処理はしていません
（分母が「0」の場合はエラーになるはずです）

どうも何度も失礼しました。m(__)m

回答No.4

某HN クロメート(Chromate)（@CoalTar）

B列のどこかのセルを選択して[Ctrl]+[F3]名前の定義
名前 : 数
参照範囲 : =COUNT($A:$A)+4
名前 : 範囲
参照範囲 : =B$5:INDEX(B:B,数)
B3セルに
=SUMPRODUCT((範囲={"S","A","C","D"})*{2,1,-1,-2})
右へオートフィル

回答No.3

maron--5

◆これでいかがでしょう
>わりにもっと大きな値を入れれば可能なのですが、それはしたくありません
★ご希望ではないかもしれませんが
B3=SUM(COUNTIF(B5:B100,{"S","A","B","C","D"})*{2,1,0,-1,-2})
★下の式でも、COUNTA(B5:B100))とすると同じように思います
B3=SUM(COUNTIF(OFFSET(B5,,,COUNTA(B5:B100)),{"S","A","B","C","D"})*{2,1,0,-1,-2})
★右にコピー

回答No.2

kmetu

> B3セルに「B$5:B$11」の「11」代わりにもっと大きな値を入れれば可能なのです> が、それはしたくありません

なぜしたくないのでしょうか？
余分なセルの指定をしたくないと言うことでしたら
A:A指定でも駄目と言うことになると思いますが…

最後の行を求めるにしても
=MAX(INDEX((B1:B100<>"")*ROW(B1:B100),0))
などのようにセル範囲に値が入ると思われる範囲を指定しなければいけませんし…

回答No.1

tom04

こんばんは！
一例ですが・・・

まず、メニュー　→　ツール　→　オプション　→　「計算方法」タブで「反復計算」にチェックを入れておきます。

そして、B3セルに
=SUM(COUNTIF(B:B,"S")*2,COUNTIF(B:B,"A")*1,COUNTIF(B:B,"B")*0,COUNTIF(B:B,"C")*(-1),COUNTIF(B:B,"D")*(-2))
という計算式を入れて
列方向にオートフィルでコピーではどうでしょうか？
（エラー処理はしていません）

以上、当方使用のExcel2003での回答になります。

以上、参考になれば幸いです。m(__)m

お礼

目から鱗です。うまくいきました。
関数で処理しようとばかり頭がいってました。
ありがとうございました。
でもせっかくだから関数で処理する方法も知りたいと思います。
何かいい案ないでしょうか？