Դինամիկ միջակայք՝ ավտոմատ չափագրմամբ

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-ը):

Նման հղումները կարող են հաջողությամբ օգտագործվել առանցքային աղյուսակներ ստեղծելիս՝ ընտրելով ներդիրում Տեղադրել – առանցքային աղյուսակ (Տեղադրել – առանցքային աղյուսակ) և մուտքագրելով խելացի աղյուսակի անունը որպես տվյալների աղբյուր.

Եթե ​​ընտրեք նման աղյուսակի մի հատված (օրինակ՝ առաջին երկու սյունակները) և ստեղծեք ցանկացած տեսակի դիագրամ, ապա նոր տողեր ավելացնելիս դրանք ավտոմատ կերպով կավելացվեն գծապատկերում։

Բացվող ցուցակներ ստեղծելիս խելացի աղյուսակի տարրերի ուղիղ հղումները չեն կարող օգտագործվել, բայց դուք կարող եք հեշտությամբ շրջանցել այս սահմանափակումը՝ օգտագործելով մարտավարական հնարք. օգտագործել գործառույթը: Անկախ (Անուղղակի), որը տեքստը վերածում է հղման.

Նրանք. խելացի աղյուսակի հղումը տեքստային տողի տեսքով (չակերտների մեջ) վերածվում է լիարժեք հղման, և բացվող ցուցակը սովորաբար ընկալում է այն:

Մեթոդ 2. Դինամիկ անունով տիրույթ

Եթե ​​ձեր տվյալները խելացի աղյուսակի վերածելը ինչ-ինչ պատճառներով անցանկալի է, ապա կարող եք օգտագործել մի փոքր ավելի բարդ, բայց շատ ավելի նուրբ և բազմակողմանի մեթոդ՝ Excel-ում ստեղծել դինամիկ անունով տիրույթ, որը վերաբերում է մեր աղյուսակին: Այնուհետև, ինչպես խելացի աղյուսակի դեպքում, կարող եք ազատորեն օգտագործել ստեղծված տիրույթի անվանումը ցանկացած բանաձևերում, հաշվետվություններում, գծապատկերներում և այլն: Սկսենք պարզ օրինակից.

ԽնդիրԿազմեք դինամիկ անունով տիրույթ, որը կվերաբերի քաղաքների ցանկին և ավտոմատ կերպով ձգվում և փոքրանում է նոր քաղաքներ ավելացնելիս կամ դրանք ջնջելիս:

Մեզ պետք կգան երկու ներկառուցված Excel ֆունկցիաներ, որոնք հասանելի են ցանկացած տարբերակով − POICPOZ (ՄԱՏՉ) տիրույթի վերջին բջիջը որոշելու համար և INDEX (INDEX) դինամիկ հղում ստեղծելու համար:

Գտեք վերջին բջիջը 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-ը իրականում համեմատում է նիշերի կոդերը որոնելիս, մեր աղյուսակի ցանկացած տեքստ տեխնիկապես «փոքր» կլինի, քան նման երկար «yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy» տողը.

Ստեղծեք հղում՝ օգտագործելով 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:INDEX($A$2:$A$100; MATCH (REP («I»; 255);A2:A100))

Ստեղծեք անվանված տիրույթ

Մնում է այդ ամենը փաթեթավորել մեկ ամբողջության մեջ: Բացեք ներդիր ֆորմուլա (բանաձևեր) Եւ սեղմեք այն Անունը կառավարիչ (Անուն կառավարիչ). Բացվող պատուհանում սեղմեք կոճակը Ստեղծել (նոր), դաշտում մուտքագրեք մեր տիրույթի անվանումը և բանաձևը Շարք (Տեղեկանք):

Մնում է սեղմել OK և պատրաստի տիրույթը կարող է օգտագործվել ցանկացած բանաձևերում, բացվող ցուցակներում կամ գծապատկերներում:

• Օգտագործելով VLOOKUP ֆունկցիան՝ աղյուսակները և որոնման արժեքները կապելու համար
• Ինչպես ստեղծել ավտոմատ համալրվող բացվող ցուցակ
• Ինչպես ստեղծել առանցքային աղյուսակ՝ մեծ քանակությամբ տվյալներ վերլուծելու համար