টিউনের
শিরোনাম দেখে অনেকের কাছে
এলোমেলো মনে হতে পারে। আমি
আজকে দেখাব কিভাবে অনেকগুলো
আইটেমের একটা লিস্ট থেকে
শুধুমাত্র একক আইটেম গননা এবং
তা লিস্ট আকারে বের
করা যায়। ধরুন এক্সেলের একটি কলামে অনেকগুলো ডাটা এন্ট্রি করা আছে এবং একই আইটেম একাধিক
বার আছে। এখন আমারা চাই ঐ কলামে মোট কতটা একক আইটেম আছে, এবং ঐ একক আইটেমগুলোর লিস্ট।
যেকোন সময় নতুন আইটেম যদি এন্ট্রি করা হয়, তাহলে যেন অটোমেটিক ঐ আটইটেম একক আইটেমের
লিস্টে চলে আসে। নিচের উদাহরনটি দেখলে বিষয়টি আরো পরিস্কার হয়ে যাবে।
এখানে একটা
sample sales data এবং report দেখানো হয়েছে। Report থেকে আমরা দেখতে পাই মোট কতটি পন্য
এবং তাদের লিস্ট এবং কোন পন্য কত পরিমান সেল করা হয়েছে তার পরিমান। এই কাজগুলোই এক্সেলে
formula এর মাধ্যমে কিভাবে বের করতে হয় তা দেখাব। বুঝার সুবিধার জন্য sample file
download করুন।
প্রথমে একটি
এক্সেল ফাইল নিয়ে তাতে উপরের চিত্রের মত ডাটা এন্ট্রি করি। এরপর যে সেলে আমরা একক আইটেমগুলোর
গননার ফলাফল দেখতে চাই সেই সেলে নিচের formula বসান।
=SUMPRODUCT((D3:D1000<>"")/COUNTIF(D3:D1000,D3:D1000&""))
এখন যে সেলে
আমরা একক আইটেমগুলো লিস্ট আকারে দেখতে চাই সেই সেলে নিচের formula বসান।
=IFERROR(INDEX($D$3:$D$25,MATCH(0,COUNTIF($I$3:I4,$D$3:$D$25),0)),"")
এটি একটি অ্যারে
formula তাই formula বসানোর পর অবশ্যই Ctrl+Shift+Enter চাপবেন, তাছাড়া formula টি
ঠিক মত কাজ করবে না। Ctrl+Shift+Enter চাপার পর formula টি এরকম দেখাবে:
{=IFERROR(INDEX($D$3:$D$25,MATCH(0,COUNTIF($I$3:I4,$D$3:$D$25),0)),"")}
প্রতিটা আইটেম
কতটা সেল হয়েছে তা বের করার জন্য এই ফরমুলা ব্যবহার করুন।
=IF(I4="","",SUMIF($D$3:$D$25,I4,$E$3:$E$25))
যারা vba
use করতে পারেন তারা নিচের Custom Formula ব্যবহার করতে পারেন। Zip ফাইলে সবকিছু দেওয়া
আছে।
যেকোন সমস্যা
হলে কমেন্টে অথবা মেইলে জানাবেন। ইমেইল: harun24hr@gmail.com
Function
CountUnique(ByVal MyRange As Range) As Integer
Dim Cell As Range
Dim J As Integer
Dim iNumCells As Integer
Dim iUVals As Integer
Dim sUCells() As String
iNumCells = MyRange.Count
ReDim sUCells(iNumCells) As String
iUVals = 0
For Each Cell In MyRange
If Cell.Text > "" Then
For J = 1 To iUVals
If sUCells(J) = Cell.Text Then
Exit For
End If
Next J
If J > iUVals Then
iUVals = iUVals + 1
sUCells(iUVals) = Cell.Text
End If
End If
Next Cell
CountUnique = iUVals
End
Function
Function
listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
Dim elementSize As Integer
Dim newElement As Boolean
Dim i As Integer
Dim distance As Integer
Dim result As String
elementSize = 0
newElement = True
For Each row In rng.Rows
If row.Value <> "" Then
newElement = True
For i = 1 To elementSize Step 1
If elements(i - 1) = row.Value
Then
newElement = False
End If
Next i
If newElement Then
elementSize = elementSize + 1
ReDim Preserve
elements(elementSize - 1)
elements(elementSize - 1) =
row.Value
End If
End If
Next
distance =
Range(Application.Caller.Address).row - rng.row
If distance < elementSize Then
result = elements(distance)
listUnique = result
Else
listUnique = ""
End If
End
Function
January 09, 2016
