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

• Название: Практическая работа 20
• Анкор: ррррррр
• Дата: 17.04.2023
• Размер: 0.54 Mb.
• Имя файла: Пр. 20.docx
• Тип: Документы; #1066260

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

«Использование стандартных функций. Адресация»
Цель: Изучение принципов создания и использования формул и функций в электронных таблицах. Приобретение умения создавать, сохранять, оформлять, редактировать и выполнять расчеты с использованием формул и встроенных функций в табличном процессоре Excel

План

1. 
   Изучить основные возможности табличного процессора Excel
   
2. 
   Изучить формулы Excel и способы работы с ними
   
3. 
   Изучить встроенные функции Excel и способы работы с ними
   
4. 
   Выполнить создание, сохранение, оформление, редактирование и расчеты с использованием встроенных функций и формул в табличном процессоре Excel
   
5. 
   Ответить на контрольные вопросы
   

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

Работа с формулами. Вычисления в таблицах выполняются с помощью формул, состоящих из математических операторов, констант, ссылок на ячейку и имен функций. Результатом выполнения формулы есть некоторое новое значение, содержащееся в ячейке, где находится формула. Формула начинается со знака равенства =. В формуле могут использоваться арифметические операторы (Табл. 1) Порядок вычислений определяется обычными математическими законами (вычисляются значения функций, выполняется возведение в степень, умножение, деление, сложение, вычитание). Примеры формул:

=(А4+В8)*С6

=F7/С14+B9^2

Таблица 1

Оператор	Значение	Пример
Арифметические операторы
+ (знак плюс) - (знак минус) / (косая черта) * (звездочка) % (знак процента) ^ (крышка)	Сложение Вычитание или унарный минус Деление Умножение Процент Возведение в степень	=А1+В2 =А1-В2 =-В2 =А1/В2 =А1*В2 =20% =5^3 (5 в 3-й степени)
Операторы сравнения
=	Равно	=ЕСЛИ (А1=В2; Да; Нет)
>	Больше	=ЕСЛИ (А1>В2; А1; В2)
<	Меньше	=ЕСЛИ (А1<В2; В2; А1)
>=	Больше или равно	=ЕСЛИ (А1>=В2; А1; В2)
<=	Меньше или равно	=ЕСЛИ (А1<=В2; В2; А1)
<>	Не равно	=ЕСЛИ (А1<>В2; неравны)
Текстовый оператор
& (амперсанд)	Объединение последовательностей символов в одну последовательность символов	=Значение ячейки В2 равняется: &В2
Адресные операторы
Диапазон	Ссылка на все ячейки между	=СУММ (А1:В2)
(двоеточие)	границами диапазона
Объединение	включительно
(точка с запятой)	Ссылка на объединение ячеек	=СУММ (А1:В2; С3; D4;E5)
	диапазонов

Константы – текстовые или числовые значения, которые вводятся в ячейку и не могут изменяться во время вычислений.

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

нулю. Ссылки на ячейки бывают двух типов:

1. 
   относительные – ячейки обозначаются относительным смещением от ячейки с формулой (например: F7). При копировании формул относительные ссылки изменяются на размер перемещения.
   
2. 
   абсолютные – ячейки обозначаются координатами ячеек в сочетании со знаком $ (например:
   

$F$7). При копировании формул абсолютные ссылки не изменяются.

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

Абсолютный вид адресации применяется для того, чтобы защитить в формулах адреса от изменения при копировании, если ссылка производиться на одну и ту же ячейку. При абсолютной адресации перед той частью адреса ячейки, которая не должна меняться при копировании ставится сим- вол <$> (Табл. 2).

Таблица 2

Вид адресации	Адрес ячейки (пример)	Действие при копировании
Относительный столбец, относительная строка	В6	Меняются имя столбца и но- мер строки
Абсолютный столбец, относительная строка	$B6	Не меняется имя столбца, ме- няется номер строки
Относительные столбцы, абсолютная строка	B$6	Меняется имя столбца, не ме- няется номер строки
Абсолютный столбец, абсолютная строка	$B$6	Не меняются имя столбца и номер строки

