19 এক্সেলের ব্যবহারিক ডেটা ক্লিনিং টেকনিক

ডেটা এন্ট্রি এবং সংস্থার সাথে, মাইক্রোসফ্ট এক্সেল আজকাল সহজেই প্রাথমিক থেকে মধ্যবর্তী স্তরের ডেটা বিশ্লেষণ করতে পারে। আমাদের দৈনন্দিন ব্যবহারের জন্য, এটি কার্যকরী সরঞ্জাম সরবরাহ করতে পারে যা অনেক দূর যেতে পারে। ডেটা পরিষ্কার করা যে কোনও ডেটা বিশ্লেষণ পদ্ধতির প্রাথমিক পদক্ষেপ। এটিতে অবাঞ্ছিত বা অনিয়মিত মানগুলিকে ব্যবহারযোগ্য মানগুলিতে অপসারণ করা, প্রস্তুত করা বা রূপান্তর করা অন্তর্ভুক্ত। এই টিউটোরিয়ালে, আমরা বিভিন্ন ডেটা ক্লিনিং কৌশল এবং কিভাবে Microsoft Excel এ সেগুলি সম্পাদন করতে হয় তা নিয়ে আলোচনা করব।

আপনি নীচের লিঙ্ক থেকে প্রদর্শনের জন্য ব্যবহৃত ওয়ার্কবুকটি ডাউনলোড করতে পারেন।

এক্সেলের 19 ডেটা ক্লিনিং টেকনিক যা কাজে আসবে

আমরা এই নিবন্ধে এক্সেলের মোট উনিশটি বিভিন্ন ডেটা পরিষ্কার করার কৌশল নিয়ে আলোচনা করব। সবগুলোই কমবেশি বিভিন্ন ক্ষেত্রে গুরুত্বপূর্ণ। তাদের প্রত্যেকটি কীভাবে কাজ করে তা দেখতে বরাবর অনুসরণ করুন বা উপরের বিষয়বস্তুর সারণী থেকে আপনার প্রয়োজনীয় একটি খুঁজুন।

1. ডুপ্লিকেট সারি সরান

আপনার ডেটার ডুপ্লিকেট সারি থাকতে পারে, কারণ যাই হোক না কেন। বেশিরভাগ সময়, আপনাকে ডুপ্লিকেট সারিগুলি বাদ দিতে হবে। পুরানো দিনে, ডুপ্লিকেট ডেটা অপসারণ করা একটি ম্যানুয়াল কাজ ছিল- যদিও অপসারণের কাজগুলি উন্নত কৌশলগুলির সাথে করা যেতে পারে। কিন্তু সদৃশ সরান দিয়ে কমান্ড, সদৃশ অপসারণ এখন একটি সহজ কাজ. সদৃশগুলি সরান৷ কমান্ড এক্সেল 2007 এ চালু করা হয়েছিল।

আসুন পাশের কলামটি প্রতিলিপি করি এবং তারপরে পরবর্তীতে তুলনা করার জন্য সদৃশগুলি সরিয়ে ফেলি৷

সদৃশ অপসারণ করতে এই পদক্ষেপগুলি অনুসরণ করুন৷

পদক্ষেপ:

• প্রথমে, আপনি যে কলামটি থেকে সদৃশ অপসারণ করতে চান সেটি নির্বাচন করুন৷
৷
• এখন ডেটা -এ যান আপনার রিবনে ট্যাব।
• তারপর সদৃশগুলি সরান নির্বাচন করুন৷ ডেটা টুলস থেকে সেখানে গ্রুপ।

• এর পরে, বর্তমান নির্বাচনের সাথে চালিয়ে যান নির্বাচন করুন পপ-আপ বক্সে বিকল্প এবং সদৃশ সরান-এ ক্লিক করুন .

• এরপর, ঠিক আছে এ ক্লিক করুন .

এটি নির্বাচন থেকে সমস্ত সদৃশ মুছে ফেলবে৷

2. ডুপ্লিকেট মান হাইলাইট করুন

কখনও কখনও সদৃশ মানগুলিকে সম্পূর্ণরূপে সরানোর পরিবর্তে, তাদের হাইলাইট করা সহায়ক। এখানে আমরা আপনাকে ডুপ্লিকেট মান হাইলাইট করার বিভিন্ন পরিস্থিতি এবং পদ্ধতি দেখাব।

শর্তাধীন বিন্যাস ব্যবহার করা হচ্ছে

প্রথম অংশে, আমরা কন্ডিশনাল ফরম্যাটিং ব্যবহার করতে যাচ্ছি ডুপ্লিকেট মান হাইলাইট করার বৈশিষ্ট্য। আমরা নিম্নলিখিত ডেটাসেটে তা করব।

আমরা কীভাবে তা করতে পারি তা দেখতে এই পদক্ষেপগুলি অনুসরণ করুন৷

পদক্ষেপ:

• প্রথমে, আমরা যে কক্ষগুলি নকল করতে চাই সেগুলি নির্বাচন করুন। এই ক্ষেত্রে, এটি D5:D18।
• দ্বিতীয়ভাবে, হোম এ যান> শর্তাধীন বিন্যাস চয়ন করুন> হাইলাইট সেল নিয়ম এ যান> ডুপ্লিকেট মান নির্বাচন করুন .

• অবশেষে, একটি ডুপ্লিকেট মান উইন্ডো প্রদর্শিত হবে।
• তৃতীয়ত, ডুপ্লিকেট বেছে নিন .
• চতুর্থত, এর সাথে মান-এ যেকোনো রঙের বিকল্প নির্বাচন করুন এই ক্ষেত্রে, আমরা গাঢ় লাল পাঠের সাথে হালকা লাল পূরণ নির্বাচন করেছি .

• ফলে, আমরা দেখতে পাব যে D-এ নকল মান কলাম হাইলাইট করা হয়।

প্রথম ঘটনা সহ ডুপ্লিকেট হাইলাইট করা

এখন, একই ডেটাসেটের জন্য, আমরা প্রথম ঘটনা সহ ডুপ্লিকেট মান হাইলাইট করতে যাচ্ছি। এটি পূর্বের উল্লিখিত হিসাবে একই। কিন্তু আমরা এখানে COUNITF ফাংশন সহ একটি ভিন্ন পথ অনুসরণ করব . এই পদ্ধতিটি কীভাবে কাজ করে তা দেখতে এই পদক্ষেপগুলি অনুসরণ করুন৷

পদক্ষেপ:

• প্রথমে, নতুন ফর্ম্যাটিং নিয়মে যান৷ কন্ডিশনাল ফরম্যাটিং এ গিয়ে উইন্ডো

• দ্বিতীয়ভাবে, বেছে নিন কোন কক্ষকে ফর্ম্যাট করতে হবে তা নির্ধারণ করতে একটি সূত্র ব্যবহার করুন .
• তৃতীয়ত, সূত্র বক্সে নিম্নলিখিত সূত্রটি লিখুন।

=COUNTIF($D$5:$E$16,$D5)>1

• ফলে, আমরা লক্ষ্য করব যে D কলামটি সদৃশগুলির জন্য হাইলাইট করা হয়েছে, 1ম ঘটনা সহ৷
৷

প্রথম ঘটনা বাদ দিয়ে ডুপ্লিকেট হাইলাইট করা

এখন আমরা ডুপ্লিকেট মান সনাক্ত করার জন্য সবচেয়ে দরকারী কৌশলগুলির মধ্যে আরেকটি নিয়ে আলোচনা করতে যাচ্ছি। এটি প্রথম ঘটনাগুলি বাদ দিয়ে তাদের হাইলাইট করে না। এটি সহায়ক যদি আমরা সমস্ত ডুপ্লিকেট মানের পরিবর্তে পুনরাবৃত্ত মানগুলি পর্যবেক্ষণ করতে চাই। ঠিক আগেরটির মতো, আমরা COUNTIF ফাংশন ব্যবহার করব৷ এখানে।

আমরা কীভাবে তা করতে পারি তা দেখতে এই পদক্ষেপগুলি অনুসরণ করুন৷

পদক্ষেপ:

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

• দ্বিতীয়ভাবে, কোন কক্ষ বিন্যাস করতে হবে তা নির্ধারণ করতে একটি সূত্র ব্যবহার করুন নির্বাচন করুন আগের মতই।
• তৃতীয়ত, সূত্র বক্সে নিম্নলিখিত সূত্রটি লিখুন।

=COUNTIF($D$5:$D5,$D5)>1

• ঠিক আছে এ ক্লিক করার পর , এটি প্রথম ঘটনাগুলি ব্যতীত সমস্ত সদৃশগুলিকে হাইলাইট করবে৷
৷

3. একাধিক কক্ষে পাঠ্য বিভক্ত করুন

আপনি যখন অন্য উৎস থেকে ডেটা আমদানি করেন, তখন এমন হতে পারে যে একাধিক মান একক কলামে আমদানি করা হয়েছে। নিম্নলিখিত চিত্রটি এই ধরণের আমদানি ঘটনার একটি উদাহরণ দেখায়৷

একটি দরকারী কলামে পাঠ্য আছে৷ বৈশিষ্ট্য যা আমরা ব্যবহার করতে পারি যা আমাদের এই পাঠ্যগুলিকে বিভক্ত করতে সাহায্য করবে৷

পদক্ষেপ:

• প্রথমে, আপনি যে পরিসর থেকে পাঠ্য বিভক্ত করতে চান তা নির্বাচন করুন৷
৷
• তারপর ডেটা -এ যান আপনার রিবনে ট্যাব।
• তার পর, ডেটা টুলস থেকে গোষ্ঠীতে, কলামে পাঠ্য নির্বাচন করুন .

• এরপর, সীমাবদ্ধ নির্বাচন করুন পরবর্তী বক্সে।

• পরবর্তী এ ক্লিক করার পর , আরেকটি বক্স প্রদর্শিত হবে।
• স্পেস নির্বাচন করুন এখানে ডিলিমিটার এর অধীনে টেক্সট স্পেস দ্বারা বিভক্ত হিসাবে বিকল্প।
• তারপর পরবর্তী এ ক্লিক করুন .

