Wednesday, January 6, 2016

MS Access: কিভাবে দুইটি তারিখের মধ্যে ডাটা ফিল্টারিং করবেন। How to filter data between two dates.

আমরা আজকে দেখব কিভাবে দুই তারিখের মধ্যে ডাটা ফিল্টারিং করা যায়। আমি এখানে sample হিসাবে কিছু বিক্রয়ের তথ্য ব্যবহার করেছি। আপনারা আপনাদের ডাটা ব্যবহার করে কাজটি করতে পারেন। আমি ভিডিওতে সম্পূর্ন কাজটা দেখিয়েছি। ভিডিওটা ডাউনলোড করে দেখবেন, তাহলে আপনারা পরিস্কার ভাবে বুঝতে পারবেন।


Sample File Download: Data Filtering Between Two Date (968 KB)                Zip File (52 KB)
Video Tutorial Download:  Data Filtering Between Two Date

*** নিচের কোন step না বুঝতে পারলে অবশ্যই video টা দেখবেন।
(১) প্রথমে নিচের চিত্রের মত করে একটি টেবিল ডিজাইন করুন। টেবিলের নাম দিন tblSalesData

(২) এরপর টেবিলে কিছু ডাটা এন্ট্রি করুন।

(৩) এখন একটা ফরম ডিজাইন করুন, যেখানে দুইটা Textbox এবং একটা Command Button নিন। Textbox দুটোর নাম দিন যথাক্রমে txtStartDate এবং txtEndDate. Form টা frmFilter নামে সেভ করুন।

(4) এখন একটা Query design করুন (Quer এর নাম দিন qrySalesData) নিচের চিত্রের মত করে এবং ‍SalesDate এর criteria তে এই লাইন add করুন। Between [Forms]![frmFilter]![txtStartDate] And [Forms]![frmFilter]![txtEndDate]

এই লাইনটাই ডাটা ফরম এর মধ্যে দেওয়া তারিখ অনুযায়ী ফিল্টার করবে। এখানে frmFilter এর যায়গায় আপনাদের ফরমের নাম ব্যবহার করবেন (যদি ফরম অন্য নামে সেভ করে থাকেন)।

(৫) এখন নিচের ছবির মত করে একটি report design করুন। Report এর Record Source হিসাবে Query (qrySalesData) ব্যবহার করুন।

(৬) frmFilter এ যে command button নিয়েছেন তার “On Click” event এ নিচের ছবির মত করে Macro design করুন। এরপর Form টা চালু করে টেক্সট বক্সে তারিখ বসিয়ে কমান্ড বাটনে ক্লিক করুন। আপনার দেয়া তারিখ অনুযায়ী ডাটা report এ দেখাবে।



*** কোন জিজ্ঞাসা থাকলে কমেন্টে অথবা ইমেইলে জানাবেন। আমার ই-মেইল: harun24hr@gmail.com


Excel Advance: কিভাবে একটি sheet এর data অন্যের দেখা থেকে Protect করবেন password সেট করার মাধ্যমে।

আজকে আমার পোষ্টের বিষয় হলো কিভাবে একটি শীটের data password এর মাধ্যমে protect করা যায়, যাতে অন্য কেউ দেখতে না পারে। অনেকে মনে করতে পারে আমিতো শীটে password দিলেই কেউ দেখতে পারবেনা। কিন্তু না আমার আজকের টিউনের বিষয়টি একটু অন্যরকম। Suppose একটি workbook এ একাধিক শীট আছে, যেখানে একেকটি শীটে একক জন data entry করে। কিন্তু একটি শীটে কিছু important information আছে যা সবার দেখার জন্য না। আমি শুধু ঐ শীটটিতে password দিয়ে রাখব যাতে অন্যরা অন্যান্য সব শীটের ডাটা দেখতে ও এডিট করতে পারলেও ঐ শীটের ডাটা দেখতে ও এডিট করতে না পারে। আসুন দেখি কাজটি কিভাবে করা যায়।



Downloadsample file from here    (Zip File)

(১) একটি Excel file নিই এবং এতে তিনটি শীট নিই এবং যথাক্রমে নাম দেই Main, HiddenSheet, AnotherSheer.
(২) HiddenSheet এ আপনি কিছু ডাটা entry করুন। Keyboard এর Alt+F11 চেপে Excel code window open করুন। নিচের চিত্রের মত বাম পাশের project explorer window এর ThisWorkbook এ double click করুন এবং চিত্রের ন্যায় ২ ও ৩ নং ধাপে Workbook এবং SheetActivate select করুন। এখন চিত্রের মত করে নিচের কোড টুকো লিখুন (Copy Paste করতে পারেন)।


