В предшествующих публикациях, посвященных теме сбора данных из различных источников, рассматривались вопросы объединения определенных листов из различных книг в одном файле, копирования диапазонов ячеек со всех листов рабочей книги на итоговый лист, а также сбора информации с заданных листов множества рабочих книг на итоговый лист отдельной рабочей книги. Наряду с рассмотренными ранее задачами, достаточно часто встречается необходимость выбора определенных значений ячеек из нескольких однотипных файлов Excel.
Выбор определенных значений с листов нескольких рабочих книг
Например, существует несколько рабочих книг, на листах которых сформированы сметы, акты выполненных работ либо любые другие однотипные документы.
Требуется выбрать из всех рабочих книг суммы смет, то есть значения столбца «Сумма», соответствующие значению строки «Всего по смете».
Для быстрого решения подобных задач разработана надстройка для сбора заданных значений ячеек.
Выбор значений из заданных листов множества рабочих книг
Передача исходных данных от пользователя к программе производится при помощи диалогового окна.
надстройка для сбора определенных значений ячеек в итоговую таблицу
Надстройка позволяет:
1. Перебирать выбранные рабочие книги;
2. перебирать определенные пользователем листы выбранных рабочих книг;
3. находить заданные значения в указанном столбце;
4. возвращать значения, находящиеся на заданном смещении от искомых значений;
5. создавать список из возвращаемых значений (из всех найденных, из первых, из последних);
6. в созданном списке добавлять гиперссылки на исходные рабочие книги.
Пользователь задает в ячейках строки на любом рабочем листе значения для поиска, например, «Итого по смете» и «Всего по смете». Выбирает при помощи кнопки обзора рабочие книги, указывает листы и столбец, в котором программа будет производить поиск значений (в нашем случае, это столбец «C»), а также задает смещение для ячеек, из которых будет осуществляться сбор значений (в нашем случае смещение составляет 4 столбца 7-3=4) и запускает программу.
Столбец для поиска можно задавать как по имени, так и по номеру, то есть вместо имени столбца «C» можно указать номер этого столбца – 3.
Для случаев, когда искомое значение встречается в заданном столбце несколько раз, предусмотрена возможность сбора всех значений, только первых значений на каждом листе и только последних значений на каждом листе.
После пуска программа перебирает все указанные рабочие книги, заходит на листы в соответствии с выбранным условием (все видимые листы, все непустые листы, листы с определенным именем и так далее), в указанном столбце находит заданные значения, если конечно они там есть. После этого, из ячейки с заданным смещением, программа забирает значение и дописывает его под искомым значением, как это показано на скриншоте ниже.
Слева от первого искомого значения программа дописывает имена файлов, которым эти значения принадлежат. В случае, если в диалоговом окне устанавливается флажок в поле «Добавить гиперссылки», то к именам файлов добавляются гиперссылки и появляется возможность быстро открыть тот или иной файл одним кликом мыши. Если установить флажок в поле «Продолжить таблицу», то новые данные будут дописываться ниже. Для того чтобы разместить искомые значения по вертикали, а имена рабочик книг по горизонтали, сформированную таблицу со значениями ячеек можно транспонировать.
Исходные значения для поиска должны размещаться на листе только в одну строку в ячейках, не имеющих объединений с другими ячейками.
Другие материалы по теме:
