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

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

ببینید: این نکات اکسل را که همه باید بدانند بررسی کنید.

یک مثال خوب در این مورد نام ها هستند. شما اغلب کل نام را در یک ستون پیدا خواهید کرد که کار با آن مقادیر را دشوار می کند. در این آموزش، دو روش سریع برای تجزیه داده ها با استفاده از ویژگی های Flash Fill و Power Query Excel به شما نشان می دهم.

پرش به:

آنچه شما نیاز دارید

من از مایکروسافت 365 در سیستم 64 بیتی ویندوز 11 استفاده می کنم. می‌توانید از توابع رشته‌ای در نسخه‌های قدیمی‌تر از جمله قالب منوی xls. استفاده کنید. Flash Fill در اکسل 2013 به بعد و Power Query در اکسل 2010 به بعد موجود است. علاوه بر این، اکسل برای وب از Flash Fill پشتیبانی می کند. همچنین می‌توانید کوئری‌های Power Query موجود را در اکسل برای وب باز کنید، اما نمی‌توانید آنها را در نسخه مرورگر ایجاد کنید.

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

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

قبل از Flash Fill، بیشتر تجزیه به ترکیب توابع رشته مانند RIGHT()، LEFT()، MID()، FIND() و LEN() نیاز داشت. به عنوان مثال، عبارت در ستون D شکل A.

=IFERROR(RIGHT(B3,LEN(B3)-IFERROR(FIND(” “,B3,FIND(” “,B3)+1),FIND(” “,B3))),B3)

شکل A

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

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

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

ویژگی Flash Fill اکسل از سال 2013 وجود داشته است، بنابراین ممکن است با آن آشنا باشید. هنگام استفاده از آن برای تجزیه، اولین مقدار را در ستون هدف وارد کنید تا اکسل بتواند الگو را ارزیابی کند. گاهی اوقات، Flash Fill فقط به یک مقدار نیاز دارد. از آنجایی که داده های ما سازگار است، این کار تجزیه آسان خواهد بود.

بیایید از Flash Fill برای تجزیه نام خانوادگی از مقادیر نام در ستون B استفاده کنیم:

1. Harkins را در C3 وارد کنید.

2. Enter را فشار دهید. به احتمال زیاد، Flash Fill هنوز ستون را پر نمی کند.

3. اگر Flash Fill الگو را تعیین نمی کند، همانطور که در شکل B نشان داده شده است، چند کاراکتر اول را در Smyth وارد کنید . همانطور که می بینید، Flash Fill اکنون الگوی را دارد و پیشنهاد می کند کل ستون را پر کند.

شکل B

4. همانطور که در شکل C نشان داده شده است، Enter را فشار دهید تا سلول های باقی مانده را پر کنید . اگر از نسخه قدیمی اکسل استفاده می کنید، ممکن است لازم باشد Ctrl + Enter را فشار دهید.

شکل ج

Flash Fill اکسل بسیار سریعتر و آسانتر از ترکیب توابع رشته است. حتی زمانی که داده ها یکسان نیستند، این ویژگی پس از چند بار تلاش برای یافتن الگو به خوبی کار می کند. اکسل برای وب از Flash Fill پشتیبانی می کند. آن را در تب Data جستجو کنید. به جای فشار دادن Enter، روی Flash Fill در گروه Data Tools کلیک کنید.

ببینید: 6 راه برای صرفه جویی در زمان با استفاده از Flash Fill در اکسل مایکروسافت کاوش کنید .

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

Power Query برای کاربران اکسل مایکروسافت 2010 به بعد در دسترس است. هدف آن بازیابی و پاکسازی داده ها است، اما پر از ویژگی هایی است که می تواند کارهای بیشتری انجام دهد. با استفاده از Power Query، یک ستون اضافه می کنیم و نام های خانوادگی را در آن ستون تجزیه می کنیم.

ابتدا باید داده ها را به صورت زیر در Power Query بارگذاری کنیم:

1. در داخل جدول اکسل که می خواهید در Power Query بارگذاری شود، کلیک کنید.

2. روی تب Data کلیک کنید.

3. در گروه Get & Transform Data، روی From Table/Range کلیک کنید. اگر داده ها را به صورت جدول فرمت نکرده اید، Power Query از شما می خواهد که این کار را انجام دهید. جدول نمایشی TablePersonnel نام دارد ( شکل D ). شما نیازی به نام گذاری جدول ندارید، اما کار با یک جدول کمی ساده تر است.

شکل D

هنگامی که داده ها در Power Query قرار گرفتند، یک ستون بر اساس ستون Personnel به صورت زیر اضافه کنید:

1. برای انتخاب ستون، روی هدر Personnel کلیک کنید، سپس روی تب Add Column کلیک کنید.

2. در گروه عمومی، از منوی کشویی Column From Examples، From Selection را انتخاب کنید ( شکل E ). Power Query یک ستون خالی جدید اضافه می کند. برای وارد کردن مقادیر نام خانوادگی جدید برای این ستون، از نسخه Flash Fill خود Power Query استفاده می کنیم.

شکل های E

3. در اولین سلول ستون جدید، Harkins را وارد کنید ( شکل F ). Power Query با ارائه مجموعه ای از مقادیر نام خانوادگی برای کل ستون پاسخ می دهد. کمی به آنها نگاه کنید: اگر درست نیستند، نام خانوادگی بعدی را وارد کنید. با این حال، Power Query لیست صحیح را فقط با یک نام برای تنظیم الگوی داده های ما برمی گرداند.

شکل F

4. برای ایجاد و پر کردن ستون جدید با نام خانوادگی، روی OK کلیک کنید، همانطور که در شکل G نشان داده شده است .

شکل G

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

5. روی تب Home کلیک کنید.

6. در گروه Close از منوی بازشو Close & Load روی Close و Load کلیک کنید.

شکل H نام‌های خانوادگی تجزیه‌شده را در یک برگه اکسل که به‌عنوان یک شی جدول قالب‌بندی شده را نشان می‌دهد. Power Query از نام جدول اصلی، TablePersonnel، برای نام‌گذاری برگه جدید استفاده می‌کند. در این مرحله می توانید نام ستون جدید را تغییر دهید. همچنین می توانید قبل از ذخیره داده ها، نام ستون را در Power Query تغییر دهید.

شکل H

Flash Fill قطعا سریع‌تر بود، اما دانستن روش Power Query زمانی که در حال کار با داده‌ها در Power Query هستید به شما کمک می‌کند.

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