Как разделить базу в excel

Разделение таблицы по листам

В Microsoft Excel есть много инструментов для сборки данных из нескольких таблиц (с разных листов или из разных файлов): прямые ссылки, функция ДВССЫЛ (INDIRECT), надстройки Power Query и Power Pivot и т.д. С этой стороны баррикад всё выглядит неплохо.

Но если вы нарвётесь на обратную задачу — разнесения данных из одной таблицы на разные листы — то всё будет гораздо печальнее. На сегодняшний момент цивилизованных встроенных инструментов для такого разделения данных в арсенале Excel, к сожалению, нет. Так что придется задействовать макрос на Visual Basic, либо воспольоваться связкой макрорекордер+Power Query с небольшой «доработкой напильником» после.

Давайте подробно рассмотрим, как это можно реализовать.

Постановка задачи

Имеем в качестве исходных данных вот такую таблицу размером больше 5000 строк по продажам:

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

Подготовка

Чтобы не усложнять код макроса и сделать его максимально простым для понимания, выполним пару подготовительных действий.

Во-первых, создадим отдельную таблицу-справочник, где в единственном столбце будут перечислены все города, для которых нужно создать отдельные листы. Само-собой, в этом справочнике могут быть не все города, присутствующие в исходных данных, а только те, по которым нам нужны отчеты. Проще всего создать такую таблицу, используя команду Данные — Удалить дубликаты (Data — Remove duplicates) для копии столбца Город или функцию УНИК (UNIQUE) — если у вас последняя версия Excel 365.

Поскольку новые листы в Excel по умолчанию создаются перед (левее) текущего (предыдущего), то имеет смысл также отсортировать города в этом справочнике по убыванию (от Я до А) — тогда после создания листы-города расположатся по алфавиту.

Во-вторых, преобразуем обе таблицы в динамические («умные»), чтобы с ними было проще работать. Используем команду Главная — Форматировать как таблицу (Home — Format as Table) или сочетание клавиш Ctrl+T. На появившейся вкладке Конструктор (Design) назовём их таблПродажи и таблГорода, соответственно:

Способ 1. Макрос для деления по листам

На вкладке Разработчик (Developer) нажмите на кнопку Visual Basic или используйте сочетание клавиш Alt+F11. В открывшемся окне редактора макросов вставьте новый пустой модуль через меню Insert — Module и скопируйте туда следующий код:

Sub Splitter()
    For Each cell In Range("таблГорода")
        Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value
        Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy
        Sheets.Add
        ActiveSheet.Paste
        ActiveSheet.Name = cell.Value
        ActiveSheet.UsedRange.Columns.AutoFit
    Next cell
    Worksheets("Данные").ShowAllData
End Sub	

Здесь с помощью цикла For Each … Next реализован проход по ячейкам справочника таблГорода, где для каждого города происходит его фильтрация (метод AutoFilter) в исходной таблице продаж и затем копирование результатов на новый созданный лист. Попутно созданный лист переименовывается в то же имя города и на нем включается автоподбор ширины столбцов для красоты.

Запустить созданный макрос в Excel можно на вкладке Разработчик кнопкой Макросы (Developer — Macros) или сочетанием клавиш Alt+F8.

Способ 2. Создаем множественные запросы в Power Query

У предыдущего способа, при всей его компактности и простоте, есть существенный недостаток — созданные макросом листы не обновляются при изменениях в исходной таблице продаж. Если обновление «на лету» необходимо, то придется использовать связку VBA+Power Query, а точнее — создавать с помощью макроса не просто листы со статическими данными, а обновляемые запросы Power Query.

Макрос в этом случае частично похож на предыдущий (в нём тоже есть цикл For Each … Next для перебора городов в справочнике), но внутри цикла будет уже не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:

Sub Splitter2()

For Each cell In Range("таблГорода")
    ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Источник = Excel.CurrentWorkbook(){[Name=""таблПродажи""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Измененный тип"" = Table.TransformColumnTypes(Источник,{{""Категория"", type text}, {""Наименование"", type text}, {""Город"", type text}, {""Менеджер"", type text}, {""Дата сделки"", type datetime}, {""Стоимость"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Строки с примененным фильтром"" = Table.Se" & _
        "lectRows(#""Измененный тип"", each ([Город] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Строки с примененным фильтром"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & cell.Value & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = cell.Value
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.Name = cell.Value
Next cell
End Sub

После его запуска мы увидим те же листы по городам, но формировать их будут уже созданные запросы Power Query:

При любых изменениях в исходных данных достаточно будет обновить соответствующую таблицу правой кнопкой мыши — команда Обновить (Refresh) или обновить сразу все города оптом, используя кнопку Обновить всё на вкладке Данные (Data — Refresh All).

Ссылки по теме

• Что такое макросы, как их создавать и использовать
• Сохранение листов книги как отдельных файлов
• Сборка данных со всех листов книги в одну таблицу

Введение

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

Как быстро разделить таблицу Excel на отдельные книги макросом. Пошаговый алгоритм

• Сочетанием клавиш Alt+F11 открываем окно Visual Basic
• Создаем новый модуль Insert -> Module
• Вставляем код

Sub Разделить_столбец_по_книгам()
Const column = 2 'номер столбца, по которому будет происходить разделение.'
Const head = True
Set wbAct = ActiveWorkbook

Set dic = CreateObject("Scripting.Dictionary")

lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).column

arr = Range("A1", Cells(lr, lc)).Value

If head Then fr = 2 Else fr = 1

For i = fr To UBound(arr)
    If Trim(arr(i, column)) <> "" Then dic.Item(arr(i, column)) = dic.Item(arr(i, column)) & "|" & i
Next

iPath = wbAct.Path & Application.PathSeparator & "Result" & Application.PathSeparator
'Result - название папки с результатами'
If Dir(iPath, vbDirectory) = "" Then MkDir iPath

arrDic = dic.keys
Set Rng = Nothing
Application.DisplayAlerts = False
For i = 0 To UBound(arrDic)
rrs = Split(Mid(dic.Item(arrDic(i)), 2), "|")
    If head Then Set Rng = Rows(1)
    For Each rr In rrs
        If Not Rng Is Nothing Then Set Rng = Union(Rows(rr), Rng) Else Set Rng = Rows(rr)
    Next
    Set wb = Workbooks.Add(1)
    Set sh = wb.Sheets(1)
    Rng.Copy
    sh.[A1].PasteSpecial xlPasteColumnWidths
    sh.[A1].PasteSpecial xlPasteAll
    Set Rng = Nothing
    wb.SaveAs iPath & Replace_symbols(arrDic(i)) & ".xlsx", xlOpenXMLWorkbook
    wb.Close False
Next
Application.DisplayAlerts = True
End Sub
'Замена запрещённых символов в имени файла или папки'
Function Replace_symbols(ByVal txt As String) As String
    St$ = "\/~!@#$%^&*=|`'"""
    For i% = 1 To Len(St$)
        txt = Replace(txt, Mid(St$, i, 1), "_")
    Next
    Replace_symbols = txt
End Function

• На второй строке кода, цифру 2 замените на номер столбца, в котором содержится критерий для разбиения
• Сохраните код Ctrl+S
• Сохраните файл Excel, как книгу с поддержкой макросов xlsm
• Выполните макрос Alt+F8

Папка с новыми файлами будет лежать по тому же пути, что и файл, в котором вы запускали макрос

Заключение

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

У нас Вы можете заказать выполнение задач по MS Excel и Google таблицам

А также, пройти бесплатные онлайн курсы по MS Excel с заданиями

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

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

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

Затем мы представляем вам всю информацию, которую вам нужно знать о Microsoft Excel и процедуру, если вы хотите разделить Excel на несколько файлов по одному столбцу.

Что такое Microsoft Excel?

Microsoft Excel — это приложение, входящее в состав пакета Office. Основана в 1985 году. и был очень полезен при выполнении задач в некоторых областях, таких как бухгалтерский учет, математика и финансы.

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

Для чего используется Microsoft Excel?

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

Как работает Microsoft Excel?

Microsoft Excel работает как электронная таблица, в которой данные упорядочиваются по ячейкам. В связи с этим клетки пересечение строки и столбца. Эта табличная организация позволяет пользователям вводить числовые и / или буквенно-цифровые данные для выполнения различные расчеты .

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

Разделите Excel на несколько файлов по столбцу

Чтобы разделить Excel на несколько файлов на основе столбца, вам необходимо использовать инструмент под названием Данные XL для автоматического создания этого процесса. Вы можете получить инструмент от Загрузите DataXL, перейдя на предоставленной странице и нажав «Загрузить DataXL».

Загрузите инструмент Data XL для Excel

«Данные XL» это очень полезное дополнение, которое вы можете скачать для Microsoft Excel. Загружаемый файл сжат в формате .zip, поэтому процесс установки необходимо выполнять вручную.

Вставьте надстройку в Microsoft Excel

После загрузки Data XL разархивируйте файл .zip. Затем войдите в Excel и найдите опцию «Вставить». Там вы найдете «Мои дополнения» и «Управление другими надстройками» . Когда вы выберете последний вариант, вы увидите окно, в котором вы должны выбрать «Управление надстройками Excel». Затем нажмите кнопку «Перейти».

Позже вы увидите окно с несколькими доступными надстройками; ты должен нажать на кнопку «Просматривать» чтобы найти путь, по которому был распакован файл Data XL. Нажмите «ОК», и надстройка будет установлена ​​в Microsoft Excel.

Используйте плагин Data XL

Чтобы использовать плагин Data XL, вам необходимо открыть файл, который вы хотите разделить, и активировать   Data XL в из меню. Выберите параметр «Разделить данные», чтобы указать диапазон ячеек и столбцов, которые вы будете использовать для разделения файла Excel.

Затем выберите «Создать новые файлы по определенному пути» и укажите формат создаваемых файлов. Наконец, нажмите кнопку «Разделяй сейчас» . Следовательно, вы получите несколько файлов из одного столбца в Excel.

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