Fanoloana lahatsoratra marobe miaraka amin'ny formulas

Eritrereto hoe manana lisitra ianao, miaraka amin'ny ambaratonga samihafa amin'ny "fahatsorana", ny angon-drakitra voalohany nosoratana - ohatra, adiresy na anaran'ny orinasa:

Hita mazava fa io tanàna na orinasa io ihany no misy eto amin'ny variana motley, izay mazava ho azy fa hiteraka olana maro rehefa miasa miaraka amin'ireo latabatra ireo amin'ny ho avy. Ary raha mieritreritra kely ianao dia afaka mahita ohatra maro momba ny asa mitovy amin'izany avy amin'ny faritra hafa.

Alao sary an-tsaina izao fa tonga aminao tsy tapaka ny angon-drakitra miolakolaka toy izany, izany hoe tsy tantara iray indray mandeha "manamboatra azy io, manadino azy", fa olana tsy tapaka sy amin'ny sela marobe.

Ny hatao? Aza soloina amin'ny tanana ny lahatsoratra miolakolaka in-100500 amin'ny marina amin'ny alàlan'ny boaty "Find and Solo" na amin'ny fipihana. Ctrl+H?

Ny zavatra voalohany tonga ao an-tsaina amin'ny toe-javatra toy izany dia ny manao fanoloana faobe araka ny boky fanovozan-kevitra efa natambatra amin'ny mifanandrify safidy diso sy marina - toy izao:

Indrisy anefa, miaraka amin'ny fihanaky ny asa toy izany, ny Microsoft Excel dia tsy manana fomba tsotra natsangana hamahana izany. Hanombohana, andeha hojerentsika ny fomba hanaovana izany miaraka amin'ny formulas, tsy misy "artillery mavesatra" amin'ny endrika macro ao amin'ny VBA na Power Query.

Tranga 1. Fanoloana feno betsaka

Andeha isika hanomboka amin'ny tranga somary tsotra – toe-javatra izay ilainao hanoloana ny lahatsoratra miolakolaka taloha amin'ny vaovao. feno.

Andeha atao hoe manana latabatra roa isika:

Ao amin'ny voalohany - ny tany am-boalohany variegated anarana ny orinasa. Ao amin'ny faharoa - boky fanoharana momba ny fifanekena. Raha mahita teny avy amin'ny tsanganana amin'ny anaran'ny orinasa eo amin'ny tabilao voalohany isika Mahita, dia mila soloinao tanteraka io anarana miolakolaka io amin'ny anarana marina – avy amin'ny tsanganana mpisolo toerana latabatra fitadiavana faharoa.

Ho fanamorana:

• Ireo tabilao roa ireo dia avadika ho mavitrika ("smart") amin'ny fampiasana hitsin-dàlana fitendry Ctrl+T na ekipa Ampidiro - Tabilao (Ampidiro - latabatra).
  
• Ao amin'ny tabilao miseho Mpanao (Volavola) tabilao voalohany nomena anarana Data, ary ny latabatra fanondro faharoa - Substitutions.

Mba hanazavana ny lojikan'ny raikipohy dia aleo handeha lavitra.

Maka ny orinasa voalohany amin'ny sela A2 ho ohatra ary manadino vetivety ny orinasa sisa, andeha hojerentsika hoe inona no safidy avy amin'ny tsanganana. Mahita mihaona any. Mba hanaovana izany, mifidiana sela tsy misy na inona na inona ao amin'ny ampahany malalaka amin'ny takelaka ary ampidiro ao ny fiasa MAHITA (MITADY):

Ity fiasa ity dia mamaritra raha toa ka tafiditra ao ny zana-tsipìka nomena (ny hevitra voalohany dia ny sanda rehetra avy amin'ny tsanganana Mahita) ho ao amin'ny lahatsoratra loharano (orinasa voalohany avy amin'ny tabilao data) ary tokony hamoaka na ny laharana ordinal an'ny endri-tsoratra izay nahitana ny lahatsoratra, na ny hadisoana raha tsy hita ny substring.

Ny hafetsena eto dia satria tsy nofaritanay ny iray, fa ny soatoavina maromaro ho toy ny tohan-kevitra voalohany, ity asa ity dia hiverina tsy sanda iray ihany, fa singa 3. Raha tsy manana ny kinova farany an'ny Office 365 ianao izay manohana ny dynamic arrays, dia rehefa avy niditra ity formula ity ianao ary tsindrio ny Midira ho hitanao eo amin'ny takelaka ity array ity:

Raha manana dikan-teny Excel teo aloha ianao, dia rehefa avy manindry Midira Ny sanda voalohany ihany no ho hitantsika avy amin'ny laharan'ny vokatra, izany hoe fahadisoana #VALUE! (#VALUE!).

Tsy tokony hatahotra ianao 🙂 Raha ny marina, miasa ny formulay ary mbola azonao jerena daholo ny valin'ny valiny raha misafidy ny fiasa tafiditra ao amin'ny bara formula ianao ary manindry ny fanalahidy F9(aza adino ny manindry Eschiverina amin'ny formula):

Ny vokatra azo dia midika fa ao amin'ny anaran'ny orinasa miolakolaka tany am-boalohany (GK Morozko OAO) ny sanda rehetra ao anaty tsanganana Mahita ny faharoa ihany no hita (Morozko), ary manomboka amin'ny tarehintsoratra faha-4 misesy.

Andeha isika hanampy asa amin'ny formulay VIEW(HITADY):

Ity asa ity dia manana hevitra telo:

1. Sanda irina - azonao atao ny mampiasa isa ampy tsara (ny tena zava-dehibe dia mihoatra ny halavan'ny lahatsoratra rehetra ao amin'ny angon-drakitra loharano)
   
2. Viewed_vector – ny laharan-tseza na ny laharan-tseza izay itadiavana ny sanda irina. Ity ny asa nampidirina teo aloha MAHITA, izay mamerina laharana {#VALUE!:4:#VALUE!}
   
3. Vector_vokatra – ny elanelana izay tiantsika hamerenana ny sandany raha hita ao amin'ny sela mifanandrify ny sanda irina. Ireto ny anarana marina avy amin'ny tsanganana mpisolo toerana latabatra fanondroanay.
   

Ny endri-javatra lehibe sy tsy miharihary eto dia ny fiasa VIEW raha tsy misy mifanandrify tsara dia tadiavo foana ny sanda kely indrindra (teo aloha) akaiky indrindra. Noho izany, amin'ny famaritana isa lehibe (ohatra, 9999) ho sanda irina, dia hanery VIEW tadiavo ny sela misy isa kely indrindra (4) akaiky indrindra ao amin'ny laharan-kira {#VALUE!:4:#VALUE!} ary avereno ny sanda mifanaraka amin'izany avy amin'ny virtoaly valiny, izany hoe ny anaran'ny orinasa marina avy amin'ny tsanganana. mpisolo toerana.

Ny nuance faharoa dia ny hoe, ara-teknika, ny formulay dia formula array, satria miasa MAHITA dia miverina ho vokatra tsy iray, fa array misy soatoavina telo. Fa hatramin'ny asa VIEW manohana array ivelan'ny boaty, dia tsy mila miditra an'io formula io ho toy ny formula array mahazatra isika - mampiasa hitsin-dàlana fitendry Ctrl+fanovana+Midira. Ny tsotra dia ampy Midira.

Izay ihany. Manantena fa azonao ny lojika.

Dia mijanona ny mamindra ny raikipohy vita amin'ny sela B2 voalohany amin'ny tsanganana Tafatoetra – ary voavaha ny asantsika!

Mazava ho azy, miaraka amin'ny latabatra mahazatra (tsy marani-tsaina), ity raikipohy ity dia miasa tsara (aza adino fotsiny ny fanalahidy F4 ary manamboatra ireo rohy mifandraika):

Tranga 2. Fanoloana ampahany betsaka

Ity tranga ity dia somary sarotra kokoa. Manana tabilao "smart" roa indray isika:

Ny latabatra voalohany misy adiresy voasoratra miolakolaka mila ahitsy (nantsoiko hoe Takelaka data2). Ny latabatra faharoa dia boky fanondroana, araka izay ilainao ny fanoloana ampahany amin'ny sobika ao anaty adiresy (nantsoiko ity latabatra ity Fanoloana2).

Ny fahasamihafana fototra eto dia ny tsy maintsy soloinao ampahany amin'ny angon-drakitra tany am-boalohany - ohatra, misy diso ny adiresy voalohany. “St. Petersburg” etsy ankavanana “St. Petersburg”, mamela ny sisa amin'ny adiresy (kaody paositra, arabe, trano) ho toy izany.

Ny formula efa vita dia ho toy izao (ho fanamorana ny fahitana, nozaraiko ho andalana firy ny fampiasana azy Alt+Midira):

Ny asa lehibe eto dia vita amin'ny alàlan'ny asa soratra Excel mahazatra mpisolo toerana (MAMPIANA), izay manana hevitra 3:

1. Loharano lahatsoratra – ny adiresy miolakolaka voalohany avy amin'ny tsanganana Adiresy
2. Ny zavatra tadiavintsika - eto dia mampiasa ny fika miaraka amin'ny fiasa VIEW (HITADY)avy amin'ny fomba teo aloha hisintona ny sanda avy amin'ny tsanganana Mahita, izay ampidirina ho sombiny amin'ny adiresy miolikolika.
3. Inona no hosoloina - toy izany koa no ahitantsika ny sanda mifanaraka amin'izany avy amin'ny tsanganana mpisolo toerana.

Ampidiro ity formula ity miaraka amin'ny Ctrl+fanovana+Midira Tsy ilaina eto koa, na dia, raha ny marina, formula array.

Ary hita mazava (jereo ny #N/A lesoka eo amin'ny sary teo aloha) fa ny raikipohy toy izany, noho ny hatsarany rehetra, dia manana lesoka roa:

• asa SUBSTITUTE dia saro-pady, ka tsy hita tao amin'ny tabilao fanoloana ny "Spb" ao amin'ny andalana penultimate. Mba hamahana ity olana ity dia azonao atao ny mampiasa ny fiasa ZAMENIT (SOLO), na ento mialoha amin'ny rejisitra iray ny latabatra roa.
• Raha marina na ao anatiny ilay lahatsoratra tsy misy sombiny ho soloina (andalana farany), avy eo ny formulay dia mamoaka hadisoana. Ity fotoana ity dia azo atao neutralize amin'ny alàlan'ny fisakanana sy fanoloana ny lesoka amin'ny fampiasana ny fiasa IFERROR (IFERROR):

  

• Raha misy ny lahatsoratra tany am-boalohany sombiny maromaro avy amin'ny lahatahiry indray mandeha, dia misolo ny farany (ao amin'ny andalana faha-8, Ligovsky «Avenue« niova ho "pr-t", Nefa "S-Pb" on “St. Petersburg” tsy intsony, satria “S-Pb” dia ambony kokoa ao amin'ny lahatahiry). Ity olana ity dia azo vahana amin'ny alàlan'ny fampandehanana indray ny raikipohy manokana, fa efa eo amin'ny tsanganana Tafatoetra:

  
  

Tsy tonga lafatra sy sarotra amin'ny toerana, fa tsara kokoa noho ny fanoloana manual mitovy, sa tsy izany? 🙂

PS

Ao amin'ny lahatsoratra manaraka dia hojerentsika ny fomba fampiharana ny fanoloana betsaka toy izany amin'ny fampiasana macro sy Power Query.

• Ahoana ny fiasan'ny SUBSTITUTE hanoloana lahatsoratra
  
• Mitady lahatsoratra mifanandrify amin'ny fampiasana ny EXACT Function
• Fikarohana sy fanoloana saro-pady (VLOOKUP saro-pady)