جستجو

ترفندهای کامپیوتری

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
ربات پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfand_bot

خلاصه آمار

تبليغات

پربحث ترين ها

۲

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

اکسل ماکرونویسی

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

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

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

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

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

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

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

=$C$4

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

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

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

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


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

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

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

---------------------------------------

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

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

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

-----------------------------------------------------
روش ۳: با استفاده از ماکرو
استفاده از کلید F4 برای تبدیل آدرس‌های موجود در یک سلول بسیار کاربردی است اما اگر بخواهید اینکار را برای تعداد زیادی سلول انجام دهید نمی‌توانید چند سلول را انتخاب کرده و از کلید F4 استفاده کنید چون این کار عملی نیست. برای این حالت بهتر است از کد ماکرو استفاده کنید.

  

برای ایجاد ماکرو، کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

در پنجره جدید باز شده کدهای زیر را کپی کنید.

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

سپس روی دکمه doc-multiply-calculation-3 کلیک کنید یا کلید F5 را فشار دهید تا پنجره‌ی زیر ظاهر شود. ناحیه سلول‌های مدنظرتان (مثلا A1:A4) را انتخاب کنید.

سپس روی دکمه Ok کلیک کنید. در پنجره جدید باز شده با وارد کردن اعداد ۱ تا ۴ باید مشخص کنید که چه عملیاتی را می‌خواهید انجام دهید. (۱ برای تبدیل به مطلق، ۲ برای تبدیل به مطلق سطری، ۳ برای تبدیل به مطلق ستونی و ۴ برای تبدیل به نسبی)

در پایان روی دکمه Ok کلیک کنید تا تبدیل انجام شود.

منبع با تغییر: extendoffice.com

کدهای ماکروی دیگر را می‌توانید در این سایت و این سایت و این سایت مشاهده کنید.

--------------------------------------------------------------

روش ۴: با استفاده از افزونه

- افزونه Kutools for Excel: نحوه دانلود و استفاده از این افزونه را می‌توانید در اینجا مشاهده کنید.

- افزونه ASAP Utilities: نحوه دانلود و استفاده از این افزونه را می‌توانید در اینجا مشاهده کنید.

 

عضویت در خبرنامه:

نظرات (۲)

  1. علاقمند به یادگیری اکسل

    سلام ، ممنونم بابت به اشتراک گذاشتن دانش خوبتان .
    موفق باشید

ارسال نظر

قبل از ارسال نظر به نکات زیر توجه کنید:

۱- با توجه با اینکه نظرات خصوصی شما امکان نمایش در سایت را ندارد، بنابراین هنگام ارسال نظر، گزینه "" را انتخاب نکنید. چون تنها راه پاسخگویی به آن از طریق ایمیل شما است که با توجه به مشغله کاری فرصت ارسال پاسخ از طریق ایمیل وجود ندارد.

۲- قبل از مطرح کردن سوالتان، مطلب فوق را با دقت مطالعه کنید. اگر پاسخ سوالتان را پیدا نکردید در سایت جستجو کنید، ممکن است در مطلب دیگر پاسخ سوالتان را پیدا کنید.

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی
X بستن