Zamawianie zestawu losowo rozmieszczonych danych

Mam zestaw danych losowo rozmieszczonych, jak widać na poniższym obrazku. Chciałbym je zebrać i uporządkować w grupach (grupa 1, grupa 2 itd.). W poniższym przykładzie, jeśli wartość „1” jest obecna w jednej z kolumn A: R, wówczas wszystkie „1” powinny być wyłożone w grupie 1. jeśli nie ma 1, powinien wyglądać na pusty. Czy można to zrobić w programie Excel za pomocą formuły lub makra? Wolę jednak formułę. Dzięki.

Oto link do przykładowego skoroszytu (edytowalny): https://1drv.ms/x/s!AgVGRfBWRysXgWJTuf-ILJ0BTxyQ

microsoft-excel

— Max
źródło

Nie możesz po prostu dodać tabeli przestawnej danych i pogrupować ją?

— Thales

Naprawdę wolałbym formułę lub makro. Nie chciałbym za każdym razem przechodzić na stół przestawny.

— Max

Przede wszystkim grupy muszą mieć ten sam rozmiar danych (na wypadek, gdyby wiersz zawierał tylko jedną liczbę). Zatem grupa 1 musi należeć do przedziału od T do AK. W komórce T2 wprowadź formułę:

= JEŻELI (INDEKS ($ A $ 2: $ R $ 2, MAŁY (JEŻELI ($ A $ 2: $ R $ 2 = 1, KOLUMNA ($ A $ 2: $ R $ 2)), KOLUMNA (A: A))), „” )

Wpisz formułę i naciśnij Ctrl + Shift + Enter, aby wprowadzić formułę tablicową. Następnie, podczas kopiowania i wklejania w kolumnach U do AK pokaże w Grupie 1 wszystkie wystąpienia 1.

Kilka wskazówek, jak to poprawić:

Jeśli umieścisz w komórce T1 tylko cyfrę 1, a następnie sformatujesz ją w celu wyświetlenia "Group " 0, możesz zmienić 1 w formule na adres T1. Następnie, kopiując go do innych grup, formuła powinna dopasować się do innych liczb (musisz wstawić 1 do każdej kolumny od T do AK, ale pokaż tylko jedną z nich i, tworząc kolumny AM do BD, powinna być liczba 2 i tak dalej).
Jeśli twój Excel ma rok 2010 lub starszy, to nie masz tej IFERROR()funkcji. Rozwiązaniem jest używać IF()i ISERROR(): IF(ISERROR(...),"",(...)). Jest (...)to formuła pierwszego argumentu IFERROR().
Aby skopiować formułę do innych wierszy, możesz zmienić funkcję INDEKS, aby zwracać wiersz i kolumnę. Różnica polega tylko na tym, że zwracany wiersz jest wierszem komórki, a kolumna jest formułą (...).

— Thales
źródło

kiedy wklejam formułę do T2 i Ctrl + Shift + Enter, powie mi to „wprowadziłeś zbyt wiele argumentów dla tej funkcji”. Nie mogłem uzyskać wyniku. Czy przetestowałeś już tę formułę?

— Max

Zasadniczo chcę zebrać te same cyfry jednocyfrowe (powiedzmy 1 lub 2), a następnie wyrównać je razem. 1 są osobne. 2 są osobne. Trwa tak. ale ich liczba nie jest stała dla każdego wiersza. W rzędzie może nie być żadnych 1 lub tylko dwóch lub trzech. to sprawia, że jest to trudne.

— Max

Przepraszamy, w formule była literówka. Już to poprawiłem. Pierwsza funkcja COLUMN () była niepoprawna. Zamiast tego jest funkcja SMALL (). Testowany tutaj i działa.

— Thales

=INDEX($A$2:$R$2,SMALL(IF($A$2:$R$2=1,COLUMN($A$2:$R$2)),COLUMN(A:A)))powinno być właściwe

— Thales

Próbowałem to zrobić z funkcją indeksu, ale nie mogłem tego rozgryźć. tak, działa pięknie. Wielkie dzięki!

— Maks.