استكشاف وظائف التحويل والتحويل في Excel

ملخص تنفيذي

في عصر بحيرات البيانات وقواعد البيانات بحجم بيتابايت ، من المثير للصدمة مدى تكرار تلقي البيانات في شكل ملفات CSV ونصوص وملفات Excel. بينما تركز التحليلات الحديثة على التطورات المتطورة في خوارزميات التعلم الآلي ، فإن الكد اليومي لتحليل البيانات لا يزال عملية يدوية للعثور على أنواع البيانات المتباينة وتجميعها ومناقشتها.

بالنسبة للمحلل المالي ، تصل البيانات غالبًا في شكل جدول بيانات Excel ، ولكن في كثير من الأحيان ، يتم تفريغ البيانات في ملف CSV أو استعلام في قاعدة بيانات SQL. في بعض الأحيان ، يتم ترتيب البيانات في مخطط محير أو لا تحتوي على جميع المكونات المطلوبة للتحليل. الوقت الذي يقضيه المحلل في تنظيف هذه البيانات هو وقت ثمين يضيعه المحلل ، ولكن في بعض الأحيان يتم قبول هذه المهمة على أنها شر لا بد منه يجب تحمله.

ماذا يحصل ويتحول؟

يمكن الوصول إلى حل لهذه المشكلة الشائعة تمامًا: يحتوي Excel و Power BI على مجموعة كاملة من أدوات تحويل البيانات التي لا يعرفها سوى عدد قليل من المستخدمين ، تسمى Get & Transform (المعروفة سابقًا باسم Power Query). يتيح استخدام وظائف الاستخراج والتحويل والتحميل المضمنة (ETL) للمحللين الماليين الارتباط بسلاسة بمصادر بياناتهم والوصول إلى الرؤى بشكل أسرع.

نظرًا لأننا نرفع البيانات لتحميلها في Excel أو Power BI ، يتعين علينا عادةً إجراء بعض التحويلات على البيانات. تتضمن بعض أمثلة التلاعب بالبيانات ما يلي:

• إزالة الأعمدة ،
• تصفية البيانات ،
• تجميع البيانات ،
• تمحور / عدم تنشيط البيانات ،
• تقسيم الأوتار إلى سلاسل فرعية ،
• استخراج الكلمات الرئيسية من السلاسل ،
• إلحاق صفوف من جدول آخر ، و
• ربط جدولي الأبعاد.

في الرسم البياني أدناه ، نرى أن Get & Transform يؤدي هذا الدور الممل المتمثل في المعالجة المسبقة للبيانات قبل تحميلها.

لماذا يجب عليك استخدام Get & Transform؟

لماذا من المجدي تعلم كيفية استخدام Get & Transform؟ حسنًا ، عندما ألقي نظرة على ما استخدمت شخصيًا من أجله هذه الوظيفة ، فقد قدمت لي مجموعة مرنة من الأدوات من أجل:

• تحميل مجلد كامل من الملفات النصية في جدول بيانات واحد
• تحويل ملفات المحاسبة المصدرة إلى تخطيط قابل للفهم
• تحميل الملايين من صفوف المبيعات إلى Power Pivot مباشرة
• تجميع البيانات اليومية في نتائج شهرية يمكن إدارتها قبل استيرادها إلى Excel
• تضافر البيانات من جدول آخر عن طريق الانضمام إلى الأعمدة المطابقة

بشكل عام ، عندما أتلقى بيانات جديدة ، سأستكشفها باستخدام Get & Transform قبل تحميلها في Power Pivot. يتيح لي ذلك معرفة التحولات التي قد تكون ضرورية وتنفيذ بعض المحاور والتجمعات بسرعة على البيانات لصياغة إطار عمل للتحليل. في كثير من الحالات ، في هذه المرحلة ، سأجد أنني بحاجة إلى مزيد من البيانات ، أو أن هناك مشكلات في البيانات. باستخدام نظام أساسي قائم على Excel ، يمكنني التكرار بسرعة مع مصدر البيانات الخاص بي للعثور على هذه البيانات الشاذة.

في النهاية ، سيعتمد قرار البقاء في Excel أو نقل تحليل البيانات إلى نظام أساسي آخر على الجمهور وقابلية تكرار التحليل وتوزيعه. إذا كان عملائي يستخدمون Excel فقط ، فحينئذٍ سأستخدم دائمًا Get & Transform لتحميل البيانات ، و Power Pivot لإجراء التحليل ، و Excel لإنتاج جداول PivotTable والمخططات. بالنسبة للعميل ، سيشعر هذا بالسلاسة نظرًا لأنه موجود في Excel.

ومع ذلك ، إذا كان موكلي:

1. يريد استخدام أداة تصور أخرى ،
2. لديه العديد من المستخدمين الذين سيقومون بتحديث البيانات ، أو
3. يحتاج إلى استخدام نماذج التعلم الآلي ،

