Ƙididdiga Hanyoyin Bayanai a cikin Tambayar Wuta

Idan kun riga kun fara amfani da kayan aikin ƙarar Query Query kyauta a cikin Microsoft Excel, nan ba da jimawa ba za ku ci karo da wata ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararrun ƙwararru, amma sau da yawa kuma matsala mai ban sha'awa mai ban sha'awa wacce ke da alaƙa da karya hanyoyin haɗin gwiwa zuwa bayanan tushen. Ma'anar matsalar ita ce, idan a cikin tambayarka ka koma ga fayiloli na waje ko manyan fayiloli, to Power Query hardcodes da cikakkiyar hanyar zuwa gare su a cikin rubutun tambaya. Komai yana aiki lafiya a kan kwamfutarka, amma idan kun yanke shawarar aika fayil tare da buƙatun ga abokan aikin ku, to za su ji takaici, saboda. suna da wata hanya ta daban zuwa bayanan tushen akan kwamfutar su, kuma tambayarmu ba za ta yi aiki ba.

Me za a yi a irin wannan yanayi? Bari mu dubi wannan harka dalla-dalla tare da misali mai zuwa.

Samar da matsala

A ce muna da a cikin babban fayil E:Rahoton tallace-tallace karya fayil Manyan samfuran 100.xls, wanda shine saukewa daga bayanan kamfanoni ko tsarin ERP (1C, SAP, da dai sauransu) Wannan fayil ɗin yana ƙunshe da bayanai game da abubuwan da aka fi sani da kayayyaki kuma yayi kama da wannan a ciki:

Wataƙila a bayyane yake a kan jemage cewa kusan ba zai yuwu a yi aiki tare da shi a cikin Excel a cikin wannan tsari ba: layuka maras amfani ta hanyar ɗaya tare da bayanai, sel da aka haɗa, ƙarin ginshiƙai, jagorar matakai da yawa, da sauransu za su tsoma baki.

Don haka, kusa da wannan fayil ɗin a cikin babban fayil ɗin, muna ƙirƙirar wani sabon fayil Mai kulawa.xlsx, wanda a ciki za mu ƙirƙiri tambayar Tambayi Power wanda zai loda munanan bayanai daga fayil ɗin loda tushen Manyan samfuran 100.xls, kuma ka tsara su:

Yin buƙatu zuwa fayil na waje

Buɗe fayil ɗin Mai kulawa.xlsx, zaɓi akan shafin data umurnin Samu Bayanai - Daga Fayil - Daga Littafin Ayyukan Excel (Data - Sami Data - Daga fayil - Daga Excel), sannan saka wurin fayil ɗin tushen da takardar da muke buƙata. Za a loda bayanan da aka zaɓa a cikin editan Query Query:

Mu dawo da su daidai:

1. Share fankon layi tare da Gida - Share layuka - Share layukan da ba komai (Gida - Cire Layuka - Cire Layukan da ba kowa).
2. Share manyan layuka 4 da ba dole ba Gida - Share Layuka - Share Manyan Layukan (Gida - Cire Layuka - Cire Manyan Layukan).
3. Ɗaga jere na farko zuwa kan teburin tebur tare da maɓallin Yi amfani da layin farko azaman masu kai tab Gida (Gida - Yi amfani da jere na farko azaman kai).
4. Ware labarin mai lamba biyar daga sunan samfurin a cikin shafi na biyu ta amfani da umarnin tsaga shafi tab Sake Kama (Canza - Rarraba ginshiƙi).
5. Share ginshiƙan da ba dole ba kuma a sake suna kan taken ragowar don ingantacciyar gani.

A sakamakon haka, ya kamata mu sami wadannan, mafi dadi hoto:

Ya rage don loda wannan tebur mai daraja a mayar da shi zuwa takardar da ke cikin fayil ɗin mu Mai kulawa.xlsx kungiyar rufe da saukewa (Gida - Rufe&Load) tab Gida:

Nemo hanyar zuwa fayil a cikin buƙata

Yanzu bari mu ga yadda tambayar mu ta kasance "ƙarƙashin kaho", a cikin yare na ciki da aka gina cikin Tambayoyin Wuta tare da taƙaitaccen suna "M". Don yin wannan, koma zuwa tambayar mu ta danna sau biyu a cikin dama Bukatu da haɗin kai kuma a kan tab review zabi Babban Edita (Duba - Babban Edita):

A cikin taga da ya buɗe, layi na biyu nan da nan ya bayyana hanya mai wuyar ƙima zuwa fayil ɗin loda mu na asali. Idan za mu iya maye gurbin wannan kirtani na rubutu da siga, m, ko hanyar haɗi zuwa tantanin halitta na Excel inda aka riga aka rubuta wannan hanyar, to za mu iya canza shi cikin sauƙi daga baya.

