- •Оглавление
- •Введение
- •Введение в табличный процессорExcel
- •Теоретические сведения
- •Общий вид экрана
- •Создание, открытие и сохранение рабочих книг
- •Листы рабочей книги, ячейка и адрес ячейки
- •Перемещения по экрану
- •Выделение фрагментов таблицы
- •Контроль вводимых данных
- •Работа с формулами
- •Адреса ячеек
- •Распространение формул
- •Визуализация зависимостей и примечания
- •Форматирование таблиц
- •Задание к работе
- •Варианты индивидуальных заданий
- •Контрольные вопросы
- •Графики и диаграммы
- •Теоретические сведения
- •Общие сведения о диаграммах
- •Создание диаграммы
- •Изменение стиля или макета диаграммы
- •Применение готового макета диаграммы
- •Применение готового стиля диаграммы
- •Изменение макетов элементов диаграммы вручную
- •Изменение формата элементов диаграммы вручную
- •Добавление и удаление названий или меток данных
- •Добавление названия диаграммы
- •Добавление названий осей
- •Связывание названия с ячейкой листа
- •Добавление меток данных
- •Удаление названий или меток данных с диаграммы
- •Обновление и изменение данных на диаграмме.
- •Копирование данных листа на диаграмму
- •Изменение данных на существующей диаграмме
- •Изменение диапазона ячеек, на котором основана диаграмма
- •Изменение ряда данных, отображаемого на диаграмме
- •Изменение подписей вдоль горизонтальной оси (оси категорий)
- •Удаление данных с диаграммы Удаление данных источника из листа
- •Удаление ряда данных из диаграммы
- •Создание смешанной диаграммы
- •Задание к работе
- •Контрольные вопросы.
- •ФункцииExcel
- •Теоретические сведения
- •Средства автоматизации ввода данных вExcel2007
- •Автозавершение ввода
- •Автозаполнение смежных ячеек
- •Автозаполнение смежных ячеек числами
- •Заполнение числами с шагом отличным от 1
- •Встроенные функцииExcel
- •Статистический анализ
- •Загрузка пакета статистического анализа и доступ к нему
- •Работа с надстройкой Анализ данных
- •Задание к работе
- •Варианты индивидуальных заданий
- •Средства защиты книг
- •Защита элементов книги Ограничение возможностей изменений в книге
- •Установление доступа к книге по паролю
- •Совместная работа с книгой
- •Просмотр журнала изменений
- •Задание к работе
- •Контрольные вопросы.
- •Фильтры
- •Теоретические сведения
- •Сортировка списков
- •Фильтрация списков
- •Автофильтр
- •Расширенный фильтр
- •Примеры условий отбора расширенного фильтра
- •Виды условий отбора
- •Задание к работе
- •Варианты индивидуальных заданий
- •Контрольные вопросы.
- •Формы и автоматическое подведение итогов
- •Теоретические сведения
- •Промежуточные итоги
- •Задание к работе
- •Варианты индивидуальных заданий
- •Контрольные вопросы.
- •Консолидация данных
- •Теоретические сведения
- •Консолидация данных на основе трехмерных ссылок
- •Консолидация данных по расположению
- •Консолидация данных по категориям
- •Изменение итоговой таблицы консолидации данных
- •Добавление области данных в итоговую таблицу
- •Изменение области данных в итоговой таблице
- •Удаление области данных из итоговой таблицы
- •Задание к работе
- •Варианты индивидуальных заданий
- •Контрольные вопросы.
- •Сводные таблицы
- •Теоретические сведения
- •Создание сводной таблицы
- •Модификация сводной таблицы
- •Задание к работе
- •Варианты индивидуальных заданий
- •Контрольные вопросы.
- •Составление условий в электронных таблицах, условное форматирование
- •Теоретические сведения
- •Условное форматирование
- •Форматирование с использованием гистограммы
- •Форматирование с использованием набора значков
- •Управление правилами условного форматирования
- •Функция если
- •Задание к работе
- •Варианты индивидуальных заданий
- •Текстовые функции, функции даты и времени.
- •Теоретические сведения
- •Задание к работе
Контрольные вопросы.
Для чего предназначен инструмент Консолидация?
Какие методы консолидации существуют? В каких случаях может быть применен каждый из них?
Можно ли консолидировать данные не по всем категориям? Если – да, то что для этого нужно сделать?
Какие действия нужно выполнить, чтобы после консолидации данных при каждом изменении в исходных диапазонах автоматически изменялся и результат?
Как удалить результаты ошибочной консолидации?
Сводные таблицы
Цель работы:изучение средств создания и модификации сводных таблиц.
Теоретические сведения
Сводные таблицы используются для группировки данных по одному или нескольким критериям, при этом производятся различные расчеты по этим данным: суммирование, нахождение среднего и т.д. Можно рассматривать сводную таблицу как средство группирования данных по различным вторичным ключам с последующими расчетами по требуемым параметрам.
Создание сводной таблицы
Сводная таблица создается на базе уже существующей таблицы, путем группировки данных по выбранному параметру и способу вычислений. Для создания Сводной таблицы на вкладке Вставкав группеТаблицынажмите кнопку <Сводная таблица>(Рис. 21).
Рис. 21. Вставка сводных таблиц
Далее в появившемся окне в поле «Выбрать таблицу или диапазон» автоматически выберется вся ваша таблица. Если вы хотите выбрать только часть таблицы для анализа, то нажмите кнопку в конце строки ввода и мышкой выделите нужную часть таблицы. Обратите особое внимание на то, что верхняя строка выделенного диапазона обязательно должна содержать названия столбцов, т.к. она не будет обрабатываться как данные, а определит будущие названия полей по которым будет происходить формирования отчета Сводной таблицы. После выделения нажмите на кнопку в конце строки ввода еще раз.
Укажите, куда следует поместить отчет сводной таблицы: на новый лист или на существующий лист. Далее нажмите <Ok> (Рис. 22). В результате в правой части экрана появится конструктор сводной таблицы – параметры формирования отчета (Рис. 23). В списке полей выберите те, по которым вам нужен отчет, а в нижней части мышкой поместите поля в нужные области в нужном порядке.
Рис. 22. Окно создания сводной таблицы
Рис. 23. Конструктор сводных таблиц
Пример.
Пусть исходный список имеет вид, представленный на Рис. 24.
Рис. 24. Исходные данные для создания сводной таблицы
Сформируем сводную таблицу, определив средний балл за сессию по каждому студенту. Для этого при формировании сводной таблицы переместим в область Строка поле Фамилия, в поле Значения – поле Оценка. При этом предлагается подсчет суммы по этому полю. Для изменения операции щелкаем левой кнопкой мыши в окне Значения по строке «Сумма по полю Оценка». В открывшемся контекстном меню выбираем пункт «Параметры полей значений». В списке «Операция» выбираем «Среднее». В итоге получим таблицу, представленную на Рис. 25.
Рис. 25. Сводная таблица для определения среднего балла студентов
Сформируем сводную таблицу для определения максимального балла по каждой из дисциплин. Для этого в поле Строка поместим поле Дисциплина, в поле Значения – Оценка и изменим операцию на Максимум (Рис. 26).
Рис. 26. Сводная таблица для определения максимального балла по дисциплинам
Сформируем сводную таблицу для определения среднего балла по каждой дисциплине с дополнительной возможностью фильтрации результатов в соответствии со списком студентов. Для этого в поле Фильтр отчета поместим поле Фамилия, в поле Строка – Дисциплина, в поле Значения – Оценка и изменим операцию на Среднее (Рис. 27). Здесь виден фильтр (строка Фамилия), позволяющий просматривать весь список студентов и по каждому видеть результат (очевидно, средний балл для студента по одной дисциплине есть не что иное как оценка по этой дисциплине). Там же есть опция «Все», позволяющая получать агрегированный результат по всей группе студентов. Общий итог показывает средний балл за сессию по одному (на нашем рисунке) или для всех студентов.
Рис. 27. Сводная таблица для определения среднего балла по дисциплинам с применением фильтра
Сформируем сводную таблицу, группирующую студентов по дисциплинам и по каждому студенту выводящую его балл. Для этого в поле Строка поместим сначала поле Дисциплина, затем – Фамилия. В поле Значения поместим поле Оценка, причем операцию можно назначить, например, – Среднее (рис.10). Очевидно, кроме заказанной информации сформированы промежуточные итоги для каждой дисциплины и по всей группе.
Рис. 28. Сводная таблица с группировкой данных
Сформируем сводную таблицу, содержащую в строках информацию о дисциплинах, в столбцах – о фамилиях студентов, а на пересечении строк и столбцов – оценки. Для этого поместим поле Дисциплина в поле Строка, поле Фамилия – в поле Столбец, поле Оценка – в поле Значения (операция - Максимум). Получим результат (рис. 11).
Рис. 29. Сводная таблица с несколькими столбцами
Необходимо помнить, что в вычисляемых полях подведение итогов данных всегда производится по той же функции, которая используется для основных столбцов.