Бир нече китептерден ар кандай аталыштар менен таблицаларды түзүңүз

Проблеманы түзүү

Бизде бир папкада бир нече файлдар бар (биздин мисалда – 4 даана, жалпы учурда – сиз каалагандай). отчеттор:

Ичинде бул файлдар төмөнкүдөй көрүнөт:

Мында:

• Бизге керектүү маалымат баракчасы ар дайым деп аталат Photos, бирок иш китебинин каалаган жеринде болушу мүмкүн.
  
• Барактан тышкары Photos Ар бир китептин башка барактары болушу мүмкүн.
• Маалыматтар камтылган таблицаларда саптардын саны ар түрдүү жана иш барагында башка саптан башталышы мүмкүн.
• Ар башка таблицалардагы бир эле мамычалардын аталыштары ар кандай болушу мүмкүн (мисалы, Саны = Саны = Саны).
• Таблицалардагы мамычалар башка тартипте жайгаштырылышы мүмкүн.

Тапшырма: барактагы бардык файлдардан сатуу маалыматтарын чогултуу Photos кийинчерээк жыйынтыктоо же башка аналитика түзүү үчүн жалпы таблицага.

Кадам 1. Мамычалардын аталыштарынын каталогун даярдоо

Биринчи нерсе - тилкелердин аталыштары жана аларды туура чечмелөө үчүн бардык мүмкүн болгон варианттар менен маалымдама даярдоо:

Өтмөктөгү Формат катары таблица баскычын колдонуп, бул тизмени динамикалык “акылдуу” таблицага айландырабыз Home (Башкы бет — Таблица катары формат) же баскычтоптун жарлыгы Ctrl+T жана буйрук менен Power Queryге жүктөңүз Берилиштер – Таблицадан/Аразиядан (Маалымат — Таблицадан/Аразиядан). Excelдин акыркы версияларында анын аты өзгөртүлдү Жалбырактары менен (Барактан).

Power Query суроо редакторунун терезесинде биз салттуу түрдө кадамды жок кылабыз Өзгөртүлгөн түрү жана баскычты чыкылдатуу менен анын ордуна жаңы кадам кошуңуз f_xформула тилкесинде (эгерде ал көрүнбөсө, анда аны өтмөктө иштетсеңиз болот кароо) жана ал жерге орнотулган Power Query M тилинде формуланы киргизиңиз:

=Table.ToRows(Булак)

Бул буйрук мурунку кадамда жүктөлгөндү өзгөртөт булак маалымдама таблицасы уяланган тизмелерден (Тизме) турган тизмеге, алардын ар бири өз кезегинде маанилердин түгөйү болуп саналат Болду бир саптан:

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

Конверсияны аяктагандан кийин, буйруктарды тандаңыз Башкы бет — Жабуу жана жүктөө — Жабуу жана жүктөө… жана импорттун түрү Жөн гана байланыш түзүңүз (Башкы бет — Жабуу&Жүктөө — Жабуу&Жүктөө... — Байланыш түзүү гана) жана Excelге кайтуу.

Кадам 2. Биз бардык файлдардан баарын ошол бойдон жүктөйбүз

Эми бардык файлдарыбыздын мазмунун папкадан жүктөйбүз – азырынча, азыркыдай. Командаларды тандоо Берилиштер – Маалыматтарды алуу – Файлдан – Папкадан (Маалымат — Маалыматты алуу — Файлдан — Папкадан) анан биздин булак китептерибиз турган папка.

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

Андан кийин бардык жүктөлүп алынган файлдардын мазмунун кеңейтиңиз (экилик) тилкенин аталышындагы кош жебелери бар баскыч ыраазы:

Биринчи файлдын мисалы боюнча Power Query (Vostok.xlsx) бизден ар бир иш китебинен алгысы келген барактын атын сурайт – тандаңыз Photos жана OK басыңыз:

Андан кийин (чындыгында) колдонуучуга түшүнүксүз болгон бир нече окуялар болот, алардын кесепеттери сол панелде ачык көрүнүп турат:

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

   

4. Кошумча мамычалар алынып салынды.
5. Уюшкан таблицалардын мазмуну кеңейтилген (кадам Кеңейтилген таблица тилкеси) – жана биз бардык китептерден маалыматтарды чогултуунун акыркы жыйынтыгын көрүп жатабыз:

   
   

3-кадам

Мурунку скриншот түздөн-түз чогултуу "кандай болсо да" сапатсыз болуп чыкканын ачык көрсөтүп турат:

• Мамычалар тескери.
• Көптөгөн кошумча саптар (бош жана гана эмес).
• Таблица баштары баш аталыштар катары кабыл алынбайт жана маалыматтар менен аралаштырылат.
  

Сиз бул көйгөйлөрдүн баарын оңой эле чече аласыз - жөн гана Convert Sample File суроосун чыңдаңыз. Биз ага киргизген бардык оңдоолор автоматтык түрдө байланышкан Файлды конверттөө функциясына түшөт, демек алар кийинчерээк ар бир файлдан маалыматтарды импорттоодо колдонулат.

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

Ар кандай файлдардагы тилкелер кийинчерээк автоматтык түрдө бири-бирине туура келиши үчүн, алар бирдей аталышы керек. М-коддун бир сабы менен мурда түзүлгөн каталог боюнча мындай массалык атын өзгөртүүнү жасай аласыз. Дагы кнопканы басалы f_x формула тилкесинде жана өзгөртүү үчүн функцияны кошуңуз:

= Table.RenameColumns(#"Жогорулган баштар", Баштар, MissingField.Ignore)

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

Чындыгында, баары ушунда.

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

• Power Query, Power Pivot, Power BI деген эмне жана алар Excel колдонуучусуна эмне үчүн керек
  
• Берилген папкадагы бардык файлдардан маалыматтарды чогултуу
• Китептин бардык барактарынан маалыматтарды бир таблицага чогултуу