Як отримати останнє значення у стовпці таблиці Excel

За певний період ведеться регістр кількості проданого товару в магазині. Необхідно регулярно відстежувати останній виданий із магазину товар. Для цього необхідно відобразити останній запис у стовпці найменування продуктів. Щоб просто подивитися на останнє значення стовпця, достатньо перемістити курсор на будь-яку його комірку та натиснути комбінацію гарячих клавіш CTRL + стрілка вниз (↓). Але найчастіше користувачеві доводиться з останнім значенням стовпця виконувати різні обчислювальні операції Excel. Тому краще його отримати як значення для окремого осередку.

Пошук останнього значення у стовпці Excel

Схематичний регістр товарів, виданих з магазину:

Щоб мати можливість постійно спостерігати, який товар зареєстрований останнім, в окремий осередок E1 введемо формулу:

Результат виконання формули для отримання останнього значення:

Розбір принципу дії формули для пошуку останнього значення у стовпці:

Головну роль бере на себе функція =ІНДЕКС(), яка повинна повертати вміст комірки таблиці, де перетинаються певний рядок і стовпець. Як перший аргумент функції ІНДЕКС виступає незмінна константа, саме посилання цілий стовпець (B:B). У другому аргументі знаходиться номер рядка з останнім заповненим значенням стовпця B. Щоб дізнатися цей номер рядка використовується функція РАХУНОК, яка повертає кількість непустих осередків у діапазоні. Відповідно це число дорівнює номеру останнього непустого рядка в стовпці B і використовується як другий аргумент для функції ІНДЕКС, яка відразу повертає останнє значення стовпця B в окремому осередку E1.

Увага! Всі записи в стовпці B повинні бути нерозривними (без порожніх осередків до останнього значення).

Варто зазначити, що дана формула є динамічною. При додаванні нових записів у стовпець B результат у осередку E1 автоматично оновлюватиметься.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

Пошук останнього значення останнього рядка у стовпці Excel

При складанні формул в Excel часто виникає необхідність знайти останній рядок або отримати останнє значення у стовпці таблиці даних. Тут слід враховувати кілька умов, поставлених перед пошуком: чи буде список значень у стовпці нерозривним чи міститиме порожні осередки? Які значення: текст, числа? Від цих чинників залежить тип використовуваних формул.

Як знайти останній заповнений рядок у стовпці таблиці Excel

Нижче на малюнку представлено невідсортований перелік фактур. Допустимо нам необхідно знайти останній рядок із фактурою у списку номерів фактур. Простим способом пошуку останньої позиції у стовпці є використання функції ІНДЕКС та підрахунок усіх позицій списку з метою визначення номера останнього рядка.

Функція ІНДЕКС використана з одним стовпцем вимагає лише вказати один аргумент із номером рядка. Третій необов'язковий для заповнення аргумент у цій ситуації не використовується. Функція РАХУНОК використовується з метою підрахування непустих осередків у стовпці B. Її підсумковий результат обчислення слід збільшити на число +1, так як у першому рядку порожній осередок. Функція ІНДЕКС у цьому прикладі повертає 12-й рядок у стовпці B.

Функція РАХУНОК підраховує комірки, що містять значення: числа, текстові рядки, дати і всі будь-які інші значення за винятком порожніх осередків. Якщо ваші дані містять порожні комірки, які розривають цілісність списків, тоді ця формула не повертатиме правильних результатів обчислення.

Пошук останнього числа у стовпці з порожніми осередками Excel

Функції ІНДЕКС і РАХУНКІВ чудово використовуються для пошуку значень у випадку, коли діапазон вихідних даних не містить порожніх осередків і є нерозривним. Якщо ж вихідний діапазон комірок містить порожні комірки, а шуканими значеннями є числа, можна скористатися функцією ПЕРЕГЛЯД з дуже великим числовим значенням в аргументах. Ця техніка застосовується за допомогою складання наступної формули:

Шукане значення в даній формулі (одиниця з 308 знаками) – це найбільше доступне в Excel. Так як функція ПЕРЕГЛЯД не мала шансів знайти більшого значення, ніж шукане, вона припинила своє обчислення на останньому знайденому числовому значенні, яку і повернула в результат.

Що означає число з літерою E в Excel?

Число таке, як, наприклад, 9,99E+307 записано в експоненційному форматі. Число перед літерою E має одну цифру 0-9 і лише дві цифри після коми. Число після літери E означає кількість розрядів, на які слід змістити кому (307 у даному прикладі), щоб отримати числове значення, записане традиційним способом. Плюс означає, що кому слід зміщувати вправо, а мінус - вліво. Наприклад, запис 4,32E-02 означає число, записане в десятковому дробі 0,0432.

Функція ПЕРЕГЛЯД має перевагу перед іншими функціями в тому, що вона повертає останнє число навіть тоді, коли осередки в діапазоні, що переглядається, можуть бути не тільки порожніми, але і містити текстові рядки, коди помилок або дати.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади