Последнее значение в столбце Excel не равно 0

Обновлено: 20.11.2024

Вы можете использовать функцию ПРОСМОТР, чтобы найти последнюю непустую ячейку в столбце.

Давайте рассмотрим эту формулу.

Часть формулы B:B<>”” возвращает массив, содержащий значения True и False: , проверка каждой ячейки в столбце B пуста (FALSE).

Эти логические значения преобразуются в 0 или 1 и используются для деления 1.

Это искомый_вектор для функции ПРОСМОТР. В нашем случае искомое_значение равно 2, но наибольшее значение в искомом_векторе равно 1, поэтому функция ПРОСМОТР будет соответствовать последнему 1 в массиве и вернет соответствующее значение в результирующем_векторе.

Если вы уверены, что в вашем столбце есть только числовые значения, ваши данные начинаются с строки 1, а диапазон данных непрерывен, вы можете использовать несколько более простую формулу с функциями ИНДЕКС и СЧЁТ.

Функция COUNT возвращает количество ячеек, заполненных данными в непрерывном диапазоне (4), а функция INDEX, таким образом, возвращает значение ячейки в этой соответствующей строке (4th).

Чтобы избежать возможных ошибок, когда ваш диапазон данных содержит смесь числовых и нечисловых значений или даже несколько пустых ячеек, вы можете использовать функцию ПРОСМОТР вместе с функциями ЕПУСТО и НЕ.

Если вам нужно вернуть номер строки с последней записью, вы можете изменить формулу, использованную в нашем первом примере, вместе с функцией ROW в вашем result_vector.

Чтобы найти значение последней непустой ячейки в строке или столбце, даже если данные могут содержать пустые ячейки, вы можете использовать функцию ПРОСМОТР с операцией массива. Формула в F6:

Результатом является последнее значение в столбце B. Данные в B:B могут содержать пустые ячейки (т. е. пробелы) и не нуждаются в сортировке.

Примечание. Это формула массива. Но поскольку ПРОСМОТР может обрабатывать операции с массивами изначально, формулу не нужно вводить с помощью клавиш Control + Shift + Enter даже в более старых версиях Excel.

В этом примере цель состоит в том, чтобы получить последнее значение в столбце B, даже если данные могут содержать пустые ячейки. Это одна из тех головоломок, которые часто возникают в Excel, потому что ответ не очевиден. Один из способов сделать это — использовать функцию ПРОСМОТР, которая изначально может обрабатывать операции с массивами и всегда предполагает приблизительное совпадение. Это делает формулу удивительно простой и компактной:

Действуя изнутри наружу, мы используем логическое выражение для проверки наличия пустых ячеек в столбце B:

Диапазон B:B представляет собой полную ссылку на каждую ячейку в столбце B. Основное преимущество полной ссылки на столбец заключается в том, что она остается неизменной при удалении или добавлении строк.

Примечание. Excel, как правило, хорошо оценивает формулы, в которых используются полные ссылки на столбцы, но вам следует позаботиться об управлении используемым диапазоном. В этом случае, поскольку мы создаем «виртуальный столбец» из более чем 1 миллиона строк с выражением B:B<>"", используемый диапазон, вероятно, не имеет большого значения, но формула определенно намного медленнее, чем та же формула. с фиксированными диапазонами. Если вы заметили проблемы с производительностью, попробуйте ограничить диапазон. Например: =ПРОСМОТР(2,1/(B1:B100<>""),B1:B100)

Логический оператор <> означает не равно, а "" означает пустую строку, поэтому это выражение означает, что столбец B не пуст. Результатом является массив значений TRUE и FALSE, где TRUE представляет ячейки, которые не пусты, а FALSE представляет ячейки, которые пусты. Этот массив начинается так:

Далее мы делим число 1 на массив. Математическая операция автоматически приводит TRUE к 1, а FALSE к 0, поэтому мы имеем:

Этот массив становится аргументом lookup_array в функции ПРОСМОТР. Обратите внимание, что единицы обозначают непустые ячейки, а ошибки — пустые ячейки.

lookup_value указывается числом 2.Мы используем 2 в качестве значения поиска, чтобы заставить ПРОСМОТР сканировать до конца данных. ПРОСМОТР автоматически игнорирует ошибки, поэтому ПРОСМОТР просматривает 1 в поисках 2, которые никогда не будут найдены. Когда он достигает конца массива, он "отступает" к последнему 1, что соответствует последней непустой ячейке.

Наконец, ПРОСМОТР возвращает соответствующее значение в result_vector (заданное как B:B), поэтому окончательный результат: 30 июня 2020 г.

