Eurotehnik.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте так же:
Доступ к файлу невозможен проверьте следующее excel

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

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

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

Макрос впр в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте так же:
Как восстановить ссылки в яндекс браузере

Итоги

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

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

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

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

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

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

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