Excel последнее значение в строке
За определенный период времени ведется регистр количества проданного продукта в магазине. Нужно часто выслеживать крайний выданный из магазина продукт. Для этого необходимо показать последнюю запись в столбце наименования продуктов. Чтоб просто поглядеть на последнее значение столбца, довольно переместить курсор на всякую его ячейку и надавить комбинацию жарких кнопок CTRL + стрелка в низ (↓). Но почаще всего юзеру приходится с крайним значением столбца делать разные вычислительные операции в Excel. Потому лучше его получить в качестве значения для отдельной ячейки.
Поиск крайнего значения в столбце Excel
Схематический регистр продуктов, выданных с магазина:
Чтоб иметь возможность повсевременно следить, какой продукт зарегистрирован крайним, в отдельную ячейку E1 введем формулу:
Итог выполнения формулы для получения крайнего значения:
Разбор принципа деяния формулы для поиска крайнего значения в столбце:
Главную роль берет на себя функция =ИНДЕКС(), которая обязана возвращать содержимое ячейки таблицы где пересекаются определенная строчка и столбец. В качестве первого аргумента функции ИНДЕКС выступает неизменяемая константа, а конкретно ссылка на целый столбец (B:B). Во 2-м аргументе находится номер строчки с крайним заполненным значением столбца B. Чтоб выяснить этот номер строчки употребляется функция СЧЁТЗ, которая возвращает количество непустых ячеек в спектре. Соответственно это число равно номеру крайней непустой строчки в столбце B и употребляется как 2-ой аргумент для функции ИНДЕКС, которая сходу возвращает последнее значение столбца B в отдельной ячейке E1.
Внимание! Все записи в столбце B должны быть неразрывны (без пустых ячеек до крайнего значения).
Необходимо отметить что данная формула является динамической. При добавлении новейших записей в столбец B итог в ячейке E1 будет автоматом обновляться.
Описание функции
Функция =ПОСЛЕДНЕЕВСТРОКЕ(ЯЧЕЙКА) имеет один аргумент.
- ЯЧЕЙКА — ссылка на всякую ячейку из строчки, в которой нужно отыскать последнее непустое значение.
Пример
Определение значения крайней непустой ячейки в строке.
Найдем номер строчки крайней заполненной ячейки в столбце и перечне. По номеру строчки найдем и само значение.
Разглядим спектр значений, в который часто заносятся новейшие данные.
Спектр без пропусков и начиная с первой строчки
В случае, если в столбце значения вводятся, начиная с первой строчки и без пропусков, то найти номер строчки крайней заполненной ячейки можно формулой:
=СЧЁТЗ(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.
СОВЕТ:
Как видно, наличие пропусков в спектре значительно усложняет подсчет. Потому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц.
Эксель последнее значение в столбце
Разглядим спектр значений, в который часто заносятся новейшие данные.
Спектр без пропусков и начиная с первой строчки
В случае, если в столбце значения вводятся, начиная с первой строчки и без пропусков, то найти номер строчки крайней заполненной ячейки можно формулой:
=СЧЁТЗ(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.
СОВЕТ:
Как видно, наличие пропусков в спектре значительно усложняет подсчет. Потому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц.
За определенный период времени ведется регистр количества проданного продукта в магазине. Нужно часто выслеживать крайний выданный из магазина продукт. Для этого необходимо показать последнюю запись в столбце наименования продуктов. Чтоб просто поглядеть на последнее значение столбца, довольно переместить курсор на всякую его ячейку и надавить комбинацию жарких кнопок CTRL + стрелка в низ (↓). Но почаще всего юзеру приходится с крайним значением столбца делать разные вычислительные операции в Excel. Потому лучше его получить в качестве значения для отдельной ячейки.
Поиск крайнего значения в столбце Excel
Схематический регистр продуктов, выданных с магазина:
Чтоб иметь возможность повсевременно следить, какой продукт зарегистрирован крайним, в отдельную ячейку E1 введем формулу:
Итог выполнения формулы для получения крайнего значения:
Разбор принципа деяния формулы для поиска крайнего значения в столбце:
Главную роль берет на себя функция =ИНДЕКС(), которая обязана возвращать содержимое ячейки таблицы где пересекаются определенная строчка и столбец. В качестве первого аргумента функции ИНДЕКС выступает неизменяемая константа, а конкретно ссылка на целый столбец (B:B). Во 2-м аргументе находится номер строчки с крайним заполненным значением столбца B. Чтоб выяснить этот номер строчки употребляется функция СЧЁТЗ, которая возвращает количество непустых ячеек в спектре. Соответственно это число равно номеру крайней непустой строчки в столбце B и употребляется как 2-ой аргумент для функции ИНДЕКС, которая сходу возвращает последнее значение столбца B в отдельной ячейке E1.
Внимание! Все записи в столбце B должны быть неразрывны (без пустых ячеек до крайнего значения).
Необходимо отметить что данная формула является динамической. При добавлении новейших записей в столбец B итог в ячейке E1 будет автоматом обновляться.
На практике нередко возникает необходимость стремительно отыскать значение крайней (последней) непустой ячейки в строке либо столбце таблицы. Представим, для примера, что у нас есть вот таковая таблица с данными продаж по нескольким филиалам:
Задачка: отыскать значение продаж в крайнем месяце по любому филиалу, т.е. для Москвы это будет 78, для Питера – 41 и т.д.
Если б в нашей таблице не было пустых ячеек, то путь к решению был бы очевиден – можно было бы посчитать количество заполненных ячеек в каждой строке и брать позже ячейку с сиим номером. Но филиалы работают неравномерно: Москва простаивала в марте и августе, филиал в Тюмени открылся лишь с апреля и т.д., потому таковой метод не подойдет.
Всепригодным решением будет внедрение функции ПРОСМОТР (LOOKUP) :
У данной функции хитрецкая логика:
- Она по очереди (слева-направо) перебирает непустые ячейки в спектре (B2:M2) и ассоциирует каждую из их с разыскиваемым значением (9999999).
- Если значение очередной проверяемой ячейки совпало с разыскиваемым, то функция останавливает просмотр и выводит содержимое ячейки.
- Если четкого совпадения нет и еще одно значение меньше искомого, то функция перебегает к последующей ячейке в строке.
Просто сообразить, что если в качестве искомого значения задать довольно огромное число, то функция пройдет по всей строке и, в итоге, выдаст содержимое крайней испытанной ячейки. Для компактности, можно указать разыскиваемое число в экспоненциальном формате, к примеру 1E+11 (1*10 11 либо 100 млрд).
Если в таблице не числа, а текст, то мысль остается той же, но "весьма огромное число" необходимо поменять на "весьма большенный текст":
Применительно к тексту, понятие "большенный" значит код знака. В любом шрифте знаки идут в последующем порядке возрастания кодов:
- латиница строчные (A-Z)
- латиница строчные (a-z)
- кириллица строчные (А-Я)
- кириллица строчные (а-я)
Потому строчная "я" оказывается буковкой с большим кодом и слово из нескольких попорядку "яяяяя" будет, условно, "весьма огромным словом" – заранее "огромным", чем хоть какое текстовое значение из нашей таблицы.
Вот так. Не совершенно явное, но прекрасное и малогабаритное решение. Для поиска крайней непустой ячейки в столбцах работает тоже "на ура".