Лабораторная работа № 3. Microsoft Office Excel. Работа с формулами. Использование абсолютной и относительной адресации. Математические функции Цель работы Освоение основных…

Лабораторная работа № 3. Microsoft Office Excel. Работа с формулами. Использование абсолютной и относительной адресации. Математические функции Цель работы Освоение основных приемов работы в электронной таблице. Занесе-ние данных, использование автозаполнения и формул с относительными и абсолютными ссылками. Учебные вопросы 1. Ячейки электронной таблицы и их адресация 2. Занесение данных в ячейки 3. Работа с формулами. Относительные и абсолютные ссылки 4. Использование математических функций и матричных операций Краткое изложение учебного материала по теме 1 Ячейки электронной таблицы и их адресация Основным элементом электронной таблицы является ячейка (или клетка) таблицы. В каждую ячейку может быть помещена информация различного типа: – текстовая длиной до 32767 символов; – числовая; – дата; – время. Полную техническую характеристику, возможности и ограничения Excel 2010 можно посмотреть на сайте Microsoft https://support.office.com/ru-ru/excel. На лист электронной таблицы можно также вставить объекты разных типов: диаграммы, векторные фигуры, рисунки, надписи в рамке, фигур-ный текст (WordArt) и пр. Ячейки таблицы образуют строки и столбцы. Каждая ячейка имеет адрес, образованный обозначением столбца и строки (например, A1, BB22, CCC333), который может использоваться при написании формул (рисунок 5.1). Стиль A1 адреса ячейки можно изменить на другой – на числовое обозначение столбцов и колонок (вкладка Файл ► Параметры, в появив-шемся окне Параметры Excel в разделе Формулы поставить галочку у пункта Стиль ссылок R1C1). Те же ссылки, что и ранее, будут выглядеть, 1 как R1C1, R22C54, R333C2109, где R – row (ряд), C – column (колонка). Таб-лица с типом адресации R1C1 показана на рисунке 5.2. Рисунок 5.1 – Книга Microsoft Excel с типом адресации A1 Рисунок 5.2 – Книга Microsoft Excel с типом адресации R1C1 Каждая ячейка электронной таблицы имеет множество свойств, ко-торые определены по умолчанию для выбранного стиля ячейки. Свойства можно просмотреть и изменить на вкладке Главная ленты, либо с исполь-зованием традиционного диалогового окна Формат ячеек, которое пока-зано на рисунке 5.3 (вызвать его можно из контекстного меню или малень-кой кнопочкой в правом нижнем углу любой группы кнопок вкладки Глав-ная). 2 Рисунок 5.3 – Окно Формат ячеек Ячейка электронной таблицы по умолчанию имеет стиль Обычный (лента Главная, группа Стили). Параметры стиля Обычный можно изме-нить, тогда все ячейки таблицы этого стиля будут иметь новые свойства. Можно создать новый стиль, если для ячейки задать новые свойства. В соответствии с требованиями по оформлению студенческих работ зададим для стиля ячеек Обычный шрифт Times New Roman, обычный, размер 13, черный, как показано на рисунке 5.4. 2 Занесение данных в ячейки При размещении информации на листе следует предварительно за-дать параметры страницы (вкладка Разметка страницы), чтобы на листе появились пунктирные линии границ страницы. Перед вводом информа-ции для ее однозначного толкования системой можно задать тип данных в ячейке в группе Число вкладки Главная. Чтобы занести информацию в выбранную ячейку, нужно щелкнуть на ней левой кнопкой мыши и приступить к вводу данных с клавиатуры. При этом старая информация в ячейке, если она присутствовала, уничто-жается. 3 Рисунок 5.4 – Редактирование стиля ячеек Обычный Редактирование существующей информации ячейки возможно в Строке формул (см. рисунок 5.2) или в ячейке после двойного щелчка на ней мышкой. Следует помнить, что в Excel существует внутреннее представление информации и внешнее. Внешнее представление задается форматом ячей-ки. Внутреннее – 15 значащих цифр для чисел, 32767 символов при вводе текста в ячейку (вставить из буфера обмена можно больше). Внутреннее представление даты – целое число, которое отсчитывается от 1 января 1900 г., формат времени – число от 0 до 0,99999999 (количество секунд в сут-ках равно 86400, одна секунда в Excel = 1/86400 = 0,0000115740). При вводе длинного текста в ячейку этот текст виден справа от ячей- 4 ки, пока не встретит не пустую ячейку (на ней текст оборвется). Если за-дать параметр Перенос текста (кнопка в группе Выравнивание вкладки Главная) – текст будет располагаться в несколько строк в одной ячейке или в группе объединенных ячеек (кнопка Объединить и поместить в цен-тре). Примеры размещения текста на листе электронной таблицы показа-ны на рисунке 5.5. Рисунок 5.5 – Примеры работы с текстом в электронной таблице При вводе чисел в ячейку следует учитывать следующее: - разделителем целой и дробной части числа может быть точка или запятая. Вид разделителя определяется настройками Windows. Поэтому следует всегда использовать цифры и точку (она является и запятой при соответствующей настройке) на числовой клавиатуре; - для ввода больших и маленьких чисел используется экспоненци-альная форма с использованием латинской буквы e. Число -1,23e-15 это -1,23⋅10-15; - в формате чисел можно задать количество видимых знаков после запятой, для отрицательных чисел можно при необходимости за- дать формат красные числа без знака; - для чисел можно задать разделитель групп разрядов (в соответ-ствии с настройками Windows). Тип данных дата/время наиболее сложен из -за наличия различных форм внешнего представления даты, времени или даты и времени одно-временно. Формат дат и времени зависит от настроек Windows (Язык и ре-гиональные стандарты). Полный список всех стандартных форматов да-ты/времени можно просмотреть в окне Формат ячеек (см. рисунок 5.3), 5 кроме того можно определить собственные форматы представления дан-ных. При вводе данных может быть использовано автозаполнение для чисел, дат и определенных в системе Excel списков (рисунок 5.6). Рисунок 5.6 – Списки для автозаполнения системы Excel Пример использования списков: пишем в ячейку A2 Пн, затем с нажатой левой кнопкой мыши за маркер копирова-ния/автозаполнения в правом нижнем углу ячейки A2 расширяем выбор вниз до ячейки A8. Получа-ем список сокращенных названий дней недели (рисунок 5.7). Анало-гично для других списков. При автозаполнении ячеек датами существует несколько ва-риантов: расширение выбора за маркер копирования/ автозаполне- Маркер копирова- ния/автозаполнения Рисунок 5.7 – Использование Списков ния левой кнопкой мыши – заполнение датами подряд по дням календаря; при растягивании за маркер копирования/автозаполнения правой кнопкой мыши – в контекстном меню появляются варианты: по дням, по рабочим дням (пятидневка), по месяцам, по годам (рисунок 5.8). 6 Рисунок 5.8 – Использование автозаполнения для дат Автозаполнение для чисел при расширении выбора за маркер копи-рования/автозаполнения правой кнопкой мыши – после выбора в кон-текстном меню пункта Прогрессия появляется диалоговое окно для зада-ния параметров арифметической или геометрической прогрессии (рисунок 5.9). Рисунок 5.9 – Использование автозаполнения для чисел Автозаполнение для текста, начинающегося или заканчивающегося числом – при копировании число в начале строки или в конце (при отсут-ствии числа в начале строки) автоматически увеличивается на 1 (рисунок 5.10). 7 Рисунок 5.10 – Использование автозаполнения для текста с числами 3 Работа с формулами. Относительные и абсолютные ссылки Формулы – это выражения, с помощью которых можно выполнять вычисления, манипулировать содержимым других ячеек, проверять усло-вия и пр. Формула всегда начинается со знака равенства (=). В Excel обычно задан режим показа результатов вычислений по формулам, но можно задать и режим показа формул (вкладка Формулы – Показать формулы в группе Зависимости формул). Формулы в системе Excel могут состоять из постоянных значений (числа, текст, даты/время), символов математических операций (+, -, *, /, ^), круглых скобок для изменения порядка действий (система знает прио-ритет вычислений в математических формулах, принятый в математике), функций разного типа, ссылки на ячейки и имена ячеек. Часто при вычислениях одинаковую операцию нужно выполнить с рядом данных. Простейший пример показан на рисунке 5.11. Рисунок 5.11 – Расчет по формулам в Excel, слева – обычный режим с результатами расчета, справа – показ формул Как видно из рисунка 5.11, одинаковую операцию сложения нужно выполнить с парами чисел колонок A и B в строках 1 – 5, затем с парами чисел колонок B и C. Для организации подобных вычислений используется одна из глав-ных особенностей электронных таблиц – изменение относительной адре-сации при копировании формул. Формула в колонке C была написана один раз в строке 1, затем за маркер копирования/автозаполнения в правом 8 нижнем углу скопирована в остальные строки путем расширения выбора на строки 2 – 5, затем получившаяся выделенная колонка была скопирова-на в столбец D (рисунок 5.12). Маркер копирования/ автозаполнения Рисунок 5.12 – Копирование формул При написании формулы не следует писать ссылки символами, а следует указывать на экране мышкой те ячейки, данные которых исполь-зуются при вычислениях в формуле. При этом в формуле появляются ссылки на указанные ячейки. Как видно из примера, при копировании формул ссылки изменяются: Рисунок 5.11 – при копировании вниз формулы строки 1 =A1+B1, для строки 2 формула Копирование формулы будет иметь вид =A2+B2 и т. д., при копировании вправо – формула для столбца C – =A1+B1, для столбца D – =B1+C1. Такие ссылки (A1, B1, C1) называются относительными. Если при копировании формулы ссылка в ней или ее часть (обо-значение столбца или строки) не должны изменяться, используется аб-солютная и смешанная адресация. Для обозначения такого адреса ис-пользуют знак доллар $ перед неизменяющимся элементом (изменять тип адресации следует нажатием клавиши F4): 1) $A$1 при копировании не будут изменяться обозначение столбца и строки; 2) $A1 при копировании не будет изменяться обозначение столбца; 3) A$1 при копировании не будет изменяться обозначение строки. Пример использования абсолютной и относительной адресации по-казан на рисунке 5.13. 9 =B3 =(B4-B3)/(B5-1) =D4+$B$6 Рисунок 5.13 – Использование абсолютной и относительной адресации Пример использования смешанной адресации показан на рисунке 5.14. Рисунок 5.14 – Использование смешанной адресации 10 Система Excel имеет достаточно большой набор функций (см. вклад-ку Формулы на рисунке 5.15). Функции сгруппированы по типу обрабатываемых ими данных: – финансовые (БС, ВСД, КПЕР…); – логические (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА …); – текстовые (ЗАМЕНИТЬ, КОДСИМВ, ЛЕВСИМВ, ПРАВСИМВ … ); – работы с датой и временем (СЕГОДНЯ, ТДАТА, МИНУТЫ…); – ссылки и массивы (АДРЕС, ВЫБОР, ТРАНСП …); – математические (ABS, ACOS, LN, LOG10…); – в том числе матричные МОБР, МОПРЕД, МУМНОЖ…; – статистические (ДИСП, КВАДРОТКЛ, КОРРЕЛ…); – инженерные (БЕССЕЛЬ.I, ВОСЬМ.В.ДВ, ДВ.В.ДЕС…) и пр. Рисунок 5.15 – Вкладка ленты Формулы Excel 2010 на широком экране 4 Использование математических функций и матричных операций В системе Excel в категории Математические функции присут-ствует 63 наименования. Это функции тригонометрические, логарифмиче-ские, матричные, округления, преобразования, вычисления случайного числа, суммирования, перемножения и пр. Кроме того в категории Инженерные функции присутствует не-сколько десятков наименований функций для работы с числами различных систем счисления, операций с комплексными числами и пр. Названия функций для русской версии Office и для версий на других языках могут различаться. Поэтому при использовании функций всегда следует найти их в библиотеке системы (даже таких, как SIN и LOG). Для этого в процессе написания формул можно использовать следу-ющие варианты работы с функциями: 11 1) списки для кнопок на вкладке ленты Формулы (см. рисунок 5.15), 2) раскрывающийся список функций слева в строке формул, 3) значок fx в той же строке, 4) контекстный список, появляющийся при написании первых букв функций. В 1 – 3 варианте для выбранной функции появляется диалоговое ок-но задания аргументов функции (для варианта 3 при повторном выделении в формуле ранее написанной функции позволяет редактировать её в диало-говом окне), как показано на рисунке 5.16. Это же окно можно вызвать, ес-ли выбрать в контекстном списке функций (вариант 4) нужное название, а потом нажать на значок fx в строке формул. Рисунок 5.16 – Диалоговое окно задания аргументов для функции ОКРУГЛ При использовании математических функций следует обращать вни-мание на наличие нескольких функций логарифмов с разным основанием и на единицы измерения углов в тригонометрических функциях (радианы). Кроме основных функций с системой Microsoft Office поставляются надстройки для Excel – вспомогательные программы, служащие для ис-пользования дополнительных возможностей, они устанавливаются из окна Параметры Excel, раздел Надстройки. Например, при проведении сложного статистического или инженер-ного анализа можно упростить процесс и сэкономить время, используя надстройку "Пакет анализа". Надстройку "Поиск решения" можно исполь-зовать для определения влияния ячеек на экстремальные значения зависи-мой ячейки. Примеры использования математических функций Пусть нам нужно вычислить y по математической формуле: 12 причем в этой формуле значения b и с заданы в градусах. В системе Excel для вычисления y следует в отдельных ячейках за-дать значения переменных a, b и c, т. к. эти значения встречаются в фор-муле несколько раз, а также с целью расчета по формуле с различными ис-ходными данными. Кроме того эту сложную формулу во избежание оши-бок следует разбить на части, например, следующим образом: Расчетные формулы в системе Excel показаны на рисунке 5.17. Рисунок 5.17 – Пример расчета по сложной формуле Достаточно часто в электронных таблицах используется функция суммирования чисел в столбце или строке (рисунок 5.18). Кроме обычной функции СУММ в системе Excel есть также: - СУММЕСЛИ (вычисление суммы для заданного условия, пример ис-пользования показан на рисунке 5.18), - СУММЕСЛИМН (суммирует числа в ячейках в соответствии с не сколькими заданными условиями), - СУММКВ (вычисляет сумму квадратов аргументов), - СУММКВРАЗН (вычисляет сумму квадратов разностей значений в двух массивах), - СУММСУММКВ (вычисляет сумму сумм квадратов соответствующих элементов двух массивов). =D3*E3 =СУММ(F3:F12) =СУММЕСЛИ($A$3:$A$12;A15;$F$3:$F$12) Рисунок 5.18 – Использование функций СУММ и СУММЕСЛИ Особенность использования матричных операций: исходные данные и результат расчета – не одно число, а группа чисел. Исходные числа должны размещаться в соседних ячейках листа Excel, результирующие числа получа-ются первоначально в одной ячейке, но затем разворачиваются на расчетную матрицу с использованием сочетания клавиш CTRL+SHIFT+ENTER, как показано на рисунке 5.19. Рисунок 5.19 – Использование матричных функций МОБР и МУМНОЖ Задания: 1. С использованием автозаполнения занести на Лист 1 электронной таблицы списки дней недели и месяцев (сокращенные обозначения и полные), дат по дням, по рабочим дням, по месяцам и по годам. Показать, как будет выглядеть при копировании текст: Группа 1, 1-я пара, 1-й семестр, 1-я неделя. 2. Выполнить на Листе 2 расчет значений аргумента и функции, как показано на рисунке 5.11, для варианта задания в таблице, соответствующего номеру Вашего компьютера: 3. Выполнить на Листе 3 расчет значения Y для варианта задания в таблице, соответствующего номеру Вашего компьютера. Значения а, b и c задать в таблице исходных данных. Единицы измерения угла в таблице заданы в радианах. Сравнить полученное значение с результатом в таблице: 15 4. Выполнить на Листе 4 решение системы линейных уравнений в соответствии с примером, показанным на рисунке 5.19. 16