Указания и примери за формули за масиви

Указания и примери за формули за масиви

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

Започвайки с актуализацията от септември 2018 г. за Microsoft 365, всяка формула, която може да върне няколко резултата, автоматично ще ги разлее надолу или в съседни клетки. Тази промяна в поведението е придружена и от няколко нови функции за динамичен масив. Формулите за динамични масиви, независимо дали използват съществуващи функции, или функциите за динамичен масив, трябва да бъдат въвеждани само в една клетка, след което да бъдат потвърдени чрез натискане на Enter. По-рано старите формули за масиви изискват първо да изберете целия изходен диапазон, след което да потвърдите формулата с Ctrl+Shift+Enter. Те често се наричат CSE формули.

Можете да използвате формули за масиви, за да изпълнявате сложни задачи, като например:

  • Бързо създаване на примерни набори от данни.

  • Преброяване на броя на знаците, съдържащи се в диапазон от клетки.

  • Сумирайте само числата, които отговарят на определени условия, като например най-ниските стойности в диапазон, или числа, които попадат между горна и долна граница.

  • Сумиране на всяка N-та стойност в диапазон от стойности.

Следващите примери ви показват как да създавате формули за масиви от няколко клетки и една клетка. Където е възможно, включихме примери с някои от функциите за динамичен масив, както и съществуващи формули за масиви, въведени както като динамични, така и като стари масиви.

Изтеглете нашите примери

Изтеглете примерна работна книга с всички примери за формули за масиви в тази статия.

Това упражнение ви показва как да използвате многоклетъчни формули за масиви и такива с единствена клетка за изчисляване на набор от суми на продажби. Първият набор стъпки използва многоклетъчна формула за изчисляване на набор от междинни суми. Вторият набор използва формула с единствена клетка за изчисляване на обща сума.

  • Mногоклетъчна формула за масив

    Функция за многоклетъчен масив в клетка H10 =F10:F19*G10:G19 за изчисляване на броя на колите, продадени по единична цена

  • Тук изчисляваме общите продажби на купета и седани за всеки продавач, като въвеждаме =F10:F19*G10:G19 в клетка H10.

    Когато натиснете Enter,ще видите резултатите да се разлеят до клетки H10:H19. Обърнете внимание, че диапазонът на разливане е осветен с граница, когато изберете произволна клетка в диапазона на разливане. Може също да забележите, че формулите в клетките H10:H19 са сиви. Те са само там за справка, така че ако искате да настроите формулата, ще трябва да изберете клетка H10, където живее главната формула.

  • Формула за масив от една клетка

    Формула за масив от една клетка за изчисляване на обща сума с =SUM(F10:F19*G10:G19)

    В клетка H20 на примерната работна книга въведете или копирайте и поставете =SUM(F10:F19*G10:G19)и след това натиснете Enter.

    В този случай Excel умножава стойностите в масива (диапазона от клетки от F10 до G19) и след това използва функцията SUM, за да събере общите суми заедно. Резултатът е обща сума от $1 590 000 за продажбите.

    Този пример показва колко мощна може да бъде такъв тип формула. Да предположим например, че имате 1000 реда данни. Можете да сумирате част или всички данни, като създадете формула за масив в единствена клетка вместо да плъзгате формулата надолу през 1000 реда. Също така обърнете внимание, че формулата за единична клетка в клетка H20 е напълно независима от многоклетъчното формула (формулата в клетки от H10 до H19). Това е друго предимство на използването на формули за масиви – гъвкавост. Можете да промените другите формули в колона H, без да засягате формулата в H20. Също така може да е добра практика да имате независими общи суми по този начин, тъй като това помага за проверка на точността на вашите резултати.

  • Формулите за динамични масиви предлагат и следните предимства:

    • Съгласуваност (Consistency)    Ако щракнете върху някоя от клетките от H10 надолу, ще видите същата формула. Тази съгласуваност може да помогне за осигуряване на по-голяма точност.

    • Безопасност    Не можете да заместите компонент на формула за многоклетъчен масив. Например щракнете върху клетка H11 и натиснете Delete. Excel няма да промени резултата от масива. За да го промените, трябва да изберете горната лява клетка в масива или клетката H10.

    • По-малки размери на файловете    Често можете да използвате формула в единствена клетка вместо няколко междинни формули. Примерът за продажби на коли например използва една формула за масив, за да изчисли резултатите в колона E. Ако сте използвали стандартни формули, като например =F10*G10, F11*G11, F12*G12 и т.н., бихте използвали 11 различни формули, за да изчислите едни и същи резултати. Това не е голяма работа, но какво ще стане, ако имате хиляди редове общо? След това може да има голяма разлика.

    • Ефективност    Функциите за масиви могат да бъдат ефективен начин за създаване на сложни формули. Формулата за масив =SUM(F10:F19*G10:G19) е същата като тази: =SUM(F10*G10;F11*G11;F12*G12;F13*G13;F14*G14;F15*G15;F16*G16;F17*G17;F18*G18;F19*G19).

    • Разливане    Формулите за динамични масиви автоматично ще се разлеят в изходния диапазон. Ако вашите изходни данни са в Excel таблица, вашите формули за динамичен масив автоматично ще се преоразмерят, докато добавяте или премахвате данни.

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

Масивите от константи са компонент на формулите за масиви. Създавате масиви от константи чрез въвеждане на списък с елементи и последващото му ръчно ограждане с фигурни скоби ({ }), ето така:

={1,2;3,4,5} или ={"Януари";"Февруари";"Март"}

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

Следните процедури ще ви създадат известни навици за създаване на хоризонтални, вертикални и двумерни константи. Ще покажем примери с помощта на функцията SEQUENCE за автоматично генериране на масиви от константи, както и ръчно въведени масиви от константи.

  • Създаване на хоризонтална константа

    Използвайте работната книга от предишните примери или създайте нова работна книга. Изберете произволна празна клетка и въведете =SEQUENCE(1;5). Функцията SEQUENCE изгражда масив от 1 ред по 5 колони, който е същият като ={1,2,3,4,5}. Показва се следният резултат:

    Създаване на хоризонтална масивна константа с =SEQUENCE(1,5) или ={1,2;3,4,5}

  • Създаване на вертикална константа

    Изберете произволна празна клетка със стая под нея и въведете =SEQUENCE(5)или ={1;2;3;4;5}. Показва се следният резултат:

    Създаване на вертикална масивна константа с =SEQUENCE(5) или ={1;2;3;4;5}

  • Създаване на двумерна константа

    Изберете произволна празна клетка със стая отдясно и под нея и въведете =SEQUENCE(3,4). Виждате следния резултат:

    Създаване на константа за масив от 3 реда по 4 колони с =SEQUENCE(3;4)

    Можете също да въведете: или ={1,2,3,4;5,6,7,8;9,10,11,12}, но ще искате да обърнете внимание къде поставяте точка и запетая спрямо запетаи.

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

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

