В списке или базе данных какого-либо листа Microsoft Excel

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

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

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

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

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

В общем случае полное имя интервала задается в виде:

имя книги – имя листа – интервал. Если СТ строится в той же книге, где находится исходная таблица, то имя книги указывать не обязательно. Для указания интервала из другой закрытой рабочей книги нажимается кнопка ПРОЛИСТАТЬ, далее, в открывшемся диалоговом окне, выбирается диск, каталог и файл закрытой книги, вводятся имя рабочего листа и интервал клеток.

5.2.3 На третьем шаге в макете таблицы в режиме создания (рис.10.) создается структура СТ и определяются ее функции. Макет представлен в центре окна и состоит из областей: строка, столбец, страница и данные. Справа от макета отображаются все имена полей (заголовки столбцов) в заданном интервале исходной таблицы.

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

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

Строка - поля этой области формируют заголовки строк СТ; если таких полей несколько, то они размещаются в макете сверху вниз, обеспечивая группирование данных СТ по иерархии полей, где для каждого элемента внешнего поля, элементы внутреннего поля повторяются (см. пример 3);

Столбец - поля в этой области формируют заголовки столбцов СТ; если таких полей несколько, то они в макете размещаются слева направо, обеспечивая группирование данных СТ по иерархии полей;

Страница - поля в этой области выступают в качестве фильтров и позволяют просматривать «на отдельных страницах» данные СТ, соответствующие разным значениям поля, помещенного в эту область; поле страницы в режиме просмотра располагаются на две строки выше основной части СТ в крайнем левом столбце (рис.13). В соседней справа клетке выводится (Все) и рядом раскрывающая стрелка для выбора другого элемента поля; в области страница может быть размещено несколько полей, между которыми устанавливается иерархическая связь – сверху вниз;

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

Пример 3.

Задание: По исходной таблице «Учет количества деревьев» (Приложения, табл.1) построить СТ, сгруппировав данные по породе, № делянки и возрасту. Подсчитать количество деревьев в указанных группах.

Выполнение: В макете (рис.10.) в область строка размещены два поля: порода дерева и № п.п. делянки, в область столбец - поле Возраст. В области данные: Сумма по полю Количество. Готовая СТ (рис.12.) выводится на листе рабочей книги Ехсе1 в режиме просмотра, где клетки, содержащие имена полей, выделены темным цветом. На пересечении строк с номерами делянок (которые повторяются для каждой породы деревьев) и столбцов с возрастом выводятся величины, обозначающие общее количество деревьев данного возраста и породы деревьев, произрастающих на данной делянке. По внешнему полю Порода выводятся промежуточные итоги (Дуб всего, Клен всего и т.д.) и общие итоги по строкам и столбцам.

Примечание: Excel автоматически вычисляет промежуточные итоги под каждым элементом внешнего поля строки или столбца при помощи той же функции, которая выбрана для поля данных.

Данная СТ станет более компактной и это позволит отдельно просматривать данные по каждой породе, если переместить поле Порода в область страница.

В полученной СТ (рис. 13.) данные сгруппированы по возрасту, по номеру делянки и просуммированы для всех пород деревьев. Для ввода данных по любой отдельной породе, нужно щелкнуть на раскрывающей стрелке и выбрать другой элемент (например, ель).

Рис.11. Возможный вид макета сводной таблицы

Рис. 12. Сводная таблица к примеру 3

Рис.13. Фрагмент сводной таблицы с полем в области страница

Пример 4.

Задание: По данным таблицы “Баланс ресурсов и использования мяса и мясопродуктов” (Приложения, табл.12) определить: как в процентном соотношении изменился общий объем ресурсов за 1994-1996 г.?

Выполнение: Так как, общий объем ресурсов по каждой области находится в столбце ИТОГ, нужно создать СТ с вычислением суммы по полю ИТОГ для каждого отдельного года и определить процентное отличие полученной суммы годов 1995 и 1996 от 1994. В макете поместить поле Области, в область строка: Годы, в область данные – Сумма по полю, в итог задать дополнительное вычисление: «% отличия от», по полю Годы базовый элемент: 1994 (рис.15).

Рис. 14. Окно “Поле сводной таблицы” для настройки

Не нашли, что искали? Воспользуйтесь поиском: