В качестве знаков арифметических операций Excel использует стандартные компьютерные символы операций, представленных в табл. 6.1. Операции выполняются над числами.
Таблица 6.1
Арифметические операторы
| Операция | Символ |
| Сложение | + (знак плюс) |
| Вычитание | - (знак минус) |
| Умножение | * (знак звездочка) |
| Деление | / (знак косая черта) |
| Возведение в степень | ^ (знак крышка) |
| Указание приоритета | Заключить в скобки () |
Порядок выполнения действий – это короткий набор правил, определяющий, как производятся вычисления в формуле:
- формулы вычисляются слева направо, например, 15/3+2 равно 7, а не 3;
- возведение в степень и выражения в скобках выполняются в первую очередь;
- затемвыполняютсяумножение и деление;
- сложение и вычитание выполняются в последнюю очередь.
Скобки не нужно ставить, если в формуле одно действие: они ставятся только в том случае, если нужно указать порядок двух или более действий.
Задание 2. Знакомство с порядком выполнения арифметических операций в формулах.
Ваши действия:
1. В ячейку А13 введите формулу, показанную на рис. 6.3.
2. С помощью маркера заполнения распространите полученную формулу в ячейки А14:А17.
3. Сделайте активной ячейку А14.
4. Изменяя приоритет выполнения арифметических операций с помощью круглых скобок, измените порядок выполнения действий. Редактирование производите в строке формул.
Рис. 6.3. Приоритет выполнения действий
Если вы не знаете точно, в каком порядке Excel будет выполнять операторы в формуле, используйте скобки – даже тогда, когда на самом деле в них нет необходимости. Кроме того, при последующих изменениях скобки облегчат чтение и анализ формул.
Задание 3. Изменение приоритета выполнения математических действий в формуле.
Ваши действия:
1. Введите в ячейку А19 формулу =1+2-3*4/5^6. Определите порядок выполнения операций при проведении вычислений.
2. Скопируйте формулу в ячейку А20.
3. Измените приоритет выполнения операций, расставив круглые скобки таким образом, чтобы сначала выполнить вычитание, затем сложение, деление, умножение и, наконец, возведение в степень.
Задание 4. Самостоятельно в ячейке А22 произведите вычисления по формуле:
| 7 + | 53 |
| 6*8 |
В математике формулы «двумерные», а в Excel формулы нужно располагать в одной строке. Вот почему приходится вводить дополнительные скобки, которых нет в исходных формулах.
Задание 5. Использование в формулах имен.
Ваши действия:
1. В ячейки А25 и А26 введите х и у.
2. В ячейки В25 и В26 введите числа 4 и 3.
3. Присвойте значениям имена из столбца А, соответственно х и у (рис. 6.4).
4. В ячейки С25, С26 и С27 введите следующие формулы:
| 1+х | ; |
Х-2 | -2х + | х3 | ||
| 4у | 5 + | 2х | 3у2+4 | |||
| у2+3 | ||||||
Рис. 6.4. Использование имен ячеек в формулах
Задание 6. Самостоятельно в ячейке А30 произведите вычисления по формуле.
Ваши действия:
1. Введите в ячейки А29:D29 числа: 5, 10, 15, 20.
2. В ячейку А30 введите формулу
| A21 | + | 5 + C21 | 2,4 |
| B21 - 5 | 25 - D21 |
3. Сохраните файл Упражнения.xlsx.
Вопросы для самоконтроля
1. Что такое формула?
2. Где отображается формула?
3. Где отображается результат вычислений?
4. Что может входить в состав формулы?
5. Можно ли использовать в формуле имена ячеек?
6. С какого символа должна начинаться формула?
7. Можно ли в формуле использовать пробелы?
8. Перечислите математические операторы, которые можно использовать в формуле?
9. Назовите правила построения формулы?
10. Перечислите порядок выполнения действий?
Тесты
1. С какого символа должна начинаться формула?
A. Не имеет значения.
B. ' (апостроф).
C. ~ (тильда).
D. = (равно).
2. Какой символ следует использовать в формулах в качестве знака умножения?
A. х
B. (
C. *
D. ^
3. Какой символ следует использовать в формулах в качестве знака деления?
A.:
B. \
C. /
D. |
4. Как подтвердить ввод формулы в ячейку?
A. Нажать кнопку 1.
B. Нажать кнопку 2.
C. Нажать кнопку 3.
D. Нажать кнопку 4.
5. Какую клавишу клавиатуры следует нажать для подтверждения ввода формулы в ячейку?
A. F4.
B. F2.
C. ENTER.
D. ESC.
6. Можно ли при вводе формул использовать скобки?
A. Да, всегда.
B. Да, но только при использовании абсолютных ссылок.
C. Да, но только при использовании ссылок на другие листы.
D. Нет, никогда.
7. Для ввода формулы в ячейку С5 необходимо …
A. Сделать ячейку С5 активной, ввести знак = и затем вводить формулу.
B. Сделать ячейку С5 активной, и затем ввести формулу.
C. Сделать ячейку С5 активной, выполнить команду Вставка, Функция, и затем использовать Мастер функций для ввода формулы.
8. Вычисления производятся в формуле …
A. Слева направо.
B. Справа налево.
C. Сверху вниз.
D. Снизу вверх.
9. В первую очередь в формуле выполняются …
A. Умножение и деление.
B. Сложение и вычитание.
C. Возведение в степень и выражения в скобках.
10. В последнюю очередь в формуле выполняются …
A. Умножение и деление.
B. Сложение и вычитание.
C. Возведение в степень и выражения в скобках.
11. В формуле могут присутствовать …
A. Константы.
B. Ссылки на ячейки.
C. Математические операторы.
D. Функции.
E. Круглые скобки.
12. Можно ли в формуле использовать пробелы?
A. Да, можно.
B. Нет, нельзя.
13. Результат вычислений отображается …
A. В строке формул.
B. В ячейке.
C. В поле Имя.
D. В строке состояния.
ГЛАВА 7. ФУНКЦИИ
Функции – это стандартные формулы, которые обеспечивают выполнение определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы, особенно если они длинные или сложные. Функции используют не только для непосредственных вычислений, но также и для преобразования чисел, например для округления, для поиска значений, сравнения и т. д.
Excel имеет несколько сотен встроенных функций, которые выполняют широкий спектр различных вычислений. Некоторые функции, такие как СУММ() или SIN(), являются эквивалентами длинных математических формул, которые вы можете создавать сами. Другие функции, такие как ЕСЛИ() или ВПР(), в виде формул реализовать невозможно, поэтому они относятся в разряду подпрограмм.
Другими словами функции – это заранее разработанные небольшие вспомогательные программы, выполняющие конкретные задачи. Часто этими задачами являются вычисления, однако иногда они бывают более обобщенными (например, некоторые функции просто возвращают дату и/или время). Фактически функции заменяют одну или несколько формул.
Каждая функция состоит из трех элементов:
- знак равенства (=) собственно указывает на функцию (формулу);
- имя функции (например, СУММ) указывает, какую операцию необходимо провести;
- аргумент функции (например, А1:Н1) указывает адреса ячеек, значения которых используются при вычислениях. Аргумент часто представляет собой группу ячеек, но может быть и более сложным.
Аргументом функции может быть число, текст, логическое значение, массив, значение ошибки, ссылка на ячейку. В качестве аргументов используются также константы, формулы, или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента.
Вы можете вводить функции в ячейки самостоятельно или с помощью Мастера функций (предпочтительно).
Существует четыре способа для вызова Мастера функций.
Для создания формул с функциями обычно используют группу Библиотека функций вкладки Формулы (рис. 7.1).
Рис. 7.1. Группа команд Библиотека функций вкладки Формулы
Математические функции
Математические функции используют при выполнении арифметических и тригонометрических вычислений, округлении чисел и в некоторых других случаях.
Рассмотрим некоторые математические функции: СУММ(), ПРОИЗВЕД(), СУММЕСЛИ(), ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ(), СТЕПЕНЬ().
Суммирование
Функция СУММ() – складывает все числа в диапазоне ячеек.
Синтаксис функции: СУММ(А),
где A – список от 1 до 255 аргументов, которые требуется суммировать. Аргумент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.
Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Однако есть и некоторые отличия. При использовании функции СУММ()добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.
Задание 1. Создание с использованием функцииСУММ().
Ваши действия:
1. Откройте файл Упражнения.xlsx из своей личной папки.
2. Создайте новый лист с именем Упр. 7.
3. Установите курсор в ячейку А2.
4. Нажмите на кнопку Вставить функцию, расположенную в строке формул. Появится диалоговое окно Вставка функции (рис. 7.1). На первом шаге работы Мастера функций необходимо выбрать имя функции, например, СУММ() из категории Математические.
Рис. 7.1. Диалоговое окно Вставка функции
5. Нажмите кнопку ОК или сделайте двойной щелчок по названию выбранной функции.
6. В появившемся окне Аргументы функции (рис. 7.2) введите аргументы функции. В поле Число 1 введите первый аргумент – 3, а в поле Число 2 второй аргумент – 2.
7. Нажмите кнопку ОК.
Рис. 7.2. Диалоговое окно Аргументы функции
В результате в ячейке А2 появится значение, а в строке формул будет видна формула, по которой было произведено вычисление (рис. 7.3).
Рис. 7.3. Примеры функции СУММ()
8. Введите исходные данные в ячейки А3:Е3 (см. рис. 7.3).
9. Установите курсор в ячейку А4 и нажмите на клавишу (=) на клавиатуре.
10. В поле Имя появится список последних 10 использовавшихся функций (рис. 7.4). Так как последней была выполнена функция СУММ(), она в этом списке находится первой.
Рис. 7.4. Выпадающее меню списка функций
11. Выберите функцию СУММ(), вы сразу попадете на второй шаг работы Мастера функций.
12. В диалоговом окне Аргументы функции в поле Число 1 введите аргумент A3:C3.
=СУММ(А3:С3)
13. В результате вычисления в ячейке появится значение 50.
14. В ячейку А5 введите следующую формулу:
=СУММ(B3:E3;15)
Здесь в качестве второго аргумента в поле Число 2 введите константу – 15 (см. рис. 7.3).
Умножение
Для умножения используют функцию ПРОИЗВЕД().
Синтаксис функции: ПРОИЗВЕД(А),
где A – список от 1 до 255 аргументов, которые требуется перемножить. Аргумент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.
Фактически данная функция заменяет непосредственное умножение с использованием оператора умножения (*). Так же как и при использовании функции СУММ(), при использовании функции ПРОИЗВЕД() добавление ячеек в диапазон перемножения автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон перемножения. Аналогично формула будет изменяться и при уменьшении диапазона.
Задание 2. Создание формулы с использованием функции ПРОИЗВЕД().
Ваши действия:
1. Введите в ячейку А7 формулу:
=ПРОИЗВЕД(А3:С3)
2. Введите в ячейку А8 формулу:
=ПРОИЗВЕД(B3:D3;2)
3. Введите в ячейку А9 формулу:
=СУММ(ПРОИЗВЕД(A3:B3);ПРОИЗВЕД(C3:D3))
Результаты вычислений можно увидеть на рис. 7.5.
Рис. 7.5. Примеры функции ПРОИЗВЕД()
Выборочная сумма
Иногда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям). В этом случае используют функцию СУММЕСЛИ().
Синтаксис функции: СУММЕСЛИ(А;В;С),
где А – диапазон вычисляемых ячеек, В – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки, С – фактические ячейки для суммирования.
В тех случаях, когда диапазон вычисляемых ячеек и диапазон фактических ячеек для суммирования совпадают, аргумент С можно не указывать.
Задание 3. Создание формулы с использованием функции СУММЕСЛИ().
Ваши действия:
1. Введите исходные данные в ячейки А11:С14, как показано на рис. 7.6.
Рис. 7.6. Примеры функции СУММЕСЛИ()
2. Введите в ячейки А15 и А16 формулы:
=СУММЕСЛИ(В12:В14;>500;С12:С14)
=СУММЕСЛИ(В12:В14;=500; С12:С14)
Округление
Округление чисел особенно часто требуется при денежных расчетах. Например, цену товара в рублях, как правило, нельзя устанавливать с точностью более двух знаков после запятой. Если же в результате вычислений получается большее число десятичных разрядов, требуется округление. В противном случае накапливание тысячных и десятитысячных долей рубля приведет в итоге к ошибкам в вычислениях.
Для округления чисел можно использовать целую группу функций.
Наиболее часто используют функции ОКРУГЛ(), ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ().
Синтаксис функции: ОКРУГЛ(А;В),
где A – округляемое число;
В – число знаков после запятой, до которого округляется число.
Задание 4. Создание формулы с использованием функции ОКРУГЛ(), ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ().
Функция ОКРУГЛ() отбрасывает цифры меньшие 5, а цифры большие 5 округляет до следующего разряда.
Синтаксис функций ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ() точно такой же, что и у функции ОКРУГЛ().
Ваши действия:
1. Введите в ячейки А18:А21 исходные данные, показанные на рис. 7.7.
Рис. 7.7. Примеры функции ОКРУГЛ(), ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ()
2. В столбце В произведите округление с точностью до 2-х знаков после запятой с помощью функции ОКРУГЛ().
3. В столбце С произведите округление с точностью до 1 знака после запятой с помощью функции ОКРУГЛ().
4. В столбце С произведите округление с точностью до 2-х знаков после запятой с помощью функции ОКРУГЛВВЕРХ() (см. рис. 7.7).
5. В столбце D произведите округление с точностью до 2-х знаков после запятой с помощью функции ОКРУГЛВНИЗ() (см. рис. 7.7).
Функция ОКРУГЛВВЕРХ() при округлении любые цифры округляет до следующего разряда. Функция ОКРУГЛВНИЗ() при округлении отбрасывает любые цифры.
Возведение в степень
Для возведения в степень используют функцию СТЕПЕНЬ().
Синтаксис функции: СТЕПЕНЬ(А;В),
где A – число, возводимое в степень;
В – показатель степени, в которую возводится число.
Задание 5. Создание формулы с использованием функции СТЕПЕНЬ().
Ваши действия:
1. Введите в ячейки А24:А26 исходные данные для вычислений, как показано на рис. 7.8.
Рис. 7.8. Примеры функции СТЕПЕНЬ()
2. В столбце В произведите возведение в 4-ю степень.
3. В столбце С произведите возведение в степень ½.
4. В столбце D произведите возведение в отрицательную степень -2.
Отрицательные числа можно возводить только в степень, значение которой является целым числом. В остальном ограничений на возведение в степень нет.
Статистические функции
Статистические функции используют при анализе данных. Использование большинства функций этой категории требует знания математической статистики и теории вероятностей.
Рассмотрим некоторые из них: СРЗНАЧ(), МИН(), МАХ(), НАИБОЛЬШИЙ(), НАИМЕНЬШИЙ(), СЧЕТ(), СЧЕТЗ(), СЧИТАТЬПУСТОТЫ(), СЧЕТЕСЛИ().
