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

Read More

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

Read More

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

Read More

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

Read More

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 

Read More

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.

Read More

Excel Advance: বাংলাদেশী কমা (Comma) স্টাইল।

আমরা যারা বাংলাদেশে এক্সেল নিয়ে কাজ করি তাদের মধ্যে অনেকে এক্সপার্ট আছে। কিন্তু সমস্যা হলো নিজের জানা কাজ অন্যকে জানানো। কেউ জানায় না, কেউ জানাইতে চাইলেও পারে না। যাই হোক আমি আজকে একটা খুব সাধারন বিষয় নিয়ে টিউন করছি। অংকে লেখার সময় আমরা কমা ব্যবহার করি। যেমন শতকের পার, হাজারের পর, লক্ষের পর, কোটির পর। কিন্তু Excel এ দেখবেন কমা আসে বিলিয়ন মিলিয়ন এর পর। যেমন 124050222 এই সংখাটি যদি আপনি Excel এ লিখেন এবং কমা ব্যবহার করেন তাহলে 124,050,222 এ রকম হবে। তার মানে কমার হিসাবে আসল এক শত চব্বিশ মিলিয়ন পঞ্চাশ হাজার দুই শত বাইশ টাকা। কিন্তু আমাদের বাংলাদেশের কমা স্টাইলে হবে এরকম 12,40,50,222 মানে বার কোটি চল্লিশ লাখ পঞ্চাশ হাজার দুই শত বাইশ টাকা। আমি আজকে দেখাব কিভাবে Excel বাংলাদেশের স্টাইলে কমা ব্যবহার করতে হয়।

Sample File Download: Bangladeshi_Comma_Style           Zip File

(১) প্রথমে আপনার কাখ্ঙিত সংখ্যাটি যেকোন সেল এ লিখুন (একাধিক সেল ব্যবহার করতে পারেন)।

(২) এরপর সেলগুলো সেলেক্ট করে Right Button Click করে Format Cell select করুন।

(৩) Number tab থেকে Category এর নিচে Custom select করুন। এরপর Type এর নিচের বক্সে নিচের লাইনটি হুবহু কপি করে পেষ্ট করুন। 

[>=10000000] ##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00

OK করুন। দেখুন বাংলাদেশী স্টাইলে কমা এসে গেছে।

আর যারা vba code নিয়ে কাজ করেন বা করতে পারবেন তারা নিচের Function টি ব্যবহার করতে পারেন। ফাংশনের মাধ্যমেও একই ফলাফল পাবেন।

Public Function BdCommaStyle(YourValue)

Dim vResult As Variant

Dim strProcess As String

Dim strFormat As String

Dim iLength As Long

vResult = Null

    If IsNumeric(YourValue) Then

       strProcess = Abs(YourValue) & ""

       iLength = InStr(1, strProcess & ".", ".") - 1

       iLength = iLength + iLength \ 2

       strFormat = Right("@@@,@@,@@,@@@", iLength)

       vResult = Format(strProcess, strFormat)

       If Left(vResult, 1) = "," Then

          vResult = Mid(vResult, 2)

       End If

       If Val(YourValue) < 0 Then

          vResult = "-" & vResult

       End If

    End If

    BdCommaStyle = vResult

End Function

Word, Excel, Access নিয়ে যেকোন Query? Please mail me at harun24hr@gmail.com

Read More