قواعد فرمول نویسی حرفه ای در اکسل | قسمت دوم
چرا آدرس دهی بسیار مهم است؟
همانطور که قبلا گفتم فرمول نویسی حرفه ای اصول و قوانینی داره که یکی از مهم ترین موضوعات، بحث مطلق/نسبی (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 بعلاوه یک را در ستون B سطر اول بنویسم و وقتی در ستون B به پایین درگ میکنم ستون A رو به بالا حرکت کند (یعنی مثلا از A10 به A1 حرکت کند وقتی که درگ از B1 به B10 رو به پایین حرکت می کند)؟
B A
A6+1 ۱
A5+1 ۲
A4+1 ۳
A3+1 ۴
A2+1 ۵
A1+1 ۶
با تشکر
سلام
ممنون، در مورد مثالی که ارسال کردید تو سلول A1 فرمول زیر رو بنویسید و درگ کنید:
درود بر شما
از تابع address استفاده کنید و الگو رو بسازید
نمونه د راین مقاله هست
https://excelpedia.net/address-function/
سلام
من سایت های اکسل فراوانی دیدم ولی مطالب شما و تسلط شما به مباحث اکسل فوق العادست به دلیل توضیح ندادن اصول درگ کردن در آدرس دهی مطلق و نسبی و ارتباط بین اینها من همیشه با این تعریف ساده مشکل داشتم
بسیار کمکم کردید ممنون و سپاس فراوان
سلام
یک فرمول به صورت زیر در سلول D1 نوشتم
(A1+B1-C1;”درست است”IF(A1+B1<0=
حال اگر A1+B1 بزرگتر مساوی صفر باشد باید بیاید A1+B1 منهای C1 کند در تمام موارد هیچ مشکلی ندارم فقط زمانی مشکل پیش می اید که مقدار (A1+B1) با مقدار C1 برابر باشد اگر مقدار بدون اعشار باشد باز مشکلی ندارم تفریقشون میشه صفر ولی اگر مقدار (A1+B1) دارای اعشار باشد و C1 برابر باشد با (A1+B1) اونوقت تفریقشون را صفر نمیده و مثلا ۲.۸۸۶۵۸E-15- میزنه در قسمت FORMAT CELL هم نوع اعداد را یکجور تعیین کردم
ممنون
درود بر شما
اعداد خیلی کوچک و یا اعداد خیلی بزرگ بصورت خودکار بصورت نماد علمی یا scientific نمایش داده میشه
یا فرمت رو عوض کنید و روی number تنظیم کنید
یا خروجی رو تا ۲ رقم اعشار گرد کنید با استفاده از توابع گرد کردن مثل round
سلام
در شیت دو یک تابع شرطی IF نوشتم به صورت زیر
(“”;”اشتباه است”;IF(F1>INDIRECT(“‘Sheet1’!j”&(ROW(J1)-1)*8+3);”درست است”;IF(F1<INDIRECT("'Sheet1'!j"&(ROW(J1)-1)*8+3=
و کاملا درست کار میکنه ولی زمانی که اعداد اعشار دارن اگر مقدار ها بزرگتر یا کوچکتر باشن نسبت به هم باز هم درست کار میکنه حتی اگه اعشار خیلی ریز باشه
ولی مشکل زمانی هست که اعداد برابر هستند و اعشار دارن اونوقت هست که طبق شرط باید چیزی در سلول ننویسه ولی مینویسه "اشتباه هست"
ممنون
سلام
برای اینکه قدم به قدم فرمولی که نوشتید رو بررسی کنید و ببینید مشکل کجا هست، از مطلب عیب یابی فرمول ها استفاده کنید.
سلام
مشکلم در مورد سوالم برطرف شد
مرسی
سلام
طبق سوالات قبل که پرسیدم :
در شیت یک دو ستون داشتم یکی هزینه یکی تاریخ . و میخواستم جمع هزینه هایی که دارای یک تاریخ مشخص هستند را در شیت دو داشته باشم بر همین اساس فرمول زیر را در شیت دوم نوشتم :
SUMPRODUCT(SUMIF((INDIRECT(“‘Sheet1’!E5:E10485″&(ROW(E1)-1)*7+5));B3;INDIRECT(“‘Sheet1’!G5:G10485″&(ROW(G1)-1)*7+5)))
– تاریخ ها در شیت یک در ستون E میباشد ولی در سطر ۵ – ۱۲ – ۱۹ – ۲۶ و …. که برای همین طبق راهنمایی شما به صورت روبرو نوشتم (ROW(E1)-1)*7+5)
– هزینه ها هم در شیت یک در ستون G میباشد و اون هم در سطر های ۵ – ۱۲ – ۱۹ – ۲۶ و … میباشد برای همین اون هم به صورت
(ROW(G1)-1)*7+5)
– مقدار تاریخی هم که برای من مبنا میباشد در ستون B در شیت دو میباشد
وقتی فرمول بالارا مینوسم مشکلی نیست و درست کار میکند ولی مشکل زمانی پیش می اید:
مشکل یک: فرمول را که به سطرهای دیگر بسط میدهم (چون در شیت دو برای یکسال ۳۶۵ سطر دارم و باید برای ۳۶۵ سطر بست بدهم) تا حدود ۱۳ سطر دیگر هم درست کار میکند ولی در سطر ۱۴ تا ۳۶۵ میزنه !REF#
در حالیکه هیچ مشکلی در شیت یک وجود نداره و تا سطر ۱۰۵۴۰ داده در شیت یک وجود دارد
مشکل دو: در شیت یک ۱۰۵۰۴ سطر دارم ولی در فرمول که میخواهم حدود سطر را مشخص کنم تا ۱۰۴۸۵ از من قبول میکنه و وقتی عدد بیشتر میزنم به کل فرمول ایراد میگیرد INDIRECT(“‘Sheet1’!E5:E10485″&(ROW(E1)-1)*7+5)
ممنون
با سلام
در یک سطر که شامل سلول های پر و خالی هست میخام آدرس ستون اولین سلول پر را پیدا کنم و در تابع match قرار دهم چه فرمولی را پیشنهاد می کنید؟
ممنون اگر راهنماییم کنید اساتید عزیز
سلام
با استفاده از فرمول زیر میتونید شماره اولین ستونی که در ردیف اول پر هست رو بدست بیارید:
سلام
در ابتدا عذرخواهی میکنم بابت اینکه سوال زیاد پرسیدم
و ممنون بابت اینکه راهنمایی میکنید
یک سوال دیگه از خدمتتان داشتم لطف میکنید راهنمایی بفرمایید
یک فایل اکسل داریم دو تا شیت داره در شیت شماره یک دو تا ستون A,B داره یکی ستون تاریخ و ستون بعدی وزن که ممکنه مثلا در تاریخ امروز ۹۸/۱۰/۲۹ ما ۱۰ تا وزن مختلف داشته باشیم که هرکدوم در سطرهای مختلف ستون A نوشته شده باشه
حالا میخواهم در شیت شماره ۲ در یک سلول فرمول یا شزطی بنویسم که بگه
تمام وزن هایی که تاریخشون مثلا ۹۸/۱۰/۲۹ هست در شیت شماره یک را با هم جمع کن و در اون سلول مدنظر در شیت دو قید کنه
ممنون
سلام
خواهش میکنم.
از تابع Sumif استفاده کنید.
سلام
برای اینکه تاریخ را که وارد میکنم نیاز به زدن / نباشه مراحل زیر را انجام دادم:
Number
format cells
Custom
##/##/##
و بعد در Data Validation هم در قسمت Texte lenght را مقدار شش دادم و همه چیز درست هست فقط مشکل اینجا هست که میخواهم تاریخ را وارد کنم اول باید سال بعد ماه بعد روز را بزنم و این کارو مشکل میکنه آیا امکانش هست کاری کرد اول روز بعد ماه بعد سال را بزنم .
ممنون
سلام
خیر، به فرمت مشخص شده در Format Cell به همین طریقی که الان اعمال میشه کار میکنه و امکان برعکس اعمال شدنش نیست.
ممنون از راهنماییتون
ولی مشکلی که هست اکثر سلول ها data validation براشون فعال هست برای موارد دیگر و امکان اینکه اون حالت ها را حذف کنم و بخوام حالت Text length فعال کنم نمیشه برای همین اگر امکانش باشه به نحو دیگه سلول را در حالتی گذاشت که در صورت تغییرات پیغام بده ممنون میشم .
در این صورت باید در سلول هایی که Data Validation دارند در قسمت Custom فرمول نویسی کنید که هم Text Length رو داشته باشند و هم Rule قبلی خودشون.