Конденсировать данные столбца с множеством пробелов

Question

Я видел этот и подобные методы, размещенные здесь и в других местах:

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

У меня есть данные пациента, в основном так:

D       E       F           H         I         J
label1  label2  label3      label4    label5    label6
8       15      7           8         15        7
                            4         11        3
                            5         13        1
4       11      3           2          8        0
                            3          5        2



5       13      1

2       8       0                    


3       5       2

• строка 1 - метки данных
• до 5 возможных числовых значений расположены в столбцах D, E, F случайным образом
• там много заготовок
• собрать эти числа до 5 и поместить их в сетку 3x5y H2:H6, чтобы я мог усреднить их и вывести их в другое место
• нет NUM! ошибки могут быть возвращены

2 решения были найдены здесь и в других местах, но они не работают.

=IFERROR(INDEX(D:D,SMALL(IF(ISNUMBER(D:D),ROW(D:D)),ROW(D1))),"") 

Это не работает в формате Excel 2016 xlsx, но, кажется, иногда работает в Excel .xls 2003 года. Это действительно странная часть. Я хотел бы использовать формат 2016 года, потому что 97-2003 дает некоторые ошибки при попытке сохранить. Формула возвращает пробелы или метку данных первой строки, или все те же числа из D2 вместо сжатых до 5 чисел.

=IF(ROWS(H$2:H2)>COUNTA(D:D),"",INDEX(D:D,SMALL(IF(D$2:D$256<>"",ROW(D$2:D$256)),ROWS(H$2:H2))))

Это не работает, потому что возвращает NUM! значения ошибок для полностью пустых столбцов или столбцов с менее чем пятью числовыми значениями. В этой книге будут "будущие" дни и листы, которые будут пустыми, но имеют вставленную или заданную формулу, а вся книга из H2:J6 будет усреднена и stdev'd на листе 1. Это в свою очередь графически. Он не может иметь нечисловых значений ошибок.

Answer 1

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

Это на самом деле сработает, если правильно вставить с окончанием (ROW (D1)), оставаясь относительной ссылкой. Другие ссылки могут быть относительными или абсолютными по желанию. Многие источники предложат выделить все ячейки назначения для формулы массива, затем введите формулу в первой ячейке, затем нажмите cntl + shift + enter. Это не верно. Это буквально помещает одну и ту же формулу в каждую ячейку. Эта окончательная относительная ссылка ROW должна обновляться в каждой ячейке назначения для вывода формулы.

Нажмите в первой ячейке желаемой области вывода. Введите формулу. С + С + Е. Затем возьмите правый нижний угол и перетащите формулу через все нужные ячейки вывода. В моем случае я ввел формулу в H2. С + С + Е. Лекарство от H2:H6. Затем перетащите эту столбчатую группу по горизонтали от H2:H6 до J2:J6. Работает отлично сейчас.

Answer 2

У меня менее 50 репутации, поэтому я не могу комментировать.

Насколько я понимаю, вам нужно удалить только пустые строки, верно? Кажется, простой фильтр может сделать эту работу. AFAIK это то, что я получаю из образца.

Однако могут быть пустые ячейки вместо пустых строк. В этом случае более трудоемким, но более простым в реализации методом является копирование данных вставки из столбца D в другое место, удаление пробелов и удаление дубликатов. Сделайте то же самое для двух других столбцов, и все готово. Это легко, так как у вас есть всего 3 столбца для работы.