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

Обновлено: 04.07.2024

Среди всех арифметических действий можно выделить четыре основных: сложение, умножение, деление и вычитание. О последнем и пойдет в речь в данной статье. Давайте рассмотрим, какими методами можно выполнять данное действие в Excel.

Процедура вычитания

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

Само действие можно выполнить с помощью формулы, которая начинается со знака “равно” (“=”). Затем, согласно законам арифметики, пишем уменьшаемое, после него ставим знак “минус” (“-“) и в конце указываем вычитаемое. В сложных формулах вычитаемых можно быть несколько, и в этом случае, они идут следом, а между ними ставится ставится “-“. Таким образом, мы получаем результат в виде разности чисел.

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

Пример 1: разность конкретных чисел

Допустим, нам нужно найти разность между конкретными числами: 396 и 264. Выполнить вычитание можно с помощью простой формулы:

Примечание: безусловно, программа Эксель умеет работать и с отрицательными числами, поэтому, вычитание можно выполнить в обратном порядке. В этом случае формула выглядит так: =264-365 .

Пример 2: вычитание числа из ячейки

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

Как и в первом методе, сначала выбираем свободную ячейку, куда хотим вывести результат вычисления. В ней:
- пишем знак “=”.
- указываем адрес ячейки, в которой находится уменьшаемое. Сделать это можно вручную, прописав координаты с помощью клавиш на клавиатуре. Либо можно выбрать нужную ячейку, кликну по ней левой кнопкой мыши.
- добавляем в формулу знак вычитания (“-“).
- пишем вычитаемое (если вычитаемых несколько, добавляем их через символ “-“).
После нажатия клавиши Enter, получаем результат в выбранной ячейке.

Примечание: данный пример работает и обратном порядке, т.е. когда уменьшаемое – это конкретное число, а вычитаемое – это числовое значение в ячейке.

Пример 3: разность между числами в ячейках

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

Пример 4: Вычитание одного столбца из другого

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

Программа предоставляет пользователю такую возможность, и вот как ее можно реализовать:

Пример 5: Вычитание конкретного числа из столбца

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

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

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

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

Алгоритм действий в данном случае следующий:

Переходим в самую верхнюю ячейку столбца для вычислений. Пишем в нем привычную формулу вычитания между двумя ячейками.
Когда формула готова, не спешим нажимать клавишу Enter. Чтобы при растягивании формулы зафиксировать адрес ячейки с вычитаемым, необходимо напротив ее координат вставить символы “$” (другими словами, сделать адрес ячейки абсолютными, так как по умолчанию ссылки в программе относительные). Сделать это можно вручную, прописав в формуле нужные символы, или же, при ее редактировании переместить курсор на адрес ячейки с вычитаемым и один раз нажать клавишу F4. В итоге формула (в нашем случае) должна выглядеть так:
После того, как формула полностью готова, жмем Enter для получения результата.
С помощью маркера заполнения производим аналогичные вычисления в остальных ячейках столбца.

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

Заключение

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

Расчет количества дней

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

«Формат ячейки…»

Ctrl+1

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

Способ 1: простое вычисление

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

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

Способ 2: функция РАЗНДАТ

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

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

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

Способ 3: вычисление количеств рабочих дней

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

В этой функции основные аргументы, такие же, как и у оператора РАЗНДАТ – начальная и конечная дата. Кроме того, имеется необязательный аргумент «Праздники».

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

«Вставить функцию»

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

Как видим, программа Excel предоставляет своим пользователем довольно удобный инструментарий для расчета количества дней между двумя датами. При этом, если нужно рассчитать просто разницу в днях, то более оптимальным вариантом будет применение простой формулы вычитания, а не использование функции РАЗНДАТ. А вот если требуется, например, подсчитать количество рабочих дней, то тут на помощь придет функция ЧИСТРАБДНИ. То есть, как всегда, пользователю следует определиться с инструментом выполнения после того, как он поставил конкретную задачу.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

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

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

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

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

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

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

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

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

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

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

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

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

2. Чтобы найти месяцы, снова используйте DATEDIF с "м".

В другой ячейке используйте формулу РАЗНОМЕР с параметром "м". "м" возвращает количество оставшихся месяцев за последний полный год.

3. Используйте другую формулу для поиска дней.

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

Предупреждение: Не рекомендуется использовать аргумент "мд" РАЗДАТ.

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

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

Скачивание примеров

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

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

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

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

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

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

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

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

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

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

Примечание. Excel 2007 не имеет networkDAYS. Функция INTL. Однако в нем есть NETWORKDAYS. В этом примере в Excel 2007 г. будет выглядеть так: =NETWORKDAYS(D53;E53). Вы не указываете 1, так как в networkDAYS предполагается, что выходные — в субботу и воскресенье.

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

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

Если вы используете Excel 2007, пропустите этот шаг. Excel 2007 г. функция NETWORKDAYS всегда предполагает, что выходные дни — в субботу и воскресенье.

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

Если вы создали имя диапазона праздников в разделе "Начало работы" выше, введите его в конце так: Если у вас нет праздников, оставьте запятую и запятую MyHolidays. Если вы используете Excel 2007, пример будет такой: =ДНЕЙМН(D53;E53;MyHolidays).

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

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

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

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

2. Задать формат ч:мм.AM/PM.

Выберите обе даты и нажмите CTRL+1 (или + 1 на Компьютере Mac). Выберите настраиваемый >ч:мм/PM, если он еще не установлен.

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

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

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

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

Расчет заархив времени между двумя датами и временем

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

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

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

2. Задать формат 14.03.2012, 13:30.

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

3. Вычитать эти два.

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

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

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

Расчет разности дат

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

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

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

по умолчанию мы должны оказаться во вкладке “Число” (если это не так, переключаемся в нее);
в списке “Числовые форматы” находим позицию “Дата” и кликаем на нее;
в поле “Тип” выбираем нужный образец представления данных, в соответствии с которым они будут отображаться на листе;
нажимаем OK.

Метод 1: использование формулы вычитания

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

Вносим две даты, между которыми будем определять разницу в днях, в две разные ячейки отформатированного ранее диапазона.
Теперь выделим ячейку, в которой планируем отобразить результат нашего вычисления.
ВАЖНО: Количество дней обозначается простым целым числом, следовательно, ячейка для его вывода должна иметь отличный от “Дата” формат, а именно – “Общий“. В противном случае полученный результат будет отображаться в формате даты, что не позволит оценить корректность проведенных вычислений. Для определения текущего формата ячейки достаточно взглянуть на соответствующее поле, расположенное в разделе инструментов “Число” на ленте вкладки “Главная“.

Метод 2: функция РАЗНДАТ

Следующий способ – это определение разницы между двумя датами при помощи функции РАЗНДАТ.

Функция имеет следующий синтаксис: =РАЗНДАТ(начальная_дата;конечная_дата;единица).

Если с двумя первыми аргументами все понятно – их суть соответствует названию, то на третьем (“единица“) стоит остановиться подробнее, так как именно он позволяет варьировать результат использования функции, выбирая единицу расчета разницы между датами:

“d” — количество дней между датами;
“m” — количество полных месяцев между датами;
“y” — количество полных лет между датами;
“YM” — разница между датами в месяцах;
“MD” — разница между датами в днях, без учета месяцев и лет;
“YD” — разница между датами в днях, без учета лет.

В нашем случае покажем пример использование аргумента “YD”, так как целью расчета является определение количества дней между датами в пределах года.

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

Метод 3: функция ЧИСТРАБДНИ для определения количества рабочих дней

Третий описываемый метод позволяет вычислить количество дней с дополнительным условием – исключением из него нерабочих (выходных и праздничных) дней.

В данном методе используется функция ЧИСТРАБДНИ со следующим синтаксисом: =ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники]).

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

Приступаем к вычислениям:

Заключение

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

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