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

Как посчитать возраст человека в Microsoft Excel

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

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

Подготовительные действия

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

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

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

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

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

Читайте также: Как использовать функцию СЕГОДНЯ в Microsoft Excel

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

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

Подписаться

Создание формулы для подсчета возраста в Microsoft Excel

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

1. Выберите первую ячейку в столбце «Возраст» и вызовите функцию =ДОЛЯГОДА(.

2. Для наглядности перейдем в окно «Аргументы функции», чтобы не возникло проблем с выбором подходящих ячеек.

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

4. Конечная дата является сегодняшним днем, то есть нужно выбрать значение функции СЕГОДНЯ, которую мы объявили ранее.

5. Базис – это метод вычисления дня, который для возраста должен равняться единице.

6. После введения всей информации нажмите Enter и ознакомьтесь с результатом.

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

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

Растягивание формулы для подсчета возраста

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

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

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

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

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

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

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

Как в Excel вычислить часы между двумя значениями времени

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

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

=ЕСЛИ(конец>начало; конец-начало; 1-начало+конец)

Описание

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

=ЕСЛИ(C5>B5;C5-B5;1-B5+C5)

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

Пояснение

Сложность подсчета количества часов между двумя моментами времени следует из времени, которое пересекает полночь. Это связано с тем, что время, которое пересекает полночь, часто имеет время начала позже, чем время окончания (т.е. начало в 21:00, окончание в 6:00). В этой статье представлены несколько формул решений в зависимости от ситуации.

Простой расчет продолжительности

Когда время начала и время окончания совпадают с одним и тем же днем, вычислить продолжительность в часах несложно. Например, с временем начала 9:00 и временем окончания 17:00 вы можете просто использовать эту формулу:

= конец-начало
= 17:00–8:00
= 0,375-0,708 = 0,333
Результат: 8 часов

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

Однако, когда время пересекает границу суток (полночь), все может усложниться.

Как Excel отслеживает время

В Excel один день равен 1, что соответствует 24 часам. Это означает, что время и часы являются дробными значениями от 1, как показано в таблице ниже:

Когда наступает полночь

Вычислить прошедшее время сложнее, если время пересекает границу суток (полночь). Например, если в один день время начала — 22:00, а время окончания — 5:00 утра следующего дня, то время окончания на самом деле меньше, чем время начала, и приведенная выше формула вернет отрицательное значение, а Excel отобразит строку хеш-символов (########).
Чтобы исправить эту проблему, вы можете использовать эту формулу для времени, которое проходит границу суток:

=1-начало+конец

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

=ЕСЛИ(конец>начало; конец-начало; 1-начало+конец)

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

Альтернатива c функцией ОСТАТ

Функция ОСТАТ представляет собой элегантный способ упростить приведенную выше формулу. Используя функцию ОСТАТ с делителем 1, мы можем создать формулу для обработки обеих ситуаций:

=ОСТАТ(конец-начало;1)

Эта формула учитывает отрицательное время, используя функцию ОСТАТ, чтобы «перевернуть» отрицательное значение на требуемое положительное значение. Поскольку эта формула обрабатывает время в один и тот же день и время, после полуночи, то нам не нужен условный оператор ЕСЛИ.
Примечание: ни одна из приведенных выше формул не будет обрабатывать длительность более 24 часов.
Если вам это нужно, см. параметр «дата + время» ниже.

Упрощение с помощью даты + времени

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

43922.41667

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

=C5-B5 ‘конец-начало

Результат отформатирован в пользовательском формате: [h]:mm для отображения прошедших часов. Эта формула правильно рассчитает часы между двумя временными метками за один день или за несколько дней.

Автоматическое заполнение данных в ячейках рабочего листа

Введение в Excel

Справка по Excel и обучение

Введение в Excel

Введение в Excel

Автоматическое заполнение данных в ячейках листа

• Создать новую книгу
  

  Статья

• Вставка или удаление рабочего листа
  

  Статья

• Переместить или скопировать рабочие листы или данные рабочего листа
  

  Статья

• Печать листа или книги
  

  Статья

• Используйте Excel в качестве калькулятора
  

  Статья

• Автоматическое заполнение данных в ячейках листа
  

  Статья

• Создать раскрывающийся список
  

  Статья

Следующий:

Строки и столбцы

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

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

Найдите больше видео на YouTube-канале Майка Гирвина, excelisfun .

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

   Для таких серий, как 1, 2, 3, 4, 5…, введите 1 и 2 в первые две ячейки. Для серий 2, 4, 6, 8…, тип 2 и 4.

   Для серий 2, 2, 2, 2… введите 2 только в первую ячейку.

2. Перетащите маркер заполнения.

3. При необходимости нажмите  Параметры автозаполнения   и выберите нужный вариант.

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

Операторы вычисления и приоритет в Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Дополнительно…Меньше

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

Типы операторов

Существует четыре различных типа операторов вычисления: арифметические операции, сравнение, конкатенация текста и ссылка.

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

93

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

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

Объединение диапазонов ячеек для вычислений с помощью этих операторов.

Порядок, в котором Excel выполняет операции в формулах

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

Формулы вычисляют значения в определенном порядке. Формула в Excel всегда начинается со знака равенства (=). Знак равенства сообщает Excel, что следующие за ним символы составляют формулу. После этого знака равенства может быть ряд вычисляемых элементов (операндов), которые разделены операторами вычисления. Excel вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.

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

9

Чтобы изменить порядок оценки, заключите в скобки ту часть формулы, которая будет вычисляться первой. Например, следующая формула дает значение 11, поскольку Excel вычисляет умножение перед сложением. Формула сначала умножает 2 на 3, а затем прибавляет к результату 5.

=5+2*3

Напротив, если вы используете круглые скобки для изменения синтаксиса, Excel складывает 5 и 2 вместе, а затем умножает результат на 3, чтобы получить 21.

=(5+2)*3

В приведенном ниже примере круглые скобки, заключающие первую часть формулы, заставят Excel сначала вычислить B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.

=(B4+25)/СУММ(D5:F5)

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

Как Excel преобразует значения в формулах

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

Нужна дополнительная помощь?

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

См. также

• Базовая математика в Excel

• Используйте Excel в качестве калькулятора

• Обзор формул в Excel

• Как избежать неработающих формул