Excel 2016: заполнить столбец списком на основе критериев соответствия из другого листа

Question

Я попытался сформулировать название как можно лучше и постараюсь объяснить. Я приму любые предложения по переименованию названия, если это необходимо.

У меня есть два листа, которые я пытаюсь использовать для отслеживания раундов, сыгранных игроками в спорте. Это по сути одни и те же данные, просто представленные по-разному

На одном листе у меня есть 12 рядов, каждый ряд представляет игрока, с именем игрока в столбце B. 

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

например,

================================================
|  A  |    B    |    C    |   ...   |    M     |
================================================
| No. | Player  | Round 1 |   ...   | Round 11 |
------------------------------------------------
|  1  | Male 1  |    I    |         |    I     |
|  2  | Male 2  |    I    |         |    I     |
|  3  | Male 3  |    I    |    I    |          |
|  4  | Male 4  |    I    |    I    |          |
|  5  | Male 5  |    I    |    I    |    I     |
|  6  | Male 6  |         |    I    |    I     |
|  7  | Male 7  |         |    I    |    I     |
|  8  | Female1 |    I    |         |          |
|  9  | Female2 |    I    |    I    |          |
|  10 | Female3 |    I    |    I    |    I     | 
|  11 | Female4 |         |    I    |    I     |  
|  12 | Female5 |         |         |    I     |

Только 5 игроков мужского пола могут играть в раунде. Только 3 игрока женского пола могут играть в раунде.

Поэтому, если игрок находится внутри, я вписываю "I" в столбец для соответствующего раунда и очищаю, если они не играют в этом раунде.

На другом листе у меня есть строки, которые представляют игроков, которые находятся "IN", и игроков, которые являются "OUT". Затем у меня есть столбцы, которые представляют раунд игры.

Однако в эти ячейки я в настоящее время вручную вводю (копирую / вставляю) имена игроков, которые участвуют в этом раунде и / или выходят из него.

================================================
|  A  |    B    |    C    |   ...   |    M     |
================================================
| No. | Status  | Round 1 | Round 2 | Round 3 |
|-----------------------------------------------
| 1   |   IN    | Male 1  | Male 3  | Male 1  |
| 2   |   IN    | Male 2  | Male 4  | Male 2  |
| 3   |   IN    | Male 3  | Male 5  | Male 6  |
| 4   |   IN    | Male 4  | Male 6  | Male 7  |
| 5   |   IN    | Male 5  | Male 7  | Male 7  |
| 6   |   IN    | Female1 | Female2 | Female3 |
| 7   |   IN    | Female2 | Female3 | Female4 |
| 8   |   IN    | Female3 | Female4 | Female5 |
| 9   |   OUT   | Male 6  | Male 1  | Male 3  |
| 10  |   OUT   | Male 7  | Male 2  | Male 4  |
| 11  |   OUT   | Female4 | Female1 | Female1 |
| 12  |   OUT   | Female5 | Female5 | Female2 |

Как я могу автоматически заполнить имена игроков для столбцов на втором листе на основе строки и статуса с первого листа?

РЕДАКТИРОВАТЬ:

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

РЕДАКТИРОВАТЬ:

Добавлены имена столбцов в примерах.

Answer 1

Этот код VBA сделает свое дело

Public Sub players_rounds()
    first_sheet = "Sheet1"
    second_sheet = "Sheet2"
    Dim wkb As Workbook
    Dim wk1 As Worksheet
    Dim wk2 As Worksheet
    Set wkb = ThisWorkbook
    Set wk1 = wkb.Worksheets(first_sheet)
    Set wk2 = wkb.Worksheets(second_sheet)
    wk1_lastColumn = wk1.Cells(1, Columns.Count).End(xlToLeft).Column
    wk1_lastRow = wk1.Cells(Rows.Count, 1).End(xlUp).Row
    count_in = 0
    For i = 2 To wk1_lastRow
        If wk1.Cells(i, 2) = "I" Then count_in = count_in + 1
    Next i
    wk2.Cells.Clear
    wk2.Rows(1).value = wk1.Rows(1).value
    wk2.Cells(1, 1).value = "Status"
    count_out = wk1_lastRow - count_in - 1

    For i = 2 To count_in + count_out + 1
        If i <= count_in + 1 Then
            wk2.Cells(i, 1).value = "IN"
        Else
            wk2.Cells(i, 1).value = "OUT"
        End If
    Next i

    For i = 2 To wk1_lastRow
        thisplayer = wk1.Cells(i, 1)
        For j = 2 To wk1_lastColumn
            playervalue = wk1.Cells(i, j)
            playerround = wk1.Cells(1, j)
            If playervalue = "I" Then
                firstrow = 2
                lastrow = count_in + 1
            Else
                firstrow = count_in + 2
                lastrow = count_in + count_out + 1
            End If
            For k = 2 To wk1_lastColumn
                If wk2.Cells(1, k) = playerround Then
                    For m = firstrow To lastrow
                        If wk2.Cells(m, k) = "" Then
                            wk2.Cells(m, k) = thisplayer
                            m = lastrow
                            k = wk1_lastColumn
                        End If
                    Next m
                End If
            Next k
        Next j
    Next i
End Sub

Откройте VBA / Macros с помощью Alt+ F11, щелкните правой кнопкой мыши ThisWorkbook и вставьте модуль под ним. Вставьте код с правой стороны.

Убедитесь, что значения переменных first_sheet и second_sheet совпадают с именами ваших таблиц.

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

