Формула разницы в Excel

Обновлено: 20.11.2024

Алекс Эдвардс

Алекс Эдвардс
Excel Writer

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

Как работает процент изменений

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

Например, если цена галлона бензина вчера по дороге домой составляла 2,999 доллара США, а сегодня утром, когда вы заправили бак, она выросла до 3,199 доллара США, вы можете рассчитать процент изменения, подставив эти значения в формулу.< /p>

Давайте рассмотрим пример

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

Вот наш образец данных, содержащий три столбца: "Исходная цена", "Новая цена" и "Процент изменения". Мы отформатировали первые два столбца как суммы в долларах.

Начните с выбора первой ячейки в столбце "Процент изменения".

Введите следующую формулу и нажмите Enter:

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

В главном меню перейдите в меню «Числа». Мы будем использовать две кнопки: одну для форматирования значения ячейки в процентах, а другую для уменьшения количества знаков после запятой, чтобы в ячейке отображались только десятые доли. Сначала нажмите кнопку «%». Затем нажмите кнопку «.00->.0». Вы можете использовать любую из кнопок в правой части меню, чтобы увеличить или уменьшить отображаемую точность значения.

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

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

Выделите все ячейки столбца «Процент изменения» и нажмите Ctrl+D. Ярлык Ctrl+D заполняет данные вниз или вправо по всем выделенным ячейкам.

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

Как рассчитать абсолютную разницу между двумя значениями/временами в Excel?

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

Рассчитать абсолютную разницу с помощью Kutools for Excel

Расчет абсолютной разницы с помощью формулы

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

=ЕСЛИ(B2>=A2,B2-A2,A2-B2)

Примечание: если вы хотите рассчитать абсолютную разницу между временем, вы можете применить только =ABS(A2-B2), а затем отформатировать значения как время. Смотрите скриншот:

Вычислить абсолютные различия с помощью Kutools for Excel

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

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

<р>1. Выберите ячейку, в которую вы хотите поместить различия, введите =F2-G2 и перетащите маркер заполнения вниз, чтобы заполнить эту формулу ячейками.

<р>2. Нажмите Kutools > Содержание > Изменить знак значений, в диалоговом окне «Изменить знак значений» установите флажок «Изменить все отрицательные значения на положительные». Смотрите скриншот:

<р>3. Нажмите «ОК» или «Применить», и появится диалоговое окно, чтобы напомнить вам некоторую информацию, нажмите «Да», чтобы продолжить. Смотрите скриншот:

Теперь все отрицательные разницы преобразованы в абсолютные значения.

Хотите узнать, как найти разницу между двумя числами (положительными и отрицательными) в Excel? Или как рассчитать процентную разницу для отрицательных чисел в Excel с помощью формулы?

Вы находитесь в правильном месте.

Определение и номенклатура вычитания

В операции вычитания участвуют следующие участники:

Minuend: количество или число, из которого следует вычесть другое. В приведенном выше примере 9 — это уменьшаемое число.
Знак минус (-). Затем мы используем знак минус (-), чтобы найти разницу между двумя числами.
Вычитаемое: вычитаемое — это количество или число, которое нужно вычесть из уменьшаемого.
Знак равенства (=). Затем ставится знак равенства (=).
Разница. Разница является результатом операции вычитания.

Загрузить файл Excel

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

Как найти разницу между двумя числами в Excel с помощью формулы

В MS Excel вы не найдете функции SUBTRACT для выполнения операции вычитания. Вместо этого мы используем обычный символ минуса (-) для выполнения вычитаний.

1) Использование чисел непосредственно в формуле

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

Итак, введите знак равенства (=), чтобы начать формулу Excel > Введите уменьшаемое значение > Введите знак минус (-) > Поместите вычитаемое значение > Нажмите Enter

Пример: =50-5

Примечание. Если вычитаемое значение отрицательное, используйте круглые скобки, чтобы поместить число в формулу вычитания следующим образом: =-91-(-23)

Почему этот метод не рекомендуется?

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

2) Использование ссылок на ячейки вместо чисел в формуле

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

Итак, мы будем использовать этот метод, и именно поэтому Excel так полезен.

В ячейку C2 введите следующую формулу: =A2-B2

Нажмите Enter, и вот что мы получили.

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

3) Вычислить абсолютную разницу между двумя числами в Excel (используя функцию ABS())

Чтобы получить абсолютную разницу между двумя числами в Excel, мы будем использовать функцию Excel ABS().

Функция ABS() возвращает абсолютное значение числа, число без знака.

Синтаксис функции ABS: ABS(число)

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

Разница в процентах между двумя числами в Excel

Процентная разница и процентная ошибка на самом деле являются одним и тем же. В разделе «Разница в процентах» вы сравните два экспериментальных значения.

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

Здесь E₁ = первое экспериментальное значение, а E₂ = второе экспериментальное значение

