希望從右到左計算零值，直到出現非零值 (Looking to count zero values from right to left until non-zero values appears)

問題描述

希望從右到左計算零值，直到出現非零值 (Looking to count zero values from right to left until non‑zero values appears)

我有一個大的月值表。
我希望從右到左計算零值，一旦出現非零值就停止。
我希望最後一列顯示這些

| 一月 | 二月 | 五月 | 四月 | 五月 | 六月 | 我需要的值 |
本 | 10 | 10 | 10 | 0 | 0 | 0 | =3 |
蒂姆 | 0 | 0 | 10 | 10 | 10 | 0 | =1 |
蘇珊 | 0 | 0 | 5 | 10 | 0 | 10 | =0 |
弗蘭克 | 10 | 0 | 0 | 10 | 10 | 10 | =0 |

非常感謝您的幫助！

參考解法

方法 1:

I don't think you need anything very sophisticated ‑ just find last column which is non‑zero:

=ArrayFormula(columns(B:G)‑max(if(B2:G2>0,column(B:G)‑column(A:A),0)))

方法 2:

try:

=ARRAYFORMULA(IF(A2:A="",,LEN(REGEXREPLACE(INDEX(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(
 IF(VLOOKUP(A2:A, A2:G, TRANSPOSE(SORT(TRANSPOSE(COLUMN(B:G)), 1, 0)), 0)=0, 
 "♦", "♥")),,9^9)), "♥", , 0),,1), "^ .+| |#.+", ))))

(by Mr K、Tom Sharpe、player0)

參考文件

1. Looking to count zero values from right to left until non‑zero values appears (CC BY‑SA 2.5/3.0/4.0)