توابع متن در فرمول های اکسل: حروف بزرگ، کوچک و propn. نمونه هایی از توابع متنی در اکسل هر مقدار متنی در اکسل

نام کامل، اعداد کارت های بانکی، آدرس مشتریان یا کارمندان، نظرات و موارد دیگر - همه اینها خطوطی هستند که بسیاری در هنگام کار با آنها مواجه می شوند. برنامه اکسل. بنابراین، امکان پردازش این نوع اطلاعات مفید است. این مقاله به توابع متنی در اکسل می پردازد، اما نه همه آنها، اما آنهایی که به نظر سایت مفیدترین و جالب ترین هستند:

شما می توانید لیستی از تمام توابع متنی را در برگه "Formulas" => "Text" لیست کشویی پیدا کنید:

عملکرد چپ

یک زیر رشته از متن را به ترتیب از چپ به راست در تعداد معینی از کاراکترها برمی گرداند.

نحو:=LEFT(متن، [تعداد کاراکترها])

تعاریف استدلال:

• text – یک رشته یا یک مرجع به سلولی حاوی متنی که می‌خواهید یک زیررشته را از آن برگردانید.
• number_characters یک آرگومان اختیاری است. یک عدد صحیح که نشان می دهد چند کاراکتر از متن بازگردانده می شود. پیش فرض 1 است.

مثال استفاده:

فرمول: =LEFT("متن سفارشی"؛8) - مقدار بازگشتی "Custom".

عملکرد راست

این تابعمشابه تابع LEFT، با این تفاوت که کاراکترها از انتهای رشته برگردانده می شوند.

مثال استفاده:

فرمول: =RIGHT ("متن آزاد"؛ 5) - مقدار بازگشتی "متن" است.

تابع LENGTH

برای تعیین طول رشته استفاده می شود. نتیجه یک عدد صحیح است که تعداد کاراکترهای متن را نشان می دهد.

نحو:=LENGTH(متن)

مثال استفاده:

تابع FIND

عددی را برمی‌گرداند که اولین کاراکتر زیر رشته متن جستجو است. اگر متن پیدا نشد، خطای "#VALUE!" برگردانده می شود.

نحو:=FIND(متن_جستجو؛ متن_جستجو؛ [موقعیت_شروع])

تعاریف استدلال:

• search_text – رشته ای که باید پیدا شود.
• search_text – متنی که اولین آرگومان در آن جستجو می شود.
• start_position یک عنصر اختیاری است. یک عدد صحیح را می‌پذیرد که مشخص می‌کند جستجو با کدام کاراکتر text_to_search باید آغاز شود. پیش فرض 1 است.

مثال استفاده:

از گزیده ای از شعر شاعر بزرگ S.A. Yesenin به وقوع اولین شخصیت از خط "پرنده" پی می بریم. جستجو از ابتدای خط انجام می شود. اگر در مثال بالا جستجو از روی کاراکتر 40 انجام شود، تابع یک خطا برمی‌گرداند، زیرا هیچ موقعیت ورودی پیدا نشد

تابع REPLACE

این تابع بخشی از یک رشته را در تعداد مشخصی از کاراکترها، که با کاراکتر مشخص شده شروع می شود، با متن جدید جایگزین می کند.

نحو: REPLACE (متن_قدیمی، موقعیت_شروع، تعداد کاراکترها، متن_جدید)

تعاریف استدلال:

• old_text – رشته یا پیوندی به سلول حاوی متن.
• start_position - شماره سریال کاراکتر از چپ به راست که باید جایگزین شود.
• number_characters – تعداد کاراکترهایی که از start_position شروع می‌شود و باید با متن جدید جایگزین شوند.
• new_text – رشته ای که جایگزین بخشی از متن قدیمی می شود که با آرگومان های start_position و تعداد کاراکترها مشخص شده است.

مثال استفاده:

در اینجا، در خط موجود در سلول A1، کلمه "old" که از نویسه 19 شروع می شود و 6 کاراکتر است، با کلمه "جدید" جایگزین می شود.

در مورد پردازش تعداد کمی سلول، این روش کاملا قابل قبول است. اما اگر نیاز به پردازش چندین هزار ردیف مختلف داشته باشید، فرآیند ایجاد فرمول ها کار فشرده خواهد شد. بنابراین، مثال در نظر گرفته شده را با استفاده از دانش توابعی که قبلاً در ابتدای مقاله توضیح داده شده است، دوباره انجام می دهیم:

• آرگومان "start_position" با تابع "FIND" جایگزین خواهد شد.
• به جای آرگومان "تعداد کاراکترها" تابع "LENGTH" را وارد می کنیم.

در نتیجه، فرمول را دریافت می کنیم: =REPLACE(A1; FIND("قدیمی";A1);LENGTH("قدیمی")؛"جدید")

به صفحه چاپ پنجره عملکرد نگاه کنید. نتایج برگردانده شده آرگومان ها تغییر نکرده اند، اما اکنون می توان آنها را به صورت پویا با یک بار نوشتن فرمول تعیین کرد.

تابع SUBSTITUTE

این تابع، تکرارهای زیر رشته مشخص شده در متن را با متن جدید جایگزین می‌کند که مشابه تابع “REPLACE” است، اما تفاوت اساسی بین آنها وجود دارد. اگر تابع "REPLACE" متن مشخص شده را به صورت دستی تغییر دهد، تابع "SUBSTITUTE" به طور خودکار وقوع رشته مشخص شده را پیدا کرده و آنها را تغییر می دهد.

نحو: SUBSTITUTE (متن؛ متن_قدیمی؛ متن_جدید؛ [شماره_رویداد])

تعاریف استدلال:

• old_text – رشته فرعی از اولین آرگومان که باید جایگزین شود.
• new_text – رشته ای برای جایگزینی متن قدیمی.
• رخداد_شماره آرگومان اختیاری است. یک عدد صحیح را می‌پذیرد که نشان‌دهنده شماره دنباله رخداد old_text است که قرار است جایگزین شود؛ همه رخدادهای دیگر تحت تأثیر قرار نخواهند گرفت. اگر آرگومان را خالی بگذارید، همه رخدادها جایگزین خواهند شد.

مثال استفاده:

خط در سلول A1 حاوی متنی است که دارای 2 رشته فرعی "قدیمی" است. ما باید رشته "جدید" را به جای اولین رخداد جایگزین کنیم. در نتیجه بخشی از متن «...قدیمی-قدیمی...» با «...جدید-قدیمی...» جایگزین می شود.

اگر آخرین آرگومان حذف شود، نتیجه رشته "رشته ای حاوی متن جدید-جدید" خواهد بود.

تابع PSTR

PSTR از رشته مشخص شده بخشی از متن را در تعداد مشخصی از کاراکترها، با شروع از کاراکتر مشخص شده، برمی گرداند.

نحو: PSTR (متن؛ موقعیت_شروع؛ تعداد_کاراکترها)

تعاریف استدلال:

• متن – رشته یا پیوندی به سلول حاوی متن؛
• start_position - شماره سریال کاراکتری که رشته باید از آن برگردانده شود.
• number_characters - یک عدد صحیح طبیعی که تعداد کاراکترهایی را که باید برگردانده شوند، از موقعیت start_position شروع می‌کند.

مثال استفاده:

از متنی که در سلول A1 قرار دارد، باید 2 کلمه آخر را که در مجموع 12 کاراکتر طول دارند، برگردانید. اولین کاراکتر عبارت برگشتی دارای دنباله شماره 12 است.

ممکن است آرگومان number_characters از طول مجاز کاراکترهای برگشتی بیشتر شود. آن ها اگر در مثال در نظر گرفته شده، به جای number_characters = 12، مقدار 15 مشخص شده بود، نتیجه تغییر نکرد و تابع نیز رشته "عملکرد PSTR" را برگرداند.

برای سهولت استفاده از این تابع، آرگومان های آن را می توان با توابع "FIND" و "LENGTH" جایگزین کرد، همانطور که در مثال با تابع "REPLACE" انجام شد.

عملکرد فضا

این تابع تمام فضاهای اضافی را حذف می کند: فاصله های اطراف لبه ها و فاصله های دو برابر بین کلمات. پس از اینکه تابع رشته ها را پردازش کرد، تنها فاصله های تکی بین کلمات باقی می ماند.

نحو:=فضا(متن)

مثال استفاده:

فضا( "متن با فاصله اضافی بین کلمات و اطراف لبه ها" )

نتیجه اجرای تابع خط زیر خواهد بود: "متن با فاصله اضافی بین کلمات و اطراف لبه ها" .

عملکرد اتصال

با استفاده از تابع CONCATENATE می توانید چندین خط را با هم ترکیب کنید. حداکثر تعداد ردیف هایی که باید ادغام شوند 255 است.

نحو:=CONCATENATE(متن1، [متن2]، ...)

تابع باید حداقل یک آرگومان داشته باشد

در اینجا متداول ترین توابع متن اکسل مورد استفاده قرار می گیرند (مرجع سریع). اطلاعات تکمیلیدر مورد توابع را می توان در کادر محاوره ای تابع جادوگر و همچنین در سیستم کمک اکسل.

توابع متنی مقادیر متن عددی را به اعداد و مقادیر عددی را به رشته های کاراکتری (رشته های متنی) تبدیل می کنند و همچنین به شما امکان می دهند عملیات مختلفی را روی رشته های کاراکتر انجام دهید.

تابع TEXT

تابع TEXT یک عدد را به یک رشته متن با فرمت مشخص تبدیل می کند. نحو:

=TEXT (مقدار، قالب)

آرگومان مقدار می تواند هر عدد، فرمول یا مرجع سلولی باشد. آرگومان format تعیین می کند که رشته برگشتی چگونه نمایش داده شود. برای تنظیم قالب مورد نیاز می توانید از هر یک از کاراکترهای قالب بندی به جز ستاره استفاده کنید. استفاده از قالب عمومی مجاز نیست. به عنوان مثال، فرمول زیر رشته متن 25،25 را برمی گرداند:

=TEXT(101/4"0.00")

تابع روبل

تابع DOLLAR یک عدد را به رشته تبدیل می کند. با این حال، RUBLE یک رشته را در قالب ارز با تعداد مشخص شده اعشار برمی گرداند. نحو:

=RUBLE(تعداد، تعداد_نویسه ها)

اکسل در صورت لزوم عدد را گرد می کند. اگر آرگومان number_characters حذف شود، اکسل از دو رقم اعشار استفاده می کند و اگر این آرگومان منفی باشد، مقدار برگشتی به سمت چپ نقطه اعشار گرد می شود.

تابع LENGTH

تابع LEN تعداد کاراکترهای یک رشته متنی را برمی گرداند و دستور زیر را دارد:

=LENGTH(متن)

آرگومان متن باید یک رشته کاراکتری باشد که در آن محصور شده باشد نقل قول های دوگانه، یا یک مرجع سلولی. به عنوان مثال، فرمول زیر مقدار 6 را برمی گرداند:

=LENGTH ("سر")

تابع LENGTH طول متن یا مقدار نمایش داده شده را برمی گرداند، نه مقدار ذخیره شده سلول. علاوه بر این، صفرهای ابتدایی را نادیده می گیرد.

عملکرد CHARACTER و CHARACTER CODE

هر کامپیوتری از کدهای عددی برای نمایش کاراکترها استفاده می کند. رایج ترین سیستم رمزگذاری کاراکتر ASCII است. در این سیستم اعداد، حروف و سایر نمادها با اعداد از 0 تا 127 (255) نشان داده می شوند. توابع CHAR و CODE به طور خاص با کدهای ASCII سروکار دارند. تابع CHAR کاراکتری را برمی‌گرداند که با کد عددی اسکی مشخص شده مطابقت دارد و تابع CHAR کد ASCII را برای اولین کاراکتر آرگومان خود برمی‌گرداند. نحو توابع:

=CHAR(شماره)
=CODECHIM(متن)

اگر کاراکتری را به عنوان آرگومان متنی وارد می‌کنید، حتماً آن را در دو گیومه قرار دهید، در غیر این صورت، اکسل مقدار نادرستی را برمی‌گرداند.

عملکردهای SPACEBEL و PECHSIMV

اغلب فضاهای پیشرو و انتهایی مانع از مرتب شدن صحیح مقادیر در یک کاربرگ یا پایگاه داده می شوند. اگر از توابع متنی برای کار با متن کاربرگ استفاده می‌کنید، فضاهای اضافی می‌توانند مانع از کارکرد صحیح فرمول‌ها شوند. تابع TRIM فاصله های پیشرو و انتهایی را از یک رشته حذف می کند و تنها یک فاصله بین کلمات باقی می گذارد. نحو:

=فضا(متن)

عملکرد CLEAN مشابه تابع SPACE است با این تفاوت که تمام کاراکترهای غیرچاپ را حذف می کند. تابع PREPCHYMB به ویژه هنگام وارد کردن داده ها از برنامه های دیگر مفید است زیرا برخی از مقادیر وارد شده ممکن است حاوی کاراکترهای غیر چاپی باشند. این نمادها ممکن است در برگه ها به صورت مربع های کوچک یا میله های عمودی ظاهر شوند. عملکرد PRINTCHARACTERS به شما امکان می دهد نویسه های غیرچاپی را از چنین داده هایی حذف کنید. نحو:

=PECHSIMB(متن)

تابع تصادفی

تابع EXACT دو رشته متن را برای هویت کامل با در نظر گرفتن حالت حروف مقایسه می کند. تفاوت در قالب بندی نادیده گرفته می شود. نحو:

=تصادف(متن1،متن2)

اگر استدلال ها متن 1 و متن 2 تابع حساس به حروف بزرگ و کوچک، TRUE و در غیر این صورت FALSE را برمی گرداند. استدلال ها متن 1 و متن 2 باید رشته های کاراکتری باشد که در گیومه های دوتایی محصور شده اند یا به سلول هایی که حاوی متن هستند ارجاع دهند.

توابع UPPER، LOWER، و PROP

اکسل دارای سه تابع است که به شما امکان می دهد حروف را در رشته های متنی تغییر دهید: UPPER، LOWER و PROPER. تابع CAPITAL تمام حروف یک رشته متن را به حروف بزرگ و تابع LOWER همه حروف را به حروف کوچک تبدیل می کند. تابع PROPER حرف اول هر کلمه و تمام حروف بلافاصله بعد از کاراکترهای غیرحرفی را بزرگ می کند. تمام حروف دیگر به حروف کوچک تبدیل می شوند. این توابع دارای نحو زیر هستند:

=بزرگ (متن)
=پایین (متن)
=PROPNACH(متن)

هنگام کار با داده‌های موجود، اغلب اوقات موقعیتی پیش می‌آید که باید مقادیر اصلی را که توابع متن برای آنها اعمال می‌شود، تغییر دهید. می توانید تابع را در همان سلول هایی که این مقادیر در آن قرار دارند وارد کنید، زیرا فرمول های وارد شده جایگزین آنها می شوند. اما می‌توانید فرمول‌های موقتی را با یک تابع متن در سلول‌های خالی همان ردیف ایجاد کنید و نتیجه را در کلیپ بورد کپی کنید. برای جایگزینی مقادیر اصلی با مقادیر اصلاح شده، سلول های متن اصلی را انتخاب کنید، از منوی Edit گزینه Paste Special را انتخاب کنید، دکمه رادیویی Values ​​را انتخاب کنید و OK را کلیک کنید. سپس می توانید فرمول های موقت را حذف کنید.

اکسل زمانی ضروری است که نیاز به سازماندهی، پردازش و ذخیره اطلاعات زیادی دارید. این به خودکار کردن محاسبات کمک می کند و آنها را آسان تر و قابل اطمینان تر می کند. فرمول ها در اکسل به شما این امکان را می دهند که محاسبات پیچیده دلخواه را انجام دهید و فوراً نتیجه بگیرید.

نحوه نوشتن فرمول در اکسل

قبل از یادگیری این، چند اصل اساسی برای درک وجود دارد.

1. هر کدام با علامت "=" شروع می شود.
2. مقادیر و توابع سلول می توانند در محاسبات شرکت کنند.
3. عملگرها به عنوان نمادهای ریاضی آشنا برای عملیات استفاده می شوند.
4. هنگامی که یک رکورد را وارد می کنید، سلول به طور پیش فرض نتیجه محاسبه را نشان می دهد.
5. می توانید طرح را در خط بالای جدول مشاهده کنید.

هر سلول در اکسل یک واحد تقسیم ناپذیر با شناسه (آدرس) خاص خود است که با یک حرف (شماره ستون) و یک عدد (شماره ردیف) نشان داده می شود. آدرس در قسمت بالای جدول نمایش داده می شود.

بنابراین چگونه می توان یک فرمول را در اکسل ایجاد و چسباند؟ طبق الگوریتم زیر عمل کنید:

معنی تعیین

اضافه
- منها کردن
/ بخش
* ضرب

اگر به جای آدرس سلول نیاز به تعیین شماره دارید، آن را از صفحه کلید وارد کنید. برای مشخص کردن علامت منفی در فرمول اکسل، روی "-" کلیک کنید.

نحوه وارد کردن و کپی فرمول ها در اکسل

آنها همیشه پس از کلیک بر روی "=" وارد می شوند. اما اگر محاسبات مشابه زیادی وجود داشته باشد چه باید کرد؟ در این صورت می توانید یکی را مشخص کرده و سپس به سادگی آن را کپی کنید. برای انجام این کار، فرمول را وارد کنید و سپس آن را در جهت دلخواه بکشید تا ضرب شود.
نشانگر را روی سلولی که قرار است کپی شود قرار دهید و نشانگر ماوس را به گوشه پایین سمت راست (روی مربع) ببرید. باید به شکل یک صلیب ساده با اضلاع مساوی باشد.


کلیک دکمه سمت چپو بکشید.


زمانی که نیاز به توقف کپی دارید، رها کنید. در این لحظه، نتایج محاسبات ظاهر می شود.


همچنین می توانید آن را به سمت راست بکشید.
نشانگر را به سلول مجاور منتقل کنید. همان ورودی را خواهید دید، اما با آدرس های متفاوت.


هنگام کپی کردن به این روش، اگر جابجایی به سمت پایین رخ دهد، شماره ردیف افزایش می یابد، یا اگر به سمت راست منتقل شود، تعداد ستون ها افزایش می یابد. به این آدرس دهی نسبی می گویند.
بیایید ارزش مالیات بر ارزش افزوده را در جدول وارد کنیم و قیمت را با احتساب مالیات محاسبه کنیم.


قیمت با احتساب مالیات بر ارزش افزوده به عنوان قیمت*(1+ مالیات بر ارزش افزوده) محاسبه می شود. بیایید دنباله را وارد سلول اول کنیم.


بیایید سعی کنیم ورودی را کپی کنیم.


نتیجه عجیب بود.


بیایید محتویات سلول دوم را بررسی کنیم.


همانطور که می بینید، هنگام کپی، نه تنها قیمت، بلکه مالیات بر ارزش افزوده نیز جابجا شد. و ما نیاز داریم که این سلول ثابت بماند. بیایید آن را با یک پیوند مطلق لنگر بیاوریم. برای انجام این کار، نشانگر را به سلول اول منتقل کنید و روی آدرس B2 در نوار فرمول کلیک کنید.


F4 را فشار دهید. آدرس با علامت "$" ضمیمه خواهد شد. این نشانه یک سلول مطلق است.


اکنون پس از کپی کردن آدرس B2 بدون تغییر باقی می ماند.
اگر به طور تصادفی داده را در سلول اشتباهی وارد کردید، به سادگی آن را جابجا کنید. برای انجام این کار، نشانگر ماوس را روی هر مرزی حرکت دهید، صبر کنید تا ماوس مانند یک ضربدر با فلش به نظر برسد، دکمه سمت چپ را فشار دهید و بکشید. در محل مورد نظر، به سادگی دستکاری کننده را رها کنید.

استفاده از توابع برای محاسبات

اکسل تعداد زیادی توابع را ارائه می دهد که به دسته ها تقسیم می شوند. با کلیک بر روی دکمه Fx در کنار خط فرمول یا باز کردن بخش "Formulas" در نوار ابزار می توانید لیست کامل را مشاهده کنید.


بیایید در مورد برخی از عملکردها صحبت کنیم.

نحوه تنظیم فرمول های "اگر" در اکسل

این تابع به شما امکان می دهد یک شرط را تنظیم کنید و بسته به درستی یا نادرستی آن یک محاسبه را انجام دهید. به عنوان مثال، اگر تعداد کالاهای فروخته شده بیش از 4 بسته باشد، باید بیشتر خرید کنید.
برای درج نتیجه بسته به شرایط، ستون دیگری به جدول اضافه می کنیم.


در اولین سلول زیر عنوان این ستون، نشانگر را تنظیم کرده و روی آیتم "Boolean" در نوار ابزار کلیک کنید. بیایید تابع "If" را انتخاب کنیم.


مانند درج هر تابع، پنجره ای برای پر کردن آرگومان ها برای شما باز می شود.


بیایید شرایط را نشان دهیم. برای انجام این کار، روی ردیف اول کلیک کنید و اولین سلول "فروخته" را انتخاب کنید. سپس علامت ">" را قرار داده و عدد 4 را نشان دهید.


در خط دوم "خرید" را می نویسیم. این پیام برای اقلامی که به فروش رسیده اند ظاهر می شود. خط آخر را می توان خالی گذاشت زیرا اگر شرط نادرست باشد، هیچ اقدامی نداریم.


روی OK کلیک کنید و ورودی کل ستون را کپی کنید.


برای جلوگیری از نمایش "FALSE" در سلول، اجازه دهید عملکرد را دوباره باز کرده و آن را اصلاح کنیم. نشانگر را روی سلول اول قرار دهید و Fx را در کنار نوار فرمول فشار دهید. مکان نما را روی خط سوم قرار دهید و بین نقل قول ها فاصله بگذارید.


سپس اوکی کنید و دوباره کپی کنید.


اکنون می بینیم که کدام محصول را باید خریداری کرد.

متن فرمول در اکسل

این تابع به شما امکان می دهد یک قالب را برای محتویات یک سلول اعمال کنید. در این حالت، هر نوع داده ای به متن تبدیل می شود، به این معنی که نمی توان از آن برای محاسبات بیشتر استفاده کرد. بیایید یک ستون برای قالب بندی کل اضافه کنیم.


در سلول اول، یک تابع را وارد کنید (دکمه "متن" در بخش "فرمول ها").


در پنجره آرگومان‌ها، پیوندی به سلول مقدار کل نشان می‌دهیم و قالب را روی "#rub" تنظیم می‌کنیم.


روی OK کلیک کنید و کپی کنید.


اگر بخواهیم از این مقدار در محاسبات استفاده کنیم، با پیغام خطا مواجه می شویم.

"VALUE" نشان می دهد که محاسبه نمی تواند انجام شود.
نمونه هایی از فرمت ها را می توانید در اسکرین شات ببینید.

فرمول تاریخ در اکسل

اکسل گزینه های زیادی برای کار با تاریخ ها ارائه می دهد. یکی از آنها، DATE، به شما امکان می دهد یک تاریخ را از سه عدد بسازید. اگر سه ستون مختلف داشته باشید - روز، ماه، سال، راحت است.

نشانگر را در سلول اول ستون چهارم قرار دهید و یک تابع را از لیست تاریخ و زمان انتخاب کنید.

آدرس سلول ها را بر اساس آن مرتب کنید و روی OK کلیک کنید.


ورودی را کپی کنید

AutoSum در اکسل

در صورت نیاز به جمع آوری تعداد زیادی داده، اکسل تابع SUM را ارائه می دهد. به عنوان مثال، بیایید مقدار کالاهای فروخته شده را محاسبه کنیم.
نشانگر را در سلول F12 قرار دهید. کل را محاسبه خواهد کرد.


به پنل Formulas بروید و روی AutoSum کلیک کنید.


اکسل به طور خودکار نزدیکترین محدوده اعداد را برجسته می کند.


می توانید محدوده متفاوتی را انتخاب کنید. در این مثال، اکسل همه چیز را به درستی انجام داد. روی OK کلیک کنید. به محتویات سلول توجه کنید. تابع SUM به طور خودکار جایگزین شد.


هنگام درج یک محدوده، آدرس سلول اول، دو نقطه و آخرین آدرس سلول مشخص می شود. ":" به این معنی است که "همه سلول ها را بین اول و آخر بگیرید. اگر نیاز دارید چندین سلول را فهرست کنید، آدرس آنها را با یک نقطه ویرگول جدا کنید:
SUM (F5;F8;F11)

کار در اکسل با فرمول ها: مثال

نحوه ساخت فرمول در اکسل را به شما گفتیم. این دانشی است که می تواند حتی در زندگی روزمره مفید باشد. شما می توانید بودجه شخصی خود را مدیریت کرده و هزینه ها را کنترل کنید.


اسکرین شات فرمول هایی را نشان می دهد که برای محاسبه میزان درآمد و هزینه ها و همچنین محاسبه موجودی در پایان ماه وارد می شوند. اگر نمی‌خواهید همه برگه‌ها در یک صفحه باشند، برای هر ماه برگه‌ها را به کتاب کار اضافه کنید. برای انجام این کار، کافی است بر روی "+" در پایین پنجره کلیک کنید.

برای تغییر نام یک برگه، روی آن دوبار کلیک کنید و یک نام وارد کنید.

جدول را می توان حتی دقیق تر کرد.
اکسل خیلی برنامه مفید، و محاسبات در آن تقریباً امکانات نامحدودی را ارائه می دهد.

روز خوبی داشته باشی!

اغلب در اکسل باید رشته های متنی را به روشی پردازش کنید. زمانی که تعداد خطوط بیش از صد خط باشد، انجام چنین عملیاتی به صورت دستی بسیار دشوار است. برای راحتی، اکسل مجموعه خوبی از توابع را برای کار با مجموعه داده های رشته ای پیاده سازی کرده است. در این مقاله به طور مختصر توابع لازم برای کار با رشته ها را در رده "متن" شرح می دهم و برخی از آنها را با مثال در نظر می گیریم.

توابع دسته "متن".

بنابراین، اجازه دهید به عملکردهای اصلی و مفید دسته "متن" نگاه کنیم؛ می توانید با بقیه آشنا شوید.

• BATTEXT(Value) – تابعی که عددی را به نوع متن تبدیل می کند.
• DLST(Value) یک تابع کمکی است که هنگام کار با رشته ها بسیار مفید است. طول رشته را برمی‌گرداند، یعنی. تعداد کاراکترهای موجود در خط؛
• جایگزین کردن(متن قدیمی، موقعیت شروع، تعداد کاراکترها، متن جدید) - تعداد مشخص شده از یک موقعیت خاص در متن قدیمی را با یک عدد جدید جایگزین می کند.
• قابل توجه(متن) - متن را به عدد تبدیل می کند.
• LEVSIMV(رشته، تعداد کاراکترها) - یک تابع بسیار مفید، تعداد مشخص شده کاراکترها را از اولین کاراکتر باز می گرداند.
• درست(رشته، تعداد کاراکترها) - آنالوگ تابع LEVSIMV، تنها با این تفاوت که شخصیت های بازگشتی با آخرین شخصیترشته های؛
• پیدا کردن(متن برای جستجو، متنی که در آن جستجو می کنیم، موقعیت شروع) - این تابع موقعیتی را که از آنجا وقوع متن جستجو شده آغاز می شود، برمی گرداند. کاراکترها به حروف بزرگ و کوچک حساس هستند. اگر باید به حروف بزرگ و کوچک حساس نباشید، از تابع استفاده کنید جستجو کردن. فقط موقعیت اولین رخداد در رشته برگردانده می شود!
• جایگزین(متن، متن قدیمی، متن جدید، موقعیت) - یک تابع جالب، در نگاه اول شبیه به تابع جایگزین کردن، اما عملکرد جایگزیندر صورت حذف آرگومان موقعیت، قادر است تمام رخدادهای رشته را با یک زیررشته جدید جایگزین کند.
• PSTR(متن، موقعیت شروع، تعداد کاراکترها) - تابع مشابه است LEVSIMV، اما قادر به برگرداندن کاراکترها از موقعیت مشخص شده است:
• اتصال(Text1, Text 2 .... Text 30) – این عملکرد به شما امکان می دهد تا حداکثر 30 خط را متصل کنید. شما همچنین می توانید از نماد استفاده کنید " & "، مانند این خواهد بود "="Text1" & "Text2" & "Text3"";

اینها اساساً توابعی هستند که معمولاً هنگام کار با رشته ها استفاده می شوند. حالا اجازه دهید به چند مثال نگاه کنیم که نحوه عملکرد برخی از توابع را نشان می دهد.

مثال 1
با توجه به مجموعه ای از خطوط:

استخراج تاریخ، شماره فاکتور از این خطوط و همچنین اضافه کردن فیلد ماه برای فیلتر کردن خطوط به ماه ضروری است.

بیایید شماره فاکتورها را در ستون B استخراج کنیم. برای انجام این کار، به اصطلاح نماد یا کلمه کلید را پیدا می کنیم. در مثال ما، می توانید ببینید که قبل از هر شماره فاکتور "خیر" وجود دارد و طول شماره فاکتور 6 کاراکتر است. بیایید از توابع FIND و PSTR استفاده کنیم. فرمول زیر را در سلول B2 می نویسیم:

= PSTR(A2; پیدا کردن("№";A2)+1;6)

بیایید به فرمول نگاه کنیم. از خط A2 از موقعیت بعدی بعد از علامت "نه" یافت شده، 6 کاراکتر عددی را استخراج می کنیم.

حالا بیایید تاریخ را استخراج کنیم. اینجا همه چیز ساده است. تاریخ در انتهای خط قرار دارد و 8 کاراکتر را شامل می شود. فرمول C2 به شرح زیر است:

= درست(A2;8)

اما تاریخ استخراج شده یک رشته خواهد بود، برای تبدیل آن به تاریخ لازم است بعد از استخراج، متن را به عدد تبدیل کنید:

= قابل توجه(درست(A2;8))

و سپس فرمت نمایش را در سلول تنظیم کنید، همانطور که در مقاله "".

و در آخر، برای راحتی فیلتر کردن ردیف‌ها، ستون ماه را معرفی می‌کنیم که از تاریخ دریافت می‌کنیم. فقط برای ایجاد یک ماه باید روز را حذف کرده و "01" را جایگزین آن کنیم. فرمول D2:

= قابل توجه(اتصال("01"; درست(A2;6))) یا = قابل توجه("01"& درست(A2;6))

تنظیم فرمت سلول " MMMM YYYY" نتیجه:

مثال 2
در صف " نمونه ای از کار با رشته ها در اکسل"همه فاصله ها باید با علامت "_" جایگزین شوند و قبل از کلمه "Excel" "MS" را اضافه کنید.

فرمول به صورت زیر خواهد بود:

=جایگزین(جایگزین کردن(A1; جستجو کردن("excel";A1);0;"MS");" ";"_")

برای درک این فرمول، آن را به سه ستون تقسیم کنید. با SEARCH شروع کنید، آخرین مورد SUBSTITUTE خواهد بود.

همه. اگر سوالی دارید، در پرسیدن دریغ نکنید

اکسل برای کار با حروف بزرگ و کوچک در متن (بزرگ و کوچک) به کاربران خود 3 عملکرد ارائه می دهد. این توابع متنی حروف را بزرگ و کوچک می کنند یا فقط حرف اول یک کلمه را به بزرگ تغییر می دهند.

فرمول هایی با توابع متن اکسل

ابتدا به 3 تابع متن اکسل به عنوان مثال نگاه می کنیم:

1. CAPITAL - این تابع متن تمام حروف یک کلمه را به حروف بزرگ تغییر می دهد.
2. LOWER - این تابع تمام کاراکترهای متن را به حروف کوچک و کوچک تبدیل می کند.
3. PROPNACH - تابع فقط حرف اول هر کلمه را به حرف بزرگ تغییر می دهد.

همانطور که در مثال در شکل مشاهده می کنید، این توابع در آرگومان های خود به غیر از داده های متن اصلی نیازی ندارند که باید مطابق با نیاز کاربر تبدیل شوند.

علیرغم چنین انتخاب گسترده ای از توابع، اکسل هنوز به تابعی نیاز دارد که بتواند حرف اول را با یک بزرگ تنها برای اولین کلمه در یک جمله و نه در هر کلمه جایگزین کند. با این حال، برای حل این مشکل، می توانید فرمول سفارشی خود را با استفاده از همان و سایر توابع متن اکسل ایجاد کنید:

برای حل این مشکل محبوب، باید از توابع متنی اضافی اکسل در فرمول استفاده کنید: LEFT، RIGHT و LENGTH.



اصل عملکرد فرمول جایگزینی حرف اول در یک جمله

اگر به نحو فرمول بالا دقت کنید، به راحتی متوجه می شوید که از دو قسمت تشکیل شده است که توسط عملگر & به یکدیگر متصل شده اند.

سمت چپ فرمول از تابع LEFT اضافی استفاده می کند:

هدف این قسمت از فرمول تغییر حرف اول به حرف بزرگ در رشته متن اصلی سلول A1 است. به لطف عملکرد LEFT، می توانید تعداد مشخصی کاراکتر را از سمت چپ متن دریافت کنید. تابع نیاز به 2 آرگومان برای پر کردن دارد:

1. متن - پیوندی به یک سلول با متن منبع.
2. Number_characters – تعداد کاراکترهایی که از سمت چپ (از ابتدا) متن مبدأ برگردانده شده است.

در این مثال، شما فقط باید 1 کاراکتر اول را از رشته متن اصلی در سلول A1 دریافت کنید. سپس کاراکتر به دست آمده به یک حرف بزرگ بزرگ تبدیل می شود.

سمت راست فرمول بعد از عملگر & در اصل بسیار شبیه به سمت چپ است، فقط مشکل متفاوتی را حل می کند. وظیفه آن تبدیل تمام کاراکترهای متن به حروف کوچک است. اما این کار باید انجام شود تا حرف بزرگ اول که سمت چپ فرمول مسئول آن است تغییر نکند. به جای تابع LEFT در سمت راست فرمول، تابع RIGHT استفاده می شود:

تابع متن RIGHT با تابع LEFT برعکس عمل می کند. همچنین نیاز به پاک کردن دو آرگومان دارد: متن منبع و تعداد کاراکترها. اما تعداد معینی از حروف به دست آمده از سمت راست متن مبدأ را برمی گرداند. با این حال، در این مورد، ما نمی توانیم یک مقدار ثابت را به عنوان آرگومان دوم تعیین کنیم. از این گذشته، ما از قبل تعداد کاراکترهای متن منبع را نمی دانیم. علاوه بر این، طول رشته های متن منبع مختلف ممکن است متفاوت باشد. بنابراین، ابتدا باید طول خط متن را محاسبه کرده و -1 را از مقدار عددی حاصل کم کنیم تا اولین حرف بزرگ در خط تغییر نکند. از این گذشته ، حرف اول توسط سمت چپ فرمول پردازش می شود و قبلاً برای مطابقت با نیازهای کاربر تغییر شکل داده است. بنابراین، نباید تحت تأثیر هیچ تابعی از سمت راست فرمول قرار گیرد.

برای محاسبه خودکار طول متن منبع، از تابع متن اکسل - DLST (مخفف طول خط) استفاده کنید. این تابع فقط به یک آرگومان نیاز دارد که پر شود - پیوندی به متن منبع. در نتیجه محاسبه، یک مقدار عددی برمی گرداند، بنابراین بعد از تابع =LENGTH(A1) -1 را کم می کنیم. که این فرصت را به ما می دهد که اولین حرف بزرگ سمت راست فرمول را لمس نکنیم. در نتیجه، تابع RIGHT یک رشته متن بدون یک کاراکتر اول را به تابع LOWER برمی‌گرداند که تمام کاراکترهای متن را به حروف کوچک کوچک تبدیل می‌کند.

در نتیجه اتصال هر دو قسمت فرمول با عملگر & یک جمله متنی زیبا به دست می آید که طبق قوانین با اولین حرف بزرگ شروع می شود. و بقیه حروف تا آخر جمله کوچک هستند. صرف نظر از طول متن، با استفاده از همین فرمول به نتیجه صحیح می رسیم.