Лекция №7

Электронная таблица Excel

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

Ячейка - неделимый элемент таблицы, в котором содержаться данные.

Структура ячейки в электронной таблице Excel имеет несколько уровней:

1. Верхний уровень — это изображение содержимого ячейки, которое выводится на экран. В нем мы видим отформатированные значения, такие как текст, числа или результаты вычисления формул.

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

3. Третий уровень содержит формулу. В зависимости от того какие опции установлены, можно видеть либо значение, вычисленное формулой, либо саму формулу.

4. На четвертом уровне содержится имя ячейки.

5. Пятый уровень заключает в себе комментарий.

Адрес ячейки - определяется названием столбца и номером строки, которые образуют пересечение (например А1).

Блок ячеек - группа последовательных ячеек. Блок ячеек может состоять из одной ячейки, строки, столбца, последовательности частей строк и столбцов (например, В2: С5).

Рабочий лист - содержит отдельную электронную таблицу.

Рабочая книга - совокупность рабочих листов.

2. Типы данных

Символьные (текстовые) данные имеют описательный характер. Могут включать в себя буквы, цифры, знаки препинания и т.д. Для явного указания символьных данных впереди ставиться апостроф ' (например, запись '132 - электронная таблица будет считать текстом).

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

Числовые данные содержат только численные значения. Если вводимое число больше размера клетки, то оно не отображается в соседней пустой ячейки, а автоматически происходит его преобразование в экспоненциальную форму (1, 6Е+5) или происходит забивание клетки знаками — #.

В качестве десятичного разделителя используется запятая (12, 1999).

Формула содержит запись, которая может включать арифметические, логические и другие действия, производимые с данными других ячеек (=А1+В1). Запись формулы всегда начинается со знака равно =.

Функция - небольшая подпрограмма, осуществляющая расчет данных по нескольким формулам (например, функция СРЗНАЧ(В2: D5) рассчитывает сначала сумму данных ячеек В2: D5, а затем делит полученную сумму на общее количество ячеек). Запись функции всегда начинается с символа @.

Даты - числовые данные, отображающие дни, месяцы, годы (например, 12.1999 - декабрь 1999 года).

3. Формат ячеек

Для форматирования ячеек необходимо выполнить команду Формат - Ячейки. Результатом ваших действий будет появление диалогового окна:

Рис. 1. Диалоговое окно Формат ячеек.

Закладка Число позволяет сделать выбор следующих форматов:

Общий - используется для отображения текстовых и числовых значений произвольного типа.

Числовой - отображает числа в нормальном виде с заданной точностью (т.е. задается число цифр после запятой).

Денежный - отображает числа в денежной форме, т.е. с указанием денежной единицы (125, 56 р.).

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

Дата - отображает числовые значения в формате дат. Т.е. если вы введете число 366, то в формате даты это будет 30.12.1900. Дата может отображаться

· в числовой форме (12.04.99),

· с указанием дня и месяца (12 апр.),

· с указанием месяца и года (апр 99),

· с указанием дня, месяца и года (12 Апрель, 1999),

· с указанием дня, месяца, года и времени (12.04.99 14: 40).

Время - отображает числовые значения в формате времени. При указании времени может использоваться абривиатура PM, которая означает, что время указано после обеда (5: 00 РМ - 17.00 ч.).

Процентный - отображает числа в виде процента. При вводе процентов надо учитывать, что проценты определяются, как часть от единицы. Т.е. для того чтобы ввести 85% надо вводить число 0, 85.

Дробный - отображает не целые числа в виде дробей. Т.е. если ввести число 0, 25, то оно отобразится в виде 1/4.

Экспоненциальный - отображает числа в экспоненциальной форме. Т.е. все числа отображаются в формате:

ME-+P

где М - число с целой частью в виде одной цифры;

Е - указатель десятичной экспоненты,

Р - порядок экспоненты.

Например, число 245 будет отображено как 2, 45E+02

Текстовый - отображает числа в текстовом формате. Над такими числами действия производить нельзя.

Дополнительный - предназначен для работы с адресами, номерами телефонов и др.

4. Формулы и функции

4.1 Использование формул

Различают арифметические и логические формулы. В арифметических формулах используются следующие операторы:

+ - сложение;

- - вычитание;

* - умножение;

/ - деление;

^ - возведение в степень

В логических формулах используются операторы сравнения:

= - равно;

< > - не равно;

< - меньше;

> - больше;

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

> = - больше либо равно.

4.2 Использование функций

В электронной таблице представлены следующие виды функций:

· математические - выполняют сложные математические операции (расчет алгоритмов, тригонометрических функций и т.д.);

· статистические - выполняют операции по вычислению параметров случайных величин или их распределений;

· текстовые - выполняют операции над текстовыми строками, например, вычисляют длину строки, производят операции замены текста;

· логические - используются для построения логических выражений;

· финансовые - используются в сложных финансовых расчетах, например, для определения размера ежемесячных выплат для погашения кредита;

· дата и время - используются для операций с датами и временем, например, находят число дней между двумя датами;

· ссылки и массивы - используются при работе с большими массивами ячеек, например, находят данные по указанному условию;

· работа с базой данных - применяются, если таблица используется как база данных;

· проверка свойств и значений - используются для проверки данных, внесенных в ячейки.

4.3 Относительная и абсолютная адресация

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

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

Абсолютный адрес ячейки - это неизменяемый, в процессе копирования или переноса формулы, адрес ячейки. Для указания абсолютного адреса вводится символ $. Различают два типа абсолютной адресации: полная и частичная:

· Полная абсолютная адресация указывается, если при копировании или перемещении, адрес клетки полностью не должен меняться. Для этого символ $ ставится перед именем столбца и номером строки $A$1+$B$1.

· Частичная абсолютная адресация указывается, если при копировании и перемещении не меняется только номер строки или имя столбца A$1+B$1

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

5. Общие принципы работы

5.1 Ввод и редактирование данных

Для ввода данных необходимо установить курсор на требуемую ячейку и с клавиатуры ввести данные. Ввод данных заканчивается нажатием клавиши [Enter].

Для редактирования содержимого ячейки необходимо установить курсор на требуемую ячейку и нажать клавишу [F2] или два раза щелкнуть мышкой. Редактирование данных также заканчивается нажатием клавиши [Enter].

5.2 Построение диаграмм

В электронной таблице Excel можно построить следующие основные типы диаграмм:

· Круговая диаграмма - используется для графической интерпретации одной переменной. Значения этой переменной представляются в диаграмме секторами круга. Может располагаться на плоскости и в пространстве.

· График - изображает каждую переменную в виде ломаной линии или плоскости (в пространстве).

· Гистограмма - изображает каждую переменную в виде столбца. Может располагаться на плоскости и в пространстве.

· Линейчатая - аналогична гистограмме, но располагается по вертикали. Может располагаться на плоскости и в пространстве.

· С областями - изображает каждую переменную в виде площади.

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

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

1. Выделить данные. Если данные находятся не в соседних столбцах, то для выделения можно использовать клавишу [Ctrl].

2. Выполнить команду Вставка - Диаграмма.

3. Выбрать тип диаграммы и вид графика.

4. При необходимости изменить диапазон ячеек с данными.

5. Ввести атрибуты диаграммы (название осей и самого графика, легенду и т.д.).

6. Указать размещение диаграммы (на этом же листе или на новом).

7. Нажать кнопку Готово.

5.3 Работа с макросами

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

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

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

1. Выполнить команду Сервис – Макрос – Начать запись.

2. Указать имя макроса и установить сочетание клавиш, вызывающих макрос.

3. Произвести действия, которые должен выполнять макрос.

4. Для завершения записи нажать кнопку Остановить запись на панели инструментов.