Использование Итогов и Консолидации в MS-Excel при решении социально-экономических задач

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

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

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

Вычисление промежуточных итогов

Промежуточные итоги. Для вычисления значений промежуточных итогов используется итоговая функция, например СУММ или СРЗНАЧ. Промежуточные итоги могут быть отображены в списке с помощью нескольких типов вычислений одновременно.

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

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

Вложение промежуточных итогов

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

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

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

В поле Операция выберите функцию для вычисления итогов.

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

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

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

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

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

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

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

=СУММ(Продажи!B4;Персонал!F5;Маркетинг!B4)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Инструкции

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дата добавления: 2015-04-23