Замена Н/Д в формуле + ВПР по частичному совпадению

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

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

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Замена Н/Д в формуле + ВПР по частичному совпадению (Формулы/Formulas)

Nikonka89

Дата: Вторник, 13.10.2020, 15:01 | Сообщение № 1

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

Ранг: Участник

Сообщений: 56

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

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

Excel 2019

Добрый день.
Очень нужна ваша помощь.
Пример во вложении: считаем табель. Если сумма в день больше 10000 - то ставим коэф. 1, если сумма меньше - то 0,5. Саму формулу написала для такого условия (два разных варианта, не знаю какая будет удобнее).

1. По Смирновой нужно , чтобы Н/Д заменялось на "0". Знаю , что через функцию ЕНД, но как ее применить в моей формуле никак не могу сообразить.
2. Петрова Ольга. Аналогичные данные, нужно чтобы Н/Д заменялось на "0". Но тут ФИО совпадает частично, нужно еще чтобы данные подтягивались по имени "Ольга". Как это все объединить в одной формуле тоже не понимаю.

Помогите, пожалуйста

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

Ответить

Che79

Дата: Вторник, 13.10.2020, 15:47 | Сообщение № 2

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

Ранг: Старожил

Сообщений: 1649

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

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

2013 Win, 365 Mac

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

Код

=ЕСЛИОШИБКА(ЕСЛИ(ПРОСМОТР(;-1/(СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ($A4;" ";ПОВТОР(" ";99));99))=Лист1!$B3:$B500)/(C3=Лист1!$A3:$A500);Лист1!$C3:$C500)<10000;0,5;1);)

Смирнова Анна

Код

=ЕСЛИОШИБКА(ЕСЛИ(ПРОСМОТР(;-1/($A5=Лист1!$B3:$B500)/(C3=Лист1!$A3:$A500);Лист1!$C3:$C500)<10000;0,5;1);)

Делай нормально и будет нормально!

Ответить

Nikonka89

Дата: Вторник, 13.10.2020, 16:17 | Сообщение № 3

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

Ранг: Участник

Сообщений: 56

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

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

Excel 2019

Che79, супер! все работает . Спасибо большое.

Еще вопрос возник. Получается, что <10000 формула считает и нулевые продажи.

4.10.2020 Смирнова Анна продажи были 0, но так как это меньше 10000, то коэф.посчитался как 0,5

Как тогда правильно написать в условии, что-то типа от 1 до 10000 = 0.5 , от 10000 и выше = 1 ?

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

Ответить

Che79

Дата: Вторник, 13.10.2020, 16:36 | Сообщение № 4

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

Ранг: Старожил

Сообщений: 1649

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

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

2013 Win, 365 Mac

Цитата Nikonka89, 13.10.2020 в 16:17, в сообщении № 3 ()

от 1 до 10000 = 0.5 , от 10000 и выше = 1

Первое, что приходит в голову, это ВПР(). Попробуйте на реальном файле. Для Петровой

Код

=ЕСЛИОШИБКА(ВПР(ПРОСМОТР(;-1/(СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ($A4;" ";ПОВТОР(" ";99));99))=Лист1!$B3:$B500)/(C3=Лист1!$A3:$A500);Лист1!$C3:$C500);{1;0,5:10000;1};2);)

Делай нормально и будет нормально!

Сообщение отредактировал Che79 - Вторник, 13.10.2020, 17:15

Ответить

-vp-

Дата: Вторник, 13.10.2020, 17:18 | Сообщение № 5

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

Ранг: Участник

Сообщений: 64

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

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

Excel 2019

Еще вариант, для всего коллектива:

Код

=ЕСЛИОШИБКА(ЕСЛИ(ИНДЕКС(Лист1!$C$3:$C$13;ПОИСКПОЗ(C$3&ЕСЛИ($A4="Петрова Ольга";ЗАМЕНИТЬ($A4;1;НАЙТИ(" ";$A4;1);"");$A4);ЕСЛИ(Лист1!$C$3:$C$13>0;Лист1!$A$3:$A$13&Лист1!$B$3:$B$13;"");0))<10000;0,5;1);)

Ответить

Nikonka89

Дата: Вторник, 13.10.2020, 20:36 | Сообщение № 6

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

Ранг: Участник

Сообщений: 56

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

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

Excel 2019

Работает! ура) Спасибо, сама бы ни за что не додумалась, сложные формулы)

Ответить

Страница 1 из 1
1