Федеральное агентство по образованию

Государственное образовательное учреждение высшего профессионального образования «Челябинский государственный университет»

Кафедра математических методов

в экономике

Л.В. Прохорова

ИНФОРМАТИКА

Лекции и вопросы к зачету за 2 семестр

Челябинск 2009

Табличный процессор Excel.

Основные понятия.

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

В 1979 г. выпускник Гарвардского университета Дэн Бриклин и профессиональный программист Боб Франкстон предложили миру способ автоматизации трудоемких и утомительных операций пересчета, которые всегда требуются при ручном составлении таблиц в бухгалтерском и банковском учете, в проектно-сметных работах, при решении планово-экономических задач. Это была программа VisiCalc (наглядный калькулятор), которая позволяла представлять данные в виде таблицы на экране дисплея, прокручивать эту электронную таблицу по строкам и столбцам и, самое главное, обладала удивительным свойством – в таблице проводился автоматический пересчет содержимого ячеек при изменении значений одной из них. Причем, все действия были наглядны.

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

Табличным процессором, получившим широкое распространение, стал Lotus 1-2-3, ставший стандартом для табличных процессоров, заключающихся в следующем:

• Структура таблицы (пересечения строк и столбцов создают ячейки, куда заносятся данные).

• Стандартный набор математических и бухгалтерских функций.

• Возможность сортировки данных.

• Наличие средств визуального отображения данных (диаграмм).

В СССР получили широкое распространение два табличных процессора Super Calc и Quatro Pro. С появлением MS Windows и его приложений стандартом стал табличный процессор Excel.

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

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

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

Функции табличных процессоров:

1. Создание и редактирование электронных таблиц.

2. Создание многотабличных документов.

3. Оформление и печать электронных таблиц.

4. Построение диаграмм, их модификация и решение экономических задач графическими методами.

5. Создание многотабличных документов, объединенных формулами.

6. Работа с электронными таблицами как с базами данных: сортировка данных, выборка данных по запросам.

7. Создание итоговых и сводных таблиц.

8. Решение оптимизационных задач.

9. Решение экономических задач «что - если» путем подбора параметров.

10. Разработка макрокоманд, настройка среды под потребности пользователя.

Окно программы Excel.

Самая верхняя строчка – заголовок окна: Microsoft Excel – Книга.xls (по умолчанию) – называются файлы Excel.

Меню такое же, как у Word, только добавлен пункт «Данные».

Стандартная панель инструментов тоже похожа на Word , но есть новые кнопки: мастер функций, мастер диаграмм.

Панель инструментов форматирования тоже как у Word, но с некоторыми новыми кнопками.

Далее идет отличная от Word строка формул, в которой указывается номер текущей ячейки и ее содержимое.

Файл Excel называется Книгой. Книга состоит из трех листов – Лист1, Лист2, Лист3 (можно при необходимости добавлять листы).

Щелкая левой клавишей по ярлычку листа можно переходить из одного листа в другой. Можно дать и другое название, отражающее содержимое листа. Для этого необходимо щелкнуть по ярлычку листа правой клавишей мыши, вызвав контекстное меню, вызвать опцию «Переименовать» и набрать с клавиатуры новое имя листа. С помощью этого меню можно производить удаление листа, добавление нового листа и перемещение его в другой файл. Другой способ переименования – двойной щелчок клавишей мыши по ярлычку листа и набор нового имени.

Ячейки.

Каждый лист Excel представляет собой таблицу. Столбцы обозначены буквами от А до Z и далее сочетаниями букв от А до IZ, а строки числами – от1 до 65536.

Ячейки расположены на пересечении строк и столбцов, и их номер складывается из номера столбца и номера строки. Например, А1, GA200. Номер ячейки называется ее адресом. Адрес отображается в поле имени в строке формул. Текущая ячейка выделяется черным контуром.

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

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

Правила обозначения адреса блока.

Адрес блока состоит из адреса левой верхней ячейки и адреса правой нижней ячейки, разделенных двоеточием. C2:D7.

Выделение объектов на рабочем листе.

При работе с программными продуктами, функционирующими в среде Windows, основное правило гласит: сначала выделить объект, а затем выполнить над выделенным объектом операцию.

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

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

Строки – щелкнуть мышью по левой адресной полосе на числе-заголовке строки.

Несколько строк – не отпуская кнопку после щелчка, протащить мышь по адресной полосе строк по соответствующим цифрам.

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

Типы данных.

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

По первому символу Excel определяет, что введено:

• Если это буква или апостроф, то это текст.

• Если цифра, то число.

• Если знак равенства, то формула.

Если текст не входит в ячейку, то можно:

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

• объединить несколько ячеек и них записать текст. Для этого необходимо выделить несколько соседних ячеек и выбрать через Главное меню Excel путь: Формат ячейки (появляется диалоговое окно Формат ячеек) – Выравнивание – Объединение ячеек (этот же путь можно выбрать через контекстное меню).

• организовать перенос текста в ячейке по словам: Формат – Ячейки – Выравнивание – Переносить по словам.

(Числовой формат) Ввод чисел.

Если вы набрали последовательность символов, в которую входят цифры, а также знаки «+», «-» (в начале последовательности) или «,» (как разделитель целой и дробной части), эта последовательность считается числом.

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

Число можно представить в различных форматах:

- экспоненциальный – число будет представлено в виде мантиссы и порядка. Например, 48900 4,89Е+04. Точность числа (кол-во знаков после запятой) можно регулировать с помощью кнопок панели инструментов «Форматирование».

Если число не входит в ячейку, то Excel вместо числа ставит знаки ####. Тогда необходимо раздвинуть границы ячейки.

- денежный формат, к числу добавляется наименование 27,00р.

- дробный формат – число представляется в виде обыкновенной дроби, 2/3

- форматы даты (времени – применяется для вычисления, например, даты окончания работы, количество рабочих дней, количество дней, затраченный на ту или иную работу

- процентный формат. Применяя его не требуется умножение на 100%. Если ввести число 0,12 и задать процентный формат, то число примет вид 12%.

Для отмены заданного формата следует применить «Правка-Очистка-Форматы».

Ввод формулы.

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

При его записи следует соблюдать обычные правила алгоритмических языков: арифметические операции выполняются слева направо в порядке старшинства (возведение в степень ^, умножение *, деление /, сложение +, вычитание -).

Для изменения порядка выполнения операций используются круглые скобки. Формула записывается в строку.

Адреса ячеек набираются только латинским шрифтом.

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

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

Редактирование формулы.

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

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

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

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

Пример:

	А	В
1	35	12	=А1+В1
2	38	14	=А2+В2
3	42	15	=А3+В3
4	53	11	=А4+В4

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

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

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

	A	B	C	D
	День недели	Кол-во товаров	Сумма	Цена
1	Понедельник	43	=В1*D$1	110
2	Вторник	52	=В2*D$1
3	Среда	40	=В3*D$1
4	Четверг	39	=В4*D$1
5	Пятница	35	=В5*D$1

F4 устанавливает знак $.

1 раз F4 – $A$2

2 раза F4 – A$2

3 раза F4 – $A2

4 раза F4 – A2

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

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

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

$A1; A$1; – смешанная ссылка. $A$1.

Перемещение данных.

Можно производить с помощью мыши. Для этого указатель мыши установить на рамку выделенного блока (он примет форму стрелки) и буксировать блок в нужное место. При перемещении адресации я в формуле не изменяется.

Если нажать Ctrl – произойдет копирование данных.

Вставка простых строк и столбцов.

Строки – выделить на адресной полосе строку, перед которой будут вставляться пустые строки. В меню выбрать Вставка-Строка или воспользоваться контекстным меню. Так же вставляются столбцы – выделяется в адресной области.

Автозаполнение – функция Excel, которая применяется при заполнении рядов данных.

Если ввести в две соседние ячейки последовательно 2 числа, составляющие арифметической прогрессии, например, 1 и 2, затем их выделить и, как при копировании, с помощью маркера заполнения протащить их на несколько ячеек, то ряд продолжится: 1, 2, 3, 4, и т.д.

Excel тоже позволяет вводить и нечисловые последовательности. Например, если ввести в 2 соседние ячейки Январь, Февраль и осуществить описанную выше операцию, то в следующих ячейках появится Март, Апрель и т.д. Эти последовательности или списки, можно сформировать самому и дать Excel заполнить их. Для этого необходимо выполнить команду Сервис-Параметры-Списки-Добавить и, в окне «Элементы списка» записать (разделяя Enter) элементы, составляющие список.

Операции в формулах.

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

Операция – действие, выполняемое над данными.

Excel позволяет записывать в формулах не только числа, но и другие типы данных (тексты, дату, время).

Рассмотрим арифметические выражения. Формула всегда начинается со знака =. Далее мы набираем арифметическое выражение, которое состоит из операндов

Операнд – величина в выражении, над которой производится операция; арифметические операции.

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

Знаки арифметических операций в Excel: +, -, *, /, ^.

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

Имена ячеек.

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

Присвоить имя ячейке можно следующими способами:

1. Выделить ячейку или блок, щелкнуть мышью в поле имени и набрать в этом поле нужное имя. Ввод имени – Enter.

2. Выделить ячейку или блок, выбрать пункт меню Вставка-Имя-Присвоить. Появляется диалоговое окно.

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

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

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

Имена ячеек в пределах одной рабочей книги не должны повторяться.

Пример: В ячейке А1 задано число 10. Присвоим ей имя ИТОГО.

В ячейке А2 – число 12.

Найти в ячейке В1 сумму 2-х чисел. В1=ИТОГО+А2.

Перейдем на Лист2. В ячейке А2 задано число 35. В ячейке В1 найти сумму чисел, из ячейки Лист1!А1 и ячейки Лист»!А2.

На Листе2 в ячейку В1 заносим формулу В1=ИТОГО+А2.

Значения ошибок в формулах.

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

Виды ошибок, которые распознает Excel.

В ячейке появляется:

#### – число не вмещается в ячейку, чтобы убрать это сообщение, нужно увеличить ширину столбца;

#ДЕЛ/0! – попытка деления на ноль. Возможно, делитель ссылается на пустую ячейку;

#ИМЯ? – Excel не понимает, что за имя или адрес вы использовали в формуле, появляется, когда адрес задан русскими буквами или неверно набрано имя функции;

#ЗНАЧ! – в формуле имеется ссылка на текстовое значение, которое Excel не может преобразовать в числовое;

#ССЫЛКА! – ячейка, к которой обращается формула, была удалена (содержимое неверно);

#ЧИСЛО! – неверно значение аргумента (например, извлекается корень из отрицательного числа);

#Н/Д – значение недопустимо (значение недоступно функции или формуле);

#ПУСТО! – диапазоны не имеют общих ячеек.

Табличные формулы.

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

Ввод табличной формулы.

Рассмотрим пример.

	А	В	С	D
	Год	Приход	Расход	Прибыль
1	1992	200	150	{=B2:B5-C2:C5}
2	1993	360	230
3	1994	410	250
4	1995	200	180

Выделим блок D2:D5. В этом блоке активной ячейкой является D2. Наберем знак равенства =. Выделим блок В2:В5, наберем «-», выделим блок С2:С5, нажмем сочетание клавиш Ctrl+Shift+Enter. В ячейках D2:D5 появятся значения, а в строке формул отразится формула единая для всех ячеек: {=B2:B5-C2:C5}.

Изменение табличной формулы.

При увеличении блока. Добавим дополнительную строку 1996 I 240 I 200

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

Выделим D2:D6, нажимаем клавишу F2 и редактируем формулу. Выделим в формуле подстроку В2:В5 и выделим блок B2:B6, также поступим с блоком C2:C5, либо просто заменим в адресах блоков цифру 5 на цифру 6. Нажимаем сочетание клавиш Ctrl+Shift+Enter.

При уменьшении блока. Удалить из таблицы строку 1996. Выделяем блок с табличной формулой, нажимаем F2 и добавляем в самое начало формулы апостроф (расположен на клавише с буквой Э). Формула превращается в текст. Вводим этот текст во все ячейки (клавиши Ctrl+Enter). Табличная формула прекратила существование. Очищаем последнюю строку таблицы. Выделяем блок D2:D5, нажимаем клавишу F2, удаляем апостроф, нажимаем клавиши Ctrl+Shift+Enter.

Понятие функции.

Функция – это переменная величина, значение которой зависит от значения других величин (аргументов).

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

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

Смысл и порядок следования аргументов однозначно определен описанием функции, составленным ее автором.

Например, если в ячейке G6 записана формула с функцией возведения в степень = СТЕПЕНЬ (А4;2), значением этой ячейки будет значение А4, возведенное в степень 2.

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

Для упрощения ввода функций в Excel предусмотрен специальный Мастер Функций, который можно вызвать нажатием кнопки f_x на панели инструментов «Стандартная». Предварительно необходимо выделить ячейку, куда будете вводить формулу.

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

Выбрав в списке функцию нажать Ок, появится следующее окно – для выбранной функции. В этом окне можно ввести аргумент функции: если аргументом является другая функция, то можно вызвать вложенный Мастер функций (кнопка f_x).

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

	А
1	12
2	10
3	15
4	16
5	30
6

Выделяем ячейку А6

Мастер функций–Статистические–МАКС

Окно для аргумента Число1 Вводим блок А1:А5–Ок

В ячейке А6 появляется значение, а в строке формул: =МАКС(А1:А5)

Логические функции.

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

Пример.

Обработать результаты экзамена и рассчитать начисление стипендии.

	А	В	С		D	E	F	G
1	Ф.И.О.	Оценки за экзамен						Начисл. стипендии
2		Информ.		Экон.	История	Математ.	Балл
3	1.Иванов	5		4	5	4	=СУММ18(В3;F3)
4	2.Зверев	4		5	4	3	16
5	3.Калачев	3		3	4	3	13
6	4.Синицына	4		3	3	4	14
7	5.Писарев	5		3	4	4	16
8

Рассчитать стипендию, используя логическую функцию ЕСЛИ, и исходя из условия, что если суммарный балл 16, то стипендия будет 200 руб., иначе – 400 руб.

Сначала находим суммарный балл. Делаем активной ячейку F3. Воспользуемся Мастером функций. Выбираем Математические–Сумм. Аргумент-блок В3:Е3-Ок. Копируем полученную формулу с помощью маркера заполнения в оставшиеся ячейки. Получим результат. Для оценки среднего балла воспользуемся логической функцией ЕСЛИ. Мастер функций–Логические–ЕСЛИ.

Формат функции ЕСЛИ.

ЕСЛИ (<логическое выражение>, <выражение1>, <выражение2>).

Логическое выражение – правило для вычисления (логического) значения.

Если логическое выражение истинно, т.е. условие выполняется , тогда выполняется <выражение1>, иначе (когда условие не выполняется) выполняется <выражение2>.

=ЕСЛИ(F3 16; 400; 200)

Функция ЕСЛИ может быть вложенной, если суммарный балл 16, то стипендия будет 400 руб., если =16, то 300 руб., иначе 200 руб.

Если выполняется условие F3>16, то выражение примет значение 400. Если не выполняется, то будет проверяться следующее условное выражение.

Число вложенных функций ЕСЛИ не должно превышать 7.

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

Функция «и» принимает значение «истина», если одновременно истинны все логические выражения, указанные в качестве аргументов этой функции, иначе значение функции «и» – ложь.

Формат функции «и» =и(<логич.выражение1>, <логич.выражение2>, ….)

В скобках можно указывать до 30 логических выражений.

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

Формат функции «или» =или(<логич.выражение1>, <логич.выражение2>, …)

Пример1. Определить, существует ли треугольник со сторонами a,b,c. Условия существования треугольника: сумма двух любых сторон больше третьей.

=ЕСЛИ (и(а + в>с; а + с>b; в + с>a); существует, не существует)

Пример2. Назначить стипендию, если нет троек за сессию.

=ЕСЛИ(ИЛИ(В3 3; С3 3; D3 3; Е3 3); нет стипендии, стипендия)

Пример3. Построить график функции на интервале [-2;2], шаг 0,2

Поместим х в ячейку А3

B3=ЕСЛИ (А3 0; 5*А3+3; 3*А3/(5*А3+6))

Пример 4. Построить график функции на интервале [-2;2], шаг 0,2

Числовую прямую можно разделить на 3 отрезка:

С3 = ЕСЛИ (И(А3>= -1; A3<= 1); А3 + 2; А3 – 2)

Пример 5. Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 3000, то 5%; если объем до 10000, то 2%; если выше 10000, то 1,5%. Вычислить размер вознаграждения.

В3 =ЕСЛИ(A3<3000;5%*A3;ЕСЛИ(A3<10000;2%*A3;1,5%*A3))

Математические функции.

Сумма – СУММ (интервал) – вычисляет сумму значений в заданном интервале СУММ(А1:А5).

СУММЕСЛИ – суммирует ячейки, заданные критерием

Синтаксис.

СУММЕСЛИ(диапазон; критерий; диапазон-суммирование:

- Диапазон – диапазон вычисляемых ячеек.

- Критерий – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, «32», «<32», яблоки.

Диапазон–суммирование - фактические ячейки для суммирования.

Ячейки в «диапазон-суммирование» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.

Если «диапазон-суммирование» опущен, то суммируются ячейки в аргументе «диапазон».

Пример.

	А	В
1	Стоимость имущества	Комиссионные
2	100 000	7000
3	200 000	14000
4	300 000	21000
5	400 000	28000

Найти сумму комиссионных для стоимости имущества более 160000.

=СУММЕСЛИ (А2:А5; «>160000»; В2:В5)

Ответ: 63000

КОРЕНЬ (число) – возвращает значение квадратного корня.

СТЕПЕНЬ (число; степень) – возвращает результат возведения в степень. Показатель степени может быть дробным, т.е. корнем любой степени.

ПИ( ) – возвращает округленное до 15 знаков после запятой число Пи.

Статистические функции.

СРЗНАЧ (диапазон) – возвращает среднее значение чисел, содержащихся в ячейках заданного диапазона.

А

1 15

2 13

3 18

4 16

5 = СРЗНАЧ(А1:А4)

СЧЕТЕСЛИ – подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию

СЧЕТЕСЛИ (диапазон; критерий).

Диапазон – диапазон, в котором нужно подсчитать ячейки.

Критерий – критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

«>50» – выражение

«яблоко» – слово

Пример: Подсчитать количество «5»

А

1 4

2 5

3 5

4 4

5 3

6 = СЧЕТЕСЛИ (А1; А5; 5)

Ответ: 2

РАНГ – возвращает ранг числа в списке чисел. Ранг числа – это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией).

РАНГ (число; ссылка; порядок)

Число – число, для которого определяется ранг.

Ссылка – массив или ссылка на список чисел. Нечисловые значения игнорируются.

Порядок – число, определяющее способ упорядочения.

- если порядок =0 или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания.

- если порядок – любое ненулевое число, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Замечания. РАНГ присваивает повторяющимся числам одинаковый ранг. При этом наличие повторяющихся чисел влияет на ранг последующих чисел.

Например, если в списке целых чисел дважды встречается число 10, имеющие ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранг 6).

Пример:

А

1 Данные

2 7

3 3,5

4 3,5

5 1

6 2

Формула:

=РАНГ(А3; А2; А6; 1). Ранг числа 3,5 в приведенном списке (3)

=Ранг (А2; А2; А6; 1). Ранг числа 7 в приведенном списке (5)

ЧАСТОТА – вычисляет распределение значений по интервалам и возвращает вертикальный массив, содержащий на 1 элемент больше, чем массив интервалов.

ЧАСТОТА – вычисляет частоту появления значений в интервале значений и возвращает массив цифр.

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

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

ЧАСТОТА (массив_данных; массив_интервалов)

Массив данных – массив или ссылка на множество данных, для которых вычисляются частоты. Если массив данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей.

Массив_интервалов – массив или ссылка на множество интервалов, в которые группируются значения аргумента массива_данных. Если массив_интервалов не содержим значений, функция ЧАСТОТА возвращает количество элементов в аргументное частота.

Замечания.

- ЧАСТОТА вводится как формула массива после выделения интервала смежных ячеек, в которые нужно вернуть полученный массив распределения.

- количество элементов в возвращаемом массиве на 1 больше числа элементов в массиве массив_интервалов.

Дополнительный элемент в возвращаемом массиве содержит количество значений, больших чем максимальное значение в интервалах.

