2.1. В диапазоне G4:G8 запишите формулы для вычисления суммарной нагрузки по группам, пользуясь формулой массива.
2.2. В диапазоне В10:F10 запишите формулы для вычисления суммарной нагрузки по видам нагрузки, пользуясь буфером обмена (ввести формулу, вычисляющую суммарную нагрузку по лекциям в ячейку B10, затем воспользоваться командами Главная – Буфер обмена – Копировать и Главная – Буфер обмена – Вставить, предварительно выделив диапазон вставки).
Рис.5.1
2.3. Запишите формулу для суммирования нагрузки по строкам в ячейку G9.
2.4. Запишите формулу для суммирования нагрузки по столбцам в ячейку G10.
2.5. Запишите формулу для вычисления процентного содержания нагрузки для группы ЕС61-63 в общей сумме часов (ячейка H4).
2.6. Скопируйте данную формулу в диапазон H5:H8, пользуясь автозаполнением.
2.8. Запишите формулу для вычисления процентного содержания лекционной нагрузки в общей сумме часов (ячейка В11).
2.9. Заполните аналогичными формулами диапазон C11:F11, пользуясь командой Главная – Редактирование – Заполнить вправо.
3. Пользуясь автовычислением, определите среднее, минимальное и максимальное значения нагрузки для групп ЕС61-63 и СУ61 и зафиксируйте результаты.
4. Активизируйте режим ручного пересчета формул (Office – Параметры Excel).
4.1. Несколько раз измените значения в таблице и выполните ручной пересчет.
5. Отформатируйте таблицу на листе "Таб2" по образцу, представленному на рис.5.2, обратив внимание на центровку строки заголовка и формат процентного представления чисел в ячейках (H4:H8 и В11:F11).
5.1. Заголовки столбцов оформите с использованием непосредственного форматирования.
5.2. Для форматирования ячеек А10:А11 используйте копирование формата, созданного в п.5.1.
5.3. Отформатируйте таблицу на листе "Таб3", пользуясь функцией автоформатирования.
Рис.5.2
6. Пользуясь командой Формулы – Зависимости формул, выявите влияющие и зависимые ячейки для ячейки G9.
7. Пользуясь "объемной" формулой =СУММ(Таб1:Таб3!G9), вычислите сумму значений в клетках G9 трех листов и зафиксируйте полученный результат в клетке G15 листа "Таб1".
8. Пользуясь командой Главная – Буфер обмена – Вставить – Специальная вставка, уменьшите значения в диапазоне B10:F10 в четыре раза.
9. Реализуйте подсчет суммы значений с последовательным накоплением сумм в столбце Накопленные суммы таблицы, приведенной на рис.5.3. Сумма с накоплением для ячейки С2 – это продажи за январь, для С3 – продажи за январь и февраль, для С4 – продажи за январь, февраль и март и т.д. Для осуществления этого алгоритма примените необходимую адресацию в формуле =сумм(В2:В2), помещенной в ячейку С2 указанного столбца и скопируйте ее в остальные ячейки С3:С14.
Рис.5.3
10. Предъявите результаты преподавателю.
Практическая работа №4.
Тема «Функции»
Цель: Знакомство с использованиемфункций табличного процессора MS Excel.
1. Научитесь пользоваться математическими и статистическими функциями.
1.1.Создайте таблицу, приведенную на рис.6.1.
Рис.6.1
1.2. Введите в столбец B функции, указанные в столбце А (столбец А заполнять не надо) и сравните полученные результаты с данными, приведенными в столбце В на рис.6.1.
1.3. Проанализируйте результаты и сохраните созданную таблицу в книге.
2. Научитесь пользоваться логическими функциями.
2.1. Активизируйте второй лист созданной книги.
2.2. Введите таблицу, приведенную на рис.6.2.
2.3. В клетку С2 введите формулу, по которой будет вычислена скидк а и скопируйте ее в диапазон С3:С6:
· если стоимость товара <2000 единиц, то скидка составляет 5% от стоимости товара,
· в противном случае - 10%.
2.4. В клетку D2 введите формулу, определяющую налог и скопируйте ее в диапазон D3:D6:
· если разность между стоимостью и скидкой >5000, то налог составит 5% от этой разности,
· в противном случае - 2%.
Рис.6.2
2.5. Повторите п.2.3 для следующих условий:
· если стоимость товара <2000, то скидка составляет 5% от стоимости товара,
· если стоимость товара >5000, то скидка составляет 15% от стоимости товара,
· в противном случае - 10%.
2.6. В клетку А10 может быть занесена одна из текстовых констант: "желтый", "зеленый", "красный". В клетку А11 введите формулу, которая в зависимости от содержимого клетки А10, будет возвращать значения: "ждите","идите" или "стойте", соответственно.
2.7. Занесите в клетки Е8:E10 три имени: (Лена, Зина, Вера), а в клетки F8:F10 занесите даты их рождений. В клетку E4 введите одно из упомянутых имен.
Пользуясь конструкцией "вложенного" оператора ЕСЛИ, выполните следующие действия:
- проанализировав имя в клетке Е4, запишите в клетку С12 функцию ЕСЛИ, обеспечивающую:
· вывод даты рождения, взятой из соответствующей клетки,
· если же введено неподходящее имя, вывод сообщения: "нет такого имени".
3. Научитесь пользоваться функциями даты и времени, ссылки и массива.
3.1. Активизируйте третий лист книги Имя_6_1.
3.2. Введите в клетку С2 функцию, отображающую сегодняшнюю дату.
3.3. Введите в клетку С3 функцию ДАТА, отображающую произвольно выбранную дату.
3.4. В клетку С5 запишите функцию ВЫБОР, позволяющую вывести название дня недели для даты, введенной в клетку С2 (понедельник, вторник, среда...).
3.5. В клетку С6 запишите аналогичную функцию для даты, введенной в клетку С3.
3.6. Вычислите возраст человека, поместив дату его рождения в клетку С10. Для этого используйте формулу:
= РАЗНДАТ(С10;СЕГОДНЯ();"y")
3.7. Представьте текущее время, используя функции ТДАТА() и СЕГОДНЯ().
3.8. Поместите в соседние ячейки текущую дату и время и дату и время, отстоящую от текущей на трое суток. Найдите количество часов и минут между этими датами, пользуясь форматом [ч]:мм:сс и Общим форматом, а также форматом 13:30. Зафиксируйте результаты и объясните различие.
3.9. Определите номер текущей недели и выведите сообщение:
"Сейчас идет № недели неделя".
3.10. На четвертом листе книги создайте таблицу, приведенную на рис.6.3.
3.10.1. Дайте имена диапазонам клеток, определяющим полученную стипендию за каждый семестр.
3.10.2. В клетку В8 запишите функцию, дающую ответ на вопрос: "Какую стипендию в n -м семестре получил m -й студент?" Значения n -го семестра и фамилия m -го студента должны быть введены в клетки А8 и А9. Для решения поставленной задачи используйте функции ПРОСМОТР и ВЫБОР.
Рис.6.3
4. Научитесь пользоваться статистическими функциями
РАНГ и ПРЕДСКАЗАНИЕ.
4.1. На пятом листе книги создайте таблицу, приведенную на рис.6.4.
4.2. Используя функцию РАНГ, определите ранги цехов в зависимости от объема продаж по каждому году и поместите результаты в соответствующие клетки таблицы. В ячейки J3:J7 запишите формулы для вычисления средних значений рангов цехов.
4.3. Пользуясь информацией об объемах продаж, спрогнозируйте объемы продаж для каждого цеха в 1999 году, пользуясь функцией ПРЕДСКАЗАНИЕ.
Рис.6.4
