ExcelでMAXIFとMINIFを使う(高速化)

昨年のExcelでMAXIFとMINIFを使うで掲載したオリジナルの関数「MAXIF」と「MINIF」ですが、再度実業務で触る機会があり、その際に高速化を行いましたので再掲載しておきます（旧MAXIFや旧MINIFとの互換性はあるので、関数内の差し替えだけで使用できます）。
　
以前と同じく、SUMIF的な感じで指定範囲の最大値or最小値を返す関数です。縦方向への使用にしか対応していません。
　
※なお、エクセルのセル内計算式でMAXIFやMINIFをしたい場合は、「=MAX(IF(A1:A6=1,B1:B6))」「=MIN(IF(A1:A6=1,B1:B6))」のようなやり方もあるそうです（入力確定時にCtrl＋Shift＋Enterする必要有り）。
　
MAXIF関数

Function MAXIF(ScanCells As Range, _
               KeyWord As Variant, _
               CalcCells As Range) As Variant
    ' 条件付きMax関数
    Dim Y As Long
    Dim MaxVal As Variant
    Dim ChkVal As Range
    Dim CmpVal As Variant
    Dim CmpRows As Long
    
    ' 高速化のためセル関係の情報を変数へ取り込む
    CmpVal = CalcCells
    CmpRows = CalcCells.Rows.Count
    
    ' 比較用初期値
    MaxVal = ""
    
    Y = 1
    For Each ChkVal In ScanCells
      If ChkVal.Value = KeyWord Then
        If Y <= CmpRows Then
          If Not IsNull(CmpVal(Y, 1)) Then
            If CmpVal(Y, 1) <> "" Then
              If MaxVal = "" Or _
                 MaxVal < CmpVal(Y, 1) Then
                  MaxVal = CmpVal(Y, 1)
              End If
            End If
          End If
        End If
      End If
      Y = Y + 1
    Next ChkVal
    
    If MaxVal <> "" Then
        MAXIF = MaxVal
    Else
        MAXIF = ""
    End If
End Function

　
MINIF関数

Function MINIF(ScanCells As Range, _
               KeyWord As Variant, _
               CalcCells As Range) As Variant
    ' 条件付きMin関数
    Dim Y As Long
    Dim MinVal As Variant
    Dim ChkVal As Range
    Dim CmpVal As Variant
    Dim CmpRows As Long
    
    ' 高速化のためセル関係の情報を変数へ取り込む
    CmpVal = CalcCells
    CmpRows = CalcCells.Rows.Count
    
    ' 比較用初期値
    MinVal = ""
    
    Y = 1
    For Each ChkVal In ScanCells
      If ChkVal.Value = KeyWord Then
        If Y <= CmpRows Then
          If Not IsNull(CmpVal(Y, 1)) Then
            If CmpVal(Y, 1) <> "" Then
              If MinVal = "" Or _
                 MinVal > CmpVal(Y, 1) Then
                  MinVal = CmpVal(Y, 1)
              End If
            End If
          End If
        End If
      End If
      Y = Y + 1
    Next ChkVal
    
    If MinVal <> "" Then
        MINIF = MinVal
    Else
        MINIF = ""
    End If
End Function

　
それぞれ、ScanCellsに比較対象のセル（単列複数行）、KeyWordにScanCellsから選択するデータ（文字列等）、CalcCellsにMax/Min判定対象のセル（単列複数行）を指定。
「=MAXIF(A2:A12, "合計", B2:B12)」とすると、A列が"合計"の行のB列のうち最大の値が返るというイメージ。
　
今回の修正で、ループをfor each 化したり処理前にセル情報を変数へ転送するなどして高速化しています。以前hiyori.net様で高速化のヒントを頂いていたのでそちらを参考にさせて頂いてます。感謝です。
　
多段のIFは条件一致のチェックですが、若干厳重に組んでいるので、使用条件によってはもっと簡略化することで高速化が図れると思います（ヌルチェックを飛ばしたり、１行のIF文にまとめてしまったり）。
　
あとは煮るなり焼くなりお好きにどうぞ〜。