Դասավորել ըստ բանաձևի

Եթե ​​Ձեզ անհրաժեշտ է տեսակավորել ցուցակը, ապա ձեր ծառայության մեջ կան բազմաթիվ եղանակներ, որոնցից ամենահեշտը ներդիրի կամ մենյուի տեսակավորման կոճակներն են: Ամսաթիվ (Տվյալներ — Դասավորել). Այնուամենայնիվ, կան իրավիճակներ, երբ ցուցակի տեսակավորումը պետք է կատարվի ավտոմատ կերպով, այսինքն՝ բանաձեւեր։ Սա կարող է պահանջվել, օրինակ, բացվող ցուցակի համար տվյալներ ստեղծելիս, գծապատկերների համար տվյալներ հաշվարկելիս և այլն: Ինչպե՞ս դասավորել ցուցակը բանաձևով անմիջապես:

Մեթոդ 1. Թվային տվյալներ

Եթե ​​ցանկը պարունակում է միայն թվային տեղեկատվություն, ապա այն կարելի է հեշտությամբ դասավորել՝ օգտագործելով գործառույթները ԱՌԱՆՑ (ՓՈՔՐ) и LINE (ROW):

ֆունկցիա ԱՌԱՆՑ (ՓՈՔՐ) զանգվածից (սյունակ A) դուրս է հանում անընդմեջ ամենափոքր n-րդ տարրը: Նրանք. SMALL(A:A;1)-ը սյունակի ամենափոքր թիվն է, SMALL(A:A;2)-ը երկրորդ ամենափոքր թիվն է և այլն:

ֆունկցիա LINE (ROW) վերադարձնում է նշված բջիջի տողի համարը, այսինքն՝ ROW(A1)=1, ROW(A2)=2 և այլն: Այս դեպքում այն ​​օգտագործվում է պարզապես որպես n=1,2,3 թվերի հաջորդականության գեներատոր… մեր տեսակավորված ցուցակը: Նույն հաջողությամբ հնարավոր եղավ կատարել լրացուցիչ սյունակ, ձեռքով լրացնել այն 1,2,3 թվային հաջորդականությամբ… և հղում անել ROW ֆունկցիայի փոխարեն:

Մեթոդ 2. Տեքստային ցանկ և կանոնավոր բանաձևեր

Եթե ​​ցանկը պարունակում է ոչ թե թվեր, այլ տեքստ, ապա ՓՈՔՐ ֆունկցիան այլևս չի աշխատի, ուստի պետք է գնալ այլ, մի փոքր ավելի երկար ճանապարհով:

