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

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

Упражнение

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

1

2

3

A

B

C

Вложения

Расходы

Остаток

1000р.

625р.

=СУММ(A2;-B2)

1000

740

=СУММ(C2;A3;-B3)

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

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

Инструкции

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

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

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

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

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

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

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

1

2

3

А

1

1

=A1*A2

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

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

Инструкции

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

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

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)

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

2

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

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

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

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

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

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

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

Инструкции

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

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

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

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

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

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

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

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

Упражнение

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

    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)

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

Упражнение

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

1

2

3

4

5

6

7

A

Данные

10

7

9

27

0

4

Формула

Описание (результат)

= СРЗНАЧ ( ЕСЛИ (A2:A7<>0; A2:A7;""))

Вычисление среднего значения всех чисел списка, кроме нулевых, таких как в ячейке A6 (11,4)

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

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

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

Упражнение

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

1

2

A

B

Ноябрь

Декабрь

2342

2500

Формула

Описание (результат)

=(B2-A2)/ABS(A2)

Разница в процентах (0,06746 или 6,75%)

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

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

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

  1. Выделите ячейку снизу или справа от чисел, наименьшее из которых требуется найти.
  2. Нажмите стрелку рядом с кнопкой Автосумма 3 выберите команду Минимум (поиск наименьшего) или Максимум (поиск наибольшего), а затем нажмите клавишу 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. Чтобы после запятой отображалось больше или меньше знаков, нажмите на панели инструментов Форматирование кнопку Увеличить разрядность 6 или Уменьшить разрядность 5.

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

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)

שינוי אחרון: 11/08/2011, 12:32