Excel: как показать 3 лучших "победителей" по возрасту и полу?

Question

Я хотел бы использовать Excel для управления данными для марафона. Марафон объединяет победителей по age и gender в category , и присуждает призы за первое-третье место в каждой category .

Можно ли использовать Excel для динамического определения победителей путем ввода данных на одном листе и автоматического вычисления результатов и их отображения на другом листе?

У меня есть один лист, который содержит следующие данные для каждой строки:

name, age, gender, age category, overall rank

Я открыт для VBA и Excel подходов, основанных на функциях. Спасибо!

Answer 1

Если вы хотите обновить рабочий лист2 при изменении данных в рабочем листе1, вы можете использовать VBA для создания результирующей страницы, которая динамически обновляется.

• На вкладке разработчика нажмите Visual Basic.
• Разверните папку объектов Microsoft Excel и просмотрите код для Sheet1.

• Создайте новую функцию с именем:

  Private Sub Worksheet_ChangeByVal Target As Range)
  EndSub
  

Эта функция вызывается всякий раз, когда данные в листе 1 изменяются.

Чтобы обновить данные на основе столбца рейтинга и сделать следующие предположения:
Данные находятся в этих столбцах Имя: столбец A, Возраст: столбец B, Пол: столбец C, Категория: столбец D, Общий рейтинг: столбец E

В подпункте Worksheet_Change сравните значение в столбце E, столбце ранжирования и перенесите содержимое строки, если участник занял первые четыре позиции.

'If data is changed within column 5 (ranking column), repopulate worksheet2
If Target.Column = 5 Then
    'Remove all of the old rows except the header
    Sheets(2).Rows("2:" & CStr(Sheets(2).UsedRange.Rows.Count)).EntireRow.Delete

    'For each row on worksheet1 check if the value in column "E", if the ranking column is less than 4, if it is copy the row onto worksheet2
    Dim rowCounter As Integer: rowCounter = 1
    For i = 1 To Sheets(1).UsedRange.Rows.Count Step 1
        If (Sheets(1).Range("E" & CStr(i)).Value < 4 And Sheets(1).Range("E" & CStr(i)).Value <> "") Then
            Sheets(2).Range("A" & CStr(rowCounter + 1)).Value = Sheets(1).Range("A" & CStr(i)).Value
            Sheets(2).Range("B" & CStr(rowCounter + 1)).Value = Sheets(1).Range("B" & CStr(i)).Value
            Sheets(2).Range("C" & CStr(rowCounter + 1)).Value = Sheets(1).Range("C" & CStr(i)).Value
            Sheets(2).Range("D" & CStr(rowCounter + 1)).Value = Sheets(1).Range("D" & CStr(i)).Value
            Sheets(2).Range("E" & CStr(rowCounter + 1)).Value = Sheets(1).Range("E" & CStr(i)).Value
            rowCounter = rowCounter + 1
        End If
    Next i
End If

Вставьте сюда алгоритм сортировки, если это необходимо, после переноса данных.

Если вам нужна дополнительная информация или помощь в сортировке, пожалуйста, дайте мне знать.

Answer 2

Да. То, что вы описываете, является сводной таблицей. Щелкните ячейку на вкладке исходных данных, вставьте сводную таблицу и нажмите кнопку «ОК». По умолчанию он будет создан на новом листе. Перетащите возрастную группу, пол и имя в область строк и ранжируйте в область значений.

Щелкните правой кнопкой мыши любое из имен в сводной области и используйте Filter> Top 10. Предполагая, что верхний ранг равен 1, измените "Top" на "Bottom", 10 на 3, и вы получите нечто похожее на это:

Если вы основываете сводную таблицу на таблице Excel (которая была создана с помощью команды «Вставка»> «Таблица»), вы можете добавить данные в исходную таблицу, обновить сводную таблицу (на ленте инструментов «Сводная таблица»), и отобразятся текущие результаты.

Копия файла здесь.

Answer 3

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

=IF(*ConditionForCatI*,CatI,IF(*ConditionForCatII*,CatII,...)))

пока все категории не заполнены. После этого выберите фильтр под данными. Это позволит вам отсортировать их по возрастной группе или полу. (например, если вы хотите отсортировать по полу):

1. щелкните стрелку вниз, кроме заголовка пола
2. выберите только мужчину или только женщину
3. щелкните стрелку вниз, кроме заголовка времени (я полагаю, у вас будет заголовок времени, чтобы определить победителя)
4. Сортировка от А до Я, так как он покажет от наименьшего к наибольшему, а у победителя будет наименьшее время

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

ура