Работа с определен диапазон в Microsoft Excel

Един от инструментите, които опростяват работата с формули и ви позволява да оптимизирате работата с масиви от данни, е да зададете имена на тези масиви. По този начин, ако искате да се отнесете към обхват от хомогенни данни, няма да е необходимо да напишете сложна справка, а просто да посочите просто име, което преди това сте определили конкретен масив. Нека да разберем основните нюанси и предимства на работата с назованите диапазони.

Манипулация с наименовани области

Определен обхват е областта на клетките, на които потребителят е назначен конкретно име. В този случай това име се счита за Excel, като адреса на посочената област. Той може да се използва като част от аргументите за формули и функции, както и в специализирани инструменти на Excel, например "Validating Input Values" .

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

• Не трябва да има никакви интервали в него;
• Тя задължително трябва да започне с писмо;
• Нейната дължина не може да бъде повече от 255 знака;
• То не трябва да се представя от координатите на формулярите А1 или R1C1 ;
• Книгата не трябва да има същото име.

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

В случай, че името не е зададено в обхвата, то в горното поле, когато е избрано, се показва адреса на горната горна клетка на масива.

Създаване на именуван диапазон

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

1. Най-бързият и лесен начин да зададете име на масив е да го напишете в полето за име, след като изберете съответната област. Така че, изберете масива и въведете името в полето, което според нас е необходимо. Желателно е лесно да се запомнят и да се отговори на съдържанието на клетките. И, разбира се, е необходимо тя да отговаря на задължителните изисквания, изложени по-горе.
2. За да може програмата да въведе това име в своя регистър и да го запомни, кликнете върху клавиша Enter . Името ще бъде присвоено на избраната клетъчна област.

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

1. Изберете масива, над който искате да извършите операцията. Кликнете с десния бутон върху селекцията. В списъка, който се отваря, спираме секцията с опцията "Assign name ..." .
2. Отваря прозореца за създаване на име. В полето "Име" името трябва да бъде въведено в съответствие с условията, посочени по-горе. В областта "Обхват" се показва адреса на избрания масив. Ако направихте подбор правилно, не е необходимо да правите промени в тази област. Кликнете върху бутона "OK" .
3. Както можете да видите в полето за име, името на региона е назначено успешно.

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

1. Изберете клетъчната област, която искате да конвертирате в избрана клетка. Преминаваме към раздела "Формули" . В групата "Специфични имена" кликнете върху иконата "Присвояване на име" .
2. Отваря се точно същия прозорец за задаване на име, точно както при предишната версия. Всички следващи операции се извършват по същия начин.

Последната опция за задаване на име на областта на клетките, която ще разгледаме, е използването на Name Manager .

1. Изберете масива. В раздела "Формули" кликваме върху голямата икона "Мениджър на имена" , намираща се в същата група "Специфични имена" . Друга възможност е вместо това да използвате клавишната комбинация Ctrl + F3 .
2. Прозорецът Мениджър на имената е активиран. В него кликнете върху бутона "Създаване ..." в горния ляв ъгъл.
3. След това се стартира прозорецът за създаване на вече познат файл, където трябва да изпълните манипулациите, описани по-горе. Името, което ще бъде присвоено на масива, ще се покаже в мениджъра . Тя може да бъде затворена, като кликнете върху стандартния бутон за затваряне в горния десен ъгъл.

Урокът: Как да зададете име на клетка в Excel

Операции с избрани диапазони

Както споменахме по-горе, наречените масиви могат да се използват при изпълнение на различни операции в Excel: формули, функции, специални инструменти. Нека да разгледаме конкретен пример за това как се случва това.

На един лист имаме списък с модели компютърно оборудване. Имаме задача по втория лист в таблицата да направим падащ списък от този списък.

1. На първо място, на лист със списъка, ние присвояваме диапазона на името с някой от методите, споменати по-горе. В резултат на това, когато изберете списъка в полето за име, трябва да покажем името на този масив. Нека това е името на "Модел" .
2. След това преминете към листа, където се намира таблицата, в която трябва да създадете падащ списък. Изберете областта в таблицата, в която планираме да приложим падащия списък. Преминаваме в раздела "Данни" и кликваме върху бутона "Проверка на данни" в полето "Работа с данни" на лентата.
3. В прозореца, който започва проверката на данните, отворете раздела "Параметри" . В полето "Тип данни" изберете "Списък" . В полето "Източник" в обичайния случай трябва ръчно да въведете всички елементи от падащия списък в бъдеще или да дадете връзка към техния списък, ако се намира в документа. Това не е много удобно, особено ако списъкът е на друг лист. Но в нашия случай всичко е много по-лесно, тъй като ние определихме името на съответния масив. Така че просто поставете знака "равен" и напишете това име в полето. Получен е следният израз:

   =Модели

   Кликнете върху "OK" .

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

Определен обхват е удобен за използване като аргументи за различни функции. Нека да разгледаме как това работи на практика с конкретен пример.

Така че имаме таблица, в която приходите на пет клона на предприятието се регистрират ежемесечно. Трябва да знаем общите приходи за филиал 1, клон 3 и клон 5 за целия период, посочен в таблицата.

1. На първо място, всеки ред от съответния клон в таблицата ще бъде наименуван. За клон 1 изберете областта с клетките, която съдържа данни за приходите за нея за 3 месеца. След избора в полето за имена напишете името "Branch_1" (не забравяйте, че името не може да съдържа интервал) и кликнете върху клавиша Enter . Ще бъде назначено името на съответната област. Ако искате, можете да използвате друг начин за задаване на името, споменато по-горе.
2. По същия начин, като избираме съответните области, даваме имена на линиите на други отрасли: "Branch_2" , "Branch_3" , "Branch_4" , "Branch_5" .
3. Изберете елемента на листа, към който ще бъде изведено резултатът за сумиране. Кликваме върху иконата "Вмъкване на функция" .
4. Стартира функцията Start Wizard . Преминаваме до блока "Математически" . Спираме селекцията от списъка с налични оператори на името "SUMM" .
5. Активирането на прозореца SUMM оператор е активирано. Тази функция, която е част от група математически оператори, е специално предназначена да обобщава цифровите стойности. Синтаксисът се представя със следната формула:

   =СУММ(число1;число2;…)

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

   Като цяло операторът SUMM може да има от един до 255 аргумента. Но в нашия случай ще са необходими само три аргумента, тъй като ще добавим три диапазона: "Branch_1" , "Branch_3" и "Branch_5" .

   Така че, задайте курсора в полето "Номер1" . Тъй като дадохме имената на диапазоните, които искате да добавите, не е необходимо да въвеждате координати в полето или да изберете съответните области от листа. Достатъчно е само да посочите името на масива, който трябва да бъде добавен : "Branch_1" . В полетата "Брой 2" и "Брой 3" съответно пишем "Branch_3" и "Branch_5" . След като направите горните манипулации, кликнете върху "OK" .

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

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

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

Урокът: Как да изчислите сумата в Microsoft Excel

Управление на наименувани диапазони

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

1. За да отидете до диспечера , отиваме в раздела "Формули" . Там трябва да кликнете върху иконата, която се нарича "Name Manager" . Посочената икона се намира в групата "Специфични имена" .
2. След като се преместите в мениджъра, за да извършите необходимата манипулация с диапазона, трябва да намерите името му в списъка. Ако списъкът с елементи не е много обширен, тогава е съвсем прост. Но ако в текущата книга има няколко десетки имена масиви или повече, тогава за улесняване на задачата има смисъл да се използва филтърът. Кликваме върху бутона "Филтър" , намиращ се в горния десен ъгъл на прозореца. Филтрирането може да се извърши по следните начини, като изберете съответния елемент в отвореното меню:
   • Имената на листа;
   • в книгата;
   • с грешки;
   • без грешки;
   • Някои имена;
   • Имената на таблиците.

   За да се върнете към пълния списък с имена, достатъчно е да изберете опцията "Изчистване на филтъра" .

3. За да промените границите, името или други свойства на посочения диапазон, изберете желания елемент в мениджъра и кликнете върху бутона "Промяна ..." .
4. Прозорецът за промяна на името се отваря. Той съдържа точно същите полета като прозореца за създаване на посочения диапазон, който споменахме по-рано. Само този път полетата ще бъдат попълнени с данни.

   В полето "Име" можете да промените името на полето. В полето "Забележка" можете да добавяте или редактирате съществуваща бележка. В полето "Обхват" можете да промените адреса на имения масив. Възможно е да направите това, като приложите ръчно въвеждане на необходимите координати и като настроите курсора в полето и изберете съответния масив от клетки в листа. Адресът му се показва незабавно на полето. Единственото поле, чиито стойности не могат да бъдат редактирани, е "Площ" .

   След като редактирате данните, кликнете върху бутона "OK" .

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

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

1. За да изпълните процедурата за изтриване, изберете желания елемент от списъка и кликнете върху бутона "Изтриване" .
2. След това се стартира диалогов прозорец, който иска да потвърди решимостта си да изтрие избрания елемент. Това се прави, за да не се допусне погрешка процедурата от страна на потребителя. Така че, ако сте сигурни, че трябва да изтриете, кликнете върху бутона "OK" в полето за потвърждение. В противен случай кликнете върху бутона "Отказ" .
3. Както можете да видите, избраният елемент е премахнат от списъка на контролера . Това означава, че масивът, към който е приложен, е изгубил името си. Сега тя ще бъде идентифицирана само по координати. След като приключите всички манипулации в мениджъра , кликнете върху бутона "Затваряне", за да излезете от прозореца.

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

• ← Създаване на връзки в Microsoft Excel
• Изчисляване на разликата в Microsoft Excel →