Какие функции позволяют извлекать из базы данных нужное значение в excel
Если потребовалось заиметь в Excel функцию, которой там еще нет, но она очень нужна или её применение значительно упростило бы жизнь при выполнение определенных задач, то самое время посмотреть в сторону функций пользователя.
Функция пользователя(UDF) - или в дословном переводе Функция, Определенная Пользователем, т.к. в оригинале она звучит как: User Defined Function. Так же их называют пользовательские функции.
Такие функции вызываются через Мастер функций -категория Определенные пользователем (User Defined) :
Так что же это за функции такие? Функция пользователя это функция, написанная при помощи языка Visual Basic for Application (VBA) и вызываемая как любая другая функция с листа. Но т.к. эти функции пишутся самостоятельно - можно создать любую функцию, которая будет делать то, что ни одна стандартная функция делать не умеет. Естественно, теперь возникает вопрос как написать такую функцию. Для написания UDF понадобятся хотя бы базовые знания языка VBA. Я в статье опишу лишь принципы создания таких функций и после прочтения вы сможете создать простейшую функцию. Но это никак не означает, что я научу создавать функции на все случаи жизни, ибо это сводится к обучению самому языку программирования. В статье же рассмотрим основные принципы создания, некоторые нюансы и как уже написанные функции использовать в своей книге.
Основные ограничения функций пользователя
Самое главное, что необходимо усвоить - это определенные ограничения, накладываемые на функцию пользователя(UDF), вызываемую с листа:
- UDF не может изменять значения других ячеек (с небольшими недокументированными исключениями)
- UDF не может изменять форматы ячеек либо присваивать форматы (с небольшими недокументированными исключениями)
- UDF не может изменять так называемые объекты окружения самого Excel. Например, сменить стиль ссылок или параметры вычислений формул, вид курсора и т.п.
- UDF будет некорректно работать с такими методами как FindNext, SpecialCells, CurrentRegion, CurrentArray, Select, ShowPrecedents и ShowDependents(выделение зависимостей ячеек), Application.GoTo и т.п. Хотя методы вроде Range.End(xlUp), Range.End(xlDown), обычный Find(без FindNext) проблем не вызывают. Подробнее про работу этих методов из UDF можно узнать из статьи: Глюк работы в UDF методов SpecialCells и FindNext
- UDF может возвращать результат только в ту ячейку, в которой записана сама функция
- для работы функции пользователя(UDF) обязательно должны быть разрешены макросы
Как создать функцию пользователя
Предполагается, что Вы уже обладаете начальными навыками написания процедур в VBA и умеете создавать эти самые процедуры, хотя бы самые простые.
Однако прежде чем читать дальше советую ознакомиться так же со статьей: Что такое модуль? Какие бывают модули?
Основные моменты, которые следует помнить при создании функции пользователя:
- в отличие от процедуры ( Sub ) функция всегда начинается именно со слова Function , а не Sub ;
- в теле функции всегда должно быть присвоение ей значения, иначе функция не вернет необходимый результат;
- функция должна располагаться в стандартном модуле или в модуле книги, если Вы планируете вызывать её непосредственно с листа Excel
- функции пользователя "привязаны" к той книге, в которой созданы и по умолчанию не будут работать в других (для этого надо будет всегда указывать имя книги с функцией). Чтобы созданные функции работали удобно и без проблем в любой книге необходимо книгу с функциями сохранить как надстройку: Как создать свою надстройку?
Самая простая функция пользователя может выглядеть так:
Function ТекущаяДата() 'присваиваем функции значение, чтобы она вернула его на лист(обязательно!) ТекущаяДата = Date 'ТекущаяДата - имя функции и именно ему необходимо передать результат End Function
Эта функция делает одно - возвращает в ячейку, в которую записана, текущую дату. В ячейке эта функция будет выглядеть так:
=ТекущаяДата()
К записи пользовательских функций в ячейку предъявляются такие же требования, как и к встроенным функциям. Это касается так же и скобок на конце функции, у которой нет аргументов. И так же это означает, что в функцию могут быть переданы наши собственные аргументы
Аргументы функции пользователя
Function MySum(vArg1 As Double, vArg2 As Double) Dim dblSum as Double 'получаем сумму двух аргументов dblSum = vArg1 + vArg2 'присваиваем функции значение, чтобы она вернула его на лист(обязательно!) MySum = dblSum 'MySum имя функции и именно ему необходимо передать результат End Function
Необязательные аргументы функции пользователя
Однако иногда бывает неизвестно, сколько аргументов будет передано в функцию: 1, 2 или 10. Для этого можно использовать ключевой параметр Optional перед аргументом, который укажет функции, что этот аргумент является не обязательным, т.е. указывать его в функции при вызове этой функции не обязательно. На примере приведенной выше функции мы можем сделать обязательным только один параметр, а еще 4 необязательными:
Function SumFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double) Dim dblSum As Double dblSum = arg1 dblSum = dblSum + arg2 dblSum = dblSum + arg3 dblSum = dblSum + arg4 dblSum = dblSum + arg5 SumFiveArgs = dblSum End Function
Function SumFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5) Dim dblSum As Double dblSum = arg1 dblSum = dblSum + arg2 dblSum = dblSum + arg3 dblSum = dblSum + arg4 dblSum = dblSum + arg5 SumFiveArgs = dblSum End Function
Можно, конечно, всегда задавать тип данных, как в первом примере. Но стоит учитывать, что для числовых типов данных( Double , Integer , Long ) значение по умолчанию будет всегда 0, даже если мы аргумент не передали в функцию(для типа String значение по умолчанию нулевая строка - ""). Это нам не мешает произвести операцию сложения и вычитания. Но операция внутри функции может быть умножением или делением и в этом случае мы получим ошибку или неверный результат:
'функция деления аргументов между собой Function DivideFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double) Dim dblSum As Double dblSum = arg1 dblSum = dblSum / arg2 dblSum = dblSum / arg3 'уже здесь получим ошибку "на ноль делить нельзя" dblSum = dblSum / arg4 dblSum = dblSum / arg5 DivideFiveArgs = dblSum End Function
'функция перемножения аргументов между собой Function MultipleFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double) Dim dblSum As Double dblSum = arg1 dblSum = dblSum * arg2 dblSum = dblSum * arg3 'здесь arg3 равен нулю, значит далее сумма будет тоже равна нулю dblSum = dblSum * arg4 dblSum = dblSum * arg5 MultipleFiveArgs = dblSum End Function
Проверять каждый аргумент на равенство нулю( If arg2 = 0 Then ) тоже будет неверно - вдруг какой-либо реально переданный аргумент будет действительно равен 0? Будет неверный результат функции. Поэтому, чтобы функции выше заработали правильно - нужна проверка на отсутствие в аргументе значения.
Тут надо знать, что если тип аргумента не указан и сам аргумент в функцию не был передан - то ему назначается особый тип - Missing . Который и дает понять, что аргумент просто не передавался в функцию(Missing в переводе можно представить как "пропущен"). И в VBA для таких случаев есть специальная функция - IsMissing . Тогда можно более гибко манипулировать аргументами(на примере функции с умножением):
Function MultipleFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5) Dim dblSum As Double dblSum = arg1 'проверяем, что аргумент передан(NOT IsMISSING) If Not IsMissing(arg2) Then dblSum = dblSum * arg2 End If If Not IsMissing(arg3) Then dblSum = dblSum * arg3 End If If Not IsMissing(arg4) Then dblSum = dblSum * arg4 End If If Not IsMissing(arg5) Then dblSum = dblSum * arg5 End If MultipleFiveArgs = dblSum End Function
Как видно - теперь Optional можно использовать вполне эффективно. Но надо помнить одно правило: аргументы, заданные в функции с ключевым Optional должны быть заданы самими последними. Т.е. после них не может идти никаких других обязательных аргументов(без ключевого Optional). Впрочем, в этом случае VBA сообщит нам об этом ошибкой " Expected: Optional ", что означает: Ожидался не обязательный аргумент .
И для большего кругозора еще одна простая функция, но которая работает уже с текстом и вернет строку до первого пробела:
Function ТекстДоПервогоПробела(Текст As String) As String Dim i As Long Dim Result As String 'переменная для результата i = InStr(1, Текст, " ", 1) 'ищем позицию первого пробела в переданном тексте 'если пробел есть и он не первый символ в строке If i > 1 Then Result = Mid(Текст, 1, i - 1) 'получаем текст до первого пробела Else 'если пробела нет - возвращаем всю строку Result = Текст End If 'присваиваем результат функции для возврата его на лист ТекстДоПервогоПробела = Result End Function
Text - ссылка на ячейку или непосредственно текст, первое слово из которого надо извлечь. Если переданный текст не будет содержать пробелов или это будет число - функция вернет весь текст. Если ячейка будет пустая - функция вернет пусто и не выдаст ошибки.
Эту функцию можно записать и намного короче:
Function ТекстДоПервогоПробела(Текст As String) As String ТекстДоПервогоПробела = Split(Текст, " ")(0) End Function
Function ТекстДоУказанногоСимвола(Текст As String, Optional Разделитель As String = " ") As String ТекстДоУказанногоСимвола = Split(Текст, Разделитель)(0) End Function
В данном примере если вызвать функцию так:
=ТекстДоУказанногоСимвола( A1 )
то функция будет использовать в качестве разделителя пробел( Optional Разделитель As String = " " ). Или можно задать символ разделения напрямую в функции и это может быть как пробел, так и любой другой символ:
=ТекстДоУказанногоСимвола(A1; ";" )
Динамическое количество аргументов в функции пользователя(ParamArray)
Сразу после некоторого использования Optional напрашивается вопрос: а если заранее неизвестно сколько аргументов будет передано? Может их будет передано 50? Или 70? Что, все перечислять? В принципе, можно сделать и так. Но можно и иначе. В VBA предусмотрен очень интересный тип данных - ParamArray . Он представляет собой динамический массив, размер которого зависит от количества переданных аргументов. На примере суммирования данных функция будет выглядеть следующим образом:
Function SumMultiple(ParamArray args()) Dim dblSum As Double, arg On Error Resume Next For Each arg In args dblSum = dblSum + arg Next SumMultiple = dblSum End Function
Но такая функция может выдать ошибку, если в качестве любого аргумента будет передана не одна единственная ячейка или значение - а диапазон ячеек( A1:A4 ) или массив( ). В этом случае внутри функции обязательно придется определять тип данных внутри ParamArray . Сделать это можно следующим образом:
Function SumMultiple_DiffTypes(ParamArray args()) Dim dblSum As Double, arg, rc As Range, x On Error Resume Next For Each arg In args Select Case TypeName(arg) Case "Range" 'это диапазон 'цикл по всем ячейкам For Each rc In arg.Cells 'проверяем, что в ячейке числовой тип данных If IsNumeric(rc.Value) Then dblSum = dblSum + rc.Value End If Next Case "Variant()" 'это произвольный массив() 'цикл по всем ячейкам For Each x In arg 'проверяем, что это числовой тип данных If IsNumeric(x) Then dblSum = dblSum + x End If Next Case "Double", "Long", "Integer" 'это любой числовой тип 'суммируем dblSum = dblSum + arg 'все остальные типы игнорируем End Select Next SumMultiple_DiffTypes = dblSum End Function
И в такую функцию может быть передан любой из наиболее распространенных типов данных:
=SumMultiple_DiffTypes(; A1:A4 ;10; C1 )
Но и у ParamArray есть недостаток: он не может использоваться одновременно с необязательными аргументами( Optional ). Вместе с ParamArray могут быть использованы только обязательные аргументы и они должны обязательно идти ДО ParamArray . Если хоть один будет указан после, то получим ошибку компилятора: " Expected: ) ". Т.е. ожидалась завершающая скобка функции .
Так же можно применить ParamArray , чтобы указывать "неограниченное" количество аргументов для сцепления значений из ячеек в одну строку с указанным разделителем:
Function ОбъединитьВсеСРазделителем(Разделитель As String, ParamArray Значения()) As String Dim result As String, arg, x, rc As Range For Each arg In Значения Select Case TypeName(arg) Case "Range" 'это диапазон 'цикл по всем ячейкам For Each rc In arg.Cells If result = "" Then result = rc.Value Else result = result & Разделитель & rc.Value End If Next Case "Variant()" 'это произвольный массив() 'цикл по всем ячейкам For Each x In arg If result = "" Then result = x Else result = result & Разделитель & x End If Next Case Else 'это любой другой тип 'суммируем If result = "" Then result = arg Else result = result & Разделитель & arg End If End Select Next ОбъединитьВсеСРазделителем = result End Function
Пример вызова такой функции с листа(первым обязательно передается разделитель, а далее уже что объединять - любой тип данных):
=ОбъединитьВсеСРазделителем("; "; A1:A4 ; C1 ;"Привет";)
Как добавить уже созданную функцию в свою книгу
Если Вы используете версию Excel 2007 и выше, то книгу необходимо будет сохранить с поддержкой макросов: Меню -Сохранить как -Книга Excel с поддержкой макросов.
Обновление расчетов функции пользователя UDF(автопересчет)
По умолчанию функции пользователя не пересчитываются вместе с пересчетом листа или по нажатию F9 ( Shift + F9 ). Чтобы функция пользователя пересчиталась, как правило необходимо либо изменить значение любого аргумента функции(например, изменить значение участвующей в расчетах ячейки) или имитировать редактирование самой функции последовательным нажатием клавиш F2 - Enter . Это не всегда удобно и часто возникает вопрос: как заставить функцию пересчитываться при любом изменении листа и при пересчете листа/книги клавишами( F9 или Shift + F9 ). Между тем делается это довольно просто и при этом сделать можно для каждой отдельной функции. На примере простой функции записи даты-времени в ячейку:
Function ТекущаяДатаВремя() ТекущаяДатаВремя = Now 'Now - возвращает текущие дату и время End Function
Если записать её в таком виде, то после записи в ячейку:
=ТекущаяДатаВремя()
при первой записи будут показаны текущие дата и время. Чтобы эксперимент был более наглядным, лучше перейти в Формат ячеек и выставить для ячейки с функцией формат " ДД.ММ.ГГ ч:мм:сс;@ ". С небольшим интервалом времени понажимайте клавишу F9 , чтобы вызвать пересчет книги. Тогда наглядно будет видно, что при пересчете значение функции не изменяется - секунды "застынут" на том месте, где были при начальном вводе функции. Выделите ячейку с функцией - нажмите F2 - Enter . Только тогда значение будет пересчитано. А теперь чуть изменим функцию - добавим ключевой параметр пересчета - Application.Volatile :
Function ТекущаяДатаВремя() Application.Volatile True ТекущаяДатаВремя = Now 'Now - возвращает текущие дату и время End Function
Теперь при каждом пересчете листа и при любом изменении на листе функция будет пересчитываться. В некоторых случаях это делать просто необходимо(например, если применяется функция получения имени листа или книги).
Но стоит всегда учитывать тот факт, что не всегда такой автопересчет полезен. Если функция пользователя использует "тяжелые" расчеты и выполняется долго - добавление автопересчета может значительно затормозить работу с файлом. Поэтому применять параметр следует с осторожностью.
Если надо, чтобы функция пересчитывалась только при изменениях в конкретном диапазоне/ячейках, можно просто сделать необязательные параметры:
Function ТекущаяДатаВремя(Optional ДиапазонОбновления As Range = Nothing) ТекущаяДатаВремя = Now End Function
тогда при любом изменении в ячейках аргумента ДиапазонОбновления функция будет пересчитана. При этом использовать хоть как-то сам этот аргумент внутри функции совершенно необязательно. Выглядеть запись такой функции будет так:
=ТекущаяДатаВремя( E:E )
при любом изменении в столбце E функция будет пересчитана.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование DGET в Microsoft Excel.
Описание
Извлекает из столбца списка или базы данных одно значение, удовлетворяющее заданным условиям.
Синтаксис
БИЗВЛЕЧЬ(база_данных; поле; условия)
Аргументы функции БИЗВЛЕЧЬ описаны ниже.
База_данных — обязательный аргумент. Диапазон ячеек, образующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Первая строка списка содержит заголовки всех столбцов.
Поле — обязательный аргумент. Столбец, используемый функцией. Введите текст с заголовком столбца в двойных кавычках, например "Возраст" или "Урожай", или число (без кавычек), задающее положение столбца в списке: 1 — для первого столбца, 2 — для второго и т. д.
Условия — обязательный аргумент. Диапазон ячеек, который содержит задаваемые условия. В качестве аргумента "условия" можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.
Замечания
В качестве аргумента "условия" можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.
Например, если диапазон G1:G2 содержит заголовок столбца "Доход" в ячейке G1 и значение 10 000 ₽ в ячейке G2, можно определить диапазон "СоответствуетДоходу" и использовать это имя как аргумент "условия" в функции баз данных.
Несмотря на то, что диапазон условий может находиться в любом месте листа, не следует помещать его под списком. Это связано с тем, что данные, добавляемые в список, вставляются в первую строку после списка. Если эта строка уже содержит данные, Microsoft Excel не сможет добавить новые данные в список.
Диапазон условий не должен перекрываться со списком.
Чтобы выполнить операцию над целым столбцом базы данных, вставьте пустую строку под строкой заголовков столбцов в диапазоне условий.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как "кузьмина" или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку "Перенос текста" (вкладка "Главная", группа "Выравнивание").
В данной категории собраны несколько специфические функции для работы с упорядоченными данными. Не зря категория включает в себя упоминание про базы данных: таблицы для работы этих функций должны отвечать определенным требованиям:
- таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
- таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
- в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) - значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.
Для разбора функции баз данных возьмем следующий пример таблицы:
Данная таблица отвечает всем требованиям для работы с функциями баз данных, однако, чтобы более удобно и гибко работать с функциями баз данных лучше сместить таблицу данных на несколько строк вниз, а выше добавить таблицу критериев, где будут формироваться условия отбора данных из основной таблицы:
Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля "Дерево" Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле "Возраст" больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.
Как видно, в качестве критериев можно указывать выражение в виде: >6 , , <>0 (не равно нулю), >=7 , . Так же знаки равенства и сравнения можно применять и с текстовыми данными, например: <>"Яблоня" .
Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных - ссылка на ячейки данных таблицы, включая заголовок( A6:E12 ).
поле - в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках ("Дерево", "Возраст" или "Урожай"), так и число, задающее положение столбца в таблице: 1 - для первого поля(столбца) в таблице "Дерево", 2 - для второго поля "Высота", 3 - для третьего поля "Возраст" и так далее.
критерий - ссылка на диапазон ячеек с условиями отбора( A1:F3 ). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.
-
ДСРЗНАЧ (DAVERAGE) - Вычисляет среднее значение выбранных записей базы данных:
=ДСРЗНАЧ( A6:E12 ;5; A1:F3 )
=ДСРЗНАЧ( A6:E12 ;"Прибыль"; A1:F3 )
=DAVERAGE( A6:E12 ,5, A1:F3 ) вернет значение 90 000р. , т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000 .
Если в двух словах, то ВПР позволяет сравнить данные двух таблиц на основании значений из одного столбца.
Чтобы чуть лучше понять принцип работы ВПР лучше начать с некоего практического примера. Возьмем две таблицы:
рис.1
На картинке выше для удобства они показаны рядом, но на самом деле могут быть расположены на разных листах. Таблицы по сути одинаковые, но фамилии в них расположены в разном порядке, и к тому же в одной заполнены все столбцы, а во второй столбцы ФИО и Отдел. И из первой таблицы необходимо подставить во вторую дату для каждой фамилии. Для трех записей это не проблема и руками сделать - все очевидно. Но в жизни это таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час. Вот где ВПР (VLOOKUP) будет весьма кстати. Все, что необходимо - записать в ячейку C2 второй таблицы(туда, куда необходимо подставить даты из первой таблицы) такую формулу:
=ВПР( $A2 ; Лист1!$A$1:$C$4 ;3;0)
=VLOOKUP($A2,Лист1!$A$1:$C$4,3,0)
Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций, выбрав в категории Ссылки и массивы (References & Arrays) функцию ВПР (VLOOKUP) и по отдельности указав нужные критерии. Теперь копируем( Ctrl + C ) ячейку с формулой(С2), выделяем все ячейки столбца С до конца данных и вставляем( Ctrl + V ).
Теперь разберем поподробнее саму функцию, её аргументы и некоторые особенности.
ВПР ищет заданное нами значение(аргумент искомое_значение ) в первом столбце указанного диапазона(аргумент таблица ). Поиск значения всегда происходит сверху вниз(собственно, поэтому функция и называется ВПР: В ертикальный ПР осмотр). Как только функция находит заданное значение - поиск прекращается, ВПР берет строку с найденным значением и смотрит на аргумент номер_столбца . Именно из этого столбца берётся значение, которое мы и видим как итог работы функции. Т.е. в нашем конкретном случае, для ячейки С2 второй таблицы, функция берет фамилию "Петров С.А." (ячейка $A2 второй таблицы) и ищет её в первом столбце указанной таблицы( Лист1!$A$1:$C$4 ), т.е. в столбце А. Как только находит(это ячейка А3)
 
