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

Показатели	Функциональные уровни
(индексы/
баллы)
	Женщины
Весо–ростовой (г/см)	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
Оценка	Низкий	Ниже среднего	Средний	Выше среднего	Высокий

Ход работы:

1. Откройте файл с данными, полученными после действий, описанных в предыдущем разделе: «Фамилия_Excel».

2. Расчет Индекса 1

1) Создать столбец после весо–ростового индекса и назвать («Индекс 1»).

2) Ввод функции ЕСЛИ.

Выбрать ячейку Е2 и нажать и выбрать функцию ЕСЛИ (рис. 13). В появившемся окне «Аргументы функции» (рис. 14) ввести первое условие согласно данным таблицы 2 и нажать «ЕСЛИ». В появившемся втором окне «Аргументы функции» (рис. 15) ввести второе условие и третье в строку «значение_если_ложь» в соответствии с данными таблицы 2.

3) Скопировать формулу во все ячейки списка

4) Изменить формат на «Общий».

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

Рис. 13. Вызов функции ЕСЛИ

3. Установить курсор

1. Ввести условие

2. Ввести значение, если условие выполняется

4. «Щелкнуть» для ввода следующего условия

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

3. Ввести значение, если условие не выполняется

2. Ввести значение, если условие выполняется

1. Ввести условие

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

Окончательный результат представлен на рисунке 16.

Рис. 16. Расчет Индекса 1

3. Расчет Индекса 2

Обратимся снова к таблице 2. Довольно часто в литературе приводятся интервалы значений, подразумевающие только анализ целых чисел и приводящих к формальной ошибке, если число дробное. Какой индекс 1 следует дать, пользуясь таблице 2, человеку, у которого весо–ростовой индекс 350,5? Мы даем 0, если 350 и ниже и –1 если 351 и выше. Таким образом, 350,5 вообще оказывается неоцененным значением.

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

Индекс 2 имеет ту же проблему в определении. Между 40 и 41, 45 и 46, 50 и 51, 55 и 56 значения индекса не определены. Выберем в качестве граничных точек 40, 45, 50 и 55 (рис. 17).

При обозначении диапазона чисел круглые скобки обозначает, что число не включаются в полуинтервал. Квадратная скобка обозначает, что данная точка во множество включена. Например, на рис. 17 число 40 включено в полуинтервал от минус бесконечности до 40 и не включено в интервал от 40 до 45.

Так, при значении индекса:

§ до 40 включительно – 0 баллов

§ от 40 до 45 включительно – 1 балл

§ от 45 до 50 включительно – 2 балла

§ от 50 до 55 включительно – 4 балла

§ от 55 и выше – 5 баллов

Рис. 17. Жизненный индекс, соотношение баллов и значений

Создание условного выражения с вложенным «если» удобно осуществлять выделяя по очереди интервалы слева направо (от меньшего к большему) или справа налево (от большего к меньшему).

При движении слева направо следует пользоваться знаком < или <=, в зависимости от того, в какой из соседних интервалов входит точка. Сначала обрабатываем интервал от «минус бесконечности» до 40 (знак<=), затем от 40 до 45 (знак <=), затем от 45 до 50 (знак <=) и т.д.

При движении справа налево выделяем интервалы от большего числа к меньшему, пользуясь знаком > или >=. Сначала обрабатываем интервал от 55 (знак >) до бесконечности, затем от 50 (знак >) до 55 и т.д.

Приведем пошаговые инструкции для создания вложенного «если» при движении слева направо.

1) Создать столбец после жизненного индекса и ввести «Индекс 2».

2) Ввод функции ЕСЛИ, для этого надо составить 4 условия:

a) «Лог_выражение»: G2(значение из первой ячейки, показывающей
индекс) <= 40.

b) Так, если наше условие выполняется, то есть это ИСТИНА, выставляется 0 баллов. Поэтому записываем «0» в «Значение_если_истина». Помним, что условие выполняется при значении до 40 включительно. Далее ставим курсор на строку «Значение_если_ложь» (обязательно!) и ещё раз нажимаем на функцию «ЕСЛИ», как это показано на рисунке 14(п4). Таким образом, мы продолжаем функцию, задав следующее условие;

c) «Лог_выражение»: G2 <= 45. «Наслаиваем» теперь это условие на предыдущее. Как было показано в начале, такой интервал от 41 до 45 включительно даёт нам 1 балл. Поэтому ставим цифру «1» в «Значение_если_истина». Далее ставим курсор на строку «Значение_если_ложь». Делаем так же, как и в пункте 1 и открываем новую функцию «ЕСЛИ».

d) «Лог_выражение»: G2 <= 50.Принцип сохраняется. Но в этом интервале при соблюдении условия ставится другой балл – «2». Проделываем все те же операции и открываем последнее «ЕСЛИ».

