It’s no secret that most Excel users, when creating tables on sheets, think first of all about their own comfort and convenience. This is how beautiful, colorful and cumbersome tables with complex “headers” are born, which, at the same time, cannot be filtered or sorted at all, and it’s better not to think about an automatic report with a pivot table at all.
Sooner or later, the user of such a table comes to the conclusion that “it may not be so beautiful, but it can work” and begins to simplify the design of his table, bringing it into line with the classic recommendations:
- a simple one-line header, where each column will have its own unique name (field name)
- one line – one completed operation (deal, sale, posting, project, etc.)
- no merged cells
- without breaks in the form of empty rows and columns
But if you make a one-line header out of a multi-level one or split one column into several ones, it is quite simple, then the table reconstruction can take a lot of time (especially at large sizes). It means the following situation:
| Of |  | do |  |
In terms of databases, the right table is usually called flat (flat) – it is according to such tables that it is best to build reports of pivot tables (pivot tables) and conduct analytics.
You can convert a two-dimensional table to a flat table using a simple macro. Open the Visual Basic Editor via the tab Developer – Visual Basic (Developer — Visual Basic Editor) or keyboard shortcut Alt+F11. Insert a new module (Insert – Module) and copy the text of this macro there:
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 You can then close the VBA editor and return to Excel. Now we can select the original table (completely, with a header and the first column with months) and run our macro through Developer – Macros (Developer — Macros) or pressing combination Alt+F8.
The macro will insert a new sheet into the book and create a new, reconstructed version of the selected table on it. You can work with such a table “in full”, using the entire arsenal of Excel tools for processing and analyzing large lists.
- What are macros, where to insert macro code in VBA, how to use them
- Creating Reports with PivotTables
- Tool to redesign XNUMXD tables to flat ones from the PLEX add-on