Подведении итогов в базе данных

 

Один из способов обработки и анализа базы данных состоит в подведении различных итогов. С помощью команды Данные | Итоги можно вставить строки итогов в список, осуществив суммирование данные нужным способом. При вставке строк итогов Excel автоматически помещает в конец списка данных строку общих итогов.

В качестве примера допустим, что список данных содержит десятки записей о продаже товара, организованных в столбцы с метками по дате продажи, по покупателям, по видам товара (рисунок 8).

После выполнения команды Данные | Итоги вы можете выполнить следующие операции:

выбрать функцию для подведения итогов

выбрать данные, по которым нужно подвести итоги

Кроме подведения итогов по одному столбцу, автоматическое подведение итогов позволяет:

выводить одну строку итогов по нескольким столбцам

выводить многоуровневые, вложенные строки итогов по нескольким столбцам

выводить многоуровневые строки итогов с различными способами вычисления для каждой строки

скрывать или показывать детальные данные в этом списке

Перед тем, как применять автоматическое подведение итогов, отсортируйте список. Впервой строке списка должны находиться названия столбцов.

После выполнения команды Данные|Итоги появляется окно диалога “Промежуточные итоги” (рисунок 9).

В поле ввода При каждом изменении в определяется столбец, который содержит группы, для которых нужно вычислять промежуточные итоги. Нажмите мышью на раскрывающемся списке и выделите нужный столбец для сортировки списка.

В раскрывающемся списке Операция выберите функцию, которая вычисляет итоги. По умолчанию Excel использует функцию Сумма для числовых значений или функцию Счет для текстовых значений. Кроме того, вы можете использовать следующие функции: Среднее, Максимум, Минимум, Произведение, Кол-во чисел, Несмещенное отклонение, Смещенное отклонение, Несмещенная дисперсия, Смещенная дисперсия. Назначение этих функций приведено в таблице 1.

Вычисленные итоги располагаются под столбцом, который выделяется в поле ввода Добавить итоги по. Вы можете выделить более одного столбца. Если столбец выделен, то перед его названием появляется крестик.

Если установлен флажок Заменить текущие итоги, то все итоги в списке заменяются новыми итогами, выбранными вами. Снимите этот флажок для сохранения текущих итогов и добавления новых итогов.

Флажок Конец страницы между группами автоматически вставляет конец страницы перед каждой группой данных, для которой вычисляются итоги.

Если установлен флажок Итоги под данными, то строки итогов и общих итогов помещаются под соответствующими данными. Снимите этот флажок, если нужно вставить строки итогов и общих итогов над данными.

При нажатии на кнопку Убрать все удаляются все итоги из текущего списка.

 

 

Рисунок 8 - Список данных

 

 

 

Рисунок 9 - Окно диалога “Промежуточные итоги”

 

Пусть при помощи команды Итоги вам предстоит вычислить, сколько денег потрачено на покупки товаров каждым покупателем.

Перед тем как подводить итоги, отсортируем нашу таблицу по полю “Код покупателя”. Для этого выполните команду Данные | Сортировать. Появится окно диалога “Сортировка диапазона”.

В раскрывающемся списке поля ввода Сортировать по выберите поле “Код покупателя” и нажмите кнопку OK.

Для подведения итогов по затратам каждого покупателя выполните следующие действия:

Выполните команду Данные | Итоги. Появится окно диалога “Промежуточные итоги”.

В поле При каждом изменении в нажмите мышью на кнопке со стрелкой. Появится список полей базы данных. Выберите поле “Код покупателя”.

В поле Операция нажмите мышью на кнопке со стрелкой. Появится список функций, которые могут обрабатывать ваши данные. Выберите функцию Сумма.

В поле Добавить итоги по нажмите мышью на поле “Выручка”.

Установите флажки Заменить текущие итоги и Итоги под данными. Окно диалога “Промежуточные итоги” заполнено. Нажмите кнопку OK.

Таблица 1 - Назначение итоговых функций

 

Функция	Значение в строке итогов
Сумма	Сумма чисел. Эта операция используется по умолчанию для подведения итогов по числовым полям
Среднее	Среднее чисел
Максимум	Наибольшее числовое значение
Минимум	Наименьшее числовое значение
Произведение	Произведение всех чисел в группе
Кол-во чисел	Количество записей или строк, которые содержат числовые данные в группе
Кол-во значений	Количество записей или строк данных. Эта операция используется по умолчанию для подведения итогов по нечисловым полям
Несмещенное отклонение	Несмещенная оценка среднего квадратичного отклонения генеральной совокупности по выборке данных
Смещенное отклонение	Смещенная оценка среднего квадратичного отклонения генеральной совокупности по выборке данных
Несмещенная дисперсия	Несмещенная оценка дисперсии генеральной совокупности по выборке данных
Смещенная дисперсия	Смещенная оценка дисперсии генеральной совокупности по выборке данных

 

 

Итоги подведены и результат представлен на рисунке 10. Для каждого покупателя подсчитано, сколько он потратил денег на покупки.

Обратите внимание на маленькие кнопки со знаками “+” и “-”, расположенные слева от таблицы.

Символ показа деталей (кнопка со знаком “+”) указывает итоговую строку или столбец со скрытыми деталями. Для показа деталей нажмите мышью на эту кнопку.

Символ скрытия деталей (кнопка со знаком “-”) указывает итоговую строку или столбец с выведенными детальными строками или столбцами. Нажмите мышью на эту кнопку для скрытия детальных данных, указываемых полосой уровня столбца или строки.

Символы уровня строки и столбца (кнопки с изображением цифр “1”, “2”, “3”) указывают число уровней строк и столбцов в структуре документа. Нажмите мышью на символ для вывода соответствующих уровней данных в структуре документа. Полосы уровня строки и столбца указывают, что выведены детальные строки и столбцы. Нажмите мышью на символе скрытия деталей или на полосе уровня, чтобы скрыть детальные строки или столбцы.

Нажмите мышью на изображении кнопки со знаком “+” напротив числа 11 в поле “Код покупателя”. Раскроется информация, относящаяся к покупателю с кодом 11. Чтобы свернуть информацию, нажмите мышью на кнопке со знаком “-”.

 

Задания к лабораторной работе.

Подготовьте таблицу для ввода в среде Excel согласно варианту задания. В таблице должно быть порядка 10-15 строк. Варианты задания представлены в таблице 2.

Используя команду “Форма” введите данные втаблицу.

Используя команду “Критерий” определите простой и сложный критерий для поиска по одному из полей.

Упорядочите информацию в базе данных по одному из полей.

Воспользуйтесь командой “Автофильтр” для выбора записей по какому-либо критерию.

Подведите итоги базы данных.

Содержание отчета

Постановка задачи, справочник базовых команд Excel, используемых при работе с таблицей как базой данных, результаты выполнения, выводы.

Контрольные вопросы.

Что такое “База данных” с точки зрения Excel?

Какие средства существуют в Excel для ввода данных в базу?

Как осуществлять поиск записи по критерию?

Как выполнять сортировку таблицы по одному из полей?

Какие возможности предоставляет команда «Автофильтр»?

Как подвести итоги в базе данных?

 

 

Рисунок 10 - Результат выполнения команды “Итоги”

 

Таблица 2 - Варианты заданий на лабораторную работу

№ варианта	Поля базы данных
	отдел предприятия; Ф.И.О. работника; зар. плата; налог (20% от зар. платы); сумма к выдаче
	Ф.И.О. преподавателя, наименование учебной дисциплины, количество часов в неделю лекционной нагрузки, количество часов в неделю лабораторных работ, общий объем нагрузки преподавателя в семестре (15 недель)
	шифр специальности, Ф.И.О. студента, стипендия, наличие надбавки (1 – есть надбавка; 0 – нет), сумма к выдаче
	Марка автомобиля, номер автомобиля, стоимость 1 км. пути, пробег в км, стоимость пробега.
	Автор, Наименование книги, Цена, Тираж, Стоимость тиража.
	Код, наименование комплектующего, количество изделий на складе, цена 1 изделия, стоимость партии изделий
	наименование товара, дата выпуска, цена единицы товара, количество в партии, стоимость партии
	вид недвижимости (квартира, дом, офис, склад), район города, цена 1 кв м площади, площадь, стоимость
	наименование товара, страна поставщик, цена единицы товара, количество в партии, стоимость партии
	наименование товара, название филиала торговой фирмы, цена единицы товара, объем продаж в филиале, выручка филиала
	название туристической фирмы, наименование тура, стоимость тура, количество проданных путевок, выручка фирмы

 

Лабораторная работа №4