পাওয়ার কোয়েরিতে অস্পষ্ট মিলগুলির সাথে নোংরা এক্সেল ডেটা দ্রুত পরিষ্কার করুন৷

স্ট্যান্ডার্ড এক্সেল লুকআপ হল পারফেকশনিস্ট যা টাইপো বা অতিরিক্ত স্পেসে ব্যর্থ হয়। যদিও আপনি নেস্টেড XLOOKUP, TRIM এবং LOWER ফাংশন ব্যবহার করে একটি সমাধান হ্যাক করতে পারেন, এটি ভঙ্গুর এবং বজায় রাখা কঠিন। পাওয়ার কোয়েরি ফাজি ম্যাচিং হল কাজটি করার সবচেয়ে স্মার্ট, “যথেষ্ট কাছাকাছি” উপায়।

পাওয়ার কোয়েরি দুটি স্ট্রিং-এর মধ্যে একটি সাদৃশ্য স্কোর গণনা করতে একটি পরিশীলিত অস্পষ্ট ম্যাচিং অ্যালগরিদম ব্যবহার করে, একটি একক ম্যানুয়াল সম্পাদনা ছাড়াই “Microsoft” এবং “Mcrosoft” এর মধ্যে ব্যবধান পূরণ করে৷ এর মানে আপনি সেকেন্ডের মধ্যে একটি ক্লিনার মাস্টার ডাটাবেসের সাথে নোংরা রপ্তানি মার্জ করতে পারেন।

লক্ষ্য: দুটি টেবিলের গল্প

কল্পনা করুন আপনার কাছে একটি বিক্রয় প্রতিবেদন (T_Sales, কমলা) ম্যানুয়াল এন্ট্রিতে ভরা আছে, এবং আপনাকে মাস্টার টেবিল (T_Master, সবুজ) থেকে বিক্রয় ক্ষেত্রটি টেনে আনতে হবে।

যাইহোক, T_Sales-এ টাইপো ত্রুটি এবং বিভিন্ন নামকরণের কারণে, একটি আদর্শ XLOOKUP প্রায় প্রতিটি লুকআপের জন্য #N/A ফেরত দেবে। তাই, T_Sales-এ নাম খুঁজতে, T_Master-এ সবচেয়ে কাছের মিল খুঁজে বের করতে এবং বানানটি সামান্য ভুল হলেও ফিল্ডটি ফেরত দিতে আমাদের এক্সেলের প্রয়োজন।

কোনো অ্যালগরিদম অনুমান করতে পারে না এমন নির্দিষ্ট ব্যবসায়িক শর্টহ্যান্ড পরিচালনা করতে (যেমন “MSFT”), আপনাকে From এবং To কলাম শিরোনাম সহ একটি ছোট সেতু টেবিল (T_Abbreviation, blue) প্রয়োজন। আপনি শুরু করার আগে নিশ্চিত করুন যে এই টেবিলটি প্রস্তুত।

ধাপ 1: সংযোগ ব্যবহার করে আপনার ডেটা আমদানি করুন

আপনি অ্যালগরিদমটি আলগা করার আগে, আপনাকে পাওয়ার কোয়েরি ইঞ্জিনে তিনটি টেবিল আনতে হবে। আপনার ডেটা রেঞ্জগুলি এক্সেল টেবিল (Ctrl+T) হিসাবে ফর্ম্যাট করা হয়েছে তা নিশ্চিত করে শুরু করুন। তারপর, টেবিলের যেকোনো ঘর নির্বাচন করে এবং ডেটা > টেবিল/রেঞ্জ থেকে ক্লিক করে প্রথম টেবিলটি আমদানি করুন।

সংযুক্ত

আপনি যদি Excel এ টেবিলের নাম পরিবর্তন না করেন, তাহলে আজই শুরু করার দিন।

একটি নামে কি আছে? ওয়েল, বেশ কয়েক.

আপনার ওয়ার্কবুকে বিশৃঙ্খলা এড়াতে, পাওয়ার কোয়েরিতে প্রতিটি টেবিল ইম্পোর্ট করার পর স্ট্যান্ডার্ড “ক্লোজ এবং লোড” বোতামে ক্লিক করবেন না। পরিবর্তে, নিচের তীরটিতে ক্লিক করুন এবং “বন্ধ এবং লোড” নির্বাচন করুন।

তারপর, ডেটা আমদানি ডায়ালগে, “শুধু সংযোগ তৈরি করুন” নির্বাচন করুন এবং “ঠিক আছে” ক্লিক করুন।

আপনি তিনটি টেবিলের জন্য এই প্রক্রিয়াটি পুনরাবৃত্তি করার পরে, আপনি কেবলমাত্র সেগুলিকে কোয়েরি এবং সংযোগ ফলকে সংযোগ হিসাবে দেখতে পাবেন (যা আপনি প্রথম সংযোগ তৈরি করার পরে স্বয়ংক্রিয়ভাবে খোলে), একত্রিত হওয়ার জন্য প্রস্তুত৷

ধাপ 2: পরিষ্কার করা শুরু করতে আপনার টেবিল মার্জ করুন

ক্লিনআপ শুরু করতে, ক্যোয়ারি এবং কানেকশন প্যানে “T_Sales” এ ডান-ক্লিক করুন এবং “মার্জ করুন” নির্বাচন করুন।

তারপর, মার্জ ডায়ালগে, দ্বিতীয় টেবিল হিসাবে “T_Master” নির্বাচন করুন এবং উভয় টেবিলের পূর্বরূপে, “নাম” কলাম নির্বাচন করুন। মূলত, আপনি এক্সেলকে বলছেন যে আপনি T_Sales এবং T_Master টেবিলগুলি একসাথে ভাঙতে চান, এবং নাম কলাম নিজেই তাদের সংযুক্ত করে।

এখন, এক্সেলকে জানাতে “একত্রিত করতে অস্পষ্ট ম্যাচিং ব্যবহার করুন” চেক করুন যে কিছু সংযোগ টাইপো বা অসামঞ্জস্যপূর্ণ নামকরণের কারণে সঠিক নয়৷ হুডের নিচে, ইগনোর কেস বিকল্পটি ডিফল্টরূপে চেক করা হয়, যার অর্থ হল “Microsoft” সঠিকভাবে “Microsoft” এর সাথে কোনো অতিরিক্ত পদক্ষেপ ছাড়াই মিলবে৷ যোগদানের প্রকারের জন্য, “লেফ্ট আউটার”-এর সাথে লেগে থাকুন – এটি নিশ্চিত করে যে আপনি প্রতিটি বিক্রয় লেনদেনের ট্যাগিং শুধুমাত্র আঞ্চলিক তথ্যের সাথে মিলে যাচ্ছেন।

সংযুক্ত

কিভাবে আপনি আপনার অনুসন্ধান পরিমার্জিত করতে Microsoft Excel এ ওয়াইল্ডকার্ড ব্যবহার করতে পারেন

এক মুহূর্তের মধ্যে আংশিক মিল খুঁজুন।

ধাপ 3: সাদৃশ্য পরিসরে ডায়াল করুন

ঠিক আছে, তাই আপনি Excel কে বলেছেন যে আপনি কোন টেবিলগুলি একত্রিত করছেন, সংযোগকারী ভেরিয়েবলগুলি, এবং মিলগুলি সঠিক নয়৷ এখন, আপনাকে সীমা নির্ধারণ করতে হবে।

“ফজি ম্যাচিং অপশন” এর পাশের তীরটিতে ক্লিক করুন।

সাদৃশ্য থ্রেশহোল্ড হল 0.00 থেকে 1.00 পর্যন্ত একটি স্কেল, যেখানে 0.00 যেকোন স্তরের মিলের সাথে সমস্ত মানকে মেলে এবং 1.00 শুধুমাত্র সঠিক মানগুলির সাথে মেলে। উপরের স্ক্রিনশটটিতে লক্ষ্য করুন যে ছয় লাইনের মধ্যে তিনটি মিলেছে। কারণ পাওয়ার কোয়েরি 0.80 সাদৃশ্য থ্রেশহোল্ডে ডিফল্ট। এই পর্যায়ে, ইঞ্জিনটি কঠোর – এটি সহজেই “Microsoft Inc” কে ধরতে পারে কিন্তু “Appel” বা “Goggle” এর মত আরো আক্রমনাত্মক টাইপোতে ব্যর্থ হয়।

এই ক্ষেত্রে, 0.50 এর থ্রেশহোল্ড “আপেল” এবং “গগল” কে আকস্মিকভাবে সম্পর্কহীন শব্দের সাথে মিল না করে ধরার জন্য যথেষ্ট কম। এটি ছয়টি লাইনের মধ্যে পাঁচটি খুঁজে পেয়েছে, শুধুমাত্র “MSFT” সংক্ষিপ্ত নামটি অতুলনীয়।

ধাপ 4: সংক্ষেপণের জন্য রূপান্তর সারণী প্রয়োগ করুন

“MSFT” এর অর্থ মাইক্রোসফ্ট জানার জন্য যথেষ্ট স্মার্ট কোন অ্যালগরিদম নেই। এখানেই T_Abbrev টেবিলটি কার্যকর হয় – ট্রান্সফর্মেশন টেবিল ড্রপ-ডাউন মেনুতে, “T_Abbrev” ক্লিক করুন এবং এটি ইঞ্জিনকে আপনার ম্যানুয়াল ওভাররাইডগুলিকে সঠিক মিল হিসাবে বিবেচনা করতে বাধ্য করে৷ একবার নির্বাচিত হলে, নিচের আপনার ম্যাচ সংখ্যা ছয়টির মধ্যে ছয়ে বেড়ে যাবে।

ধাপ 5: আপনার চূড়ান্ত রিপোর্ট পরিষ্কার করুন এবং সংক্ষিপ্ত করুন

যখন আপনি “ঠিক আছে” ক্লিক করেন, তখন আপনাকে পাওয়ার কোয়েরি এডিটরে নিয়ে যাওয়া হবে একটি নতুন কলামের সাথে টেবিলের ফলাফলে ভরা।

কলাম হেডারে “প্রসারিত করুন” আইকনে ক্লিক করুন, T_Master.Name-এর মতো ক্লাঙ্কি হেডার প্রতিরোধ করতে “প্রিফিক্স হিসাবে মূল কলামের নাম ব্যবহার করুন” টিক চিহ্ন মুক্ত করুন এবং “ঠিক আছে” এ ক্লিক করুন।

আপনি দেখতে পাচ্ছেন যে নামগুলি এখন একটি নতুন Name.1 কলামে প্রমিত হয়েছে এবং অঞ্চলগুলিকে একটি নতুন অঞ্চল কলামে একত্রিত করা হয়েছে৷

এখান থেকে, মূল নোংরা নামের কলামটি মুছুন (কলাম হেডারে ডান-ক্লিক করুন এবং “রিমুভ” নির্বাচন করুন), বাম ক্লিক করুন এবং নতুন নাম কলামটি টেনে আনুন যাতে এটি টেবিলের প্রথম কলাম হয় এবং এটির নাম পরিবর্তন করুন (কলাম হেডারে ডাবল ক্লিক করুন) এরকম কিছু। অফিসিয়াল নাম. আপনি যখন সেখানে থাকবেন, প্রতিটি কলামে সঠিক ডেটা প্রকারগুলি বরাদ্দ করা হয়েছে তা নিশ্চিত করতে কলাম শিরোনামের আইকনগুলিতে ক্লিক করুন৷

সংযুক্ত

এক্সেলে পাওয়ার কোয়েরি ব্যবহার করে কীভাবে ডেটা পরিষ্কার এবং আমদানি করবেন

এই আশ্চর্যজনক এক্সেল টুল উপেক্ষা করবেন না!

যেহেতু এই প্রক্রিয়াটি প্রতিটি পৃথক লেনদেনকে ট্যাগ করে, তাই একই কোম্পানির জন্য আপনার একাধিক লাইন রয়েছে। এটিকে একটি পরিষ্কার প্রতিবেদনে পরিণত করতে, ট্রান্সফর্ম ট্যাবে, “গ্রুপ বাই” এ ক্লিক করুন।

তারপর, গ্রুপ বাই ডায়ালগে, উপরে “উন্নত” রেডিও বোতামটি নির্বাচন করুন৷ এটি আপনাকে একসাথে একাধিক কলামের উপর ভিত্তি করে গ্রুপ তৈরি করতে দেয়। এই ক্ষেত্রে, আমরা নাম এবং অঞ্চল কলাম দ্বারা গোষ্ঠীবদ্ধ করতে চাই। এটি করার জন্য, প্রথম গোষ্ঠীর জন্য, “অফিসিয়াল নাম” নির্বাচন করুন। তারপরে, “গ্রুপ যোগ করুন” এ ক্লিক করুন এবং “এরিয়া” নির্বাচন করুন।

এখন গণনা সেট আপ করুন। নতুন কলামের নামের ক্ষেত্রে টাইপ করুন মোট বিক্রয়অপারেশন ক্ষেত্রে, “মোট” নির্বাচন করুন এবং কলাম ক্ষেত্রে, “বিক্রয়” নির্বাচন করুন।

আপনি যখন “ঠিক আছে” ক্লিক করেন, সমস্ত পুনরাবৃত্তি সারি একত্রিত হয়।

একবার আপনি ফলাফলগুলি পর্যালোচনা করলে নিশ্চিত করুন যে সমস্ত ডেটা প্রত্যাশিত হিসাবে উপস্থিত হয়েছে, হোম ট্যাবে “বন্ধ এবং লোড” এ ক্লিক করুন৷

এখন, আপনার সুন্দর সংক্ষিপ্ত, সম্পূর্ণ বানান করা ডেটা একটি তাজা এক্সেল শীটে প্রদর্শিত হবে। এখানে একটি আগে এবং পরে স্ক্রিনশট রয়েছে যা আপনাকে এক্সেলে নোংরা ডেটাসেটগুলিকে একত্রিত করতে এবং পরিষ্কার করতে পাওয়ার কোয়েরির অস্পষ্ট ম্যাচিং ব্যবহার করার সুবিধা দেখায়৷

স্ট্যান্ডার্ড লুকআপগুলি অসংগঠিত ডেটার বাস্তবতার জন্য খুব কঠোর। অস্পষ্ট ম্যাচিং ব্যবহার করে, আপনি একটি স্কেলযোগ্য, স্বয়ংক্রিয় সিস্টেমের জন্য ম্যানুয়াল ডেটা এন্ট্রি ট্রেড করেন যা সিনট্যাক্সের পরিবর্তে উদ্দেশ্য বোঝে। অগোছালো স্প্রেডশীট ডেটা সংগঠিত করার জন্য পাওয়ার কোয়েরি টুলের সুবিধা নেওয়ার অনেক উপায়ের মধ্যে এটি একটি – একবার আপনি শুরু করলে, আপনি থামতে পারবেন না!

os

Windows, macOS, iPhone, iPad, Android

বিনামূল্যে ট্রায়াল

1 মাস

মাইক্রোসফ্ট 365-তে ওয়ার্ড, এক্সেল এবং পাওয়ারপয়েন্টের মতো অফিস অ্যাপে অ্যাক্সেস রয়েছে পাঁচটি ডিভাইস পর্যন্ত, 1 টিবি ওয়ানড্রাইভ স্টোরেজ এবং আরও অনেক কিছু।