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, а потім натисніть Ctrl + Shift + 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)) у порожню клітинку, де потрібно знайти результат, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати результат, див. скріншот:

doc знайти n-ту непорожню 3

Примітка: У наведеній вище формулі A1: M1 - значення рядків, які ви хочете використовувати, і число 4 - це четверта не пуста клітинка, яку ви хочете повернути, якщо ви хочете отримати другу не пусту клітинку, вам просто потрібно змінити число 4 на 2, як вам потрібно.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

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

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (8)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Привіт, я шукаю третій порожній рядок у стовпці, і я скопіював вашу формулу і щойно змінив діапазон з $A$1:$A:$25 на мій діапазон $D$306:$D$354. Але коли я змінюю діапазон, я отримую #REF! як моя відповідь. Я вже перетворив на масив, Control-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 Raw Data'!CR:CR,SMALL(ROW('PO Raw Data'!CR:CR)+(100*('PO Raw Data'!CR:CR="")),$W2) &"")

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


Будь-які думки щодо того, що я можу зробити, щоб це виправити?
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця