Как можно сжать список с отсутствующими данными в новый список без ячеек с отсутствующими данными?

Question

Column A    to     Column B
0.83               0.83
0.6                0.6
#N/A               0.99
#N/A               0.93
0.99    
#N/A    
0.93    

Как можно сжать список с отсутствующими данными в новый список без ячеек с отсутствующими данными?

Answer 1

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

1. Выберите столбец, который включает данные. Добавить фильтр (меню данных-> фильтр).
2. выберите раскрывающийся список для списка и снимите флажок для ошибки
3. Выберите первую ячейку в столбце.
4. Нажмите CTRL+SHIFT+ ВНИЗ, чтобы выбрать другие видимые ячейки
5. Нажмите CTRL+ C, чтобы скопировать
6. Перейти к новому листу в книге (или нажмите SHIFT+ F11, чтобы создать новый)
7. Нажмите CTRL+ V, чтобы вставить данные без ошибки

Примечание: для более старых версий Excel вместо этого вам нужно будет использовать "пользовательский фильтр" из выпадающего меню фильтра (ошибка "не равно"). Вы можете найти быстрый здесь (смотрите под заголовком "Excel Custom AutoFilters").

@Nixda: yikes .. Я не заметил, что вы дали в основном тот же ответ в своем комментарии, пока я не обновил страницу после ответа

Answer 2

В дополнение к решению автофильтрации tiktok (которое, безусловно, более гибкое), это еще одно «быстрое и грязное» исправление, которое немного короче:

1. Выберите столбец A (Ctrl - Пробел)
2. Домой -> Найти и выбрать -> Перейти (Ctrl - G) -> Специальные (Alt - S)
3. В зависимости от формул (Alt- F) или констант (Alt- O), выберите один из вариантов и снимите флажок Ошибки (Alt- E)
4. Копировать (Ctrl - C) - это выберет каждую ячейку, которая не является ошибкой.
5. Выберите цель (например, ячейку B1) и вставьте (Ctrl - V)

Готово!

Если вы не хотите копировать данные, а только удалить ошибки, вы можете изменить шаг 3, чтобы выбрать только ошибки, а затем просто удалить их ((Ctrl - -)

Answer 3

Или с формулой (подстройка под ваши нужды). Начните с B1 и скопируйте вниз.

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROW(A1))),"")

Это формула массива и должна быть подтверждена с помощью Ctrl+Shift+Enter

Если данные не от A1 до A10, а, скажем, от H25 до H34, используйте

=IFERROR(INDEX($H$25:$H$34,SMALL(IF(ISNUMBER($H$25:$H$34),ROW($A$1:$A$10)),ROW(A1))),"")

Обратите внимание, что формула все еще показывает строку ($ A $ 1:$ A $ 10). Эта часть будет возвращать число от 1 до 10 для правильных записей, и это число, которое необходимо INDEX для вытягивания правильной строки.

Кроме того, последняя строка (A1) остается неизменной, поскольку она возвращает числа от одного вверх и будет использоваться для поиска наименьшего (1), второго наименьшего (2), третьего наименьшего (3) и т.д. Значения.

Answer 4

Мне нужно уточнить кое-что в ответе @teylyn. Формула массива должна быть вставлена в B1 в вышеприведенном примере (или в первую ячейку желаемого местоположения данных для вывода формулы)... затем нажмите C+S+E, затем возьмите эту ячейку и перетащите ее вниз желаемое количество выходных ячеек - например, - 4 в приведенном выше примере. Это немного сбивает с толку выше, и собственный сайт справки Microsoft не помогает никому из пользователей, потому что он направляет пользователей выделять выходные ячейки для любой формулы массива, затем вводить формулу в первую ячейку, затем нажимать C+S+E , Я потратил некоторое время, пытаясь понять это, и это очень разозлило меня, пока я не понял, что мой первый шаг был неверным.

Это не сработает, если вы выделите, вставьте формулу, C+S+E. Это будет работать, только если вы вставите, C+S+E, а затем перетащите формулу вниз или через ячейки назначения.

так что-то подобное будет работать также:

= ЕСЛИОШИБКА (ИНДЕКС (А: А, МАЛЫЙ (ЕСЛИ (ЕЧИСЛО (А: А), СТРОКА (А: А)), СТРОКА (А1))), "")

или как это, если ваши данные в столбце D:

= ЕСЛИОШИБКА (ИНДЕКС ($ D:$ D, МАЛЫЙ (ЕСЛИ (ЕЧИСЛО ($ D:$ D) СТРОКА ($ D:$ D)), СТРОКА (D1))), "")

... но окончательная ссылка "строка" должна оставаться относительной ссылкой, потому что, когда вы перетаскиваете ее вниз по ячейкам назначения, она будет заполнять формулу D2, D3, D4 и т. д.

Итак, еще раз, в частности:- выделите одну ячейку назначения - вставьте в нее = IFERROR(INDEX(A:A, SMALL(IF(ISNUMBER(A:A), ROW(A:A)), ROW(A1))), "")- control+shift+enter - снова выделите эту ячейку - перетащите угловое поле вниз на нужное количество строк / столбцов для выходных ячеек