বিষয়বস্তু
ডেটা নিয়ে কাজ করার সময় সোর্স টেক্সটে কীওয়ার্ড অনুসন্ধান করা সবচেয়ে সাধারণ কাজগুলির মধ্যে একটি। আসুন নিম্নলিখিত উদাহরণটি ব্যবহার করে বিভিন্ন উপায়ে এর সমাধানটি দেখি:
ধরুন যে আপনার এবং আমার কাছে কীওয়ার্ডের একটি তালিকা রয়েছে - গাড়ির ব্র্যান্ডের নাম - এবং সমস্ত ধরণের খুচরা যন্ত্রাংশের একটি বড় টেবিল, যেখানে বিবরণে কখনও কখনও এক বা একাধিক ব্র্যান্ড একসাথে থাকতে পারে, যদি খুচরা যন্ত্রাংশ একাধিক ফিট করে। গাড়ির ব্র্যান্ড। আমাদের কাজ হল একটি প্রদত্ত বিভাজক অক্ষরের (উদাহরণস্বরূপ, একটি কমা) মাধ্যমে প্রতিবেশী কক্ষে সমস্ত সনাক্ত করা কীওয়ার্ডগুলি সন্ধান করা এবং প্রদর্শন করা।
পদ্ধতি 1. পাওয়ার কোয়েরি
অবশ্যই, প্রথমে আমরা একটি কীবোর্ড শর্টকাট ব্যবহার করে আমাদের টেবিলগুলিকে গতিশীল ("স্মার্ট") এ পরিণত করি জন্য ctrl+T বা আদেশ হোম - একটি টেবিল হিসাবে বিন্যাস (হোম - টেবিল হিসাবে ফর্ম্যাট), তাদের নাম দিন (উদাহরণস্বরূপ ষ্ট্যাম্পи খুচরা যন্ত্রাংশ) এবং ট্যাবে নির্বাচন করে পাওয়ার কোয়েরি সম্পাদকে একের পর এক লোড করুন ডেটা - টেবিল/রেঞ্জ থেকে (ডেটা — টেবিল/রেঞ্জ থেকে). যদি আপনার কাছে এক্সেল 2010-2013 এর পুরানো সংস্করণ থাকে, যেখানে পাওয়ার কোয়েরি একটি পৃথক অ্যাড-ইন হিসাবে ইনস্টল করা আছে, তাহলে পছন্দসই বোতামটি ট্যাবে থাকবে পাওয়ার কোয়েরি. আপনার যদি এক্সেল 365 এর একটি নতুন সংস্করণ থাকে তবে বোতামটি টেবিল/রেঞ্জ থেকে এখন সেখানে ডাকা পাতা দিয়ে (শীট থেকে).
পাওয়ার কোয়েরিতে প্রতিটি টেবিল লোড করার পর, আমরা কমান্ডের সাথে এক্সেলে ফিরে আসি হোম — বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন... — শুধুমাত্র সংযোগ তৈরি করুন (হোম — বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন... — শুধুমাত্র সংযোগ তৈরি করুন).
এখন একটি ডুপ্লিকেট অনুরোধ তৈরি করা যাক খুচরা যন্ত্রাংশএটিতে ডান ক্লিক করে এবং নির্বাচন করে ডুপ্লিকেট অনুরোধ (সদৃশ প্রশ্ন), তারপর ফলস্বরূপ অনুলিপি অনুরোধের নাম পরিবর্তন করুন ফলাফলগুলো এবং আমরা তার সাথে কাজ চালিয়ে যাব।
কর্মের যুক্তি নিম্নরূপ:
- উন্নত ট্যাবে একটি কলাম যোগ করা হচ্ছে একটি দল নির্বাচন করুন কাস্টম কলাম (কলাম যোগ করুন — কাস্টম কলাম) এবং সূত্র লিখুন = ব্র্যান্ড. ক্লিক করার পর OK আমরা একটি নতুন কলাম পাব, যেখানে প্রতিটি ঘরে আমাদের কীওয়ার্ডগুলির একটি তালিকা সহ একটি নেস্টেড টেবিল থাকবে - অটোমেকার ব্র্যান্ডগুলি:
- সমস্ত নেস্টেড টেবিল প্রসারিত করতে যোগ করা কলামের হেডারে ডবল তীর সহ বোতামটি ব্যবহার করুন। একই সময়ে, খুচরা যন্ত্রাংশের বর্ণনা সহ লাইনগুলি ব্র্যান্ডের সংখ্যার একাধিক দ্বারা গুণিত হবে, এবং আমরা "স্পেয়ার পার্ট-ব্র্যান্ড" এর সমস্ত সম্ভাব্য জোড়া-সংমিশ্রণ পাব:
- উন্নত ট্যাবে একটি কলাম যোগ করা হচ্ছে একটি দল নির্বাচন করুন শর্তাধীন কলাম (শর্তাধীন কলাম) এবং সোর্স টেক্সটে (আংশিক বিবরণ) কীওয়ার্ড (ব্র্যান্ড) এর উপস্থিতি পরীক্ষা করার জন্য একটি শর্ত সেট করুন:
- অনুসন্ধান কেসটিকে সংবেদনশীল করতে, সূত্র বারে ম্যানুয়ালি তৃতীয় আর্গুমেন্ট যোগ করুন Compare.OrdinalIgnoreCase ঘটনা চেক ফাংশন পাঠ্য। রয়েছে (যদি সূত্র বারটি দৃশ্যমান না হয়, তাহলে এটি ট্যাবে সক্ষম করা যেতে পারে পর্যালোচনা):
- আমরা ফলাফলের টেবিলটি ফিল্টার করি, কেবলমাত্র শেষ কলামে রেখে যাই, অর্থাৎ মিল এবং অপ্রয়োজনীয় কলামটি সরিয়ে ফেলি ঘটনা.
- কমান্ডের সাথে অভিন্ন বর্ণনাকে গোষ্ঠীবদ্ধ করা গ্রুপ দ্বারা ট্যাব রুপান্তর (রূপান্তর — গোষ্ঠী অনুসারে). একটি সমষ্টি অপারেশন হিসাবে, নির্বাচন করুন সব লাইন (সমস্ত সারি). আউটপুটে, আমরা টেবিল সহ একটি কলাম পাই, যাতে প্রতিটি খুচরা যন্ত্রাংশের সমস্ত বিবরণ রয়েছে, আমাদের প্রয়োজনীয় ব্র্যান্ডের অটোমেকার সহ:
- প্রতিটি অংশের জন্য গ্রেড বের করতে, ট্যাবে আরেকটি গণনা করা কলাম যোগ করুন একটি কলাম যোগ করা - কাস্টম কলাম (কলাম যোগ করুন — কাস্টম কলাম) এবং একটি টেবিল সমন্বিত একটি সূত্র ব্যবহার করুন (এগুলি আমাদের কলামে অবস্থিত বিস্তারিত) এবং নিষ্কাশিত কলামের নাম:
- আমরা ফলাফল কলামের শিরোনামে ডবল তীর সহ বোতামে ক্লিক করি এবং কমান্ডটি নির্বাচন করি এক্সট্রাক্ট মান (মান নির্যাস)আপনি চান যে কোনো বিভেদক অক্ষর সহ স্ট্যাম্প আউটপুট করতে:
- একটি অপ্রয়োজনীয় কলাম সরানো হচ্ছে বিস্তারিত.
- ফলস্বরূপ টেবিলে এটি থেকে অদৃশ্য হয়ে যাওয়া অংশগুলি যোগ করতে, যেখানে বর্ণনাগুলিতে কোনও ব্র্যান্ড পাওয়া যায়নি, আমরা ক্যোয়ারীটি একত্রিত করার পদ্ধতিটি সম্পাদন করি ফল মূল অনুরোধ সহ খুচরা যন্ত্রাংশ বোতাম মেশা ট্যাব হোম (হোম — মার্জ কোয়েরি). সংযোগ টাইপ - বাইরের জয়েন ডান (ডান বাইরের যোগদান):
- যা অবশিষ্ট থাকে তা হল অতিরিক্ত কলামগুলি সরানো এবং অবশিষ্টগুলিকে পুনরায় নামকরণ করা - এবং আমাদের কাজটি সমাধান করা হয়েছে:
পদ্ধতি 2. সূত্র
যদি আপনার কাছে Excel 2016 বা তার পরবর্তী সংস্করণ থাকে, তাহলে নতুন ফাংশন ব্যবহার করে আমাদের সমস্যাটি খুব কমপ্যাক্ট এবং মার্জিত উপায়ে সমাধান করা যেতে পারে। একসাথে (টেক্সট জয়েন):
এই সূত্রের পিছনে যুক্তি সহজ:
- ক্রিয়া অনুসন্ধান (অনুসন্ধান) অংশের বর্তমান বিবরণে ঘুরে ঘুরে প্রতিটি ব্র্যান্ডের উপস্থিতির জন্য অনুসন্ধান করে এবং হয় প্রতীকটির ক্রমিক নম্বর প্রদান করে, যেখান থেকে ব্র্যান্ডটি পাওয়া গেছে বা ত্রুটি #VALUE! যদি ব্র্যান্ড বিবরণে না থাকে।
- তারপর ফাংশন ব্যবহার করে IF (আইএফ) и ইওশিবকা (ISERROR) আমরা ত্রুটিগুলিকে একটি খালি টেক্সট স্ট্রিং "" দিয়ে প্রতিস্থাপন করি, এবং অক্ষরগুলির ক্রমিক সংখ্যাগুলি ব্র্যান্ডের নামগুলির সাথে।
- খালি কোষ এবং পাওয়া ব্র্যান্ডগুলির ফলস্বরূপ অ্যারে ফাংশন ব্যবহার করে একটি প্রদত্ত বিভাজক অক্ষরের মাধ্যমে একটি একক স্ট্রিংয়ে একত্রিত হয় একসাথে (টেক্সট জয়েন).
স্পীডআপের জন্য পারফরম্যান্স তুলনা এবং পাওয়ার কোয়েরি কোয়েরি বাফারিং
কর্মক্ষমতা পরীক্ষার জন্য, প্রাথমিক ডেটা হিসাবে 100টি খুচরা যন্ত্রাংশের বিবরণের একটি টেবিল নেওয়া যাক। এটিতে আমরা নিম্নলিখিত ফলাফলগুলি পাই:
- সূত্র দ্বারা পুনঃগণনার সময় (পদ্ধতি 2) – 9 সেকেন্ড। যখন আপনি প্রথম পুরো কলামে সূত্রটি অনুলিপি করবেন এবং 2 সেকেন্ড। পুনরাবৃত্তিতে (বাফারিং প্রভাবিত করে, সম্ভবত)।
- পাওয়ার ক্যোয়ারী ক্যোয়ারী আপডেটের সময় (পদ্ধতি 1) অনেক খারাপ – 110 সেকেন্ড।
অবশ্যই, অনেক কিছু একটি নির্দিষ্ট পিসির হার্ডওয়্যার এবং অফিসের ইনস্টল করা সংস্করণ এবং আপডেটের উপর নির্ভর করে, তবে সামগ্রিক চিত্র, আমি মনে করি, পরিষ্কার।
একটি পাওয়ার ক্যোয়ারী ক্যোয়ারী গতি বাড়ানোর জন্য, আসুন লুকআপ টেবিলটি বাফার করি ষ্ট্যাম্প, কারণ এটি ক্যোয়ারী সম্পাদনের প্রক্রিয়াতে পরিবর্তন হয় না এবং এটি ক্রমাগত পুনরায় গণনা করার প্রয়োজন হয় না (পাওয়ার কোয়েরি ডি ফ্যাক্টো করে)। এর জন্য আমরা ফাংশন ব্যবহার করি টেবিল.বাফার বিল্ট-ইন পাওয়ার কোয়েরি ভাষা থেকে এম.
এটি করতে, একটি ক্যোয়ারী খুলুন ফলাফলগুলো এবং ট্যাবে পর্যালোচনা বাটনটি চাপুন অ্যাডভান্সড এডিটর (দেখুন — অ্যাডভান্সড এডিটর). যে উইন্ডোটি খোলে, সেখানে একটি নতুন ভেরিয়েবলের সাথে একটি লাইন যোগ করুন মার্কি 2, যা আমাদের অটোমেকার ডিরেক্টরির একটি বাফার সংস্করণ হবে এবং পরবর্তীতে নিম্নলিখিত ক্যোয়ারী কমান্ডে এই নতুন ভেরিয়েবলটি ব্যবহার করবে:
এই ধরনের পরিমার্জনার পরে, আমাদের অনুরোধের আপডেটের গতি প্রায় 7 গুণ বৃদ্ধি পায় - 15 সেকেন্ড পর্যন্ত। একদম আলাদা জিনিস 🙂
- পাওয়ার কোয়েরিতে অস্পষ্ট পাঠ্য অনুসন্ধান
- সূত্র সহ বাল্ক টেক্সট প্রতিস্থাপন
- List.Accumulate ফাংশন সহ পাওয়ার কোয়েরিতে বাল্ক টেক্সট প্রতিস্থাপন