Примечание. Процентное изменение ((Новое значение/Старое значение) -1) и Процентная разница — это два разных явления в физике.

В Excel мы можем легко рассчитать процентную разницу:

Мы использовали эту формулу в ячейке C2: =2*ABS(A2-B2)/(A2+B2)

Как работает эта формула?

Разница между числами A2 и B2 (A2-B2) может быть отрицательной. Итак, мы использовали функцию ABS() (ABS(A2-B2)), чтобы сделать число абсолютным.
Затем мы умножили абсолютное значение на 2, а затем разделили значение на (A2+B2)

Расчет процентного изменения отрицательных чисел в Excel

Теоретически и даже практически невозможно найти процентное изменение для отрицательных чисел. Если это невозможно теоретически, как мы можем вычислить их в Excel?

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

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

1) Рассчитайте % изменения отрицательных чисел, сделав знаменатель абсолютным

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

Мы знаем, % изменения = ((Новое значение – Старое значение)/ Старое значение) x 100%

Если мы применим эту формулу для расчета процентного изменения двух приведенных выше значений (-400 и 200), то получим следующий результат:

Это совершенно неправильный ответ.

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

На следующем изображении я показал метод знаменателя ABS.

Эти результаты также вводят в заблуждение, поскольку вы видите, что значения от -100 до 200 показывают наибольшее изменение в %, тогда как значения от -500 до 200 показывают наименьшее изменение в %.

Взгляните на следующее изображение:

Эти результаты также вводят в заблуждение.

Видите ли, хотя в компании E произошло самое резкое снижение дохода (с 600 до -100), изменение в процентах показывает, что оно снизилось меньше всего (-117%).

2) Сдвиньте числа, чтобы они стали положительными

Это сложный метод, но результат неприемлем.

Скажем, у нас есть два значения:

Мы сдвинем эти два числа, используя их абсолютный плюс, а затем умножив его на 2: (|-50|+ |20|)*2 = 140

значение shifted_old = -50 + 140 = 90

shifted_new значение = 20 + 140 = 160

Теперь рассчитаем их % изменения: ((160-90)/90)*100% = 77,78%

Давайте посмотрим, дает ли этот метод удовлетворительные результаты:

Просто взгляните на последние две строки: % изменения от 1 до 100 и от 1 до 1000 почти одинаковы.

Но они должны быть: ((100-1)/1)*100% = 99% и ((1000-1)/1)*100% = 999%

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

Итак, каким будет решение?

Решение. Показывать N/A (или что-то еще), когда появляется % изменения отрицательных чисел

Мы будем использовать эту формулу:

