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

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

• 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(Данни))