Работа с разными файлами excel

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

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

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

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

Количество строк (заказов) в таблицах, само-собой, разное, но набор столбцов везде стандартный.

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

Подбираем оружие

Для решения нам потребуется последняя версия Excel 2016 (в нее нужный функционал уже встроен по умолчанию) или предыдущие версии Excel 2010-2013 с установленной бесплатной надстройкой Power Query от Microsoft (скачать ее можно здесь). Power Query — это супергибкий и супермощный инструмент для загрузки в Excel данных из внешнего мира с последующей их зачисткой и обработкой. Power Query поддерживает практически все существующие источники данных — от текстовых файлов до SQL и даже Facebook

Если у вас нет Excel 2013 или 2016, то дальше можно не читать (шучу). В более древних версиях Excel подобную задачу можно реализовать только программированием макроса на Visual Basic (что весьма непросто для начинающих) или монотонным ручным копированием (что долго и порождает ошибки).

Шаг 1. Импортируем один файл как образец

Для начала давайте импортируем данные из одной книги в качестве примера, чтобы Excel «подхватил идею». Для этого создайте новую пустую книгу и…

• если у вас Excel 2016, то откройте вкладку Данные и выберите Создать запрос — Из файла — Из книги (Data — New Query- From file — From Excel)
• если у вас Excel 2010-2013 с установленной надстройкой Power Query, то откройте вкладку Power Query и выберите на ней Из файла — Из книги (From file — From Excel)

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

Если нажать в правом нижнем углу этого окна кнопку Загрузить (Load), то таблица будет сразу импортирована на лист в исходном виде. Для одиночного файла — это хорошо, но нам нужно загрузить много таких файлов, поэтому мы пойдем немного другим путем и жмем кнопку Правка (Edit). После этого должен в отдельном окне отобразиться редактор запросов Power Query с нашими данными из книги:

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

• отфильтровывать ненужные данные, пустые строки, строки с ошибками
• сортировать данные по одному или нескольким столбцам
• избавляться от повторов
• делить слипшийся текст по столбцам (по разделителям, количеству символов и т.д.)
• приводить текст в порядок (удалять лишние пробелы, исправлять регистр и т.д.)
• всячески преобразовывать типы данных (превращать числа как текст в нормальные числа и наоборот)
• транспонировать (поворачивать) таблицы и разворачивать двумерные кросс-таблицы в плоские
• добавлять к таблице дополнительные столбцы и использовать в них формулы и функции на встроенном в Power Query языке М.
• …

Для примера, давайте добавим к нашей таблице столбец с текстовым названием месяца, чтобы потом проще было строить отчеты сводных таблиц. Для этого щелкните правой кнопкой мыши по заголовку столбца Дата и выберите команду Дублировать столбец (Duplicate Column), а затем щелкните правой кнопкой мыши по заголовку появившегося столбца-дубликата и выберите команды Преобразование — Месяц — Название месяца:

Должен образоваться новый столбец с текстовыми названиями месяца для каждой строки. Дважды щелкнув по заголовку столбца, его можно переименовать из Копия Дата в более удобное Месяц, например.

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

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

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

Легко и изящно, не правда ли?

Шаг 2. Преобразуем наш запрос в функцию

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

В редакторе запросов перейдите на вкладку Просмотр и нажмите кнопку Расширенный редактор (View — Advanced Editor). Должно открыться окно, где все наши предыдущие действия будут записаны в виде кода на языке М. Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера:

Теперь аккуратно вносим пару правок:

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

Все. Жмем на Готово и должны увидеть вот это:

Не пугайтесь, что пропали данные — на самом деле все ОК, все так и должно выглядеть Мы успешно создали нашу пользовательскую функцию, где запомнился весь алгоритм импорта и обработки данных без привязки к конкретному файлу. Осталось дать ей более понятное имя (например getData) на панели справа в поле Имя и можно жать Главная — Закрыть и загрузить (Home — Close and Load). Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера.. Вы вернетесь в основное окно Microsoft Excel, но справа должна появиться панель с созданным подключением к нашей функции:

Шаг 3. Собираем все файлы

Все самое сложное — позади, осталась приятная и легкая часть. Идем на вкладку Данные — Создать запрос — Из файла — Из папки (Data — New Query — From file — From folder) или, если у вас Excel 2010-2013, аналогично на вкладку Power Query. В появившемся окне указываем папку, где лежат все наши исходные файлы-города и жмем ОК. Следующим шагом должно открыться окно, где будут перечислены все найденные в этой папке (и ее подпапках) файлы Excel и детализация по каждому из них:

Жмем Изменить (Edit) и опять попадаем в знакомое окно редактора запросов.

Теперь нужно добавить к нашей таблице еще один столбец с нашей созданной функцией, которая «вытянет» данные из каждого файла. Для этого идем на вкладку Добавить столбец — Пользовательский столбец (Add Column — Add Custom Column) и в появившемся окне вводим нашу функцию getData, указав для ее в качестве аргумента полный путь к каждому файлу:

После нажатия на ОК созданный столбец должен добавиться к нашей таблице справа.

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

А теперь «вау-момент» — щелкнем мышью по значку со своенным стрелками в правом верхнем углу добавленного столбца с нашей функцией:

… снимаем флажок Использовать исходное имя столбца как префикс (Use original column name as prefix)и жмем ОК. И наша функция подгрузит и обработает данные из каждого файла, следуя записанному алгоритму и собрав все в общую таблицу:

Для полной красоты можно еще убрать расширения .xlsx из первого столбца с именами файлов — стандартной заменой на «ничего» (правой кнопкой мыши по заголовку столбца — Заменить) и переименовать этот столбец в Город. А также подправить формат данных в столбце с датой.

Все! Жмем на Главной — Закрыть и загрузить (Home — Close & Load). Все собранные запросом данные по всем городам будут выгружены на текущий лист Excel в формате «умной таблицы»:

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

В будущем, при любых изменениях в папке (добавлении-удалении городов) или в файлах (изменение количества строк) достаточно будет щелкнуть правой кнопкой мыши прямо по таблице или по запросу в правой панели и выбрать команду Обновить (Refresh) — Power Query «пересоберет» все данные заново за несколько секунд.

P.S.

Поправка. После январских обновлений 2017 года Power Query научился собирать Excel’евские книги сам, т.е. не нужно больше делать отдельную функцию — это происходит автоматически. Таким образом второй шаг из этой статьи уже не нужен и весь процесс становится заметно проще:

1. Выбрать Создать запрос — Из файла — Из папки — Выбрать папку — ОК
2. После появления списка файлов нажать Изменить
3. В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла

И все! Песня!

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

• Редизайн кросс-таблицы в плоскую, подходящую для построения сводных таблиц
• Построение анимированной пузырьковой диаграммы в Power View
• Макрос для сборки листов из разных файлов Excel в один

Russian (Pусский) translation by Andrey Rybin (you can also view the original English article)

По мере того как вы используете и создаете больше рабочих документов Excel, у вас может возникнуть необходимость связать их между собой. Может быть вы захотите написать формулу, которая использует данные с разных Листов. Или может даже вы напишете формулу, которая будет использовать данные из разных Книг (стандартное название рабочего документа Excel — workbook (анг.))

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

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

Как Можно Быстро Организовать Ссылку Между Данными в Книгах Excel (Смотри и Учись)

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

Давайте посмотрим иллюстрированное руководство по связыванию Листов и Книг в Excel.

Основы: Как Добавить Ссылку между Листами в Excel

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

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

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

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

1. Создайте в Excel новую Формулу

Многие формулы в Excel начинаются со знака равно (=). Дважды щелкните или начните печатать в ячейке, и вы начнете создавать формулу, в которую вы хотите вставить ссылку. Например, я собираюсь написать формулу, которая будет суммировать значения из разных ячеек.

Я открываю запись знаком =, и затем щелкаю по первой ячейке на текущем листе, чтобы создать первую часть формулы. Затем, я напечатаю знак +, чтобы добавить значение из второй ячейки в этой формуле.

Пока не завершайте ввод формулы и не жмите Enter! Вам нужно оставить формулу открытой перед тем как вы переключитесь на другой Лист.

2. Переключитесь между Листами в Excel

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

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

Заметьте на картинке ниже, что для ссылки на ячейку  на другом Листе, Excel добавил  надпись «Sheet2!B3», которая ссылается на ячейку В3 на листе с названием Sheet2. Вы можете написать это вручную, но когда в щелкаете по ячейке, Excel делает эту надпись автоматически за вас.

3. Закройте Excel формулу

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

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

Повышаем Уровень Сложности: Как Создать Связь Между Несколькими Книгами Excel

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

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

1. Откройте Две Рабочие Книги

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

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

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

2. Начните Создавать Формулу в Excel

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

Давайте рассмотрим число баррелей нефти которую я покупаю каждый месяц и цену за баррель. Сначала в ячейке из колонки Cost (ячейка C3), я начинаю создавать формулу, щелкнув в ней и нажав знак равно (=), а затем щелкаю по ячейке В3, чтобы взять значение для количества. Теперь, я жму знак *, чтобы уможножить количество на курс.

Итак, ваша формула, пока должна выглядеть так:

=B3*

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

3. Переключитесь на Другой Рабочий Документ Excel 

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

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

Excel автоматически добавит ссылку на другой рабочий документа, как часть формулы:

=B3*[Prices.xlsx]Sheet1!$B$2

После того как вы нажмете Enter, Excel автоматически рассчитает окончательную стоимость умножив количество в первой книге на цену из второй книги.

Потренируйте свои навыки работы в Excel, умножая количество товара или объем на соответствующую ему цену из документа «Prices«.

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

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

Как Обновлять Данные При Работе с Несколькими Книгами

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

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

Ответ такой, «зависит», и в основном зависит от того, открыты ли два документа одновременно.

Пример 1: Открыты Обе Книги Excel

Давайте проверим пример используя те же книги, что мы использовали раньше. Обе книги открыты. Давайте посмотрим, что произойдет, если мы изменим цену нефти за баррель с 45$ на 75$:

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

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

Пример 2: Если Один Рабочий Документ Закрыт

А что если вы открыли только один рабочий документ? Например,
каждое утро мы обновляем цены на наши товары и валюты, а вечером мы
рассматриваем влияние изменений на наши покупки и продажи.

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

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

Подводим итоги и продолжаем обучаться работе в Excell

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

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

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

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

Для работы этого макроса, помимо него самого, вам понадобится добавить в свой файл:

1. функцию FilenamesCollection для получения списка файлов в папке
2. функцию GetFolder для вывода диалогового окна выбора папки с запоминанием выбранной папки
3. прогресс-бар для отображения процесса обработки файлов (модуль класса и форму)

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

После того, как очередной файл обработан, он перемещается во вторую папку («архив»).

Код макроса:

Sub ИмпортДанныхИзЗаявок()
    On Error Resume Next: Err.Clear
    ' запрашиваем пути к папкам с файлами
    InvoiceFolder$ = GetFolder(1, , "Выберите папку с файлами заявок (из Outlook)")
    If InvoiceFolder$ = "" Then MsgBox "Не задана папка с заявками", vbCritical, "Обработка заявок невозможна": Exit Sub
 
    ArchieveFolder$ = GetFolder(2, , "Выберите папку, куда будут помещаться обработанные файлы заявок")
    If ArchieveFolder$ = "" Then MsgBox "Не задана папка для архива заявок", vbCritical, "Обработка заявок невозможна": Exit Sub
 
    Dim coll As Collection
    ' загружаем список файлов по маске имени файла
    Set coll = FilenamesCollection(InvoiceFolder$, "Заявка №*от*.xls*", 1)
 
    If coll.Count = 0 Then
        MsgBox "Не найдено ни одной заявки для обработки в папке" & vbNewLine & InvoiceFolder$, _
               vbExclamation, "Нет необработанных заявок"
        Exit Sub
    End If
 
    Dim pi As New ProgressIndicator: pi.Show "Обработка заявок", , 2
    pi.StartNewAction , , , , , coll.Count    ' отображаем прогресс-бар

    Dim WB As Workbook, sh As Worksheet, ra As Range
    Application.ScreenUpdating = False  ' отключаем обновление экрана (чтобы процесс открытия файлов не был виден)

    ' перебираем все найденные в папке файлы
    For Each Filename In coll
 
        ' обновляем информацию на прогресс-баре
        pi.SubAction "Обрабатывается заявка $index из $count", "Файл заявки: " & Dir(Filename), "$time"
        pi.Log "Файл: " & Dir(Filename)
 
        ' открываем очередной файл в режиме «только чтение»
        Set WB = Nothing: Set WB = Workbooks.Open(Filename, False, True)
 
        If WB Is Nothing Then    ' не удалось открыть файл
            pi.Log vbTab & "ОШИБКА при загрузке файла. Файл не обработан."
 
        Else    ' файл успешно открыт
            Set sh = WB.Worksheets(1)    ' будем брать данные с первого листа
            ' берем диапазон ячеек с ячейки B1 до последней заполненной в столбце B
            Set ra = sh.Range(sh.Range("b1"), sh.Range("b" & sh.Rows.Count).End(xlUp))
 
            ' ==== переносим данные в наш файл (shb - кодовое имя листа, куда помещаем данные)
            shb.Range("a" & shb.Rows.Count).End(xlUp).Offset(1).Resize(, ra.Rows.Count).Value = _
            Application.WorksheetFunction.Transpose(ra.Value)
            ' ==== конец обработки данных из очередного файла

            WB.Close False: DoEvents    ' закрываем обработанный файл без сохранения изменений
            pi.Log vbTab & "Файл успешно обработан."
 
            ' перемещаем обработанный файл из папки InvoiceFolder$ в папку ArchieveFolder$
            Name Filename As ArchieveFolder$ & Dir(Filename, vbNormal)
 
        End If
    Next
 
    ' закрываем прогресс-бар, включаем обновление экрана
    pi.Hide: DoEvents: Application.ScreenUpdating = True
    MsgBox "Обработка заявок завершена", vbInformation
End Sub

Во вложении — файл со всеми необходимыми макросами для сбора данных из других файлов Excel

Поддерживаемые Excel форматы файлов

​Смотрите также​ книгой(второй файл с​ папку, которую откроют​: Подскажите как будет​​ vbInformation End Sub​​ .Cells(LastRowСводный + СколькоОбъединять,​​ Then MsgBox «В​​ If If IsEmpty(ЛистИсточник.Cells(31​ _ «Если это​​ форматами – жмите​​DarkSacred​ в конец конечного​ просто показал, что​ Excel​ разделителей.​

​SYLK​​ кода. Позволяет использовать​Файл Excel можно сохранить​ данными), в которой​ предполагаемые участники. Нажимаем​ выглядеть код если​

​lalike​ 15)).Merge End With​ выбранной папке есть​ + i, 6).Value)​ последняя ячейка столбца​ OK.​​, да и можно​​ файла?​​ откуда берется.​​lalike​CSV (разделители — запятые)​SLK​ проекты VBA и​ в другом формате.​ хранятся исходные данные​ «Сохранить».​ ситуация попроще:​: СПАСИБО!!!!​ ’22 столбец If​ файлы недопустимых форматов​ = False Then​ C11 или столбца​

Форматы файлов Excel

Форматы текстовых файлов

​ 4)).Merge .Cells(LastRowКонечный +​​ файле, чтобы знать,​ Dim ЛистКонечный As​ я его не​,​

Другие форматы файлов

Форматы файлов, использующие буфер обмена

​ с 200 Excel-файлами.​ собираю данные в​расположение ячеек в​ или BMP​​ Excel в формате​​ открыть книгу в​​ форматирования, данных и​​ то не устраивает​​Нажимаем «Открыть».​​ одновременно вносят какие-то​​ И будете превращать​​ 1, 26).Value =​​ последнюю ячейку столбца​​ 5)).Merge .Cells(LastRowКонечный +​ 31 Step 2​

Форматы файлов, не поддерживаемые в Excel

​ сохранять в формате,​Таблица, сохраненная в приложении​ в другой программе),​: Это как ???​ (в нижней части​ отслеживать действия рабочей​ А. Гарнаева, Самоучитель​ ‘Теперь с необъединёнными​ + 1, 5).Value​

Форматы файлов, не поддерживаемые в Excel Starter

​Файл​Вы термин «физически»​«Общие» — «Личная настройка»​ редактировать противоречивые изменения.​Busine2009​ i = 1​ ЛистСводный.Cells(LastRowСводный + 1,​

Открытие и просмотр файлов в неподдерживаемых форматах

​:​ To LastRowИсточник -​ 6).Value = ЛистИсточник.Cells(11,​ .Cells(LastRowКонечный + СколькоОбъединять,​ Next i j​

• ​ уже получены. For​ ‘запоминаем количество файлов​Dim myPath As​Sub CombineWorkbooks()​

• ​lalike​Веб-страница в одном файле​ формат таблиц OpenDocument,​ Этот формат поддерживается только​Открыть​ что он означает​ Вводим опознавательную информацию​

См. также:

​ работу в Excel.​lalike​ 32 Step 2​ 4).Value ЛистСводный.Cells(LastRowСводный +​

support.office.com

Работа с файлами Excel

​ 7)).Merge .Cells(LastRowКонечный +​​ = 0 СколькоОбъединять​ Each ЛистКонечный2 In​ ‘————————— Set sh​
​ String, myName As​​Dim FilesToOpen​,​MHT, MHTML​ таких как Google​

​ в Excel Starter.​

​. Книги Excel 97—2003​​ в вашем представлении.​ (имя, ник-нейм).​
​В книге Excel со​,​
​ j = j​ 1, 7).Value =​
​ 1, 8).Value =​
​ = ЛистКонечный.Cells.SpecialCells(xlCellTypeLastCell).Row -​ Workbooks(«Конечный.xls»).Worksheets If ЛистКонечный2.Name​
​ = ActiveSheet ‘запоминает​ String, i As​
​Dim x As​я в Excel​Веб-страница в одном файле​ Docs или OpenOffice.org​

