Промежуточные итоги
Прежде чем подводить прмежуточные итого надо отсортировать записи по тем параметрам, по которым будут подводиться итоги.
Для поведения простых промежуточных итогов надо сделать следующее:
Выделить диапазон, подлежащий обработке.
Выполнить команду Данные| Структура| Промежуточный итог.
В открывшемся диалоговом окне Промежуточные итоги
Установить в качестве значения поля При каждом изменении в названии нужного столбца таблицы;
В поле Операция задать производимую операцию;
В поле Добавить итоги по установите флажки против названий тех столбцов таблицы, к которым будет применяться операция, заданная в поле Операция.
Установить флажок Заменить текущие итоги.
При необходимости установить флажок Конец страницы между группами − если требуется, чтобы конец страницы (при печати) не разрывал данные по одной группе.
Установите флажок Итоги под данными − в этом случае окончательные итоги будут выведены в нижней части таблицы
Нажать ОК.
Например, найдем средний бал в каждой группе групп.
Окончательно, будем иметь:
Применение функций базы данных
В Excel существует ряд функций базы данных, которые можно использовать для подсчета статистических значений. Список функций и их синтаксис можно найти в Мастере функций, для доступа к которому нужно выполнить: Формулы| Вставить функцию.
Функции базы данных независимо от различий в названиях и выполняемых вычислений, используют аргументы
имя_функции(база_данных, поле, критерий).
Аргументы функции базы данных предоставляют следующую информацию:
база_данных − это аргумент, который определяет диапазон, содержащий таблицу и обязательно включающий верхнюю строку с названием полей;
поле − аргумент, определяющий поле, значения в котором будут подсчитываться с помощью функции базы данных;
критерий − аргумент, определяющий адрес диапазона с критерием, который используется при определении подсчитываемых значений. В этот диапазон должно быть включено хотя бы одно название поля, которое задает поле, значения в котором будут оцениваться, и одна ячейка со значениями или выражением, используемым при оценке.
Например, имеется таблица, содержащая данные о количестве деталей.
Используя функцию БДСУММ найдем суммы качественных и бракованных деталей каждого вида для цеха № 1. Предварительно на рабочем листе создадим критерии:
Затем, заполняем таблицу для цеха № 1.
Сводная таблица
Сводные таблицы являются одним из наиболее мощных средств MSExcel по анализу баз данных, помещенных в таблицыили списки. Сводная таблица не просто группирует и обобщает данные, но и дает возможность провести глубокий анализ имеющейся информации.
При создании сводной таблицы пользователь распределяет информацию, указывая, какие элементы и в каких полях сводной таблицы будет содержаться. Поле − это некоторая совокупность данных, собранных по одному признаку. Элемент − отдельное значение, содержащееся в поле. Обычные таблицы могут быть только двумерными. Сводная таблица является многомерной. Её можно сравнить со стопкой листов: виден только верхний лист, но в любой момент можно вытащить наверх любой необходимый. Сводная таблица всегда связана с источником данных. Сама она предназначена только для чтения, а изменения нужно вносить в исходные таблицы.
Опишем пошаговый процесс создания сводной таблицы на примере таблицы, содержащей данные о качестве деталей. В данном случае назначение сводной таблицы − подвести итоги по типам (болты, винты, гайки), качеству деталей и по цехам.
Сводные таблицы создаются одним из следующих способов:
с помощью средства Мастер сводных таблиц и диаграмм;
командой на ленте Вставка| Таблицы| Сводная таблица| сводная таблица.
Использование команды Мастер сводных таблиц и диаграмм более удобно и предоставляет больше возможностей. Сначала поместим мастер сводных таблиц и диаграмм на Панель быстрого доступа. Для этого надо
Перейти на вкладку Файл;
Открыть Параметры;
Выбрать Настройки;
В раскрывшемся списке Выбрать команды из выберети пункт Все команды, и в нем команду Мастер сводных таблиц и диаграмм;
Нажать кнопку Добавить
Нажать ОК.
Для того чтобы создать сводную таблицу:
На панели быстрого доступа в поле Вставка щелкнуть левой кнопкой мыши по значку Мастер сводных таблици диаграмм. На экране появится окно Мастер сводных таблици диаграмм - шаг 1 из 3. Установите переключатели так, как это показано на рисунке и нажмите кнопку Далее.
На втором шаге выбираем диапазон данных на основе которых будет создана сводная таблица.
3.На третьем шаге выбираем расположение таблицы на новый лист
После выполнения описанных действий на рабочем листе появится пустой макет новой сводной таблицы.
Замечание 1. Для создания сводной таблицы средствами ленты выполняем команды: Вставка| Таблицы| Сводная таблица| Сводная таблица. В открывшемся окне Создание сводной таблицы указываем диапозон и затем нажимаем ОК.
Замечание 2. Если структура сводной таблицы не отобразится, то следует выполнить команды: Работа со сводными таблицами| Параметры| Сводная таблица| Параметры. В диалоговом окне Параметры сводной таблицы перейти на вкладку Вывод и установить флажок Классический макет сводной таблицы (разрешено перетаскивание полей).
Далее переходим к заполению полей сводной таблицы. Для настройки структуры сводной таблицы в нашем примере нужно сделать следующее:
Перетащить поле Цех в область Названия строк. Тем самым задаем групировку по цехам.
Перетащить поле Квартал в область Названия столбцов (задаем группировку по кварталам);
Перетащить поле Деталь в область Фильтр отчета. В результате сводная таблица будет либо представлять данные по отдельной детали (выбранной в списке), либо сразу по всем деталям, если выбран вариант Все.
Перетащить поля Качественные, Брак и Всего в область ∑ Значения.
Полученный результат показан на рисунке.
При создании сводной таблицы в графе Общий итог подсчитывается сумма значений из поля данных.
Чтобы изменить название поля или элемента, выделяем необходимую ячейку и набираем новое имя.
Чтобы удалить поле строки, столбца или страницы, надо перетащить удаляемое поле за пределы сводной таблицы.
Если в исходную таблицу были внесены какие либо изменения, то результаты в сводной таблице не изменятся до тех пор, пока не будет произведено обновление. Для обнавления сводной таблицы нажимаем кнопку Обновить в группе инструментов Данные на вкладке Параметры. При обнавлении таблицы учитываются:
изменения данных в поле данных;
появление новых или изменение существующих элементов;
удаление полей данных.