Contents
Samar da matsala
A matsayin bayanan shigarwa, muna da fayil ɗin Excel, inda ɗayan zanen gado ya ƙunshi tebur da yawa tare da bayanan tallace-tallace na nau'i mai zuwa:
Lura cewa:
- Tables na daban-daban masu girma dabam kuma tare da samfurori daban-daban na samfurori da yankuna a cikin layuka da ginshiƙai ba tare da rarrabuwa ba.
- Za a iya shigar da layukan da ba su da komai a tsakanin teburi.
- Yawan tebur na iya zama kowane.
Mahimman zato guda biyu. Ana kyautata zaton cewa:
- Sama da kowane tebur, a cikin ginshiƙi na farko, akwai sunan manajan wanda tebur ya kwatanta tallace-tallace (Ivanov, Petrov, Sidorov, da sauransu).
- Sunan kayayyaki da yankuna a cikin duk allunan an rubuta su ta hanya ɗaya - tare da daidaiton shari'a.
Babban makasudin shine tattara bayanai daga dukkan allunan zuwa teburin da aka daidaita guda ɗaya, wanda ya dace da bincike na gaba da gina taƙaitawa, watau a cikin wannan:
Mataki 1. Haɗa zuwa fayil ɗin
Bari mu ƙirƙiri sabon fayil ɗin Excel mara komai kuma zaɓi shi akan shafin data umurnin Samun Bayanai - Daga Fayil - Daga Littafin (Data - Daga fayil - Daga littafin aiki). Ƙayyade wurin fayil ɗin tushen tare da bayanan tallace-tallace sannan a cikin taga mai kewayawa zaɓi takardar da muke buƙata kuma danna maɓallin. Maida Bayanai (Canja bayanai):
Sakamakon haka, duk bayanan da ke cikinsa yakamata a loda su cikin editan Query:
Mataki 2. Tsaftace shara
Share matakan da aka samar ta atomatik nau'in gyarawa (An Canja Nau'in) и Maɗaukakin kai (Ingantattun Headers) da kuma kawar da layukan fanko da layi tare da jimlar ta amfani da tacewa null и TOTAL ta shafi na farko. A sakamakon haka, muna samun hoto mai zuwa:
Mataki 3. Ƙara manajoji
Don fahimtar daga baya inda tallace-tallace ya kasance, yana da mahimmanci don ƙara shafi zuwa teburin mu, inda a cikin kowane jere za a sami sunan mahaifi daidai. Don wannan:
1. Bari mu ƙara ginshiƙi na taimako tare da lambobin layi ta amfani da umarnin Ƙara Rukunin - Rukunin Fihirisa - Daga 0 (Ƙara shafi - Shagon Fihirisa - Daga 0).
2. Ƙara ginshiƙi tare da dabara tare da umarni Ƙara ginshiƙi - Shagon Al'ada (Ƙara shafi - ginshiƙi na al'ada) kuma gabatar da gini mai zuwa a can:
Ma'anar wannan tsari yana da sauƙi - idan darajar tantanin halitta na gaba a cikin shafi na farko shine "samfurin", to wannan yana nufin cewa mun yi tuntuɓe a farkon sabon tebur, don haka muna nuna darajar tantanin halitta ta baya tare da sunan manaja. In ba haka ba, ba za mu nuna komai ba, watau null.
Don samun mahaifar tantanin halitta tare da suna na ƙarshe, mun fara komawa zuwa tebur daga mataki na baya #"An ƙara index", sannan a saka sunan ginshiƙin da muke buƙata [Shafi na 1] a maƙallan murabba'i da lambar tantanin halitta a cikin wannan ginshiƙi cikin maƙallan masu lanƙwasa. Lambar tantanin halitta zai zama ɗaya ƙasa da na yanzu, wanda muke ɗauka daga ginshiƙi index, bi da bi.
3. Ya rage don cika sel marasa komai tare da null sunaye daga sel mafi girma tare da umarni Canza - Cika - Kasa (Canza - Cika - Kasa) kuma share ginshiƙin da ba a buƙata tare da fihirisa da layuka masu suna na ƙarshe a cikin ginshiƙi na farko. A sakamakon haka, muna samun:
Mataki na 4. Ƙirƙiri cikin tebur daban ta manajoji
Mataki na gaba shine haɗa layuka na kowane manajan zuwa tebur daban. Don yin wannan, a kan Canjawa shafin, yi amfani da Rukunin ta hanyar umarni (Canja - Rukuni ta hanyar) kuma a cikin taga wanda ya buɗe, zaɓi ginshiƙin Manajan da aiki Duk layuka (Duk layuka) don tattara bayanai kawai ba tare da amfani da duk wani aikin tarawa ba. su ( jimlar, matsakaici, da dai sauransu). P.):
A sakamakon haka, muna samun tebur daban-daban ga kowane manajan:
Mataki na 5: Canza Teburan Gida
Yanzu muna ba da tebur ɗin da ke kwance a cikin kowane tantanin halitta na ginshiƙin da aka samu Duk bayanai a cikin tsari mai kyau.
Da farko, share ginshiƙi wanda ba a buƙatarsa a cikin kowane tebur Manager. Muna amfani kuma Rukunin al'ada tab Sake Kama (Canja - Al'ada shafi) da kuma wannan maƙirarin:
Sa'an nan, tare da wani ginshiƙi ƙididdiga, muna ɗaga jere na farko a kowane tebur zuwa kanun:
Kuma a ƙarshe, muna yin babban canji - buɗe kowane tebur ta amfani da M-aiki Tebur.UnpivotWasu Rukunin:
Sunayen yankuna daga kan taken za su shiga cikin sabon shafi kuma za mu sami kunkuntar, amma a lokaci guda, tebur mai tsayi mai tsayi. Kwayoyin fanko tare da null an yi watsi da su.
Cire ginshiƙai marasa mahimmanci, muna da:
Mataki na 6 Fadada Tebur Masu Gida
Ya rage don faɗaɗa duk ingantattun allunan teburi zuwa jeri ɗaya ta amfani da maɓalli tare da kibau biyu a cikin taken shafi:
... kuma a ƙarshe mun sami abin da muke so:
Kuna iya fitar da sakamakon da aka samu zuwa Excel ta amfani da umarnin Gida - Rufe kuma Loda - Rufe kuma Load a… (Gida - Rufe & Load - Rufe & Loda zuwa…).
- Gina teburi tare da kanun labarai daban-daban daga littattafai da yawa
- Tattara bayanai daga duk fayiloli a cikin babban fayil da aka bayar
- Ana tattara bayanai daga dukkan zanen gado na littafin cikin tebur guda