MS Excel. Моделирование

Постановка задачи

Существует легенда о том, что в древнем Китае монахи день за днем вели наблюдения за человеком, записывая параметры его физической активности, умственных способностей и эмоционального состояния. В результате многолетних исследований они пришли к выводу, что эти функции являются периодическими с периодами для физической активности (23+n) дня, эмоциональной – (28+n) дней и интеллектуальной – (33+n) дня (здесь и далее n - номер Вашего варианта). Характерная особенность этой гипотезы заключается в том, что функции состояния человека в момент его рождения равны нулю, затем начинают возрастать, каждая за свой период принимает одно положительное максимальное и одно отрицательное минимальное значение.
Таким образом, можно сделать вывод, что биологические ритмы могут быть описаны функциями вида

Здесь t - время,T_k - периоды (для физической активности T_1=23+n, эмоциональной – T_2=28 +n и интеллектуальной – T_3=33+ns), k - номер периода. Началом всех трех кривых (t_0) является день рождения.

ЗАДАНИЕ

Создать среду для расчета календаря биоритмов на (30+n) дней для двух персон, построить графики биоритмов и проанализированть совместимость персон. Результат сохранить под именем вида Фамилия_Модель

Порядок выполнения работы

1. Разработка общего вида таблицы Исходных данных со вспомогательной справочной информацией

1.1. На листе под именем вида Фамилия_ДопТаблицы создайте две вспомогательные таблицы следующего вида:

1.2. На листе под именем вида Фамилия_ИсходныеДанные создайте таблицу для ввода персональных данных для двух персон:

Примечание: Обратите внимание, что для Персоны 2 используется узорная заливка (цвет узора - белый), причем основной цвет заливки и тип узора должны соответствовать варианту. Выбранной узорной заливкой в последующем выделяются все данные, касающиеся Персоны 2.

1.3. Ниже на этом же листе разместите раздел Справочная информация. Все значения, выделенные красным цветом или подчеркнутые, вычисляются по формулам.

Примечание: В вычислениях используются функции СЕГОДНЯ(), ДАТА(), ДЕНЬНЕД(), ВПР(), ЕСЛИ() и таблица Дни недели с листа Фамилия_ДопТаблицы. Функция ЕСЛИ() используется для вывода слов ДО/ПОСЛЕ в зависимости от знака полученного числа прожитых дней.
Цвет заливки выбирается в тон заливке таблиц из п. 1.2.

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

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

Здесь t - время,T_k - периоды (для физической активности T_1=23+n, эмоциональной – T_2=28+n и интеллектуальной – T_3=33+n), k - номер периода. Началом всех трех кривых (t_0) является день рождения.

2.1. На новом листе Фамилия_СправочнаяТаблица создайте таблицу вида:

2.2. Заполните эту таблицу на 30+n дней вперед от даты биоритмов.

2.2.1. В таблице Периоды биоритмов (Т_k) значения T_k определены в постановке задачи, значения во второй строке этой таблицы вычисляются по соответствующей формуле.

Основная таблица

2.2.2. Дата начала построения графика совпадает с Датой биоритмов на листе Фамилия_ИсходныеДанные

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

"Биоритмы Персоны 1 (Персоны 2)"

2.2.4. В графе Прожитые дни необходимо вычислить значения выражения t-t_0, где t_0 - дата рождения Персоны 1 (Персоны 2) с листа Фамилия_ИсходныеДанные, t - конкретная дата, для которой проводятся вычисления (для первой строки это дата начала расчетов биоритмов, а в последующих строках значение предыдущей строки увеличивается на 1).

2.2.5. В графы Физический, Эмоциональный, Интеллектуальный необходимо ввести формулы для просчёта значений функций

Обратите внимание на то, что в таблице Периоды биоритмов (Т_k) просчитаны значения выражений 2p/T_k, а в графе Прожитые дни - значения t-t_0. Для полученных числовых значений необходимо утсновить (n+1) знаков после запятой

2.2.6. При заполнении графы Подъемы и упадки необходимо проанализировать числовые значения в графах Физический, Эмоциональный и Интеллектуальный. Если значение положительное, то в соответствующую графу записывается "1", в противном случае – "0". При заполнении этих граф следует использовать функцию ЕСЛИ().

2.2.7. В графе Количество совпадений вычислите сумму значений трех предыдущих граф.

"Анализ совместимости"

2.2.8. При заполнении графы Совпадение подъемов и упадков необходимо сравнить значения в графах Подъемы и упадки для Персоны 1 и Персоны 2. Если значения совпадают, то в соответствующую графу записывается "1", в противном случае – "0". При заполнении этих граф следует использовать функцию ЕСЛИ().

2.2.9. В графе Количество совпадений вычислите сумму значений трех предыдущих граф.

3. Разработка Графика биоритмов

На отдельном листе Фамилия_ГрафикБиоритмов постройте график биоритмов, используя информацию с листа Фамилия_СправочнаяТаблица, и оформите его следующим образом, учитывая сделанные в примечании уточнения для вариантов

Примечание: оформление графика должно соответствовать варианту

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

На отдельном листе Фамилия_Биоритмы разместить Таблицу биоритмов и Таблицу анализа совпадений, которая представляет собой обобщенную информацию c листа Фамилия_СправочнаяТаблица. Вид таблиц должен быть следующим:

4.1. Заполнеие Таблицы биоритмов для Персоны 1 и Персоны 2

4.1.1. Заполнение первой графы аналогично графе Дата листа Фамилия_СправочнаяТаблица.

4.1.2. При заполнении второй графы следует использовать функции ДЕНЬНЕД() и ВПР(), а также таблицу Дни недели с листа Фамилия_ДопТаблицы.

4.1.3. Заполнение граф Физические, Эмоциональные и Интеллектуальные производится на основе граф Подъемы и упадки: Ф, Э, И для Персоны 1 и Персоны 2 листа Фамилия_СправочнаяТаблица: если значение графы равно нулю, то в соответствующую графу листа Таблица биоритмов записывается "минус", а если – единица, то – "плюс". При заполнении следует использовать функцию ЕСЛИ().

4.1.4. Заполнение последней графы Прогноз осуществляется на основе граф Количество совпадений (Персона ?) с листа Фамилия_СправочнаяТаблица и таблицы Прогноз с листа Фамилия_ДопТаблицы. При заполнении следует использовать функцию ВПР().

4.1.5. Для большей наглядности при оформлении таблицы следует использовать условное форматирование. Так, в графах Физическое, Эмоциональное, Интеллектуальное предусмотреть красную заливку для ячеек, содержащих "-", и зеленую для ячеек с "+", а для графы Прогноз задать разные цвета для различных состояний.

4.2. Заполнеие Таблицы анализа совпадений биоритмов для Персоны 1 и Персоны 2

4.2.1. Для заполнения первой и второй графы можно использовать значения графы Датаиз Таблицы биоритмов.

4.2.2. Заполнение граф Физические, Эмоциональные и Интеллектуальные производится на основе граф Совпадения подъемов и упадков: Ф, Э, И листа Фамилия_СправочнаяТаблица: если значение графы равно нулю, то в соответствующую графу листа Таблица биоритмов записывается "минус", а если – единица, то – "плюс". При заполнении следует использовать функцию ЕСЛИ().

4.2.3. Заполнение последней графы Совпадение осуществляется на основе последней графы Количество совпадений с листа Фамилия_СправочнаяТаблица и таблицы Прогноз с листа Фамилия_ДопТаблицы. При заполнении следует использовать функцию ВПР().

4.2.4. Аналогично п. 4.1.5 для большей наглядности при оформлении таблицы следует использоватьусловное форматирование. Так, в графах Физическое, Эмоциональное, Интеллектуальное предусмотреть красную заливку для ячеек, содержащих "-", и зеленую для ячеек с "+", а для графы Прогноз задать разные цвета для различных состояний.

4.2.5. В строке Количество совпадений посчитать количество "+" в соответствующих столбцах Физические, Эмоциональные и Интеллектуальные с использованием функции СЧЁТЕСЛИ().

4.2.5. В строке Степень совпадений с использованием функции ЕСЛИ() интерпретировать полученные в предыдущей строке результаты по правилу: если количество совпадений менее 10+n/3, то степень совпадений считается низкой; если количество совпадений более 20+2n/3, то степень совпадений считается высокой; в остальных случаях степень совпадений считается средней.