Быстрые ссылки
DGET — это простая функция поиска, предназначенная для извлечения одного значения из столбца в таблице или базе данных. Она особенно полезна для получения отдельной точки данных из большой таблицы, позволяя избежать бесконечной прокрутки в поисках нужной информации.
В этом руководстве я расскажу вам о синтаксисе функции, приведу несколько примеров из реальной жизни и обсудим некоторые ее плюсы и минусы.
Синтаксис DGET
Вот синтаксис для этой функции:
где
Все три аргумента для этой функции являются обязательными, то есть, если вы пропустите любой из них, Excel вернет ошибку #VALUE!.
Чтобы объяснить это более наглядно, вот несколько примеров.
Пример 1: Один критерий
Начнем с этого очень простого примера, который включает в себя список идентификаторов сотрудников, имен, отделов и стажа работы.
Настройка таблицы
Синяя таблица вверху — это моя таблица для извлечения, а зеленая таблица ниже — это моя база данных. Цель состоит в том, чтобы вернуть имя, фамилию, отдел и стаж сотрудника в синей таблице, когда я введу его идентификатор в ячейку A2.
Прежде чем показать вам, как извлекать данные из зеленой таблицы базы данных в синюю таблицу для извлечения, позвольте мне выделить несколько важных моментов на скриншоте выше:
Добавление выпадающего списка
Чтобы мне не пришлось каждый раз вводить идентификатор сотрудника в ячейку A2, я создам выпадающий список этих номеров.
Если вы хотите сделать то же самое, выберите соответствующую ячейку и нажмите "Проверка данных" на вкладке "Данные". Затем выберите "Список" в поле "Разрешить" и выберите ячейки, содержащие данные для выпадающего списка, в поле "Источник". В моем примере, даже несмотря на то, что у меня есть только 175 идентификаторов в базе данных, я расширил список проверки данных до ячейки A236, чтобы любые дополнительные идентификаторы, которые я добавлю, были включены в мой выпадающий список.
Обратите внимание, что ячейка A2 теперь содержит выпадающую стрелку, которую можно щелкнуть, чтобы показать полный список идентификаторов.
С одним из этих идентификаторов, выбранным, я теперь готов начать извлечение DGET.
Формула DGET
В ячейку B2 я введу:
поскольку ячейки A4 до E172 представляют мою базу данных, значение в B1 (имя) является категорией или полем, по которому я хочу, чтобы Excel производил поиск, а ячейки A1 и A2 (название категории "ID" и ID в ячейке A2, выбранный из моего выпадающего списка) являются критериями. Когда я нажимаю Enter, я вижу, что Excel успешно извлекло имя, основанное на идентификаторе в ячейке A2.
Аргументы a и c содержат символы доллара ($) перед ссылками на столбцы и строки, потому что они являются абсолютными ссылками. Другими словами, эти ссылки никогда не изменятся — я всегда буду использовать ID для создания поиска, и база данных всегда будет находиться в этих ячейках. Я добавил эти символы доллара, нажав F4 после добавления каждой ссылки в формулу.
Однако я намеренно оставил аргумент b как относительную ссылку, так как теперь я буду использовать рукоятку заполнения Excel, чтобы применить ту же формулу к оставшимся категориям в моей таблице извлечения (фамилия, отдел и стаж работы).
Обратите внимание, как формула в ячейке E2 извлекает имя поля из ячейки E1 как результат, в то время как ссылки на базу данных и критерии остались неизменными.
Теперь я могу выбрать другой идентификатор в ячейке A2, используя выпадающий список, который я создал, чтобы извлечь данные о других сотрудниках.
Если вы отформатировали свою базу данных с помощью инструмента форматирования таблиц Excel, аргумент a будет именем таблицы (также известным как структурированная ссылка) вместо ее ссылок на ячейки.
Пример 2: Несколько критериев
Чтобы сделать поиск более специфичным — полезно, если ваш DGET продолжает возвращать ошибку #NUM! из-за наличия нескольких совпадений — вы можете использовать более одного критерия в аргументе c.
Здесь я хочу вернуть идентификатор, имя и фамилию сотрудника, который, как я знаю, работал в отделе кадров в течение десяти лет, но чьё имя я немного не помню.
Сначала в ячейке A2 я введу:
где ячейки A4 до A172 содержат мою базу данных, ячейка A1 — это категория, а ячейки D1 до E2 содержат мои два критерия. Фактически Excel создает логическую последовательность И между ячейками D2 и E2 для определения моих критериев.
Поскольку я закрыл свои ссылки на базу данных и критерии, но оставил ссылку на категорию относительной, я могу дублировать формулу в оставшиеся ячейки своей таблицы извлечения, чтобы напомнить себе имя этого сотрудника.
Если вам более знаком VLOOKUP, вы могли заметить, что вы можете использовать DGET для извлечения данных справа или слева от места, где вы вводите свою формулу, что является гибкостью, которая недоступна VLOOKUP.
Вы также можете создать логическую последовательность ИЛИ, добавив еще одну строку в свою таблицу извлечения. Например, если я знал, что кто-то был на работе один или два года, но не мог вспомнить его имя, я бы ввел 1 в ячейку E2, 2 в ячейку E3 и расширил аргумент c на ячейки E1 до E3. Excel затем будет искать и возвращать запись, у которой либо 1 или 2 в качестве стажа работы. Тем не менее, если несколько человек соответствуют этим критериям, Excel вернет ошибку #NUM!.
Преимущества использования DGET
Возможно, вы задаетесь вопросом: "Почему я должен использовать DGET, если есть другие более продвинутые функции?" Что ж, вот некоторые преимущества использования этого инструмента:
Недостатки использования DGET
С другой стороны, хотя простота DGET делает его легким в использовании, это также означает, что есть некоторые недостатки, о которых следует знать:
Недостатки DGET
Как их исправить
Вы можете искать только одну запись за раз. Каждый поиск требует своего собственного заголовка и критериев.
Используйте XLOOKUP (или VLOOKUP, если массив возврата находится справа от массива поиска) или создайте отдельные области извлечения DGET для нескольких поисков.
Если есть несколько совпадений, DGET возвращает ошибку #NUM!.
Измените данные так, чтобы не было дубликатов или используйте VLOOKUP, который возвращает данные из первого найденного совпадения.
DGET не работает с горизонтальными таблицами (где категории находятся в строках, а данные — в столбцах).
Используйте инструмент транспонирования Excel, чтобы изменить структуру базы данных, используйте HLOOKUP, который предназначен для работы с горизонтальными таблицами, или используйте XLOOKUP, который может искать в любом направлении.
В этой статье я обсудил DGET, VLOOKUP, HLOOKUP и XLOOKUP, некоторые из самых известных функций поиска Excel. Однако было бы опрометчиво не упомянуть INDEX и MATCH, которые — в сочетании — являются мощными, гибкими и адаптируемыми альтернативами.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Новинка! На нашем канале в Яндекс Дзен появилась премиум подписка, которая позволяет читать статьи без рекламы и получать доступ к эксклюзивным материалам, недоступным обычным пользователям. Будем рады, видеть вас в числе премиум пользователей!
️ Любите активный отдых на природе? Подписывайтесь на канал Поход лайфхак в Яндекс Дзен — кладезь полезных советов для любителей активного отдыха!
Вы также можете читать наши материалы в:
- Telegram: https://t.me/gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru