Excel кестелерінде құрылымдық сілтемелерді қолдану

Құрылымдық сілтемелер кесте, кесте бөліктері немесе толық кестеге өрнектер қолдану кезінде кесте деректерімен жұмысты жеңілдетеді және тиімділігін арттырады. Олар әсіресе кесте дерегі ауқымының жиі өзгеріп, ұяшық айнымалы сілтемелерінің автоматты түрде құрылымдық айнымалы сілтемелерге сай лайықталып отыратындықтан да тиімді болып табылады. Бұл жолдар мен бағандар қосылғанда не жойылған кезде немесе сыртқы дерек жаңартылған кезде өрнектерді қайта жазу қажеттігін азайтады.

Берілген құрылымдық айнымалы сілтемені түсіну оңайырақ:

Мына ұяшыққа қарағанда:

=SUM (DeptSales[SaleAmt])

=Sum(C2:C7)

Бұл мақалада

Бөлім сатылымдары кестесінің мысалы

Құрылымдық сілтеменің құрамдас бөліктері

Кесте атауы мен баған белгілегіштер

Сілтеме амалдағыштары

Арнайы элемент белгілегіштері

Есептелінетін бағандардағы құрылымдық сілтемелерді анықтау

Құрылымдық сілтемелерді қолдану мысалдары

Құрылымдық сілтемелермен жұмыс істеу

Құрылымдық сілтеме синтаксисінің ережелері

Бөлім сатылымдары кестесінің мысалы

Мұнда бөлімнің алты қызметкерінің соңғы сауда көлемдері мен комиссиондық көрсеткіштеріне негізделетін кестеге мысал келтіріледі.

Бөлім сатылымдарының кестесі

Бөлім сатылымдарының мысалдық кестесі

1. Толық кесте (A1:E8)

2. Кесте деректері (A2:E7)

3. Баған және баған тақырыбы (D1:D8)

4. Есептелінетін баған (E1:E8)

5. Жолдар қорытындысы (A8:E8)

Беттің жоғарғы жағы

Құрылымдық сілтеменің құрамдас бөліктері

Кесте және құрылымдық сілтемелермен тиімді жұмыс істеу үшін өрнек құру кезінде құрылымдық сілтемелердің синтаксисін жасау жолдарын білу керек. Барлық сауда көлемі мен комиссиялық сауда көлемінің қосындысы берілген өрнектің келесі мысалында құрылымдық сілтеменің құрамдас бөлшектері көрсетілген:

Құрылымдық сілтемелер берілген өрнек мысалы

1. Кесте атауы ағымдағы кесте деректеріне сілтеме жасауға арналған мағыналы атау (үстіңгі деректемесі жолы мен жиын жолын қоспағанда).

2. Баған белгілегіші тік жақшаларда қоршалған баған үстіңгі деректемесінен шығарылады және баған деректеріне сілтеме жасайды (үстіңгі деректемесі жолы мен жиынды қоспағанда).

3. Арнайы элемент белгілегіші кестенің белгілі бір бөліктеріне, мысалы Жиыны жолы үшін, арналған .

4. Кесте белгілегіші құрылымдық сілтеменің сыртқы бөлігі болып табылады және кесте атынан кейінгі шаршы тік жақшалармен қоршалған.

5. Құрылымдық сілтеме - кесте атауымен басталып, кесте белгілеуішімен аяқталатын толық жол.

Беттің жоғарғы жағы

Кесте атауы мен баған белгілегіштер

Кестені енгізген сайын, Microsoft Office Excel жалпы жұмыс парағы деңгейінде немесе аумағында сәйкес кесте атауын (Кесте1, Кесте2 және т.с.с). Өзіңізге мағынасы барынша ыңғайлы болуы үшін оның атауын оңай өзгерте аласыз. Мысалы, Кесте 1 атауын Бөлім сатылымдары деп өзгерту үшін Атауды өзгерту тілқатысу терезесін қолдана аласыз. (Жасақтама қойындысындағы Сипаттар тобынан , Кесте атауы жолағында кесте атауын өңдеңіз.)

Кесте атауы үстіңгі атау жолы мен жиын жолдарын қоспағандағы кестеде берілген деректердің толық ауқымына сілтеме жасайды. Бөлім саудасы кестесінің мысалы ішіндегі Бөлім саудасы кесте атауы А2:Е7 ұяшықтар ауқымына сілтеме жасайды.

Кесте атауларымен бірдей, баған белгілегіштері баған үстіңгі деректемесі мен жиынды қоспағандағы деректердің толық бағанына сілтемелерді көрсетеді. Бөлім саудасы кестесінің мысалы, [Region] баған белгілегіші ,B2:B7 ұяшықтар ауқымына сілтеме жасайды және [ComPct] баған белгілегіші D2:D7 ұяшықтар ауқымына сілтеме жасайды.

Беттің жоғарғы жағы

Сілтеме амалдағыштары

Ұяшықтардың көрсетілген ауқымына барынша икемді ету қажет болғанда, баған белгілегіштерін қиыстыру үшін келесі сілтеме амалдағыштарын пайдалана аласыз.

Бұл құрылымдық сілтеме:

Сілтеме жасайы:

Қолдану арқылы:

Мысал ішінде ұяшық ауқымы:

=DeptSales[[SalesPers]:[Region]]

Барлық ұяшықтар екі немесе одан да көп бағандарда орналасқан

: (қос нүкте) ауқым амалдағышы

A2:B7

=DeptSales[SaleAmt],DeptSales[ComAmt]

Екі немесе одан да көп бағандардың тіркесімі

, (үтір) бірлестік амалдағышы

C2:C7, E2:E7

=DeptSales[[SalesPers]:[SaleAmt]] DeptSales[[Region]:[ComPct]]

Екі немесе одан да көп бағандардың қиылысуы

 (бос орын) қиылысу амалдағышы

B2:C7

Беттің жоғарғы жағы

Арнайы элемент белгілегіштері

Тиімділігін арттыру үшін арнайы элементтерді кестенің әртүрлі бөліктеріне, мысалы Жиыны жолына сілтеме жасау үшін, сондай-ақ өрнектердегі осы бөліктерге сілтеме жасауды жеңілдету үшін пайдалана аласыз. Келесі көрсетілгендер құрылымдық сілтемелерге пайдалана алатын арнайы элемент белгілеуіштері болып табылады:

Бұл арнай элемент белгілеуіші:

Сілтеме жасайды:

Мысал ішінде ұяшық ауқымы:

=DeptSales[#All]

Баған үстіңгі атауы, дерек және жиынды қоса алғандағы (егер болса) толық кесте.

A1:E8

=DeptSales[#Data]

Тек дерек қана.

A2:E7

=DeptSales[#Headers]

Тек үстіңгі дерек жолы ғана.

A1:E1

=DeptSales[#Totals]

Тек жиын жолы ғана. Егер ол болмаса, бос орынға қайтарылады.

A8:E8

=DeptSales[#This Row]

Тек ағымдағы жолдағы баған бөліктері ғана. # Бұл жол басқа арнайы элементтер белгілеуішімен тіркеспейді. Мұны сілтеме үшін қиылысу әрекетін жүзеге асыру үшін немесе қиылысу әрекетін елемеу үшін және баған ішіндегі жалғыз мәндерге сілтеме жасауға пайдалануға болады. Қосымша мысалдармен танысу үшін, Құрылымдық сілтемелерді қолдану мысалдарыбөлімін қараңыз.

A5:E5 (Егер ағымдағы жол 5 болса)

Беттің жоғарғы жағы

Есептелінетін бағандардағы құрылымдық сілтемелерді анықтау

Есептелінетін өрнек құруыңыз кезінде әдетте құрылымдық сілтемені қолданасыз. Бұл құрылымдық сілтеме жарамай қалуы немесе толығымен жарауы мүмкін. Мысалы, доллар бойынша комиссиялық сауда көлемін есептейтін ComAmt бағанын жасау үшін келесі өрнектерді пайдалана аласыз:

Құрылымдық сілтеме түрі

Мысал

Аңғартпа

Анықталмаған

=[SaleAmt]*[ComPct]

Ағымдағы жолдан сәйкес мәндері көбейтеді.

Толығымен жарамды

=DeptSales[SaleAmt]*DeptSales[ComPct]

Екі бағанның әр жолы үшін сәйкес мәндерді көбейтеді.

Мұны жүзеге асырудың келесі ережесі: Егер кесте ішінде есептелінетін баған жасау кезінде құрылымдық сілтемені қолдансаңыз, тек анықталмаған құрылымдық сілтемені пайдалана аласыз, бірақ егер құрылымдық сілтемені кестеден тыс қолдансаңыз, толығымен анықталған құрылымдық сілтемені пайдалана аласыз.

Беттің жоғарғы жағы

Құрылымдық сілтемелерді қолдану мысалдары

Арнайы элементтерді қолдану мен оларды кесте атаулары мен баған сілтемелермен қиыстырудың келесі ақпарат көрсеткендей бірнеше жолы бар:

Бұл құрылымдық сілтеме:

Сілтеме жасайды:

Мысал , ұяшық ауқымы:

=DeptSales[[#All],[SaleAmt]]

SaleAmt бағанындағы барлық ұяшықтар.

C1:C8

=DeptSales[[#Headers],[ComPct]]

ComPct бағанының үстіңгі деректемесі.

C1

=DeptSales[[#Totals],[Region]]

Аймақ бағанының жиыны. Егер Жиыны жолы болмаса, ол бос орынға оралады.

B8

=DeptSales[[#All],[SaleAmt]:[ComPct]]

SaleAmt және ComPct бағандарының ішіндегі барлық ұяшықтар.

C1:D8

=DeptSales[[#Data],[ComPct]:[ComAmt]]

Тек ComPct және ComAmt бағандарының деректері ғана.

D2:E7

=DeptSales[[#Headers],[Region]:[ComAmt]]

Аймақ және ComPct ComAmt арасындағы бағандардың үстіңгі деректемелері ғана.

B1:E1

=DeptSales[[#Totals],[SaleAmt]:[ComAmt]]

ComAmt бағаны SaleAmt жиыны. Егер Жиыны жолы болмаса, ол бос орынға қайта оралады.

C8:E8

=DeptSales[[#Headers],[#Data],[ComPct]]

Тек үстіңгі деректемесі мен ComPct дерегі ғана.

D1:D7

=DeptSales[[#This Row], [ComAmt]]

Ұяшық ағымдағы жол мен ComAmt бағанының қиылысуында орналасқан.

E5 (егер ағымдағы жол 5 болса)

Беттің жоғарғы жағы

Құрылымдық сілтемелермен жұмыс істеу

Құрылымдық сілтемелермен жұмыс кезінде келесілерді есте сақтаңыз.

Автоаяқтау өрнегін қолдану    

Құрылымдық сілтемелерді енгізу кезінде және дұрыс синтаксиспен қамтамасыз етуде Автоаяқтау өрнегін қолдану өте тиімді болып табылуы мүмкін. Қосымша ақпарат үшін Автоаяқтау өрнегін қолдану бөлімін қараңыз.

Жартылай таңдаулардағы кестелер үшін құрылымдық сілтеме жасау қажеттігін шешу    

Өрнек құру кезінде кестедегі ұяшықты нұқу ұяшықты жартылай таңдауды жүзеге асырып, өрнектегі ұяшық ауқымы орнына автоматты түрде құрылымдық сілтемені енгізеді. Жартылай таңдау әрекеті құрылымдық сілтемені енгізуді анағұрлым жеңілдетеді. Excel параметрлері тілқатысу терезесіндегі Өрнектер санатының Өрнектермен жұмыс жасау бөлімінен Кесте атауларын өрнектерде пайдалану параметр жанына құсбелгі қою немесе алу арқылы бұл әрекетті қоса немесе өшіре аласыз.

Ауқымды кестеге және кестені ауқымға түрлендіру    

Кестені ауқымға түрлендіру кезінде барлық ұяшық сілтемелері өздеріне сәйкес A1 мәнеріндегі сілтемелерге өзгереді. Ал егер ауқым кестеге түрлендірілсе, Excel ұяшық сілтемелерін сәйкес кесте атауларына немесе баған сілтемелеріне автоматты түрде өзгертпейді.

Баған үстіңгі атауын өшіру    

Егер кесте бағанының үстіңгі деректемесін өшіретін болсаңыз (Кестенің Жасақтама қойындысында, Кесте мәнерінің параметрлері тобынан Тақырып жолы түймешігін аластаңыз), үстіңгі деректемесін қолданатын құрылымдық сілтемелер өзгеріссіз қалады және оларды әлі де өрнектерде пайдалана аласыз.

Кестеге баған немесе жолды қосу немесе одан жою    

Кесте дерегінің ауқымы жиі өзгеріске ұшырауына байланысты құрылымдық сілтемелердің ұяшық сілтемелері автоматты түрде лайықталады.Егер Бөлім саудасы кестесіндегі деректер ұяшықтарын есептеу үшін өрнектегі кесте атауын қолдансаңыз, мысалы Бөлім саудасы кестесі мысалы ішіндегі =COUNTA(Бөлім саудасы) , қайтарылатын мән 30, себебі дерек ауқымы A2:E7. Егер соңынан деректер жолы қосылса, онда ұяшық сілтемесі автоматты түрде A2:E8 болып лайықталады және қайтарылатын жаңа мән 35 болып өзгереді.

Кесте немесе бағанды қайта атау    

Егер баған немесе кестені қайта атасаңыз, Excel жұмыс парағында пайдаланылатын барлық құрылымдық сілтемелердегі кесте мен баған үстіңгі деректемесінің қолданысын автоматты түрде өзгертеді.

Құрылымдық сілтемелерді жылжыту, көшіру және бояу    

Құрылымдық сілтемені қолданатын өрнекті көшіргенде немесе жылжытқанда барлық құрылымдық сілтемелер сол күйінде сақталады.

Өрнекті бояған кезде толығымен анықталған құрылымдық сілтемелер келесі кестеде жинақталған қатарлар секілді баған белгілеуіштерді лайықтайды.

Егер бояу бағыты:

Бояу кезінде басасыз:

Кейін:

Жоғары немесе төмен

Ештеңе

Баған белгілеуіші баптаулары жоқ.

Жоғары немесе төмен

CTRL

Баған белгілеуіштер қатарлар секілді лайықтайды.

Оң немесе сол

Ешбір

Баған белгілеуіштері қатар секілді лайықтайды.

Оң немесе сол

CTRL

Баған белгілеуіштерінің баптаулары жоқ.

Жоғары, төмен, оң немесе сол

SHIFT

Ағымдағы ұяшықтағы мәндерді қайта жазу орнына ағымдағы ұяшық мәндері жылжытылады және баған белгілеуіштері енгізіледі.

Беттің жоғарғы жағы

Құрылымдық сілтеме синтаксисінің ережелері

Мұнда құрылымдық сілтемені жасау немесе өңдеу кезінде білуіңіз керек синтаксистік ережелер тізімі беріледі.

Ескерту : Кесте атаулары ережелерге анықталған атаулармен бірдей бағынады. Қосымша ақпарат алу үшін Өрнектерді аластау үшін атауларды қолдану бөлімін қараңыз.

Белгілеуіштер ішінде тік жақшаларды қолдану    

Барлық кесте, баған және арнайы элемент белгілеуіштері сәйкес тік жақшаларда қоршалуы керек ([ ]). Басқа белгілеуіштерге ие белгілеуіш басқа белгілеуіштердің ішкі сәйкес тік жақшаларын қоршау үшін сыртқы тік жақшалармен қоршалуды қажет етеді.

Мысал     =DeptSales[[SalesPers]:[Region]]

Баған үстіңгі деректемелері мәтін жолдары болып табылады    

Барлық баған үстіңгі деректемелері мәтін жолдары болып табылады, бірақ олар құрылымдық сілтемелерде қолданылу кезінде тырнақшаларды қажет етпейді. Егер баған үстіңгі деректемелерінде нөмір немесе күн-ай берілсе, мысалы 2004 немесе 1/1/2004, олардың барлығы мәтін жолы ретінде қабылданады. Баған үстіңгі деректемелері мәтін жолы болып табылғандықтан, тік жақшалар ішінде өрнек қолдана алмайсыз.

Мысал     =DeptSalesFYSummary[[2004]:[2002]]

Кесте бағанының үстіңгі деректемесіндегі арнайы таңбалар    

Егер кесте бағынының үстіңгі деректемелері келесі арнайы таңбаларға ие болса, толық баған үстіңгі деректемесі тік жақшада қоршалуы керек. Бұл қос тік жақшаның келесі арнайы таңбалары бар баған белгілеуіштерде қажет етілетіндігін білдіреді: Бос орын, қойынды, жол ауыстыру, кірістірмені қайтару, үтір (,), қос нүкте (:), период (.), сол жақ тік жақша ([) , оң жақ тік жақша(]), фунт белгісі (#), жалғыз тырнақша ('), қос тырнақша ("), оң жақ бейнелі жақша ({), сол жақ бейнелі жақша (}), доллар белгісі ($), енгізу белгісі (^), амперсанд (&), жұлдызша (*), қосу белгісі (+), теңдік белгісі (=), алу белгісі (-), символдан үлкен (>), символдан кіші (<) және бөлу белгісі (/).

Мысал     =DeptSalesFYSummary[[Total$Amount]]

Бұл ережеге бағынбайтыны: егер арнайы таңба ретінде тек бос орын таңбасының қолданылуы.

Мысал    =DeptSales[Total Amount]

Баған үстіңгі деректемесіндегі escape-таңбасын қолдануды талап ететін арнайы таңбалар    

Келесі таңбалар арнайы мағынаға ие және олар жалғыз тырнақша белгісін (") escape-таңбасы ретінде: Сол жақ тік жақша ([), оң жақ тік жақша (]), фунт белгісі (#) және жалғыз тырнақша белгісі (') қолдануды талап етеді.

Мысал     =DeptSalesFYSummary['#OfItems]

Құрылымдық сілтеменің оқылуын жақсарту үшін бос орны таңбаларын пайдалану    

Құрылымдық сілтемелердің оқылуын жетілдіру үшін бос орын таңбаларын келесі жолмен пайдалана аласыз:

  • Бірінші сол жақ тік жақшадан кейін ([) және соңғы сол жақ тік жақша алдына (]) бос орын қою.

  • Қос нүктеден кейін бір бос орын.

Мысал     =DeptSales[ [SalesPers]:[Region] ]

Мысал     =DeptSales[[#Headers], [#Data], [ComPct]]

Беттің жоғарғы жағы

Дағдыларды жетілдіру
Оқыту курсымен танысыңыз
Жаңа мүмкіндіктерге бірінші болып қол жеткізу
Office Insider бағдарламасына қосылу

Осы ақпарат пайдалы болды ма?

Пікіріңіз үшін рақмет!

Пікіріңізге рақмет! Сізді Office қолдау көрсету қызметіндегі агенттердің бірімен байланыстырған жөн болуы мүмкін.

×