мазмуну
Эгер сиз башталгыч колдонуучу болбосоңуз, анда Excelдеги бардык нерселердин 99% тик таблицалар менен иштөөгө ылайыкташтырылганын, анда параметрлер же атрибуттар (талаалар) мамычалар аркылуу өтүп, объекттер же окуялар жөнүндө маалымат жайгашканын байкасаңыз керек. саптарда. Пивот таблицалары, кошумча жыйынтыктар, эки жолу чыкылдатуу менен формулаларды көчүрүү - бардыгы ушул маалымат форматы үчүн атайын ылайыкталган.
Бирок, бөтөнчө эч кандай эреже жок жана бир кыйла үзгүлтүксүз жыштык менен менден горизонталдык семантикалык ориентациядагы таблица же саптар менен мамычалар мааниси боюнча бирдей салмакка ээ болгон таблица иштесе эмне кылуу керек деп сурашат:
Ал эми Excel дагы эле горизонталдуу түрдө кантип иреттөө керектигин билсе (буйрук менен Маалымат – Сорттоо – Параметрлер – Мамычаларды иреттөө), анда чыпкалоонун абалы начарыраак - Excelде саптарды эмес, мамычаларды чыпкалоо үчүн орнотулган куралдар жок. Ошентип, эгерде сиз ушундай тапшырмага туш болсоңуз, анда ар кандай деңгээлдеги татаалдыкты чечүү жолдорун табууга туура келет.
Метод 1. Жаңы FILTER функциясы
Эгер сиз Excel 2021 жаңы версиясында же Excel 365 жазылуусунда болсоңуз, жаңы киргизилген өзгөчөлүктөн пайдалана аласыз FILTER (ФИЛЬТР), бул баштапкы маалыматтарды саптар боюнча гана эмес, мамычалар боюнча да чыпкалай алат. Иштөө үчүн бул функцияга жардамчы горизонталдуу бир өлчөмдүү массив-сап керек, мында ар бир маани (ЧЫНДЫК же ЖАЛГАН) таблицадагы кийинки тилкени көрсөтүү же жашырышыбызды аныктайт.
Келгиле, таблицабыздын үстүнө төмөнкү сапты кошуп, андагы ар бир тилкенин статусун жазалы:
- Айталы, биз ар дайым биринчи жана акыркы тилкелерди көрсөтүүнү каалайбыз (башаттар жана жалпылар), ошондуктан алар үчүн массивдин биринчи жана акыркы уячаларында = TRUE маанисин койдук.
- Калган тилкелер үчүн тиешелүү уячалардын мазмуну функцияларды колдонуу менен бизге керектүү шарттарды текшерген формула болот. И (ЖАНА) or OR (ЖЕ). Мисалы, жалпысынан 300дөн 500гө чейин.
Андан кийин, бул функцияны колдонуу үчүн гана калат FILTER биздин жардамчы массивибиз TRUE мааниге ээ болгон тилкелерди тандоо үчүн:
Ошо сыяктуу эле, сиз берилген тизме боюнча мамычаларды чыпкалай аласыз. Бул учурда, функция жардам берет COUNTIF (COUNTIF), ал уруксат берилген тизмедеги таблица башындагы кийинки тилке аталышынын кайталануу санын текшерет:
Метод 2. Кадимки таблицанын ордуна пивот таблицасы
Учурда Excelде горизонталдык чыпкалоодо бир гана пивот таблицаларындагы мамычалар боюнча орнотулган, андыктан биз түпнуска таблицабызды пивот таблицага айландыра алсак, анда биз бул камтылган функцияны колдоно алабыз. Бул үчүн, биздин баштапкы таблица төмөнкү шарттарга жооп бериши керек:
- бош жана бириктирилген уячалары жок "туура" бир сап баш сызыгына ээ болуңуз - антпесе пивот таблицасын түзүү иштебейт;
- саптардын жана мамычалардын этикеткаларында дубликаттарды камтыбаңыз – алар корутундуда уникалдуу баалуулуктардын тизмесине “кыйратышат”;
- маанилердин диапазонундагы сандарды гана камтыйт (саптар менен мамычалардын кесилишинде), анткени пивот таблица аларга сөзсүз түрдө кандайдыр бир топтоо функциясын колдонот (сума, орточо ж.б.) жана бул текст менен иштебейт.
Эгерде бул шарттардын бардыгы аткарылса, анда биздин баштапкы таблицага окшош пивот таблицасын куруу үчүн аны (оригиналдуу) кайчылаш таблицадан жалпак таблицага (нормаланган) кеңейтүү керек болот. Ал эми муну жасоонун эң оңой жолу - 2016-жылдан бери Excel программасына орнотулган күчтүү маалыматтарды трансформациялоо куралы болгон Power Query кошумчасы.
Булар:
- Келгиле, таблицаны “акылдуу” динамикалык буйрукка айландыралы Башкы бет – Таблица катары форматтоо (Башкы бет — Таблица катары формат).
- Буйрук менен Power Queryге жүктөлүүдө Берилиштер – Таблицадан / Диапазондон (Маалыматтар – Таблицадан / Диапазондон).
- Биз сапты жыйынтыктар менен чыпкалайбыз (жыйынтыктын өзүнүн жалпы суммасы болот).
- Биринчи тилкенин аталышын оң баскыч менен чыкылдатып, тандаңыз Башка тилкелерди жыйыштыруу (Башка мамычаларды ачуу). Бардык тандалбаган тилкелер экиге айландырылат - кызматкердин аты жана анын көрсөткүчүнүн мааниси.
- Мамычага кирген жыйынтыктар менен тилкени чыпкалоо сыпат.
- Буйрук менен пайда болгон жалпак (нормаланган) таблицага ылайык пивот таблицасын курабыз Башкы бет — Жабуу жана жүктөө — Жабуу жана жүктөө… (Башкы бет — Жабуу жана жүктөө — Жабуу жана жүктөө...).
Эми сиз пивот таблицаларында жеткиликтүү мамычаларды чыпкалоо мүмкүнчүлүгүн колдоно аласыз - аттардын жана элементтердин алдындагы кадимки белгилерди Кол чыпкалар (Энбелги чыпкалары) or Маани боюнча чыпкалоо (Баалуу чыпкалары):
Жана, албетте, маалыматтарды өзгөртүүдө, сиз биздин сурообузду жана кыскача баскычтоптун жарлыгы менен жаңыртышыңыз керек болот Ctrl+Alt+F5 же команда Берилиштер – Баарын жаңыртуу (Маалымат — баарын жаңылоо).
Метод 3. VBAдагы макро
Мурунку бардык ыкмалар, сиз оңой эле көрүп тургандай, так чыпкалоодон өтпөйт - биз оригиналдуу тизмедеги мамычаларды жашырбайбыз, бирок оригиналдан берилген мамычалар топтому менен жаңы таблица түзөбүз. Эгерде баштапкы маалыматтардагы мамычаларды чыпкалоо (жашыруу) талап кылынса, анда принципиалдуу түрдө башкача мамиле керек, тактап айтканда, макро.
Биз столдун башындагы менеджердин аты A4 сары уячасында көрсөтүлгөн маскага жооп берген мамычаларды чыпкалоону каалайбыз дейли, мисалы, "А" тамгасынан башталат (б.а. "Анна" жана "Артурду" алуу " Натыйжада).
Биринчи ыкмадагыдай эле, биз адегенде жардамчы диапазонду ишке ашырабыз, мында ар бир уячада биздин критерий формула менен текшерилет жана көрүнгөн жана жашыруун мамычалар үчүн TRUE же ЖАЛГАН логикалык маанилери көрсөтүлөт:
Анда жөнөкөй макросту кошолу. Барак барагын оң баскыч менен чыкылдатып, буйрукту тандаңыз булак (Булак коду). Төмөнкү VBA кодун көчүрүп, ачылган терезеге чаптаңыз:
Private Sub Worksheet_Change(ByVal Target As Range) Эгерде Target.Address = "$A$4" Анда Ар бир уяча үчүн Ар бир диапазон үчүн("D2:O2") Эгерде уяча = True Анда cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next уяча End If End Sub Анын логикасы төмөнкүдөй:
- Жалпысынан алганда, бул окуяны иштетүүчү Worksheet_Change, башкача айтканда, бул макрос автоматтык түрдө учурдагы барактын каалаган уячасын өзгөртүүдө иштейт.
- Өзгөрүлгөн уячага шилтеме ар дайым өзгөрмөдө болот бута.
- Биринчиден, колдонуучу уячаны (A4) критерийи менен так өзгөрткөнүн текшеребиз - бул оператор тарабынан жасалат if.
- Андан кийин цикл башталат Ар бирине… ар бир тилке үчүн TRUE / FALSE көрсөткүчү менен боз уячаларды (D2: O2) кайталоо.
- Эгерде кийинки боз уячанын мааниси TRUE (чын) болсо, анда мамыча жашырылган эмес, антпесе биз аны жашырабыз (мүлк жашыруун).
- Office 365тен динамикалык массив функциялары: ФИЛЬТР, СОРТ жана UNIC
- Power Query аркылуу көп сап баштары бар пивот таблицасы
- Макрос деген эмне, аларды кантип түзүү жана колдонуу керек