Формули с масиви в Excel: пълно ръководство и практически примери

Формулите за масиви в Excel са едни от онези инструменти, които изглеждат като „черна магия“ при първия ви поглед, но щом ги овладеете, те ви позволяват да правите с една-единствена формула това, което преди е изисквало десетки или стотици помощни клетки.

Въпреки че в началото може да изглеждат малко плашещи, масивите и формулите за масиви са едни от най-мощните ресурси на Excel за анализ на данни, моделиране, финанси, инженерство или просто за това да направите електронните си таблици много по-чисти и по-бързи.

Какво е масив и какво е формула за масив в Excel?

В Excel матрицата е просто колекция от стойности , които се третират като набор: те могат да бъдат в един ред, в една колона или да образуват блок от няколко реда и колони.

Например, един много типичен масив може да съдържа месеците от годината , написани един под друг или един до друг, и Excel би работил с тази група, сякаш е един обект с данни.

Формулата за масив е формула, която вместо да работи с една единствена стойност, работи едновременно с цял масив от елементи: тя може да извършва множество изчисления едновременно и да връща или един резултат, или масив от резултати.

Ключовата идея е, че формулата за масив кара Excel да обработва много елементи наведнъж , като вътрешно оценява всички стойности и, ако е необходимо, връща няколко резултата едновременно в различни клетки.

Например, представете си, че имате броя продадени единици в колона B и цената на всяка единица в колона C. С формула за масив като =SUM(B2:B11*C2:C11) , Excel умножава всеки ред (единици по цена) и след това сумира всички тези продукти, без да е необходима междинна колона.

Как да въведете и разпознаете класическа матрична формула

Традиционните формули за масиви в Excel се въвеждат с помощта на специална клавишна комбинация: Ctrl + Shift + Enter. Самото натискане на Enter не е достатъчно.

Когато напишете формула за масив и потвърдите с тази комбинация, Excel показва формулата във формулната лента, оградена от къдрави скоби { } . Тези скоби не се въвеждат ръчно: Excel ги добавя автоматично, когато открие, че е формула за масив.

Ако се опитате сами да въведете къдравите скоби, Excel няма да ги третира като формула за масив , а като нормална формула, така че е задължително да използвате клавишната комбинация, за да работи.

Всеки път, когато редактирате формула за масив, къдравите скоби временно изчезват: ще трябва да натиснете Ctrl + Shift + Enter отново , след като приключите с редактирането, в противен случай формулата вече няма да бъде формула за масив и ще изчислява само върху първия елемент от диапазона.

И един важен детайл: ако забравите да използвате комбинацията и просто натиснете Enter, формулата ще се държи като стандартна формула , приемайки само първата стойност от всеки диапазон, което може да даде неправилни резултати, без да го осъзнавате.

Видове матрични формули: един резултат или множество резултати

В Excel можем да различим два основни типа формули с масиви : тези, които връщат една стойност, и тези, които връщат набор от резултати, разпределени в няколко клетки.

В първия случай формулата взема масив от данни, извършва изчисленията и връща един резултат в клетка (например сума, средна стойност, брой, минимум или максимум).

Във втория тип, самата формула генерира изходен масив , който заема две или повече клетки. В този случай всички резултати са част от една единствена формула за масив, която „живее“ в няколко клетки едновременно.

Функции като SUM , AVERAGE , MAX или MIN (и техните варианти, MINIFS и MAXIFS ) могат да работят с масиви, ако са въведени като формули за масиви в една клетка, докато други, като TRANSPOSE , TREND или FREQUENCY , са предназначени да връщат масиви от няколко клетки.

Силата на всичко това е, че една-единствена формула може да замени много помощни колони , като по този начин поддържа листа ви по-чист, по-лек и с по-малък риск от грешки при копиране или актуализиране.

Разширени примери за класически матрични формули

Формулите за масиви ви позволяват да решавате ситуации, които биха били тромави или невъзможни със стандартни формули. По-долу са дадени няколко типични и разширени примера, базирани на диапазони с имена като Данни, Продажби, МоитеДанни или ВашитеДанни.

Диапазони на суми, които съдържат грешки

Когато даден диапазон съдържа грешки като #N/A , нормалната функция SUM ще се провали. С формула за масив можете да игнорирате тези грешки и да сумирате само валидните стойности:

=SUM(IF(ISERRER(Данни),"";Данни))

Функцията ISERROR открива клетки с грешки в диапазона Data, а функцията IF създава нов масив, където вместо грешки поставя празни низове «» и където клетките без грешки запазват първоначалната си стойност.

В този чист масив функцията SUM изчислява общата сума, игнорирайки празните елементи, така че можете да получите сумата, дори ако оригиналният диапазон има дефектни клетки.

Пребройте колко грешки има в даден диапазон

Ако трябва да преброите грешките, вместо да ги сумирате, можете да използвате подобен вариант:

=SUM(IF(ISERRER(Данни);1;0))

Тази формула изгражда матрица, в която всяка клетка с грешка се трансформира в 1 , а всяка клетка без грешка - в 0 , така че сумата от всички тези 1 и 0 дава общия брой грешки.

Формулата може да се опрости чрез премахване на третия аргумент на IF, тъй като когато условието е невярно, то връща FALSE, а SUM интерпретира FALSE като 0:

=SUM(IF(ISERRER(Данни);1;0))

И все още е възможно да се съкрати допълнително, като се умножи директно булевият резултат по 1, възползвайки се от факта, че TRUE*1=1 и FALSE*1=0 :

=SUM(IF(ISERRER(Данни)*1))

Добавяне на стойности, които отговарят на условията (И и ИЛИ „на ръка“)

С формули за масиви можете да сумирате само стойностите, които отговарят на определени условия, без винаги да се налага да използвате SUMIFS. Например, за да сумирате само положителните стойности в диапазона „Продажби“:

=SUM(IF(Продажби>0;Продажби))

Тук функцията IF генерира масив, където клетки със стойност по-голяма от 0 запазват стойността си, а останалите стават FALSE; функцията SUM игнорира стойностите FALSE и сумира само положителните числа.

Можете също да комбинирате няколко условия, като използвате умножение (еквивалентно на логическо И ) или събиране (еквивалентно на логическо ИЛИ ). Например, за да добавите стойности, по-големи от 0 и по-малки или равни на 5:

=SUM((Продажби>0)*(Продажби<=5)*(Продажби))

В този случай логическите изрази връщат масиви TRUE/FALSE, които при умножение стават 1 или 0 и действат като филтри върху стойностите на Sales.

Ако се нуждаете от поведение тип ИЛИ, можете да използвате сумата от логическите условия в рамките на АКО, както е в тази формула, която сумира стойности по-малки от 5 или по-големи от 15:

=SUM(IF((Продажби<5)+(Продажби>15);Продажби))

Функциите AND и OR връщат едно TRUE или FALSE, така че не се използват директно с множество масиви; решението е да се емулират с умножения и събирания, както в предишните примери.

Изчислете средна стойност, изключвайки нулите

Ако искате да получите средна стойност, без да вземате предвид нулите , можете да комбинирате AVERAGE с условие за масив в диапазона Sales:

=СРЕДНО(АКО(Продажби<>0;Продажби))

Получената матрица от SI съдържа само стойностите, различни от 0, които са тези, които PROVIDEIO в крайна сметка използва за изчисляване на средната стойност.

Преброяване на разликите между два диапазона

Да предположим, че имате два диапазона с еднакъв размер и форма, наречени MyData и YourData, и искате да знаете с колко клетки се различават . Формула за масив решава това по следния начин:

=SUM(IF(МоитеДанни=ВашитеДанни;0;1))

Функцията IF генерира масив, където всяко съвпадение става 0, а всяко несъответствие става 1. Сумирането на масива ви дава броя на различните клетки.

Съществува и по-компактна версия, която директно използва неравномерното сравнение ( <> ), умножено по 1:

=SUM(1*(МоитеДанни<>ВашитеДанни))

За пореден път се използва трикът , че TRUE е равно на 1, а FALSE е равно на 0, когато се умножи по число.

Намерете максимума и неговата позиция в даден диапазон.

С формулите за масиви можете не само да намерите максималната стойност в набор, но и точната ѝ позиция в листа; можете също да използвате функцията RANK , за да сортирате и локализирате позиции.

За да намерите номера на реда с максимума в диапазона на колона с име Данни, можете да използвате:

=MIN(IF(Данни=MAX(Данни);ROW(Данни),»»))

Тази формула създава масив, където клетките, съдържащи максималната стойност, съхраняват номера на реда си, а останалите се оставят като празни низове. Функцията MIN намира най-малкото число в този масив, което съответства на първия ред, където се появява максималната стойност.

Ако искате директно да получите референтната клетка с максималната стойност, можете да обгърнете предишното изчисление с АДРЕС и КОЛОНА:

=АДРЕС(МИН(АКО(Данни=МАКС(Данни);РЕД(Данни),"")),КОЛОНА(Данни))

Създаване на формули за масиви с множество клетки стъпка по стъпка

В примерна работна книга с таблица с доставчици , типове превозни средства, продадени бройки и цени можете да илюстрирате как работи формула за масив, която заема няколко клетки едновременно.

Представете си, че сте копирали таблица, започвайки от клетка A1, със следните полета: Търговец, Тип превозно средство, Брой продадени бройки, Единична цена и Общо продажби , и че в колона E искате да изчислите продажбите на всеки ред, използвайки формула за масив.

В диапазона C2:C11 имате продадените количества, а в D2:D11 - цените; целта е да се попълни E2:E11 с произведението на всеки ред, без да се пишат формули една по една.

За да направите това като формула за масив в няколко клетки, първо изберете целия диапазон E2:E11 , след което въведете следното в лентата за формули:

=C2:C11*D2:D11

и потвърдете с Ctrl + Shift + Enter . Excel ще запълни всички клетки от E2 до E11 наведнъж с резултата, съответстващ на всеки ред.

Формула за масив от една клетка на същия пример

Започвайки от същата таблица, е възможно да се получи общата сума на продажбите, използвайки една единствена формула за масив в една клетка, например B13.

Вместо да пишете формули ред по ред, просто въведете следното в B13:

=СУМА(C2:C11*D2:D11)

и потвърдете с Ctrl + Shift + Enter . Excel вътрешно извършва умножението на всяка двойка клетки Cx*Dx и след това сумира всички тези произведения, за да даде общия резултат.

Как да отстраните грешки и да разберете сложна матрична формула

Когато една формула е дълга и донякъде загадъчна, е важно да можете да видите какво изчислява всяка част . Excel ви позволява да изчислявате части от формула, като използвате клавиша F9.

Номерът е да изберете конкретна част в лентата с формули (например, само B2:B11*C2:C11 ) и след това да натиснете F9 , така че Excel временно да замени този фрагмент с междинния резултат.

Като направите това във формула за масив, ще видите получения масив да се разгъва с всичките му елементи, което значително помага за разбирането на поведението на формулата и локализирането на евентуални грешки.

След като сте проверили избраната част, можете да натиснете Esc , за да излезете без запазване на промените, или Ctrl + Z, ако случайно сте потвърдили и искате да отмените оценката.

Константни масиви в Excel: как да ги създавате и използвате

В допълнение към използването на диапазони от клетки, Excel ви позволява да работите с константи за масиви , които са набори от фиксирани стойности, записани директно във формула и не се променят при копиране или преместване на формулата.

Константата тип масив може да съдържа числа, текст, логически стойности (TRUE/FALSE) или грешки , но не може да включва препратки към клетки, дефинирани имена, дати, функции или други масиви.

Съществуват хоризонтални едномерни константи (един ред), вертикални едномерни константи (една колона) и двумерни константи (блок от редове и колони) и те се различават по разделителите, използвани между елементите.

В испанските регионални настройки вертикалните масиви обикновено разделят елементите с точка и запетая (;) , докато в хоризонталните масиви може да се използва друг разделител (в някои настройки, обратната наклонена черта) или запетая, в зависимост от конфигурацията на системата.

Например, вертикална матрица с месеците от годината може да бъде изразена като:

={"Януари";"Февруари";"Март";"Април";"Май";"Юни";"Юли";"Август";"Септември";"Октомври";"Ноември";"Декември"}

Присвояване на име на константа на масив

За да улесните използването на голяма константа, можете да ѝ зададете име, като използвате диспечера на имена на Excel, като по този начин я използвате повторно, без да се налага да я въвеждате отново.

Процесът е прост: отидете в раздела Формули, използвайте опцията Дефинирано име или Присвояване на име , въведете желаното име и в полето „Отнася се до“ въведете директно константата на масива.

Например, можете да създадете име, наречено Месеци, което сочи към константата :

={"януари"\"февруари"\"март"\"април"\"май"\"юни"\"юли"\"август"\"септември"\"октомври"\"ноември"\"декември"}

След това просто изберете толкова клетки, колкото елементи има в масива, въведете името =Месеци и потвърдете като формула за масив, така че да се появят всички стойности, разпределени в листа.

Ако константата причинява проблеми, е добре да проверите използваните разделители и дали е избран подходящ диапазон за размера на масива, преди да въведете формулата с Ctrl + Shift + Enter.

Примери за използване на матрични константи

Константите ви позволяват да създавате мощни формули на много малко място. Например, за да сумирате трите най-високи стойности в диапазон, можете да използвате комбинация от LARGE и константа, която определя желания ред.

По подобен начин е възможно да се сумират N-малките стойности с SMALLEST , просто като се промени функцията, но се запази масивът от позиции, които искате да сумирате.

Друг типичен случай е преброяването колко пъти даден оценител (например Педро) е оценил с няколко специфични стойности, без да се налага да повтаря критериите в COUNTIFS отново и отново.

В редица оценки можете да използвате формула като тази:

=SUMA(CONTAR.SI.CONJUNTO(A2:A28;»Pedro»;C2:C28;{3\4\5}))

Тук константата {3\4\5} събира приетите оценки (3, 4 и 5) и прави формулата по-компактна и лесна за поддръжка, въпреки че можете да я разширите с повече стойности, ако е необходимо, като винаги спазвате максималния брой знаци на формулата.

Динамични формули за масиви в Excel 365 и 2021

С модерните версии на Excel ( Microsoft 365 и Excel 2021) беше въведена много важна промяна: динамични формули за масиви , които елиминират необходимостта от използване на Ctrl + Shift + Enter в повечето случаи.

Тези нови формули работят директно с диапазони и масиви и имат възможността автоматично да „преливат“ в съседни клетки, заемайки толкова редове и колони, колкото е необходимо, за да се покажат всички резултати.

Голямата разлика е, че просто въвеждате формулата в клетка и натискате Enter както обикновено; Excel запълва необходимия изходен диапазон и го маркира със специална рамка, показваща, че е диапазон на препълване.

Освен това се появиха нови функции, специално проектирани за работа с динамични масиви, като например FILTER , SORT , UNIQUE , SEQUENCE , SORTBY или RANDOMARRAY , между другото.

Тези функции ви позволяват да филтрирате, сортирате, генерирате последователни списъци или случайни числа и да връщате набори от резултати, без да е необходимо предварително да дефинирате размера на целевия диапазон.

Ключови разлики между класическите и динамичните матрични формули

Класическите формули за масиви изискват Ctrl+Shift+Enter , могат да бъдат малко по-трудни за четене и в много случаи са ограничени в способността си автоматично да разпределят резултатите, за разлика от съвременните функции като VLOOKUP и XLOOKUP.

Динамичните формули за масиви се пишат като нормални формули , потвърждават се само с Enter и изписват резултатите самостоятелно, без да се избират предишни диапазони или да се използват стари формули за масиви.

Друга важна разлика е, че динамичните функции изрично връщат масиви и се очакват като такива; ако по-стара работна книга използва функция, която връща масив от множество клетки, Excel може безшумно да приложи имплицитно пресичане.

При динамичните масиви Excel маркира тези стари случаи с оператора @ , който показва къде се е случвало това имплицитно пресичане, за да запази предишното поведение и да избегне неочаквани резултати.

Също така си струва да се отбележи, че динамичните формули за масиви са налични само в Excel 365 и Excel 2021 ; те не работят в по-ранни версии и може да се показват като остарели формули за масиви, ако тези работни книги се отварят на компютри, които не поддържат динамични масиви.

Настройване и използване на динамични формули за масиви

За да използвате динамична формула, просто изберете началната клетка , въведете формулата с функция като FILTER или SORT и натиснете Enter. Excel автоматично ще разпредели резултатите надолу и надясно.

Важно е да се уверите, че има свободно място около началната клетка, защото ако клетките, където трябва да се изхвърли масивът, вече съдържат данни, Excel ще покаже грешка за препълване (#OVERFLOW или подобна).

След като формулата е създадена, препълващият диапазон действа като блок : ако промените формулата в главната клетка, всички резултати се актуализират; ако искате да изтриете всичко, просто премахнете формулата от тази клетка.

Можете също да се обърнете към препълващия диапазон от други формули, като използвате оператора за препълване (например =SUM(F2#)), така че ако размерът му се увеличи или свие, формулите, които го използват, да се адаптират автоматично.

В програмни среди , библиотеки като Aspose.Cells ви позволяват да задавате и преизчислявате динамични формули за масиви чрез код, използвайки специфични методи за присвояването им на клетка и обновяването им, преди да извършите общото изчисление на формулата.

Разширени приложения на матриците: линейна алгебра и финанси

Работата с матрици в Excel не се ограничава до сумиране на диапазони или филтриране на стойности: тя може да се използва и за задачи с линейна алгебра и оптимизация, като например инверсия на матрици, решаване на системи от уравнения или изграждане на инвестиционни портфейли.

Квадратна матрица A може да бъде обърната в Excel с помощта на функцията MINVERSE , която изисква формула за масив (или динамична, в зависимост от версията) в диапазон със същия размер като оригиналната матрица.

За да получите обратната функция на матрица 3×3, разположена в B3:D5, трябва да изберете празен блок 3×3, да напишете =MINVERSE(B3:D5) и да потвърдите като матрична формула, като по този начин ще получите матрицата A ^-1.

Ако умножите оригиналната матрица по нейната обратна, използвайки MMULT върху подходящ ранг, ще получите единична матрица , аналогична на умножението на число по неговата обратна, което винаги дава 1.

По подобен начин можете да формулирате система от линейни уравнения в матрична форма, където A е матрицата на коефициентите, K е векторът от неизвестни и P е векторът от независими членове, и да я решите, използвайки отношението K = A ^-1 · P, като използвате MINVERSA и MMULT.

Във финансите този матричен подход се прилага например към задачи за оптимизация на портфолио , където ковариационната матрица σ _ij между ценните книжа, очакваната доходност μ _j и ограниченията за рентабилност се използват, за да се намери най-нискорисковият микс от активи за конкретна цел за рентабилност.

От функцията на Лагранж и извеждането на условията от първи ред, отново стигаме до матрична система от вида A·X = P, чието решение X = A ^-1 ·P ни дава оптималните тегла на всеки актив в портфолиото.

В пример с три акции A, B и C, с дадени ковариации и очаквана доходност, може да се определи инвестиционен вектор, така че портфолиото да получи очаквана доходност от 4% с минимална дисперсия , което води до комбинация, която се възползва от диверсификацията за намаляване на риска в сравнение с инвестирането в една акция.

Всичко това е реализирано със същите основни инструменти: MINVERSA, MMULT и формули за масиви , което подсилва идеята, че Excel може да бъде много компетентна платформа за числен анализ, когато се овладее използването на масиви.

След като обхванахме всичко - от основите на класическите матрични формули, през матрични константи и динамични матрици, до напредналите приложения в линейната алгебра и финансите, става съвсем ясно, че изучаването на добрата работа с матрици в Excel е инвестиция, която ви позволява да работите по-чисто, по-бързо и с решения, които често са извън обсега на конвенционалните формули.