змест
У вас ёсць табліцы з дадзенымі ў Excel, памер якіх можна змяняць, г.зн. колькасць радкоў (слупкоў) можа павялічвацца або памяншацца падчас працы? Калі памеры табліцы «плаваюць», то вам прыйдзецца пастаянна сачыць за гэтым момантам і выпраўляць яго:
- спасылкі ў формулах справаздач, якія спасылаюцца на нашу табліцу
- пачатковыя дыяпазоны зводных табліц, якія будуюцца па нашай табліцы
- пачатковыя дыяпазоны дыяграм, пабудаваных па нашай табліцы
- дыяпазоны для выпадаючых спісаў, якія выкарыстоўваюць нашу табліцу ў якасці крыніцы даных
Усё гэта ў сукупнасці не дасць вам засумаваць 😉
Нашмат зручней і правільней будзе стварыць дынамічны «гумовы» дыяпазон, які будзе аўтаматычна падладжвацца па памерах пад рэальную колькасць радкоў і слупкоў дадзеных. Каб рэалізаваць гэта, ёсць некалькі спосабаў.
Спосаб 1. Разумны стол
Вылучыце дыяпазон вочак і абярыце з укладкі Галоўная – фармат у выглядзе табліцы (Галоўная – фармат у выглядзе табліцы):
Калі вам не патрэбны паласаты дызайн, які дадаецца ў табліцу ў якасці пабочнага эфекту, вы можаце адключыць яго на ўкладцы, якая з'явіцца Канструктар (дызайн). Кожная створаная такім чынам табліца атрымлівае імя, якое можна замяніць на больш зручнае ў тым жа месцы ўкладкі Канструктар (дызайн) у полі Назва табліцы (Імя табліцы).
Цяпер мы можам выкарыстоўваць дынамічныя спасылкі на нашу «разумную табліцу»:
- Табліца 1 – спасылка на ўсю табліцу, акрамя радка загалоўка (A2:D5)
- Табліца1[#Усе] – спасылка на ўсю табліцу (A1:D5)
- Табліца1[Пётр] – спасылка на слупок дыяпазону без першага загалоўка ячэйкі (C2:C5)
- Табліца 1[#Загалоўкі] – спасылка на “загаловак” з назвамі слупкоў (A1:D1)
Такія спасылкі выдатна працуюць у формулах, напрыклад:
= Сум (Табліца1[Масква]) – разлік сумы для графы “Масква”
or
=ВПР(F5;Табліца 1;3;0) – шукаць у табліцы месяц з ячэйкі F5 і выдаваць для яго пецярбургскую суму (што такое VLOOKUP?)
Такія спасылкі можна з поспехам выкарыстоўваць пры стварэнні зводных табліц шляхам выбару на ўкладцы Устаўка – зводная табліца (Устаўка – зводная табліца) і ўвод імя разумнай табліцы ў якасці крыніцы дадзеных:
Калі вылучыць фрагмент такой табліцы (напрыклад, першыя два слупка) і стварыць дыяграму любога тыпу, то пры даданні новых радкоў яны будуць аўтаматычна дадавацца ў дыяграму.
Пры стварэнні выпадаючых спісаў нельга выкарыстоўваць прамыя спасылкі на элементы разумнай табліцы, але вы можаце лёгка абыйсці гэтае абмежаванне з дапамогай тактычнай хітрасці - скарыстацца функцыяй ускосна (УСКАСНЫ), які ператварае тэкст у спасылку:
Тыя. спасылка на разумную табліцу ў выглядзе тэкставай радкі (у двукоссе!) ператвараецца ў паўнавартасную спасылку, і выпадальны спіс яе нармальна ўспрымае.
Спосаб 2: Дынамічны найменны дыяпазон
Калі ператварэнне вашых дадзеных у разумную табліцу па нейкіх прычынах непажадана, то вы можаце скарыстацца крыху больш складаным, але значна больш тонкім і універсальным спосабам - стварыць дынамічны найменны дыяпазон у Excel, які спасылаецца на нашу табліцу. Затым, як і ў выпадку з разумнай табліцай, вы можаце свабодна выкарыстоўваць імя створанага дыяпазону ў любых формулах, справаздачах, дыяграмах і г. д. Пачнем з простага прыкладу:
Задача: стварыць дынамічны найменны дыяпазон, які спасылаўся б на спіс гарадоў і аўтаматычна расцягваўся і змяншаўся ў памеры пры даданні новых гарадоў або іх выдаленні.
Нам спатрэбяцца дзве ўбудаваныя функцыі Excel, даступныя ў любой версіі − ПОІЦПОЗ (МАТЧ) каб вызначыць апошнюю ячэйку дыяпазону, і індэкс (ІНДЭКС) каб стварыць дынамічную спасылку.
Пошук апошняй ячэйкі з дапамогай MATCH
MATCH(пошукавае_значэнне, дыяпазон, тып_супадзення) – функцыя, якая шукае зададзенае значэнне ў дыяпазоне (радку або слупку) і вяртае парадкавы нумар ячэйкі, дзе яно было знойдзена. Напрыклад, формула MATCH(“March”;A1:A5;0) у выніку верне лічбу 4, таму што слова “March” знаходзіцца ў чацвёртай ячэйцы ў слупку A1:A5. Апошні аргумент функцыі Match_Type = 0 азначае, што мы шукаем дакладнае супадзенне. Калі гэты аргумент не ўказаны, то функцыя пяройдзе ў рэжым пошуку бліжэйшага найменшага значэння - гэта менавіта тое, што можна паспяхова выкарыстоўваць для пошуку апошняй занятай вочка ў нашым масіве.
Сутнасць трука простая. MATCH шукае ячэйкі ў дыяпазоне зверху ўніз і, тэарэтычна, павінен спыняцца, калі знаходзіць самае блізкае да зададзенага значэнне. Калі ў якасці жаданага значэння ўказаць значэнне, відавочна большае за любое з даступных у табліцы, то MATCH дасягне самага канца табліцы, нічога не знойдзе і выдасць парадкавы нумар апошняй запоўненай ячэйкі. А нам гэта трэба!
Калі ў нашым масіве ёсць толькі лічбы, то ў якасці жаданага значэння мы можам пазначыць лік, відавочна большы за любое з табліц:
Для гарантыі можна выкарыстоўваць лік 9E + 307 (9 памножаных на 10 у ступені 307, г.зн. 9 з 307 нулямі) – максімальнае лік, з якім у прынцыпе можа працаваць Excel.
Калі ў нашым слупку ёсць тэкставыя значэнні, то ў якасці эквівалента максімальна магчымага ліку можна ўставіць канструкцыю REPEAT(«i», 255) - тэкставы радок, які складаецца з 255 літар «i» - апошняй літары алфавіт. Паколькі Excel сапраўды параўноўвае коды сімвалаў падчас пошуку, любы тэкст у нашай табліцы тэхнічна будзе «меншым», чым такі доўгі радок «yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
Стварыце спасылку з дапамогай INDEX
Цяпер, калі мы ведаем пазіцыю апошняга непустога элемента ў табліцы, засталося сфармаваць спасылку на ўвесь наш дыяпазон. Для гэтага мы выкарыстоўваем функцыю:
INDEX(дыяпазон; нумар_радка; нумар_слупка)
Ён дае змесціва ячэйкі з дыяпазону па нумары радка і слупка, г. зн., напрыклад, функцыя =INDEX(A1:D5;3;4) у нашай табліцы з гарадамі і месяцамі з папярэдняга метаду дасць 1240 – змест з 3-га радка і 4-га слупка, г.зн. вочак D3. Калі слупок толькі адзін, то яго нумар можна апусціць, г.зн. формула INDEX(A2:A6;3) дасць на апошнім скрыншоце «Самара».
І ёсць адзін не зусім відавочны нюанс: калі INDEX не проста ўводзіцца ў вочка пасля знака =, як звычайна, а выкарыстоўваецца ў якасці завяршальнай часткі спасылкі на дыяпазон пасля двукроп'я, то ён ужо не выдае змесціва ячэйкі, але яе адрас! Такім чынам, формула тыпу $A$2:INDEX($A$2:$A$100;3) дасць спасылку на дыяпазон A2:A4 на выхадзе.
І вось тут прыходзіць функцыя MATCH, якую мы ўстаўляем у INDEX, каб дынамічна вызначаць канец спісу:
=$A$2:ІНДЭКС($A$2:$A$100; МАТЧ(ПАВ(“Я”;255);A2:A100))
Стварыце найменны дыяпазон
Засталося ўсё гэта спакаваць у адзінае цэлае. Адкрыйце ўкладку формула (Формулы) І націсніце Менеджэр імёнаў (Менеджэр імёнаў). У якое адкрылася акне націсніце кнопку Ствараць (новы), увядзіце назву дыяпазону і формулу ў поле Range (Даведка):
Засталося націснуць OK і гатовы дыяпазон можна выкарыстоўваць у любых формулах, выпадальных спісах або дыяграмах.
- Выкарыстанне функцыі VLOOKUP для сувязі табліц і значэнняў пошуку
- Як стварыць выпадальны спіс з аўтаматычным запаўненнем
- Як стварыць зводную табліцу для аналізу вялікай колькасці даных