Работа с книгами и листами
В табличном процессоре Microsoft Excel можно работать с четырьмя основными типами документов: электронной таблицей (в Excel ЭТ называют рабочим бланком), рабочей книгой, диаграммой, макротаблицей.
Рабочий бланк служит для организации и анализа данных. Одновременно на нескольких бланках данные можно вводить, править, производить с ними вычисления. В книгу можно вставить листы диаграмм для графического представления данных и модули для создания и хранения макросов, используемых при хранении специальных задач.
Рабочая книга в электронной таблице представляет собой файл, содержащий несколько листов, предназначенных для размещения и обработки данных. Рабочая книга является электронным эквивалентом папки-скоросшивателя. Книга состоит из листов, имена которых выводятся на ярлычках в нижней части экрана. По умолчанию книга открывается с несколькими рабочими листами – Лист 1, Лист 2 и т.д. Число рабочих листов можно уменьшить или увеличить. В книгу можно поместить несколько различных типов документов, например рабочий лист с электронной таблицей, лист диаграмм, лист макросов и т.п.
Электронные таблицы состоят из столбцов и строк. Для обозначения строк используется цифровая нумерация, столбцов – буквенно-цифровые индексы (номера). Количество строк и столбцов в разных ЭТ различно.
Ячейка в электронной таблицепредставляет собой область, определяемую пересечением столбца и строки электронной таблицы, имеющей свой уникальный адрес.
Для перемещения по ячейкам листа используется “мышь” или клавиши перемещения курсора. Ячейка, на которой устанавливается указатель, становится активной. Для просмотра на экране различных участков листа служат полосы прокрутки. Для перехода на другой лист книги следует указать на ярлычок листа, содержащий нужные данные. Если ярлычок нужного листа не виден, то для его вывода используются кнопки прокрутки листов, а затем указывается необходимый ярлычок.
Для изменения количества листов, установленных по умолчанию, следует:
выбрать команду “Параметры” в меню “Сервис”, а затем вкладку “Общие”.
ввести нужное количество листов в поле листов.
При перемещении и копировании листов следует быть осторожным, так как это может повлечь за собой появление неточностей в вычислениях или диаграммах, основанных на данных листа. Например, при размещении листа внутри трехмерного диапазона, на который ссылается формула, результат, возвращаемый формулой, может измениться.
Для перемещения или копирования рабочего листа следует:
открыть книгу, в которую нужно переместить или скопировать листы;
активизировать книгу, содержащую копируемые или перемещаемые листы, и выделить в ней нужные листы;
в меню “Правка” выбрать команду “Переместить/копировать”;
выбрать соответствующую книгу в поле “Переместить выбранные листы в книгу”;
в поле “Перед листом” выбрать лист, перед которым нужно вставить перемещаемые или копируемые листы.
Чтобы только скопировать, а не переместить листы, необходимо установить флажок “Создавать копию”.
Для выбора отдельного листа в книге надо указать ярлычок этого листа. Для выбора двух или нескольких листов следует указать ярлычок первого листа, затем, удерживая клавишу SHIFT, указать ярлычок последнего листа. Для выбора двух или более несмежных листов указать ярлычок первого листа, затем, удерживая клавишу CTRL, указать ярлычки остальных листов.
Перемещать выбранные листы в пределах текущей книги можно, перетаскивая их с помощью мыши вдоль ряда ярлычков листов. Чтобы копировать листы, необходимо нажать и удерживать нажатой клавишу CTRL, переместить листы в нужное положение, затем отпустить кнопку “мыши” и только после этого — клавишу CTRL. Для удаления листов книги необходимо выбрать листы, которые надо удалить, и выбрать команду “Удалить” в меню “Правка”. Для переименования листа двойным нажатием “мыши” выбирается нужный ярлычок листа и вводится новое имя поверх старого.
При работе в интегрированной среде некоторые рабочие листы могут включать в себя сложные форматы и формулы, которые необходимо оставить неизменными или защитить от других пользователей. Можно установить защиту листа и пароль на доступ с помощью команды меню “Сервис/Защита”.
Как и остальные программы интегрированного пакета Microsoft Office, табличный процессор Excel имеет собственную справочную систему, которая служит и средством решения возникающих проблем, и средством обучения работе с программой. Доступ к справочной информации Excel аналогичен описанному в разделе 4.4. Необходимо отметить, что пользование справочной системой не только существенно облегчает работу, но и способствует повышению квалификации пользователя.
Проведение расчетов
В интегрированной среде основной функцией табличного процессора является проведение расчетов. Вычисления в электронной таблице представляют собой процесс, при котором изменение значения в одной ячейке вызывает изменение значений в других ячейках, связанных с первой. В ЭТ можно работать как с отдельными ячейками, так и с группами ячеек, которые образуют блок. Имена ячеек в блоках разделяются двоеточием (:), например, блок А3:В5 включает в себя ячейки А3, А4, А5, В3, В4, В5. С блоками ячеек в основном выполняются операции копирования, удаления, перемещения, вставки и т.п. Адреса используются в формулах как ссылки на определенные клетки. Таким образом, введенные один раз значения можно многократно и в любом месте таблицы, книги использовать без повторного набора. Соответственно, при изменении значения клетки автоматически произойдут изменения в тех формулах, в которых содержатся ссылки на данную клетку.
Технология работы с табличным документом аналогична процедурам подготовки текстовых документов: редактируемый отчет в виде таблицы выводится на экран, и пользователь может в диалоговом режиме вносить в него свои изменения (т.е. редактировать содержимое клеток ЭТ), используя панель управления. Панель управления в электронной таблице представляет собой часть экрана, дающую пользователю информацию об активной ячейке и ее содержимом, меню и режиме работы. Все внесенные изменения сразу же отображаются на экране компьютера.
Пользователь может переносить и копировать содержимое клеток из одного места таблицы в другое, использовать всевозможные шрифты, печатать на принтере необходимое количество экземпляров подготовленного документа (таблицы).
В клетки ЭТ могут быть введены текст, цифры и формулы. Во всех табличных процессорах существуют синтаксические соглашения, позволяющие отличать формульно-цифровую информацию от текстовой, которых должен придерживаться пользователь.
Формула – это выражение, состоящее из числовых величин и арифметических операций. Кроме числовых величин, в формулу могут входить в качестве аргументов адреса ячеек, функции и другие формулы. Пример формулы: =А5/В8*12. В ячейке, в которой находится формула, виден только результат вычислений, саму формулу можно увидеть в строке ввода, когда данная ячейка станет активной.
Создать формулу можно двумя способами: непосредственным вводом с клавиатуры и указанием ячеек. Второй способ значительно уменьшает количество ошибок, вносимых пользователем при написании формул.
Для создания формулы посредством ввода в ячейку чисел, ссылок на ячейки и математических операций необходимо:
выделить ячейку, в которую вводится фрмула;
ввести знак (=) или (+), с которого должна начинаться любая формула в табличном процессоре;
ввести необходимые символы математических операций и ссылки на ячейки, над которыми проводятся вычисления;
нажать кнопку с изображением галочки в строке формул. В выделенной (активной) ячейке отобразится результат вычислений.
Для создания формул указанием ячеек с помощью мыши необходимо:
выделить ячейку, в которую надо ввести формулу;
ввести в эту ячейку знак (=);
щелкнуть “мышью” по ячейке, ссылку на которую необходимо ввести в формулу;
ввести знак необходимой математической операции;
щелкнуть “мышью” по следующей ячейке, ссылку на которую необходимо ввести в формулу;
повторить аналогичные действия, необходимые для полного ввода формулы, по окончании ввода нажать кнопку с изображением галочки в строке формул.
Используемые в формулах ссылки на ячейки бывают абсолютные, относительные и смешанные. Также можно использовать ссылки на другие рабочие листы и книги. В формулах чаще используются относительные ссылки, которые изменяются в зависимости от того, в какой строке и столбце находится формула. При копировании формул относительные ссылки преобразуются так, чтобы соответствовать новому расположению формулы.
Применение абсолютных ссылок связано с тем, что иногда надо использовать ссылку на конкретную ячейку, независимо от того, где находится сама формула. Чтобы определить абсолютную ссылку на ячейку, столбец или строку, надо перед соответствующей ссылкой поставить знак доллара ($). Например: $A$1, $A:$C, $2:$7.
Смешанные ссылки являются комбинацией абсолютных и относительных ссылок. Например: при копировании формулы, содержащей ссылку $D4, номер строки изменится в зависимости от нового положения формулы, а номер столбца не изменится.
Для изменения типа существующей ссылки надо выделить ячейку, содержащую формулу, поместить указатель в соответствующую позицию в строке формул и нажать клавишу F4 несколько раз, чтобы выбрать необходимый тип ссылки.
Для ввода ссылки на ячейки другого рабочего листа надо поместить название листа и восклицательный знак (!) перед ссылкой на ячейку. Например, ссылка на ячейку А1 листа 2 выглядит следующим образом: Лист2!А1.
С помощью формул можно провести некоторые вычисления, выполнить определенные действия над текстом, сравнить два значения и провести операции над ссылками. При вычислениях можно использовать встроенные функции и обычные арифметические операции.
С помощью текстовых операций можно провести объединение текстовых строк или значений различных ячеек. Текстовые строки при этом должны быть заключены в двойные кавычки. Например, формула = “Сумма:” В4 возвращает текстовое значение Сумма: 340, если ячейка В4 содержит значение 340.
Адресные операции позволяют в формулах ссылаться на различные группы ячеек. Например, формула =Сумм (В3:С4) возвращает (вычисляет) сумму значений ячеек диапазона В3:С4. При выполнении вычислений надо выдерживать определенный порядок выполнения операций, предусмотренный программой. Порядок выполнения операций можно изменить, заключив часть формулы в скобки. Можно создать формулу, использующую для вычислений значения даты или время. При этом их надо заключить в двойные кавычки и вводить в формате, который распознает программа.
При возникновении ошибки в формуле в ячейке отображается определенное значение ошибки. Это позволяет определить причину возникновения ошибки и оперативно ее исправить.
Программа Excel имеет дополнительные средства проверки, позволяющие:
отслеживать влияющие ячейки;
отслеживать зависимые ячейки;
производить поиск источников ошибок.
Такие средства помогают анализировать созданные формулы и предотвращать появление ошибок. Отслеживание влияющих ячеек позволяет определить все ячейки, на которые ссылается формула. Отслеживание зависимых ячеек позволяет определить все ячейки, содержащие ссылки на текущую ячейку.
Для применения средств проверки используются команды меню “Сервис, Зависимости”. Чтобы найти источник ошибки в конкретной формуле необходимо:
выделить ячейку, содержащую формулу, вернувшую значение ошибки;
выбрать команду меню “Сервис, Зависимости, Источник ошибки”. Зависимые и влияющие ячейки соединяются стрелками;
проанализировать значения влияющих ячеек, которые указаны стрелками, ведущими к ячейке, содержащей формулу. Это поможет определить причину возникновения ошибки (рис.7).
В результате работы с формулами возникает потребность в описании данных, находящихся в ячейках, на которые ссылается формула. Например, формула: =С5-А4 ничего не говорит о данных, участвующих в вычислениях. Можно назначить имя ячейке или диапазону, чтобы описать данные. Например, по формуле: +Сумма_дохода – Сумма_расхода можно сразу определить, какие данные использует формула.
Для присвоения имени ячейке или диапазону необходимо:
выделить ячейку или диапазон, которому присваивается имя;
щелкнуть мышью по полю имен, расположенному в левой части строки формул;
ввести любое имя, которое надо присвоить ячейке или диапазону, и нажать клавишу “Enter”.
Достаточно нажать на кнопку раскрывающегося списка, находящуюся рядом с полем имен в строке формул, в списке отобразятся все имена, назначенные ячейкам рабочей книги. После назначения имени ячейке или диапазону можно вставлять имена в формулы, для этого необходимо:
выделить ячейку, в которую вводится формула;
ввести знак равенства (=), чтобы создать формулу;
выбрать команду “Вставка, Имя, Вставить”;
в появившемся диалоговом окне “Вставка имени” выделить в списке “Имя” имя, которое надо вставить, и нажать кнопку ОК;
ввести оставшуюся часть формулы и нажать кнопку с изображением галочки в строке формул. В ячейке отобразится результат вычисления формулы.
Иногда в создаваемую таблицу надо включить сложные вычисления. Для этого используют функции. Функции представляют собой запрограммированные формулы, позволяющие проводить часто встречающиеся последовательности вычислений. Например, функция автосуммирования может быть представлена следующим образом: =СУММ(А1:А4).
Формула, использующая любую функцию, состоит из знака равенства, имени функции и одного или нескольких аргументов. Аргумент — это число или ссылка на ячейки, используемые в вычислениях. Справочная система программы содержит описание всех функций рабочего листа. В описании функций обязательные параметры выделены полужирным шрифтом.
Для ввода функции существует два способа: ввод вручную, использование кнопки “Автосуммирование” и использование “Мастера функций”, который управляет пошаговым построением функции.
Для ввода функции вручную набирается знак равенства в ячейке, куда вводится формула, затем имя функции, например, СУММ и открывающая скобка. Затем указывается в качестве аргумента ячейка или диапазон с помощью выделения его с мышью, и вводится закрывающая скобка. После нажатия клавиши “Enter” в ячейке появляется результат.
Для вычисления суммы значений в ячейках диапазона строки или столбца созданной таблицы с помощью кнопки “Автосуммирование” на стандартной панели инструментов необходимо выделить ячейку, смежную с суммируемым диапазоном, и нажать кнопку “Автосуммирование”. В выделенной (активной) ячейке автоматически введется формула СУММ и выделится диапазон с данными в строке слева или столбце сверху от активной ячейки.
С помощью кнопки “Автосуммирование” можно вычислить сумму ячеек несмежных диапазонов таблицы. Для этого необходимо