Ранжирование списка по условию

Дата: Понедельник, 27.06.2016, 13:16 | Сообщение № 1

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

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

Сообщений: 279

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

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

2007, 2010, 2013

Уважаемые, подскажите пожалуйста решение:
Требуется отранжировать список по по значению в столбце "В" отвечающие условиям в столбце "А". В столбце "С" определен ранг во всем списке, важно что бы места распределялись последовательно без пропуска как делает функция

Код

=Ранг()

поэтому была применена такая формула

Код

=СУММПРОИЗВ(--((ПОИСКПОЗ([Данные];[Данные];)=СТРОКА([Данные])-1)*[Данные]>=[@Данные]))

. Откуда я раскопал эту формулу уже не помню, вытащил из своего архива интересных решений, и поэтому объяснить самому себе как она работает не могу, чтобы её докрутить под себя.
подскажите пожалуйста путь истинный.

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

Nic70y

Дата: Понедельник, 27.06.2016, 13:33 | Сообщение № 2

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

Ранг: Экселист

Сообщений: 8759

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

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

Excel 2010

вариант, формула массива:

Код

=ПОИСКПОЗ([@Данные];НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(ЕСЛИ(ПОИСКПОЗ([Данные];ЕСЛИ([@Условие]=[Условие];[Данные]);)=СТРОКА([Условие])-1;[Данные]));[Данные]);СТРОКА([Условие])-1);)

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

ЮMoney 41001841029809

Дата: Понедельник, 27.06.2016, 13:51 | Сообщение № 3

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

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

Сообщений: 16475

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

Замечаний: ±

Excel 2016

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

А не проще будет использовать сводную таблицу (см. вложение)?

К сообщению приложен файл: 20160627_Sancho.xls (53.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390

Дата: Понедельник, 27.06.2016, 15:26 | Сообщение № 4

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

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

Сообщений: 279

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

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

2007, 2010, 2013

Nic70y, Спасибо, формула работает. Может быть еще покороче будут варианты?
Serge_007, дело в том таблица должна быть динамической в неё вставляются данные, количество строк всегда разные. я же готовлю шаблон с одной строкой забитую формулами. Но как вариант возьму на заметку, как то пробовал, но функционал сводной таблицы мне не совсем понравился (может быть плохо еще пробовал). в вашем же примере на фильтр нажал и все сломалось, боюсь возникнут проблемы у коллег при работе со сводными таблицами т.к. фильтрацией таблиц очень часто будем пользоваться.

Дата: Понедельник, 27.06.2016, 15:37 | Сообщение № 5

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

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

Сообщений: 16475

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

Замечаний: ±

Excel 2016

Цитата Sancho, 27.06.2016 в 15:26, в сообщении № 4

таблица должна быть динамической в неё вставляются данные, количество строк всегда разные

Это не играет роли

Цитата Sancho, 27.06.2016 в 15:26, в сообщении № 4

функционал сводной таблицы мне не совсем понравился (может быть плохо еще пробовал). в вашем же примере на фильтр нажал и все сломалось

Нет примера конкретики что "сломалось". "Сломаться" ничего не может, Вы что-то сделали неправильно

Цитата Sancho, 27.06.2016 в 15:26, в сообщении № 4

фильтрацией таблиц очень часто будем пользоваться

Сколько угодно

ЮMoney:41001419691823 | WMR:126292472390

Дата: Понедельник, 27.06.2016, 15:57 | Сообщение № 6

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

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

Сообщений: 279

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

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

2007, 2010, 2013

Цитата Serge_007, 27.06.2016 в 15:37, в сообщении № 5

Нет примера конкретики что "сломалось". "Сломаться" ничего не может, Вы что-то сделали неправильно

Вот пример, просто перестроил сортировку от Я до А

К сообщению приложен файл: 8918145.xls (43.5 Kb)

Дата: Понедельник, 27.06.2016, 16:06 | Сообщение № 7

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

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

Сообщений: 16475

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

Замечаний: ±

Excel 2016

Цитата Sancho, 27.06.2016 в 15:57, в сообщении № 6

перестроил сортировку от Я до А

Это не соответствует условиям задачи. Зачем Вы это сделали?

ЮMoney:41001419691823 | WMR:126292472390

Дата: Понедельник, 27.06.2016, 16:13 | Сообщение № 8

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

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

Сообщений: 279

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

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

2007, 2010, 2013

Цитата Serge_007

Это не соответствует условиям задачи.

Ну как не соответствует?) Изначально условие было предоставлено в "умной таблице" следовательно и решение хотелось бы получить внутри этой таблицы, которую можно вооротить как хочешь и сколько угодно, хочешь от "Я" сортируй, хочешь от "А", хочешь фильтруй. нужно, что бы вставил данные в шаблон таблицы и все что нужно рассчиталось внутри её или на других листах без дополнительного инструктирования коллег и их криворуких действий.

pabchek

Дата: Понедельник, 27.06.2016, 16:53 | Сообщение № 9

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

Ранг: Ветеран

Сообщений: 931

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

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

Excel 2007

Цитата Sancho, 27.06.2016 в 15:26, в сообщении № 4

еще покороче

Сократил немного формулу Николая

Код

=ПОИСКПОЗ(Таблица3[[@Эта строка];[Данные]];НАИБОЛЬШИЙ((Таблица3[[@Эта строка];[Условие]]=[Условие])*[Данные];СТРОКА([Условие])-1);-1)

[p.s.]не понимаю. пока пишу, формула выглядит корректно. А когда сохраняю сообщение, выскакивает Undefined/
Так или иначе, в файле все норм
Добавлено
Заменил # На @ как предыдущий оратор - все получилось. Не знал этой особенности

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

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Понедельник, 27.06.2016, 17:04