りんご 10
ばなな 20
みかん 30
りんご 11
ばなな 21
りんご 12
あんまりスマートではないですが、簡単な方法を一つ。
A列に果物名、B列に数字が入っているとして、
A列に空の列を1つ挿入し、
A1=B1&COUNTIF($B$1:B1,B1)
と記入して以下フィルコピーします。
A | B | C |
---|---|---|
りんご1 | りんご | 10 |
ばなな1 | ばなな | 20 |
みかん1 | みかん | 30 |
りんご2 | りんご | 11 |
ばなな2 | ばなな | 21 |
りんご3 | りんご | 12 |
すると↑のようになりますので、
数値を出したいセルに
=VLOOKUP("りんご3",$A$1:$C$6,3,0)
と記入してエンターで「12」が得られます。
=9+countif(A1:A6,"りんご")
これでできます。
A列に果物の名前
B列に数字で、範囲はA1から該当セルのとなりまで出できます。
3つ目のりんごは12とは限りませんのでだめですね、、、
あんまりスマートではないですが、簡単な方法を一つ。
A列に果物名、B列に数字が入っているとして、
A列に空の列を1つ挿入し、
A1=B1&COUNTIF($B$1:B1,B1)
と記入して以下フィルコピーします。
A | B | C |
---|---|---|
りんご1 | りんご | 10 |
ばなな1 | ばなな | 20 |
みかん1 | みかん | 30 |
りんご2 | りんご | 11 |
ばなな2 | ばなな | 21 |
りんご3 | りんご | 12 |
すると↑のようになりますので、
数値を出したいセルに
=VLOOKUP("りんご3",$A$1:$C$6,3,0)
と記入してエンターで「12」が得られます。
そうですね。最悪、この方法ならいけますね!もっとスマートは方法があればいいのですが、、、
標準の関数では2の方の様に作業用のシートを使用しないと難しいと思います。(2の方の方法でもりんごの数値が昇順に並んでいるという条件が必要ですが・・・)
いっそのことVBAでマクロを組んでみてはいかがでしょうか?
この場合なら
Public Function GetMaxValue(rg As Range, fruits As String) Dim row As Long Dim max As Long Dim item As String Dim rw As Range max = 0 For Each rw In rg.Rows If rw.Cells(1, 1) = fruits Then If Val(rw.Cells(1, 2)) > max Then max = Val(rw.Cells(1, 2)) End If End If Next GetMaxValue = max End Function |
このような関数を作り、EXCELのシートでは
=GetMaxValue(A1:B6,"りんご")
とすればご希望の動作になると思います。
(A1~B6までにご質問のデータが入っているものと仮想しています)
VBAを使えばいかようにもできますね、、、
今回もユーザ定義関数を使ってみます。
=ExecSQL(SQL文,何番目)
という関数を作ってみます。
以下、サンプルです。
1.ユーザ定義関数の作成
VBAのエディタを起動し、標準モジュールを追加。
以下のコードを記述してください。
Public Function ExecSQL(ByVal sql As String, ByVal no As Integer) As Variant
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xl_file As String
Dim cnt As Integer
xl_file = ThisWorkbook.FullName '他のブックを指定しても良し
Set cn = New ADODB.Connection
cn.Provider = "MSDASQL"
cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & xl_file & "; ReadOnly=False;"
cn.Open
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenStatic
cnt = 0
ExecSQL = Empty
Do Until rs.EOF
cnt = cnt + 1
If cnt = no Then
ExecSQL = rs(0).Value
Exit Do
End If
rs.MoveNext
Loop
rs.Close
cn.Close
End Function
2.Sheet1 にデータベースを作成
| A | B
--------------------
1|名称 | 数値
2|りんご | 10
3|ばなな | 20
4|みかん | 30
5|りんご | 11
6|ばなな | 21
7|りんご | 12
3.今度はSheet2のA1セルに次の数式を入力します。。
=execsql("Select 数値 from [Sheet1$] Where 名称 = 'りんご'",3)
セルA1に3番目のりんごの、12が表示されます。
良いのですが、今回はちょっとVBAを使いたくなかったもので、、、
"3番目"が変数でなければ、
=INDIRECT("B"&MATCH("りんご",INDIRECT("A"&MATCH("りんご",INDIRECT("A"&MATCH("りんご",A1:A100)+1&":A100"))+1&":A100"),0))
というややこしい関数を作りました。
データが100個以内の場合です。
X番目のときは、適当に2行目の A1:A100 の部分に
INDIRECT("A"&MATCH("りんご",A1:A100)+1&":A100"))+1&":A100")
を埋め込めば何とかなると思います。
りんごを3つ書かなきゃいけないのが問題ですね、、、
そうですね。最悪、この方法ならいけますね!もっとスマートは方法があればいいのですが、、、