Консолидация данных в Excel. Создание и применение.’
Вначале необходимо разобраться – что же такое консолидация? Это объединение каких-то объектов, параметров, данных в единое целое для достижения общего результата. Консолидация данных в Excel представляет собой объединение данных из одной или нескольких таблиц в единую таблицу для получения общих итогов. При этом параметры, или критерии, по которым вычисляются итоги, должны находится в первом столбце одной или нескольких таблиц, которые консолидируются. Эти критерии будут консолидированы, и по ним в остальных столбцах будет выведен расчет итогов, причем выбранного типа.
Пример выполненной консолидации таблицы по поступлениям
Рисунок 1
Хотелось бы заметить, что в исходной таблице было почти 5 тысяч строк,10 граф. Однако после консолидации все несущественные столбцы были удалены, число же строк после консолидирования данных в Excel по каждой дате поступлений сократилось до 15. Другими словами, вместо большой таблицы мы имеем короткую, сжатую и понятную итоговую информацию
Консолидация по сути очень похожа на инструмент ПРОМЕЖУТОЧНЫЙ ИТОГ, но имеет важные отличия.
- ПРОМЕЖУТОЧНЫЙ ИТОГ может работать только с одной таблицей, а количество таблиц в консолидации может быть до 255
- ПРОМЕЖУТОЧНЫЙ ИТОГ требует обязательной сортировки колонки с критериями, для которых ведется расчет. КОНСОЛИДАЦИЯ сама группирует данные независимо от их расположения в колонке
- ПРОМЕЖУТОЧНЫЙ ИТОГ выводит результат по умолчанию выводит результат только в последней графе, остальные нужно указывать вручную. Консолидация подводит итоги во всех столбцах, кроме первого.
- ПРОМЕЖУТОЧНЫЙ ИТОГ выводи результат непосредственно в таблице, консолидация может использовать данные из любого диапазона.
Нетрудно убедиться, что консолидация имеет много преимуществ, однако за все надо платить. Так и здесь. Прежде всего, позиции, по которым будет идти консолидация, должны находится именно в первом столбце. Иначе вы ничего не получите! Кроме этого, так как в консолидации есть возможность создания связей с исходными данными, то нежелательно располагать таблицу консолидации на одном листе с исходными данными, на практике в этом случае консолидация данных в Excel может привести к проблеме с смещением данных в исходной таблице.
Кроме этого, в исходной таблице не должно быть объединенных ячеек. Не допускаются пустые ячейки, особенно в заголовке таблицы и в ее крайней левой графе. Не допускается так же и наличие структуры в виде группировок или скрытых элементов. Если все это присутствует, то группировку надо убрать, а скрытые колонки и строки показать. Иначе вы рискуете получить мягко говоря, неожиданный результат.
Если консолидируются несколько таблиц, то они могут иметь разное расположение и количество строк, а вот наименования столбцов в заголовке, да и сам заголовок должен быть одинаков. Если исходные таблицы находятся в разных файлах, то или эти файлы должны быть открыты, или нужным таблицам в этих файлах надо присвоить заранее имена. Эти имена могут быть как постоянными, так и динамическими, а вот умные таблицы в этом случае не работают.
Перед созданием консолидации необходимо вначале убедиться, что таблицы соответствуют требованиям. Например, если вы собираете ежемесячный отчет по разным филиалам, то удобнее заранее присвоить имена таблицам отчетов в файлах, предоставляемых филиалами.
Создание консолидации в Excel – процесс несложный и доступный даже новичку. Разберем его на примере консолидирования одной таблицы. Делаем следующее:
- Выбираем на отдельном чистом листе ячейку для начала будущей таблицы консолидации и на вкладке ВСТАВКА нажимаем кнопку КОНСОЛИДАЦИЯ.
Рисунок 2
Если в книге пустого листа нет, его надо создать. Выбираем функцию для вычисления. Выбор довольно большой, есть фактически все необходимые варианты. Присутствует сумма, количество (количество позиций), максимум, минимум, среднее, отклонение и другие варианты для расчета. Надо учесть, что консолидация не умеет вычислять математические функции с ТЕКСТОВЫМИ ДАННЫМИ. По сути, это не умеет делать никакая программа. Так что если вы будете обрабатывать нечисловые данные, то максимум что вы сможете сделать, это подсчитать количество значений.
рисунок 3
После выбора нажимаем кнопку ДОБАВИТЬ, и наша выделенная таблица попадает в список диапазонов. Если что-то выбрано неправильно, то можно нажать кнопку УДАЛИТЬ и повторить процесс заново.
Кроме этого, не забудьте, что после создания таблицы консолидации на этой странице Excel «запомнит» исходные данные. Поэтому вы всегда можете запустить на ней создание консолидации и поменять в частности расчет. Сам адрес исходный диапазон будет сохранен
- Теперь ОБЯЗАТЕЛЬНО ставим внизу ОБА флажка (галочки) под надписью ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН. Если не включить в качестве имен заголовки, у вас будут безымянные колонки. Итог получится примерно такой.
Рисунок 4
Если не включить в качестве имен значения левого столбца, то Excel выведет весь список данных, но без их наименований.
Рисунок 5
Как видно, название колонки есть, наименования самих же товаров отсутствуют. Понятно, что толку от такой таблицы немного.
Флажок же СОЗДАВАТЬ СВЯЗИ поставьте если только это действительно необходимо. К примеру, если вы собираете с помощью консолидации ежемесячный отчет, то вряд ли вам понравится, когда из-за включенной связи данные за март попадут в отчет за январь. С другой стороны, если вы собираете постоянно итоговый отчет на текущий момент, эта галочка будет полезна. С другой стороны, несомненным плюсом создания связей является возможность обновления данных. Достаточно, находясь на нужном листе, на вкладке ДАННЫЕ нажать кнопку ОБНОВИТЬ ВСЕ, и вы получите свежие данные из исходной таблицы. Однако при добавлении данных в новые строки исходной таблицы вам в любом случае придется создавать консолидацию заново. После создания можно удалить ненужные колонки. Например, можно по каждому наименованию получить таблицу консолидации по ценам, но при выборе диапазона захватить также и столбец с количеством единиц товара.
Консолидировать можно не только одну, но и несколько таблиц. При этом каждую из них необходимо последовательно выделять, а затем добавлять в список диапазонов для консолидации. Надо учитывать, что пока не нажата кнопка добавить, при переходе на новый лист Excel автоматически попытается получить адрес диапазона консолидации с текущего листа. Кроме этого, надо следить за выбранным диапазоном, так как по умолчанию будет предложено для выбора количество строк и расположение диапазона, которое совпадает с предыдущим. Обратите внимание на рисунок внизу. Так как на листе проценты всего 41 строка, то при попытке указать данные с листа продажи Excel пытается помочь и предлагает выбрать так же 41 строку, хотя на самом деле в этой таблице строк больше.
Рисунок 6
Кстати, именно из-за этого говорят, что таблицы для консолидации должны иметь абсолютно одинаковый размер и начинаться с одной ячейки. На самом деле это ПОЛНАЯ ЧУШЬ. Таблица для создания таблицы консолидации может начинаться откуда угодно и иметь абсолютно разное количество строк. А вот заголовок у всех таблиц действительно должен быть одинаковым. В следующем примере показан консолидированный годовой отчет по городам Актау, Караганда и Семей. Сразу отмечу, что не во всех городах есть продажи по каждому из наименований за каждый месяц. Тем не менее такая ситуация никак не влияет на создание таблицы консолидации в Excel. Обратите внимание на адреса диапазонов!
Рисунок 7
Кроме консолидации данных из текущей книги вы можете создавать таблицу консолидации и из диапазонов, расположенных в разных файлах. Другими словами, Однако тут есть нюанс. Дело в том, что при запуске создания консолидации вы не сможете иметь доступ к меню программы. Другими словами, вы сможете только перемещаться между уже открытыми фалами и их листам.
Открыть же книгу во время создания консолидации нельзя.
Обойти это ограничение можно двумя способами. Если вам надо получить таблицу консолидации по разным диапазонам из разных файлов, тогда предварительно задайте для этих диапазонов имена. В этом случае вы можете во время создания консолидации нажать кнопку ОБЗОР и, указав в окне проводника нужный файл, дописать после его названия имя диапазона в нем, который нужен для консолидации.
Второй способ заключается в том, что файл должен быть предварительно открыт. Тогда можно выбрать его на панели задач рабочего стола, а затем указать нужную таблицу. В этом случае надо быть особо внимательным, так как когда файлов много, то после добавления диапазона, при попытке выбрать новый диапазон, Excel попытается вернуться в ту же книгу, откуда уже был добавлен диапазон. Обойти это можно удалением содержимого строки ИСТОЧНИК после КАЖДОГО ДОБАВЛЕНИЯ ДИАПАЗОНА.
Рисунок 8
Обратите внимание, что на примере выше первый диапазон был в том же файле, что и создаваемая таблица консолидации.
Второй диапазон был получен из файла Алматы, который предварительно был открыт. Ну а третий был получен из закрытого файла Нур-султан.xlsx по имени диапазона. Для удобства этот диапазон имеет такое же имя, как и сам файл, но из-за ограничений на структуру имен в нем дефис заменен на знак подчеркивания.
Таким образом, мы убедились, что создание таблицы консолидации несложно и действительно очень помогает в объединении итоговых отчетов с разных таблиц в единую таблицу. Однако этот способ имеет и свои недостатки. Например, кто-то жаловался на то, что нет операции разность. Зачем она понадобилась, не знаю, но человек переживал. Кроме этого, все данные собираются вместе. В результате, если необходимо увидеть не только общие итоговые данные, но и данные по каждой позиции отдельно для каждой таблицы, то придется создать отдельные таблицы консолидации по каждому исходному диапазону, присвоит этим таблицам имена, а затем связать их в единое целое с помощью формул. В качестве второго варианта можно создать сводную таблицу по нескольким диапазонам консолидации либо на основе модели данных.
Эти возможности мы рассмотрим на отдельных уроках, а текущее занятие подошло к концу.
Желаю всем успешной работы и хорошего настроения! Если остались вопросы, пишите в комментариях
Консолидация строк графика по запланированным складским заказам
Если запланированные складские заказы используются, то можно консолидировать строки графика продаж по количеству и/или по дате. Консолидация по количеству означает, что несколько строк графика объединяются в один запланированный складской заказ. Заказанное количество запланированного складского заказа является сгруппированным количеством и один и тот же запланированный складской заказ связывается в различными строками графика. Консолидация по дате означает, что отгрузки для изделий графика консолидируются в предопределенные моменты поставок по запланированным складским заказам. Основные данные В сеансе Логистические данные строки контракта на продажу (tdsls3102m000) необходимо указать следующие поля консолидации:
Консолидация потребностей по количеству Консолидация по количеству позволяет объединять множество строк графика для конкретного графика продаж в один запланированный складской заказ. В сеансе Запланированные складские заказы графика продаж (tdsls3520m000) объединяются строки графика в одном запланированном складском заказе, если поля строки графика продаж не одинаковы, за исключением этих полей, которые могут отличаться:
Если строки графика отличаются, например, информацией по цене или единице продаж, то они не могут быть консолидированы. В этом случае может быть создано несколько запланированных складских заказов для одной и той же ссылки отгрузки. Примечание Строки связанных графиков могут быть объединены в одном запланированном складском заказе, только если у них одна и та же ссылка отгрузки. Если флаг Текст стр.графика установлен для строки графика продаж, то эта строка графика никогда не может быть консолидирована по количеству. Для консолидации по количеству применимо следующее:
Пример Для графика SCH0001, первая редакция содержит следующие данные:
Строки графика 10 и 20 консолидируются в один запланированный складской заказ (SCH0001) с заказанным количеством 50. Вторая редакция графика содержит следующие данные:
Из-за изменения точки доставки, эти строки графика больше не могут быть консолидированы по количеству в один запланированный складской заказ. В зависимости от статуса (запланированного) складского заказа, применимо следующее:
Примечание LN всегда выдает сообщение с предупреждением, если новая редакция графика или обновления приводят к непредвиденным (пере-)поставкам. Консолидация потребностей по дате Консолидация по дате позволяет изменять моменты поставок для изделий графика на фиксированные даты поставки в запланированных складских заказах. Во время утверждения графика продаж следующие даты в сеансе Запланированные складские заказы графика продаж (tdsls3520m000) могут быть изменены на один из моментов поставки, указанных в шаблоне:
Таким образом, запланированные складские заказы получают даты, приведенные в соответствие с желаемыми моментами поставки в поле Шаблон поставки сеанса Логистические данные строки контракта на продажу (tdsls3102m000). Опции ручной консолидации Хотя автоматическая консолидация и заметно уменьшает число запланированных складских заказов, Вы можете счесть их число всё еще большим. Для последующего уменьшения или контроля числа запланированных складских заказов Вы можете вручную определить опции консолидации при или после утверждения графика. Для существующих или новых запланированных складских заказов Вы можете консолидировать потребности строк графика для определенной даты начала потребности, включая прошлые потребности, в один запланированный складской заказ. Воспользуйтесь сеансом Утверждение графиков продаж (tdsls3211m000) и сеансом Утверждение заборных листов (tdsls3211m200) для определения опций консолидации при утверждении графика. Воспользуйтесь сеансом Консолидировать запланированные складские заказы (tdsls3220m000) и сеансом Консолидировать запланированные складские заказы (Заборные листы) (tdsls3220m100) для определения опций консолидации после утверждения графика. Вы можете задать следующие опции консолидации:
Пример Шаблон поставки: отгрузка в среду, 7 ноября, в 11:00 утра Сегодня: 1 ноября
В зависимости от ваших настроек может быть создан один запланированный складской заказ с количеством 10, например, на понедельник, 5 ноября, 11:00 утра.
| |||||||||||||||||||||||||||||||||||||||||||||||
информацию при консолидации выбран.
В результате этого можно вмешаться вручную в процесс отгрузки, при необходимости.
Excel Consolidate Data не работает — данные не объединяются
Задавать вопрос
спросил
Изменено 1 год, 9 месяцев назад
Просмотрено 7к раз
По какой-то причине я не могу заставить работать функцию консолидации Excel. Мои данные отказываются объединяться.
Все числа в моей колонке отформатированы как суммы в долларах.
Мой исходный набор данных выглядит так:
__________________ |ИМЯ |ДОЛЛАРЫ | |Эндрю | $10.00| |Майкл | $10.00| |Эмма | $10.00| |Эндрю | $10.00| |Майкл | $10.00| |Эмма | $10.00| --------------------
Но после того, как я запустил его через функцию «Консолидация данных», где я выбрал все данные + заголовки и выбрал «сумму», мои результаты выглядят так:
__________________ |ИМЯ |ДОЛЛАРЫ | | | $10.00| | | $10.00| | | $10.00| | | $10.00| | | $10.00| | | $10.00| --------------------
00|
| | $10.00|
| | $10.00|
| | $10.00|
| | $10.00|
| | $10.00|
--------------------
Понятия не имею, почему он не работает. Я пытался буквально следовать нескольким руководствам, а также искать решения в Интернете, но все, что я пробовал, не сработало.
Это потому, что вы не можете объединять буквы, «суммируя» их. Поэтому он оставляет их пустыми, так как это приведет к ошибке.
Вы должны указать, что первый столбец не должен суммироваться, а должен использоваться как метки строк. Это делается путем галочки
Используйте метки в > левой колонке
Вы уже выбрали Верхний ряд . это вариант ниже:
Это приводит к отсутствию имени столбца для первого столбца, но вы можете легко ввести его позже в целевом диапазоне.
Но У меня есть ощущение, что вы, возможно, не захотите использовать инструмент консолидации. Это для объединения нескольких диапазонов данных в один. Ваш набор данных включает только один диапазон значений, поэтому было бы бессмысленно его объединять.
Подробнее о консолидации можно прочитать здесь: https://support.microsoft.com/en-us/office/consolidate-data-in-multiple-worksheets-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b
2
Зарегистрируйтесь или войдите в систему
Зарегистрируйтесь с помощью Google
Зарегистрироваться через Facebook
Зарегистрируйтесь, используя адрес электронной почты и пароль
Опубликовать как гость
Электронная почта
Требуется, но не отображается
Опубликовать как гость
Электронная почта
Требуется, но не отображается
Excel Consolidate Data не работает — данные не объединяются
По какой-то причине мне не удается заставить работать функцию консолидации Excel.
Мои данные не объединятся.
Мой столбец содержит только числа, структурированные как суммы в долларах.
Мой исходный набор данных выглядит так:
__________________ |ИМЯ |ДОЛЛАРЫ | |Эндрю | $10.00| |Майкл | $10.00| |Эмма | $10.00| |Эндрю | $10.00| |Майкл | $10.00| |Эмма | $10.00| --------------------
Однако, когда я запускаю его через функцию консолидации данных, убедившись, что выбраны все данные, а также заголовки, я получаю следующий вывод:
__________________ |ИМЯ |ДОЛЛАРЫ | | | $10.00| | | $10.00| | | $10.00| | | $10.00| | | $10.00| | | $10.00| -------------------
Не знаю, почему он не работает. Я тщательно пробовал в соответствии с рядом руководств и поиском решений в Интернете, но ничего из того, что я пробовал, не сработало.
17 ноября 2022 г. в других к Китузз • 31 640 баллов • 204 просмотра1 ответ на этот вопрос.
0 голосов
Связанные вопросы в других
Вы используете C# версии 4, .
.. ПОДРОБНЕЕ
ответил 16 октября 2022 г. в других к нариккадан • 57 060 баллов • 540 просмотров
- С#
- первенствовать
Попробуй это: Публичная подписка Mail_Merge() При ошибке GoTo ErrH Дим… ПОДРОБНЕЕ
ответил 6 ноября 2022 г. в других к нариккадан • 57 060 баллов • 258 просмотров
- Excel
- вба
- MS-слово
- мс-офис
- слияние
Теперь я знаю, что лист Excel … ПОДРОБНЕЕ
ответил 12 ноября 2022 г. в других к нариккадан • 57 060 баллов • 77 просмотров
- Excel
- xlsx
Согласно определению здесь: support.microsoft.com/en-us/office/… если вы это сделаете … ПОДРОБНЕЕ
ответил 17 ноября 2022 г. в других к нариккадан • 57 060 баллов • 125 просмотров
- Excel
- excel-формула
Этот код должен войти в систему при условии .
.. ПОДРОБНЕЕ
ответил 5 сентября 2018 г. в блокчейне к копатель • 26 740 баллов • 544 просмотра
- блокчейн
- биткойн
- вба
- первенствовать
- восстановить баланс
Забудьте на время о сложностях PsychoPy… ПОДРОБНЕЕ
ответил 3 октября 2018 г. в Питоне к Приядж • 58 100 баллов • 495 просмотров
- питон
- первенствовать
- питон-2-7
- openpyxl
- психопия
Вот как я это делаю. Дим… ПОДРОБНЕЕ
ответил 15 октября 2018 г. в РПА к Приядж • 58 100 баллов • 3522 просмотра
- первенствовать
- голубая призма
- рпа
попробуй это. позволять TableA = … ПОДРОБНЕЕ
ответил 22 октября 2018 г. в Power BI к Энни97 • 2 160 баллов • 3064 просмотра
- мощность-би
- powerquery
- первенствовать
- список
Каждый метод в API Office.