Работа с книгами и листами

 

В табличном процессоре 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” в ячейке появляется результат.

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

С помощью кнопки “Автосуммирование” можно вычислить сумму ячеек несмежных диапазонов таблицы. Для этого необходимо выделить суммируемые диапазоны ячеек, включая, если надо, пустые ячейки справа и (или) снизу и нажать кнопку “Автосуммирование”. Программа подсчитает суммы как по строкам, так и по столбцам.

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

Чтобы получить доступ к какой-либо функции, надо выделить подходящую категорию, затем выбрать в списке “Функция” нужную функцию и, нажав кнопку “Далее”, перейти в следующее диалоговое окно. На следующем этапе вводятся все необходимые аргументы. Аргументы в формуле должны быть заключены в скобки и отделены друг от друга точкой с запятой. Заканчивается ввод функции нажатием кнопки “Готово”. Введенную функцию можно отредактировать, используя для этого “Мастера функций”, или изменить формулу, содержащую функцию, прямо в ячейке.

Обработка и анализ данных

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

Табличный процессор Excel имеет следующие возможности обработки и анализа данных:

· создание списка;

· использование формы данных для ввода и редактирования записей;

· сортировка и фильтрация данных списка;

· вычисление промежуточных и общих итогов;

· использование средств “Подбор параметра” для получения определенного результата.

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

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

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

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

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

 выделить любую ячейку списка или несколько записей, если надо отсортировать только часть списка;

 выбрать команду “Данные, Сортировка”;

 в появившемся диалоговом окне “Сортировка диапазона” для исключения участия в сортировке строки заголовков полей списка выбрать в группе “Идентифицировать поля по” переключатель “Подписям”;

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

 выделить имена полей в раскрывающемся списке “Затем по” и “В последнюю очередь, по” для задания следующих полей для сортировки;

 нажать кнопку ОК. Программа отсортирует данные списка согласно заданному порядку (рис.9).

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

Для фильтрации списка следует:

 выделить любую ячейку списка;

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

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

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

 для использования пользовательского фильтра выделить элемент “Условие”, в появившемся диалоговом окне “Пользовательский автофильтр” сформировать операцию сравнения и нажать кнопку ОК.

Программа отобразит только те записи, которые удовлетворяют выбранным критериям. Чтобы вернуться к исходному состоянию списка необходимо выделить элемент “Все” в раскрывающемся списке.

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

 отсортировать список в первую очередь по выбранному полю;

 выделить любую ячейку списка;

 выбрать команду “Данные, Итоги”;

 в появившемся диалоговом окне “Промежуточные итоги” выбрать столбец, содержащий группы, по которым необходимо подвести итоги, для чего выделить его название в раскрывающемся списке “При каждом изменении в”. Этот должен быть столбец, по которому проводилась сортировка списка на шаге 1;

 выбрать функцию, необходимую для подведения итогов, в раскрывающемся списке “Операции”;

 в списке “Добавить итоги по” выделить названия столбцов, содержащих значения, по которым необходимо подвести итоги, и нажать кнопку “ОК”.

Результат подведения общего итога отразится на рабочем листе.

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

Для скрытия или показа деталей определенной группы строк списка надо нажать кнопку с изображением минуса (плюса) соответственно. Для отображения или показа строк списка определенного уровня надо нажать кнопку с изображением цифры, соответствующей степени детализации отображаемых данных. Для удаления итоговых данных из списка надо выделить любую ячейку списка и выбрать команду “Данные, Итоги” и в появившемся диалоговом окне “Промежуточные расчеты” надо нажать кнопку “Убрать все”.

Программа обладает мощным вычислительным аппаратом для решения задач оптимизации. Одно из таких средств - это “Подбор параметра”, которое позволяет проводить так называемое прогнозирование при решении различных задач.

При использовании “Подбора параметра” надо начать с постановки задачи и ввода на рабочий лист значений известных параметров. В ячейку, значение которой будет варьироваться для достижения заданного результата, надо ввести формулу, которая ссылается на другие ячейки листа. Ячейка, содержащая формулу, называется целевой ячейкой, а ячейки, на которые ссылается формула, - изменяемыми ячейками.

Для использования “Подбора параметра” следует:

 выбрать команду “Сервис, Подбор параметра”;

 в появившемся диалоговом окне “Подбор параметра” ввести ссылку на целевую ячейку в поле ввода “Установить в ячейке”;

 ввести требуемое значение в поле ввода “Значение”;

 ввести ссылку на изменяемую ячейку в поле ввода “Изменяя значение ячейки” и нажать кнопку ОК.

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