Страницы: 1
RSS
ВПР минимальной даты или Аналог функции МИНЕСЛИ в офисе до 2019
 
Добрый день. Есть такая функция МИНЕСЛИ, в 2019 офисе, но к сожалению офис 2016, есть подозрение, что через нее можно получить требуемое.

Т.е. есть табличка, название/дата.
Из нее подтягиваем дату. Например
Код
=ВПР("*текст*"&с1;лист1!a:b;2;)

где с1 содержит название, которое ищем в лист1!a:b, *текст*- произвольное название, например города Москва,Ростов-на-Дону и т.п.... в зависимости от ситуации
Но так он находит первое встретевшиеся , сортировать лист 1 не вариант.

=МИН(ЕСЛИ или =НАИМЕНЬШИЙ(ЕСЛИ  не отрабатывают как надо из-за поиска с неточным соответствием "*текст*".

Есть ли решение? Может быть даже VBA и пользовательские функции... (желательно не формулы массива, я их боюсь)
Можно конечно через сводные таблицы и ВПРить от туда, но и это лишние телодвижения, огромный вес файлов, доп таблицы и вообще, очень не удобно
 
Формулы массива
Код
=50000-МАКС((ЛЕВСИМВ($B$3:$B$11;ДЛСТР($F9))=$F9)*(ЕСЛИОШИБКА(НАЙТИ(1;$B$3:$B$11);0)>0)*(50000-$C$3:$C$11))
=МАКС((ЛЕВСИМВ($B$3:$B$11;ДЛСТР($F9))=$F9)*(ЕСЛИОШИБКА(НАЙТИ(1;$B$3:$B$11);0)>0)*($C$3:$C$11))
 
МатросНаЗебре, что то не то, данные при сортировке меняются...
а при добавлении &"*1*" вообще ахинея
 
Цитата
написал:
данные при сортировке меняются...
Что-то не то делаете, у меня данные при сортировке не меняются.

Цитата
написал:
а при добавлении &"*1*" вообще ахинея
1 уже добавлен в формулы.
Код
...НАЙТИ(1;...
 
,ну не знаю. Да и не совсем она универсальная. Мой конечно косяк, в примере организация&"1",  по жизни мне надо "*текст*"&c1  , т.е. может быть любое кол-во знаков между "текстом" и с1

Пока нашел 2костыльных решения. 1 через сводную таблицу и вприть из нее, и сортировка листа 1, но можно просто не обратить внимание, что там отсортировано не как надо...

В любом случае, вариант по своему интересен. Спасибо
 
Для варианта "*текст*"& формулы примут вид:
Код
=50000-МАКС((ЕСЛИОШИБКА(НАЙТИ($F9;$B$3:$B$11);0)>0)*(ЕСЛИОШИБКА(НАЙТИ(1;$B$3:$B$11);0)>0)*(50000-$C$3:$C$11))
=МАКС((ЕСЛИОШИБКА(НАЙТИ($F9;$B$3:$B$11);0)>0)*(ЕСЛИОШИБКА(НАЙТИ(1;$B$3:$B$11);0)>0)*($C$3:$C$11))
Как я уже писал, это формулы массива. Вводятся через Ctrl+Shift+Enter.
 
=MIN(IF(ISNUMBER(SEARCH("*"&F3&"*";$B$3:$B$11));$C$3:$C$11))
По вопросам из тем форума, личку не читаю.
 
Цитата
mihail_ms: в офисе до 2019
в 2019ом тоже нет, если что  :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
БМВ, в принципе работает, но правда на большой таблице не правильно адрес встречается единожды, ВПР подтягивает правильно, а формула  ниже нет
Цитата
=МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК("*Ростов-на-Дону*"&B5;'лист1'!$D$2:$D$99999));'лист1'!$I$2:$I$99999))
не говоря уже о том, что почему то формула массива отдупляется через пару минут и т.к. я не умею с ними работать, то filldown кусок
Код
"*Ростов-на-Дону*"&B5
протягивает всегда как одно и то же B и приходится в сначало ctrl+d, а потом в каждую ячейку ctrl+shift+enter ,

короче, что то я делаю не так, а так как я формулы не держу в книге, что бы не грузить книгу, они у меня вставляются через макрос FormulaLocal = "=....", с масивами я так делать не пробовал, но пока и пробывать нет смысла, пока формула ошибается и отрабатывает оч. долго.
 
Цитата
mihail_ms написал:
а так как я формулы не держу в книге, что бы не грузить книгу, они у меня вставляются через макрос
Так делайте сразу в макросе... Зачем грабли то плодите?
 
, тут есть несколько "НО"
1. Я глуповат и не умею
2. Я глуповат и буду оч. долго этому учиться, курить полностью язык для сложно, я как обезьянка по аналогии, мне быстрее вставить/написать формулу, протянуть ее и если надо сохранить как значение
3. Макрос формирует некую таблицу с формулами, где я проверяю глазами адреса, и вот адреса могут быть записаны как корпус, строение, /, \, корп.  ну и т.д. я их конечно привожу к общему виду, но все учесть невозможно, так вот в этом промежуточном этапе я могу подправить адрес, а формулы обновятся и подтянут...
 
Цитата
mihail_ms написал:
1. Я глуповат и не умею
Так научитесь.

Ещё есть вариант через АГРЕГАТ. Судя по https://support.microsoft.com/ru-ru/office/%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D0%B0%D0%B3%D1%80%D0%B5%D0%B3%D0%B0%D1%82-43b9278e-6aa7-4f17-92b6-e19993fa26df с 2010 офиса сие есть.
 
агрегат может даже проиграть по скорости.
Не думаю что данных там на 100000 строк. запас в данном случае серьезно отражается на быстродействии формул. Можно либо диапазон выбирать соизмеримый с набором данных, или сделать динамический диапазон, ну и если данных много то однозначно переходить на иные решения, аля Query или VBA.
По вопросам из тем форума, личку не читаю.
 
Цитата
написал:
Так научитесь.
Легко сказать... может я гуманитарий...

А вообще если к поиску добавлять "*текст*", то без * , работает, а с * выдает #число.
А победа казалась так близка, в моем случае то, где ищем выглядит "какойнибудьтекстразное кол-вознаков,Ростов-на-Дону,какойнибудьтекстразное кол-вознаков,искомое в ячейках c1,с2 и т.д), соответственно ищу "*Ростов-на-Дону*"&c1  и т.п.
Изменено: mihail_ms - 06.12.2021 12:11:00
 
Цитата
mihail_ms написал:
А вообще если к поиску добавлять "*текст*", то без * , работает, а с * выдает #число.
Файл пример. Что и куда добавляли. В предложенном файле всё сработало.
Цитата
mihail_ms написал:
Легко сказать... может я гуманитарий...
Не имеет значения. Всем не легко.
 
, Ну как то примерно так.
Изменено: mihail_ms - 06.12.2021 12:38:29
 
mihail_ms, как по мне вполне адекватное поведение - "Аксай*Ленина 1" в списке нет ни одного... Если прям надо чтоб было АксайЧТОУГОДНОТУТЛенина 1, то ЛЕВСИМВ ПРАВСИМВ Вам в руки...
 
tutochkin,спасибо за уточнение, т.е он * воспринимает как *, а ни как ЧТОУГОДНОТУТ.
Уже направление, пошел пробывать...
 

Короче решил через ВПР со *, т.е  то что ищем подтягиваем через впр, и по сути в агрегате он подставляет то, что надо. В моем случае как бы оно...

=АГРЕГАТ(15;6;'лист1'!I:I/('лист1'!D:D=ВПР("*, Ростов*"&B2;'лист1'!D:D;1;));1)

 
Цитата
mihail_ms написал:
'лист1'!I:I/('лист1'!D:D=ВПР
сделаем планету ( я про землю) теплее  :D
По вопросам из тем форума, личку не читаю.
 
Цитата
написал:
сделаем планету ( я про землю) теплее  
Наполним небо добротой
 
mihail_ms, вы не поняли
если с ВПР("*, Ростов*"&B2;'лист1'!D:D;1;) еще худо бедно можно сгласится, оно определея используемую область автоматом и не шарится по миллиону строк, хотя если список не отсортирован то может найти Ростов-на-Дону вместо ожидаемого Ростова.
А вот далее вы скармливаете целый столбец и в агрегат поступает массив в миллион строк который надо ему перелопатить , исключить ошибки и выбрать минимальное.
Процессор все это время греет планету.
По вопросам из тем форума, личку не читаю.
 
, с майнерами мне все равно не сравниться.
так то агрегатор я ес-но поправлю с D:D на D2:D99999

А с впр "*, Ростов*" , этот момент я тоже осознаю, и учитываю при ручной проверке, просто на радостях не акцентировал внимание, все зависит от структуры таблитцы и ее логики, например осле Ростов может быть пробел или зпт, а так же он может быть Великим...
 
Цитата
mihail_ms написал:
с майнерами мне все равно не сравниться.
так дело не только в майнерах а в последующих соплях о том, что медленно все считается, в то время когда формула лопатит 90% ненужных данных. кстати не факт и я писал выше, что агрегат будет быстрее или показывать равную скорость. Тоже самое массивное через MIN может оказаться быстрее но надо проверять.
если "*, Ростов*" более менее устраивает, то добавьте пробел "*, Ростов *"
По вопросам из тем форума, личку не читаю.
 
, да все хорошо, обычно искать не более 200 строк в 50000, думаю агрегат самое то, не надо ctrl+shift+enter юзать.
Страницы: 1
Наверх