Munguva pfupi yapfuura, takakurukura kushandiswa kweFILTER.XML basa rekutumira data yeXML kubva paInternet - basa guru iro basa iri, chaizvoizvo, rinotarisirwa. Munzira, zvakadaro, kumwe kushandiswa kusingatarisirwi uye kwakanaka kweichi basa kwavepo - nekukurumidza kupatsanura mavara anonamira mumaseru.
Ngatiti isu tine data column seizvi:
Zvechokwadi, kuti zvive nyore, ndinoda kuiparadzanisa muzvikamu zvakasiyana: zita rekambani, guta, mugwagwa, imba. Iwe unogona kuita izvi nenzira dzakawanda dzakasiyana:
- shandisa Mavara nezvikamu kubva kune tab Data (Data - Mavara kumakoramu) uende nhanho nhatu Text parser. Asi kana iyo data ikachinja mangwana, iwe uchafanirwa kudzokorora maitiro ese zvakare.
- Isa iyi data muPower Query uye igovane ipapo, uye wozoidzosera kupepa, uye wozogadzirisa mubvunzo kana data rachinja (izvo zvatova nyore).
- Kana iwe uchida kugadzirisa pane nhunzi, saka iwe unogona kunyora mamwe akaomarara mafomula ekutsvaga koma uye kubvisa zvinyorwa pakati pavo.
Uye iwe unogona kuzviita zvakanyanyisa uye kushandisa iyo FILTER.XML basa, asi ine chekuita nazvo?
Iyo FILTER.XML basa rinogashira sekupokana kwayo kwekutanga kodhi yeXML - zvinyorwa zvakamisikidzwa nematagi akakosha uye hunhu, uye wozvipatsanura muzvikamu zvaro, uchibvisa zvidimbu zve data zvatinoda. Iyo XML kodhi kazhinji inotaridzika seizvi:
Mu XML, chimwe nechimwe che data chinofanira kuvharirwa muma tag. Tegi ndechimwe chinyorwa (mumuenzaniso uri pamusoro apa maneja, zita, purofiti) yakavharirwa mumabhuraketi. Mategi anogara achiuya ari maviri maviri - kuvhura nekuvhara (ine slash yakawedzerwa pakutanga).
Iyo FILTER.XML basa rinogona kubvisa zviri nyore zviri mukati memategi ese atinoda, semuenzaniso, mazita evatungamiriri vese, uye (kunyanya kukosha) kuaratidza ese kamwechete mune imwe runyorwa. Saka basa redu nderekuwedzera ma tag kune chinyorwa chinyorwa, tichichishandura kuita XML kodhi yakakodzera kuongororwa kunotevera neiyo FILTER.XML basa.
Kana tikatora kero yekutanga kubva pane yedu semuenzaniso, isu tichazofanira kuishandura kuita ichi chivakwa:
Ndakafonera kuvhurika kwepasi rose nekuvhara ese mameseji tag t, uye iwo ma tag ekumisikidza chimwe nechimwe chinhu s., asi iwe unogona kushandisa chero mamwe mazita - hazvina basa.
Kana tikabvisa indents uye mitsara inopwanya kubva pane iyi kodhi - zvachose, nenzira, sarudzo uye yakawedzerwa chete kuti ijekeswe, zvino zvese izvi zvinoshanduka kuita mutsara:
Uye inogona kutowanikwa zviri nyore kubva kunobva kero nekutsiva commas mairi nematagi akati wandei. kushandisa basa SUBSTITUTE (SUBSTITUTE) uye kunama nechiratidzo & pakutanga uye pakupera kwema tag ekuvhura nekuvhara:
Kuti tiwedzere mhedzisiro inoguma nehorizontally, isu tinoshandisa yakajairwa basa TRANSP (TRANSPOSE), tichiputira fomula yedu mairi:
Chinhu chakakosha cheiyi dhizaini ndechekuti mushanduro nyowani yeHofisi 2021 uye Hofisi 365 ine tsigiro yeane simba arrays, hapana ma gesture akakosha anodiwa pakuisa - ingo pinda wodzvanya pa. pinda - iyo formula pachayo inotora nhamba yemasero yainoda uye zvinhu zvose zvinoshanda ne bang. Mushanduro dzakapfuura, pakanga pasati pave neane simba arrays, iwe unozofanirwa kutanga wasarudza nhamba yakakwana yemasero asina chinhu usati waisa fomula (iwe unogona nemargin), uye mushure mekugadzira fomula, tinya iyo keyboard nzira yekudimbudzira. Ctrl+kusuduruka+pindakuiisa seyakarongwa fomula.
Uchenjeri hwakafanana hunogona kushandiswa kana uchipatsanura zvinyorwa zvakanamirwa pamwechete musero rimwe kuburikidza nekutyora mutsetse:
Musiyano chete nemuenzaniso wapfuura ndewekuti pachinzvimbo checomma, pano tinotsiva isingaonekwe Alt + Enter mutsara wekutyora mutsara, iyo inogona kutsanangurwa mufomula uchishandisa iyo CHAR basa nekodhi 10.
- Hunyengeri hwekushanda nemitsara yekutyora (Alt + Enter) muExcel
- Kamura mavara nemakoramu muExcel
- Kutsiva mavara neSUBSTITUTE