подсчет рейтинга участников на новом листе со смещением столбцов

[coriace]

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

Есть таблица с данными: участники соревнования, баллы и время, набранные каждым участником по каждой из 8 недель соревнования.

Мне нужен рейтинг участников на отдельном листе в зависимости от баллов и времени. (Рейтинг отдельно по каждой неделе + итоговый за 2 месяца.) Чтобы можно было отсортировать по убыванию баллов и сразу видеть, кто в лидерах.

Задача осложняется тем, что исходные данные расположены на двух листах ("начало" и "продолжение") + столбцы получаются со смещением

Подозреваю, что тут нужна функция СМЕЩ, но я так ничего и не сваяла за полдня ковыряний. В итоге сделала рейтинг на первую неделю вручную. Но их еще семь, а потом еще 8 в перспективе... %(
Может быть есть все-таки способ автоматизировать процесс с помощью формул?

Файл прилагаю. Листы "Начало" и "Продолжение" - исходные данные. W1 - рейтинг по первый неделе, подтянутый вручную. Нужно заполнение остальных недель (листы W2-W8) + лист FinalScore (общие баллы и время за 8 недель)

Спасибо заранее!

[IKor]

Посмотрите прилагаемый документ.

Т.к. Вы уже потрудились заполнить левый столбец участников правильными именами, с помощью функции ПОИСКПОЗ() можно легко определить номера столбцов, в которых они присутствуют. А баллы и время для всех участников записываются в одной и той же строке (для каждой недели своей) - просто в соседних столбцах с найденным именем. Остается лишь для каждой недели _руками_ указать правильный номер строки - и можно копировать формулы с листа на лист.

Двухстраничная таблица потребовала несколько усложнить формулу, но ИМХО не критично.
Обратите внимание на именованные диапазоны (имена совпадают с названиями листов),которые я использовал для большей наглядности и упрощения формул.

[coriace]

IKor, спасибо большое, что откликнулись!
На моем 2003 excel возвращает ошибку, говорит, что какая-то часть формулы не работает в этой версии. Попробую на 2013, надеюсь, там все заведется!

[IKor]

Замените формулу ЕСЛИОШИБКА(одно;другое) на конструкцию ЕСЛИ(ЕОШИБКА(одно);другое;одно)
мне кажется, что это решит Вашу проблему

[coriace]

IKor, подправить я не в состоянии, к сожалению, не соображу, в каком месте, т.к. там несколько условий друг в друга вложены, но поскольку на ноутбуке с 2013 excel все работает, моя проблема решена ) Спасибо за сэкономленные часы механической работы!!

[coriace]

Уважаемые форумчане, подскажите еще раз, пожалуйста, по уже поднимавшемуся вопросу.
Задача та же, что в первом посте топика: подсчет рейтинга участников по параметрам "баллы" и "время" по каждой из недель соревнования (W1-W13). Рейтинг должен считаться между участниками из двух листов: ступень I и Ступень II.

IKor предложил решение, которое отлично работало, но теперь соревнование удлинилось и мне нужно не 8 недель, как в первоначальном файле, а 13, плюс итоговый рейтинг "final score". Я добавила листы W9-W13 и final score и скопировала туда формулы, проставив руками номера строк, из которых должны подтягиваться данные, но на новых листах баллы и время не отображаются. Файл прилагаю.

Коротко: нужно, чтобы на вкладках W9, W10, W11, W12, W13 и final score столбцы Баллы и Время заполнялись значениями из листов "Ступень I" и "Ступень II" так же, как это происходит на вкладках W1-W8.

Спасибо заранее.

[IKor]

2 coriace

Исходя из беглого анализа прикрепленного файла я могу порекомендовать Вам обратить внимание на три следующих момента:

1. Именованные диапазоны "Начало" и "Продолжение" должны перекрывать всю значимую область данных на первых двух листах. В закладке "Формулы" найдите кнопку "Диспетчер имен" и отредактируйте (или создайте заново под теми же именами) эти диапазоны.

2. Вы можете обратить внимание на то, что на первых листах-неделях формулы в столбцах C и D записаны в фигурных скобках - это признак формулы массива. Поэтому для их корректной работы необходимо [на каждом новом листе!]:
=>  перейти в режим редактирования ячейки C3 (ЛКМ в строке редактирования формул ИЛИ наступите на ячейку и нажмите клавишу F2) и одновременно нажмите три клавиши CTRL+SHFT+ENTER
=> повторите эти действия для ячейки D3
=> затем скопируйте ячейки C3 и D3 и вставьте [формулы] во все остальные значимые ячейки этих столбцов
==> повторите данные действия для всех остальных листов-недель

3. До тех пор пока Ваш список участников на листах-неделях не изменяется, нет особенной необходимости выполнять следующую рекомендацию. Тем не менее для чистоты эксперимента я рекомендую выполнить следующую операцию:
=> выделить все лист-недели (SHFT+ЛКМ по обоим граничным листам)
=> открыть окно "Найти и заменить" (CTRL+F)
=> и заменить текст "'W1'!$A" на "$A"
Данное действие должно переключить поиск участников на текущий лист (ранее номера строк находились на листе первой недели)

Кроме того, хочу сообщить следующее: формулы массива требуют выполнения большого числа вычислений - следовательно, Ваш документ будет обновляться все дольше по мере увеличения количества листов-недель. Поэтому я бы рекомендовал подумать об изменении структуры хранения исходных данных, что позволит упростить их обработку. А при необходимости имитировать сегодняшние исходные таблицы в виде отчетов (без возможности вносить данные непосредственно в них)

[coriace]

Спасибо вам большое!! Все заработало после выполнения первого шага!

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

А с третьим пунктом не совсем поняла. У меня уже изменился список участников по сравнению с прошлым соревнованием в двух исходных листах (часть осталась, часть новые фамилии) -  и все работает. Но я фамилии руками в листы-недели переписала.