0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
1 | |
Excel Excel Макрос для копирования информации из одной таблицы в другую по условию10.03.2022, 17:13. Показов 13408. Ответов 52
Метки нет (Все метки)
Добрый день.
Помогите доработать медленный макрос (см.файл макрос Nik035) или возможно кто то предложит альтернативный более шустрый вариант кода для моей задачи: сам в этом совсем не разбираюсь В эксель есть две таблицы Левая - ОБЩАЯ исходная где есть все наименования и по которой идет отбор в СВОДНУЮ и только из тех строк для которых в ОБЩЕЙ проставлено какое то значение в графе - кол-во. т.е. выбор по условию кол-во ЗНАКОВ в графе (графа Кол. > 0) не всегда знаки это число может быть что то типа 1* или * с форматированием в цвет (бел) для отображения пустой строки В левой ОБЩЕЙ таблице будет порядка 200-300 наименований из которых по условию (графа Кол. > 0) строки копируются в СВОДНУЮ таблицу справа. Сводную перед заполнением наверно нужно предварительно очищать. Я так понимаю макрос из файла тормозит на переносе форматирования текста из ОБЩЕЙ таблицы, в принципе если по другому ускорить работу макроса не получится (кроме как убрав копирование этого форматирования текста) то можно этим пожертвовать или хотя бы оставить форматирование только для графы обозначение. Всем откликнувшимся, заранее ОГРОМНОЕ спасибо!
0
|
10.03.2022, 17:13 | |
Ответы с готовыми решениями:
52
Макрос для копирования информации с одного листа в другой по условию Формула для копирования части данных из одной ячейки в другую - MS Excel Макрос для переноса данных из одной таблицы в другую Макрос копирования ячеек по условию в Excel Макрос копирования ячеек по условию в Excel |
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
10.03.2022, 17:35 [ТС] | 2 |
может можно как то сделать что бы копирование было не по отдельным ячейкам, а сразу по строкам типа:
rng.Range(Cells(rw, 1), Cells(rw, 6)).Copy Cells(cnt, 12)
0
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
11.03.2022, 11:24 [ТС] | 3 |
Совсем совсем не кому помочь?
0
|
552 / 482 / 191
Регистрация: 11.12.2013
Сообщений: 2,500
|
|
11.03.2022, 11:34 | 4 |
для того, чтобы ускорить перенос данных сформируйте массив и скопируйте его.
0
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
||||||
11.03.2022, 12:04 [ТС] | 5 | |||||
Создать массив чего? и куда его скопировать
В файле задан диапазон ARM? но не знаю то это или не то? и если то то куда его нужно скопировать? Так же попутный вопрос как сделать что бы в сводной таблице отображались значения как текст а не формула (если она будет в общей таблице)? Добавлено через 9 минут макрос модифицировал...стало значительно лучше
При переносе листа в другой файл с наличием других диапазонов (печати, ВПР и т.д.) он отказывается работать...как быть? то же самое происходит если листы из другого файла перенести в данный файл с макросом. По итогу мне нужен общая книга эксель из нескольких листов в том числе с данным макросом Добавлено через 7 минут я совсем не специалист в макросах и поэтому предполагаю что проблема в наличии других разделов. Возможно что это и не так
0
|
Модератор
|
|
11.03.2022, 12:29 | 6 |
ceatv,
почему пункты 5.11 и 5.12 повторяются 2 раза, оба с количеством видимо и КИП , не имеющий количества , надо переносить, если он возглавляет группу строк с количеством
0
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
11.03.2022, 14:03 [ТС] | 7 |
Спасибо за вопросы...может хоть так получится найти проблему
учитывая что не совсем понимаю действий макроса (только по пояснениям для каждой строки) и то с натяжкой про п. 5.11 и 5.12 наверно имеется ввиду 5 и 6? 5. Dim rw As Integer ' номер строки в именованном диапазоне 6. Dim cl As Integer ' номер столба в именованном диапазоне п.6 - удалил...в отдельном файле макрос работает нормально, в другом файле с другими не привязанными к данному макросу диапазонами - не работает Добавлено через 1 час 14 минут Совсем нет ни каких предложений...что может быть не так в макросе? почему он не хочет работать в другом файле при наличии других именованных диапазонов с ним не связанных?
0
|
малоболт
1324 / 507 / 212
Регистрация: 30.01.2020
Сообщений: 1,230
|
||||||
11.03.2022, 14:21 | 8 | |||||
ceatv, Давайте пройдёмся по теории.
1. Скорость 1.1 Каждое одно обращение к диапазону ячеек кушает время одинаковое вне зависимости от числа ячеек в данном диапазоне. 1.2. Когда число обращений переваливает за 100 - время обработки начинает измеряться секундами. 1.3. Обработка даже очень больших массивов в памяти занимает доли секунды. То есть считать чохом лист с миллионом заполненных ячеек в массив = доли секунды. Читать его поячеечно = доли секунды * кол-во ячеек = могут быть десятки минут при больших объемах Обрабатывать данные миллиона ячеек, беря/сохраняя их из/в массивы - доли секунды Обрабатывать те же миллионы, читая/записывая в цикле каждый раз значения из/в ячейки = десятки минут, если не часы. Соответственно, если объём данных велик - оптимально считать всё в массив, обработать, подготовить выходной массив и залить его в нужный диапазон ячеек - такие программы отрабатывают даже на нескольких миллионах ячеек за время моргания глазом. Вот только, если в каждой ячейке использовано разное форматирование частей текста, разные шрифты, наклон, выделение жирным и т.п. - это в массив не запишется: там только текст. Поэтому всю красивость таким образом быстро не перенести, как ни старайся, а только через Copy-Paste. Здесь тоже можно уменьшить число обращений к ячейкам, постаравшись копировать максимально крупными регионами. Вот, например ниже ещё чуть более быстрый копировщик из вашего кода, который накапливает счётчик строк для копирования, пока не встретится пустая строка, и только потом переносит диапазон между пустыми строками в новое место. Если бы в нём ещё и количество не читать каждый раз из ячеек, а один раз считать в массив и потом перебирать элементы массива - было бы ещё быстрее. 2. Добавление листов и сбой работы макроса. Когда у вас более одного листа, нельзя обращаться просто к Range или Cells. Нужно указывать лист, на котором эти Range и Cells обретаются. Например: Sheets(1).Range(Sheets(1).Cells(2,5),Sheets(1).Cells(50,12)).Value = ... Или Sheets("ваше_имя_листа").Cells(2,12).Value = ... А если вы открываете одновременно более одного файла Excel, то надо конкретизировать к какому WorkBooks() относятся ваши листы: WorkBooks("test.xlsx").Sheets(1).Range... Вот пример, который должен работать при наличии нескольких листов:
0
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
11.03.2022, 14:52 [ТС] | 9 |
спасибо! попробую, отпишусь по результату
0
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
11.03.2022, 15:03 [ТС] | 10 |
В отдельном файле работает
при переносе в другой файл или при добавлении листов в этот файл с макросом из другого файла - не работает скан во вложении при последовательном нажатии F8 на следующем после желтой строки ошибка
0
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
11.03.2022, 15:10 [ТС] | 11 |
с ошибкой
0
|
малоболт
1324 / 507 / 212
Регистрация: 30.01.2020
Сообщений: 1,230
|
|
11.03.2022, 15:20 | 12 |
Данного именованного диапазона нет на данном листе. То есть либо при переносе теряются именованные диапазоны, либо добавляются диапазоны, которых нет на данном листе, либо что-то ещё с ними происходит. А так важно использовать именно именованные диапазоны? Нельзя просто по ячейкам определённых столбцов определённого листа идти?
0
|
552 / 482 / 191
Регистрация: 11.12.2013
Сообщений: 2,500
|
||||||
11.03.2022, 16:57 | 13 | |||||
Сообщение было отмечено ceatv как решение
Решение
попробуйте так (без именованных диапазонов):
1
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
11.03.2022, 17:31 [ТС] | 14 |
СУПЕР! Спасибо огромное...работает на ура!
работает быстро и в том файле где есть те самые другие диапазоны и Вы правы у тех диапазонов имен не было. Очень, очень помогли, СПАСИБО!...а то мучаюсь с этим макросом 3й день. Добавлено через 7 минут Еще подскажите такой момент если в Общей спецификации (левая таблица) в одной из строк есть формула ссылающаяся на значение из строк выше условно например есть поз. 1 кран ф15 - кол-во - 7 шт. Ниже есть строка поз. 155 резьба ф15 - кол-во 2х7=14 (где 7 - это кол-во из поз.1) предложенный макрос работает - но только с конкретными значениями...там где есть формула он вносит свои корректировки не поддающиеся логическому пониманию я так понимаю использовать формулы в Общей таблице - не желательно или есть способ обойти эту проблему? Добавлено через 6 минут В идеале конечно хотелось бы использовать формулы в левой части таблицы ОБЩАЯ, а в правую СВОДНУЮ переносить только текст (то как отображается в левой) с поддержкой форматирования (цвет, выделение) Добавлено через 11 минут Но даже без возможности использовать формулы - работает отлично, СУПЕР! Тестил при разных условиях - глюков не обнаружил (кроме переноса ячеек с формулами)
0
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
12.03.2022, 16:46 [ТС] | 15 |
И все же обнаружились некоторые странности в работе макроса
1. Если вся строка в левой части таблицы пустая (в т.ч. критерий кол-во) - макрос все равно переносит строку в правую таблицу 2. Если есть текст в столбце Наименование, но в графе кол-во - пусто - макрос все равно переносит строку в правую таблицу я для таких строк предполагал следующее в графе Кол-во сделать формулу - что то типа: - если сумма значений раздела больше 0 то в графе Кол-во символ ="пробел" и тогда раздел отобразиться в правой части - если сумма значений раздела меньше 0 то в графе символ ="" и тогда раздел НЕ отобразиться в правой части сводн.табл Но это наверно возможно только если макрос сможет читать результат формул как текст а не переносить сами формулы как сейчас??? в принципе есть и проблема с переносом формул...но это удалось решить изменив пути исходных значений в формуле на абсолютные
0
|
552 / 482 / 191
Регистрация: 11.12.2013
Сообщений: 2,500
|
|
12.03.2022, 16:59 | 16 |
Пустую строку вставлял специально т.к. считал что это разделитель одного типа оборудования от другого, это легко уберу.
0
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
12.03.2022, 17:48 [ТС] | 17 |
я так и подумал, но иногда она вставляется без какой либо логики...
проще в левой таблице в нужной для отображения пустой строке в кол-во проставить пробел и она отобразиться в сводной, но может так как есть и не хуже. Просто бывает (к примеру берем) раздел Демонтажные работы а он пустой - ни чего не демонтируется ( везде кол-во ="") и в сводной ни чего не отобразиться кроме Демонтажные работы - они отобразятся да же если кол-во в этой строке ="". Стоит ли усложнять макрос? если медленнее будет работать (не вижу смысла - лучше так) - тут же наверно придется что то придумывать что бы макрос смотрел не формулу, а результат, но как тогда сохранить форматирование результата (это мои мысли/размышления не специалиста) По сути просто проскакивают несколько лишних не заполненных разделов в сводной таблице и иногда несколько пустых строк. Добавлено через 4 минуты В любом случае и в таком виде макрос работает шикарно!!! спасибо
0
|
малоболт
1324 / 507 / 212
Регистрация: 30.01.2020
Сообщений: 1,230
|
||||||
12.03.2022, 18:18 | 18 | |||||
Сообщение было отмечено ceatv как решение
Решение
ceatv, Вот макрос, который по идее должен чистить формулы в 15 колонке в процессе переноса.
1
|
0 / 0 / 0
Регистрация: 19.12.2019
Сообщений: 46
|
|
12.03.2022, 19:25 [ТС] | 19 |
Вроде как все хорошо...макрос чуть медленнее предыдущего, но результат значительно лучше...особенно в плане формул, теперь левую часть таблицы можно автоматизировать.
Спасибо ОГРОМНОЕ! и извините что отвлекаю в выходной.
0
|
малоболт
1324 / 507 / 212
Регистрация: 30.01.2020
Сообщений: 1,230
|
|
12.03.2022, 19:30 | 20 |
Забыл вставить Application.ScreenUpdating = False в начале и его же = True в конце.
Если вставить самостоятельно - макрос ускорится.
0
|
12.03.2022, 19:30 | |
12.03.2022, 19:30 | |
Помогаю со студенческими работами здесь
20
Макрос копирования данных из одной книги в другую Макрос для добавления нужных строк из одной таблицы в другую Написать макрос для переноса данных из одной таблицы в другую по кнопке Макрос переноса информации с одной страницы на другую Макрос для копирования по условию Формирование SQL запроса копирования полей из одной таблицы в другую Искать еще темы с ответами Или воспользуйтесь поиском по форуму: |