Присвоить значение по нескольким критериям

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Присвоить значение по нескольким критериям (Формулы)

AlexZm

Дата: Понедельник, 24.06.2013, 08:28 | Сообщение № 1

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

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

Сообщений: 2

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

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

Excel 2007

Здравствуйте!

Подскажите как решить следующию задачу.
Имеется 2 таблицы

Таблица 1
В столбце А находится текст с характеристикой местоположения (город, улица)
В столбце В числовое значение соответствующего города
В столбце С необходимо записать числовое значение улицы в соответствующем городе

Таблица 2
А-наименование улицы
В-значение города
С-значение улицы

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

Подскажите есть ли решение этой задачи с помощию формул EXEL

Спасибо за внимание! Зарание блогодарен!

К сообщению приложен файл: 0457459.xlsx (9.1 Kb)

Ответить

Pelena

Дата: Понедельник, 24.06.2013, 09:17 | Сообщение № 2

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

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

Сообщений: 19401

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

Замечаний: ±

Excel 365 & Mac Excel

Здравствуйте.
В файле два варианта. На первом листе формулой (на большом кол-ве строк может тормозить)

Код

=СУММПРОИЗВ((B3=$F$3:$F$14)*ЕЧИСЛО(ПОИСК($E$3:$E$14;A3))*$G$3:$G$14)

На втором листе с помощью доп. столбца более быстрый.
Доп. столбец получен с помощью ДАННЫЕ -- ТЕКСТ ПО СТОЛБЦАМ (все столбцы пропускаем, кроме последнего)

К сообщению приложен файл: 9325421.xlsx (11.5 Kb)

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

Ответить

AlexZm

Дата: Понедельник, 24.06.2013, 09:32 | Сообщение № 3

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

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

Сообщений: 2

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

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

Excel 2007

Огромное спасибо!
Работает!

Ответить

SergeyKorotun

Дата: Понедельник, 24.06.2013, 14:09 | Сообщение № 4

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

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

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

Excel 2007

А если в таблице 2 значения улиц начинаются не с 1, а с 0, то в первой таблице в значении улицы нулевое значение будет соответствовать и номеру улицы с номером 0 и отсутствию улицы во 2 таблице.

К сообщению приложен файл: 0457459_.xlsx (10.1 Kb)

Сообщение отредактировал SergeyKorotun - Понедельник, 24.06.2013, 14:26

Ответить

vikttur

Дата: Понедельник, 24.06.2013, 17:33 | Сообщение № 5

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

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

Сообщений: 2941

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

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

Второй файл еще не смотрел.
По первому:

Код

=ВПР(ПСТР(A3;ПОИСК("ул.";A3)+4;20);ИНДЕКС($E$3:$E$14;ПОИСКПОЗ(B3;$F$3:$F$14;)):$G$14;3;)

Ответить

SergeyKorotun

Дата: Понедельник, 24.06.2013, 17:47 | Сообщение № 6

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

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

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

Excel 2007

Цитата (vikttur)

=ВПР(ПСТР(A3;ПОИСК("ул.";A3)+4;20);ИНДЕКС($E$3:$E$14;ПОИСКПОЗ(B3;$F$3:$F$14;)):$G$14;3;)

Если в справочнике нет улицы для данного города, ищет улицу в другом городе

К сообщению приложен файл: 9325421-2.xlsx (11.7 Kb)

Ответить

Pelena

Дата: Понедельник, 24.06.2013, 19:55 | Сообщение № 7

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

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

Сообщений: 19401

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

Замечаний: ±

Excel 365 & Mac Excel

Цитата (SergeyKorotun)

если в таблице 2 значения улиц начинаются не с 1, а с 0

Как-то странно, номер улицы и вдруг с нуля?.. Маловероятно

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

Ответить

vikttur

Дата: Понедельник, 24.06.2013, 19:56 | Сообщение № 8

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

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

Сообщений: 2941

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

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

И это правильно! smile

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

Код

ИНДЕКС($E$3:$E$14;ПОИСКПОЗ(B3;$F$3:$F$14;)+СЧЁТЕСЛИ($F$3:$F$14;B3)-1)

При отсутствии улицы выдаст #Н/Д

Елена, это не номер улицы - какое-то секретное значение. А оно может и с буквы или пробела начинаться smile