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

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

§ выполнение различных вычислений с использованием функций и формул;

§ исследование влияния различных факторов на данные;

§ решение задач оптимизации;

§ получение выборки данных, удовлетворяющих определенным критериям;

§ упорядочивание данных;

§ графическое представление данных (построение графиков и диаграмм);

§ статистический анализ данных.

Рабочее пространство MS Excel 2010показано нарисунке 1 [6].

Рис. 1. Рабочее пространство MS Excel 2010:

1– Лента; 2 – Панель быстрого доступа; 3 – Строка формул;

4 – Рабочая книга; 5 – Группы, набор родственных команд

На Ленте расположены вкладки: Файл, Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид. На вкладке Файл доступен интерфейс пользователя Microsoft Office Backstage. Представление Backstage реализует различный доступ к основным операциям с документом, позволяет открывать, сохранять, выводить на печать файлы Excel, управлять ими и настраивать параметры. Для выхода из Представления Backstage необходимо открыть любую вкладку на Ленте или нажать клавишу ESC. Каждая группа имеет дополнительные параметры, которые запускаются с помощью диалоговых окон (рис. 2). Для скрытия и освобождения места на экране монитора, можно воспользоваться сочетанием клавиш CTRL+F1 или использовать кнопку (7) на рисунке 3, повторное нажатие отобразит Ленту.

Рис. 2. Дополнительные параметры Группы, Рис. 3. Скрытие/ отображение Ленты

6 – запуск диалоговых окон

Таблица 1

Задачи и команды

Задача	Вкладка	Расположение команд
Создание, открытие, сохранение, печать, предварительный просмотр, защита, отправка и преобразование файлов	ФАЙЛ
Вставка, удаление, форматирование и поиск данных в ячейках, столбцах и строках	ГЛАВНАЯ	Группы Число, Стили, Ячейки и Редактирование на Ленте
Добавление сводных таблиц, таблиц Excel (списки), диаграмм, гиперссылок, верхних и нижних колонтитулов	ВСТАВКА	Группы Таблицы, Диаграммы, Ссылки и Текст на Ленте
Установка полей и разрывов страниц, выбор области печати и настройка повторения строк	РАЗМЕТКА СТРАНИЦЫ	Группы Параметры страницы и Вписать на Ленте
Поиск функций, определение имен и устранение ошибок в формулах	ФОРМУЛЫ	Группа Библиотекафункций, Определенные имена и Зависимости формул, Вычисления на Ленте
Импорт данных, подключение к источнику данных, сортировка, фильтрация, проверка данных, выполнение анализа «что если»	ДАННЫЕ	Группы Получение внешних данных, Подключения, Сортировка и фильтр, Работа с данными на Ленте
Проверка орфографии, просмотр, исправления и защита книги	РЕЦЕНЗИРОВАНИЕ	Группы Правописание, Примечания и Изменения на Ленте
Изменение режима просмотра книги, выбор активной книги, упорядочение окон, закрепле-ние областей и запись макросов	ВИД	Группы Режимы просмотра книги, Окно и Макросы на Ленте

Для того чтобы изменить начальные установки программы, такие как количество пустых листов, создаваемых в новой книге, шрифт и его размер по умолчанию, необходимо открыть вкладку Файл и выбрать командуПараметры Excel (Общие). Рабочая книга в MS Excel позволяет сохранить разнообразную взаимосвязанную информацию в одном файле на нескольких листах. Для сохранения файла необходимо перейти на вкладку Файл (Excel2010)и выбрать необходимый формат сохранения документа, задать имя и указать путь.

При добавлении/исключении команд на Панель быстрого доступанужно выделить команду с помощью курсораи нажать правую клавишу мыши, в появившемся контекстном меню выбрать Добавить …илиУдалить …

Набор команд на Ленте настраивается в открытом диалоговом окне Файл→ПараметрыExcel→Настройка Ленты. Команды добавляются в любую группу, для этого щелкните по выбранной группе на Ленте правой клавишей мыши и выберите команду Настройка Ленты.

Рабочий лист MS Excel представляет собой таблицу и состоит из столбцов и строк.

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

Строки располагаются горизонтально, заголовок строки состоят из уникальной цифры.

Ячейка – пересечение столбца и строки (A1, B4….). После выделения ячейка становится активной, и ее ссылочный адрес отображается в строке формул слева. Можно выделить группу ячеек или область (диапазон ячеек). Для этого, не отпуская левой клавиши мыши, необходимо перетащить мышь до следующей ячейки (например, выделяем А4 и протягиваем до Е8, выделилось 25 ячеек). Теперь можно нажать клавишу CTRL, отпустить левую клавишу мыши, выделить другую ячейку, например В11, а затем перетащить мышь до ячейки D1. Будут выделены две прямоугольные области ячеек.

В ячейку можно вводить информацию в различных форматах (выделить ячейку, нажать правую клавишу мыши, Формат ячеек, выбираем формат вводимой информации) и формулы. При вводе числовой информации происходит автоматическое выравнивание по правому краю. Ввод формулы начинается со знака «=». Формула вводится самостоятельно в строке формул или используется Мастер функций f_x. Для ввода даты, дней недели, месяцев и т.д. используется функция Автозаполнение. Необходимо ввести информацию в две соседние ячейки (например, А2 – 9.30, А3 – 10.15), выделить ячейки, а затем поместить указатель мыши на нижний правый угол второй ячейки (А3). Указатель примет вид небольшого черного знака «+». Теперь нажмите и, удерживая левую клавишу мыши, перетащите указатель до необходимой ячейки, отпустите кнопку мыши.

Для объединения ячеек используется вкладка Главная/Выравнивание/Объединить и поместить в центре.

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

При наборе текста из нескольких строк в одной ячейке используется при переходе к следующей строке сочетание клавиш Alt+Enter.

В Excel различают тривида адресации:

§ относительная, например А1, D5

§ абсолютная, например $C$7, $F$3

§ смешанная, например $B2, K$6

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

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

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически изменяется, а абсолютная ссылка не изменяется. Например, при копировании смешанной ссылки из ячейки A2 в ячейку B3, она изменяется с =A$1 на =B$1.

Если Рабочая книга содержит несколько Листов, то им можно дать информативные имена нажав правую клавишу мыши и выбрав Переименование. Данные из электронных таблиц можно импортировать в СУБД MS Access.

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

Практическая часть работы в программе MS Excel отражает общую оценку здоровья по тесту Г.Л. Апанасенко. Тест Г.Л. Апанасенко является тестовой системой, которая объединяет важнейшие антропометрические показатели, анализ состояния вегетативной нервной системы по показателю «двойное произведение» (некоторые исследователи считают, что этот показатель позволяет косвенно судить о показателе максимального потребления кислорода) и состоя­ния гемодинамики по показателям пробы Мартине. Набор тестов, соединенных в систему, позволяет судить о соматическом здоровье человека и может быть использован для скрининговых исследований групп.

Начальные навыки:

1. Открываем Microsoft Excel. Сохраняем файл «Фамилия_Excel» в папочке с номером группы.

2. Откроем Лист 1 и переименуем его в «Задание 1», для этого два раза щелкните по названию «Лист1». Введите название.

3. Ввод данных: В ячейку А1 вводим число 2. В ячейку А2 – 4. В ячейку А3 – 8. В ячейку А4 – 9. В ячейку В1 – 2 (рис. 4).

4. Ввод формулы=А1+В1. Выделим ячейку С1. Вводим формулу: =А1+В1, нажимаем Enter. Сделаем активной ячейку с формулой С1, для этого курсор мыши подведем к нижнему правому углу ячейки, на экране появляется черный плюс (вместо белого плюса), нажимаем на него и растягиваем вниз до ячейки С4. Посмотрите на формулы в ячейках С1–С5.

5. Ввод формулы=А1+$В$1. Выделим ячейку D1. Вводим формулу: =А1+$В$1 и копируем ее по столбцу до ячейки D4. Сравните полученные значения и формулы с результатами в пункте 4.

Рис. 4. Относительная и абсолютная адресация

6. Вставка новой строки. Выделите первую строку, для этого щелкните правой кнопкой мыши по заголовку строки (1) и в контекстном меню выберите «Вставить» (рис. 5).

Рис. 5. Вставка новой строки

7. Объединение ячеек. В ячейку С1 введите « ». Выделите диапазон ячеек С1–D1 и нажмите «Объединить и поместить в центр» (вкладка Главная, группа Выравнивание, рис. 6). Чтобы текст поместился в объединённые ячейки, откройте контекстное меню правой кнопкой мыши и выберите «Формат ячеек». На вкладке «Выравнивание» поставьте галочку «Переносить по словам» и растяните границы ячейки. Для этого: подведите курсор между заголовками 1 и 2 строки и, удерживая левую кнопку мыши, растяните ячейку вниз. Переносить текст на другую строчку в одной ячейке можно с помощью сочетания клавиш ALT+Enter. Используя различные типы выравнивания группы Выравнивание (вкладка Главная) можно отформатировать набранный текст.

Рис. 6. Объединение ячеек

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

1. Весо–ростовой индекс (норма для мужчин 370–400, для женщин 325–375):

масса тела (в грамм)

рост (в см)

2. Жизненный индекс (мужчины 60–65 мл, женщины 50–55мл):

ЖЕЛ (мм³)

масса тела (кг)

3. Силовой индекс (мужчины 65–75%, женщины 45–50%):

сила кисти (кг)

масса тела (кг)

4. Двойное произведение:

ЧСС х АДсист

5. Проба Мартине оценивает скорость восстановления пульса до исходного уровня после выполнения 20 приседаний за 30 сек.

Ход работы:

1. Копирование данных. Открыть файл с исходными данными «Исходные данные задание 1–2». Открыть лист «Исходные данные». Выделите данные и скопируйте (CTRL+C).

2. Перейти на «Лист 2» в файле Excel «Фамилия_Excel» и вставить скопированные в п.1 данные.

3. Расчет весо–ростового индекса

1) Вставить новый столбец.

Выделите столбец D щелчком правой кнопкой мыши по заголовку столбца и в контекстном меню (рис. 7) выберите пункт «Вставить».

2) Ввод заголовка.

В ячейку D1 ввести текст «В–Р индекс, г/см»

Рис. 7. Вставка нового столбца

3) Ввод формулы =B2/C2

Выбрать ячейку D2 и ввести в рабочую строку формулу =B2/C2 (рис. 8),
нажать Enter.

4) Копирование формулы. Растянуть формулу по столбцу для всех данных.

Рис. 8. Ввод формулы =B2/C2

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

5) Округление значений до сотых долей (изменение количество знаков после запятой в выделенных ячейках).

Открыть контекстное меню щелчком правой кнопкой мыши по заголовку столбца D и выбрать «Формат ячейки…» (рис. 9). На вкладке «Число» выберем «Числовой», обозначим число десятичных знаков – 2 (рис. 10).

Рис. 9. Открытие «Формат ячеек…»

Рис. 10. Изменение количество знаков после запятой

4. Расчет Жизненного индекса

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

1) Ввести в ячейку А31 «пересчет в кг» (рис. 11), в ячейке В31 указать количество грамм, содержащихся в 1 кг («1000»).

2) Создать столбец для показателя жизненного индекса.

3) Ввести в ячейку F2 (рис. 11) формулу «=Е2/В2*$B$31» (знак $ означает абсолютную ссылку, которая при копировании формулы в другие ячейки не изменяется).

4) Копировать формулу для всех данных.

5) Изменить количество знаков после запятой.

Рис. 11. Расчет Жизненного индекса

5. Расчет Силового индекса

1) Создать столбец для показателя силового индекса.

2) Ввести в ячейку H2 формулу, учитывая, что для расчета показателя силового индекса требуется перевести вес тела обследуемого из единиц измерения грамм в килограмм (см. п. 6) и в проценты: «=G2/B2*$B$31*100».

3) Копировать формулу для всех данных.

4) Изменить количество знаков после запятой.

6. Аналогично произведите расчет двойного произведения:

ЧСС∙АДсист

7. Окончательный вид рабочего окна представлен на рисунке 12.

Рис. 12. Расчет всех показателей

8. Сохранить, полученный результат.