Ни для кого не секрет, что большинство пользователей Excel при создании таблиц на листах думают в первую очередь о собственном комфорте и удобстве. Так рождаются красивые, красочные и громоздкие таблицы со сложными «заголовками», которые при этом вообще нельзя фильтровать или сортировать, а об автоматическом отчете со сводной таблицей лучше вообще не думать.
Рано или поздно пользователь такого стола приходит к выводу, что «может, это не так красиво, но работать может» и начинает упрощать конструкцию своего стола, приводя ее в соответствие с классическими рекомендациями:
- простой однострочный заголовок, где каждый столбец будет иметь свое уникальное имя (имя поля)
- одна строка – одна завершенная операция (сделка, продажа, проводка, проект и т.д.)
- нет объединенных ячеек
- без разрывов в виде пустых строк и столбцов
Но если сделать из многоуровневого заголовка однострочный или разбить один столбец на несколько, это достаточно просто, то реконструкция таблицы может занять много времени (особенно при больших размерах). Это означает следующую ситуацию:
| Of |  | do |  |
В терминах баз данных правильную таблицу обычно называют плоской (плоской) — именно по таким таблицам лучше всего строить отчеты сводных таблиц (сводных таблиц) и проводить аналитику.
Преобразовать двумерную таблицу в плоскую можно с помощью простого макроса. Откройте редактор Visual Basic на вкладке Разработчик — Visual Basic (Разработчик — редактор Visual Basic) или сочетание клавиш другой+F11. Вставьте новый модуль (Вставка – Модуль) и скопируйте туда текст этого макроса:
Sub Redesigner() Dim i As Long Dim hc As Integer, hr As Integer Dim ns As Worksheet hr = InputBox("Сколько строк с подписями сверху?") hc = InputBox("Сколько столбцов с подписями слева?") Application.ScreenUpdating = Ложь i = 1 Установить inpdata = Набор выбора ns = Worksheets.Add Для r = (hr + 1) В inpdata.Rows.Count Для c = (hc + 1) В inpdata.Columns.Count Для j = 1 В hc ns. Cells(i, j) = inpdata.Cells(r, j) Следующий j Для k = 1 До часа ns.Cells(i, j + k - 1) = inpdata.Cells(k, c) Следующий k ns.Cells( i, j + k - 1) = inpdata.Cells(r, c) i = i + 1 Следующий c Следующий r End Sub Затем вы можете закрыть редактор VBA и вернуться в Excel. Теперь мы можем выбрать исходную таблицу (полностью, с заголовком и первым столбцом с месяцами) и запустить наш макрос через Разработчик – Макросы (Разработчик — Макросы) или нажать комбинацию другой+F8.
Макрос вставит в книгу новый лист и создаст на нем новую, реконструированную версию выбранной таблицы. С такой таблицей можно работать «по полной программе», используя весь арсенал инструментов Excel для обработки и анализа больших списков.
- Что такое макросы, куда вставлять код макроса в VBA, как их использовать
- Создание отчетов с помощью сводных таблиц
- Инструмент для перепроектирования XNUMXD таблиц в плоские из дополнения PLEX