Описание аргументов ВПР
- Искомое_значение ( $A2 ) - это то значение из одной таблицы, которые мы ищем в другой таблице. Т.е. для первой записи второй таблицы это будет Петров С.А. . Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках - =ВПР( "Петров С.А" ;Лист1!$A$1:$C$4;3;0) , либо ссылку на ячейку, с данным текстом(как в примере функции). Есть небольшой нюанс: так же можно применять символы подстановки: "*" и "?" . Это очень удобно, если необходимо найти значения лишь по части строки. Например, можно не вводить полностью "Петров С.А", а ввести лишь фамилию и знак звездочки - "Петров*". Тогда будет выведена любая запись, которая начинается на "Петров". Если же надо найти запись, в которой в любом месте строки встречается фамилия "Петров" , то можно указать так: "*петров*" . Если хотите найти фамилию Петров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид: "Иванов . " .
Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке A2 записано: Иванов . Так же известно, что Иванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: A2 &"*" . Эта запись будет равнозначна "Иванов*" . В A2 записано Иванов , амперсанд( & ) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем:
A2&"*" =>
"Иванов"&"*" =>
"Иванов*"
А полная формула в итоге будет выглядеть так: =ВПР( A2&"*" ; Лист1!$A$1:$C$4 ; 3 ;0)
Очень удобно, если значений для поиска много.
Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон: "*"& A1 &"*" - Таблица( Лист1!$A$1:$C$4 ) - указывается диапазон ячеек, в первом столбце которых будет просматриваться аргумент Искомое_значение . Диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100 . Диапазон в аргументе таблица всегда должен быть "закреплен" , т.е. содержать знаки доллара( $ ) перед названием столбцов и перед номерами строк( Лист1! $ A $ 1: $ C $ 4 ).
- Номер_столбца(3) - указывается номер столбца в аргументе Таблица , значения из которого нам необходимо записать в итоговую ячейку в качестве результата. В примере это Дата принятия - т.е. столбец №3. Если бы нужен был отдел, то необходимо было бы указать номер столбца 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Номер столбца всегда указывается числом и не должен быть больше числа столбцов в аргументе Таблица .
если аргумент Таблица имеет слишком большое кол-во столбцов и необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их количество. Можно использовать формулу, которая подсчитывает количество столбцов в указанном диапазоне: =ВПР( $A2 ;Лист1! $A$1:$C$4 ;ЧИСЛСТОЛБ(Лист1! $A$1:$C$4 );0) . К слову в данном случае Лист1! тоже можно убрать, т.к. функция ЧИСЛОСТОЛБ просто подсчитывает количество столбцов в переданном ей диапазоне и неважно на каком он листе: =ВПР( $A2 ;Лист1! $A$1:$C$4 ;ЧИСЛСТОЛБ( $A$1:$C$4 );0) .
 
При работе с ВПР всегда важно помнить три вещи:
Многие наверняка заметили, что на картинке у меня попутаны отделы для ФИО(в обеих таблицах ФИО относятся к разным отделам). Это не ошибка записи. В прилагаемом к статье примере показано, как можно одной формулой подставить и отделы и даты, не меняя вручную аргумент Номер_столбца: =ВПР( $A2 ; Лист1!$A$1:$C$4 ;СТОЛБЕЦ();0) . Такой подход сработает, если в обеих таблицах одинаковый порядок столбцов.
Решение при помощи ПОИСКПОЗ
Общий принцип работы ПОИСКПОЗ (MATCH) очень похож на ВПР - функция ищет заданное значение в массиве (в столбце или строке) и возвращает его позицию(порядковый номер в заданном массиве). Т.е. ищет Искомое_значение в аргументе Просматриваемый_массив и в качестве результата выдает номер позиции найденного значения в Просматриваемом_массиве . Именно номер позиции, а не само значение. Если бы мы хотели применить её для таблицы выше, то она была бы такой:
=ПОИСКПОЗ( $A2 ; Лист1!$A$1:$A$4 ;0)
=MATCH($A2,Лист1!$A$1:$A$4,0)
- Искомое_значение( $A2 ) - непосредственно значение или ссылка на ячейку с искомым значением. Если опираться на пример выше - то это ФИО. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки * и ? и ровно в таком же исполнении.
- Просматриваемый_массив( Лист1!$A$1:$A$4 ) - указывается ссылка на столбец, в котором необходимо найти искомое значение. В отличии от той же ВПР, где указывается целая таблица, это должен быть именно один столбец, в котором мы собираемся искать Искомое_значение . Если попытаться указать более одного столбца, то функция вернет ошибку. Справедливости ради надо отметить, что можно указать либо столбец, либо строку
- Тип_сопоставления(0) - то же самое, что и Интервальный_просмотр в ВПР. С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего.
С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС (INDEX) (которая возвращает из указанного диапазона значение на пересечении заданных строки и столбца) - то это то, что нам нужно и даже больше.
=ИНДЕКС(Лист1! $A$1:$C$4 ;ПОИСКПОЗ( $A2 ;Лист1! $A$1:$A$4 ;0);2)
Такая формула результатом вернет то же, что и ВПР.
Аргументы функции ИНДЕКС
Массив(Лист1! $A$2:$C$4 ) . В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не обязательно или он всегда будет равен 1(столбец-то всего один). К слову - данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.
Работа с критериями длиннее 255 символов
Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов. Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий:
=ВПР(ПСТР( $A2 ;1;255);ПСТР( Лист1!$A$1:$C$4 ;1;255);3;0)
но это формула массива. Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые 255 символов идентичны первым 255 символам в таблице, а дальше знаки различаются - формула этого уже не увидит. Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно.
Поэтому лучше использовать такую хитрую формулу:
=ИНДЕКС( Лист1!$A$1:$C$4 ;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА; Лист1!$A$1:$A$4 = $A2 ;0));2)
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.
Сама формула построена на возможности функции СУММПРОИЗВ преобразовывать в массивные вычисления некоторых функций внутри неё. В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится.
Ну и все же я рекомендовал бы Вам прочитать подробнее про данные функции в справке.
В прилагаемом к статье примере Вы найдете примеры использования всех описанных случаев и пример того, почему ИНДЕКС и ПОИСКПОЗ порой предпочтительнее ВПР.
Tips_All_VLookUp.xls (26,0 KiB, 17 025 скачиваний)
Так же см.:
ВПР и интервальный просмотр(range_lookup)
ВПР по двум и более критериям
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Как заменить/удалить/найти звездочку?
Читайте также: