LINEBURG


<< Пред. стр.

страница 48
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Отобрать сотрудников, у которых тариф выше среднего.
1. Открыть файл Примеры.XLS с помощью команды меню Файл • Открыть.
2. Выбрать лист Критерии.
3. Сформировать вычисляемый критерий, начиная с ячейки А10 (рис. 5.36).

Средний тариф Результат Средний тариф
вычисления
формулы
=Е2>СРЗНАЧ(Тариф) ЛОЖЬ

Рис. 5.36. Вычисляемый критерий

Имя столбца — Средний тариф должно отличаться от имен полей списка Micro­
soft Excel.
1. Выбрать лист Результат.
2. Установить курсор в ячейку Н1.
3. Выполнить команду меню Данные • Фильтр • Расширенный фильтр.
4. В диалоговом окне Расширенный фильтр указать:
О Скопировать результат в другое место.
О Исходный диапазон — блок БазаДанных.
О Диапазон условий — Критерии!$А$10:$А$11
О Поместить результат в диапазон — Результат! $Н$1.
О Кнопка ОК.
5. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

ВНИМАНИЕ
Фильтрация нескольких списков одновременно невозможна. Сложные алгоритмы совме­
стной обработки данных различных списков можно реализовать с помощью специализи­
рованного программного компонента Microsoft Query.


Агрегирование данных в Microsoft Excel
Агрегирование данных списков состоит в формировании промежуточных ито­
гов, создании сводных и консолидированных таблиц. Агрегирование информа­
ции выполняется для списков Microsoft Excel, в записях которых имеются поля
с повторяющимися значениями. Наиболее типичными для анализа данных яв­
ляются различного вида статистические итоги.
403
Базовые информационные технологии Microsoft Excel


Промежуточные итоги
Для формирования промежуточных итогов требуется предварительная сорти­
ровка списка по полям группировки записей (рис 5 37), например, по полям
Поле1, Поле2, ПолеЗ Команда меню Данные • Итоги позволяет сформировать ито
ги определенного вида по указанным полям при каждом изменении поля груп­
пировки — Поле1 (или Поле2 или ПолеЗ)
По отдельному полю 1руппировкн за одно выполнение команды можно сфор­
мировать итоги по нескотьким полям

Таблица 5.15. Встроенные функции для агрегированных итогов

Результат
Итоговая операция Встроенная функция

Сумма Сумма чисе г СУММ
Кол-во значений Количество заполненных СЧЕТЗ
полей
Среднее Среднее чисел СРЗНАЧ
Максимальное число
Максимум МАКС
Минимум Минимальное число МИН
Произведение чисел
Произведение ПРОИЗВЕДЕНИЕ
Кол-во чисел СЧЕТ
Количество заношенных
полей, содержащих числа
Несмещенное отклонение Несмещенная оценка СТАНДОТКЛОН
стандартною отклонения
1Снеральнои COBOKVJIHOCTIJ
по выборке данных
Смещенное отклонение Смещенная оценка СТАНДОТКЛОНП
стандартною отклонения
генеральной совокупности
по выборке данных
Несмещенная дисперсия Несмещенная оценка ДИСП
дисперсии генеральной
совокупности по выборке
данных

Смещенная дисперсия Смещенная оценка ДИСПР
дисперсии генеральной
совокмшости по выборке
данных


Тип итоговой операции (табт 5 15) выбирается с учетом типа данных
• поля числового типа — операции типа сумма, среднее, максимальное, мини­
мальное значение, количество чисел, количество значений, произведение, дис­
персия и отклонение,
404 Глава 5. Электронная таблица Microsoft Excel 2000


• поля типа дата/время — операции типа количество значений, максимальное,
минимальное значение;
• текстовые поля — операции типа количество значений.

Промежуточны* итоги

ОРИ ЙЙЙУФЙ ЙЗКанбМИЙ©!
{Профессия




"3
{Среднее

ДРОБИТЬ ктрги яп:
Г" Разряд работающего TJ
Р? Тариф

гс

Г" Kpww стрвиилы тмш вдпвзии


Отидаз
^SpaTt-tce- j Г


Рис. 5.37. Диалоговое окно промежуточных итогов

К полученным промежуточным итогам можно добавить новые итоги с сохране­
нием предыдущих итогов — для этого в диалоговом окне Итоги надо снять уста­
новку Заменить текущие итоги. Если установлено Итоги под данными, итоги раз­
мещаются под детальными строками таблицы, иначе — над строками таблицы.
Чтобы выводить каждую группу строк на отдельном листе, следует установить
Конец страницы между группами.
Промежуточным и общим итогам соответствует автоматически формируемая в
итоговой таблице встроенная функция вида:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (номер,ссылка1,ссылка2; ..),
где номер — номер функции промежуточных итогов (табл. 5.16), ссылка — от 1
до 29 диапазонов ячеек (блоков) или ссылок, по которым подводятся итоги.
Таблица 5.16. Функции итогов

Функция Функция
Номер Номер
СТАНДОТКЛОН
1 СРЗНАЧ 7
2 СТАНДОТКЛОНП
СЧЁТ 8
СЧЁТЗ СУММ
9
, ДИСП
МАКС 10
5 ДИСПР
МИН 11
6 ПРОИЗВЕЛ


Для приведения списка в исходное состояние курсор устанавливается в таблицу,
выполняется команда меню Данные • Итоги. В диалоговом окне Итоги нажимает-
405
Базовые информационные технологии Microsoft Excel


ВНИМАНИЕ
Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ игнорирует скрытые строки, которые получаются в
результате фильтрации списка.

Пример 19
Сформировать промежуточные итоги по полю Профессия. Вычислить средний
тариф, средний разряд, количество сотрудников по профессиям.
1. Открыть файл npnMepbi.XLS с помощью команды меню Файл • Открыть.
2. Выбрать лист Картотека.
3. Установить курсор в область списка на листе Картотека.
4. Отсортировать список по полю Профессия с помощью команды меню Данные •
Сортировка.
5. Установить курсор в область списка на листе Картотека.
6. Выполнить команду меню Данные • Итоги:
О При каждом изменении — Профессия.
О Операция — Среднее.
О Добавить итоги по — Разряд работающего, Тариф.
О Установить Итоги под данными.
О Нажать на кнопку ОК.
7. Установить курсор в область списка на листе Картотека.
8. Выполнить команду меню Данные • Итоги:
О При каждом изменении — Профессия.
О Операция — Кол-во значений.
О Добавить итоги по — Табельный №.
О Установить Итоги под данными.
О Нажать на кнопку ОК.
На рис. 5.38 представлен внешний вид таблицы Картотека с промежуточны­
ми итогами.
9. Закрыть файл с сохранением — команда меню Файл • Закрыть.
1 ! ;F;L. в i
М2Л
L" «>«»,<, Профессия работающего Ларя.ф. шты
".»
2 Колесов В И 02345 Грузчик 3 5 76 1
г* 6 79
1« 3 Крылов А Р 00127 Грузчик 4 2
4 Михайлов П Р 12980 Грузчик 3 5 76 2
\; 3 Грузчик Кол во значений

**< 8 "Иванов А П Грузчик Среднее 3 333333333 610
7 0(234 Кладовщик 3 5 76 1
ч

| : $ 'Смирнов И А 13980 Кладовщик 4 6 79 3
2 Кладовщик Кол ео значений
&J
Кладовщик Среднее 3,5 е?в
» Ж
И* п,Соколов Р В
П 21097 Уборщик 2 495 1
1 Уборщик Кол во значений
и W Уборщик Среднее 2 495
6 Общее количество
-1 Ч< Общее среднее 3 166666667 5 97


Рис. 5.38. Промежуточные итоги
406 Глава 5. Электронная таблица Microsoft Excel 2000


Сводные таблицы
Сводная таблица обеспечивает формирование сводной (агрегированной) ин­
формации и представление табличных данных в структурированном виде, а так­
же построение связанной со сводной таблицей сводной диаграммы. Источника­
ми данных для формирования сводной таблицы являются:
• список (база данных) Microsoft Excel;
• внешний источник данных (текстовый файл, содержащий табличные дан­
ные, реляционная база данных);
• диапазоны ячеек электронной таблицы для консолидации;
• другая сводная таблица Microsoft Excel.
ПРИМЕЧАНИЕ
Если источник данных для построения сводной таблицы — список (база данных) Microsoft
Excel, сводная таблица размещается в той же книге. Если источник — диапазон консоли­
дируемых ячеек, они могут находиться на различных листах одной рабочей книги и даже
в разных рабочих книгах.

Мастер сводных таблиц
Построение сводной таблицы осуществляется с помощью Мастера сводных таб­
лиц, который вызывается командой меню Данные • Сводная таблица. Работа мас­
тера сводных таблиц выполняется по шагам:
• первый шаг — выбор типа источника данных и вида результата;
• второй шаг — указание диапазона ячеек для построения сводной таблицы.
• третий шаг — формирование макета (структуры) сводной таблицы — кнопка
Макет и настройка параметров сводной таблицы — кнопка Параметры.
Макет сводной таблицы включает области (рис. 5.39):
• Страница — для размещения полей фильтрации (отбора) записей, отображае­
мых в сводной таблице.
• Строка — для размещения полей группирования, учитывается последователь­
ность полей для создания вложенных групп, подгрупп и т. д.
• Столбец — для размещения полей группирования, учитывается последова­
тельность полей для создания вложенных групп, подгрупп и т. д.
• Данные — для размещения полей итогов.
При построении макета сводной таблицы выполняются следующие правила:
• любое поле размещается однократно в области группирования (страница, стро­
ка или столбец);
• в области Данные размещаются только те поля, которые не вошли в области
группирования;
• любое поле из области Данные может многократно размещаться в этой об­
ласти для вычисления различных итогов.
407
Базовые информационные технологии Microsoft Excel


М а с т е р сводных таблиц и д и а г р а м м - м а к е т

Tjllrfflllj


РГ СЕЛ*



Столбец
?тр.аница|




' Строка Данные




^ftffyttJfC^iT.'i : <**?*,,{
Рис. 5.39. Макет сводной таблицы

Сводные таблицы являются весьма мощным и гибким инструментом анализа в
среде Microsoft Excel. Основные ограничения для сводных таблиц представ пены
в табл. 5.17.
Таблица 5.17. Ограничения для сводных таблиц

Параметр Значение

Ограничивается объемом доступной
Число сводных таблиц на одном листе
оперативной памяти

8,000
Число элементов сводной таблицы

Число полей строк или столбцов Ограничивается объемом доступной
в сводной таблице оперативной памяти

Число полей страницы в сводной таблице 256 (может офаничиваться объемом
доступной оперативной памяти)

Число полей данных в сводной таблице 256

Число формул вычисляемых элементов Офаничивается объемом доступной
в сводной таблице оперативной памяти


Вычисляемые поля и элементы
Помимо «базовых» полей из основной таблицы (списка), сводная таблица до­
пускает формирование вычисляемых полей в области Данные, а также вычис­
ляемых элементов для полей группирования. Элементы полей группирования
могут объединяться в группы, для которых можно указать тип игога, скры­
тие/отображение деталей подчиненных полей
Сводная таблица имеет набор параметров, влияющих на внешнее представле­
ние. Кнопка Параметры вызывает диалоговое окно настройки формата сводной
таблицы и данных внешнего источника (рис 5.40).
408 Глава 5 Электронная таблица Microsoft Excel 2000


П арамстры стопной таблицы
*
Фовк»?
tWSSf СТРвИИШ! |Вниз, затем поперек »|
Р oftu«s?yMw»«r<H#»
Р сйцая^миа пб г.трй??ц
**—<т^ (5-13
Р артофармаг
Г" тщ?тьщ»ПЬ>к »ач**я
> Г для ода бек доврямген 1
Г* р$;$единят& ячейки ачг^яовка*
F сорандаь ф<*м«т«роеаиив Р дл» пуста*«меш отображать! }
Р повторять водпиош^кдаглой Г [««sTfesaroncew»
страниц? веч»гя

единые
Источник*
Р сафвми» дэчкмевпвсге:!: телицей F IS-."***&!««*!
F ра^вртьвзние разрешена Г* 4>>< ^ *! • "р>
Г" оаурвитьири агкрытии ? **к„& > ? &•<& ' * « v v **
Г" ^

Г>. ,Л9 «>•«-*-* }б0 i| ш

| OK | ог»«иа {


Рис. 5.40. Параметры сводной таблицы

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

Панель инструментов Сводные таблицы

Для работы со сводными таблицами выводится панель инструментов Сводные таб­
лицы (рис. 5 41)

•Саояные таблицы
т
jLUJjfejB
Своайаятабянаа» $ S §fti ?J2l "•

ФИО Та«е*>н, rtiwscc. Paspaa," Т*(*ч> &•



Дуоты '•



-&
Рис. 5 . 4 1 . Панель инструментов Сводные таблицы

Кнопка Сводная таблица вызывает меню команд:
• Формат отчета сводной таблицы для выбора одного из 10 автоформатов для
отчета сводной таблицы.
• Сводная диаграмма для построения диаграммы для сводной таблицы.
• Мастер для перехода к 3-му шагу построения сводной таблицы, который обес­
печивает изменение макета, настройку параметров, выбор места размещения
сводной таблицы.
409
Базовые информационные технологии Microsoft Excel


• Обновить данные сводной таблицы при изменении информации источника
• Настройка клиент-сервера при использовании OLAP-куба для построения свод­
ной таблицы
• Выделить отдельные части сводной таблицы заголовки, данные или сводную
таблицу целиком
Q Формулы для создания вычисляемых полей в области данных или вычистяе
мых объектов/элементов в области заголовков
• Параметры поля для настройки параметров поля сводной таблицы
• Параметры таблицы для настройки параметров (аналог кнопки Параметры)
• Отобразить страницы сводной таблицы на отдельных листах книги

<< Пред. стр.

страница 48
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign