পাওয়ার কোয়ারি, পাওয়ার পিভট এবং ভিবিএসহাসে উন্নত এক্সেল ড্যাশবোর্ড তৈরি করুন - ডেটা থেকে ইনসাইট পর্যন্ত

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

এই টিউটোরিয়ালে, আমরা দেখব কিভাবে পাওয়ার কোয়ারি, পাওয়ার পিভট, এবং ভিবিএস ব্যবহার করে উন্নত এক্সেল ড্যাশবোর্ড তৈরি করা যায়।

চলুন একটি কাল্পনিক রিটেইল কোম্পানির সেলস ড্যাশবোর্ড তৈরি করি যা একাধিক অঞ্চলে প্রোডাক্ট বেচে। আমরা নিচের টেবিলগুলো সহ ডেটাসেট নিয়ে কাজ করব।

• Sales - ট্রানজ্যাকশন ডেটা ধারণ করে।
• Products - প্রোডাক্ট ডিটেইল এবং ক্যাটাগরি।
• Customers - কাস্টমার ইনফরমেশন।
• Regions - ভৌগোলিক ইনফরমেশন।

স্টেপ ১: পাওয়ার কোয়ারি দিয়ে ডেটা ইম্পোর্ট করুন

ডেটা ইম্পোর্ট:

• ডেটা ট্যাবে যান >> Get Data সিলেক্ট করুন >> From File সিলেক্ট করুন >> From Text/CSV সিলেক্ট করুন।
• আপনার sales_data.txt ফাইলে নেভিগেট করুন >> Import-এ ক্লিক করুন।

• পাওয়ার কোয়ারি এডিটর খুললে, ডেটা রিভিউ করুন এবং এই ট্রান্সফরমেশনগুলো করুন:
• ডেটা টাইপ পরিবর্তন করুন।
• যেকোনো কলামে রাইট-ক্লিক করুন >> Change Data Type সিলেক্ট করুন >> Data Type সিলেক্ট করুন।
  • OrderDate to Date।
  • Quantity to Whole Number।
  • UnitPrice এবং Discount to Decimal Number।

• Remove Duplicates ব্যবহার করে ডুপ্লিকেট রো রিমুভ করুন।
• Products.csv, Customers.csv, এবং Dates.csv-র জন্য ইম্পোর্ট প্রসেস রিপিট করুন, উপযুক্ত ডেটা টাইপ ট্রান্সফরমেশন প্রয়োগ করে।

পাওয়ার কোয়ারি দিয়ে ডেটা ট্রান্সফর্ম করুন:

আমাদের সেলস ডেটাকে পাওয়ার কোয়ারি ব্যবহার করে টাস্ক সম্পাদন করে উন্নত করব।

ক্যালকুলেটেড কলাম যোগ করুন:

• পাওয়ার কোয়ারি এডিটরে, Sales ডেটা সিলেক্ট করুন।
• Add Column ট্যাবে যান >> Custom Column সিলেক্ট করুন।
• নাম দিন: Revenue।
• নিচের ফর্মুলা ইনসার্ট করুন।

= [Quantity] * [UnitPrice] * (1-[DiscountRate]) 

• OK-এ ক্লিক করুন।

• আরেকটি কাস্টম কলাম যোগ করুন।
• নাম দিন: Profit।
• নিচের ফর্মুলা ইনসার্ট করুন।

Profit = [Revenue] - ([Quantity] * [UnitCost]) 

• OK-এ ক্লিক করুন।
• Products টেবিলের সাথে মার্জ করতে হবে Cost পেতে।

অতিরিক্ত ইনসাইটের জন্য টেবিল মার্জ করুন:

• Sales ডেটা খোলা অবস্থায় পাওয়ার কোয়ারি এডিটরে।
• Home ট্যাবে যান >> রিবন থেকে Merge Queries-এ ক্লিক করুন।
  • ProductID Sales টেবিল থেকে সিলেক্ট করুন।
  • Products টেবিল সিলেক্ট করুন এবং ProductID-এ জয়েন করুন।
  • OK-এ ক্লিক করুন।

• Expand Table অপশনে ক্লিক করুন >> শুধু UnitCost কলাম সিলেক্ট করুন আনতে।
• OK-এ ক্লিক করুন।

• এখন Profit কলামটিকে Expanded Products স্টেপের নিচে টেনে আনুন।
• আপনি প্রফিট অমাউন্ট পাবেন।

• ডেটা ট্রান্সফরমেশন শেষ হলে।
• Close & Load To…-এ ক্লিক করুন।

• Import Data বক্সে;
  • Only Create Connection সিলেক্ট করুন।
  • সभी চারটি টেবিলের জন্য Add this data to Data Model সিলেক্ট করুন।
  • OK-এ ক্লিক করুন।

স্টেপ ২: পাওয়ার পিভট দিয়ে ডেটা মডেল তৈরি করুন

পাওয়ার পিভট খুলুন:

যদি রিবনে পাওয়ার পিভট না থাকে, তবে এটি ইনেবল করুন।

• File ট্যাবে যান >> Options সিলেক্ট করুন >> Add-ins সিলেক্ট করুন।
• Manage বক্সে >> COM Add-ins সিলেক্ট করুন >> Go সিলেক্ট করুন।

• Microsoft Power Pivot for Excel সিলেক্ট করুন।
• OK-এ ক্লিক করুন।

• Power Pivot ট্যাবে যান >> Manage সিলেক্ট করুন।
• এটি পাওয়ার কোয়ারি থেকে ইম্পোর্ট করা ডেটা খুলবে।

রিলেশনশিপ তৈরি করুন:

• Home ট্যাবে যান >> Diagram View সিলেক্ট করুন।
  • Sales[ProductID] থেকে Products[ProductID]-এ টেনে আনুন।
  • Sales[CustomerID] থেকে Customers[CustomerID]-এ টেনে আনুন।
  • Customers[RegionID] থেকে Regions[RegionID]-এ টেনে আনুন।

• অথবা Design ট্যাবে যান >> Create Relationship সিলেক্ট করুন।
• তারপর মিলে যাওয়া কলাম সিলেক্ট করুন।

ক্যালকুলেটেড মেজার তৈরি করুন:

• পাওয়ার পিভটে, Sales টেবিলে ক্লিক করুন।
• Home ট্যাবে যান >> Measures সিলেক্ট করুন >> New Measure-এ ক্লিক করুন।
• অথবা, Home ট্যাবে যান >> Calculation Area সিলেক্ট করুন।
• নিচে ক্যালকুলেটেড মেজারগুলো ইনসার্ট করুন:

• Total Revenue:

Total Revenue := SUM(Sales[Revenue]) 

• Total Profit:

Total Profit := SUM(Sales[Profit]) 

• Profit Margin:

Profit Margin := DIVIDE([Total Profit], [Total Revenue], 0) 

• Total orders:

Total Orders:=COUNTA(Sales[OrderID]) 

• Average Order Value:

Average Order Value:=DIVIDE([Total Revenue], DISTINCTCOUNT(Sales[OrderID]), 0) 

• YTD Revenue:

YTD Revenue:=CALCULATE([Total Revenue], DATESYTD(Sales[OrderDate])) 

• Previous Year Revenue:

Previous Year Revenue:=CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Sales[OrderDate])) 

• YOY Growth:

YOY Growth := DIVIDE([Total Revenue] - [Previous Year Revenue], [Previous Year Revenue], 0) 

স্টেপ ৩: ড্যাশবোর্ড কম্পোনেন্ট তৈরি করতে পিভট টেবিল ব্যবহার করুন

পিভট টেবিল তৈরি করুন

• Insert ট্যাবে যান >> PivotTable সিলেক্ট করুন >> From Data Model সিলেক্ট করুন।
• পাওয়ার পিভটে, Home ট্যাবে যান >> Pivot Table সিলেক্ট করুন।

• Create PivotTable বক্সে;
  • New Worksheet সিলেক্ট করুন।
  • OK-এ ক্লিক করুন।

• Total Revenue, Total Profit, Profit Margin, এবং Growth-এর জন্য আলাদা পিভট টেবিল তৈরি করুন।
• এগুলো আপনার ড্যাশবোর্ড স্ট্রাকচারের সাথে আলাইন করে সেলে রাখুন।
• উপযুক্ত কয়েন্সি বা পারসেন্টেজ হিসেবে ফরম্যাট করুন।

চার্ট এবং ভিজ্যুয়ালাইজেশন

Revenue Trend Chart:

• Data Model থেকে একটি PivotTable তৈরি করুন।
  • Rows: Sales[OrderDate[Month]]
  • Values: Total Revenue
• PivotTable Analyze ট্যাবে যান >> Clustered Column Chart সিলেক্ট করুন।

Top Products Chart:

• Data Model থেকে একটি PivotTable তৈরি করুন।
  • Rows: Products[ProductName]
  • Values: Total Revenue
• Total Revenue ডিসেন্ডিং অর্ডারে সোর্ট করুন, টপ ১০-এ ফিল্টার করুন।

• PivotTable Analyze ট্যাবে যান >> Clustered Bar Chart সিলেক্ট করুন।

Category Performance:

• Data Model থেকে একটি PivotTable তৈরি করুন।
  • Rows: Products[Category]
  • Values: Total Revenue, Total Profit, Profit Margin।
• PivotTable Analyze ট্যাবে যান >> Combo >> Clustered Column – Line on Secondary Axis সিলেক্ট করুন।

স্টেপ ৪: ইন্টারঅ্যাক্টিভ এলিমেন্ট ইনসার্ট করুন

স্লাইসার এবং টাইমলাইন ইনসার্ট করুন:

Product এবং Region Slicer তৈরি করুন:

• PivotAnalyze ট্যাবে যান >> Insert Slicer সিলেক্ট করুন।
• Products[Category] এবং Customers[Region] সিলেক্ট করুন।
• OK-এ ক্লিক করুন।

• আপনার ড্যাশবোর্ড ডিজাইনের সাথে মেলাতে ফরম্যাট করুন।

ডেটা ফিল্টারিং-এর জন্য Timeline যোগ করুন:

• PivotAnalyze ট্যাবে যান >> Insert Slicer সিলেক্ট করুন।
• Sales[OrderDate] সিলেক্ট করুন।
• OK-এ ক্লিক করুন।

• আপনার চার্টের উপরে পজিশন করুন।
• আপনার ড্যাশবোর্ড স্টাইলের সাথে মেলাতে ফরম্যাট করুন।

সকল স্লাইসারকে সব পিভট টেবিলের সাথে কানেক্ট করুন:

• প্রতিটি স্লাইসারে রাইট-ক্লিক করুন >> Report Connections সিলেক্ট করুন।

• সকল PivotTables চেক করুন যাতে ফিল্টার গ্লোবালি প্রয়োগ হয়।
• OK-এ ক্লিক করুন।

স্টেপ ৫: ভিবিএস দিয়ে অটোমেট করুন এবং ইন্টারঅ্যাক্টিভিটি বাড়ান

ড্যাশবোর্ডকে ডাইনামিক এবং নেভিগেট করা সহজ করার জন্য ভিবিএস ব্যবহার করব।

উদাহরণ ১: Refresh Data Button

• Developer ট্যাবে যান >> Insert >> Button সিলেক্ট করুন।
• বাটনকে Refresh Data-রূপে রিনেম করুন।
• বাটনে রাইট-ক্লিক করুন >> Assign Macro >> New সিলেক্ট করুন।

• নিচের কোড কপি-পেস্ট করুন।

VBA Code:

Sub RefreshDashboard() ThisWorkbook.RefreshAll MsgBox "Dashboard data refreshed!", vbInformation End Sub 

উদাহরণ ২: Dashboard Reset Button

• Developer ট্যাবে যান >> Insert >> Button সিলেক্ট করুন।
• বাটনকে Reset Dashboard-রূপে রিনেম করুন।
• বাটনে রাইট-ক্লিক করুন >> Assign Macro >> New সিলেক্ট করুন।
• নিচের কোড কপি-পেস্ট করুন।

VBA Code:

Sub ResetDashboardFilter() Dim ws As Worksheet Dim slicer As slicerCache Dim pivotTable As pivotTable ' Clear all slicer caches For Each slicer In ActiveWorkbook.SlicerCaches slicer.ClearAllFilters Next slicer ' Reset any timelines (already using SlicerCaches) For Each slicer In ActiveWorkbook.SlicerCaches If slicer.SourceType = xlTimeline Then slicer.ClearAllFilters End If Next slicer ' Refresh pivot tables For Each ws In ActiveWorkbook.Worksheets For Each pivotTable In ws.PivotTables pivotTable.RefreshTable Next pivotTable Next ws MsgBox "Dashboard filters have been reset!", vbInformation, "Reset Filters" End Sub 

স্টেপ ৬: ড্যাশবোর্ড লে আউট তৈরি করুন

Dashboard নামে একটি নতুন ওয়ার্কশিট তৈরি করুন।

• ড্যাশবোর্ড স্ট্রাকচার সেট আপ করুন:
  • ড্যাশবোর্ড টাইটেল এবং ডেট ফিল্টার।
  • KPI সেকশন (Revenue, Profit, Margin, Growth)।
  • চার্ট (Sales Trend, Top Products, Regional Performance)।

• Data টেবিলের জন্য সামারি টেবিল এবং ডিটেইল্ড অ্যানালিসিস তৈরি করতে পারেন।

• সামঞ্জস্যপূর্ণ ফরম্যাটিং প্রয়োগ করুন:
  • সর্বত্র একই কালার স্কিম ব্যবহার করুন।
  • সকল এলিমেন্ট সঠিকভাবে অ্যালাইন করুন।
  • ড্যাশবোর্ড সেকশন আলাদা করার জন্য বর্ডার যোগ করুন।

• Data Tables-এ কন্ডিশনাল ফরম্যাটিং যোগ করুন:
  • মূল্যবান মান হাইলাইট করতে ডেটা বার এবং কালার স্কেল ব্যবহার করুন।
  • টার্গেটের বিপরীতে পারফরম্যান্স দেখাতে KPI আইকন যোগ করুন।
• Instruction Sheet তৈরি করুন:
  • Instructions নামে একটি নতুন ওয়ার্কশিট তৈরি করুন।
  • ড্যাশবোর্ড কীভাবে ব্যবহার করবেন তা ব্যাখ্যা করে টেক্সট যোগ করুন।
  • ডেটা রিফ্রেশ, ইন্টারঅ্যাক্টিভিটি, এবং উপলব্ধ ফিচারের তথ্য অন্তর্ভুক্ত করুন।

স্টেপ ৭: টেস্ট এবং ট্রাবলশুট

সব ইন্টারঅ্যাক্টিভ এলিমেন্ট টেস্ট করুন:

• স্লাইসার সব রিলেভ্যান্ট ভিজ্যুয়ালাইজেশন ফিল্টার করছে কিনা নিশ্চিত করুন।
• বাটনগুলো সঠিকভাবে ম্যাক্রো এক্সিকিউট করছে কিনা ভেরিফাই করুন।
• টাইমলাইন কন্ট্রোল চেক করুন ডেটা রেঞ্জ সঠিকভাবে কাজ করছে কিনা।
  • Category স্লাইসার থেকে Beauty সিলেক্ট করুন।
  • Timeline থেকে Jan-April 2024 সিলেক্ট করুন।
• এটি ফিল্টার সিলেকশনের ভিত্তিতে পুরো ড্যাশবোর্ড আপডেট করবে।

Reset Data Filter টেস্ট করুন:

• Reset Dashboard-এ ক্লিক করুন।
• একটি মেসেজ পপ আপ হবে, "Dashboard filters have been reset"।
• OK-এ ক্লিক করুন।
• সকল ফিল্টার রিমুভ হয়ে যাবে, এবং আপনি একটি ফ্রেশ ড্যাশবোর্ড পাবেন।

Data Refresh টেস্ট করুন:

• সোর্স ডেটা মডিফাই করুন।
• Refresh Data-এ ক্লিক করুন।
• সকল ক্যালকুলেশন সঠিকভাবে আপডেট হচ্ছে কিনা নিশ্চিত করুন।
• ব্রোকেন কানেকশন বা ফর্মুলা আছে কিনা চেক করুন।

Practice Workbook ডাউনলোড করুন

উপসংহার

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