Консолидация данных в программе Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Консолидация данных в программе Microsoft Excel

Лабораторная работа №4.
КОНСОЛИДАЦИЯ ДАННЫХ В MS EXCEL

Цель работы: изучить понятие консолидации данных, научиться консолидировать данные однотипных таблиц средствами MS Excel .

Организационная форма занятия: лабораторная работа, выполнение проекта.

Вопросы (компетенции, навыки) для освоения:

  1. Познакомиться с понятием «консолидация» и изучить элементы интерфейса MS Excel для проведения консолидации данных.
  2. Освоить технологию проведения консолидации.

Задания для выполнения и методические рекомендации:

В MS Excel существует возможность консолидации данных (объединения) из различных источников на одном рабочем листе. При консолидации одна ячейка результирующего листа связана с несколькими ячейками. Значение такой ячейки вычисляется на основе значений ячеек, с которыми она связана.

Задание 1. Изучите возможности консолидации данных в MS Excel .

  • Создайте три однотипные таблицы по образцу на одном листе или на разных листах MS Excel (рис. 1).

Рис. 1 Образец таблиц для консолидации

  • Подсчитайте средний балл студента по текущей аттестации.
  • Проведите консолидацию 3-х таблиц аттестации в одну с вычислением среднего балла по каждому предмету и разместите консолидированную таблицу на листе Консолидация , для чего:
  • перейдите на чистый лист в книге и установите маркер мыши в левый верхний угол будущей таблицы;
  • на панели Данные выберите Консолидация ;
  • в окне Консолидация (рис. 2) установите значение Функции – Среднее ;

Рис. 2 Диалоговое окно Консолидация

  • перейдите в строку Ссылка, затем выделите на листе Данные для консолидации всю область 1 таблицы, включая заголовки таблицы (но не название таблицы) . Нажмите кнопку Добавить . Затем выделите таблицу 2 и снова нажмите кнопку Добавить . Затем таблицу 3 – Добавить . Установите флажки в окнах Подписи верхней строки , Значение верхнего столбца , чтобы использовать подписи таблиц в качестве подписей к новой консолидированной таблице. Поставьте флажок в окне Создавать связи с исходными данными для того, чтобы любое изменение в исходных таблицах приводило к автоматическому пересчёту в консолидированной таблице. Нажмите ОК ;
  • Обратите внимание на кнопки сворачивания и разворачивания структуры, которые появились слева от таблицы.
  • Настройте представление числовых данных в консолидированной таблице. Установите Числовой формат с одним знаком после запятой. (панель Главная – Число )
  • Примените параметры форматирования к таблице (установите границы таблицы, оформите шапку) (рис. 3).

Рис. 3 Консолидированная таблица

Задание 2. Постройте круговую диаграмму по средним баллам студентов за сессию консолидированной таблицы.

Сохраните результаты проделанной работы в своей папке под названием Работа 4

  1. Информатика: учебник / Под ред. проф. В.В.Трофимова. – М.: Издательство Юрйт; ИД Юрайт, 2011. – 911 c. ( указать раздел и стр.)
  2. Симонович С.В. Информатика. Базовый курс. Учебник для вузов. Изд-во: Питер, 2009. – 640 с. ( указать раздел и стр.)
  1. Программное обеспечение ЭВМ (практическое руководство по работе с приложениями OpenOffice.org): Учебно-методическое пособие. – Ставрополь: Изд-во СГУ, 2009. – 235 с. ( Электронный процессор OpenOffice.org Calc – Лабораторная работа 5, стр. 122-125 ).

http://www.intuit.ru/catalog/office/ – Офисные технологии: Microsoft Excel : Работа с данными.

http://office.microsoft.com – официальный сайт Корпорации Майкрософт (Microsoft Corporation)

Задание для развития и контроля владения компетенциями:

Выполнение проекта: «Подготовка отчета «Показатели выпуска молочной продукции» средствами MS Excel »

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

  1. Создайте новый документ Excel Молочный комбинат . На листе 1 создайте таблицу Выпуск молочной продукции за 2006 год (в литрах) (рис. 1).

Рис. 1. Выпуск молочной продукции за 2006 год

  1. На листе 2 создайте Выпуск молочной продукции за 2007 год (рис. 2.). Так как заголовки столбцов и строк объединяемых таблиц должны полностью совпадать, то лучше скопировать структуру таблицы с первого листа на второй лист и заполнить эту структуру новыми данными.

Рис. 2. Выпуск молочной продукции за 2007 год

  1. На листе 3 создайте «Прайс-лист продукции молочного комбината» (рис. 3.).

Рис. 3. Прайс-лист продукции молочного комбината

  1. При помощи функции Сумм посчитайте строки Итого за год для каждого вида продукции на 1 и 2 листах.
  2. Используя данные прайс-листа подсчитайте столбец На сумму в обеих таблицах. Для этого установите курсов в пустую ячейку столбца На сумму, создайте формулу = Итого за год * Цена за 1 литр. Например, для ячейки G 3 первой таблицы нужно ввести формулу = F 3*Лист3. B 3. (формула вводится при помощи мыши) Готовую формулу можно скопировать для оставшихся строк таблицы.
  3. На листе 4 посчитайте Средние показатели выпуска продукции за 2006-2007 годы . Для чего проведите консолидацию данных первых двух таблиц.
  4. Измените количество выпуска молока в 1 квартале 2006 года на 0. Проверьте, как изменились расчёты в итоговой таблице.
  5. Постройте гистограмму и круговую диаграммы по итоговой таблице. Вставьте название в область диаграммы, легенду. В гистограмме подпишите оси координат.
  6. Сдайте отчет о проделанной работе преподавателю.

Лабораторная работа Консолидация данных

Цель занятия: Изучение технологии связей между файлами и консолидации данных в MS EXCEL.

Для изучения технологии работы, смотреть видео урок http://www.youtube.com/watch?v=8ouvhYlsza4&feature=player_detailpage – консолидация данных в Excel

Задание 1:Задать связи между файлами.

  1. Запустите редактор электронных таблиц MS EXCEL и создайте новую электронную книгу.
  1. Создайте таблицу «Отчет о продажах 1 квартал» по образцу:

И проведите расчеты Прибыли: Прибыль = Доходы – Расходы. Сохраните файл под именем «1 квартал».

  1. Создайте таблицу «Отчет о продажах 2 квартал» по образцу в виде нового файла:

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

Обратите внимание, как изменился расчет Прибыли. Сохраните файл под именем «2 квартал».

  1. Создайте таблицу «Отчет о продажах за полугодие» по образцу в виде нового файла:

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

  1. Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал».

Задание 2:Обновить связи между файлами.

  1. Закройте файл «Полугодие» предыдущего задания.
  1. Измените значения «Доходы» в файлах первого и второго квартала, увеличив их на 100. Сохраните изменения и закройте файлы.
  1. Откройте файл «Полугодие» Одновременно с открытием файла появится окно с предложением обновить связи. Для обновления нажмите кнопку Да. Проследите, как изменились данные файла полугодие (величина «Доходы» должна увеличиться на 200 р. И принять значение 887,18 р.).

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

  1. Изучим процесс ручного обновления связи. Сохраните файл «Полугодие» и закройте его.
  1. Вновь откройте файлы первого и второго кварталов и измените исходные данные Доходов, увеличив значения на 100 р. Сохраните изменения и закройте файлы.
  1. Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи в меню Правка выберите команду Связи, появится окно. В окне перечислены все файлы, данные из которых используются в активном файле «Полугодие».

Задание 3:Консолидация данных для подведения итогов по таблицам данных сходной структуры.

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

  1. Откройте все три файла задания 2 и в файле «Полугодие» в колонке В удалите все численные значения данных. Установите курсор в ячейку В3.
  1. Выполните команду Данные/Консолидация. В появившемся окне Консолидация выберите функцию – «Сумма».

В строке «Ссылка» сначала выделите в файле «1 квартал» диапазон ячеек В3:В5 и нажмите кнопку Добавить, затем выделите в файле «2 квартал» диапазон ячеек В3:В5 и опять нажмите кнопку Добавить.

В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации. Далее нажмите кнопку ОК, произойдет консолидированное суммирование данных за первый и второй кварталы.

Вид таблицы после консолидации приведен на рисунке:

Задание 4: Консолидация данных для подведения итогов по таблицам неоднородной структуры.

  1. Запустите редактор электронных таблиц MS EXCEL и создайте новую электронную книгу. Наберите отчет по отделам за третий квартал по образцу:

Произведите расчеты и сохраните файл с именем 3 квартал.

  1. Создайте новую электронную книгу. Наберите отчет по отделам за четвертый квартал по образцу:

Произведите расчеты и сохраните файл с именем «4 квартал».

  1. Создайте новую электронную книгу. Наберите название таблицы «Полугодовой отчет о продажах по отделам». Установите курсор на ячейку А3 и проведите консолидацию за третий и четвертый кварталы по заготовкам таблиц. Для этого выполните команду Данные/Консолидация. В появившемся окне консолидации данных сделайте ссылки на диапазон ячеек А3:Е6 файла «3 квартал» и A3:D6 файла «4 квартал». Обратите внимание, что интервал ячеек включает имена столбцов и строк таблицы.

В окне Консолидация активируйте опции (поставьте галочку):

  • Подписи верхней строки;
  • Значения левого столбца;
  • Создавать связи с исходными данными (результаты будут не константами, а формулами).
Читайте также:  Программа Microsoft Excel: сводные таблицы

После нажатия кнопки ОК произойдет консолидации. Сохраните все файлы в папке вашей группы.

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

Создайте три таблицы, содержащие сведения о ценах на программные продукты, по образцу, приведенному на рис.1. Для каждого месяца первого квартала на отдельном листе книги Имя_10_1 создается собственная таблица с названием “Прайс-лист (Месяц)”, где месяц – Январь, Февраль, Март.

2. Создайте таблицы “Отгрузка (Январь)”, “Отгрузка (Февраль)” и “Отгрузка (Март)”по образцу, приведенному на рис.2, пользуясь режимом группового заполнения, и дайте листам книги названия: Отгр_ЯНВ, Отгр_ФЕВ, Отгр_МАР.

Создайте новую таблицу “Суммарный доход за три месяца”, в которой будут сведены итоговые значения выручки за все кварталы за счет организации “трехмерной связи”, т.е. связи между одинаковыми клетками однотипных таблиц. Принцип создания такой таблицы представлен на рис.4. В создаваемой таблице запишите две формулы для получения одного и того же значения, но в одной из них запишите формулу с непосредственным обращением к каждой таблице, а в другой – с обращением к блоку таблиц, так называемую “объемную” формулу. Примеры записи таких формул приведены на рис.4 непосредственно под ячейками В4, В7 и выделены курсивом.

1. Создайте три таблицы, содержащие сведения о поставляемых товарах, по образцу, приведенному на рис.11.1. Для каждого месяца первого квартала на отдельном листе книги Имя_11_1 создается собственная таблица с названием “Поставки товаров в месяце”, где месяц январь, февраль, март. При создании таблиц пользуйтесь режимом “группового заполнения листов” или копирования данных.

1.1. Переменная часть таблиц (столбцы “Объем” и “Дата”) должна соответствовать данным, приведенным на рис.11.1. Переименуйте листы, дав им соответствующие имена (Янв, Фев, Мар).

2. Вставьте новый лист, дав ему имя “Конс_данные”. Скопируйте в него заголовок таблицы и откорректируйте его соответствующим образом (рис.11.3). Установите курсор в первую свободную ячейку (А3).

2.1. Активизируйте диалоговое окно Консолидация с помощью команд Данные – Работа с данными – Консолидация, и, последовательно указывая в поле Ссылка необходимые адреса консолидируемых областей, сформируйте их полный список, состоящий из трех записей, как представлено на рис.11.2.

2.2. В поле Функция оставьте функцию вычисления суммы и укажите, что в качестве имен (названий строк) будут выбираться данные из левого столбца (А) консолидируемой области. Укажите на необходимость создания динамической связи с исходными данными.

