قواعد فرمول نویسی حرفه ای در اکسل | قسمت دوم
چرا آدرس دهی بسیار مهم است؟
همانطور که قبلا گفتم فرمول نویسی حرفه ای اصول و قوانینی داره که یکی از مهم ترین موضوعات، بحث مطلق/نسبی (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″]
با سلام و خسته نباشید
خانوم مهندس آیا امکان آدرس دهی (لینک) text box به سلول ها هم وجود دارد؟
(مقادیر داخل text box در سلول مورد نظر نمایش داده شود؟)
درود بر شما
منظورتون همچین چیزیه؟
https://excelpedia.net/shape-to-cell-connection/
سلام در ستون c10 تا c20 اعددی نوشته شده است می خواهیم اطلاعاتی را از ستون های f تا h ولی در ردیف های نوشته شده در اعداد c10 که مثلا ۱۱۸ است را بخوانیم یعنی اطلاعات f118 تا h 118 را نیاز داریم با کدام تابع عدد ۱۱۸ را که خودش مقدار c10 است را بخوانیم و بتوانیم در فرمولی عدد f118 را خوانده وهر بار که مقدار عددی سلول c10 تغییر می کند فرمول ما صحیح عمل کند ممنون
درود بر شما
سوال نامفهومه
سلام. وقت بخیر
اگر ستونی با تعداد ۱۰۰۰ سلول داشته باشیم و بخواهیم ۸ تا ۸تا جدا کنیم. باید چیکار کنیم؟
درود بر شما
جدا کنیم که چکار کنیم؟ انتخاب کنیم؟ محدوده فرمول باشه؟ سورت کنیم؟ و ….
اگر منظورتون استفاده از محدوده بصورت هشت تایی در فرمول هست، یک راه استفاده از تابع offset هست، یکی هم address
نمونه رو میتونید در لینک زیر مطالعه کنید:
https://excelpedia.net/address-function/
با سلام و وقت بخیر
یک سوال داشتم از شما خانم مهندس
من دو تا ستون دارم که اعداد نرخ دلار دارن و میخواهم به یورو تبدیل شود اینم میدونم که باید اعداد را در ۰.۴۰۷ ضرب شود و این هم میدانم که عدد ۰.۴۰۷ با هر دو طرف $ داشته باشد که ثابت باشد سوالم این است که میخواهم بدونم فرمولی هست که مثلا من در سطر اول ستون اول عددم تغییر کرد همزمان سطر اول ستون دوم هم تغییر کند
درود بر شما
متوجه خواستتون نشدم.
اما اگر فرمول هاتون به یک متغیر وصل باشه.با تغییر اون متغیر، قاعدتا نتایج عوض خواهند شد
سلام. شما نیاز به علامت $ ندارید. کافیه سه عدد ستون دوم رو به صورت ضرب عدد ۰.۴۰۷ در ستون اول بنویسید و بعد درگ کنید یعنی علامت + رو به سمت پایین بکشید.
اما اگه می خواهید با تغییر نرخ تبدیل، ستون دوم تغییر کند. بهتر است بالای هر ستون یک سلول را به نرخ تبدیل اختصاص بدهید. مثلا ستون c1 نرخ تبدیل ۰.۴۰۷ باشد. بعد ردیفهای بعدی ستون را می توانید به صورت b1*c$1 نویسید و درگ کنید. حالا با تغییر نرخ تبدیل، تمام مقادیر تغییر می کند.
سلام و روز بخیر
من این فرمول رو نوشتم و جواب نگرفتم و خانم خاکزاد راهنمایی کردن و آرس مطالب If رو فرستادن ولی بازهم نتیجه نگرفتم .
میشه راهنمایی کنید برای تکمیل این فرمول چکار کنم و یا فرمول مناسب برای گرفتن جواب چه فرمولیه .
(((IF(B9=2,Master!AK7,Master!AH7,IF(B9=3,Master!AQ7,Master!AN7,IF(B9=4,Master!AW7,Master!AT7
درود بر شما
به نظر میرسه اصلا مزالعه نکردید اون آموزش رو. چون اولین موضوع، که رعایت تعداد آرگومان هست رو انجام ندادید. الان برای هر IF چهار جز یا آرگومان گذاشتید که غلطه. هر IF 3 آرگومان بیشتر نداره. با توجه به منطق سوالتون، باید شرط های منطقی IF رو بچینید. و تا زمانی که شرح سوال و توضیح ندید نمیشه این فرمول رو تغییر داد. بصورت کلی یا بجای آرگومان دوم یا بجای آرگومان سوم باید یک IF دیگه بذارید.
تشکر می کنم
اینو نوشتم
ولی برای آخرین فرمول اگر b214=4 بشه فرمول من جواب نمیده
درواقع میخوام اینجور بنویسم اگر b=2 بشه بره از یک شیت دیگه به دوتا از سلو لهام نگاه کنه هر کدوم که رقم داشت بیاره اینجا برام بزاره
و اگر b=3 بود باز به همین شکل از شیت دیگه دو تا سلول رو بخونه و هر کدومش که رقم داشت برام بیاره .
در واقع همیشه فقط یکی از این دو تا شیتهام رقم داره و یکی دیگش خالیه
این علامت های & کار جمع رو انجام نمیدن. فقط سلول ها رو به هم میچسبونن و خروجی متنی میدن.
شما اگه میخواید دو سلول رو جمع بزنید، نیازی به & ندارید. کافیه داخل تابع sum همون دو سلول رو وارد کنید به عنوان دو آرگومان جداگانه.
به نظر میرسه با اصلاح تابع sum بصورتی که توضیح دادم جواب درست بگیرید
تشکر می کنم از راهنماییتون
سلام .
برای نوشتن این فرمول چکار می تونم بکنم تا جواب درست بگیرم
لطفا راهنماییم کنید
درود بر شما
ساختار If رو رعایت نکردید. هر if سه آرگومان بیشتر نداره
شما برای هر if چهارتا گذاشتید.
در واقع به جای آرگومان سوم یا دوم باید یک IF دیگه بذارید. آموزش زیر رو ببینید تا کاملا درک کنید:
https://excelpedia.net/nested-if-functions/
سلام . تشکر می کنم از پاسخگویی و راهنمایی خوبتون . بنظر شما بجای این فرمول از چهفرمول دیگه ای می تونم استفاده کنم .
لطفا راهنمایی بفرمایید
سلام
من اکسل مقدماتی رو تا حدود زیادی بلدم میخواستم بدونم سایتی یا مطلبی جایی سراغ دارید که اکسل پیشرفته رو بخصوص فرمول نویسی ها رو بصورت پشت سرهم آموزش گذاشته باشه
ممنون
سلام، میتونید از دوره پیشرفته اکسل نینجا که مباحث کاربردی و پیشرفته مخصوصا در حوزه فرمول نویسی رو آموزش میده استفاده کنید.
باسلام
عرض خسته نباشید
اگر بخواهیم یک تابع ثابت با شماره سل های ثابت بعد از درگ کردن فقط شماره شیت در ادرس دهی جلو بره باید چیکار کنیم .
لازم به ذکر که کل شیت ها جداول یکسان و ثابتی رو دارن فقط دیتا عوض میشه.
ممنون
درود بر شما
از تابع address استفاده کنید
لینک زیر رو بخونید:
https://excelpedia.net/address-function/
با سلام
من در یک شیت دو ستون اطلاعات دارم یکی نوشتاری و ستون بعد عدد هست تو شیت دیگه هم همین دو ستون رو میخوام فراخوان بدم بطوریکه ستون عددی براساس بزرگ به کوچک سورت بشن تا اینجا اوکی شده میخوام بدونم فرمولی داریم که بعد از سورت کردن ستون اعداد ستون نوستار رو هم بر اساس سورت اعداد سورت کنه
درود بر شما
فرمول مشخصی وجود نداره
باید با ترکیب توابع جستجو و منطق داده هاتون این کار و انجام بدید
سلام . میخواستم بدونم که چطور میتونم سلولی رو که میخوام توش مبلغ رو دستی وارد کنم ، براش فرمولی شرطی بگذارم که در صورتی که مبلغی وارد شده دارای شرط مورد نظر نبود ، ارور بده و ثبت نشه . و هم ارور بده ولی ثبت بشه . هر دو حالت مد نظرم هست . ممنون میشم راهنمایی کنید .
درود بر شما
میتونید از ابزار data validation استفاده کنید و داخلش فرمول مورد نظر رو بنویسید تا ورود داده رو کنترل کنه