Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

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

На протяжении многих лет я всё думал, когда же доберусь до этой темы, и вот, наконец-то свершилось! …как и во многих делах, самое трудное – первый шаг, но я таки открыл вёрдовский файл (решался и обдумывал 2 недели) и с радостью и даже какой-то торжественностью написал первый абзац.

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

Из литературы рекомендую те же две книги: задачник и учебное пособие В.Е. Гмурмана под названием Теория вероятностей и математическая статистика.

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

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

Немедленный пример. Что главное орудие физика? Секундомер:

Студент Константин выполняет лабораторную работу по определению коэффициента вязкости жидкости методом Стокса.

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

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

Предположим, что в результате 5 опытов получены следующие результаты (в секундах):

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

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

Справка: вариАнта (существительное женского рода) – в статистике означает отдельно взятое эмпирическое значение.

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

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

Это значение называют простой средней или, как многие знают, средним арифметическим. Его стандартно обозначают с чёрточкой наверху.

Справка на всякий случай: математический значок означает суммирование, а переменная играет роль «счётчика»; в данном случае изменяется от 1 до 5.

Если грызут сомнения на счёт точности, то лучше не полениться и провести 10 опытов, что, кстати, удобнее в плане вычислений (на 10 делить проще). И, разумеется, полученный результат будет надёжнее, чем в 1-м случае.

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

…возможно, у вас возник вопрос, почему я выбрал такой пример? Это единственное, что мне запомнилось из институтского курса физики :)

Сбором статистических данных здесь занимался преподаватель, и обратите внимание на их характер: они эмпирические, массовые (громко, конечно, сказано, но таки массовые) и отчасти случайные. Кому-то повезло с вопросом, кому-то нет, кто-то что-то вспомнил / забыл, списал, прогулял и так далее…, прямо какое-то броуновское движение студентов))

Теперь обратим внимание на следующую вещь: двоечников и отличников у нас мало, а нормальных студентов :) много. И возникает вопрос: как вычислить «справедливую» среднюю оценку по всей совокупности? Решение напрашивается – с помощью так называемой средневзвешенной средней:

– средняя успеваемость по группе. И я обязательно приму соответствующие меры!

…да, суровые у меня сегодня примеры :) Давайте проанализируем их принципиальные отличия:

1) В первом примере проводится статистическое исследование количественной величины (времени), а во втором «оцифровывается» и анализируется качественный признак (успеваемость).

Второй пример отличен тем, что в нём исследуется ВСЯ совокупность, и поэтому её называют генеральной совокупностью, а соответствующее среднее значение – генеральной средней. Но такая ситуация редкость. Редко когда удаётся исследовать всю совокупность.

И сейчас мы подошли к основному методу математической статистики:

Федор пошёл на базу исследовать помидоры. Требуется определить среднюю массу помидора и среднюю долю первосортных помидоров.

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

Но это долго и трудно, даже если Феде будут помогать все его однокурсники.

Что нужно для того, чтобы обеспечить репрезентативность?

Ну, во-первых, выборка должна быть достаточно велика, помидоров так 500-1000 точно, что уже вполне по силам даже одному Феде.

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

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

В-третьих, отбор должен быть случайным. Для этого используются разные приёмы, и самый простой здесь – это выбор «вслепую» из случайно выбранного места ящика, обязательно с разной глубины (а то мало ли, что поставщик там мог спрятать).

И, в-четвёртых (а может быть, и, в-первых), есть и другие факторы, которые могут быть менее очевидны. В частности, важно знать, а однородна ли генеральная совокупность? Так, если помидоры поступили от разных поставщиков, то каждую партию полезно исследовать по отдельности (сделать несколько выборок).

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

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

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

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

Вот оно как – в статистике есть и прямая экономическая выгода!

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

Вторая часть задачи. Определим вместе с Фёдором среднюю долю высококачественных помидоров на базе (ну мы же не садисты заставлять его одного заново перебирать 1000 штук :)).

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

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

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

Доля, как вы догадываетесь, может принимать значение от 0 до 1, и иногда её домножают на 100, чтобы выразить этот показатель в процентах.

Константин, Фёдор, спасибо за участие, а остальные, как в том анекдоте, поедут на картошку :) Тем более, сейчас на дворе конец сентября, а осень, как сказал прозаик, это клубни.

В качестве разминки предлагаю вам задачу с тремя пунктами различного уровня сложности. Проверьте наличие инструментов под рукой и свои навыки вычислений (Эксель вечной живой по-прежнему тут):

а) Урожайность картофеля по трём областям за **** год составила 147, 145, 155 ц/га (центнеров с га). Требуется вычислить среднюю урожайность.

Метрическая справка: 1 центнер = 100 кг, 1 тонна = 1000 кг;
1 гектар (га) = 10000 квадратных метров;
показатель ц/га обозначает, сколько центнеров собрано с 1 гектара.

Не забываем приписывать к итоговому результату размерность! (секунды, граммы и т.д., а в данном случае – ц/га).

Обратите внимание, что здесь урожайность, скажем, по 3-й области велика, но её посевная площадь мала. Поэтому урожайность уместно «взвесить» по площадям.

ДУМАЕМ, ВНИКАЕМ и РАССУЖДАЕМ – принцип здесь точно такой же, как и при решении задач по теории вероятностей. И, главное, не паримся – это просто разминочные задачи!

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Если же номера справочников всегда представляют собой числа, имеет смысл использовать следующее решение Вывод результата в ячейку C3. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Примечание: В версиях надстройки «Поиск решения», выпущенных до Excel 2007, ячейки переменных решения назывались изменяемыми или регулируемыми. В Excel 2010 надстройка «Поиск решения» была значительно улучшена, так что работа с ней в Excel 2007 будет несколько отличаться.

РЕШЕНИЕ ЗАДАЧ ФИЗИЧЕСКОЙ ХИМИИ С ПОМОЩЬЮ ПРОГРАММЫ MS EXCEL — Современные проблемы науки и образования (сетевое издание)

— Что обозначают переменные а, b, с, D, t1 и t2?
— Какое из двух решений квадратного уравнения нужно выбрать?
— Какой результат возвращает стандартная функция sqrt?
— Может ли случиться так, что уравнение не будет иметь вещественных корней?
— Как изменить начальную высоту шарика и начальную скорость?

Решение задач в Excel с использованием условных функций и логических выражений

Компания по снабжению электроэнергией взимает плату плату с клиентов по тарифу: k рублей за 1 КВт/ч и m рублей за каждый КВт/ч сверх нормы, которая составляет 50 Квт/ч. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента.

10 спортсменов-многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определенное количество очков. Спортсмену присваивается звание мастер, если он набрал в сумме не менее k очков. Сколько спортсменов получило звание мастера?

Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

2.2.Решение задач в Excel с использованием математических функций

Для ввода символа № надо переключить клавиатуру на русский алфавит и нажать одновременно две клавиши: Shift + # (№).

В заголовках «Имя функции» ввести конкретные имена функций согласно своему варианту.

В столбце «№ п/п» для получения порядковых номеров использовать формулу =строка()-k, где k – это количество строк, расположенных выше строки, в которую вводится формула.

·i – адрес ячейки, в которой записана формула =строка()-k;

·h – шаг, на который увеличивается значение аргумента х.

В столбцы с заголовками функций ввести формулы, используя первый способ (ввод функции непосредственно в ячейку). Ввод функции начинается с символа = (равно). Для ввода аргумента функции достаточно щёлкнуть мышью по ячейке со значением х, то есть по ячейке, в которую введена формула =а+(i-1)*h.

B C D E F
№ п/п x Sin(x) Cos(x) Exp(x)
=СТРОКА()-4 =0+(A5-1)*0,1 =SIN(C4) =COS(C4) =EXP(C4)

Функции ln(x) и log10(x) неопределены при x£0, а функции asin(x) и acos(x) при (x(>1, поэтому при их применении надо также воспользоваться функцией если().

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

Щёлкнуть мышью по ячейке первого столбца и первой строки таблицы. Ячейка станет активной. Указатель мыши установить на правый нижний угол рамки этой ячейки (чёрный крестик). Это маркер заполнения. Нажать левую кнопку мыши, поймать черный крестик и при нажатой левой кнопке мыши протянуть ячейку по столбцу вниз так, чтобы в таблице получилась 20 строк.

Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

Задание 1. MS Excel. В ячейке А1 хранится число 36. Какой получится результат, если будет использована формула = ЕСЛИ (А1>100;A1;ЕСЛИ (А1<36;0;A1*2)).

Задание 2. Если для фрагмента электронной таблицы вида:

после вычисления значений построить диаграмму типа «гистограмма» по столбцу А, то получим рисунок:

Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

Задание 3. Если в ячейку А1 введена формула f=A2+B2, то после копирования ячейки А1 в ячейку В1, содержимое ячейки B1 будет вычисляться по формуле:

1. Microsoft Office Excel 2003. Учебный курс / В. Кузьмин, — СПб.: Питер: Издательская группа BHV, 2009.

2. Excel 2003. Эффективный самоучитель / В.В. Серогородский, — СПб.: Наука и техника, 2005.

3. Excel: Сборник примеров и задач / С.М. Лавренов, — М.: Финансы и статистика, 2009.

4. Васильева В.С. Персональный компьютер. Быстрый старт. – СПб.: БХВ-Петербург, 2010.

5. Ефимова М.Р., Бычкова С.Г. Социально-экономическая статистика. – М.: Финансы и статистика – 2010.

6. Колесникова И.И. Социально-экономическая статистика. – М.: Новое знание – 2002.

7. Куликов Е. Прикладной статистический анализ. – М.: Радио и связь, 2009.

8. Макаров А.А., Кулаичев А.П., Синева И.С. Использование программ обработки данных в преподавании курсов теории вероятностей, математической и прикладной статистики и информатики. Методические рекомендации (выпуск 1). – М.: МГУ, 2009.

9. Мидлтон М. Р. Анализ статистических данных с использованием Microsoft Excel для Office XP. – М.: Бином: 2010.

10. Минько А.А. Статистический анализ в Microsoft Office Excel. Профессиональная работа. М.: Вильямс, 2010.

11. Мхитарян В.С. Пакет анализа MS Excel в экономико-статистических расчетах. Юнити-Дана, 2010.

13.Чекотковский Э. В. Графический анализ статистических данных в Microsoft Excel 2000. – М.: Диалектика, 2009. 464 с.

14. Чернова Т.В. Экономическая статистика. – Таганрог: Изд-во ТРТУ, 2005.

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

Почему двоичная система счисления так распространена?: Каждая цифра должна быть как-то представлена на физическом носителе.

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

Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

©2015-2022 megaobuchalka.ru Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. (5259)

Постановка и решение задачи с помощью надстройки Поиск решения
Известно, что после того, как 13 апреля 1996 года в городе N появился первый компьютерный вирус, каждый месяц их количество увеличивается вдвое. Подготовьте статистический отчет о количестве вирусов за период с 13 апреля 1996 года по 13 мая1997 года.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
В тот момент, когда Вы нажимаете клавишу , в формулу вместо адреса ячейки подставляется число, находящееся в указанной ячейке. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
• Для проведения компьютерных экспериментов нужно построить компьютерную модель на основе математической модели. Для этого можно написать свою программу, использовать табличный процессор или специальную среду моделирования.
image

Мануал по решению типизированных задач в Microsoft Excel / Хабр

Затем в рабочей части таблицы уже прописывается формула со ссылкой на справочник, заполненный ранее. Т.е. в справочнике в столбце «D» происходит поиск значения из столбца «А» и при нахождении соответствия выводится значение из столбца «Е» в столбец «В».
Синтаксис формулы:
Вывод результата в ячейку B2:

Как Решать Математической Задачи с Помощью Excel

Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

1. Байрамов В. М. Химическая кинетика и катализ: Примеры и задачи с решениями: Учеб. пособие для студ. высш. учеб.заведений. — М.: Издат. центр «Академия», 2003. — 320 с.

2. Задачи по физической химии: Учебное пособие / В. В. Еремин, С. И. Каргов и др. — М.: Изд-во «Экзамен», 2005. — 320 с.

3. Таланов, В. М. Сборник вопросов и задач по Периодическому закону: учеб. пособ. для студентов химических специальностей высших технических учебных заведений / В. М. Таланов, Г. М. Житный; под ред. проф. В. М. Таланова. — М.: Издательство «Академия Естествознания», 2007. — 103 с.

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

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

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

Покажем вычислительные возможности MS Excel на примере решения задачи химической кинетики.

Задача: Бензоилпероксид (перекись бензоила) разлагается при температуре выше 100 ºС. Для данной реакции были получены следующие кинетические данные [1]:

Исходная концентрация бензоилпероксида составляет 0,02 моль/л.

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

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

Аналитический метод (метод «подбора уравнений», метод «проб и ошибок») основан на подстановке экспериментальных данных по концентрации веществ для каждого момента времени (из кинетической кривой) в кинетические уравнения реакций различных (нулевого, первого, второго и третьего) порядков [2-5].

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

Графический метод основан на том, что определяют такую функцию от концентрации, которая на графике зависимости ее от времени дает прямую линию. В соответствии с кинетическими уравнениями (aг) такими функциями являются: для реакции нулевого порядка — С; реакций первого порядка — lnC; реакций второго порядка — 1/С.

Исходные данные из таблицы 1 заносятся в столбцы А и В. По этим данным с помощью Мастера диаграмм строится точечная диаграмма зависимости концентрации от времени — кинетическая кривая (рис. 1).

Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

Аналогичным образом с помощью формул б), в), г) заполняются ячейки столбцов D, E, F. Соответствующие формулы Excel принимают вид: в ячейке D3: =LN(($B$2)/B3)/A3; в ячейке E3: =(1/B3-1/$B$2)/A3; в ячейке F3: =(1/B3^2-1/$B$2^2)/A3.

Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

Рис. 2. Таблица вычислений для определения порядка реакции аналитическим методом

Для определения порядка реакции графическим методом в столбцах G, H, I вычислим значения . Соответствующие формулы Excel примут вид: в ячейке G3: =LN(B3), функция LN содержится в библиотеке функций в категории «математические»; в ячейке H3: =1/B3; в ячейке I3: =1/B3^2. Все остальные ячейки столбцов G, H, I заполняются с помощью маркера заполнения.

Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

Рис. 3. Таблица вычислений для определения порядка реакции графическим методом

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

По результатам вычислений, представленным на рис. 3, с помощью мастера диаграмм строятся графики зависимости от времени t (рис. 4). Замечание: график зависимости C от t — построенная ранее кинетическая кривая.

Как видим, данная реакция имеет первый порядок, поскольку именно график зависимости ln C от t представляет собой прямую линию.

Как Решать Математической Задачи с Помощью Excel • 14 математическое моделирование

Рис. 4. Иллюстрация графического метода определения порядка реакции

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

Математическая статистика для «чайников»
8. Макаров А.А., Кулаичев А.П., Синева И.С. Использование программ обработки данных в преподавании курсов теории вероятностей, математической и прикладной статистики и информатики. Методические рекомендации (выпуск 1). – М.: МГУ, 2009.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Константин, Фёдор, спасибо за участие, а остальные, как в том анекдоте, поедут на картошку Тем более, сейчас на дворе конец сентября, а осень, как сказал прозаик, это клубни. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
При сохранении модели введите ссылку на первую ячейку вертикального диапазона пустых ячеек, в котором следует разместить модель оптимизации. При загрузке модели введите ссылку на весь диапазон ячеек, содержащий модель оптимизации.

Математическое моделирование.

На протяжении многих лет я всё думал, когда же доберусь до этой темы, и вот, наконец-то свершилось! …как и во многих делах, самое трудное – первый шаг, но я таки открыл вёрдовский файл (решался и обдумывал 2 недели) и с радостью и даже какой-то торжественностью написал первый абзац.

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

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: