Как использовать функцию консолидации в Microsoft Excel

25 марта 2022 г.

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

Зачем использовать функцию консолидации?

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

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

Как использовать функцию консолидации в Excel по позициям

Есть два способа использования функции консолидации в Excel, в том числе консолидация по позиции. Вот как использовать функцию таким образом:

1. Настройте данные в составных листах

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

2. Откройте меню функций консолидации и сводки.

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

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

3. Выберите данные, которые вы хотите консолидировать

Под выбором функции во всплывающем окне есть раздел «Справочник». Если вы выполняете консолидацию из большого диапазона или не знаете конкретных ячеек, вы можете щелкнуть «Свернуть» справа от поля, чтобы свернуть окно и позволить вам вручную обрабатывать ячейки с вашими данными. Выберите «Расширить диалоговое окно» в правой части меню, чтобы снова развернуть меню функций. Если вы знаете свои ячейки, вы можете ввести диапазон вручную в этом поле. Например, если ваши данные начинаются с ячейки A1 и заканчиваются на H23, вы можете ввести в поле «$A$1:$H$23».

Если вы хотите объединить данные из другой книги, вы можете перейти к кнопке «Обзор» в меню. Появятся все рабочие книги, и вы можете найти ту, которую хотите объединить, и щелкнуть ее, чтобы выбрать. Нажмите кнопку «ОК», после чего книга станет доступна в меню «Справочник». Затем вы можете выбрать этот рабочий лист при выборе консолидации.

4. Подтвердите консолидацию

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

Чтобы все ваши данные консолидации оставались актуальными при каждом изменении исходных данных, вы можете выбрать «Создать ссылки на исходные данные» в нижней части меню «Консолидация». Это позволяет программе автоматически обновлять ваш основной рабочий лист каждый раз, когда вы изменяете свои данные, чтобы отражать самую новую и точную информацию.

Другие способы использования функции консолидации в Excel

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

Введите формулу со ссылками на ячейки

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

Например, если вы хотите объединить данные из ячейки B4 на листе под названием «Маркетинг», ячейки F5 в «Финансы» и ячейки B9 в ячейке «Бухгалтерский учет» на основном листе, вы можете написать следующую формулу: = СУММ(Маркетинг!B4,Финансы!F5,Бухгалтерский учет!B9).

Введите формулу с трехмерной ссылкой

Вы также можете консолидировать данные в одних и тех же ячейках на разных листах с помощью функции консолидации. Если данные, которые вы хотите консолидировать, являются частью ряда рабочих листов, вы можете ввести формулу, имеющую трехмерную ссылку. Например, если вы хотите консолидировать данные, просуммировав значения в ячейке A2 из рабочего листа «Маркетинг» через «Бухгалтерский учет» включительно в ячейку E5 на вашем основном листе, вы можете написать следующую формулу: СУММ(Маркетинг: Бухгалтерский учет!E5).

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