Содержание

В Департаменте экономики рассказали особенностях работы с электронной трудовой книжкой

В 2021 году работодатели:

1. Выдадут трудовые книжки тем, кто отказался от их ведения.
 Это касается тех работников, которые написали заявления об отказе от них. Последняя запись в трудовой книжке будет о том, что работник подал заявление о предоставлении сведений о трудовой деятельности в электронном виде.Отказ от ведения бумажной трудовой книжки не значит, что ее теперь можно выкинуть! В электронной трудовой будут сведения о работе только начиная с 2020 года. Поэтому при трудоустройстве к новому работодателю работник предоставит и электронные сведения, и бумажную трудовую. Однако теперь работодатель просто ознакомится с ее содержанием, а затем отдаст назад работнику. Никакие записи при трудоустройстве в трудовую книжку, от дальнейшего ведения которой сотрудник отказался, не вносятся.

2. Продолжат вести бумажные трудовые тем, кто не отказался от них.


Для тех, кто решил оставить бумажную трудовую, ничего не изменится – работодатель продолжит вести бумажную трудовую книжку по старым правилам, но также, как и на остальных работников, будет заполнять отчет СЗВ-ТД. СЗВ-ТД расшифровывается как «Сведения о трудовой деятельности зарегистрированного лица» и напрямую связано с переходом на электронные трудовые книжки.

3. Выдадут сведения о трудовой деятельности
При увольнении работников, которые отказались от бумажной трудовой, работодатель должен выдать вместо нее сведения о трудовой деятельности. Это специальная форма СТД-Р. По желанию работника ее можно предоставлять в электронном виде. Тем сотрудникам, которые сохранили бумажную трудовую, работодатель не должен предоставлять сведения из электронной трудовой. Они будут их получать самостоятельно в МФЦ, отделении Пенсионного фонда или на сайте госуслуг.

4. Будут оформлять новых сотрудников на работу по новым правилам

Новые сотрудники должны будут предъявлять новому работодателю как бумажную трудовую книжку (если она у них была заведена), так и сведения о трудовой деятельности, полученные либо у прежнего работодателя (форма СТД-Р – «сведения о трудовой деятельности»), либо самостоятельно. Если в трудовой книжке будет запись о том, что она выдана на руки в соответствии со статьей 66.1 Трудового кодекса РФ, а в сведениях о трудовой деятельности сказано, что этот человек отказался от бумажной трудовой, значит, записи о приеме на работу в трудовой книжке не будет. Работодатель должен подать отчет в Пенсионный фонд (СЗВ-ТД).

Если же в трудовой книжке записи о ее выдаче на руки в соответствии со статьей 66.1 Трудового кодекса РФ нет, а есть обычная запись об увольнении с прежнего места, и в сведениях о трудовой деятельности не сказано, что сотрудник отказался от бумажной трудовой, значит, работодатель, как раньше, сделает в трудовой книжке работника запись о приеме на работу, оставить книжку у себя. Но еще и подаст отчет СЗВ-ТД.

А вот обязанности заводить бумажные трудовые книжки тем новым сотрудникам, у которых их никогда не было, у работодателей больше не будет. Новички рынка труда автоматически будут «получать» только электронные трудовые.
Если бумажная трудовая книжка у нового сотрудника до 2021 года была, но он ее потерял или она испорчена, он, как и сейчас, сможет потребовать, чтобы работодатель завел ему новую трудовую.

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

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

а) временной нетрудоспособности;
б) отпуска;
в) отстранения от работы в случаях, предусмотренных Трудовым кодексом Российской Федерации, другими федеральными законами, иными нормативными правовыми актами Российской Федерации;

2) лица, имеющие стаж работы по трудовому договору (служебному контракту), но по состоянию на 31 декабря 2020 года не состоявшие в трудовых (служебных) отношениях и до указанной даты не подавшие одно из письменных заявлений.

Зачем нужна электронная трудовая книжка и как ее получить – Инструкции и советы – Москва и Подмосковье

По словам Софьи Повзиковой, руководителя отдела по кадровому администрированию «Coleman Services», до 30 июня 2020 года включительно работодатели должны уведомить всех сотрудников (в том числе совместителей, пенсионеров, иностранных работников) в письменной форме об изменениях в трудовом законодательстве, а также о праве работника подать письменное заявление и сделать выбор. В зависимости от него работодатель либо продолжит вести трудовую книжку в бумажном виде в соответствии со статьей 66 Трудового кодекса Российской Федерации, либо будет предоставлять работнику сведения о трудовой деятельности в соответствии со статьей 66.1 ТК РФ в новом порядке.

Заявление в произвольной форме надо подать до 31 декабря 2020 года (совместители делают свой выбор по основному месту работы).

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

2 Закона N 439-ФЗ), а также подавать сведения о трудовой деятельности этих работников в электронной форме. То есть работодатель фактически будет вести двойную работу, отмечает Широкова.

Если работодатель получил заявление о выборе ведения трудовой книжки в порядке статьи 66.1 ТК РФ, он должен выдать сотруднику его бумажную трудовую книжку. При этом он вносит в нее закрывающую запись, делаются соответствующие отметки в Книге учета ведения трудовых книжек.

Илья Данцкер не советует торопиться и переходить на ЭТК. По его мнению, надежнее остаться с бумажной версией. В этом случае работодатель все равно будет вести вашу электронную трудовую книжку, представляя соответствующие сведения в ПФР, так что она всегда будет обновленной и актуальной, и вы сможете перейти на нее, как только захотите. Но в качестве подстраховки у вас будет и актуальная версия бумажной книжки, что куда надежнее, чем полагаться исключительно лишь на сервера ПФР.

Как бы то ни было, скоро все в любом случае перейдут на ЭТК, а бумажные трудовые книжки уже не будут вестись на тех работников, которые впервые трудоустроятся в 2021 году, добавила Ольга Широкова.

У россиян появится возможность завести электронную трудовую книжку

Государственная Дума приняла в третьем, окончательном чтении поправки в Трудовой кодекс и ряд других законов, вводящие в России электронные трудовые книжки.

В соответствии с документом, с 1 января 2021 года работодатели по выбору работника смогут вести трудовые книжки в электронном виде. До 30 июня 2020 года работодатель обязан в письменной форме уведомить каждого работника о праве выбора между бумажной или электронной трудовой книжкой.

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

При этом лицам, впервые поступающим на работу после 31 декабря 2020 года, бумажные трудовые книжки оформляться не будут.

«Изменения затронут около 60 млн работников и 8,4 млн хозяйствующих субъектов, включая предпринимателей без образования юридического лица. Важно, что переход на электронные трудовые книжки будет добровольным и постепенным», — говорил ранее Председатель ГД Вячеслав Володин Володин
Вячеслав Викторович Председатель Государственной Думы Федерального Собрания Российской Федерации восьмого созыва. Избран по избирательному округу № 163 (Саратовский – Саратовская область) .

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

ТК не разрешает иметь две трудовые книжки, а Пенсионный не возражает

Вся суть наших законов.

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

То есть, в Пенсионном фонде считают: есть у работника одна книжка — хорошо, посчитаем стаж по ней. Есть вторая — ещё лучше — приплюсуем стаж, основываясь и на данных из этой книжки.
Но не все так гладко. Имеет место противоречие. Сейчас со всем разберемся.

Почему у работника может быть две трудовые?

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

О том, насколько это законно, я расскажу чуть позже.

Еще пара причин, по которым трудовых книжек может быть две:

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

Что говорит Трудовой кодекс?

А там все очень просто: есть ст. 66, в которой написано: если работник трудится где-то по совместительству, то, по его желанию, начальник на основной работе вносит сведения об этом в трудовую книжку.
То есть, работодатель в компании, где осуществляется работа по совместительству, никаких записей в трудовой делать не должен.
В принципе, подобные записи можно считать недействительными. Но Пенсионный фонд на это не обращает внимания, указывает, что все записи им будут учитываться. Это и хорошо. Меньше обычные люди — работники, будут страдать.

А вот потеря или «утаивание» книжки — это совсем другое дело. Но ПФР, судя по всему, разбираться не будет.

Гость, заканчивается набор на курс по пяти новым ФСБУ

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

В учебном пособии показаны три способа объединения файлов Excel в один: копирование вкладок листа, запуск VBA и использование инструмента «Копировать рабочие листы».

Гораздо проще обрабатывать данные в одном файле, чем переключаться между многочисленными источниками. Однако объединение нескольких книг Excel в один файл может быть громоздким и длительным процессом, особенно если книги, которые необходимо объединить, содержат несколько листов. Итак, как бы вы подошли к проблеме? Будете ли вы копировать листы вручную или с помощью кода VBA? Или вы используете один из специализированных инструментов для объединения файлов Excel? Ниже вы найдете несколько хороших способов справиться с этой задачей.

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

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

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

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

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

    • Чтобы выбрать соседние листы, щелкните вкладку первого листа, который вы хотите скопировать, нажмите и удерживайте клавишу Shift, а затем щелкните вкладку последнего листа. Это выберет все рабочие листы между ними.
    • Чтобы выбрать несмежные листы, удерживайте клавишу Ctrl и щелкните вкладку каждого листа отдельно.
  3. Выбрав все рабочие листы, щелкните правой кнопкой мыши любую из выбранных вкладок и выберите Переместить или скопировать… .
  4. В диалоговом окне Переместить или скопировать выполните следующие действия:
    • В раскрывающемся списке Переместить выбранные листы в книгу выберите целевую книгу, в которую вы хотите объединить другие файлы.
    • Укажите, куда именно должны быть вставлены скопированные выступы листа. В нашем случае мы выбираем вариант move to end .
    • Выберите поле Создать копию , если вы хотите, чтобы исходные листы остались в исходном файле.
    • Нажмите OK, чтобы завершить процесс слияния.

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

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

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

Если у вас есть несколько файлов Excel, которые необходимо объединить в один файл, более быстрым способом будет автоматизировать процесс с помощью макроса VBA.

Ниже вы найдете код VBA, который копирует все листы из всех файлов Excel, которые вы выбираете, в одну книгу. Этот макрос MergeExcelFiles написан Алексом, одним из наших лучших гуру Excel.

Важное примечание! Макрос работает со следующей оговоркой – объединяемые файлы не должны быть открыты физически или в памяти. В таком случае вы получите ошибку времени выполнения.

Sub MergeExcelFiles()
    Dim fnameList, fnameCurFile как вариант
    Dim countFiles, countSheets как целое число
    Dim wksCurSheet как рабочий лист
    Dim wbkCurBook, wbkSrcBook как рабочая книга

    fnameList = Application. GetOpenFilename(FileFilter:="Книги Microsoft Excel (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Выберите файлы Excel для объединения", MultiSelect:=True)

    Если (vbBoolean <> VarType(fnameList)) Тогда

        Если (UBound(fnameList) > 0) Тогда
            количество файлов = 0
            количество листов = 0

            Application.ScreenUpdating = Ложь
            Приложение.Расчет = кслкалкулатионмануал

            Установите wbkCurBook = ActiveWorkbook

            Для каждого fnameCurFile в fnameList
                количество файлов = количество файлов + 1

                Установите wbkSrcBook = Workbooks.Открыть (имя файла: = fnameCurFile)

                Для каждого wksCurSheet в wbkSrcBook.Sheets
                    количество листов = количество листов + 1
                    wksCurSheet.Copy после:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                Следующий

                wbkSrcBook.Close SaveChanges:=False

            Следующий

            Application. ScreenUpdating = Истина
            Приложение. Расчет = кслкалкулатуатаматимат

            MsgBox "Обработано" & countFiles & "файлы" & vbCrLf & "Объединено" & countSheets & "рабочие листы", Title:="Объединить файлы Excel"
        Конец, если

    Еще
        MsgBox "Файлы не выбраны", Title:="Объединить файлы Excel"
    Конец, если
Конец сабвуфера
 

Как добавить этот макрос в книгу

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

  1. Нажмите Alt + F11, чтобы открыть редактор Visual Basic.
  2. Щелкните правой кнопкой мыши ThisWorkbook на левой панели и выберите Вставить > Модуль в контекстном меню.
  3. В появившемся окне (окно кода) вставьте приведенный выше код.

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

Кроме того, вы можете загрузить макрос в файле Excel, открыть его вместе с целевой книгой (включите макрос, если будет предложено), затем переключиться на свою книгу и нажать Alt + F8, чтобы запустить макрос. Если вы новичок в использовании макросов в Excel, выполните подробные действия, описанные ниже.

Как использовать макрос MergeExcelFiles

Откройте файл Excel, в который вы хотите объединить листы из других книг, и выполните следующие действия:

  1. Нажмите Alt + F8, чтобы открыть диалоговое окно Macro .
  2. В разделе Имя макроса выберите MergeExcelFiles и нажмите Выполнить .
  3. Откроется стандартное окно проводника, вы выбираете одну или несколько книг, которые хотите объединить, и нажимаете Открыть .Чтобы выбрать несколько файлов , удерживайте нажатой клавишу Ctrl, щелкая имена файлов.

В зависимости от того, сколько файлов вы выбрали, дайте макросу несколько секунд или минут для их обработки. После завершения макроса он сообщит вам, сколько файлов обработано и сколько листов объединено:

Объединение нескольких файлов Excel в один с помощью Ultimate Suite

Если вам не очень удобно работать с VBA и вы ищете более простой и быстрый способ объединения файлов Excel, взгляните на инструмент Copy Sheets , одну из более чем 60 функций, позволяющих сэкономить время, включенных в наш Ultimate Suite for Excel.

С Ultimate Suite объединить несколько книг Excel в одну очень просто, как раз-два-три (буквально, всего 3 быстрых шага). Вам даже не нужно открывать все рабочие книги, которые вы хотите объединить.

  1. Открыв основную рабочую книгу, перейдите на вкладку Ablebits Data > группу Merge и нажмите Копировать листы > Выбранные листы в одну рабочую книгу .
  2. В диалоговом окне Копировать рабочие листы выберите файлы (и, возможно, рабочие листы), которые вы хотите объединить, и нажмите Далее .
    Советы:
    • Чтобы выбрать все листы в определенной книге, просто поставьте галочку в поле рядом с именем книги, все листы в этом файле Excel будут выбраны автоматически.
    • Чтобы объединить листы из закрытых книг , нажмите кнопку Добавить файлы… и выберите столько книг, сколько хотите. Это добавит выбранные файлы только в окно «Копировать листы», не открывая их в Excel.
    • Чтобы скопировать только определенную область в определенную рабочую книгу, наведите указатель мыши на имя листа, затем щелкните значок Свернуть диалоговое окно и выберите нужный диапазон.По умолчанию копируются все данные.
  3. При необходимости выберите один или несколько дополнительных параметров и нажмите Копировать . На скриншоте ниже показаны настройки по умолчанию: Вставить все (формулы и значения) и Сохранить форматирование .

Дайте мастеру Copy Worksheets несколько секунд для обработки и наслаждайтесь результатом!

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

Другие способы объединения листов Excel и объединения данных

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

Доступные загрузки

Макрос для объединения нескольких файлов Excel (файл .xlsm)
Полнофункциональная версия Ultimate Suite на 14 дней (файл .zip)

Объединение нескольких листов Excel с помощью Power Query Append

Power Query — это здорово!

После просмотра этого руководства вы поймете почему.

Я получаю множество запросов от читателей моего блога, которые спрашивают меня, есть ли способ легко объединить таблицы из нескольких листов в одной книге с помощью Power Query

С помощью Power Query ответ — ДА!

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

Хотите знать, как добавлять рабочие листы для консолидации данных с помощью Excel Power Query? Посмотрите видео о Power Query Multiple Worksheets на YouTube и поддержите его 👍

 

Следуйте пошаговому руководству по Power Query для нескольких листов с помощью бесплатной загружаемой книги Excel для практики.

Download Excel Workbook

Шаг 1: Убедитесь, что данные каждого рабочего листа находятся в таблице Excel , нажав данные и нажав Ctrl + T

Шаг 2 :  Нажмите на данные каждого листа, которые вы хотите объединить, и выберите:

Power Query > Из таблицы

 

ШАГ 3:  Откроется 10 1 Редактор запросов, и все, что у вас есть здесь нужно нажать Close & Load .

NB: Убедитесь, что шаг 2 и 3 для каждого рабочего листа Вы хотите консолидировать

Шаг 4: Выбор Power Query> Добавить

Шаг 5 :  Выберите вариант Три или более таблиц

 

ШАГ 6:  Добавьте таблицы для добавления из Доступные таблицы (слева) к выбрав и нажав кнопку Добавить .

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

Нажмите кнопку OK!

 

ШАГ 7: Это снова откроет редактор запросов . Выберите Закрыть и загрузить .

 

ШАГ 8: Откроется новый рабочий лист, который объединит все рабочие листы в одну большую таблицу:

ШАГ 9: Из этого сводного рабочего листа90 можно объединить сводную таблицу10. и сделайте свой анализ:

Вот как вы можете объединять таблицы и использовать функцию консолидации нескольких листов Power Query.

Power Query — чрезвычайно полезный инструмент в Excel. Вы можете узнать больше об этом здесь.

 

👉 Нажмите здесь, чтобы присоединиться к нашему БЕСПЛАТНОМУ вебинару по сводным таблицам и информационным панелям Excel, который сэкономит вам часы работы и улучшит ваши навыки работы с Excel!

Об авторе
Джон Михалудис является основателем и главным вдохновителем MyExcelOnline!

Как сравнить две книги Excel? – Веб-ноты

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



Зачем сравнивать листы Excel?

Во многих ситуациях вам может понадобиться сравнить два листа Excel:

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

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

Что можно сравнить в двух книгах Excel?

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

  • Введенное значение
  • Расчетные значения
  • Формулы
  • Формулы SysGen
  • Ошибки формул SysGen
  • Структурные
  • Имена
0 0
  • Имена SysGen
  • Ошибка имен SysGen
  • Макросы
  • Подключение данных
  • Формат ячейки
  • Защита ячейки
  • Защита листа/книги

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

Как сравнить две книги Excel?

Первый шаг в сравнении двух книг Excel для активации вкладки «Запрос» в Microsoft Excel. Если у вас его нет прямо сейчас, просто выполните следующие действия:

Активация вкладки «Запрос» в Microsoft Excel

  1. Перейдите в меню «Файл > Параметры». Это откроет вам небольшое окно «Параметры Excel».
  2. Теперь перейдите в раздел «Надстройки». Вы увидите раздел активных и неактивных надстроек приложений.
  3. В разделе «Неактивные надстройки приложений» нажмите «Запросить надстройку» и активируйте.
  4. Выберите надстройку «Запросить» и посмотрите на нижние параметры «Управление». В раскрывающемся списке выберите «Надстройки COM», нажмите кнопку «Перейти…», а затем нажмите «ОК».
  5. Теперь на ленте появится вкладка «Запрос».
Включение вкладки “Запрос” в Excel

шагов для сравнения двух книг Excel

Допустим, у нас есть две рабочие книги для сравнения их данных. Здесь мы используем рабочую тетрадь «Ранее» и «Текущая» рабочая тетрадь.В «Ранее» рабочую книгу мы внесли некоторые изменения и сохранили ее под новым именем «Текущая» рабочая книга. Более ранняя рабочая книга содержит старые данные, а «текущая» содержит некоторые измененные данные.

  • Шаг 1: У нас есть наша более ранняя рабочая книга:
более ранняя рабочая книга
  • Шаг 2: Вот наша текущая рабочая книга, в которой, как вы видите, есть некоторые измененные данные.
Текущая рабочая книга
  • Шаг 3: Теперь перейдите к любой рабочей книге. Нажмите на вкладку «Запрос», а затем в разделе «Сравнить» нажмите «Сравнить файлы».
Выберите «Сравнить» на вкладке «Запросить»
  • Шаг 4: Вы увидите окно сравнения, предлагающее выбрать файл для сравнения и с каким файлом вы хотите его сравнить. В нашем случае более ранний файл имеет параметр «Сравнить», а текущий файл — параметр «С». Вы можете легко поменять местами файлы, нажав кнопку «Поменять файлы».
Выбрать файлы для сравнения
  • Шаг 5: Нажмите кнопку «Сравнить», и вы увидите новое окно с результатами сравнения.В нашем случае это статус «Нет в наличии» в файле «Ранее», который меняется на «В наличии» в файле «Текущий». Вы также можете увидеть подробное описание, приведенное ниже, например «Введенное значение изменено».
Результаты сравнения двух файлов Excel

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


Связанные статьи:


Что нельзя сравнивать?

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

Эта функция работает не более чем с двумя книгами. Как вы видели выше, в окнах сравнения вы можете добавить только 2 книги. Таким образом, это может быть ограничением этой функции. Если какая-либо книга защищена, эта функция не позволит сравнить этот файл.Вам нужно сначала снять защиту, а затем вы можете использовать его для сравнения.

Заключение

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

Искать все книги в папке


Сегодня я покажу вам, как искать определенную строку во всех книгах Excel с расширениями файлов xls, xlsx и xlsm в заданной папке.

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

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

Затем вы можете нажать левой кнопкой мыши на ссылку, и Excel откроет соответствующую книгу и найдет ячейку, содержащую строку поиска.

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

Следующий макрос запрашивает папку для поиска и строку поиска.

Поиск книг в папке для одной строки

 'Имя макроса
ПодпоискWKBooks()

'Измерение переменных и объявление типов данных
Dim WS как рабочий лист
Dim myfolder как строка
Dim Str As String
Dim a как сингл
Dim sht As Worksheet

'Создайте новый рабочий лист и подключите его к объекту с именем WS
Установить WS = Листы. Добавить

'Показать диалоговое окно и запросить папку
С приложением.Файлдиалог (мсофиледиалогфолдерпикер)
    .Показывать

    'Сохранить путь к выбранной папке в переменную myfolder
    моя папка = .SelectedItems(1) & "\"
Конец с

'Показать поле ввода и запросить строку поиска для использования
Str = Application.InputBox(prompt:="Строка поиска:", Title:="Поиск во всех книгах в папке", Тип:=2)

'Остановить макрос, если строка не содержит ничего
Если Str = "", то выйдите из Sub

'Сохранить текст "Строка поиска:" в ячейку A1 на листе WS
WS.Range("A1") = "Строка поиска:"

'Сохранить текст в переменной Str в ячейку B1 на листе WS
ВС.Диапазон ("B1") = Сила

'Сохранить текст "Путь:" в ячейку A2 на листе WS
WS.Range("A2") = "Путь:"

'Сохранить текст в переменной myfolder в ячейку B2 на листе WS
WS.Range("B2") = моя папка

'Сохранить текст "Workbook" в ячейку A3 на листе WS
WS.Range("A3") = "Рабочая книга"

'Сохранить текст "Рабочий лист" в ячейку B3 на рабочем листе WS
WS. Range("B3") = "Рабочий лист"

'Сохранить текст "Адрес ячейки" в ячейку C3 на листе WS
WS.Range("C3") = "Адрес ячейки"

'Сохранить текст "Ссылка" в ячейку D3 на листе WS
ВС.Диапазон("D3") = "Ссылка"

'Сохранить 0 (ноль) в переменную a
а = 0

' Функция DIR возвращает первое имя файла, соответствующее пути, указанному в переменной myfolder, и сохраняет его в переменной Value
Значение = Каталог (моя папка)

'Продолжайте итерацию до тех пор, пока переменная Value не станет ничем
Делать до значения = ""

    'Проверить, равно ли значение . или ..
    Если Значение = "." Или Значение = ".." Тогда

    'Если значение не равно . или .. запустите эти строки кода
    Еще

        'Проверить, соответствуют ли последние три символа имени файла xls или четыре последних символа соответствуют xlsx или xlsm
        Если Right(Value, 3) = "xls" Или Right(Value, 4) = "xlsx" Или Right(Value, 4) = "xlsm" Тогда

            'Включить обработку ошибок
            При ошибке Возобновить Далее

            'Открыть книгу на основе пути в переменной папке и имени файла в переменной Значение, используя пароль zzzzzzzzzzzz
            Рабочие тетради. Открыть имя файла:=myfolder и значение, пароль:="zzzzzzzzzzzz"

            'Проверить, не произошла ли ошибка
            Если Число Ошибок > 0 Тогда

                'Сохранить путь к столбцу A на основе переменной счетчика a
                WS.Range("A4").Смещение(a, 0).Value = Значение

                'Сохранить текст "Защищено паролем" в столбце B на основе переменной счетчика a
                WS.Range("B4").Offset(a, 0).Value = "Защищено паролем"

                'Добавить 1 к числу, хранящемуся в переменной a
                а = а + 1

            'Продолжить здесь, если ошибка не возвращается
            Еще

                'Отключить обработку ошибок
                При ошибке Перейти к 0

                'Пройтись по всем листам в активной книге
                Для каждого шт в ActiveWorkbook.Рабочие листы

                        'Поиск листа по значению переменной Str и сохранение результата в объект c
                        Установить c = sht.Cells. Find(Str, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

                        'Проверить, не является ли объект c ничем
                        Если не c - это ничто, то

                            'Сохранить адрес на основе объекта диапазона c в переменную firstAddress
                            первый адрес = c.Адрес

                            'Повторите следующие строки между Do и Loop
                            Делать

                                'Сохранить имя файла в столбец A и номер строки на основе переменной a
                                WS.Range("A4").Смещение(a, 0).Value = Значение

                                'Сохранить имя рабочего листа в столбце B и номер строки на основе переменной a
                                WS.Range("B4").Offset(a, 0).Value = шт.Имя

                                'Сохранить адрес ячейки в столбец C и номер строки на основе переменной a
                                ВС. Диапазон ("C4"). Смещение (a, 0). Значение = c. Адрес

                                'Создать гиперссылку, указывающую на имя файла, имя рабочего листа и адрес ячейки, показать текст гиперссылки "Ссылка"
                                WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
                                шт.Название & "!" & c.Address, TextToDisplay:="Ссылка"

                                'Увеличить счетную переменную a на 1
                                а = а + 1

                                'Найти следующую совпадающую ячейку и сохранить в объект c
                                Установить с = шт.Cells.FindNext(c)

                            'Продолжайте итерацию до тех пор, пока c не станет ничем, а c не будет равно первой найденной ячейке на том же рабочем листе.
                            Цикл, в то время как Not c Is Nothing и c.Address <> firstAddress
                        Конец, если

                'Продолжить со следующего рабочего листа
                Следующий шт
            Конец, если

            'Закройте книгу и не сохраняйте внесенные изменения
            Книги (значение). Закрыть Ложь

            'Отключенная обработка ошибок
            При ошибке Перейти к 0
        Конец, если
    Конец, если

    'Найти следующий файл
    Значение = Направление

'Продолжайте повторять
Петля

'Изменить ширину ячеек, чтобы она соответствовала тексту
Cells.EntireColumn.AutoFit
Конец сабвуфера
 

Наверх

Объяснение макроса

Макрос перебирает все файлы в заданной папке, и если расширение файла соответствует xls, xlsx или xlsm, книга открывается.

Если рабочая книга защищена паролем, макрос выводит «Защищено паролем» для этой рабочей книги.

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

Наверх

Куда поставить код?

  1. Копировать макрос (CTRL + c)
  2. Перейти к редактору VB (Alt + F11)
  3. Нажмите левой кнопкой мыши на “Вставка” в верхнем меню.
  4. Нажмите левой кнопкой мыши на «Модуль», чтобы вставить модуль кода в вашу книгу.
  5. Вставьте код в модуль. (CTRL+V)
  6. Вернуться в Excel.
  7. Сохраните книгу как книгу с поддержкой макросов (файл *.xlsm).
    Если вы этого не сделаете, макрос исчезнет при следующем открытии той же книги.

Наверх

Пример

Пример, у меня есть 5 рабочих книг в папке c:\temp\, содержащих много случайных чисел. Макрос запрашивает путь и строку поиска.

Затем создается этот лист.

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

Наверх

Поиск других расширений файлов

Измените следующую строку на любые расширения файлов, которые вы хотите найти.

 Если Right(Value, 3) = "xls" Или Right(Value, 4) = "xlsx" Или Right(Value, 4) = "xlsm" Тогда
 

Наверх

Наверх

Поиск нескольких строк во всех книгах в папке

Следующий макрос позволяет выполнять поиск по нескольким строкам поиска во всех книгах в папке.

 ПодпоискWKBooks()
Dim WS как рабочий лист
Dim myfolder как строка
Dim Str As String
Dim a как сингл
Dim sht As Worksheet
Dim RNG как диапазон

'Включить обработку ошибок
При ошибке Возобновить Далее

'Показать диалоговое окно и запросить диапазон ячеек
Установите RNG = Application.InputBox(Prompt:="Выберите диапазон ячеек, содержащий строки поиска" _
, Название:="Выберите диапазон", По умолчанию:=ActiveCell.Address, Тип:=8)

'Отключить обработку ошибок
При ошибке Перейти к 0


С приложением.Файлдиалог (мсофиледиалогфолдерпикер)
    .Показывать
    моя папка = .SelectedItems(1) & "\"
Конец с


Установить WS = Листы.Добавить


WS.Range("A2") = "Путь:"
WS.Range("B2") = моя папка
WS.Range("A3") = "Рабочая книга"
WS.Range("B3") = "Рабочий лист"
WS.Range("C3") = "Адрес ячейки"
WS.Range("D3") = "Ссылка"
WS.Range("E3") = "Строка поиска"

а = 0

Значение = Каталог (моя папка)
Делать до значения = ""
    Если Значение = "." Или Значение = ". ." Потом
    Еще
        Если Right(Value, 3) = "xls" Или Right(Value, 4) = "xlsx" Или Right(Value, 4) = "xlsm" Тогда
            При ошибке Возобновить Далее
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
            Если Число Ошибок > 0 Тогда
                WS.Range("A4").Смещение(a, 0).Value = Значение
                WS.Range("B4").Offset(a, 0).Value = "Защищено паролем"
                а = а + 1
            Еще
                При ошибке Перейти к 0
                Для каждого шт в ActiveWorkbook.Рабочие листы

                    'Перебрать все ячейки в переменной RNG
                    Для каждого d в ГСЧ

                        'Найти ячейки на листе, равные значению в ячейке d
                        Установить c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
                        Если не c - это ничто, то
                            первыйАдрес = c.Адрес
                            Делать
                                ВС. Диапазон ("A4"). Смещение (a, 0). Значение = Значение
                                WS.Range("B4").Offset(a, 0).Value = шт.Имя
                                WS.Range("C4").Смещение(a, 0).Value = c.Address
                                WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
                                шт.Название & "!" & c.Address, TextToDisplay:="Ссылка"

                                'Сохранить значение ячейки в переменной d в столбец E и строку на основе переменной count a
                                ВС.Диапазон ("E4"). Смещение (a, 0). Значение = d
                                а = а + 1
                                Установить c = шт.Cells.FindNext(c)
                            Цикл, в то время как Not c Is Nothing и c.Address <> firstAddress
                        Конец, если
                    Следующий д
                Следующий шт
            Конец, если
            Книги(значение).Close False
            При ошибке Перейти к 0
        Конец, если
    Конец, если
    Значение = Направление
Петля
Клетки. EntireColumn.AutoFit
Конец сабвуфера
 

Как использовать макрос

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

Новый лист вставляется и заполняется данными, если найдены значения.

Поиск нескольких строк и возврат соответствующих значений в той же строке

Показанный выше макрос ищет несколько строк поиска во всех книгах Excel в папке, а также возвращает значение в той же строке.

 ПодпоискWKBooks()
Dim WS как рабочий лист
Dim myfolder как строка
Dim Str As String
Dim a как сингл
Dim sht As Worksheet
Dim RNG как диапазон

При ошибке Возобновить Далее
'Показать поле ввода и запросить диапазон ячеек, содержащий значения поиска
Установите RNG = Application.InputBox(prompt:="Выберите диапазон ячеек, содержащий строки поиска" _
, Название:="Выберите диапазон", По умолчанию:=ActiveCell.Address, Тип:=8)

При ошибке Перейти к 0

'Показать поле ввода и запросить смещение ячейки
Стр = Приложение. InputBox (приглашение: = "Смещение ячейки:", Название: = "Смещение", Тип: = 2)

С Application.FileDialog(msoFileDialogFolderPicker)
    .Показывать
    моя папка = .SelectedItems(1) & "\"
Конец с


Установить WS = Листы.Добавить


WS.Range("A2") = "Путь:"
WS.Range("B2") = моя папка
WS.Range("A3") = "Рабочая книга"
WS.Range("B3") = "Рабочий лист"
WS.Range("C3") = "Адрес ячейки"
WS.Range("D3") = "Ссылка"
WS.Range("E3") = "Строка поиска"
WS.Range("F3") = "Возвращаемое значение"

а = 0

Значение = Каталог (моя папка)
Делать до значения = ""
    Если Значение = "." Или Значение = ".." Тогда
    Еще
        Если Right(Value, 3) = "xls" Или Right(Value, 4) = "xlsx" Или Right(Value, 4) = "xlsm" Тогда
            При ошибке Возобновить Далее
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
            Если Число Ошибок > 0 Тогда
                WS.Range("A4").Смещение(a, 0).Value = Значение
                WS.Range("B4").Offset(a, 0). Value = "Защищено паролем"
                а = а + 1
            Еще
                При ошибке Перейти к 0
                Для каждого шт в ActiveWorkbook.Рабочие листы
                    Для каждого d в ГСЧ
                        'MsgBox д
                        Установить c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
                        Если не c - это ничто, то
                            первыйАдрес = c.Адрес
                            Делать
                                WS.Range("A4").Смещение(a, 0).Value = Значение
                                WS.Range("B4").Смещение(а, 0).Значение = шт.Имя
                                WS.Range("C4").Смещение(a, 0).Value = c.Address
                                WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
                                шт.Название & "!" & c.Address, TextToDisplay:="Ссылка"
                                WS. Range("E4").Смещение(a, 0).Value = d

                                'Сохранить значение из ячейки на основе переменной смещения Str
                                ВС.Диапазон ("F4"). Смещение (a, 0). Значение = c. Смещение (0, Str). Значение
                                а = а + 1
                                Установить c = шт.Cells.FindNext(c)
                            Цикл, в то время как Not c Is Nothing и c.Address <> firstAddress
                        Конец, если
                    Следующий д
                Следующий шт
            Конец, если
            Книги(значение).Close False
            При ошибке Перейти к 0
        Конец, если
    Конец, если
    Значение = Направление
Петля
Клетки.EntireColumn.AutoFit
Конец сабвуфера

 

Как использовать

  1. Запустить макрос (Alt+F8)
  2. Выберите диапазон ячеек, содержащий строки поиска
  3. Нажмите левой кнопкой мыши на кнопку OK.
  4. Выберите, на сколько ячеек вправо (положительные числа) или влево (отрицательные числа) от найденного значения.

    Пример, 1 вернет соседнее значение справа.
    Пример, -2 вернет значение из ячейки двумя столбцами слева от найденного значения.
  5. Выберите папку, которую хотите использовать.
  6. Нажмите левой кнопкой мыши на OK

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

Наверх

Наверх

В следующей статье объясняется, как выполнять поиск в папке и вложенных папках:

Поиск во всех книгах в папке и подпапках

Рекомендуемое чтение:

Открывать файлы Excel в папке [VBA]

Ссылка на другие листы или книги в макросе Excel

Примечание редактора. Это вторая из двух статей, посвященных макросам Excel, составленным на основе вопросов аудитории, заданных на веб-трансляции MrExcel на прошлой неделе, «Макросы для бюджетирования и корпоративного учета.«Первая статья об использовании преобразования текста в столбцы в макросе была темой колонки на прошлой неделе.

Во время веб-трансляции на прошлой неделе я рассказал об одном простом примере записи макроса, а затем рассказал о четырех вещах, которым вам обязательно нужно научиться, чтобы создавать эффективные макросы. По сути, программа записи макросов Excel никогда не запишет для вас эти четыре элемента. Однако, как только вы освоите четыре элемента, вы сможете использовать комбинацию средства записи макросов и четырех концепций для решения любой проблемы в Excel.Интернет-трансляция длилась пять минут, и я не успел ответить на все вопросы и ответы в конце. (Кстати, если вы пропустили веб-трансляцию, архив скоро будет доступен на странице веб-трансляции CFO.com.)

Итак, в этой колонке я отвечу на третий и последний вопрос, на который у меня не было времени обсудить во время онлайн-сессии.

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

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

Рабочие книги («Бухгалтерские отчеты.xlsm»). Активировать

Листа («Балансовый отчет»). Выберите

Диапазон (“G22”). Выберите

Выбор.Копия

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

рабочих книг («Бухгалтерские отчеты.xlsm»). Листы («Балансовый отчет»). Диапазон («G22»). Копия

Использование специальных переменных для ссылки на рабочие книги или рабочие листы
В веб-трансляции я говорил об использовании простых переменных для хранения одного числа, таких как FinalRow, TotalRow, FirstRow и т. д. В VBA есть еще один тип переменных, который называется объектной переменной. Переменная объекта может относиться ко всему рабочему листу, рабочей книге, диаграмме или любому другому объекту в Excel.Вы всегда должны объявлять переменные объекта в верхней части макроса. Это не обязательно, но я всегда начинаю объектные переменные моей рабочей книги с WB, а объектные переменные моего рабочего листа – с WS.

Представьте себе сценарий, в котором макрос будет выполняться, пока открыта определенная рабочая книга. В этой рабочей книге есть рабочий лист данных и рабочий лист поиска. Цель будет состоять в том, чтобы создать совершенно новую книгу с одним листом отчета. В этом сценарии я бы использовал WSD в качестве переменной для рабочего листа данных, WSR для рабочего листа отчета и WSL для рабочего листа поиска.Я бы использовал WBN для обозначения новой созданной рабочей книги, а WBT — для ссылки на исходную рабочую книгу (буква T означает «Эта рабочая книга», рабочая книга, в которой находится код).

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

Подпрограмма MyAmazingMacro()

Dim WBT As Workbook ‘Эта рабочая книга

Dim WBN As Workbook ‘ Новая рабочая книга

Dim WSD As Worksheet ‘ Данные

Dim WSL As Worksheet ‘ Лист поиска

Dim WSR As Worksheet Рабочий лист отчета в WBN

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

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

Установить WBT = Workbooks(“Raw Data.xlsm”)

Установить WSD = WBT.Worksheets(“GLData”)

Установить WSL = WBT.Worksheets(“CCtrList”)

Установить WBN = Workbooks(“Accounting Reports.xlsm”)

Установить WSR = WBN. Worksheets(“Балансовый отчет”)

После определения переменных становится проще обращаться к ячейкам в другой книге или листе.Вы можете обращаться к ячейкам на любом рабочем листе, не активируя рабочий лист или рабочую книгу, при условии, что рабочая книга открыта в текущем сеансе Excel. Следующая строка скопирует ячейку C4 из рабочего листа CCtrList в текущей рабочей книге в ячейку Z9 в рабочей книге Balance Sheet на листе AccountingReports.xlsm:

WSR.Range(“Z9”) = WSL.Range(“C4”)

Деловые хитрости
Существует множество приемов обращения к рабочим книгам и рабочим листам.

Много раз вы хотите, чтобы макрос ссылался на рабочий лист, который был активен в момент запуска макроса.Excel VBA предоставляет ключевое слово ActiveSheet для ссылки на текущий активный рабочий лист. Если вы определите WSD как ActiveSheet, макрос запомнит, какой рабочий лист был активен в начале макроса. Позже вы сможете вернуться к этому рабочему листу.

Sub ReturnToOriginal()

Dim WSO As Worksheet ‘ Исходный рабочий лист

Dim WSN As Worksheet ‘ новый рабочий лист, созданный макросом

Установить WSO = ActiveSheet

‘…. здесь макрос для создания WSN

‘Вернуться к исходному рабочему листу

ВСО.Выберите

Конечный переходник

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

Каждый день ИТ-отдел создает единый рабочий лист Excel со всей открытой дебиторской задолженностью. Каждый день эта рабочая тетрадь приходит с другим именем.Сегодняшний файл может называться ARLog20110823.xlsx. Завтрашний файл будет ARLog20110824.xlsx. Вместо того, чтобы менять макрос каждый день, чтобы иметь дело с именем, вы можете настроить процесс, посредством которого вы открываете книгу макросов, открываете журнал AR текущего дня, а затем вызываете макрос. Вы можете написать макрос, предполагая, что данные находятся в ActiveWorkbook и что ваш лист поиска находится в книге макросов.

Sub ProcessTodaysData()

Dim WBT As Workbook ‘книга макросов

Dim WBD As Workbook — рабочая книга данных от I.Т.

Dim WBN As Workbook ‘ Новая рабочая книга, созданная макросом

Dim WSD As Worksheet — единственный рабочий лист в WBD

Dim WSL As Worksheet Lookups, в WBT

Dim WSR As Worksheet ‘ Отчет, созданный в WBN

 

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

Установить WBT = ThisWorkbook

Установить WBD = ActiveWorkbook

Установить WSL = WBT.Worksheets(“CCtrList”)

Set WSD = WBD.Worksheets(1) ‘ Не знаю, как это называется каждый день

В приведенном выше макросе вы обходитесь тем, что не знаете имя рабочего листа текущего дня, ссылаясь на него как на ActiveWorkbook.Если рабочий лист также каждый день имеет другое имя, использование Worksheets(1) для ссылки на первый (в данном случае единственный) рабочий лист в рабочей книге является эффективным приемом.

Открытие рабочей книги
Если вы запишете действие по открытию рабочей книги, программа записи макросов выдаст:

Workbooks.Open FileName: = «C: DataAccountList.xls»

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

Dim WBA as Workbook «Список учетных записей» рабочая книга

Dim WSL as Worksheet ‘ Рабочий лист поиска

Рабочие тетради.Открыть имя файла: = «C: DataAccountList.xls»

Установить WBA = ActiveWorkbook

Установить WSL = WBA.Worksheets(1)

Кроме того, вы можете открыть книгу и назначить ее объектной переменной в одной строке кода. Обратите внимание, что вы должны добавить круглые скобки к команде Workbooks.Open:

Dim WBA as Workbook «Список учетных записей» рабочая книга

Dim WSL as Worksheet ‘ Рабочий лист поиска

Установить WBA = Workbooks. Open(FileName:=”C:DataAccountList.xls”)

Установить WSL = WBA.Рабочие листы(1)

Создание новой пустой книги
Много раз в макросе я хотел создать совершенно новую книгу. По умолчанию новая книга в Excel будет состоять из трех листов. Тем не менее, многие сообразительные Exceller изменили настройки по умолчанию, чтобы помещать в свои новые книги только один рабочий лист. Кто-то, использующий Excel 97, может автоматически получить 16 рабочих листов в своих новых книгах. Вместо того, чтобы оставить это на волю случая, я использую встроенный шаблон под названием xlWBATWorksheet для создания новой книги только с одним рабочим листом:

.

Подпрограмма AddAWorkbook()

Dim WBT As Workbook ‘ Исходная рабочая книга

Dim WBN As Workbook ‘ Новая рабочая книга

Установить WBT = ActiveWorkbook

Рабочие тетради.Добавить кслвбатворклист

Установить WBN = ActiveWorkbook ‘новая рабочая книга

WBT.Activate ‘ Вернуться к исходной книге

Как и в предыдущем примере, вы можете создать новую книгу и назначить ее объектной переменной в одной строке кода. Снова добавьте скобки:

Подпрограмма AddAWorkbook()

Dim WBT As Workbook ‘ Исходная рабочая книга

Dim WBN As Workbook ‘ Новая рабочая книга

Установить WBT = ActiveWorkbook

Установить WBN = Рабочие книги.Добавить (xlWBATWorksheet)

WBT.Activate ‘ Вернуться к исходной книге

Создание нового пустого рабочего листа в активной рабочей книге
При использовании команды Worksheets.Add Excel добавит рабочий лист в текущую рабочую книгу. Рабочий лист будет иметь непредсказуемое имя (например, Sheet4, Sheet6, Sheet17). Вероятно, вы захотите переименовать рабочий лист. Когда вы добавляете рабочий лист, выбирается новый лист, поэтому вы можете использовать ключевое слово ActiveSheet для ссылки на рабочий лист после его добавления.

Подпрограмма AddWorksheet()

Dim WSN как рабочий лист

Рабочие листы. Добавить после: = ActiveSheet

Установить WSN = ActiveSheet

WSN.Name = «Отчет»

Кроме того, вы можете создать рабочий лист и назначить его объектной переменной в одной строке:

Подпрограмма AddWorksheet2()

Dim WSN как рабочий лист

Установить WSN = Worksheets. Add (после: = ActiveSheet)

WSN.Name = «Отчет»

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

Простое использование команды .Copy на рабочем листе скопирует этот рабочий лист в новую рабочую книгу. Новая рабочая книга становится активной, поэтому вы можете обращаться к ней с помощью ActiveWorkbook

.

Sub CreateReportWorkbook()

Dim WBT As Workbook ‘ Эта рабочая книга

Dim WBR As Workbook ‘ Новая рабочая книга создана позже

Dim WSN As Worksheet ‘ Новый рабочий лист

Установить WSN = Worksheets.Add (после: = ActiveSheet)

WSN.Name = «Отчет»

‘ Здесь много кода для создания отчета

‘ Здесь много кода для создания отчета

‘ Здесь много кода для создания отчета

‘ Когда отчет будет готов, скопируйте в новую книгу

БСН. Копировать

Установить WBR = ActiveWorkbook

WBR.SaveAs «C:ReportsReport.xlsx»

Конечный переходник

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

Билл Джелен — автор 33 книг о Microsoft Excel и ведущий сайта MrExcel.com. Отправьте вопросы для рассмотрения в будущих статьях, щелкнув его имя в верхней части этой статьи и отправив ему сообщение по электронной почте или отправив заметку непосредственно на [email protected]

.

RDBMerge, надстройка слияния Excel для Excel для Windows

RDBMerge, надстройка Excel Merge для Excel для Windows

Коротко! RDBMerge — это удобный способ объединения данных из нескольких Книги Excel,
файла csv и xml в сводную книгу.

Установите утилиту RDBMerge

1) Загрузите правильную версию и распакуйте ее в локальный каталог.

2) Скопировать RDBMerge. xla(m) в незащищенный каталог в вашей системе.
     Совет: используйте одна папка для всех надстроек (таким образом легко сделать резервную копию надстроек)

3) Запустите Excel и откройте книгу.

Excel 97-2003
Нажмите «Инструменты», нажмите «Надстройки», используйте «Обзор», чтобы перейти к надстройку, а затем
нажмите кнопку ОК.Убедитесь, что RDBMerge установлен в надстройке. список, а затем нажмите кнопку ОК.

Excel 2007-2016
2007: Нажмите кнопку Microsoft Office, нажмите «Параметры Excel», нажмите «Надстройки». вкладка
2010–2016: нажмите «Файл», нажмите «Параметры», перейдите на вкладку «Надстройки». В раскрывающемся списке «Управление» выберите «Надстройки Excel» и нажмите «Перейти». Используйте «Обзор», чтобы перейдите к надстройке и нажмите «ОК». Убедитесь, что RDBMerge отмечен в список надстроек, а затем нажмите кнопку ОК.

Как использовать надстройку RDBMerge Шаг за шагом

В Excel 2007-2016 вы найдете кнопку с именем RDBMerge на ленте на Вкладка «Данные», чтобы открыть пользовательскую форму. В Excel 97-2003 вы найдете пункт меню RDBMerge в меню «Данные», чтобы открыть пользовательскую форму.

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

Какие файлы:
В разделе «Какие файлы» выберите тип файлы в раскрывающемся списке, которые вы хотите объединить. Если вы используете первый вариант “XL?” он объединит все типы файлов Excel с расширением, которое начинается с XL (xls, xlsx, xlsm, xlsb).Но вы также можете объединять файлы только с определенное расширение в папке. По умолчанию возможность объединить все файлы в папка выбрана, но вы также можете использовать опцию для фильтрации файла имена, нажмите кнопку «Советы» для получения дополнительной информации. В надстройке для Excel 2007-2013 также есть возможность выбрать нужные файлы, легко если вы не хотите объединять все файлы в папке.

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

Какой диапазон:
У вас есть возможность объединить фиксированный диапазон (может быть более одной области). Нажмите на кнопку Советы Чтобы получить больше информации. Или использовать первую ячейку? до последней ячейки на листе вариант, вы можете изменить начальную ячейку (по умолчанию = A1).Используйте A2, например, если вы не хотите копировать свой заголовок каждый раз.

Разное варианты:
Прежде чем мы нажмем кнопку Merge, у нас есть несколько параметры здесь, которые вы можете изменить.

1: Добавить файл Флажок имени добавит имя файла или имя файла и листа до или выше ваши данные.
Вы можете использовать его, например, для фильтрации листа комбинирования для данные из определенного файла.

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

3: Если в ваших книгах есть ссылки на другие книги, используйте параметр UpdateLinks, чтобы обновить значения.

4: Используйте данные Вставить рядом с друг друга, если вы хотите вставить данные из каждого файла рядом с каждым другие, а не друг под другом. Например, если вы установите свой фиксированный диапазон на A1:A100 и установите этот флажок, он будет использовать один столбец для каждого файла в комбинированный лист. Если у вашего диапазона есть два столбца, он будет использовать два столбца для каждый файл в объединенном листе.

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

Нажмите кнопку «Объединить»:
Когда вы нажмите кнопку «Объединить», она создаст для вас новую книгу с двумя листы:

Объединить лист со всеми data
Log Sheet с копированием/вставкой и ошибкой информация

Тогда вам решать, хотите ли вы сохранить эту книгу.

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

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

 

Скачать

Примечание : загрузите надстройку, подходящую для вашего Excel. версия. Если вам нужна версия надстройки для Mac, ознакомьтесь с разделом Mac на моем сайте. Примечание : версия 1.4 работает в Excel 2007-2016

Excel 2007–2016, версия 1.4
Дата файла (8 декабря 2010 г.)

Excel 97-2003 версия 1.3
Дата файла (1 апреля 2010 г.)

Примечание : Если вы не можете найти пункт меню на вкладке «Данные» в Лента и надстройка отмечены в диалоговом окне надстроек см. этот веб-сайт Чтобы получить больше информации : http://peltiertech.com/Utility30/Documentation30/RibbonDisappears.html

 

 

ВПР Excel из другой книги — Блог Contextures

Если вы заполняете форму заказа в Excel, вы можете использовать функцию ВПР, чтобы найти продажную цену для каждого товара в заказе на продажу.Например, на снимке экрана ниже форма заказа находится на листе «Заказы», ​​а формула ВПР в столбце D извлекает стоимость из таблицы цен на листе «Цены».


Диапазон поиска в другой книге

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

Чтобы сослаться на другую книгу в формуле ВПР, выполните следующие действия.

  1. Откройте обе книги (этот шаг не является обязательным, но упрощает создание ссылки)
  2. Создайте формулу ВПР и в качестве аргумента table_array выберите диапазон поиска в другой книге. Excel автоматически добавит имя другой книги и имя листа в формулу.

На приведенном ниже снимке экрана таблица поиска находится в книге PriceList.xlsx на листе Prices в диапазоне с именем PriceLU.

Закрытие рабочей книги поиска

Пока рабочая книга таблицы поиска прайс-листа открыта, формула ВПР покажет имя рабочей книги и указанный адрес диапазона или имя диапазона.

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

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