تابع جالب 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 رو با هم بررسی کردیم. برای مشاهده و بررسی نکات تشریح شده میتونید فایل مورد استفاده در آموزش رو از لینک زیر دانلود کنید.
سلام
یه فاکتوری ساختم.میخوام وقتی روی کلیدی که برای ذخیره تعیین کردم کلیک کردم شیت هربار با اسم خریدار به صورت پی دی اف ذخیره بشه.
کد ذخیره کردنشو زدم.ولی اون قسمتی که باید نام خریدارو از سلول استخراج کنه و فایلو به اون اسم ذخیره کنه بلد نیستم.اگه راهنمایی کنید ممنون میشم
درود بر شما
این مقاله رو بخونید
https://excelpedia.net/payroll/
سلام
من میخوام توی یک فایل اکسل که دارم توی یک شیت اطلاعات A2 عه هر شیتی رو ببینم
توضیح اینکه این فایل فایل موجودی بانک منه و اطلاعات خونه A2 هر روز تغییر میکنه
وقتی من اطلاعات تراکنشهای بانکم رو توی این شیتها insert میکنم خود اکسل همون خونه قدیمی A2 رو که الان شده A6 رو نشون میده
در واقع میخوام همواره فقط A2 باشه و به هیچ عنوان تغییر نکنه
درود بر شما
از ترکیب تابع address, indirect استفاده کنید
نمونه در مقاله زیر هست:
مقاله Address
من فرمولم رو اینجوری نوشتم خطا داد
=INDIRECT(ADDRESS(2,7,1,1,A1))
میخواماطلاعات توی سلول G2
توی شیت A1
ردیف دوم
ستون هفتم
هر اطلاعاتی هست برام بیاره
ممنون میشم بهم بگین
درود
فرمول درسته
احتمالا اسمی که در A1 به عنوان نام شیت نوشتید، یا اشتباه تایپ یداره یا شیتی به اون نام ندارید!
سلام وقت بخیر
لطفا در مورد مقدار دهی یک سلول از طریق سلول دیگر در اکسل هم کمک کنید
a1=20
a2
a3
حالا در سلول a3 توسط تابعی میخوام که مقدار a1 را در a2 قرار دهم اما تابعش را نمیشناسم
در حقیقت میخوام یه شرط بنویسم که اگر شرط صحیح بود مقدار a1 را در a2 قرار دهد در غیر اینصورت کاری انحام نشود.
درود
فرمول رو باید در a2 بنویسید
یعنی در a2 بنویسید :
مثلا اگر a1 بزرگتر از ۲۰ باشه، مقدار a1رو بذاره در a1 در غیر اینصورت هم خالی بذاره (هیچ کاری نکنه)
من میخوام که فرمول در سلول a3 باشه که به محتویات a2در صورت برابر نبودن شرط دست نخوره
میتونید راهنمایی کنید که چطور فرمولی بنویسیم که در صورت برقرار نبودن شرط به محتویات سلول مورد نظر کاری نداشته باشد
با فرمول شما داخل سلول مورد نظر با بلنک پر میشود و محتویات قبلی از بین میرود
درود
از بیرون کنترلی روی سلول ندارید مگر اینکه با VBA مسئله رو حل کنید
نمیتونید شرطی بذارید که اگه برقرا شد فرمولی نوشته بشه و اگه نشد، اصلا فرمول نیاد
یعنی نمیتونید در a3 فرمولی بنویسید که بیاد در a2 فرمولی بذاره یا نه.
سلام وقتتون بخیر.
ببخشید یه سوال داشتم، فرضا من اعداد یک تا ۱۰۰ رو داخل یه شیت نوشتم بعد میخوام از یه فایل یا شیت دیگه این اعداد رو فراخوانی کنم ، ولی بین این اعداد، اعداد زوج بینشون نباشه فقط اعداد فرد رو بخونه. برای این کار باید از چه تابعی استفاده کنم؟ امکانش هست بهم کمک کنید؟؟
سلام و احترام
خانم مهندس یک فایل اکسل حاوی مشخصات و کد چندین نوع کالا دارم . اگر بخوام فاکتور فروش لینک بشه به این فایل اصلی و با زدن کد محصول مشخصات فنی اون رو که شامل چند سلول هست رو با هم فراخوانی و در فرم فاکتور جایگذاری کنه، چطور میتونم؟
ممنون میشم راهنمایی بفرمایید
با دستور vlookup فقط سلول روبری کد محصول نمایش داده میشه و سلول های پایین تر که بقیه مشخصات رو داره نمیاره! چطور میتونم امه مشخصات و بیارم؟
درود بر شما
بستگی به ساختار و چینش داده هاتون داره
میتونید این مقاله رو هم بخونید
https://excelpedia.net/search-duplicates/
با سلام
ببخشید اگر من بخوام یک سلول رو به نحوی آدرس دهی کنم که شماره اون سلول رو از جای دیگه بخونه چیکار میتونم انجام بدم؟
مثلا میخوام بنویسم =M8 که برای ستون M و ردیف ۸ هست اما عدد ۸ رو میخوام از یک جای دیگه به اکسل بگم که با M ترکیب کنه
سلام
از تابع address استفاده کنید
https://excelpedia.net/address-function/
سلام
دنبال تابعی هستم که آدرس یک سلول رو به ما بده و اگر مقدار اون سلول تغییر اون همون مقدار اولیه رو به ما نمایش بده
هست همچین تابعی ؟
تابع address یا تایع cell ادرس سلول رو میده
اما اینکه مقدار قبلی رو بیاره نیاز به کد وی بی دارید تا مقادیر رو در حافظه نگه داره
سلام وقتتون بخیر
من ۲ تا فایل دارم که سلول های آنها به هم ارتباط دارند و به مسیر سلول ارجاع می شوند.
وقتی که فایل ها از فولدر جا به جا میشه…ارجاع سلول ها هم بهم میریزه.و با تغییر یکی ، دیگری دیگه آپدیت نمیشه.
آیا راهی داره که ارجاع با جا به جایی محل فایل ها از بین نره؟
با تشکر
سلام
وقت شما هم بخیر
میتونید از قسمت File گزینه Edit Links رو بزنید و با انتخاب گزینه Change Source فایل رو مجددا انتخاب کنید تا لینک های مربوطه آپدیت بشه.
سلام
من یک ستون دارم که اعداد با فرمت ریالی و دلاری و درهمی و یورو رو داخل اون دارم حالا اگر بخوام داخل چند تا سلول جمع هر کدوم از این فرمت ها رو جداگانه داشته باشم چیکار باید بکنم؟ مممون میشم راهنمایی بکنید چند ماه درگیر این مشکل هستم و هیچ کسی راهنمایی درست که قابل انجام باشه رو نمیدونه
درود
فرمت ریال و درهم و … از طریق فرمت سله؟
یعنی سلول رو که کلیک میکنید در فرمول بار، فقط عدد میبینید؟
ببخشین دوباره مزاحم شدم. اینکارو کردم بار اول جواب میده بعد چندبار استفاده و جمع چندتا سلول دیگه ، فرمولا پاک میشه یا سلولی که بهش ربط نداره جمع میشهو
خواهشمندم راه حلشو بگین.
میخوام با تغییر تو مقدار هر سلول تمام سلولهایی که در فرمول بکار میبرم ، تغییر کنه.
خیلی ممنون. ببخشین بازم
خیلی ممنون
درست شد. خیلی تشکر