Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/НаибольшийЕслиМн - без макроса - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » НаибольшийЕслиМн - без макроса (Формулы/Formulas)
НаибольшийЕслиМн - без макроса
DJ_Marker_MC Дата: Среда, 10.09.2014, 16:36 | Сообщение № 1
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Коллеги, добрый день, как можно получить НаибольшийЕслиМн - без udf.
В приложенном файле все расписал по столбцам. Второй час не могу подобрать то что нужно.
Мне нужно получить для каждого филиала 2 первых места, причем как первых так и вторых мест может быть несколько (в теории может, но на практике вряд ли).
Условием также является: места раздаются при пересечении планки в "Выполнении" больше-равно 50%

находил связки если(макс) массивная и найбольший(если) тоже массивная, но привязать в негромоздком виде к своим условиям не смог. Прошу Вашей помощи.
К сообщению приложен файл: marker.xlsx (9.4 Kb)
 
Ответить
СообщениеКоллеги, добрый день, как можно получить НаибольшийЕслиМн - без udf.
В приложенном файле все расписал по столбцам. Второй час не могу подобрать то что нужно.
Мне нужно получить для каждого филиала 2 первых места, причем как первых так и вторых мест может быть несколько (в теории может, но на практике вряд ли).
Условием также является: места раздаются при пересечении планки в "Выполнении" больше-равно 50%

находил связки если(макс) массивная и найбольший(если) тоже массивная, но привязать в негромоздком виде к своим условиям не смог. Прошу Вашей помощи.
Автор - DJ_Marker_MC
Дата добавления - 10.09.2014 в 16:36
Nic70y Дата: Среда, 10.09.2014, 16:45 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 8984
Репутация: 2359 ±
Замечаний: 0% ±

Excel 2010
вариант формулой массива:
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ(C2;НАИБОЛЬШИЙ(ЕСЛИ(A$2:A$18=A2;C$2:C$18);{1:2});0)&" -е место";"")
К сообщению приложен файл: 836-1.xlsx (11.2 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщениевариант формулой массива:
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ(C2;НАИБОЛЬШИЙ(ЕСЛИ(A$2:A$18=A2;C$2:C$18);{1:2});0)&" -е место";"")
Автор - Nic70y
Дата добавления - 10.09.2014 в 16:45
DJ_Marker_MC Дата: Среда, 10.09.2014, 16:49 | Сообщение № 3
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Nic70y, Спасибо, практически в точку))) НО
"места раздаются при пересечении планки в "Выполнении" больше-равно 50%"
 
Ответить
СообщениеNic70y, Спасибо, практически в точку))) НО
"места раздаются при пересечении планки в "Выполнении" больше-равно 50%"
Автор - DJ_Marker_MC
Дата добавления - 10.09.2014 в 16:49
krosav4ig Дата: Среда, 10.09.2014, 16:51 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Код
=ЕСЛИ(ИЛИ((НАИБОЛЬШИЙ(ДВССЫЛ("C"&ПОИСКПОЗ(A2;A:A;)&":C"&ПОИСКПОЗ(A2;A:A));{1;2})=C2)*(C2>=0,5));ЕСЛИ(СЧЁТЕСЛИ(ДВССЫЛ("C"&ПОИСКПОЗ(A2;A:A;)&":C"&ПОИСКПОЗ(A2;A:A));">"&C2);2;1)&"-е место";"")
К сообщению приложен файл: 1652725.xlsx (10.0 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Среда, 10.09.2014, 16:52
 
Ответить
Сообщение
Код
=ЕСЛИ(ИЛИ((НАИБОЛЬШИЙ(ДВССЫЛ("C"&ПОИСКПОЗ(A2;A:A;)&":C"&ПОИСКПОЗ(A2;A:A));{1;2})=C2)*(C2>=0,5));ЕСЛИ(СЧЁТЕСЛИ(ДВССЫЛ("C"&ПОИСКПОЗ(A2;A:A;)&":C"&ПОИСКПОЗ(A2;A:A));">"&C2);2;1)&"-е место";"")
Автор - krosav4ig
Дата добавления - 10.09.2014 в 16:51
Nic70y Дата: Среда, 10.09.2014, 16:53 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8984
Репутация: 2359 ±
Замечаний: 0% ±

Excel 2010
Цитата DJ_Marker_MC, 10.09.2014 в 16:49, в сообщении № 3
больше-равно 50%
это нормальное явление - т.к. я до конца ни когда не дочитываю :(
К сообщению приложен файл: 924.51.xlsx (11.2 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата DJ_Marker_MC, 10.09.2014 в 16:49, в сообщении № 3
больше-равно 50%
это нормальное явление - т.к. я до конца ни когда не дочитываю :(
Автор - Nic70y
Дата добавления - 10.09.2014 в 16:53
Rioran Дата: Среда, 10.09.2014, 16:54 | Сообщение № 6
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
DJ_Marker_MC, привет.

Моё решение такое, формула на удивление обычная.

Код
=ЕСЛИ(C2>0,5;РАНГ(C2;СМЕЩ($C$2;ПОИСКПОЗ(A2;$A$2:$A$18;0)-1;;СЧЁТЕСЛИ($A$2:$A$18;A2));0);"")
К сообщению приложен файл: X_Place.xlsx (9.7 Kb)

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Сообщение отредактировал Rioran - Среда, 10.09.2014, 16:55
 
Ответить
СообщениеDJ_Marker_MC, привет.

Моё решение такое, формула на удивление обычная.

Код
=ЕСЛИ(C2>0,5;РАНГ(C2;СМЕЩ($C$2;ПОИСКПОЗ(A2;$A$2:$A$18;0)-1;;СЧЁТЕСЛИ($A$2:$A$18;A2));0);"")
Автор - Rioran
Дата добавления - 10.09.2014 в 16:54
DJ_Marker_MC Дата: Среда, 10.09.2014, 16:55 | Сообщение № 7
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Чуток, опередили меня, я каплю по другому подправил:
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ(ЕСЛИ(C2>=0.5;C2;"");НАИБОЛЬШИЙ(ЕСЛИ(A$2:A$18=A2;C$2:C$18);{1:2});0)&" -е место";"")


Спасибо за помощь.
 
Ответить
СообщениеЧуток, опередили меня, я каплю по другому подправил:
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ(ЕСЛИ(C2>=0.5;C2;"");НАИБОЛЬШИЙ(ЕСЛИ(A$2:A$18=A2;C$2:C$18);{1:2});0)&" -е место";"")


Спасибо за помощь.
Автор - DJ_Marker_MC
Дата добавления - 10.09.2014 в 16:55
Nic70y Дата: Среда, 10.09.2014, 16:56 | Сообщение № 8
Группа: Друзья
Ранг: Экселист
Сообщений: 8984
Репутация: 2359 ±
Замечаний: 0% ±

Excel 2010
krosav4ig, Rioran, ничего, что я Вас немного поругаю? :)
нафига здесь летучие формулы?

ЮMoney 41001841029809
 
Ответить
Сообщениеkrosav4ig, Rioran, ничего, что я Вас немного поругаю? :)
нафига здесь летучие формулы?
Автор - Nic70y
Дата добавления - 10.09.2014 в 16:56
DJ_Marker_MC Дата: Среда, 10.09.2014, 16:58 | Сообщение № 9
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Rioran, Оу, думал что без массива никак, РАНГ никода в работе не применял, спасибо, почитаю, отсутствие массива привлекает больше чем его наличие.
 
Ответить
СообщениеRioran, Оу, думал что без массива никак, РАНГ никода в работе не применял, спасибо, почитаю, отсутствие массива привлекает больше чем его наличие.
Автор - DJ_Marker_MC
Дата добавления - 10.09.2014 в 16:58
DJ_Marker_MC Дата: Среда, 10.09.2014, 16:59 | Сообщение № 10
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Nic70y, а чем формула от Rioran плоха?


Сообщение отредактировал DJ_Marker_MC - Среда, 10.09.2014, 16:59
 
Ответить
СообщениеNic70y, а чем формула от Rioran плоха?
Автор - DJ_Marker_MC
Дата добавления - 10.09.2014 в 16:59
DJ_Marker_MC Дата: Среда, 10.09.2014, 17:02 | Сообщение № 11
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Rioran, увидел оплошность, ячейка B6 пустая, по ней считать не нужно.
 
Ответить
СообщениеRioran, увидел оплошность, ячейка B6 пустая, по ней считать не нужно.
Автор - DJ_Marker_MC
Дата добавления - 10.09.2014 в 17:02
Nic70y Дата: Среда, 10.09.2014, 17:03 | Сообщение № 12
Группа: Друзья
Ранг: Экселист
Сообщений: 8984
Репутация: 2359 ±
Замечаний: 0% ±

Excel 2010
Летучие формулы рекомендуются для использования, только когда без них ни как обойтись нельзя (лучше без них)
моя формула + Ваша поправка =
Код
=ЕСЛИ(C2<0,5;"";ЕСЛИОШИБКА(ПОИСКПОЗ(C2;НАИБОЛЬШИЙ(ЕСЛИ(A$2:A$18=A2;C$2:C$18);{1:2});0)&" -е место";""))

ЮMoney 41001841029809
 
Ответить
СообщениеЛетучие формулы рекомендуются для использования, только когда без них ни как обойтись нельзя (лучше без них)
моя формула + Ваша поправка =
Код
=ЕСЛИ(C2<0,5;"";ЕСЛИОШИБКА(ПОИСКПОЗ(C2;НАИБОЛЬШИЙ(ЕСЛИ(A$2:A$18=A2;C$2:C$18);{1:2});0)&" -е место";""))
Автор - Nic70y
Дата добавления - 10.09.2014 в 17:03
DJ_Marker_MC Дата: Среда, 10.09.2014, 17:08 | Сообщение № 13
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Nic70y, Все равно не так чуток))) пустого менеджера берет в расчет, если напротив пустого менеджера поставить 100% то он станет на первое место
 
Ответить
СообщениеNic70y, Все равно не так чуток))) пустого менеджера берет в расчет, если напротив пустого менеджера поставить 100% то он станет на первое место
Автор - DJ_Marker_MC
Дата добавления - 10.09.2014 в 17:08
Rioran Дата: Среда, 10.09.2014, 17:13 | Сообщение № 14
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
DJ_Marker_MC, да, про менеджера надо ещё доработать. Есть возможность сортировать таблицу так, чтобы пустые менеджеры были всегда последними в регионе?

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
СообщениеDJ_Marker_MC, да, про менеджера надо ещё доработать. Есть возможность сортировать таблицу так, чтобы пустые менеджеры были всегда последними в регионе?
Автор - Rioran
Дата добавления - 10.09.2014 в 17:13
Nic70y Дата: Среда, 10.09.2014, 17:14 | Сообщение № 15
Группа: Друзья
Ранг: Экселист
Сообщений: 8984
Репутация: 2359 ±
Замечаний: 0% ±

Excel 2010
Код
=ЕСЛИ(C2<0,5;"";ЕСЛИОШИБКА(ПОИСКПОЗ(C2;НАИБОЛЬШИЙ(ЕСЛИ(A$2:A$18=A2;ЕСЛИ(B$2:B$18<>"";C$2:C$18));{1:2});0)&" -е место";""))
К сообщению приложен файл: 1000.xlsx (11.2 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=ЕСЛИ(C2<0,5;"";ЕСЛИОШИБКА(ПОИСКПОЗ(C2;НАИБОЛЬШИЙ(ЕСЛИ(A$2:A$18=A2;ЕСЛИ(B$2:B$18<>"";C$2:C$18));{1:2});0)&" -е место";""))
Автор - Nic70y
Дата добавления - 10.09.2014 в 17:14
DJ_Marker_MC Дата: Среда, 10.09.2014, 17:15 | Сообщение № 16
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Rioran, если б была возможность сортировки я б сюда не писал)))
В реальную таблицу целиком и полностью подошел вариант от Nic70y с небольшой правкой
Код
=ЕСЛИ(ИЛИ(H5<0.5;D5="");"";ЕСЛИОШИБКА(ПОИСКПОЗ(H5;НАИБОЛЬШИЙ(ЕСЛИ(B$5:B$231=B5;H$5:H$231);{1:2});0)&" -е место";""))
 
Ответить
СообщениеRioran, если б была возможность сортировки я б сюда не писал)))
В реальную таблицу целиком и полностью подошел вариант от Nic70y с небольшой правкой
Код
=ЕСЛИ(ИЛИ(H5<0.5;D5="");"";ЕСЛИОШИБКА(ПОИСКПОЗ(H5;НАИБОЛЬШИЙ(ЕСЛИ(B$5:B$231=B5;H$5:H$231);{1:2});0)&" -е место";""))
Автор - DJ_Marker_MC
Дата добавления - 10.09.2014 в 17:15
Nic70y Дата: Среда, 10.09.2014, 17:17 | Сообщение № 17
Группа: Друзья
Ранг: Экселист
Сообщений: 8984
Репутация: 2359 ±
Замечаний: 0% ±

Excel 2010
Цитата DJ_Marker_MC, 10.09.2014 в 17:15, в сообщении № 16
ИЛИ(H5<0.5;D5="")
ну прет меня на массивы :D

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата DJ_Marker_MC, 10.09.2014 в 17:15, в сообщении № 16
ИЛИ(H5<0.5;D5="")
ну прет меня на массивы :D
Автор - Nic70y
Дата добавления - 10.09.2014 в 17:17
krosav4ig Дата: Четверг, 11.09.2014, 13:00 | Сообщение № 18
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
вот еще вариант ;)
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(B2>"";C2>0,5);СЧЁТЕСЛИМН(A:A;A2;C:C;">"&C2);"")+1&"-е место";"")


точнее
Код
=ЕСЛИОШИБКА(ВЫБОР(ЕСЛИ(И(B2>"";C2>0,5);СЧЁТЕСЛИМН(A:A;A2;C:C;">"&C2);"")+1;1;2)&"-е место";"")

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Четверг, 11.09.2014, 13:11
 
Ответить
Сообщениевот еще вариант ;)
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(B2>"";C2>0,5);СЧЁТЕСЛИМН(A:A;A2;C:C;">"&C2);"")+1&"-е место";"")


точнее
Код
=ЕСЛИОШИБКА(ВЫБОР(ЕСЛИ(И(B2>"";C2>0,5);СЧЁТЕСЛИМН(A:A;A2;C:C;">"&C2);"")+1;1;2)&"-е место";"")
Автор - krosav4ig
Дата добавления - 11.09.2014 в 13:00
DJ_Marker_MC Дата: Четверг, 11.09.2014, 13:37 | Сообщение № 19
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
krosav4ig, спасибо за решение. Это лучший вариант.
Подставил уже его себе в живую таблицу.
Количество знаков для итоговой формулы было 183, в Вашей 136, плюс отсутствие массива (а я их использую лишь в крайних случаях).
Снизило размер файла на 10кб.

Вот мой итог:
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(B5="ИТОГО";H5>=0.3);"100л.";ВЫБОР(ЕСЛИ(И(D5<>"";H5>0.5);СЧЁТЕСЛИМН(B:B;B5;H:H;">"&H5);"")+1;"Премия 1";"Премия 2"));"")


Сообщение отредактировал DJ_Marker_MC - Четверг, 11.09.2014, 13:41
 
Ответить
Сообщениеkrosav4ig, спасибо за решение. Это лучший вариант.
Подставил уже его себе в живую таблицу.
Количество знаков для итоговой формулы было 183, в Вашей 136, плюс отсутствие массива (а я их использую лишь в крайних случаях).
Снизило размер файла на 10кб.

Вот мой итог:
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(B5="ИТОГО";H5>=0.3);"100л.";ВЫБОР(ЕСЛИ(И(D5<>"";H5>0.5);СЧЁТЕСЛИМН(B:B;B5;H:H;">"&H5);"")+1;"Премия 1";"Премия 2"));"")
Автор - DJ_Marker_MC
Дата добавления - 11.09.2014 в 13:37
krosav4ig Дата: Четверг, 11.09.2014, 13:55 | Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
DJ_Marker_MC, всегда пожалуйста :)

имхо, можно еще немного укоротить
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(B5="ИТОГО";H5>=0.3);"100л.";"Премия "&ВЫБОР(ЕСЛИ(И(D5<>0;H5>0.5);СЧЁТЕСЛИМН(B:B;B5;H:H;">"&H5);"")+1;1;2));"")

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеDJ_Marker_MC, всегда пожалуйста :)

имхо, можно еще немного укоротить
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(B5="ИТОГО";H5>=0.3);"100л.";"Премия "&ВЫБОР(ЕСЛИ(И(D5<>0;H5>0.5);СЧЁТЕСЛИМН(B:B;B5;H:H;">"&H5);"")+1;1;2));"")
Автор - krosav4ig
Дата добавления - 11.09.2014 в 13:55
Мир MS Excel » Вопросы и решения » Вопросы по Excel » НаибольшийЕслиМн - без макроса (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!