Excel masīva formula ir formula, kas veic aprēķinus ar vērtībām vienā vai vairākos masīvos, nevis uz vienu datu vērtību. Izklājlapu programmās masīvs ir saistītu datu vērtību diapazons vai sērija, kas parasti atrodas darblapas blakus šūnās.
Šie norādījumi attiecas uz Excel 2019, 2016, 2013, 2010 un Excel for Microsoft 365.
Kas ir masīvu formulas?
Masīvu formulas ir līdzīgas parastajām. Viņi:
- Sāciet ar vienādības zīmi (=)
- Izmantojiet to pašu sintaksi kā parastajām formulām
- Izmantojiet tos pašus matemātiskos operatorus
- Izpildiet to pašu darbību secību
Tomēr masīvu formulas ieskauj cirtaini iekavas { }. Un jūs nevarat tās vienkārši ierakstīt; tie jāpievieno, nospiežot taustiņus Ctrl, Shift un Enter pēc formulas ievadīšanas šūna vai šūnas. Šī iemesla dēļ programmā Excel masīva formulu dažreiz sauc par PMP formulu.
Ikreiz, kad rediģējat masīva formulu, cirtainās figūriekavas pazūd. Lai tos atgrieztu, vēlreiz nospiediet taustiņus Ctrl, Shift un Enter.
Ir divi galvenie masīvu formulu veidi:
- Vienas šūnas masīva formulas, kas veic vairākus aprēķinus vienā darblapas šūnā
- Vairāku šūnu masīvu formulas, kas atrodas vairāk nekā vienā darblapas šūnā
Kā izveidot masīva formulu
- Ievadiet formulu šūnā.
- Turiet nospiestu tastatūras taustiņus Ctrl un Shift.
- Nospiediet un atlaidiet taustiņu Enter, lai izveidotu masīva formulu.
- Atlaidiet taustiņus Ctrl un Shift.
- Ja izdarīts pareizi, formulu ieskauj cirtaini breketes.
Vienas šūnas masīva formulas
Vienas šūnas masīva formulā tiek izmantota funkcija, piemēram, SUM, VIDĒJAIS vai COUNT, lai apvienotu vairāku šūnu masīva formulas izvadi vienā vērtībā vienā šūnā. Tālāk ir sniegts piemērs:
{=SUM(A1:A2B1:B2)}
Iepriekšējā formula saskaita A1B1 un A2B2 reizinājumu, un pēc tam tā atgriež vienu rezultātu viena šūna darblapā. Vēl viens veids, kā parādīt šo formulu, ir:
=(A1B1)+(A2B2)
Daudzšūnu masīvu formulas
Kā norāda nosaukums, vairāku šūnu masīva formulas atrodas vairākās darblapas šūnās, un tās atgriež masīvu kā atbildi. Citiem vārdiem sakot, viena un tā pati formula ir divās vai vairākās šūnās, un tā katrā šūnā atgriež dažādas atbildes.
Katra masīva formulas kopija vai eksemplārs veic vienu un to pašu aprēķinu katrā šūnā, kurā tā atrodas, taču katra izmanto dažādus datus. Tāpēc katrs no tiem dod atšķirīgus rezultātus. Vairāku šūnu masīva formulas piemērs ir:
{=A1:A2B1:B2}
Ja iepriekš minētā masīva formula ir šūnās C1 un C2 darblapā, rezultāti būtu šādi:
- Dati mapē A1 tiek reizināti ar B1 datiem, un rezultāti tiek parādīti šūnā C1..
- Dati mapē A2 tiek reizināti ar B2 datiem, un rezultāti tiek parādīti šūnā C2..
Masīvu formulas un Excel funkcijas
Varat izmantot daudzas Excel iebūvētās funkcijas, piemēram, SUM, VIDĒJAIS un COUNT , masīva formulā. Ir arī dažas funkcijas, piemēram, TRANSPOSE, kurām vienmēr ir jābūt masīva formulai, lai tās darbotos pareizi. (Funkcija TRANSPOSE kopē datus no rindas kolonnā vai otrādi.)
Varat arī paplašināt daudzu funkciju lietderību, piemēram, INDEX un MATCH vai MAXun IF, izmantojot tos kopā masīva formulā.
Izveidojiet vienkāršu vienas šūnas masīva formulu
Vienas šūnas masīva formulas parasti vispirms veic vairāku šūnu aprēķinu un pēc tam izmanto tādu funkciju kā AVERAGE vai SUM, lai apvienotu masīva izvade vienā rezultātā.
Ignorēt kļūdu vērtības, meklējot datus
Šajā masīva formulā tiek izmantotas funkcijas AVERAGE, IF un ISNUMBER, lai atrastu esošo datu vidējā vērtība, ignorējot tādas kļūdu vērtības kā DIV/0! un NAME?
Datu šūnu skaitīšana
Izmantojiet funkcijas SUM un IF masīva formulā, lai saskaitītu datu šūnas, kas atbilst vienam no vairākiem nosacījumiem; šis paņēmiens atšķiras no programmas Excel funkcijas COUNTIFS izmantošanas, kas prasa, lai visi iestatītie nosacījumi tiktu izpildīti, pirms tiek skaitīta šūna.
Atrodiet lielāko pozitīvo vai negatīvo skaitli
Šajā piemērā ir apvienota funkcija MAX un funkcija IF masīva formulā, kas atradīs lielāko vai maksimālo vērtību diapazonam datus, ja tie atbilst konkrētiem kritērijiem. Šeit lielākā vērtība apzīmē lēnāko laiku.
Atrodiet mazāko pozitīvo vai negatīvo skaitli
Līdzīgi kā iepriekš minētajā piemērā, varat apvienot funkcijas MIN un IF masīva formulā, lai atrastu mazāko vai minimālo vērtību. datu diapazonam, ja tas atbilst noteiktiem kritērijiem.
Atrodiet vidējo vai vidējo vērtību
Funkcija MEDIAN programmā Excel atrod datu saraksta vidējo vērtību. Apvienojot to ar funkciju IF masīva formulā, jūs varat atrast vidējo vērtību dažādām saistīto datu grupām.
Izveidojiet uzmeklēšanas formulu ar vairākiem kritērijiem
Šī masīva formula ietver funkciju MATCH un INDEX ligzdošanu, lai datubāzē atrastu konkrētu informāciju.
Izveidojiet kreisās puses meklēšanas formulu
Funkcija VLOOKUP parasti meklē tikai datus, kas atrodas kolonnās pa labi, taču, apvienojot to ar funkciju CHOOSE, jūs var izveidot kreiso uzmeklēšanas formulu, kas meklēs datu kolonnas pa kreisi no argumenta Lookup_value.
Ievadot masīvu kā argumentu funkcijai, kas parasti satur tikai vienu vērtību vai šūnas atsauci, varat ierakstīt figūriekavas tieši, nevis izmantot Ctrl+Shift+Entertaustiņsitiena kombinācija, kā norādīts iepriekš minētajā piemērā.
