Ho bapisa litafole tse peli

Re na le litafole tse peli (mohlala, mefuta ea khale le e ncha ea lenane la litheko), tseo re hlokang ho li bapisa le ho fumana liphapang kapele:

Hang-hang ho hlakile hore ho na le ho hong ho ekelitsoeng lethathamong le lecha la theko (matsatsi, konofolo ...), ho na le ntho e nyametseng (li-blackberries, tse tala ...), litheko li fetohile bakeng sa thepa e itseng (lifeiga, mahapu ...). U hloka ho fumana kapele le ho bonts'a liphetoho tsena kaofela.

Bakeng sa mosebetsi ofe kapa ofe ho Excel, hangata ho na le tharollo e fetang e le 'ngoe (hangata 4-5). Bakeng sa bothata ba rona, mekhoa e mengata e fapaneng e ka sebelisoa:

• mosebetsi VPR (VLOOKUP) - batla mabitso a lihlahisoa lethathamong le lecha la theko ho la khale 'me u bontše theko ea khale haufi le e ncha, ebe u tšoara liphapang
• kopanya manane a mabeli ho e le 'ngoe ebe u theha tafole ea pivot e thehiloeng ho eona, moo liphapang li tla bonahala ka ho hlaka
• sebelisa Keketso ea Potso ea Matla bakeng sa Excel

A re li nkeng kaofela ka tatellano.

Mokhoa oa 1. Ho bapisa litafole le mosebetsi oa VLOOKUP

Haeba u sa tloaelane ka ho feletseng le tšobotsi ena e babatsehang, joale sheba pele mona 'me u bale kapa u shebe thuto ea video ho eona - ipholose lilemo tse' maloa tsa bophelo.

Ka tloaelo, ts'ebetso ena e sebelisoa ho hula data ho tloha tafoleng e 'ngoe ho ea ho e' ngoe ka ho hokahanya parameter e itseng e tloaelehileng. Tabeng ena, re tla e sebelisa ho sutumelletsa litheko tsa khale ho theko e ncha:

Lihlahisoa tseo, tseo phoso ea #N/A e hlahileng ho tsona, ha li lethathamong la khale, ke hore, li kentsoe. Liphetoho tsa theko le tsona li bonahala ka ho hlaka.

Pros mokhoa ona: e bonolo le e hlakileng, "classic of the genre", joalokaha ba re. E sebetsa ho mofuta ofe kapa ofe oa Excel.

tlhoka mesola e teng hape. Ho batla lihlahisoa tse kentsoeng lethathamong le lecha la litheko, u tla tlameha ho etsa ts'ebetso e ts'oanang ka lehlakoreng le fapaneng, ke hore, ho hula litheko tse ncha ho theko ea khale ka thuso ea VLOOKUP. Haeba boholo ba litafole bo fetoha hosane, joale liforomo li tla tlameha ho fetoloa. Hantle, 'me litafoleng tse kholo haholo (> mela e likete tse 100), thabo ena eohle e tla fokotseha ka mokhoa o hlomphehang.

Mokhoa oa 2: Ho bapisa litafole ho sebelisa pivot

Ha re qopiseng litafole tsa rona ka tlas'a e 'ngoe, re kenyelletsa kholumo e nang le lebitso la lenane la litheko, e le hore hamorao u ka utloisisa hore na ke lethathamo lefe:

Hona joale, ho itšetlehile ka tafole e entsoeng, re tla etsa kakaretso ka Kenya – PivotTable (Kenya - Pivot Table). A re lahleleng tšimo Product sebakeng sa mela, tšimo theko ho isa sebakeng sa litšiea le tšimo Цena sebakeng:

Joalokaha u ka bona, tafole ea pivot e tla iketsetsa lethathamo le akaretsang la lihlahisoa tsohle ho tloha lethathamong la khale le le lecha la theko (ha ho na ho pheta-pheta!) U ka bona ka ho hlaka lihlahisoa tse ekelitsoeng (ha li na theko ea khale), lihlahisoa tse tlositsoeng (ha li na theko e ncha) le liphetoho tsa theko, haeba li teng.

Lipalopalo tse kholo tafoleng e joalo ha li utloahale, 'me li ka holofala ho tab Moetsi - Lipalo-palo tse kholo - E thibela mela le litšiea (Moqapi - Lipalo-palo tse kholo).

Haeba litheko li fetoha (empa eseng palo ea thepa!), joale ho lekane ho nchafatsa kakaretso e entsoeng ka ho tobetsa ho eona ka ho le letona - khatholla.

Pros: Mokhoa ona ke taelo ea boholo ka potlako ka litafole tse kholo ho feta VLOOKUP. 

tlhoka mesola: o hloka ho kopitsa data ka tlas'a e mong le ho eketsa kholomo e nang le lebitso la lenane la theko. Haeba boholo ba litafole bo fetoha, joale u tlameha ho etsa ntho e 'ngoe le e' ngoe hape.

Mokhoa oa 3: Ho bapisa litafole le Potso ea Matla

Power Query ke kenyelletso ea mahala ea Microsoft Excel e u lumellang ho kenya data ho Excel ho tsoa mohloling ofe kapa ofe ebe o fetola data ena ka tsela efe kapa efe eo u e batlang. Ho Excel 2016, kenyelletso ena e se e ntse e hahiloe ka ho sa feleng ho tab Lintlha (Lintlha), mme bakeng sa Excel 2010-2013 o hloka ho e jarolla ka thoko ho webosaete ea Microsoft le ho e kenya - fumana tabo e ncha. Potso ea Matla.

Pele re kenya manane a litheko tsa rona ho Power Query, a tlameha ho qala ka ho fetoloa litafole tse bohlale. Ho etsa sena, khetha sebaka se nang le data ebe o tobetsa motsoako ho keyboard Ctrl+T kapa kgetha tab ya lenteng Lehae – Fometa joalo ka tafole (Lehae - Fometa joalo ka Tafole). Mabitso a litafole tse entsoeng a ka lokisoa ho tab Constructor (Ke tla tlohela maemo Lethathamo 1 и Lethathamo 2, tse fumanoang ka ho sa feleng).

Kenya theko ea khale ho Power Query u sebelisa konopo Ho tloha ho Tafole/Range (Ho tsoa ho Tafole/Boemo) ho tswa ho tab Lintlha (Letsatsi) kapa ho tsoa ho tab Potso ea Matla (ho ipapisitse le mofuta oa Excel). Ka mor'a ho kenya, re tla khutlela Excel ho tloha Power Query ka taelo Koala 'me u laole - Koala 'me u kene... (Koala 'me U Laele - Koala 'me U Lokise ho…):

… 'me fensetereng e hlahang ebe u khetha Etsa feela khokahano (Khokahano Feela).

Pheta se tšoanang ka lenane le lecha la theko. 

Joale ha re theheng potso ea boraro e tla kopanya le ho bapisa data ho tsoa ho tse peli tse fetileng. Ho etsa sena, khetha ho Excel tab Lintlha - Fumana Lintlha - Kopanya Likopo - Kopanya (Data - Fumana Lintlha - Kopanya Lipotso - Kopanya) kapa tobetsa konopo Kopanya (kopanya) tab ya Potso ea Matla.

Ka fensetere ea ho kopanya, khetha litafole tsa rona mananeng a theoha, khetha litšiea tse nang le mabitso a thepa ho tsona, 'me ka tlase, beha mokhoa oa ho kopanya - Felletse kantle (Kantle e felletseng):

Ka mor'a ho tobetsa OK Tafole ea litšiea tse tharo e lokela ho hlaha, moo kholomong ea boraro u hlokang ho holisa litaba tsa litafole tse behiloeng ka metsu e habeli hloohong:

Ka lebaka leo, re fumana ho kopanngoa ha data ho tloha litafoleng ka bobeli:

Ho molemo, ehlile, ho reha mabitso a kholomo hloohong ka ho tobetsa habeli ho a utloisisehang haholoanyane:

'Me hona joale e thahasellisang ka ho fetisisa. Eya ho tab Kenya kholomo (Eketsa Kholomo) ebe o tobetsa konopo Kholomo e nang le maemo (Conditional Column). 'Me ka fensetere e butsoeng, kenya maemo a' maloa a liteko ka boleng ba bona ba tlhahiso:

E sala ho tobetsa OK ebe u kenya tlaleho ea sephetho ho Excel u sebelisa konopo e tšoanang koala le ho jarolla (Koala & Laela) tab ya Home (Lehae):

Botle.

Ho feta moo, haeba liphetoho leha e le life li etsahala lethathamong la litheko nakong e tlang (mela e kenyelelitsoe kapa ea hlakoloa, litheko lia fetoha, joalo-joalo), ho tla lekana feela ho ntlafatsa likopo tsa rona ka tsela e khuts'oane ea keyboard. Ctrl+Alt+F5 kapa ka konopo Hlatsoa kaofela (Hlakola Tsohle) tab ya Lintlha (Letsatsi).

Pros: Mohlomong tsela e ntle ka ho fetisisa le e bonolo ho feta tsohle. E sebetsa ka bohlale ka litafole tse kholo. Ha e hloke liphetoho tsa letsoho ha o fetola boholo ba litafole.

tlhoka mesola: E hloka kenyelletso ea Potso ea Matla (ho Excel 2010-2013) kapa Excel 2016 hore e kengoe. Mabitso a likholomo mohloling oa data ha aa tlameha ho fetoloa, ho seng joalo re tla fumana phoso "Kholomo e joalo ha e ea fumanoa!" ha o leka ho nchafatsa potso.

• Mokhoa oa ho bokella data ho tsoa lifaeleng tsohle tsa Excel foldareng e fanoeng o sebelisa Power Query
• Mokhoa oa ho fumana lipapali lipakeng tsa manane a mabeli ho Excel
• Ho kopanya manane a mabeli ntle le likopi