В этом уроке Вы познакомитесь с различными полезными формулами сложения и вычитания дат в Excel. Например, Вы узнаете, как вычесть из одной даты другую, как прибавить к дате несколько дней, месяцев или лет, и т.д.
Если Вы уже проходили уроки по работе с датами в Excel (наши или любые другие уроки), то должны знать формулы для вычисления единиц времени, таких как дни, недели, месяцы, года.
Анализируя даты в каких-либо данных, часто нужно выполнять над этими датами арифметические действия. Эта статья объяснит некоторые формулы сложения и вычитания дат, которые могут оказаться для Вас полезными.
Предположим, что у Вас в ячейках A2 и B2 содержатся даты, и нужно вычесть одну дату из другой, чтобы узнать, сколько между ними дней. Как часто бывает в Excel, этот результат можно получить несколькими способами.
Думаю, Вы знаете, что Excel хранит даты в виде целых чисел, начиная с 1, что соответствует 1 января 1900 г. Поэтому Вы можете просто арифметически вычесть одно число из другого:
Если предыдущая формула кажется Вам слишком простой, тот же результат можно получить более изощрённым способом с помощью функции РАЗНДАТ (DATEDIF).
РАЗНДАТ(A2;B2;"d")
=DATEDIF(A2,B2,"d")
Следующий рисунок показывает, что обе формулы возвращают одинаковый результат, за исключением ряда 4, где функция РАЗНДАТ (DATEDIF) возвращает ошибку #ЧИСЛО! (#NUM!). Посмотрим, почему это происходит.
Когда Вы вычитаете более позднюю дату (6 мая 2015) из более ранней (1 мая 2015), операция вычитания возвращает отрицательное число. Однако синтаксис функции РАЗНДАТ (DATEDIF) не позволяет, чтобы начальная дата была больше конечной даты и, естественно, возвращает ошибку.
Чтобы вычесть определенную дату из текущей даты, Вы можете использовать любую из ранее описанных формул. Просто вместо сегодняшней даты используйте функцию СЕГОДНЯ (TODAY):
СЕГОДНЯ()-A2
=TODAY()-A2
РАЗНДАТ(A2;СЕГОДНЯ();"d")
=DATEDIF(A2,TODAY(),"d")
Как и в предыдущем примере, формулы отлично работают, когда текущая дата больше, чем вычитаемая. В противном случае функция РАЗНДАТ (DATEDIF) возвращает ошибку.
Если Вы предпочитаете вводить даты непосредственно в формулу, указывайте их с помощью функции ДАТА (DATE), а затем вычитайте одну дату из другой.
Функция ДАТА имеет следующий синтаксис: ДАТА(год ; месяц ; день ) .
Например, следующая формула вычитает 15 мая 2015 года из 20 мая 2015 года и возвращает разность – 5 дней.
ДАТА(2015;5;20)-ДАТА(2015;5;15)
=DATE(2015,5,20)-DATE(2015,5,15)
Если нужно подсчитать количество месяцев или лет между двумя датами , тогда функция РАЗНДАТ (DATEDIF) – единственное возможное решение. В продолжении статьи Вы найдёте несколько примеров формул, подробно раскрывающих эту функцию.
Теперь, когда Вы знаете, как вычитать одну дату из другой, посмотрим, как можно прибавить или вычесть из даты определённое количество дней, месяцев или лет. Для этого существует несколько функций Excel. Что именно выбрать зависит от того, какие единицы времени требуется прибавить или вычесть.
Если у Вас есть дата в ячейке или список дат в столбце, Вы можете прибавить к ним (или отнять) определённое количество дней, используя соответствующую арифметическую операцию.
Общая формула добавления определённого количества дней к дате выглядит так:
= Дата + N дней
Дату можно задать несколькими способами:
ДАТА(2015;5;6)+10
=DATE(2015,5,6)+10
СЕГОДНЯ()+10
=TODAY()+10
Следующий рисунок показывает действие этих формул. На момент написания текста текущей датой было 6 мая 2015 г.
Примечание: Результатом выполнения этих формул является целое число, представляющее дату. Чтобы показать его как дату, необходимо выбрать ячейку (или ячейки) и нажать Ctrl+1 . Откроется диалоговое окно Формат ячеек (Format Cells). На вкладке Число (Number) в списке числовых форматов выберите Дата (Date) и затем укажите нужный вам формат. Более подробное описание Вы найдёте в статье .
Чтобы вычесть определённое количество дней из даты, Вам снова нужно использовать обычную арифметическую операцию. Единственное отличие от предыдущего примера – минус вместо плюса
= Дата - N дней
Вот несколько примеров формул:
A2-10
=ДАТА(2015;5;6)-10
=СЕГОДНЯ()-10
Когда требуется прибавить (вычесть) несколько недель к определенной дате, Вы можете воспользоваться теми же формулами, что и раньше. Просто нужно умножить количество недель на 7:
A2 + N недель * 7
Например, чтобы прибавить 3 недели к дате в ячейке А2 , используйте следующую формулу:
А2 - N недель * 7
Чтобы вычесть 2 недели из сегодняшней даты, используйте эту формулу:
СЕГОДНЯ()-2*7
=TODAY()-2*7
Чтобы прибавить (или отнять) определённое количество месяцев к дате, нужно использовать функцию ДАТА (DATE) или ДАТАМЕС (EDATE), как показано ниже.
Если список дат находится, например, в столбце A , укажите количество месяцев, которые Вы хотите добавить (положительное число) или отнять (отрицательное число) в какой-то ячейке, скажем, в C2 .
Введите в ячейку B2 указанную ниже формулу, щёлкните по выделенному углу ячейки и протащите его мышкой вниз по столбцу B до последней заполненной ячейки в столбце A . Формула из ячейки B2 будет скопирована во все ячейки столбца B .
ДАТА(ГОД(A2);МЕСЯЦ(A2)+$C$2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)+$C$2,DAY(A2))
Посмотрим, что делает эта формула. Логика формулы ясна и очевидна. Функция ДАТА(год ; месяц ; день ) получает следующие аргументы:
Всё просто! Если Вы введёте в C2 отрицательное число, формула вычтет месяцы, а не прибавит.
Естественно, ничто не мешает Вам ввести минус прямо в формуле, чтобы вычесть месяцы:
ДАТА(ГОД(A2);МЕСЯЦ(A2)-$C$2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)-$C$2,DAY(A2))
И, конечно, вы можете указать число прибавляемых или вычитаемых месяцев непосредственно в формуле без ссылки на ячейку. Готовые формулы будут выглядеть примерно так:
ДАТА(ГОД(A2);МЕСЯЦ(A2)+2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)+2,DAY(A2))
ДАТА(ГОД(A2);МЕСЯЦ(A2)-2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)-2,DAY(A2))
В Excel предусмотрена специальная функция, которая возвращает дату, отстоящую от заданной на определенное количество месяцев назад или вперёд, – это функция ДАТАМЕС (EDATE). Она доступна в последних версиях Excel 2007, 2010, 2013 и новой Excel 2016.
При использовании ДАТАМЕС (EDATE) Вы указываете два следующих аргумента:
Эти формулы дадут тот же самый результат, что и формулы с функцией ДАТА (DATE) в предыдущем примере:
При использовании функции ДАТАМЕС (EDATE) начальную дату и количество месяцев можно указывать непосредственно в формуле. Даты можно задавать с помощью функции ДАТА (DATE) или как результат выполнения других формул. Например:
ДАТАМЕС(ДАТА(2015;5;7);10)
=EDATE(DATE(2015,5,7),10)
ДАТАМЕС(СЕГОДНЯ();-10)
=EDATE(TODAY(),-10)
Примечание: Функция ДАТАМЕС (EDATE) возвращает просто целое число. Чтобы представить его как дату, необходимо применить к ячейке формат даты. Как это сделать, указано в статье Как изменить формат даты в Excel .
Добавление лет к датам в Excel осуществляется так же, как добавление месяцев. Вам необходимо снова использовать функцию ДАТА (DATE), но на этот раз нужно указать количество лет, которые Вы хотите добавить:
ДАТА(ГОД(дата
) + N лет
; МЕСЯЦ(дата
); ДЕНЬ(дата
))
= DATE(YEAR(дата
) + N лет
, MONTH(дата
), DAY(дата
))
На листе Excel, формулы могут выглядеть следующим образом:
ДАТА(ГОД(A2)+5;МЕСЯЦ(A2);ДЕНЬ(A2))
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))
ДАТА(ГОД(A2)-5;МЕСЯЦ(A2);ДЕНЬ(A2))
=DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))
Чтобы получить универсальную формулу, Вы можете ввести количество лет в ячейку, а затем в формуле обратиться к этой ячейке. Положительное число позволит прибавить годы к дате, а отрицательное – вычесть.
Если Вы внимательно читали два предыдущих примера, то, думаю, догадались, как добавить (или вычесть) сразу годы, месяцы и дни к дате в одной формуле. Да, с помощью старой доброй функции ДАТА (DATA)!
ДАТА(ГОД(дата
) + X лет
; МЕСЯЦ(дата
) + Y месяцев
; ДЕНЬ(дата
) + Z дней
)
= DATE(YEAR(дата
) + X лет
, MONTH(дата
) + Y месяцев
, DAY(дата
) + Z дней
)
ДАТА(ГОД(дата
) - X лет
; МЕСЯЦ(дата
) - Y месяцев
; ДЕНЬ(дата
) - Z дней
)
= DATE(YEAR(дата
) - X лет
, MONTH(дата
) - Y месяцев
, DAY(дата
) - Z дней
)
Например, следующая формула прибавляет 2 года и 3 месяца, и вычитает 15 дней из даты в ячейке A2 :
ДАТА(ГОД(A2)+2;МЕСЯЦ(A2)+3;ДЕНЬ(A2)-15)
=DATE(YEAR(A2)+2,MONTH(A2)+3,DAY(A2)-15)
Применительно к нашему столбцу с датами, формула принимает следующий вид:
ДАТА(ГОД(A2)+$C$2;МЕСЯЦ(A2)+$D$2;ДЕНЬ(A2)+$E$2)
=DATE(YEAR(A2)+$C$2,MONTH(A2)+$D$2,DAY(A2)+$E$2)
В Microsoft Excel складывать и вычитать время можно с помощью функции ВРЕМЯ (TIME). Она позволяет Вам обращаться с единицами времени (часами, минутами и секундами) так же, как и с годами, месяцами и днями в функции ДАТА (DATE).
A2 + ВРЕМЯ(часы
; минуты
; секунды
)
= A2 + TIME(часы
, минуты
, секунды
)
A2 - ВРЕМЯ(часы
; минуты
; секунды
)
= A2 - TIME(часы
, минуты
, секунды
)
где A2 – это ячейка с временем, которое нужно изменить.
Например, чтобы добавить 2 часа 30 минут и 15 секунд к времени в ячейке A2 надо использовать следующую формулу:
A2+ВРЕМЯ(2;30;15)
=A2+TIME(2,30,15)
A2+ВРЕМЯ(2;30;-15)
=A2+TIME(2,30,-15)
Также Вы можете ввести нужные значения в ячейки листа и ссылаться на них в формуле:
A2+ВРЕМЯ($C$2;$D$2;$E$2)
=A2+TIME($C$2,$D$2,$E$2)
Работая в программе Excel часто возникает необходимость прибавлять или отнимать от числа какие-то проценты. Это может быть связано с необходимостью прибавить процент НДС или рассчитать прибыль. Какой бы не была конкретная задача, ее можно решить в Excel.
Сейчас мы расскажем о том, как прибавить процент к числу в Excel. Материал будет полезен для пользователей всех версий Excel, включая Excel 2003, 2007, 2010, 2013 и 2016.
Для того чтобы объяснить, как прибавить процент к числу рассмотрим простой пример. Допустим у вас есть число, к которому вам нужно прибавить определенный процент (например, нужно прибавить 18% НДС). А в соседней клетке вы хотите получить значение с уже прибавленным процентом.
Для этого вам нужно выделить ячейку, которой должен находится результат, и вписать в нее формулу. В качестве формулы можно использовать вот такую не сложную конструкцию: =A2+A2*18%. Где A2 – это ячейка содержащая исходное число, а 18 – это процент, который вы хотите прибавить к этому исходному числу.
После того как вы ввели формулу, нужно просто нажать на клавишу Enter на клавиатуре, и вы получите результат. В нашем случае, мы прибавляли 18 процентов к числу 100 и получили 118.
Если хотите не прибавлять процент, а отнимать, то это делается аналогичным способом. Только в формуле используется не плюс, а минус.
При необходимости, процент, который вы будете прибавлять или отнимать, можно не указывать прямо в формуле, а брать из таблицы. Для этого случая формулу нужно немного изменить: =A2+A2*B2%. Как видите, в формуле вместо конкретного значения процентов используется адрес ячейки, а после него процент.
После использования такой формулы вы получите число с прибавленным к нему процентом, который был указан в таблице.
Нужно отметить, что при работе с процентами вы можете смокнуться с тем, что в ваших ячейках начнут отобраться какие-то слишком большие цифры, а также знак процента.
Это происходит в тех случаях, когда пользователь сначала вводит неправильно формулу, а потом исправляется. Например, в случае прибавления 18 процентов можно ошибиться и ввести: =A2+18%.
Если после этого исправиться и ввести правильную формулу =A2+A2*18%, то вы получите какое-то неправдоподобно большое число.
Проблема в том, что в результате введения первой формулы формат ячейки поменялся с числового на процентный. Для того чтобы это исправить кликните по ячейке правой кнопкой мышки и перейдите в «Формат ячеек».
В открывшемся окне выберите формат ячейки, который будет ей подходить.
Чаще всего, это общий или числовой. После выбора нужно формата сохраните настройки с помощью кнопки «Ок».
В этой статье я вам расскажу об очень простой и часто встречающейся задаче в excel — как прибавить проценты в excel с помощью формулы .
Хотя все компьютерные программы разрабатываются для людей с целью облегчить работу, это не означает,что математику знать не нужно. Поэтому давайте вспомним немного математику, то как это нужно делать на листике с бумагой. Например, нам нужно прибавить 5%. Для этого мы должны сначала найти 5% от числа, а потому полученные 5% прибавить к известному числу. Простейший способ найти 5% это решить пропорцию:
Теория — основная пропорция для нахождения нужного процента
После нахождения Х выполняется сложения числа с полученным значением. Хочется сразу отметить, что для многих подобный метод окажется непреодолимой ямой. В конце все выглядит так:
Теория — результат нахождения нужного процента
Освоив рутинную теорию математики можно перейти к решению задачи в excel.
Предположим нам нужно к числу 37 прибавить 5%. Для этого мы в ячейку В2 запишем число 37, а в ячейку С2 запишем известную нам формулу:
После того как мы нажмем «ввод» мы можем получить «неправильный» результат.
Получения такого результата связанно с тем, что компьютер неправильно определил формат ячейки. Поэтому для получения достоверных данных нужно Указать формат ячейки с формулой «числовой».
Описанная задача встречается очень часто. Например в экономике, нужно прибавить 3% к заработной плате работникам бригады.
Как видно из рисунка в ячейке D9 записана известная нам формула. Нажмем «ввод», исправим формат ячейки на «Числовой»(если нужно). После этого растянем формулу на остальные ячейки в столбике. В результате мы получим следующий результат.
Предыдущие способы прибавления процентов в Excel можно назвать «топорноми» и используются чаще теми, кто имеет маленькую практику работы с приложением. Это связано с тем, что есть рекомендованный способ прибавления процентов – использование символа процентов(%) .
Допустим, у нас есть маленькая табличка, в первом столбце у нас записано число, а во втором то, сколько процентов нужно прибавить. Результат будем писать в третий столбец.
В третьем столбце нам нужно записать формулу H3+H3*I3%, обращаю ваше внимание на то, что мы ячейку H3 умножаем на ячейку I3%. Надеюсь все увидели символ проценты.
По сути, данный способ ничем не отличается от того, что было описано ранее. Просто разработчики посчитали, что он будет интуитивно понятен.
Инструкция
Допустим, к исходному числу, помещенному в ячейку A1, нужно прибавить фиксированный процент и отобразить результат в ячейке A2. Тогда в A2 следует поместить формулу, которая значение из A1 увеличивает на некоторый множитель. Начните с определения величины множителя - к единице прибавьте одну сотую часть фиксированного процента. Например, если к числу из ячейки A1 надо прибавить 25%, то множитель будет равен 1 + (25/100) = 1,25. Кликните A2 и наберите нужную формулу: введите знак равенства, кликните ячейку A1, нажмите (знак операции умножения) и напечатайте множитель. Выглядеть вся запись для приведенного выше примера должна так: =A1*1,25. Нажмите Enter и Excel посчитает и отобразит результат.
Если нужно рассчитать одинаковый процент от значений в каждой ячейке определенной колонки и добавить полученную величину в эти же ячейки, то поместите множитель в отдельную ячейку. Допустим, прибавлять требуется 15%, а исходные значения занимают с первой по двадцатую строки в колонке A. Тогда в свободную ячейку поместите значение 1,15 (1 + 15/100) и скопируйте ее (Ctrl + C). Затем выделите диапазон с A1 по A20 и нажмите сочетание клавиш Ctrl + Alt + V. На экране появится диалог «Специальная вставка». В секции «Операция» поставьте отметку напротив надписи «умножить» и кликните кнопку OK. После этого все выделенные ячейки изменятся на заданную в процентах величину, а вспомогательную ячейку, содержащую множитель, можно будет удалить.
При необходимости иметь возможность изменять прибавляемый процент его лучше поместить в отдельную ячейку, а не исправлять каждый раз в . Например, исходную величину поместите в первую строку первой колонки (A1), а процент, который нужно прибавить, во вторую колонку той же строки (B1). Чтобы отобразить результат в ячейке C1, введите в нее такую формулу: =A1*(1+B1/100). После нажатия клавиши Enter искомое значение появится в колонке, если ячейки A1 и B1 уже содержат нужные значения.
Источники:
В приложении Microsoft Office одно и то же действие можно выполнить несколькими способами. Все зависит только от того, что будет удобнее для пользователя. Чтобы добавит ячейку на лист, вы можете выбрать тот способ, который подойдет именно вам.
Инструкция
Чтобы добавить новую ячейку, установите курсор в ту, выше которой планируете добавить еще одну. Откройте вкладку «Главная» и нажмите на кнопку «Вставить» в разделе «Ячейки ». Если вы выделите несколько ячеек по горизонтали и нажмете ту же кнопку, выше добавится же количество ячеек, что и было выделено. Если выделять их по вертикали, новые ячейки добавятся левее от выбранного диапазона.
Чтобы точнее указать, где должна быть расположена дополнительная ячейка, поставьте курсор в ту, возле которой хотите добавить новую, и кликните в ней правой кнопкой мыши. В контекстном меню выберите из двух команд «Вставить» вторую сверху. Появится новое диалоговое окно. Установите в нем маркер напротив одного из вариантов: «Ячейки , со сдвигом вправо» или «Ячейки , со сдвигом вниз». Нажмите на кнопку ОК. Если вы выделите сразу несколько ячеек, будет добавлено такое же количество новых.
Те же действия можно выполнить с помощью кнопок на панели инструментов. Выделите одну или несколько ячеек и нажмите в разделе «Ячейки » не саму кнопку «Вставить», а кнопку в виде стрелки, расположенную рядом. Откроется контекстное меню, выберите в нем пункт «Вставить ячейки». После этого появится то же окно, о котором шла речь во втором шаге. Отметьте в нем тот вариант добавления ячеек, который вам подходит, и нажмите на кнопку ОК.
Если вы оформляли свою таблицу с помощью инструмента «Таблица», то сможете добавить только новые строки или столбцы. Чтобы иметь возможность вставить пустую ячейку, кликните в таблице правой кнопкой мыши, выберите в контекстном меню пункт «Таблица» и подпункт «Преобразовать в диапазон». Подтвердите свои действия в окне запроса. Вид таблицы изменится, после этого вы сможете вставить ячейку одним из описанных выше способов.
В жизни рано или поздно каждый столкнется с ситуацией, когда необходимо будет работать с процентами. Но, к сожалению, большинство людей не готовы к таким ситуациям. И данное действие вызывает затруднения. В этой статье будет рассказано, как отнять проценты от числа. Более того, разобраны будут различные способы решения задачи: от самого простого (с помощью программ) до одного из сложнейших (с помощью ручки и листка).
Сейчас мы узнаем, как отнять с помощью ручки и листка. Действия, которые будут представлены ниже, изучает абсолютно каждый человек еще в школе. Но по какой-то из причин не каждый запомнил все манипуляции. Итак, что вам будет нужно, мы уже разобрались. Теперь расскажем, что необходимо делать. Чтобы было более понятно, рассматривать будем пример, беря за основу конкретные числа. Допустим, вы хотите отнять 10 процентов от числа 1000. Конечно, вполне возможно эти действия провернуть в уме, так как задача очень проста. Главное, понять саму суть решения.
В первую очередь вам необходимо записать пропорцию. Допустим, у вас есть две колонки с двумя рядами. Запомнить нужно одно: в левый столбец вписываются числа, а в правый - проценты. В левой колонке будет записано два значения - 1000 и X. Икс вписан потому, что именно он символизирует число, которое нужно будет найти. В правой колонке будут вписаны - 100% и 10%.
Теперь получается, что 100% - это число 1000, а 10% - X. Чтобы найти икс, нужно 1000 умножить на 10. Полученное значение делим на 100. Запомните: необходимый процент нужно всегда умножать на взятое число, после чего произведение следует поделить на 100%. Формула выглядит так: (1000*10)/100. На картинке будут наглядно изображены все формулы по работе с процентами.
У нас получилось число 100. Именно оно и кроется под тем самым иксом. Теперь все, что остается сделать, это от 1000 отнять 100. Получается 900. Вот и все. Теперь вы знаете, как отнять проценты от числа с помощью ручки и тетради. Потренируйтесь самостоятельно. И со временем данные действия вы сможете совершать в уме. Ну а мы двигаемся дальше, рассказывая о других способах.
Ясное дело: если под рукою есть компьютер, то мало кто захочет применить для подсчетов ручку и тетрадь. Проще воспользоваться техникой. Именно поэтому сейчас рассмотрим, как отнять проценты от числа с помощью калькулятора Windows. Однако стоит сделать небольшую ремарку: многие калькуляторы способны совершать эти действия. Но пример будет показан именно с использованием калькулятора Windows для большего понимания.
Здесь все просто. И очень странно, что мало кто знает, как отнять проценты от числа в калькуляторе. Изначально откройте саму программу. Для этого войдите в меню "Пуск". Далее выберите "Все программы", после чего перейдите в папку "Стандартные" и выберите "Калькулятор".
Теперь все готово для того, чтобы приступить к решению. Оперировать будем теми же числами. У нас есть 1000. И от нее нужно отнять 10%. Все что нужно - ввести в калькулятор первое число (1000), далее нажать минус (-), после чего кликнуть на процент (%). Как только вы это сделали, вам сразу покажется выражение 1000-100. То есть калькулятор автоматически посчитал, сколько это 10% от 1000.
Теперь нажмите Enter или же равно (=). Ответ: 900. Как можно заметить, и первый, и второй способ привел к одному и тому же итогу. Поэтому решать только вам, каким способом пользоваться. Ну, а мы тем временем переходим к третьему, последнему варианту.
Многие люди пользуются программой Excel. И бывают такие ситуации, когда жизненно необходимо быстро произвести расчет в этой программе. Именно поэтому сейчас разберемся, как отнять процент от числа в Excel. В программе это сделать очень просто, используя формулы. К примеру, у вас есть колонка со значениями. И вам нужно от них отнять 25%. Для этого выделите колонку рядом и в поле для формул впишите равно (=). После этого нажмите ЛКМ по ячейке с числом, далее ставим "-" (и опять кликаем на ячейку с числом, после этого вписываем - "*25%). У вас должно получиться, как на картинке.
Как можно заметить, эта все та же формула, что и приводилась в первый раз. После нажатия Enter вы получите ответ. Чтобы быстро отнять 25% ото всех чисел в колонке, достаточно лишь навести курсор на ответ, разместив его в нижнем правом углу, и протянуть вниз на нужное количество ячеек. Теперь вы знаете, как в "Эксель" отнять процент от числа.
Напоследок хочется сказать лишь одно: как можно видеть из всего вышесказанного, во всех случаях используется лишь одна формула - (x*y)/100. И именно с ее помощью у нас и получилось решить задачу всеми тремя способами.
