Remontnouta.ru

ПК Ремонт техники
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция ВПР в Excel

Функция ВПР предназначена для поиска и подстановки значений из одной таблицы в другую на основании какого-либо признака, объединяющего обе эти таблицы. Находится функция в категории «Ссылки и массивы».

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

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

formula vpr

Функция ВПР имеет четыре аргумента:

  1. Искомое значение, в рассматриваемом примере, это табельный номер, ячейка с табельным номером выделена рамкой синего цвета;
  2. Таблица, в приведенном примере это таблица с табельными номерами и именами, выделена рамкой зеленого цвета;
  3. Номер столбца, в используемой для примера таблице, столбец с именами имеет порядковый номер два;
  4. Интервальный просмотр. Это необязательный аргумент, о нем чуть позже.

Результат вычисления функции виден на изображении ниже.

rezultat vychisleniya formuly vpr

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

Когда возникает ошибка #ЗНАЧ и как от нее избавиться при использовании ВПР().

Ошибка #ЗНАЧ может выводиться функцией ВПР(), если введенные значения аргументов функции некорректны и функция не может их обработать.

Казалось бы какие значения могут быть некорректными, если ВПР() необходимо просто сравнить одно значение с другим и присвоить ячейке данные из совпавших ячеек, но эта ошибка возникает.

Появляется ошибка #ЗНАЧ в функции ВПР() тогда, когда длина строки сравниваемой функцией слишком большая и не может быть обработана. Например, в Excel 2010 максимальная длина строки обрабатываемой функцией всего 255 символов, и если Вы будете сравнивать строки длиной 256 и более символов, то получите ошибку #ЗНАЧ.

Исправить ошибку #ЗНАЧ в таком случае можно уменьшив длины сравниваемых строк.

Еще ошибка #ЗНАЧ может возникнуть если Вы пропустили(не указали) один из аргументов в функции.не хватает аргумента

Записываем первый свой макрос

Шаблон таблицы - работаем в Эксель

На странице «Сводный» делаем шаблон исходной таблицы (смотреть рисунок).

Единственное, что надо знать для написания программы в excel — как включить макросы. По ряду существенных причин по умолчанию они отключены. На панели вкладок находим закладку «Вид» и активируем её. Если в базовой версии такой вкладки нет, то добавляем. На всплывающей панели активируем функцию «Макросы». Активируем кнопку «Запись Макроса». Теперь любое действие с таблицей будет фиксироваться и записываться в виде специальных кодов.

Выбираем имя, например «Склад».

Как производится активация макроса в Эксель

  • Назначаем сочетание клавиш, активирующих программу – «Ctrl +Я».
  • Выбираем сохранить в таблице – «Эта книга».
  • Описание – «Сортировка данных кладовщиков».
  • Подтверждаем клавишей OK (результат действий приведён на рис.).

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

Используя стандартные операции и формулы заполняем шаблон (на рис. ниже приведена получившаяся форма).

Завершаем запись. Теперь при всех изменениях исходных данных, нажатием клавиш «Ctrl +Я», будут меняться значения в таблице-шаблоне. Даже на очень простом примере видно, как написать макрос в excel 2007.

Читайте так же:
Можно ли в 1с создать общий реквизит

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

Работа макроса в Exel - что и как

  • XLSM, в отличие от стандартного варианта XLSX, таблица с таким расширением поддерживает макрокоманды.
  • XLSB обычно используется для больших таблиц и хранит информацию в бинарном формате.
  • XLTM для идентификации шаблонов с поддержкой макрокоманд.

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

Как скачать и протестировать программу

Для загрузки надстройки Lookup воспользуйтесь кнопкой Скачать программу

Если не удаётся скачать надстройку, читайте инструкцию про антивирус

Если скачали файл, но он не запускается, читайте почему не появляется панель инструментов

Это полнофункциональная пробная (TRIAL) версия, у вас есть 15 дней бесплатного использования ,
в течение которых вы можете протестировать работу программы.

Этого вполне достаточно, чтобы всё настроить и проверить, используя раздел Справка по программе

Если вам понравится, как работает программа, вы можете Купить лицензию

Лицензия (для постоянного использования) стоит 1400 рублей .

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

  • 282345 просмотров

Возможности Excel, без которых не обойтись специалисту по контекстной рекламе

Статья написана в рамках статейного конкурса Serpstat и SEOnews. Условия конкурса.

Эта статья для тех, кто пользуется Excel на продвинутом уровне. Здесь вы не найдёте информацию об использовании стандартных фильтров и сортировок, о форматировании и видах разбивки по столбцам. Я расскажу, как использую для решения реальных задач в кампаниях некоторые средства Excel: условия, формулы внутри формул, продвинутые замены, регулярные выражения, формулу =ВПР.

Математические действия с учетом условия/нескольких условий

Чаще всего в работе с одним или несколькими условиями я сталкиваюсь с суммированием:

  • =СУММЕСЛИ: применяется, когда необходимо сложить значения по одному условию;
  • =СУММЕСЛИМН: применяется, когда необходимо вычислить сумму значений с учётом нескольких условий.

Реже в моей практике встречаются ситуации, где с учётом выполнения одного или нескольких условий необходимо получить среднее значение : =СРЗНАЧЕСЛИ или =СРЗНАЧЕСЛИМН.

Эти действия — суммирование и получение среднего значения — работают по одному принципу. Покажу решение на примере суммирования.

Синтаксис выглядит следующим образом:

=СУММЕСЛИМН(Диапазон суммирования;Диапазон условия1;условие1;Диапазон условия2;условие2;…).

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

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

Диапазон суммирования — область таблицы для вычисления суммы по заданному условию.

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

Пример использования

Представьте, что при заливке кампании в Google AdWords мы забыли разбить расписание показов на дни недели и часы. При этом наша задача — расставить корректировки по временным сегментам.

Сначала заходим в отчёт:

И выгружаем его:

Далее работаем с выгрузкой. С помощью формулы =СУММЕСЛИМН мы можем по трём условиям свести данные по дням недели, где каждый день разбит на несколько сегментов времени. Красной линией подчёркнут диапазон условий, зелёной — условия и диапазоны условий.

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

Читайте так же:
Как вернуть жесткий диск по гарантии

NB Не забывайте использовать знак «#187; для фиксации строк и столбцов. При такой фиксации достаточно будет обозначить все диапазоны абсолютными ссылками один раз. Потом вы сможете протягивать и копировать формулы, не опасаясь, что у вас поедут диапазоны или условия.

Использование формул внутри формул

Для решения большинства реальных задач я часто использую сложные составные формулы. Например:

