تابع Address در اکسل و نکات آن
تابع کاربردی Address
تابع Address در اکسل یکی از توابع خیلی پرکاربرد هست. خروجی این تابع آدرس یک سلول هست و از این تابع برای ساختن محدوده ها استفاده میشه. برای این مسئله باید محدوده ها رو بسازیم. برای اینکه کاربرد این تابع رو ببینیم. لازمه اول آرگومان های این تابع رو معرفی کنیم:
تابع Address پنج آرگومان داره:
Row_Num: شماره ردیف آدرس سلول مورد نظر رو نشون میده. جنس این آرگومان عددی است.
Col_Num: شماره ستون آدرس سلول مورد نظر رو نشون میده. جنس این آرگومان عددی است.
[Abs_Num]: مطلق و نسبی بودن یک سلول. $ در آدرس یک سلول رو تعیین میکنه. از ۱ تا ۴. هر عدد، یکی از حالت ها مطلق و نسبی بودن رو نشون میده. در صورتیکه این آرگومان تعیین نشه بصورت پیش فرض عدد ۱ یعنی Absolute یا کاملا مطلق در نظر گرفته میشه.
[A1]: نحوه آدرس دهی سلول بصورت A1 یا R1c1. این آرگومان بصورت ۰ و ۱ هست. اگر این آرگومان تعیین نیشه، بصورت پیش فرض عدد ۱ یا همون حالت A1 برای آدرس دهی تعیین میشه.
[Sheet_Text]: اسم شیت رو تعیین میکنه. در صورتی که تعیین نشه، آدرس سلول بدون نام شیت خواهد بود.
همونطور که می بینید سه آرگومان آخر داخل براکت [ ] هستن و این یعنی که این آرگومان ها اختیاری هستن.
در ادامه با شرح مثال، کاربردهای این تابع رو میبینیم.
مثال اول
فرض کنید ۴ شیت داریم که میخوایم اطلاعات موجود در سلول D10 همه شیت ها رو یکجا جمع کنیم. برای اینکه با یک فرمول بتونیم خروجی مد نظر رو داشته باشیم، از تابع Address استفاده میکنیم:
=Address(10,4,1,1,A1)
شرح فرمول:
ردیف ۱۰ و ستون ۴ یعنی سلول D10 ، دو آرگومان بعدی که ۱ در نظر گرفته شده یعنی آدرس دهی بصورت مطلق و بصورت نام ستون باشه یعنی $D$۱۰. آرگومان آخر هم که اسم شیت هست و از سلول A1 گرفته شده. (شکل ۱)
شکل ۱- تابع Address در اکسل – فراخوانی سلول مشابه از شیت های مختلف
در نهایت هم آدرس های ایجاد شده در تابع Indirect قرار میدیم که بصورت غیرمستقیم به محتوای آدرس سلول های بدست آمده اشاره بشه.
فقط از طریق تابع Indirect هست که میتونیم به آدرس های خروجی تابع Address دسترسی پیدا کنیم. در واقع خروجی تابع Address حکم متن داره و تا زمانیکه در تابع Indirect قرار نگیره، خاصیت رنج (Range) نداره.
مثال دوم
در شیتی به نام “Sheet2” یک سری داده در ردیف اول ثبت شده است. حالا در یک شیت دیگه میخوایم داده موجود در سلول اول رو با تک تک سلول های بعدی جمع بزنیم. یعنی در یک سلول نوشته شده =A1+B1. میخواهیم با درگ کردن به سمت پایین، بجای اینکه شماره ردیف B1 اضافه بشه، شماره ستونش اضافه بشه. یعنی سلول بعد بشه =A1+C1، بعدی =A1+D1 و …
همونطور که میدونیم در اکسل وقتی به سمت پایین و بالا حرکت میکنیم، بصورت ذاتی فقط شماره ردیف تغییر میکنه نه ستون. پس متوجه میشیم که با $ این مسئله حل نمیشه. باید کاری کنیم تا این آدرس ساخته بشه. به شکل زیر دقت کنید. طوری آدرس رو می نویسیم که با درگ به سمت پایین شماره ستون اضافه بشه:
چون میخوایم با درگ به سمت پایین، شماره ردیف ثابت باشه و فقط شماره ستون زیاد بشه، در آرگومان Row_Num عدد ۱ میذاریم و در قسمت Col_Num تابعی استفاده میکنیم که با درگ به سمت پایین، یکی یکی افزایش پیدا کنه. پس از تابع Row() استفاده میکنیم. خروجی تابع Row(B2) عدد ۲ هست و همینطور الی آخر.
حالا که قسمت دوم فرمول رو ساختیم، کافیه با قسمت اول که A1 ثابت هست، ترکیب کنیم و درگ کنیم به سمت پایین. یعنی:
=$A$1+Indirect(Address(1,Row(B2),”Sheet2”))
با این کار، با درگ به سمت پایین، آرگومان Col_Num یکی یکی اضافه میشه و این هر بار یعنی B1, C1, D1 و …
با تابع Address در اکسل کارهای خلاقانه زیاید میشه انجام داد. سعی کنید خوب کارکرد این تابع رو درک کنید که به موقع بتونید استفاده کنید.
پیشنهاد میکنم مقالات زیر رو هم نگاه کن که ترکیبات بیشتری از این تابع یاد بگیری:
سلام و عرض ادب
آیا میشود آدرس دهی طوری انجام داد که آدرس بر اساس مقدار عددی وارد شده شیت گزارش ساز متغیر باشه؛
فرض بفرمایید ۱۰۰۰ فایل اکسل را داخل یک فولدر داریم با نامهای ۱.xlsx …. تا ۱۰۰۰.xlsx و مثلا در سلول A5 هر کدام از آنها تکست یا عددی درج شده است. حال میخواهیم در در یک فایل گزارشی، فرمولی بنویسیم که با وارد کردن هر عدد بین ۱۰۰۰ – ۱ در آن (مثلا وارد کردن عدد ۵۰) ، فرمول ما فایل xlsx متناظر با آن (۵۰.xlsx) را یافته و یدون باز کردن فایل، محتوای سلول A5 آن فایل را در شیت گزارش نمایش دهد.
با سپاس فراوان از راهنماییتون
سلام و وقت بخیر
میتونید از روش زیر استفاده کنید:
ابتدا یک جدول درست کنید که ستون اول نام فایل و ستون دوم اطلاعات نوشته شده در سلول A5 آن شیت باشه. مقادیر ستون دوم به فایل های مذکور لینک شده باشه که با هر بار باز شدن فایل اصلی و زدن دکمه Update اطلاعات این فایل ها بروز بشه. نهایتا در جائیکه کاربر داره نام فایل رو وارد میکنه میتونید با استفاده از تابع Vlookup مقدار مورد نظر در فایل مذکور رو از جدولی که ساختید فراخوانی کنه.
سلام
من چطور میتونم فغقط صفحاتی چاپ بگیرم که که اطلاعات توش باشه .مثلا ctrl+p که بزنم فقط اون صفحات چاپ بشن
توی یک شیت ۱۰ صحفه دارم کی بستگی به روز هفته که انتخاب میکنم ممکنه از این ده صحفه ۵ تا یا ۷ صحفه دیتا توش باشه که متغیر هست باید چیکار کنم فقط اون صفحات رو چاپ بگیره :ممنون
با سلام
فرض کنید ۱۰۰ شیت داده بنام های sheet1 تا sheet100 دارم. می خواهم در یک شیت جداگانه، خانه A1 از برخی از این ۱۰۰ شیت را با هم جمع کنم.
مثلا یکبار از شیت های ۱ تا ۲۰ را در خانه ای جمع کنم و یکبار در همان خانه از ۲۰ تا ۱۰۰ را.
فرمول جمع یک خانه از تمام شیت ها این است:
(sum(sheet1:sheet100!A1
ولی می خواهم نام شیت های اول و آخرم را از دو خانه دیگر بخواند که هر وقت در آن دو خانه نام شیت اول و آخر را نوشتم، این فرمول جمع خانه A1 شیت های بین این دو را حساب کند.
این کار شدنی است؟
درود
داینامیک کردن فمرول های سه بعدی تقریبا نشدنی هست
اما اگر اسم شیت هاتون عدد باشه، میتونید از این فرمول استفاده کنید:
=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT(A2&":"&B2))&"'!A1"),">0"))در سلول A2 و B2 شماره شیت ها رو بنویسید. مثلا ۱ و ۴. که میشه جمع سلول A1 از شیت ۱ تا ۴
سلام و تشکر
من می خواهم در فرمول آدرس یک سلول در یک شیت، فرمول، نام شیت را از یک خانه دیگر بخواند.
مثلا آدرس من هست : Sheet2!A1
ولی می خواهم این کلمه Sheet2 را از یک خانه دیگر در همان صفحه بخواند و هر وقت آن خانه را تغییر دادم، آدرس در این خانه عوض شود.
مثال: مثلا ۵ شیت داده از Sheet1 تا Sheet5 دارم.
در شیت ششم بنام Calc که محاسباتم را در آن انجام می دهم، ده تا ارجاع دارم که همه از Sheet2 هستند. حال می خواهم به یکباره آدرس همه آن ارجاعاتم به Sheet3 بشود. با تغییر فقط نام شیت در یکی از سلولهای شیت Calc (از Sheet2 به Sheet3) تمام ارجاعاتم درست شود.
درود
عین سوال شما در مقاله بالا پاسخ داده شده
اسم شیت رو در ارگومان اخر میتونید بدید
و بعد هم Indirect
لطفا مقاله رو محجدد به دقت مطالعه کنید
چرا از خروجی تابع Address برای Reference تابع Offset نمی توان استفاده کرد؟؟
راهی برای اینکار وجود دارد یا خیر؟؟
با Indirect میشه
مشکلی نداره
سلام
برای رفرنس دهی در اکسل شیت انلاین ممنون میشم راهنمایی کنید
منظورم اینه مقادیر سلول ها رو در یک شیت در یک شیت دیگر انلاین نشان دهد.
درود
اگر منظورتون از آنلاین اکسل آنلاین هست، مقالات مربوط به گوگل شیت رو مطالعه کنید
در غیر اینصورت توضیح بدید
بله منظور اکسل آنلاین هستش منتهی جواب سوالم رو پیدا نکردم
من دو تا اکسل انلاین دارم میخوام هر عددی در سلول xاکسل انلاین A هست در سلول y اکسل انلاین B هم باشه.
منتهی فرمولش رو پیدا نکردم
از تابع importrange استفاده کنید
=IMPORTRANGE("URL ادرس فایل مبدا l","اسم شیت فایل مبدا!c7")ممنون بابت پاسخ اما importrange مربوط به گوگل شیت هست و در اکسل آنلاین فرمول importrange وجود ندارد
سلام
دوتا سوال داشتم از خدمتتون
اول اینکه چطور وقتی یه سلولو هایپرلینک میکنم بقیه سلول های زیریش هم به ترتیب هایپرلینک بشن به یک عدد بالاتر
مثلا من بایگانی هریک از مشتریام رو جدا کردم تو یک فایل اکسل و یه لیست دارم که اسم و کد هر کیو توش نوشتم و کدارو که به ترتیب هست هایپرلینک کردم تا فایل همون شخص باز بشه. حالا واسه هر کی باید این عملو جدا انجام بدم و با درگ کردن هم نشد، امکانش هست جوری باشه که اتومات هایپرلینک بشه به یه عدد بالاتر
دوم اینکه می خوام نام یک سلول در فیل همون شخص همیشه برگرفته از کدش از لیست باشه بدون اینکه هر بار مجبور باشم ارجاع بدم به لیست مربوطه یا دستی بنویسم امکانش هست ؟
درود
برای سوال اول از تابع Hyperlink استفاده کنید
سوال دوم واضح نیست. توضیح بیشتر بدید لطفا
ببینید من یه لیست دارم که اسامی مشتریام با کداشون توشه
بعدش روی کد، هایپر لینک کردم و وقتی روش کلیک می کنم وارد فایل اکسل اون شخص می شه که یک جدول شلوغ اپتیکیه
که یه قسمتش دوباره کد مشتریه
من هر بار باید اون سلولو مساوی بزنم و برم از لیست روی کدش کلیک کنم (چون ممکنه کدا تغییر کنه مجبورم این کارو بکنم)
حالا وقتی همه کد پشت سر همه نمی شه یه جوری باشه که کد اون سلول همیشه برگرفته از لیست باشه به طوری که وقتی من فایلو برای کس دیگه ای کپی می کنم و براش یه کد بالاترو انتخاب می کنم کدش داخل فایل هم تغییر کنه و به این شکل به یکباره من فرم خامی که دارم واسه ۱۰۰۰ تای آینده آماده کنم بدون اینکه نیاز باشه یکی یکی انجام بشه
سپاس
واقعیت اینه که نمیتونم تصوری از سوالتون داشته باشم!
اینکه منطق پیدا کردن اون کد چیه
شرایط سوال چیه
ساختار دیتابیس چیه
کجاها داینامیکه و …
اینها همه اثرگذاره
میتونید نمونه فالیتون ور داخل گروه تلگرامی بذارید و سوال مطرح کنید
دوستان اونجا راهنماییتون میکنن
با سلام و تشکر از توضیحات خوبتون- سوالی دارم و اون اینکه چطور میتونم عدد داده درون یک سلول رو به عنوان آدرس استفاده کنم؟ مثلا درون سلول b3 عدد ۱۰ قرار داره. حالا من میخوام تو فرمول نویسی مثلا مقدار سلول مثلا f10 رو در یک سلول قرار بدم به نحوی که ۱۰ در اینجا عدد ۱۰ نیست بلکه مقدار داده درون سلول مشخصی مانند b3 هست. ممنون میشم راهنمایی کنید.
درود
همین مقاله بالا ارجع به همین موضوع هست!
ارگومان ردیف در تابع address رو همون سلولی که حاوی عدد هست قرار بدید. ستون هم F یعنی شماره ستون F رو میذاردی و بعد Indirect
بخونید به دقت مقاله رو. جواب میگیرید
با سلام،متاسفانه جواب نگرفتم. بذار سوال رو طور دیگه مطرح کنم. فرض کنید میخوام اطلاعات سلولهای سطر ۱۵ در شیت A1 فایل اکسل رو در شیت مثلا شیتA5 فراخوانی کنم. برای راحتی اینکار میخوام عدد ۱۵ رو در اولین سلول شیت A5 یعنی تو سلول A1 شیتA5بنویسم .حال روی یکی از سلولهای این شیت کلیک می کنم و میخوام مثلا داده سلول B15 شیت اول رو با کمک عدد ۱۵ بالا در که در سلول A1نوشته شده است فراخوانی کنم؟ دقیقا چه دستوری باید بنویسم که از عدد ۱۵ درون سلولa1 به عنوان شماره سطر برای ستونB شیت اول استفاده کنم؟ ممنون میشم از توضیحات جامع شما. خدا نگهدار
درود
اگر مقالاتی که ارائه میشه رو به دقت بخونید مشکلتون حل میشه. ما زمان میذاریم و این مطالب رو تهیه میکنیم که دوستانی مثل شما بتونن استفاده کنن!
تابع address رو اگر بخونید، میبینید ارگومان ردیف و ستون و نام شیت داره.
بجای ارگومان ردیفش، میذارید سلول A1 که داخلش نوشته ۱۵
برای ستون میذارید ۲، چرا؟ چون شماره ستون B عدد ۲ هست.
برای اسم شیت هم داخل دبل کوتیشن تایپ میکنید “A1”
این فرمول، داده موجود در سلول B15 شیت A1 رو نمایش میده
مثال اول حل شده در این مقاله، خیلی مشابه سوال شماست. مطالعه و استفاده کنید
سلام. وقت بخیر. میخوام با درگ کردن یک سلول به سمت چپ بره مشخصات همون سلول (مثلا N1) رو بخونه ولی در شیت ها بعدی
درود بر شما
یکی از راه ها استفاده از تابع address هست
مقاله بالا رو بخونید
سلام شبتون بخیر
سایتتون عالیه خیلی کامل توضیح داده شده
من یه مشکلی با یه فایل اکسل دارم متوجه شدم که name box ساخته شده ولی نمیتونم اون باکس رو پیدا کنم. در قسمت Name Box که روی اون ها میزنم هیچی بهم نشون نمیده. آیا امکان هاید یا قفل کردن دارن و اینکه چجوری میتونم پیدا کنم اون باکس ها رو
ممنون میشم راهنماییم کنید.
خودم جواب رو پیدا کردم بد نیست که اینجا هم بنویسم
متوجه شدم یه سری از شیت ها هاید شده که داخل اون ها باکس هایی وجود داره واسه همین توی لیست باکس ها بهم نشون میداد ولی وقتی انتخابش میکردم هیچی بهم نشون نمی داد.
بازم ممنون ار سایت خوبتون
امشب کلی اطلاعاتم رفت بالا