Самостоятельная работа 7. Применение математических и статистических функций.

Задание 1. Вычислите значения среднего, минимального, максимального и общего значения данных.

Ваши действия:

1. Откройте книгу Лабораторные работы.xls.

2. На листе Лаб.7 создайте таблицу, показанную на рис. 7.18. Исходные данные выделены полужирным начертанием.

Рис.7.18. Таблица расчета финансового результата

3. Введите в указанные ячейки следующие итоговые функции:

- СРЗНАЧ() в ячейках B10:D10;

- МАКС() в ячейках B11:D11;

- МИН() в ячейках B12:D12;

- СУММ() в ячейке D13.

4. Присвойте следующие имена:

- имя Общий_результат ячейке D13;

- имя Доход ячейкам В3:В9;

- имя Расход ячейкам С3:С9;

- имя Финансовый_результат ячейкам D3: D9.

Задание 2. Вычислите значения функции Y(X).

Ваши действия:

1. На листе Лаб.7 введите в ячейки А18-А38 исходные данные – значения Х, меняющегося в диапазоне от 0 до 6,280 с шагом 0,314 (рис.7.19).

2. В ячейку В18 введите формулу Y= SIN(X), используя Мастера функций.

3. Распространите полученную формулу на весь диапазон ячеек В19:В38, используя маркер заполнения.

4. Дополните таблицу тремя новыми формулами, зависящими от одного и того же аргумента Х, меняющегося в том же диапазоне значений. Формулы введите в ячейках C18, D18 и E18 соответственно, затем распространите полученные формулы на оставшийся диапазон ячеек.

Y=1,25*SIN(2X)

Y=1,5*SIN(4X)

Y=SIN(X)+1,25*SIN(2X)+1,5*SIN(4X)

 

Рис.7.19. Результаты вычислений

Вопросы для самоконтроля

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

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

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

4. Перечислите математические функции.

5. Перечислите статистические функции.

6. Перечислите логические функции.

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

8. Что делает функция НЕ()?

9. Что делает функция И()?

10. Что делает функция ИЛИ()?

Тесты

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.

13. В ячейке А2 задана формула =ЕСЛИ(А1<5%;"ДА";"НЕТ"). Какое значение будет отображено в ячейке А2, если в ячейке А1 установлено значение – 3,5%?

A. ДА.

B. ИСТИНА.

C. ЛОЖЬ.

D. НЕТ.

14. К какой категории относится функция ЕСЛИ()?

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

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

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

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

15. Функция И() называется функцией …

A. Логического умножения.

B. Логического сложения.

16. Функция ИЛИ() называется функцией …

A. Логического умножения.

B. Логического сложения.

17. Что делает функция НЕ()?

A. Меняет на противоположное логическое значение своего аргумента.

B. Функция возвращает значение ИСТИНА, если хотя бы одно из условий истинно.

C. Функция И() возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.

18. Что делает функция И()?

A. Меняет на противоположное логическое значение своего аргумента.

B. Функция возвращает значение ИСТИНА, если хотя бы одно из условий истинно.

C. Функция И() возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.

19. Что делает функция ИЛИ()?

A. Меняет на противоположное логическое значение своего аргумента.

B. Функция возвращает значение ИСТИНА, если хотя бы одно из условий истинно.

C. Функция И() возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.

ГЛАВА 8. ВИДЫ АДРЕСАЦИИ

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

Можно использовать смешанные ссылки на ячейки, т.е. сочетание в формулах относительных и абсолютных ссылок одновременно, например, A$1 или $A1. Часть ссылки, не содержащая знак «$», будет обновляться при копировании, а другая часть, со знаком «$», останется без изменения.

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

Относительные ссылки

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

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

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

Задание 1. Создание относительной ссылки.

Ваши действия:

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

2. Вставьте в рабочую книгу новый лист с именем Упр.8.

3. В столбец А введите несколько целых чисел.

4. В ячейке В1 введите формулу, вычисляющую возведение в степень 2 значения из ячейки А1.

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

Заполнение диапазона В2:В5 формулой возведения в степень можно осуществить тремя способами:

- выделить диапазон В1:В5, выберите команду Заполнить, Вниз из группы команд едактирование, которая находится на вкладке ГлавнаяРРРедактирование, расположенной на вкладке Главная или (что проще) использовать клавиатурную комбинацию Ctrl + D;

- поставить курсор на маркер заполнения ячейки В1 и, не отпуская левую кнопку мыши, протащить курсор до ячейки В5;

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

Рис. 8.1. Вид формулы с относительной ссылкой

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

6. В ячейки D1:E2 введите указанные числа (рис. 8.2).

Рис. 8.2. Копирование относительной ссылки

7. В ячейку D3 введите формулу, которая будет суммировать две вышележащие ячейки = D 1+ D 2. Эта формула показана в строке формул на рис. 8.2.

8. Распространите полученную формулу на ячейку E3 с помощью маркера заполнения.

9. Скопируйте формулу сложения двух чисел в ячейку D6 с помощью команд Копировать и Вставить.

10. Распространите формулу вдоль строки 6 и объясните, почему в ячейках получился 0.

Задание 2. Использование относительной ссылки.

Ваши действия:

1. Введите исходные данные, как показано на рис. 8.3.

2. В ячейку D11 введите формулу, указанную в строке формул.

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

Рис. 8.3. Пример использования относительной ссылки

Абсолютные ссылки

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

Задание 3. Создание абсолютной ссылки.

Ваши действия:

1. В ячейку С1 введите формулу, показанную на рис. 8.4.

2. Сначала введите знак равно (=), затем укажите мышью на ячейку А1.

3. Нажмите на функциональную клавишу F4, затем продолжите формулу возведения в степень 2.

4. Распространите на все ячейки столбца С формулу, используя маркер заполнения.

Рис. 8.4. Абсолютная ссылка в формуле – пример 1

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

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

5. Замените в ячейке D3 относительные ссылки на абсолютные ссылки, используя функциональную клавишу F4 (рис. 8.5).

Рис. 8.5. Пример использования абсолютной ссылки в формуле

6. Скопируйте формулу сложения двух чисел в ячейку D6.

7. Распространите измененную формулу вдоль строки 6.

8. Объясните, почему в ячейке получилось 3.

Задание 4. Использование абсолютной ссылки.

Налог вводится отдельно от вычисления исключительно для удобства: при изменении налога будет гораздо легче изменить один элемент.

Ваши действия:

1. Добавьте к таблице, показанной на рис. 8.3, еще один столбец для расчета величины налога в рублях (рис. 8.6).

2. В ячейку Е11 введите формулу, показанную в строке формул на рис. 8.6.

3. Ссылку на общую стоимость оставьте относительной.

4. Ссылку на налог сделайте абсолютной, используя функциональную клавишу F4.

5. Полученную формулу скопируйте в ячейки Е12 и Е13, используя маркер заполнения.

Рис. 8.6. Абсолютная ссылка в формуле – пример 3

6. Добавьте к таблице еще один столбец, чтобы рассчитать величину налога в долларах.

7. В ячейку D9 введите курс доллара.

8. В ячейку F11 введите формулу, показанную в строке формул на рис. 8.7.

9. Распространите полученную формулу в ячейки F12 и F13.

Рис. 8.7. Абсолютная ссылка в формуле – пример 4

10. К таблице, показанной на рис. 8.7, добавьте еще один столбец для расчета величины налога в евро.

11. В ячейку F9 введите значение курса евро.

12. Присвойте этой ячейке имя – курс_евро.

13. В ячейке G9 введите формулу, показанную на рис. 8.8.

14. Распространите полученную формулу в ячейки G12 и G13.

Рис. 8.8. Использование в формуле имени

Задание 5. Выполните следующие примеры.

Пример 1.  Перейдите на лист Упр.3 (см. рис.3.13) и произведите расчет итоговых сумм в долларах и евро, используя абсолютную ссылку на курс доллара, указанный в ячейке D 9 на листе Упр.8, и имя курс_евро, имеющийся в списке имен книги Упражнения.xlsx (рис. 8.9).

Рис. 8.9. Вид таблицы для примера 1

Пример 2. Перейдите на лист Упр.8 и рассчитайте зарплату с учетом премии работникам организации (10 человек), следуя следующим указаниям (рис. 8.10):

- Если в ячейке В15 установить значение 3, то зарплата будет увеличена только женщинам на 5000 руб.

- Если в ячейке В15 установить месяц второй, то премия в размере 7000 руб. назначается только мужчинам.

- Если ввести в В15 месяц первый, то всем работникам назначается премия в размере 10000 руб.

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

В ячейку D17 введите следующую формулу, затем распространите ее до ячейки D26 с помощью маркера заполнения:

=ЕСЛИ(И($B$15=2;B17="м");C17+7000;ЕСЛИ(И($B$15=3;B17="ж");C17+5000;ЕСЛИ($B$15=1;C17+10000;C17)))

Формула имеет трехкратное вложение функции ЕСЛИ(), учитывая 3 поставленных условия.

Рис. 8.10. Вид таблицы для примера 2

Смешанные ссылки

Ссылка может быть не только относительной или абсолютной, но и смешанной.

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

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

Задание 6. Использование смешанной адресации.

Ваши действия:

1. На листе Упр.8 в качестве исходных данных введите горизонтальную и вертикальную последовательности чисел от 1 до 10 (рис. 8.11).

Рис.8.11. Таблица умножения

2. В ячейку В29 введите формулу, которой потом нужно будет заполнить весь диапазон В29:К38:

=$A2*B$1

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

4. Установите курсор в ячейку В29 и нажмите на клавиши Ctrl + Shift + End. Будет выделена область В29:К38.

5. Последовательно нажмите клавиши Ctrl + D (копирование формулы вниз) и Ctrl + R (копирование формулы вправо). Произойдет автоматическое заполнение всей таблицы формулой, расположенной в ячейке В29.