☰

Пример данных

tag-icon • tag-icon microsoft-excel worksheet-function
Пример данных

Я составляю организационную таблицу для молодой киберспортивной команды, и моя цель — рассчитать средний игровой рейтинг игроков моей команды.

Клетка F2и каждыйдругойстрока в столбце Fимеет идентификатор ранга, например «S3» или «G1» (для рангов серебро 3 или золото 1).

У меня есть еще один лист, содержащий числовые соответствия между этими идентификаторами рангов (I1 равен 1, I2 равен 2 и т. д.)

Итак, для каждой второй строки в этом диапазоне ( F2:F100) мне нужно получить идентификатор, преобразовать его в числовую форму с помощью операции VLOOKUPс Lookupsлистом, выполнить операцию FLOOR(AVERAGE()с результатом и преобразовать это число обратно в текстовую форму, изменив порядок операций VLOOKUP.

Я уже выполнил несколько шагов, но не уверен, как усреднить результаты предыдущей формулы, подобной этой.

Чтобы получить числовую версию ранга из его текстовой версии, используется следующая формула:

=VLOOKUP(F2, Lookups!A2:B29,2, FALSE)

Чего я не знаю, так это:

  • Как сделать ссылку на ячейку, которая пропускает все остальные ячейки
  • Как усреднить большое количество результатов VLOOKUP

Пример данных

Основной лист

Основная электронная таблица

Справочная таблица

Таблица поиска

Каждый набор из двух столбцов представляет одного игрока, причем ранг этого игрока хранится в строке F четного столбца. Из таблицы поиска мы видим, что S3 представляет числовой счет 10. G2 следующего игрока будет иметь счет 15 и т. д.

Как только у меня есть сумма этих чисел, я беру среднее значение, что дает мне средний балл для команды. Возвращаясь к таблице поиска, это делается в обратном порядке предыдущей операции и дает мне один ранг, который соответствует среднему баллу каждого игрока, указанного в таблице.

Взяв трех игроков на листе в настоящее время, я бы взял S3, G2 и NA. Это дает мне оценку 10, 15 и 10. Среднее из них равно 11 (округленное вниз), что соответствует рангу S2 с использованием таблицы поиска. Ячейка, содержащая эту гипотетическую формулу, будет иметь значение "S2".

решение1

В:Останутся ли клавиши F3, F5, F7 и т. д. пустыми?
A: Будут. Я закрасил поле серым, чтобы показать, что туда ничего вводить не нужно.

Проще всего использовать эти пустые ячейки для хранения числовых значений для отдельных рангов. Затем вы можете усреднить F2:F101, и будут учитываться только истинные числа. Числа можно скрыть от отображения с помощью формата числа ячейки ;;;. Это отобразит пустую ячейку для всего, что набрано или возвращено из формулы, при этом сохранив необработанное значение.

Если вы не хотите копировать и вставлять 50 раз, выполните эту короткую подпроцедуру после корректировки имени основного рабочего листа в первой строке кода.

Option Explicit

Sub PopulateLookups()

    With Worksheets("sheet1")
        With .Range(.Cells(2, "F"), .Cells(.Rows.Count, "F").End(xlUp))
            With .SpecialCells(xlCellTypeConstants, xlTextValues)  '<~~ see footnote
                With .Offset(1, 0)

                    .FormulaR1C1 = "=vlookup(r[-1]c, lookups!r2c1:r29c2, 2, false)"
                    .NumberFormat = ";;;"

                End With
            End With
        End With
    End With

End Sub

Теперь вы можете использовать один из следующих способов для получения среднего значения искомых значений.

=average(F2:F100)
=average(F:F)

Если вы не хотите повторно использовать эти пустые ячейки, «вспомогательный столбец» является жизнеспособной альтернативой. Я протестировал метод пустого столбца против метода вспомогательного столбца, и результаты были идентичны.

¹ Если существующие ранги возвращаются по формуле и не вводятся, тоxlCellTypeConstantsследует заменить наxlCellTypeFormulas.

решение2

Я бы решил первую задачу поиска, где требуется значение в каждой второй строке, поместив ВПР внутрь формулы iferror, которая просто возвращает пустой текст, когда не может найти результат:

=IFERROR(VLOOKUP(F2,Lookups!A2:B29,2,0),"")

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

Наконец, формула vlookup не будет работать, чтобы получить "Rank" из этого среднего, потому что он всегда начинается слева и движется вправо - тогда как вам нужно смотреть справа и двигаться влево. Для этого я использую комбинацию Index/Match:

=INDEX(Lookups!A2:B29,MATCH(E11,Lookups!B2:B29,1),1)

Для части соответствия я использовал параметр "1", чтобы он нашел ближайшее значение без перехода за пределы, предполагая, что это может быть не точное совпадение. Ознакомьтесь с описаниями индекса и формулы соответствия внутри Excel для получения дополнительной информации и опций.

Я построил это в виде листа-образца, который показывает выписанные формулы. Надеюсь, это поможет:Образец решения с примерами формул

решение3

Предположим, вы хотите получить среднее значение четных строк, используйте следующую формулу:

=IF(ROW()/2 = ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

В противном случае используйте:

=IF(ROW()/2 <> ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

Затем используйте =AVERAGE(G1:G5)для получения среднего значения

Связанный контент