تابع Address در اکسل و نکات آن
تابع Address در اکسل یکی از توابع خیلی پرکاربرد هست. خروجی این تابع آدرس یک سلول هست و از این تابع برای ساختن محدوده ها استفاده میشه. برای این مسئله باید محدوده ها رو بسازیم. برای اینکه کاربرد این تابع رو ببینیم. لازمه اول آرگومان های این تابع رو معرفی کنیم:
تابع Address پنج آرگومان داره:
Row_Num: شماره ردیف آدرس سلول مورد نظر رو نشون میده. جنس این آرگومان عددی است.
Col_Num: شماره ستون آدرس سلول مورد نظر رو نشون میده. جنس این آرگومان عددی است.
[Abs_Num]: مطلق و نسبی بودن یک سلول. $ در آدرس یک سلول رو تعیین میکنه. از 1 تا 4. هر عدد، یکی از حالت ها مطلق و نسبی بودن رو نشون میده. در صورتیکه این آرگومان تعیین نشه بصورت پیش فرض عدد 1 یعنی Absolute یا کاملا مطلق در نظر گرفته میشه.
[A1]: نحوه آدرس دهی سلول بصورت A1 یا R1c1. این آرگومان بصورت 0 و 1 هست. اگر این آرگومان تعیین نیشه، بصورت پیش فرض عدد 1 یا همون حالت A1 برای آدرس دهی تعیین میشه.
[Sheet_Text]: اسم شیت رو تعیین میکنه. در صورتی که تعیین نشه، آدرس سلول بدون نام شیت خواهد بود.همونطور که می بینید سه آرگومان آخر داخل براکت [ ] هستن و این یعنی که این آرگومان ها اختیاری هستن.
در ادامه با شرح مثال، کاربردهای این تابع رو میبینیم.
مثال اول
فرض کنید 4 شیت داریم که میخوایم اطلاعات موجود در سلول D10 همه شیت ها رو یکجا جمع کنیم. برای اینکه با یک فرمول بتونیم خروجی مد نظر رو داشته باشیم، از تابع Address استفاده میکنیم:
=Address(10,4,1,1,A1)
شرح فرمول:
ردیف 10 و ستون 4 یعنی سلول D10 ، دو آرگومان بعدی که 1 در نظر گرفته شده یعنی آدرس دهی بصورت مطلق و بصورت نام ستون باشه یعنی $D$10. آرگومان آخر هم که اسم شیت هست و از سلول A1 گرفته شده. (شکل 1)
شکل 1- تابع Address در اکسل – فراخوانی سلول مشابه از شیت های مختلف
در نهایت هم آدرس های ایجاد شده در تابع Indirect قرار میدیم که بصورت غیرمستقیم به محتوای آدرس سلول های بدست آمده اشاره بشه.
فقط از طریق تابع Indirect هست که میتونیم به آدرس های خروجی تابع Address دسترسی پیدا کنیم. در واقع خروجی تابع Address حکم متن داره و تا زمانیکه در تابع Indirect قرار نگیره، خاصیت رنج (Range) نداره.
مثال دوم
در شیتی به نام “Sheet2” یک سری داده در ردیف اول ثبت شده است. حالا در یک شیت دیگه میخوایم داده موجود در سلول اول رو با تک تک سلول های بعدی جمع بزنیم. یعنی در یک سلول نوشته شده =A1+B1. میخواهیم با درگ کردن به سمت پایین، بجای اینکه شماره ردیف B1 اضافه بشه، شماره ستونش اضافه بشه. یعنی سلول بعد بشه =A1+C1، بعدی =A1+D1 و …
همونطور که میدونیم در اکسل وقتی به سمت پایین و بالا حرکت میکنیم، بصورت ذاتی فقط شماره ردیف تغییر میکنه نه ستون. پس متوجه میشیم که با $ این مسئله حل نمیشه. باید کاری کنیم تا این آدرس ساخته بشه. به شکل زیر دقت کنید. طوری آدرس رو می نویسیم که با درگ به سمت پایین شماره ستون اضافه بشه:
چون میخوایم با درگ به سمت پایین، شماره ردیف ثابت باشه و فقط شماره ستون زیاد بشه، در آرگومان Row_Num عدد 1 میذاریم و در قسمت Col_Num تابعی استفاده میکنیم که با درگ به سمت پایین، یکی یکی افزایش پیدا کنه. پس از تابع Row() استفاده میکنیم. خروجی تابع Row(B2) عدد 2 هست و همینطور الی آخر.
حالا که قسمت دوم فرمول رو ساختیم، کافیه با قسمت اول که A1 ثابت هست، ترکیب کنیم و درگ کنیم به سمت پایین. یعنی:
=$A$1+Indirect(Address(1,Row(B2),”Sheet2”))
با این کار، با درگ به سمت پایین، آرگومان Col_Num یکی یکی اضافه میشه و این هر بار یعنی B1, C1, D1 و …
با تابع Address در اکسل کارهای خلاقانه زیاید میشه انجام داد. سعی کنید خوب کارکرد این تابع رو درک کنید که به موقع بتونید استفاده کنید.
برچسب:تابع Address, تابع Indirect, متوسط
ممکن است همچنین دوست داشته باشید
توابع منطقی در اکسل | تابع XOR
عملگرهای منطقی در اکسل
16 نظر
سلام وقت شما بخیر
یه سوال من حدود 50 شیت دارم که میخوام یک جدول گزارش از این شیت ها از یکسری خانه های خاص تهیه کنم که در جدولم بیاد از چه فرمولی میتونم استفاده کنم
بعنوان مثال نام طرف حساب – میزان بدهی – میزان خرید – مانده حساب رو از شیت بیارم داخل جدول برای همه 50 شیت
متشکر میشم اگه راهنمایی بفرمائید.
با تشکر
درود بر شما
اگر ساختار فایل مشخص باشه و یکسان، میتونید از همین تابع Address استفاده کنید و سلول های مورد نظر رو بیارید توی شیت دلخواه.
کلا بستگی به ساختار فایل و شیت ها داره
سلام مجدد. ممنون از راهنمایی و مقاله خوبتون
لازم به ذکره، آدرس دهی من بصورت فرموله (پویا) هست که با تغییر سلول کناری آدرس نیز تغبییر میکنه
در اینحالت وقتی فایل مورد نظر باز هست محتویات سلول نمایان میشه و فایل به درستی کار می کنه ولی وقتی فایل بسته میشه ارور ref# نمایان میشه.
درود بر شما
مقاله زیر رو بخونید
https://excelpedia.net/excel-external-link/
آدرس کامل فایل رو باید بدید
سلام و خسته نباشیدبه شما
سوالی داشتم در خصوص آدرس دهی و ارجاع محتویات یک سلول از یک ورک بوک بسته به یک شیت در اکسل
فرمول را با استفاده از iNDIRECT نوشتم ولی وقتی ورک بوک بسته میشه ارور میده و محتویات نمایش داده نمیشه
ممنون میشم راهنمای کنید
درود بر شما
مقاله زیر رو مطالعه کنید
https://excelpedia.net/excel-external-link/
سلام و خسته نباشید
من یه فایل اکسل دارم که آمار تولید رو در اون وارد می کنم – از جدول 001 به عنوان دیتا بیس با بیش از 150 محصول استفاده می کنم که در اون مثلاً مشخص شده تعداد در کارتن برای محصول کد0022 مقدار 100 عدد است- اما گاهی پیش میاد که اطلاعات از یه تاریخی به بعدتغییر می کنه مثلاً میشه 200 عدد – چطور می تونم آدرس بدم که از این به بعد از جدول 002 به عنوان دیتا بیس استفاده کن
درود بر شما
سوالتون خیلی بستگی به ساختار فایل و منطق استفاده داره. نمیشه نظر داد به راحتی.
اما اگر هدف عوض کردن پیج هست با یک if شرز رو بررسی کنید و بعد با تابع address مرجع رو جابجا کنید
سلام. فرض یه جدول داریم که یه ستونش امار تولید می باشد. این آمار از دست نوشته وارد سیستم میشود. اولین و آخرین تولید هر صفحه دستی را با ستاره و مربع در یک ستون نمایش میدهیم. حالا چطور میتوانیم مشخص کنیم که جلوی علامت مربع که آخرین ورودی صفحه دستی می باشد جمع تولید آن صفحه مشخص گردد یعنی جمع تولید از هر ستاره تا مربع بعدی در ستون کناری مربع اتوماتیک جمع زده شود؟
سلام،
سوال اینه که مربع ها رو چطور وارد کردید؟
با استفاده از Shape یا با استفاده از فونت های Webdings؟
اگر با استفاده از فونت های Webdings این کار رو کردید میتونید با ترکیب If این کار رو انجام بدید.
به اینصورت که در یک ستون خالی بنویسید
در این فرمول فرض شده که میزان تولید در ستون A نوشته شده و علائم مربع در ستون B وارد شده.
اون دو نقطه در شرط IF کارکتر معادل مربع در فونت Webdings هست.
با عرض سلام خدمت سروران گرامی
سوال اینجانب اینست که چطور میتوان در اکسل زمانی که تابع Address را درگ می کنیم محتوای ستون یا سطر نیز متناسباً تغییر کند. مطابق توضیحات بالا میبایست در قسمت abs_num عدد 4 گذاشته شود اما جواب تاکنون جواب نداده، لطفا راهنمایی فرمایید.
=(ADDRESS(2,3,4,,”sheet1″))
از لطفتان بسیار سپاسگزارم
درود بر شما
برا یتغییر باید آرگومان row یا col رو متغیر بذارید. مثلا بجای عدد 3 بذارید row(A3) که با درگ کردن تغییر کنه.
آرگومانی که $ رو تعیین میکنه فقط روی نتیجه نهایی $ اعمال میکنه. در درگ کردن خود تابع address نقشی نداره
سلام.یه سوالی داشتم اگه بتوانید کمکم کنید لطف بزرگی کرده اید.
من یه فایل اکسلی دارم که مربوط به یه سری داده ها است که به صورت ماهیانه از 31 شیت تشکیل شده است از یکم تا سی و یکم. یک سری فرمول نویسی در گزارش ماهیانه دارم که از شیت ها یک تا 31 باید فراخوانی شود و در گزارش ماهیانه نشان دهد. فرمول گزارش ماهیانه بدین صورت است(SUM(‘1’!$N$4:$P$6=. میخواستم ببینم فرمول یا عبارتی است که با گذاشتن آن و درگ کردن به سمت پایین فقط شیت یک تا 31 به ترتیب درگ شود. و نیازی نباشد به صورت دستی وارد کرد.
با تشکر فراوان
درود بر شما
از همین تابع که در بالا اموزش داده شده میتونید اسفاده کنید و از ارگومان شیت برای این موضوع استفاده کنید. یا:
با سلام
دو سوال داشتم.
ا. آیا قالب آماده اکسل سراغ دارید که بتوان به عنوان to do list حرفه ای از آن استفاده کرد.
۲. من یک فایل اکسل با چهار ستون دارم که اطلاعات به صورت نامرتب زیر هم درج شده اند. به این صورت که
ستون یک= نام افراد (مثلا ۲۰ نفر)
ستون دوم = عنوان فعالیت/ کاری که به آنها محول شده است( کارهای مختلف و غیر تکراری)
ستون سوم= تاریخی که کار به افراد محول شده است.
ستون چهارم = شرکت /اداره ای که فعالیت مد نظر به آن مرتبط است.
میخواهم به شکل تفکیک شده در یک شیت مجزا
نام هر فرد با کارهایی که به او محول شده است مرتب شده بیاید.
لطفا راهنمایی کنید . با تشکر و سپاس بیکران
سلام
1- متاسفانه خیر.
2- بهترین راه استفاده از پیوت تیبل هست که هر نوع گزارش رو از روی اطلاعاتتون در بیارید