Նախ, եկեք ավելացնենք ծառայության սյունակ բանաձևով, որտեղ յուրաքանչյուր անվան սերիական համարը կհաշվարկվի ապագա տեսակավորված ցուցակում` օգտագործելով ֆունկցիան. COUNTIF (COUNTIF):

Անգլերեն տարբերակում կլինի.

=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)

Առաջին տերմինը գործողից փոքր բջիջների թիվը հաշվելու գործառույթ է: Երկրորդը անվտանգության ցանց է, եթե որևէ անուն մեկից ավելի անգամ է հայտնվում: Այդ ժամանակ նրանք չեն ունենա նույն, բայց հաջորդաբար աճող թվերը։

Այժմ ստացված թվերը պետք է դասավորվեն հաջորդաբար աճման կարգով։ Դրա համար կարող եք օգտագործել գործառույթը ԱՌԱՆՑ (ՓՈՔՐ) առաջին ճանապարհից.

Դե, վերջապես, մնում է միայն անունները ցուցակից հանել իրենց թվերով։ Դա անելու համար կարող եք օգտագործել հետևյալ բանաձևը.

ֆունկցիա ԱՎԵԼԻ ԲԱՑՎԱԾ (ՄԱՏՉ) B սյունակում որոնում է ցանկալի սերիական համարը (1, 2, 3 և այլն) և, փաստորեն, վերադարձնում է այն տողի համարը, որտեղ գտնվում է այս համարը: Գործառույթ INDEX (INDEX) Ա սյունակից հանում է այս տողի համարի անունը:

Մեթոդ 3. Զանգվածի բանաձև

Այս մեթոդը, ըստ էության, նույն տեղաբաշխման ալգորիթմն է, ինչ Մեթոդ-2-ում, բայց իրականացվում է զանգվածի բանաձևով։ Բանաձևը պարզեցնելու համար C1:C10 բջիջների տիրույթին տրվեց անվանում Ցուցակ (ընտրեք բջիջները, սեղմեք Ctrl + F3 և կոճակը Ստեղծել):

E1 բջիջում պատճենեք մեր բանաձևը.

=INDEX(Ցուցակ; MATCH(Փոքրիկ(COUNTIF(Ցուցակ; “<"&Ցուցակ); ROW(1:1)); COUNTIF(Ցուցակ; "<"&Ցուցակ); 0))

Կամ անգլերեն տարբերակով.

=INDEX(Ցուցակ, MATCH(ՓՈՔՐ (COUNTIF(Ցուցակ, «<"&Ցուցակ), ROW(1:1)), COUNTIF(Ցուցակ, "<"&Ցուցակ), 0))

և մղել Ctrl + Shift + Մուտքմուտքագրելու այն որպես զանգվածի բանաձև: Այնուհետև ստացված բանաձևը կարող է պատճենվել ցուցակի ամբողջ երկարությամբ:

Եթե ​​ցանկանում եք, որ բանաձևը հաշվի առնի ոչ թե ֆիքսված տիրույթը, այլ կարողանա հարմարվել ցուցակում նոր տարրեր ավելացնելիս, ապա ձեզ հարկավոր է մի փոքր փոխել ռազմավարությունը:

Նախ, ցանկի տիրույթը պետք է դինամիկ կերպով սահմանվի: Դա անելու համար ստեղծելիս պետք է նշել ոչ թե ֆիքսված C3:C10 միջակայք, այլ հատուկ բանաձեւ, որը կվերաբերի բոլոր հասանելի արժեքներին՝ անկախ դրանց քանակից։ Սեղմել Alt + F3 կամ բացեք ներդիրը Բանաձևեր – Անունների կառավարիչ (Բանաձևեր — Անունների կառավարիչ), ստեղծել նոր անուն և դաշտում կապ (Տեղեկանք) մուտքագրեք հետևյալ բանաձևը (ես ենթադրում եմ, որ տեսակավորվող տվյալների տիրույթը սկսվում է C1 բջիջից).

=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)

=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)

Երկրորդը, վերը նշված զանգվածի բանաձևը պետք է ձգվի մի մարժայով` ապագայում մուտքագրվող լրացուցիչ տվյալների ակնկալիքով: Այս դեպքում զանգվածի բանաձևը կսկսի #NUMBER սխալ տալ այն բջիջների վրա, որոնք դեռ լրացված չեն: Այն ընդհատելու համար կարող եք օգտագործել ֆունկցիան ԻՐԱER, որը պետք է ավելացվի «շուրջ» մեր զանգվածի բանաձևը.

=IFERROR (INDEX (Ցուցակ; MATCH (Փոքրիկ (COUNTIF (Ցուցակ; «<"&Ցուցակ); ROW (1:1)); COUNTIF (Ցուցակ; "<"&Ցուցակ); 0));»»)

=IFERROR(NDEX(Ցուցակ, MATCH(SMALL(COUNTIF(Ցուցակ, «<"&Ցուցակ), ROW(1:1)), COUNTIF(Ցանկ, "<"&Ցուցակ), 0));"")

Այն որսում է #NUMBER սխալը և փոխարենը թողարկում է void (դատարկ չակերտներ):

:

• Տեսակավորել միջակայքն ըստ գույնի
• Որոնք են զանգվածի բանաձևերը և ինչու են դրանք անհրաժեշտ
• SORT տեսակավորում և դինամիկ զանգվածներ նոր Office 365-ում