ধরুন আপনার কাছে একটি তালিকা আছে যেখানে "সরলবোধ" এর বিভিন্ন ডিগ্রী সহ প্রাথমিক ডেটা লেখা আছে - উদাহরণস্বরূপ, ঠিকানা বা কোম্পানির নাম:
 |  |
এটি স্পষ্টভাবে দেখা যায় যে একই শহর বা কোম্পানি এখানে মোটলি ভেরিয়েন্টে উপস্থিত রয়েছে, যা স্পষ্টতই, ভবিষ্যতে এই টেবিলগুলির সাথে কাজ করার সময় অনেক সমস্যা তৈরি করবে। এবং আপনি যদি একটু চিন্তা করেন, আপনি অন্যান্য এলাকা থেকে অনুরূপ কাজের অনেক উদাহরণ খুঁজে পেতে পারেন।
এখন কল্পনা করুন যে এই ধরনের কুটিল ডেটা আপনার কাছে নিয়মিত আসে, অর্থাৎ এটি একটি এককালীন "ম্যানুয়ালি ঠিক করুন, ভুলে যান" গল্প নয়, তবে নিয়মিতভাবে এবং প্রচুর সংখ্যক কক্ষে একটি সমস্যা।
কি করো? ম্যানুয়ালি আঁকাবাঁকা টেক্সটটি 100500 বার সঠিক লেখা দিয়ে প্রতিস্থাপন করবেন না “Find and Replace” বক্সের মাধ্যমে বা ক্লিক করে জন্য ctrl+H?
এই ধরনের পরিস্থিতিতে প্রথম যে জিনিসটি মনে আসে তা হল একটি প্রাক-সংকলিত রেফারেন্স বইয়ের সাথে মিলে যাওয়া ভুল এবং সঠিক বিকল্পগুলি অনুসারে একটি ভর প্রতিস্থাপন করা - যেমন:
দুর্ভাগ্যবশত, এই ধরনের কাজের সুস্পষ্ট প্রসারের সাথে, মাইক্রোসফ্ট এক্সেলের এটি সমাধানের জন্য সহজ অন্তর্নির্মিত পদ্ধতি নেই। শুরু করার জন্য, আসুন VBA বা পাওয়ার ক্যোয়ারীতে ম্যাক্রো আকারে "ভারী আর্টিলারি" যুক্ত না করে ফর্মুলা দিয়ে কীভাবে এটি করা যায় তা বের করা যাক।
কেস 1. বাল্ক সম্পূর্ণ প্রতিস্থাপন
চলুন শুরু করা যাক একটি তুলনামূলক সহজ কেস দিয়ে – এমন একটি পরিস্থিতি যেখানে আপনাকে পুরানো আঁকাবাঁকা পাঠ্যটিকে একটি নতুন দিয়ে প্রতিস্থাপন করতে হবে। সম্পূর্ণরূপে.
ধরা যাক আমাদের দুটি টেবিল আছে:
প্রথমটিতে - সংস্থাগুলির মূল বৈচিত্র্যময় নাম। দ্বিতীয় - চিঠিপত্রের একটি রেফারেন্স বই. যদি আমরা প্রথম টেবিলে কোম্পানির নামে কলাম থেকে কোনো শব্দ খুঁজে পাই খুঁজতে, তারপর আপনাকে এই কুটিল নামটি সম্পূর্ণরূপে সঠিকটির সাথে প্রতিস্থাপন করতে হবে – কলাম থেকে বিকল্প দ্বিতীয় সন্ধান টেবিল।
সুবিধার জন্য:
- উভয় টেবিল একটি কীবোর্ড শর্টকাট ব্যবহার করে গতিশীল ("স্মার্ট") রূপান্তরিত হয় জন্য ctrl+T বা দল সন্নিবেশ - টেবিল (ঢোকান — টেবিল).
- প্রদর্শিত ট্যাবে রচয়িতা (নকশা) প্রথম টেবিলের নাম উপাত্ত, এবং দ্বিতীয় রেফারেন্স টেবিল - খেলোয়াড়.
সূত্রের যুক্তি বোঝাতে একটু দূর থেকে যাই।
সেল A2 থেকে প্রথম কোম্পানীটিকে একটি উদাহরণ হিসাবে নেওয়া এবং সাময়িকভাবে বাকি সংস্থাগুলি সম্পর্কে ভুলে যাওয়া, আসুন কলাম থেকে কোন বিকল্পটি নির্ধারণ করার চেষ্টা করি খুঁজতে সেখানে দেখা হয়। এটি করার জন্য, শীটের মুক্ত অংশে যে কোনও খালি ঘর নির্বাচন করুন এবং সেখানে ফাংশনটি প্রবেশ করান খুঁজতে (অনুসন্ধান):
এই ফাংশনটি নির্ধারণ করে যে প্রদত্ত সাবস্ট্রিং অন্তর্ভুক্ত করা হয়েছে কিনা (প্রথম আর্গুমেন্টটি কলামের সমস্ত মান খুঁজতে) সোর্স টেক্সটে (ডেটা টেবিলের প্রথম কোম্পানি) এবং যে অক্ষর থেকে টেক্সট পাওয়া গেছে তার অর্ডিন্যাল নম্বর আউটপুট করা উচিত, অথবা যদি সাবস্ট্রিং পাওয়া না যায় তাহলে একটি ত্রুটি।
এখানে কৌশলটি হল যে যেহেতু আমরা প্রথম আর্গুমেন্ট হিসাবে একটি নয়, বেশ কয়েকটি মান নির্দিষ্ট করেছি, তাই এই ফাংশনটিও একটি মান নয়, 3টি উপাদানের একটি অ্যারে হিসাবে ফিরে আসবে। আপনার যদি অফিস 365 এর সর্বশেষ সংস্করণ না থাকে যা গতিশীল অ্যারে সমর্থন করে, তাহলে এই সূত্রটি প্রবেশ করার পরে এবং ক্লিক করার পরে প্রবেশ করান আপনি শীটে এই অ্যারেটি দেখতে পাবেন:
আপনার যদি এক্সেলের আগের ভার্সন থাকে তাহলে ক্লিক করার পর প্রবেশ করান আমরা ফলাফল অ্যারে থেকে শুধুমাত্র প্রথম মান দেখতে পাব, যেমন ত্রুটি #VALUE! (#VALUE!).
আপনার ভয় পাওয়া উচিত নয় 🙂 আসলে, আমাদের সূত্র কাজ করে এবং আপনি যদি ফর্মুলা বারে প্রবেশ করা ফাংশনটি নির্বাচন করেন এবং কী টিপুন তাহলে আপনি ফলাফলের সম্পূর্ণ অ্যারে দেখতে পাবেন F9(শুধু চাপ দিতে ভুলবেন না esc চাপুনসূত্রে ফিরে যেতে):
ফলাফলের ফলাফলের অ্যারে মানে মূল কুটিল কোম্পানির নামে (জি কে মরোজকো ওএও) একটি কলামের সমস্ত মানের খুঁজতে মাত্র দ্বিতীয় পাওয়া গেছে (মরোজকো), এবং একটি সারিতে ৪র্থ অক্ষর থেকে শুরু।
এখন আমাদের সূত্রে একটি ফাংশন যোগ করা যাক দেখুন(খুঁজে দেখো):
এই ফাংশনের তিনটি আর্গুমেন্ট আছে:
- কাঙ্ক্ষিত মান - আপনি যে কোনও পর্যাপ্ত পরিমাণে বড় সংখ্যা ব্যবহার করতে পারেন (মূল জিনিসটি হ'ল এটি উত্স ডেটার যে কোনও পাঠ্যের দৈর্ঘ্য অতিক্রম করে)
- দেখা_ভেক্টর - পরিসর বা অ্যারে যেখানে আমরা পছন্দসই মান খুঁজছি। এখানে পূর্বে চালু করা ফাংশন খুঁজতে, যা একটি অ্যারে প্রদান করে {#VALUE!:4:#VALUE!}
- ভেক্টর_ফলাফল - যে পরিসর থেকে আমরা মানটি ফেরত দিতে চাই যদি পছন্দসই মানটি সংশ্লিষ্ট ঘরে পাওয়া যায়। এখানে কলাম থেকে সঠিক নাম আছে বিকল্প আমাদের রেফারেন্স টেবিল।
এখানে প্রধান এবং অ-স্পষ্ট বৈশিষ্ট্য হল যে ফাংশন দেখুন কোন সঠিক মিল না থাকলে, সর্বদা নিকটতম ক্ষুদ্রতম (আগের) মানটি সন্ধান করে. অতএব, পছন্দসই মান হিসাবে যে কোনও মোটা সংখ্যা (উদাহরণস্বরূপ, 9999) নির্দিষ্ট করে, আমরা জোর করব দেখুন অ্যারে {#VALUE!:4:#VALUE!}-এ নিকটতম ক্ষুদ্রতম সংখ্যা (4) সহ ঘরটি সন্ধান করুন এবং ফলাফল ভেক্টর থেকে সংশ্লিষ্ট মান প্রদান করুন, যেমন কলাম থেকে সঠিক কোম্পানির নাম বিকল্প.
দ্বিতীয় সূক্ষ্মতা হল যে, প্রযুক্তিগতভাবে, আমাদের সূত্র একটি অ্যারের সূত্র, কারণ ফাংশন খুঁজতে ফলাফল একটি নয়, তিনটি মানের অ্যারে হিসাবে প্রদান করে। কিন্তু ফাংশন থেকে দেখুন বাক্সের বাইরে অ্যারে সমর্থন করে, তাহলে আমাদের এই সূত্রটিকে ক্লাসিক অ্যারে সূত্র হিসাবে প্রবেশ করতে হবে না - একটি কীবোর্ড শর্টকাট ব্যবহার করে জন্য ctrl+স্থানপরিবর্তন+প্রবেশ করান. একটি সহজ এক যথেষ্ট হবে প্রবেশ করান.
এখানেই শেষ. আশা করি যুক্তিটা পেয়েছেন।
এটি সমাপ্ত সূত্রটি কলামের প্রথম কক্ষ B2 এ স্থানান্তর করতে রয়ে গেছে ফিক্সড - এবং আমাদের টাস্ক সমাধান করা হয়েছে!
অবশ্যই, সাধারণ (স্মার্ট নয়) টেবিলের সাথে, এই সূত্রটিও দুর্দান্ত কাজ করে (শুধু কী সম্পর্কে ভুলবেন না F4 এবং প্রাসঙ্গিক লিঙ্ক ঠিক করা):
কেস 2. বাল্ক আংশিক প্রতিস্থাপন
এই কেসটা একটু জটিল। আবার আমাদের দুটি "স্মার্ট" টেবিল আছে:
বাঁকা লিখিত ঠিকানা সহ প্রথম টেবিল যা সংশোধন করা দরকার (আমি এটিকে বলেছি ডেটা 2) দ্বিতীয় টেবিলটি একটি রেফারেন্স বই, যা অনুসারে আপনাকে ঠিকানার ভিতরে একটি সাবস্ট্রিং এর আংশিক প্রতিস্থাপন করতে হবে (আমি এই টেবিলটিকে বলেছি প্রতিস্থাপন 2).
এখানে মৌলিক পার্থক্য হল যে আপনাকে মূল ডেটার শুধুমাত্র একটি অংশ প্রতিস্থাপন করতে হবে - উদাহরণস্বরূপ, প্রথম ঠিকানায় একটি ভুল আছে সেন্ট পিটার্সবার্গ" ডানদিকে সেন্ট পিটার্সবার্গ", বাকি ঠিকানা (জিপ কোড, রাস্তা, বাড়ি) রেখে দিন।
সমাপ্ত সূত্রটি এইরকম দেখাবে (ধারণার সহজতার জন্য, আমি এটিকে কতগুলি লাইন ব্যবহার করে ভাগ করেছি অল্টার+প্রবেশ করান):
এখানে মূল কাজটি স্ট্যান্ডার্ড এক্সেল টেক্সট ফাংশন দ্বারা করা হয় সাবস্টিটিউট (প্রতিস্থাপন), যার 3টি আর্গুমেন্ট আছে:
- উৎস পাঠ – ঠিকানা কলাম থেকে প্রথম আঁকাবাঁকা ঠিকানা
- আমরা যা খুঁজছি - এখানে আমরা ফাংশনের সাথে কৌশলটি ব্যবহার করি দেখুন (খুঁজে দেখো)আগের উপায় থেকে কলাম থেকে মান টানুন খুঁজতে, যা একটি বাঁকা ঠিকানায় একটি খণ্ড হিসাবে অন্তর্ভুক্ত করা হয়েছে।
- কি দিয়ে প্রতিস্থাপন করতে হবে - একইভাবে আমরা কলাম থেকে এটির সাথে সম্পর্কিত সঠিক মানটি খুঁজে পাই বিকল্প.
সঙ্গে এই সূত্র লিখুন জন্য ctrl+স্থানপরিবর্তন+প্রবেশ করান এখানেও প্রয়োজন নেই, যদিও এটি আসলে একটি অ্যারে সূত্র।
এবং এটি স্পষ্টভাবে দেখা যায় (আগের ছবিতে #N/A ত্রুটিগুলি দেখুন) যে এই জাতীয় সূত্র, তার সমস্ত কমনীয়তার জন্য, কয়েকটি ত্রুটি রয়েছে:
- ক্রিয়া SUBSTITUTE কেস সংবেদনশীল, তাই প্রতিস্থাপন সারণীতে উপান্তরেখায় "Spb" পাওয়া যায়নি। এই সমস্যাটি সমাধান করতে, আপনি হয় ফাংশনটি ব্যবহার করতে পারেন জামেনিট (প্রতিস্থাপন), অথবা প্রাথমিকভাবে উভয় টেবিল একই রেজিস্টারে আনুন।
- লেখাটি প্রাথমিকভাবে সঠিক হলে বা এতে প্রতিস্থাপন করার জন্য কোন টুকরা নেই (শেষ লাইন), তারপর আমাদের সূত্র একটি ত্রুটি নিক্ষেপ. ফাংশনটি ব্যবহার করে ত্রুটিগুলিকে বাধা দিয়ে এবং প্রতিস্থাপন করে এই মুহূর্তটিকে নিরপেক্ষ করা যেতে পারে IFERROR (IFERROR):
- মূল লেখা থাকলে একবারে ডিরেক্টরি থেকে বেশ কয়েকটি খণ্ড, তাহলে আমাদের সূত্রটি শুধুমাত্র শেষটি প্রতিস্থাপন করে (8 তম লাইনে, লিগোভস্কি «প্রশস্ত রাজপথ« পরিবর্তন "প্র-টি", কিন্তু "এস-পিবি" on সেন্ট পিটার্সবার্গ" আর নেই, কারণ “এস-পিবি" ডিরেক্টরিতে উচ্চতর)। এই সমস্যাটি আমাদের নিজস্ব সূত্র পুনরায় চালানোর মাধ্যমে সমাধান করা যেতে পারে, তবে ইতিমধ্যে কলাম বরাবর ফিক্সড:
জায়গাগুলিতে নিখুঁত এবং কষ্টকর নয়, তবে একই ম্যানুয়াল প্রতিস্থাপনের চেয়ে অনেক ভাল, তাই না? 🙂
PS
পরের প্রবন্ধে, আমরা ম্যাক্রো এবং পাওয়ার ক্যোয়ারী ব্যবহার করে এই ধরনের বাল্ক প্রতিস্থাপন কিভাবে বাস্তবায়ন করা যায় তা বের করব।
- পাঠ্য প্রতিস্থাপনের জন্য SUBSTITUTE ফাংশন কীভাবে কাজ করে
- সঠিক ফাংশন ব্যবহার করে সঠিক টেক্সট মিল খুঁজে বের করা
- কেস সংবেদনশীল অনুসন্ধান এবং প্রতিস্থাপন (কেস সংবেদনশীল VLOOKUP)