Bifikirin ku we navnîşek heye ku tê de, bi dereceyên cihêreng ên "rasterast", daneyên destpêkê têne nivîsandin - ji bo nimûne, navnîşan an navên pargîdaniyê:
 |  |
Eşkere tê dîtin ku heman bajar an pargîdanî li vir di guhertoyên cûrbecûr de heye, ku, eşkere, dê di pêşerojê de dema ku bi van tabloyan re bixebitin gelek pirsgirêkan çêbike. Û heger hûn piçek bifikirin, hûn dikarin gelek nimûneyên karên wekhev ên ji deverên din bibînin.
Naha bifikire ku daneyên wusa xelet bi rêkûpêk ji we re têne, ango ev ne çîrokek yek carî "bi destan rast bike, ji bîr bike" ye, lê pirsgirêkek bi rêkûpêk û di hejmareke mezin a hucreyan de ye.
Çi bikim? Bi navgîniya qutika "Bibîne û Biguherîne" an bi tikandina nivîsa xelet 100500 carî bi ya rast biguhezîne. Ctrl+H?
Yekem tiştê ku di rewşek weha de tê bîra me ev e ku meriv li gorî pirtûkek referansê ya pêş-berhevkirî ya vebijarkên nerast û rast - bi vî rengî veguherînek girseyî pêk bîne:
Mixabin, digel berbelavbûna eşkere ya peywirek weha, Microsoft Excel ji bo çareserkirina wê xwedan rêbazên hêsan ên çêkirî nîne. Ji bo destpêkê, em fêr bibin ka meriv çawa vê yekê bi formulan dike, bêyî tevlêbûna "topên giran" di forma makroyan de di VBA an Power Query de.
Doza 1. Guhertina tevahî ya Bulk
Werin em bi dozek nisbeten hêsan dest pê bikin - rewşek ku hûn hewce ne ku nivîsa kevn a kevn bi ya nû veguherînin. bi temamî.
Em bibêjin du tabloyên me hene:
Di ya yekem de - navên cihêreng ên orîjînal ên pargîdaniyan. Di ya duyemîn de - pirtûka referansa danûstandinê. Ger em di tabloya yekem de di navê pargîdaniyê de peyvek ji stûnê bibînin Ji bo dîtina, wê hingê hûn hewce ne ku bi tevahî vê navê xelet bi ya rast biguhezînin - ji stûnê Diberdasitin tabloya lêgerîna duyemîn.
Ji bo rehetiyê:
- Her du tablo bi karanîna kurtenivîsek klavyeyê veguhezînin dînamîk ("aqilmend"). Ctrl+T an tîm Têxe - Tablo (Têxe - Tablo).
- Li ser tabloya ku xuya dike Kêrker (Mînakkirin) tabloya yekem bi navê Jimare, û tabloya referansê ya duyemîn - Substitutions.
Ji bo ravekirina mantiqa formulê, em hinekî ji dûr ve biçin.
Pargîdaniya yekem ji hucreya A2 wekî mînak digirin û bi demkî pargîdaniyên mayî ji bîr dikin, em hewl bidin ku diyar bikin ka kîjan vebijark ji stûnê Ji bo dîtina li wir dicive. Ji bo vê yekê, di beşa belaş a pelê de her şaneyek vala hilbijêrin û li wir fonksiyonê têkevin TO FIND (DÎTIN):
Vê fonksiyonê diyar dike ka binerxeya hatî dayîn tê de heye (argumana yekem hemî nirxên ji stûnê ne Ji bo dîtina) di metna çavkaniyê de (şirketa yekem a ji tabloya daneyê) û divê an jimara rêzî ya karaktera ku nivîs jê hatî dîtin derxe, an jî xeletiyek heke binesaz nehatibe dîtin derxe.
Li vir hîle ev e ku ji ber ku me ne yek, lê çend nirx wekî argumana yekem diyar kir, dê ev fonksiyon jî di encamê de ne yek nirx, lê komek ji 3 hêmanan vegere. Heke we guhertoya herî dawî ya Office 365-ê ya ku rêzikên dînamîkî piştgirî dike tune ye, wê hingê piştî ku hûn vê formula têkevin û li ser bitikînin. Derbasbûn hûn ê vê rêzê rast li ser pelê bibînin:
Ger we guhertoyên berê yên Excel hene, wê hingê piştî ku li ser bikirtînin Derbasbûn em ê tenê nirxa yekem ji rêzika encamê bibînin, ango xeletiya #VALUE! (#GIRANÎ!).
Pêdivî ye ku hûn netirsin 🙂 Bi rastî, formula me dixebite û heke hûn fonksiyona têketinê di barika formula de hilbijêrin û bişkojkê bikirtînin hûn hîn jî dikarin tevahiya rêza encaman bibînin. F9(tenê ji bîr nekin ku çap bikin Escvegere formulê):
Rêzeya encam a encam tê vê wateyê ku di navê pargîdaniya xapînok a orjînal de ye (GK Morozko OAO) ji hemî nirxan di stûnekê de Ji bo dîtina tenê ya duyemîn dît (Morozko), û ji karaktera 4emîn li pey hev dest pê dike.
Naha em fonksiyonek li formula xwe zêde bikin DÎTINÎ(PEYDA KIRIN):
Vê fonksiyonê sê arguman hene:
- Nirxa xwestî - hûn dikarin hejmareke têra xwe mezin bikar bînin (ya sereke ev e ku ew ji dirêjahiya her nivîsê di daneya çavkaniyê de derbas dibe)
- Viewed_vector - rêze an rêzika ku em li nirxa xwestinê digerin. Li vir fonksiyona ku berê hatî destnîşan kirin e TO FIND, ku arrayek vedigerîne {#VALUE!:4:#VALUE!}
- Vector_results - Rêjeya ku em dixwazin nirxê jê vegerînin heke nirxa xwestî di hucreya têkildar de were dîtin. Li vir navên rast ji stûnê hene Diberdasitin tabloya referansa me.
Taybetmendiya sereke û ne eşkere li vir ew fonksiyon e DÎTINÎ ger lihevhatina rast tune be, her gav li nirxa herî piçûk (berî) ya herî nêzîk digere. Ji ber vê yekê, bi destnîşankirina hejmareke giran (mînak, 9999) wekî nirxa xwestî, em ê zorê bikin. DÎTINÎ di rêzika {#VALUE!:4:#VALUE!} de şaneya bi hejmara herî biçûk (4) ya herî nêzik bibînin û ji vektora encamê nirxa têkildar vegerînin, ango navê pargîdaniyê ji stûnê rast bikin. Diberdasitin.
Nîşaneya duyemîn ev e ku, ji hêla teknîkî ve, formula me formula array e, ji ber ku fonksiyonê ye TO FIND ne yek, lê komek ji sê nirxan wekî encam vedigere. Lê ji ber ku fonksiyonê DÎTINÎ rêzikên ji qutîkê piştgirî dike, wê hingê ne hewce ye ku em vê formulê wekî formula arrayek klasîk têkevin - bi karanîna kurtenivîsek klavyeyê Ctrl+Tarloqî+Derbasbûn. Yek sade dê têrê bike Derbasbûn.
Navê pêger. Hêvî dikim ku hûn mantiqê bistînin.
Ew dimîne ku formula qediyayî veguhezîne hucreya yekem B2 ya stûnê Fixed - û karê me çareser bû!
Bê guman, bi tabloyên asayî (ne jîr) re, ev formula di heman demê de pir baş dixebite (tenê mifteyê ji bîr nekin F4 û rastkirina girêdanên têkildar):
Case 2. Guhertina qismî Bulk
Ev doz hinekî dijwartir e. Dîsa me du tabloyên "aqilmend" hene:
Tabloya yekem a bi navnîşanên bi xeletî hatine nivîsandin ku divê were sererast kirin (min jê re gazî kir Daneyên2). Tabloya duyemîn pirtûkek referansê ye, ku li gorî wê hûn hewce ne ku di hundurê navnîşanê de guheztinek qismî çêbikin (min gazî vê tabloyê kir Cîgir2).
Cûdahiya bingehîn li vir ev e ku hûn hewce ne ku tenê perçeyek daneyên orîjînal biguhezînin - mînakî, navnîşana yekem xelet e. “St. Petersburg” li rastê “St. Petersburg”, navnîşana mayî (zip kod, kolan, xanî) wekî xwe hiştin.
Formula qediyayî dê bi vî rengî xuya bike (ji bo hêsan têgihîştinê, min ew li çend rêzan dabeş kir ku bikar tînin alt+Derbasbûn):
Karê sereke li vir ji hêla fonksiyona nivîsê ya standard Excel ve tê kirin DIBERDASITIN (DIBERDASITIN), ku 3 arguman hene:
- Nivîsara çavkaniyê - yekem navnîşana xelet a ji stûna Navnîşan
- Tiştê ku em lê digerin - li vir em fêlbazî bi fonksiyonê bikar tînin DÎTINÎ (PEYDA KIRIN)ji riya berê ve nirxê ji stûnê vekişîne Ji bo dîtina, ku wekî parçeyek di navnîşana xêzkirî de tê de ye.
- Tiştê ku were guheztin - bi heman rengî em nirxa rast a ku jê re têkildar e ji stûnê dibînin Diberdasitin.
Bi vê formula re têkevin Ctrl+Tarloqî+Derbasbûn Li vir jî ne hewce ye, her çend ew, bi rastî, formula array e.
Û bi zelalî tê dîtin (di wêneya berê de xeletiyên #N/A binêre) ku formulayek weha, digel hemî xweşikbûna xwe, çend kêmasiyên xwe hene:
- Karî SUBSTITUTE hesas e, ji ber vê yekê "Spb" di rêza paşîn de di tabloya veguheztinê de nehat dîtin. Ji bo çareserkirina vê pirsgirêkê, hûn dikarin fonksiyonê bikar bînin ZAMENIT (DIBERDAXISTIN), an jî pêşî her du tabloyan bînin heman qeydê.
- Ger nivîs di destpêkê de rast an jî tê de be tu parçeyek ku li şûna wê derê (xêza paşîn), wê hingê formula me xeletiyek derdixe. Vê gavê bi karanîna fonksiyonê ve bi navgînkirin û şûna xeletiyan ve were bêbandor kirin IFERROR (IFERROR):
- Ger nivîsa orîjînal hebe çend perçe ji pelrêça di carekê de, wê hingê formula me tenê ya paşîn diguhezîne (di rêza 8-an de, Ligovsky «Rêya fireh û nexkiri« guherandin "pr-t", Lê Lê "S-Pb" on “St. Petersburg” nema, ji ber "S-Pb” di pelrêça bilind de ye). Ev pirsgirêk dikare bi ji nû ve meşandina formula xwe, lê jixwe li ser stûnê were çareser kirin Fixed:
Li cîhan ne bêkêmasî û giran e, lê ji heman veguheztina destan pir çêtir e, rast? 🙂
PS
Di gotara din de, em ê fêr bibin ka meriv çawa bi karanîna makro û Power Query veguheztinek wusa mezin pêk tîne.
- Çawa fonksiyona SUBSTITUTE dixebite ku li şûna nivîsê bigire
- Bi karanîna Fonksîyona TEMAM Bikaranîna Maçên Nivîsarên Rastîn Dîtina
- Lêgerîna hesas a dozê û cîgir (VLOOKUP)