У меня есть эта формула массива.
=ArrayFormula(IF(P3:P="",,IFERROR(VLOOKUP(P3:P,sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0),2,0),UPLUS(V3:V))))
Работает нормально в гугл листах. Но когда я конвертировал свои листы Google в Excel, он сказал, что формула сортировки неверна. И я замечаю, что массивы немного отличаются в Excel. Так как я могу преобразовать эту формулу из листов Google, чтобы преуспеть, не теряя функции.
Ошибка в функции sort . Прежде чем мы начнем, давайте рассмотрим пример функции sort Google Sheets, которую вы использовали:
Давайте посмотрим, у нас есть сетка от A1 до C3, как показано ниже:
-------------------------------
| A | B | C |
-----------------------------------------
| 1 | 3 | 11 | 61 |
-----------------------------------------
| 2 | 7 | 13 | 21 |
-----------------------------------------
| 3 | 5 | 12 | 141 |
-----------------------------------------
Тогда, если вы выпустите эту формулу в E7:
=SORT(A1:C3, 2, 0)
Это создаст отсортированную сетку из A1:C3 с самой верхней и левой ячейками, расположенными в E7 и отсортированными по второму столбцу (столбец B или столбец 2). Третий аргумент требует ascending (1) or descending (0) order столбца 2 или столбца B. Результат:
-------------------------------
| E | F | G |
-----------------------------------------
| 7 | 7 | 13 | 21 |
-----------------------------------------
| 8 | 5 | 12 | 141 |
-----------------------------------------
| 9 | 3 | 11 | 61 |
-----------------------------------------
Ваш диапазон значений является кусочным (не непрерывным), и {{C:C,L:L,A:A};{D:D,M:M,A:A}} - это ввод, который выглядит следующим образом при вставке в давайте предположим, что Q14:
-------------------------------------------------
| Q | R | S |
-----------------------------------------------------------
| 14 |entire C column|entire L column|entire A column|
-----------------------------------------------------------
|14+Len(C)|entire D column|entire M column|entire A column|
-----------------------------------------------------------
где Len(C) - длина столбца C. Обратите внимание, что все столбцы должны иметь одинаковую длину (количество строк), чтобы использовать функцию SORT. Теперь здесь вы выдаете следующую формулу:
sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0)
Как вы, наверное, догадались, столбец 3 теперь является столбцом S, который представляет собой слияние столбца A дважды. 0 предполагает сортировку в порядке убывания.
Вы должны имитировать эту функцию в Excel, так как Excel не имеет той же функции сортировки.
Если вы видите эту страницу, она объясняет, как сортировать массив с помощью функций INDEX, MATCH и ROWS для выполнения той же операции в MS Excel, ОДНАКО, массивы Excel не работают так же, как массивы Google Sheet (последний из них значительно проще с функцией sort ). Просто посмотрите на огромную формулу сортировки для Excel по ссылке. К сожалению, в Excel нет более простого метода. Технически, при сортировке с использованием формул в Excel вы сами пишете алгоритм сортировки.
Смею предположить, что в этом сценарии вам придется изолировать сортировку от основной формулы. Например, сначала выполните сортировку динамических данных (сортировку по формуле), а затем примените к ней VLOOKUP и другие операции.