[حل شده] لینك داینامیك برای مقدار متغیر

[ظهور 313]

سلام به همه دوستان
میخوام یك مقدار در یك ستون رو هر وقت تغییر میدم به همون سلول در ستون مورد نظر، مراجعه كنه
از اساتید كسی میتونه راهنمایی كنه؟

[Ali Parsaei]

سلام،
سوالتان يک کم گنگ است، من يک توضيح مي دهم ببينيد منظورتان يک همچين چيزي است:
اولا" که به صورت معمول نمي شود يک سلول را تغيير دهيد و يک لينک به جاي خاصي فعال شود.
شما مي توانيد يک سلول را به جاي خاصي لينک کنيد ولي لينک سلول ياد شده نسبت به مندرجات سلول کناري آن متغيير باشد ... چطور؟
فرض کنيد نام فايل شما EXCEL و پسوند آن XLSX است و در صفحه اي به نام SHEET1 در فايل ياد شده قرار داريد.
شما مي توانيد در سلول B1 فرمول زير را بنويسيد:

=HYPERLINK("[EXCEL.xlsx]SHEET1!"&ADDRESS(A1;1);"LINK") 

حالا در سلول A1 هر عددي که بنويسيد، وقتي روي سلول B1 کليک مي کنيد به ستون A و سطري برابر عدد مندرج در سلول A1 لينک خواهيد شد.
مثلا" اگر در سلول A1 عدد 258 را درج کنيد با کليک روي سلول B1 به سل A258 منتقل خواهيد شد.
با تغيير مشخصات فايل و شيت در فرمول ياد شده مي توانيد آن را در فايلها و شيتهاي با نام ديگر استفاده کنيد.

[ظهور 313]

سلام،
سوالتان يک کم گنگ است، من يک توضيح مي دهم ببينيد منظورتان يک همچين چيزي است:
اولا" که به صورت معمول نمي شود يک سلول را تغيير دهيد و يک لينک به جاي خاصي فعال شود.
شما مي توانيد يک سلول را به جاي خاصي لينک کنيد ولي لينک سلول ياد شده نسبت به مندرجات سلول کناري آن متغيير باشد ... چطور؟
فرض کنيد نام فايل شما EXCEL و پسوند آن XLSX است و در صفحه اي به نام SHEET1 در فايل ياد شده قرار داريد.
شما مي توانيد در سلول B1 فرمول زير را بنويسيد:

=HYPERLINK("[EXCEL.xlsx]SHEET1!"&ADDRESS(A1;1);"LINK") 

حالا در سلول A1 هر عددي که بنويسيد، وقتي روي سلول B1 کليک مي کنيد به ستون A و سطري برابر عدد مندرج در سلول A1 لينک خواهيد شد.
مثلا" اگر در سلول A1 عدد 258 را درج کنيد با کليک روي سلول B1 به سل A258 منتقل خواهيد شد.
با تغيير مشخصات فايل و شيت در فرمول ياد شده مي توانيد آن را در فايلها و شيتهاي با نام ديگر استفاده کنيد.

ظاهرا من نتونستم سوالم رو خوب توضیح بدم،
یك ستون دارم كه مجموعه*ای از داده*ها (مثلا عدد 1 تا 1000) در اون قرار گرفته، حالا میخوام در بالای ستون وقتی یكی از اعداد رو تایپ میكنم، در سلول كناری، دكمه*ای قرار داشته باشه كه من رو به محل دقیق همین عدد در ستون اعداد ببره
الان دیگه فكر كنم خوب توضیح دادم

[ظهور 313]

آقا كسی نیست راهنمایی كنه؟

[Ali Parsaei]

همانطور که قبلا" گفتم اگر فايلتان به نام و پسوند: EXCEL.XLSX باشد و در صفحه اي به نام SHEET1 بخواهيد اين کار را انجام دهيد بايد:
اولا" بايد داده هايتان از سل A2 به بعد در ستون A درج شده باشد
در سل A1 عدد مورد نظرتان را درج مي نماييد
در يک سل ديگر (فرضا" سل B1) فرمول زير را بنويسيد:

=HYPERLINK("[EXCEL.xlsx]SHEET1!"&ADDRESS(MATCH(A1;A2:A1000000;0)+1;1);"LINK") 

اين فرمول تا سطر 1/000/000 ستون A عمل مي کند.
اگر توضيح فرمول را خواستيد بفرماييد تا برايتان شرح دهم که چطور عمل مي کند.

[ظهور 313]

همانطور که قبلا" گفتم اگر فايلتان به نام و پسوند: EXCEL.XLSX باشد و در صفحه اي به نام SHEET1 بخواهيد اين کار را انجام دهيد بايد:
اولا" بايد داده هايتان از سل A2 به بعد در ستون A درج شده باشد
در سل A1 عدد مورد نظرتان را درج مي نماييد
در يک سل ديگر (فرضا" سل B1) فرمول زير را بنويسيد:

=HYPERLINK("[EXCEL.xlsx]SHEET1!"&ADDRESS(MATCH(A1;A2:A1000000;0)+1;1);"LINK") 

اين فرمول تا سطر 1/000/000 ستون A عمل مي کند.
اگر توضيح فرمول را خواستيد بفرماييد تا برايتان شرح دهم که چطور عمل مي کند.

بله دوست عزیز، ممنون میشم اگه بیشتر توضیح بدید

[rasools13]

ظاهرا من نتونستم سوالم رو خوب توضیح بدم،
یك ستون دارم كه مجموعه*ای از داده*ها (مثلا عدد 1 تا 1000) در اون قرار گرفته، حالا میخوام در بالای ستون وقتی یكی از اعداد رو تایپ میكنم، در سلول كناری، دكمه*ای قرار داشته باشه كه من رو به محل دقیق همین عدد در ستون اعداد ببره
الان دیگه فكر كنم خوب توضیح دادم

سلام از ماکروی ذیل میتونید استفاده کنید یک نمونه هم در فایل پیوست است

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim Target As Variant
    Dim i, Result As Integer
    Target = Sheets(1).Range("D3")
    Set Cell = Cells.Find(What:=Target, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not Cell Is Nothing Then
       Cell.Activate
       Result = Result + 1
    End If
    
    If Result = 0 Then
        MsgBox "موردي پيدا نشد"
    End If
End Sub 

[Ali Parsaei]

بله دوست عزیز، ممنون میشم اگه بیشتر توضیح بدید

از تابع HYPERLINK براي لينک کردن يک سلول به مکاني خاص استفاده مي شود
اين تابع دو قسمت (دو آرگومان) دارد
قسمت اول آدرس مکاني است که مي خواهيم سلول را به آن لينک کنيم
قسمت دوم يک عبارت متني است که مي خواهيم در سلول درج گردد ، اين عبارت بايد داخل: " " نوشته شود. مثلا" من در فرمولي که مثال زدم عبارت LINK را نوشته ام، شما مي توانيد از هر عبارت ديگر به دلخواه خود استفاده کنيد.
و اما در قسمت اول که آدرس مکان را مي خواهيم بنويسيم در مثالي که من زدم چون لينک در داخل خود فايل انجام مي گردد احتياجي به نوشتن آدرس درايو و نام پوشه ها و .... نيست
همانطور که در فرمول مشخص است ابتدا عبارت: " و سپس کروشه و بعد نام فايل همراه با پسوند فايل به طور کامل درج گشته و سپس کروشه را بسته و نام شيت مورد نظر به اضافه يک علامت تعجب درج کرده و پس باز علامت: " را مي نويسيم .... تا اينجا ما آدرس شيت مربوطه را به اکسل فهمانده ايم ... حالا بايد خانه اي را که مي خواهيم لينک به آن انجام گردد را ذکر کنيم
اگر به خانه اي ثابت مي خواستيم لينک شويم مي توانستيم آدرس آن خانه را در انتهاي نام شيت در فرمول اضافه کنيم و کار تمام بود، مثلا" اگر به خانه A20 مي خواستيم لينک شويم بايد مي نوشتيم:

=HYPERLINK("[EXCEL.xlsx]SHEET1!A20";"LINK") 

ولي آدرس ما دايناميک است
بنابراين ما بايد از روشي ديگر استفاده کنيم
تابع ADDRESS در اين مورد به ما کمک مي کند
در تابع آدرس ما ابتدا شماره سطر و سپس شماره ستون را به تابع مي دهيم و پاسخ تابع برابر نام خانه مورد نظر خواهد بود، فرضا" براي به دست آوردن ام خانه اي که در سطر دوم و ستون 25 قرار دارد مي نويسيم:

=ADDRESS(2;25) 

پاسخ خانه Y2 خواهد بود. البته تابع آرگومانهاي ديگري هم دارد که فعلا" به کار ما نمي آيد.
بنابراين ما با تابع آدرس مي توانيم يک آدرس متغيير به تابع هايپرلينک ارجاع کنيم
و اما يک کار ديگر هم بايد بکنيم، ما مي خواهيم عددي که در خانه A1 نوشته شده را در خانه A2 تا A1000000 پيدا کرده و شماره سطر آن را بفهميم تا از شماره سطر آن در تابع آدرس استفاده کنيم
براي اين کار از تابع MATCH استفاده مي کنيم. تابع MATCH سه قسمت (آرگومان) دارد، اين تابع در يک سطر يا ستون يک عبارت را جستجو کرده و در صورت يافتن آن عبارت به ما مي گويد که آن عبارت چندمين عبارت آن سطر يا ستون است، در قسمت اول تابع عبارت مورد نظرمان را مي نويسيم و در قسمت دوم آدرس سطر يا ستون مربوطه را مي نويسيم و در قسمت آخر نيز اگر عدد صفر را بنويسيم به تابع مي فهمانيم که دقيقا" دنبال همان عددي که ما نوشه ايم بگردد وگرنه تابع در جستجوهاي عددي در صورت نيافتن عدد ما، نزديکترين عدد به آن را مي يابد.
حال با تابع MATCH اکسل در محدوده A2 تا A1000000 به دنبال عدد نوشته شده ما در خانه A1 مي گردد، فرضا" اگر آن را در خانه A2 بيابد، پاسخ تابع MATCH عدد يک خواهد بود زيرا سل A2 اولين سل از محدوده جستجو مي باشد، اگر آن را در سل A10 بيابد پاسخ تابع MATCH عدد 9 خواهد بود زيرا سل A10 نهمين سطر از محدوده A2 تا A1000000 است. و ما براي اينکه شماره سطر سل را بيابيم بايد يک عدد به پاسخ تابع MATCH اضافه کنيم.
و حالا جمع بندي:
ما با تابع MATCH عدد نوشته شده در سل A1 را در محدوده A2 تا A1000000 يافته و يک عدد به آن اضافه مي کنيم تا شماره سطر آن به دست آيد:

MATCH(A1;A2:A1000000;0)+1 

بعد با تابع ADDRESS نام خانه مورد نظر خود را مي يابيم، يعني ما خانه اي را مي خواهيم که سطر آن پاسخ MATCH فوق و ستونش ستون يک باشد:

ADDRESS(MATCH(A1;A2:A1000000;0)+1;1) 

در آخر آدرس به دست آمده را با استفاده از عبارت: & به انتهاي نام شيت در تابع هايپرلينک اضافه مي کنيم. البته در فرمول فوق من محدوده را تا سل A1000000 گرفته ام، شما مي توانيد آن را به دلخواه تغيير دهيد.

=HYPERLINK("[EXCEL.xlsx]SHEET1!"&ADDRESS(MATCH(A1;A2:A1000000;0)+1;1);"LINK") 

اگر عبارت مورد نظر خود را مثلا" در سل B1 بنويسيد و ستون داده هايتان را از سل A1 شروع کنيد ديگر احتياجي به اضافه کردن "يک" به تابع MATCH نخواهيد داشت.
عبارت & به معني AND در انگليسي است و با آن مي توانيد رشته هاي متني را با هم ترکيب کنيد.

[ظهور 313]

از تابع HYPERLINK براي لينک کردن يک سلول به مکاني خاص استفاده مي شود
اين تابع دو قسمت (دو آرگومان) دارد
قسمت اول آدرس مکاني است که مي خواهيم سلول را به آن لينک کنيم
قسمت دوم يک عبارت متني است که مي خواهيم در سلول درج گردد ، اين عبارت بايد داخل: " " نوشته شود. مثلا" من در فرمولي که مثال زدم عبارت LINK را نوشته ام، شما مي توانيد از هر عبارت ديگر به دلخواه خود استفاده کنيد.
و اما در قسمت اول که آدرس مکان را مي خواهيم بنويسيم در مثالي که من زدم چون لينک در داخل خود فايل انجام مي گردد احتياجي به نوشتن آدرس درايو و نام پوشه ها و .... نيست
همانطور که در فرمول مشخص است ابتدا عبارت: " و سپس کروشه و بعد نام فايل همراه با پسوند فايل به طور کامل درج گشته و سپس کروشه را بسته و نام شيت مورد نظر به اضافه يک علامت تعجب درج کرده و پس باز علامت: " را مي نويسيم .... تا اينجا ما آدرس شيت مربوطه را به اکسل فهمانده ايم ... حالا بايد خانه اي را که مي خواهيم لينک به آن انجام گردد را ذکر کنيم
اگر به خانه اي ثابت مي خواستيم لينک شويم مي توانستيم آدرس آن خانه را در انتهاي نام شيت در فرمول اضافه کنيم و کار تمام بود، مثلا" اگر به خانه A20 مي خواستيم لينک شويم بايد مي نوشتيم:

=HYPERLINK("[EXCEL.xlsx]SHEET1!A20";"LINK") 

ولي آدرس ما دايناميک است
بنابراين ما بايد از روشي ديگر استفاده کنيم
تابع ADDRESS در اين مورد به ما کمک مي کند
در تابع آدرس ما ابتدا شماره سطر و سپس شماره ستون را به تابع مي دهيم و پاسخ تابع برابر نام خانه مورد نظر خواهد بود، فرضا" براي به دست آوردن ام خانه اي که در سطر دوم و ستون 25 قرار دارد مي نويسيم:

=ADDRESS(2;25) 

پاسخ خانه Y2 خواهد بود. البته تابع آرگومانهاي ديگري هم دارد که فعلا" به کار ما نمي آيد.
بنابراين ما با تابع آدرس مي توانيم يک آدرس متغيير به تابع هايپرلينک ارجاع کنيم
و اما يک کار ديگر هم بايد بکنيم، ما مي خواهيم عددي که در خانه A1 نوشته شده را در خانه A2 تا A1000000 پيدا کرده و شماره سطر آن را بفهميم تا از شماره سطر آن در تابع آدرس استفاده کنيم
براي اين کار از تابع MATCH استفاده مي کنيم. تابع MATCH سه قسمت (آرگومان) دارد، اين تابع در يک سطر يا ستون يک عبارت را جستجو کرده و در صورت يافتن آن عبارت به ما مي گويد که آن عبارت چندمين عبارت آن سطر يا ستون است، در قسمت اول تابع عبارت مورد نظرمان را مي نويسيم و در قسمت دوم آدرس سطر يا ستون مربوطه را مي نويسيم و در قسمت آخر نيز اگر عدد صفر را بنويسيم به تابع مي فهمانيم که دقيقا" دنبال همان عددي که ما نوشه ايم بگردد وگرنه تابع در جستجوهاي عددي در صورت نيافتن عدد ما، نزديکترين عدد به آن را مي يابد.
حال با تابع MATCH اکسل در محدوده A2 تا A1000000 به دنبال عدد نوشته شده ما در خانه A1 مي گردد، فرضا" اگر آن را در خانه A2 بيابد، پاسخ تابع MATCH عدد يک خواهد بود زيرا سل A2 اولين سل از محدوده جستجو مي باشد، اگر آن را در سل A10 بيابد پاسخ تابع MATCH عدد 9 خواهد بود زيرا سل A10 نهمين سطر از محدوده A2 تا A1000000 است. و ما براي اينکه شماره سطر سل را بيابيم بايد يک عدد به پاسخ تابع MATCH اضافه کنيم.
و حالا جمع بندي:
ما با تابع MATCH عدد نوشته شده در سل A1 را در محدوده A2 تا A1000000 يافته و يک عدد به آن اضافه مي کنيم تا شماره سطر آن به دست آيد:

MATCH(A1;A2:A1000000;0)+1 

بعد با تابع ADDRESS نام خانه مورد نظر خود را مي يابيم، يعني ما خانه اي را مي خواهيم که سطر آن پاسخ MATCH فوق و ستونش ستون يک باشد:

ADDRESS(MATCH(A1;A2:A1000000;0)+1;1) 

در آخر آدرس به دست آمده را با استفاده از عبارت: & به انتهاي نام شيت در تابع هايپرلينک اضافه مي کنيم. البته در فرمول فوق من محدوده را تا سل A1000000 گرفته ام، شما مي توانيد آن را به دلخواه تغيير دهيد.

=HYPERLINK("[EXCEL.xlsx]SHEET1!"&ADDRESS(MATCH(A1;A2:A1000000;0)+1;1);"LINK") 

اگر عبارت مورد نظر خود را مثلا" در سل B1 بنويسيد و ستون داده هايتان را از سل A1 شروع کنيد ديگر احتياجي به اضافه کردن "يک" به تابع MATCH نخواهيد داشت.
عبارت & به معني AND در انگليسي است و با آن مي توانيد رشته هاي متني را با هم ترکيب کنيد.

خیلی خیلی ممنون از توضیحات مفیدتون