Какие функции позволяют получить нужное значение из базы данных в Excel

Обновлено: 01.07.2024

Искать в большой электронной таблице Excel не всегда просто. Используйте формулы поиска для экономии времени и эффективного поиска в электронных таблицах.

Поиск в электронной таблице Microsoft Excel может показаться простым. В то время как Ctrl + F может помочь вам найти большинство вещей в электронной таблице, вы захотите использовать более сложные инструменты для поиска и извлечения данных на основе определенных значений. Мы поможем вам сэкономить массу времени с помощью нашего списка функций расширенного поиска.

Как только вы научитесь выполнять поиск в Excel с помощью поиска, неважно, насколько велики будут ваши электронные таблицы, вы всегда сможете найти то, что вам нужно!

1. Функция ВПР

Функция ВПР позволяет найти конкретное значение в столбце и извлечь значения из соответствующей строки в соседних столбцах. Это можно сделать двумя примерами: (1) найти фамилию сотрудника по его номеру или (2) найти номер телефона, указав фамилию.

Вот синтаксис функции:

[lookup_value] — это часть информации, которая у вас уже есть. Например, если вам нужно узнать, в каком состоянии находится город, это будет название города.
[table_array] позволяет указать ячейки, в которых функция будет искать искомые и возвращаемые значения. Выбирая диапазон, убедитесь, что первый столбец, включенный в массив, содержит искомое значение!
[col_index_num] – это номер столбца, содержащего возвращаемое значение.
[range_lookup] — это необязательный аргумент, который может принимать значения 1 или 0, хотя вы также можете ввести TRUE или FALSE. Если вы введете 1 или опустите этот аргумент, функция ищет приблизительное значение, но мы обнаружили, что это случайность. В приведенном ниже примере функция ВПР, ищущая значение 100, возвращает 90. Поиск меньшего значения, например 88, возвращает ошибку.

Вот формула, которую вы бы использовали:

Поскольку четвертая оценка находится в пятом столбце после фамилии, которую мы ищем, 5 является аргументом индекса столбца. Обратите внимание, что при поиске текста рекомендуется установить для параметра [range_lookup] значение 0. Без этого вы можете получить плохие результаты.

Вот результат:

Примечания к функции ВПР

При использовании функции ВПР полезно помнить несколько вещей. Убедитесь, что первый столбец в вашем диапазоне содержит искомое значение. Если его нет в первом столбце, функция вернет неверные результаты. Если ваши столбцы хорошо организованы, это не должно быть проблемой.

Кроме того, имейте в виду, что функция ВПР всегда возвращает только одно значение. Был еще один учащийся с фамилией "Дэвидсон", но функция ВПР всегда возвращает результаты только для первой записи, без указания того, что имеется более одного совпадения.

2. Функция ГПР

Где ВПР находит соответствующие значения в другом столбце, ГПР находит соответствующие значения в другой строке. Поскольку обычно проще просмотреть заголовки столбцов, пока не найдете нужный, и использовать фильтр, чтобы найти то, что вы ищете, HLOOKUP лучше всего использовать, когда у вас есть огромные электронные таблицы или если вы работаете со значениями, которые организованы по время.

Вот синтаксис функции:

[lookup_value] – это известное вам значение, для которого вы хотите найти соответствующее значение.
[table_array] – это ячейки, в которых вы хотите выполнить поиск.
[row_index_num] указывает строку, из которой будет получено возвращаемое значение.
[range_lookup] такой же, как и в функции ВПР, оставьте это поле пустым, чтобы получить ближайшее значение, если это возможно, или введите 0, чтобы искать только точные совпадения.

Мы будем использовать ту же таблицу, что и раньше. Вы можете использовать HLOOKUP, чтобы найти оценку для определенной строки. Вот как мы это сделаем:

Как вы можете видеть на изображении ниже, возвращается оценка:

Учащийся в ряду 6, Томас Дэвидсон, набрал 68 баллов за четвертый тест.

Примечания к HLOOKUP

Как и в случае с функцией ВПР, искомое значение должно находиться в первой строке массива таблиц. Это редко проблема с HLOOKUP, так как вы обычно будете использовать заголовок столбца для значения поиска. HLOOKUP также возвращает только одно значение.

3-4. Функции ИНДЕКС и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ – это две разные функции, но при их совместном использовании поиск в большой электронной таблице становится намного быстрее. У обеих функций есть недостатки, но, объединив их, мы задействуем сильные стороны обеих.

Во-первых, синтаксис обеих функций:

[массив] — это массив, в котором вы будете искать.
[row_number] и [column_number] можно использовать для сужения области поиска (мы рассмотрим это чуть позже).

[lookup_value] – это поисковый запрос, который может быть строкой или числом.
[lookup_array] — это массив, в котором Microsoft Excel будет искать поисковый запрос.
[match_type] — необязательный аргумент, который может принимать значения 1, 0 или -1. 1 вернет наибольшее значение, которое меньше или равно вашему поисковому запросу. 0 вернет только ваш точный термин, а -1 вернет наименьшее значение, которое больше или равно вашему поисковому запросу.

Может быть непонятно, как мы будем использовать эти две функции вместе, поэтому я объясню это здесь. ПОИСКПОЗ принимает условие поиска и возвращает ссылку на ячейку. На изображении ниже видно, что при поиске фамилии «Дэвидсон» в столбце B функция ПОИСКПОЗ возвращает 2.

Индекс, с другой стороны, делает обратное: берет ссылку на ячейку и возвращает содержащееся в ней значение. Здесь вы можете видеть, что при запросе вернуть вторую строку столбца B функция ИНДЕКС возвращает «Дэвидсон», значение из строки 2.

Что мы собираемся сделать, так это объединить их, чтобы ПОИСКПОЗ возвращал ссылку на ячейку, а ИНДЕКС использовал эту ссылку для поиска значения в ячейке. Допустим, вы помните, что был студент по фамилии Таунсенд, и вы хотите узнать, какой у него был четвертый балл. Вот формула, которую мы будем использовать:

Вы заметите, что здесь для типа соответствия установлено значение 0. Когда вы ищете строку, это то, что вы захотите использовать. Вот что мы получаем, когда запускаем эту функцию:

Как видно из вставки, Ральф Таунсенд набрал 68 баллов в своем четвертом тесте. Это число появляется при запуске функции. Это может показаться не таким уж полезным, когда вы можете просто просмотреть несколько столбцов, но представьте, сколько времени вы бы сэкономили, если бы вам пришлось делать это 50 раз для большой электронной таблицы базы данных, содержащей несколько сотен столбцов!

5. Функция НАЙТИ

Статья о поиске чего-либо в Excel была бы неполной без функции НАЙТИ. Но это может быть не то, что вы ожидаете. Вы можете использовать функцию FIND Excel, чтобы определить положение строки текста в другой строке текста.

Допустим, мы хотели найти первое вхождение буквы "x" во фразе "Коричневая лиса перепрыгнула через забор". Это будет наша функция:

Полученное число представляет позицию запрошенной строки. Если вы ищете многосимвольную строку, скажем, мы запросили «лиса», результат будет указывать позицию первого символа запроса; в данном случае 11.

Примечания по ПОИСКУ

Как и функции ВПР, ГПР и другие, функция НАЙТИ идентифицирует только первое вхождение строки. Обратите внимание, что FIND чувствителен к регистру. Вы можете использовать его, чтобы НАЙТИ несколько символов. И хотя в нашем примере мы использовали буквы, он работает и с цифрами.

Сама по себе эта функция может показаться не очень полезной, но она становится полезной, когда вы начинаете вкладывать функции. Например, вы можете использовать результат НАЙТИ, чтобы разбить строку текста в позиции, соответствующей строке, указанной с помощью НАЙТИ.

ПОИСК и ПОИСК

Мы не можем описать НАЙТИ, не упомянув функцию ПОИСК. По сути, это то же самое, что и НАЙТИ, за исключением того, что регистр не учитывается. Он также позволяет использовать подстановочные знаки, что означает, что вы можете искать совпадения, которые не являются точными.

Excel поддерживает три подстановочных знака:

