Какая функция в excel поможет консолидировать бюджет

Функция ОБЪЕДИНИТЬ

Функция ОБЪЕДИНИТЬ объединяет текст из нескольких диапазонов и (или) строк, вставляя между текстовыми значениями указанный разделитель. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны.

Примечание: Эта функция доступна на Windows или Mac, если у вас Office 2019 или если у вас Microsoft 365 подписка. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

Синтаксис

ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст1; [текст2]; . )

Разделитель
(обязательно)

Текстовая строка (пустая или с символами в двойных кавычках) или ссылка на действительную текстовую строку. Введенные числа будут считаться текстом.

В случае значения ИСТИНА игнорирует пустые ячейки.

текст1
(обязательно)

Элемент текста, который нужно присоединить. Текстовая строка или массив строк, например диапазон ячеек.

[текст2, . ]
(необязательно)

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

Например, выражение =ОБЪЕДИНИТЬ(» «;ИСТИНА; «Не»; «слышны»; «в»; «саду»; «даже»; «шорохи.») вернет строку Не слышны в саду даже шорохи.

Примечания

• Если объединенная строка содержит более 32767 символов (ограничение для ячейки), функция ОБЪЕДИНИТЬ вернет ошибку #ЗНАЧ!.

Примеры

Скопируйте данные примеров из приведенных ниже таблиц и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Какая функция в excel поможет консолидировать бюджет

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Консолидация данных с нескольких листов

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

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

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

Способы консолидации данных

Существует два способа консолидации данных: по позиции или категории.

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

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

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

Примечание: Примеры, приведенные в этой статье, были созданы с помощью Excel 2016. Хотя ваше представление может отличаться, если вы используете другую версию Excel, шаги будут одинаковыми.

Как консолидировать

Выполните следующие действия, чтобы объединить несколько листов в master лист:

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

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

Щелкните Data>Консолидация (в группе Data Tools).

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

Выделите данные. Затем в поле Ссылка нажмите кнопку Свернуть , чтобы сжать панель и выбрать данные на листе.

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

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

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

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

Консолидация данных с помощью формул

Если данные для консолидации есть в разных ячейках на разных листах:

Введите формулу со ссылками на ячейки других листов, по одной на каждый лист. Например, чтобы консолидировать данные из листов «Продажи» (в ячейке B4), «Кадры» (в ячейке F5) и «Маркетинг» (в ячейке B9), в ячейке A2 основного листа, введите следующее:

Совет: Ввод ссылки на ячейку, например Sales! B4 — в формуле без ввода введите формулу до точки, в которой требуется ссылка, затем перейдите на вкладку листа и щелкните ячейку. Excel заполтит имя листа и адрес ячейки. ПРИМЕЧАНИЕ. Формулы в таких случаях могут быть подвержены ошибкам, так как очень легко случайно выбрать неправильную ячейку. Также может быть трудно обнаружить ошибку после ввода сложной формулы.

Если данные для консолидации находится в одних и том же ячейках на разных листах:

Введите формулу с трехмерной ссылкой, которая указывает на диапазон имен листов. Например, чтобы объединить данные в ячейках A2 от Sales до Marketing включительно, в ячейке E5 master листа необходимо ввести следующее:

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Какая функция в excel поможет консолидировать бюджет

На этом шаге мы рассмотрим консолидацию рабочих листов.

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

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

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

• Использовать формулы, содержащие внешние ссылки.
• Скопировать данные и воспользоваться диалоговым окном Специальная вставка .
• Воспользоваться командой Данные | Консолидация .
• Воспользоваться сводной таблицей.

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

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

Если рабочие листы, которые Вы консолидируете, находятся в одной рабочей книге и если способы размещения информации в них идентичны, то в этом случае можно воспользоваться стандартными формулами. Например, для вычисления суммы значений из ячеек A1 , находящихся в рабочих листах, начиная с листа Лист2 и заканчивая листом Лист10 , надо ввести формулу: =СУММ(Лист2:Лист10!А1) .

Если консолидация затрагивает другие рабочие книги, то для ее выполнения можно воспользоваться формулами, содержащими внешние ссылки. Например, если нужно сложить значения из ячеек A1 , которые находятся в рабочих листах ( Лист1 ) из двух разных рабочих книг, с именами Регион1 и Регион2 , то можно воспользоваться следующей формулой: =[Регион1.xls]Лист1!А1 + [Регион2.xls]Лист1!А1 .

Формула может включать произвольное число внешних ссылок, при этом число содержащихся в ней символов не должно превышать 1024.

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

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

Использование команды Данные | Консолидация
Этот метод универсален, с его помощью можно выполнить статичную (без формул связи) или динамичную консолидацию (с формулами связи). Команда Данные | Консолидация поддерживает следующие методы консолидации:

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

На рисунке 1 показано диалоговое окно Консолидация , которое содержит следующие элементы управления:

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

• Функция . Определяется тип консолидации ( Сумма, Количество значений, Среднее, Максимум и т.д.)
• Ссылка . Адрес диапазона для консолидации из исходного файла. После того, как Вы введете адрес диапазона, щелкните на кнопке Добавить , чтобы добавить его в список диапазонов. Если Вы выполняете консолидацию по позиции, не включайте в диапазон консолидации заголовок диапазона. А в случае консолидации по категории Вы должны этот заколовок включить.
• Список диапазонов . Список всех ссылок, которые были зафиксированы с помощью кнопки Добавить .
• Использовать в качестве имен . Опции этого раздела позволяют при выполнении консолидации использовать заголовки в верхней строке, левом столбце или обеих позициях. Используйте эти опции для выполнения консолидации по категории.
• Создать связи с исходными данными . При выборе этой опции в зависимом рабочем листе создается структура, которая содержит внешние ссылки на ячейки назначения. Кроме того, структура содержит такжн формулы вычисления итогов. Если не активизировать эту опцию, то при выполнении консолидации формулы со связями создаваться не будут.
• Кнопка Обзор . Служит для вызова диалогового окна, в котором можно выбрать рабочую книгу.
• Кнопка Добавить . Перенос ссылки из поля Ссылка в поле Список диапазонов .
• Кнопка Удалить . Удаление выбранной ссылки из списка диапазонов.

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