تابع جالب Cell در اکسل
گاهی اوقات لازمه که اطلاعاتی راجع به یک سلول داشته باشیم و از اونها در سایر توابع و فرمول نویسی ها استفاده کنیم. اطلاعاتی مثل اینکه آیا سلول قفل شده یا نه؟ نوع فرمت موجود روی سلول، پهنای ستون، نمایش مسیر ذخیره فایلی که سلول مورد نظر داخلش هست و …
در این مقاله قصد داریم به تشریح تابع cell بپردازیم و توضیح بدیم که چطور میتونیم این قبیل اطلاعات رو راجع به یک سلول استخراج کنیم.
آرگومان های این تابع به شرح زیر است:
Info_type: این آرگومان اجباری است و حتما باید تخصیص داده بشه. با توجه به انتخاب یکی از گزینه ها، یکی از اطلاعات موجود در سلول مورد نظر رو نمایش میده.
Reference: این آرگومان اختیاری است و میتونه حذف بشه. بصورت کلی، آدرس سلول مورد نظر در این آرگومان قرار میگیره. اگر حذف بشه، اطلاعات آخرین سلول انتخاب شده نمایش داده خواهد شد.
در جدول زیر، همه اطلاعات راجع به یک سلول رو که میشه از تابع Cell بدست آورد رو میبینیم:
| Info_type | توضیح |
| “address” | آدرس سلول به عنوان یک متن نمایش داده میشه |
| “col” | شماره ستون سلول نمایش داده میشه |
| “color” | اگر سلول مورد نظر با فرمت اعداد منفی (از طریق فرمت سل) فرمت دهی شده باشه عدد یک نمایش داده میشه در غیر اینصورت صفر |
| “contents” | مقدار موجود در سلول نمایش داده میشه. اگر سلولی حاوی فرمول باشه، نتیجه محاسبه شده فرمول، نمایش داده خواهد شد |
| “filename” | مسیر ذخیره فایلی که سلول مورد نظر در اون فایل قرار داره، بصورت متنی نمایش داده میشه. اگر فایل هنوز ذخیره نشده باشه، این تابع خروجی نخواهد داشت و سلول خالی نمایش داده میشه. |
| “format” | نوع فرمت عددی (number format) تخصیص داده شده نمایش داده میشه که در ادامه توضیح بیشتر در این مورد خواهیم داشت. |
| “parentheses” | اگر سلول با پرانتز فرمت دهی شده باشه، عدد ۱ نمایش داده خواهد شد، در غیر اینصورت صفر |
| “prefix” | نحوه قرارگیری یک متن در سلول رو بصورت یکی از علائم زیر نمایش میده.
مقادیر عددی، فارغ از نوع قرارگیری در سلول، بصورت خالی نمایش داده میشه |
| “protect” | اگر سلول قفل باشه عدد یک و اگر نباشه عدد صفر نمایش داده میشه. نکته مهم: قفل بودن سلول با پروتکت بودن متفاوت هست. قفل (Lock) برای هر سلولی بصورت پیشفرض فعال (Format Cell/ Protection/ Lock) هست. اگر بخوایم سلول رو پروتکت کنیم باید طبق مقاله محافظت از فایل، عمل کنیم. |
| “row” | شماره ردیف سلول مورد نظر نمایش داده میشه |
| “type” | متناسب با هر نوع داده، یکی از علائم زیر نمایش داده میشه.
|
| “width” | پهنای ستون رو به نزدیک ترین عدد صحیح گرد میکنه و نمایش میده. واحد عدد نمایش داده شده، پیکسل هست. |
نکته:
اگر در آرگومان دوم این تابع بجای یک سلول یک محدوده تخصیص بدیم، تابع اولین سلول (سمت چپ در صفحه های چپ به راست) رو در نظر میگیره.
در مثال زیر، اطلاعاتی که در مورد یک سلول از تابع Cell میشه گرفت نمایش داده شده است:
شکل ۱- مثال هایی از خروجی های تابع Cell، روی داده متنی
همونطور که در شکل زیر مشخص هست، میتونیم آرگومان اول تابع رو از سلول هم بگیریم. نتیجه رو روی عددی که فرمت دهی شده از طریق فرمت سل می بینیم:
شکل ۲- مثال هایی از خروجی های تابع Cell، روی داده عدد منفی
با توضیحاتی که در بالا داده شد، براحتی میتونیم خروجی های این تابع رو با توجه به ورودی مربوطه، تفسیر کنیم.
تشریح کدهای نمایش داده شده در Info_type: Format
وقتی آرگومان اول تابع Cell تابع Format هست، خروجی تابع بصورت یک سری کد نمایش داده میشه. در جدول زیر معنی و مفهوم این کدها آمده است:
| کد نمایش داده شده | فرمت انتخابی |
| G | General |
| F0 | ۰ (صفر) |
| F2 | 0.00 |
| ,0 | #,##0 |
| ,2 | #,##0.00 |
| C0 | Currency بدون اعشار $#,##0 or $#,##0_);($#,##0) |
| C2 | Currency با دو رقم اعشار ($#,##۰.۰۰ یا$#,##۰.00_);($#,##۰.۰۰) |
| P0 | درصد بدون اعشار 0% |
| P2 | درصد با دو رقم اعشار 0.00% |
| S2 | Scientific notation (نماد علمی) 0.00E+00 |
| G | Fraction ( عدد کسری) # ?/? or # ??/?? |
| D4 | m/d/yy یا m/d/yy h:mm یا mm/dd/yy |
| D1 | d-mmm-yy یا dd-mmm-yy |
| D2 | d-mmm یا dd-mmm |
| D3 | mmm-yy |
| D5 | mm/dd |
| D7 | h:mm AM/PM |
| D6 | h:mm:ss AM/PM |
| D9 | h:mm |
| D8 | h:mm:ss |
از اونجا که فرمت سل خیلی متنوع هست و ممکنه خروجی تابع Cell چیزی غیر از مقادیر بالا باشه، یک سری کلیات رو شرح میدیم که راحت تر بشه تشریح کرد خروجی تابع رو:
- عموما حروف نشون داده شده حرف اول فرمت انتخابی هستن. مثلا G برای General، C برای Currency، P برای Percentage، S برای Scientific و D برای Date.
- با اعداد، واحد پول و درصد، عددی که نمایش داده میشه تعداد رقم اعشار هست. مثلا اگه در یک فرمت دلخواه، عدد مورد نظر سه رقم اعشار داشته باشه مثل ### ، تابع Cell خروجی F3 خواهد داشت.
- کاما (,) که به ابتدای خروجی تابع میچسبه، به عنوان جداکننده هزارگان هست. مثلا فرمت #,#.۰۰۰۰ مقدار ,۴ نمایش میده که نشان دهنده ۴ اعشار و جداکننده هزارگان هست.
- اگر عدد با فرمت Negative number رنگ شده باشه، علامت – به انتهای خروجی فرمول اضافه میشه
- اگر اعداد با پرانتز فرمت دهی شده باشن، علامت () به انتهای خروجی فرمول اضافه میشه.
دقت داشته باشید اگر بعد از فرمول نویسی، داده مورد نظر رو تغییر دادید، باید صفحه محاسبه بشه تا نتیجه فرمول تغییر کنه. این کار از طریق کلید ترکیبی F9 یا از تب Formula و گزینه Calculate انجام پذیر هست.
استفاده از تابع Cell در ترکیب با سایر توابع
همونطور که در بالا دیدیم، تابع Cell میتونه ۱۲ نوع اطلاعات رو راجع به یک سلول نمایش بده. حالا این تابع در ترکیب با سایر توابع میتونه کاربدرهای خیلی بیشتری داشته باشه.
فراخوانی آدرس سلول جستجو شده با استفاده از تابع Cell
هر موقع بخوایم یک داده رو جستجو کنیم و داده مرتبطی رو از یک ستون دیگه فراخوانی کنیم، از vlookup یا ترکیب index, match استفاده میکنیم. حالا اگه بخوایم آدرس سلول فراخوانی شده رو داشته باشیم، میتونیم خروجی تابع vlookup رو در آرگومان دوم تابع Cell قرار بدیم:
=CELL(“address”, INDEX (return_column, MATCH (lookup_value, lookup_column, 0)))
شکل ۳- ترکیب تابع Cell با تابع Index
در این مثال نمیتونیم از تابع Vlookup استفاده کنیم چون خروجی تابع Vlookup مقدار سلول هست نه یک Reference. تابع Index هم ظاهرا مقدار سلول رو نشون میده ولی در عمل Reference رو هم بر میگردونه. پس تابع Cell از خروجی تابع Index به عنوان آرگومان دوم میتونه استفاده کنه.
ایجاد لینک به داده فراخوانی شده با استفاده از تابع Cell
وقتی میخوایم بعد از جستجوی داده مورد نظر، با کالیک بر روی سلول، به داده موجود در دیتابیس مت=نتقل بشیم، میتونیم با استافده از تابع Hyperlinnk، روی خروجی فرمول جستجو، لینک برقرار کنیم.
HYPERLINK(“#”&CELL(“address”, INDEX (return_column, MATCH (lookup_value, lookup_column, 0))), link_name)
در این مثال هم مشابه مثال قبلی از تابع Index و Match برای فراخوانی داده ها استفاده میشه و در تابع Cell قرار میگیره. در نهایت در تابع Hyperlink به یک “#” متصل میشه که به تابع نشون بده منظور شیت فعلی هست.
=HYPERLINK(“#”&CELL(“address”, INDEX(B2:B7, MATCH(E1,A2:A7,۰))), “کلیک کنید”)
شکل ۴- ایجاد ارتباط بین خروجی تابع و دیتابیس
استخراج قسمت های مختلف مسیر ذخیره فایل
همونطور که در بالا توضیح داده شد اگر آرگومان اول، Filename باشه، خروجی تابع، مسیر ذخیره فایل خواهد بود که با ترکیب توابع متنی میتونیم اجزای مختلف این رشته مثل نام فایل، نام شیت و … رو استخراج کنیم.
الگوی خروجی این تابع به صورت زیر خواهد بود
Drive:\مسیر ذخیره\[اسم فایل.xlsx]اسم شیت
با توجه به این الکو می بینیم که اسم فایل بین دو علامت [ ] قرار گرفته و اسم شیت بعد از علامت ]. با توجه به این نکات، میتونیم از توابع متنی Left, Right, Mid و Find استفاده کنیم و مقادیر دلخواه رو استخراج کنیم.
اگر آرگومان دوم تابع رو خالی بذاریم، آدرس فایل و شیت جاری نمایش داده میشه. اما اگر این آرپومان رو مشخص کنیم، آدرس شیتِ سلول انتخاب شده نمایش داده میشه.
استخراج نام فایل با استفاده از تابع Cell
برای اینکه نام فایل رو بصورت داینامیک داشته باشیم و با هر تغییر، نتیجه آپدیت بشه، از ترکیب زیر در تابع Cell استفاده میکنیم:
=MID(CELL(“filename”), Find(“[“, CELL(“filename”))+1, Find(“]”, CELL(“filename”)) – Find(“[“, CELL(“filename”))-1)
شکل ۵- استخراج نام فایل از خروجی تابع Cell
این فرمول چطور کار میکنه؟
چون میدونیم نام فایل بین دو علامت [ ] هست. کافیه با استفاده از تابع Find مکان] و [ پیدا بشه و بعد با تابع MID فاصله بین دو [ و ] رو استخراج میکنیم.
توابع MID و FIND رو در مقالات مرتبط مطالعه کنید.
به نظر شما نام شیت چطور میتونه استخراج بشه؟
پاسخ رو در قالب کامنت در ادامه همین آموزش برامون بنویسید.
استخراج مسیر ذخیره فایل با استفاده از تابع Cell
مسیر ذخیره فایل هم میتونه با منطق مشابه استخراج بشه. یعنی ترکیب تابع Left با Find که مکان [ رو پیدا میکنه. در واقع از ابتدا تا قبل از علامت [ رو نیاز داریم. الگوی زیر مسیر ذخیره رو نمایش میده:
=LEFT(CELL(“filename”), Search(“[“, CELL(“filename”))-1)
شکل ۶- استخراج مسیر ذخیره فایل از تابع Cell
این فایل رو روی هر سیستمی و در هر درایو و فولدری باز کنیم، نتیجه آپدیت میشه و مسیر جدید رو نمایش میده. برای همین میتونیم از این فرمول در تابع Hyperlink و برای ایجاد لینک بین فایل ها استفاده کنیم.
همونطور که میدونیم که تابع Find و Search مشابه هم عمل میکنند فقط تابع Find به حروف بزرگ و کوچک انگلیسی حساس هست.
در این مقاله تابع Cell رو با هم بررسی کردیم. برای مشاهده و بررسی نکات تشریح شده میتونید فایل مورد استفاده در آموزش رو از لینک زیر دانلود کنید.
سلام وقت بخیر
نام کالا و بارکد کالا در اکسل دارم و در یه ستون دیگه میخوام فقط عدد هفتم بارکد نشونم بده چه فرمولی میتونم بنویسم؟
درود بر شما
با تابع mid متونید کاراکتر nام هر استرینگ رو جدا کنید
سلام
روز بخیر
فایل اکسل پیام اخطار میده که یک فرمول ایجاد لوپ کرده و باید تغییرش بدم ولی نمیدونم فرمول کدوم سلول لوپ رو ایجاد کرده!لطف میکنید راهنماییم کنید؟
درود بر شما
این مقاله رو ببینید
https://excelpedia.net/circular-reference-error/
سلام
می خوام در اکسل محتوای یک سل مثلا a1 برابر محتوای یک سل دیگر مثلاً b30 شود بشکلی که اگر محتوای سل b30 با اضافه کردن یک ردیف به سل b31 رفت باز هم a1 محتوای سل b30 که الان خالی است را بخواند و نه b31 را
درود
بنویسید :
=indirect(address(30,2))