= ПОДСТАВИТЬ ( ЗАМЕНИТЬ ( C3 ; ПОИСК ( D3 ; C3 ) — 1 ; ДЛСТР ( C3 ) — ПОИСК ( D3 ; C3 ) + 2 ; «» ) ; «http://» ; «http://» & D3 & «.» )

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

=ЕСЛИ(Условие;Значение, если условие выполняется;Значение, если условие не выполняется);

=ЕСЛИОШИБКА(Значение;Значение, если ошибка);

=ПОИСК(Подстрока, которую ищем;Строка, в которой ищем).

Пример использования

Перед нами стоит задача — выгрузить все url, которые есть в аккаунте, и провести проверку определённой их части на наличие конкретного символа. Это позволит клиенту заменить искомый символ на другой и регулировать замену лендинга, если она необходима. Как выделить определенную часть url я расскажу далее в статье, а пока предположим, что она у нас уже есть:

Что мы делаем дальше:

    С помощью формулы =ПОИСК ищем позицию, на которой встречается символ «

Разумеется, задачу можно решить иначе: разбить url на сегменты по знаку «/» и поместить каждый сегмент в отдельный столбец. Однако надо учитывать, что этих сегментов может быть три, а может — двадцать. Представьте, сколько времени это займёт и как будет выглядеть итоговая таблица.

NB Символ тильда «

» является служебным в Excel. Он используется перед служебными знаками «?», «*» или «

» для поиска соответственно вопросительных знаков, звездочек и других знаков тильды. Поэтому в формуле мы используем выражение «

Выделение подстроки с помощью регулярных выражений

Отмечу, что речь идёт о решении для Google-таблиц, так как в Excel вы можете решить задачу через макросы VBA.

В Google-таблицах есть 3 формулы, которые позволяют работать с регулярными выражениями:

  • =REGEXREPLACE: заменяет часть строки на другой текст с помощью регулярного выражения;
  • =REGEXEXTRACT: извлекает определенную часть текста, соответствующую регулярному выражению;
  • = REGEXMATCH: проверяет, соответствует ли текст регулярному выражению.

Синтаксис выглядит следующим образом:

=REGEXREPLACE(Исходная строка;Регулярное Выражение;Выражение для замены);

=REGEXEXTRACT(Исходная строка;Регулярное Выражение);

=REGEXMATCH(Исходная строка;Регулярное Выражение).

Исходная строка — текст, на который применяется регулярное выражение .

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

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

Пример использования

Представим, что мы выгрузили чистые url и нужно выделить из них подстроку. Для этого подходят первые две формулы . Сначала я покажу как в одно действие операцию можно сделать с =REGEXREPLACE.

Строим шаблон на весь url « (.+)/(.+)/(.+)+#187; . Фактически получается, что мы его разделяем на 3 группы:

  • Первая группа выделяет протокол сайта;
  • Вторая группа выделяет домен;
  • Третья группа выделяет часть url, которая стоит между последним «/» и концом строки.

У каждой такой группы на выходе будет соответственно свой уникальный номер $1, $2, $3. Так как нам нужна последняя группа, мы заменяем всю строку на третью группу, то есть на «$3»:

И проверяем, соответствует ли url регулярному выражению, например, с помощью =REGEXMATCH:

Если же мы хотим решить задачу с помощью формулы =REGEXEXTRACT, то действуем иначе. Строим шаблон на часть url, которая идет с конца строки до последнего одинарного «/»: «[w.+-

Проверяем url на соответствие регулярному выражению:

Конечно, мы снова можем разбить url вручную по столбцам с ориентацией на «/», но наше решение с регулярными выражениями быстрее и компактнее. Кроме того, это решение можно использовать для автоматизации некоторых процессов в создании рекламных кампаний.

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

Продвинутая замена

Заменять значения можно с помощью двух формул: =ЗАМЕНИТЬ и =ПОДСТАВИТЬ.

Формула =ЗАМЕНИТЬ позволяет делать любые замены указанного числа знаков на новую подстроку. Формула =ПОДСТАВИТЬ используется, когда нужно заменить заранее известную подстроку в строке.

Синтаксис выглядит следующим образом:

=ЗАМЕНИТЬ(исходный текст;начальная позиция;число знаков;текст для замены);

=ПОДСТАВИТЬ(исходный текст;текст, который заменяем;текст для замены).

Исходный текст — строка, в которой будем производить замену.

Начальная позиция — позиция в исходном тексте, начиная с которой производится замена.

Число знаков — общее число знаков, которое подвергается замене.

Текст для замены — текст, на который заменяем выбранную подстроку.

Текст, который заменяем — подстрока, которую заменяем.

Пример использования

Задача — разместить часть url в области субдомена, а из самого url убрать её.

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

Решение с регулярными выражениями выглядит следующим образом:

Манипуляции с url бывают разными и зависят от задачи, которую надо решить в рекламной кампании. Например, полученные здесь субдомены мы использовали в объявлениях. Наше решение однозначно ускорило работу, так как в выборке было более 5000 url.

Формула =ВПР

Формула =ВПР выполняет вертикальный поиск в таблице, позволяет найти и забрать данные из неё и переставить значения в соответствующие поля другой таблицы.

Синтаксис выглядит следующим образом:

=ВПР(искомое значение;таблица для поиска;номер столбца;интервальный просмотр).

Исходное значение — уникальное значение (ключ), по которому осуществляется поиск в таблице для поиска.

Таблица для поиска — таблица, по которой осуществляется поиск и из которой забираем данные в исходную таблицу.

Номер столбца — порядковый номер столбца,из которого мы забираем данные в таблице для поиска.

Интервальный просмотр — точное или приблизительное соответствие исходного значения значениям из таблицы для поиска.

Пример использования

Предположим, есть задача — свести онлайн-данные с площадки с офлайн-данными в разрезе регионов. На входе мы имеем две таблицы. В таблице №1 представлены онлайн-данные: показы, клики, расход, лиды. В таблице №2 представлены офлайн-данные: заключённые договоры, отказы.

Используем формулу =ВПР и переставляем данные из таблицы №2 в таблицу №1. Сначала забираем в первую таблицу значения из столбца «Заключённый договор»:

Затем меняем в формуле номер столбца и забираем данные по «Отказам»:

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

Важно помнить, что делать такую интеграцию данных вручную на большом проекте сложно и неэффективно. В этом случае лучше один раз выстроить систему аналитики на базе Google BigQuery, например. Затем автоматизированно в одном месте собирать данные из Google Analytics, «Яндекс.Метрики», CRM-системы и других источников. Наше решение удобно для локальных задач, когда нет возможности быстро настроить полноценную систему аналитики, а данные нужны здесь и сейчас.

Заключение

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

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

Этот инструмент является простой и наглядной альтернативой функции ВПР в Excel. Работает он следующим образом:

  1. Выберите свою основную таблицу. Предположим, это таблица с данными о продажах.
  1. Выберите таблицу поиска. Это может быть список менеджеров, закрепленных за отдельными покупателями.
  1. Выберите один или несколько общих столбцов в качестве уникальных идентификаторов. В нашем случае это будет наименование заказчика.
  1. Укажите, какие столбцы нужно обновить. В данном случае – ничего. Просто пропускаем этот шаг.
  2. При желании выберите столбцы, которые нужно добавить. Добавим в основную таблицу колонку с фамилиями менеджеров, которые работают с конкретным заказчиком. При этом совершенно не важно, где находятся эти столбцы для добавления — слева или справа от столбца поиска.
  1. На следующем шаге вы можете указать дополнительные опции объединения – выделение цветом добавленного, добавление несовпадающих значений в конец основной таблицы, вставка столбца статуса и др. Но в нашем случае в этом нет необходимости. Ведь мы просто хотим найти и добавить в основную таблицу фамилии менеджеров. Поэтому просто нажимаем Finish. Теперь дайте Мастеру объединения таблиц несколько секунд для обработки… и наслаждайтесь результатами 🙂

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

Более подробную информацию об инструменте Merge Tables вы можете посмотреть здесь.

Дополнительные материалы о функции ВПР:

Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…

ВПР с несколькими таблицами

2 способа извлечь данные из разных таблиц при помощи ВПР. — Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном…
/> Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и…
Почему не работает ВПР в Excel? — Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…
ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами — ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска…
Формула ВПР в Excel — 22 факта, которые нужно знать. — В процессе работы в Excel часто возникает задача извлечения нужных данных из рабочих таблиц. Для этой цели в Excel предусмотрена формула ВПР (VLOOKUP в английском варианте). И хотя ВПР относительно…

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

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

Аналогично функции ВПР, в диалоговом окне надстройки необходимо задать несколько параметров:

  1. Номер столбца с искомыми значениями;
  2. Таблица;
  3. Номер столбца в таблице;
  4. Условие (тип) поиска;
  5. Номер столбца для вставки результатов.

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

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

Вот пример макроса с использованием словаря и загрузкой с отдельной БД:

То есть например в свой файл мне нужно подтянуть 5-й, 9-й, 14-й столбец из книги «БД»

Добрый день уважаемый пользователь Excel!

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

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

Итак, создадим таблицу примеров: Теперь можно узнать, к примеру, какая прибыль принесла вторая отгрузка компании «Ванта» ну или когда отгружалась третий раз компания «Каскад». Так как стандартная возможность функции ВПР этого сделать не может, она найдет первое вхождения по названию компании и остановится. А на вопрос о том кто отгрузил товар по накладной №874, ответа вы не получите, т.к. функционал ВПР не работает на поиск значений левее от столбика поиска и нужно использовать комбинацию функций ПОИСКПОЗ и ИНДЕКС.

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

На просторе Интернета много полезностей, вот и нашёл там код функции от Николая Павлова, который вам поможет. Условно назовите ее, к примеру, VPR, чтобы не забыть. Откройте редактор VBA с помощью горячего сочетания клавиш ALT+F11 или на панели управления в разделе «Разработчик» в блоке «Код», нажимаете кнопку «Visual Basic».

Следующим шагом создаете новый модуль, в меню выбираете пункт «Insert», а потом нажать «Module» и вставляете в него следующий код:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector