Расширенное сравнение двух колонок (списков) – Макросы и программы VBA – Excel – Каталог статей – Perfect Excel

Как в Excel показать сравнить рост двух различных показателей? Можно «просто» сравнить данные. Правда, если они не сопоставимы, то особо ничего не увидишь. Например, можно построить гистограмму с двумя столбиками.

Или показать данные на объединенных графиках с помощью вспомогательной оси.

Но есть еще один вариант — пожалуй, самый наглядный: методом базисной подстановки, который любит журнал The Economist. Как это сделать в Excel, смотрите в инструкции.

А теперь по порядку:

1. Исходные данные — суммы розничных продаж и продаж на рынках за несколько лет.

1. Готовим данные для диаграммы.

• Рассчитываем рост продаж в процентах по отношению к первому году. А в столбце с первым годом (в таблице – 2008 год) проставляем 100%.

• Добавляем строку, которая будет показана на графике вместо оси Х. Назовем эту ось «псевдоось». Указываем значения в строке – 100% для всех ячеек и добавляем один «лишний» столбец – чтобы на графике появилось место для подписей.

• Следующие строки – маркеры для последнего года и их подписи.

В первом столбце укажите названия направлений и сумму продаж за последний год: «розница 21,4трлн.р.», «рынки 2,3трлн.р.» Значения всех столбцов периодов, кроме последнего — #Н/Д. В последнем столбце – процент фактического роста.

• И последнее – это рамка графика, где можно разместить подписи данных. В двух последних столбцах введите значение чуть больше максимального процента роста.

В итоге получится такая таблица:

1. Строим график: выделите исходные данные и нажмите Вставка -> График.

1. Настраиваем график:

• Удалите легенду.

• Измените тип диаграммы: Конструктор -> Изменить тип диаграммы.

Выберите комбинированную диаграмму и настройте типы диаграмм, как на рисунке.

• Выделите столбцы гистограммы правой кнопкой мышки, перейдите к Формату ряда данных. Задайте боковой зазор 0%.

• Щелкните по подписи оси Х правой кнопкой мышки, перейдите к Формату оси. В параметрах оси укажите ее положение – на делениях. Чтобы на оси легче читалось название последнего года, введите формулу в названии последнего столбца =» «&2012

• Настройте график «псевдооси» — выделите линию, задайте для нее темно-серый цвет и ширину 0,75 пт.

• Добавьте подписи для маркеров – выделите маркеры, перейдите в меню Конструктор -> Добавить элемент диаграммы -> Метки данных -> Справа.

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

1. Настройте цвета на диаграмме, её название и отображение осей. Диаграмма готова.

Больше интересных диаграмм в статье:

12 нестандартных диаграмм Excel

В этой статье мы собрали 12 необычных диаграммам Excel со ссылками на краткие инструкции по их построению.

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

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Office 365 и Excel для Mac

Как сравнить два файла Excel

Если у вас есть два файла Excel с несколькими листами, самый простой подход — выполнить полное сравнение листов с помощью сторонних инструментов.

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

Spreadsheet Compare

, доступное от SourceForge.

Загрузите и запустите приложение, которое установит надстройку в приложение Excel.

Spreadsheet Compare работает на всех версиях Excel после Excel 2000. Он совместим только с операционной системой Windows.

Чтобы использовать Spreadsheet Compare для сравнения двух файлов Excel:

1. Откройте оба файла Excel, которые вы хотите сравнить, и выберите меню «

   Надстройки»

   . В этом меню вы увидите три параметра: Полное сравнение, Быстрое сравнение и Сравнение диапазона.

2. Выберите

   Полное сравнение

   . Вы увидите всплывающее окно с обоими файлами в двух полях. Если файл до внесения изменений находится не в верхнем поле, просто нажмите кнопку «

   Поменять»

   . Затем выберите

   Далее

   .

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

   Далее,

   чтобы перейти к следующему окну.

4. Выберите листы, которые вы хотите сравнить, и нажмите

   Добавить,

   чтобы переместить эти листы на панель справа. Выберите

   Далее

   и сделайте то же самое для следующего листа.

5. Нажмите

   Далее,

   чтобы открыть окно с настройками конфигурации отчета. Измените их, если хотите, затем дважды нажмите

   Next

   и

   сравните,

   чтобы закончить.

6. Наконец, вы увидите, что каждый исходный лист обновляется с измененными ячейками, выделенными красным. Инструмент также создает третий лист с отчетом, в котором вычеркнуто старое значение и вместо него новое значение.

7. Этот инструмент представляет собой мощный способ сравнить целые рабочие листы Excel и увидеть быстрые результаты со всеми изменениями. Вы можете прокручивать и сохранять или удалять изменения, которые вы хотите сохранить.

Используйте Excel для сравнения двух листов

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

Сравнить таблицы Excel с помощью формул

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

Формула IF полезна для сравнения одной ячейки за раз, а также с помощью функции автозаполнения Excel, чтобы заполнить весь лист результатов результатами сравнения. Бесполезно сравнивать целые рабочие книги без большого количества дополнительной работы.

1. Чтобы начать сравнение двух листов, создайте третий лист. Пометьте его как «

   Результаты»,

   чтобы позже было легче определить лист результатов.

2. В ячейку

   A2

   таблицы результатов вставьте следующую формулу и нажмите

   Enter

   :

   = IF (Лист1! A2 <> Лист2! A2, «Лист1:» & Лист1! A2 & «И Лист2:» & Лист2! A2, «Без разницы») 

   Вы можете изменить эту формулу, чтобы начать с любой ячейки, которая вам нравится. Если данные на вашем листе начинаются со строки B и столбца 3, вы должны изменить формулу, чтобы использовать B3 вместо A2.

3. Эта формула сравнивает ячейку из Sheet1 с той же ячейкой в ​​Sheet2. Если ячейки совпадают, на листе результатов отобразится

   Без разницы

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

4. Щелкните по углу ячейки и перетащите указатель мыши по листу в последний столбец, в котором есть данные из других сравниваемых листов. Это заполнит формулу сравнения до последнего столбца и автоматически настроит ссылки на ячейки.

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

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

7. Использование формул — это один из самых простых способов сравнения отдельных листов, поскольку вам не нужно каким-либо образом изменять исходные листы.

Сравните листы Excel с условным форматированием

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

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

Вы можете использовать условное форматирование, чтобы определить различия.

1. Сначала выделите все ячейки в таблице результатов. Выберите меню «

   Домой»

   , затем выберите «

   Условное форматирование»

   в группе «

   Стили

   » и выберите «

   Новое правило»

   .

2. В

   открывшемся

   окне « Новое правило форматирования» выберите «

   Использовать формулу», чтобы определить ячейки для форматирования

   . В поле «

   Значения формата», где эта формула является истинным

   , вставьте следующую формулу.

   = Лист1! A2 <> Лист2! A2 

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

3. Выберите «

   Формат»

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

   ОК

   .

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

Сравнить листы Excel вручную

Один простой и быстрый способ сравнить две таблицы — сделать это визуально. Excel предоставляет простой способ визуального сравнения двух листов бок о бок.

1. На листе, где у вас есть два листа, которые вы хотите сравнить, выберите меню

   Вид

   . Выберите «

   Новое окно»,

   чтобы открыть ту же книгу в новом окне Excel на заднем плане.

2. Снова выберите меню «

   Вид»

   и нажмите «

   Просмотр рядом»

   . Это помещает два окна книги рядом друг с другом, каждое из которых занимает половину экрана.

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

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

Просмотр бок о бок в Excel 2010 и более ранних версиях

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

1. Откройте оба файла в одном окне Excel.

2. В меню «

   Вид»

   выберите «

   Упорядочить все»,

   чтобы разбить открытые файлы на несколько вложенных окон.

3. Выберите значок «

   Просмотр рядом»,

   чтобы просмотреть подокна рядом и прокрутите их одновременно.

Параметры облегчают сравнение двух файлов Excel

Опция, которую вы используете для сравнения данных в Excel, зависит от объема данных и места их хранения. Если у вас есть несколько листов в двух разных файлах Excel, лучше всего использовать стороннее дополнение, например Spreadsheet Compare.

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

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

Использование условного оператора ЕСЛИ

Метод использования условного оператора ЕСЛИ отличается тем, что для сравнения двух столбцов используется только необходимая для сравнения часть, а не весь массив целиком. Ниже описаны шаги по реализации данного метода:

Разместите оба столбца для сравнения в колонках A и B рабочего листа.

В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));””;A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.

Использование формулы подстановки ВПР

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

Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.

Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В противном случае Excelвернет ошибку #Н/Д.

Использование макроса VBA

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

Создайте новый модуль кода VBA и введите следующий код. О том, как писать макросы вы можете прочитать более подробно в статье написания VBA кода.

В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.

Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_Matches и щелкните кнопку выполнить.

После выполнения макроса, результат должен быть следующим:

Использование надстройки Inquire

С запуском версии 2013, Excel обзавелась интересной надстройкой под названием Inquire, которая позволяет сравнивать и анализировать два файла Excel. Если вам необходимо сравнить две версии одного и того же файла, например, после создания книги, ваши коллеги внесли кое-какие изменения, и вам нужно определить что именно они изменили, воспользуйтесь инструментом WorkbookCompare надстройки Inquire. Подробнее о том, как устанавливать, запускать и использовать надстройку читайте в моей предыдущей статье про надстройку Inquire.

Итог

Вам также могут быть интересны следующие статьи

–>

Расширенное сравнение двух колонок (списков)

–>Добавлять комментарии могут только зарегистрированные пользователи.–> [ –>Регистрация–> | –>Вход–> ]–>

Моментальное сравнение значений двух списков онлайн. Инструмент работает абсолютно бесплатно и сравнивает списки на уникальность и совпадение общих элементов в двух столбцах и по отдельности в каждом.

Инструкция

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

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

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

Дополнительно

Еще несколько важных критериев обработки данных, требующих внимания при работе со списками:

1. Автоматическое удаление лишних пробелов — двойной пробел, пробел в начале и в конце строки.
2. Чувствительность к регистру — приравнивает «Петров» и «пЕтРов» к дублям.

Обратная связь

Друзья, буду благодарен за любую обратную связь. Мне важно знать как работает инструмент и как его можно улучшить. Пишите комментарии на info@konstantinbulgakov.com. Добавляйте ссылку на этот и другие инструменты сайта в закладки и делитесь с коллегами.