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

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

Забележка: Бихме искали да ви осигурим най-новото помощно съдържание възможно най-бързо на вашия собствен език. Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас, в дъното на тази страница? Ето статията на английски за бърза справка.

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

Започвайки с актуализацията от септември 2018 за Office 365, всяка формула, която може да върне множество резултати, автоматично ще ги прелива надолу или през съседните клетки. Тази промяна в поведението е съпроводена и от няколко нови динамични функцииза масиви. Динамичните формули за масиви, независимо дали използват съществуващи функции, или динамичните функции за масиви, трябва да бъдат въведени само в единична клетка, след което се потвърждават чрез натискане на клавиша Enter. По-ранни формули за масиви изискват първо да изберете целия диапазон от резултати, а след това да потвърдите формулата с CTRL + SHIFT + ENTER. Те обикновено се наричат формулите пт .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    • Безопасност    Не можете да заместите компонент на многоклетъчна формула за масив. Например щракнете върху клетка Н11 и натиснете клавиша 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 * Г17).

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

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

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

= {1 \ 2 \ 3 \ 4 \ 5} или = {"януари"; "февруари"; "март"}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= SUM (последователност (1; 5; 3; 1) * последователност (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 показва предупредително съобщение. Освен това, числените стойности не могат да съдържат знак за процент, знаци за долар, знаци \ или кръгли скоби.

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

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

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

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

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

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

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

Използвайте наименуван масив от константи във формула, като например = Тримесечие1, където Тримесечие1 е дефиниран като = {"януари"; "февруари"; "март"}

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

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

= TEXT (дата (година (TODAY ()); последователност (1; 12); 1); "Ммм")

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

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

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

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

  • Кратно на всеки елемент в масив

    Enter = последователност (1, 12) * 2или = {1 \ 2 \ 3 \ 4; 5, 6, 7, 8; 9, 10, 11; 12} 2 .

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

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

    Enter = последователност (1, 12) ^ 2или = {1 \ 2 \ 3 \ 4; 5, 6, 7, 8; 9, 10, 11; 12} ^ 2

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

    Enter =SQRT(последователност (1; 12) ^ 2)или = SQRT ({1 \ 2 \ 3 \ 4; 5; 6; 7, 8; 9; 10; 11; 12} ^ 2)

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

    Enter = транспониране (последователност (1; 5))или = транспониране ({1 \ 2 \ 3 \ 4 \ 5})

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

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

    Enter = транспониране (последователност (5; 1))или = транспониране ({1; 2; 3; 4; 5})

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

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

    Enter = транспониране (последователност (3; 4))или = транспониране ({1 \ 2 \ 3 \ 4; 5; 6; 7, 8; 9, 10, 11, 12})

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

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

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

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

    Enter = последователност (3, 6, 10, 10)или = {10, 20, 30, 40, 50, 60; 70; 80; 90100110120; 130140150160170180}

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

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

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

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

    Можете да изведете резултатите от разливане на формула за масив и да я преобразувате в нейни компоненти. Изберете Cell 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 – Връща най-голямата стойност, която е по-малка или равна на справката Val

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

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

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

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

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

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

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

    Формула за масив на Excel, за да намерите най-малката стойност: = SMALL (B9 #; ПОРЕДЕН (D9))

    Enter = Small (B9 #; последователност (D9); = Small (B9: Б18; {1; 2; 3})

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

    = SUM (SMALL (B9 #; ПОРЕДЕН (D9))

    = AVERAGE (SMALL (B9 #; ПОРЕДЕН (D9))

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

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

    Enter = Large (B9 #; Row (индиректен ("1:3")))или = Large (B9: Б18; Row (непряко ("1:3" )))

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

    =ROW(1:10)

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

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

    ИНДИРЕКТНата функция използва текстови низове като аргументите си (поради което диапазонът 1:10 е ограден с кавички). Excel не настройва текстови стойности, когато вмъквате редове или по друг начин премествате формулата за масив. В резултат на това функцията ROW винаги генерира масива от цели числа, който искате. Можете също така лесно да използвате последователност:

    = ПОСЛЕДОВАТЕЛНОСТ (10)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Например тази формула за масив сумира само положителните числа в диапазон с име Sales, което представлява клетки E9: E24 в примера по-горе:

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

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

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

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

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

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

= SUM (IF ((Sales>0) + (Sales<2500); Sales))

Не можете да използвате функциите 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 * (MyData<>YourData))

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

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

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

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

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

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

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

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

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

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

Sales Човек

Car Тип

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

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

Общо Sales

Белишки

Седан

5

33000

Купе

4

37000

Христов

Седан

6

24000

Купе

8

21000

Тодоров

Седан

3

29000

Купе

1

31000

Христозова

Седан

9

24000

Купе

5

37000

Димитров

Седан

6

33000

Купе

8

31000

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

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

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

=SUM(C2:C11*D2:D11)

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

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

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

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

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

В клетка D13 на работната книга въведете следната формула и след това натиснете CTRL + SHIFT + ENTER:

=SUM(C2:C11*D2:D11)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

До сега трябва да натиснете CTRL + SHIFT + ENTER , когато създавате формули за масиви. Тъй като масивите от константи са компонент на формулите за масиви, вие ограждате константите с фигурни скоби ръчно, като ги въвеждате. След това използвайте CTRL + SHIFT + ENTER , за да въведете цялата формула.

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

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

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

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

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

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

  2. В лентата за формули въведете следната формула и след това натиснете CTRL + SHIFT + ENTER:

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

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

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

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

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

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

  2. В лентата за формули въведете следната формула и след това натиснете CTRL + SHIFT + ENTER:

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

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

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

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

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

  2. В лентата за формули въведете следната формула и след това натиснете CTRL + SHIFT + ENTER:

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

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

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

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

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

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

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

  3. В клетка A3 въведете следната формула и след това натиснете CTRL + SHIFT + ENTER:

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

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

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

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

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

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

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

1. Функция

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

3. Оператор

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  5. Въведете следната формула и след това натиснете CTRL + SHIFT + ENTER.

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

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

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

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

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

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

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

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

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

  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

    В клетки от C1 до E3 се показва набор от 3x3 клетки със същите стойности, които виждате от 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})

Тази формула използва масив от константи, за да оцени малките функции три пъти и да върне най-малката (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 (ИНДИРЕКТЕН ("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 (непряко ("1:3")))) – започвайки от вътрешните скоби и работите пасивно: индиректната функция връща набор от текстови стойности, в този случай стойностите от 1 до 3. Функцията Row от своя ред създава колонен масив с три клетки. Функцията Large използва стойностите в диапазона от клетки A5: A14 и се изчислява три пъти, веднъж за всяка препратка, върната от функцията Row . Стойностите 3200, 2700 и 2000 се връщат към колонния масив с три клетки. Ако искате да намерите повече стойности, добавете по-голям диапазон от клетки към индиректната функция.

Както при предишните примери, можете да използвате тази формула с други функции, като например 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 * (MyData<>YourData))

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

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

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

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

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

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

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

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

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

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

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

Вж. също

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

Динамични формули за масиви срещу наследени формули за масиви

FILTER функция

RANDARRAY функция

SEQUENCE функция

SINGLE функция

SORT функция

SORTBY функция

UNIQUE функция

Грешки #SPILL! в Excel

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

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

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

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

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

×