エクセルVBA 変数Ａと変数Ｂの組み合わせに対応する値を返すコード

質問

ワークシート上に次の表があり，この表をもとにして，変数Ａ(0～4)と変数Ｂ（0～4）の組み合わせに対応するＣ列の値を返すコードは，どう書いたらよいでしょうか。
　例えば，変数Ａのセルに●（４），変数Ｂのセルに▲（０）と入力したら，値のセルに■（５）と表示させたいのです。
　どなたか教えていただけませんか。

Ａ列　　Ｂ列　　Ｃ列
　4　　　0　　　5
　3　　　1　　　4
　3　　　0　　　5
　2　　　2　　　3
　2　　　1　　　4
　2　　　0　　　4
　1　　　3　　　2
　1　　　2　　　2
　1　　　1　　　3
　1　　　0　　　3
　0　　　4　　　1
　0　　　3　　　1
　0　　　2　　　2
　0　　　1　　　3
　0　　　0　　　3

変数Ａ：●
変数Ｂ：▲
値：■

回答No.3

KenKen_SP · ベストアンサー

こんにちは。KenKen_SP です。

VBA を使わなくてもワークシート関数の SUMPRODUCT で十分な気がしますが。
Sheet1 の A1:C15 にデータがあるとしたらこんな感じで。

　　=SUMPRODUCT((A1:A15=4)*(B1:B15=0)*(C1:C15))

以下の VBA でも SUMPRODUCT 関数をそのまま使っているだけです。

Sub Sample()

　　Dim 変数A As Long
　　Dim 変数B As Long
　　Dim 値　　As Long

　　変数A = 4
　　変数B = 0

　　strArg1 = "(Sheet1!A1:A15=" & 変数A & ")"
　　strArg2 = "(Sheet1!B1:B15=" & 変数B & ")"
　　strArg3 = "(Sheet1!C1:C15)"

　　strFormula = "=SumProduct(" _
　　　　　　　 & strArg1 & "*" _
　　　　　　　 & strArg2 & "*" _
　　　　　　　 & strArg3 & ")"
　　値 = Evaluate(strFormula) '※
　　MsgBox 値

End Sub

ほかには、ループ処理で一行ずつ調べる方法、Match 関数で調べる方法など
色々考えられますね。

...この回答はトリッキーな部類かもしれません。

回答No.2

Wendy02

こんばんは。

なぜ、VBAで、これを解かなくてはならないのか、私には良く分かりません。
通常は、ワークシート関数で十分だと思います。

=SUMPRODUCT((A1:A15=G1)*(B1:B15=G2)*(C1:C15))

もし、VBAで行うなら、そのままワークシート 関数を使うのは、ちょっと気が引けます。
それで、例えば、解が2つ以上ある場合にも出せるように工夫を加えます。配列でも出せますが、その解の数だけ、高さを伸ばして、配列確定をしなければならないので、万民向きではありません。

以下は、名前は安直に、VLOOKUP とINDEX を合わせました。

　　F　　G
変数Ａ：　4
変数Ｂ：　0
値：　5

値のG3 の部分は、このようになります。

=VLOOKINDEX($G$1,$G$2,$A$1:$C$15,1,2,3,1))

VLOOKINDEX(第一検索値,第二検索値,検索領域,列1,列2,解を返す列3,[インデックス])

省略化
[インデックス]は、解が二つ以上ある場合に使用します。

エラー値は、インデックスが、解の数よりも大きい場合は、#NULL!
解が見つからない場合は、#N/A!

となります。その他のエラーは、#VALUE!

'標準モジュール
'----------------------------------------------------
'Option Explicit

Function VLOOKINDEX(arg1 As Variant, _
　　　　　　　　　　arg2 As Variant, _
　　　　　　　　　　Rng As Range, _
　　　　　　　　　　col1 As Integer, _
　　　　　　　　　　col2 As Integer, _
　　　　　　　　　　col3 As Integer, _
　　　　　　　　　　Optional indx As Variant) As Variant
'VLOOKINDEX(第一検索値,第二検索値,検索領域,列1,列2,解を返す列3,[インデックス])
　Dim ar() As Variant
　Dim res() As Variant
　Dim i As Long
　Dim j As Long
　Dim k As Long
　Dim n As Long

　'配列出力のための添え字
　If IsMissing(indx) Then
　　indx = 0
　ElseIf indx <= 0 Then
　　indx = 0
　Else
　　indx = CInt(indx) - 1
　End If

　ReDim ar(1, 0)
　For i = 1 To Rng.Rows.Count
　　If arg1 = Rng.Cells(i, col1).Value Then
　　　ReDim Preserve ar(1, j)
　　　ar(0, j) = Rng.Cells(i, col2).Value
　　　ar(1, j) = Rng.Cells(i, col3).Value
　　　j = j + 1
　　End If
　Next i
　For k = LBound(ar(), 2) To UBound(ar(), 2)
　　If arg2 = ar(0, k) Then
　　　ReDim Preserve res(n)
　　　res(n) = ar(1, k)
　　　n = n + 1
　　End If
　Next k
　On Error GoTo ErrHandler
　If UBound(res()) > -1 Then
　If UBound(res()) < indx Then VLOOKINDEX = CVErr(xlErrNull): Exit Function 'オバー
　　VLOOKINDEX = res(indx)
　End If
　Exit Function
ErrHandler:
　'解が見つからない
　VLOOKINDEX = CVErr(xlErrNA)
End Function

*後は、ご自身で検証してみてください。他にもいろいろ方法はあります。

お礼

ご丁寧にご教示いただき，本当にありがとうございます。ＶＢＡでやろうと思ったわけは，教えていただいたコードを応用して，大量の評定データと観点データの整合性をボタンひとつで検査できるようなプログラムを作りたかったからです。教えてくださったコードは自分にはあまりに高度ですが，地道に勉強させていただきます。本当にありがとうございました。

回答No.1

popesyu

これには何らからの（数学の公式として表せるような）法則性があるのでしょうか？　例えば　C=A*A-B*B　のような式。
ぱっと見では、AとBは足して4以下になる組み合わせしかないというのとA=BのときC=3になる,Cは5以下の値をとるしかわからなかったのですが。
もし公式があるのであればその公式にあわせてコードを書くだけですし、そういう公式がないのであれば全部書くしかないような（A=BのときC=3になるという部分しか省略できる部分が無いですが、もしそれだけしか省略できないのであれば省略する意味もあまりないですし）。
まぁSelect Caseで全部の組み合わせに足しての返り値をそれぞれ出すしかないかと思います。

補足

説明不足ですみません。これは学校の成績のつけ方なんですけど，知識不足で困っておりまして。。。
　Ａ列は観点Ａの数です。Ｂ列は観点Ｃの数。例えば，４項目ある観点がすべてＡだと，評定５。観点Ａ３つと観点Ｂがひとつ（観点Ｂは省略）でも，評定５となる。という意味です。。。

やはり，ひとつひとつのケースについて，全部記述するしか手はないですかね。