Практическая работ 8

Лабораторно-практическая работа №6

MS excel. Вычисления с помощью формул и функций

Цель работы:

формирование навыков работы с электронными таблицами в MS Excel, вычисления при помощи формул и функций.

Краткие методические рекомендации

Простейшие вычисления

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

Рисунок 1 – Вычисление выражения.

После ввода выражения нажать «Enter» – появится результат.

Рисунок 2 – Результат вычисления.

В Excel можно использовать различные операторы:

• умножение;

• / деление;

• - вычитание;

• + сложение;

• ^ возведение в степень и т.д.

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

Рисунок 3 – Ввод переменных.

Для ввода формулы в ячейке С4 поставим знак «=». Чтобы сделать ссылку на значение x, находящееся в ячейке B2, необходимо щелкнуть по данной ячейке левой кнопкой мыши. Аналогично поступаем со значением y. В результате формула принимает вид:

Рисунок 4 – Вычисление выражения.

Рисунок 5 – Результат вычислений.

Формат ячеек

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

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

• Если вы вводите целочисленное значение, дополненное символом процента, то Excel назначит ему процентный формат.

• Если вводится значение в одном из стандартных форматов дат Excel, например 06/11/19 или 6-ноя-19, то ему будет назначен формат даты. Данные в формате дат хранятся в виде числовых значений.

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

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

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

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

Чтобы изменить формат ячейки необходимо щелкнуть на ней правой кнопкой мыши и выбрать Формат ячеек.

Рисунок 6 – Формат ячеек.

Затем в открывшемся диалоговом окне задать необходимые параметры.

Рисунок 7 – Формат ячеек.

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

По данному протоколу определите время пробега дистанции для каждого спортсмена в минутах.

Для отображения количества минут между двумя моментами времени (аналогичный прием подходит для часов и секунд) установите формат [мм]. В контекстном меню ячейки выберите команду Формат ячеек – Число – (все форматы) и в поле Тип введите [мм].

Рисунок 8 – Формат ячеек: минуты.

Функции

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

Использование встроенных функций в Excel позволяет значительно упростить и автоматизировать расчеты. Чтобы добавить функцию, необходимо установить курсор в нужную ячейку и перейти во вкладку Формулы (MS Excel 2010). Затем нажать на кнопку Вставить функцию.

Рисунок 9 – Вкладка Формулы (MS Excel 2010).

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

Рисунок 10 – Окно мастера функций.

А затем нужную формулу. Например, LOG:

Рисунок 10 – Окно мастера функций.

В окне Аргументы функций задать необходимые параметры:

Рисунок 11 – Аргументы функции LOG.

1. Текстовая функция ЛЕВСИМВ().

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

Рисунок 12 – Таблица примера.

Для этого в ячейку D2 необходимо ввести формулу содержащую операцию объединения строк & и функцию ЛЕВСИМВ().

Рисунок 13 – Функция объединения строк и ЛЕВСИМВ().

1. Функции ЦЕЛОЕ (), ОСТАТ().

Чтобы вычислить сумму и произведение цифр двузначного числа, используйте функции ЦЕЛОЕ() для вычисления количества десятков в двузначном числе (1 цифра) и ОСТАТ() для вычисления единиц (2 цифра).

Рисунок 14 – Вычисление суммы и произведения чисел двузначного числа.

4. Функция СЛУЧМЕЖДУ().

Функция СЛУЧМЕЖДУ() позволяет заполнить выбранный диапазон случайными числами из определенного интервала значений. Для этого:

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

2. Вставьте в ячейку А1 функцию СЛУЧМЕЖДУ().

Рисунок 15 – Функция СЛУЧМЕЖДУ().

1. Укажите минимальное и максимальное значение интервала.

Рисунок 16 – Аргументы функции СЛУЧМЕЖДУ().

1. Скопируйте формулу в другие ячейки используя маркер заполнения.

Рисунок 17 – Маркер заполнения.

5. Функция ТЕКСТ().

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

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

Рисунок 18 – Функция ТЕКСТ().

6. Функции ДОЛЯГОДА() и СЕГОДНЯ().

Функция ДОЛЯГОДА возвращает долю года, которую составляет количество дней между двумя датами (начальной и конечной). Синтаксис: ДОЛЯГОДА(нач_дата;кон_дата;базис).

Функция СЕГОДНЯ производит вывод в указанную ячейку даты, установленной на компьютере. Она относится к группе операторов «Дата и время».

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

Для вычисления возраста человека, день рождения которого записан в ячейке А1, используется формула:

Рисунок 19 – Функции ДОЛЯГОДА и СЕГОДНЯ.

Задание

Задание 1

1. Создайте новый документ MS Excel. Сохраните его на рабочем столе под именем Группа_Фамилия_ЛР6.

2. Переименуйте лист 1 в «Задание 1».

3. Создайте следующую таблицу (Внимание! Не используйте инструмент вкладка Вставка – Таблица):

4. В ячейку C2 введите формулу вычисляющую значение функции f1. Сама функция f1 задается выражением:

5. В ячейку D2 введите формулу вычисляющую значение функции f2. Сама функция f2 задается выражением:

6. В ячейку E2 введите формулу вычисляющую значение функции f3. Сама функция f3 задается выражением:

7. Сравните полученные результаты с образцом:

8. Произведите вычисления для диапазона ячеек C3:E6.

9. Установите для диапазона ячеек C2:E6 числовой формат данных с округлением до одного знака после запятой. Сравните полученную таблицу с образцом:

10. Продлите таблицу и вычислите следующие 5 значений функций f1, f2, f3.

Задание 2

1. Перейдите на новый лист. Переименуйте его в «Задание 2».

2. Заполните данными таблицу и выполните вычисления в ней. Учтите, что в тригонометрических функциях в MS Excel аргумент задается в радианах.

Задание 3

1. Перейдите на новый лист. Переименуйте его в «Задание 3».

2. В ячейках A2 и B2 введите Фамилию, Имя, Отчество как представлено на рисунке. Напишите формулу для вывода в ячейке D2 фамилии и инициалов в виде Фамилия И. О.

3. Ниже реализуйте при помощи функции ЛЕВСИМВ() преобразование своих ФИО в форме «Фамилия И. Отчество».

Задание 4

1. Перейдите на новый лист. Переименуйте его в «Задание 4».

2. Далее вычислите сумму и произведение цифр двузначного числа (само двузначное число выберите произвольно).

Задание 5

1. Перейдите на новый лист. Переименуйте его в «Задание 5».

2. Используя функцию СЛУЧМЕЖДУ(), заполните диапазон из 4 строк и 5 столбцов случайными числами от –20 до 20. Правее полученного диапазона вычислите:

а) сумму всех чисел диапазона;

б) сумму чисел второй строки;

в) среднее значение третьего столбца;

г) минимальное значение первой строки;

д) максимальное значение пятого столбца;

е) количество чисел в диапазоне;

ж) сумму квадратов чисел первого столбца.

Пример выполнения:

Задание 6

1. Перейдите на новый лист. Переименуйте его в «Задание 6».

2. Определите, в какой день недели (понедельник, вторник, …) Вы родились.

3. Вычислите количество полных прожитых лет на текущий день.

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

1. Как переименовать лист документа в Excel?

2. Как осуществляются простейшие вычисления в Excel?

3. Что такое маркер заполнения? Как им пользоваться?

4. Для чего в Excel существуют функции? Какие категории функций вы знаете? Как добавить функцию в ячейку таблицы?

5. Приведите примеры математических функций. Продемонстрируйте преподавателю работу с одной из данных функций.

6. Приведите примеры текстовых функций. Продемонстрируйте преподавателю пример работы с одной из них.

7. Для чего в Excel функция СЛУЧМЕЖДУ()? Как с ней работать?

8. Поясните для чего в Excel функции ДОЛЯГОДА() и СЕГОДНЯ()? Нужен ли для функции СЕГОДНЯ() аргумент?