Tömbképletek

Hogyan válaszolnánk meg Excel segítségével a következő kérdéseket:

• Két egész szám között hány 3-al osztható szám van?
• Két dátum között hány hétfő van?
• Két dátum között hány dátum esik hétvégére

Megoldások

Első

Nézzük az első feladatot. Hagyományos módon ezt úgy csinálnánk, hogy képezzük egy oszlopba a növekvő értékeket, és mellette egy segédoszlopban logikai értékként megadnánk az oszthatóságot, majd feltételes összegzéssel megkapjuk az eredményt. Az ábrán látható, hogy a „B” oszlopban a hárommal való oszthatóságot vizsgáljuk.

Ezután nincs több dolgunk, mint egy eredménycellába megszámolni a „B” oszlopban lévő TRUE értékek számát.

Mondjuk a „D2”-be írjuk be a képletet:

=COUNTIF(B2:B11;TRUE)

Második

A megoldást egyetlen képlettel is megadhatjuk, ami az egész tartományban vizsgálja az oszthatóságot, a visszakapott TRUE/FALSE értékeket 1-re és 0-ra alakítja, és összeadja az eredményt. Értelemszerűen csak ott lesz 1-es, ahol az érték osztható, és az 1-esek összegzése megadja, hogy hány szám van a tartományban, ami osztható 3-al.

=SUMPRODUCT(--(MOD(A2:A11;3)=0))

Tól-Ig tömbök képzése

Próbáljuk ki a következőket:

Egy cellába írjuk be a következő képletet, de mielőtt lezárnánk, nyomjuk meg az F9 billentyűt :

=SOR(INDIREKT("34:55"))

angol verzióban

=ROW(INDIRECT("34:55"))

A tartalom átváltozik erre

={34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55}

Tehát ez az összefüggés létrehozza a memóriában a kezdő és végérték közötti számok növekvő halmazát. (Ctrl+Z-vel vagy ESC-el lehet visszatérni.)

Ha a kezdő és a végérték nem konstans, akkor felvehetjük őket egy-egy cellába, és a képlet így alakul

=SOR(INDIREKT(A1 &":" & A2))

Látható, hogy az Indirekt függvénynek egy szövegre (stringre) van szüksége, ezt összefűzéssel állítjuk elő, belecsempészve a kettőspontot, mint a kezdő és a végérték elválasztójelét.

Ugyanez dátumokkal

A dátumokat ugyebár az Excel sorszámként kezeli. Egy egy szomszédos nap között pont egy egész különbség van. Két dátum közötti tömböt az előzőekhez hasonlóan képezhetjük, azaz a kezdő és végdátumot beírjuk két cellába, és az előző képletet beírva, majd F9-et nyomva ezt láthatjuk:

Tehát a 2013 január 1 és 5 közötti dátumszámok növekvő értékeit.

Mire jó ez az egész?

Ha visszatérünk a fenti feladatokhoz, akkor abban az esetben, ha nem akarjuk vagy nem lehetséges képezni a tömb elemeit külön a munkalapon, akkor ezt a megoldást alkalmazhatjuk a képletekben.

Hány hárommal osztható szám van két szám között (A1 és A2):

=SUMPRODUCT(--(MOD(ROW(INDIRECT(A1&":"&A2));3)=0))

Két dátum között hány hétfő van?

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1 & ":" & $A$2));2)=1))

A WEEKDAY függvény magyar verzióban a HÉT.NAPJA. Azt mondja meg, hogy egy dátum a hét hányadik napjára esik. A képlet végén 1-el hasonlítjuk össze, mert a Hétfő a hét első napja.

Két dátum között hány dátum esik hétvégére

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT($A$18&":"&$A$2));2)=6)+(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2));2)=7))

A hatodik nap a szombat, a hetedik nap pedig a vasárnap. A kettő közötti plusz jel a logikai VAGY műveletet jelenti.

Letölthető munkafüzet, Video

Ertekek-kozotti-szamolas-row-indirect

• Növekvő számhalmazok

Tovább... →