Помогите настроить формулу подсчета места (столбец AS) в таблице, должно считать сначала по очкам(AR) - затем если у двух и более команд одинаковое количество очков по разнице сетов(AR), далее по разнице очков сетах(AJ). Перерыл весь форум такого не нашел, пробовал подогнать другие формулы ничего пока не выходит. по ссылке второй лист: https://docs.google.com/spreads....sharing
Помогите настроить формулу подсчета места (столбец AS) в таблице, должно считать сначала по очкам(AR) - затем если у двух и более команд одинаковое количество очков по разнице сетов(AR), далее по разнице очков сетах(AJ). Перерыл весь форум такого не нашел, пробовал подогнать другие формулы ничего пока не выходит. по ссылке второй лист: https://docs.google.com/spreads....sharingOka08
Сообщение отредактировал Oka08 - Пятница, 02.02.2018, 15:47
В той моей теме, ссылку на которую дала Елена, в сообщении N 3 исчерпывающе изложено как построить подобную формулу, особенно во фрагментах со степенями 100 и 20. [p.s.]ну, видимо, пока сам не покажу, никто не покажет (тэг vba вместо формульного применяю сознательно - для лучшего форматирования в целях наглядности)[/p.s.] Итак, в соответствии с моим подходом могу предложить следующие формулы для Вашего листа "таблица".
В ячейку AV2 - "Суммарный ранг" по трём показателям (очкам, разницам сетов, разницам очков в партиях): [vba]
[/vba]Понятно, что это просто некий искусственно созданный показатель, ранжирование по которому, тем не менее, обеспечит правильное определение мест команд.
После ввода в ячейку AV2 протягиваем формулу на все команды в диапазоне AV2:AV11 и далее вводим формулу определения мест в ячейку AW2 (и также протягиваем в AW2:AW11): [vba]
Код
=RANK(AV2;$AV$2:$AV$11)
[/vba]
Формула для "Суммарного ранга", приведенная выше, не обеспечивает полной однозначности места при совпадении всех трёх составляющих показателей. Особенно часто это возможно на старте турнира, когда некоторые команды еще просто ни разу не сыграли и имею нули по всем показателям. При этом, чисто математически, они не находятся на последнем месте, а занимают некоторое общее место в середине таблице, оставляя места ниже командам, которые уже успели сыграть и проиграть.
Для обеспечения полной однозначности мест в таблице можно прибегнуть к двум простым приёмам. 1. Добавление случайного числа в конец "Суммарного ранга" (в два младших разряда): [vba]
[/vba] 2. Добавление номера строки таблицы в конец "Суммарного ранга" (в два младших разряда) - в этом случае гарантируется абсолютная однозначность: [vba]
[/vba] Поскольку номер строки вычитается из 100, то "преимуществом" будут обладать команды, располагающиеся выше в исходной таблице. Что может быть логичным, например, в связи с тем, что исходная таблица для записи результатов могла быть сформирована по результатам прошлого розыгрыша, когда прошлый чемпион располагается на первой строке.
В той моей теме, ссылку на которую дала Елена, в сообщении N 3 исчерпывающе изложено как построить подобную формулу, особенно во фрагментах со степенями 100 и 20. [p.s.]ну, видимо, пока сам не покажу, никто не покажет (тэг vba вместо формульного применяю сознательно - для лучшего форматирования в целях наглядности)[/p.s.] Итак, в соответствии с моим подходом могу предложить следующие формулы для Вашего листа "таблица".
В ячейку AV2 - "Суммарный ранг" по трём показателям (очкам, разницам сетов, разницам очков в партиях): [vba]
[/vba]Понятно, что это просто некий искусственно созданный показатель, ранжирование по которому, тем не менее, обеспечит правильное определение мест команд.
После ввода в ячейку AV2 протягиваем формулу на все команды в диапазоне AV2:AV11 и далее вводим формулу определения мест в ячейку AW2 (и также протягиваем в AW2:AW11): [vba]
Код
=RANK(AV2;$AV$2:$AV$11)
[/vba]
Формула для "Суммарного ранга", приведенная выше, не обеспечивает полной однозначности места при совпадении всех трёх составляющих показателей. Особенно часто это возможно на старте турнира, когда некоторые команды еще просто ни разу не сыграли и имею нули по всем показателям. При этом, чисто математически, они не находятся на последнем месте, а занимают некоторое общее место в середине таблице, оставляя места ниже командам, которые уже успели сыграть и проиграть.
Для обеспечения полной однозначности мест в таблице можно прибегнуть к двум простым приёмам. 1. Добавление случайного числа в конец "Суммарного ранга" (в два младших разряда): [vba]
[/vba] 2. Добавление номера строки таблицы в конец "Суммарного ранга" (в два младших разряда) - в этом случае гарантируется абсолютная однозначность: [vba]
[/vba] Поскольку номер строки вычитается из 100, то "преимуществом" будут обладать команды, располагающиеся выше в исходной таблице. Что может быть логичным, например, в связи с тем, что исходная таблица для записи результатов могла быть сформирована по результатам прошлого розыгрыша, когда прошлый чемпион располагается на первой строке.Gustav
еще вопрос по той же таблице, как привязать значение ячейки на одном листе к значению ячейки в другом листе, чтобы при сортировке другого листа значения не терялись. Сейчас к примеру у меня на листе "Таблица" значения ячеек $C2$AF11 берутся через "=" с листа "игры" и если на листе игры сделать сортировку строк, то значения в листе "Таблица" сбиваются???? Просто туры расписывают организаторы и я так понял они это начали делать от балды, теперь вот под них подстраиваю таблицу.
еще вопрос по той же таблице, как привязать значение ячейки на одном листе к значению ячейки в другом листе, чтобы при сортировке другого листа значения не терялись. Сейчас к примеру у меня на листе "Таблица" значения ячеек $C2$AF11 берутся через "=" с листа "игры" и если на листе игры сделать сортировку строк, то значения в листе "Таблица" сбиваются???? Просто туры расписывают организаторы и я так понял они это начали делать от балды, теперь вот под них подстраиваю таблицу.Oka08
Сообщение отредактировал Oka08 - Среда, 07.02.2018, 12:03
Oka08, так Вы бы доступ к своей таблице не закрывали - глядишь, кто-то бы и помог уже. А так всё очень в уме надо представлять, не все готовы... Но даже в такую "слепую" рискну предположить, что Вам, скорее всего, нужно сочетание функций ИНДЕКС и ПОИСКПОЗ.
Oka08, так Вы бы доступ к своей таблице не закрывали - глядишь, кто-то бы и помог уже. А так всё очень в уме надо представлять, не все готовы... Но даже в такую "слепую" рискну предположить, что Вам, скорее всего, нужно сочетание функций ИНДЕКС и ПОИСКПОЗ.Gustav
[/vba] Далее совместно выделяете эти три ячейки, копируете их и вставляете в диапазон C2:AF11, после чего восстанавливаете синюю диагональ, стирая формулы в её ячейках. -99 - это произвольное заведомо минимальное число для участия в сравнениях по MAX, при желании можно самостоятельно "уменьшить", скажем, до -9 или даже -1 (но не 0 - по понятной причине).
[p.s.]Зарядил формулы в Вашу таблицу по ссылке[/p.s.]
P.P.S. Протягиваемые формулы для колонок "В 3","В 2","П 1","П" (отличаются только условиями):
[/vba] Формула для колонки "И" (игры) при этом становится совершенно банальной суммой:
[vba]
Код
Ячейка AG2: =SUM(AK2:AN2)
[/vba]
Формулы для колонок "С" (сеты выигранные и проигранные) тоже можно заменить на единообразные протягиваемые суммы, если (и это ключевой момент для всех формул) также НЕ исключать пустые ячейки диагонали:
[vba]
Код
Ячейка AO2: =SUM(C2;F2;I2;L2;O2;R2;U2;X2;AA2;AD2)
Ячейка AQ2: =SUM(E2;H2;K2;N2;Q2;T2;W2;Z2;AC2;AF2)
[/vba]
И, наконец, для суммарных очков в сетах "Ов" и "Оп" (выигранные и проигранные):
[/vba] Далее совместно выделяете эти три ячейки, копируете их и вставляете в диапазон C2:AF11, после чего восстанавливаете синюю диагональ, стирая формулы в её ячейках. -99 - это произвольное заведомо минимальное число для участия в сравнениях по MAX, при желании можно самостоятельно "уменьшить", скажем, до -9 или даже -1 (но не 0 - по понятной причине).
[p.s.]Зарядил формулы в Вашу таблицу по ссылке[/p.s.]
P.P.S. Протягиваемые формулы для колонок "В 3","В 2","П 1","П" (отличаются только условиями):
[/vba] Формула для колонки "И" (игры) при этом становится совершенно банальной суммой:
[vba]
Код
Ячейка AG2: =SUM(AK2:AN2)
[/vba]
Формулы для колонок "С" (сеты выигранные и проигранные) тоже можно заменить на единообразные протягиваемые суммы, если (и это ключевой момент для всех формул) также НЕ исключать пустые ячейки диагонали:
[vba]
Код
Ячейка AO2: =SUM(C2;F2;I2;L2;O2;R2;U2;X2;AA2;AD2)
Ячейка AQ2: =SUM(E2;H2;K2;N2;Q2;T2;W2;Z2;AC2;AF2)
[/vba]
И, наконец, для суммарных очков в сетах "Ов" и "Оп" (выигранные и проигранные):
Это ссылка на мою копию таблицы. В учебных целях я убрал всю "конфиденциальную" информацию (типа фамилий игроков, судей, мест проведения). Лист "положение" без труда можно скопировать в первоначальную оригинальную таблицу.
Это ссылка на мою копию таблицы. В учебных целях я убрал всю "конфиденциальную" информацию (типа фамилий игроков, судей, мест проведения). Лист "положение" без труда можно скопировать в первоначальную оригинальную таблицу.Gustav
Хочу поделиться новым опытом. Помогаю одному из участников нашего Форума построить удобную турнирную таблицу. За основу взяли таблицу этого топика. В процессе работы удалось применить новомодные функции таблиц Гугл, введенные совсем недавно в августе 2022. Это функции LAMBDA, MAP, SCAN, REDUCE, BYROW, BYCOL, MAKEARRAY, а также появившаяся возможность создания именованных пользовательских функций, составленных из других функций (не путать с функциями UDF, которые пишутся как скрипты).
Microsoft в некоторых особо продвинутых версиях Excel (по подписке, типа 365 или Web) ввел эти функции уже достаточно давно. Но в том-то и дело, что при проводимой им в последние годы политике, совершенно непонятно, когда пользователь конкретной версии получит их в своё активное распоряжение - ведь далеко не все имеют возможность пользования подписочной версией. Поэтому и получается, что теоретически оно, вроде, и есть, но не всегда "про нашу честь".
Google - другое дело: уж коли добавили, то всем и навсегда и, соответственно, новшествами можно начинать активно пользоваться. С нетерпением жду добавления Гуглом функции LET, хотя, как показала первая практика, и с LAMBDA уже можно творить довольно интересно, на новом уровне.
В общем, создал я две именованные функции (доступны в файле по ссылке выше, по команде меню "Данные \ Именованные функции"): * ZCOUNT_GAME_RESULTS(range; condition) - Подсчет исходов матчей, удовлетворяющих заданному условию * ZTOURN_CROSSTAB_CELL(teama; teambidx; rngteams; rngteamab; rngpointsab) - Вычисление содержимого ячейки турнирной таблицы ("шахматки") - счёт матча с активным разделителем (массив из 3-х ячеек)
Функции применяются на новом добавленном листе "таблица (именованные формулы)". Это копия листа "таблицы", но с новыми функциями.
Фукция ZCOUNT_GAME_RESULTS применяется в четырех колонках AK:AN ("В3","В2","П1","П"). Например, вызов в ячейке AK2 выглядит так: [vba]
Код
=ZCOUNT_GAME_RESULTS($C2:$AF2;">1")
[/vba] Для сравнения - на старом листе "таблицы" в аналогичной ячейке формула была такой: [vba]
[/vba] Налицо прогресс уже хотя бы в том, что вместо утомительного перечисления ячеек по отдельности (потому что не подряд идут, а с шагом) теперь указывается просто сплошной диапазон $C2:$AF2, а уж нужные ячейки выбираются "под капотом" функции с помощью "зубодробительных" конструкций: [vba]
[/vba] Особый кайф - в использовании внутри формулы имен параметров - range и condition, а не ссылок на ячейки. Очень удобно при разработке.
Вторая моя функция ZTOURN_CROSSTAB_CELL применяется на том же листе "таблица (именованные формулы)" в каждой третьей ячейке диапазона C2:AF11. Вызов в ячейке F2: [vba]
[/vba] Формула хорошо протягивается по всему диапазону C2:AF11. При копировании фактически меняются только два первых параметра - команда строки и команда столбца (копировать нужно сразу три ячейки подряд - первую с формулой и две следующие справа. Один указанный вызов формулы заменяет две старых формулы вида (тоже для F2): [vba]
[/vba] Но это ж "внутренности", а снаружи - всего лишь идентификатор функции и пять параметров. А на внутренности практикующему пользователю таблицы можно и "забить", оставив их разработчику.
В общем, рекомендую новшествами увлечься. Как минимум, это не скучно.
Хочу поделиться новым опытом. Помогаю одному из участников нашего Форума построить удобную турнирную таблицу. За основу взяли таблицу этого топика. В процессе работы удалось применить новомодные функции таблиц Гугл, введенные совсем недавно в августе 2022. Это функции LAMBDA, MAP, SCAN, REDUCE, BYROW, BYCOL, MAKEARRAY, а также появившаяся возможность создания именованных пользовательских функций, составленных из других функций (не путать с функциями UDF, которые пишутся как скрипты).
Microsoft в некоторых особо продвинутых версиях Excel (по подписке, типа 365 или Web) ввел эти функции уже достаточно давно. Но в том-то и дело, что при проводимой им в последние годы политике, совершенно непонятно, когда пользователь конкретной версии получит их в своё активное распоряжение - ведь далеко не все имеют возможность пользования подписочной версией. Поэтому и получается, что теоретически оно, вроде, и есть, но не всегда "про нашу честь".
Google - другое дело: уж коли добавили, то всем и навсегда и, соответственно, новшествами можно начинать активно пользоваться. С нетерпением жду добавления Гуглом функции LET, хотя, как показала первая практика, и с LAMBDA уже можно творить довольно интересно, на новом уровне.
В общем, создал я две именованные функции (доступны в файле по ссылке выше, по команде меню "Данные \ Именованные функции"): * ZCOUNT_GAME_RESULTS(range; condition) - Подсчет исходов матчей, удовлетворяющих заданному условию * ZTOURN_CROSSTAB_CELL(teama; teambidx; rngteams; rngteamab; rngpointsab) - Вычисление содержимого ячейки турнирной таблицы ("шахматки") - счёт матча с активным разделителем (массив из 3-х ячеек)
Функции применяются на новом добавленном листе "таблица (именованные формулы)". Это копия листа "таблицы", но с новыми функциями.
Фукция ZCOUNT_GAME_RESULTS применяется в четырех колонках AK:AN ("В3","В2","П1","П"). Например, вызов в ячейке AK2 выглядит так: [vba]
Код
=ZCOUNT_GAME_RESULTS($C2:$AF2;">1")
[/vba] Для сравнения - на старом листе "таблицы" в аналогичной ячейке формула была такой: [vba]
[/vba] Налицо прогресс уже хотя бы в том, что вместо утомительного перечисления ячеек по отдельности (потому что не подряд идут, а с шагом) теперь указывается просто сплошной диапазон $C2:$AF2, а уж нужные ячейки выбираются "под капотом" функции с помощью "зубодробительных" конструкций: [vba]
[/vba] Особый кайф - в использовании внутри формулы имен параметров - range и condition, а не ссылок на ячейки. Очень удобно при разработке.
Вторая моя функция ZTOURN_CROSSTAB_CELL применяется на том же листе "таблица (именованные формулы)" в каждой третьей ячейке диапазона C2:AF11. Вызов в ячейке F2: [vba]
[/vba] Формула хорошо протягивается по всему диапазону C2:AF11. При копировании фактически меняются только два первых параметра - команда строки и команда столбца (копировать нужно сразу три ячейки подряд - первую с формулой и две следующие справа. Один указанный вызов формулы заменяет две старых формулы вида (тоже для F2): [vba]
[/vba] Но это ж "внутренности", а снаружи - всего лишь идентификатор функции и пять параметров. А на внутренности практикующему пользователю таблицы можно и "забить", оставив их разработчику.
В общем, рекомендую новшествами увлечься. Как минимум, это не скучно.Gustav
Здравствуйте, столкнулся с проблемой при настройке нового турнира, по положению почему то они сделали фактор личной встречи главнее разницы очков в сетах. Теперь не могу добавить ранжирование для личной встречи. Не могли бы помочь? В таблице лист "Таблица"
Здравствуйте, столкнулся с проблемой при настройке нового турнира, по положению почему то они сделали фактор личной встречи главнее разницы очков в сетах. Теперь не могу добавить ранжирование для личной встречи. Не могли бы помочь? В таблице лист "Таблица"
сделали фактор личной встречи главнее разницы очков в сетах. Теперь не могу добавить ранжирование для личной встречи
Надо же, 5 лет прошло! Это говорит о том, что вопросы турнирных таблиц - вечные вопросы! Как "Всемирная история, банк Империал"
Вопрос учета встреч между собой при равенстве турнирных очков у двух и более участников подробно рассмотрен в сообщении №3 в теме Турнирная таблица, ссылка на которую дана Еленой (Pelena) в этом топике чуть выше (в сообщении №2). Вот моя цитата оттуда (сейчас я лучше не скажу):
Итак, по поводу мини-чемпионатов среди команд, набравших равное количество очков. Оказалось, что надо к основной шахматке добавить еще одну, в которой отобразить только матчи среди команд с одинаковыми очками. По ней посчитать показатели и добавить в общую формулу ранга (на соответствующих позициях).
Эта дополнительная шахматка будет общей и единой (и вот в это не сразу верится!) для всех команд, имеющих равные очки с еще какими-нибудь командами. И неважно, какие равноочковые группы встретятся в ней. В примере из файла в дополнительную шахматку вынесены только матчи между командами, имеющими по 8 очков (4 команды) и по 6 очков (2 команды). Поначалу же казалось, что для каждой равноочковой группы нужно завести по отдельной таблице (и даже были громоздкие попытки реализации).
Там, в сообщении № 3, есть прикрепленный файл архива HockeyTab_EW.rar. Внутри архива - файл Excel с турнирной таблицей чемпионата отдела по настольному хоккею. В таблице чётко наблюдается основная шахматка (красочно расцвеченная, с черной главной диагональю). А справа от основной шахматки - ещё одна (блеклая, с серой главной диагональю), очень неполная по результатам, но именно в этом ее смысл. Вот эту дополнительную "серую" шахматку Вам и надо изучить и по аналогии воспроизвести в своей таблице. Понятно, что её всегда можно скрыть от посторонних глаз (скрыть столбцы).
сделали фактор личной встречи главнее разницы очков в сетах. Теперь не могу добавить ранжирование для личной встречи
Надо же, 5 лет прошло! Это говорит о том, что вопросы турнирных таблиц - вечные вопросы! Как "Всемирная история, банк Империал"
Вопрос учета встреч между собой при равенстве турнирных очков у двух и более участников подробно рассмотрен в сообщении №3 в теме Турнирная таблица, ссылка на которую дана Еленой (Pelena) в этом топике чуть выше (в сообщении №2). Вот моя цитата оттуда (сейчас я лучше не скажу):
Итак, по поводу мини-чемпионатов среди команд, набравших равное количество очков. Оказалось, что надо к основной шахматке добавить еще одну, в которой отобразить только матчи среди команд с одинаковыми очками. По ней посчитать показатели и добавить в общую формулу ранга (на соответствующих позициях).
Эта дополнительная шахматка будет общей и единой (и вот в это не сразу верится!) для всех команд, имеющих равные очки с еще какими-нибудь командами. И неважно, какие равноочковые группы встретятся в ней. В примере из файла в дополнительную шахматку вынесены только матчи между командами, имеющими по 8 очков (4 команды) и по 6 очков (2 команды). Поначалу же казалось, что для каждой равноочковой группы нужно завести по отдельной таблице (и даже были громоздкие попытки реализации).
Там, в сообщении № 3, есть прикрепленный файл архива HockeyTab_EW.rar. Внутри архива - файл Excel с турнирной таблицей чемпионата отдела по настольному хоккею. В таблице чётко наблюдается основная шахматка (красочно расцвеченная, с черной главной диагональю). А справа от основной шахматки - ещё одна (блеклая, с серой главной диагональю), очень неполная по результатам, но именно в этом ее смысл. Вот эту дополнительную "серую" шахматку Вам и надо изучить и по аналогии воспроизвести в своей таблице. Понятно, что её всегда можно скрыть от посторонних глаз (скрыть столбцы).Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
:
