содржина
Дали имате табели со податоци во Excel кои може да се менуваат, односно бројот на редови (колони) да се зголемува или намалува во текот на работата? Ако големините на табелата „пловат“, тогаш ќе мора постојано да го следите овој момент и да го коригирате:
- врски во формулите за извештаи кои се однесуваат на нашата табела
- почетни опсези на стожерни табели кои се изградени според нашата табела
- почетни опсези на графикони изградени според нашата табела
- се движи за паѓачки мени кои ја користат нашата табела како извор на податоци
Сето ова вкупно нема да ви дозволи да ви биде здодевно 😉
Ќе биде многу поудобно и правилно да се создаде динамичен „гумен“ опсег, кој автоматски ќе се приспособи во големина на вистинскиот број на редови и колони на податоци. За да се спроведе ова, постојат неколку начини.
Метод 1. Паметна маса
Означете го вашиот опсег на ќелии и изберете од картичката Дома – Формат како табела (Дома – Формат како табела):
Ако не ви треба дизајнот со риги што се додава на табелата како несакан ефект, тогаш можете да го исклучите на јазичето што се појавува Конструктор (дизајн). Секоја табела создадена на овој начин добива име што може да се замени со попогодно на истото место на картичката Конструктор (дизајн) на терен Име на табелата (Име на табела).
Сега можеме да користиме динамички врски до нашата „паметна табела“:
- Табела 1 – линк до целата табела освен за редот за заглавие (A2:D5)
- Табела 1[#Сите] – линк до целата табела (A1:D5)
- Табела 1 [Петар] – упатување на колона-опсег без првото заглавие на ќелијата (C2:C5)
- Табела 1[#Headers] – линк до „заглавието“ со имињата на колоните (A1:D1)
Таквите референци работат одлично во формули, на пример:
= SUM (Табела 1[Москва]) – пресметка на збирот за колоната „Москва“
or
=VPR(F5;Табела 1;3;0) – побарајте во табелата за месецот од ќелијата F5 и издадете ја сумата од Санкт Петербург за него (што е VLOOKUP?)
Ваквите врски може успешно да се користат при креирање стожерни табели со избирање на јазичето Вметни – Стожерна табела (Вметни – Стожерна табела) и внесување на името на паметната табела како извор на податоци:
Ако изберете фрагмент од таква табела (на пример, првите две колони) и креирате дијаграм од кој било тип, тогаш кога додавате нови линии, тие автоматски ќе се додадат на дијаграмот.
Кога креирате паѓачки списоци, не може да се користат директни врски до паметните елементи на табелата, но лесно можете да го надминете ова ограничување користејќи тактички трик - користете ја функцијата ИНДИРЕКТЕН (Индиректно), што го претвора текстот во врска:
Оние. врската до паметна табела во форма на текстуална низа (во наводници!) се претвора во полноправна врска, а паѓачката листа нормално ја перцепира.
Метод 2: Динамички именуван опсег
Ако претворањето на вашите податоци во паметна табела е непожелно поради некоја причина, тогаш можете да користите малку покомплициран, но многу посуптилен и разноврсен метод - креирајте динамичен опсег со име во Excel што се однесува на нашата табела. Потоа, како и во случајот со паметна табела, можете слободно да го користите името на креираниот опсег во какви било формули, извештаи, графикони итн. Да почнеме со едноставен пример:
Задача: направете динамичен опсег со име што ќе се однесува на список на градови и автоматски ќе се протега и намалува по големина кога додавате нови градови или ги бришете.
Ќе ни требаат две вградени Excel функции достапни во која било верзија − ПОИКПОЗ (натпревар) да се одреди последната ќелија од опсегот и Индекс (ИНДЕКС) да креирате динамична врска.
Наоѓање на последната ќелија користејќи MATCH
MATCH(вредност_пребарувач, опсег, тип_совпаѓање) – функција која бара дадена вредност во опсег (ред или колона) и го враќа редниот број на ќелијата каде што е пронајдена. На пример, формулата MATCH(„Март“; A1:A5;0) ќе го врати бројот 4 како резултат, бидејќи зборот „Март“ се наоѓа во четвртата ќелија во колоната A1:A5. Последниот функциски аргумент Match_Type = 0 значи дека бараме точно совпаѓање. Ако овој аргумент не е наведен, тогаш функцијата ќе се префрли на режимот за пребарување за најблиската најмала вредност - токму тоа може успешно да се искористи за да се најде последната зафатена ќелија во нашата низа.
Суштината на трикот е едноставна. MATCH пребарува ќелии во опсегот од врвот до дното и, теоретски, треба да престане кога ќе ја најде најблиската најмала вредност до дадената. Ако наведете вредност што е очигледно поголема од која било достапна во табелата како посакувана вредност, тогаш MATCH ќе стигне до самиот крај на табелата, ќе не најде ништо и ќе го даде секвенцискиот број на последната пополнета ќелија. И ни треба!
Ако има само броеви во нашата низа, тогаш можеме да наведеме број како саканата вредност, која е очигледно поголема од која било од оние во табелата:
За гаранција, можете да го користите бројот 9E + 307 (9 пати 10 на јачината на 307, т.е. 9 со 307 нули) – максималниот број со кој Excel може да работи во принцип.
Ако има текстуални вредности во нашата колона, тогаш како еквивалент на најголемиот можен број, можете да ја вметнете конструкцијата REPEAT(„i“, 255) – текстуална низа која се состои од 255 букви „i“ – последната буква од азбуката. Бидејќи Excel всушност ги споредува шифрите на знаци при пребарувањето, секој текст во нашата табела технички ќе биде „помал“ од толку долга линија „yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy“:
Генерирајте врска користејќи INDEX
Сега кога ја знаеме позицијата на последниот непразен елемент во табелата, останува да формираме врска до целиот наш опсег. За ова ја користиме функцијата:
INDEX(опсег; ред_број; колона_број)
Ја дава содржината на ќелијата од опсегот по ред и број на колона, односно на пример функцијата =INDEX(A1:D5;3;4) во нашата табела со градови и месеци од претходниот метод ќе даде 1240 - содржината од 3-тиот ред и 4-та колона, односно ќелиите D3. Ако има само една колона, тогаш нејзиниот број може да се испушти, т.е. формулата INDEX(A2:A6;3) ќе даде „Самара“ на последната слика од екранот.
И има една не сосема очигледна нијанса: ако ИНДЕКС не само што се внесува во ќелијата по знакот =, како и обично, туку се користи како последен дел од референцата за опсегот по дебелото црево, тогаш тој повеќе не дава содржината на ќелијата, но нејзината адреса! Така, формулата како $A$2:INDEX($A$2:$A$100;3) ќе даде референца за опсегот A2:A4 на излезот.
И тука доаѓа функцијата MATCH, која ја вметнуваме во ИНДЕКС за динамички да го одредиме крајот на листата:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(„I“;255);A2:A100))
Направете именуван опсег
Останува сето тоа да се спакува во една единствена целина. Отворете таб формула (Формули) И кликнете на Менаџер за имиња (Име менаџер). Во прозорецот што се отвора, кликнете на копчето Креирај (ново), внесете го името на нашиот опсег и формулата во полето Опсег (Референца):
Останува да кликнете на OK и готовиот опсег може да се користи во какви било формули, паѓачки листи или графикони.
- Користење на функцијата VLOOKUP за поврзување на табели и вредности за пребарување
- Како да креирате паѓачка листа со автоматско пополнување
- Како да креирате стожерна табела за да анализирате голема количина на податоци