چگونه یک سلول را در فرمول اکسل فریز کنیم

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

در این صورت می توانید مرجع سلول را در اکسل تعمیر کنید. و این را می توان به چندین روش در یک زمان انجام داد. بیایید نگاهی دقیق تر به چگونگی دستیابی به این هدف بیندازیم.

لینک اکسل چیست؟

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

هر پیوند یک سلول با یک حرف و یک عدد را مشخص می کند. یک حرف نشان دهنده یک ستون و یک عدد نشان دهنده یک ردیف است. 

سه نوع پیوند وجود دارد: مطلق، نسبی و مختلط. مورد دوم به طور پیش فرض تنظیم شده است. مرجع مطلق مرجعی است که آدرس ثابتی از ستون و ستون داشته باشد. بر این اساس، مختلط همان چیزی است که در آن یک ستون یا یک ردیف جداگانه ثابت است.

روش 1

برای ذخیره آدرس ستون و ردیف، مراحل زیر را دنبال کنید:

1. روی سلول حاوی فرمول کلیک کنید.
2. روی نوار فرمول سلولی که نیاز داریم کلیک کنید.
3. F4 را فشار دهید.

در نتیجه، مرجع سلول به مطلق تغییر خواهد کرد. با علامت مشخصه دلار قابل تشخیص است. به عنوان مثال، اگر روی سلول B2 کلیک کنید و سپس روی F4 کلیک کنید، پیوند به این صورت خواهد بود: $B$2.

علامت دلار قبل از قسمت آدرس در هر سلول به چه معناست؟

1. اگر در مقابل یک حرف قرار گیرد، نشان می دهد که مرجع ستون بدون توجه به جایی که فرمول منتقل شده است، ثابت می ماند.
2. اگر علامت دلار جلوی عدد باشد، نشان دهنده پین ​​شدن رشته است. 

روش 2

این روش تقریباً مشابه روش قبلی است، فقط باید F4 را دو بار فشار دهید. برای مثال، اگر سلول B2 داشتیم، پس از آن تبدیل به B$2 می شود. به عبارت ساده، به این ترتیب توانستیم خط را درست کنیم. در این صورت حرف ستون تغییر می کند.

برای مثال در جداولی که باید محتویات سلول دوم را از بالا در سلول پایین نمایش دهید بسیار راحت است. به جای اینکه چنین فرمولی را چندین بار انجام دهید، کافی است سطر را درست کنید و اجازه دهید ستون تغییر کند.

روش 3

این روش کاملاً مشابه روش قبلی است، فقط باید کلید F4 را سه بار فشار دهید. سپس فقط ارجاع به ستون مطلق خواهد بود و سطر ثابت می ماند.

روش 4

فرض کنید به یک سلول اشاره مطلق داریم، اما در اینجا لازم بود آن را نسبی کنیم. برای این کار کلید F4 را آنقدر فشار دهید که هیچ علامت $ در لینک وجود نداشته باشد. سپس نسبی می شود و وقتی فرمول را جابجا یا کپی می کنید، آدرس ستون و آدرس ردیف تغییر می کند.

پین کردن سلول ها برای یک محدوده بزرگ

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

متأسفانه روش های استاندارد اکسل به این هدف نمی رسند. برای این کار باید از یک افزونه مخصوص به نام VBA-Excel استفاده کنید. این شامل بسیاری از ویژگی های اضافی است که به شما امکان می دهد کارهای رایج را با اکسل بسیار سریعتر انجام دهید.

این شامل بیش از صد عملکرد تعریف شده توسط کاربر و 25 ماکرو مختلف است و همچنین به طور منظم به روز می شود. این به شما امکان می دهد تقریباً از هر جنبه ای کار را بهبود بخشید:

1. سلول ها.
2. کلان
3. توابع از انواع مختلف.
4. پیوندها و آرایه ها.

به طور خاص، این افزونه به شما امکان می دهد پیوندها را در تعداد زیادی فرمول به طور همزمان اصلاح کنید. برای این کار باید مراحل زیر را انجام دهید:

1. یک محدوده انتخاب کنید.
2. تب VBA-Excel را که پس از نصب ظاهر می شود، باز کنید. 
3. منوی "توابع" را باز کنید، جایی که گزینه "Lock formulas" در آن قرار دارد.
   

   

4. سپس یک کادر محاوره ای ظاهر می شود که در آن باید پارامتر مورد نیاز را مشخص کنید. این افزونه به شما این امکان را می دهد که یک ستون و یک ستون را به طور جداگانه با هم پین کنید و همچنین پین کردن موجود را با یک بسته حذف کنید. پس از انتخاب پارامتر مورد نیاز با استفاده از دکمه رادیویی مربوطه، باید اقدامات خود را با کلیک کردن بر روی "OK" تأیید کنید.

مثال

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

در مثال ما، برای این شما باید فرمول = را وارد کنیدB2*C2. همانطور که می بینید بسیار ساده است. استفاده از مثال او برای توضیح اینکه چگونه می توانید آدرس یک سلول یا ستون یا سطر مجزای آن را اصلاح کنید بسیار آسان است. 

البته در این مثال می توانید سعی کنید با استفاده از نشانگر تکمیل خودکار فرمول را به پایین بکشید اما در این حالت سلول ها به صورت خودکار تغییر می کنند. بنابراین، در سلول D3 فرمول دیگری وجود خواهد داشت که در آن اعداد به ترتیب با 3 جایگزین می شوند. علاوه بر این، طبق طرح - D4 - فرمول به شکل = B4 * C4، D5 - به طور مشابه، اما با شماره 5 و غیره

اگر لازم باشد (در بیشتر موارد معلوم می شود)، پس هیچ مشکلی وجود ندارد. اما اگر باید فرمول را در یک سلول ثابت کنید تا هنگام کشیدن تغییر نکند، این کار تا حدودی دشوارتر خواهد بود. 

فرض کنید باید درآمد دلار را تعیین کنیم. بیایید آن را در سلول B7 قرار دهیم. بیایید کمی نوستالژیک شویم و هزینه 35 روبل به ازای هر دلار را نشان دهیم. بر این اساس، برای تعیین درآمد به دلار، باید مبلغ را به روبل بر نرخ دلار تقسیم کرد.

در مثال ما در اینجا به نظر می رسد.

اگر مانند نسخه قبلی سعی کنیم فرمولی را تجویز کنیم، شکست خواهیم خورد. به همین ترتیب، فرمول به فرمول مناسب تغییر خواهد کرد. در مثال ما به این صورت خواهد بود: =E3*B8. از اینجا می توانیم ببینیم. که قسمت اول فرمول به E3 تبدیل شده است و ما این وظیفه را برای خودمان قرار می دهیم اما نیازی نیست قسمت دوم فرمول را به B8 تغییر دهیم. بنابراین، ما باید مرجع را به یک مرجع مطلق تبدیل کنیم. این کار را می توانید بدون فشار دادن کلید F4 و فقط با گذاشتن علامت دلار انجام دهید.

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

خود فرمول به شکل زیر خواهد بود:

=D2/$B$7

توجه! ما دو علامت دلار را نشان دادیم. به این ترتیب به برنامه نشان می دهیم که هم ستون و هم سطر باید ثابت شوند.

ارجاعات سلولی در ماکروها

ماکرو زیربرنامه‌ای است که به شما امکان می‌دهد اقدامات را خودکار کنید. برخلاف عملکرد استاندارد اکسل، یک ماکرو به شما امکان می دهد بلافاصله یک سلول خاص را تنظیم کنید و اقدامات خاصی را تنها در چند خط کد انجام دهید. برای پردازش دسته ای اطلاعات مفید است، برای مثال، اگر راهی برای نصب افزونه ها وجود نداشته باشد (مثلاً از رایانه شرکت استفاده می شود، نه شخصی).

ابتدا باید درک کنید که مفهوم کلیدی یک ماکرو، اشیایی است که می تواند شامل اشیاء دیگری باشد. شی Workbooks مسئول کتاب الکترونیکی (یعنی سند) است. شامل شی Sheets است که مجموعه ای از تمام برگه های یک سند باز است. 

بر این اساس، سلول ها یک شی سلول هستند. این شامل تمام سلول های یک صفحه خاص است.

هر شیء با آرگومان های پرانتز واجد شرایط است. در مورد سلول ها به این ترتیب ارجاع داده می شوند. ابتدا شماره ردیف و سپس شماره ستون یا حرف ذکر شده است (هر دو قالب قابل قبول هستند).

برای مثال، یک خط کد حاوی ارجاع به سلول C5 به شکل زیر است:

کتاب‌های کار ("Book2.xlsm"). Sheets ("List2"). سلول‌ها (5، 3)

کتابهای کار ("Book2.xlsm"). Sheets ("List2"). سلولها (5، "C")

شما همچنین می توانید با استفاده از یک شی به یک سلول دسترسی داشته باشید مرتب. به طور کلی، در نظر گرفته شده است که به یک محدوده ارجاع داده شود (که به هر حال، عناصر آن می توانند مطلق یا نسبی نیز باشند)، اما شما به سادگی می توانید یک نام سلول را با همان فرمت یک سند اکسل بدهید.

در این صورت خط به این شکل خواهد بود.

کتابهای کاری ("Book2.xlsm"). Sheets ("List2"). Range ("C5")

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

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

نتیجه گیری

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