مطالب برتر
حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfandir
🔵 ارتباط با ادمین:
telegram.me/pctarfand_admin
تبليغات
۰۵
خرداد ۹۴
نظرات
۱۷
معرفی توابع جستجو و جایگزینی در اکسل
مرادی
در برخی از موارد نیاز است که کاربران کاراکتری خاص در یک رشته‌ی متنی را جستجو کنند، مثلاً برای جدا کردن نام و نام خانوادگی نوشته شده در یک سلول کاربر ابتدا باید بتواند محل قرار گرفتن فاصله (Space) در رشته‌ی متنی مورد نظر را پیدا کند یا مثلاً ممکن است شما بخواهید یک حرف یا کلمه‌ی بخصوص را در رشته‌های متنی خود پیدا کرده و با یک عبارت جدید جایگزین کنید، در این موارد شما لازم است از توابع جستجو و جایگزینی در اکسل استفاده کنید. در ادامه مطلب با پی‌سی‌ترفند همراه باشید تا با توابع جستجو و جایگزینی آشنا شوید.
توابع جستجو
۱- تابع Find:
با استفاده از این تابع می‌توان در متن مورد جستجو موقعیت آغاز یک کاراکتر (حرف، عدد و کلمه) را پیدا کرد. لازم به ذکر است که فاصله (space) نیز یک کاراکتر محسوب میشود. ساختار (Syntax) این تابع به شکل زیر است:
=FIND(find_text , within_text , start_num)
این تابع دارای ۳ آرگومان (پارامتر یا شیء) است:
- find_text (آرگومان ۱): عبارتی که بایستی جستجو شود که می‌تواند یک حرف یا یک کلمه یا متن باشد.
- within_text (آرگومان ۲): متن مرجع که در آن به جستجو خواهیم پرداخت. این متن نیز می‌تواند یک حرف یا یک کلمه یا متن طولانی باشد.
- Start_num (آرگومان ۳): نقطه آغاز جستجو است. در واقع مشخص می‌کنیم که در آرگومان ۲ از چندمین کاراکتر شروع به جستجو کند که در این صورت کاراکترهای قبلی جستجو نخواهند شد. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان ۲ می‌تواند باشد.
توجه: درصورتیکه عدد صفر یا عددی بیش از تعداد کاراکتر موجود در آرگومان ۲ به آن اختصاص یابد خطای #VALUE! را نشان خواهد داد.
توجه: اگر آرگومان ۱ در آرگومان ۲ موجود نباشد خطای #VALUE! را خواهیم دید.
۲- تابع Search
تمامی آرگومان‌ها و همچنین عملکرد تابع Searchبا تابع Find یکسان است و تنها تفاوتشان این است که تابع Find به بزرگی یا کوچکی حرف حساس است ولی تابع Search اینگونه نیست. ساختار (Syntax) این تابع به شکل زیر است:
=Search(find_text , within_text , start_num)
منابع:
rahdar-ie.blogfa.com
introexcel.blogfa.com
توابع جایگزینی:
۱- تابع REPLACE:
از تابع REPLACE برای جایگزین کردن قسمتی از یک رشته‌ی متنی توسط رشته‌ی متنی جدید استفاده می‌گردد، ساختار (Syntax) این تابع به شکل زیر است:
=REPLACE(old_text,start_num,num_chars,new_text)
این تابع دارای ۴ آرگومان است:
- old_text (آرگومان ۱): متن قدیمی که قصد دارید عملیات جایگزینی را در آن انجام دهید.
- Start_num (آرگومان ۲): نقطه آغاز جایگزینی در متن قدیمی. در واقع مشخص می‌کنیم که در آرگومان ۱ از چندمین کاراکتر جایگزینی شروع شود. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان ۱ می‌تواند باشد.
- num_chars (آرگومان ۳): تعداد کاراکتری که در متن قدیمی باید با متن جدید جایگزین شوند.
- new_text (آرگومان ۴): متن جدید که باید در متن قدیم جایگزین شود.
به مثال زیر توجه کنید:
=REPLACE(“09122048458″,1,1, “+98″)
فرمول بالا عدد صفر ابتدای شماره تماس را با عبارت ۹۸+ جایگزین می‌کند.
۲- تابع SUBSTITUTE:
تابع SUBSTITUTE همانند تابع REPLACE برای جایگزین کردن بخش از یک رشته‌ی متنی با رشته‌ی متنی جدید مورد استفاده قرار می‌گیرد با این تفاوت که در این تابع دقیقاً مشخص می‌کنیم چه بخشی (دقیقاً کدام کاراکترها) از یک رشته‌ی متنی با رشته‌ی متنی جدید باید جایگزین شود، ساختار این تابع مانند زیر است:
=SUBSTITUTE(text,old_text,new_text,[instance_num])
این تابع دارای ۴ آرگومان است:
۱- text (آرگومان ۱): متن مرجع که قصد دارید عملیات جایگزینی را در آن انجام دهید.
۲- old_text (آرگومان ۲): کاراکترهایی از رشته‌ی متنی مرجع می‌باشد که قرار است با کاراکترهای جدید جایگزین شوند.
۳- new_text (آرگومان ۳): متن جدید که باید در متن قدیم جایگزین شود.
۴- [instance_num] (آرگومان ۴): این آرگومان اختیاری است و زمانی استفاده میشود که آرگومان ۲ چند مرتبه در متن مرجع تکرار شده باشد و بخواهیم تنها یک مورد از آن‌ها جایگزین شود. در این حالت با استفاده از آرگومان ۴ مکان آن را مشخص می‌کنیم.
حال میخواهیم مانند مثال تابع REPLACE عدد صفر را با ۹۸+ جایگزین کنیم:
=SUBSTITUTE(“09122048458″,”0″, “+98″)
خروجی این فرمول عبارت “۹۸۹۱۲۲+۹۸۴۸۴۵۸+” خواهد بود، در واقع تمام صفرهای پیدا شده با عبارت “۹۸+” جایگزین می‌شوند. در این حالت باید از آرگومان ۴ استفاده کرد.اگر این آرگومان خالی باشد (مانند مثال بالا) به این معنی است که تمام صفرها باید جایگزین شوند و اگر به جای این آرگومان عدد ۱ نوشته شود، تنها صفر اول (اولین صفر پیدا شده در رشته) با عبارت “۹۸+” جایگزین میشود، مانند زیر:
=SUBSTITUTE(“09122048458″,”0″, “+98″, 1)
منبع: excelpro.ir
برای مطلع شدن از جدیدترین مطالب سایت، کانال پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:
|
سوالات خود در زمینه ورد و اکسل و ... را میتوانید از طریق ایمیل و تلگرام با ما مطرح کنید. آدرس ایمیل: tarfandha.blog@gmail.com آی‌دی تلگرام: pctarfand_admin@ |
نظرات (۱۷)
-
حمید
سلام
ارسال کردم ولی ظاهرا بعد از زدن دکمه ارسال کد میپره
کدی که میخوام استفاده کنم مربوط به لینک هست و دارای اتریبیوت های تایتل و اچ رف هست -
حمید
با سلام. عذرخواهی می کنم فرمول رو نذاشته بودم
یک سری اطلاعات داخل ستون A1 دارم و یک سری اطلاعات داخل ستون B1 ، میخوام اونها رو در یک فرمول به شکل زیر جایگذاری کنم، ممنون میشم راهنمایی کنید
B1-
پاسخ:
سلاممجددا اشاره ای به فرمول نکردید!
-
-
حمید
سلام .وقت بخیر
یک سری اطلاعات داخل ستون A1 دارم و یک سری اطلاعات داخل ستون B1 ، میخوام اونها رو در یک فرمول به شکل زیر جایگذاری کنم، ممنون میشم راهنمایی کنید
B1-
پاسخ:
سلاماشاره ای به فرمول نکردید!
-
-
امیری
من یه فایل فرهنگ واژگان دارم که متاسفانه در ورد تایپ شده من در اکسل اوردمش اما یه قسمت هایی فاصله خورده در یک ردیف به جای کلمه دو تا کلمه افتاده راهی هست که بشه این فاصله حذف بشه و به صورت یک شت مستقل اضافه بشه-
پاسخ:
سلامتا جاییکه اطلاع دارم نمی توان بصورت خودکار اینکار را در اکسل انجام داد. باید دستی اصلاح کنید.
-
-
sam
سلام؛ من میخواهم دو حرف «ی» و «ک» عربی را با دو حرف «ی» و «ک» فارسی در آنِ واحد با یکبار فرمول SUSBTITUTE عوض کنم. برای یک حرف مثلاً حرف ک فرمول زیر را نوشتم و خیلی خوب جواب داد ولی برای هر دو حرف چه میشود کرد؟ مثلاً اگر بخواهیم همزمان هم حرف ی و هم حرف ک را در یک بار فرمول نویسی با SUSBTITUTE عوض کنیم چه باید کرد؟ ممنون.
=SUBSTITUTE(A2;"ک";"ک")-
پاسخ:
سلام
از فرمول تو در تو استفاده کنید. به طور مثال در فرمول زیر a با a1 و b با b1 جایگزین می شود.
SUBSTITUTE(SUBSTITUTE(F1,"a","a1"),"b","b1")
موفق باشید.
-
-
احسان صفابخش
با سلام
در اکسل میخواهم با انتخاب یک سل یه لیست شامل اعداد انتخاب گردد.
ممنونم-
پاسخ:
سلام
با انتخاب سل که بعید می دانم امکان پذیر باشد مگر اینکه یک دکمه برای اینکار بسازید.
-
-
حجت
سلام راه حل یا فرمول شمردن تعداد یک کاراکتر خاص در یک سلول اکسل چی هستش-
پاسخ:
-
-
امین
سلام
یک فایل اکسل شامل دو ردیف کد شناسایی و نام و نام خانوادگی داریم. افراد جدیدی تنها با نام و نام خانوادگی و بدون کد شناسایی به مجموعه اضافه شده.
سوال و مشکل این است که میخواهیم در شیت جدید میخواهیم کل افراد باشند به گونه ای که جلوی نام افراد قدیم کد شناسایی قدیم و جلوی نام افراد جدید بجای کد شناسایی یا خالی باشد یا عدد صفر قرار گیرد-
پاسخ:
سلام
می خواهید اینکار به صورت خودکار در شیت جدید انجام شود؟ یعنی با اضافه کردن اطلاعات در شیت اول، در شیت دوم خواسته شما انجام شود.
-
-
سلام. در اکسل لیستی است که در هر سلول آن کلمه ای وجود دارد و توضیح آن کلمه درون پرانتز قرار دارد . می خواهم آن متن درون پرانتز و خود پرانتز را کامل در اکسل حذف کنم.
با تشکر-
پاسخ:
سلام
با استفاده از ابزار Find and Replace عبارت (*) را جستجو کنید و با هیچ جایگزین کنید.
موفق باشید.
-
-
احمد
سلام
ایا میشود با نوشتن یک اسم در سلول , این اسم را جایگزین اسم اصلی فایل کرد؟-
پاسخ:
سلام
اگر منظورتان این است با نوشتن یک اسم در سلول، اسم فایل اکسل با اسم تایپ شده جایگزین شود، بعید می‌دانم اینکار امکان‌پذیر باشد. چون برای تغییر نام باید عملیات ذخیره فایل انجام شود که در اینکار انجام نمی‌شود.
-
-
مهدی میکا
ممنون از وقتی که گذاشتینالبته روش find& replace درسته ک روش ساده ایه اما تمام ارزش های یک فایل اکسل رو تغییر میده و نمیشه در مورد یک ستون خاص استفادش کرددر اصل تو همین مثالی که زدم ممکنه ستون های دیگه ای هم کلمات بله و خیر داشته باشن که من نخوام تغییر کنن ولی با این روش تمام اونها هم به یک و دو تبدیل میشنبازم ممنون از کمکتون -
مهدی میکا
ممنون بابت مطلبی ک لینکشو گذاشتینولی من منظورم اینه که مثلا یک ستون داشته باشم که تمام ارزش های اون ستون کلمات بله و خیر باشن و قصدم این باشه که تمام اون بله ها با عدد یک و تمام اون خیرها با عدد دو جایگزین بشنچون با این روشی ک تو این پیج توضیح داده شده فقط ارزش یک سلول تغییر میکنه نه تمام سلول های اون ستون مورد نظرجایگزینی گروهی هم خیلی وقت گیر هست-
پاسخ:
مطلبی که گفتم دقیقا مشکل شما را حل می‌کند. اما کار شما خیلی ساده است. با ابزار جستجو و جایگزینی اکسل (Ctrl+H) کلمه بله را پیدا کنید و با ۱ جایگزین کنید. برای خیر هم همین کار را انجام دهید.
-
-
مهدی میکا
سلامدر مورد جایگزینی یک کلمه با کلمه دیگه میخواستم بپرسم چطور میشه این تابع رو روی یک ستون خاص اعمال کرد-
پاسخ:
-
-
اکسل
سلام
و تشکر
خیلی کارم رو راه اندخت
و ممنون از سایت خوبتون -
اکسل
سلام
یک سوال داشتم. در یک فایل اکسل با دو شیت
شیت اول دارای یک ستون است: نام
شیت دوم هم دارای دو ستون است: نام وتلفن
نام ها در شیت اول شامل همون نام های شیت اول + نام های دیگری که اضافه شده است (نام ها بیشتر از شیت دوم است)
ترتیب هم در دو شیت متفاوت است.
حال می خواهیم تلفن هایی که قبلا در شیت دوم برای نام ها اختصاص دادیم در شیت اول هم مقابل نامشان (در ستون دوم) کپی شود. آنهایی که خالی می ماند دستی باید وارد شود
راه حل چیست؟-
پاسخ:
سلام
از تابع vlookup که در اینجا معرفی شده است استفاده کنید.
مثلا اگر نام‌های شیت اول در ستون A باشد و نام و تلفن‌های شیت دوم در ستون A و B باشد. در ستون B شیت اول از فرمول زیر استفاده کنید.=VLOOKUP(A1,Sheet2!A:B,2,FALSE)
موفق باشید.
-
-
سلام ممنون از حضور و نظر.
-
سلام لطفا به وبلاگ من سر بزنید و نظرتان را بگویید.ممنون
