টেক্সট মান সহ পিভট টেবিল

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

আসুন এই সীমাবদ্ধতাটি ঘেঁষে নেওয়ার চেষ্টা করি এবং অনুরূপ পরিস্থিতিতে একটি "দুটি ক্রাচ" নিয়ে আসা যাক।

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

সুবিধার জন্য, কমান্ডটি ব্যবহার করে আগাম প্রাথমিক ডেটা "স্মার্ট" দিয়ে টেবিলটি তৈরি করা যাক হোম - একটি টেবিল হিসাবে বিন্যাস (হোম - টেবিল হিসাবে ফর্ম্যাট) এবং তার একটি নাম দিন deliveries ট্যাব রচয়িতা (নকশা). ভবিষ্যতে, এটি জীবনকে সহজ করবে, কারণ। টেবিলের নাম এবং এর কলামগুলি সরাসরি সূত্রগুলিতে ব্যবহার করা সম্ভব হবে।

পদ্ধতি 1. সবচেয়ে সহজ – পাওয়ার কোয়েরি ব্যবহার করুন

Power Query হল Excel এ ডেটা লোড এবং রূপান্তর করার জন্য একটি অতি শক্তিশালী টুল। এই অ্যাড-ইনটি 2016 সাল থেকে ডিফল্টরূপে এক্সেলে তৈরি করা হয়েছে৷ আপনার যদি এক্সেল 2010 বা 2013 থাকে তবে আপনি এটি আলাদাভাবে ডাউনলোড এবং ইনস্টল করতে পারেন (সম্পূর্ণ বিনামূল্যে)৷

পুরো প্রক্রিয়াটি, স্পষ্টতার জন্য, আমি নিম্নলিখিত ভিডিওতে ধাপে ধাপে বিশ্লেষণ করেছি:

যদি পাওয়ার কোয়েরি ব্যবহার করা সম্ভব না হয়, তাহলে আপনি অন্য উপায়ে যেতে পারেন - একটি পিভট টেবিল বা সূত্রের মাধ্যমে। 

পদ্ধতি 2. সহায়ক সারাংশ

আমাদের মূল টেবিলে আরও একটি কলাম যোগ করা যাক, যেখানে একটি সাধারণ সূত্র ব্যবহার করে আমরা টেবিলের প্রতিটি সারির সংখ্যা গণনা করি:

স্পষ্টতই, -1 প্রয়োজন, কারণ আমাদের টেবিলে একটি এক-লাইন হেডার রয়েছে। যদি আপনার টেবিলটি শীটের শুরুতে না থাকে, তাহলে আপনি একটি সামান্য জটিল, কিন্তু সর্বজনীন সূত্র ব্যবহার করতে পারেন যা বর্তমান সারি এবং টেবিলের শিরোনামের সংখ্যার পার্থক্য গণনা করে:

এখন, একটি আদর্শ উপায়ে, আমরা আমাদের ডেটার উপর ভিত্তি করে পছন্দসই ধরণের একটি পিভট টেবিল তৈরি করব, কিন্তু মান ক্ষেত্রে আমরা ক্ষেত্রটি বাদ দেব। লাইন সংখ্যা আমরা যা চাই তার পরিবর্তে আধার:

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

অতিরিক্তভাবে, আপনি ট্যাবে গ্র্যান্ড এবং সাবটোটাল বন্ধ করতে পারেন কনস্ট্রাক্টর - সাধারণ মোট и সাবটোটাল (ডিজাইন — গ্র্যান্ড টোটাল, সাবটোটাল) এবং একই জায়গায় বোতামের সাহায্যে সারাংশটিকে আরও সুবিধাজনক টেবিল লেআউটে স্যুইচ করুন মকআপ রিপোর্ট করুন (রিপোর্ট লেআউট).

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

এখন সারাংশটি অনুলিপি করা যাক (একই শীটে বা অন্য) এবং এটিকে মান হিসাবে পেস্ট করুন এবং তারপরে আমাদের সূত্রটি মান অঞ্চলে প্রবেশ করান, যা সারাংশে পাওয়া লাইন নম্বর দ্বারা ধারক কোডটি বের করবে:

ক্রিয়া IF (আইএফ), এই ক্ষেত্রে, সারাংশের পরবর্তী ঘরটি খালি নেই তা পরীক্ষা করে। যদি খালি থাকে, তাহলে একটি খালি টেক্সট স্ট্রিং “” আউটপুট করুন, অর্থাৎ ঘরটি ফাঁকা রাখুন। যদি খালি না হয়, তাহলে কলাম থেকে এক্সট্রাক্ট করুন আধার উত্স টেবিল deliveries ফাংশন ব্যবহার করে সারি সংখ্যা দ্বারা সেল সামগ্রী এর INDEX (INDEX).

সম্ভবত এখানে কেবলমাত্র খুব স্পষ্ট নয় তা হল দ্বৈত শব্দ আধার সূত্রে লেখার এমন অদ্ভুত রূপ:

সরবরাহ [[পাত্র]:[পাত্র]]

… শুধুমাত্র কলাম রেফারেন্স প্রয়োজন আধার পরম ছিল (সাধারণ "নন-স্মার্ট" টেবিলের জন্য $ চিহ্ন সহ একটি রেফারেন্সের মতো) এবং আমাদের সূত্রটি ডানদিকে অনুলিপি করার সময় পার্শ্ববর্তী কলামগুলিতে স্লিপ করেনি৷

ভবিষ্যতে, উৎস টেবিলে ডেটা পরিবর্তন করার সময় deliveries, আমাদের অবশ্যই মনে রাখতে হবে যে আমাদের সহায়ক সারাংশ লাইন নম্বরের সাথে আপডেট করার জন্য এটিতে ডান-ক্লিক করে এবং কমান্ডটি বেছে নিয়ে আপডেট এবং সংরক্ষণ করুন (রিফ্রেশ).

পদ্ধতি 3. সূত্র

এই পদ্ধতিতে একটি মধ্যবর্তী পিভট টেবিল তৈরি এবং ম্যানুয়াল আপডেট করার প্রয়োজন নেই, তবে এক্সেলের "ভারী অস্ত্র" - ফাংশন ব্যবহার করে SUMMESLIMN (SUMIFS). সংক্ষিপ্তসারে সারি নম্বরগুলি দেখার পরিবর্তে, আপনি এই সূত্রটি ব্যবহার করে তাদের গণনা করতে পারেন:

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

অবশ্যই, এই ক্ষেত্রে, আপনাকে আর সারাংশ আপডেট করার বিষয়ে চিন্তা করতে হবে না, তবে বড় টেবিলে, ফাংশনটি সুমেস্লি লক্ষণীয়ভাবে ধীর হতে পারে। তারপরে আপনাকে সূত্রগুলির স্বয়ংক্রিয় আপডেট বন্ধ করতে হবে, বা প্রথম পদ্ধতিটি ব্যবহার করতে হবে - একটি পিভট টেবিল।

যদি সারাংশের উপস্থিতি আপনার প্রতিবেদনের জন্য খুব উপযুক্ত না হয়, তাহলে আপনি এটি থেকে চূড়ান্ত টেবিলে সারি নম্বরগুলি বের করতে পারেন, যেমনটি আমরা করেছি, কিন্তু ফাংশনটি ব্যবহার করে PIVOT.TABLE.DATA পান (GET.PIVOT.DATA). কিভাবে এটি করতে এখানে পাওয়া যাবে.

• কিভাবে একটি পিভট টেবিল ব্যবহার করে একটি প্রতিবেদন তৈরি করতে হয়
• কিভাবে পিভট টেবিলে গণনা সেট আপ করবেন
• SUMIFS, COUNTIFS ইত্যাদি সহ নির্বাচনী গণনা।