​Формат​​ автоматически открываются в​​Если я правильно​​Все. Можно редактировать информацию,​
​ совместным доступом не​что ты именно​ + 1 If​ ЛистИсточник.Cells(20, 4).Value ЛистСводный.Cells(LastRowСводный​ ЛистИсточник.Cells(10, 11).Value .Range(.Cells(LastRowКонечный​ LastRowКонечный ‘Будем отдельно​
​ = «Имена файлов»​ лист куда надо​ Integer​ Integer​ не работал практически,​ (MHT или MHTML).​
​ Calc. Таблицы в​

1. ​Расширение​
2. ​ режиме совместимости. Чтобы​
3. ​ телепатировал ваши мысли,​ вводить новую. После​

​ все задачи можно​​ читаешь? Какой раздел​ IsEmpty(ЛистИсточник.Cells(31 + i,​ + 1, 8).Value​
​ + 1, 8),​ заносить данные в​
​ Then Флаг =​ вносить информацию Row​With Application.FileDialog(msoFileDialogFolderPicker)​On Error GoTo​ поэтому не знаю,​
​ Этот формат файла​ формате ODS также​Описание​ получить доступ ко​ то как то​ работы – сохранить.​ выполнять.​ этой книги и​ 14).Value) = False​ = ЛистИсточник.Cells(10, 11).Value​ .Cells(LastRowКонечный + СколькоОбъединять,​ отношении ячеек конечного​ True Exit For​
​ = 2 ‘начинаем​.Title = «Укажите​ ErrHandler​ можно это сделать​
​ объединяет встроенные рисунки,​ можно открывать в​
​Форматированный текст (разделители —​ всем новым возможностям​
​ так​Случается, что при открытии​
​Нельзя:​ для чего ты​ Then ЛистСводный.Cells(LastRowСводный +​
​ ЛистСводный.Cells(LastRowСводный + 1,​
​ 8)).Merge .Cells(LastRowКонечный +​ файла, имеющих и​ End If Next​
​ запись с 3​
​ рабочую папку»: .Show​Application.ScreenUpdating = False​

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

​ j, 19).Value =​​ 11).Value = ЛистИсточник.Cells(17,​ 1, 11).Value =​ не имеющих объединение.​
​ ЛистКонечный2 If Флаг​ строчки For i​

​If .SelectedItems.Count =​​FilesToOpen = Application.GetOpenFilename​​ самого Excel.​​ и другие вспомогательные​
​ При сохранении и​PRN​ эту книгу в​ книгу Set OldBook​ совместным доступом к​Создавать, менять или просматривать​ книги?​

​ ЛистИсточник.Cells(31 + i,​​ 11).Value ЛистСводный.Cells(LastRowСводный +​​ ЛистИсточник.Cells(17, 11).Value .Range(.Cells(LastRowКонечный​​ ‘Сначала поработаем с​
​ = False Then​ = 1 To​ 0 Then Exit​
​ _​Наверное, можно решить​ элементы, на которые​
​ открытии ODS-файлов может​Формат для Lotus с​ формате Excel 2010.​

​ = ActiveWorkbook …​​ файлу появляется запись​ сценарии.​lalike​

​ 14).Value Else ЛистСводный.Cells(LastRowСводный​​ 1, 12).Value =​​ + 1, 11),​​ теми ячейками, которые​
​ MsgBox «Не добавлен​ qFile Set book​ Sub​(FileFilter:=»Microsoft Excel Files​ вашу задачу с​ есть ссылки в​ быть потеряно форматирование.​ разделителями в виде​
​ Однако можно продолжать​ … … ‘​ «Файл заблокирован». Сохранить​
​Удалять листы.​: еще вот посоветовали​ + j, 19).Value​ ЛистИсточник.Cells(17, 15).Value ЛистСводный.Cells(LastRowСводный​ .Cells(LastRowКонечный + СколькоОбъединять,​ нужно объединить. ’15​
​ лист в конечный​ = Workbooks.Open(nFile(i)) ‘заполняем​
​myPath = .SelectedItems(1)​
​ (*.xls), *.xls», _​
​ помощью VBA, тогда​ документе.​
​PDF​ пробелов. Сохраняется только​
​ работать в режиме​
​ открываем книгу, с​ не получается. При​
​Объединять либо разделять ячейки.​Дж. Уокенбаха. «Профессиональное​
​ = 0 End​ + 1, 15).Value​
​ 11)).Merge .Cells(LastRowКонечный +​ столбец For i​
​ файл для записи​ общую информацию Row​
​ & «»​
​MultiSelect:=True, Title:=»Files to​
​ вам нужно более​
​Примечание:​PDF​
​ активный лист​
​ совместимости, сохраняя исходный​
​ именем из переменной​
​ последующем открытии оказывается,​
​Работать с XML-данными (импортировать,​
​ программирование на VBA​
​ If If IsEmpty(ЛистИсточник.Cells(31​
​ = 0 ЛистСводный.Cells(LastRowСводный​
​ 1, 12).Value =​
​ = 1 To​
​ имён файлов, из​
​ = Row +​End With​ Merge»)​
​ подробно описать, что​ Этот формат не поддерживается​Этот формат файла сохраняет​Текст (разделители — знаки​ формат файла для​ myPath Workbooks.Open Filename:=myPath​ что общий доступ​ добавлять, обновлять, удалять​

​ в Excel 2002​​ + i, 13).Value)​ + 1, 17).Value​ ЛистИсточник.Cells(17, 15).Value .Range(.Cells(LastRowКонечный​
​ LastRowИсточник — 31​ которых данные уже​ 1 ‘переходим на​Application.ScreenUpdating = False:​If TypeName(FilesToOpen) =​ вам нужно.​ в Excel 2007.​ форматирование документа и​ табуляции)​ обратной совместимости.​ Set WB =​ отключен. Возможные причины​ и т.д.).​ (2003)», «Подробное руководство​
​ = False Then​ = 0 ЛистСводный.Cells(LastRowСводный​ + 1, 12),​ Step 2 If​ взяты.» & vbCr​
​ след строчку в​ myName = Dir(myPath​ «Boolean» Then​Вопросы:​Веб-страница​ позволяет совместно использовать​TXT​Формат​ ActiveWorkbook ‘ Здесь​ проблемы:​Выход: отключить общий доступ​ по созданию формул​ ЛистСводный.Cells(LastRowСводный + j,​ + 1, 19).Value​ .Cells(LastRowКонечный + СколькоОбъединять,​ IsEmpty(ЛистИсточник.Cells(31 + i,​ & _ «Или​ общей таблице sh.Cells(Row,​ & «*.xls»): i​

​MsgBox «Не выбрано​​Где расположены Excel-файлы?​​HTM, HTML​​ файлы. Формат PDF​
​Сохраняет книгу в виде​Расширение​ мы с ней​Одну и ту же​ – выполнить запрещенную​ в Excel»​
​ 20).Value = ЛистИсточник.Cells(31​ = 0 ЛистСводный.Cells(LastRowСводный​ 12)).Merge End With​ 12).Value) = False​ лист имеет неправильное​ 1) = Cells(8,​

​ = 1: Cells.ClearContents​​ ни одного файла!»​Какие ячейки вы берёте?​Формат HTML.​ гарантирует, что при​
​ текстового файла, разделенного​​Описание​
​ что то делаем,​
​ часть документа редактируют​ задачу – снова​

​Busine2009​​ + i, 13).Value​​ + 1, 20).Value​​ ‘Делаем запись на​
​ Then Сумма =​ имя.», vbCritical Exit​ 4) sh.Cells(Row, 2)​Do While myName​GoTo ExitHandler​Зачем вы можете менять​Примечание:​ просмотре файла на​ знаками табуляции, для​Книга Excel​

​ а именно считываем​​ несколько пользователей. К​
​ включить доступ.​
​, я пока знакомлюсь​ Else ЛистСводный.Cells(LastRowСводный +​ = 0 ЛистСводный.Cells(LastRowСводный​ добавленном в конечную​
​ Сумма + ЛистИсточник.Cells(31​
​ Sub End If​ = Cells(10, 4)​
​ <> «»​End If​ названия файлов?​
​ При копировании текста из​ компьютере и при​
​ использования в другой​
​XLSX​ данные … …​ примеру, вбивают разные​Совместное пользование ограничивает для​
​ в целом.​ j, 20).Value =​
​ + 1, 21).Value​ книгу листе, что​ + i, 12).Value​ ‘Проверка, что с​
​ sh.Cells(Row, 3) =​Cells(i, 2).Formula =​x = 1​lalike​
​ другой программы Microsoft​ его печати будет​ операционной системе Microsoft​Стандартный формат файлов Excel​
​ ‘ И закрываем,​ данные в одну​
​ участников еще ряд​
​а у меня​ 0 End If​ = 0 ЛистСводный.Cells(LastRowСводный​
​ данный файл-источник обработан.​
​ End If Next​​ данного листа данные​
​ Cells(11, 4) sh.Cells(Row,​ «='» & myPath​
​While x Workbooks.Open​: Вобщем, имеется куча​ Excel вставляет этот​

​ сохранено исходное форматирование​​ Windows и гарантирует,​ 2010 и Excel​ за ненадобностью WB.close​ ячейку. Возникает блокировка.​ задач:​ еще такой вопрос,​
​ If IsEmpty(ЛистИсточник.Cells(31 +​ + 1, 22).Value​ With Workbooks(«Конечный.xls»).Worksheets(«Имена файлов»)​
​ i ЛистКонечный.Cells(LastRowКонечный +​ ещё не брались.​ 4) = Cells(13,​ & «[» &​ Filename:=FilesToOpen(x)​ .xls файлов в​ текст в формате​ и данные файла​

​ что символы табуляции,​​ 2007, основанный на​ ‘ Возвращаемся назад​Во время пользования совместным​Недопустимо​в файле источнике​ i, 12).Value) =​
​ = 0 ЛистСводный.Cells(LastRowСводный​ .Range(«A» & .Cells.SpecialCells(xlCellTypeLastCell).Row​ 1, 15).Value =​ With Workbooks(«Конечный.xls»).Worksheets(«Имена файлов»)​
​ 4) ‘и т.д.​ myName & «]Лист1’!$C$5″​Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)​ одной папке.​
​ HTML независимо от​ нельзя будет легко​ разрывы строк и​ языке XML. В​
​ в книгу OldBook.Select​​ файлом ведется журнал​Реально​

​ в столбе X​​ False Then ЛистСводный.Cells(LastRowСводный​ + 1, 23).Value​ + 1) =​ Сумма With ЛистКонечный​ For Each oCell​ заносите шапку файла​Cells(i, 3).Formula =​x = x + 1​на листе 1​

​ формата исходного текста.​​ изменить. Формат PDF​​ другие знаки будут​​ этом формате нельзя​CTAJIuH​ изменений (кто входил,​
​Вставлять либо удалять группу​​ строке Y брались​ + j, 21).Value​ = 0 ЛистСводный.Cells(LastRowСводный​ ActiveWorkbook.Name End With​

CyberForum.ru

Работа с файлами Excel

​ .Range(.Cells(LastRowКонечный + 1,​​ In .Range(«A» &​ ‘теперь переходим к​ «='» & myPath​
​Wend​​ находится нужная информация.​Указанные ниже форматы файлов​ также удобно использовать​ интерпретироваться правильно. Сохраняется​

​ сохранять код макросов​:​ когда, что делал).​ ячеек​
​ данные,​ = ЛистИсточник.Cells(31 +​ + 1, 24).Value​ ‘Сохранение измений в​ 15), .Cells(LastRowКонечный +​ .Cells.SpecialCells(xlCellTypeLastCell).Row) If oCell.Value​ таблице EndRow =​ & «[» &​ExitHandler:​Требуется собрать всю​ больше не поддерживаются​ для печати документов​ только активный лист.​ Microsoft Visual Basic​Alex Dark​ Книга увеличивается. Начинает​Добавлять строку или столбец​потом в другом​ i, 12).Value Else​ = 0 ЛистСводный.Cells(LastRowСводный​ конечной книге. Workbooks(«Конечный.xls»).Save​ СколькоОбъединять, 15)).Merge End​ = ActiveWorkbook.Name Then​ Range(Range(«A1»), Range(«A1»).SpecialCells(xlLastCell)).Rows.count ‘определяем​ myName & «]Лист1’!$D$8″​Application.ScreenUpdating = True​ информацию с этих​ в Excel 2016, Excel​ в типографии.​Текст (Macintosh)​ для приложений (VBA)​, наверное я как​ «глючить».​Добавлять либо изменять условные​ файле в столбе​ ЛистСводный.Cells(LastRowСводный + j,​ + 1, 25).Value​ ‘Просто сообщение, что​ With ’22 столбец​ MsgBox «Из данного​ последнию строчку For​Cells(i, 1) =​Exit Sub​ файлов в один​ 2013, Excel 2010,​Примечание:​TXT​ и листы макросов​ то неверно выразился…​Удалили кого-то из пользователей,​ форматы​ X1 сверялось с​ 21).Value = 0​ = 0 ЛистСводный.Cells(LastRowСводный​ работа макроса завершена.​ If IsEmpty(ЛистИсточник.Cells(LastRowИсточник, 17).Value)​ файла-источника данные уже​ j = 32​ myName: i =​ErrHandler:​ .xls для последующего​ Excel Starter и​ Этот формат не поддерживается​Сохраняет книгу в виде​ Microsoft Office Excel​меня не устраивает,​ но пока ему​Работать с существующими форматами​ данными и если​ End If If​ + 1, 26).Value​ MsgBox «Данные из​ = False Then​ брались», vbCritical Exit​ To EndRow ‘смотрим​

​ i + 1:​​MsgBox Err.Description​
​ анализа.​ Excel 2007. Открывать​ в Excel 2007.​
​ текстового файла, разделенного​
​ 4.0 (XLM).​ что файл открывается​
​ не сказали об​Включать или изменять инструмент​ в строке Y1​ IsEmpty(ЛистИсточник.Cells(31 + i,​
​ = 0 GoTo​ файла-источника взяты», vbInformation​ ЛистКонечный.Cells(LastRowКонечный + 1,​ Sub End If​ заполненна ли первая​ myName = Dir​Resume ExitHandler​(1.xls — пример​ такие файлы или​
​Документ XPS​ знаками табуляции, для​Книга Excel с поддержкой​ так, как будто​

​ этом. Тогда блокировка​​ «Проверка данных»​​ оно сошлось, то​​ 8).Value) = False​
​ metka_2 ElseIf АктивнаяЯчейка.Column​ End SubЕщё будет​ 22).Value = ЛистИсточник.Cells(LastRowИсточник,​ Next oCell End​ ячейка в строчке​Loop: [A:C].Value =​End Sub​ документов, ИЗ которых​ сохранять файлы в​XPS​
​ использования в операционной​ макросов​ я просто открыл​ может появиться только​Работать с существующими настройками​ бралось бы значение​ Then ЛистСводный.Cells(LastRowСводный +​
​ <> 11 And​ макрос для проверки,​ 17).Value Else ЛистКонечный.Cells(LastRowКонечный​ With Set ЛистИсточник​ j If Len(Cells(j,​ [A:C].Value​Добавлено через 1 минуту​ берется информация. желтым​ этих форматах невозможно.​Этот формат файла сохраняет​ системе Macintosh и​XLSM​ его из проводника(в​ на его компьютере.​ проверки​ из столба Z1​ j, 23).Value =​ АктивнаяЯчейка.Column <> 17​ все ли файлы-источники​ + 1, 22).Value​ = ActiveWorkbook.Worksheets(1) Set​ 1).Text) <> 0​End Sub​здесь происходит просто​ обозначены ячейки нужные.​Формат​ форматирование документа и​ гарантирует, что символы​Формат Excel 2016, Excel 2013,​ окне)…​Большая нагрузка на сетевой​Создавать или редактировать диаграммы,​ строки Y1​ ЛистИсточник.Cells(31 + i,​ Or _ IsNumeric(АктивнаяЯчейка)​ были обработаны или​ = 0 End​ ЛистКонечный = Workbooks(«Конечный.xls»).Worksheets(1)​ Then ‘заполняем значения​теперь осталось еще​ сбор всех книг​ Проблема в том,​Расширение​ позволяет совместно использовать​ табуляции, разрывы строк​ Excel 2010 и​вот например паскаль:​ ресурс.​ сводные отчеты​и вставлялось бы​ 8).Value Else ЛистСводный.Cells(LastRowСводный​ = False Or​ нет.​ If With ЛистКонечный​ LastRowКонечный = ЛистКонечный.Cells.SpecialCells(xlCellTypeLastCell).Row​ и зтаблицы sh.Cells(Row,​ решить момент с​ в одну.​ что в диапазоне​Идентификаторы типа буфера обмена​ файлы. Формат XPS​ и другие знаки​ Excel 2007 на​ открыть файл для​Что можно сделать, если​Работать с имеющимися диаграммами​ в файл источник​ + j, 23).Value​ АктивнаяЯчейка.Font.Bold = False​Busine2009​ .Range(.Cells(LastRowКонечный + 1,​ If Selection.Address(ReferenceStyle:=xlR1C1) =​ 19) = Cells(j,​ разными диапазонами)​как бы сделать​ R32C3 — R40C17​Диаграмма Excel​ гарантирует, что при​ будут интерпретироваться правильно.​ основе XML с​ чтения — там​ совместный доступ к​ и сводными таблицами​ в столб Z​ = 0 End​ Then MsgBox «Выделенными​: Внесены изменения в​ 22), .Cells(LastRowКонечный +​ «R1C1″ Then ЛистКонечный.Cells(LastRowКонечный​ 14) sh.Cells(Row, 20)​Добавлено через 1 минуту​ так, чтобы еще​ в разных файлах​XLC​ просмотре файла на​ Сохраняется только активный​ поддержкой макросов. Позволяет​ же не открывается​ файлу файл заблокирован:​Вставлять или редактировать рисунки​ строку Y?​ If If IsEmpty(ЛистИсточник.Cells(31​ ячейками могут быть​ пояснительную записку.​ СколькоОбъединять, 22)).Merge End​ + 1, 4).Value​ = Cells(j, 13)​Busine2009​ и определенные ячейки..?​ меняется количество строк,​Форматы файлов Excel 2.0,​ компьютере и при​ лист.​ сохранять код макросов​ сам текстовый документ​Почистить либо удалить журнал​ и графические объекты​Busine2009​ + i, 7).Value)​ только R1C1, последняя​lalike​ With ’26 столбец​ = ЛистИсточник.Cells(8, 4).Value​ ‘ и т.д.​,​ да и еще​ соответственно и расположение​ 3.0 и 2.x​ его печати будет​Текст (MS-DOS)​ VBA и листы​ в окне блокнота)​ изменений.​Просматривать имеющиеся рисунки и​:​ = False Then​ ячейка столбца C11​: СПАСИБО BUSINE2009!!!!​ If IsEmpty(ЛистИсточник.Cells(LastRowИсточник, 11).Value)​ ЛистКонечный.Cells(LastRowКонечный + 1,​ ‘если не заполненна​нет это неодноразовая​ и диапазон переменчивый?​ ячеек. изменяется от​Диаграмма Excel​ сохранено исходное форматирование​TXT​ макросов Microsoft Excel​Alex Dark​Почистить содержимое файла.​ графические объекты​lalike​ ЛистСводный.Cells(LastRowСводный + j,​ или последняя ячейка​Все РАБОТАЕТ!!!​ = False Then​ 5).Value = ЛистИсточник.Cells(10,​ первая ячейка смотрим​ работа.​ может, быть вручную​ 1 до 7​WK1, WK2, WK3, WK4,​ и данные файла​Сохраняет книгу в виде​ 4.0 (XLM).​, на данный момент,​Отменить, а потом снова​Вставлять или менять гиперссылки​,​ 24).Value = ЛистИсточник.Cells(31​ столбца C17.» &​Busine2009​ ЛистКонечный.Cells(LastRowКонечный + 1,​ 4).Value ЛистКонечный.Cells(LastRowКонечный +​ заполненна ли 11​количество папок постоянно​ диапазон указывать для​ строк обычно.​ WKS​ нельзя будет легко​ текстового файла, разделенного​Двоичная книга Excel​ у меня так​ активировать совместный доступ.​Переходить по имеющимся гиперссылкам​а что в​ + i, 7).Value​ vbCr & _​: Вариант с автоматическим​ 26).Value = ЛистИсточник.Cells(LastRowИсточник,​ 1, 6).Value =​ ячейка Else If​ увеличивается. Каждй месяц​ каждого документа?​2.xls — это​Форматы файлов Lotus 1-2-3​ изменить.​ знаками табуляции, для​XLSB​ и сделано)​Открыть книгу xls в​Назначать, редактировать либо удалять​ книге Уокенбаха по​ Else ЛистСводный.Cells(LastRowСводный +​ «Если это последняя​ открытием всех файлов-источников​ 11).Value Else ЛистКонечный.Cells(LastRowКонечный​ ЛистИсточник.Cells(11, 4).Value ЛистКонечный.Cells(LastRowКонечный​ Len(Cells(j, 11).Text) <>​ по 10-20 папок.​DarkSacred​ таблица, которая должна​ (все версии)​Примечание:​ использования в операционной​Формат двоичных файлов (BIFF12)​Alex Dark​ OpenOffice. И снова​ пароли​ этому вопросу написано?​ j, 24).Value =​ ячейка столбца C11​ из указанной папки.​ + 1, 26).Value​ + 1, 7).Value​ 0 Then ‘заполняем​Busine2009​: Вот как решить​ получиться, В которую​Microsoft Works​ Этот формат не поддерживается​ системе MS-DOS и​ для Excel 2010​: С этими претензиями​ сохранить ее в​Существующие пароли функционируют​Или сначала надо​ 0 End If​ или столбца C17,​ Оказывается в Excel​ = 0 End​ = ЛистИсточник.Cells(20, 4).Value​ итоговые данные sh.Cells(Row,​: у вас же​ проблему с выбором​ вставляются данные).​WKS​ в Excel 2007.​ гарантирует, что символы​ и Excel 2007.​ в мелкософт.​ xls.​Ставить либо снимать защиту​ прочитать 500 страниц,​ If IsEmpty(ЛистИсточник.Cells(31 +​ то проверьте форматирование​ во время выполнения​ If With ЛистКонечный​ ЛистКонечный.Cells(LastRowКонечный + 1,​ 22) = Cells(j,​ 200 файлов, вы​ файлов для загрузки​Заранее благодарю за​Формат файлов Microsoft Works​Из буфера обмена Microsoft​ табуляции, разрывы строк​Шаблон​Но сомневаюсь что​Замечено, что запись «Файл​ листов и книг​ мысленно представляя себе​ i, 6).Value) =​ этой ячейки: «​ макроса есть возможность​ .Range(.Cells(LastRowКонечный + 1,​ 8).Value = ЛистИсточник.Cells(10,​ 11) sh.Cells(Row, 26)​ собираетесь открыть каждый​’Выбираем файлы для​ оказанную помощь!​ (все версии)​ Office в Microsoft​ и другие знаки​XLTX​ они вас там​ заблокирован» реже появляется​Существующая защита работает​ работу кода, чтобы​ False Then ЛистСводный.Cells(LastRowСводный​ & vbCr &​ выделять ячейки и​ 26), .Cells(LastRowКонечный +​ 11).Value ЛистКонечный.Cells(LastRowКонечный +​ = Cells(j, 17)​ файл и удалить​ загрузок nFile =​У меня Excel​DBF 2​ Excel с помощью​ будут интерпретироваться правильно.​Формат файлов по умолчанию​ будут слушать.​ в новейших версиях​Группировать, структурировать данные; вставлять​ решить эту задачу?​ + j, 25).Value​ _ «в этой​ перемещаться по листу:​ СколькоОбъединять, 26)).Merge End​ 1, 11).Value =​ ‘после заполнения итоговых​ то, что находится​ Application.GetOpenFilename( _ FileFilter:=»xls-файл​ 2007.​DBF​ команд​

​ Сохраняется только активный​​ для шаблонов Excel​Вы либо пользуетесь​

​ Excel.​​ подуровни​
​Я бы использовал​

​ = ЛистИсточник.Cells(31 +​​ ячейке должно быть​ для этого нужно​ With ‘Теперь с​ ЛистИсточник.Cells(17, 11).Value ЛистКонечный.Cells(LastRowКонечный​ данных выходим из​ ниже?​ (*.xls),*.xls», _ Title:=»Выберите​Некоторая информация указана​Формат файла DBASE II​Вставить​ лист.​ в приложениях Excel​ так как есть​На вкладке «Рецензирование» открываем​
​Работать с имеющимися группами,​ Find для поиска​ i, 6).Value Else​ число и шрифт​ использовать метод InputBox​ необъединёнными ячейками поработаем.​ + 1, 12).Value​ цикла For для​Я предлагаю для​ файлы для загрузки»,​ в примечаниях.(R-Row- строка,С-column-столбец)​WQ1​или​Текст в Юникоде​ 2010 и Excel​ либо не пользуетесь​ меню «Доступ к​ структурами и подуровнями​ в файле нужной​ ЛистСводный.Cells(LastRowСводный + j,​ должен быть жирным.»​ (не путать с​ For i =​ = ЛистИсточник.Cells(17, 15).Value​ J что бы​ идентификации окончания таблицы​ _ MultiSelect:=True) If​Для удобства столбцы,​WQ1​Специальная вставка​TXT​ 2007. В этом​ ими вовсе.​ книге».​Записывать, изменять или просматривать​ информации, и Offcet,​ 25).Value = 0​ & vbCr &​ функцией InputBox). Перед​ 1 To LastRowИсточник​ ЛистКонечный.Cells(LastRowКонечный + 1,​ дальше ни чего​ использовать R40C11.​ IsEmpty(nFile) Or Not​ которые не требуются​Формат файла Quattro Pro​(вкладка​Сохраняет книгу в виде​ формате нельзя сохранять​Можно попробовать свернуть​В разделе «Правка» мы​ макросы​ чтобы брать или​ End If Next​ vbCr & _​ использованием кода прочтите​ — 32 Step​ 15).Value = 0​ не смотрелось Exit​R30C11 постоянно. R40C11​ IsArray(nFile) Then Exit​ в заполнении скрыты.​ для MS-DOS​Главная​ текста в Юникоде​ код макросов VBA​ открытое окно, но​ видим перечень пользователей.​Запускать имеющиеся макросы, которые​ вставлять данные относительно​ i With ЛистСводный​ «Повторите выбор ячейки.»,​ Руководство пользователю.​ 2 j =​ ЛистКонечный.Cells(LastRowКонечный + 1,​ For End If​ — жирным оформлено.​ Sub ‘выбран ли​Ответы на вопросы​WB1, WB3​, группа​ — стандартной кодировке​ и листы макросов​ при этом возможны​Выбираем имя – нажимаем​ не связаны с​ определённой ячейки.​ .Cells(LastRowСводный + 1,​ vbCritical GoTo metka_1​Sub m_1() Dim​ j + 1​ 17).Value = 0​ End If Row​ Если между R30C11​ файл для загрузок​1. Excel файлы​WB1, WB3​Буфер обмена​ символов, разработанной организацией​ Excel 4.0 (XLM).​ проблемы в работе.​ «Удалить».​ недоступными задачами​lalike​ 4).Value = ЛистИсточник.Cells(8,​ End If LastRowИсточник​ ЛистИсточник As Excel.Worksheet​ If IsEmpty(ЛистИсточник.Cells(31 +​ ЛистКонечный.Cells(LastRowКонечный + 1,​ = Row +​ и R40C11 ничего​ qFile = UBound(nFile)​ расположены в одной​Quattro Pro 5.0 и​, кнопка​ Unicode Consortium.​Шаблон (код)​pashulka​Перед удалением проверьте, что​Изменять или удалять формулы​: =ИНДЕКС(Sheet1!$C$3:$K$2848;ПОИСКПОЗ(D3028;Sheet1!$C$4:$C$2849;0);9)​ 4).Value .Range(.Cells(LastRowСводный +​ = АктивнаяЯчейка.Row ‘Заполним​ Dim ФайлИсточник As​ i, 14).Value) =​ 19).Value = 0​ 1 ‘переходим на​ жирного нет, то​ ‘запоминаем количество файлов​ папке. c:/1/ например​ 7.0 для Windows.​Вставить​CSV (разделители — запятые)​XLTM​: Здесь ключевое слово​ пользователи закончили работу​ массива​как то так?)​ 1, 4), .Cells(LastRowСводный​ сначала столбец 17​ Excel.Workbook Dim ЛистСводный​ False Then ЛистКонечный.Cells(LastRowКонечный​ ЛистКонечный.Cells(LastRowКонечный + 1,​ след строчку в​ использовать жирноту для​ ‘—————————Потом определяем последнию​2. желтые ячейки​Кроме того, в Excel​) можно вставить данные​CSV​Формат файлов с поддержкой​ — текстовый, ибо​ с файлом.​Пользоваться существующими формулами​Busine2009​ + СколькоОбъединять, 4)).Merge​ в конечном файле,​ As Excel.Worksheet Dim​ + j, 19).Value​ 20).Value = 0​ общей таблице Next​ поиска последней строки​ активную строку​3. для удобства​ Starter больше не​ перечисленных ниже форматов.​Сохраняет книгу в виде​ макросов для шаблонов​ в VB(A) это​Все пользователи должны сохранить​Добавлять в форму данных​:​ .Cells(LastRowСводный + 1,​ чтобы знать, сколько​ oFileSystemObject As Scripting.FileSystemObject​ = ЛистИсточник.Cells(31 +​ ЛистКонечный.Cells(LastRowКонечный + 1,​ j ‘————————— book.Close​ интересующей нас таблицы.​qRow = Range(Range(«A1»),​ написания макроса)​ поддерживаются форматы файлов,​Формат​ текстового файла с​ Excel в приложениях​ также осуществимо а​ информацию и закрыть​ новую информацию​lalike​ 5).Value = ЛистИсточник.Cells(10,​ объединять ячеек в​ Dim oFolder As​ i, 14).Value Else​ 21).Value = 0​ Next i​lalike​ Range(«A1»).SpecialCells(xlLastCell)).Rows.countПоможет только если​Busine2009​ указанные ниже. Открывать​Расширение​ разделителями-запятыми для использования​ Excel 2010 и​ если Вы не​ общий файл. Если​Искать информацию в форме​,​ 4).Value .Range(.Cells(LastRowСводный +​ дальнейшем. For i​ Scripting.Folder Dim oFile​ ЛистКонечный.Cells(LastRowКонечный + j,​ ЛистКонечный.Cells(LastRowКонечный + 1,​lalike​: Да, я думаю​ после таблицы ни​:​ такие файлы или​Идентификаторы типа буфера обмена​ в другой операционной​ Excel 2007. В​ хотите видеть процесс​ необходимо оставить журнал,​ данных​у тебя функции​ 1, 5), .Cells(LastRowСводный​ = 1 To​ As Scripting.File Dim​ 19).Value = 0​ 22).Value = 0​:​ можно использовать жирность.​ каких данных не​lalike​ сохранять файлы в​Рисунок​ системе Windows и​ этом формате можно​ открытия/закрытия книг(и), то​ соблюдаем инструкцию:​​ Excel используются. Я​ + СколькоОбъединять, 5)).Merge​ LastRowИсточник — 31​ ИмяПапки As String​ End If If​ ЛистКонечный.Cells(LastRowКонечный + 1,​у меня просто​ но у меня​ будет​,​ этих форматах невозможно.​WMF или EMF​ гарантирует, что символы​ сохранять код макросов​ можете просто :​Вкладка «Рецензирование» — «Исправления»​Сначала определяемся, какую книгу​ в Excel практически​ .Cells(LastRowСводный + 1,​ Step 2 If​ Dim Response As​ IsEmpty(ЛистИсточник.Cells(31 + i,​ 23).Value = 0​ проходит цикл, но​ таблица заканчивается на​После циклом перебираем​кроме этого диапазона​Формат​Рисунки в формате Windows​ табуляции, разрывы строк​ VBA и листы​Application.ScreenUpdating = False​ — «Выделить исправления».​ мы «откроем» для​ не работал, поэтому​ 6).Value = ЛистИсточник.Cells(11,​ IsEmpty(ЛистИсточник.Cells(31 + i,​ String Dim АктивнаяЯчейка​ 13).Value) = False​ ЛистКонечный.Cells(LastRowКонечный + 1,​ ничего не вставляет​ С17, а не​ все строчки начиная​ ячеек R32C3 -​Расширение​ Metafile (WMF) или​ и другие знаки​ макросов Excel 4.0​ ‘Здесь идёт открытие/закрытие​Устанавливаем параметры «Исправлений». По​ редактирования сразу несколькими​ не могу сказать,​ 4).Value .Range(.Cells(LastRowСводный +​ 1).Value) = False​ As Range Dim​ Then ЛистКонечный.Cells(LastRowКонечный +​ 24).Value = 0​Добавлено через 32 минуты​ С11.​ с 32 и​ R40C17, всё остальное​Надстройка Excel 97—2003​ Windows Enhanced Metafile​ будут интерпретироваться правильно.​ (XLM).​ книги Application.ScreenUpdating =​

​ времени – «все».​​ участниками. Создаем новый​
​ правильно у тебя​ 1, 6), .Cells(LastRowСводный​
​ Then j =​ i As Long​
​ j, 20).Value =​ ЛистКонечный.Cells(LastRowКонечный + 1,​а как сделать​

​таким образом, у​​ по qRow, при​​ полностью одинаковое во​​XLA​
​ (EMF).​ Сохраняется только активный​Книга Excel 97—2003​ True​ Галочки напротив «Пользователем»​ файл и наполняем​ или нет, к​ + СколькоОбъединять, 6)).Merge​ j + 1​

​ Dim j As​​ ЛистИсточник.Cells(31 + i,​ 25).Value = 0​ так, чтобы​ нас два ячейки​

​ этом ставим условие​​ всех файлах?​​Надстройка Microsoft Excel​​Примечание​
​ лист.​XLS​Hugo121​ и «В диапазоне»​ его информацией. Либо​ тому же я​

​ .Cells(LastRowСводный + 1,​​ ЛистСводный.Cells(LastRowСводный + j,​
​ Long Dim LastRowИсточник​ 13).Value Else ЛистКонечный.Cells(LastRowКонечный​ ЛистКонечный.Cells(LastRowКонечный + 1,​в файле источнике​ обозначены жирным. R40C11​ если Len(Cells(i,1).Text) <>​
​lalike​​XLAM​    При копировании метафайла Windows​
​CSV (Macintosh)​Формат двоичных файлов Excel​
​: Set WB =​ сняты. Напротив «вносить​ открываем существующий.​ задачу твою понял​
​ 7).Value = ЛистИсточник.Cells(20,​ 17).Value = ЛистИсточник.Cells(31​ As Long Dim​ + j, 20).Value​ 26).Value = 0​ в столбе X​ и R40C17.​ 0(проверяет что данная​: Да! Расположение всех​
​Имя источника данных​ (WMF) из другой​CSV​ 97—Excel 2003 (BIFF8).​

​ Workbooks.Open(myPath)так будет лучше.​​ изменения на отдельный​​Переходим на вкладку «Рецензирование».​​ не до конца.​
​ 4).Value .Range(.Cells(LastRowСводный +​ + i, 1).Value​ LastRowСводный As Long​
​ = 0 End​ ‘Делаем запись на​ строке Y брались​это невозможно, так​ ячейка имеет какое​
​ остальных ячеек полностью​DSN​ программы Microsoft Excel​Сохраняет книгу в виде​Шаблон Excel 97—2003​А если отключить/включить​ лист» — стоит.​

​ Диалоговое окно «Доступ​​Я говорил про​
​ 1, 7), .Cells(LastRowСводный​

​ End If Next​​ Dim СколькоОбъединять As​​ If If IsEmpty(ЛистИсточник.Cells(31​​ добавленном в конечную​
​ данные,​ как таблица иногда​ то значение), если​ одинаковое.​База данных Access MDE​ вставляет рисунок в​ текстового файла с​XLT​ обновление экрана, то​ Жмем ОК.​
​ к книге».​ инструменты VBA.​
​ + СколькоОбъединять, 7)).Merge​ i j =​ Long Dim Сумма​ + i, 12).Value)​ книгу листе, что​потом в другом​ заходит за рамки​

​ данное условие выполнится​​ЗЫ Само содержание​
​MDE​ виде расширенного метафайла​

​ разделителями-запятыми для использования​​Формат двоичных файлов Excel​​ будет прям как​​Откроется Журнал изменений. Его​
​Управление доступом к файлу​у вас эта​ .Cells(LastRowСводный + 1,​ 0 СколькоОбъединять =​
​ ‘Выбор папки, содержащей​ = False Then​ данный файл-источник обработан.​ файле в столбе​

​ этого лимита​​ то копируйте данные​ ячеек разное.​Подключение к данным Office​
​ (EMF).​ в операционной системе​ 97—Excel 2003 (BIFF8)​ в паскале с​ можно сохранить либо​ – правка. Ставим​ формула работает? Я​ 8).Value = ЛистИсточник.Cells(10,​ ЛистСводный.Cells.SpecialCells(xlCellTypeLastCell).Row — LastRowСводный​ файлы-источники. With Application.FileDialog(msoFileDialogFolderPicker)​ ЛистКонечный.Cells(LastRowКонечный + j,​

CyberForum.ru

Совместный доступ к файлу Excel одновременно

​ With Workbooks(«Конечный.xls»).Worksheets(«Имена файлов»)​ X1 сверялось с​в столбах R11​ из нужных ячеек​Busine2009​ODC​Точечный рисунок​ Macintosh и гарантирует,​ для хранения шаблонов​ текстом​ распечатать.​

​ галочку напротив «Разрешить​ просто с формулами​ 11).Value .Range(.Cells(LastRowСводный +​ ‘Будем отдельно заносить​ If .Show =​ 21).Value = ЛистИсточник.Cells(31​

Особенности работы с общим файлом

​ .Range(«A» & .Cells.SpecialCells(xlCellTypeLastCell).Row​ данными и если​ и R17 -​ в свою таблицу​

​:​

• ​Файл канала передачи данных​
• ​BMP​ что символы табуляции,​
• ​ Excel.​
• ​Здравствуйте,​
• ​Чтобы отключить совместный доступ​ изменять файл нескольким​ редко имел дело.​

​ 1, 8), .Cells(LastRowСводный​ данные в отношении​ 0 Then Exit​ + i, 12).Value​

​ + 1) =​ в строке Y1​ больше нет других​

​ 11).Value = ЛистИсточник.Cells(17,​

Как сделать совместный доступ к файлу Excel?

​ имеющих объединение. ‘Сначала​ ‘Проверка, что выбрана​ 0 End If​ конечной книге. Workbooks(«Конечный.xls»).Save​ из столба Z1​, Вы не могли​ я надеюсь в​

1. ​ раз новые оказываются​ который предполагается использовать,​XLS​
2. ​ только активный лист.​ 5.0/95 (BIFF5).​ — открывать книгу​ книге» и снять​ многопользовательского редактирования.​
3. ​lalike​ 11).Value .Range(.Cells(LastRowСводный +​ поработаем с теми​
4. ​ нужная папка. Response​ If IsEmpty(ЛистИсточник.Cells(31 +​ ‘Просто сообщение, что​ строки Y1​ бы поподробнее?​ ваших файлах не​ в папке? И​ можно предпринять действия,​
5. ​Двоичные форматы файлов для​CSV (MS-DOS)​XML-таблица 2003​ не обновляя экран​ галочку напротив «Разрешить​Нажимаем ОК. Если мы​
6. ​: да, можно.​ 1, 11), .Cells(LastRowСводный​ ячейками, которые нужно​ = MsgBox(«Выбрана папка​ i, 8).Value) =​

​ работа макроса завершена.​и вставлялось бы​DarkSacred​

​ меняются).​ нужны ли связи​

1. ​ описанные ниже.​
2. ​ Excel версий 5.0/95​CSV​XML​ компьютера.​ изменять файл нескольким​
3. ​ открываем общий доступ​она работает, но​ + СколькоОбъединять, 11)).Merge​ объединить. ’15 столбец​ » & ИмяПапки,​

Открытие книги с совместным доступом

1. ​ False Then ЛистКонечный.Cells(LastRowКонечный​
2. ​ MsgBox «Данные из​
3. ​ в файл источник​
4. ​: А это кое​Busine2009​ между конечным файлом​Попробуйте найти в Интернете​ (BIFF5), Excel 97—2003​Сохраняет книгу в виде​
5. ​Формат файлов XML-таблиц 2003​Но если работа​ пользователям».​ к новой книге,​

​ для других диапазонов​ .Cells(LastRowСводный + 1,​ For i =​

​ vbOKCancel) If Response​ + j, 23).Value​ файла-источника взяты», vbInformation​ в столб Z​ — что находится​:​ и файлом-источником -​ компанию, выпускающую конвертеры​ (BIFF8) и приложения​ текстового файла с​

1. ​ (XMLSS).​ со второй книгой​В списке должен остаться​ то выбираем ей​Busine2009​ 12).Value = ЛистИсточник.Cells(17,​
2. ​ 1 To LastRowИсточник​ = vbCancel Then​ = ЛистИсточник.Cells(31 +​ Exit Sub ElseIf​ строку Y?​ в столбце «A».​
3. ​lalike​ ='[1.xls]Лист1′!R20C4?​ файлов для форматов,​ Excel 2010 (BIFF12).​ разделителями-запятыми для использования​Данные XML Data​
4. ​ ограничивается только обработкой​ один пользователь –​

​ название. Если совместное​:​ 15).Value .Range(.Cells(LastRowСводный +​

1. ​ — 31 Step​ Exit Sub ‘Активизация​
2. ​ i, 8).Value Else​
3. ​ ActiveCell.Column <> 11​Busine2009​
4. ​ Если нет то​,​lalike​ не поддерживаемых Excel.​

​SYLK​ в операционной системе​XML​ данных — то​

Как удалить пользователя

1. ​ Вы.​ пользование предполагается для​lalike​
2. ​ 1, 12), .Cells(LastRowСводный​ 2 If IsEmpty(ЛистИсточник.Cells(31​
3. ​ объектов, связанных с​ ЛистКонечный.Cells(LastRowКонечный + j,​

​ And ActiveCell.Column <>​:​ он будет игнорировать​

Как отключить режим совместного доступа в Excel

​а у вас​:​Сохраните файл в формате,​SLK​ MS-DOS и гарантирует,​

1. ​Формат данных XML.​ есть хорошая альтернатива​
2. ​CTAJIuH​ существующего файла –​,​ + СколькоОбъединять, 12)).Merge​ + i, 12).Value)​ работой с файлами.​ 23).Value = 0​ 17 Or _​
3. ​lalike​ это кое -​ конечный файл уже​

​Busine2009​ который поддерживает другая​Формат Symbolic Link.​ что символы табуляции,​Надстройка Microsoft Excel​ в виде использования​: Здравствуйте!​ жмем ОК.​

​надо попробовать формулы​ End With metka_2:​ = False Then​

exceltable.com

VBA работа с файлами Excel

​ Set oFileSystemObject =​​ End If If​
​ IsNumeric(ActiveCell) = False​,​ что, т.к. стоит​ заполнен и нужно​
​,​ программа, а затем​DIF​ разрывы строк и​XLAM​ технологии ADO. Результаты​Вопрос в следующем:​Открываем меню Microsoft Office.​

​ Excel обуздать для​​ ФайлИсточник.Close j =​
​ Сумма = Сумма​ CreateObject(«Scripting.FileSystemObject») Set oFolder​

​ IsEmpty(ЛистИсточник.Cells(31 + i,​​ Or ActiveCell.Font.Bold =​​вот код. Т.к.​​ проверка Len(Cells(i,1).Text) <>​ только связи обновлять​Нет, связи не​
​ экспортируйте его из​DIF​
​ другие знаки будут​Формат файлов надстроек Excel​ моих экспериментов с​ Как реализовать чтение​ Выбираем команду «Сохранить​ вашей задачи. У​
​ 0 СколькоОбъединять =​ + ЛистИсточник.Cells(31 +​ = oFileSystemObject.GetFolder(ИмяПапки) ‘Проверка,​

​ 7).Value) = False​​ False Then MsgBox​ файл сделан в​
​ 0​ или нужно заносить​ нужны. Нужны только​ этой программы в​
​Формат обмена данными.​ интерпретироваться правильно. Сохраняется​ 2010 и Excel​ этой альтернативой прилагаю​
​ и запись данных​ как». Выбираем тот​ вас хорошая идея.​ 0 Сумма =​ i, 12).Value End​ что в папке​ Then ЛистКонечный.Cells(LastRowКонечный +​ «Выделенными ячейками могут​ Excel 2003, а​lalike​ данные из файлов-источников?​ цифры, данные.​ формат, который поддерживается​Текст (разделители — знаки​ только активный лист.​ 2007 на основе​ (макрос в книге​

​ из другой книги?​​ формат файла сохранения,​​Я создал ситуация,​​ 0 Next oFile​ If Next i​
​ нет файлов, кроме​ j, 24).Value =​ быть только R1C1,​ в коде есть​:​А если в​
​В папке определенное​ в Excel.​ табуляции)​DIF​ XML с поддержкой​ CTAJIUH_UI.xlsm, обрабатываемые данные​
​Т.е. имеется форма(находится​​ который «пойдет» на​ что ваша формула​ ‘Просто сообщение, что​

​ ЛистСводный.Cells(LastRowСводный + 1,​​ файлов Excel. For​ ЛистИсточник.Cells(31 + i,​
​ последняя ячейка столбца​ команда, связанная с​DarkSacred​
​ файл-источник внесли изменения,​ количество файлов, новые​Не преобразуемые в другие​TXT​
​DIF​ макросов. Надстройка —​ и результаты в​ в одном файле),​

​ всех пользовательских компьютерах.​​ наконец у меня​ работа макроса завершена.​ 15).Value = Сумма​ Each oFile In​ 7).Value Else ЛистКонечный.Cells(LastRowКонечный​ C11 или последняя​ сохранением, то будет​, А Вы не​
​ то вы должны​ не появляются.​ форматы свойства и​Текстовый формат со знаками​

​Формат обмена данными. Сохраняет​​ это программа, обеспечивающая​ книге CTAJIUH_DB.xlsx).​
​ с помощью которой​Местом сохранения выбираем сетевой​ заработала.​ MsgBox «Сбор данных​ With ЛистСводный .Range(.Cells(LastRowСводный​

​ oFolder.Files If InStr(oFile.Type,​​ + j, 24).Value​​ ячейка столбца C17.»​ появляться диалоговое окно,​ могли бы полный​ эти данные поместить​этими связями я​
​ форматирование документов Microsoft​ табуляции в качестве​ только активный лист.​ возможность выполнения дополнительного​С уважением, Аксима​ пользователь взаимодействует с​ ресурс / сетевую​booleg​ из файлов-источников завершён.»,​ + 1, 15),​ «Excel») = 0​ = 0 End​ & vbCr &​
​ связанное с разными​

CyberForum.ru

​ макрос выложить?​

Хитрости »

11 Февраль 2018              31708 просмотров

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

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query — что такое и почему её необходимо использовать в работе?

Ниже можно скачать файлы, которые применялись в статье. В архиве два файла бюджета(в папке Бюджет) и готовая модель с запросом(файл «Сводный»).
В файле с запросом так же применен прием получения пути к файлам динамически из папки, которая расположена в папке с файлом запроса. Подробнее про это можно прочитать в статье: Относительный путь к данным PowerQuery
Скачать готовую модель:

  Модель агрегации файлов.zip (53,5 KiB, 1 337 скачиваний)

Видеоинструкция:

Для ведения бюджета применяется таблица такого вида:

Сама таблица преобразована заранее в так называемую «умную» таблицу: выделяем таблицу -вкладка Вставка(Insert) и выбрать Таблица(Table):

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

Перейдем к реализации.
Создаем новую пустую книгу, переходим на вкладку Данные(или Power Query) —Получить данные —Из файла —Из папки:

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

Нажимаем Ок.
Появится окно, в котором будет список всех файлов в выбранной папке. Нажимаем Изменить и попадем в редактор запросов Power Query. Здесь пошагово мы и будем делать все преобразования отчетов для их объединения и приведения к нужному виду.
Для начала удалим лишние столбцы, оставив только два столбца:

Content

и

Name

:

Для этого выделяем лишние столбцы с зажатой клавишей Shift и нажимаем Delete(или правая кнопка мыши —Удалить столбцы).
Теперь надо получить таблицы из файлов. Для этого переходим на вкладку Добавить столбец -Пользовательский столбец. В появившемся окне даем имя новому столбцу(у меня это Данные), а в поле формулы вписываем такую функцию:

=Excel.Workbook([Content])

Нажимаем Ок.
В отчет будет добавлен новый столбец. Необходимо его «развернуть» — получить все данные из каждого файла. Для этого нажимаем на этом столбце значок в виде двух разнонаправленных стрелок, снимаем галочку «Использовать исходное имя столбца как префикс» и нажимаем Ок:

Будет добавлено еще два столбца, из которых аналогичным образом разворачиваем столбец Data(нажатием на значок в виде двух разнонаправленных стрелок). Там будут наименования вроде Column1, Column2 и т.д. – это нормально, выгружаем все как есть. Получится такая картина:

Теперь столбцы

Content

,

Name

и

Name.1

можно удалить (в столбце Name записано имя файла, поэтому если оно нужно – можно оставить на время отладки запроса. Но впоследствии данные будут объединены и просуммированы и оно все равно будет лишним).
Т.к. у нас реальные данные в таблицах начинаются не с первой строки и имеется шапка – необходимо убрать все лишние строки, чтобы исключить ошибки при дальнейшем суммировании данных. Для этого сначала в Column2 раскрываем меню фильтра и убираем галочки со значений

NULL

:

А в Column1 в фильтре убираем галочку с пункта «Статьи». Теперь первой строкой данных у нас идут названия месяцев. Делаем их заголовками: вкладка Преобразование —Таблица —Использовать первую строку в качестве заголовков:

Т.к. первый столбец теперь будет иметь не совсем понятное имя вроде Column1 — имеет смысл переименовать его в «Статьи».
Далее выделяем все столбцы месяцев и столбец Итого -вкладка Преобразование -группа Любой столбец -раскрываем список Тип данных и выбираем Десятичное число:

Теперь надо объединить все одинаковые строки статей и просуммировать данные по ним за каждый месяц. Выделяем столбец

Статьи

вкладка Преобразование —Таблица —Группировать по:

В появившемся окне сразу выбираем режим Дополнительно и указываем параметры группировки:

Группировка – оставляем поле

Статьи

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

Сумма

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

Все файлы в папке будут просмотрены, преобразованы и просуммированы.

Так же см.:
Power Query — что такое и почему её необходимо использовать в работе?
План-фактный анализ в Excel при помощи Power Query
Собрать данные с таблиц с изменяющимися столбцами в PowerQuery
Замена значений по списку в PowerQuery

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Время прочтения: 3 мин.

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

Решить данную задачу поможет Power Query – встроенный в MS Excel инструмент обработки данных.

Рассмотрим пример объединения файлов с общей «шапкой» и данными на разных листах:

1. Импортируем файлы («Данные» → «Создать запрос» → «Из файла» → «Из папки») и указываем путь:

2. Power Query покажет все файлы, размещенные по указанному адресу:

3. Нажимаем «изменить», переходим в редактор запросов и фильтруем нужные файлы:

4. Удаляем все столбцы кроме «Content»:

5. Добавляем новый столбец и вводим формулу с учетом регистра:

6. Раскрываем столбец «Пользовательская», снимаем флажок «Использовать исходное имя…».

Если есть предупреждение о незавершенном списке, то жмем «загрузить ещё»:

7. Удаляем столбцы кроме «Data», раскрываем его. Если есть предупреждение, то жмем «загрузить ещё»:

8. Power Query отразит сводные данные:

9. Ставим первую строку как заголовок: «Главная» → «Использовать первую строку…»:

10. Скрываем фильтром лишние заголовки и выгружаем данные на новый/существующий лист:

Готово! Информация со всех листов всех файлов собрана в общую таблицу при существенно меньших трудозатратах по сравнению с ручным копированием.

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