В клетка D9 въведохме =SEQUENCE(1,5,3;1),но можете също да въведете 3, 4, 5, 6 и 7 в клетки A9:H9. Няма нищо специално в този конкретен избор на номера, просто избрахме нещо друго освен 1-5 за разграничаване.

В клетка E11 въведете =SUM(D9:H9*SEQUENCE(1,5))или =SUM(D9:H9*{1,2;3;4,5}). Формулите връщат 85.

Използвайте масиви от константи във формули. В този пример използвахме =SUM(D9:H(*SEQUENCE(1;5))

Функцията SEQUENCE изгражда еквивалента на константата за масив {1,2,3,4,5}. Тъй Excel извършва операции по изрази, оградени първо в скоби, следващите два елемента, които се изпълняват, са стойностите на клетките в D9:H9 и оператора за умножение (*). В този момент формулата умножава стойностите в съхранения масив по съответните стойности в константата. Това е еквивалентно на:

=SUM(D9*1;E9*2;F9*3;G9*4;H9*5)или =SUM(3*1;4*2;5*3;6*4,7*5)

И накрая, функцията SUM добавя стойностите и връща 85.

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

=SUM(SEQUENCE(1;5;3;1)*SEQUENCE(1;5))или =SUM({3,4;5;6;7}*{1;2;3;4;5})

Елементи, които можете да използвате в масиви от константи

  • Масивите от константи могат да съдържат числа, текст, логически стойности (например TRUE и FALSE) и стойности за грешки, като например #N/A. Можете да използвате числа в цели, десетични и научни формати. Ако включите текст, трябва да го оградите с кавички ("текст").

  • Масивите от константи не могат да съдържат допълнителни масиви, формули или функции. С други думи, те могат да съдържат само текст или числа, разделени със знак \ или точка и запетая. Когато въведете формула, подобна на {1\2\A1:D4} или {1\2\SUM(Q2:Z8)}, Excel показва предупредително съобщение. Освен това, числените стойности не могат да съдържат знак за процент, знаци за долар, знаци \ или кръгли скоби.

Един от най-добрите начини да използвате масиви от константи е да ги наимените. Именуваните константи могат да се използват много по-лесно и могат да скриват част от сложността на формулите за масиви от другите. За да именувате константа за масив и да я използвате във формула, направете следното:

Отидете на Формули >дефинирани имена >Дефиниране на име. В полето Име въведете Тримесечие1. В полето Препраща към въведете следната константа (помнете да въведете ръчно фигурните скоби):

={"Януари"\"Февруари"\"Март"}

Диалоговият прозорец сега би трябвало да изглежда така:

Добавяне на наименувана константа за масив от формули > дефинирани имена > Name Manager > New

Щракнете върху OK,след което изберете произволен ред с три празни клетки и въведете =Quarter1.

Показва се следният резултат:

Използвайте наименувана константа за масив във формула, като например =Quarter1, където Quarter1 е дефинирано като ={"Януари";"Февруари";"Март"}

Ако искате резултатите да се разлеят вертикално вместо хоризонтално, можете да използвате =TRANSPOSE(тримесечие1).

Ако искате да покажете списък от 12 месеца, както можете да използвате при изграждане на финансов отчет, можете да базирате такъв от текущата година с функцията SEQUENCE. Правилното нещо за тази функция е, че въпреки че се показва само месецът, зад нея има валидна дата, която можете да използвате в други изчисления. Ще намерите тези примери в работните листове Наименувана масивна константа и Бърз примерен набор от данни в примерната работна книга.

=TEXT(DATE(YEAR(TODAY());SEQUENCE(1;12);1);"mmm")

Използвайте комбинация от функциите TEXT, DATE, YEAR, TODAY и SEQUENCE, за да създадете динамичен списък от 12 месеца

Това използва функцията DATE, за да създаде дата, базирана на текущата година, SEQUENCE създава масив от 1 до 12 за януари до декември, а след това функцията TEXT преобразува формата на показване в "мммм" (януари, февруари, март и т.н.). Ако искате да покажете пълното име на месеца, като например януари, ще използвате "мммм".

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

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

  • Няколко елемента в масив

    Въведете =SEQUENCE(1;12)*2или ={1,2;3;4;5,6,7;8;9;10;11;12}*2

    Можете също да разделите с (/), да добавите с (+) и да извадите с (-).

  • Повдигане на квадрат на елементите в масив

    Въведете =SEQUENCE(1;12)^2или ={1,2;3;4;5,6,7;8;9,10;11;12}^2

  • Намиране на квадратния корен на елементите с квадрат в масив

    Enter =SQRT(SEQUENCE(1;12)^2)или =SQRT({1,2;3;4;5,6,7;8;9,10;11;12}^2)

  • Транспониране на едномерен ред

    Въведете =TRANSPOSE(SEQUENCE(1;5))или =TRANSPOSE({1,2;3;4;5})

    Въпреки че сте въвели хоризонтална константа за масив, функцията TRANSPOSE конвертира константата за масив в колона.

  • Транспониране на едномерна колона

    Въведете =TRANSPOSE(SEQUENCE(5;1))или =TRANSPOSE({1;2;3;4;5})

    Въпреки че сте въвели вертикална константа за масив, функцията TRANSPOSE конвертира константата в ред.

  • Транспониране на двумерна константа

    Въведете =TRANSPOSE(SEQUENCE(3;4))или =TRANSPOSE({1,2;3;4;5,6,7;8;9;10;11;12})

    Функцията TRANSPOSE конвертира всеки ред в серия от колони.

Този раздел предоставя примери на основни формули за масиви

  • Създаване на масив от съществуващи стойности

    Примерът по-долу обяснява как да използвате формули за масиви, за да създадете нов масив от съществуващ масив.

    Въведете =SEQUENCE(3,6;10,10)или ={10,20,30,40,50;60;70,80,90,100,110;120;130,140,150,160,170,180}

    Не забравяйте да въведете { (отваряща скоч), преди да въведете 10, и } (затваряща скова), след като въведете 180, защото създавате масив от числа.

    След това въведете =D9#или =D9:I11 в празна клетка. Появява се масив от 3 x 6 клетки със същите стойности, които виждате в D9:D11. Знакът #се нарича оператор на разлят диапазон итова е начинът на Excel на препращане към целия диапазон от масиви, вместо да се налага да го въвеждате.

    Използване на оператора за разлят диапазон (#) за препратка към съществуващ масив

  • Създаване на константа за масив от съществуващи стойности

    Можете да вземете резултатите от разлята формула за масив и да я преобразувате в нейните съставни части. Изберете клетка D9, след което натиснете F2, за да превключите към режим на редактиране. След това натиснете F9, за да преобразувате препратките към клетки в стойности, Excel след това се преобразува в масив от константи. Когато натиснете Enter, формулата, =D9#, сега трябва да бъде ={10,20;30;40,50;60;70,80,90}.

  • Преброяване на знаците в диапазон от клетки

    Примерът по-долу ви показва как да преброите броя на знаците в диапазон от клетки. Това включва интервали.

    Преброяване на общия брой знаци в диапазон и други масиви за работа с текстови низове

    =SUM(LEN(C9:C13))

    В този случай функцията LEN връща дължината на всеки текстов низ във всяка от клетките в диапазона. След това функцията SUM сумира тези стойности и показва резултата (66). Ако искате да получите среден брой знаци, можете да използвате:

    =AVERAGE(LEN(C9:C13))

  • Съдържание на най-дългата клетка в диапазон C9:C13

    =INDEX(C9:C13;MATCH(MAX(LEN(C9:C13));LEN(C9:C13);0);1)

    Тази формула работи само когато диапазонът на данните съдържа единична колона от клетки.

    Нека разгледаме формулата по-внимателно, започвайки от най-вътрешния елемент и действайки навън. Функцията LEN връща дължината на всеки от елементите в диапазона от клетки D2:D6. Функцията MAX изчислява най-голямата стойност между тези елементи, която съответства на най-дългия текстов низ, който е в клетка D3.

    Тук е мястото, където нещата малко се усложняват. Функцията MATCH изчислява изместването (относителната позиция) на клетката, която съдържа най-дългия текстов низ. За да направи това, тя изисква три аргумента: търсена стойност, справочен масив и тип съвпадение. Функцията MATCH претърсва справочния масив за конкретна търсена стойност. В този случай търсената стойност е най-дългият текстов низ:

    MAX(LEN(C9:C13)

    и този низ се намира в този масив:

    LEN(C9:C13)

    Аргументът тип съвпадение в този случай е 0. Типът на съвпадението може да бъде стойност 1, 0 или -1.

    • 1 – връща най-голямата стойност, която е по-малка или равна на стойността на справката

    • 0 – връща първата стойност, точно равна на стойността за справка

    • -1 – връща най-малката стойност, която е по-голяма или равна на зададената справочна стойност

    • Ако пропуснете типа съвпадение, Excel я приема за 1.

    И накрая, функцията INDEX приема тези аргументи: масив и номер на ред и колона в този масив. Диапазонът от клетки C9:C13 предоставя масива, функцията MATCH предоставя адреса на клетката, а крайният аргумент (1) указва, че стойността идва от първата колона в масива.

    Ако искате да получите съдържанието на най-малкия текстов низ, ще заместите MAX в горния пример с MIN.

  • Намиране на n-те най-малки стойности в диапазон

    Този пример показва как да намерите трите най-малки стойности в диапазон от клетки, където е създаден масив от примерни данни в клетки B9:B18 с: =INT(RANDARRAY(10,1)*100). Обърнете внимание, че RANDARRAY е нестабилна функция, така че ще получавате нов набор от случайни числа всеки път, Excel изчислява.

    Excel за масив, за да намерите N-тата най-малка стойност: =SMALL(B9#;SEQUENCE(D9))

    Въведете =SMALL(B9#;SEQUENCE(D9), =SMALL(B9:B18;{1;2;3})

    Тази формула използва масив от константи, за да оцени функцията SMALL три пъти и да върне най-малките 3 членове в масива, който се съдържа в клетки B9:B18, където 3 е променлива стойност в клетка D9. За да намерите повече стойности, можете да увеличите стойността във функцията SEQUENCE или да добавите още аргументи към константата. Можете също да използвате допълнителни функция с тази формула, например SUM или AVERAGE. Например:

    =SUM(SMALL(B9#;SEQUENCE(D9))

    =AVERAGE(SMALL(B9#;SEQUENCE(D9))

  • Намиране на n-те най-големи стойности в диапазон

    За да намерите най-големите стойности в диапазон, можете да заместите функцията SMALL с функцията LARGE. Освен това следващият пример използва функциите ROW и INDIRECT.

    Въведете =LARGE(B9#;ROW(INDIRECT("1:3")))или =LARGE(B9:B18;ROW(INDIRECT("1:3")))

    В този момент би ви помогнала малко информация за функциите ROW и INDIRECT. Можете да използвате функцията ROW, за да създадете масив от последователни цели числа. Например изберете празно и въведете:

    =ROW(1:10)

    Формулата създава колона от 10 последователни цели числа. За да видите потенциален проблем, вмъкнете ред над диапазона, който съдържа формулата за масив (т. е. над ред 1). Excel настройва препратките към редове, а формулата сега генерира цели суми от 2 до 11. За да поправите проблема, добавяте към формулата функцията INDIRECT:

    =ROW(INDIRECT("1:10"))

    Функцията INDIRECT използва текстовите низове като свои аргументи (затова диапазонът 1:10 е ограден с кавички). Excel не настройва текстови стойности, когато вмъквате редове или по друг начин премествате формулата за масив. В резултат на това функцията ROW винаги генерира масива от цели числа, който искате. Можете също толкова лесно да използвате SEQUENCE:

    =SEQUENCE(10)

    Нека разгледаме формулата, която сте използвали по-рано – =LARGE(B9#;ROW(INDIRECT("1:3"))) – започвайки от вътрешните скоби и работи навън: Функцията INDIRECT връща набор от текстови стойности, в този случай стойностите от 1 до 3. Функцията ROW на свой ред генерира масив от три клетки на колони. Функцията LARGE използва стойностите в диапазона от клетки B9:B18 и се оценява три пъти по веднъж за всяка препратка, върната от функцията ROW. Ако искате да намерите още стойности, добавяте по-голям диапазон от клетки към функцията INDIRECT. И накрая, както при примерите SMALL, можете да използвате тази формула с други функции, като например SUM и AVERAGE.

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

    Функцията SUM в Excel не работи, когато се опитате да сумирате диапазон, който съдържа стойност на грешка, като например #VALUE! или #N/A. Този пример ви показва как да сумирате стойностите в диапазон с име Данни, които съдържат грешки:

    Използвайте масиви, за да се справите с грешките. Например =SUM(IF(ISERROR(Data);"";Data) ще сумира диапазона с име Данни дори ако включва грешки, като #VALUE! или #NA!.

  • =SUM(IF(ISERROR(Данни);"";Данни))

    Формулата създава нов масив, който съдържа първоначалните стойности без всички стойности за грешки. Започвайки от вътрешните функции и действайки навън, функцията ISERROR претърсва диапазона от клетки (Данни) за грешки. Функцията IF връща специфична стойност, ако зададеното от вас условие изчисли TRUE и друга стойност, ако то изчисли FALSE. В този случай, тя връща празен низ ("") за всички стойности на грешки, понеже те се изчисляват като TRUE, и връща оставащите стойности от диапазона (Данни), защото те се изчисляват като FALSE, което означава, че те не съдържат стойности за грешки. След това функцията SUM изчислява общото за филтрирания масив.

  • Преброяване на стойностите за грешки в диапазон

    Този пример е като предишната формула, но връща броя на стойностите на грешките в диапазон с име Данни, вместо да ги филтрира:

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

    Тази формула създава масив, който съдържа стойността 1 за клетките, които съдържат грешки, и стойността 0 за клетките, които не съдържат грешки. Можете да опростите формулата и да постигнете същия резултат, като премахнете третия аргумент за функцията IF ето така:

    =SUM(IF(ISERROR(Данни);1))

    Ако не зададете аргумента, функцията IF връща FALSE, ако дадена клетка не съдържа стойност за грешка. Можете да опростите формулата дори още повече:

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

    Тази версия работи, понеже TRUE*1=1 и FALSE*1=0.

Може да се нуждаете от сумиране на стойности на базата на условия.

Можете да използвате масиви, за да изчислите въз основа на определени условия. =SUM(IF(Продажби>0;Продажби)) ще сумира всички стойности, по-големи от 0, в диапазон, наречен "Продажби".

Например тази формула за масив сумира само положителните цели суми в диапазон с име "Продажби", който представя клетките E9:E24 в примера по-горе:

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

Функцията IF създава масив от положителни и грешни стойности. Функцията SUM по същество пренебрегва грешните стойности, понеже 0+0=0. Диапазонът от клетки, който използвате в тази формула, може да се състои от произволен брой редове и колони.

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

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

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

Можете също да създадете формули за масиви, които използват тип или условие ИЛИ. Можете например да сумирате стойности, които са по-големи от 0 ИЛИ по-малки от 2500:

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

Не можете да използвате функциите AND и OR във формули за масиви непосредствено, защото тези функции връщат единичен резултат – или TRUE, или FALSE, а формулите за масиви изискват масиви от резултати. Можете да заобиколите проблема, като използвате логиката, показана в предишната формула. С други думи, извършвате математически операции, като например добавяне или умножение на стойности, които отговарят на условието OR или AND.

Този пример ви показва как да премахвате нули от диапазон, когато се нуждаете от усредняване на стойностите в този диапазон. Формулата използва диапазон на данните, именуван "Продажби":

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

Функцията IF създава масив от стойности, които не са равни на 0, и след това подава тези стойности на функцията AVERAGE.

Тази формула за масив сравнява стойностите в два диапазона от клетки с име MyData и YourData и връща броя на разликите между двете. Ако съдържанието на двата диапазона е идентично, формулата връща 0. За да използвате тази формула, диапазоните от клетки трябва да са с един и същ размер и еднакъв размер. Ако например MyData е диапазон от 3 реда по 5 колони, YourData трябва също да бъде 3 реда по 5 колони:

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

Формулата създава нов масив със същия размер като диапазоните, които сравнявате. Функцията IF попълва масива със стойността 0 и стойността 1 (0 за несъвпадения и 1 за идентични клетки). След това функцията SUM връща сумата от стойностите в масива.

Можете да опростите формулата така:

=SUM(1*(MyData<>вашитеданни))

Подобно на формулата, която преброява стойностите за грешки в диапазон, тази формула работи, защото TRUE*1=1, а FALSE*1=0.

Тази формула за масив връща номера на реда на максималната стойност в диапазон от една колона, наречен "Данни":

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

Функцията IF създава нов масив, който съответства на диапазона на име "Данни". Ако съответната клетка съдържа максималната стойност в диапазона, масивът съдържа номера на реда. В противен случай масивът съдържа празен низ (""). Функцията MIN използва новия масив като свой втори аргумент и връща най-малката стойност, която съответства на номера на реда на максималната стойност в "Данни". Ако диапазонът на име "Данни" съдържа идентични максимални стойности, формулата връща реда на първата стойност.

Ако искате да върнете действителния адрес на клетката с максималната стойност, използвайте тази формула:

=ADDRESS(MIN(IF(Данни=MAX(Данни);ROW(Данни);""));COLUMN(Данни))

Ще намерите подобни примери в примерната работна книга в работния лист Разлики между набори от данни.

Това упражнение ви показва как да използвате многоклетъчни формули за масиви и такива с единствена клетка за изчисляване на набор от суми на продажби. Първият набор стъпки използва многоклетъчна формула за изчисляване на набор от междинни суми. Вторият набор използва формула с единствена клетка за изчисляване на обща сума.

  • Mногоклетъчна формула за масив

Копирайте цялата таблица по-долу и я поставете в клетка A1 в празен работен лист.

Продавач

Тип кола

Продадено число

Единична цена

Общо продажби

Белишки

Седан

5

33000

Купе

4

37000

Христов

Седан

6

24000

Купе

8

21000

Тодоров

Седан

3

29000

Купе

1

31000

Христозова

Седан

9

24000

Купе

5

37000

Димитров

Седан

6

33000

Купе

8

31000

Формула (крайна обща сума)

Крайна обща сума

'=SUM(C2:C11*D2:D11)

=SUM(C2:C11*D2:D11)

  1. За да видите Общи продажби на купета и седани за всеки продавач, изберете клетки E2:E11, въведете формулата =C2:C11*D2:D11и след това натиснете Ctrl+Shift+Enter.

  2. За да видите общата сума на всички продажби, изберете клетка F11, въведете формулата =SUM(C2:C11*D2:D11)и след това натиснете Ctrl+Shift+Enter.

Когато натиснете Ctrl+Shift+Enter,Excel огражда формулата с скоби ({ }) и вмъква екземпляр на формулата във всяка клетка от избрания диапазон. Това става много бързо, така че виждате в колоната E общото количество продажби за всеки тип кола за всеки един продавач. Ако изберете E2, след това E3, E4 и т.н., ще видите, че се показва същата формула: {=C2:C11*D2:D11}

Общите суми в колона E са изчислени чрез формула за масив

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

В клетка D13 на работната книга въведете следната формула и след това натиснете Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

В този случай Excel умножава стойностите в масива (диапазона от клетки от C2 до D11) и след това използва функцията SUM,за да сумира общите суми. Резултатът е обща сума от $1 590 000 за продажбите. Този пример показва колко мощна може да бъде такъв тип формула. Да предположим например, че имате 1000 реда данни. Можете да сумирате част или всички данни, като създадете формула за масив в единствена клетка вместо да плъзгате формулата надолу през 1000 реда.

Също така обърнете внимание, че формулата за единична клетка в клетка D13 е напълно независима от многоклетъчното формула (формулата в клетки от E2 до E11). Това е друго предимство на използването на формули за масиви – гъвкавост. Можете да промените формулите в колона E или да изтриете изцяло тази колона, без да засягате формулата в D13.

Формулите за масиви предлагат тези предимства:

  • Съгласуваност (Consistency)    Ако щракнете върху коя да е клетка от E2 надолу, виждате същата формула. Тази съгласуваност може да помогне за осигуряване на по-голяма точност.

  • Безопасност    Не можете да заместите компонент от многоклетъчна формула за масив. Например щракнете върху клетка E3 и натиснете Delete. Трябва или да изберете целия диапазон от клетки (от E2 до E11) и да промените формулата за целия масив, или да оставите масива както си е. Като допълнителна мярка за безопасност, трябва да натиснете Ctrl+Shift+Enter, за да потвърдите всяка промяна във формулата.

  • По-малки размери на файловете    Често можете да използвате формула в единствена клетка вместо няколко междинни формули. Например работната книга използва една формула за масив, за да изчисли резултатите в колона E. Ако бяхте използвали стандартни формули (например =C2*D2, C3*D3, C4*D4…), би трябвало да използвате 11 различни формули, за да изчислите същите резултати.

Като общо правило, формулите за масиви използват стандартния синтаксис на формула. Всички те започват със знак за равенство (=) и можете да използвате всяка от вградените функции на Excel във формулите за масиви. Ключовата разлика е, че когато използвате формула за масив, натиснете Ctrl+Shift+Enter, за да въведете формулата. Когато направите това, Excel огражда формулата за масив във фигурни скоби – ако въведете фигурните скоби ръчно, формулата ще се преобразува в текстов низ и няма да работи.

Функциите за масиви могат да бъдат ефективен начин за създаване на сложни формули. Формулата за масив =SUM(C2:C11*D2:D11) е същата като това: =SUM(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Важно: Натиснете Ctrl+Shift+Enter винаги, когато трябва да въведете формула за масив. Това се отнася както за многоклетъчните формули, така и за тези в единствена клетка.

Винаги когато работите с многоклетъчни формули, помнете също:

  • Изберете диапазона от клетки за съдържание на резултатите, преди да въведете формулата. Направихте това, когато създадохте многоклетъчната формула, когато избрахте клетките от E2 до E11.

  • Не можете да промените съдържанието на отделна клетка във формула за масив. За да пробвате това, изберете клетка E3 в работната книга и натиснете клавиша Delete. Excel показва съобщение, което ви казва, че не можете да промените част от масив.

  • Можете да преместите или изтриете цяла формула за масив, но не можете да преместите или изтриете част от нея. С други думи, за да свиете формула за масив, първо изтривате съществуващата формула и след това започвате отначало.

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

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

Понякога може да се нуждаете от разширяване на формула за масив. Изберете първата клетка в съществуващия диапазон от масиви и продължете, докато не изберете целия диапазон, до който искате да разширите формулата. Натиснете F2, за да редактирате формулата, след което натиснете CTRL+SHIFT+ENTER, за да потвърдите формулата, след като сте настроили диапазона на формулите. Ключът е да изберете целия диапазон, започвайки от клетката от горния ляв ъгъл в масива. Горната лява клетка е тази, която се редактира.

Формулите за масиви са чудесни, но те могат да имат някои недостатъци.

  • Понякога може да забравите да натиснете Ctrl+Shift+Enter. Може да се случи дори на най-опитните потребители на Excel. Помнете да натискате тази клавишна комбинация всеки път, когато въвеждате или редактирате формула за масив.

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

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

Масивите от константи са компонент на формулите за масиви. Създавате масиви от константи чрез въвеждане на списък с елементи и последващото му ръчно ограждане с фигурни скоби ({ }), ето така:

={1\2\3\4\5}

Вече знаете, че трябва да натиснете Ctrl+Shift+Enter, когато създавате формули за масиви. Тъй като масивите от константи са компонент на формулите за масиви, вие ограждате константите с фигурни скоби ръчно, като ги въвеждате. След това използвате Ctrl+Shift+Enter, за да въведете цялата формула.

Ако разделяте елементите със знак \, вие създавате хоризонтален масив (ред). Ако разделяте елементите с помощта на точка и запетая, създавате вертикален масив (колона). За да създадете двумерен масив, отделяте елементите във всеки ред с помощта на знака \, а всеки ред отделяте, като използвате точка и запетая.

Ето масив в един ред: {1,2,3,4}. Ето масив в единствена колона: {1;2;3;4}. А ето масив с с два реда и четири колони: {1\2\3\4;5\6\7\8}. В масива от два реда първият ред е 1, 2, 3 и 4, а вторият ред е 5, 6, 7 и 8. Единична точка и запетая разделя двата реда, между 4 и 5.

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

Следните процедури ще ви създадат известни навици за създаване на хоризонтални, вертикални и двумерни константи.

Създаване на хоризонтална константа

  1. В празен работен лист изберете клетки от A1 до E1.

  2. В лентата за формули въведете следната формула и след това натиснете Ctrl+Shift+Enter:

    ={1\2\3\4\5}

    В този случай трябва да въведете скобата за отваряне и затваряне ({ }), а Excel ще добавите втория набор за вас.

    Показва се следният резултат.

    Хоризонтален масив от константи във формула

Създаване на вертикална константа

  1. В работната книга изберете колона от пет клетки.

  2. В лентата за формули въведете следната формула и след това натиснете Ctrl+Shift+Enter:

    ={1;2;3;4;5}

    Показва се следният резултат.

    Вертикален масив от константи във формула за масив

Създаване на двумерна константа

  1. В работната книга изберете блок от клетки с четири колони ширина и три реда височина.

  2. В лентата за формули въведете следната формула и след това натиснете Ctrl+Shift+Enter:

    ={1\2\3\4;5\6\7\8;9\10\11\12}

    Виждате следния резултат:

    Двумерен масив от константи във формула за масив

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

Ето прост пример, който използва константи:

  1. В примерната работна книга създайте нов работен лист.

  2. В клетка A1 въведете 3 и след това въведете 4 в B1, 5 в C1, 6 в D1 и 7 в E1.

  3. В клетка A3 въведете следната формула и след това натиснете Ctrl+Shift+Enter:

    =SUM(A1:E1*{1\2\3\4\5})

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

    Формула за масив с масив от константи

    В клетка A3 се показва стойността 85.

Следващият раздел обяснява как работи формулата.

Току-що използваната формула съдържа няколко части.

Синтаксис на формула за масив с масив от константи

1. Функция

2. Съхранен масив

3. Оператор

4. Константа за масив

Последният елемент в скобите е масивната константа: {1,2,3,4,5}. Не забравяйте, Excel не огражда константите за масиви с скоби; всъщност ги въвеждате. Също така не забравяйте, че след като добавите константа към формула за масив, натиснете Ctrl+Shift+Enter, за да въведете формулата.

Тъй като Excel извършва първо операциите с изразите, които са заградени със скоби, следващите два елемента, които влизат в играта, са стойностите, съхранени в работната книга (A1:E1), и операторът. В този момент формулата умножава стойностите в съхранения масив по съответните стойности в константата. Това е еквивалентно на:

=SUM(A1*1;B1*2;C1*3;D1*4;E1*5)

И накрая, функцията SUM събира стойностите, а сумата 85 се показва в клетка A3.

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

=SUM({3\4\5\6\7}*{1\2\3\4\5})

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

=SUM(A1:E1*{1\2\3\4\5})

Масивите от константи могат да съдържат числа, текст, логически стойности (например TRUE и FALSE) и стойности за грешки (например #N/A). Можете да използвате числа в цял, десетичен и научен формат. Ако включите текст, трябва да оградите текста с двойни кавички (").

Масивите от константи не могат да съдържат допълнителни масиви, формули или функции. С други думи, те могат да съдържат само текст или числа, разделени със знак \ или точка и запетая. Когато въведете формула, подобна на {1\2\A1:D4} или {1\2\SUM(Q2:Z8)}, Excel показва предупредително съобщение. Освен това, числените стойности не могат да съдържат знак за процент, знаци за долар, знаци \ или кръгли скоби.

Един от най-добрите начини да използвате масиви от константи е да ги наимените. Именуваните константи могат да се използват много по-лесно и могат да скриват част от сложността на формулите за масиви от другите. За да именувате константа за масив и да я използвате във формула, направете следното:

  1. В раздела Формули, в групата Дефинирани имена щракнете върху Дефиниране на име.
    Появява се диалоговият прозорец Дефиниране на име.

  2. В полето Име въведете Тримесечие1.

  3. В полето Препраща към въведете следната константа (помнете да въведете ръчно фигурните скоби):

    ={"Януари"\"Февруари"\"Март"}

    Съдържанието на диалоговия прозорец сега изглежда така:

    диалоговият прозорец "дефиниране на име" с формула

  4. Щракнете върху OK и след това изберете ред с три празни клетки.

  5. Въведете следната формула и след това натиснете Ctrl+Shift+Enter.

    =Тримесечие1

    Показва се следният резултат.

    именуван масив, въведен като формула

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

Когато масивите ви от константи не работят, потърсете следните проблеми:

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

  • Може да сте избрали диапазон от клетки, който не съответства на броя на елементите в константата. Ако например изберете шест клетки за използване в константа от пет клетки, в празната клетка се показва стойността за грешка #N/A. Обратно, ако изберете твърде малко клетки, Excel пропуска стойностите, които нямат съответна клетка.

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

Умножаване на всеки елемент в масив

  1. Създайте нов работен лист и след това изберете блок от празни клетки с ширина от четири колони и височина от три реда.

  2. Въведете следната формула и след това натиснете Ctrl+Shift+Enter:

    ={1\2\3\4;5\6\7\8;9\10\11\12}*2

Повдигане на квадрат на елементите в масив

  1. Изберете блок от празни клетки с ширина от четири колони и височина от три реда.

  2. Въведете следната формула за масив и след това натиснете Ctrl+Shift+Enter:

    ={1\2\3\4;5\6\7\8;9\10\11\12}*{1\2\3\4;5\6\7\8;9\10\11\12}

    Друга възможност е да въведете тази формула за масив, която използва оператора карета (^):

    ={1\2\3\4;5\6\7\8;9\10\11\12}^2

Транспониране на едномерен ред

  1. Изберете колона от пет празни клетки

  2. Въведете следната формула и след това натиснете Ctrl+Shift+Enter:

    =TRANSPOSE({1\2\3\4\5})

    Въпреки че сте въвели хоризонтална константа за масив, функцията TRANSPOSE конвертира константата за масив в колона.

Транспониране на едномерна колона

  1. Изберете ред от пет празни клетки

  2. Въведете следната формула и след това натиснете Ctrl+Shift+Enter:

    =TRANSPOSE({1;2;3;4;5})

Въпреки че сте въвели вертикална константа за масив, функцията TRANSPOSE конвертира константата в ред.

Транспониране на двумерна константа

  1. Изберете блок от клетки с ширина от три колони и височина от четири реда.

  2. Въведете следната константа и след това натиснете Ctrl+Shift+Enter:

    =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

    Функцията TRANSPOSE конвертира всеки ред в серия от колони.

Този раздел предоставя примери на основни формули за масиви

Създаване на масиви и масиви от константи от съществуващи стойности

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

Създаване на масив от съществуващи стойности

  1. В работен лист на Excel изберете клетки C8:E10 и въведете тази формула:

    ={10\20\30;40\50\60;70\80\90}

    Не забравяйте да въведете { (отваряща фигурна скоба), преди да въведете 10, и } (затваряща фигурна скоба), след като въведете 90, тъй като създавате масив от числа.

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

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Изберете диапазона от клетки от C1 до E3.

  4. Въведете следната формула в лентата за формули и след това натиснете Ctrl+Shift+Enter:

    =C8:E10

    3x3 масив от клетки се появява в клетки от C1 до E3 със същите стойности, които виждате в C8 до E10.

Създаване на константа за масив от съществуващи стойности

  1. Когато клетките C1:C3 са избрани, натиснете F2, за да превключите към режим на редактиране. 

  2. Натиснете F9, за да преобразувате препратките към клетки в стойности. Excel конвертира стойностите в константа за масив. Формулата сега трябва да бъде ={10,20,30;40,50,60;70,80,90}.

  3. Натиснете Ctrl+Shift+Enter, за да въведете константата за масив като формула за масив.

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

Следващият пример ви показва как се изчислява броят на знаците, включително интервали, в диапазон от клетки.

  1. Копирайте цялата таблица и я поставете в клетка A1 на работен лист.

    Данни

    Това е

    набор от клетки, които

    се събират,

    за да съставят

    едно-единствено изречение.

    Общ брой знаци в A2:A6

    =SUM(LEN(A2:A6))

    Клетка с най-дълго съдържание (A3)

    =INDEX(A2:A6;MATCH(MAX(LEN(A2:A6));LEN(A2:A6);0);1)

  2. Изберете клетка A8 и след това натиснете Ctrl+Shift+Enter, за да видите общия брой знаци в клетките A2:A6 (66).

  3. Изберете клетка A10 и след това натиснете Ctrl+Shift+Enter, за да видите съдържанието на най-дългите клетки A2:A6 (клетка A3).

Следната формула се използва в клетка A8 преброяване на общия брой знаци (66) в клетки от A2 до A6.

=SUM(LEN(A2:A6))

В този случай функцията LEN връща дължината на всеки текстов низ на клетките в диапазона. След това функцията SUM сумира тези стойности и показва резултата (66).

Намиране на n-те най-малки стойности в диапазон

Този пример показва как да се намерят трите най-малки стойности в диапазон от клетки.

  1. Въведете някои случайни числа в клетки A1:A11.

  2. Изберете клетки от C1 до C3. Този набор от клетки ще съдържа резултатите, върнати от формулата за масив.

  3. Въведете следната формула и след това натиснете Ctrl+Shift+Enter:

    =SMALL(A1:A11;{1;2;3})

Тази формула използва масив от константи, за да оцени функцията SMALL три пъти и да върне най-малките (1), вторите най-малки (2) и третите най-малки (3) членове в масива, който се съдържа в клетки A1:A10 За да намерите повече стойности, добавяте още аргументи към константата. Можете също да използвате допълнителни функция с тази формула, например SUM или AVERAGE. Например:

=SUM(SMALL(A1:A10;{1;2;3})

=AVERAGE(SMALL(A1:A10;{1;2;3})

Намиране на n-те най-големи стойности в диапазон

За да намерите най-големите стойности в диапазон, можете да заместите функцията SMALL с функцията LARGE. Освен това следващият пример използва функциите ROW и INDIRECT.

  1. Изберете клетки от D1 до D3.

  2. В лентата за формули въведете тази формула и след това натиснете Ctrl+Shift+Enter:

    =LARGE(A1:A10;ROW(INDIRECT("1:3")))

В този момент би ви помогнала малко информация за функциите ROW и INDIRECT. Можете да използвате функцията ROW, за да създадете масив от последователни цели числа. Например изберете празна колона от 10 клетки във вашата работна книга за упражнение, въведете тази формула за масив и след това натиснете Ctrl+Shift+Enter:

=ROW(1:10)

Формулата създава колона от 10 последователни цели числа. За да видите потенциален проблем, вмъкнете ред над диапазона, който съдържа формулата за масив (т. е. над ред 1). Excel настройва препратките към редове, а формулата генерира цели числа от 2 до 11. За да поправите проблема, добавяте към формулата функцията INDIRECT:

=ROW(INDIRECT("1:10"))

Функцията INDIRECT използва текстови низове като свои аргументи (това е причината, поради която диапазонът 1:10 е ограден с двойни кавички). Excel не настройва текстови стойности, когато вмъквате редове или по друг начин премествате формулата за масив. В резултат на това функцията ROW винаги генерира масива от цели числа, който искате.

Нека да разгледаме формулата, която сте използвали по-рано – =LARGE(A5:A14;ROW(INDIRECT("1:3"))) – започвайки от вътрешните скоби и работи навън: Функцията INDIRECT връща набор от текстови стойности, в този случай стойностите от 1 до 3. Функцията ROW от своя страна генерира триклетъчен колонен масив. Функцията LARGE използва стойностите в диапазона от клетки A5:A14 и се оценява три пъти по веднъж за всяка препратка, върната от функцията ROW. Стойностите 3200, 2700 и 2000 се връщат в триклетъчен колонен масив. Ако искате да намерите още стойности, добавяте по-голям диапазон от клетки към функцията INDIRECT.

Както при по-ранни примери, можете да използвате тази формула с други функции, като например SUM и AVERAGE.

Намиране на най-дългия текстов низ в диапазон от клетки

Върнете се към по-ранния пример за текстов низ, въведете следната формула в празна клетка и натиснете Ctrl+Shift+Enter:

=INDEX(A2:A6;MATCH(MAX(LEN(A2:A6));LEN(A2:A6);0);1)

Появява се текстът "куп клетки, които" се появява.

Нека разгледаме формулата по-внимателно, започвайки от най-вътрешния елемент и действайки навън. Функцията LEN връща дължината на всеки от елементите в диапазона от клетки A2:A6. Функцията MAX изчислява най-голямата стойност между тези елементи, която съответства на най-дългия текстов низ, който е в клетка A3.

Тук е мястото, където нещата малко се усложняват. Функцията MATCH изчислява изместването (относителната позиция) на клетката, която съдържа най-дългия текстов низ. За да направи това, тя изисква три аргумента: търсена стойност, справочен масив и тип съвпадение. Функцията MATCH претърсва справочния масив за конкретна търсена стойност. В този случай търсената стойност е най-дългият текстов низ:

(MAX(LEN(A2:A6))

и този низ се намира в този масив:

LEN(A2:A6)

Аргументът за тип съвпадение е 0. Типът съвпадение може да е една от стойностите 1, 0 или -1. Ако зададете 1, MATCH връща най-голямата стойност, която е по-малка или равна на търсената стойност. Ако зададете 0, MATCH връща първата стойност, която е точно равна на търсената стойност. Ако зададете -1, MATCH търси най-малката стойност, която е по-голяма или равна на зададената стойност за търсене. Ако пропуснете типа съвпадение, Excel я приема за 1.

И накрая, функцията INDEX приема следните аргументи: масив и ред и номер на колона в този масив. Диапазонът от клетки A2:A6 предоставя масива, функцията MATCH предоставя адреса на клетката, а крайният аргумент (1) указва, че стойността идва от първата колона в масива.

Този раздел предоставя примери на разширени формули за масиви

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

Функцията SUM в Excel не работи, когато пробвате да сумирате диапазон, който съдържа стойност за грешка, например #N/A. Този пример ви показва как да сумирате стойностите в диапазон с име "Данни", който съдържа грешки.

=SUM(IF(ISERROR(Данни);"";Данни))

Формулата създава нов масив, който съдържа първоначалните стойности без всички стойности за грешки. Започвайки от вътрешните функции и действайки навън, функцията ISERROR претърсва диапазона от клетки (Данни) за грешки. Функцията IF връща специфична стойност, ако зададеното от вас условие изчисли TRUE и друга стойност, ако то изчисли FALSE. В този случай, тя връща празен низ ("") за всички стойности на грешки, понеже те се изчисляват като TRUE, и връща оставащите стойности от диапазона (Данни), защото те се изчисляват като FALSE, което означава, че те не съдържат стойности за грешки. След това функцията SUM изчислява общото за филтрирания масив.

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

Този пример е подобен на предишната формула, но връща броя на стойностите за грешки в диапазона, наречен "Данни", вместо да го филтрира:

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

Тази формула създава масив, който съдържа стойността 1 за клетките, които съдържат грешки, и стойността 0 за клетките, които не съдържат грешки. Можете да опростите формулата и да постигнете същия резултат, като премахнете третия аргумент за функцията IF ето така:

=SUM(IF(ISERROR(Данни);1))

Ако не зададете аргумента, функцията IF връща FALSE, ако дадена клетка не съдържа стойност за грешка. Можете да опростите формулата дори още повече:

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

Тази версия работи, понеже TRUE*1=1 и FALSE*1=0.

Сумиране на стойности на базата на условия

Може да се нуждаете от сумиране на стойности на базата на условия. Например тази формула за масив сумира само положителните цели числа в диапазона с име "Продажби":

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

Функцията IF създава масив от положителни стойности и погрешни стойности. Функцията SUM по същество пренебрегва грешните стойности, понеже 0+0=0. Диапазонът от клетки, който използвате в тази формула, може да се състои от произволен брой редове и колони.

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

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

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

Можете също да създадете формули за масиви, които използват тип или условие ИЛИ. Можете например да сумирате стойности, които са по-малки от 5 и по-големи от 15:

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

Функцията IF намира всички стойности по-малки от 5 и по-големи от 15 и след това подава тези стойности на функцията SUM.

Не можете да използвате функциите AND и OR във формули за масиви непосредствено, защото тези функции връщат единичен резултат – или TRUE, или FALSE, а формулите за масиви изискват масиви от резултати. Можете да заобиколите проблема, като използвате логиката, показана в предишната формула. С други думи, можете да извършите математически операции, като например събиране или умножение, върху стойности, които отговарят на условията ИЛИ или И.

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

Този пример ви показва как да премахвате нули от диапазон, когато се нуждаете от усредняване на стойностите в този диапазон. Формулата използва диапазон на данните, именуван "Продажби":

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

Функцията IF създава масив от стойности, които не са равни на 0, и след това подава тези стойности на функцията AVERAGE.

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

Тази формула за масив сравнява стойностите в два диапазона от клетки с име MyData и YourData и връща броя на разликите между двете. Ако съдържанието на двата диапазона е идентично, формулата връща 0. За да използвате тази формула, диапазоните от клетки трябва да са с един и същ размер и със същия размер (например ако MyData е диапазон от 3 реда по 5 колони, YourData трябва също да бъде 3 реда по 5 колони):

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

Формулата създава нов масив със същия размер като диапазоните, които сравнявате. Функцията IF попълва масива със стойността 0 и стойността 1 (0 за несъвпадения и 1 за идентични клетки). След това функцията SUM връща сумата от стойностите в масива.

Можете да опростите формулата така:

=SUM(1*(MyData<>вашитеданни))

Подобно на формулата, която преброява стойностите за грешки в диапазон, тази формула работи, защото TRUE*1=1, а FALSE*1=0.

Намиране на местоположението на максималната стойност в диапазон

Тази формула за масив връща номера на реда на максималната стойност в диапазон от една колона, наречен "Данни":

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

Функцията IF създава нов масив, който съответства на диапазона на име "Данни". Ако съответната клетка съдържа максималната стойност в диапазона, масивът съдържа номера на реда. В противен случай масивът съдържа празен низ (""). Функцията MIN използва новия масив като свой втори аргумент и връща най-малката стойност, която съответства на номера на реда на максималната стойност в "Данни". Ако диапазонът на име "Данни" съдържа идентични максимални стойности, формулата връща реда на първата стойност.

Ако искате да върнете действителния адрес на клетката с максималната стойност, използвайте тази формула:

=ADDRESS(MIN(IF(Данни=MAX(Данни);ROW(Данни);""));COLUMN(Данни))

Потвърждение

Части от тази статия са базирани на серия от колони на Power User на Excel, написани от Колин Уилкокс и адаптирани от глави 14 и 15 от формулите на Excel 2002 г., книга, написана от John Walkenbach, бивш Excel MVP.

Имате нужда от още помощ?

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

Вж. също

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

Формули за динамични масиви спрямо стари формули за масиви на CSE

FILTER функция

RANDARRAY функция

SEQUENCE функция

SORT функция

SORTBY функция

UNIQUE функция

Грешки #SPILL! в Excel

Implicit intersection operator: @

Общ преглед на формулите

Нуждаете се от още помощ?

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×