Учебное пособие ''Табличный редактор Microsoft Excel'' (версий 95, 97), КАЗАНСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ, Индейкин В.В., 1999 год.

Министерство общего и профессионального образования Российской Федерации
              Казанский государственный технический университет

                           им. А.Н. Туполева (КАИ)



                     Табличный редактор Microsoft Excel

                               Учебное пособие



                                                               Индейкин В.В.



                               Издание первое



     Данное пособие предназначено для изучения базового курса  по  Microsoft
Excel.  Дано  понятие  табличного  редактора,  его  возможности  и   области
применения.  Рассмотрены  логика  табличного  редактора   и   вычисления   в
Microsoft Excel. Показана работа с относительными  и  абсолютными  ссылками.
Уделено внимание созданию серий и построению диаграмм. Частично  рассмотрены
возможности работы  с  базами  данных  в  Microsoft  Excel.  Также  частично
рассмотрены специальные  возможности:  автоформат,  поиск  решения  (решения
уравнений),  создание  сценариев  и  отчетов  по  ним,  создание  макроса  и
активной кнопки.
       Данное   пособие   предназначено   для   студентов   обучающихся   по
специальностям   «Авиационные    приборы    и    измерительно-вычислительные
комплексы» и «Экономика и управление производством».
                                 Оглавление
1.    Введение   6
  1.1.      Понятие табличного редактора      6
  1.2.      Особенности Microsoft® EXCEL      6
   1.2.1.  Ключевые преимущества  7
   1.2.2.  Что дает Microsoft Excel    7
   1.2.3.  Требования к конфигурации компьютера    9
  1.3.      Возможности      9
   1.3.1.  Интеграция в Microsoft Office     11
   1.3.2.  Мастера     11
   1.3.3.  Специальные возможности     11
   1.3.4.  Программирование в Exсel    12
  1.4.      Области применения     12
2.    Общие понятия    13
  2.1.      Логика табличного редактора 13
  2.2.      Вид и основные понятия 13
   2.2.1.  Рабочая книга и рабочий лист.     13
   2.2.2.  Строка состояния 15
   2.2.3.  Основные правила 15
   2.2.4.  Ячейка      16
  2.3.      Окна Excel 17
  2.4.      Ввод и редактирование данных      22
  2.5.      Меню и пиктограммы     24
   2.5.1.  Панели инструментов    24
  2.6.      Формат данных    28
3.    Вычисления в Excel. Формулы и функции  31
  3.1.      Формулы    31
  3.2.      Использование ссылок и имен.      31
  3.3.      Перемещение и копирование формул. Относительные и абсолютные
  ссылки.   33
  3.4.      Функции    34
   3.4.1.  Понятие функции  34
   3.4.2.  Типы функций     35
   3.4.3.  Мастер функций   36
4.    Серии и диаграммы      39
  4.1.      Серии 39
   4.1.1.  Прогрессия  39
   4.1.2.  Создание серий   40
  4.2.      Диаграммы и графики    42
   4.2.1.  Типы диаграмм    42
   4.2.2.  Построение диаграмм    42
   4.2.3.  Редактирование диаграмм     47
5.    Работа со Списками и Базы данных в Excel     48
   5.1.1.  Формирование списка    49
  5.2.      Работа с командой Форма     49
  5.3.      Поиск в базе данных    50
  5.4.      Сортировка базы данных 51
  5.5.      Фильтрация данных в списке  52
  5.6.      Подведении итогов в базе данных   53
   5.6.1.  Вставка промежуточных итогов      55
   5.6.2.  Создание диаграммы закупочных цен 57
6.    Некоторые специальные возможности 60
  6.1.      Автоформат 60
  6.2.      Решения уравнений      60
  6.3.      Создание сценариев     61
  6.4.      Отчет по сценариям.    63
  6.5.      Создание макроса 63
  6.6.      Создание активной кнопки    64
   6.6.1.  Excel 95    64
   6.6.2.  Excel 97    64
7.    Некоторые особенности (для углубленного изучения)  66
  7.1.      Полезные советы при работе с Excel 95 и Excel 97   66
   7.1.1.  Создание Web-страницы  66
   7.1.2.  Использование Web-запросов  66
   7.1.3.  Сохранение файла Microsoft Excel 97 сразу в двух форматах     66
   7.1.4.  Связь надписи с ячейкой электронной таблицы  67
   7.1.5.  Блокировка доступа к личным ячейкам электронной таблицы  67
   7.1.6.  "Повышение" точности вычислений формул  68
   7.1.7.  Перевод строки в ячейке таблицы   68
   7.1.8.  Локальное имя (использование одного и того же имени на разных
   листах) 68
   7.1.9.  Отображение чисел с лидирующими нулями  68
   7.1.10. Использование специальных символов в формате 68
   7.1.11. Удалении ячеек рабочего листа без «порчи» ссылок на удаленные
   ячейки  68
8.    СпрАвочный материал    69
  8.1.      Функциональные и «горячие» клавиши     69
   8.1.1.  Функциональные клавиши 69
   8.1.2.  «Горячие» клавиши      70
  8.2.      Некоторые функции в EXCEL   73
   8.2.1.  Математические функции 73
   8.2.2.  Как к дате добавлять месяцы 74
Список литературы      75


Как устроено это пособие
      Настоящее пособие может  служить  одновременно  руководством,  учебным
пособием и исчерпывающим справочником по Microsoft Excel. Оно было  создано,
с целью дать представление об Excel его  возможностях  и  особенностях,  при
этом предполагается, что читатель умеет работать в Microsoft Windows.
      В данном пособие описывается две  версии  Microsoft  Excel  95  и  97.
Второе издание данного пособия будет полностью  посвящено  версии  Microsoft
Excel 97 или Microsoft Excel 2000.
Соглашения
       В  этой  книге  комбинация  клавиш,  разделенная  знаком  +   (плюс),
например, Ctrl+A означает:  Нажмите  первую  клавишу,  и,  не  отпуская  ее,
нажмите вторую, т. е. Ctrl+A значит: Нажмите клавишу Ctrl,  и,  не  отпуская
ее, нажмите клавишу А. Если же  две  клавиши  разделены  запятой,  например,
Alt, F то это, значит Нажмите и  отпустите  первую  клавишу,  затем  нажмите
вторую.
      Вместо многословных предложений вроде: «Раскройте меню Файл,  и  затем
выберите команду Печать», принято такое написание: «Выберите из меню Файл  |
Печать».
      Адреса ячеек и формулы записаны в следующем виде: V5.
      Обратите внимание: как и  большинство  приложений  Windows,  Microsoft
Excel  использует  символы  разделителей  (десятичные,  разрядов,   даты   и
времени), которые указаны в разделе Стандарты | Панели Управления Windows.

Введение


1 Понятие табличного редактора

      Табличные редакторы (иногда их называют также электронные таблицы)  на
сегодняшний день, одни  из  самых  распространенных  программных  продуктов,
используемые во всем мире. Они без специальных навыков  позволяют  создавать
достаточно  сложные  приложения,  которые  удовлетворяют  до  90%   запросов
средних пользователей.
      Табличные редакторы появились практически  одновременно  с  появлением
персональных компьютеров, когда появилось  много  простых  пользователей  не
знакомых  с  основами   программирования.   Первым   табличным   редактором,
получившим широкое распространение, стал Lotus 1-2-3, ставший стандартом де-
факто для табличных редакторов:
   1. Структура таблицы (пересечения строк и столбцов создают  ячейки,  куда
      заносятся данные);
   2. Стандартный набор математических и бухгалтерских[1] функций;
   3. Возможности сортировки данных;
   4. Наличие средств визуального отображения данных (диаграмм).
      В  СССР  получили  широкое  распространение  два  табличных  редактора
SuperCalc и Quattro Pro. С появлением Microsoft® Windows  и  его  приложений
стандартом де-факто стал табличный редактор Microsoft® Excel.

2 Особенности Microsoft® EXCEL

      Microsoft Excel  -  средство  для  работы  с  электронными  таблицами,
намного превышающее по своим  возможностям  существующие  редакторы  таблиц,
первая версия данного продукта была  разработана  фирмой  Microsoft  в  1985
году.  Microsoft  Excel  -  это  простое  и  удобное  средство,  позволяющее
проанализировать данные и, при необходимости, проинформировать о  результате
заинтересованную аудиторию, используя Internet. Microsoft® Excel  разработан
фирмой Microsoft, и является на сегодняшний день самым популярным  табличным
редактором в мире. Кроме стандартных  возможностей  его  отличает  следующие
возможности, он  выводит  на  поверхность  центральные  функции  электронных
таблиц и делает их более доступными для всех пользователей.  Для  облегчения
работы   пользователя   упрощены   основные   функции,   создание    формул,
форматирование, печать и построение графиков.

1 Ключевые преимущества

   5. Эффективный анализ и обработка данных;
   6. Богатые средства форматирования и отображения данных;
   7. Наглядная печать;
   8. Совместное использование данных и работа над документами;
   9. Обмен данными и информацией через Internet и внутренние Intranet-сети.

2 Что дает Microsoft Excel


1 Эффективный анализ и обработка данных

  10. Быстрый и эффективный анализ, удобные средства для  работы  с  данными
      (мастер сводных таблиц позволяет быстро обрабатывать  большие  массивы
      данных и получать итоговые результаты в удобном виде);
  11. Механизм автокоррекции формул автоматически  распознает  и  исправляет
      ошибки при введении формул.  Microsoft  Excel  умеет  распознавать  15
      наиболее распространенных ошибок, которые  допускаются  пользователями
      при  вводе  формул  в  ячейку.  Например,  автоматически  исправляются
      ошибки, связанные с неправильными ссылками, полученными  в  результате
      перемещения  ячеек.  Введенный  по  ошибке  символ  "х"  автоматически
      преобразовывается в знак умножения и т.д. Естественно, при этом  Excel
      сначала спрашивает пользователя, нужно ли производить исправление;
  12. Использование естественного языка при написании формул;
  13. Проведение различных  вычислений  с  использованием  мощного  аппарата
      функций и формул;
  14. Исследование влияния различных факторов на данные;
  15. Решение задач оптимизации;
  16. Получение выборки данных, удовлетворяющих определенным критериям;
  17. Построение графиков и диаграмм;
  18. Статистический анализ данных.  В  Excel  для  Windows  есть  настолько
      мощный аппарат математической статистики,  что  вы  можете  заниматься
      статистическим моделированием.

2 Богатые средства форматирования и отображения данных

  19.  Средства  форматирования  делают  оформление  таблиц  более  ярким  и
      понятным (возможности слияния ячеек  в  электронной  таблице,  поворот
      текста в ячейке на любой угол, начертание текста в ячейке с отступом);
  20. Мастер создания диаграмм  позволяет  сделать  представление  данных  в
      таблицах более наглядным  (более  удобный  и  мощный  мастер  создания
      диаграмм, кроме стандартных, новые типы диаграмм -  диаграммы  в  виде
      круглых столбиков, тетраэдров, в виде "бубликов" и др.).

3 Наглядная печать

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

4 Совместное использование данных и работа над документами

  22. Теперь для  пользователей  Microsoft  Excel  доступен  режим  реальной
      многопользовательской работы.  Несколько  разных  пользователей  могут
      одновременно работать с одной и той же таблицей и даже документировать
      внесенные изменения. Аналогично режиму исправлений в  Microsoft  Word,
      теперь можно оставлять комментарии о том, кто и когда внес изменения в
      данную ячейку

5 Обмен данными и работа в Internet

  23. Возможность использовать самые  свежие  данные.  С  помощью  Microsoft
      Excel Вы можете получать их  в  виде  электронных  таблиц  прямо  Web-
      серверов в Internet.  Microsoft  Excel  содержит  встроенные  функции,
      позволяющие легко помещать на Web-сервер документы, созданные в  среде
      Microsoft Excel. К их числу относятся: мастер сохранения  документа  в
      формате HTML,  средство  просмотра  содержимого  документов  Microsoft
      Excel для пользователей,  не  работающих  с  этим  приложением  и  ряд
      других;
  24.   Вы   можете   использовать   встроенный   Internet   Assistant   для
      преобразования таблицы в формат  HTML  и  публикации  на  Web-сервере.
      Microsoft Excel позволяет  импортировать  данные  из  HTML-документов,
      найденных на Web-сервере, восстанавливая при этом формат и  оформление
      таблицы. После импорта данные доступны для выполнения любых операций в
      Microsoft Excel;
  25. Web Queries. В Microsoft Excel 97 имеется операция создания запроса  к
      данным, хранящимся на Web-сервере. Можно создать постоянную ссылку  на
      страницу  в  Internet   и   данные   в   таблице   будут   обновляться
      автоматически.

|Требования к конфигурации компьютера                                    |
|Аппаратное     |Персональный компьютер с процессором класса 486 или    |
|обеспечение    |выше.                                                  |
|               |Устройство для чтения компакт-дисков (CD-ROM) или      |
|               |3,5"-дисковод.                                         |
|               |VGA (или выше) видеоадаптер (рекомендуется Super VGA,  |
|               |256 цветов)                                            |
|               |Манипулятор Microsoft IntelliMouse(tm), Microsoft Mouse|
|               |или совместимый                                        |
|Операционная   |Microsoft Windows 95/98                                |
|система        |Microsoft Windows NT 3.51 с Service Pack 5 или старше  |
|               |Microsoft Windows NT 4.0 с Service Pack 1 или старше   |
|Память         |Для Windows 95/98: 8 MB памяти (12 MB рекомендуется при|
|               |использовании функции WordMail);                       |
|               |Для Windows NT: 16 MB памяти;                          |
|Требуемое      |35-40 MB требуется для типовой установки               |
|пространство на|                                                       |
|жестком диске  |                                                       |


3 Возможности

      Справка  и  помощник  по  работе  с  электронными  таблицами  Помогает
пользователям  научиться  эффективной  работе  с   электронными   таблицами.
Предоставляет полезные советы  и  решения.  Отвечает  на  вопросы  доступным
пользователю языком.
      Удобство ввода формул Пользователи могут писать  уравнения,  пользуясь
простой  терминологией.  В  формулах   вместо   ссылок   на   ячейки   можно
использовать названия колонок  и  рядов  (например:  Цена*Количество  вместо
А1*А2), что облегчает создание и понимание вычислений.
      Просмотр  макета  страницы  Предоставляет  возможность  задать  точное
расположение информации на каждой печатной странице.
      Заказные ячейки  Пользователи  могут  «проникнуть  за  сетку»  обычной
электронной таблицы, чтобы данные выглядели согласно их требованиям.
       Контроль  изменений  В  случае  нескольких  пользователей  упрощается
контроль изменений  в  рабочем  каталоге  (выделяются  изменения,  внесенные
другими пользователями). Для каждой выделенной  ячейки  имеются  сведения  о
том, что было изменено, когда и кем.
      Гиперссылки Во все документы Excel 97 можно вставлять гиперссылки  для
доступа к нужным документам независимо от их местонахождения.
      Открытие из URL Большая гибкость при совместном  использовании  данных
в Intranet. Пользователи легко могут открывать файлы Excel или HTML из HTTP-
 и FTP-серверов при помощи команды Excel 97 "Открыть файл".
      Публикация в режиме "online"  -  запись  в  URL  Облегчает  размещение
тетрадей Excel на HTTP- и FTP-серверах сети Intranet.  Теперь  каждый  может
просматривать  и  использовать  данные  электронных  таблиц,  помещенных   в
совместную сеть Intranet или в глобальную сеть
      Работа с HTML-документами Excel 97 работает с документами HTML так  же
легко, как и со своими документами  (*.xls).  Форматирование  и  гиперссылки
сохраняются,  а  пользователи  получают  возможность  динамического  анализа
данных, а не простого их просмотра.
      Среда разработки Visual Basic® (VBE) Среда VBE  совместима  со  средой
Visual  Basic 5.0.  В  ней  имеется  улучшенный  редактор   кодов,   броузер
иерархических объектов,  многооконный  отладчик,  окно  свойств,  анализатор
проекта и конструктор
      Мастер диаграмм Расположение всех параметров графиков  в  одном  месте
ускоряет их создание и редактирование.
       Тетради  коллективного  пользования   В   этих   тетрадях   несколько
пользователей  могут  одновременно  открыть  один   документ,   не   получая
сообщения  "файл  уже  открыт".  Можно  изменять   данные,   форматирование,
редактировать формулы и даже добавлять и удалять ячейки.
       Сохранение  в  гипертекстовом  формате  (HTML)   Пользователи   могут
автоматически преобразовывать свои таблицы, созданные при  помощи  программы
Excel, в HTML-формат для того, чтобы размещать их в  Интернете  или  в  сети
Intranet. Диаграммы теперь можно записывать  в  формате  GIF  с  улучшенными
возможностями преобразования.
      Просмотр документов Excel  Пользователи  могут  публиковать  документы
Excel так, чтобы их могли просматривать и те, у кого  нет  программы  Excel.
Программа  Excel  Viewer   позволяет,   не   пользуясь   программой   Excel,
просматривать, копировать, увеличивать, фильтровать,  печатать  и  следовать
гиперссылкам в документах Excel.

1 Интеграция в Microsoft Office

      Microsoft Excel является составной частью  офисного  пакета  Microsoft
Office, тесно интегрированный с остальными приложениями входящими  в  пакет,
что позволяет импортировать и экспортировать данные  из  других  приложений,
создавать связанные документы и сохранять единый стиль в них.
      Меню и панели инструментов  Excel  максимально  соответствуют  меню  и
панелям Microsoft Word. Все диалоговые окна  приложений  Windows,  созданных
Microsoft, выдержаны  в  едином  стиле.  Ярлычки  вкладок  в  верхней  части
диалогового окна, подобные ярлычкам  листов  в  нижней  части  окна  рабочей
книги, позволят быстро переходить  от  одной  вкладки  к  другой.  Например,
различные вкладки диалогового окна Формат ячеек дают возможность  установить
числовые  форматы,  типы  выравнивания,  шрифты,  рамки,   добавить   узоры,
установить защиту ячеек и многое другое.
      Реализована возможность перетаскивания  данных  из  Excel  в  (и  иные
приложения из пакета Microsoft Office),  а  также  поддержка  второй  версии
протокола связи и внедрения объектов (Object Linking and Embedding, OLE 2.)

2 Мастера

      Для  облегчения  работы  в  Microsoft  Excel  предусмотрены  различные
средства, облегчающие рутинную  работу.  Одним  из  таких  средств  являются
Мастера (помощники). Их несколько:
  26. Мастер Диаграмм, позволяющий упростить построение диаграмм;
  27. Мастер Функций, позволяющий упростить написание функций;
  28. Мастер Подсказок,  для  быстрого  поиска  информации  (хотя  он  и  не
      является мастером, в строгом смысле этого слова).
  29. Мастер Web-страниц, для создания HTML-страницы  при  помощи  Microsoft
      Excel.
  30. Мастер шаблонов, позволяет создать новый шаблон или базу данных.
  31. Мастер сводных таблиц позволяет анализировать  не  только  любой  срез
      имеющейся информации, но и упорядочивать столбцы, строки  и  заголовки
      страниц методом перетащить и отпустить.
  32. Мастер преобразований, позволяет конвертировать документы в  Excel  из
      других форматов.

3 Специальные возможности

      В Microsoft Excel включены некоторые  возможности,  которые  позволяют
упростить работу и выполнить необходимые вам вычисления.
      К  первым  относится  автозаполнение  таблицы.  Режим  Автозаполнение,
позволяющий  создавать  последовательности   из   числовых   или   текстовых
значений, практически не вводя данные вручную, также расширен.  В  Microsoft
Excel возможно создавать свои собственные режимы  автозаполнения.  Например,
если Вам понадобится создать листы с заголовками типа Январь, Февраль,  Март
и  т.д.,  режим  Автозаполнение  к  Вашим  услугам.  Более   того,   команда
Сортировка обрабатывает теперь и пользовательские  (т.е.  созданные)  списки
Автозаполнения, так что Вы всегда сумеете отсортировать свои данные.
      Ко второму относится команда Автосуммирование, позволяющая  складывать
числовые  значения  одним  щелчком  мыши,  дает  возможность   сделать   это
одновременно и со строками, и  со  столбцами.  По  этой  команде  Вы  можете
подводить общие итоги даже в таблицах с промежуточными итогами.
      Также существует такая возможность, как  Подбор  параметров  (получить
необходимый результат изменяя исходные данные) и Поиск  решения  (нахождение
корней уравнений).
      Еще одной интересной особенностью Excel является возможность работы  с
географическими картами Карта....

4 Программирование в Exсel

      Microsoft  Excel  это  первое  приложение  со  встроенным  макроязыком
программирования Microsoft Visual Basic для  приложений  (Visual  Basic  for
Applications, VBA). VBA значительно упрощает создание управляемых  макросами
приложений. Кроме того,  Excel  содержит  новый  редактор  диалоговых  окон,
новый редактор меню и новый отладчик.
      Для  удобства  пользователей  Microsoft  Excel  поддерживает  макросы,
созданные в предыдущих версиях Excel, и макросы, импортированные из Lotus 1-
2-3.

4 Области применения

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

Общие понятия


1 Логика табличного редактора

      Любая  таблица  состоит  из  строк  и  столбцов,  как  правило  строки
нумеруются цифрами 1, 2,  3  и  т.д.  до  16384,  а  столбцам  присваивается
соответствие латинскому алфавиту  A,  B,  C  и  т.д.  Причем  когда  алфавит
заканчивается, за Z следует AA, AB, AC и т.д. до  IV.  Пересечения  строк  и
столбцов образует ячейки, каждая ячейка имеет  адрес,  который  обозначается
именем столбца и номером строки. Например B5.
                                    [pic]
                         Рис. 1. Рабочий лист Excel
      В ячейки заносятся данные. В дальнейшем при  обращении  к  эти  данным
всегда идет ссылка на адрес  ячейки  где  они  расположены.  Например  в  Е9
расположено число 5. Если необходимо провести какие-либо  действия  с  этими
данными например сложить два числа 4 из  ячейки  A1  и  4  из  ячейки  C7  и
поместить их сумму в ячейку C3, то в ячейке C3 вводится формула =A1+C7  и  в
ячейки C3 появляется результат 8.  Подробнее  об  этом  будет  рассказано  в
Главе 3 «Вычисления в Excel. Формулы и функции».

2 Вид и основные понятия


1 Рабочая книга и рабочий лист.

Рабочий  лист      -  это  собственно  электронная  таблица,  основной   тип