Бірнеше кітаптардан әртүрлі тақырыптары бар кестелерді құрастырыңыз

Мәселені тұжырымдау

Бізде бір қалтада бірнеше файл бар (біздің мысалда – 4 дана, жалпы жағдайда – қалағаныңызша). Есептер:

Ішінде бұл файлдар келесідей көрінеді:

Бұл жерде:

• Бізге қажет деректер парағы әрқашан шақырылады фотосуреттер, бірақ жұмыс кітабының кез келген жерінде болуы мүмкін.
  
• Парақтан тыс фотосуреттер Әр кітапта басқа парақтар болуы мүмкін.
• Деректері бар кестелерде жолдардың саны әртүрлі және жұмыс парағындағы басқа жолдан басталуы мүмкін.
• Әртүрлі кестелердегі бірдей бағандардың атаулары әртүрлі болуы мүмкін (мысалы, Саны = Саны = Саны).
• Кестелердегі бағандарды басқа ретпен орналастыруға болады.

Тапсырма: парақтағы барлық файлдардан сату деректерін жинаңыз фотосуреттер жиынтықты немесе оған кез келген басқа аналитиканы кейіннен құру үшін бір ортақ кестеге айналдырыңыз.

Қадам 1. Баған атауларының каталогын дайындау

Ең алдымен, баған атауларының барлық мүмкін нұсқалары және олардың дұрыс түсіндірмесі бар анықтамалық дайындау керек:

Біз бұл тізімді қойындыдағы «Кесте ретінде пішімдеу» түймесін пайдаланып динамикалық «ақылды» кестеге түрлендіреміз Home (Үй — Кесте ретінде пішімдеу) немесе пернелер тіркесімі Ctrl+T және оны пәрмен арқылы Power Query ішіне жүктеңіз Деректер – Кестеден/Ауқымнан (Деректер — кестеден/ауқымнан). Excel бағдарламасының соңғы нұсқаларында оның атауы өзгертілді Жапырақтарымен (парақтан).

Power Query сұрау өңдегішінің терезесінде біз дәстүрлі түрде қадамды жоямыз Түрі өзгертілді түймешігін басу арқылы оның орнына жаңа қадам қосыңыз f_xформула жолағында (егер ол көрінбесе, оны қойындыда қосуға болады шолу) және формуланы кірістірілген Power Query M тілінде енгізіңіз:

=Кесте.Жолдар(Дереккөз)

Бұл пәрмен алдыңғы қадамда жүктелгенді түрлендіреді қайнар көз анықтамалық кесте кірістірілген тізімдерден (Тізім) тұратын тізімге, олардың әрқайсысы өз кезегінде мәндер жұбы болып табылады болды - болды бір жолдан:

Бізге бұл деректер түрі сәл кейінірек, барлық жүктелген кестелердің тақырыптарының атын жаппай өзгерту кезінде қажет болады.

Түрлендіруді аяқтағаннан кейін пәрмендерді таңдаңыз Негізгі бет — Жабу және жүктеу — Жабу және жүктеу… және импорт түрі Тек қосылым жасаңыз (Басты бет — Жабу&Жүктеу — Жабу&Жүктеу... — Тек қосылым жасау) және Excel бағдарламасына оралыңыз.

2-қадам. Біз барлық файлдардан барлығын сол күйінде жүктейміз

Енді барлық файлдарымыздың мазмұнын қалтадан жүктеп алайық – әзірге сол күйінде. Командаларды таңдау Деректер – Деректерді алу – Файлдан – Қалтадан (Деректер — Деректерді алу — Файлдан — Қалтадан) содан кейін біздің бастапқы кітаптарымыз орналасқан қалта.

Алдын ала қарау терезесінде түймесін басыңыз Түрлендіру (Трансформация) or өзгеріс (Өңдеу):

Содан кейін барлық жүктелген файлдардың мазмұнын кеңейтіңіз (екілік) баған тақырыбында қос көрсеткілері бар түйме мазмұны:

Бірінші файл мысалында Power Query (Vostok.xlsx) бізден әр жұмыс кітабынан алғымыз келетін парақтың атын сұрайды – таңдау фотосуреттер және OK түймесін басыңыз:

Осыдан кейін (шын мәнінде) пайдаланушыға түсініксіз бірнеше оқиғалар орын алады, олардың салдары сол жақ панельде анық көрінеді:

1. Power Query қалтадан бірінші файлды алады (ол бізде болады Vostok.xlsx — көру Файл мысалы) мысал ретінде және сұрау жасау арқылы оның мазмұнын импорттайды Үлгі файлды түрлендіру. Бұл сұрауда бірнеше қарапайым қадамдар болады қайнар көз (файлға қол жеткізу) навигация (парақ таңдау) және мүмкін тақырыптарды көтеру. Бұл сұрау тек бір нақты файлдан деректерді жүктей алады Vostok.xlsx.
   
2. Осы сұраудың негізінде онымен байланысты функция жасалады Файлды түрлендіру (сипатты белгішемен көрсетілген f_x), мұнда бастапқы файл енді тұрақты емес, айнымалы мән – параметр болады. Осылайша, бұл функция біз оған дәлел ретінде кіретін кез келген кітаптан деректерді шығара алады.
   
3. Функция бағандағы әрбір файлға (екілік) кезекпен қолданылады мазмұны – қадам бұған жауапты Теңшелетін функцияны шақыру файлдар тізіміне баған қосатын сұрауымызда Файлды түрлендіру әрбір жұмыс кітабынан импорт нәтижелерімен:

   

4. Қосымша бағандар жойылады.
5. Кірістірілген кестелердің мазмұны кеңейтілді (қадам Кеңейтілген кесте бағаны) – және біз барлық кітаптардан деректер жинаудың соңғы нәтижелерін көреміз:

   
   

3-қадам. Тегістеу

Алдыңғы скриншот тікелей құрастырудың «сол қалпында» сапасыз болып шыққанын анық көрсетеді:

• Бағандар керісінше.
• Көптеген қосымша жолдар (бос және тек қана емес).
• Кесте тақырыптары тақырыптар ретінде қабылданбайды және деректермен араласады.
  

Сіз бұл ақаулардың барлығын оңай түзете аласыз – Үлгі файлын түрлендіру сұрауын түзетіңіз. Біз оған енгізетін барлық түзетулер автоматты түрде байланысты файлды түрлендіру функциясына түседі, яғни олар кейінірек әрбір файлдан деректерді импорттау кезінде пайдаланылады.

Сұраныс ашу арқылы Үлгі файлды түрлендіру, қажетсіз жолдарды сүзу үшін қадамдарды қосыңыз (мысалы, баған бойынша Column2) және түймені пайдаланып тақырыптарды көтеру Бірінші жолды тақырып ретінде пайдаланыңыз (Тақырып ретінде бірінші жолды пайдаланыңыз). Кесте әлдеқайда жақсы көрінеді.

Әртүрлі файлдардағы бағандар бір-бірінің астына кейін автоматты түрде сәйкес келуі үшін олар бірдей аталу керек. М-кодтың бір жолы бар бұрын жасалған каталогқа сәйкес мұндай жаппай атауды өзгертуге болады. Түймені қайтадан басайық f_x формула жолында және өзгерту үшін функцияны қосыңыз:

= Table.RenameColumns(#”Жоғартылған тақырыптар”, тақырыптар, MissingField.Ignore)

Бұл функция кестені алдыңғы қадамнан алады Көтерілген тақырыптар және кірістірілген іздеу тізіміне сәйкес ондағы барлық бағандардың атын өзгертеді Тақырыптар. Үшінші аргумент MissingField.Ignore каталогта бар, бірақ кестеде жоқ тақырыптарда қате пайда болмауы үшін қажет.

Шындығында, бәрі осы.

Сұраныс дегенге қайта келу Есептер біз мүлде басқа суретті көреміз – алдыңғысынан әлдеқайда әдемі:

• Power Query, Power Pivot, Power BI деген не және олар Excel пайдаланушысына не үшін қажет
  
• Берілген қалтадағы барлық файлдардан деректерді жинау
• Кітаптың барлық парақтарынан мәліметтерді бір кестеге жинау