Green brown cyan lightgray 11 глава

Green brown cyan lightgray 11 глава

Рис.4.29. Построение сводной таблицы

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

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

  • на первом шаге выбирается источник данных, для нашего примера они хранятся в базе данных листа Excel и нажимается кнопка Далее;
  • на втором шаге определяется диапазон адресов исходных данных и выполняется переход к шагу 3 командой Далее;
  • на третьем шаге, являющимся самым главным, осуществляется выбор структуры создаваемой сводной таблицы, шаг начинается с нажатия кнопки Макет (см. рис.4.29), после чего на экране появляется окно, представленное на рис.4.30.

Green brown cyan lightgray 11 глава

Рис.4.30. Макет сводной таблицы

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

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

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

Green brown cyan lightgray 11 глава

Рис.4.31.Результирующая сводная таблица

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

4.8. Решение типовых задач средствами Excel

4.8.1.Подбор параметров

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

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

Рассмотрим задачу подбора параметра на примере анализа объема продаж туров в соответствии с таблицей на рис.4.32. В ячейке В5 приведена формула расчета прибыли, в которой стоимость тура является переменной, а остальные параметры константами, поэтому нужную прибыль в 11000 у.е. в данном примере будем получать путем подбора стоимости тура (ячейка В2) . Для подбора искомого параметра поместите курсор в ячейку В5 необходимо выполнить командуСервис-Подбор параметра, в результате чего появится окно для подбора параметра (рис 4.32). Введите Значениеприбыли 11000 уе, адрес изменяемой ячейки В2 и нажмите ОК. В результате перечисленных действий получены следующие значения (рис.4.33).

Green brown cyan lightgray 11 глава

Рис.4.32. Окно подбора параметра

Green brown cyan lightgray 11 глава

Рис.4.33. Результат подбора параметра

4.8.2.Анализ и прогнозирование данных

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

Для такой постановки задачи ее математическая формулировка может выглядеть следующим образом. Имеется зависимость переменной E от переменной X, полученная путем эксперимента E=F(X). Требуется построить аналитическое описаниедляфункции T =F(X),где T(X) — некоторая функция от X, наилучшим образом описывающая наблюдаемые экспериментальные значения E. Обычно T =F(X) следует выбирать так, чтобы минимизировать сумму квадратов разностей между экспериментальными и теоретическими значениями E и T, т.е. минимизировать некоторый функционал:

Green brown cyan lightgray 11 глава

где n — число наблюдений. При решении такой задачи главной проблемой является выбор некоторой математической функции, позволяющей наиболее достоверно описывать полученные экспериментальные данные и прогнозировать ожидаемые результаты. В Excel существует возможность рассчитывать наиболее подходящую линию, которая с некоторой точностью описывает экспериментальные данные, которую называют линией тренда. Линия тренда — статистический инструмент, представляющий собой линию T, построенную на основе данных диаграммы Е с использованием некоторой аппроксимации. В некоторых случаях этими рассчитанными результатами можно воспользоваться для анализа тенденций рынка сбыта некоторой продукции и краткосрочного прогнозирования.

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

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

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

Green brown cyan lightgray 11 глава

Рис.4.34. Подбор линии тренда

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

Достаточно актуальной является задача построения аналитических зависимостей для функций от двух и более переменных, Для подобного рода зависимостей аппроксимацию можно выполнить, используя функции из статистической группы: ЛИНЕЙН и ЛГРФПРИБЛ. Функция ЛИНЕЙН выполняет статистическую оценку для ряда с использованием метода наименьших квадратов для вычисления аппроксимирующей зависимости. Функция возвращает массив, описывающий полученную функцию.

4.8.3. Использование логических функций в Excel

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

Функция ЕСЛИ.Функция ЕСЛИпозволяет реализовывать вычисления с использованием проверки логических условий, в качестве которых используются операторы =, , и т.п. Синтаксис для записи функции ЕСЛИ имеет следующий вид:

=ЕСЛИ(условие; значение, если_истина ;значение, если_ложь)

Например, выражение =ЕСЛИ(В27;5;13) возвращает значение 5, если значение в ячейке В2 больше 7, иначе возвращает значение 13. В качестве аргументов в функции ЕСЛИ можно использовать также другие функции, например можно использовать текстовые аргументы, например: =ЕСЛИ(В2 А2;Старт;Финиш). Функция ЕСЛИ может также использовать другие функции ЕСЛИ, как вложенные, до 7 уровней вложения.

Функции И, ИЛИ, НЕ.Функции И (AND), ИЛИ (OR), НЕ (NOT) позволяют создавать сложные логические выражения и их можно использовать совместно с операциями сравнения. Функции ИЛИ, И включают до 30 аргументов и их синтаксис имеет следующий вид:

  • =И(логическое_значение1;логическое_значение2…);
  • =ИЛИ(логическое_значение1;логическое_значение2…).

Функция НЕ имеет только один аргумент и следующий синтаксис:

  • =НЕ(логическое_значение).

Аргументами функций ИЛИ, И, НЕ могут являтся логические выражения, массивы, ссылки на ячейки, которые содержат логические значения. Ниже рассмотрен пример вычисления функции И с формированием результата в ячейке С6 (рис.4.35) и комментарий результатов для выполнения указанных трех логических функций:

Функция Комментарий результата

=И(А2А3; А2

=ИЛИ(A2A3; A2

=НЕ(A2+A3=24) -15 плюс 9 НЕ равно 24? (ЛОЖЬ).

Функция НЕ изменяет значение аргумента на противоположное логическое значение и используется совместно с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА.

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

=ЕСЛИ(В1=10;Отлично;ЕСЛИ(И(В1=6;В1=3;В1

Green brown cyan lightgray 11 глава

Рис.4.35 Выполнение логической функции И

Данное выражение описывает следующую логическую задачу: Если значение в ячейке В1 равно 10, возвратить результат Отлично. Иначе, если в ячейке В1 находится значение между 6 и 9, то возвращается результат Хорошо. Иначе, если в ячейке В1 значение находится в диапазоне от 3 до 6, то возвратить результат Удовлетворительно. И,наконец, если ни одно из этих условий не выполняется, возвращается результат Неудовлетворительно. Функция ЕСЛИ допускает использование до 7 уровней вложений. Другой пример иллюстрирует использование функции ЕСЛИ для выбора товара в ячейках В3:В8 по стоимости, представленной в ячейках С3:С8 . Если значение стоимости товара удовлетворяет поставленному условию, то результат, представленный в ячейках D3:D8, принимает значение “Смотреть”, а если значение не соответствует заданному критерию, то результат — “Пропустить” (рис.4.36).

Вычисление выражений с условиями.Пусть требуется вычислить функцию :

Y = a/b*6, ecли x0;

Y =(a+c), если x

Green brown cyan lightgray 11 глава

Рис.4.36. Анализ цен товаров с использованием функции ЕСЛИ

Переменная х хранится в ячейке A2, а константы a, b, c — соответственно в ячейках B2, C2,D2. На рис 4.37 показана реализация данной функции для случая Х=6, то есть больше Х0. Формула для вычисления выражения показана в строке формул.

Green brown cyan lightgray 11 глава

Рис. 4.37. Реализация функции Y

Если в ячейку А2 поместить значение Х

4.8.4. Вычисление функций и построение графиков

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

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

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

Вычисление функций одной переменной

Рассмотрим построение функции у=2х+1, представляющей собой уравнение линии для значений х= (0; +10) с шагом ?х=1. Задача построения прямой, как и любой диаграммы в Excel разбивается на несколько этапов в соответствии.

Этап 1. Ввод данных.

Прежде, чем построить прямую, необходимо составить таблицу данных x и yв рабочем окне таблицы Excel. Для этого значения xи yследует представить в виде таблицы, где столбцами являются соответствующие показатели. Пусть в рассматриваемом примере столбец А будет использоваться для аргументов x, а столбец В – для значений функции прямой y. Для введения значений аргумента х в ячейку А2 вводится первое значение аргумента (0), а в ячейку A3 — второе значение аргумента с учетом заданного шага (1). Выделив блок ячеек А2:АЗ, и, используя режим автозаполнения, формируем все значения аргумента (за правый нижний угол блока протягиваем до ячейки А12). Затем в ячейку В2 вводим ее уравнение: =2*A2+1, а затем копируем эту формулу в диапазон В2:В12. В результате должна быть получена следующая таблица исходных данных (Рис.4.38.).

Этап 2. Построение графика прямой.

После запуска программы Мастер диаграмм в появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы выбираем тип диаграммы — График, а вид — График с маркерами и нажимаем кнопку Далее в диалоговом окне.

Этап 3. Указание диапазона.

В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон указать интервал данных, то есть ввести ссылку на ячейки, содержащие данные, которые необходимо представить на диаграмме.

Green brown cyan lightgray 11 глава

Рис. 4.38. Вычисление и построение функции у=2х+1

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

Этап 4. Ввод надписей по оси X(горизонтальной) и У (вертикальной).

В диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы необходимо выбрать вкладку Ряд (щелкнув на ней указателем мыши) и в поле Подписи оси X указать диапазон подписей (в примере — аргументх).Для этого следует активизировать поле Подписи оси х, щелкнув в нем указателем мыши, и, наведя его на левую верхнюю ячейку подписей (А2), нажать левую кнопку мыши, затем, не отпуская ее, протянуть указатель мыши к правой нижней ячейке, содержащей выносимые на ось X подписи (А12), затем отпустить левую кнопку мыши. После появления требуемой записи диапазона необходимо нажать кнопку Далее.

Этап 5. Введение заголовков.

В третьем окне Мастер диаграмм (шаг 3 из 4): параметры диаграммы требуется ввести заголовок диаграммы и названия осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув на ней указателем мыши. Щелкнув в рабочем поле Название диаграммы указателем мыши, ввести с клавиатуры в поле название: Прямая. Затем аналогичным образом ввести в рабочие поля Ось X (категорий) и Ось Y (значений) соответствующие названия: Аргументи Значения. Далее в данном окне необходимо выбрать вкладку Легенда и указать ее необходимость для обозначения функций, для чего устанавливаем флажок в поле Добавить легенду и нажимаем кнопку Далее.

Этап 6. Выбор места размещения.

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

В качестве второго примера рассмотрим построение функции одной переменной у=f(x) на примере уравнения параболы y=x2 для значений х=(-5; +5) с шагом ?х=0.5.

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

Этап 1. Ввод данных и вычисление функции.

Для построения параболы необходимо составить таблицу данных (х и у).В рассматриваемом примере столбец А будет использоваться для значений аргумента х, а столбец В – для значений функции у. В ячейку А1 вводим Аргумент, а в ячейку В1 — Парабола. Далее в ячейку А2 вводим первое значение аргумента (-5), в ячейку A3 вводится второе значение аргумента (–4,5) и затем, выделив блок ячеек А2:АЗ,автозаполнением вводим все значения аргумента. Далее в ячейку В2 вводим уравнение параболы: =А2*А2 и копируем эту формулу в диапазон ячеек В2:В14. В результате должна быть получена следующая таблица исходных данных (рис.4.39).

Green brown cyan lightgray 11 глава

Рис.4.39. Вычисление и построение параболы y=x2

Этап 2. Выбор типа диаграммы.

На панели инструментов Стандартная необходимо нажать кнопку Мастер диаграмм. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы указать тип диаграммы. В рассматриваемом примере выберем тип — График, вид — График с маркерами (левую среднюю диаграмму в правом окне). После чего нажимаем кнопку Далее в диалоговом окне.

Этап 3. Указание диапазона.

В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон указать интервал данных, то есть ввести ссылку на ячейки, содержащие данные, которые необходимо представить на диаграмме. Для этого с помощью клавиши Delete необходимо очистить рабочее поле Диапазон и, убедившись, что в нем остался только мигающий курсор, навести указатель мыши на левую верхнюю ячейку данных (В1), нажать левую кнопку мыши и, не отпуская ее, протянуть указатель мыши к правой нижней ячейке, содержащей выносимые на диаграмму данные (В12), затем отпустить левую кнопку мыши. В рабочем поле должна появиться запись: =Лист1!$В$1:$В$12. Здесь наиболее важным для нас является указание диапазона В1:В12, что подтверждает правильное введение интервала данных. Если с первого раза не удалось получить требуемую запись в поле Диапазон, действия необходимо повторить. Далее необходимо указать в строках или столбцах расположены ряды данных. В примере значения точек параболы расположены в столбце, поэтому переключатель Ряды в с помощью указателя мыши следует установить в положение столбцах (черная точка должна стоять около слова столбцах).

Этап 4. Ввод подписей по оси X(горизонтальной).

В диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы необходимо выбрать вкладку Ряд (щелкнув на ней указателем мыши) и в поле Подписи оси X указать диапазон подписей (в примере — Аргумент). Для этого следует активизировать поле Подписи оси X, щелкнув в нем указателем мыши, и, наведя указатель мыши на левую верхнюю ячейку подписей (А2), нажать левую кнопку мыши, затем, не отпуская ее, протянуть указатель мыши к правой нижней ячейке, содержащей выносимые на ось X подписи (А12), затем отпустить левую кнопку мыши. В рабочем поле должна появиться запись: =Лист1!$А$2:$А$14. Здесь, как и для данных, наиболее важным для нас является указание диапазона А2:А14, что подтверждает правильное введение интервала подписей. После появления требуемой записи диапазона необходимо нажать кнопку Далее.

Этап 5. Введение заголовков.

В третьем окне Мастер диаграмм (шаг 3 из 4): параметры диаграммы требуется ввести заголовок диаграммы и названия осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув па ней указателем мыши. Щелкнув в рабочем поле Название диаграммы указателем мыши, ввести с клавиатуры в поле название График параболы. Затем аналогичным образом ввести в рабочие поля Ось X (категорий) и Ось Y (значений) соответствующие названия и после чего нажать кнопку Далее.

Этап 6. Выбор места размещения.

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

Этап 7. Завершение.

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

Вычисление функций двух переменных

Примерами объектов подобного вида второго порядка являются эллипсоид, гиперболоид, параболоид, конус второго порядка и многие другие. Рассмотрим построение эллипсоида, под которым понимается поверхность, определяемая в системе декартовых прямоугольных координат следующим уравнением:

Green brown cyan lightgray 11 глава (4.1)

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

Для построения эллипсоида в Excel каноническое уравнение (4.1) необходимо решить относительно переменной z(представить в виде функции z=f(x, у):

Green brown cyan lightgray 11 глава

Пусть необходимо построить верхнюю часть эллипсоида, лежащую в диапазонах: х=[–3; 3], у=[–2; 2] с шагом ?х =0,5 для обеих переменных. Введем значения переменной хв столбец А,для чего в ячейку А1 вводим символ х, а в ячейку А2 вводится первое значение аргумента (–3). В ячейку A3 вводится второе значение аргумента — левая граница диапазона плюс шаг построения (–2,5).Затем, выделив блок ячеек А2:АЗ, автозаполнением получаем все значения аргумента (за правый нижний угол блока протягиваем до ячейки А14).

Значения переменной увводим в строку 1, для чего в ячейку В1 вводится первое значение переменной у= -2. В ячейку С1 вводится второе значение переменной у=-1,5 в соответствии с заданным шагом переменной у. Затем, выделив блок ячеек В1:С1, автозаполнением вводим все остальные значения аргумента у (за правый нижний угол блока протягиваем до ячейки J1).

Далее вводим значения функции z в соответствии с уравнением (4.1). Для этого табличный курсор необходимо поместить в ячейку В2 и на панели инструментов Стандартная нажать кнопку Вставка Функции fx. В появившемся диалоговом окне Мастер функций шаг 1 из 2 в поле Категория выбираем Математические. Справа в поле Функция выбираем функцию Корень,нажимаем кнопку ОК и появляется диалоговое окно Корень. В рабочее поле вводим подкоренное выражение: 1- $А2^2/9-В$1^2/4, обратите внимание, что символы $ предназначены для фиксации адреса столбца А — переменной х истроки 1 — переменной у. Нажимаем кнопку ОК. В ячейке В2 появляется #ЧИСЛО! (при х=–3 и у=–2 точек рассматриваемого эллипсоида не существует). Теперь необходимо скопировать функцию из ячейки В2, для чего автозаполнением (протягиванием вправо) копируем эту формулу вначале в диапазон B2:J2, после чего (протягиванием вниз) — в диапазон ВЗ:J14. В результате должна быть получена следующая таблица точек эллипсоида.

Type


Похожие статьи.

Понравилась статья? Поделиться с друзьями: