بلڪ ٽيڪسٽ متبادل فارمولن سان

فرض ڪريو توھان وٽ ھڪڙي فهرست آھي جنھن ۾، مختلف درجي جي "سڌي طرح" سان، شروعاتي ڊيٽا لکيل آھي - مثال طور، ايڊريس يا ڪمپني جا نالا:

اهو واضح طور تي ڏسڻ ۾ اچي ٿو ته هڪ ئي شهر يا ڪمپني هتي موجود آهي motley variants، جيڪو، ظاهر آهي، جڏهن مستقبل ۾ انهن جدولن سان ڪم ڪندي تمام گهڻا مسئلا پيدا ڪندو. ۽ جيڪڏهن توهان ٿورو سوچيو، توهان ٻين علائقن مان ساڳئي ڪمن جا ڪيترائي مثال ڳولي سگهو ٿا.

هاڻي تصور ڪريو ته اهڙي ڪڙي ڊيٽا توهان وٽ باقاعدگي سان ايندي آهي، يعني هي هڪ ڀيرو نه آهي ”دستي طور تي ان کي درست ڪريو، ان کي وساريو“ ڪهاڻي، پر هڪ مسئلو آهي باقاعده بنياد تي ۽ سيلز جي وڏي تعداد ۾.

ڇا ڪجي؟ دستي طور تي 100500 ڀيرا ٽوڙيل متن کي صحيح سان تبديل نه ڪريو "ڳولڻ ۽ تبديل ڪريو" باڪس ذريعي يا ڪلڪ ڪري. Ctrl+H?

پهرين شيء جيڪا ذهن ۾ اچي ٿي اهڙي صورتحال ۾، غلط ۽ صحيح اختيارن جي ميلاپ جي اڳوڻي مرتب ڪيل حوالن جي ڪتاب جي مطابق وڏي پئماني تي متبادل ٺاهڻ - هن طرح:

بدقسمتي سان، اهڙي ڪم جي واضح اڳڀرائي سان، Microsoft Excel وٽ ان کي حل ڪرڻ لاء سادي ٺهيل طريقا نه آهن. شروع ڪرڻ لاءِ، اچو ته اهو سمجهون ته فارمولين سان اهو ڪيئن ڪجي، سواءِ وي بي اي يا پاور سوال ۾ ميڪرو جي صورت ۾ ”هيوي آرٽلري“ کي شامل ڪرڻ جي.

ڪيس 1. بلڪ مڪمل متبادل

اچو ته هڪ نسبتاً سادو ڪيس سان شروع ڪريون - هڪ اهڙي صورتحال جتي توهان کي پراڻي ٽڙيل متن کي نئين سان تبديل ڪرڻ جي ضرورت آهي. ناه.

اچو ته اسان وٽ ٻه ٽيبل آهن:

پهرين ۾ - ڪمپنين جا اصل مختلف نالا. ٻئي ۾ - correspondence جو هڪ حوالو ڪتاب. جيڪڏهن اسان پهرين ٽيبل ۾ ڪمپني جي نالي ۾ ڳوليندا آهيون ڪالمن مان ڪو به لفظ لهڻ، پوءِ توھان کي ضرورت آھي مڪمل طور تي ھن بگڙيل نالي کي صحيح ھڪڙي سان تبديل ڪريو - ڪالمن مان سقراط ٻيو ڳولڻ جي ٽيبل.

سهولت لاءِ:

• ٻئي ٽيبل کي ڪيبورڊ شارٽ ڪٽ استعمال ڪندي متحرڪ ("سمارٽ") ۾ تبديل ڪيو ويو آهي Ctrl+T يا ٽيم داخل ڪريو - ٽيبل (داخل ڪريو - ٽيبل).
  
• ٽيب تي جيڪو ظاهر ٿئي ٿو تعمير ڪندڙ (خاڪو) پهريون نالو ٽيبل ڊيٽا، ۽ ٻيو حوالو جدول - substitutions.

فارمولا جي منطق جي وضاحت ڪرڻ لاء، اچو ته ٿورو پري کان وڃو.

سيل 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!} ۽ واپس ڪريو ملندڙ قدر نتيجن جي ویکٹر مان، يعني ڪالمن مان صحيح ڪمپني جو نالو سقراط.

ٻيو nuance اهو آهي ته، ٽيڪنيڪل طور تي، اسان جو فارمولا هڪ صف فارمولا آهي، ڇاڪاڻ ته فنڪشن لهڻ نتيجن جي طور تي واپسي هڪ نه، پر ٽن قدرن جو هڪ صف. پر فنڪشن کان وٺي VIEW دٻي جي ٻاهران صفن کي سپورٽ ڪري ٿو، پوءِ اسان کي هن فارمولا کي کلاسڪ اري فارمولا طور داخل ڪرڻ جي ضرورت ناهي - ڪي بورڊ شارٽ ڪٽ استعمال ڪندي Ctrl+شفٽ+شامل ڪريو. ھڪڙو سادو ڪافي ٿيندو شامل ڪريو.

اهو ئي سڀ ڪجهه آهي. اميد ته توهان منطق حاصل ڪندا.

اهو ختم ٿيل فارمولا کي ڪالمن جي پهرين سيل B2 ڏانهن منتقل ڪرڻ لاء رهي ٿو مقرر - ۽ اسان جو ڪم حل ٿي ويو آهي!

يقينا، عام (سمارٽ نه) جدولن سان، هي فارمولا پڻ تمام سٺو ڪم ڪري ٿو (صرف اهم جي باري ۾ نه وساريو. F4 ۽ لاڳاپيل لنڪ کي درست ڪرڻ):

ڪيس 2. بلڪ جزوي متبادل

هي ڪيس ٿورڙو پيچيدو آهي. ٻيهر اسان وٽ ٻه "سمارٽ" ٽيبل آهن:

پهرئين جدول جنهن ۾ لکيل پتا آهن جن کي درست ڪرڻ جي ضرورت آهي (مون ان کي سڏيو ڊيٽا 2). ٻيو ٽيبل هڪ حوالو ڪتاب آهي، جنهن جي مطابق توهان کي پتي جي اندر هڪ ذيلي اسٽرنگ جي جزوي متبادل ڪرڻ جي ضرورت آهي (مون هن ٽيبل کي سڏيو آهي متبادل 2).

هتي بنيادي فرق اهو آهي ته توهان کي اصل ڊيٽا جي صرف هڪ ٽڪرا کي تبديل ڪرڻ جي ضرورت آهي - مثال طور، پهرين ايڊريس غلط آهي ”ايس. پيٽرسبرگ" سڄي پاسي تي ”ايس. پيٽرسبرگ"، باقي ايڊريس (زپ ڪوڊ، گهٽي، گھر) ڇڏي ڏيو جيئن آھي.

ختم ٿيل فارمولا هن طرح نظر ايندو (سمجهڻ جي آسانيءَ لاءِ، مون ان کي ورهايو ته ڪيترين لائينن ۾ ھن+شامل ڪريو):

هتي بنيادي ڪم معياري ايڪسل ٽيڪسٽ فنڪشن طرفان ڪيو ويندو آهي سبسٽيٽيوٽ (متبادل)، جنهن ۾ 3 دليل آهن:

1. ماخذ ٽيڪسٽ - ايڊريس ڪالمن مان پهريون ڪرڙ پتو
2. جيڪو اسان ڳولي رهيا آهيون - هتي اسان فنڪشن سان چال استعمال ڪندا آهيون VIEW (ڏسو)ڪالمن مان قدر ڪڍڻ لاءِ پوئين طريقي کان لهڻ، جيڪو هڪ وکر پتي ۾ هڪ ٽڪرا طور شامل ڪيو ويو آهي.
3. ڇا سان مٽائڻو آهي - ساڳئي طريقي سان اسان ڪالمن مان ان سان لاڳاپيل صحيح قدر ڳوليندا آهيون سقراط.

هن فارمولا سان داخل ڪريو Ctrl+شفٽ+شامل ڪريو هتي به گهربل ناهي، جيتوڻيڪ اهو، حقيقت ۾، هڪ صف فارمولا آهي.

۽ اهو واضح طور تي ڏٺو ويو آهي (ڏسو #N/A اڳئين تصوير ۾ غلطيون) ته اهڙي فارمولا، ان جي تمام خوبصورتي لاء، ڪجهه خرابيون آهن:

• فعل SUBSTITUTE ڪيس حساس آهي, so “Spb” penultimate line ۾ متبادل جدول ۾ نه مليو. هن مسئلي کي حل ڪرڻ لاء، توهان يا ته فنڪشن استعمال ڪري سگهو ٿا ZAMENIT (تبديل)، يا ابتدائي طور تي ٻنهي ٽيبلن کي ساڳي رجسٽر تي آڻيو.
• جيڪڏهن متن شروعاتي طور تي صحيح آهي يا ان ۾ تبديل ڪرڻ لاء ڪو به ٽڪرو نه آهي (آخري لڪير)، پوء اسان جو فارمولا هڪ غلطي اڇلائي ٿو. ھن لمحي کي غير جانبدار ڪري سگھجي ٿو مداخلت ڪندي ۽ غلطي کي تبديل ڪندي فنڪشن استعمال ڪندي IFERROR (عيب):

  

• جيڪڏهن اصل متن تي مشتمل آهي هڪ ئي وقت ڊاريڪٽري مان ڪيترائي ٽڪرا، پوء اسان جو فارمولا صرف آخري ھڪڙي کي تبديل ڪري ٿو (8 هين لائن ۾، Ligovsky «ايون« ۾ تبديل ٿي ويو "پي آر ٽي"، پر "S-Pb" on ”ايس. پيٽرسبرگ" هاڻي نه، ڇاڪاڻ ته "S-Pbڊاريڪٽري ۾ وڌيڪ آهي). اهو مسئلو اسان جي پنهنجي فارمولا کي ٻيهر هلائڻ سان حل ڪري سگهجي ٿو، پر اڳ ۾ ئي ڪالمن سان مقرر:

  
  

جڳهن ۾ مڪمل ۽ بوجھل ناهي، پر ساڳئي دستي متبادل کان گهڻو بهتر، صحيح؟ 🙂

PS

ايندڙ آرٽيڪل ۾، اسان اهو معلوم ڪنداسين ته ميڪروس ۽ پاور سوال استعمال ڪندي اهڙي بلڪ متبادل کي ڪيئن لاڳو ڪيو وڃي.

• متن کي تبديل ڪرڻ لاءِ SUBSTITUTE فنڪشن ڪيئن ڪم ڪندو آهي
  
• EXACT فنڪشن استعمال ڪندي درست متن جي ميلاپ ڳولڻ
• ڪيس حساس ڳولا ۽ متبادل (ڪيس حساس VLOOKUP)