طریقه تبدیل آدرس‌های نسبی به مطلق و بالعکس در اکسل

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

قبل از بیان روش‌های تبدیل آدرس‌های نسبی به مطلق، ابتدا به صورت خلاصه با این دو مدل آدرس‌دهی آشنا می‌شویم:

• آدرس‌ نسبی (Relative Address):

آدرس نسبی یعنی آدرسی که هنگام کپی کردن و جابجایی ثابت نماند و تغییر کند. به طور مثال اگر درون سلولی فرمول C4*10 را بنویسیم و این سلول را چه بصورت دستی و چه با استفاده از قابلیت AutoFill، در سه سلول پایین‌تر کپی کنیم فرمول قبلی به C7*10 تغییر پیدا می‌کند. این تغییر به علت نسبی بودن فرمول است که آدرس C4 به علت جابجا شدن به سه سلول پایین‌تر به C7 تبدیل می‌شود.

• آدرس مطلق (Absolute Address):

بر خلاف حالت قبل، آدرس مطلق یعنی آدرسی که هنگام عمل کپی ثابت بماند و تغییر نکند. برای نوشتن آدرس سلول به صورت مطلق علامت $ را قبل از نام ستون و شماره سطر وارد می‌کنیم. مثلا فرم مطلق آدرس C4 به صورت زیر می‌باشد:

=$C$4

نکته: علاوه بر فرم‌های فوق الذکر، آدرس‌های ترکیبی یا مختلط (Mixed Reference) نیز داریم که بصورت نیمه‌مطلق – نیمه‌نسبی هستند:

الف) حالت مطلق سطری (Row absolute) مثل C$4: در این حالت فقط سطر به صورت مطلق ارجاع داده شده است. یعنی در صورت کپی شدن این فرمول، نام ستون یعنی ستون C بسته به محل جدید کپی شدن، می‌تواند به صورت نسبی تغییر کند، اما شماره سطر یعنی سطر شماره ۴ که قبل از آن $ آمده، همیشه ثابت خواهد ماند.

ب) حالت مطلق ستونی (Column absolute) مثل o$C4: در این حالت فقط ستون به صورت مطلق ارجاع داده شده است. یعنی در صورت کپی شدن این فرمول، شماره سطر یعنی سطر شماره ۴، بسته به محل جدید کپی شدن، می‌تواند به صورت نسبی تغییر کند، اما نام ستون یعنی ستون C که قبل از آن $ آمده، همیشه ثابت خواهد ماند.

برای آشنایی با کاربرد این نوع از آدرس‌دهی به مثال ارائه شده در این سایت مراجعه کنید.

• روش‌های تبدیل آدرس‌های نسبی به مطلق و بالعکس:

روش ۱: درج دستی علامت $

ابتدایی‌ترین روش برای تبدیل آدرس نسبی به مطلق و بالعکس درج و حذف دستی علامت $ است.

—————————————

روش ۲: با استفاده از کلید F4

اگر قبل یا بعد یا حتی وسط یک آدرس نسبی (C4) کلیک کنید یا چنانچه آنرا انتخاب کنید و سپس کلید F4 را یک بار فشار دهید آدرس مورد نظر به آدرس مطلق (o$C$4) تبدیل می‌شود. اگر همین کلید را برای بار دوم فشار دهید از حالت مطلق به حالت مطلق سطری (C$4) تبدیل می‌شود و اگر برای بار سوم نیز این کلید را فشار دهید از حالت مطلق سطری به حالت مطلق ستونی (o$C4) در می‌آید. فشردن کلید F4 برایبار چهارم موجب می‌شود آدرس به حالت اولیه یعنی نسبی (C4) برگردد.

توجه: برای تبدیل تمام آدرس‌های نسبی به مطلق و یا بالعکس که در یک سلول وجود دارد کافیست تمام آدرس‌ها یا فرمول‌ها را به حالت انتخاب دربیاورید و کلید F4 را بفشارید.

Sub ConverFormulaReferences()
'pctarfand.ir & tarfandha.blog.ir'
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
Dim xIndex As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
xIndex = Application.InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Absolute = 1" & Chr(13) _
& "Row absolute = 2" & Chr(13) _
& "Column absolute = 3" & Chr(13) _
& "Relative = 4", xTitleId, 1, Type:=1)
For Each Rng In WorkRng
    Rng.Formula = Application.ConvertFormula(Rng.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, xIndex)
Next
End Sub