Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую
Прочитав статью, вы не только узнаете, как найти данные в таблице Excel и извлечь их в другую, но и приёмы, которые можно применять вместе с функцией ВПР.
Батьянов Денис на правах гостевого автора рассказывает в этом посте о том, как найти данные в одной таблице Excel и извлечь их в другую, а также открывает все секреты функции вертикального просмотра.
При работе в Excel очень часто возникает потребность найти данные в одной таблице и извлечь их в другую. Если вы ещё не умеете это делать, то, прочитав статью, вы не только научитесь этому, но и узнаете, при каких условиях вы сможете выжать из системы максимум быстродействия. Рассмотрено большинство весьма эффективных приёмов, которые стоит применять совместно с функцией ВПР.
Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.
Повторяющиеся значения в Excel — находим и выделяем быстро
- Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
- Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром , в противном случае возвращается код ошибки #Н/Д (#N/A).
Многие формулы в Excel начинаются со знака равно (=). Дважды щелкните или начните печатать в ячейке, и вы начнете создавать формулу, в которую вы хотите вставить ссылку. Например, я собираюсь написать формулу, которая будет суммировать значения из разных ячеек.
Исключение значений одного списка из другого с помощью формулы
Потрясающе! Использование эффективных вкладок в Excel Как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи кликов мышью каждый день!
Для этого вы можете применить следующие формулы. Пожалуйста, сделайте следующее.
1. Выберите пустую ячейку, которая находится рядом с первой ячейкой списка, который вы хотите удалить, затем введите формулу = СЧЁТЕСЛИ ($ D $ 2: $ D $ 6, A2) в панель формул, а затем нажмите Enter . См. снимок экрана:
Примечание . В формуле $ D $ 2: $ D $ 6 — это список, на основе которого вы будете удалять значения, A2 — это первая ячейка списка, который вы собираетесь удалить. Измените их по своему усмотрению.
2. Продолжая выбирать ячейку результата, перетащите маркер заполнения вниз, пока он не достигнет последней ячейки списка. См. снимок экрана:
3. Продолжайте выбирать список результатов, затем нажмите Данные > Сортировать от А до Я .
Затем вы можете увидеть, что список отсортирован, как показано на скриншоте ниже.
4. Теперь выберите целые строки с именами с результатом 1, щелкните правой кнопкой мыши выбранный диапазон и нажмите Удалить , чтобы удалить их.
Теперь вы исключили значения из одного списка на основе другого.
Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую — Лайфхакер
- Повторное использование чего угодно: добавьте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное, и быстро использовать их в будущем.
- Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
- Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
- Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
- Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Важно помнить: если оба рабочих документа открыты одновременно, изменения будут внесены автоматически в реальном времени. Когда вы меняете переменную, то информация в другом документе будет автоматически изменена или пересчитана, на основании новых данных.
2. Включение проверки данных для выбранных ячеек
В этом примере я хочу добавить раскрывающиеся списки в столбец «Рейтинг». Выберите ячейки, в которые вы хотите добавить выпадающие списки. В моем случае я выбрал B2 через B10.
В разделе «Инструменты данных» нажмите кнопку «Проверка данных».
Как создавать раскрывающиеся списки в Microsoft Excel с помощью проверки данных — Блог веб-программиста
Другим элементом опции в раскрывающемся списке является сообщение об ошибке, которое будет отображаться, когда пользователь попытается ввести данные, которые не соответствуют настройкам проверки. В нашем примере, когда кто-то вводит опцию в ячейку, которая не соответствует ни одному из предустановленных параметров, отображается сообщение об ошибке.
