Како да поставите опсег на вредности во Excel

При користење на Microsoft Excel, многу често корисникот однапред не знае колку информации ќе има во табелата како резултат. Затоа, не разбираме во сите ситуации кој опсег треба да биде покриен. На крајот на краиштата, збир на ќелии е променлив концепт. За да се ослободите од овој проблем, неопходно е да се направи автоматско генерирање на опсегот, така што ќе се заснова исклучиво на количината на податоци што биле внесени од корисникот.

Автоматско менување на опсегот на ќелиите во Excel

Предноста на автоматските опсези во Excel е тоа што го олеснуваат користењето на формулите. Покрај тоа, тие овозможуваат значително да се поедностави анализата на сложени податоци кои содржат голем број формули, кои вклучуваат многу функции. Можете да му дадете име на овој опсег, а потоа тој автоматски ќе се ажурира во зависност од тоа кои податоци ги содржи.

Како да направите автоматска промена на опсегот во Excel

Да претпоставиме дека сте инвеститор кој треба да инвестира во некој објект. Како резултат на тоа, сакаме да добиеме информации за тоа колку може да заработите вкупно за целото време кога парите ќе работат за овој проект. Сепак, за да ги добиеме овие информации, треба редовно да следиме колкав вкупен профит ни носи овој објект. Направете го истиот извештај како на оваа слика од екранот.

На прв поглед, решението е очигледно: само треба да ја сумирате целата колона. Ако се појават записи во него, износот ќе се ажурира независно. Но, овој метод има многу недостатоци:

1. Ако проблемот се реши на овој начин, нема да може да се користат ќелиите вклучени во колоната Б за други цели.
2. Таквата табела ќе потроши многу RAM меморија, што ќе го оневозможи користењето на документот на слаби компјутери.

Оттука, неопходно е да се реши овој проблем преку динамични имиња. За да ги креирате, мора да ја извршите следнава низа на дејства:

1. Одете во табулаторот „Формули“, кој се наоѓа во главното мени. Ќе има дел „Дефинирани имиња“, каде што има копче „Додели име“, на кое треба да кликнеме.
2. Следно, ќе се појави дијалог-кутија во која треба да ги пополните полињата како што е прикажано на екранот. Важно е да се напомене дека треба да ја примениме функцијата = ПОМЕСТУВАЊЕ заедно со функцијата ПРОВЕРКАза да креирате опсег за автоматско ажурирање. 
3. После тоа треба да ја користиме функцијата SUM, кој го користи нашиот динамички опсег како аргумент. 

По завршувањето на овие чекори, можеме да видиме како се ажурира покриеноста на ќелиите што припаѓаат на опсегот „приход“ додека додаваме нови елементи таму.

Функција OFFSET во Excel

Ајде да ги погледнеме функциите што претходно ги снимивме во полето „опсег“. Користење на функцијата РАБОТУВАЕ можеме да ја одредиме количината на опсег со оглед на тоа колку ќелии во колоната Б се пополнети. Аргументите на функцијата се како што следува:

1. Почетна ќелија. Со овој аргумент, корисникот може да покаже која ќелија во опсегот ќе се смета горе лево. Ќе известува надолу и надесно.
2. Опсегот поместен со редови. Користејќи го овој опсег, го поставивме бројот на ќелии со кои треба да се појави поместување од горната лева ќелија на опсегот. Можете да користите не само позитивни вредности, туку и нула и минус. Во овој случај, поместувањето може да не се случи воопшто, или ќе се изврши во спротивна насока.
3. Опсегот поместен со колони. Овој параметар е сличен на претходниот, само што ви овозможува да го поставите степенот на хоризонтално поместување на опсегот. Овде можете да користите и нула и негативни вредности.
4. Износот на опсегот во висина. Всушност, насловот на овој аргумент ни дава до знаење што значи. Ова е бројот на ќелии со кои опсегот треба да се зголеми.
5. Вредноста на опсегот во ширина. Аргументот е сличен на претходниот, само што се однесува на колоните.

Не треба да ги специфицирате последните два аргументи ако не треба. Во овој случај, вредноста на опсегот ќе биде само една ќелија. На пример, ако ја наведете формулата =OFFSET(A1;0;0), оваа формула ќе се однесува на истата ќелија како онаа во првиот аргумент. Ако вертикалното поместување е поставено на 2 единици, тогаш во овој случај ќелијата ќе се однесува на ќелијата А3. Сега да опишеме детално што значи функцијата ПРОВЕРКА.

COUNT функција во Excel

Користење на функцијата ПРОВЕРКА одредуваме колку ќелии во колоната Б сме пополниле вкупно. Тоа е, користејќи две функции, одредуваме колку ќелии во опсегот се пополнети и врз основа на добиените информации, ја одредуваме големината на опсегот. Затоа, конечната формула ќе биде следнава: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Ајде да погледнеме како правилно да го разбереме принципот на оваа формула. Првиот аргумент укажува на тоа каде започнува нашиот динамички опсег. Во нашиот случај, ова е ќелија Б2. Понатамошните параметри имаат нула координати. Ова сугерира дека не ни требаат поместувања во однос на горната лева ќелија. Сè што пополнуваме е вертикалната големина на опсегот, како што ја користевме функцијата ПРОВЕРКА, кој го одредува бројот на ќелии кои содржат некои податоци. Четвртиот параметар што го пополнивме е единицата. Така покажуваме дека вкупната ширина на опсегот треба да биде една колона.

