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

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

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

Excel поддерживает следующие операторы:

• Арифметические операции:
  • сложение (+);
  • умножение (*);
  • нахождение процента (%);
  • вычитание (-);
  • деление (/);
  • экспонента (^).
• Операторы сравнения:
  • = равно;
  • < меньше;
  • > больше;
  • <= меньше или равно;
  • >= больше или равно;
  • <> не равно.
• Операторы связи:
  • : диапазон;
  • ; объединение;
  • & оператор соединения текстов.

Таблица 22. Примеры формул

Упражнение

Вставка формулы -25-А1+АЗ

Предварительно введите любые числа в ячейки А1 и A3.

1. Выберите необходимую ячейку, например В1.
2. Начните ввод формулы со знака=.
3. Введите число 25, затем оператор (знак -).
4. Введите ссылку на первый операнд, например щелчком мыши на нужную ячейку А1.
5. Введите следующий оператор(знак +).
6. Щелкните мышью в той ячейке, которая является вторым операндом в формуле.
7. Завершите ввод формулы нажатием клавиши Enter . В ячейке В1 получите результат.

Автосуммирование

Кнопка Автосумма (AutoSum) - ∑ может использоваться для автоматического создания формулы, которая суммирует область соседних ячеек, находящихся непосредственно слева в данной строке и непосредственно выше в данном столбце.

1. Выберите ячейку, в которую надо поместить результат суммирования.
2. Щелкните кнопку Автосумма - ∑ или нажмите комбинацию клавиш Alt+=. Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.
3. Нажмите Enter для принятия области, которую выбрала программа Excel, или выберите с помощью мыши новую область и затем нажмите Enter.

Функция "Автосумма" автоматически трансформируется в случае добавления и удаления ячеек внутри области.

Упражнение

Создание таблицы и расчет по формулам

1. Введите числовые данные в ячейки, как показано в табл. 23.

	А	В	С	D	Б	F
1
2		Магнолия	Лилия	Фиалка	Всего
3	Высшее	25	20	9
4	Среднее спец.	28	23	21
5	ПТУ	27	58	20
в	Другое	8	10	9
7	Всего
8	Без высшего

Таблица 23. Исходная таблица данных

1. Выберите ячейку В7, в которой будет вычислена сумма по вертикали.
2. Щелкните кнопку Автосумма - ∑ или нажмите Alt+= .
3. Повторите действия пунктов 2 и 3 для ячеек С7 и D7.

Вычислите количество сотрудников без высшего образования (по формуле В7-ВЗ).

1. Выберите ячейку В8 и наберите знак (=).
2. Щелкните мышью в ячейке В7, которая является первым операндом в формуле.
3. Введите с клавиатуры знак (-) и щелкните мышью в ячейке ВЗ, которая является вторым операндом в формуле (будет введена формула).
4. Нажмите Enter (в ячейке В8 будет вычислен результат).
5. Повторите пункты 5-8 для вычислений по соответствующим формулам в ячейках С8 и 08.
6. Сохраните файл с именем Образование_сотрудников.х1s.

Таблица 24. Результат расчета

	А	B	С	D	Е	F
1	Распределение сотрудников по образованию
2		Магнолия	Лилия	Фиалка	Всего
3	Высшее	25	20	9
4	Среднее спец.	28	23	21
5	ПТУ	27	58	20
6	Другое	8	10	9
7	Всего	88	111	59
8	Без высшего	63	91	50

Тиражирование формул при помощи маркера заполнения

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

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

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

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

Упражнение

Тиражирование формул

1.Откройте файл Образование_сотрудников.х1s.

1. Введите в ячейку ЕЗ формулу для автосуммирования ячеек =СУММ(ВЗ:03).
2. Скопируйте, перетащив маркер заполнения, формулу в ячейки Е4:Е8.
3. Просмотрите как меняются относительные адреса ячеек в полученных формулах (табл. 25) и сохраните файл.

	А	В	С	D	Е	F
1	Распределение сотрудников по образованию
2		Магнолия	Лилия	Фиалка	Всего
3	Высшее	25	20	9	=СУММ{ВЗ:03)
4	Среднее спец.	28	23	21	=СУММ(В4:04)
5	ПТУ	27	58	20	=СУММ(В5:05)
6	Другое	8	10	9	=СУММ(В6:06)
7	Всего	88	111	58	=СУММ(В7:07)
8	Без высшего	63	91	49	=СУММ(В8:08)

Таблица 25. Изменение адресов ячеек при тиражировании формул

Относительные и абсолютные ссылки

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

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

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

Абсолютная ссылка на ячейку.иди область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.

Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).

1. Создайте таблицу, аналогичную представленной ниже.

Таблица 26. Расчет зарплаты

