mixed_references_02

آموزش تصویری مراجع سلولی (رفرنس دهی سلولی) در نرم افزار اکسل

اساطیر زبان

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

  • آدرس دهی نسبی
  • آدرس دهی مطلق

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

مراجع نسبی

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

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

۱- سلولی که فرمول را شامل خواهد شد، انتخاب کنید. در مثال ما، سلول D2 را انتخاب خواهیم کرد.

C:\Users\takrayaneh\Desktop\Untitled.jpg۲- فرمول را به منظور محاسبه ارزش دلخواه، وارد کنید. در مثال ما، B2-C2 را تایپ خواهیم کرد.

C:\Users\takrayaneh\Desktop\reference_relative2_formula.png۳- کلید ENTER روی کیبورد (صفحه کلید) را انتخاب کنید. فرمول محاسبه خواهد شد و نتیجه در آن سلول، نشان داده خواهد شد.

۴- در گوشه سمت راست سلول مورد نظر، یک دستگیره مربعی شکل تو پر وجود دارد، در مثال ما دستگیره مورد نظر را در سلول D2 شناسایی خواهیم کرد.

C:\Users\takrayaneh\Desktop\reference_relative2_fill5.png۵- کلیک کنید، نگه دارید و دستگیره توپر را به سلول های دیگری که می خواهید پر شوند، درگ کنید در مثال ما، سلول های (D12-D3)D3:D12 را انتخاب خواهیم کرد.

C:\Users\takrayaneh\Desktop\reference_relative2_drag4.png۶- موس را رها کنید. فرمول از طریق مراجع نسبی در تمام سلول های انتخاب شده، کپی خواهد شد و مقادیر در هر سلول، محاسبه می شوند. C:\Users\takrayaneh\Desktop\reference_relative2_done.png

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

C:\Users\takrayaneh\Desktop\reference_relative2_splat.png

مراجع مطلق

شاید بارها پیش بیاید که شما نخواهید هنگام پر کردن سلول ها یک مرجع سلولی تغییر کند. بر خلاف مراجع نسبی، مراجع هنگام کپی یا پر شدن، تغییر نمیکنند. شما می توانید برای ثابت نگه داشتن یک سطر یا یک ستون، از مرجع مطلق استفاده کنید. یک مرجع مطلق در یک فرمول با افزودن علامت دلار ($) مشخص می شود. آن علامت می تواند جلو تر از مرجع ستون، ردیف یا هر دو قرار بگیرد. شما به طور کلی فرمت $A$2 را برای ساختن فرمول جدید که مراجع مطلق را شامل می شود، استفاده کنید. آن دو فرمت دیگر اغلب کاربردی ندارند. $A2 –A$2 وقتی که در حال نوشتن فرمول هستید، می توانید کلید F4 روی صفحه کلید را فشار دهید و بین مراجع سلول های نسبی و مطلق ، جابجا شوید. این یک راه اسان برای وارد کردن سریع یک مرجع مطلق می باشد.

ساختن و کپی یک و استفاده از مراجع مطلق

در مثال ما، ۷٫۵% از میزان مالیات فروش را در سلول E1 برای محاسبه مالیات فروش همه ی کالا ها در ستون D را استفاده خواهیم کرد. ما به مرجع سلول مطلق $E$1 در فرمولمان نیاز خواهیم داشت. به این خاطر که هر فرمولی که برای میزان مالیات مشابه به کار برده می شود، هدف ما این است که وقتی فرمول کپی می شود و سایر سلول های ستون D را پر میکند، مرجع ماثابت باقی بماند.

  1. سلولی که فرمول در آن باید باشد را انتخاب کنید. در مثال ما، D3 را انتخاب خواهیم کرد.

C:\Users\takrayaneh\Desktop\reference_absolute2_select.png۲- فرمول را برای محاسبه مقدار مورد نظر وارد کنید. در مثال ما، فرمولی که در تصویر می بینید را تایپ می کنیم.

C:\Users\takrayaneh\Desktop\reference_absolute2_formula.png۳- کلید ENTER از صفحه کلید را فشار دهید. فرمول محاسبه خواهد کرد و نتیجه در سلول، نشان داده خواهد شد.

۴- دستگیره ی مربع توپر در گوشه سمت راست سلول مورد نظر را پیدا کنید. در مثال ما، در سلول D3 این دستگیره پیدا می شود.

C:\Users\takrayaneh\Desktop\reference_absolute_fill6.png۵- کلیک کنید، روی دستگیره نگه دارید و دستگیره را تا سلول هایی که میخواهید پر شوند، درگ کنید، در مثال ما از سلول D4 تاD13 (D4:D13).

C:\Users\takrayaneh\Desktop\reference_absolute2_drag2.png۶- موس را رها کنید. فرمول، با ی مرجع مطلق به سلول های انتخاب شده کپی خواهد شد، و مقادیر در هر سلول، محاسبه خواهند شد.

C:\Users\takrayaneh\Desktop\reference_absolute2_done.png

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

C:\Users\takrayaneh\Desktop\reference_absolute2_splat2.png

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

C:\Users\takrayaneh\Desktop\reference_absolute_splat_new1.png

نکته مهم! چگونگی تجزیه و تحلیل داده های آماری پرسشنامه ای در برنامه اکسل

کاربرد مراجع سلول با چندین صفحه کار

اکسل به شما اجازه می دهد که به هر سلول در هر صفحه کاری، اشاره کنید. که این می تواند فوق العاده به شما کمک کند که به یک مقدار ویژه از یک صفحه به صفحه ی دیگر، برای انجام این کار، فقط نیاز به آغاز مرجع سلول با نام صفحه ی کار به همراه یک علامت هشدار (!) دارید. به عنوان مثال اگر می خواهید به سلول A1 روی صفحه ی ۱ اشاره کنید، مرجع سلول آن sheet1!a1 خواهد شد. به عنوان یادداشت که اگر نام صفحه کار، شامل یک دوره می شود، شما نیاز به علامت گیومه ساده (‘ ‘ ) دارید که دور نام آن بگذارید. به عنوان مثال، اگر می خواهید به سلول مرجع A1 روی صفحه ی کاری که نام آن بودجه یجولای است، اشاره کنید ، مرجع سلولی آن!A1’بودجه جولای’ خواهد بود. (‘July bedget’A1).

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

۱- سلولی را که میخواهید به آن مراجعه کنید، شناسایی کنید، (پیدا کنید) و صفحه کار آن را یادداشت کنید. در مثال ما، می خواهیم به سلول E14 روی صفحه کار سفارش منو اشاره کنیم.

C:\Users\takrayaneh\Desktop\reference_sheet_locate2.png

۲- به سمت صفحه ی کار مورد نظر حرکت کنید. در مثال ما، صفحه ی کار فاکتور تهییه غذا را انتخاب می کنیم.

C:\Users\takrayaneh\Desktop\reference_sheet2_switch2.png

۳- صفحه ی کار انتخاب شده، ظاهر خواهد شد.

۴- سلولی را که می خواهید مقدار در آن ظاهر شود را پیدا و انتخاب کنید. در مثال ما، سلول B2 را انتخاب می کنیم.

C:\Users\takrayaneh\Desktop\reference_sheet2_select.png۵- علامت مساوی(=) را تایپ کنید، نام صفحه کار به همراه علامت هشدار(!) ، و آدرس سلول.

C:\Users\takrayaneh\Desktop\reference_sheet_splat.png۶- در مثال ما،!E14’سفارش منو’= را تایپ می کنیم. ، !E14’menu order’ =

۷- کلید enter روی صفحه کلید را فشار دهید. مقدار سلول اشاره شده ظاهر خواهد شد.اگر مقدار سلولE14 روی صفحه کار سفارش منو تغییر کرد، آن مقدار ، به صورت اتوماتیک روی صفحه کار فاکتور تهییه غذا آپدیت (به روز) می شود.

C:\Users\takrayaneh\Desktop\reference_sheet2_done5.pngاگر نام صفحه کار را تغییر دهید، مرجع سلولی به صورت اتوماتیک ، نام جدید صفحه کار را نشان می دهد.

نکته مهم! آموزش کامل نوشتن فرمول ها در نرم افزار اکسل ۲۰۱۰-۲۰۱۶ (تصویری)

اگر نام صفحه کار را به صورت اشتباه وارد کنید، خطای #REF! در سلول، ظاهر خواهد شد. در مثال زیر، ما نام صفحه کار را اشتباه تایپ کردیم. روی علامت (!) کلیک می کنیم و گزینه ی مرد نظر را از منو انتخاب کرده، و اشتباه را ویرایش کرده یا نادیده می گیریم.

C:\Users\takrayaneh\Desktop\reference_sheet_error2.png

نکته مهم! نرم افزار اکسل را در کمتر از ۳ هفته بصورت حرفه ای بیاموزید! (بدون نیاز به کلاس)

اگر هرگونه سوالی در این زمینه دارید در بخش نظرات مطرح کنید تا سریع وآسان به آن پاسخ دهیم.

ویدیوی آموزشی

 

تفاوت پک های حرفه ای ما با آموزشهای رایگان: بطور خلاصه از زمین تا آسمان! 1-پکها، جدیدترین نسخه نرم افزارها را آموزش می دهند با قابلیت های بسیار بیشتر. 2-پکها توسط متخصص آن نرم افزار، به صورت کاملا پروژه محور و با حل چالش هایی که در مسیر کار عملی و حرفه ای با آن روبرو می شوید تهیه شده اند و بعد از استفاده، کاملا برای بازار کار آماده اید! 3- متد این پکها کاملا کار شده و تا ماهها، در ذهن تان ماندگارند و یادگیری بسیار سریعتر و کاملتری خواهید داشت. آموزشهای رایگان فقط دستورات نرم افزارها را (آنهم ناقص) بیان می کنند و تازه برای ورود به بازار باید ماهها تجربه عملی هم کسب کنید !!

turned_in, , , , ,

چرب زبان

با این اپلیکیشن ساده، هر زبانی رو فقط با 5 دقیقه در روز، توی 80 روز مثل بلبل حرف بزن! بهترین متد روز، تقویت حافظه، آموزش تصویری. یادگیری زبان کلید یادگیری هر مهارتی در قرن 21 !


حتما بخوانید!

11 دیدگاه. ارسال دیدگاه جدید

  • سلام. ببخشید می خوام در انتهای فایل اکسلم، تعدادی عکس بزارم و در ابتدای فایل اکسل به این عکس ها رفرنس بدم طوری که با کلیک روی شماره نوشته شده به عکس گذاشته شده مراجعه بشه..چطوری اینکار رو انجام بدم. ممنون

    پاسخ
    • درود. یک راه آسان این است که عکس ها را آپلود کنید، روی یک سلول کلیک کنید، متن دلخواه را وارد کنید، کلیدهای Ctrl+K را فشار دهید ولینک عکسی که اپلود کردید را بدهید. موفق باشید

      پاسخ
      • سلام ممنون از آموزش خوب شما من مشکلی دارم که به این صورت است در شیت1 در خانه d5 فرمولی دارم که حاصل خانه های دیگر است و به صورت عددی است،میخواهم در شیت2 در خانه g5 محتواهای d5 قرار گیرد که به صورت عددی باشد ولی به این صورت به نمایش در می اید =`شیت۱`!d5 و هر کاری میکنم محتوای d5را نمایش نمیده.با تشکر

        پاسخ
  • سلام واسه رشته حسابداری کدوم بخش اکسل بیشتر استفاده میشه

    پاسخ
  • سلام،میخوام در دو فایل جدای اکسل یه طوری فرمول بنویسم که با تغییر عدد در یک فایل در دیگری هم تغییر کند.

    پاسخ
  • با سلام و احترام
    من نیاز دارم که فرمولی بنویسم که بتونه ۵ سلول اخیر در سطر ۱ رو جمع بزنه و نتیجه رو در سطر دیگری درست در زیر همان ستون بنویسه.
    در واقع می خواهم هر بار که عدد تازه ای در سطر ۱ وارد می کنم، جمع ۵ سلول اخیر در سطر ۱ در سطر ۱۳ درست در همان ستون نوشته شود.
    آیا فرمولی برای اشاره به ۵ سلول اخیر در یک سطر وجود دارد؟ ممنون میشوم اگر این فرمول را در اختیار من بگذارید.

    پاسخ
  • سلام
    من یه فایل ۲۰۰ ردیفی دارم شامل شماره فنی قطعات و می خواهم ارزش دلاری هر قطعه را از یک فایل خیلی بزرگتر که شامل ۵۰۰۰ قطعه می شود جستجو کنم.اگر تک تک جستجو کنم خیلی زمان می برد. آیا فرمولی هست که بر اساس آن بشه راحت قیمت ها را از فایل اصلی پیدا کرد.

    پاسخ
  • سلام شدیدا نیاز به کمک دارم. یه فرمولی میخوام میخوام که هر محصولی با کمترین تعداد به ما نشون بده
    مثلا اگر دو محصول به شرح زیر داشته باشیم
    کیک ۴
    نوشابه ۱۰
    و بخوایم محصول با کمترین تعداد رو بهمون نشون بده
    در جواب بنویسه: کیک

    پاسخ
  • غلامرضا ساجدی
    آبان 5, 1399 9:06 ق.ظ

    سلام من اطلاعات را از دوربین نقشه برداری به اکسل انتقال دادم اطاعات اضافی بصورت چند صفر قبل از اعداد میباشد وذ رسلول a میباشد وسلولهای دیگر نه گپی میشود ونه جابجا میشود

    پاسخ
  • سلام در بحث نامگذاری محدودهها از گزینه apply name استفاده میشود که زیرمجموعه منوی formulas هست در نوار ریبون .در خصوص نحوه عملکردش راهنمایی بفرمایید

    پاسخ

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

این فیلد را پر کنید
این فیلد را پر کنید
لطفاً یک نشانی ایمیل معتبر بنویسید.

فهرست