Преобразование таблиц в excel

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

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

Создание таблицы

1. Выделить любую ячейку, содержащую данные, которые должны будут войти в таблицу.
2. В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table].

1. Появится диалоговое окно, в котором Excel автоматически предложит границы диапазона данных для таблицы

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

1. ОК.

Присвоение имени таблице

По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя.

1. Выделить ячейку таблицы.
2. На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы нажать клавишу Enter.

Требования к именам таблиц аналогичны требованиям к именованным диапазонам.

Форматирование таблиц

В созданной таблице можно изменить цвет, шрифт, вид границ и прочее. С помощью форматирования можно добиться того, что информация будет легче восприниматься. Эти изменения можно выполнить самостоятельно или выбрать готовый стиль оформления.

1. Выделить ячейку таблицы.
2. На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].

Вычисления в таблицах

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

1. На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].

1. В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию.

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

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

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

Если вместо имени поля на экране в формулах указаны адреса ячеек, необходимо изменить настройку:

1. Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
2. В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
3. OK.

Преобразование таблицы в обычный диапазон

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

1. На вкладке Конструктор [Design] выбрать группу Сервис [Tools].
2. Выбрать вкладку Преобразовать в диапазон [Convert to Range].

1. Нажать на кнопку Да [Yes].

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

Важно: Чтобы выполнить преобразование в диапазон, необходимо иметь таблицу Excel. Дополнительные сведения можно найти в разделе Создание и удаление таблицы Excel.

Щелкните в любом месте таблицы, а затем перейдите в раздел работа с таблицами > конструктор на ленте.

В группе Сервис выберите команду преобразовать в диапазон.

Щелкните таблицу правой кнопкой мыши, а затем в контекстном меню выберите пункт таблица > преобразовать в диапазон.

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

Щелкните в любом месте таблицы, а затем откройте вкладку Таблица .

Нажмите кнопку преобразовать в диапазон.

Нажмите кнопку Да , чтобы подтвердить действие.

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

Щелкните таблицу правой кнопкой мыши, а затем в контекстном меню выберите пункт таблица > преобразовать в диапазон.

Примечание: Функции таблицы станут недоступны после ее преобразования в диапазон. Например, заголовки строк больше не включают стрелки "Сортировка и фильтр", а вкладка " Конструктор таблиц " исчезает.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

Сводные таблицы также называют двумерными (2D) таблицами или таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и наглядной матрице с заголовками столбцов и строк. Но такое представление данных не подходят для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в сторонние системы, т.д. Поэтому перед анализом данных так важно аккуратно преобразовать сводные таблицы в «плоский» список.

Надстройка «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:

• Редизайн сводной таблицы в список в секунды
• Преобразование сложных таблиц с многоуровневыми заголовками
• Корректный редизайн таблиц с объединёнными или пустыми ячейками
• Сохранение заголовков столбцов
• Сохранение форматирования ячеек

Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

Добавить «Редизайн таблицы» в Excel 2019, 2016, 2013, 2010

Подходит для: Microsoft Excel 2019 – 2010, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

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

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
2. Выделите сводную таблицу, включая заголовки.
   Совет: нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.
3. Укажите размер заголовков:
   В простой таблице: Заглавных строк = 1, Заглавных столбцов = 1
4. Укажите, следует ли поместить результат на новый или на существующий лист.
   Чтобы вставить плоский список на существующий лист, укажите начальную ячейку (верхняя левая).
5. Нажмите ОК > Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.

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

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

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
2. Выделите сводную таблицу, включая заголовки.
   Совет: нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.
3. Укажите размер заголовков:
4. Заглавных строк: число строк, которые составляют заголовок таблицы сверху.
5. Заглавных столбцов: число столбцов, которые составляют заголовок таблицы слева.
6. Укажите, следует ли поместить результат на новый или на существующий лист.
7. Нажмите ОК > Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.

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

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

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

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажком «Пропустить пустые значения».
5. Укажите, куда поместить результат.
6. Нажмите ОК > Готово.

Как выполнить редизайн таблицы с объединёнными ячейками

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажком «Дублировать значение в объединённых ячейках»:
5. Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответствующей строке плоского списка.
6. Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.
7. Укажите, куда поместить результат.
8. Нажмите ОК > Готово.

Как выполнить редизайн таблицы с сохранением заголовков

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

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

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажком «Сохранить формат ячеек»:
   Каждая ячейка сохранит своё форматирование в результирующем плоском списке, в т.ч. цвет заливки, границы, цвет шрифта, цвета условного форматирования, дата/общий/числовой/денежный/ формат, т.д.
5. Укажите, куда поместить результат.
6. Нажмите ОК > Готово.

Внимание: обработка больших таблиц с множеством форматов займёт больше времени.

Какие таблицы обрабатывает надстройка «Редизайн таблицы»

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

Термином «Таблица» в Excel часто обозначают разные понятия:

• «Настоящая» таблица — именованный диапазон с применением стиля таблицы (операция «Форматировать как таблицу»). Может быть преобразована в простой диапазон.
• Диапазон — простой диапазон внешне похожий на таблицу, с применением (или без) форматирования цвета фона, границ, т.д. Может быть преобразован в «настоящую» таблицу.
• Сводная таблица (PivotTable) — динамическая таблица, сгенерированная с помощью операции Excel «Сводная таблица» (PivotTable). Ячейки не могут быть отредактированы.

Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.

Появились вопросы или предложения? Оставьте комментарий ниже.