EXEL関数またはマクロでA,B,Cを数値に変換してその和を出すには

質問

EXEL関数またはマクロでA,B,Cを数値に変換してその和を出すには

こんにちは。EXEL関数をほとんど使ったことがない初心者です。
各人に対してA,B,C,Dの評価が複数ついている、下のようなデータがあります。

　　　　　1　2　3　4　5　6　7　合計
山田太郎　A　A　B　B　C　A　D　28
山田花子　B　C　B　C　D　A　C　24
.
.
.

これをA=5、B=4、C=3、D=2と変換し、さらにその数値の和を"合計"セルに表示させる方法を教えていただけないでしょうか。

自分なりにいろいろ調べてみたところ、IFやVLOOKUPを用いてA,B,C,Dを個々に数値に変換して、さらにそれを足すという方法まではたどり着きました。
しかし、変換と集計をいっぺんにできたらいいなと思っています。
それについても調べてみたのですが、なかなかよい方法がみつかりません。

方法や、考え方のヒントや、調べ方のヒントや、またはそんなことはできないというご指摘でもかまいません。
なにかアドバイスいただければ嬉しいです。
よろしくお願いします。

回答No.12

MackyNo1 · ベストアンサー

>実は「対象文字部分をセル参照できる」という意味がまだわかっていないので、後でちゃんと勉強しようと思いますが、それでも初心者の私にもどんどん応用して活用できそうです。

セル参照についてですが、たとえばAからＨまでの文字列に対して、それぞれ対応する数値があるような場合に、その一覧表を作っておきこのセル範囲を数式で参照することができます。

配列数式のメリットとしては、実際の文字データはＨまであるようなので、通常の数式を単純に＋でつなげると、数式が煩雑になるだけでなく、数式の文字列の長さの制限にかかる可能性があります。
また表のリストを作成しておけば、このリストの文字列と数字を変更するだけで（数式は変更せずに）簡単に計算内容を変更できます。

たとえばK1:K8セルにＡからＨまでの文字、L1:L8セルに対応する数字が入力されているなら、データの組み合わせが多くなっても、以下のような数式で合計点が表示できます。

=SUMPRODUCT(COUNTIF(B2:H2,$K$1:$K$8)*$L$1:$L$8)

お礼

再び回答をいただきありがとうございます。

「対象文字部分をセル参照できる」の意味がよくわかりました！
合計点を出してみてから、合計点の分布の調整をするために評価に対する数値を変更したりする事がよくありますので、この方法はニーズに非常にマッチしていて、まさにコレ！！という感じです。
数式もとても簡潔で初心者の私にわかりやすく、数式を間違えてしまう心配もなさそうです。

今後はこの手の仕事がとても楽しくできそうです。
どうもありがとうございました！

他の回答者の皆様もそれぞれお知恵をかしてくださり感謝しています。
いろいろな方法や考え方を知ることができ、とても勉強になりました。

自分で調べてるだけでは多分ここまでたどり着けなかったように思います。
ここで質問してみてよかったです。
ありがとうございました！！

回答No.11

imogasi

関数で
例データ
A-G列　記号英数字（小文字にしたが、質問のように、大文字でもよい。カタカナでも記号交じりでも良い）
a c b a 17
a a b b c d a 28
b c b c d a c 24
I列I2の式
=SUM(IF(A2:G2="",0,CHOOSE(FIND(A2:G2,"abcd"),5,4,3,2)))
と入れて、SHIFT+CTRL+ENTERを同時押しする。配列数式。
これは
・この質問では、aーｄの４種だが、１０種類ぐらいまでは式に書ける.（それ以上ダメと言うのでなく、長すぎてイヤになる。CHOOSE関数の引数は２９個までと言う制約は在る）
・5432でなくてもどんな数に設定しても良い。
・空白の列があっても差し支えない。くうはくがA-G列までの途中にあっても良い。
・ただしセルに入れるのは１文字や３文字と言った同じ長さである必要がある（３文字の場合は式が少し変わるが）
・配列数式なので、想像では、１０００行を超えると重いかもしれない。

お礼

回答をいただきましてありがとうございます。

また、新たな方法を教えていただき、皆さんの知識の豊富さに恐れ入っています。

このような式だと、1000行を超えると重くなるのですね。
1000行を超える場合はマクロを使うのがいいんですね。
それから、セルに入れるのは１文字や３文字と言った同じ長さである必要があるというのも、大変参考になりました。

なんだかいろいろとても勉強になりました。
当分、この手の仕事が楽しくなりそうです。
どうもありがとうございました！！

回答No.10

tom04

こんばんは！
すでに解決していると思いますので・・・
参考程度で目を通してみてください。

↓の画像のように評価と点数を表にしておいて、それを参照する方法です。

I2セルに
=IF(A2="","",SUMPRODUCT((COUNTIF(B2:H2,$K$2:$K$5)*($L$2:$L$5))))

という数式をいれオートフィルで下へコピーすると
画像のような感じになります。m(__)m

お礼

回答をいただきありがとうございます。

なんと画像までつけていただいて、昨日まで対応表を作って参照するのあたりがピンときていなかったのですが、画像をみてよくわかりました！

この方法だと、評価と点数が変化した場合にも対応が簡単で、より実用的ですね。
EXELを使う場合、このような作業が多いので、今後とても役に立ちそうです。

どうもありがとうございました！

回答No.9

Wendy02

#8で書いたものです。
>最初の数行を書き換えたら私でも応用できるでしょうか。

Const STRTXT As String = "A,B,C,D,E"
Const FIGTXT As String = "5,4,3,2,1"
Const mySTART As String = "B2" 'スタート地点

書き換えるのはここだけでよいはずです。Aが5, Bが4, Cが3　と対応しているはずです。
D まででしたら、

