ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 21.10.2020
Просмотров: 1549
Скачиваний: 3
Расширенные возможности Microsoft Excel 2007
41
Консолидация
Консолидация
–
объединение значений из
нескольких диапазонов в один
новый диапазон
.
Объединяемые диапазоны могут находиться в разных книгах
,
на разных листах
.
Для создания консолидации
данных надо:
1.
Выделить пустую ячейку листа
, начиная с которой будет размещен
консолидированный диапазон;
2.
На вкладке
Данные
(
Data
) в группе
Работа с данными
(Data Tools) нажать
кнопку
Консолидация
(Consolidate);
3.
В диалоговом окне Консолидация
выполнить настройки
:
1)
Выбрать
Функцию
,
которая будет применена к объединяемым данным
,
2)
Щелкнув в поле
Ссылка
, –
выделить первый диапазон
консолидации
,
3)
Нажать кнопку
Добавить
(Add)
,
…
шаги 2) и 3)
повторить для всех объединяемых областей,
…
4)
В области
Использовать в качестве имен
(Use Labels in)
включить
:
Подписи верхней строки
(Top row),
Значение левого столбца
(Left column);
Для консолидации с сохранением связи с исходными данными надо
поставить флажок
Создавать связь с исходными данными
(Create links to
source data). В этом случае, при изменении данных в первоначальных
областях, консолидированная область будет также изменяться.
4.
Нажать
OK
.
Центр Компьютерного Обучения «Специалист»
www.specialist.ru
42
Сводные таблицы
Сводные таблицы
–
интерактивные таблицы
,
используемые для анализа
больших объемов данных
,
–
одна из самых сильных возможностей
Excel.
Сводная таблица
может быть построена на основе правильно сформированной
области данных
:
без объединенных ячеек
,
пустых строк и столбцов
.
Создание сводной таблицы
1.
Перейти
в любую ячейку
области данных;
2.
Вкладка
Вставка
(Insert),
группа
Таблица
(Table),
выбрать
Сводная
таблица
(PivotTable)
;
3.
В диалоговом окне
Создание сводной таблицы
(Create PivotTable)
проверить правильность выделения
диапазона данных
(или установить
новый источник данных)
, определить
место размещения
Сводной таблицы:
На новый лист
(New Worksheet),
На существующий лист
(Existing
Worksheet)
; нажать
OK
.
Диапазон данных
,
на основе
которого строится отчет
Сводной
таблицы
должен
быть выделен
с
заголовками
столбцов
(
с шапкой
)
и
не
должен включать
итоговые
данные
.
4.
Внести поля
в область построения
сводной таблицы (в макет)
по схеме
:
разм
вып
В об
(
по
у
сумм
счит
(
тек
друг
в об
(∑ Va
нужн
кома
поле
Field
появ
окне
опер
пара
назв
(
мак
Прем
сраб
обла
итог
при
свод
наст
прав
пост
Д
р
п
П
(
(
В обла
мещают
по
олнять вы
бласти ∑
умолчанию
мируются
(
тается
их
к
кст
,
даты
)
Для испо
гих функ
бласти ∑
alues)
нажа
ного
поля
анду
«
П
ей значени
d
Settin
вившемся д
е выбрать
рацию
, наж
2
й
спосо
аметров п
ванию
пол
кет
) напри
мия
.
Дв
атывает и
асти строк
говых
опер
классиче
ной таблиц
Удобен
ройки
пар
вой
по
роения (ма
4
й
спос
Если панел
отображе
построен
или кликну
вкладке
Па
Для пост
размеченн
правой
к
Параметр
(Display)
в
(
разрешен
стях
Назв
оля
с
дата
ычисления
Значения
ю
)
данные
(
числа
)
или
количество
.
ользования
ций
надо
Значения
ать кнопку
я
, выбрать
Параметры
ий
…» (Value
ngs).
В
диалоговом
ь
другую
жать
OK
.
об
вызова
оля
–
дво
ля
в обла
имер, по
С
войной
и на
назва
или
столб
аций
по по
еской наст
цы (как в Ex
3
й
спосо
раметров
данным
акет)
–
Пар
соб
–
(впер
ль
«
Список
ения
:
щел
ния
(
макет
уть левой
араметры
троения
с
ные облас
кнопкой
м
ры сводно
включить
но перетаск
Настро
вания
стр
ами
или
т
я.
я
е
и
о
я
:
я
у
ь
ы
e
В
м
ю
окна настр
ойной кли
сти постро
Сумма по
клик
т
аниях пол
бцов
(
для вы
олям
),
но т
тройке ма
xcel 2003).
б
вызова
поля
–
м
в
обл
раметры
п
рвые в тек
к полей сво
елкнуть
т
)
,
выбрат
по макету
,
(Options)
в
сводной т
сти макет
мыши в
ой табли
опцию
Кл
кивание по
ойка п
ара
ок
,
Назва
екстом
. С
ройки
ик
по
оения
полю
также
лей в
ыбора
олько
акета
окна
клик
ласти
поля
.
кущей
Расширен
43
одной таб
правой
ть
Показа
,
нажать к
группе
Пок
таблицы п
та
(
как
область
ицы
(PivotT
Классическ
олей
)
(Clas
аметров
ания
стол
соответст
нные возмож
блицы
»
был
кнопкой
ать
список
кнопку
Спи
казать или
при помощ
в
Excel
2
построени
Table
Optio
кий макет
ssic
PivotTa
поля
бцов
,
Фи
твующими
жности Micro
ла закрыт
мыши
в
к
полей
(S
исок полей
и скрыть
(
щи перено
2003
)
над
ия
(
макет
ons),
на вкл
т сводно
able
layout)
льтр отч
и им числ
osoft Excel 20
та
,
то для
в област
Show
Field
L
й
(Field
List)
(Show/Hide)
оса полей
о щелкну
т
)
,
выбра
ладке
Выв
ой таблиц
).
чета
обыч
лами, мож
007
я ее
ть
ist)
)
на
).
й в
ть
ть
вод
цы
чно
жно
Центр Компьютерного Обучения «Специалист»
www.specialist.ru
44
версии)
для
быстрого изменения
итоговой
операции
воспользоваться контекстным
меню:
щелкнуть
правой кнопкой
по любому элементу поля
(для изменения итоговой
операции по этому полю)
–
Итоги по
(Summarize Data By),
выбрать итоговую операцию:
Сумма
(Sum),
Количество
(Count),
Среднее
(Average),
Максимум
(Max),
Минимум
(Min),
Произведение
(Product),
Дополнительно
…
(More options…)
– другие операции
диалогового окна
Параметры
поля значений
.
Вкладка
Дополнительные вычисления
(Show values as) позволяет выбрать
способ сложения значений
в области данных отчета сводной таблицы, используя
значения других областей данных. Доступны следующие функции:
Функция
Результат
Отличие
Отображение значений в виде разницы по отношению к значению
элемента в поле
Доля
Отображение значений в процентах к значению элемента в поле
Приведенное
отличие
Отображение значений в виде разницы в процентах по отношению
к значению элемента в поле
С нарастающим
итогом в поле
Отображение значений в виде нарастающего итога для
последовательных
элементов
в поле.
Например
,
поле
«
Месяц
»
будет
включать такие элементы как
«
Январь
»,
«
Февраль
»
и т
.
п
.
Доля от суммы
по строке
Отображение значения в каждой строке или категории в
процентах от итогового значения по этой строке или категории
Доля от суммы
по столбцу
Отображение всех значений в каждом столбце или ряду в
процентах от итогового значения по этому столбцу или ряду
Доля от общей
суммы
Отображение значений в процентах от их общей суммы
Индекс
Вычисление значений следующим образом:
(Знач_в_ячейке * Общий_итог) / (Итог_строки * Итог_столбца)
Изменение способа отображения итогов
Для
созданного
отчета
сводной
таблицы
существуют
разные
способы
отображения итогов
(
промежуточных и общих
)
.
1
способ
. Окно диалога «
Параметры поля
» –
настройка промежуточных итогов отчета.
Данное окно удобно вызывать кликом правой на
любом элементе данного поля в области отчета
.
Выбор «
нет
» позволит отказаться от
промежуточных итогов в текущем поле.
Опция «
другие
» позволит выбрать
несколько
функций
для
подведения
промежуточных итогов по текущему полю.
2
способ
. Быстрое
отображение
/
скрытие
промежуточных и общих
итогов
отчета.
1.
Щелкнуть в область отчета сводной таблицы;
2.
На вкладке
Конструктор
(
Design
) в группе
Макет
(
Layout
) выбрать нужное
из кнопок
Промежуточные итоги
(
Subtotals
) и
Общие итоги
(
Grand Totals
).
Промежуточные итоги вкл
/
выкл одновременно для всех полей отчета из
областей названий
(
строк и столбцов
)
сводной таблицы
.
3
сп
поле
доба
макс
раз в
В
стол
помо
При
уже
особ
. Поз
ей. Для эт
авить или
Повторн
Для ана
симальное
в виде отд
Чтобы
п
1.
Щел
поле
2.
Выб
3.
Люб
ново
поля
В поя
ычислен
В отчет
лбцы для
ощью вста
их создан
имеющие
1.
Щел
свод
2.
На в
в гру
кноп
выбр
(Calc
3.
В д
выч
Calcu
1) вв
2) со
3) д
и
п
зволит от
того:
клик
скрыть ит
ное испол
ализа дан
е, среднее)
дельных ст
повторно
лкнуть
пра
ей сводной
брать
∑ До
бым извес
ого поля.
я
,
выбрать
явившемся
ния в св
т сводной т
вставки ф
авки так
нии можно
ся.
лкнуть
в
дной табли
вкладке
Па
уппе
Серв
пку
Фор
рать
Вы
culated Field
диалогово
числяемог
ulated Field)
вести
Имя
оздать
Фо
важды кли
их вставки
поля можн
Для уд
вычисля
нажать
Вычисл
распола
тобразить/
кнуть пра
тоги, выбр
льзовани
ных одно
) это поле
толбцов в о
добавить
авой кноп
й таблицы
обавить в
тным спо
(
Наприме
ь команду
«
диалоговом
одной т
таблицы н
формул и
называемы
о вводить
Создани
область
ицы;
араметры
ис
(Tools)
р
рмулы
(
ычисляемо
d)
;
м окне
го
поля
)
:
я
нового по
рмулу
, на
икать по п
в формул
о записать
даления
яемого по
ь кнопку
Уд
ляемое пол
гаться
то
/скрыть
п
авой
на л
рать коман
е одного
го поля н
е отобража
области ∑
ь поле
в об
пкой
мыш
ы»;
значения
собом
изм
ер
,
в облас
«
Парамет
м окне выбр
аблице
нельзя обы
дополнит
ых вычисл
ь формулы
ие вычис
ь отчета
ы
(Options)
раскрыть
(Formulas)
,
ое поле
Вставка
я
(Insert
оля,
чиная с
=
полям для
у (имя
ь и вручну
вычисляе
оля
из спис
далить
.
ле
отлича
олько
в об
Расширен
45
промежут
любом эле
нду
Проме
поля в о
нескольким
ают в отче
Значения
бласть
Зна
ши на наз
я
(∑ Add to
менить ит
сти ∑
Знач
ры
полей
рать
другую
ычным пу
ельных ра
ляемых по
ы для расч
сляемого
ую), нажат
емого по
ска
Имя
вы
ается от о
бласти да
нные возмож
точные
ит
ементе пол
ежуточны
отчете сво
ми разным
ете сводно
я
(∑ Values)
ачения
, на
звании пол
Values
);
тоговую о
чения
(
∑
V
значений
…
ю
операци
тем встави
асчетов. В
олей и вы
чета новых
о поля
:
ь
OK
.
оля надо
ыбрать Им
обычных п
анных
–
∑
З
жности Micro
тоги
для
ля, по кот
ый итог
…
(
одной та
ми способ
ой таблиц
).
адо:
ля в обла
операцию
Values)
наж
…»
(Value
F
ю
,
нажать
ить новые
Все это вы
ычисляемы
х данных
о в окне
мя
_
поля
_
д
полей тем
,
Значения
osoft Excel 20
выбранн
торому на
Subtotal…)
блицы
бами (сумм
ы несколь
асти «Спис
ю
по данны
жать кноп
Field
Setting
ь
OK
)
е строки и
ыполняется
ых объект
с опорой
е
Встав
ля
_
удален
что мож
(
∑
Values).
007
ых
адо
).
ма,
ько
сок
ым
пку
gs).
или
я с
ов.
на
вка
ия
,
ет