বিষয়বস্তু
আপনি যদি একজন নবীন ব্যবহারকারী না হন, তাহলে আপনি অবশ্যই ইতিমধ্যে লক্ষ্য করেছেন যে Excel এর 99% সবকিছু উল্লম্ব টেবিলের সাথে কাজ করার জন্য ডিজাইন করা হয়েছে, যেখানে প্যারামিটার বা বৈশিষ্ট্যগুলি (ক্ষেত্রগুলি) কলামগুলির মধ্য দিয়ে যায় এবং বস্তু বা ঘটনা সম্পর্কে তথ্য অবস্থিত। লাইনে পিভট টেবিল, সাবটোটাল, একটি ডাবল ক্লিকের মাধ্যমে সূত্র অনুলিপি করা - সবকিছু এই ডেটা ফর্ম্যাটের জন্য বিশেষভাবে তৈরি করা হয়েছে।
যাইহোক, ব্যতিক্রম ছাড়া কোনও নিয়ম নেই এবং মোটামুটি নিয়মিত ফ্রিকোয়েন্সি সহ আমাকে জিজ্ঞাসা করা হয় যে যদি একটি অনুভূমিক শব্দার্থিক অভিযোজন সহ একটি টেবিল, বা একটি টেবিল যেখানে সারি এবং কলামের অর্থ একই ওজন থাকে, কাজটিতে আসে তাহলে কী করতে হবে:
এবং যদি এক্সেল এখনও অনুভূমিকভাবে বাছাই করতে জানে (কমান্ড সহ ডেটা - সাজান - বিকল্প - কলাম সাজান), তারপর ফিল্টারিং এর পরিস্থিতি আরও খারাপ – এক্সেলের সারি নয়, কলাম ফিল্টার করার জন্য কেবল কোনও অন্তর্নির্মিত সরঞ্জাম নেই। সুতরাং, আপনি যদি এই জাতীয় কাজের মুখোমুখি হন তবে আপনাকে বিভিন্ন মাত্রার জটিলতার সমাধান নিয়ে আসতে হবে।
পদ্ধতি 1. নতুন ফিল্টার ফাংশন
আপনি যদি এক্সেল 2021 এর নতুন সংস্করণ বা এক্সেল 365 সাবস্ক্রিপশনে থাকেন তবে আপনি নতুন চালু হওয়া বৈশিষ্ট্যটির সুবিধা নিতে পারেন ছাঁকনি (ছাঁকনি), যা শুধুমাত্র সারি দ্বারা নয়, কলাম দ্বারাও উত্স ডেটা ফিল্টার করতে পারে৷ কাজ করার জন্য, এই ফাংশনের জন্য একটি সহায়ক অনুভূমিক এক-মাত্রিক অ্যারে-সারি প্রয়োজন, যেখানে প্রতিটি মান (সত্য বা মিথ্যা) নির্ধারণ করে যে আমরা টেবিলের পরবর্তী কলামটি দেখাব বা লুকিয়ে রাখব।
আমাদের টেবিলের উপরে নিম্নলিখিত লাইন যোগ করুন এবং প্রতিটি কলামের অবস্থা লিখুন:
- ধরা যাক আমরা সর্বদা প্রথম এবং শেষ কলাম (শিরোনাম এবং মোট) প্রদর্শন করতে চাই, তাই তাদের জন্য অ্যারের প্রথম এবং শেষ কক্ষে আমরা মান = TRUE সেট করি।
- অবশিষ্ট কলামগুলির জন্য, সংশ্লিষ্ট কক্ষের বিষয়বস্তু একটি সূত্র হবে যা ফাংশন ব্যবহার করে আমাদের প্রয়োজনীয় অবস্থা পরীক্ষা করে И (এবং) or OR (বা). উদাহরণস্বরূপ, মোট 300 থেকে 500 এর মধ্যে রয়েছে।
এর পরে, এটি শুধুমাত্র ফাংশন ব্যবহার করার জন্য অবশেষ ছাঁকনি কলাম নির্বাচন করতে যার উপরে আমাদের অক্জিলিয়ারী অ্যারের একটি সত্য মান রয়েছে:
একইভাবে, আপনি একটি প্রদত্ত তালিকা দ্বারা কলাম ফিল্টার করতে পারেন। এই ক্ষেত্রে, ফাংশন সাহায্য করবে COUNTIF (COUNTIF), যা অনুমোদিত তালিকার টেবিলের শিরোনাম থেকে পরবর্তী কলামের নামের সংঘটনের সংখ্যা পরীক্ষা করে:
পদ্ধতি 2. সাধারণের পরিবর্তে পিভট টেবিল
বর্তমানে, Excel শুধুমাত্র পিভট টেবিলে কলাম দ্বারা অন্তর্নির্মিত অনুভূমিক ফিল্টারিং আছে, তাই যদি আমরা আমাদের মূল টেবিলটিকে একটি পিভট টেবিলে রূপান্তর করতে পরিচালনা করি, তাহলে আমরা এই অন্তর্নির্মিত কার্যকারিতা ব্যবহার করতে পারি। এটি করার জন্য, আমাদের উত্স টেবিলটি অবশ্যই নিম্নলিখিত শর্তগুলি পূরণ করবে:
- খালি এবং একত্রিত কক্ষ ছাড়া একটি "সঠিক" এক-লাইন হেডার লাইন আছে - অন্যথায় এটি একটি পিভট টেবিল তৈরি করতে কাজ করবে না;
- সারি এবং কলামের লেবেলে ডুপ্লিকেট ধারণ করবেন না - তারা শুধুমাত্র অনন্য মানগুলির একটি তালিকায় সারাংশে "পতন" করবে;
- মানগুলির পরিসরে (সারি এবং কলামের সংযোগস্থলে) শুধুমাত্র সংখ্যাগুলি ধারণ করে, কারণ পিভট টেবিলটি অবশ্যই তাদের জন্য কিছু ধরণের সমষ্টিগত ফাংশন প্রয়োগ করবে (সমষ্টি, গড়, ইত্যাদি) এবং এটি পাঠ্যের সাথে কাজ করবে না
যদি এই সমস্ত শর্ত পূরণ করা হয়, তাহলে আমাদের আসল টেবিলের মতো দেখতে একটি পিভট টেবিল তৈরি করার জন্য, এটিকে (মূলটি) ক্রসট্যাব থেকে একটি সমতল (সাধারণকৃত) তে প্রসারিত করতে হবে। এবং এটি করার সবচেয়ে সহজ উপায় হল পাওয়ার কোয়েরি অ্যাড-ইন, একটি শক্তিশালী ডেটা ট্রান্সফরমেশন টুল যা 2016 সাল থেকে Excel এ তৈরি করা হয়েছে।
এইগুলো:
- টেবিলটিকে একটি "স্মার্ট" ডাইনামিক কমান্ডে রূপান্তর করা যাক হোম - একটি টেবিল হিসাবে বিন্যাস (হোম - টেবিল হিসাবে ফর্ম্যাট).
- কমান্ড দিয়ে পাওয়ার কোয়েরিতে লোড হচ্ছে ডেটা - টেবিল / রেঞ্জ থেকে (ডেটা - টেবিল / রেঞ্জ থেকে).
- আমরা মোটের সাথে লাইনটি ফিল্টার করি (সারাংশের নিজস্ব মোট থাকবে)।
- প্রথম কলাম শিরোনামে ডান ক্লিক করুন এবং নির্বাচন করুন অন্যান্য কলাম আনকলাপ করুন (অন্যান্য কলাম আনপিভট করুন). সমস্ত অ-নির্বাচিত কলাম দুটিতে রূপান্তরিত হয় - কর্মচারীর নাম এবং তার সূচকের মান।
- কলামে যাওয়া মোটের সাথে কলাম ফিল্টার করা হচ্ছে গুণ.
- আমরা কমান্ডের সাথে ফলস্বরূপ ফ্ল্যাট (সাধারণকৃত) টেবিল অনুসারে একটি পিভট টেবিল তৈরি করি হোম — বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন... (বাড়ি — বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন...).
এখন আপনি পিভট টেবিলে উপলব্ধ কলামগুলি ফিল্টার করার ক্ষমতা ব্যবহার করতে পারেন - নাম এবং আইটেমগুলির সামনে সাধারণ চেকমার্কগুলি স্বাক্ষর ফিল্টার (লেবেল ফিল্টার) or মান অনুযায়ী ফিল্টার (মান ফিল্টার):
এবং অবশ্যই, ডেটা পরিবর্তন করার সময়, আপনাকে একটি কীবোর্ড শর্টকাট দিয়ে আমাদের ক্যোয়ারী এবং সারাংশ আপডেট করতে হবে জন্য ctrl+অল্টার+F5 বা দল ডেটা - সমস্ত রিফ্রেশ করুন (ডেটা — রিফ্রেশ সব).
পদ্ধতি 3. VBA-তে ম্যাক্রো
আগের সমস্ত পদ্ধতি, যেমন আপনি সহজেই দেখতে পাচ্ছেন, ঠিক ফিল্টারিং নয় – আমরা মূল তালিকায় কলামগুলি লুকিয়ে রাখি না, তবে মূল থেকে কলামগুলির একটি নির্দিষ্ট সেট সহ একটি নতুন টেবিল তৈরি করি। যদি উত্স ডেটাতে কলামগুলি ফিল্টার (লুকান) করার প্রয়োজন হয়, তবে একটি মৌলিকভাবে ভিন্ন পদ্ধতির প্রয়োজন, যথা, একটি ম্যাক্রো৷
ধরুন আমরা ফ্লাইতে কলামগুলি ফিল্টার করতে চাই যেখানে টেবিল হেডারে ম্যানেজারের নামটি হলুদ কক্ষ A4-এ নির্দিষ্ট করা মুখোশকে সন্তুষ্ট করে, উদাহরণস্বরূপ, "A" অক্ষর দিয়ে শুরু হয় (অর্থাৎ, "আন্না" এবং "আর্থার" পান "ফলস্বরূপ)।
প্রথম পদ্ধতির মতো, আমরা প্রথমে একটি সহায়ক পরিসর-সারি প্রয়োগ করি, যেখানে প্রতিটি ঘরে আমাদের মানদণ্ড একটি সূত্র দ্বারা পরীক্ষা করা হবে এবং যথাক্রমে দৃশ্যমান এবং লুকানো কলামগুলির জন্য যৌক্তিক মান TRUE বা FALSE প্রদর্শিত হবে:
তারপর একটি সহজ ম্যাক্রো যোগ করা যাক. শীট ট্যাবে ডান-ক্লিক করুন এবং কমান্ড নির্বাচন করুন উৎস (সোর্স কোড). খোলে উইন্ডোতে নিম্নলিখিত VBA কোডটি অনুলিপি করুন এবং পেস্ট করুন:
প্রাইভেট সাব ওয়ার্কশীট_পরিবর্তন(পরিসীমা হিসাবে বাইভাল টার্গেট) যদি টার্গেট হয়। ঠিকানা = "$A$4" তারপর রেঞ্জের প্রতিটি কক্ষের জন্য ("D2:O2") যদি সেল = সত্য হয় তাহলে সেল।EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next সেল End If End Sub এর যুক্তি নিম্নরূপ:
- সাধারণভাবে, এটি একটি ইভেন্ট হ্যান্ডলার ওয়ার্কশীট_পরিবর্তন, অর্থাৎ এই ম্যাক্রোটি বর্তমান শীটের যেকোন কক্ষে যেকোন পরিবর্তনের জন্য স্বয়ংক্রিয়ভাবে চলবে।
- পরিবর্তিত ঘরের রেফারেন্স সবসময় পরিবর্তনশীল হবে লক্ষ্য.
- প্রথমে, আমরা পরীক্ষা করি যে ব্যবহারকারী মানদণ্ড (A4) এর সাথে ঠিক সেলটি পরিবর্তন করেছেন - এটি অপারেটর দ্বারা করা হয়েছে if.
- তারপর চক্র শুরু হয় প্রতিটির জন্য… প্রতিটি কলামের জন্য TRUE/FALSE সূচক মান সহ ধূসর কক্ষের (D2:O2) উপর পুনরাবৃত্তি করতে।
- যদি পরবর্তী ধূসর ঘরের মান সত্য (সত্য) হয়, তাহলে কলামটি লুকানো হয় না, অন্যথায় আমরা এটি লুকিয়ে রাখি (সম্পত্তি গোপন).
- অফিস 365 থেকে ডায়নামিক অ্যারে ফাংশন: ফিল্টার, SORT, এবং UNIC
- পাওয়ার কোয়েরি ব্যবহার করে মাল্টিলাইন হেডার সহ পিভট টেবিল
- ম্যাক্রো কি, কিভাবে তৈরি এবং ব্যবহার করতে হয়