Аппроксимация ряда эмпирической формулой.
Такая задача часто возникает при обработке экспериментальных данных. Построим вначале модельный ряд: возьмём простую аналитическую зависимость (например, 
), на которую наложим шум, и попытаемся эту зависимость восстановить. В столбце A поместим ряд аргументов – случайные числа в интервале (–1; 1). Запишем в A1 формулу =2*СЛЧИС() – 1 (функция СЛЧИС генерирует случайные числа в интервале (0; 1), коэффициент 2 растягивает этот интервал, и минус 1 сдвигает). Распространим формулу на 100 ячеек протаскиванием вниз, и зафиксируем этот результат (Копирование диапазона, Специальная вставка с отметкой Значения на это же место). Массив, имитирующий зависимость , разместим в столбце B. В ячейку B1 запишем формулу: = A1^2+0,5*СЛЧИС()–0,25. Слагаемое –0,25 восстанавливает средний нулевой уровень последовательности. Также протащим эту формулу на 100 ячеек и зафиксируем.
Проще всего выполнить аппроксимацию графически. Построим стандартным образом точечный график. При выделенной диаграмме в головном меню появляется пункт Диаграмма, активизировав который, нажмём Добавить линию тренда, и выберем тип аппроксимации Полиномиальная, отметим 2 (степень полинома). Теперь наведём курсор на появившуюся линию тренда и вызовем контекстное меню (правой клавишей мыши); выберем Формат линии тренда и на вкладке Параметры отметим Показывать уравнение на диаграмме, OK. Для лучшей видимости уравнение можно перетащить на свободное место. Результат приведён на следующем рисунке.
|
|
|
| |
| Рис.12 |
В качестве меры отклонения эмпирических значений от построенной модели можно вычислить сумму квадратов отклонений в заданных точках (столбец A). Рассчитаем сперва квадратичную зависимость по найденной формуле. Для этого поместим в ячейку C1 формулу =1,0265*A1^2 – 0.0322*A1 и протащим на 100 ячеек. Далее в свободную ячейку (например, D1) запишем формулу =СУММКВРАЗН(B1:B100;C1:C100), в которой для вычисления суммы квадратов разностей используется библиотечная функция.
Фильтрация данных.
В электронных таблицах предусмотрен ряд операций со списками – таблицами однородных данных, снабжёнными заголовками столбцов. Такие таблицы можно рассматривать как простейший аналог баз данных. Наиболее полезной операцией со списками является фильтрации. Команда Фильтр меню Данные позволяет отыскивать и использовать нужное подмножество данных в списке. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или отвечают определенным критериям.
Следующая таблица представляет собой фрагмент условного авиационного расписания.
| Рис. 13 | 
|
|
|
|
Для фильтрации данных используются команды Автофильтр и Расширенный фильтр пункта Фильтр меню Данные. Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов.
С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке строки, не содержащие данный элемент, будут скрыты. Например, если в списке поля Самолёт выбрать ТУ154, то будут выведены только записи, у которых в поле Самолёт содержится значение ТУ154.
| Рис.14 | 
|
Если в поле списка выбрать пункт Условие …, то появится окно Пользовательский автофильтр. В верхнем левом списке следует выбрать один из операторов (равно, больше, меньше и др.), в поле справа – выбрать или задать значение. В нижнем левом списке можно выбрать другой оператор, и в поле по правую сторону – значение. Когда включен переключатель И, то будут выводиться только записи, удовлетворяющие обоим условиям. При включенном переключателе ИЛИ будут выводиться записи, удовлетворяющие одному из условий. Например, в панели ввода на следующем рисунке введены условия для показа рейсов с отправлением от 11 часов и до 18 часов.
|
|
|
| Рис. 15 | 
|
Для отбора записей с текстовыми значениями поля можно использовать шаблоны, идентичные поисковым шаблонам в файловой системе Windows. В критерий отбора включаются символы замещения? и *. Символ? в некоторой позиции указывает, что на данном месте текста может находиться любой символ, а символ * замещает любое количество символов в данном месте. Например, следующий автофильтр для поля Куда
| Рис.16 | 
|
выделит все рейсы на Москву и Минводы. Смысл использованного шаблона – отбор всех текстовых значений, начинающихся с «М».
Для вывода нескольких записей с самым большим или самым малым значением по числовому полю следует в скрытом списке поля выбрать пункт Первые 10. В диалоговом окне Наложение условия по списку в первом поле со счетчиком необходимо выбрать количество записей, а в поле справа выбрать – наибольших или наименьших.
Чтобы вернуть таблицу в исходный вид, необходимо вызвать команду Отобразить все или отменить команду Автофильтр меню Данные, подменю Фильтр.
Команда Расширенный фильтр позволяет фильтровать данные с использованием диапазона критериев для вывода только записей, удовлетворяющих определенным критериям. Значения переключателей и полей окна Расширенный фильтр следующие:
|
|
|
| Рис.17 | 
|
● фильтровать список на месте – переключатель, скрывающий строки, которые не удовлетворяют указанному критерию;
● скопировать результат в другое место – копирует отфильтрованные данные на другой рабочий лист или на другое место на этом же рабочем листе;
● исходный диапазон – блок ячеек с данными, подлежащими фильтрации;
● диапазон условий – блок ячеек на рабочем листе, который содержит необходимые условия;
● поместить результат в диапазон – блок ячеек, в который копируются строки, удовлетворяющие заданным условиям; это поле активно только в том случае, если выбран переключатель скопировать результат в другое место;
● только уникальные записи – переключатель, который выводит только строки, удовлетворяющие критерию и не содержащие повторяющихся элементов.
Для установления сложных критериев необходимо:
● в пустых строках ввести имена столбцов, по которым следует отфильтровать таблицу; в строках, расположенных под строкой с именами проверяемых столбцов, ввести критерии, которым должны соответствовать ячейки проверяемых столбцов;
● выбрать в меню Данные → Фильтр → Расширенный фильтр, и в диалоговом окне ввести адреса всех диапазонов.
Для объединения критериев с помощью условного оператора И следует указать критерии в одной и той же строке, а для объединения критериев с помощью условного оператора ИЛИ следует ввести критерии в разных строках. Например, диапазон критериев на рисунке 18 выводит на экран все записи о рейсах с отправлением между 11 и 18 часами.
Рис.18
Самый гибкий вид расширенного фильтра называется вычисляемым фильтром. Для него строится формула, имеющая смысл используемого сложного условия, которое включает в качестве своих аргументов имена столбцов. Эта формула записывается в свободную ячейку, а ячейка над ней оставляется пустой либо туда записывается название (произвольное) этого фильтра. Далее следуют такие же действия, что и для обычного расширенного фильтра. В качестве примера выделим рейсы длительностью более двух с половиной часов. Вычисляемый фильтр в данном случае будет иметь вид: = Приб – Отпр > Время(2;30;0). Здесь из времени прибытия вычитается время отправления и ставится условие, чтобы эта разность была более 2,5 часов. В правой части используется библиотечная функция Время, которая переводит 2,5 часа во внутреннее представление времени. Результат применения фильтра показан на следующем рисунке. Формула записана в ячейку C12 и сразу вычислена, поэтому отображается как ЛОЖЬ. Все три диапазона выделены рамками. Полезно обратить внимание, что в качестве диапазона условий необходимо задавать блок из двух ячеек.
|
Рис. 19
Оптимизация.
Широкий класс задач составляют задачи нахождения наибольших и наименьших значений (оптимизационные задачи). В общем виде задача оптимизации состоит в том, чтобы подобрать такие значения аргументов целевой функции, при которых она принимает экстремальное значение (минимальное или максимальное), причём соблюдается заданная система ограничений на значения аргументов. В математической форме задача может быть сформулирована следующим образом:
;
;
где f – целевая функция; n – количество аргументов целевой функции; g i, – функции системы ограничений (равенств или неравенств различного вида). Часто выделяют условия вида x j ≥ 0 (условие физической реализуемости). Инструментом для поиска решений оптимизационных задач служит в Excel надстройка Поиск решения.
Рассмотримзадачу об оптимальной структуре производства. Предприятие выпускает телевизоры, стерео- и акустические системы, используя общий склад унифицированных комплектующих деталей. В ячейках D11:F15 показано, в каком количестве каждая деталь входит в изделия, перечисленные в столбце A. Готовые изделия продаются по ценам: телевизор – 75 у.е., стерео – 50, акустическая система – 35. Необходимо максимизировать прибыль от продажи всех произведённых изделий с учётом ограниченности складского запаса комплектующих (столбец B) и уменьшения прибыли при насыщении рынка (так называемый коэффициент отдачи в ячейке H15).
Рис.20
Предположим, что мы собираемся выпускать по 100 штук каждого изделия (D9:F9 – начальный план). Тогда потребуется количество шасси, выражаемое формулой =$D$9*D11+$E$9*E11+$F$9*F11, которую запишем в ячейку C11. Аналогичные формулы расхода по каждой детали получим в ячейках C12:C15, протянув маркер протаскивания вниз от ячейки C11. Прибыль от продажи изделий каждого вида запишем в ячейках D17:F17. Для телевизоров, например, эта формула имеет вид =75*МАКС(D9;0)^$H$15, который учитывает уменьшение коэффициента отдачи, а также условие физической реализуемости. Последнее означает, что если значение переменной D9 окажется отрицательным, то будет использовано нулевое значение. Полная прибыль от продажи всех изделий записана в ячейке D18 (=СУММ(D17:F17)), это целевая функция.
Вызовем через пункт меню Сервис –> Поиск решения. Открывается диалоговое окно, в котором предусмотрены следующие поля ввода:
● Установить целевую ячейку – служит для указания ячейки с целевой функцией (целевой ячейки), которую необходимо максимизировать, минимизировать или установить равным заданному числу;
● Равной – служит для выбора варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного числа);
● Изменяя ячейки – служит для указания ячеек (аргументов, поисковых
параметров), значения которых изменяются в процессе поиска решения до тех пор, пока не станут выполняться наложенные ограничения
| Рис.21 | 
|
● Предположить – используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле Установить целевую ячейку. Результат поиска отображается в поле Изменяя ячейки;
● Ограничения - служит для отображения заданного списка ограничений (граничных условий) поставленной задачи;
● Добавить – используется для отображения диалогового окна Добавить ограничения;
● Изменить – применяется для отображения диалогового окна Изменить ограничения;
● Удалить – служит для снятия указанного ограничения;
● Выполнить – используется для запуска поиска решения поставленной задачи;
● Закрыть – служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом заданные установки сохраняются;
● Параметры – применяется для отображения диалогового окна Параметры поиска решения, в котором можно загрузить или сохранить оптимизируемую модель и указать предусмотренные варианты поиска решения;
● Восстановить – служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию.
В рассматриваемой задаче адрес ячейки с целевой функцией – D18, адрес диапазона независимых переменных – D9:F9 (это те же ячейки, в которых указан начальный план), ограничения: $C$11:$C$15 ≤ $B$11:$B$15 (расход деталей не должен превышать имеющегося запаса), и $D$9:$F$9 ≥ 0 (условия физической реализуемости). Заданные здесь ограничения называются групповыми. После этого нажимаем кнопку Выполнить и можно либо Сохранить найденное решение либо восстановить диалоговое окно в исходом виде. Решение, доставляющее максимум (14917), отображается в блоке D9:F9.
Дата добавления: 2016-01-04; просмотров: 15; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!