V, спасибо!

V, прекрасно! Спасибо!
Можете подсказать логику формулы просмотр? (непонятно почему двойка, обратное значение ПОИСКа и вектор результатов)

Уважаемые форумчане, не придумав лаконичного решения решил задать вопрос.
Есть список адресов, в виде Московская область, г. Пущино, ул. Революции. Отдельно есть список городов.
Нужна формула, проверяющая, встречается ли название какого-либо города из списка городов в строке с адресом и если да - выводить рядом название города.

Пример во вложении.
Некрасивое решение 1 - искать пробелы/запятые в тексте через НАЙТИ и ЛЕВ/ПРАВСИМВ.
Некрасивое решение 2 - делать перебор ПОИСКом наименования каждого города в строке.
Красивое решение с формулой придумать не могу.

Всем спасибо, во всем разобрался!

ikki - да, я знаю про такую проверку, и планировал ее писать в excel (схема написана в вики), просто это дольше, сначала для быстрого анализа хотел исключить вот такие, явные, ошибки.

JayBhagavan - ваш поиск совсем не ищет(

JayBhagavan - указанный вами - некорректный, но вы подсказали еще один хороший критерий некорректности - нарастающие цифры от 1 до 9.
Критерий некорректности я указывал:
Сергей - шикарный вариант, только можете объяснить как работает оператор "--" ?

JayBhagavan, нет, это ИНН. Задача - отфильтровать явно некорректные, вбитые "наобум" инн.
Alexander88, не пойдет - могут быть и двойки, тройки и т.п.

IKKI, Сергей, с длиной строки то вопросов нет. Это все понятно как вычислить.
Вопрос именно по ячейкам, где повторяются одни и те же символы и которые забивали просто чтобы что-то поставить, но поставили некорректно, т.к. поле обязательное для заполнения. Их надо вычленять.

Уважаемые форумчане, не смог в короткий срок придумать полное и лаконичное решение, нужна подсказка. Поиск по форуму не дал результатов (надеюсь ничего не проглядел).

Есть столбец с номерами (должны быть 10 цифр длиной), некоторые из них забиты неверно, например "111111" или "1111" или "2222222", при этом нормальный номер выглдядит, например, как "7865890610".
Нужно формулой вычислить такие некорректные номера, и поставить для них признак исключить.
В большинстве своем некорректные номера - это несколько единиц подряд. Мое решение было в поиске через =НАЙТИ цифр от 2 до 9, и если ничего не встречается - выводить ошибку. Но, могут быть не только повторяющиеся единицы, но и двойки, тройки и т.п.
Поэтому перебирать все варианты так не выйдет.

Решение нужно формулой, макрос не могу использовать.

vikttur, красота какая спасибо!

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

Владимир, отличный вариант, один вопрос, не могу понять - каким образом формула понимает, что нужно брать итоговое значение для группы (из 13 строки), а не предыдущие, из строк 6-12?

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

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

Я пытался решить задачу путем впр + смещение диапазона поиска вниз, пока не дойду до строки, где номер магазина будет пустой, но не получается ничего нормального. Может кто-то знает как лучше написать формулу? Или данную задачу можно решить не путем смещения диапазона, а как-то иначе?

Владимир, спасибо большое! Заработало!
JayBhagavan, спасибо за и ваш вариант тоже.

Владимир, со столбцом значений работает, если делать по столбцу, записанному через формулу =ЕСЛИ(A2="";"";A2), то все равно появляется пустая ячейка =(

Nic70y, спасибо за ссылку, но приведенная там формула массива
почему-то не работает в случае, если массив данных записан не значениями, а через простую формулу
В техниках есть целая страница про эту же тему http://www.planetaexcel.ru/techniques/2/80/, но формула Николая
почему-то не работает совсем для моего диапазона.

Уважаемые форумчане!
Прошу вас подсказать, как решить задачу.

Есть столбец, заполненный фамилиями. Из него, формулой массива, вытягиваю уникальные значения фамилий. По получившемуся списку строю выпадающий список (через элементы управления).
Проблема - надо, чтобы в выпадающем списке фамилии шли отсортированно по алфавиту (по первой букве фамилии).
По-видимому, надо, формируя уникальные значения, сортировать их. Возможности отсортировать исходный столбец нет.
На форуме были схожие темы, и есть пример с сайта
http://sql.ru/forum/actualthread.aspx?bid=46&tid=508429&pg=2 (Автор файла KL(XL))
с формулой вида
но проблема в том, что таким образом полученный список содержит на первом месте пустую ячейку, а потом уже отсортированные данные.
Буду признателен, если кто-то сможет помочь усовершенствовать эту или предложить другую формулу, чтобы в отсортированном списке не было пустых ячеек и начинался он с первой фамилии.

Свой пример и пример с сайта прикрепил.

Vitaliispok, спасибо!
Но у меня не открывается файл по причине нечитаемого контента. Можете перезалить?

vikttur, ок.
Dima S, спасибо большое еще раз за макрос.

Придумал сам решение формулами через ДВСССЫЛ и подсчет количества непустых ячеек на каждом листе, с которого надо выводить данные, если кто-то поможет оптимизировать формулу - буду рад =)
=ЕСЛИ((ЕСЛИ(KZ_MASS_serv!A2="";ДВССЫЛ($I$1&"!A"&СТРОКА()-(СЧЁТЗ(KZ_MASS_serv!A:A)-1));KZ_MASS_serv!A2))=0;ДВССЫЛ($J$1&"!A"&СТРОКА()-(СЧЁТЗ(KZ_MASS_serv!A:A;KZ_PM_serv!A:A)-2));ЕСЛИ(KZ_MASS_serv!A2="";ДВССЫЛ($I$1&"!A"&СТРОКА()-(СЧЁТЗ(KZ_MASS_serv!A:A)-1));KZ_MASS_serv!A2))

JayBhagavan
Спасибо! Я так смотрю, код получился довольно сложный...
Не могу понять проихождение на листе параметры_исх значений в диапазоне А1-G4 (где зеленая заливка), можете подсказать как их получили?

NikitaV
Шапку можно сделать одинаковой, если это поможет.
Можно также расположить требуемые столбцы один за другим в конце листа.

Уважаемые форумчане!
В очередной раз вынужден задать вопрос и попросить помощи, т.к. сам решения не вижу, поиск по форуму и техники/приемы не помогли.

Есть данные на 3 листах. Набор столбцов на каждом листе разный, кол-во строк также разное, но на каждом листе, хоть и под разным именем-заголовком есть по 5 столбцов, со схожими  данными в них.
Задача - соединить на четвертом листе нужные пять столбцов с исходных листов один-под-другим, т.е. на четвертом листе должны быть пять столбцов, вставленные один под другим без пропуска строк.

Надеюсь, понятно объяснил. Пример прилагаю. Очень надеюсь на помощь!

китин, Спасибо =)
я "в качестве костылей" добавил просто 2 диапазона условий, равных первому, и все получилось - три диапазона условий, три диапазона усреднений.

Z, ок, буду искать варианты, спасибо за информацию.

Тему можно закрывать.

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

Пример во вложении. Буду признателей за помощь.

Dima S
Спасибо за вариант!
Я так понимаю, что итоговая оценка строится по принципу Чем меньше произведение показателей (Оценка магазина(баллы)), тем лучше?

Dima S
Тогда мой способ не подходит  
Но мне кажется что есть некая корреляция между расположением-позицией города и (видимо) номером заказа, который идет вначале...

Насчет макроса не подскажу. Если формулами, то зависит от того, какое кол-во типов таких срок в вашем файле. Т.е. в приложенном варианте я вижу 2:
1) 12090502MOW-забор груза от агента ГОРОД / 01120621MOW-возврат груза от агента ГОРОД (город после 4 пробела)
2) 12090695GOJ-доставка до ГОРОД / 12098300MOW-Россия, Краснодарский, ГОРОД (город после 2 пробела)
Если их всего 2 или немногим больше, то я бы искал пробелы и выводил бы справа название города. Если гораздо больше  - то такой вариант не подойдет.

% доля или же вес каждого критерия одинаковый.
К сводным вообще любовь, но в данном случае сводные увеличат вес файла + их каждый раз надо будет ручками обновлять, а мне бы этого не хотелось. Но я с этим уже разобрался, по ссылке был  пример того, как формулами массив сформировать, что я и сделал.

Я не понял в вашем варианте как вы в рез-те посчитали рейтинг (сортировка и цифры по порядку? если да, то, к сожалению, так не подойдет), формул нет)