1. В ячейку СЗ введите формулу для расчета зарплаты Иванова =В1*ВЗ.

При тиражировании формулы данного примера с относительными ссылками в ячейке С4 появляется сообщение об ошибке (#ЗНАЧ!), так как изменится относительный адрес ячейки В1, и в ячейку С4 скопируется формула =В2*В4;

1. Задайте абсолютную ссылку на ячейку В1, поставив курсор в строке формул на В1 и нажав клавишу F4, Формула в ячейке СЗ будет иметь вид =$В$1*ВЗ.
2. Скопируйте формулу в ячейки С4 и С5.
3. Сохраните файл (табл. 27) под именем Зарплата.xls.

Таблица 27. Итоги расчета зарплаты

Имена в формулах

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

• имена могут содержать не более 255 символов;
• имена должны начинаться с буквы и могут содержать любой символ, кроме пробела;
• имена не должны быть похожи на ссылки, такие, как ВЗ, С4;
• имена не должны использовать функции Excel, такие, как СУММ, ЕСЛИ и т. п.

В меню Вставка, Имя существуют две различные команды создания именованных областей: Создать и Присвоить.

Команда Создать позволяет задать (ввести) требуемое имя (только одно ), команда Присвоить использует метки, размещенные на рабочем листе, в качестве имен областей (разрешается создавать сразу несколько имен ).

Создание имени

1. Выделите ячейку В1 (табл. 26).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Присвоить (Define) .
3. Введите имя Часовая ставка и нажмите ОК .
4. Выделите ячейку В1 и убедитесь, что в поле имени указано Часовая ставка .

Создание нескольких имен

1. Выделите ячейки ВЗ:С5 (табл. 27).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Создать (Create) , появится диалоговое окно Создать имена (рис. 88).
3. Убедитесь, что переключатель в столбце слева помечен и нажмите ОК .
4. Выделите ячейки ВЗ:СЗ и убедитесь, что в поле имени указано Иванов.

Рис. 88. Диалоговое окно Создать имена

Можно в формулу вставить имя вместо абсолютной ссылки.

1. В строке формул установите курсор в то место, где будет добавлено имя.
2. Выберите в меню Вставка, Имя (Insert, Name) команду Вставить (Paste), появится диалоговое окно Вставить имена.

1. Выберите нужное имя из списка и нажмите ОК.

Ошибки в формулах

Бели при вводе формул или данных допущена ошибка, то в результирующей ячейке появляется сообщение об ошибке. Первым символом всех значений ошибок является символ #. Значения ошибок зависят от вида допущенной ошибки.

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

Ошибка # # # # появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.

Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.

Ошибка #Н/Д! является сокращением термина "неопределенные данные". Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.

Ошибка #ИМЯ? появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.

Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.

Ошибка #ЧИСЛО! появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.

Ошибка #ЗНАЧ! появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введен текст.

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

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

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

Функции в Excel

Более сложные вычисления в таблицах Excel осуществляются с помощью специальных функций (рис. 90). Список категорий функций доступен при выборе команды Функция в меню Вставка (Insert, Function).

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

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

Рис. 90. Мастер функций

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

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

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

Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ .

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

Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ .

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

Упражнение

Вычисление величины среднего значения для каждой строки в файле Образование.хls.

1. Выделите ячейку F3 и нажмите на кнопку мастера функций.
2. В первом окне диалога мастера функций из категории Статистические выберите функцию СРЗНАЧ , нажмите на кнопку Далее .
3. Во втором диалоговом окне мастера функций должны быть заданы аргументы. Курсор ввода находится в поле ввода первого аргумента. В это поле в качестве аргумента число! введите адрес диапазона B3:D3 (рис. 91).
4. Нажмите ОК .
5. Скопируйте полученную формулу в ячейки F4:F6 и сохраните файл (табл. 28).

Рис. 91. Ввод аргумента в мастере функций

Таблица 28. Таблица результатов расчета с помощью мастера функций

	А	В	С	D	Е	F
1	Распределение сотрудников по образованию
2		Магнолия	Лилия	Фиалка	Всего	Среднее
3	Высшее	25	20	9	54	18
4	Среднее спец.	28	23	21	72	24
8	ПТУ	27	58	20	105	35
в	Другое	8	10	9	27	9
7	Всего	88	111	59	258	129

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

В этом уроке Вы увидите, как при помощи Excel быстро вычислить проценты, познакомитесь с основной формулой расчёта процентов и узнаете несколько хитростей, которые облегчат Вашу работу с процентами. Например, формула расчёта процентного прироста, вычисление процента от общей суммы и кое-что ещё.

Умение работать с процентами может оказаться полезным в самых разных сферах жизни. Это поможет Вам, прикинуть сумму чаевых в ресторане, рассчитать комиссионные, вычислить доходность какого-либо предприятия и степень лично Вашего интереса в этом предприятии. Скажите честно, Вы обрадуетесь, если Вам дадут промокод на скидку 25% для покупки новой плазмы? Звучит заманчиво, правда?! А сколько на самом деле Вам придётся заплатить, посчитать сможете?

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

Базовые знания о процентах

Термин Процент (per cent) пришёл из Латыни (per centum) и переводился изначально как ИЗ СОТНИ . В школе Вы изучали, что процент – это какая-то часть из 100 долей целого. Процент рассчитывается путём деления, где в числителе дроби находится искомая часть, а в знаменателе – целое, и далее результат умножается на 100.

Основная формула для расчёта процентов выглядит так:

(Часть/Целое)*100=Проценты

Пример: У Вас было 20 яблок, из них 5 Вы раздали своим друзьям. Какую часть своих яблок в процентах Вы отдали? Совершив несложные вычисления, получим ответ:

(5/20)*100 = 25%

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

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

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

Основная формула расчёта процента в Excel

Основная формула расчёта процента в Excel выглядит так:

Часть/Целое = Процент

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

А теперь посмотрим, как расчёт процентов в Excel может помочь в реальной работе с данными. Допустим, в столбец В у Вас записано некоторое количество заказанных изделий (Ordered), а в столбец С внесены данные о количестве доставленных изделий (Delivered). Чтобы вычислить, какая доля заказов уже доставлена, проделаем следующие действия:

• Запишите формулу =C2/B2 в ячейке D2 и скопируйте её вниз на столько строк, сколько это необходимо, воспользовавшись маркером автозаполнения.
• Нажмите команду Percent Style (Процентный формат), чтобы отображать результаты деления в формате процентов. Она находится на вкладке Home (Главная) в группе команд Number (Число).
• При необходимости настройте количество отображаемых знаков справа от запятой.
• Готово!

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

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

Расчёт процента от общей суммы в Excel

На самом деле, пример, приведённый , есть частный случай расчёта процента от общей суммы. Чтобы лучше понять эту тему, давайте рассмотрим ещё несколько задач. Вы увидите, как можно быстро произвести вычисление процента от общей суммы в Excel на примере разных наборов данных.

Пример 1. Общая сумма посчитана внизу таблицы в конкретной ячейке

Очень часто в конце большой таблицы с данными есть ячейка с подписью Итог, в которой вычисляется общая сумма. При этом перед нами стоит задача посчитать долю каждой части относительно общей суммы. В таком случае формула расчёта процента будет выглядеть так же, как и в предыдущем примере, с одним отличием – ссылка на ячейку в знаменателе дроби будет абсолютной (со знаками $ перед именем строки и именем столбца).

Например, если у Вас записаны какие-то значения в столбце B, а их итог в ячейке B10, то формула вычисления процентов будет следующая:

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

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

Пример 2. Части общей суммы находятся в нескольких строках

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

В этом случае используем функцию SUMIF (СУММЕСЛИ). Эта функция позволяет суммировать только те значения, которые отвечают какому-то определенному критерию, в нашем случае – это заданный продукт. Полученный результат используем для вычисления процента от общей суммы.

SUMIF(range,criteria,sum_range)/total
=СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)/общая сумма

В нашем примере столбец A содержит названия продуктов (Product) – это диапазон . Столбец B содержит данные о количестве (Ordered) – это диапазон_суммирования . В ячейку E1 вводим наш критерий – название продукта, по которому необходимо рассчитать процент. Общая сумма по всем продуктам посчитана в ячейке B10. Рабочая формула будет выглядеть так:

SUMIF(A2:A9,E1,B2:B9)/$B$10
=СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10

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

SUMIF(A2:A9,"cherries",B2:B9)/$B$10
=СУММЕСЛИ(A2:A9;"cherries";B2:B9)/$B$10

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

=(SUMIF(A2:A9,"cherries",B2:B9)+SUMIF(A2:A9,"apples",B2:B9))/$B$10
=(СУММЕСЛИ(A2:A9;"cherries";B2:B9)+СУММЕСЛИ(A2:A9;"apples";B2:B9))/$B$10

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

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

Формула Excel, вычисляющая изменение в процентах (прирост/уменьшение)

(B-A)/A = Изменение в процентах

Используя эту формулу в работе с реальными данными, очень важно правильно определить, какое значение поставить на место A , а какое – на место B .

Пример: Вчера у Вас было 80 яблок, а сегодня у Вас есть 100 яблок. Это значит, что сегодня у Вас на 20 яблок больше, чем было вчера, то есть Ваш результат – прирост на 25%. Если же вчера яблок было 100, а сегодня 80 – то это уменьшение на 20%.

Итак, наша формула в Excel будет работать по следующей схеме:

(Новое значение – Старое значение) / Старое значение = Изменение в процентах

А теперь давайте посмотрим, как эта формула работает в Excel на практике.

Пример 1. Расчёт изменения в процентах между двумя столбцами

Предположим, что в столбце B записаны цены прошлого месяца (Last month), а в столбце C – цены актуальные в этом месяце (This month). В столбец D внесём следующую формулу, чтобы вычислить изменение цены от прошлого месяца к текущему в процентах.

Эта формула вычисляет процентное изменение (прирост или уменьшение) цены в этом месяце (столбец C) по сравнению с предыдущим (столбец B).

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

Пример 2. Расчёт изменения в процентах между строками

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

Здесь C2 это первое значение, а C3 это следующее по порядку значение.

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

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

Например, вот так будет выглядеть формула для расчёта процентного изменения для каждого месяца в сравнении с показателем Января (January):

Когда Вы будете копировать свою формулу из одной ячейки во все остальные, абсолютная ссылка останется неизменной, в то время как относительная ссылка (C3) будет изменяться на C4, C5, C6 и так далее.

Расчёт значения и общей суммы по известному проценту

Как Вы могли убедиться, расчёт процентов в Excel – это просто! Так же просто делается расчёт значения и общей суммы по известному проценту.

Пример 1. Расчёт значения по известному проценту и общей сумме

Предположим, Вы покупаете новый компьютер за $950, но к этой цене нужно прибавить ещё НДС в размере 11%. Вопрос – сколько Вам нужно доплатить? Другими словами, 11% от указанной стоимости – это сколько в валюте?

Нам поможет такая формула:

Total * Percentage = Amount
Общая сумма * Проценты = Значение

Предположим, что Общая сумма (Total) записана в ячейке A2, а Проценты (Percent) – в ячейке B2. В этом случае наша формула будет выглядеть довольно просто =A2*B2 и даст результат $104.50 :

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

Другими словами, формула =A2*11% эквивалентна формуле =A2*0,11 . Т.е. в формулах Вы можете использовать либо десятичные значения, либо значения со знаком процента – как Вам удобнее.

Пример 2. Расчёт общей суммы по известному проценту и значению

Предположим, Ваш друг предложил купить его старый компьютер за $400 и сказал, что это на 30% дешевле его полной стоимости. Вы хотите узнать, сколько же стоил этот компьютер изначально?

Так как 30% – это уменьшение цены, то первым делом отнимем это значение от 100%, чтобы вычислить какую долю от первоначальной цены Вам нужно заплатить:

Теперь нам нужна формула, которая вычислит первоначальную цену, то есть найдёт то число, 70% от которого равны $400. Формула будет выглядеть так:

Amount/Percentage = Total
Значение/Процент = Общая сумма

Для решения нашей задачи мы получим следующую форму:

A2/B2 или =A2/0,7 или =A2/70%

Как увеличить/уменьшить значение на процент

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

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

Значение*(1+%)

Например, формула =A1*(1+20%) берёт значение, содержащееся в ячейке A1, и увеличивает его на 20%.

Чтобы уменьшить значение на процент, используйте такую формулу:

Значение*(1-%)

Например, формула =A1*(1-20%) берёт значение, содержащееся в ячейке A1, и уменьшает его на 20%.

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

Увеличить на процент: =A2*(1+B2)
Уменьшить на процент: =A2*(1-B2)

Как увеличить/уменьшить на процент все значения в столбце

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

Нам потребуется всего 5 шагов для решения этой задачи:

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

В результате значения в столбце B увеличатся на 20%.

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

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

На сегодня всё, благодарю за внимание!

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

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

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

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

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

Применение простых формул в Excel.

Рассмотрим формулы на простом примере суммы двух чисел для того, чтобы понять принцип их работы. Примером будет являться сумма двух чисел. Переменными будут выступать ячейки А1 и В1, в которые пользователь будет вводить числа. В ячейке С3 выведется сумма этих двух чисел в том случае, если на ней задана следующая формула:
=СУММ(А1;В1).

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

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

Как создать формулу в программе?

В прошлом примере рассмотрен пример суммы двух чисел, с чем справится каждый и без помощи Excel, но когда надо посчитать сумму более, чем 2 значения, то это займет большее время, поэтому можно выполнить сумму сразу трех ячеек, для этого просто нужно написать следующую формулу в ячейку D1:
=СУММ(А1:B1;C1).

Но бывают случаи, когда нужно сложить, к примеру, 10 значений, для этого можно использовать следующий вариант формулы:
=СУММ(А1:А10), что будет выглядеть следующим образом.

