Завантажте зразок робочої книги
Цей підручник навчить вас шукати останнє значення у стовпці чи рядку в Excel.
Останнє значення у стовпці
Ви можете скористатися функцією LOOKUP, щоб знайти останню непусту клітинку у стовпці.
| 1 | = ПОГЛЯД (2,1/(B: B ""), B: B) |
Пройдемося по цій формулі.
Частина формули B: B ”” повертає масив, що містить істинні та хибні значення: {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 | = ПОГЛЯД (2,1/(НЕ (ISBLANK (B: B))), B: B) |
Функція ISBLANK повертає масив, що містить істинні та хибні значення, що відповідають одиницям 1 та 0. Функція NOT змінює True (тобто 1) на False і False (тобто 0) на True. Якщо ми перевернемо цей результуючий масив (при поділі 1 на цей масив), ми отримаємо масив результатів, що знову містить #DIV/0! помилки та 1, які можна використовувати як масив пошуку (lookup_vector) у нашій функції LOOKUP. Тоді функціонал функції LOOKUP такий самий, як і в нашому першому прикладі: він повертає значення вектора результату в позиції останнього 1 у масиві пошуку.
Коли вам потрібно повернути номер рядка з останнім записом, ви можете змінити формулу, яка використовується у нашому першому прикладі, разом із функцією ROW у вашому result_vector.
| 1 | = ПОГЛЯД (2,1/(B: B ""), РЯД (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_ встановлено на 1 (менше або рівне lookup_value), функція MATCH поверне значення остання "перевірена" позиція комірки в масиві, тобто кількість клітинок, заповнених даними в діапазоні B2: XFD2 (XFD - це останній стовпець у новіших версіях Excel).
Функція OFFSET
Потім ми використовуємо функцію OFFSET, щоб отримати значення цієї комірки, позицію якої повернула функція MATCH.
| 1 | = ЗМІЩЕННЯ (B2,0, C4-1) |
