الكلمات التي تحتوي على أخطاء إملائية والمسافات الزائدة العنيدة والبادئات غير المرغوب فيها والحالات غير الصحيحة والأحرف غير المطبوعة تعطي انطباعا أوليا سيئا. وهذه ليست حتى قائمة كاملة بالطرق التي يمكن أن تتسخ بها بياناتك. نشمر عن سواعدك. حان الوقت لإجراء بعض التنظيف التفصيلي الرئيسي لأوراق العمل باستخدام Microsoft Excel.
أساسيات تنظيف البيانات
لا يمكنك دائما التحكم في تنسيق البيانات التي تستوردها من مصدر بيانات خارجي ونوعها، مثل قاعدة بيانات أو ملف نصي أو صفحة ويب. قبل أن تتمكن من تحليل البيانات، ستحتاج في الغالب إلى تنظيفها. لحسن الحظ، تتوفر في Excel ميزات كثيرة لمساعدتك في الحصول على البيانات بالتنسيق الدقيق الذي تريده. في بعض الأحيان، تكون المهمة واضحة وتوجد ميزة معينة تقوم بالمهمة نيابة عنك. على سبيل المثال، يمكنك استخدام المدقق الإملائي بسهولة لتنظيف الكلمات التي بها أخطاء إملائية في الأعمدة التي تحتوي على تعليقات أو أوصاف. أو، إذا كنت تريد إزالة الصفوف المكررة، فيمكنك إجراء ذلك بسرعة باستخدام مربع الحوار "إزالة التكرارات ".
في أحيان أخرى، قد تحتاج إلى معالجة عمود واحد أو أكثر باستخدام صيغة لتحويل القيم المستوردة إلى قيم جديدة. على سبيل المثال، إذا كنت تريد إزالة المسافات الزائدة، فيمكنك إنشاء عمود جديد لتنظيف البيانات باستخدام صيغة، وتعبئة العمود لأسفل، وتحويل صيغ العمود الجديد إلى قيم، ثم إزالة العمود الأصلي.
الخطوات الأساسية لتنظيف البيانات هي كما يلي:
استيراد البيانات من مصدر بيانات خارجي.
إنشاء نسخة احتياطية من البيانات الأصلية في مصنف منفصل.
تأكد من أن البيانات بتنسيق جدولي مؤلف من صفوف وأعمدة مع: بيانات مماثلة في كل عمود، وكل الأعمدة والصفوف مرئية، وعدم وجود صفوف فارغة داخل النطاق. للحصول على أفضل النتائج، استخدم جدول Excel.
نفذ مهاما لا تتطلب معالجة العمود أولا، مثل التدقيق الإملائي أو مربع الحوار "بحث واستبدال ".
بعد ذلك، نفذ المهام التي تتطلب معالجة الأعمدة. تتمثل الخطوات العامة للتعامل مع عمود فيما يلي:
- قم بإدراج عمود جديد (B) بجانب العمود الأصلي (A) الذي يحتاج إلى تنظيف.
- أضف صيغة من شأنها تحويل البيانات الموجودة في أعلى العمود الجديد (B).
- قم بتعبئة الصيغة لأسفل في العمود الجديد (B). في جدول Excel، يتم تلقائيا إنشاء عمود محسوب يتضمن قيما معبأة لأسفل.
- حدد العمود الجديد (B) وانسخه ثم الصقه كقيم في العمود الجديد (B).
- إزالة العمود الأصلي (A)، مما يؤدي إلى تحويل العمود الجديد من B إلى A.
لتنظيف مصدر البيانات نفسه بشكل دوري، يمكنك تسجيل ماكرو أو كتابة تعليمات برمجية لأتمتة العملية بكاملها. هناك أيضا عدد من الوظائف الإضافية الخارجية المكتوبة بواسطة موردي الجهات الخارجية، مدرجة في قسم موفري الجهات الخارجية، والتي يمكنك التفكير في استخدامها إذا لم يتوفر لديك الوقت الكافي أو الموارد اللازمة لأتمتة العملية بنفسك.
| مزيد من المعلومات | الوصف |
|---|---|
| تعبئة البيانات تلقائياً في خلايا ورقة العمل | يوضح كيفية استخدام الأمر "تعبئة ". |
|
إنشاء الجداول وتنسيقها تغيير حجم جدول من خلال إضافة صفوف وأعمدة أو إزالتها استخدام الأعمدة المحسوبة في جدول Excel |
إظهار كيفية إنشاء جدول Excel وإضافة أعمدة أو أعمدة محسوبة أو حذفها. |
| إنشاء ماكرو | يعرض عدة طرق لأتمتة المهام المتكررة باستخدام ماكرو. |
التدقيق الإملائي
يمكنك استخدام المدقق الإملائي ليس للعثور على الكلمات التي بها أخطاء إملائية فقط، ولكن أيضا للعثور على القيم التي لا يتم استخدامها بشكل متسق، مثل أسماء المنتجات أو الشركات، عن طريق إضافة هذه القيم إلى قاموس مخصص.
| مزيد من المعلومات | الوصف |
|---|---|
| التدقيق الإملائي والنحوي | يوضح كيفية تصحيح الكلمات التي بها أخطاء إملائية في ورقة عمل. |
| استخدام قواميس مخصصة لإضافة كلمات إلى المدقق الإملائي | تشرح هذه المقالة كيفية استخدام القواميس المخصصة. |
إزالة الصفوف المكررة
تشكل الصفوف المكررة مشكلة شائعة عند استيراد البيانات. من المستحسن تصفية القيم الفريدة أولا للتأكد من الحصول على النتائج المطلوبة قبل إزالة القيم المكررة.
| مزيد من المعلومات | الوصف |
|---|---|
| التصفية حسب القيم الفريدة أو إزالة القيم المتكررة | يظهر إجراءين ذا صلة وثيقة: كيفية تصفية الصفوف الفريدة وكيفية إزالة الصفوف المكررة. |
البحث عن نص واستبداله
قد تحتاج إلى إزالة سلسلة بادئة شائعة، مثل تسمية متبوعة بنقطتين ومسافة، أو لاحقة، مثل عبارة أقواس في نهاية السلسلة قديمة أو غير ضرورية. يمكنك القيام بذلك عن طريق البحث عن مثيلات لذلك النص ثم استبداله بدون نص أو نص آخر.
| مزيد من المعلومات | الوصف |
|---|---|
|
التحقق مما إذا كانت الخلية تحتوي على نص (غير حساس لحالة الأحرف) التحقق مما إذا كانت الخلية تحتوي على نص (تحسس حالة الأحرف) |
إظهار كيفية استخدام الأمر "بحث" والعديد من الدالات للبحث عن نص. |
| إزالة الأحرف من النص | يوضح كيفية استخدام الأمر "استبدال" والعديد من الدالات لإزالة النص. |
| البحث عن نص وأرقام في ورقة عمل أو استبدالها | إظهار كيفية استخدام مربعي الحوار "بحثواستبدال ". |
|
FIND وFINDB SEARCH وSEARCHB REPLACE، REPLACEB SUBSTITUTE LEFT، LEFTB RIGHT، RIGHTB LEN، LENB MID، MIDB |
هذه هي الدالات التي يمكنك استخدامها لتنفيذ مهام معالجة السلسلة المختلفة، مثل البحث عن سلسلة فرعية واستبدالها داخل سلسلة أو استخراج أجزاء من سلسلة أو تحديد طول سلسلة. |
تغيير حالة نص
يأتي النص أحيانا في حقيبة مختلطة، خاصة عندما يتعلق الأمر بالنص. باستخدام واحدة أو أكثر من دالات Case الثلاثة، يمكنك تحويل النص إلى أحرف صغيرة، مثل عناوين البريد الإلكتروني أو الأحرف الكبيرة مثل رموز المنتجات أو حالة الأحرف العادية، كالأسماء أو عناوين الكتب.
| مزيد من المعلومات | الوصف |
|---|---|
| تغيير حالة نص | يعرض كيفية استخدام دالات Case الثلاث. |
| LOWER | تحويل كافة الأحرف الكبيرة في سلسلة نصية إلى أحرف صغيرة. |
| PROPER | تحوّل هذه الدالة الحرف الأول في سلسلة نصية وأي أحرف أخرى في النص الذي يلي أي حرف آخر غير حرف أبجدي إلى حرف كبير. وتحوّل كافة الأحرف الأخرى إلى أحرف صغيرة. |
| UPPER | تحول هذه الدالة النص إلى أحرف كبيرة. |
إزالة المسافات والأحرف غير القابلة للطباعة من النص
تحتوي القيم النصية أحيانا على أحرف مسافة بادئة أو لاحقة أو متعددة مضمنة (قيم مجموعة أحرف Unicode 32 و160)، أو غير قابلة للطباعة (قيم مجموعة أحرف Unicode من 0 إلى 31 و127 و129 و141 و143 و144 و157). في بعض الأحيان، قد تسبب هذه الأحرف نتائج غير متوقعة عند الفرز أو التصفية أو البحث. على سبيل المثال، في مصدر البيانات الخارجية، قد يقوم المستخدمون بأخطاء مطبعية عن طريق إضافة أحرف مسافة إضافية بدون قصد، أو قد تحتوي البيانات النصية المستوردة من مصادر خارجية على أحرف غير قابلة للطباعة مضمنة في النص. ونظرا لأنه لا يمكن ملاحظة هذه الأحرف بسهولة، فقد يكون من الصعب فهم النتائج غير المتوقعة. لإزالة هذه الأحرف غير المرغوب فيها، يمكنك استخدام مجموعة من الدالات TRIM وCLEAN وSUBSTITUTE.
| مزيد من المعلومات | الوصف |
|---|---|
| CODE | تُرجع رمزاً رقمياً للحرف الأول في سلسلة نصية. |
| CLEAN | إزالة الأحرف ال 32 الأولى غير القابلة للطباعة في رمز ASCII المكون من 7 بت (من القيمة 0 إلى القيمة 31) من النص. |
| TRIM | تزيل حرف مسافة ASCII 7 بت (القيمة 32) من النص. |
| SUBSTITUTE | يمكنك استخدام الدالة SUBSTITUTE لاستبدال أحرف Unicode ذات القيمة الأعلى (القيم 127 و129 و141 و143 و144 و157 و160) بأحرف ASCII 7 بت التي تم تصميم الدالتين TRIM وCLEAN لها. |
إصلاح الأرقام وعلامات الأرقام
هناك مشكلتان رئيسيتان في الأرقام قد تتطلبان منك تنظيف البيانات: تم استيراد الرقم دون قصد كنص، ويجب تغيير علامة سالب إلى المعيار المناسب لمؤسستك.
| مزيد من المعلومات | الوصف |
|---|---|
| تحويل الأرقام المخزّنة كنص إلى أرقام | يعرض هذا الموضوع كيفية تحويل الأرقام المنسقة والمخزنة في الخلايا كنص، الأمر الذي قد يسبب مشاكل في الحسابات أو ينتج منه ترتيبات فرز مربكة، إلى تنسيق أرقام. |
| DOLLAR | تحول رقما إلى تنسيق نصي وتطبق رمز عملة. |
| نص | تقوم هذه الدالة بتحويل قيمة إلى نص بتنسيق أرقام محدد. |
| تم التصحيح | تقرب هذه الدالة رقما إلى عدد محدد من الأرقام العشرية، وتنسق الرقم بالتنسيق العشري باستخدام نقطة وفواصل، وترجع النتيجة كنص. |
| القيمة | تحوّل هذه الدالة سلسلة نصية تمثل رقماً إلى رقم. |
تحديد التواريخ والأوقات
نظرا لوجود العديد من تنسيقات التواريخ المختلفة، ونظرا لإمكانية الخلط بين هذه التنسيقات ورموز الأجزاء ذات التعداد الرقمي أو السلاسل الأخرى التي تحتوي على علامات الشرطة المائلة أو الواصلات، تحتاج التواريخ والأوقات إلى تحويلها وإعادة تنسيقها.
| مزيد من المعلومات | الوصف |
|---|---|
| تغيير نظام التاريخ أو التنسيق أو الترجمة الفورية للسنة المكونة من رقمين | توضيح كيفية عمل نظام التاريخ في Office Excel. |
| تحويل الأوقات | يوضح كيفية التحويل بين وحدات زمنية مختلفة. |
| تحويل التواريخ المخزّنة كنص إلى تواريخ | يعرض هذا الموضوع كيفية تحويل التواريخ المنسقة والمخزنة في الخلايا كنص، الأمر الذي قد يسبب مشاكل في الحسابات أو إرباكا في ترتيبات الفرز، إلى تنسيق تاريخ. |
| التاريخ | ترجع هذه الدالة الرقم التسلسلي المتتالي الذي يمثل تاريخا محددا. إذا كانت الخلية بالتنسيق عام قبل إدخال الدالة، فيتم تنسيق الخلية كتاريخ. |
| DATEVALUE | تحول تاريخا ممثلا بنص إلى رقم تسلسلي. |
| TIME | تُرجع هذه الدالة الرقم العشري لوقت محدد. إذا كانت الخلية بالتنسيق عام قبل إدخال الدالة، فيتم تنسيق الخلية كتاريخ. |
| TIMEVALUE | تُرجع هذه الدالة الرقم العشري للوقت ممثلاً بسلسلة نصية. إن الرقم العشري عبارة عن قيمة تتراوح من 0 (صفر) إلى 0,999999999 وتمثل الأوقات من 0:00:00 (12:00:00 ص) إلى 23:59:59 (11:59:59 م). |
دمج الأعمدة وتقسيمها
من المهام الشائعة بعد استيراد البيانات من مصدر بيانات خارجي دمج عمودين أو أكثر في عمود واحد، أو تقسيم عمود واحد إلى عمودين أو أكثر. على سبيل المثال، قد ترغب في تقسيم عمود يحتوي على اسم كامل إلى الاسم الأول واسم العائلة. أو قد ترغب في تقسيم عمود يحتوي على حقل عنوان إلى أعمدة منفصلة للشارع والمدينة والمنطقة والرمز البريدي. وقد يكون العكس صحيحا أيضا. قد ترغب في دمج عمود "الاسم الأول" و"اسم العائلة" في عمود "الاسم الكامل" أو دمج أعمدة عناوين منفصلة في عمود واحد. تتضمن القيم الشائعة الإضافية التي قد تتطلب الدمج في عمود واحد أو تقسيمها إلى أعمدة متعددة رموز المنتجات ومسارات الملفات وعناوين بروتوكول الإنترنت (IP).
| مزيد من المعلومات | الوصف |
|---|---|
|
جمع الأسماء الأولى وأسماء العائلة دمج النصوص والأرقام دمج النص مع تاريخ أو وقت جمع عمودين أو أكثر باستخدام دالة |
اعرض الأمثلة النموذجية لدمج القيم من عمودين أو أكثر. |
| تقسيم النص في أعمدة مختلفة باستخدام "معالج تحويل النص إلى أعمدة" | يعرض كيفية استخدام هذا المعالج لتقسيم الأعمدة استنادا إلى مختلف المحددات الشائعة. |
| تقسيم النص إلى أعمدة مختلفة باستخدام دالات | يوضح كيفية استخدام الدالات LEFT وMID وRIGHT وSEARCH وLEN لتقسيم عمود اسم إلى عمودين أو أكثر. |
| دمج محتويات الخلايا أو تقسيمها | توضح هذه المقالة كيفية استخدام الدالة CONCATENATE، وعامل التشغيل & (علامة العطف)، ومعالج تحويل النص إلى أعمدة. |
| دمج خلايا أو تقسيم خلايا مدمجة | توضح هذه المقالة كيفية استخدام أوامر " دمج الخلايا" و "دمج عبر" و "دمج" و"توسيط ". |
| CONCATENATE | ربط سلسلتين نصيتين أو أكثر في سلسلة نصية واحدة. |
تحويل الأعمدة والصفوف وإعادة ترتيبها
تفترض معظم ميزات التحليل والتنسيق في Office Excel وجود البيانات في جدول واحد مسطح ثنائي الأبعاد. في بعض الأحيان قد ترغب في تحويل الصفوف إلى أعمدة، والأعمدة إلى صفوف. في أحيان أخرى، لا تكون البيانات منظمة حتى وفق تنسيق جدولي، وتحتاج إلى طريقة لتحويل البيانات من تنسيق غير جدولي إلى تنسيق جدولي.
| مزيد من المعلومات | الوصف |
|---|---|
| TRANSPOSE | ترجع هذه الدالة نطاقا عموديا من الخلايا كنطاق أفقي، أو العكس. |
تسوية بيانات الجدول عن طريق الضم أو المطابقة
يستخدم مسؤولو قواعد البيانات في بعض الأحيان Office Excel للبحث عن أخطاء المطابقة وتصحيحها عند توصيل جدولين أو أكثر. قد يشتمل ذلك على التوفيق بين جدولين من أوراق عمل مختلفة، على سبيل المثال، لرؤية جميع السجلات في الجدولين أو المقارنة بين الجداول والبحث عن صفوف غير متطابقة.
| مزيد من المعلومات | الوصف |
|---|---|
| البحث عن قيم في قائمة بيانات | إظهار الطرق الشائعة للبحث عن البيانات باستخدام دالات البحث. |
| LOOKUP | ترجع هذه الدالة قيمة إما من نطاق صف واحد أو عمود واحد أو من صفيف. تتضمن الدالة LOOKUP نموذجين لبناء الجملة: نموذج الخط المتجه ونموذج الصفيف. |
| HLOOKUP | تبحث هذه الدالة عن قيمة في الصف العلوي لجدول أو صفيف من القيم، ثم ترجع قيمة في العمود نفسه من صف تحدده في الجدول أو الصفيف. |
| VLOOKUP | تبحث هذه الدالة عن قيمة في العمود الأول لصفيف جدول وترجع قيمة في الصف نفسه من عمود آخر في صفيف الجدول. |
| الفهرس | تُرجع هذه الدالة قيمة أو مرجعاً إلى قيمة من ضمن جدول أو نطاق. يتوفر نموذجان للدالة INDEX: نموذج الصفيف ونموذج المرجع. |
| MATCH | ترجع الموضع النسبي لعنصر في صفيف يتطابق مع قيمة محددة بترتيب محدد. استخدم الدالة MATCH بدلاً من إحدى دالات LOOKUP عندما تريد معرفة موضع عنصر في نطاق وليس معرفة العنصر نفسه. |
| OFFSET | تُرجع هذه الدالة مرجعاً إلى نطاق يتكوّن من عدد معين من الصفوف والأعمدة من خلية أو نطاق من الخلايا. يكون المرجع الذي يتم إرجاعه عبارة عن خلية واحدة أو نطاق من الخلايا. ويمكنك تحديد عدد الصفوف وعدد الأعمدة التي سيتم إرجاعها. |
موفرو الجهات الخارجية
فيما يلي قائمة جزئية بموفري الجهات الخارجية الذين لديهم منتجات تستخدم لتنظيف البيانات بمجموعة متنوعة من الطرق.
ملاحظة
لا توفر Microsoft الدعم لمنتجات الجهات الخارجية.