Формула Excel для получения последних 5 ненулевых значений и имен из двух столбцов соответственно, которые имеют ссылки

Question

У меня есть ряд данных в четырех столбцах в Excel, как показано ниже. Столбцы A, B представлены в текстовом формате, столбцы C, D генерируются с использованием ссылочной ссылки. C(F) и D(F) показывают значения в столбце формул C и D. Поскольку ячейки A10, A11, B10, B11 пусты, C10, C11, D10, D11 показывают «0».

Я пытаюсь получить последние 5 ненулевых значений в таком порядке, как показано в столбцах E и F, но на основе только столбцов C и D, а не напрямую из столбцов A или B.

     A       B      C(F)    D(F)      C       D      E      F    
 1 Name     Val     Name    Val     Name     Val   Name     Val
 2 abc01    101     =A2     =B2     abc01    101   abc04    104
 3 abc02    102     =A3     =B3     abc02    102   abc05    105
 4 abc03    103     =A4     =B4     abc03    103   abc06    106
 5 abc04    104     =A5     =B5     abc04    104   abc07    107
 6 abc05    105     =A6     =B6     abc05    105   abc08    108
 7 abc06    106     =A7     =B7     abc06    106
 8 abc07    107     =A8     =B8     abc07    107
 9 abc08    108     =A9     =B9     abc08    108
10                  =A10    =B10      0       0
11                  =A11    =B11      0       0

Если строка данных добавляется в столбцы A и B, столбцы E и F должны отображаться, как показано ниже.

     A       B      C(F)    D(F)      C       D      E      F    
 1 Name     Val     Name    Val     Name     Val   Name     Val
 2 abc01    101     =A2     =B2     abc01    101   abc05    105
 3 abc02    102     =A3     =B3     abc02    102   abc06    106
 4 abc03    103     =A4     =B4     abc03    103   abc07    107
 5 abc04    104     =A5     =B5     abc04    104   abc08    108
 6 abc05    105     =A6     =B6     abc05    105   abc09    109
 7 abc06    106     =A7     =B7     abc06    106
 8 abc07    107     =A8     =B8     abc07    107
 9 abc08    108     =A9     =B9     abc08    108
10 abc09    109     =A10    =B10    abc09    109
11                  =A11    =B11      0       0

Answer 1

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

• =INDEX(C:C, MATCH(0,C:C,0)-5, 1) в E2
• =INDEX(C:C, MATCH(0,C:C,0)-4, 1) в E3
• =INDEX(C:C, MATCH(0,C:C,0)-3, 1) в E4
• =INDEX(C:C, MATCH(0,C:C,0)-2, 1) в E5
• =INDEX(C:C, MATCH(0,C:C,0)-1, 1) в E6

а затем выберите их и перетащите / заполните вправо, в F2:F6 .  Как я сказал в начале, он ищет в столбце C значение 0, а затем выбирает значения из пяти предыдущих строк.