Звездочка (*), которая заменяет любое количество символов, включая ноль.
Вопросительный знак (?), который может заменить один любой символ.
Тильда (~), которая превращает подстановочные знаки "звездочка" и "вопросительный знак" в буквальные символы, то есть отменяет их функцию подстановочных знаков. Вы бы использовали его как ~* или ~?.

6. Функция XLOOKUP

XLOOKUP — это новая функция, предназначенная для замены функции VLOOKUP. Как и функцию ВПР, вы можете использовать ее для поиска элементов в таблице или диапазоне путем поиска известного значения. Он отличается от ВПР тем, что позволяет искать значения, расположенные в столбцах слева или справа от запрашиваемого значения; с функцией ВПР вы можете найти данные только справа от запрашиваемого столбца.

Вот синтаксис функции:

=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

[lookup_value] — это значение, которое вы ищете; то есть ваш запрос.
[lookup_array] – массив или диапазон для поиска.
[return_array] — возвращаемый массив или диапазон. Это первое отличие от ВПР.
[if_not_found] – необязательный аргумент, возвращающий сообщение по вашему выбору, если совпадений не найдено.
[match_mode] – еще один необязательный аргумент, который позволяет найти точное совпадение (0), следующий меньший элемент (-1), следующий больший элемент (1) или совпадение с подстановочным знаком (2).
[search_mode] является необязательным и позволяет вам управлять порядком поиска. По умолчанию (1) поиск начинается с первого элемента. Вы также можете начать с последнего элемента (-1) и выполнить бинарный поиск, который зависит от сортировки lookup_array в порядке возрастания (2) или убывания (-2).

Возьмем наш пример с функцией ВПР и обратим порядок поиска. Это должно позволить нам найти счет второго студента по имени Дэвидсон.Вот формула:

Обратите внимание, что мы берем имя из столбца G2, а не записываем его непосредственно в формулу. Ниже показано, как это выглядит.

На этот раз формула вернула оценку Томаса Дэвидсона, а не Эйдана Дэвидсона. Но он по-прежнему не может вернуть более одного результата.

Начнем поиск в Excel

Microsoft Excel имеет множество чрезвычайно мощных функций для работы с данными, и четыре перечисленные выше функции лишь поверхностные. Изучение того, как их использовать, сделает вашу жизнь намного проще.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Используйте ПРОСМОТР, одну из функций поиска и ссылок, когда вам нужно просмотреть одну строку или столбец и найти значение из той же позиции во второй строке или столбце.

Например, предположим, что вы знаете номер детали для автомобиля, но не знаете цену. Вы можете использовать функцию ПРОСМОТР, чтобы вернуть цену в ячейке H2, когда вы вводите номер автозапчасти в ячейку H1.

Используйте функцию ПРОСМОТР для поиска в одной строке или одном столбце. В приведенном выше примере мы ищем цены в столбце D.

Советы. Рассмотрите одну из новых функций поиска, в зависимости от используемой версии Office.

Используйте функцию ВПР для поиска в одной строке или столбце или для поиска в нескольких строках и столбцах (например, в таблице). Это значительно улучшенная версия LOOKUP. Посмотрите это видео о том, как использовать функцию ВПР.

Если вы используете Microsoft 365, используйте XLOOKUP — это не только быстрее, но и позволяет выполнять поиск в любом направлении (вверх, вниз, влево, вправо).

Есть два способа использования ПРОСМОТРА: векторная форма и форма массива

Векторная форма. Используйте эту форму ПРОСМОТРА для поиска значения в одной строке или одном столбце. Используйте векторную форму, если вы хотите указать диапазон, содержащий значения, которые вы хотите сопоставить. Например, если вы хотите найти значение в столбце А, перейдите к строке 6.

Форма массива. Мы настоятельно рекомендуем использовать функцию ВПР или ГПР вместо формы массива. Посмотрите это видео об использовании функции ВПР. Форма массива предназначена для совместимости с другими программами для работы с электронными таблицами, но ее функциональные возможности ограничены.

