Вдосконалимо функцію ВВР Excel

Прочитання публікації Спрощуємо бінарний пошук в Excel сподвигло на додаткове вдосконалення функції ВВР порівняно з наведеним у статті.
Що не було враховано, і що хотілося б додати:
1. Універсальність, тобто можливість викликати функцію як для відсортованого масиву, так і для неотсортированного.
2. Виключити необхідність два рази викликати функцію бінарного пошуку (ВВР).
3. Як вихідна функція ВВР, так і запропонована в статті має наступний недолік: номер колонки зазвичай фіксується при виклику функції. Звичайно життя не стоїть на місці, і в таблицю доводиться додавати колонки в довільне місце. Якщо аргументи функції зазначені у вигляді посилань на комірки, то Excel вміє самостійно змінювати посилання таким чином, щоб посилання продовжувала посилатися на ті ж дані, що і раніше. Якщо ж посилання задано номером колонки, то при додаванні колонок в середину таблиці, такий перенумерації не відбувається і доводиться здійснювати пошук використаних функцій ВВР і змінювати виклики вручну. Тому доцільно задавати колонку з ключем і колонку з шуканим значенням у вигляді двох окремих аргументів.
4. Зробити захист від дурня — перевіряти передаються аргументи на коректність.
У зв'язку з цим доцільно відокремити вказівка на те, яким чином відсортований масив за зростанням, за спаданням або не відсортований) від бажаного результату (потрібен точний результат чи досить наближений). Ось отриманий код з коментарями.
' VPR - поліпшена версія функцій ВВР і ДПР (VLookup & HLookup)
'key - шукане значення (індекс)
'a - масив для пошуку індексу
'b - масив такої ж форми, для видачі значення
'Ordered - вказує тип впорядкування масиву: 1 - за зростанням, 0 - не впорядкований, -1 - відсортований за спаданням
'NotStrict - вказує, чи потрібно точне або приблизне значення: False - точне, True - досить приблизного.
'Якщо не впорядкований масив (Ordered = 0), то завжди повертається точне значення

Function VPR(key As Variant, ByRef a As Range, ByRef b As Range, Optional Ordered As Integer = 0, Optional NotStrict As Boolean = False) As Variant

'перевіряємо коректність аргументів a і b - повинні бути лінійними, з однієї області і 
'з однаковим ненульовим кількістю елементів

If (b.Areas.Count <> 1) Or ((b.Columns.Count > 1) And (b.Rows.Count > 1)) Then
VPR = CDbl("")
Exit Function
End If

If (a.Areas.Count <> 1) Or ((a.Columns.Count > 1) And (a.Rows.Count > 1)) Then
VPR = CDbl("")
Exit Function
End If

If (a.Count <> b.Count) Or (a.Count < 1) Then
VPR = CDbl("")
Exit Function
End If

If Ordered = 0 Then
NotStrict = False
End If

Dim index As Long
index = Application.WorksheetFunction.Match(key, a, Ordered)

If (Not NotStrict) And (a(index).value <> key) Then
VPR = CDbl("")
Else
VPR = b(index).value
End If
End Function

Додаткові плюшки
1. Можливість пошуку в масиві, відсортованому за спаданням (Ordered = -1).
2. Функція дозволяє робити пошук якщо будь-який (або обидва) з аргументів a і b є горизонтальними рядами (тобто узагальнює і функцію горизонтального перегляду ДПР).
Коментарі
1. Виклик CDbl("") потрібен для генерації помилки (видача #VALUE#).
2. Використовується не ВВР, а функція Match (російський аналог — MATCH).
Дякую за увагу!
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.