Тема: «Дополнительные возможности Microsoft Office Excel».

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

 

Табл. 4 Оценка количества соматического здоровья

Показатели (индексы/ баллы)		Функциональные уровни
	Женщины
1 Низкий	2 Ниже среднего	3 Средний	4 Выше среднего	5 Высокий
Индекс Кетле (г/см)	451 и выше	351-450	350 и ниже	—
Баллы	-2	-1		—	—
Жизненный ин- декс (мл/кг)	40 и ниже	41-45	46-50	51-55	56 и выше
Баллы
Силовой индекс (%)	40 и ниже	41-50	51-55	56-60	61 и выше
Баллы
Двойное произведение	101 и выше	95-100	85-94	70-84	69 и ниже
Баллы	-2
Проба Мартине (с)	180 и выше	120-179	90-119	60-89	59 и ниже
Баллы	-2
Сумма баллов	4 и ниже	5-9	10-13	14-16	17-21

 

Для решения этой задачи в Exсel применяются функции ЕСЛИ и И, которые используется при проверке условий для значений и формул.

Синтаксис:

 

ЕСЛИ (лог_выражение;значение_если_истина; значение_если_ложь)

Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A1=100 — это логическое выражение; если значение в ячейке A1 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ.

И (логическое_значение1; логическое_значение2;…). Проверяет все ли аргументы имеют значение ИСТИНА, и возвращает значение ИСТИНА, если истинны все аргументы.

Ход работы:

1. Открыть файл с данными, полученными после действий, описанных в предыдущем разделе (см. рис.22)

2. Создать столбец после Индекса Кетле и назвать Индекс 1.

3. Для ввода в ячейку Е2 использовать мастер функций. Для этого вы-

брать ячейку Е2 и нажать , выбрать функцию ЕСЛИ (рис.23). В появившемся окне «Аргументы функции» (рис.24) ввести первое условие согласно данным в табл.1. и нажать «ЕСЛИ». В появившемся втором окне «Аргументы функции» (рис. 25) ввести второе условие и третье в строку «значение_если_ложь» согласно данным табл.1. Скопировать формулу во все ячейки списка и «щелчком» правой кнопкой мыши вызвать контекстное меню, выбрать «Формат ячеек…», в открывшемся меню выбрать числовой формат «Общий». Окончательный результат представлен на рис.26.

 

Рисунок 23

. Выбор функции ЕСЛИ

 

Рисунок 24. Первое условие для индекса 1

 

Рисунок 25. Второе условие для индекса 1

 

Рисунок 26. Окончательный вид формулы для подсчета индекса 1

 

 

4. Далее создать столбец после жизненного индекса и ввести название Индекс 2.

 

5. Для ввода в ячейку H2 использовать окна мастер функций и аргументы функций. В окне «Аргументы функции» (рис.28) ввести первое условие G2<41 согласно данным табл.1 для жизненного индекса, значение истины 0 и нажать ЕСЛИ. В появившемся втором окне Аргументы функции ввести второе условие, используя функцию И: И(G2>40;G2<46), значение истины 1 и нажать ЕСЛИ (рис.29). В третьем окне Аргументы функции ввести третье условие, используя функцию И: И(G2>45;G2<51), значение истины 2 (рис.20) и нажать ЕСЛИ. В четвертом окне «Аргументы функции» ввести четвертое условие G2>56, значение истины 5 и значение, если ложь 4 (рис.30).

 

 

6. Скопировать формулу во все ячейки списка и «щелчком» правой кнопки мыши вызвать контекстное меню, выбрать «Формат ячеек…», в открывшемся меню числовой формат «Общий». Окончательный результат представлен на рис.31.

 

 

Рисунок 27. Первое условие для расчета индекса 2.

 

Рисунок 28. Второе условия для расчета индекса 2.

 

Рисунок 29. Третье условие для расчета индекса 2.

Рисунок 30. Четвертое условие для расчета индекса 2.

Рисунок 31. Окончательный вид формулы для расчета индекса 2.

 

7. Окончательно формула расчета баллов по жизненному индексу (индекс 2) для ячейки G2 в ячейке H2 выглядит так:

= ЕСЛИ(G2<41;0;ЕСЛИ(И(G2>40;G2<46);1;ЕСЛИ(И(G2>45;G2<51);2; ЕСЛИ(G2>56;5; 4))))

 

8. Аналогичным образом рассчитать баллы для:

 

• Индекса 3

= ЕСЛИ(J2>60;5;ЕСЛИ(J2<41;0;ЕСЛИ(И(J2>40;J2<51);1;ЕСЛИ(И(J2>50

;J2<56);2;3))))

• Индекса 4

= ЕСЛИ (О2>101;-2;ЕСЛИ (О2<70;4;ЕСЛИ (И(О2>69;O2<85);3;ЕСЛИ

(И (О2 >84;O2 <95);2;0))))

• Индекса 5

= ЕСЛИ(Q2>179;2;ЕСЛИ(И(Q2>119;Q2<180);1;ЕСЛИ(И(Q2>89;Q2<120);3;ЕС ЛИ(Q2<59;7;5))))

После всех действий получится рабочее окно, показанное на рис.32

 

 

Рисунок 32. Вид рабочего окна после подсчета 5-ти индексов

 

9. В столбце S необходимо рассчитать сумму баллов по пяти индексам с суммированием содержимого ячеек: =E2+H2+K2+P2+R2.

 

10. Далее нужно вычислить среднюю сумму баллов для всех обследованных. Для этого в ячейку Р31 ввести текст «средняя сумма баллов». Выделить ячейку S31 и вызывать рабочее окно «Мастера функции», выбрать функцию СРЗНАЧ (рис.33) и в окне «Аргументы функции» проверить выделенный диапазон в строке «Число_1» и нажать “Ok” (рис.34). Изменить формат ячейки S31 на числовой с 1 знаком после запятой (рис.35).

Рисунок 33

. Функция среднее значение

Рисунок 34. Диапазон S2:S30

 

 

Рисунок 35 Средняя сумма баллов

11. Далее необходимо оценить полученную сумму баллов согласно табл.1 с помощью функции ЕСЛИ. Для этого нужно создать столбец после суммы баллов и ввести текст Общая оценка здоровья. Для ввода в ячейку Т2 использовать окно мастер функций и аргументы функций. В окне «Аргументы функции» (рис.35) ввести первое условие S2<5 согласно данным в табл.1 значение истины "низкий" и нажать ЕСЛИ. В появившемся втором окне «Аргументы функции» ввести второе условие, используя функцию И: И(S2>4;S2<10) значение истины "ниже среднего" и нажать ЕСЛИ (рис.36). В третьем окне «Аргументы функции» ввести третье условие, используя функцию И: И(S2>9;S2<14), значение истины "средний" (рис.37) и нажать ЕСЛИ. В четвертом окне «Аргументы функции» ввести четвертое условие S2>16, значение истины "высокий" и значение, если ложь "выше среднего" (рис.38). Формула будет выглядеть так:

=ЕСЛИ(S2<5;"низкий";ЕСЛИ(И(S2>4;S2<10); "ниже среднего";

ЕСЛИ(И(S2>9;S2<14);"средний";ЕСЛИ(S2>16;"высокий"; "выше среднего"))))

12. Скопировать формулу во все ячейки списка и «щелчком» правой кнопки мыши вызвать контекстное меню, выбрать «Формат ячеек…», в открывшемся меню формат «Общий» (рис 39).

Рисунок 36. Первое условие для подсчета общей оценки здоровья

 

Рисунок 37. Второе условие для подсчёта общей оценки здоровья

 

 

Рисунок 38. Третье условие для подсчета общей оценки здоровья

 

Рисунок 39. Окончательный вид формулы для подсчета общей оценки здоровья.

13. Далее нужно сосчитать количество обследуемых, имеющих низкую, ниже среднего, среднюю, выше среднего и высокую оценку здоровья. Для этого нужно ввести в ячейки S34 «низкий», S35 «ниже среднего», S36 «средний», S37 «выше среднего», S38 «высокий» оценки здоровья. Выбрать ячейку Т34 и открыть «Мастер функций». Изменить в строке «Категория» на «Полный алфавитный перечень» и в окне «Выберите функцию» найти СЧЕТЕСЛИ и перейти в окно «Аргументы функции» (рис.40). Выбрать строку «Диапазон» и выделить (не отпуская левую кнопку мыши) ячейки с Т2 по Т30. В строку «Критерий» ввести «низкий» и нажать Ok. (рис. 41). Аналогично сосчитать количество в остальных группах по оценке здоровья (рис.42).

 

Рисунок 40

. Выбор функции СЧЕТЕСЛИ

Рисунок 41

. Аргументы функции

 

Рисунок 42. Количество обследуемых по группам здоровья

 

14. Далее следует построить круговую диаграмму по данным распределения обследуемых по уровню здоровья. Выбрать ячейку в диапазоне S34:T38. В меню Вставка выбрать пункт Диаграммы (рис. 43) и выбрать круговую диаграмму.

Рисунок 43

. Построение круговой диаграммы.

15. Название диаграммы.

Для того чтобы назвать диаграмму нужно «щелкнуть» саму диаграмму и на панели управления будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат. На вкладке Макет в группе Подписи нажмите кнопку Название диаграммы (рис. 44).

 

Рисунок 44. Название диаграммы.

 

Выбрать пункт Название по центру с перекрытием или Над диаграммой. В текстовом поле Название диаграммы, появившемся в диаграмме, введите «Оценка уровня здоровья». (Рис 45)

 

Рисунок 4

. Диаграмма «Оценка уровня здоровья»