Lektsia_PPO (1)


Лекция №1.
Введение. Основы работы в Excel. Типы ссылок и копировние данных. Относительные ссылки. Абсолютные ссылки. Форматирование и оформление данных.
Основы работы в ExcelЕсли пакет Microsoft Office установлен, то запустите Excel: Пуск►Программы►Microsoft Excel. Окно Excel, приведенное на рисунке 1, содержит много элементов, присущих большинству программ в среде Windows.

Рисунок SEQ Рисунок \* ARABIC 1 - Вид окна рабочей книги Excel
Принципы работы с данными в электронной таблице отличаются от подготовки текстовых документов.
Строки электронной таблицы пронумерованы в порядке возрастания, а столбцы поименованы буквами латинского алфавита. Ячейки таблицы организованы в рабочие листы. На рисунке 1 выбран рабочий лист с именем Лист1. Рабочие листы можно переименовывать, добавлять и удалять. Для чего следует щелкнуть правой кнопкой по закладке листа.
Ячейки таблицы адресуются (именуются) по номеру столбца и строки (как в игре морской бой), например, А1, С7 и т.д. При ссылке на другой лист дополнительно следует указать имя листа, например, Лист2!В5 – это ссылка на ячейку В5 второго листа. При ссылке на другую рабочую книгу кроме листа указывается и имя книги, например, [Книга2]Лист1!А3 – это ссылка на ячейку А3 листа 1 книги 2.
Окно Excel (рисунок 1) содержит специфические поля и кнопки. На активном рабочем листе (в нашем случае это Лист1) одна ячейка является активной или, другими словами, выделенной. Эта ячейка обрамлена черной рамкой. Адрес этой ячейки В2 (иначе это называется ссылкой на выбранную ячейку) отражается в поле Имя. Рядом расположена Строка формул, помеченная знаком =, которая отображает содержащиеся в текущей ячейке формулы и данные. На рисунке 1 в строке формул отображается текст из активной ячейки В2.
Приведенный в строке формул знак равно является подсказкой-напоминанием пользователям ВСЕ ФОРМУЛЫ ЗАПИСЫВАЮТСЯ СО ЗНАКОМ = ВНАЧАЛЕ. Формулы могут содержать имена (адреса, ссылки) других ячеек. Результат вычисления будет показан в ячейке, а сама формула – в строке формул.
Типы ссылок и копирование данныхРазличают относительные и абсолютные ссылки ячеек. Абсолютные ссылки записываются со знаком $ :
A1- относительная ссылка
$A$1- абсолютная ссылка
$A1- смешанная ссылка: на столбец ссылка абсолютная, на строку - относительная
A$1- смешанная ссылка: на строку ссылка абсолютная, на столбец - относительная
Функциональное различие типов ссылок проявляется лишь при копировании ячеек: относительные ссылки корректируются при копировании, а абсолютные – нет.
Относительные ссылкиРассмотрим пример. В ячейках столбца С требуется найти сумму ячеек столбцов А и В. Для этого введите в ячейку С1 формулу, как показано на рисунке 2 и нажмите клавишу Enter.

Рисунок SEQ Рисунок \* ARABIC 2 – Ввод формулы с относительными ссылками
Введенная формула показывает, что необходимо относительно текущей ячейки (ячейки С1) сложить значения, содержащиеся в двух соседних слева ячейках (ячейки А1 и В1).
Для того, чтобы в ячейке С2 получить сумму ячеек А2 и В2 достаточно просто скопировать формулу из ячейки С1 в ячейку С2. Копирование можно выполнить двумя способами: через буфер обмена или при помощи маркера заполнения (в этом случае следует навести указатель мыши на правый нижний угол ячейки С1, так чтобы указатель мыши изменился на +). Поскольку в ячейке С1 формула с относительными адресами, то в ячейку С2 скопируется формула =А2+В2 (рисунок 3)

Рисунок SEQ Рисунок \* ARABIC 3 – Копирование относительной ссылки по столбцу вниз
На рисунке 3 показано, что относительные адреса при копировании корректируются. Для закрепления материала скопируйте формулу из ячейки С1 в ячейку D1 (рисунок 4).

Рисунок SEQ Рисунок \* ARABIC 4 – Копирование относительной ссылки по строке влево
Как видно из рисунка 4 в ячейке D1 при копировании получена формула =В1+С1, т.е. сумма двух соседних слева ячеек относительно активной ячейки D1.
Абсолютные ссылки
Теперь введите в ячейку С1 формулу с абсолютными адресами. Для автоматизации ввода знаков $ воспользуйтесь клавишей F4. Затем скопируйте формулу из ячейки С1 в ячейку С2 и далее в любую другую ячейку рабочего листа (рисунок 5)

Рисунок SEQ Рисунок \* ARABIC 5 – Абсолютная ссылка
Как видно из рисунка 5 при копировании во всех ячейках будет содержаться одна и та же формула, что и в ячейке С1, т.е адреса не изменятся – они будут абсолютно закреплены за одними и теми же ячейками.
Смешанные ссылкиВернемся к расчетам, приведенным на рисунке 4. Для того, чтобы при копировании формулы, введенной в ячейку С1, вправо по строке в последующих ячейках появлялась сумма первых двух столбцов, необходимо в формуле использовать смешанные ссылки. Для этого следует ответить на вопрос: «Что не будет меняться при копировании: строка или столбец?» В нашем примере – столбец. Перед именем столбца поставьте знак $. Теперь формула примет вид =$A1+$B1. Затем скопируйте формулу из ячейки С1 в ячейку С2 а также в любую другую ячейку этой же строки (рисунок 6).

Рисунок SEQ Рисунок \* ARABIC 6 – Смешанная ссылка
Следует отметить, что рассмотренный пример (рисунок 6) носит учебный характер, демонстрируя поведение смешанных ссылок при копировании.
Форматирование и оформление данныхФорматирование ячейки – это установка правил ввода и отображения данных. Форматировать ячейку, выделенную группу ячеек, группу строк, столбцов или целый лист можно через меню Формат и командой Формат ячеек… выпадающего контекстного меню. В любом случае откроется окно Формат ячеек (рисунок 7), в котором можно настроить варианты отображения содержимого ячейки.

Рисунок SEQ Рисунок \* ARABIC 7 – Формат типов данных ячеек
В Excel существует понятие типа данных ячейки. Формат данных распознается автоматически при вводе данных. Например, если вы введете дату 15.02.2006, то по умолчанию установится формат Дата, если проценты 34%, то установится Процентный формат и т.д. Если формат не удается установить, то устанавливается Общий формат. Для большинства числовых форматов можно выбрать тип и число десятичных знаков дробной части. Здесь следует отметить, что дробные числа в вводятся с десятичной запятой, а не с точкой. Например, при вводе числа 1,25 вместо десятичной запятой была введена точка – 1.25. В этом случае Excel автоматически назначит ячейке формат Дата и отобразит введенную информацию как дату - Янв.25. И даже в случае исправления точки на запятую – Excel все равно будет отображать дату, но только Янв.00, что связано с внутренним представлением дат в Excel в виде чисел. Для того, чтобы избавиться от автоматически «распознанного» формата следует «вручную» назначить ячейке формат Числовой на вкладке Число диалогового окна Формат ячеек.
По умолчанию текст, введенный в ячейку, выравнивается по ее левому краю, а числа – по правому. Изменить способ выравнивания в ячейке или диапазоне можно с помощью вкладки Выравнивание окна Формат ячеек (рисунок 8). В ней располагаются часто необходимые возможности: перенос по словам, объединение ячеек и наклонную ориентацию текста.
Изменить тип и размер шрифта можно с помощью кнопок, расположенных на панели инструментов Форматирование. Кроме того, вкладка Шрифт диалогового окна Формат ячеек (рисунок 9) позволяет установить дополнительные форматы, например верхний или нижний индекс.
Рамки вокруг выделенной ячейки или диапазона ячеек создается с помощью кнопки Границы на панели инструментов Форматирование. Вкладка Граница диалогового окна Формат ячеек позволяет дополнительно задавать тип и цвет линий оформления (рисунок 10)

Рисунок SEQ Рисунок \* ARABIC 8 – Форматирование выравнивания ячеек

Рисунок SEQ Рисунок \* ARABIC 9 – Форматирование шрифтов ячейки
Вкладка Вид позволяет изменить цвет фона активной ячейки или диапазона ячеек.
На вкладке Защита можно запретить/разрешить изменение конкретных ячеек с помощью флага Защищаемая ячейка. После установки/снятия флагов защиты нужных ячеек следует выполнить защиту листа: пункт меню Сервис►Защита►Защитить лист… По умолчанию на листе все флажки Защищаемая ячейка включены, т.е. выполнение пункта меню Сервис►Защита►Защитить лист… приведет к защите всех ячеек.

Рисунок SEQ Рисунок \* ARABIC 10 – Форматирование типов границ
Пример создания и форматирования таблицыНеобходимо составить отчетную ведомость о результате работы сети торговых точек за IV квартал, в которой подсчитать:
суммарную выручку по каждой торговой точке за три месяца,
суммарную выручку за каждый месяц по всеем торговым точкам
долю реализации каждой торговой точки в общем объеме.
Исходные данные приведены в таблице 1
Таблица SEQ Таблица \* ARABIC 1
Выручка сети торговых точек
Торговая точка Октябрь Ноябрь Декабрь Суммарная выручка Доля
1 225 455 534 2 342 356 345 3 432 357 454 4 324 243 248 Итого Введите в ячейку А1 заголовок таблицы Выручка сети торговых точек.
Выделите диапазон ячеек А2:F2 и выполните команду Формат►Формат ячейки… В появившемся диалоговом окне Формат ячеек перейдите на вкладку Выравнивание и установите в разделе Выравнивание по горизонтали и по вертикали по центру, а также в разделе Отображение отметьте флажком переносить по словам (рисунок 11).
Последовательно в ячейки А2, В2 … F2 введите заголовки каждого столбца. Обратите внимание, что содержимое ячеек автоматически выравнивается по центру, а для длинных заголовков – разбиваются на несколько строк.

Рисунок SEQ Рисунок \* ARABIC 11 – Параметры выравнивания
Заполните исходными данными диапазон ячеек А3:D6. Затем выделите диапазон и отобразите содержимое ячеек по центру. Для этого достаточно просто нажать кнопку (По центру) на панели инструментов Форматирование.
В ячейку Е3 введите формулу
=СУММ(B3:D3).
Формулу можно ввести «вручную», а можно воспользоваться кнопкой (Автосуммирование), расположенной на панели инструментов Стандартная (рисунок 12).

Рисунок SEQ Рисунок \* ARABIC 12 – Ввод формулы с помощью кнопки Автосуммирование
Итак, установите табличный курсор на ячейку Е3 и нажмите кнопку . По умолчанию выделится смежный диапазон ячеек, содержащий числовую информацию. В нашем случае это диапазон А3:D3, т.е суммирование затронет и номер торговой точки. Выделите «вручную» диапазон B3:D3, как показано на рисунке 12 и нажмите клавишу Enter.
Введенную в ячейку Е3 формулу с помощью маркера заполнения протащите на диапазон Е4:Е6. Маркер заполнения располагается в правом нижнем углу активной ячейки и при «наведении» на него указателя мыши указатель изменяет свой вид с на . Итак, вы навели указатель мыши на правый нижний угол ячейки, в которой располагается формула. Указатель изменил свой вид: теперь он выглядит . Удерживая нажатой левую кнопку мыши протягивайте пунктирный диапазон до ячейки Е6. Отпустите левую кнопку мыши, и диапазон сразу заполнится «скопированными» формулами.
По аналогии введите в ячейку В7 формулу
=СУММ(B3:B6), которую протащите на диапазон С7:Е7.
В ячейку F3 введите формулу (рисунок 13)
=E3/$E$7, которую протащите на диапазон F4:F6.

Рисунок SEQ Рисунок \* ARABIC 13 – Ввод формулы, содержащей абсолютную ссылку
Для того, чтобы изменить тип ссылки на ячейку E7 с относительной (E7) на абсолютную ($E$7) нажмите клавишу F4. Если бы в ячейку F3 была введена формула =E3/E7, то ее копирование на ячейки диапазона F4:F6 дало бы ошибочный результат (можете проверить).
Присвойте диапазону F3:F6 процентный формат с помощью кнопки . Сумма диапазона ячеек F3:F6 должна быть равной 100%. Введите в ячейку F7 формулу
=СУММ(F3:F6)
Для того, чтобы прорисовать границы таблицы ее следует выделить (диапазон А2: F7) и нажать на панели инструментов Форматирование кнопку (Границы), как показано на рисунке 14

Рисунок SEQ Рисунок \* ARABIC 14 – Форматирование границ таблицы
Для того, чтобы поместить заголовок таблицы по центру следует выделить диапазон ячеек А1:F1 и нажать на панели инструментов Форматирование кнопку (Объединить и поместить в центре).
Итак, мы получили расчетную таблицу, представленную на рисунке 15.

Рисунок SEQ Рисунок \* ARABIC 15 – Расчетная таблица
Для того, чтобы просмотреть расчетные формулы следует выполнить команду Сервис►Параметры. На вкладке Общие диалогового окна Параметры в разделе Параметры окна отметьте поле Формулы флажком. Окно рабочего листа примет вид, показанный на рисунке 16.

Рисунок SEQ Рисунок \* ARABIC 16 – Расчетная таблица с «включенными» формулами
Для возврата в прежний режим снимите флажок Формулы на вкладке Общие диалогового окна Параметры.

Лекция №2. Использование различных функций Excel при выполнении расчетов. Общие сведения о логических выражениях. Частичные операции. Формулы массива
Использование различных функций Excel при выполнении расчетов
Общие сведения о логических выраженияхСоздайте в рабочей книге Excel новый лист «Логика».
Введите в ячейку А1 формулу =7>5. Она вернет значение Истина. Теперь введите в ячейку А2 формулу =3>5. Эта формула вернет значение Ложь. Правые части обеих формул представляют собой высказывания, т.е. утверждение, относительно которых можно заключить, верны они или нет. Арифметические формулы, которые были рассмотрены выше, например формула =A1*B1, высказываниями не являются. Арифметические формулы предписывают, как по исходным данным вычислить значение и вопрос об их истинности или ложности не имеет смысла.
Рассмотрим другой пример. Введите в ячейку А4 число 2, а в ячейку B4 формулу =А4>3. Формула возвращает значение ЛОЖЬ. Теперь исправьте содержимое ячейки А4 – введите число 6. Формула возвращает значение ИСТИНА. В ячейке В4 записан предикат, т.е. высказывание с переменными (в данном случае переменная одна). В зависимости от значения переменных предикат может принимать значения Истина или ЛОЖЬ. В этом примере формула как бы дает ответ на вопрос: «Число (или результат вычислений по формуле), хранящийся в ячейке А4, превышает число 3?» В зависимости от значения А4 ответ будет либо ДА (ИСТИНА) либо НЕТ (ЛОЖЬ).
Сравнение двух арифметических выражений, содержащих переменные, дает предикат. В формуле =А4>3 ее составные части (А4 и 3) можно считать арифметическими выражениями, только очень простыми. Более сложный пример: =3*(A4^2-1)>(2*A4+1)/5.
Таблица SEQ Таблица \* ARABIC 2
Операции сравнения
> >= < <= = <>
больше больше или равно меньше меньше или равно равно не равно
Обратите внимание, что символ отношения «больше или равно» изображается двумя знаками. Причина в том, что на клавиатуре отсутствует знак ≥.
Высказывание и предикат имеют общее название – логическое выражение. Имеются логические операции, которые позволяют строить сложные логические выражения. Эти операции реализованы в Excel как функции, перечень которых приведен в таблице 3 в порядке убывания приоритета.
Таблица SEQ Таблица \* ARABIC 3
Логические операции
Название Обозначение Функция Excel
Отрицание ⌐ НЕ
Конъюнкция & И
Дизъюнкция V ИЛИ
На самом деле в Excel приоритет логических операций не имеет значения, так как они реализованы виде функций.
У логических функций аргументы могут принимать только два значения: ИСТИНА или ЛОЖЬ. Поэтому логические функции можно задавать таблицей, где перечислены все возможные значения аргументов и соответствующие им значения функций. Такие таблицы называются таблицами истинности (таблица 4 и таблица 5).
Функция НЕ может иметь только один аргумент, а функции И и ИЛИ два и более аргументов.
Таблица SEQ Таблица \* ARABIC 4
Таблица истинности для функции НЕ
х НЕ(х)
ЛОЖЬ ИСТИНА
ИСТИНА ЛОЖЬ
Таблица SEQ Таблица \* ARABIC 5
Таблица истинности для функций И и ИЛИ
х у И(х,у) ИЛИ(х,у)
ЛОЖЬ ЛОЖЬ ЛОЖЬ ЛОЖЬ
ЛОЖЬ ИСТИНА ЛОЖЬ ИСТИНА
ИСТИНА ЛОЖЬ ЛОЖЬ ИСТИНА
ИСТИНА ИСТИНА ИСТИНА ИСТИНА
На практике в числом виде логические выражения как правило не используются. Логическое выражение служит первым аргументом функции ЕСЛИ:
ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь).
В языках программирования высокого уровня этой функции соответствует оператор
если лог_выражение то действие1 иначе действие2
IF лог_выражение THEN действие1 ELSE действие2
Лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае – ЛОЖЬ.
Например, сведения о предполагаемых и фактических расходах сведены в таблице в интервале A1:C4.
Предполагаемые расходы Фактические расходы Бюджет
900 1500 900 500 925 500 Проверьте сведения на превышение бюджета. Для этого введите в ячейку С2 следующую формулу, которую затем скопируйте в ячейки С3 и С4 =ЕСЛИ(A2<B2;"Превышение бюджета";"Бюджет не превышен")
Частичные операцииВ Excel есть термин – частичная сумма, которым обозначается операция суммирования чисел из заданного диапазона, но «не всех подряд», а только тех, которые удовлетворяют определенному условию. Например, функции СУММЕСЛИ и СЧЕТЕСЛИ.
Рассмотрим частичную операцию суммирования на примере подсчета суммы комиссионных для стоимости имущества более 250 000 руб.

Рисунок SEQ Рисунок \* ARABIC 17 – Частичная операция суммирования
В ячейку В6 введена функция СУММЕСЛИ, аргументы которой показаны в диалоговом окне на рисунке

Рисунок SEQ Рисунок \* ARABIC 18 – Диалоговое окно СУММЕСЛИ
В поле Диапазон задан диапазон А2:А5 в котором содержатся данные о стоимости имущества. Функция СУММЕСЛИ сравнивает каждое значение из диапазона вычисляемых ячеек А2:А6 с критерием отбора >250000, заданным в поле Критерий. Ячейки в поле Диапазон_суммирования суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию. Если Диапазон_суммирования опущен, то суммируются ячейки в аргументе «диапазон».
В нашем случае в поле Диапазон_суммирования присутствуют фактические ячейки для суммирования – значения комиссионных. Функция СУММЕСЛИ просуммировала только те значения комиссионных (21 000р. + 28 000р.), для которых стоимость имущества превышает 250 000р.
Microsoft Excel предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа появлений текстовой строки или числа в пределах диапазона ячеек, можно воспользоваться функцией СЧЁТЕСЛИ.
Для получения формулы, возвращающей в зависимости от выполнения условия одно из двух значений, например вознаграждение по указанному объему продаж, можно воспользоваться функцией ЕСЛИ. Расширим понятие частичная сумма и введем термин частичная операция, которым будем обозначать любую из следующих операций – суммирование, подсчет количества чисел, поиск минимального или максимального числа, которые выполняются над числами, принадлежащими заданному диапазону и удовлетворяющие заданному пользователем критерию.
Будем считать, что компонента из диапазона будет включена в частичную операцию, если для нее выполняются заданные условия. Необходимо подчеркнуть, что условие включения в частичную операцию проверяется для каждой компоненты диапазона.
Формулы массиваДля поиска частичного максимума ли минимума, а также частичные подсчет и суммирование сложных условий могут быть выполнены с помощью формул массива. Такие формулы состоят из внешней функции СУММ, МАКС или МИН (собственно выполняющих их необходимую частичную операцию) и одной или нескольких вложенных функций ЕСЛИ, создающих виртуальный массив, над которыми эти операции выполняются.
Формула массива является мощным вычислительным элементом Excel. В отличие от обычных (скалярных) формул, вычисляющих значение только для одной ячейки, формулы массива вычисляют значения как для одной ячейки, так и для нескольких ячеек одновременно. Формулы массива заключены в фигурные скобки {}. Завершение ввода формулы массива осуществляется не простым нажатием клавиши Enter, а нажатием комбинации клавиш Ctrl+Shift+Enter, после чего формула автоматически «обрамляется» фигурными скобками.
411480012636500Допустим, требуется вычислить сумму абсолютных значений чисел, содержащихся в диапазоне А1:А5. Если не использовать формулу массива, то единственный способ решения этой задачи состоит в том, чтобы в соседнем диапазоне, например, В1:В5, с помощью обычной функции ABS создать абсолютные значения и потом просуммировать их, например, в ячейке С1 с помощью функции СУММ.
Для того, чтобы ускорить процесс вычислений с помощью единственной формулы воспользуемся формулой массива {=СУММ(ABS(A1:A5))}.
Специально подчеркнем, что хотя функция ABS в этой формуле массива и возвращает пять значений, но ни в какие ячейки они не записываются, а просто используются в качестве слагаемых функцией СУММ. В этом случае можно говорить, что в формуле массива функция ABS возвращает виртуальный массив значений.
С учетом вышеизложенного, формулы массива, реализующие рассматриваемые задачи частичных операций могут быть записаны в следующем виде:
подсчет {=СУММ(ЕСЛИ(Усл; 1; 0))} суммирует единицы
суммирование {=СУММ(ЕСЛИ(Усл; А; 0))} суммирует отобранные
элементы
максимальный {=МАКС(ЕСЛИ(Усл; А))} находит наибольшее среди отобранных
минимальный {=МИН(ЕСЛИ(Усл; А))} находит наименьшее среди отобранных
428117016446500Рассмотрим использование формул массива на примере поиска минимальной выручки торговой точки, превышающую заданную величину, например 1000 р.
Исходные данные для решения задачи содержатся в диапазоне A1:B10 рабочего листа Excel. Размер выручки записан в ячейках диапазона В2:В10. Если для нахождения величины минимальной выручки ввести в ячейку С2 функцию =МИН(B2:B10) – вы найдете самую минимальную выручку 850 р., которая не удовлетворяет заданному условию – меньше 1000р. Для того, чтобы «отсечь» лишние значения и сформировать виртуальный диапазон, в котором отсутствуют компоненты, неудовлетворяющие заданному условию включим в функцию МИН функцию ЕСЛИ, задающую условия отбора.
{=МИН(ЕСЛИ(В2:В10>1000; В2:В10))}
24003006604000Функция ЕСЛИ в части условия будет поэлементно сравнивать значения в диапазоне В2:В10 c заданным критерием отбора >1000 и формировать виртуальный массив из которого будут исключены элементы неудовлетворяющие заданному критерию, т.е элементы 900р. и 850р..
Значение критерия отбора можно задавать в отдельной ячейке для того, чтобы меняя его, получать новое требуемое значение.

