كيفية تحليل البيانات في Microsoft Excel

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

وخير مثال على ذلك الأسماء. غالبًا ما ستجد الاسم بالكامل في عمود واحد مما يجعل العمل بهذه القيم صعبًا. في هذا البرنامج التعليمي ، سأوضح لك طريقتين سريعتين لتحليل البيانات باستخدام ميزات Flash Fill و Power Query في Excel.

أستخدم Microsoft 365 على نظام Windows 10 64 بت. يمكنك استخدام وظائف السلسلة في الإصدارات القديمة بما في ذلك تنسيق القائمة .xls. يتوفر Flash Fill في Excel 2013 والإصدارات الأحدث ، ويتوفر Power Query في Excel 2010 والإصدارات الأحدث. بالإضافة إلى ذلك ، يدعم Excel للويب التعبئة الفلاش. يمكنك أيضًا فتح استعلامات Power Query الموجودة في Excel للويب ، ولكن لا يمكنك إنشاؤها في إصدار المستعرض. قم بتنزيل الملفات التجريبية.

كيفية التحليل باستخدام وظائف سلسلة Microsoft Excel

قبل Flash Fill ، تطلب معظم التحليل الجمع بين وظائف السلسلة مثل RIGHT () و LEFT () و MID () و FIND () و LEN (). على سبيل المثال ، التعبير الموجود في العمود D من الشكل A.

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

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

يمكن للتعبئة السريعة إكمال العديد من مهام التحليل في الإصدارات الأحدث من Microsoft Excel.

كيفية استخدام Flash Fill لتحليل البيانات في Microsoft Excel

ميزة Flash Fill في Excel موجودة منذ Excel 2013 ، لذا قد تكون على دراية بها. عند استخدامه للتحليل ، أدخل القيمة الأولى في العمود الهدف ، حتى يتمكن Excel من تقييم النمط. في بعض الأحيان ، يتطلب Flash Fill قيمة واحدة فقط. نظرًا لأن بياناتنا متسقة ، ستكون مهمة التحليل هذه سهلة.

دعنا نستخدم Flash Fill لتحليل الأسماء الأخيرة من قيم الاسم في العمود B.

أولاً ، أدخل Harkins في C3 واضغط على Ctrl + Enter. على الأرجح ، لن يملأ Flash Fill العمود.

بعد ذلك ، أدخل الأحرف القليلة الأولى في Smyth ، كما هو موضح في الشكل B. كما ترى ، يحتوي Flash Fill الآن على النمط والعروض لملء العمود بأكمله.

اضغط على Enter لملء الخلايا المتبقية ، كما هو موضح في الشكل C.

يعد برنامج Excel’s Flash Fill أسرع وأسهل بكثير من الجمع بين وظائف السلسلة. حتى عندما لا تكون البيانات متسقة ، تقوم هذه الميزة بعمل جيد في العثور على النمط.

يدعم Excel للويب التعبئة الفلاش. ابحث عنها في علامة التبويب البيانات. بدلاً من الضغط على Ctrl + Enter ، انقر فوق Flash Fill في مجموعة Data Tools.

كيفية استخدام Power Query لتحليل البيانات في Microsoft Excel

يتوفر Power Query لمستخدمي Microsoft Excel 2010 والإصدارات الأحدث. والغرض منه هو استرداد البيانات وتنظيفها ، ولكنها مليئة بالميزات التي يمكنها فعل المزيد. باستخدام Power Query ، سنضيف عمودًا ونحلل الأسماء الأخيرة في هذا العمود.

أولاً ، نحتاج إلى تحميل البيانات في Power Query على النحو التالي:

• انقر في أي مكان داخل الجدول الذي تريد تحميله في Power Query.
• انقر فوق علامة التبويب البيانات.
• في المجموعة الحصول على البيانات وتحويلها ، انقر فوق من جدول / نطاق.
• إذا لم تقم بتنسيق البيانات كجدول ، سيطالبك Power Query بالقيام بذلك. جدول العرض هو TablePersonnel. لست بحاجة إلى تسمية الجدول ، ولكن من الأسهل قليلاً التعامل مع الجدول المسمى.

يوضح الشكل D TablePersonnel في Power Query.

بمجرد أن تكون البيانات في Power Query ، أضف عمودًا استنادًا إلى عمود الأفراد.

أولاً ، انقر فوق رأس الأفراد لتحديد العمود ، وانقر فوق علامة التبويب إضافة عمود.

في المجموعة العامة ، اختر من التحديد من القائمة المنسدلة العمود من الأمثلة (الشكل هـ). يضيف Power Query عمودًا فارغًا جديدًا. لإدخال قيم الاسم الأخير الجديدة لهذا العمود ، سنستخدم إصدار Power Query الخاص من Flash Fill.

في الخلية الأولى من العمود الجديد ، العمود 1 ، أدخل Harkins (الشكل F). يستجيب Power Query من خلال تقديم مجموعة من قيم الاسم الأخير للعمود بأكمله. انظر إليهم قليلا. إذا لم تكن صحيحة ، فأدخل اسم عائلة آخر. ومع ذلك ، يُرجع Power Query القائمة الصحيحة باسم واحد فقط لتعيين النمط لبياناتنا.

انقر فوق “موافق” لإنشاء وملء العمود الجديد بأسماء العائلة ، كما هو موضح في الشكل ز.

كل ما تبقى القيام به هو حفظ البيانات المعدلة في Microsoft Excel ، على النحو التالي:

1. انقر فوق علامة التبويب الصفحة الرئيسية.
2. في المجموعة إغلاق ، انقر فوق إغلاق وتحميل من القائمة المنسدلة إغلاق وتحميل.

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

كان Flash Fill أسرع بالتأكيد ولكن معرفة طريقة Power Query سيساعدك عندما تعمل بالفعل مع البيانات في Power Query.