قواعد فرمول نویسی حرفه ای در اکسل | قسمت دوم
چرا آدرس دهی بسیار مهم است؟
همانطور که قبلا گفتم فرمول نویسی حرفه ای اصول و قوانینی داره که یکی از مهم ترین موضوعات، بحث مطلق/نسبی (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″]
با سلام و تشکر ازمطالب خوب و آموزنده تون.
من می خوام یک سلول که حاوی اعدد با ۴ رقم اعشار هست. به اعداد با ۲ رقم اعشارتبدیل بشه . از تابع trunc , برای ایجاداعداد بااعشار ۲ رقمی در سلول های مجاور می تونم استفاده کنم.سوال اینکه میشه روی همون سلول حاوی عدد ۴رقمی فرمول اعمال بشه . از راهنماییتون سپاسگزارم.
درود بر شما
نکته اول اینکه دقت داشته باشید تابع Trunc گرد نمیکنه و فقط عدد رو قطع میکنه. برای گرد کردن از تواع مخصوص اینکار استفاده کنید که در لینک زیر آمده:
https://excelpedia.net/number-rounding/
نکته بعدی اینکه خیر، نمیشه در همون سلول اینکار و کرد، مگر اینکه از سلول استفاده نکنید و فرمول رو مستقیم قبل از عدد تایپ کنید. که قابل درگ هم نیست
سلام خانم خاکزاد تشکر میکنم از سایتتون و پیگیری جهت باز گردن گره های اکسلی ملت
درود بر شما
خواهش میکنم. پاینده باشید
سلام ؛
لطفا کمکم کنید
می خواهم در یک سلول اکسل عددی که وارد می کنم در دو ضر ب بشه (دو برابر بشه) از هر راهی رفتم نشد
اگه راهی هست ممنون می شم راهنماییم کنید.
سلام
برای اینکار حتما از کد وی بی باید استفاده کنید. کد زیر رو در ماژول مربوط به ورک بوکی که میخواید وارد کنید:
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Intersect(Target, Range("A1:a10")).Cells.Count > 0 Then Application.EnableEvents = False Target.Value = Target.Value * 2 Application.EnableEvents = True End If End Subسلام خانم خاکزاد محترم
چنانچه بخوام ستون جمع یکسری مقادی رو فقط داشته باشم و ستونهای قبل که مقادیری هست که می خوام حذف بشن و لی مقادی جمع رو free می زنه باید چیکار کنم.
اگه یک سلول رو ادرس دهی مطلق کنم نمیشه بقیه سلول هاش رو درگ کنم تا انتها یا باید دونه دونه ادرس دهی کنم
سپاس فراوان
درود بر شما
سوالتون واضح نیست که چی رو دقیقا میخواید جمع بزنید.
اما بحث آدرس دهی دقیقا برای همین هست که تک تک این کار و نکنید. یکبار امجام بدید و درگ کنید.
سلام. یک سوال در مورد ارجاع سلول ها دارم. در B1 عدد ۱۲ را نوشته ایم. می خواهیم از ستون A ارجاع به شماره سلولی بدهیم که شماره آن در B1 نوشته شده. یعنی مثلاً (A(B1 که در نهایت مقدار A12 را به ما بدهد. چطوری امکان پذیر هست؟
درود بر شما
با استفاده از تابع indirect میتونید این کار و بکنید:
=indirect("A"&B1)پیشنهاد میکنم این لینک رو هم مطالعه کنید:
https://excelpedia.net/address-function/
ادامه پیام قبلی
در این حالت فقط جمع وزن بچه های حیوان شماره ۲۷۶ رو در سال ۷۳ میده، در حالیکه ما وزن همه حیوانات در این سال و همه حیوانات در سال های دیگه رو میخوایم
ممنون
درود بر شما
با توجه به حجم داده ها، بهتره از pivottable استفاده کنید
چون Sumif خیلی حجم فایل رو میبره بالا.
اما اگر باز هم میخواید از این فرمول استفاده کنید
باید یک ماتریس یونیک بسازید، که ستونش، اسم حیوان باشه و سطرش سال مورد نظر. بعد فرمول SUmif رو روی داده ها استفاده کنید.
حواستون به $ ها هم باشه
موفق باشید
ممکنه یک مثال کوچیک بزنید چون کمی گنگه برام
ممنون
ی همچین ماتریسی باید داشته باشید که میشه شرط هاتون. بعد فرمول SUMIF رو در این جدول بنویسید و درگ کنید.
———سال ۱۳۸۹ سال ۱۳۹۰ سال ۱۳۹۱ سال ۱۳۹۲ سال ۱۳۹۳
حیوان ۱
حیوان ۲
حیوان ۳
حیوان ۴
حیوان ۵
حیوان ۶
حیوان ۷
حیوان ۸
اگر با تابع SUMIF آشنا نیستید این لینک و بخونید:
https://excelpedia.net/sumifs-function/
اگر با $ آشنا نیستید این لینک رو:
http://excelpedia.net/cell-address/
ضمن اینکه این گزارش رو با Pivottable میتونید بدست بیارید.
سلام خانم خاکزاد
من یک فایل اکسل دارم که در اون یک ستون شماره حیوان هست یک ستون شماره سال زایش حیوان یک ستون وزن بچه های حیوان در زمان تولد
حالا ممکنه این حیوان در سال دوبار زایمان کرده باشه یا یکبار
چیزی که میخوام اینه که مجموع وزن بچه های حیوان در یک سال خاص رو بهم بده
از دستور sumifs هم استفاده کردم اما فقط برای یک حیوان خاص رو تونستم به دست بیارم درحالیکه میخوام برای همه شش هزار حیوان رو بهم بده
دستوری که استفاده کردم بصورت زیر بود
Sumifs(m1:m6000;c1:c6000;”73″;b1:b6000;”276″)
سلام
ببینید من در اصل دارم یک دیتا بیس از قیمتهای ۱۰نوع سهام درست میکنم، در واقع بنده یازده تا شیت دارم، برای هر سهم یکی و یکی کل اطلاعات ده سهم را یکجا از وب سایت بورس میگیره. من میخوام دستوری داشته باشم که هر روز وقتی فایلم را باز میکنم، اطلاعات هر سهم از شیت جمعی کپی و در شیت مربوطه در سطر و ستون خاصی ذخیره گردد.
خوشحال میشم دوستان کمک کنن
باتشکر
سلام
برای اینکه اطلاعات هر سهم رو در شیت مربوطه و از دیتابیس کلی که از اینترنت میگیرید داشته باشید از تابع Vlookup استفاده کنید.
سلام من هم یک فایل برای سهم های بورسی نوشتم که ورود و خروج نقدینگی را در یک سهم نمایش می دهد اما نمیدونم چه جوری قیمت ها را از سایت Tse بخونم. ممنون میشم بهم ایمیل بزنید تا فایل اکسل را برایت ارسال کنم تا راهنمایی فرمایید
درود بر شما
از قسمت from web سایت رو معرفی کنید
یا از قسمت get and transform گزینه web رو بزنید و از پاور کوئری استفاده کنید
سلام
در یک سلول اکسل به آدرس مثلا H9 نوشتم ۲۰ و در یک سلول دیگر به آدرس M23 نوشتم ۵ …. در یک سلول دیگر فرمول دادم H9+M23= حالا به جای اینکه در سلول موقع نمایش فرمول بنویسه H9+M23= می خوام اینو نمایش بده بهم ۵+۲۳=
لطفا راهنمایی کنید
سلام
میتونید در فرمولی که نوشتید جهت نمایش نتیجه هر بخش از فرمول، قسمتی که میخواید رو انتخاب کنید و دکمه F9 رو بزنید و در آخر Enter بزنید. (در این حالت دیگه به جای فرمول نتیجه اون فرمول جایگزین میشه)
در فرمول شما اول قسمت H9 رو انتخاب کنید و بعد گزینه F9 رو بزنید، بعد قسمت M23 رو انتخاب کنید و F9 رو بزنید.
سلام.خیلی ممنون که وقت گذاشتید و به پیام من پاسخ دادید…… کلیدF9رو که میزنم هیچ اتفاقی رخ نمیده
خواهش میکنم. در بعضی کیبوردها باید علاوه بر F9 دکمه Fn هم گرفته بشه.
ممنون مشکل F9 حل شد با راهنمایی شما….حالا یه مشکل دیگه ای که دارم اینه که میخوام سلول ها هم به هم لینک بشن اگه M23عددش ۵ است بعد از زدن F9 این عدد رو به من نشون میده ولی من میخوام اگه عدد سلول M23تغییر کرد اونجا هم تغییر کند….آیا راه حلی برای این مشکل وجود دارد؟
خواهش میکنم. اگه بعد از زدن دکمه F9 دکمه اینتر رو بزنید دیگه خروجی سلول ثابت میشه. برای همین بهتره بعد از اینکه F9 رو زدید و خروجی ها رو بررسی کردید، دکمه Esc رو بزنید که دوباره به حالت اول برگرده.
در غیر اینصورت راه دیگه ای ندارید.
سلام و عرض ادب
من یک مشکلی دارم ممنون میشم کمکم کنید .اونم اینکه دیتاهایی رو از سایتی میگیرم ولی چون سطرهای اون اکسل دریافتی از سایت مورد نظر جابجا میشود بعد از آپدیت کردن نمیتونم جلوی جابجایی سطرها رو اکسل خودم رو بگیرم و مجبور میشم بصورت دستی این قیمتهای دریافتی رو جابجا بکنم .آیا امکانش هست که خود اکسل آن اطلاعات دریافتی را بر اساس آنچه که من میخوام تنظیم کند و خودش قیمتها رو آپدیت کند.ممنون
درود بر شما
برای این کار باید کدنویسی انجام بدید که اطلاعات رو جابجا کنه و هر بار ببره بذاره انتهای اطلاعات قبلی.
یک بار ماکرو ضبط کنید برای این کار و سعی کنید کد رو ویرایش کنید
(اگر درست متوجه سوالتون شده باشم)