Ƙara tebur mai wayo tare da hanyar fayil

Mu rufe Query Query a yanzu kuma mu koma fayil ɗin mu Mai kulawa.xlsx. Bari mu ƙara sabon takardar da ba komai a ciki sannan mu yi ƙaramin tebur “mai wayo” a kai, a cikin tantanin halitta ɗaya kaɗai za a rubuta cikakken hanyar zuwa fayil ɗin bayanan tushen mu:

Don ƙirƙirar tebur mai wayo daga kewayon yau da kullun, zaku iya amfani da gajeriyar hanyar madannai Ctrl+T ko button Tsara azaman tebur tab Gida (Gida - Tsarin azaman Tebur). Taken shafi (cell A1) na iya zama komai. Hakanan lura cewa don tsabta na ba tebur suna Siga tab Constructor (Zane).

Kwafi hanya daga Explorer ko ma shigar da shi da hannu, ba shakka, ba mai wahala ba ne, amma yana da kyau a rage girman yanayin ɗan adam kuma ƙayyade hanyar, idan zai yiwu, ta atomatik. Ana iya aiwatar da wannan ta amfani da daidaitaccen aikin takaddar aikin Excel Sanarwa (CELL), wanda zai iya ba da tarin bayanai masu amfani game da tantanin halitta da aka ƙayyade azaman hujja - gami da hanyar zuwa fayil ɗin yanzu:

Idan muka ɗauka cewa fayil ɗin bayanan tushen koyaushe yana kwance a cikin babban fayil ɗaya da Mai sarrafa mu, to hanyar da muke buƙata za ta iya zama ta hanyar wannan dabara:

= HAGU (CELL("filename")); NEMA ("["; CELL ("filename")) -1)&"Sakamakon samfuran 100.xls"

ko a cikin Turanci:

=LEFT(CELL(«filename»); NEMI («[«; CELL («filename»)) -1)&»Топ-100 товаров.xls»

... ina aikin yake LEVSIMV (HAGU) yana ɗaukar guntun rubutu daga cikakkiyar mahadar har zuwa madaidaicin madauri na buɗewa (watau hanyar zuwa babban fayil ɗin yanzu), sannan suna da tsawo na fayil ɗin bayanan tushen mu yana manne da shi.

Daidaita hanya a cikin tambayar

Ƙarshe kuma mafi mahimmancin taɓawa ya rage - don rubuta hanyar zuwa fayil ɗin tushe a cikin buƙatar Manyan samfuran 100.xls, Yana nufin cell A2 na mu halitta "smart" tebur Siga.

Don yin wannan, bari mu koma kan tambayar Power Query kuma mu sake buɗe ta Babban Edita tab review (Duba - Babban Edita). A maimakon hanyar rubutu a cikin zance "E: Rahoton tallace-tallace Top 100 samfurori.xlsx" Bari mu gabatar da tsari mai zuwa:

Littafin Aikin Yanzu (){[Name=”Saituna”] [Abin ciki]0 {}[Hanya zuwa bayanan tushe]

Bari mu ga abin da ya kunsa:

• Littafin Aikin Yanzu () aiki ne na harshen M don samun damar abubuwan da ke cikin fayil ɗin yanzu
• {[Name=”Saituna”] [Abin ciki] - wannan ma'auni ne na gyare-gyare ga aikin da ya gabata, yana nuna cewa muna son samun abubuwan da ke cikin tebur "mai hankali". Siga
• [Hanya zuwa bayanan tushe] shine sunan ginshiƙi a cikin tebur Sigawanda muke nufi
  
• 0 {} shine lambar layi a cikin tebur Sigadaga inda muke son daukar bayanai. Hul ɗin baya ƙirgawa kuma lambar tana farawa daga sifili, ba daga ɗaya ba.

Wannan ke nan, a gaskiya.

Ya rage don dannawa Gama kuma duba yadda bukatar mu ke aiki. Yanzu, lokacin aika babban fayil ɗin tare da fayiloli biyu a ciki zuwa wani PC, buƙatar za ta ci gaba da aiki kuma ta ƙayyade hanyar zuwa bayanan ta atomatik.

• Menene Tambayar Wuta kuma me yasa ake buƙata lokacin aiki a Microsoft Excel
• Yadda ake shigo da snippet rubutu mai yawo cikin Query Query
• Sake tsara hanyar Crosstab XNUMXD zuwa Tebur mai fa'ida tare da Tambayar Wuta