мазмуну
Сизде 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 деген эмне?)
Мындай шилтемелерди өтмөктө тандоо менен пивот таблицаларын түзүүдө ийгиликтүү колдонсо болот Insert – Pivot Table (Кыстаруу – Pivot Table) жана маалымат булагы катары акылдуу столдун атын киргизүү:
Эгерде сиз ушундай таблицанын фрагментин (мисалы, биринчи эки мамычаны) тандап, каалаган түрдөгү диаграмманы түзсөңүз, анда жаңы саптарды кошкондо алар автоматтык түрдө диаграммага кошулат.
Ашылма тизмелерди түзүүдө акылдуу үстөлдүн элементтерине түз шилтемелерди колдонуу мүмкүн эмес, бирок тактикалык трюк аркылуу бул чектөөдөн оңой чыга аласыз – функцияны колдонуңуз КЫЙЫР (ТУЗДУК), бул текстти шилтемеге айлантат:
Ошол. тексттик сап түрүндөгү акылдуу таблицага шилтеме (тырмакчанын ичинде!) толук кандуу шилтемеге айланат жана ылдый түшүүчү тизме аны адатта кабыл алат.
Метод 2: Динамикалык аталган диапазон
Эгер кандайдыр бир себептерден улам маалыматыңызды акылдуу үстөлгө айландыруу жагымсыз болсо, анда сиз бир аз татаал, бирок бир топ кылдат жана ар тараптуу ыкманы колдонсоңуз болот - Excelде биздин таблицага тиешелүү динамикалык аталган диапазонду түзүңүз. Андан кийин, акылдуу таблицадагыдай эле, сиз түзүлгөн диапазонун атын каалаган формулаларда, отчеттордо, диаграммаларда жана башкаларда эркин колдоно аласыз. Жөнөкөй бир мисалдан баштайлы:
маселе: шаарлардын тизмесине шилтеме жасай турган динамикалык аталган диапазонду түзүңүз жана жаңы шаарларды кошкондо же аларды жок кылганда автоматтык түрдө чоюлуп, кичирейген.
Бизге каалаган версияда жеткиликтүү эки камтылган Excel функциясы керек болот - POICPOZ (МАТЧ) диапазонун акыркы уячасын аныктоо үчүн, жана INDEX (ИНДЕКС) динамикалык шилтеме түзүү.
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 чындыгында издөөдө символдук коддорду салыштыргандыктан, биздин таблицадагы каалаган текст техникалык жактан ушунчалык узун "жыййййййййййййййййййййййййййййййй" сапка караганда "кичирээк" болот:
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 функциясы кирет, аны биз тизменин соңун динамикалык түрдө аныктоо үчүн INDEX ичине киргизебиз:
=$A$2:INDEX($A$2:$A$100; MATCH(REP("I";255);A2:A100))
Аты аталган диапазонду түзүңүз
Мунун баарын бир бүтүнгө жыйноо керек. Өтмөк ачуу формула (Формулалар) Жана бас Аты менеджер (Аты-жөнү менеджери). Ачылган терезеде баскычты басыңыз түзүү (жаңы), талаага диапазонун атын жана формуласын киргизиңиз кырка (Шилтеме):
Бул басуу үчүн калды OK жана даяр диапазону каалаган формулаларда, ачылуучу тизмелерде же диаграммаларда колдонсо болот.
- Таблицаларды жана маанилерди издөө үчүн VLOOKUP функциясын колдонуу
- Автоматтык түрдө ачылуучу тизмени кантип түзүү керек
- Чоң көлөмдөгү маалыматтарды талдоо үчүн пивот таблицасын кантип түзүүгө болот