الكلمات التي بها أخطاء إملائية، والمسافات اللاحقة العنيدة، والبادئات غير المرغوب فيها، والحالات غير الصحيحة، والأحرف غير المطبوعة تجعل انطباعا أول سيئا. وهذه ليست حتى قائمة كاملة بالطرق التي يمكن أن تتسخ بها بياناتك. شموا أكمامكم. حان الوقت لبعض عمليات التنظيف الرئيسية لأوراق العمل باستخدام 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 الثلاث، يمكنك تحويل النص إلى أحرف صغيرة، مثل عناوين البريد الإلكتروني أو الأحرف الكبيرة، مثل رموز المنتج أو الحالة المناسبة، مثل الأسماء أو عناوين الكتب.
| مزيد من المعلومات | الوصف |
|---|---|
| تغيير حالة نص | يوضح كيفية استخدام دالات الحالة الثلاث. |
| 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 الدعم لمنتجات الجهات الخارجية.
| الموفر | المنتج |
|---|---|
| الوظيفة الإضافية Express Ltd. | Ultimate Suite for Excel، معالج دمج الجداول، مزيل مكرر، معالج دمج أوراق العمل، معالج دمج الصفوف، منظف الخلايا، منشئ عشوائي، دمج الخلايا، أدوات سريعة ل Excel، فارز عشوائي، بحث متقدم & استبدال، مكتشف مكرر غامض، تقسيم الأسماء، معالج تقسيم الجدول، مدير المصنف |
| Add-Ins.com | مكتشف مكرر |
| AddinTools | AddinTools Assist |
| WinPure |
ListCleaner Lite ListCleaner Pro |