Contents
Du tabloyên me hene (mînak, guhertoyên kevn û nû yên navnîşa bihayê), ku divê em wan bidin ber hev û bi lez cûdahiyan bibînin:
Di cih de diyar dibe ku tiştek li lîsteya nû ya bihayan hatiye zêdekirin (xurme, sîr…), tiştek winda bûye (reş, rîspî…), bihayên hin tiştan (hejîr, mêş…). Pêdivî ye ku hûn zû van hemî guhertinan bibînin û nîşan bidin.
Ji bo her peywirê li Excel, hema hema her gav ji yek çareserî (bi gelemperî 4-5) heye. Ji bo pirsgirêka me, gelek nêzîkatiyên cûda dikarin werin bikar anîn:
- karî VPR (VLOOKUP) - Navên hilberan ji navnîşa bihayê nû ya kevin bigerin û nirxê kevn li kêleka ya nû nîşan bidin, û dûv re cûdahiyan bigirin
- du navnîşan bikin yek û dûv re li ser bingeha wê tabloyek pivot ava bikin, ku cihêreng dê bi zelalî xuya bibin
- Add-in Power Query ji bo Excel bikar bînin
Werin em wan hemûyan bi rêz bigirin.
Rêbaz 1. Berawirdkirina tabloyan bi fonksiyona VLOOKUP
Heke hûn bi vê taybetmendiya ecêb bi tevahî nenas in, wê hingê pêşî li vir binihêrin û li ser vîdyoyek hînkerek vîdyoyê bixwînin an temaşe bikin - çend salên jiyanê xwe xilas bikin.
Bi gelemperî, ev fonksiyon ji bo kişandina daneyan ji tabloyek din bi berhevkirina hin parametreyên hevpar tê bikar anîn. Di vê rewşê de, em ê wê bikar bînin da ku bihayên kevin bixin nav bihayê nû:
Ew berhemên ku li hember wan xeletiya #N/A derketiye, di lîsteya kevn de nîn in, ango hatine zêdekirin. Guhertinên bihayê jî bi zelalî têne xuyang kirin.
Pros Ev rêbaz: sade û zelal, wekî ku ew dibêjin "klasîkî ya celebê". Di her guhertoya Excel de dixebite.
Stewrê jî li wir e. Ji bo lêgerîna hilberên ku di navnîşa bihayê nû de hatine zêdekirin, hûn ê neçar bimînin ku heman prosedurê berevajî vê yekê bikin, ango bi alîkariya VLOOKUP-ê bihayên nû berbi bihayê kevn ve bikişînin. Ger sibe mezinahiya tabloyan biguhere, wê hingê pêdivî ye ku formula bêne sererast kirin. Welê, û li ser tabloyên bi rastî mezin (> 100 hezar rêz), dê hemî ev bextewarî hêdî hêdî hêdî bibe.
Rêbaz 2: Berhevdana tabloyan bi karanîna pivot
Ka em tabloyên xwe yek li binê yê din kopî bikin, stûnek bi navê navnîşa bihayê lê zêde bikin, da ku paşê hûn fêm bikin ji kîjan navnîşê kîjan rêz:
Naha, li ser bingeha tabloya çêkirî, em ê bi kurtasî biafirînin Têxe - PivotTable (Têxe - Tabloya Pivot). Ka em zeviyekê bavêjin mal ber bi qada xêzan, zeviyê Biha li qada stûn û zeviyê Цena nav rêzê:
Wekî ku hûn dibînin, tabloya pivot bixweber navnîşek giştî ya hemî hilberan ji navnîşên bihayên kevn û nû (bê dubarekirin!) çêdike û hilberan bi alfabetîk rêz dike. Hûn dikarin hilberên lêzêdekirî bi zelalî bibînin (bihaya wan a kevn nîne), hilberên jêbirin (bihaya wan a nû tune) û guhertinên bihayê, heke hebe.
Di tabloyek wusa de jimareyên mezin ne wate ne, û ew dikarin li ser tabloyê werin seqet kirin Avaker - Tevheviyên mezin - Ji bo rêz û stûnan neçalak bikin (Sêwiran - Tevahiya Mezin).
Ger biha biguhezin (lê ne hêjmara tiştan!), wê hingê bes e ku meriv kurteya hatî afirandin bi tikandina rastê li ser wê nûve bike - hênikkirin.
Pros: Ev nêzîkatî bi tabloyên mezin ji VLOOKUP-ê re rêzek mezinahiyê zûtir e.
Stewrê: Pêdivî ye ku hûn bi destan daneyan di binê hev de kopî bikin û stûnek bi navê navnîşa bihayê zêde bikin. Ger mezinahiya tabloyan biguhere, wê hingê pêdivî ye ku hûn her tiştî ji nû ve bikin.
Rêbaz 3: Berhevdana tabloyan bi Query Power
Power Query ji bo Microsoft Excel-ê pêvekek belaş e ku dihêle hûn hema hema ji her çavkaniyek daneyê li Excel bar bikin û dûv re van daneyan bi her awayê xwestinê veguherînin. Di Excel 2016-ê de, ev pêvek jixwe li ser tabê ji hêla xwerû ve hatî çêkirin Jimare (Jimare), û ji bo Excel 2010-2013 hûn hewce ne ku wê ji malpera Microsoft-ê cuda dakêşin û saz bikin - tabloyek nû bistînin Pirsa Hêzê.
Berî barkirina navnîşên bihayên me li Power Query, divê ew pêşî li tabloyên biaqil werin veguheztin. Ji bo kirina vê yekê, rêza bi daneyan hilbijêrin û li ser klavyeyê bihevre bikirtînin Ctrl+T an tabloya li ser ribbonê hilbijêrin Mal - Wek tabloyê format bikin (Mal - Format wek Tablo). Navên tabloyên çêkirî dikarin li ser tabê werin rast kirin Kêrker (Ez ê standard bihêlim Table 1 и Table 2, ku ji hêla xwerû ve têne wergirtin).
Bi karanîna bişkojkê bihayê kevn di Power Query de bar bikin Ji Tablo/Range (Ji sifrê/Range) ji tabê Jimare (Rojek) an ji tabê Pirsa Hêzê (li gorî guhertoya Excel-ê ve girêdayî ye). Piştî barkirinê, em ê bi fermanê ji Power Query vegerin Excel Girtin û barkirin - Girtin û barkirin li… (Girtî û Barkirin - Girtin û Barkirin Ji…):
... û di pencereya ku xuya dike de paşê hilbijêrin Tenê pêwendiyek çêbikin (Tenê Têkilî).
Heman tişt bi navnîşa bihayê nû re dubare bikin.
Naha werin em pirsek sêyemîn biafirînin ku dê daneyên her du berê bi hev re bicivîne û bide ber hev. Ji bo vê yekê, li ser tabê di Excel de hilbijêrin Dane - Daneyan bistînin - Daxwazan berhev bikin - Bihev bikin (Daneyên - Daneyên Bistînin - Pirsên Hevgirtinê - Hevgirtin) an jî pêl bişkoka Hevgirêdan (Bihevkelyan) tab Pirsa Hêzê.
Di pencereya tevlêbûnê de, di navnîşên dakêşan de tabloyên me hilbijêrin, stûnên bi navên tiştên di wan de ne hilbijêrin, û li jêr, rêbaza tevlêbûnê saz bikin - Bi tevahî derveyî (Tamam Derveyî):
Piştî hilweşînê OK Divê tabloyek ji sê stûnan xuya bibe, ku di stûna sêyemîn de hûn hewce ne ku naveroka tabloyên hêlînkirî bi karanîna tîra ducar a di serî de berfireh bikin:
Wekî encamek, em yekbûna daneyan ji her du tabloyan digirin:
Bê guman, çêtir e ku meriv navên stûnên di serî de bi du-klîkkirina li ser yên bêtir fêmkirî biguhezîne:
Û niha ya herî balkêş. Biçe tabê Stûnê zêde bikin (Stûn lê zêde bike) û li ser bişkojkê bikirtînin Stûna şertî (Stûna Şert). Û dûv re di pencereya ku vedibe de, çend şertên ceribandinê bi nirxên derketinê yên têkildar re têkevin:
Ew dimîne ku li ser bitikîne OK û rapora encam bi karanîna heman bişkojê li Excel barkirin bigire û dakêşin (Girtî & Barkirin) tab Xane (Xane):
Çelengî.
Wekî din, heke di paşerojê de di navnîşên bihayan de guhertin çêbibin (xêz têne zêdekirin an jêbirin, biha diguhezin, hwd.), wê hingê bes e ku meriv daxwazên xwe bi kurteya klavyeyê nûve bike. Ctrl+alt+F5 an bişkojka Hemî nûve bikin (Hemû nûve bike) tab Jimare (Rojek).
Pros: Belkî ji hemûyan rêya herî xweş û rehettir e. Bi tabloyên mezin re biaqil dixebite. Dema ku mezinahiya tabloyan diguhere, guherandinên destan hewce nake.
Stewrê: Pêdivî ye ku pêveka Power Query (li Excel 2010-2013) an Excel 2016 were sazkirin. Divê navên stûnên di daneya çavkaniyê de neyên guheztin, wekî din em ê xeletiya "Stûna wusa û wusa nehat dîtin!" dema ku hewl didin ku pirsê nûve bikin.
- Meriv çawa bi karanîna Power Query daneyên ji hemî pelên Excel-ê di peldankek diyar de berhev dike
- Meriv çawa di navbera du navnîşan de di Excel de hevberdanê bibîne
- Yekkirina du navnîşan bêyî dubare