جستجو در یک سلول با تابع Find
پیدا کردن عبارتی خاص در یک سلول با استفاده از تابع Find در اکسل
خیلی وقتها میخوایم عبارات داخل سلول رو مورد جستجو قرار بدیم. مثلا میخوایم ببینیم آیا یک کلمه خاص در یک سلول وجود داره یا نه؟ یا مثلا میخوایم عبارت های عددی رو از متن یک سلول استخراج کنیم (البته جستجو کلمات فارسی یه سری نکته ریز داره که در مقاله اصول جستجو در اکسل | جستجوی عبارات فارسی و انگلیسی در موردش صحبت کردیم).
این قبیل کارها رو باید از طریق توابع متنی انجام بدیم. دسته توابع متنی از سری توابع خیلی خیلی کاربردی هستند که بیشتر در هنگام ترکیب خروجی های خیلی خوبی خواهند داشت. تابعی که در این آموزش به آن می پردازیم تابع Find در اکسل هست. حتما این مقاله رو تا انتها مطالعه کنید.
آرگومان های این تابع عبارتند از:
Find_Text: عبارتی که میخواهیم جستجو کنیم.
Within_Text: سلولی که میخواهیم جستجو در اون انجام بشه.
[Start_Num]: این آرگومان اختیاری و از جنس عدد است. اگر خالی بذاریم، جستجو از ابتدای سلول شروع میشه. اگر عدد بذاریم، مثلا ۴. از حرف چهارم به بعد جستجو رو شروع میکنه.
تابع Find در صورت پیدا کردن عبارت مورد نظر، شماره مکان اولین حرف مورد نظر رو داخل سلول میده. پس در صورتیکه جستجو موفق باشه، خروجی، عدد خواهد بود. اگر جستجو به نتیجه نرسه، خروجی خطای #Value! خواهد بود.
حالا با چند مثال کاربرد این تابع رو با هم می بینیم:
در مثال زیر یک سری کد داریم، میخواهیم کدهایی که شامل عدد ۸۷ هستند رو پیدا کنیم.
شکل ۱- تابع find در اکسل – پیدا کردن عبارت خاص در یک سلول
در فرمول نوشته شده در شکل ۱، در صورتی که عدد ۸۷ در سلول وجود داشته باشه، مکان عدد ۸ رو به عنوان خروجی تابع می بینیم. مثلا در سلول A2 نشون میده که پنجمین حرف ۸ و ششمین حرف ۷ هست.
نکته:در صورتی که عدد ۸۷ در سلول تکرار شده باشه، این تابع مکان اولین عدد ۸۷ که پیدا کنه رو به عنوان خروجی نمایش خواهد داد.
حالا فرض کنید بخوایم مسئله رو اینطور حل کنیم که اگر سلول شامل عبارت ۸۷ بود، برامون بزنه “وجود دارد” و اگر نبود، “وجود ندارد”.
مطابق شکل ۲ تابع رو با If و Iserror ترکیب میکنیم.
شکل ۲- تابع find در اکسل – ترکیب تابع Find با If و Iserror
شرح فرمول:
اگر خروجی تابع Find با خطا مواجه شود یا بعبارتی خطای آن True شود، یعنی عبارت مورد نظر پیدا نشده پس خروجی “وجود ندارد” و در غیر اینصورت خروجی فرمول “وجود دارد” خواهد بود.
حالا فرض کنید بخوایم سوال رو اینطور حل کنیم که دنبال عدد ۸۷ بگردیم، اما بعد از قسمت حروفی در کد یعنی بعد از AD. برای این کار باید ببینیم AD چندمین حرف هست. در این مثال، چون کد هست، تعداد ارقام و الگوی اون ثابت هست. در واقع یک کد ۱۱ رقمی است که AD هشتمین و نهمین حرف سلول هست. پس برای اینکه بتونیم اعداد بعد از AD رو مورد جستجو قرار بدیم، باید آرگومان آخر تابع Find رو ۸ بذاریم. به شکل ۳ دقت کنید. وقتی آرگومان سوم ۸ بذاریم، عملیات جستجو از هشتمین حرف شروع میشه. یعنی به عدد ۸۷ که قبل از حرف AD هست، کاری نداره.
شکل ۳- تابع find در اکسل- جستجوی عبارت مورد نظر از یک جایی به بعد
حالا سوال اینجاست که اگه تعداد ارقام کد ثابت نبود و مکان AD مشخص نبود چی؟
برای این کار اول باید با Find مکان AD رو پیدا کنیم. بعد خروجی این تابع رو در آرگومان سوم تابع Find اصلی بذاریم.
به شکل ۴ دقت کنید:
شکل ۴- تابع Find ترکیبی
در این فرمول قسمت FIND(“AD”,A1) مکان AD رو در هر سلول پیدا میکنه. خروجی این تابع به عنوان آرگومان سوم در تابع Find اول قرار داده میشه.
در این مقاله تابع Find رو کاملا توضیح دادیم. این سوال رو میشه در حالت های مختلف مطرح کرد و راه حل های مختلفی با ترکیب سایر توابع متنی ارائه داد. نکته اصلی در کار کردن با توابع متنی این هست که بتونیم بین داده ها الگوی منطقی پیدا کنیم تا با ترکیب توابع مختلف به نتایج دلخواه برسیم.
سلام
در یک شیت اکسل، تعداد ۱۰ ستون و ۱۰۰۰۰ ردیف(نام دانش آموز و کد) دارم. این دانش آموزان هر کدوم هشت ویژگی مهم دارند(مضطرب، وسواسی، بداخلاق، ترسو و …) این ویژگی ها هم در ستون های جلوی نام و کد اونها نشسته(ترتیب قرار گیری این ویژگی ها هم توسط معلمشون تعیین شده. یعنی اگر ویژگی اضطراب یک دانش آموز بولدتر بوده، اون رو در سلول اول بعد از اسمش نوشتیم و بعدش ویژگی وسواسش رو نوشتیم. ولی اگر دانش اموز دیگه ای ویژگی خواب آلودگیش بیشتر از اضطرابش به چشم میاد اول خواب آلودگی رو نوشتیم.(برای این ویژگی ها هم کد گذاشتیم که راحتتر بشه پیدا کرد(کل این ویژگی ها هم ۱۰۰ تاست و یک جدول جدا براشون داریم که مثلا کد a1 یعنی اضطراب)
حالا نیاز داریم یک جدول داشته باشیم که اسم این ۱۰۰ ویژگی بره روی سرستون ها و زیرش کد دانش آموزاش که اون ویژگی رو دارند بنویسه(تو این خروجی اهمیتی نداره که این ویژگی چندم دانش آموز هست.
ممنونم
درود
با توجه به ساختار داده ها و حجم داده، به نظرم بهتره از ابزار یا کد وی بی استفاده کنید
ابزار Power query اول داده ها رو unpivot بکنه که بشه Table و بعد در کوئریف Group By کنید و فرمول نوشته شده رو اصلاح کنید (یا اینکه بعد از unpivot کردن یک پیوت ساده بگیرید)
یا اینکه در پیوت تیبل با Dax فرمول نویسی کنید. فرمولش هم Concatenatext هست
اگر هم بخواید فرمول نویسی کنید باید با توابع match و index و . .. اینکار و انجام بدید
کلا استفاده از هر روش بستگی به میزان تسلط شما در هر حوزه داره، کوئری، وی بی، فرمول
با سلام
چطور میتوانم در اکسل حاصل جمع یک عدد را جستجو کنم اگر عدد مورد نظر بنده، مثلا دو یا سه عدد در هزار سلول اکسل باشد
فرض کنیم از سلول آ۱ تا آ۱۰۰ ما صد عدد داریم
و من به عددی در دفاترم برخورده ام که احیانا حاصل جمع دو یا سه عدد از این ستون است
چطور باید سرچ کنم که اکسل مشخص کند که عدد من حاصل جمع کدام اعداد در کدام سلول ها است؟
درود
اگر زیر ۲۰۰ عدد هست، متونید یک مدل بسازید و از solver add in حل کنید مدل رو
مدل هم به این صورت که از sumproduct اعداد در متغیرهای روبروش استفاده کنید و نتیجه رو معادل عددد مورد نظر قرار بدید
هر کدوم از متغیر ها که بعد از حل مدل، ۱ شدن، یعنی اون اعداد در جمع عدد مورد نظر شما دخیل هستن
میتونید راجع به solver مقاله زیر رو مطالعه کنید
https://excelpedia.net/solver-add-ins/
دقت کنید که متغیرهای مدل رو binary یعنی صفر و یک انتخاب کنید
سلام و درود
بابرنامه اکسل یا ورد ، تعدادی لغت انگلیسی بهم ریخته ، به ترتیب تعداد حروف چطور مرتب میشه مثلا کلمات ۳ حرفی و ۴ حرفی و ۵ حرفی و غیره در ستون های مختلف جدا باشن
ممنونم از راهنماییتون
درود
تابع len تعداد کاراکتر حساب میکنه
از اون استفاده کنید
در یک رشته ۹ رقمی رقم های ۳ و ۴ برای کاربرد خاصی معنا دار هستند.
هر نوبت من لازم دارم یک عدد دو رقمی را جستجو کنم که دقیقا در همین خانه هستند.
با چه فرمولی می توانم ژیدا کنم و اگر بخواهم حذف بزنم چطور؟
درود بر شما
اگر با همین ابزار find بخواید پیدا کنید میتونید با علامت ? انجام بدید
با فرض اینکه در کد نه رقمی به دنبال عدد ۶۷ هستید، در ابزار find بزنید :
?????۶۷??
سلام و وقت بخیر
عذر خواهم من جهت برسی اطلاعات خروجی یک نرم افزار نیاز دارم بتوانم عدد های بعد از یک عبارت خواص را از متن شرح خارج کنم و آنها را وارسی کنم
توضیح اینکه شرح ها متفاوت است اما الگوی کلمه خاص و شروع اعداد یک الگوی ثابت است مثلا کلید واژه من کلمه آبی است و بعد از آن عدد های منحصر به فردی که معرف کالا است و بعد آنها باز شرح های متفاوت می آید.
آنچه مطلوب من است این عدد های منحصر به فرد در بین شرح است
با سپاس
تقوی
سلام
برای اینکه عدد مورد نظر رو از متن بیرون بکشید باید ابتدا و انتهای اون رو پیدا کنید.
برای پیدا کردن نقطه شروع از همان کلمه آبی استفاده کنید و با استفاده از تابع Find جایگاه کلمه آبی رو پیدا کنید (که اگر به آن ۴ تا اضافه شود میشود محل شروع عدد شما)
اگر عدد در انتهای متن هست، کافیه نقطه ای که در بالا پیدا کردید رو در تابع Left یا Right بذارید و عدد رو استخراج کنید.
اگر عدد شما در بین متن قرار داره باید الگوریتمی برای پیدا کردن نقطه انتهایی اون پیدا کنید و نهایتا با استفاده از تابع Mid عدد رو استخراج کنید.
مشکل اینجاست که نقطه پایان از نظر منطقی نظمی ندارد و به شرحی که کاربر نوشته وابسته است تنها یک بخش منطقی وجود دارد آن که پایان عدد [ space] قرار میگیرد و بعد شرح شروع میشود.
باز هم از حسن توجه شما متشکرم
دقیقا از همین فاصله میتونید برای حذف کردن متن انتهایی استفاده کنید.
جواب شما در دو مرحله هست.
مرحله اول جهت پیدا کردن نقطه شروع اعداد، که پس از پیدا کردن این نقطه با استفاده از تابع Right قسمتی که شامل عدد و متن هست رو جدا میکنید و از این به بعد روی این قسمت کار میکنید.
در مرحله دوم کافیه اولیه فاصله رو در تکه ای که در مرحله قبل ایجاد کردید پیدا کنید و با استفاده از تابع Left اعداد را از محل فاصله جدا کنید.
سلام وقتتون بخیر
من میخوام چند عبارت رو به صورت همزمان در اکسل سرچ کنم .در گوگل سرچ زدم میگن ماکرو نویسی داره منم اصلا بلد نیستم .ممنون میشم کمکم کنید
سلام
با استفاده از فرمول نویسی آرایه ای هم میشه این کار رو انجام داد.
البته باید دقیق مشخص کنید که زمانیکه جست و جو انجام شد میخواید چه محاسباتی انجام بشه؟
مثلا میخواید آدرس سلول ها رو بدونید؟ یا تعداد سلول هایی که حاوی اطلاعات مورد نظر شما هست؟ …
با سلام
۲ شیت داریم که در شیت اول اسم شرکت بصورت چند سیلابی مثلا تک خاوران متین روز اومده و در ستون دوم میخوایم فرمولی بدیم که عدد متناظر و مربوط به این شزکتو که در شیت دوم هست بیاره
نکته اینکه ممکنه در شیت دوم اسم شرکت فرق داشته باشه و مثلا خاوران متین باشه
درود
نمیشه کاری کرد
مگر اینکه مثلا مطمئن باشید کلمه خاوران فقط توی همون یک شرکته. در غیر اینصورت اگر کلمه خاوران در یک شرکت دیگه هم وجود داشته باشه کاری نممیتونید بکنید
اگر میتونید کلمه یونیک پیدا کنید، شرط جستجو رو *خاوران* بزنید در vlookup
داخل سلولهایی که در یک ستون هستند یک عبارت به فرمت زیر دارم:ASD123456
میخوام خروجی به این صورت داشته باشم:ASD/1/2/3/4/56 لطفا راهنمایی کنید از چه فرمولی باید استفاده کنم
درود بر شما
اگر تعداد کاراکترها ثابته و این الگو همیشگی هست، یک راه استفاده از ابزار flash fill هست و یک راه فرمول نویسی. ابزار رو از این مقاله مطالعه بفرمایید:
https://excelpedia.net/flash-fill/
برای فرمول هم بهترین راه استفاده از تابع leftو mid و توابع چسباندن مثل concat, concatenate و .. است.
مثلا ASD/1 با این فرمول ایجاد میشه:
با سلام و خسته نباشید
من یک ستون در اکسل دارم که مقادیر هر سول مرکب از اعداد و حروف می باشد (حساب بانکی)
میخواستم که مقدار مشخصی که در ستون دوم میباشد در ستون اول جستجو شود که اولا موجود هست یا نه و شماره سلول یاردیف رو به من نشون بده
مثال:
ستون اول ستون دوم ستون سوم
واریزی ۱-۲-۳۳۳-۱ ۶۶۶ (ردیف ستون اول)
برداشت ۲-۶۶۶-۱ ۳۳۳ (ردیف ستون اول)
درود
جستجو رو با تابع match و ترکیب با * و ؟ میتونید خصوصی سازیش کنید
اما کلا سوال واضح نبود. الان چی رو میخواید سرچ کنید؟
اگر هم میخواید تفکیک کنید میتونید از ابزار flash fill استفاده کنید
سلام خدمت استاد محترم
امکان ارسال فایل وجود نداره تو سایت؟
میتونید توی سایت های دیگه اپلود کنید
ولی در کل نیزای به فایل نیست… میتونید اصل موضوع رو با یک مثال ساده بفرمایید.
بعد رو یداده هاتون اعمال کنید
سلام
من یه جدول مثلا ۳۰*۱۰۰۰ دارم
مثلا ۱۰۰۰ تا ستون دارم(عنوانشونم هست مثلا: “دوره ۱” تا “دوره ۱۰۰۰”)
و مثلا ۳۰ تا سطر به عنوان فصل (“فصل۱” تا “فصل ۳۰”)
تو این جدول اعدادی وجود داره
تو یه قسمتی از کاربر میپرسم از کجا تا کجا رو برات میانگین بگیرم:کاربر مثلا میگه که واسه “فصل ۱” از “دوره ۱۱۵” تا “دوره ۷۴۸” رو برام میانگین بگیر
سوال: بدون اینکه من بیام با عملیات درگ که باید برم مکان موردنظر رو پیدا کنم و اگه هم پیدا کردم احتمال داره اشتباهی محل سلولها رو مشخص کنم یا سرعت و دقت کارم کم بشه و… خود اکسل بیاد برام میانگین اونرو حساب کنه.
ممنون
درود بر شما
از تابع offset کمک بگیرید و محدوده رو بسازید و روی اون میانگین بگیرید
برا یآشنایی با تابع offset مقاله زیر رو ببینید:
https://excelpedia.net/offset-function/
سلام
ممکنه راهنمایی کنید لطفا؟ خیلی اضطراریه
با چه فرمولی میتونم رکوردهایی که عدد سوم بعد از اعشارشون ۵ یا ۶ هستش رو پیدا کنم؟
درود بر شما
=OR(MID(A1,FIND(".",A1)+3,1)="5",MID(A1,FIND(".",A1)+3,1)="6")