حل تکنیک تاپسیس در اکسل (برنامه نویسی شده): راهنمای کامل و جامع

تکنیک تاپسیس (TOPSIS) که مخفف "Technique for Order of Preference by Similarity to Ideal Solution"، یکی از روش‌های تصمیم‌گیری چندمعیاره است که در حوزه‌های مختلف، از جمله مدیریت، مهندسی، و علوم داده، کاربرد فراوانی دارد. این روش، بر اساس مقایسه گزینه‌ها با راه‌حل ایده‌آل و منفی، بهترین گزینه را مشخص می‌کند. ولی، چطور می‌توان این روش را در اکسل، به صورت برنامه‌نویسی شده، پیاده‌سازی کرد؟ در این مقاله، قصد داریم به صورت کامل و جامع، مراحل، الگوریتم، و کدهای لازم برای پیاده‌سازی تکنیک تاپسیس را توضیح دهیم، به طوری که حتی کسانی که تازه وارد اکسل و برنامه‌نویسی هستند، بتوانند آن را درک و اجرا کنند.
مقدمه‌ای بر روش تاپسیس و اهمیت آن
در تصمیم‌گیری‌های چندمعیاره، انتخاب بهترین گزینه، کار ساده‌ای نیست. چندین معیار و شاخص وجود دارد که باید همزمان در نظر گرفته شوند، و هر کدام اهمیت متفاوتی دارند. تکنیک تاپسیس، با هدف یافتن گزینه‌ای که به راه‌حل ایده‌آل نزدیک‌تر است، این مشکل را حل می‌کند. این روش، به صورت اصولی، نقاط قوت و ضعف هر گزینه را تحلیل می‌کند و بر اساس فاصله نسبی، بهترین گزینه را مشخص می‌نماید.
در واقع، در این روش، دو راه‌حل وجود دارد:
- راه‌حل ایده‌آل (که بهترین مقادیر هر معیار است)
- راه‌حل منفی (که بدترین مقادیر هر معیار است)
سپس، فاصله هر گزینه از این دو راه‌حل محاسبه می‌شود، و بر اساس این فاصله‌ها، رتبه‌بندی انجام می‌گیرد.
پروسه کلی پیاده‌سازی در اکسل
برای پیاده‌سازی تکنیک تاپسیس در اکسل، باید چند گام مشخص را طی کنیم:
1. وارد کردن داده‌ها و معیارها
2. نرمال‌سازی داده‌ها
3. وزن‌دهی به معیارها
4. تعیین راه‌حل ایده‌آل و منفی
5. محاسبه فاصله هر گزینه از این راه‌حل‌ها
6. محاسبه نسبت نزدیکی هر گزینه به راه‌حل ایده‌آل
7. رتبه‌بندی نهایی گزینه‌ها
در ادامه، هر مرحله را به صورت کامل شرح می‌دهیم و در نهایت، کدهای VBA برای اتوماتیک کردن این فرآیند ارائه می‌دهیم.
---

مرحله ۱: وارد کردن داده‌ها

در ابتدای کار، باید داده‌های مربوط به هر گزینه و معیارها را وارد کنید. فرض کنیم در شیت "Data"، داده‌ها به صورت جدول قرار دارند، که در آن:
- ستون A، نام گزینه‌ها است
- ستون‌های دیگر، معیارهای مختلف با مقادیر مربوط به هر گزینه
به عنوان مثال، فرض کنید ۵ گزینه و ۳ معیار دارید، و داده‌ها در سلول‌های A2 تا D6 قرار دارند.
---

مرحله ۲: نرمال‌سازی داده‌ها

در تکنیک تاپسیس، معیارها باید نرمال شوند، چون مقیاس‌های مختلف دارند. یکی از روش‌های رایج، نرمال‌سازی با روش مایوس (روش تقسیم بر جذر مجموع مربعات) است.
برای هر معیار، باید:
1. مجموع مربعات هر ستون را محاسبه کنید.
2. هر مقدار را بر ریشه مجموع مربعات آن معیار تقسیم کنید.
در اکسل، این کار به شکل زیر انجام می‌شود:
فرض کنید داده‌های معیارها در محدوده B2:D6 قرار دارند. در سلول F2، فرمول زیر را وارد کنید:
`=B2 / SQRT(SUMSQ($B$2:$B$6))`
و این فرمول را به سمت پایین و سمت راست کپی کنید، تا نرمال‌سازی برای تمام داده‌ها انجام شود.
---

مرحله ۳: وزن‌دهی به معیارها

بعد از نرمال‌سازی، باید هر معیار را بر اساس اهمیت آن وزن دهیم. فرض کنید وزن‌ها در سلول‌های F1، G1، H1 وارد شده‌اند، مثلا:
- وزن معیار ۱ در F1
- وزن معیار ۲ در G1
- وزن معیار ۳ در H1
در نتیجه، هر مقدار نرمال شده، باید در ضرب در وزن مربوطه شود.
در ستون‌های جدید، مثلا در I2، فرمول زیر را وارد کنید:
`=F2 * $F$1`
و همین کار را برای سایر معیارها انجام دهید.
---

مرحله ۴: تعیین راه‌حل‌های ایده‌آل و منفی

برای هر معیار، راه‌حل ایده‌آل و منفی را تعیین می‌کنیم:
- راه‌حل ایده‌آل، بیشترین مقدار (در صورت معیار مثبت) یا کم‌ترین (در صورت معیار منفی)
- راه‌حل منفی، برعکس آن
در اکسل، می‌توانید از توابع `MAX` و `MIN` برای تعیین این مقادیر استفاده کنید.
فرض کنید داده‌های نهایی در ستون‌های I، J، K قرار دارند. در سلول L2، راه‌حل ایده‌آل برای معیار ۱ را وارد می‌کنید:
`=MAX(I2:I6)`
و در سلول M2، راه‌حل منفی برای همان معیار:
`=MIN(I2:I6)`
این کار را برای هر معیار تکرار کنید.
---

مرحله ۵: محاسبه فاصله هر گزینه از راه‌حل‌ها

برای هر گزینه، فاصله از راه‌حل ایده‌آل و منفی را محاسبه می‌کنیم، با استفاده از فاصله اقلیدسی.
در سلول N2، فاصله از راه‌حل ایده‌آل:
`=SQRT(SUMXMY2(I2:K2, $L$2:$L$4))`
و در سلول O2، فاصله از راه‌حل منفی:
`=SQRT(SUMXMY2(I2:K2, $M$2:$M$4))`
این فرمول‌ها، فاصله هر گزینه را نسبت به راه‌حل‌های مثبت و منفی محاسبه می‌کنند.
---

مرحله ۶: محاسبه نسبت نزدیکی (Closeness Coefficient)

نسبت نزدیکی، نشان‌دهنده میزان نزدیکی هر گزینه به راه‌حل ایده‌آل است و با فرمول زیر محاسبه می‌شود:
`=O2 / (N2 + O2)`
در سلول P2 قرار می‌گیرد، و باید برای همه گزینه‌ها کپی شود.
این مقدار، بین ۰ و ۱ است، و هر چه نزدیک‌تر به ۱ باشد، گزینه بهتر است.
---

مرحله ۷: رتبه‌بندی نهایی و نتیجه‌گیری

در نهایت، بر اساس مقادیر نسبت نزدیکی، گزینه‌ها را رتبه‌بندی می‌کنیم. می‌توان از تابع `RANK.EQ` در اکسل استفاده کرد:
در سلول Q2:
`=RANK.EQ(P2, $P$2:$P$6, 0)`
و این کار را برای دیگر گزینه‌ها تکرار کنید.
در نتیجه، گزینه‌هایی که رتبه‌های پایین‌تر دارند، بهترین گزینه‌ها محسوب می‌شوند.
---

پیاده‌سازی اتوماتیک با VBA

برای اینکه فرآیند بالا به صورت خودکار انجام شود، می‌توان کد VBA نوشت. در ادامه، نمونه کد VBA برای پیاده‌سازی کامل این فرآیند ارائه می‌شود، که کاربران می‌توانند در محیط VBA اکسل قرار دهند و اجرا کنند.

vba  

Sub TPSSIS()

    Dim dataRange As Range  

    Dim normalizedRange As Range  

    Dim weights As Range  

    Dim ideal As Range  

    Dim nadir As Range  

    Dim distancesPos As Range  

    Dim distancesNeg As Range  

    Dim closeness As Range  

    Dim nRows As Integer, nCols As Integer  

    Dim i As Integer, j As Integer
' تعیین محدوده داده‌ها  

    Set dataRange = Sheets("Data").Range("B2:D6")
nRows = dataRange.Rows.Count  

    nCols = dataRange.Columns.Count
' نرمال‌سازی  

    For j = 1 To nCols  

        Dim colSumSquares As Double  

        colSumSquares = 0  

        For i = 1 To nRows  

            colSumSquares = colSumSquares + (dataRange.Cells(i, j).Value) ^ 2  

        Next i  

        Dim denom As Double  

        denom = Sqr(colSumSquares)

        For i = 1 To nRows  

            dataRange.Cells(i, j).Offset(0, nCols + 1).Value = dataRange.Cells(i, j).Value / denom  

        Next i  

    Next j
' وزن‌دهی  

    ' فرض بر این است که وزن‌ها در سلول‌های F1، G1، H1 وارد شده است  

    For j = 1 To nCols  

        For i = 1 To nRows  

            dataRange.Cells(i, j).Offset(0, 2 * nCols + 1).Value = dataRange.Cells(i, j).Offset(0, nCols + 1).Value * Sheets("Data").Cells(1, j + 5).Value  

        Next i  

    Next j
' تعیین راه‌حل‌های ایده‌آل و منفی  

    Dim idealArr() As Double  

    Dim nadirArr() As Double  

    ReDim idealArr(1 To nCols)

    ReDim nadirArr(1 To nCols)
For j = 1 To nCols  

        Dim colValues() As Double  

        ReDim colValues(1 To nRows)

        For i = 1 To nRows  

            colValues(i) = dataRange.Cells(i, j).Offset(0, 2 * nCols + 1).Value  

        Next i  

        idealArr(j) = Application.WorksheetFunction.Max(colValues)

        nadirArr(j) = Application.WorksheetFunction.Min(colValues)

    Next j
' محاسبه فاصله‌ها  

    For i = 1 To nRows  

        Dim distPos As Double, distNeg As Double  

        distPos = 0  

        distNeg = 0  

        For j = 1 To nCols  

            Dim val As Double  

            val = dataRange.Cells(i, j).Offset(0, 2 * nCols + 1).Value  

            distPos = distPos + (val - idealArr(j)) ^ 2  

            distNeg = distNeg + (val - nadirArr(j)) ^ 2  

        Next j  

        distPos = Sqr(distPos)

        distNeg = Sqr(distNeg)

        ' محاسبه نسبت نزدیکی  

        Sheets("Data").Cells(i + 1, nCols * 3 + 4).Value = distNeg / (distPos + distNeg)

    Next i
' رتبه‌بندی  

    Dim closenessArr() As Double  

    ReDim closenessArr(1 To nRows)

    For i = 1 To nRows  

        closenessArr(i) = Sheets("Data").Cells(i + 1, nCols * 3 + 4).Value  

    Next i
Dim ranks() As Variant  

    ranks = Application.WorksheetFunction.Rank_Eq(closenessArr, closenessArr, 0)
For i = 1 To nRows  

        Sheets("Data").Cells(i + 1, nCols * 4 + 4).Value = ranks(i)

    Next i
MsgBox "پیاده‌سازی کامل شد!"

End Sub  

در این کد، فرض بر این است که داده‌ها در شیت "Data" قرار دارند، وزن‌ها در سلول‌های F1، G1، H1 وارد شده است و نتایج در ستون‌های بعدی نمایش داده می‌شوند.
---

نتیجه‌گیری و جمع‌بندی

در این مقاله، به صورت کامل و جامع، مراحل پیاده‌سازی تکنیک تاپسیس در اکسل را شرح دادیم. از وارد کردن داده‌ها، نرمال‌سازی، وزن‌دهی، تعیین راه‌حل‌های ایده‌آل و منفی، محاسبه فاصله‌ها، نسبت نزدیکی، تا رتبه‌بندی نهایی، همه را با جزئیات و مثال‌های عملی همراه کردیم. همچنین، نمونه کد VBA در اختیار شما قرار دادیم تا فرآیند به صورت خودکار و سریع انجام شود.
این روش، به دلیل سادگی و در عین حال قدرت، یکی از بهترین گزینه‌ها برای تصمیم‌گیری‌های چندمعیاره است. با پیروی از این راهنما، می‌توانید در پروژه‌های خود، سیستم‌های تصمیم‌گیری کارآمد و دقیقی پیاده‌سازی کنید، و از تحلیل‌های چندمعیاره بهره‌برداری کامل داشته باشید.
اگر نیاز دارید، می‌توانید این کد و فرآیند را بر اساس داده‌های خاص خود، شخصی‌سازی و توسعه دهید، و نتایج مطلوب‌تری بدست آورید. به خاطر داشته باشید، تمرین و تکرار در این حوزه، کلید مهارت است. بنابراین، پیشنهاد می‌کنم، نمونه‌های مختلف را امتحان کنید و مهارت خود را در پیاده‌سازی این تکنیک، افزایش دهید.