Типы зависимостей, используемые для построения линии тренда в MS Excel

 

Тип зависимости	Уравнение	Аргументы
Линейная	y=ax+b	a – угол наклона b – координата пересечения оси абсцисс
Логарифмическая	y=c ln x + b	ln – натуральный логарифм b и c - константы
Степенная	y=cxb	b и c - константы
Экспоненциальная	y=cebx	e – основание натурального логарифма b и c - константы
Полиномиальная	y=b +c1x + c2x2 + … + c6x6	b, c1 – c6 - константы

 

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

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

Операция консолидации данных запускается с помощью команды Консолидация меню Данные. В открывшемся одноименном диалоговом окне следует указать консолидируемые диапазоны ячеек и вид операции.

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

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

 

Рис. 10.33. Операция Консолидация (суммирование данных таблиц)

 

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

В качестве примера для консолидации рассмотрим обработку данных об объемах продаж фирмы «ООО Рассвет» за первый квартал текущего года.

Выберем команду Данные/Консолидация и в появившемся окне установим список диапазонов, показанный на рис. 10.34.

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

Для автоматического вычисления итогов таблицы предназначена операция, которая вызывается командой Итоги меню Данные. Для применения этой операции данные должны быть представлены в форме списка^[5]. Следует задать

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

 

Рис. 10.34. Вызов команды Сортировка и фильтр в программе MS Excel 2007

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

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

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

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

В ячейки G5 и H5 должны быть введены формулы для определения коэффициента наращения и суммы выплат. Эти формулы выглядят следующим образом:

 

Ячейка	Формула
G5	=(1+C8)^C6
H5	=C10*C4

 

Формулы идентичны содержащимся в ячейках С10 и С13. В эти формулы в дальнейшем должны быть подставлены значения процентной ставки (ячейка С8) из списка.

 

Рис. 10.35. Список значений процентных ставок и результат применения операции Подстановка

 

При построении таблиц подстановки пользователь может задавать несколько формул. Например, формулы можно дополнительно ввести в ячейки I5, J5 и т.д.

Формулы для вычислений должны быть обязательно указаны в первой строке, поскольку значения в списке могут быть считаны только в направлении возрастания номеров строк (столбцов).

Для построения таблицы подстановки следует выделить диапазон ячеек исходных значений и диапазон ячеек с формулами (F5:H19). После этого необходимо выбрать в меню Данные команду Таблица подстановки

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

· Оптимизация перевозок грузов;

· Оптимизация распределения ресурсов (от распределения производственных мощностей для выпуска нескольких видов товаров с различной прибыльностью до оптимизации состава стада крупного рогатого скота для наиболее прибыльного производства молока и мяса);

· Оптимизация расхода материалов.

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

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

Рис. 10.36. Вызов инструмента Поиск решения с помощью панели быстрого доступа программы MS Excel 2007