[[20100310185601]]　『空白のある値の範囲からｘ番目に大きい値を抽出し』（めろす）　ページの最後に飛ぶ

『空白のある値の範囲からｘ番目に大きい値を抽出したい』（めろす）

Excel2007、Windows Vistaです。
以下の様に、空白セルのある入力値の範囲から、一意に2番目に大きい値を探してくる関数を知りたいのですが、教えていただけますでしょうか。
このデータの場合、一番大きい9.1は二つあるので、次に大きい8.8を返してくるような方法です。

もし以下の様に空白の無い範囲なら、LARGEとFREQUENCYを使うと関数が使えることは分かりました。

空白のない範囲だと、以下の式でうまくいきました。

=LARGE(IF(FREQUENCY(A1:A9,A1:A9),A1:A9),2)

が、一番上のデータ例のように、空白セルがあると#N/Aが返ってきてしまいます。

どうか解決法を教えて下さい。
よろしくお願いします。

 例の場合でしたら。
 =LARGE(A1:A11,COUNTIF(A1:A11,MAX(A1:A11))+1)
 でいけそうです。
 (kkk)

 ユーザー定義関数を作ってみました。(ROUGE)
　
Function Meros(rng As Range, n As Long)
Dim tbl, x(), i As Long, m As Long, num As Double
If rng.Rows.Count > 1 And rng.Columns.Count > 1 Then Exit Function
If rng.Cells.Count = 1 Then Exit Function
tbl = rng.Value
With CreateObject("Scripting.Dictionary")
    m = 0
    If rng.Rows.Count > 1 Then
        For i = 1 To UBound(tbl, 1)
            If IsEmpty(tbl(i, 1)) Then
                If m > 0 Then
                    num = WorksheetFunction.Max(x)
                    .Item(num) = Empty
                    m = 0
                End If
            Else
                If IsNumeric(tbl(i, 1)) Then
                    m = m + 1
                    ReDim Preserve x(1 To m)
                    x(m) = tbl(i, 1)
                End If
            End If
        Next
    Else
        For i = 1 To UBound(tbl, 2)
            If IsEmpty(tbl(1, i)) Then
                If m > 0 Then
                    num = WorksheetFunction.Max(x)
                    .Item(num) = Empty
                    m = 0
                End If
            Else
                If IsNumeric(tbl(1, i)) Then
                    m = m + 1
                    ReDim Preserve x(1 To m)
                    x(m) = tbl(1, i)
                End If
            End If
        Next
    End If
    If m > 0 Then
        num = WorksheetFunction.Max(x)
        .Item(num) = Empty
    End If
    tbl = .Keys
    Meros = WorksheetFunction.Large(tbl, n)
End With
End Function

kkkさん。早速コメントをありがとうございます。
思ってもいなかった視点でした。まずMAX関数で一番大きい値を見つけた後に、同じ値の数を数え、その次に大きい値の順番を見つけ、LARGE関数で抽出するのですね。確かに、2番目の値を抽出するには使えますね。
参考になりました。

