Знакомство с программированием в Excel. Программирование в Excel

Инструкция

Запустите Microsoft Office Excel. Если необходимо, создайте новый документ. Для этого нажмите клавиши Ctrl+N или разверните меню «Файл» и выберите пункт «Создать...». Затем кликните по ссылке «Чистая книга» в панели «Создание книги».

Откройте окно редактора Visual Basic. Для этого нажмите сочетание клавиш Alt+F11 или выберите пункт «Редактор Visual Basic» в разделе «Макрос» меню «Сервис». Там вы увидите панель проекта, отображающую дерево объектов данной книги Excel, а также содержащиеся в ней формы, модули и модули классов.

Создайте формы, если это необходимо. В главном меню кликните по пункту Insert, а затем UserForm. В разделе Forms панели проекта добавится новый элемент. При создании форма будет автоматически открыта. Мышью переносите элементы управления из панели Toolbox в окно формы. Изменяйте их размеры и положение. После выделения мышью меняйте их свойства в панели Properties. Сохраняйте формы, нажав Ctrl+S.

Создайте нужное количество модулей или модулей классов. Для этого выбирайте пункты Module или Class Module в разделе Insert главного меню. Откройте окна редактирования кода нужных модулей или форм, дважды кликнув по соответствующим им элементам в окне проекта.

Добавьте декларации классов в модули классов. Определите их при помощи ключевого слова Class:
Class CSampleClass
End Class

В определения классов добавьте методы, а в модули - заготовки функций и процедур. Функции объявляются с помощью ключевого слова Function, после которого следует имя и набор параметров, через запятую, заключенных в скобки. Например:
Function SampleFunction(a, b, c)
End Function
Аналогично (только при помощи ключевого слова Sub) объявляются процедуры:
Sub SampleProcedure(a, b)
End Sub

Объявите члены классов, а также глобальные и локальные (в функциях и методах) переменные. Для этого используйте предложение Dim...As (после ключевого слова As указывается тип переменной). Например, объявление переменной oWB, хранящей ссылку на объект книги, может выглядеть так:
Dim oWB As Excel.Workbook
Указав размерность в круглых скобках, можно задекларировать массив:
Dim aWBooks(10) As Excel.Workbook

Реализуйте алгоритм работы программы, внеся изменения в код функций, процедур, методов классов. Используйте структуры управления Visual Basic для контроля потока исполнения. Работайте с собственными структурами данных, объектами созданных форм встроенными объектами Excel.

Источники:

• как создать документ в excel

Visual Basic это среда для разработки ПО, которое разрабатывается компанией Microsoft, включает в себя язык программирования. Он унаследовал стиль и частично синтаксис своего предшественника, языка «Бейсик». Среда разработки Visual Basic включает в себя инструменты работы с конструированием пользовательского интерфейса.

Вам понадобится

• - компьютер;
• - установленная программа Visual Studio;
• - навыки программирования.

Инструкция

Запустите программу Visual Studio, создайте проект , чтобы выполнить создание программы Visual Basic. Для этого выберите меню «Файл», а в нем команду «Создать проект ». В диалоговом окне выберите опцию «Приложение Windows» и щелкните по кнопке «ОК». В интерфейсе будет открыта форма. По умолчанию проект будет назван WindowsApplication1. Проект Visual Basic – это место, где хранятся части программы, и поддерживается их организация. Форма, которая открывается при создании проект а, представляет собой то окно, которое будет отображено при запуске программы. Соответственно, если в программе несколько окон, значит и в проект е может содержаться несколько форм.

Добавьте на форму элементы управления с панели элементов. Она располагается в левой части окна, содержит в себе несколько вкладок, в том числе «Все формы», «Компоненты», «Данные». В каждой вкладке есть определенный набор операций, который представляет элементы управления, либо компоненты, которые могут быть добавлены в приложении при создании проект а в Visual Studio.

Выделите панель элементов, щелкните по вкладке «Все формы», перетащите в левый верхний угол вашей формы элемент управления «Панель». Аналогично переместите в проект программы Visual Basic элемент «Текстовая область». Чтобы изменить его положение, просто перетащите его левой кнопкой мыши в нужное место формы.

Аналогично выполните добавление кнопок, справа от текстового блока. Далее выберите элемент, соответствующий разновидности проект а, например, «Web-браузер» и поместите его под панелью. Каждый добавленный элемент управления содержит специальный код, который определяет его внешний вид, а также задачи, которые выполняются данным элементом. Вы можете создать собственный код, например, для кнопки, чтобы изменить ее внешний вид, добавить выполнение определенной задачи, но это трудоемкий процесс. Гораздо проще выполнить это с помощью редактирования проект а в Visual Basic.

Далее выполните настройку внешнего вида программы, добавьте код, который определяет их поведение. По необходимости добавьте пользовательские элементы управления. Далее проведите пробный запуск и тестирование вашей программы, по необходимости вернитесь в конструктор проект а Visual Basic и исправьте недостатки.

Видео по теме

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

Инструкция

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

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

Видео по написанию (программированию) макросов на VBA в Excel

VBA (Visual Basic for Applications) — разновидность языка программирования Visual Basic, входящая в пакет Microsoft excel

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

При написании кодов в excel программист использует:

• Для написания кода существует два основных способа: писать внутри листа; внутри книги. Любая программа в рабочей среде excel начинается с ключевого слова «SUB», далее идет любое название с использованием букв или цифр, после чего открываем и закрываем скобки () и нажимаем «ENTER», образуется рабочая среда для написания программы. В рабочей среде набираем Аpplication, указываем ThisWorkBooks — эта книга, далее Sheet — листы, далее Cells(5,1) — номер ячейки, сначала пишется строка потом колонка. Программирование будет иметь такой вид: Аpplication — ThisWorkBooks — Sheets — Сells(5,1). Присваиваем этому адресу значение 10 через ключевой оператор присваивания «=» и запускаем программу на исполнение нажатием на кнопку «RunSub» или клавишей «F5». Присвоенное значение 10 окажется в указанном месте.

При работе с данным объектом в excel используется «среда разработки». «Среда разработки» вызывается сочетанием клавиш Сtrl+F11, после чего необходимо выбрать вкладку «View», далее «Обозреватель проекта», на котором будет отображено три элемента, два листа рабочей книги и сама книга excel.

DOM — Document object model (объектная модель документа) — независимый программный интерфейс, дающий возможность скриптам и программам находить доступ к содержимому , XHTML и HTML документов и менять оформление и структуру данных документов. DOM не ограничивает структуру документа, а представляет его в виде дерева узлов, любой из которых является элементом, атрибутом, графическим или текстовым объектом.

• «Работа с переменными» — это слова или буквы, которые содержат в себе какое-то значение, например: Х=5, Y=10, с ними можно производить любые математические операции. Допустим, чтобы найти сумму данных значений с помощью программирования, в рабочей среде вводим следующую программу: Cells(2,1) = X+Y нажимаем «RunSub» или «F5» во 2 строке, 1 столбца отобразится 15 — сумма программируемых значений. Помимо чисел в значениях могут содержаться слова, единственное отличие, что программируемые слова должны браться в кавычки.

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

Excel 2000".

Почему не просто "Мир Excel 2000"? Видимо, потому что я предпочитаю смотреть на все, что происходит в Excel , с объектной точки зрения, рассматривать все происходящее как действия над объектами. Да и описанию самих объектов в этой книге уделяется достаточно много внимания. При этом в круг рассмотрения входят не только объекты самого Excel . Мир объектов Excel более широк. Так или иначе, в рассмотрении появляются общие объекты Office 2000, объекты других приложений, в книге, например, много внимания уделяется связи Excel и Access. Помимо объектов Excel в книге подробно рассматриваются еще две группы объектов - ADO и OWC. Объекты ADO , позволяющие организовать доступ к базам данных, несомненно, входят в мир Excel , поскольку, чаще всего, Excel является тем приложением, где обрабатываются данные, хранимые в различных источниках. Компоненты OWC, обладающие функциональностью Excel , - их объектная модель - это тоже часть мира объектов Excel . Без этих объектов не обойтись, если Ваше решение переносится в интранет или интернет , и Вы захотите создать Web -узел, страницы которого обладают функциональностью Excel документов.

Что же можно найти в этой книге, и кому она предназначена?

Эта книга предназначена тем, кто создает решения на базе Excel . Конечно, в первую очередь речь идет о тех, кто уже использует программирование на VBA и VBScript при создании своих решений. Но я думаю, что и те, кто давно работает в Excel , но еще не использовал программные способы настройки своих решений, могут начать с этой книги, хотя, предупреждаю, требуется определенная программистская подготовка.

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

Глава 3 дает описание объектной модели Excel . Подробно рассмотрены все основные действующие лица этой модели - объекты Application , WorkBook, WorkSheet, Chart , Range и другие. Понятно, что при раскрытии этой темы без технических деталей не обойтись, поэтому чтение главы довольно утомительно, но как справочный материал, она, надеюсь, будет весьма полезной.

Базы данных и другие источники данных - это одна из ведущих тем этой книги. Глава 4 " Excel и базы данных " начинает эту тему. В этой же главе дается знакомство с приложением Access, на примере показано, как строится база данных в Access, изучаются связи Excel и Access.

Пятая и шестая главы книги посвящены объектам ADO . Уже то, что этим объектам посвящены две главы, говорит о степени подробности рассмотрения этой темы. Объекты ADO задают интерфейс между самыми разнообразными источниками данных и клиентским приложением, где эти данные обрабатываются. Таким приложением совсем не обязательно должен быть Excel , сфера применения объектов ADO значительно шире. Но Excel это одна из наиболее подходящих кандидатур на роль такого клиентского приложения. Чтение этих глав требует определенной подготовки и ориентированно на более продвинутых программистов.

Седьмая глава книги посвящена разработке интерактивных документов. Она носит в определенном смысле центральный характер. В ней рассматривается весь процесс создания интерактивного документа Excel . Этот документ можно считать примером создаваемого в Excel типичного решения, в котором программный проект играет определяющую роль. Предлагаемое решение использует базу данных Access и объекты ADO для организации связи с базой данных. Здесь же обсуждается и ряд других вопросов, важных в практической работе, начиная от организации интерфейса пользователя до получения печатной формы документа.

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

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

В главе 10 в рассмотрение вводятся новые объекты, тесно связанные с Excel . В этой главе рассматриваются Office Web Components - компоненты OWC. По моему мнению, эти компоненты являются лучшим продуктом, созданным Microsoft в области компонентного программирования. Они дают возможность добавить функциональность Excel в свои решения без использования самого Excel и других приложений Office 2000. Основное предназначение этих компонент - обеспечение корпоративной работы с документами в интрасетях, что позволяет создавать интерактивные страницы Web -узлов, обладающие функциональностью Excel . При рассмотрении этих компонент большое внимание уделяется программной работе с этими компонентами, возможности программной настройки Web -документов, использующих эти компоненты.

Глава 11 посвящена введению в Web - программирование . Я подробно рассматриваю процесс создания интерактивного Web -документа. Ключевым словом здесь является " интерактивность ", поскольку, по большому счету, вся эта книга о том, как придать нужную интерактивность создаваемым документам. Глава носит вполне самостоятельный характер, но, по сути, является естественным продолжением двух тем, рассматриваемых в главах 7 и 10, поскольку с одной стороны речь идет о создании интерактивного документа, с другой - документа Web , страницы которого могут обладать функциональностью Excel . Замечу, что при рассмотрении вопросов создания интерактивного очередь , на школьников, но не только на них. Большинство игр относится к классу "развивающих" игр, направленных на развитие логического мышления. Среди игр есть вариации известных игр - Lines, Tetris, Digger, игры-головоломки, известная карточная игра "Подкидной дурак" и другие игры.

Завершая предисловие, хочу сказать, что предлагаемый курс тесно связан с книгами серии "Офисное программирование ", вышедшими в издательстве "Русская редакция". Есть явные и неявные ссылки в тексте многих глав:

1. "VBA в Office 2000. Офисное программирование,
2. "Средства разработки VBA-программиста". Офисное программирование,
3. Мир объектов Excel 2000. Средства разработки VBA-программиста.

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

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

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

Формат файла: .chm

Справочник по функциям листа Excel (532,2 KiB, 15 462 скачиваний)

VBA Программирование в MSOffice - очень хороший учебник для начинающих программировать в VBA. Книга написана сертифицированным преподавателем Microsoft Office доступным для понимания языком, снабжена грамотными листингами кодов. Учебник поможет освоить программирование не только в Microsoft Excel, но и Microsoft Word и Microsoft Project.

Формат файла: .pdf

VBA Программироваие в MSOffice (4,1 MiB, 26 659 скачиваний)
У Вас нет прав для скачивания этого файла. Возможно, Вы не зарегистрированы на сайте. Зарегистрируйтесь и попробуйте скачать снова.

Уокенбах Дж. Профессиональное программирование в VBA Excel 2003 - пожалуй самый популярный учебник для начинающих. Многие начинали именно с него. Правда, в печатной версии данного учебника использованы листинги кодов, которые не всегда нужно принимать на веру - очень часто там встречаются опечатки и вместо английских символом встречаются русские. Поэтому лично я бы посоветовал не копировать коды напрямую из книги, а переписывать их вручную в редакторе VBA. Так и запомнится лучше и избавите себя от поиска ошибок.

Формат файла: .pdf

Office 2007. Самоучитель - Если Вы решили поближе познакомиться с интерфейсом и основными возможностями приложений Microsoft Office, то советую почитать данную книгу. В ней описано все необходимое для успешной работы в таких приложениях как: Excel, Word, Outlook, Power Point, Access. После прочтения Вы сможете создавать красивые презентации и информативные графики, научитель создавать задачи в Outlook и базы в Access.

Формат файла: .pdf

Русская справка по Visual Basic for Application(VBA) - Это официальная справка по Visual Basic for Application(VBA), которая была включена в пакет Microsoft Office 97. Тогда справка была еще русифицирована. Ни для кого не секрет, что сейчас во всех версиях офиса справка по VBA доступна только на английском языке, независимо от локализации. А по буржуйски не все могут читать(даже сносно, как показывает практика).
Хочу сразу предупредить - т.к. справка предназначена для довольно старой версии, в ней описаны не все методы и свойста. Но основная их масса все же описана и, надеюсь, данная справка поможет Вам в начальном изучении VBA.

Формат файла: .файл справки

RUS_VBA.zip (1,1 MiB, 9 164 скачиваний)
У Вас нет прав для скачивания этого файла. Возможно, Вы не зарегистрированы на сайте. Зарегистрируйтесь и попробуйте скачать снова.

Объекты Shell_RegExp и пр - целый сборник примеров работы с такими объектами как: Shell, RegExp, FileSystemObject, WshShell, Dictionary, WshNetwork, WScript, константы и функции WScript. Все примеры структурированы по разделам и сделаны в качестве справки по свойствам и методам каждого из объектов. Очень удобно, если не очень часто применяете в работе эти объекты и что-то забылось.

Оптимизационные модели используются, чтобы найти ответы на вопросы типа:

• как составить расписание для сотрудников колл-центра, чтобы оно соответствовало их отпускным запросам, сбалансировало переработки и исключало круглосуточные дежурства?
• какие возможности бурения нефтяных скважин использовать для получения максимального дохода, держа при этом под контролем все риски?
• когда следует делать новые заказы в Китае и как их доставлять, чтобы минимизировать стоимость и соответствовать ожидаемому спросу?

Скачать заметку в формате или , примеры в формате

Целью оптимизации всегда является «максимизация» или «минимизация». Самая распространенная и понятная форма математической оптимизации - это линейное программирование, секретная разработка советских инженеров конца 1930-х годов, ставшая популярной в ходе Второй мировой войны. Кстати, слово «программирование» в данном словосочетании является пережитком военной терминологии того времени и не имеет ничего общего с компьютерным программированием.

Начнем с любимого примера экономистов - пушек и масла. Идет 1941-й год, вы – хозяин французской молочной фермы. Днем вы доите коров и производите сливочное масло, ночью – собираете автоматы. Ваша цель – максимальная прибыль, чтобы как можно дольше производить автоматы. От посредника из Сопротивления вы получаете за каждый автомат по 195 денежных единиц (чтобы не напрягать Excel несуществующими франками, допустим, что это доллары). За каждую бочку масла на рынке вам платят по $150.

Условия и ограничения. Себестоимость одной бочки масла – $100, а одного автомата – $150. Месячный бюджет на производство - $1800. Вы храните продукцию в 21-кубометровом подвале. Автомат занимает ½ м 3 , бочка масла 1½ м 3 . Сколько автоматов и бочек масла вам нужно продать за месяц, чтобы получить максимальную прибыль?

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

Представим области допустимых значений графически. Во-первых, количество пушек и бочек масла должно быть неотрицательным. Во-вторых, максимально можно произвести $1800/$150 = 12 автоматов или $1800/$100 = 18 бочек масла (рис. 1). Общее название этого треугольника – политоп – фигура с плоскими сторонами (например, бриллиант). В-третьих, подвал может вместить не более 21/(½) = 42 автоматов или 21/(1½) = 14 бочек масла (рис. 2).

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

(195 – 150) * N автоматов + (150 – 100) * N бочек масла = С,

где С – константа.

Например, при С = 450, линия будет проходить через координаты (0;10) и (9;0). Графически идея максимизации прибыли реализуется перемещением линии уровня параллельно самой себе в направлении увеличения значений по осям Х и Y (рис. 3). Любопытно, что для политопа оптимум всегда лежит в одной из вершин (или единственного решения не существует вовсе). На этом свойстве основан алгоритм симплексного метода. Решение задачи в Excel начинают с создания области модели (рис. 4). Формула целевой функции в ячейке В1 =СУММПРОИЗВ(C4:D4;C10:D10).

Рис. 3. Линия уровня и функция для оптимизации прибыли: а) некое произвольное начальное положение; б) линия уровня в оптимальном положении

У вас всё готово, чтобы нажать кнопку ДАННЫЕ –> Поиск решения . (Если вы не видите этой кнопки, установите надстройку Поиск решения; см. , глава 1). В открывшемся окне Параметры поиска решения задайте выделенные опции и нажмите Найти решение .

Рис. 5. Окно Поиск решения

Excel обновит лист и внесет на него результаты расчета (рис. 6).

Что произойдет, если добавить нелинейность? Допустим ваш посредник предлагает $500, если число автоматов в месяц будет более 5. Просто добавьте функцию ЕСЛИ в ячейку с прибылью (В1). Теперь целевая функция выглядит так: =СУММПРОИЗВ(C4:D4;C10:D10)+ЕСЛИ(C4>5;500;0). Жмем Поиск решения . Неудача, Excel сообщает об ошибке – условия линейности не выполнены (рис. 7).

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

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

К сожалению, с эволюционным алгоритмом все же возникают некоторые проблемы:

• Время работы существенно больше, чем при симплекс-методе
• Нет никакой гарантии, что он найдет оптимальное решение. Все, что в его силах - это контроль лучшего решения в популяции, пока не закончится время, либо популяция не изменится в достаточной степени для продолжения, либо вы принудительно не остановите «Поиск решения» нажатием кнопки ESC.
• Эволюционный поиск решения работает довольно медленно. А если области допустимых значений сложные, он часто ругается, не найдя даже места, с которого начать.
• Если вы хотите заставить эволюционный алгоритм хорошо работать в Excel, вам придется определить жесткие границы для каждой переменной решения. Даже если ваше решение более или менее неограниченное, вам все же нужны ограничения.

Принимая во внимание последний пункт, для решения задачи с автоматами и маслом вам нужно добавить ограничение, согласно которому оба решения не должны быть больше 25 (рис. 8). Установив основные параметры модели, кликните на кнопку Параметры . Проработав около минуты, эволюционный алгоритм выдал ожидаемое решение – 6 автоматов и 9 бочек масла. Поскольку без бонуса оптимально сделать лишь три автомата, а бонус выплачивается при производстве более 5 автоматов, очевидно, что оптимальным будет выбор 6 автоматов.

Рассмотрим теперь более сложный пример. Вы работаете в компании, которая производит апельсиновый сок, смешивая натуральные соки разных сортов (рис. 9). Чтобы ваш сок отвечал самым изысканным требованиям:

• отношение по шкале Брикс/кислотность должно оставаться в пределах 11,5–12,5;
• уровень кислотности должен оставаться между 0,75–1%;
• уровень вяжущего вкуса должен быть 4 или ниже;
• цвет должен находиться в рамках 4,5–5,5.

Шеф сообщил вам, что на январь и февраль он ожидает спросу на уровне 600 000 галлонов сока в месяц, а в марте – 700 000 галлонов. И еще, имеется договор со штатом Флорида, предоставляющий налоговые льготы при условии, что компания покупает не менее 40% сока каждый месяц у фермеров, выращивающих сорт Valencia . Договор следует соблюсти.

Рис. 9. Список характеристик для производства свежевыжатого апельсинового сока (чтобы увеличить изображение, кликните на нем правой кнопкой мыши, и выберите Открыть картинку в новой вкладке )

Создайте оптимизационную модель (рис. 10). Формулы можно изучить на соответствующем листе, приложенного Excel-файла. Кликните Поиск решения , и введите параметры (рис. 11). Нажмите Найти решение .

Рис. 11. Заполненное окно Поиск решения для задачи смешивания

Запустив Поиск решения , вы находите оптимальную стоимость закупок - $1,23 млн. (рис. 12). Обратите внимание, что заказ флоридской Valencia проходит по нижней границе условия. Очевидно, эта сделка - не лучший вариант, но приходится смириться. Второй по популярности сорт - это Verna из Мексики, которая чертовски дешева, но ровно настолько же ужасна.

Вы представляете результаты расчета шефу, но он остается недоволен, и говорит о том, что не хочет выходить за бюджет $1,17 млн. Вы возвращаетесь к компьютеру и начинаете понимать, что стоимость перестала быть целевой функцией. Теперь это условие! А какова цель? Вы можете снизить стоимость закупок только смягчив требования к качеству. Вы решаете сформулировать их в терминах процентного сокращения, и делаете новую модель (рис. 13).

Обратите внимание, что в ячейках В26:29 и F26:F29 теперь не константы, а формулы. Ваша новая цель – минимизация процента снижения качества в ячейках G26:G29. Точнее, вы бы хотели минимизировать максимальное из значений в ячейках G26:G29. Однако, если в ячейку D2 поместить формулу =МАКС(G26:G29), модель не будет работать. Напоминаю, функция МАКС не является линейной. Здесь доступна маленькая хитрость – можно внести дополнительное условие в модель: $G$26:$G$29<=$D$2 (рис. 14), а ячейку D2 оставить пустой. Т.е., ячейка D2 будет оптимизироваться не благодаря наличию в ней формулы, а последовательными циклами, запускаемыми этим дополнительным условием.

Нажмите Найти решение . Симплексный алгоритм будет пытаться приблизить D2 к 0 как целевую функцию модели, в то время как ограничения по вкусу и цвету будут пытаться увеличить ее насколько возможно, чтобы получить пригодную для работы смесь. Где же остановится значение D2? Самое меньшее из возможных значений - максимальный процент из четырех сниженных в диапазоне G26:G29. Мы видим (рис. 15, ячейки С26:Е29), что снижение расходов на 5% потребовало выйти за ограничения качества по всем четырем параметрам.

Вы представили данные шефу, который увидел, что сокращение расходов на 5% не стоит снижения качества сока, поэтому он согласовал ваш первый вариант. Но, когда вы принесли его в отдел снабжения, сотрудники возмутились. Как можно было так раздробить поставки!? Снабженцы настаивают, чтобы вы укрупнили партии: не более 4 поставщиков ежемесячно! И вы садитесь за новую модель. К сожалению, использовать функции ЕСЛИ или СЧЁТ вы не можете, так как хотите остаться в рамках линейной модели. Поэтому вам снова приходится прибегнуть к ухищрениям (рис. 16). Вы добавляете в модель область С33:Е43, которую определяете, как бинарную (значения в ней могут быть только 0 или 1), и оставляете ее пустой. А также область F33:H43, где каждая ячейка равна произведению значения из областей С33:Е43 на G5:G15. В параметры Поиск решения (рис. 17) вы добавляете еще одно условие $С$15:$Е$15 <= $F$33:$H$43 и еще одну область переменных – $C$33:$E$43.

Как в этом случае будет работать оптимизационный алгоритм? Когда он стартует все значения в областях С5:Е15, С33:Е43 и F33:H43 равны нулю. Допустим, что алгоритм пытается в ячейку С7 поместить значение 240. Сработает условие С7 <= F35, которое приведет к увеличению значения в F35, которое, в свою очередь, определяется формулой F35 = C35*$G7. Поскольку G7 – константа, а С35 – бинарная переменная, последней присваивается значение 1. Условие С7 <= F35 выполнено, т.к., 240 <= 1200. Таким образом вы моделируете неудобное условие «если… то»: «если заказ сделан, то бинарная переменная включается».

Нажмите Найти решение . Вы заметите, что решение задачи требует больше времени из-за добавления бинарных переменных. Если по какой-то причине Поиск решения слишком затянул свой поиск, вы всегда можете нажать ESC и увидеть лучшее из найденных решений на данный момент.

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

Инженеры сообщили, что на производстве появились новые «снижатели кислотности». Данная технология способна нейтрализовать 20% кислоты в соке, протекающем через прибор. Это не только снижает процент кислоты, но и повышает индекс Брикс/кислотность на 25%. Но для «снижателя» нужна энергия и расходные материалы стоимостью $20 за 1000 галлонов сока. Не весь сок, поступающий от поставщиков, нужно прогонять через этот процесс, однако, если поставка по какому-нибудь заказу прогоняется через ионообменник, то должен быть обработан весь ее объем. Постройте модель с участием ионообменника для снижения стоимости.

Проблема с новым правилом заключается в том, что естественный способ его моделирования - нелинейный, что приведет к использованию медленного алгоритма оптимизации. Но, как и в предыдущем примере, можно ввести бинарную переменную в области С25:Е35, которая бы «включалась» при необходимости понизить кислотность партии (рис. 18). Поскольку, нельзя использовать произведение «индикатор понижения кислотности (бинарный) * объем партии», вы создаете область С37:Е47, которая вам пригодятся для уравнивания объемов, подлежащих снижению кислотности, без прямого участия в формулах самих этих объемов. Итак, области С25:Е35 и С37:Е47 не содержат формул. В области G25:I35 используются формулы =С25:Е35*G5:G15 (это ограничение партии общим доступным объемом сока), а в области К25:М35 =Е5:E15-GG5:15*(1-Е25:E35). Это условие заработает только если партия подлежит снижению кислотности.

Также в модели со «снижателем кислотности» были изменены формулы в ячейках С16:Е16 (теперь они учитывают затраты на снижение кислотности по формуле «индикатор (бинарный) * объем партии * $20) и в ячейках С50:Е51 (теперь они учитывают повышение коэффициента Брикс/кислотность на 25% и снижение кислотности на 20% для обработанных партий). В параметрах Поиска решения появились новые переменные и дополнительные условие (рис. 19). К сожалению, нажав кнопку Найти решение , вы узнаете, что надстройка Поиск решения не может справиться с задачей (рис. 20). Модель стали слишком сложной.

Рис. 19. Параметры Поиска решения в модели со «снижателем кислотности»

Рис. 20. Поиск решения не справляется с задачей

Вам нужно загрузить и установить OpenSolver (как это сделать см. , глава 1). OpenSolver «подхватит» установки, введенные только что в окне Поиск решения . Поэтому просто нажмите кнопку Solver . Полученное решение – $1 235 927 более чем на $ 100 000 лучше предыдущего минимума – $1 338 913.

До сих пор мы считали, что поставляемая продукция имеет точно указанные параметры. Резонно предположить, что эти параметры подвержены вариации, характеризуемой среднеквадратичным отклонением (рис. 21; подробнее см. ). Самое известное и широко используемое распределение случайной величины - это нормальное распределение, иначе называемое «колоколообразной кривой». Скажем, в случае с соком из Египта среднее значение отношения Брикс/кислотность будет 13, а среднеквадратичное отклонение (также называемой стандартным отклонением) - 0,9 (рис. 21). В данном примере 13 - это центр распределения вероятности, 68% заказов будут в пределах ±0,9 от 13, а 95% будут в пределах ±1,8 от 13.

Ваша цель - предложить план смешивания стоимостью меньше $1,25 миллиона, который наилучшим образом соответствует ожиданиям по качеству в свете вариабельности поставок.

Мы используем среднее и среднеквадратичное отклонение характеристик, чтобы применить имитационное моделирование по методу Монте-Карло (если вы слышите это название впервые, рекомендую ). В этом методе вместо включения параметров распределения (среднего значения и среднеквадратичного отклонения) в модель напрямую, создается большое число сценариев, основанных на этих самых параметрах распределения.

Сценарий - это один из возможных ответов на вопрос: «Если это - распределения, основанные на статистике, на что же будет похож конкретный заказ?» Каждый сценарий включает сорок параметров десяти сортов сока (рис. 22). Чтобы получить один такой параметр, воспользуйтесь функцией НОРМ.ОБР (подробнее о функции см. ). Например, в ячейке В33 отношение Брикс/кислотность для сорта Hamlin определяется формулой =НОРМ.ОБР(СЛЧИС();H5;N5). Введите аналогичные формулы в область В33:СW76, сгенерировав 100 сценариев. Поиск решения не сможет работать с этими формулами, так как они нелинейны, поэтому скопируйте их в буфер и вставьте, но уже, как значения.

Цель минимизировать значение в ячейке D2. Т.е., найти решение, которое менее всего снижает границы качества для 100 сценариев. Как и в примерах на рис. 13–15, в ячейке D2 нет формулы. Оптимизация выполняется заданием параметров в окне Поиск решения. Все, что нужно - это поместить во все сценарии границы качества, а не просто ожидаемые значения характеристик. Таким образом, в отношение Брикс/кислотность вы добавляете условия B78:CW80 >= B26 и =< F26, затем проделываете то же самое с кислотностью, вяжущей составляющей вкуса и цветом (рис. 24). Нажмите Найти решение . Решение найдется довольно быстро. Если вы генерировали случайные значения сами, а не использовали те, что находятся в файле для загрузки, ваше решение может отличаться. Для моей сотни сценариев наилучшим показателем, который мне удалось получить, является изменение качества на 133%.

Рис. 24. Настройка Поиска решения для модели с вариабельностью характеристик

Если вы хотите расширить свои знания в области линейного программирования, рекомендую книгу The AIMMS optimization modeling book . Не пропустите две главы про трюки и подсказки – они поистине гениальны.

Написано по материалам книги Джона Формана . – М.: Альпина Паблишер, 2016. – С. 129–186. Насчет секретности разработки и Второй мировой – это, похоже, личное мнение автора книги. См. Википедию . – Прим. Багузина .