সাধারণত TRANSPOSE ফাংশনটি প্রায়ই রোকে কলামে কনভার্ট করতে ব্যবহৃত হয়। যাইহোক, নির্দিষ্ট ক্রাইটেরিয়ার উপর ভিত্তি করে ফলাফল, যেন অনন্য মান, রিটার্ন করবে না। এই টিউটোরিয়ালে আমরা আপনাকে দেখাব কিভাবে ক্রাইটেরিয়া অনুযায়ী এক্সেলে রোকে কলামে ট্রান্সপোজ (transpose rows to columns based on criteria in Excel) করা যায়।
ক্রাইটেরিয়া অনুযায়ী এক্সেলে রোকে কলামে ট্রান্সপোজ করার ২টি হ্যান্ডি পদ্ধতি
নিচের চিত্রে বিভিন্ন পণ্য এবং তাদের কোয়ান্টিটি এর ডেটা সেট দেওয়া আছে। রোগুলো কলামে ট্রান্সপোজ (transpose rows to columns) করা হবে। যেহেতু আলাদা সেলে কিছু ডুপ্লিকেট এন্ট্রি রয়েছে, তাই আমরা অনন্য মান (unique values)-এর ক্রাইটেরিয়া (criteria) অনুযায়ী রোগুলো কলামে ট্রান্সপোজ (transpose the rows into columns based on the criteria) করব। প্রথমে আমরা INDEX, MATCH, COUNTIF, IF, এবং IFERROR ফাংশন দিয়ে ফর্মুলা তৈরি করব। আমরা VBA কোডও এক্সিকিউট করব একই কাজ করার জন্য।
১. INDEX, MATCH এবং COUNTIF ফাংশনসহ ফর্মুলা প্রয়োগ করে ক্রাইটেরিয়া অনুযায়ী রোকে কলামে ট্রান্সপোজ
শুরুতে আমরা INDEX, MATCH, COUNTIF, IF, এবং IFERROR ফাংশনসহ অ্যারে ফর্মুলা প্রয়োগ করব।
স্টেপ ১: INDEX, MATCH এবং COUNTIF ফাংশন ইনসার্ট
- সেল E5-এ নিচের ফর্মুলা টাইপ করুন অনন্য পণ্য পেতে।
=INDEX($B$5:$B$12, MATCH(0, COUNTIF($E$4:$E4, $B$5:$B$12), 0))
স্টেপ ২: অ্যারে প্রয়োগ
- ফর্মুলাটি অ্যারে হিসেবে প্রয়োগ করতে Ctrl + Shift + Enter চাপুন।
- অতএব আপনি প্রথম অনন্য ফলাফল পাবেন।
স্টেপ ৩: সেলগুলো অটো-ফিল (Auto-Fill) করুন
- সকল অনন্য মান পেতে কলামটি অটো-ফিল করতে AutoFill Handle Tool ব্যবহার করুন।
স্টেপ ৪: IFERROR ফাংশন ইনসার্ট
- কোয়ান্টিটির রো ভ্যালু কলামে ট্রান্সপোজ করতে নিচের ফর্মুলা লিখুন।
=IFERROR(INDEX($C$5:$C$12, MATCH(0, COUNTIF($E5:E5, $C$5:$C$12) + IF($B$5:$B$12<>$E5,1,0),0)),0)
স্টেপ ৫: অ্যারে প্রয়োগ
- অ্যারে ইনসার্ট করতে Ctrl + Shift + Enter চাপুন।
- ফলস্বরূপ সেল F5 প্রথম ট্রান্সপোজড ভ্যালু দেখাবে।
- AutoFill Handle Tool দিয়ে ড্র্যাগ ডাউন করুন কলাম অটো-ফিল করতে।
- শেষে AutoFill Handle Tool দিয়ে রোগুলো অটো-ফিল করুন।
- অতএব সকল ট্রান্সপোজড রো কলামে রূপান্তরিত হবে।
আরও পড়ুন: এক্সেলে গ্রুপে একাধিক রোকে কলামে ট্রান্সপোজ করা
সদৃশ পড়ুন
- এক্সেলে ডুপ্লিকেট রোকে কলামে ট্রান্সপোজ করা (৪ টি উপায়)
- এক্সেল VBA: গ্রুপে একাধিক রোকে কলামে ট্রান্সপোজ
- একাধিক কলামকে এক কলামে এক্সেলে ট্রান্সপোজ (৩ টি হ্যান্ডি মেথড)
- এক্সেলে রিভার্স ট্রান্সপোজ করা (৩ টি সিম্পল মেথড)
২. VBA কোড রান করে ক্রাইটেরিয়া অনুযায়ী রোকে কলামে ট্রান্সপোজ
স্টেপ ১: মডিউল (Module) তৈরি
- প্রথমে Alt + F11 চাপুন VBA Macro শুরুতে।
- Insert-এ ক্লিক করুন।
- Module অপশন সিলেক্ট করুন মডিউল তৈরি করতে।
স্টেপ ২: VBA কোড টাইপ
- নিচের VBA কোডটি পেস্ট করুন।
Sub TransposeRows() 'Define Variables Dim RowsNumber As Long Dim p As Long Dim xColCr As String Dim xColumn As New Collection Dim InputRng As Range Dim OutputRng As Range Dim RngText As String Dim CountRow As Long Dim xRowRg As Range On Error Resume Next 'Set values and Input Box fot Input Range RngText = ActiveWindow.RangeSelection.Address Set InputRng = Application.InputBox("Select Your Input Range for 2 columns:", "ExcelDemy", RngText, , , , , 8) Set InputRng = Application.Intersect(InputRng, InputRng.Worksheet.UsedRange) 'Apply Condition to count only 2 columns and to show msg box for alerting to select only two columns If InputRng Is Nothing Then Exit Sub If (InputRng.Columns.Count <> 2) Or _ (InputRng.Areas.Count > 1) Then MsgBox "The specified range is only area for 2 columns ", , "ExcelDemy" Exit Sub End If 'Set values and Input Box fot Input Range Set OutputRng = Application.InputBox("Select Your Output Range (one cell):", "ExcelDemy", RngText, , , , , 8) If OutputRng Is Nothing Then Exit Sub Set OutputRng = OutputRng.Range(1) 'Count Rows RowsNumber = InputRng.Rows.Count 'Apply For loop For p = 2 To RowsNumber xColumn.Add InputRng.Cells(p, 1).Value, InputRng.Cells(p, 1).Value Next Application.ScreenUpdating = False For p = 1 To xColumn.Count xColCr = xColumn.Item(p) OutputRng.Offset(p, 0) = xColCr InputRng.AutoFilter Field:=1, Criteria1:=xColCr Set xRowRg = InputRng.Range("B2:B" & RowsNumber).SpecialCells(xlCellTypeVisible) If xRowRg.Count > CountRow Then CountRow = xRowRg.Count 'Copy and paste with transpose InputRng.Range("B2:B" & RowsNumber).SpecialCells(xlCellTypeVisible).Copy OutputRng.Offset(p, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False Next OutputRng = InputRng.Cells(1, 1) OutputRng.Offset(0, 1).Resize(1, CountRow) = InputRng.Cells(1, 2) InputRng.Rows(1).Copy OutputRng.Resize(1, CountRow + 1).PasteSpecial Paste:=xlPasteFormats InputRng.AutoFilter Application.ScreenUpdating = True End Sub
স্টেপ ৩: প্রোগ্রাম রান করুন
- প্রথমে save করুন এবং F5 চাপুন প্রোগ্রাম রান করতে।
- হেডারসহ আপনার ডেটা সেট সিলেক্ট করুন।
- OK চাপুন।
- আউটপুট (output) পেতে একটি সেল সিলেক্ট করুন।
- তারপর OK চাপুন।
- ফলস্বরূপ আপনি রোকে কলামে ট্রান্সপোজ করা ফলাফল পাবেন।
আরও পড়ুন: এক্সেল VBA ব্যবহার করে রোকে কলামে ট্রান্সপোজ করা (৪ টি আইডিয়াল উদাহরণ)
উপসংহার
আশা করি এই আর্টিকেলটি আপনাকে ক্রাইটেরিয়া অনুযায়ী এক্সেলে রোকে কলামে ট্রান্সপোজ (transpose rows to columns based on criteria in Excel) করার টিউটোরিয়াল দিয়েছে। এই সব প্রক্রিয়া শিখে আপনার ডেটাসেটে প্রয়োগ করুন। প্র্যাক্টিস ওয়ার্কবুকে নজর দিন এবং এই স্কিলগুলো পরীক্ষা করুন। আপনার মূল্যবান সমর্থনের কারণে আমরা এই ধরণের টিউটোরিয়াল তৈরি করতে প্ররোচিত হই।
আপনার কোনো প্রশ্ন থাকলে – আমাদের সাথে বিনামূল্যে জিজ্ঞাসা করুন। 또한 নিচের কমেন্ট সেকশনে কমেন্ট ছাড়াও করতে পারেন।
আমরা Exceldemy টিম, আপনার প্রশ্নের উত্তর দিতে সবসময় প্রস্তুত।
আমাদের সাথে থাকুন এবং শিখুন।
সম্পর্কিত আর্টিকেলগুলো
- পাওয়ার কোয়েরি ব্যবহার করে এক্সেলে কলামকে রোতে কনভার্ট
- সেল ভ্যালুর ভিত্তিতে এক্সেলে কলামকে রোতে কনভার্ট করার উপায়
- VBA ব্যবহার করে একাধিক কলামকে এক্সেলে রোতে ট্রান্সপোজ (২ টি মেথড)
- সিংগল কোটসহ কলামকে কমা সেপারেটেড লিস্টে কনভার্ট করা
- এক্সেল পেস্ট ট্রান্সপোজ শর্টকাট: ৪ টি সহজ উপায় ব্যবহার করার
- রেফারেন্স বদলানো ছাড়া এক্সেল ট্রান্সপোজ ফর্মুলা (৪ টি সহজ উপায়)
- এক্সেল VBA-এ ট্রান্সপোজ করার উপায় (৩ টি মেথড)
