Построение математической модели в excel

Обновлено: 08.07.2024

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

Регрессионный анализ

Регрессионный анализ позволяет получить функциональную зависимость между некоторой случайной величиной Y и некоторыми влияющими на Y величинами X. Такая зависимость получила название уравнения регрессии . Различают простую (парную) и множественную регрессию линейного и нелинейного типа.

Пример простой линейной регрессии:

Пример множественной линейной регрессии:

Для оценки степени связи между величинами используется коэффициент множественной корреляции R Пирсона (корреляционное отношение), который может принимать значения от 0 до 1. R=0 если между величинами нет никакой связи и R=1, если между величинами имеется функциональная (детерминированная) связь. В большинстве случаев R принимает промежуточные значения от 0 до 1. Величина R 2 называется коэффициентом детерминации.

Задачей построения регрессионной зависимости является нахождение вектора коэффициентов M модели (1) при котором коэффициент R принимает максимальное значение.

Для оценки значимости R применяется F-критерий Фишера, вычисляемый по формуле

где n - размер выборки (количество экспериментов); k - число коэффициентов модели. Если F превышает некоторое критическое значение для данных n и k и принятой доверительной вероятности, то величина R считается существенной. Таблицы критических значений F приводятся в справочниках по математической статистике.

Таким образом, значимость R определяется не только его величиной, но и соотношением между количеством экспериментов и количеством коэффициентов (параметров) модели. Действительно, корреляционное отношение для n=2 для простой линейной модели равно 1 (через 2 точки на плоскости можно всегда провести единственную прямую). Однако, если экспериментальные данные являются случайными величинами, доверять такому значению R следует с большой осторожностью. Обычно для получения значимого R и достоверной регрессии стремятся к тому, чтобы количество экспериментов существенно превышало количество коэффициентов модели (n>>k).

Для построения линейной регрессионной модели необходимо:

1) подготовить список из n строк и m столбцов, содержащий экспериментальные данные (столбец, содержащий выходную величину y должен быть либо первым, либо последним в списке);

2) обратиться к меню Сервис/Анализ данных/Регрессия

Если пункт "Анализ данных" в меню "Сервис" отсутствует, то следует обратиться к пункту "Надстройки" того же меню и установить флажок "Пакет анализа" .

3) в диалоговом окне "Регрессия" задать:

входной интервал Y;
входной интервал X;
выходной интервал - верхняя левая ячейка интервала, в который будут помещаться результаты вычислений (рекомендуется разместить на новом рабочем листе);

4) нажать "Ok" и проанализировать результаты.

Пример использования множественной линейной регрессии

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

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

y - оценочная цена здания под офис;
x₁ - общая площадь в квадратных метрах;
x₂ - количество офисов;
x₃ - количество входов (0,5 входа означает вход только для доставки корреспонденции);
x₄ - время эксплуатации здания в годах.

В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (x₁, x₂, x₃ и x₄) и зависимой переменной (y), то есть ценой здания под офис в данном районе. Исходные данные показаны на рисунке.

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

В итоге мы получили следующую математическую модель:

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

y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = 158 261 у.е.

В регрессионном анализе наиболее важными результатами являются:

коэффициенты при переменных и Y-пересечение, являющиеся искомыми параметрами модели;
множественный R, характеризующий точность модели для имеющихся исходных данных;
F-критерий Фишера (в рассмотренном примере он значительно превосходит критическое значение, равное 4,06);
t-статистика – величины, характеризующие степень значимости отдельных коэффициентов модели.

На t-статистике следует остановиться особо. Очень часто при построении регрессионной модели неизвестно, влияет тот или иной фактор x на y. Включение в модель факторов, которые не влияют на выходную величину, ухудшает качество модели. Вычисление t-статистики помогает обнаружить такие факторы. Приближенную оценку можно сделать так: если при n>>k величина t-статистики по абсолютному значению существенно больше трех, соответствующий коэффициент следует считать значимым, а фактор включить в модель, иначе исключить из модели. Таким образом, можно предложить технологию построения регрессионной модели, состоящую из двух этапов:

1) обработать пакетом "Регрессия" все имеющиеся данные, проанализировать значения t-статистики;
2) удалить из таблицы исходных данных столбцы с теми факторами, для которых коэффициенты незначимы и обработать пакетом "Регрессия" новую таблицу.

Для примера рассмотрим переменную x4. В справочнике по математической статистике t-критическое с (n-k-1)=6 степенями свободы и доверительной вероятностью 0,95 равно 1,94. Поскольку абсолютная величина t, равная 17,7 больше, чем 1,94, срок эксплуатации - это важная переменная для оценки стоимости здания под офис. Аналогичным образом можно протестировать все другие переменные на статистическую значимость. Ниже приводятся наблюдаемые t-значения для каждой из независимых переменных:

Общая площадь	5,1
Количество офисов	31,3
Количество входов	4,8
Срок эксплуатации	17,7

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

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

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

Задача 1. Определение максимального объема коробки.

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

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

Геометрическая модель.
Математическая модель.
Расчетные формулы:
С = а-2b – длина стороны дна;
S = c 2 – площадь дна;
V = Sb – объем.
Здесь а – длина стороны картонного листа, b – длина выреза.

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

Заполнить вниз пока длина выреза не будет равна 20, а длина стороны равна 0.

Задание для самостоятельного решения:

Проведите расчеты с шагом увеличения 0,5см.
Определите по столбцу Объем наибольший объем коробки для выреза с шагом увеличения 1 см.; с шагом увеличения 0,5 см. Сравните их.
Решите следующую задачу.
В прямоугольном треугольнике задана длина гипотенузы с. Найти размеры катетов, при которых треугольник имеет наибольшую площадь. Составьте геометрическую и математическую модели. Провести расчеты.

Задача 2. Определение наибольшей выручки.

Несколько человек решили организовать видео кафе на 6 столиков по 4 места за каждым. С каждого посетителя будет взиматься плата за сеанс видеофильма и ужин (всем посетителям будет предлагаться один и тот же набор блюд). Администрация города постановила, что плата за вход не должна превышать 500 рублей. Требуется определить такую входную плату, при которой будет получена наибольшая выручка.

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

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

Обозначим входную плату через X. Тогда среднее число посетителей кафе является функцией от Х. Обозначим эту функцию через Р(Х). В задаче требуется найти такое значение X, при котором выручка, равная произведению входной платы на количество посетителей Х * Р(Х), достигает максимума. Если бы функция Р(Х) была известна, то найти требуемый максимум не составило бы особого труда. Но эта функция неизвестна, поэтому попробуем найти хотя бы общий вид функции. Его можно указать, обобщив опыт работы подобных кафе: P(x) = ax 2 + bx + c (1).

Коэффициенты а, b и с для каждого кафе свои. Как же их определить? Проще всего найти значение с. Представьте себе невообразимое – в кафе пускают бесплатно (т. е. X = 0). Ясно, что свободных мест не будет. Следовательно, Р(0) равно числу мест в кафе. С другой стороны, подставив 0 вместо X, получим P(0) = с. Значит, с равно количеству мест. В нашем случае с = 24 (6 столиков по 4 места за каждым).

Определить а и b так же просто не удается. Справочников по посещаемости кафе еще нет. Поэтому здесь требуется эксперимент.

Достаточно открыть кафе и установить на некоторый срок (дней на десять) определенную плату за вход. Среднее число посетителей и даст нам (приближенное!) значение функции. Установив другую плату за вход, найдем приближенное значение Р(Х) при новом X, и так несколько раз.

Зависимость посещаемости от входной платы (на основе экспериментальных данных для конкретного кафе):

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

Составьте следующую таблицу.

Выполнение работы.

I. В столбце С подсчитайте выручку на основе экспериментальных данных:

В ячейке С3 наберите формулу = А3*В3;
Скопируйте формулу в ячейки С4:С10.

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

Построим диаграмму по экспериментальным данным типа Х-У (Х – входная плата, У – данные по количеству посетителей).
Аппроксимируйте полученную кривую. Для построения линии тренда:
- выделить линию графика (щелкнуть мышью по линии графика);
- вызвать контекстно-зависимое меню и выполнить команду Добавить линию тренда.
  Откроется окно Линия тренда. Далее:
  - выбрать полиномиальный тип линии тренда;
  - выбрать вкладку Параметры;
  - установить параметр Показывать уравнение на диаграмме ;
  - указать У – пересечение = 24;
  - нажать кнопку OK.

Внесем полученные значения коэффициентов а и b в ячейки G3 и H3 и присвоим им имена: G3 имя а, H3 имя b.

III. Вычислим теоретическое количество посетителей и теоретическую выручку, причем С = 24:

В ячейке D3 запишем формулу (1): = a*(A3*A3) – b*A3 + 24;
Скопируйте формулу в ячейки D4:D10;
В ячейке E3 запишем формулу = D3*A3;
Скопируйте формулу в ячейки E4:E10.

IV. Вычислим отклонение между экспериментальной и теоретической выручкой и погрешность:

В ячейку FЗ занести формулу = ABS(EЗ – C3);
Скопировать формулу в ячейки F4:F11;
Затем вычислим погрешность – это максимальное отклонение. Для этого в ячейку F11 заносим формулу, содержащую функцию определения максимального из чисел этого столбца = МАКС(F3:F11).

V. Подберем коэффициенты a и b, стараясь минимизировать погрешность. Для этого:

Выберем команду Сервис | Поиск решения.
Настройте параметры в окне диалога Поиск решения:
- в поле Установить целевую ячейку укажите адрес ячейки $F$11 (в ней погрешность);
- установите переключатель Минимальному значению;
- в поле Изменяя ячейки укажите $G$3:$H$3;
- нажмите на кнопку Выполнить, начнется поиск решения;
- установите кнопку Сохранить найденное решение, чтобы сохранить предложенные значения;
- нажмите на кнопку ОК.

Задания для учащихся:

Постройте на одной диаграмме два графика (экспериментальный и теоретический) зависимости количества посетителей от входной платы;
Постройте на одной диаграмме два графика (экспериментальный и теоретический) зависимости выручки от входной платы;
Определите, при какой входной плате выручка будет максимальна;
Каково среднее число посетителей сеанса при найденной оптимальной входной плате.

Смоделируем еще одну ситуацию. Цель моделирования – эффективность управления объектом.

Задача 3. Оптимизационная задача.

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

К таким задачам, например, относятся:

задача нахождения наиболее рационального использования сырья и материалов;
задача определения наиболее выгодных производственных режимов;
задача повышения эффективности работы транспорта и т.п.

Рассмотрим следующую задачу: цех молокозавода выпускает эскимо и другой вид мороженого (назовем его просто "мороженое"). Эскимо в 2 раза дороже мороженого. За 1 мин выпускается 90 порций мороженого или 30 порций эскимо, возможен одновременный выпуск двух видов продукции. Из-за ограничения срока реализации продукции и недостаточного объема холодильных камер в течение часа на хранение может быть принято не более 3600 штук изделий. Определите наибольшую стоимость выпускаемой продукции молокозавода и оптимальный план выпуска мороженого и эскимо за 1 мин.

Решим задачу с применением надстройки Поиск решения приложения Microsoft Excel.

Пусть одновременно выпускается два вида продукции.

Обозначим число выпускаемых за 1 мин порций эскимо x, мороженого – у, t₁ – время, необходимое для производства одного эскимо; t₂ – время, необходимое для производства одного мороженого.

Из условия задачи следует, что за 1 мин производится 90 порций мороженого или 30 порций эскимо, то есть времени на производство одного эскимо затрачивается в 3 раза больше, чем на производство одного мороженого: t₁= 3*t₂.

За 1 мин соотношение времени при одновременном выпуске каждого из двух видов продукции x и y составит: t₁x +t₂y 1 или, подставляя t₁ = 3*t₂, получаем 3t₂x +t₂y 1. Отсюда

t2(3x + y) 1;

Ho величина 1/t₂ – это максимальный выпуск мороженого за 1 мин, т. е. она равна 90.

Итак, возможности производства определяют условие 3x + у 90.

Еще одно условие – ограниченная емкость холодильника. B течение 1 ч холодильник может принять 3600 штук продукции, то есть за одну минуту 3600/60 = 60 порций: x + у 60.

Обозначив цену одного эскимо C₁ (руб.), а цену одного мороженого – C₂ (руб.), можно записать в соответствии с условием задачи следующее соотношение цен на продукцию: C₁= 2C₂.

Общая стоимость продукции, выпускаемой цехом за 1 мин: S = C₁x + C₂y.

Заменяя С₁ на С₂ получим: S =2C₂x + C2y или S = C₂(2x + y).

Поскольку C₂ – заданная положительная константа, то для упрощения задачи можно принять C₂ = 1. По условию задачи необходимо найти возможную наибольшую стоимость выпускаемой продукции. Таким образом, следует добиваться максимального значения целевой функции S = 2x + y.

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

Итак, учитывая все условия задачи, приходим к ее математической модели: Среди целочисленных решений системы линейных неравенств

найти такое, при котором достигается максимум линейной функции S = 2x + y.

Решение с помощью надстройки "Поиск решения".

Решим задачу с помощью надстройки "Поиск решения". Опишем содержимое ячеек рабочего листа Excel в таблице 1.

B описанной модели необходимо максимизировать значение в ячейке B11.

B качестве начальных значений x и у принимаются нули. Ограничения задачи представлены в таблице 2.

Условие	Ячейки
Количество эскимо не должно превышать заданного значения	$B$4 <= $C$4
Количество мороженого не должно превышать заданного значения	$B$5 <= $C$5
Ограничение по объему холодильной установки	$B$8 <= $C$8
Ограничение по объему производства	$B$9 <= $C$9
Количества производимого эскимо и мороженого не могут быть отрицательными числами	$B$4:$B$5 0

Выполните следующие действия:

Выделите ячейку с оптимизируемым значением B11.
Выберите команду Сервис | Поиск решения. Загрузится надстройка, и появится диалоговое окно Поиск решения.
В поле Установить целевую ячейку уже находится ссылка на выделенную на первом шаге ячейку (при необходимости эту ссылку можно изменить).
Установите переключатель Равной равным максимальному значению (ищется максимальное значение целевой ячейки B11).
Перейдите в поле Изменяя ячейки и укажите диапазон ячеек (или введите ссылки на них), которые должны изменяться в процессе поиска наилучшего решения. B данном примере это ячейки $B$4:$B$5.
Щелкните на кнопке Добавить, чтобы ввести первое ограничение задачи. Откроется диалоговое окно Добавление ограничения.
Введите первое ограничение: $B$4:$B$5 >= O. Для этого, находясь в поле, Ссылка на ячейку, укажите ячейки мышью или введите диапазон с клавиатуры. Нажмите клавишу Tab или щелкните на стрелке раскрывающегося списка и выберите знак отношения (>=). Щелкните на кнопке OK. B поле Ограничение введите 0.

Щелкните на кнопке Добавить, введите следующее ограничение и щелкните на кнопке OK.

Примечание. С помощью клавиши ЕSC можно прервать слишком затянувшийся процесс поиска решения.

Итак, при выпуске 15 штук эскимо и 45 штук мороженного максимальная прибыль составит 75 единиц за минуту.

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

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

Учебно-методическое обеспечение: презентация (Презентация), ПО MS Excel, ПО MS PowerPoint, методические указания.

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

Ход конференции

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

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

Теоретическая часть

Преподаватель: Рассмотрим этапы информационного моделирования.

1. Модель задачи.

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

2. Понятие математической модели.

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

Математическая модель выражает существенные признаки объекта или процесса языком уравнений и других математических средств. (Презентация. Слайд 3)

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

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

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

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

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

Предположив, что поверхность стола – прямоугольник, мы легко указываем исходные данные и находим результат. Они связаны соотношение S = a * b. (Презентация. Слайд 5)

Сделанное предположение позволило «перевести» нашу задачу на язык чисел: и исходные данные, и результат – числа, а соотношение между ними задается математической формулой.

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

3. Этапы решения задач на компьютере.

1 этап. Постановка задачи – точная формулировка условий и целей решения, описание наиболее существенных свойств объекта. (Презентация. Слайд 6)

2 этап. Построение математической модели – описание наиболее существенных свойств объекта с помощью математических формул. (Презентация. Слайд 6)

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

Построение алгоритма решения задачи и его кодирование на одном из языков программирования.
Построение компьютерной модели и использованием ПО компьютера (приложений Windows – электронных таблиц, СУБД и пр.). (Презентация. Слайд 7)

4 этап. Проведение компьютерного эксперимента (исследование модели) – если компьютерная модель существует в виде программы на одном из языков программирования, то её нужно запустить на выполнение и получить результаты; если компьютерная модель исследуется в приложении, например, в электронных таблицах, можно провести сортировку или поиск данных, построить диаграмму или график и т.д. (Презентация. Слайд 8)

5 этап. Анализ полученных результатов и корректировка модели – в случае различия результатов, полученных при исследовании модели, с измеряемыми параметрами реальных объектов можно сделать вывод, что на предыдущих этапах построения модели были допущены ошибки или неточности. В этом случае необходимо провести корректировку модели, причём уточнение модели может проводиться многократно, пока анализ результатов не покажет их соответствие изучаемому объекту. (Презентация. Слайд 9)

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

Практическая часть

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

размеры кухни 405 × 310 × 285 см;
88% площади стен занимает кафельная плитка;
1 банка краски предназначена для покраски площади 5 м 2 ? (Презентация. Слайд 10)

a = 405 см – длина комнаты,
b = 310 см – ширина комнаты,
c = 285 см – высота комнаты,
1 – 0,88 = 0,12 – часть комнаты для покраски (без кафеля),
5 м 2 – площадь покраски при использовании 1 банки краски.

Найти: необходимое для покраски стен кухни количество банок краски. (Презентация. Слайд 11)

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

S_{стен с кафелем} =2(a + b)c.
S_{стен для покраски} = 2(a + b)c * 0,12.

Чтобы определить, сколько потребуется банок краски, надо площадь для покраски разделить на 5 м 2 , т. е. S_{стен для покраски} /5 и результат округлить до целых.

Моделирование в среде ЭТ.

Заносим данные задачи в электронную таблицу, вводим формулы.
Электронная таблица в режиме отображения формул. (Приложение 1. Презентация. Слайд 12)
Электронная таблица в режиме отображения значений. (Приложение 2. Презентация. Слайд 13)
С помощью MS Excel мы определили, что для покраски стен кухни необходима 1 банка краски.

Задача 2. Через иллюминатор корабля требуется вытащить сундук с драгоценностями. Удастся ли это сделать?

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

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

Пусть r – радиус иллюминатора,
a, b, c – размеры сундука,
d1, d2, d3 – диагонали боковых поверхностей сундука. (Презентация. Слайд 15)

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

(Презентация. Слайд 16)

Моделирование в среде ЭТ.

Заносим данные задачи в электронную таблицу, вводим формулы.
Электронная таблица в режиме отображения формул. (Приложение 3. Презентация. Слайд 17)
Электронная таблица в режиме отображения значений. (Приложение 4.Презентация. Слайд 18)

Компьютерный эксперимент.

В электронной таблице находим сумму трех условий. Если сумма равна 0, делаем вывод «Сокровища недоступны», иначе «Сокровища доступны» (Слайд 19 Презентация).

Задача 3. Решить уравнение х4-4х3-10х2+37х-14=0 (Слайд 20 Презентация).

Необходимо построить график функции у = х 4 – 4х 3 – 10х 2 + 37х – 14. Точки пересечения графика с осью Х будут решениями данного уравнения. Составляем в MS Excel таблицу значений функции. (Приложение 5. Презентация. Слайд 21)

Построим график функции (диаграмму). (Приложение 5. Презентация. Слайд 22)

Мы видим, что график четырежды пересекает ось ОХ, значит уравнение х 4 – 4х 3 – 10х 2 + 37х –14 = 0 имеет четыре корня.

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

(Презентация. Слайд 23)

Затем с помощь анализа «что-если»/Подбор параметра можно уточнить значения корней. Для этого следует активизировать ячейку со значением функции у = 55,56, соответствующим значению аргумента х = -3,5, или ячейку со значением у = -26, соответствующим х = -3, и выполнить команду Данные/группа Работа с данными/Анализ «что-если»/Подбор параметра. Появится одноименное диалоговое окно с тремя строками (Слайд 23 Презентация).

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

Аналогично проверяются корни из других промежутков.

Из результирующей таблицы выбираем корни уравнения. (Приложение 5. Презентация. Слайд 24)

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

Поясним это на примере решения уравнения.

Если построить график соответствующей функции в области ее определения с шагом h = 0,04, то получится один результат (Приложение 6. Презентация. Слайд 27), но если построить тот же график с меньшим шагом h = 0,01, то мы получим иной результат. (Приложение 6. Презентация. Слайд 27) Сравнение этих графиков показывает, что в первом случае из-за слишком большого шага «потеряны» два первых корня. Всего же рассматриваемое уравнение имеет шесть корней, которые уточняются с помощью Подбора параметра. (Презентация. Слайд 28)

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

Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы используем power query & Transform ( Get & Transform), чтобы вы могли захотеть вернуться на шаг назад и посмотреть видео или воспользоваться учебным руководством по & Преобразование и Power Pivot.

Excel 2016 & Excel для Microsoft 365 — На ленте есть Power Pivot.

Excel 2013 — Power Pivot входит в выпуск Office профессиональный плюс версии Excel 2013, но по умолчанию не включен. Подробнее о запуске надстройки Power Pivot для Excel 2013.

Excel 2010 — скачайте надстройку Power Pivot, а затем установите надстройку Power Pivot,

Excel 2016 & Excel для Microsoft 365 — & Power Query интегрирован с Excel на вкладке Данные.

Excel 2013 : Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите в > файлов > надстройки ,а затем в меню Управление в нижней части области выберите Надстройки COM > Перейти. Проверьте, нет ли в Microsoft Power Query Excel, а затем ОК, чтобы активировать его. На ленту будет добавлена вкладка Power Query.

Excel 2010— скачивание и установка надстройки Power Query.. После активации на ленту будет добавлена вкладка Power Query.

Начало работы

Во-первых, вам нужно получить некоторые данные.

В Excel 2016 и Excel для Microsoft 365 используйте data > Get & Transform Data > Get Data (Получить данные), чтобы импортировать данные из любого числа внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, которая содержит несколько связанных таблиц.

В Excel 2013 и 2010 перейдите в Power Query > Получитьвнешние данные и выберите источник данных.

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

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

Выберите одну или несколько таблиц и нажмите кнопку Загрузить.

Если вам нужно изменить исходные данные, можно выбрать параметр Изменить. Дополнительные сведения см. в обзоре редактора запросов (Power Query).

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

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

Модели создаются явно, если вы импортируете данные с помощью надстройки Power Pivot. В надстройке модель представлена в макете со вкладками так же, как Excel, где каждая вкладка содержит табличные данные. Сведения о том,как импортировать данные с помощью базы данных, см. в этой SQL Server Power Pivot.

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

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

Советы по уменьшению размера модели данных см. в Excel и Power Pivot.

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

Совет: Как узнать, есть ли в книге модель данных? Перейдите в Power Pivot > Управление. Если вы видите данные, похожие на таблицу, то модель существует. Дополнительные сведения см. в этой теме.

Создание связей между таблицами

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

Перейдите на > PowerPivot .

На вкладке Главная выберите представление диаграммы.

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

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

Мы создали следующие ссылки:

tbl_Students | ИД учащегося > tbl_Grades | ИД учащегося

Другими словами, перетащите поле "ИД учащегося" из таблицы "Учащиеся" в поле "ИД учащегося" в таблице "Оценки".

tbl_Semesters | ИД семестра > tbl_Grades | Семестр

tbl_Classes | Номер > tbl_Grades | Номер класса

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

Соединители в представлении диаграммы имеют 1 с одной стороны, а "*" — на другой. Это означает, что между таблицами существует связь "один-к-многим", которая определяет способ использования данных в таблицах. Дополнительные сведения см. в этой теме: Связи между таблицами в модели данных.

Соединитетели указывают только на связь между таблицами. Они не будут показывать, какие поля связаны друг с другом. Чтобы увидеть ссылки, перейдите в Power Pivot > Управление > конструктором > связи > Управление связями. В Excel вы можете перейти к data > Relationships (Отношения > данных).

Создание и создание с помощью модели данных для создания сводная диаграмма

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

В Power Pivot, перейдите вуправление .

На вкладке Главная выберите вкладку "PivotTable".

Выберите место для размещения таблицы: новый или текущий.

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

Добавление имеющихся несвязанных данных в модель данных

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

Добавьте данные одним из следующих способов.

Щелкните Power Pivot > Добавить в модель данных.

Выберите Вставка > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне "Создание сводной таблицы".

Диапазон или таблица будут добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в статье Добавление данных с помощью связанных таблиц Excel в Power Pivot.

Добавление данных в Power Pivot таблицу

В Power Pivot невозможно добавить строку в таблицу, введя текст непосредственно в новой строке, как это можно сделать на листе Excel. Но вы можете добавить строки, скопируяи впав их или обновив исходные данные и обновив модель Power Pivot.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Читайте также: