Как сделать плоскую таблицу в excel

Обновлено: 05.07.2024

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

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

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

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

Из

сделать

В терминах баз данных правую таблицу обычно называют плоской (flat) - именно по таким таблицам лучше всего строить отчеты сводных таблиц (pivot tables) и проводить аналитику.

Преобразовать двумерную таблицу в плоскую можно при помощи простого макроса. Откройте редактор Visual Basic через вкладку Разработчик - Visual Basic (Developer - Visual Basic Editor) или сочетанием клавиш Alt + F11 . Вставьте новый модуль (Insert - Module) и скопируйте туда текст этого макроса:

После этого можно закрыть редактор VBA и вернуться в Excel. Теперь можно выделить исходную таблицу (полностью, с шапкой и первым столбцом с месяцами) и запустить наш макрос через Разработчик - Макросы (Developer - Macros) или нажав сочетание Alt + F8 .

Макрос вставит в книгу новый лист и создаст на нем новый, реконструированный вариант выделенной таблицы. С такой таблицей можно работать "по полной программе", применяя весь арсенал средств Excel для обработки и анализа больших списков.

- простая однострочная шапка, где у каждого столбца будет свое уникальное название (имя поля)

- одна строка - одна законченная операция (сделка, продажа, проводка, проект и т.д.)

- без объединенных ячеек

- без разрывов в виде пустых строк и столбцов

из такой таблицы

В терминах баз данных нижнюю таблицу обычно называют плоской (flat) - именно по таким таблицам лучше всего строить отчеты сводных таблиц (pivot tables) и проводить аналитику.

Преобразовать двумерную таблицу в плоскую можно при помощи простого макроса. Откройте редактор Visual Basic через вкладку Разработчик - Visual Basic (Developer - Visual Basic Editor) или сочетанием клавиш Alt+F11. Вставьте новый модуль (Insert - Module) и скопируйте туда текст этого макроса:

Sub Redesigner()
Dim i As Long
Dim hc As Integer, hr As Integer
Dim ns As Worksheet
hr = InputBox("Сколько строк с подписями сверху?")
hc = InputBox("Сколько столбцов с подписями слева?")
Application.ScreenUpdating = False
i = 1
Set inpdata = Selection
Set ns = Worksheets.Add
For r = (hr + 1) To inpdata.Rows.Count
For c = (hc + 1) To inpdata.Columns.Count
For j = 1 To hc
ns.Cells(i, j) = inpdata.Cells(r, j)
Next j
For k = 1 To hr
ns.Cells(i, j + k - 1) = inpdata.Cells(k, c)
Next k
ns.Cells(i, j + k - 1) = inpdata.Cells(r, c)
i = i + 1
Next c
Next r
End Sub

Также есть второй вариант для работы с большими таблицами

Sub Redesigner()
Dim inpdata As Range, realdata As Range, ns As Worksheet
Dim i&, j&, k&, c&, r&, hc&, hr&
Dim out(), dataArr, hcArr, hrArr
hr = Val(InputBox("Сколько строк с подписями данных сверху?"))
hc = Val(InputBox("Сколько столбцов с подписями данных слева?"))
Set inpdata = Selection
If inpdata.Rows.Count <= hr Or inpdata.Columns.Count <= hc Then Exit Sub
Set realdata = inpdata.Offset(hr, hc).Resize(inpdata.Rows.Count - hr, inpdata.Columns.Count - hc)
dataArr = realdata.Value
If hr Then hrArr = inpdata.Offset(0, hc).Resize(hr, inpdata.Columns.Count - hc).Value
If hc Then hcArr = inpdata.Offset(hr, 0).Resize(inpdata.Rows.Count - hr, hc).Value
ReDim out(1 To Application.CountA(realdata), 1 To hr + hc + 1)
Set ns = Worksheets.Add
For i = 1 To UBound(dataArr, 1)
For j = 1 To UBound(dataArr, 2)
If Not IsEmpty(dataArr(i, j)) Then
k = k + 1
For c = 1 To hc: out(k, c) = hcArr(i, c): Next c
For r = 1 To hr: out(k, c + r - 1) = hrArr(r, j): Next r
out(k, c + r - 1) = dataArr(i, j)
End If
Next j, i
ns.Cells(2, 1).Resize(UBound(out, 1), UBound(out, 2)) = out
End Sub

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

ОБНОВЛЕНО 19.04.15 до Версии 2 (макрос не изменился но был добавлен видеобзор на данную примочку)

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

- Данное решение построено на массивах, авторство принадлежит МСН.
- Внес небольшую коррективу, а именно запрос на выделение диапазона - Максим Зеленский , с дружеского форума.
- Дополнительные улучшения и полезности - SLAVICK .
- Небольшие дополнения и коррективы - DJ Marker MC

В итоге получился вот такой вот макрос:

1. К примеру имеем таблицу такого вида:

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

2. Указываем диапазон таблицы: (если таблица была выделена до запуска макроса, то выделенный диапазон будет подхвачен автоматически)

3. Третий шаг, в диалоговом окне необходимо указать сколько строк находится в шапке таблицы. В нашем примере - три строки.

4. Следующим шагом указываем количество столбцов с данными в левой части таблицы. В нашем примере их пять - Код, Цена, Направление, Страна, Мин.уп!

5. Если мы ходим получить ПЛОСКУЮ ТАБЛИЦУ, то оставляем тут значение по умолчанию 1, если Вам будет необходимо чтоб вправо таблица была разбита помесячно, а года были разбиты вниз то поставьте 12 (вообще на этом шаге поэкспериментируйте и сами поймете как это работает.
Я же оставлю тут 1, так как желаю получить плоскую таблицу

6. Последний шаг - вопрос: Хотите Вы уменьшить шапку таблицы или нет? Данный вопрос задается только в том случае, если в предыдущем шаге Вы указали - 1 или же если в шапку таблицы попадает всего одна строка (тоже попробуйте как это работает в двух вариантах методом "ТЫКА")

После того как мы нажмем "ОК", наша таблица уходящая вправо, превратится в плоскую, такого вида как на картинке ниже в левой части. Как Вы можете заметить все столбцы уже подписаны кроме первых трех (это была наша шапка), поскольку программе нету откуда взять название этих столбцов, мы подписываем их вручную. Подписываем наши столбцы и получаем готовую плоскую таблицу как в правой части картинки.

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

ОБНОВЛЕНО 19.04.15 до Версии 2 (макрос не изменился но был добавлен видеобзор на данную примочку)

В итоге получился вот такой вот макрос:

1. К примеру имеем таблицу такого вида:

Попробовать как это работает можно с помощью приложенного файла.
В целом все очень быстро и красиво!
Всем приятного пользования! DJ_Marker_MC

В итоге получился вот такой вот макрос:

1. К примеру имеем таблицу такого вида:

Попробовать как это работает можно с помощью приложенного файла.
В целом все очень быстро и красиво!
Всем приятного пользования! Автор - DJ_Marker_MC
Дата добавления - 04.02.2015 в 10:40

Читайте также: