Как сделать сравнительную таблицу в excel по данным таблицы

Сравнительная диаграмма в Excel и примеры ее особенностей построения

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

Как сделать сравнительную диаграмму в Excel

Для сравнения разных типов данных могут использоваться следующие виды диаграмм:

  1. Круговые. Позволяют сопоставить доли отдельных элементов в общем значении. Такие диаграммы не совсем удобны для сравнения.
  2. Линейчатые. Позволяют сравнивать несколько значений в определенном диапазоне данных.
  3. Гистограммы. Вертикальные столбики для сопоставления значений в одном или нескольких диапазонах.
  4. Графики. Используются для иллюстрации тенденции изменения показателя. Если нужно сравнить несколько рядов данных, то в одной области строится несколько графиков.
  5. Точечные. Показывают взаимосвязь между двумя наборами данных. Представляют собой точки в области построения.
  6. Лепестковые. Отображают функциональную зависимость нескольких переменных. Данный тип диаграммы имеет вид криволинейного многоугольника с несколькими векторами, выходящими из центра. Векторные линии – оси координат по каждой категории.

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

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

Как программа воспринимает данные для столбчатых и линейчатых диаграмм и графиков:

  1. Каждая строка воспринимается Excel как отдельный ряд данных. На диаграмме это обособленная линия или столбик одного цвета.
  2. Каждое значение в строке – это точка на линейном графике или высота (длина) столбика.
  3. Заголовки строк – названия рядов данных (легенда).
  4. Заголовки столбцов – подписи одной из осей.

Как Excel интерпретирует данные для круговых диаграмм:

  1. Программе нужен только один ряд данных (один столбец со значениями).
  2. Каждое значение в столбце – это сектор круга.
  3. Заголовки строк – подписи секторов (легенда).
  4. Название столбца – наименование ряда данных.

В точечной диаграмме координаты каждой точки – значения из двух наборов данных. Из первого столбца – координата по оси Х. Из второго столбца – по оси Y.

Рассмотрим на примере, как строится лепестковая сравнительная диаграмма:

  1. Для сопоставления понадобятся статистические значения нескольких рядов данных. Возьмем темп роста ВВП и инфляцию в России за 2005-2015 годы. Показатели выражены в процентах. Инфляция.
  2. Выделяем всю таблицу, включая заголовки столбцов. На вкладке «Вставка» в группе «Диаграммы» выбираем тип Лепестковая с маркерами».
  3. Ряды данных – темп роста ВВП и инфляция. То есть получится два многоугольника. Категории (подписи горизонтальной оси) – годы. Категории.
  4. Получаем сравнительную лепестковую диаграмму с маркерами.

Сравнительная лепестковая.

Изменения ВВП и инфляции показаны относительно одного центра.

Показатели уровня экономического развития страны

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

  • уровень ВВП / НД на душу населения;
  • отраслевая структура экспорта и импорта;
  • уровень и качество жизни.

Ведущий показатель – ВВП.

Сравним с помощью гистограммы значения ВВП на душу населения в США, Великобритании, Японии, Канаде и России за последние три года:

ВВП.

Проанализируем диаграмму, сравнительные показатели экономического развития и сделаем все возможные выводы. Данные для построения взяты из Википедии (по списку МВФ). Сравнительные показатели экономического развития стран мало поменялись со времен Советского Союза. Вот диаграмма на основе значений 1991 года (ВВП на душу населения – цифра 1):

МВФ.

Сравним ВНД на душу населения в этих же странах в 2015 году. Используем линейчатую диаграмму:

ВНД.

Для иллюстрации уровня жизни населения возьмем индекс развития человеческого потенциала как наиболее широкий показатель, который можно представить в количественном виде. Помимо экономических данных, ИРЧП учитывает ожидаемую продолжительность жизни, уровень грамотности и образования. Сравним индексы по странам с помощью лепестковой диаграммы (данные взяты за 2014 год из Википедии):

Пример1.

Для сопоставления доли каждой отрасли в структуре экономики страны лучше использовать круговые диаграммы. Показатели по России за 2014 год:

Пример2.

Так как доля сельского хозяйства и других областей первичного сектора достаточно низкая, вторичные и третичные сектора (производство, услуги, образование) преобладают, Россию можно охарактеризовать как развитую страну. Но индекс человеческого развития немногим выше среднемирового значения (ИРЧП в 2014 году в РФ – 0,798, в том же 1985 – 0,811).

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

Sverit 2 tablici 1 8 способов как сравнить две таблицы в Excel Добрый день!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию