Как получить список несмежных значений из строки в Excel (без учета пробелов)?

Я реструктурирую несколько наборов данных, и мне нужно получить список несмежных значений в строке в Excel для Mac. Есть ячейки с пустыми пробелами, которые следует отбросить, поэтому в результате получается горизонтальный список тех же значений без пустых ячеек между ними.

Значения отображаются в блоках из 6 смежных ячеек, чередующихся разным количеством (кратным 6!) Пустых ячеек. Данные числовые, поэтому> 0 помогает. Я пробовал адаптировать решения, найденные в Интернете, такие как это здесь, но безуспешно.

Мне пришлось сделать что-то подобное, получив значения с 1-го по 10-е по отдельности, и я смог сделать это после сброса hloookup, изучения формул индекса + сопоставления и массива и корректировки решений, найденных в Интернете для аналогичной проблемы со столбцами, например это.

Вот как выглядит мое решение для получения 2-го вхождения подряд:

= ЕСЛИОШИБКА (ИНДЕКС ($ FR5: $ GT5; МАЛЫЙ (ЕСЛИ ($ FR5: $ GT5> 0, COLUMN ($ FR5: $ GT5) -COLUMN ($ FR5) + 1, FALSE), 2)), «9999» )

где $ FR5: $ GT5 - это диапазон, из которого мне нужно получить значения, а 9999 - мой код для пропущенных значений. Просто подумал, что брошу его туда, кому-то с ограниченными навыками, как я, может оказаться полезным.

Какие-нибудь советы, которые помогут мне двигаться дальше? Желательно, чтобы я адаптировал мою предыдущую формулу для решения этой проблемы. Я пробовал, но не смог избавиться от пустых ячеек. Я застрял! Заранее спасибо.


array-formulas excel-formula excel-2011
person user5225119    schedule 13.08.2015    source источник
comment
Спасибо, это действительно сработает - за исключением того, что мне нужно сделать это для нескольких наборов данных, по несколько раз в каждом из них, для различных диапазонов и количества значений, которые нужно получить. Поэтому мне нужно полагаться на формулы, которые я могу дважды проверить и т. Д. У меня даже есть сумма / счетчики в моих диапазонах происхождения и судьбы в качестве проверки безопасности, чтобы все значения были получены правильно! В любом случае, спасибо, что добавили в мой набор трюков с Excel!   -  person user5225119    schedule 16.08.2015

Ответы (1)


arrow_upward
1
arrow_downward

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

Ваша формула очень близка к сбору последовательности чисел, игнорируя пробелы и числа, меньшие или равные нулю. Вам просто нужно настроить параметр k для МАЛЕНЬКАЯ функция (2 в приведенном выше примере формулы).

Если вы поместите =COLUMN(A:A) в ячейку и заполните ее вправо, вы получите последовательность целых чисел, таких как 1, 2, 3, 4 и т. Д. Точно так же, если вы поместите =ROW(1:1) в ячейку и заполните ее, вы снова получите 1, 2, 3, 4 и т. Д. Одну из этих подформул можно использовать для увеличения k вашего МАЛЕНЬКОГО в зависимости от того, хотите ли вы заполнить формулу прямо для получения возвращаемых значений в одной строке или заполнить до получить возвращенные значения в одном столбце. Таким образом, заполнение вправо или вниз даст вам второй, третий и т. Д. Возвраты.

Чтобы получить возвращаемые значения в строке, используйте эту формулу массива,

=IFERROR(INDEX(5:5, SMALL(IF($FR5:$GT5>0, COLUMN($FR:$GT)), COLUMN(A:A))),"9999")

Залейте вправо по мере необходимости, чтобы уловить все значения. Чтобы получить возвращаемые значения в столбце, используйте эту формулу массива,

=IFERROR(INDEX($5:$5, SMALL(IF(FR$5:GT$5>0, COLUMN(FR:GT)), ROW(1:1))),"9999")

Заполните по мере необходимости, чтобы уловить все значения.

Формулы массива должны быть завершены с помощью Ctrl + Shift + Enter↵. После правильного ввода в первую ячейку их можно заполнить или скопировать вниз или вправо, как и любую другую формулу.

Я никогда не был поклонником математической акробатики (например, COLUMN($FR5:$GT5)-COLUMN($FR5)+1), которую ваша исходная формула использует для определения столбца (или строки) в этом стиле формулы. Я существенно сократил это в приведенных выше эквивалентных формулах, но они составляют одно и то же.

person Community    schedule 15.08.2015
comment
Спасибо! Это прекрасно работает. Я очень признателен за то, что вы нашли время объяснить, почему это тоже работает :-) Я подозревал, что решение было в значительной степени в моем лице ... ха-ха. Это также помогает иметь более элегантный подход к моему n-му решению. - person user5225119; 16.08.2015