Чтобы сделать его еще более автоматическим в макросах, дважды щелкните первый лист, в правой части выберите « Рабочий лист» и « Изменить».

Сделайте так, чтобы это выглядело так:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call players_rounds
End Sub

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

Answer 2

Я смог сделать это с помощью двух «вспомогательных матриц».  «Вспомогательная ячейка» - это ячейка, которая получает / вычисляет некоторое промежуточное значение из входных данных.  Это промежуточное значение затем используется в другой ячейке (ях) для получения желаемого результата.  Обычно в таблице табличных данных есть «вспомогательный столбец».  Я с нетерпением жду возможности узнать, сможет ли кто-нибудь упростить мой ответ и сделать его более элегантным, но это лучшее, что я смог придумать за 59 минут.

Можно разместить помощников на одном листе с фактическими данными, в другом столбце (столбцах) или строках, которые обычно скрыты.  Или вы можете поместить их на отдельных листах.  Поскольку этот ответ требует двух из них, я проиллюстрирую оба метода; затем вы можете использовать один метод последовательно.

Я предполагаю, что ваши исходные данные (представленные в вашем вопросе) находятся в Sheet1!A2:D8 , с заголовками в строке 1.  Положил

=IF(B2="", "blank", B2)

в ячейку AB2 и перетащите / заполните вниз до AB8 и вправо до столбца AD . Это приводит к

   |    AA    |    AB    |    AC    |    AD    |
---+----------+----------+----------+----------+
 1 |          |          |          |          |
 2 |          |   I      |   blank  |   I      |
 3 |          |   I      |   blank  |   I      |
 4 |          |   I      |   I      |   blank  |
 5 |          |   I      |   I      |   blank  |
 6 |          |   I      |   I      |   I      |
 7 |          |   blank  |   I      |   I      |
 8 |          |   blank  |   I      |   I      |

Я верю, что объяснения не нужны.

Теперь все становится сложнее.  Создайте Sheet3 и введите следующие формулы:

• B2 → =MATCH("I", Sheet1!AB$2:AB$8, 0)
• B3 → =MATCH("I", OFFSET(Sheet1!AB$2,B2,0):Sheet1!AB$8, 0) + B2
• B7 → =MATCH("blank", Sheet1!AB$2:AB$8, 0)
• B8 → =MATCH("blank", OFFSET(Sheet1!AB$2,B7,0):Sheet1!AB$8, 0) + B7

Перетащите / заполните ячейку B3 до B6 . Если вы когда-нибудь измените свою схему, чтобы у вас было более двух «аут» игроков одновременно, вам нужно будет соответствующим образом перетащить / заполнить ячейку B8 .  И, как и раньше, перетащите / заполните столбец B справа от столбца D Вы должны получить:

   |    A    |    B    |    C    |    D    |
---+---------+---------+---------+---------+
 1 |         |         |         |         |
 2 |         |      1  |      3  |      1  |
 3 |         |      2  |      4  |      2  |
 4 |         |      3  |      5  |      5  |
 5 |         |      4  |      6  |      6  |
 6 |         |      5  |      7  |      7  |
 7 |         |      6  |      1  |      3  |
 8 |         |      7  |      2  |      4  |

Объяснение:

• B2 содержит (относительное) местоположение первого I в диапазоне Sheet1!AB$2:AB$8 .  Это 1 , потому что Sheet1!AB2 содержит I , и это первая ячейка в этом диапазоне.  Аналогично, C2 и D2 содержат 3 и 1 соответственно, потому что Sheet1!AC4 и Sheet1!AD2 (третья и первая ячейки, соответствующие Player3 и Player1 соответственно), являются местоположениями первого I в этих столбцах.
• Глядя на B3 - OFFSET(Sheet1!AB$2,B2,0) OFFSET(Sheet1!AB$2,1,0) , что эквивалентно Sheet1!AB3 .  Так что этот MATCH смотрит на диапазон Sheet1!AB3:AB8 .  Этот MATCH также вернет 1 , потому что Sheet1!AB3 содержит I , и это первая ячейка в этом диапазоне.  Затем мы добавляем B2 (1) к этому, получая 2 , которое является местоположением второго I в Sheet1!AB2:AB8 .
• Аналогично, MATCH в ячейке D4 смотрит на диапазон Sheet1!AD4:AB8 .  Этот MATCH вернет 3 , потому что Sheet1!AD6 - первая ячейка в этом диапазоне, содержащая I Добавление D3 (2) дает нам 5 , потому что эта строка соответствует Player5.

• Затем мы делаем то же самое в строках 7 и 8, ища только blank .

  Мне бы не понадобился Sheet1!AA:AD вспомогательная матрица AD , за исключением того, что, очевидно, вы не можете использовать MATCH("", …) чтобы найти пустую ячейку.

Теперь мы можем просто спуститься и поставить =INDEX(Sheet1!$A$2:$A$8, Sheet3!B2) в Sheet2!B2 .

+--------+---------+---------+---------+
| Status | Round 1 | Round 2 | Round 3 |
+--------+---------+---------+---------+
| IN     | Player1 | Player3 | Player1 |
| IN     | Player2 | Player4 | Player2 |
| IN     | Player3 | Player5 | Player5 |
| IN     | Player4 | Player6 | Player6 |
| IN     | Player5 | Player7 | Player7 |
| OUT    | Player6 | Player1 | Player3 |
| OUT    | Player7 | Player2 | Player4 |