Кафедра информационных технологий в бизнесе

Информационные технологии в офисной работе

Часть 3

Разработка и анализ экономических документов c помощью табличного процессора Excel 2007

Учебно-методическое пособие

Пермь 2011

Информационные технологии в офисной работе. Часть 3. Разработка и анализ экономических документов и помощью табличного процессора Excel 2007. Учебно-методическое пособие., Пермь: ГУ ВШЭ ПФ, 2011. 82 с.

 

Составитель: Лебедев Валерий Викторович

 

Учебно-методическое пособие составлено в соответствии с Государственным образовательным стандартом и содержит описание современных информационных технологий, позволяющих создавать сложные документы экономического характера. Эти документы могут включать в себя различные типы данных (текстовые, графические, рассчитываемые). Пособие содержит серию лабораторных занятий, позволяющих исследовать возможности современных табличных процессоров по ведению экономических расчетов и анализу полученных результатов. Пособие предназначено для студентов и преподавателей ГУ ВШЭ ПФ, заинтересованных в использовании эффективных компьютерных технологий в своей профессиональной деятельности.

В пособие включены материалы, подготовленные Викентьевой О.Л., Дерябиным А.И., Лебедевым В.В. и Шестаковой Л.В.

 

 

ВВЕДЕНИЕ

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

Для разработки электронных документов используются современные пакеты прикладных программ, в частности пакет Microsoft Office 2007. Система управления электронными таблицами является самой популярной программой пакета – это универсальная система обработки данных. В отличие от текстового процессора табличный процессор позволяет создать документ, который содержит не только текст и графику, но и формулы практически любой степени сложности, рассчитываемые системой. Результаты расчетов автоматически изменяются в зависимости от изменения исходных данных. Программа Excel 2007 снабжена мощным набором специальных средств, предназначенных для организации анализа результатов расчета. Результаты такого анализа могут служить основой для принятия управленческого решения.

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

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

- постановка задачи на содержательном уровне;

- выбор аппаратных и программных средств для реализации документа;

- создание дружественной информационной среды;

- ввод, редактирование и форматирование документа;

- расчет и анализ результатов расчета;

- принятие управленческого решения и его оформление;

- сохранение и рассылка документа.

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

 

 

Создание информационной среды для решения пользовательской задачи и ввод исходных данных

Особенности работы внутри главного окна Excel 2007

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

Запуск Excel

Для запуска программы может быть использована традиционная команда Пуск, Все программы, Microsoft Office, Microsoft Office Excel 2007. В появившемся окне Excel создает новый документ - рабочую книгу, содержащую по умолчанию три пустых рабочих листа (рис.1.1).

Строка заголовка

Кнопка Office

Панель быстрого доступа

 

Ярлыки рабочих листов

Область документа

Лента

Поле имени

Строка формул

Ярлыки режимов просмотра

Элементы управления масштабом

Рисунок 1.1. Окно программы с открытым документом Книга 1

Существуют и другие способы запуска программы, например список Недавние документы (Последние элементы) в меню Пуск. При многократном запуске приложения Excel операционная система Windows автоматически поместит ярлык для его вызова в список наиболее часто применяемых приложений. Это позволит начать работу с программой более короткой командой Пуск, Microsoft Office Excel 2007.

Если вы хотите, чтобы ярлык для вызова Excel всегда присутствовал в составе меню Пуск, то подайте следующую команду: Пуск, Все программы, Microsoft Office, Microsoft Office Excel2007. Откройте контекстное меню щелчком правой кнопки по пункту Microsoft Office Excel2007 и выберите в нем соответствующий пункт – Закрепить в меню Пуск. С помощью контекстного меню можно поместить этот ярлык и на панели быстрого запуска, выбрав в контекстном меню команду Добавить на панель быстрого запуска. При использовании различных операционных систем перечисленные команды могут отличаться от приведенных команд, однако эти отличия будут несущественными.

1.1.2. Главное окно Excel 2007

Основными элементами главного окна являются (рис. 1.1) следующие элементы:

1. Кнопка Office. Эта кнопка открывает меню Office (рис. 1.2), в которое входят команды, которые в предыдущих версиях программы располагались в меню Файл.

Рисунок 1.2. Меню кнопки Office

2. Панель быстрого доступа. Первоначально эта панель содержит три команды: Сохранить, Отменить последнее действие, Вернуть отмененного действия. Количество команд может быть увеличено за счет команд раскрывающегося списка (рис. 1.3). Эта панель может быть помещена под ленту.

Рисунок 1.3. Меню для настройки панели быстрого доступа

3. Лента. Инструментальная лента представляет собой линейку вкладок: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид. Каждая вкладка содержит набор управляющих функций, связанных друг с другом тематически. Принципы работы ленты аналогичны принципам работы с ней в Word 2007.

4. Поле имени. Это поле (рис.1.1) отображает адрес активной ячейки таблицы.

5. Строка формул служит для введения и редактирования данных и формул.

6. Область документа содержит электронную таблицу.

7. Ярлыки рабочих листов. Количество рабочих листов, выведенных на экран, и их характеристики могут быть изменены с помощью контекстного меню (рис. 1.4).

Рисунок 1.4. Контекстное меню для работы с рабочими листами

8. Ярлыки режимов просмотра служат для изменения вида рабочего листа таблицы.

9. Элементы управления масштабом позволяют изменить объем листа, выводимого на экран.

10. Панели задач. Excel 2007, в отличие от предыдущих версий, при запуске программы панель задач по умолчанию не выводит. Панель задач выводится на экран автоматически, в ответ на произведенные действия. Однако любую доступную (относящуюся к выполняемой задаче) панель можно, при необходимости, вывести вручную. Excel 2007 позволяет видеть несколько панелей задач одновременно. Примерами панелей задач, с которыми можно работать в Excel, могут быть следующие панели:

· Выбор рисунка SmartArt (рис.1.5). На экран она выводится следующей цепочкой действий: вкладка Вставка, в группе Иллюстрации кнопка SmartArt.

Рисунок 1.5. Панель задач Выбор рисунка SmartArt

· Справочные материалы. На экран о на выводится следующей цепочкой действий: вкладка Рецензирование, в группе Правописание кнопка Справочники.

· Панель задач Буфер обмены на экран она выводится следующей цепочкой действий: вкладка Главная, в группе Буфер обмена одноименная кнопка.

 

 

Рабочие листы и рабочие книги

Документ в Excel называется Книгой. Книга состоит из рабочих листов. Каждый рабочий лист может содержать 16 384 столбца и 1 048 576 строк (256 х 65 536 в более ранних версиях). Таким образом, рабочий лист допускает использование 17 179 869 184 ячеек (против 16 777 216 в ранних версиях). Для ссылки на определенную ячейку используется адрес ячейки формата A1, содержащий идентификатор столбца и идентификатор строки. Столбцы обозначаются буквами от A до XFD, строки цифрами от 1 до 1 048 576. Таким образом, первая ячейка имеет адрес A1, а последняя XFD1048576.

Excel допускает и альтернативную систему обозначений: вместо формата A1 можно использовать формат R1C1. Здесь буква R и последующая цифра указывает порядковый номер строки, а буква C и цифра – порядковый номер столбца. Так адрес ячейки B1 в формате R1C1 равен R1C2. Перейти к альтернативной форме можно следующим образом: меню Office, кнопка Параметры Excel, категория Формулы, флажок Стиль ссылок R1C1, ОК.

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

 

Открытие ранее созданных рабочих книг

Excel предлагает насколько способов доступа к рабочей книге, созданной ранее:

1. С помощью кнопки вызова меню Office. Меню, вызываемое кнопкой Office, содержит перечень названий рабочих книг, которые загружались в Excel последними (рис. 1.6). По мере использования других рабочих книг состав списка меняется: старые элементы заменяются последними отрывающимися. Обратите внимание на значок с изображением шпильки справа от имени каждого элемента. Если вы хотите обеспечить постоянное присутствие в перечне какой-либо рабочей книги, нажмите на кнопку-шпильку. Шпилька станет «воткнутой». Повторное нажатие на кнопку вернет ее в прежний вид. По умолчанию последний список состоит из девяти названий. Для изменения их количества достаточно: открыть меню Office, нажать кнопку Параметры Excel, на левой панели выбрать вкладку Дополнительно, в параметрах Экран выбрать нужное число документов в списке последних файлов.

Рисунок 1.6. Выберите нужную книгу в списке последних документов

2. С помощью диалогового окна Открытие документа. Если в списке последних документов нет нужной вам книги, откройте диалоговое окно Открытие документа (рис. 1.7):

· В меню Office выберите пункт Открыть.

· Перейдите к папке, которая содержит нужную рабочую книгу. Для этого целесообразно использовать панель ссылок: Документы, Недавно измененные, Рабочий стол и т.д.

· Откройте найденную папку и выберите нужную рабочую книгу, нажмите на кнопку Открыть. Кнопка Открыть для чтения открывает файл, но лишает вас возможности его изменить. Кнопка Открыть как копию создает новую рабочую книгу, кнопка Открыть в обозревателе открывает файл в Internet Explorer и доступна только для HTML файлов, кнопка Открыть и восстановить применяется только для тех файлов, которые Excel не удалось открыть.

Рисунок 1.7. Диалоговое окно Открытие документа позволяет открыть любой документ

3. С рабочего стола или из Проводника.

4. Преобразование рабочей книги в документ формата Excel 2007. Документ, созданный ранними версиями программы (Excel 97-2003), предполагает расширение.xls (Excel 2007 -.xlsx). Когда вы открываете рабочую книгу с расширением.xls, в строке названия появляются слова Режим совместимости (рис.1.8). Они напоминают вам, что формат файла нужно преобразовать в файл Excel 2007:

· В меню Office выберите пункт Преобразовать. Появится диалоговое окно Microsoft Office Excel. Установите флажок Больше не задавать вопросы о преобразовании книг, если вы хотите, чтобы это окно больше не выводилось. Нажмите кнопку ОК.

· Excel преобразует рабочую книгу в новый формат и выводит соответствующее диалоговое окно в котором следует нажать кнопку Да.

 

Кнопки для перехода от одного листа к другому

Рисунок 1.8. Средства для преобразования форматов

 

Перемещение в рабочих книгах и рабочих листах, выделение объектов

Для перехода от одного рабочего листа к другому удобно использовать кнопки, расположенные в нижней строке окна документа (рис. 1.8). Переход от одной ячейки к другой осуществляется клавишами управления курсором (← ↑ → ↓). После перемещения на требуемую часть листа необходимо выделить объекты: ячейки и диапазоны ячеек. Диапазоном смежных ячеек называется прямоугольный блок ячеек. Обозначается он адресами первой (левой верхней) и последней (правой нижней) ячеек, разделенных двоеточием. Например, диапазон C4:E7 представляется блоком из 12 ячеек. Диапазон независимых ячеек обозначается адресами ячеек-составляющих, разделенных запятыми, например B3, B5, B7, B9. Для выделения нескольких независимых блоков используется клавиша Ctrl.

Смежные ячейки

Независимые ячейки

Выделение строки

Выделение столбца

Рисунок 1.9. Выделение объектов

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

· Выделить требуемый диапазон (рис.1.10).

· На вкладке Формулы в группе Определенные имена нажать кнопку Присвоить имя. Появится диалоговое окно Создание имени.

· В поле Имя ввести название диапазона.

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

· Убедитесь, что в поле Диапазон указаны соответствующие координаты и нажмите кнопку ОК.

Рисунок 1.10. Средства для задания имени диапазону

Удалить поименованный диапазон можно с помощью Диспетчера имен, который открывается одноименной кнопкой на вкладке Формулы (рис. 1.11).

Рисунок 1.11. Диалоговое окно Диспетчер имен Рисунок 1.12. Средство для выделения диапазона ячеек по имени

Для перехода от одного поименованного диапазона к другому используется диалоговое окно Перейти, которое открывается командой: Главная, Редактирование, Найти и выделить, Перейти (рис. 1.12).

Диалоговое окно Выделение группы ячеек позволяет пометить ячейки, информация в которых удовлетворяет обозначенным критериям (рис.1.13). Активизировать его можно с помощью команды: Главная, Редактирование, Найти и выделить, Выделение группы ячеек. Критерии отбора описаны в табл. 1.

Рисунок 1.13. Средства для отбора ячеек, данные в которых удовлетворяют определенным условиям

Таблица 1. Критерии для выделения группы

Название критерия	Действия при его выборе
Примечание	Выделение ячеек, которые содержат примечания (включена по умолчанию при открытии окна)
Константы	Выделение ячеек, которые содержат постоянные данные (текстовые, числовые или даты). После установки этого переключателя, становятся доступными флажки, расположенные ниже переключателя Формулы.
Формулы	Выделение ячеек, которые содержат формулы. После установки этого переключателя становится возможным дополнительно указать тип результата, который должен появиться в ячейках после вычисления формулы: числовой, текстовый, логический или сообщение об ошибке. Например, установив последний флажок Ошибки, можно обнаружить все ошибочные формулы.
Пустые ячейки	Выделение ячеек, которые не содержат никакой информации. Из них Excel исключает все ячейки, находящиеся правее и ниже последней заполненной ячейки рабочего листа с данными.
Текущая область	Выделение прямоугольного блока ячеек, который содержит активную ячейку
Текущий массив	Выделение массива ячеек, в состав которого входит активная.
Объекты	Выделение объектов, таких как текстовые окна, диаграммы, готовые фигуры и другие.
Отличия по строкам	Выделение ячеек выбранного диапазона, данные в которых отличаются о данных в указанной ячейке. Выделите диапазон, щелкните мышкой по любой ячейке диапазона, удерживая клавишу Ctrl (сделайте активной ячейку с эталонными данными) и установите переключатель Отличия по строкам.
Отличия по столбцам	Совпадают с действиями при использовании предыдущего переключателя
Влияющие ячейки	Выделение ячеек, на которые происходят ссылки в активной ячейке. При установке этого переключателя становятся активными переключатели, помощью которых можно указать характер связи активной и выделяемых ячеек: непосредственной или косвенной. При выборе первого варианта (применяется по умолчанию) будут активизироваться данные только в ячейках, прямо связанных с активной, а при выборе второго – на всех уровнях вложенности.
Зависящие ячейки	Выделение ячеек, в которых присутствует ссылка на активную ячейку.
Последнюю ячейку	Выделение последней, когда-либо используемой ячейки активного рабочего листа
Только видимые ячейки	Выделение видимых (не скрытых) ячеек.
Условные форматы	Выделение ячеек, к которым применено условное форматирование. При выборе этого переключателя становятся активными два переключателя, позволяющие показать, как происходит поиск: либо всех ячеек с условным форматированием (включен переключатель Всех – по умолчанию), либо ячеек с одинаковым условием форматирования (Этих же).
Проверка данных	Выделение ячеек, которые содержат правила проверки наличия ошибочных данных. Будут выделены все ячейки (Всех) или ячейки с одинаковыми правилами (Этих же).

 

Получение справки Excel 2007

Для получения справки достаточно нажать клавишу F1 или кнопку с изображением вопросительного знака в правом верхнем углу окна программы (рис.1.14).

Кнопка Показать оглавление

Рисунок 1.14. Справочная система может работать автономно или подключиться к узлу Office Online

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

Рисунок 1.15. Можно использовать Оглавление или строку поиска

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