Изтеглете примерната работна книга
Този урок ще покаже как да намерите последния непразен ред в набор от данни в Excel и Google Sheets.
Намерете последния ред с данни
Често е полезно да знаете на кой ред завършват вашите данни. Ако вашият диапазон има или може да има празни клетки, можете да намерите последния непразен ред, като използвате един от методите по-долу.
Универсален метод
Първият метод използва функциите ROW и MAX и може да се използва с всякакъв вид данни:
| 1 | = MAX ((B: B "")*(ROW (B: B))) |
Нека анализираме тази формула.
Започваме с помощта на логически тест в колоната с данни. Нашата формула разглежда цялата колона (B: B) и извежда TRUE за непразни клетки и FALSE за празни клетки.
| 1 | = B: B "" |
Функцията ROW произвежда номера на ред на дадена клетка. Ако не му дадем конкретен клетъчен вход, той дава номера на реда на клетката, в която се намира.
| 1 | = ROW () |
Умножаването на всеки номер на ред със съответните стойности TRUE (= 1) или FALSE (= 0), връща номера на реда за попълнена клетка и нула за празна клетка.
| 1 | = E3*F3 |
Функцията MAX дава максимум от набор от числа. В този пример, тъй като всички празни клетки произвеждат нулева стойност, максимумът е най -високият номер на ред.
| 1 | = MAX (G3: G12) |
Комбинирането на тези стъпки ни дава оригиналната формула:
| 1 | = MAX ((B: B "")*(ROW (B: B))) |
Моля, обърнете внимание, че това е формула за масив, така че ако използвате Excel 2022 или по -ранна версия, трябва да натиснете CTRL + SHIFT + ENTER, за да я задействате.
Метод за текстов диапазон
Ако вашият (непрекъснат) диапазон съдържа само текстови стойности и празни клетки, можете да използвате по-малко сложна формула, съдържаща функциите REPT и MATCH:
| 1 | = MATCH (REPT ("z", 50), B: B) |
Нека да видим как работи тази формула.
Функция REPT
Функцията REPT повтаря текстов низ определен брой пъти. За този пример можем да го използваме за създаване на текстов низ, който би бил последният във всеки сортиран по азбучен ред списък. Ако повторим „z“ 50 пъти, това би трябвало да работи за почти всяка колона с текстови данни; няма да има записи преди „zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz“ по азбучен ред.
| 1 | = REPT ("z", 50) |
MATCH функция
Функцията MATCH намира дадена стойност за търсене в масив.
Извършваме търсене в цялата колона с данни за нашия текстов низ от 50 z. Пропускането на въвеждане на тип съвпадение във функцията MATCH му казва да намери приблизително, а не точно съвпадение.
| 1 | = MATCH (REPT ("z", 50), B: B) |
Функцията MATCH търси в колона B и търси нашия текстов низ от 50 „z“ s. Тъй като не го намира, формулата връща позицията на последната непразна клетка. Тази клетка съдържа последната стойност в масива за търсене, която е по -малка (или равна) на стойността за търсене.
Имайте предвид, че тази формула работи само когато вашият диапазон съдържа изключително текст и празни клетки (или поне стойността на последната клетка е нецифрена).
Намерете последния ред с данни в Google Таблици
Тези формули работят абсолютно същите в Google Sheets, както и в Excel.