ЕСЛИ(МИН(старое_значение, новое_значение) Похожие чтения

Заключение

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

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

Разница в днях

В этом примере дата начала указана в ячейке D9, а дата окончания — в ячейке E9. Формула находится в F9. «d» возвращает количество полных дней между двумя датами.

Разница в неделях

В этом примере дата начала указана в ячейке D13, а дата окончания — в ячейке E13. «d» возвращает количество дней. Но обратите внимание на /7 в конце. Это делит количество дней на 7, так как в неделе 7 дней. Обратите внимание, что этот результат также необходимо отформатировать как число. Нажмите CTRL + 1. Затем нажмите Число > Десятичные разряды: 2.

Разница в месяцах

В этом примере дата начала указана в ячейке D5, а дата окончания — в ячейке E5. В формуле «m» возвращает количество полных месяцев между двумя днями.

Разница в годах

В этом примере дата начала находится в ячейке D2, а дата окончания — в ячейке E2. «y» возвращает количество полных лет между двумя днями.

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

Вы также можете рассчитать возраст или стаж работы. Результатом может быть что-то вроде «2 года, 4 месяца, 5 дней».

1. Используйте DATEDIF, чтобы найти общее количество лет.

В этом примере дата начала указана в ячейке D17, а дата окончания — в ячейке E17. В формуле «y» возвращает количество полных лет между двумя днями.

2. Используйте DATEDIF снова с «ym», чтобы найти месяцы.

В другой ячейке используйте формулу DATEDIF с параметром "ym". «Гм» возвращает количество оставшихся месяцев после последнего полного года.

3. Используйте другую формулу, чтобы найти дни.

Теперь нам нужно найти количество оставшихся дней. Мы сделаем это, написав другую формулу, показанную выше. Эта формула вычитает первый день последнего месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: Сначала функция ДАТА создает дату 01.05.2016. Он создается с использованием года в ячейке E17 и месяца в ячейке E17. Тогда 1 представляет первый день этого месяца. Результат функции ДАТА — 01.05.2016.Затем мы вычитаем это из исходной даты окончания в ячейке E17, то есть 06.05.2016. 06.05.2016 минус 01.05.2016 равно 5 дням.

Предупреждение. Мы не рекомендуем использовать аргумент DATEDIF "md", так как это может привести к неточным результатам.

4. Необязательно: объедините три формулы в одну.

Вы можете поместить все три вычисления в одну ячейку, как в этом примере. Используйте амперсанд, кавычки и текст. Это более длинная формула для ввода, но, по крайней мере, все в одном. Совет: нажмите клавиши ALT+ВВОД, чтобы вставить в формулу разрывы строк. Это облегчает чтение. Кроме того, нажмите CTRL+SHIFT+U, если вы не видите всю формулу.

Скачать наши примеры

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

Другие расчеты даты и времени

Как вы видели выше, функция РАЗНДАТ вычисляет разницу между датой начала и датой окончания. Однако вместо того, чтобы вводить конкретные даты, вы также можете использовать функцию СЕГОДНЯ() внутри формулы. Когда вы используете функцию СЕГОДНЯ(), Excel использует в качестве даты текущую дату вашего компьютера. Имейте в виду, что это изменится, когда файл снова откроется в будущем.

Обратите внимание, что на момент написания этой статьи это было 6 октября 2016 года.

Используйте функцию ЧИСТРАБДНИ.МЕЖД, если вы хотите рассчитать количество рабочих дней между двумя датами. Вы также можете исключить выходные и праздничные дни.

Прежде чем начать: решите, хотите ли вы исключить праздничные дни. Если вы это сделаете, введите список дат праздников в отдельной области или листе. Поместите каждую дату праздника в отдельную ячейку. Затем выберите эти ячейки, выберите «Формулы» > «Определить имя». Назовите диапазон MyHolidays и нажмите OK. Затем создайте формулу, выполнив указанные ниже действия.

1. Введите дату начала и дату окончания.

В этом примере дата начала указана в ячейке D53, а дата окончания — в ячейке E53.

2. В другой ячейке введите следующую формулу:

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

Примечание. В Excel 2007 нет функции ЧИСТРАБДНИ.МЕЖД. Однако у него есть ЧИСТРАБДНИ. Приведенный выше пример в Excel 2007 выглядел бы так: =ЧИСТРАБДНИ(D53,E53). Вы не указываете 1, потому что ЧИСТРАБДНИ предполагают, что выходные приходятся на субботу и воскресенье.

3. При необходимости измените 1.

Если суббота и воскресенье не являются вашими выходными днями, измените 1 на другое число из списка IntelliSense. Например, число 2 делает воскресенье и понедельник выходными днями.

Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ Excel 2007 всегда предполагает, что выходные приходятся на субботу и воскресенье.

4. Введите название диапазона праздников.

Если вы создали название диапазона праздников в разделе "Перед началом работы" выше, введите его в конце, как показано ниже. Если у вас нет праздников, вы можете не указывать запятую и MyHolidays. Если вы используете Excel 2007, приведенный выше пример будет таким: =ЧИСТРАБДНИ(D53,E53,MyHolidays).

Совет. Если вы не хотите ссылаться на название диапазона праздников, вы также можете вместо этого ввести диапазон, например D35:E:39. Или вы можете ввести каждый праздник внутри формулы. Например, если ваши праздники приходятся на 1 и 2 января 2016 года, введите их так: =NETWORKDAYS.INTL(D53,E53,1,). В Excel 2007 это будет выглядеть так: =ЧИСТРАБДНИ(D53,E53,)

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

1. Введите время начала и время окончания.

В этом примере время начала указано в ячейке D80, а время окончания — в ячейке E80. Обязательно введите часы, минуты и с пробелом перед AM или PM.

2. Установите формат ч:мм AM/PM.

Выберите обе даты и нажмите CTRL + 1 (или + 1 на Mac). Обязательно выберите «Пользовательский» > «ч:мм AM/PM», если он еще не установлен.

3. Вычтите два раза.

В другой ячейке вычтите ячейку времени начала из ячейки времени окончания.

4. Установите формат ч:мм.

Нажмите CTRL + 1 (или + 1 на Mac). Выберите «Пользовательский» > «ч:мм», чтобы результат не включал AM и PM.

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

1. Введите две полные даты и время.

В одной ячейке введите полную дату/время начала. А в другой ячейке введите полную дату/время окончания. В каждой ячейке должны быть указаны месяц, день, год, час, минута, и пробел перед AM или PM.

2. Установите формат 14.03.12 13:30.

Выделите обе ячейки и нажмите CTRL + 1 (или + 1 на Mac). Затем выберите Дата > 14.03.12 13:30. Это не дата, которую вы установите, это просто пример того, как будет выглядеть формат. Обратите внимание, что в версиях, предшествующих Excel 2016, этот формат может иметь другую примерную дату, например 14 марта 2001 г., 13:30.

3. Вычтите два.

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

4. Установите формат [ч]:мм.

Нажмите CTRL + 1 (или + 1 на Mac). Выберите Пользовательский. В поле Тип введите [ч]:мм.

Читайте также: