Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Перенос всех строк с заданным условием или перечнем на...... - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Перенос всех строк с заданным условием или перечнем на...... (Макросы/Sub)
Перенос всех строк с заданным условием или перечнем на......
SVFB Дата: Вторник, 21.06.2022, 15:01 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 20% ±

Перенос всех строк с заданным условием или перечнем на другой лист или в другой файл.

Добрый день. Пожалуйста помогите с решением проблемы. Читал похожие темы, но они не подходят под мою задачу. Очень прошу помочь мне разобраться. Есть таблица в excel (пример (лист 1)) в ней может быть сколько угодно колонок, сколько угодно строчек. Колонки могут варьироваться от 1 до бесконечности, строки точно также от 1 до бесконечности. Насчет одной я конечно утрирую, их там всегда много. Просто чтобы было понимание, что это изменяемая величина как в 1 так и во втором случае.
Но как говорится рандомно в какой-то колонке появляется определенное значение. Сегодня это один столбец, завтра другой и т.п. Но есть перечень этих отдельных значений (перечень и перечень 1). Сохранил в разных вариантах т.к. не знаю с чем будет проще работать. Он может быть как в отдельном файле тхт или таблице excel (перечень и перечень 1). Но и этот перечень тоже может меняться, сегодня это один список значений, завтра это другой список. Но список есть всегда. И поиск в одной таблице должен происходить по текущему перечню. Мне нужно чтобы исходная таблица (пример (лист 1)) производила поиск из перечня (перечень или перечень 1) и переносила целиком всю строчку в которой найден один из элементов перечня на отдельный лист. Для примера: в таблице (пример (лист 1)), есть сколько-то строчек в которых в каком-то рандомном столбце есть значение которое указано в перечне в отдельном файле, на отдельном листе и т.п. (перечень или перечень 1). Дальше случается непостижимая пока для меня магия, которую я надеюсь вы мне покажите. И все строчки в которых есть любой из элементов перечня переносятся на отдельный лист или в отдельный файл. Пример: (Пример (лист 2)). В данном случае я табличку своял на коленке так сказать и на примере колонки серийный номер нужно найти все строчки в которых встречается "11112233445566700000" и перенести их на отдельный лист или в отдельный файл. В дальнейшем таблица будет совсем другая. В идеале чтобы при переносе копировалась и шапка самой таблицы с наименованиями, но это уже не так важно. И как вишенка на торте, чтобы потом по какой-то из колонок считалась авто сумма или сумма всех значений. Для примера: (пример (лист 2)) колонка поступило за месяц. Но это уже не так важно так сказать. Очень надеюсь на вашу помощь. Спасибо.
К сообщению приложен файл: 5146996.xlsx (16.4 Kb) · 2102337.xlsx (7.8 Kb)


Сообщение отредактировал SVFB - Вторник, 21.06.2022, 15:04
 
Ответить
СообщениеПеренос всех строк с заданным условием или перечнем на другой лист или в другой файл.

Добрый день. Пожалуйста помогите с решением проблемы. Читал похожие темы, но они не подходят под мою задачу. Очень прошу помочь мне разобраться. Есть таблица в excel (пример (лист 1)) в ней может быть сколько угодно колонок, сколько угодно строчек. Колонки могут варьироваться от 1 до бесконечности, строки точно также от 1 до бесконечности. Насчет одной я конечно утрирую, их там всегда много. Просто чтобы было понимание, что это изменяемая величина как в 1 так и во втором случае.
Но как говорится рандомно в какой-то колонке появляется определенное значение. Сегодня это один столбец, завтра другой и т.п. Но есть перечень этих отдельных значений (перечень и перечень 1). Сохранил в разных вариантах т.к. не знаю с чем будет проще работать. Он может быть как в отдельном файле тхт или таблице excel (перечень и перечень 1). Но и этот перечень тоже может меняться, сегодня это один список значений, завтра это другой список. Но список есть всегда. И поиск в одной таблице должен происходить по текущему перечню. Мне нужно чтобы исходная таблица (пример (лист 1)) производила поиск из перечня (перечень или перечень 1) и переносила целиком всю строчку в которой найден один из элементов перечня на отдельный лист. Для примера: в таблице (пример (лист 1)), есть сколько-то строчек в которых в каком-то рандомном столбце есть значение которое указано в перечне в отдельном файле, на отдельном листе и т.п. (перечень или перечень 1). Дальше случается непостижимая пока для меня магия, которую я надеюсь вы мне покажите. И все строчки в которых есть любой из элементов перечня переносятся на отдельный лист или в отдельный файл. Пример: (Пример (лист 2)). В данном случае я табличку своял на коленке так сказать и на примере колонки серийный номер нужно найти все строчки в которых встречается "11112233445566700000" и перенести их на отдельный лист или в отдельный файл. В дальнейшем таблица будет совсем другая. В идеале чтобы при переносе копировалась и шапка самой таблицы с наименованиями, но это уже не так важно. И как вишенка на торте, чтобы потом по какой-то из колонок считалась авто сумма или сумма всех значений. Для примера: (пример (лист 2)) колонка поступило за месяц. Но это уже не так важно так сказать. Очень надеюсь на вашу помощь. Спасибо.

Автор - SVFB
Дата добавления - 21.06.2022 в 15:01
SVFB Дата: Вторник, 21.06.2022, 15:05 | Сообщение № 2
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 20% ±

По какой-то причине, не дает вставить текстовый файл с перечнем. Но он такой же как и эксель, только в виде файла тхт.
К сообщению приложен файл: _1.txt (0.2 Kb)
 
Ответить
СообщениеПо какой-то причине, не дает вставить текстовый файл с перечнем. Но он такой же как и эксель, только в виде файла тхт.

Автор - SVFB
Дата добавления - 21.06.2022 в 15:05
SVFB Дата: Вторник, 21.06.2022, 15:14 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 20% ±

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

[vba]
Код
Sub Cheezy()
'Updated by Kutools for Excel 2017/8/28
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").Us edRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("G1:G" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "11223344556677800000" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "11223344556677800000" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
[/vba]

К сожалению мои познания ничтожны, и все что я смог это подставить определенную цифру и прописать ее колонку(столбец). Я абсолютно не понимаю как это работает и отсюда есть много но. Так как я совсем ничего не понимаю в этом и не знаю как адаптировать под свой вариант для большого количества переменных. Из минусов тут, при отработке не копирует шапку таблицы, если нет листа 2 то не работает и по какой-то причине работает всего 1 раз, потом надо удалять лист 2 и снова создавать, после этого можно выполнить еще раз и отработать следующее значение. Но мне нужно чтобы вместо одного значения, сразу искало все те значения которые есть в перечне который можно будет постоянно менять, у меня их может быть до 1500 шт. Ну и как вишенка, не считает авто сумму например по колонке Q. Но это уже мелочи, главное чтобы работало по списку а не по одному значению, суммы это все фигня.


Сообщение отредактировал Serge_007 - Вторник, 21.06.2022, 15:37
 
Ответить
СообщениеНашел на просторах инета вот такую вот вариацию. Адаптировал ее под свой пример, на примере колонки G. Чтобы искало значения этого столбца. Но этот вариант ищет только одно конкретное значение, а не весь перечень который есть.

[vba]
Код
Sub Cheezy()
'Updated by Kutools for Excel 2017/8/28
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").Us edRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("G1:G" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "11223344556677800000" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "11223344556677800000" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
[/vba]

К сожалению мои познания ничтожны, и все что я смог это подставить определенную цифру и прописать ее колонку(столбец). Я абсолютно не понимаю как это работает и отсюда есть много но. Так как я совсем ничего не понимаю в этом и не знаю как адаптировать под свой вариант для большого количества переменных. Из минусов тут, при отработке не копирует шапку таблицы, если нет листа 2 то не работает и по какой-то причине работает всего 1 раз, потом надо удалять лист 2 и снова создавать, после этого можно выполнить еще раз и отработать следующее значение. Но мне нужно чтобы вместо одного значения, сразу искало все те значения которые есть в перечне который можно будет постоянно менять, у меня их может быть до 1500 шт. Ну и как вишенка, не считает авто сумму например по колонке Q. Но это уже мелочи, главное чтобы работало по списку а не по одному значению, суммы это все фигня.

Автор - SVFB
Дата добавления - 21.06.2022 в 15:14
SVFB Дата: Вторник, 21.06.2022, 15:16 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 20% ±

Помогите пожалуйста адаптировать под работу со списком а не с одним условием. И объясните пожалуйста, почему работает только один раз и надо потом удалять и снова создавать листы?
 
Ответить
СообщениеПомогите пожалуйста адаптировать под работу со списком а не с одним условием. И объясните пожалуйста, почему работает только один раз и надо потом удалять и снова создавать листы?

Автор - SVFB
Дата добавления - 21.06.2022 в 15:16
Pelena Дата: Вторник, 21.06.2022, 16:36 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Обязательно макросом? Расширенный фильтр не вариант? Всего несколько кликов мышкой
Ограничение: список искомых значений должен быть в Excel и результат будет расположен на том же листе, что и список.
В приложенном файле поиск идет по первым трём номерам (желтым).
Если очень хочется макрос, то можно записать макрорекодером)
К сообщению приложен файл: 6222925.xlsx (20.1 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Обязательно макросом? Расширенный фильтр не вариант? Всего несколько кликов мышкой
Ограничение: список искомых значений должен быть в Excel и результат будет расположен на том же листе, что и список.
В приложенном файле поиск идет по первым трём номерам (желтым).
Если очень хочется макрос, то можно записать макрорекодером)

Автор - Pelena
Дата добавления - 21.06.2022 в 16:36
SVFB Дата: Вторник, 21.06.2022, 17:24 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 20% ±

Обязательно макросом?


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

Расширенный фильтр не вариант? Всего несколько кликов мышкой

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

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

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

Если очень хочется макрос, то можно записать макрорекодером)

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

Большое спасибо за файл, сейчас посмотрю.
 
Ответить
Сообщение
Обязательно макросом?


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

Расширенный фильтр не вариант? Всего несколько кликов мышкой

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

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

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

Если очень хочется макрос, то можно записать макрорекодером)

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

Большое спасибо за файл, сейчас посмотрю.

Автор - SVFB
Дата добавления - 21.06.2022 в 17:24
Pelena Дата: Вторник, 21.06.2022, 18:06 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
по пунктам

1) на отдельный лист выкладываем список искомых значений. ВАЖНО: шапка столбца со списком должна в точности совпадать с названием столбца в основной таблице, по которому должен идти поиск. В предыдущем примере это серийный номер
2) находясь на листе со списком, нажимаем кнопку Дополнительно на листе Данные. Открывается диалоговое окно Расширенный фильтр
3) ставим переключатель Скопировать в другое место
4) ставим курсор в поле Исходный диапазон, переходим на лист с исходной таблицей и выделяем её полностью. Для быстрого выделения: становимся в верхнюю левую ячейку таблицы -- нажимаем сочетание клавиш Ctrl+Shift+End
5) ставим курсор в поле Диапазон условий (если в этом поле что-то написано, лучше удалить). Выделяем список искомых значений, обязательно захватывая шапку с названием столбца (серийный номер)
6) ставим курсор в поле Поместить результат в диапазон (если в этом поле что-то написано, лучше удалить). Кликаем мышкой на пустом месте на листе - это будет левая верхняя ячейка отфильтрованной таблицы
7) нажимаем ОК

Ну а на написание макроса надо больше времени, поэтому позже


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
по пунктам

1) на отдельный лист выкладываем список искомых значений. ВАЖНО: шапка столбца со списком должна в точности совпадать с названием столбца в основной таблице, по которому должен идти поиск. В предыдущем примере это серийный номер
2) находясь на листе со списком, нажимаем кнопку Дополнительно на листе Данные. Открывается диалоговое окно Расширенный фильтр
3) ставим переключатель Скопировать в другое место
4) ставим курсор в поле Исходный диапазон, переходим на лист с исходной таблицей и выделяем её полностью. Для быстрого выделения: становимся в верхнюю левую ячейку таблицы -- нажимаем сочетание клавиш Ctrl+Shift+End
5) ставим курсор в поле Диапазон условий (если в этом поле что-то написано, лучше удалить). Выделяем список искомых значений, обязательно захватывая шапку с названием столбца (серийный номер)
6) ставим курсор в поле Поместить результат в диапазон (если в этом поле что-то написано, лучше удалить). Кликаем мышкой на пустом месте на листе - это будет левая верхняя ячейка отфильтрованной таблицы
7) нажимаем ОК

Ну а на написание макроса надо больше времени, поэтому позже

Автор - Pelena
Дата добавления - 21.06.2022 в 18:06
SVFB Дата: Вторник, 21.06.2022, 18:52 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 20% ±

излишнее цитирование удалено
Спасибо, у меня все получилось. Но столкнулся со следующими проблемами:
- после первого поиска, необходимо целиком очищать все что есть на листе с диапазоном.
- для повторного поиска нужно снова формировать новый расширенный фильтр и проделывать все пункты что описаны выше.
- фильтр отрабатывает только один раз
- если поменять наполнение исходной таблицы, результаты не меняются.
Огромное вам спасибо за помощь. Это существенно упрощает поиск и отбор данных. Это очень удобно и просто. Но как говорится на один два раза в день. А когда нужно эту манипуляцию проделывать по 15-20 раз, это уже совсем другое. Но это определенно в десятки раз быстрее чем это было раньше, в виде простого автофильтра и выставления галочек на нужных строчках. Спасибо.

Ну а на написание макроса надо больше времени, поэтому позже

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


Сообщение отредактировал Pelena - Вторник, 21.06.2022, 21:56
 
Ответить
Сообщениеизлишнее цитирование удалено
Спасибо, у меня все получилось. Но столкнулся со следующими проблемами:
- после первого поиска, необходимо целиком очищать все что есть на листе с диапазоном.
- для повторного поиска нужно снова формировать новый расширенный фильтр и проделывать все пункты что описаны выше.
- фильтр отрабатывает только один раз
- если поменять наполнение исходной таблицы, результаты не меняются.
Огромное вам спасибо за помощь. Это существенно упрощает поиск и отбор данных. Это очень удобно и просто. Но как говорится на один два раза в день. А когда нужно эту манипуляцию проделывать по 15-20 раз, это уже совсем другое. Но это определенно в десятки раз быстрее чем это было раньше, в виде простого автофильтра и выставления галочек на нужных строчках. Спасибо.

Ну а на написание макроса надо больше времени, поэтому позже

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

Автор - SVFB
Дата добавления - 21.06.2022 в 18:52
Pelena Дата: Вторник, 21.06.2022, 21:52 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Исходная таблица должна быть на Лист1, начиная с ячейки А1.
Список искомых значений на активном листе, начиная с А1. На этом же листе будет результат.
С какого листа макрос запустите, там и отработает.
В файле макрос можно запустить сочетанием клавиш Ctrl+я (в русской раскладке)
[vba]
Код
Sub AdvFilter()
    Application.ScreenUpdating = False
    With ActiveSheet
        .Cells(1, 3).CurrentRegion.Clear
        Sheets("Лист1").Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)), .Cells(1, 3)
    End With
    Application.ScreenUpdating = True
End Sub
[/vba]
К сообщению приложен файл: 5146996.xlsm (24.6 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеИсходная таблица должна быть на Лист1, начиная с ячейки А1.
Список искомых значений на активном листе, начиная с А1. На этом же листе будет результат.
С какого листа макрос запустите, там и отработает.
В файле макрос можно запустить сочетанием клавиш Ctrl+я (в русской раскладке)
[vba]
Код
Sub AdvFilter()
    Application.ScreenUpdating = False
    With ActiveSheet
        .Cells(1, 3).CurrentRegion.Clear
        Sheets("Лист1").Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)), .Cells(1, 3)
    End With
    Application.ScreenUpdating = True
End Sub
[/vba]

Автор - Pelena
Дата добавления - 21.06.2022 в 21:52
SVFB Дата: Среда, 22.06.2022, 14:14 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 20% ±

Pelena, Огромное вам спасибо. Работает на ура. Большое вам спасибо за ваш огромный труд и титаническую работу.
 
Ответить
СообщениеPelena, Огромное вам спасибо. Работает на ура. Большое вам спасибо за ваш огромный труд и титаническую работу.

Автор - SVFB
Дата добавления - 22.06.2022 в 14:14
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Перенос всех строк с заданным условием или перечнем на...... (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!