একাধিক ডেটা ব্যাপ্তি জুড়ে পিভট টেবিল

সমস্যা প্রণয়ন

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

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

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

পদ্ধতি 1: পাওয়ার কোয়েরি ব্যবহার করে একটি পিভটের জন্য টেবিল তৈরি করুন

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

প্রথমে, আসুন এক্সেলে একটি নতুন খালি ফাইল তৈরি করি – এতে সমাবেশ হবে এবং তারপরে একটি পিভট টেবিল তৈরি হবে।

তারপর ট্যাবে উপাত্ত (যদি আপনার এক্সেল 2016 বা তার পরে থাকে) বা ট্যাবে পাওয়ার কোয়েরি (যদি আপনার এক্সেল 2010-2013 থাকে) কমান্ডটি নির্বাচন করুন ক্যোয়ারী তৈরি করুন - ফাইল থেকে - এক্সেল (ডেটা পান — ফাইল থেকে — এক্সেল) এবং সংগ্রহ করা টেবিলের সাথে উত্স ফাইলটি নির্দিষ্ট করুন:

প্রদর্শিত উইন্ডোতে, যেকোনো শীট নির্বাচন করুন (এটি কোন ব্যাপার নয়) এবং নীচের বোতাম টিপুন পরিবর্তন (সম্পাদনা করুন):

পাওয়ার কোয়েরি ক্যোয়ারী এডিটর উইন্ডোটি এক্সেলের উপরে খোলা উচিত। প্যানেলে জানালার ডান দিকে পরামিতি অনুরোধ প্রথমটি ছাড়া স্বয়ংক্রিয়ভাবে তৈরি সমস্ত পদক্ষেপ মুছুন - উৎস (উৎস):

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

কলাম ছাড়া সব কলাম মুছুন উপাত্তএকটি কলাম শিরোনামে ডান ক্লিক করে এবং নির্বাচন করে অন্যান্য কলাম মুছুন (অপসারণ অন্যান্য কলাম):

তারপরে আপনি কলামের উপরের ডাবল তীরটিতে ক্লিক করে সংগৃহীত টেবিলের বিষয়বস্তু প্রসারিত করতে পারেন (চেকবক্স উপসর্গ হিসাবে মূল কলাম নাম ব্যবহার করুন আপনি এটি বন্ধ করতে পারেন):

আপনি যদি সবকিছু সঠিকভাবে করেন, তবে এই মুহুর্তে আপনি একটির নীচে সংগৃহীত সমস্ত টেবিলের বিষয়বস্তু দেখতে পাবেন:

এটি বোতামের সাহায্যে টেবিলের শিরোনামে প্রথম সারি বাড়াতে বাকি আছে হেডার হিসেবে প্রথম লাইন ব্যবহার করুন (শিরোনাম হিসাবে প্রথম সারি ব্যবহার করুন) ট্যাব হোম (বাড়ি) এবং ফিল্টার ব্যবহার করে ডেটা থেকে ডুপ্লিকেট টেবিল হেডার মুছে ফেলুন:

কমান্ড দিয়ে করা সবকিছু সংরক্ষণ করুন বন্ধ করুন এবং লোড করুন - বন্ধ করুন এবং লোড করুন... (বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন...) ট্যাব হোম (বাড়ি), এবং যে উইন্ডোটি খোলে সেখানে বিকল্পটি নির্বাচন করুন শুধুমাত্র সংযোগ (শুধু সংযোগ):

সবকিছু। এটা শুধুমাত্র একটি সারাংশ নির্মাণ অবশেষ. এটি করতে, ট্যাবে যান সন্নিবেশ করুন - PivotTable (ঢোকান — পিভট টেবিল), বিকল্পটি নির্বাচন করুন বাহ্যিক তথ্য উৎস ব্যবহার করুন (বাহ্যিক তথ্য উৎস ব্যবহার করুন)এবং তারপর বোতামে ক্লিক করে সংযোগ নির্বাচন করুন, আমাদের অনুরোধ. আমাদের প্রয়োজনীয় ক্ষেত্রগুলিকে সারি, কলাম এবং মান অঞ্চলে টেনে এনে পিভটের আরও তৈরি এবং কনফিগারেশন সম্পূর্ণরূপে আদর্শ উপায়ে ঘটে:

যদি ভবিষ্যতে উত্স ডেটা পরিবর্তন হয় বা আরও কয়েকটি স্টোর শীট যোগ করা হয়, তাহলে কমান্ডটি ব্যবহার করে ক্যোয়ারী এবং আমাদের সারাংশ আপডেট করার জন্য এটি যথেষ্ট হবে সব রিফ্রেশ ট্যাব উপাত্ত (ডেটা — রিফ্রেশ সব).

পদ্ধতি 2. আমরা একটি ম্যাক্রোতে UNION SQL কমান্ডের সাথে টেবিলগুলিকে একত্রিত করি

আমাদের সমস্যার আরেকটি সমাধান এই ম্যাক্রো দ্বারা উপস্থাপন করা হয়, যা কমান্ড ব্যবহার করে পিভট টেবিলের জন্য একটি ডেটা সেট (ক্যাশে) তৈরি করে UNITY এসকিউএল ক্যোয়ারী ভাষা। এই কমান্ডটি অ্যারেতে নির্দিষ্ট করা সমস্ত থেকে টেবিলকে একত্রিত করে পত্রকের নাম একটি একক ডেটা টেবিলে বইয়ের শীট। অর্থাৎ, শারীরিকভাবে বিভিন্ন শীট থেকে একের রেঞ্জ কপি এবং পেস্ট করার পরিবর্তে, আমরা কম্পিউটারের র‌্যামে একই কাজ করি। তারপর ম্যাক্রো প্রদত্ত নামের সাথে একটি নতুন শীট যোগ করে (ভেরিয়েবল ResultSheetName) এবং সংগৃহীত ক্যাশের উপর ভিত্তি করে এটির উপর একটি পূর্ণাঙ্গ (!) সারাংশ তৈরি করে।

একটি ম্যাক্রো ব্যবহার করতে, ট্যাবের ভিজ্যুয়াল বেসিক বোতামটি ব্যবহার করুন ডেভেলপার (বিকাশকারী) বা কীবোর্ড শর্টকাট অল্টার+F11. তারপরে আমরা মেনুর মাধ্যমে একটি নতুন খালি মডিউল সন্নিবেশ করি সন্নিবেশ - মডিউল এবং সেখানে নিম্নলিখিত কোড অনুলিপি করুন:

Sub New_Multi_Table_Pivot() Dim i As long dim arSQL() স্ট্রিং হিসাবে dim objPivotCache হিসাবে PivotCache হিসাবে dim objRS হিসাবে অবজেক্ট ডিম ResultSheetName হিসাবে স্ট্রিং Dim SheetsNames ভেরিয়েন্ট হিসাবে শীট নাম যেখানে ফলাফল পিভট প্রদর্শিত হবে "arSheetN" = "arSheetName" এর ফলাফল উৎস টেবিলের সাথে নাম SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'আমরা ActiveWorkbook ReDim arSQL(1 থেকে (UBound(SheetsNames) + 1) এর সাথে SheetsNames থেকে শীট থেকে টেবিলের জন্য একটি ক্যাশে তৈরি করি ) i = LBound (SheetsNames) থেকে UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" এর জন্য পরবর্তী i সেট objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL"), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName,"; Extended Properties=""Excel 8.0;" ""), vbNullString ) এর সাথে শেষ করুন 'এর ফলে পিভট টেবিলটি প্রদর্শনের জন্য শীটটি পুনরায় তৈরি করুন পরবর্তী অ্যাপ্লিকেশন পুনরায় শুরু করুন। ডিসপ্লে অ্যালার্ট = মিথ্যা ওয়ার্কশীট(রেজাল্টশিটনাম)। সেট wsPivot = ওয়ার্কশীট মুছুন। wsPivo যোগ করুন। t. Name = ResultSheetName 'এই শীটে জেনারেট করা ক্যাশের সারাংশ প্রদর্শন করুন objPivotCache = ActiveWorkbook.PivotCaches সেট করুন।Add(xlExternal) objPivotCache সেট করুন।রেকর্ডসেট = objRS সেট objRS = wsPjTivestable3"এর সাথে কিছু নেই। objPivotCache = কিছুই নেই রেঞ্জ("A3")। শেষ সাব সহ শেষ নির্বাচন করুন    

সমাপ্ত ম্যাক্রো তারপর একটি কীবোর্ড শর্টকাট দিয়ে চালানো যেতে পারে অল্টার+F8 অথবা ট্যাবে ম্যাক্রো বোতাম ডেভেলপার (ডেভেলপার - ম্যাক্রো).

এই পদ্ধতির অসুবিধা:

• তথ্য আপডেট করা হয় না কারণ ক্যাশে উৎস টেবিলের সাথে কোন সংযোগ নেই। আপনি যদি উৎস ডেটা পরিবর্তন করেন, তাহলে আপনাকে আবার ম্যাক্রো চালাতে হবে এবং আবার সারাংশ তৈরি করতে হবে।
• শীটের সংখ্যা পরিবর্তন করার সময়, ম্যাক্রো কোড (অ্যারে) সম্পাদনা করা প্রয়োজন পত্রকের নাম).
  

কিন্তু শেষ পর্যন্ত আমরা একটি বাস্তব পূর্ণাঙ্গ পিভট টেবিল পাই, যা বিভিন্ন শীট থেকে বিভিন্ন রেঞ্জের উপর নির্মিত:

ভাল খবর!

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

	 প্রদানকারী=Microsoft.Jet.OLEDB.4.0;  

করুন:

	প্রদানকারী=Microsoft.ACE.OLEDB.12.0;  

এবং মাইক্রোসফ্ট ওয়েবসাইট থেকে অ্যাক্সেস থেকে বিনামূল্যে ডেটা প্রসেসিং ইঞ্জিন ডাউনলোড এবং ইনস্টল করুন - Microsoft Access Database Engine 2010 Redistributable

পদ্ধতি 3: Excel এর পুরানো সংস্করণ থেকে PivotTable উইজার্ড একত্রিত করুন

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

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

যোগ করা বোতামে ক্লিক করার পরে, আপনাকে উইজার্ডের প্রথম ধাপে উপযুক্ত বিকল্পটি নির্বাচন করতে হবে:

এবং তারপরে পরবর্তী উইন্ডোতে, প্রতিটি ব্যাপ্তি পালাক্রমে নির্বাচন করুন এবং এটিকে সাধারণ তালিকায় যুক্ত করুন:

কিন্তু, আবার, এটি একটি পূর্ণাঙ্গ সারাংশ নয়, তাই এটি থেকে খুব বেশি আশা করবেন না। আমি শুধুমাত্র খুব সাধারণ ক্ষেত্রে এই বিকল্পটি সুপারিশ করতে পারি।

• PivotTables দিয়ে রিপোর্ট তৈরি করা
• PivotTables-এ গণনা সেট আপ করুন
• ম্যাক্রো কি, কিভাবে ব্যবহার করতে হয়, VBA কোড কোথায় কপি করতে হয় ইত্যাদি।
• একাধিক শীট থেকে একটিতে ডেটা সংগ্রহ (PLEX অ্যাড-অন)