Новости:

Прикрепить к сообщению можно только файлы xls, gif, jpg, rar, zip,7z, bas, frm, cls, doc размером до 150 Кб.

Главное меню

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

Автор coriace, 28.06.2017, 18:17

« назад - далее »

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

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

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

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