Автоматическое создание листа в excel

Обновлено: 03.07.2024

Так как новичок в Excel, не перестаю удивляться возможностям этой программы. Хочу попросить у вас помощи форумчани.
У меня есть файл, таких на работе встречается много и хотелось бы как-то автоматизировать процесс, ну или хотя бы облегчить его.
Во вложении вставлю пример.
У меня есть ряд следующих вопросов:
1) Устанавливая фильтр по странам можно ли автоматически создать листы с наименованием каждой страны представленной в фильтре в алфавитном порядке. В файле есть пример, но делал я это вручную.
2) Можно ли создать такой макрос/формулу что бы выбирая страну из фильтра можно было бы автоматически перенести информацию с колонок CDEFG в разбитые по странам листы, с последующим удалением дубликатов. (я пробовал создавать макросы, но они выполняют только то количество что я выделял, т.е. не важно какая страна, он тупо выделяет только то количество колонок, которое я выделял записывая макрос)

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

Заранее Благодарю за помощь.

Заранее Благодарю за помощь. Hjq

Заранее Благодарю за помощь. Автор - Hjq
Дата добавления - 05.11.2017 в 17:35

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

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

Эстетика и юзабилити
Оптимизация кода + нововведения
Структура и связи

1. Эстетика и юзабилити

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

Есть и минусы такого решения, в частности всплывающие подсказки могут раздражать, но в ситуации, когда на объекте 15" мониторы на ноутбуках с разрешением 1366×768 это разумный компромисс, что бы рабочая область была как можно больше.

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

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

Здесь первая процедура постоянно будет защищать лист при помощи пароля 111, вторая будет блокировать функционал вырезать-вставить. Надо ли говорить, что это все работает только при включенных макросах, но с другой стороны без них и файл на 100% функционировать не будет.
Для случаев же п.2 разумно завести лист где столбцы будут содержать меняющиеся значения, прописать в них ссылки на диапазоны, присвоить им имена, т.е. на вкладке «Формулы» -> «Диспетчер имен» каждому диапазону присвоить имена и через вкладку «Данные» -> пункт меню «Проверка данных» -> вкладка «Параметры» -> условие проверки — «Список» реализовать выпадающее меню.

И, конечно, не забывайте ставить условия форматирования цветом, например для случаев, когда заполнены все необходимые строки в столбце через «Условное форматирование», например формула условного форматирования закрашивает ячейку, если следующие ячейки под ней содержат текст: =И(ДЛСТР(E5)>0; ДЛСТР(E6)>0)

2. Оптимизация кода + нововведения

Начать придется издалека, а именно вернуться к вопросу о реализации механизма заполнения шаблона. Если Вы решите заполнить шаблон в формате Excel и в формате Word, то это будут совершенно 2 разных механизма. В основе своей в файл Excel пишутся значения в конкретные ячейки файла или диапазоны ячеек и имеют привязку вида (у, х) (не спрашивайте почему у них строка идет впереди столбца при адресации — не знаю), например: Worksheet.Cells(y, x) = k. Отсюда же и первая мысль, что заполнять Excel-шаблон можно либо явным образом, т.е. непосредственно весь макрос будет содержать что откуда берется и куда закладывается, но что если придется вносить изменения в таблицы данных или выйдет новая форма шаблона? Отсюда вторая идея реализации, код которой описан в первой статье — это парсинг некоторых символов, которыми сперва заполняется массив, а так же в свою очередь содержит файл шаблона в нужных местах. Затем в каждой строке шаблона ищется совпадение с элементами массива поочередно, если совпадение есть, то порядковый номер массива привязан к строке таблицы откуда берутся данные, а столбец берется с листа в котором мы указываем какие именно акты мы хотим вывести. Итого несколько вложенных циклов, что накладывает ограничения на форматирование шаблона Excel, чем проще — тем лучше, потому что чем больше ячеек парсить — тем дольше будет происходить заполнение шаблона данными.

По многочисленным просьбам мною была интегрирована возможность вывода в шаблон формата Word, и здесь на самом деле есть 2 способа вывода текста:

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

Здесь вынесена в отдельную процедуру обращение к закладке и arrСсылкиДанных(i) — это массив который содержит управляющие символы. Издержки метода, если Вам потребуется сослаться на значение Закладки в другом месте, например дату нужно использовать в заголовке и напротив фамилии каждого подписанта, то необходимо использовать в шаблоне Меню «Вставка» -> пункт меню «Перекрестная ссылка» -> Тип ссылки: «Закладка», Вставить ссылку на: «Текст закладки» и снять галочку «Вставить как гиперссылку». Что бы это отобрадзилось корректно не забудте обновить в конце макроса перед выводом поля Wd.Fields.Update

2. Если рисовать таблицы средствами Word, то к ним можно обращаться с адресацией в ячейку
Здесь нужно обратить внимание, что у каждой таблицы в Word есть свой внутренний номер, методом нехитрого перебора Вы найдете нужный, а дальше принцип тот же, что и в Excel.

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

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

Шаблон Word при настройке автоматически переносит текст на последующую строку, если он не убрался по ширине ячейки/строки, однако этим самым он вызывает непрогнозируемый сдвиг текста по вертикали. Учитывая тот факт, что по требованиям к Исполнительной документации в строительстве ЗАПРЕЩЕНО один акт печатать на 2х и более листах, то это в свою очередь так же рождает проблемы.

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

Для первой строки:

<=ЕСЛИОШИБКА(ЕСЛИ($F$20<>"-"; ЕСЛИ(ДЛСТР('Данные для проекта'!$C$3)<106;'Данные для проекта'!$C$3; ПСТР('Данные для проекта'!$C$3;1;105-ПОИСКПОЗ(" *"; ПРАВСИМВ(ПСТР('Данные для проекта'!$C$3;1;105); СТРОКА($1:$10));))));"-")>

<=ЕСЛИОШИБКА(ЕСЛИ($F$20<>"-"; ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105-ПОИСКПОЗ(" *"; ПРАВСИМВ(ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105); СТРОКА($1:$10));)));"-")>

Здесь используется принцип массивов, т.е. вводится такой текст по Ctrl + Shift + Enter, а не обычному Enter. Сами формулы располагаются в ячейках F1 и F2. 'Данные для проекта'!$C$3 — ссылка на наименования объекта, длина текста которого более 105 символов. Перенос организуется в случае превышения длины текста в 105 символов.

Еще одним нововведением стал общий реестр, а так же контроль списания материалов по актам АОСР, но здесь ничего нового, просто парсинг соответствующих строк в свяске ИНДЕКС + ПОИСКПОЗ, которые расписаны во многих мануалах.

3. Структура и связи

Но мой пост так бы и остался рядовым постом с очередной игрой в изобретание велосипеда инструментами, которые рассчитаны на совершенно другое, если бы ни одно НО(!) Месячно-суточный график.

Идея о том, что можно именно на него много чего повесить, например заполнение Общего журнала работ в части Раздела 3 — наименование работ по датам, очередность и необходимость Актов освидетельствования скрытых работ и не только — завладела моими мыслями. Обычно в Excel закрашивают даты, в зависимости от диапазонов дат — начало и конец, но не на стройке. На стройке в календарном графике пишут объемы, а в зависимости от того с какой даты напротив наименования работ стоят объемы и по которую — получаются диапазоны дат отчетных периодов. На скриншоте серым помечены объемы попадающие в систематизированные отчетные периоды (1мес). Таким образом получается, что если:

Сразу оговорюсь из похожих тем нашел только "Автоматическое создание листа в книге." но это все же не совсем то.

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

Заранее благодарю если кто сможет помочь

Если просмотрел и такая тема есть дайте плиз ссылку.

Файл с примером прикрепил.

Заранее благодарю если кто сможет помочь

Если просмотрел и такая тема есть дайте плиз ссылку.

Файл с примером прикрепил. Semenbl4

Заранее благодарю если кто сможет помочь

Если просмотрел и такая тема есть дайте плиз ссылку.

Файл с примером прикрепил. Автор - Semenbl4
Дата добавления - 16.09.2014 в 12:08

Доброго времени суток! Подскажите как сделать так, чтобы при открытии книги создавался новый лист, с именем текущая дата. А если лист с данной датой уже существует, то лист создаваться не должен. Т.е. если открыть прикрепленный файл, то в книге появится лист с именем сегодняшней даты (на данный момент 03.09.2013). Спасибо!

И сколько таких листов должно быть? на месяц, год, пятилетку?

И сколько таких листов должно быть? на месяц, год, пятилетку? Michael_S

[/vba]каждый день будет создавать новый лист. Пока памяти у компа хватит.

[/vba]каждый день будет создавать новый лист. Пока памяти у компа хватит. Michael_S

[/vba]каждый день будет создавать новый лист. Пока памяти у компа хватит. Автор - Michael_S
Дата добавления - 03.09.2013 в 12:43

Если листы перемещаться не предполагается, можно упростить:
[vba]

[/vba] Автор - Michael_S
Дата добавления - 03.09.2013 в 12:49

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

Спасибо большое! Пойду пробовать! Автор - An4ous
Дата добавления - 03.09.2013 в 13:13

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

Первый вариант работает, второй ошибка во 2 строке. Спасибо еще раз!

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

Первый вариант работает, второй ошибка во 2 строке. Спасибо еще раз! An4ous

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

Первый вариант работает, второй ошибка во 2 строке. Спасибо еще раз! Автор - An4ous
Дата добавления - 03.09.2013 в 13:22

Вместо:
Sheets(Sheets(Sheets.Count)).Name
напишите:
Sheets(Sheets.Count).Name
и все будет работать.

Вместо:
Sheets(Sheets(Sheets.Count)).Name
напишите:
Sheets(Sheets.Count).Name
и все будет работать. KuklP

да, писал на коленке, не обратил внимания на лишний Sheets
[vba]

[/vba] Автор - Michael_S
Дата добавления - 03.09.2013 в 13:29

модуль книги
Private Sub Workbook_Open()
Dim i&
For i = Sheets.Count To 1 Step -1
If Sheets(i).Name = Str(Date) Then Exit Sub
Next
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Date
End Sub
каждый день будет создавать новый лист. Пока памяти у компа хватит.

Подскажите. Как сделать, чтобы создались вкладки с шаблоном и изменёнными данными, например 1 лист: имя листа Вексель №1, в листе Вексель №1, далее ничего не меняется кроме суммы:
2 548 200 (два миллиона пятьсот сорок восемь тысяч двести) рублей 00 копеек.
Номера векселей берутся из первого столбца листа список, а суммы из 9 столбца листа список.
Что то наковырял, но что то не работает (первый раз пытаюсь выяснить что такое макросы).

Читайте также: