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

Как в эксель посчитать разницу между двумя столбцами

  • автор:

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

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

Способ 1. Использование формулы рабочего листа

  1. Запустите Excel.
  2. В качестве примера введите в новом листе введите следующие данные (оставьте столбец B пустым):
A B C
1 1 3
2 2 5
3 3 8
4 4 2
5 5 0

Повторяющиеся числа отображаются в столбце B, как в следующем примере:

A B C
1 1 3
2 2 2 5
3 3 3 8
4 4 2
5 5 5 0

Способ 2. Использование макроса Visual Basic

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

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

  1. Запустите Excel.
  2. Нажмите ALT+F11, чтобы запустить редактор Visual Basic.
  3. В меню Вставка выберите Модуль.
  4. Введите следующий код на листе модуля:

Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant ' Set CompareRange equal to the range to which you will ' compare the selection. Set CompareRange = Range("C1:C5") ' NOTE: If the compare range is located on another workbook ' or worksheet, use the following syntax. ' Set CompareRange = Workbooks("Book2"). _ ' Worksheets("Sheet2").Range("C1:C5") ' ' Loop through each cell in the selection and compare it to ' each cell in CompareRange. For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 1) = x Next y Next x End Sub
    Введите в качестве примера следующие данные (оставьте столбец B пустым):
A B C
1 1 3
2 2 5
3 3 8
4 4 2
5 5 0

Примечание: Если вы не видите вкладку Разработчик, возможно, вам понадобится включить ее. Для этого выберите Файл > Параметры > Настроить ленту, а затем выберите вкладку Разработчик в поле настройки справа.

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

A B C
1 1 3
2 2 2 5
3 3 3 8
4 4 2
5 5 5 0

Как вычесть два столбца в сводной таблице в Excel

Как вычесть два столбца в сводной таблице в Excel

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

Шаг 1: введите данные

Во-первых, давайте введем следующие данные для трех разных отделов продаж:

Шаг 2: Создайте сводную таблицу

Чтобы создать сводную таблицу, щелкните вкладку « Вставка » на верхней ленте, а затем щелкните значок «Сводная таблица»:

В появившемся новом окне выберите A1: C16 в качестве диапазона и поместите сводную таблицу в ячейку E1 существующего рабочего листа:

После того, как вы нажмете OK , в правой части экрана появится новая панель полей сводной таблицы .

Перетащите поле « Команда» в поле « Строки », а поля « Продажи » и « Возвраты» — в поле « Значения »:

Сводная таблица будет автоматически заполнена следующими значениями:

Шаг 3: вычтите два столбца из сводной таблицы

Предположим, мы хотим создать новый столбец в сводной таблице, отображающий разницу между столбцами « Сумма продаж» и «Сумма возвратов» .

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

В появившемся новом окне введите все, что хотите, в поле «Имя», затем введите = «Продажи — Возврат» в поле «Формула».

Затем нажмите « Добавить », затем нажмите «ОК ».

Это вычисляемое поле будет автоматически добавлено в сводную таблицу:

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Как в Эксель посчитать разницу столбцов (вычесть столбец из столбца)?

Как в Экселе (Excel) найти разницу между всеми ячейками столбцов и разницу между столбцами в целом?

комментировать
в избранное
11 ответов:
59617­ 982 [14.7K]
4 года назад

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

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

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

автор вопроса выбрал этот ответ лучшим
комментировать
в избранное ссылка отблагодарить
Extri­ mal [181K]
2 года назад

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

Чтобы посчитать количество столбцов, которых бывает очень много и в ручную это делать долго, можно сделать это благодаря формуле. Сначала мы выделяем все столбцы, которые нам необходимо посчитать. Затем мы создаём новый столбец и указываем формулу =С3-D3, и видим результат.

комментировать
в избранное ссылка отблагодарить
Ксарф­ акс [156K]
4 года назад

Рассмотрим как посчитать разницу между столбцами в Эксель на конкретном примере.

В таблице имеются данные о квартальной прибыли организации за 2017 и 2018 год.

Нужно узнать разницу в прибыли за каждый квартал 2017 и 2018 года и разницу в прибыли между двумя годами в целом.

Сделать это очень просто:

1) Создаём специальный столбец, в котором будут производиться вычисления.

2) Ставим курсор в 1 ячейку столбца (то есть E3) и в строке формул пишем:

3) Затем копируем эту формулу в нижестоящие ячейки, адреса ячеек из столбцов C и D будут меняться:

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

Положительно значение говорит о том, что прибыль в 2018 году по сравнению с 2017 годом упала, а отрицательное значение — о том, что прибыль в 2018 году наоборот увеличилась.

Для наглядного отображения данных можно применить условное форматирование:

Если значение в ячейке > 0, то прибыль упала — выделяем красным цветом.

1) Выделяем столбец E.

2) Нажимаем на кнопку «Условное форматирование» (панель инструментов «Главная») -> «Правила выделения ячеек» -> «Больше».

3) В открывшемся окне ставим значение 0 и красный тип заливки.

4) Теперь также нажимаем на «Условное форматирование» ) -> «Правила выделения ячеек» и выбираем «Меньше».

5) В итоге получим следующее оформление:

Также замечу, что можно было бы сделать и наоборот — вычитать из столбца 2018 года столбец 2017 года (например, D3 — C3).

Тогда бы получилось всё наоборот — значение > 0 означало бы увеличение прибыли, а значение < 0 - уменьшение прибыли.

комментировать
в избранное ссылка отблагодарить
2 года назад

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

Если вам не нужны отрицательные значения в ячейках, то можно сделать так:

1) В формулу добавить функцию ABS — это модуль числа

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

После этого можно использовать условное форматирование зелёным/красным цветом.

Пример

Исходная таблица такая:

В ячейку D3 можно записать формулу =C3-B3, а затем протянуть её на весь столбец. Тогда бы в ячейке D15 мы бы получили искомую разницу столбцов.

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

=СЦЕПИТЬ(ЕСЛИ(B3>C3;­ «прибыль»;»убыток»);­ » «;ABS(B3-C3);» €»)

С помощью функции ЕСЛИ проверяется разность между доходом и расходом. Если доход больше, то пишем «прибыль», если меньше — «убыток». А затем с помощью функции СЦЕПИТЬ добавляем в запись числовое значение.

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

Чтобы было красивее, сделаем условное форматирование. Выделяем весь диапазон с результатами и нажимаем на кнопку «Условное форматирование» -> «Правила выделения ячеек» -> «Текст содержит».

Получится такая итоговая таблица:

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

1) Выделите сначала первый столбец — в строке состояния будет записана сумма всех чисел в нем (вместе с количеством и средним значением).

Запишите эту цифру в отдельную ячейку.

2) Также поступите и со вторым столбцом.

3) А затем просто пропишите формулу, в которой из одной ячейки отнимается другая. Например, «=D10-D11».

Это и будет разница столбцов.

Как сравнить два столбца в Excel на совпадения: 6 способов

Обзор смартфона Honor X7b: три дня без подзарядки

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

Как сравнить два столбца в Excel на совпадения: 6 способов

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

Подпишитесь на наши каналы, чтобы не пропустить интересные новости и полезные статьи

Подпишитесь на наши каналы, чтобы не пропустить интересные новости и полезные статьи

1 Сравнение с помощью простого поиска

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

  1. Перейти на главную вкладку табличного процессора.
  2. В группе «Редактирование» выбрать пункт поиска.
  3. Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
  4. Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.

Как сравнить два столбца в Excel на совпадения: 6 способов

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

Как работает округление чисел в Эксель: принципы и настройки

2 Операторы ЕСЛИ и СЧЕТЕСЛИ

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

  1. Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
  2. В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
  3. Протянуть формулу до конца столбца.

Как сравнить два столбца в Excel на совпадения: 6 способов

Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.

Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.

Как сравнить два столбца в Excel на совпадения: 6 способов

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

Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.

Как сравнить два столбца в Excel на совпадения: 6 способов

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

Как сравнить два столбца в Excel на совпадения: 6 способов

3 Формула подстановки ВПР

Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.

Как сравнить два столбца в Excel на совпадения: 6 способов

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

4 Функция СОВПАД

Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:

  1. В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
  2. Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
  3. Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.

Как сравнить два столбца в Excel на совпадения: 6 способов

Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».

Как сравнить два столбца в Excel на совпадения: 6 способов

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

Как в Экселе посчитать сумму определенных ячеек

5 Сравнение с выделением совпадений цветом

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

Порядок действий для применения методики следующий:

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

Как сравнить два столбца в Excel на совпадения: 6 способов

Как сравнить два столбца в Excel на совпадения: 6 способов

Как сравнить два столбца в Excel на совпадения: 6 способов

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

6 Надстройка Inquire

Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации.

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

Процесс использования надстройки включает такие действия:

  1. Перейти к параметрам электронной таблицы.
  2. Выбрать сначала надстройки, а затем управление надстройками COM.
  3. Отметить пункт Inquire и нажать «ОК».
  4. Перейти к вкладке Inquire.
  5. Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare.
  6. В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах.

Добавить комментарий

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