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

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

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

  

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

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

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

  

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

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

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

• Динамичните формули за масиви също предлагат следните предимства:

  • Съгласуваност    Ако щракнете върху някоя от клетките от 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, масивът автоматично ще се преоразмери, когато добавяте или премахвате данни от диапазона за вашия масив.

  • #ПРЕНАСЯНЕ! грешка    Динамичните масиви вкараха #ПРЕНАСЯНЕ! грешка, което означава, че предвиденият диапазон за пренасяне е блокиран по някаква причина. Когато разрешите блокирането, формулата автоматично ще се пренесе.

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

={1\2\3\4\5} or ={"January"\"February"\"March"}

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

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

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

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

  

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

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

  

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

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

  

  Можете също да въведете: or ={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.

Функцията 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 се показва в клетка A3.

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

=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. В полето Препраща към въведете следната константа (помнете да въведете ръчно фигурните скоби):

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

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

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

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

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

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

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

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

Когато използвате именувана константа като формула за масив, не забравяйте да въведете знака за равенство, като =Тримесечие1, не само като Тримесечие1. Ако не го направите, 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

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

  Въведете =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 изчислява.

  

  Въведете =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(Данни);"";Данни))

  Формулата създава нов масив, който съдържа първоначалните стойности без всички стойности за грешки. Започвайки от вътрешните функции и действайки навън, функцията 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.

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

Тази формула за масив например сумира само положителните цели числа в диапазон с име "Продажби", който представлява клетките 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, а формулите за масиви изискват масиви от резултати. Можете да заобиколите проблема, като използвате логиката, показана в предишната формула. С други думи изпълнявате математически операции, като събиране или умножение на стойности, които отговарят на условието „ИЛИ“ или „И“.

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

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

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

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

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

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

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

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

Подобно на формулата, която преброява стойностите за грешки в диапазон, тази формула работи, защото 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}. 

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

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

=SUM(C2:C11*D2:D11)

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

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

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

• Съгласуваност    Ако щракнете върху коя да е клетка от 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).

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

• Изберете диапазона от клетки за съдържание на резултатите, преди да въведете формулата. Направихте това, когато създадохте многоклетъчната формула, когато избрахте клетките от 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.

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

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

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

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

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

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

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

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

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

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

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

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

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