Программа подстановки данных из одного файла в другой (замена функции ВПР)

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

То же самое можно сделать при помощи формулы =ВПР(), но:

формулы могут тормозить работу с файлом при пересчёте, если объём данных большой (много строк или столбцов)
если источник данных или файл, в который подставляются данные, каждый раз новый, — требуется время на прописывание или редактирование формул
если с файлами работают люди, «далёкие» от Excel, — их проще обучить нажимать одну кнопку, чем объяснять им, как прописывать эти формулы
иногда нужны дополнительные возможности (не учитывать заданные слова и символы при сравнении, выделять цветом изменения, копировать недостающие строки, и т.д.)

В настройках программы можно задать:

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

Быстрое сравнение двух таблиц с помощью ВПР

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

В старом прайсе делаем столбец «Новая цена».
Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.

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

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

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

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

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

Как вернуть палитру в фотошопе

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

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

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

Макрос впр в excel

При работе в Excel у некоторых категорий граждан исключительно часто возникает задача сравнить 2 столбца в разных таблицах и понять, в чём они совпадают, а в чём разнятся. При помощи формул рабочего листа эта задача обычно решается либо при помощи ВПР , либо при помощи СЧЁТЕСЛИ . Однако, дело это очень муторное, требует внимательности, усидчивости, а, если таких таблиц много, то вы весьма быстро запутаетесь и устанете.

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

Вот перед вами упрощённый учебный пример такой задачи.

Путём нехитрых развлечений с функцией ВПР , вы можете получить такой результат:

То есть мы нашли пересечение наших двух диапазонов там, где ВПР вернул значение. Отфильтровав по #Н/Д в каждой таблице, мы получаем список значений того, что есть слева, но отсутствует справа, и того, что есть справа, но отсутствует слева. Это максимум того, что можно выжать из стандартного подхода с ВПР.

Недостатки стандартного подхода:

Мы потратили много сил. Если таких таблиц у нас много, то такой метод не годится. Это и трудоёмко и слишком легко запутаться.
Как мы знаем, ВПР ищет первое совпадение и значит о том, что, например, в левой таблице два значения 040310475653, а в правой только одно, мы можем и не узнать, особенно, если таблицы большие.
В случае с #Н/Д мы также не будем понимать структуру аномалий наших данных. Например, то, что значение 40310307297 справа встречается дважды.
У нас не будет информации, сколько вообще уникальных значений встречается в каждой таблице.

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

Можно ли есть школьный белый мел

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

Основные возможности предлагаемого инструмента:

Исчерпывающее сравнение двух списков с выявлением всех возможных аномалий в данных
Сравнение осуществляется как на месте, где расположены указанные пользователем диапазоны (вставляется колонка справа), так и на отдельном новом листе рабочей книги
Цветовое акцентирование результатов сравнения
Предоставление детальной статистики по результатам сравнения (вставляется в комментарий к ячейке заголовка столбца, где происходит сравнение)
Автоопределение столбца с данными при указании диапазонов (достаточно указать одну ячейку)
Две модели сравнения: простая и обычная.
Учёт регистра текста, если в этом есть необходимость
Учёт наличия / отсутствия заголовка у диапазонов
2 типа сортировки
Возможность заменить стандартные статусы сравнения на пользовательские

Используемая концепция сравнения списков

Концепция очень проста и легка для понимания при минимуме усилий.

2 сравниваемых диапазона будем называть ЛЕВЫЙ и ПРАВЫЙ . Это очевидно и естественно, если таблицы располагаются на одном листе. В случае разных листов, левым диапазоном можно называть ту таблицу, чей рабочий лист располагается левее листа второй таблицы.
Каждое значение в сравниваемых списках получит СТАТУС , означающий то, как данное значение соотносится с аналогичным значением во второй таблице и (!) с такими же значениями в своей таблице, если данное значение повторяется.
Есть 2 набора статусов: упрощённый и обычный.
Упрощенные статусы: BOTH , LEFT , RIGHT .
- BOTH — значение есть в обоих столбцах. Например, если значение «5» встречается в левой таблице 2 раза, а в правой 3 раза, то все эти строки получат статус BOTH
- LEFT — значение встречается только в левой таблице
- RIGHT — значение встречается только в правой таблице
Обычные статусы (вы можете выбрать, какой набор статусов будете использовать в форме управления данным инструментом):
- Группа BOTH :
  - L1R1 — в левои и правом диапазонах есть по одному значению
  - LnRn — в левои и правом диапазонах есть по нескольку (более 1) значений с каждой стороны.
  - L1Rn — слева — одно значение, справа — несколько
  - LnR1 — слева — несколько значений, справа — одно
- Группа LEFT :
  - L1R0 — в левом диапазоне одно значение, в правом такого нет
  - LnR0 — в левом диапазоне несколько одинаковых значений, в правом таких нет
- Группа RIGHT :
  - L0R1 — в левом диапазоне нет таких значений, в правом — одно
  - L0Rn — в левом диапазоне нет таких значений, в правом — несколько (более одного)

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

Можно ли давать фотографировать птс

Результаты работы инструмента

Вот как выглядят результаты сравнения примера из начала статьи

Мы видим, что получается следующая картина:

В левом списке есть 12 значений, которые отсутствуют в правом (статус L1R0 )
В левом списке задвоено значение 040310475653 (статус LnR1 )
Только 3 значения совпали в списках по принципу «один к одному» (статус L1R1 )
В правом списке есть 7 значений, которые отсутствуют в левом списке (статус L0R1 )
В правом списке есть 4 строки (две по 2 значения), которых нет слева и которые задвоены справа (статус L0Rn )
И слева, и справа есть 2 значения, которые встречаются единожды слева, но которые задвоены справа (статус L1Rn )

и режим объединенных списков:

Как видите, моя утилита всё разложила по полочкам!

Пользовательский интерфейс

Описание элементов управления:

Списки для сравнения — Левый список и Правый список
- Сравниваемые списки должны быть в составе одной книги Excel
- Списки могут располагаться на разных листах книги
- Если они располагаются на одном листе, то колонка левого списка должен быть действительно левее правой колонки
- Рекомендуется заполнять данные поля ввода при помощи кнопок Выбор
- Через кнопку Выбор достаточно указать одну ячейку нужной колонки, программа автоматически расширит ваш выбор на всю область в составе этого столбца, к которой относится указанная ячейка. В этой связи пустые ячейки в сравниваемых колонках рекомендуется заменять на какие-то текстовые константы, типа «Пусто».
Разное — Выделить цветом
- Если указана данная опция, то колонки со сравниваемыми значениями и колонка со статусами получают стандартное цветовое акцентирование, образцы которого вы можете видеть ниже
- Простые статусы:
- Обычные статусы:
Разное — Объединить списки
- По умолчанию эта опция не выбрана и списки сравниваются в том месте, где они расположены. Справа от колонки с данными вставляется столбец со статусами сравнения.
- Если эта опция выбрана, то создаётся новый лист, куда помещается таблица с объединенными уникальными значениями из сравниваемых списков. Данная таблица имеет 4 столбца: Значения , Кол-во слева , Кол-во справа , Статус .
- Данный режим удобен для детального анализа всех аномалий в данных. В частности только так видны конкретные количества строк у статусов с буквой «n» . Например, LnRn или L0Rn .
- Только в режиме объединения списка таблица может сортироваться, так как в проивном случае это могло бы повредить ваши данные — ведь указанный столбец может быть в составе большой таблицы, но определение координат этой таблицы (для сортировки) уже слишком выходит за рамки данного инструмента.
Разное — Учёт регистра
- Полезно при анализе текстовых списков, где может быть важен регистр текстовых значений.
Разное — Статистика в комм .
- При этом создаётся комментарий к ячейке, содержащей заголовок столбца со статусом сравнения данных.
- Таких комментариев 2, если списки сравниваются на своих оригинальных местах, и один, если они объединены.
- Комментарий выглядит примерно так
Разное — Простые статусы
- Осуществляется выбор между простыми и обычными статусами. Данные статусы были описаны выше.
Разное — Есть заголовки
- Указываем имеют ли ваши списки заголовки столбцов. По умолчания включено.
Где сравниваем? — Оригинальное место или Отдельный лист
- Настройка блокируется, если выбрана опция Объединить списки.
- В противном случае вы в праве выбрать место самостоятельно.
Сортировка — По значению или По статусу
- Настройка блокируется (по умолчанию), если не выбрана опция Объединить списки, так как сотрировка осуществляется только на отдельном листе.
Дополнительно — Альтернативные статусы
- По умолчанию отключено. Включаем, если вы хотите по каким-то своим причинам использовать свои статусы.
Дополнительно — Обычные статусы или Упрощённые статусы
- Статусы сохраняются на скрытом листе той книги, где вы воспользовались этим инструментом, и в следующий раз подгружаются оттуда на форму. То есть вам не придётся их вводить постоянно.
Сравнить списки
- Кнопка, которую необходимо нажать для запуска процедуры сравнения после того, как вы выбрали все необходимые вам опции.

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

Где в ворде корень квадратный

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

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

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

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

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

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

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

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

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

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

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

Как работает Vlookup в Excel при вставке значений без привязывания к таблице?

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

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

Метод секущих в excel

Итоги

Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.

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

Функцию можно вписать вручную или в специальном окне (Shift + F3).

Искомое значение – относительная ссылка, а таблица – абсолютная.

Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.

Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

голоса

Рейтинг статьи