Условия для выполнения процедуры консолидации
Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:
o столбцы во всех таблицах должны иметь одинаковое название (допускается лишь перестановка столбцов местами);
o не должно быть столбцов или строк с пустыми значениями;
o шаблоны у таблиц должны быть одинаковыми.
Создание консолидированной таблицы
Рассмотрим, как создать консолидированную таблицу на примере трех таблиц, имеющих одинаковый шаблон и структуру данных. Каждая из них расположена на отдельном листе, хотя по такому же алгоритму можно создать консолидированную таблицу из данных расположенных в разных книгах (файлах).
1. Открываем отдельный лист для консолидированной таблицы.
- На открывшемся листе отмечаем ячейку, которая будет являться верхней левой ячейкой новой таблицы.
- Находясь во вкладке «Данные» кликаем по кнопке «Консолидация», которая расположена на ленте в блоке инструментов «Работа с данными».
4. Открывается окно настройки консолидации данных.
В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:
сумма;количество;среднее;максимум;минимум;произведение;количество чисел;смещенное отклонение;несмещенное отклонение;смещенная дисперсия;несмещенная дисперсия.
В большинстве случаев используется функция «Сумма».
|
|
|
Выбор функции для консолидации в Microsoft Excel
В поле «Ссылка» указываем диапазон ячеек одной из первичных таблиц, которые подлежат консолидации. Если этот диапазон находится в этом же файле, но на другом листе, то жмем кнопку, которая расположена справа от поля ввода данных.
Переход к выбору диапазона для консолидации в Microsoft Excel
Переходим на тот лист, где расположена таблица, выделяем нужный диапазон. После ввода данных жмем опять на кнопку расположенную справа от поля, куда был внесен адрес ячеек.
Выбор диапазона для консолидации в Microsoft Excel
Вернувшись в окно настроек консолидации, чтобы добавить уже выбранные нами ячейки в список диапазонов, жмем на кнопку «Добавить».
Добавление диапазона в Microsoft Excel
Как видим, после этого диапазон добавляется в список.
Диапазон добавлен в Microsoft Excel
Аналогичным образом, добавляем все другие диапазоны, которые будут участвовать в процессе консолидации данных.
Все диапазоны добавлены для консолидации в Microsoft Excel
Если же нужный диапазон размещен в другой книге (файле), то сразу жмем на кнопку «Обзор…», выбираем файл на жестком диске или съемном носителе, а уже потом указанным выше способом выделяем диапазон ячеек в этом файле. Естественно, файл должен быть открыт.
|
|
|
Выбор файла для консолидации в Microsoft Excel
Точно так же можно произвести некоторые другие настройки консолидированной таблицы.
Для того, чтобы автоматически добавить название столбцов в шапку, ставим галочку около параметра «Подписи верхней строки». Для того, чтобы производилось суммирование данных устанавливаем галочку около параметра «Значения левого столбца». Если вы хотите, чтобы при обновлении данных в первичных таблицах обновлялась также и вся информация в консолидированной таблице, то обязательно следует установить галочку около параметра «Создавать связи с исходными данными». Но, в этом случае нужно учесть, что, если вы захотите в исходную таблицу добавить новые строки, то придется снять галочку с данного пункта и пересчитать значения вручную.
Когда все настройки выполнены, жмем на кнопку «OK».
Установка настроек для консолидации в Microsoft Excel
Консолидированный отчет готов. Как видим, данные его сгруппированы. Чтобы посмотреть информацию внутри каждой группы, кликаем на плюсик слева от таблицы.
Просмотр содержимого группы консолидированной таблицы в Microsoft Excel
|
|
|
Теперь содержимое группы доступно для просмотра. Аналогичным способом можно раскрыть и любую другую группу.
Содержимое группы группы консолидированной таблицы в Microsoft Excel
Как видим, консолидация данных в Эксель является очень удобным инструментом, благодаря которому можно собрать воедино информацию расположенную не только в разных таблицах и на разных листах, но даже размещенную в других файлах (книгах). Делается это относительно просто и быстро.
16. Фильтрация (выборка) данных.
ильтрация {выборка) данных в таблице позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. С помощью фильтров пользователь может в удобной для себя форме выводить или удалять (скрывать) записи списка.
В отличие от сортировки данные при фильтрации не переупорядочиваются, а лишь скрываются те записи, которые не отвечают заданным критериям выборки.
Отобранные записи можно форматировать или удалять, копировать в отдельную область таблицы, распечатывать, а также использовать для последующих вычислений или построения диаграмм.
Фильтрация данных в электронных таблицах может выполняться двумя способами: с помощью автофильтра или расширенного фильтра.
|
|
|
Фильтрация данных с использованием автофильтра.Для выполнения этой операции нужно:
1) установить курсор внутри таблицы;
2) ввести команду меню ДАННЫЕÞФильтрÞАвтофильтр;
3) щелчком мыши по кнопке со стрелкой раскрыть список столбца, по которому будет производиться выборка;
4) указать требуемые значения или выбрать строку "условие" и задать критерии выборки в диалоговом окне Пользовательский автофильтр.
Условия для отбора записей в определенном столбце могут состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ.
Каждая часть условия может включать:
· значение, которое может выбираться из списка или содержать шаблонные символы подстановки. В качестве символов подстановки используются звездочка * - для указания произвольного количества символов или вопросительный знак ? - для замены одного символа;
· оператор отношения (сравнения). При задании критериев выборки могут использоваться следующие операторы сравнения:
= Равно <> Не равно
< Меньше < = Меньше или равно
> Больше > = Больше или равно
Для восстановлениявсех строк исходной таблицы нужно щелкнуть мышью по кнопке со стрелкой (синего цвета) и в раскрывшемся списке выбрать строку "все" или выполнить команду ДАННЫЕÞФильтрсÞОтобразить все.
Для отменырежима фильтрации нужно установить курсор внутри таблицы и снова ввести команду меню ДАННЫЕÞФильтрÞАвтофильтр(убрать переключатель).
Фильтрация данных с использованием расширенного фильтра.Расширенный фильтр позволяет формировать множественные критерии выборки и осуществлять более сложную фильтрацию данных электронной таблицы с заданием набора условий отбора по нескольким столбцам.
Для фильтрации записей списка расширенный фильтр обеспечивает использование двух типов критериев:
· критериев сравнения; · вычисляемых критериев.
Фильтрация записей с использованием расширенного фильтра выполняется с помощью команды меню ДАННЫЕÞФильтрÞРасширенный фильтр.
Важной особенностью этого режима является то, что до выполнения самой команды фильтрации необходимо сформировать специальную область для задания условий фильтрации данных -диапазон условий отбора (интервал критериев).
Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Обычно для создания диапазона условий вначале копируют в отдельное место (на другом или на том же самом рабочем листе - как правило, выше исходной таблицы) строку с заголовками столбцов, затем в расположенные ниже строки вводят критерии выборки по отдельным столбцам.
Между значениями условий отбора и таблицей должна находиться, как минимум, одна пустая строка.
Если критерии отбора (условия фильтрации) вводятся в одной строке для разных столбцов, то они считаются связанными условием "И". Если критерии отбора записываются в разных строках, то они считаются связанными условием "ИЛИ".
После формирования диапазона условий с критериями выборки записей устанавливают курсор внутри таблицы, вводят команду ДАННЫЕÞФильтрÞРасширенный фильтри в диалоговом окне Расширенный фильтруказывают диапазон ячеек таблицы и адрес или имя сформированного заранее диапазона условий.
Записи можно фильтровать на месте или одновременно с выполнением фильтрации копировать в указанную область на текущем рабочем листе.
Чтобы скопировать отфильтрованные строки в другую область листа, следует установить переключатель Скопировать результаты в другое место,перейти в поле Поместить результат в диапазони указать верхнюю левую ячейку области вставки отобранных данных.
Ссылка на диапазон условий отбора должна включать только заголовки столбцов и строки с критериями, т. е. не содержать пустых строк.
17. Автофильтр.
С помощью фильтрации данных на листе, вы сможете быстро найти значения. Можно выполнить фильтрацию по одному или нескольким столбцам данных. С помощью фильтрации, можно управлять не только вы хотите просмотреть, но нужно исключить. Можно выполнить фильтрацию на основании выбранных параметров из списка или можно создавать специальные фильтры для выделения точно данные, которые вы хотите просмотреть.
Вы можете искать текста и чисел при фильтрации с помощью поля поиска в интерфейсе фильтра.
При фильтрации данных, если значения в один или несколько столбцов не соответствует условию фильтрации скрыты целых строк. Можно выполнить фильтрацию по числовых значений или текста значений или применить фильтр по цвету для ячеек, содержащих форматирование цветом их фона или текста.
Процедура
Дата добавления: 2018-08-06; просмотров: 836; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!