Математические формулы

Вычисление текущего остатка

Упражнение

1. Настройте лист, как в следующем примере, или скопируйте этот пример на пустой лист.

2. Чтобы просмотреть итоговое значение, щелкните вне ячейки C3.

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

Инструкции

1. Вводите суммы вложений и расходов в пустые строки прямо под имеющимися данными.

Например, в приведенном выше примере суммы вложений следует вводить в ячейки A4, A5 и т.д., а суммы расходов — в ячейки B4, B5 и т.д.

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

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

Создание таблицы умножения

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

1. Настройте лист со следующей структурой.

3. Введите строку значений из ячейки B3 направо. Например, от 1 до 10.

4. Введите столбец значений из ячейки A4 вниз. Например, от 1 до 10.
5. Выделите все ячейки в диапазоне, кроме ячеек A1 и A2.
6. В меню Данные выберите команду Таблица подстановки.
7. В поле Подставлять значения по строкам в введите A1.
8. В поле Подставлять значения по столбцам в введите A2.
9. Нажмите кнопку OK.
10. При необходимости закрепите таблицу данных, преобразовав результат в свои значения.

Инструкции

1. Выделите в таблице данных все рассчитанные значения.

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

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

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

Сложение чисел

Упражнение

Числа можно складывать при вводе в ячейку. Например, если ввести в ячейку =5+10, будет показан результат 15.

Сложение всех чисел, стоящих подряд в строке или столбце

Для выполнения этой задачи используется функция Автосумма.

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

2. Нажмите на панели инструментов Стандартные кнопку Автосумма, а затем нажмите клавишу ВВОД.

Сложение чисел, расположенных вразброс

Для выполнения этой задачи используется функция СУММ, как показано в следующем примере.

Скопируйте пример на пустой лист.

1 2 3 4 5 6 7

A B Продавец Счет Батурин 15 000 Батурин 9 000 Рощин 8 000 Рощин 20 000 Батурин 5 000 Тихонов 22 500 Формула Описание (результат) =СУММ(B2:B3;B5) Сложение двух счетов от Батурина и одного от Рощина (44 000) =СУММ(B2;B5;B7) Сложение отдельных счетов от Батурина, Рощина и Тихонова (57 500)

Примечание. Функция СУММ может включать до 30 ссылок на диапазоны ячеек.

Сложение чисел в зависимости от одного условия

Для суммирования значений по одному диапазону на основе данных другого диапазона используется функция СУММЕСЛИ, как в следующем примере.

Скопируйте пример на пустой лист.

1 2 3 4 5 6 7

A B Продавец Счет Батурин 15 000 Батурин 9 000 Рощин 8 000 Рощин 20 000 Батурин 5 000 Тихонов 22 500 Формула Описание (результат) =СУММЕСЛИ(A2:A7,"Батурин";B2:B7) Сумма по счетам Батурина (29000) =СУММЕСЛИ(B2:B7;">=9000";B2:B7) Сумма по счетам, значения которых больше либо равны 9 000 (66500) =СУММЕСЛИ(B2:B7;"<9000";B2:B7) Сумма по счетам, значения которых меньше 9 000 (13000)

Функция СУММЕСЛИ использует следующие аргументы.

Формула с функцией СУММЕСЛИ

1 Диапазон для вычисления: эти ячейки строки проверяются на соответствие заданному условию.

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

3 Диапазон для сложения: ячейки, значения которых будут сложены, если в этой строке выполняется заданное условие.

Сложение чисел в зависимости от нескольких условий

Для выполнения этой задачи используются функции ЕСЛИ и СУММ, как показано в следующем примере.

Скопируйте пример на пустой лист.

1 2 3 4 5 6 7 8 9 10 11

А B C D Район Продавец Тип Продажи Южный Батурин Напитки 3571 Западный Белов Молоко 3338 Восточный Рощин Напитки 5122 Северный Рощин Молоко 6239 Южный Тихонов Фрукты 8677 Южный Белов Мясо 450 Южный Белов Мясо 7673 Восточный Рощин Фрукты 664 Северный Белов Фрукты 1500 Южный Тихонов Мясо 6596 Формула Описание (результат) =СУММ(ЕСЛИ((A2:A11="Южный")*(C2:C11="Мясо");D2:D11)) Объемы продаж мяса в южном районе (14719) =СУММ(ЕСЛИ((A2:A11="Южный")+(A2:A11="Восточный");D2:D11)) Объемы продаж по южному и восточному районам (32753)