Ключом к пониманию этой формулы является признание того, что искомое_значение, равное 2, преднамеренно больше, чем любые значения, которые появятся в искомом_векторе. Когда искомое_значение не может быть найдено, ПРОСМОТР будет соответствовать следующему наименьшему значению, которое не является ошибкой: последнему 1 в массиве. Это работает, потому что LOOKUP предполагает, что значения в lookup_vector сортируются в порядке возрастания, и всегда выполняет приблизительное совпадение. Если функция ПРОСМОТР не может найти совпадение, она будет соответствовать следующему наименьшему значению.

Получить соответствующее значение

Вы можете легко изменить формулу поиска, чтобы она возвращала соответствующее значение. Например, чтобы получить цену, связанную с последним значением в столбце B, используйте формулу F7:

Единственное отличие состоит в том, что аргумент result_vector указан как C:C.

Работа с ошибками

Если последняя непустая ячейка содержит ошибку, она будет проигнорирована. Если вы хотите вернуть ошибку, которая появляется последней в диапазоне, вы можете настроить формулу для использования функций IПУСТО и НЕ следующим образом:

Последнее числовое значение

Чтобы получить последнее числовое значение, вы можете добавить функцию ISNUMBER следующим образом:

Последнее непустое, ненулевое значение

Чтобы убедиться, что последнее значение не является пустым и не нулевым, вы можете адаптировать формулу с помощью логической логики следующим образом:

Если вы заметили проблемы с производительностью, ограничьте диапазон (например, используйте B1:B100, B1:B1000 вместо B:B).

Позиция последнего значения

Чтобы получить номер строки последнего значения, можно использовать следующую формулу:

Мы используем функцию ROW, чтобы передать номера строк для столбца B функции LOOKUP в качестве result_vector, чтобы получить номер строки для последнего совпадения.

Возможно, нам потребуется найти последнюю запись, последнюю покупку или последний заказ больше нуля при использовании MS Excel для различных целей. Есть много способов сделать это, но сегодня я приведу две простые формулы, которые помогут вам найти последнее значение в столбце, которое больше нуля в Excel.

Загрузить книгу с практиками

Вы можете загрузить бесплатный шаблон Excel, нажав кнопку загрузки ниже, и попрактиковаться самостоятельно.

2 формулы Excel для поиска последнего значения в столбце, которое больше нуля

Итак, давайте сначала представим наш образец набора данных. Здесь я поместил в свой набор данных количество некоторых фруктов и их цены за единицу. Теперь мы применим две формулы, чтобы найти последнее значение в столбце C, которое больше нуля.

Мы можем использовать функцию ПРОСМОТР в Excel, чтобы просмотреть один столбец или строку, чтобы найти определенное значение в том же месте во втором столбце или строке. Мы часто используем эту функцию, когда в книге есть несколько листов или большой объем данных на листе.
Мы будем использовать его здесь, в нашем первом методе, чтобы найти последнюю ненулевую величину в столбце C.
Итак, давайте посмотрим, как это сделать.

Шаги:

⏩ Активируйте ячейку D14.

⏩ Затем введите следующую формулу:

⏩ Теперь просто нажмите кнопку Enter, чтобы получить результат.

Теперь посмотрите, мы нашли наше последнее количество, равное 10.

Похожие чтения:

Как найти несколько значений в Excel (8 быстрых способов)

Найти справа в Excel (6 методов)

Как найти символ в строке Excel (8 простых способов)

Excel найти последний столбец с данными (4 быстрых способа)

2. Функции ПРОСМОТР + ЕСЛИ для поиска последнего ненулевого положительного значения в столбце

В этом методе мы будем выполнять задачу по-другому. Давайте объединим две функции — функцию ПРОСМОТР и функцию ЕСЛИ и найдем последнее значение в столбце, которое больше нуля. Функция ЕСЛИ — это логическая функция, возвращающая одно значение, если условие истинно, и другое значение, если оно ложно.

Шаги:

⏩ Активируйте ячейку D14, нажав здесь.

⏩ Затем запишите данную формулу-

⏩ Нажмите кнопку Enter, чтобы получить результат.

Примечание. Возможно, вам придется нажать Ctrl+Shift+Enter вместо кнопки Enter в старых версиях Excel, если вы получите неверный результат.

Теперь вы увидите, что последнее значение определяется объединенными функциями.

⏬ Как работает формула?

⏩ ЕСЛИ($C$5:$C$12<>0,$C$5:$C$12)
Функция ЕСЛИ проверит массив C5:C12 на наличие нулевого значения или нет. Если он найдет ненулевое значение, он покажет это значение, а если найдет ноль, то покажет FALSE. Вывод будет возвращен как-

