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

Zobacz kurs wideo Mistrz Excela

Ż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.

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.

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)

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

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

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ń).

Możesz zmienić 5 na dowolną wartość, by uzyskać ciąg jaki chcesz.

Wpis na podstawie Excel Magic Trick 991

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

[wojciechkowalczyk]

cześć adam! próbuję stworzyć sekwencję w kolumnie w postaci 0, 3, 5, 7, 10, 12, 14, 17, 19, 20, 23...itd to znaczy między zerem 3 mamy dwie "przerwy", między 3i5 oraz 5i7 mamy jedną "przerwę zaznaczeniu pięciu liczb i przeciągnięciu tworzy się sekwencja, ale zamiast liczby 12 pojawia się 12, 2 zamiast 14 pojawia się 14, 6 potem 19, 4 oraz 21, 80 itd itd.Nie pomaga nic formatowanie komórek, wybór liczb z zaokrąglenie do liczb całkowitych..Oczywiście pomaga dla wyświetlania w komórkach, ALE UWAGA!! W PASKU FORMUŁY JEST BEZ ZMIAN!!.Podejrzewam, że trzeba użyć jakiejś funkcji która zaokrągli do licz całkowitych, ale nie do końca: po liczbie 26, 60 pojawia się całkowita liczba 29, 00 a powinna być 28. i co ty na to? ...ciekawy problem?  pozdrawiam!

[czarnylewpl]

dlaczego w moim office proffesional plus 2019 nie mam funkcji sekwencja...? Czy ktoś wie jak ją uzyskać w excelu.