Примечание. Формулы в примере необходимо ввести как формулу массива. После копирования этого примера на пустой лист выделите ячейку с формулой. Нажмите клавишу F2, а затем нажмите клавиши CTRL+SHIFT+ВВОД. Если формула не будет введена как формула массива, будет возвращена ошибка #ЗНАЧ!

Сложение чисел в зависимости от условий, хранящихся в отдельном диапазоне

Для выполнения этой задачи используется функция БДСУММ, как показано в следующем примере.

Упражнение

1 2 3 4 5 6 7 8 9 10 11

A B C D Район Продавец Тип Продажи Южный Батурин Напитки 3571 Западный Белов Молоко 3338 Восточный Рощин Напитки 5122 Северный Рощин Молоко 6239 Южный Тихонов Фрукты 8677 Южный Белов Мясо 450 Южный Белов Мясо 7673 Восточный Рощин Фрукты 664 Северный Белов Фрукты 1500 Южный Тихонов Мясо 6596 Район Продавец Тип Продажи Южный Мясо Фрукты Формула Описание (результат) =БДСУММ(A1:D11;"Продажи";A12:D13) Объемы продаж мяса в южном районе (14719) =БДСУММ(A1:D11;"Продажи";A12:D14) Объемы продаж мяса и фруктов в южном районе (25560)

Функция БДСУММ использует следующие аргументы.

1 Диапазон для вычисления: список суммируемых данных.

2 Поле: подпись суммируемого столбца.

3 Условие: диапазон ячеек, содержащих условия.

Сложение чисел в зависимости от нескольких условий с использованием мастера суммирования

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

1. Выделите ячейку в списке.
2. В меню Сервис укажите на пункт Мастер и выберите команду Суммировать по условию.

Если команда Суммировать по условию отсутствует в подменю Мастер меню Сервис, загрузите надстройку «Мастер суммирования».

Инструкции

1. В меню Сервис выберите команду Надстройки.
2. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.
3. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.
4. Следуйте инструкциям программы установки, если они имеются.

3. Следуйте инструкциям мастера.

Примечание. Формулы, созданные мастером суммирования, являются формулами массива. После изменения этих формул необходимо нажать одновременно клавиши CTRL+SHIFT+ВВОД, чтобы блокировать их.

Вычисление среднего значения

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

1. Выделите ячейку снизу или справа от чисел, среднее значение которых требуется найти.
2. Нажмите на панели инструментов Стандартные стрелку рядом с кнопкой Автосумма , а затем выберите команду Среднее и нажмите клавишу ВВОД.

Вычисление среднего значения ячеек, расположенных вразбрс

Для выполнения этой задачи используется функция СРЗНАЧ.

Упражнение

Скопируйте пример на пустой лист.

1 2 3 4 5 6 7

A Данные 10 7 9 27 0 4 Формула Описание (результат) =СРЗНАЧ(A2:A7) Вычисление среднего значения всех чисел списка (9,5) =СРЗНАЧ(A2:A4;A7) Вычисление среднего значения трех первых и последнего чисел списка (7,5) = СРЗНАЧ ( ЕСЛИ (A2:A7<>0; A2:A7;"")) Вычисление среднего значения всех чисел списка, кроме нулевых, таких как в ячейке A6 (11,4)

Примечание. Последнюю формулу в примере необходимо ввести как формулу массива. После копирования этого примера на пустой лист выделите ячейку A11. Нажмите клавишу F2, а затем нажмите клавиши CTRL+SHIFT+ВВОД. Если формула не будет введена как формула массива, будет возвращена ошибка #ЗНАЧ!

Вычисление среднего взвешенного значения

Для выполнения этой задачи используются функции СУММПРОИЗВ и СУММ.

Упражнение

Скопируйте пример на пустой лист.

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

1 2 3 4

A B Цена единицы товара Количество единиц товара 20 500 25 750 35 200 Формула Описание (результат) = СУММПРОИЗВ (A2:A4;B2:B4)/ СУММ (B2:B4) Деление общей стоимости всех трех покупок на общее количество приобретенных единиц товара (24,66)

Вычисление среднего значения всех чисел, кроме нулевых (0)

Для выполнения этой задачи используются функции СРЗНАЧ и ЕСЛИ.

Упражнение

Скопируйте пример на пустой лист.

Примечание. Формулу в примере необходимо ввести как формулу массива. После копирования этого примера на пустой лист выделите ячейку A9. Нажмите клавишу F2, а затем нажмите клавиши CTRL+SHIFT+ENTER. Если формула не будет введена как формула массива, будет возвращена ошибка #ЗНАЧ!

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

Для выполнения этой задачи используются оператор вычитания (-), деления (/) и функция ABS.

Упражнение

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2	A B Ноябрь Декабрь 2342 2500 Формула Описание (результат) =(B2-A2)/ABS(A2) Разница в процентах (0,06746 или 6,75%)

Примечание. Чтобы просмотреть числа в виде процентов, выделите ячейку и выберите в меню Формат команду Ячейки. На вкладке Число в списка Числовые форматы выберите вариант Процентный.

Поиск наименьшего или наибольшего числа в диапазоне

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

1. Выделите ячейку снизу или справа от чисел, наименьшее из которых требуется найти.
2. Нажмите стрелку рядом с кнопкой Автосумма  выберите команду Минимум (поиск наименьшего) или Максимум (поиск наибольшего), а затем нажмите клавишу ENTER.

Для ячеек, расположенных вразброс

Для выполнения этой задачи используется функция МИН, МАКС, НАИМЕНЬШИЙ или НАИБОЛЬШИЙ.

Упражнение

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2 3 4 5 6 7

A Данные 10 7 9 27 0 4 Формула Описание (результат) =МИН(A2:A7) Поиск наименьшего числа в диапазоне (0) =МАКС(A2:A7) Поиск наибольшего числа в диапазоне (27) =НАИМЕНЬШИЙ(A2:A7;2) Поиск второго наименьшего числа в диапазоне (4) =НАИБОЛЬШИЙ(A2:A7;3) Поиск третьего наибольшего числа в диапазоне (9)

Преобразование единиц измерения

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

Если данная функция недоступна, установите и загрузите надстройку «Пакет анализа».

Инструкции

1. В меню Сервис выберите команду Надстройки.
2. В списке надстроек выберите Пакет анализа и нажмите кнопку OK.
3. Выполните инструкции программы установки, если это необходимо.

Упражнение

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2

А Данные 6 Формула Описание (результат) =ПРЕОБР(A2;"C";"F") Преобразование 6 градусов по Цельсию в градусы по Фаренгейту (42,8) =ПРЕОБР(A2;"tsp";"tbs") Преобразование 6 чайных ложек в столовые ложки (2) =ПРЕОБР(A2;"gal";"l") Преобразование 6 галлонов в литры (22,71741274) =ПРЕОБР(A2;"mi";"km") Преобразование 6 миль в километры (9,656064) =ПРЕОБР(A2;"km";"mi") Преобразование 6 километров в мили (3,728227153) =ПРЕОБР(A2;"in";"ft") Преобразование 6 дюймов в футы (0,5) =ПРЕОБР(A2;"cm";"in") Преобразование 6 сантиметров в дюймы (2,362204724)

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

Подсчет чисел, больших или меньших чем иное число

Для выполнения этой задачи используется функция СЧЕТЕСЛИ.

Упражнение

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2 3 4 5 6 7

А B Продавец Счет Батурин 15 000 Батурин 9 000 Рощин 8 000 Рощин 20 000 Батурин 5 000 Тихонов 22 500 Формула Описание (результат) =СЧЕТЕСЛИ(B2:B7;">9000") Числа большие 9000 (3) =СЧЕТЕСЛИ(B2:B7;"<=9000") Числа, меньшие или равные 9000 (3)

Увеличение или уменьшение числа на заданное количество процентов

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

Упражнение

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2

A B Число Процент увеличения 23 3% Формула Описание (результат) =A2*(1+5%) Увеличение числа в ячейке A2 на 5% (24,15) =A2*(1+B2) Увеличение числа в ячейке A2 на число процентов, указанное в ячейке B2: 3% (23,69) =A2*(1-B2) Уменьшение числа в ячейке A2 на число процентов, указанное в ячейке B2: 3% (22,31)

Примечание. При использовании числа, после которого стоит знак процентов (%), это число считается заданным в сотых долях. Пример: 5% считается 0,05.

Округление числа

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

На листе

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

2. Чтобы после запятой отображалось больше или меньше знаков, нажмите на панели инструментов Форматирование кнопку Увеличить разрядность  или Уменьшить разрядность .

Во встроенном числовом формате

1. В меню Формат выберите команду Ячейки, а затем откройте вкладку Число.

2. В списке Числовые форматы выберите Денежный, Финансовый, Процентный или Экспоненциальный.

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

Округление числа вверх

Для выполнения этой задачи используются функции ОКРУГЛВВЕРХ, ЧЕТН и НЕЧЕТ.

Пример листа

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2 3 4

A Данные 20,3 -5,9 12,5493 Формула Описание (результат) =ОКРУГЛВВЕРХ(A2;0) Округление числа 20,3 до ближайшего целого (21) =ОКРУГЛВВЕРХ(A3;0) Округление числа -5,9 вверх (-6) =ОКРУГЛВВЕРХ(A4;2) Округление числа 12,5493 до второго знака после запятой (12,55) =ЧЁТН(A2) Округление числа 20,3 до ближайшего четного (22) =НЕЧЁТ(A2) Округление числа 20,3 до ближайшего нечетного (21)

Округление числа вниз

Для выполнения этой задачи используется функция ОКРУГЛВНИЗ.

Упражнение

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2 3 4

A Данные 20,3 -5,9 12,5493 Формула Описание (результат) =ОКРУГЛВНИЗ(A2;0) Округление числа 20,3 вниз до ближайшего целого (20) =ОКРУГЛВНИЗ(A3;0) Округление числа -5,9 вниз (-5) =ОКРУГЛВНИЗ(A4;2) Округление числа вниз до второго знака после запятой (12,54)

Округление числа до ближайшего

Для выполнения этой задачи используется функция ОКРУГЛ.

Упражнение

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2 3 4

A Данные 20,3 5,9 -5,9 Формула Описание (результат) =ОКРУГЛ(A2;0) Округление числа 20,3 вниз, так как дробная часть меньше 0,5 (20) =ОКРУГЛ(A3;0) Округление числа 5,9 вверх, так как дробная часть больше 0,5 (6) =ОКРУГЛ(A4;0) Округление числа -5,9 вниз, так как дробная часть меньше 0,5 (-6)

Округление числа до указанного числа знаков после запятой

Для выполнения этой задачи используется функция ОКРУГЛ.

Упражнение

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2 3

A Данные 1,25 30,452 Формула Описание (результат) =ОКРУГЛ(A2;1) Округление числа до одного знака после запятой. Поскольку округляемая часть — 0,05 или больше, число округляется вверх (результат: 1,3) =ОКРУГЛ(A3;2) Округление числа до двух знаков после запятой. Поскольку округляемая часть (0,002) меньше 0,005, число округляется вниз (результат: 30,45)

Округление числа до указанного количества значимых разрядов

Для выполнения этой задачи используются функции ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ДЛСТР и ЦЕЛОЕ.

Упражнение

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

1 2 3

A Данные 5492820 22230 Формула Описание (результат) =ОКРУГЛ(A2;3-ДЛСТР(ЦЕЛОЕ(A2))) Округление верхнего числа до 3 значимых разрядов (5490000) =ОКРУГЛВНИЗ(A3;3-ДЛСТР(ЦЕЛОЕ(A3))) Округление нижнего числа вниз до 3 значимых разрядов (22200) =ОКРУГЛВВЕРХ(A2;6-ДЛСТР(ЦЕЛОЕ(A2))) Округление верхнего числа вверх до 5 значимых разрядов (5492900)