Электронные таблицы Excel
Excel – программа, предназначенная для обработки электронных таблиц.
Электронные таблицы Excel используются как средство проведения расчетов и визуализации результатов расчетов в виде таблиц и графиков.
Загрузка Excel:
Пуск ► Программы ► MS Office ► MS Excel.
После запуска MS Excel на экране появляется окно, представленное на рис. 1.
Рис.1.Окно MS Excel 2007
Документ Excel называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц.
По умолчанию рабочая книга имеет три рабочих листа (Лист 1, Лист 2 и Лист 3). В окне документа отображается только текущий рабочий лист, с которым и ведется работа. Число листов можно увеличить, можно изменить название листа, удалить лист, переместить лист с помощью контекстного меню (правой кнопкой мыши по ярлычку листа) и др. способов.
Ячейки и их адресация.
Электронные таблицы состоят из столбцов и строк. Столбцы озаглавлены буквами латинского алфавита и их двухбуквенными комбинациями (А, В, С,…, АА, АВ,…). Строки – пронумерованы (1, 2, 3,…).Всего рабочий лист содержит 256 столбцов и 65536 строк.
Место пересечения столбца и строки называется ячейкой. Каждая ячейка имеет свой уникальный адрес, состоящий из названия столбца и номера строки (например А1, Р45, …). Одна из ячеек всегда является активной и выделяется рамкой (рис. 1). Операция ввода и редактирования содержимого ячейки всегда проводятся в активной ячейке. В ячейку можно ввести число (целое или действительное), текст или формулу. Запись формулы в ячейку начинается с ввода знака равенства «=». Ввод данных обязательно должен завершаться нажатием клавиши ENTER.
В электронных таблицах можно работать как с отдельными ячейками, так и с группой ячеек, которые образуют диапазон (блок). В качестве диапазона может рассматриваться строка (часть строки) или столбец (часть столбца), или прямоугольник, состоящий из нескольких строк, столбцов или их частей. Диапазон задается указанием адреса первой и последней его ячеек, между которыми ставится символ двоеточие «:» (например, А4:С10, В2:В15, С1:Н1). Диапазону можно присвоить имя. Для этого необходимо выделить его на рабочем листе, а затем выполнить команду меню Вставка ► Имя и в появившемся диалоговом окне задать имя диапазона.
Формат указания адреса ячейки называется ссылкой. Ссылки на ячейки можно задавать разными способами. В пределах одного рабочего листа используются относительные, абсолютные и смешанные ссылки.
Относительные – это ссылки, которые при копировании формулы изменяются автоматически в соответствии с относительным расположением исходной ячейки и создаваемой копией. Например, А4, С10, В2 и др.
Абсолютные – это ссылки, которые не изменяются при копировании. Например, $А$4, $С$10, $В$2 и др. Если диапазону присвоено имя, то оно будет использоваться в качестве абсолютной ссылки.
Смешанные – это ссылки, в которых при копировании изменяется либо столбец, либо строка. Например, А$4, $С10, В$2 и др.
Для изменения способа адресации при записи или редактировании формулы необходимо установить указатель мыши на ссылку и нажать клавишу «F4» один или несколько раз.
Ссылки на другие листы
Различают внутренние и внешние ссылки на другие листы.
Внутренние ссылки – это ссылки на другие листы той же книги. Существует два типа внутренних ссылок:
1. Ссылки на лист с номером N. Формат ссылки <ЛистN!> <Ссылка>,
где Ссылка – относительная ссылка на ячейку или диапазон ячеек листа N;
2. Объемные ссылки представляют собой ссылки на ячейки диапазона листов с одинаковой структурой в книге. Формат объемной ссылки
=<ИмяФункции> (‘ЛистК:ЛистМ’!<Ссылка>),
где <ИмяФункции> – имя стандартной функции Excel, К, М – номера первого и последнего листов непрерывного диапазона в книге соответственно.
Например
Для создания объемной ссылки необходимо выполнить следующие действия:
1. ввести в ячейку текущего листа =< ИмяФункции >;
2. отметить с помощью мыши лист К (левой кнопкой мыши по ярлычку листа с номером К) диапазона листов, а затем при нажатой клавише Shift – лист М;
3. выделить диапазон ячеек (< Ссылка >) на текущем листе, и нажать клавишу Enter.
При внутренних ссылках по умолчанию используется механизм относительной адресации ячеек листа.
Объемные ссылки, как правило, используются со следующими встроенными функциями Excel: СУММ, СЧЕТ, СРЗНАЧ, МАКС, МИН, ПРОИЗВЕД и др.
Внешние ссылки – это ссылки на листы других книг. Формат внешних ссылок [КнигаК]<ЛистN!><Ссылка>,
Где К – идентификатор книги.
При использовании внешних ссылок по умолчанию используется механизм абсолютной адресации на листе.
Вычисления в Excel
Вычисления в Excel осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками арифметических операций.
Часто для табличных вычислений необходимо использовать числовые последовательности. Зададим последовательность для чисел, изменяющихся
от -4 до 4 с шагом равным 0,5. Существует три способа задания числовой последовательности с помощью автозаполнения:
Первый способ. В ячейку А1 введем начальное значение (-4). В ячейку А2 – следующее по порядку (-3,5). Выделяем ячейки А1 и А2. Указатель мыши устанавливаем на маркер заполнения и левой кнопкой мыши перемещаем его по столбцу А до значения равного 4 (рис. 2).
Рис. 2. Первый способ задания числовой последовательности
Второй способ. В ячейку А1 введем начальное значение (-4). Выделим эту ячейку. Откроем вкладку Главная ► группа Редактирование ► кнопка Заполнение (открыть) ►пункт Прогрессия. В появившемся диалоговом окне «Прогрессия» установим параметры: расположение, шаг, предельное значение (рис. 3)
Рис. 3. Второй способ задания числовой последовательности
Закрыв окно «Прогрессия» в столбце А получим все числа заданной последовательности.
Третий способ. В ячейку А1 введем начальное значение (-4). В ячейку А2 введем формулу (рис. 4). Выделим ячейку А2, установим указатель мыши на маркер заполнения и с его помощью скопируем формулу по столбцу А до значения равного 4.
Рис. 4. Третий способ задания числовой последовательность
Пользователь выбирает для себя наиболее удобный способ заполнения.
Теперь перейдем к работе с функциями Excel.
Функции — заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. В качестве аргументов используются ссылки на ячейки, формулы, или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента. Функции позволяют выполнять как простые, так и сложные вычисления.
На рабочем листе имеется строка формул (рис. 1), предназначенная для отображения и редактирования содержимого активной ячейки. Запись формулы начинается с ввода знака равенства «=». При этом строка формул преобразуется к виду
– кнопка раскрывающегося списка недавно использованных функций, можно использовать для вставки нужной функции;
– кнопка отмены ввода формулы;
– кнопка завершения ввода формулы, аналогична действию клавиши Enter;
– кнопка вызова Мастера функций.
В самом общем смысле Мастером в Excel называется подпрограмма, нацеленная на решение определенного класса задач. Мастер ведет активный диалог с пользователем, предлагая ему вопросы, на которые он должен дать ответ, чтобы добиться желаемого результата. Помимо вопросов, Мастер предлагает варианты ответов на них.
Функции Excel предназначены для выполнения математических, статистических, финансовых и других расчетов. Методика использования функций требует соблюдения определенной технологии.
1. На рабочем листе в отдельных ячейках осуществляется подготовка (ввод) значений аргументов функции.
2. В отведенную для формулы ячейку вводится знак равенства «=». Для вставки функции вызывается Мастер функций нажатием кнопки в строке формул.
3. В появившемся диалоговом окне (рис. 5)
Рис. 5 Окно Мастера функций
в строке «Категория» выбираем нужную категорию или «Полный алфавитный перечень», в разделе «Выберите функцию» – выделяем заданную функцию и закрываем окно Мастера с помощью кнопки ОК.
4. В строке формул появляется синтаксическая форма выбранной функции (ее имя и пустые круглые скобки), а на экране – диалоговое окно «Аргументы функции» для ввода ее аргументов (рис. 6).
В строку «Число» диалогового окна можно ввести аргумент в виде ссылки на ячейку, содержащую значение аргумента, число или выражение, содержащее другие функции. При этом для вставки функции снова вызывается Мастер функций.
5. После окончания ввода аргумента, не обращая внимания на диалоговое окно «Аргументы функции», переходим в строку формул для продолжения ее ввода и т.д. Завершение ввода формулы фиксируется нажатием клавиши Enter или кнопки в строке формулы.
Рис. 6. Диалоговое окно для ввода аргументов функции
При наборе формулы необходимо помнить, что:
· Формула вводится слева направо в одну строку (не более 256 символов);
· Знаки арифметических операций («+», «-», «*», «/», «^») вводятся с клавиатуры;
· Порядок действий при вычислении формулы определяется приоритетом операций и круглыми скобками. Приоритет операций по убыванию:
1. действие в круглых скобках;
2. вычисление функций;
3. действие возведения в степень «^»;
4. умножение и деление «* и /»;
5. сложение и вычитание «+ и -».
Следовательно, если необходимо выполнить операцию низкого приоритета перед операцией более высокого приоритета, то используются круглые скобки.
Для проведения математических расчетов используются функции, относящиеся к категории «Математические». Наиболее часто встречающиеся функции приведем в таблице
| Математическая запись | Запись в синтаксисе Excel |
| SIN(число) |
| COS(число) |
| TAN(число) |
| ASIN(число) |
| ACOS(число) |
| ATAN(число) |
| ABS(число) |
| EXP(число) |
| LN(число) |
| LOG10(число) |
| LOG(число, основание) |
| КОРЕНЬ(число) |
| ПИ() |
Пример 1. Пусть необходимо вычислить значение 
при 
. В ячейку А1 введем значение 0,1 а в ячейку В1 – формулу. Результат вычисления представлен на рис. 7.
Рис. 7. Результат вычисления по формуле
Пример 2. Пусть необходимо вычислить значение 
при 
и 
. Ввод исходных данных и все вычисления представлены на рис. 8.
Рис. 8. Результаты вычисления по формуле
В этом примере в столбец С вводится числовая последовательность одним из способов автозаполнения, которые описаны выше. В ячейку D2 вводится формула для вычисления произведения вида
D2 =EXP((C2+$A$2)^(1/3))/(C2^2+$A$2)+1,1
и копируется с помощью маркера заполнения по столбцу для вычисления при каждом значении k. Необходимо учесть, что для адресации значения x используется абсолютная ссылка ($A$2). В ячейку Е2 вводим формулу для вычисления произведения
Е2=ПРОИЗВЕД(D2:D11).
Формулу для вычисления y вводим в ячейку F2
F2=КОРЕНЬ(ПИ()/2+ABS(A2))-E2
Здесь для адресации значения x используется относительная ссылка, так как вычисляется единственное значение и не копируется по столбцу.
Вычисление значений функции двух переменных
Если необходимо произвести вычисления по формуле, содержащей два аргумента, то расчет оформляется в виде таблицы. В качестве заголовков строк и столбцов такой таблицы должны выступать значения аргументов, а тело таблицы будет представлять собой набор значений функции. Таким способом можно подготовить данные для построения поверхности с помощью Мастера диаграмм. Рассмотрим вычисление функции двух переменных на примере.
Пусть необходимо вычислить значения функции двух переменных вида 
для 
, изменяющегося от -5 до 5 с шагом 2, и для 
, изменяющегося от 0 до 5 с шагом 1.
Построение таблицы начнем с ячейки А1, в которую введем символы x и y через несколько пробелов. Зафиксируем ввод (нажатием клавиши Enter) и выделим ее снова. Затем выполним команду меню Формат ► Ячейки ► Вкладка «Граница ». Находим нужную границу. После выполнения всех операций ячейка А1 имеет вид 
. Теперь понятно, что заголовками строк будут значения аргумента x, а заголовками столбцов будут значения аргумента y. Далее выполняем следующие действия:
1. Введем последовательность значений x в столбец А, начиная с ячейки А2, воспользовавшись одним из способов автозаполнения;
2. Введем последовательность значений y в строку 1, начиная с ячейки В1, воспользовавшись одним из способов автозаполнения;
3. В ячейку В2 введем формулу =A2^2+B1^2. Конечно, хотелось бы, чтобы формулы в таблице появились в результате применения операции заполнения (с помощью маркера заполнения). Поскольку заполнение выполняется для строки или для столбца, то формулу в таком виде оставлять нельзя, так как при перемещении формулы относительные ссылки будут меняться. При заполнении столбцов в формуле будет меняться только номер строки, а, следовательно, и значение аргумента y, которое должно оставаться постоянным. При заполнении строк будут изменяться только столбцы, а, следовательно, и значение аргумента x, которое должно оставаться постоянным. Поэтому в ссылке А2 необходимо закрепить столбец ($A2) а, в ссылке В1 – строку (B$1). Результирующая формула будет иметь вид =$A2^2+B$1^2. Теперь можно использовать операцию заполнения.
4. Выделим ячейку В2 и с помощью маркера заполнения сначала заполним вторую строку, а затем – последний столбец. В результате получим таблицу значений функции двух переменных (рис. 9).
Рис. 9. Результаты вычислений
На рис. 9 выделена матрица значений функции двух переменных.
Создание диаграмм
Excel предоставляет пользователю широкий диапазон стандартных типов диаграмм научного и делового назначения, каждый из которых имеет несколько вариантов. Диаграммы в Excel включают много элементов, каждый из которых можно выделять и изменять отдельно. Краткие сведения об основных элементах диаграмм представлены в таблице 1.
Таблица 1. Элементы диаграммы Excel
| Название элемента | Описание |
| Ось | Линия, используемая как основа измерений для построения данных на диаграмме. Ось категорий (Х) и ось значений (У) образуют границы диаграммы и имеют шкалу для нанесения данных. Ось значений (Z) используется в трехмерных диаграммах. |
| Точка данных | Элемент данных. |
| Ряд данных | Совокупность точек данных, отображающая значения в строках или в столбцах рабочего листа. На диаграмме может быть отображен один или несколько рядов данных. |
| Легенда | Подпись, определяющая закрасу или цвета точек данных или категорий диаграммы. |
| Маркер | Элемент, который представляет точку данных на диаграмме. Вид маркера зависит от типа и вида диаграммы. Все маркеры одного ряда данных имеют одинаковую форму и цвет. |
| Область диаграммы | Вся диаграмма вместе со всеми ее элементами. |
| Область построения | В двумерной диаграмме это прямоугольная область, ограниченная двумя осями и содержащая все ряды диаграммы. В трехмерной диаграмме это область, ограниченная осями и включающая ряды данных, названия категорий, подписи делений и названия осей. |
| Формула рядов | Формула с внешними ссылками на данные определенного листа, по которым построена диаграмма. |
| Текст | Надписи (название диаграммы, значения и категории на осях) и подписи (текст, связанный с точками данных). Любой текст на диаграмме можно изменять и перемещать. |
В Excel можно построить диаграммы на рабочем листе рядом с таблицами данных или на отдельном листе. Создание диаграммы рядом с таблицами данных имеет смысл при оформлении отчетов, для демонстрации диаграмм рядом с данными, по которым они построены. Построение диаграммы на отдельном листе удобно, когда ее, например, нужно вывести на печать.
Диаграммы легко строятся с помощью Мастера диаграмм. Он руководит процессом создания диаграммы шаг за шагом и дает возможность перед завершением процесса посмотреть ее и внести необходимые изменения.
Типы диаграмм
MS Excel имеет в своем составе 11 стандартных типов диаграмм. Их названия и краткое описание приведены в таблице 2.
Таблица 2. Типы диаграмм в Excel
| Тип диаграммы | Описание |
| Гистограмма | Используется, когда нужно создать впечатление отдельных измерений, относящихся к разным интервалам времени. Категории располагаются по горизонтали, а значения – по вертикали. |
| Линейчатая | Похожа на гистограмму за исключением того, что осью категорий является вертикальная ось (У), а осью значений – горизонтальная ось (Х). Используется для сопоставления отдельных значений в определенный момент времени. |
| График | Отображает зависимость данных (ось У) от величины, которая меняется с постоянным шагом (ось Х).Используется для отображения тенденции изменения данных во времени. |
| Круговая | Используется для отображения соотношения частей и целого. Строятся только по одному ряду данных, первому в выделенном диапазоне. Эту диаграмму следует использовать, когда все компоненты в сумме составляют 100%. |
| Точечная | Используется для представления дискретных измерений по осям Х и У. Демонстрирует тенденции изменения данных при неравных интервалах времени или других интервалах измерения, отложенных по оси категорий (Х). |
| Диаграмма с областями | Используется, если необходимо проследить непрерывное изменение суммы значений всех рядов данных и вклад каждого ряда в эту сумму. |
| Кольцевая | Позволяет сравнить вклад частей в целое. В ней могут быть представлены два и более ряда данных. Каждое кольцо представляет отдельный ряд данных. |
| Лепестковая | Используется, когда нужно показать соотношения отдельных рядов данных, или одного определенного ряда данных и всех остальных рядов. |
| Поверхность | Используется для наглядного представления высоких и низких значений в наборе данных, который зависит от двух переменных. Диаграмма представляет собой поверхность, натянутую на точки. Такую диаграмму можно поворачивать и видеть ее с разных точек зрения. |
| Пузырьковая | Позволяет отобразить на плоскости набор данных из трех значений. Первые два откладываются по оси категорий (Х) и по оси значений (У). Третье значение представляется размером пузырька. |
| Биржевая | Используется для отображения изменения курса акций во времени. При формировании рядов данных для биржевой диаграммы необходимо размещать данные в последовательности, которая указывается внизу диалогового окна «Тип диаграммы», при выборе соответствующего формата диаграммы. |
| Цилиндрические, конические и пирамидальные | Объемные варианты гистограмм и линейчатых диаграмм. Эти типы объемных диаграмм не добавляют новой информации, а имеют только внешний эффект. |
Построение диаграммы
Теперь перейдем непосредственно к построению диаграммы.
1. Чтобы создать диаграмму с помощью мастера диаграмм необходимо сначала создать таблицу данных, на основе которых она будет построена.
1.1. В первом столбце должны быть представлены значения аргумента X. Используя приёмы автозаполнения ввести в диапазон А2:В630 значения от –p до p с шагом 0,01.
1.2. Во втором столбце должны быть представлены значения функции Y=f(X). Значения Y вычислить по формуле sinХ. Для этого введем в ячейку В2 формулу =sin(A2) и скопируем ее по столбцу В.
1.3. Выделите эту таблицу.
2. Выберите команду Вставка–Диаграммы–Точечная и выберите тип диаграммы Точечная с гладкими кривыми
Редактирование и форматирование диаграммы осуществляется во вкладках Конструктор, Макет и Формат, которые отображаются при выделении области построения диаграммы.
3. Измените следующие параметры диаграммы:
3.1. Уберите легенду командой Макет–Подписи–Легенда–Нет
3.2. Введите название диаграммы «График функции y = sin x» с помощью команды Макет–Подписи–Название диаграммы––Над диаграммой;
3.3. Введите названия осей «Х» и «Y», выполнив соответственно команды Макет–Подписи–Название осей–Название основной горизонтальной оси–Название под осью и Макет–Подписи–Название осей–Название основной вертикальной оси–Горизонтальное название
3.4. Измените расположение подписей к осям: название оси ОХ – справа от оси, название оси OY – сверху над осью. Для этого достаточно выделить подпись и перетащить ее мышью.
3.5. Измените максимальное и минимальное значения оси ОХ, выполнив команду Макет–Оси–Оси–Основная горизонтальная ось–Дополнительные параметры основной горизонтальной оси. В появившемся диалоговом окне выберите Параметры оси и установите
· фиксированное минимальное значение -3,5
· фиксированное максимальное значение 3,5
3.6. Добавьте линии сетки по оси ОХ, выполнив команды Макет–Оси–Сетка–Вертикальные линии сетки по основной оси–Основные линии сетки
3.7. Измените размер основных делений сетки по оси ОХ:
· Выделите ось ОХ на диаграмме мышью или выполним команду Макет–Текущий фрагмент и в раскрывающемся списке Элементы диаграммы выберем Горизонтальная ось (значений)
· Выполните команду Макет–Текущий фрагмент–Формат выделенного фрагмента. В появившемся диалоговом окне выберите Параметры оси и установите цена основных делений 0,5
После выполнения перечисленных действий, будет построена диаграмма, вид которой представлен на рис.1. Но на ней не хватает элементов оформления. Ее необходимо отформатировать.
Рис. 1. Вид графика функции y=sin x