Тема 6

Табличный процессор Microsoft Excel

Основные элементы электронной таблицы Excel

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

Книга является хорошим организационным средством. Например, можно в одной книге собрать все документы (рабочие листы), относящиеся к определенному проекту (задаче), или все документы, которые ведутся одним исполнителем. Основа рабочего листа представляет собой сетку из строк и столбцов. Ячейка образуется пересечением строки и столбца. Выделенная мышью ячейка (ячейки) называется активной (на рис. 1 активная ячейка выделена рамкой).

Рис. 1. Элементы таблицы

Строка в рабочем листе идентифицируется именем (цифрой), которое появляется на левой стороне рабочего листа.

Столбец в рабочем листе также идентифицируется именем (буквами латинского алфавита), которое появляется вверху рабочего листа.

Рабочий лист книги Excel может содержать до 65 536 строк и 256 столбцов.

Ячейка - основной элемент таблицы - имеет свой уникальный адрес, состоящий из номера столбца и строки, например Е4.

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

При открытии ранее созданного файла в окне Excel появляется книга с введенными данными.

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

Окно электронной таблицы Excel

Общие элементы окна приложений Office описаны в предыдущих главах. Рассмотрим подробнее специфические элементы окна Excel (рис. 2).

Рис. 2. Окно Excel

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

Строка формул показывает формулу (если она присутствует в ячейке) или данные, содержащиеся в активной ячейке. В строке формул можно вводить и редактировать текст, числа и формулы.

В активную ячейку можно вводить и редактировать данные непосредственно в ячейке или в строке формул.

Ссылка на ячейку - адрес точки пересечения ее столбца и строки.

Например, ссылка на самую левую верхнюю ячейку на рабочем листе Excel - А1 .

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

Ярлыки используются для идентификации листа в рабочей книге, По умолчанию листы нумеруются Лист 1, Лист 2 и т. д.

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

Типы данных Excel

Excel позволяет вводить в ячейки три типа данных: числа, текст, формулы. Текст может использоваться для заголовков таблиц, объяснения или пометок на рабочем листе. Если Excel не распознает тип данных как числовой или как формулу, то данные воспринимаются как текст.

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

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

Любая формула начинается со знака (=). Если в ячейку введена формула, то по умолчанию ячейка будет показывать результат расчета.

Данные набираются непосредственно в активной ячейке, при этом они отображаются в строке формул. Также данные могут набираться и в строке формул.

Ввод данных в ячейки Excel

Нажатие клавиш перемещения курсора или щелчок по другой ячейке будет всегда приводить к сохранению набранных данных в активной ячейке перед перемещением к следующей.

Текст, ширина которого слишком велика, чтобы вместиться в текущую ячейку, будет зрительно накладываться на соседние ячейки, хотя фактически он будет содержаться в одной ячейке. Excel ограничивает текст или формулы в ячейке длиной в 255 символов.

Числа, которые слишком велики, чтобы быть показанными внутри текущей ячейки, будут отображаться как последовательность символов # # # #. Чтобы показать числовое значение в ячейке, надо увеличить ширину столбца (см. раздел "Форматирование ячеек").

Удалить содержимое ячейки можно с помощью клавиши Delete.

Автозаполнение в Excel

Функция Автозаполнение позволяет заполнять данными область ячеек по определенным правилам. Excel осуществляет поиск правила заполнения введенных данных для того, чтобы определить значения пустых ячеек. Если вводится одно начальное значение образца заполнения, то выделяется одна ячейка, если список с интервалом изменения данных, то необходимо выделить две ячейки, заполненные соответствующими данными.

Автозаполнение

1. Введите данные в начальную ячейку или смежные ячейки (если задается правило заполнения).

2. Выберите ячейку или ячейки, в которых установлено правило заполнения.

3. Установите курсор мыши на Маркере заполнения (рис. 3), маленьком квадратике в правом нижнем углу активной ячейки.

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

5. Отпустите кнопку мыши.

Рис. 3. Маркер заполнения активной ячейки

Есть один полезный прием автозаполнения - это перетаскивание маркера заполнения при нажатой правой клавише мыши. При этом появляется контекстное меню, которое поможет выбрать способ автозаполнения (рис. 45).

Рис. 4. Контекстное меню автозаполнения

Для удобства работы в Excel существуют стандартные списки, содержащие названия дней недели и месяцев, доступные при выборе команды Параметры в меню Сервис (закладка Списки) , а также списки, создаваемые пользователем (рис. 5).

Рис. 5. Создание пользовательского списка

Стандартные списки в отличие от пользовательских нельзя удалить или отредактировать. Для создания или изменения пользовательского списка автозаполнения выполните следующие действия:

1. Выберите команду Параметры в меню Сервис (Tools, Options) , а затем закладку Списки (List) .

2. Укажите Новый список (New) в поле Списки (List) .

3. Введите элементы списка или сделайте изменения в поле Элементы списка и нажмите кнопку Добавить (Add) , затем кнопку ОК.

Для удаления списка выделите его и нажмите кнопку Удалить (Delete) .

Проверка орфографии

Создание простых формул в Excel

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

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

В Excel формулы начинаются со знака =. Скобки ( ) могут использоваться для определения порядка математических операции.

Excel поддерживает следующие арифметические операции:

сложение (+);
умножение (*);
нахождение процента (%);
вычитание (-);
деление (/);
экспонента ( ^ ).

Операторы сравнения:

= равно;
< меньше;
> больше;
<= меньше или равно;
>= больше или равно;
<> не равно.

Операторы связи:

: диапазон;
; объединение;
& оператор соединения текстов.

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

Функция "Автосумма" автоматически трансформируется в случае добавления и удаления ячеек внутри области.

Формулы, реализующие вычисления в таблицах, для адресации ячеек используют так называемые ссылки.

Относительные и абсолютные ссылки Excel

Ссылка на ячейку может быть относительной или абсолютной.

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

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

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

Абсолютная ссылка на ячейку .иди область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.

Абсолютная ссылка может быть создана только при наборе формулы, перед адресом строки и столбца вводится знак доллара - $.

Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.

Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).

Функции в Excel

Более сложные вычисления в таблицах Excel осуществляются с помощью специальных функций (рис. 6). Список категорий функций доступен при выборе команды Функция в меню Вставка (Insert, Function).

Финансовые функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.

Функции Дата и время позволяют работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ .

Рис. 6. Мастер функций

Математические функции выполняют простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, абсолютной величины числа, округление чисел и др.

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

Функции Ссылки и массивы позволяют осуществить поиск данных в списках или таблицах, найти ссылку на ячейку в массиве. Например, для поиска значе н ия в строке таблицы используется функция ГПР .

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

Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ .

Логические функци и предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.

Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ .

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

Трехмерные ссылки в Excel

Каждая ячейка в Excel имеет две координаты - номер строки и номер столбца. Поскольку рабочая книга состоит из нескольких рабочих листов (по умолчанию из трех), то ячейке можно присвоить третью координату - номер листа, которая представляет собой третье измерение, что позволяет говорить об объемных таблицах.

Использование рабочих книг с несколькими листами обеспечивает следующие преимущества:

гибкость в оформлении рабочих листов;

возможность консолидации (объединения) данных.

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

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

Трехмерные ссылки

Excel использует ярлыки листов для определения адреса листа. Имя каждого листа последовательно увеличивается на единицу, начиная с Лист! (Sheetl). Трехмерные формулы включают области, котррые охватывают более одного листа, т. е. определяют диапазон листов, содержащих данные.

Лист!: Лист2!В11
Квартал!: Квартал4!А1:А10

Создание диаграмм в Excel

Чтобы создать диаграмму, необходимо сначала ввести данные для диаграммы на лист Excel. Выделите данные, а затем поэтапный процесс выбора типа диаграммы и различных параметров диаграммы для диаграммы с помощью мастера диаграмм.

В меню Вставка щелкните диаграмму , чтобы запустить мастер диаграмм.

В диалоговом окне Тип диаграммы мастер диаграмм - шаг 1 из 4 - укажите тип диаграммы, который требуется использовать для диаграммы. Для этого выполните одно из следующих действий.

Перейдите на вкладку Стандартные типы . Пример, как будет выглядеть выбирается один из стандартных типах диаграмм, предоставляемых Excel данных выберите тип диаграммы, выберите подтип диаграммы, который необходимо просмотреть и затем нажмите кнопку Просмотр результата.
Чтобы выбрать тип диаграммы, выберите тип диаграммы, выберите подтип диаграммы, который требуется и нажмите кнопку Далее.
Перейдите на вкладку Нестандартные . Чтобы выбрать встроенный нестандартный тип диаграммы или создать свой собственный тип диаграммы, нажмите кнопку пользовательских или встроенных. Выберите нужный тип диаграммы и нажмите кнопку Далее.

В диалоговом окне Мастер диаграмм - шаг 2 из 4 - источник данных диаграммы можно указать диапазон данных и как ряд отображается в диаграмме.
Если диаграмма отображается должным образом, нажмите кнопку Далее.
Если необходимо изменить диапазон данных или ряд для диаграммы, выполните одно из следующих действий и нажмите кнопку Далее.

На вкладке Диапазон данных щелкните поле Диапазон данных , а затем выделите ячейки, которые требуется, чтобы на листе.
Укажите серии, отображаются в столбцах или строках.
На вкладке ряд добавить и удалить ряд или изменить диапазон, содержащий имена и значения для каждого ряда диаграммы.

В диалоговом окне Параметры диаграммы мастер диаграмм - шаг 3 из 4 - можно изменить внешний вид диаграммы более при выборе любых параметров диаграммы на шести вкладках. При изменении этих параметров, просмотрите Образец диаграммы убедитесь в том, что диаграмма выглядит должным образом.
После завершения выбора нужные параметры диаграммы, нажмите кнопку Далее.

На вкладке заголовки можно добавить или изменить названия диаграммы и осей.
На вкладке оси можно установить параметры отображения основные оси диаграммы.
На вкладке « сетки » можно отобразить или скрыть линии сетки.
На вкладке Легенда можно добавить легенду в диаграмму.
На вкладке Подписи данных в диаграмму можно добавить подписи данных.
На вкладке « Таблица данных » можно отобразить или скрыть таблицы данных.

В диалоговом окне Мастер диаграмм - шаг 4 из 4: размещение диаграммы выберите местоположение, в котором следует разместить на диаграмме, выполнив одно из следующих:

Щелкните на новом листе для отображения диаграммы на отдельном листе.
Выберите как объект для отображения на диаграмме в виде объекта в таблице.

Нажмите кнопку Готово.

Изменение внешнего вида диаграммы

Тип диаграммы: это, можно изменить тип диаграммы.
Источник данных: это, можно изменить диапазон или диапазоны листа для каждого ряда на диаграмме.
Параметры диаграммы: это, можно задать параметры диаграммы.
Расположение: это, можно изменить расположение, в котором отображается диаграмма.

После создания диаграммы можно изменить любые параметры диаграммы, чтобы он выглядел должным образом.