قواعد فرمول نویسی حرفه ای در اکسل | قسمت دوم
چرا آدرس دهی بسیار مهم است؟
همانطور که قبلا گفتم فرمول نویسی حرفه ای اصول و قوانینی داره که یکی از مهم ترین موضوعات، بحث مطلق/نسبی (Absolute/Relative) بودن آدرس محدوده هاست. این موضوع وقتی مطرح میشه که بخوایم فرمولی که نوشتیم رو Drag کنیم. اگر به این مسئله تسلط عالی نداشته باشیم، هیچ وقت نمیتونیم به فرمولی که می نویسیم و درگ می کنیم اعتماد کنیم و مجبوریم تک تک نتایج رو بررسی کنیم که این کار در مقیاس های بزرگ بسیار وقتگیر خواهد بود. برای درک بهتر این موضوع (آدرس دهی) اول باید با نحوه ارجاع به یک سلول و یا فراخوانی یک سلول در اکسل آشنا بشیم که به دو روش صورت میگیره:
- مدل A1
- مدل R1C1 یا (Row1Column1~ ردیف۱ستون۱)
هر دوی این آدرس ها به سل A1 اشاره می کنند که پیش فرض اکسل، همون حالت اول یعنی A1 است. فقط یک نکته اینکه درصورتی که بخواهیم از حالت دوم استفاده کنیم، باید تیک گزینه R1C1 reference style در شکل ۱ را بزنیم تا سرستون های اکسل از A, B, C… به ۱,۲,۳… و در نتیجه نوع آدرس دهی از A1 به R1C1 تغییرکند.
شکل۱- آدرس دهی – تغییر نوع آدرس دهی دراکسل
حالا با حل یک مثال، بحث نسبی و مطلق بودن آدرس در فرمول نویسی رو شرح میدم:
محدوده ای از اعداد داریم که میخواهیم همه رو در یک سل به خصوص ضرب کنیم. طبق شکل ۱، در سل C2 می نویسیم A2*C1= و درگ می کنیم. مسئله ای که پیش میاد این هست که همه سل ها در حین درگ کردن، با هم حرکت میکنند (مطابق شکل ۱).
شکل ۲- آدرس دهی – درگ کردن فرمول (نتیجه غلط)
در حالیکه ما میخواهیم سل C1 ثابت باشه و فقط سل های ستون A تغییر کنند. یعنی چیزی مطابق با شکل ۲٫
من این فرمول رو بصورت دستی برای هر سل تایپ کردم. اما اگر حجم داده ها زیاد بود هم امکان این کار وجود داشت؟ پس باید راهی وجود داشته باشه تا بتونیم تصمیم بگیریم در حین درگ کردن، کدام سل ها تغییر کنند و کدام ها تغییر نکنند.
شکل۳- آدرس دهی – فرمول مد نظر بعد از درگ کردن
درگ کردن در اکسل به دو صورت هست. در لحظه یا در ستون حرکت میکنیم (به سمت بالا و پایین) و یا در سطر(به سمت چپ و راست).
وقتی در ستون حرکت میکنیم (بالا یا پایین) فقط ردیف سل حرکت کننده تغییر می کند و وقتی در ردیف حرکت میکنیم (چپ یا راست) فقط ستون سل حرکت کننده تغییر میکند.
پس برای مطلق/نسبی کردن آدرس سل ها در فرمول ها:
- اول باید ببینیم در کدام مسیر داریم حرکت میکنیم (سطر یا ستون) و چه چیزی در حال تغییر است (شماره ردیف یا نام ستون)؟
- بعد تصمیم بگیریم که آیا میخواهیم تغییر کند یا ثابت بماند؟
وقتی میخواهیم سطر یا ستون رو فیکس کنیم، باید یک علامت $ پشت شماره ردیف یا نام ستون بذاریم. با این تفاسیر، چهار حالت برای آدرس دهی داریم:
| سطر آزاد-ستون آزاد | A1 | با درگ کردن در ستون، شماره ردیف تغییر میکند |
| سطر مطلق-ستون مطلق | $A$1 | با درگ کردن در ستون، شماره ردیف تغییر نمیکند |
| سطر آزاد-ستون مطلق | $A1 | با درگ کردن در ستون، شماره ردیف تغییر میکند |
| سطر مطلق-ستون آزاد | A$1 | با درگ کردن در ستون، شماره ردیف تغییر نمیکند |
حالا برگردیم به همان سوال اول. میخواهیم $ را برای فرمول A2*C1= تنظیم کنیم که با درگ کردن، بدرستی عمل کند. چون در ستون داریم حرکت میکنیم، پس فقط شماره ردیف تغییرمیکنه. حالاما باید تصمیم بگیریم کدوم شماره ردیف تغییر کنده و کدوم ثابت بمونه. چون میخواهیم سل C1 ثابت بمونه و در همه سل ها تکرار بشه (شکل۳)، پس مطابق شکل ۴، $ را پشت ۱ در C1 میگذاریم. اما میخواهیم A2 درسل های بعدی به A3 و A4 و… تغییر کند.پس $ نیازی ندارد.
شکل ۴- آدرس دهی – درگ کردن فرمول (نتیجه درست)
علامت $ را هم میتونیم مستقیما تایپ کنیم. هم اینکه از کلید F4 استفاد کنیم. وقتی روی آدرس مورد نظر قرار بگیریم، با هر بار F4 زدن، یکی از ۴ حالت آدرس دهی ظاهر میشه.
مثال دوم:
میخواهیم یک جدول ضرب ایجاد کنیم. فرمول خیلی ساده هست، A2*B1=. حالا باید طوری آدرس دهی کنیم که با انتقال آن به کل جدول، محاسبات به درستی انجام شود. به شکل ۵ دقت کنید. علامت $ پشت نام A و ردیف ۱ قرار گرفته. چرا؟
شکل ۵- آدرس دهی در جدول ضرب
A2*B1= رو در نظر بگیرید. وقتی در ستون حرکت میکنیم، همواره میخواهیم اعداد موجود در ردیف ۱ در بقیه اعداد که در ستون A هستن، ضرب بشن. پس ردیف ۱ را فیکس میکنیم. وقتی هم که در ردیف حرکت میکنیم، میخواهیم عدد موجود در ستون A در بقیه اعداد ردیف ۱ ضرب بشن. پس $ ها رو به این صورت اعمال میکنیم A2*B$۱ا$=. بعبارت کلی، هر جای این جدول ضرب هستیم، میخواهیم عددی در ردیف۱ ضرب در عددی در ستون A بشه. پس ردیف ۱ و ستون A در فرمول باید فیکس بشه.
مبحث آدرس دهی بسیار بسیار مهمه. کسی که میخواد فرمول نویس حرفه ای بشه، حتما باید به این موضوع تسلط کافی داشته باشه. پس علاوه بر تمرین و تکرار دو مثال تشریح شده، حتما مثال های مختلفی رو امتحان کنید تا کاملا ملکه ذهنتون بشه. هر موقع بحث درگ کردن و انتقال فرمول پیش میاد، اول از همه برید سراغ $ و آدرس دهی رو تنظیم کنید بعد شروع کنید به انتقال فرمول.
مشاهده ویدئو آدرس دهی در اکسل
در این ویدئو نحوه استفاده صحیح از $ جهت ایجاد فرمول های درست آموزش داده شده:
[jwp-video n=”1″]
سلام من سوال دارم!
میخوام توی یک ردیف یا همون سطر که به فرض ۲۰ تا ستون داره سطری های که یکی از سلول هاش برابر یک مقدار مشخصی باشه کل ردیف ( ۲۰ستون ) به رنگ مثلا قرمز نمایان بشه ؟
منو به چالش کشونده اگه میتونید راهنمایی بفرمائید.
درود بر شما
باید با $ این مسئله رو حل کنید. مثلا اگه محدوده از A:F هست و شرط در ستون b قرار داره
میتونید اینو بنویسید:
=$B1=20
و کل محدوده رو برای شرط دادن انتخاب کنید
سلام ممنون از توجه شما عذر خواهی میکنم سوالم رو واضح تر خدمتتون عرض میکنم مثلاً توی یک شیت ۱۰۰ ردیف و ۱۰۰ ستون داریم که اطلاعاتی مثل روز . تاریخ . ساعت و … و آخرین ستون نوشته تعطیل حالا من میخوام توی این ۱۰۰ ردیف ردیف هایی که ستون آخرش تعطیل نوشته شده اکسل برام رون ردیف رو مثلا زرد کنه که توی این ۱۰۰ ردیف راحت مشخص باشه میتونم ستون آخر رو هرجا تعطیل نوشته به یک رنگ دربیارم ولی میخوام ردیفش کلا زرد بشه آیا امکانش هست. سپاس از حسن توجه شما
درود
با همون روش که عرض کردم انجام میشه
با $ میتونید این قضیه رو کنترل کنید
سلام
من یه مشکلی تو اکسل دارم
وقتی یک تابع مثلا switch رو داخل یک سلول تایپ میکنم و بعدش آرگومان های داخلش زو که شامل سلول و نتیجه و … است رو پر میکنم بعداز زدن کلید اینتر اکثر جیزهایی ک بعداز تابع نوشتم جابه جا میشن و اعداد هم فارسی میشن و تابع کار نمیکنه .
علت این جا به جا شدن و بهم ریختگی بعد از زدن اینتر چیست ؟این مشکل رو برای تمام توابع دارم.ممنون میشم راهنمایی کنید
به نظر میرسه جدا کننده آرگومان ها یک کاراکتر فارسی هست مثل ویرگول ، یا نقطه ویرگول ؛پچک کنید که حتما کاما , یا سمیکالن ; باشه
به نظر میرسه جدا کننده آرگومان ها یک کاراکتر فارسی هست مثل ویرگول ، یا نقطه ویرگول ؛چک کنید که حتما کاما , یا سمیکالن ; باشه
سلام.خدا قوت.
تعدادی شهرستان دارم که هرکدوم در یکی از گروههای AتاD هستند.چه شکلی باید برای هر شهرستان گروهش را تعریف کنم که تا اسم شهرستانها توی شیت های مختلف این فایل اکسل میاد، در ستون جلوییش گروه مربوطه جایگذاری بشه.مثلا برای شهر ساری کد A ،برای بابل و آمل کدB.
با تشکر از شما
درود بر شما
اگر منظو راینه که با ورود نام شهر، کد مربوطه جلوی سلولش نمایش داده بشه، یک راه استفاده VLOOKUP هست
یک جدول استاندارد تشکیل بدید و از روی اون فرمول نویس یکنید
سپاسگزارم از لطفتون
ممکنه راهنماییم کنید که اگر بخوام داده های چند سلول در یک ردیف که ترکیبی از اعداد و حروف هستند در نهایت بصورت یکجا و پشت سر هم در یک سلول دیگر نمایش داده بشه از چه تابعی باید استفاده کنم( هر کدام از سلول ها حاوی بخشی از اجزای یک شماره سریال ۱۲ رقمی است که در نهایت باید در سلول هدف بصورت یک کد پشت هم نمایش داده بشه)
بازم ممنون از وقتی که میذارید…
درود بر شما
لینک زیر رو مطالعه کنید
https://excelpedia.net/text-binding/
سلام، خداقوت
من میخوام در تابع if تو قسمت Logical-test بجای مقدار عددی یه عبارت متنی رو چک کنه، آیا امکانش وجود داره؟
ممنون از راهنماییتون
سلام، ممنون
بله کافیه به صورت “متن” = A1 در قسمت Logical نوشته بشه.
سلام-آدرس ایمیلتون رو جهت ارتباط میدید لطفامن یه فایل اکسیل دارک که نیاز به کمک دارم
درود بر شما
درخواست پروژه رو به ایمیل Info@excelpedia.net ارسال بفرمایید
با سلام خدمت شما
من میخوام با رفتن روی یک سلول، یک سلول دیگه که خودمون تعیین کنیم نشون داده بشه آیا فرمول خاصی هست که استفاده کنیم؟
به عنوان مثال با رفتن روی روز شنبه، سلول دیگری که برنامه کاری روز شنبه نوشته شده نشون داده بشه
درود بر شما
اگر منظورتون اینه که فقط با حرکت موس ی اتافقی بیفته. کد نویسی نیاز دارید. اما اگر منظور انتخاب یک داده و فراخوانی داده های مرتبط هست، که فرمول نویسی های جستجو، نتیجه میده مثل Vlookup, index و …
سلام وقت بخیر
دو ستون دارم در یک شیت که در هر ستون نوشته های متنی وارد شده و در شیت دیگر می خواهم این دو ستون به صورت اتوماتیک در یک ستون و پشت سر هم وارد شود ، راهنمایی میفرمایید لطفا
سلام، ممنون
برای حل این مسئله از ترکیب تابع Row و تابع Offset میتونید استفاده کنید.
درود بر شما
میخواستم بدونم این فرمول ایرادی داره که جواب درستی به من نمیده؟
=IF(FIND("kf", A1),"عطری",(IF(FIND("kp",A1),"ساده"), (IF(FIND("kd",A1),"سه بعدی","0"))))برای عطری جواب میده یعنی وقتی kf رو تو عبارت پیدا میکنه درسته ولی برای بقیه موارد جواب نمیده..
در توضیح هم عرض کنم من میخوام وقتی در یک متن ترکیب عدد و رقم اگر kp رو پیدا کرد بزنه ساده
kd بشه سه بعدی
kf بشه عطری
با تکشر از راهنمایی شما
درود بر شما
برای if های چندگانه اگر ورژن ۲۰۱۹ دارید، از تابع IFs استفاده کنید
اگر نه هم از ساختار زیر برای نوشتن IF ها متداخل باید استفاده کنید
=IF(isnumber(FIND("kf", A1)),"عطری",IF(isnumber(FIND("kp",A1)),"ساده", IF(isnumber(FIND("kd",A1)),"سه بعدی","")))https://excelpedia.net/nested-if-functions/
ممنونم از پاسخگوییتون..در کانال تلگرامتون عضو شدم..ایشالا مابقی سوالات اونجا:)
سلام خدمت شما استاد گرامی
تشکر بابت آموزش هایی که قرار دادید. واقعا کار بنده رو راه انداخت
یک سوال داشتم
یک TABLE دارم . میخواهم مقادیر برخی سلول ها در یک ستون مشخص را که مقادیر متناظر آنها ( در همان سطر خودشان) در ستون مشخصی با هم برابر است را انتخاب کنم و با هم جمع کنم
چه کار باید بکنم ؟
از چه فرمولی استفاده کنم ؟
درود بر شما
خیلی واضح نیست
ولی به نظر میرسه که با sumif بتونید انجام بدید
https://excelpedia.net/sumifs-function/
سلام. یه سوال داشتم.
اگر بخوام اکسل نشانه — (EM Dash) را بجای ! (علامت تعجب) جاگذاری نماید باید چیکار کنم؟ وقتی از دستور Replace استفاده میکنم ارور میده
درود بر شما
از چی استفاده کردید؟
ابزار FInd؟ تابع؟ خطا چی هست؟
بصورت کلی این کاراکتری که گذاشتید در اکسل کد ۱۵۱ هست
و مثلا میتونید از این تابع استفاده کنید