Как автоматически сортировать таблицу в Excel каждый раз, когда обновляется одно из ее значений?
Я сохраняю результаты сезона бейсбольной настольной игры Strat-O-Matic в электронной таблице Excel (Excel 2011 для Mac). У листа есть расписание лиги и положение лиги. Я уже настроил его так, чтобы каждый раз, когда я вводил счет игры, турнирная таблица обновлялась, чтобы отразить новые рекорды побед-поражений команд, которые участвовали в этой игре.
После того, как я введу счет игры, я бы хотел, чтобы турнирная таблица сортировалась автоматически, а не сортировалась вручную по процентам выигрышей. Если это что-то меняет, на самом деле в этой лиге есть четыре разных турнирных таблицы, по одной для каждого из четырех дивизионов. Все они должны быть индивидуально отсортированы по процентам выигрышей при каждом обновлении таблицы.
Любая помощь приветствуется, спасибо.
1 ответ
Для сортировки таблицы вам нужно написать скрипт VBA. Вообще говоря, формула в ячейке не может влиять на содержимое другой ячейки (точнее, формула другой ячейки; одна ячейка может изменить значение других ячеек, если другие ячейки содержат формулу, которая ссылается на нее).
В качестве обходного пути, так как вы собираетесь сортировать таблицу назначения (т.е. таблицы) после обновления исходной таблицы (например, расписание), мы можем выполнить некоторую сортировку косвенно:
- Измените исходную таблицу, чтобы рейтинг автоматически вычислялся
- Обновите таблицу назначения, чтобы ее содержимое было найдено из исходной таблицы
VLOOKUP,
Шаг 1
Сделайте ранжирование по первому столбцу (то есть столбцу A в этом примере) и сделайте формулу следующим образом:
A | B | C
--------------------------------------------------+------+-------------------
Rank | Team | Winning Percentage
=COUNTIF(C$2:C$9,">"&C2)+1 | A | 0.05
=COUNTIF(C$2:C$9,">"&C3)+COUNTIF(C$2:C2, C3)+1 | B | 0.99
=COUNTIF(C$2:C$9,">"&C4)+COUNTIF(C$2:C3, C4)+1 | C | 0.81
=COUNTIF(C$2:C$9,">"&C5)+COUNTIF(C$2:C4, C5)+1 | D | 0.92
=COUNTIF(C$2:C$9,">"&C6)+COUNTIF(C$2:C5, C6)+1 | E | 0.54
=COUNTIF(C$2:C$9,">"&C7)+COUNTIF(C$2:C6, C7)+1 | F | 0.15
=COUNTIF(C$2:C$9,">"&C8)+COUNTIF(C$2:C7, C8)+1 | G | 0.15
=COUNTIF(C$2:C$9,">"&C9)+COUNTIF(C$2:C8, C9)+1 | H | 0.40
Пожалуйста, обновите диапазон самостоятельно. Первый COUNTIF подсчитывает, сколько команд имеет процент выигрыша, превышающий его, а вторая COUNTIF считает, сколько команд связано с этим. Это важно, потому что нам не нужно, чтобы дублирующийся рейтинг сбивал с толку VLOOKUP,
Например, приведенный выше пример выглядит так:
Rank | Team | Winning Percentage
8 | A | 5%
1 | B | 99%
3 | C | 81%
2 | D | 92%
4 | E | 53%
6 | F | 15%
7 | G | 15%
5 | H | 40%
Как вы видите, команда F и команда G имеют одинаковый процент выигрыша, им присваивается различный рейтинг.
Шаг 2
В таблице назначения (то есть в турнирной таблице) вам необходимо обновить ее, используя множество VLOOKUP:
A | B | C
-----+---------------------------------------------+------------------------------------------
Rank | Team | Winning percentage
1 | =VLOOKUP($A2,Schedule!$A$1:$C$9,2,FALSE) | =VLOOKUP($A2,Schedule!$A$1:$C$9,3,FALSE)
2 | =VLOOKUP($A3,Schedule!$A$1:$C$9,2,FALSE) | =VLOOKUP($A3,Schedule!$A$1:$C$9,3,FALSE)
3 | =VLOOKUP($A4,Schedule!$A$1:$C$9,2,FALSE) | =VLOOKUP($A4,Schedule!$A$1:$C$9,3,FALSE)
4 | =VLOOKUP($A5,Schedule!$A$1:$C$9,2,FALSE) | =VLOOKUP($A5,Schedule!$A$1:$C$9,3,FALSE)
5 | =VLOOKUP($A6,Schedule!$A$1:$C$9,2,FALSE) | =VLOOKUP($A6,Schedule!$A$1:$C$9,3,FALSE)
6 | =VLOOKUP($A7,Schedule!$A$1:$C$9,2,FALSE) | =VLOOKUP($A7,Schedule!$A$1:$C$9,3,FALSE)
7 | =VLOOKUP($A8,Schedule!$A$1:$C$9,2,FALSE) | =VLOOKUP($A8,Schedule!$A$1:$C$9,3,FALSE)
8 | =VLOOKUP($A9,Schedule!$A$1:$C$9,2,FALSE) | =VLOOKUP($A9,Schedule!$A$1:$C$9,3,FALSE)
И вы получите ваши результаты, как:
Rank | Team | Winning percentage
1 | B | 99%
2 | D | 92%
3 | C | 81%
4 | E | 53%
5 | H | 40%
6 | F | 15%
7 | G | 15%
8 | A | 5%