قواعد فرمول نویسی حرفه ای در اکسل | قسمت دوم
چرا آدرس دهی بسیار مهم است؟
همانطور که قبلا گفتم فرمول نویسی حرفه ای اصول و قوانینی داره که یکی از مهم ترین موضوعات، بحث مطلق/نسبی (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″]
سلام و درود بر شما
یه سوال دارم
من یه اکسل با حدود ۴۰ هزار ردیف دارم ،میخوام برای این اکسل صد تا صدتا بصورت پشت هم ،فرمول بزنم.مثلا از ردیف ۱ تا صد یک جواب داشته باشم،برای ۱۰۱ تا ۲۰۰ جواب دوم تا انتها..که میشه ۴۰۰ تا جواب..ولی مشکلی که دارم اینه که با درگ کردن نمیتونم بازه صدتایی رو به اکسل بفهمونم وباید دستی وارد کنم که خیلی زمان بره.راهی پیدا نکردم برای این موضوع.در ضمن برای هر صد ردیف یک نام قرار دادم..مثلا صد تای اول رو گذاشتم ملت ،صدتای دوم رو میرداماد الی آخر..راهی هست برای انجام اینکار؟
باتشکر
درود بر شما
راه های مختلفی وجود داره
یک راه میتونه این باشه که سر ردیف هر بخش رو بنویسید یک بار، باد با Go to + blank داده ها رو کپی کنید
یک راه هم اینه جدولی تهیه کنید که مثلا ملت باربر باشه با ۱۰۲و میرداماد برابر باشه با ۲۵۰ یا هر مقدار دیگه که مد نظرتونه و بعد vlookup کنید
سلام مجدد
راستش من متوجه نشدم…مخصوصا راه اول رو
Go to + blank
بهتره سوالتون رو در گروه تلگرامی مطرح کنید تا جزئیات بیشتری ارائه بشه.
درود بر شما
من میخوام مثلا سلول A1 را در یک سلول دیگه تو یه شیت دیگه کپی کنم.البته قسمت عددی را از یک سلول دیگه بگیرم.یعنی A که عدد ۱ آن را از مثلا سلول B5 سلول جاری بگیرم.یعنی A(shit1!B5)
درود بر شما
آدرس رو بسازید و از تابع Indirect کمک بگیرید
https://excelpedia.net/address-function/
=INDIRECT("A"&B5)سلام خدمت سرکار خانم استاد خاکزاد، من به یک مشکلی برخوردم.
مثلا در معکوس گرفتن و یا ضرب دو ماتریس، فقط جواب سلول اول نمایش داده می شود. دو روز هم در اکسل کار کردم ولی نتیجه نگرفتم، لطفا راهنمایی بفرمائید.
در چنین حالاتی که خروجی فرمول بیش از یک مقدار هست باید بیشتر از یک سلول رو انتخاب کنید، فرمول رو بنویسید و به جای Enter بسته به فرمول Ctrl + Enter و یا Ctrl + Shift + Enter بزنید.
مثلا اگر دارید ماتریس ۲*۳ را در ماتریس ۳*۲ ضرب میکنید باید قبل از وارد کردن فرمول یک محدوده ۲*۲ رو انتخاب کرده باشید.
ضمن سپاس و تشکر فراوان، من اول محدوده را انتخاب می کنم. یه سوال، دقیقا منظور از انتخاب محدوده چیست؟ من مثلا در ضرب یک ماتریس ۳*۳ در یک ماتریس ۳*۳، یک محدوده ۳*۳ را انتخاب میکنم ( یعنی درواقع در یک سلول کلیک کرده و با موس آن را تا پایان محدوده ۳*۳ میکشم). بعد فرمول را زده و ctrl+shift+enter را با هم می زنم. ولی متاسفانه همیشه فقط جواب در سلول اول نمایش داده می شود.
خواهش میکنم. برای نمونه آموزش کار با تابع Frequency رو بخونید.
ببخشید، خوندم ولی خیلی متوجه نشدم، دقیقا مشکل کار من کجاست که فقط سلول اول را نمایش می دهد.
در بعضی موارد هست که شما حاصل ضرب دو ماتریس رو در تابعی مثل SUM قرار میدید که نتیجتا تمام اعداد حاصل از ضرب دو ماتریس رو با هم جمع میکنه و به یک عدد تبدیل میشه.
در آموزشی که مشاهده کردید فایل گیف انتهای آموزش دقیقا مورد مشابه شما هست که با استفاده از تابع Frequency، چندین عدد رو به عنوان خروجی میده که در یک محدوده که از قبل انتخاب کرده جا داده.
اگر محدوده انتخابی بزرگتر از خروجی حاصل از فرمول باشه در سلول های خالی خطا ایجاد میشه.
اگر هنوز با این توضیحات به نتیجه نرسیدید در گروه تلگرامی فایل رو ارسال کنید تا دقیقتر بررسی بشه.
داخل سلول A1 تا A30 داده وجود داره وداخل سلول K1 یک متغییر هست من میخوام در سلول C1 فرمولی بنویسم که مقدار متغییر K1 که فقط بین ۱ تا ۳۰ به عنوان شماره سطر ستون A در نظر بگیره و مقدار همون سطر رو داخل K1 قرار بده
درود بر شما
تابع Address و indirect رو بخونید
https://excelpedia.net/address-function/
سلام
من یک سلول دارم که می خوام مقدارش را از روی یک ستون که دارای ۳۰ تا ردیف هست بخونه با این شرط که شماره ی ردیف با مقدار یک سلول مستقل که مقدارش از ۱ تا ۳۰ متقییر هست برابر باشه
ممنون میشم برای نوشتن فرمولش راهنمایی کنید
درود بر شما
سوالتون واضح نیست
مثال بزنید
سلام.من میخوام اطلاعات یه نفر روبا زبان برنامه نویسی سی شارپ از بانک اطلاعاتی در فرم قراردادی که تو اکسل تهیه کردم پر کنم و ذخیره کنم.همه کارها رو انجام دادم فقط عکس رو نتونستم در اون فایل اکسل بیارم.
درود بر شما
فکر میکنم سوال مربوط به سی شارپ باشه!
از متخصین این حوزه باید بپرسید
سلام و خداقوت
ممنون از این آموزشهای مفید و کاربردی که با مطالعش یکی از مشکلات مخفی تو جداولم رو تونستم حل کنم…سپاس از حوصله و وقتی که میذارید…
سلام خواستم بدانم چه فرمانی را به این فرمول که در یک سلول نوشته شده ، تغییر یا اضافه کنم که حاصل جواب را در سلول کناری خود به نمایش بگذارد.
با تشکر فراوان از شما
درود بر شما
داخل هر سلولی که بخواید نتیجه رو ببینید، باید داخل همون فرمول رو بنویسید
با توابع نمیتونید جواب رو منتقل کنید
مساوی هم میتونید بذارید در سلول کناری و مساوی مقدار سلول فعلی قرار بدید.
اگر خیلی ضروری باشه باید کد نویسی کنید
با سلام من یه جدول در آمد روزانه طراحی کردم میخوام درآمد هر روزم از ساعت ۲۴ در یک سلول دیگر ثبت شه و همچین آخر هر ماه در سلول دیگرولی نمی دونم با کدوم فرمول
درود بر شما
سوال خیلی مبهم هست
میشه لطفاً یک نمونه تمپلیت آماده جهت اهنمایی بیشتر برای من بفرستید؟
با سلام:
یک فایل اکسل دارم که با وارد کردن یک سری مقادیر یک سری محاسبات انجام میده و نتایجش را به ما نشون میده ، میخوام اگه در یک سلول خاص اطلاعاتی وارد نشه دسترسی به کل شیت امکان پذیر نباشه، میشه لطفاً راهنمایی کنین.
درود بر شما
باید کد نویسی انجام بدید
VBA و سطوح دسترسی تعریف کنید