Kusintha mawu ambiri ndi mafomula

Tiyerekeze kuti muli ndi mndandanda womwe, ndi magawo osiyanasiyana a "kuwongoka," deta yoyamba imalembedwa - mwachitsanzo, maadiresi kapena mayina a kampani:

Zikuwonekeratu kuti mzinda womwewo kapena kampaniyo ilipo pano mumitundu yosiyanasiyana ya motley, yomwe, mwachiwonekere, idzabweretsa mavuto ambiri pogwira ntchito ndi matebulo awa m'tsogolomu. Ndipo ngati mukuganiza pang'ono, mungapeze zitsanzo zambiri za ntchito zofanana kuchokera kumadera ena.

Tsopano taganizirani kuti deta yokhotakhota yotereyi imabwera kwa inu nthawi zonse, mwachitsanzo, iyi si nthawi imodzi "kukonza pamanja, kuiwala" nkhani, koma vuto nthawi zonse komanso m'maselo ambiri.

Zoyenera kuchita? Osasintha pamanja mawu okhotakhota nthawi 100500 ndi olondola kudzera pabokosi la "Pezani ndi Kusintha" kapena podina Ctrl+H?

Chinthu choyamba chomwe chimabwera m'maganizo muzochitika zotere ndikusinthiratu anthu ambiri molingana ndi bukhu lolozera losankhidwiratu lofananiza zosankha zolakwika ndi zolondola - monga chonchi:

Tsoka ilo, ndi kufalikira koonekeratu kwa ntchito yotere, Microsoft Excel ilibe njira zosavuta zopangira zothetsera. Choyamba, tiyeni tione momwe tingachitire izi ndi mafomu, popanda kuphatikizira "zankhondo zolemera" mu mawonekedwe a macros mu VBA kapena Power Query.

Mlandu 1. Kusintha kodzaza kochuluka

Tiyeni tiyambe ndi nkhani yosavuta - nthawi yomwe muyenera kusintha mawu okhotakhota akale ndi atsopano. kwathunthu.

Tiyerekeze kuti tili ndi matebulo awiri:

Pachiyambi - mayina oyambirira variegated makampani. Chachiwiri - buku lofotokozera la makalata. Ngati tipeza m'dzina la kampani patebulo loyamba liwu lililonse kuchokera pamzati Kuti mupeze, ndiye muyenera kusintha kwathunthu dzina lopotokali ndi lolondola - kuchokera pamzati M'malo tebulo lachiwiri loyang'ana.

Kuti zitheke:

• Matebulo onsewa amasinthidwa kukhala amphamvu ("anzeru") pogwiritsa ntchito njira yachidule ya kiyibodi Ctrl+T kapena timu Ikani – Table (Lowetsani - Table).
  
• Pa tabu yomwe ikuwoneka Constructor (Kapangidwe) gome loyamba lotchedwa Deta, ndi tebulo lachidziwitso lachiwiri - Subtitutions.

Kuti tifotokoze malingaliro a chilinganizo, tiyeni tipite pang'ono kuchokera patali.

Kutengera kampani yoyamba kuchokera ku cell A2 monga chitsanzo ndikuyiwala kwakanthawi zamakampani ena onse, tiyeni tiyesetse kudziwa kuti ndi njira iti yomwe ili pamndandanda. Kuti mupeze akukumana kumeneko. Kuti muchite izi, sankhani selo iliyonse yopanda kanthu mu gawo laulere la pepala ndikulowetsani ntchitoyo pamenepo KUPEZA (PEZANI):

Ntchitoyi imatsimikizira ngati chingwe chaching'ono chomwe chaperekedwa chikuphatikizidwa (mtsutso woyamba ndi wamtengo wapatali kuchokera pamndandanda Kuti mupeze) m'mawu oyambira (kampani yoyamba kuchokera pa tebulo la data) ndipo iyenera kutulutsa nambala ya ordinal ya munthu yemwe mawuwo adapezeka, kapena cholakwika ngati chingwecho sichinapezeke.

Chinyengo apa ndikuti popeza sitinatchule chimodzi, koma zikhalidwe zingapo ngati mkangano woyamba, ntchitoyi ibwereranso chifukwa osati mtengo umodzi, koma mndandanda wazinthu zitatu. Ngati mulibe mtundu waposachedwa wa Office 3 womwe umathandizira masanjidwe amphamvu, ndiye mutalowa fomula ndikudina Lowani mudzawona mndandanda uwu papepala:

Ngati muli ndi mitundu yam'mbuyomu ya Excel, ndiye mukadina Lowani tidzangowona mtengo woyamba kuchokera pamndandanda wazotsatira, mwachitsanzo, cholakwika #VALUE! (#VALUE!).

Simuyenera kuchita mantha 🙂 M'malo mwake, formula yathu imagwira ntchito ndipo mutha kuwona mndandanda wonse wazotsatira mukasankha zomwe zalowetsedwa mu bar ya formula ndikudina batani. F9(osaiwala kukanikiza Esckubwerera ku formula):

Zotsatira zake zimatanthawuza kuti mu dzina la kampani yokhota (GK Morozko OAO) zamtengo wapatali muzambiri Kuti mupeze anapeza yachiwiri yokha (Morozko), ndi kuyambira pa munthu wachinayi motsatizana.

Tsopano tiyeni tiwonjezere ntchito ku fomula yathu Onani(YANG'ANANI):

Ntchitoyi ili ndi zifukwa zitatu:

1. Mtengo wofunidwa - mutha kugwiritsa ntchito chiwerengero chokwanira chokwanira (chachikulu ndichakuti chimaposa kutalika kwa zolemba zilizonse zomwe zili mugwero)
   
2. Viewed_vector - mndandanda kapena gulu lomwe tikuyang'ana mtengo womwe tikufuna. Nayi ntchito yomwe idayambitsidwa kale KUPEZA, yomwe imabweretsanso mndandanda {#VALUE!:4:#VALUE!}
   
3. Vector_zotsatira - mtundu womwe tikufuna kubweza mtengo ngati mtengo womwe tikufuna ukupezeka mu cell yofananira. Nawa mayina olondola kuchokera pamndandanda M'malo tebulo lathu lolozera.
   

Chinthu chachikulu komanso chosadziwika bwino apa ndikuti ntchitoyo Onani ngati palibe chofanana ndendende, nthawi zonse mumayang'ana mtengo wocheperako (wam'mbuyo).. Chifukwa chake, pofotokoza nambala yayikulu (mwachitsanzo, 9999) ngati mtengo womwe tikufuna, tidzakakamiza. Onani pezani selo yokhala ndi nambala yaying'ono kwambiri (4) pamndandanda wa {#VALUE!:4:#VALUE!} ndi kubweza mtengo wogwirizana nawo kuchokera mu vekitala ya zotsatira, mwachitsanzo, dzina la kampani lolondola kuchokera pamndandanda. M'malo.

Chachiwiri ndi chakuti, mwaukadaulo, chilinganizo chathu ndi chilinganizo chotsatira, chifukwa ntchito KUPEZA imabweretsanso ngati zotsatira osati imodzi, koma mndandanda wamagulu atatu. Koma chifukwa ntchito Onani imathandizira masanjidwe kuchokera m'bokosilo, ndiye kuti sitiyenera kuyika fomulayi ngati njira yachikale - pogwiritsa ntchito njira yachidule ya kiyibodi. Ctrl+kosangalatsa+Lowani. Chosavuta chidzakwanira Lowani.

Ndizomwezo. Ndikukhulupirira kuti mwamva zomveka.

Zimatsalira kusamutsa fomula yomalizidwa kupita ku selo yoyamba B2 ya gawolo amodzi - ndipo ntchito yathu yathetsedwa!

Zachidziwikire, ndi matebulo wamba (osati anzeru), fomula iyi imagwiranso ntchito bwino (musaiwale za kiyi F4 ndi kukonza maulalo oyenera):

Mlandu 2. Kusintha pang'ono pang'ono

Mlanduwu ndi wovuta pang'ono. Apanso tili ndi matebulo awiri "anzeru":

Gome loyamba lomwe lili ndi maadiresi olembedwa mokhotakhota omwe akufunika kuwongoleredwa (ndinayitcha Chidziwitso2). Gome lachiwiri ndi bukhu lofotokozera, malinga ndi momwe muyenera kusinthira pang'ono kachigawo kakang'ono mkati mwa adilesi (ndinatcha tebulo ili). M'malo2).

Kusiyana kwakukulu apa ndikuti muyenera kungosintha kagawo kakang'ono ka deta yoyambirira - mwachitsanzo, adilesi yoyamba ili ndi zolakwika. "St. Petersburg” kumanja "St. Petersburg”, kusiya ma adilesi ena onse (zip code, street, house) monga zilili.

Fomu yomalizidwa idzawoneka chonchi (kuti mumvetsetse bwino, ndidayigawa kukhala mizere ingati yomwe ikugwiritsidwa ntchito alt+Lowani):

Ntchito yayikulu apa ikuchitika ndi ntchito yokhazikika ya Excel WOLEMBEDWA (MALOWA), yomwe ili ndi zotsutsana zitatu:

1. Mawu oyambira - adilesi yoyamba yokhota kuchokera pagawo la Adilesi
2. Zomwe tikuyang'ana - apa timagwiritsa ntchito chinyengo ndi ntchitoyi Onani (YANG'ANANI)kuchokera panjira yapitayi kukokera mtengo kuchokera pamzati Kuti mupeze, yomwe imaphatikizidwa ngati chidutswa mu adilesi yopindika.
3. Zomwe tingalowe m'malo - momwemonso timapeza mtengo wolondola womwe umagwirizana nawo kuchokera pamzati M'malo.

Lowetsani fomula iyi ndi Ctrl+kosangalatsa+Lowani sikufunikanso pano, ngakhale, kwenikweni, ndi njira yotsatsira.

Ndipo zikuwoneka bwino (onani #N/A zolakwika pachithunzi cham'mbuyomu) kuti chilinganizo choterocho, chifukwa cha kukongola kwake konse, chili ndi zovuta zingapo:

• ntchito SUBSTITUTE ndizovuta kwambiri, kotero "Spb" mu mzere wa penultimate sanapezeke mu tebulo losintha. Kuti muthane ndi vutoli, mutha kugwiritsa ntchito ntchitoyi ZAMENIT (M'MALO), kapena bweretsani magome onsewo ku kaundula yemweyo.
• Ngati malembawo ali olondola poyamba kapena mmenemo palibe chidutswa chosintha (mzere womaliza), ndiye kuti fomula yathu imapanga cholakwika. Mphindi iyi ikhoza kuchepetsedwa podutsa ndikusintha zolakwika pogwiritsa ntchito ntchitoyi IFERROR (IFERROR):

  

• Ngati malemba oyambirira ali zidutswa zingapo za bukhuli nthawi imodzi, ndiye chilinganizo chathu chimalowa m'malo omaliza (mu mzere wa 8, Ligovsky «Avenue« zasinthidwa kukhala "pr-t", Koma "S-Pb" on "St. Petersburg” ayinso, chifukwa "S-Pb” ndi apamwamba m'ndandanda). Vutoli litha kuthetsedwa poyambitsanso fomula yathu, koma kale pamndandanda amodzi:

  
  

Osati angwiro komanso ovuta m'malo, koma bwino kwambiri kuposa m'malo omwewo, sichoncho? 🙂

PS

M'nkhani yotsatira, tiwona momwe tingagwiritsire ntchito m'malo mochuluka chotere pogwiritsa ntchito macros ndi Power Query.

• Momwe ntchito ya SUBSTITUTE imagwirira ntchito kusintha mawu
  
• Kupeza Zofananira Zenizeni Pogwiritsa Ntchito Ntchito EXACT
• Kusaka kovutirapo pamilandu ndikusintha m'malo (VLOOKUP)