Намерете първата непразна стойност в низ
Задача: изисква се формула, която ви позволява да намерите първата непразна стойност в ред, т.е. ще върне номера на първата непразна клетка в реда. Да приемем, че данните са представени в колони C:K (фиг. 1).
Ориз. 1. Формулата намира първата непразна клетка във всеки ред и връща нейния номер в масива
Изтеглете бележка във формат Word или pdf, примери във формат Excel
Решение: Формула в A2: =MATCH(1,INDEX(1-EMPLAND(C2:K2),1,0),0). Въпреки че тази формула работи с клетъчен масив, тя в крайна сметка връща една стойност, така че не е необходимо да натискате Ctrl+Shift+Enter, докато пишете (за формули за масиви вижте Майкъл Гирвин, Ctrl+Shift+Enter, Овладяване на формули за масиви в Excel).
Нека да разгледаме по-отблизо как работи формулата. Функцията IMPLAND връща TRUE, ако клетката е празна, и FALSE, ако клетката не е празна. Погледнете реда за данни в C2:K2. ISBLANK(C2:C2) ще върне масив: .
Обърнете внимание, че след това този масив се изважда от 1. Когато се опитате да използвате TRUE и FALSE в математическа формула, TRUE се интерпретира като 1 и FALSE като 0. Указването на 1-NULL(C2:K2) преобразува масив от булеви стойности TRUE/FALSE в числова последователност от нули и единици: .
И така, фрагментът на формулата 1-EMPLAND(С2:К2) връща масив. Това е малко странно, тъй като Excel очаква една стойност от такъв фрагмент. Странно, но не смъртоносно. Функцията INDEX също обикновено връща една стойност. Но ето какво казва помощта за Excel: Ако посочите 0 (нула) като аргумент номер_на_ред или номер_на_колона, функцията ИНДЕКС ще върне масив от стойности съответно за цяла колона или цял ред. За да използвате върнатите стойности като масив, въведете функциятаИНДЕКС като формула за масив към хоризонтален диапазон от клетки за ред и вертикален диапазон за колона.
Ако функцията INDEX върне масив, тя може да се използва в други функции, които очакват аргумент от масив.
И така, като посочим стойността нула като трети аргумент на функцията INDEX(1-EMPLAND(C2:K2);1;0 ), получаваме масив.
Функцията MATCH търси търсена стойност в едномерен масив и връща относителната позиция на първото намерено съвпадение. Формулата =MATCH(1, ARRAY, 0) иска от Excel да намери номера на клетката в ARRAY, който съдържа първото срещнато 1. Функцията MATCH определя коя колона съдържа първата непразна клетка. Когато помолите MATCH да намери първото 1 в масива, той връща 3.
Така =MATCH(1;INDEX(1-EMPLAND(C2:K2);1;0);0) става =MATCH(1;;0) и връща резултат 3.
На този етап знаете, че третата колона на ред C2:K2 съдържа първата непразна стойност. Оттук нататък е много лесно, като използвате функцията INDEX, да откриете самата тази първа непразна стойност: =INDEX(ARray;1;3) или =INDEX(C2:K2;1;MATCH(1;INDEX(1-EMPLAND(C2:K2);1;0);0)).
Ориз. 2. Формулата намира първата непразна клетка във всеки ред и връща стойността на тази клетка
Допълнителна информация: Ако всички клетки са празни, формулата връща грешка #N/A.
Алтернативни стратегии: Когато извадите стойността на NULL от единица, преобразувате булевите стойности TRUE/FALSE в цифрови 1/0. Можете да пропуснете тази стъпка, но тогава ще трябва да търсите FALSE като първи аргумент на функцията MATCH: =INDEX(C2:K2;1;MATCH(FALSE;INDEX(NULL(C2:K2);1;0);0)).