Страницы: 1
RSS
Создание списка выборкой из двух листов по нескольким условиям
 
Есть выгрузка из программы по расположению вагонов. необходимо выбрать и создать список вагонов, вес которых равен нулю, при этом опереция должна содержать ВУ или станция назначения совпадать со станцией из списка.
Решение для вагонов весом ноль и операцией с содержанием ВУ найдено, но как создать список с вагонами, станция назначения которых совпадает со списком на другом листе и объеденить эти два списка в один? Усложнение задачи сделать выборку из двух листов с указанием типа вагона в зависимости из того, с какго листа было взято значение.  
 
Анна Таскаева, Ваше описание проблемы должно быть понятно человеку из другой предметной области и другой сферы деятельности. Поэтому, пожалуйста, расшифруйте: что за ВУ, какие 2 листа имеются в виду (в примере их больше 2), где должен быть вывод и в какой форме.
 
Есть два листа: ПВ и ДУМ (полувагоны и думкары). На каждом листе список вагонов состоящий из колонок:номер вагона, операция по вагону (некотоые операции начинаются на ВУ), станцией дислокации, дата и время прибытия на стинцию, станция назначения. Необходимо сделать выборку из списка из листов ПВ и ДУМ на лист Ремонтные. Условия вывода: вес вагона равен нулю и операция по нему начиниаться на ВУ, а так же все вагоны станция назначения которых совпадает с названием станции назначения из листа Список ремонтных ДПО(столбец А). Надеюсь так понятно.  
Изменено: Анна Таскаева - 28.11.2017 19:14:40
 
Здравствуйте! Вариант с доп.столбцами для поиска по станциям + соединение столбцов
 
Анна Таскаева, да, так понятно, спасибо. Если решение _Igor_61 Вам не подойдёт, завтра напишу более удобный, на мой взгляд, вариант решения без доп.столбцов.
 
Цитата
_Igor_61 написал:
Здравствуйте! Вариант с доп.столбцами для поиска по станциям + соединение столбцов
Не много не так)) С листа ПВ должны были попасть в список вагоны с 3 по 9 строку и вагоны с 10 по 18 строку. На листе Ремонтные я смогла вытянуть номера по вагонам, вес которых равен нулю и операция содержит ВУ (диапазон А2:А7), но вот вагоны, у которых станция назначения совпадает со списком, не смогла сделать. При этом, как видете, этот диапазон А2:А7 автоматически выкидывает все неподходящие вагоны, формируя список без пустых строк и дополнительных столбиков. В формуле даипазона А2:А7 листа Ремонтные учтено уловие вес=0, а операция содердит ВУ. Эта формула форирует массив только из подходящих мне вагонов и отображает их в той последовательности. в которой они находяться в таблице-источнике. Но я никак не могу придумать, как добавить выборку по условию, что мне подходят и вагоны, у которых станция отправления такая же, как в списе листа Ремонтные ДПО, не смотря на то, что операция не сожержит ВУ. В итоге должен получиться список из вагонов с 3 по 18 строку листа ПВ.  
 
Цитата
Irregular Expression написал:
вариант решения без доп.столбцов
Конечно напишите! Может мне подойдет Ваш вариант или меня посетит идея, как еще это можно сделать.  
 
.
Изменено: copper-top - 29.11.2017 02:41:25
 
Цитата
Анна Таскаева написал:
должны были попасть в список вагоны с 3 по 9 строку
Это вагоны по первому условию, для которых
Цитата
Анна Таскаева написал:
Решение для вагонов весом ноль и операцией с содержанием ВУ найдено
Вот и не стал заморачиваться с проверкой - правильно или не правильно "найдено", показал решение, как можно искать оставшиеся (по станции назначения):
Цитата
Анна Таскаева написал:
вагоны с 10 по 18 строку
Это работает. И работает также:
Цитата
Анна Таскаева написал:
объеденить эти два списка в один
Извините, что не догадался проверить правильно ли работает найденное Вами решение для первого условия  :)  
 
Кнопка цитирования не для ответа [МОДЕРАТОР]

copper-top, интересное решение. Ранее не работала с функцией АГРЕГАТ, использовала формулы массива. Вот только один момент смущает, что в случае добавления еще одного ремонтного ДПО его прийдется добавлять в формулу в ручную. Думала, что есть как-то возможность создать массив по первому условию (0 и ВУ) и по второму (совпадение с ремонными ДПО) и сцепить их вместе. При этом мечталось, что не надо прописывать в формуле каждое депо отдельно, а просто дать ссылку на диапазон с названиями ремонтных ДПО, а этот диапазон потом менять, как захочешь.
Первое условие я смогла реализовать так, что при копированиии формулы идет выборка из листа ПВ
Код
{=ИНДЕКС(ПВ!$A$1:$A$262;НАИМЕНЬШИЙ(ЕСЛИ((ПВ!$D$1:$D$262=0)*(ЛЕВСИМВ(ПВ!$E$1:$E$262;2)="ВУ");СТРОКА(ПВ!$A$1:$A$262);"");СТРОКА()-1))}

Пытылысь прописать второе условие через
Код
{=ЕСЛИ(ПВ!$L$2:$L$262='список ремонтных ДПО'!A1:A23;СТРОКА(ПВ!$A$1:$A$262))}

но потом сообразила, что данный способ не саботает, т.к. ячейка ПВ!$L$2 сравнивается с ячейкой 'список ремонтных ДПО'!A1, а ячейка ПВ!$L$3 с ячейкой 'список ремонтных ДПО'!A2. Кроме того длина деапазонов разная...

попробовала ввести формулу в ячейку С10 (см. вложенный файл)
Код
{=ИНДЕКС(ПВ!$A$1:$A$262;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ('список ремонтных ДПО'!$A$4:$A$23;ПВ!$L$2:$L$262)=1;СТРОКА(ПВ!$A$1:$A$262);"");СТРОКА()-1))}

И все бы хорошо, но на четвертом шаге вычесления функции 262 сторка выдает значение #н/д, хотя до этого показывала, что в даной ячейке значение ложь и формула просто должна была ее проигнорировать и не включать в массив. Сотвествеено дальнейшее вычисление не возможно... Если исправить эту ошибку, то  у меня должен таки выйти масив по второму условию. Останется только придумать, как массив по первому условию сцепить со вторым. Можно конечно вствить формулу игнорирования ошибок, но ведь в 262 строке есть значение и оно равно ЛОЖЬ. Почему на выходе получается #н/д - не пойму...
Изменено: Анна Таскаева - 29.11.2017 13:50:14
 
Анна Таскаева, готовое решение макросом (макросы должны быть разрешены). Меню запуска макросов - Alt + F8, посмотреть код - Alt + F11.
У меня 62 вагона отобралось на Вашем примере (т.е. вагоны с весом 0, у которых или станция назначения ремонтная, или операция на ВУ, или и то, и другое).

UPD: Обратил внимание, сравнивая вывод, что у Вас одна и та же станция в ПВ и ДУМ может называться иначе, чем в ремонтных (или "ВЧД Херсон" и "ХЕРСОН" - не одно и то же?). Гарантии, что макрос корректно обработает такие случаи я не дам - почистить входные данные от опечаток/лишних символов в списке ДПО и столбцах "станция назначения" Вам надо самой.  
Изменено: Irregular Expression - 29.11.2017 17:16:12
 
Анна Таскаева, рекомендую посмотреть в сторону бесплатной надстройки power query (раздел Данные в на закладках excel). Можно данные собирать, фильтровать и еще много-много чего, причем в отличие от VBA для этого там есть пользовательский интерфейс в виде кнопок. Там это достаточно тривиальная задача - собрать данные из любого кол-ва источников, фильтрануть, обработать, вывести в единый список. Причем сами исходные данные могут вообще находиться в других файлах.
 
Цитата
Irregular Expression написал:
или "ВЧД Херсон" и "ХЕРСОН" - не одно и то же?
одно и то же. Это список ремонтных немного не корректно составлен. Будет корректироватся по мере определения названия станции из базы данных
 
Цитата
Irregular Expression написал:
готовое решение макросом
Спасибо, посмотрю код. давно хочу освоить VBA, но пока руки до этого не доходят.
 
Цитата
tabularasa написал:
рекомендую посмотреть в сторону бесплатной надстройки power query
Никогда ее не пользовалась. Спасибо за совет. Поиграюсь.
 
Цитата
tabularasa написал:
Там это достаточно тривиальная задача - собрать данные из любого кол-ва источников, фильтрануть, обработать, вывести в единый список.
Попробовала. Что-то не вышло. Так как при фильтре происходит наложени условий: все вагоны с операцией ВУ автоматично отбрасывают в списке вагоны со станцие назначения Ремонтное ДЕПО. вагоны с операцией ВУ не имеют станцию назначения из списка репонтных ДПО, а вагоны со станцией ремотного ДПО не имеют операцию ВУ. А мне все эти вагоны необходимо собрать в один список.  
 
Анна Таскаева, если я правильно уловил суть, следует создать условный столбец, в котором сделать несколько условий:
если операция ВУ, то "Учитывать"
иначе если назначение ДЕПО, то "Учитывать"
иначе "Не учитывать"

следующим шагом фильтровать по "учитывать". Примерно так:
 
Я нашла решение более понятным для моего уровня методом

=ИНДЕКС(ПВ!$A$2:$A$262;НАИМЕНЬШИЙ(ЕСЛИ((ПВ!$D$2:$D$262=0)*(ЛЕВСИМВ(ПВ!$E$2:$E$262;2)="ВУ")+(СЧЁТЕСЛИ('список ремонтных ДПО'!$A$1:$A$23;ПВ!$L$2:$L$262)=1);СТРОКА(ПВ!$A$2:$A$262);"");СТРОКА()-1))

И скрыла ошибку через ЕСЛИОШИБКА

Теперь справа можно добавить остальные столбцы использую эту же формулу, а после добавить формулы для обработки таблицы. например, время простоя вагонов в ремонтном ДПО.
 
Цитата
Irregular Expression написал:
готовое решение макросом
спасибо! Решение хорошее, но не зная как работать в VBA, я не смогу его моделировать под каждые конкретные задачи, которые еще предстоят с этими данными.
 
Цитата
tabularasa написал:
следующим шагом фильтровать по "учитывать". Примерно так:
Я что-то до этого шага не дошла. не поняла, даже, как Вы смогли создать условный столбец. Хотельсь бы попробывать этот вариант. Так как в моем варианте возможна ошибка при наложении условий: в какй-то момент один из вагонов может начать отвечать всем условиям и при сложении массивов

ЕСЛИ((ПВ!$D$2:$D$262=0)*(ЛЕВСИМВ(ПВ!$E$2:$E$262;2)="ВУ")+(СЧЁТЕСЛИ('список ремонтных ДПО'!$A$1:$A$23;ПВ!$L$2:$L$262)=1)

вместо 0 и 1 выйдет 2. Этот вагон выпадет из списка
Изменено: Анна Таскаева - 29.11.2017 19:35:47
 
Анна Таскаева, там в редакторе powerquery есть закладки "преобразование", "добавление". Вот на закладке добавление есть кнопка "добавить условный столбец", которая открывает тот интерфейс, что я сфоткал.

Я понял, эту кнопку добавили в каком-то из обновлений excel. Если у вас он не обновляется, возможно у вас кнопки нет. Тогда два варианта - 1) обновлять или 2) писать строку запроса по созданию условного столбца вручную в редакторе, что уже требует знаний "кода".
Изменено: tabularasa - 02.12.2017 00:50:38
Страницы: 1
Наверх