Excelде үлгү алуу керек: 4 жумушчу модага

Excel таблицалар менен иштөөдө, белгилүү бир критерийге же бир нече шартта тандоону жүзөгө ашыруу керек. Программа муну ар кандай бир катар куралдарды колдонуп жасай алат. Ар кандай варианттарды колдонуп, эң сонун тандап алууну билип алалы.

Тандоо

Маалымат үлгүлөрү көрсөтүлгөн шарттарды канааттандырган натыйжалардын жалпы массивинин тандалма жол-жоболорунан турат, андан кийин өзүнчө тизмедеги же булак аралыкта жайгашкан.

1-ыкма: Кеңейтилген Autofilt колдонуңуз

Тандоону тандоонун эң оңой жолу - бул узартылган автордук колдонуу. Муну белгилүү бир мисал боюнча кантип жасоону карап көрөлү.

1. Тандалгыңыз келген маалыматтардын арасында баракта аймакты тандаңыз. Үй бетинде, "Сорттоо жана чыпкалоочу" баскычын чыкылдатыңыз. Ал түзөтүү орнотуулары бөгөттөөсүнө жайгаштырылат. Бул тизмеден кийин ачылган тизмеде "Чыпка" баскычын чыкылдатыңыз.

   

   Башкача айтканда, башкача мүмкүнчүлүк бар. Бул үчүн, баракта аймакты тандап алгандан кийин, биз "Маалымат" өтмөгүнө өтөбүз. Сорттоо жана чыпкалоочу топтун тасмасына жайгаштырылган "чыпка" баскычын чыкылдатыңыз.



2. Таблицанын башчысындагы бул иш-аракет кийин, пиктограммалар кичинекей үч бурчтуктардын учтары түрүндө клеткалардын оң четине чыпкалай баштайт окшойт. Бул колоннанын аталышында бул колоннанын аталышында чыкылдатыңыз, ага ылайык, үлгү алууну каалайбыз. Меню иштетүү менюсу, "тексттик чыпкалар" пунктун өтүңүз. Андан кийин, "Ыңгайлаштырылган чыпкасы ..." позициясын тандаңыз.



3. Колдонуучу чыпкалоо терезеси жандырылды. Анда сиз тандоонун чеги түзүлөрүн белгилей аласыз. Мисалы, биз колдонгон сандык форматтагы клетканын бир тилкесинин ачылышы үчүн, сиз беш шарттардын беш түрүнүн бирин тандай аласыз:
   • барабар;
   • барабар эмес;
   • көбүрөөк;
   • көбүрөөк же барабар;
   • кичине.

   Келгиле, кирешени гана тандап алуу үчүн, ал эми кирешенин көлөмү 10000 рубльдан ашкан маанилерди тандап алалы. "Дагы" позицияга которулбайбыз. Туура талаада "10,000" маанисине туура келет. Аракетти аткаруу үчүн "OK" баскычын чыкылдатыңыз.



4. Көрүнүп тургандай, чыпкалоодон кийин, бир гана линиялар гана калган, ал эми кирешенин көлөмү 10000 рубльдан ашып кеткен.



5. Бирок ошол эле тилкеде биз экинчи абалды кошо алабыз. Бул үчүн, бажы чыпкасы терезеге кайтып келиңиз. Көрүнүп тургандай, төмөнкү бөлүгүндө, башка которгуч абалы жана киргизүү үчүн тиешелүү талаа бар. Эми 15000 рубль жогорку селекциялык чек арасын орнотуп көрөлү. Бул үчүн, "Аз" позициясына өтүү үчүн, "15000" маанисине туура келген талаада өтүңүз.

   Мындан тышкары, дагы эле шарттар дагы деле бар. Анын эки жоболору "жана" жана "же". Демейки боюнча, ал биринчи абалда орнотулган. Демек, чектөөлөр гана чектөөлөрдү канааттандырган үлгүдө кала берет дегенди билдирет. Эгер ал "же" позициясына коюлса, анда эки шартта ылайыктуу маанилер сакталат. Биздин учурда, сиз "жана" кызмат ордуна которулушу керек, башкача айтканда, ушул демейки жөндөөнү калтырыңыз. Бардык баалуулуктар киргизилгенден кийин, OK баскычын чыкылдатыңыз.



6. Эми бир гана сызыктар гана столдо калып калган, ал эми кирешенин көлөмү 10000 рублден кем эмес, бирок 15000 рублден ашпайт.



7. Анын сыңарындай, сиз чыпкаларды башка мамыларда конфигурациялай аласыз. Чыпканын чыпкалоосун жана колоннага коюлган мурунку шарттарда сактоого болот. Ошентип, тандоо форматында чыпканы колдонуп, тандоо кандайча кабыл алынгандыгын көрөлү. Тиешелүү тилкедеги чыпкаларды сөлөкөтүн чыкылдатыңыз. "Дата боюнча" тизме боюнча чыпкасы боюнча чыпкалоо баскычын чыкылдатып, ырааттуу.



8. Ыңгайлаштырылган Autofilt Run терезеси дагы башталат. Таблицада 4-майдан 6-майга чейин таблицада жыйынтыктарды тандоону жүзөгө ашырат. Селекциялык которуштуруңузда, сиз сандык форматка караганда, андан да көп параметрлерге көбүрөөк варианттарга ээ. "Кийин же барабар" позицияны тандаңыз. Оң жактагы талаада "04.05.2016" маанисин коюңуз. Төмөнкү блоктун ичинде биз "өйдө" позицияга "чейин" позициясына өтүүнү орнотдук. Туура талаада "06.05.2016" маанисин киргизиңиз. Шайкештикти шайкештиги демейки абалда калтырыңыз - "жана". Чыпкалоону иш жүзүндө колдонуу үчүн, "OK" баскычын басыңыз.



9. Көрүнүп тургандай, биздин тизмеден башка эч нерседен баш тартты. Эми ал боюнча гана линиялар гана калды, ал эми кирешенин көлөмү 04.05 дан 06.05.2016-жылга чейинки мезгил ичинде 10000ден 15000 рубль өзгөрүлүп турат.



10. Биз киришмелердин биринде чыпкаларды баштапкы абалга келтире алабыз. Келгиле, киреше баалуулуктары үчүн жасайлы. Тиешелүү тилкедеги AutoFilter сүрөтчөсүн чыкылдатыңыз. Ачылуучу тизмеде, "Чыпка" статьясын чыкылдатыңыз.



11. Көрүнүп тургандай, ушул иш-аракеттерден кийин, кирешелердин суммасында үлгү иштен чыгарылат, бирок күн боюнча тандоону гана алат (06.05.20056-жылдын 6-сентябрына чейин).



12. Бул таблицада дагы бир тилкеде - "аты" бар. Анда Текст форматында маалыматтарды камтыйт. Ушул баалуулуктар аркылуу чыпкалоону колдонуп, үлгү алууну кантип түзөрүн карап көрөлү.

    Колонна атындагы чыпкан сөлөкөтүн чыкылдатыңыз. Ырааттуу түрдө "тексттик чыпкалар" тизмеге барып, "Ыңгайлаштырылган чыпкасы ..." тизмесинин аталышына барасыз.



13. Колдонуучу AutoFilter терезеси дагы ачылат. Келгиле, "Картошканын" жана "Эт" аттары боюнча үлгү жасайлы. Биринчи блокто, шарттарды күйгүзүү "барабар" абалына орнотулган. Талаада ал "картошка" деген сөзгө туура келет. Төмөнкү бирдикти киргизүү "бирдей" позицияны да коёт. Карама-каршы талаада, мен "Эт" жазам. Эми биз буга чейин жасаган иш-аракеттерин аткарып жатабыз: шайкештикти "же" же "абалга коюңуз. Азыр көрсөтүлгөн шарттарды камтыган сызык экранда көрсөтүлөт. "OK" баскычын чыкылдатыңыз.



14. Көрүнүп тургандай, жаңы үлгүдөгү датада чектөөлөр бар (04.05.2016 чейин 06.05.2016 чейин) жана аты (картошка жана эт) боюнча. Кирешенин көлөмүнө эч кандай чектөөлөр жок.



15. Сиз аны орнотуу үчүн колдонулган бир эле ыкмалар менен чыпканы толугу менен алып салсаңыз болот. Жана кандай ыкма колдонулгандыгын эч кандай мааниге ээ эмес. Чыпкалоону баштапкы абалга келтирүү үчүн, "Маалымат" өтмөгүндө "иреттөө" тобунда жайгашкан "чыпка" баскычын чыкылдатыңыз.

    

    Экинчи вариант "үй" өтмөгүнө өтүүнү камтыйт. Түзөтүү бөлүмүндөгү "Сорттоо жана чыпкалоочу" баскычын басуу баскычын чыкылдатыңыз. Жандандырылган тизмеде "чыпка" баскычын чыкылдатыңыз.

Жогоруда көрсөтүлгөн эки ыкманын кайсынысын болбосун, чыпкалоо жок кылынат, ал эми үлгүлүү натыйжалар тазаланат. Башкача айтканда, таблицада анын маалыматтарынын бардыгын толугу менен көрсөтүлөт.

Сабак: Excelдеги авто фильтр функциясы

2-ыкма: Массивдин формуласын колдонуу

Тандоо массивдин татаал формуласын да колдонсо болот. Мурунку версиясынан айырмаланып, бул ыкма натыйжаны өзүнчө столго алып келет.

1. Ошол эле баракта биз булак катары аталышта бир эле аталыштар бар бош столду түзөбүз.



2. Жаңы таблицанын биринчи тилкесинин бардык бош клеткаларын бөлүңүз. Курсорду формуланын сапына орнотуңуз. Бул жерде көрсөтүлгөн критерийлер боюнча үлгү чыгарган формула киргизилет. 15000 рубльдан ашып кеткен кирешени, кирешенин көлөмүн тандап алабыз. Өзгөчө үлгүбүздө, киргизилген формула төмөнкүлөрдү карайт:

   = Index (A2: A29; эң кичине (if)

   Албетте, ар бир өзгөчө учурда, клеткалардын дареги жана диапазонун дареги сиздики болот. Бул мисалда сиз формуланы координаттар менен салыштырып, аны сиздин муктаждыгыңыз үчүн ылайыкташтыра аласыз.



3. Бул массив формуласы, аны иш-аракет менен колдонуу үчүн, сиз Enter баскычын басуу керек, бирок Ctrl + Shift + ачкыч айкалышын киргизиңиз. Биз муну жасайбыз.



4. Экинчи тилкени баса белгилөө жана формуланын курсорун орнотуп, формуланын формуласында курсорду орнотуп, биз төмөнкүдөй билдирүүнү киргизебиз:

   = Индекси (b2: b29; кичине (if)

   Ctrl + Shift + Enter айкалышын чыкылдатыңыз.



5. Ошо сыяктуу эле, киреше менен бир тилкеде төмөнкү мазмундагы формуланы киргизиңиз:

   = Индекс (C2: C29; эң кичине (if)

   Дагы бир жолу, Ctrl + Shift + Enter айкалышын териңиз.

   Үч учурларда координаттардын биринчи наркы гана өзгөрүп жатат, ал эми калыпталган калыс бирдей.



6. Жогоруда айтылгандай, таблица маалыматка толот, бирок анын көрүнүшү толугу менен жагымдуу эмес, андан тышкары, даттын баалуулуктары туура эмес толтурулат. Бул кемчиликтерди оңдоо керек. Күндүн туура эместиги тиешелүү колоннанын клеткаларынын форматы кеңири таралганына байланыштуу, биз күн форматын орнотушубуз керек. Биз бүт тилкени, анын ичинде каталар бар клеткаларын баса белгилеп, Чычкан баскычын баса белгилөө баскычын чыкылдатыңыз. Тизмеде пайда болгон тизмеде "клетканын форматын өзгөртүү ...".



7. Форматтоо терезесинде ачылган, "Номдун" өтмөгүн ачыңыз. "Сандык форматтар" блогу "Күн" маанисин бөлүп берүү. Терезенин оң жагында сиз каалаган күн дисплей түрүн тандай аласыз. Жөндөөлөр көргөзмөдөн кийин, "OK" баскычын чыкылдатыңыз.



8. Азыр күн туура көрсөтүлөт. Бирок, биз көргөндөй, столдун түпкү бөлүгү жаңылыш маанисин камтыган клеткаларга толтурулат. Мааниси, бул клеткалар, ал үчүн, алар жетишсиз болгон тандалмадан алынган маалыматтар. Эгер алар бош болсо, анда ал бош болсо, ал дагы жагымдуу болмок. Ушул максаттар үчүн биз шарттуу форматтоону колдонобуз. Баш атыдан башка бардык үстөлдөрдү баса белгилейбиз. Үйдө жүргөндө, "Styles" курал-жарак блогунда жайгашкан "Шарттуу форматтоо" баскычын чыкылдатыңыз. Кыязы, тизмеде "Эреже түзүү ..." деген нерсе тандаңыз.



9. Терезеде ачылган, эреже түрүндөгү түрүн тандаңыз "Колдонулган клеткалар гана". Биринчи талаада, "Төмөнкү абалда" клеткаларды форматтоо менен гана форматтоо "деген жазууда" Ката "позициясын тандаңыз. Андан кийин, "Форматты ..." баскычын чыкылдатыңыз.



10. Форматтоо терезесинде иштеп жатат, "арип" өтмөгүнө өтүңүз жана тиешелүү талаага Ак тандаңыз. Бул аракеттерден кийин, "OK" баскычын чыкылдатыңыз.



11. Ошол эле аталышта көрсөтүлгөн баскычта, түзүүчү терезеге кайтып келгенден кийин чыкылдатыңыз.

Азыр бизде өткөн чекте өзүнчө жасалгаланган столдо бизде үзгүлтүксүз үлгү бар.

Сабак: Шарттуу форматтоо Excel

3-ыкма: Формуланы колдонуу менен бир нече шарттарда тандоо

Формуланы колдонуп, формуланы колдонуп, сиз бир нече шартта тандай аласыз. Мисалы, бир эле булак таблицаны, ошондой эле бош столду алыңыз, анда натыйжа натыйжа жүргүзүлөт, анда буга чейин сандык жана шарттуу форматтоо менен жүргүзүлөт. Тандоонун төмөнкү чек арасын киреше менен киреше менен 15000 рубльда, ал эми 20000 рубльдун үстүнкү чек арасынын экинчи абалы.

1. Тандоо үчүн өзүнчө тилке, чек ара шарттарына кириңиз.



2. Мурунку ыкмадагыдай эле, жаңы таблицанын боштуктарын бөлүп берүү жана аларга тиешелүү үч формуланы киргизиңиз. Биринчи тилкеде биз төмөнкүдөй көрүнүш тааныштырабыз:

   = Индекси (A2: A29; эң кичине (эгер); Line (C2: C29); "); сап (C2: C29) -стркок ($ C $ 1)) ($ C $ 1))

   Кийинки тилкеде, бир эле формулаларды, ошондой эле бирдей формулаларды, ошондой эле оператордун аталышынан кийинки координаттарды оператордун аталышынан кийин, индекси мурунку ыкма менен байланышуу менен, ага муктаж болгон тиешелүү графалардын индекси.

   Киргенден кийин ар бир жолу Ctrl + Shift + Enter айкалышын ээлей тургандыгын унутпаңыз.



3. Мурунку ыкманын артыкчылыгы мурункуга чейин артыкчылык, эгер биз тандалма чек араларын өзгөрткүңүз келсе, анда ал катуу формуланы өз-өзүнчө өзгөртүү керек, ал өзүнөн өзү көйгөйлүү. Колдонуучуга керектүү болгондордун чек ара номерлерин өзгөртүү үчүн баракта шарт барагынын графасында жетиштүү. Тандоонун натыйжалары ошол замат автоматтык түрдө өзгөрөт.

4-ыкма: Кокус тандоо

Сүргүндө атайын формуланын жардамы менен, кокустук тандоону колдонууга болот. Айрым учурларда массивдин бардык маалыматтарын ар тараптуу талдабастан, жалпы көлөмдө көп өлчөмдө иштеп жатканда, бир нече учурларда талап кылынат.

1. Дасторкон сол жагына бир тилкеде өткөрүп жиберди. Столдун маалыматтары менен биринчи клетканын карама-каршы жайгашкан кийинки графанын клеткасында формуланы киргизиңиз:

   = Чаптама ()

   Бул функция кокустук номерди көрсөтөт. Аны жандыруу үчүн, Enter баскычын чыкылдатыңыз.



2. Кокус сандардын бүт тилкесин түзүп, курсорду формула камтылган клетканын төмөнкү оң бурчуна коюңуз. Толтуруучу маркер пайда болот. Мен аны солго чычкан баскычы менен бөлүп-жарып, дасторконго параллелдүү маалыматка чейин белгиленем.



3. Азыр бизде кокустук сандарга толгон бир катар клеткалар бар. Бирок, ал CALLдин формуласы камтылган. Биз таза баалуулуктар менен иштешибиз керек. Муну жасоо үчүн, оң жагындагы бош колонка көчүрүңүз. Кокустук номерлери бар клеткалардын спектрин тандаңыз. "Home" өтмөгүндө жайгашкан, лентадагы "Көчүрүү" сөлөкөтүн чыкылдатыңыз.



4. Биз бош колоннаны баса белгилеп, контексттик меню деп аталган оң баскычты чыкылдатыңыз. "Параметрлерди" куралдар панелинде, сандар менен сүрөткө түшкөн "Мааниси" пунктун тандаңыз.



5. Андан кийин, "үй" өтмөгүндө, "иреттөө жана чыпкалоочу" сөлөкөтүнүн сүрөтчөсүнүн мурунтансыз тааныш сүрөтчөсүн чыкылдатыңыз. Ачылуучу тизмеде, "Ыңгайлаштырылган сорттоо" статьясында тандоону токтотуңуз.



6. Сорттоо жөндөөлөрү терезе жандырылды. Параметрдин карама-каршылыктуу белгини орнотуп коюңуз "Менин маалыматтарым бар" Эгерде капкак бар болсо, анда кене жок. "Сорттоо" талаасында, кокустук сандардын көчүрүлгөн маанилери камтылган ошол тилкеңизди көрсөтүңүз. "Сорттоо" талаасында демейки жөндөөлөрдү калтырыңыз. "Заказ" талаасында сиз параметрди "көтөрүлүп" жана "түшүп" деп тандай аласыз. Кокус үлгү үчүн бул мааниге ээ эмес. Жөндөөлөр өндүрүлгөндөн кийин, "OK" баскычын чыкылдатыңыз.



7. Андан кийин, таблицанын бардык баалуулуктары жогорулап, кокустук сандарды азайтуу үчүн курулган. Сиз таблицадан биринчи саптардын бардыгын (5, 10, 12, 15 ж.б.) жана алар кокусунун натыйжасы деп эсептей аласыз.

Сабак: Маалыматтарды Excel үчүн сорттоо жана чыпкалоо

Көрүнүп тургандай, Excel таблицасындагы үлгү AutoFilter колдонуп, атайын формулаларды колдонуу менен өндүрүлүшү мүмкүн. Биринчи учурда, булак таблицада жана экинчисинде - өзүнчө аймакта көрсөтүлөт. Тандоо, бир шартка жана бир нече нерсени жаратууга болот. Мындан тышкары, сиз жабышчаак функциясын колдонуп, туш келди үлгү жасай аласыз.