Транспонировать значения из строки, в которой выполнены критерии

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

Входной набор данных выглядит следующим образом:

Набор данных со скидками

То, что я хочу сделать, - это по существу транспонировать набор данных и создать следующий вывод, где скидка > 0, и вернуть название компании/столбца. (см. ниже)

Введите описание изображения здесь

Я попытался использовать формулы ниже: 

{=INDEX(A1:F7,SMALL(IF(AND(A2:A7=H2,B2:F2>0),COLUMNS(B1:F1)),COLUMNS(1:1))-1,3)}

- это, кажется, всегда дает #NUM! Ошибка 

=INDEX(A1:F7, MATCH(H2,A2:A7,0),MATCH(I2,B1:F1,0))
  • это работает только в том случае, если я перечислил все возможные маршруты и имею один маршрут для каждой компании, даже если скидка равна 0. Учитывая, что у меня есть 40 компаний и 15000+ маршрутов, я действительно не хочу создавать длинный список LONPAR с каждой компанией в колонке B, за которой следует LONFRA и т. д.

Есть ли что-нибудь, чего мне не хватает, или какие-либо указания или функции, которые вы могли бы предложить, чтобы помочь мне получить решение?

1 2
excel arrays excel-formula

1 ответ:

Следующий код, по-видимому, довольно эффективно разбивает вашу таблицу скидок на поезд.

Option Explicit

Sub qwewretq()
    Dim a As Long, b As Long, h As Long, aDISs As Variant

    With Worksheets("Sheet10")
        With .Cells(1, 1).CurrentRegion
            aDISs = .Cells.Value2
            h = .Columns.Count + 2
        End With
        .Cells(1, h).CurrentRegion.ClearContents
        .Cells(1, h).Resize(1, 3) = Array("route", "company", "discount")
        For a = 2 To UBound(aDISs, 1)
            For b = 2 To UBound(aDISs, 2)
                If aDISs(a, b) > 0 Then
                    If IsError(Application.Match(aDISs(a, 1), .Columns(h), 0)) Then
                        .Cells(Rows.Count, h + 1).End(xlUp).Offset(1, -1) = aDISs(a, 1)
                    End If
                    .Cells(Rows.Count, h + 1).End(xlUp).Offset(1, 0).Resize(1, 2) = _
                        Array(aDISs(1, b), aDISs(a, b))
                End If
            Next b
        Next a
    End With
End Sub

Есть области, которые можно настроить для дополнительной эффективности, в частности настройки среды приложения, такие как .Свойство ScreenUpdating и .Свойство EnableEvents . Для чрезвычайно больших блоков данных также может помочь построение целевого массива и сброс разрешенных данных обратно на рабочий лист en masse. Вы должны быть в состоянии отформатировать целевую таблицу для проценты и любая визуальная оценка довольно легко.

transpose_trains

1