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

M S Excel 2000 – табличный процессор, предназначенный для создания и обработки электронных таблиц. Ярлык MS Excel 2000 имеет вид, представленный на рисунке 5.1.

При запуске MS Excel создается новая рабочая книга (Книга1,2,… и т.д.). Книга в MS Excel представляет собой файл, используемый для обработки и хранения данных. Такие файлы имеют расширение – *.xls. В каждом файле *.xls может размещаться одна книга.

Расположить открытые рабочие книги на экране можно четырьмя способами:

• рядом – рабочие книги открываются в маленьких окнах, на которые делится весь экран «плиточным» способом;

• сверху вниз – открытые рабочие книги отображаются в окнах, имеющих вид горизонтальных полос;

• слева направо – открытые рабочие книги отображаются в окнах, имеющих вид вертикальных полос;

• каскадом – рабочие книги (каждая в своем окне) «выкладываются» на экране слоями.

MS Excel позволяет работать с таблицами в двух режимах отображения:

• Обычный. Отображается только текущий рабочий лист. Этот режим используется для решения большинства задач MS Excel, таких как ввод, отбор и форматирование данных и построение диаграмм.

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

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

Табличный процессор MS Excel имеет несколько режимов работы:

• режим готовности – в Строке состояния появляется индикатор Готово;

• режим ввода данных – в Строке состояния появляется индикатор Ввод;

• режим редактирования – отмечается появлением индикатора Правка;

• командный режим (режим команд) – в Строке состояния появляются подсказки;

• запись макроса – производится запись макроса. Макрос содержат программы последовательности команд или действий пользователя.

Р абочее окно MS Excel представляет собой стандартизованную оболочку (рис. 5.2), похожую на оболочку редактора MS Word.

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

Строка состояния выводит информацию о выбранной команде или выполняемой операции. Выделенная команда коротко описывается в левой части строки состояния. Здесь также указывается информация о выполняемой операции, такой, как открытие или сохранение файла, копирование ячеек или запись макроса. Правая часть строки состояния показывает включение ключей CAPS LOCK, SCROLL LOCK или NUM LOCK. Режим работы, в котором находится табличный процессор в каждый конкретный момент, отражается в Строке состояния (рис. 5.2).

Лист представляет собой сетку из строк и столбцов. Каждый рабочий лист имеет имена (Лист1, Лист2, …). Число листов в книге ограничено объемом доступной памяти (по умолчанию — 3 листа). Имена листов отображаются на ярлычках в нижней части окна книги. Если ярлычки листов не отображаются в нижней части экрана, необходимо выбрать команду Параметры в меню Сервис и на вкладке Вид установить флажок Ярлычки листов. Для перехода с одного листа на другой следует щелкнуть мышью на соответствующем ярлычке.

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

Каждая ячейка ЭТ образуется пересечением строки и столбца и имеет свой уникальный адрес или ссылку. Например, ячейка, находящаяся на пересечении столбца В и строки 7, имеет адрес В7. Эти адреса используются при записи формул или ссылках на ячейки. На листе 65536 строк и 256 столбцов. Столбцы могут обозначаться прописными латинскими буквами A, B, C … ZZ или цифрами 1, 2, 3 … 255, 256.

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

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

Присвоение имен имеет ряд преимуществ перед ссылками:

• быстрый переход к новой области выделением нужного имени в списке имен;

• упрощение создания формул (вставка имени из списка вместо указания ссылки);

• наглядность математических формул;

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

В именах допустимы следующие символы, цифры и знаки:

• буквы (русские и латинские);

• _ (подчеркивание);

• . (точка);

• \ (обратный слеш);

• ? (вопросительный знак).

Ограничения в именах:

• нельзя использовать пробелы;

• нельзя начинать имя с цифры;

• нельзя использовать имена похожие на адреса ячеек, например, Х1;

• длина имени должна быть не более 255 символов.

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

ПРИМЕР ИМЕН:

ДоходЗаПериод

Ставка_налога

Можно вводить и изменять данные одновременно на нескольких листах, а также выполнять вычисления на основе данных из нескольких листов.

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

В таблицах используются три типа адресации ячеек:

• относительная;

• абсолютная;

• смешанная.

По умолчанию устанавливается относительный тип адресации.

Р ассмотрим пример (рис. 5.4).

В ячейки С2, С3 записаны числа 5 и 8 соответственно, в ячейки D2, D3 числа 4 и 11 соответственно, а в ячейке A4 формула =C2*D2. При относительной адресации это означает, что содержимое ячейки, находящейся на 2 строки выше, чем А4, и на 2 столбца правее, чем А4, надо умножить на содержимое ячейки, находящейся на 2 строки выше и на 3 столбца правее, чем А4. При копировании формулы из ячейки А4 в ячейку А5 получим в ней следующую формулу: = C3*D3. Таким образом, относительные адреса ячеек D2 и C2 автоматически заменяются на новые адреса D3 и C3.

Абсолютный адрес указывает на единственное положение ячейки. При копировании формул абсолютный адрес сохраняется неизменным. Признаком абсолютного адреса является наличие знака $ перед именем столбца и/или номером строки.

При копировании формулы из ячейки В4 =С2*$D$2, в ячейку В5 получим в ячейке В5 формулу =С3*$D$2, то есть абсолютный адрес ячейки D2 при копировании не меняется, а меняется относительный адрес ячейки С2. Имена ячеек в формулах представляют собой абсолютные ссылки.

При смешанной адресации в адресе одной ячейки используются абсолютный и относительный адрес. Например: В$10, $A10.

Т аким образом, для одной ячейки можно записать следующие адреса:

• C12 – относительный адрес;

• $C$12 – абсолютный адрес;

• $С12, С$12 – смешанные адреса.

Изменение типа ссылки для редактируемого адреса производится с помощью клавиши F4. Например, при последовательных нажатиях клавиши F4 номер ячейки A1 будет записываться как A1, $A$1, A$1 и $A1 (рис. 5.5). В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой – как относительный.

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

MS Excel позволяет создавать связи с другими рабочими листами и другими рабочими книгами трех типов:

• ссылка на другой рабочий лист в формуле связывания с использованием ссылки на лист;

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

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

ПРИМЕР:

П усть цены на изделия приведены в ПрайсЛисте, а расходы на приобретение изделий записаны на листе Калькуляция. Тогда формула в клетке С2, вычисляющая сумму затрат на изделие, будет выглядеть как ПрайсЛист!$B$2*В2 (рис. 5.6).

При сохранении связанных книг сначала сохраняются исходные книги, а затем книги со ссылками.

Для обновления или переадресации связи можно использовать команду Правка – Связи, которая содержит кнопки Обновить, Открыть, Изменить. Команда Связи доступна из файла, содержащего ссылки. Команда Обновить приводит данные в связанных файлах в соответствие с последними изменениями ссылки. Команда Изменить позволяет изменять ссылки.

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