⏩ ПРОСМОТР(1000000,ЕСЛИ($C$5:$C$12<>0,$C$5:$C$12))
Наконец, функция ПРОСМОТР будет искать большое число, которое мы предоставили через вывод функции ЕСЛИ. Мы использовали это большое число намеренно, потому что знаем, что он не найдет это число, поэтому, в конце концов, он выполнит приблизительное совпадение и покажет последнее ненулевое значение. Это вернет как-
10

Заключение

Я надеюсь, что все описанные выше методы будут достаточно хороши, чтобы найти последнее значение в столбце больше нуля в Excel. Не стесняйтесь задавать любые вопросы в разделе комментариев и, пожалуйста, дайте мне обратную связь.

В этой статье демонстрируются формулы, возвращающие последнее значение в заданном диапазоне ячеек или столбце. На изображении выше показана формула в ячейке D3, которая извлекает последнее значение из диапазона ячеек B3:B12.

Диапазон ячеек B3:B12 заполнен значениями и пустыми ячейками в случайном порядке, это показывает, что формула нормально работает с пустыми ячейками.

Оглавление

1. Извлечь последнее значение в заданном столбце

Формула в ячейке D3 позволяет получить последнее значение в столбце B. Она отлично работает с пустыми ячейками в вашем списке.

Формула в ячейке D3:

Эта формула довольно интенсивно использует ЦП, поскольку обрабатывает все ячейки в столбце B, а в одном столбце Excel 2007 и более поздних версий более миллиона ячеек.

В этой статье демонстрируется формула, которая возвращает последнее совпадающее значение на основе заданного условия. На изображении выше показано […]

Вы можете изменить ссылки на ячейки, если знаете, что у вас будет меньший список, эту формулу проще обрабатывать в Excel:

1.1 Объяснение формулы

Затем он делит 1 на массив, создавая ошибки для всех пустых ячеек.

Функция ПРОСМОТР игнорирует ошибки и пытается найти совпадение. Если каждое совпадение равно 1, а значение поиска равно 2, функция ПРОСМОТР возвращает последнее значение в диапазоне ячеек. Обычно список должен быть отсортирован по возрастанию, чтобы функция ПРОСМОТР работала, однако, поскольку каждое значение равно 1, нет необходимости сортировать список.

Я буду использовать следующую формулу, потому что ее будет легче продемонстрировать:

Шаг 1. Проверьте, не равны ли ячейки в столбце B нулю

Знаки меньше и больше являются логическими операторами, они проверяют, не пусты ли значения ячеек в столбце B. Результатом является массив, содержащий значения TRUE или FALSE.

Шаг 2. Разделите 1 на массив

Шаг 3. Возвращаемое значение

и возвращает XH в ячейке D3.

Находит значение в отсортированном диапазоне ячеек и возвращает значение в той же строке.

1.2 Что насчет ошибок?

Я добавил несколько ошибок в столбец B, см. изображение выше.

Я работаю с Excel 2016, и ошибки в столбце B не представляют проблемы.

2. Вернуть гиперссылку на последнее значение в столбце

Формула в ячейке D3, показанная на изображении выше, создает гиперссылку на последнюю ячейку в столбце B. Нажмите левую кнопку мыши на гиперссылке, чтобы перейти к последнему значению в столбце B.

Формула в ячейке D3:

=ГИПЕРССЫЛКА(MID(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))&"!$B $"&ПРОСМОТР(2, 1/(B:B<>""), СТРОКА(B:B)), ПРОСМОТР(2, 1/(B:B<>""), B:B))

2.1 Объяснение формулы

Шаг 1. Получение пути и имени книги

Функция ЯЧЕЙКА получает информацию о форматировании, расположении или содержимом ячейки.

возвращает C:\temp\[Найти последнее значение в listv2.xlsx]Гиперссылка

Шаг 2. Найдите местоположение символа [

Нам не нужен путь, чтобы создать рабочую гиперссылку. Этот шаг возвращает символ первой строки, которая нам нужна.

Функция ПОИСК возвращает число, представляющее позицию символа, в которой найдена определенная текстовая строка, читаемая слева направо.

ПОИСК("[", ЯЧЕЙКА("имя файла", A1))

ПОИСК("[", "C:\temp\[Найти последнее значение в listv2.xlsx] Гиперссылка")

Шаг 3. Извлечение книги и названия листа

Функция MID возвращает подстроку из строки на основе начальной позиции и количества символов, которые вы хотите извлечь.

MID(текст, start_num, num_chars)

СРЕДН(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))

СРЕДН(ЯЧЕЙКА("имя файла", A1), 9, ДЛСТР(ЯЧЕЙКА("имя файла", A1)))

Функция ДЛСТР вычисляет количество символов в заданной строке.

СРЕДН(ЯЧЕЙКА("имя файла", A1), 9, ДЛСТР(ЯЧЕЙКА("имя файла", A1)))

MID(ЯЧЕЙКА("имя файла", A1), 9, 49)

"[Найти последнее значение в listv2.xlsx] Гиперссылка"

Шаг 4. Объедините имя книги, имя листа и столбец

Символ амперсанда & позволяет объединять строки в формуле Excel.

СРЕДН(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))&"!$B$"< /p>

"[Найти последнее значение в listv2.xlsx]Гиперссылка"&"!$B$"

и возвращает "[Найти последнее значение в listv2.xlsx]Гиперссылка!$B$".

Шаг 5. Вычисление номера строки последнего значения в столбце

Функция СТРОКА позволяет вычислить номер строки последнего значения.

СРЕДН(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))&"!$B$"&ПРОСМОТР (2, 1/(B:B<>""), СТРОКА(B:B))

"[Найти последнее значение в listv2.xlsx]Гиперссылка!$B$"&ПРОСМОТР(2, 1/(B:B<>""), ROW(B:B))

"[Найти последнее значение в listv2.xlsx]Гиперссылка!$B$"&11

и возвращает "[Найти последнее значение в listv2.xlsx]Гиперссылка!$B$11"

Шаг 6. Возврат последнего значения

Шаг 7. Создайте гиперссылку

Функция ГИПЕРССЫЛКА позволяет создать в ячейке ссылку, указывающую на что-то еще, например на файл, рабочую книгу, ячейку, диапазон ячеек или веб-страницу.

ГИПЕРССЫЛКА(СРЕДН(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))&"!$B$ "&ПРОСМОТР(2, 1/(B:B<>""), СТРОКА(B:B)), ПРОСМОТР(2, 1/(B:B<>""), B:B))

HYPERLINK("[Найти последнее значение в listv2.xlsx]Hyperlink!$B$"&11, "XH")

и возвращает гиперссылку на ячейку B11.

3. Возвращает номер строки последнего значения в столбце

Формула в ячейке D3 возвращает число, представляющее номер строки последнего значения в столбце B.

Формула в ячейке D3:

4. Возвращает смежное значение

В этом наборе данных есть два столбца B и C. Формула в ячейке E3 возвращает соответствующее значение последнего значения в столбце B из столбца C.

Формула в ячейке E3:

Формула возвращает соседнее значение последнего значения. На самом деле, она не обязательно должна быть смежной, вы можете изменить ссылку на ячейку (C:C), если ее начальная и конечная точки совпадают с первой ссылкой на ячейку (B:B).

5. Найдите последнюю непустую ячейку с помощью горячих клавиш

Следующие шаги описывают, как выбрать последнюю непустую ячейку в столбце B с помощью клавиш клавиатуры.

Нажмите левой кнопкой мыши на ячейку A1, чтобы выбрать ее.
Нажмите и удерживайте клавишу CTRL.
Один раз нажмите клавишу со стрелкой вниз. Это приведет вас к последней ячейке в столбце A, если все ячейки в столбце A пусты.
Отпустите все клавиши. Нажмите клавишу со стрелкой вправо, чтобы перейти к последней ячейке в столбце B.
Нажмите и удерживайте клавишу CTRL.
Один раз нажмите клавишу со стрелкой вверх.Это приведет вас к последней непустой ячейке в столбце B. См. изображение выше.

Получить файл Excel

Найти последнее значение в спискеv2.xlsx

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.

Статьи по теме

Во-первых, позвольте мне объяснить разницу между уникальными значениями и уникальными уникальными значениями, важно, чтобы вы знали разницу […]

В этой статье я продемонстрирую четыре различные формулы, которые позволяют вам искать значение, которое нужно найти […]

В этой статье показана формула, которая выполняет обратный поиск и возвращает соответствующее значение на основе последнего совпадения […]

Содержание Найти ближайшее значение Как ввести формулу массива Найти ближайшее значение - Excel 365 Найти ближайшее […]

Вопрос. Как мне искать данные в таблице перекрестных ссылок. У меня есть строка заголовка (т.е. 24) […]

В этой статье демонстрируется формула, позволяющая выполнять поиск в наборе данных с использованием любого количества условий, однако […]

Читайте также: