Автоматическое вычисление общих и промежуточных итогов

Использование функций баз данных.

Краткая теоретическая часть.

Занятие 3

Учебные вопросы:

1. Использование функций баз данных.

2. Автоматическое вычисление общих и промежуточных итогов.

3. Структурирование рабочих листов.

Библиотека Excel содержит специальные встроенные функции для работы с данными списков (баз данных). Эти функции позволяют получить информацию из списка или произвести в нем необходимые вычисления. Все эти функции сгруппированы в категорию Работа с базами данных.

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

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

Для удобства работы с функциями баз данных следует заранее присвоить имена диапазонам ячеек, содержащим данные списка (включая заглавную строку) и область критериев.

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

Автоматическое подведение итогов - это удобный способ быстрого обобщения и анализа данных в электронной таблице.

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

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

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

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

Для одной и той же группы данных можно одновременно вычислять промежуточные итоги с помощью нескольких функций, а также вычислять «вложенные» или многоуровневые итоги.

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

При подведении промежуточных итогов автоматически могут быть вычислены:

§ Сумма

§ Количество значений

§ Среднее

§ Максимум

§ Минимум

§ Произведение

§ Количество чисел и др.

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

1) отсортировать список по столбцу, для которого необходимо вычислить промежуточные итоги;

2) выделить какую-либо ячейку таблицы или требуемый диапазон;

3) ввести команду ДАННЫЕ ► Итоги;

4) в диалоговом окне Промежуточные итоги из списка При каждом изменении в: выбрать столбец, содержащий группы, по которым необходимо подвести итоги. Это должен быть тот столбец, по которому проводилась сортировка списка;

5) из списка Операция выбрать функцию, необходимую для подведения итогов, например Сумма;

6) в списке Добавить итоги по: выбрать столбцы, содержащие значения, по которым необходимо подвести итоги.

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

Команда ДАННЫЕ ► Итоги для одной и той же таблицы может выполняться многократно. При этом ранее созданные итоги могут как заменяться новыми, так и оставаться неизменными, если в диалоговом окне Промежуточные итоги снят переключатель Заменить текущие итоги.

На основе таблицы с итоговыми данными можно выполнять дополнительные вычисления.

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

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

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

Для удаления промежуточных итогов нужно:

· выделить ячейку в таблице, содержащей итоги;

· ввести команду меню ДАННЫЕ ► Итоги и щелкнуть по кнопке Убрать все.

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

Команда ДАННЫЕ ► Итоги используется для обобщения данных, которые находятся на одном рабочем листе, и в том случае, если эти данные расположены в смежных ячейках. Итоги выводятся на том же рабочем листе в структурированной таблице с исходными данными.

Дата добавления: 2014-10-23