Это будет работать в Excel 2010 или более поздней версии, хотя, боюсь, я не могу сказать, будет ли это работать и для Mac.
Доступны более общие решения, которые будут работать с данными в любом количестве таблиц, а не только в двух. Однако они обязательно достаточно сложны. Более того, поскольку у вас на самом деле есть только две таблицы, вероятно, стоит также предложить альтернативное (хотя и менее гибкое) решение для этой цели.
=IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))+SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0)),"",IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0)),INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))))
Скопируйте вниз и вправо по мере необходимости.
Это может быть сокращено, хотя, опять же, используемые методы представляют собой сложный комплекс. Я не хочу покровительствовать; Я просто подумал, что вы могли бы найти немного более длинное, хотя, возможно, более понятное решение для большего использования.
Что касается объяснения:
Часть:
SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
подсчитывает, сколько записей в столбце ОПИСАНИЕ ПУНКТА таблицы ProductList непусто . COUNTIF является обычным выбором для этой цели, хотя две возможные установки, использующие эту функцию, потребуют, чтобы либо пустые места в вашем диапазоне были "подлинными" пустыми (и, следовательно, не пустой строкой "", например, как результат формул в тех ячейки), или что записи имеют согласованный тип данных. Поскольку я не могу быть уверен ни в одном из этих фактов при проверке вашего образца, тест на пустоту с использованием SUMPRODUCT - который наверняка сработает независимо от ответов на вышеприведенный - является более строгим выбором.
Так же:
SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0))
подсчитывает, сколько записей в столбце ОПИСАНИЕ ПУНКТА таблицы ProductList2 не являются пустыми.
Таким образом, начальный пункт:
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))+SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0)),""
означает, что в строках, превышающих общее количество непустых записей в столбце ОПИСАНИЕ ПУНКТА обеих таблиц, будет возвращен пустой.
Еще лучше ввести эти две конструкции в пару ячеек на рабочем листе и вместо этого ссылаться на них в формулах. Таким образом, их нужно будет оценивать только один раз, а не вычислять на каждой итерации формулы. Например, если вы положили в G1:
=SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
и в H1:
=SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0))
тогда основной формулой становится:
=IF(ROWS($1:1)>$G$1+$H$1,"",IF(ROWS($1:1)>$G$1,INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-$G$1),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))))
и будет намного эффективнее.
Обратите внимание, что эта установка, которая включает ссылку на счетчик, предпочтительнее, чем потенциально ресурсоемкая IFERROR , объяснение, почему вы можете найти здесь:
Найдите значение в списке и верните ВСЕ несколько соответствующих значений
В любом случае, если это начальное предложение FALSE , мы переходим ко второму условному выражению, т.е.
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
что, аналогично вышесказанному, говорит нам, что в строках, превышающих общее количество непустых записей в столбце ОПИСАНИЕ ПУНКТА таблицы ProductList , мы будем знать, что мы сконцентрируемся на другой таблице, т.е. ProductList2. В противном случае, если вышеприведенное FALSE , мы обратим наше внимание на первую таблицу, ProductList.
Я рассмотрю два примера, поскольку, хотя конструкции для каждого из них практически идентичны, в одном есть небольшая, но необходимая разница.
Используя данные, которые вы разместили, и взяв в качестве примера формулу в первой строке, то есть:
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0)),INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A)))
мы видим, что начальное предложение, а именно:
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
который:
IF(1>3
явно FALSE , и поэтому мы переходим к конструкции, включающей предложение value_if_false в этом операторе IF , то есть:
INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))
AGGREGATE (только 2010 или более поздняя версия) предлагает нам отличную конструкцию, отличную от CSE, по сравнению со стандартными настройками CSE с LARGE или SMALL .
Часть:
ROW(ProductList)-MIN(ROW(ProductList))+1
является стандартной конструкцией, используемой для генерации массива целых чисел от 1 до количества строк в таблице ProductList. Это работает, поскольку, предполагая, например, что эта таблица занимает строки от 4 до 7 (т.е. всего 4 строки), мы бы имели:
{4;5;6;7}-MIN({4;5;6;7})+1
то есть:
{4;5;6;7}-4+1
то есть:
{4;5;6;7}-3
то есть:
{1;2;3;4}
как требуется.
Часть:
LEN(ProductList[ITEM DESCRIPTION])>0
проверяет, имеют ли каждая из записей в этом столбце ненулевую длину (т. е. не являются ли они пустыми), и разрешает:
LEN({"Item1";"Item2";"";"Item3"})>0
то есть:
{5;5;0;5}>0
то есть:
{TRUE;TRUE;FALSE;TRUE}
Затем мы рассчитываем долю:
(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0)
который:
({1;2;3;4})/({TRUE;TRUE;FALSE;TRUE})
чтобы получить:
{1;2;#DIV/0!;4}
в силу того факта, что при применении любой подходящей математической операции (здесь деление) логические значения TRUE/FALSE приводятся в их числовые эквиваленты (TRUE = 1, FALSE = 0).
Таким образом, фактически, любые записи, которые не удовлетворяли нашему условию длины больше нуля (то есть пустые ячейки), теперь были обработаны как ошибки.
И поскольку, установив для второго параметра AGGREGATE значение 6, мы даем указание этой функции игнорировать любые значения ошибок в пределах диапазона, поэтому мы получили средство исключения любых пустых ячеек из рассмотрения.
В качестве таких:
AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1))
который:
AGGREGATE(15,6,{1;2;#DIV/0!;4},1)
возвращает 1 (наименьшее значение в этом массиве, 15 является параметром, эквивалентным SMALL для AGGREGATE).
Теперь мы передаем это значение в качестве параметра row_num в INDEX , так что:
INDEX(ProductList,AGGREGATE(15,6,{1;2;#DIV/0!;4},1),COLUMNS($A:A)
который:
INDEX(ProductList,1,1)
возвращает "Item1", по желанию.
Обратите внимание на использование:
COLUMNS($A:A)
для col_num элемента INDEX , который, поскольку эта формула скопирована вправо, станет последовательно:
COLUMNS($A:B)
то есть 2,
COLUMNS($A:C)
т.е. 3,
и т. д. и т. д., что дает нам возможность возвращать значения из последующих столбцов таблицы без необходимости вручную вставлять столбец, на который ссылаются для каждой итерации.
Чтобы взять другой пример, формула после копирования формулы на пять строк, то есть:
IF(ROWS($1:5)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0)),INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:5)),COLUMNS($A:A)))
будет, с этого времени первоначальное предложение разрешается в:
IF(5>3
что TRUE , означает, что мы рассматриваем другую конструкцию, то есть:
INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A))
Это практически идентично, хотя здесь мы должны быть немного осторожны с передачей параметра k в AGGREGATE . Если бы мы использовали, как и раньше, просто:
AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5))
мы бы хотели иметь:
AGGREGATE(15,6,({1;2;3;4;5})/({TRUE;TRUE;FALSE;TRUE;FALSE}),ROWS($1:5))
который:
AGGREGATE(15,6,{1;2;#DIV/0!;4;#DIV/0!},ROWS($1:5))
хотя, так как:
ROWS($1:5)
5, а так как массив:
{1;2;#DIV/0!;4;#DIV/0!}
не содержит пятое наименьшее значение, приведенное выше вернет ошибку.
Сначала вычитая количество непустых записей в другой таблице из этого значения 5, мы гарантируем, что получим правильный параметр. Поэтому мы используем:
ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
который:
5-3
то есть 2.
И вот мы теперь имеем:
AGGREGATE(15,6,{1;2;#DIV/0!;4;#DIV/0!},2)
который 2.
И, наконец, наша конструкция:
INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A))
который:
INDEX(ProductList2,2,1)
возвращает "Item5", как требуется.
И последнее: оптимизация скорости расчета. Точно так же, как более эффективно вводить две формулы подсчета в ячейки фактического рабочего листа, также эффективнее сделать так, чтобы порции:
ROW(ProductList)-MIN(ROW(ProductList))+1
а также:
ROW(ProductList2)-MIN(ROW(ProductList2))+1
рассчитываются только один раз каждый.
Хотя они, в отличие от двух конструкций SUMPRODUCT , по техническим причинам не могут быть введены в фактические ячейки рабочего листа, их можно сохранить в диспетчере имен (вкладка « Формулы »).
Следовательно, если вы определили их как, например, Arry1 и Arry2, где Arry1 :
=ROW(ProductList)-MIN(ROW(ProductList))+1
и Arry2 существо:
=ROW(ProductList2)-MIN(ROW(ProductList2))+1
основная формула станет более читабельной, более эффективной:
=IF(ROWS($1:1)>$G$1+$H$1,"",IF(ROWS($1:1)>$G$1,INDEX(ProductList2,AGGREGATE(15,6,Arry2/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-$G$1),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,Arry1/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))))
С уважением