Например, при подсчете 3-х диапазонов значений (интервалов), введенных в три ячейки, убедитесь в том, что функция ЧАСТОТА возвращает значения в четырех ячейках. Дополнительная ячейка возвращает число значений в массив_данных, больших чем значение границы 3-го интервала.

- ЧАСТОА игнорирует пустые ячейки и тексты.

- формулы, которые возвращают массивы, должны быть введены как формулы массива.

ПРИМЕР.

	А	В
	Баллы	Интервалы
	79	70
	85	79
	78	89
	85
	50
	81
	95
	88
	97

Формула

= ЧАСТОТА (А2:А10; В2:В5)

Описание (результат)

- число баллов в интервале в номером 70 и меньше (1)

- число баллов в интервале 71-79 (2)

- число баллов в интервале 80-89 (4)

- число баллов в интервалах с номером 90 и больше (2)

Примечание. Формулу в этом примере необходимо ввести как формулу массива. Выделите диапазон, начиная с ячейки, содержащей формулу, где будет подсчитана частота (число новое + 1). Нажмите клавишу F2, а затем нажмите клавиши Ctrl+Shift+Enter. Если формула не будет введена, как формула массива, единственное значение будет равно 1.

Функции для работы с датами.

Функция СЕГОДНЯ( ) – отображает сегодняшнее число в формате дата. Если на ячейку, в которую введена функция СЕГОДНЯ( ) наложить формат «Общий», мы увидим количество дней, прошедших от начала ХХ века, т.е. с 1900 до сегодняшнего дня.

Функция относится к категории «дата и время». Эта функция не имеет аргументов.

Работа со списками в Excel. Понятие списка в Excel.

Списком в Excel называют однотабличную базу данных.

Столбцы – это поля.

Строки – записи.

Список должен удовлетворять следующим условиям.

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

• столбцы списка должны содержать однородную информацию, т.е. либо только числовую, либо только текстовую, либо даты;

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

Работа со списками предполагает сортировку, поиск информации в соответствии с критериями, перегруппировку, обработку информации.

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

Для сортировки необходимо поставить курсор в базу данных и выбрать пункт меню Данные–Сортировка.

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

Эти параметры задаются в диалоговом окне Сортировка.

Пример. Имеются данные в 3-х столбцах А, В, С. Зададим сортировку данных по 3-м ключам, по убыванию.

Сначала Excel расставит строки по убыванию (если содержит текст, то по алфавиту от Я до А) данных в столбце А, а внутри групп с одинаковыми значениями А – по убыванию данных в столбце В, внутри групп с одинаковыми значениями В – по убыванию данных в столбце С.

А	В	С
Канал	Товар	Кол-во
Рынок	Ручка	470
Рынок	Ручка	400
Рынок	Альбом	387
Рынок	Альбом	300
Магазин	Тетрадь	860
Магазин	Тетрадь	780
Магазин	Ручка	260

Автофильтр.

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

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

Для выборки сделать следующее:

- поставить курсор в базу данных;

- выбрать пункт меню Данные–Фильтр–Автофильтр, после чего в каждой ячейке верхней строки появится по кнопке со стрелкой, обозначающей наличие какого-то списка.

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

Полученную таблицу можно напечатать. Отфильтрованные строки можно выделить цветом или иным образом отформатировать. Отключив потом режим фильтрации (Данные–Фильтр–Автофильтр), мы получим наглядную разметку таблицы.

Отбор по нескольким полям

Усложним задачу: выведем количество ручек проданных с рынка. Для этого выберем соответствующие элементы в выпадающих списках: в столбце Канал–Рынок, в столбце Товар – ручка. В результате получим только строки, содержащие информацию о ручках, проданных с рынка.

С помощью Автофильтра можно решать и более сложные задачи.

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

Например, сделать выборку товаров, проданных из магазина количеством от 200 до 800 шт.

Курсор в базу данных. Данные–Фильтр–Автофильтр. Щелкаем по стрелке в ячейке Количество. В выпадающем меню выбираем Условие, после чего появится диалоговое окно, в котором можно задать условие.

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

Отмена режима фильтрации: Данные–Фильтр–Автофильтр. Появится исходная таблица, исчезнут кнопки со стрелками в заголовках.

Расширенный фильтр.

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

Расширенный фильтр позволяет:

1. Сразу копировать отфильтрованные запасы в другое место рабочего листа, на котором находится исходный список.

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

3. Показывать в отфильтрованных записях не все столбцы, а только указанные.

4. Объединять операторами И, ИЛИ условия для разных столбцов.

5. Для одного столбца объединять операторами И, ИЛИ боле двух условий.

6. Создавать вычисляемые критерии.

7. Выводить только уникальные значения.

Рассмотрим работу расширенного фильтра на примерах.

А	В	С	D	E	F
Канал	Товар	Кол-во	Цена	Выручка	Дата
Магазин	Тетрадь	2000	2	400
Магазин	Ручка	1000	1,5	1500
Рынок	Тетрадь	3000	2,5	7500
Рынок	Ручка	2000	1,8	3600
Рынок	Альбом	500	7	3500
Магазин	Тетрадь	10000	2,3	23000
Магазин	Ручка	5000	1,7	8500
Магазин	Альбом	300	6,5	1950
Рынок	Тетрадь	5000	2,8	14000

Применив расширенный фильтр, сделать выборку тетрадей, проданных с рынка по цене от 2 до 2,9 руб.

Для выборки сделать следующее:

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

Создадим таблицу критериев в ячейках А12: D13.

Для этого в ячейки А12: D12 введем имена полей, по которым необходимо сделать выборку. Лучше нужные поля скопировать из заголовка базы данных во избежание ошибок. «Канал» в ячейку А12; «Товар» в ячейку В12; «Цена» в ячейки С12 и D12.

	A	B	C	D
12	Канал	Товар	Цена	Цена
13	Рынок	Тетрадь	>=2	<=2,9

В ячейки А13: D13 ввести условия выборки, т.е. в ячейку А13 скопировать слово «рынок» из списка, в ячейку В13 «тетрадь». В ячейкиС13 и D13 вводим условия С13: >=2; D13: <=2,9

2. Выбрать в меню пункт Данные–Фильтр–Расширенный фильтр.

Появится диалоговое окно.

В окне щелкнуть по «Скопировать результат в другое место», указать исходный диапазон базы данных, диапазон критериев и ячейку, с которой будет начинаться диапазон для результата выборки.

3. Далее щелкнуть ОК. В результате в ячейках, начиная с А20 вправо и вниз, расположится таблица, в которой будут выведены тетради, проданные на рынке по цене от 2 до 2,9 р.

	A	B	C	D	E	F
20	Канал	Товар	Кол-во	Цена	Выручка	Дата
21	Рынок	Тетрадь	3000	2,5	75000
22	Рынок	Тетрадь	5000	2,8	14000

Условие, записанное в одну строку, соответствует логической функции «И».

Рассмотрим пример с использованием функции «ИЛИ».

Сделать выборку тетрадей и ручек, проданных на рынке.

Составляем таблицу критериев:

Канал	Товар
Рынок	Тетрадь
Рынок	ручка

Условие критерия может быть задано формулой. Дана база данных.

	A	B	C	D	E
1	Канал	Товар	Кол-во	Цена	Дата
2	Магазин	Тетрадь	2000	2	19.03.04
3	Магазин	Ручка	1000	1,5	20.03.04
4	Рынок	Тетрадь	3000	2,5	18.03.04
5	Рынок	Ручка	2000	1,8	26.03.04
6	Рынок	Альбом	500	7	25.03.04

Выбрать товар, проданный на рынке с выручкой более 3500.

Составляем таблицу критериев.

Канал	Сумма
Рынок	=B2*C6>3500

Работа с датами с использованием расширенного фильтра.

Пример: выбрать тетради, поступившие неделю назад на сумму меньше 7000 руб.

Таблица критериев:

Товар		Дата
тетрадь	=С2*D2<7000	=СЕГОДНЯ()-7

Выделить проданный товар с 20.03.04 по 26.03.04

Дата	Дата
>20.03.04	<25.03.04

Выделить проданный товар, кроме промежутка с 20.03.03 по 26.03.04

Дата	Дата
>20.03.04	<25.03.04

Выделить количество товара, поступившего в течение последней недели.

=Е2>СЕГОДНЯ()-7

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

Построим таблицу следующего вида:

	Канал
Товар	Данные кол-во

В меню Данные выбираем команду Сводная таблица. Откроется диалоговое окно Мастера сводных таблиц, т.к. нужно построить сводную таблицу на основе списка (базы данных) рабочего листа MS Excel, то на 1-м шаге Мастера сводных таблиц оставляем опцию «В списке или базе данных MS Excel». Далее.

В поле Диапазон следующего диалогового окна уже должен быть введен адрес списка (проверяем). Далее.

Далее на следующем шаге появится диалоговое окно построения сводной таблицы

Для получения таблицы, нужно кнопку «Товар» перетащить в область «Строка», кнопку «Канал» в область «Данные».

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

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

Готово.

Можно быстро изменить данные сводной таблицы с помощью панели инструментов «Запрос и сводная таблица» или щелкнув правой кнопкой мыши по таблице.

Поиск оптимальных решений.

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

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

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

Или та же задача, но в несколько другой постановке: какой формы должен быть кувшин заданного объема, чтобы его стоимость была минимальной.

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

Какая-либо другая постановка задачи проектирования просто невозможна.

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

Виды задач.

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

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

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

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

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

И это вместо того, чтобы сначала рассчитать, а потом начать работать.

Любое решение, а особенно оптимальное принять очень трудно, не выбрав критерия.

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

И еще, оказывается, что при решении почти каждого вопроса необходимо знать ответы на дополнительные бесчисленные вопросы, которые можно объединить в 2 группы:

- что будет, если….

- что надо, чтобы…

В это время вопросы анализа, без ответа на которые обоснованное решение принять невозможно. Основные вопросы, требующие решения: если, еще и не всегда сформулированы, то объективно уже существуют:

- проектирование изделия: либо заданной стоимости с наилучшими свойствами, либо с заданными свойствами наименьшей стоимости;

- задачи распределения ресурсов вообще и задачи распределения ресурсов во времени;

- выбор критерия;

- анализ принимаемого решения.

Основные методы решения задач.

Человек хорошо или плохо решает все возникающие перед ним задачи.

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

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

Принятие формализованных решений – наука. Оно базируется на двух основных методах: логическом моделировании и оптимизации.

Принятие оптимальных решений базируется на «трех китах»:

Основа принятия оптимальных решений.

• математической модели

• решении задачи на компьютере

• исходных данных.

Математическая модель

Математическое моделирование имеет два существенных преимущества:

• дает быстрый ответ на поставленный вопрос

• предоставляет возможность широкого экспериментирования, осуществить которое на реальном объекте зачастую просто невозможно.

Чтобы моделирование было успешным, надо выполнить 3 правила:

• учитывать главные свойства моделируемого объекта

• пренебрегать его второстепенными свойствами

• уметь отделить главные свойства от второстепенных

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

Поэтому на втором этапе необходимо отделить главное от второстепенного, что сделать довольно сложно.

Составление модели – это искусство, творчество. До какого-то уровня научить можно, но не более того.

Алгоритмы принятия решений настолько сложны, что без применения компьютера реализовать их практически невозможно.

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

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

Пример математической модели.

Будем проектировать бак, имеющий форму прямоугольного параллелепипеда, объем которого

V=abh (1)

Где а, b, h – стороны бака.

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

