پث‌وایز

آموزش اکسل و گوگل شیتدرس ۳ از ۱۲

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

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

چیزی که از این درس می‌بری

  • کشیدن فرمول به پایین همه‌ی آدرس‌های نسبی رو جابه‌جا می‌کنه؛ این برای مقدارهای مشترک اشتباهه.
  • آدرس $F$1 هر جا کپی بشه ثابت می‌مونه؛ با کلید F4 علامت‌های دلار رو اضافه کن.
  • نرخ‌ها، تارگت‌ها و جمع کل رو قفل کن، ولی مقدارهای اختصاصی هر ردیف رو نسبی بذار.
فهرست این درس۵
  1. وقتی جابه‌جا شدن آدرس اشتباهه
  2. آدرس مطلق (Absolute Reference)
  3. کدوم حالت رو لازم داری؟
  4. یه مثال واقعی: سهم هر کالا از جمع کل
  5. مرور درس

۰۱ریشه‌ی مشکل

وقتی جابه‌جا شدن آدرس اشتباهه

فرض کن نرخ مالیات فقط توی یه سلول یعنی F1 اومده و تو هر ردیف باید قیمت ضربدر این نرخ بشه. تو ردیف ۲ می‌نویسی =B2*F1. وقتی فرمول رو می‌کشی پایین، ردیف ۳ می‌شه =B3*F2 و ردیف ۴ می‌شه =B4*F3. اما F2 و F3 خالی‌ان، پس جواب‌ها صفر می‌شن. قیمت باید جابه‌جا بشه؛ نرخ باید سر جاش بمونه.

آدرس مطلق (Absolute Reference)

اصطلاح · اکسل و شیت

آدرس سلولی که علامت $ داره، مثل $F$1، و هر جای جدول که فرمول رو کپی کنی دقیقاً ثابت می‌مونه. علامت $ قبل از حرف، ستون رو قفل می‌کنه و $ قبل از عدد، ردیف رو قفل نگه می‌داره.

فرمول =B2*$F$1 وقتی به پایین کشیده بشه، می‌شه =B3*$F$1 ، =B4*$F$1 …

خودت رو امتحان کنسؤال ۱ از ۴

نرخ ارز توی سلول E1 اومده. مینا تو سلول C2 فرمولی می‌نویسه تا قیمت توی B2 رو تبدیل کنه، بعد فرمول رو می‌کشه پایین. کدوم فرمول تو همه‌ی ردیف‌ها درست کار می‌کنه؟

  1. =B2*E1
  2. =$B$2*E1
  3. =B2*$E$1
  4. =$B$2*$E$1
جواب رو ببین

=B2*$E$1

درسته. قیمت هر ردیف باید عوض بشه، پس B2 نسبی می‌مونه؛ ولی نرخ ارز نباید تکون بخوره، پس E1 علامت دلار می‌گیره.

کدوم حالت رو لازم داری؟

نسبی (B2)

چیزهایی که تو هر ردیف فرق دارن: قیمت این کالا، ساعت کاری این فرد، فروش این روز.

مطلق ($F$1)

یه مقدار ثابت که بین همه‌ی ردیف‌ها مشترکه: نرخ مالیات، نرخ ارز، تارگت، جمع کل.

خودت رو امتحان کنسؤال ۲ از ۴

تو فرمولی که می‌خوای بکشی پایین، هر مورد باید قفل بشه یا نسبی بمونه؟

نسبی بمونهقفل با $

  • ساعت کاری این کارمند
  • دستمزد ساعتی که همه می‌گیرن
  • فروش این ماه
  • جمع کل سال
  • درصد پورسانت توی سلول تنظیمات
جواب رو ببین

نسبی بمونه: ساعت کاری این کارمند, فروش این ماه

قفل با $: دستمزد ساعتی که همه می‌گیرن, جمع کل سال, درصد پورسانت توی سلول تنظیمات

۰۴قدم‌به‌قدم

یه مثال واقعی: سهم هر کالا از جمع کل

  1. اول جمع کل

    فروش توی B2:B6 اومده. توی B7 بنویس =SUM(B2:B6).

  2. سهم ردیف ۲

    توی C2 بنویس =B2/$B$7. سلول B2 تکون می‌خوره، ولی جمع کل قفل می‌مونه.

  3. تبدیل به درصد

    دکمه‌ی % رو بزن تا ۰٫۲۵ به شکل ۲۵٪ نشون داده بشه.

  4. بکش پایین و چک کن

    C2 رو تا C6 بکش پایین. جمع سهم‌ها باید ۱۰۰٪ بشه. اگه یکی #DIV/0! نشون داد، علامت دلار یادت رفته.

خودت رو امتحان کنسؤال ۳ از ۴

فرمول سهم کیان از ردیف ۳ به بعد خطای #DIV/0! می‌ده. اضافه کردن علامت $ به آدرس جمع کل مشکل رو حل می‌کنه.

  1. درست
  2. نادرست
جواب رو ببین

درست

درسته. بدون $، آدرس جمع کل سر خورده روی سلول‌های خالی و تقسیم بر سلول خالی خطای #DIV/0! می‌ده. قفل کردنش باعث می‌شه همه‌ی ردیف‌ها به جمع کل واقعی وصل بمونن.

خودت رو امتحان کنسؤال ۴ از ۴

تارگت سحر توی سلول H1 قرار داره. توی C2 فرمول =B2>=H1 رو می‌نویسه تا ببینه کی به هدف رسیده، بعد فرمول رو می‌کشه پایین. از ردیف ۳ به بعد، حتی برای عددهای خیلی کوچیک هم نتیجه TRUE می‌شه. چرا؟

  1. عددهاش به عنوان متن ذخیره شدن
  2. توی مقایسه به جای >= باید از > استفاده می‌کرد
  3. آدرس H1 سر خورده روی سلول‌های خالی، و هر عددی از صفرِ سلول خالی بزرگ‌تر یا مساویه
  4. نتیجه‌ی پیش‌فرض ردیف‌های جدید همیشه TRUE می‌شه
جواب رو ببین

آدرس H1 سر خورده روی سلول‌های خالی، و هر عددی از صفرِ سلول خالی بزرگ‌تر یا مساویه

دقیقاً. ردیف ۳ با سلول H2 مقایسه می‌شه که خالیه و صفر حساب می‌شه، پس هر عددی شرط رو پاس می‌کنه. فرمول =B2>=$H$1 درستش می‌کنه.

۰۵جمع‌بندی

مرور درس

  • کشیدن فرمول به پایین همه‌ی آدرس‌های نسبی رو جابه‌جا می‌کنه؛ این برای مقدارهای مشترک اشتباهه.
  • آدرس $F$1 هر جا کپی بشه ثابت می‌مونه؛ با کلید F4 علامت‌های دلار رو اضافه کن.
  • نرخ‌ها، تارگت‌ها و جمع کل رو قفل کن، ولی مقدارهای اختصاصی هر ردیف رو نسبی بذار.
  • صفر شدن، خطای #DIV/0! یا TRUE شدن همه‌ی ردیف‌ها بعد از کشیدن فرمول به پایین، معمولاً یعنی علامت $ جا افتاده.

به سبک خودت یادش بگیر.

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

Cafe Bazaar Myket توی اپ شخصی‌سازیش کن