Файл: Лабораторная работа ms excel Ввод и форматирование данных Цель работы отработка базовых навыков работы в ms.doc

2. Лист 2 переименуйте как ФИЗКУЛЬТУРА.

а) Создайте и оформите на этом листе следующую таблицу:

	A	B	C	D	E	F
1	Физическое развитие
2	Фамилия	Дата рождения	Рост	Отклонение от нормы роста, %	Вес	Отклонение от нормы веса, %
3
4	Девочки
5	Замятина Анна	21.09.83	153		45
6	Захарова Светлана	24.11.84	151		45
7	Арканова Ольга	15.10.83	154		49
8	Волошина Наталья	15.07.83	162		51
9	Иванова Екатерина	07.01.83	160		51
10	Копылова Светлана	22.01.83	154		57
11	Левченко Ольга	24.03.83	162		68
12	Свиридова Елена	23.02.84	171		73
13	Удалова Мария	10.05.84	167		50
14	Среднее значение:
15
16	Мальчики
17	Новосельцев Андрей	12.12.83	154		45
18	Иванов Иван	12.09.84	153		47
19	Титов Владислав	29.06.83	165		52
20	Рогожин Павел	07.03.84	164		53
21	Шейнин Алексей	06.06.84	161		56
22	Воронин Александр	26.03.84	170		60
23	Петров Денис	26.01.84	171		63
24	Булавин Михаил	13.08.83	175		65
25	Савельев Михаил	06.04.84	158		54
26	Суворов Сергей	10.01.84	149		61
27	Егоров Николай	07.10.83	173		71
28	Среднее значение:

---

- Вычислите среднее значение Роста и Веса отдельно для мальчиков и отдельно для девочек.

- Заполните столбцы «Отклонение от нормы роста, %», «Отклонение от нормы веса, %», введя в ячейки соответствующие формулы. За норму у девочек примите: рост 154, вес 53. За норму у мальчиков – рост 150, вес 50.

- Название таблицы выполните подчеркнутым полужирным шрифтом 16 размера.

- Отформатируйте таблицу командой Автоформат – Список 1.

- Названия «Пол: Девочки», «Пол: Мальчики» отформатируйте полужирным подчеркнутым шрифтом.

- Строки Среднего значения Роста и Веса отформатируйте полужирным шрифтом.

- Сохраните изменения в Вашем файле.

З адание 3. Использование функций «Дата и время»

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

1. 
   Откройте книгу Расчеты.xls. Следующий лист назовите Юбилей. На нем оформите следующую таблицу:
   
2. 
   В столбце D вычислим возраст. Для этого нужно из текущей даты вычесть дату рождения, а затем полученный результат разделить на число 365 (число дней в году). Деление на 365 нужно, чтобы результат перевести в годы. Полученное число необходимо округлить до целого. Другими словами, в ячейке D2 нужно создать следующую формулу: =ЦЕЛОЕ((СЕГОДНЯ()-С2)/365)
   

Для этого выполните следующие действия:

• 
  Сделайте активной ячейку D2.
  
• 
  В ставьте Математическую функцию ЦЕЛОЕ.
  
• 
  В поле имени (в левой зоне строки формул) вызовите из списка функцию СЕГОДНЯ.
  
• 
  Щелкните левой кнопкой мыши в строке формул перед последней закрывающей кнопкой и введите с клавиатуры знак «-», а затем щелкните по ячейке С2, а затем наберите на клавиатуре /365 .
  
• 
  Нажмите кнопку ОК.
  

3. Скопируйте формулу из ячейки D2 до ячейки D15.

4. Если значение возраста кратно 5, будем считать возраст юбилейным. Чтобы создать формулу, определяющую юбилейный возраст, воспользуемся функцией ЕСЛИ. Правильно созданная формула должна иметь следующий вид: =ЕСЛИ(ОСТАТ(D2;5)=0;"юбилей";"-") Для этого выполните следующие действия:

• 
  Сделайте активной ячейку Е2.
  
• 
  Вставьте функцию ЕСЛИ.
  
• 
  Значение_если_истина впишите юбилей.
  
• 
  Значение_если_ложь впишите «–» (или поставьте пробел).
  
• 
  В поле Лог_выражение при помощи поля имени вставьте Математическую функцию ОСТАТ. Для поля Число введите D2, Делитель - 5.
  
• 
  Щелкните левой кнопкой мыши в строке формул после OCTAT(D2;5) и допишите =0 .
  
• 
  Нажмите кнопку ОК.
  

---

5. Скопируйте формулу из ячейки Е2 до ячейки Е15.

6 . При помощи Условного форматирования отформатируем ячейки с текстом «юбилей». Для этого выделите ячейки Е2:Е15, затем выполните команду Формат → Условное форматирование. Установите следующие параметры: Затем нажмите на кнопку Формат..., в появившемся окне выполните следующее: на закладке Шрифт установите начертание - полужирный, цвет - белый; на закладке Вид установите цвет заливки - красный. Нажмите ОК.

7. Тем, у кого в этом году юбилей, начисляется премия в размере 50$. Для этого в ячейку F2 вставьте функцию ЕСЛИ. Правильно созданная формула должна иметь следующий вид: =ECЛИ(E2=”юбилeй”;50;”–”).

8. Скопируйте формулу из ячейки F2 до ячейки F15.

9. Примените любой из финансовых долларовых форматов для оформления ячеек F2:F15. Для этого выделите эти ячейки, выполните команду Формат → Ячейки и на закладке Число установите необходимый формат.

10. В ячейке Е17 вычислим количество юбиляров при помощи Статистической функции СЧЕТЕСЛИ. Вставьте эту функцию в ячейку и установите следующие параметры: Диапазон - Е2:Е15, Критерий - юбилей.

11. В ячейке Е18 вычислим количество человек моложе 24 лет при помощи той же функции СЧЕТЕСЛИ. Вставьте эту функцию в ячейку и установите следующие параметры: Диапазон - D2:D15, Критерий - <24.

---

12. В ячейке Е19 самостоятельно вычислите количество человек старше 25 лет.

Задание для самостоятельной работы

1. 
   Заполнили приведенную таблицу:
   

	A	B	C	D	E	F	G	H
1	Фамилия	Имя	Дата рождения	№ группы	Математика	История	Информатика	Средний балл
2	Жукова	Екатерина	16.02.1986	4569	3	2	4
3	Сухов	Андрей	25.10.1987	5433	3	2	4
4	Самойлов	Дмитрий	20.11.1987	4569	5	5	5
5	Данилов	Александр	12.12.1987	5433	5	5	5
6	Валеев	Даниэль	19.02.1988	5433	4	4,4	5
7	Андреева	Юлия	12.04.1988	4785	3	2	5
8	Рахний	Ирина	27.04.1988	5433	4	5	5
9	Стречень	Ирина	26.12.1988	5433	5	4	5
10	Волкова	Анна	17.06.1989	4569	4	4	4

---

- Рассчитайте средний балл студентов за экзамены.

- Создайте поле Возраст (после Даты рождения). Рассчитайте возраст студентов, используя соответствующую функцию.

- Полученный результат представьте в формате Год.

- Определите самого молодого студента с помощью мастера функций, вставив соответствующее поле в таблицу.

- Добавьте к списку с данными о студентах столбец «Стипендия».

- Назначьте дифференцированную стипендию: если средний балл студента равен 5, повышенная стипендия, (50% от 2500 руб.), средний балл от 4 до 5 и все экзамены сданы без троек – стипендия назначается в размере 1000 руб., используя соответствующую функцию (для расчета необходимо посмотреть с помощью Справки логическую функцию И).

---