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

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

За да станете опитен потребител на Excel, трябва да знаете как да използвате формули за масиви, които могат да извършват изчисления, които не можете да изпълнявате без формули за масиви. Следната статия се основава на серия от текстове за опитни потребители на Excel, написани от Колин Уилкокс и адаптирани от глави 14 и 15 от Формули за Excel 2002 – книга, написана от Джон Уокънбах, MVP за Excel.

Научете за формулите за масиви

Формулите за масиви често се наричат CSE (Ctrl+Shift+Enter) формули, защото вместо да натискате Enter, натискате Ctrl+Shift+Enter, за да завършите формулата.

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

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

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

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

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

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

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

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

Опитайте!

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

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

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

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

    Продавач

    Тип
    кола

    Брой на
    продадените

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

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

    Белишки

    Седан

    5

    33 000

    Купе

    4

    37 000

    Христов

    Седан

    6

    24 000

    Купе

    8

    21 000

    Тодоров

    Седан

    3

    29 000

    Купе

    1

    31 000

    Христозова

    Седан

    9

    24 000

    Купе

    5

    37 000

    Димитров

    Седан

    6

    33 000

    Купе

    8

    31 000

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

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

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

    =SUM(C2:C11*D2:D11)

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

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

Можете да изтеглите тази работна книга, като щракнете върху зеления бутон на Excel в черната лента на работната книга. След това можете да отворите файла в Excel, да изберете клетките, които съдържат формулите за масиви, и да натиснете Ctrl+Shift+Enter, за да накарате формулите да работят.

Ако работите в Excel, уверете се, че Лист1 е активен, след което изберете клетките E2:E11. Натиснете F2 и след това въведете формулата =C2:C11*D2:D11 в текущата клетка, E2. Ако натиснете клавиша Enter, ще видите, че той въвежда формулата в клетка E2 и показва 165000. Вместо това, след като въведете формулата, натиснете Ctrl+Shift+Enter вместо да натиснете само Enter. Сега ще видите резултатите в клетките E2:E11. Обърнете внимание, че в лентата за формули формулата се показва като {=C2:C11*D2:D11}. Това ви уведомява, че това е формула за масив, както е показано в следващата таблица.

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

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

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

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

=SUM(C2:C11*D2:D11)

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

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

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

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

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

  • За да изтриете формула за масив, изберете цялата формула (например =C2:C11*D2:D11), натиснете клавиша Delete и след това натиснете Ctrl+Shift+Enter.

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

Разширяване на формула за масив

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

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

Направете това в настолната програма на Excel (след като изтеглите работната книга на компютъра си).

Разгръщане на формула за масив
  1. Копирайте цялата таблица в клетка A1 в работен лист на Excel.

    Продавач

    Тип
    кола

    Брой на
    продадените

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

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

    Белишки

    Седан

    5

    33 000

    165 000

    Купе

    4

    37 000

    148 000

    Христов

    Седан

    6

    24 000

    144 000

    Купе

    8

    21 000

    168 000

    Тодоров

    Седан

    3

    29 000

    87 000

    Купе

    1

    31 000

    31 000

    Христозова

    Седан

    9

    24 000

    216 000

    Купе

    5

    37 000

    185 000

    Димитров

    Седан

    6

    33 000

    198 000

    Купе

    8

    31 000

    248 000

    Главчовски

    Седан

    2

    27 000

    Купе

    3

    30 000

    Калнъкова

    Седан

    4

    22 000

    Купе

    1

    41 000

    Симбаева

    Седан

    5

    32 000

    Купе

    3

    36 000

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

  2. Изберете клетка E18, въведете формулата за крайна обща сума =SUM(C2:C17*D2:D17) от клетка A20 и натиснете Ctrl+Shift+Enter.
    Отговорът трябва да бъде 2 131 000.

  3. Изберете диапазона от клетки, който съдържа текущата формула за масив (E2:E11) плюс празните клетки (E12:E17), които са съседни на новите данни. С други думи, изберете клетките E2:E17.

  4. Натиснете F2, за да превключите на режим на редактиране.

  5. В лентата за формули променете C11 на C17, сменете D11 с D17 и след това натиснете Ctrl+Shift+Enter.
    Excel актуализира формулата в клетките от E2 до E11 и поставя екземпляр на формулата в новите клетки от E12 до E17.

  6. Въведете формулата за масив =SUM(C2:C17*D2:D17) в клетка F17, така че да се отнася за клетките от ред 2 до ред 17, и натиснете Ctrl+Shift+Enter, за да изпълните формулата за масив.
    Новата крайна обща сума трябва да е 2 131 000.

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

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

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

  2. Изберете клетките от A1 до E1.

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

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

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

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

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

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

  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, за да превключите в режим на редактиране.
    Формулата за масив трябва да остане = C8:E10.

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

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

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

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

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

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

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

Данни

Това е

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

се събират,

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

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

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

=SUM(LEN(A2:A6))

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

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

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

=SUM(LEN(A2:A6))

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

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

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

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

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

    =SMALL(A5:A14;{1;2;3})

Стойностите 400, 475 и 500 се показват съответно в клетките от A16 до A18.

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

=SUM(SMALL(A 5 :A1 4 ;{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ;{1;2;3}))

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

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

  1. Изберете клетките от A1 до A3.

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

    =LARGE(A5:A14;ROW(INDIRECT("1:3")))

Стойностите 3200, 2700 и 2000 се показват съответно в клетките от A1 до A3.

В този момент би ви помогнала малко информация за функциите ROW и INDIRECT. Можете да използвате функцията ROW, за да създадете масив от последователни цели числа. Например изберете празна колона от 10 клетки в работната книга за упражнения, въведете тази формула за масив в клетки A5:A14 и след това натиснете 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.

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

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

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

В клетка A16 се показва текстът "сноп от клетки, които".

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

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

(MAX(LEN( A6 : A9 ))

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

LEN( A6:A9 )

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

И накрая, функцията INDEX приема следните аргументи: масив и ред и номер на колона в този масив. Диапазонът от клетки A6:A9 предоставя масива, функцията 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(Данни))

Най-горе на страницата

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

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

Вж. също

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

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

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

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

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

×