Лекция №3. Моделирование функций И и ИЛИ в формулах массива. Извлечение данных из массива значений. Задание критериев для извлечения данных из массива
Моделирование функций И и ИЛИ в формулах массиваДопустим, что компонента диапазона должна быть включена в частичную операцию при одновременном выполнении двух условий.
Например, нам необходимо подсчитать общую сумму и количество дней, когда объем выручки находился в диапазоне от 1 000р. до 2 000р.
В данном случае мы имеем дело с реализацией функции И, используя арифметические свойства логических переменных, т.е. того факта, что в арифметических вычислениях ИСТИНА рассматривается как 1, а ложь как 0. Как мы помним функция И – это функция логического умножения, потому формула массива будет выглядеть следующим образом.
Для подсчета количества продаж, лежащих в диапазоне от 1000 р. до 2000 р.
{=СУММ((B2:B10>=1000)*(B2:B10<=2000))}.
Для подсчета суммарной выручки, лежащей в диапазоне от 1000 р. до 2000 р.
{=СУММ(((B2:B10>=1000)*(B2:B10<=2000))* B2:B10)}.
Для подсчета числа продаж меньших 1000 р. или больших 5000 р. мы реализуем функцию ИЛИ – логическое сложение.
Для подсчета количества продаж, меньших 1000 р. или больших 5000 р
{=СУММ((B2:B10<=1000)+(B2:B10>=5000))}.
Для подсчета суммарной выручки, меньших 1000 р. или больших 5000р
{=СУММ(((B2:B10<=1000)+(B2:B10>=5000))*B2:B10)}.}.
Извлечение данных из массива значенийВ диапазоне A1:D23 рабочего листа Excel, показанном на рисунке 19, приведены сведения о дистрибьюторах нашей фирмы, регионах (в которых они расположены) и расстоянии до складов, на которые наша фирма доставляет товары.
Решим задачу по определению расстояния до склада компании, название которой задается в отдельной ячейки, например, до склада компании ЗАО Титан.
Нам необходимо решить задачу поиска значения элемента в заданном массиве, пользуясь функциями из категории Ссылки и массивы. Прежде чем привести окончательный вариант решения задачи рассмотрим возможности работы функции данной категории. Начнем с функции ИНДЕКС, которая имеет две формы. Мы изучим только одну:
ИНДЕКС(массив, номер_строки, номер_столбца)
Эта функция выбирает из прямоугольного блока (массива) элемент, задаваемый номерами строки и столбца, причем эти номера отсчитываются от левого верхнего элемента блока.

Рисунок SEQ Рисунок \* ARABIC 19 – Исходный массив значений
Введите в ячейку Е1 название компании, в нашем примере ЗАО Титан. Затем установите курсор в ячейку Е2, нажмите кнопку Вставка функции и в появившемся окне Мастер функций в поле со списком Категории выберите Ссылки и массивы. В поле Выберите функцию выделите функцию ИДЕКС и нажмите ОК.
На экране появится диалоговое окно Мастер функций - шаг 1а из 2, в котором необходимо выбрать первый тип функции.

Рисунок SEQ Рисунок \* ARABIC 20– Выбор списка аргументов функции ИНДЕКС
Выделите первую строку в диалоговом окне, показанном на рисунке и нажмите кнопку ОК. На экране появится диалоговое окно для ввода аргументов функции ИНДЕКС, как показано на рисунке 21.

Рисунок SEQ Рисунок \* ARABIC 21 – Аргументы функции ИНДЕКС
Массив в нашем примере будет диапазон ячеек A2:D23.
Мы можем задавать номер строки явно – 20, поскольку в учебных целях был взят массив с небольшим количеством строк. Итак, введите в поле Номер_строки число 20.
Километраж, показывающий расстояние до складов фирм, содержится в четвертом столбце. Поэтому в поле Номер_столбца вводим 4.
Задавая в качестве аргументов функции ИНДЕКС явно номер_строки, в которой расположена информация о компании ЗАО Титан (20 строка) и номер_столбца (4 столбец) мы получаем искомое расстояние 160 км.
В данном примере мы могли бы найти расстояние не прибегая к возможностям функций Excel, но в практической деятельности экономиста-менеджера зачастую приходится сталкиваться со значительными объемами данных, которые трудно «обозреть одним взглядом» и найти нужную информацию – в этом случае приходится затрачивать значительное время.
Для облегчения поиска позиции элемента в списке существует функция ПОИСКПОЗ. При помощи этой функции мы найдем позицию в массиве данных искомого значения – ЗАО Титан.
Введите в ячейку Е3 функцию ПОИСКПОЗ, которая находится в категории Ссылки и массивы окна Мастер функций.
Синтаксис функции
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)
Искомое_значение – значение, используемое при поиске значения в таблице. В нашем примере Искомое_значение ЗАО Титан.
Просматриваемый_массив – непрерывный интервал ячеек, возможно, содержащих искомые значения. В нашем примере просматриваемый_массив диапазон В2:В23.
Тип_сопоставления – число -1, 0 или 1. Тип_сопоставления указывает, как Microsoft Excel сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив
Если тип_сопоставления равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое меньше либо равно, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА.
Если тип_сопоставления равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.
Если тип_сопоставления равен -1, то функция ПОИСКПОЗ находит наименьшее значение, которое больше либо равно чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1, 0, -1, -2, ..., и так далее.
Если тип_сопоставления опущен, то предполагается, что он равен 1.
В нашем примере список неупорядочен, поэтому в поле тип_сопоставления введите 0

Рисунок SEQ Рисунок \* ARABIC 22 – Аргументы функции ПОИСКПОЗ
Как видно из рисунка 22 функция ПОИСКПОЗ нашла позицию элемента ЗАО Титан в массиве данных (20 позиция).
Функцию для окончательного решения задачи введем в ячейку Е4. Так как в практической работе экономисту-менеджеру для поиска требуемой информации в больших массивах данных необходимо сначала найти позицию искомого элемента в массиве, а затем по найденной позиции и сам элемент. В окончательном решении задачи, рассмотренные выше функции будут использоваться совместно: результат работы функции ПОИСКПОЗ будет аргументом Номер_строки в функции ИНДЕКС.
Для того, чтобы вложить функцию ПОИСКПОЗ в функцию ИНДЕКС необходимо выполнить следующие действия.
Сначала в окне Мастер функций в поле со списком Категории выберите Ссылки и массивы. В поле Выберите функцию выделите функцию ИДЕКС и нажмите ОК.
На экране появится диалоговое окно Мастер функций - шаг 1а из 2, в котором необходимо выбрать первый тип функции.

Рисунок SEQ Рисунок \* ARABIC 23 – Выбор списка аргументов функции ИНДЕКС
Выделите первую строку в диалоговом окне, показанном на рисунке и нажмите кнопку ОК. На экране появится диалоговое окно для ввода аргументов функции ИНДЕКС, как показано на рисунке 24

Рисунок SEQ Рисунок \* ARABIC 24 – Выбор вложенной функции ПОИСКПОЗ
В поле Массив введите диапазон ячеек A2:D23, в котором содержатся исходные данные.
Перейдите в поле Номер_строки. Для того чтобы ввести в поле Номер_строки функцию ПОИСКПОЗ выполните щелчок на кнопке , расположенной в строке формул слева от кнопки (Отмена). Кнопка вызывает список 10 недавно использовавших функций и строку Другие функции, по которой можно вызвать окно Мастера функций. В нашем примере, мы только что воспользовались функцией ПОИСКПОЗ, поэтому она находится в списке 10 недавно использовавших.
Итак, Вы щелкнули по кнопке со стрелкой вниз и в появившемся списке выполнили щелчок по функции ПОИСКПОЗ. На экране появилось диалоговое окно ПОИСКПОЗ. Введите аргументы функции: Искомое_значение – ЗАО Титан; просматриваемый_массив – В2:В23; тип_сопоставления – 0. Не нажимайте кнопку ОК после ввода аргументов функции ПОИСКПОЗ.

Рисунок SEQ Рисунок \* ARABIC 25 – Ввод аргументов во вложенную функцию
Обратите внимание на строку формул. В ней содержится функция ИНДЕКС, в которую вложена функция ПОИСКПОЗ. Обратите внимание на то, что в функции ИНДЕКС введены не все аргументы – не указан номер столбца.
Для того, чтобы вернуться в функцию ИНДЕКС просто щелкните по ее имени в строке формул

Рисунок SEQ Рисунок \* ARABIC 26 – Окончательный вариант решения задачи
Введите аргумент Номер_столбца – 4 и только сейчас нажмите кнопку ОК.
Как вы можете видеть из рисунка ответ – это вложенные функции, которые позволяет найти расстояние до склада компании ЗАО Титан выглядит следующим образом:
=ИНДЕКС(A2:D23;ПОИСКПОЗ("ЗАО Титан";B2:B23;0);4)
Закрепим полученные знания на обратном примере. Например, нам необходимо найти название компании, расположенной на расстоянии 200 км. Функция выглядит следующим образом
=ИНДЕКС(A2:D23;ПОИСКПОЗ(200;D2:D23;0);2)
Диалоговое окно показано на рисунке 27.

Рисунок SEQ Рисунок \* ARABIC 27 – Окончательный вариант решения задачи
Задание критериев для извлечения данных из массива значенийТеперь усложним задачу. Найдем название дистрибьютора, склад которого расположен на минимальном расстоянии от нашей фирмы.
Для решения данной задачи мы должны сначала найти минимальное расстояние, затем порядковый номер найденного минимального значения в диапазоне, а по порядковому номеру – название дистрибьютора, склад которого расположен на минимальном расстоянии.
В данной задаче аргументом функции ПОИСКПОЗ Искомое_значение будет результат работы функции МИН.
Для того, чтобы усовершенствовать функцию по поиску названия компании-дистрибьютора, воспользуемся уже готовой функцией, введенной в ячейку D4. Выделите ячейку D4, содержащую данную функцию, а затем в строке формул щелкните по имени функции, в которую нужно внести изменения (в нашем примере ПОИСКПОЗ) и щелкните по кнопке (Вставка функции)

Рисунок SEQ Рисунок \* ARABIC 28 – Редактирование части формулы
Чтобы вставить в аргумент Искомое_значение вместо 200 функцию МИН вызовите ее в списке функций, как показано на рисунке 29.

Рисунок SEQ Рисунок \* ARABIC 29 – Выбор вложенной функции МИН
В качестве аргументов функции МИН задайте диапазон D2:D23.

Рисунок SEQ Рисунок \* ARABIC 30 – Аргументы функции МИН
Как видно из рисунка минимальное расстояние равно 60 км, а название компании ОАО Литисия. Нажмите кнопку ОК.
Итоговая функция имеет вид
=ИНДЕКС(A2:D23;ПОИСКПОЗ(МИН(D2:D23);D2:D23;0);2)
Еще усложним задачу. Найдем минимальное расстояние до склада компании, расположенной не в Ростовской области.
Теперь мы должны вложить в функцию МИН функцию ЕСЛИ, благодаря которой при вычислении минимального расстояния можно проигнорировать компании из заданного региона (Ростовской области).
В строке формул щелкните по имени функции, в которую нужно внести изменения (в нашем примере МИН) и щелкните по кнопке (Вставка функции)

Рисунок SEQ Рисунок \* ARABIC 31 – Выбор вложенной функции для редактирования
Чтобы вставить в аргумент Число1 функцию ЕСЛИ вызовите ее в списке функций, как показано на рисунке 32.

Рисунок SEQ Рисунок \* ARABIC 32 – Выбор вложенной функции ЕСЛИ
В качестве аргументов функции ЕСЛИ задайте следующее:
Лог_выражение – C2:C23<>"Ростовская область". Знак <> означает «не равно», кавычки вводить обязательно. Если ввести текстовый критерий отбора (в нашем примере – Ростовская область) без кавычек, Excel будет интерпретировать его как имя ячейки.
Значение_если_истина – D2:D23.
Не забывайте о необходимости ввода данной строки как формулы массива для области C2:C23. Это означает, что завершать ввод данной функции нужно при помощи одновременного нажатия трех клавиш {Ctrl}+{Shift}+{Enter}.

Рисунок SEQ Рисунок \* ARABIC 33 – Аргументы функции ЕСЛИ
Как видно из рисунка минимальное расстояние компании, расположенной за пределами Ростовской области у ЗАО ЛТД
Итоговая функция имеет вид
{=ИНДЕКС(A2:D23;ПОИСКПОЗ(МИН(ЕСЛИ(C2:C23<>"Ростовская область";D2:D23)); D2:D23;0);2)}
Диаграмма Excel – это средство графического представления данных или зависимостей между несколькими (двумя-тремя) величинами (показателями). На одной диаграмме одновременно может быть представлено несколько наборов данных или зависимостей, которые в терминологии Excel называются рядами. Каждый ряд на диаграмме состоит из нескольких геометрических фигур одинакового типа, которые называются элементами ряда. Это могут быть прямоугольники, сектора круга и других фигур, а также отдельные точки, которые, часто, но не всегда, помечают различными маркерами и соединяют линией. Элементы одного ряда обычно имеют одинаковое оформление.
Обычно диаграмма строится на основе данных, содержащихся в ячейках рабочего листа. Диаграмма может быть построена на отдельном специальном листе Excel – листе диаграмм, или непосредственно на рабочем листе, содержащем исходные данные (такая диаграмма называется встроенной).
Диаграмма всегда динамически связана с данными (ячейками), на основании которых она построена. Это значит, что любое изменение значений таких ячеек автоматически приводит к изменениям на диаграмме, а для отдельных типов верно и обратное – изменение положение элемента ряда на диаграмме ведет к изменению значений в ячейке рабочего листа.

Лекция №4. Графическое представление данных. Деловая графика
Графическое представление данныхДиаграмма всегда представляет собой элемент, обладающий рядом особых свойств. Поэтому все операции с построенными диаграммами выполняются в специальных режимах работы.
Тип диаграммы – это конкретный способ его графического представления. Типы диаграмм подразделяются на стандартные и нестандартные. Списки названий диаграмм каждой из этих групп содержатся в соответствующих вкладках. Диаграмма нестандартного типа (или смешанного) типа представляет собой либо специальный вид одного из стандартных типов, либо сочетает на одной диаграмме ряды нескольких стандартных типов. Каждый стандартный тип имеет различные модификации, называемые видами. Виды не имеют названий и представлены лишь пиктограммами с кратким описанием выделенного вида, которое располагается внизу окна Мастера диаграмм.
Пусть имеются данные о продажах по отделам, данные размещены в
таблице по месяцам.
Отдел Продано, тыс.руб.
Январь Февраль
Оптовый 7 819 7 383
Розничный 4 924 6 497
Предварительного заказа 3 004 2 640
Построим гистограмму с использованием вспомогательной оси для отображения итоговых данных. Использование второй оси Y необходимо, в частности, в следующих двух случаях:
при отображении на одной диаграмме величин разной размерности. Например, если на одной диаграмме для разных дат отображаются объемы выпущенной продукции и затраты на ее производство, то показывать эти величины на одной оси бессмысленно. В этом случае один из рядов следует перенести на вспомогательную ось.
если значения рядов на диаграмме существенно различаются по величине. Так, сумма нескольких слагаемых, выдаваемая на график, может быть существенно больше каждого отдельного слагаемого. В этом случае следует на каждой оси размещать ряды с одинаковым порядком значений Y.
Для этого занесите табличные данные на рабочий лист Excel и отформатируйте таблицу.
Для вычисления итоговых данных воспользуйтесь инструментом Автосуммирование и введите в ячейку В6 формулу как показано на рисунке 34.

Рисунок SEQ Рисунок \* ARABIC 34 – Вычисление итоговых значений
Скопируем формулу из ячейки В6 в С6.
Для построения гистограммы выделите диапазон А3:С6 и нажмите на панели инструментов кнопку Мастер диаграмм .
На первом шаге работы Мастера диаграмм в окне Тип выделите диаграмму Гистограмма, а в окне Вид выделите обычную гистограмму, отображающую значения различных категорий, как показано на рисунке 35. Нажмите кнопку Далее для перехода на второй шаг Мастера.

Рисунок SEQ Рисунок \* ARABIC 35 – Первый шаг работы мастера диаграмм
На втором шаге потребуется настроить ряды для чего сначала следует обратить внимание на пульсирующий контур вокруг диапазона А3:С6, показывающий на основе каких данных строится диаграмма. Также на этом шаге следует убедиться в правильности предлагаемой Excel ориентации данных. Как видно из рисунка 36 если все оставить без изменений мы получим не тот график, который хотели.

Рисунок SEQ Рисунок \* ARABIC 36 – Ориентация рядов данных по умолчанию
Изменим ориентацию рядов, пометив селектор Ряды в: строках, и диаграмма примет вид, близкий к искомому.
Однако нам еще необходимо добавить подписи по оси Х: вместо цифры 1 указать Январь, а 2 – Февраль. Для этого нам потребуется перейти на закладку Ряд. На рисунке 37 поле Ряд содержит список рядов, поле Имя – имя выбранного ряда, поле Значение – диапазон ячеек ряда. В поле Подписи оси Х необходимо записать название месяцев, т.е. диапазон =Лист1!$B$2:$C$2.

Рисунок SEQ Рисунок \* ARABIC 37 – Задание подписей по оси Х
Нажмем ОК для перехода к следующему шагу.
На третьем шаге Мастера диаграмм ознакомьтесь с вкладками, открывающегося окна Параметры диаграммы, при этом следует иметь в виду, что состав и содержание вкладок этого окна зависят от типа и вида создаваемой диаграммы. Далее выполните следующие действия:
на вкладке Заголовки введите в соответствующие поля Название диаграммы – «Данные о продажах», Ось Х (категорий) – «Месяц», Ось Y (значений) – «руб.», как показано на рисунке 38;

Рисунок SEQ Рисунок \* ARABIC 38 – Задание заголовков диаграммы
откройте вкладку Легенда и посмотрите, как Мастер диаграмм расположил легенду. Пометьте различные секторы, определяющие положение легенды, и посмотрите, как оно будет изменяться на диаграмме. Окончательно установите расположение легенды внизу как показано на рисунке 39.

Рисунок SEQ Рисунок \* ARABIC 39 – Добавление легенды к диаграмме
на вкладке Подписи данных пометьте селектор Значения, после чего на диаграмме появятся численные значения элементов всех рядов данных, как показано на рисунке 40;

Рисунок SEQ Рисунок \* ARABIC 40 – Добавление подписей к данным на диаграмме
последовательно откройте вкладки Оси и Линии сетки и посмотрите, как пометка соответствующих флагов и селекторов влияет на вид диаграммы. Окончательно оставьте на диаграмме обе оси X и Y и основные линии сетки по оси Y;
изучите назначение флагов на вкладке Таблица данных;
щелкните кнопку Далее.
В последнем четвертом шаге Мастера диаграмм Расположение диаграммы определяется: будет ли диаграмма расположена на том же листе, что и исходные данные (будет встроенной), либо разместится на отдельном листе. Поместите диаграмму на отдельном листе, пометив соответствующий селектор, и задайте название «Гистограмма» как показано на рисунке 41.

Рисунок SEQ Рисунок \* ARABIC 41 – Задание расположения диаграммы
Щелкните кнопку ОК после чего в рабочей книге добавится специальный тип рабочего листа – Диаграмма с построенным на нем графиком.
Для переноса ряда «Итого» на другую ось необходимо открыть окно форматирования ряда и во вкладке Ось пометить селектор По вспомогательной оси. После этого с помощью команды Диаграмма►Параметры диаграммы►Заголовки следует дать название второй оси. Названия основной и вспомогательной оси должны быть таковы, чтобы было ясно, какие ряды размещены на каждой из них. Иногда для наглядности одновременно с переносом ряда на другую ось полезно изменить его тип представления.
Итак, после построения гистограммы выделите ряд «Итого», для чего щелкните один раз левой кнопкой мыши в середине любого элемента ряда «Итого», например Январь. Ряд будет считаться выделенным, если каждый элемент ряда буде помечен маркером, как показано на рисунке 42.

Рисунок SEQ Рисунок \* ARABIC 42 – Выделенный ряд данных
Щелкните правой кнопкой мыши, чтобы открыть окно контекстного меню (рисунок 42). С помощью команды Тип диаграммы преобразуйте ряд «Итого» в тип График как показано на рисунке 43.

Рисунок SEQ Рисунок \* ARABIC 43 – Смена типа диаграммы для выделенного ряда
Щелкните правой кнопкой мыши, чтобы открыть окно форматирования ряда. С помощью вкладки Ось перенесите ряд на вторую (вспомогательную) ось Y, для чего пометьте селектор по вспомогательной оси (рисунок 44).

Рисунок SEQ Рисунок \* ARABIC 44 – Перенос данных на вспомогательную ось
Выделите область построения диаграммы, для чего щелкните один раз левой кнопкой мыши в середине диаграммы. Выделенная область построения диаграммы будет выглядеть так, как показано на рисунке 45.

Рисунок SEQ Рисунок \* ARABIC 45 – Выделенная область построения диаграммы
Откройте окно Параметры диаграммы и с помощью вкладки Заголовки назовите вторую ось «Итого, тыс.руб.» так, чтобы Итого и тыс.руб. располагались в разных строках.
Обратите внимание на изменение масштаба основной оси и общего вида диаграммы.
Для того, чтобы названия осей Y (основной и дополнительной) располагались над осью следует отредактировать их заголовки. При этом следует различать операцию редактирования над элементом (в данном случае заголовком оси) в целом и операцию редактирования со значением элемента (текстом заголовка). Для выполнения любой операции элемент должен быть выделен, после чего вокруг него появляется штриховая (символьная) рамка как показано на рисунке 46.

Рисунок SEQ Рисунок \* ARABIC 46 – Выделенный элемент диаграммы – заголовок оси Y
Если указатель вставки (курсор) внутри рамки отсутствует, то операция будет применяться ко всему элементу. Этот указатель появляется после щелчка на данных внутри символьной рамки. Для его удаления следует щелкнуть по самой рамке или нажать клавишу Esc.
Итак, в окне Формат названия оси на вкладке Выравнивание измените в окне Ориентация ориентацию Надписи, установив ее равной 0, как показано на рисунке 47.

Рисунок SEQ Рисунок \* ARABIC 47 – Редактирование ориентации заголовка оси
Для перемещения заголовка необходимо его выделить, затем установить мышь на символьной рамке так, чтобы ее указатель принял форму  (Выбор объекта). Переместите элемент заголовка, нажав левую кнопку мыши и «отбуксуйте» его в верхнюю часть оси Y.
Повторите все указанные выше действия и для заголовка на дополнительной оси. Для того чтобы название оси располагалось в двух строках, следует активизировать курсор внутри области заголовка после слова «Итого» и принудительно разбить текст на две строки с помощью клавиши Enter.
Окончательный вид диаграммы приведен на рисунке 48.

Рисунок SEQ Рисунок \* ARABIC 48 – Гистограмма с дополнительной осью

Лекция №5. Деловая графика. Cпарклайны в Еxcel 2010
Спарклайны в Excel — создание микрографиков

C приходом Excel 2010 появилась новая возможность создавать спарклайн-графики. Спарклайн – это небольшая диаграмма, помещенная в одну ячейку. Она позволяет быстро отслеживать динамически изменяющиеся данные. Поскольку спарклайны умещаются в одну ячейку, их принято использовать группами.
Хотя спарклайны во многом схожи с обычными диаграммами, его функциональность полностью отделена от функциональности диаграмм в Excel. К примеру, размещение диаграммы требует отдельного слоя и на диаграмме можно построить несколько рядов данных. Спарклайн отображается внутри ячейки и показывает только один ряд данных.
На рисунке отображены три различных вида спарклайнов, столбец H:

Для каждого спарклайна соответствует шесть точек данных (отображены слева). Всего их доступно три разных вида:
График – схож с диаграммой в виде графика. Для данного типа спарклайнов возможен вариант отображения маркера для каждой точки данных. Первая группа показывает спарклайн с маркерами.
Гистограмма – как и обычная гистограмма. Вторая группа показывает те же данные, в самом правом столбце.
Выигрыш/проигрыш – делит все данные на два типа: отрицательные и положительные и отображает их в виде верхнего и нижнего блока. Данный вид спарклайнов реализован в третьей группе.
Чтобы создать спарклайн-графику, выберите данные, которые вы хотите визуализировать. Затем перейдите во вкладку Вставка в группу Спарклайны и выберите один из трех типов спарклайнов: гистограмма, график или выигрыш/проигрыш.

Excel отобразит диалоговое окно Создание спарклайнов, где необходимо будет ввести диапазон данных и диапазон расположения. Как привило, графики располагаются рядом с данными для лучшего восприятия, но это не обязательное требование. Большую часть времени вы будете использовать пустой диапазон для хранения спарклайнов.
Однако Excel не мешает вам вставлять спарклайны в ячеки, которые уже содержат данные. Расположение спарклайнов, которое вы указываете, должно соответствовать источнику данных по количеству строк или количеству столбцов.
Excel предоставляет достаточный набор инструментов для изменения внешнего вида спарклайна. Для этого вам необходимо перейти по вкладке Работа со спаркайнами -> Конструктор (данная вкладка появляется, когда вы выбираете ячейку со спарклайном).
На рисунке ниже отображена группа спарклайнов в диапазоне H3:H13 в сравнении с обычным графиком. Каждый из этих видов визуализаций хорош по-своему. Если цель стоит — сравнение значений в различных городах в определенный месяц – оптимальным выбором будет график. Спарклайны, с другой стороны, полезны для отображения тенденций отдельных городов.

Создание спарклайна
Выберите пустую ячейку или группу ячеек, в которые необходимо вставить один или несколько спарклайнов.
На вкладке Вставка в группе Спарклайны выберите тип спарклайна: График, Гистограмма или Выигрыш/проигрыш.

В поле Диапазон данных укажите диапазон ячеек с данными, на основе которых будут созданы спарклайны.
Примечание    Можно нажать кнопку , чтобы временно свернуть диалоговое окно, выделить на листе нужный диапазон ячеек и нажать кнопку , чтобы восстановить прежний размер диалогового окна.
При выборе одного или нескольких спарклайнов появляется окно Работа со спарклайнами с вкладкой Конструктор. На вкладке Конструктор можно выбрать одну или несколько команд из групп Спарклайн, Тип, Показать, Стиль и Группировать. С помощью этих команд можно создать спарклайн, изменить его тип, отформатировать его, показать или скрыть точки данных на графике спарклайна или задать формат вертикальной оси в группе спарклайнов.
Excel - наиболее часто используемая программа у многих людей, работающих с цифрами. Периодически, а то и всегда, возникает необходимость массу данных представить в более презентабельном и удобном виде. Есть для этого диаграммы конечно. Но теперь появилась и новинка. Это спарклайны. Что же это?
Спарклайны — это небольшие простые диаграммы, которые содержат много сведений в небольшом пространстве, которые часто встроены в текст. Спарклайны часто используются в таблицах и матрицах. Их преимуществом является возможность просматривать большое их количество одновременно и быстро сравнивать их друг с другом без необходимости просматривать каждое изображение отдельно. На них удобно смотреть выбросы, строки, отличающиеся от других. Хотя они небольшие, каждый спарклайн часто представляет множественные точки во времени. Каждый спарклайн обычно представляет отдельный ряд. Спарклайн нельзя добавить к группе сведений в таблице. Спарклайны отображают статистические данные, поэтому должны входить в ячейку, которая связана с группой. Sparkline-графики и гистограммы содержат основные элементы диаграмм (категории, ряды и значения), но не имеют условных обозначений, линий осей, меток или делений.
Где же их найти? Чтобы добавить спарклайн, необходимо перейти во вкладку "Вставка". Теперь на ленте мы увидим небольшой раздел "Спарклайны".

На изображении хорошо видно, что нам доступно три типа спарклайнов: график, столбец и выигрыш/проигрыш. Создав спарклайн одного типа, мы в любое время можем сменить его тип. Для этого достаточно выделить его, выбрать вкладку "Работа со спарклайнами" - "Конструктор" и на ленте появятся все инструменты, для работы с ним. В том числе и изменения типа.
На приведенном изображении наглядно показана тенденция изменения значения в каждой строке. И это действительно удобнее просматривать, чем просто пересматривать все цыфры. К тому же это компактно. В данном примере я использовал тип "линия". Из визуальных возможностей отметил только максимальные значения зеленым цветом и минимальные значения красным цветом. Каждый же может настроить на свой вкус. Достаточно просто "поиграться" с настройками спарклайна.
Теперь благодаря такой мелочи вы можете украсить ваши "сухие" цифровые данные, не используя громоздких диаграмм.

Лекция №6. Технология обработки анализа экономической информации1. Технология автоматизированной обработки экономической информации1.1 Принципы обработки экономической информацииТехнология автоматизированной обработки экономической информации строится на следующих принципах:
• интеграции обработки данных и возможности работы пользователей в условиях эксплуатации автоматизированных систем централизованного хранения и коллективного использования данных (банков данных);
• распределенной обработки данных на базе развитых систем передачи;
• рационального сочетания централизованного и децентрализованного управления и организации вычислительных систем;
• моделирования и формализованного описания данных, процедур их преобразования, функций и рабочих мест исполнителей;
• учета конкретных особенностей объекта, в котором реализуется машинная обработка экономической информации.
1.2 Типы организации технологических процессовРазличают два основных типа организации технологических процессов: предметный и пооперационный.
Предметный тип организации технологии предполагает создание параллельно действующих технологических линий, специализирующихся на обработке информации и решении конкретных комплексов задач (учет труда и заработной платы, снабжение и сбыт, финансовые операции и т.п.) и организующих пооперационную обработку данных внутри линии.
Пооперационный (поточный) тип построения технологического процесса предусматривает последовательное преобразование обрабатываемой информации, согласно технологии, представленной в виде непрерывной последовательности сменяющих друг друга операций, выполняемых в автоматическом режиме. Такой подход к построению технологии оказался приемлемым при организации работы абонентских пунктов и автоматизированных рабочих мест.
Организация технологии на отдельных ее этапах имеет свои особенности, что дает основание для выделения внемашинной и внутримашинной технологии. Внемашинная технология (ее нередко именуют предбазовой) объединяет операции сбора и регистрации данных, запись данных на машинные носители с контролем. Внутримашинная технология связана с организацией вычислительного процесса в ЭВМ, организацией массивов данных в памяти машины и их структуризацией, что дает основание называть ее еще и внутрибазовой. Учитывая, что средствам, составляющим техническую базу внемашинного и внутримашинного преобразования информации, посвящены последующие главы учебника, кратко рассмотрим лишь особенности построения названных технологий.
Основной этап технологического процесса связан с решением функциональных задач на ЭВМ. Внутримашинная технология решения задач на ЭВМ, как правило, реализует следующие типовые процессы преобразования экономической информации: формирование новых массивов информации; упорядочение информационных массивов; выборка из массива некоторой части записей, слияние и разделение массивов; внесение изменений в массив; выполнение арифметических действий над реквизитами в пределах записей, в пределах массивов, над записями нескольких массивов. Решение каждой отдельной задачи или комплекса задач требует выполнения следующих операций: ввод программы машинного решения задачи и размещение ее в памяти ЭВМ, ввод исходных данных, логический и арифметический контроль введенной информации, исправление ошибочных данных, компоновка входных массивов и сортировка введенной информации, вычисления по заданному алгоритму, получение выходных массивов информации, редактирование выходных форм, вывод информации на экран и на машинные носители, печать таблиц с выходными данными.
Выбор того или иного варианта технологии определяется прежде всего объемно-временными особенностями решаемых задач, периодичностью, срочностью, требованиями к быстроте обработки сообщений и зависит как от диктуемого практикой режима взаимодействия пользователя с ЭВМ, так и режимных возможностей технических средств - в первую очередь ЭВМ.
Различают следующие режимы взаимодействия пользователя с ЭВМ: пакетный и интерактивный (запросный, диалоговый). Сами же ЭВМ могут функционировать в различных режимах: одно- и многопрограммном, разделении времени, реального времени, телеобработки. При этом предусматривается цель удовлетворения потребности пользователей в максимально возможной автоматизации решения разнообразных задач.
Пакетный режим был наиболее распространен в практике централизованного решения экономических задач, когда большой удельный вес анализа производственно-хозяйственной деятельности экономических объектов разного уровня управления.
Организация вычислительного процесса при пакетном режиме строилась без доступа пользователя к ЭВМ. Его функции ограничивались подготовкой исходных данных по комплексу информационно-взаимосвязанных задач и передачей их в центр обработки, где формировался пакет, включающий задание для ЭВМ на обработку, программы, исходные, нормативно-расценочные и справочные данные. Пакет вводился в ЭВМ и реализовывался в автоматическом режиме без участия пользователя и оператора, что позволяло минимизировать время выполнения заданного набора задач. При этом работа ЭВМ могла проходить в однопрограммном или многопрограммном режиме, что предпочтительнее, так как обеспечивалась параллельная работа основных устройств машины. В настоящее время пакетный режим реализуется применительно к электронной почте.
Интерактивный режим предусматривает непосредственное взаимодействие пользователя с информационно-вычислительной системой, может носить характер запроса (как правило, регламентированного) или диалога с ЭВМ.
Запросный режим необходим пользователям для взаимодействия с системой через значительное число абонентских терминальных устройств, в том числе удаленных на значительное расстояние от центра обработки. Такая необходимость обусловлена решением оперативных задач, какими являются, например, маркетинговые задачи, задачи перестановки кадров, задачи стратегического характера и т.п. ЭВМ в подобных случаях реализует систему массового обслуживания, работает в режиме разделения времени, при котором несколько независимых абонентов (пользователей) с помощью устройств ввода-вывода имеют в процессе решения своих задач непосредственный и практически одновременный доступ к ЭВМ. Этот режим позволяет дифференцированно в строго установленном порядке предоставлять каждому пользователю время для общения с ЭВМ, а после окончания сеанса отключать его.
Диалоговый режим открывает пользователю возможность непосредственно взаимодействовать с вычислительной системой в допустимом для него темпе работы, реализуя повторяющийся цикл выдачи задания, получения и анализа ответа. При этом ЭВМ сама может инициировать диалог, сообщая пользователю последовательность шагов (предоставление меню) для получения искомого результата.
Обе разновидности интерактивного режима (запросный, диалоговый) основываются на работе ЭВМ в режимах реального времени и телеобработки, которые являются дальнейшим развитием режима разделения времени. Поэтому обязательными условиями функционирования системы в этих режимах являются: во-первых, постоянное хранение в запоминающих устройствах ЭВМ необходимой информации и программ и лишь в минимальном объеме поступление исходной информации от абонентов и, во-вторых, наличия у абонентов соответствующих средств связи с ЭВМ для обращения к ней в любой момент времени.
2. Автоматизированная обработка экономической информации2.1 Экономическая информация и ее обработкаЭкономическая информация – это преобразованная и обработанная совокупность сведений, отражающая состояние и ход экономических процессов. Экономическая информация циркулирует в экономической системе и сопровождает процессы производства, распределения, обмена и потребления материальных благ и услуг. Экономическую информацию следует рассматривать как одну из разновидностей управленческой информации.
Экономическая информация может быть:
- управляющая (в форме прямых приказов, плановых заданий и т.д.);
- осведомляющая (в отчетных показателях, выполняет в экономической системе функцию обратной связи).
Информацию можно рассматривать как ресурс, аналогичный материальным, трудовым и денежным ресурсам. Информационные ресурсы – совокупность накопленной информации, зафиксированной на материальных носителях в любой форме, обеспечивающей ее передачу во времени и пространстве для решения научных, производственных, управленческих и других задач.
Сбор, хранение, обработка, передача информации в числовой форме осуществляется с помощью информационных технологий. Особенностью информационных технологий является то, что в них и предметом и продуктом труда является информация, а орудиями труда – средства вычислительной техники и связи.
Основная цель информационных технологий - производство необходимой пользователю информации в результате целенаправленных действий по ее переработке.
Известно, что информационная технология – это совокупность методов, производственных и программно-технологических средств, объединенных в технологическую цепочку, обеспечивающую сбор, хранение, обработку, вывод и распространение информации.
С точки зрения информационных технологий для информации необходим материальный носитель в качестве источника информации, передатчик, канал связи, приемник и получатель информации.
Сообщение от источника к получателю передается через каналы связи или посредством среды.
Информация является формой связи между управляемыми и управляющими объектами в любой системе управления. В соответствии с общей теорией управления, процесс управления можно представить как взаимодействие двух систем - управляющей и управляемой.
Точность информации обеспечивает ее однозначное восприятие всеми потребителями. Достоверность определяет допустимый уровень искажения как поступающей, так и результатной информации, при котором сохраняется эффективность функционирования системы. Оперативность отражает актуальность информации для необходимых расчетов и принятия решений в изменившихся условиях.
В процессах автоматизированной обработки экономической информации в качестве объекта, подвергающегося преобразованиям, выступают различного рода данные, которые характеризуют те или иные экономические явления. Такие процессы именуются технологическими процессами АОЭИ и представляют собой комплекс взаимосвязанных операций, протекающих в установленной последовательности. Или, более детально, это процесс преобразования исходной информации в выходную с использованием технических средств и ресурсов.
Рациональное проектирование технологических процессов обработки данных в ЭИС во многом определяет эффективное функционирование всей системы.
Весь технологический процесс можно подразделить на процессы сбора и ввода исходных данных в вычислительную систему, процессы размещения данных и хранения в памяти системы, процессы обработки данных с целью получения результатов и, процессы выдачи данных в виде, удобном для восприятия пользователем.
2.2 Этапы технологического процессаТехнологический процесс можно разделить на 4 укрупненных этапа:
1.- начальный или первичный (сбор исходных данных, их регистрация и передача на ВУ);
2.- подготовительный (прием, контроль, регистрация входной информации и перенос ее на машинный носитель);
3.- основной (непосредственно обработка информации);
4.- заключительный (контроль, выпуск и передача результатной информации, ее размножение и хранение).
В зависимости от используемых технических средств и требований к технологии обработки информации изменяется и состав операций технологического процесса. Например: информация на ВУ может поступать на МН, подготовленных для ввода в ЭВМ или передаваться по каналам связи с места ее возникновения.
Операции сбора и регистрации данных осуществляются с помощью различных средств.
Различают:
─механизированный;
─автоматизированный;
2.3 Автоматические способы сбора и регистрации данных1). Механизированный - сбор и регистрация информации осуществляется непосредственно человеком с использованием простейших приборов (весы, счетчики, мерная тара, приборы учета времени и т.д.).
2). Автоматизированный - использование машиночитаемых документов, регистрирующих автоматов, универсальных систем сбора и регистрации, обеспечивающих совмещение операций формирования первичных документов и получения машинных носителей.
3). Автоматический - используется в основном при обработке данных в режиме реального времени.
(Информация с датчиков, учитывающих ход производства - выпуск продукции, затраты сырья, простои оборудования и т.д. - поступает непосредственно в ЭВМ).
Технические средства передачи данных включают:
─ аппаратуру передачи данных (АПД), которая соединяет средства обработки и подготовки данных с телеграфными, телефонными и широкополосными каналами связи;
─ устройства сопряжения ЭВМ с АПД, которые управляют обменом информации - мультиплексоры передачи данных.
Запись и передача информации по каналам связи в ЭВМ имеет следующие преимущества:
─ упрощает процесс формирования и контроля информации;
─ соблюдается принцип однократной регистрации информации в первичном документе и машинном носителе;
─ обеспечивается высокая достоверность информации, поступающей в ЭВМ.
Дистанционная передача данных, основанная на использовании каналов связи, представляет собой передачу данных в виде электрических сигналов, которые могут быть непрерывными во времени и дискретными, т.е. носить прерывный во времени характер. Наиболее широко используются телеграфные и телефонные каналы связи. Электрические сигналы, передаваемые по телеграфному каналу связи являются дискретными, а по телефонному - непрерывными.
В зависимости от направлений, по которым пересылается информация, различают каналы связи:
─ симплексный (передача идет только в одном направлении);
─ полудуплексный (в каждый момент времени производится либо передача, либо прием информации);
─ дуплексный (передача и прием информации осуществляются одновременно в двух встречных направлениях).
Каналы характеризуются скоростью передачи данных, достоверностью, надежностью передачи.
Скорость передачи определяется количеством информации, передаваемой в единицу времени и измеряется в бодах (бод = бит/сек).
Телеграфные каналы (низкоскоростные - V=50-200 бод),
телефонные (среднескоростные - V=200-2400 бод), а
широкополосные (высокоскоростные - V=4800 бод и более).
При выборе наилучшего способа передачи информации учитываются объемные и временные параметры доставки, требования к качеству передаваемой информации, трудовые и стоимостные затраты на передачу информации.
Говоря о технологических операциях сбора, регистрации, передачи информации с помощью различных технических средств необходимо несколько слов сказать и о сканирующих устройствах.Ввод информации, особенно графической, с помощью клавиатуры в ЭВМ очень трудоемок. В последнее время наметились тенденции применения деловой графики - одного из основных видов информации, что требует оперативности ввода в ЭВМ и предоставления пользователям возможности формирования гибридных документов и БД, объединяющих графику с текстом. Все эти функции в ПЭВМ выполняют сканирующие устройства. Они реализуют оптический ввод информации и преобразование ее в цифровую форму с последующей обработкой.
Для ПЭВМ IBM PC разработана система PC Image/Graphix, предназначенная для сканирования различных документов и их передачи по коммуникациям. В числе документальных носителей системы, которые могут сканироваться камерой, являются: текст, штриховые чертежи, фотографии, микрофильмы. Сканирующие устройства на базе ПЭВМ применяются не только для ввода текстовой и графической информации, но и в системах контроля, обработки писем, выполнения различных учетных функций.Для указанных задач наибольшее применение нашли способы кодирования информации штриховыми кодами. Сканирование штриховых кодов для ввода информации в ПЭВМ производится с помощью миниатюрных сканеров, напоминающих карандаш. Сканер перемещается пользователем перпендикулярно группе штрихов, внутренний источник света освещает область этого набора непосредственно около наконечника сканера. Штриховые коды нашли широкое применение и в сфере торговли, и на предприятиях (в системе табельного учета: при считывании с карточки работника фактически отработанное время, регистрирует время, дату и т.д.).
В последнее время все большее внимание уделяется устройствам тактильного ввода - сенсорному экрану ("сенсорный" - чувствительный). Устройства тактильного ввода широко применяются как информационно-справочные системы общего пользования и системы автоматизированного обучения. Фирмой США разработан сенсорный монитор Point-1 с разрешением 1024 х 1024 точек для ПЭВМ IBM PC и др. ПЭВМ. Сенсорный экран широко применяется для фондовых бирж (сведения о последних продажных ценах на акции ...).На практике существует множество вариантов (организационных форм) технологических процессов обработки данных. Это зависит от использования различных средств вычислительной и организационной техники на отдельных операциях технологического процесса.Построение технологического процесса зависит от характера решаемых задач, круга пользователей, от используемых технических средств, от систем контроля данных и т.д.
3. Возможности программы ExcelПрограмма Microsoft Excel относится к классу программ, называемых электронными таблицами. Электронные таблицы ориентированы прежде всего на решение экономических и инженерных задач, позволяют систематизировать данные из любой сферы деятельности. Существуют следующие версии данной программы – Microsoft Excel 4.0, 5.0, 7.0, 97, 2000. В данном практикуме рассмотрена версия 97. Знакомство с более ранними версиями позволит легко перейти к следующей.
Программа Microsoft Excel позволяет:
сформировать данные в виде таблиц;
рассчитать содержимое ячеек по формулам, при этом возможно использование более 150 встроенных функций;
представить данные из таблиц в графическом виде;
организовать данные в конструкции, близкие по возможностям к базе данных.
В Microsoft Excel имеется 12 функций рабочего листа, используемых для анализа данных из списков или баз данных. Каждая из этих функций, которые из соображений совместимости имеют обобщенное название БДФункция, использует три аргумента: база данных, поле и критерий. Эти три аргумента ссылаются на интервалы ячеек на рабочем листе, которые используются данной функцией.
База данных — это интервал ячеек, формирующих список или базу данных.
База данных в Microsoft Excel — это список связанных данных, в котором строки данных являются записями, а столбцы - полями. Верхняя строка списка содержит названия каждого столбцов. Ссылка может быть задана как диапазон ячеек либо как имя, соответствующее диапазону списка.
Поле определяет столбец, используемый функцией. Поля данных в списке должны содержать идентифицирующее имя в первой строке. Аргумент поле может быть задан как текст с названием столбца в двойных кавычках, например «Возраст» или «Урожай» в приведенном ниже примере базы данных, или как число, задающее положение столбца в списке: 1 — для первого поля (Дерево), 2 — для второго поля (Высота) и так далее.
Критерий — это ссылка на интервал ячеек, задающих условия для функции. Функция возвращает данные из списка, которые удовлетворяют условиям, определенным диапазоном критериев. Диапазон критериев включает копию названия столбца в списке, для которого выполняется подведение итогов. Ссылка на критерий может быть введена как интервал ячеек, например A1:F2 в приведенном ниже примере базы данных, или как имя интервала, например "Критерии". Для получения дополнительных сведений об условиях, которые могут быть использованы в качестве аргумента критерий нажмите кнопку.
Функции для работы с базами данных и списками
БДДИСП Оценивает дисперсию по выборке из выделенных записей базы данных
БДДИСПП Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных
БДПРОИЗВЕД Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию
БДСУММ Суммирует числа в поле для записей базы данных, удовлетворяющих условию
БИЗВЛЕЧЬ Извлекает из базы данных одну запись, удовлетворяющую заданному условию
БСЧЁТ Подсчитывает количество числовых ячеек в базе данных
БСЧЁТА Подсчитывает количество непустых ячеек в базе данных
ДМАКС Возвращает максимальное значение среди выделенных записей базы данных
ДМИН Возвращает минимальное значение среди выделенных записей базы данных
ДСРЗНАЧ Возвращает среднее значение выбранных записей базы данных
ДСТАНДОТКЛ Оценивает стандартное отклонение по выборке из выделенных записей базы данных
ДСТАНДОТКЛП Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных
Организация данных в программе
Файл программы представляет собой так называемую рабочую книгу, или рабочую папку. Каждая рабочая книга может содержать 256 рабочих листов. По умолчанию версия программы Excel 97 содержит 3 рабочих листа, предыдущая версия программы по умолчанию содержала 16 рабочих листов. На листах может содержаться как взаимосвязанная, так и совершенно независимая информация. Рабочий лист представляет собой заготовку для таблицы.
РАСЧЕТ ПО ФОРМУЛАМПравила работы с формулами
формула всегда начинается со знака =;
формула может содержать знаки арифметических операций + – * / (сложение, вычитание, умножение и деление);
если формула содержит адреса ячеек, то в вычислении участвует содержимое ячейки;
для получения результата нажмите <Enter>.
Если необходимо рассчитать данные в столбце по однотипной формуле, в которой меняются только адреса ячеек при переходе на следующую строку таблицы, то такую формулу можно скопировать или размножить на все ячейки данного столбца.
Например:
№ Наименование товара Единица измерения Цена одного экземпляра Количество На сумму
1 Молоко пакет 4,9 100

Расчет суммы в последнем столбце происходит путем перемножения данных из столбца “Цена одного экземпляра” и данных из столбца “Количество”, формула при переходе на следующую строку в таблице не изменяется, изменяются только адреса ячеек.
Копирование содержимого ячеек
Выделяем исходную ячейку, помещаем указатель мыши на край рамки и при нажатой клавише <Cntr> и левой клавише мыши перемещаем рамочку в новое место. При этом копируется содержимое ячейки, в том числе и формула.
Автозаполнение ячеек
Выделяем исходную ячейку, в нижнем правом углу находится маркер заполнения, помещаем курсор мыши на него, он примет вид + ; при нажатой левой клавише растягиваем границу рамки на группу ячеек. При этом все выделенные ячейки заполняются содержимым первой ячейки. При этом при копировании и автозаполнении соответствующим образом изменяются адреса ячеек в формулах. Например, формула = А1 + В1 изменится на = А2 + В2.
Если формула содержит адреса, ссылка на которые не должна изменяться, перед этим адресом необходимо указать знак $.
Например: = $A$5 * A6
При копировании этой формулы в следующую строку ссылка на первую ячейку останется неизменной, а второй адрес в формуле изменится.
Расчет итоговых сумм по столбцам
В таблицах часто необходимо подсчитать итоговые суммы по столбцу. Для этого существует специальная пиктограмма Автосуммирование. Предварительно ячейки с исходными данными нужно выделить, для этого нажимаем пиктограмму, сумма будет расположена в свободной ячейке под столбцом.
Лекция №7. Обработка данных с помощью встроенных функций Excel. Применение встроенных функций при решении экономических задач
Финансовая функция ПЛТ
Лист1 в книге ФИНАНСОВЫЙ АНАЛИЗ переименуйте в ПЛТ. Все упражнения в данной лабораторной работе выполняйте на листе ПЛТ.
Рассмотрим пример расчета 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате с помощью функции ПЛТ.
Для приведенного на рис.4.1.1 ипотечного расчета в ячейки введены формулы, показанные на рис. 4.1.2.

Рис. 4.1.1 Расчет ипотечной ссуды
Введите представленные на рис. 4.1.2. данные на лист ПЛТ и сравните полученный результат с данными на рис. 4.1.1.

Рис. 4.1.2 Формулы для расчета ипотечной ссуды
Функция ПЛТ вычисляет величину постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянном процентной ставке.

Синтаксис: ПЛТ(ставка; кпер; пс; бс; тип).
Аргументы:
ставка—процентная ставка по ссуде, кпер — общее число выплат по ссуде, пс — приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой, бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение бс равно 0, Тип — число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.
Если бс = 0 и тип = 0, то функция ПЛТ вычисляет по формуле (1):
(1)
где Р — пс;
i — ставка;
n — кпер.
Отметим, что очень важно быть последовательным в выборе единиц измерения для задания аргументов ставка и КПЕР. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента ставка используйте 12%/12, а для задания аргумента КПЕР - 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента ставка используйте 12%, а для задания аргумента КПЕР - 4.
Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЛТ значение на величину КПЕР. Интервал выплат — это последовательность постоянных денежных платежей, осуществляемых за непрерывный период. Например, заем под автомобиль или заклад являются интервалами выплат. В функциях, связанных с интервалами выплат, выплачиваемые вами деньги, такие как депозит на накопление, представляются отрицательным числом, а деньги, которые вы получаете, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом -1000, если вы вкладчик, и аргументом 1000, если вы — пpeдставитель банка.
Функции для расчета амортизации АПЛ, АСЧ, ФУО и ДДОБ.
В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данной лабораторной работе выполняйте на Листе6. Лист6 переименуйте в АПЛ, АСЧ, ФУО, ДДОБ.
Под амортизацией подразумевается уменьшение (обычно — на единицу времени) стоимости имущества в процессе эксплуатации.
Функция АПЛ (sln) возвращает величину амортизации имущества за один период времени, используя метод равномерной амортизации.
Синтаксис: АПЛ(нач_стоимость; ост_стоимость; время_ эксплуатации).
Аргументы:
нач_стоимость - начальная стоимость имущества; ост_стоимость -остаточная стоимость в конце амортизации (иногда называется ликвидной стоимостью имущества); время_эксплуатации - количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).
Рассмотрим на конкретном примере применение всех функций, описываемых в данном разделе.
Предположим вы купили за 6000 руб. компьютер, который имеет срок эксплуатации 5 лет, после чего оценивается в 1000 руб.
Снижение стоимости компьютера для каждого года эксплуатации вычисляется по формуле:
=АПЛ(6000; 1000; 5)
В результате получаем: 1 000 р.
Функция АСЧ (syd) возвращает годовую амортизацию имущества для указанного периода.
Синтаксис:
АСЧ(нач_стоимость; ост_стоимость; время_эксплуатации; период).
Аргументы:
нач_стоимость - начальная стоимость имущества; ост_стоимость-остаточная стоимость в конце амортизации (иногда называется ликвидной стоимостью имущества); время_эксплуатации - количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации); период - номер периода для вычисления амортизации (должен быть измерен в тех же единицах, что и время_эксплуатации).
Возвращаясь к предыдущему примеру, можно рассчитать амортизацию, например, за первый год эксплуатации компьютера по формуле
=АСЧ(6000;1000;5;1)
и за последний год по формуле
=АСЧ(6000;1000;5;5)
В результате получим: 1666.67р. и 333.33 p., соответственно.
Функция ФУО (DB) возвращает амортизацию имущества за заданный период, используя метод постоянного учета амортизации.
Синтаксис: ФУО(нач_стоимость; ост_стоимость; время_эксплуатации; период; месяц)
Аргументы:
нач_стоимость - начальная стоимость имущества; ост_стоимость - остаточная стоимость в конце амортизации (иногда называется ликвидной стоимостью имущества); время_эксплуатации - количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации); период - номер периода для вычисления амортизации (измеряется в тех же единицах, что и время_эксплуатации); месяц - количество месяцев в первом году. Если аргумент месяц опущен, то предполагается, что он равен 12.
Метод постоянного учета амортизации вычисляет амортизацию, используя фиксированную процентную ставку.
При расчете нашего примера получим амортизацию за время эксплуатации компьютера:
=ФУО(6000;1000;5;1) вычисляет 1806-ООр.
=ФУО(6000;1000;5;2) вычисляет 1262.394р.
=ФУО(6000;1000;5;3) вычисляет 882.41р.
=ФУО(6000;1000;5; 4) вычисляет 616.81р.
=ФУО(6000;1000;5;5) вычисляет 431.15р.
Функция ДДОБ (ddb) возвращает величину амортизации имущества для указанного периода, используя метод двукратного (или k-кратного) учета амортизации.
Синтаксис: ДДОБ (нач_стоимость; ост_стоимость; время_эксплуатации; период; коэффициент)
Аргументы:
нач_стоимость - начальная стоимость имущества; ост_стоимость - остаточная стоимость в конце амортизации (иногда называется ликвидной стоимостью имущества); время_эксплуатации - количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации); период - номер периода для вычисления амортизации (должен быть измерен в тех же единицах, что и время_эксплуатации); коэффициент - процентная ставка снижения балансовой стоимости (амортизации). Если коэффициент опущен, то предполагается, что он равен 2 (метод двукратного учета амортизации).
Метод двукратного учета амортизации предполагает ускоренную амортизацию имущества. При этом амортизация максимальна в первый период, а в последующие периоды снижается.
В примере с компьютером по методу двукратной амортизации она составит:
=ДДОБ(6000;1000;5;1) Ответ: 2 400.00р.
=ДДОБ(6000;1000;5;2) Ответ: 1 440.00р.
=ДДОБ(6000;1000;5;3) Ответ: 864.00р.
=ДДОБ(6000;1000;5;4) Ответ: 296.00р.
=ДДОБ(6000;1000;5;5) Ответ: 0.00р.

Рис. 4.6.1 Функции для расчета амортизации.
Лекция №8. Списки и базы данных. Фильтрация данных.Формирование итогов. Анализ данных
Примеры простейших отчетных ведомостей
В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данного раздела лабораторной работе выполняйте на Листе7. Лист7 переименуйте в Примеры списков.
Прежде чем обратиться к примерам составления отчетных ведомостей, рассмотрим средство создания списков, которое очень ускоряет и облегчает создание таблиц в Excel. На рис.4.8.1 приведены примеры допустимых списков.

Рис.4.8.1. Примеры списков
Для создания любого из приведенных списков, за исключением столбца G, достаточно ввести в ячейку первый элемент списка, выделить ячейку, установить указатель мыши на маркер заполнения ячейки и протащить его вдоль столбца (строки) до тех пор, пока не будет создан требуемый ряд. На вкладке Списки (Custom Lists) диалогового окна Параметры (Options) (рис.4.8.2), открываемого командой Сервис, Параметры (Tools, Options), приведены встроенные в Excel списки, которые представляют собой последовательности названий месяцев и дней недели.

Рис.4.8.2. Вкладка Списки диалогового окна Параметры
Используя вкладку Списки (Custom Lists), можно создавать пользовательские списки. Элементы списка пользователя надо ввести в поле Элементы списка (List Entries), причем каждый элемент вводится с новой строки. Если нажать кнопку Добавить (Add), то созданный список будет занесен в библиотеку списков. Список можно также добавить и непосредственно с рабочего листа, указав в поле Импорт списка из ячеек (Import List from Cells) диапазон, из которого импортируется список.
Кроме стандартных списков, занесенных в библиотеку. Excel позволяет легко создавать по приведенному выше алгоритму последовательности с текстом и порядковыми номерами (рис.4.8.1, столбцы Е, F и Н). Если номера меняются с шагом, отличным от единицы, необходимо в две соседние ячейки ввести первые два члена последовательности, например, экспо 87 в G1 и экспо 89 в G2, затем выделить диапазон G1:G2, установить указатель мыши на маркер заполнения диапазона и протащить его вдоль столбца до тех пор, пока не будет создан требуемый ряд.
Перед началом создания первой отчетной ведомости пополним наши знания, рассмотрев синтаксис трех функций, наиболее часто встречающихся при расчетах в таблицах: сумм, срзнач и ранг.
Функция сумм (SUM) находит сумму чисел из указанного диапазона ячеек.
Синтаксис:
СУММ(число1; число2; ...), где число1, число2, ... — числа, которые суммируются.
Функция срзнач (average) находит среднее значение чисел из указанного диапазона ячеек.
Синтаксис:
СРЗНАЧ(число1; число2; ...). Аргументы — те же, что и у функции сумм.
Функция ранг (rank) возвращает ранг числа в списке чисел. Ранг числа - это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией).
Синтаксис:
РАНГ(число; ссылка; порядок)
Аргументы:
число - число, для которого определяется ранг; ссылка - массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются; порядок - число, определяющее способ упорядочения. Если порядок равен 0 или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если порядок — любое ненулевое число, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.
Отметим, что функция ранг присваивает повторяющимся числам одинаковый ранг.
Вооружившись навыками работы со списками и знаниями о функциях сумм, срзнач и ранг перейдем к созданию двух отчетных таблиц.
Первый пример.
В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данного раздела лабораторной работе выполняйте на Листе8. Лист8 переименуйте в «Отчетная ведомость по магазинам».
Сначала обсудим, как создать отчетную ведомость о результатах работы сети магазинов, приведенную на рис.4.8.2.

Рис.4.8.2. Отчетная ведомость о работе сети магазинов за июнь — август
В ячейку Е4 введем формулу:
=СУММ(В4:D4),
которую с помощью маркера заполнения протащим на диапазон Е4:Е9. В ячейку В 10 введем формулу:
=СУММ(В4:В9),
которую протащим на диапазон В10:Е10. В ячейку G4 введем формулу:
=СРЗНАЧ(В4:D4),
которую протащим на диапазон G4:G9. В ячейку Н4 введем формулу:
=Е4/$Е$10,
которую протащим на диапазон Н4:Н9. После чего диапазону ячеек H4:H9 назначим формат с помощью кнопки на панели инструментов:

Заметим, что знак $, стоящий перед буквой в имени ячейки, дает абсолютную ссылку на столбец с данным именем, а знак $, стоящий перед цифрой в имени ячейки, дает абсолютную ссылку на строку с этим именем. Поэтому если в формуле буква, входящая в имя ячейки, окружена с двух сторон знаками $, это означает, что в формуле дается не относительный, а абсолютный адрес ячейки, т. е. адрес, не подлежащий изменению при протаскивании формулы.
Для ввода в формулу абсолютного адреса ячейки достаточно после ввода ее относительного адреса нажать клавишу <F4>. Если бы в ячейку Н4 была введена формула =Е4/Е10, то ее протаскивание на ячейки Н5:Н9 привело бы к неверному результату. Присвоение ячейке имени с помощью команды Вставка / Имя / Присвоить (Inset, Name, Define) дает другой способ абсолютной адресации ячейки. Например, если бы ячейке Е10 было присвоено имя итого, то в ячейку Н4 можно было бы ввести формулу:
=Е4/Итого
которую затем протаскиваем на диапазон Н4:Н9. Для нахождения места магазина по объему продаж введем в ячейку F4 формулу:
=РАНГ(Е4;$Е$4:$Е$9),
которую протаскиваем на диапазон F4:F9.
С помощью функции частота (frequency) подсчитаем для данного множества суммарных выручек магазинов, сколько значений попадает в интервалы от 0 до 1000, от 1001 до 1100, от 1101 до 1200 и свыше 1201 млн. руб. С этой целью в диапазон ячеек 14:16 введем верхние границы этих интервалов: 1000, 1100 и 1200, соответственно, а в диапазон ячеек J4:J7 введем формулу:
{=ЧАСТОТА(Е4:E9;I4:I6)}
Фигурные скобочки не вводите вручную. После того как Вы наберете функцию нажмите одновременно три клавиши: Ctrl+Shift+Enter и скобки появяться автоматически. Если в последующем Вы решите внести изменения в функцию, то после обязательно, нажмите эти же клавиши, иначе у Вас появиться сообщение об ошибке.
Данная формула выведет в ячейку J4, сколько значений находится в интервале от 0 до 1000, в ячейку J5 — от 1001 до 1 100, в ячейку J6 — от 1 101 до 1 200, в ячейку J7 — сколько значений будет не меньше 1201.
Функция частота возвращает распределение частот в виде вертикального массива. Для данного множества значений и данного множества карманов (интервалов, в математическом смысле) частотное распределение подсчитывает, сколько исходных значений попадает в каждый интервал.
Синтаксис: ЧАСТОТА(массив_данных; массив_карманов).
Аргументы:
массив_данных - массив или ссылка на множество данных, для которых вычисляются частоты; если массив_данных не содержит значений, то функция частота возвращает массив нулей; массив_карманов- массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных; если массив_карманов не содержит значений, то функция частота возвращает количество элементов в аргументе массив данных.
Частоты можно также вычислить, воспользовавшись диалоговым окном Анализ данных (Data Analysis), которое открывается командой Сервис / Анализ данных (Tools, Data Analysis). Средство анализа данных является одной из надстроек Excel. Если в меню Сервис (Tools) отсутствует команда Анализ данных (Data Analysis), то для ее установки нужно выполнить команду Сервис / Надстройки / Analysis ToolPak (Tools, Add-ins, Analysis ToolPak).
После выбора пункта Гистограмма (Histogram) в диалоговом окне Анализ данных (Data Analysis) откроется диалоговое окно Гистограмма (Histogram) (рис.4.8.3).

Рис.4.8.3. Диалоговое окно Гистограмма
В поле Входной интервал (Input Range) введем диапазон Е4:Е9, по которому строим гистограмму. В поле Интервал карманов (Bin Range) введем диапазон I4:I6 со значениями верхних границ интервалов. Гистограмма строится на новом рабочем листе или на текущем листе с указанием диапазона ячеек для результата. В данном случае в поле ввода Выходной интервал (Output Range) введем диапазон L4:L7. На рис. 4.8.4 приведен результат построения гистограммы.

Рис. 4.8.4. Результат построения гистограммы
Второй пример.
В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данного раздела лабораторной работе выполняйте на Листе9. Лист9 переименуйте в «Итоговая выручка».
Рассмотрим еще один пример составления отчетной ведомости, в которой по объему реализованных товаров рассчитывается итоговая выручка (рис.4.8.5).
В ячейки А22:С22 введены стоимости трех различных товаров, а в ячейки B25:D27 — объемы их реализации по месяцам. Для того чтобы вычислить суммарную стоимость реализованных товаров по месяцам, введем в ячейки Е25:Е27 формулу:
{=МУМНОЖ(В25:D27;ТРАНСП(А22:С22)}
Отметим, что данную таблицу можно было заполнить и без привлечения матричных формул. Можно ввести в ячейку Е27 формулу:
=СУММПРОИЗВ(В25:D25;$А$22:$С$22)
и протащить ее на диапазон Е25:Е27. Функция суммпроизв (sumproduct) вычисляет сумму произведений элементов указанных диапазонов ячеек.

Рис. 4.8.5. Расчет итоговой выручки по объему реализации
При построении гистограммы (рис. 4.8.5) в поле ввода первого диалогового окна Мастер диаграмм (Cart Wizard) введите диапазоны А25:А27; Е25:Е27. Напоминаем, что для одновременного выделения диапазонов, которые не примыкают друг к другу, сначала необходимо выделить
Лекция №9. Технология решения экономических задач с применением финансовых функций Excel. Пример отчетной ведомости по расчету просроченных платежей
В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данного раздела лабораторной работе выполняйте на Листе10. Лист10 переименуйте в «Расчет просроченных платежей».
Рассмотрим пример составления отчетной ведомости фирмы, продающей компьютеры, позволяющей определить количество и сумму просроченных клиентами платежей (рис.4.8.6).
Дата переучета введена в ячейку F2 с помощью формулы:
=ДАТА(98;7;31)
Функция дата (date) возвращает дату в числовом формате.
Синтаксис: ДАТА(год; месяц; день)

Рис.4.8.6. Расчет просроченных платежей
Аргументы:
год - число от 1900 до 2078; месяц - число, представляющее номер месяца в году. Если оно больше 12, то прибавляется к первому месяцу указанного года. Например, дата (96; 14;2) возвращает числовой формат даты 2 февраля 1997 года; день - число, представляющее номер дня в месяце. Если оно больше числа дней в указанном месяце, то прибавляется к первому дню указанного месяца
В ячейку Е2 введена формула, определяющая срок просрочки:
=ЕСЛИ(D2=0;$F$2-С2;"") ,
которая протаскивается на диапазон ЕЗ:Е20. В ячейки G8, G9 и G10 введены следующие формулы:
(=СУММ((Е2:Е20>0)*(Е2:Е20<=29)*(В2:В20))} {=СУММ((Е2:Е20>=30)*(Е2:Е20<=39)*(В2:В20))} {=СУММ((Е2:Е20>=40)*(В2:В20))},
вычисляющие суммарные стоимости просроченных оплат сроком до 29 дней, от 30 до 39 дней и свыше 40 дней.
Дадим пояснения к третьей из этих формул. Excel в формуле массива возвращает условие (Е2:Е20>=40) в виде массива, состоящего из 0 и 1, где о стоит на месте ячейки со значением меньше 40 и 1 — на месте ячейки со значением не меньше 40. Следовательно, данная формула вычисляет сумму произведений элементов массива (Е2:Е20>=40) (с единицами в случае просрочки на указанный срок и нулями — в противном случае) и массива В2:В20 (с ценами процессоров). Таким образом, третья формула возвращает суммарную стоимость заказов, просроченных не менее чем на 40 дней.
В ячейки G2, G3 и G4 введены формулы:
{=СУММ((Е2:Е20>0)*(Е2:Е20<=29))}
{=СУММ((Е2:Е20>=30)*(Е2:Е20<40))}
=СЧЁТЕСЛИ(Е2:Е20;">=40"),
вычисляющие количество просроченных оплат сроком до 29 дней, от 30 до 39 дней и свыше 40 дней.
Функция счётесли (COUNTIF) возвращает количество ячеек внутри указанного интервала, удовлетворяющих заданному критерию.
Синтаксис: СЧЁТЕСЛИ(интервал; критерий)
Аргументы:
интервал - интервал, в котором нужно подсчитать ячейки; критерий - критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать (например, критерий может быть выражен следующим образом: 17, "17", ">17","Компьютер")
4.8.1.3. Пример отчетной ведомости по расчету затрат на производствоВ книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данного раздела лабораторной работе выполняйте на Листе11. Лист11 переименуйте в «Затраты на производство».
Рассмотрим пример составления отчетной ведомости по расчету затрат на производство товара (рис.4.8.7).

Рис.4.8.7. Расчет затрат на производство товара
Предположим, что фирма производит CD-диски. Упаковка диска обходится фирме в 1 руб./шт., стоимость материалов — 4 руб./шт. Готовые диски фирма продает по цене 10 руб./шт. Технические возможности фирмы позволяют выпускать до 5 тысяч дисков в день. Оплата труда рабочих является сдельной и зависит от количества выпущенных дисков. За первую тысячу дисков оплата труда рабочих составляет 0,3 руб./шт., за вторую тысячу дисков — 0,4 руб./шт., за третью тысячу дисков — 0,5 руб./шт., за четвертую тысячу дисков — 0,6 руб./шт. и свыше 4000 дисков — 0.7 руб./шт.
Фирме поступил заказ на изготовление 4500 CD-дисков. Необходимо подсчитать суммарные издержки и прибыль от выполнения данного заказа.
Для упрощения чтения формул присвоим с помощью команды Вставка / Имя / Присвоить (Insert, Name, Define) диапазонам D2:D7, E2:E7, F2:F7 и ячейке В1, соответственно, имена:
ДискиШт ОплатаРубШт ОплатаРуб ЗаказШт
Зарплата рабочих, в зависимости от объема выпущенных дисков, находится в диапазоне F2:F7 по формуле:
{=ЕСЛИ(ЗаказШт-1000>ДискиШт;1000*ОплатаРубШт;
ЕСЛИ(ЗаказШт>ДискиШт;(ЗаказШт-ДискиШт)*ОплатаРубШт;0))}
Заметим, что имя диапазона или ячейки удобнее вводить в формулу из диалогового окна Вставка имени (Paste Name), которое открывается командой Вставка / Имя / Вставить (Insert, Name, Paste), что помогает избегать ошибок при вводе с клавиатуры (рис.4.8.8).
Стоимость упаковки и материалов вычисляются в ячейках В6 и В7 по формулам:
=В1*ВЗ
=В1*В4
Зарплата, общие издержки и прибыль вычисляются в ячейках В8, В9 и В 10 по формулам:
=СУММ(ОплатаРуб)
=СУММ(В6:В8)
=В1*В2-В9
Расчет прибыли и затрат на производство закончен.
4.8.2. Индивидуальное задание
Номер варианта определяется согласно списку группы в файле с рейтингом, если у Вас номер более 10, то от номера отнимаете число 10 и получаете номер своего варианта.
При выполнении индивидуального задания Вам необходимо:
Внимательно прочитать условие задачи.
Открыть книгу Задания.xls.
Создать новый лист.
Появившийся Лист7 переименуйте в «Отчетные ведомости».
Сохранить полученные результаты.

Рис.4.8.8. Ввод имени в ячейку из диалогового окна Вставка имени
Задание:
Составить отчетную ведомость реализации товаров п магазинами с месяца А по месяц В, приведенную на рис. 4.8.7.
Таблица 1
Вариант А В п
1 май декабрь 3
2 июнь январь 4
3 июль октябрь 5
4 август январь 6
5 сентябрь декабрь 7
6 октябрь март 8
7 ноябрь март 9
8 декабрь июль 10
9 январь мюль 4
10 февраль август 5
В качестве стоимостей товаров введите произвольные трехзначные числа, а в качестве объемов их реализации — произвольные двузначные числа.
Лекция №9. Технология решения экономических задач с применением финансовых функций Excel. Анализ операции по кредитным займам
Пример расчета эффективности неравномерных капиталовложений с помощью функций ЧПС, ВСД и ПОДБОР ПАРАМЕТРА
Все упражнения в данной лабораторной работе выполняйте в книге ФИНАНСОВЫЙ АНАЛИЗ на Листе2. Лист2 переименуйте в ЧПС, ВСД.
Рассмотрим следующую задачу. Вас просят в долг 10000 руб. и обещают вернуть через год 2000 руб., через два года - 4000 руб., через три года - 7000 руб. При какой годовой процентной ставке эта сделка выгодна?
В приводимом на рис.4.2.1 расчете в ячейку В11 введена формула =ЧПС(В10;В5:В7) (см. рис. 4.2.2)

Рис. 4.2.1 Расчет годовой процентной ставки
Введите представленные на рис. 4.2.2. данные на лист “ЧПС, ВСД” и сравните полученный результат с данными на рис. 4.2.1.

Рис. 4.2.2 Формулы для расчета годовой процентной ставки
Кроме того, для автоматизации составления таблицы в ячейку С6 введена формула:
=ЕСЛИ(В8=1; "год"; ЕСЛИ(И(В8>=2;B8<=4) ; "года"; "лет"))
Первоначально в ячейку В10 вводится произвольный процент, например 3%. После этого выбираем команду Сервис / Подбор параметра (Tools / Goal Seek) заполняем открывшееся диалоговое окно Подбор параметра (Goal Seek), как показано на рис.4.2.3.
5715006032500
Рис. 4.2.3 Диалоговое окно Подбор параметра при расчете годовой процентной ставки
В поле Установить в ячейке (Set Cell) даем ссылку на ячейку В8, в которой счисляется чистый текущий объем вклада по формуле:
=ЧПС(B10;B5:B7)
В поле Значение (То Value) указываем 10000 — размер ссуды. В поле Изменения значения ячейки (By Changing Cell) даем ссылку на ячейку В10, в которой вычисляется годовая процентная ставка. После нажатия кнопки ОК средство подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 руб. Результат вычисления выводится в ячейку В10. В нашем случае годовая учетная ставка равна 11,79%. Вывод: если банки предлагают большую годовую процентную ставку, то предлагаемая сделка не выгодна.
Функция ЧПС (npv) возвращает чистый текущий объем вклада, вычисляемый на основе ряда последовательных поступлений наличных и нормы амортизации. Чистый текущий объем вклада — это сегодняшний объем будущих платежей (отрицательные значения) и поступлений (положительные значения). Например, вам предлагают следующую сделку. У вас берут в долг некоторую сумму денег и предлагают через k1 лет вернуть сумму, равную Рk1, через k2 лет — Рk2 и т. д. и, наконец, через kn лет — Рkn. Кроме данной сделки, у вас есть альтернативный способ использования ваших денег, например, положить их в банк под i процентов годовых. Тогда чистым текущим объемом вклада является та сумма денег, которой вам нужно располагать начальный год, чтобы, положив их в банк под i процентов годовых, получили предлагаемую прибыль. В нашем случае чистый текущий объем вклада равен (2).
(2)
Синтаксис: ЧПС(ставка; 1-е значение; 2-е значение;…..)
Аргументы:
ставка — ставка дисконтирования за один период.
Значение1, значение2, — от 1 до 29 аргументов, представляющих расходы и доходы.
значение1, значение2, … должны быть равномерно распределены во времени, выплаты должны осуществляться в конце каждого периода.
ЧПС использует порядок аргументов значение1, значение2, … для определения порядка поступлений и платежей. Убедитесь в том, что ваши платежи и поступления введены в правильном порядке.
Считается, что инвестиция, значение которой вычисляет функция ЧПС, начинается за один период до даты денежного взноса 1-го значения и заканчивается с последним денежным взносом в списке. Вычисления функции ЧПС базируются на будущих денежных взносах. Если первый денежный взнос приходится на начало первого периода, то первое значение следует добавить к результату функции ЧПС, но не включать в список аргументов.
Если п — это количество денежных взносов в списке значений, Р — j-e значение и i — ставка, то функция ЧПС вычисляется по формуле (3):
(3)
Функция ЧПС связана с функцией ВСД (внутренняя скорость оборота). ВСД — это скорость оборота, для которой ЧПС равняется нулю:
ЧПС(ВСД(…);…)=0.
Функция ВСД (irr) возвращает внутреннюю скорость оборота для ряда последовательных операций с наличными, представленными числовыми значениями. Объемы операций не обязаны быть регулярными, как в случае ренты. Внутренняя скорость оборота — это процентная ставка дохода, полученного от инвестиций, состоящих из выплат (отрицательные значения) и поступлений (положительные значения), которые происходят в регулярные периоды времени.
Синтаксис: ВСД (значения; предположение).
Аргументы:
значения – массив или ссылка на ячейки, содержащие числовые величины, для которых вычисляется внутренняя ставка доходности. Значения должны включать, по крайней мере, одно положительное значение и одно отрицательное значение, для того чтобы можно было вычислить внутреннюю скорость оборота. Функция ВСД использует порядок значений для интерпретации порядка денежных выплат или поступлений, поэтому нужно следить, чтобы значения выплат и поступлений вводились в правильном порядке.
Предположение – величина, о которой предполагается, что она близка к результату ВСД.
Если п + 1 — количество значений в списке, Pj — j-e значение, то ВСД является корнем относительно I (ставки) уравнения (4):
(4)
Для вычисления ВСД Excel использует метод итераций. Начиная со значения прогноз, функция ВСД выполняет циклические вычисления, пока не получит результат с точностью 0,00001. Если функция ВСД не может получить результат после 20 попыток, возвращается значение ошибки #ЧИСЛО!
В большинстве случаев нет необходимости задавать прогноз для вычислений помощью функции ВСД. Если прогноз опущен, то он полагается равным 0,1 (10%).
Если ВСД выдает значение ошибки #ЧИСЛО! Или результат далек от ожидаемого, можно попытаться выполнить вычисления еще раз, но уже с другим значением аргумента прогноз.
Таким образом, задачу, сформулированную в самом начале данного раздела, можно решить также с помощью функции ВСД. Для этого в ячейку D3 надо ввести -10 000 р., а в ячейку D10— функцию ВСД(D3:D7), которая и найдет минимальную годовую учетную ставку.
Пример расчета эффективности капиталовложений с помощью функции ПС
Все упражнения в данной лабораторной работе выполняйте в книге ФИНАНСОВЫЙ АНАЛИЗ на Листе3. Лист3 переименуйте в ПС.
Рассмотрим следующую задачу. Допустим, что у вас просят в долг 10000 руб. и обещают возвращать по 2000 руб. в течение 7 лет. Будет ли выгодна эта сделка при годовой ставке 7%?
В приводимом на рис.4.3.1 расчете в ячейку В5 введена формула
=ПС(В4;В2;-ВЗ)
Рис. 4.3.1 Расчет эффективности капиталовложений
Кроме того, для автоматизации составления таблицы в ячейки С2 и В6 введены формулы
=ЕСЛИ(В2=1;"год";ЕСЛИ(И(В2>=2;В2<=4);"года";"лет"))
=ЕСЛИ (В1<В5; "Выгодно дать деньги в долг"; ЕСЛИ(В5=В1; "Варианты равносильны"; "Выгоднее деньги положить под проценты"))
Функция ПС (pv) возвращает текущий объем вклада на основе постоянных периодических платежей. Функция ПС аналогична функции ЧПС. Основное различие между ними заключается в том, что функция ПС допускает, чтобы денежные взносы происходили либо в конце, либо в начале периода. Кроме того, в отличие от функции ЧПС, денежные взносы в функции ПС должны быть постоянными на весь период инвестиции.
Синтаксис: ПС(ставка; кпер; плт; бс; тип)
Аргументы:
ставка — процентная ставка за период, кпер — общее число периодов платежей по аннуитету, плт— выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты. Обычно выплаты включают основные платежи и платежи по процентам, но не включают других сборов или налогов. бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0), Тип — число 0 или 1, обозначающее, когда должна производиться выплата.
Если тип = 0 и бс = 0, то функция ПС вычисляется по формуле (5):
(5)
где А — выплата;
i — ставка;
n — кпер.
В данном разделе была рассмотрена задача с двумя результирующими функциями: числовой — чистым текущим объемом вклада и качественной, оценивающей, выгодна ли сделка. Эти функции зависят от нескольких параметров. Некоторыми из них вы можете управлять, например, сроком и суммой ежегодно возвращаемых денег. Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Команда Сервис, Сценарии (Tools, Scenarios) предоставляет такую возможность с одновременным автоматизированным составлением отчета. Рассмотрим способ применения этой команды для следующих трех комбинаций срока и суммы ежегодно возвращаемых денег: 6, 2000; 12, 1500 и 7, 1500.
Выберем команду Сервис / Сценарии (Tools, Scenarios). В открывшемся диалоговом окне Диспетчер сценариев (Scenarios Manager) для создания первого сценария нажмите кнопку Добавить (Add) (рис.4.3.2).
В диалоговом окне добавление сценария (Add Scenario) в поле Название сценария (Scenario Name) введите, например пс1, а в поле Изменяемые ячейки (Сhanging Cells) — ссылку на ячейки В2 и ВЗ, в которые вводятся значения параметров задачи (срок и сумма ежегодно возвращаемых денег) (рис. 4.3.3).
После нажатия кнопки ОК появится диалоговое окно Значения ячеек сценария (Scenario Values), в поля которого введите значения параметров для первого сценария (рис.4.3.4).
С помощью кнопки добавить (Add) последовательно создайте нужное число сценариев. После этого диалоговое окно Диспетчер сценариев (Scenarios Manager) будет иметь вид, показанный на рис. 4.3.5.
Рис.4.3.2 Диалоговое окно Диспетчер сценариев

Рис. 4.3.3 Диалоговое окно добавление сценария

Рис. 3.4.4 Диалоговое окно Значения ячеек сценария
21717001587500
Рис. 4.3.5 Вывод сценариев на рабочий лист с помощью диалогового окна Диспетчер сценариев
С помощью кнопки Вывести (Show) можно вывести результаты, соответствующие выбранному сценарию. Нажатие кнопки Отчет (Summary) открывает диалоговое окно Отчет по сценарию (Scenario Summary) (рис.4.3.6).

Рис. 4.3.6 Диалоговое окно Отчет по сценарию
В этом окне в группе Тип отчета (Scenario Type) необходимо установить переключатель в положение Структура (Scenario Summary) или Сводная таблицa (Scenario Pivot Table), а в поле Ячейки результата (Result Cells) дать ссылку на ячейки, где вычисляются значения результирующих функций. После нажатия кнопки ОК создается отчет. На рис.4.3.7 показан отчет по сценарием типа Структура (Scenario Summary).
Рис. 4.3.7 Отчет по сценарию типа Структура
Финансовые функции ПРПЛТ И ОСПЛТ
В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данной лабораторной работе выполняйте на Листе4. Лист4 переименуйте в ПРПЛТ И ОСПЛТ.
Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 100000 руб. на срок 5 лет при годовой ставке 2% (рис 4.4.1.).

Рис. 4.4.1 Вычисление основных платежей и платы по процентам
Ежегодная плата вычисляется в ячейке В4 по формуле:
=ПЛТ(процент; срок; -размер_ссуды),
где ячейки В2, В3 и В5 имеют имена: процент, срок и размер_ссуды, соответственно. Присвоение имени ячейке осуществляется с помощью команды Вставка / Имя / Присвоить (Insert, Name, Define). За первый год плата по процентам в ячейке В8 вычисляется по формуле:
=D7*процент
Основная плата в ячейке С8 вычисляется по формуле:
=ежегодная_плата-В8,
где ежегодная_плата — имя ячейки В4. Остаток долга в ячейке D8 вычисляется по формуле:
=D7-C8
В оставшиеся годы эти платы определяются с помощью протаскивания маркера заполнения выделенного диапазона B8:D8 вниз по столбцам. Отметим, что основную плату и плату по процентам можно было непосредственно найти с помощью функций ОСПЛТ (ррмт) и ПРПЛТ (ipmt), соответственно.
Функция ПРПЛТ возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис: ПРПЛТ (ставка; период; кпер; пс; бс; тип)
Функция ОСПЛТ возвращает величину выплаты за данный период на основе периодических постоянных платежей и постоянной процентной ставки.
Синтаксис: ОСПЛТ(ставка; период; кпер; пс; бс; тип)
Аргументы функций ПРПЛТ и ОСПЛТ:
ставка—процентная ставка за период, период— задает период, значение должно быть в интервале от 1 до «кпер», кпер— общее число периодов выплат годовой ренты, пс — приведенная стоимость, т. е. общая сумма, которая равноценна ряду будущих платежей, бс— требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение бс равно 0.
Тип—число 0 или 1, обозначающее, когда должна производиться выплата.
Функции ПРПЛТ и ОСПЛТ тесно связаны между собой, а именно ПЛПj = i Bj-1, ОСНПj = А - ПЛПj, Bj = Вj-1 - ОСНПj при j [0, n],
где j — номер периода, п — КПЕР, ПЛПj, ОСНПj и Bj — это ПРПЛТ, ОСПЛТ и остаток долга, соответственно, за j -й период, ПЛПо = 0, ОСНПо = 0, Bо — пс, А- величина выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки, вычисляемая с помощью функции ПЛТ.
Введите представленные на рис. 4.4.2. данные на лист “ ПРПЛТ И ОСПЛТ ” и сравните полученный результат с данными на рис. 4.4.1.

Рис. 4.4.2 Функции для вычисления основных платежей и платы по процентам
Финансовые функции БС, КПЕР и СТАВКА
В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данной лабораторной работе выполняйте на Листе5. Лист5 переименуйте в БС, КПЕР, СТАВКА.
Функция БС (fv) вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Функция БС подходит для расчета итогов накоплений при ежемесячных банковских взносах.
Синтаксис: БС(ставка; КПЕР; плт; пс; тип).
Аргументы:
ставка- процентная ставка за период, КПЕР- общее число периодов выплат, плт - величина постоянных периодических платежей, пс - текущее значение, т. е. общая сумма, которую составят будущие платежи, тип - число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — в начале периода.
Если тип = 0 и нз = 0, то функция БС вычисляется по формуле (6):
, (6)
где А — плт,;
i — ставка;
n — КПЕР.
Приведем пример использования функции БС. Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1000 руб. при годовой ставке 6%. Вы собираетесь вкладывать по 100 руб. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?
С помощью формулы:
=БС(6%/12; 12; -100; -1000; 1)
получаем ответ: 2 301.40р.
Функция КПЕР (nper) вычисляет общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис: КПЕР(ставка; плт; пс; БС; тип).
Аргументы:
ставка - процентная ставка за период, плт - величина постоянных периодических платежей, пс - текущее значение, т. е. общая сумма, которую составят будущие платежи, БС - будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент БС опущен, он полагается равным 0 (например, будущая стоимость займа равна 0), тип - число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — то в начале периода.
Если тип = 0 и БС = 0 функция КПЕР вычисляется по формуле (7):
, (7)
где Р — пс;
i — ставка;
А — плт.
Например, если вы берете в долг 1000 руб. при годовой ставке 1% и собираетесь выплачивать по 100 руб. в год, то число выплат вычисляется следующим образом:
=КПЕР(1%; -100; 1000)
В результате получаем ответ: 11.
Функция СТАВКА (rate) вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов. Следует отметить, что функция СТАВКА вычисляет процентную ставку методом итераций, поэтому решение может быть и не найдено. Если после 20 итераций погрешность определения ставки превышает 0,000000 1, то функция СТАВКА возвращает значение ошибки #ЧИСЛО!
Синтаксис: СТАВКА(КПЕР; плт; пс; БС; тип; предположение).
Аргументы:
КПЕР— общее число периодов платежей по аннуитету; плт— регулярный платеж (один раз в период), величина которого остается постоянной в течение всего срока аннуитета. Обычно плт состоит из платежа основной суммы и платежа процентов, но не включает других сборов или налогов. Если аргумент опущен, должно быть указано значение аргумента БС; пс— приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей; БС—требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (например, БС для займа равно 0); тип— число 0 или 1, обозначающее, когда должна производиться выплата; предположение— предполагаемая величина ставки.
Если БС = 0 и тип = 0, функция СТАВКА является корнем уравнения (8):
, (8)
где А — плт;
i — СТАВКА;
n — КПЕР;
Р — пс.
Рассмотрим пример использования функции СТАВКА. Чтобы определить процентную ставку для четырехлетнего займа размером в 8000 руб. с ежемесячной выплатой 200 руб., можно использовать формулу
=СТАВКА(48; -200; 8000)
В результате получаем: месячная (т. к. период равен месяцу) процентная ставка равна 0,77%.
Рассчитайте описанные выше примеры опираясь на данные на рис. 4.5.1. При вводе функций заменяйте конкретные числа в параметрах адресами соответствующих ячеек.

Рис.4.5.1 Финансовые функции БС, КПЕР и СТАВКА
Лекция №10. Технология решения финансовой задачи с применением Excel: Планирование рекламной кампании
Планирование рекламной компании
В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данной лабораторной работе выполняйте на новом листе, которому дайте имя «Рекламная компания».
Прежде всего опишем функции макс (МAХ) и ПОИСКПОЗ (match), которые используются в дальнейшем при рассмотрении примера составления оптимального плана рекламной кампании.
Функция макс возвращает максимальный элемент массива. Функция ПОИСКПОЗ возвращает относительную позицию элемента массива, который соответствует указанному значению. Функция ПОИСКПОЗ используется вместо функций типа просмотр, если нужна позиция элемента, а не сам элемент.
Синтаксис: ПОИСКПОЗ(искомое значение; просматриваемый_массив; тип сопоставления)
Аргументы:
искомое_значение - значение, для которого ищется соответствие в аргументе просматриваемый_массив. Например, когда вы ищете номер телефона в телефонной книге, вы используете фамилию человека как искомое_значение.Оно может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение. Просматриваемый_массив - непрерывный интервал ячеек, который возможно содержит искомые значения. Просматриваемый_массив может быть массивом или ссылкой на массив. Тип_сопоставления - число: -1, 0 или 1. Тип_сопоставления указывает, как Excel сопоставляет искомое значение со значениями в аргументе просматриваемый_массив.
Рассмотрим подробнее возможные варианты:
• Если тип_сопоставления равен 1, то функция поискпоз находит наибольшее значение, которое равно или меньше, чем искомое_значение
(просматриваемый_массив ДОЛЖЕН БЫТЬ упорядочен ПО Возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА).
• Если тип_сопоставления равен 0, то функция поискпоз находит первое значение, которое в точности равно аргументу искомое_значение (просматриваемый_массив может быть расположен в любом порядке).
• Если тип_сопоставления равен -1, то функция поискпоз находит наименьшее значение, которое равно или больше, чем искомое_значение (просматриваемыи_массив должен быть упорядочен по убыванию).
• Если тип_сопоставления опущен, то предполагается, что он равен 1.
Теперь рассмотрим следующий пример. Фирма еженедельно анализирует, как обстоят дела со сбытом одного из видов своей продукции и дает оценку: отличную ("о" — состояние 1), хорошую ("х" — состояние 2) или удовлетворительную ("у" — состояние 3). Необходимо принять решение о целесообразности рекламирования этой продукции с целью расширения ее сбыта.
Приведенные на рис. 4.9.1. в диапазонах B3:D5 и B6:D8 матрицы P1 и P2 определяют переходные вероятности без рекламы и при ее наличии в течение любой недели.
Так, P122=0,5 и P123=0,5 означает, что если в предыдущую неделю сбыт был хорошим, то и без рекламы на текущей неделе с равной вероятностью он останется хорошим или станет удовлетворительным. Соответствующие доходы заданы матрицами R1 и R2 в диапазонах E3:G5 и E6:G8. Отметим, что элементы матрицы R2 учитывают затраты на рекламу. Необходимо спланировать оптимальную рекламную кампанию на последующие три недели.
Для общности предположим, что план составляется на N недель, а число состояний для каждого этапа равно т.
Рис.4.9.1. Планирование рекламной кампании
Пусть fn(i) — оптимальный ожидаемый доход за этапы п, п+1, ..., N при условии, что система находится в состоянии i в начале n-й недели.
Тогда:
,
гдеfN+1(j) = 0 при всех j. Пусть

тогда

.
В ячейку I5 введена формула:
=СУММПРОИЗВ(В5:D5;Е5:G5),
вычисляющая v11, которая протаскивается на диапазон I6:I10 для вычисления v21,...,v32 .
В ячейки диапазона I12:I17 последовательно введены формулы:
=I5
=I8
=I6
=I9
=I7
=I10,
упорядочивающие ожидаемые доходы по следующим парам: первое состояние без рекламы и при ее наличии, второе состояние без рекламы и при ее наличии и третье состояние без рекламы и при ее наличии.
В ячейки диапазона В13:В15 введены формулы:
=МАКС(I12:I13) =МАКС(I14:I15)
=МАКС(I16:I17),
определяющие максимальную ожидаемую прибыль на третьей неделе, если на предыдущей неделе система находилась в первом, втором или третьем состоянии, соответственно. В ячейках диапазона С13:С15 по формулам:
=ПОИСКПОЗ(В13;I12:I13;0)
=ПОИСКПОЗ(В14;I14:I15;0)
=ПОИСКПОЗ(В15;I16:I17;0)
определяется оптимальный вариант действий. Если 1, то деньги на рекламу не тратить, а если 2 — то тратить.
Перейдем ко второй неделе рекламной кампании. В ячейку J5 введена формула:
=I5+МУМНОЖ(В5:D5;$В$13:$В$15),
вычисляющая

которая протаскивается на диапазон J6: J10 для вычисления


В ячейки диапазона J12: J17 введены последовательно формулы:
=J5
=J8
=J6
=J9
=J7
=J10,
упорядочивающие ожидаемые доходы по следующим парам: первое состояние без рекламы и при ее наличии, второе состояние без рекламы и при ее наличии и третье состояние без рекламы и при ее наличии.
В ячейки диапазона D13:D15 введены формулы:
=МАКС(J12:J13) =МАКС(J14:J15)
=МАКС(J16 :J17),
определяющие максимальную ожидаемую прибыль на второй неделе, если на предыдущей неделе система находилась в первом, втором или третьем состоянии, соответственно. В ячейках диапазона Е13:Е15 по формулам:
=ПОИСКПОЗ(D13;J12:J13;0)
=ПОИСКПОЗ(D14;J14:J15;0)
=ПОИСКПОЗ(D15;J16:J17;0)
определяется оптимальный вариант действий. Аналогично проводятся расчеты для первой недели.
Из рис.4.9.1. видно, что на первой и второй неделях необходимо использовать рекламу, не считаясь с состоянием системы, однако, на третьей неделе рекламу следует использовать только тогда, когда система находится во втором или третьем состояниях. Суммарный ожидаемый доход фирмы составит 10736 при отличной оценке, 7923 — при хорошей и 4222 — при удовлетворительной оценке.

