Як задаць дыяпазон значэнняў у Excel

Пры выкарыстанні Microsoft Excel вельмі часта карыстальнік загадзя не ведае, колькі інфармацыі ў выніку апынецца ў табліцы. Такім чынам, мы не ва ўсіх сітуацыях разумеем, які дыяпазон павінен быць ахоплены. Бо набор вочак - паняцце зменнае. Каб пазбавіцца ад гэтай праблемы, неабходна зрабіць аўтаматычную генерацыю дыяпазону, каб яна грунтавалася выключна на аб'ёме дадзеных, уведзеных карыстальнікам.

Аўтаматычнае змяненне дыяпазонаў вочак у Excel

Перавага аўтаматычных дыяпазонаў у Excel заключаецца ў тым, што яны значна палягчаюць выкарыстанне формул. Акрамя таго, яны дазваляюць значна спрасціць аналіз складаных дадзеных, якія змяшчаюць вялікую колькасць формул, якія ўключаюць мноства функцый. Вы можаце даць гэтаму дыяпазону назву, і тады ён будзе аўтаматычна абнаўляцца ў залежнасці ад таго, якія даныя ён змяшчае.

Як зрабіць аўтаматычнае змяненне дыяпазону ў Excel

Выкажам здагадку, вы інвестар, якому трэба ўкласціся ў нейкі аб'ект. У выніку мы хочам атрымаць інфармацыю аб тым, колькі вы можаце зарабіць у агульнай складанасці за ўвесь час, што грошы будуць працаваць на гэты праект. Тым не менш, каб атрымаць гэтую інфармацыю, нам неабходна рэгулярна сачыць за тым, колькі сумарнага прыбытку прыносіць нам гэты аб'ект. Складзіце такую ​​ж справаздачу, як на гэтым скрыншоце.

На першы погляд, разгадка відавочная: трэба проста прасумаваць увесь слупок. Калі ў ім з'явяцца запісы, сума абнаўляецца самастойна. Але ў гэтага метаду ёсць шмат недахопаў:

1. Калі задача будзе вырашана такім чынам, то вочкі, якія ўваходзяць у слупок Б, выкарыстоўваць не па прызначэнні будзе немагчыма.
2. Такая табліца будзе спажываць шмат аператыўнай памяці, што зробіць немагчымым выкарыстанне дакумента на слабых кампутарах.

Такім чынам, неабходна вырашыць гэтую праблему праз дынамічныя імёны. Для іх стварэння неабходна выканаць наступную паслядоўнасць дзеянняў:

1. Перайдзіце ва ўкладку «Формулы», якая знаходзіцца ў галоўным меню. Там будзе раздзел «Вызначаныя імёны», дзе ёсць кнопка «Прысвоіць імя», на якую нам трэба націснуць.
2. Далей з'явіцца дыялогавае акно, у якім неабходна запоўніць палі, як паказана на скрыншоце. Важна адзначыць, што нам трэба прымяніць функцыю = ЗМЯШЧЭННЕ разам з функцыяй ПРАВЕРКАкаб стварыць дыяпазон аўтаматычнага абнаўлення. 
3. Пасля гэтага нам трэба выкарыстоўваць функцыю SUM, які выкарыстоўвае наш дынамічны дыяпазон у якасці аргумента. 

Пасля выканання гэтых крокаў мы можам убачыць, як абнаўляецца ахоп ячэек, якія належаць да дыяпазону «даход», калі мы дадаем туды новыя элементы.

Функцыя OFFSET у Excel

Давайце паглядзім на функцыі, якія мы запісалі ў поле «дыяпазон» раней. Выкарыстанне функцыі ВЫКАРЫСТАННЕ мы можам вызначыць колькасць дыяпазону, улічваючы, колькі вочак у слупку B запоўнена. Аргументы функцыі наступныя:

1. Пачаць вочка. З дапамогай гэтага аргумента карыстальнік можа паказаць, якая ячэйка ў дыяпазоне будзе лічыцца верхняй левай. Ён будзе паведамляць уніз і направа.
2. Дыяпазон зрушаны радкамі. Выкарыстоўваючы гэты дыяпазон, мы ўсталёўваем колькасць вочак, на якія павінна адбывацца зрушэнне ад левай верхняй ячэйкі дыяпазону. Вы можаце выкарыстоўваць не толькі станоўчыя значэння, але нуль і мінус. У гэтым выпадку зрушэнне можа не адбыцца зусім, альбо яно будзе ажыццяўляцца ў адваротным кірунку.
3. Зрушэнне дыяпазону слупкамі. Гэты параметр падобны на папярэдні, толькі ён дазваляе задаць ступень гарызантальнага зрушэння дыяпазону. Тут вы таксама можаце выкарыстоўваць як нулявыя, так і адмоўныя значэнні.
4. Колькасць дыяпазону па вышыні. Фактычна, назва гэтага аргументу дае нам зразумець, што ён азначае. Гэта колькасць вочак, на якое павінен павялічвацца дыяпазон.
5. Значэнне дыяпазону па шырыні. Аргумент аналагічны папярэдняму, толькі тычыцца слупкоў.

Вам не трэба ўказваць апошнія два аргументы, калі вам гэта не трэба. У гэтым выпадку значэнне дыяпазону будзе складаць толькі адну ячэйку. Напрыклад, калі задаць формулу =ЗРУШЭННЕ(A1;0;0), гэтая формула будзе спасылацца на тую ж ячэйку, што і першая вочка. Калі вертыкальнае зрушэнне ўстаноўлена ў 2 адзінкі, то ў гэтым выпадку вочка будзе спасылацца на вочка A3. Зараз давайце падрабязна апішам, што азначае функцыя ПРАВЕРКА.

Функцыя COUNT у Excel

Выкарыстанне функцыі ПРАВЕРКА мы вызначаем, колькі клетак у слупку B мы запоўнілі ў агульнай складанасці. Гэта значыць з дапамогай двух функцый мы вызначаем, колькі вочак у дыяпазоне запоўнена, і на аснове атрыманай інфармацыі вызначаем памер дыяпазону. Такім чынам, канчатковая формула будзе наступнай: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Давайце разбярэмся, як правільна разумець прынцып гэтай формулы. Першы аргумент паказвае на тое, дзе пачынаецца наш дынамічны дыяпазон. У нашым выпадку гэта вочка B2. Далейшыя параметры маюць нулявыя каардынаты. Гэта сведчыць аб тым, што нам не патрэбныя зрушэнні адносна левай верхняй ячэйкі. Усё, што мы запаўняем, - гэта вертыкальны памер дыяпазону, у якасці якога мы выкарыстоўвалі функцыю ПРАВЕРКА, які вызначае колькасць ячэек, якія змяшчаюць некаторыя дадзеныя. Чацвёрты параметр, які мы запоўнілі, гэта адзінка. Такім чынам, мы паказваем, што агульная шырыня дыяпазону павінна складаць адзін слупок.

Такім чынам, выкарыстоўваючы функцыю ПРАВЕРКА карыстальнік можа максімальна эфектыўна выкарыстоўваць памяць, загружаючы толькі тыя вочкі, якія ўтрымліваюць некаторыя значэнні. Адпаведна, дадатковых памылак у працы, звязаных з дрэннай працай кампутара, на якім будзе працаваць таблічны працэсар, не будзе.

Адпаведна, каб вызначыць памер дыяпазону ў залежнасці ад колькасці слупкоў, трэба выканаць аналагічную паслядоўнасць дзеянняў, толькі ў гэтым выпадку ў трэцім параметры трэба паказаць адзінку, а ў чацвёртым - формулу. ПРАВЕРКА.

Мы бачым, што з дапамогай формул Excel можна не толькі аўтаматызаваць матэматычныя разлікі. Гэта толькі кропля ў моры, але на самой справе яны дазваляюць аўтаматызаваць практычна любую аперацыю, якая прыйдзе вам у галаву.

Дынамічныя дыяграмы ў excel

Такім чынам, на апошнім этапе мы змаглі стварыць дынамічны дыяпазон, памер якога цалкам залежыць ад колькасці запоўненых вочак. Цяпер на аснове гэтых даных вы можаце ствараць дынамічныя дыяграмы, якія будуць аўтаматычна змяняцца, як толькі карыстальнік унясе якія-небудзь змены або дадасць дадатковы слупок або радок. Паслядоўнасць дзеянняў у гэтым выпадку такая:

1. Выбіраем наш дыяпазон, пасля чаго ўстаўляем графік тыпу «Гістаграма з групоўкай». Вы можаце знайсці гэты пункт у раздзеле «Уставіць» у раздзеле «Дыяграмы - Гістаграма».
2. Пстрыкніце левай кнопкай мышы па выпадковым слупку гістаграмы, пасля чаго ў радку функцый з'явіцца функцыя =SERIES(). На скрыншоце вы бачыце падрабязную формулу. 
3. Пасля гэтага ў формулу неабходна ўнесці некаторыя змены. Вам трэба замяніць дыяпазон пасля "Sheet1!" да назвы дыяпазону. Гэта прывядзе да наступнай функцыі: =РАКОЎ(Аркуш1!$B$1;;Аркуш1!даход;1)
4. Цяпер засталося дадаць новы запіс у справаздачу, каб праверыць, аўтаматычна абнаўляецца дыяграма ці не.

Давайце паглядзім на нашу схему.

Давайце падвядзем вынік, як мы гэта зрабілі. На папярэднім этапе мы стварылі дынамічны дыяпазон, памер якога залежыць ад таго, колькі элементаў ён змяшчае. Для гэтага мы выкарыстоўвалі камбінацыю функцый ПРАВЕРКА и ВЫКАРЫСТАННЕ. Мы назвалі гэты дыяпазон, а потым выкарысталі спасылку на гэта імя ў якасці дыяпазону нашай гістаграмы. Які канкрэтна дыяпазон абраць у якасці крыніцы дадзеных на першым этапе - не гэтак важна. Галоўнае - потым замяніць яго назвай дыяпазону. Такім чынам можна зэканоміць шмат аператыўнай памяці.

Найменныя дыяпазоны і іх выкарыстанне

Давайце цяпер падрабязней пагаворым аб тым, як правільна ствараць найменныя дыяпазоны і выкарыстоўваць іх для выканання задач, якія ставяцца перад карыстальнікам Excel.

Па змаўчанні мы выкарыстоўваем звычайныя адрасы вочак, каб зэканоміць час. Гэта карысна, калі вам трэба напісаць дыяпазон адзін або некалькі разоў. Калі яго неабходна выкарыстоўваць увесь час або ён павінен быць адаптыўным, то варта выкарыстоўваць найменныя дыяпазоны. Яны значна палягчаюць стварэнне формул, а аналізаваць складаныя формулы, якія ўключаюць у сябе вялікую колькасць функцый, карыстальніку не складзе працы. Давайце апішам некаторыя этапы стварэння дынамічных дыяпазонаў.

Усё пачынаецца з наймення клеткі. Для гэтага проста выбіраем яго, а затым пішам патрэбнае нам імя ў поле яго імя. Важна, каб ён лёгка запамінаўся. Ёсць некаторыя абмежаванні, якія варта ўлічваць пры прысваенні імя:

1. Максімальная даўжыня - 255 сімвалаў. Гэтага цалкам дастаткова, каб прысвоіць імя, якое душа пажадае.
2. Імя не павінна ўтрымліваць прабелаў. Такім чынам, калі ён змяшчае некалькі слоў, то іх можна падзяліць знакам падкрэслівання.

Калі пазней на іншых аркушах гэтага файла нам спатрэбіцца адлюстраваць гэтае значэнне або прымяніць яго для правядзення далейшых вылічэнняў, то няма неабходнасці пераходзіць на самы першы ліст. Вы можаце проста запісаць імя гэтай ячэйкі дыяпазону.

Наступны крок - стварэнне найменнага дыяпазону. Працэдура ў асноўным такая ж. Спачатку трэба выбраць дыяпазон, а затым паказаць яго назву. Пасля гэтага гэта імя можна выкарыстоўваць ва ўсіх астатніх аперацыях з дадзенымі ў Excel. Напрыклад, найменныя дыяпазоны часта выкарыстоўваюцца для вызначэння сумы значэнняў.

Акрамя таго, можна стварыць найменны дыяпазон на ўкладцы «Формулы» з дапамогай інструмента «Задаць імя». Пасля таго, як мы яго вылучым, з'явіцца акно, дзе нам трэба выбраць імя для нашага дыяпазону, а таксама ўказаць вобласць, на якую ён будзе распаўсюджвацца ўручную. Вы таксама можаце вызначыць, дзе будзе працаваць гэты дыяпазон: на адным аркушы або ва ўсёй кнізе.

Калі дыяпазон імёнаў ужо створаны, то для яго выкарыстання існуе спецыяльны сэрвіс, які называецца дыспетчар імёнаў. Гэта дазваляе не толькі рэдагаваць або дадаваць новыя імёны, але і выдаляць іх, калі яны больш не патрэбныя.

Варта мець на ўвазе, што пры выкарыстанні ў формулах найменных дыяпазонаў, то пасля яго выдалення формулы не будуць аўтаматычна перазапісаны правільнымі значэннямі. Таму могуць узнікаць памылкі. Такім чынам, перш чым выдаляць найменны дыяпазон, неабходна пераканацца, што ён не выкарыстоўваецца ні ў адной з формул.

Іншы спосаб стварыць найменны дыяпазон - атрымаць яго з табліцы. Для гэтага існуе спецыяльны інструмент «Стварыць з вылучэння». Як мы разумеем, каб выкарыстоўваць яго, трэба спачатку выбраць дыяпазон, які мы будзем рэдагаваць, а потым усталяваць месца, у якім у нас будуць загалоўкі. У выніку на аснове гэтых дадзеных Excel будзе аўтаматычна апрацоўваць усе дадзеныя, і загалоўкі будуць прысвойвацца аўтаматычна.

Калі загаловак змяшчае некалькі слоў, Excel аўтаматычна падзяліць іх знакам падкрэслівання.

Такім чынам, мы высветлілі, як ствараць дынамічныя найменныя дыяпазоны і як яны дазваляюць аўтаматызаваць працу з вялікімі аб'ёмамі дадзеных. Як бачыце, дастаткова выкарыстоўваць некалькі функцый і ўбудаваных у функцыянал праграмных сродкаў. Нічога складанага ў гэтым няма, хоць пачаткоўцу гэта можа здацца на першы погляд.