• এর পর, Finish এ ক্লিক করুন .

• অবশেষে, পাঠ্যটি বিভক্ত হবে।

• এখন এটিকে আরও উপস্থাপনযোগ্য করার জন্য কিছু পরিবর্তন করা যাক।

এইভাবে আমরা এক্সেলের অন্যান্য ডেটা পরিষ্কারের কৌশলগুলির সাথে বিভিন্ন কোষে পাঠ্যগুলিকে বিভক্ত করতে পারি৷

4. সূত্রের সাথে ডেটা রূপান্তর করা

কখনও কখনও, আপনি চাইতে পারেন যে আপনার পাঠ্যের ক্ষেত্রে একই রকম হবে। এক্সেল পাঠ্যের ক্ষেত্রে পরিবর্তন করার কোনো সরাসরি উপায় প্রদান করে না, তবে আপনি কিছু সূত্র ব্যবহার করে সহজেই এটি করতে পারেন। টেক্সট পরিবর্তন করার তিনটি সূত্র হল:

ঊর্ধ্ব :এই সূত্রটি পাঠ্যটিকে সমস্ত বড় হাতের অক্ষরে রূপান্তরিত করে৷

নিম্ন :এই সূত্রটি টেক্সটকে সব ছোট হাতের অক্ষরে রূপান্তর করে।

সঠিক :এটি টেক্সটটিকে প্রপার কেসে রূপান্তরিত করে (প্রতিটি শব্দের প্রথম অক্ষরটি একটি সঠিক নামের মতো ক্যাপিটাল করা হবে)।

আমরা সমস্ত উপস্থাপনার জন্য নিম্নলিখিত ডেটাসেট ব্যবহার করব।

অপার কেস এ পরিবর্তন করা হচ্ছে

আগেই উল্লেখ করা হয়েছে, আমরা UPPER ফাংশন ব্যবহার করব এই উদ্দেশ্যে. আমরা কীভাবে তা করতে পারি তা দেখতে এই পদক্ষেপগুলি অনুসরণ করুন৷

পদক্ষেপ:

• প্রথমে, সেল C5 নির্বাচন করুন .
• তারপর নিচের সূত্রটি লিখুন।

=UPPER(B5)

• এর পর, Enter টিপুন .

• এখন আবার ঘরটি নির্বাচন করুন এবং ফর্মুলা দিয়ে বাকি ঘরগুলি পূরণ করতে ফিল হ্যান্ডেল আইকনে ক্লিক করুন এবং টেনে আনুন৷

লোয়ার কেসে পরিবর্তন করা হচ্ছে

এর পরে, আমরা দেখাব কীভাবে সমস্ত পাঠ্যকে ছোট হাতের অক্ষরে রূপান্তর করা যায়।

আমরা এর জন্য নিম্নলিখিত পদক্ষেপগুলি অনুসরণ করব৷

পদক্ষেপ:

• প্রথমে, সেল D5 নির্বাচন করুন .
• তারপর নিচের সূত্রটি লিখুন।

=LOWER(B5)

• এর পর, Enter টিপুন .

• এখন আবার ঘরটি নির্বাচন করুন এবং ফর্মুলা দিয়ে বাকি ঘরগুলি পূরণ করতে ফিল হ্যান্ডেল আইকনে ক্লিক করুন এবং টেনে আনুন৷

সঠিক ক্ষেত্রে পরিবর্তন করা হচ্ছে

সঠিক কেসটি সঠিক স্থানে বড় হাতের অক্ষর এবং অন্যান্য স্থানে ছোট হাতের অক্ষর নির্দেশ করে। এই ডেটাসেটের জন্য, আমরা এর জন্য নিম্নলিখিত পদ্ধতি ব্যবহার করছি।

পদক্ষেপ:

• প্রথমে, সেল E5 নির্বাচন করুন .
• তারপর নিচের সূত্রটি লিখুন।

=PROPER(B5)

• এর পর, Enter টিপুন .

• অবশেষে, ঘরটি আবার নির্বাচন করুন এবং বাকি ঘরগুলি পূরণ করতে ফিল হ্যান্ডেল আইকনে ক্লিক করুন এবং টেনে আনুন।

5. অতিরিক্ত স্থান সরান

পাঠ্যটিতে অতিরিক্ত স্পেস নেই তা নিশ্চিত করা সর্বদা একটি গুরুত্বপূর্ণ বিষয়। একটি টেক্সট স্ট্রিং এর শেষে একটি স্পেস অক্ষর খুঁজে বের করা সম্ভব নয়। অতিরিক্ত স্পেস অনেক সমস্যা তৈরি করে, বিশেষ করে যখন আপনাকে টেক্সট স্ট্রিং তুলনা করতে হয়। উদাহরণস্বরূপ বলুন "জুলাই" "জুলাই" এর মতো নয়। প্রথমটি চারটি অক্ষর এবং দ্বিতীয়টি পাঁচটি অক্ষর দীর্ঘ৷

অতিরিক্ত স্পেস অপসারণের জন্য আমরা Excel-এ এই দুটি ডেটা পরিষ্কার করার কৌশল অনুসরণ করতে পারি।

আমরা নিম্নলিখিত ডেটাসেটে উভয়ই ব্যবহার করব।

TRIM ফাংশন ব্যবহার করে

আমরা TRIM ফাংশন ব্যবহার করি একটি পাঠ্য থেকে সমস্ত অগ্রণী এবং পরবর্তী স্থানগুলি সরাতে৷ TRIM ফাংশন একটি একক স্পেস দিয়ে একাধিক স্পেস প্রতিস্থাপন করে।

পদক্ষেপ:

• প্রথমে, আপডেটেড ডেটা নামে একটি কলাম যোগ করুন ফলাফল দেখানোর জন্য।
• তারপর সেল D5-এ ক্লিক করুন .
• =TRIM টাইপ করুন এবং সেল C5 নির্বাচন করুন প্রথম যুক্তিতে। বন্ধনী বন্ধ করুন।
• সুতরাং, সূত্রটি হয়ে যায়:

=TRIM(C5)

• এখন, এন্টার টিপুন .

• এখন ফিল হ্যান্ডেল আইকনটিকে শেষ ঘরে টেনে আনুন।

আমরা দেখতে পাচ্ছি যে অপ্রয়োজনীয় স্থানগুলি সরানো হয়েছে; প্রতিটি শব্দের পরে শুধুমাত্র একক স্পেস বিদ্যমান।

ফাইন্ড এবং রিপ্লেস ফিচার ব্যবহার করা:

এই বিভাগে, আমরা আলোচনা করব কিভাবে সমস্ত স্পেস অপসারণ করতে Find &Replace কমান্ড ব্যবহার করতে হয়।

পদক্ষেপ:

• প্রথমে, যেখান থেকে আমরা অতিরিক্ত স্পেস মুছে ফেলতে চাই সেই ডেটা নির্বাচন করুন।
• তারপর হোম  এ যান৷ ট্যাব।
• সম্পাদনা থেকে কমান্ড, খুঁজুন এবং নির্বাচন করুন-এ যান বৈশিষ্ট্য।
• প্রতিস্থাপন নির্বাচন করুন ড্রপ-ডাউন তালিকা থেকে।

• যখন আমরা প্রতিস্থাপন নির্বাচন করি , আমরা একটি ডায়ালগ বক্স পাব।
• কী খুঁজুন এ একটি ফাঁকা স্থান টাইপ করুন ক্ষেত্র।
• এর সাথে প্রতিস্থাপন রাখুন বাক্স খালি।
• তারপর, সব প্রতিস্থাপন করুন ক্লিক করুন .

• ক্লিক করার পর সব প্রতিস্থাপন করুন , আমরা প্রতিস্থাপনের সংখ্যা দেখানো একটি পপ-আপ পাব।
• এখন ঠিক আছে ক্লিক করুন পপ-আপে৷
৷
• তারপর বন্ধ করুন ক্লিক করুন৷ ডায়ালগ বক্সের।
• অবশেষে, আমরা ফলাফল পেয়েছি।

6. অদ্ভুত অক্ষর সরান

প্রায়শই, এক্সেল ওয়ার্কশীটে ডেটা ইম্পোর্ট করার পরে, আপনি দেখতে পাবেন যে কিছু অদ্ভুত (কখনও কখনও মুদ্রণযোগ্য) অক্ষর রয়েছে। আপনি ক্লিন ফাংশন ব্যবহার করতে পারেন একটি পাঠ্য থেকে সমস্ত অমুদ্রণ অক্ষর মুছে ফেলার জন্য। যদি ডেটা A2, কক্ষে থাকে আপনি আপনার কাজ করতে ব্যবহার করতে পারেন:

=CLEAN(A2)

ক্লিন ফাংশন কখনও কখনও কিছু অ-মুদ্রণ ইউনিকোড অক্ষর মিস করতে পারে। এটি 7-বিট ASCII কোডের প্রথম 32টি অ-মুদ্রণ অক্ষরগুলি সরানোর জন্য প্রোগ্রাম করা হয়েছে৷ কিভাবে অমুদ্রিত ইউনিকোড অক্ষরগুলি সরাতে হয় সে সম্পর্কে তথ্যের জন্য এক্সেল সহায়তা সিস্টেম ব্রাউজ করুন৷

7. মান রূপান্তর করুন

ডেটা ক্লিনিং কৌশলগুলির আরেকটি গুরুত্বপূর্ণ হল একটি ডেটাসেটের একটি মানকে অন্যটিতে রূপান্তর করা। কখনও কখনও আপনাকে একটি মেট্রিক সিস্টেম থেকে অন্য মেট্রিক সিস্টেমে মান রূপান্তর করতে হতে পারে। উদাহরণস্বরূপ, আপনি একটি ফাইল আমদানি করতে পারেন যার মান ফ্লুইড আউন্স (fl oz) আছে এবং সেগুলিকে মিলিলিটারে প্রকাশ করতে হবে। Excel এর CONVERT ফাংশন এই ধরনের এবং অন্যান্য অনেক ধরনের রূপান্তর সম্পাদন করতে পারে।

এই ফাংশনটি অত্যন্ত সহায়ক এবং নিম্নলিখিত বিভাগে সবচেয়ে সাধারণ পরিমাপের ইউনিটগুলি পরিচালনা করতে পারে:ওজন এবং ভর, দূরত্ব, সময়, চাপ, বল, শক্তি, শক্তি, চুম্বকত্ব, তাপমাত্রা, আয়তন, তরল, এলাকা, বিট এবং বাইট এবং গতি .

একটি প্রদর্শনের জন্য, আসুন একটি ডেটাসেট দেখি এবং এটিতে এটি প্রয়োগ করি। এটি হল ডেটাসেট৷

তাপমাত্রা ফারেনহাইটে পরিবর্তন করতে এই পদক্ষেপগুলি অনুসরণ করুন৷

পদক্ষেপ:

• প্রথমে, আউটপুট সেল D5 নির্বাচন করুন
• তারপর নিম্নলিখিত সূত্রটি টাইপ করুন

=CONVERT(C5,"C","F")

• এর পর, Enter টিপুন .

• এখন ক্লিক করুন এবং সমস্ত মান রূপান্তর করতে কলামের শেষে ফিল হ্যান্ডেল আইকনটি টেনে আনুন৷

এইভাবে আমরা মানকে অন্য ইউনিটে রূপান্তর করতে পারি। অন্যান্য ইউনিট রূপান্তরের জন্য, এই নিবন্ধটি দেখুন .

8. হাইলাইট ত্রুটিগুলি

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

ধরুন আমাদের কিছু কোষে কিছু ত্রুটি সহ একটি ডেটাসেট আছে। এখন আমরা তাদের শর্তসাপেক্ষ বিন্যাস এবং বিশেষে যান দিয়ে হাইলাইট করব এক্সেলের বৈশিষ্ট্য।

চলুন নিচের স্প্রেডশীটটি দেখে নেওয়া যাক।

এখানে ত্রুটিগুলি হাইলাইট করতে এই পদক্ষেপগুলি অনুসরণ করুন৷

পদক্ষেপ:

• সর্বোপরি, পুরো ডেটাসেট নির্বাচন করুন এবং নতুন নিয়ম ক্লিক করুন কন্ডিশনাল ফরম্যাটিং  থেকে বিকল্প।

• তারপর, নিয়মের টাইপে ফরম্যাট করুন শুধুমাত্র যে কক্ষগুলি রয়েছে বেছে নিন এবং তারপর ত্রুটি নির্বাচন করুন নিচের ড্রপ-ডাউন তালিকা থেকে।

• তারপর ঠিক আছে এ ক্লিক করুন .

এটি ডেটাসেটের চূড়ান্ত ফলাফল। আপনি দেখতে পাচ্ছেন, এক্সেল ত্রুটির মানগুলিকে হাইলাইট করেছে৷

এইভাবে আপনি এক্সেলের ডেটা পরিষ্কারের কৌশলগুলির একটি হিসাবে ত্রুটিগুলি হাইলাইট করতে পারেন৷

9. কলামে যোগ দিন

কলামগুলিতে যোগদান করা সেই কৌশলগুলির মধ্যে আরেকটি হল ডেটা পরিষ্কার বা পুনর্বিন্যাস করার সময় আমাদের কিছু সময়ের প্রয়োজন হতে পারে। এই প্রদর্শনের জন্য, আসুন নিম্নলিখিত ডেটাসেটটি নেওয়া যাক।

এক্সেলের পৃথক কোষ থেকে আমরা কীভাবে এই পাঠ্যগুলিতে যোগ দিতে পারি তা দেখতে এই পদক্ষেপগুলি অনুসরণ করুন৷

পদক্ষেপ:

• প্রথমে, সেল E5 নির্বাচন করুন .
• তারপর নিচের সূত্রটি লিখুন।

=B5&" "&D5&" "&C5

• এর পর, Enter টিপুন .

• এখন ফিল হ্যান্ডেল আইকনটিতে ক্লিক করুন এবং টেনে আনুন কলামের শেষের কক্ষের জন্য সূত্রটি প্রতিলিপি করতে৷

এভাবেই আমরা Excel-এ দুই বা ততোধিক কলামে যোগ দিতে পারি।

10. কলাম পুনরায় সাজান

কখনও কখনও আপনাকে একটি ওয়ার্কশীটে কলামগুলি পুনর্বিন্যাস করতে হতে পারে। আপনি যদি কিছু মিনিটের জন্য চিন্তা করেন, আপনি এইভাবে এই সমস্যার সমাধান করতে পারেন:আপনি একটি ফাঁকা কলাম তৈরি করতে পারেন এবং তারপরে নতুন ফাঁকা কলামে অন্য কলাম টেনে আনতে পারেন। চলমান কলামটি একটি ফাঁক রেখে যাবে এবং আপনার কাজ শেষ হওয়ার পরে আপনাকে সেই কলামটি মুছে ফেলতে হবে৷

এখানে একটি সহজ উপায় রয়েছে যা আপনি কলামটিকে সহজেই পুনর্বিন্যাস করতে ব্যবহার করতে পারেন যা আমরা নিম্নলিখিত ডেটাসেটে করছি৷

পদক্ষেপ:

• প্রথমে, কলাম হেডারে ক্লিক করে আপনি যে কলামটি সরাতে চান সেটি নির্বাচন করুন৷
• তারপর প্রসঙ্গ মেনু ব্যবহার করে বা Ctrl+X টিপে কলামটি কাটুন . আপনি এই পয়েন্টে নির্বাচনের সীমানায় বিন্দুযুক্ত লাইন দেখতে পাবেন।

• এর পরে, কলামের শিরোনামটিতে ডান-ক্লিক করুন যার আগে আপনি আগের কলামটি রাখতে চান৷
• এখন কাট কোষ ঢোকান নির্বাচন করুন প্রসঙ্গ মেনু থেকে।

• এটি এখানে পূর্ববর্তী কলামটি সন্নিবেশ করবে।

এইভাবে আমরা Excel-এ ডেটা পরিষ্কার করার কৌশলগুলির অংশ হিসাবে কলামগুলিকে পুনর্বিন্যাস করতে পারি।

11. র্যান্ডমাইজ সারি

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

আমরা নিম্নলিখিত ডেটাসেটের জন্য এই ধরনের একটি কাজ সম্পাদন করতে যাচ্ছি।

সারিগুলি র্যান্ডমাইজ করতে এবং Excel-এ ক্রম পরিবর্তন করতে এই পদক্ষেপগুলি অনুসরণ করুন৷

পদক্ষেপ:

• প্রথমত, আমাদের B কলামের পরে একটি নতুন কলাম তৈরি করতে হবে। . এর জন্য, C কলামের হেডার লেটারে ক্লিক করুন , এবং সমগ্র C কলাম নির্বাচন করা হবে।
• তারপর, ডান-ক্লিক করুন এবং সন্নিবেশ কমান্ডটি নির্বাচন করুন, এবং একটি নতুন কলাম তৈরি হবে।

• এখন, নতুন তৈরি কলামে একটি নাম বরাদ্দ করুন (অর্থাৎ র‍্যান্ডম নম্বর)। প্রথম ঘরটি (অর্থাৎ C5) কলামটি নির্বাচন করুন এবং সেলে নিম্নলিখিত সূত্রটি টাইপ করুন৷

=RAND()

• এর পর, ENTER টিপুন , এবং C5 কক্ষে একটি এলোমেলো সংখ্যা প্রদর্শিত হবে৷ . সংখ্যাটি 1 এর কম।

• এরপর, ঘরটি আবার নির্বাচন করুন এবং বাকি কক্ষগুলির জন্য সূত্রটি প্রতিলিপি করতে কলামের শেষে ফিল হ্যান্ডেল আইকনে ক্লিক করুন এবং টেনে আনুন৷

• যখন সেল C5 নির্বাচিত হলে হোম  এ যান ট্যাব।
• তারপর সম্পাদনা -এ যান গ্রুপ করুন এবং বাছাই এবং ফিল্টার ক্লিক করুন .
• এখন সবচেয়ে ছোট থেকে বড় সাজান নির্বাচন করুন ড্রপ-ডাউন মেনু থেকে।

• আপনি কমান্ডে ক্লিক করার সাথে সাথেই আপনার সারিগুলি এলোমেলো হয়ে যাবে। এটা লক্ষণীয় যে শুধুমাত্র কলাম C নয় , কিন্তু সমস্ত কলামও এলোমেলোভাবে সারি বরাবর পরিবর্তন করা হয়েছে।

তাই এই পদক্ষেপগুলি আপনি অনুসরণ করতে পারেন যখন আপনি একাধিক কলাম সহ একটি ডেটাসেট পান এবং আপনি আপনার সারিগুলিকে এমনভাবে র্যান্ডমাইজ করতে চান যাতে একটি সারির সমস্ত কলাম তাদের মানগুলি এলোমেলো করে দেয়৷

12. URL থেকে ফাইলের নাম বের করুন

কখনও কখনও আপনার URL-এর একটি তালিকা থাকতে পারে এবং আপনাকে শুধুমাত্র ফাইলের নাম বের করতে হবে। নিম্নলিখিত উপায়ে, আপনি একটি URL থেকে ফাইলের নাম বের করতে পারেন। অনুমান সেল B5 এই URLটি রয়েছে:https://example.com/assets/images/horse.jpg .

আমাদের এখানে ফাংশনগুলির সম্পূর্ণ আধিক্য নিয়ে গঠিত একটি সূত্রের প্রয়োজন হবে। চিন্তা করবেন না, এটি কীভাবে কাজ করে তা বোঝার জন্য পরে একটি ব্যাখ্যা যোগ করা হয়েছে। এই সূত্রে সঠিক রয়েছে , LEN , খুঁজে নিন এবং পরিবর্তন এতে ফাংশন।

এখন লিঙ্ক থেকে ফাইলের নাম (horse.jpg) বের করতে এই ধাপগুলি অনুসরণ করুন।

পদক্ষেপ:

• প্রথমে, যে ঘরে আপনি ফাইলের নাম রাখতে চান সেটি নির্বাচন করুন। এই ক্ষেত্রে, এটি সেল C5 .
• তারপর নিচের সূত্রটি লিখুন।

=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))))

• After that, press Enter .

🔎 Breakdown of the Formula

👉 SUBSTITUTE(B5,”/”,””) removes all the “/” from the URL. It returns http:www.example.comassetsimageshorse.jpg.

👉 LEN(SUBSTITUTE(B5,”/”,””)) returns the length of the previous string.

👉 LEN(B5) returns the length of the string in cell B4 which is 46 here.

👉 LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))) indicates the difference between the two length values. This in turn indicates the number of “/” were removed.

👉 SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))) substitutes all the slashes (/) is cell B5 with star sign (*) with the instance number from the result of the previous function.

👉 FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””)))) finds the position “*” is in the string up until now.

👉 LEN(B5)-FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””)))) is the difference between the original string length and the previous position.

👉 Finally, RIGHT(B5,LEN(B5)-FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))))) extracts that number of characters from the right side of the cell.

13. Match Text in List

Let’s take a look at the following figure. We are going to find out the persons who have resigned from the left side of our example. There is a list on the right side of the resigned numbers.

The above one shows a simple example. The data is in the range B5:D133 . The goal is to identify the rows in the data zone which are appearing in the Resigned Members list, in column G . You can delete these unnecessary rows later. We are going to need a combination of the IF and COUNTIF functions for the purpose.

Follow these steps to see how we can achieve the resigned status in our dataset.

পদক্ষেপ:

• First, select cell E5 .
• Then write down the following formula.

=IF(COUNTIF($G$5:$G$25,C5),"Resigned","")

• After that, press Enter .

• Now double-click the fill handle icon on the bottom-right corner of the cell border. You can also click and drag. But as the dataset is large, we are opting for the previous method.
• Once you have done that, the spreadsheet will look like this.

This is how we can match text as part of the data analysis techniques in Excel.

14. Change Vertical Data to Horizontal

Transposing or changing vertical to horizontal and vice versa in a dataset is another common one in data analysis. So we will be discussing changing vertical data to horizontal now in the series of data cleaning techniques in Excel.

For that, we will be using the following dataset.

Using Paste Special Option

The easiest way to change a vertical column to a horizontal row is to use the Paste Special option of Excel. It also keeps the exact formatting while changing the vertical column. So, you don’t need to apply any formatting later. Let’s follow the steps below to see how we can use the Paste Special option to transpose the columns.

• First of all, select the range that you want to change to horizontal rows. Here, we have selected the range B4:C10 .
• Secondly, press Ctrl+C to copy the range.
• Thirdly, select a cell where you want to paste the range horizontally. In our case, we have selected Cell B12 .

This will change vertical columns into horizontal rows in the Excel spreadsheet.

Using TRANSPOSE Function:

We can use some Excel Functions to convert a vertical column to a horizontal row. Here, we will use the TRANSPOSE function for that purpose. The main advantage of using functions is that you will get dynamic updates in the horizontal rows if you change anything in the main dataset. But, the horizontal rows will not have the same formatting as the vertical columns. You need to add formatting after applying the method.

Let’s follow the steps below to learn more.

পদক্ষেপ:

• First, select the cell where you want to place the formula.
• Then write down the following formula in it.

=TRANSPOSE(B4:C10)

• Now press Enter .

This is how we can change vertical data to horizontal using the TRANSPOSE function.

15. Fill Blank Cells

One of the most important data cleaning techniques in any form of data analysis is filling blank values, either with average in numeric cases or with values with the previous or later rows.

Sometimes we keep some blank cells to understand that they will be the first filled ones, have a look at our dataset to understand it. But sometimes it creates problems like while we are filtering or sorting data. So it’s better to fill them and there are easy ways to o it.

This is our dataset.

We can fill them up using these two simple methods.

Using Fill Command

If your data is small, you can enter the missing cell values from above manually or by using this command. Follow these steps to see how you can do that.

• Select Cell B6 .
• Then go to the Home
• From the Editing group, click on Fill and then Down .

• The dataset will look like this now.

• Now repeat the process for all the blank cells and fill out all the cells.

This is one way to fill up blank cells in a dataset.

Using Keyboard Shortcut:

We can achieve the same result using a keyboard shortcut. The shortcut is Ctrl+D . You just have to select the blank cell and press the shortcut. It will fill the cell with the value from the previous cell in the same column. Follow these steps for a more detailed guide.

• First, select cell B6 .
• Then press Ctrl+D on your keyboard.

• Now selecting cell B9 and pressing Ctrl+D will yield the following result.

• Now fill out all the blank values by selecting them and pressing the shortcut.

16. Check Spelling

When you use Microsoft Word Document or another word processor, you use its spell checker feature most of the time. Spelling mistakes in a text document are embarrassing. In Excel, spelling mistakes can also occur serious problems. Say, for example, if you tabulate data by month, a misspelled month name will make that a year has 13 months.

Here we have made a dataset for the demonstration.

We are going to correct the range C5:C7 . Follow these steps to see how we can do that.

পদক্ষেপ:

• First, select the range C5:C7 .
• Then go to the Review tab and select Spelling from the Proofing You can also skip this step and press F7 on your keyboard.

• Now select the correct suggestion and click on Change to change the value of a particular cell. The errors come in the selection order here.

• This will change the first error in the dataset.

• Now repeat this process for all the errors and you will have correct spellings this way.

17. Replace or Remove Text

Removing and replacing data, especially outliers is another important task in any data cleaning process. So as a part of our data cleaning techniques compilation, we are going to demonstrate replacing and removing text in Excel. We can do that using three methods.

For all of the methods, we are using the following dataset.

Using Find &Replace Feature:

Excel has a direct feature that can help find, replace and remove certain values from another. Follow these steps to see how we can use this feature to remove or replace in Excel.

• First, select the range which you want to modify. In this case, it is C5:C13 .
• Then go to the Home
• After that, select Find &Replace from the Editing group section.
• Next, select Replace from the drop-down.

• Now, go to the Replace tab in the pop-up box.
• Then insert the value you want to replace in the Find what field and the value you want to replace it with in the Replace with

• Finally, click Replace All to replace all of them in the dataset.

This will be the final product after removing all of the hyphens from the dataset.

Utilizing Flash Fill:

Another feature we can use to replace or remove values while using data cleaning techniques is to utilize the flash fill feature. For the same dataset, follow these steps to see how that works.

• First, write down the final product after removing or replacing the desired value in the first cell. We are replacing the second hyphen in the string with a colon.

• Now start writing the second one manually. As soon as you start writing Excel will recognize the pattern and start suggesting output for the rest.

Note

If the suggestion doesn’t occur in the second entry, try it out manually. It sometimes suggests the output at the third or fourth entry if the pattern isn’t that obvious to Excel.

• Now press Enter .

Thus, you can replace or remove values using this handy feature of Excel while data cleaning.

Using SUBSTITUTE Function:

Another way we can replace a value, or remove them in Excel is to use the SUBSTITUTE function . This function takes three values- a string, the value to replace, and the value to replace it with. Follow these steps to see how we can use this function.

• First, select cell C5 .
• Then write down the following formula in it.

=SUBSTITUTE(B5,"-",":",2)

• After that, press Enter .

• Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

18. Add Text to Cells

Adding text is not only common as one of the data cleaning techniques but also in general Excel operations. We can add text to cells in various ways. For demonstrations of those, we will use the following dataset.

Now you can use one of the following methods to add text to cells.

Using Ampersand (&) Symbol:

The ampersand (&) symbol in a formula works as the string connector. It connects the previous and the following string it is in between. Follow these steps to see how we can easily add text to cells using this.

• First of all, select cell C5 .
• Secondly, put down the formula below.

=B5&".mp3"

• Next, press Enter .

• Finally, click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

Applying Flash Fill Feature:

Follow these steps to add text using the flash fill feature easily in Excel.

• First, write down the first intended value manually in cell C5 .

• Then start filling out the second one in the dataset. Once you start filling, Excel will suggest the pattern.

• As soon as the suggestion appears, press Enter .

This is another way to add text in Excel.

Using CONCATENATE Function:

The CONCATENATE function in Excel, as the name suggests, concatenates two or more strings together. It takes those strings as its arguments. It works the same way as the ampersand sign method described earlier. Follow these steps to see how we can use this function.

• First, select cell C5 .
• Then write down the following formula in it.

=CONCATENATE(B5,".mp3")

• After that, press Enter .

• Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

19. Fix Trailing Minus Sign

Sometimes while taking data from other sources, the negative values do have a trailing minus sign. Take a look at the following figure.

Excel doesn’t consider the values with trailing negative signs as numbers. Instead, it treats them as texts. This may cause problems, not only in the data cleaning techniques but also in general operations like using formulas in Excel. So we need to address the problem and convert them into negative numbers.

Follow these simple steps to do that.

পদক্ষেপ:

• First, select the range you want to convert. We have copied it to compare it with the previous one.

• Then go to the Data tab on your ribbon.
• Then select Text to Columns from the Data Tools group section.

• Finally, directly click on Finish in the wizard.

This will automatically change all the negative values with trailing minus signs to Excel’s negative sign format.

This procedure works because there is a default setting in the Advanced Text Import Settings ডায়ালগ বক্স।

উপসংহার

So these were some of the most useful data cleaning techniques in Excel used for data analysis. Hopefully, you can use these techniques while using data cleaning in Excel with ease. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides like this, visit Exceldemy.com .