でも実は、2番目だけじゃなく、3番目以降、5番目くらいまでの数値も抽出したいのです。この場合はどうすればよいでしょうか？どうかご教示お願いします。
（めろす）

 空白で挟まれた範囲の代表値として最大値を出し、
 その最大値の中から1番目、2番目、、、の数値を出すと思いましたが、
 もしかしてσ(^-^;)は深読みしましたか？
 (ROUGE)

 実は深読みしていないUDFを作っていたのですがROUGEさんの速さに負けて
 消しかけていましたのでとりあえずアップしてみます。(^^;

 標準モジュールに貼り付けて、セルで
 2番目なら　=myLARGE(B1:B11,2) のように。
 3番目なら　=myLARGE(B1:B11,3) のように。

  Function myLARGE(rng As Range, n As Long) As Variant
  Dim tbl, c, k, buf, i As Long
  tbl = rng.Value
  With CreateObject("Scripting.Dictionary")
    For Each c In tbl
      If c <> "" And Not .Exists(c) Then
        .Add c, ""
      End If
    Next c
    k = .keys
  End With
  If UBound(k) + 1 < n Then
    myLARGE = CVErr(xlErrNum)
    Exit Function
  End If
  For i = 0 To UBound(k)
    For j = i + 1 To UBound(k)
      If k(i) < k(j) Then
        buf = k(i): k(i) = k(j): k(j) = buf
      End If
    Next j
  Next i
  myLARGE = k(n - 1)
  End Function

 （ｍｏｍｏ）

Rouge様
あ、改行がなくなり、わかりにくくなってしまいました。もう一度結果の例だけ示します。

１番目に大きい値：9.1

２番目に大きい値：8.8

３番目に大きい値：6.4

４番目に大きい値：6.2

５番目に大きい値：5

６番目に大きい値：3.3

７番目に大きい値：2.5

です。よろしくお願いします。
（めろす）

 こんな感じでしょうか。
 =LARGE(INDEX((MATCH(A1:A11,A1:A11,)=ROW(A1:A11))*A1:A11,),2)
 2の部分を適当に変えてください。
 (kkk)

Rouge様
先にアップしたつもりの文章が消えてしまいました。私の意図は、A1～A11全体から１番目に大きい値、２番目に大きい値、3番目に・・・、を抽出したい、というものです。上に書いたのが希望する結果です。よろしくお願いします。
（めろす）

kkk様

 ありがとうございます。
=LARGE(INDEX((MATCH(A1:A11,A1:A11,)=ROW(A1:A11))*A1:A11,),2)
をそのままペーストして試してみましたら、うまくいきました。ただ、データの範囲がA1～A11の時だけです。

なぜかデータの範囲を一行下にずらしてA2～A12にしてみるとうまく行きません。数式も以下の様にセルの参照値を直したのですが。

=LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A2:A12))*A2:A12,),2)

これではだめでしょうか？ROW関数はA1から始まった範囲でないと使えないのでしょうか？

単純化した例で解決策をお聞きした私が悪いのですが、実際のデータテーブルにはヘッダ行などがあり、データ範囲が2行目より下の方にあります。このような場合でも使える関数にはできませんか？
後から後から新しい条件を追加して申し訳ありません。どうかよろしくお願いします。
（めろす）

 VBAで問題なければ、私のユーザー定義関数も試してみてくださいね。
 めろすさんの要求内容は完備していると思いますので。
 （ｍｏｍｏ）

 2行目から始まるようでしたら、
 =LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A2:A12)-1)*A2:A12,),D2)
 とか
 =LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A1:A11))*A2:A12,),D2)
 にして下さい。
 3行目からなら　-1を-2に
 n行目からなら　-(n-1)にして下さい。
 (kkk)

 記念参加。
 =Hasire(A$2:A$12,ROW(A1)) で下方向にズリズリとコピペ
 　　　　（弥太郎）
 '------------------------
 Function Hasire(rng As Range, n As Integer)
    Dim dic As Object, i As Long, tbl, x()
    Set dic = CreateObject("scripting.dictionary")
    tbl = rng.Value
    For i = 1 To UBound(tbl, 1)
        If tbl(i, 1) <> "" Then
            dic(tbl(i, 1)) = Empty
        End If
    Next i
    Hasire = Application.Large(dic.keys, n)
 End Function

 あれ？半平太はんの関数どこへいった？
 =LARGE(IF(FREQUENCY(A$2:A$12,A$2:A$12+0),A$2:A$12),ROW(A1))
 ↑　は半平太はんのんですワ。　ROW(A1)を2でも3でも好きに数値にかえてもOKです。
 　　　　（弥太郎）

ｍｏｍｏ様
できました！！おっしゃるとおり、例のデータではもちろんうまくいきましたが、実際に作業しているデータでもできました。ありがとうございます！！

ついでに、小さい方からx番目のデータも抽出したかったので、頂いたコードを以下のように書き換えたら同じようにうまくいきました。

  Function mySMALL(rng As Range, n As Long) As Variant
  Dim tbl, c, k, buf, i As Long
  tbl = rng.Value
  With CreateObject("Scripting.Dictionary")
    For Each c In tbl
      If c <> "" And Not .Exists(c) Then
        .Add c, ""
      End If
    Next c
    k = .keys
  End With
  If UBound(k) + 1 < n Then
    mySMALL = CVErr(xlErrNum)
    Exit Function
  End If
  For i = 0 To UBound(k)
    For j = i + 1 To UBound(k)
      If k(i) > k(j) Then
        buf = k(i): k(i) = k(j): k(j) = buf
      End If
    Next j
  Next i
  mySMALL = k(n - 1)
  End Function

ありがとうございました。
（めろす）

kkk様

LARGEを使ったさらなるアイデアをお知らせ下さりありがとうございます。

kkkさんが書かれた、

 > =LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A2:A12)-1)*A2:A12,),D2)
 > とか
 > =LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A1:A11))*A2:A12,),D2)

のD2というのは、”x番目に大きい値”を出したい場合のxの値が入っているセル、と理解してよいでしょうか？

そうだと仮定して、A2～A12に例のデータが入っている状態で、2番目に大きい値を抽出するために、以下の二つの数式を試してみました。

=LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A1:A11)-1)*A2:A12,),2)

=LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A1:A11))*A2:A12,),2)

結果は＃N/Aでした。配列数式としてペーストするのですよね？配列数式にしてもしなくても同じ結果でしたが。やり方まちがってますでしょうか？もしまだおつきあい頂けるようなら教えて下さい。
（めろす）

弥太郎様

 おお、VBAの中でApplication.Largeという関数を使うとより単純なコードになるのですね。これもうまくいきました！
 これは、x番目に小さい値を抽出する場合にはApplication.Smallに変えるだけでよいのですね。助かりました。ありがとうございます。
 （めろす）

 2行目からデータが有る場合。
 =LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A1:A11)-1)*A2:A12,),2) 
 の式は、ROW(A1:A11)の部分は、ROW(A2:A12)　でないといけません。

 =LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A1:A11))*A2:A12,),2) 
 の式は、そのままで答えが出ると思いますが？

 配列数式の入力にしないで済むように、index関数を使っていますので、
 普通に入力して下さい。
 (kkk)

弥太郎様、半平太様

 半平太さんの数式も試してみたら、うまくいきました！これは配列数式としてペーストしなくてよいのですね。
 それにしても、

 =LARGE(IF(FREQUENCY(A2:A12,A2:A12),A2:A12),1)

 では空白セルがあるとうまく行かなかったのに、

 =LARGE(IF(FREQUENCY(A2:A12,A2:A12+0),A2:A12),1)

 と、FREQUENCY関数の区間配列の引数に+0を加えただけでうまくいく理由が未だによく分かりません。Excel配列関数のことをもっと勉強しんてくてはなりませんね。
 ともあれほんとうにありがとうございました。
 （めろす）

 kkk様

 >2行目からデータが有る場合。
 > =LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A1:A11)-1)*A2:A12,),2) 
 > の式は、ROW(A1:A11)の部分は、ROW(A2:A12)　でないといけません。

 ご指示に様に、A2:A12にデータがある状態で、

  =LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A2:A12)-1)*A2:A12,),2)

 としてやってみましたが、やはり#N/Aでした。

 > =LARGE(INDEX((MATCH(A2:A12,A2:A12,)=ROW(A1:A11))*A2:A12,),2) 
 > の式は、そのままで答えが出ると思いますが？

 こちらも#N/Aでした。ともあれ、いろいろ解決策を提案して下さり、本当にどうもありがとうございました。

 皆様ありがとうございました。たくさんの応援を頂いて、命の限り走ってきましたが、さすがのめろすも息切れしてきました。今日はここまでにします。（めろす）

 タブン、検索範囲に 0 がある環境とない環境で答えがことなる（エラーが出るか否か）と推測します。
 MATCH(A2:A12&"",A2:A12&"",) とすれば回避できるようなそうでないような。。。？
 (ROUGE)

コメント返信：

[ 一覧(最新更新順) ]

[[20100310185601]] 『空白のある値の範囲からｘ番目に大きい値を抽出し』（めろす） ページの最後に飛ぶ

[[20100310185601]]　『空白のある値の範囲からｘ番目に大きい値を抽出し』（めろす）　ページの最後に飛ぶ