Как да използвате референтни функции в Excel 2016

Референтните функции на Excel 2016 в падащия списък на командния бутон Търсене и справка в раздела Формули на лентата са предназначени да се справят конкретно с различни аспекти на препратките към клетки в работния лист. Тази група функции включва:

• АДРЕС за връщане на препратка към клетка като текстов запис в клетка на работния лист

• AREAS за връщане на броя на областите в списък със стойности ( области се дефинират като диапазон от съседни клетки или една клетка в препратката към клетка)

• COLUMN за връщане на числото, представляващо позицията на колоната на препратка към клетка

• COLUMNS за връщане на броя на колоните в препратка

• FORMULATEXT за връщане на формулата, посочена като текстов низ

• GETPIVOTDATA за връщане на данни, съхранявани в обобщена таблица на Excel

• HYPERLINK, за да създадете връзка, която отваря друг документ, съхранен на вашия компютър, мрежа или интернет (можете да направите това и с командата Insert→Hyperlink)

• ИНДИРЕКТНО за връщане на препратка към клетка, определена от текстов низ и довеждане на съдържанието в клетката, към която се отнася към тази клетка

• LOOKUP за връщане на стойност от масив

• OFFSET за връщане на препратка към диапазон от клетки, който е определен от броя на редовете и колоните от клетка или диапазон от клетки

• ROW за връщане на номера на реда на препратка към клетка

• ROWS за връщане на броя на редовете в диапазон от клетки или масив

• RTD за връщане на данни в реално време от сървър, изпълняващ програма, която поддържа автоматизация на COM (Component Object Model)

• TRANSPOSE за връщане на вертикален масив като хоризонтален масив и обратно

Вземете кльощава на колони и редове

Функциите COLUMNS и ROWS връщат броя на колоните и редовете в определен диапазон от клетки или масив. Например, ако имате диапазон от клетки в електронната таблица с име product_mix, можете да разберете колко колони съдържа, като въведете тази формула:

=КОЛОНИ(микс_продукт)

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

=РЕДОВЕ(микс_продукт)

Можете да използвате функциите COLUMNS и ROWS заедно, за да изчислите общия брой клетки в определен диапазон. Например, ако искате да знаете точния брой клетки, използвани в диапазона от клетки product_mix, създавате следната проста формула за умножение, като използвате функциите COLUMNS и ROWS:

=КОЛОНИ(микс_продукт)*РЕДОВЕ(микс_продукт)

Не бъркайте функцията COLUMNS (множествено число) с функцията COLUMN (единствено число) и функцията ROWS (множествено число) с функцията ROW (единствено число). Функцията COLUMN връща номера на колоната (все едно Excel използва референтната система R1C1) за препратката към клетката, която посочвате като негов единствен аргумент. По същия начин функцията ROW връща номера на реда за препратката към клетката, която сте посочили като неин аргумент.

Транспониране на диапазони от клетки

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

Например, ако използвате функцията TRANSPOSE за транспониране на диапазон от клетки 2 x 5 (тоест диапазон, който заема два съседни реда и пет съседни колони), трябва да изберете празен диапазон от клетки 5 x 2 (т.е. , диапазон, който заема пет съседни реда и две съседни колони) в работния лист, преди да използвате бутона Insert Function, за да вмъкнете функцията TRANSPOSE в първата клетка. След това, след като изберете диапазона от клетки 2 x 5, който съдържа данните, които искате да транспонирате в текстовото поле Array на диалоговия прозорец Аргументи на функцията, трябва да натиснете Ctrl+Shift+Enter, за да затворите този диалогов прозорец и да въведете функцията TRANSPOSE в целия избран диапазон от клетки като формула за масив (заградена в къдрави скоби).

Да предположим, че искате да транспонирате данните, въведени в диапазона от клетки A10:C11 (масив 2 x 3) в празния диапазон от клетки E10:F12 (масив 3 x 2) на работния лист. Когато натиснете Ctrl+Shift+Enter, за да завършите формулата на масива, след като изберете диапазона от клетки A10:C11 като аргумент на масива , Excel поставя следната формула на масива във всяка клетка от диапазона:

{=ТРАНСПОЗИРАНЕ(A10:C11)}

Следващата фигура илюстрира използването на функцията TRANSPOSE. Диапазонът от клетки B2:C4 съдържа масив 3 x 2. За да преобразувате този масив 3 x 2 в диапазона от клетки B2:C4 в масив 2 x 3 в диапазона B6:D7, следвайте тези стъпки:

Изберете празния диапазон от клетки B6:D7 в работния лист.

Щракнете върху командния бутон Търсене и справка в раздела Формули на лентата и след това изберете опцията TRANSPOSE от падащото меню на бутона.

Excel вмъква =TRANSPOSE() в лентата с формули и отваря диалоговия прозорец Аргументи на функцията, където е избрано текстовото поле за аргумент Array.

Плъзнете през диапазона от клетки B2:C4 в работния лист, така че текстовото поле за аргумент Array съдържа B2:C4 и формулата в лентата с формули вече гласи =TRANSPOSE(B2:C4).

Натиснете Ctrl+Shift+Enter, за да затворите диалоговия прозорец Вмъкване на аргументи (не щракайте върху OK) и да вмъкнете формулата на TRANSPOSE масива в диапазона от клетки B6:D7, както е показано тук.

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

Щракването върху бутона OK в диалоговия прозорец Аргументи на функцията вмъква функцията TRANSPOSE в активната клетка на текущата селекция на клетка. Правейки това връща #VALUE! стойност на грешката към клетката. Трябва да запомните да натиснете Ctrl+Shift+Enter, за да затворите диалоговия прозорец и да поставите формулата в целия диапазон от клетки.

Ако всичко, което искате да направите, е да транспонирате заглавия на редове и колони или обикновена таблица с данни, не е нужно да преминавате през сложното създаване на формула на масив с помощта на функцията TRANSPOSE. Просто копирайте диапазона от клетки, които ще бъдат транспонирани, с командния бутон Копиране в раздела Начало на лентата. Позиционирайте курсора на клетката в първата празна клетка, където транспонираният диапазон трябва да бъде поставен, преди да щракнете върху опцията Транспониране в падащото меню на командния бутон Поставяне.