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

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

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

• বিক্রয় - লেনদেনের ডেটা রয়েছে৷
• পণ্য - পণ্যের বিবরণ এবং বিভাগ।
• গ্রাহক - গ্রাহক তথ্য।
• অঞ্চল - ভৌগলিক তথ্য।

ধাপ 1:ডেটা আমদানির জন্য পাওয়ার কোয়েরি ব্যবহার করুন

ডাটা আমদানি করুন:

• ডেটা -এ যান ট্যাব>> ডেটা পান নির্বাচন করুন>> ফাইল থেকে নির্বাচন করুন>> পাঠ্য/CSV থেকে নির্বাচন করুন .
• আপনার sales_data.txt-এ নেভিগেট করুন ফাইল>> আমদানি করুন ক্লিক করুন .

• যখন পাওয়ার কোয়েরি এডিটর খোলে, ডেটা পর্যালোচনা করুন এবং এই রূপান্তরগুলি করুন:
• ডাটা প্রকার পরিবর্তন করুন।
• যেকোন কলামে ডান-ক্লিক করুন>> ডেটা টাইপ পরিবর্তন করুন নির্বাচন করুন>> ডেটা টাইপ নির্বাচন করুন .
  • অর্ডারের তারিখ তারিখ থেকে .
  • পরিমাণ পুরো নম্বরে .
  • ইউনিট মূল্য এবং ছাড় দশমিক সংখ্যা থেকে .

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

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

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

গণনা করা কলাম যোগ করুন:

• পাওয়ার কোয়েরি এডিটরে, বিক্রয় ডেটা নির্বাচন করুন।
• কলাম যোগ করুন এ যান ট্যাব>> কাস্টম কলাম নির্বাচন করুন .
• এর নাম দিন:রাজস্ব .
• নিম্নলিখিত সূত্র ঢোকান।

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

• ঠিক আছে ক্লিক করুন .

• আরেকটি কাস্টম কলাম যোগ করুন।
• এর নাম দিন:লাভ .
• নিম্নলিখিত সূত্র ঢোকান।

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

• ঠিক আছে ক্লিক করুন .
• মূল্য পেতে আমাদের পণ্য টেবিলের সাথে একত্রিত হতে হবে।

অতিরিক্ত অন্তর্দৃষ্টির জন্য সারণি মার্জ করুন:

• পাওয়ার কোয়েরি এডিটরে সেলস ডাটা খোলা আছে।
• হোম এ যান ট্যাব>> কোয়েরি মার্জ করুন ক্লিক করুন ফিতা থেকে।
  • ProductID নির্বাচন করুন বিক্রয় টেবিল থেকে।
  • পণ্যগুলি নির্বাচন করুন৷ টেবিল এবং ProductID-এ যোগ দিন .
  • ঠিক আছে ক্লিক করুন .

• টেবিল প্রসারিত করুন-এ ক্লিক করুন বিকল্প>> শুধুমাত্র ইউনিটকস্ট নির্বাচন করুন আনতে কলাম।
• ঠিক আছে ক্লিক করুন .

• এখন, লাভ টানুন প্রসারিত পণ্যের অধীনে কলাম ধাপ।
• আপনি লাভের পরিমাণ পাবেন।

• একবার আপনার ডেটা ট্রান্সফরমেশন হয়ে গেলে।
• ক্লিক করুন বন্ধ করুন এবং এতে লোড করুন…

• ডেটা আমদানি করুন-এ বক্স;
  • শুধু সংযোগ তৈরি করুন নির্বাচন করুন .
  • ডেটা মডেলে এই ডেটা যোগ করুন নির্বাচন করুন চারটি টেবিলের জন্য।
  • ঠিক আছে ক্লিক করুন .

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

ওপেন পাওয়ার পিভট:

যদি পাওয়ার পিভট রিবনে উপলব্ধ না থাকে, তাহলে এটি সক্রিয় করুন৷

• ফাইল-এ যান ট্যাব>> বিকল্প নির্বাচন করুন>> অ্যাড-ইনস নির্বাচন করুন .
• পরিচালনা-এ বক্স>> COM অ্যাড-ইন নির্বাচন করুন>> যাও নির্বাচন করুন .

• এক্সেলের জন্য মাইক্রোসফ্ট পাওয়ার পিভট নির্বাচন করুন .
• ঠিক আছে ক্লিক করুন .

• পাওয়ার পিভটে যান ট্যাব>> পরিচালনা নির্বাচন করুন .
• এটি পাওয়ার কোয়েরি থেকে আমদানি করা ডেটা খুলবে৷
৷

সম্পর্ক তৈরি করুন :

• হোম এ যান ট্যাব>> ডায়াগ্রাম ভিউ নির্বাচন করুন .
  • টেনে আনুন বিক্রয়[ProductID] পণ্য[ProductID]-এ .
  • টেনে আনুন বিক্রয়[CustomerID] গ্রাহকদের[CustomerID]কে .
  • টেনে আনুন গ্রাহকদের[RegionID] অঞ্চলে[RegionID] .

• অথবা ডিজাইন -এ যান ট্যাব>> সম্পর্ক তৈরি করুন নির্বাচন করুন .
• তারপর মিলে যাওয়া কলাম নির্বাচন করুন।

গণনা করা পরিমাপ তৈরি করুন:

• পাওয়ার পিভটে, সেলস টেবিলে ক্লিক করুন।
• হোম এ যান৷ ট্যাব>> পরিমাপ নির্বাচন করুন>> নতুন পরিমাপ ক্লিক করুন .
• অথবা, হোম এ যান৷ ট্যাব>> গণনা এলাকা নির্বাচন করুন .
• সেখানে গণনা করা পরিমাপ ঢোকান:

• মোট রাজস্ব:

Total Revenue := SUM(Sales[Revenue])

• মোট লাভ:

Total Profit := SUM(Sales[Profit])

• লাভের মার্জিন:

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

• মোট অর্ডার:

Total Orders:=COUNTA(Sales[OrderID])

• গড় অর্ডার মান:

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

• YTD আয়:

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

• পূর্ববর্তী বছরের আয়:

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

• YOY বৃদ্ধি:

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

ধাপ 3:ড্যাশবোর্ড উপাদান তৈরি করতে পিভট টেবিল ব্যবহার করুন

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

• ঢোকান -এ যান ট্যাব>> পিভটটেবল নির্বাচন করুন>> ডেটা মডেল থেকে নির্বাচন করুন .
• পাওয়ার পিভটে, হোম-এ যান৷ ট্যাব>> পিভট টেবিল নির্বাচন করুন .

• পিভট টেবিল তৈরি করুন-এ বক্স;
  • নতুন ওয়ার্কশীট নির্বাচন করুন৷
  ৷
  • ঠিক আছে ক্লিক করুন .

• মোট আয়, মোট লাভ, লাভ মার্জিন এবং বৃদ্ধির জন্য পৃথক পিভট টেবিল তৈরি করুন।
• এগুলি আপনার ড্যাশবোর্ডের কাঠামোর সাথে সারিবদ্ধ ঘরগুলিতে রাখুন৷
• মুদ্রা বা শতকরা হিসাবে উপযুক্ত হিসাবে বিন্যাস করুন।

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

রেভিনিউ ট্রেন্ড চার্ট:

• ডেটা মডেল থেকে একটি পিভট টেবিল তৈরি করুন।
  • সারি:বিক্রয়[অর্ডারের তারিখ[মাস]]
  • মান: মোট রাজস্ব
• পিভটটেবিল বিশ্লেষণ-এ যান ট্যাব>> ক্লাস্টারড কলাম চার্ট নির্বাচন করুন .

শীর্ষ পণ্য তালিকা:

• ডেটা মডেল থেকে একটি পিভট টেবিল তৈরি করুন।
  • সারি:পণ্য[ProductName]
  • মান:মোট রাজস্ব
• মোট রাজস্ব অনুসারে সাজান অবরোহ, শীর্ষ 10 এ ফিল্টার করুন।

• পিভটটেবিল বিশ্লেষণ-এ যান ট্যাব>> ক্লাস্টারড বার চার্ট নির্বাচন করুন .

বিভাগ কর্মক্ষমতা :

• ডেটা মডেল থেকে একটি পিভট টেবিল তৈরি করুন।
  • সারি:পণ্য[শ্রেণী]
  • মান:মোট রাজস্ব, মোট লাভ, লাভ মার্জিন .
• পিভটটেবিল বিশ্লেষণ-এ যান ট্যাব>> কম্বো নির্বাচন করুন>> ক্লাস্টারড কলাম – সেকেন্ডারি অক্ষের লাইন নির্বাচন করুন .

ধাপ 4:ইন্টারেক্টিভ উপাদান সন্নিবেশ করান

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

পণ্য এবং অঞ্চল স্লাইসার তৈরি করুন:

• PivotAnalyze-এ যান ট্যাব>> স্লাইসার ঢোকান নির্বাচন করুন .
• পণ্য[শ্রেণী] নির্বাচন করুন এবং গ্রাহক[অঞ্চল] .
• ঠিক আছে ক্লিক করুন .

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

তারিখ ফিল্টারিংয়ের জন্য একটি টাইমলাইন যোগ করুন:

• PivotAnalyze-এ যান ট্যাব>> স্লাইসার ঢোকান নির্বাচন করুন .
• বিক্রয়[অর্ডার তারিখ] নির্বাচন করুন .
• ঠিক আছে ক্লিক করুন .

• আপনার চার্টের উপরে অবস্থান।
• আপনার ড্যাশবোর্ড শৈলীর সাথে মেলে ফর্ম্যাট করুন।

সমস্ত স্লাইসারকে PivotTables-এ সংযুক্ত করুন :

• প্রতিটি স্লাইসারে ডান-ক্লিক করুন>> সংযোগ প্রতিবেদন করুন নির্বাচন করুন .

• সমস্ত PivotTables চেক করুন ফিল্টার বিশ্বব্যাপী প্রযোজ্য নিশ্চিত করতে।
• ঠিক আছে ক্লিক করুন .

ধাপ 5:স্বয়ংক্রিয় এবং VBA এর সাথে ইন্টারঅ্যাকটিভিটি উন্নত করুন

আমরা ড্যাশবোর্ডকে গতিশীল করতে এবং নেভিগেট করা সহজ করতে VBA ব্যবহার করব।

উদাহরণ 1:রিফ্রেশ ডেটা বোতাম

• ডেভেলপার -এ যান ট্যাব>> ঢোকান নির্বাচন করুন>> বোতাম নির্বাচন করুন .
• বোতামটিকে ডেটা রিফ্রেশ করতে পুনঃনামকরণ করুন .
• বাটনে ডান ক্লিক করুন>> ম্যাক্রো বরাদ্দ করুন>> নির্বাচন করুন নতুন নির্বাচন করুন .

• নিম্নলিখিত কোড কপি-পেস্ট করুন।

VBA কোড:

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

উদাহরণ 2:ড্যাশবোর্ড রিসেট বোতাম

• ডেভেলপার -এ যান ট্যাব>> ঢোকান নির্বাচন করুন>> বোতাম নির্বাচন করুন .
• বোতামটিকে ড্যাশবোর্ড রিসেট করুন এর নাম পরিবর্তন করুন .
• বাটনে ডান ক্লিক করুন>> ম্যাক্রো বরাদ্দ করুন>> নির্বাচন করুন নতুন নির্বাচন করুন .
• নিম্নলিখিত কোড কপি-পেস্ট করুন।

VBA কোড:

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

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

ড্যাশবোর্ড নামে একটি নতুন ওয়ার্কশীট তৈরি করুন৷

• ড্যাশবোর্ড স্ট্রাকচার সেট আপ করুন:
  • ড্যাশবোর্ড শিরোনাম এবং তারিখ ফিল্টার।
  • KPI বিভাগ (রাজস্ব, লাভ, মার্জিন, বৃদ্ধি)।
  • চার্ট (বিক্রয় প্রবণতা, শীর্ষ পণ্য, আঞ্চলিক কর্মক্ষমতা)।

• আপনি ডেটা টেবিল এবং একটি বিশদ বিশ্লেষণের জন্য একটি সারাংশ টেবিল তৈরি করতে পারেন।

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

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

পদক্ষেপ 7:পরীক্ষা এবং সমস্যা সমাধান

সমস্ত ইন্টারেক্টিভ উপাদান পরীক্ষা করুন:

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

টেস্ট রিসেট ডেটা ফিল্টার:

• ড্যাশবোর্ড রিসেট করুন-এ ক্লিক করুন .
• একটি বার্তা পপ আপ হবে, “ড্যাশবোর্ড ফিল্টার রিসেট করা হয়েছে” .
• ঠিক আছে ক্লিক করুন .
• সমস্ত ফিল্টার মুছে ফেলা হবে, এবং আপনি একটি নতুন ড্যাশবোর্ড পাবেন।

ডেটা রিফ্রেশ পরীক্ষা করুন :

• উৎস ডেটা পরিবর্তন করুন।
• ডেটা রিফ্রেশ করুন এ ক্লিক করুন .
• সকল হিসাব সঠিকভাবে আপডেট করা নিশ্চিত করুন।
• কোনও ভাঙা সংযোগ বা সূত্র পরীক্ষা করুন।

অভ্যাস ওয়ার্কবুক ডাউনলোড করুন

উপসংহার

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