Note: The other languages of the website are Google-translated. Back to English

Как найти n-ю непустую ячейку в Excel?

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

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

Найти и вернуть n-е непустое значение ячейки из строки с формулой


стрелка синий правый пузырь Найдите и верните n-е непустое значение ячейки из столбца с формулой

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

doc найти n-й непустой 1

Пожалуйста, введите эту формулу: =INDEX($A$1:$A$25,SMALL(ROW($A$1:$A$25)+(100*($A$1:$A$25="")), 3))&"" в пустую ячейку, в которую вы хотите вывести результат, например, D2, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат, см. снимок экрана:

doc найти n-й непустой 2

Внимание: В формуле выше A1: A25 это список данных, который вы хотите использовать, и номер 3 указывает на третье непустое значение ячейки, которое вы хотите вернуть, если вы хотите получить вторую непустую ячейку, вам просто нужно изменить число 3 на 2, как вам нужно.


стрелка синий правый пузырь Найти и вернуть n-е непустое значение ячейки из строки с формулой

Если вы хотите найти и вернуть n-е непустое значение ячейки в строке, вам может помочь следующая формула, сделайте следующее:

Введите эту формулу: =INDEX($A$1:$M$1,SMALL(IF($A$1:$M$1<>"",COLUMN($A$1:$M$1)-COLUMN($A$1)+1),4)) в пустую ячейку, где вы хотите найти результат, а затем нажмите Shift + Ctrl + Enter ключи вместе, чтобы получить результат, см. снимок экрана:

doc найти n-й непустой 3

Примечание: В приведенной выше формуле A1: M1 - это значения строки, которые вы хотите использовать, а число 4 - это четвертое непустое значение ячейки, которое вы хотите вернуть, если вы хотите получить вторую непустую ячейку, вам просто нужно изменить число 4 на 2, как вам нужно.


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (10)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, я ищу третью пустую строку в столбце, скопировал вашу формулу и просто изменил диапазон с $A$1:$A:25 до моего диапазона $D$306:$D$354. Но когда я меняю диапазон, я получаю #REF! как мой ответ. Я уже конвертировал в массив, Ctrl-Shift-Enter. Теперь диапазон ячеек, на который я ссылаюсь, содержит формулы, поэтому может быть поэтому я получаю #REF. Ваша формула: =ИНДЕКС($A$1:$A$25,МАЛЕНЬКИЙ(СТРОКА($A$1:$A$25)+(100*($A$1:$A$25="")), 3))&"" Моя формула: =ИНДЕКС($D$306:$D$354,МАЛЕНЬКИЙ(СТРОКА($D$306:$D$354)+(1*($D$306:$D$354="")), 2))&"" Спасибо, Ноэль
Этот комментарий был сведен к минимуму модератором на сайте
вы поставили 1 вместо 100, поэтому ответили неправильно. пожалуйста, используйте приведенную ниже формулу

ИНДЕКС($D$306:$D$354,МАЛЕНЬКИЙ(СТРОКА($D$306:$D$354)+(100*($D$306:$D$354="")), 3))&"", чем ctrl + shift +ввести
Этот комментарий был сведен к минимуму модератором на сайте
Всем привет,

У меня есть таблица с многочисленными столбцами и строками. Строки имеют имена диапазонов и описание/местоположение в начале диапазона строк с собственным диапазоном, например «Номера зданий». Есть несколько рядов/зданий. В верхней части каждого столбца есть заголовки для конкретных «Типов сборки здания». Содержимое таблицы представляет собой числовые величины для каждого типа «Тип сборки здания» для каждого конкретного описания/местоположения «Здание». Может быть до (100) заголовков столбцов «Типы сборок зданий», но я ограничу сборки до (10) на диапазон строк, поэтому в каждом «Номере здания» будет множество пустых ячеек. диапазон.

У меня есть другие сводные таблицы, настроенные для каждого «Здания», в которые я хочу вывести количество непустых ячеек, вместе с соответствующим заголовком «Тип сборки» для каждого «Здания» с максимальным (10) «Сборки» на странице .

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

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

Надеюсь, это делает с тех пор, и кто-то может помочь.
Этот комментарий был сведен к минимуму модератором на сайте
Всем привет!


Не могли бы вы также помочь мне вернуть номер строки второй, третьей непустой ячейки?


Пожалуйста, помогите мне.
Этот комментарий был сведен к минимуму модератором на сайте
Привет Джон,
следующая формула может помочь вам получить все номера строк непустых ячеек, пожалуйста, попробуйте, спасибо!
=IFERROR(ROW(INDEX($A$1:$A$12,SMALL(INDEX(NOT(ISBLANK($A$1:$A$12))*ROW($A$1:$A$12),0),COUNTBLANK($A$1:$A$12)+ROW(A1)))),"")
Этот комментарий был сведен к минимуму модератором на сайте
очень полезно,
хотя, когда я возвращаю n-ю непустую ячейку в столбце, установленном на DATE, возвращаемое значение отображается как число (общее число). Форматирование ячейки в DATE также не меняет результат. не могли бы вы дать мне руку!
большое спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Ширази,
Извините, возможно, у вас нет прямой формулы для получения формата даты.

Если у кого-то есть решение, пожалуйста, прокомментируйте здесь.
Этот комментарий был сведен к минимуму модератором на сайте
Я запускаю эту формулу на листе ~ 400 строк с примерно 20 пробелами, перемешанными в данных, и она работает отлично, ЗА ИСКЛЮЧЕНИЕМ, когда я заполняю серию, она иногда по какой-то причине дублирует строку. Например, кажется, что значение каким-то образом одновременно является 331-м непустым и 332-м.

Что я заметил:
- Дублирование всегда происходит парами (напр. 331 и 332 дублируются, 333 и 334 тоже будут), потом на какое-то время возвращается в норму.
- Необработанные данные также содержат пробелы в парах, но не соответствующие дубликатам.
- В первых 94 строках дублирования не происходит, но они происходят через каждые 33-35 строк после этого.
-Похоже, что это не связано со значением в дублируемой ячейке.
-Я ввел как массив и как стандартную формулу, никакой разницы в функции

Для справки, вот моя фактическая формула:

=INDEX('Исходные данные PO'!CR:CR,SMALL(СТРОКА('Исходные данные PO'!CR:CR)+(100*('Исходные данные PO'!CR:CR="")),$W2) &"")

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


Любые мысли о том, что я могу сделать, чтобы исправить это?
Этот комментарий был сведен к минимуму модератором на сайте
CIAO STO CERCANDO DI APPLICARE LA TUA FORMULA MA NON MI FUNZIONA, DOVE STO SBAGLIANDO?
=INDICE($K$37:$K$88;MIN(RIGHE($K$37:$K$88)+RIGHE(100*($K$37:$K$88=""));6))&""
DEVO VISUALIZZARE L'ENNESIMA RIGA DIVERSA DA VUOTO

GRAzie
Этот комментарий был сведен к минимуму модератором на сайте
=INDICE($K$37:$K$88;MIN(RIGHE($K$37:$K$88)+(100*($K$37:$K$88=""));6))&""
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL