close

Вход

Забыли?

вход по аккаунту

?

1587.Табличный процессор MS Excel Основы работы и применения в экономике

код для вставкиСкачать
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Министерство образования и науки Российской Федерации
Федеральное агентство по образованию
Государственное образовательное учреждение
высшего профессионального образования
Российский государственный торгово - экономический
университет
Казанский институт (филиал)
Кафедра информатики и высшей математики
Ю.Н. Марков, И.И. Исмагилов
ТАБЛИЧНЫЙ ПРОЦЕССОР MS EXCEL:
ОСНОВЫ РАБОТЫ И ПРИМЕНЕНИЯ В ЭКОНОМИКЕ
Учебное пособие
Казань 2010
УДК 681.3
ББК 32.973.26-018.2
М 27
Печатается по решению учебно-методического совета
Казанского института (филиала) ГОУ ВПО «РГТЭУ»
3
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рецензенты: - И.В. Аникин, к.т.н., доцент, заведующий кафедрой систем
информационной безопасности КГТУ (КАИ) им.
А.Н.Туполева;
- И.Н. Аглиуллин, к.т.н., доцент кафедры информатики и высшей математики КИ (филиала) РГТЭУ;
Марков Ю.Н., Исмагилов И.И.
М27
Табличный процессор MS Excel: Основы работы и применения в
экономике: учебное пособие. - Казань: Изд-во МОиН РТ, 2010. – 116 с.
ISBN 978-5-4233-0036-4
В данном учебном пособии раскрыты функциональные возможн ости, базовые объекты и операции, основные средства и инструменты табли чного процессора MS Excel. Упражнения иллюстрированы с целью ускорения
приобретения необходимых навыков практической работы с популярным
приложением офисного назначения. Рассматривается решение отдельных
классов экономических задач средствами MS Excel.
Учебное пособие предназначено для студентов вузов экономических
специальностей, а также может быть полезно всем желающим освоить информационные технологии табличной обработки данных.
 РГТЭУ
 Марков Ю.Н., Исмагилов И.И., 2010
 Оформление Изд-ва МОиН РТ , 2010
4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ВВЕДЕНИЕ
Среди интегрированных программных пакетов офисного назначения
широкую популярность приобрел пакет Microsoft Office. Основными составными его компонентами являются: текстовый процессор MS Word, табличный процессор MS Excel, СУБД MS Access. Изучение этих программных
продуктов, как базовых в экономической информатике, предусмотрено уче бными программами современных ВУЗов [1-5].
Если текстовый процессор MS Word в сфере офисного применения
предназначен для создания текстовых документов, а СУБД MS Access для
организации работы с массивами данных, то табличный процессор MS Excel
в основном несет вычислительную нагрузку и используется, прежде всего,
для автоматизации разнообразных расчетов. Сфера применения табличного
процессора MS Excel распространяется как на финансово-экономические
расчеты [6], так и на проектирование технических систем [7]. Эффективно
MS Excel применяется в менеджменте[7-9], бухгалтерском учете и аудите
[12, 13].
Цель настоящего учебного пособия - обеспечить методическую поддержку практических занятий по дисциплинам информационного цикла: Информатике, Программным средствам офисно го назначения, Информационным технологиям управления, Информационным технологиям в коммерч еской деятельности. В нем приведены сведения об основных технологических
операциях и процессах в среде табличного процессора MS Excel при работе с
данными, представленными в табличной форме.
Излагаемые положения демонстрируются на примерах. Примеры с опровождаются подробным алгоритмом действий. Часть используемых пр имеров содержится в источниках, представленных в списке литературы. Пр иводятся дополнительные контрольные задания и тесты, направленные на закрепление изложенного материала.
1. ОБЩИЕ СВЕДЕНИЯ О ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL
1.1.
Назначение
Табличные процессоры предназначены для создания, хранения и обр аботки электронных таблиц. Электронные таблицы – это двумерные массивы
данных, состоящие из столбцов и строк и размещенные в памяти компьютера. Они позволяют не только создавать табличные документы, но и автом атизировать обработку табличных данных. Табличные процессоры часто
называют также просто электронными таблицами. Лидирующее место в этом
классе программных продуктов занимает табличный процессор фирмы Microsoft Excel, который является Windows -прграммой и входит в пакет Microsoft Office.
5
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
1.2.
Возможности
Табличные процессоры обеспечивают работу с большими таблицами
данных. При работе с табличным процессором на экран выводится прям оугольная таблица, в ячейках (клетках) которой могут находиться числа, пояснительные тексты и формулы для расчета значения в ячейке по имеющи мся данным.
Кроме того, с помощью электронных таблиц можно выполнять различные экономические, бухгалтерские и инженерные расчеты, а также строить
разного рода диаграммы, проводить сложный экономический анализ, моделировать и оптимизировать решение различных хозяйственных ситуаций. В
расчетах можно использовать более 400 математических, статистических,
финансовых и других специализированных встроенных функций (см. раздел
2), связывать различные таблицы между собой, выбирать произвольные
форматы представления данных, создавать иерархические структуры.
Табличный процессор MS Excel предлагает большой набор возможностей по графическому представлению данных (см. раздел 4). Имеется возможность выбора из различных (14 стандартных и 20 нестандартных) типов
диаграмм, причем каждый тип диаграмм имеет несколько разновидностей
(подтипов). В процессе формирования диаграммы можно просмотреть любой
тип и выбрать наиболее удачный для данной таблицы.
С электронными таблицами MS Excel (с числом строк до нескольких
тысяч) можно выполнять целый ряд действий (см . раздел 5), характерных для
работ с базами данных, таких как сортировка, фильтрация, формирование
итогов и т.д.
Возможности MS Excel эффективно используются в постановке, решении и анализе оптимизационных задач (см. раздел 6), в построении экстрап оляционных моделей прогнозирования экономических процессов, в решении
задач корреляционного и регрессионного анализа (см. раздел 7).
Замечательной особенностью табличного процессора является наличие
инструмента макросов (см. раздел 8), позволяющего создавать приложения.
Приложения записываются в виде программ на языке программирования
Visual Basic for Applications, относящегося к классу объектноориентированных языков.
Использование MS Excel в составе интегрированного пакета Microsoft
Office допускает взаимный импорт данных на уровне документов из одного
приложения в другое (см. раздел 9).
1.3.
Интерфейс пользователя
Вид главного окна табличного процессора M S Excel приведен на рис. 1.
Структура главного окна MS Excel во многом походит на структуру окна текстового процессора MS Word и, как правило, включает строку Меню и обыч-
6
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
но две панели инструментов, а именно, панель инструментов Стандартная и
панель инструментов Форматирование.
Кнопки панели инструментов Стандартная соответствуют наиболее
часто выполняемым действиям. Одного щелчка мышью достаточно, чтобы
открыть или сохранить файл, напечатать, вырезать, скопировать и вставить,
суммировать или сортировать данные.
Кнопки панели инструментов Форматирование позволяют выбирать
шрифты; выделять текст полужирным шрифтом, курсивом, подчеркиванием;
выравнивать текст; форматировать числа; оформлять таблицы рамками и
цветом.
Во всех случаях, когда это не оговаривается, под щелчком мыши по дразумевается использование левой кнопки мыши.
Поле имени
Строка Меню
Панели инструментов:
Стандартная и
Форматирование
Строка формул
Рабочий лист
Строка состояния
Рисунок 1. Общий вид экрана в Microsoft Excel.
Заголовок строки
Заголовок столбца
Имя файла рабочей книги
Заголовок окна документа
Выделенная
ячейка
Кнопки прокрутки листов
Ярлычок листа
Полосы прокрутки
Рисунок 2. Элементы рабочего листа.
7
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Основную площадь экрана занимают рабочие листы, разбитые на
ячейки. На рис. 2 приведены элементы рабочего листа. Максимальный ра змер рабочего листа составляет 65536 строк (в версии MS Excel 2000) на 256
столбцов, чего вполне достаточно для выполнения большинства задач. Раб очие листы документа образуют рабочую книгу. Она хранится в файле с произвольным именем и расширением .XLS.
Кроме использования кнопок на панели инструментов в вызове и и сполнении основного набора команд применяется строка Меню. Последовательное исполнение набора команд будем записывать в форме последо вательного перечисления и разделения их знаком « », например, Файл

Печать или Вставка  Рисунок  Картинки.
Широкие возможности предоставляет также использование контекстно-зависимого меню различных объектов экрана MS Excel. Оно вызывается
щелчком правой кнопки мыши по объекту и содержит набор команд, прим енимых к данному объекту окна. В качестве объектов могут выступать выделенная ячейка рабочего листа, заголовки строк и столбцов, ярлычки листа
(см. рис. 5) или кнопки панели инструментов. Например, контекстное меню
ячейки содержит часто используемую команду Очистить содержимое с
очевидным назначением, а команды Добавить ячейки и Удалить контекстного меню заголовков строк и столбцов позволяют осуществлять вставку и удаление строк и столбцов таблицы несколько иначе, чем с помощью
строки Меню (см. раздел 2.9. Вставка и удаление строк таблицы).
Выбор той или иной команды, как правило, сопровождается вызовом
на экран соответствующего окна (см. рис. 6-7), в котором нужно установить
необходимые опции. Условие выхода из окна с требованием исполнения
данной команды осуществляется нажатием клавиши
, что
в
дальнейшем дополнительно оговариваться не будет.
Тесты по разделу 1.
1. Основными составными интегрированного пакета MS Office являются:
а) система управления базами данных (СУБД) MS Access;
б) настольный информационный менеджер MS Outlook (программа анализа
и составления расписаний);
в) табличный процессор MS Excel;
г) текстовый процессор MS Word.
2. Для автоматизации разнообразных расчетов наиболее часто используются:
а) таблицы текстового процессора MS Word;
в) СУБД MS Access;
б) табличный процессор MS Excel;
г) все перечисленное.
3. Сфера применения MS Excel распространяется на:
а) проектирование технических систем;
б) финансово-экономические расчеты;

Для в ыполнения тестов в ыберите прав ильный отв ет из предложенных в ариантов .
8
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
в) организацию документооборота.
4. В ячейках электронной таблицы могут находиться:
а) пояснительные тексты;
б) формулы для расчета;
в) числа.
5. В расчетах с помощью MS Excel можно использовать специализированные
встроенные функции:
а) более 700;
б) более 600;
в) более 500;
г) более 400.
6. Табличный процессор MS Excel может использоваться для решения о птимизационных задач:
а) работ с базами данных;
б) построения экстраполяционных моделей прогнозирования экономич еских процессов;
в) графического представления данных.
7. Рабочие листы документа образуют:
а) Рабочую папку; б) Рабочую книгу; в) Рабочие страницы; г) Рабочий
файл.
8. Файл для хранения электронных таблиц имеет расширение:
а) xlt;
б) xlp;
в) xls;
г) xld.
9. В качестве объектов использования контекстного меню в MS Excel могут
выступать:
а) ярлычки листа;
б) кнопки панели инструментов;
в) ячейки рабочих листов;
г) заголовки столбцов и строк.
10. Кнопки панели инструментов Форматирование в MS Excel позволяют:
а) оформлять таблицы рамками и цветом;
б) суммировать или сортировать данные;
в) выбирать шрифты;
г) вырезать, копировать и вставлять данные.
2. ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ В ТАБЛИЧНОМ ПРОЦЕССОРЕ
MS EXCEL
2.1. Изменение интерфейса пользователя
Пользователь имеет возможность изменить вид экрана текстового пр оцессора в соответствии со своими потребностями. Интерфейс пользователя
определяется вкладкой Вид строки Меню. Параметры вкладки Вид позволяют
изменить внешний вид окна программы и документа. Дополнительные во зможности устанавливаются набором опций на вкладке Вид после применения команды Сервис  Параметры. В результате можно скрыть или показывать строку формул, рабочую сетку ячеек, строку состояния, показывать
формулы в ячейках или вычисленные по ним значения, осуществить
настройку панелей инструментов, управлять масштабом изображения таблицы и т.д.
Работу с панелями инструментов облегчают подсказки с названиями
кнопок панелей. Чтобы отобразить или скрыть всплывающие подсказки, вы-
9
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
берите команду Сервис  Настройка, а затем установите или снимите
флажок Отображать подсказки для кнопок на вкладке Параметры.
Управление интерфейсом пользователя возможно также через контекстно-зависимое меню (см. раздел 8.2. Запуск макроса).
Изменение размеров и перемещение окон производится согласно пр авилам, принятым в среде Windows.
2.2.
Адресация и выделение ячеек
Каждая ячейка определяется своим адресом в таблице – индексом
столбца (A, B, C…) и номером строки (1, 2, 3…), на пересечении которых она
находится. Например, ячейка в столбце А в первой строке имеет адрес А1, а
ячейка в том же столбце, но во второй строке – А2.
Этот способ записи адресов ячеек используется в MS Excel по умолчанию и называется стилем «A1». Другой способ называется стилем «R1C1». В
этом случае указание номера строки и номера столбца следует после букв R и
C, соответственно. Например, R5C3 – адрес ячейки, стоящей на пересечении
5-й строки с 3-м столбцом (равнозначно адресу C5 при первом способе записи). Изменение способа адресации осуществляется настройкой параметров
функционирования MS Excel командой Сервис  Параметры  Общие 
Стиль ссылок. В данном пособии используется стиль «A1».
В электронных таблицах можно работать как с отдельными ячейками,
так и с группами ячеек, которые образуют блок. Адреса ячеек в блоках разделяются двоеточием « : », например блок А1:В4 включает в себя ячейки А1,
А2, А3, А4, В1, В2, В3 и В4. Блоку ячеек может быть присвоено имя. С блоками ячеек в основном выполняются операции копирования, удаления, пер емещения, вставки и т.п.
Выделение (выбор) – очень важное понятие, необходимое для практической работы. В MS Excel действует правило: для того чтобы обработать
данные, их нужно сначала выделить.
Щелкните мышью по ячейке А1. Ее окружит темная рамка (табличный
курсор) – признак выделения. Теперь в ячейку можно вводить данные. Введите в нее какое-нибудь число и то же самое последовательно проделайте для
ячеек А2 и А3. Теперь, если вы выделите ячейку А1 и, не отпуская кнопку
мыши, потяните указатель вниз до ячейки А3, то весь введенный столбец
чисел попадет в выделение. Во время выделения указатель мыши имеет вид
жирного белого крестика. Для выделения блока ячеек необходимо устан овить курсор в ячейку, начиная с которой выполняется выделение, нажать левую кнопку мыши и протащить курсор, закрашивая в темный цвет область
выделения.
Для быстрого перемещения табличного курсора в требуемую ячейку
можно пользоваться окном Переход, вызов которого осуществляется нажати-
10
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ем функциональной клавиши F5. В открывшемся окне в поле Ссылка ввести
адрес ячейки и нажать клавишу
или Enter.
Выделение всех ячеек листа достигается нажатием кнопки в левом
верхнем углу листа (на пересечении заголовков строк и столбцов). Выделить
все ячейки строки (столбца) можно щелкнув мышью на заголовке этой стр оки (столбца). Чтобы выделить одновременно несколько смежных строк
(столбцов), щелкайте на заголовках крайних позиций, придерживая нажатой
клавишу Shift. Чтобы выделить несколько отдельных строк (столбцов), щелкайте на заголовках выбранных позиций, придерживая нажатой клавишу Ctrl.
2.3. Ввод, редактирование и копирование данных
Содержимым ячейки электронной таблицы может быть либо текст,
числовое значение или формула. Текст и числа рассматриваются как ко нстанты, поэтому их изменение возможно лишь только редактированием соо тветствующих ячеек. Формулы же автоматически пересчитывают свои знач ения при изменении хотя бы одного аргумента.
При вводе чисел используются цифры и знаки: +, - , разделитель целой
или дробной части (, или .), %. Выбор разделителя (, или .) зависит от устан овок стандарта представления чисел, указанных в Панели управления → Язык
и стандарты.
Числовые константы разделяются на целые, вещественные, даты, время. Вещественные константы можно записывать в форме с фиксированной
запятой и в форме с плавающей точкой. Представление числа в форме с фиксированной запятой содержит мантиссу и порядок, записанные через лати нскую букву E (прописную или строчную). Мантисса может быть записана как
целая константа или константа с фиксированной запятой, а порядок только
как целая константа. Числовая константа в экспоненциальной форме трактуется как мантисса умноженная на 10 в степени, равной порядку. Например,
число 1000000 имеет вид 1Е+06.
При вводе значения в формате Общий (см. рис. 6) автоматически подбирается подходящий формат – фиксированный или экспоненциальный. Но
пользователь имеет возможность изменить отображение числа (см. раздел
2.8. Форматирование ячеек). Если число превышает размер ячейки, то оно
отображается в виде последовательности знаков #.
Даты и время вводятся в ячейки электронной таблицы как числа, так
как они могут быть использованы в качестве аргументов для формул. Расп ознаются следующие форматы для даты: 31/12/99 или 31.12.99. или
31/декабря/99 или 31 декабря 99 и форматы для времени: 12:12 или 12:12:04.
Чтобы быстро ввести в ячейки текущее число, выделите ячейку и нажмите
клавиши [Ctrl] +[;], а для ввода текущего времени – [Ctrl] + [:].
После ввода числовые данные выравниваются по правой границе яче йки.
11
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Любая введённая строка символов, которая не является числом или
формулой, воспринимается как текст. Текстовые данные, начинающиеся с
цифры или со знаков /, +, -, (, $, @, #, необходимо предопределять апострофом. Например, «/Иванов/».
Если введенный текст не умещается в ячейке, то он занимает соседние
пустые ячейки. В случае отсутствия таких ячеек текст усекается. Текст можно увидеть, расширив ячейку, или сделав её текущей (тогда в строке формул
появится её полное содержимое).
Длинный текст можно разбить в ячейке на несколько строк нажатием
комбинации клавиш Alt + Enter.
После ввода текстовые данные автоматически выравниваются по левой
границе ячейки. Это положение можно изменить форматированием ячеек
(см. раздел 2.8. Форматирование ячеек).
При вводе числовых и текстовых данных необходимо выполнить следующее: выделить ячейку, ввести данные с клавиатуры и закончить ввод.
Окончание ввода осуществляют нажатием клавиши Enter, или щелчком мыши в любой другой ячейке, или щелчком по кнопке с зеленой галочкой в
строке формул.
Для ввода значений в диапазоне ячеек рекомендуется сначала выделить
требуемые области ввода и вводить значения, завершая их нажатием клавиши Enter. При этом курсор будет автоматически перемещаться от одной
ячейки к другой в соответствии с установкой направления перемещения (команда Сервис→ Параметры → Правка → Переход к другой ячейке после
ввода в направлении).
Для отмены ввода в ячейку набранного значения следует нажать клавишу Esc или щёлкнуть на кнопку с красным крестом в строке формул.
Для редактирования данных необходимо дважды щелкнуть в ячейке,
или щелкнуть на строке формул, после чего изменить значение и нажать клавишу Enter.
Для копирования содержимого ячеек необходимо выбрать копируемую
ячейку или выделить диапазон ячеек, скопировать информацию в буфер о бмена (команда Правка → Копировать), переместить курсор в ячейку назначения и вставить данные из буфера обмена (команда Правка → Вставить).
Для быстрого копирования целесообразно воспользоваться соответствующ ими кнопками панели инструментов Стандартная (см. раздел 3.3.Технология
создания электронной таблицы).
Для заполнения ячеек одним и тем же значением данных рекомендуе тся выполнить следующее. Выбрать исходную ячейку и установить курсор
мыши на квадратик в правом нижнем углу ячейки (маркер заполнения). Когда курсор примет вид черного крестика, следует нажать левую кнопку мыши
и растянуть бледно-серую рамку, охватывая область, куда необходимо копировать данные.
12
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2.4.
Ввод, редактирование и копирование формул
В ячейку можно вводить не только текст и числовую информацию, но
и формулы. Формула – это математическая запись вычислений, производимых над данными таблицы. С помощью формул можно, например, складывать, умножать и сравнивать данные одного или нескольких листов, то есть
формулами следует пользоваться, когда необходимо ввести в ячейку текущего листа вычисляемое значение. В формуле могут использоваться внешние
ссылки – ссылки на ячейки других листов текущей рабочей книги или на
ячейки листов других рабочих книг.
Таким образом, например перемножение в формуле двух адресов ячеек
означает перемножение числового содержимого этих ячеек и помещение
результата перемножения в третьей ячейке, в которой и записана эта форм ула. В сущности, формула является инструкцией по выполнению последовательности операций. Она может включать в себя функции, адреса ячеек и
диапазонов, константы, имена ячеек или диапазонов, соединенные круглыми
скобками и знаками операций.
Ввод формулы начинается с символа « = » (знак равенства). Именно
этим знаком вы отличаете ввод формулы от ввода текста или просто числового значения. Символы адресов ячеек, входящих в состав формулы, можно
либо набирать с клавиатуры, либо можно помещать в формулу, щелкая п оследовательно по ячейкам с соответствующим адресом.
Завершается ввод формулы нажатием клавиши Enter. В ячейке при
правильном вводе формулы появляется результат вычисления. Саму форм улу можно увидеть теперь в Строке формул (см. рис. 1). Можно задать также
отображение вида формулы в ячейке вместо значения результата, применив
команду Сервис → Параметры → Вид → Формулы.
Результат любой части формулы можно просмотреть следующим образом. Выделите необходимый фрагмент в Строке формул и нажмите на клавиатуре клавишу «F9». Для отмены просмотра результата следует нажать
клавишу Esc . Если этого не сделать, то первоначальный фрагмент формулы
будет заменен результатами.
Для быстрого задания формул можно использовать механизм Автовычислений. Для его запуска достаточно выделить ячейки и вызвать контекстно-зависимое меню Строки состояния (см. рис. 1). Контекстное меню содержит опции для расчета: Сумма, Среднее, Максимум, Минимум и т.д.
Чтобы отредактировать уже имеющиеся данные в ячейке, дважды
щелкните на ней мышью или, выделив ее, нажмите на клавиатуре клавишу
«F2». Вводить данные в ячейку и редактировать их можно как непосре дственно в ячейке, так и в Строке формул.
Если формула введена с ошибками или вычисление результата по
формуле невозможно, то в ячейке появляется сообщение об ошибке (см.
табл. 1).
13
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Таблица 1.
Сообщения об ошибках в формуле
Текст
сообщения
Возможная причина
# ДЕЛ/0!
Деление на ноль
# ССЫЛКА!
Указана некорректная ссылка
# ЧИСЛО!
# ПУСТО!
# ЗНАЧ!
# ИМ Я!
# Н/Д!
Невозможность вычисления
значений (например, извлечение квадратного корня из
отрицательного числа)
Неверно указано пересечение
диапазонов ячеек (нет общих
ячеек)
Задан аргумент недопустимого типа
Указано недопустимое имя
операнда (например, функции
или диапазона)
Нет данных в ячейке, на которую сделана ссылка
Способ устранения
Проверить содержимое влияющей ячейки
Проверить ссылки, особенно
внешние: правильно ли указан путь, не был ли переименован файл, лист и т.д.
Проверить правильность задания аргументов функций
Задать правильно диапазон
ячеек
Уточнить типы аргументов
для применяемой функции
Проверить правильность
написания имен
Проверить содержимое влияющей ячейки
Экономические таблицы содержат в пределах одной графы (или стр оки) однородные данные, то есть данные одного типа и структуры. К примеру,
все величины в одной графе, как правило, рассчитываются по одинаковым
формулам. Так, если в столбце G необходимо просуммировать содержимое
столбцов E и F (см. рис. 1-2), то необходимо в ячейку G1 поместить формулу
=E1+F1, в ячейку G2 формулу =E2+F2, в ячейку G3 формулу =E3+F3 и т.д.
Поэтому достаточно ввести формулу в одну ячейку и скопировать ее в ячейки всего столбца (или строки).
В MS Excel копирование может производиться несколькими способ ами. Чтобы скопировать указанную формулу суммирования из ячейки G1 в
блок ячеек G2:G5 необходимо выделить ячейку G1, выбрать в строке Меню
команду Правка  Копировать. MS Excel окружит ячейку G1 движущейся
рамкой. Выделите блок ячеек G2:G5 как область вставки и выберите команду
Правка  Вставить. При копировании MS Excel автоматически корректирует адреса ячеек. Убедитесь в этом, для чего переведите курсор к ячейке G2
и посмотрите строку ввода. Там сейчас находится формула =E2+F2. В ячейке
G3 формула =E3+F3 и т.д. Другой способ копирования формул с помощью
Маркера заполнения описан в разделе 2.3 (последний абзац) и в пункте 8 раздела 3.3. Технология создания электронных таблиц.
14
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2.5.
Автоматизация построения функций
Табличный процессор MS Excel оперирует большим числом встроенных функций для различных типов вычислений (функции математические,
статистические, логические, текстовые, информационные и др.). Функция в
MS Excel имеет имя, записываемое прописными буквами, например, Сумм( )
для суммирования или СРЗНАЧ( ) для подсчета среднего значения. В ско бках помещается аргумент, содержащий отдельные адреса или диапазон ячеек
с соответствующими данными. Аргумент, в свою очередь, может включать
другие, так называемые вложенные функции, например, =SIN(LN(…)).
Любую функцию можно ввести непосредственно в Строке формул
(см. рис. 1) с помощью клавиатуры. Табличный процессор MS Excel имеет
мощное средство автоматизации построения функций – Мастер функций.
Его использование упрощает
ввод функций и снижает количества ошибок. Мастер функций
позволяет построить любую комбинацию функций, причем отслеживает правильность завершения каждой из них.
Перечисление назначения
только нескольких функций из
категории финансовых расчетов
показывает, какие широкие возможности предоставляет
Рисунок
3.
использование Мастера функций: функция НОМИНАЛ вычисляет ном инальную годовую процентную ставку; функция ДОХОД позволяет рассч итать годовую ставку помещения по операциям с ценными бумагами; функция СКИДКА вычисляет норму скидки – учетную ставку для ценных бумаг и
др.
Мастер функций запускается либо кнопкой
, расположенной на панели инструментов Стандартная, либо командой Вставка  Функция.
Действия в Мастере
функций состоят из двух шагов.
Окно для первого шага, показанное на рис. 3, позволяет выбрать требуемую функцию из
весьма обширного
списка.
Функции разбиты на категории, поэтому нужно выбрать
категорию, а затем –
Рисунок 4.
функцию. В нижней части окна
15
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
выводится подсказка: шаблон для функции, который помогает понять правила ее записи, и описание назначения функции.
Вид окна на втором шаге Мастера функций показан на рис. 4. Здесь от
пользователя требуется задать аргумент (или аргументы) функции. В этом
окне также присутствует подсказка, но кроме словесного описания функции
в окне размещена еще дополнительная информация о типе аргумента.
Например, для функции EXP аргументом может быть числовая константа
или ссылка на ячейку, в данном случае A1 (см. рис. 4). Для того чтобы ввести
функцию для строки или столбца данных, достаточно записать ее в первую
ячейку диапазона, а затем скопировать функцию в остальные яче йки.
Дополнительные возможности по редактированию формулы в Строке
формул с вставкой необходимой функции предоставляет вызов Мастера
функций щелчком мыши по знаку равенства «=» в Строке формул.
2.6.
Абсолютные и относительные ссылки
Ссылка – это запись адреса ячейки в составе формулы. Используемые в
формулах ссылки на ячейки могут быть трех типов: абсолютные, относ ительные и смешанные.
Абсолютная ссылка задает абсолютные координаты ячейки на рабочем
листе. При перемещении или копировании формулы из одной ячейки в др угие такая ссылка в формуле не изменяется и всегда указывает на одну и ту же
ячейку таблицы. Для записи абсолютной ссылки используется знак $, напр имер $B$5, $C$4 и т.д.
Относительная ссылка записывается без знака $, например A1, D5, J3
и т.д. При вычислении формулы такие ссылки, так же как и абсолютные, указывают на координаты ячеек таблицы, участвующих в операциях. Например,
по формуле =A2+B2+C2, так же как и по формуле = $A$2+$B$2+$C$2, осуществляется суммирование содержимого первых трех ячеек второй строки
рабочего листа. Отличие заключается в том, что при копировании формулы с
относительными ссылками в другое место последние изменяются. Измен ения определяются новым положением формулы в таблице (см. выше раздел
2.4. Ввод редактирование и копирование формул).
Смешанная ссылка – это ссылка, в которой адресация строки отличается от адресации столбца. Например, A$3 – смешанная ссылка с указанием
относительной адресации столбца и абсолютной адресации строки.
Таким образом, отличие абсолютных ссылок от относительных наблюдается только при копировании, а применяются абсолютные ссылки тогда,
когда требуется, чтобы адрес ячейки при копировании был сохранен. Типи чным примером использования абсолютных ссылок является пример со здания
электронной таблицы прайс–листа фирмы. При этом значение курса доллара
помещается в ячейку, для которой используется абсолютная ссылка.
16
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
При работе с большими объем ами данных одного рабочего листа может быть недостаточно. И дело не в том, что не останется свободных ячеек, а
в том, что обрабатывать эти данные будет неудобно. При помощи нескольких рабочих листов можно структурировать информацию, повышая ее
наглядность, и, более того связывать
данные между собой, используя в формулах адреса ячеек, которые расположены на другом листе. Чтобы перейти
на другой лист книги, достаточно
щелкнуть мышью на его ярлычке (см.
рис. 2).
Рисунок 5.
Дополнительные возможности при работе с несколькими листами
предоставляет контекстно-зависимое меню, которое всплывает на экране
при щелчке правой кнопкой мыши по ярлычкам листов. Вид контекстного
меню и соответствующие команды представлены на рис. 5.
2.7. Присвоение имен объектам рабочих листов
Для наглядности в понимании сложной структуры формул без допо лнительных объяснений рекомендуется присваивать имена ячейкам, диапазонам ячеек и константам MS Excel.
Для присвоения имени ячейке или диапазону ячеек необходимо осущ ествить действия. Предварительно именуемый объект выделяется на рабочем
листе. Затем выберите команду Вставка → Имя → Присвоить и наберите
нужное имя в поле Имя. Другой способ связан с использованием Поля имени
(см. рис. 1). Выделив адрес в Поле имени, наберите новое имя и нажмите клавишу Enter.
Для присвоения имени константе выберите команду Вставка → Имя
→ Присвоить и наберите имя константы в поле Имя. Затем в Строке формул
наберите значение, которое хотите присвоить константе. Для о пределения
константы Поле имен использовать нельзя.
В формулах можно использовать имена вместо адресов ячеек или диапазонов. При этом следует воспользоваться командой Вставка → Имя →
Вставить и выбрать нужное вам имя в списке Имя диалогового окна Вставка имени. В случае неоднократного использования формулы в рабочем листе
целесообразно присвоение ей им ени. Достигается это командой Вставка →
Имя → Присвоить. При этом в поле Имя задается имя формулы, а в Строке
формул - сама формула.
При выборе имени рассматриваемых объектов можно пользоваться
прописными и строчными буквами, например: Общий Доход, ИнвКодТовара.
Не рекомендуется употреблять только прописные буквы (традиционно им
пишутся названия функций MS Excel). В именах можно использовать циф17
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ры, но они не должны быть первыми символами имен. Имена не должны
совпадать с адресами ячеек рабочего листа в стиле адресации « A1».
Продемонстрируем преимущества присвоения имен ячейкам и константам на примере. Взгляните на такую формулу [13]:
=ЕСЛИ(И(B12<3000;A12<5);C14* D14*0,05; C14*D14*0,075)
Не меньше 15 минут понадобиться вам для того, чтобы понять, для ч его предназначена эта формула, даже если вы знаете основное назначе ние таблицы. Если же использовать имена для ячеек и констант, то эту же фо рмулу
можно представить в виде
=ЕСЛИ(И(ГодДоход<3000;ГодПринятия<5);Кол_во*Цена*МинКомис;
Кол_во*Цена*МаксКомис),
и для ее понимания вам потребуется не более 15 секунд. Совсем нетрудно понять, что по этой формуле вычисляется:
«Если в течение этого года доходы данного сотрудника были меньше
3000 долларов, и этот сотрудник был принят на работу менее 5 лет назад, то
прибыль от суммы, получаемой в результате реализации, ниже получаемых
им комиссионных; в противном случае прибыль от суммы – выше комиссионных».
2.8.
Форматирование ячеек
Форматирование – это задание определенных параметров для внешнего представления данных, записанных в одной или нескольких ячейках.
Ячейки рабочего листа имеют заданный формат, который устанавливается
командой Формат Ячейки с помощью строки Меню. Эта команда имеет
несколько вкладок.
Вкладка Число, изображенная на рис. 6, определяет категории знач ений в ячейке и задание кодов формата.
Рисунок 6.
Рисунок 7.
18
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Вкладка Выравнивание представлена на рис. 7. Из рис. 6-7 видно, что к
параметрам форматирования относятся вид и размер шрифта, ра мка, цвет,
выравнивание содержимого ячейки по горизонтали и по вертикали, перенос
по словам, ориентация текста в ячейке, объединение яч еек и т.д.
В MS Excel для отделения дробной части числа часто применяется запятая. Тогда числа, в которых точки выступают в качестве разделителей,
воспринимаются программой как текст и не могут быть использованы в вычислениях. С целью увеличения числа дробных знаков для выделенных яч еек
применяйте команду Увеличить разрядность с помощью клавиши
на панели Форматирование.
В случае появления в ячейке набора символов типа ######, увеличьте
ширину этого столбца до появления ее содержимого в обычном численном
виде.
2.9. Вставка и удаление строк таблицы
В процессе работы с таблицей уже на этапе редактирования созданного
шаблона (см. контрольные задания 3.1-3.3) у вас возникнет необходимость
добавить в нее новые строки и удалить ненужные. Для создания новой стр оки установите курсор на строку, над которой требуется создать новую, и выберите, используя Меню, команду Вставка  Строки.
Удаление строки может происходить по-разному. Если требуется удалить только данные из строки, то есть сделать ее пустой, то надо выделить ее
и нажать на клавиатуре Del. Если надо удалить не только данные, но и саму
строку рабочего листа, то надо выделить строку, щелкнув по ее заголовку, и
выбрать команду Правка  Удалить. Так же можно удалять наборы смежных и несмежных строк, используя для выделения клавиши Shift и Ctrl (см.
раздел 2.2). При подобных операциях номера строк листа корректируются
автоматически.
2.10. Получение справочной информации
Встроенный
справочник MS Excel содержит более
4000 разделов (статей), поясняющих
работу программы и содержащих
рекомендации по применению тех
или иных средств. Активнее используйте справку для поиска необходимых команд и усвоения основных
понятий. Чтобы запустить справку и
вызвать окно Справочной системы,
изображенное на рис. 8, необходимо
использовать знак «?» или пункт
Справка в строке Меню.
Рисунок 8.
19
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Как следует из рис. 8, поиск нужной информации может быть выпо лнен тремя способами: по содержанию, по предметному указателю и по ключевым словам. Результат поиска в значительной степени зависит от правильного выбора ключевых слов, которые заносятся с клавиатуры в первую вер хнюю строку окна. Так, например, чтобы узнать команды, приведенные в
предыдущем разделе 2.9. Вставка и удаление строк таблицы, достаточно
использовать при вкладке Предметный указатель ключевые слова «вставка»
и «удаление».
Для получения справочной информации о различных элементах экрана
в
MS
Excel необходимо
использовать кнопку
«Что
это
такое?» после включения справки «?» в строке Меню.
Если
нажать эту кнопку, указатель мыши примет вид стрелки с вопросительным
знаком. Подведите указатель к элементу экрана, справку о котором вы хотите
получить, и нажмите кнопку мыши.
Контрольные задания по разделу 2.
Задание 2.1. Вначале введите число «1» в ячейки в формате по умолчанию
Общий, а затем осуществите последовательно форматирование ячеек, используя вкладку Число (см. рис. 6) так, чтобы результат соответствовал данным представления «1» в различных форматах, приведенным на рис. 9.
Задание 2.2. Запишите в ячейку A3 согласно рис. 10 формулу =A1+A2, т.е.
сумму элементов первого столбца, а в ячейку D1 – формулу =A1+B1+C1, т.е.
сумму элементов первой строки. Заметим, что обе формулы содержат относительные ссылки.
Рисунок 9.
Рисунок 11.
Рисунок 10.
20
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
При вычислении по этим формулам в ячейках A3 и D1 будут отображены значения 79 и 56, соответственно. Скопируйте формулу из ячейки A3 в
ячейки B3 и C3. Убедитесь, что в них появятся формулы =B1+B2 и =C1+C2,
т.е. суммы второго и третьего столбца соответственно. Убедитесь, что при
копировании формулы из ячейки D1 в ячейку D2 в последней будет записана
формула =A2+B2+C2, т.е. сумма второй строки 68.
Измените ссылки в обеих исходных формулах. В ячейку A3 запишите
формулу =$A$1+A2, а в ячейку D1 формулу =A1+$B$1+C$1. Скопируйте
первую формулу в ячейки B3 и C3, и убедитесь, что в них появятся записи
=$A$1+B2 и =$A$1+С2, соответственно значения расчета 57 и 34. При копировании второй формулы в ячейку D2, убедитесь, что в последней появится
запись =A2+$B$1+C$1 и результат расчета 67.
Задание 2.3. Используя Мастер функций, рассчитайте значения синуса для
блока данных A2:A6, поместив результат в блок B2:B6. Рассчитайте значения косинуса для блока B2:B6, найдите средние, максимальные и минимальные значения, разместив результат так, как показано на рис. 11. Для автом атизации заполнения значений аргумента в диапазоне от 0,3 до 1,2 с шагом 0,3
используйте команду Правка  Заполнить  Прогрессия  (Расположение
- по столбцам)  (Шаг – 0,3)  OK , предварительно установив начальное
значение 0,3 и выделив диапазон заполнения.
Тесты по разделу 2.
1. Для записи адресов ячеек в MS Excel используются стили:
а) R1C1;
б) 1A;
в) A1;
г) 1R1C.
2. Маркер заполнения для копирования формул в смежные ячейки электро нных таблиц MS Excel расположен в:
а) верхнем левом углу ячейки;
б) верхнем правом углу ячейки;
в) нижнем правом углу ячейки;
г) нижнем левом углу ячейки.
3. Маркер заполнения используется для:
а) копирования формул;
б) заполнения ячеек одним и тем же значением данных;
в) выделения требуемого диапазона ячеек с данными.
4. Адрес ячейки, стоящей на пересечении 3-ей строки и 5-го столбца, может
быть представлен как:
а) C3R5;
б) R5C3;
в) C5R3;
г) R3C5.
5. Контекстное меню ячейки содержит опции для расчета:
а) Минимум;
б) Среднее;
в) Сумма;
г) Максимум.
6. Если формула введена с ошибками или вычисление р езультата по формуле
невозможно, то в ячейке:
а) появляются данные с красной заливкой;
б) вид ячейки не изменяется;
в) появляется сообщение об ошибке; г) появляются некорректные данные.
7. Типичные ошибки при вводе формул в ячейки электронной таблицы:
а) нет данных в ячейке, на которую сделана ссылка;
21
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
б) невозможность вычисления (например, извлечение квадратного корня из
отрицательного числа);
в) задан аргумент недопустимого типа;
г) деление на ноль.
8. Смешанная ссылка - это ссылка,
а) в которой адресация строки отличается от адресации столбца (например,
$B5);
б) в которой взаимодействуют одновременно относительная и абсолютная
ссылки.
9. Совпадут ли результаты вычислений, выполненные по формулам =B7/A8
и =$B7/A$8:
а) да;
б) нет;
в) сравнение в принципе некорректно.
10. Действия в Мастере функций состоят из:
а) двух шагов;
б) трех шагов;
в) четырех шагов.
3. СОЗДАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ
Можно привести большое количество примеров, когда требуется с оставить таблицу с промежуточными и общими итогами данных по графам.
Такие сводки часто используют в торговле, на производстве, в науке. Разработчики программного обеспечения учитывают этот факт, и многие пр ограммы специально ориентированы на создание таких таблиц. Например,
генераторы отчетов в СУБД dBASE или FoxPro. Но именно в системе электронных таблиц наиболее предпочтительно использовать имеющиеся сре дства для генерации сводок.
3.1.
Состав электронных таблиц
Любая электронная таблица состоит из следующих элементов:
 заголовка таблицы;
 заголовков столбцов («шапки» таблицы);
 информационной части (исходных и выходных данных, расположенных в соответствующих ячейках).
3.2.
Этапы проектирования электронных таблиц
В общем виде процесс проектирования электронной таблицы включает
следующие шаги:
 ввод заголовка электронной таблицы;
 ввод названий граф документа;
 ввод исходных данных;
 ввод расчетных формул;
22
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»


формирование электронной таблицы с целью придания ей профе ссионального вида;
подготовку электронной таблицы к печати и ее печать.
3.3. Технология создания электронной таблицы
Для практического освоения приемов работы в MS Excel предлагается
создать электронную таблицу, используя пример решения задачи учета товарооборота. Допустим некая мини-пекарня реализует свою продукцию через
три торговые точки: две булочные и одно кафе. Требуется создать сводку о
реализации товара за один день.
Разобьем операцию по формированию таблицы на шаги. Принцип, по
которому создается эта таблица, можно использовать при решении самых
разных пользовательских задач.
Выполняем поэтапно все перечисленные ниже действия:
1. Для запуска программы MS Excel в среде Windows 98 нажмите в
левом нижнем углу окна на кнопку Пуск и в разделе Программы щелкните
мышью на пункте Microsoft Excel. В этих манипуляциях используется левая
кнопка мыши. После запуска MS Excel на экране появится стандартное окно
Windows-программы. В центре этого окна располагается рабочий лист, состоящий из отдельных ячеек.
2. Увеличьте высоту строки 1 вдвое. Для этого установите курсор
мыши в области заголовков (то есть номеров) строк на границу между пе рвой и второй строками. Курсор должен принять вид крестика с вертикальными стрелками. Нажав кнопку мыши, потяните вниз.
3. Аналогично увеличьте вдвое ширину колонки А.
4. Напечатайте в строке 1 заголовок, как показано на рис. 12 (“Реализация хлебобулочных изделий”). Выделите этот текст и установите высоту
шрифта 12 (используйте шрифт Arial Cyr).
Для центрирования текста (в общем случае любых данных) по столбцам примените команду Объединить и поместить в центре нажатием
кнопки
и нажмите кнопку
для оформления выделенного текста полужирным шрифтом. Обе кнопки находятся на панели Форматирование. Для
всех других заголовков используйте шрифт высотой 10 пунктов.
5. Введите во второй строке названия граф таблицы. Выделите их и
нажмите кнопки
и
(полужирный курсив).
6. В третьей строке введите название булочной. Выделите диапазон
A3:D3 и нажмите кнопку Объединить и поместить в центре.
7. В следующих строках введите наименование, количество и цену товара. Количество этих строк может быть любым.
23
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
8. Для расчета численного значения
в графе
«Сумма», как произведения
значений, помещенных в
графах «Количество» и «Цена», необходимо ввести формулу. Подробно ввод формул
описывается выше (см. раздел 2.4. Ввод, редактирование и копирование формул).
Рисунок 12.
Выделите ячейку D4. Введите в нее формулу =B4*C4 либо с клавиатуры, переключившись на символы латинского алфавита, либо щелчками
левой кнопки мыши по ячейкам B4 и C4. Начинается ввод формулы с символа < = > (знак равенства) и завершается - нажатием клавиши Enter.
Чтобы не повторять эту процедуру для последующих строк, скопиру йте введенную формулу в ячейки столбца D5 и D6. Установите курсор на
маркер заполнения (см. разделы 2.3. и 2.4.) ячейки D4 (черный квадратик в
правом нижнем углу ячейки) – курсор должен превратиться в черный крестик. Нажав кнопку мыши, растяните бледно-серую рамку – она показывает
диапазон ячеек, которые участвуют в операции заполнения – вниз так, чтобы
все строки с названиями товара попали в диапазон заполнения. Теперь в гр афе «Сумма» должны оказаться записанными формулы с автоматически и справленными ссылками на ячейки.
9. Слово «Итого» отформатируйте так же, как заголовки граф в пункте 5.
10. Выделите ячейку D7 в итоговой строке и нажмите кнопку
на панели инструментов Стандартная, что соответствует выполнению команды Автосумма, а затем клавишу Enter.
11. Не заполняя ячейки строки 8, введите слово «Всего» в строке 9 и
отформатируйте кнопкой Ж (полужирный шрифт).
12. Выделите диапазон ячеек, которые входят в таблицу – в нашем
примере это A1:D9, - и используйте список команды Границы на панели
Форматирование для создания рамки таблицы. Выберите в этом списке второй значок в нижнем ряду
.
13. Выделите строку, содержащую слово «Всего», щелкнув по заголо вку строки (то есть по цифре 9). Выберите команду Вставка  Строки. В
таблицу будет добавлена пустая строка.
Таблица, которая получилась у нас к этому моменту, как раз и показана
на рис. 12.
Для того чтобы вставить в таблицу данные по другим торговым то чкам, целесообразно использовать буфер обмена.
24
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
14. Выделите диапазон ячеек, которые содержат данные по одной
торговой точке (A3:D7). Скопируйте его в буфер обмена командой [Ctrl] +
[C] или кнопкой
на панели инструментов Стандартная.
Скопированные ячейки окружены теперь штриховой рамкой.
15. Для ввода блока ячеек
внутрь таблицы в данном случае
нельзя
использовать
команду
вставки из буфера [Ctrl] + [V]
или кнопку,
потому что строки, лежащие
ниже, будут затерты. Поступим иначе. Выделите ячейку А8 в пустой
строке, которую вы вставили, выполняя
пункт 13.
Выберите
команду Вставка  Скопированные
ячейки, доступную только тогда,
когда в буфер скопированы ячейки.
Эту команду можно использовать
только один раз, для повторной
вставки необходимо снова скопировать ячейки в буфер обмена.
Рисунок 13.
16. Выполните действия, описанные в
пунктах 14 и 15. Отредактируйте данные для второй и третьей торговых точек так, как показано на рис. 13.
Осталась не вычисленной сумма в строке «Всего». Для получения р езультата необходимо просуммировать значения из строк «Итого». Кнопка
команды Автосумма здесь не поможет – для несмежных ячеек автосуммирование не работает. Поэтому надо записать в ячейке D20 формулу
=D7+D13+D18. Даже если вы будете вставлять новые строки в таблицу или
удалять существующие строки, программа автоматически исправит ссылки
на ячейки в тех формулах, где это необходимо.
17. Сохраните таблицу на диске для ее дальнейшего просмотра, распечатки на принтере и многократного редактирования. Открыв меню Файл и
применив команду Сохранить как, сохраните таблицу на выбранном вами
диске и каталоге, например под именем «Таблица-сводка». В последующей
работе для сохранения файла Таблица-сводка на диске будет достаточно
щелкнуть кнопку Сохранить
или выбрать команду Файл  Сохранить.
18. Полученную таблицу можно многократно использовать, добавляя
или удаляя строки (см. раздел 2.9. Вставка и удаление строк таблицы), изменяя содержимое ячеек, то есть, меняя цены и количество товара. Все итоговые цифры будут пересчитываться уже автоматич ески.
При вставке новой строки она оказывается пустой, то есть в нее автоматически не переносятся формулы из смежных ячеек. Поэтому для расчета
25
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
данных в графе «Сумма» новой строки необходимо заново использовать
маркер заполнения для копирования в эту строку формул, как это делается в
пункте 8.
В приведенных ниже заданиях 3.1-3.3 выполните указанные действия
редактирования таблицы - сводки, которые отражают реальную ежедневную
практику работы коммерсанта. Используйте приемы, изложенные в разделе
2.9. Вставка и удаление строк таблицы.
Контрольные задания по разделу 3.
Задание 3.1. В электронной таблице (см. рис. 13) между данными для первой и второй торговыми точками сделать вставку для торговой точки – Кафе
“Осень” с ассортиментом: калач московский в количестве 115 по цене
1113,23 руб., хлеб ситный с изюмом в количестве 150 по цене 1077,37 руб.,
хлеб бородинский в количестве 111 по цене 1321,15 руб. В торговой точке булочная №1 - исключить из ассортимента лаваш, а в чайной “Золотой сам овар” добавить в ассортимент бублики простые в количестве 113 по цене
351,12 руб.
Ответ: Всего = 1258951,16 руб.
Задание 3.2. В электронной таблице (см. рис. 13) перед данными для первой торговой точки сделать вставку для торговой точки – Кафе “Весна” с
ассортиментом: калач московский в количестве 99 по цене 1113,23 руб.,
хлеб ситный с изюмом в количестве 217 по цене 1077,37 руб., хлеб бородинский в количестве 211 по цене 1321,15 руб. В торговой точке - булочная
№2 - исключить из ассортимента хлеб ржаной, а в чайной “Золотой самовар”
добавить в ассортимент бублики простые в колич естве 312 по цене 351,12
руб.
Ответ: Всего = 1425311,15 руб.
Задание 3.3. В электронной таблице (см. рис. 13) перед данными для чайной сделать вставку для торговой точки – Кафе “Лето” с ассортиментом: калач московский в количестве 199 по цене 1113,23 руб., хлеб ситный с
изюмом в количестве 917 по цене 1077,37 руб., хлеб бородинский в колич естве 198 по цене 1321,15 руб. В торговой точке - булочная №1 - исключить
из ассортимента хлеб городской, а в чайной “Золотой самовар” добавить в
ассортимент бублики простые в количестве 517 по цене 351,12 руб.
Ответ: Всего = 2405597,80 руб.
26
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Задание 3.4. Сформируйте электронную таблицу по расчету заработной платы следующего вида, представленного на рис. 14. При выполнении задания
Рисунок 14.
используйте команды форматирования ячеек (см. рис. 7 раздела 2.8. Форматирование ячеек) для переноса по словам (построчное расположение слов в
одной ячейке) и центрирования текста в ячейках.
Примените команду предварительного просмотра Файл  Предварительный просмотр, которая, как правило, используется перед распечаткой
созданной таблицы. Убедитесь, что последняя графа «Итого к выдаче» не
умещается на одной странице. Чтобы расположить всю таблицу в пределах
одной страницы необходимо применить команду Вид  Разметка страницы
и изменить положение разрыва страниц (синяя пунктирная линия), перетаскивая ее вправо с помощью мыши.
Задание 3.5. Для примера задания 3.4 рассчитайте среднее значение окладов
в ячейке C9 (см. рис.
15), используя Мастер
функций. Первый и
второй шаги Мастера
функций изображены
для этого задания на
рис.3 и рис.15, соответственно. В окне
второго шага достаточно указать диапазон
усреднения C3:C7. Обратите внимание, что
Мастер
Рисунок 15.
функций сразу же отображает значение функции в нижней центральной ч асти окна. После нажатия кнопки Готово в ячейке C9 появится результат.
Ответ: Среднее значение окладов всех сотрудников = 1150,00 руб.
27
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Задание 3.6. Используя понятие абсолютных ссылок (см. раздел 2.6. Абсолютные и относительные ссылки) для ячейки E2, в которую помещен курс
доллара, создайте прайс–лист, изображенный на рис. 16. В оформлении используйте кнопки Цвет заливки и Цвет шрифта
панели
инструментов Форматирование.
Ответ: Всего = 2983,50 в у.е. и 76079,25 в рублях. Изменяя последовательно значение курса доллара, количество и цену товара, убедитесь в автоматическом перерасчете всех промежуточных и итоговых знач ений сумм.
Рисунок 16.
Тесты по разделу 3.
1. Таблицу с промежуточными и общими итогами данных по графам можно
составить с помощью программ:
а) FoxPro;
б) СУБД MS Access;
в) СУБД dBASE;
28
г) MS Excel.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2. Электронная таблица состоит из следующих элементов:
а) информационной части (исходных и выходных данных, расположенных
в соответствующих ячейках);
б) заголовков столбцов; в) заголовка таблицы; г) пояснительной части.
3. Этапы проектирования электронных таблиц:
а) ввод исходных данных;
б) ввод расчетных формул;
в) форматирование электронной таблицы;
г) подготовка к печати.
4. Высоту строк (или ширину колонок) увеличиваем:
а) объединяя соседние строки (или столбцы);
б) мышью, предварительно установив курсор в области заголовков строк
(или столбцов);
в) мышью, установив курсор на границу строк (или столбцов) в области
самой таблицы.
5. Кнопка Объединить и поместить в центре находится на:
а) панели Форматирование;
б) панели Стандартная;
в) в контекстном меню;
г) панели Настройка изображения.
6. Кнопка Автосумма находится на:
а) панели Форматирование;
б) панели Стандартная;
в) в контекстном меню;
г) панели Настройка изображения.
7. Для ввода блока ячеек внутрь таблицы используются команды:
а) Правка  Вставить;
б) Вставка  Объект;
в) Вставка  Скопированные ячейки; г) Правка  Специальная вставка.
8. При вставке новой строки в электронную таблицу в нее:
а) автоматически переносятся формулы из смежных ячеек;
б) автоматически не переносятся формулы из смежных ячеек.
9. Команда Вид  Разметка страницы используется чтобы:
а) расположить данные таблицы в пределах конкретных страниц перед
распечаткой;
б) отформатировать таблицу;
в) создать рамки таблицы.
10. При изменении содержимого ячеек:
а) все итоговые данные необходимо пересчитывать заново;
б) необходимо повторить команды копирования и вставки основных фо рмул для подведения итогов;
в) все итоговые цифры будут пересчитываться автоматически.
4. ПОСТРОЕНИЕ ДИАГРАММ И ГРАФИКОВ В СРЕДЕ
ТАБЛИЧНОГО ПРОЦЕССОРА MS EXCEL
Диаграммы и графики позволяют заметно повысить наглядность пре дставления числовых табличных данных в документах и электронных таблицах. Создание и изменение диаграмм можно выполнить средствами табли чного процессора MS Excel.
29
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
С помощью MS Excel удобно создавать диаграммы и графики различных типов. В сфере бизнеса диаграммы MS Excel часто используют как иллюстрации объема продаж, покупательского спроса и предложения, измен ения цен и прибыли, объемов инвестиций, производственных возможностей и
т.п.
4.1.
Основные компоненты плоской диаграммы
Диаграммы графически представляют (см. рис. 17) данные числового
типа, они являются мощным и популярным инструментом, входящим в с остав электронных таблиц, широко используются для анализа и сравнения
данных, представления их в наглядном виде.
Числовым данным рабочего листа соответствуют элементы диаграммы,
которые изображаются различными геометрическими фигурами (полосы,
линии, столбики, сектора, точки и т.п.).
Название диаграммы Легенда
Ключ легенды
Ось Y
АНАЛИЗ ПРОДАЖ
Область
построения
ПРОДАЖА
Область
диаграммы
70
60
50
40
30
20
10
0
налич.
безнал.
1
Названия
осей
2
3
4
5
6
7
ПРОДУКЦИЯ
Ось Х
Ряд данных
Рисунок 17. Основные компоненты плоской диаграммы.
При изменении исходных данных автоматически изменяется изображение элементов диаграммы по размеру или их местоположению. При изменении элемента диаграммы (увеличение или уменьшение высоты столбика, и зменение местонахождения точки и т.п.) автоматически изменяются соотве тствующие числовые значения в таблицах. Легенда представляет собой вставку окна с названиями рядов данных.
4.2.
Требования к данным
Блок данных, являющийся основой для создания диаграммы, должен
отвечать требованиям:
30
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»




Данные должны быть обобщены. Каждому числу из таблицы соотве тствует элемент диаграммы (полоска, сегмент, и т. д.), поэтому чисел не
может быть много. Исключение составляют графики, где качество
кривой тем лучше, чем больше точек использовано для ее построения.
Существует ограничение на количество строк и колонок таблицы. Если в таблице много строк, то надо постараться, чтобы столбцов было
как можно меньше. И наоборот.
Данные по различным категориям должны быть соизмеримы. Если о дна полоска имеет высоту 1 пиксел (минимальный элемент растрового
изображения), а другая – 100 пикселей, то в этом случае качество диаграммы будет невысоким. Решить эту проблему помогут выбор подходящих единиц измерения, замена абсолютных единиц измерения относительными (процентами), использование логарифмической шкалы и
т. д.
Таблица должна иметь короткие и ясные заголовки строк и столбцов.
В процессе разработки диаграммы они будут использованы программой для создания подписей, легенды и т. д.
4.3.
Основные этапы
Диаграмма создается с помощью Мастера диаграмм, вызываемого командой Вставка  Диаграмма, используя строку Меню, или кнопкой
на панели инструментов Стандартная, либо с помощью панели инструментов Диаграмма.
Перед вызовом Мастера диаграмм выделяется интервал ячеек – область данных для построения диаграммы.
Мастер диаграмм осуществляет построение новой диаграммы в интерактивном режиме за несколько шагов:
Шаг 1. Выбор типа диаграммы.
Шаг 2. Выбор формата диаграммы указанного типа.
Шаг 3. Задание параметров диаграммы. Добавление легенды, ввод
названия диаграммы и подписей к осям диаграммы (см. рис. 17).
Шаг 4. Определение места размещения диаграммы.
Для изучения графических возможностей электронных таблиц используем
пример типичного представления данных в
таблице, показанной на рис. 18. Под «Результатом» в третьей графе таблицы м ожет пониматься объем продаж, закупки и
т.п. Требуется построить на
Рисунок 18.
основе имеющихся данных объемную гистограмму и круговую диаграмму. Данные, показанные на рис. 18, отвечают
требованиям создания наглядной диаграммы, изложенным выше.
31
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Выделим в процедуре формирования диаграммы отдельные шаги – ряд
технологических операций. Пошаговое описание процедуры, по которому
создается эта диаграмма, можно использовать при построении диаграмм ра зличного типа.
4.4. Построение объемной гистограммы
Выполняем поэтапно все перечисленные ниже действия:
1. Начальный этап в разработке диаграммы заключается в подготовке
блока данных. Сформируйте таблицу данных, показанную на рис. 18. Выделите блок ячеек А1:С6 и нажмите кнопку вызова Мастера диаграмм. Откроется первое окно и начнется диалог с Мастером диаграмм.
Рисунок 19.
Рисунок 20.
Шаг 1 – демонстрируется рис. 19. В окне можно выбрать тип диагра ммы. Используем в нашем примере объемную гистограмму.
Рисунок 21.
Рисунок 22.
Шаг 2 – показан на рис. 20. Если перед началом конструирования был
выделен диапазон данных, то в этом окне удастся сразу увидеть результат. На
данном этапе можно переопределить диапазон исходных данных и выпо лнить настройку рядов данных с помощью второй вкладки. Каждый ряд разрешается настраивать индивидуально. Ненужные ряды можно удалить. Разу32
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
меется, все произведенные изменения тут же находят отражение на макете
диаграммы, расположенном в окне.
Шаг 3 – показан рис. 21 и 22. Названия вкладок листа дают представление о том, какие элементы диаграммы могут быть представлены в соотве тствующих вариантах. От пользователя требуется выбрать подходящие пар аметры.
На диаграмме может быть размещена таблица с данными. Это выглядит достаточно эффектно, но такая таблица не должна быть очень большой.
На рис. 22 представлен этот
вариант.
Шаг 4 – показан рис. 23.
На этом шаге пользователь
должен определить, где будет
размещена диаграмма: на новом листе, на текущем или на
другом листе рабочей книги.
Рисунок 23.
2. Готовая
диаграмма
изображена на рис. 24. Для перемещения диаграммы на рабочем
листе
выделите
диаграмму,
щелкнув мышью в любом ее месте. Выделенная диаграмма помечена по периметру маленькими
черными квадратиками. Затем
поместите курсор в любое место
диаграммы и, нажав и удерживая
левую кнопку мыши, перетащите
диаграмму в нужное место.
Рисунок 24. Готовая диаграмма.
3. Для изменения размера диаграммы выделите диаграмму, щелкнув
мышью в любом месте диаграммы, установите указатель мыши на периметре
на одном из квадратов так, чтобы курсор принял форму двунаправленной
стрелки. Затем, перемещая мышь при нажатой левой кнопке, измените ра змер диаграммы.
4. Для удаления диаграммы выделите ее, а затем нажмите клавишу
Del .
5. Программа сама назначает многие параметры форматирования по
умолчанию, поэтому от пользователя иногда требуется доработать объекты.
Целесообразно увеличить размер стен диаграммы до максимального, в пр еделах области диаграммы (внешней рамки). Для этого надо растянуть,
насколько возможно, внутреннюю рамку, то есть область построения диа33
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
граммы (см. рис. 17). Эта область может даже накладываться на другие об ъекты, например на легенду.
6. В процессе доработки целесообразно бывает по добрать подходящие шрифты и их размеры. Поскольку таблица данных прижата к стенам
диаграммы, выбор размера шрифта для таблицы позволяет изменить вид ди аграммы в целом.
7. Если отключить таблицу данных, размер диаграммы можно будет
увеличить почти в два раза.
8. Если диаграмма трехмерная, то потянув за один из углов, можно
превратить ее изображение в проволочный контур и повернуть его, изменив
проекцию.
4.5. Построение круговой диаграммы
Круговая диаграмма отличается от гистограммы тем, что на ней, как
правило, не отображаются цифровые данные. Наглядность круговой ди аграммы заключается в легкости сравнения нескольких величин как площадей
секторов, без их выражения числовыми данными. Дополнительное преим ущество круговой диаграммы состоит в возможности уменьшать размер изображения без потери наглядности. Если небольшая круговая диаграмма м ожет
выглядеть приемлемо, то на гистограмме такого же размера отдельные элементы и детали уже неразличимы.
1. Для построения круговой диаграммы в нашем примере достаточно
блока данных А2:В6 из таблицы, показанной на рис. 18. Если выделить его и
вызвать Мастер диаграмм то вся работа по разработке диаграммы сведется к
тому, что на первом шаге Мастера диаграмм потребуется выбрать ее тип. По
всем остальным действиям Мастера диаграмм можно следовать без корректировок. В результате получаем диаграмму, изображенную на рис. 25, а.
Если установить курсор на один из ее сегментов, то на экран будет выведено соответствующее значение из таблицы-источника.
2. Щелкнув мышью по сегментам, добейтесь появления на каждом
сегменте по одному маркеру. В этом режиме можно раздвигать и сдвигать
разделенные между собой все сегменты одновременно.
а
б
Рисунок 25. Круговые диаграммы: а – момент индикации значения одной
из категорий; б – «оттаскивание» сегмента круговой диаграммы.
34
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3. Щелкнув мышью по отдельному сегменту и выделив его несколькими маркерами, можно сдвигать отдельный сегмент вдоль вообража емой плоскости. Этот вариант представлен на рис. 25, б.
Контрольные задания по разделу 4.
В приведенных ниже заданиях выполните пошаговые действия фо рмирования диаграмм, описанные выше, с дополнительным подбором подходящих параметров и элементов диаграмм в соответствующих вариантах.
Задание 4.1
100
План
150
Лак
Результат
Плитка
50
Обои
15
28
0
План
План
Краска
61
75
Обои
Краска
80
60
40
20
0
14
14
Лак
9
48
Плитка
Задание 4.2
Результат
Рисунок 26.
Рисунок 27.
Задание 4.3
Задание 4.4
План
80
Обои
70
Обои
60
50
Плитка
Плитка
29%
40
30
Краска
20
Краска
10
ЛакЛак
Лак
8%
Плитка
Краска
17%
Обои
46%
0
0
1
2
3
4
5
Рисунок 28.
Рисунок 29.
Задание 4.5 Для блока данных, представленного в верхней части рис.
30, постройте Линии тренда, которые обычно используются в задачах прогнозирования. Линию тренда с помощью регрессионного анализа можно
продолжить вперед или назад, экстраполировать ее за пределы, в которых
данные уже известны, и тем самым можно показать тенденцию их изменения.
Подробнее с понятием «Линии тренда» и с необходимыми командами для ее
построения на данном этапе познакомьтесь самосто ятельно, используя раздел
2.10. Получение справочной информации. На рис. 8 вид окна справки зафи ксирован на этапе ввода необходимого ключевого слова. Детально работа с
Линиями тренда описана в разделе 7.3. Анализ и прогнозирование временных
рядов. Конечный результат должен быть получен в форме, представленной
35
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
нижней частью рис. 30. Из рис. 30 следует, что линии тренда плана и продаж
при выборе линейной зависимости прогноза пересекаются в области деления 7.
Мы убеждаемся, таким образом, что прогноз дает выход продаж на плановые значения через семь кварталов после начала
продаж. Введите с клавиатуры в блок
исходных данных несколько новых числовых значений. Убедитесь, что изменения на диаграмме при этом происходят
синхронно и автоматически, а соответствующий подбор данных позволят м оделировать ситуацию.
Рисунок 30.
Задание 4.6. На примерах диаграмм, представленных на рис. 24 и 25,
выполнить дополнительные действия по редактированию и форматированию
уже готовой диаграммы:

ввести Заголовки: Название диаграммы «Выручка за I-квартал
2005 г.» и подписи к осям - Ось X (категорий) - «Товар», Ось
Y(значений) - «Выручка, тыс. руб.»;

изменить Ориентацию и положение подписей к осям;

изменить цену основных делений шкалы с 20 на 25 единиц для
Оси Y(значений);

ввести Подписи данных - значения (численные абсолютные значения над столбиками гистограммы) и - доля (проценты) над секторами круговой диаграммы;

Легенду переместить под таблицу горизонтальной Оси X (категорий);

использовать Линии сетки для Области построения диаграммы;

перекрашивать все плоские участки диаграммы, используя градиентный способ заливки;

импортировать готовую диаграмму в документ MS Word.
Большинство команд, необходимых для выполнения этого задания
можно найти самостоятельно, используя пункты контекстно-зависимого
меню и с помощью Справочной системы MS Excel.
36
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Тесты по разделу 4.
1. При изменении исходных данных:
а) необходимо повторить команду Вставка  Диаграмма для изменения
изображения элементов диаграммы по размеру или их местоположению;
б) автоматически изменяется изображение элементов диаграммы по разм еру или их местоположению.
2. Легенда представляет собой:
а) вставку окна с названиями рядов данных;
б) вставку окна внутри области построения с названиями осей.
3. 1 пиксел - это:
а) минимальный элемент растрового изображения;
б) единица измерения по оси Y, оценивающая высоту столбика диаграммы.
4. Перед вызовом Мастера диаграмм:
а) выделяется область рабочего листа - диапазон ячеек, где предполагается
построить диаграмму;
б) выделяется интервал ячеек - область данных для построения диаграммы.
5. Диаграмму в MS Excel можно поместить:
а) на отдельном листе;
б) на имеющемся листе.
6. Мастер диаграмм осуществляет построение новой диаграммы:
а) за 2 шага;
б) за 3 шага;
в) за 4 шага;
г) за 5 шагов.
7. Подбор подходящих единиц измерения при построении диаграмм возможен:
а) в абсолютных единицах;
б) в относительных единицах;
в) по логарифмической шкале.
8. Используя пункты контекстно-зависимого меню можно вернуться на этап:
а) редактирования названий осей и диаграммы;
б) выбора типа диаграммы.
9. На основе одного блока (ряда) данных можно построить:
а) только круговую диаграмму;
б) только столбчатую гистограмму;
в) и круговую, и столбчатую гистограмму.
10. Круговую диаграмму можно построить по:
а) одному ряду данных;
б) двум рядам данных;
37
в) трем рядам данных.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
5. ОПЕРАЦИИ НАД ТАБЛИЧНЫМИ ДАННЫМИ
5.1.
Консолидация данных в электронных таблицах
Консолидация данных – это процедура получения итоговых данных по
одной или нескольким категориям в результате объединения данных из областей-источников и вывода их в область назначения. Под категориями может подразумеваться, например, вид товара, фамилия плательщика, вид платежа и т.п. С помощью консолидации можно просуммировать данные из нескольких диапазонов, вычислить минимальные, максимальные и средние
значения, а также выполнить
некоторые статистические расчеты. На практике консолидация чаще всего применяется
для составления квартальных
отчетов, обработки данных по
обороту средств и т.п.
В качестве примера используем таблицу учета товарооборота, полученную в разделе 3.3. Требуется узнать,
Рисунок 31. Окно Консолидация.
сколько всего продукции и какого типа выпустила пекарня.
Выполним консолидацию для таблицы, показанной на рис. 13.
1. Выделите ячейку, определяющую положение итоговой таблицы
(область назначения). В нашем примере это могут быть ячейки E2 или A22.
2. Выберите команду Данные  Консолидация.
3. В окне Консолидация в списке Функция (см. рис. 31) укажите
функцию Сумма. Как видно, кроме суммирования, в MS Excel существует
еще немало способов подведения итогов.
4. Установите курсор в строку Ссылка.
5. Выделите первую исходную область (из областей-источников). В
нашем примере она находится в диапазоне A4:D7 (см. рис. 13). Рамка выделения должна быть пунктирной.
6. Щелкните в окне Консолидация кнопку Добавить. Нажмите
клавишу Del.
7. Повторите действия, описанные в пунктах 4, 5 и 6, для диапазонов A9:D13 и A15:D18.
8. Установите
флажок для
Рисунок 32.
опции Значения левого столбца и
нажмите кнопку ОK.
38
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Окно Консолидация со всеми исходными данными показано на рис. 31,
а итоговая таблица – на рис. 32.
Следует иметь ввиду, что в итоговой таблице не производится автоматический пересчет данных, если вы измените данные в таблице -сводке. Чтобы их обновить, необходимо запустить консолидацию еще раз, предвар ительно выделив всю итоговую таблицу.
5.2.
Сортировка данных
При разработке таблиц данные удобно хранить в виде списка. Список –
это последовательность строк рабочего листа, содержащих данные. Все да нные в столбце имеют одинаковый тип, например текст в одном столбце и
числа – в другом. Примером данных, организованных в список,
является таблица по расчету заработной платы без итоговой строки,
изображенная на рис. 14. А чтобы
таблицу, представленную на рис.
13, рассматривать в форме списка
ее необходимо преобразовать к
виду, приведенному на рис. 33.
Рисунок 33.
Списки в терминах MS Excel называют базой данных. При этом столбцы в списке рассматриваются как поля базы данных, заголовки столбцов являются именами полей, а каждая строка в списке представляет запись в базе
данных. Электронные таблицы MS Excel уступают по своим возможностям
профессиональным системам управления базами данных таким, например,
как СУБД MS Access, но для ведения баз данных с числом строк до нескольких тысяч они подходят.
К наиболее часто применяемым операциям при работе с базами данных
относятся Сортировка и Фильтрация.
С помощью сортировки можно поменять порядок строк в списке в с оответствии с содержимым конкретных столбцов. Под сортировкой понимают
упорядочение строк (записей) по возрастанию или по убыванию принятого
признака в назначенном столбце (поле). Если в столбце цифры, они выстраиваются по возрастанию или убыванию. Если в строках столбца текст, то
строки выстраиваются по алфавиту от А до Я или по убыванию от Я до А для
первой буквы текста. В электронной таблице, во-первых, сортировать можно
по строкам или столбцам, а во-вторых, можно сортировать строки или столбцы не целиком, а в пределах области выделения.
Рисунок 34.
39
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рисунок 35.
Рисунок 36.
Для примера выполнения
операций сортировки используем
таблицу, показанную на рис. 33.
Выделим
в таблице столбец
«Наименование» (область B1:B12)
и выберем команду Данные 
Сортировка. MS Excel активизирует окно, показанное на рис. 34,
где предлагается либо расширить
пределы сортировки автоматически на всю таблицу,
Рисунок 37.
либо сортировать в пределах указанного выделения. Расширяем выделение и нажимаем кнопку Сортировка. Открывается новое окно, представленное на рис. 35. В этом окне задается пор ядок сортировки, поочередно указываются поля, по которым будет проведена
сортировка. Таких ключей можно указать до трех, причем для каждого из них
имеется возможность определить свой порядок сортир овки. Кнопка Параметры вызывает окно (см. рис. 36), где содержаться важные опции сортиро вки. Используя кнопку
справки этого окна, изучите назначение опций
самостоятельно. Работа с кнопкой справки идентична использованию
кнопки
«Что это такое?» и описана в разделе 2.10.
Получение
справочной
информации.
Таблица после сортировки изображена на рис. 37.
Полезно знать, что для сортировки данных по возрастанию или убыванию предназначены кнопки
на панели инструментов Стандартная. Ключом сортировки в
этом случае является столбец, в котором находится текущая ячейка.
5.3. Фильтрация данных
Фильтр представляет собой конструкцию, предназначенную для отб ора тех строк таблицы, которые удовлетворяют заданному условию, и временного скрытия остальных. Рассмотрим режим автофильтрации.
Автофильтр следует применять для быстрой фильтрации данных с о дним или двумя условиями, накладываемыми на ячейки отдельного столбца.
40
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Этот режим устанавливается командой Данные  Фильтр  Автофильтр.
При этом в нижней части заголовка каждого столбца появляется кнопка со
стрелкой (маркер), которая служит для задания условий отбора (см. рис. 38).
Рисунок 39.
Рисунок 38.
Рисунок 40.
Рисунок 41.
Щелчок по маркеру открывает список (называемый в MS Excel разворачивающийся набор), который и содержит условия отбора.
Допустим нас, интересует информация, относящаяся только к наименованию «Лаваш». Раскрываем разворачивающийся набор в столбце «Наименование» и выбираем строчку – «Лаваш», как это показано на рис. 38. Результат
представлен на рис. 39.
Если же нас интересуют только те записи, в кото рых значение поля
«Сумма» заключено в интервал от 50000 до 100000, то следует в разворач ивающемся наборе столбца «Сумма» выбрать строчку «Условие…» и в о ткрывшемся новом окне, представленном на рис. 40, установить требуемый
интервал. Результат представлен на рис. 41.
Чтобы отфильтровать заданное число строк с максимальными или м инимальными значениями ячеек текущего столбца, воспользуйтесь условием
отбора «Первые 10…». Это особенно может пригодиться при составлении
различного рода рейтингов.
Чтобы отменить фильтрацию, нажмите маркер и выберите в развернувшемся списке строку «Все». Для отказа от режима автофильтрации п овторите команду Данные  Фильтр  Автофильтр.
5.4. Анализ и обобщение данных с помощью сводных таблиц
Формирование итогов в электронных таблицах не ограничивается использованием консолидации данных и структуры документа. Сводная табли41
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ца – это еще один инструмент обработки больших списков с данными. Поскольку в этом случае сразу подводятся итоги, выполняется сортировка и
фильтрация списков, то сводная таблица обслуживается более мощным инструментом обработки данных, который называется Мастером сводных таблиц [1].
Сводные таблицы – это вспомогательные таблицы, с помощью которых можно анализировать большие объемы данных, находящихся в разли чных источниках, и представлять их в наиболее удобном виде. Источниками
данных являются:
1. список (база данных) MS Excel;
2. таблицы, полученные в результате консолидации данных;
3. другие сводные таблицы;
4. внешний источник данных:
 текстовый файл, содержащий табличные данные;
 внешние базы данных (SQL Server, MS FoxPro, MS Access).
Начиная с версии MS Excel 2000, появилась новая замечательная
встроенная возможность – построение сводной диаграммы, отображающей
данные сводной таблицы и позволяющей изменять ф орму представления
данных так же легко, как и в сводных таблицах [11].
Необходимым условием для создания сводной таблицы или сводной
диаграммы является наличие одной или нескольких таблиц, содержащих заголовки столбцов. Заголовки (метки) столбцов служат для создания из них
полей данных. Создание и модификация сводных таблиц и сводных диаграмм
выполняется с помощью Мастера сводных таблиц и диаграмм, окно которого появляется после команды Данные  Сводная таблица или щелчка по
кнопке Мастер сводных таблиц на специальной панели инструментов Сводные таблицы.
Основные этапы работы с мастером:
1. выбор типа источника и вида результата;
2. указание диапазона ячеек для построения сводной таблицы (исхо дные данные);
3. формирование макета (структуры) сводной таблицы - кнопка
и настройка параметров вывода информации - кнопка
.
На рис. 42 представлено диалоговое окно Мастера сводных таблиц и
диаграмм на этапе формирования макета.
42
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рисунок 42.
В правой части этого окна макета находятся кнопки с названиями полей исходных данных, в центре - область построения сводной таблицы.
В область Страница перетаскивают мышью поля, в которых нужно
произвести отбор нужных записей (фильтрацию). Область Страница может
оставаться незаполненной.
В области Строка и Столбец помещают поля, которые должны быть
представлены в сводной таблице.
В область Данные помещают поля, по которым при создании сводной
таблицы будут производиться вычисления с помощью одной из функций:
Сумма (по умолчанию), Количество значений, Среднее, Максимум и др. Для
выбора нужной функции и настройки параметров полей, помещенных а о бласть Данные, следует дважды щелкнуть по нужному полю, а затем в диалоговом окне Вычисление поля сводной таблицы выбрать функцию.
Вычисляемые поля в сводных таблицах или отчетах сводных диаграмм
можно также создавать с помощью формул.
Готовые сводные таблицы (диаграммы) можно изменять (модифицировать их структуру в диалоговом окне Мастер сводных таблиц и диаграмм
– макет, см. рис. 42) – добавлять и удалять поля, расположение полей и т.д.,
либо можно форматировать как обычные таблицы (диаграммы) MS Excel.
Сводные таблицы служат только для отображения информации, поэтому ручная правка данных в них невозможна.
При изменении данных в исходной таблице сводная таблица автоматически не обновляется. Чтобы обновить сводную таблицу, нужно выделить в
ней любую ячейку и щелкнуть по кнопке Обновить данные на панели инструментов Сводные таблицы. Эта панель содержит также кнопки команд:
Формат отчета, Мастер диаграмм, Мастер сводных таблиц,
43
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Скрыть/Отобразить детали, Параметры поля, Скрыть поля и главная
кнопка Сводная таблица, содержащая целый набор команд, в том числе и
команду Формулы для создания вычисляемых полей.
Рассмотрим пример создания сводной таблицы на основе базы данных
MS Excel, представленной на рис. 43.
Рисунок 43.
Требуется определить с помощью сводной таблицы эффективность работы каждого продавца.
На первом этапе рассчитайте значения поля Объем продаж. Затем выделите список - диапазон ячеек A3:G21, введите команду Данные  Сводная
таблица и используйте инструкции Мастера сводных таблиц и диаграмм. В
третьем окне Мастера сводных таблиц и диаграмм для создания макета
сводной таблицы щелкните по кнопке
и перетащите с помощью
мыши в область построения Строка кнопку Продавец, а в область Данные –
кнопки полей, по которым будут производиться вычисления, - кнопку Количество и две кнопки Объем продаж (см. рис. 42).
Вторая кнопка Объем продаж нужна для выполнения дополнительных
вычислений.
Для выполнения дополнительных вычислений сделайте двойной ще лчок на кнопке Сумма по полю Объем продаж2, в диалоговом окне Вычисление поля сводной таблицы щелкните по кнопке
, а затем в
44
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
списке Дополнительные вычисления выберите строку Доля от суммы по
столбцу.
Щелкните по кнопке OK и вернитесь в третье окно Мастера сводных
таблиц и диаграмм.
Установите переключатель в позицию Новый лист и щелкните по
кнопке
. Полученная в результате сводная таблица должна иметь
вид
Рисунок 44.
Проанализируйте результат и сохраните работу.
Контрольные задания по разделу 5.
Задание 5.1. Примените процедуру консолидации к электронной таблице,
полученной в результате выполнения контрольного задания 3.6
(см. рис. 16). Итоговая
таблица должна приобрести вид, представленный на рис. 45.
Рисунок 45.
Задание 5.2. На основе таблицы по расчету заработной платы, полученной в
результате выполнения контрольного задания 3.4 (см. рис. 14), сформируйте,
используя копирование блока ячеек A1:I8, еще две электронные таблицы.
Пусть эти три таблицы относятся к январю, февралю и марту, соответстве нно. Увеличьте премию всем сотрудникам в феврале на 50 руб. и в марте еще
на 50 руб. Создайте итоговую таблицу за I-й квартал года на основе консолидации имеющихся данных.
Чтобы в итоговой таблице присутствовали и заголовки граф и фамилии
сотрудников, при выделении областей-источников делайте так, чтобы эти
строки и столбцы оказывались крайними сверху и слева, соответственно.
Например, исходная область для таблицы на рис. 14 соответствует блоку яч е45
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ек B2:I8. А в окне Консолидация (см. рис. 31) одновременно установите
флажки для опций Подписи верхней строки и Значения левого столбца.
Выполните варианты, когда три исходные таблицы расположены сн ачала на одном, а потом на трех разных рабочих листах, а итоговую таблицу
поместите на отдельном четвертом рабочем листе. Добавление четвертого
листа и переименование остальных в «Январь», «Февраль», «Март» и «I-й
квартал» осуществите, используя команды контекстного меню, изображенного на рис. 5.
Ответ: Всего = 2652,00 руб. для графы «Итого удержано» и Всего=17748,00
руб. для графы «Итого к выдаче» за весь I-й квартал года. Эти же графы в
итоговой таблице, например для Егоровой О.Б., содержат значения 608,40
руб. и 4071,60 руб., соответственно.
Задание 5.3. Необходимо строки таблицы, представленной на рис. 14, отсо ртировать по фамилиям сотрудников. После выполнения операции о братите
внимание, что в результате сортировки за каждым работником корректно
“сохранились” его оклад, а также суммы начислений и удержаний. Однако
при этом сохранился и порядковый номер, поскольку это поле также является
элементом списка, и MS Excel автоматически включил его в диапазон сортировки. Чтобы упорядочить нумерацию, достаточно выделить столбец «№ п/п
», выбрать режим Сортировать в пределах указанного выделения (см. рис.
34), а затем выполнить сортировку по возрастанию для указанного поля.
Задание 5.4. Осуществите несколько вариантов сортировки в таблице, представленной на рис. 33. В первом варианте сортируйте по полю «Цена» или
«Сумма» по возрастанию, в другом варианте используйте два ключа по полям «Наименование» и «Количество» по возрастанию, в следующем варианте
по трем ключам: по возрастанию по полям «Точка», «Наименование»,
«Сумма». Для последнего варианта необходимо выбирать область сортиро вки в пределах блока ячеек A1:E11. Иначе слово «Итого» будет воспринято,
как элемент наименования торговой точки и будет задействовано в сортировке по алфавиту.
Задание 5.5. С помощью справки (см. раздел 2.10. Получение справочной
информации) изучите команды Закрепить области и Снять закрепление
областей. Эти команды применяются для фиксации отображения названий
строк и столбцов при прокрутке больших таблиц, чтобы заголовки строк и
столбцов не уходили за пределы экрана. Примените команды к таблице,
представленной на рис. 14, так, чтобы при просмотре списка по вертикали на
экране всегда присутствовали заголовки столбцов («шапка» таблицы), а при
просмотре по горизонтали всегда были видны фамилии работников.
Задание 5.6. С помощью режима автофильтрации в таблице, представленной
на рис. 14, осуществите отбор записей, у которых значение оклада находится
в диапазоне от 1000 до1400.
Задание 5.7. С помощью справки (см. раздел 2.10. Получение справочной
информации) изучите команды скрытия и отображения любых (смежных или
несмежных) столбцов и строк таблицы. Эти манипуляции с уже готовыми
46
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
таблицами необходимы для того, чтобы было удобно анализировать большие
массивы данных. Примените команды к таблицам на рисунках 13, 14, 16.
Задание 5.8. С помощью кнопки
на панели инструментов Сводные таблицы постройте диаграмму по результатам сводной таблицы, предста вленной
на рис. 44.
Тесты по разделу 5.
1. Консолидация данных в MS Excel - это процедура получения:
а) исходных данных;
б) промежуточных данных;
в) итоговых данных.
2. С помощью консолидации данных можно вычислить:
а) средние значения;
б) минимальные значения;
в) максимальные значения;
г) статистические характеристики.
3. Если изменить данные в исходной таблице, то в итоговой таблице конс олидации:
а) производится автоматический пересчет данных;
б) не производится автоматический пересчет данных.
4. Последовательность строк рабочего листа, содержащая данные лишь о дного типа в пределах каждого отдельного столбца, называется:
а) полем;
б) записью;
в) списком.
5. Ключей сортировки (или полей), по которым одновременно можно ос уществлять сортировку данных, можно указать до:
а) одного;
б) двух;
в) трех.
6. Табличный процессор MS Excel позволяет вести базы данных с числом
строк до нескольких:
а) десятков;
б) сотен;
в) тысяч.
7. К наиболее часто применяемым операциям при работе с базами да нных
относятся:
а) консолидация;
б) сортировка;
в) фильтрация.
8. Фильтрацию можно осуществлять с данными:
а) числовыми;
б) текстовыми.
9. Автофильтр следует применять для быстрой фильтрации данных с условиями:
а) одним;
б) двумя;
в) тремя.
10. Таблица, полученная в результате консолидации данных, всегда приобр етает форму списка:
а) да;
б) нет.
47
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6. РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ
С ПОМОЩЬЮ MS EXCEL
Использование специальных средств Надстроек расширяет возможности табличного процессора [3]. Из основных надстроек MS Excel отметим
две.
Пакет анализа – обеспечивает дополнительные возможности анализа
наборов данных при решении сложных статистических, экономических и
инженерных задач (см. раздел 7. Решение задач статистического анализа и
прогнозирования данных в среде MS Excel). Для проведения анализа данных
с помощью этого инструмента следует указать входные данные и выбрать
параметры; анализ будет проведен с помощью подходящей статистиче ской
или инженерной макрофункции, а результат будет помещен в выходной ди апазон. Дополнительные инструменты позволяют представить результаты
анализа в графическом виде.
Поиск решения – используется для решения задач оптимизации [7,9]. С
помощью этого инструмента возможны постановка, решение и анализ всех
классов задач оптимизации: линейного, целочисленного, нелинейного и стохастического программирования в области менеджмента, конса лтинга и при
оптимальном проектировании технических систем.
В экономике оптимизационные задачи возникают в связи с многочисленностью возможных вариантов функционирования конкретного эконом ического объекта, когда возникает ситуация выбора варианта, наилучшего по
некоторому правилу, критерию, характеризуемому соответствующей целевой
функцией (например, иметь минимум затрат, максимум продукции) [7].
Продемонстрируем возможности надстройки – Поиск решения на примерах решения задач линейного программирования.
Перед началом работы необходимо убедиться в наличии установки
надстройки на вашем компьютере. Если в Меню Сервис отсутствует команда
Поиск решения, значит, необходимо загрузить эту надстройку. Выберите команду Сервис  Надстройки и активизируйте надстройку Поиск решения.
Если же надстройки нет в диалоговом окне Надстройки, то вам необходимо
обратиться к панели управления Windows, щелкнуть на пиктограмме Установка и удаление программ и с помощью программы установки MS Excel
(или MS Office) установить надстройку Поиск решения.
6.1.
Задача распределения ресурсов
Если финансы, оборудование, сырье и даже людей полагать ресурсами,
то значительное число задач в экономике можно рассматривать как задачи
распределения ресурсов. Достаточно часто математической моделью таких
задач является задача линейного программирования [7].
Рассмотрим следующий пример.
48
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Требуется определить, в каком количестве надо выпускать продукцию
четырех типов Прод1, Прод2, Прод3, Прод4, для изготовления которой тр ебуются ресурсы трех видов: трудовые, сырье, финансы. Количество ресурса
каждого вида, необходимое для выпуска единицы продукции данного типа,
называется нормой расхода. Нормы расхода, а также прибыль, получаемая от
реализации единицы каждого типа продукции, приведены в табл. 2. Там же
приведено наличие располагаемого ресурса.
Таблица 2.
Ресурсы
Трудовые
Сырье
Финансы
Прибыль
Нормы расхода ресурсов на единицу
продукции
Прод1
Прод2
Прод3
Прод4
1
1
1
1
6
5
4
3
4
6
10
13
60
70
120
130
знак



max
Наличие
ресурсов
16
110
100
-
Требуется найти такой план выпуска продукции, при котором будет
максимальной общая прибыль.
6.2. Экономико-математическая модель задачи и
технология ее решения
Обозначим через X1 , X2 , X3 , X4 количество выпускаемой продукции
каждого типа. Тогда экономико-математическая модель задачи, включающая
требование максимизации целевой функции F и ограничения по ресурсам,
будет иметь вид:
целевая функция F = 60X1 + 70X2 + 120X3 + 130X4  max
(6.2.1)
при ограничениях:
X1 + X2 + X3 + X4  16,
6X1 + 5X2 + 4X3 + 3X4  110,
(6.2.2)
4X1 + 6X2 + 10X3 + 13X4  100,
X1 , X2 , X3 , X4  0.
Для решения задачи с помощью надстройки – Поиск решения необходимо выполнить действия в ряд шагов:
Шаг 1. Создать форму на рабочем листе MS Excel для ввода условий задачи.
Шаг 2. Указать адреса ячеек, в которые будет помещен результат решения (изменяемые ячейки).
Шаг 3. Ввести исходные данные.
Шаг 4. Ввести зависимость для целевой функции.
49
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Шаг 5. Ввести зависимости для ограничений.
Шаг 6. Указать назначение целевой функции (установить целевую яче йку).
Шаг 7. Ввести ограничения.
Шаг 8. Ввести параметры для решения задачи линейного программир ования.
Рассмотрим подробно все перечисленные выше этапы.
Шаг 1. Для задачи (6.2.1) - (6.2.2) подготовим форму, представленную
на рис. 46, для ввода условий.
Рисунок 46.
Весь текст на рис. 46 (и в дальнейшем) является комментарием и на
решение задачи не влияет.
Шаг 2. В нашей задаче оптимальные значения переменных X1 , X2 , X3 ,
X4 будут помещены в ячейках B3:E3, оптимальное значение целевой функции – в ячейке F6.
Шаг 3. Введем исходные данные в созданную форму. Получим результат, показанный на рис. 47.
Рисунок 47.
Шаг 4. Введем зависимость для целевой функции (6.2.1).
50
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
При данной форме ввода исходных данных на рабочий лист необходимо в ячейку F6 поместить формулу =B6*B3+C6*C3+D6*D3+E6*E3. Однако
для формул подобной структуры удобнее использовать встроенную функцию
СУММПРОИЗВ( ), которая возвращает сумму произведений соответству ющих элементов массивов (в данном случае массивов B6:E6 и B3:E3).
 Поместите курсор в ячейку F6.
 Курсор на кнопку Мастер функций.
 M1 (обозначим через M1 действие – «один щелчок левой кнопкой
мыши»).
На экране: диалоговое окно Мастер функций шаг 1 из 2.
 Курсор в окно Категория на категорию Математические.
 M1.
 Курсор в окно Функция на СУММПРОИЗВ.
 M1.
 OK.
На экране появляется диалоговое окно. Сместите его перетаскиванием м ышью в нижнюю часть экрана как на рис. 48.

Рисунок 48.
В массив 1 ввести B$3:E$3. Использование здесь адресации диапазона как для смешанных ссылок в дальнейшем упростит ввод зависимостей для левых частей ограничений (6.2.2).
51
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Заметим, что во все диалоговые окна адреса ячеек удобно вводить не с клавиатуры, а протаскивая мышь по ячейкам, чьи адреса следует ввести.
 В массив 2 ввести B6:E6.
 OK.
Шаг 5. Введем зависимости для левых частей ограничений (6.2.2).
 Курсор в F6.
 Копировать в буфер обмена
.
 Курсор в F9.
 Вставить из буфера
.
 Скопировать F9 в F10:F11.
На этом ввод зависимостей закончен.
Запуск надстройки Поиск решения осуществляется командой Сервис 
Поиск решения, после применения которой появляется окно Поиск решения,
представленное на рис. 49.
Рисунок 49.
В диалоговом окне Поиск решения есть три основных параметра:
 Установить целевую ячейку.
 Изменяя ячейки.
 Ограничения.
Сначала нужно заполнить поле Установить целевую ячейку. Во всех
задачах для средства Поиск решения оптимизируется результат в одной из
ячеек рабочего листа. Целевая ячейка связана с другими ячейками этого р абочего листа с помощью формул. Средство Поиск решения использует формулы, которые дают результат в целевой ячейке, для проверки возможных
решений. Можно выбрать поиск наименьшего или наибольшего значения для
целевой ячейки или же установить конкретное значение.
52
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Второй важный параметр средства Поиск решения – это параметр Изменяя ячейки. Изменяемые ячейки – это те ячейки, значения в которых будут
изменяться для того, чтобы оптимизировать результат в целевой ячейке. Для
поиска решения можно указать до 200 изменяемых ячеек. К изменяемым
ячейкам предъявляются два основных требования: они не должны соде ржать
формул, и изменение их значений должно отражаться на изменении результата в целевой ячейке. Другими словами, целевая ячейка зависима от изм еняемых ячеек.
Третий параметр, который нужно вводить для Поиска решения – это
Ограничения.
Шаг 6. Назначение целевой функции (установить целевую ячейку).
 Курсор в поле Установить целевую ячейку.
 Ввести адрес $F$6 (щелчком мыши по ячейке F6).
 Ввести направление изменения целевой функции: максимальному значению.
Ввести адреса искомых переменных:
 Курсор в поле Изменяя ячейки.
 Ввести адреса $B$3:$E$3 (протаскиванием мыши по диапазону
ячеек).
Шаг 7. Ввод ограничений.
 Курсор на кнопку Добавить.
 M1.
 Появится диалоговое окно Добавление ограничения (рис. 50).







Рисунок 50.
В поле Ссылка на ячейку ввести адрес $F$9.
С помощью раскрывающегося списка в центре окна
ввести знак ограничения .
Курсор в правое поле Ограничение.
Ввести адрес $H$9.
Кнопка Добавить. На экране опять диалоговое окно Добавление ограничения.
Аналогично ввести остальные ограничения.
После ввода последнего ограничения применить кнопку OK.
53
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Если при вводе условий задачи возникает необходимость в изменении
или удалении внесенных ограничений, то это делается с помощью команд Изменить, Удалить.
 На экране появится диалоговое окно Поиск решения с введенными условиями (6.2.2) (см. рис. 49).
Шаг 8. Ввод параметров для решения задачи линейного программирования.
 С помощью кнопки Параметры открыть окно Параметры поиска решения, представленного на рис. 51.
Рисунок 51.
С помощью команд, находящихся в этом диалоговом окне, можно вводить условия для решения задач оптимизации всех классов.
Поле Максимальное время служит для назначения времени в секундах,
выделяемого на поиск решения задачи. В поле можно ввести время, не пр евышающее 32767 с (более 9 часов).
Поле Предельное число итераций служит для назначения числа итераций.
Команды, используемые по умолчанию, подходят для решения большей части практических задач.
 Установить флажок Линейная модель, что обеспечивает применение симплекс-метода.
 Установить флажок Неотрицательные значения, что позволит
выполнить условия последней строки системы неравенств
(6.2.2).
 OK.
На экране появиться диалоговое окно Поиск решения.
54
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
 Выполнить.
На экране появятся диалоговое окно Результаты поиска решения, а в
соответствующих ячейках рабочего листа численные результаты оптимального решения задачи, которые представлены на рис. 52.
Рисунок 52.
Полученное решение означает, что максимальная общая прибыль F6 =
1320 может быть получена при следующем плане выпуска продукции: Прод1
= B3= 10, Прод2 = C3= 0, Прод3 = D3= 6, Прод4 = E3= 0. При этом ресурсы
трудовые и финансы будут использованы полностью, а из 110 единиц сырья
будет использовано 84.
Таково оптимальное решение рассматриваемой задачи распреде ления
ресурсов. Отметим, что подобная технология используется для решения ш ирокого класса задач [17], например, определения оптимального плана разм ещения предприятий, организации оптимального снабжения, управления зап асами, транспортной задачи, задачи о назначениях и др.
Контрольные задания по разделу 6.
Задание 6.1. Для задачи распределения ресурсов, рассмотренной в
разделах 6.1-6.2, примените, описанную в разделе 2.7, операцию присвоения
Имен диапазонам ячеек так, чтобы формула для целевой функции м огла быть
записана в более наглядном виде
=СУММПРОИЗВ(Коэф_ЦФ;Перем_X)
55
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Задание 6.2. Решите задачу о размещении средств [15].
Пусть собственные средства банка вместе с депозитами в сумме с оставляют 100 млн. долл. Часть этих средств, но не менее 35 млн. долл., должна быть размещена в кредитах. Кредиты являются неликвидными активами
банка, так как в случае непредвиденной потребности в наличности обратить
кредиты в деньги без существенных потерь невозможно.
Другое дело ценные бумаги, особенно государственные . Их можно в
любой момент продать, получив некоторую прибыль или, во всяком случае,
без большого убытка. Поэтому существует правило, согласно которому ко ммерческие банки должны покупать в определенной пропорции ликвидные
активы – ценные бумаги, чтобы компенсировать неликвидность кредитов. В
нашем примере ликвидное ограничение таково: ценные бумаги должны с оставлять не менее 30% средств, размещенных в кредитах и ценных бумагах.
Обозначим через X1 средства (млн. долл.), размещенные в кредитах,
через X2 – средства, вложенные в ценные бумаги. Цель банка состоит в том,
чтобы получить максимальную прибыль от кредитов и ценных бумаг: F =
C1 X1 + C2 X2 , где C1 – доходность кредитов, C2 – доходность ценных бумаг.
Целевая функция – это выражение, которое необходимо максимизировать: F = 9X1 + 6X2 .
Имеем следующую систему линейных ограничений:
1. X1 + X2  100 – балансовое ограничение;
2. X1  35 – кредитное ограничение;
3. X2  0,3(X1 + X2 ) – ликвидное ограничение;
4. X1  0, X2  0.
Используя предложенную в разделе технологию решения задачи линейного программирования, найдите оптимальные значения искомых величин X1 , X2 .
Ответ: X1 = 70 млн. долл., X2 = 30 млн. долл., F =810 млн. долл.
Задание 6.3. Для производства двух видов изделий типа A и типа B
предприятие использует 3 вида сырья. Нормы расхода каждого вида сырья на
изготовление единицы продукции каждого типа, общее количество сырья,
имеющегося в наличии, а также прибыль от реализации одного изделия ка ждого типа приведены в таблице:
Таблица 3.
Виды
сырья
I
II
III
Прибыль от реализации
одного изделия (руб.)
Нормы расхода сырья
A
B
12
10
4
5
3
2
40
30
56
Общее количество
сырья (кг)
300
120
252
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Найти план выпуска продукции, обеспечивающий в рамках имеющихся
ресурсов наибольшую прибыль.
Ответ: XA = 25, XB = 0, F = 1000 руб.
Задание 6.4. Повторите решение задачи из задания 6.3 при условии XB
 6 на нижнюю границу выпуска продукции типа B.
Ответ: XA = 20, XB = 6, F = 980 руб.
Задание 6.5. Шоколадный цех выпускает шоколадные батончики трех
видов. Известна норма расхода сырья на каждый батончик:
Таблица 4.
Виды сырья
Сахар
Шоколад
Нуга
Орех
Вафли
Нормы расхода сырья (гр.)
«Белка»
«Зайка»
«Рыбка»
30
30
20
10
15
10
15
10
15
10
0
0
0
10
5
Общее количество
сырья (кг)
100
50
25
10
5
Один батончик «Белка» стоит 7 руб., «Зайка» - 6 руб., «Рыбка» - 5 руб.
Как спланировать производство шоколадных батончиков, чтобы пр ибыль от их производства была наибольшей?
Ответ: X1 = 1000, X2 = 250, X3 = 500, F = 11000 руб.
Задание 6.6. Решить следующие задачи:
1. F(X) = -6X1 - 4X2 + 4X3  max
при ограничениях:
X1 + X2 + X3  -1,
-2X1 - X2 + X3  1,
X1  0, X2  0, X3  0.
3. F(X) = 2X1 + 4X2 + 6X3  min
при ограничениях:
-X1 + X2 + X3  1,
X1 + X2 + 2X3  1,
X1  0, X2  0, X3  0.
2. F(X) = -2X1 - 6X2 + 6X3 + 6X4  max
при ограничениях:
-X1 + 2X2 + 2X3 + X4  1,
2X1 - X2 + X3 + 2X4  1,
X1  0, X2  0, X3  0, X4  0.
4. F(X) = -3X1 - 4X2 + X3  min
при ограничениях:
-2X1 - X2 + X3  -10,
X1 - X2 - 2X3  5,
X1  0, X2  0, X3  0.
57
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Тесты по разделу 6.
1. К основным надстройкам MS Excel относятся:
а) Анализ пакетов;
б) Решение поиска;
в) Пакет анализа;
г) Поиск решения.
2. Надстройка Поиск решения используется для решения задач оптимизации:
а) линейного программирования;
б) нелинейного программирования;
в) стохастического программирования;
г) целочисленного программирования.
3. Установить надстройку на ПК можно с помощью команды:
а) Данные  Надстройки;
б) Сервис  Поиск решения;
в) Сервис  Надстройки;
г) Данные  Поиск решения.
4. В диалоговом окне Поиск решения есть параметры:
а) Ограничения;
б) Изменяя ячейки;
в) Ссылка на ячейку;
г) Установить целевую ячейку.
5. Запуск надстройки Поиск решения осуществляется командой:
а) Данные  Надстройки;
б) Сервис  Поиск решения;
в) Сервис  Надстройки;
г) Данные  Поиск решения.
6. Выберите правильную последовательность установки параметров в диалоговом окне надстройки Поиск решения:
а) Сервис  Поиск решения  Ограничения;
б) Установить целевую ячейку  Изменяя ячейки  Ограничения;
в) Поиск решения  Параметры  Изменяя ячейки;
г) Изменяя ячейки  Ограничения  Данные.
7. В надстройке Поиск решения можно указать число изменяемых ячеек до:
а) 20;
б) 200;
в) 2000.
8. Окно Параметры поиска решения позволяет управлять временем, выделяемым на поиск решения задачи:
а) максимальным;
б) минимальным;
в) средним.
9. При использовании надстройки Поиск решения время поиска решения задачи ограничено:
а) 9 часами;
б) 5 часами;
в) 1 часом;
г) не ограничено.
10. К изменяемым ячейкам в надстройке Поиск решения предъявляются требования:
а) они не должны содержать формул; б) они должны содержать форм улы;
в) изменение их значений не должно отражаться на изменении результата в
целевой ячейке;
г) изменение их значений должно отражаться на изменении результата в
целевой ячейке.
58
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
7. РЕШЕНИЕ ЗАДАЧ СТАТИСТИЧ ЕСКОГО АНАЛИЗА И
ПРОГНОЗИРОВ АНИЯ ДАННЫХ В СРЕДЕ MS EXCEL
Методы статистического анализа позволяют изучать совокупность
массовых явлений с целью выявления закономерностей и их характеристик
при помощи обобщенных показателей. При этом должно выявляться типи чное, характерное, массовое.
Моделирование процессов с помощью статистических методов выр ажается в конечном итоге в построении статистических моделей. Важную
роль при этом играют правильность проведения статистического наблюдения, умение оперировать относительными и средними величинами, пров одить группировку статистических материалов, использовать основные виды
выборок.
Методы математической статистики в приложении к экономическим
процессам могут применяться для решения задач:
 анализа с использованием описательных статистик;
 дисперсионного анализа;
 корреляционного анализа;
 регрессионного анализа;
 многомерного статистического анализа;
 факторного анализа;
 прогнозирования.
Очевидно, что этот перечень задач не полон и может быть ра сширен.
Современное математико-статистическое программное обеспечение
представлено значительным количеством пакетов прикладных программ
(ППП) соответствующего функционального назначения. Отметим, что мировыми лидерами являются пакеты STATISTICA и SPSS. Представлены статистические методы также в ряде приложений офисного назначения, например,
в табличном процессоре MS Excel.
Отметим, что при использовании компьютерных технологий целесообразно придерживаться графически-ориентированного подхода к анализу
данных. Этот подход состоит в том, чтобы получить всестороннее визуальное
представление данных на всех этапах статистической о бработки.
Процесс статистической обработки данных с помощью ППП, как правило, включает следующие этапы:
1. Экономическая постановка задачи и подготовка данных.
2. Ввод данных в систему.
3. Преобразование данных, адекватное выбранным статистическим
методам.
4. Визуализация данных с помощью различных типов графиков и
диаграмм.
5. Реализация статистического алгоритма.
59
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6. Выбор результатов анализа в виде графиков и таблиц с числовой и
текстовой информацией.
7. Интерпретация полученных результатов.
При этом пользователь освобождается от рутинной, трудоемкой
работы (по проведению расчетов, построению таблиц, графиков и т.д.). На
его долю остается исследовательская работа по постановке задачи, выбора
метода обработки и оценки качества моделей интерпретации, которая носит
творческий характер.
Для статистического анализа и прогнозирования MS Excel предоставляет большое число встроенных функций категории Статистические и
специализированное средство – Пакет анализа. Пакет анализа – это
надстройка, содержащая набор функций и инструментов для расширения
встроенных аналитических возможностей MS Excel. Следует отметит, что
весьма значительная мощь рассматриваемого табличного пр оцессора заключена в библиотеке встроенных функций и дополнительных программных
надстройках.
При необходимости использования средства Пакет анализа предварительно следует проверить доступ к нему командой Сервис  Надстройки (должен быть установлен флажок Пакет анализа). Если Пакет анализа
отсутствует в списке надстроек, необходимо его установить, запустив пр ограмму установки MS Excel. Для выбора требуемого инструмента анализа
используется команда Сервис  Анализ данных.
При использовании инструментов Пакета анализа следует помнить,
что выходные данные являются таблицами констант. Следовательно, при
изменениях в исходных данных необходимо запустить соответствующую
процедуру повторно, так как автоматического перерасчета выходных данных
не производится. В случае же встроенных статистических функций возмо жность автоматического перерасчета предусмотр ена.
Рассмотрим решение отдельных задач статистического анализа и пр огнозирования с использованием средств MS Excel. При этом ограничимся
рассмотрением кратких теоретических сведений и технологий реализации
методов описательной статистики, корреляционного и регрессионного анализа, анализа и прогнозирования временных рядов. За более полными теоретическими сведениями следует обратиться к соответствующей учебной литер атуре по статистике и эконометрике, например [15-18].
7.1. Описательные статистики
Методы описательной статистики служат для выявления самых о бщих
свойств наблюдаемых величин в компактной форме. К числу основных описательных статистик относятся: среднее значение, дисперсия, стандартное
отклонение, медиана, мода, максимальное и м инимальное значения, размах,
квантили. На практике чаще всего используются выборочное среднее, медиана и дисперсия (или стандартное отклонение).
60
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Следует отметить, что в основе математической статистики лежат понятия генеральной совокупности и выборки (выборочной совокупности). Все
множество изучаемых элементов называется генеральной совокупностью , а
выбранная для исследования группа элементов – выборкой. Таким образом,
выборка – это лишь часть генеральной совокупности. Выборку называют р епрезентативной (представительной), если она достаточно полно представляет
изучаемые признаки и параметры генеральной совокупности. Отметим, что
на практике обычно располагают лишь выборочной совокупностью.
Очевидно, что в зависимости от вида совокупности показатели опис ательной статистики могут быть определены либо для генеральной совоку пности, либо для выборки. Ограничимся приведением формул для вычисления
основных выборочных характеристик:
N
1. Выборочная средняя: y 

1

yi .
N i 1
N
2. Выборочная дисперсия: d 

1
 yi  y 2 .

N i 1
В статистике чаще выборочную дисперсию определяют по фо рмуле:
N
d

1
  yi  y 2 .
N  1 i 1
Именно такое определение выборочной дисперсии используется в статистических функциях MS Excel.
3. Стандартное отклонение (среднее квадратическое отклонение):
 d .
4. Размах вариации (интервал): R  ymax  ymin.
5. Коэффициент вариации: v 

 100% .
y
При v  40% в выборке наблюдается большая вариация данных.
К другим важным показателям, характеризующим взаимное расположение данных в выборке, относятся мода и медиана.
Мода – это наиболее часто встречающееся значение в выборке. Если
значения не повторяются, мода отсутствует.
Медиана – серединное значение вариационного ряда при нече тном N и
полусумма двух серединных значений при четном N. Вариационный ряд –
это результат упорядочения исходной выборки данных по возрастанию их
численных значений. Выборочная медиана разбивает выборку пополам: слева и справа от неё оказывается одинаковое число элементов выборки.
Форма распределения случайной величины характеризуется значени ями асимметрии и эксцесса. Принято считать, что асимметрия характеризует
61
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
несимметричность распределения случайной величины, а эксцесс – степень
выраженности остроконечности или сглаженности распределения. Асимме трия принимает положительное (отрицательное) значение, если более “дли нная” часть кривой плотности распределения лежит правее (левее) моды. Эксцесс равен нулю для нормального распределения, положителен для остроконечных и отрицателен для сглаженных по сравнению с нормальной плотн остью распределения вероятностей.
В MS Excel достаточно полно представлены методы описательной статистики. Для определения статистик можно использовать следующие инструменты:
 встроенные статистические функции;
 инструмент Описательная Статистика надстройки Пакет Анализа.
Рассмотрим наиболее часто используемые встроенные статистические
функции. Аргументом всех этих функций может быть диапазон ячеек или
список чисел, разделенных точкой с запятой. Ниже аргумент диапазон задает
диапазон ячеек.
Функция СРЗНАЧ (диапазон) находит среднее значение чисел из указанного диапазона ячеек.
Функция МЕДИАНА (диапазон) вычисляет медиану чисел из указанного диапазона ячеек.
Функция МОДА (диапазон) определяет значение, которое чаще других встречается во множестве чисел. Если диапазон не содержит повторяющихся чисел, функция возвращает ошибочное значение #Н/Д.
Функция МАКС (диапазон) находит наибольшее значение из набора
данных.
Функция МИН (диапазон) находит наименьшее значение из набора
данных.
Четыре статистические функции ДИСП (диапазон), ДИСПР (диапазон), СТАНДОТКЛОН (диапазон) и СТАНДОТКЛОНП (диапазон) вычисляют дисперсию и стандартное отклонение для чисел, расположенных в
диапазоне ячеек. Отметим, что функции ДИСП и СТАНДОТКЛОН используются в случае, когда множество чисел является выбо ркой из генеральной
совокупности. Функции ДИСПР и СТАНДОТКЛОНП предполагают, что
значения образуют конечную генеральную совоку пность.
Показатели формы распределения случайной величины асимметрия и
эксцесс вычисляют статистические функции СКОС и ЭКСЦЕСС.
Часто целесообразно провести анализ распределения исходного множества чисел с использованием функции ЧАСТОТА. Функция ЧАСТОТА
(массив данных; массив границ) для исходного множества значений (массив
данных) определяет число значений (частот), попадающих в интервалы ра збиения (массив границ).
Функция ЧАСТОТА является функцией массива. Для использования
функции выполните следующие действия:
62
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
1. Создайте столбец границ интервалов. Интервалы разбиения не об язательно должны быть одной ширины, но они должны идти по возрастанию.
2. Выделите диапазон для выходных данных (только один столбец
ячеек).
3. Введите формулу, задав входной диапазон.
4. Введите диапазон границ интервалов в качестве аргументов.
Нажмите одновременно клавиши [Ctrl] +[Shift]+[Enter] (для создания формулы массива).
Сводную таблицу основных статистических характеристик для одного
или нескольких множеств данных можно получить с помощью инструмента
анализа данных Описательная Статистика. Диалоговое окно инструмента
представлено на рис. 53.
Рисунок 53. Диалоговое окно инструмента Описательная Статистика
Для использования инструмента Описательная Статистика выполните следующие действия:
1.В главном меню выберите пункты Сервис  Анализ данных  Описательная статистика и нажмите кнопку OK.
2.Заполните диалоговое окно ввода данных и параметров вывода и
щелкните на кнопке OK.
Инструмент Описательная Статистика требует задания следующей
информации:
 Входной интервал – диапазон, содержащий исходные данные;
63
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»


Группирование – по столбцам или строкам;
Метки в первой строке – флажок (указывает, содержит ли первая строка названия столбцов или нет);
 Выходной интервал – диапазон для статистик (достаточно указать левую верхнюю ячейку диапазона).
Если необходимо получить дополнительную информацию Итоговая
статистика, Уровень надежности, К-ые наибольшее и наименьшее значения, установите соответствующие флажки в диалоговом окне.
Пример вычисления описательных статистик приведен на рис. 54. В
выходной таблице содержатся показатели итоговой статистики. Эта таблица
является таблицей констант. В случае необходимости большинство показателей можно получить с использованием соответствующих встроенных статистических функций MS Excel.
Национальное богатство
ряда стран в конце ХХ века
На душу
Страна
населения,
тыс. долларов
США
461,5
Россия
400
Япония
423,4
Китай
28,5
Германия
375,1
Франция
359,6
Великобритания
353
Италия
295,8
Бразилия
89
Индонезия
60
Индия
20
Мексика
113
Столбец1
Среднее
248,24
Стандартная ошибка
49,27
Медиана
324,40
Мода
#Н/Д
Стандартное отклонение
170,66
Дисперсия выборки
29125,80
Эксцесс
-1,92
Асимметричность
-0,27
Интервал
441,50
Минимум
20,00
Максимум
461,50
Сумма
2978,90
Счет
12,00
Наибольший (2)
423,40
Наименьший (2)
28,50
Уровень надежности
108,43
(95,0%)
Рисунок 54. Исходные данные и результат применения
инструмента Описательная статистика
Исходные данные рассмотренного примера представляют национальное богатство ряда стран в конце XX века на душу населения (оценки по м е-
64
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
тодологии Всемирного банка) . Данные приведены по странам, общий объем
национальных богатств которых превышает 10 трлн. долларов.
Полученные показатели описательной статистики свидетельствуют о
значительной неравномерности распределения национальных богатств стран
170,66
на душу населения (коэффициент вариации  
100%  69% ).
248,24
Наблюдается заметное отличие выборочной медианы от выборочной сре дней. Следовательно, данные не подчиняются нормальному распределению.
По этой причине при анализе невозможно применение показателя уровень
надежности (доверительный интервал для средней), так как он определяется
исходя из предположения о нормальности выбо рки.
В заключение отметим следующее. Описательные статистики сильно
искажаются при наличии в выборке данных выбросов - грубых (ошибочных),
сильно отличающихся от основной массы наблюдений. Поэтому необходимо
провести хотя бы визуальный анализ данных с целью обнаружения выбросов.
В случае выявления аномального выброса необходимо определить причину
его возникновения (ошибки технического характера или аномальные уровни
из-за воздействия объективных факторов). В случае точного установления,
что выброс обусловлен ошибками технического характера необходимо ан омальное наблюдение либо устранить, либо заменить, по крайней мере, сре дней арифметической двух соседних уровней ряда данных. При построении
трендовой модели целесообразно также редактирование выбросов, обусло вленных объективными факторами.
7.2. Корреляционный и регрессионный анализ
Задачей экономического исследования является уяснение пр ироды
экономического объекта, раскрытие механизма взаимосвязи между важне йшими его переменными. Решение этой задачи позволяет разработать и ос уществить экономическую политику, необходимую для управления данным
объектом. Следует отменить, что любая экономическая политика заключае тся в регулировании экономических переменных, и она должна базироваться
на знании взаимосвязи ключевых переменных.
Изучение взаимосвязи экономических показателей проводится с и спользованием методов корреляционного и регрессионного анализа. На осн ове этих методов строятся однофакторные или многофакторные регр ессионные модели экономических объектов.
В общем случае моделирование производится с использованием
уравнений множественной регрессии. Множественная регрессия – это уравнение связи с несколькими независимыми переменными:
*
Нестеров Л.И. Нов ый этап расчетов показателей национального богатств а в мире // Вопросы ст атистики. – 2001. – № 3. – С. 12.
65
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
y  f ( x1, x2 ,, xk ) ,
y – зависимая переменная (результативный признак);
x1, x2 ,, xk – независимые переменные (факторы).
Основными этапами построения регрессионной модели являю тся:
1. Отбор факторов для построения модели.
2. Корреляционный анализ данных и отбор существенных факторов.
3. Выбор вида модели и численная оценка ее пар аметров.
4. Проверка качества модели.
Отметим, что в ряде случаев по результатам проверки качества модели
возможны повторные выполнения этапов 3, 4 с использованием уточненных
наборов существенных факторов.
Выбор факторов, влияющих на исследуемый показатель, производится
на основе качественного теоретико-экономического анализа. Для получения
надежных оценок в модель не следует включать слишком много факторов.
Обычно при оценке множественной регрессии требуется, чтобы число
наблюдений, по крайней мере, в 3 раза превосходило число оцениваемых
параметров. В противном случае параметры окажутся статистически незн ачимыми.
Однако теоретический анализ часто не позволяет однозначно ответить
на вопрос о количественной взаимосвязи рассматриваемых признаков и целесообразности включения фактора в модель. Поэтому производится сравн ительная оценка и отсев части факторов на основе коэффициентов парной
корреляции.
Для этого составляется матрица коэффициентов парной корреляции,
оценивающих тесноту связи каждого из факторов с результативным признаком и между собой. Коэффициенты парной корреляции вычисляются по
формуле:
где
N
 ( y  y)(x  x )
i
ry , x 
i
i 1
N
, .
N
 ( y  y)  ( x  x )
2
i
i 1
2
i
i 1
где y, x – соответствующие выборочные средние.
Связь считается достаточно сильной, если ry, x  0,7. Отметим, что
этот коэффициент дает объективную оценку тесноты связи лишь при лине йной зависимости переменных.
Для включения в модель отбираются те факторы, связь которых с зависимой переменной наиболее сильная. При э том анализируются также коэффициенты интеркорреляции (т.е. корреляция между факторами), что п озволяет исключить из модели дублирующие факторы. Считается, что два фа к-
66
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
тора коллинеарны (т.е. находятся между собой в линейной зависимости), е сли коэффициент корреляции больше 0,7.
Одним из условий построения множественной регрессии является н езависимость действия факторов. Коллинеарность факторов нарушает это
условие. Если факторы коллинеарны, то они дублируют друг друга. Поэтому
один фактор необходимо исключить. Предпочтение при этом отдается не
тому фактору, который более тесно связан с результативным признаком, а
тому фактору, который при достаточно тесной связи с результирующим им еет наименьшую тесноту связи с другими факторами.
Парные коэффициенты корреляции обнаруживают лишь коллинеарность между парами факторов. Возможна также мультиколлинеарность фа кторов, когда более чем два фактора связаны между собой линейной завис имостью, т.е. имеет совокупное воздействие факторов друг на друга. В результате нельзя оценить воздействие на результативный признак каждого фактора
в отдельности.
Включение в модель мультиколлинеарных факторов нежелательно, так
как:
 затрудняется интерпретация модели, параметры теряют экономический смысл;
 оценки параметров ненадежны (в ряде случаев даже невозможны, например, при использовании для оценивания метода
наименьших квадратов).
Модель оказывается не пригодной для анализа и прогнозирования. Поэтому необходимо устранение мультико ллинеарности, которое достигается
либо исключением одного или нескольких факторов, либо использованием
методов преобразования факторов, уменьшающих или исключающих ме жфакторную корреляцию.
При выборе вида модели часто используются следующие уравнения
регрессии:
1)
линейная: y  a0  a1x1  a2 x2    ak xk ;
2)
степенная y  a0 x1a1 x2a2  xkak .
На практике наибольшее применение находит линейная модель р егрессии.
Для оценивания параметров уравнения регрессии обычно используется
метод наименьших квадратов, при котором минимизируется сумма квадратов
N
остатков регрессии S 
e
2
i
, где


ei  yi  yi . Здесь y i - это оценка значе-
i 1
ния y i по модели.
Качество модели оценивается на основе анализа остатков регрессии.
Целесообразно провести хотя бы визуальный анализ графика остатков на
наличие какой-то зависимости, не учтенной в модели, и аномальных наблюдений – выбросов.
67
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Оценка качества построенной модели проводится также с использованием стандартной ошибки оценки и коэффициента детерм инации.
Стандартная ошибка оценки определяется по формуле
N
e
2
i
Se 
i 1
N  k 1
.
где k – число факторов.
Очевидно, чем ниже стандартная ошибка оценки, тем лучше аппро ксимирует регрессионная зависимость исходные данные.
Коэффициент детерминации R 2 вычисляется по формуле
N
N

ei2
( yi  y ) 2


R 2  1  N i 1
 iN1
,
0  R2  1.
2
2
 ( yi  y )  ( yi  y )
i 1
i 1
Можно также оперировать коэффициентом множественной корреляции (индексом корреляции) R , являющимся корнем квадратным из коэффициента детерминации.
Коэффициент детерминации является весьма важной характеристикой
качества построенной регрессии, он показывает долю общего разброса (относительно выборочного среднего зависимой переменной), которая объясняе тся ею.
В множественной регрессии добавление дополнительных факторов
увеличивает коэффициент детерминации. Поэтому целесо образно использование скорректированного (нормированного) коэфф ициента детерминации,
определяемого по формуле:
N 1
.
R 2  1  (1  R2 )
N  k 1
Для проверки значимости модели регрессии в целом и показателя те сноты связи R используется F-критерий Фишера
R2 N  k  1
.

k
1  R2
Проверка производится с использованием табличного (критического)
значения критерия Fкр при значениях степеней свободы  1  k ,
Fr 
 2  N  k  1 и уровне значимости  . Табличное значение F-критерия Фишера – это максимально возможное значение критерия под влиянием случа йных факторов при данных степенях свободы и уровне значимости. Здесь ур овень значимости  - это вероятность отвергнуть гипотезу о статистич еской
незначимости уравнения регрессии и показателя тесноты связи при условии,
что она верна. Обычно  принимается равной 0,05 или 0, 01.
68
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Если Fr > Fкр , то уравнение в целом и величина R признается статистически значимой. Отметим, что обычно на практике модель считается пр игодной для прогнозирования при R>0,7.
Значимость отдельных коэффициентов регрессии определяется с и спользованием t-критерия Стьюдента, определяемого как отношение коэфф ициента к его стандартной ошибке. По этому статистическому критерию пр оводится проверка гипотезы о равенстве нулю коэффициента уравнения. Если
расчетное значение t-критерия с (N-k-1) степенями свободы превосходит его
табличное значение при заданном уровне значимости, коэффициент считается значимым. В противном случае фактор, соответствующий этому коэфф ициенту, следует исключить из модели. Таким образом, производится оконч ательный отбор факторов, включаемых в м одель.
Рассмотрим технологию решения задач корреляционного и регрессионного анализа с помощью Пакета анализа MS Excel. Основными средствами решения этих задач являются инструменты Корреляция и Регрессия.
Инструмент Корреляция позволяет получить матрицу парных корреляций. С
помощью инструмента Регрессия, помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной
вероятности.
Порядок действий при решении задач многофакторного регрессионн ого анализа следующий:
1. Активизируйте инструмент Корреляция командой Сервис  Анализ данных  Корреляция. Диалоговое окно инструмента представлено на
рис. 55.
Рисунок 55. Диалоговое окно инструмента Корреляция
2. В диалоговом окне Корреляция введите следующую информацию:
 Входной интервал - диапазон ячеек, содержащих исходные
данные;
69
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»


Группирование - по столбцам или строкам;
Метки в первой строке – флажок (в случае выделения заголовков столбцов);
 Параметры вывода – установить переключатель в выбранном
варианте (целесообразно новый рабочий лист).
Щелкните на кнопке ОК диалогового окна.
3. Выполните анализ матрицы коэффициентов парной корреляции и
проведите на основе его результатов отбор значимых независимых переме нных.
4. Сформируйте новую таблицу, включающую значения зависимой
переменной и значимых независимых переменных на новом рабочем листе
путем копирования данных из исходной таблицы.
5. Активизируйте инструмент Регрессия командой Сервис  Анализ
данных  Регрессия. Диалоговое окно инструмента представлено на рис. 56.
Рисунок 56. Диалоговое окно инструмента Регрессия
6. Заполните диалоговое окно ввода данных и параметров вывода:
 Входной интервал Y – диапазон, содержащий значения зависимой переменной;
 Входной интервал X – диапазон, содержащий значения независимых переменных;
 Метки – флажок (первая строка содержит название столбцов);
 Константа – ноль – флажок (отсутствие свободного члена в
уравнении);
70
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»

Выходной интервал – диапазон для вывода результатов (достаточно указать левую верхнюю ячейку);
 Новый рабочий лист – произвольное имя нового листа.
7. Если необходимо получить дополнительную информацию и граф ики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке ОК.
8. Проверьте качество модели с помощью вычисленного значения Fкритерия Фишера, используя его минимальный уровень значимости для пр оверки гипотезы о значимости регрессии. Если полученный уровень значим ости (Значимость F) меньше уровня   0,05 , то модель статистически значима.
9. Проверьте адекватность уравнения регрессии по величине коэффициента детерминации R 2 . Чем ближе значение R 2 к 1, тем лучше модель
описывает исходные данные.
10. Проанализируйте коэффициенты регрессии на значимость, сра внивая P–значения (они соответствуют минимальным уровням значимости
полученных значений t-критериев Стьюдента для проверки гипотезы о р авенстве значений коэффициентов нулю) и выбранную величину уровня зн ачимости   0,05 . Если полученный уровень значимости (P–значение) для
коэффициента меньше этого уровня, то он статистически значим.
11. Проведите анализ результатов исследований качества модели. В
случае статистической незначимости модели в целом и ее отдельных коэ ффициентов сформируйте новый набор существенных факторов и повторно
выполните этапы 4-11.
12. Сравните расчетные (предсказанные) значения зависимой пер еменной с исходными значениями путем анализа остатков.
13. Выполните визуальный анализ качества модели с использованием
графиков остатков, подбора регрессии и примете решение об ее пригодности
для моделирования и прогнозирования изучаемого экономического проце сса.
В качестве примера рассмотрим решение задачи корреляцио нного и
регрессионного анализа данных о деятельности крупнейших компаний США
в 1996 г.
Исходные данные задачи  и результат корреляционного анализа
(часть матрицы коэффициентов парной корреляции) пр иведены на рис. 57.
Линейное уравнение множественной регрессии, включающее все фа кторы, имеет следующий вид:
y  a0  a1x1  a2 x2  a3 x3  a4 x4 .
Анализ матрицы коэффициентов парной корреляции показывает, что
наиболее тесную связь с результирующим признаком y имеют следующие
факторы (в порядке убывания тесноты связи):
*
Практику м по эконометрике/ Под ред. И.И. Елисеев ой. – М.: Финансы и статистика, 2002. – С. 92.
71
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
 чистый доход x1;
 численность служащих x3;
 оборот капитала x2.
Фактор x4 имеет слабую связь с результирующим признаком y и не
включается в набор существенных факторов.
Наблюдается также тесная межфакторная связь между факторами x1,
x2 и x3 (факторы коллинеарны между собой). Поэтому целесообразно исключить из уравнения множественной регрессии факторы x2 и x3 как малоинформативные, недостаточно статистически надежные.
В результате корреляционного анализа приходим к следующей стру ктуре регрессионной модели:
y  a0  a1 x1.
№
п/п
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Чистый
Оборот Использованны Численность
Рыночная
доход,
капитала,
й капитал,
служащих, капитализация
млрд. долл. млрд. долл. млрд. долл.
тыс. чел.
, млрд. долл.
y
x1
x2
x3
x4
0,9
31,3
18,9
43
40,9
1,7
13,4
13,7
64,7
40,5
0,7
4,5
18,5
24
38,9
1,7
10
4,8
50,2
38,5
2,6
20
21,8
106
37,3
1,3
15
5,8
96,6
26,5
4,1
137,1
99
347
37
1,6
17,9
20,1
85,6
36,8
6,9
165,4
60,6
745
36,3
0,4
2
1,4
4,1
35,3
1,3
6,8
8
26,8
35,3
1,9
27,1
18,9
42,7
35
1,9
13,4
13,2
61,8
26,2
1,4
9,8
12,6
212
33,1
0,4
19,5
12,2
105
32,7
0,8
6,8
3,2
33,5
32,1
1,8
27
13
142
30,5
0,9
12,4
6,9
96
29,8
1,1
17,7
15
140
25,4
1,9
12,7
11,9
59,3
29,3
-0,9
21,4
1,6
131
29,2
1,3
13,5
8,6
70,7
29,2
2
13,4
11,5
65,4
29,1
0,6
4,2
1,9
23,1
27,9
0,7
15,5
5,8
80,8
27,2
72
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Матрица коэффициентов парной корреляции
y
y
x1
x2
x3
x4
1
0,8480292
0,763298
0,8295679
0,2689771
x1
1
0,8977439
0,9115047
0,2486806
x2
x3
1
0,712513648
0,348493848
1
0,11519554
x4
1
Рисунок 57. Исходные данные и результат применения
инструмента Корреляция
Вычисление параметров этого линейного уравнения парной ре грессии
проводится с помощью инструмента Регрессия. Часть результатов вычислений представлена на рис. 58.
Регрессионная статистика
Множественный R
0,848
R-квадрат
0,7192
Нормированный R-квадрат
0,7069
Стандартная ошибка
0,7792
Наблюдения
25
Дисперсионный анализ
df
SS
Регрессия
1
35,7563
Остаток
23
13,9637
Итого
24
49,72
MS
35,7563
0,60712
F
58,8953
Значимость F
8,68526E-08
Рисунок 58. Результаты регрессионной статистики и
дисперсионного анализа.
Параметры уравнения регрессии представлены в табл.5.
Таблица 5.
Y-пересечение
x1
Коэффициенты
0,756294811
0,031503026
Стандартная ошибка
0,187755993
0,00410499
t-статистика
4,028072807
7,674325418
P-Значение
0,000524651
8,68526E-08
Нижние 95%
0,367892475
0,023011219
Верхние 95%
1,144697146
0,039994832
73
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
В результате вычислений имеем следующее уравнение регрессии:
y  0,756294811 0,031503026x1.
Коэффициенты уравнения статистически значимы и надежны, так как
их P-значения меньше 0,05. Отметим, что при включении в уравнение р егрессии всех факторов коэффициенты оказываются незначимыми (Pзначения всех коэффициентов оказываются выше даже уровня 0,1).
Оценка надежности уравнения регрессии в целом оказывается достаточно высокой, так как значение параметра Значимость F меньше 0,05. На
это указывают также значения скорректированного и нескорректированного
коэффициентов детерминации в рамках регрессионной статистики.
В итоге решения задачи можно сделать следующий вывод: многофакторная линейная регрессионная модель содержит лишь один существенный
фактор x1, поэтому можно ограничиться линейным уравнением парной р егрессии. Это относительно простое уравнение оказывается пригодным для
анализа и прогнозирования.
В заключение отметим, что углубленный анализ адекватности регрессионной модели требует более детального анализа остатков. Согласно общим
предположениям регрессионного анализа, остатки должны вести себя как
независимые (в действительности почти независимые), одинаково ра спределенные случайные величины. В классических методах регрессионного анализа предполагается также нормальный закон распределения остатков. Использованные в инструменте Регрессия статистические критерии оценки качества
модели базируются именно на этом предположении.
На практике в большинстве случаев целесообразна проверка о статков
на независимость с использованием соответствующих статистических критериев, например критерия Дарбина-Уотсона. Эффективные методы имеются
также для проверки нормальности распределения. Однако детальная проверка гипотезы о нормальности распределения требует довольно значительных
объемов выборки (как минимум, порядка сотни наблюдений), что часто огр аничивает возможность проведения такого исследования.
7.3. Анализ и прогнозирование временных рядов
Временной ряд (ВР) – это последовательность упорядоченных по вр емени числовых показателей, характеризующих уровень состояния и измен ения изучаемых явлений.
Данные типа ВР широко распространены в экономике. В качестве
примера можно привести ежедневные цены на акции, курсы валют, ежедневные, месячные и годовые объемы продаж и т.п.
Исследованием ВР занимается самостоятельная и весьма обширная
область статистики – анализ ВР. Круг задач, решаемых в рамках этой науки,
весьма обширен. Ограничимся рассмотрением решения задачи прогнозир о-
74
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
вания ВР на основе экстраполяции тренда, которая часто встречается в пр икладных задачах в области экономики.
Экстраполяция – это распространение выявленной при анализе ВР закономерности развития изучаемого явления на будущее. При этом часто используется закономерность изменения основной тенденции (тренда).
Тренд ВР – это плавно изменяющаяся компонента, описывающая влияние долговременных факторов, эффект которых сказывается постепенно. В
качестве примеров таких факторов в экономике можно привести рост потребления и изменения его структуры, изменение демографических характеристик популяции и т.д.
Тренд предпочитают описывать с помощью гладких кривых, зада ющихся в достаточно простом аналитическом виде. Следует отметить, что анализ ВР обычно начинается с выделения именно этой ко мпоненты. Выделение
тренда позволяет перейти к дальнейшей идентификации других компонент
ряда, например, сезонных и циклических.
Решение задач прогнозирования по тренду выполняется в следующей
последовательности:
1. Предварительный анализ ВР и формирование набора моделей
тренда для прогнозирования.
2. Оценка погрешностей моделей.
3. Получение прогнозов и графический анализ результатов.
4. Выбор лучшей модели и соответствующего прогноза.
5. Содержательный анализ полученного прогноза.
Относительно просто решение этой задачи в среде MS Excel достигается с использованием средства Мастер диаграмм. Типы линий тренда, используемые в этом средстве MS Excel, приведены в табл. 6.
Таблица 6.
Тип зависимости
Уравнение
Линейная
y  a0  a1x
Полиномиальная
y  a0  a1x  a2 x 2    ak x k , k  2,6
Логарифмическая
y  a0 a1ln x
Экспоненциальная
y  a0ea1 x
Степенная
y  a0 x a1
Технология решения рассматриваемой задачи в среде MS Excel (без
детализации шагов построения графика ВР) может быть представлена следующей последовательностью действий:
1. Постройте график ВР с помощью Мастера диаграмм.
2. Проведите визуальный анализ графика. Добавьте линию тренда в
диаграмму следующей последовательностью действий:
 выделите линию графика;
75
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
выполните команду Диаграмма  Добавить линию тренда;
в диалоговом окне выберите вкладку Тип, щелкните на требуемом типе тренда (при выборе типа Полиномиальная введите
также значение степени от 2 до 6 в поле Степень);
 перейдите на вкладку Параметры и установите флажки Показывать уравнение на диаграмме и Величина достоверности
аппроксимации, в соответствующих полях введите количество
периодов прогнозирования, которые будут добавлены к линии
тренда впереди;
 щелкните на кнопке ОК для завершения процесса создания линии тренда.
3. Выполните шаг 2 для других выбранных типов тренда.
4. Выберите лучшие модели тренда на основе анализа величин досто

2
верности аппроксимации (модели с наибольшими значениями R ).
5. Проведите визуальный анализ результатов прогноза по лучшим
моделям и выберете наилучшую модель для прогноза.
6. Занесите параметры уравнения наилучшей модели тренда в диапазон ячеек соответствующей размерности рабочего листа.
7. Вычислите теоретические значения ВР по трендовой модели на
анализируемом периоде и периоде прогнозирования.
8. Проведите содержательный анализ прогнозных значений ВР с целью выявления возможных противоречий известным фактам и сложившимся
к настоящему времени представлениям о характере развития в периоде пр огнозирования.
Остановимся подробнее на отдельных шагах представленного укру пненного алгоритма решения задачи.
При выборе типа тренда не следует выбирать Скользящее среднее, так
как он не позволяет получить аналитическое описание, необходимое для пр огнозирования. Это тип тренда можно использовать для визуального анализа
ВР с целью предварительного выбора вида трендовой модели.
Вычисление значений уровней тренда проводится по полученной м одели тренда f (t ) , поставляя в него в качестве аргумента значения соответствующего условного времени:

yt  f (t ), t  1, N ,

y*N l  f ( N  l ), l  1, L,
где:
t

y – значение уровня тренда;

y *N  l – прогнозируемый уровень;
N – количество уровней ВР;
l – период упреждения.
Отметим, что оценки параметров уравнения тренда, вычисляемые
средствами Мастера диаграмм, являются оценками метода наименьших
76
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
квадратов. В качестве показателя достоверности аппроксимации используе тся величина R 2 - коэффициент детерминации. При сравнении моделей с разным количеством параметров целесообразно оперировать скорректирова нным коэффициентом детерминации.
В качестве примера рассмотрим задачу прогноза объема продаж товара фирмы на 2003 и 2004 годы, используя метод экстраполяции ВР по тренду.
Данные о продажах за последние 7 лет (в тыс. долларах США) пр иведены в
табл. 7.
Таблица 7.
Годы
Объем продаж, тыс. долларов США
исходное значение
трендовое значение
1996
212
219
1997
280
274
1998
317
307
1999
332
330
2000
347
348
2001
356
363
2002
375
375
2003
386
2004
395
Отметим, что трендовые значения в таблице округлены до точности пре дставления исходных данных.
Для решения задачи с помощью Мастера диаграмм MS Excel построим график исходного ВР. Визуальный анализ графика показывает отсутствие
выбросов, что позволяет провести построение трендовой модели без реда ктирования данных.
Исследуем различные модели тренда и выберем из них лучшую для
построения прогноза по наибольшей величине коэффициента детерминации
R 2 . В рассматриваемом примере по разным уравнениям тренда получены
следующие результаты:
Таблица 8.
2
Вид уравнения
Скорректированное
R
R2
линейный
0,8737
0,8484
логарифмический
0,9878
0,9854
степенной
0,9673
0,9608
экспоненциальный
0,8167
0,78
Использованные модели тренда характеризуются двумя пар аметрами,
поэтому требование, как минимум, трехкратного превышения количества
уровней ВР над числом оцениваемых параметров выполняется. Отметим
также, что здесь нет необходимости в дополнительном анализе значений
77
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
скорректированных коэффициентов детерминации, так как выбранные р егрессионные модели характеризуются одинаковым количеством оценива емых параметров.
Анализ результатов показывает, что исходные данные лучше всего
описывает логарифмическая модель тренда, описываемая ура внением
f (t )  219,11  80,378ln(t ), t  1,7.
Следовательно, расчет прогнозных значений следует вести по этому
уравнению. Вычисленные трендовые значения ВР также приведены в табл. 7.
Графики ВР и его логарифмического тренда с прогнозными значени ями представлены на рис. 59.
Визуальный анализ графиков показывает, что выбранный тип тренда
хорошо описывает исходный ряд и прогнозные значения не противо речат
общей тенденции продаж. Полученной кривой и ее уравнением можно во спользоваться для построения прогноза.
Графики временного ряда Объем продаж и его тренда
Объем продаж
400
350
y = 80,378Ln(x) + 219,11
R2 = 0,9878
300
250
200
1996
1997
1998
1999
2000
2001
2002
2003
2004
Годы
Объем продаж
Логарифмический (Объем продаж)
Рисунок 59.
В итоге получены следующие результаты. Прогноз объема продаж товара фирмы может составить:
 386 тыс. долларов США на 2003 г.;
 395 тыс. долларов США на 2004 г.
Рассмотренный метод прогнозирования ВР по тренду достаточно
прост в реализации средствами MS Excel и часто используется в прикладных
экономических задачах для получения точечных прогнозов.
В заключение отметим следующее. Прогноз экономических показателей на базе трендовых моделей основывается на допущении, что закономе рности их изменения будут действовать на определенном отрезке времени в
78
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
будущем. Однако такое условие в реальности часто нарушается. Поэтому
прогнозирование по тренду в большинстве случаев можно применять с упр еждением на один, максимум на два интервала ВР. В случае среднесрочного
прогнозирования целесообразнее использование многофакторных регресс ионных моделей. Многофакторные модели позволяют точнее отразить процесс
формирования экономических ВР, чем трендовые о днофакторные модели.
Контрольные задания по разделу 7.
Задание 7.1. Для статистических рядов данных Y, X2 и X4, приведенных в табл. 9 получить сводную таблицу основных статистических характеристик (описательные статистики). Провести анализ результатов.
Таблица 9.
Y
X1
X2
X3
X4
X5
126
0
4
15
17
100
137
1
4,8
14,8
17,3
98,4
148
2
3,8
15,2
16,8
101,2
191
3
8,7
15,5
16,2
103,5
274
4
8,2
15,5
16
104,1
370
5
9,7
16
18
107
432
6
14,7
18,1
20,2
107,4
445
7
18,7
13
15,8
108,5
367
8
19,8
15,8
18,2
108,3
367
9
10,6
16,9
16,8
109,2
321
10
8,6
16,3
17
110,1
307
11
6,5
16,1
18,3
110,7
331
12
12,6
15,4
16,4
110,3
345
13
6,5
15,7
16,2
111,8
364
14
5,8
16
17,7
112,3
384
15
5,7
15,1
16,2
112,9
Задание 7.2. На основе статистических данных, приведенных в табл.
9, построить линейную многофакторную регрессионную модель и проверить
ее качество. Провести интерпретацию результатов.
Задание 7.3. По данным о недельных объемах продаж торгового
предприятия по одной группе товаров ($ США), представленных в табл. 10,
составить прогноз на две недели по тренду.
Таблица 10.
Неделя
1
2
3
4
5
6
7
8
9
10
11
12
Объем
продаж
175
263
326
297
247
298
79
366
297
420
441
453
399
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Тесты по разделу 7.
1. Статистическую обработку данных можно проводить с использованием:
а) табличных процессов;
б) математических пакетов общего назначения;
в) статистических пакетов; г) пакетов для решения задач математического
программирования.
2. При статистической обработке данных с использованием компьютерных
технологий целесообразно придерживаться:
а) режима пакетной обработки данных;
б) режима автоматической обработки данных;
в) графически-ориентированного подхода; г) мультикластерного подхода.
3. Решение задач статистического анализа и прогнозирования в среде MS
Excel поддерживается следующими средствами:
а) встроенными функциями категории Статистические;
б) надстройкой Пакет анализа;
в) Мастером диаграмм;
г) надстройкой Анализ решения.
4. Окно выбора требуемого инструмента надстройки Пакет анализа открывается командой:
а) Сервис  Анализ данных;
б) Сервис  Пакет анализа;
в) Вставка  Функция  Инструменты;
г) Вид  Панели инструментов  Анализ данных.
5. Если статистическая функция является формулой массива, то ввод форм улы необходимо завершить нажатием:
а) на клавишу F4;
б) на клавишу Ctrl;
в) на комбинацию клавиш Ctrl+Shift+Enter;
г) на комбинацию клавиш Ctrl+Enter.
6. Инструменты надстройки Пакет анализа возвращают результаты в виде:
а) массива констант;
б) массива формул;
в) массива статистических функций;
г) в представленных вариантах нет правильного ответа.
7. При изменении исходных данных, являющихся аргументами встр оенных
статистических функций MS Excel, результат пересчитывается:
а) в автоматическом режиме;
б) после нажатия на клавишу F9;
в) после нажатия на клавиши Ctrl+F9; г) в режиме отложенного времени.
8. Автоматический перерасчет результата при изменении исхо дных данных
отсутствует при использовании:
а) статистических функций MS Excel;
б) инструментов Пакета анализа;
в) векторно-матричных функций MS Excel; г) формулы массива.
9. Мастер диаграмм MS Excel позволяет получить аналитическое описание:
а) линейной трендовой модели ряда данных;
б) нелинейной трендовой модели ряда данных;
в) остаточного ряда;
г) статистических характеристик ряда данных.
80
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
10. Статистические характеристики рядов данных можно получить с использованием:
а) статистических функций MS Excel;
б) инструмента Описательная статистика Пакета анализа;
в) таблицы дисперсионного анализа инструмента Регрессия Пакета анализа;
г) надстройки Статистика.
8. ЯЗЫК ПРОГРАММИРОВ АНИЯ VBA И МАКРОСЫ
Макрос представляет собой последовательность операций над Рабочими листами, записанную в виде программы на языке Visual Basic for Applications (VBA). При помощи макросов пользователь может зафиксировать в памяти компьютера часто повторяющиеся последовательности действий, что
позволит избежать рутинных операций и ошибок, а также сэкономить время.
8.1.
Создание макросов
Создание макроса начинается с команды Сервис  Макрос  Начать
запись. По этой команде программа активизирует окно Запись макроса (рис.
60), в котором пользователь может
задать оригинальное имя макропоследовательности и при необходимости
назначить для нее горячую клавишу.
Пробелы и
Рисунок 60.
Рисунок 61.
другие символы в имени макроса не допускаются. После выхода из окна а втоматически включается запись макроса. Теперь все, что бы вы ни соверш или при помощи клавиатуры или мыши, будет переводиться программой на
Visual Basic. Именно сейчас вам и надо выполнить те действия, которые
должны повторяться при каждом запуске этого макроса. Для обеспеч ения
остановки записи макропоследовательности программа выводит панель и нструментов Останов. Если панель не появилась, остановить запись макроса
можно командой Сервис  Макрос  Остановить запись.
Макрос можно найти при помощи команды Сервис  Макрос  Макросы,
просмотрев список доступных в данный момент макросов, как показано на
рис. 61 .
81
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
8.2.
Запуск макроса
Запустить макрокоманду можно нажатием кнопки Выполнить в окне
Макрос (см. рис. 61) или, используя соответствующую комбинацию клавиш,
если она была определена.
Намного более удобным является создание специальной кнопки для запуска макроса и размещение ее на панели инструментов. Для этого необходимо:
1. Выбрать команду Сервис  Настройка.
2. На вкладке Команды открывшегося окна Настройка (см. рис. 62)
указать категорию Макросы.
3. Перетащить мышью настраиваемую кнопку на панель инструментов туда, где вы хотите ее видеть.
4. Щелкнуть по новой кнопке правой кнопкой мыши и выбрать из
контекстного меню команду Назначить макрос.
5. В открывшемся окне выбрать макрос из списка (см. рис. 61).
Контекстное меню новой кнопки позволяет также задать ее им я и рисунок на ней. Для этого необходимо в разделе Команды окна Настройка (см.
рис. 62) применить команду Настраиваемая команда меню. Вызвав правой
кнопкой мыши контекстное
меню новой кнопки на панели
инструментов, примените команду Выбрать значок для
кнопки и выберите в открывшейся коллекции значков необходимое изображение.
Не забывайте перед запуском любого макроса пом ещать курсор в ту ячейку, которая была выделенной на момент начала записи макроса.
Рисунок 62.
8.3. Использование встроенного языка программирования VBA
Visual Basic for Applications (VBA) – это сочетание одного из самых
простых языков программирования Basic и всех вычислительных возможностей MS Excel [10]. Специальный механизм обращения к объектам основных
приложений – MS Excel, MS Word, MS Access, MS PowerPoint превращает
VBA в мощное средство разработки полнофункциональных программ, раб отающих в среде приложений MS Office. Это отличное средство автоматизации не только простых рутинных операций (см. разделы 8.1. и 8.2.), которые
пользователю приходится периодически выполнять, например, при подго82
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
товке текстов в MS Word, но и сложнейших вычислений над большими объемами данных в MS Excel.
Поскольку основы языка программирования Visual Basic (VB) изучаются в рамках дисциплины Информ атика в школе и ВУЗах, целесообразно
привести сравнение двух языков - VBA и VB.
VBA предназначен для создания и выполнения программ в среде MS
Office (где, например, нельзя скомпилировать EXE – файл).
VB (5.0 и 6.0 версии) – средство разработки программ, которое по своим функциональным возможностям и назначению аналогично таким инстр ументам, как Delphi или C++Builder. Результатом работы VB – программиста
являются EXE – файлы, DLL – библиотеки и другие компоненты, которые
предназначены для использования вне среды VB – в операционных системах
Windows.
Общими для VBA и VB являются:
1. язык программирования (во всех аспектах – типы данных, правила
объявления и использования переменных, процедур и функций);
VBA соблюдает основной синтаксис и правила программирования
языков-диалектов Basic;
2. во многом похожие средства разработки и отладки программ; VBA
содержит все средства, характерные для современных средств разработки программного обеспечения: интегрированную среду ра зработки, конструктор форм, мощный отладчик, не уступающий по
своим возможностям отладчикам некоторых современных компиляторов;
3. поддержка аналогичных моделей в рамках современной конце пции
разработки программных систем – объектно-ориентированного и
событийно-управляемого программирования, а также элементов
управления на базе ActiveX, интеграции с базами данных, систем ами электронной почты и Интернетом;
4. общие библиотеки объектов, механизмы подключения и использования библиотек.
В то же время, VBA является общей платформой для всех приложений
MS Office. Каждое из приложений MS Office имеет свой набор объектов (до
100). Соответственно общее количество свойств и методов может приближаться к 1000. Например, в MS Word используются объекты – документы,
абзацы; в MS Access – базы данных, таблицы; в MS PowerPoint – слайды,
анимация. В структуре объектов MS Excel содержатся объекты WorkBooks
(рабочие книги), WorkSheets (рабочие листы), Cells (ячейки) и прочие. У
объектов, в свою очередь, есть знакомые пользователю VB свойства. Например, у конкретного объекта WorkSheets есть свойство Name (имя рабочего
листа), а его значением является название, «написанное» на ярлычке рабоч его листа (по умолчанию – Лист, Лист2, … Sheet1, Sheet2 …).
VBA содержит все привычные (имеющиеся в любом современном языке программирования) встроенные простые типы данных: логические, ари ф83
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
метические и строковые. Например, Integer, Long, Single, Double, String,
Boolean, Variant и прочие.
Объявление простых переменных имеет следу ющий синтаксис:
{Dim | Private | Public | Static } <имя переменной> [ As <имя типа> ] [, <имя
переменной> [ As <имя типа> ] ]
VBA – операторный язык, поэтому его программы (модули) предста вляют последовательность операторов. Рассмотрим типичные операторы
управления: условный оператор If-Then и оператор цикла For-Next. Применим эти операторы на, соответственно, двух примерах создания с помощью
VBA приложений для MS Excel.
Однострочная форма синтаксиса условного оператора If-Then имеет
вид:
If УсловноеВыражение Then Оператор1 [ Else Оператор2 ]
и многострочная форма для блока операторов:
If УсловноеВыражение Then
ПоследовательностьОператоров1
[ Else
ПоследовательностьОператоров2 ]
End If
Оператор If-Then управляет вычислениями, позволяя выбирать или
выполнять действия в зависимости от истинности некоторого условия.
Рассмотрим первый пример создания функции пользователя VBA [10],
вычисляющей комиссионные, начисляемые по следующему правилу:

Если продукции продано не меньше чем на 10000 руб., то комиссионные составляют 2% от стоимости реализованной продукции.

Если продукции продано меньше чем на 10000 руб., то комисс ионные составляют 1% от стоимости реализованной проду кции.

Если стаж работы в фирме не меньше 3 лет, то производится доплата в размере 0,5% от стоимости реализованной продукции.
Вариант функции пользователя имеет вид:
Function Премия (стаж, продажа)
If стаж >= 3 And _
продажа >=10000 Then Премия=0.025*продажа
If стаж < 3 And _
продажа >=10000 Then Премия=0.02*продажа
(8.3.1)
If стаж >= 3 And _
продажа <10000 Then Премия=0.015*продажа
If стаж < 3 And _
продажа <10000 Then Премия=0.01*продажа
End Function
Следующий этап заключается в создании функции пользователя, имеющей имя Премия. Функция пользователя после своего создания заносится в
84
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
библиотеку функций. С созданными пользователем функциями мо жно работать с помощью Мастера функций точно так же, как и со встроенными
функциями рабочего листа (см. раздел 2.5. Автоматизация построения фун кций). Функция рабочего листа, определенная пользователем, - это процедура
Function, которую можно указать в формуле, хранящейся в ячейке.
Создадим и используем функцию пользователя для нашего первого
примера. Загрузите MS Excel. Введите команду Сервис  Макрос  Макросы. В открывшемся окне Макрос введите имя макроса Премия и нажмите
кнопку Создать. В результате откроется приложение VBA (см. рис. 63), в
котором будет открыт уже существующий Модуль (или создан новый),
например, Модуль1.
Рисунок 63.
На экране будут видны две служебные строки:
Sub Премия( )
End Sub
Замените эти строки на листе Модуля текстом нашей программы (8.3.1),
то есть имя процедуры Sub заменяем на Function, заполняем между скобками аргумент будущей функции пользователя Премия( ), а между двумя служебными строками помещаем основной программный код (текст програ ммы). Примените команду сохранения программы и закройте окно редактир ования кода, представленного на рис. 63. Запустите Мастер функций (см. раздел 2.5) и убедитесь, что в Полном алфавитном перечне встроенных функций
появилась новая под именем Премия.
Следующий этап заключается в создании формы на рабочем листе
MS Excel для ввода исходных данных и формул расчета. Пусть в ячейку A1
введена величина стажа работы в фирме, равная 15, а в ячейку B1 – объем
реализованной продукции, равный 50000. Для того, чтобы вычислить коми ссионные в ячейке C1, достаточно в эту ячейку ввести формулу
=Премия(A1;B1). Это можно сделать непосредственно в Строке формул с
85
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
помощью клавиатуры, либо с помощью Мастера функций. Результат, полученный в ячейке C1, принимает значение 1250.
Рассмотрим второй пример построения функции пользователя, вычисляющей разность между текущим объемом вклада и размером ссуды при п остоянной годовой процентной ставке и неравномер ных платежах, то есть
функции
n
P( j)
F  
(8.3.2)
(
d
(
j )  d (1)) / 365 ,
j 1 (1 i )
где
P (1) и d (1) - размер и дата выдачи ссуды, причем P (1) берется со
знаком минус; P ( j ) и d ( j ) - размер и дата j-й выплаты; n-1 – число выплат;
i – годовая процентная ставка.
Для решения задачи воспользуемся оператором цикла For-Next, который обеспечивает многократное выполнение блока операторов п ри последовательном изменении счетчика от начального до конечного значения с указанным шагом изменения.
Форма синтаксиса оператора цикла For-Next имеет вид:
For Имя = Значение1 To Значение2 [Step Значение3 ]
ПовторяющиесяОператоры
Next [ Имя]
Вариант функции пользователя для (8.3.2), использующий оператор
цикла, имеет вид:
Function Доход (процент As Double, _
платеж As Variant, _
год As Variant) As Double
Dim i, j, n As Integer, s As Double
(8.3.3)
n = платеж.Rows.Count
s =0
For i = 1 To n
s = s + платеж (i) / _
(1 + процент)^((год(i) – год(1))/365)
Next i
Доход = s
End Function
Создадим функцию пользователя аналогично первому примеру и решим с помощью функции Доход следующую задачу. Предположим, что
11.01.97 у вас берут в долг 10000 руб. и предлагают вернуть: 20.12.97 – 2000
руб., 18.10.98 – 4000 руб., 12.04.99 – 7000 руб. Имеет ли смысл эта сделка при
годовой ставке 10%?
86
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Для решения этой задачи введем данные на рабочем листе MS Excel,
как показано на рис. 64.
Рисунок 64.
Ссуда введена в ячейке B2 со знаком минус, так как эти у вас забирают.
В ячейку B8, где вычисляется разность между текущим объемом вклада и
размером ссуды, введем формулу =Доход(B7;B2:B5;D2:D5). В данном случае найденное значение равно 857,91. Так как результат положителен, данная
сделка выгодна.
Отметим, что метод Rows в программном коде (8.3.3) возвращает строки диапазона платеж, а свойство Count считает число элементов объекта.
Таким образом, платеж.Rows.Count определяет число строк в диапазоне
платеж. Если требуется найти число столбцов диапазона платеж, то нужно
использовать конструкцию платеж.Columns.Count.
В случае, если в тексте программы допущена ошибка и его необходимо
отредактировать заново, окно редактора кода (см. рис. 63) открывается командой Сервис  Макрос  Редактор Visual Basic. Дополнительные возможности по созданию приложений VBA предоставляет использование специальной панели инструментов, которую можно вызвать командой Вид 
Панели инструментов  Visual Basic.
Контрольные задания по разделу 8.
Задание 8.1. Создайте пять отдельных макросов для всех этапов расчета в
электронной таблице, изображенной на рис. 11. А, именно, для расчета фун кций синуса и косинуса первым макросом, а остальными - сумм, средних, максимальных и минимальных значений, соответственно. Для двух макросов
расчета максимальных и минимальных значений создайте специальные
кнопки для запуска и разместите их в строке Меню, снабдив краткими
наименованиями и разными изображениями.
Задание 8.2. Создайте макрос для построения диаграммы на основе блока
данных A1:C6 из таблицы, изображенной на рис. 11. Убедитесь, что для пр а-
87
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
вильного построения диаграммы необходимо предварительно очистить с одержимое ячейки A1. В противном случае диапазон A2:A6 воспринимается
не как диапазон значений аргумента функции, а как дополнительный третий
функциональный ряд данных, что и отражается на диаграмме.
Задание 8.3. Создайте макрос для сортировки по фамилиям сотрудников в
таблице, представленной на рис. 14 (см. контрольное задание 5.3).
Задание 8.4. Создайте макрос сортировки для последнего варианта ко нтрольного задания 5.4.
Задание 8.5. Создайте отдельные макросы для расчета средней заработной
платы (см. рис. 15), сортировки по возрастанию и убыванию оклада, по алфавиту фамилий сотрудников в таблице, представленной на рис. 14.
Задание 8.6. С помощью справки (см. раздел 2.10. Получение справочной
информации) изучите команды Скрыть и Отобразить столбец. Создайте
два макроса для поочередного применения этих команд к графе «Сумма в
руб.» таблицы прайс-листа, представленной на рис. 16.
Задание 8.7. Создайте макрос в соответствии с вариантом задания. Назнач ьте созданный макрос какой-либо панели инструментов, то есть разместите на
панели кнопку для запуска макроса. Добавьте к макросу необходимые ко мментарии. Проверьте работу макроса. Внесите в код созданного макроса какие-либо изменения и проследите, как это отразится на его работе.
Макрос, копирующий содержимое одной ячейки электронно й таблицы в другую ячейку.
2. Макрос, добавляющий новую строку электронной таблицы над заданной строкой.
3. Макрос, вставляющий формулу в ячейку электронной таблицы.
4. Макрос, меняющий местами заданные строки электро нной таблицы.
5. Макрос, добавляющий в ячейку электронной таблицы текст заданного цвета.
6. Макрос, изменяющий цвет фона ячейки электронной таблицы и цвет
символов.
7. Макрос, изменяющий формат выводимого в ячейке электронной та блицы числа.
8. Макрос, добавляющий новый столбец слева от заданного столбца
электронной таблицы.
9. Макрос, удаляющий все содержимое из рабочей книги электронной
таблицы.
10. Макрос, удаляющий все содержимое рабочей книги.
1.
Задание 8.8. Решите задачу начисления комиссионных, если вместо (8.3.1),
используется вариант функции пользователя следующего вида:
Function Премия (стаж As Integer, _
продажа As Double) As Integer
88
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Dim sAge, sSale As Integer
If стаж >= 3 Then sAge = 0.005 * продажа
If продажа >= 10000 Then
sSale = 0.02 * продажа
Else
sSale = 0.01 * продажа
End If
Премия = sAge + sSale
End Function
Задание 8.9. Получите тот же результат для комиссионных 1250, что и в задании 8.8., при вводе в ячейку C1 следующей формулы:
=ЕСЛИ(И(A1>=3;B1>=10000);B1*0,025;ЕСЛИ(И(A1<3;B1>=10000);B1*0,02;
ЕСЛИ(И(A1>=3; B1<10000);B1*0,015; ЕСЛИ(И(A1<3; B1<10000);B1*0,01 ))))
или формулы:
=ЕСЛИ(A1>=3;B1*0,005)+ЕСЛИ(B1>=10000;B1*0,02; B1* 0,01)
Отметим, что данный подход к решению задачи аналогичен примеру
использования встроенной функции =ЕСЛИ( ), приведенному в разделе 2.7.
Задание 8.10. С помощью функции пользователя решите задачу начисления
комиссионных [10], если размер комиссионных зависит только от объема
проданной продукции по следующему правилу:
 Если объем продаж находится в диапазоне 0 – 19999 тыс. руб.,
то комиссионные составляют 10% от объема продаж.
 Если объем продаж находится в диапазоне 20000 – 49999 тыс.
руб., то комиссионные составляют 12%.
 Если объем продаж находится в диапазоне 50000 тыс. руб. и
более, то комиссионные составляют 16%.
Задание 8.11. Получите тот же результат 24000 тыс. руб., что и в задании
8.10., при вводе в ячейку A1 объема продаж 150000 тыс. руб., и вводе в ячейку B1 следующей формулы начисления комиссионных:
=ЕСЛИ(И(A1>=0; A1<20000); A1*0,1; ЕСЛИ(И(A1>=20000; A1<50000);
A1*0,12; ЕСЛИ(A1>=50000; A1*0,16)))
Тесты по разделу 8.
1. Макрос в табличном процессоре MS Excel использует язык:
а) QBASIC; б) Visual Basic; в) Visual Basic for Applications; г) Pascal.
2. Макрос может быть запущен с помощью:
а) комбинации горячих клавиш;
б) команды Сервис  Макрос  Макросы  Войти;
в) комбинации холодных клавиш;
г) команды Сервис  Макрос  Макросы  Выполнить.
89
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3. Для остановки записи макроса можно использовать:
а) команду Сервис  Макрос  Изменить;
б) панель инструментов Останов;
в) команду Сервис  Макрос  Остановка;
г) панель инструментов Остановить запись.
4. При задании имени макроса ограничения в использу емых символах:
а) отсутствуют;
б) имеются.
5. Запуск макроса в MS Excel возможен с помощью:
а) создания специальной кнопки на панели инструментов;
б) использования имеющихся стандартных кнопок на панели инструме нтов.
6. Макросы вносят в работу с электронными таблицами MS Excel:
а) принципиальные изменения (с добавлением новых функциональных
возможностей);
б) не принципиальные изменения (без добавления новых ф ункциональных
возможностей).
7. Можно ли команды создания кнопки для запуска макр оса поместить в сам
макрос?
а) да;
б) нет.
8. При создании кнопки для запуска макроса можно:
а) выбрать значок кнопки;
б) изменить значок кнопки;
в) задать имя кнопки;
г) изменить имя кнопки.
9. Число макросов, относящихся к одному Рабочему листу, конкретным количеством:
а) ограничено;
б) не ограничено.
10. Процедуры создания макроса в MS Excel и MS Word принципиальные
отличия:
а) имеют;
б) не имеют.
9. ИМПОРТ ДАННЫХ MS EXCEL в MS WORD
Текстовый процессор MS Word позволяет создавать эффектные таблицы и выполнять в них несложные вычисления. Однако в вычислительном
отношении MS Word не может конкурировать с MS Excel. Интегрированный пакет Microsoft Office позволяет соединить все лучшее из обеих пр ограмм, используя механизм импорта данных.
Данные MS Excel можно передать в MS Word тремя способами:
 как текст;
 как таблицу, редактируемую в MS Excel;
 как таблицу Word.
9.1. Импортирование данных как текста.
90
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Откройте рабочий лист MS Excel и выделите импортируемые данные.
Скопируйте выделенные данные в буфер обмена командой Правка  Копировать, либо воспользовавшись соответствующей кнопкой на панели инструментов Стандартная. Переключитесь в MS Word и после установки
курсора в нужное место подайте команду Правка  Специальная вставка 
Вставить. При этом необходимо вставить объект как «Неформатированный
текст».
Текст может редактироваться средствами MS Word.
9.2. Импортирование данных с возможностью редактирования в
MS Excel.
Откройте формат MS Word и книгу MS Excel. Перейдите в окно MS
Excel, выделите импортируемые данные (весь лист, диапазон ячеек или диаграмму) и подайте команду Правка  Копировать. После переключения в
MS Word и установки курсора в нужное место выберите команду Правка 
Специальная вставка  Вставить. Далее необходимо установить следующие параметры:
 в списке Как выберите Лист Microsoft Excel (объект) или Диаграмма
Microsoft Excel (объект);
 для предупреждения отображения внедренного объекта MS Excel
(листа или диаграммы) в виде графического объекта, который можно
позиционировать перед текстом или за ним, снимите флажок Поверх
текста.
.
9.3. Импортирование данных как таблицы MS Word.
Наиболее общий способ импорта файлов MS Excel заключаются в следующем:
 Щелкните на месте вставки данных MS Excel в документе MS Word;
выберите команду Вставка → Файл и из списка Тип файлов выберите Все
файлы; перейдите в папку, содержащую нужный файл, и дважды щелкните
на значке файла. MS Word выведет на экран окно диалога, в котором либо
нужно выбрать опцию Вся книга, либо выбрать рабочий лист из списка. В
случае выбора рабочего листа можно указать наименова ние диапазона на
этом листе. После нажатия на кнопку
начинается процесс преобразования, в результате которого в документе в позиции курсора появится н овая таблица.
 Выделите импортируемые данные рабочего листа MS Excel и скопируйте в буфер обмена командой Правка → Копировать. После переключения
в MS Word и установки курсора в нужное место подайте команду Правка →
Специальная вставка → Вставить. При этом необходимо вставить объект
как текст в формате RTF .
91
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
 Выделите импортируемые данные рабочего листа MS Excel и скопируйте в буфер обмена командой Правка → Копировать. После переключения
