Мазмұны
Мәселені тұжырымдау
Енгізілген деректер ретінде бізде Excel файлы бар, онда парақтардың бірінде келесі пішіндегі сату деректері бар бірнеше кестелер бар:
Ескертіп қой:
- Ешбір сұрыптаусыз жолдар мен бағандардағы әртүрлі өлшемдегі және өнімдер мен аймақтардың әртүрлі жиынтықтары бар кестелер.
- Кестелер арасына бос жолдарды қоюға болады.
- Кестелер саны кез келген болуы мүмкін.
Екі маңызды болжам. Болжам бойынша:
- Әрбір кестенің үстінде, бірінші бағанда кестеде сатылымдары көрсетілген менеджердің аты-жөні (Иванов, Петров, Сидоров және т.б.) көрсетілген.
- Барлық кестелердегі тауарлар мен аймақтардың атаулары бірдей – регистр дәлдігімен жазылған.
Түпкілікті мақсат - барлық кестелерден деректерді кейіннен талдау және қорытынды жасау үшін ыңғайлы бір тегіс нормаланған кестеге жинау, яғни мынада:
1-қадам. Файлға қосылыңыз
Жаңа бос Excel файлын жасап, оны қойындыда таңдайық мәліметтер бұйрық Деректерді алу – Файлдан – Кітаптан (Деректер — Файлдан — Жұмыс кітабынан). Сату деректерімен бастапқы файлдың орнын көрсетіңіз, содан кейін навигатор терезесінде бізге қажет парақты таңдап, түймені басыңыз Деректерді түрлендіру (Деректерді түрлендіру):
Нәтижесінде ондағы барлық деректер Power Query өңдегішіне жүктелуі керек:
2-қадам. Қоқысты тазалаңыз
Автоматты түрде жасалған қадамдарды жою өзгертілген түрі (Өзгертілген түрі) и Көтерілген тақырыптар (Жоғартылған тақырыптар) және сүзгіні пайдаланып бос жолдар мен қорытындылары бар жолдардан құтылыңыз NULL и БАРЛЫҒЫ бірінші баған бойынша. Нәтижесінде біз келесі суретті аламыз:
3-қадам. Басқарушыларды қосу
Кімнің сатылымы қайда екенін кейінірек түсіну үшін кестеге бағанды қосу керек, онда әр жолда сәйкес фамилия болады. Бұл үшін:
1. Команда арқылы жол нөмірлері бар көмекші бағанды қосамыз Баған қосу – Индекс бағанасы – 0-ден (Бағанды қосу — Индекс бағаны — 0 бастап).
2. Пәрмені бар формуласы бар бағанды қосыңыз Баған қосу – теңшелетін баған (Баған қосу — реттелетін баған) және онда келесі құрылысты енгізіңіз:
Бұл формуланың логикасы қарапайым – егер бірінші бағандағы келесі ұяшықтың мәні «Өнім» болса, бұл біз жаңа кестенің басында сүрінгенімізді білдіреді, сондықтан біз алдыңғы ұяшықтың мәнін келесідей көрсетеміз. менеджердің аты-жөні. Әйтпесе, біз ештеңе көрсетпейміз, яғни null.
Тегі бар негізгі ұяшықты алу үшін алдымен алдыңғы қадамдағы кестеге жүгінеміз #"Индекс қосылды", содан кейін бізге қажет бағанның атын көрсетіңіз [1-баған] шаршы жақшада және сол бағандағы ұяшық нөмірін бұйра жақшада. Ұяшық нөмірі бағаннан алатын ағымдағыдан бір кем болады көрсеткіш, тиісінше.
3. Бос ұяшықтарды толтыру қалады NULL пәрменімен жоғары ұяшықтардан атаулар Трансформация – Толтыру – Төмен (Түрлендіру — толтыру — төмен) және индекстері бар қажет емес бағанды және бірінші бағандағы тегі бар жолдарды жойыңыз. Нәтижесінде біз аламыз:
Қадам 4. Менеджерлер бойынша бөлек кестелерге топтастыру
Келесі қадам - әрбір менеджерге арналған жолдарды бөлек кестелерге топтау. Ол үшін «Трансформация» қойындысында «Топтау» пәрменін (Transform – Group By) пайдаланыңыз және ашылған терезеде «Басқару» бағанын және «Барлық жолдар» (Барлық жолдар) операциясын таңдап, ешқандай жинақтау функциясын қолданбай жай ғана деректерді жинаңыз. олар (сома, орташа және т.б.). P.):
Нәтижесінде біз әр менеджер үшін жеке кестелерді аламыз:
5-қадам: Кірістірілген кестелерді түрлендіру
Енді алынған бағанның әрбір ұяшығында жатқан кестелерді береміз Барлық деректер лайықты пішінде.
Алдымен әр кестеде қажет емес бағанды жойыңыз басқарушы. Біз қайтадан қолданамыз Реттелетін баған қойындысы трансформация (Түрлендіру — теңшелетін баған) және келесі формула:
Содан кейін басқа есептелген бағанмен біз әр кестедегі бірінші жолды тақырыптарға көтереміз:
Соңында біз негізгі түрлендіруді орындаймыз – M-функциясы арқылы әрбір кестені ашамыз Кесте.Басқа бағандарды шығару:
Тақырыптағы аймақтардың атаулары жаңа бағанға өтеді және біз тар, бірақ сонымен бірге ұзағырақ қалыпқа келтірілген кестені аламыз. бар бос ұяшықтар NULL ескерілмейді.
Қажет емес аралық бағандардан құтылу үшін бізде:
6-қадам Кірістірілген кестелерді кеңейтіңіз
Барлық қалыпты кірістірілген кестелерді баған тақырыбындағы қос көрсеткілері бар түймені пайдаланып бір тізімге кеңейту қалады:
... және біз ақырында қалағанымызға қол жеткіздік:
Алынған кестені пәрмен арқылы Excel бағдарламасына қайта экспорттауға болады Негізгі бет — Жабу және жүктеу — Жабу және жүктеу… (Басты бет — Жабу&Жүктеу — Жабу&Жүктеу...).
- Бірнеше кітаптардан әртүрлі тақырыптары бар кестелерді құрастырыңыз
- Берілген қалтадағы барлық файлдардан деректерді жинау
- Кітаптың барлық парақтарынан мәліметтерді бір кестеге жинау