Mi rejtőzik táblázatkezelőnk motorháztetője alatt?

Avagy mi alapján írja be a következő értéket az Autofill?

2015. szeptember 05. - Office Guru

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?

negyvennyolc.jpgHogy 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?

negyvenkilenc.jpgHivatalosan 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.

otven.jpgAz 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.

otvenegy.jpgPersze 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?

otvenketto.jpgTö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)

otvenharom.jpgTehá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).

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr97762898

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

banjankri 2015.09.14. 23:38:56

A poszt elején taglalt furcsaságra a magyarázat a lebegőpontos számábrázolás illetve annak korlátai. Bővebben, illetve a "hivatalosan dokumentált magyarázat":
support.microsoft.com/hu-hu/kb/78113

toportyánféreg 2016.01.23. 18:18:47

kicsit egyszerűbb a G1-be beírni, hogy =D1+1 és utána azt lehet másolni napestig