د فورمولونو سره لوی متن بدلول

فرض کړئ چې تاسو یو لیست لرئ په کوم کې چې د "مستقیمیت" مختلف درجې سره لومړني معلومات لیکل شوي - د مثال په توګه پتې یا د شرکت نومونه:

دا په ښکاره ډول لیدل کیږي چې ورته ښار یا شرکت دلته په موټلي ډولونو کې شتون لري، چې په ښکاره ډول به په راتلونکي کې د دې میزونو سره کار کولو کې ډیرې ستونزې رامینځته کړي. او که تاسو لږ فکر وکړئ، تاسو کولی شئ د نورو برخو څخه د ورته کارونو ډیری مثالونه ومومئ.

اوس تصور وکړئ چې دا ډول کرغېړن معلومات تاسو ته په منظمه توګه راځي، یعنې دا یو ځل "په لاسي ډول یې حل کړئ، دا هیر کړئ" کیسه نه ده، مګر په منظمه توګه او په ډیری حجرو کې یوه ستونزه ده.

چې څه کول پکار دي؟ په لاسي ډول د "موندل او ځای په ځای کولو" بکس له لارې یا د کلیک کولو له لارې 100500 ځله کرغېړن متن د سم متن سره مه بدلوئ. Ctrl+H?

لومړی شی چې په داسې حالت کې ذهن ته راځي د غلط او سم انتخابونو سره د مخکینۍ تالیف شوي حوالې کتاب سره سم د ډله ایز بدیل رامینځته کول دي - لکه دا:

له بده مرغه، د داسې یوې دندې د څرګند پراخوالي سره، مایکروسافټ ایکسل د دې حل کولو لپاره ساده جوړ شوي میتودونه نلري. د پیل کولو لپاره، راځئ چې معلومه کړو چې دا څنګه د فارمولونو سره ترسره کړئ، پرته له دې چې په VBA یا پاور پوښتنې کې د میکرو په بڼه "درانه توپخانه" شامل کړئ.

قضیه 1. ډیری بشپړ ځای په ځای کول

راځئ چې د یوې نسبتا ساده قضیې سره پیل وکړو - یو داسې وضعیت چیرې چې تاسو اړتیا لرئ چې زاړه متناسب متن له نوي سره بدل کړئ. په بشپړه توګه.

راځئ چې ووایو موږ دوه میزونه لرو:

په لومړي کې - د شرکتونو اصلي متنوع نومونه. په دویمه برخه کې - د لیکونو د حوالې کتاب. که موږ په لومړي جدول کې د شرکت په نوم کې د کالم څخه کومه کلمه ومومئ پیدا کول، بیا تاسو اړتیا لرئ چې دا کرغیړن نوم په بشپړ ډول د سم نوم سره بدل کړئ - له کالم څخه متبادل د دویم کتنې میز.

د اسانتیا لپاره:

• دواړه میزونه د کیبورډ شارټ کټ په کارولو سره متحرک ("سمارټ") ته بدل شوي Ctrl+T یا ټیم داخل کړئ - میز (داخل کړئ - جدول).
  
• په هغه ټب کې چې څرګندیږي جوړونکی (ډیزاین) لومړی جدول نومول شوی د معلوماتو د، او د دوهم حواله جدول - بدلونه.

د فورمول منطق تشریح کولو لپاره، راځئ چې یو څه لرې لاړ شو.

د سیل A2 څخه د لومړي شرکت اخیستل د مثال په توګه او په لنډمهاله توګه د پاتې شرکتونو په اړه هیر کول ، راځئ هڅه وکړو چې دا معلومه کړو چې کوم انتخاب له کالم څخه پیدا کول هلته ملاقات کوي. د دې کولو لپاره ، د شیټ په وړیا برخه کې کوم خالي حجره غوره کړئ او هلته فنکشن دننه کړئ پیدا کول (موندل):

دا فنکشن مشخص کوي چې ایا ورکړل شوی فرعي سټینګ پکې شامل دی (لومړی دلیل د کالم ټول ارزښتونه دي پیدا کول) د سرچینې متن کې (د ډیټا جدول څخه لومړی شرکت) او باید یا د هغه کرکټر منظم شمیره تولید کړي چې له هغې څخه متن وموندل شو ، یا یوه تېروتنه که فرعي سټرینګ ونه موندل شو.