И точно так же с произведением, только вместо СУММ использовать ПРОИЗВЕД.

Также можно использовать формулы для нескольких диапазонов, для этого нужно прописать следующий вариант формулы, в нашем случае произведения:
=ПРОИЗВЕД(А1-А10, В1-В10, С1-С10)

Комбинации формул.

Кроме того, что можно задать большой диапазон чисел, можно также и комбинировать различные формулы. К примеру, нам нужно сложить числа определенного диапазона, и нужно посчитать их произведение с умножением на разные коэффициенты при разных вариантах. Допустим, нам нужно узнать коэффициент 1.4 от суммы диапазона (А1:С1) если их сумма меньше 90, но если их сумма больше или равна 90, то тогда нам нужно узнать коэффициент 1.5 от этой же суммы. Для этой, с виду сложной, задачи задается всего одна простая формула, которая объединяет в себе две базовые формулы, и выглядит она так:

ЕСЛИ(СУММ(А1:С1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

Как можно заметить, в этом примере были использованы две формулы, одна из которых ЕСЛИ, которая сравнивает указанные значения, а вторая СУММ, с которой мы уже знакомы. Формула ЕСЛИ имеет три аргумента: условие, верно, неверно.

Рассмотрим формулу поподробнее опираясь на наш пример. Формула ЕСЛИ получает три аргумента. Первым является условие, которое проверяет меньше сумма диапазона 90 или нет. Если условие верно, то выполняется второй аргумент, а если ложно, то будет выполнен третий аргумент. То есть, если мы введем значение в ячейки, сумма которых будет меньше 90, то выполнится умножение этой суммы на коэффициент 1,4, а если их сумма будет больше или равна 90, то тогда произойдет умножение на коэффициент 1,5.

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

Базовые функции Excel

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

Чтобы посмотреть набор формул, которым обладает программа, необходимо нажать кнопку "Вставить функцию", которая находится на вкладке "Формулы".

Или же можно нажать комбинацию клавиш Shift+F3. Эта кнопка (или комбинация клавиш на клавиатуре) позволяет ускорить процесс написания формул. Вам необязательно вводить все вручную, ведь при нажатии на эту кнопку в выбранной в данный момент ячейке будет добавлена та формула с аргументами, которые вы выберете в списке. Можно производить поиск по этому списку, используя начало формулы, или выбрать категорию, в которой нужная вам формула будет находиться.

К примеру, функция СУММЕСЛИМН находится в категории математических функций.

После выбора нужно функции нужно заполнить поля на ваше усмотрение.

Функция ВПР

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

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

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

Это происходит из-за того, что данная функция имеет еще и четвертый аргумент, который имеет только два значения, ИСТИНА или ЛОЖЬ, а так как он у нас не задан, то по умолчанию он стал в позицию ИСТИНА.

Округление чисел, используя стандартные функции

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

Чтобы округлить значение ячейки в большую сторону вам понадобится формула "ОКРУГЛВВЕРХ". Стоит обратить внимание на то, что формула принимает не один аргумент, что было бы логичным, а два, и второй аргумент должен быть равен нулю.

На рисунке хорошо видно, как произошло округление в большую сторону. Следовательно, информацию в ячейке А1 можно менять как и во всех других случаях, потому что она является переменной, и используется в качестве значения аргумента. Но если вам необходимо округлить число не в большую, а меньшую сторону, то эта формула не поможет вам. В данном случае вам необходима формула ОКРУГЛВНИЗ. Округление произойдет к ближайшему целому числу, которое меньше нынешнего дробного значения. То есть, если в примере на картинке задать вместо формулы ОКРУГЛВВЕРХ формулу ОКРУГЛВНИЗ, то результат будет уже не 77, а 76.

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

Приближенные вычисления с помощью дифференциала

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

Кроме того, на странице присутствуют формулы нахождения абсолютной и относительной погрешность вычислений. Материал очень полезный, поскольку погрешности приходится рассчитывать и в других задачах. Физики, где ваши аплодисменты? =)

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

Практикум состоит из двух частей:

– Приближенные вычисления с помощью дифференциала функции одной переменной.

– Приближенные вычисления с помощью полного дифференциала функции двух переменных.

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

Приближенные вычисления
с помощью дифференциала функции одной переменной

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

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

Пример 1

Решение: Пожалуйста, перепишите в тетрадь рабочую формулу для приближенного вычисления с помощью дифференциала :

Начинаем разбираться, здесь всё просто!

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

Смотрим на левую часть формулы , и в голову приходит мысль, что число 67 необходимо представить в виде . Как проще всего это сделать? Рекомендую следующий алгоритм: вычислим данное значение на калькуляторе:
– получилось 4 с хвостиком, это важный ориентир для решения.

В качестве подбираем «хорошее» значение, чтобы корень извлекался нацело . Естественно, это значение должно быть как можно ближе к 67. В данном случае: . Действительно: .

Примечание: Когда с подбором всё равно возникает затруднение, просто посмотрите на скалькулированное значение (в данном случае ), возьмите ближайшую целую часть (в данном случае 4) и возведите её нужную в степень (в данном случае ). В результате и будет выполнен нужный подбор: .

Если , то приращение аргумента: .

Итак, число 67 представлено в виде суммы

Сначала вычислим значение функции в точке . Собственно, это уже сделано ранее:

Дифференциал в точке находится по формуле:
– тоже можете переписать к себе в тетрадь.

Из формулы следует, что нужно взять первую производную:

И найти её значение в точке :

Таким образом:

Всё готово! Согласно формуле :

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

Ответ:

Пример 2

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

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

У некоторых, возможно, возник вопрос, зачем нужна эта задача, если можно всё спокойно и более точно подсчитать на калькуляторе? Согласен, задача глупая и наивная. Но попытаюсь немного её оправдать. Во-первых, задание иллюстрирует смысл дифференциала функции. Во-вторых, в древние времена, калькулятор был чем-то вроде личного вертолета в наше время. Сам видел, как из местного политехнического института году где-то в 1985-86 выбросили компьютер размером с комнату (со всего города сбежались радиолюбители с отвертками, и через пару часов от агрегата остался только корпус). Антиквариат водился и у нас на физмате, правда, размером поменьше – где-то с парту. Вот так вот и мучились наши предки с методами приближенных вычислений. Конная повозка – тоже транспорт.

Так или иначе, задача осталась в стандартном курсе высшей математики, и решать её придётся. Это основной ответ на ваш вопрос =)

Пример 3

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

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

Решение: Используем знакомую формулу:
В данном случае уже дана готовая функция: . Ещё раз обращаю внимание, что для обозначения функции вместо «игрека» удобнее использовать .

Значение необходимо представить в виде . Ну, тут легче, мы видим, что число 1,97 очень близко к «двойке», поэтому напрашивается . И, следовательно: .

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

Находим первую производную:

И её значение в точке :

Таким образом, дифференциал в точке:

В результате, по формуле :

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

Абсолютная и относительная погрешность вычислений

Абсолютная погрешность вычислений находится по формуле:

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

Относительная погрешность вычислений находится по формуле:
, или, то же самое:

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

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

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

Вычислим абсолютную погрешность:

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

Ответ: , абсолютная погрешность вычислений , относительная погрешность вычислений

Следующий пример для самостоятельного решения:

Пример 4

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

Примерный образец чистового оформления и ответ в конце урока.

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

Но для страждущих читателей я раскопал небольшой пример с арксинусом:

Пример 5

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

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

Пример 6

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

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

Алгоритм решения принципиально сохраняется, то есть необходимо, как и в предыдущих примерах, применить формулу

Записываем очевидную функцию

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

Анализируя таблицу, замечаем «хорошее» значение тангенса, которое близко располагается к 47 градусам:

Таким образом:

После предварительного анализа градусы необходимо перевести в радианы . Так, и только так!

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

Дальнейшее шаблонно:

Таким образом: (при вычислениях используем значение ). Результат, как и требовалось по условию, округлён до двух знаков после запятой.

Ответ:

Пример 7

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

Это пример для самостоятельного решения. Полное решение и ответ в конце урока.

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

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

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

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

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

Пример 8

Решение: Как бы ни было записано условие, в самом решении для обозначения функции, повторюсь, лучше использовать не букву «зет», а .

А вот и рабочая формула:

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

По условию требуется найти приближенное значение функции в точке .

Число 3,04 представим в виде . Колобок сам просится, чтобы его съели:
,

Число 3,95 представим в виде . Дошла очередь и до второй половины Колобка:
,

И не смотрите на всякие лисьи хитрости, Колобок есть – надо его съесть.

Вычислим значение функции в точке :

Дифференциал функции в точке найдём по формуле:

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

Вычислим частные производные первого порядка в точке :

Полный дифференциал в точке :

Таким образом, по формуле приближенное значение функции в точке :

Вычислим точное значение функции в точке :

Вот это значение является абсолютно точным.

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

Абсолютная погрешность:

Относительная погрешность:

Ответ: , абсолютная погрешность: , относительная погрешность:

Пример 9

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

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

Данная особенность справедлива и для случая функции одной переменной (первая часть урока).

Пример 10

Решение : Вычислим данное выражение приближенно с помощью полного дифференциала функции двух переменных:

Отличие от Примеров 8-9 состоит в том, что нам сначала необходимо составить функцию двух переменных: . Как составлена функция, думаю, всем интуитивно понятно.

Значение 4,9973 близко к «пятерке», поэтому: , .
Значение 0,9919 близко к «единице», следовательно, полагаем: , .

Вычислим значение функции в точке :

Дифференциал в точке найдем по формуле:

Для этого вычислим частные производные первого порядка в точке .

Производные здесь не самые простые, и следует быть аккуратным:

;

.

Полный дифференциал в точке :

Таким образом, приближенное значение данного выражения:

Вычислим более точное значение с помощью микрокалькулятора: 2,998899527

Найдем относительную погрешность вычислений:

Ответ: ,

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

Пример 11

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

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

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

Пример 12

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

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

Если честно, немного утомился, поскольку материал был нудноватый. Непедагогично это было говорить в начале статьи, но сейчас-то уже можно =) Действительно, задачи вычислительной математики обычно не очень сложны, не очень интересны, самое важное, пожалуй, не допустить ошибку в обычных расчётах.

Да не сотрутся клавиши вашего калькулятора!

Решения и ответы:

Пример 2: Решение: Используем формулу:
В данном случае: , ,

Таким образом:
Ответ:

Пример 4: Решение: Используем формулу:
В данном случае: , ,

Speaker Deck SlideShare

Основы использования формул в Excel. Правила ввода формул, разрешенные математические операции, порядок вычисления. Функция и формула, часто используемые функции группы Автосумма.

Навыки экзамена Microsoft Office Specialist (77-420):

Теоретическая часть:

1. Построение простых формул

Видеоверсия

Текстовая версия

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

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

Основы построения формул

Если в начале ячейки поставить знак «=», то Excel начинает воспринимать ячейку как формулу.

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

По умолчанию, в ячейке, содержащей формулу, для пользователя отображается результат ее вычисления, если нужно посмотреть сами формулы, сделать этом можно командой отображения формул «Показать формулы», группа «Зависимости формул», вкладка «Формулы», либо нажать горячее сочетание Ctrl+` (клавиша тильда стоит в начале цифрового ряда на клавиатуре).

Горячее сочетание

Ctrl+` переключает режимы отображения данные/ формулы в Excel

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

Константа – это значение, которое вводится непосредственно в формулу (число, текст, дата).

Переменная – это символьное обозначение значения, которое находится вне формулы.

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

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

Порядок вычисления формулы

Когда в формуле применяется несколько вычислительных операторов, то порядок их вычисления подчинен общим математическим правилам. Следующий порядок применяется при расчете формулы (записано в порядке убывания приоритета вычисления):

1. Отрицательные значения (-);
2. Проценты (%);
3. Возведение в степень (^);
4. Умножение (*) и деление (/);
5. Сумма (+) и разность (-).

Если рядом находятся два или более вычислительных операторов, которые находятся на одном уровне, то порядок их вычисления идет слева на право. Например, «=5+5+3-2», хотя здесь этот порядок имеет значение сугубо для логики произведения вычисления, т.к. отнимите вы число «2» в конце вычисления или в начале на результат не повлияет. Если возникает необходимость повысить приоритет вычисления отдельной части формулы, то, как и в математике, нужно эту часть заключить в круглые скобки.

Несколько примеров работы вычислений в Excel:

Горячее сочетание

Если во время ввода формулы вы передумали, то просто нажмите «Esc» и значение в ячейке не будет изменено. Если формулу уже изменили (завершили ввод с помощью Enter), то вернуть старое значение можно с помощью команды «Отменить» на панели быстрого доступа или сочетания Ctrl+Z.

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

1. Использование ссылок в формулах

Видеоверсия

Текстовая версия

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

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

• «E2» – столбец «E», вторая строка;
• «F2» – столбец «F», вторая строка;
• «G2» – столбец «F», вторая строка.

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

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

Простая формула пересчета актуального заработка в рубли вводится в ячейку C2, а потом, с помощью маркера автозаполнения растягивается на остальные дни, как можно заметить, для ячейки C3 в формуле используется заработок за 02.03.2016, хотя формула вводилась «=B2*71», т.е. при сдвиге на одну ячейку вниз, введенная относительная ссылка, также меняет свой адрес. То же самое происходит, если растянуть маркер автозаполнения в любую из сторон (влево, вправо, вверх или вниз), относительная ссылка всегда будет находится на одном расстоянии от ячейки с формулой, где она используется. В данном случае, это на одну ячейку влево, т.е., если потянуть маркер автозаполнения вправо, то в формуле будет использоваться столбец «C» (и число в зависимости от текущей строки).

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

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

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

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

Знак доллара можно ввести с клавиатуры, но быстрее будет нажать клавишу «F4» на относительной ссылке, т.е. на ссылке на которой установлен курсор. Повторное нажатие на клавишу «F4» будет убирать/добавлять знак $ возле строки/столбца. Будут появляться промежуточные значения, ссылки с одним знаком доллара – это смешанные ссылки, их рассмотрение будет чуть позже.

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

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

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

В таком случае следует использовать смешанные ссылки. Смешанная ссылка – это ссылка, которая по одному из направлений (строке или столбце) абсолютная, а по другому – относительная. Какая часть ссылки ведет себя как абсолютная ссылка определяется знаком доллара.

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

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

Какие еще бывают типы ссылок

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

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

1. Использование диапазонов данных в формулах

Видеоверсия

Текстовая версия

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

Именование диапазонов данных

Если вы часто работаете с определенным диапазоном, дать ему имя бывает достаточно удобно, чтобы в последствии оперировать не безымянным диапазоном/ячейкой: «C3:C15», а понятным «прибыль_2015» и т.п.

Для именования и навигации по именованным диапазонам и ячейкам используется группа «Определенные имена» вкладки «Формулы», а также окно «Имя» (Name box), которое мы начали рассматривать во втором вопросе второго занятия.

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

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

Еще один способ быстрого создания именованного диапазона – это с использованием команды «Создать из выделенного», в той же группе «Определенные имена». Суть заключается в том, что выделяется диапазон вместе с заголовком, который и будет носить имя диапазона остальной части диапазона. Excel, кстати, сам определил какую ячейку выделить под имя диапазона.

Выделение столбца «В евро» и использование команды «Создать из выделенного» равнозначно выделению диапазона E4:E13 и присвоение ему имени «В_евро».

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

Как использовать именованные диапазоны

Именованные диапазоны используются в формулах наравне со стандартными диапазонами, но есть некоторые особенности. Во-первых, именованный диапазон ВСЕГДА является абсолютной ссылкой, во-вторых, в формуле достаточно начать писать имя именованного диапазона и Excel его предложит, если есть несколько похожих («заработок_доллары», «заработок_гривны» и т.д.) то можно мышкой будет выбрать нужный.

Именованный диапазон в формулу можно вставить с помощью команды «Использовать в формуле» группы «Определенные имена».

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

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

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

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

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

1. Длина имени не более 255 символов.
2. Начинаться имя диапазона может либо с буквы, либо с символов «_» и «\». В самом имени можно использовать цифры, буквы, знак подчеркивания. Например, «\имя_ячейки», или «_имя\ячейки.1» – допустимые названия, а «1имя_ячейки» – нельзя т.к. начинается с цифры.
3. Имя не может состоять из одиночных букв: «R», «r», «C»,»c», поскольку их ввод в окошко «Имя» приводит к выделению строки (row) или столбца (column).
4. В именах нельзя использовать пробелы, Microsoft рекомендует их заменять символом подчеркивания «_» или точкой «.». Например, «доходы_октябрь», «прибыль.год».
5. Имя диапазона не может быть таким же как имя ссылки, например, «A5» или «$B$5».

1. Введение в функции. Отображение дат и времени с помощью функций

Видеоверсия

Текстовая версия

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

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

Удобный справочник по работе с функциями представлен на нашем сайте:

Введение в функций

На заметку

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

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

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

Для суммы есть горячее сочетание клавиш «Alt+=», можно просто ввести его возле диапазона с данными и Excel выделит наиболее подходящий (по мнению программы) диапазон, который можно откорректировать, а можно выделить диапазон и нажать горячее сочетание и Excel в зависимости от диапазона (горизонтальный или вертикальный) поместит суммы в ячейку под, или над диапазоном.

Функции даты и времени

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

Отправной точкой для дат является 1 января 1900 года, т.е. именно в эту дату будет преобразовано число «1», если выставить соответствующе форматирование, «2» = это 2 января 1900 года и т.д. Даты можно складывать, вычитать, умножать, делить ровно также, как это делается с числами.

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

Например, введя «15.03.2016» числовой формат будет установлен на «Дата», если потом просто удалить дату с помощью клавиши Del (равнозначно очистке содержимого) и ввести число, например, 5, то в ячейке отобразится дата: 05.01.1900. Поэтому нужно либо полностью очищать ячейку (вместе с форматами), либо после ввода данных изменить числовой формат ячейки.

Функция СЕГОДНЯ (TODAY)

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

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

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

Функция ТДАТА (NOW)

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

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

1. Работа с часто используемыми функциями (Автосумма)

Видеоверсия

Текстовая версия

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

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

В «Автосумму» входят функции: , и .

Группу функций «Автосумма» можно найти на вкладке «Главная» в группе «Редактирование».

Группа функций «Автосумма» представлена на вкладке «Формулы» в группе «Библиотека функций».

Функция СУММ (SUM)