Разбира се някой път Борили сте се със стотици формули в електронна таблица да генерирам сложен отчет. Ако знаех матричните формули, вероятно щеше да е много по-лесно. Те са едни от тези разширени функции на Excel които повечето потребители пренебрегват, но които правят цялата разлика, когато започнете да правите сериозни изчисления.
на формули за масиви Те позволяват на Excel да обработва много стойности едновременно, да извършва междинни изчисления „невидимо“ и да връща един или повече резултати, без да запълва работния лист с помощни колони. В началото изглеждат плашещи, но щом свикнете с тях, те се превръщат в невероятно мощен инструмент за сложни анализи, отчети и модели.
Какво е матрица и какво точно е матрична формула?
Преди да обсъдим матричните формули, трябва Разбиране на това какво е матрица в ExcelМатрицата е просто набор от стойности, третирани като блок: те могат да бъдат числа или текст и могат да бъдат в един ред, в една колона или в диапазон от няколко реда и колони.
Класически пример за матрица е списъкът с месеци от годината.Представете си формула за масив, съдържаща всички месеци: {"януари";"февруари";"март";"април";"май";"юни";"юли";"август";"септември";"октомври";"ноември";"декември"}. Ако изберете 12 клетки в колона, въведете ={"януари";…;"декември"} и потвърдете като формула за масив, Excel ще запълни всички тези клетки наведнъж.
Формулата за масив е формула, която работи с масиви, вместо с отделни стойности.Основната разлика в сравнение с „нормалните“ формули е, че може:
- Получаване на множество стойности като вход (цял диапазон или константа от няколко елемента).
- Извършване на множество вътрешни изчисления относно всеки от тези елементи.
- Връща единична стойност или цял масив от резултати.
Мислете за матричната формула като за масивно паралелно изчислениеExcel оценява елемент по елемент, запазва междинни резултати в паметта и ви показва само крайния резултат (клетка или блок от клетки), без да се налага да виждате или поддържате междинните стъпки.
Как да въведете и разпознаете формула за масив в Excel
Определящата характеристика на класическата матрична формула са къдравите скоби { } Виждате тези къдрави скоби във формулната лента, когато клетката е избрана. Не въвеждате тези скоби; Excel ги добавя автоматично, когато потвърдите правилно формулата.
За да въведете формула за масив в традиционните версии на Excel (преди динамичните формули), процесът е следният:
- Напишете формулата както обикновено.Например: =SUM(B2:B11*C2:C11).
- Не натискайте просто EnterТрябва да натиснете комбинацията Ctrl + Shift + Enter (Ctrl + Shift + Enter).
- Excel ще огради формулата с къдрави скоби. във формулната лента: {=SUM(B2:B11*C2:C11)}. Това показва, че се третира като формула за масив.
Има няколко важни детайла, които трябва да бъдат много ясни. за да не полудееш с тях:
- Ръчното писане на ключовете няма да проработи.Ако въведете { и } около формулата, Excel ще я третира като нормална формула; само че клавишната комбинация я превръща във формула за масив.
- При редактиране на формула за масив, къдравите скоби временно изчезват.След като го промените, ще трябва да натиснете отново Ctrl + Shift + Enter, за да го превърнете отново в матрица.
- Ако забравите да използвате Ctrl + Shift + EnterФормулата ще бъде оценена като нормална и вероятно ще използва само първия елемент от всеки диапазон, връщайки неправилен резултат.
- В масивни формули, които връщат множество резултати (няколко клетки), първо трябва да изберете целия изходен диапазон, да въведете формулата и след това да потвърдите с Ctrl + Shift + Enter.
Освен това можете да „шпионирате“ как се изчисляват части от матрична формула. като изберете фрагмент от формулата (например, B2:B11*C2:C11) в лентата с формули и натиснете F9Excel ще покаже резултата от тази част като масив от стойности. След това, с Esc, отменяте, за да не променяте нищо.
Формули за масиви с резултат в една клетка
Много от най-мощните приложения на формулите за масиви връщат една единствена стойност Но отдолу те управляват цели диапазони и извършват множество вътрешни операции. Нека разгледаме няколко често срещани модела, които си струва да овладеем.
Основен пример: общи продажби без помощни колони
Представете си проста диаграма на продажбитеКолона А съдържа продукта, колона Б - продадените бройки (B2:B11), а колона В - цената на единица (C2:C11). Искате да получите общата фактурирана сума за периода.
Традиционният начин би бил да се създаде колона със суми (единици * цена) и след това сумиране на тази колона. С формула за масив запазвате цялата помощна колона:
=СУМА(B2:B11*C2:C11)
След като го въведете в клетка, потвърдете с Ctrl + Shift + EnterExcel ще умножи всяка двойка клетки ред по ред (B2*C2, B3*C3 и т.н.), ще съхрани тези междинни суми вътрешно и накрая ще ги сумира всички, връщайки общата сума в една клетка.
Сумиране на диапазон, който съдържа грешки
Ако се опитате да сумирате диапазон, който включва грешки като #N/A, функцията SUM се проваля.С формула за масив можете да „игнорирате“ тези грешки и да сумирате само валидните стойности. Да предположим, че имате диапазон, наречен Данни, който съдържа числа и някои грешки:
{=SUM(IF(ISERROR(Данни);"";Данни))}
Логиката е следната:
- ISERROR(Data) връща масив от TRUE/FALSE в зависимост от грешката във всяка клетка.
- IF(ISERROR(Data);"";Data) генерира нова матрица където грешките се заменят с «» (празен низ) и се запазват правилните стойности.
- SUMA работи върху тази филтрирана матрицаигнорира празните низове и връща сумата само на здрави числови стойности.
Пребройте колко грешки има в даден диапазон
Следвайки същата идея както преди, можете също да преброите грешките в даден диапазон Повикване за данни:
{=SUM(IF(ISERRER(Данни);1;0))}
Тази формула създава масив от единици и нуликато поставите 1 в клетките с грешки и 0 в тези без грешки, и след това сумирате този масив. Можете да го опростите по няколко начина:
{=SUM(IF(ISERRER(Данни);1))}
В тази версия, когато ISERROR(Data) е FALSEФункцията IF връща директно FALSE, което SUM третира като 0. Тя все още може да бъде допълнително отрязана:
{=SUM(IF(ISERRER(Данни)*1))}
Това се възползва от факта, че TRUE*1=1 и FALSE*1=0ISERROR(Data) дава TRUE/FALSE, умножението по 1 дава 1/0, а SUM брои единиците (т.е. грешките).
Добавяне на стойности, които отговарят на едно или повече условия
Едно от най-типичните приложения на матричните формули е за извършване на много гъвкави условни суми., комбинирайки няколко логически теста, без винаги да се разчита на SUMIFS или SUMIFS.
Например, сумирайте само положителните стойности в диапазон, наречен „Продажби“:
{=SUM(IF(Продажби>0;Продажби))}
IF(Sales>0;Sales) генерира масив, в който се появяват положителни стойности и FALSE за останалитеSUM игнорира FALSE и събира само числата.
Ако трябва да приложите няколко условия „И“Можете да умножавате логически условия. Например, сумиране на продажби, по-големи от 0 и по-малки или равни на 5:
{=SUM((Продажби>0)*(Продажби<=5)*Продажби)}
Всеки логически израз връща TRUE/FALSEКогато ги умножите и след това умножите по „Продажби“, само клетките, които отговарят и на двете условия, допринасят със стойността си за общата сума (TRUE се държи като 1, FALSE като 0). Забележка: тази формула ще върне грешка, ако в диапазона има смесени нечислови клетки.
За условия от тип O можете да добавите логическите проверки.Например, сумиране на продажби по-малки от 5 или по-големи от 15:
{=SUM(IF((Продажби<5)+(Продажби>15);Продажби))}
Сумата (Продажби<5)+(Продажби>15) връща 1 за клетки, които отговарят на поне едно от условията и 0 за останалите. АКО запазва стойностите на Sales, когато сумата е 1 и FALSE в останалите случаи, което SUM игнорира.
Важно е да се разбере защо функциите AND и OR не се използват директно във формули за масиви.Тези функции връщат една стойност TRUE или FALSE, докато формулите за масиви изискват масив от логически резултати (по един на клетка). Следователно, условията се умножават или сумират елемент по елемент.
Изчислете средна стойност, изключвайки нулите
Друга типична нужда е да се изчислят средни стойности, без нулите да „замърсяват“ резултата.С формула за масив можете да филтрирате тези нули от диапазон, наречен „Продажби“:
{=СРЕДНО(АКО(Продажби<>0;Продажби))}
Функцията IF създава масив с ненулеви стойности и връща FALSE, където има нули. AVERAGE ще изчисли средната стойност само с числови стойности, игнорирайки FALSE.
Преброяване на разликите между два диапазона
Ако искате да знаете с колко клетки се различават два диапазона с еднакъв размерНапример, MyData и YourData, формула за масив решава това по много компактен начин:
{=SUM(IF(МоитеДанни=ВашитеДанни;0;1))}
Формулата сравнява двата диапазона позиция по позиция.Ако стойностите съвпадат, IF връща 0; ако не съвпадат, връща 1. SUM брои разликите. Можете да напишете по-кратък вариант:
{=SUM(1*(МоитеДанни<>ВашитеДанни))}
MyData<>YourData генерира TRUE, когато има разлики и FALSE, където всичко съвпада. Умножението по 1 преобразува тези логически стойности в 1 и 0, които SUM сумира, за да получи общите несъответствия.
Намерете позицията на максималната стойност в диапазон
Формулите за масиви също ви позволяват да локализирате позиции в диапазонНапример, номерът на реда, където се намира максималната стойност в диапазон от колона, наречена Данни:
{=MIN(IF(Данни=MAX(Данни);ROW(Данни);»»))}
Тази формула работи така:
- MAX(Данни) получава максималната стойност от диапазона.
- IF(Data=MAX(Data);ROW(Data);»») генерира масив с номера на реда за клетките, които съдържат този максимум, и «» за останалите.
- MIN, приложена към този масив, връща най-малкия номер на редТоест, първото появяване на максималната стойност.
Ако вместо номера на реда искате пълната препратка към клеткатаМожете да го обгърнете във функцията ADDRESS:
{=АДРЕС(МИН(АКО(Данни=МАКС(Данни);РЕД(Данни);»»));КОЛОНА(Данни))}
Тук изчисленият ред се комбинира с колоната от диапазона данни. за да върне препратка като „$C$15“.
Формули за масиви с резултати в множество клетки
В допълнение към формулите за масиви, които връщат една стойностИма формули, които при потвърждаване запълват целия диапазон наведнъж. Те са особено полезни за създаване на трансформирани таблици, извършване на изчисления ред по ред или конструиране на резултати, които зависят от повече от едно измерение.
Пример за едномерна матрица: месеци от годината
Вече видяхме, че вертикална едномерна матрица може да съдържа например месецитеЗа да ги попълните автоматично в колона, трябва да изберете 12 клетки в една колона, да въведете константата на масива и да я потвърдите с Ctrl + Shift + Enter.
Можете също да използвате формули, които изчисляват цели диапазони.Например, ако искате да изчислите 5% данък върху сумата на всеки продукт (използвайки продажбите и цените от предишния пример), можете да напишете:
=B2:B11*C2:C11*0,05
Първо, изберете диапазон от 10 вертикални клетки. Въведете формулата, където искате да се появят тези данъчни суми, след което потвърдете формулата като формула за масив. Всеки ред ще показва данъка, съответстващ на всеки продукт.
Пример за многомерна матрица: ТРАНСПОНИРАНЕ на таблица
Excel включва функции, предназначени да връщат пълни масиви, като например ТРАНСПОРИРАНЕ, ТРЕНД, ЧЕСТОТА или Функция MAPТРАНСПОНИРАНЕТО е едно от най-популярните, защото ви позволява да разменяте редове и колони и обратно.
Да предположим, че имате таблица в A1:D11 И искате да го обърнете, така че това, което бяха редове, да стане колони. Процедурата е:
- Изберете празен диапазон с обърнат размерАко оригиналната таблица има 4 колони и 11 реда, изберете диапазон от 11 колони и 4 реда.
- Въведете формулата =TRANSPOSE(A1:D11) в лентата за формули докато този диапазон е избран.
- Потвърдете с Ctrl + Shift + Enter, за да генерирате транспонираната матрица в Excel. във всички клетки от диапазона.
При работа с формули за масиви, които връщат множество клеткиТрябва да вземете предвид няколко правила на играта:
- Първо трябва да изберете целия диапазон, където ще бъде поставена матрицатаСлед това записвате формулата и я потвърждавате.
- Не можете да редактирате нито една клетка в масиваВсяка промяна засяга цялата формула; ако се опитате да промените изолирана клетка, ще видите съобщение за грешка.
- За да изтриете масив, изберете целия диапазон и изтрийте формулата. от лентата с формули, след което потвърдете с Ctrl + Shift + Enter или просто с Delete, ако ще го премахнете.
- Ако искате матрицата да заема по-малко клеткиПърво ще трябва да изтриете оригиналния масив и да го създадете отново в новия диапазон.
- Ако имате нужда да покриете повече клеткиИзберете стария диапазон плюс новите клетки, редактирайте формулата, за да се побере в новия размер, и я потвърдете отново като формула за масив.
Константни матрици: фиксирани стойности във формули
„Константна матрица“ е просто набор от статични стойности, записани в самата формула.оградени в къдрави скоби и разделени от съответния символ. Те са много полезни за групиране на критерии, тегла или фиксирани списъци, които не искате да има в клетките на листа.
В Excel на испански език, константните масиви следват тези общи правила:
- Едномерен хоризонтален масив: стойности в един ред, разделени с обратни наклонени черти (\) и оградени в къдрави скоби. Например: {1\2\3\4}.
- Едномерен вертикален масивСтойности в една колона, разделени с точка и запетая (;) в къдрави скоби. Например: {"Януари";"Февруари";"Март"}.
- Двумерна матрицаКомбинация от двете; колоните са разделени с обратна наклонена черта, а редовете с точка и запетая. Например: {1\2\3;4\5\6} представлява два реда и три колони.
Елементите на константа тип масив могат да бъдат числа, текст, логически стойности и грешки..
- Численостцели числа, десетични числа или научна нотация.
- Текствинаги в двойни кавички.
- Булеви стойностиВЯРНО и НЕВЯРНО (без кавички).
- грешки: #Н/Д, #DIV/0! и др.
Константа тип масив не може да съдържа препратки към клетки, други диапазони, функции или дефинирани имена.Само „чисти“ ценности.
Присвояване на имена на константни масиви
За удобно повторно използване на матрична константаМожете да му зададете име, за да не се налага да го въвеждате всеки път:
- Отидете на раздела Формули > Дефинирани имена > Присвояване на име.
- Напишете описателно име (например, Месеци).
- В полето „Отнася се до“ въведете матричната константа.Например: ={"януари"\"февруари"\"март"\"април"\"май"\"юни"\"юли"\"август"\"септември"\"октомври"\"ноември"\"декември"}.
- Кликнете върху „ОК“ за да запази името.
За да използвате тази матрица в листаИзберете толкова клетки, колкото е броят на елементите, въведете =Месеци и потвърдете с Ctrl + Shift + Enter. Excel ще разпредели всеки месец в съответната му клетка.
Ако матрична константа „не работи“ и върне грешкаИма няколко точки, които трябва да прегледате:
- Проверете дали сте използвали правилните разделители: обратна наклонена черта за хоризонтално разделяне на колоните и точка и запетая за вертикално разделяне на редовете.
- Проверете дали мястото, където искате да го изхвърлите, е с подходящ размер и ориентация..
- Уверете се, че текстът е ограден в кавички и че не сте объркали препратките към клетки.които не са позволени в константи.
Пример: сумиране на N най-високи стойности в даден диапазон
Много интересно приложение на матрични константи е комбинирането им с функции като LARGE или SMALLПредставете си, че искате да сумирате трите най-високи стойности в диапазон от данни, например A1:A100.
Логичното би било да се обадите на K.ESIMO.MAYOR три пъти. (за 1, 2 и 3) и сумирайте резултата, но с матрична константа правите всичко наведнъж:
{=SUMA(K.ESIMO.MAYOR(A1:A100;{1\2\3}))}
Константата {1\2\3} казва на K.ESIMO.MAYOR да върне три стойностиНай-големият, вторият по големина и третият по големина. SUM сумира тези три резултата. Промяната на SMALLEST ще ви даде сбора от трите най-малки.
Пример: Преброяване на клетки, които отговарят на много критерии
Когато трябва да вземете предвид множество условия, COUNTIFS е добър вариант.Когато обаче има много критерии, формулата става дълга и трудна за четене, защото повтаряте диапазона от критерии отново и отново.
Представете си списък с продукти, където Педро ги е оценил от 1 до 5. Името на оценителя е в колона C (C2:C28) и в колона A (A2:A28). Искаме да знаем колко пъти Педро е дал 3, 4 или 5 точки.
Вместо три отделни функции COUNTIFS, можете да използвате константа тип масив:
=SUMA(CONTAR.SI.CONJUNTO(A2:A28;»Pedro»;C2:C28;{3\4\5}))
Константата {3\4\5} кара COUNTIFS да генерира вътрешно три резултата (за всеки резултат) и SUM ги групира. Това ви дава компактна, лесна за четене и по-ефективна формула при работа с много критерии.
Както можете да видите, формулите за масиви превръщат Excel в един вид мини машина за изчисление на вектори.Той е способен да обработва големи диапазони, множество условия и верижни операции, без да претрупва електронната таблица с поддържащи колони. Овладяването на клавишната комбинация Ctrl+Shift+Enter, разбирането на поведението на логическите масиви (TRUE/FALSE) и практикуването с примери като сумиране с условия, обработка на грешки, осредняване без нули или работа с константи на масиви ви дава ниво на контрол, далеч надхвърлящо средностатистическия потребител, и ви позволява да създавате по-чисти, по-бързи и по-лесни за поддръжка електронни таблици.