e) Лог_выражение»: G2 <= 55.Значение балла при ИСТИНЕ очевидно – 4 балла, как было показано выше. Но если условие не соблюдено, и индекс больше 56 включительно, то есть логическое выражение – ЛОЖЬ, то выставляется 5 баллов (это можно легко проследить по таблице 2. Поэтому, так и ставим 5 баллов в строку «Значение_если_ложь».

Теперь можно нажать ОК.

В итоге формула в ячейке H2 будет иметь такой вид:
=ЕСЛИ(G2<=40;0;ЕСЛИ(G2<=45;1;ЕСЛИ(G2<=50;2;ЕСЛИ(G2<=55;4;5))))
(рис. 18).

Рис. 18. Расчет Индекса 2

Не стоит забывать, что выражение может быть составлено и наоборот, как в случаях с индексом двойного произведения (рис. 19) и индексом Мартине (рис. 20) (см. таблицу 2):

Рис. 19. Расчет Индекса 4

Рис. 20. Расчет Индекса 5

4. Рассчитать баллы для:

§ силового индекса

§ показателя двойного произведения

§ пробы Мартине

После всех выполненных действий получится следующее рабочее
окно (рис. 21).

Рис. 21. Расчет пяти индексов

5. Общая сумма баллов и ее оценка. Введите после столбца Индекс 5 новый столбец и назовите его «Сумма баллов». Рассчитайте сумму баллов по пяти индексам суммированием содержимого ячеек: «=E2+H2+K2+P2+R2». Если необходимо, то измените с помощью правой клавиши мыши Формат ячеек/Число: числовой формат, число десятичных знаков = 0.

6. Средняя сумма баллов. Вычислите среднюю сумму баллов для всех обследованных. В ячейку Р31 ввести «Средняя сумма баллов». Выделить ячейку S31 и вызывать рабочее окно «Мастера функции», выбирать функцию СРЗНАЧ (рис. 22), в окне «Аргументы функции» проверить выделенный диапазон в строке «Число_1» и нажать «Ok» (рис. 23). Аналогично, можно выбрать вкладку Главная/Редактирование/значок Математической суммы, в выпадающем меню выбираем Среднее, компьютер автоматически выделяет диапазон S2:S30.

Рис. 22. Функция СРЗНАЧ

Рис. 23. Аргументы функции СРЗНАЧ

Изменить формат ячейки S31 на числовой с 1 знаком после запятой (рис. 24).

7. Оценка суммы баллов. Оцените, полученную сумму баллов помощью функции ЕСЛИ. Для этого введите новый столбец Общая оценка здоровьяи, анализируя диапазон S2:S30, введите характеристику здоровья для каждого пациента в соответствии с функциональными уровнями здоровья (табл. 2).

Отформатируйте диапазон ячеек S2:S30 с использованием гистограммы. Для этого необходимо выделить указанный диапазон, перейти по вкладке Главная, в группе Стили щелкнуть стрелку рядом с кнопкой Условное форматирование и выделить пункт Гистограммы, затем выбрать гистограмму (рис. 25). Гистограммы соответствуют значению в ячейке и помогают оптимально сравнить значения ячеек между собой.

Рис. 24. Расчет суммы баллов и средней суммы баллов

Аналогично, используйте условное форматирование для диапазона T2:T30, но в этом случае на вкладке Главная, в группе Стили щелкните стрелку рядом с кнопкой Правила выделения ячееки выделите пункт Текст содержит… (рис. 26).

Рис. 25. Форматирование ячеек с использованием гистограммы

Рис. 26.Форматирование ячеек с текстовыми значениями

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

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

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

Рис. 28. Аргументы функции СЧЕТЕСЛИ

Аналогично подсчитайте количество обследуемых по оценке здоровья в остальных группах (рис. 29).

9. Круговая диаграмма. Постройте круговую диаграмму по данным распределения обследуемых в соответствии с уровнем здоровья.

1) Выбрать ячейки в диапазоне S34:T38.

Рис. 29. Оценка физического развития (здоровья)

2) Выбрать вкладку Вставка и группу Диаграммы, щелкните по кнопке Круговая.

3) Выбрать Объемную или Обыкновенную диаграмму.

4) Поменять стиль диаграммы: на вкладке Работа с диаграммами/Конструктор и в группе Макеты диаграмм щелкнуть по кнопке с выбранным макетом (например, Макет № 6). В поле название диаграммы пишем «Оценка уровня здоровья».

5) Переместить диаграмму на отдельный лист используя вспомогательную вкладку Работа с диаграммами, группу Расположение (рис. 30).

6) Изменить внешний вида диаграмм: вкладка Работа с диаграммами/Конструктор, группа Тип,кнопка Изменение типа диаграмм
(рис. 31).

7) Название диаграммы, подписи данных и расположение легенды меняется на вкладке Работа с диаграммами/Макетв группе Подписи.При использовании группы Макеты диаграмм все вносится в соответствии с выбранным макетом.

8) Выделим пункт Добавить легенду снизув выпадающем меню кнопки Легенда группыПодписина вкладке Работа с диаграммами/Макет.

9) Выделим пункт Дополнительные параметры подписей данных …в выпадающем меню Подписи данных на вкладке Работа с диаграммами/Макет и поставим галочки на пунктах Имена категорий и Доли, Положение подписи выберем По ширине (рис. 32), нажмем кнопку Закрыть.

Рис. 30. Размещение диаграммы

Рис. 31. Изменение типа диаграмм

Рис. 32. Выбор параметров подписи

Тема 5 Построение графиков, сортировка, фильтрация, проверка данных

1) Фильтрация данных. Необходимо отфильтровать данные по столбцу «Общая оценка здоровья», оставив только строки с пациентами, у которых уровень здоровья «ниже среднего», «средний» и «выше среднего». Для этого:

a. Выделить область, подлежащую фильтрации (T2:T30), далее нажмите на «Сортировка и фильтр» и выберите «Фильтр»

b. В ячейке Т2 появился значок списка.

c. Щелкнув на этот значок оставьте галочки только на нужных позициях («ниже среднего», «средний» и «выше среднего»).

2) Сделайте сортировку данных в столбце ЧСС по возрастанию. Для этого:

a. Выделите столбец ЧСС и далее нажмите на «Сортировка и фильтр».

b. Выберите «Сортировка от минимального к максимальному»

и далее

Таким образом, все данные перестроились в соответствии возрастания в столбце ЧСС.

3) Постройте график зависимости y=f(x), где у - АД сист., х – ЧСС.

a. Выделите все числовые значения в столбце ЧСС и, удерживая Ctrl, продолжите выделение другого столбца – АД сист.

b. На вкладке «Вставка», группе «Диаграммы» выберите «Точечная», «Точечная с маркерами».

c. На получившемся графике измените формат оси, щёлкнув правой кнопкой мыши на ось и выбрав пункт «Формат оси».

d. Сделайте так, чтобы ось абсцисс(х) и ось ординат(у) начиналась не с нуля, а с минимальных значений ЧСС и АД сист. соответственно.

e. Дважды щёлкните по диаграмме. На вкладке конструктор, в группе «Макеты диаграмм» выберите Макет 9. Данный макет диаграммы добавляет на график линию тренда. С помощью тренда можно увидеть, что с повышением ЧСС наблюдается повышение АДсист.

f. Просмотрите остальные настраиваемые свойства осей и измените их на своё усмотрение.

4) Добавление элемента выбора из списка.

a. Перейдите на лист «Врачи». В столбце А сформирован список врачей. Дополните его по желанию.

b. Перейдите на лист «Исходные данные». Поставьте курсор в ячейку С35.

c. На вкладке «Данные» в группе «Работа с данными» выберите «Проверка данных».

d. Выберите в типе данных «список» и укажите в источнике тот список врачей, который находится на листе «Врачи». Нажмите ОК.

e. Выберите любую фамилию врача из списка.

5) В ячейке В37 поставьте сегодняшнюю дату, с помощью формулы =СЕГОДНЯ().

6) Сохраните работу в своей папке.

Список используемой литературы

1. Макарова Н.В., Волков В.Б., Информатика: учебник для вузов. – СПб.: Питер, 2011. 576 с.

2. Государственный стандарт РФ «Защита информации. Порядок создания автоматизированных систем в защищенном исполнении» (ГОСТ Р 51583-2000).

3. Ершов А.П. Информатика: предмет и понятие. Кибернетика. Становление информатики. –М.: Наука, 1986.

4. Лаптев В.В., Швецкий М.В. Методическая система фундаментальной подготовки в области информатики: теория и практика многоуровневого педагогического университетского образования. СПб, Изд-во СПбГУ, 2000.

5. Колин К.К. Социальная информатика: учебное пособие для вузов. М.: Фонд «Мир», 2003 г.

6. Техническая библиотека комплекта ресурсов Office 2010. (http://go.microsoft.com/fwlink/?linkid=181453&clcid=0x419) на дату публикации.

Содержание

Предисловие. 3

Тема 1. Теоретические основы Microsoft Office Excel 6

Тема 2. Практические основы.. 11

Тема 3. Расчет показателей. 13

Тема 4. Оценка физического развития (здоровья) 17

Приложение 1. ………………………………………………………………………………………… 34

Приложение 1