Anne Marie دارای طیف وسیعی از سلول های حاوی فرمول هایی است که از مراجع نسبی استفاده می کنند. او می خواهد مرجع دوم در هر فرمول را از نسبی به مطلق تغییر دهد، بنابراین می پرسد آیا راهی برای انجام این کار بدون نیاز به ویرایش فرمول هر سلول به صورت جداگانه وجود دارد؟
پاسخ به این سوال کاملاً به ویژگی های فرمول هایی که با آنها کار می کنید بستگی دارد. اگر داده های شما مانند اکثر داده هایی که دیده ام مرتب شده اند، معمولاً ستونی خواهید داشت که حاوی فرمول هایی بر اساس مقادیر در سایر ستون ها و/یا سلول ها است. شما معمولاً فرمول را در بالای ستون وارد می کنید و سپس آن فرمول را برای هر تعداد ردیفی که نیاز دارید، در پایین ستون کپی می کنید. (البته این می تواند 90 درجه چرخانده شود تا فرمول ها به جای ستون های پایین در سراسر ستون ها قرار گیرند.)
اگر این نحوه چیدمان داده های شما را توصیف می کند، تنها کاری که باید انجام دهید این است که فرمول را در سلول بالای ستون ویرایش کنید تا مرجع مطلق مورد نظر شما باشد و سپس Enter را فشار دهید. دوباره سلول را انتخاب کنید و سپس روی دستگیره پر در پایین سمت راست سلول دوبار کلیک کنید. در نهایت با فرمول اصلاح شده که در ستون کپی شده است، می شوید. نکته اصلی این است که شما واقعاً فقط نیاز به ویرایش یک فرمول واحد داشتید، نه همه فرمول ها را جداگانه.
اگر داده های شما کمی متفاوت است (نه در ستونی به روشی که توضیح داده شد)، می توانید از Find and Replace استفاده کنید تا ویرایش را برای شما انجام دهد. با این حال، ابتدا باید بفهمید که آیا الگوی ثابتی در مرجع دوم در فرمول هایی که می خواهید تغییر دهید وجود دارد یا خیر. به عنوان مثال، اگر می خواهید همه ارجاعات را به ستون D تغییر دهید تا مطلق باشند، می توانید به این ترتیب عمل کنید:
شکل 1. تب Replace از کادر محاوره ای Find and Replace.
- محدوده سلول هایی را که می خواهید ویرایش کنید انتخاب کنید.
- Ctrl+H را فشار دهید تا تب Replace در کادر محاوره ای Find and Replace نمایش داده شود.
- در صورت موجود بودن دکمه Options را کلیک کنید. (شکل 1 را ببینید.)
- در کادر Find What، حرف بزرگ D را وارد کنید .
- در کادر Replace With، "$D" (بدون علامت نقل قول) را وارد کنید.
- روی کادر بررسی Match Case کلیک کنید.
- روی Replace All کلیک کنید.
این باید همه ارجاع به ستون D را با $D جایگزین کند، که نشان می دهد مرجع مطلق است. با این حال، باید بدانید که جایگزین هر نمونه ای از D بزرگ در مقادیر متنی نیز می شود، به علاوه اگر هر یک از فرمول های شما قبلاً دارای $D باشد، در نهایت با $$D خواهید رسید. (شما می توانید با انجام یافتن و جایگزینی دوم که در آن $$D را پیدا می کنید و آن را با $D جایگزین می کنید، این آخرین مشکل احتمالی را برطرف کنید.) این رویکرد همچنین تمام ارجاعات را به ستون D تغییر می دهد، صرف نظر از اینکه آنها در دوم هستند یا خیر. مرجع یا نه
اگر مطلقاً فقط می خواهید مرجع دوم را در هر فرمولی تغییر دهید، باید یک ماکرو برای انجام کار ایجاد کنید. ماکرو باید سلول های حاوی فرمول را بررسی کند، مرجع سلول دوم را انتخاب کند و سپس مطمئن شود که نشانه های دلار در جلوی سطر و ستون در مرجع وجود دارد. با این حال، گفتن این کار آسان تر از انجام آن است. بخش دشوار این است که مرجع دوم را در فرمول انتخاب کنید. برای یک ماکرو، چگونه می تواند مرجع دوم را از فرمولی مانند شکل زیر بفهمد؟
=A23+SUM(B7:B19)/C15
آیا مرجع دوم B7، B19، B7:B19 است، یا آیا شما از آن مرجع کاملاً صرف نظر می کنید زیرا از نظر فنی یک پارامتر برای یک تابع است و در عوض از C15 استفاده می کنید؟ چنین تعیین هایی معمولاً بهتر است به یک بررسی موردی واگذار شوند، به این معنی که شما دوباره به بررسی و احتمالاً ویرایش هر سلول به صورت جداگانه، بدون کمک ماکرو، بازمی گردید.
