Сведения о консолидации нескольких диапазонов

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

Итоговый консолидированный сводный отчет может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводный отчет: «Строка», «Столбец» и «Значение». В дополнение к этому в отчет можно включить до четырех полей фильтра данных на странице, которые носят название «Страница1», «Страница2», «Страница3» и «Страница4».

 ПРИМЕЧАНИЕ.   В интерфейсе пользователя мастера сводных страниц и диаграмм используется термин «поле страницы». В Microsoft Office Excel 2007 в других частях интерфейса пользователя сводной таблицы поле страницы получило название поля фильтра отчета.

Настройка исходных данных

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

Поля страницы в консолидации данных

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

Другие способы консолидации данных

В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, можно создать формулы с трехмерными ссылками, воспользоваться командой Консолидация (доступна на вкладке Данные в группе Работа с данными).

13. Относителная абсолютная адресация. Синтаксис. Применение

Относительными называются ссылки, которые при копировании в составе формулы в другую ячейку автоматически изменяются

При копировании формулы с относительной ссылкой (столбец)(строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столец+m)(строка+n)

В большинстве случаев это очень удобно, но иногда этого не требуется.

Абсолютными называются ссылки, которые при копировании в составе формулы в другую ячейку не изменяются

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

Изменение типа ссылки

Для того, чтобы превратить относительную ссылку в абсолютную, достаточно в режиме редактирования формулы установить курсор непосредственно за ссылкой и нажать клавишу <F4> Впрочем, можно вставить в ссылку знаки доллара и при помощи обычных приемов редактирования

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

14. Работа с шаблонами. Создание, редактирование, загрузка шаблона на новый лист, вставка типовых шаблонов.

Для создания шаблона на базе файла шаблона MS Excel:

1. установите переключатель Использовать настройки шаблона Excel (подробно описание шаблонов см. в пункте Файл шаблона в формате .xlt);

2. В поле Файл Шаблона укажите путь к файлу шаблона. Для этого, нажмите кнопку 

3. справа от поля и укажите в открывшемся диалоге выбора файлов путь к файлу шаблона в формате .xlt. Создание файла шаблона описано в разделе Файл шаблона в формате .xlt;

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

5. В таблице Соответствие полей и диапазонов шаблона указывается в каких именованных диапазонах шаблона отображаются поля базы данных В столбце Поляперечислены поля базы данных, а в столбце Диапазон указываются соответствующие им диапазоны шаблона. По умолчанию, для полей подставляются диапазоны с соответствующими названиями (знаки подчеркивания в названии диапазона рассматриваются как пробелы).

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