MUNIT е сред многото функции на Excel, които имат специфично приложение и масовият потребител дори може и да не подозира за съществуването им. Наскоро обаче в блога получих интересен въпрос, решението на който се оказа изключително лесно именно с помощта на тази функция. И сега ще разкажа за този казус, може би ще бъде от полза и на други хора.
Въпросът
„Здравейте, имам следния казус в Excel, за който се надявам, че можете да ми помогнете. Всяка седмица получавам данни от различни измервания. Те представляват десетки матрици (квадратни таблици) с различен брой редове. За целите на моята работа аз трябва да събера числата по диагонала на тези матрици. Формулата за това не е сложна, но те всеки път са различен брой и с различен брой редове и колони. На кратко – губя всяка седмица 2-3 часа в кликане и създаване на формули. По-неприятното е, че на няколко пъти направих грешка, което може да доведе до сериозни загуби за фирмата ни. Можете ли да ми предложите някаква формула, с която да автоматизирам тези изчисления?“
Да онагледим казуса с една картинка:
На нея виждате проста таблица с четири реда и четири колони и целта е да се съберат тези по диагонала (оцветените в жълто)
Формулата за това е изключително елементарна – пишем =SUM( натискаме клавиша CTRL и след това кликаме последователно в тези четири клетки с левия бутон на мишката.
Когато натиснем Enter – получаваме резултата, който търсим.
За една таблица – ОК. Но когато са 40-50 и повече и ако имаме такива с 20+ редове и колони, можете да си представите за какво кликане става на въпрос.
За късмет, във въпроса имаше една много добра подсказка – думата „матрица“. Някак си тя ме подсети, че сред математическите функции на Excel има и такива за изчисления с матрици и реших да ги погледна. Ето така открих
функцията MUNIT
Тя има изключително прост синтаксис – иска само едно число, което задава броя на редовете и колоните. След това създава единична матрица, т.е. таблица с брой на редовете и колоните равен на зададеното число. В тази таблица всички числа са нули, а само тези по диагонала от ляво на дясно (главния диагонал) са единици.
Ако сте потребител на десктоп версия, а не на Офис 365, тогава предварително трябва да селектирате област с необходимия брой редове и колони и след като напишете функцията да ударите SHIFT + CTRL + ENTER. Ако работите с Офис 365, тогава е още по-лесно – удряте само един ENTER и Ексел сам разпростира таблицата колкото е необходимо (нарича се SPILL – това е една от революционните новости в Ексел 365 в последните години. Областта с формулите сама се разширява и смалява при промяна на изходните параметри)
Как да използваме MUNIT за решаване на този казус
За да илюстрирам концепцията съм наредил една до друга три таблици – оригиналната с числата, единична матрица, създадена с MUNIT(4) и трета таблица, в която ще изчисля произведенията на всеки две числа от първите две таблици. Показана е формулата
Ето какво се получава, когато я копирам в цялата таблица:
Под третата таблица съм направил проста сума на всички числа в нея. Но тъй като тези извън главния диагонал са нули на практика аз съм получил сумата на числата в диагонала. Точно това, което ни трябваше. Толкова е просто!
Веднага възниква въпросът – а можем ли да го направим с една формула без допълнителни таблици? Може, но за целта трябва да използваме функцията SUMPRODUCT. Тя връща сбора от произведенията на числата в два или повече масива с данни.
MUNIT + SUMPRODUCT за оптимален резултат
Ето тук е показано финалното решение. Вижте колко проста и елегантна формула
Тук обръщам внимание, че без значение от версията на Ексел, тази формула изисква само Enter след като я напишем.
Аз все още продължавам да не разбирам защо са необходими тези сметки и какво прави „клиентът“ с тях. Но в този изключително специфичен случай намерихме много елегантно решение с помощта на MUNIT. Функция, която кротко си съществува в каталога на Excel и може би никога нямаше да опра до нея, ако не беше този казус.
Ако искате да разберете как може да съберете числата по другия диагонал – вижте тук