|
Копирование строки таблицы на другой лист по значению ячейки |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Как скопировать строки, если столбец содержит определенный текст / значение в Excel?
Предположим, вы хотите найти ячейки, содержащие определенный текст или значение в столбце, а затем скопировать всю строку, в которой находится найденная ячейка, как вы можете с этим справиться? Здесь я представлю несколько методов, чтобы определить, содержит ли столбец определенный текст или значение, а затем скопировать всю строку в Excel.
- Скопируйте строки, если столбец содержит определенный текст / значение с помощью команды фильтра в Excel
- Скопируйте строки, если столбцы содержат определенный текст/значение с помощью Kutools for Excel
- Копировать строки, если столбец содержит определенный текст / значение другого столбца
- Копировать строки на новый лист, если столбец содержит указанный текст / значение в Excel
Скопируйте строки, если столбец содержит определенный текст / значение с помощью команды фильтра в Excel
Этот метод поможет вам найти ячейки, содержащие определенный текст или значение в указанном столбце, сначала с помощью команды «Фильтр», а затем вы можете легко скопировать отфильтрованные строки в Excel.
1. Выберите столбец, который вы будете фильтровать, а затем щелкните значок Данные > Фильтр.
2. Щелкните кнопку со стрелкой справа от первой ячейки выбранного столбца, а затем щелкните значок Текстовые фильтры > Комплект из выпадающего списка.
3. Во всплывающем диалоговом окне Custom AutoFilter введите определенный текст или значение в поле за содержит и нажмите OK кнопка. Смотрите скриншот выше:
4. Теперь выберите все отфильтрованные строки и скопируйте их, нажав Ctrl + C ключи одновременно.
5. Выберите пустую строку или ячейку и вставьте отфильтрованную строку, нажав Ctrl + V одновременно.
Скопируйте строки, если столбцы содержат определенный текст/значение с помощью Kutools for Excel
Обычно мы можем легко найти ячейки, содержащие определенный текст или значение, с помощью команды «Найти» (Ctrl + F), но она не может выделить всю строку. Тем не мение, Kutools for Excel‘s Выберите специальные ячейки Функция может не только помочь нам найти ячейки с определенным текстом или значением, но и выбрать целые строки, в которых находятся найденные ячейки.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
1. Выберите столбцы, в которых вы найдете ячейки, содержащие определенный текст или значение.
2, Нажмите Кутулс > Выберите > Выбрать определенные ячейки.
3. В диалоговом окне Выбрать определенные ячейки (см. Снимок экрана выше)
(1) Проверьте Весь ряд вариант в Тип выбора раздел;
(2) В разделе «Определенный тип» щелкните первое поле и выберите Комплект из раскрывающегося списка, затем введите конкретный текст или значение в следующее поле (см. снимок экрана ниже);
(3) Щелкните значок Ok кнопку.
4. Затем появляется другое диалоговое окно Select Specific Cells. Щелкните значок OK чтобы закрыть его.
Все ячейки, содержащие определенный текст или значение в выбранных столбцах, обнаруживаются, и все строки с выбранными ячейками выбираются сразу.
5, нажмите Ctrl + C ключи тем временем, чтобы скопировать эти строки.
6. Выберите пустую строку или ячейку и вставьте эти строки, нажав Ctrl + V ключи тем временем.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Копировать строки, если столбец содержит определенный текст / значение другого столбца
Kutools for Excel’s Сравнить диапазоны Утилита предоставляет другой обходной путь для копирования строк, если столбец содержит определенный текст / значения другого столбца в Excel. Пожалуйста, сделайте следующее:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
1. Нажмите Кутулс > Выбрать> Выбрать одинаковые и разные ячейки.
2. В открывшемся диалоговом окне «Выбор одинаковых и разных ячеек» выполните следующие действия:
(1) Укажите столбец, который вы будете проверять, содержит ли конкретный текст / значение в Найдите значения в коробка;
(2) Укажите, что столбец содержит конкретный текст / значение в Согласно информации коробка;
Внимание: Пожалуйста, проверьте У моих данных есть заголовки варианты, основанные на вашей ситуации.
(3) Проверьте Те же ценности вариант в Найти раздел;
(4) Проверьте Выбрать целые строки опцию.
(5) Щелкните значок Ok кнопку, чтобы применить эту утилиту.
3. Затем появляется другое диалоговое окно «Сравнить диапазоны», в котором показано, сколько строк было выбрано. Пожалуйста, нажмите на OK чтобы закрыть его.
4. А затем скопируйте эти выделенные строки, нажав Ctrl + C одновременно, а затем вставьте в целевой диапазон по мере необходимости.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Копировать строки на новый лист, если столбец содержит указанный текст / значение в Excel
Kutools for Excel поддерживает другой обходной путь, который копирует целые строки на новые рабочие листы, если указанный столбец содержит определенный текст/значение с помощью утилиты Split Data. Вы можете сделать следующее:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
1. Выберите диапазон, в котором вы будете копировать строки на новые листы, если столбец содержит указанный текст / значение, и нажмите Кутулс Плюс > Разделить данные.
2. В открывшемся диалоговом окне «Разделить данные на несколько листов» установите флажок Конкретный столбец опцию и выберите имя столбца, в который вы будете копировать строки на основе раскрывающегося списка ниже; укажите правило именования новых листов и нажмите кнопку Ok кнопка. Смотрите левый снимок экрана:
И тогда вы увидите, что все строки в выбранном диапазоне были скопированы в новые рабочие листы, если указанный столбец содержит определенный текст / значение. Смотрите скриншот:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Демо: копирование строк, если столбец содержит определенный текст / значение в Excel
Статьи по теме
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
как в Excel Сделать так чтоб копировалась строка из таблицы с имеющимися данными и с самой решеткой таблицы ,на основании какого либо значения в определенной ячейке(если нет значения в ячейке строка не копируется)таким образом из множества данных таблицы ,заполняя определенные ячейки ,будет формироваться новая таблица заполненная в каждой строке . Все это на одном листе
Заранее благодарю
Это только макросом! Вам это нужно динамически? то есть чтоб при заполнении количестко строка автоматом копировалась в новую таблицу. Или по кнопке заполнили и нажали кнопку все колонки где количество заполнено скопировались?
Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.
Цитата: Poltava от 22.08.2012, 12:53
Это только макросом! Вам это нужно динамически? то есть чтоб при заполнении количестко строка автоматом копировалась в новую таблицу. Или по кнопке заполнили и нажали кнопку все колонки где количество заполнено скопировались?
Можно и формулой, но зачем , если есть фильтр по непустым.
А как воспользоваться этим фильтром
ЦитироватьМожно и формулой,
А по моему формулой как раз и нельзя. На сколько мне известно формулы не могут физически изменять содержимое ячейки (хотя может с формулами массива можно что то придумать), а просят именно копирование. А вот про фильтры это вы очень правильно вспомнили но опять таки обычный фильтр не скопирует данные только расширенный. Хотя если человеку нужно просто наглядно увидеть, или его устраивает копировать данные в ручную то тогда да
Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.
СПАСИБО БОЛЬШОЕ 
ZORRO2005 Вроде как проблема решена
Ну это не «копирование», а вычисление заведомо написанной формулой. Просто как говориться правильно заданный вопрос это пол ответа спрашивали ведь о копировании
Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.
хорошее решение, но придется формулы тянуть если таблица будет увеличиваться.
|
Миха73 Пользователь Сообщений: 6 |
Доброе время суток, уважаемые форумчане! Столкнулся с проблемой, которая, откровенно говоря, привела меня в некий ступор. |
|
KuklP Пользователь Сообщений: 14868 E-mail и реквизиты в профиле. |
Чем автофильтр не устраивает? Я сам — дурнее всякого примера! … |
|
Юрий М  Модератор Сообщений: 60570 Контакты см. в профиле |
Если сломан автофильтр… |
|
Миха73 Пользователь Сообщений: 6 |
огромное спасибо! |
|
а нельзя сделать, чтобы при заполнении Листа3 данные добавлялись, а не заполнялись по новой? |
|
|
Миха73 Пользователь Сообщений: 6 |
а нельзя сделать, чтобы при заполнении Листа3 данные добавлялись, а не заполнялись по новой? |
|
Hugo Пользователь Сообщений: 23249 |
Как такой вариант — ставите вызов кода Юрия на событие активации листа (отключив обновление экрана на время работы). If .Cells(i, 3) Like «######» Then |
|
Миха73 Пользователь Сообщений: 6 |
попробую с этим разобраться |
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
{quote}{login=}{date=11.11.2011 10:38}{thema=}{post}а нельзя сделать, чтобы при заполнении Листа3 данные добавлялись, а не заполнялись по новой?{/post}{/quote}А смысл? Результат будет тот же самый. Берите что дают, а то скачаю обратно. |
|
Миха73 Пользователь Сообщений: 6 |
Смысл есть, при изменении уже скопированных строк на Листе3 и последующем выполнении макроса измененные данные, на Листе3, будут переписаны и нужно будет либо по новой вносить изменения или дублировать полученные данные на другом листе. |
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
Изменяют строки обычно в исходных данных, а по ним уже строится отчёт. Если Вы планируете изменять сам отчёт — делайте это на копии листа 3. |
|
Миха73 Пользователь Сообщений: 6 |
да, обычно так и происходит, в смысле изменение данных в исходном документе, работать с копией полученного отчета, не проблема, вот только при создании нового отчета нужно будет учитывать, ранее полученный отчет… |
|
Просматривая ответ на интересующий меня вопрос, нашел данную тему. Очень помогла. Скажите, а как можно было бы ограничить число копируемых ячеек в строке, например если исходная таблица имеет 10 ячеек в строке, а на том листе, куда переноситься (лист2 например) необходимы только 4 первых ячейки в строке (4 столбца)? |
|
|
Hugo Пользователь Сообщений: 23249 |
Range(Cells(i, 1), Cells(i, 4)).Copy |
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
Если правильно понял вопрос: |
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
#16 21.08.2012 16:02:45 {quote}{login=}{date=21.08.2012 03:52}{thema=}{post}Просматривая ответ на интересующий меня вопрос{/post}{/quote}Просматривая Ваш вопрос, обнаружил, что Вы не подписываетесь. Анонимно легче? Наоборот: многие их тут не любят |
Всем добра.
Задача:
В некую таблицу скопировать все значения из столбца другой таблицы где значение справа равно некоторому.
Мысли:
Когда у меня первый раз спросили про такое, я ответил- фигушки, без макроса никак, по крайней мере для неопределенного количества найденных строк.
Спустя время мне задали этот вопрос повторно и тут меня осенило — это не невозможно.
Итак в чем заключается мысль?, а мысль в доп столбце, в котором хранится номер позиции вхождения в некотором диапазоне, где начало диапазона определяется предыдущим.
на деле это выглядит так:
Искать будем значение из ячейки $B$5 (в этой ячейке динамический выпадающий список) в другом листе:
=ПОИСКПОЗ($B$5;ЛИСТ2!B:B;0)
ПоискПоз — ищет вхождения в диапазоне
Результат формулы = 8, т.е. восьмая строчка
Проверяем, и вправду так.
Последующие ячейки при расчете будут учитывать предыдущую:
=ПОИСКПОЗ($B$5;ДВССЫЛ(«Карт.сч.!R»&$C16+1&»C2:R[1000]C2»;ЛОЖЬ);0)+C16)
Во втором аргументе формулы ПоискПоз ( ДВССЫЛ(«Карт.сч.!R»&$C16+1&»C2:R[1000]C2»;ЛОЖЬ); ) формируется диапазон $B$0+предыдущее положение+1 т.е. R9C2:R1000C2
Т.о. мы исключаем уже найденную строку из массива для поиска.
В итоге вот такой формулой
ДВССЫЛ(«Карт.сч.!R»&C16&»C5»;ЛОЖЬ)
Мы получаем значение из пятой колонки строки номер 8, и так далее.
Это можно записать в одну ячейку, но в моем случае получается очень громоздкая конструкция:
=ЕСЛИОШИБКА(ЕСЛИ(ЕПУСТО(ДВССЫЛ(«Карт.сч.!R»&ЕСЛИОШИБКА(ПОИСКПОЗ(ДВССЫЛ(«$B»&СТРОКА()-B17-10;ИСТИНА);ДВССЫЛ(«Карт.сч.!R»&$C16+1&»C2:R[1000]C2″;ЛОЖЬ);0)+C16;»-«)&»C5″;ЛОЖЬ));»АВАНС «;»»)&ТЕКСТ(ДВССЫЛ(«Карт.сч.!R»&ЕСЛИОШИБКА(ПОИСКПОЗ(ДВССЫЛ(«$B»&СТРОКА()-B17-10;ИСТИНА);ДВССЫЛ(«Карт.сч.!R»&$C16+1&»C2:R[1000]C2″;ЛОЖЬ);0)+C16;»-«)&»C3″;ЛОЖЬ);»дд.ММ.гггг»);»»)
Для двойных/тройных условий в поиске предлагаю использовать доп колонку, в таблице поиска, с конкатенацией колонок.
MulTEx »
17 Февраль 2014 19104 просмотров
Работа со строками/столбцами по условию
Данная функция является частью надстройки MulTEx
Вызов команды:
MulTEx -группа Ячейки/Диапазоны —Работа со строками/Столбцами —Работа со строками/столбцами по условию
Данная команда позволяет удалить, переместить или скопировать строки/столбцы по различным условиям:
Операция — выбор действия над строками/столбцами. Возможно Удаление, Перемещение и Копирование. Перемещение и Копирование возможно на новый лист или в указанный диапазон того же листа.
Удалить/Переместить/Скопировать — в данном разделе можно выбрать над чем производить Удаление/Копирование/Перемещение: над строками или столбцами, а так же выбрать диапазон для выполнения действий.
Строки — действия будут произведены со строками
Столбцы — действия будут произведены со столбцами
На всем листе — действия над строками/столбцами будут произведены на всем листе
В диапазоне — действия над строками/столбцами будут произведены только внутри указанного диапазона. На картинке выше указан диапазон Лист1!$A$1:$E$25. Это значит, что если выбрано Удаление Строк, то строки будут удалены только в этом диапазоне. Даже если строка 27 листа будет удовлетворять условиям, она не будет удалена, т.к. выходит за пределены указанного диапазона.
Переместить — указывается место назначения для копируемых или перемещаемых строк/столбцов.
На новый лист — в данном случае строки/столбцы будут вставлены на новый лист, который будут создан в ходе выполнения команды. Вставка отобранных строк/столбцов начинается с ячейки А1 нового листа.
Начиная с указанной ячейки — в данном случае строки/столбцы будут вставлены на тот же лист, в котором производится Копирование/Перемещение. Вставка отобранных строк/столбцов начинается именно с указанной ячейки. Примечание: Если указать диапазон для вывода результата внутри диапазона с данными для отбора строк/столбцов, то появится сообщение:
После этого лучше проверить заданные параметры и убедиться в том, что перемещение или копирование строк/столбцов не будет произведено в имеющиеся данные. Это сообщение может появиться даже в том случае, если в ячейках для вывода результата нет данных и в качестве исходных данных указано На всем листе. Что вполне логично, т.к. ячейка для вывода в любом случае расположена на этом листе.
Наименование данного раздела меняется в зависимости от выбранного действия и принимает три значения: Переместить, Скопировать и Не доступно. Не доступно появляется при выборе действия Удалить, т.к. в данном случае определение диапазона для вывода результата не требуется.
Переместить строки — указываются критерии для отбора строк/столбцов к удалению/перемещению/копированию.
- Все пустые — будут удалены все пустые строки/столбцы в указанном диапазоне.
- Если значение ячейки:Ниже в примерах применения данных параметров описаны операции по удалению строк, но все это применимо для любой выбранной операции.
- Равно — будут удалены те строки в ячейках которых есть значение, равное указанному. В критериях можно применять символы подстановки(звездочка(*), вопр.знак(?) ). Например на картинке указан критерий: «*С.Петербург*». Это означает, что если в ячейке будет записано «Произведено в г. С.Петербург 12.03.2008», то данная строка будет удалена. Если же указать «С?Петербург», то будут удалены строки, значение в которых равно либо «С.Петербург», либо «С-Петербург», либо «С Петербург» и т.п. Если же указать «С.Петербург», то будут удалены только те строки, значение которых в точности совпадает с «С.Петербург».
- Не равно — будут удалены те строки в ячейках которых нет значения, равного указанному. Все работает так же, как и при выборе Равно, но в обратном направлении. Т.е. если указать «С.Петербург», то будут удалены все строки, значение которых не равно «С.Петербург».
- Учитывать регистр — при отборе значений будет учитываться регистр критерия или нет. Если не установлено, то при указании равно «С.Петербург» будут удалены строки равные и значению «С.Петербург» и значению «с.петербург».
- В поле ниже указывается номер столбца, в котором просматривать эти критерии. Например, если в этом поле указать 2 и На всем листе, то критерии будут просматриваться во втором столбце листа — $B. Если указать 2 и диапазон $B$1:$E$25, то критерии будут просматриваться во втором столбце указанного диапазона, т.е. в третьем столбце листа — $C. Если данное поле оставить пустым, то значения будут просматриваться во всех столбцах диапазона.
Закрыть форму после выполнения операции — по умолчанию включен. Если снять, то после Удаления/Перемещения/Копирования строк/столбцов форма не закроется и можно будет указать следующий критерий.
Расскажи друзьям, если статья оказалась полезной:
Видеоинструкции по использованию надстройки MulTEx