Skondensuj dane kolumny z wieloma pustymi miejscami

Widziałem to i podobne metody opublikowane tutaj i gdzie indziej:

Jak mogę skondensować listę, która zawiera brakujące dane, do nowej listy bez komórek z brakującymi danymi?

Mam dane pacjentów, w zasadzie takie:

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

• wiersz 1 to etykiety danych
• maksymalnie 5 możliwych wartości liczbowych jest losowo umieszczanych w kolumnach D, E, F
• jest mnóstwo pustych miejsc
• zbierz te liczby do 5 i umieść je w siatce 3x5y H2: H6, abym mogła je uśrednić i zniszczyć gdzie indziej
• bez NUM! błędy mogą zostać zwrócone

2 rozwiązania zostały znalezione tutaj i gdzie indziej, ale nie działają.

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

Nie działa to w formacie Excel 2016 xlsx, ale zdaje się działać czasami w formacie .xls programu Excel 2003. To naprawdę dziwna część. Chciałbym użyć formatu 2016, ponieważ 97-2003 zawiera pewne błędy próbujące zapisać. Formuła zwraca puste pola lub etykietę danych pierwszego wiersza lub wszystkie te same liczby z D2 zamiast liczb maksymalnie 5, które zostały skondensowane.

=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))))

To nie działa, ponieważ zwraca NUM! wartości błędów dla całkowicie pustych kolumn lub kolumn z mniej niż pięcioma wartościami liczbowymi. Ten skoroszyt będzie zawierał „przyszłe” dni i arkusze, które są puste, ale mają formułę wklejoną lub szablonową, a cały skoroszyt z H2: J6 jest uśredniany i stdeved na arkuszu1. To z kolei jest wykreślone. Nie może mieć wartości błędów innych niż liczbowe.

Mam mniej niż 50 reputacji, więc nie mogę komentować.

Z mojego zrozumienia wystarczy usunąć puste wiersze, prawda? Wydaje się, że prosty filtr może wykonać zadanie. AFAIK, to właśnie otrzymałem z próbki.

Jednak może być puste komórki zamiast pustego wydziwianie . W takim przypadku bardziej czasochłonna, ale łatwiejsza do wdrożenia metoda polega na skopiowaniu wklejonych danych z kolumny D w inne miejsce, usunięciu spacji i usunięciu duplikatów. Zrób to samo dla pozostałych dwóch kolumn i gotowe. Jest to łatwe, ponieważ masz tylko 3 kolumny do pracy.

= IFERROR (INDEKS (D: D, MAŁY (JEŚLI (ISNUMBER (D: D), WIERSZ (D: D)), WIERSZ (D1))), „”)

Będzie to działało, jeśli zostanie poprawnie wklejone w ostatnim (ROW (D1)) pozostając względnym odniesieniem. Inne odniesienia mogą być względne lub bezwzględne zgodnie z potrzebami. Wiele źródeł zasugeruje zaznaczenie wszystkich komórek docelowych dla formuły tablicowej, a następnie wpisz formułę w pierwszej komórce, a następnie naciśnij cntl + shift + enter. To jest źle. Dosłownie umieszcza dokładnie taką samą formułę w każdej komórce. To ostateczne odniesienie względne ROW musi zostać zaktualizowane w każdej komórce docelowej dla wyniku formuły.

Kliknij pierwszą komórkę żądanego obszaru wyjściowego. Wprowadź formułę. C + S + E. Następnie złap prawy dolny róg i przeciągnij formułę przez wszystkie żądane komórki wyjściowe. W moim przypadku wprowadziłem formułę do H2. C + S + E. Zrób to z H2: H6. Następnie zaaplikuj tę grupę kolumnową poziomo od H2: H6 do J2: J6. Działa teraz doskonale.