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

اگر بیش از یک روز است که در اکسل کار می کنید، احتمالاً قبلاً از مراجع علامت دلار در فرمول ها و توابع اکسل استفاده کرده اید یا از آنها استفاده کرده اید. $ 2 دلار or F$3 و غیره. در نهایت بیایید بفهمیم که دقیقاً منظور آنها چیست، چگونه کار می کنند و کجا می توانند در فایل های شما مفید باشند.

پیوندهای نسبی

اینها ارجاعات منظمی هستند که به شکل یک ستون حرف-ردیف شماره ( A1, С5(یعنی «نبرد کشتی») که در اکثر فایل‌های اکسل یافت می‌شود. ویژگی آنها این است که هنگام کپی کردن فرمول ها جابجا می شوند. آن ها C5به عنوان مثال تبدیل می شود С6, С7 و غیره هنگام کپی کردن یا به D5, E5 و غیره هنگام کپی به سمت راست و غیره در اکثر موارد این امر طبیعی است و مشکلی ایجاد نمی کند:

پیوندهای مختلط

گاهی اوقات این واقعیت که پیوند در فرمول، هنگام کپی کردن، "اسلاید" نسبت به سلول اصلی نامطلوب است. سپس برای تعمیر لینک از علامت دلار ($) استفاده می شود که به شما امکان می دهد آنچه را که قبلاً آمده است را اصلاح کنید. بنابراین، برای مثال، پیوند $C5 در بین ستون ها تغییر نخواهد کرد (یعنی С هرگز تبدیل نخواهد شد D, E or F) ، اما ممکن است در بین خطوط جابجا شود (یعنی ممکن است تغییر کند C6 دلار, C7 دلار و غیره.). به همین ترتیب، C$5 - در طول ردیف ها حرکت نمی کند، اما می تواند در امتداد ستون ها "راه برود". چنین پیوندهایی نامیده می شوند مختلط:

لینک های مطلق

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

ساده ترین و سریع ترین راه برای تبدیل یک مرجع نسبی به مرجع مطلق یا مختلط این است که آن را در فرمول انتخاب کنید و کلید F4 را چندین بار فشار دهید. این کلید هر چهار گزینه ممکن را برای رفع پیوند به یک سلول حلقه می کند: C5 → 5 دلار کانادا → $C5 → C$5 و دوباره همه چیز

همه چیز ساده و واضح است. اما یک "اما" وجود دارد.

فرض کنید می خواهیم یک مرجع سلول مطلق بسازیم С5. به طوری که او همیشه به آن اشاره می کرد С5 صرف نظر از هر اقدام بعدی کاربر. این یک چیز خنده دار به نظر می رسد - حتی اگر پیوند را مطلق کنید (یعنی 5 دلار کانادا، هنوز در برخی شرایط تغییر می کند. به عنوان مثال: اگر خط سوم و چهارم را حذف کنید، به تغییر می کند 3 دلار کانادا. اگر ستونی را در سمت چپ وارد کنید С، سپس به تغییر خواهد کرد D. اگر یک سلول را قطع کنید С5 و بچسبانید F7، سپس به تغییر خواهد کرد F7 و غیره چه می شود اگر من یک لینک واقعا سخت بخواهم که همیشه به آن اشاره کند С5 و هیچ چیز دیگری تحت هیچ شرایطی یا اقدامات کاربر؟

پیوندهای واقعاً مطلق

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

اگر فرمول را در یک سلول وارد کنید:

=INDIRECT("C5")

=INDIRECT(«C5»)

سپس همیشه به سلول با آدرس اشاره می کند C5 صرف نظر از هرگونه اقدام بعدی کاربر، درج یا حذف ردیف و غیره. تنها مشکل جزئی این است که اگر سلول هدف خالی باشد، پس غیر مستقیم خروجی 0، که همیشه راحت نیست. با این حال، با استفاده از ساختار کمی پیچیده‌تر با بررسی عملکرد به راحتی می‌توان آن را دور زد خالی است:

=IF(ISNULL(INDIRECT("C5″))،"، INDIRECT("C5"))

=IF(ISBLANK(INDIRECT(«C5»));»»;INDIRECT(«C5»))

• XNUMXD ورق به گروه ارجاع می دهد هنگام ادغام داده ها از چندین جدول
• چرا به سبک پیوند R1C1 نیاز دارید و چگونه آن را غیرفعال کنید
• کپی کردن دقیق فرمول ها توسط یک ماکرو با افزونه PLEX