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

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

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

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

5

متون

متن را از هم جدا می کند که به هم گیر کرده است

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

بیایید با صفحه گسترده داده های فروش نمونه کار کنیم. اسامی فروشندگان را که به عنوان “سارا چن” ، “مایک جانسون” و “پارک لیزا” ذکر شده است ، در یک ستون مشاهده خواهید کرد. به جای اینکه به صورت دستی هر نام را در ستون های جداگانه مجدداً مجدداً مجدداً انجام دهید ، Textsplit می تواند کار را به صورت خودکار انجام دهد.

این نحو زیر را دارد:

= textsplit (متن ، col_delimiter ، [row_delimiter] ، [ingnore_empty] ، [match_mode] ، [pad_with])

در اینجا آنچه هر پارامتر انجام می دهد آورده شده است:

• متن: سلول حاوی متنی که می خواهید تقسیم کنید.
• col_delimiter: شخصیتی که داده های شما را از هم جدا می کند (مانند یک فضا ، کاما یا نیمه رنگ).
• row_delimiter (اختیاری): هنگام تقسیم هر دو ردیف و ستون استفاده می شود.
• Ignore_empty (اختیاری): واقعی مقادیر خالی را نادیده می گیرد ، کاذب آنها را نگه می دارد (پیش فرض نادرست است).
• MATCH_MODE (اختیاری): حساسیت مورد را کنترل می کند (0 برای حساس به مورد ، 1 برای مورد حساس).
• pad_with (اختیاری): وقتی نتایج دارای طول ناهموار هستند ، چه سلولهای خالی را پر کنیم.

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

= textsplit (a2 ، “”)

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

4

جبهه

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

TextJoin خلاف Textsplit است. متن را از چندین سلول می گیرد و آنها را در یک سلول واحد با هر جدا کننده ای که انتخاب می کنید ترکیب می کند. این امر در صورت نیاز به ایجاد مقادیر هماهنگ مانند آدرس های کامل ، توضیحات محصول یا لیست های ایمیل مفید است.

نحو به این شکل است:

= TextJoin (تعیین کننده ، IGNORE_EMPTY ، TEXT1 ، [TEXT2] ، …)

در اینجا چیزی که هر پارامتر کنترل می کند آورده شده است:

• تعیین کننده: شخصیت یا متنی که مقادیر پیوسته شما را از هم جدا می کند (کاما ، فضا ، هیفن و غیره).
• IGNORE_EMPTY: سلولهای خالی را رد می کند ، دروغ ها را در نتیجه شامل می کند.
• Text1 ، Text2 ، و غیره: سلول ها یا دامنه هایی که می خواهید ترکیب کنید (می توانید سلولهای فردی یا کل محدوده را ارجاع دهید).

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

= TextJoin (” -” ، درست ، B2 ، D2)

هنگام تصمیم گیری بین روشهای مختلف ترکیبی متن ، درک تفاوت بین توابع Concat و TextJoin می تواند به شما کمک کند تا ابزار مناسب برای نیازهای خاص خود را برای نیازهای خود انتخاب کنید.

3

انتخاب

ستون های خاص را از داده های شما انتخاب می کند

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

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

این نحو زیر را دارد:

= Choosecols (آرایه ، col_num1 ، [col_num2] ، …)

در اینجا نحوه عملکرد هر پارامتر آورده شده است:

• آرایه: دامنه یا جدول حاوی داده های منبع شما (می تواند یک محدوده سلولی مانند A1: F100 یا یک مرجع جدول باشد).
• col_num1: شماره ستون اول که می خواهید استخراج کنید (1 برای ستون اول ، 2 برای دوم و غیره).
• col_num2 ، و غیره: شماره ستون های اضافی که می خواهید در آن گنجانید (اختیاری – شما می توانید در حد نیاز مشخص کنید).

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

= Choosecols (A1: I23 ، 2 ، 9)

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

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

2

گرفتن و رها کردن

بخش هایی از داده های خود را استخراج می کند

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

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

استفاده از این نحو را بگیرید:

= گرفتن (آرایه ، ردیف ، [ستون])

Drop از یک الگوی مشابه پیروی می کند:

= قطره (آرایه ، ردیف ، [ستون])

در اینجا نحوه کار پارامترها برای هر دو عملکرد آورده شده است:

• آرایه: محدوده داده منبع مورد نظر شما می خواهید از آن استخراج یا اصلاح کنید.
• ردیف ها: تعداد ردیف ها برای گرفتن/رها کردن (اعداد مثبت از بالا کار می کنند ، از پایین منفی است).
• ستون ها (اختیاری): تعداد ستون ها برای گرفتن/رها کردن (مثبت از سمت چپ ، منفی از سمت راست).

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

= گرفتن (A1: C100 ، 5)

برای حذف بیست ردیف برتر و کار با داده های تمیز ، سعی کنید:

= قطره (A1: C23 ، 20)

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

= گرفتن (A1: F23 ، 10 ، 3)

این توابع مفید هستند ، به خصوص هنگامی که به زیر مجموعه های داده پویا نیاز دارید که به طور خودکار تنظیم می شوند. یادگیری نحوه استفاده از توابع Take and Drop در اکسل امکاناتی را برای ایجاد گزارش های انعطاف پذیر که با تغییر اندازه داده ها سازگار هستند ، باز می کند.

1

جمع شده

محاسبات قدرتمند که داده های کثیف را اداره می کنند

کل عملکرد 19 عملکرد آماری مختلف را در یک فرمول انعطاف پذیر ترکیب می کند. آنچه باعث خاص می شود توانایی آن در نادیده گرفتن خطاها ، ردیف های پنهان یا داده های فیلتر شده است – عملکردهای استاندارد مانند جمع یا میانگین نمی توانند با اطمینان انجام دهند.

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

نحو شامل چندین مؤلفه است:

= جمع (function_num ، گزینه ها ، آرایه ، [k])

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

• function_num: یک عدد از 1-19 که مشخص می کند از کدام عملکرد استفاده می کند (1 = میانگین ، 4 = حداکثر ، 9 = مبلغ ، 12 = متوسط ​​و غیره).
• گزینه ها: کنترل آنچه را که باید در طول محاسبه نادیده بگیرند (0 = هیچ چیز ، 1 = ردیف های پنهان ، 2 = مقادیر خطا ، 3 = ردیف ها و خطاهای پنهان ، 5 = فقط مقادیر خطا ، 6 = ردیف های پنهان و مقادیر خطا).
• آرایه: دامنه سلول ها برای انجام محاسبه در.
• K (اختیاری): فقط با توابع خاصی مانند بزرگ ، کوچک یا صدک استفاده می شود.

به طور خلاصه مبلغ فروش قابل مشاهده در حالی که هرگونه خطایی را نادیده می گیرم ، استفاده می کنم:

= جمع (9 ، 6 ، D2: D23)

شماره 9 مبلغ را مشخص می کند ، و 6 به آن می گوید که هر دو ردیف پنهان و مقادیر خطا را نادیده می گیرند.

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

ابزارهای داخلی که ارزش استفاده دارند

با ارزش ترین کارکردهای اکسل غالباً کارهایی نیستند که مردم ابتدا یاد می گیرند. با این حال ، آنها مشکلات دقیقی را که در کارهای صفحه گسترده به وجود می آیند ، از جمله داده های متن کثیف ، استخراج بخش های خاص از مجموعه داده های بزرگ و انجام محاسبات در مورد داده های ناقص ، برطرف می کنند. هیچ یک از کارکردهایی که ما در مورد آنها بحث کردیم ، به مهارت های پیشرفته اکسل احتیاج ندارد. با این حال ، Textsplit ، Choosecols ، Take و Drop فقط در مایکروسافت 365 و اکسل برای وب موجود است.

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

منبع مقاله