Така, користејќи ја функцијата ПРОВЕРКА корисникот може да ја користи меморијата што е можно поефикасно со вчитување само на оние ќелии кои содржат некои вредности. Соодветно на тоа, нема да има дополнителни грешки во работата поврзани со слабите перформанси на компјутерот на кој ќе работи табелата.

Според тоа, за да ја одредите големината на опсегот во зависност од бројот на колони, треба да извршите слична низа на дејства, само во овој случај треба да ја наведете единицата во третиот параметар, а формулата во четвртиот ПРОВЕРКА.

Гледаме дека со помош на формулите на Excel, не само што можете да ги автоматизирате математичките пресметки. Ова е само капка во океанот, но всушност тие ви дозволуваат да ја автоматизирате речиси секоја операција што ќе ви падне на ум.

Динамички графикони во Excel

Така, во последниот чекор, можевме да создадеме динамичен опсег, чија големина целосно зависи од тоа колку пополнети ќелии содржи. Сега, врз основа на овие податоци, можете да креирате динамични графикони кои автоматски ќе се менуваат штом корисникот направи какви било промени или додаде дополнителна колона или ред. Редоследот на дејства во овој случај е како што следува:

1. Го избираме нашиот опсег, по што вметнуваме табела од типот „Хистограм со групирање“. Оваа ставка можете да ја најдете во делот „Вметни“ во делот „Табели – хистограм“.
2. Ние правиме лев клик на глувчето на случајна колона од хистограмот, по што функцијата =SERIES() ќе биде прикажана во функцијата линија. На сликата од екранот можете да ја видите деталната формула. 
3. После тоа, треба да се направат некои промени во формулата. Треба да го замените опсегот по „Лист 1!“ до името на опсегот. Ова ќе резултира со следнава функција: =ROW(Лист 1!$B$1;;Лист1!приход;1)
4. Сега останува да се додаде нов запис во извештајот за да се провери дали графиконот се ажурира автоматски или не.

Ајде да го погледнеме нашиот дијаграм.

Ајде да резимираме како го направивме тоа. Во претходниот чекор, создадовме динамичен опсег, чија големина зависи од тоа колку елементи содржи. За да го направите ова, користевме комбинација на функции ПРОВЕРКА и РАБОТУВАЕ. Го направивме овој опсег именуван, а потоа ја користевме референцата за ова име како опсег на нашиот хистограм. Кој специфичен опсег да се избере како извор на податоци во првата фаза не е толку важен. Главната работа е подоцна да го замените со името на опсегот. На овој начин можете да заштедите многу RAM меморија.

Именувани опсези и нивна употреба

Ајде сега да разговараме подетално за тоа како правилно да креирате именувани опсези и да ги користите за извршување на задачите што се поставени за корисникот на Excel.

Стандардно, користиме редовни адреси на ќелиите за да заштедиме време. Ова е корисно кога треба да напишете опсег еднаш или повеќе пати. Ако треба да се користи постојано или треба да биде приспособлив, тогаш треба да се користат именувани опсези. Тие многу го олеснуваат создавањето формули и нема да му биде толку тешко на корисникот да анализира сложени формули кои вклучуваат голем број функции. Ајде да опишеме некои од чекорите вклучени во создавањето динамички опсези.

Сè започнува со именување на ќелијата. За да го направите ова, едноставно изберете го, а потоа напишете го името што ни треба во полето на неговото име. Важно е да биде лесно за паметење. Постојат некои ограничувања што треба да се земат предвид при именувањето:

1. Максималната должина е 255 знаци. Ова е сосема доволно за да доделите име што го посакува вашето срце.
2. Името не смее да содржи празни места. Затоа, ако содржи неколку зборови, тогаш е можно да се разделат со помош на знакот за подвлекување.

Ако подоцна на другите листови од оваа датотека треба да ја прикажеме оваа вредност или да ја примениме за да извршиме понатамошни пресметки, тогаш нема потреба да се префрламе на првиот лист. Можете само да го запишете името на оваа ќелија на опсег.

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

Дополнително, можно е да се создаде именуван опсег со помош на јазичето Формули со помош на алатката Set Name. Откако ќе го избереме, ќе се појави прозорец каде што треба да избереме име за нашиот опсег, како и да ја одредиме областа до која ќе се прошири рачно. Можете исто така да одредите каде ќе работи овој опсег: во еден лист или низ целата книга.

Ако опсегот на имиња е веќе креиран, тогаш за да го користите, постои специјална услуга наречена менаџер за имиња. Овозможува не само уредување или додавање нови имиња, туку и нивно бришење доколку повеќе не се потребни.

Треба да се има на ум дека кога се користат именувани опсези во формули, а потоа по неговото бришење, формулите нема автоматски да се препишуваат со точните вредности. Затоа, може да се појават грешки. Затоа, пред да избришете именуван опсег, треба да бидете сигурни дека тој не се користи во ниту една од формулите.

Друг начин да се создаде именуван опсег е да се добие од табела. За да го направите ова, постои специјална алатка наречена „Креирај од избор“. Како што разбравме, за да го користите, прво мора да го изберете опсегот што ќе го уредиме, а потоа да го поставите местото каде што ги имаме насловите. Како резултат на тоа, врз основа на овие податоци, Excel автоматски ќе ги обработи сите податоци, а насловите автоматски ќе бидат доделени.

Ако насловот содржи неколку зборови, Excel автоматски ќе ги раздвои со долна црта.

Така, сфативме како да креираме динамични именувани опсези и како тие ви дозволуваат да ја автоматизирате работата со големи количини на податоци. Како што можете да видите, доволно е да користите неколку функции и програмските алатки вградени во функционалноста. Воопшто нема ништо комплицирано, иако на почетник на прв поглед може да изгледа така.