В предишната статия видяхме как с помощта на MUNIT можем да съберем числата, разположени по диагонала на една матрица (таблица с хикс редове и хикс колони). Само че формулата работи за диагонала, който върви отгоре и надясно.
Замислих се ще работи ли ако трябва да съберем числата от другия диагонал (отдолу и надясно)
Знаех, че номерът със SUMPRODUCT ще проработи, но трябваше да измисля начин да „обърна“ единичната матрица. Разбира се, в Ексел няма начин да няма начин. По-долу ще видите две решения – първото с „обикновен“ Excel, т.е. десктоп версии до 2019 вкл. Второто е с Excel 365
Решение с „обикновен“ Excel
Показано е на долната картинка. Използвам 2 помощни таблици и функцията INDEX, а ключовата формула е в клетка L4.
Как работи:
- В областта G4:J7 е създадена единична матрица с помощта на MUNIT
- В клетка L4 е въведена функцията INDEX. Тя връща стойността на клетка, която се намира на ред Х и колона У в дадена таблица. INDEX има следния синтаксис:
INDEX(array; row number; column number)
array e областта (таблицата), в която е търсената стойност. В нашия случай G4:J7. Тук обърнете внимание, че е използвано абсолютно адресиране (с доларите), защото искам тази област да остане непроменена, когато копирам формулите.
Row number е номерът на реда, в който се намира желаната от нас стойност. В случая аз го задавам с функцията ROWS, която връща число, равно на броя на редовете в някаква област. ROWS(G4:G$7) ще върне 4, защото в тази област има 4 реда. Като тук важното е, че е използвано абсолютно адресиране за номера на реда (G$7). Тоест незаисимо как копирам формулата – последният ред трябва винаги да бъде ред 7. Така, когато копирам една клетка надолу вече ще имам ROWS(G5:G$7), което ще върне 3 – толкова са редовете в тази област. Съответно INDEX ще търси числото на третия ред.
Column number е номерът на колоната, в която се намира желаната от нас стойност. В случая аз го задавам с функцията COLUMNS, която връща число, равно на броя на колоните в някаква област. COLUMNS($G$4:G4) ще върне 1, защото в тази област има само една клетка. Но понеже използвам абсолютно адресиране за началото ($G$4), когато копирам наляво ще имаме COLUMNS($G$4:H4), което ще върне 2, защото в тази област има 2 колони.
Накратко казано като си играя с абсолютно и относително адресиране, аз казвам на Ексел на първия ред да ми върне това, което стои на четвъртия в областта G4:J7, на втория ред – това, което е на третия и т.н. За колоните обаче искам да запази редът на номериране и в първа колона да върне това, което е от първата колона
- След като съм обърнал единичната матрица, вече не е никакъв проблем да приложа функцията SUMPRODUCT, както е показано в клетка L11
Решение с Excel 365 (и десктоп версия от 2021 нагоре)
В „модерния“ Excel е възможно да решим задачата с една единствена формула като използваме динамичните функции SORTBY и SEQUENCE.
Как работи?
Тук отново използваме SUMPRODUCT, за да умножим оригиналната матрица (областта В4:Е7) с матрица, която има стойности 1 по диагонала, а всички други са нула. Тази матрица я създаваме с MUNIT, но я „обръщаме“ като я сортираме с функцията SORTBY. Тази функция сортира стойностите в дадена област в зависимост от стойностите на друга област. Областта, която искаме да сортираме е тази, която създава MUNIT(4).
За да зададем областта, по която да се сортира, използваме функцията SEQUENCE. Тя създава поредица от стойности по зададена начална стойност, брой стойности и стъпка на нарастване. В нашия случай SEQUENCE(4;1;1;1) връща редицата от числа 1, 2, 3, 4.
Накрая използваме -1 за трети аргумент в SORTBY, защото искаме да сортираме в низходящ ред, т.е. четвъртият ред да отиде на 1 място, третият на второ и т.н.
В общи линии това е. Само с един ред можем да направим това, което в „обикновения“ Excel постигаме чрез две помощни таблици плюс формула. Ето това е голямото предимство на „модерния“ Excel, силата на динамичните формули.