تفریق در اکسل

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

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

فرمول تفریق در اکسل (فرمول منها)

به خاطر روشن بودن مطلب، تابع تفریق در اکسل وجود ندارد. برای انجام یک عملیات تفریق ساده، از علامت منها (-) استفاده می‌کنید.

فرمول اساسی تفریق در اکسل به همین سادگی است:

=number1-number2

به عنوان مثال، برای تفریق ۱۰ از ۱۰۰، معادله‌ی زیر را بنویسید و نتیجه‌ی ۹۰ را بدست می‌آورید:

= 100-10

برای وارد کردن فرمول در ورک‌شیت خود، موارد زیر را انجام دهید:

1. در سلولی که می‌خواهید نتیجه ظاهر شود، علامت مساوی (=) را تایپ کنید.
2. شماره‌ی اول را وارد کرده و بعد از آن علامت منها و بعد شماره‌ی دوم را وارد کنید.
3. فرمول را با فشار دادن کلید Enter تکمیل کنید.

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

به عنوان مثال، برای کم کردن چند عدد از ۱۰۰، تمام آن اعداد را که با علامت منها جدا شده، تایپ کنید:

= 100-10-20-30

برای نشان دادن این‌که کدام قسمت از فرمول ابتدا باید محاسبه شود، از پرانتز استفاده کنید. مثلاً:

= (100-10) / (80-20)

تصویر زیر چند فرمول دیگر برای کم کردن اعداد در اکسل نشان می‌دهد:

نحوه‌ی تفریق سلول‌ها در اکسل

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

=cell_1 - cell_2

به عنوان مثال، برای کم کردن عدد سلول B2 از عدد سلول A2، از این فرمول استفاده کنید:

=A2-B2

لزوماً نیازی به نوشتن ارجاع سلولی به صورت دستی ندارید، می‌توانید به سرعت با انتخاب سلول‌های مربوطه آن‌ها را به فرمول اضافه کنید. شیوه‌ی آن در این‌جا آمده‌است:

1. در سلولی که می‌خواهید خروجی تفریق را داشته باشید، علامت مساوی (=) را تایپ کنید تا فرمولتان شروع شود.
2. روی سلول عددی که قرار است عدد دیگری از آن کم شود کلیک کنید. ارجاع آن به صورت خودکار به فرمول اضافه می‌شود (A2).
3. یک علامت منها (-) تایپ کنید.
4. بر روی سلول حاوی عددی که کم می‌شود کلیک کنید تا ارجاع آن به فرمول (B2) اضافه شود.
5. برای تکمیل فرمول، کلید Enter را فشار دهید.

و نتیجه‌ای مشابه این خواهید داشت:

نحوه‌ی تفریق چندین سلول از یک سلول در اکسل

برای تفریق چند سلول از یک سلول، می‌توانید از یکی از روش‌های زیر استفاده کنید.

روش ۱. علامت منها

به سادگی چندین ارجاع سلول جداشده توسط یک علامت منها را — همانند هنگام کم کردن چندین عدد — تایپ کنید.

به عنوان مثال، برای تفریق سلول‌های B2:B6 از B1، فرمول را به این روش بسازید:

=B1-B2-B3-B4-B5-B6

روش ۲. تابع SUM

برای این‌که فرمول شما فشرده‌تر شود، با استفاده از تابع SUM زیرشاخه‌ها (B2:B6) را جمع کرده و سپس مقدار را از عدد اول (B1) کم کنید.

=B1-SUM(B2:B6)

روش ۳. جمع اعداد منفی

همان‌طور که شاید از ریاضیات به یاد داشته باشید، تفریق یک عدد منفی همان اضافه کردن آن است؛ بنابراین، تمام اعدادی را که می‌خواهید تفریق کنید منفی کرده (برای این کار، صرفاً یک علامت منها را قبل از آن عدد تایپ کنید)، و سپس از تابع SUM برای جمع اعداد منفی استفاده کنید:

=SUM(B1:B6)

نحوه‌ی تفریق ستون‌ها در اکسل

برای تفریق ۲ ستون به صورت سطر به سطر، یک فرمول تفریق برای بالاترین سلول بنویسید و سپس دسته‌ی پر کردن را بکشید یا روی علامت به‌علاوه دو بار کلیک کنید تا فرمول در کل ستون کپی شود.

به عنوان نمونه، بیایید اعداد ستون C را از اعداد موجود در ستون B تفریق کنیم، با شروع از سطر ۲:

=B2-C2

با توجه به استفاده از ارجاع نسبی سلول، فرمول به درستی برای هر سطر تنظیم می‌شود:

تفریق اعداد یک ستون از یک عدد

برای تفریق یک عدد از یک محدوده از سلول‌ها، آن عدد را در بعضی از سلول‌ها وارد کنید (در این مثال F1)، و سلول F۱ را از اولین سلول محدوده تفریق کنید:

=B2-$F$1

نکته‌ی اصلی، قفل کردن ارجاع سلول با علامت $ است. این کار یک ارجاعی سلولی مطلق ایجاد می‌کند که تغییر نمی‌کند و مهم نیست که از کجا فرمول کپی شده‌است. اولین ارجاع (B2) قفل نشده‌است، بنابراین برای هر سطر تغییر می‌کند.

در نتیجه، در سلول C3 فرمول =B3-$F$1 را خواهید داشت. در سلول C4 فرمول به =B4-$F$1 تغییر می‌کند و … .

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

=B2-150

نحوه‌ی تفریق درصد در اکسل

اگر می‌خواهید یک درصد را از دیگری تفریق کنید، همان فرمول تفریق که در حال حاضر معرفی شده‌است، در این‌جا نیز کار می‌کند. مثلاً:

=100%-30%

یا می‌توانید درصدها را در سلول‌های جداگانه وارد کنید و آن سلول‌ها را کم کنید:

=A2-B2

اگر می‌خواهید درصدی را از عدد کم کنید، یعنی عدد را بر حسب درصد کاهش دهید، از این فرمول استفاده کنید:

=Number * (1 - %)

به عنوان مثال، در این‌جا بدین صورت می‌توانید عدد واقع در A2 را به میزان ۳۰٪ کاهش دهید:

=A2*(1-30%)

یا می‌توانید درصد را در یک سلول جداگانه وارد کنید (مثلاً B2) و با استفاده از یک ارجاع مطلق به آن سلول ارجاع دهید:

=A2*(1-$B$2)

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

نحوه‌ی تفریق تاریخ در اکسل

ساده‌ترین راه برای تفریق تاریخ در اکسل، وارد کردن آن‌ها در سلول‌های جداگانه و تفریق یک سلول از سلول دیگر است:

=End_date - Start_date

همچنین می‌توانید با کمک توابع DATE یا DATEVALUE، تاریخ را مستقیماً در فرمول خود اعمال کنید. مثلاً:

=DATE(2018,2,1)-DATE(2018,1,1)

=DATEVALUE(“2/1/2018”)-DATEVALUE(“1/1/2018”)

نحوه‌ی تفریق زمان در اکسل

فرمول تفریق زمان در اکسل به روشی مشابه ساخته می‌شود:

=End_time - Start_time

به عنوان مثال، برای بدست آوردن تفاوت بین زمان‌های A2 و B2، از این فرمول استفاده کنید:

=A2-B2

برای نمایش صحیح نتیجه، حتماً فرمت Time را در سلول فرمول اعمال کنید:

با اعمال مقادیر زمانی به طور مستقیم در فرمول می‌توانید به همان نتیجه برسید. برای این‌که اکسل به درستی زمان‌ها را بفهمد، از تابع TIMEVALUE استفاده کنید:

=TIMEVALUE(“4:30 PM”)-TIMEVALUE(“12:00 PM”)

نحوه‌ی تفریق ماتریس در اکسل

فرض کنید شما دو مجموعه مقادیر (ماتریس) دارید و می‌خواهید عناصر مربوط به مجموعه‌هایی را که در تصویر زیر نشان داده شده‌است، کم کنید:

در این‌جا نحوه‌ی انجام این کار با تنها یک فرمول آمده است:

1. محدوده‌ای از سلول‌های خالی را انتخاب کنید که دارای همان تعداد ردیف و ستون با ماتریس شما باشند.
2. در محدوده‌ی انتخاب‌شده یا در نوار فرمول، فرمول تفریق ماتریس را تایپ کنید:
   =(A2:C4)-(E2:G4)
3. برای ایجاد فرمول آرایه‌ای Ctrl+Shift+Enter را بزنید.
   

