Мазмұны
Excel бағдарламасында өлшемін өзгертуге болатын деректері бар кестелер бар ма, яғни жұмыс барысында жолдар (бағандар) саны көбеюі немесе азаюы мүмкін бе? Егер кесте өлшемдері «қалқымалы» болса, онда сіз осы сәтті үнемі бақылап, оны түзетуге тура келеді:
- кестеге сілтеме жасайтын есеп формулаларындағы сілтемелер
- біздің кестеге сәйкес құрастырылған жиынтық кестелердің бастапқы диапазондары
- кестемізге сәйкес құрастырылған диаграммалардың бастапқы диапазондары
- кестені деректер көзі ретінде пайдаланатын ашылмалы тізімдерге арналған ауқымдар
Мұның бәрі сізді жалықтырмайды 😉
Жолдар мен деректер бағандарының нақты санына өлшемін автоматты түрде реттейтін динамикалық «резеңке» диапазонын жасау әлдеқайда ыңғайлы және дұрыс болады. Мұны жүзеге асырудың бірнеше жолы бар.
1-әдіс. Ақылды үстел
Ұяшықтар ауқымын бөлектеп, қойындыдан таңдаңыз Басты – Кесте ретінде пішімдеу (Үй – Кесте ретінде пішімдеу):
Егер сізге жанама әсер ретінде кестеге қосылған жолақты дизайн қажет болмаса, оны пайда болатын қойындыда өшіруге болады. Конструктор (дизайн). Осылайша жасалған әрбір кесте қойындының бір жерінде ыңғайлырақ ауыстырылатын атауды алады. Конструктор (дизайн) далада Кесте атауы (Кесте атауы).
Енді біз «ақылды үстелге» динамикалық сілтемелерді пайдалана аламыз:
- Кесте 1 – тақырып жолынан басқа бүкіл кестеге сілтеме (A2:D5)
- 1-кесте[#Барлығы] – толық кестеге сілтеме (A1:D5)
- 1-кесте[Питер] – бірінші ұяшық тақырыбы жоқ диапазон-бағанға сілтеме (C2:C5)
- 1-кесте[#Тақырыптар] – баған атаулары бар «тақырыпқа» сілтеме (A1:D1)
Мұндай сілтемелер формулаларда жақсы жұмыс істейді, мысалы:
= SUM (1-кесте[Мәскеу]) – «Мәскеу» бағанының сомасын есептеу
or
=VPR(F5;Кесте 1;3;0) – F5 ұяшығынан ай бойынша кестеден іздеу және оған Санкт-Петербург сомасын шығару (VLOOKUP дегеніміз не?)
Мұндай сілтемелерді қойындыда таңдау арқылы жиынтық кестелерді жасау кезінде сәтті пайдалануға болады Кірістіру – Жиынтық кесте (Кірістіру – Жиынтық кесте) және деректер көзі ретінде смарт кестенің атын енгізу:
Егер сіз осындай кестенің фрагментін таңдасаңыз (мысалы, алғашқы екі баған) және кез келген типтегі диаграмманы жасасаңыз, онда жаңа жолдарды қосқанда олар диаграммаға автоматты түрде қосылады.
Ашылмалы тізімдерді жасаған кезде смарт кесте элементтеріне тікелей сілтемелерді пайдалану мүмкін емес, бірақ тактикалық трюк арқылы бұл шектеуді оңай айналып өтуге болады – функцияны пайдаланыңыз. ТӘУЕЛСІЗДІК (ТІКЕСІЗ), ол мәтінді сілтемеге айналдырады:
Анау. мәтіндік жол түріндегі смарт кестеге сілтеме (тырнақшада!) толыққанды сілтемеге айналады және ашылмалы тізім оны әдетте қабылдайды.
2-әдіс: Динамикалық атаулы ауқым
Егер қандай да бір себептермен деректеріңізді смарт кестеге айналдыру қажет болмаса, онда сіз сәл күрделірек, бірақ әлдеқайда нәзік және жан-жақты әдісті пайдалана аласыз - Excel бағдарламасында біздің кестеге сілтеме жасайтын динамикалық аталған диапазон жасаңыз. Содан кейін, смарт кесте жағдайындағы сияқты, сіз кез келген формулаларда, есептер, диаграммалар және т.б. үшін жасалған ауқымның атын еркін пайдалана аласыз. Қарапайым мысалдан бастайық:
тапсырма: қалалар тізіміне сілтеме жасайтын динамикалық атаулы ауқымды жасаңыз және жаңа қалаларды қосқанда немесе оларды жойған кезде өлшемі автоматты түрде ұзартылады және кішірейеді.
Бізге кез келген нұсқада қол жетімді екі кірістірілген Excel функциясы қажет болады - POICPOZ (МАТЧ) диапазонның соңғы ұяшығын анықтау үшін және INDEX (ИНДЕКС) динамикалық сілтеме жасау үшін.
MATCH көмегімен соңғы ұяшықты табу
SATCH(іздеу_мәні, ауқым, сәйкестік_түрі) – берілген мәнді диапазонда (жол немесе баған) іздейтін және ол табылған ұяшықтың реттік нөмірін беретін функция. Мысалы, 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) формуласы соңғы скриншотта «Самара» береді.
Және бір анық емес нюанс бар: егер INDEX әдеттегідей ұяшыққа = белгісінен кейін ғана енгізілмесе, бірақ қос нүктеден кейінгі диапазонға сілтеменің соңғы бөлігі ретінде пайдаланылса, ол енді берілмейді. ұяшықтың мазмұны, бірақ оның мекенжайы! Осылайша, $A$2:INDEX($A$2:$A$100;3) сияқты формула шығыстағы A2:A4 ауқымына сілтеме береді.
Тізімнің соңын динамикалық түрде анықтау үшін INDEX ішіне енгізетін MATCH функциясы осы жерде келеді:
=$A$2:INDEX($A$2:$A$100; МАТЧ(REP(“I”;255);A2:A100))
Атаулы ауқымды жасаңыз
Оның бәрін бір бүтінге жинақтау ғана қалады. Қойынды ашыңыз формула (Формулалар) Және түймесін басыңыз Аты менеджері (Аты менеджері). Ашылған терезеде түймені басыңыз жасау (жаңа), өріске диапазон атауы мен формуланы енгізіңіз диапазон (Сілтеме):
Оны басу қалды OK және дайын ауқымды кез келген формулаларда, ашылмалы тізімдерде немесе диаграммаларда пайдалануға болады.
- Кестелерді байланыстыру және мәндерді іздеу үшін VLOOKUP функциясын пайдалану
- Автоматты түрде толтырылатын ашылмалы тізімді қалай жасауға болады
- Деректердің үлкен көлемін талдау үшін жиынтық кестені қалай жасауға болады