في حال استخدام البيانات كثيرًا، فمن المؤكد أننا نعرف أن احتمالات الحصول باستمرار على البيانات بالتنسيق الذي تحتاجه للتحليل منخفضة جدًا. وغالبًا ما يلجأ أولئك الذين يقومون بقدر كبير من معالجة البيانات وإعادة تنسيقها إلى لغات البرمجة النصية مثل (Python) أو الأدوات المتخصصة مثل (OpenRefine) أو (R). ولكن اتضح أن هناك الكثير من البيانات التي يمكننا القيام بها في جدول بيانات (Excel) إذا كنا نعرف كيفية تحضير الصيغ المناسبة.
أهم خمسة نصائح لمعالجة البيانات في إكسل:
1- تقسيم التواريخ إلى حقول منفصلة:
يمكن استخراج السنة والشهر واليوم في حقول منفصلة من حقل التاريخ في Excel باستخدام الصيغ((Year(CellWithDate), =MONTH(CellWithDate= و (DAY(CellWithDate=).
إن تقسيم التواريخ بهذه الطريقة، حسب السنة والشهر واليوم من الشهر، يعمل أيضًا في (Microsoft Access)، يمكنك أيضًا الحصول على يوم في الأسبوع لأي تاريخ في (Excel) باستخدام((WEEKDAY(CellWithDate=).
يقوم الخيار الافتراضي بإرجاع الأرقام، وليس أسماء أيام الأسبوع، مع 1 ليوم الأحد و 2 ليوم الاثنين وهكذا. لعرض اسم أيام الأسبوع بدلاً من رقم، سنقوم بتطبيق تنسيق مخصص على الخلايا التي تحتوي على أرقام أيام الأسبوع، باستخدام تنسيق الخلايا (Format cells)> مخصص (Custom) ، ثم نكتب ddd في مربع النص (Type) للحصول على ثلاث حروف من اسم اليوم أو dddd لاسم اليوم الكامل.
2- حساب العمر لشخص ما في إكسل:
إذا كان لدينا تاريخ ميلاد شخص ما، فيمكننا العثور على عمره الحالي في أي يوم نفتح فيه جدول البيانات باستخدام الدالتين ( ()DATEDIF=، و( ()TODAY=) هذه الدالة كما قد تتخيل، يعطي التاريخ الحالي).
يعطي ()DATEDIF الفرق بين تاريخين في وحدات السنوات (“y”) ، الأشهر (“m”) أو الأيام (“d”
باستخدام الصيغة: ((DATEDIF(Date1, Date2, Unit of measure=).
للحصول على العمر الحالي بالسنوات، نستخدم ((DATEDIF(CellWithBirthday,TODAY(), “y”)=)
نلاحظ أن وحدة السنوات ترجع الأعمار بأعداد صحيحة ولا يتم تقريبها.
3- إنشاء عدة صفوف من صف واحد فقط:
نحتاج أحيانًا إلى بيانات بتنسيق مع صف واحد لكل ملاحظة، ولكننا بالفعل بدلاً من ذلك لدينا ملاحظات متعددة لكل صف. كما لدينا طريقة واحدة لحل ذلك، حيث يُعدّ برنامج تصوّر (Tableau) أحد هذه الأدوات التي تحتاج إلى نقطة بيانات واحدة لكل صف، وليس نقاط بيانات متعددة لكل صف، لذلك أنشأ أحد المطورين أداة Tableau) Reshaper Tool) التي تعمل مع الإصدارات الأخيرة من (Excel) على (Windows).
يمكننا تنزيل هذه الأداة المجانية من موقع (Tableau). وعلى الرغم من أن إحدى الوظائف الإضافية تقول إنها مخصصة لبرنامج (Excel 2010)، إلّا أنها عملت بشكل جيد مع (Excel 2013) على جهاز الكمبيوتر الذي يعمل بنظام (Windows 8).
4- إنشاء المزيد من البيانات القابلة للفرز sortable data بسهولة:
هناك مشكلة أخرى شائعة في تنسيق البيانات، وهي عندما نحصل على “جدول بيانات” لا يشبه جدول البيانات القابل للفرز ويشبه إلى حد بعيد مستند (Word) برؤوس الأعمدة.
مثال: جدول بيانات يحتوي على اسم فريق في صف واحد متبوعًا بكل اللاعبين في هذا الفريق، ثم اسم فريق آخر أدناه متبوعًا باللاعبين وهكذا. ومن الصعب تحليل ورقة عمل؛ حيث تتداخل رؤوس الأعمدة مع البيانات، ونظرًا لأنه لا يمكنك بسهولة فرز البيانات أو تصفيتها أو تصورها حسب الفريق. كما أن هناك طريقة واحدة للتعامل مع هذا، وهو إضافة عمود جديد مع اسم الفريق لكل لاعب.
من الصعب تحليل ورقة العمل، حيث تتداخل رؤوس الأعمدة مع البيانات؛ ونظرًا لأنه لا يمكننا بسهولة فرز البيانات أو تصفيتها أو تصورها حسب الفريق. كما تتمثّل إحدى طرق التعامل مع هذا في إضافة عمود جديد باسم الفريق، وفي هذه الحالة اسم الفريق لكل لاعب.
الحيلة هي أنك بحاجة إلى نمط لتتبعه، وفي المثال أعلاه، يكون عمود المركز فارغًا لصفوف اسم الفريق ولكن يتم ملؤه لصفوف اللاعب. ومن خلال ملء الخلية الأولى فقط باسم الفريق يدويًا، يمكنك بعد ذلك استخدام هذه الصيغة لملء الباقي تلقائيًا: ((IF(B3=””,A3,C2=)
بمعنى إذا كانت الخلية (B3) فارغة، فسوف نقوم بتعبئة قيمة الخلية في العمود الأول من نفس الصف (في هذه الحالة (A3)). بخلاف ذلك، نملأ القيمة من الخلية الموجودة فوقها مباشرةً (في هذه الحالة (C2)، والتي يجب أن تكون اسم الفريق من الصف أعلاه لجميع صفوف اللاعب). كما يجب التأكد من البدء بصف اللاعب الأول بعد إدخال صف الرأس الأول يدويًا.
5- إنشاء عمود جديد:
ربما نعلم أنه يمكننا إجراء بحث واستبدال في (Excel) بعنصر تحكم نموذجي في محرر النصوص، والذي هو البحث والاستبدال (find-and-replace). ولكن هل تعلم أنه يمكننا أيضًا إنشاء عمود جديد تمامًا في (Excel) استنادًا إلى البحث والاستبدال في عمود موجود؟ وهذا يحتاج إلى الدالة (= SUBSTITUTE) ، باستخدام بناء الجملة: ((“SUBSTITUTE(CellWithText, “oldtext”, “newtext=).
