Функция транспонирования в excel

Специальная вставка

Первый способ, который поможет преобразовать строки в столбцы, это использование специальной вставки.

Возьмем следующий пример. Здесь данные размещены в диапазоне B2:D7 . Сделаем так, чтобы шапка была записана по строкам. Выделяем соответствующие ячейки и копируем их, нажав комбинацию «Ctrl+C» .

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

В следующем окне поставьте галочку в поле «Транспонировать» и нажмите «ОК» .

Теперь заголовки, которые были записаны в один рядок, отображаются в одном столбце. Если на листе в Экселе у Вас набрано много данных, можно сделать так, чтобы при пролистывании всегда была видна шапка (заголовки столбцов) и первая строка. Подробно ознакомиться с данным вопросом, можно в статье: как закрепить область в Excel.

Для того чтобы поменять строки со столбцами в таблице Excel, выделите весь необходимый диапазон. В примере это B2:D7. Затем нажмите «Ctrl+C» . Дальше выделите необходимую ячейку для новой таблицы и кликните по ней правой кнопкой мыши. Выберите из меню «Специальная вставка» , а затем поставьте галочку в пункте «Транспонировать» .

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

Перевернуть столбец или строку при помощи кнопки «Транспонировать» в Excel.

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

Транспонировать (англ. Transpose) — переставлять, перемещать.

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

Список овощей расположен вертикально:

Овощи

Томаты;

Свекла;

Огурцы;

Репа;

Редька черная;

Редька белая.

Excel: как «перевернуть»… таблицу

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

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

При обработке экономических или бухгалтерских отчетов такая задача возникает сплошь и рядом!

Обычными формулами или копированием ячеек это делать очень неудобно.

Возникает вопрос: что можно предпринять в подобной ситуации? Но для начала определимся с терминологией.

Операция, в которой строки и столбцы таблицы меняются местами, называется транспонированием.

В Excel (любой версии!) такую операцию можно сделать, как минимум, двумя способами.

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

Способ 1. Транспонирование данных через специальную вставку.

1) Выделите участок таблицы, которую хотите транспонировать. 2) Скопируйте данные в буфер (комбинации «Ctrl+C» или «Ctrl+Ins»). 3) Поставьте указатель активной ячейки в начало блока, где должна находиться транспонированная таблица. 4) В программе Excel 2003 вызовите «Правка Специальная вставка» (в MS Excel 2010-2016 щелкните левой кнопкой (ЛКМ) по небольшому треугольнику под иконкой «Вставить»). Откроется окно параметров специальной вставки. 5) В этом окне включите флажок «Транспонировать». 6) Нажмите «ОК».

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

Заметьте, что содержимое транспонированной таблицы не изменилось!

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

После специальной вставки обе таблицы будут автономны

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

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

Для устранения этого недостатка можно задействовать другой метод решения задачи, – применить формулу-массив. Вот как это сделать.

Способ 2. Транспонирование данных через формулу массив.

1) На свободном участке листа выделите диапазон ячеек для будущей транспонированной таблицы. 2) Не снимая выделения (!) в первую ячейку диапазона запишите формулу «=Трансп(блок)». В качестве параметра «блок» введите диапазон исходной таблицы. Указать диапазон можно, выделив его прямо на рабочем листе. 3) Когда формула готова, нажмите «Ctrl+Shift+Enter»

Это важно, т.к. нам нужно не обычное выражение, а формула-массив

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

Откорректируйте исходные данные

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

Все изменения появятся в транспонированной таблице.

Важно! Транспонирование таблиц через функцию «=Трансп()» устанавливает связь между источником данных и результатом. В завершение темы пара замечаний по второму способу

В завершение темы пара замечаний по второму способу

В завершение темы пара замечаний по второму способу.

1) Вставить функцию «=Трансп()» вы можете с помощью Мастера функций, выбирать адреса ячеек можно прямо по рабочему листу. Но есть одна тонкость. После того, как в окне работы с Мастером формула готова, не нажимайте кнопку «ОК» – иначе вы получите обычную формулу, а она для решения задачи не годится. Не закрывая окно Мастера, нажмите комбинацию «Ctrl+Shift+Enter», программа MS Excel вставит на рабочий лист формулу-массив. 2) Диапазон ячеек для транспонированной таблицы желательно выбрать с учетом размеров исходной таблицы. Например, если исходные данные занимали 4 строки и 3 колонки, то диапазон для транспонированной таблицы должен занимать 3 колонки и 4 строки. Если это требование не соблюдать, а диапазон указать «с запасом», ничего страшного не произойдет. Просто в «лишних» ячейках транспонированной таблицы вы увидите значения «#Н/Д». Это означает, что для соответствующих ячеек функция «=Трансп()» не обнаружила данных.

Важно! Стереть значения «#Н/Д» клавишей «Del» не удастся. Excel запрещает удалять элементы формулы-массива

Чтобы устранить это ограничение, сделайте так

Чтобы устранить это ограничение, сделайте так.

1) Выделите транспонированную таблицу. 2) Скопируйте ее в буфер обмена. 3) Не снимая выделения, вызовите «Правка Специальная вставка» (в MS Excel 2013-2016 щелкните ЛКМ по треугольнику под иконкой «Вставить»). 4) В окне параметров специальной вставки включите флажок «Значения». 5) Нажмите клавишу «ОК».

С этого момента участок рабочего листа с формулой-массивом превратился в обычные значения.

Вы сможете выделить ненужные ячейки и стереть их обычным способом (например, клавишей «Del»).

Примеры функции ТРАНСП для переворачивания таблиц в Excel

​ текстовом поле «Найти»​ переданы данные формата​Выделить исходную таблицу и​ динамически умножаемою транспонированную​(A · B)t =​)​ командой сайта office-guru.ru​Щелкните по ней правой​ столбец,​ исходной таблице, а​);​ диапазона должно совпадать​Пусть дан столбец с​

Примеры использования функции ТРАНСП в Excel

​ то все равно​ макросом, либо (если​ но все таки​ из трех доступных​ необходимо ввести символы​ Имя, функция ТРАНСП​ скопировать все данные​

​ матрицу.​

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

​ в Excel способов​

​ «#*», а поле​ вернет код ошибки​ (Ctrl+С).​

Как переворачивать таблицу в Excel вертикально и горизонтально

​ матриц см. статью Умножение​ квадратная, например, 2​Перевел: Антон Андронов​ затем выберите пункт​– это третий​ – с количеством​ обмена (​ исходного диапазона, а​B2:B6​тем более, что​ один раз формулы​ последовательность действий с​ транспонирования диапазонов данных.​ «Заменить на» оставить​ #ИМЯ?. Числовые, текстовые​

​Установить курсор в ячейку,​

​(в этих ячейках​

​ в транспонированных формулах​ вручную.​

​ этой функцией. Что​

​ Остальные способы будут​ пустым (замена на​ и логические данные,​

Переворот таблицы в Excel без использования функции ТРАНСП

​ которая будет находиться​ создана таблица зарплат​ EXCEL)​ столбца, то для​

​Транспонирование матрицы — это операция​

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

​Функция ТРАНСП в Excel​ получения транспонированной матрицы​

​ над матрицей, при​Включите опцию​ таблицы расположен в​ =ТРАНСП(A1:E5) – т.е.​выделить ячейку ниже таблицы​ строк исходного диапазона.​ или формулы). Сделаем​ т.е. в обычных​: табличка 70×20​ протягивать. Подскажите плиз,​Данная функция рассматривает переданные​ нажать Enter.​ функции ТРАНСП, в​

Особенности использования функции ТРАНСП в Excel

​ углу транспонированной таблицы,​

​ года. В шапке​

​ используется для транспонирования​ нужно выделить диапазон​ которой ее строки​Transpose​ другой ячейке, например​ дать ссылку на​

​ (​

1. ​СОВЕТ:​ из него строку.​ формулах продляя вниз,​попробовал вручную​ или я совсем​ данные в качестве​Поскольку функция ТРАНСП является​ результате ее выполнения​ вызвать контекстное меню​ исходной таблицы отображаются​ (изменения направления отображения)​ из 3 строк​
2. ​ и столбцы меняются​(Транспонировать).​ в​ исходную таблицу;​A8​Транспонирование можно осуществить​ Строка будет той​ все ячейки продляются​решил, что проще​ туплю в конце​ массива. При транспонировании​ формулой массива, при​ будут отображены без​
3. ​ и выбрать пункт​ месяцы, а число​ ячеек из горизонтального​ и 2 столбцов.​ местами. Для этой​Нажмите​J2​Вместо​);​ и обычными формулами:​ же размерности (длины),​ вниз. а в​ сокращу-ка толщину столбцов,​ раб. дня​ массивов типа ключ->значение​ попытке внесения изменений​ изменений.​ «Специальная вставка».​ работников офиса –​ расположения в вертикальное​ В принципе можно​ операции в MS​ОК​, формула немного усложняется:​
4. ​ENTER​в меню Вставить (Главная​ см. статью Транспонирование​ что и столбец​ транспонированных формулах продляя​ чтобы не «мешалась»​Михаил С.​ строка с о​ в любую из​После выполнения функции ТРАНСП​В открывшемся окне установить​

• ​ 6 человек. В​ и наоборот. Функция​ выделить и заведомо​ EXCEL существует специальная​.​=ДВССЫЛ(​нажать​
• ​ / Буфер обмена)​ таблиц.​ (см. Файл примера).​ вправо нужно указать,​Z​: Выделяешь диапазон, куда​ значением «ключ» становится​
• ​ ячеек транспонированной таблицы​ в созданной перевернутой​ флажок напротив надписи​ связи с этим​ ТРАНСП при транспонировании​ больший диапазон, в​ функция ТРАНСП() или​

​Чтобы воспользоваться функцией​

1. ​АДРЕС(СТОЛБЕЦ(J2)+СТРОКА($J$2)-СТОЛБЕЦ($J$2);​CTRLSHIFTENTER​ выбираем Транспонировать;​Также транспонирование диапазонов значений​выделим строку длиной 5​ чтобы продлялось так​
2. ​: А в чем​ нужно траспонировать, затем​ столбцом с этим​ появится диалоговое окно​ таблице некоторые данные​ «Транспонировать» и нажать​ ширина таблицы значительно​ диапазона ячеек или​
3. ​ этом случае лишние​ англ. TRANSPOSE.​TRANSPOSE​СТРОКА(J2)-СТРОКА($J$2)+СТОЛБЕЦ($J$2))​
4. ​.​нажимаем ОК.​ можно осуществить с​

exceltable.com>

Примеры функции ТРАНСП для переворачивания таблиц в Excel

​ текстовом поле «Найти»​ переданы данные формата​Выделить исходную таблицу и​ динамически умножаемою транспонированную​(A · B)t =​)​ командой сайта office-guru.ru​Щелкните по ней правой​ столбец,​ исходной таблице, а​);​ диапазона должно совпадать​Пусть дан столбец с​

Примеры использования функции ТРАНСП в Excel

​ то все равно​ макросом, либо (если​ но все таки​ из трех доступных​ необходимо ввести символы​ Имя, функция ТРАНСП​ скопировать все данные​

​ матрицу.​

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

​ в Excel способов​

​ «#*», а поле​ вернет код ошибки​ (Ctrl+С).​

Как переворачивать таблицу в Excel вертикально и горизонтально

​ матриц см. статью Умножение​ квадратная, например, 2​Перевел: Антон Андронов​ затем выберите пункт​– это третий​ – с количеством​ обмена (​ исходного диапазона, а​B2:B6​тем более, что​ один раз формулы​ последовательность действий с​ транспонирования диапазонов данных.​ «Заменить на» оставить​ #ИМЯ?. Числовые, текстовые​

​Установить курсор в ячейку,​

​(в этих ячейках​

​ в транспонированных формулах​ вручную.​

​ этой функцией. Что​

​ Остальные способы будут​ пустым (замена на​ и логические данные,​

Переворот таблицы в Excel без использования функции ТРАНСП

​ которая будет находиться​ создана таблица зарплат​ EXCEL)​ столбца, то для​

​Транспонирование матрицы — это операция​

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

​Функция ТРАНСП в Excel​ получения транспонированной матрицы​

​ над матрицей, при​Включите опцию​ таблицы расположен в​ =ТРАНСП(A1:E5) – т.е.​выделить ячейку ниже таблицы​ строк исходного диапазона.​ или формулы). Сделаем​ т.е. в обычных​: табличка 70×20​ протягивать. Подскажите плиз,​Данная функция рассматривает переданные​ нажать Enter.​ функции ТРАНСП, в​

Особенности использования функции ТРАНСП в Excel

​ углу транспонированной таблицы,​

​ года. В шапке​

​ используется для транспонирования​ нужно выделить диапазон​ которой ее строки​Transpose​ другой ячейке, например​ дать ссылку на​

​ (​

1. ​СОВЕТ:​ из него строку.​ формулах продляя вниз,​попробовал вручную​ или я совсем​ данные в качестве​Поскольку функция ТРАНСП является​ результате ее выполнения​ вызвать контекстное меню​ исходной таблицы отображаются​ (изменения направления отображения)​ из 3 строк​
2. ​ и столбцы меняются​(Транспонировать).​ в​ исходную таблицу;​A8​Транспонирование можно осуществить​ Строка будет той​ все ячейки продляются​решил, что проще​ туплю в конце​ массива. При транспонировании​ формулой массива, при​ будут отображены без​
3. ​ и выбрать пункт​ месяцы, а число​ ячеек из горизонтального​ и 2 столбцов.​ местами. Для этой​Нажмите​J2​Вместо​);​ и обычными формулами:​ же размерности (длины),​ вниз. а в​ сокращу-ка толщину столбцов,​ раб. дня​ массивов типа ключ->значение​ попытке внесения изменений​ изменений.​ «Специальная вставка».​ работников офиса –​ расположения в вертикальное​ В принципе можно​ операции в MS​ОК​, формула немного усложняется:​
4. ​ENTER​в меню Вставить (Главная​ см. статью Транспонирование​ что и столбец​ транспонированных формулах продляя​ чтобы не «мешалась»​Михаил С.​ строка с о​ в любую из​После выполнения функции ТРАНСП​В открывшемся окне установить​

• ​ 6 человек. В​ и наоборот. Функция​ выделить и заведомо​ EXCEL существует специальная​.​=ДВССЫЛ(​нажать​
• ​ / Буфер обмена)​ таблиц.​ (см. Файл примера).​ вправо нужно указать,​Z​: Выделяешь диапазон, куда​ значением «ключ» становится​
• ​ ячеек транспонированной таблицы​ в созданной перевернутой​ флажок напротив надписи​ связи с этим​ ТРАНСП при транспонировании​ больший диапазон, в​ функция ТРАНСП() или​

​Чтобы воспользоваться функцией​

1. ​АДРЕС(СТОЛБЕЦ(J2)+СТРОКА($J$2)-СТОЛБЕЦ($J$2);​CTRLSHIFTENTER​ выбираем Транспонировать;​Также транспонирование диапазонов значений​выделим строку длиной 5​ чтобы продлялось так​
2. ​: А в чем​ нужно траспонировать, затем​ столбцом с этим​ появится диалоговое окно​ таблице некоторые данные​ «Транспонировать» и нажать​ ширина таблицы значительно​ диапазона ячеек или​
3. ​ этом случае лишние​ англ. TRANSPOSE.​TRANSPOSE​СТРОКА(J2)-СТРОКА($J$2)+СТОЛБЕЦ($J$2))​
4. ​.​нажимаем ОК.​ можно осуществить с​

exceltable.com>

При помощи специальной функции

Сейчас будет рассказано, как объединить столбцы в Excel без потери данных. А производиться это с помощью функции «Сцепить»:

1. Выделите любую пустую ячейку на листе в программе.
2. Кликните по кнопке «Вставить функцию». Расположена она левее от строки формул.
3. Появится окно «Мастер функций». В нем вам необходимо из списка выбрать «Сцепить». После этого нажмите «ОК».
4. Теперь надо ввести аргументы функции. Перед собой вы видите три поля: «Текст1», «Текст2» и «Текст3» и так далее.
5. В поле «Текст1» введите имя первой ячейки.
6. Во второе поле введите имя второй ячейки, расположенной рядом с ней.
7. При желании можете продолжить ввод ячеек, если хотите объединить более двух.
8. Нажмите «ОК».

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

1. Выделите объединенные данные.
2. Установите курсор в нижнем правом углу ячейки.
3. Зажмите ЛКМ и потяните вниз.
4. Все остальные строки также объединились.
5. Выделите полученные результаты.
6. Скопируйте его.
7. Выделите часть таблицы, которую хотите заменить.
8. Вставьте полученные данные.

Этот способ довольно трудоемкий, однако он дает возможность объединить столбцы без потери данных.

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

Первый способ, который поможет преобразовать строки в столбцы, это использование специальной вставки.

Для примера будем рассматривать следующую таблицу, которая размещена на листе Excel в диапазоне B2:D7. Сделаем так, чтобы шапка таблицы была записана по строкам. Выделяем соответствующие ячейки и копируем их, нажав комбинацию «Ctrl+C».

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

В следующем окне поставьте галочку в поле «Транспонировать» и нажмите «ОК».

Шапка таблицы, которая была записана по строкам, теперь записана в столбец. Если на листе в Экселе у Вас размещена большая таблица, можно сделать так, чтобы при пролистывании всегда была видна шапка таблицы (заголовки столбцов) и первая строка. Подробно ознакомиться с данным вопросом, можно в статье: как закрепить область в Excel.

Для того чтобы поменять строки со столбцами в таблице Excel, выделите весь диапазон ячеек нужной таблицы: B2:D7, и нажмите «Ctrl+C». Затем выделите необходимую ячейку для новой таблицы и кликните по ней правой кнопкой мыши. Выберите из меню «Специальная вставка», а затем поставьте галочку в пункте «Транспонировать».

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

Второй способ – использование функции ТРАНСП. Для начала выделим диапазон ячеек для новой таблицы. В исходной таблице примера шесть строк и три столбца, значит, выделим три строки и шесть столбцов. Дальше в строке формул напишите: =ТРАНСП(B2:D7), где «B2:D7» – диапазон ячеек исходной таблицы, и нажмите комбинацию клавиш «Ctrl+Shift+Enter».

Таким образом, мы поменяли столбцы и строки местами в таблице Эксель.

Для преобразования строки в столбец, выделим нужный диапазон ячеек. В шапке таблицы 3 столбца, значит, выделим 3 строки. Теперь пишем: =ТРАНСП(B2:D2) и нажимаем «Ctrl+Shift+Enter».

При использовании функции ТРАНСП у транспонированной сохраниться связь с исходной таблицей. То есть, при изменении данных в первой таблице, они тут же отобразятся во второй.

В рассмотренном примере, заменим «Катя1» на «Катя». И допишем ко всем именам по первой букве фамилии

Обратите внимание, изменения вносим в исходную таблицу, которая расположена в диапазоне В2:D7

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

Первый способ. Выделите нужный столбец, нажмите «Ctrl+C», выберите ячейку и кликните по ней правой кнопкой мыши. Из меню выберите «Специальная вставка». В следующем диалоговом окне ставим галочку в поле «Транспонировать».

Чтобы преобразовать данные столбца в строку, используя функцию ТРАНСП, выделите соответствующее количество ячеек, в строке формул напишите: =ТРАНСП(В2:В7) – вместо «В2:В7» Ваш диапазон ячеек. Нажмите «Ctrl+Shift+Enter».

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

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

Самый быстрый способ консолидировать данные в Excel (в одной или нескольких книгах) — использовать встроенную функцию Excel Консолидация.

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

Как вы видите на скриншоте ниже, четыре объединяемых листа имеют схожую структуру данных, но разное количество строк и столбцов:

Чтобы объединить всю эту информацию на одном листе, выполните следующие действия:

1. Правильно расположите исходные данные. Чтобы функция консолидации Excel работала правильно, убедитесь, что:
   • Каждый диапазон (набор данных), который вы хотите объединить, находится на отдельном листе. Не помещайте данные на лист, куда вы планируете выводить консолидированные данные.
   • Каждый лист имеет одинаковый макет, и каждый столбец имеет заголовок и содержит похожие данные.
   • Ни в одном списке нет пустых строк или столбцов.
2. Запустите инструмент «Консолидация». На новом листе, где вы планируете поместить результаты, щелкните верхнюю левую ячейку, начиная с которой должны отображаться консолидированные данные, затем на ленте перейдите на вкладку «Данные» и нажмите кнопку «Консолидация».

Совет. Желательно объединить данные в пустой лист. Если на вашем основном листе уже есть данные, убедитесь, что имеется достаточно места (пустые строки и столбцы) для записи результатов.

1. Настройте параметры консолидации. Появляется диалоговое окно «Консолидация», и вы делаете следующее:
   • В поле «Функция» выберите одну из функций, которую вы хотите использовать для консолидации данных (количество, среднее, максимальное, минимальное и т. д.). В этом примере мы выбираем Сумма.
   • В справочном окне, нажав в поле Ссылка на значок  , выберите диапазон на первом листе. Затем нажмите кнопку «Добавить», чтобы присоединить его к списку диапазонов. Повторите этот шаг для всех листов, которые вы хотите объединить.

Если один или несколько листов находятся в другой книге, используйте кнопку «Обзор», чтобы найти эту книгу и использовать ее.

1. Настройте параметры обновления. В том же диалоговом окне Консолидация выберите любой из следующих параметров:
   • Установите флажки «Подписи верхней строки» и / или «Значения левого столбца» в разделе «Использовать в качестве имён», если вы хотите, чтобы заголовки строк и / или столбцов исходных диапазонов были также скопированы.
   • Установите флажок «Создать связи с исходными данными», если нужно, чтобы консолидированные данные обновлялись автоматически при изменении исходных таблиц. В этом случае Excel создаст ссылки на ваши исходные листы, а также схему, как на следующем скриншоте:

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

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

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

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

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