人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

●質問者: simplestyle
●カテゴリ:コンピュータ
✍キーワード:Excel みかん りんご 関数
○ 状態 :終了
└ 回答数 : 5/5件

▽最新の回答へ

1 ● sami624
●0ポイント

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

これでできます。

A列に果物の名前

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

◎質問者からの返答

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


2 ● りくっち
●43ポイント ベストアンサー

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

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

◎質問者からの返答

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


3 ● ota2244
●23ポイント

標準の関数では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を使えばいかようにもできますね、、、


4 ● llusall
●24ポイント

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

=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を使いたくなかったもので、、、


5 ● zuguimo
●10ポイント

"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つ書かなきゃいけないのが問題ですね、、、

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