Требуется определить размеры бака объемом V=2000, чтобы на его изготовление пошло как можно меньше материала, площадь которого

S=2[ab+(a+b)h] (2)

Такая постановка задачи может быть записана следующим образом:

(3)

Эта запись читается так: минимизировать величину S при условии, что V=2000.

Подставим в (3) значения V(1) и S(2), тогда получим:

(4)

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

Это условие запишем так:

a, b, h > 0

Тогда получим нашу первую математическую модель задачи поиска оптимального решения:

ЦФ

ОГР

ГРУ

(5)

Эта модель состоит из 3-х составляющих:

• целевой функции (ЦФ)

• ограничения (ОГР)

• граничных условий (ГРУ)

В общем виде система записывается следующим образом:

1. ЦФ – целевая функция или критерий оптимизации, показывает, в каком случае решение должно быть оптимальным, т.е. наилучшим.

Возможны 3 вида назначения целевой функции:

- максимизация

- минимизация

- назначение заданного значения

2. ОГР – ограничения устанавливают зависимости между переменными. Они могут быть как односторонними, например:

так и двусторонними

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

3. ГРУ – граничные условия показывают, в каких пределах могут быть значения искомых переменных в оптимальном решении.

Решения задачи, удовлетворяющее всем ограничениям и граничным условиям, называются допустимыми.

Основные этапы работ при принятии оптимальных решений.

1. Выбор задачи – это важнейший вопрос. Если задача выбрана неудачно, то она может и не решиться методами оптимизации.

Задача должна удовлетворять требованиям:

• Должно существовать минимум два варианта ее решения. Если вариантов решения нет, значит и выбирать не из чего.

• Надо четко знать, в каком смысле искомое решение должно быть наилучшим. Если мы четко не знаем, чего хотим, то математические методы, реализованные даже на самом лучшем компьютере не помогут.

Выбор задачи завершается ее содержательной постановкой.

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

   • Исходные данные: величины итерминированные или случайные.

   • Искомые переменные: непрерывные или дискретные.

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

   • Зависимости между переменными: линейные или нелинейные.

   • Критерии, по которым следует находить оптимальное решение.

Хорошо сформированная содержательная постановка – основа успешного составления математической модели.

3. Составление математической модели – очень ответственный этап работ.

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

5. Решение задачи.

6. Анализ решения.

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

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

Пример решения задач оптимизации.

Excel предлагает мощный инструмент для решения оптимизационных задач, т.е. таких задач, в которых необходимо найти экстремальное значение (min или max) некоторой функции, называемой целевой, при заданных ограничениях.

Если целевая функция и/или ограничения линейны, то такие задачи принято называть задачами линейного программирования.

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

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

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

Рассмотрим задачу.

В цехе, площадью 74м² необходимо установить станки, на приобретение которых отпущено 420 тыс.руб.

Существует два типа станков. Станок первого типа стоимостью 60 тыс.руб., требующий 12м² производственных площадей обеспечивает изготовление 70 изделий в смену. Аналогичные характеристики станка второго типа составляют соответственно 40 тыс.руб., 6м², 40 изделий в смену.

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

Обозначим Х₁ количество станков первого типа, а Х₂ – количество станков второго типа, которые предполагается установить в цехе.

Тогда количество изделий, которое будет произведено на этих станках равно

Это и есть целевая функция, которую нужно максимизировать.

Теперь запишем ограничения. Их в задаче два.

Ограничения по финансам:

тыс.руб.

Ограничения по площади размещения:

м²

Кроме этих ограничений следует добавить очевидные ограничения:

- переменные задачи должны быть неотрицательные

- переменные задачи должны быть целочисленные

Итак, математическая модель сформулирована

ЦФ

ОГР

ГРУ

Решение оптимизационных задач в Excel проводится с помощью специализированной программы Поиск решения, вызываемой из Главного меню: Сервис–Поиск решения.

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

Таким образом, теперь задача состоит в том, чтобы перенести математическую модель в Excel.

Алгоритм следующий

1. Отводим ячейки для каждой независимой переменной задачи. В нашем примере это ячейки В4 для Х₁ и ячейка В5 для Х₂. Их оставляют пустыми.

2. Отводим ячейку С13 для целевой функции и набираем в ней соответствующую формулу:

=В4*Е4+В5*Е5

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

3. Отводим ячейки (А13 и В13) для создания

	A	B	C	D	E
1
2 3	Тип	Кол-во шт.	Стоимость тыс.руб.	Площадь кв.м.	Произв. Дет/смен
4	Стан 1 типа		60	12	70
5	Стан 2 типа		40	6	40
6
7	Ограничения
8	По стоимости	По площади
9	420	74
10
11 12	Выполнение ограничений по стоимости по площади		Производство деталей
13	=В4*С4+В5*С5	=В4*D4+B5*D5	=В4*Е4+В5*Е5
14

формул, соответствующих левой части каждого ограничения:

=В4*С4+В5*С5

=В4*D4+B5*D5

4. Открываем диалоговое окно Поиск решения.

5. В поле Установить целевую ячейку указываем адрес ячейки, в которой находится формула для расчета целевой функции (С13). Ниже указываем тип оптимизации (поиск max и min).

6. В поле Изменяя ячейки отмечаем адреса ячеек, где находятся независимые переменные задачи (В4 и В5).

7. Для того, чтобы ввести ограничения, нужно нажать на кнопку Добавить. Появляется диалоговое окно Добавления ограничения.

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

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

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

Теперь, если необходимо, в поле Ограничения окна Поиск решения можно выбирать какие-либо ограничения и редактировать их или удалять

8. Запускаем процесс вычислений нажатием кнопки Выполнить. Решение: заданным ограничениям удовлетворяет следующий парк станков: 3 – первого типа, 6 – второго типа; при этом будет изготовлено max количество деталей – 450.

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

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

Задать точность, с которой ищется решение.

Установить вид зависимости модели (линейная, нелинейная) и другие параметры.

Понятие алгоритма.

Слово «Алгоритм» происходит от «algorithmi» латинского написания имени аль-Хорезми, под которым в средневековой Европе знали величайшего математика из Хорезма (город в Узбекистане) Мухаммеда бен Нусу, жившего в 783-850 г.г.

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

Алгоритм – точное предписание, определяющее последовательность действий, обеспечивающих получение требуемого результата их исходных данных.

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

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

Такой субъект или объект принято называть формальным исполнителем.

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

Человек тоже может выступать в роли формального исполнителя, но в 1-ю очередь формальными исполнителями являются различные автоматические устройства, и компьютер в том числе.

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

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

Совокупность допустимых действий образует систему команд исполнителя.

Алгоритм должен содержать только те действия, которые допустимы для данного исполнителя.

Свойства алгоритмов.

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

1. Дискретность (прерывность, раздельность) – алгоритм должен представлять процесс решения задачи как последовательное выполнение простых (ранее определенных) шагов.

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

2. Определенность – каждое правило алгоритма должно быть четким, однозначным и не оставлять места для произвола. Благодаря этому свойству выполнение алгоритма носит механический характер и не требует никаких дополнительных указаний или сведений о решаемой задаче.

3. Результативность (конечность) – алгоритм должен приводить к решению задачи за конечное число шагов.

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

Правила выполнения арифметических операций или геометрических построений представляют собой алгоритмы.

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

1 правило – при построении алгоритма, прежде всего, необходимо задать множество объектов, с которыми будет работать алгоритм. Формализованное (закодированное) представление этих объектов носит название данных.

Алгоритм приступает к работе с некоторым набором данных, которые называются входными, и в результате своей работы выдает данные, которые называются выходными. Таким образом, алгоритм преобразует входные данные в выходные.

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

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

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

В языках программирования распределение памяти осуществляется операторами описания переменных.

3 правило – дискретность. Алгоритм строится из отдельных шагов (действий, операций, команд). Множество шагов, из которых составлен алгоритм, конечно.

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

5 правило – сходимость (результативность). Алгоритм должен завершить работу после конечного числа шагов. При этом необходимо указать, что считать результатом работы алгоритма.

Итак, алгоритм каждому набору входных данных ставит в соответствие некоторый набор выходных данных.

Виды алгоритмов.

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

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

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

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

4 Линейные алгоритмы – наборы команд (указаний), выполняемых последовательно во времени друг за другом.

5 Разветвляющиеся алгоритмы – алгоритмы, содержащие хотя бы одно условие, в результате проверки которого ЭВМ обеспечивает переход на один из 2-х возможных шагов.

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

Цикл программы – последовательность команд, которая может выполняться многократно до удовлетворения некоторого условия.

Формы представления алгоритмов.

1. Словесная – записи на естественном языке.

2. Графическая – изображения из графических символов.

3. Программная – тексты на языках программирования.

Словесное описание алгоритмов.

Пример – инструкции, рецепты.

Получил значительно меньшее распространение из-за его многословности и отсутствия наглядности.

Рассмотрим пример – алгоритм нахождения максимального из 2-х значений:

1 определим форматы переменных X, Y,M, где X и Y – значения для сравнения, М – переменная для хранения максимального значения.

2 Получим два значения чисел Х и Y для сравнения.

3 Сравним Х и Y

4 Если Х меньше Y, значит большее число Y.

5 Поместим в переменную М значение Y.

6 Если Х не меньше (больше) Y, значит больше число Х.

7 Поместим в переменную М значение Х.

Блок-схема алгоритма.

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

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

Графическое изображение алгоритма широко используется перед программированием задачи вследствие его наглядности.

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

Блок-схема алгоритма служит для упрощения изображения алгоритма.

Условные графические обозначения в схемах алгоритмов.

Начало

1. Пуск-останов. Начало, конец алгоритма

a=b+c

2. Процесс Действие, вычислительная

о перация

a c b

да

нет

3 . Решение Проверка условий

4. Предопределенный Вычисления по подпрограм.

Процесс стандартной программе

5. Ввод-вывод Ввод-вывод в общем виде

6

Печать

b, c

. Документ Вывод результатов на печать

i=R,50,2

7. Модификация Начало цикла

Блок-схема алгоритма нахождения максимального из двух значений.

Начало

x, y

Да

x<y

Нет

max=y

max=x

Конец

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

Блок «решение» используется для обозначения переходов управления по условию.

Блок «модификация» используется для организации циклических конструкций (модификация – видоизменение, преобразование).

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

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

Программное представление алгоритма.

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

Однако на практике в качестве исполнителей алгоритмов используются автоматы (станки с ЧПУ) или компьютеры. Поэтому алгоритм, предназначенный для исполнения автоматическим устройством или компьютером должен быть записан на понятном ему языке.

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

Следовательно, язык для записи алгоритмов должен быть формализован. Такой язык принято называть языком программирования, а запись алгоритма на этом языке – программой для компьютера.