Excelの次の表で3つ目のりんごに該当する12が欲しいのですが、どの関数を使えば取得できますでしょうか?


りんご 10
ばなな 20
みかん 30
りんご 11
ばなな 21
りんご 12

回答の条件
  • 1人2回まで
  • 登録:
  • 終了:2006/08/05 09:54:28
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ベストアンサー

id:rikuzai No.2

回答回数1366ベストアンサー獲得回数141

ポイント43pt

あんまりスマートではないですが、簡単な方法を一つ。

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」が得られます。

id:simplestyle

そうですね。最悪、この方法ならいけますね!もっとスマートは方法があればいいのですが、、、

2006/08/04 16:15:22

その他の回答4件)

id:sami624 No.1

回答回数5245ベストアンサー獲得回数43

=9+countif(A1:A6,"りんご")

これでできます。

A列に果物の名前

B列に数字で、範囲はA1から該当セルのとなりまで出できます。

id:simplestyle

3つ目のりんごは12とは限りませんのでだめですね、、、

2006/08/04 16:13:37
id:rikuzai No.2

回答回数1366ベストアンサー獲得回数141ここでベストアンサー

ポイント43pt

あんまりスマートではないですが、簡単な方法を一つ。

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」が得られます。

id:simplestyle

そうですね。最悪、この方法ならいけますね!もっとスマートは方法があればいいのですが、、、

2006/08/04 16:15:22
id:ota2244 No.3

回答回数77ベストアンサー獲得回数4

ポイント23pt

標準の関数では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までにご質問のデータが入っているものと仮想しています)

id:simplestyle

VBAを使えばいかようにもできますね、、、

2006/08/05 09:50:04
id:llusall No.4

回答回数505ベストアンサー獲得回数61

ポイント24pt

今回もユーザ定義関数を使ってみます。

=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が表示されます。

id:simplestyle

良いのですが、今回はちょっとVBAを使いたくなかったもので、、、

2006/08/05 09:50:31
id:zuguimo No.5

回答回数19ベストアンサー獲得回数3

ポイント10pt

"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")

を埋め込めば何とかなると思います。

id:simplestyle

りんごを3つ書かなきゃいけないのが問題ですね、、、

2006/08/05 09:53:21

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

回答リクエストを送信したユーザーはいません