Dim MySheetName As String
MySheetName = "HiddenSheet" 'The sheed which I want to hide.
If Application.ActiveSheet.Name = MySheetName Then
    Application.EnableEvents = False
     Application.ActiveSheet.Visible = False
      response = Application.InputBox("Password", "Enter Password", "", Type:=2)
    If response = "123456" Then 'Unhide Password.
        Application.Sheets(MySheetName).Visible = True
        Application.Sheets(MySheetName).Select
    End If
End If
Application.Sheets(MySheetName).Visible = True
Application.EnableEvents = True

এবং workbook_open() এ {৩ নং স্টেপে SheetActivate এর যায়গায় open select করুন} নিচের লাইনটা শুধু ‍লিখুন

Sheets("Main").Select

(৩) সবশেষে ফাইলটি Excel Macro-Enabled Workbook হিসেবে save করুন। (Office Button ----> Save As ----> Excel Macro-Enabled Workbook
File টি Close করে পুনরায় চালু করুন। HiddenSheet এই শীটে ক্লিক করলেই আপনার কাছে password চাবে। password সঠিক হলে আপনি ডাটা দেখতে এবং এডিট করতে পারবেন, অন্যথায় পারবেন না।
 

For any types of query about this post mail me at harun24hr@gmail.com

Excel Advance: Count Unique Items and Lists of Unique Items. কিভাবে অনেকগুলো আইটেমের একটা লিস্ট থেকে শুধুমাত্র একক আইটেম গননা করবেন এবং তা লিস্ট আকারে বের করবেন।

টিউনের শিরোনাম দেখে অনেকের কাছে এলোমেলো মনে হতে পারে। আমি আজকে দেখাব কিভাবে অনেকগুলো আইটেমের একটা লিস্ট থেকে শুধুমাত্র একক আইটেম গননা  এবং তা লিস্ট আকারে বের করা যায়। ধরুন এক্সেলের একটি কলামে অনেকগুলো ডাটা এন্ট্রি করা আছে এবং একই আইটেম একাধিক বার আছে। এখন আমারা চাই ঐ কলামে মোট কতটা একক আইটেম আছে, এবং ঐ একক আইটেমগুলোর লিস্ট। যেকোন সময় নতুন আইটেম যদি এন্ট্রি করা হয়, তাহলে যেন অটোমেটিক ঐ আটইটেম একক আইটেমের লিস্টে চলে আসে। নিচের উদাহরনটি দেখলে বিষয়টি আরো পরিস্কার হয়ে যাবে।


এখানে একটা sample sales data এবং report দেখানো হয়েছে। Report থেকে আমরা দেখতে পাই মোট কতটি পন্য এবং তাদের লিস্ট এবং কোন পন্য কত পরিমান সেল করা হয়েছে তার পরিমান। এই কাজগুলোই এক্সেলে formula এর মাধ্যমে কিভাবে বের করতে হয় তা দেখাব। বুঝার সুবিধার জন্য sample file download করুন।

Sample File Download: Count And List Unique Items    Zip File.

প্রথমে একটি এক্সেল ফাইল নিয়ে তাতে উপরের চিত্রের মত ডাটা এন্ট্রি করি। এরপর যে সেলে আমরা একক আইটেমগুলোর গননার ফলাফল দেখতে চাই সেই সেলে নিচের 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

Excel Advance: কোন সেল এ Dropdown Box থেকে আইডি সেলেক্ট করে, পরের সেল সমূহে Automatic ডাটা নিয়ে আসা।

আজকের পোষ্টের উদ্দেশ্য হলো কিভাবে কোন শীটে সংরক্ষিত ডাটা থেকে অন্য কোন শীটে শুধু মাত্র আইডি বা Unique কোন value select করে অন্য ডাটাগুলো নিয়ে আসা যায়।



পোষ্টের শুরুতে Sample File টি Download করে নিতে পারেন।


প্রথমে উপরের চিত্রের মত করে Sheet2 তে ডাটা এন্ট্রি করুন। এরপর Sheet1 এ উপরের চিত্রের মত করে কিছু সেল সাজিয়ে নিন। তারপর B4 থেকে B23 পর্যৃন্ত select করে Data tab এর Data Validation এ ক্লিক করুন। Validation Criteria এর Allow combo box এ List select করুন। Source box এ =Sheet2!A2:A21 লিখে ok click করুন। তাহলে আমাদের Drop Down set করা হয়ে গেলো।



এখন “Name” Column (C4 Cell) এ এই Formula লিখুন =IF(B4="","",VLOOKUP(B4,Sheet2!A$2:E$200,2,FALSE))

“Joining Date” Column (D4 Cell) এ এই Formula লিখুন =IF(B4="","",VLOOKUP(B4,Sheet2!A$2:E$200,3,FALSE))

“Designation” Column (E4 Cell) এ এই Formula লিখুন =IF(B4="","",VLOOKUP(B4,Sheet2!A$2:E$200,4,FALSE))

“Department” Column (F4 Cell) এ এই Formula লিখুন =IF(B4="","",VLOOKUP(B4,Sheet2!A$2:E$200,5,FALSE))


প্রতিটি কলামে সূত্র লেখার পর 23 Row পর্যন্ত Fill করুন।

এখন ID Column এর সেল সমূহে শুধুমাত্র আইডি সেলেক্ট করলেই পরের কলামগুলোর সেলে ডাটা চলে আসবে।



Any query? E-Mail me at harun24hr@gmail.com

Excel Advance: Visit Details, Excel এর মাধ্যমে করা একটি Advance কাজ। কি করা যায় Excel এর মাধ্যমে? জানার আছে অনেক কিছু।

আমার আজকের টিউনের মূল উদ্দেশ্য হলো আসলে এক্সেল দ্বারা কি করা সম্ভব তার কিছুটা তুলে ধরা। Privacy এর জন্য ফাইলে কিছু ডাটা Unknown দেখানো হয়েছে যেমন, নাম, মোবাইল নম্বর, ই-মেইল। 


Sample file download: Visit_Details      Zip File

Excel, Microsoft Office Package এর বহুল ব্যবহৃত একটি প্রোগ্রাম। বিশ্বজুড়ে সমাদৃত এই প্রোগ্রাম দ্বারা কত যে Advance level এর কাজ করা যায়, তার কতটুকুই বা আমরা জানি। আমাদের দেশে যারা Excel নিয়ে কাজ করে, তারা অনেকেই অনেক দক্ষ কিস্তু শেয়ার করতে চায় না বা সুযোগ হয় না। আমি কিছু টুকিটাকি কাজ করি Excel এর। তারই একটি ফাইল আপনাদের সাথে শেয়ার করলাম। ফাইলটি source code সহ open রাখা হয়েছে, যাতে যারা শিখতে আগ্রহী তারা জানতে পারে, কি কোড ব্যবহার করা হয়েছে এবং এই কোডগুলো তারা অন্য ফাইলেও নিজেদের মত ব্যবহার করতে পারে।

ফাইলটির কিছু Screenshots এবং বৈশিষ্ট্য:

·         New Visit button এ click করলেই উপরের চিত্রের মত একটি ফরম ওপেন হবে। এখান থেকে আপনি নতুন ভিজিট এন্ট্রি করতে পারবেন। First, Last, Next, Previous record এ navigation করতে পারবেন।
·         Copy Data button এর মাধ্যমে যেকোন সেল সেলেক্ট করে পুরোনো ডাটা দেখতে এবং এডিট করে নিউ ‍ভিজিট হিসেবে সেভ করতে পারবেন।


·         Filter button এ click করলে উপরের মত একটি ফরম আসবে। এখান থেকে আপনি একাধিক criteria select করে ডাটা ফিল্টারিং করতে পারবেন। যেমন Visit type, Product, Customer, date range, moth, year etc.

·         ফাইলটির আরেকটি গুরুত্বপূর্ণ বৈশিষ্ট্য হলো এটি Automatic প্রতিদিনের তারিখ সহ ব্যাকআপ রাখবে। আপনি যেখানে ফাইলটি রাখবেন সেখানে Backups নামে একটি ফোল্ডারে এই Backup file গুলো জমা হবে।
ফাইলটি Macro Enable তাই কারো কম্পিউটার Excel Macro Enable করা না থাকলে, Macro Enable করে নিতে হবে। Macro Enable করার জন্য নিচের চিত্রটি অনুসরন করুন।


হয়তো ফাইলটি সরাসরি কারো কাজে লাগবে না, কিন্তু আশাকরি যারা excel advance লেভেল এ কাজ করেন তারা তাদের অন্য ফাইলে এই কোড গুলো ব্যবহার করতে পারবেন।
আর হ্যা, এই ফাইলের সকল কাজ/কোড আমার নিজের করা। এই পোষ্ট নিয়ে যেকোন জিজ্ঞাসা থাকলে দয়া করে মেইলে জানাবেন। আমার ই-মেইল harun24hr@gmail.com


Excel Advance: Automatic roll assign. স্কুল বা কলেজের ছাত্র-ছাত্রীদের পাস বা ফেল এবং মোট নাম্বারের ভিত্তিতে অটোম্যাটিক রোল নং বসানোর সূত্র।

Excel বিভিন্ন হিসাব নিকাশ করার জন্য ব্যবহার করা হয়। স্কুল কলেজে যারা student দের পরীক্ষার রেজাল্ট প্রসেসিং এর জন্য এক্সেল ব্যবহার করেন তাদের এই সূত্রটি কাজে লাগবে বলে আশা করি।



Sample file download Roll_Assign_By_Function Zip File

Formula:    =MATCH(O2*10^(MAX($N$2:$N$1000)-N2),LARGE($O$2:$O$1000*10^(MAX($N$2:$N$1000)-$N$2:$N$1000),ROW($N$2:$N$1000)-ROW($N$1)),0)

নিচের চিত্রে সূত্রটির ব্যাখা দেওয়া আছে। যারা নিজেরা অনুশীলন করবেন তারা সূত্রের মধ্যে Fail Count এবং Total কলামের কলাম নাম পরিবর্তন করে নিবেন। যেমন, এখানে Fail Count column হলো N এবং Total কলাম হলো O.


সুত্রটি কিভাবে কাজ করে: এটি প্রথমে Fail Count থেকে সবচেয়ে কম বিষয়ে ফেল করেছে এমন ছাত্র-ছাত্রীকে রোল ১ হবে। যদি Fail Count সমান হয় তখন মোট নম্বর যার সবচেয়ে বেশি তার ঘরে রোল ১ হবে। এভাবে ধারাবাহিক ভাবে রোল চলে আসবে।
Logic: যদি কারো মোট নাম্বার সবচেয় বেশি হয় কিন্তু সে যদি কোন বিষয়ে ফেল করে তাহলে, যে কোন বিষয়ে ফেল করে নাই (মোট নাম্বার কম হলেও)  তার রোল প্রথমে আসবে। যদি একাধিক ছাত্র-ছাত্রীদের ফেল সমান হয় তখন ঐ ছাত্র-ছাত্রীদের মাঝে যার মোট নাম্বার বেশি হবে তার রোল সবচেয়ে কম হবে।
*** এটি একটি Array Formula তাই Formula বসানোর পর অবশ্যই আপনাকে Crtl+Shift+Enter চাপতে হবে। তাহলে Formula টি এরকম দেখাবে {=MATCH(O2*10^(MAX($N$2:$N$1000)-N2),LARGE($O$2:$O$1000*10^(MAX($N$2:$N$1000)-$N$2:$N$1000),ROW($N$2:$N$1000)-ROW($N$1)),0)}
এরপর সবগুলো সেলে ‍Autofill ব্যবহার করে Formula টি বসিয়ে নিন।

For any query about this post, please mail at harun24hr@gmail.com 

Tuesday, January 5, 2016

How to find equal or next larger value of amount of a specific type.


Suppose, we have data in two column, "Type", "Amount". We want to select a type and input amount. If the input amount is equal of that type then it will show that equal result or will return next larger value. According to above screenshot put below formula in G3 cell.

=MIN(IF($A$3:$A$11=D3,IF($B$3:$B$11=E3,$B$3:$B$11,IF($B$3:$B$11>E3,$B$3:$B$11))))

Sample File Download

*** Note: The formula is an array formula, So you must press Ctrl+Shift+Enter after finishing typing formula.
Subscribe to: Posts (Atom)

Categories

Featured Post

Excel Advance: How to filter data of a column that have specific word?

Suppose I have list of animal (See screenshot). I want to filter data which have word cat. So, for filtering data with word cat use th...

Recent Comments

Popular Posts

Copyright © Excel & Access Tutorial | Powered by Harun24HR