8 способов как сравнить две таблицы в Excel

8 способов как сравнить две таблицы в Excel

Добрый день!

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Простой способ, как сравнить две таблицы в Excel

Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

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

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

Как сравнить две таблицы в Excel функции ЕСЛИ

Этот вариант предусматривает использования логической функции ЕСЛИ и отличие этого способа в том что для сравнения двух столбцов будет использован не весь массив целиком, а только та ее часть, которая нужна для сравнения.

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

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

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

Источник:
http://topexcel.ru/8-sposobov-kak-sravnit-dve-tablicy-v-excel/

Сравнение 2 файлов в Excel на предмет различий

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

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

Таких действий множество. Давайте рассмотрим некоторые из них.

С какой целью проводится сравнение файлов Excel

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

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

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

Все способы сравнения 2 таблиц в Excel

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

Формула равенства и проверка на ложь-истину

Начнем, конечно, с наиболее простого метода. Таким методом сравнение документов возможно, причем в довольно широких пределах. Можно сопоставлять между собой не только текстовые значения, но и числовые. И давайте приведем небольшой пример. Допустим, у нас есть два диапазона с ячейками числового формата. Для этого достаточно просто написать формулу равенства =C2=E2. Если окажется, что они равны, в ячейке будет написано «ИСТИНА». Если же они будут отличаться, то «ЛОЖЬ». После этого нужно данную формулу перенести на весь диапазон с помощью маркера автозаполнения.

Теперь разница видна невооруженным глазом.

1

Выделение различающихся значений

Также можно сделать так, чтобы значения, которые отличаются между собой, были выделены особенным цветом. Это тоже довольно простая задача. Если вам достаточно найти отличия между двумя диапазонами значений или целыми таблицами, необходимо перейти на вкладку «Главная», и там выбрать пункт «Найти и выделить». Перед тем, как вы ее нажмете, не забудьте выделить набор ячеек, в которых сохраняется информация для сравнения.

В появившемся меню необходимо кликнуть по меню «Выделить группу ячеек…». Далее откроется окошко, в котором нам необходимо в качестве критерия выбрать отличия по строкам.

2 3

Сравнение 2 таблиц при помощи условного форматирования

Условное форматирование является очень удобным и, что важно, функциональным методом, позволяющим выбрать цвет, которым будет выделяться отличающееся или такое же значение. Найти этот параметр можно на вкладке «Главная». Там можно найти кнопку с соответствующим названием и в появившемся перечне выбираем «Управление правилами». Появится диспетчер правил, в котором нам надо выбрать меню «Создать правило».

4

Далее из перечня критериев нам нужно выбрать тот, где написано, что нужно использовать формулу для определения ячеек, которые будут отформатированы особенным образом. В описании правила нужно задать формулу. В нашем случае это =$C2<>$E2, после чего подтверждаем свои действия нажатием кнопки «Формат». После этого задаем внешний вид ячейки и смотрим, нравится ли он, через специальное мини-окошко с образцом.

Если все устраивает, нажимаем кнопку «ОК» и подтверждаем действия.

5

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

Функция СЧЁТЕСЛИ + правила для сравнения таблиц

Все методы, которые мы описали ранее, удобны для тех формат, формат которых един. Если же предварительно таблицы не были упорядоченными, то лучше всего метод, в котором необходимо сравнивать две таблицы с помощью функции СЧЕТЕСЛИ и правил.

Давайте вообразим, что у нас есть два диапазона с немного различающейся информацией. Перед нами стоит задача сравнивать их и понять, какое значение отличается. Для начала необходимо осуществить его выделение в первом диапазоне и перейти на вкладку «Главная». Там находим уже знакомый нам ранее пункт «Условное форматирование». Создаем правило и в качестве правила задаем использовать формулу.

В этом примере формула такая, как показана на этом скриншоте.

6

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

Функция ВПР для сравнения 2 таблиц

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

Читайте также  Как сделать триггер в excel?

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

7

Функция ЕСЛИ

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

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

8

С помощью формулы, которая использует функции ЕСЛИ , ЕСЛИОШИБКА и ПОИСКПОЗ можно перебрать все нужные элементы колонки А, а потом в колонке B. Если оно было обнаружено в колонке B и A, то оно возвращается в соответствующую ячейку.

Макрос VBA

Макрос – это самый сложный, но и наиболее продвинутый метод сравнения двух таблиц. некоторые варианты сравнения вообще невозможны без сценариев VBA. Они позволяют автоматизировать процесс и сэкономить время. Все нужные операции для подготовки данных, если их запрограммировать один раз, будут выполняться и далее.

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

Как провести сравнение файлов в Эксель

Если пользователь поставил перед собой задачу (ну или ему поставили ее) сравнить два файла, то это можно сделать сразу двумя методами. Первый из них – использование специализированной функции. Чтобы реализовать этот метод, следуйте инструкции:

  1. Откройте те файлы, сравнение которых требуется.
  2. Откройте вкладку «Вид» – «Окно» – «Вид рядом».

После этого два файла будут открыты одном документе Excel.

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

Условное форматирование для сравнения 2 файлов Эксель

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

Сперва нам необходимо перенести сравниваемые листы в один документ.

Чтобы это сделать, необходимо нажать по подходящему листу правой кнопкой мыши, после чего нажать во всплывающем меню на кнопку «Переместить или скопировать». Далее появится диалоговое окно, в котором пользователь может выбрать документ, в который данный лист необходимо вставить.

9

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

После этого переходим в окно условного форматирования и создаем новое правило. В качестве критерия используем подходящую в конкретном случае формулу, потом задаем формат.

Внимание: адреса ячеек нужно указывать те, которые на другом листе. Это можно сделать через меню ввода формулы.

Сравнение данных в Эксель на разных листах

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

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

С помощью условного форматирования можно осуществлять эффективное сравнение даже если фамилии работников располагаются в различном порядке.

Как сравнить 2 листа в таблице Эксель

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

Так можно осуществить сравнение строк по двум критериям – фамилия и заработная плата. Ну или любым другим, определенным пользователем. Для всех совпадений, которые удалось найти, записывается в ячейке, в которую вводится формула, число. Для Excel такое значение всегда будет истинным. Поэтому для того, чтобы форматирование применялось по отношению к тем ячейкам, которые были различны, необходимо это значение заменить на ЛОЖЬ , воспользовавшись функцией =НЕ() .

Средство сравнения электронных таблиц

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

Важно учесть, что это средство доступно лишь для тех пользователей, кто купил офисные пакеты «Профессиональный плюс».

Открыть его можно непосредственно с вкладки «Главная», выбрав пункт «Сравнить файлы».

После этого появится диалоговое окно, в в котором нужно выбрать вторую версию книги. Также можно ввести адрес в интернете, по которому эта книга расположена.

После того, как мы выберем две версии документа, нужно подтвердить свои действия с помощью клавиши ОК.

В ряде случаев может быть выдана ошибка. Если она появляется, это может указывать на защищенность файла паролем. После того, как вы нажмете кнопку ОК, будет предложено указать его.

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

Как интерпретировать результаты сравнения

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

Источник:
http://office-guru.ru/excel/listy-knigi/sravnenie-2-fajlov-v-excel-na-predmet-razlichij.html

Основные задачи в средстве сравнения электронных таблиц

Допустим, у вас есть две книги Excel или две версии одной и той же книги, которые вы хотите сравнить. А может быть, вам нужно найти потенциальные проблемы, такие как введенные вручную (а не вычисляемые) итоги или формулы с ошибками. Если у вас есть Microsoft Office профессиональный плюс 2013, вы можете с помощью средства сравнения электронных таблиц от корпорации Майкрософт создать отчет о найденных различиях.

Важно: Сравнение электронных таблиц поддерживается только в Office профессиональный плюс 2013 или Приложения Microsoft 365 для предприятий.

Открытие средства сравнения электронных таблиц

В Windows 7 В меню Пуск Windows в разделе Office инструменты 2013выберите пункт сравнить электронную таблицу.

В Windows 8 На начальном экране нажмите кнопку сравнить электронную таблицу. Если плитка » Сравнение электронных таблиц » не отображается, начните вводить слова Сравнение электронной таблицы, а затем выберите плитку.

Помимо средства сравнения электронных таблиц имеется и соответствующая программа для Access — средство сравнения баз данных (Майкрософт). Для нее также требуется Office профессиональный плюс 2013. (Чтобы найти ее в Windows 8, введите средство сравнения баз данных.)

Сравнение двух книг Excel

Выберите Главная > Сравнить файлы.

Появляется диалоговое окно Compare Files (Сравнение файлов).

Щелкните значок синей папки рядом с полем Compare (Сравнить), чтобы перейти к более ранней версии книги. Помимо выбора файлов, сохраненных на компьютере или в сети, можно также ввести веб-адрес, ведущий к книге, сохраненной на сайте.

Щелкните значок зеленой папки рядом с полем To (С чем), чтобы перейти к книге, которую нужно сравнить с более ранней версией, а затем нажмите кнопку ОК.

Совет: Можно сравнивать два файла с одинаковыми именами, если они хранятся в разных папках.

В левой области выберите элементы, которые нужно включить в результаты сравнения книг: установите или снимите флажки у таких элементов, как Formulas (Формулы), Macros (Макросы) или Cell Format (Формат ячеек). Или просто выберите вариант Select All (Выделить все).

Нажмите кнопку ОК, чтобы выполнить сравнение.

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

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

Интерпретация результатов

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

Примечание: Даже если лист скрыт, он все равно сравнивается и отображается в результатах.

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

В приведенном здесь примере результаты 4-го квартала (Q4) в более ранней версии не являются окончательными. Последняя версия книги содержит окончательные цифры для 4-го квартала в столбце E.

В результатах сравнения ячейки E2:E5 в обеих версиях выделены зеленым фоном, что означает изменение введенного значения. В результате изменения этих значений изменились и вычисляемые результаты в столбце YTD — ячейки F2:F4 и E6:F6 выделены сине-зеленым фоном, что означает изменение вычисленного значения.

Вычисляемый результат в ячейке F5 тоже изменился, но гораздо важнее то, что в более ранней версии его формула содержала ошибку (суммировались только ячейки B5:D5, а значение Q4 пропускалось). После обновления книги формула в ячейке F5 была исправлена и теперь имеет вид =СУММ(B5:E5).

Если содержимое не умещается в ячейках, выберите команду Resize Cells to Fit (Размер ячеек по размеру данных).

Надстройка средства диагностики в Excel

Помимо средства сравнения электронных таблиц в Excel 2013 есть надстройка средства диагностики электронных таблиц, при включении которой появляется вкладка «Inquire» (Запрос). На этой вкладке можно проанализировать книгу, посмотреть на связи между ячейками, листами и другими книгами, а также убрать с листа лишнее форматирование. Если в Excel открыты две книги, которые нужно сравнить, можно запустить средство сравнения электронных таблиц с помощью команды «Сравнить файлы».

Читайте также  Как в excel сделать отрицательное число

Если вкладка «Inquire» (Запрос) не отображается в Excel 2013, см. статью Включение средства диагностики электронных таблиц. Подробнее об инструментах этой надстройки см. в статье Возможности средства диагностики электронных таблиц.

Дальнейшие действия

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник:
http://support.microsoft.com/ru-ru/office/%D0%BE%D1%81%D0%BD%D0%BE%D0%B2%D0%BD%D1%8B%D0%B5-%D0%B7%D0%B0%D0%B4%D0%B0%D1%87%D0%B8-%D0%B2-%D1%81%D1%80%D0%B5%D0%B4%D1%81%D1%82%D0%B2%D0%B5-%D1%81%D1%80%D0%B0%D0%B2%D0%BD%D0%B5%D0%BD%D0%B8%D1%8F-%D1%8D%D0%BB%D0%B5%D0%BA%D1%82%D1%80%D0%BE%D0%BD%D0%BD%D1%8B%D1%85-%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86-f2b20af8-a6d3-4780-8011-f15b3229f5d8

4 техники анализа данных в Microsoft Excel

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

Тренер Учебного центра Softline с 2008 года.

1. Сводные таблицы

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

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

Как работать

Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.

  1. Откройте файл с таблицей, данные которой надо проанализировать.
  2. Выделите диапазон данных для анализа.
  3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
  4. Должно появиться диалоговое окно «Создание сводной таблицы».
  5. Настройте отображение данных, которые есть у вас в таблице.

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

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

Можно её детализировать, например, по странам. Переносим «Страны».

Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

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

Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

Как работать

  1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
  2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
  3. Выделите диапазон данных для анализа.
  4. На вкладке «Вставка» есть кнопка 3D-карта.

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

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

3. Лист прогнозов

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

Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).

Как работать

  1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
  2. Выделите два ряда данных.
  3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
  4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
  5. Выберите дату окончания прогноза.

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

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

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

4. Быстрый анализ

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

Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

Как работать

  1. Откройте таблицу с данными для анализа.
  2. Выделите нужный для анализа диапазон.
  3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.

Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.

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

Источник:
http://lifehacker.ru/analiz-dannyx-v-ms-excel/

Формула ВПР в Excel для сравнения двух таблиц — 4 способа

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

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

Итак, вот наши исходные данные.

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

Для этого используем несколько способов.

  1. При помощи функции ВПР для каждого товара из прайс-листа №2 будем искать цену в первом прайс-листе и выводить ее рядом с новой. Так легче будет определить различия.
  2. Построим сводную таблицу по данным обоих прайс-листов, и там эти отличия увидим.
  3. Используем стандартную операцию сравнения.
  4. Применим формулу массива.

1. Используем ВПР, чтобы сравнить две таблицы.

Создадим именованный диапазон B4:C19 и назовем его «прайс1». Так нам будет проще ссылаться на первоначальные данные.

Добавим к новым данным еще одну колонку и назовем ее «Цена старая». Для каждого наименования из прайс-листа №2 найдем соответствующую ему цену в №1.

В Н4 вводим формулу

и копируем ее вниз по столбцу.

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

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

Для этого используем функцию ЕСЛИОШИБКА и вместо #Н/Д выведем ноль.

Изменим нашу формулу:

Теперь мы можем рассчитать отклонения новой цены от старой.

Можно показать результаты сравнения двух таблиц с использованием ВПР более наглядно и красиво. Давайте результаты сравнения вынесем отдельно.

Согласитесь, что такое сравнение выглядит гораздо аккуратнее и нагляднее.

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

То есть, ключевым является выражение ЕСЛИОШИБКА(ВПР(F4;прайс1;2;0);0).

Если найденное значение равно «новой» цене из ячейки G4, то выводим пустой пробел “”.

Значения смежных ячеек привязаны к этому результату.

Если ячейка J4 пуста, тогда ничего не выводим и в остальных:

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

Но есть один существенный недостаток в таком сравнении таблиц с использованием функции ВПР. Мы сравнили новые значения и старые, нашли изменения и новые товары. Но если какой-то товар ранее существовал, но теперь отсутствует, то этого мы не заметим. Придется повторить весь процесс в обратную сторону, взяв теперь за базу первую таблицу и сопоставляя ее со второй.

То есть, сравнивать придется в двух направлениях.

Согласитесь, не всегда хочется делать двойную работу.

2. Сравнение при помощи сводной таблицы.

Поскольку структура сравниваемых данных одинакова, то мы можем объединить их. Чтобы различить, откуда взяты какие значения, добавьте еще один столбец и укажите там источник данных – прайс1 или прайс2.

Используя наш предыдущий пример, это можно сделать следующим образом:

Теперь через меню Вставка-Сводная таблица создадим свод, можно на этом же листе для наглядности.

Читайте также  Как сделать скрывающиеся строки в excel?

Как видите, сводная таблица выводит в алфавитном порядке все уникальные (неповторяющиеся) значения из обоих прайс-листов и напротив каждого из них проставляет соответствующую цену. Так очень легко можно отследить все изменения.

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

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

Главный недостаток здесь – данные нужно предварительно подготовить, объединив их в единый массив.

Следует также отметить, что с большими объемами данных сводные таблицы умеют работать гораздо быстрее, чем ВПР.

3. Стандартное сравнение.

Это самые простой и элементарный способ сравнить два столбца Excel на совпадения. Работать таким образом возможно как с числовыми значениями, так и с текстовыми.

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

Для примера сравним два прайса, записав в столбце I условие совпадения цены

При равенстве мы получим ответ «ИСТИНА», а если совпадения нет, будет «ЛОЖЬ». Копируем из I4 вниз по столбцу.

Этот способ сравнения таблиц – самый элементарный, поэтому останавливаться на нем более не будем.

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

Здесь все гораздо сложнее. Вновь вернемся к нашим исходным данным и разместим списки товаров и цен на двух листах рабочей книги: «Прайс1» и «Прайс2».

Создадим из наименований товаров в каждой из таблиц именованный диапазон, как это показано на рисунке.

Назовем их соответственно «прайс_1» и «прайс_2». Так нам легче будет разбираться в формулах.

Результаты сравнения таблиц вынесем также на отдельный лист «Сравнение».

В ячейке A5 запишем формулу

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(прайс_1; ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;прайс_1);0)); ИНДЕКС(прайс_2;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;прайс_2);0)));»»)

Поскольку это формула массива, то не забудьте завершить ее ввод комбинацией клавиш Ctrl+Shift+Enter.

В результате получим список уникальных (неповторяющихся) значений из всех имеющихся у нас наименований товаров.

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

Функция ПОИСКПОЗ вычисляет номер позиции этого уникального значения и передает его в функцию ИНДЕКС, которая, в свою очередь, по номеру позиции извлекает значение из массива и записывает его в ячейку.

Поскольку это формула массива, то мы последовательно проходим по всему списку от начала до конца, повторяя все эти операции.

Если первая таблица закончилась, то возникает ошибка. ЕСЛИОШИБКА реагирует на это и начинает таким же образом перебирать значения второй таблицы. Когда и там возникает ошибка, то возвращается пустая строка “”.

Скопируйте эту формулу по столбцу вниз. Список уникальных значений готов.

Затем добавим еще два столбца, в которых при помощи функции ВПР запишем результат сравнения двух таблиц по каждому наименованию товара.

Не забудьте, что это тоже формула массива (Ctrl+Shift+Enter).

Можно для наглядности выделить несовпадения цветом, используя условное форматирование.

Напомним, что для этого надо использовать меню Главная – Условное форматирование – Правила выделения ячеек – Текст содержит…

Ну и если значение существует в таблице, то логично было бы его вывести в таблице сравнения.

Заменим в нашей формуле значение «Есть» на функцию ВПР:

В итоге наше формула преобразуется к виду:

Аналогично в С5 :

Напомним, что на листах Прайс1 и Прайс2 находятся наши сравниваемые таблицы.

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

Источник:
http://mister-office.ru/funktsii-excel/vlookup-two-sheet.html

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

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

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

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

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

Хотя есть способы, когда такое сравнение делается довольно быстро. Например, следующий способ. Выделяем столбцы таблицы, строки в которых необходимо сравнить на совпадение или различие. Затем в правом верхнем углу находим иконку бинокля (найти и выделить), нажимаем на нее и в открывшемся меню выбираем «выделение группы ячеек».

Нажимаем на эту строку. Откроется окно, где необходимо поставить галочку в пункте «отличия по строкам».

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

В другом варианте необходимо будет вводить в ячейку рядом со сравниваемыми значениями формулу: =B2=C2. По мере введения формулы. Будет обозначаться и та ячейка, формулу которой вы вводите.

Затем нажимаем клавишу «Enter» и, если значения различаются в этой строке отобразится слово «ЛОЖЬ», если же одинаковы, то появится слово «ИСТИНА».

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

Когда отпустим уголок, в ячейках появятся слова или «ИСТИНА», или «ЛОЖЬ».

Можно сравнить данные ячеек и при помощи условного форматирования. Для этого выделяем таблицу. Затем идем в пункт «Условное форматирование», где в выпадающем меню выбираем пункт «Создать правило».

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

В строке «форматировать значения…» вводим обозначения сравниваемых ячеек. Вписываем следующую формулу: =$B2 $C2 Это вариант для моего примера. У вас первые две верхние строчки таблицы могут обозначаться иначе.

После этого нажимаем кнопку «формат» и в появившемся окне задаем стиль отображения результата.

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

В процессе всех сделанных изменений получили окрашенные ячейки по заданному сравнению.

Таким образом у вас будут выделены все ячейки, в которых значения различаются.

Как удалить дубликаты в excel без сдвига ячеек?

При заполнении таблицы, особенно текстовыми данными, например список людей или что-то подобное, может возникнуть ситуация, когда вы запишите подряд несколько одинаковых значений. Хорошо, если таблица маленькая, то вы сразу обнаружите дубликаты. А если она большая? Сразу и не заметить. Но если вы заметили дубликат и

удалили его простым нажатием на клавишу «Delete». То у вас останется пустая ячейка, а так не должно быть. Удаляя же ячейки в самой программе, они сдвинутся или удалятся соседние данные, так как удалить одну ячейку в excel невозможно. Удаляется или строка целиком, или столбец. Как быть?

В новых версиях excel имеется полезная кнопка удалить дубликаты. Найти ее можно во вкладке «Данные».

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

Они разбросаны по столбцу и удалить их вручную сложно. Ставим курсор в этот столбец и нажимаем на кнопку «удалить дубликаты«.

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

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

Теперь нажимаем ОК и видим, что в столбце удалены дубликаты, а уникальные данные остались.

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

Как остсортировать дубликаты в excel?

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

Итак, имеем список с фамилиями, именами и отчествами, среди которых «закрались» дубликаты. Их то нам надо и отфильтровать. Ставим курсор в любом месте таблицы и идем во вкладку «данные». Здесь находим кнопку «дополнительно» и нажимаем на нее.

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

В результате список уменьшился, а в нумерации строк листа исчезли некоторые цифры. Они теперь идут не по порядку.

Выделяем красным цветом оставшийся список и нажимаем на кнопку фильтр с иконкой воронки.

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

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

Источник:
http://fast-wolker.ru/kak-v-eksel-sravnit-dva-stolbca.html