دلته چال دا دی چې موږ د لومړي دلیل په توګه یو نه، مګر څو ارزښتونه مشخص کړل، دا فنکشن به د پایلې په توګه یو ارزښت نه وي، مګر د 3 عناصرو لړۍ. که تاسو د Office 365 وروستۍ نسخه نلرئ چې د متحرک صفونو ملاتړ کوي، نو بیا وروسته دا فورمول داخل کړئ او کلیک وکړئ وليکئ تاسو به دا صف په پاڼه کې وګورئ:

که تاسو د Excel پخوانۍ نسخې لرئ، نو بیا وروسته کلیک وکړئ وليکئ موږ به یوازې د پایلې سرې څخه لومړی ارزښت وګورو، د بیلګې په توګه غلطی #VALUE! (#VALUE!).

تاسو باید ویره ونلرئ 🙂 په حقیقت کې، زموږ فورمول کار کوي او تاسو لاهم کولی شئ د پایلو ټوله لړۍ وګورئ که تاسو د فارمول بار کې داخل شوی فنکشن غوره کړئ او کیلي فشار ورکړئ F9(یوازې فشارول مه هیروئ Escبیرته فارمول ته لاړ شه):

د پایلې پایلې د دې معنی لري چې د اصلي کرکیډ شرکت نوم کې (GK Morozko OAO) په یوه کالم کې د ټولو ارزښتونو څخه پیدا کول یوازې دویمه وموندل شوه (مورزکو)، او په پرله پسې ډول د څلورم کرکټر څخه پیل کیږي.

اوس راځئ چې زموږ فورمول ته یو فنکشن اضافه کړو VIEW(لټون):

دا فنکشن درې دلیلونه لري:

1. مطلوب ارزښت - تاسو کولی شئ په کافي اندازه لوی شمیر وکاروئ (اصلي شی دا دی چې دا د سرچینې ډیټا کې د هر متن اوږدوالی څخه ډیر دی)
   
2. لیدل شوی_ویکتور - هغه حد یا سرې چیرې چې موږ د مطلوب ارزښت په لټه کې یو. دلته مخکې معرفي شوی فعالیت دی پیدا کول، کوم چې یو سري راګرځوي {#VALUE!:4:#VALUE!}
   
3. ویکتور_پايلې - هغه سلسله چې له هغه څخه موږ غواړو ارزښت بیرته راګرځوو که چیرې مطلوب ارزښت په اړونده حجره کې وموندل شي. دلته د کالم څخه سم نومونه دي متبادل زموږ د حوالې جدول.
   

دلته اصلي او غیر څرګند ځانګړتیا دا ده چې فعالیت VIEW که چیرې دقیق میچ شتون ونلري، تل د نږدې کوچني (مخکیني) ارزښت په لټه کې وي. له همدې امله، د مطلوب ارزښت په توګه د هر لوی شمیر (د بیلګې په توګه، 9999) مشخص کولو سره، موږ به مجبور کړو VIEW په صف کې تر ټولو نږدې کوچنۍ شمېره (4) سره حجره ومومئ {#VALUE!:4:#VALUE!} او د پایلې ویکتور څخه ورته ارزښت بیرته راګرځئ، د بیلګې په توګه د کالم څخه د شرکت نوم سم کړئ متبادل.

دوهم اهمیت دا دی چې په تخنیکي توګه زموږ فورمول د صف فورمول دی، ځکه چې فعالیت پیدا کول د یوې پایلې په توګه بیرته راګرځي، مګر د دریو ارزښتونو لړۍ. مګر د فعالیت راهیسې VIEW د بکس څخه بهر د صفونو ملاتړ کوي، نو بیا موږ اړتیا نلرو دا فورمول د کلاسیک سري فارمول په توګه داخل کړو - د کیبورډ شارټ کټ په کارولو سره Ctrl+Shift+وليکئ. یو ساده به کافي وي وليکئ.

بس نور څه نه. هیله ده منطق مو پیدا کړی وي.

دا د کالم لومړۍ حجرې B2 ته د بشپړ شوي فارمول لیږدولو لپاره پاتې دي ثابته - او زموږ دنده حل شوه!

البته، د عادي (سمارټ نه) جدولونو سره، دا فورمول هم ښه کار کوي (یوازې د کیلي په اړه مه هېروئ F4 او د اړوندو لینکونو اصلاح کول):

قضیه 2. په لویه کچه جزوی بدیل

دا قضیه یو څه پیچلې ده. بیا موږ دوه "سمارټ" میزونه لرو:

لومړی جدول د کره لیکل شوي ادرسونو سره چې سمولو ته اړتیا لري (ما ورته وویل ډاټا 2). دوهم جدول د حوالې کتاب دی ، د کوم له مخې چې تاسو اړتیا لرئ د پتې دننه د فرعي سټینګ جزوی ځای په ځای کړئ (ما دې جدول ته بللی بدیلونه 2).

دلته بنسټیز توپیر دا دی چې تاسو اړتیا لرئ د اصلي معلوماتو یوازې یوه ټوټه بدله کړئ - د بیلګې په توګه، لومړی پته غلطه ده "سینټ. پیټرزبورګ" ښي لور ته "سینټ. پیټرزبورګ"، پاتې پته پریږدئ (زپ کوډ، کوڅه، کور) لکه څنګه چې ده.

بشپړ شوی فورمول به داسې ښکاري (د آسانتیا لپاره، ما دا په څو لینونو ویشلی alt+وليکئ):

دلته اصلي کار د معیاري Excel متن فنکشن لخوا ترسره کیږي ضمیمه (موضوع)، کوم چې 3 دلیلونه لري:

1. د سرچینې متن - د پتې کالم څخه لومړی کرغیړن پته
2. هغه څه چې موږ یې په لټه کې یو - دلته موږ د فنکشن سره چال کاروو VIEW (لټون)له پخوانۍ لارې څخه د کالم څخه ارزښت راوباسي پیدا کول، کوم چې په منحل شوي پته کې د ټوټې په توګه شامل شوی.
3. د څه شی سره بدل کړئ - په ورته ډول موږ د کالم څخه ورته سم ارزښت پیدا کوو متبادل.

دا فورمول دننه کړئ Ctrl+Shift+وليکئ دلته هم اړتیا نشته، که څه هم دا په حقیقت کې د صف فورمول دی.

او دا په واضح ډول لیدل کیږي (په مخکني عکس کې #N/A تېروتنې وګورئ) چې دا ډول فورمول، د دې ټول ښکلا لپاره، یو څو نیمګړتیاوې لري:

• دنده SUBSTITUTE د قضیې حساس دی، نو په پای کې "Spb" د بدیل جدول کې ونه موندل شو. د دې ستونزې د حل لپاره، تاسو کولی شئ یا هم د فنکشن څخه کار واخلئ ZAMENIT (بدلول)، یا په ابتدايي توګه دواړه میزونه ورته راجستر ته راوړي.
• که متن په پیل کې سم وي یا په هغه کې د بدلولو لپاره هیڅ ټوټه نشته (وروستی کرښه)، بیا زموږ فورمول یوه تېروتنه کوي. دا شیبه د فنکشن په کارولو سره د غلطو مداخلو او ځای په ځای کولو سره بې طرفه کیدی شي IFERROR (IFERROR):

  

• که اصلي متن ولري په یوځل کې د لارښود څخه څو ټوټې، بیا زموږ فورمول یوازې وروستی یو ځای نیسي (په اتم کرښه کې، Ligovsky «ایونیو« ته بدل شو "پي آرټي"، خو "S-Pb" on "سینټ. پیټرزبورګ" نور نه، ځکه "S-Pb"په لارښود کې لوړ دی). دا ستونزه زموږ د خپل فارمول په بیا چلولو سره حل کیدی شي ، مګر دمخه د کالم سره ثابته:

  
  

په ځایونو کې کامل او پیچلي ندي ، مګر د ورته لارښود بدیل څخه خورا ښه ، سمه ده؟ 🙂

PS

په راتلونکې مقاله کې، موږ به معلومه کړو چې څنګه د میکرو او پاور پوښتنې په کارولو سره دا ډول لوی بدیل پلي کول.

• د SUBSTITUTE فعالیت څنګه د متن بدلولو لپاره کار کوي
  
• د دقیق فنکشن په کارولو سره دقیق متن میچونه موندل
• د قضیې حساس لټون او بدیل (د قضیې حساس VLOOKUP)