Найдите первую непустую ячейку в диапазоне

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

Предположим, что список - это ячейки A1, A2, A3, A4. Я пытаюсь создать функцию, которая будет делать следующее:

IF A1 has a value I want the cell to return A1.
  IF A1 is empty then I want it to return A2.
   IF A1 and A2 are both empty I want it to return A3.
    If A1, A2 and A3 are all empty I want it to return A4.

Ответ 1

первый результат в google: http://chandoo.org/wp/2014/01/15/find-first-non-blank-item-in-a-list-excel-formulas/

Эта формула возвращает первую ячейку TEXT для диапазона B1:B100:

=VLOOKUP("*", B1:B100, 1,FALSE)

* - это дикая карта в Excel. Когда вы попросите VLOOKUP найти *, он найдет первую ячейку, которая содержит что-либо.

ПРИМЕЧАНИЕ. Этот подход находит первую ячейку, содержащую любой ТЕКСТ. Поэтому, если первой непустой ячейкой является число (или дата,% или логическое значение), формула отображает следующую ячейку, содержащую текст.

Если вам нужно найти не пустое, то url дает следующее решение:

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

=INDEX(B3:B100, MATCH(FALSE, ISBLANK(B1:B100), 0))

Обязательно нажмите CTRL + Shift + Enter после ввода этой формулы.

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

ISBLANK(B1:B100): Это дает нам список значений TRUE/FALSE, в зависимости от 98 ячеек в B1:B100, пустых или нет. Это выглядит так: {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE; ...}

MATCH(FALSE, ISBLANK(…), 0): Когда мы имеем значения TRUE/FALSE, нам просто нужно найти первое значение FALSE (т.е. первую непустую ячейку). Вот что делает эта функция MATCH. Он находит точное соответствие значения FALSE в списке.

INDEX(B1:B100, MATCH(…)): Когда мы узнаем, какая ячейка является первой непустой ячейкой, нам нужно ее значение. Это то, что делает INDEX.

Ответ 2

Как указано в вашем комментарии к вашему вопросу, у вас есть 500 строк, чередующихся с пустыми ячейками. Вы хотите заполнить пустые ячейки значением последней непустой ячейки.

Я бы написал код VBA, который будет работать следующим образом: выберите диапазон ячеек, которые вы хотите заполнить, и запустите этот VBA:

Sub fillBlanks()
  For Each c In Selection.Cells
    If c.Value <> "" Then
      lastVal = c.Value
    Else
      c.Value = lastVal
    End If
  Next c
End Sub

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

Пошаговые инструкции по использованию этого кода vba - для этого образца:

Убедитесь, что выбран диапазон, нажмите ALT + F11.

Это должно открыть редактор Visual Basic:

Нажмите F7. Это приведет к созданию кода для активной таблицы. Вставьте код VB сверху:

Нажмите F5 (или используйте меню для запуска кода).

Конечный результат должен быть следующим:

Ответ 3

Вы можете просто поместить формулу rank.eq в соседний столбец и сделать vlookup, чтобы перенести все ваши данные вверх. Это приведет к тому, что все ваши данные вернутся.

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