Симонович С.В. Информатика

Подождите немного. Документ загружается.

320 Глава 12. Обработка данных средствами электронных таблиц

ляются на рабочем листе цветными рамками, а сами ссылки отображаются

ячейке

и в строке формул тем же цветом. Это облегчает редактирование и проверку пра-

вильности формул.

Абсолютные и относительные ссылки

По

умолчанию,

ссылки на ячейки

формулах рассматриваются как

относительные.

Это

означает,

что

при копировании формулы адреса

ссылках автоматически изме-

няются

соответствии

относительным расположением исходной ячейки

созда-

ваемой копии.

Пусть, например,

ячейке

В2

имеется ссылка

на

ячейку

A3.

относительном пред-

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

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

левее и на

одну строку

ниже

данной.

Если формула будет скопиро-

вана

другую

ячейку,

то такое относительное указание ссылки

сохранится.

Напри-

мер,

при копировании формулы

ячейку

ЕА27

ссылка будет продолжать указывать

на ячейку, располагающуюся левее и ниже, в данном случае на ячейку DZ28.

При

абсолютной адресации

адреса,

ссылок при копировании не изменяются, так

что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для

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

надо

выделить ссылку

на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсо-

лютную адресацию, предваряются символом $. Например, при последовательных

нажатиях клавиши

номер ячейки

А1

будет записываться как

А1,

$А$1,

А$1

$А1.

двух последних случаях один из компонентов номера ячейки рассматривается

как абсолютный, а другой

—

как относительный.

Копирование содержимого ячеек

Копирование и перемещение ячеек в программе

Excel

можно осуществлять мето-

дом перетаскивания или

через

буфер

обмена.

При

работе с

небольшим числом ячеек

удобно использовать первый

метод,

при работе

большими диапазонами

—

второй.

Метод перетаскивания. Чтобы методом перетаскивания скопировать или пере-

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

навести указатель мыши

на

рамку текущей ячейки (он примет

вид

стрелки с допол-

нительными стрелочками). Теперь ячейку можно перетащить

любое место рабо-

чего листа (точка вставки помечается всплывающей подсказкой).

Для выбора способа выполнения этой операции,

также для более надежного кон-

троля над

ней

рекомендуется использовать специальное

перетаскивание с

помощью

правой кнопки мыши. В этом случае при отпускании кнопки мыши появляется

специальное

меню,

котором можно выбрать конкретную выполняемую операцию.

Применение буфера обмена. Передача информации через буфер обмена имеет в

программе

Excel

определенные особенности, связанные со сложностью контроля

над

этой

операцией.

Вначале необходимо выделить копируемый (вырезаемый) диа-

пазон и дать команду на его помещение в буфер обмена: Правка • Копировать или

Правка • Вырезать. Вставка данных в рабочий лист возможна лишь немедленно

после их помещения в буфер обмена. Попытка выполнить любую другую опера-

12.2.

Содержание электронной таблицы 321

цию приводит

отмене начатого процесса копирования или перемещения. Однако

утраты данных

не

происходит, поскольку «вырезанные» данные удаляются

из

места

их исходного размещения только в момент выполнения вставки.'

Место вставки определяется путем указания ячейки, соответствующей верхнему

левому углу диапазона, помещенного

буфер обмена, или путем выделения диапа-

зона,

который

по

размерам

точности

равен

копируемому

(перемещаемому).

Вставка

выполняется командой Правка •

Вставить.

Для управления способом вставки можно

использовать команду Правка • Специальная

вставка.

этом случае правила вставки

данных из буфера обмена задаются в открывшемся диалоговом окне.

Автоматизация ввода

Так

как

таблицы

часто

содержат повторяющиеся

или

однотипные

данные,

программа

Excel

содержит средства автоматизации ввода. К числу предоставляемых средств

относятся:

автозавершение,

автозаполнение

числами и автозаполнение

формулами.

Автозавершение. Для автоматизации ввода текстовых данных используется метод

автозавершения.

Его

применяют

при

вводе

ячейки одного столбца рабочего листа

текстовых строк, среди которых есть повторяющиеся.

ходе ввода текстовых дан-

ных в очередную ячейку программа

Excel

проверяет соответствие введенных сим-

волов строкам, имеющемся в этом столбце выше. Если обнаружено однозначное

совпадение, введенный текст автоматически

дополняется.

Нажатие клавиши

ENTER

подтверждает операцию автозавершения, в противном случае ввод можно продол-

жать,

не обращая внимания на предлагаемый вариант.

Можно прервать работу средства автозавершения, оставив

столбце

пустую

ячейку.

И наоборот, чтобы использовать возможности средства автозавершения, заполнен-

ные ячейки должны идти подряд, без промежутков между ними.

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

автозапол-

нения.

правом нижнем углу рамки текущей ячейки имеется черный квадратик

—

маркер

заполнения.

При наведении на него указатель мыши (он обычно имеет вид

толстого белого креста) приобретает форму тонкого черного крестика. Перетаски-

вание маркера заполнения рассматривается как операция «размножения» содер-

жимого ячейки в горизонтальном или вертикальном направлении.

Если ячейка содержит число (в том числе

дату,

денежную сумму), то при перетас-

кивании маркера происходит копирование ячеек или их заполнение арифмети-

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

специальное перетаскивание с использованием правой кнопки мыши.

Пусть,

например,

ячейка

А1

содержит число

Наведите указатель мыши на маркер

заполнения, нажмите правую кнопку мыши и перетащите маркер заполнения так,

чтобы рамка охватила ячейки

А1,

81 и

С1,

и отпустите кнопку мыши. Если теперь

выбрать

открывшемся

меню

пункт Копировать

ячейки,

все

ячейки будут содержать

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

2 и 3.

Чтобы точно сформулировать условия заполнения ячеек, следует дать команду

Правка • Заполнить •Прогрессия. В открывшемся диалоговом окне Прогрессия

выбирается тип прогрессии, величина шага и предельное значение. После щелчка

322

Глава 12. Обработка данных средствами электронных таблиц

на кнопке

ОК

программа £гсе/ автоматически заполняет ячейки в соответствии с

заданными правилами.

Автозаполнение формулами. Эта операция выполняется так же, как автозапол-

нение числами. Ее особенность заключается в необходимости копирования ссы-

лок на другие ячейки. В ходе автозаполнения во внимание принимается характер

ссылок в формуле: относительные ссылки изменяются в соответствии с относи-

тельным расположением копии

оригинала, абсолютные остаются без изменений.

Для примера предположим, что значения в третьем столбце рабочего листа (стол-

бце С) вычисляются как суммы значений в соответствующих ячейках столбцов

В.

Введем

ячейку

С1

формулу

=А1

+В1.

Теперь скопируем эту формулу методом

автозаполнения во все ячейки третьего столбца таблицы. Благодаря относитель-

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

В таблице 12.1 приведены правила обновления ссылок при автозаполнении вдоль

строки или вдоль столбца.

Таблица 12.1. Правила обновления ссылок при автозаполнении

Ссылка в исходной ячейке

А1 (относительная)

$А1 (абсолютная по столбцу)

А$1 (абсолютная по строке)

$А$1 (абсолютная)

Ссылка в следующей ячейке

При заполнении вправо

В1

$А1

В$1

$А$1

При заполнении вниз

А2

$А2

А$1 1

$А$1

Использование стандартных функций

Стандартные функции используются в программе jErc^/только в формулах.

Вызов

функции состоит в указании в формуле

имени

функции,

после которого в скобках

указывается

список

параметров.

Отдельные параметры разделяются в списке точ-

кой с запятой. В качестве параметра может использоваться число, адрес ячейки

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

ваться функции.

В режиме ввода формулы в левой части строки формул, где раньше располагался

номер текущей ячейки, появляется раскрывающийся список функций. Он содер-

жит десять функций, которые использовались последними, а также пункт Другие

функции.

Использование мастера

функций.

При выборе пункта

Другие функции

запускается

Мастер

функций,

облегчающий выбор нужной

функции.

раскрывающемся списке

Категория выбирается категория, к которой относится функция (если определить

категорию затруднительно, используют пункт Полный алфавитный перечень), а в

списке Выберите

функцию —

конкретная функция данной

категории.

После щелчка

на кнопке

ОК

имя функции заносится

строку формул вместе

со

скобками, ограни-

чивающими список параметров. Текстовый курсор устанавливается между этими

скобками. Вызвать Мастер функций можно и проще, щелчком на кнопке Вставка

функции в строке формул.

12.3.

Печать документов Excel

323

Аргументы функции. Как только имя функции выбрано, на экране появляется

диалоговое

окно Аргументы

функции (в предыдущих версиях Excel

это

окно рассма-

тривалось как палитра

формул).

Это окно, в частности, содержит значение, кото-

рое получится, если немедленно закончить ввод формулы (рис. 12.4).

Раскрывающийся'список функций

Обязательный параметр

Необязательный параметр

Содержимое текущей ячейки

Значение

параметра

Кнопки сворачивания

палитры формул

. Описание параметра N.. Значение параметра

.Значение функции

. Описание функции

Рис.

12.4.

Строка формул и диалоговое окно Аргументы функции

Правила вычисления формул, содержащих функции, не отличаются от правил

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

параметров функции, также могут быть относительными или абсолютными, что

учитывается при копировании формул методом автозаполнения.

12.3.

Пбчать документов Excel

Экранное представление электронной таблицы в £гс^/значительно отличается от

того,

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

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

деляется форматом печатного листа. Кроме того, элементы оформления рабочего

окна программы: номера строк и столбцов, условные границы ячеек

—

обычно не

отображаются при печати.

Предварительный просмотр

Перед

печатью рабочего листа следует перейти в

режим

предварительного просмотра

(кнопка Предварительный просмотр

на

стандартной панели инструментов). Режим

предварительного просмотра (рис. 12.5) не допускает редактирования документа.

324

Глава 12. Обработка данных средствами электронных таблиц

Управляющие

кнопки

Маркеры

управления

размером

колонтитулов

Печатное

представление

документа

Маркер

управления

размером полей

Поле для

колонтитула

Рис.

12,5.

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

но позволяет увидеть его на экране точно в таком виде, в каком он будет напеча-

тан.

Кроме

того,

режим предварительного просмотра позволяет изменить свойства

печатной страницы и параметры печати.

Управление в режиме предварительного просмотра осуществляется при помощи

кнопок, расположенных вдоль верхнего края окна. Кнопка Страница открывает

диалоговое окно Параметры страницы, которое служит для задания параметров

страницы: ориентации

листа,

масштаба страницы (изменение масштаба позволяет

управлять числом печатных

страниц,

необходимых

для

документа),

размеров полей

документа. Здесь же можно задать верхние и нижние колонтитулы для страницы.

На вкладке

Лист

включается или отключается

печать

сетки

номеров строк

столб-

цов,

а также выбирается последовательность разбиения

на

страницы

рабочего

листа,

превосходящего размеры печатной страницы как по длине, так и по ширине.

Изменить величину полей

страницы,

а также ширину ячеек

при

печати

мойсно

также

непосредственно

режиме предварительного просмотра, при помощи кнопки Поля.

При щелчке на этой кнопке на странице появляются маркеры, указывающие гра-

ницы полей страницы и ячеек. Изменить положение этих границ можно методом

перетаскивания.

Завершить работу

режиме предварительного просмотра можно тремя способами,

в зависимости от

того,

что п.^анируется делать

дальше.

Щелчок на кнопке Закрыть

позволяет вернуться к редактированию документа. Щелчок на кнопке Разметка

12.4.

Применение электронных таблиц

для

расчетов 325

страницы служит для возврата

редактированию документа,

но в

режиме разметки

страницы.

этом режиме документ отображается таким образом, чтобы наиболее

удобно показать не содержимое ячеек таблицы, а

область печати

и границы стра-

ниц документа. Переключение между режимом разметки и обычным режимом

можно также осуществлять через меню Вид (команды Вид • Обычный и Вид • Раз-

метка

страницы).

Третий способ

—

начать печать документа.

Печать документа

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

распечатки документа (его можно открыть и без предварительного просмотра

—

с помощью команды Файл • Печать). Это окно содержит стандартные средства

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

Выбор области печати

Область печати

—

эта часть рабочего листа, которая должна быть выведена на

печать. По умолчанию область печати совпадает с заполненной частью рабочего

листа

представляет собой прямоугольник, примыкающий

верхнему левому углу

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

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

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

команду Файл • Область печати • Задать. Если текущей является одна-единствен-

ная ячейка, то программа предполагает, что область печати не выделена, и выдает

предупреждающее сообщение.

Если область печати задана, то программа отображает

режиме предварительного

просмотра

распечатывает только

ее.

Границы области печати выделяются

на

рабо-

чем

листе крупным пунктиром (сплошной линией

режиме

разметки).

Для измене-

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

Область печати • Убрать вернуться к параметрам, используемым по умолчанию.

Границы отдельных печатных страниц отображаются на

рабочем листе

мелким пунк-

тиром.

некоторых случаях

требуется,

чтобы определенные ячейки располагались

вместе на одной и той же печатной странице или, наоборот, разделение печатных

страниц происходило в определенном месте рабочего листа. Такая возможность

реализуется путем задания границ печатных страниц

вручную.

Чтобы вставить

раз-

рыв страницы, надо сделать текущей ячейку, которая будет располагаться

левом

верхнем углу печатной страницы,

дать команду Вставка • Разрыв

страницы.

Про-

грамма

£гсб/вставит

принудительные разрывы страницы перед строкой

столбцом,

в которых располагается данная ячейка. Если выбранная ячейка находится в первой

строке или столбце

А,

то разрыв страницы задается только

по

одному направлению.

12.4. Применение электронных таблиц

для

расчетов

в научно-технической деятельности программу

Excel

трудно рассматривать как

основной вычислительный инструмент. Однако

ее удобно

применять

тех случаях,

когда требуется быстрая обработка больших объемов данных. Она полезна для

выполнения таких

операций,

как статистическая обработка

анализ

данных,

реше-

326 Глава 12. Обработка данных средствами электронных таблиц

ние задач оптимизации, построение диаграмм и графиков. Для такого рода задач

применяют как основные средства программы

Excely

так и дополнительные (над-

стройки).

Итоговые вычисления

предполагают получение числовых характеристик, описы-

вающих определенный набор данных в целом. Например, возможно вычисление

суммы значений, входящих в набор, среднего значения и других статистических

характеристик, количества или доли элементов набора, удовлетворяющих опреде-

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

Excel

выполня-

ется при помощи встроенных функций. Особенность использования таких

итого-

вых функций состоит в том, что при их задании программа пытается «угадать»,

в каких ячейках заключен обрабатываемый набор данных, и задать параметры

функции автоматически.

В качестве параметра итоговой функции обычно задается некоторый диапазон

ячеек,

размер которого определяется

автоматически.

Выбранный диапазон рассмат-

ривается как отдельный параметр («массив»), и в вычислениях используются все

ячейки, составляющие,

его.

Суммирование. Для итоговых вычислений применяют ограниченный набор функ-

ций,

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

(СУММ).

Это

единственная функция, для применения которой есть отдельная кнопка на стан-

дартной панели инструментов (кнопка

Автосумма).

Диапазон суммирования, выби-

раемый автоматически,

В1слючает

ячейки

данными, расположенные над текущей

ячейкой (предпочтительнее) или слева от нее и образующие непрерывный блок.

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

ющий к текущей ячейке.

Автоматический подбор диапазона не исключает возможности редактирования

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

а также задать дополнительные параметры функции.

Функции для итоговых вычислений. Прочие функции для итоговых вычислений

выбираются обычным образом,

помощью раскрывающегося списка

строке фор-

мул или с использованием мастера

функций.

Все

эти функции относятся к катего-

рии Статистические. В их число входят функции ДИСП (вычисляет дисперсию),

МАКС

(максимальное

число в

диапазоне),

СРЗНАЧ

(среднее арифметическое значе-

ние чисел диапазона),

СЧЕТ

(подсчет ячеек с числами в диапазоне) и другие.

Функции, предназначенные для выполнения итоговых вычислений, часто приме-

няют при использовании таблицы Excels качестве базы данных, а именно на фоне

фильтрации записей или при создании сводных таблиц.

Использование надстроек

Надстройки —

это специальные средства, расширяющие возможности программы

Excel.

На практике именно надстройки делают программу iErce/удобной для исполь-

зования в научно-технической работе. Хотя эти средства считаются внешними.

12.4. Применение электронных таблиц для расчетов

327

дополнительными, доступ к ним осуществляется при помощи обычных команд

строки меню (обычно через меню Сервис или Данные). Команда использования

настройки обычно открывает специальное диалоговое

огшо,

оформление которого

не отличается от стандартных диалоговых окон программы Excel

Подключить

или

от1С71ючить установленные надстройки

можно с

помощью команды

Сервис • Надстройки (рис.

12.6).

Подключение надстроек увеличивает нагрузку на

вычислительную

систему,

поэтому обычно рекомендуют подключать

только те

над-

стройки, которые реально используются.

Список

надстроек

Описание

выбранной

надстройки

Над<:тр<>ики

AocTynHtMe н<|дстрдйкн:

р Analysis ToolPak - VBA

р Conditional Sum Wizard

:uro Currency Tools

p Internet Assistant VBA

g Lookup Wizard

W Solver Add-in

Отнена

Обаор...

бртомати>ацня.

jVnalysii ToolPak - "•

i:' ^ Provides functions and Interfaces for finane Wand

$cienttfjcdat*flftalystt

Кнопка выбора

папки,

в которой

хранятся

надстройки

Рис.

12.6.

Диалоговое окно для подключения и отключения надстроек

Вот основные надстройки, поставляемые вместе с программой Excel

Пакет анализа (Analysis

ToolPak).

Обеспечивает дополнительные возможности

анализа наборов данных. Выбор конкретного метода анализа осуществляется в

диалоговом окне Data Analysis (Анализ данных), которое открывается командой

Сервис • Data Analysis (Анализ данных).

Мастер суммирования {ConditionalSum

Wizard).

Позволяет автоматизировать созда-

ние формул для суммирования данных

столбце

таблицы.

При этом ячейки могут

включаться в сумму только при выполнении определенных условий. Запуск мас-

тера осуществляется

с помощью

команды

Сервис

•

Conditional Sunn

(Частичная сумма).

Мастер

подстановок

{Lookup Wizard). Автоматизирует создание формулы для

поиска данных в таблице по названию столбца и строки. Мастер позволяет п|)оиз-

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

метров, используемых для поиска. Вызывается командой Сервис • Lookup (Поиск).

Поиск

решения

{Solver

Add-in). Эта надстройка используется для решения задач

оптимизации. Ячейки, для которых подбираются оптимальные значения и зада-

ются ограничения, выбираются в диалоговом окне Solver Paranneters (Поиск реше-

ния),

которое открывают при помощи команды Сервис • Solver (Поиск решения).

328 Глава 12. Обработка данных средствами электронных таблиц

12.5.

Построение диаграмм и графиков

в программе Excel

термин

«диаграмма» используется для обозначения всех видов

графического представления числовых данных. Построение графического изобра-

жения производится на основе ряда

данных.

Так называют группу ячеек с данными

в пределах отдельной строки или столбца. На одной диаграмме можно отображать

несколько рядов данных.

Диаграмма представляет собой вставной объект, внедренный на один из листов

рабочей книги. Она может располагаться на том же листе, на котором находятся