ثم سأستخدم Get & Transform فقط لاستكشاف البيانات الأولية ، ثم أنقل الرفع الثقيل إلى R.

كيفية الوصول إلى الحصول على وتحويل في Excel أو Power BI

في الإصدارات السابقة من Excel ، كان Power Query عبارة عن وظيفة إضافية يمكن تثبيتها للمساعدة في وظائف ETL. ومع ذلك ، في Excel 2016 و Power BI ، تكون هذه الأدوات أكثر تكاملاً. في Excel 2016 ، يمكن الوصول إليها من خلال علامة التبويب البيانات ، ثم قسم الحصول على البيانات وتحويلها.

في Power BI ، توجد الوظيفة في علامة التبويب الصفحة الرئيسية ، في قسم البيانات الخارجية .

في هذه المقالة ، تحدث الأمثلة الخاصة بي في Power BI ، لكن الواجهة متطابقة تقريبًا مع واجهة Excel. سأشير إلى الاختلافات عند ظهورها ، لذا يجب أن يكون البرنامج التعليمي منطقيًا لكلا النوعين من المستخدمين.

1. تحميل ملفات CSV

للمساعدة في هذا البرنامج التعليمي ، قمت بإنشاء بعض الأمثلة على بيانات المبيعات لمتاجر تجزئة خيالي يبيع معدات وملابس خارجية. في كل من هذه الأمثلة ، سيتم إنتاج البيانات بطرق مختلفة لتوضيح الأساليب الواقعية لتفريغ البيانات.

كمثال أولي ، سنرى البيانات المقدمة على أنها تفريغ بيانات كبير في ملف CSV. العامل المعقد هو أن البيانات يتم تقديمها في أعمدة متعددة تمثل مخازن مختلفة. نود بشكل مثالي استيراد البيانات وتحويلها إلى تخطيط أكثر قابلية للاستخدام.

يوجد أدناه لقطة شاشة لما يبدو عليه ملف CSV الخام:

لماذا نريد تغيير هذا؟ للاستفادة من إمكانيات العلاقة الممكنة في هذه التطبيقات. سنرى هذا يلعب أكثر في المناقشة.

في الوقت الحالي ، لنفترض أننا بحاجة إلى رؤية البيانات على أنها بنية "أضيق وأطول" ، بدلاً من بنية "أوسع وأقصر". الخطوة الأولى هي تحميل ملف CSV ؛ بعد ذلك ، سنبدأ في "إلغاء تنشيط" البيانات.

كما ترى ، فإن البنية النهائية للبيانات أضيق من البيانات الأولية وأطول بكثير. نقطة أخرى هي أنه أثناء النقر فوق إجراءات مختلفة ، تقوم الأداة الموجودة على الجانب الأيمن بإنشاء قائمة بالخطوات المطبقة المستخدمة لبناء الاستعلام. من المهم أن نفهم أن هذا يحدث في الخلفية ، حيث سيتم إعادة النظر فيه لاحقًا.

احصل على & تحويل المظهر والسلوك بشكل مشابه بين Power BI و Excel في معظم الأحيان. ومع ذلك ، في Excel ، بعد النقر فوق إغلاق وتحميل ، هناك موجه إضافي واحد. في الشكل أدناه ، يمكننا التبديل بين ما إذا كنا نرغب في تحميل البيانات إلى:

1. جدول في Excel ،
2. تم إنشاء PivotTable مقابل البيانات ،
3. تم إنشاء PivotChart مقابل البيانات ، أو
4. "إنشاء اتصال فقط."

بالإضافة إلى ذلك ، يتم منحنا أيضًا خيار إضافة هذه البيانات إلى نموذج البيانات من عدمه. يؤدي تحديد هذا المربع إلى تحميل البيانات في جدول Power Pivot. إذا كنا سنقوم بتحليل البيانات في Power Pivot ، فإنني أنصح باختيار إنشاء اتصال فقط ثم التأكد من تحديد الخيار إضافة هذه البيانات إلى نموذج البيانات . إذا كانت البيانات ضمن حد صف Excel ، ونفضل إجراء تحليلنا في Excel ، فما عليك سوى اختيار الجدول .

في المقطع التالي ، سنرى أن سبب تنسيقنا للبيانات لتكون طويلة ونحيفة هو أننا قادرون على تحليل المبيعات ليس فقط حسب المتجر ولكن أيضًا حسب المنطقة والولاية. لإنجاز هذه المهمة ، سنقوم باستيراد جدول يقوم بتعيين كل متجر إلى منطقة ودولة. سنرى أدناه أنه يمكننا إنشاء تقارير بسرعة تُظهر المبيعات من خلال هذه المجموعات المختلفة.

يمكنك أن تتخيل كيف يمكن تطبيق هذا النوع من القدرة على تحويل البيانات في Excel أو Power BI بقوة على أي حالة لدينا فيها مجموعات ديناميكية من البيانات ، مثل:

• تجميع البيانات اليومية في الأسابيع والشهور والأرباع ؛
• تجميع موظفي المبيعات في الإدارات والمناطق ؛ أو
• تعيين رموز التخزين التعريفية لأنواع المنتجات.

بينما تتناول هذه المقالة CSV وملفات Excel الأخرى ، تعالج Get & Transform مجموعة واسعة من أنواع البيانات. بمجرد إنشاء استعلام ، يمكن تحديثه بمرور الوقت مع تغير البيانات.

2. التعامل مع سلاسل النص

من أجل إثبات قدرة Get & Transform على معالجة السلاسل ، قمت بإنشاء مجموعة بيانات أخرى تحاكي ملفًا نصيًا يعرض المعاملات المحاسبية من دفتر الأستاذ العام للشركة (GL).

لاحظ كيف يظهر رقم الحساب والاسم في نفس السلسلة؟ في Power BI ، يمكننا تحليل رقم الحساب والاسم بسهولة في حقول منفصلة.

في هذا الفيديو ، يمكنك أن ترى أنه بعد تقسيم العمود ، خمنت الأداة أن الجانب الأيسر الجديد من حقل الحساب يجب أن يكون رقمًا ، وتقوم بإنشاء خطوة "تم تغيير النوع 1". نظرًا لأننا نريد هذا الحقل في النهاية كسلسلة ، يمكننا المضي قدمًا وحذف الخطوة يدويًا ضمن الخطوات المطبقة.

بعد ذلك ، نأخذ نفس البيانات وننشئ مخططًا للحسابات مع تعيينات لفئات الحساب.

لماذا نمر بكل هذه الخطوات لتعيين بعض أرقام الحسابات؟ يمكن أن يتكون دفتر الأستاذ العام الحقيقي من مئات أو حتى آلاف الحسابات. استعلام التعيين السريع هذا ، كما أوضحنا ، سيتوسع إلى هذا المستوى بدون أي عمل إضافي.

3. العمل مع مصادر البيانات المختلفة

يدعم Get & Transform العديد من مصادر البيانات المختلفة. في حين أن القائمة ليست شاملة ، فيما يلي بعض الأمثلة:

شخصيا ، لقد جربت فقط حوالي نصف الاتصالات في القائمة أعلاه. كان كل من الموصلات التي استخدمتها قويًا إلى حد ما ؛ لقد انتقلت من البيانات الأولية إلى الرؤى دون بذل جهد مرهق. وبنفس القدر من الأهمية ، فهو يعمل كمدقق بين مصادر البيانات المتباينة ، مما يضمن أن المخرجات النهائية لها مستوى معياري من مراقبة الجودة.

4. إضفاء الطابع الشخصي على الكود مع لغة M

في الخلفية ، يقوم Get & Transform بإنشاء رمز في كل مرة نقوم فيها بالنقر فوق زر في الأداة أو إجراء تحديد. فيما يلي مثال على كيفية الوصول إلى رمز استعلام تعيين الحساب الذي أنشأناه:

يستخدم الرمز لغة وظيفية تسمى M ، والتي يتم إنشاؤها تلقائيًا لحالات الاستخدام الأساسية. ومع ذلك ، لمزيد من الجدل حول البيانات المعقدة ، يمكننا تحرير وكتابة التعليمات البرمجية الخاصة بنا. في معظم الحالات ، سأقوم فقط بإجراء تعديلات طفيفة على هذا الرمز. في عمليات التحويل الأكثر تعقيدًا ، قد أكتب معظم الكود من البداية إلى الجداول المؤقتة ، أو لإجراء عمليات صلات أكثر تعقيدًا.

حدود الحصول والتحويل

يميل Excel إلى الوصول إلى حدوده عندما تحاول تصدير أكثر من مليون صف. في الحالات التي قمت فيها بتحويل ملايين الصفوف باستخدام Get & Transform ، فإن الطريقة الوحيدة لشحن الصفوف غير المجمعة هي عبر الاختراقات أو الحلول البديلة المملة. لقد اكتشفت أيضًا أن استعلامات Get & Transform يمكن أن تكون غير مستقرة للنشر إلى عدة مستخدمين ، خاصةً إذا كنت تستخدم مصادر بيانات متعددة وتنضم. في هذه الحالات ، سأستخدم دائمًا R لنشر مشاحنات البيانات القابلة للتكرار. أخيرًا ، لم يتم إنشاء Excel لنمذجة بيانات أكثر تقدمًا. يمكنك إجراء الانحدار الخطي بسرعة كبيرة ، ولكن بعد ذلك ، ستحتاج إلى استخدام نظام أساسي أكثر صرامة.

بعد قولي هذا كله ، أجد أن برنامج Excel هو أكثر ما يرتاح إليه معظم عملائي. لا يزال برنامج Excel أهم أداة في ترسانة المحللين الماليين. من خلال دمج وظائف Get & Transform ، يصبح Excel و Power BI أكثر قوة من خلال مجموعة مصادر البيانات التي يمكنهم قبولها.