Excel поиск последнего значения в столбце

Последняя заполненная ячейка в EXCEL

Найдем номер строки последней заполненной ячейки в столбце и списке. По номеру строки найдем и само значение.

Рассмотрим диапазон значений, в который регулярно заносятся новые данные.

Диапазон без пропусков и начиная с первой строки

В случае, если в столбце значения вводятся, начиная с первой строки и без пропусков, то определить номер строки последней заполненной ячейки можно формулой: =СЧЁТЗ(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 .

СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц .

Источник: excel2.ru

Excel поиск последнего значения в столбце

Информация о сайте

Инструменты и настройки

Excel Windows
и
Excel Macintosh

Вопросы и решения

Работа и общение

Работа форума и сайта

Функции листа Excel

= Мир MS Excel/Статьи об Excel

  • 1
  • 2
  • 3
  • Приёмы работы с книгами, листами, диапазонами, ячейками [6]
    Приёмы работы с формулами [13]
    Настройки Excel [3]
    Инструменты Excel [4]
    Интеграция Excel с другими приложениями [4]
    Форматирование [1]
    Выпадающие списки [2]
    Примечания [1]
    Сводные таблицы [1]
    Гиперссылки [1]
    Excel и интернет [1]
    Excel для Windows и Excel для Mac OS [2]

    Если Вам необходимо в таблицах, которые имеют неодинаковое количество ячеек в строках и/или столбцах, например таких:

    находить последние заполненные ячейки и извлекать из них значения, то в Excel Вы, к сожалению, не найдёте функции типа ВЕРНУТЬ.ПОСЛЕДНЮЮ.ЯЧЕЙКУ()

    Вот как это сделать имеющейся в стандартном наборе функций функцией ПРОСМОТР().

    1. Для текстовых значений:

    В английской версии:

    Как это работает: Функция ПРОСМОТР() ищет сверху вниз в указанном столбце текст “яяя” и не найдя его, останавливается на последней ячейке в которой есть хоть какой-то текст. Так как мы не указали третий аргумент этой функции “Вектор_результатов”, то функция возвращает значение из второго аргумента “Вектор_просмотра”.

    Пояснение: Почему именно “яяя“? Во-первых, потому что функция сравнивает при поиске текст посимвольно, а символ “я” в русском языке последний и все предыдущие при сравнении отбрасываются, во-вторых, потому что в русском языке нет такого слова.

    Примечание: Вообще-то достаточно использовать и “яя“, но тогда возникает мизерная возможность попасть на таблицу, в которой будет такое слово. Так называются город и река в Кемеровской области. В детстве я был в этом городе и даже купался в этой реке 🙂

    2. Для числовых значений:

    В английской версии:

    Как это работает: Функция ПРОСМОТР() ищет слева направо в указанной строке число “9E+307” и не найдя его, останавливается на последней ячейке в которой есть хоть какое-то число. Так как мы не указали третий аргумент этой функции “Вектор_результатов”, то функция возвращает значение из второго аргумента “Вектор_просмотра”.

    Пояснение: Почему именно “9E+307“? Потому что это максимально возможное число в Excel. Поэтому функция найти его может только в каком-то невероятном случае, в реальной жизни пользователь такими числами просто не оперирует.

    3. Для смешанных (текстово-числовых) значений:

    В английской версии:

    Как это работает: Функция ПРОСМОТР() ищет слева направо в указанной строке число “1” и найдя его, останавливается на последней ячейке в которой есть это число. Так как мы указали третий аргумент этой функции “Вектор_результатов”, то функция возвращает значение из него, соответствующее позиции последнего вхождения искомого в просматриваемый массив.

    Пояснение: Почему именно “1“? Да просто так 🙂 С таким же успехом можно использовать число 2 или 3 или 100500, например. Главное что бы первый аргумент функции был не менее делимого в выражении 1/Диапазон. Вот пример применения другого числа в первом аргументе, при делимом отличном от единицы:

    Источник: www.excelworld.ru

    Поиск последнего значения в строке или столбце функцией ПРОСМОТР

    На практике часто возникает необходимость быстро найти значение последней (крайней) непустой ячейки в строке или столбце таблицы. Предположим, для примера, что у нас есть вот такая таблица с данными продаж по нескольким филиалам:

    Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера – 41 и т.д.

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

    Универсальным решением будет использование функции ПРОСМОТР (LOOKUP) :

    У этой функции хитрая логика:

    • Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
    • Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
    • Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.

    Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*10 11 или сто миллиардов).

    Если в таблице не числа, а текст, то идея остается той же, но “очень большое число” нужно заменить на “очень большой текст”:

    Применительно к тексту, понятие “большой” означает код символа. В любом шрифте символы идут в следующем порядке возрастания кодов:

    1. латиница прописные (A-Z)
    2. латиница строчные (a-z)
    3. кириллица прописные (А-Я)
    4. кириллица строчные (а-я)

    Поэтому строчная “я” оказывается буквой с наибольшим кодом и слово из нескольких подряд “яяяяя” будет, условно, “очень большим словом” – заведомо “большим”, чем любое текстовое значение из нашей таблицы.

    Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже “на ура”.

    Источник: www.planetaexcel.ru

    Поиск значений в списке данных

    Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

    В этой статье

    Поиск значений в списке по вертикали по точному совпадению

    Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

    Примеры использования функции ВПР

    Дополнительные сведения можно найти в разделе функция ВПР.

    Примеры ИНДЕКСов и СОВПАДЕНИй

    =ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение “Капуста” в массиве B2:B10))

    Формула ищет первое значение в ячейке C2: C10, соответствующее капусты (в B7), и возвращает значение в C7 ( 100) — первое значение, соответствующее капусты.

    Дополнительные сведения можно найти в разделе Функция индекс и функция ПОИСКПОЗ.

    Поиск значений в списке по вертикали по приблизительному совпадению

    Для этого используйте функцию ВПР.

    Важно: Убедитесь, что значения в первой строке отсортированы в возрастающем порядке.

    В приведенном выше примере функция ВПР ищет имя учащегося, у которого есть 6 тардиес в диапазоне A2: B7. В таблице нет записи для 6 тардиес, поэтому функция ВПР ищет следующее самое высокое соответствие ниже 6 и находит значение 5, связанное с первым именем Дэйв, и, следовательно, возвращает Дэйв.

    Дополнительные сведения можно найти в разделе функция ВПР.

    Поиск значений по вертикали в списке неизвестного размера с точным соответствием

    Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

    C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

    Match (“апельсины”; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

    1 — количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales.

    Поиск значений в списке по горизонтали по точному совпадению

    Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

    Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

    Дополнительные сведения можно найти в разделе функции ГПР.

    Поиск значений в списке по горизонтали с использованием приблизительного совпадения

    Для выполнения этой задачи используется функция ГПР.

    Важно: Убедитесь, что значения в первой строке отсортированы в возрастающем порядке.

    В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

    Дополнительные сведения можно найти в разделе функции ГПР.

    Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )

    Примечание: Надстройка “Мастер подстановок” прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

    В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

    Щелкните ячейку в диапазоне.

    На вкладке формулы в группе решения нажмите кнопку Подстановка .

    Если команда подстановка недоступна, необходимо загрузить мастер подстановок надстройка программу.

    Загрузка программы-надстройки “Мастер подстановок”

    Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

    В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

    В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

    Источник: support.office.com

    Выборка последнего значения по условию

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

    Пример во вложении.

    Заранее благодарен за помощь.

    Вложения

    Пример.xls (99.5 Кб, 140 просмотров)
    01.02.2013, 12:58

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

    Выборка последнего дива

    Выборка по условию
    Приветствую. Подскажите. В общем есть задача. Юзверь вводит три цифры. По этим трем цифрам.

    Выборка по условию
    Здравствуйте! По воле случая приходится потихоньку изучать 1с. Подскажите, пожалуйста, как задать.

    01.02.2013, 14:19 2 01.02.2013, 14:38 [ТС] 3 01.02.2013, 21:22 4 04.02.2013, 11:14 5 04.02.2013, 11:36 6 04.02.2013, 11:46 7 04.02.2013, 22:18 8
    04.02.2013, 22:18
    04.02.2013, 22:18

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

    Выборка по условию
    Подскажите плиз, как создать такое условие? Mysql только начинаю учить Имеется две таблицы: с.

    Выборка по условию
    Доброго времени! Помогите пожалуйста с задачей: Есть лист “март” с именами менеджеров и их.

    SQLite и выборка по условию
    SQLite не работает выборка по условию Устройство или ОС, прошивка: LG d380 Коротко опишу свои.

    Выборка данных по условию
    День добрый Подскажите, можно ли с помощью формул организовать выборку данных с одного листа на.

    Источник: www.cyberforum.ru

    Функции поиска данных: разыскивается информация!

    Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

    Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!

    Поиск в таблице Эксель, функции ВПР и ГПР

    Роль этих функций в жизни обычного пользователя сложно переоценить. Теперь можно легко найти в таблице данных подходящую запись и вернуть соответствующее ей значение.

    Синтаксис функции ВПР такой: =ВПР( Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

    • Искомое значение – значение, которое будем искать. Это обязательный аргумент;
    • Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
    • Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
    • Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения.

    Поиск точного совпадения с помощью ВПР

    Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

    Функция ВПР в Excel

    Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

    Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

    И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

    Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д.

    Поиск неточного совпадения с помощью ВПР

    Благодаря этой опции в работе ВПР, мы можем избежать сложных формул, чтобы найти нужный результат.

    В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

    Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

    Нестрогий поиск ВПР в Excel

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

    Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.

    Поиск данных с помощью функции ПРОСМОТР

    Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения) :

    • Искомое значение – данные или ссылка на данные, которые нужно искать;
    • Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
    • Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

    При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

    Функция «ПРОСМОТР» в Microsoft Excel

    Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС

    Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

    Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:

    • Искомое значение – обязательный аргумент
    • Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
    • Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

    Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца]) :

    • Массив – аргумент указывает из какого массива ячеек нужно выбрать значение
    • Номер строки – указываете порядковый номер строки (начиная с первой ячейки массива), которую нужно вывести. Здесь можно записать значение вручную, либо использовать результат вычисления другой функции. Например, ПОИСКПОЗ.
    • Номер столбца – необязательный аргумент, указывается, если массив состоит из нескольких столбцов. Если аргумент упущен, формула использует первый столбец таблицы.

    Теперь скомбинируем эти функции, чтобы получить результат:

    Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

    Источник: officelegko.com

    Добавить комментарий

    Adblock
    detector