Excel макрос объединения файлов: Программы и макросы Excel в категории Объединение файлов

Программы и макросы Excel в категории Объединение файлов

Как автоматически собрать данные из нескольких файлов Excel в один с помощью макроса Excel-VBA

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

Для того, чтобы перебрать все файлы .xls и .xlsx в папке C:/Folder/ можно использовать следующий код:

sFolder = "C:/Folder/"
sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator)

sFiles = Dir(sFolder & "*.xls*")

Do While sFiles <> ""
Workbooks.Open sFolder & sFiles

'what to do in each file

ActiveWorkbook.Close False

'write data

sFiles = Dir
Loop

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

sFolder = "C:/Folder/"
sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator)

Следующая команда открывает первый файл, находящийся в целевой папке. Для того, чтобы был открыт именно файл Excel  мы прописываем в качестве аргумента соединение двух строк: sFolder & «.xls*». Звёздочки в данном случае интерпретируются как любое количество любых символов. Таким образом мы открываем все файлы, оканчивающиеся на .»xls»+ любые другие символы. Если нам было бы нужно перебрать только файлы с определёнными символами в имени, то аргумент мог бы выглядеть так:  sFolder &»*Шаблон_имени_файла*»& «.xls*»

sFiles = Dir(sFolder & "*.xls*")

В заключительной части мы перебираем все остальные файлы, попадающие под заданный шаблон:

Do While sFiles <> ""
Workbooks.Open sFolder & sFiles

'what to do in each file

ActiveWorkbook.Close False

'write data

sFiles = Dir
Loop

Очевидно, что перебор осуществляется с помощью цикла While. Цикл работает пока переменная sFiles не будет равна пустой строке «». Предпоследняя строка — повторное использование функции Dir без аргумента присваивает переменной sFiles название пути и имени следующего файла, имя которого соответствует шаблону. В случае, если под заданный шаблон не больше не попадает файлов, переменная функция Dir присваивает переменной sFiles значение «», что приводит к остановке цикла.

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул 

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

 Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы




2. Создайте новую пустую книгу (Ctrl + N)




3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация (Data — Consolidate). Откроется соответствующее окно:




4. Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.




5. Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

 

 Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels). Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

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

 

Объединить несколько Excel файлов в один легко с «Excel Merger»

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

Это не требует много времени. Электронные таблицы легко соединять с приложением «Excel Merger» в Windows 10.

Объединить несколько Excel файлов в один

Перейдите по этому адресу:

https://www.pdfexcelconverter.com/excelmerger.html

и загрузите актуальную на данный момент версию приложения. Размер файла установщика — 3.3 МБ. Установите и запустите Excel Merger.

В приложении присутствует поддержка следующих форматов:

Для начала работы нажмите на кнопку «Добавить» на верхней Панели инструментов. Она обозначена значком плюса. С помощью системного файлового Проводника выберите объекты, подлежащие слиянию.

Проследите, чтобы внизу для опции «Объединить» из списка, установлен параметр «файлы». Для опции «Выход» укажите формат конечного файла, который вам подходит. Для параметра «Сохранить в» укажите место на жестком диске, где будет помещен конечный результат.

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

Выбор нескольких файлов Excel и их объединение в один

Чтобы понять, как работает слияние, рекомендуем для начала поработать с несколькими простыми файлами, которые содержат только несколько ячеек данных и один лист. При выборе параметра «файлы» — листы из обоих сливаемых добавляются отдельно в новый файл. При выборе параметра «файлы и листы» — все листы из обоих объединяются в один.

Здесь важно понять принцип работы и необходимо экспериментировать. Также необходимо убедиться, что вы проверили данные в новом Excel-файле, прежде чем избавляться от исходных объектов. Теперь вы знаете, как можно объединить несколько Excel файлов в один и сможете применять этот метод при необходимости на практике. Спасибо за внимание, оставайтесь с нами, комментируйте!

Объединение листов из различных рабочих книг в один файл

Версия от 27.08.2019

Операционная система: Windows

Microsoft Excel 2007/2010/2013/2016/2019/365

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

Пошаговая инструкция по установке для различных версий Excel

Видео по работе с надстройкой

Для работы с надстройкой необходимо:

1. Скачать файл, который станет доступен для скачивания сразу после оплаты (порядок оплаты);

2. Установить надстройку, следуя пошаговой инструкции по установке для различных версий Excel;

3. Нажать кнопку вызова диалогового окна на вкладке «Надстройки»;

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

Итогом действия надстройки является открытая книга, в которой объединены все выбранные пользователем листы из всех выбранных файлов.

*В случае, если в надстройку будут вноситься какие-либо изменения или дополнения, то обновленную версию надстройки Вы будете получать бесплатно в течение одного года со дня покупки.

Alexander,
11.02.2019

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

Павел,
31.12.2018

Добрый день!

Приобрел у Вас данную надстройку.

Спасибо Вам большое за проделанную работу.

Олеся,
07.11.2018

Большое спасибо разработчикам! Работает очень быстро, в пару кликов собрала 41 файл в один! Пожалуй приобрету ещё и другие надстройки для экономии времени. Отдельное спасибо за оперативную помощь в установке! Очень довольна сервисом!

Сергей,
19.10.2018

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

Ruslan,
20.09.2018

Спасибо за программу!

Андрей,
06.12.2017

Купил, скачал надстройку, установил на Офис 2007, всё заработало с «полпинка». Не пожалел о покупке, сэкономило добрых 3-4 часа моего времени. Благодарен автору, за столь полезный инструмент.

Артём,
21.04.2016

Быстро собрал нужные листы из всех файлов в один. Понравилась функция «присвоить листам имена файлов». Это очень удобно) Спасибо!

Оставить отзыв

Надстройка для работы с ячейками и листами

Клавиша:	Ctrl + T	Макрос:	Создание Текстовых Файлов
Клавиша:	Ctrl + 4	Макрос:	Окраска Объединённых Ячеек
Клавиша:	Ctrl + 5	Макрос:	Окраска Всех Объединённых Ячеек
Клавиша:	Ctrl + G	Макрос:	Объединение Значений Из Ячеек
Клавиша:	Ctrl + Shift + G	Макрос:	Разъединение Значений Из Ячеек
Клавиша:	Ctrl + Shift + D	Макрос:	Поиск Дубликатов В Книге
Клавиша:	Ctrl + Shift + O	Макрос:	Объединение Файлов Из Выбранной Папки
Клавиша:	Ctrl + Shift + E	Макрос:	Раскидать Листы По Файлам
Клавиша:	Ctrl + L	Макрос:	Количество Листов В Книге
Клавиша:	Ctrl + Shift + L	Макрос:	Добавление Фразы К Именам Выделенных Листов

 

Код надстройки:

Public ОписаниеГорячихКлавиш As String
 
Function НазначитьКомбинацию(ByVal code As String, ByVal macroname As String) As String
    Application.OnKey code, macroname
    Клавиша = UCase(code)
    Клавиша = Replace(Клавиша, "+", "Shift + ")
    Клавиша = Replace(Клавиша, "^", "Ctrl + ")
    Клавиша = Replace(Клавиша, "%", "Alt + ")
    Клавиша = Клавиша & Space(36 - Len(Клавиша))
    НазначитьКомбинацию = "Клавиша: " & vbTab & Клавиша & vbTab & "Макрос: " & vbTab & macroname & vbNewLine
End Function

Sub КоличествоЛистовВКниге()
    If ActiveWorkbook Is Nothing Then MsgBox "Сначала откройте книгу, а потом запускайте макрос", vbCritical, "Ошибка": End
    Dim sh As Worksheet, msg As String
    msg = "Книга: " & ActiveWorkbook.Name & vbNewLine
    msg = msg & "Путь: " & ActiveWorkbook.Path & vbNewLine & vbNewLine
    msg = msg & "Количество листов: " & ActiveWorkbook.Worksheets.Count & vbNewLine & vbNewLine
 
    For Each sh In ActiveWorkbook.Worksheets
        msg = msg & "Лист " & sh.Index & ": " & vbTab & _
              " (" & sh.UsedRange.Rows.Count & " строк)" & vbTab & sh.Name & vbNewLine
    Next sh
    MsgBox msg, vbInformation, "Информация о текущей книге"
End Sub

Sub ДобавлениеФразыКИменамВыделенныхЛистов()
    Dim sh As Worksheet, Фраза As String: On Error Resume Next
    ЗапрещённыеСимволы = "\/?:*[]": ЗапрещённыеСимволы2 = "\ / ? : * [ ] "
    msg = "Введите фразу для добавления к именам выделенных листов" & vbNewLine & vbNewLine
    msg = msg & 

Форматирование соответствия при объединении (Microsoft Excel)

Обратите внимание:
Эта статья написана для пользователей следующих версий Microsoft Excel: 97, 2000, 2002 и 2003. Если вы используете более позднюю версию (Excel 2007 или новее), , этот совет может не сработать для вас . Чтобы посмотреть версию этого совета, написанную специально для более поздних версий Excel, щелкните здесь: Соответствие форматирования при объединении.

При использовании формулы для объединения содержимого нескольких ячеек в одну, Крис не может заставить Excel сохранить форматирование исходных ячеек.Например, предположим, что ячейки A1 и B1 содержат 1 и 0,33 соответственно. В ячейку C1 он вводит следующую формулу:

= A1 & ":" & B1
 

Результат в ячейке C1 выглядит так:

1: 0,3333333333
 

Причина, по которой полученный C1 не соответствует тому, что показано в B1 (0,33), заключается в том, что значение в B1 на самом деле не 0,33. Внутри Excel поддерживает значения до 15 цифр, поэтому, если ячейка B1 содержит формулу, например = 1/3, внутренне она сохраняется как 0.33333333333333. Однако то, что вы видите в ячейке B1, зависит от того, как она отформатирована. В этом случае форматирование, вероятно, настроено на отображение только двух цифр после десятичной точки.

Однако есть несколько способов получить желаемые результаты в ячейке C1. Один из способов — просто немного изменить формулу, чтобы значения, извлеченные из ячеек A1 и B1, были отформатированы. Например, в следующем примере для форматирования используется функция ТЕКСТ:

= ТЕКСТ (A1; "0") & ":" & ТЕКСТ (B1; "0.00 ")
 

В этом случае A1 отформатирован для отображения только целых чисел, а B1 отформатирован для отображения только двух десятичных знаков. Вы также можете использовать функцию ROUND для достижения аналогичного результата:

= ОКРУГЛ (A1,0) & ":" & ОКРУГЛ (B1,2)
 

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

1. Выберите «Параметры» в меню «Инструменты». Excel отображает диалоговое окно «Параметры».
2. Убедитесь, что отображается вкладка «Расчет».(См. Рисунок 1.)

Рисунок 1. Вкладка «Расчет» диалогового окна «Параметры».

3. Убедитесь, что установлен флажок «Точность отображения».
4. Нажмите ОК.

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

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

Функция FmtText (rng As Range)
    Application.Volatile
    FmtText = rng.Cells (1) .Text
Конечная функция
 

Чтобы использовать этот макрос, вы должны использовать такую ​​формулу в своем листе:

= FmtText (A1) & ":" & FmtText (B1)
 

ExcelTips — ваш источник экономичного обучения работе с Microsoft Excel.Этот совет (3213) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь: Соответствие форматирования при объединении .

Автор биографии

Аллен Вятт

Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей.Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …

Форматирование текста поля со списком

Если вы вставляете на лист объекты, например поле со списком, вам может потребоваться способ изменить шрифт, используемый в объекте. …

Узнать больше

Комментарии в примечаниях

Можно ли добавлять комментарии везде, кроме примечаний? Это кажется ограничением в Word, но вот несколько способов сделать это…

Узнать больше

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

При редактировании формулы может оказаться полезным сочетание клавиш F4. Однако это может оказаться полезным не во всех случаях. Этот совет …

Узнать больше

.

Запуск макроса при изменении определенных ячеек в Excel — Office

• 22.05.2020
• 2 минуты на чтение

• Применимо к:

  Excel 2007, Excel 2003, Excel 2002, Excel 2000

В этой статье

Примечание

Office 365 профессиональный плюс переименовывается в Microsoft 365 Apps для предприятий .Дополнительные сведения об этом изменении см. В этом сообщении в блоге.

Сводка

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

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

Дополнительная информация

Microsoft предоставляет примеры программирования только для иллюстрации, без явных или подразумеваемых гарантий.Это включает в себя, но не ограничивается, подразумеваемые гарантии товарной пригодности или пригодности для определенной цели. В этой статье предполагается, что вы знакомы с демонстрируемым языком программирования и инструментами, которые используются для создания и отладки процедур. Инженеры службы поддержки Майкрософт могут помочь объяснить функциональность конкретной процедуры, но они не будут изменять эти примеры для обеспечения дополнительных функций или построения процедур в соответствии с вашими конкретными требованиями.Во многих случаях макрос должен запускаться только тогда, когда определенное количество ячеек содержит введенные в них значения (называемые в этом документе «ключевыми ячейками»). Чтобы предотвратить запуск большого макроса каждый раз, когда значение вводится в ячейку листа, необходимо проверить, не
ActiveCell — одна из ключевых ячеек. Для этого используйте метод Intersect для ActiveCell и диапазон, содержащий ключевые ячейки, чтобы убедиться, что ActiveCell является одной из ключевых ячеек. Если ActiveCell находится в диапазоне, содержащем ключевые ячейки, вы можете вызвать макрос.

Чтобы создать макрос Visual Basic:

1. Щелкните правой кнопкой мыши вкладку Sheet1 и выберите команду Просмотреть код.

   Лист модуля за Sheet1 открыт.

2. Введите следующий код в лист модуля:

     Private Sub Worksheet_Change (ByVal Target как диапазон)
       Dim KeyCells как диапазон
   
   'Переменная KeyCells содержит ячейки, которые будут
       вызывают предупреждение при их изменении.
       Установите KeyCells = Range ("A1: C10")
   
   Если не приложение.Пересечение (KeyCells, Диапазон (Target.Address)) _
              Тогда ничего
   
   'Отображать сообщение, когда одна из обозначенных ячеек была
           'изменилось.
           'Разместите здесь свой код.
           MsgBox «Ячейка» & Target.Address & «изменилась».
   
   Конец, если
   Конец подписки
     

3. Щелкните Закрыть и вернуться в Microsoft Excel в меню «Файл».

При вводе записи в ячейки A1: C10 на листе Sheet1 отображается окно сообщения.

.

No related posts.