بالاخره این ویژگی اکسل را که همه می‌دانند اما نادیده می‌گیرند امتحان کردم—خیلی مفیدتر از آنچه فکر می‌کردم

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

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

مشکلی که بالاخره باعث شد به آن توجه کنم

به دلیل چندین عامل بازار و مالیات‌های وارداتی، خرید قطعات کامپیوتر در منطقه من اغلب گران‌تر از ایالات متحده است. می‌خواستم بفهمم برای همان قطعات چقدر بیشتر پرداخت می‌کنم و آیا بهتر است به‌جای خرده‌فروشان محلی، مستقیماً از آمازون یا نیوِگ سفارش کنم. بنابراین، چند ماه داده‌های قیمت‌گذاری برای قطعات کلیدی کامپیوتر (CPUها، GPUها، RAM) که فروشگاه‌های محلی معمولاً وارد می‌کنند، جمع‌آوری کردم. پروژه‌ی ساده‌ای برای پیگیری، درست است؟ نه.

به سرعت به یک هرج و مرج کامل از داده‌ها رسیدم. هر فروشنده اطلاعات خود را با قالب‌بندی‌های متفاوت صادر می‌کرد که ترکیب فایل‌ها را تقریباً غیرممکن می‌کرد. آمازون تاریخ‌ها را در قالب MM/DD/YYYY ارائه داد، نیوِگ با YYYYMMDD و شاپی (فروشگاه محلی من) از DD‑MM‑YYYY استفاده می‌کرد.

ناسازگاری‌ها در آنجا پایان نیافت. نام ستون‌ها به‌طرزی وحشیانه متفاوت بود. نیوِگ قیمت‌ها را با “retail_price” نام‌گذاری کرده بود، در حالی که آمازون از “unit_price_usd” استفاده می‌کرد و شاپی “price_php” را انتخاب کرد. قالب‌بندی قیمت نیز به همان اندازه مشکل‌ساز بود؛ برخی فایل‌ها “₱18,600” با نمادهای ارز نشان می‌دادند، در حالی که دیگران اعداد ساده‌ای مانند “320” را نمایش می‌دادند. حتی نام برندها نیز ثابت نبودند؛ برای همان سازنده در فایل‌های مختلف به‌صورت “gigabyte”، “GIGABYTE INC.” یا “Gigabyte Tech” ظاهر می‌شدند.

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

در همین لحظه بود که نهایتاً به استفاده از ویژگی‌ای که همیشه طرفداران اکسل درباره‌اش صحبت می‌کنند—Power Query— فکر کردم. چندین ویژگی قدرتمند دیگر که اکسل ارائه می‌دهد، اما شنیده بودم Power Query ابزار مناسبی برای مشکل خاص من است. بنابراین، پس از تماشای چند آموزش یوتیوب، بلافاصله متوجه شدم چقدر می‌توانم زمان ذخیره کنم وقتی شروع به استفاده از Power Query Editor برای پاک‌سازی تمام داده‌های پراکنده‌ای که از اینترنت برداشت کرده‌ام، می‌کنم.

چگونه از Power Query برای تمیز کردن داده‌های کثیف استفاده می‌کنم

مدتی پس از آن، روی یک فرآیند ساده گام به گام در Power Query Editor استقرار پیدا کردم. این دقیقاً نحوه پاک‌سازی آن خروجی‌های CSV پراکنده و تبدیل آن‌ها به یک جدول منظم و ساختاریافته است.

ابتدا داده‌هایم را به Power Query Editor وارد کردم با باز کردن یک کتاب‌کار خالی، کلیک روی Data در نوار ابزار، و انتخاب From Text/CSV. سپس فایل CSV خود را انتخاب کردم و روی Transform Data کلیک کردم تا با Power Query Editor باز شود.

من با اصلاح ستون Date شروع کردم. چون داده‌ها را از دو منبع با جدول زمانی 12 ساعته متفاوت جمع‌آوری می‌کردم، نیاز به استانداردسازی تاریخ‌ها داشتم. این کار بسیار ساده بود. ستون Date را انتخاب کردم، کلیک راست کردم تا منوی زمینه باز شود، و Change Type > Using Locale را انتخاب کردم. در پنجره باز شونده، نوع را به Date تنظیم کرده و English (United States) را برای اطمینان از قالب یکسان انتخاب کردم. سپس Power Query به‌صورت خودکار قالب‌های مختلف مانند MM/DD/YYYY، YYYY/MM/DD و انواعی که از نمادهایی مثل DD‑MM‑YY استفاده می‌کردند، شناسایی کرد و همه را به یک قالب تاریخ واحد استاندارد کرد.

حالا که قالب تاریخ اصلاح شده بود، فقط نیاز به تمیز کردن ستون داشتم. روش‌های مختلفی برای پاک‌سازی یک صفحه‌گسترده اکسل وجود دارد، اما چون تمام خطاها ورودی‌های بدی بودند که ابزار استخراج من ایجاد کرده بود، به‌سادگی فیلتر Remove Errors را برای حذف این ورودی‌ها انتخاب کردم. این گام مقادیر NULL و هر داده problematic باقی‌مانده که به‌درستی ثبت نشده بود حذف کرد و من را با تاریخ‌های پاک و سازگار در تمام فایل‌ها باقی گذاشت.

سپس، با استفاده از عملکرد Replace Values به آشفتگی نام برندها پرداختم. همانند قبل، ستون هدف را انتخاب کردم، سپس کلیک راست کردم تا منوی زمینه باز شود و Replace Values را انتخاب کردم. در پنجره بازشده، مقدار نامنظم را در فیلد Value to Find وارد کرده و مقدار استانداردم را در فیلد Replace With وارد کردم.

این کار را حدود دو بار دیگر انجام دادم و سرانجام تمام “gigabyte” و “GIGABTYE Inc.” را به یک “GIGABYTE” یک‌پارچه در تمام فایل‌ها تبدیل کردم. همان کار را برای AMD انجام دادم و اکنون کل ستون Brand برای GPUها از نام‌های برند استاندارد استفاده می‌کند.

• 

• 

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

روشی که این مشکل را برطرف کردم این بود که ابتدا با استفاده از Replace Values تمام $, ₱، USD و PHP را از ستون Price حذف کردم. سپس مقادیر را به اعشار تبدیل کردم با استفاده از Change Type > Decimal. با داشتن ستونی پاک که فقط شامل اعداد اعشاری باشد، یک ستون جدید ایجاد کردم با کلیک روی Column (نوار ابزار) > Custom Column و آن را «Price USD» نام‌گذاری کردم. سپس فرمول زیر را وارد کردم:

if [Store] = "Shopee PH"
then Number.Round([Price] / 55, 2)
else Number.Round([Price], 2)

سپس روی Okay کلیک کردم. این کار تمام قیمت‌ها را از Shopee PH به دلار آمریکا تبدیل کرد برای مقایسه آسان.

این فرمول در زبان فرمول Power Query M نوشته شده است. این یک زبان اسکریپت‌نویسی نسبتاً ساده با مفهوم پایه‌ای ساده است که به‌راحتی می‌توان آن را درک کرد. نیازی نیست تمام زبان را یاد بگیرید تا بتوانید از آن مؤثر استفاده کنید — من هم این‌کار را نکردم. در عوض، هر زمان که نیاز به ساخت فرمول‌های خودم داشتم، از آن به‌عنوان مرجع استفاده می‌کنم.

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

Power Query ساعت‌ها از وقت من را نجات داد

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

چیزی که حتی بیشتر مرا شگفت‌زده کرد این بود که هر دستوری که اجرا می‌کردم ضبط می‌شد و می‌توانست بارها و بارها تکرار شود. این به‌طور اساسی یک اسکریپت تمیزکاری خودکار به شما می‌دهد که می‌تواند فایل‌های CSV پراکنده را به صفحه‌گسترده‌های پاک و ساختاریافته تبدیل کند — ایده‌آل اگر در حال ساخت دیتاست‌های سفارشی با وب‌اکتشاف هستید، چون این ابزارها اغلب داده‌های کثیف خروجی می‌دهند.

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

منبع مقاله