Как преобразовать таблицу в обычный диапазон в excel

Как преобразовать сводную таблицу в таблицу в Excel

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

Шаг 1: введите данные

Во-первых, давайте введем следующие данные о продажах для трех разных магазинов:

Шаг 2: Создайте сводную таблицу

Чтобы создать сводную таблицу, щелкните вкладку « Вставка » на верхней ленте, а затем щелкните значок «Сводная таблица»:

В появившемся новом окне выберите A1: C16 в качестве диапазона и поместите сводную таблицу в ячейку E1 существующего рабочего листа:

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

Перетащите поле « Магазин » в поле « Строки », затем перетащите поле « Продукт » в поле « Столбцы », затем перетащите поле « Количество » в поле « Значения »:

Сводная таблица будет автоматически заполнена следующими значениями:

Шаг 3. Преобразование сводной таблицы в таблицу

Чтобы преобразовать эту сводную таблицу в обычную таблицу данных, просто выберите всю сводную таблицу (в данном случае мы выбираем диапазон E1:I6 ) и нажмите Ctrl+C , чтобы скопировать данные.

Затем щелкните правой кнопкой мыши ячейку, в которую вы хотите вставить данные (мы выберем ячейку E8 ), и выберите параметр « Вставить значения »:

Значения из сводной таблицы будут автоматически вставлены как обычные значения данных, начиная с ячейки E8 :

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

У нас просто осталась таблица обычных значений данных.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Диапазоны

Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.

Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft Excel разными способами: формулами, в сводной таблице, в запросе Power Query.

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

Подробный разбор новой функции ПРОСМОТРX (XLOOKUP), которая приходит на смену классической ВПР (VLOOKUP).

Подробный разбор революционно нового инструмента Excel — динамических массивов (Dynamic Arrays). Логика их работы, нюансы, плюсы и минусы, совместимость со старыми версиями.

Как быстро собрать данные со всех (или только с нужных) листов книги в одну большую таблицу — готовым макросом или с помощью Power Query.

Как сравнить между собой две таблицы (прайс-листа) и наглядно отобразить отличия между ними — добавленные и удаленные товары, изменение цены и т.д. Разбор 3 способов: с помощью функции ВПР (VLOOKUP), сводной таблицей и через надстройку Power Query.

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

Как подсчитать максимальное количество идущих подряд значений, удовлетворяющих заданному условию? Например, самое большое количество рабочих дней подряд в табеле? Или самую длинную победную серию в спорте?

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

Подробный разбор на примерах нюансов и особенностей функции ДВССЫЛ (INDIRECT), позволяющей реализовать косвенные текстовые ссылки.

Как использовать одну из малоизвестных, но крайне полезных функций БДСУММ (DSUM) для выборочного суммирования по одному или нескольким сложным условиям (еще и связанным между собой И-ИЛИ).

Как с помощью функции ВЫБОР (CHOOSE) реализовать выборку элементов из набора, работу с массивами, динамические итоги и склейку диапазонов.

Для чего (на самом деле!) нужна функция СУММПРОИЗВ (SUMPRODUCT) и какие замечательные вещи она умеет делать (включая выборочный подсчет из ЗАКРЫТОГО файла).

Как использовать функцию ПОИСКПОЗ (MATCH) для поиска позиции нужного элемента в списке, первой или последней текстовой ячейки или ячеек с заданным значением в диапазоне.

Как реализовать в Excel аналог функции ВПР (VLOOKUP), который будет выдавать значения левее поискового столбца.

Подробный разбор всех пяти вариантов применения мегамощной функции ИНДЕКС (INDEX): от простого поиска данных в столбце, до двумерного поиска в нескольких таблицах и создания авторастягивающихся диапазонов.

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

Как найти значение последней непустой ячейки в строке или столбце таблицы Excel с помощью функции ПРОСМОТР (LOOKUP).

Как найти в диапазоне чисел минимальное или максимальное значение по условию с помощью функций МИНЕСЛИ и МАКСЕСЛИ, с помощью формул массива, функции ДМИН или сводной таблицы.

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

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

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

Как при помощи функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ просуммировать (а также вычислить среднее, минимум, максимум, количество и т.д.) только видимых ячеек оставшихся после применения фильтра, скрытия строк/столбцов, сворачивания группировкой, а также проигнорировать ячейки с ошибками при расчетах.

Как использовать массивы констант и имена, чтобы встроить поисковые таблицы прямо внутрь функций ВПР, ИНДЕКС, ПОИСКПОЗ и им подобных, чтобы подставлять данные на лету и без использования отдельных таблиц.

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

Несколько способов произвести вычисления (просуммировать, выбрать максимум/минимум, посчитать среднее арифметическое) с каждой 2-ой, 3-ей. n-ой ячейкой в заданном диапазоне, т.е. например сложить каждую четную ячейку или ячейки через одну.

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

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

Как копировать и вставлять данные только в отобранные Фильтром строки, игнорируя скрытые.

Несколько способов решить одну из наиболее распространенных задач при работе в Microsoft Excel — вычислить итоги (сумму, среднее, количество и т.д.) только для тех строк в таблице, которые удовлетворяют заданному условию или набору из нескольких условий.

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

Быстрая и удобная альтернатива стандартному Автофильтру с помощью небольшого макроса. Умеет искать по маске и одному/двум сложным условиям (с И и ИЛИ). Без мыши. В реальном времени.

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

Как быстро объединить данные из двух взаимно не пересекающихся колонок с пустыми ячейками. Без формул, макросов и т.д. — только с помощью мыши.

Как проверить попадание параметра в один из заданных диапазонов и, при этом, обойтись без громоздких вложенных проверок с помощью кучи функций ЕСЛИ (IF)?

Одна из самых мощных и красивых функций в Excel — функция ВПР (VLOOKUP), позволяющая автоматически находить нужные значения в таблице. О том, как ее правильно использовать — просто и понятно.

Как отсортировать таблицу по цвету заливки ячеек или по цвету шрифта. Синие — к синим, красные — к красным и т.д.

Встроенная функция ВПР (VLOOKUP) имеет один существенный недостаток — ищет только первое вхождение искомого значения в таблицу и только в крайнем левом столбце. А если нужно найти 2-е, 3-е и не в крайнем левом?

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

Как случайным образом выбрать из большого массива данных заданное количество элементов?

О двух ближайших «родственниках» функции ВПР (VLOOKUP) — функциях поиска и выборки ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), позволяющих выбирать данные из списка по заданным условиям и реализовать «левый ВПР».

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

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

Как преобразовать горизонтальный диапазон ячеек в вертикальный и обратно.

В чем разница между относительными, смешанными и абсолютными ссылками на ячейки в формулах? Как сделать ссылку на ячейку, которая не будет изменяться ни при копировании исходной ячейки, ни при вставке перед ней новых строк/столбцов?

Как извлечь ВСЕ данные из таблицы по заданному критерию. Функция ВПР (VLOOKUP) находит значение только по первому совпадению, а нужно вытащить все. Поможет хитрая формула массива!

Несколько способов отсортировать числовой или текстовый список не через меню Данные — Сортировка, а с помощью формул «на лету».

Как при помощи формулы массива сделать аналог функции ВПР (VLOOKUP), который при поиске будет учитывать регистр и различать строчные и прописные символы.

Как искать и выбирать нужные данные из двумерной таблицы, т.е. производить выборку не по одному параметру (как функции ВПР или ГПР), а по двум сразу.

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

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

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

Таблицу преобразовать в диапазон excel

Всем доброго времени суток.
Как преобразовать таблицу на листе в диапазон с помощью макроса.
Пробовал записать через макрокодер, но он окромя выделения ячейки не чего не пишет.
Что в итоге: я перешел на страницу запустил макрос, он выделил таблицу (одна на странице) и преобразовал её в диапазон.
пример.xlsx

Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Как с помощью функций преобразовать текстовый файл в excel-таблицу?
Как можно внутри матлаб с помощью функций преобразовать текстовый файл в excel-таблицу?

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

Диапазон [0; 1024] преобразовать в [-60; 60]
Пользуюсь processing. Принимаю сигнал по АЦП, значение от 0 до 1024. Собственно надо вывести.

Преобразовать диапазон из текста в числа
Такая ситуация, есть диапазон C2:D2 который сохранён как текст, в ячейке B2 вставляю 1, копирую B2.

4133 / 2237 / 940
Регистрация: 01.12.2010
Сообщений: 4,624

Пример преобразования всех умных таблиц :

1 2 3 4 5 6

Private Sub Test() Dim t As ListObject For Each t In ActiveSheet.ListObjects t.Unlist Next End Sub

Регистрация: 29.10.2013
Сообщений: 178
pashulka, не работает.
Вот так работает.

1 2 3 4 5 6 7 8 9

Sub UnListObj() Dim Sh As Worksheet Dim iObj As ListObject For Each Sh In Worksheets For Each iObj In Sh.ListObjects iObj.Unlist Next Next End Sub

6919 / 2829 / 543
Регистрация: 19.10.2012
Сообщений: 8,645

Николай, у Вас хаос!
Как может не работать первый код, если работает точно такой же второй?
Притом что если внимательно читать вопрос — то именно второй работает не так, как требовалось по задаче.

Регистрация: 29.10.2013
Сообщений: 178

Цитата Сообщение от Hugo121 Посмотреть сообщение

Как может не работать первый код

а хрен его знает.
Создал кнопку. Назначил на нее фильтр. Ваш код не срабатывает, код указанный другим участником (который привел я) работает. Я в кодах не силен.

4133 / 2237 / 940
Регистрация: 01.12.2010
Сообщений: 4,624
И правда хаос, ещё и с фильтром
Регистрация: 29.10.2013
Сообщений: 178

Цитата Сообщение от NikolayHAOS Посмотреть сообщение

Назначил на нее фильтр.
Естественно назначил макрос. Ошибся.
4133 / 2237 / 940
Регистрация: 01.12.2010
Сообщений: 4,624

Это не единственная ошибка. Например, Вы могли бы прочитать офисную справку, касаемо метода Unlist и понять, что именно он и преобразует «умную» таблицу в диапазон, но .

Регистрация: 29.10.2013
Сообщений: 178

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

Sub Test() ' тест Sheets("Микроучасток").Select Call Макрос1 Sheets("Микроучасток (2)").Select Call Макрос1 Sheets("Микроучасток (3)").Select Call Макрос1 Sheets("Микроучасток (4)").Select Call Макрос1 Sheets("Микроучасток (5)").Select Call Макрос1 Sheets("Микроучасток (6)").Select Call Макрос1 Sheets("Микроучасток (7)").Select Call Макрос1 Sheets("Микроучасток (8)").Select Call Макрос1 Sheets("Микроучасток (9)").Select Call Макрос1 Sheets("Микроучасток (10)").Select Call Макрос1 Sheets("Микроучасток (11)").Select Call Макрос1 Sheets("Микроучасток (12)").Select Call Макрос1 Sheets("Микроучасток (13)").Select Call Макрос1 Sheets("Микроучасток (14)").Select Call Макрос1 Sheets("Микроучасток (15)").Select Call Макрос1 Sheets("Микроучасток (16)").Select Call Макрос1 Sheets("Микроучасток (17)").Select Call Макрос1 Sheets("Микроучасток (18)").Select Call Макрос1 End Sub

Как заставить макрос последовательно перебирать листы, и выполнять на них макрос1?
В смысле как оптимизировать код.
Как не пытаюсь не могу сделать.