Как в excel вычислить формулу: Вычисление вложенной формулы по шагам

Содержание

Как отследить динамику роста в Microsoft Excel – База знаний Timeweb Community

Сравнение показателей в таблице за определенный период – одна из популярных задач, которая стоит перед пользователями Microsoft Excel. Чаще всего это осуществляется путем вывода вспомогательного столбца, где значения указаны в процентах, и сразу становится понятно, насколько сильной была позитивная или негативная динамика.

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

Предварительные действия

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

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

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

Для этого выделите необходимые ячейки и щелкните по любой из них правой кнопкой мыши. Из контекстного меню выберите пункт «Формат ячеек».

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Создание формулы отслеживания динамики роста

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

Выделите ячейку, где должны выводиться проценты и напишите первую часть формулы =(C2 — B2). Это поможет определить разницу между суммами за два разных месяца. Соответственно, если номера ячеек в вашей таблице другие, замените их, отняв сумму второго значения от первого.

После скобок добавьте /B2, чтобы разделить это все на сумму первого значения, то есть на 100%, если говорить о стандартных математических операциях с процентами.

Нажмите Enter и посмотрите на результат. В моем случае сумма за первый месяц была 102 000, за второй — 124 000, а разница – 22 000. Получается, динамика роста является положительной и равняется 21,62%. У вас значение будет другим, в соответствии с суммами в таблице.

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

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

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

Формула отслеживания динамики роста по норме

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

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

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

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

В этом случае формула обретает вид =(C2-$B$9)/$B$9, где $B$9 – номер той самой закрепленной ячейки, а C2 – сумма второго месяца, от которой и отнимается норма для вычисления процентов.

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

Подробнее: Как создать диаграмму темпов роста в Microsoft Excel

Как посчитать проценты в Excel

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

как посчитать проценты в Excel формула;
как посчитать процент от числа в Excel;
как посчитать проценты от суммы в Excel;
посчитать разницу в процентах Excel;

Как посчитать проценты в Excel формула

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

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

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

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

Как посчитать проценты в Excel – Исходные данные для расчета процентов

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

В ячейке D2 ввести формулу =C2/B2 и скопировать ее в остальные ячейки с помощью маркера заполнения.
На вкладке «Главная» в группе «Число» выбрать «Процентный формат» для отображения результатов в формате процентов.

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

Как посчитать проценты в Excel – Процент выполнения плана

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

Как посчитать процент от числа в Excel

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

Рассмотрим пример расчета процента от числа. У нас есть таблица со стоимостью товаров без НДС и ставкой НДС для каждого товара.

Как посчитать проценты в Excel – Исходные данные для расчета процента от числа

Примечание: если вы вручную вводите в ячейке числовое значение и после него ставите знак %, то Excel применяет к данной ячейке процентный формат и воспринимает это число как его сотую часть. Например, если в ячейку ввести 18%, то для расчётов Excel будет использовать значение 0,18.

Пусть нам необходимо рассчитать НДС и стоимость продуктов с налогом на добавленную стоимость.

Для того чтобы посчитать НДС в денежном эквиваленте, т.е. посчитать процент от числа в ячейке D2 вводим формулу =B2*C2 и заполняем остальные ячейки.
В ячейке E2 суммируем ячейки B2 и D2, для того чтобы получить стоимость с НДС.

В результате получаем следующие данные расчета процента от числа:

Как посчитать проценты в Excel – Процент от числа в Excel

Как посчитать проценты от суммы в Excel

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

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

Для этого проделываем следующее:

В ячейке C2 вводим следующую формулу: =B2/$B$9. Для ячейки B9 мы используем абсолютную ссылку (со знаками $), чтобы она была неизменной, а для ячейки B2 – относительную, чтобы она изменялась при копировании формулы в другие ячейки.
Используя маркер заполнения копируем эту формулу расчета процентов от суммы для всех значений.
Для отображения результатов в формате процентов, на вкладке «Главная» в группе «Число», задаем «Процентный формат» с двумя знаками после запятой.

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

Как посчитать проценты в Excel – Проценты от суммы в Excel

Посчитать разницу в процентах Excel

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

где А – старое значение, а B – новое.

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

Как посчитать проценты в Excel – Исходные данные для расчета разницы в процентах

Итак приступим к расчетам процентов:

В ячейке D2 вводим формулу =(C2-B2)/B2.
Копируем формулу в остальные ячейки, используя маркер заполнения.
Применяем процентный формат для результирующих ячеек.

В результате у нас получается следующая таблица:

Как посчитать проценты в Excel – Вычисление разницы в процентах

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

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

Как рассчитать выслугу лет в Excel (простые формулы)

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

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

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

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

В этом учебном пособии рассматриваются:

Расчет лет службы с использованием формулы YEARFRAC

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

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

Вы можете сделать это, используя следующую формулу:

 =ЦЕЛОЕ(ДОЛЯГОД(B2,C2,1))

ФункцияДОЛЯГОД дает общее количество лет между двумя датами (где полные годы показаны как целые числа, а неполные годы показаны десятичными знаками).

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

Расчет лет службы с использованием формулы DATEDIF

Если вам нужно рассчитать использование службы, где вы можете получить количество лет, а также количество месяцев и/или дней, вам лучше использовать формулу DATEDIF (произносится как формула DATE DIF)

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

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

Подсчет только лет службы

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

Ниже приведена формула, которая сделает это:

 =РАЗНДАТ(B2,C2,"y")

Формула РАЗНДАТ принимает начальную и конечную дату в качестве первых двух аргументов и дает общее количество лет между датами ( поскольку я указал Y в качестве третьего аргумента).

Расчет продолжительности службы в годах и месяцах

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

Ниже приведена формула, которая сделает это:

 =РАЗД.(B2,C2,"y")&" Годы" &РАЗД.(B2,C2,"ym")&" Месяцы"

Приведенная выше формула содержит два РАЗНД. функции

Первая используется для расчета общего количества полных лет между двумя датами
Второй используется для расчета общего количества месяцев между двумя датами, но не включает те месяцы, которые завершили год. Например, если разница между двумя датами составляет 14 месяцев, будет возвращено только 2, так как первые 12 месяцев станут годом.

Эти функции DATEDIF комбинируются с текстом «Годы» и «Месяцы», чтобы результат был более содержательным, и пользователь знал, сколько лет и месяцев отработано.

Расчет продолжительности обслуживания в годах, месяцах и днях

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

 =DATEDIF(B2,C2,"y" )&" Years" &DATEDIF(B2,C2,"ym")&" Months" &DATEDIF(B2,C2,"md")&" Days"

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

Я также добавил слова «Годы», «Месяцы» и «Дни», чтобы сделать результат более понятным.

Расчет продолжительности обслуживания до сегодняшнего дня

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

СЕГОДНЯ() дает вам текущую дату на основе настроек вашего компьютера. Это изменчивая формула, что означает, что в следующий раз, когда вы откроете книгу или внесете изменения в книгу, эта формула будет обновлена, и если дата изменилась, будет использоваться новая дата.

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

Вот формула, которая это сделает:

 =РАЗНД.ДАТ(B2,СЕГОДНЯ(),"г")&" Годы" &РАЗНД.ДАТЫ(B2,СЕГОДНЯ(),"гм")&" Месяцы" &РАЗНД. ДАТЫ(B2,СЕГОДНЯ). (),"md")&" Days"

Примечание : Вы также можете использовать эту формулу для расчета возраста человека, если у вас есть дата его рождения

Расчет даты после определенного срока службы

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

Допустим, вы — HR в компании, которая предлагает бонусы за лояльность сотрудникам, проработавшим 10 лет.

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

Ниже приведена формула, которая даст вам эту дату:

 =ДАТА(B2,10*12)

Формула EDATE предназначена для возврата даты после или до указанного количества месяцев, но мы взломали ее, чтобы получить дату через 10 лет.

Для этого вместо количества месяцев в качестве второго аргумента мы указали 10*12, то есть общее количество месяцев за 10 лет.

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

Я также рассказал, как получить дату через определенное количество лет, используя формулу EDATE.

Надеюсь, этот урок был вам полезен.

Другие учебники по Excel, которые вам также могут понравиться:

Получить название дня по дате в Excel (простые формулы)
Как суммировать значения между двумя датами (используя формулу СУММЕСЛИМН)
Вычислить количество месяцев между двумя датами в Excel
Как рассчитать количество дней между двумя датами в Excel
Как добавить месяцы к дате в Excel (простая формула)
Проверить, находится ли дата между двумя заданными датами в Excel (простая формула)

Основы расчета времени в таблице Excel — применение формул в нескольких условиях

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

Хотя вручную вычислить разницу во времени между двумя временами может быть сложно. В Excel значения времени кодируются от 0 до 1.

Основная формула

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

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

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

Далее вам нужно будет изменить числовой формат значений, указанных в ячейках A2, B2 и C2, на Общий. Как уже упоминалось, значения времени кодируются числами от 0 до 1, поэтому значение 6:00 будет равно 0,25 четверти дня.

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

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

Помните, что отрицательные значения времени отображаются как #######.

Чтобы решить эту проблему, необходимо изменить числовой формат значений, указанных в ячейках A2, B2 и C2, на Общий.

Приведенная ниже формула разницы во времени очень полезна.

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

Как бы вы управляли расчетом времени в листе Excel (истекшее время)

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

Вычесть один раз из другого

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

Наиболее распространенная формула Excel, используемая для расчета времени в листах Excel:

= Время окончания – Время начала

Ваша структура данных определяет, какую формулу для расчета разницы во времени вы выберете, например:

Формула	Объяснение
=A2-B2	Помогает вычислить разницу во времени между ячейками A2 и B2.
=ЗНАЧ ВРЕМЕНИ(«8:30 PM») – ЗНАЧ ВРЕМЕНИ(«6:40 AM»)	Помогает рассчитать разницу между конкретным временем.
= ВРЕМЯ (ЧАС (A2), МИНУТА (A2), СЕКУНД (A2)) – ВРЕМЯ (ЧАС (B2), МИНУТА (B2), СЕКУНД (B2))	В ячейках A2 и B2 это помогает вычислить разницу во времени между двумя значениями, игнорируя разницу в датах, поскольку ячейки содержат как значения даты, так и времени.

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

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

код времени	Объяснение
ч	.
ч:мм:сс	Истекшие часы, минуты и секунды, отображаемые как 4:10:20.

Используя клавиатуру, нажмите CTRL + 1 в случае применения пользовательского формата времени. Это откроет диалоговое окно «Формат ячеек», в котором вы должны выбрать опцию «Пользовательский» прямо из списка категорий. Теперь вам нужно ввести временные коды в поле «Тип».

В реальном времени вы увидите четкие результаты формулы, используемой для расчета разницы во времени, на листе Excel. В столбце A вам нужно будет ввести значения времени начала, а в столбце B введите значения времени окончания. В столбцы с C по E скопируйте приведенную ниже формулу:

=$B2-$A2

Выбранный формат времени будет определять шаблон отображения прошедшего времени.

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

Вычисление разницы во времени с помощью функции «Текст»

Чтобы узнать разницу во времени с помощью функции «Текст», можно воспользоваться еще одним удобным способом.

Используя функцию ТЕКСТ, вы можете определить формат формулы.

=ТЕКСТ(Дата окончания – Дата начала, Формат)

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

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

Использование различных формул позволяет получить результаты в разных форматах.

Показать только количество часов:

=ТЕКСТ(B2-A2,»hh»)

Вышеприведенная формула полезна только тогда, когда вам нужно количество часов, прошедших между двумя временными значениями. Когда ваш результат равен 4 часам и 30 минутам, вы получите только 4.

Показать общее количество минут

=ТЕКСТ(B2-A2,“[мм]”)

Показать общее количество секунд

=ТЕКСТ(B2-s2 ]»)

Показать часы и минуты

=ТЕКСТ(B2-A2,»[чч]:мм»)

Показать часы, минуты и секунды

7 EXT A2,“чч:мм:сс”)

Подсчет часов, минут или секунд между двумя временами

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

Расчет часов между двумя временами:

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

= (Время окончания – Время начала) * 24

Предположим, что время начала указано в A2, а время окончания — в B2. Чтобы найти разницу между двукратными значениями, используется простое уравнение B2-A2. Позже вы должны умножить это значение на 24, и результат известен по количеству часов в одном дне:

=(B2-A2) * 24

Функция INT — еще одна замечательная функция, используемая для получения количества полных часов. При этом ваш результат будет сводиться к ближайшему целому числу:

=INT(B2-A2) *24

Сумма минут между двумя значениями времени

Вычисление минут между двукратными значениями требует умножения разница во времени на 1440. Она известна как количество минут в одном дне (24 часа * 60 минут = 1440).

=(Время окончания – Время начала) * 1440

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

= (B2-A2) * 1440

Сумма секунд между временами:

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