۱۰ فرمول کاربردی اکسل برای تجزیه و تحلیل داده ها

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

توجه : برای افراد مبتدی که نمی دانند ، ، کارکردهای ذکر شده در این پست باید در نوار فرمول صفحه گسترده اکسل حاوی داده یا درون سلول که در آن نتیجه می خواهید اضافه شود.

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

۱. CONCATENATE

CONCATENATE یکی از مهمترین کارکردها و از فرمول های کاربردی اکسل برای تجزیه و تحلیل داده ها است زیرا به شما امکان می دهد متن ، شماره ، تاریخ و غیره را از چندین سلول به یک دیگر ترکیب کنید. این عملکرد به ویژه برای ترکیب داده ها از سلولهای مختلف به یک سلول واحد مفید است. به عنوان مثال ، برای ایجاد پارامترهای ردیابی برای کمپین های بازاریابی ، ساختن نمایش داده های API ، اضافه کردن متن به قالب شماره و چندین مورد دیگر مفید است.

در مثال بالا ، ماه و فروش را با هم در یک ستون می خواستم. برای همین ، من از فرمول  CONCATENATE (A2 ، B2)= در سلول C2 استفاده کرده ام تا در نتیجه Jan$700 ژانویه بدست آورم.

فرمول : = CONCATENATE ( سلول هایی که می خواهید ترکیب کنید )

۲. LEN

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

در مثال بالا می خواستم ارقام را برای ستون C4 Views ، حساب کنم. برای این کار ، از فرمول  LEN (C4)= در سلول D2 استفاده کردم تا نتیجه ۵ شود.

فرمول: = LEN ( سلول )

۳. VLOOKUP

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

در مثال بالا می خواستم تعداد بازدیدها را در یک ماه خاص جستجو کنم. برای همین ، از فرمول VLOOKUP(“Jun”, A2:C13, 3)= در سلول G4 استفاده کردم و به عنوان نتیجه ۷۴۹۹۲ گرفتم. در اینجا ، “Jun” مقدار جستجو است ، A2: C13 جداولی هستند که در آن به دنبال “Jun” هستم و ۳ شماره ستونی است که در آن فرمول نمایش های مربوطه را برای ماه ژوئن پیدا می کند.

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

فرمول: = VLOOKUP ( lookup_value ، Table_array ، col_index_num ، [range_lookup] )

• توصیه می شود : استفاده از فرمول VLOOKUP در اکسل

۴. INDEX / MATCH

دقیقاً مانند عملکرد VLOOKUP ، عملکرد INDEX و MATCH برای جستجوی داده های خاص بر اساس یک مقدار ورودی مفید هستند. INDEX و MATCH ، هنگام استفاده از یکدیگر ، می توانند بر محدودیتهای VLOOKUP در ارائه نتایج اشتباه غلبه کنند. بنابراین ، هنگامی که این دو عملکرد را با هم ترکیب می کنید ، می توانند مرجع داده ها را مشخص کرده و مقدار را در یک آرایه یک بعدی جستجو کنند. که مختصات داده را به عنوان عدد برمی گرداند.

در مثال بالا می خواستم تعداد Views را در ژانویه جستجو کنم. برای آن ، من از فرمول  INDEX (A2: C13 ، MATCH (“Jan” ، A2: A13،۰) ، ۳)= استفاده کردم. در اینجا ، A2: C13 ستونی از داده هایی است که می خواهم فرمول را برگردانیم ، “Jan” مقداری است که می خواهم با آن مطابقت داشته باشم ، A2: A13 ستونی است که در آن فرمول “Jan” را پیدا می کند و ۰ نشان می دهد که من می خواهم فرمول برای پیدا کردن یک مطابقت دقیق برای مقدار باشد.

اگر می خواهید یک مسابقه تقریبی پیدا کنید ، باید ۰ را با ۱ یا -۱ جایگزین کنید. بدین ترتیب که ۱ بیشترین مقدار کمتر یا مساوی با مقدار جستجو پیدا کند و -۱ کوچکترین مقدار کمتر یا مساوی با مقدار جستجو پیدا کند. توجه داشته باشید که اگر از ۰ ، ۱ یا -۱ استفاده نکنید ، فرمول ۱ را استفاده می کند.

حال اگر نمی خواهید نام ماه را کدگذاری کنید ، می توانید آن را با شماره سلول جایگزین کنید. بنابراین می توانیم “Jan” را در فرمول ذکر شده در بالا با F3 یا A2 جایگزین کنیم تا نتیجه ی مشابه حاصل شود.

فرمول: = INDEX ( ستونی از داده هایی که می خواهید برگردانید ، MATCH (نقطه مشترک داده ای که می خواهید با آن مطابقت داشته باشید ، ستونی از منبع دیگر داده ای که دارای داده مشترک است ،))

۵. MINIFS / MAXIFS

MINIFS و MAXIFS بسیار شبیه به توابع MIN و MAX هستند ، به جز این واقعیت که به شما امکان می دهد مجموعه ای از حداقل / حداکثر مقادیر را بگیرید و آنها را با معیارهای خاصی مطابقت دهید. بنابراین در اصل ، تابع به دنبال حداقل / حداکثر مقادیر بوده و آن را با معیارهای ورودی مطابقت می دهد.

در مثال بالا می خواستم حداقل نمرات را بر اساس جنسیت دانش آموز پیدا کنم. برای آن ، من از فرمول  MINIFS (C2: C10 ، B2: B10 ، “M”)= استفاده کردم و نتیجه آن را ۲۷ گرفتم . در اینجا C2: C10 ستونی است که در آن فرمول به دنبال امتیازات خواهد بود ، B2: B10  ستونی که در آن فرمول معیارها (جنس) را جستجو می کند ، و “M” معیارهاست.

به همین ترتیب ، برای حداکثر نمرات ، از فرمول MAXIFS (C2: C10 ، B2: B10 ، “M”)= استفاده کردم و نتیجه ۱۰۰ را بدست آوردم.

فرمول: MINIFS: = MINIFS ( min_range ، معیار_های_1 ، معیارهای ۱ ، … )

فرمول: MAXIFS: = MAXIFS ( max_range ، معیار_های_1 ، معیارهای ۱ ، … )

۶. AVERAGEIFS

عملکرد AVERAGEIFS به شما امکان می دهد به طور متوسط ​​برای یک مجموعه داده خاص بر اساس یک یا چند معیار پیدا کنید. در حین استفاده از این عملکرد ، باید در نظر داشته باشید که هر معیار و دامنه متوسط ​​می تواند متفاوت باشد. با این حال ، در عملکرد AVERAGEIF ، دامنه معیارها و دامنه مبلغ باید دامنه اندازه یکسانی داشته باشند. به تفاوت difference  و singular در بین این توابع توجه کنید؟ خوب ، اینجاست که باید مراقب باشید.

در این مثال می خواستم نمره متوسط ​​را براساس جنسیت دانش آموزان پیدا کنم. برای همین من از فرمول ،  AVERAGEIFS (C2: C10 ، B2: B10 ، “M”)= استفاده کردم و به عنوان نتیجه ۵۶.۸ گرفتم. در اینجا ، C2: C10 دامنه ای است که در آن فرمول به دنبال میانگین خواهد بود ، B2: B10 دامنه معیارها ، و “M” معیارها است.

فرمول: = AVERAGEIFS (متوسط_رنگ ، معیار_های_1 ، معیارهای ۱ ، … )

۷. COUNTIFS

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

در این مثال ، می خواستم تعداد دانش آموزان پسر یا زن که نمره قبولی گرفتند را پیدا کنم (یعنی  ۴۰=<) ، برای همین من از فرمول COUNTIFS استفاده کردم (B2: B10 ، “M” ، C2: C10 ، “> = 40”). در اینجا ، B2: B10 دامنه ای است که در آن فرمول معیارهای اول (جنسیت) را جستجو می کند ، “M” معیارهای اول ، C2: C10 دامنه ای است که فرمول در آن معیارهای دوم (علائم) را جستجو می کند ، و “> = 40” معیار دوم است.

فرمول: =COUNTIFS(criteria_range1, criteria1,…)

۸. SUMPRODUCT

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

در این مثال می خواستم کل محصولات فروخته شده را پیدا کنم. برای آن ، من از فرمول SUMPRODUCT (B2: B8 ، C2: C8)= استفاده کردم. در اینجا B2: B8 اولین آرایه است (مقدار کالای فروخته شده) و C2: C8 آرایه دوم (قیمت هر محصول) است. سپس فرمول مقدار هر محصول فروخته شده را با قیمت آن ضرب می کند و سپس کل آن را اضافه می کند تا کل فروش را تحویل دهد.

فرمول: =SUMPRODUCT(array1, [array2], [array3],…)

۹. TRIM

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

در این مثال می خواستم تمام فاصله های اضافی بین کلمات ماوس و پد را در A7 حذف کنم. برای همین من از فرمول TRIM (A7)= استفاده کردم.

فرمول به سادگی فضاهای اضافی را حذف کرد و نتیجه را با یک فاصله واحد تحویل داد.

فرمول: = TRIM ( متن )

۱۰. FIND/SEARCH

گردآوری موارد توابع FIND / SEARCH است که به شما در جداسازی متن خاص در یک مجموعه داده کمک می کند. هر دو عملکرد در عملکردی که انجام می دهند کاملاً مشابه هستند ، به غیر از یک تفاوت عمده – عملکرد FIND فقط مسابقات حساس مورد را برمی گرداند. در ضمن ، عملکرد SEARCH چنین محدودیتی ندارد. این توابع به ویژه هنگام جستجوی ناهنجاری یا شناسه های منحصر به فرد مفید هستند.

در این مثال ، می خواستم تعداد دفعاتی را پیدا کنم که “Gui” در Guiding Tech ظاهر شد و برای آن از فرمول FIND (A2 ، B2)= استفاده کردم ، که نتیجه را تحویل می داد. ۱. اگر می خواستم تعداد دفعات پیدا کنم “. در عوض gui ‘در Guiding Tech ظاهر شد ، من باید از فرمول SEARCH استفاده کنم چون حساس نیست.

فرمول برای یافتن: = FIND ( find_text، within_text، [start_num] )

فرمول جستجو : = Search ( find_text، brenda_text، [start_num] )

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

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