[Львиное сердце]

Всем привет.
Помогите пожалуйста создать вот такую формулу:
В ячейке D36 нужно посчитать сумму чисел напротив числа "150", а в ячейке D37 нужно посчитать сумму напротив числа "200".
Я знаю что есть такая функция как "ВПР", но она сумму не считает.
Заранее спасибо.

[Маруся30]

RomaV- в виде ответа "да эти города есть в строке"
RrRR- мне нужно, чтобы формула оценивала ячейку А 1 на предмет наличия в ней всех возможных значений из столбца С.

[RomaV]

Т.е. на против каждой строки из столбца А на поставить "Да" или "Нет".

Попробуем...

[RrRR]

Сообщение от Маруся30 мне нужно, чтобы формула оценивала ячейку А 1 на предмет наличия в ней всех возможных значений из столбца С.

то есть будет два варианта результата (условно говоря):
"все города из столбца С есть в ячейке А1"
"не все города из столбца С есть в ячейке А1"
правильно?

[Маруся30]

RrRR, мне нужен результат даже если хотябы один город есть в ячейке А1

[RrRR]

В этом примере в среднем столбце отображается количество совпадений





Формула громоздкая, можно упростить немного, если задействовать промежуточные ячейки...

Вся проблема в том, что функция ПОИСК воспринимает отсутствие совпадений как ошибку, а не как ноль.
Соответственно с ошибкой нельзя производить арифметические действия.
То есть нельзя просто сложить функции ПОИСК по каждому аргументу, если будет хоть одно несовпадение, то результатом будет ошибка. Истина будет только в том случае если все аргументы перечислены в ячейке в первом столбце.
Тоже происходит и для логических функций.
Поэтому пришлось изгаляться через ЕСЛИ и ЕОШИБКА, чтобы несовпадениям присваивался ноль,а совпадениям единица.
Может есть более подходящая функция, но я ее так и не нашел

формулу для ячейки В2 можно скопировать

Код:

[Выделить все]

 

=ЕСЛИ(ЕОШИБКА(ПОИСК(C$2;A2));0;1)+ЕСЛИ(ЕОШИБКА(ПОИСК(C$3;A2));0;1)+ЕСЛИ(ЕОШИБКА(ПОИСК(C$4;A2));0;1)+ЕСЛИ(ЕОШИБКА(ПОИСК(C$5;A2));0;1)+ЕСЛИ(ЕОШИБКА(ПОИСК(C$6;A2));0;1)+ЕСЛИ(ЕОШИБКА(ПОИСК(C$7;A2));0;1)

[RomaV]

Ну вот я тоже попробовал.
Трудность в том что результатом проверки каждой строки является несколько значений (равное кол-ву городов в списке), а нужен один результат который получается в результате проверки этих нескольких значений. Вот что-бы хранить эти промежуточные значения я воспользовался вспомогательной таблицей. (Другой вариант, залезть в VBA и создать массив для хранения, но это муторно). В шапку вспомогательной таблици транспонируются города из списка, потом проверятся каждый город на наличие его в строке столбца "В" и ставится "0" если нет или цыфра позиции если есть. Потом цыферки тупо складываем в стобце "С" и если сумма "0" то пишем "Нет", иначе "Да".
Вспомогательнцю таблицу можно скрыть и вроде всё будет как надо. Список можно продлить, но тогда нужно и соответсвенно "продлить" вспомогательную таблицу (её вообще можно на другом листе сделать и очень большой)

[RrRR]

Offtop: Вот Марусе30 повезло, столько вариантов сразу

[RomaV]

Сообщение от RrRR Offtop: Вот Марусе30 повезло, столько вариантов сразу

Ну да, только оба варианта не сильно то и простые.

[xIrbis]

Помогите с формулой суммы ячеек. В ячейках A1:F1 имеются записи, как в числовом, так и в текстовом формате.
Есть два типа - в ячейке просто число, и число<несколько пробелов>другое число. Число пробелов не фиксировано.
Например, в A1 число 4, в B1 строка "6 2" (без кавычек), в C1 5,5 и т.д.
В G1 надо вывести сумму 4+6+2+5,5 (17,5). Другого текста, кроме пробелов и чисел, в ячейках нет. Всю голову сломал, получается какое-то дикое нагромождение формул, и работает нестабильно.

[gesper]

Сообщение от xIrbis Помогите с формулой суммы ячеек. В ячейках A1:F1 имеются записи, как в числовом, так и в текстовом формате. Есть два типа - в ячейке просто число, и число<несколько пробелов>другое число. Число пробелов не фиксировано. Например, в A1 число 4, в B1 строка "6 2" (без кавычек), в C1 5,5 и т.д. В G1 надо вывести сумму 4+6+2+5,5 (17,5). Другого текста, кроме пробелов и чисел, в ячейках нет. Всю голову сломал, получается какое-то дикое нагромождение формул, и работает нестабильно.

через VBA сделай

[xIrbis]

Спасибо за совет.
Но я обратился с конкретной проблемой - если бы я знал, какие инструменты применять, и способ применения, то вряд ли обратился бы за помощью, не так ли?

xIrbis, посмотри вложение, так?

Формулой в одной ячейке не получилось. Подробности на листе "Пояснение". Про то, откуда взялось слово "Вычисление" в ячейке, читать здесь.
Хотя, конечно, VBA был бы поизящнее.

[gesper]

Сообщение от xIrbis Спасибо за совет. Но я обратился с конкретной проблемой - если бы я знал, какие инструменты применять, и способ применения, то вряд ли обратился бы за помощью, не так ли?

Какие инструменты применять я тебе написал. Дальше нужно потратить время и посмотреть в поисковике как это работает. Я чужую работу за так не делаю, куда рыть и что смотреть могу подсказать, что я и сделал.

Первый закон Хартли: Нетрудно свести лошадь к воде. Но если вы заставите ее плавать на спине - вот это значит, что вы чего-то добились!

[xIrbis]

AlphaGeo, спасибо, работает.
в ячейке с формулой сделал текст одного цвета с фоном, чтоб она (формула) не смущала ничьих глаз.

gesper, ок, ты был услышан. Еще раз благодарю за потраченное время.

[Кулик Алексей aka kpblc]

Для ячейки B1 (если гарантируется, что чисел не больше 2) можно использовать формулу вида

Код:

[Выделить все]

=ЕСЛИ(ЕОШИБКА(НАЙТИ(" ";B1));B1;ЗНАЧЕН(ЛЕВСИМВ(B1;НАЙТИ(" ";B1)-1))+ЗНАЧЕН(ПРАВСИМВ(B1;ДЛСТР(B1)-НАЙТИ(" ";B1)+1)))

Соответственно просуммировать значения будет попроще. Проверял для разделителя целой и дробной частей - ",".

[KronSerg]

Сделал вариант решения на VBA. не забудь включить макросы.

[Сергей812]

Сообщение от xIrbis в ячейке с формулой сделал текст одного цвета с фоном, чтоб она (формула) не смущала ничьих глаз.

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

[DMSbrick]

Привет. Помогите разобраться.

В ячейку "f2" вводим число от 1 до 5

в ячейке "f5" должно показать соответствующее значение из столба "c" если не верно то "неверно".

[RomaV]

DMSbrick, для показа значения используй функцию ВПР, а для обработки неверного значения и показа слова "неверно" - функцию ЕСЛИОШИБКА.
Полная формула конкретно для твоего файла будет выглядеть вот так: =ЕСЛИОШИБКА(ВПР(F2;A1:C5;3;0);"неверно")

[Сергей812]

Пользуйтесь справкой офиса)
в ячейку F5

=ЕСЛИОШИБКА(ИНДЕКС(C1:C5;ПОИСКПОЗ(F2;C1:C5;0));"Неверно")

----- добавлено через ~5 мин. -----
а еще лучше диапазон ячеек задавать через имя (в данном случае - Список1) - тогда можно оперативно добавлять и убирать ячейки со значениями, не меняя формул

=ЕСЛИОШИБКА(ИНДЕКС(Список1;ПОИСКПОЗ(F2;Список1;0));"Неверно")