в MS Word и установки курсора в нужное место подайте команду Правка →
Вставить.
Заметим, что при этом способе импорта данных диаграммы не вста вляются в документ MS Word.
9.4. Создание электронной таблицы в среде MS Word.
В MS Word имеется возможность помещения электронной таблицы в
документ. Для этого после установки курсора в нужном месте документа MS
Word щелкните кнопкой Добавить таблицу Microsoft Excel на панели инструментов Стандартная. В появившемся окне выделите необходимое число
строк и столбцов для формирования таблицы. После этого появляются рамки
электронной таблицы, а на панели инструментов – кнопки MS Excel и можно
вводить данные, формулы в таблицу. Возврат к работе над документом MS
Word достигается двойным щелчком мыши вне области электронной таблицы.
Контрольные задания по разделу 9.
Задание 9.1. Создайте комплексный (составной) документ. На первом этапе
подготовьте служебное письмо директору фирмы о деятельности магазина
за отчетный период (месяц, квартал, год) в MS Word. На втором этапе используя возможности MS Excel, постройте таблицу выручки от реализации
продукции за отчетный период и диаграмму к этой таблице. Полученные
таблицу и диаграмму вставьте в документ MS Word.
Задание 9.2. Данные электронных таблиц MS Excel, представленные на рис.
14, 16 и 30, импортируйте в документы MS Word описанными выше тремя
способами.
Тесты по разделу 9.
1. Данные MS Excel можно передать в MS Word как:
а) текст;
б) электронную таблицу;
в) таблицу MS Word;
г) графический объект.
2. Данные MS Excel можно передать в MS Word, используя механизм:
а) импорта данных;
б) экспорта данных;
в) Plug и Play;
г) активного перемещения данных.
3. В MS Word можно импортировать следующие объекты р абочей книги MS
Excel:
а) рабочий лист;
б) диапазон ячеек;
в) диаграмму;
г) список.
4. Наиболее широкие возможности при передаче данных из MS Excel в MS
Word достигаются при использовании следующего режима вставки:
а) специальная вставка;
б) импорт данных;
92
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
в) расширенная вставка;
г) сложная вставка.
5. Скопированный в буфер обмена диаграмма MS Excel не вставляется документ MS Word при подаче команды:
а) Правка  Вставить;
б) Правка  Специальная вставка;
в) Правка  Расширенная вставка; г) Правка  Простая вставка.
6. В MS Word имеется возможность добавления электронной таблицы:
а) да;
б) нет;
в) да, если предварительно установить специальную надстройку;
г) да, если предварительно ее создать в буфере обмена.
7. При импорте данных MS Excel в MS Word как текста необходимо вставить
объект в режиме:
а) специальная ставка как неформатированный текст;
б) простая вставка как неформатированный текст;
в) расширенная вставка форматированный текст;
г) специальная вставка как форматированный макет .
8. Импортированные как текст данные MS Excel в среде можно:
а) редактировать средствами MS Word;
б) редактировать средствами как MS Word, так и MS Excel;
в) редактировать лишь после активизации MS Excel;
г) лишь просматривать.
9. Целесообразность импорта электронных таблиц в MS Word связана с его:
а) слабыми вычислительными возможностями;
б) расширенными возможностями по форматированию таблиц;
в) расширенными возможностями по работе с базами данных;
г) в перечисленном нет правильного ответа.
10. Вычисления в таблицах MS Word не поддерживают:
а) использования встроенных функций MS Excel;
б) автоматического перерасчета результатов при изменении исходных да нных;
в) формирования формул массива;
г) формирования списков в результате вычислений.
10. ДОПОЛНИТЕЛЬ НЫЕ ЗАМЕЧАНИЯ
Работа специалиста в той или иной конкретной области, как правило,
не ограничивается стандартным набором используемых команд и возможн остей табличного процессора, а требует освоения специфических технологических приемов.
В практике вузовского обучения освоение этих дополнительных во зможностей должно стать предметом изучения в рамках самостоятельной р аботы. Особенно это важно для студентов заочной формы обучения, для которой характерно значительную часть часов рабочего учебного плана отводить
под самостоятельные занятия.
93
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рамки данного учебного пособия, очевидно, не могут охватить все и нтересные и актуальные аспекты использования такого мощного и популярн ого программного продукта как табличный процессор MS Excel. Подчеркнем
еще раз главное преимущество электронных таблиц и наметим лишь некоторые направления дальнейшего самостоятельного изучения табличного пр оцессора MS Excel.
 Особенность электронных таблиц заключается в возможно сти применения формул для описания связи между значениями различных ячеек. Ра счет по заданным формулам выполняется автоматически. Изменение с одержимого какой либо ячейки приводит к мгновенному пересчету знач ений всех ячеек, которые с ней связаны формульным и отношениями и, тем
самым, к обновлению всей таблицы в соответствии с изменившимися
данными. Применение электронных таблиц упрощает работу с данными и
позволяет получать результаты без проведения расчетов вручную или
специального программирования.
 В связи со сказанным большое внимание должно быть уделено работе с
формулами и, в частности, корректному и осторожному использованию
такой процедуры как копирование. Операции копирования или перем ещения можно производить как над ячейкой в целом, так и над ее знач ением, формулой или форматом по отдельности . С содержимым копируемых
ячеек при вставке можно выполнять математические и иные операции.
Чтобы избежать копирования данных, находящихся в скрытых ячейках, в
MS Excel предусмотрена возможность копирования только видимых ячеек. Если область вставки содержит скрытые строки или столбцы, данные
копируемой области будут помещены в последовательные строки или
столбцы, даже если они не видны. В этом случае, для просмотра всех скопированных ячеек, необходимо сделать видимыми скрытые строки или
столбцы области вставки.
 Так как таблицы часто содержат повторяющиеся или однотипные данные,
программа MS Excel содержит средства автоматизации ввода. К числу
предоставляемых средств относятся: автозавершение (автозамена), автозаполнение числами и автозаполнение формулами [3,11].
Автозавершение применяют при вводе в ячейки одного столбца р абочего листа текстовых строк, среди которых есть повторяющиеся.
Автозаполнение числами имеет целью быстрое заполнение нескольких ячеек одинаковым начальным значением или несколькими разными
значениями из заранее созданных (существующих) рядов данных [2,11].
Если ячейка содержит число (в том числе дату, денежную сумму), то при
перетаскивании маркера мышью происходит копирование или запо лнение
диапазона ячеек арифметической (геометрической) прогрессией пользовательского ряда.
Особенность автозаполнения формулами заключается в необходимости
копирования ссылок на другие ячейки. В ходе автозаполнения во вним ание принимается характер ссылок в формуле: относительные ссылки из94
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»






меняются в соответствии с относительным расположением копии и ор игинала, абсолютные остаются без изменения.
Абсолютные ссылки позволяют организовать механизм связывания данных электронных таблиц. MS Excel предоставляет возможность объединять и связывать рабочие листы или файлы электронных таблиц таким
образом, чтобы в результате ввода значения в одну таблицу изменялось
содержимое другой таблицы, а выполнение вычислений в одном файле
приводило бы, например, к выдаче сообщения в другом [1].
К основным средствам обработки данных относятся – сортировка, фильтрация и формирование итогов. Рассмотренный выше алгоритм сортировки обеспечивает упорядочение данных как по столбцам, так и по стр окам. Другой важной, если не основной, задачей баз данных является поиск записей, удовлетворяющих назначенному критерию. Однако, поиск
производится только по полям, то есть по столбцам электро нной таблицы
[7].
Если же необходимо выполнить поиск по записям, то есть по строкам
электронной таблицы, то предварительно электронную таблицу надо
транспонировать, в результате чего строки таблицы станут столбцами, а
столбцы – строками. И такая транспонированная таблица будет готова для
выполнения требуемого поиска [7,14].
Наряду с рассмотренной выше фильтрацией с помо щью команды Автофильтр, используется команда Расширенный фильтр. Она позволяет реализовать более сложные запросы для фильтрации списка или базы данных
по сложному критерию [1, 11].
Наряду с консолидацией данных широко используется при создании итоговых отчетов в среде MS Excel структурирование данных рабочих листов [1, 11, 14]. Оно позволяет показывать или скрывать уровни структурированных данных, выводя на экран данные с большими или меньшими
подробностями.
Большую гибкость при работе со сложными и объемными электронными
таблицами предоставляет использование элементов управления из панели
инструментов Формы таких, как Поле со списком, Счетчик, Переключатель, Флажок [8].
Описанные в пособии технологические операции универсальны и могут
профессионально использоваться в решении сложнейших задач из различных областей деятельности. Вместе с тем табличный процессор MS
Excel позволяет эффективно организовать работу в таких узких и специфических областях, как решение аудиторских и аналитических задач. Ре ализованная в MS Excel методика анализа финансовых функций позволяет
легко и быстро выполнять простую и рутинную работу повседневных задач бухгалтера и аудитора [6,12,13].
95
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ЗАКЛЮЧЕНИЕ
Приведенный в данном пособии анализ возможностей, основных те хнологических операций и процессов в среде табличного процессора MS Excel
показывает, что процессор обеспечивает:
 Ввод, хранение и корректировку больших массивов данных.
 С помощью формул можно задавать зависимость одних значений
от других. В результате осуществляется автом атическое перевычисление всех итоговых цифр при изменении исходных данных.
 Сформированная электронная таблица сразу же становится документом, который может многократно использоваться, легко модифицироваться и выводиться на печать. На основе одной таблицы
можно быстро создать аналогичную таблицу с другими исходными
данными и возможно с измененными формулами.
 Построение в рамках деловой графики разнообразных диаграмм на
основе данных уже созданных таблиц.
 Различные формы представления одних и тех же данных в разных
таблицах в результате, например, консолидации данных, сортиро вки, фильтрации, создании сводных таблиц и др.
 Постановку, решение и анализ всех классов задач оптимизации:
линейного, целочисленного, нелинейного и стохастического пр ограммирования в области менеджмента, консалтинга и при оптимальном проектировании технических систем.
 Решение задач корреляционного и регрессионного анализа, построение экстраполяционных моделей прогнозирования эконом ических процессов.
 Автоматизацию рутинных и часто повторяющихся операций с помощью макросов.
 Разработку приложений средствами встроенного языка програ ммирования Visual Basic for Applications (VBA).
 Импорт данных MS Excel в документы текстового процессора MS
Word.
Кроме описанных выше, MS Excel представляет другие весьма широкие возможности по созданию и оформлению табличных документов. Овладение этими возможностями должно стать целью дальнейшего изучения та бличного процессора MS Excel.
Ответы на тесты по разделам
Раздел 1
1. а), б), в), г). 2. б). 3. а), б). 4. а), б), в). 5. г). 6. а), б), в), г).
7. б). 8. в). 9. а), б), в), г). 10. а), в).
96
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Раздел 2
Раздел 3
Раздел 4
Раздел 5
Раздел 6
Раздел 7
Раздел 8
Раздел 9
1. а), в). 2. в). 3. а), б). 4. г). 5. а), б), в), г). 6. в). 7. а), б), в),
г). 8. а). 9. а). 10. а).
1. а), б), в), г). 2. а), б), в). 3. а), б), в), г). 4. б). 5. а). 6. б). 7.
в). 8. б). 9. а). 10. в).
1. б). 2. а). 3. а). 4. б). 5. а), б). 6. в). 7. а), б), в). 8. а), б). 9.
в). 10. а).
1. в). 2. а), б), в), г). 3. б). 4. в). 5. в). 6. в). 7. б), в). 8. а), б).
9. а), б). 10. б).
1. в), г). 2. а), б), в), г). 3. в). 4. а), б), г). 5. б). 6. б). 7. б). 8.
а). 9. а). 10. а), г).
1. а), б), в). 2. в). 3. а), б), в). 4. а). 5. в). 6. а). 7. а). 8. б). 9.
а), б). 10. а), б).
1. в). 2. а), г). 3. г). 4. б). 5. а). 6. б). 7. а). 8. а), б), в), г). 9.
б). 10. б).
1. а), б), в). 2. а). 3. а), б), в), г). 4. а). 5. а). 6. а). 7. а). 8. а).
9. а). 10. а), б).
Глоссарий
Блок ячеек. Группа последовательных ячеек. Блок ячеек может состоять из
одной ячейки, строки (или ее части), столбца (или его части), а
также последовательности строк или столбцов (или их частей).
Буфер. Запоминающее устройство для временного хранения данных и согласования скоростей взаимодействия устройств с разными возможностями.
Данные. Материальные объекты произвольной формы, выступающие в кач естве средства представления информации.
Диаграмма. Представляет собой графическое изображение связей м ежду
числами электронной таблицы. Она позволяет показать колич ественное соотношение между сопоставляемыми величинами.
Диалог. Способ взаимодействия между объектами, включая процессы и
пользователя, со скоростью, достаточной для поддержания ко мфортной рабочей обстановки.
Диапазон ячеек рабочего листа. Область, включающая смежные ячейки
рабочего листа, которая имеет адрес или имя.
Идентификация. Процесс отождествления какого-либо объекта с одним из
известных.
Интерфейс пользователя. Порядок, определяющий процедуры взаимодействия пользователя с системой.
Компиляция. Процесс трансляции (перевода) программы с языка высокого
уровня (Бейсика) на язык низкого уровня (язык машинных команд
компьютера). Скомпилированная программа помещается в особый
файл, имя которого имеет расширение EXE.
97
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Компьютер. Общее название вычислительной машины, предназначенной
для выполнения преобразований над вводимыми и хранимыми в
ней данными.
Консолидация. Объединение данных из одной или нескольких таблиц с выполнением необходимых расчетов.
Макрокоманда. Последовательность команд, выделяемая в виде небольшой
программы.
Макрос. Последовательность команд и действий пользователя, записанных и
хранящихся внутри документа. Программа на языке программир ования VBA, оформленная как процедура SUB, выполняющая заданную последовательность операций на рабочем листе.
Массив. Упорядоченное множество однотипных элементов данных.
Мастер. Программа, нацеленная на решение определенной, узкой задачи.
Мастер ведет диалог с пользователем, в процессе которого приложение получает необходимые для решения задачи сведения.
Меню. Список команд или функций, предлагаемых пользователю на выбор.
Модель. Прототип реального объекта либо процесса, адекватно отражающего только те свойства реального процесса или объекта, которые
существенны для исследования или решения задачи. Математич еская модель - модель объекта или процесса, заданная аналитическими выражениями (математическими формулами).
Надстройка. Особое приложение, позволяющее расширить стандартные
возможности MS Excel. Надстройки созданы для того, чтобы не перегружать ненужными компонентами оперативную пам ять и функциональные свойства MS Excel.
Окно. Средство фрагментации данных при их представлении и обр аботке.
Поиск решения. Инструмент, включенный в надстройки табличного процессора MS Excel, который вычисляет заданное значение функции путем подбора значений ее аргумента.
Процедура Sub. Содержит набор команд, с помощью которого можно р ешить некоторую задачу.
Процедура Function (Функция). Содержит набор команд, который решает
некоторую задачу и при этом возвращает некоторое значение.
Рабочая книга. Электронный эквивалент папки-скоросшивателя.
Книга
состоит из рабочих листов, в которые могут входить таблицы, диаграммы или макросы, и имена листов выводятся на ярлычках в
нижней части экрана.
Рабочий лист. Объект служит для организации и анализа данных. Одновр еменно на нескольких листах данные можно вводить, править, пр оизводить с ними вычисления.
Ссылка. Способ (формат) указания адреса ячейки.
Табличный процессор. Специальный комплекс программ используется для
управления электронной таблицей.
98
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Файл. Поименованная совокупность данных в памяти ПК или на машинном
носителе.
Формат. Структура информационного объекта.
Форматирование данных. Выбор формы представления числовых или символьных данных в ячейке.
Целевая функция. Функция, которая оптимизируется путем подбора ее аргументов.
Шрифт. Набор форм символов алфавита, служащий для восприятия устро йствами компьютера и людьми.
Электронная таблица. Распространенное название комплекса пр икладных
программ для обработки таблиц. Компьютерный эквивалент обы чной таблицы, в клетках (ячейках) которой записаны данные различных типов: тексты, даты, формулы, числа.
Ячейка. Адресуемый элемент однородной структуры, например таблицы.
Область, определяемая пересечением столбца и строки электро нной таблицы.
ActiveX. Элемент (компонент) ActiveX – это отдельный законченный проект,
содержащий все необходимые данные, объекты, элементы упра вления, классы, программные модули и т.д., который компилирован
в файл (возможные расширения: .OCX, .EXE, .DLL).
Visual Basic for Applications (VBA). Язык объектно-ориентированного программирования. VBA - это сочетание одного из самых простых языков пр ограммирования (Basic) и всех вычислительных возможностей MS Excel.
99
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Список литературы
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Евдокимов В.В. и др. Экономическая информатика. Учебник для вузов. /Под ред. д. э. н., проф. Евдокимова В.В. - СПб.: Питер, 1997.
Основы современных компьютерных технологий: Учебное пособие.
/Под ред. проф. Хомоненко А.Д. Авторы: Артамонов Б.Н., Брякалов
Г.А., Гофман В.Э. и др. - СПб.: КОРОНА принт, 1998.
Симонович С.В. и др. Информатика для юристов и экономистов.
Учебник для вузов. /Под ред. Симоновича С.В. – СПб.: Питер, 2006.
Информатика: Учебник. – 3-е перераб. изд./Под ред. Н.В. Макаровой. – М.: Финансы и статистика, 2005.
Варфоломеев В.И. Программные средства офисного назначения.
Практикум. - М.: Изд-во МГУК, 2001.
Овчаренко Е.К. и др. Финансово-экономические расчеты в Excel.
Издание 3-е, дополненное – М.: Информационно-издательский дом
«Филинъ», 1999.
Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0. СПб.: BHV - Санкт-Петербург, 1997.
Компьютер для менеджера. Практическое пособие в 2-х ч. /Под ред.
Комягина В.Б. – М.: ТРИУМФ, 1998.
Дубина А.Г., Орлова С.С., Шубина И.Ю., Хромов А.В. Excel для
экономистов и менеджеров. – СПб.: Питер, 2004.
Демидова Л.А., Пылькин А.Н. Программирование в среде Visual
Basic for Applications: Практикум. – М.: Горячая линия – Телеком,
2004.
Безручко В.Т. Практикум по курсу “Информатика”. Работа в Windows 2000, Word, Excel: Учеб. пособие. – 2-е изд., доп. и перераб. –
М.: Финансы и статистика, 2005.
Ильина О.П. Информационные технологии бухгалтерского учета. СПб.: Питер, 2001.
Карлберг К. Бизнес-анализ с помощью Microsoft Excel, 3-е издание.:
Пер. с англ. – М.: Издательский дом «Вильямс», 2004.
Гончаров А. Excel 97 в примерах. - СПб.: Питер Пресс, 1997.
Орлова И.В. Экономико-математические методы и модели. Выполнение расчетов в среде Excel / Практикум: Учебное пособие для вузов. – М.: ЗАО «Финстатинформ», 2000.
Общая теория статистики: Статистическая методология в изучении
коммерческой деятельности / Под ред. А.А. Спирина, О.Э. Башиной.
– М.: Финансы и статистика, 1994.
Экономико-математические методы и прикладные модели / В.В. Федосеев и др. – М.: ЮНИТИ, 1999.
Макарова Н.В., Трофимец В.Я. Статистика в Excel. - М.: Финансы и
статистика, 2002.
100
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
101
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
СОДЕРЖАНИЕ
Введение………………………………………………………………………..….3
1. Общие сведения о табличном процессоре MS Excel ……………………....3
1.1. Назначение ……………………………….……………………………....3
1.2. Возможности ………………………………………………………..… ..4
1.3. Интерфейс пользователя……………………………………………….. 4
Тесты по разделу 1……………………………………………………………6
2. Основные приемы работы в табличном процессоре MS Excel . …………7
2.1. Изменение интерфейса пользователя…………………………………...7
2.2. Адресация и выделение ячеек……………………………………….…..8
2.3. Ввод, редактирование и копирование данных…………………...…….9
2.4. Ввод, редактирование и копирование формул…………………..…...11
2.5. Автоматизация построения функций…………………………………13
2.6. Абсолютные и относительные ссылки……………………………..…14
2.7. Присвоение имен объектам рабочих листов…………………………..15
2.8. Форматирование ячеек………………………………………………....16
2.9. Вставка и удаление строк таблицы…………………………….……...17
2.10. Получение справочной информ ации…………..…………………..…17
Контрольные задания по разделу 2…………………………………………18
Тесты по разделу 2…………………………………………………………..19
3. Создание электронных таблиц……………………………………..……….20
3.1. Состав электронных таблиц……………………………….……..……20
3.2. Этапы проектирования электронных таблиц…………………………20
3.3. Технология создания электронной таблицы………………………….21
Контрольные задания по разделу 3…………………………………………24
Тесты по разделу 3…………………………………………………………..26
4. Построение диаграмм и графиков в среде
табличного процессора MS Excel ..…....…………………………………...27
4.1. Основные компоненты плоской диаграммы………………….………28
4.2. Требования к данным……………………………………………….….28
4.3. Основные этапы………………………………………………..……….29
4.4. Построение объемной гистограммы………………………………..…30
4.5. Построение круговой диаграммы………………………………..……32
Контрольные задания по разделу 4…………………………………………33
Тесты по разделу 4…………………………………………………………..35
5. Операции над табличными данными………………………………………36
5.1. Консолидация данных в электронных таблицах………………….….36
5.2. Сортировка данных…………………………………………………….37
5.3. Фильтрация данных…………………………………………………….39
5.4. Анализ и обобщение данных с помощью сводных таблиц …………40
Контрольные задания по разделу 5.……………………………………..…43
Тесты по разделу 5…………………………………………………………..45
6. Решение задач линейного программирования с помощью MS Excel…....46
102
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6.1. Задача распределения ресурсов……………….………………….…...46
6.2. Экономико-математическая модель задачи
и технология ее решения…………………….…………………….…..47
Контрольные задания по разделу 6.………………………………………...53
Тесты по разделу 6…………………………………………………………..56
7. Решение задач статистического анализа и прогнозирования
данных в среде MS Excel……………….....………………………………..57
7.1. Описательные статистики…………………….………………….……58
7.2. Корреляционный и регрессионный анализ…………….………….….63
7.3. Анализ и прогнозирование временных рядов………………………..72
Контрольные задания по разделу 7.………………………………………..77
Тесты по разделу 7…………………………………………………………..78
8. Язык программирования VBA и макросы…………………………………79
8.1. Создание макросов………………………………………………….….79
8.2. Запуск макроса…………………………………………………….……80
8.3. Использование встроенного языка программирования VBA ………80
Контрольные задания по разделу 8…………………………………………85
Тесты по разделу 8…………………………………………………………..87
9. Импорт данных MS Excel в MS Word……………………………………...88
9.1. Импортирование данных как текста…………………………………...89
9.2. Импортирование данных с возможностью
редактирования в MS Excel......................................................................89
9.3. Импортирование данных как таблицы MS Word……………………...89
9.4. Создание электронной таблицы в среде MS Word……………………90
Контрольные задания по разделу 9…………………………………………90
Тесты по разделу 9…………………………………………………………..90
10. Дополнительные замечания ………………………………………………..91
Заключение………………………………………………………………………94
Ответы на тесты по разделам …………………………………………………..95
Глоссарий ………………………………………………………………………..95
Список литературы……………………………………………………..….……98
103
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Марков Юрий Николаевич
Исмагилов Ильяс Идрисович
Табличный процессор MS Excel:
Основы работы и применения в экономике
Учебное пособие
104
Документ
Категория
Без категории
Просмотров
163
Размер файла
1 579 Кб
Теги
процессов, 1587, excel, применению, основы, табличные, экономика, работа
1/--страниц
Пожаловаться на содержимое документа