Нахождение крайних значений

Модуль 2.7. Знакомство с функциями

Функции – это специальные, заранее созданные формулы, которые позволяют легко и быстро выполнять сложные вычисления. Они подобны специальным клавишам на некоторых калькуляторах, предназначенным для вычисления квадратных корней, логарифмов и статистических характеристик. Excel имеет несколько сотен встроенных функций, которые выполняют широкий спектр различных вычислений. Некоторые функции, такие как СУММ() или SIN(), являются эквивалентами длинных математических формул, которые вы можете создавать сами. Другие функции, такие как ЕСЛИ() или ВПР(), в виде формул реализовать невозможно, поэтому они относятся в разряду подпрограмм.

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

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

Каждая функция состоит из трех элементов:

- знак равенства (=) собственно указывает на функцию (формулу);

- имя функции (например, СУММ) указывает, какую операцию необходимо провести;

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

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

Вы можете вводить функции в ячейки самостоятельно или с помощью Мастера функций (предпочтительно).

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

Для создания формул с функциями обычно используют группу Библиотека функций вкладки Формулы (рис. 7.1).

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

Математические функции

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

Рассмотрим некоторые математические функции: СУММ(), ПРОИЗВЕД(), СУММЕСЛИ(), ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ(), СТЕПЕНЬ().

Суммирование

Функция СУММ() – складывает все числа в диапазоне ячеек.

Синтаксис функции: СУММ(А),

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

Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Однако есть и некоторые отличия. При использовании функции СУММ()добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.

Задание 1. Создание формулы с использованием Мастера функций.

1. Откройте файл Упражнения.xlsx из своей личной папки.

2. Создайте новый лист с именем Упр. 8.

3. Установите курсор в ячейку А2.

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

Рис. 7.1. Диалоговое окно Мастера функций – шаг 1

5. Нажмите кнопку ОК или сделайте двойной щелчок по названию выбранной функции.

6. В появившемся окне Аргументы функции (рис. 7.2) введите аргументы функции. В поле Число 1 введите первый аргумент – 3, а в поле Число 2 второй аргумент – 2.

7. Нажмите кнопку ОК.

Рис. 7.2. Диалоговое окно Мастера функций – шаг 2

В результате работы Мастера функций в ячейке А2 появится значение, а в строке формул будет видна формула, по которой было произведено вычисление (рис. 7.3).

Рис. 7.3. Примеры функции СУММ()

8. Введите исходные данные в ячейки А3:Е3 (см. рис. 7.3).

9. Установите курсор в ячейку А4 и нажмите на клавишу (=) на клавиатуре.

10. В поле Имя появится список последних 10 использовавшихся функций (рис. 7.4). Так как последней была выполнена функция СУММ(), она в этом списке находится первой.

Рис. 7.4. Выпадающее меню списка функций

11. Выберите функцию СУММ(), вы сразу попадете на второй шаг работы Мастера фукнций.

12. В диалоговом окне Аргументы функции в поле Число 1 введите аргумент A3:C3. Формула примет следующий вид:

13. =СУММ(А3:С3)

14. В результате вычисления в ячейке появится значение 50.

15. В ячейку А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. Создание формулы с использованием функции ОКРУГЛ(), ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ().

1. Введите в ячейки А18:А21 исходные данные, показанные на рис. 7.7.

Рис. 7.7. Примеры функции ОКРУГЛ(), ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ()

2. В столбце В произведите округление с точностью до 2-х знаков после запятой с помощью функции ОКРУГЛ().

3. В столбце С произведите округление с точностью до 1 знака после запятой с помощью функции ОКРУГЛ().

Функция ОКРУГЛ() при округлении отбрасывает цифры меньшие 5, а цифры большие 5 округляет до следующего разряда.

Синтаксис функций ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ() точно такой же, чт и у функции ОКРУГЛ().

4. В столбце С произведите округление с точностью до 2-х знаков после запятой с помощью функции ОКРУГЛВВЕРХ() (см. рис. 7.7).

5. В столбце D произведите округление с точностью до 2-х знаков после запятой с помощью функции ОКРУГЛВНИЗ() (см. рис. 7.7).

Функция ОКРУГЛВВЕРХ() при округлении любые цифры округляет до следующего разряда. Функция ОКРУГЛВНИЗ() при округлении отбрасывает любые цифры.

Возведение в степень

Для возведения в степень используют функцию СТЕПЕНЬ().

Синтаксис функции: СТЕПЕНЬ(А;В),

где A – число, возводимое в степень;

В – показатель степени, в которую возводится число.

Задание 5. Создание формулы с использованием функции СТЕПЕНЬ().

1. Введите в ячейки А24:А26 исходные данные для вычислений, как показано на рис. 7.8.

Рис. 7.8. Примеры функции СТЕПЕНЬ()

2. В столбце В произведите возведение в 4-ю степень.

3. В столбце С произведите возведение в степень ½.

4. В столбце D произведите возведение в отрицательную степень -2.

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

Статистические функции

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

Рассмотрим некоторые из них: СРЗНАЧ(), МИН(), МАХ(), НАИБОЛЬШИЙ(), НАИМЕНЬШИЙ(), СЧЕТ(), СЧЕТЗ(), СЧИТАТЬПУСТОТЫ(), СЧЕТЕСЛИ().

Нахождение крайних значений

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

Синтаксис функции: МАКС(А),

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

Задание 6. Создание формулы с использованием функции МАКС().

1. Введите в ячейки А28:Е28 числа 10, 7, 9, 27, 2, как показано на рис. 7.9.

2. В ячейки А29 и А30 ведите следующие формулы:

=МИН(А28:Е28)

=МИН(А28:Е28; 0)

Результат вычислений показан на рис. 7.9.

Рис. 7.9. Примеры функции МИН()

Функция МИН() – находит наименьшее значение в диапазоне ячеек. Функция МИН() имеет такой же синтаксис, что и функция МАКС(). Функции МАКС() и МИН() только определяют крайние значения, но не показывают, в какой ячейке эти значения находятся.

3. В ячейки А32 и А33 введите следующие формулы:

=МАКС(А28:Е28)

=МАКС(А28:Е28;30)

Результат вычислений показан на рис. 7.10.

Рис. 7.10. Примеры функции МАКС()

В тех случаях, когда требуется найти не самое большое (самое маленькое) значение, а значение, занимающее определенное положение в множестве данных (например, второе или третье по величине), следует использовать функции НАИБОЛЬШИЙ() или НАИМЕНЬШИЙ().

Синтаксис функции: НАИБОЛЬШИЙ(А;В),

где A – список от 1 до 30 элементов, среди которых требуется найти значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются;

В – позиция (начиная с наибольшей) в множестве данных. Если требуется найти второе значение по величине, то указывается позиция 2, если третье, то позиция 3 и т. д.

4. В ячейки А36, В36 и С36 введите следующие формулы:

=НАИБОЛЬШИЙ(А28:Е28;2)

=НАИБОЛЬШИЙ(А28:Е28;3)

=НАИБОЛЬШИЙ(А28:Е28;4)

Результаты вычислений показаны на рис. 7.11

Рис. 7.11. Примеры функции НАИБОЛЬШИЙ()

Функция НАИМЕНЬШИЙ() имеет такой же синтаксис, что и функция НАИБОЛЬШИЙ().

5. В ячейки А39, В39 и С39 введите следующие формулы:

=НАИМЕНЬШИЙ(А28:Е28;2)

= НАИМЕНЬШИЙ(А28:Е28;3)

= НАИМЕНЬШИЙ(А28:Е28;4)

Результаты вычислений показаны на рис. 7.12.

Рис. 7.12. Примеры функции НАИМЕНЬШИЙ()

Расчет средних значений

В самом простом случае для расчета среднего арифметического значения используют функцию СРЗНАЧ().

Синтаксис функции: СРЗНАЧ(А),

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

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

Задание 7. Создание формулы с использованием функции СРЗНАЧ().

1. В ячейки А41:С41 введите числа – 4,18 и 7.

2. В ячейки А42 и А43 введите следующие формулы:

=СРЗНАЧ(А28:Е28)

=СРЗНАЧ(А28:Е28; 5)

Результаты вычислений показаны на рис. 7.13.

Рис. 7.13. Примеры функции СРЗНАЧ()

3. Присвойте диапазону ячеек А28:Е28 имя Баллы

4. В ячейку А5 введите следующую формулу:

=СРЗНАЧ(Баллы)

5. Присвойте диапазону ячеек А41:С41 имя ДругиеБаллы

=СРЗНАЧ(Баллы;ДругиеБаллы)

Результаты вычислений показаны на рис. 7.13.

Расчет количества ячеек

Функция СЧЕТ()служитдля определения количества ячеек, содержащих числовые значения.

Синтаксис функции: СЧЕТ(А),

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

Задание 8. Создание формулы с использованием функции СЧЕТ().

1. Введите в ячейку В23 слово «Продажи», в ячейку В24 «10.04.2010», В25 – пустая, В26 введите целое число – 19, а В27 дробное число – 22,24 (рис. 7.14).

Рис. 7.14. Примеры функции СЧЕТ()

2. В ячейки В47 и В48 введите следующие формулы:

=СЧЕТ(А47:А51)

=СЧЕТ(А49:А51)

Если требуется определить количество ячеек, содержащих любые значения (числовые, текстовые, логические), то следует использовать функцию СЧЕТЗ().

Синтаксис функции: СЧЕТЗ(А),

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

Рис.7.15. Примеры функции СЧЕТЗ()

3. В ячейки А53 и А54 введите следующие формулы:

=СЧЕТЗ(А47:А51)

=СЧЕТЗ(А49:А51)

Результат вычислений показан на рис. 7.15.

Наоборот, если требуется определить количество пустых ячеек, следует использовать функцию СЧИТАТЬПУСТОТЫ().

Синтаксис функции: СЧИТАТЬПУСТОТЫ(А),

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

4. В ячейки А56 введите следующую формулу:

= СЧИТАТЬПУСТОТЫ(А47:А51)

Рис. 7.16. Пример функции СЧИТАТЬПУСТОТЫ()

Результат вычислений показан на рис. 7.16.

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

Синтаксис функции: СЧЕТЕСЛИ(А;В),

где А – диапазон проверяемых ячеек;

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

5. В ячейки А56 и В56 введите следующие формулы:

=СЧЕТЕСЛИ(В12:В14;500)

=СЧЕТЕСЛИ(С12:С14;900)

Рис. 7.17. Примеры функции СЧЕТЕСЛИ()

Задание 8. В книге Лабораторные работы.xls на листе Лаб_3 создайте следующую таблицу (рис. 7.18).

1. В таблице должны быть применены следующие формулы: СУММ(), МИН(), МАКС(), СРЗНАЧ().

2. Присвойте соответствующие имена следующим диапазонам ячеек: Минимальные продажы (3 ячейки), Средний объем продаж (1 ячейка), Максимальные расходы (3 ячейки), Итого (1 ячейка).

 

 

 

Рис. 7.18. Лабораторная работа № 3

 

Контрольные вопросы

1. Что такое функция?

2. Из каких элементов состоит функция?

3. Какие категории функций существуют?

Тест

1. Что означает формула =СУММ(В2:В4)?

A. вычисляется сумма ячеек В2 и В4

B. вычисляется сумма ячеек В2, В3 и В4

C. вычисляется частное от деления ячеек В2 и В4

D. вычисляется частное от деления ячеек В2, В3 и В4

2. Что означает формула =ПРОИЗВЕД(В2:В4)?

A. вычисляется произведение ячеек В2 и В4

B. вычисляется произведение ячеек В2, В3 и В4

C. вычисляется частное от деления ячеек В2 и В4

D. вычисляется частное от деления ячеек В2, В3 и В4

3. Какая группа функций содержит функции для расчета средних значений?

A. Статистические

B. Логические

C. Математические

D. ссылки и массивы

4. Какая группа функций содержит функции для расчета суммы или произведения?

A. Статистические

B. Логические

C. Математические

D. ссылки и массивы

5. Какую функцию следует использовать для расчета среднего арифметического?

A. МЕДИАНА

B. СРГЕОМ

C. СРЗНАЧ

D. УРЕЗСРЕДНЕЕ

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

A. МАКС

B. МИН

C. НАИМЕНЬШИЙ

D. НАИБОЛЬШИЙ

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

A. МАКС

B. МИН

C. НАИМЕНЬШИЙ

D. НАИБОЛЬШИЙ

8. Для каких целей следует использовать функцию СЧИТАТЬПУСТОТЫ?

A. для определения числа пустых ячеек

B. для определения числа пустых ячеек и ячеек, содержащих нулевые значения

C. для определения числа ячеек, не содержащих числовых значений

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

9. Что означает аргумент 2 в формуле =НАИБОЛЬШИЙ(B2:B14;2)?

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

B. в указанном диапазоне ищется второе по величине значение

C. в указанном диапазоне ищется значение, большее числа 2

D. в указанном диапазоне ищутся два одинаковых самых больших значения

10. Что означает аргумент 2 в формуле =НАИМЕНЬШИЙ(B2:B14;2)?

A. в указанном диапазоне ищется наименьшее значение, за исключением двух самых малых значений

B. в указанном диапазоне ищется второе по малости значение

C. в указанном диапазоне ищется значение, меньшее числа 2

D. в указанном диапазоне ищутся два одинаковых самых малых значения

11. В чем отличие функций СЧЕТ и СЧЕТЗ? Выберите верное утверждение.

A. отличий нет

B. функция СЧЕТ рассчитывает число ячеек, содержащих только числовые значения, а СЧЕТЗ – число ячеек, содержащих любые значения

C. функция СЧЕТ рассчитывает число ячеек, содержащих любые значения, а СЧЕТЗ – число ячеек, содержащих только числовые значения

D. функция СЧЕТ игнорирует пустые ячейки, а СЧЕТЗ – нет

12. Что означает аргумент >20 в формуле =СУММЕСЛИ(B2:B40;">20")?

A. доля суммируемых данных

B. суммируются только ячейки со значениями не превышающими 20

C. суммируются только ячейки со значениями превышающими 20

D. суммируются только ячейки, начиная со строки 20