Цель работы. Изучить технологии использования встроенных вычислительных функций Excel

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

Цель работы. Изучить технологии использования встроенных вычислительных функций Excel

Оборудование и материалы: персональный компьютер с операционной системой Windows, пакет программ Microsoft Office.

Краткие теоретические сведения

Формула — это выражение, состоящее из операндов, соединенных между собой знаками операций и круглых скобок. Операндом может быть число, текст, адрес ячейки (ссылка на ячейку), функция. Формула всегда начинается со знака «=».

Excel допускает арифметические операции:

"+" — сложение,

"-" — вычитание,

"*" — умножение,

"/" — деление,

"^" — возведение в степень;

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

Ссылка, которая включает имя колонки и номер строки, является относительной (например, А1, D1). При копировании формулы, а также редактировании листа такая ссылка будет модифицироваться.

В абсолютных ссылках перед именем колонки и номером строки стоит символ $ (например, $А$1, $D$1). Такие ссылки не модифицируются.

В смешанных ссылках абсолютной является название столбца и относительной — номер строки, или наоборот (например, $А1, D$1). В них модифицируется только относительная часть ссылки.

Название	Запись	При копировании	Технология ввода
Относительная	C3	Меняется в соответствии с новым положением ячейки	Щелкнуть в ячейке
Абсолютная	$C$3	Не меняется	Щелкнуть в ячейке и нажимать F4 до преобразования адреса к нужному виду
Смешанная	С$3	Не меняется номер строки
$C3	Не меняется имя столбца

 

Функция – это заранее определенная формула. MS Excel содержит 320 встроенных функций. Для удобства функции в Excel разбиты по категориям (математические, финансовые, статистические и т.д.). Обращение к каждой функции состоит из двух частей: имени функции и аргументов в круглых скобках. Библиотека функций находится на вкладке Формулы или открывается кнопкой на строке формул.

 

Порядок выполнения работы

1. В личной папке создайте документ электронной таблицы «Практическая работа №27».
2. Лист 1 переименуйте в «Задания 1 и 2 », лист 2 – «Задания 3 и 4 »
3. На листе «Задания 1 и 2 » в ячейке А1 вставьте текущую дату (Shift+Ctrl+4).
4. В ячейке А2 запишите тему работы.
5. В ячейке А3 запишите цель работы.
6. В ячейке А4 запишите оборудование.
7. Откройте лист в режиме Разметка страницы (вкладка Вид). В верхний колонтитул запишите номер практической работы. В нижний колонтитул запишите Ваши данные.
8. Откройте лист в Обычном режиме.
9. К диапазону ячеек А2:H4 примените команды Объединить по строкам, Перенос текста, высота строки 35.

 

Задание 1. Создать таблицу финансовой сводки за неделю.

 

Исходные данные представлены на рис. 1, результаты работы – на рис. 4.

 

Рис 1. Исходные данные для Задания 1.

 

1. К диапазонам А8:D8 и А20:С20 примените команду Объединить и поместить в центре.
2. К диапазону А10:D10 примените выравнивание по середине и Перенос текста.
3. Ширина столбца А 17, столбцов В и С 10, столбца D 15.

Краткая справка. Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (нажатие левой кнопкой мыши на маркер автозаполнения в правом нижнем углу ячейки и заполнить оставшиеся клетки).

 

1. Произведите расчеты в графе «Финансовый результат» по следующей формуле:

Финансовый результат = Доход – Расход.

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

1. Для ячеек с результатом расчетов задайте формат «Денежный» с выделением отрицательных чисел красным цветом (рис.2) (Формат Ячейки /вкладка – Число /формат – Денежный / отрицательные числа – красные. Число десятичных знаков задайте равное двум. Обозначение валюты – Нет).

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

Рис. 2. Задание формата отрицательных чисел красным цветом

1. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка f_x). Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения дохода установите курсор в соответствующей ячейке для расчета среднего значения (В18), запустите мастер функций и выберите функцию СРЗНАЧ (Формула /Вставить Функцию /категория – Статистические / СРЗНАЧ). В качестве первого числа (Число1) выделите группу ячеек с данными для расчета среднего значения – В11:В17 и нажмите ОК.

Аналогично рассчитайте среднее значение расхода.

1. В ячейке D20 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования (S) на Главной вкладке или функцией СУММ. В качестве первого числа выделите группу ячеек с данными для расчета суммы – D11:D17 и нажмите Enter или Ввод (кнопка ).

Конечный вид таблицы приведен на рис. 3.

Рис. 3. Таблица расчета финансового результата (Задание 1)

 

Задание 2. Заполнить таблицу «Анализ продаж» (рис. 4), произвести расчеты, вычислить минимальную и максимальную суммы покупки.

Рис. 4. Исходные данные для Задания 2

 

Формулы для расчета:

Сумма = Цена * Количество

Всего = Сумма значений колонки «Сумма».

Краткая справка. Для выделения максимального/минимального значения установите курсор в ячейке расчета, выберите встроенную функцию Excel МАКС (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма» (ячейки Е3:Е10).

 

Задание 3. Заполнить ведомость учета брака (рис. 5), произвести расчеты, выделить минимальную, максимальную и среднюю суммы брака, а также средний процент брака.

Рис. 5. Исходные данные для Задания 3

Формула для расчета:

Сумма брака = Процент брака * Сумма затрат.

Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Формат Ячейки /вкладка – Число /формат – Процентный).

 

Задание 4. Заполнить таблицу «Анализ продаж» (рис. 6), произвести расчеты, выделить минимальную и максимальную продажу (количество и сумму).

Формулы для расчета:

Всего = Безналичные платежи + Наличные платежи;

Выручка от продажи = Цена * Всего.

Рис.6. Исходные данные для Задания 4

 

FВыведите на печать лист Задания 1 и 2, предварительно убедившись в режиме Страничный (вкладка Вид), что данные расположены на одной странице.

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

 

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

1. Дайте понятие формулы в редакторе Excel.

2. Какие арифметические операции допускаются в формулах?

3. Какие вы знаете виды ссылок на ячейки? Чем они отличаются?

4. Что такое функция в электронных таблицах? Из чего состоит запись функции?

5. Как открыть список функций в редакторе MS Excel?

 

Литература

 

1. Цветкова М.С. Хлобыстова И.Ю. Информатика Учебник М., издательский центр «Академия», 2018

2. Цветкова М.С. Информатика Практикум для профессий и специальностей естественно-научного и гуманитарного профилей: учебное пособие для студентов учреждн\ений сред.проф. образования М., издательский центр «Академия», 2018