Массив — это набор значений в строках и столбцах (например, в таблице), в которых вы хотите выполнить поиск. Например, если вы хотите выполнить поиск в столбцах A и B, до строки 6. ПРОСМОТР вернет ближайшее совпадение. Чтобы использовать форму массива, ваши данные должны быть отсортированы.

Векторная форма

Векторная форма ПРОСМОТР ищет значение в диапазоне из одной строки или одного столбца (известного как вектор) и возвращает значение из той же позиции во втором диапазоне из одной строки или одного столбца.

Синтаксис

ПРОСМОТР(искомое_значение, искомый_вектор, [результативный_вектор])

Синтаксис векторной формы функции ПРОСМОТР имеет следующие аргументы:

lookup_value Обязательно. Значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.

lookup_vector Обязательно. Диапазон, содержащий только одну строку или один столбец. Значения в lookup_vector могут быть текстовыми, числовыми или логическими значениями.

Важно: значения в lookup_vector должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . AZ, ЛОЖЬ, ИСТИНА; в противном случае ПРОСМОТР может не вернуть правильное значение. Текст в верхнем и нижнем регистре эквивалентен.

result_vector Необязательно. Диапазон, содержащий только одну строку или столбец. Аргумент result_vector должен быть того же размера, что и lookup_vector. Он должен быть одинакового размера.

Примечания

Если функция ПРОСМОТР не может найти искомое_значение, функция сопоставляет наибольшее значение в искомом_векторе, которое меньше или равно искомое_значение.

Векторные примеры

Вы можете попробовать эти примеры на своем листе Excel, чтобы узнать, как работает функция ПРОСМОТР. В первом примере вы получите электронную таблицу, похожую на эту:

Скопируйте данные из следующей таблицы и вставьте их в новый лист Excel.

Скопируйте эти данные в столбец A

Скопируйте эти данные в столбец B

Затем скопируйте формулы ПРОСМОТР из следующей таблицы в столбец D вашего рабочего листа.

Скопируйте эту формулу в столбец D

Вот что делает эта формула

Вот результат, который вы увидите

=ПРОСМОТР(4.19, A2:A6, B2:B6)

Ищет 4.19 в столбце A и возвращает значение из столбца B, который находится в той же строке.

=ПРОСМОТР(5.75, A2:A6, B2:B6)

Ищет 5,75 в столбце A, находит ближайшее меньшее значение (5,17) и возвращает значение из столбца B, которое находится в той же строке.

=ПРОСМОТР(7.66, A2:A6, B2:B6)

Ищет 7,66 в столбце A, находит ближайшее меньшее значение (6,39) и возвращает значение из столбца B, которое находится в той же строке.

=ПРОСМОТР(0, A2:A6, B2:B6)

Ищет 0 в столбце A и возвращает ошибку, поскольку 0 меньше наименьшего значения (4,14) в столбце A.

Чтобы эти формулы отображали результаты, вам может потребоваться выбрать их на листе Excel, нажать F2, а затем нажать Enter. При необходимости измените ширину столбцов, чтобы увидеть все данные.

Форма массива

Совет. Мы настоятельно рекомендуем использовать функцию ВПР или ГПР вместо формы массива. Посмотрите это видео о ВПР; он предоставляет примеры. Массивная форма ПРОСМОТРА предоставляется для совместимости с другими программами для работы с электронными таблицами, но ее функциональные возможности ограничены.

Форма массива ПРОСМОТР ищет указанное значение в первой строке или столбце массива и возвращает значение из той же позиции в последней строке или столбце массива. Используйте эту форму ПРОСМОТРА, когда значения, которые вы хотите сопоставить, находятся в первой строке или столбце массива.

Синтаксис

Синтаксис формы массива функции ПРОСМОТР имеет следующие аргументы:

lookup_value Обязательно. Значение, которое функция ПРОСМОТР ищет в массиве. Аргумент lookup_value может быть числом, текстом, логическим значением, именем или ссылкой, которая ссылается на значение.

Если ПРОСМОТР не может найти значение искомое_значение, используется наибольшее значение в массиве, которое меньше или равно искомое_значение.

Обязательный массив. Диапазон ячеек, содержащий текст, числа или логические значения, которые вы хотите сравнить с искомым_значением.

Форма массива функции ПРОСМОТР очень похожа на функции ГПР и ВПР. Разница заключается в том, что ГПР ищет значение искомое_значение в первой строке, ВПР ищет в первом столбце, а ПРОСМОТР ищет в соответствии с размерами массива.

Если массив покрывает область, ширина которой превышает высоту (столбцов больше, чем строк), функция ПРОСМОТР ищет значение искомое_значение в первой строке.

Если массив квадратный или его высота превышает ширину (строк больше, чем столбцов), функция ПРОСМОТР ищет в первом столбце.

С помощью функций ГПР и ВПР вы можете индексировать вниз или поперек, но ПРОСМОТР всегда выбирает последнее значение в строке или столбце.

Важно: значения в массиве должны располагаться в порядке возрастания: . -2, -1, 0, 1, 2, . AZ, ЛОЖЬ, ИСТИНА; в противном случае ПРОСМОТР может не вернуть правильное значение. Текст в верхнем и нижнем регистре эквивалентен.

В этой статье описываются синтаксис формулы и использование функции ДГЕТ в Microsoft Excel.

Описание

Извлекает одно значение из столбца списка или базы данных, которое соответствует указанным вами условиям.

Синтаксис

DGET(база данных, поле, критерии)

Синтаксис функции DGET имеет следующие аргументы:

Требуется база данных. Диапазон ячеек, составляющих список или базу данных. База данных — это список связанных данных, в котором строки связанной информации являются записями, а столбцы данных — полями. Первая строка списка содержит ярлыки для каждого столбца.

Обязательное поле. Указывает, какой столбец используется в функции. Введите метку столбца, заключенную в двойные кавычки, например «Возраст» или «Доходность», или число (без кавычек), обозначающее положение столбца в списке: 1 для первого столбца, 2 для второго столбца. и т. д.

Необходимые критерии. Диапазон ячеек, содержащий указанные вами условия. Вы можете использовать любой диапазон для аргумента условия, если он включает хотя бы одну метку столбца и хотя бы одну ячейку под меткой столбца, в которой вы указываете условие для столбца.

Примечания

Вы можете использовать любой диапазон в качестве аргумента условия, если он включает хотя бы одну метку столбца и хотя бы одну ячейку под меткой столбца для указания условия.

Например, если диапазон G1:G2 содержит метку столбца "Доход" в ячейке G1 и сумму 10 000 долл. США в ячейке G2, вы можете определить диапазон как MatchIncome и использовать это имя в качестве аргумента критерия в функциях базы данных.

Несмотря на то, что диапазон критериев может располагаться в любом месте рабочего листа, не размещайте его под списком. Если вы добавите в список дополнительную информацию, новая информация будет добавлена в первую строку под списком. Если строка под списком не пуста, Microsoft Excel не может добавить новую информацию.

Убедитесь, что диапазон критериев не перекрывает список.

Чтобы выполнить операцию со всем столбцом в базе данных, введите пустую строку под метками столбцов в диапазоне критериев.

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.

поиск меню

Урок 5: Функции

Введение

Функция – это предопределенная формула, которая выполняет вычисления с использованием определенных значений в определенном порядке. Все программы для работы с электронными таблицами включают общие функции, которые можно использовать для быстрого нахождения суммы, среднего значения, количества, максимального значения и минимального значения для диапазона ячеек. Чтобы правильно использовать функции, вам необходимо понимать различные части функции и то, как создавать аргументы для вычисления значений и ссылок на ячейки.

Посмотрите видео ниже, чтобы узнать больше об использовании функций в Excel.

Части функции

Для правильной работы функция должна быть написана особым образом, который называется синтаксисом. Базовый синтаксис функции — это знак равенства (=), имя функции (например, SUM) и один или несколько аргументов. Аргументы содержат информацию, которую вы хотите вычислить. Функция в приведенном ниже примере добавит значения диапазона ячеек A1:A20.

Работа с аргументами

Аргументы могут относиться как к отдельным ячейкам, так и к диапазонам ячеек и должны быть заключены в круглые скобки. Вы можете включить один аргумент или несколько аргументов, в зависимости от синтаксиса, необходимого для функции.

Например, функция =СРЗНАЧ(B1:B9) будет вычислять среднее значение значений в диапазоне ячеек B1:B9. Эта функция содержит только один аргумент.

Несколько аргументов должны быть разделены запятой. Например, функция =СУММ(A1:A3, C1:C2, E2) суммирует значения всех ячеек в трех аргументах.

Использование функций

Есть множество функций. Вот некоторые из наиболее распространенных функций, которые вы будете использовать:

СУММ: эта функция суммирует все значения ячеек в аргументе.
СРЕДНЕЕ: Эта функция определяет среднее значение значений, включенных в аргумент. Он вычисляет сумму ячеек, а затем делит это значение на количество ячеек в аргументе.
COUNT: эта функция подсчитывает количество ячеек с числовыми данными в аргументе. Эта функция полезна для быстрого подсчета элементов в диапазоне ячеек.
MAX: эта функция определяет максимальное значение ячейки, включенное в аргумент.
MIN: эта функция определяет наименьшее значение ячейки, включенное в аргумент.

Чтобы использовать функцию:

В приведенном ниже примере мы будем использовать базовую функцию для расчета средней цены за единицу для списка недавно заказанных товаров с помощью функции СРЗНАЧ.

Ваша электронная таблица не всегда сообщит вам, содержит ли ваша функция ошибку, поэтому вы должны проверить все свои функции. Чтобы узнать, как это сделать, ознакомьтесь с уроком "Перепроверьте свои формулы".

Работа с незнакомыми функциями

Если вы хотите узнать, как работает функция, вы можете начать вводить ее в пустую ячейку, чтобы увидеть, что она делает.

Затем вы можете ввести открывающую скобку, чтобы увидеть, какие аргументы ей нужны.

Вложенные функции

Всякий раз, когда формула содержит функцию, она обычно вычисляется перед любыми другими операторами, такими как умножение и деление. Это связано с тем, что формула рассматривает всю функцию как одно значение — прежде чем использовать это значение в формуле, необходимо запустить функцию. Например, в приведенной ниже формуле функция СУММ будет вычисляться до деления:

Давайте рассмотрим более сложный пример, в котором используется несколько функций:

=РАБДЕНЬ(СЕГОДНЯ(),3)

Здесь у нас есть две разные функции, работающие вместе: функция РАБДЕНЬ и функция СЕГОДНЯ. Они известны как вложенные функции, поскольку одна функция помещается или вкладывается в аргументы другой. Как правило, вложенная функция всегда вычисляется первой, так же как скобки выполняются первыми в порядке выполнения операций. В этом примере функция СЕГОДНЯ будет вычисляться первой, поскольку она вложена в функцию РАБДЕНЬ.

Другие общие функции

Существует множество других функций, которые можно использовать для быстрого расчета различных параметров с вашими данными. Изучение того, как использовать другие функции, позволит вам решать сложные проблемы с вашими электронными таблицами, и мы будем говорить о них больше в этом руководстве. Вы также можете ознакомиться с нашими статьями ниже, чтобы узнать о конкретных функциях:

Функции базы данных предназначены для выполнения простых операций с данными, имеющими «структуру, подобную базе данных». Структура, подобная базе данных, означает, что данные должны быть в табличной структуре, в которой записи организованы с метками и соответствующим разделением. В этой модели каждая строка представляет собой отдельную запись, а каждый столбец представляет собой информацию другого типа. Для данных, которые находятся в этой структуре, вы можете использовать функции базы данных Excel, чтобы манипулировать таблицами и управлять ими гораздо эффективнее.

Все функции базы данных Excel, за исключением DGET, можно использовать так же, как и функции xIFS (СУММЕСЛИМН, СРЗНАЧЕСЛИМН и т. д.). Формулы xIFS выполняют определенную агрегацию или операцию для определенного столбца с указанными фильтрами. С другой стороны, функция DGET может получить значение ячейки как есть, без какой-либо агрегации, если после применения фильтров получен только один результат. Ниже приведен список функций базы данных Excel.

< /таблица>

Формулы выглядят и работают так же, как их аналоги IFS, за исключением функции DGET.

Все функции базы данных Excel используют один и тот же синтаксис, который имеет 3 аргумента для данных, поля и фильтров. Требуются все аргументы.

Функция	Описание
DAVERAGE	Возвращает среднее значение выбранных записей базы данных
DCOUNT	Подсчитывает ячейки, содержащие числа в базе данных
DCOUNTA	Подсчитывает непустые ячейки в база данных
DGET	Извлекает из базы данных одну запись, соответствующую заданным критериям
DMAX	Возвращает максимальное значение из выбранных записей базы данных
DMIN	Возвращает минимальное значение из выбранных записей базы данных
DPRODUCT	Умножает значения в определенном поле записей, которые соответствуют критериям в базе данных
DSTDEV	Оценивает стандартный dev на основе выборки выбранных записей базы данных
DSTDEVP	Вычисляет стандартное отклонение на основе всей совокупности выбранных записей базы данных
DSUM	Добавляет числа в столбце поля записей в базе данных, которые соответствуют критерии
DVAR	Оценивает дисперсию на основе выборки из выбранных записей базы данных
DVARP	Вычисляет дисперсию на основе всей совокупности выбранных записей базы данных

База данных	Диапазон ячеек, содержащих сами данные. Верхняя строка диапазона содержит метки для каждого столбца.
Поле	Столбец, который должен быть используется в расчетах. Можно ввести метку (имя) или позицию столбца. Вы можете ввести имя в кавычках, например «Базовый оклад», или индекс должности, например, 7 для 7-го столбца.
Критерии	Диапазон ячеек, содержащих условия, определяющие, какие записи следует включать в расчеты. Критерии имеют метку столбца (столбцов) для фильтрации и условие (я) под меткой.

Сначала построение логики критериев может показаться пугающим. Вы можете создавать условия и объединять их с помощью логических операторов И и ИЛИ. Идея состоит в том, что каждое условие внутри строки связано с помощью логики И, тогда как каждая строка связана с помощью ИЛИ. Давайте посмотрим на эти логические утверждения на примерах. Не стесняйтесь загружать наш образец рабочей тетради ниже.

Основы

Введите названия полей (столбцов) в одну строку, в которые вы хотите добавить критерии. Например, если мы хотим отфильтровать значения Атланты в столбце «Местоположение», наши критерии должны быть такими, как показано ниже.

Если нам нужно добавить второе значение для местоположения, второе значение должно быть помещено под первым. Приведенные ниже критерии относятся к записям, которые имеют значения Atlanta или Valdosta в соответствующем местоположении. Это соединение ИЛИ.

В качестве альтернативы, если мы хотим получить записи, в которых Атланта для местоположения И меньше 2017 года, нам нужно использовать ту же строку.

Обратите внимание, что вы также можете использовать операторы , = и <>. Хотя Excel предлагает использовать = с равными условиями, это не обязательно. Формулы базы данных поддерживают * и ? подстановочные знаки для неизвестных символов. Дополнительные сведения о подстановочных знаках в Excel см. в статье Использование подстановочных знаков в формуле Excel.

Сложные примеры

Множественное И

Булева логика: (Местоположение = Атланта И Год > 2011 И Год НИОКР)

Записи в Атланте или не в отделе исследований и разработок.

Вы можете оставить поля пустыми, если они вам не нужны в критериях.

Сочетание логики И или ИЛИ

Булева логика: ((Расположение = Атланта И Год > 2011 И Год НИОКР)) )

Записи в Атланте с 2011 по 2018 год или в Дотане, но не в отделе исследований и разработок.

Подстановочные знаки

Булева логика: (Полное имя = Дана*, И Местоположение = Атланта, И Год = 2018)

Записи с именами, начинающимися с Даны, в Атланте и в 2018 году. Отдел в данном случае не имеет значения.

Как только вы поймете структурные требования, управление данными в структуре, похожей на базу данных, станет намного проще. Наконец, давайте кратко рассмотрим некоторые преимущества и недостатки использования формул базы данных Excel вместо традиционных методов агрегирования.

Читайте также: