Использование функций ВПР() и ГПР()

ВПР() – «Вертикальный ПРосмотр»
ГПР() – «Горизонтальный Просмотр»

Для начала создадим документ следующего вида:

Для этого:

Создайте документ Microsoft Excel.
Для ячеек A1:F1 выполните команду Объединить и центрировать. Введите заголовок.
Введите заголовки столбцов. Выделите ячейки A2:F2 и выполните команду Формат/Ячейки. Во вкладке Выравнивание установите флажок на Переносить по словам.
Введите данные в таблицу.
Для ячеек A20:B20 выполните команду Объединить и центрировать и введите заголовок таблицы.
В ячейки A22:A26 введите заголовки таблицы.
В ячейку B22 введите название любой страны.

Переходим к использованию функций ВПР() и ГПР(). Эти функции имеют следующий синтаксис:

ВПР(искомое_значение; диапазон_таблицы; номер_столбца;_интервальный_просмотр)

ГПР(искомое_значение; диапазон_таблицы; номер_строки;_интервальный_просмотр)

искомое_значение – это значение, которое требуется найти в первом столбце/строке таблицы;
диапазон_таблицы – это ячейки, составляющие информационную таблицу;
номер_столбца/номер_строки – номер столбца/строки, в котором требуется найти искомое значение;
интервальный_просмотр – (является необязательным) Это логическое значение, которое определяет, нужно ли, чтобы функция ВПР()/ВКР() искала точное значение или приближенное соответствие. Если этот аргумент опущен, или имеет истинное значение, то функция возвращает приблизительное соответствующее значение (если точное соответствие не найдено).

Вернемся к нашему примеру и рассмотрим использование функции ВПР():

В ячейку B22 введите формулу =ВПР(B21;A3:F17;2;ЛОЖЬ). У вас отобразится информация о выбранной вами стране, а именно, её площадь.
Аналогичным образом «узнайте» другую информацию об этой стране.
В ячейку B21 введите название другой страны. В ячейках B22:B26 немедленно отобразится информация об этой стране.

Таблицы подстановок

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

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

Создадим документ следующего вида:

Для этого:

Создайте документ Microsoft Excel.
Выделите ячейки A1:C1 и выполните команду Объединить и центрировать. Введите заголовок таблицы.
В ячейку B3 введите заголовок и выполните команду Вставка/Примечание.
В ячейку B4 и В6 введите нужные заголовки.
В ячейку C3 введите значение. И выполните команду Формат/Ячейки во вкладке Число выберите Денежный формат - $Английский(Канада).
Введите курс доллара. (Формат ячейки F1 - Денежный р.)
В ячейку C6 введите формулу =C3*C4*F1.
Выделите ячейки C6:C15 и установите формат Денежный (р).

Переходим к созданию таблицы подстановки. Формулы, используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода. Ячейка ввода – это ячейка, в которую подставляются значения из таблицы данных. В нашем случае, ячейкой ввода является ячейка C4, а диапазон ячеек B6:C15 – таблицей данных - она содержит формулы и значения подстановки. Итак,

Выделите диапазон ячеек B6:C15.
Выполните команду Данные/Таблица подстановки. На экране откроется диалоговое окно Таблица подстановки. В нашем случае аргументы функции составляют столбец, поэтому мы должны вводить ссылку на ячейку ввода в поле Подставлять значения по строкам.
В поле Подставлять значение по строкам укажите ячейку $C$4. Это ячейка ввода, в которую последовательно вводились бы значения, если бы не было средств таблицы постановки.
Нажмите кнопку OK.

После этого таблица заполнится требуемыми значениями.

Анализ «что - если»

Формулы и виды адресации в таблицах

Это процесс изменения ячеек и анализ влияния этих изменений на результат изменения формул на листе. К блоку задач «если - что» относиться подбор параметра. Когда желаемый результат известен, но не известны значения, которые требуется ввести для получения этого результата можно воспользоваться средством «Подбор параметра».

Создадим таблицу:

Для этого:

Введем названия строк.
Выделим ячейки от А1 до В4.
Выберем пункт меню Формат/ячейки.
Во вкладке Граница сделайте внутренние и внешние.
Выделите столбец А.
В пункте меню Формат выберете пункт Ячейки/автоподбор ширины.
Введите числа в В1 и В2.
Выделите ячейку В3 и выполните команду Формат/Ячейки во вкладке Число выберите Процентный.
В ячейке В4 введите формулу: =(В1*В3)*В2+В1.

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

В пункте меню Сервис выберем пункт Подбор параметра, выйдет окошко Подбор параметра.
В строке Установить в ячейке введем В4.
В строке Значение нужное значение. В нашем примере 1000000.
В строке Изменяя значение в ячейке введем В3. Нажмем кнопку OK.
Выйдет окошко Результат подбора. Нажмем OK.

Работа со сценариями

Сценарии являются частью блока задач, которые называются инструментами анализа «что - если». Сценарий – это инструмент, позволяющий моделировать различные физические, экономические и другие виды задач. Он представляет собой зафиксированный в памяти компьютера набор значения ячеек рабочего листа.

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

Для того, чтобы получить таблицу исходных данных:

Введем названия исходных данных и значения;
Для того чтобы не загромождать отчет лишними таблицами, присвоим имена:

Выделите диапазон А2:В7.
В пункте меню Вставка выберете пункт Имя, затем Создать.
Откроется окно Создать имена. Установите в нем флажок в столбце слева.
Нажмите Ok.

Теперь, если выделить какую-либо ячейку, то в поле Имена, находящемся справа от строки формул, будет выведено ее имя

Теперь создадим таблицу результатов:

В столбец D введем то, что нам надо высчитать.
В ячейку E3 введем формулу: =2*ПИ()*КОРЕНЬ(B3/B5).
В ячейку E4 введем формулу: =B6*B3/COS(B6).
В ячейку E5 введем формулу: =КОРЕНЬ(B5/B3).
В ячейку E6 введем формулу: =E4*COS(E5*B7+B6).
В ячейку E7 введем формулу: =E4*E5*SIN(E5*B7+B6).
В ячейку E8 введем формулу: =B4*B5*B3*(1-COS(E6/B3)).
В ячейку E9 введем формулу: =B4*(E7*E7)/2.
В ячейку E10 введем формулу: =E8+E9.

Теперь создадим сценарий. Его можно создать двумя способами:

Раскрывающийся список Сценарий на панели инструментов.
При помощи Диспетчера сценариев.

Рассмотрим использование списка Сценарий:

Установим список Сценарий на панели инструментов:

В пункте меню Сервис выберем пункт Настройка.
Откроется диалоговое окно, в котором откроем вкладку Команды и выберем в левом списке элемент Сервис.
В правом списке найдем элемент Сценарий и перетащим его на панель инструментов.
Нажмите Ok.

Введите в ячейки исходных данных некоторые значения.
Выделите ячейки B3-B7.
Установите указатель в области списка Сценарий и введите имя сценария.
Нажмите Enter.

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

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

Теперь рассмотрим Диспетчер сценариев. Этот способ более медленный, но довольно эффективный.

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

Чтобы перейти от одного сценария к другому при помощи диспетчера сценариев надо:

В пункте меню Сервис выберете пункт Сценарий.
Откроется окно Диспетчер сценариев, в списке окна выберете имя нужного сценария и дважды щелкните кнопкой мыши.
Нажмите кнопку Закрыть.

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

Проще всего изменить его с помощью списка. Для этого:

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

При помощи Диспетчера сценариев это делается так:

В пункте меню Сервис выберете пункт Сценарий.
Выйдет диалоговое окно Диспетчер сценариев.
Выберете изменяемый сценарий из списка двойным щелчком или с помощью кнопки Изменить.
Откроется диалоговое окно, в котором можно изменить имя сценария а также диапазон ячеек.
Нажмите кнопку Ok.
Откроется диалоговое окно Значения ячеек сценария. В нем можно изменить значения ячеек.
Нажмите кнопку Ok.
Откроется окно Диспетчер сценариев.
Нажмите кнопку Закрыть.

Иногда в задачах возникает необходимость исследовать зависимость результата от некоторого подмножества исходных данных, при этом значения остальных исходных данных фиксировано. Можно рассматривать всевозможные комбинации исходных данных. Для этого в раскрытом списке сценариев создают различные сценарии. Например: нормальное ускорение, большое ускорение, груз1, груз2, время1, время2. Такие сценарии создаются обычным способом, но в качестве диапазона изменяемых ячеек для каждого из них взято по одной ячейке.

В результате работы со сценариями мы получаем большие документы, но иногда их надо распечатать на принтере. Для таких случаев Excel предлагает создавать отчеты. Существует два вида отчетов:

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

Рассмотрим сначала итоговый отчет:

В пункте меню Сервис выберем пункт Сценарий.
В диалоговом окне Диспетчер сценариев. нажмите кнопку Отчет.
Откроется диалоговое окно Отчет по сценарию.
Установите флажок около типа отчета – структура.
При необходимости измените диапазон ячеек в поле Ячейки результата.
Нажмите Ok.

Рассмотрим отчет в виде сводной таблицы:

В пункте меню Сервис. выберем пункт Сценарий.
В диалоговом окне Диспетчер сценариев нажмите кнопку Отчет.
Откроется диалоговое окно Отчет по сценарию.
Установите флажок около типа отчета – сводная таблица.
При необходимости измените диапазон ячеек в поле Ячейки результата.
Нажмите Ok.

Со временем может накопиться очень много сценариев их все можно объединить. Для этого:

В пункте меню Сервис выберем пункт Сценарий.
В окне Диспетчер сценариев нажмите кнопку Объединить.
Откроется диалоговое окно Объединение сценариев.
Выберете в списке Книга рабочую книгу, содержащую необходимый сценарий.
В списке Лист выберете нужный лист.
Нажмите кнопку Ok.

Поиск решений

Традиционными задачами, решаемыми с помощью настройки «Поиск решений» являются:

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

Все эти задачи имеют три общих свойства:

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

Как же строятся ограничения в задачах? Под ограничениями понимают соотношения вида А1<=B1, A1=A2, A1>=0, по крайней мере одна из ячеек в соотношении должна зависеть от переменных, в противном случае это соотношение не влияет на решение задачи.

Замечание: правильная формулировка ограничений является самой ответственной частью постановки задачи.

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

Для того чтобы надстройка Поиск решения загружалось сразу при запуске Excel:

В пункте меню Сервис выберете пункт Надстройки.
Выйдет диалоговое окно Надстройки. В списке Доступные надстройки установите флажок напротив надстройки Поиск решений.
Нажмите кнопку Ok.

Составим таблицу вида:

Для этого:

В ячейки C3:G5 вводим константы.
В ячейку B3 вводим формулу: =СУММ(C3:G3).
В ячейку B4 вводим формулу: =СУММ(C4:G4).
В ячейку B5 вводим формулу: =СУММ(C5:G5).
В ячейку C7 вводим формулу: =СУММ(C3:C5).
В ячейку D7 вводим формулу: =СУММ(D3:D5).
В ячейку E7 вводим формулу: =СУММ(E3:E5).
В ячейку F7 вводим формулу: =СУММ(F3:F5).
В ячейку G7 вводим формулу: =СУММ(G3:G5).
В ячейки C8:G8 вводим константы.
В ячейки B10:B12 вводим константы.
В ячейки C10:G12 вводим константы.
В ячейку C14 вводим формулу: =C3*C10+C4*C11+C5*C12.
В ячейку D14 вводим формулу: =D3*D10+D4*D11+D5*D12.
В ячейку E14 вводим формулу: =E3*E10+E4*E11+E5*E12.
В ячейку F14 вводим формулу: =F3*F10+F4*F11+F5*F12.
В ячейку G14 вводим формулу: =G3*G10+G4*G11+G5*G12.
В ячейку B14 вводим формулу: =СУММ(C14:G14).
В следующие ячейки мы введем ограничения:

В ячейку С25 введем ограничение: B3:B5<=B10:B12. Общее количество доставленных товаров с завода не должно превышать его производственной мощности.
В ячейку C28 введем ограничение: C7:G7>=C8:G8. Количество товаров на складе должно удовлетворять потребностям соответствующего рынка.
В ячейку C31 введем ограничение: C3:G5>=0. Количество поставляемых товаров неотрицательно.

Теперь надо найти оптимальное решение:

Выделите оптимизируемую ячейку. (В данном примере это В14).
В пункте меню Сервис выберете пункт Поиск решения. Откроется диалоговое окно Поиск решения.
В поле Установить целевую ячейку будет находиться имя оптимизируемой ячейки. При необходимости имя ячейки можно изменить.
Установите флажок около какого-нибудь значения в поле Равной (Максимальному значению – поиск максимального значения для целевой ячейки, минимальному значению – поиск минимального значения для целевой ячейки, значению – поиск заданного значения для целевой ячейки).
В поле Изменяя ячейки определите диапазон ячеек, которые изменяются.
Определите ограничения. Около поля Ограничения нажмите кнопку Добавить. Откроется диалоговое окно Добавление ограничения, в которое вводятся неравенства ограничений. Если хотите ввести несколько ограничений, после введения первого ограничения, в том же окне, нажмите кнопку Добавить. После введения всех ограничений нажмите кнопку Ок. Снова выведется окно Поиск решения.
Нажмите кнопку Выполнить. По окончанию поиска решения откроется диалоговое окно Результаты поиска решения.
Выберете переключатель Сохранить найденное значение или Восстановить исходные значения.
Нажмите кнопку Ok.

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

В пункте меню Сервис выберете пункт Поиск решения.
В диалоговом окне Поиск решения, в списке имеющихся ограничений выделите нужное ограничение и нажмите кнопку Изменить.
Откроется диалоговое окно Изменение ограничений. Измените ограничения и нажмите кнопку Ok.
Снова откроется диалоговое окно Поиск решения. Нажмите кнопку Выполнить.

Чтобы удалить ограничение, надо выделить неравенство и нажать кнопку Удалить.

Дата добавления: 2018-10-27; просмотров: 775; Мы поможем в написании вашей работы!

Поделиться с друзьями:

⇐ Предыдущая 1 2 3 4 5 6 789 Следующая ⇒

Мы поможем в написании ваших работ!