Использование вложенных функций в формулах

Задание. Пусть в ячейках D6 листов Экзамен1, Экзамен2 и Экзамен3 находятся оценки. В ячейку F8 листа Итоги нужно поместить «да», если все эти оценки – пятерки, а в противном случае «нет». В формуле будем использовать функцию ЕСЛИ и вложенную в нее функцию И. Применение двух функций (функции ЕСЛИ и функции И)обусловлено необходимостью проверки не одного, а нескольких условий.

 

Порядок формирования формулы таков:

Щелкнуть в ячейке F8 листа Итоги.

Нажать кнопку Панели инструментовили выбрать пункт меню Вставка-Функция

В диалоговом окне выбрать Категория: Логические, Функция: ЕСЛИ, нажать ОК

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

Поместить курсор в строку Лог_выражение и затем в окне таблицы щелкнуть раскрывающийся список функций в строке адреса, которая находится слева от строки формул , и выбрать в нем функцию И. (Примечание: для выбора функции И может понадобиться сначала выбрать строку Другие функции, затем категорию Логические, функцию И). После выбора этой функции появится диалоговое окно Аргументы функции уже для вложенной функции И.

Установить курсор в строке Логическое_значение1 диалогового окна Аргументы функции И. Перейти на лист Экзамен1, щелкнуть по ячейке D6 и в поле Логическое_значение1 дописать «=5», чтобы получилась следующая запись: Экзамен1!D6=5

Аналогичным образом (выбирая нужные ячейки на разных листах книги и записывая условие сравнения =5) заполнить строки Логическое_значение2 и Логическое_значение3, затем нажать ОК.

В строке формул ячейки F8 листа Итоги должна будет отобразиться формула:

=ЕСЛИ(И(Экзамен1!D6=5;Экзамен2!D6=5;Экзамен3!D6=5);”да”;”нет”)

 

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

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

Задание. Пусть в ячейке В5 находится дата поступления товара на склад. В ячейку D6 нужно поместить формулу для вычисления количества дней, прошедших с этой даты до настоящего момента.

Для получения текущей даты нужно использовать функцию СЕГОДНЯ. Формула в ячейке D6 будет иметь вид: =СЕГОДНЯ()-В5

Примечание: ячейка с результатом вычислений (ячейка D6) должна иметь числовой формат, а ячейка В5 формат даты.

Построение диаграмм

Построение диаграммы по данным таблицы Еxcel выполняется в несколько этапов:

1-й этап. Выбор типа диаграммы и ее вида.

2-й этап. Указание диапазона данных для диаграммы.

3-й этап. Задание подписей к осям диаграммы, ее названия и, если, необходимо, Легенды (легенда – надпись, поясняющая смысловое содержание разноцветных столбиков или секторов диаграммы).

4-й этап. Задание места расположения диаграммы.

 

Диаграммы можно строить в различной последовательности:

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

2-й способ. Сначала вызвать Мастер диаграмм, а затем, в процессе построения, выбирать из таблицы данные, по которым строится диаграмма.

 

2.3.1. Построение диаграммы по данным таблицы Excel

Пусть создана таблица, приведенная ниже:

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

Выделить мышью блок ячеек с данными, включая заголовки столбцов А2:D7

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

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

В появившемся диалоговом окне на вкладке Стандартные выбрать тип Гистограмма, вид – первая в верхнем ряду; щелкнуть кнопку Далее

Появится диалоговое окно Мастера диаграмм с изображением диаграммы:

 

 

Полученная диаграмма нас устраивает. Нажмем кнопку Далее.

 

В новом диалоговом окне перейдем на вкладку Заголовки и введем текст «Распределение учебных часов» в строку Название диаграммы и названия осей: «группы» в строку Ось Х (категорий) и «часы (в семестр)» в строку Ось У (значений).

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

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

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

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

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

2.3.2. Редактирование диаграммы

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

При выборе пункта меню Формат диаграммы … можно выполнить форматирование диаграммы (см. п.2.3.3).

При выборе пункта меню Тип диаграммы… можно изменить тип и вид имеющейся диаграммы.

При выборе пункта меню Параметры диаграммы… можно: на вкладке Заголовки диалогового окна изменить (добавить, удалить) заголовок диаграммы и названия осей диаграммы, а на вкладке Легенда добавить или удалить Легенду с диаграммы.

При выборе пункта меню Исходные данные… можно изменить, добавить, удалить данные, используемые для построения диаграммы, а также поменять расположение данных по осям (например, в трехмерной диаграмме поменять местами данные, отображаемые по оси Х, с данными по оси Z).

Рассмотрим подробнее редактирование диаграмм на примере гистограммы, построенной в п. 2.3.1. Для редактирования вызовем контекстное меню в области диаграммы, выберем пункт меню Исходные данные… и перейдем на вкладку Ряд диалогового окна.