نتایج تفریق در محدوده‌ی انتخاب شده ظاهر می‌شود. اگر روی هر سلول در آرایه‌ی نتیجه کلیک کنید و به نوار فرمول نگاه کنید، خواهید دید که این فرمول توسط {} احاطه شده‌است، که نشانگر دیداری فرمول‌های آرایه در اکسل است:

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

در این مثال می‌توانیم فرمول زیر را در C7 قرار داده و آن را به ۲ ستون و ۲ ردیف بعدی آن را بکشیم.

=A2-C4

با توجه به استفاده از ارجاع نسبی سلول (بدون علامت $)، فرمول بر اساس موقعیت نسبی ستون و سطر در جایی که کپی می‌شود، تنظیم می‌شود:

تفریق متن یک سلول از سلول دیگر

بسته به این‌که می‌خواهید با کارکترهای بزرگ و کوچک به صورت یکسان یا متفاوت رفتار کنید، از یکی از فرمول‌های زیر استفاده کنید.

فرمول حساس به بزرگی و کوچکی برای تفریق متن

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

TRIM(SUBSTITUTE(full_text, text_to_subtract,""))

با متن کامل در A2 و زیرلایه‌ای که می‌خواهید در B2 حذف کنید، فرمول به شرح زیر است:

=TRIM(SUBSTITUTE(A2,B2,””))

همان‌طور که مشاهده می‌کنید، فرمول برای تفریق یک زیررشته از ابتدا و انتهای یک رشته به خوبی کار می‌کند:

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

به عنوان نمونه، بیایید کلمه “Apples” را از سلول A2 حذف کنیم:

=TRIM(SUBSTITUTE(A2,”Apples”,””))

برای کار کردن با فرمول، حتما متن را دقیقاً تایپ کنید، من جمله بزرگی و کوچکی کاراکتر.

فرمول غیر حساس به بزرگی و کوچکی برای تفریق متن

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

• تابع SEARCH موقعیت اولین کاراکتر را برای تفریق در رشته‌ی اصلی برمی‌گرداند، بدون توجه به کوچکی و بزرگی متن. این عدد به آرگومان start_num از تابع REPLACE می‌رود.
• تابع LEN طول زیررشته را که باید حذف شود پیدا می‌کند. این عدد به آرگومان num_chars از تابع REPLACE می‌رود.

فرمول کامل به شرح زیر است:

TRIM(REPLACE(full_text, SEARCH(text_to_subtract, full_text), LEN(text_to_subtract),""))

با اعمال آن بر مجموعه داده‌های نمونه، فرمول شکل زیر را می‌گیرد:

=TRIM(REPLACE(A2,SEARCH(B2,A2),LEN(B2),””))

در این‌جا A۲ متن اصلی است و B۲ زیررشته‌ی حذف‌شده است.

تفریق یک لیست از لیست دیگر

به فرض، شما دو لیست از مقادیر متنی در ستون‌های مختلف دارید که یک لیست کوچک زیرمجموعه‌ی یک لیست بزرگ‌تر است. سؤال این است: چگونه عناصر لیست کوچک‌تر را از لیست بزرگتر حذف می‌کنید؟

از نظر ریاضی، کار با کم کردن لیست کوچک‌تر از لیست بزرگ‌تر قابل انجام است:

لیست بزرگ‌تر: {“A”, “B”, “C”, “D”}

لیست کوچک‌تر: {“A”, “C”}

نتیجه: {“A”, “D”}

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

=IF(COUNTIF($B:$B, $A2)=0, “Unique”, “”)

در این‌جا A2 اولین سلول لیست بزرگ‌تر است و B ستونی است که لیست کوچک‌تر را در خود جای می‌دهد.

در نتیجه، مقادیر منحصربه‌فرد در لیست بزرگ‌تر بر این اساس برچسب‌گذاری می‌شوند:

و اکنون می‌توانید مقادیر منحصربه‌فرد را فیلتر کرده و در هر کجا که می‌خواهید کپی کنید.

به این ترتیب اعداد و سلول را در اکسل تفریق می‌کنید. برای مشاهده‌ی دقیق‌تر مثال‌هایمان، می‌توانید فرمول تفریق ما را در ورک‌بوک اکسل بارگیری کنید. از شما متشکریم که این مطلب را خواندید و امیدوارم که دوباره شما را در وب‌سایتمان ببینم.