Структура и назначение программ в области информационных технологий (стр. 22 из 29)

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

Функция – встроенное средство расчетов в Excel. Функции используют аргументы – конкретные значения которые вводятся в специальном порядке. В Excel существует мастер функций который можно ввести режим функции.

Аргумент – для вычисления большинства функций в excel используются вводимые из вне числа или переменные – аргументы.

Чтобы формула работала корректно аргументы вводят в строго определенном порядке. некоторые функции имеют как обязательные так и необязательные аргументы формула – запись действий 999999999 уже решений заданий как правило состоящая из локальных функций и аргументов (бывают простые и рекурсивные).

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

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

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

При дублировании формулы копируется не формула а ее действие.

Абсолютные ссылки – на конкретный адрес ячейки (со знаком $ (при $A $I)).

Их можно смешивать оставляя фиксированным только один адрес ($B3 C$3) по умолчанию все ссылки – абсолютные

67. Построение и применение таблиц данных.

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

Таблицы даных Excel нужно организовывать специальным образом.

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

В Excel можно строить таблицы данных: с одной переменной – позволяет изменять значения единственного аргумента функции с двумя переменными – можно изменять значения двух аргументов функции

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

При создании таблицы данных используется окно «Таблица подстановка» (из меню Данные). Перед тем нужно сконструировать таблицу данных. После чего выделить таблицу и выбрать команду Данные ->Таблица подстановки.

Для таблицы данных с одним элементом нужно указать столбец (строку) с подставляемыми значениями. Для таблицы данных нужно указать как строки так и столбцы.

68. Построение и применение таблиц данных с одной переменной.

Компоновка таблицы данных (ТД)

ТД с одной переменной конструируют либо по вертикали столбец либо по горизонтали строку. Если таблица сконструирована как последующий столбец то в диалоговом окне «Таблицы подстановки» необходимо использовать поле «Подставлять значение по строкам в» после чего происходит вывод значений находящихся в левом и результат в правом.

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

Для примера рассмотрим чистый текущий объем вклада по 5 инвестиционным проектам с использованием функции НПЗ(ставка, значение_1, значение 2…). Значения аргументов – платежи (можно ввести до 29 значений). С5:С14, … G5-G14–представлено движение наличности по 5 инвестиционным проектам. Первоначальные значения потоков имеют отрицательный знак что говорит о вложении денег. Функция НПЗ позволяет сравнивать эти потоки поступлений и расходов и выбрать более прибыльный проект. Текущее значение проекта обозначается процентной ставкой. В ячейку В17 заносим процентную ставку (В17=6,48%).

В ячейке В20:В29 вводятся ставки для каждого из проектов (от 6 до 8,25). Можно создать вручную блок формул. Вводим формулы в ячейки С20:С29 =НПЗ(В20, С5:С14):НПЗ(В29, G5:G14). Чтобы создать таблицу с расчетом 1 нужен НПЗ различных проектов. Введем в С19-

G19 и используем формулу С19=НПЗ($B$17, C5:C14);

Затем щелкнув на кнопке в правом нижнем углу ячейки С19 перетащить область ячейки до G19 чтоб заполнить поле формулой.

Выделим область В19-В29 и выберем Таблицу подстановки. В поле «постанавливать значения по строкам в» вставить адрес В17 и Ок. Указанная зона заполнится формулами итераций.

69. Построение и применение таблиц данных с двумя переменными ТД с двумя переменными – позволяет изменять два аргумента функции.

Рассмотрим пример ее использование. Учет амортизации имущества за период. При этом за остаток берется начальная и конечная стоимость. За основу берется функция ДОБ, которая имеет следующие параметры

ДОБ(стоимость, остаточная стоимость, время эксплуатации, период, месяц)

Стоимость – стоимость имущества

Остаточная стоимость – после амортизации

Время эксплуатации –число периодов амортизации

Период – период времени для которой нужна амортизация

Месяц –к-во месяцев в первом году

Введем в ячейки следующие параметры В2:В5=(1000,100,10,1) Е3=ДОБ(В2,В3,В4,В5,)

Формула рассчитала только цену обесценивания стоимости строения за период амортизации. Если введем в Е5 – Е15 числа с 1 до 10, то эти значения будут использоваться в качестве переменной периода. В ячейках F4 по J4 введем значения соответствующие первоначальным стоимостям. Таким образом есть две переменные (F4:J4) стоимости и период (С5:С14). В ячейки Е4 –функция ДОБ (В2,В3,В4,В5) Выделим область (Е4:J14)в которой будут отражены результаты. Выберем сервис подстановки и определим параметр «подставлять по столбцам» (=В2) и «подставлять по строкам» (В5), нажать ОК, Таблица заполнила данными. В каждой ячейке будет отображена величина амортизации в зависимости от периода и стоимости.

70. Использование способа «подбор параметра».

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

подбор параметра с помощью графика, диаграмы

Подбор параметра

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

Чтобы использовать процедуру подбора параметра, необходимо загрузить надстройку Поиск решения (Solver). При отсутствии файла Solver.xla на жестком диске его необходимо установить.

1. Выберите ячейку, содержащую формулу, параметр которой необходимо подобрать. 2. Выберите команду Сервис > Подбор параметра. В поле Установить в ячейке будет находитьcя ссылка на ячейку, указанную в шаге 1.

3. В поле Значение введите значение, которое должно быть достигнуто путем подбора. В поле Изменяя значение ячейки введите ссылку на ячейку, которая содержит изменяемый параметр.

4. Нажмите кнопку ОК. 5. После того как решение найдено, нажмите кнопку ОК.

71. Использование надстройки “Поиск решения”.

Процедура поиска решения (solver) предназначена для решения задач по оптимизации, при этом значения исходных ячеек изменяются, оставляя другие данные в указанных пределах. При необходимости получить конкретное значение в определенной ячейке, изменяя всего один параметр, удобнее использовать процедуру подбора параметра (см. раздел «Подбор параметра»).

Для использования процедуры поиска решения на рабочем листе необходимо сначала загрузить надстройку Поиск решения (Solver). При отсутствии файла Solver.xla на жестком диске его необходимо установить.

72. Циклические ссылки и итерации.

Циклические ссылки (ЦС) появляются в ячейках рабочего листа, когда происходит обращение прямо или косвенно к самой ячейке.

ЦС используются для решения систем уравнений, которые возникаю при решении различных инженерных задач.

Например для решения системы уравнений

Х=9+У/2

Н=1+2Х

Необходимо в ячейках ввести следующее:

В2=9+В3/2

В3=1+2*В2

После завершения ввода в В3 появится диалоговое окно предупреждающее о ЦС.

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

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

При изменении числа итераций и погрешности вычислений пересчитать значения можно с помощью клавиши F9.

При решении некоторых систем уравнений, процессы решения которых не сходятся: В2=2+В3^2

В3=КОРЕНЬ(2+В2)

В этом примере количество итераций, указанное в параметрах, будет выполнено и вычисления прекратятся. Хоть и результат не будет найден. При расхождении ЦС значения хранимые в ячейках могут постоянно меняться. Поэтому нужно учитывать это в дальнейших расчетах.

73. Использования сценариев „Что-если".

Сценарии являются частью блока задач, который иногда называют инструментами анализа "что-если" (Анализ «что-если». Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе. Например, изменение процентной ставки, используемой в таблице амортизации для определения сумм платежей.). Сценарий — это набор значений, которые Microsoft Excel сохраняет и может автоматически подставлять на листе. Сценарии можно использовать для прогноза результатов моделей и систем расчетов. Существует возможность создать и сохранить на листе различные группы значений, а затем переключаться на любой из этих новых сценариев для просмотра различных результатов. Редактор сценариев. Используется для добавления текста, изменения тегов HTML и редактирования сценариев VBScript (Microsoft Visual Basic Scripting Edition) на странице доступа к данным. В редакторе сценариев также можно просматривать страницы в том виде, который они будут иметь при отображении в веб-обозревателе.