Создать список непустых значений ячеек из диапазона?

ProgramBox

Создать список непустых значений ячеек из диапазона?

Post author:admin
Запись опубликована:12 марта, 2023
Post category:Вопросы по программированию

#arrays #excel

#массивы #excel

Вопрос:

У меня есть электронная таблица, например:

 Column A

Ducks
Frogs
<Blank>
Rabbits
<blank>
Horse

Я хочу иметь возможность создать список всех непустых ячеек в диапазоне (столбец A) следующим образом:

 Ducks
Frogs
Rabbits
Horse

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

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

Однако на данный момент я получаю 0

Вот моя формула массива, пожалуйста, может кто-нибудь показать мне, где я ошибаюсь?

 =IFERROR(INDEX($F$16:$F$5000,IF($G$16:$G$5000<>"",ROW($1:$5000)),1),"")

Ответ №1:

IFERROR при такой настройке это может быть очень неэффективно, особенно если размер запрашиваемого набора данных велик; см. Мой ответ здесь:

https://superuser.com/questions/812727/look-up-a-value-in-a-list-and-return-all-multiple-corresponding-values

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

=COUNTIF(G$16:G$5000,"<>")

Основная формула массива ** тогда:

=IF(ROWS($1:1)>H$16,"",INDEX(F:F,SMALL(IF(G$16:G$5000<>"",ROW(G$16:G$5000)),ROWS($1:1))))

и копируйте вниз, пока не получите пробелы.

Для уникального списка измените формулу на H16 :

=SUM(IF(FREQUENCY(IF(G16:G5000<>"",MATCH(F16:F5000,F16:F5000‌,0)),ROW(F16:F5000)-MIN(ROW(F16:F5000)) 1),1))

и основная формула для:

IF(ROWS($1:1)>H$16,"",INDEX(F:F,SMALL(IF(FREQUENCY(IF(G$16:G$5000<>"",MATCH(F$16:F$5000,F$16:F$5000,0)),ROW(F$16:F$5000)-MIN(ROW(F$16:F$5000)) 1),ROW(F$16:F$5000)),ROWS($1:1))))

С уважением

1. Спасибо за ваше предложение, это работает очень хорошо. Однако есть ли способ перечислить только отдельные значения?

2. Я пропустил это в вашем первоначальном вопросе? Или вам нужно изменить его, чтобы отразить как таковой?

3. Нет, вы были на месте и полностью поняли вопрос, мне просто интересно, есть ли способ изменить формулу, чтобы также исключить повторяющиеся значения? Как показывать только уникальные значения? Спасибо

4. Вы бы изменили формулу в H16 на следующую формулу массива **: =СУММА(ЕСЛИ(ЧАСТОТА(ЕСЛИ(G16:G5000<>»»,СОВПАДЕНИЕ(F16:F5000,F16:F5000,0)),СТРОКА(F16:F5000)-МИН(СТРОКА(F16:F5000)) 1),1)) и основная формула массива ** тогда будет: =IF(СТРОКИ ($ 1: 1)> H $ 16,»», ИНДЕКС (F: F, МАЛЫЙ (ЕСЛИ(ЧАСТОТА(ЕСЛИ(G $ 16: G $ 5000<>»»,СОВПАДЕНИЕ (F $ 16: F $ 5000, F $ 16: F $ 5000,0)), СТРОКА (F $ 16: F $ 5000)-MIN(СТРОКА (F $ 16: F $ 5000)) 1), СТРОКА(F $ 16: F $5000)),СТРОК ($ 1:1))))

5. спасибо за этот код, однако excel сообщает, что есть проблема с первой формулой, которая будет находиться в ячейке H16. Есть идеи, почему?

Ответ №2:

я предлагаю вам рассмотреть возможность использования macro для этой проблемы (ее в параметрах панели инструментов). вы можете записать пустой макрос и вставить добавленный мной код. обратите внимание, что макросы переопределяют возможность обратного изменения с помощью истории (ctrl z). в коде столбец равен 16, а последняя строка равна 5000, но вы можете его изменить.

 Dim max As Integer
Dim colNum As Integer
Dim limit As Integer

max = 5000
colNum = 16

limit = max

For i = 1 To max
    If Cells(i, colNum) = "" Then
        Cells(i, colNum).Delete Shift:=xlUp
        If limit > 0 Then
            i = i - 1
            limit = limit - 1
        end if
    End If
Next i

1. Конечно, существует большая опасность того, что это перейдет в бесконечный цикл, если I продолжит уменьшаться на единицу?

2. Было бы проще уменьшить ваш цикл с помощью For i = max to 1 Step -1 .

Ответ №3:

Я нахожу другие решения (и большинство обращений поисковых систем по этой теме) неразборчивыми 🙂 Адаптация к другому / более общему использованию затруднена, если она не понятна сама по себе.

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

=INDEX( $A:$A, 2*ROW($A1)-COUNTIF($A$1:$A1,"<>") )

Это работает путем подсчета количества пустых ячеек выше и использования этого в качестве текущего смещения, необходимого для «заглядывания вперед».