Краткое изложение учебного материала по теме

Лабораторная работа Microsoft Excel 2010.

Работа с формулами. Использование абсолютной и относительной адресации. Математические функции

Цель работы

Освоение основных приемов работы в электронной таблице. Занесение данных, использование автозаполнения и формул с относительными и абсолютными ссылками.

Учебные вопросы

1. Ячейки электронной таблицы и их адресация

2. Занесение данных в ячейки

3. Работа с формулами. Относительные и абсолютные ссылки

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

Краткое изложение учебного материала по теме

1 Ячейки электронной таблицы и их адресация

Основным элементом электронной таблицы является ячейка (иликлетка) таблицы.

В каждую ячейку может быть помещена информация различного типа:

– текстовая длиной до 32767 символов;

– числовая;

– дата;

– время.

Полную техническую характеристику, возможности и ограничения Excel 2010 можно посмотреть на сайте Microsoft http://office.microsoft.com/ru-ru/excel-help/HP010342495.aspx?CTT=1.

На лист электронной таблицы можно также вставить объекты разных типов: диаграммы, векторные фигуры, рисунки, надписи в рамке, фигурный текст (WordArt) и пр.

Ячейки таблицы образуют строки и столбцы. Каждая ячейка имеет адрес, образованный обозначением столбца и строки (например, A1,BB22,CCC333),который может использоваться при написании формул(рисунок

5.1).

Стиль A1 адреса ячейки можно изменить на другой – на числовое обозначение столбцов и колонок (вкладка Файл ► Параметры, в появившемся окне Параметры Excel в разделе Формулы поставить галочку у пункта Стиль ссылок R1C1). Те же ссылки, что и ранее, будут выглядеть, как R1C1, R22C54, R333C2109, где R – row (ряд), C – column (колонка).

Каждая ячейка электронной таблицы имеет множество свойств, которые определены по умолчанию для выбранного стиля ячейки. Свойства можно просмотреть и изменить на вкладке Главная ленты, либо с использованием традиционного диалогового окна Формат ячеек (вызвать его можно из контекстного меню или маленькой кнопочкой в правом нижнем углу любой группы кнопок вкладки Главная).

Ячейка электронной таблицы по умолчанию имеет стиль Обычный (лента Главная, группа Стили). Параметры стиля Обычный можно изменить, тогда все ячейки таблицы этого стиля будут иметь новые свойства. Можно создать новый стиль, если для ячейки задать новые свойства.

В соответствии с требованиями по оформлению студенческих работ зададим для стиля ячеек Обычный шрифт Times New Roman, обычный, размер 13, черный, как показано на рисунке 5.4.

Рисунок 5.4 – Редактирование стиля ячеек Обычный

2 Занесение данных в ячейки

При размещении информации на листе следует предварительно задать параметры страницы (вкладка Разметка страницы), чтобы на листе появились пунктирные линии границ страницы. Перед вводом информации для ее однозначного толкования системой можно задать тип данных в ячейке в группе Число вкладки Главная.

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

Редактирование существующей информации ячейки возможно в Строке формул или в ячейке после двойного щелчка наней мышкой.

Следует помнить, что в Excel существует внутреннее представление информации и внешнее. Внешнее представление задается форматом ячейки. Внутреннее – 15 значащих цифр для чисел, 32767 символов при вводе текста в ячейку (вставить из буфера обмена можно больше). Внутреннее представление даты – целое число, которое отсчитывается от 1 января 1900 г., формат времени – число от 0 до 0,99999999 (количество секунд в сутках равно 86400, одна секунда в Excel = 1/86400 = 0,0000115740).

При вводе длинного текста в ячейку этот текст виден справа от ячейки, пока не встретит не пустую ячейку (на ней текст оборвется). Если за-дать параметр Перенос текста (кнопка в группе Выравнивание вкладки Главная) – текст будет располагаться в несколько строк в одной ячейке или в группе объединенных ячеек (кнопка Объединить и поместить в центре). Примеры размещения текста на листе электронной таблицы показаны на рисунке 5.5.

Рисунок 5.5 – Примеры работы с текстом в электронной таблице При вводе чисел в ячейку следует учитывать следующее:

- разделителем целой и дробной части числа может быть точка или запятая. Вид разделителя определяется настройками Windows.

Поэтому следует всегда использовать цифры и точку (она является и запятой при соответствующей настройке) на числовой клавиатуре;

- для ввода больших и маленьких чисел используется экспоненциальная форма с использованием латинской буквы e. Число -1,23e-15 это -1,23 ⋅ 10^-15;

- в формате чисел можно задать количество видимых знаков после запятой, для отрицательных чисел можно при необходимости задать формат красные числа без знака;

- для чисел можно задать разделитель групп разрядов (в соответствии с настройками Windows).

Тип данных дата/время наиболее сложен из-за наличия различных форм внешнего представления даты, времени или даты и времени одновременно. Формат дат и времени зависит от настроек Windows (Язык и региональные стандарты).Полный список всех стандартных форматов даты/времени можно просмотреть в окне Формат ячеек, кроме того можно определить собственные форматы представления данных.

При вводе данных может быть использовано автозаполнение для чисел, дат и определенных в системе Excel списков (рисунок 5.6).

Рисунок 5.6 – Списки для автозаполнения системы Excel

Пример использования списков: пишем в ячейку A2 Пн, затем с нажатой левой кнопкой мыши за маркер копирования/автозаполнения в правом нижнем углу ячейки A2 расширяем выбор вниз до ячейки A8. Получаем список сокращенных названий дней недели. Аналогично для других списков.

При автозаполнении ячеек Маркер копирования/автозаполнения датами существует несколько вариантов: расширение выбора за маркер копирования/ автозаполнения левой кнопкой мыши – заполнение датами подряд по дням

календаря; при растягивании за маркер копирования/автозаполнения правой кнопкой мыши – в контекстном меню появляются варианты: по дням, по рабочим дням (пятидневка), по месяцам, по годам (рисунок 5.8).

Рисунок 5.8 – Использование автозаполнения для дат

Автозаполнение для чисел при расширении выбора за маркер копирования/автозаполнения правой кнопкой мыши – после выбора в контекстном меню пункта Прогрессия появляется диалоговое окно для задания параметров арифметической или геометрической прогрессии (рисунок 5.9).

Рисунок 5.9 – Использование автозаполнения для чисел

Автозаполнение для текста, начинающегося или заканчивающегося числом – при копировании число в начале строки или в конце (при отсутствии числа в начале строки) автоматически увеличивается на 1 (рисунок 5.10).

Рисунок 5.10 – Использование автозаполнения для текста с числами

3 Работа с формулами. Относительные и абсолютные ссылки

Формулы – это выражения, с помощью которых можно выполнять вычисления, манипулировать содержимым других ячеек, проверять условия и пр.

Формула всегда начинается со знака равенства (=).

В Excel обычно задан режим показа результатов вычислений по формулам, но можно задать и режим показа формул (вкладка Формулы – Показать формулы в группе Зависимости формул).

Формулы в системе Excel могут состоять из постоянных значений (числа, текст, даты /время), символов математических операций (+, -, *, /, ^), круглых скобок для изменения порядка действий (система знает прио-ритет вычислений в математических формулах, принятый в математике), функций разного типа, ссылки на ячейки и имена ячеек.

Часто при вычислениях одинаковую операцию нужно выполнить с рядом данных. Простейший пример показан на рисунке 5.11.

Рисунок 5.11 – Расчет по формулам в Excel,

слева – обычный режим с результатами расчета, справа – показ формул

Как видно из рисунка 5.11, одинаковую операцию сложения нужно выполнить с парами чисел колонок A и B в строках 1 – 5, затем с парами чисел колонок B и C.

Для организации подобных вычислений используется одна из главных особенностей электронных таблиц – изменение относительной адресации при копировании формул. Формула в колонке C была написана один раз в строке 1, затем за маркер копирования/автозаполнения в правом нижнем углу скопирована в остальные строки путем расширения выбора на строки 2 – 5, затем получившаяся выделенная колонка была скопирована в столбец D.

При написании формулы не следует писать ссылки символами, а следует указывать на экране мышкой те ячейки, данные которых используются при вычислениях в формуле. При этом в формуле появляются ссылки на указанные ячейки.

Если при копировании формулы ссылка в ней или ее часть (обозначение столбца или строки) не должны изменяться, используется абсолютная и смешанная адресация. Для обозначения такого адреса используют знак доллар $ перед неизменяющимся элементом (изменять тип адресации следует нажатием клавиши F4):

₁₎ $A$1 при копировании не будут изменяться обозначение столбцаи строки;

₂₎ $A1 при копировании не будет изменяться обозначение столбца; A$1 при копировании не будет изменяться обозначение строки.

Пример использования смешанной адресации показан на рисунке 5.14.

Рисунок 5.14 – Использование смешанной адресации

Система Excel имеет достаточно большой набор. Функции сгруппированы по типу обрабатываемых ими данных:

– финансовые (БС, ВСД, КПЕР…);

– логические (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА …);

– текстовые (ЗАМЕНИТЬ, КОДСИМВ, ЛЕВСИМВ, ПРАВСИМВ …);

– работы с датой и временем (СЕГОДНЯ, ТДАТА, МИНУТЫ…);

– ссылки и массивы (АДРЕС, ВЫБОР, ТРАНСП …);

– математические (ABS, ACOS, LN, LOG10…);

– в том числе матричные МОБР, МОПРЕД, МУМНОЖ…;

– статистические (ДИСП, КВАДРОТКЛ, КОРРЕЛ…);

– инженерные (БЕССЕЛЬ.I, ВОСЬМ.В.ДВ, ДВ.В.ДЕС…) и пр.

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

В системе Excel в категории Математические функции присут-ствует 63 наименования. Это функции тригонометрические, логарифмиче-ские, матричные, округления, преобразования, вычисления случайного числа, суммирования, перемножения и пр.

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

Названия функций для русской версии Office и для версий на других языках могут различаться. Поэтому при использовании функций всегда следует найти их в библиотеке системы (даже таких, как SIN и LOG).

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

1) списки для кнопок на вкладке ленты Формулы,

2) раскрывающийся список функций слева в строке формул,

3) значок f_x в той же строке,

4) контекстный список, появляющийся при написании первых букв

функций.

В 1 – 3 варианте для выбранной функции появляется диалоговое окно задания аргументов функции (для варианта 3 при повторном выделении в формуле ранее написанной функции позволяет редактировать её в диалоговом окне), как показано на рисунке 5.16. Это же окно можно вызвать, если выбрать в контекстном списке функций (вариант 4) нужное название, а потом нажать на значок f_x в строке формул.

При использовании математических функций следует обращать вни-мание на наличие нескольких функций логарифмов с разным основанием и на единицы измерения углов в тригонометрических функциях (радианы).

Кроме основных функций с системой Microsoft Office поставляются надстройки для Excel – вспомогательные программы, служащие для и с-пользования дополнительных возможностей, они устанавливаются из окна

Параметры Excel,раздел Надстройки.

Например, при проведении сложного статистического или инженерного анализа можно упростить процесс и сэкономить время, используя надстройку "Пакет анализа". Надстройку "Поиск решения" можно использовать для определения влияния ячеек на экстремальные значения зависимой ячейки.

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

Пусть нам нужно вычислить y по математической формуле:

причем в этой формуле значения b и с заданы в градусах.

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

Расчетные формулы в системе Excel показаны на рисунке 5.17.

Рисунок 5.17 – Пример расчета по сложной формуле

Достаточно часто в электронных таблицах используется функция суммирования чисел в столбце или строке (рисунок 5.18). Кроме обычной функции СУММ в системе Excel есть также:

- СУММЕСЛИ (вычисление суммы для заданного условия,пример использования показан на рисунке 5.18),

- СУММЕСЛИМН (суммирует числа в ячейках в соответствии с несколькими заданными условиями),

- СУММКВ (вычисляет сумму квадратов аргументов),

- СУММКВРАЗН (вычисляет сумму квадратов разностей значений вдвух массивах),

СУММСУММКВ (вычисляет сумму сумм квадратов соответствующихэлементов двух массивов).

Рисунок 5.18 – Использование функций СУММ и СУММЕСЛИ

Особенность использования матричных операций: исходные данные и результат расчета – не одно число, а группа чисел. Исходные числа должны размещаться в соседних ячейках листа Excel, результирующие числа получаются первоначально в одной ячейке, но затем разворачиваются на расчетную матрицу с использованием сочетания клавиш CTRL+SHIFT+ENTER, как показано на рисунке 5.19.

Рисунок 5.19 – Использование матричных функций МОБР и МУМНОЖ

Учебное задание

а) с использованием автозаполнения занести на Лист 1 электронной таблицы списки дней недели и месяцев (сокращенные обозначения и полные), дат по дням, по рабочим дням, по месяцам и по годам. Показать, как будет выглядеть при копировании текст: Группа 1, 1-я пара, 1-й семестр, 1-я неделя.

б) выполнить на Листе 2 расчет значений аргумента и функции, как показано на рисунке 5.11, для варианта задания в таблице, соответствующего номеру Вашего компьютера:

в) выполнить на Листе 3 расчет значения Y для варианта задания в таблице, соответствующего номеру Вашего компьютера. Значения а,b и c задать в таблице исходных данных. Единицы измерения угла в таблице заданы в радианах. Сравнить полученное значение с результатом в таблице:

г) выполнить на Листе 4 решение системы линейных уравнений в соответствии с примером, показанным на рисунке 5.19.

Дата добавления: 2015-12-21; просмотров: 34; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!