Тізімді сұрыптау қажет болса, сіздің қызметіңізде көптеген жолдар бар, олардың ең оңайсы қойындыдағы немесе мәзірдегі сұрыптау түймелері. мәліметтер (Деректер - сұрыптау). Дегенмен, тізімді сұрыптауды автоматты түрде, яғни формулаларды жасау қажет болатын жағдайлар бар. Бұл, мысалы, ашылмалы тізім үшін деректерді жасау кезінде, диаграммалар үшін деректерді есептеу кезінде және т.б. қажет болуы мүмкін. Тізімді жылдам формуламен қалай сұрыптауға болады?
1-әдіс. Сандық деректер
Тізімде тек сандық ақпарат болса, оны сұрыптау функциялардың көмегімен оңай орындалуы мүмкін Ең аз (КІШІ) и ТҮЗУ (ҚАТАР):
функция Ең аз (КІШІ) массивтен (А бағанынан) қатардағы n-ші ең кіші элементті шығарады. Анау. КІШІ(A:A;1) - бағандағы ең кіші сан, КІШІ (A:A;2) - екінші ең кіші сан және т.б.
функция ТҮЗУ (ҚАТАР) көрсетілген ұяшық үшін жол нөмірін қайтарады, яғни ROW(A1)=1, ROW(A2)=2 т.б. Бұл жағдайда ол жай ғана n=1,2,3… үшін сандар тізбегінің генераторы ретінде пайдаланылады сұрыпталған тізіміміз. Дәл осындай жетістікпен қосымша баған жасауға, оны 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 ұяшығына формуламызды көшіріңіз:
=ИНДЕКС(Тізім; СӘЙКЕСТІК(КІШІ(ЕРЕЖЕ(Тізім; “<"&Тізім); ҚОЛ(1:1)); COUNTIF(Тізім; "<"&Тізім; 0))
Немесе ағылшын тіліндегі нұсқасында:
=INDEX(Тізім, СӘЙКЕСТІК(КІШІ(COUNTIF(Тізім, «<"&Тізім), ҚОЛ(1:1)), COUNTIF(Тізім, "<"&Тізім), 0))
және итеріңіз Ctrl + Shift + Enter пернелер тіркесіміоны массив формуласы ретінде енгізу үшін. Содан кейін алынған формуланы тізімнің бүкіл ұзындығына көшіруге болады.
Формула бекітілген ауқымды емес, тізімге жаңа элементтерді қосқанда реттей алатынын қаласаңыз, стратегияны сәл өзгертуіңіз керек.
Біріншіден, Тізім ауқымы динамикалық түрде орнатылуы керек. Ол үшін құру кезінде тіркелген C3:C10 ауқымын емес, олардың санына қарамастан барлық қол жетімді мәндерге сілтеме жасайтын арнайы формуланы көрсету керек. басыңыз 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(Тізім, СӘЙКЕСТІК(КІШКЕН(ЕҢЕК(Тізім, «<"&Тізім), ЖОЛ(1:1)), COUNTIF(Тізім, "<"&Тізім), 0));"")
Ол #NUMBER қатесін алады және оның орнына бос мәнді (бос тырнақшалар) шығарады.
:
- Түс бойынша ауқымды сұрыптау
- Массив формулалары дегеніміз не және олар не үшін қажет
- Жаңа Office 365 жүйесінде сұрыптау және динамикалық массивтерді СҰРЫПТАңыз