ستساعدك المقالة التالية: تقنيات تدقيق SEO المتقدمة: Excel
يتطلب إعداد البيانات والتعامل معها القليل من العمل، ولكنك ستصبح محترفًا في وقت قصير مع القليل من الممارسة. انها حقا أسهل بكثير مما تعتقد.
إذا كنت ترغب في إعداد البيانات بسرعة دون عناء، فقم بتنزيل The Complete Technical SEO Audit Workbook.
نصائح لتنظيف وإعداد البيانات
سيكون أحد التحديات الأولى هو تنظيف البيانات.
بمعنى آخر، ستحتاج إلى إزالة الصفوف أو الأعمدة الفارغة التي يمكن أن تفسد بياناتك، وتسمية جميع الأعمدة بشكل صحيح، وتوحيد البيانات (على سبيل المثال، يمكن أن يكون لديك “من AZ” في مجموعة بيانات واحدة، ولكن “أريزونا” في مجموعة بيانات واحدة مجموعة بيانات أخرى. يجب أن تكون هي نفسها حتى يتمكن Excel أو Google Sheets من التعرف على أنها متطابقة.)
يعد تنظيف البيانات أمرًا صعبًا، ولكن هناك بعض الحيل التي يمكن أن تجعله أسرع.
كيفية البحث عن الصفوف الفارغة وإزالتها
تعمل جداول بيانات Google وExcel بشكل أساسي بنفس الطريقة لإزالة الفراغات. إنها مجرد أن الأوامر تبدو مختلفة قليلاً.
في إكسل:
- حدد كافة البيانات.
- انقر فوق ، وحدد “الفراغات”، ثم انقر فوق “موافق”.
- انقر بزر الماوس الأيمن على الخلية المحددة وحدد “حذف” من القائمة المنسدلة.
- اختر “الصف بأكمله” وانقر على “موافق”.
في جداول بيانات Google:
- حدد البيانات.
- انقر وألغِ تحديد المربع المجاور لـ “الفراغات”.
- ثم حدد “إنشاء مرشح” في علامة التبويب “البيانات”.
- انقر فوق رمز التصفية في الصف 1. ثم، .
- Ctrl (أو Cmd) + A وحذف الصفوف.
- قم بإيقاف تشغيل الفلتر.
كيفية البحث عن الأعمدة الفارغة وإزالتها
خطوات إزالة الأعمدة الفارغة هي في الأساس نفس خطوات إزالة الصفوف الفارغة. ومع ذلك، تخطي هذه الخطوة، وستجد أن تحليل البيانات يتضمن فجأة الكثير من الأخطاء.
في إكسل:
- حدد البيانات.
- انقر فوق، ثم انقر فوق “موافق”.
- استخدم Ctrl (أو Cmd) + Spacebar لتحديد العمود بأكمله ثم انقر فوق “حذف” في قائمة النقر بزر الماوس الأيمن.
- حدد “العمود بأكمله” وانقر على “موافق”.
في جداول بيانات Google:
- حدد البيانات.
- انقر .
- انقر فوق زر التصفية في الصف 1 وقم بإلغاء تحديد المربع بجوار “الفراغات”.
- حدد الأعمدة الفارغة، وانقر بزر الماوس الأيمن، ثم حدد “حذف العمود” من القائمة.
كيفية استخدام سلسلة للجمع بين خليتين
يمكنك دمج النص في خلية واحدة باستخدام الدالة CONCATENATE.
إنه مفيد بشكل خاص للجمع بين الأسماء الأولى والأخيرة، أو التواريخ، أو البيانات الأخرى المقدمة لك في أعمدة متعددة. ومن خلال ربطها معًا، يمكنك تنظيم البيانات ودمجها في ورقة واحدة.
في Excel وجداول بيانات Google:
لنفترض أن لدينا مجموعتين من البيانات في أوراق منفصلة:
تحتوي الورقة 1 على الاسم الأول واسم العائلة والبريد الإلكتروني.
تحتوي الورقة 2 على الاسم الأول واسم العائلة ورقم الهاتف.
دعونا نجمع البيانات من الورقتين لتسهيل التعامل مع البيانات.
أولاً، دعونا ندمج الاسم الأول والأخير في حالة وجود أكثر من جون في مجموعة البيانات الخاصة بنا.
1. أدخل عمودًا جديدًا في الورقتين 1 و2 على يمين عمود “الاسم الأخير”.
2. في C2 على كلا الورقتين، أدخل الصيغة:
=التسلسل(A2،” “،B2)
(يشير هذا إلى أنه بالنسبة للسلسلة الموجودة في A2، اترك مسافة (“”) ثم قم بإضافة محتويات B2.)
3. حدد C2، وانقر فوق الزاوية اليمنى السفلية لـ C2، واسحبه إلى أسفل بياناتك لنسخ الصيغة لأسفل.
4. الآن، قم بإدراج عمود جديد على يمين الاسم المدمج (C) في الورقة 1.
5. في الصف الأول من العمود الجديد، أدخل الصيغة التالية:
=VLOOKUP(Sheet1!C2,Sheet2!$C:$E,2,FALSE)
ستقوم هذه الصيغة بالبحث عن كل ما هو موجود في C2 في الورقة 2 (الاسم الكامل لـ John Doe) وإرجاع رقم الهاتف المقابل إلى الورقة 1.
6. انسخ الصيغة في الصف الأول من العمود الجديد في الورقة 1 والصقها في الصفوف المتبقية في العمود كما فعلت مع الصيغة المتسلسلة.
أوامر أخرى سريعة ومفيدة لتنظيف البيانات
هل تريد إزالة المساحة غير المرغوب فيها داخل الخلية؟ استخدم تريم. (يعمل في كل من Excel وGoogle Sheets.)
كيفية دمج مجموعات البيانات في جدول واحد باستخدام جداول بيانات Google
هناك مهمة أخرى شائعة ستجد نفسك تقوم بها عند التعامل مع البيانات وهي جمعها كلها في نفس الورقة.
لكن كلمة تحذير: قد يكون الجمع بين ملفات بيانات متعددة بمثابة كابوس إذا لم تقم بإعداد بياناتك. لذا، تأكد من أن جميعها لها نفس البنية والشكل قبل البدء.
لنبدأ بشيء سهل ونواصل طريقنا.
استيراد مجموعة بيانات كاملة إلى أخرى
استيراد البيانات إلى ورقة موجودة. . ثم قم بتغيير القائمة المنسدلة إلى “إدراج ورقة جديدة”. وسوف تضيف البيانات الجديدة أدناه.
استخدم وظيفة “الاستعلام”.
لنفترض أن لديك ورقة تحتوي على معدلات حركة المرور والارتداد، وتريد إنشاء ورقة بمعدلات ارتداد أقل من 50% لتحديد صفحاتك ذات الأداء الأفضل.
=QUERY(ورقة1!A2:C5، “حدد A,B,C حيث C <0.5")
ستعمل هذه الصيغة على إنشاء مجموعة بيانات جديدة تتضمن جميع المعلومات الخاصة بأي صفحات تلبي متطلباتنا بمعدل ارتداد قدره 50%.
في هذه الصيغة، الورقة1!أ2:C5 هي النطاقات التي ينظر من خلالها؛ اختر أ، ب، ج ويخبرها بالخلايا التي يجب سحبها إذا كان معدل الارتداد أقل من 0.5؛ و ج <0.5" هي دالة رياضية تطلب منها نسخ المعلومات من الورقة الأخرى فقط إذا كانت القيمة في العمود C أقل من 0.5.
استخدم وظيفة “VLOOKUP”.
تتيح لك وظيفة VLOOKUP البحث عن قيمة في مجموعة بيانات واحدة وإرجاع قيمة مقابلة من مجموعة بيانات أخرى. فيما يلي مثال لكيفية استخدامه:
ورقتان: واحدة تحتوي على معدلات الارتداد (الورقة 2) والأخرى تحتوي على أرقام حركة المرور (الورقة 1).
=VLOOKUP(الورقة1!A2، الورقة2!أ:ب، 2، خطأ)
ستقوم هذه الصيغة بالبحث عن القيمة الموجودة في الورقة 1! A2 في العمود الأول من الورقة 2 وإرجاع القيمة المقابلة من العمود الثاني من الورقة 2 عند مطابقتها.
يجب أن تكون أسماء صفحتك هي نفسها في كلا الورقتين. إذا كانت لديك “صفحة بيع” في إحدى مجموعات البيانات و”صفحة مقصودة” في الأخرى، فلن تعمل.
وظائف “INDEX” و”MATCH”.
=INDEX(Sheet2!B:B, MATCH(Sheet1!A2, Sheet2!A:A, 0))
ستقوم هذه الصيغة بالبحث عن القيمة الموجودة في الورقة 1! A2 في العمود الأول من الورقة 2 وإرجاع القيمة المقابلة من العمود الثاني من الورقة 2.
وظائف الاستيراد في شريط الصيغة
يمكن لـ IMPORTRANGE إنجاز المهمة من شريط الصيغة.
=IMPORTRANGE(“spreadsheet_url”، “range_string”)
انسخ عنوان URL لمجموعة البيانات الأخرى. (كل شيء قبل علامة #.)
ثم أخبره بالنطاق الذي سيتم النسخ عبره. (سيتم تحديثه تلقائيًا عند إجراء التغييرات على الورقة الأخرى.)
سيعطيك خطأ، لذا عليك فقط منحه حق الوصول. أنواع الاستيراد الأخرى التي يمكنك اللعب بها؟
الاستيراد عبر موجز RSS:
=IMPORTFEED(https://example.com/rss، “عنوان العناصر”)
الاستيراد عبر XML:
=IMPORTXML(“https://example.com/”، “xpath_query”)
الاستيراد عبر ملف بيانات منظم عبر الإنترنت (مثل ملف CSV):
=IMPORTDATA(“https://example.com/file.csv”)
الاستيراد من جدول على صفحة ويب:
=IMPORTHTML(“https://example.com/slug”،”الجدول”، 1)
(حيث يمثل “الجدول” الاستعلام و”1″ هو موقع بدء الفهرس.)
حلول لا صيغة
يمكن أن تكون الوظائف الإضافية لجداول بيانات Google مفيدة أيضًا إذا كنت ترغب في تجنب الصيغ. أنا أشجعك على تجربة الصيغ. إنها مرنة. (ويجعلونك تشعر بنوع من الذكاء والقوة عندما يعملون).
كيفية دمج مجموعات البيانات في جدول واحد باستخدام برنامج Excel
استيراد بسيط
اختر مجموعة البيانات. ثم أخبره إذا كان أو ملفًا. أخبره كيف يتم رسم الملف.
وأخيرًا، أخبره إذا كنت تريد إضافته إلى ورقة موجودة (وأين)، أو إذا كان يجب عليه إنشاء ورقة جديدة.
استخدم Power Query في Excel
إذا كنت تحب جلب الاستعلام في جداول بيانات Google، فسوف تحب Power Query في Excel. بعيدًا عن Python وR وتعلم لغة ترميز كاملة، فهي الميزة الأكثر فائدة وقوة التي يمكنك استخدامها للعمل مع مجموعات كبيرة من البيانات.
ستجد أيضًا وثائق ممتازة والكثير من مقاطع الفيديو (مع البيانات التي يمكنك تنزيلها لمتابعتها) لمساعدتك على تعلم كيفية استخدامها. تعد بيانات التقارير الشهرية ممارسة ممتازة.
هل تبدأ بملف Excel جديد؟ قم باستيراد ملفات البيانات الخاصة بك مباشرة إليها.
حدد نوع الملف المراد استيراده أو إضافته عبر عنوان URL واتبع المطالبات لاستيراد الملف إلى Excel.
استمر في استيراد كافة ملفات البيانات إلى Excel.
الآن، تحتاج إلى الجمع بين ملفات البيانات.
حدد تلك التي تريدها. يمكنك أيضًا إضافة عمود مخصص يحدد الملف الذي جاءت منه البيانات.
VLOOKUP في برنامج Excel
تعمل وظيفة “VLOOKUP” تمامًا مثل VLOOKUP في جداول بيانات Google. هنا مثال:
=VLOOKUP(A2، الورقة1!أ:ب، 2، خطأ)
ستقوم هذه الصيغة بالبحث عن القيمة الموجودة في A2 في العمود الأول من الورقة 2 وإرجاع القيمة المقابلة من العمود الثاني من الورقة 2.
استخدم “INDEX” و”MATCH”
يعمل كل من INDEX وMATCH بشكل رائع في Excel أيضًا.
=INDEX(Sheet2!B5:C8,MATCH(Sheet1!A2,Sheet2!A5:A8,0)MATCH(D1,Sheet2!A4:C4,0))
تعمل هذه الصيغة من خلال البحث في النطاق الموجود في الورقة 2 (باللون الأزرق) عن طريق التحقق من العمود الأول (A5:A8) بحثًا عن أي قيمة موجودة في A2 للحصول على تطابق تام (0). هذا هو كل النص باللون الأحمر.
النص الأخضر هو معلومات الصف. يقوم بالتحقق من الصف الأول (A4:C4) بحثًا عن أي قيمة يجدها في D1، ويبحث عن التطابق التام (0).
هل تريد فقط نسخ القيمة إذا كانت القيمة مبلغًا معينًا؟ 1 = أقل من و -1 = أكثر من.
إذا لم يكن هذا يبدو مثيرًا، فما عليك سوى الانتظار حتى ترى ما يمكنك فعله بهذا بعد ذلك.
العمل مع البيانات
البحث عن القيم باستخدام متغيرين مخصصين
في بعض الأحيان، تريد فقط أن تكون قادرًا على البحث عن قيمة معينة.
في هذا المثال، لدينا أرقام حركة المرور الشهرية في الأعمدة والصفحات الموجودة في الصفوف. الآن، يمكننا استخدام INDEX وMATCH للبحث عن مقدار حركة المرور التي تلقتها صفحة الاتصال (أو أي صفحة) في شهر يناير (أو أي شهر) فقط عن طريق الكتابة في G2 وG3.
الصيغة التي وضعتها في G4:
=INDEX(B2:C5,MATCH(G3,A2:A5,0),MATCH(G2,B1:C1,0))
يحتاج INDEX إلى معرفة النطاق الذي يجب أن يبحث فيه. وبعد ذلك، يريد معرفة العمود والصف الذي يحتاج إلى البحث عنه. تعمل وظائف MATCH كأرقام الصفوف والأعمدة من خلال البحث عن التطابقات التامة وفقًا لما كتبته في G2 وG3.
وهذا يعني أنه يمكنك نقل قسم الكتابة الصغير هذا إلى ورقة نظيفة وإعداد لوحة معلومات حتى يتمكن أي شخص في فريقك من البحث عن البيانات!
إذا أعجبك هذا، فجرّب ذلك باستخدام SUMIFs وAVERAGEIFs، والتي تجمع (أو متوسط) البيانات بناءً على معايير متعددة.
يعد هذا مثاليًا لتلخيص بيانات Google Analytics بسرعة بناءً على معايير محددة، مثل مصدر الزيارات والوسيط والنطاق الزمني.
إنشاء الجداول المحورية
من المحتمل أن تكون الجداول المحورية واحدة من أهم الأدوات التي ستتوفر لديك عند محاولة تلخيص كميات كبيرة من البيانات وتحويلها إلى تنسيق أكثر قابلية للإدارة.
استخدمه لتسريع عملية إعداد التقارير من خلال:
- لخص بيانات حركة المرور حسب المنتج أو الفئة أو الفترة الزمنية.
- تحليل حركة المرور مع مرور الوقت لتحديد الاتجاهات الموسمية.
- يقارن البيانات عبر فئات مختلفة، مثل معدلات الارتداد للصفحات المقصودة لمنتجات أو مناطق مختلفة.
- منقي البيانات لإظهار البيانات الخاصة بمنتج أو منطقة معينة فقط.
- احسب مقاييس مثل المتوسطات والأعداد والنسب المئوية. على سبيل المثال، متوسط عدد الزيارات يوميًا لكل منتج خلال آخر 30 يومًا.
- تعريف القيم المتطرفة مثل الصفحات أو المنتجات التي يتم بيعها أكثر أو أقل بكثير من المنتجات الأخرى.
في جداول بيانات Google:
قم بتمييز البيانات التي تريد تحليلها.
لإنشاء الجدول: لفتح ورقة جديدة تحتوي على جدول محوري فارغ.
الآن، قم بإعداد الجدول المحوري الخاص بك.
في حقلي “الصفوف” و”الأعمدة”، حدد الحقول التي تريد استخدامها كصفوف وأعمدة. في حقل “القيم”، حدد الحقل الذي تريد تحليله.
هل تحتاج إلى شيء مختلف قليلاً؟ قم بتخصيص الجدول المحوري الخاص بك عن طريق تغيير وظيفة الملخص (المجموع، المتوسط، العدد، على سبيل المثال)، وفرز البيانات وتصفيتها، وإضافة الإجماليات الفرعية والإجماليات الكلية.
نصيحة محترف: هل لديك مجموعة بيانات كبيرة؟ حدد الجدول بأكمله من خلال النقر على الزاوية العلوية اليسرى من الجدول.
في إكسل:
كما هو الحال في الأوراق، ابدأ بتمييز البيانات التي تريد تحليلها. بالنسبة لمجموعة كبيرة من البيانات، فإن النقر على الزاوية العلوية اليسرى من الجدول يعمل هنا أيضًا.
النطاق الذي تريد تحليل بياناتك فيه. وبعد ذلك، إذا كنت تريد وضع الجدول المحوري في ورقة جديدة أو في الورقة الحالية.
(إذا حددت نطاقًا فارغًا عن طريق الخطأ، فسيعطيك خطأ. لذا، إذا اشتكت، فهذا هو السبب عادةً.)
الآن، قم بإعداد الجدول المحوري الخاص بك. أضف ما تريد في الأعمدة في حقل “القيم” والصفوف في حقل “الصفوف”.
وإذا كنت بحاجة إلى تغييرها (لذلك فهي تستخدم المتوسط بدلاً من المجموع، على سبيل المثال)، قم بتغيير ذلك باستخدام زر المعلومات الموجود على القيمة المناسبة.
استخدم REGEXTRACT لسحب المعلومات من عناوين URL
تستخرج صيغة Regexextract بيانات محددة من السلاسل النصية باستخدام التعبيرات العادية. لذلك، على سبيل المثال، يمكنك استخدام Regexextract لاستخراج مصدر ووسيط حركة المرور إلى موقعك على الويب من تقارير Google Analytics.
لاستخراج المجال من عنوان URL في الخلية A2، يمكنك استخدام الصيغة التالية:
=REGEXEXTRACT(A2،”^(?:https?://)?(?:[^@\n]+@)?(?:www.)?([^:/\n]+)”))
يمكن أن تكون التعبيرات العادية معقدة، لذا من المهم اختبار صيغتك بأمثلة مختلفة للتأكد من أنها تستخرج البيانات الصحيحة.
تصغير الجداول
لتصغير الجدول، انقر على أيقونة السهم الصغير في الزاوية العلوية اليسرى من الجدول لطيه وإخفاء البيانات، مع ترك رأس الجدول فقط مرئيًا.
لتوسيع الجدول مرة أخرى، ما عليك سوى النقر على أيقونة السهم.
يمكنك أيضًا استخدام الميزة لتجميع الصفوف أو الأعمدة ذات الصلة معًا وإنشاء أقسام قابلة للطي.
تجميد الصفوف والأعمدة
يؤدي تجميد الصفوف والأعمدة إلى إبقاء صفوف أو أعمدة محددة مرئية على الشاشة أثناء التمرير خلال بقية جدول البيانات.
في جداول بيانات Google: حدد الصف أو العمود الذي تريد تجميده. انقر (أو أعلى إلى الصف/العمود الحالي وقم بتضمينه.)
في إكسل: حدد الصف أو العمود الذي تريد تجميده. ثم اضغط
إذا كنت تواجه صعوبة كبيرة في التعامل مع كمية كبيرة من البيانات، فجرّب استخدام المرشحات. يتيح لك ذلك إزالة أي بيانات غير ذات صلة وإظهار البيانات التي تلبي معايير معينة فقط.
نصائح إضافية لمجموعات البيانات الكبيرة
استخدم النطاقات المسماة لتعيين اسم محدد لنطاق من الخلايا في الورقة الخاصة بك. وهذا يمكن أن يسهل الرجوع إلى مجموعات البيانات الكبيرة وتحليلها.
تجنب استخدام الكثير من الصيغ. يؤدي ذلك إلى إبطاء أداء الورقة الخاصة بك ويمكن أن يجعل حياتك صعبة للغاية، خاصة عندما تبدأ في ربط عدة صيغ معًا.
الاستفادة من التنسيق الشرطي:
في جداول بيانات Google:
حدد نطاق الخلايا التي تريد تطبيق التنسيق الشرطي عليها.
في لوحة “قواعد التنسيق الشرطي” الموجودة على الجانب الأيسر من الشاشة، حدد نوع التنسيق الذي تريد تطبيقه. هناك العديد من الخيارات للاختيار من بينها، بما في ذلك “مقياس اللون” و”مجموعة الأيقونات” و”الصيغة المخصصة”.
في إكسل:
حدد نطاق الخلايا الذي تريد تطبيق التنسيق الشرطي عليه ثم انقر فوقه
في القائمة المنسدلة، حدد نوع التنسيق الذي تريد تطبيقه، بما في ذلك “قواعد تمييز الخلايا”، و”القواعد العلوية/السفلى”، و”أشرطة البيانات”.
خاتمة
قد يبدو برنامجي Excel وGoogle Sheets مبسطين للغاية بحيث لا يمكنهما فعل الكثير، أو قد يبدوان معقدين للغاية بحيث لا يمكن تعلمهما، ولكن أيًا من هذه الافتراضات غير صحيح.
مع وجود عدد قليل من الصيغ والحيل في جعبتك، ستجد بسرعة أنها من بين أقوى الأدوات وأكثرها أهمية في صندوق أدوات تحسين محركات البحث لديك.
هل أنت مستعد لتصبح محترفًا في مجال البيانات؟ قم بتنزيل المصنف الكامل للتدقيق الفني لتحسين محركات البحث (SEO) وأتقن فن إعداد البيانات والعمل معها.