Jak stworzyć formułę do tworzenia sekwencji 1,1 1,2 1,3 2,1 2,2 2,3 itd?

Żeby stworzyć niektóre sekwencje w Excelu trzeba się wykazać pomysłowością. Np: stworzenie przykładowego ciągu 1,1 1,2 1,3 2,1 2,2 2,3 itd wymaga użycia przynajmniej kilku funkcji.

Tworzenie tej sekwencji można podzielić na 2 części, część całkowitą i część po przecinku.


Formuła do tworzenie sekwencji 1,1 1,2 1,3 2,1 2,2 2,3 itd — sztuczki #15

Aby stworzyć część całkowitą potrzeba najpierw zacząć liczyć od miejsca, z którego chcesz aby zaczynała się sekwencja. Uda ci się to zrobić dzięki dynamicznym obszarom i funkcji ILE.WIERSZY. Zakładając, że zaczynasz w komórce B7 funkcja wyglądać może tak

=ILE.WIERSZY(B$7:B7)

Dynamiczny obszar budujesz przez zalokowanie pierwszej części obszaru (B$7) i pozostawienie drugiej części jako odwołanie względne (B7). Teraz jak będziesz kopiował funkcję w dół obszar będzie się powiększał o nowe wierze i tym samym funkcja ILE.WIERSZY będzie zwracać kolejne liczby całkowite. 

Liczenie wierszy

Liczenie wierszy

W filmie założone zostało, że sekwencja ma mieć 5 razy jedną liczbę na przedzie zanim przejdzie do następnej, dlatego wynik funkcji trzeba podzielić przez 5. Następnie trzeba go zaokrąglić do góry, tak, żeby zamiast części ułamkowej dostawać całości. Formuła będzie wyglądać tak:

=ZAOKR.W.GÓRĘ(ILE.WIERSZY(B$7:B7)/5;1)

Formuła dla sekwencja części całkowitej

Formuła dla sekwencja części całkowitej

Teraz można zabrać się za 2 część (po przecinku). Będzie to ciąg powtarzający się 1, 2, 3, 4, 5, 1 itd. Łatwo z tego wywnioskować, że przyda się funkcja MOD, która zwraca resztę z dzielenia. Najpierw trzeba zastosować tą samą funkcję jak w części pierwszej, tylko zmniejszoną o 1 ponieważ dla poprawnego wyniku trzeba zacząć liczyć od 0 nie od 1.

=ILE.WIERSZY(D$7:D7)-1

Następnie trzeba wyciągnąć resztę z dzielenia przez 5 i jeszcze zwiększyć ją o 1, żeby uzyskać pożądany ciąg, czyli

=MOD(ILE.WIERSZY(D$7:D7)-1;5)+1

Formuła dla sekwencja części ułamkowej

Formuła dla sekwencja części ułamkowej

Teraz możesz już połączyć formuły ciągiem

&","&

cała formuła dla stworzonego ciągu będzie wyglądać:

=ZAOKR.W.GÓRĘ(ILE.WIERSZY(F$7:F7)/5;1)&","&MOD(ILE.WIERSZY(F$7:F7)-1;5)+1

Połączona formuła sekwencji

Połączona formuła sekwencji

Ważne, że ta formuła zwróci tekst, ponieważ łączenie (&) będzie wykonane na samym końcu. Jeśli chcesz, żeby Twój ciąg był liczbami trzeba tą formułę wziąć w nawiasy i dodać zero (dodawanie Excel wykonuje przed łączeniem, taka jest kolejność działań).

=(ZAOKR.W.GÓRĘ(ILE.WIERSZY(F$7:F7)/5;1)&","&MOD(ILE.WIERSZY(F$7:F7)-1;5)+1)+0

Zapis formuły sekwencji w taki sposób, żeby była traktowana jako liczba

Zapis formuły sekwencji w taki sposób, żeby była traktowana jako liczba

P.S.

Wpis na podstawie Excel Magic Trick 991

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Formuła do tworzenie sekwencji 1,1 1,2 1,3 2,1 2,2 2,3 itd — sztuczki #15