При копировании ячеек, содержащих формулы с относительными ссылками, координаты ячеек аргументов изменяются автоматически. Например, при копировании формулы =А1+А2 из ячейки А3 в ячейку В3 ее содержимое изменится на =В1+В2. Эта формула как и ранее вычисляет сумму двух ячеек слева от ячейки с формулой. При копировании ячеек, содержащих формулы с абсолютными ссылками, адреса ячеек-аргументов не изменяются.

Для ввода формулы в ячейку следует ввести знак = и формулу для вычисления. После нажатия клавиши Enter в ячейке появится результат вычисления. При выделении ячейки, содержащей формулу, формула появляется в строке редактирования.
Работа с функциями. Функция представляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов функции, стоящих в скобах после его имени. Функцию (также как и число) можно считать частным случаем формулы. Различают стати- стические, логические, финансовые и другие функции (рис. 1). Например, ячейка содержит функ- цию вычисления суммы множества чисел, находящихся в ячейках В4, В5, В6, В8, в виде: СУММ(B4:B6, B8). Вставить в ячейку функцию суммы СУММ можно с помощью кнопки .

Функции в Microsoft Excel представляют собой формулы, которые имеют один или несколько аргументов. В качестве аргументов указываются числовые значения или адреса ячеек. Например: =СУММ(А5:А9) - сумма ячеек А5, А6, А7, А8, А9; =СРЗНАЧ(G4:G6) – среднее значение ячеек G4, G5, G6. Функции могут входить одна в другую, например: =ОКРУГЛ(СРЗНАЧ(H4:H8);2) –
	Рис.1. Мастер функций

округлить до двух знаков после запятой среднее значение из ячеек H4, H5, H6, H7, H8

Для обращения к группе ячеек используются специальные символы:

: (двоеточие) – формирует обращение к блоку ячеек. Через двоеточие указывается левая верхняя и правая нижняя ячейки блока. Например: С4:D6 – обращение к ячейкам С4, С5, С6, D4, D5, D6.

; (точка с запятой) – обозначает объединение ячеек. Например, D2:D4;D6:D8 – обращение к ячейкам D2, D3, D4, D6, D7, D8.

Для введения функции в ячейку необходимо:

 выделить ячейку для формулы;

 вызывать Мастер функций с помощью команды Функция меню Вставка или кнопки ;

 в диалоговом окне Мастер функций (рис.13), выбрать тип функции в поле Категория, затем функцию в списке Функция;

 щелкнуть кнопку ОК;

 в полях Число1, Число2 и др. следующего окна ввести аргументы функции (числовые значения или ссылки на ячейки);

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

Заданиякпрактическойработе

Задание 1. Создать таблицу вкладчиков банка (рис. 2). Выполнить расчет значений доли общего вклада с использованием формул.

Порядок работы Запустите редактор элек- тронных таблиц Microsoft Ex- cel (Пуск – Программы – 0ffiсе_2000 – Miсrosoft Excel) Документ сохранить на Ра- бочий стол ФИ.xcl. На вкладке лист1 напеча- тать Ф. И.О., группа (в ячейке А1), № практической работы (в ячейке D2), тема практиче- ской работы (в ячейке А3), цель практической работы (в ячейке А5).

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

4. 
   На вкладке лист2 выполнить задание 1: создать таблицу Вклады в банке (исходные данные представлены на рис. 2).
   
5. 
   Установите курсор на ячейку А1. Введите заголовок таблицы Вклады в банке. Затем выделите диапазон A1:D1 объедините ячейки командой Формат/Ячейки/вкладка Выравнивание/установить флажок Объединениеячеек
   
6. 
   Введите названия столбцов таблицы - «№ п/п», «Фамилия вкладчика», «Сумма вклада, руб.»,
   

«Доля от общего вклада, %». Изменение ширины столбцов производите перемещением мышью в строке имен столбцов (А, В, С и т.д.).

4. 
   Для оформления шапки таблицы выделите строку, содержащую шапку таблицы (строка 2), задайте перенос по словам командой Формат/Ячейки/вкладка Выравнивание/Переносить по сло-вам,выберите горизонтальное и вертикальное выравнивание — «по центру».
   
5. 
   Заполните таблицу исходными данными согласно заданию 1 (рис. 2).
   
