Excel: как рассчитать разницу между двумя сводными таблицами
В следующем примере показано, как вычислить разницу между двумя сводными таблицами в Excel.
Пример: расчет разницы между двумя сводными таблицами
Предположим, у нас есть следующие две сводные таблицы, которые показывают общие продажи и возвраты для разных магазинов за два разных года:
Предположим, мы хотели бы рассчитать разницу в столбцах « Сумма продаж» и «Сумма возвратов» между двумя сводными таблицами.
Чтобы вычислить разницу в столбцах « Сумма продаж» между двумя сводными таблицами только для магазина A , мы можем ввести следующую формулу:
=GETPIVOTDATA("Sum of Sales", $E$2 ,"Team","A")-GETPIVOTDATA("Sum of Sales", $E$10 ,"Team","A") На следующем снимке экрана показано, как использовать эту формулу на практике:
Обратите внимание, что эта формула правильно вычисляет разницу между значениями суммы продаж между 2020 и 2019 годами для магазина А.
Разница = 38 – 16 = 22 .
Мы можем изменить значения в формуле GETPIVOTDATA , чтобы вычислить разницу между каждым соответствующим значением в двух сводных таблицах:
Примечание.Полную документацию по функции ПОЛУЧИТЬСВОДНЫЕДАННЫЕ в Excel можно найти здесь .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как посчитать разницу в Excel
В программе Эксель очень удобно делать различного рода расчеты, как простые (суммирование, умножение, деление), так и с использованием формул. Я уже рассказывала, как рассчитать сумму в Эксель, и статью можете прочесть на сайте. Разбирали мы функции СУММЕСЛИ и СУММЕСЛИМН.
Сейчас давайте рассмотрим еще один простой вид расчета – вычитание. Покажу, как его применить к обычным числам, или к тем значениям, формат которых изменен на денежный, даты или время.
Обычные числа
Начнем с самого простого – использование программы в качестве калькулятора. Для этого выделите любую ячейку, поставьте в нее знак «=» (он вводится с клавиатуры), наберите первое число, ставьте «-» (он тоже на клавиатуре), набирайте второе и так все значения, которые необходимо отнять.
В конце нажмите «Enter» и расчет будет выполнен.
Если же у Вас на рабочем листе есть заполненные данные, которые требуется вычесть, тогда переходим на ячейку, в которой будет результат ( А4 ). Поставьте в нее знак «=» , а потом выделите сначала число от которого вычитаете ( А3 ), ставьте «-» , затем выделяем второе значение ( В3 ).
Нажимайте «Enter» и появится результат. В строке формул можно посмотреть, из каких ячеек берутся данные для расчета.
Даты
Если с вычитанием чисел понятно, тогда перейдем к датам. Они должны быть вписаны в блоки и для каждой нужно указать числовой формат «Дата» .
Давайте отнимем от нее определенное количество дней. Выберите любую ячейку ( Е3 ), впишите в нее «=» . Потом кликните по дате ( Е1 ), поставьте минус и впишите нужное число дней.
В результате формат ячейки изменится, и в ней отобразится та дата, которая меньше изначальной на то значение, которое Вы указали.
Для того чтобы отнять между собой две даты и узнать между ними разницу в днях, ставим в пустом блоке равно ( Е4 ), выделяем позднее значение ( Е1 ), потом раннее ( D1 ).
Нажимаем «Enter» и получаем результат. То есть, между датами 5 октября и 12 сентября 23 дня.
Также для расчета разницы дат можно использовать формулу РАЗНДАТ() . У нее в скобках указываются следующие аргументы: сначала раннее потом позднее значение и в чем хотим получить результат (день, месяц, год).
Подробнее про последний аргумент. Если хотите получить результат в днях, укажите «d» , месяцах – «m» , годах – «y» . Если нужны только дни, и не хотите учитывать года, тогда «yd» (например, разница 5 лет 23 дня, останется только 23), дни без учета месяцев – «md» , только месяцы – «ym» .
Отнимем для примера 12.09.2016 и 05.10.2017. Поставьте в ячейку «=» и наберите «РАЗНДАТ» со скобками. Внутри них ставим курсив и выделяем сначала раннюю ( D1 ), затем позднюю ( Е1 ) дату. В конце укажите нужную букву. В примере считаем разницу в полных месяцах. Между аргументами ставьте «;» без пробелов. Получилась формула:
Нажмем «Enter» и получим результат. Между указанными значениями у нас ровно 12 месяцев.
Воспользоваться формулой можно и иначе: сразу в строке формул укажите нужные значения, взяв их в двойные кавычки.
Время
Для расчета разницы во времени, нужно чтобы для них был выбран числовой формат «Время» . У меня это ячейки G1 и H1 . Та, в которой будет расчет ( Н3 ), может быть и общего формата. Ставим в нее равно и вычитаем от большего значения времени меньшее.
После нажатия «Enter» отобразится разница и у ячейки изменится формат.
Если нужна разница в минутах, тогда ее можно рассчитать. Получившееся количество часов (10) умножьте на 60 и прибавьте минуты (23).
Получилось 623 минуты.
Денежные единицы
Чтобы отнять между собой значения в ячейках, где выбран денежный формат, используется самый простой способ. Выберите пустой блок, поставьте в нем знак равенства и отнимете значения, выделяя их по очереди.
После нажатия уже знакомой кнопки, получим значение, после которого также будет стоять символ выбранной денежной единицы.
Вот такими способами получится посчитать разницу между числами, датами, временем, или денежными единицами в Эксель.
Подскажите как вычислить разницу между колонками в excel? Буду благодарен за ответ
вычислить разницу первых двух ячеек, потом схватить мышкой за правый нижний уголок ячейки с результатом разницы и потянуть вниз
Есть 2 ячейки, например А и В. В этой же строке нужно поставить равно, затем нажать на ячейку А, ввести знак «минус» , Нажать на В и Ентер. Если я правильно понял вопрос в ячейке С должно быть число-разница.
Похожие вопросы
покупка
Как сравнить два столбца и посчитать различия в Excel?
Если есть два столбца, которые содержат повторяющиеся значения, как вы можете сравнить столбцы и подсчитать разные значения только в Excel? В этой статье я расскажу о методах подсчета различий по ячейкам или строкам между двумя столбцами / диапазонами.
Сравните и подсчитайте различия между двумя столбцами по ячейкам
Сравните и подсчитайте различия между двумя диапазонами по строкам
Сравните и подсчитайте различия между двумя столбцами по ячейкам
Метод 1 Условное форматирование и функции фильтрации
Чтобы сравнить два столбца и подсчитать различия по ячейкам, вы можете использовать Условное форматирование сначала выделите дубликаты, а затем воспользуйтесь функцией Фильтр для подсчета общих различий.
1. Выберите столбец, в котором нужно подсчитать различия, нажмите Главная > Условное форматирование > Новое правило.
2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел, затем введите эту формулу = counttif ($ C: $ C, $ A1) в Формат значений, где эта формула истинна текстовое окно.
3. Нажмите Формат, под Заполнять на вкладке выберите цвет дубликатов.
4. Нажмите OK > OK, теперь дубликаты выделены определенным цветом фона.
5. Выберите столбец, в котором выделены некоторые ячейки, щелкните Данные > Фильтр, а затем нажмите Фильтр значок для выбора Фильтр по цвету > Без заливки.
Теперь различия в этом столбце отфильтрованы. Выберите отфильтрованные данные и перейдите к Статус бар, вы можете увидеть количество.
Наконечник: В формуле =countif($C:$C, $A1) , A1 — первая ячейка столбца, в котором вы хотите посчитать различия, столбец C — это еще один столбец, с которым вы хотите сравнить.
Метод 2: выберите одинаковые и разные ячейки
Если у вас есть Kutools for Excel установлен, вы можете использовать его Select Same & Different Cells утилита для быстрого подсчета различий по ячейкам или строкам между двумя столбцами за 3 шага.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Кутулс > Select > Select Same & Different Cells .
2. в Select Same & Different Cells диалоговом окне выберите два столбца без заголовков в Find values in и According to текстовые поля, отметьте Single cell и Different Values настройки.
3. Нажмите Ok, появится диалоговое окно, в котором указано количество разницы в столбце.
Сравните и подсчитайте различия между двумя диапазонами по строкам
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Kutools > Выберите > Select Same & Different Cells .
2. в Select Same & Different Cells диалоговом окне выберите два столбца без заголовков в Find values in и According to текстовые поля, отметьте Each row и Different Values настройки.
3. Нажмите Ok, были выбраны разные строки в столбце (который находится в поле «Найти значения в текстовом поле»), и в открывающемся диалоговом окне отображается количество счетчиков.