Изтеглете примерната работна книга
Този урок ще ви научи как да търсите последната стойност в колона или ред в Excel.
Последна стойност в колона
Можете да използвате функцията LOOKUP, за да намерите последната непразна клетка в колона.
| 1 | = LOOKUP (2,1/(B: B ""), B: B) |
Нека преминем през тази формула.
Частта от формулата B: B ”” връща масив, съдържащ True и False стойности: {FALSE, TRUE, TRUE,…}, като всяка клетка в колона B е празна (FALSE).
| 1 | = LOOKUP (2,1/({FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; …), B: B) |
Тези булеви стойности се преобразуват в 0 или 1 и се използват за разделяне на 1.
| 1 | = LOOKUP (2, {#DIV/0!; 1; 1; 1; 1; 1;#DIV/0!;, B: B) |
Това е lookup_vector за функцията LOOKUP. В нашия случай lookup_value е 2, но най -голямата стойност в lookup_vector е 1, така че функцията LOOKUP ще съвпадне с последните 1 в масива и ще върне съответната стойност в result_vector.
Ако сте сигурни, че имате само числови стойности в колоната си, вашите данни започват от ред 1 и диапазонът от данни в непрекъснато, можете да използвате малко по -проста формула с функциите INDEX и COUNT.
| 1 | = ИНДЕКС (B: B, COUNT (B: B)) |
Функцията COUNT връща броя на клетките, пълни с данни в непрекъснатия диапазон (4) и по този начин функцията INDEX дава стойността на клетката в съответния ред (4 -ти).
За да избегнете възможни грешки, когато вашият диапазон от данни съдържа смес от числови и нечислени стойности или дори някои празни клетки, можете да използвате функцията LOOKUP заедно с функциите ISBLANK и NOT.
| 1 | = LOOKUP (2,1/(НЕ (ISBLANK (B: B))), B: B) |
Функцията ISBLANK връща масив, съдържащ True и False стойности, съответстващи на 1 и 0. Функцията NOT променя True (т.е. 1) на False и False (т.е. 0) на True. Ако обърнем този получен масив (когато разделим 1 на този масив), получаваме масив с резултати, съдържащ отново #DIV/0! грешки и 1, които могат да се използват като масив за търсене (lookup_vector) в нашата функция LOOKUP. След това функционалността на функцията LOOKUP е същата като в първия ни пример: връща стойността на вектора на резултата в позицията на последния 1 в масива за търсене.
Когато имате нужда от връщане на последния запис, можете да промените формулата, използвана в първия ни пример, заедно с функцията ROW във вашия result_vector.
| 1 | = LOOKUP (2,1/(B: B ""), ROW (B: B)) |
Последна стойност в ред
За да получите стойността на последната непразна клетка в ред, изпълнена с цифрови данни, може да искате да използвате подобен подход, но с различни функции: функцията OFFSET заедно с функциите MATCH и MAX.
| 1 | = OFFSET (Справка, редове, колони) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) |
Нека да видим как работи тази формула.
MATCH функция
Използваме функцията MATCH, за да „преброим“ колко стойности на клетките са под 1 + максимума на всички стойности в ред2, започвайки от B2.
| 1 | = MATCH (lookup_value, lookup_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) |
Lookup_value на функцията MATCH е максимумът от всички стойности в реда2 + 1. Тъй като тази стойност очевидно не съществува в ред2 и match_type е зададен на 1 (по -малко или равно на lookup_value), функцията MATCH ще върне последната „проверена“ позиция на клетката в масива, тоест броя на клетките, изпълнени с данни в диапазона B2: XFD2 (XFD е последната колона в по -новите версии на Excel).
OFFSET функция
След това използваме функцията OFFSET, за да получим стойността на тази клетка, чиято позиция е върната от функцията MATCH.
| 1 | = OFFSET (B2,0, C4-1) |
