мазмуну
Эгер тизмени иреттөө керек болсо, анда сиздин кызматта көптөгөн жолдор бар, алардын эң оңойсу өтмөктөгү же менюдагы сорттоо баскычтары. маалыматтар (Маалымат — сорттоо). Бирок, тизмени сорттоо автоматтык түрдө, б.а. формулалар менен жүргүзүлүшү керек болгон жагдайлар бар. Бул, мисалы, ылдый түшүүчү тизме үчүн маалыматтарды түзүүдө, диаграммалар үчүн маалыматтарды эсептөөдө ж.
Метод 1. Сандык маалыматтар
Эгерде тизмеде сандык гана маалымат камтылса, анда аны сорттоо функциялардын жардамы менен оңой жасалышы мүмкүн эң кичине (КИЧИ) и LINE (КАП):
милдети эң кичине (КИЧИ) массивден (А тилкеси) катардагы n-кичи элементти чыгарат. Ошол. КИЧИ(A:A;1) - тилкедеги эң кичине сан, КИЧИ (A:A;2) - экинчи эң кичине, ж.б.
милдети LINE (КАП) көрсөтүлгөн уяча үчүн сап номерин кайтарат, башкача айтканда ROW(A1)=1, ROW(A2)=2 ж.б. биздин иреттелген тизмебиз. Ушундай эле ийгилик менен кошумча тилке жасап, аны кол менен 1,2,3... сандык ырааттуулук менен толтуруп, ROW функциясынын ордуна ага кайрылууга мүмкүн болду.
Метод 2. Текст тизмеси жана кадимки формулалар
Эгерде тизмеде сандар эмес, текст болсо, анда SMALL функциясы иштебей калат, андыктан башка, бир аз узунураак жол менен барышыңыз керек.
Биринчиден, келгиле, келечектеги сорттолгон тизмедеги ар бир аттын сериялык номери функциянын жардамы менен эсептеле турган формуласы бар кызмат тилкесин кошолу COUNTIF (COUNTIF):
Англисче версиясында мындай болот:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Биринчи термин учурдагыдан азыраак клеткалардын санын эсептөө функциясы. Экинчиси, кандайдыр бир аталыш бир нече жолу кайталанган учурда коопсуздук тору. Ошондо алардын саны бирдей эмес, ырааттуу түрдө көбөйөт.
Эми алынган сандар ырааттуу түрдө өсүү тартибинде жайгаштырылышы керек. Бул үчүн сиз функцияны колдоно аласыз эң кичине (КИЧИ) биринчи жол менен:
Акыр-аягы, тизмеден аты-жөнүн сандары боюнча алып салуу гана калды. Бул үчүн, сиз төмөнкү формуланы колдоно аласыз:
милдети КӨБҮРӨГҮ АЧЫРЫЛГАН (МАТЧ) В тилкесинде керектүү сериялык номерди (1, 2, 3 ж.б.) издейт жана чындыгында бул номер жайгашкан саптын номерин кайтарат. Функция INDEX (ИНДЕКС) А тилкесинде ушул саптын номериндеги атын чыгарат.
Метод 3: Массив формуласы
Бул ыкма, чынында, Метод-2дегидей эле жайгаштыруу алгоритми, бирок массив формуласы менен ишке ашырылат. Формуланы жөнөкөйлөтүү үчүн C1:C10 уячаларынын диапазону аталды тизме (уячаларды тандап, басыңыз Ctrl + F3 жана баскыч түзүү):
E1 уячасына биздин формуланы көчүрүңүз:
=INDEX(Тизме; МАТЧ(КИЧИ(COUNTIF(Тизме; “<"&Тизме; ROW(1:1)); COUNTIF(Тизме; "<"&Тизме; 0))
Же англисче версиясында:
=INDEX(Тизме, МАТЧ(КИЧИК(COUNTIF(Тизме, «<"&Тизме), ROW(1:1)), COUNTIF(Тизме, "<"&Тизме), 0))
жана түртүү Ctrl+Shift+Enterаны массив формуласы катары киргизүү үчүн. Андан кийин алынган формула тизменин бүткүл узундугуна көчүрүлүшү мүмкүн.
Эгерде сиз формула туруктуу диапазонду эмес, тизмеге жаңы элементтерди кошууда тууралоону кааласаңыз, анда стратегияны бир аз өзгөртүүгө туура келет.
Биринчиден, Тизме диапазону динамикалык түрдө коюлушу керек. Бул үчүн, түзүүдө C3:C10 туруктуу диапазонун эмес, алардын санына карабастан, бардык жеткиликтүү маанилерге шилтеме жасай турган атайын формуланы көрсөтүү керек. Click Alt + F3 же өтмөктү ачыңыз Формулалар – ысым менеджери (Формулалар — Аты-жөнү менеджери), жаңы ат түзүү жана талаада байланыш (Шилтеме) төмөнкү формуланы киргизиңиз (сорттолуучу маалыматтардын диапазону C1 уячасынан башталат деп ойлойм):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Экинчиден, жогорудагы массивдин формуласын маржа менен ылдый сунуу керек болот - келечекте киргизилген кошумча маалыматтарды күтүү менен. Бул учурда массивдин формуласы толтура элек уячаларда #САН катасын бере баштайт. Аны кармоо үчүн, функцияны колдонсоңуз болот КАТА, ал биздин массив формулабыздын "айланасына" кошулушу керек:
=КАТА(INDEX(Тизме; МАТЧ(КИЧИК(COUNTIF(Тизме; “<"&Тизме; ROW(1:1)); COUNTIF(Тизме; "<"&Тизме; 0));»»)
=ЭГЕРРОР(NDEX(Тизме, МАТЧА)(КИЧИ(COUNTIF(Тизме, «<"&Тизме), ROW(1:1)), COUNTIF(Тизме, "<"&Тизме), 0));"")
Ал #NUMBER катасын кармап, анын ордуна боштук (бош тырмакчалар) чыгарат.
:
- Түс боюнча диапазонду иреттөө
- Массив формулалары деген эмне жана алар эмне үчүн керек
- Жаңы Office 365те сорттоо жана динамикалык массивдерди СОРТТОРУ