Меню

Vba excel сравнение значение столбцов



Сравнение таблиц в Excel с помощью макросов VBA

Статья даёт ответы на следующие вопросы:

  • Как сравнить две таблицы в Excel с помощью макросов VBA?
  • Как обращаться к ячейкам таблицы Excel с помощью VBA?
  • Как осуществлять перебор ячеек таблицы в цикле с помощью VBA?

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

В данной статье рассмотрим способ сравнения таблиц Excel с помощью VBA макросов на примере тех же исходных данных.

Проиллюстрируем задачу картинкой из первой статьи.

Для начала напишем алгоритм наших действий по сравнению таблиц.

  1. Определим диапазоны данных первой и второй таблицы, то есть найдем последние значимые строки и сохраним их номера в переменных (последняя строка таблицы 1 — last_i и последняя строка таблицы 2 — last_j).
  2. Начнем проходить по каждой строке таблицы 2 (внешний цикл), данные из которой нужно перенести в таблицу 1. С первой строки данных (в примере это строка 3) до последней строки таблицы 2.
  3. Для каждой строки таблицы 2 определим идентификатор строки, путем формирования строки, содержащей полный адрес квартиры (значения из нескольких колонок, разделенные дефисами).
  4. Начнем проходить по каждой строке таблицы 1 (внутренний цикл) с первой строки данных (в примере это строка 3) до последней строки таблицы 1, определяя при этом идентификатор строки.
  5. Сравним значения идентификаторов строк таблицы 1 и таблицы 2.
  6. Если идентификаторы равны, перепишем ФИО покупателя из ячейки таблицы 2 в соответствующую ячейку таблицы 1; прервем внутренний цикл по таблице 1 и перейдем к следующей строке таблицы 2 (переход к п.2).

Теперь остается реализовать алгоритм в виде программного кода макроса.

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

В появившемся диалоге выделим наш макрос и нажмем Изменить.

На экране откроется окно редактора макросов Visual Basic for Applications. В области кода (правая верхняя область) отображается код только что созданного пустого макроса.

В процедуру Макрос1 (между объявлениями начала и конца процедуры: Sub и End Sub) необходимо вставить код, решающий поставленную задачу. Образец кода представлен ниже.

Результат решения задачи:

Источник

Vba 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 — в левом диапазоне нет таких значений, в правом — несколько (более одного)

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

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

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

  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. Сравнить списки
    • Кнопка, которую необходимо нажать для запуска процедуры сравнения после того, как вы выбрали все необходимые вам опции.
Читайте также:  Сравнение фитнес часов xiaomi

Скачать утилиту

Можно здесь. Текущая версия 0.05 от 09.12.2014.

Источник

Сравнение двух столбцов листа и выведение совпавших данных

Доброго времени суток.

Возникла такая потребность:
имеется лист со столбцами A и B. В столбце A находятся данные, которые нужно сравнить с имеющимися в столбце B. Сравнивать ячейку нужно со всем столбцом B (не по парам). Если данные совпадают, то в столбец C нужно вывести значение из A.
Сложность в том, что в столбце A данные представлены в формате ФамилияИмя, а в столбце B — ФамилияИмяОтчество (пример во вложении).

Помогите, пожалуйста составить макрос.
Спасибо.

Сравнение двух столбцов листа и выведение данных из одног столбца
Добрый день! Уважаемые програмисты у меня такой вопрос ;если В > А надо вывести значение В в.

Сравнение двух столбцов в Excel и вывод несовпавших данных
Добрый день, Помогите, пожалуйста, составить макрос: имеется лист со столбцами A и B. В.

Сравнение диапазонов и заливка совпавших записей
Проблема в следующем: На листе1 книги имеется несколько блоков с данными (Дата, фамилия, и.

Сравнение двух столбцов
Добрый день. Имеется таблица с 3 столбцами. значения вводятся в ручную в 1 и 2 столбец.

Вложения

пример.xlsx (10.6 Кб, 974 просмотров)

Вложения

Сравнение 2 столбцов листа и выведение совпавших данных.xls (21.0 Кб, 1710 просмотров)

Вложения

2017.zip (86.6 Кб, 53 просмотров)

Вложения

2017.7z (72.6 Кб, 81 просмотров)

mc-black,
да этот провайдер привязан к офису
но таких провайдеров всего два
один для офисов 2003 и более пожилых — Microsoft.Jet.4.0 (вроде так называется)
а для более молодых вот этот — у меня офис 2010 и работает нормально с файлами xlsm
определить версию офиса можно программно и в зависимости от офиса переписывать строку Open

Добавлено через 7 минут
кстати сам макрос (как и пример) были созданы в офисе 2016

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Сравнение двух столбцов
Здравствуйте форумчане, подскажите как сделать сравнение следующим образом: В столбце А есть.

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

Сравнение двух столбцов
Есть 4 столбца Логин студента; номер вопроса по тесту; оценка препода и оценка студент. Есть.

Сравнение двух столбцов
Всем здравствуйте! Да, с таким названием тем достаточно, но я не нашел решения своей задачи. Есть.

Источник

Сравнение двух столбцов

Доброго всем дня. Нужна помощь.

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

Есть вот такой код, но он не работает как нужно, а сравнивает только находящиеся друг напротив друга значения. Подскажите, пожалуйста, как его улучшить.

Сравнение двух столбцов
Здравствуйте форумчане, подскажите как сделать сравнение следующим образом: В столбце А есть.

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

Макрос на сравнение двух столбцов
Всем добрый вечер. Помогите пожалуйста с макросом на сравнение двух столбцов, после чего он находит.

Сравнение двух столбцов из разных файлов
Доброго времени суток! Не геракл в VBA, но возникла такая потребность: Есть 2 абсолютно.

Hugo121, а, ну да, конечно:

Hilk, и зачем весть пост цитировать?

Решение

Hilk, код берет ячейку из первого столбца и проверяет, есть ли такое значение во втором (на любом месте). Если есть — в третью колонку ставится плюс, если нет — то минус. Что не так?

Читайте также:  Сравнение географического положения материков 7 класс таблица

Добавлено через 6 минут
А. Нужно наоборот. Тогда так:

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Сравнение двух столбцов и удаление неравных
Приветствую! Помогите пожалуйста, сижу в ручную убираю строки, потом подумала, что можно это.

Сравнение двух столбцов в разных файлах
добрый день есть два файла экселя каждый из 5 столбцов Наименование столбцов одинаковые но.

Сравнение двух столбцов из разных книг
Здравствуйте глубокоуважаемые. Нужна помощь в написании VBA скрипта. Вообщем есть 2 книги.

Сравнение двух столбцов по нескольким критериям
Добрый день. Очень надеюсь на Вашу помощь. Есть два диапазона данных (эти данные могут быть как.

Сравнение и распределение двух столбцов на разных листах
Всем привет! Задача довольно банальна: сравнить столбец «Идентификационный номер (2)» на 2-м листе.

Сравнение двух столбцов в Excel и вывод несовпавших данных
Добрый день, Помогите, пожалуйста, составить макрос: имеется лист со столбцами A и B. В.

Источник

Vba excel сравнение значение столбцов

  • Советы по Excel
  • Функции Excel
  • Формулы Excel
  • Советы по словам
  • Советы по Outlook

Как сравнить два столбца и выделить несогласованные данные в Excel?

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

The Compare Ranges utility of Kutools for Excel can help you solve the problem. See below screenshot:

  • Select the the two columns you will compare for unmatched data highlighting.
  • Select Different Value option.
  • Specify a background color for highlighting.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Как показано на скриншоте выше, вы сравниваете данные в столбце A и столбце B. Запустите следующий код VBA, чтобы выделить несопоставленные данные.

1. Нажмите другой + F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > модуль. Затем скопируйте и вставьте следующий код в окно кода.

Код VBA: сравните два столбца и выделите несогласованные данные

3. нажмите F5 ключ для запуска кода. В всплывающем Kutools for Excel в диалоговом окне выберите первый столбец, который необходимо сравнить, затем щелкните значок OK кнопка. Смотрите скриншот.

4. Затем выберите второй сравниваемый столбец в следующем всплывающем окне. Kutools for Excel диалоговое окно.

5. В диалоговом окне, как показано на скриншоте ниже, для выделения несогласованных данных щелкните значок Нет кнопку.

Затем все несогласованные данные между двумя столбцами немедленно выделяются. Смотрите скриншот:

Если вы просто хотите узнать и выделить, существуют ли значения ячеек в столбце в другом столбце или нет, например, как показано на скриншоте ниже, номер 2 в столбце A не существует в столбце B, вы можете попробовать Сравнить диапазоны полезности Kutools for Excel.

1. Нажмите Kutools > Вставить > Выберите одинаковые и разные ячейки. Смотрите скриншот:

2. в Выберите одинаковые и разные ячейки диалоговое окно необходимо настроить следующим образом:

3. Затем появится диалоговое окно, чтобы сообщить вам, сколько ячеек только в столбце A, но не в столбце B, нажмите кнопку ОК, и все совпавшие ячейки будут выбраны в столбце A.

Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Источник