A következő posztban továbbra is maradunk az Excelnél (előbb-utóbb teszek újabb kitekintést más modulokba is megint) és egy kedves Olvasó által beküldött kérdést járok majd körül (utólagos engedelmével), ami egy igencsak érdekes problémát vet fel egy sokak által napi szinten is rengetegszer használt funkció, az Autofill kapcsán.

Szóval a felvetés a következő képen látható példához (a példa a saját értékeimen és kísérletemen alapul, de ez ráhúzható minden hasonló témára) kapcsolódik, azaz ha kijelölve a három cellánkat lehúzzuk, hogy az Autofill tegye a dolgát, miért 2.33333 a következő érték?

Hogy ne csak egyetlen problémán lássuk ezt a történetet, íme egy másik hasonló kérdés, azaz ha a következő képen látható sorozatot eggyel lejjebb húzzuk, miért 47 lesz a következő érték?

Hivatalosan dokumentált magyarázatról nem tudok, de az én elméletem szerint a magyarázat a lineáris regresszióban és a trendvonalban keresendő, azaz ha felrajzoljuk értékeinket egy koordinátarendszerben és megpróbáljuk összehozni az úgynevezett "best-fit" lineáris trendvonalat, akkor az Excel számítása szerint a koordinátarendszerben a trendnek megfelelő következő érték az Autofill által generált szám lenne.

Elsőre elolvasva ez lehet, hogy kicsit értelmetlennek tűnik (gondolom egy matematikatanár csak nevetne a leírásomon, de én normál szavakkal próbálom leírni az elméletem, hogy bárki megérthesse), úgyhogy következzen itt két diagram és egy trendvonal.

Az első diagramunk a rajta lévő trendvonallal tökéletesen mutatja a lineáris trendjét számaink alakulásának - ha ezt a trendvonalat továbbhúznánk, a következő elem a 47 lenne, ahogy ez a második diagramon tökéletesen látszik is. És ez az elmélet működik az 1,1,2-es sorozatunkra is, sőt igazából bármely olyan nem lineáris sorozatra, amelynél Autofillt szeretnénk használni.

Persze ez csak inkább elméleti kihívás volt, hiszen ha konkrétan tudjuk, hogy mit szeretnénk látni következő értékként az Autofill által felkínált verzió helyett, az nem lehet túlságosan nagy feladat, hiszen mondjuk a CTRL billentyű használatával az Autofillünket másolásra kényszeríthetjük, de mondjuk a következő példa esetén is könnyen elérhetjük az eredményünket függvények segítségével.

Adott a következő sorozat, ahogy a képen is látható: 1,1,1,2,2,2 - hogy érjük el, hogy így folytatódjon: 3,3,3,4,4,4?

Több megoldás is létezik, de én elsőre valószínűleg egy IF - COUNTIF kombinációt használnék. A COUNTIF-ről tudjuk, hogy egy adott tartományon belül számolja meg a második paramétereként megadott kritériumnak megfelelő értékeket, azaz példánk esetén a

=COUNTIF(A1:F1,F1)

azt fogja megnézni, hogy az A1:F1 tartományban hányszor fordul elő az F1 érték.

Innen már látszik, hol jön a képbe az IF függvény, hiszen csak annyit kell megnéznünk, hogy az F1 (vagy bármely cella) előfordulása egyenlő hárommal vagy sem, hiszen innen tudja majd a függvényünk, hogy lépjen egyet feljebb a számok sorozatában.

Azaz a következő függvénykombináció adja meg a megoldást:

=IF(COUNTIF(A1:F1,F1)=3,F1+1,F1)

Tehát az előbb már megismert COUNTIF-fel megszámoljuk F1 előfordulását, majd megnézzük, hogy ez egyenlő-e hárommal vagy sem. Amennyiben egyenlő hárommal, akkor a következő cellába az F1 értékét eggyel megnövelve írja, ha nem egyenlő hárommal, akkor még F1 értékét írja ki. Hát, én az ilyenek miatt imádom az Excelt (is).