Пивот таблицалары бардыгы үчүн жакшы – алар тез эсептешет жана ийкемдүү конфигурацияланат жана эгер талап кылынса, дизайн аларга жарашыктуу түрдө орнотулат. Бирок, ошондой эле майдын бир нече чымын бар, атап айтканда, наркы аянты сандарды камтышы керек эмес, кыскача түзүү мүмкүн эмес, бирок текст.
Келгиле, бул чектөөнү айланып өтүүгө аракет кылалы жана ушул сыяктуу жагдайда "эки балдак" ойлоп көрөлү.
Биздин компания өз продукциясын контейнерлер менен өлкөбүздүн жана Казакстандын бир нече шаарларына ташыйт дейли. Контейнерлер айына бир жолудан ашык эмес жөнөтүлөт. Ар бир контейнердин алфавиттик номери бар. Баштапкы маалымат катары, жеткирүүлөрдүн стандарттуу тизмеси бар, андан ар бир шаарга жана ай сайын жөнөтүлгөн контейнерлердин санын так көрүү үчүн кандайдыр бир жыйынтык чыгаруу керек:
Ыңгайлуу болуу үчүн, келгиле, команданы колдонуп, баштапкы маалыматтар менен таблицаны алдын ала “акылдуу” кылып алалы Башкы бет – Таблица катары форматтоо (Башкы бет — Таблица катары формат) жана ага ат бер Берүүлөр табулатура Конструктор (Дизайн). Келечекте бул жашоону жөнөкөйлөтөт, анткени. таблицанын атын жана анын мамычаларын түздөн-түз формулаларда колдонууга мүмкүн болот.
Метод 1. Эң оңойсу – Power Query колдонуңуз
Power Query Excelдеги маалыматтарды жүктөө жана өзгөртүү үчүн абдан күчтүү курал. Бул кошумча 2016-жылдан бери демейки боюнча Excelге орнотулган. Эгер сизде Excel 2010 же 2013 болсо, аны өзүнчө жүктөп алып, орното аласыз (толугу менен акысыз).
Бүт процессти тактоо үчүн, мен төмөнкү видеодо этап-этабы менен талдап чыктым:
Эгер Power Queryди колдонуу мүмкүн болбосо, анда башка жолдор менен өтсөңүз болот - пивот таблицасы же формулалар аркылуу.
Метод 2. Көмөкчү корутунду
Баштапкы таблицага дагы бир мамычаны кошолу, анда жөнөкөй формуланы колдонуп, таблицадагы ар бир саптын санын эсептейбиз:
Албетте, -1 керек, анткени биздин таблицада бир сап баш бар. Эгерде сиздин таблицаңыз барактын башында болбосо, анда сиз бир аз татаалыраак, бирок учурдагы саптын жана таблица башынын сандарындагы айырманы эсептеген универсалдуу формуланы колдоно аласыз:
Эми, стандарттуу түрдө, биз маалыматыбыздын негизинде каалаган түрдөгү пивот таблицасын курабыз, бирок маани талаасында биз талааны таштайбыз Саптын номери биз каалаган нерсенин ордуна контейнер:
Бир айда бир шаарда бир нече контейнер болбогондуктан, биздин корутундубуз чындыгында сумманы эмес, бизге керек болгон контейнерлердин номерлерин берет.
Кошумча, сиз өтмөктөгү чоң жана орто суммаларды өчүрө аласыз Конструктор – Жалпы жыйынтыктар и Аралык суммалар (Дизайн — Чоң суммалар, кошумча суммалар) жана ошол эле жерде кнопка менен кыскача таблицаны ыңгайлуураак макетке которуңуз Кабарлоо макети (Отчёттун макети).
Ошентип, биз жыйынтыктын жарымына жеттик: бизде шаар менен айдын кесилишинде баштапкы таблицада сап номери бар, бизге керектүү контейнер коду жайгашкан таблица бар.
Эми корутундуну көчүрөлү (ошол эле баракка же башкасына) жана аны маанилер катары чаптайлы, андан кийин биздин формуланы маанилер аймагына киргизели, ал контейнердин кодун корутундуда табылган сап номери боюнча чыгарып алат:
милдети IF (ЭГЕР), бул учурда корутундудагы кийинки уячанын бош эмес экенин текшерет. Эгерде бош болсо, анда бош текст сабын "" чыгарыңыз, башкача айтканда, уячаны бош калтырыңыз. Эгерде бош эмес болсо, анда тилкеден чыгарып алыңыз контейнер булак таблицасы Берүүлөр функцияны колдонуу менен сап номери боюнча клетка мазмуну INDEX (ИНДЕКС).
Балким, бул жерде бир гана өтө ачык эмес жагдай кош сөз болуп саналат контейнер формулада. Мындай кызыктай жазуу түрү:
Жабдуулар[[Контейнер]:[Контейнер]]
… тилкеге шилтеме жасоо үчүн гана керек контейнер абсолюттук болгон (кадимки "акылдуу эмес" таблицалар үчүн $ белгилери бар маалымдама сыяктуу) жана биздин формуланы оңго көчүрүп жатканда коңшу мамычаларга жылган эмес.
Келечекте, баштапкы таблицадагы маалыматтарды өзгөртүүдө Берүүлөр, биз жардамчы корутундуну сап номерлери менен жаңыртууну унутпашыбыз керек, аны оң баскыч менен чыкылдатып, буйрукту тандоо Жаңыртуу & Сактоо (Жаңылоо).
Метод 3. Формулалар
Бул ыкма ортодогу пивот таблицасын түзүүнү жана кол менен жаңыртууну талап кылбайт, бирок Excelдин “оор куралын” – функцияны колдонот. SUMMESLIMN (SUMIFS). Кыскача саптардын номерлерин издөөнүн ордуна, сиз аларды бул формула менен эсептей аласыз:
Кээ бир тышкы көлөмдүү, чындыгында, бул тандалма суммалоо функциясы үчүн стандарттуу колдонуу учуру SUMMESLIMNБерилген шаар жана ай үчүн катар номерлерин жыйынтыктоочу A. Дагы бир жолу, бир айда бир шаарда бир нече контейнер жок болгондуктан, биздин функция чындыгында сумманы эмес, саптын номерин берет. Анан мурунку ыкмадан мурунтан эле тааныш болгон функция INDEX Сиз ошондой эле контейнер коддорун чыгара аласыз:
Албетте, бул учурда сиз мындан ары корутундуну жаңылоо жөнүндө ойлонушуңуз керек эмес, бирок чоң таблицаларда функция СУММЕСЛИ байкаларлык жай болушу мүмкүн. Андан кийин сиз формулаларды автоматтык түрдө жаңыртууну өчүрүшүңүз керек же биринчи ыкманы - пивот таблицасын колдонушуңуз керек.
Эгерде корутундунун сырткы көрүнүшү сиздин отчетуңузга анча ылайык келбесе, анда сиз андан сап номерлерин акыркы таблицага биз жасагандай түз эмес, функцияны колдонуп чыгара аласыз. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Муну кантип жасоону бул жерден тапса болот.
- Пивот таблицасын колдонуу менен кантип отчет түзүүгө болот
- Пивот таблицаларында эсептөөлөрдү кантип орнотуу керек
- SUMIFS, COUNTIFS ж.б. менен тандалма эсептөө.