Gribu padalīties ar savu pieredzi masīva formulās. Iespējams, nekad par tādām neesi dzirdējis. Masīva formulas spēj paveikt īpaši sarežģītus aprēķinus, kurus tradicionāli paveikt ir grūtāk vai neiespējami. Iespējams, esi mēģinājis saskaitīt vērtības, kuras atbilst specifiskākiem kritērijiem kā SUMIF vai SUMIFS spēj paveikt, piemēram saskaitīt noteikta diapazona vērtības. Ar masīva formulu viegli saskaitīt zīmju skaitu kādā šūnu grupā. Pielietojums ir plašs un daudzos gadījumos formulas padara universālas.
Kas ir masīvs un ko spēj paveikt masīva formula?
Masīvs (Array) ir horizontāls, vertikāls vai gan horizontāls, gan vertikāls vairāku šūnu kopums. Masīva formula spēj veikt darbības ar vienu vai vairākām šūnām laukā.
Vizuāli masīva formulu no parastas formulas atšķir figūriekavas pirms vienādības zīmes un formulas beigās. Tas izskatās šādi:
{=SUM(B2:B6)}
Figūriekavas iegūst spiežot CTRL + SHIFT + ENTER pabeidzot rakstīt formulu. Pamēģini! Šī formula gan veic tādu pašu darbību kā SUM formula. Tādēļ paskatīsimies arī sarežģītākus piemērus.
Šajā vienkāršajā piemērā masīva formula tiek ievadīta laukā D3:D12. Ievēro, ka esmu iezīmējis visu lauku un ievadījis formulu =B3:B12*C3:C12. Nospiežot CTRL + SHIFT + ENTER, formula tiek ievietota visā iezīmētajā laukā, tā kļūst par masīva formulu ar figūriekavām. Tā ir vienāda visās iezīmētā masīva šūnās, tomēr rezultātā katras rindas šūna tiek sareizināta ar atbilstošo šūnu – B2*C2, B3*C3, utt.
Lai iegūtu visu ieņēmumu kopsummu, varam vienkārši saskaitīt visus reizinājumus ar SUM formulu, tomēr kopsummu var iegūt arī ar masīva formulu neizmantojot reizinājumus D kolonā.
Šis piemērs, protams, ir tikai ilustratīvs, jo Excel ir arī iebūvēta formula SUMPRODUCT(), kura veic divu lauku reizināšanu un nav nepieciešams izmantot masīva formulu. Ar šo piemēru vēlos parādīt pavisam vienkāršas formulas un radītu sākotnējo priekšstatu par masīva formulām.
Esmu sagatavojis arī sarežģītākus piemērus, kurus ar vienkāršām metodēm bez masīva formulām atrisināt būs grūtāk. Excel failā apkopots Latvijas pilsētu saraksts (pilns saraksts failā). Pieņemsim, ka jums jāatbild uz šiem jautājumiem:
Kā jūs rīkotos? Tradicionāls risinājums, iespējams, būtu pievienot papildu kolonu, kurā ar formulu LEN() noteiktu katras pilsētas nosaukuma garumu, tad veiktu tālākās darbības. Iespējams, garākā nosaukuma pilsētas atrašanai būtu nepieciešams pārkārtot kolonas, lai VLOOKUP() formula spētu strādāt. Masīva formulas padara uzdevumu vienkāršāku:
Šajā piemērā masīva formulas darbība veic katras laukā ietvertās šūnas zīmju skaita noteikšanu => LEN(A2:A77), kā arī vienā piemērā veic visu iegūto vērtību saskaitīšanu ar SUM(), otrā piemērā tiek noteikta maksimālā vērtība starp iegūtajām izmantojot MAX() formulu. Mēģinot noteikt pilsētu skaitu ar maksimālo zīmju skaitu, galvā iezogas doma, ka to vienkārši izdarīt ar COUNTIF() formulu. Tomēr sākot par to domāt, nekas vienkāršs nesanāk.
Lai iegūtu vēlamo rezultātu, izmantoju šādu formulu:
{=SUM(IF(LEN(A2:A77)=MAX(LEN(A2:A77));1;0))}
Formulā:
MAX(LEN(A2:A77)) => nosaka zīmju skaitu garākā nosaukuma pilsētai
LEN(A2:A77) => nosaka katras šūnas ieraksta zīmju skaitu (pilsētas garumu)
IF(LEN(A2:A77)=MAX(LEN(A2:A77));1;0) => ar IF formulu izvērtē, vai katras šūnas ieraksta zīmju skaits ir vienāds ar zīmju skaitu garākā nosaukuma pilsētai. Ja tas tā ir, tad piešķir vērtību 1, ja tā nav, tad – vērtību 0.
SUM(IF(LEN(A2:A77)=MAX(LEN(A2:A77));1;0)) => visbeidzot saskaitām kopā visus iegūtos IF rezultātus.
Mūsu piemērā ir tikai viena pilsēta ar 12 zīmju garumu. Tādēļ tikai vienā gadījumā IF nosacījums rezultātā atgrieza ‘1’. Pārējos gadījumos rezultāts bija 0.
Šis bija ļoti īss ieskats masīva formulās. Kā redzi, palika viens neatbildēts jautājums. Par to pastāstīšu citā reizē.