ترفند

بهترین توابع کاربردی اکسل برای تحلیل داده

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

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

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

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

استفاده از تابع IF همراه با AND، OR و NOT

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

توابع AND، OR و NOT به شما کمک می‌کنند شرایط خاصی را بررسی کنید. در مثال زیر، سه شرط برای هر متقاضی در نظر گرفته‌ایم:

  1. آیا متقاضی بالای 18 سال است و گواهی طلایی دارد؟ (ستون F)
  2. آیا یا گواهینامه کامل رانندگی دارند یا قادر به کار در خارج از کشور هستند؟ (ستون G)
  3. آیا سن آن‌ها زیر 50 سال است؟ (ستون H)

یک نمونه جدول اکسل

برای بررسی شرط اول، در سلول F2 فرمول زیر را وارد می‌کنیم:

=AND(B2>18,D2="Gold")

این فرمول بررسی می‌کند که آیا مقدار B2 بزرگ‌تر از 18 است و مقدار D2 برابر با “Gold” است یا نه.

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

=OR(C2="Full",E2="YES")

این فرمول بررسی می‌کند که آیا مقدار C2 برابر با “Full” یا مقدار E2 برابر با “YES” است یا نه.

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

=NOT(B2>50)

این فرمول بررسی می‌کند که آیا مقدار B2 بزرگ‌تر از 50 نیست.

هنگام ایجاد آرگومان های منطقی، تمام متن باید در داخل نقل قول های دوگانه “” قرار گیرد.

سپس، از قابلیت AutoFill اکسل برای کپی‌کردن فرمول‌ها به ردیف‌های باقی‌مانده استفاده می‌کنم. توجه کنید که هر نتیجه یا TRUE (درست) یا FALSE (نادرست) خواهد بود، بسته به اینکه آیا شرایط تعیین‌شده برقرار هستند یا خیر. هرچند این نتایج مفید هستند، اما داشتن یک عبارت مشخص، مانند “Senior” یا “Junior”، می‌تواند بسیار کاربردی‌تر باشد. اینجاست که تابع IF به کار می‌آید و با توجه به شرایط تعیین‌شده، یک مقدار خاص را برمی‌گرداند.

این بار می‌خواهم برای هر متقاضی نتایج زیر را به‌دست آورم:

  • اگر بالای 18 سال باشند و گواهی طلایی داشته باشند، به عنوان عضو ارشد (Senior) برچسب‌گذاری شوند.
  • اگر یا گواهینامه رانندگی کامل داشته باشند یا توانایی کار در خارج از کشور را داشته باشند، به عنوان کسی که قادر به سفر است برچسب‌گذاری شوند.
  • اگر زیر 50 سال باشند، نشان داده شود که واجد شرایط برای دوره کارآموزی هستند.

بنابراین، در سلول F2 فرمول زیر را تایپ می‌کنم:

=IF(AND(B2>18,D2="Gold"),"Senior","Junior")

این فرمول بررسی می‌کند که آیا مقدار B2 بزرگ‌تر از 18 است و مقدار D2 برابر با “Gold” است یا خیر، و سپس “Senior” یا “Junior” را بازمی‌گرداند.

در سلول G2 فرمول زیر را وارد می‌کنم:

=IF(OR(C2="Full",E2="YES"),"Can travel","Cannot travel")

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

=IF(NOT(B2>50),"Eligible for scholarship","No scholarship")

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

سعی کنید از IF، AND و OR با چک باکس در اکسل برای پیگیری پیشرفت کار استفاده کنید. همچنین می‌توانید تابع XOR را انتخاب کنید، که در ساده‌ترین شکل آن، اگر فقط یکی درست باشد، TRUE یا اگر هر دو مقدار TRUE باشد، FALSE را برمی‌گرداند.

استفاده از تابع IFERROR

من از تابع IFERROR برای مرتب نگه داشتن صفحات گسترده اکسل خود استفاده می‌کنم. هیچ‌کس نمی‌خواهد جدولی پر از خطاهایی مانند #N/A، #VALUE!، #REF!، #DIV/0!، #NUM!، #NAME? و #NULL! داشته باشد. تابع IFERROR به جلوگیری از بروز این خطاها کمک می‌کند.

در این مثال، من نسبت گل به بازی ده بازیکن را پیگیری می‌کنم. برای این کار، فرمول زیر را در سلول D2 تایپ کردم:

=SUM(C2/B2)

سپس این محاسبه را به ستون D کپی کردم. با این حال، به دلیل اینکه بازیکن C هیچ بازی‌ای انجام نداده است، تقسیم صفر بر صفر انجام می‌شود و نتیجه به صورت #DIV/0! ظاهر می‌شود.

تقسیم صفر بر صفر در اکسل

برای مرتب نگه داشتن محاسبات و جلوگیری از نمایش خطاها، می‌توانم همه محاسبات موجود در ستون D را درون تابع IFERROR قرار دهم. ساختار این تابع به شکل زیر است:

=IFERROR(x,y)

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

=SUM(C2/B2)

درون تابع IFERROR قرار دهید تا خطاهایی مانند #DIV/0! را مدیریت کنید، باید به شکل زیر عمل کنید:

=IFERROR(SUM(C2/B2), "Error")
اگر y را بعد از کاما خالی بگذارید، اکسل برای محاسبه خطا دار را 0 برمی گرداند.

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

=IFERROR(SUM(C2/B2),"-")

سپس این فرمول را به بقیه ستون D کپی می‌کنم. در این حالت، محاسبه SUM(C2/B2) همچنان وجود دارد، اما درون تابع IFERROR قرار گرفته است. هر زمان که خطایی در محاسبات رخ دهد، به جای نمایش پیغام خطا مانند #DIV/0!، یک خط تیره (“-“) نمایش داده می‌شود که از نظر بصری بسیار مرتب‌تر به نظر می‌رسد.

به خاطر داشته باشید که استفاده از IFERROR برای پنهان کردن خطاها در صفحه گسترده می تواند شناسایی خطاهای محاسباتی را دشوارتر کند. به همین دلیل است که من فقط تمایل دارم از آن در سناریوهایی استفاده کنم که #DIV/0 هستند! در غیر این صورت باید نمایش داده شوند تا بتوانید خطا را شناسایی و رفع کنید.
شاید این مقاله نیز برای شما کاربردی باشد: آموزش تابع IFERROR در اکسل به همراه مثال

تابع is به همراه if

برای استفاده از توابع IS در ترکیب با تابع IF، ابتدا باید بدانیم هرکدام از این توابع چه وظیفه‌ای دارند. توابع IS در اکسل به منظور بررسی وضعیت یا نوع داده‌ای خاص در یک سلول به کار می‌روند. این توابع زمانی که با IF ترکیب می‌شوند، می‌توانند اطلاعات بسیار مهمی درباره داده‌ها و نتایج مورد انتظار به شما بدهند.

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

=IS[TYPE](a)

که در آن [TYPE] نوع تابع IS است که می‌خواهید استفاده کنید و a مرجع سلول یا مقداری است که باید ارزیابی شود.

مثال‌هایی از توابع IS

  • ISBLANK: بررسی می‌کند که آیا سلول خالی است یا نه.
  • ISERROR: بررسی می‌کند که آیا مقدار سلول یک خطا است (مانند #N/A، #VALUE! و غیره).
  • ISNUMBER: بررسی می‌کند که آیا مقدار یک عدد است.
  • ISTEXT: بررسی می‌کند که آیا مقدار سلول یک متن است.

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

=IF(ISBLANK(A1),"سلول خالی است","سلول پر است")

این فرمول بررسی می‌کند که آیا سلول A1 خالی است یا نه. اگر خالی باشد، نتیجه “سلول خالی است” خواهد بود و اگر خالی نباشد، نتیجه “سلول پر است” نمایش داده می‌شود.

بیایید نگاهی به نحوه استفاده از توابع IS در یک جدول اکسل بیندازیم. فرض کنید داده‌های زیر را در ستون A دارید و می‌خواهید با استفاده از توابع IS مقادیر این سلول‌ها را بررسی کنید:

مثال‌هایی از توابع IS در اکسل

برای استفاده از توابع IS همراه با IF در اکسل، باید تابع IS را درون IF قرار دهید. به‌طور مثال، در سلول B2، فرمول زیر را برای بررسی اینکه آیا سلول خالی است استفاده کردم:

=IF(ISBLANK(B2),"نیاز به اطلاعات",B2*7)

در اینجا از تابع ISBLANK استفاده شده است تا بررسی کنیم که آیا سلول B2 خالی است یا خیر. اگر خالی باشد، نتیجه “نیاز به اطلاعات” نمایش داده می‌شود و در غیر این صورت، مقدار سلول B2 در 7 ضرب می‌شود.

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

=IF(ISNUMBER(A2), "عدد است", "عدد نیست")

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

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

در نتیجه، استفاده از بهترین توابع کاربردی اکسل مانند IF، AND، OR، NOT، IFERROR، و IS برای مدیریت و تحلیل داده‌ها به کاربران این نرم‌افزار کمک می‌کند تا بهره‌وری بیشتری در انجام وظایف روزمره داشته باشند. این توابع نه‌تنها در رفع خطاها و سازماندهی داده‌ها مؤثر هستند، بلکه می‌توانند با خودکارسازی فرایندها، دقت و سرعت تصمیم‌گیری را بهبود بخشند. به‌خصوص زمانی که با حجم زیادی از داده‌ها سر و کار دارید، استفاده صحیح از این توابع، شما را در مرتب‌سازی اطلاعات و تحلیل آن‌ها با دقت و سرعت بیشتر یاری می‌دهد.

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

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

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

برای امتیاز به این نوشته کلیک کنید!
[کل: 1 میانگین: 5]
هاست وردپرس هاست وردپرس

سعید زارعین

سعید هستم 27 ساله، یک عدد تولید محتوا(ئر) خلاق :)))

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

دکمه بازگشت به بالا