এক্সেল সাবটোটালে দক্ষতা: সঠিক ডেটা সারাংশের জন্য ৭টি প্রমাণিত কৌশল

পদ্ধতি ১: এক্সেলে স্বয়ংক্রিয়ভাবে সাবটোটাল ইনসার্ট করা

1. একটি র‍্যান্ডম সেল সিলেক্ট করুন > Data ট্যাবে যান > Sort-এ ক্লিক করুন।

2. Sort ডায়ালগ বক্স পপ আপ হবে।
3. Sort by ফিল্ডে সাবটোটাল ইনসার্ট করার জন্য কলামটি বেছে নিন।
4. Sort On ফিল্ডে Cell Values রাখুন।
5. অর্ডার হবে आरোহী: A to Z।
6. OK-এ ক্লিক করুন।

7. এটি Sales Reps কলামকে অ্যালফাবেটিক্যালি সর্ট করবে।

বিল্ট-ইন ফিচারটি ব্যবহার করুন, যা এক্সেলে সাবটোটাল শর্টকাট হিসেবে পরিচিত।

1. রেঞ্জের একটি সেলে ক্লিক করুন > Data ট্যাব থেকে Outline গ্রুপের অধীনে Subtotal বেছে নিন।

2. Subtotal ডায়ালগ বক্স প্রদর্শিত হবে। আমরা SUM ফাংশন ব্যবহার করে সাবটোটাল ইনসার্ট করব।
3. At each change in ফিল্ডে সাবটোটাল ইনসার্ট করার ডেটা রাখুন।
4. Use function ফিল্ডে SUM ফাংশন বেছে নিন মোট মান ক্যালকুলেট করতে।
5. OK-এ ক্লিক করুন।

এখন আপনি এক্সেলে সাবটোটাল ইনসার্ট করতে পারবেন। Sales Reps কলামে সাবটোটাল এবং গ্র্যান্ড টোটাল থাকবে।

5. শীর্ষ বাম কোণে ১, ২, ৩ নম্বরযুক্ত আইকনটি নির্দেশ করবে কীভাবে এক্সেলে সাবটোটাল ব্যবহার করবেন। এটি গ্রুপ হ্যান্ডল করার এবং সাবটোটাল দেখানো/লুকানোয় সাহায্য করে।

পদ্ধতি ২: একাধিক সাবটোটাল যোগ করা

২.১. ভিন্ন কলামগুলিতে একাধিক সাবটোটাল ইনসার্ট করা

পূর্বের সেকশনে আমরা দেখিয়েছি কীভাবে Sales Reps কলামে সাবটোটাল প্রয়োগ করা যায়। এখন Category आधায়রেও একটি সাবটোটাল যোগ করব।

1. Data ট্যাব থেকে Sort ডায়ালগ বক্স খুলুন।
2. Sales Reps দ্বারা সর্ট করুন।
3. একটি লেভেল যোগ করুন।
4. আমরা Sales Reps দ্বারা সর্ট করেছিলাম; এখন Category দ্বারা সর্ট করব।
5. OK-এ ক্লিক করুন।

6. Data ট্যাব থেকে Subtotal ডায়ালগ বক্স খুলুন (পদ্ধতি-১-এ বর্ণিত)।
7. At each change in ফিল্ডে Sales Reps বেছে নিন।

8. এক্সেল Sales Reps কলামে সাবটোটাল ইনসার্ট করবে।
9. আবার Subtotal পপ-আপ খুলুন।
10. এবারের বার Category-এ প্রতিটি পরিবর্তন নিশ্চিত করুন।
11. Replace Current Subtotals ফিল্ডটি অনচেক থাকতে হবে। যদি চেক করা থাকে, তবে আগের সাবটোটাল ক্লিয়ার হয়ে যাবে এবং একক সাবটোটাল ফলাফল আসবে।
12. OK-এ ক্লিক করুন।

একাধিকবার গ্রুপ করে এক্সেলে সাবটোটাল ইনসার্ট করুন।

আরও সাবটোটাল যোগ করতে চাইলে Subtotal বক্সে অন্য পরিবর্তন বেছে নিন।

২.২. একই কলামে একাধিক সাবটোটাল খুঁজে বের করা

একই কলামে একাধিক সাবটোটাল ইনসার্ট করুন। মোট ক্যালকুলেট করার পর গড় এবং স্ট্যান্ডার্ড ডেভিয়েশন ক্যালকুলেট করব। এর জন্য দুটি আর ফাংশন লাগবে। AVERAGE ফাংশন বেছে নিন এবং দ্বিতীয়বার StdDev ফাংশন বেছে নিন।

SUM ফাংশন ব্যবহার করে Sales Reps-এ সাবটোটাল ইনসার্ট করার পর (পদ্ধতি-১ থেকে প্রাপ্ত) শুরু করুন।

1. Subtotal বক্স ২ বার আর খুলুন।
2. AVERAGE ফাংশন বেছে নিন এবং দ্বিতীয়বার StdDev ফাংশন বেছে নিন।

3. AVERAGE ফাংশনটি নির্বাচিত কলামগুলোর গড় বের করবে।
4. StdDev ফাংশনটি স্ট্যান্ডার্ড ডেভিয়েশন ক্যালকুলেট করবে।

এই ফাংশনগুলো অনুযায়ী আপনার ডেটাতে সাবটোটাল ইনসার্ট হবে।

পদ্ধতি ৩: এক্সেল টেবিলে সাবটোটাল ইনসার্ট করা

এক্সেল টেবিলে সরাসরি সাবটোটাল প্রয়োগ করা যায় না। Subtotal ফিচারটি এখানে কাজ করে না।

সাবটোটাল ইনসার্ট করতে চাইলে প্রথমে টেবিলকে রেঞ্জে রূপান্তর করতে হবে।

1. টেবিলের একটি র‍্যান্ডম সেল সিলেক্ট করুন, Table Design ট্যাবে যান > Tools গ্রুপের অধীনে Convert to Range বেছে নিন।

2. মাইক্রোসফট এক্সেল জিজ্ঞেস করবে টেবিলকে রেঞ্জে রূপান্তর করতে চান কিনা। কনফার্ম করতে Yes-এ ক্লিক করুন।

এখন এক্সেল টেবিলকে নরমাল রেঞ্জে রূপান্তর করবে। আপনি সাবটোটাল ফিচার প্রয়োগ করে সাবটোটাল ইনসার্ট করতে পারবেন।

পদ্ধতি ৪: ফিল্টারসহ এক্সেলে SUBTOTAL ফর্মুলা প্রয়োগ করা

আমরা Subtotal ফিচারটি ব্যবহার করেছি। এক্সেলে SUBTOTAL ফাংশন-ও আছে সাবটোটাল যোগ করার জন্য। ফিল্টার করা ডেটার জন্য ফাংশনটি ব্যবহার করুন।

SUBTOTAL ফাংশনের সিনট্যাক্স:

SUBTOTAL(function_num,ref1,[ref2],…)

প্রথম আর্গুমেন্ট: function_num কিছু নম্বর (1-9 এবং 101-109) দিয়ে বর্ণনা করা হয়েছে যা বিভিন্ন ফাংশন নির্দেশ করে।

৪.১. ফিল্টার আউট করা সারিগুলিতে সাবটোটাল

নিচের SUBTOTAL ফর্মুলা প্রয়োগ করে সাবটোটাল ইনসার্ট করা হয়েছে।

যেহেতু প্রথম আর্গুমেন্ট: ৯ ব্যবহার করা হয়েছে, এটি ফিল্টার করা সেল উপেক্ষা করবে।

1. Sales Reps কলামের ড্রপ-ডাউন অ্যারোতে ক্লিক করে একটি নাম (যেমন Adam) বেছে নিন ফিল্টার করতে।

ফাংশনটি শুধু ফিল্টার করা সেলগুলোর জন্য সাবটোটাল ক্যালকুলেট করবে, বাকিগুলো উপেক্ষা করবে।

নিচের ছবিটি SUBTOTAL ফাংশনের আউটপুট উভয় আর্গুমেন্ট ৯ এবং ১০৯-এর জন্য দেখাচ্ছে।

৪.২. ম্যানুয়ালি হাইড করা সারিগুলিতে সাবটোটাল

ম্যানুয়ালি হাইড করা সারিগুলির জন্য SUBTOTAL ফাংশনের আউটপুট দেখুন; সারির নম্বরে রাইট-ক্লিক করে কন্টেক্সট মেনু থেকে Hide বেছে নিন।

আর্গুমেন্ট ৯ এবং ১০৯-এর আউটপুট তুলনা করুন। আর্গুমেন্ট ৯ ম্যানুয়ালি হাইড করা সারিগুলির সেলগুলো অন্তর্ভুক্ত করবে, কিন্তু আর্গুমেন্ট ১০৯ সেগুলো উপেক্ষা করবে।

পদ্ধতি ৫: VBA কোড দিয়ে সাবটোটাল বের করা

1. ALT+F11 চাপে Visual Basic Editor উইন্ডো লঞ্চ করুন।
2. টুলবারের Insert-এ ক্লিক করুন > Module বেছে নিন।

3. Module উইন্ডোতে আপনার VBA কোড ইনসার্ট করুন।

কোড:

Sub Calculate_Subtotal()
Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer
Application.ScreenUpdating = False
iValue = 5
xValue = iValue
Range("B5").CurrentRegion.Offset(1).Sort Range("B6"), 1
Do While Range("B" & iValue) <> ""
If Range("B" & iValue) <> Range("B" & (iValue + 1)) Then
Rows(iValue + 1).Insert
Range("B" & (iValue + 1)) = "Subtotal " & Range("B" & iValue).Value
For iColumn = 7 To 8 'Columns to Calculate Sum
Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
Next iColumn
Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
iValue = iValue + 2
xValue = iValue
Else
iValue = iValue + 1
End If
Loop
Application.ScreenUpdating = True
End Sub

4. Run বাটনে ক্লিক করে VBA কোড এক্সিকিউট করুন।

এখন এক্সেল Sales Reps-এর ভিত্তিতে আপনার ডেটাতে সাবটোটাল ইনসার্ট করবে।

পদ্ধতি ৬: এক্সেল পিভট টেবিলে সাবটোটাল যোগ করা

পিভট টেবিলে চারটি ফিল্ড আছে, এবং আমাদের ডেটা ব্যবহার করে সেগুলোতে ফরম্যাটেড পিভট টেবিল তৈরি করা হয়েছে:
১. Filter: Sales Reps

1. Columns: Blank
2. Rows: Category, Product
3. Values: Quantity, Unit Price, Total Price, and Profit

1. Design ট্যাবে যান > Layout গ্রুপের অধীনে Subtotals অপশনের ড্রপডাউনে ক্লিক করুন।

2. ড্রপডাউন মেনু থেকে Show all Subtotals at Bottom of Group বেছে নিন।

পিভট টেবিল এখন Row Labels-এর ভিত্তিতে প্রতিটি গ্রুপের নিচে সাবটোটাল ইনসার্ট করবে। টেবিলের নিচে গ্রুপের গ্র্যান্ড টোটাল যোগ হবে।

পদ্ধতি ৭: পাওয়ার কোয়েরি ব্যবহার করে সাবটোটাল ইনসার্ট করা

পাওয়ার কোয়েরি ব্যবহার করে গ্রুপের জন্য সাবটোটাল ইনসার্ট করুন।

1. রেঞ্জের একটি সেলে ক্লিক করুন > Data ট্যাবে যান > Get & Transform Data অধীনে From Table/Range-এ ক্লিক করুন।

2. Create Table ডায়ালগ বক্সে OK-এ ক্লিক করুন।

3. এক্সেল আপনাকে Power Query Editor উইন্ডোতে নিয়ে যাবে।
4. ডেটাতে যেসব কলাম (যেমন Sales Reps এবং Category) দিয়ে কাজ করতে চান সেগুলো সিলেক্ট করুন > Transform ট্যাব থেকে Group By আইকনে ক্লিক করুন।

4. Group By ডায়ালগ বক্সে একটি নতুন কলামের নাম দিন।
5. আপনি যে ধরনের অপারেশন করতে চান সেটি বেছে নিন (যেমন SUM)।
6. Column ফিল্ডে ক্যালকুলেশন করার কলাম বেছে নিন (যেমন Total Price)।
7. আরও গ্রুপ করতে Add Aggregation-এ ক্লিক করুন।

5. Profit-এর সাবটোটাল ক্যালকুলেট করতে Column ফিল্ডে Profit এবং Operation ফিল্ডে Sum বেছে নিন। এই অগ্রেশনকে একটি নাম দিন (যেমন Profit)।
6. OK-এ ক্লিক করুন।

6. Sales Reps এবং Category-এর ভিত্তিতে ইনসার্ট করা সাবটোটাল দেখতে পাবেন।
7. Home ট্যাবে যান > Close & Load-এর ড্রপডাউনে ক্লিক করুন > Close & Load বেছে নিন।

Power Query এডিটর আপনার গ্রুপ করা ডেটা সাবটোটালসহ এক্সেল ওয়ার্কশীতে লোড করবে।

এক্সেলে সাবটোটাল সরানোর পদ্ধতি

1. Data ট্যাব থেকে Subtotals ডায়ালগ বক্স খুলুন > Remove all-এ ক্লিক করুন।

2. গ্রুপ এবং সাবটোটাল সরিয়ে আপনার ডেটা নরমাল রেঞ্জে রূপান্তরিত হবে।

নোট: সাবটোটাল সরানোর পর ডেটা সর্ট করা অবস্থায় থাকে, আগের অনসর্টেড অবস্থায় ফিরে যায় না।

প্রশ্নোত্তর

১. এক্সেলে সাবটোটাল কেন সরাতে পারছি না?

সাবটোটাল কাজ না করার кільতিন কারণ হতে পারে।

i) প্রোটেক্টেড ওয়ার্কশীট: আপনার শীট যদি পাসওয়ার্ড দিয়ে প্রোটেক্টেড থাকে, তবে সেটি আনপ্রোটেক্ট করতে হবে।

ii) শেয়ার্ড ওয়ার্কবুক: ওয়ার্কবুকটি আনশেয়ার করতে হতে পারে বা চেক করতে হবে একাধিক ইউজারের মধ্যে শেয়ার করা হয়েছে কিনা।

iii) কাস্টম ফর্মুলা বা ম্যাক্রো: যদি কাস্টম ফর্মুলা বা ম্যাক্রো দিয়ে সাবটোটাল যোগ করা হয়ে থাকে, তবে সেগুলো মডিফাই বা ডিলিট করতে হতে পারে।

২. এক্সেলে সাবটোটাল কিভাবে কollapse বা expand করবেন?

গ্রুপড আউটলাইন ফিচার ব্যবহার করে এক্সেলে সাবটোটাল collapse বা expand করা যায়। এক্সেল অটোমেটিক্যালি আউটলাইন সিম্বল যোগ করে যা সাবটোটাল গ্রুপ collapse বা expand করতে দেয়। গ্রুপ সারির পাশে মাইনাস (-) বা প্লাস (+) সিম্বলে ক্লিক করলে যথাক্রমে সাবটোটাল collapse বা expand হবে।

৩. ডেটা পরিবর্তন হলে এক্সেলে সাবটোটাল কি অটোমেটিক্যালি আপডেট হয়?

হ্যাঁ, ডেটা পরিবর্তন হলে সাবটোটাল অটোমেটিক্যালি আপডেট করতে এক্সেল সেট করা যায়। সাবটোটাল সারিতে রাইট-ক্লিক করে কন্টেক্সট মেনু থেকে “Refresh” বেছে নিয়ে ম্যানুয়ালি রিফ্রেশ করাও যায়। যদি অটোমেটিক্যালি আপডেট না হয়, তবে নিশ্চিত করুন আপনার ওয়ার্কবুক ফর্মুলা অটো-ক্যালকুলেট করছে।

৪. এক্সেলে AGGREGATE এবং SUBTOTAL ফাংশন একই কি?

না, AGGREGATE এবং SUBTOTAL ফাংশনে পার্থক্য আছে যদিও সিনট্যাক্স মিলতেই পারে।

আর্টিকেল থেকে মূল বिन्दুসমূহ

• এক্সেলের "Subtotal" ফিচার নির্দিষ্ট কলামের ভিত্তিতে ডেটা গ্রুপের জন্য সাবটোটাল ইনসার্ট করতে দেয়।
• সাবটোটাল ইনসার্ট করতে ডেটা রেঞ্জ সিলেক্ট করুন, "Data" ট্যাবে যান, "Outline" গ্রুপের "Subtotal" বাটনে ক্লিক করুন।
• একাধিক কলাম সিলেক্ট করে সাবটোটালের একাধিক লেভেল যোগ করা যায়।
• সাবটোটাল collapse বা expand করা যায় গ্রুপড আউটলাইন ফিচার দিয়ে, যা মাইনাস (-) এবং প্লাস (+) সিম্বল দ্বারা ইঙ্গিত করা হয়।
• এক্সেলের টেবিল বা ডাইনামিক নেমড রেঞ্জ ব্যবহার করে ডেটা পরিবর্তন হলে সাবটোটাল অটোমেটিক্যালি আপডেট করা যায়।

প্র্যাকটিস ওয়ার্কবুক ডাউনলোড করুন

এখান থেকে প্র্যাকটিস ওয়ার্কবুক ডাউনলোড করতে পারেন।

<< Subtotal in Excel | Learn Excel-এ ফিরে যান

ফ্রি অ্যাডভান্সড এক্সেল এক্সারসাইজ সমাধানসহ পান!