6. 
   Выполните расчет итогового показателя в ячейке С13. Для этого установить курсор в ячейку С13 и дважды нажать кнопку Автосумма на панели инструментов.
   
7. 
   Для расчета доли от общего вклада необходимо в ячейку D3 ввести формулу =С3/$С$13. За- тем произведите автокопирование формулы: установите курсор в нужную ячейку (D3), подведите курсор к маркеру автозаполнения, расположенному в правом нижнем углу ячейки; когда курсор примет вид черного крестика, нажмите левую кнопку мыши и протяните формулу вниз по ячейкам до нужной ячейки (до ячейки D12).
   
8. 
   Выполните расчет итогового показателя в ячейке D13. Для этого установить курсор в ячейку D13 и дважды нажать кнопку Автосумма на панели инструментов.
   
9. 
   Затем отформатируйте значения из последнего столбца в процентном формате, для этого вы- делите диапазон ячеек D3:D13 и выполните команду Формат/Ячейки/вкладка Число/формат Про- центный, число десятичных знаков задайте равное 1
   
10. 
    Произведите обрамление таблицы. Для этого выделите блок ячеек таблицы А2:D13. Откройте окно Обрамление таблиц командой Формат/ Ячейки/вкладка Границы. Для внутренних линий вы- берите тонкую, а для контура — более толстую непрерывную линию. Макет отображает конечный вид форматирования обрамления, поэтому кнопку ОК нажмите, когда вид обрамления на макете полностью вас удовлетворит.
    
11. 
    Выполните выравнивание данных таблицы как на рис. 2
    
12. 
    Сохраните электронную таблицу Файл-Сохранить
    

Задание2.Создать таблицу Погода (рис.3). Выполнить нахождения максимального, минималь- ного и среднего значений в столбцах Температура, Давление и Влажность.

Порядок работы

1. 
   Запустите редактор электронных таблиц Microsoft Excel.
   
2. 
   Документ сохраните на Рабочий стол ФИ.xcl.
   

Дополните таблицу данными как на рис. 3 Произведите расчеты Макси- мума, для этого установите кур- сор в ячейку С20 и выберите ко- манду Вставка/Функция/выбе- рите функцию МАКС/Ok (рис. 4). Затем нажать кнопку для уменьшения размеров окна функции МАКС, выделить диа- пазон С3:С19 и развернув окно кнопкой нажать Ok. Далее произведите автокопирование полученной функции в ячейке С20. Краткая справка. Для автокопи- рования функции выполните следующие действия: подведите курсор к маркеру автозаполне- ния, расположенному в правом нижнем углу ячейки; когда кур- сор примет вид черного кре- стика, нажмите левую кнопку мыши и протяните формулу в нужном направлении (вниз, вправо) по ячейкам.

Рис.3.Исходныеданныедлязадания2

9. Произведите расчеты Минимума, для этого установите курсор в ячейку С21 и выберите ко- манду Вставка/Функ- ция/ выберите функцию МИН/ Ok. Затем нажать кнопку для уменьшения размеров окна функции МИН, выделить диапазон С3:С19 и развернув окно кнопкой нажать Ok. Далее произведите авто- копирование полученной функции вправо.

Рис. 4. Использование встроенной функции МАКС

10. 
    Произведите расчеты Среднего значения, для этого установите курсор в ячейку С22 и выбе^{рите команду Вставка/Функция/ выберите функцию СРЗНАЧ/Ok. Затем нажать кнопку для} уменьшения размеров окна функции МИН, выделить диапазон С3:С19 и развернув окно кнопкой ^{нажать Ok. Далее произведите автокопирование полученной функции вправо.}
    
11. 
    Сохраните электронную таблицу Файл-Сохранить
    
12. 
    Вставить новый лист (лист 4) командой Вставка-Лист и выполните на нем дополнительное задание.
    

Дополнительные задания

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

квартал с использова- нием встро- енных функ- ций.

Рис. 5. Исходные данные для дополнительного задания Вариант 1

Вариант 2. Создать таб- лицу Резуль- таты опроса «Ваши увлечения» (рис. 6). Вы- полнить рас- чет значений в столбце Среднее значение и в строке Всего опрошено с использова- нием встро- енных функ- ций.

Рис. 6. Исходные данные для дополнительного задания Вариант 2