Загрузка...Программа подстановки данных из одного файла в другой (замена функции ВПР)
Если вам надо сравнить 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 — в левом диапазоне нет таких значений, в правом — несколько (более одного)
- Группа BOTH :
Результаты работы инструмента
Вот как выглядят результаты сравнения примера из начала статьи
Мы видим, что получается следующая картина:
- В левом списке есть 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 при вставке значений без привязывания к таблице?
Когда задаются любые формулы в книге, при открытии ее в следующий раз идет по умолчанию пересчет этих формул. Не исключением является и использование функции ВПР. Предположим, что у нас данные в исходной таблице поменяются, тогда автоматически поменяются данные и в таблице, в которой применялась функция ВПР, а нам это не нужно по каким-либо причинам. В этом случае после применения функции нужно скопировать значения и вставить их с помощью специальной вставки, выбрав «Вставить» — «Значения» в меню «Специальная вставка». В результате таблица вставится только со значениями без формул.
Итоги
Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
Функцию можно вписать вручную или в специальном окне (Shift + F3).
Искомое значение – относительная ссылка, а таблица – абсолютная.
Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
