Знайдемо номер рядка останньої заповненої комірки в стовпці і списку. За номером виявимо і саме значення.
Розглянемо діапазон значень, в який регулярно заносяться нові дані.
Діапазон без пропусків і починаючи з першого рядка
У разі, якщо в стовпці значення вводяться, починаючи з першого рядка і без пропусків, то визначити номер рядка останньої заповненої комірки можна формулою:
= СЧЁТЗ (A: A))
Формула працює для числових і текстових діапазонів (див. Файл прикладу)
Значення з останньої заповненої комірки в стовпці виведемо за допомогою функції ІНДЕКС ():
= ІНДЕКС (A: A; СЧЁТЗ (A: A))
Посилання на цілі стовпці і рядки досить ресурсомісткі і можуть уповільнити перерахунок листа. Якщо є впевненість, що при введенні значень користувач не вийде за межі певного діапазону, то краще вказати посилання на діапазон, а не на стовпець. У цьому випадку формула буде виглядати так:
= ІНДЕКС (A1: A20; СЧЁТЗ (A1: A20))
Діапазон без пропусків в будь-якому місці аркуша
Якщо список, в який вводяться значення розташований в діапазоні E8: E30 (тобто не починається з першого рядка), то формулу для визначення номера рядка останньої заповненої комірки можна записати в такий спосіб:
= СЧЁТЗ (E9: E30) + СТРОКА (E8)
Формула СТРОКА (E8) повертає номер рядка заголовка списку. Значення з останньої заповненої комірки списку виведемо за допомогою функції ІНДЕКС ():
= ІНДЕКС (E9: E30; СЧЁТЗ (E9: E30))
Діапазон з пропусками (числа)
У разі наявності пропусків (порожніх рядків) в стовпці, функція СЧЕТЗ () буде повертати неправильний (зменшений) номер рядка: воно і зрозуміло, адже ця функція підраховує тільки значення і не враховує порожні клітинки.
Якщо діапазон заповнюється числовими значеннями, то для визначення номера рядка останньої заповненої комірки можна використовувати формулу = ПОИСКПОЗ (1E + 306; A: A; 1). Порожні осередки і текстові значення ігноруються.
Так як в якості проглядається масиву вказано цілий стовпець (A: A), то функція ПОИСКПОЗ () поверне номер останнього заповненого рядка. Функція ПОИСКПОЗ () (з третім параметром = 1) знаходить позицію найбільшого значення, яке менше або дорівнює значенню першого аргументу (1E + 306). Правда, для цього потрібно, щоб масив був відсортований по зростанню. Якщо він не впорядкований, то ця функція повертає позицію останнього заповненого рядка стовпця, тобто то, що нам потрібно.
Щоб повернути значення в останній заповненої комірки списку, розташованого в діапазоні A2: A20. можна використовувати формулу:
= ІНДЕКС (A2: A20; ПОИСКПОЗ (1E + 306; A2: A20; 1))
Діапазон з пропусками (текст)
У разі необхідності визначення номера рядка останнього текстового значення (також при наявності пропусків), формулу потрібно переробити:
= ПОИСКПОЗ ( "*"; $ A: $ A; -1)
Порожні осередки, числа і текстове значення Порожній текст ( "") ігноруються.
Діапазон з пропусками (текст і числа)
Якщо стовпець містить і текстові та числові значення. то для визначення номера рядка останньої заповненої комірки можна запропонувати універсальне рішення:
= МАКС (ЕСЛІОШІБКА (ПОИСКПОЗ ( "*"; $ A: $ A; -1); 0);
ЕСЛІОШІБКА (ПОИСКПОЗ (1E + 306; $ A: $ A; 1); 0))
Функція ЕСЛІОШІБКА () потрібна для придушення помилки виникає, якщо стовпець A містить тільки текстові або тільки числові значення.
Іншим універсальним рішенням є формула масиву:
= МАКС (СТРОКА (A1: A20) * (A1: A20<>""))
Після введення формули масиву треба натиснути CTRL + SHIFT + ENTER. Передбачається, що значення вводяться в діапазон A1: A20. Краще поставити фіксований діапазон для пошуку, тому що використання в формулах масиву посилань на цілі рядки або стовпці є досить ресурсномісткою завданням.
Значення з останньої заповненої комірки, в цьому випадку, виведемо за допомогою функції ДВССИЛ ():
= ДВССИЛ ( "A" МАКС (СТРОКА (A1: A20) * (A1: A20<>"")))
Як завжди, після введення формули масиву треба натиснути CTRL + SHIFT + ENTER замість ENTER.
ПОРАДА:
Як видно, наявність пропусків в діапазоні істотно ускладнює підрахунок. Тому має сенс при заповненні та проектуванні таблиць дотримуватися правил наведених в статті Поради з побудови таблиць.
пов'язані статті
Прочитайте інші статті, які вирішують схожі завдання в MS Excel. Це дозволить Вам вирішувати широкий клас подібних задач.