Excel копировать строку по условию - Word и Excel - помощь в работе с программами

Здравствуйте, уважаемые форумчане.
Не прошло и полгода, и я опять прошу Вашей помощи.
Отписываюсь в этой теме, т.к. сложности плавно вытекли из неё, суть вопроса осталась та же, так как предложенное AlexM решение проблемы теперь не помогает. Проблема была такая:

«Есть файл (во вложении), в котором существует огромный список адресов (каждая строка это отдельный адрес с кучей нужной информации по нему).
Каждая строка (т.е. каждый адрес) имеет 2 состояния (столбец «H») «Закрыт» и «В работе».
Задача у меня такая: скопировать на другой лист, который «ОТК», все строки, имеющие статус «В работе».»

Теперь же появилось третье состояние этого столбца (в примере P) — «На выплату» (дополнительно меня нагрузили теперь бухгалтерскими вопросами) и данная формула не помогает:

Код

=ИНДЕКС(Адреса!$A$1:$A$175;НАИМЕНЬШИЙ(ЕСЛИ(Адреса!$H$5:$H$175<>"Закрыт";СТРОКА($A$5:$A$175));СТРОКА(A1)))

, т.к. в листе ОТК теперь выводятся и объекты в работе и на выплату.

Попытка заменить «<> «Закрыт» на «= «В работе» приводит у тому, что пустые клетки выдают ошибку «!#ЧИСЛО».
Собственно 1 вопрос в том, как грамотно поменять формулу, чтобы сохранить функционал.

Есть 2 вопрос, уже бухгалтерский: в том же примере есть окно «Бухгалтерия», в котором я пытался автоматизировать подсчет зарплаты. Т.е. мне надо, чтобы все объекты со статусом «На выплату» автоматически забивались в ведомость, соответственно если одна фамилия встречается более 1 раза, зарплата суммировалась.

Буду благодарен, если кто поможет решить задачу или подскажет, как её победить.
Заранее спасибо.

Источник

Миха73

Пользователь

Сообщений: 6
Регистрация: 11.11.2011

Доброе время суток, уважаемые форумчане! Столкнулся с проблемой, которая, откровенно говоря, привела меня в некий ступор.
Есть таблица на Листе1, которая ежедневно заполняется, необходимо из нее скопировать строки по условию значения в ячейке столбца C (он же 3)и вставить (добавлял) удовлетворяющую условию строку на Лист3. условием является шестизначное число (от 000000 до 999999).
Заранее Спасибо всем за интерес и ответы!
Поиск ничего не дал

KuklP Пользователь Сообщений: 14868 Регистрация: 21.12.2012 E-mail и реквизиты в профиле.	Чем автофильтр не устраивает? Я сам — дурнее всякого примера! …

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	Если сломан автофильтр…

Миха73 Пользователь Сообщений: 6 Регистрация: 11.11.2011	огромное спасибо! то, что надо!

	а нельзя сделать, чтобы при заполнении Листа3 данные добавлялись, а не заполнялись по новой?

Миха73 Пользователь Сообщений: 6 Регистрация: 11.11.2011	а нельзя сделать, чтобы при заполнении Листа3 данные добавлялись, а не заполнялись по новой?

Hugo

Пользователь

Сообщений: 23249
Регистрация: 22.12.2012

Как такой вариант — ставите вызов кода Юрия на событие активации листа (отключив обновление экрана на время работы).
Только нужно Cells первого листа указать, чьи они, а третьего тогда не надо.
И можно код на одну строку сократить:

If .Cells(i, 3) Like «######» Then

Миха73 Пользователь Сообщений: 6 Регистрация: 11.11.2011	попробую с этим разобраться

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	{quote}{login=}{date=11.11.2011 10:38}{thema=}{post}а нельзя сделать, чтобы при заполнении Листа3 данные добавлялись, а не заполнялись по новой?{/post}{/quote}А смысл? Результат будет тот же самый. Берите что дают, а то скачаю обратно.

Миха73

Пользователь

Сообщений: 6
Регистрация: 11.11.2011

Смысл есть, при изменении уже скопированных строк на Листе3 и последующем выполнении макроса измененные данные, на Листе3, будут переписаны и нужно будет либо по новой вносить изменения или дублировать полученные данные на другом листе.

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	Изменяют строки обычно в исходных данных, а по ним уже строится отчёт. Если Вы планируете изменять сам отчёт — делайте это на копии листа 3.

Миха73 Пользователь Сообщений: 6 Регистрация: 11.11.2011	да, обычно так и происходит, в смысле изменение данных в исходном документе, работать с копией полученного отчета, не проблема, вот только при создании нового отчета нужно будет учитывать, ранее полученный отчет…

Просматривая ответ на интересующий меня вопрос, нашел данную тему. Очень помогла. Скажите, а как можно было бы ограничить число копируемых ячеек в строке, например если исходная таблица имеет 10 ячеек в строке, а на том листе, куда переноситься (лист2 например) необходимы только 4 первых ячейки в строке (4 столбца)?

Hugo Пользователь Сообщений: 23249 Регистрация: 22.12.2012	Range(Cells(i, 1), Cells(i, 4)).Copy

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	Если правильно понял вопрос: Range(Cells(i, 1), Cells(i, 4)).Copy .Cells(LastRow + 1, 1)

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	#16 21.08.2012 16:02:45 {quote}{login=}{date=21.08.2012 03:52}{thema=}{post}Просматривая ответ на интересующий меня вопрос{/post}{/quote}Просматривая Ваш вопрос, обнаружил, что Вы не подписываетесь. Анонимно легче? Наоборот: многие их тут не любят

Источник

Копирование строк по условию

Ольга93

Дата: Четверг, 25.04.2013, 21:15 |
Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Здравствуйте. Подскажите пожалуйста, как автоматически копировать строки из первого листа на новый (включая столбцы A-I), если в столбце J истина. Можно ли это сделать с помощью встроенных функций, или нужен макрос?
заранее спасибо smile

Сообщение отредактировал Ольга93 — Четверг, 25.04.2013, 23:14

Ответить

AlexM

Дата: Четверг, 25.04.2013, 21:44 |
Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4257

Репутация:
1046
±

Замечаний:
0% ±

Excel 2003

Цитата (Ольга93)

Можно ли это сделать с помощью встроенных функций

Нельзя

Цитата (Ольга93)

или нужен макрос

Нужен

Цитата (Ольга93)

автоматически копировать

Макрос с запуском по событию на листе
smile

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

Ольга93

Дата: Четверг, 25.04.2013, 23:18 |
Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Спасибо

Сообщение отредактировал Ольга93 — Четверг, 25.04.2013, 23:19

Ответить

Ольга93

Дата: Пятница, 26.04.2013, 09:08 |
Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

А если не автоматически копировать?

Ответить

ShAM

Дата: Суббота, 27.04.2013, 05:01 |
Сообщение № 5

Группа: Друзья

Ранг: Старожил

Сообщений: 1347

Репутация:
249
±

Замечаний:
0% ±

Excel 2010

Может, ИНДЕКС и ПОИСКПОЗ помогут? Но это точно будет не «копирование».

Ответить

Ольга93

Дата: Суббота, 27.04.2013, 20:58 |
Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

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

Ответить

taxi_driver

Дата: Суббота, 27.04.2013, 21:05 |
Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 34

Репутация:
1
±

Замечаний:
0% ±

Ольга93, файл примера бы было не плохо, а то слово истина слишком широкое понятие

Ответить

AlexM

Дата: Суббота, 27.04.2013, 21:06 |
Сообщение № 8

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4257

Репутация:
1046
±

Замечаний:
0% ±

Excel 2003

Вы же к Сообщению №3 хотели добавить файл. Почему-то передумали. ???

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

Ольга93

Дата: Суббота, 27.04.2013, 22:48 |
Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Файл прикрепляла несколько раз, пишет что прикреплено, но почему то не отображается:(

Ответить

Pelena

Дата: Суббота, 27.04.2013, 22:51 |
Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 18797

Репутация:
4284
±

Замечаний:
±

Excel 2016 & Mac Excel

Цитата (Ольга93)

Файл прикрепляла несколько раз

Ольга93, Вы, наверное, Правила форума невнимательно прочитали

«Черт возьми, Холмс! Но как??!!»
Ю-money 41001765434816

Ответить

Ольга93

Дата: Суббота, 27.04.2013, 23:58 |
Сообщение № 11

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Пробую еще раз)

Сообщение отредактировал Ольга93 — Воскресенье, 28.04.2013, 00:01

Ответить

AlexM

Дата: Воскресенье, 28.04.2013, 00:00 |
Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4257

Репутация:
1046
±

Замечаний:
0% ±

Excel 2003

Файл должен быть до 100Кб

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

ShAM

Дата: Воскресенье, 28.04.2013, 00:00 |
Сообщение № 13

Группа: Друзья

Ранг: Старожил

Сообщений: 1347

Репутация:
249
±

Замечаний:
0% ±

Excel 2010

Пусть даже не Правила. Здесь же внизу (между «Прикрепить файл» и «Добавить ответ») все написано.

Ответить

Ольга93

Дата: Воскресенье, 28.04.2013, 00:07 |
Сообщение № 14

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Ответить

AlexM

Дата: Воскресенье, 28.04.2013, 00:21 |
Сообщение № 15

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4257

Репутация:
1046
±

Замечаний:
0% ±

Excel 2003

Предполагаю, вы не смогли найти правила форума.
Рекомендации по составлению примера из правил.

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

Ольга93

Дата: Воскресенье, 28.04.2013, 00:24 |
Сообщение № 16

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Хорошо, исправлюсь)

К сообщению приложен файл:

20_15.xls
(34.5 Kb)

Сообщение отредактировал Ольга93 — Воскресенье, 28.04.2013, 00:27

Ответить

ShAM

Дата: Воскресенье, 28.04.2013, 01:25 |
Сообщение № 17

Группа: Друзья

Ранг: Старожил

Сообщений: 1347

Репутация:
249
±

Замечаний:
0% ±

Excel 2010

Можно отфильтровать по ИСТИНЕ в столбце J. Потом выделяем нужный диапазон, жмем F5, Выделить, Только видимые ячейки, ОК.
Копируем, переходим на Лист1, вставляем. Убираем автофильтр.
Записал макрорекордером, немного подкорректировал и вот, что получилось:
[vba]

Код

Sub Макрос1()
Application.ScreenUpdating = False
       Dim LastRow As Long
       LastRow = Cells(Rows.Count, 10).End(xlUp).Row
       Range(«$A$2:$J$» & LastRow).AutoFilter Field:=10, Criteria1:=»ИСТИНА»
       Range(«A1:I» & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets(«Лист1»).[a1]
       Range(«A2:J» & LastRow).AutoFilter
Application.ScreenUpdating = True
End Sub

[/vba]

Сообщение отредактировал ShAM — Воскресенье, 28.04.2013, 01:36

Ответить

AlexM

Дата: Воскресенье, 28.04.2013, 01:28 |
Сообщение № 18

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4257

Репутация:
1046
±

Замечаний:
0% ±

Excel 2003

Вариант с формулой, в составе которой именованная формула.

Код

=ЕСЛИ(НИРиД<9^9;ИНДЕКС(НИР_20_15!$A$1:$I$1000;НИРиД;СТОЛБЕЦ(A1));»»)

Именованная формула НИРиД

Код

=НАИМЕНЬШИЙ(ИНДЕКС((НИР_20_15!$J$1:$J$1000<>ИСТИНА)*9^9+СТРОКА(НИР_20_15!$J$1:$J$1000););СТРОКА(НИР_20_15!A1))

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM — Воскресенье, 28.04.2013, 01:35

Ответить

AlexM

Дата: Воскресенье, 28.04.2013, 02:17 |
Сообщение № 19

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4257

Репутация:
1046
±

Замечаний:
0% ±

Excel 2003

Еще вариант макроса

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM — Воскресенье, 28.04.2013, 02:21

Ответить

Serge_007

Дата: Воскресенье, 28.04.2013, 07:34 |
Сообщение № 20

Группа: Админы

Ранг: Местный житель

Сообщений: 15888

Репутация:
2623
±

Замечаний:
±

Excel 2016

Цитата (Ольга93)

пишет что прикреплено

Зачем обманывать? Нет на форуме такой опции (надписи при создании поста о том что файл прикреплён)

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Источник

Всем добра.

Задача:

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

Мысли:

Когда у меня первый раз спросили про такое, я ответил- фигушки, без макроса никак, по крайней мере для неопределенного количества найденных строк.

Спустя время мне задали этот вопрос повторно и тут меня осенило — это не невозможно.

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

на деле это выглядит так:
Искать будем значение из ячейки $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″;ЛОЖЬ);»дд.ММ.гггг»);»»)

Для двойных/тройных условий в поиске предлагаю использовать доп колонку, в таблице поиска, с конкатенацией колонок.

Источник

Копирование строк по условию из существующего набора данных в отдельную таблицу с помощью кода VBA Excel. Определение числа строк в исходной таблице.

Условие задачи

Есть исходная таблица (набор данных) со списком файлов, расположенных в двух папках. Необходимо строки таблицы, содержащие слово «Изображения», скопировать в новую таблицу, расположенную ниже исходного набора данных, через одну пустую строку. В результате должно получиться, как на изображении ниже:

Решение задачи

Код VBA Excel для копирования строк исходного набора данных по условию в отдельную таблицу:

Sub KopirovaniyeStrok()

Dim s As String, n As Long, m As Long, i As Long

‘Задаем условие поиска

s = «Изображения»

‘Определяем номер последней строки исходной таблицы

n = Range(«A2»).CurrentRegion.Rows.Count

‘Задаем номер первой строки новой таблицы

m = n + 2

For i = 2 To n

‘Проверяем условие

If Cells(i, 1) = s Then

‘Копируем строку, удовлетворяющую условию, в новую таблицу

Cells(i, 1).Resize(1, 3).Copy Cells(m, 1)

m = m + 1

End If

End Sub

При желании, можно добавить в эту процедуру еще одну переменную и автоматическое определение количества столбцов:

Dim c As Long

c = Range(«A2»).CurrentRegion.Columns.Count

Тогда выражение копирования примет следующий вид:

Cells(i, 1).Resize(1, c).Copy Cells(m, 1)

Источник

Условие задачи

Решение задачи

А вот еще интересные статьи: