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

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

Нужно объединить несколько наборов данных в одну электронную таблицу? Вот как.

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

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

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

Простой метод — копировать листы руками.

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

1. Откройте книги, которые мы планируем объединить.
2. Выберите листы в исходной книге, которые вы хотите скопировать в основную книгу.

Чтобы выбрать несколько листов, используйте один из следующих приемов:

• Чтобы выбрать соседние листы, щелкните вкладку первого, который вы хотите скопировать, нажмите и удерживайте клавишу Shift, а затем щелкните вкладку последнего. Это действие выберет все листы между ними.
• Чтобы выбрать несмежные, удерживайте клавишу Ctrl и щелкайте вкладку каждого из них по отдельности.
• Выделив все нужные листы, щелкните правой кнопкой мыши любую из выделенных вкладок и выберите «Переместить» или «Копировать…» .

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

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

Замечание. При копировании листов вручную помните о следующем ограничении, налагаемом Excel: невозможно переместить или скопировать группу листов, если какой-либо из них содержит «умную» таблицу. В этом случае вам придется либо преобразовать таблицу в диапазон, либо использовать один из других методов, не имеющих этого ограничения.

5 способов объединить файлы и таблицы Excel в один рабочий лист

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

Что произойдет, если эти разные книги нужно объединить вместе, чтобы все листы находились в одном месте? Иногда Excel может быть сложной задачей, потому что он настолько мощный. Вы знаете, что то, что вы хотите делать, возможно, но, возможно, вы не знаете, как это сделать.

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

Способы объединения файлов и таблиц Excel

1. Скопируйте диапазоны ячеек.

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

Недостаток: этот метод очень проблематичен, если вам приходится иметь дело с несколькими листами или диапазонами ячеек. С другой стороны: для нескольких диапазонов это, вероятно, самый быстрый способ

2. Используйте КОСВЕННУЮ формулу

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

Вы создаете ссылку на файл и ячейку с помощью КОСВЕННОЙ формулы. Таким образом, исходные файлы остаются, и формула КОСВЕННО ищет только значения в этих файлах. файлов.

Если вы удалите файлы, вы получите # ССЫЛКА! ошибки.

Давайте подробнее рассмотрим, как построить формулу. Формула INDIRECT имеет только один аргумент: ссылку на другую ячейку, которая также может находиться в другой книге.

1. Скопируйте первую исходную ячейку.
2. Вставьте его в свой основной файл, используя специальную вставку (Ctrl + Alt + v). Вместо обычной вставки нажмите «Ссылка» в нижнем левом углу окна «Специальная вставка». Таким образом, вы извлечете полный путь. В нашем случае у нас есть следующая ссылка:
   = [160615_Examples.xlsm] Тысячи! 4 канадских доллара
3. Теперь мы обернем эту формулу КОСВЕННОЙ формулой. Кроме того, мы разделяем его на имя файла, имя листа и ссылку на ячейку. Таким образом, позже мы сможем просто изменить одну из этих ссылок, например, для разных версий одного и того же файла. Полная формула выглядит так (см. Также изображение выше):
   = КОСВЕННО («‘» & $ A3 & $ B3 & «‘!» & D $ 2 & $ C3)

Важно — обратите внимание: эта функция работает, только если исходные книги открыты.

3. Объедините файлы с помощью простого макроса VBA.

Вы не боитесь использовать простой макрос VBA? Затем вставим новый модуль VBA:

1. Перейдите на ленту разработчика. Если вы не видите ленту разработчика, щелкните правой кнопкой мыши любую ленту и выберите «Настроить ленту…». С правой стороны установите галочку на «Разработчик».
2. Щелкните Visual Basic в левой части ленты разработчика.
3. Щелкните правой кнопкой мыши имя своей книги и выберите Вставить -> Модуль.
4. Скопируйте и вставьте следующий код в новый модуль VBA. Поместите курсор в код и нажмите «Пуск» (зеленый треугольник) вверху. Вот и все!

4. Автоматическое объединение книг

Пятый способ, наверное, наиболее удобен:

1. Нажмите кнопку «Объединить файлы» на ленте «Профессор Excel».
2. Теперь выберите все файлы, которые вы хотите объединить, и все рабочие листы этих файлов Excel будут скопированы в текущую книгу.

Эта процедура хорошо работает также для многих файлов одновременно и не требует пояснений.

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

5. Копирование листов вручную

Следующий метод — вручную скопировать или переместить один или несколько листов Excel в другой файл. Поэтому откройте обе книги Excel:

Файл, содержащий рабочие листы, которые вы хотите объединить (исходная книга), и новый, который должен включать все рабочие листы из отдельных файлов.

1. Выберите листы в исходных книгах, которые вы хотите скопировать. Если в одном файле несколько листов, удерживайте клавишу Ctrl и щелкните вкладку каждого листа. Или перейдите к первому листу, который вы хотите скопировать, и удерживайте нажатой клавишу Shift. и щелкните последний лист. Таким образом, все рабочие листы между ними также будут выбраны.
2. После того, как все листы выбраны, щелкните правой кнопкой мыши любой из выбранных листов.
3. Щелкните «Переместить или скопировать».
4. Выберите целевую книгу.
5. Установите флажок «Создать копию». Таким образом, исходные листы останутся в исходной книге, и будет создана копия.
6. Подтвердите с помощью OK.

Нижняя линия

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

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

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

Используйте НЕПРАВИЛЬНУЮ формулу

Следующий метод имеет некоторые недостатки и немного сложнее. Это работает, если ваши файлы расположены в систематическом порядке и нужно импортировать только определенные значения. Вы используете формулу INDIRECT для создания ссылок на файлы и ячейки. Таким образом, исходные файлы сохраняются, и формула INDIRECT ищет только значения в этих файлах. Если вы удалите файлы, вы получите #REF! ошибки.

Рассмотрим подробнее как строится формула, У формулы INDIRECT есть только один аргумент: ссылка на другую ячейку, которая также может находиться в другой книге.

• Скопируйте первую исходную ячейку.
• Вставьте его в свой основной файл с помощью команды «Специальная вставка» (Ctrl computer_key_Ctrl + Alt computer_key_Alt + v computer_key_V). Вместо обычной вставки нажмите «Ссылка» в левом нижнем углу специального окна «Вставить». Таким образом вы извлечете полный путь. В нашем случае у нас есть следующая ссылка: = [160615_Examples.xlsm] Тысячи! $ C $ 4
• Теперь мы обернем эту формулу КОСВЕННОЙ формулой. Мы также разделяем его на имя файла, имя листа и ссылку на ячейку. Таким образом, мы можем легко изменить одну из этих ссылок позже, например, для разных версий одного и того же файла. Полная формула выглядит следующим образом: = КОСВЕННО («’» & $ A3 & $ B3 & »’!» & D $ 2 & $ C3)
• Важно — обратите внимание: эта функция работает, только если исходные папки открыты.

Excel works!

Меня часто спрашивают, зачем в Excel множество форматов файлов? А выбор действительно большой. Давайте разберемся поподробнее, для чего нужны разные расширение файлов Excel. Большинство типов файлов действительно полезны!

1. Расширение файлов Excel. Чем отличается *.xlsx от *.xls?

Сперва ответим на самый распространенный вопрос

Файл с расширением *.xlsx — это формат подавляющего большинства файлов Excel на настоящий момент. Появился начиная с версии 2007. Как говорит нам википедия: это набор *.xml файлов, помещенный в архив.

Файл с расширением *.xls — это формат всех файлов версии Excel 2003 и раньше. Он не является архивом. Имеет меньше возможностей, работает медленнее.

2. Формат с поддержкой макросов в Эксель

Расширение *.xlsm — Расширение файлов Excel 2007 и выше. Способен хранить в себе макросы . Т.е. программный код на языке VBA (Visual Basic for Applications), подробнее читайте здесь.

3. Двоичная книга Excel. *.xlsb

Расширение *.xlsb — это, объективно, самый удобный формат Excel. Это расширение позволяет уменьшать размер файла примерно в два раза, а убыстряет его работу где-то в полтора раза. Дело в том, что это документ хранящий информацию в бинарном коде, что позволяет быстрее работать с форматом .XML. Расширение просто не заменимо когда вы работаете с файлом с десятками тысяч строк (сохраните файл как Двоичная книга Excel (см. первую картинку). Тотальный разбор, от чего может тормозить Excel, читайте здесь .

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

Но, к сожалению, просто большинство пользователей никогда не слышало о *.xlsb.

4. Расширение *.xltx

Это формат для сохранения шаблонов. Скоро будет отдельная статья по этой теме. Макросы не сохраняет, для этого есть формат *.xltm — тоже самое, но с макросами.

5. Расширения *.xlam и *.xla

Форматы для cохранения надстроек в 2007 и выше и 2003 и ниже соответственно. Очень пригодятся, если нужно запускать макрос из любой книги.

Если вам попался формат .xlс — то это формат для сохранения диаграмм в Excel 2003. Формат — большая редкость и уже не поддерживается новыми версиями программы. Так же как и форматы WK1 и выше и форматы постепенно отмирающей Lotus 1-2-3.

Совет № 3: Закройте все открытые книги с помощью макроса

В этом разделе я поделюсь несколькими макросами, которые можно использовать для закрытия всех открытых рабочих книг. Макросы дают нам большую гибкость, когда мы хотим контролировать, какие книги сохраняются перед закрытием.

Макрос для закрытия всех книг без сохранения

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

Как работает этот макрос?

Приведенный выше макрос просматривает все открытые книги на компьютере с помощью цикла For Next Loop. Он использует оператор If, чтобы проверить, является ли файл в цикле (wb.Name) НЕ (<>) файлом, который содержит код ThisWorkbook.Name.

Затем он использует метод Workbooks.Close (страница справки MSDN), чтобы закрыть книгу. Метод Close имеет необязательный параметр для SaveChanges. Мы можем установить значение True, чтобы сохранить изменения, и False, чтобы закрыть без сохранения.

Примечание. Этот макрос следует запускать только в том случае, если вы уверены, что не хотите сохранять изменения ни в одном из открытых файлов. Его нельзя отменить. Возможно, вы также захотите добавить окно сообщения Да / Нет, чтобы предупредить пользователя перед запуском этого макроса.

Макрос для сохранения всех, кроме новых несохраненных книг (блокнот)

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

Файлы — блокноты никогда не сохранялись, поэтому они еще не имеют расширения. Они называются: Book1, Book2, Book2 и т.д. Обратите внимание, что на снимке экрана отсутствует расширение файла (.xlsx, .xlsm).

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

Как работает этот макрос?

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

Функция Right используется для возврата 5 символов в конце имени файла. Если wb — это существующий файл, который уже был сохранен, то Right (wb.Name, 5) вернет «.xlsx» или «.xlsm».

Функция Instr (страница справки MSDN) используется для поиска строки в строке. Возвращает начальный номер строки. Таким образом, если Instr находит «.xls» в пределах «.xlsx» или «.xlsm», он возвращает 1. В противном случае он возвращает 0.

Оператор If проверяет, является ли возвращаемое значение из Instr больше 0, и сохраняет изменения при закрытии книги. В противном случае изменения не сохраняются для книг, которые НЕ содержат расширения.

Макрос для сохранения и закрытия всех книг и автоматического именования файлов

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

Следующий макрос сохранит и закроет ВСЕ файлы и автоматически назовет новые файлы, которые не были сохранены. Он помещает все новые файлы (блокнот) в указанную вами папку.

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

Как работает этот макрос?

Этот макрос работает аналогично двум другим выше. Единственное отличие состоит в том, что он сохраняет новые файлы (блокноты) в папку на вашем компьютере.

Чтобы использовать этот макрос, вам нужно изменить переменную sPath на папку на вашем компьютере. Обязательно заканчивайте строку пути к файлу обратной косой чертой.

У метода Workbooks.Close есть еще один необязательный параметр (Filename), который позволяет нам указать имя файла, в котором мы хотим сохранить файл, если файл еще не был сохранен.

Если мы не используем параметр «Имя файла», Excel предложит нам меню «Сохранить как» для каждого файла. Таким образом, указание имени файла обходит это окно и может сэкономить нам много времени.

Значение параметра Filename объединяет строку для полного пути к файлу. Он также изменяет текущую дату и время до конца имени файла, используя функции «Формат» и «Сейчас». Функция Format в VBA аналогична функции TEXT в Excel и позволяет нам указать числовой формат. Функция «Сейчас» возвращает текущую дату и время на компьютере пользователя.

Я рекомендую добавить эти макросы в вашу личную книгу макросов, чтобы вы могли использовать их в любое время. Чтобы узнать больше, ознакомьтесь с моей статьей и видео о личной книге макросов.