Microsoft Excel может автоматически обобщать данные, вычисляя промежуточные и общие итоги в списке. Для использования автоматических промежуточных итогов список должен содержать столбцы с подписями и должен быть отсортирован по столбцам, для которых требуются промежуточные итоги.
При вставке автоматических промежуточных итогов Excel изменяет разметку списка, группируя строки данных с каждой связанной строкой промежуточных итогов, а строки промежуточных итогов — со строкой общих итогов.
Существует возможность выбора функции, используемой в Excel для вычисления итогов. В данном примере для вычисления промежуточных итогов и общих итогов по всему листу используется функция СУММ.
Для создания промежуточных итогов необходимо:
1. Выделить ячейку списка данных.
2. Из меню Данные выбрать команду Итоги.
3. В диалоговом окне Про межуточные итоги в списке При каждом изменении в выбрать колонку таблицы, содержащую отсортированные данные. При изменении значения в данной колонке будут подводиться итоги.
4. В списке Операция выбрать операцию, выполняемую над данными.
5. В списке Добавить итоги по выбрать данные, которые должны участвовать в расчетах, установив флажки требуемых столбцов данных.
6. Установить требуемые флажки:
• в поле Заменить текущие итоги, чтобы заменить все старые промежуточные итоги на вновь создаваемые;
• в поле Конец страницы между группами, чтобы печать одной группы осуществлялась на отдельной странице;
• в поле Итоги под данными, чтобы размещать итоговые данные под детальными данными.
Для удаления промежуточных итогов необходимо из меню Данные выбрать команду Итоги, а затем в диалоговом окне Промежуточные итоги нажать кнопку Убрать все.
СВОДНАЯ ТАБЛИЦА
Сводная таблица – это средство, упорядочивающее информацию. Распределение информации в сводной таблице задается пользователем, указывая, какие поля и элементы должны в ней содержаться. Поле – это некоторая общая категория, а элемент – отдельное значение внутри некоторой категории значений. Непосредственно в сводной таблице нельзя изменять и вводить в нее данные, можно только изменять форматирование и выбирать различные параметры вычислений. Сводные таблицы создаются с помощью Мастера сводных таблиц.
Для того чтобы создать сводную таблицу, необходимо:
1) из меню Данные выбрать команду Сводная таблица;
2) установить переключатель В списке или базе данных Microsoft Excel, затем нажать кнопку Далее;
3) выделить диапазон данных, по которым будет строиться сводная таблица, и нажать кнопку Далее.
Задания 1
Основные приемы создания и форматирования рабочих листов Excel
1. Создайте таблицу следующего вида.
2. Заполните в таблице 5 строк произвольными данными (столбцы Фамилия, Адрес, Дата рождения, Пол, Стаж, Оценки).
3. Отформатируйте заголовок таблицы следующим образом: разместить данные по центру столбца; установить полужирный шрифт; установить голубой цвет заливки для заголовка таблицы.
4. В столбце Сумма баллов выведите формулу для подсчета суммарного балла по каждому абитуриенту. Для создания формулы используйте кнопку Автосумма на панели инструментов.
18
5. В столбце Средний балл рассчитайте средние баллы функцией СРЗНАЧ из категории Статистические.
6. В столбце Результат выведите информацию следующим образом: если абитуриент набрал нужное количество баллов (сумма баллов больше или равна 16), то вывести Поступил; если абитуриент не набрал проходного балла, то вывести Не поступил. Использоватьфункцию ЕСЛИ из категории Логические.
Таблица 2.1
Фамилия
Адрес
Дата
Рождения
Пол
Стаж
Оценки
Сумма баллов
Средний балл
Результат
Физика
математика
Жуков О.Р.
Гродно
12.09.1990
муж
4
9
10
19
9,5
Поступил
7. Выведите под таблицей итоговые значения.
Функции для вычислений указаны в скобках, они находятся в категории Статистические (кроме СУММЕСЛИ).
§ Всего в списке (СЧЕТЗ)
§ Поступили (СЧЕТЕСЛИ)
§ Не поступили (СЧЕТЕСЛИ)
§ Средняя сумма баллов (СРЗНАЧ)
§ Средний балл (СРЗНАЧ)
§ Максимальная сумма баллов (МАКС)
§ Минимальная сумма баллов (МИН)
§ Кол-во «8» по математике (СЧЕТЕСЛИ)
§ Имеют стаж (СЧЕТЕСЛИ)
§ Средний балл для абитуриентов со стажем (составить формулу из функций: СУММЕСЛИ из категории Математические и СЧЕТЕСЛИ)
§ Из Витебска (СЧЕТЕСЛИ)
8. Отсортируйте таблицу по фамилиям в алфавитном порядке (Данные – Сортировка).
9. Постройте диаграмму на отдельном листе по данным столбца Сумма баллов.
Варианты индивидуальных заданий
Для выполнения заданий используются данные таблицы 1.
1. Вывести результат сравнения количества оценок выше 5 по математике и физике:
оценок выше 5 больше по физике;
оценок выше 5 больше по математике;
оценок выше 5 равно по математике и физике.
Результат должна выводить одна формула с вложенными функциями.
2. Рассчитать средний балл по математике для тех, кто поступил. Результат должна выводить одна формула с вложенными функциями.
3. Подсчитать количество иногородних абитуриентов (не из Витебска) и их средний балл.
4. Рассчитать средний балл по физике для тех, кто поступил. Результат должна выводить одна формула с вложенными функциями.
5. Используя данные табл.1, рассчитать, сколько абитуриентов:
со средним баллом < 4;
со средним баллом от 4 до 7;
со средним баллом >= 7.
6. Дополнить таблицу 1 столбцом ФОРМА ОБУЧЕНИЯ и поместить один вариант ответа:
НЕ ПОСТУПИЛ, если сумма баллов < 16
ПЛАТНАЯ – сумма баллов >= 16 и <18;
БЮДЖЕТНАЯ – сумма баллов >= 18.
7. Вывести результат сравнения количества абитуриентов:
больше абитуриентов женского пола;
больше абитуриентов мужского пола;
количество абитуриентов женского и мужского пола равно.
Результат должна выводить одна формула с вложенными функциями.
8. Используя данные табл. 1, рассчитать, сколько абитуриентов с датой рождения:
до 1992 г.,
в 1992 г.;
после 1992 г.
9. Сколько студентов получили оценки по математике:
< 4;
от 4 до 7;
>= 7.
10. Вывести текстовые результаты сравнения средних, максимальных и минимальных баллов по физике и математике:
средний балл выше по математике;
средний балл выше по физике;
средние баллы по математике и физике равны.
Результат должна выводить одна формула с вложенными функциями.
11. Дополнить таблицу 1 столбцом ОЦЕНКА(ПО СРЕДНЕМУ) и поместить один вариант ответа:
ВЫШЕ, если ср. балл выше среднего по группе;
РАВЕН, если ср. балл равен;
НИЖЕ, если ср. балл ниже.
12. Вывести результат сравнения количества оценок ниже 4 по математике и физике:
оценок ниже 4 больше по физике;
оценок ниже 4 больше по математике;
оценок ниже 4 по физике и математике равно.
Результат должна выводить одна формула с вложенными функциями.
13. Вывести результат сравнения количества средних баллов по группе выше среднего и ниже среднего:
кол-во средних баллов выше среднего больше;
кол-во средних баллов ниже среднего больше;
кол-во средних баллов ниже и выше среднего равны.
Результат должна выводить одна формула с вложенными функциями.
14. Вывести результат сравнения количества оценок ниже 4 по математике и физике:
оценок ниже 4 больше по физике;
оценок ниже 4 больше по математике;
оценок ниже 4 по математике и физике равно.
Результат должна выводить одна формула с вложенными функциями.
Таблицу дополнить столбцом ПОСТУПИЛ СО СТАЖЕМ и заполнить текстом ДА, если абитуриент поступил и у него есть стаж. При других условиях столбец не заполнять. Подсчитать по данному столбцу количество студентов со стажем.
