Создание стипендиальной ведомости
Дана таблица с итогами экзаменационной сессии на группу из 10 студентов с различной успеваемостью.
Итоги экзаменационной сессии
| № п/п | Ф. И.О. | Математика | Экономическая теория | Правоведение | Информатика |
| 1 | Макаров С.П. | 5 | 4 | 3 | зачтено |
| 2 | Антонов А.Г. | 4 | 3 | 3 | не зачтено |
| 3 | Троян С.П. | 2 | 3 | 3 | зачтено |
| 4 | Арбузов Г.П. | 5 | 5 | 5 | зачтено |
| 5 | Стоянов А.Л. | 5 | 4 | 5 | зачтено |
| 6 | Абдулов М.Н. | 2 | 3 | 3 | не зачтено |
| 7 | Чернов Е.А. | 3 | 4 | 3 | зачтено |
| 8 | Каюков Н.Ф. | 4 | 3 | 4 | зачтено |
| 9 | Петров И.А. | 5 | 3 | 4 | зачтено |
| 10 | Леонов Л.П. | 4 | 4 | 4 | зачтено |
1.Составить электронную таблицу, определяющую стипендию по следующему правилу: если студент по всем дисциплинам получил положительные оценки и «зачтено», то ему начисляется стипендия. В зависимости от величины среднего балла за экзаменационную сессию (s) базовая стипендия (2000 р.) может быть повышена. Повышающий коэффициент (k) рассчитывается по следующему правилу:
если 4 £s<4,6, то k=1,5,
если 4,6£s£5, то k=2,0.
Если не сдан какой-либо предмет или имеется «незачёт», то стипендия не начисляется и в этом случае в ячейке должен появиться текст «отчислить».
Выполнение.
1. Скопировать исходную таблицу и добавить необходимые для выполнения заданий строки и столбцы.
|
|
|
Средний балл и стипендию рассчитать по соответствующим формулам с использованием функций=СРЗНАЧ(), ЕСЛИ. Функции можно найти во вкладке «Мастер функций»
Подробно пример выполнения подобного задания можно посмотретьздесь.
3. По результатам сдачи сессии группой студентов определить:
– общий стипендиальный фонд (с помощью кнопкиАвтосумма);
– количество неуспевающих по «Математике»(имеющих 2 балла) с использованием функции СЧЁТЕСЛИ(диапазон;"*"), которая подсчитывает количество соответствующих заданному условию ячеек и относится к статистическим функциям;
– количество студентов, которые поданы на отчисление (с помощью функции СЧЁТЕСЛИ();
–количество студентов, фамилия которых начинается на букву «А» (с помощью функции СЧЁТЕСЛИ(диапазон;"А*")).
Если условий несколько, можно воспользоваться функцией СЧЁТЕСЛИМН().
Определить:
– количество студентов, имеющих «5» по «Математике» и «Экономической теории» с использованием функции СЧЁТЕСЛИМН().
–самый «сложный» предмет. Самым сложным предметом будем считать тот, по которому средний балл, полученный студентами,является минимальным. Минимальное число из нескольких можно определить с помощью функции МИН().Вданном случае эту функцию надо применить к диапазону данных, в котором ранее подсчитаны средние баллы по предметам;
|
|
|
– определить фамилию студента, с наивысшим средним баллом с использованием функции МАКС().
Подробно пример выполнения похожего задания можно посмотреть здесь.
– выделить цветом ячейки, содержащие «отчислить». Для этого нужно выделить таблицу (или нужный диапазон), нажать кнопку «Условное форматирование».В появившемся окне выбрать «Создать правило».
Далее выбрать «Форматировать только ячейки, которые содержат» и задать параметры форматирования.
– для большей иллюстративности таблицы с помощью «Условного форматирования» выделить различными цветами значения среднего балла студентов (использовать «Цветовые шкалы»).
Лабораторная работа 4.
I.Консолидация данных
Консолидация – обобщение данных, находящихся на разных рабочих листах (книгах), с помощью функций: среднее, максимум, минимум, сумма и другие. В некоторых случаях консолидация может включать в себя создание связанных формул.
В зависимости от размещения информации в рабочих таблицах выделяют различные способы консолидации данных. Если размещение информации во всех таблицах одинаково, то говорят о консолидации по позиции.
|
|
|
В том случае, когда размещение информации не идентично, но достаточно похоже, можно объединить данные по заголовкам строк и/или столбцов. Такая консолидация называется консолидацией по категориям.
Если же рабочие таблицы имеют мало общего друг с другом, то необходимо перед консолидацией отредактировать листы, чтобы они стали единообразными.
Задание.
Пусть на разных листах книги представлены отчеты о продаже печатных изданий за три месяца различными книжными магазинами.
Магазин № 1
| Название товара | Январь | Февраль | Март |
| Правда | 110 | 10 | 20 |
| Знание | 10 | 10 | 20 |
| Политика | 20 | 20 | 40 |
| Знание | 30 | 30 | 60 |
| Финансы | 40 | 40 | 80 |
| Досуг и развлечения | 60 | 60 | 120 |
| Автомобилист | 50 | 50 | 100 |
| Наука и жизнь | 70 | 70 | 140 |
| Студенчество | 120 | 20 | 20 |
| Здоровье | 11 | 11 | 24 |
Магазин № 2
| Название товара | Январь | Февраль | Март |
| Студенчество | 10 | 10 | 20 |
| Знание | 10 | 10 | 20 |
| Наука и жизнь | 20 | 20 | 20 |
| Здоровье | 20 | 40 | 40 |
| Знание | 30 | 30 | 60 |
| Финансы | 40 | 40 | 80 |
| Досуг и развлечения | 60 | 60 | 120 |
| Автомобилист | 50 | 20 | 100 |
| Наука и жизнь | 70 | 70 | 140 |
| Студенчество | 40 | 40 | 45 |
| Спорт | 30 | 20 | 45 |
Магазин № 3
|
|
|
| Название товара | Январь | Февраль | Март |
| Правда | 10 | 10 | 20 |
| Знание | 10 | 10 | 20 |
| Политика | 20 | 20 | 40 |
| Наука и жизнь | 100 | 100 | 100 |
| Знание | 30 | 30 | 60 |
| Финансы | 40 | 40 | 80 |
| Досуг и развлечения | 60 | 60 | 120 |
| Автомобилист | 50 | 50 | 100 |
| Наука и жизнь | 70 | 70 | 140 |
| Спорт | 10 | 10 | 10 |
Нужно 1) найти суммарное количество товаров, проданных в январе и феврале;
2) определить максимальные объёмы продаж по каждому товару в каждом месяце.
Списки включенных в них товаров, а также порядок перечисления в них различны. Другими словами, способ размещения информации в этих рабочих таблицах не одинаков. Поэтому для получения итоговых данных о продаже изделий фирмой по месяцам, необходимо выполнить консолидацию по категориям.
Выполнение.
1. Создайте рабочие таблицы на различных листах рабочей книги (например, на листах с первого по третий).
Выберите новый рабочий лист, где должны размещаться результаты консолидации. Выполните команду Данные / Консолидация.
2. Задание параметров для диалогового окна «Консолидация»
В поле «Функция» укажите функцию Сумма, которая показывает тип объединения данных.
В поле «Ссылка» введите ссылку на диапазон первой рабочей таблицы, которые должны быть консолидированы. Когда в поле «Ссылка» будет введена нужная ссылка, щелкните по кнопке «Добавить», чтобы добавить ее к списку диапазонов.
Введите ссылку на диапазон второй рабочей таблицы и добавьте ее к списку диапазонов. Выполните указанное действие для остальных диапазонов консолидации.
Так как способы размещения информации в рабочих таблицах различны, установим опции Подписи верхней строки и Значения левого столбца. В результате Excel будет подбирать данные по заголовкам.
Для того, чтобы консолидация была динамической, установите опцию Создавать связи с исходными данными и нажмите кнопку «ОК». В результате Excel создаст структуру, содержащую внешние ссылки.
3. Аналогичные действия проделайте для определения максимальных объёмов продаж по каждому товару в каждом месяце.
II. Сводные таблицы
Сводными называются таблицы, содержащие часть данных анализируемой таблицы, показанные так, чтобы связи между ними отображались наглядно.
Сводные таблицы используются для обобщения больших массивов подробной информации и подведения различных итогов: суммирования по отдельным группам, вычисления среднего и процентного значения по отдельным группам, подведения промежуточных и общих итогов и так далее. Сводные таблицы предназначены для удобного просмотра данных больших таблиц, т.к. обычными средствами делать это неудобно, а порой, практически невозможно.
Подробнее о создании сводных таблиц можно посмотреть по ссылке:
http://office-guru.ru/excel/rabota-so-svodnymi-tablicami-v-microsoft-excel-112.html
Задание.
Из данной таблицы выбрать сведения, содержащие информацию о том, каковы затраты на рекламу на ТВ и в Интернете каждого застройщика по месяцам.
| Затраты на рекламу крупных застройщиков СПб в 2015 (тыс. руб) | ||||||||
| Застройщик | Месяц | Реклама в печатных СМИ | Реклама на радио | Реклама на ТВ | Реклама в метро | Промо акции | Реклама в Интернете | Наружная реклама |
| SetlCity | январь | 550 | 40 | 450 | 200 | 100 | 200 | 60 |
| ЛСР.Недвижимость | январь | 300 | 50 | 350 | 500 | 150 | 260 | 70 |
| ЦДС | январь | 600 | 80 | 550 | 400 | 70 | 330 | 85 |
| Лидер Групп | январь | 300 | 50 | 300 | 400 | 50 | 400 | 50 |
| ЛенСпецСМУ | январь | 450 | 30 | 250 | 300 | 0 | 0 | 36 |
| ЮИТ ДОМ | январь | 300 | 20 | 100 | 150 | 20 | 20 | 12 |
| Normann | январь | 350 | 25 | 150 | 170 | 0 | 80 | 55 |
| Полис групп | январь | 400 | 0 | 200 | 200 | 80 | 250 | 35 |
| Северный город | январь | 100 | 20 | 50 | 50 | 0 | 60 | 47 |
| SetlCity | февраль | 550 | 40 | 450 | 200 | 100 | 200 | 0 |
| ЛСР.Недвижимость | февраль | 300 | 50 | 350 | 350 | 150 | 260 | 70 |
| ЦДС | февраль | 450 | 80 | 350 | 450 | 0 | 30 | 35 |
| Лидер Групп | февраль | 300 | 50 | 300 | 500 | 200 | 400 | 50 |
| ЛенСпецСМУ | февраль | 250 | 30 | 250 | 200 | 0 | 100 | 6 |
| ЮИТ ДОМ | февраль | 300 | 20 | 100 | 150 | 10 | 20 | 10 |
| Normann | февраль | 250 | 0 | 150 | 170 | 0 | 80 | 55 |
| Полис групп | февраль | 100 | 40 | 200 | 200 | 80 | 250 | 35 |
| Северный город | февраль | 100 | 20 | 50 | 50 | 30 | 50 | 47 |
| SetlCity | март | 550 | 40 | 250 | 200 | 100 | 200 | 60 |
| ЛСР.Недвижимость | март | 300 | 50 | 350 | 400 | 150 | 260 | 70 |
| ЦДС | март | 300 | 80 | 350 | 300 | 70 | 230 | 85 |
| Лидер Групп | март | 300 | 50 | 300 | 500 | 30 | 20 | 50 |
| ЛенСпецСМУ | март | 250 | 30 | 250 | 300 | 10 | 100 | 0 |
| ЮИТ ДОМ | март | 300 | 0 | 100 | 150 | 50 | 20 | 12 |
| Normann | март | 350 | 25 | 150 | 170 | 0 | 80 | 55 |
| Полис групп | март | 300 | 40 | 200 | 200 | 80 | 250 | 35 |
| Северный город | март | 100 | 20 | 50 | 50 | 0 | 60 | 47 |
Выполнение:
1. Скопируйте таблицу в Excel. Верхняя строка обязательно должна содержать заголовки столбцов.
2. Откройте вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица». Если вместе со сводной таблицей нужно создать и сводную диаграмму – нажмите на стрелку в нижнем правом углу значка «Сводная таблица» и выберите пункт «Сводная диаграмма».
3. В открывшемся диалоговом окне «Создание сводной таблицы» выберите только что созданную таблицу с данными или ее диапазон. Для этого выделите нужную область.
4. Далее нужно будет указать, где размещать отчет сводной таблицы. Удобнее всего это делать на новом листе.
5. После подтверждения действия нажатием кнопки «ОК», будет создан и открыт макет отчета. Рассмотрим его.
В правой половине окна создается панель основных инструментов управления – «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты (Застройщик, Месяц, Реклама на ТВ, Реклама в Интернете), и отчет сводной таблицы с выбранными полями будет создан.
Установите денежный формат записи чисел. Для этого найдите в окне «Значения» поля «Сумма по полю Реклама в Интернете» и «Сумма по полю Реклама на ТВ», кликните на них и вызовите меню, в котором выберите «Параметры полей значений». Далее – «Числовой формат», в котором задайте нужный формат.
6. Настройте формат для всей сводной таблицы. Для этого в «Конструкторе» разверните меню нажатием на стрелочку в нижнем правом углу раздела «Стили сводной таблицы», чтобы увидеть обширную коллекцию встроенных стилей, из которых выберите удобный для просмотра данной таблицы шаблон формата.
7. Поднимите на первую позицию поле «Месяц» в окне «Названия строк» и посмотрите, как изменилась структура отчёта.
8. Осуществите фильтрацию найденной информации с целью определения величины затрат на рекламу по заданному месяцу (например, по январю). Для этого перетащите поле «Месяц» в окно «Фильтр отчёта». Затем задайте «Январь» в соответствующем поле отчёта.
9. Определите затраты на рекламу в печатных СМИ и на радио компании «SetlCity»в зимние месяцы помесячно и суммарно.Эту информацию представьте на новом листе в виде такой таблицы:
Дата добавления: 2018-06-01; просмотров: 998; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!