Простая, совсем простая формула

[Niefer]

С новым годом!

И вот такая новогодная задача, вроде простая, но я запутался - таблица состоит всего из 3-х столбцов и 2 из них заполняются вручную. Записей много, тысячи и каждый день добавляется сотня-другая. Мне нужна всего одна простая (желательно) формула в 3-ем столбце. Таблица примерно такая:

 1  Иванов    8  1
 2  Петров    9  1
 3  Сидоров   3  1
 4  Путин     1  1
 5  Медведев  3  1
 6  Петров    7  2
 7  Иванов    5  2
 8  Путин     6  2
 9  Сидоров   1  2
10  Петров    8  3
11  Петров   -5  4
12  Иванов    7  3
13  Медведев  3  2
14  Петров    7  1
15  Сидоров   3  3
16  Иванов   -4  4
17  Путин     2  3
18  Путин    -5  4
19  Медведев  4  3
20  Иванов    7  1
21  Иванов    6  2
22  Путин     4  1
23  Сидоров   3  4
24  Петров    2  1

3-й столбец счетчик, но не простой. Он считает фамилии в первом столбце следующим образом: при первом появлении фамилии в списке ему присваивается 1, при последующем - 2, потом 3 и т.д., однако при появлении рядом с фамилией отрицательного числа, отсчет начинается заново.

Например, Петров впервые появляется на второй строке, рядом с ним стоит число 9, и ему присваивается единица в третьем столбце.
2-е появление Петрова на 6-й строке, рядом с ним 7, присваивается 2.
3-е - на 10-й строке, рядом с ним 8, присваивается 3.
4-е - на 11-й строке, рядом с ним -5, присваивается 4.
5-е - на 14-й строке, рядом с ним 7, и ему присваивается 1, потому что отчет начинается заново так как при предидущем (4-м) появлении рядом с ним стояло -5 (отрицательное число).

Это все. Надеюсь на красивое, простое решение.

[Скрипт]

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

[MCH]

Вариант решения формулами в желтых ячейках, не уверен, что будет быстро считать если

Записей много, тысячи и каждый день добавляется сотня-другая

Niefer, у Вас в последней строчке у Петрова должно быть 2, а не 1

PS: Что то мне это напоминает биржу, или какуюто другую азартную игру

[IgorGO]

у меня две формулы:
простая:

=ЕСЛИ(мр=0;1;ЕСЛИ(ИНДЕКС(C2;мр)<0;1;ИНДЕКС(C3;мр)+1)

и не очень:

=ЕСЛИ(МАКС((R1C1:R[-1]C1=RC1)*СТРОКА(R1C1:R[-1]C1))=0;1;ЕСЛИ(ИНДЕКС(C2;МАКС((R1C1:R[-1]C1=RC1)*СТРОКА(R1C1:R[-1]C1)))<0;1;ИНДЕКС(C3;МАКС((R1C1:R[-1]C1=RC1)*СТРОКА(R1C1:R[-1]C1)))+1))

мр в простой формуле это вот этот фрагмент

МАКС((R1C1:R[-1]C1=RC1)*СТРОКА(R1C1:R[-1]C1))

из второй формулы
вторая формула - это формула массива.

[IgorGO]

Уважаемый Niefer,
а не затруднит-ли Вас определить какой из вариантов оказался наиболее производительным на тысячах строк?

[Niefer]

@Скрипт
Нет, макросы исключаются.

@MCH
1. Вы правы - действительно, должно быть 2, хотя надеюсь, эта опечатка никак не исказила идею.
2. Вы правы второй раз - задача связана с азартной игрой.
3. Ваша формула работает, спасибо. Однако проверить ее быстродействие (для приблизительно 50 тысяч записей) я смогу не раньше завтрашнего дня.

@IgorGO
Ваши формулы работают тоже, спасибо за внимание. И хотя они мне кажутся несколько громоздкими, это для меня все таки вопрос не красоты, а практической пригодности.

В общем, выбор формулы определится после практических тестов и я об этом обязательно сообщу завтра. Еще раз спасибо всем.

[MCH]

Сообщение от Niefer

Нет, макросы исключаются.

А это зря, если строк 50 тысяч, то любая формула построенная на массивах будет тормозить (здесь либо делать альтернативные варианты с использованием доп.столбцов и более быстрых функций - СЧЕТЕСЛИ/СУММЕСЛИ, либо макросы на массивах)

Протестировал на 2000 строчках, формулы пересчитываются поряда 2 секунд, при этом макрос работает в десятки (если не в сотни) раз быстрее, так на 50 тысяч строк макрос работает менее секунды

Запустил формульное решение на 10000 строк - не дождался пересчета формул

--------
UPD:
В макросе была ошибка - исправил, файл перезалил
Добавил формулу с доп. столбцом (Тест 3), у меня работает в 4 раза быстрее массивных формул, так что данную формулу теоретически можно использовать для 5000-10000 срок данных

[Niefer]

Итак, с учетом всех за и против (макросы, формулы массива, дополнителные столбцы) и с учетом моей конкретной цели, самым подходящим оказался вариант MCH с доп.столбцом и с SUMIF (из файла Niefer3.rar). Правда мне пришлось его немного изменить, так как в действительности моя задача несколько сложней, чем вышеописанная схема в моем первом посте.

Что касается быстродействия - для 50 тысяч записей все варианты IgorGO и MCH занимают по 10 минут и больше (мне не хватало терпения дождаться окончания пересчета). А вот варианту "Test4" от MCH потребовалось всего пол-секунды, но к сожалению макросы исключаются.

В принципе я получил то, что хотел. Спасибо.