данные, или нагшобом другом листе (часто для отображения диаграммы отводят

отдельный лист). Диаграмма сохраняет связь с данными, на основе которых она

построена, и при обновлении этих данных немедленно изменяет свой вид.

Для построения диаграммы обычно используют Мастер диаграмм, запускаемый

щелчком на кнопке

Мастер

диаграмм на стандартной панели инструментов. Часто

удобно

заранее выделить

область,

содержащую

данные,

которые будут отображаться

на диаграмме, но задать эту информацию можно и в ходе работы мастера.

Выбор типа диаграммы

На первом этапе работы мастера выбирают форму диаграммы. Доступные формы

перечислены

списке

Тип на

вкладке

Стандартные.

Для выбранного типа диаграммы

справа указывается несколько вариантов представления данных (палитра

Вид),

из

которых следует выбрать наиболее подходящий. На вкладке Нестандартные отоб-

ражается набор полностью сформированных типов диаграмм с готовым формати-

рованием. После задания формы диаграммы следует щелкнуть на кнопке Далее.

Выбор данных

Второй этап работы мастера служит для выбора данных, по которым будет стро-

иться диаграмма

(рис.

12.7).

Если диапазон данных

был

выбран

заранее,

то в

области

предварительного просмотра в верхней части окна мастера появится приблизи-

тельное отображение будущей диаграммы. Если данные образуют единый прямо-

угольный диапазон, то

их

удобно выбирать при помощи вкладки Диапазон данных.

Если данные

не

образуют единой

группы,

то

информацию для отрисовки отдельных

рядов данных задают на вкладке

Ряд.

Предварительное представление диаграммы

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

Оформление диаграммы

Третий этап работы мастера (после щелчка на кнопке Далее) состоит в выборе

оформления диаграммы. На вкладках окна мастера задаются:

• название диаграммы, подписи осей (вкладка Заголовки);

• отображение и маркировка осей координат (вкладка Оси);

• отображение сетки

линий,

параллельных осям координат (вкладка

Линии

сетки);

• описание построенных графиков (вкладка Легенда);

12.5.

Построение диаграмм и графиков

329

На этой вкладке

ряды данных

выбираются

вручную

Список рядов

данных

Кнопки

добавления

и удаления

рядов данных

::ЩШШШМ^щШ^-ШмШ^

Диализов

AeiHHNX

и^^Л

Экспериментальные точки

*J Ммл^ |-Теэультаты

изиере>у1Й"

^Наилучшая прямая Зкец:(^нтХ: j-'Обработкд эксперимента^

Z} ЗначбИИяУг j-'Обработказкс|;иримемта^

Область

предварительного

.просмотра

Имя текущего

.ряда данных

Значения,

используемые

при построении

-точек графика

Рис.

12.7.

Выбор

данных,

отображаемых

на

диаграмме

• отображение надписей, соответствующих отдельным элементам данных на гра-

фике (вкладка Подписи данных);

• представление данных, использованных при построении графика, в виде таб-

лицы (вкладка Таблица данных).

В зависимости от типа диаграммы некоторые из перечисленных вкладок могут

отсутствовать.

Размещение диаграммы

На последнем этапе работы мастера (после щелчка на кнопке Далее) указывается,

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

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

мента, содержащего диаграмму. После щелчка на кнопке

Готово

диаграмма строится

автоматически и вставляется на указанный рабочий лист (рис. 12.8).

Редактирование диаграммы

Готовую диаграмму можно изменить. Она состоит из набора отдельных элемен-

тов,

таких, как сами графики (ряды данных), оси координат, заголовок диаграм-

мы,

область построения и прочее. При щелчке на элементе диаграммы он выделя-

ется маркерами, а при наведении на него указателя мыши — описывается

всплывающей подсказкой. Открыть диалоговое окно для форматирования элемента

диаграммы можно через меню Формат (для выделенного элемента) или через кон-

текстное меню (команда Формат). Различные вкладки открывшегося диалогового

окна позволяют изменять параметры отображения выбранного элемента данных.