Const STRTXT As String = "A,B,C,D"
Const FIGTXT As String = "5,4,3,2" このように、,(コンマ）区切りにします。

問題がなければ、そのままでよいはずです。

B2 というのは、文字データの左上端の位置のことです。
ある程度、間違えても、出来るようになっているはずです。後は、右端、下端の部分は全部探します。

万が一、数字化されてしまっている時は、もう一度、数式だけ貼りつけなおします。

たぶん、今回のマクロは、私も数ヶ月も経つと読めなくなるような気がします。内容は、ややこしいです。

今回の、メインポイントはどこだというなら、
　rng.Replace strAr(n), strFig(n), , , False, False

ここの部分に付きます。つまり置換しています。後は、エラーを避けるための処理に過ぎません。
別に、マクロは使えなくてもよいけれども、VBAやマクロアレルギーだけはないようにしていればよいと思います。数式では、どうしてもいろんな制限があって出来ないことでも、マクロなら可能なものもいくつかあります。

今回は、置換さえしなければ、VBAでなくても可能ですが、何千行もなると、VBAでやったほうが楽です。

お礼

さらに詳しい回答をいただきありがとうございます。

書き換えるの最初の3行だけでよいのですね。
なんとか私にも使うことができそうな気がしてきました。

実はVBAやマクロアレルギーになりかけてたんですが(^^;)
マクロでないと出来ないことも沢山あるんですよね、なんとか仲良くしていきたいと思います。

どうもありがとうございました！

回答No.8

Wendy02

>変換と集計をいっぺんに
これは、マクロでないと無理ですね。

以下は、マクロは短いですが、なかなか複雑にできています。
理由は、マクロを二度行っても、データを壊さないこと。
計算は、数式で置かれます。数式は、文字列が混じってもエラーがでない。
マクロを途中で止めたり、保存しない限りは、データは戻せること。(GoBack)
大文字・小文字・全角・半角の区別はしません。

スタートB2 を決めてあげます。

'//
'設定
Const STRTXT As String = "A,B,C,D,E"
Const FIGTXT As String = "5,4,3,2,1"
Const mySTART As String = "B2" 'スタート地点
Private backRng As Variant
Sub ReplaceMark2Number()
　'文字を数字に変換
　Dim StartRng As Range
　Dim strAr As Variant, strFig As Variant
　Dim rng As Range, r As Range
　Dim i As Long, j As Long, n As Long, k As Long, m As Long
　strAr = Split(STRTXT, ",")
　strFig = Split(FIGTXT, ",")
　Set StartRng = Range(mySTART) 'スタートの場所
　If UBound(strAr) > UBound(strFig) Then MsgBox "最初のSTRTXTの登録文字数が足りません。", vbExclamation: Exit Sub
　i = Cells(Rows.Count, StartRng.Column).End(xlUp).Row
　j = Cells(StartRng.Row, Columns.Count).End(xlToLeft).Column
　If IsNumeric(Cells(StartRng.Row, j).Value) = False Then
　　k = 1
　ElseIf Application.Count(Range(StartRng, Cells(StartRng.Row, j))) = _
　　Range(StartRng, Cells(StartRng.Row, j)).Count _
　　And Cells(StartRng.Row, j).HasFormula = False Then
　　k = 1
　End If
　If Application.Count(Range(StartRng, Cells(StartRng.Row, j))) = _
　　Range(StartRng, Cells(StartRng.Row, j)).Count Then
　　m = -1
　End If
　Set rng = Range(StartRng, Cells(i, j))
　If m = 0 Then
　　backRng = rng.Value
　End If
　Application.ScreenUpdating = False
　For n = 0 To UBound(strAr)
　　rng.Replace strAr(n), strFig(n), , , False, False
　Next
　For Each r In rng.Rows
　　With r.Rows(1)
　　　.Cells(.Cells.Count).Offset(, k).FormulaLocal = "=SUM(" & .Resize(, .Cells.Count + k - 1).Address(0, 0) & ")"
　　End With
　Next
　Application.ScreenUpdating = True
　Set rng = Nothing
End Sub

Sub GoBack()
　'データを戻す
　If IsArray(backRng) Then
　　Range(mySTART).Resize(UBound(backRng, 1), UBound(backRng, 2)).Value = backRng
　Else
　　MsgBox "残念ながら戻せません", vbExclamation
　End If
End Sub

お礼

さっそく回答をいただきありがとうございます。

マクロを教えていただきありがとうございます！
マクロは初歩の初歩の簡単なのを、かなり以前に使ったことがあるだけで、私にはそこまで必要ないだろうと敬遠してしまっていましたが、使えるようになるときっと色んな事ができるんでしょうね。

これを機会にちょっと勉強してみたいと思います。
とりあえず教えていただいたマクロはちっともわかっていないのですが、最初の数行を書き換えたら私でも応用できるでしょうか。
時間のあるときにぜひ勉強してみます。

どうもありがとうございました！

回答No.7

nattocurry

Excelの初心者ということなので、比較的簡単なCOUNTIF関数を利用する#3の方法が良いでしょうね。
応用も利くと思うので良いでしょうね。

文章としても考えやすい、
Aの個数×5＋Bの個数×4＋Cの個数×3＋Dの個数×2＋Eの個数×1
を数式にしているだけですし。

データが無い場合に、空白ではなく0が表示されても良いのであれば、IF関数も必要ありませんよ。

お礼

さっそく回答をいただきありがとうございます。

＃３の方の方法を試してみたところ、初心者の私にも集計することができました！

"データが無い場合に、空白ではなく0が表示されても良いのであれば、IF関数も必要ありませんよ。"
こちらも教えていただいてありがとうございます。

EXELの初心者用のテキストや、ヘルプやネットで自分なりに調べてみた時は、なかなか方法を見つかれらなかったのですが、こちらで質問したらいろいろな方法を教えていただき、皆さんの知識になんだか感動してます。
私も皆さんのようにEXELを使いこなせるようになりたいです。

どうもありがとうございました！

回答No.6

MackyNo1

基本のＣＯＵＮＴＩＦ関数を使う場合、以下のような配列定数を使った数式を使用すると、対象文字部分をセル参照できるので応用範囲も広くわかりよい数式ではないかと思います。

=SUMPRODUCT(COUNTIF(B2:H2,{"A","B","C","D"})*{5,4,3,2})

お礼

さっそく回答いただきましてありがとうございます。

教えていただいた方法を試してみたところ、合計を出すことができました！
すばらしく簡潔でわかりやすい式を教えて下さってありがとうございます。

実は「対象文字部分をセル参照できる」という意味がまだわかっていないので、後でちゃんと勉強しようと思いますが、それでも初心者の私にもどんどん応用して活用できそうです。
仕事の能率が非常によくなりそうで、とても嬉しいです！

どうもありがとうございました！

回答No.5

KURUMITO

回答Ｎｏ3です。しばしば英字が半角か全角かの問題もあって、正確な値が出ないこともありますね。
その問題をなくした対応ができる式としては次のようにすることでしょう。

=SUMPRODUCT((ASC(A1:A1000)="A")*1)*5+SUMPRODUCT((ASC(A1:A1000)="B")*1)*4+SUMPRODUCT((ASC(A1:A1000)="C")*1)*3+SUMPRODUCT((ASC(A1:A1000)="D")*1)*2

お礼

再び回答をいただきありがとうございます。

半角、全角の問題までをも一気に解決できる方法があるのでですね。
なんだかEXELでなんでもできるような気がしてきました。

しかもこの式なら仕組みを理解できなくても、応用して活用することができそうです。
初心者の私にも応用できる方法を教えてくださってありがたいです。

今回は半角・全角の問題はなかったので、前の回答で目的を果たすことができましたが、また機会があるときにはぜひこの方法を使わせていただきたいです。
どうもありがとうございました！

回答No.4

web2525

=SUMPRODUCT(MATCH(B2:H2,{"","D","C","B","A"},0))
こんな感じでもできますね

お礼

さっそく回答いただきましてありがとうございます。

教えていただいた方法を試してみましたが、なんだかうまくゆかず...。
実際にはA～Hまであるのですが、私が式の仕組みを理解できていないので、応用できませんでした。

しかし、1つの目的に対していろんな方法があることがわかりました。
時間のあるときに勉強して、この方法も使いこなせるようになりたいです。
できれば皆さんのようにEXELの達人になりたいです。
そしたら仕事がもっと楽しくなりそうです！

どうもありがとうございました！

回答No.3

KURUMITO

例えばＢ1セルからＨ1セルにＡ，Ｂ，Ｃなどがあり、Ｉ1セルにその合計を表示させるのでしたらＩ1セルに次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",COUNTIF(B1:H1,"A")*5+COUNTIF(B1:H1,"B")*4+COUNTIF(B1:H1,"C")*3+COUNTIF(B1:H1,"D")*2)

お礼

さっそく回答いただきましてありがとうございます。

教えていただいた方法を試してみたところ、見事に合計がでました！
オートフィルドラッグして全ての行の合計がさーっと出てきたときには、なんだか感動してしまいました～。

式の仕組みも関数をよくわかっていない私にも理解しやすかったです。
他に○や×を数値化する部分もあったので、応用しやすかったです。
おかげさまで、この仕事にかける時間をぐっと減らすことができました。

他の方に教えていただいた方法も試してみようと思っていますが、とりあえずお礼を申し上げます。
どうもありがとうございました！

回答No.2

jcctaira

=SUMPRODUCT((B1:H1="D")*2+(B1:H1="C")*3+(B1:H1="B")*4+(B1:H1="A")*5)

または

=SUMPRODUCT(70-CODE(B2:H2))
注）"A"～"D" 以外の値が無いとした場合

お礼

さっそく回答いただきましてありがとうございます。

教えていただいた方法のうち、上のを試してみたところ、合計を出すことができました！

式の仕組みも関数をよくわかっていない私にも理解しやすかったです。
他に○や×を数値化する部分もあったので、応用しやすかったです。

下の式はまだ理解できていませんが、時間のあるときに調べてみようと思います。
目的に対する方法を、さっといくつも思い浮かぶのは、本当にすごいですね。
私もそうなりたいです。
そうなったらものすごく仕事ができる人になれそうな気がします！

他の方に教えていただいた方法も試してみようと思っていますが、とりあえずお礼を申し上げます。
どうもありがとうございました！