ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 10.01.2024
Просмотров: 166
Скачиваний: 7
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
Практическая работа №20
«Использование стандартных функций. Адресация»
Цель: Изучение принципов создания и использования формул и функций в электронных таблицах. Приобретение умения создавать, сохранять, оформлять, редактировать и выполнять расчеты с использованием формул и встроенных функций в табличном процессоре Excel
План
-
Изучить основные возможности табличного процессора Excel -
Изучить формулы Excel и способы работы с ними -
Изучить встроенные функции Excel и способы работы с ними -
Выполнить создание, сохранение, оформление, редактирование и расчеты с использованием встроенных функций и формул в табличном процессоре Excel -
Ответить на контрольные вопросы
Краткие сведения
Работа с формулами. Вычисления в таблицах выполняются с помощью формул, состоящих из математических операторов, констант, ссылок на ячейку и имен функций. Результатом выполнения формулы есть некоторое новое значение, содержащееся в ячейке, где находится формула. Формула начинается со знака равенства =. В формуле могут использоваться арифметические операторы (Табл. 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) |
| | диапазонов | |
Константы – текстовые или числовые значения, которые вводятся в ячейку и не могут изменяться во время вычислений.
Ссылка на ячейку или группу ячеек – способ, которым указывается конкретная ячейка или несколько ячеек. Ссылка на отдельную ячейку – ее координаты. Значение пустой ячейки равно
нулю. Ссылки на ячейки бывают двух типов:
-
относительные – ячейки обозначаются относительным смещением от ячейки с формулой (например: F7). При копировании формул относительные ссылки изменяются на размер перемещения. -
абсолютные – ячейки обозначаются координатами ячеек в сочетании со знаком $ (например:
$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). Выполнить расчет значений доли общего вклада с использованием формул.
| Порядок работы
|  Рис.2.Исходныеданныедлязадания2 |
-
На вкладке лист2 выполнить задание 1: создать таблицу Вклады в банке (исходные данные представлены на рис. 2). -
Установите курсор на ячейку А1. Введите заголовок таблицы Вклады в банке. Затем выделите диапазон A1:D1 объедините ячейки командой Формат/Ячейки/вкладка Выравнивание/установить флажок Объединениеячеек -
Введите названия столбцов таблицы - «№ п/п», «Фамилия вкладчика», «Сумма вклада, руб.»,
«Доля от общего вклада, %». Изменение ширины столбцов производите перемещением мышью в строке имен столбцов (А, В, С и т.д.).
-
Для оформления шапки таблицы выделите строку, содержащую шапку таблицы (строка 2), задайте перенос по словам командой Формат/Ячейки/вкладка Выравнивание/Переносить по сло-вам,выберите горизонтальное и вертикальное выравнивание — «по центру». -
Заполните таблицу исходными данными согласно заданию 1 (рис. 2). -
Выполните расчет итогового показателя в ячейке С13. Для этого установить курсор в ячейку С13 и дважды нажать кнопку
Автосумма на панели инструментов. -
Для расчета доли от общего вклада необходимо в ячейку D3 ввести формулу =С3/$С$13. За- тем произведите автокопирование формулы: установите курсор в нужную ячейку (D3), подведите курсор к маркеру автозаполнения, расположенному в правом нижнем углу ячейки; когда курсор примет вид черного крестика, нажмите левую кнопку мыши и протяните формулу вниз по ячейкам до нужной ячейки (до ячейки D12). -
Выполните расчет итогового показателя в ячейке D13. Для этого установить курсор в ячейку D13 и дважды нажать кнопку Автосумма на панели инструментов. -
Затем отформатируйте значения из последнего столбца в процентном формате, для этого вы- делите диапазон ячеек D3:D13 и выполните команду Формат/Ячейки/вкладка Число/формат Про- центный, число десятичных знаков задайте равное 1 -
Произведите обрамление таблицы. Для этого выделите блок ячеек таблицы А2:D13. Откройте окно Обрамление таблиц командой Формат/ Ячейки/вкладка Границы. Для внутренних линий вы- берите тонкую, а для контура — более толстую непрерывную линию. Макет отображает конечный вид форматирования обрамления, поэтому кнопку ОК нажмите, когда вид обрамления на макете полностью вас удовлетворит. -
Выполните выравнивание данных таблицы как на рис. 2 -
Сохраните электронную таблицу Файл-Сохранить