Ниже приведены диалоговое окно Исходные данные и измененная таблица данных:

 

 

Ряды данных, для которых построена диаграмма (выделен ряд «история») Адрес ячейки с названием выделенного ряда данных (ячейка В2) Адреса ячеек, в которых находятся данные, использованные для построения выделенного ряда (блок ячеек В3:В7) Адреса ячеек, в которых содержатся текстовые или числовые значения, взятые в качестве подписей по оси Х на диаграмме (блок ячеек А3:А7)

Задание. Добавить в имеющуюся диаграмму данные по иностранному языку, которые были добавлены в таблицу «Часы (в семестр)» в ячейки Е2:Е7. Ход выполнения работы:

Необходимо добавить новый ряд данных на диаграмму, поэтому щелкнем по кнопке Добавить под списком Ряд (Примечание: если же необходимо убрать данные с диаграммы, то лишний ряд данных в списке Ряд выделяется и нажимается кнопка Удалить).

Будет добавлен новый ряд с именем Ряд4 (т.к. это четвертый по счету ряд). Для изменения этого названия на «ин. язык» установим курсор в строке Имя и либо введем название с клавиатуры, либо щелкнем мышью в ячейке Е2 таблицы данных. Затем установим курсор в строке Значения, сотрем отображенную там информацию и выделим мышью нужный диапазон ячеек в таблице данных Е3:Е7. На диаграмме сразу отразятся сделанные изменения.

Можно изменить вид диаграммы так, чтобы по оси Х отражались названия предметов (а не номера групп, как в нашем случае), а номера групп фигурировали в Легенде. Для этого перейдем на вкладку Диапазон данных и проверим, отражен ли в строке Диапазон блок данных таблицы, по которым строилась диаграмма (в нашем случае это ячейки А2:Е7, с указанием имени листа). Если строка Диапазон пуста, выделим мышью этот блок ячеек и убедимся, что он отразился в строке. Изменим установку переключателя Ряды в с позиции столбцах на позицию строках. Вид диаграммы изменится. Ниже приведены различные виды одной и той же диаграммы:

Расположение Ряды в столбцах

Расположение Ряды в строках

2.3.3. Форматирование (изменение размеров и расположения) всей диаграммы в целом и отдельных ее частей

Рассмотрим форматирование диаграмм на примере трехмерной гистограммы

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

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

 

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

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

 

Основные составляющие диаграммы и их месторасположение:

 

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

 

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

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

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

В диалоговом окне форматирования оси можно изменить размер, начертание и цвет шрифта (вкладка Шрифт), угол наклона подписей по оси (вкладка Выравнивание, область Ориентация), начертание делений по оси (вкладка Вид). На вкладке Шкала можно изменить: для осей с текстовыми подписями - число подписей между делениями, например, подписывать каждое деление, а не через одно как на приведенной выше диаграмме; а для осей с числовыми значениями – изменить цену основных и промежуточных делений, например, установить цену делений 30, а не 50, как на приведенной выше диаграмме.

Диаграмма после форматирования.

 

Примечание. В трехмерных диаграммах можно также изменять угол наклона диаграммы.

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

 

2.3.4. Построение тренда

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

Примечание: тренды могут быть построены только на плоских (не объемных) видах диаграмм.

Задание. Для приведенной гистограммы построить линейный и полиномиальный тренды

Ход работы:

 

Устанавливаем на любой столбик гистограммы указатель мыши и вызываем контекстное меню правой кнопкой мыши, выбираем команду Добавить линию тренда

.

 

 

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

Можно произвести форматирование линии тренда, наведя на нее указатель мыши, вызвав контекстное меню и выбрав из него команду Формат линии тренда… Воспользуемся этим и на вкладке Вид изменим тип линии на пунктирный. Нажмем ОК. “Захватив” мышью уравнение линии при нажатой левой клавише мыши перенесем его (уравнение) на свободное место диаграммы.

Теперь произведем аппроксимацию гистограммы полиномом третьей степени. Опять, наведя указатель мыши на любой столбик диаграммы, вызовем контекстное меню и выберем Добавить линию тренда. На вкладке Тип выберем вид линии тренда (полиномиальная, степень 3). На вкладке Параметры установим переключатель показывать уравнение на диаграмме и нажмем ОК. Переместимуравнение полинома на свободное место в области диаграммы. Получим:

Можно рассмотреть несколько типов трендов и выбрать ту функцию, которая наиболее точно аппроксимирует искомую диаграмму. Для оценки точности аппроксимации нужно навести указатель мыши на линию тренда, вызвать контекстное меню, выбрать команду Формат линии тренда… и на вкладке Параметры установить переключатель поместить на диаграмму величину достоверности аппроксимации (R^2).

 

Создание таблиц подстановки

 

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

Задача: денежный вклад помещается владельцем в банк под определенный процент на определенный срок. Параметры задачи: размер вклада (руб), срок вклада (лет), процентная ставка (%), сумма возврата вклада (руб), вычисляемая как произведение размера вклада на коэффициент увеличения вклада. При начислении сложных процентов коэффициент увеличения вклада вычисляется по формуле:

Коэффициент = (1+процентная ставка)^{срок вклада}

 

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

Задание 1. Определить влияние изменения срока вклада на сумму возврата вклада.

 

 

Переменной величиной в нашем случае является срок вклада (ячейка В4 в этом случае называется ячейкой ввода). Возможные значения срока вклада (с 5 до 10 лет) поместим в смежные ячейки одной строки или одного столбца, например, ячейки С10:Н10

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

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

 

Выделяем диапазон ячеек В10:Н11, содержащий подставляемые значения срока вклада, расчетную формулу и ячейки, в которые будут помещены результаты расчета и затем выполняем команду Данные – Таблица подстановки. (В приведенном примере в ячейках А10 и А11 находятся поясняющие надписи, их выделять не нужно). В диалоговом окне Таблица подстановки в строке Подставлять значения по столбцам в: нужно ввести абсолютный адрес ячейки ввода, (в нашем случае это ячейка $В$4), затем нажать ОК.

Получим результат:

Примечание: если возможные значения переменной величины расположить в смежные ячейки столбца, а не строки, то в диалоговом окне Таблица подстановки адрес ячейки ввода записывается в строке Подставлять значения по строкам в:

 

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

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

Переменными величинами в этом случае являются срок вклада (ячейка В4) и процентная ставка (ячейка В5). Возможные значения срока вклада (с 5 до 10 лет) поместим в ячейки строки, например, ячейки С10:Н10, а возможные значения процентной ставки (от 3% до 10%) – в ячейки столбца В11:В18. Формула для определения значений должна находиться в месте пересечения столбца и строки с подставляемыми значениями, поэтому в угловую ячейку, находящуюся на пересечении столбца и строки (в нашем случае В10), поместим расчетную формулу. Получим:

Выделяем диапазон ячеек В10:Н18, содержащий формулу и оба набора данных для подстановки. (В ячейках А10 и В9 размещены поясняющие надписи, их выделять не нужно). Выполняем команду Данные – Таблица подстановки.. В диалоговом окне в строке Подставлять значения по столбцам в: указываем адрес параметра, значения которого расположены в строке (в нашем случае это параметр срок вклада, ячейка ввода $В$4), а в строке Подставлять значения по строкам в: указываем адрес ячейки ввода для параметра, значения которого расположены в столбце (в нашем случае это параметр процентная ставка, ячейка ввода $В$5). Нажимаем ОК. Получим результат:

 

Задачи оптимизации

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

 

Задача. На выпуск 1 тыс. изделий А затрачивается 3 т. металла и 3 тыс. кВтч электроэнергии, а на выпуск 1 тыс. изделий В затрачивается 1 т. металла и 6 тыс. кВтч электроэнергии. На рынке можно реализовать не более 5 тыс. изделий А и не более 7 тыс. изделий В. От реализации 1 тыс. изделий А фирма получает прибыль 400 тыс. руб., а от реализации 1 тыс. изделий В – прибыль 200 тыс. руб. Выделенные ресурсы на производство всех видов изделий: 25 т металла и 45 тыс. кВтч электроэнергии. Составить модель и определить, выпуск какого количества изделий А и В обеспечит максимум прибыли при выполнении оговоренных ограничений.

Ход выполнения работы:

Произвольные ячейки, например, А1 и А2 выделим для хранения количества изделий вида А и В соответственно (в тыс. шт.). Запишем в них начальные значения, например, 5 и 7. В ячейку А4 введем формулу вычисления целевой функции (функции вычисления прибыли): =А1*400+А2*200. В ячейку В1 введем формулу вычисления затрат электроэнергии: =3*А1+6*А2. В ячейку B2 введем формулу вычисления затрат металла: =3*А1+ А2

 

Выполняем команду меню Сервис – Поиск решения. В появившемся диалоговом окне Поиск решения в строке Установить целевую ячейку введем адрес ячейки с целевой функцией (в нашем случае это $А$4) и установим переключатель в положение максимальному значению. В строке изменяя значения установим адреса ячеек, значения которых будут изменяться в процессе поиска решения (в нашем случае это ячейки с количеством изделий $А$1 и $А$2):

В поле Ограничения нужно оговорить накладываемые по условию задачи ограничения. Таких ограничений в нашем случае четыре: 1. на количество изделий А (не более 5 тыс.); 2. на количество изделий В (не более 7 тыс.); 3. на общие затраты металла (не более 25 т); 4. на общие затраты электроэнергии (не более 45 кВтч). Ввод каждого нового ограниче-ния производится после нажатия кнопку Добавить.

 

Нажмем кнопку Выполнить и увидим результаты проведенной оптимизации:

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