Лекция №11. Автоматизированное решение задач статистического анализа. Определение эконометрики. Эконометрические методы анализа данных.
Согласно Большому Энциклопедическому словарю (М.: Изд-во «Большая Российская Энциклопедия», 1997), эконометрика – наука, изучающая конкретные количественные и качественные взаимосвязи экономических объектов и процессов с помощью математических и статистических методов и моделей. Эконометрические методы - это прежде всего методы статистического анализа конкретных экономических данных, естественно, с помощью компьютеров [1]. В нашей стране они пока сравнительно мало известны, хотя именно в России уже полтора столетия активно работает наиболее мощная (в мире) научная школа в области основы эконометрики – теории вероятностей.
Статистические (эконометрические) методы используются в зарубежных и отечественных экономических и технико-экономических исследованиях, работах по управлению (менеджменту). Применение прикладной статистики и других эконометрических методов дает заметный экономический эффект. Например, в США - не менее 20 миллиардов долларов ежегодно только в области статистического контроля качества. В 1988 г. затраты на статистический анализ данных в нашей стране оценивались в 2 миллиарда рублей ежегодно. Согласно расчетам сравнительной стоимости валют на основе потребительских паритетов, эту величину можно сопоставить с 2 миллиардами долларов США. Следовательно, объем отечественного "рынка статистических и эконометрических услуг" был на порядок меньше, чем в США, что совпадает с оценками и по другим показателям, например, по числу специалистов.
 В мировой науке эконометрика занимает достойное место. Об этом свидетельствует, например, присуждение Нобелевских премий по экономике. Их получили эконометрики Ян Тильберген, Рагнар Фриш, Лоуренс Клейн, Трюгве Хаавельмо. В 2000 г. к ним добавились еще двое - Джеймс Хекман и Дэниель Мак-Фадден. Выпускается ряд научных журналов, полностью посвященных эконометрике, в том числе: Journal of Econometrics (Швеция), Econometric Reviews (США), Econometrica (США), Sankhya (Indian Journal of Statistics. Ser.D. Quantitative Economics. Индия), Publications Econometriques (Франция), электронный еженедельник "Эконометрика" (Россия). Публикуются также масса книг и статей в иных изданиях. Действуют национальные и международные эконометрические общества, объединяющие десятки тысяч специалистов.
 А что у нас? База для успешного развития и применения эконометрики есть. Так, только в секции “Математические методы исследования” журнала “Заводская лаборатория” за последние 35 лет напечатано более 1000 статей по высоким статистическим технологиям и их применениям. Однако в нашей стране по ряду причин эконометрика не была сформирована как самостоятельное направление научной и практической деятельности, в отличие, например, от Польши, не говоря уже об англосаксонских странах. Польша стараниями известного экономиста Оскара Ланге и его коллег покрыта сетью эконометрических "институтов" (в российской терминологии - кафедр вузов). В результате - специалистов по эконометрике у нас на порядок меньше, чем в США и Великобритании (Американская статистическая ассоциация включает более 20000 членов).
  В настоящее время в России начинают развертываться теоретические и практические эконометрические исследования, а также положено начало распространению обучения этой дисциплине. Преподавание эконометрики ведется в Московском государственном университете экономики, статистики и информатики (МЭСИ), на экономическом факультете МГУ им. М.В. Ломоносова и еще в нескольких экономических учебных заведениях. Среди технических вузов МГТУ им. Н.Э.Баумана имеет в настоящее время приоритет в развитии и преподавании эконометрики.
 Высокие статистические технологии в эконометрике. Особый интерес представляют эконометрические применения высоких статистических технологий. Речь идет об их применении для анализа конкретных экономических данных, прежде всего в контроллинге).
 Может возникнуть естественный вопрос: зачем нужны высокие статистические технологии, разве недостаточно обычных статистических методов? Исследователи в области эконометрики считают (и доказывают своими теоретическими и прикладными работами), что совершенно недостаточно. Так, многие данные в реальной социально-экономической деятельности, а потому и в информационных системах поддержки принятия решений имеют нечисловой характер, например, являются словами или принимают значения из конечных множеств (выбор происходит из конечного числа градаций). Нечисловой характер имеют и упорядочения, которые дают эксперты или менеджеры, например, выбирая главную цель предприятия, следующую по важности и т.д., сравнивая образцы продукции с целью выбора наиболее подходящего для запуска в серию и др. Значит, для контроллинга нужна статистика нечисловых данных. Далее, многие величины известны не абсолютно точно, а с некоторой погрешностью - лежат в пределах от одной границы до другой. Другими словами, исходные данные - не числа, а интервалы. Это -следствие общеинженерного утверждения: любое измерение проводится с погрешностями. Следовательно, контроллеру нужна статистика интервальных данных. Ниже мы показываем, что мнения людей естественно описывать в терминах теории нечеткости. Значит, контроллеру нужна статистика нечетких данных. Ни статистики нечисловых данных, ни статистики интервальных данных, ни статистики нечетких данных нет и не могло быть в классической статистике. Все это - высокие статистические технологии. Разработанные за последние 10-30 лет, они основаны на последних достижениях прикладной математической статистики. А обычные курсы по общей теории статистики и по классической математической статистике разбирают научные результаты, полученные в первой половине ХХ века.
 Важная часть эконометрики - применение высоких статистических технологий к анализу конкретных экономических данных. Такие исследования зачастую требуют дополнительной теоретической работы по "доводке" статистических технологий применительно к конкретной ситуации. Большое значение для контроллинга имеют не только общие методы, но и конкретные эконометрические модели, например, вероятностно-статистические модели тех или иных процедур экспертных оценок или экономики качества, имитационные модели деятельности организации. И конечно, такие конкретные применения, как расчет и прогнозирование индекса инфляции. Сейчас уже многим специалистам ясно, что годовой бухгалтерский баланс предприятия может быть использован для оценки его финансово-хозяйственной деятельности только с привлечением данных об инфляции. Различные области экономической теории и практики еще далеко не согласованы. При оценке и сравнении инвестиционных проектов принято использовать такие характеристики, как чистый приведенный доход, внутренняя норма доходности, основанные на учете изменения стоимости денежной единицы во времени (учет осуществляется с помощью дисконтирования). А при анализе финансово-хозяйственной деятельности организации на основе данных бухгалтерской отчетности про необходимость дисконтирования "забывают". Эта ошибочная практика объясняется тем, что основы бухгалтерской науки и практики были заложены во времена отсутствия инфляции.
 Бесспорно, что экономисты, менеджеры и инженеры, прежде всего специалисты по контроллингу, должны быть вооружены современными средствами информационной поддержки, в том числе высокими статистическими технологиями и эконометрикой. Очевидно, преподавание должно идти впереди практического применения. Ведь как применять то, чего не знаешь?
 Один раз - в 1990-1992 гг. отечественные специалисты по эконометрике уже обожглись на недооценке необходимости предварительной подготовки тех, для кого предназначены современные компьютерные средства. Всесоюзной статистической ассоциацией и Всесоюзным центром статистических методов и информатики Центрального правления Всесоюзного экономического общества была разработана система диалоговых программных систем обеспечения качества продукции. Их созданием руководили ведущие специалисты страны. Но распространение шло на 1-2 порядка медленнее, чем ожидалось (единицы и десятки продаж вместо сотен и тысяч). Причина стала ясна не сразу. Как оказалось, работники предприятий просто не понимали возможностей разработанных систем, не знали, какие задачи можно решать с их помощью, какой экономический эффект они дадут. А не понимали и не знали потому, что в вузах и после вузов никто их не учил статистическим методам управления качеством. Без такого систематического обучения нельзя обойтись - сложные концепции "на пальцах" за пять минут не объяснишь.
 Есть и противоположный пример - положительный. В середине 1980-х годов в советской средней школе ввели новый предмет "Информатика". И сейчас молодое поколение превосходно владеет компьютерами, мгновенно осваивая быстро появляющиеся новинки, и этим заметно отличается от тех, кому за 30-40 лет. Если бы удалось ввести в средней школе курс вероятности и статистики - а такой курс есть в Японии и США, Швейцарии, Кении и Ботсване, почти во всех странах мира (см. подготовленный ЮНЕСКО сборник докладов [2]) - то ситуация могла бы быть резко улучшена. Надо, конечно, добиться, чтобы такой курс был построен на высоких эконометрических (статистических) технологиях, а не на низких. Другими словами, он должен отражать современные достижения, а не концепции пятидесятилетней или столетней давности.
 Вполне закономерно, что в деятельности российского объединения профессионалов в области контроллинга - "Общества контроллеров" - выделено направление, посвященное применению высоких статистических технологий и эконометрики в контроллинге, а также обучению основам этого направления при подготовке и переподготовке контроллеров.
 Статистические технологии применяют для анализа данных двух принципиально различных типов. Один из них - это результаты измерений различных видов, например, результаты управленческого или бухгалтерского учета, данные Госкомстата и др. Короче, речь идет об объективной информации. Другой - это оценки экспертов, на основе своего опыта и интуиции делающих заключения относительно экономических явлений и процессов. Очевидно, это - субъективная информация. Стабильная экономическая ситуация позволяет рассматривать длинные временные ряды тех или иных экономических величин, полученных в сопоставимых условиях. В подобных условиях данные первого типа вполне адекватны. В быстро меняющихся условиях приходятся опираться на экспертные оценки. Такая новейшая часть эконометрики, как статистика нечисловых данных, была создана как ответ на запросы теории и практики экспертных оценок [3].
 Для решения каких экономических задач может быть полезна эконометрика? Практически для всех, использующих конкретную информацию о реальном мире. Только чисто абстрактные, отвлеченные от реальности исследования могут обойтись без нее. В частности, эконометрика необходима для прогнозирования, в том числе поведения потребителей, а потому и для планирования. Выборочные исследования, в том числе выборочный контроль, основаны на эконометрике. Но планирование и контроль - основа контроллинга [4, 5]. Поэтому эконометрика - важная составляющая инструментария контроллера, воплощенного в компьютерной системе поддержки принятия решений. Прежде всего оптимальных решений, которые предполагают опору на адекватные эконометрические модели. В производственном менеджменте это может означать, например, использование оптимизационных эконометрических моделей типа тех, что применяются при экстремальном планировании эксперимента (они позволяют повысить выход полезного продукта на 30-300%).
 Высокие статистические технологии в эконометрике предполагают адаптацию применяемых методов к меняющейся ситуации. Например, параметры прогностического индекса меняются вслед за изменением характеристик используемых для прогнозирования величин. Таков метод экспоненциального сглаживания. В соответствующем алгоритме расчетов значения временного ряда используются с весами. Веса уменьшаются по мере удаления в прошлое. Многие методы дискриминантного анализа основаны на применении обучающих выборок. Например, для построения рейтинга надежности банков можно с помощью экспертов составить две обучающие выборки - надежных и ненадежных банков. А затем с их помощью решать для вновь рассматриваемого банка, каков он - надежный или ненадежный, а также оценивать его надежность численно, т.е. вычислять значение рейтинга.
 Один из способов построения адаптивных эконометрических моделей - нейронные сети [6]. При этом упор делается не на формулировку адаптивных алгоритмов анализа данных, а - в большинстве случаев - на построение виртуальной адаптивной структуры. Термин "виртуальная" означает, что "нейронная сеть" - это специализированная компьютерная программа, "нейроны" используются лишь при общении человека с компьютером. Методология нейронных сетей идет от идей кибернетики 1940-х годов. В компьютере создается модель мозга человека (весьма примитивная с точки зрения физиолога). Основа модели - весьма простые базовые элементы, называемые нейронами. Они соединены между собой, так что нейронные сети можно сравнить с хорошо знакомыми экономистам и инженерам блок-схемами. Каждый нейрон находится в одном из заданного множества состояний. Он получает импульсы от соседей по сети, изменяет свое состояние и сам рассылает импульсы. В результате состояние множества нейтронов изменяется, что соответствует проведению эконометрических вычислений.
 Нейроны обычно объединяются в слои (как правило, два-три). Среди них выделяются входной и выходной слои. Перед началом решения той или иной задачи производится настройка. Во-первых, устанавливаются связи между нейронами, соответствующие решаемой задаче. Во-вторых, проводится обучение, т.е. через нейронную сеть пропускаются обучающие выборки, для элементов которых требуемые результаты расчетов известны. Затем параметры сети модифицируются так, чтобы получить максимальное соответствие выходных значений заданным величинам.
 С точки зрения точности расчетов (и оптимальности в том или ином эконометрическом смысле) нейронные сети не имеют преимуществ перед другими адаптивными эконометрическими системами. Однако они более просты для восприятия. Надо отметить, что в эконометрике используются и модели, промежуточные между нейронными сетями и "обычными" системами регрессионных уравнений (одновременных и с лагами). Они тоже используют блок-схемы, как, например, универсальный метод моделирования связей экономических факторов ЖОК [1].
 Заметное место в математико-компьютерном обеспечении принятия решений в контроллинге занимают методы теории нечеткости (по-английски - fuzzy theory, причем термин fuzzy переводят на русский язык по-разному: нечеткий, размытый, расплывчатый, туманный, пушистый и др.). Начало современной теории нечеткости положено работой Л.А.Заде  1965г., хотя истоки прослеживаются со времен Древней Греции [3,7] Это направление прикладной математики получило бурное развитие. К настоящему времени по теории нечеткости опубликованы тысячи книг и статей, издается несколько международных журналов (больше половины - в Китае и Японии), постоянно проводятся международные конференции. В области теории нечеткости выполнено достаточно много как теоретических, так и прикладных научных работ, практические приложения дали ощутимый технико-экономический эффект.
 Основоположник рассматриваемого научного направления Лотфи А. Заде рассматривал теорию нечетких множеств как аппарат анализа и моделирования гуманистических систем, т.е. систем, в которых участвует человек. Его подход опирается на предпосылку о том, что элементами мышления человека являются не числа, а элементы некоторых нечетких множеств или классов объектов, для которых переход от "принадлежности" к "непринадлежности" не скачкообразен, а непрерывен. В настоящее время методы теории нечеткости используются почти во всех прикладных областях, в том числе при управлении качеством продукции и технологическими процессами.
 Нечеткая математика и логика - мощный элегантный инструмент современной науки, который на Западе и на Востоке (в Японии, Китае) можно встретить в программном обеспечении десятков видов изделий - от бытовых видеокамер до систем управления вооружениями. В России он был известен с начала 1970-х годов. Однако первая монография российского автора по теории нечеткости [7] была опубликована лишь в 1980 г. В дальнейшем раз в год всесоюзные конференции собирали около 100 участников - по мировым меркам немного.
 При изложении теории нечетких множеств обычно не подчеркивается связь с вероятностными моделями. В нашей стране в середине 1970-х годов установлено [3,7], что теория нечеткости в определенном смысле сводится к теории случайных множеств, хотя эта связь и имеет, возможно, лишь теоретическое значение. В США подобные работы появились лет на пять позже.
 Профессионалу в области контроллинга полезны многочисленные интеллектуальные инструменты анализа данных, относящиеся к высоким статистическим технологиям и эконометрике.
 
Лекция №12. Парная регрессия и корреляция.
Решение типовой задачи в MS Excel
Парная регрессия и корреляция.
2.1 Постановка задачи.
По имеющимся данным наблюдений за совместным изменением двух параметров и необходимо определить аналитическую зависимость , наилучшим образом описывающую данные наблюдений.
2.2 Понятие линейной регрессии.
Функция , задающая среднее значение переменной QUOTE , при условии, что независимая переменная приняла фиксированное значение, называется функцией (линейной) регрессии.
2.3 Оценка параметров модели.
Для оценки параметров линейной регрессии используется метод наименьших квадратов (МНК). МНК позволяет получить такие оценки параметров, при которых сумма отклонений фактических значений результативного признака от теоретических значений при тех же значениях фактора минимальна, т.е.
.
QUOTE В случае линейной регрессии параметры и QUOTE находятся из следующей системы нормальных уравнений МНК:

Можно воспользоваться готовыми формулами, которые вытекают из этой системы:

2.4 Интерпретация коэффициентов уравнения регрессии.
Коэффициент при факторной переменной QUOTE имеет следующую интерпретацию: он показывает, на сколько изменится в среднем величина при изменении фактора на 1 единицу измерения.
Коэффициент – свободный член в уравнении регрессии показывает значения переменной при . Этот коэффициент не всегда имеет экономическую интерпретацию.
2.5 Оценка тесноты связи.
В качестве меры для тесноты линейной связи между переменными используется коэффициент корреляции. Приведем формулу выборочного коэффициента корреляции переменных и QUOTE :
.
Коэффициент корреляции будет положителен, если отклонения переменных и от своих средних значений, как правило, имеют одинаковый знак, и отрицательным – если разные знаки. Коэффициент корреляции является безразмерной величиной. Его величина меняется от -1 в случае строгой линейной отрицательной связи до +1 в случае строгой линейной положительной связи. Близкая к 0 величина коэффициента корреляции говорит об отсутствии линейной связи между переменными, но не об отсутствии связи между ними вообще.
2.6 Оценка качества построенной модели (адекватности эмпирическим данным).
2.6.1 Коэффициент детерминации. Для оценки качества построенной модели регрессии можно использовать коэффициент детерминации . Коэффициент детерминации может быть вычислен по формуле:
.
С другой стороны, для парной линейной регрессии верно равенство:
.
При близости значения коэффициента детерминации к 1 говорят, что уравнение регрессии статистически значимо и фактор оказывает сильное воздействие на результирующий признак .
При анализе модели парной линейной регрессии по значению коэффициента детерминации можно сделать следующие предварительные выводы о качестве модели:
Если , то будем считать, что использование регрессионной модели для аппроксимации зависимости между переменными и статистически необоснованно.
Если , то использование регрессионной модели возможно, но после оценивания параметров модель подлежит дальнейшему многостороннему статистическому анализу.
Если , то будем. считать, что у нас есть основания для использования регрессионной модели при анализе поведения переменной .
2.6.2 Средняя ошибка аппроксимации.
Другой показатель качества построенной модели –– среднее относительное отклонение расчетных значений от фактических или средняя ошибка аппроксимации:
.
Построенное уравнение регрессии считается удовлетворительным, если значение не превышает 10% – 12% .
3. Пример.
По 21 региону страны изучается зависимость розничной продажи телевизоров () от среднедушевого денежного дохода в месяц ().
Номер региона Среднедушевой денежный доход в месяц, тыс. руб., Объем розничной продажи телевизоров, тыс. шт.,
1 2 28
2 2,4 21,3
3 2,1 21
4 2,6 23,3
5 1,7 15,8
6 2,5 21,9
7 2,4 20
8 2,6 22
9 2,8 23,9
10 2,6 26
11 2,6 24,6
12 2,5 21
13 2,9 27
14 2,6 21
15 2,2 24
16 2,6 24
17 3,3 31,9
18 3,9 33
19 4 35,4
20 3,7 34
21 3,4 31
Необходимо найти зависимость, наилучшим образом отражающую связь между переменными и .
Рассмотрим вопрос применения модели линейной регрессии в этой задаче.
Построим поле корреляции, т.е. нанесем исходные данные на координатную плоскость. Для этого воспользуемся, например, возможностями MS Excel 2003.
Подготовим таблицу исходных данных.

Нанесем на координатную плоскость исходные данные:

Характер расположения точек на графике дает нам основание предположить, что искомая функция регрессии линейная: . Для оценки коэффициентов уравнения регрессии необходимо составить и решить систему нормальных уравнений ( ).
По исходным данным рассчитываем необходимые суммы:
Номер региона
1 2 28 56 4 784
2 2,4 21,3 51,12 5,76 453,69
3 2,1 21 44,1 4,41 441
4 2,6 23,3 60,58 6,76 542,89
5 1,7 15,8 26,86 2,89 249,64
6 2,5 21,9 54,75 6,25 479,61
7 2,4 20 48 5,76 400
8 2,6 22 57,2 6,76 484
9 2,8 23,9 66,92 7,84 571,21
10 2,6 26 67,6 6,76 676
11 2,6 24,6 63,96 6,76 605,16
12 2,5 21 52,5 6,25 441
13 2,9 27 78,3 8,41 729
14 2,6 21 54,6 6,76 441
15 2,2 24 52,8 4,84 576
16 2,6 24 62,4 6,76 576
17 3,3 31,9 105,27 10,89 1017,61
18 3,9 33 128,7 15,21 1089
19 4 35,4 141,6 16 1253,16
20 3,7 34 125,8 13,69 1156
21 3,4 31 105,4 11,56 961
Сумма 57,4 530,1 1504,46 164,32 13926,97
Составляем систему уравнений:

Имеем систему линейных алгебраических уравнений, которая может быть решена, например, по формулам Крамера. Для этого вычислим следующие определители:



Тогда, согласно теореме Крамера,

Получаем уравнение регрессии:

Величина коэффициента регрессии означает, что увеличение среднедушевого месячного дохода на 1 тыс. руб. приведет к увеличение объема розничной продажи в среднем на 7 540 телевизоров. Коэффициент в данном случае не имеет содержательной интерпретации.
Оценим тесноту линейной связи между переменными и качество построенной модели в целом.
Для оценки тесноты линейной зависимости рассчитаем коэффициент детерминации. Для этого необходимо провести ряд дополнительных вычислений.
Прежде всего, найдем выборочное среднее по формуле:
.
Для рассматриваемого примера имеем:

Теперь произведем расчет остальных вспомогательных величин:
Номер региона
1 2 28 19,76 8,24 67,89 2,76 7,60
2 2,4 21,3 22,75 -1,45 2,11 -3,94 15,55
3 2,1 21 20,51 0,49 0,24 -4,24 18,00
4 2,6 23,3 24,25 -0,95 0,90 -1,94 3,77
5 1,7 15,8 17,52 -1,72 2,95 -9,44 89,17
6 2,5 21,9 23,50 -1,60 2,56 -3,34 11,17
7 2,4 20 22,75 -2,75 7,57 -5,24 27,49
8 2,6 22 24,25 -2,25 5,04 -3,24 10,52
9 2,8 23,9 25,74 -1,84 3,39 -1,34 1,80
10 2,6 26 24,25 1,75 3,08 0,76 0,57
11 2,6 24,6 24,25 0,35 0,13 -0,64 0,41
12 2,5 21 23,50 -2,50 6,24 -4,24 18,00
13 2,9 27 26,49 0,51 0,26 1,76 3,09
14 2,6 21 24,25 -3,25 10,54 -4,24 18,00
15 2,2 24 21,26 2,74 7,53 -1,24 1,54
16 2,6 24 24,25 -0,25 0,06 -1,24 1,54
17 3,3 31,9 29,48 2,42 5,86 6,66 44,32
18 3,9 33 33,96 -0,96 0,93 7,76 60,17
19 4 35,4 34,71 0,69 0,47 10,16 103,17
20 3,7 34 32,47 1,53 2,34 8,76 76,69
21 3,4 31 30,23 0,77 0,60 5,76 33,14
Сумма 57,4 530,1     130,68   545,73
Здесь столбец «» – это значения , рассчитанные с помощью построенного уравнения регрессии, столбцы «» и – это столбцы, так называемых, «остатков»: разностей между исходными значениями , и рассчитанными с помощью уравнения регрессии , а также их квадратов, а в последних двух столбцах – разности между исходными значениями , выборочным средним , а также их квадраты.
Для вычисления коэффициента детерминации воспользуемся формулой ( ):

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

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

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

Нажав на ОК, получаем еще одну прямую на диаграмме, которая совпадает с построенными ранее точками линии регрессии:

Сплошная черная линия на диаграмме – это линия регрессии, рассчитанная средствами Excel. Линия регрессии, построенная нами ранее, совпала с данной линией регрессии. Нетрудно убедиться, что уравнение регрессии и коэффициент детерминации тоже совпадают с полученными ранее вручную.
Найдем теперь среднюю ошибку аппроксимации для оценки погрешности модели. Для этого нам потребуется вычислить еще ряд промежуточных величин:
Номер региона
1 2 28 19,76 8,24 0,29
2 2,4 21,3 22,75 -1,45 0,07
3 2,1 21 20,51 0,49 0,02
4 2,6 23,3 24,25 -0,95 0,04
5 1,7 15,8 17,52 -1,72 0,11
6 2,5 21,9 23,50 -1,60 0,07
7 2,4 20 22,75 -2,75 0,14
8 2,6 22 24,25 -2,25 0,10
9 2,8 23,9 25,74 -1,84 0,08
10 2,6 26 24,25 1,75 0,07
11 2,6 24,6 24,25 0,35 0,01
12 2,5 21 23,50 -2,50 0,12
13 2,9 27 26,49 0,51 0,02
14 2,6 21 24,25 -3,25 0,15
15 2,2 24 21,26 2,74 0,11
16 2,6 24 24,25 -0,25 0,01
17 3,3 31,9 29,48 2,42 0,08
18 3,9 33 33,96 -0,97 0,03
19 4 35,4 34,71 0,69 0,02
20 3,7 34 32,47 1,53 0,05
21 3,4 31 30,23 0,77 0,02
Здесь столбец «» – это значения , рассчитанные с помощью построенного уравнения регрессии, столбец «» – это столбец так называемых «остатков»: разностей между исходными значениями , и рассчитанными с помощью уравнения регрессии , и, наконец, последний столбец «» – это вспомогательный столбец для вычисления элементов суммы по формуле ( ). Просуммируем теперь элементы последнего столбца и разделим полученную сумму на 21 – общее количество исходных данных:
.
Переведем это число в проценты и запишем окончательное выражение для средней ошибки аппроксимации:
.
Итак, средняя ошибка аппроксимации оказалась около 8%, что говорит о небольшой погрешности построенной модели. Данную модель, с учетом неплохих характеристик ее качества, вполне можно использовать для прогноза – одной из основных целей эконометрического анализа. Предположим, что среднедушевой месячный доход в одном из регионов составит 4,1 тыс. руб. Оценим, каков будет уровень продаж телевизоров в этом регионе согласно построенной модели? Для этого необходимо выбранное значение фактора подставить в уравнение регрессии ( ):
(тыс. руб.),
т.е. при таком уровне дохода, розничная продажа телевизоров составит, в среднем, 35 480 телевизоров.

Лекция №13. Автоматизированные решения оптимального выбора процесса управления. Решение линейных оптимизационных задач
Процессоры электронных таблиц
Решение уравнений и задач оптимизации
Для решения задач оптимизации широкое променение находят различные средства Excel.
В этом разделе рассмотрим команды:
Подбор параметров для нахождения значения, приводящего к требуемому результату.
Надстройку Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям;
Диспетчер сценариев для создания и оценки наборов сценариев «что – если» с несколькими вариантами исходных данных.
Подбор параметров
Основной командой для решения оптимизационных задач в Excel является команда Сервис/Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату.
Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка
Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:
формула для расчета;
пустая ячейка для искомого значения;
другие величины, которые используются в формуле.
Ссылка на пустую ячейку должна обязательно присутствовать в формуле, так как именно она является переменной, значение которой ищет Excel.
Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи.
Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью  команды Сервис/Параметры, вкладка Вычисления)
Оптимизация с помощью команды Подбор параметров выполняется так:
1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными (B1), которые могут понадобиться при вычислениях. Например, необходимо определить количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн.
Рис. 1.
2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле "Установить в ячейке" в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра.
Рис. 2.
3. Введите в текстовое поле Значение число, соответствующее объему продаж - 10000. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение.
Рис. 3.
После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу  по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.
Команда Поиск решения
Для решения сложных задач, требующих применения линейного и нелинейного программирования, а также методов исследования операций применяется надстройка - Поиск решения. Чтобы использовать надстройку Поиск решения не обязательно знать методы программирования и исследования операций, но необходимо определять, какие задачи можно решать этими методами.
Пользователь должен уметь с помощью диалоговых окон надстройки Поиск решения правильно сформулировать условия задачи, и если решение существует, то “Поиск решения” отыщет его. В основе надстройки  лежат итерационные методы.
В том случае, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо воспользоваться надстройкой Поиск решения. “Поиск решения” позволяет использовать одновременно большое количество  изменяемых ячеек (до 200) и  задавать ограничения для изменяемых ячеек.
Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:
Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным, какому-то конкретному значению.
Формула в этой целевой ячейке содержит ссылки на ряд изменяемых ячеек. Поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые бы обеспечили оптимальное значение для формулы в целевой ячейке.
Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.
Постановка задачи
Первым шагом при работе с командой Поиск решения является создание специализированного листа. Для этого необходимо создать целевую ячейку, в которую вводится основная формула.
Кроме того, лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Формула в целевой ячейке должна опираться в вычислениях на значения переменных ячеек.
После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе:
Выделите на листе целевую ячейку, в которую введена формула.
Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения (Рис. 3.). Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».
Перейдите к полю "Изменяя ячейки" и введите переменные ячейки листа.
Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.
Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.
Чтобы отобразить найденное решение в ячейках листа, установите переключатель "Сохранить найденное решение" и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.
Рис. 4.
Диспетчер сценариев «что – если»
При работе с командами Подбор параметра и Поиск решения не существует удобного способа сравнения результатов вычислений – при каждом изменении данных предыдущее значение пропадает.
Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что – если». Командой Сервис/Сценарии можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчеты.
Создание сценария
Сценарием называется модель «что – если», в которую входят переменные ячейки, связанные одной или несколькими формулами. Перед созданием сценария необходимо спроектировать лист так, чтобы на нем была хотя бы одна формула, зависящая от ячеек, которые могут принимать различные значения. Например, может возникнуть потребность в сравнении лучшего и худшего сценариев.
Создание сценариев происходит следующим образом:
Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.
Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.
Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.
Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов.
Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.
Рис. 5.
Рис. 6.
Просмотр сценария
Excel сохраняет сценарии вместе с листом текущей книги, и просмотр их командой Сервис /Сценарии возможен только при открытии данного листа. Просмотр сценария выполняется следующим образом:
Выполните команду Сервис/Сценарии. Открывается окно диалога.
Выберите из списка сценарий для просмотра.
Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.
Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что – если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.
Создание отчетов по сценарию
Сравнивать различные сценарии можно, переходя от сценария к сценарию с помощью кнопки показать в окне диалога Диспетчер сценариев, но иногда возникает необходимость в создании отчета с обобщенной информацией о различных сценариях листа.
Эту задачу можно выполнить с помощью кнопки Отчет в окне диалога Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги.
Создание отчета по сценарию происходит следующим образом:
Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.
Нажмите кнопку Отчет. Открывается окно диалога Отчет по сценарию, в котором предлагается выбрать ячейки, входящие в отчет, а также его тип. Отчет типа структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчет сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.
Лекция №14. Решение задач линейного программирования с помощью MS Excel. Решение задачи планирования производства
Задача линейного программирования
Линейные модели являются одним из наиболее используемых классов математических моделей. Модель общей задачи линейного программирования применяют для решения задач на смеси, использования сырья, определения оптимального плана выпуска изделий и ряда других. Программирование в данном понятии имеет смысл планирования. Линейное же означает, что ищется экстремум целевой функции при линейных ограничениях, представленных в виде неравенств.
В математическую модель входят три составляющие:
ЦФ – целевая функция или критерий оптимизации, показывает в каком смысле решение должно быть оптимальным. Целевая функция может стремиться к max или min. При ограничении на сырье целевая функция будет стремиться к min, то есть необходимо определить такой выпуск продукции, при котором расход сырья был бы min. Если назначить ограничение на прибыль, то целевая функции будет стремится к max, то есть необходимо определить такой выпуск продукции, при котором прибыль была бы максимальной.
ОГР – ограничения, устанавливающие зависимости между переменными.
ГРУ – граничные условия, показывающие, в каких пределах могут быть искомые переменные.
Решение задачи, удовлетворяющее всем граничным условиям и всем ограничениям, называется - допустимым. Если математическая модель задачи составлена правильно, то задача имеет целый ряд допустимых решений. Критерий выбирается человеком, который принимает решение. С помощью критерия можно оценивать качества желательные (прибыль, производительность) и не желательные (затраты, расход материалов). Тогда в первом случае ЦФ – max, а во втором случае ЦФ – min.
Линейное программирование применяется часто и эффективно при решении следующих задач: задач о составлении смеси, цель которых заключается в выборе наиболее экономичной смеси ингредиентов (руды, нефти, пищевых продуктов и др.) при учете ограничений на физический или химический состав смеси и на наличие необходимых материалов; задач производства, целью которых является подбор наиболее выгодной производственной программы выпуска одного или нескольких видов продукции при использовании некоторого числа ограниченных источников сырья; задач распределения, цель которых состоит в том, чтобы организовать доставку материалов от некоторого числа источников к некоторому числу потребителей так, чтобы оказались минимальными либо расходы по этой доставке, либо время, затраченное на нее (транспортная задача).
Рассматриваются и комбинированные задачи (например, в случае, когда какой-то товар производится в разных местах, задачи производства и распределения объединяются в единую модель).
Наиболее распространенным методом решения задач линейного программирования является так называемый симплекс-метод. В простейшем случае, когда число переменных равно двум, удобен простой и наглядный графический метод. На ЭВМ задачи линейного программирования решаются в системах поддержки принятия решений (СППР). СППР, использующие экономико-математические методы, реализованы в специальных программах (Mathcad, Excel).
Решение задачи планирования производства
Рассмотрим следующую задачу планирования производства.
Небольшая фабрика выпускает два типа красок: для внутренних (I) и наружных (Е) работ. Продукция обоих видов поступает в оптовую продажу. Для производства красок используются два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн, соответственно. Расходы продуктов А и В на 1 т соответствующих красок приведены в табл. 4.10.1. Изучение рынка сбыта показало, что суточный спрос на краску I никогда не превышает спроса на краску Е более чем на 1 т. Кроме того, установлено, что спрос на краску I никогда не превышает 2 т в сутки. Оптовые цены одной тонны красок равны: 3000 руб. для краски Е и 2000 руб. для краски I. Какое количество краски каждого вида должна производить фабрика, чтобы доход от реализации продукции был максимальным?
Таблица 4.10.1. Исходные данные задачи
Исходный продукт
Расход исходных продуктов на тонну краски, т
Максимально возможный запас, т
краска Е
Краска I
А
В
1
2
2
1
6
8
Для решения этой задачи необходимо построить математическую модель. Процесс построения модели можно начать с ответа на следующие три вопроса:
1. Для определения, каких величин строится модель (т.е. каковы переменные модели)?
2. В чем состоит цель, для достижения которой из множества всех допустимых значений переменных выбираются оптимальные?
3. Каким ограничениям должны удовлетворять неизвестные?
Задача планирования производства в общем виде записывается следующим образом:
;
;
dj <= Xj <= Dj;
i=1,m; j=1,n;
где F – целевая функция; Cj - прибыль получаемая от реализации единицы продукции j-го типа, Хj – количество выпускаемой продукции j – го типа, - норма расхода i- ресурса для выпуска единицы продукции j-го типа, -количество располагаемого ресурса i – го вида, Dj – максимально возможный выпуск j- вида продукции , dj – минимально возможный выпуск продукции j- вида .
В нашем случае фабрике необходимо спланировать объем производства красок так, чтобы максимизировать прибыль. Поэтому переменными являются:
XI— суточный объем производства краски I и XE — суточный объем производства краски Е. Суммарная суточная прибыль от производства XI краски I и XE краски Е равна
Z = 3000*XE + 2000*XI .
Целью фабрики является определение среди всех допустимых значений XE и XI таких, которые максимизируют суммарную прибыль, т. е. целевую функцию Z.
Перейдем к ограничениям, которые налагаются на XE и XI. Объем производства красок не может быть отрицательным, следовательно:
XE, XI >=0
Расход исходного продукта для производства обоих видов красок не может превышать максимально возможный запас данного исходного продукта, следовательно:
XE +2 XI <=6,
2 XE + XI <=8.
Кроме того , ограничения на величину спроса на краски таковы:
XI - XE <=1,
XI <=2.
Таким образом, математическая модель данной задачи имеет следующий вид:
Максимизировать
Z=3000* XE +2000* XI
При следующих ограничениях:
XE +2* XI <=6,
2* XE + XI <=8,
XI - XE <=1,
XI <=2,
XI, XE >=0
Заметим, что данная модель является линейной, т. к. целевая функция и ограничения линейно зависят от переменных.
На листе книги создадим таблицу Исходные данные и отведем диапазон ячеек под решение .(рисунок 4.10.1).
В ячейку D13 введем функцию цели =E4*C11+E5*D11
В ячейки D16: D19 соответственно:
=C11+C4*D11
=B5*C11+D11
=D11-C11
=D11
В ячейки С11, D11 введем начальные значения, т.е. нулевые значения.
После этого выберем команду Сервис, Поиск решения (Tools, Solver) и заполним открывшееся диалоговое окно Поиск решения (Solver), как показано на рисунке 4.10.2.

Рисунок 4.10.1 - Диапазоны, отведенные под исходные данные

Рис. 4.10.2 - Диалоговое окно Поиск решениязадачи о планировании производства красок
После нажатия кнопки Выполнить (Solve) открывается окно Результаты поиска решения (Solver Results), которое сообщает, что решение найдено (рисунок 4.10.3).
Результаты расчета нашей задачи (оптимальный план производства и соответствующая ему прибыль) представлены на рисунке 1.1. Как видно из рисунка, оптимальным является производство 3,33 т краски Е и 1,33 т. краски I в сутки. Этот объем производства принесет фабрике 12666,66 тыс. руб. прибыли.

Рисунок 4.10.3 - Диалоговое окно Результаты поиска решения
Элементы диалогового окна Поиск решения. В поле Установить целевую ячейку диалогового окна Поиск решения дается ссылка на ячейку с функцией, для которой будет находится максимум, минимум или заданное значение. В задаче о производстве красок в поле Установить целевую ячейку вводится D13.
Тип взаимосвязи между решением и целевой ячейкой задается путем установки переключателя в группе Равной. Для нахождения максимального или минимального значения целевой функции этот переключатель ставится в положение Максимальному значению или Минимальному значению соответственно. Для нахождения значения целевой функции, заданного в поле группы Равной, переключатель ставится в положение значению. В нашей задаче о красках установим переключатель в положение Максимальному значению, т.к. планируем производство, обеспечивающее максимальную прибыль.
В поле Изменяя ячейки указываются ячейки, которые должны изменяться в процессе поиска решения задачи, т. е. ячейки отведенные под переменные задачи. В нашем случае в поле Изменяя ячейки введем диапазон C11:D11.
Ограничения, налагаемые на переменные задачи, отображаются в поле Ограничения. Средство поиска решений допускает ограничения в виде равенств, неравенств, а так же позволяет ввести требование целочисленности переменных. Ограничения добавляются по одному. Для ввода ограничений нажмите кнопку Добавить в диалоговом окне Поиск решения и в открывшемся диалоговом окне Добавление ограничений заполните поля (рисунок 2.10.4).

Рисунок 4.10.4 - Диалоговое окно Добавление ограничений
В поле Ссылка на ячейку введите левую часть ограничения D16, а в поле Ограничение - правую часть , в нашем примере D4. с помощью раскрывающегося списка вводится тип соотношения между левой и правой частями ограничения. В нашем примере это >=.Таким образом, требование неотрицательности переменных задано.
Нажмите кнопку Добавить в диалоговом окне Добавление ограничения и введите последовательно всю группу ограничений, налагаемых на переменные. Нажатие кнопки ОК завершает ввод ограничений. Обратите внимание на то, что ограничения удобнее задавать в виде диапазонов.
Теперь нажмите Параметры в диалоговом окне Поиск решения, для того чтобы проверить, какие параметры заданы для поиска решений (рисунок 4.10.5).

Рисунок 4.10.5 - Диалоговое окно Параметры поиска решения
Рассмотрим элементы этого окна:
Поле Максимальное время (Max Time) служит для ограничения времени, отпускаемого на поиск решения задачи
Поле Предельное число итераций (Iteration) служит для ограничения числа промежуточных вычислений
Поля Относительная погрешность (Precision) и Допустимое отклонение
(Tolerance) служат для задания точности, с которой ищется решение. Рекомендуется после нахождения решения с величинами данных параметров, заданными по умолчанию, повторить вычисления с большей точностью и меньшим допустимым отклонением и сравнить с первоначальным решением. Использование подобной проверки особенно рекомендуется для задач с требованием целочисленности переменных.
Флажок Линейная модель (Assume Linear model) служит для поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи. В случае нелинейной задачи этот флажок должен быть сброшен, в случае линейной задачи — установлен, т. к. в противном случае возможно получение неверного результата
Флажок Показывать результаты итераций (Show Iteration Results) служит для приостановки поиска решения и просмотра результатов отдельных итераций.
Флажок Автоматическое масштабирование (Use Automatic Scaling) служит для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине, например, при максимизации прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.
Группа Оценка (Estimates) служит для выбора метода экстраполяции.
Группа Производные (Derivatives) служит для выбора метода численного дифференцирования.
Группа Метод (Search) служит для выбора алгоритма оптимизации.
Лекция №15. Решение транспортной задачи помощью MS Excel
Решение транспортной задачи в Excel
Решение транспортной задачи в Excel — условное название для методов нахождения решения транспортной задачи с применением электронных таблиц Microsoft Excel. Надстройка «Поиск решения» в Microsoft Excel позволяет напрямую находить оптимальное решение транспортной задачи. В MS Excel также можно организовать поиск начального допустимого плана и пошаговое решение транспортной задачи симплеккс-методом.
Рабочий лист
При решении в качестве начального этапа можно подготовить рабочий лист как показано на рисунке:
A B C D E F G
1 2 3 Цены перевозки, руб./кг Потребитель 1 Потребитель 2 Потребитель 3 Потребитель 4 4 Поставщик 1 2 3 2 4 5 Поставщик 2 3 2 5 1 6 Поставщик 3 4 3 2 6 7 8 Объёмы перевозки, кг: Потребитель 1 Потребитель 2 Потребитель 3 Потребитель 4 Запасы
9 Поставщик 1 20 - 10 - 30 30
10 Поставщик 2 0 30 - 10 40 40
11 Поставщик 3 - - 20 - 20 20
12 20 30 30 10 90
13 Спрос: 20 30 30 10 90 14 Целевая функция: 170
Формулы в таблице
Ячейки рядом с серыми (на изображении — строка 12 и столбец F) содержат формулы суммирования по строке и столбцу.
F9: =СУММ(B9:E9)
F10: =СУММ(B10:E10)
F11: =СУММ(B11:E11)
B12: =СУММ(B9:B11)
C12: =СУММ(C9:C11)
D12: =СУММ(D9:D11)
E12: =СУММ(E9:E11)
В отмеченной красным цветом итоговой ячейке использована формула =СУММПРОИЗВ(B4:E6;B9:E11), которая вычисляет сумму произведений цены на объем для каждого из путей перевозки груза. Другие ячейки на этом рабочем листе формул не содержат.
Изменение числа поставщиков и потребителей
Если число строк и столбцов (поставщиков и потребителей) не совпадает с примером, их добавляют, "не задевая" первую и последнюю колонку из диапазона, чтобы не испортились настройки. Например, чтобы добавить еще одну колонку, добавляйте ее после столбца B, а нового поставщика — после строки Поставщик 1 в двух местах), после чего нужно «размножить» соответствующие формулы и оформление из имеющихся ячеек на вновь вставленные.
Ввод исходных данных
В отмеченные зеленым цветом клетки затем надо ввести цены, в отмеченные серым — объем спроса и предложения. Желтые ячейки (объемы перевозки) при вызове надстройки «Поиск решения» программа посчитает сама.
Сбалансированность задачи
Сумма спроса и сумма запасов (в этом примере = 90) должны совпадать, в противном случае требуется ввести фиктивного отправителя или поставщика с нулевыми ценами доставки.
Установка надстройки
Чтобы начать расчет, нужно убедиться, что в меню Сервис есть пункт меню «Поиск решения»:

Если его там нет, то нужно зайти в пункт «Надстройки» и установить соответствующую надстройку:

Выполнение вычислений
Затем необходимо вызвать пункт меню «Сервис — Поиск решения»:

В этом примере наложено целочисленное ограничение, если оно не требуется, то его можно убрать (выделить в настройках строку со словом «целое» и нажать кнопку «Удалить»).
Для начала поиска решения нужно нажать кнопку «Выполнить», затем в появившемся окне — «Сохранить найденное решение».
Округление
В итоговом решении могут оказаться числа наподобие 19.99999 или 1E-6 — для их форматирования до чисел с нужной разрядностью следует использовать кнопку «Формат с разделителями» на панели инструментов.
Настройки для предотвращения зацикливания
По нажатию кнопки Параметры доступно окно с параметрами поиска решения:

В частности, задано ограничение на время исполнения алгоритма и на число итераций (повторений) цикла во избежание зацикливания, при необходимости длительных вычислений можно выставить значения до 32767. Если алгоритм впал в бесконечный цикл, то есть транспортная задача вырожденная, то можно исправить ситуацию, прибавив к объемам груза у потребителей в исходной задаче небольшие числа, такие как 0.0001. Чтобы при этом задача не оказалась разбалансированной, сумму этих небольших чисел надо прибавить к объему груза одного из поставщиков.
Итоговое решение
Общая стоимость транспортировки содержится в отмеченной красным цветом ячейке «Целевая функция». Чем меньше это значение, тем меньше будет затрачено денег на перевозку всего груза.
Пример решения транспортной задачи в среде MS Excel
Задача. Пусть производство продукции осуществляется на 4-х предприятиях А1, А2, А3, А4 а затем развозится в 5 пунктов потребления этой продукции B1, B2, B3, B4, B5. На предприятиях Ai (i = 1, 2, 3, 4) продукция находится соответственно в количествах ai (условных единиц). В пункты Bj (j = 1, 2, 3, 4,5) требуется доставить bj единиц продукции. Стоимость перевозки единицы груза (с учетом расстояний) из Ai в Bj определена матрицей .Предприятия могут выпускать в день 235, 175, 185 и 175 единиц продукции. Пункты потребления готовы принимать ежедневно 125, 160, 60, 250 и 175 единиц продукции. Стоимость перевозки единицы продукции (в у. е.) с предприятий в пункты потребления приведена в таблице.
Требуется минимизировать суммарные транспортные расходы по перевозке продукции.
Решение.Необходимо выполнить следующее:1. Установить, является ли модель транспортной задачи, заданная таблицей, сбалансированной.2. Разработать математическую модель задачи.3. Найти минимальную стоимость перевозок, используя надстройку «Поиск решения» в среде MS Excel.
Решение.
1. Выполним проверку сбалансированности математической модели задачи. Модель является сбалансированной, так как суммарный объем производимой продукции в день равен суммарному объему потребности в ней:
235+175+185+175=125+160+60+250+175
(При решении этой задачи не учитываются издержки, связанные со складированием и недопоставкой продукции).

2. Приступим к построению математической модели поставленной задачи. Неизвестными будем считать объемы перевозок.Пусть хij – объем перевозок с i-го пункта поставки в j-й пункт потребления. Суммарные транспортные расходы – это функция , где сij – стоимость перевозки единицы продукции с i-го предприятия в j-й пункт потребления .
Неизвестные в этой задаче должны удовлетворять следующим ограничениям:• Объемы перевозок не могут быть отрицательными, т. е. ;• Поскольку модель сбалансирована, то вся продукция должна быть вывезена с предприятий, а потребности всех пунктов потребления должны быть полностью удовлетворены, т. е. и .
Итак, имеем следующую задачу ЛП:найти минимум функции: при ограничениях:, ,
3. Приступаем к решению задачи на компьютере.3.1. Откроем новый рабочий лист Excel.3.2. В ячейки B3:F6 стоимость перевозок единицы груза.3.3. В ячейках B16:F16 укажем формулы для расчета суммарной потребности продукции для j-го пункта, в ячейках G12:G15 – формулы суммарного объема производства i-го предприятия.
3.4. В ячейки B18:F18 заносим значения потребности продукции соответствующего пункта потребления, в ячейки H12:H15 заносим значения объема производства соответствующего предприятия.3.5. В ячейку B20 занесем формулу целевой функции.3.6. Выполним команду Сервис → Поиск решения. Откроется диалоговое окно Поиск решения. Если такой команды во вкладке Сервис нет, то следует подключить эту надстройку перейдя по Сервис → Надстройки, и поставив галочку напротив нужной, т.е. Поиск решения.3.7. В поле Установить целевую ячейку указываем ячейку, содержащую оптимизируемое значение. Установим переключатель Равный в положение минимальному значению.3.8. В поле Изменяя ячейки мышью зададим диапазон подбираемых параметров $B$12:$F$15.3.9. В поле Ограничения введем необходимые ограничения и нажмем на кнопку Добавить, затем Выполнить.


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

Оптимальность решения можно проверить, экспериментируя со значениями ячеек $B$12:$F$15.

Приложенные файлы

  • docx 15838123
    Размер файла: 3 MB Загрузок: 0

Добавить комментарий