Через некоторое время вы захотите преобразовать сконструированную сводную таблицу в список в Excel, теперь вы можете прочитать это руководство, чтобы завершить преобразование.
Чтобы преобразовать сводную таблицу в список, вы должны убедиться, что сводная таблица была настроена как ваша потребность. Например, я преобразую приведенную ниже сводную таблицу в список.
1. Нажмите на любую ячейку в сводной таблице и перейдите к дизайн вкладку и нажмите подытоги > Не показывать субтитры .
2. Нажмите Grand Totals > Выкл. Для строк и столбцов под дизайн Вкладка. Смотрите скриншот:
3. Нажмите Макет отчета > Повторить все метки элементов под дизайн Вкладка. См. Снимок экрана:
4. Нажмите Макет отчета снова и нажмите Показать в табличной форме , Смотрите скриншот:
Теперь сводная таблица показана ниже:
5. Нажмите Опционы вкладку (или Анализировать вкладка) и снимите флажок Кнопки и Заголовки полей , который относится к Показать группа.
Теперь сводная таблица, показанная ниже:
5. Теперь я покажу пустые ячейки как ноль. Щелкните любую ячейку сводной таблицы и щелкните правой кнопкой мыши, чтобы выбрать Параметры сводной таблицы , затем в раскрывающемся диалоговом окне, под Макет и формат вкладка, снимите флажок Для пустых ячеек И нажмите OK закрыть Параметры сводной таблицы Диалог. Смотрите скриншот:
Если вы хотите узнать больше о том, как показывать пустые ячейки как ноль, нажмите .
6. Выберите сводную таблицу и нажмите Ctrl + C в то время, чтобы скопировать его, затем поместите курсор на ячейку, в которую вы хотите вставить сводную таблицу в виде списка, и щелкните правой кнопкой мыши, чтобы выбрать Специальная вставка > Значение (V) , Смотрите скриншот:
Внимание : В Excel 2007 вам нужно щелкнуть Главная > макаронные изделия > Вставить значения для вставки сводной таблицы в виде списка.
Теперь вы можете увидеть список, показанный ниже:
Принесите удобные вкладки в Excel и другое программное обеспечение Office, как Chrome, Firefox и новый Internet Explorer.
Сводные таблицы также называют двумерными (2D) таблицами или таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и наглядной матрице с заголовками столбцов и строк. Но такое представление данных не подходят для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в сторонние системы, т.д. Поэтому перед анализом данных так важно аккуратно преобразовать сводные таблицы в «плоский» список.
Надстройка «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:
Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)
Подходит для: Microsoft Excel 2019 - 2007, desktop Office 365 (32-бит и 64-бит).
Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заголовки. Их также можно сделать плоскими с помощью XLTools:
Если в вашей сводной таблице имеются пустые ячейки, то и в соотвествующие ячейки плоского списка также будут пустыми. При этом пустые значения в плоском списке не несут значимой информации для анализа. Поэтому мы рекомендуем следующее:
Внимание : обработка больших таблиц с множеством форматов займёт больше времени.
Редизайн таблицы по сути означает, что данные исходной таблицы копируются и преобразуются так, чтобы сформировать плоский список. Ваши исходные таблицы не изменяются. Чтобы избежать искажения данных, вместо ссылок на ячейки, функций или формул в исходной таблице, в результирующий плоский список надстройка XLTools вставляет их значения.
Термином «Таблица» в Excel часто обозначают разные понятия:
Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.
Появились вопросы или предложения? Оставьте комментарий ниже.Сегодня поговорим про ТАБЛИЦЫ. Не про таблицы, а именно про ТАБЛИЦЫ . Именно так Microsoft предложил называть те замечательные таблицы, о которых пойдёт речь ниже. В зачаточном состоянии они появились в Excel 2003 и назывались там "списками " ("lists"). В Excel 2007 их довели до ума и переименовали в ТАБЛИЦЫ (TABLES), а то что раньше все нормальные люди называли таблицами, теперь предложено называть ДИАПАЗОНОМ (range). В России этот подход не прижился, да и чего ради людям менять задним числом устоявшиеся термины, поэтому TABLES мы будем называть "умными таблицами ", а таблицы в их общеупотребительном понимании оставим в покое.
Надо признать, что термин умные таблицы достаточно хорошо ухватывает суть явления. Действительно, иметь дело в Excel с умными таблицами куда проще и приятнее, чем с обычными. Однако не все о них знают. Давайте восполним этот пробел, потому что умные таблицы воистину изменят вашу жизнь и вы не захотите возвращаться после этого к обычным таблицам.
Те люди, кто много работает в Excel со временем понимают, что, создавая свои таблицы, надо придерживаться определенных жёстких правил.
Соблюдение этих простых правил поможет вам, если не уходить пораньше с работы домой, так хотя бы работать более продуктивно и осмысленно, осваивая действительно интересные и сложные вещи, а не воюя с последствиями своей неаккуратности на каждом шагу.
Так вот к 13-й версии (Excel 2007) его разработчики пригляделись к типовым действиям квалифицированных пользователей Excel и падарили нам функционал умных таблиц, за что им огромное спасибо. Потому что большую часть того, что я только что перечислил умные таблицы либо делают сами автоматически, либо очень сильно облегчают настройку оного.
Итак, давайте познакомимся, как создаются умные таблицы и какими полезными свойствами обладают.
Создать умную таблицу можно буквально за пару кликов мышью. Встаньте на любую ячейку вашей таблицы,
Если до того, как поумнеть, ваша таблица имела форматирование (рамки, цвета букв и фона и т.п.), то возможно вам стоит это форматирование сбросить, чтобы оно не "конфликтовало" с форматированием умной таблицы. Для этого:
При этом все проблемы с форматированием сразу решаются. Однако вам придётся восстанавливать форматы столбцов ячеек: формат даты, времени, нюансы числового формата (типа количества знаков после точки), но это не очень сложно. В любом случае вам решать - сбрасывать форматирование этим способом, либо каким-то другим, менее "разрушительным", но знать о нём надо.
Через меню Форматировать как таблицу вы можете увидеть, как будет выглядеть ваша таблица при приминении любого имеющегося стандартного стиля. Очень удобно и наглядно!
Хоть умная таблица и появляется в Диспетчере имен , но она не полностью равносильна именованному диапазону. Например, не получится напрямую столбец умной таблицы использовать в качестве источника строк для выпадающего списка функции Проверка данных (Data validation). Приходится создавать именованный диапазон, который ссылается уже на умную таблицу, тогда это работает. И в этом случае данный именованный диапазон, в случае добавления новых строк в умную таблицу, та расширяется автоматически, чем я очень люблю пользоваться и вам очень рекомендую.
В Excel 2010 появилась такая полезная функция как срезы . Это наглядные фильтры, которые можно добавлять к сводным таблицам, а также и к умным таблицам тоже. Посмотрим как это работает:
Структурированные формулы оперируют не адресами ячеек и диапазонов, а столбцами умной таблицы, диапазонами столбцов и специальными областями таблиц (типа заголовков, строки итогов, всей областью данных таблицы). Смотрим:
| "Умный" способ адрессации | На что ссылается | Формула возвращает | Стандартный диапазон |
| =СУММ(Результаты) | По умолчанию умная таблица, которая названа "Результаты" ссылается на область своих данных | 87 | B3:E7 |
| =СУММ(Результаты[#Данные]) | Тот же результат вернёт данная формула, где область данных указана в явном виде. | 87 | B3:E7 |
| =СУММ(Результаты[Продажи]) | Суммируем область данных столбца "Продажи". Если надо создать именованный диапазон, который будет ссылаться на столбец умной таблицы, то надо использовать синтаксис Результаты[Продажи]. | 54 | D3:D7 |
| =Результаты[@Прибыль] | Данную формулу мы вводили в строке 3. @ - означает текущую строку, а Прибыль - столбец, из которого возвращаются данные. | 6 | E3 |
| =СУММ(Результаты[Продажи]:Результаты[Прибыль]) | Ссылка на диапазон столбцов: от колонки "Продажи", до колонки "Прибыль" включительно. Обратите внимание на оператор ":", который создаёт диапазон. | 87 | D3:E7 |
| =СУММ(Результаты[@]) | Формулу вводили в троке 3. Она вернула всю строку таблицы. | 11 | B3:E3 |
| =СЧЁТЗ(Результаты[#Заголовки]) | Подсчёт количества элементов в #Заголовки. | 4 | B2:E2 |
| =Результаты[[#Итоги];[Продажи]] | Формула возвращает итоговую строку для столбца Продажи. Это не одно и тоже, что Результаты[Продажи], так как итоговая функция может быть разной, например, средней величиной. | 54 | D8 |
Предупреждение для любителей полазить по иностранным сайтам: товарищи, учитывайте различия в региональных настройках России и западных стран (англоязычных то точно). В региональных настройках есть такой параметр, как "Разделитель элементов списка ". Так вот на Западе это запятая , а у нас точка с запятой . Поэтому, когда они пишут формулы в Excel, то параметры разделяются запятыми , а когда мы пишем, то - точкой с запятой . Тоже самое и в переводных книгах, везде.