2.3. Выполните консолидацию. Сравните полученные результаты с приведенными на рис.11.3.

Консолидация данных в EXCEL

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

Консолидация объектов – это процесс объединения данных из разных источников в виде итоговых значений, размещаемых в диапазоне назначения.

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

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

Возможны следующие виды консолидации:

– по физическому расположению (п.1.2);

– по заголовкам строк и столбцов (п.1.3);

– с использованием ссылок (п.1.4);

– ручная консолидация (п.1.5).

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

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

Проблемам связывания объектов посвящен раздел 2.

Общее описание процесса консолидации

Процесс консолидации предполагает обязательное указание

– наличия связи между объектами консолидации;

– типа (функции) консолидации.

Последние четыре действия выполняются с помощью диалогового окна Консолидация, которое вызывается по команде Данные – Консолидация (рис.1).

Диапазон назначения указывается с помощью выделения на рабочем:

– диапазона ячеек, куда необходимо поместить обобщенные данные.

Заполнение его осуществляется по правилам, представленным в табл.1.

Таблица 1. Заполнение диапазона назначения

Заполняются все ячейки, необходимые для всех консолидируемых категорий (элементов) исходных данных

Заполняются ячейки вниз от выделения. Ширина области назначения в точности совпадает с шириной выделения

Заполняются ячейки вправо от выделения. Высота области назначения в точности совпадает с высотой выделения

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

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

– Текст и формулы не переносятся с исходных листов в область назначения. Их необходимо создать вручную перед консолидацией.

Источники данных представляют собой диапазоны ячеек. Число диапазонов может достигать 255. Источники данных не обязаны быть открыты во время консолидации. Исходные области обрабатываемых данных задаются либо трехмерными формулами непосредственно в ячейках диапазона назначения, либо в поле Ссылка диалогового окна Консолидация (рис.1). Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах или в файлах Lotus 1-2-3.

Для указания источников данных могут быть использованы два способа:

– выделение исходного диапазона с помощью мыши;

– ввод ссылки на диапазон с клавиатуры.

Выделение исходного диапазона с помощью мыши осуществляется стандартными приемами.

Ввод ссылок на диапазон с клавиатуры. Существует два вида ссылок: внутренние и внешние.

Внутренняя ссылка – это ссылка на диапазоны ячеек рабочей книги. Она является частным случаем внешней ссылки.

Синтаксис внутренней ссылки:

Необходимо записать в ячейку В2 первого листа(Лист1) значение ячейки D4 следующего листа (Лист2).

Для этого в ячейку В2 введите формулу:

Внешняя ссылка -это ссылка на диапазоны ячеек, расположенных в других книгах.

Синтаксис внешней ссылки:

Необходимо в ячейку В2 первого листа (Лист1) записать значение ячейки А1 из листа 2кв1996 файла КВАРТАЛ2.xls, находящегося на диске Е: в папке USERS.

Для этого в ячейку В2 следует ввести следующую формулу:

Если исходная книга закрыта, полный путь к ней следует указывать обязательно. Путь можно набрать с клавиатуры или воспользоваться кнопкой Обзор окна Консолидация (рис.1) и выбрать файл на диске.

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

Пример 3. Исходные области и область назначения находятся на одном листе. Например, включить диапазон Бюджет (диапазону ячеек предварительно присвоено имя Бюджет).

Для этого вводим ссылку

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

Пример 5. Исходные области и область назначения находятся в разных книгах. Используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» из листа «Дальний Восток» в книге «1996», находящейся в этой же папке, введите:

Пример 6. Исходные области и область назначения находятся в разных книгах разных каталогах диска. Используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль» в книге «Отдел продаж», которая находится в папке «Бюджет» на диске С:, введите:

Если диапазонам назначены уникальные, не присвоенные автоматически имена, то в ссылке можно не указывать имена листов. Например ‘[1996.xls]’!Продажи или ‘[C:БюджетОтдел продаж.xls]’!Оборот в примерах 5 и 6.

Мастер Класс Консолидация Данных В Excel 2010

Название: Мастер Класс Консолидация Данных В Excel 2010

Длительность: 6 мин и 3 сек

Битрейт: 192 Kbps

7.96 MB и длительностью 6 мин и 3 сек в формате mp3.

Консолидация Сборка Данных Из Нескольких Таблиц В Excel

Excel Диаграмма Парето

Динамическая Выборка Функциями Индекс И Поискпоз

Мастер Класс Анализ Сводных Таблиц В Excel

Excel На Экзамене Оценщика Полезные Функции Для Расчета

Саморегулируемая организация НП “АРМО”

Создание Выпадающих Списков В Excel

Консолидация Сборка Данных В Excel Из Нескольких Таблиц

Приемы Первичной Обработки Массивов Данных В Ms Excel

Консолидация Данных В Microsoft Excel

Создание Сводной Таблицы В Excel

Читайте также:  SQL запросы в Microsoft Excel

Урок 7 Функция Если

Как Сделать Консолидацию Данных В Excel Пошаговая Инструкция Консолидации Руно

Образовательный центр Руно. Бухучет Кадры Логистика

Как Построить Диаграмму Шкалу В Microsoft Excel

Microsoft Excel 2007 Сводные Таблицы

Программирование Макросов На Vba В Excel

Урок 18 Функция Смещ В Excel

Создать Отчет За 5 Минут В Excel Сводные Таблицы

Как Использовать Функцию Впр Vlookup В Excel

Как Объединить Значения Из Диапазона Ячеек В Excel

Уникальный Мастер Класс Мгновенный Анализ Данных С Помощью Excel Олег Видякин

Умные Таблицы В Microsoft Excel

Использование Функции Впр В Ms Excel 2007

Сортировка Данных В Excel

Excel 2010 Консолидация Табличных Данных

Полный Курс По Функции Впр В Excel Часть 1

Сводные Таблицы В Excel

Функция Если В Ms Excel Видео Урок

Сводная Таблица И Срезы В Microsoft Excel 2010

4 Excel Формулы Avi

Microsoft Excel 2007 Привязка Данных

Excel 2010 Работа С Таблицами

Использование Функций Счетесли И Суммесли В Excel

Создание Сводных Таблиц В Ms Excel 2007

Секреты Excel 2007 Формулы Перевёртыши

Запись Макроса В Microsoft Office Excel 2010 27 50

Сводная Таблица В Excel 2010 Урок 1 Уц Бейсик Wmv

Учебный центр Бейсик

Сводные Таблицы В Excel 2010

Ms Excel Группировка Данных В Сводной Таблице

Как Посчитать Количество Ячеек Значений В Excel

Ячейки В Excel Автозаполнение

Урок 2 Использование Функций В Формулах Excel

Excel Урок 5 Создание Списков Автозаполнения

Создание Печатной Подложки В Excel

Как Сделать Таблицу В Excel Уроки Excel

Nashi100: Интересное видео

Урок 3 Распределение По Группам

Работа С Формулами В Microsoft Excel 2010 4 6

Сравнение Таблиц В Excel

Урок По Макросам В Excel

Cute Songs To Help You Relax And Stay Calm

Шарлотта Опенинг На Русском

Мастер Класс Консолидация Данных В Excel 2010

Halloween 2018 The Musical Animated Parody Song

Dota 2 Live Og Seed Vs Nemiga Gaming Bo3 Vulkan Fight Series Upper Bracket R1

В Последний Раз Витольд Петровский Новая Песня 2020 Онлайн Премьера

Вот Это Песни Послушайте За Душу Берут

Рыцарь Вампир Опенинг 1 На Русском

One Punch Man In Vainglory Ardan Vainglory 5V5

Кто Хочет Стать Миллионером Выпуск От 26 05 18

Митинг В России City 17

Dmitry Zharkov Olga Kulikova Final Tango

Best Roll In Peace Cover

Artem Anistratov Sponsorme 2017

Играем Турнир Фейсит Прем Своей Командой Cs Go

Наруто Кровавый Лёд Альтернативный Сюжет Наруто Наруто Альтернативный Сюжет Наруто 2

Гт Удобный И Простой Съемник Своими Руками Для Демонтажа Сальников Клапанов

Speed Me Up Sonic The Hedgehog 2020

С Юбилеем 55 Лет Футаж Для Видеомонтажа Hd Скачать Бесплатно

Iso Virabyan Feat Tabi Chem Karox Исо Вирабян Feat Таби Не Могу

Самая Окупная Рулетка Namalsk Rp

Тарн И Тайп Ты Океан Что Прямо Внутри История Тарна И Тайпа Клип

Лучший Зажим На Sig Mpx Sbr Custom Макрос Warface

Признаки Последнего Времени Covid 19 Богдан Бондаренко

Alastor Hello Neighbor Song Get Out Hazbinhotel

Венеция Жизнь Других Выпуск От 17 02 2019

Duma Television Legendary Television Regency Television Mgm Ua Television

Robo Kitty Meme Flipaclip

Решил Проверить Чердак Брошенного Дома Заброшенный Дом

Аксенова Вера Странное Чувство

Pretty Lights One Day They Ll Know Odesza Remix A Color Map Of The Sun Remixes

Треки Монстры Топ5 Самых Легендарных И Грандиозных Работ Цифея Собраны Лучшие Треки Для Души

Kaname X Yuki Vampire Knight Amv

Мерлия Саммерс Покоряет Волны Барби Русалочка Barbie Россия 3

Vainglory Wtf Auto Disconnect Is Real Check At The End Of Video

Yung Beef Soto Asa Driftin Audio Oficial

Канаме И Юки Королевы И Короли

Gussie P Ft I Natural Uk The Father Is Calling Shaka Dub I I Bunker A Dam 18 08 2007

Абдул Кадир Красивое Чтение Корана

Аниме Клип Ужасы Дин Дон Я Пришла

Медитация На Привлечение Любимого Пояс Афродиты

СВЯЗЬ МЕЖДУ ФАЙЛАМИ И КОНСОЛИДАЦИЯ ДАННЫХ В MS EXCEL

150.000₽ призовой фонд • 11 почетных документов • Свидетельство публикации в СМИ

Практическая работа 13

Тема: СВЯЗЬ МЕЖДУ ФАЙЛОМИ И КОНСОЛИДАЦИЯ ДАННЫХ В MS EXCEL

Цель занятия. Изучение технологии связей между файлами и консолидации данных в MS EXCEL

Задание 13.1.Задать связи между файлами

1.запустить редактор электронных таблиц Microsoft Excel и создать новую электронную таблицу.

2.Создать таблицу«Отчет о продаже 1 квартала» по образцу рис.Введите исходные данные(Доходы и Расходы):

и проведите расчет Прибыли: Прибыль = Доходы- Расходы. Сохраните файл под именем «1 квартал»

3.Создайте таблицу «Отчет о продаже 2 квартала» по образцу рис. 13.1 в виде нового файла. Для этого создайте новый документ (Файл/Создать) И скопируйте таблицу отчета о продаже за первый квартал, после чего исправьте заголовок таблицы и измените исходные данные:

Обратите внимание, как изменился расчет Прибыли. Сохраните файл под именем «2 квартал».

4.Создайте таблицу «Отчет о продажах за полугодие» по образцу рис.13.1 в виде нового файла. Для этого создайте новый документ (Файл/Создать) и скопируйте таблицу отчета о продаже за первый квартал, после чего подправьте заголовок таблицы и колонке B удалите все значения исходных данных и результаты расчетов. Сохраните файл под именем «Полугодие»

5.Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» «2 квартал».

Краткая справка. Для связи формулами файлов Excel Выполните действия:

·откройте эти файлы (все три файла)

·начните ввод формулы в файле- клиента (в файле «Полугодие»введите формулу для расчета «доход за полугодие»

Формула для расчета:

Доход за полугодие = доход за 1 квартал + Доход за 2 квартал.

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

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

В ячейке B 3 файла «Полугодие» формула для расчета полугодового дохода имеет следующий вид:

=’[1 квартал. xls ] Лист !’$ B $3+’[2 квартал. xls ] Лист!’$ B $3.

Аналогично рассчитайте полугодовые значения Расходов и Прибыли, используя данные файлов «1 квартал» и «2 квартал».Результатом работы представлены на рис. 13.1.Сохраните текущее результаты расчетов.

Примечание. Если файл-источник данных закрыт, в формуле которое на него ссылается будет указан весь путь для этого файла.

13.2.Обновить связи между файлами

1.Закройте файлы «Полугодие» предыдущего задания

2. Измените значения «Доходы» в файлах первого и второго квартала, увеличив их на 100 руб.

Доходы 1квартала=334,58 р.

Сохраните изменения и закройте их.

3.Откройте файл «Полугодие».Одновременно с открытие с файлом появится окно с предложением обновить связи(рис.13.2.), Для обновления связей нажмите кнопку Да. Проследите, как изменились данные файла «Полугодие»(величина «Доходы» должна увеличиться на 200 руб. и принять значения 887,18 р.).

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

4.Изучим процесс ручного обновления связи. Сохраните файл «Полугодие» и закройте его.

5.Вновь откройте файл первого и второго кварталов и измените исходные данные Доходы, увеличив значение на 100 р.

Доходы 1квартала=434,58 р.

Сохраните изменения и закройте их.

6.Откройте файл «Полугодие». одновременно с открытием файла появится окно с предложением обновить связи, нажмите кнопку Нет. Для ручного обновления связи в меню Правка выберете команду Связи, появиться окно, как на рис. 13.3. В окне перечислены все файлы, данные из которых используются в активном файле «Полугодие».

Расположите его так, что бы были видны данные файла «Полугодие», выберете файл «1 квартал» и нажмите кнопку Обновить и проследить, как изменились данные файлы Полугодие.

Примечание. При изменении данных в нескольких исходных файла обновление связи производится для каждого файла.

Задание 13.3 Консолидация данных для подведения итогов по таблицам данных сходной структуры.

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

1. Откройте все три файла задания 13.2 и в файле Полугодие в колонке В удалите все численные значения данных. Установите курсор в ячейке В3.

2. Выполните команду Данные/Консолидация (рис 13.4 ). В появившемся окне Консолидация выберите функцию- Сумма.

В строке Ссылка сначала выделите в файле 1 квартал диапазон ячеек В3/В5 и опять нажмите на кнопку Добавить( см. рис. 13.4 ). В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации.

Читайте также:  Методы сравнения таблиц в Microsoft Excel

Далее нажмите ОК, произойдёт консолидированное суммирование данных за первый и второй кварталы.

Вид таблиц после консолидации данных приведён рис 13.5

Задание 13.4. Консолидация данных для подведения итогов по таблицам неоднородной структуры.

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. Наберите отчёт по отделам за третий квартал по образцу ( рис . 13.6). Произведите расчёты и сохраните файл с именем 3 квартал

2. Создайте новую электронную книгу. Наберите отчёт по отделам за четвертый квартал по образцу (13.7). Произведите расчёты и сохраните файл с именем 4 квартал

3. Создайте новую электронную книгу. Наберите название таблицы Полугодовой отчёт о продажах по отделам. Установите курсор на ячейке А3 и проведите консолидацию за третий и четвертый кварталы по заголовкам таблиц. Для этого выполните команду Данные/Консолидация. В появившемся окне консолидации данных сделайте ссылки на диапазон ячеек А3/Е6 файла 3 квартал и А3/ D 6 файла 4 квартал (рис. 13.8). Обратите внимание, что интервал ячеек включает имена столбцов и строк таблицы.

В окне Консолидация активизируйте опции( поставьте галочку):

*подписи верхней строки

*значения левого столбца

*создавать связи с исходными данным (результаты будут не константами, а формулами).

После нажатия кнопки ОК произойдёт консолидация (рис. 13.9).

Сохраните все файлы в папке вашей группы.

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

О консолидации данных

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

Консолидировать данные в Microsoft Excel можно несколькими способами. Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.

Использование трехмерных формул

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

Используйте формулы для объединения данных. В приведенном ниже примере формула в ячейке A2 складывает три числа, расположенные в различных местах трех разных листов.

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

Консолидация по расположению

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

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

Консолидация по категории

Консолидацию по категории следует использовать в случае, если требуется обобщить набор листов, имеющих одинаковые заголовки рядов и столбцов, но различную организацию данных. Этот способ позволяет консолидировать данные с одинаковыми заголовками со всех листов.

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

Другие способы объединения данных

Можно создать отчет сводной таблицы из нескольких диапазонов консолидации. Данный метод сходен с консолидацией по категории, однако обладает большей гибкостью в отношении реорганизации категорий.

Если данные вводятся в несколько листов-форм, основанных на одном шаблоне, и при этом требуется объединить данные из форм на отдельном листе, следует воспользоваться мастером шаблонов с функцией автоматического сбора данных.

Консолидация данных

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

Формулы. Трехмерные ссылки можно использовать в формулах для любого типа и расположения данных. Рекомендуется пользоваться этим способом.

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

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

Выполните одно из следующих действий.

Консолидируйте данные с использованием трехмерных ссылок или формул

На листе консолидации скопируйте или задайте надписи для данных консолидации.

Укажите ячейку, в которую следует поместить данные консолидации.

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

Например, чтобы объединить данные в ячейке B3 листов с Лист2 по Лист7, введите =СУММ(Лист2:Лист7!B3). Если данные, которые требуется объединить, находятся в разных ячейках на разных листах, введите формулу в формате=СУММ(Лист3!B4; Лист4!A7; Лист5!C5). Чтобы ввести ссылку (например Лист3!B4), не используя клавиши на клавиатуре, введите формулу до того места, где требуется вставить ссылку, а затем укажите на листе нужную ячейку.

Консолидируйте данные по положению или категории

Настройте данные для консолидации.

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

Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.

Если консолидация выполняется по положению, убедитесь, что макеты всех диапазонов совпадают.

Если консолидация выполняется по категории, убедитесь, что подписи столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв.

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

Щелкните левый верхний угол области, в которой требуется разместить консолидированные данные.

В меню Данные выберите команду Консолидация.

Выберите из раскрывающегося списка Функция весовую функцию, которую требуется использовать для консолидации данных.

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

Если таблицу консолидации требуется обновлять автоматически при каждом изменении данных в каком-либо исходном диапазоне, и позже точно не потребуется изменять или добавлять диапазоны исходных данных для консолидации, установите флажок Создавать связи с исходными данными.

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

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

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

Изменение итоговой таблицы консолидации данных

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

Если вы использовали команду Консолидация в меню Данные, можно изменить консолидацию, выполнив следующие действия.

    Щелкните верхнюю левую ячейку консолидированных данных.

    В меню Данные выберите команду Консолидация.

    Консолидацию изменить невозможно, если был установлен флажок Создавать связи с исходными данными. Если флажок установлен, нажмите кнопку Закрыть и создайте новую консолидацию.

    Выполните одно или несколько следующих действий.

    Добавьте диапазон данных в консолидируемые данные

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

    Щелкните поле Ссылка.

    Для консолидации щелкните вкладку листа первого диапазона.

    Введите имя диапазона или выберите диапазон из списка.

    Нажмите кнопку Добавить.

    Измените размер или формы диапазона данных

    В списке Список диапазонов щелкните диапазон данных, который требуется изменить.

    Внесите изменения в выбранную область в поле Ссылка.

    Нажмите кнопку Добавить.

    Удалите диапазон данных и консолидации

    В списке Список диапазонов щелкните диапазон данных, который требуется изменить.

    Нажмите кнопку Удалить.

    Автоматически обновите консолидацию

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

    Установите флажок Создавать связи с исходными данными.

    Чтобы сохранить изменения, нажмите кнопку OK.

    Итоговые функции для анализа данных

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

    Ссылка на основную публикацию
    ×
    ×
    Adblock
    detector