سيو هو روتيني. في بعض الأحيان ، عليك القيام بعمليات كئيبة للغاية مثل إزالة "الإيجابيات" في الكلمات الرئيسية. في بعض الأحيان يكون شيء أكثر تقدمًا ، مثل تحليل العلامات الوصفية أو دمج البيانات من جداول مختلفة. في أي حال ، كل هذا يستهلك الكثير من الوقت.
لكننا لا نحب الروتين. نحن نقدم 16 ميزة مفيدة من أوراق Google والتي ستعمل على تبسيط العمل مع البيانات وتساعدك على تحرير بضع ساعات عمل أو حتى أيام. (بالتأكيد ، لم تكن تعرف بوجود بعض الوظائف).
1. IF - وظيفة المنطق الأساسية
هذه هي واحدة من الميزات الأساسية المألوفة لك من Excel. وهي تساعد في حل مختلف مشاكل كبار المسئولين الاقتصاديين. تقوم صيغة IF بطباعة قيمة واحدة إذا كان التعبير المنطقي صحيحًا ، وقيمة أخرى إذا كانت خاطئة.
بناء الجملة:
=IF(_;"_";"_")
مثال هناك قائمة المفاتيح مع الترددات. هدفنا هو احتلال TOP-3. في الوقت نفسه ، نريد أن نختار فقط مثل هذه المفاتيح ، والتي ستجلب كل منا 300 زائر على الأقل شهريًا.
نحدد مقدار حركة المرور إلى المركز الثالث في القطاع العضوي. للقيام بذلك ، نذهب إلى هذه الخدمة ونرى أن المركز الثالث يؤدي إلى حوالي 10 ٪ من حركة المرور العضوية (بالطبع ، هذا الرقم غير دقيق ، ولكن هذا أفضل من لا شيء).
نؤلف تعبير IF الذي سيعيد قيمة 1 للمفاتيح ، والتي ستؤدي إلى 300 زائر على الأقل ، و 0 للمفاتيح المتبقية:
=IF(B2*0.1>=300;"1";"0")
يرجى ملاحظة أنه في السطر 7 ، تسببت الصيغة في حدوث خطأ ، حيث تم تعيين قيمة التردد بالتنسيق الخطأ. لمثل هذه الحالات ، هناك إصدار متقدم من الدالة IF - IFERROR.
يرجى ملاحظة: يتم تحديد استخدام فاصلة أو نقطة للكسور العشرية في الصيغة في إعدادات الجداول الخاصة بك.
2. IFERROR - تعيين القيمة الخاصة بك في حالة وجود خطأ
تتيح لك الوظيفة عرض نقطة التحديد في الخلية إذا تم إصدار خطأ.
بناء الجملة:
=IFERROR( ;" ")
نستخدم هذه الوظيفة في المثال الموضح أعلاه. اضبط القيمة في حالة حدوث خطأ "لا توجد بيانات".
كما ترون ، القيمة هي #VALUE! غير وجهة النظر إلى "لا توجد بيانات" مفهومة.
3. ARRAYFORMULA - تمديد الصيغة لأسفل في نقرة واحدة
في العمل مع البيانات ، في كل مرة تقريبًا يجب عليك كتابة صيغة لجميع الخلايا في عمود. "اسحبها" بالضغط على زر الماوس الأيسر أو لصق النسخ - هذا هو القرن الأخير.
يكفي التفاف الوظيفة الأصلية في دالة ARRAYFORMULA ، وسيتم تطبيق الصيغة على جميع الخلايا أدناه. علاوة على ذلك ، إذا حذفت إضافة صفوف ، فستظل الصيغة تعمل - بدون مسافات في العمليات الحسابية.
بناء الجملة:
=ARRAYFORMULA( )
مثال سنقوم تلقائيًا بتطبيق الصيغة الموضحة أعلاه لجميع الخلايا في النطاق. للقيام بذلك ، نختتم الصيغة الأصلية في ARRAYFORMULA:
=ARRAYFORMULA(IFERROR(IF(B2:B*0,1>=300;"1";"0");" "))
يرجى ملاحظة أنه بدلاً من الخلية B2 ، حددنا النطاق الذي نطبق عليه الصيغة (B2: B هو العمود B بأكمله ، بدءًا من الصف الثاني). إذا قمت بتحديد خلية واحدة ، فلن تعمل الصيغة.
اختراق الحياة. اضغط CTRL + SHIFT + ENTER بعد إدخال الصيغة الرئيسية ، وسيتم تطبيق وظيفة ARRAYFORMULA تلقائيًا.
ARRAYFORMULA لا يعمل مع جميع الوظائف. على سبيل المثال ، لا يتوافق مع GOOGLETRANSLATE و IMPORTXML ، والتي سنناقشها أدناه.
4. LEN - حساب عدد الأحرف في الخلية
هذه الميزة مفيدة بشكل خاص عند إنشاء إعلانات سياقية - عندما يكون من المهم عدم تجاوز عدد الأحرف المخصص للعناوين والأوصاف وعناوين URL المعروضة والروابط السريعة والتحسينات.
في SEO ، يتم استخدام وظيفة LEN ، على سبيل المثال ، عند تكوين العلامات الوصفية للعنوان والوصف. وظيفة الرموز بحساب المسافات.
بناء الجملة:
=LEN( )
مثال نحتاج إلى عمل عناوين لجميع صفحات الموقع. نعلم أنه يتم عرض حوالي 55 حرفًا في نتائج البحث. مهمتنا هي تأليف العناوين بحيث تكون أهم المعلومات في أول 55 حرفًا. نكتب صيغة LEN للخلايا المملوءة. الآن نحن نعرف بالتأكيد عندما نقترب من 55 حرفاً معروضة.
5. TRIM - إزالة المسافات في بداية ونهاية العبارة
عندما تقوم بتحليل الدلالات من مصادر مختلفة ، غالبًا ما تحتوي على عناصر "البيانات المهملة" - المسافات والإيجابيات والأحرف الخاصة. النظر في الميزات التي تساعد بسرعة تنظيف النواة. واحد منهم هو TRIM.
تعمل هذه الوظيفة على إزالة المسافات في بداية ونهاية العبارة المحددة في الخلية.
بناء الجملة:
=TRIM(, )
تزيل الوظيفة جميع المسافات قبل وبعد العبارة - بغض النظر عن عددها.
6. البديل - تغيير / حذف المسافات والأحرف الخاصة
وظيفة عالمية لاستبدال / حذف الحروف في الخلايا.
بناء الجملة:
=SUBSTITUTE( ;" ";" "; )
رقم المراسلات - الرقم التسلسلي لقيمة met المراد استبداله ، على سبيل المثال ، استبدال قيمة met الأولى ، وترك الباقي. المعلمة اختياري.
مثال لدينا تفريغ العبارات الرئيسية من Yandex.Wordstat. العديد من المفاتيح تحتوي على إيجابيات. نحن بحاجة إلى إزالتها.
سوف تبدو الصيغة كما يلي:
=SUBSTITUTE(B12;"+";"";)
ماذا فعلنا:
- مكان البحث - تشير إلى خلية تحتوي على بيانات ؛
- "ما الذي تبحث عنه" - تشير إلى علامة زائد تحتاج إلى إزالتها ؛
- "ما الذي يجب تغييره" - نظرًا لأنه يلزم حذف الشخصية ، فقد أشرنا إلى علامات اقتباس بدون أحرف في الداخل ؛ إذا كنا بحاجة إلى بديل ، فسنكتب هنا النص الذي نحتاج إلى استبدال علامة الجمع ؛
- رقم المطابقة - هنا لم نحدد شيئًا ، وستحذف الوظيفة جميع إيجابيات العبارة ؛ إذا حددنا 1 ، فإن الوظيفة ستحذف فقط علامة الجمع الأولى ، إذا كانت 2 - الثانية ، إلخ.
7. LOWER - ترجمة الحروف من الحروف الكبيرة إلى الحروف الصغيرة
عند تجميع المفاتيح والتحليل من مصادر مختلفة (على سبيل المثال ، من العلامات الوصفية للمنافسين) ، قد يتضح أنه سيكون لديهم أحرف كبيرة. لإحضار المفاتيح في شكل موحد ، تحتاج إلى ترجمة جميع الحروف في صغيرة. للقيام بذلك ، استخدم الدالة LOWER.
بناء الجملة:
=LOWER(, )
8. فريدة من نوعها - إخراج البيانات دون تكرار الخلايا
تقوم الوظيفة بتحليل النطاق المحدد للالتقاط الكامل وتعرض فقط الخطوط الفريدة - بنفس الترتيب كما في النطاق الأصلي.
بناء الجملة:
=UNIQUE( )
مثال جمعنا مفاتيح من Yandex.Wordstat ، نصائح البحث ، وتحليل كلمات المنافسين. بطبيعة الحال ، في هذه المجموعة من المفاتيح ، سيكون لدينا تكرارات. نحن لسنا بحاجة لهم. نحن إزالتها مع فريدة من نوعها.
إذا كنت ترغب في تنظيف نواة القمامة في ضربة واحدة ، فاستخدم أداة تطبيع الكلمات المجانية. يزيل العبارات المكررة (خاضعة للتغييرات) ، يغير الحالة ، ويزيل المسافات والأحرف الخاصة. في جوهرها ، يعمل نفس الشيء مع وظائف TRIM و SUBSTITUTE و LOWER و UNIQUE مجتمعة - بنقرة واحدة فقط.
9. البحث - العثور على البيانات في السطر
مع هذه الوظيفة ، سوف تجد بسرعة الصفوف التي تحتاج إليها مع كمية كبيرة من البيانات.
بناء الجملة:
=SEARCH(« »; )
يتم استخدام الوظيفة في مواقف مختلفة:
- تسليط الضوء على العبارات الرئيسية مع النية اللازمة (على سبيل المثال ، ذات العلامات التجارية أو ذات الصلة بموضوع معين أو منتج أو خدمة معينة) ؛
- ابحث عن أحرف محددة في عنوان URL (على سبيل المثال ، معلمات UTM أو علامة استفهام)
- ابحث عن عناوين URL لأغراض بناء الروابط - على سبيل المثال ، تحتوي على عبارة "ضيف وظيفة").
مثال لدينا قائمة مفاتيح لمتجر الأبواب عبر الإنترنت. نريد العثور على جميع طلبات البحث ذات العلامات التجارية ووضع علامات عليها في الجدول. للقيام بذلك ، استخدم الصيغة:
=SEARCH("porta";A1)
ولكن في هذا النموذج ، ستظهر لنا الصيغة في غياب كلمة "porta" في المفتاح #VALUE! .. بالإضافة إلى ذلك ، إذا كانت هذه الكلمة في الخلية المطلوبة ، فسوف تخفض الدالة عدد الحرف الذي تبدأ به هذه الكلمة. النتيجة تبدو مثل هذا:
للحصول على نتيجة البحث في نموذج مناسب لنا ، نستخدم بالإضافة إلى ذلك وظائف IF و IFERROR:
=IFERROR(IF(SEARCH("porta";A1)>0;"";"0"))
10. SPLIT - تقسيم العبارات إلى كلمات منفصلة
تقسم الدالة السلاسل إلى أجزاء باستخدام المحدد المحدد.
بناء الجملة:
=SPLIT(;"")
يجب أن يؤخذ في الاعتبار أن النصف الثاني من النص المقسم سيشغل العمود التالي. لذلك إذا كان لديك جدول ضيق ، فأنت بحاجة إلى إضافة عمود فارغ قبل تطبيق الصيغة.
مثال لدينا قائمة المجالات. نحتاج إلى تقسيمها إلى أسماء النطاقات والإضافات. في وظيفة SPLIT ، حدد نقطة كفاصل والحصول على النتيجة:
11. CONCATENATE - الجمع بين البيانات في الخلايا
هذه الوظيفة ، بخلاف الوظيفة السابقة ، تجمع البيانات من عدة خلايا.
بناء الجملة:
=CONCATENATE( 1; 2;...)
هام: لا يمكن فقط إدراج قيم الخلايا ، ولكن أيضًا الأحرف (في علامات اقتباس) في الصيغة.
مثال في المثال مع وظيفة SPLIT ، نقوم بتقسيم المجالات. لنقم بالعملية العكسية باستخدام CONCATENATE (حدد الخلايا المراد دمجها وحدد الفاصل - الفترة بينهما):
12. VLOOKUP - البحث عن القيم في نطاق بيانات مختلف
تبحث الوظيفة عن المفتاح في العمود الأول من النطاق وتقوم بإرجاع قيمة الخلية المحددة في الصف الموجود.
بناء الجملة:
=VLOOKUP(;;_;[])
مثال 1. هناك مجموعتان من العبارات الرئيسية تم الحصول عليها من مصادر مختلفة. تحتاج إلى العثور على المفاتيح في الصفيف الأول التي لم يتم العثور عليها في الصفيف الثاني. للقيام بذلك ، استخدم الصيغة:
=VLOOKUP(A2:A;B2:B;1;false)
ماذا فعلنا:
- اضبط النطاق A2: A ، الذي نأخذ منه المفاتيح للمقارنة ؛
- اضبط النطاق B2: B ، الذي نقارن به المفاتيح من العمود A ؛
- اضبط رقم العمود (1) ، الذي نقوم من خلاله بتشديد مفاتيح التطابق ؛
- خطأ - أشار إلى أننا لسنا بحاجة إلى الفرز.
غالبًا ما تستخدم وظيفة VLOOKUP عند البحث عن بيانات على أوراق مختلفة أو في مستندات مختلفة.
مثال 2. قمنا بتنزيل البيانات من Yandex.Webmaster و Google Search Console عن فهرسة الصفحات على الموقع. مهمتنا هي مقارنة البيانات وتحديد الصفحات التي يتم فهرستها في محرك بحث واحد ، ولكن لا تتم فهرستها في محرك بحث آخر.
نقوم بتحميل نتائج عمليات التحميل إلى ملف أوراق Google. على صفحة واحدة ، يوجد عنوان URL من Google ، والثاني من ياندكس.
في الخلية C2 ، نكتب وظيفة VLOOKUP. اختتم على الفور في وظيفة في ARRAYFORMULA لسحب تلقائيا إلى أسفل:
=ARRAYFORMULA(VLOOKUP(A2:A;Yandex!A2:A;1;false))
الآن نرى على الفور الصفحات التي يتم فهرستها في Google ، ولكن لا يتم فهرستها في ياندكس.
ماذا فعلنا:
- اضبط النطاق A2: A من الورقة الحالية ، والتي نأخذ منها القيمة للمقارنة ؛
- عيّن النطاق Yandex! A2: ورقة مع إلغاء التحميل من Yandex ، والتي سنقارن بها قيم URL من Google ؛
- أشار إلى رقم عمود الورقة مع التفريغ من Yandex ، والقيم التي ننسحب منها عندما تتزامن القيم من النطاقات المقارنة ؛
- خطأ - أشار إلى أننا لسنا بحاجة إلى الفرز.
إذا كنت بحاجة إلى التحقق من فهرسة صفحات معينة في نفس الوقت في Yandex و Google ، فاستخدم الأداة من PromoPult. قم بتنزيل قائمة عناوين URL وقم بتشغيل الشيك. إذا تمت فهرسة الصفحة في محرك بحث ، فسيحتوي العمود على الرقم 1 ، إن لم يكن - 0.
كيفية استخدام هذه الأداة وما هي المواقف المفيدة ، اقرأ في هذا الدليل .
13. IMPORTRANGE - استيراد البيانات من الجداول الأخرى
تتيح لك الوظيفة إدراج بيانات من جداول أخرى في الملف الحالي.
بناء الجملة:
=IMPORTRANGE(" ";" ")
مثال:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d//"," !A2:A25")
مثال أنت تروج لموقع العميل. يعمل ثلاثة متخصصين في المشروع: رابط بناء ، متخصص في تحسين محركات البحث وكاتب مؤلف. الجميع يحتفظ بالتقرير. يهتم العميل بمراقبة العملية عبر الإنترنت. يمكنك إنشاء تقرير واحد له بعلامات جدولة: "روابط" ، "مواقع" ، "نصوص". يتم سحب البيانات في كل اتجاه إلى علامات التبويب هذه باستخدام وظيفة IMPORTRANGE.
تتمثل ميزة الوظيفة في فتح إمكانية الوصول فقط إلى أوراق محددة. في الوقت نفسه ، تظل الأجزاء الداخلية للتقارير المتخصصة غير متاحة للعملاء.
14. IMPORTXML - تحليل البيانات من صفحات الويب
وظيفة النشر لتحليل البيانات من صفحات الويب باستخدام XPath.
بناء الجملة:
=IMPORTXML("url";"xpath-")
فيما يلي بعض الاستخدامات القليلة لهذه الميزة:
- استخراج البيانات الوصفية من قائمة عناوين URL (العنوان والوصف) ، وكذلك رؤوس h1-h6 ؛
- جمع البريد الإلكتروني من الصفحات ؛
- تحليل عناوين الصفحات في الشبكات الاجتماعية.
مثال نحن بحاجة إلى جمع محتويات علامات العنوان لقائمة عناوين URL. يبدو استعلام XPath الذي نستخدمه للحصول على هذا الرأس كما يلي: "// title".
ستكون الصيغة:
=IMPORTXML(A2;"//title")
IMPORTXML لا يعمل مع ARRAYFORMULA ، لذلك انسخ الصيغة يدويًا إلى جميع الخلايا.
فيما يلي استعلامات XPath الأخرى التي قد تجدها مفيدة:
- تفريغ رؤوس H1 (وقياسًا على ذلك - h2-h6): // h1
- تحليل وصف العلامات الوصفية: // meta [@ name = 'description'] / @ content
- تحليل العلامات الفوقية للكلمات الرئيسية: // meta [@ name = 'Keywords'] / @ content
- استخراج عناوين البريد الإلكتروني: // a [يحتوي على ( href ، 'mailTo:') أو يحتوي على ( href ، 'mailto:')] / @ href
- استخراج روابط لملفات التعريف على الشبكات الاجتماعية: // a [يحتوي على ( href ، "vk.com/") أو يحتوي على ( href ، "twitter.com/") أو يحتوي على ( href ، "facebook.com/") أو يحتوي على ( href أو "instagram.com/") أو يحتوي على ( href ، 'youtube.com/'))/@href
إذا كنت بحاجة إلى معرفة طلب XPath لعناصر أخرى من الصفحة ، فافتحه في Google Chrome ، وانتقل إلى عرض الشفرة ، وابحث عن العنصر ، وانقر بزر الماوس الأيمن فوقه ، وانقر فوق نسخ / نسخ XPath.
15. GOOGLETRANSLATE - ترجمة الكلمات الرئيسية وغيرها من البيانات
في المشروعات متعددة اللغات ، يجب عليك في كثير من الأحيان ترجمة العبارات الرئيسية. يتم إجراء ذلك بسهولة أكبر باستخدام وظيفة GOOGLETRANSLATE مباشرة في الجدول.
بناء الجملة:
=GOOGLETRANSLATE(«»; [_]; [_])
على سبيل المثال ، إذا كنا بحاجة إلى ترجمة المفاتيح من الروسية إلى الإنجليزية ، فستكون الصيغة:
=GOOGLETRANSLATE(A1;"ru";"en")
إذا كنا نترجم من الإنجليزية إلى الروسية ، فسنحتاج إلى تغيير ترتيب اللغات:
=GOOGLETRANSLATE(A1;"en";"ru")
لا يعمل GOOGLETRANSLATE مع ARRAYFORMULA ، لذلك ، كما هو الحال في IMPORTXML ، نقوم بتوسيع الصيغة يدويًا.
16. REGEXEXTRACT - استخراج النص المطلوب من الخلايا
تتيح لك هذه الوظيفة استخراج النص الموصوف باستخدام تعبيرات RE2 العادية المدعومة من Google من سلسلة بيانات. بناء جملة التعبيرات العادية معقد للغاية ، يمكنك العثور على المزيد من الأمثلة في مساعدة Google .
بناء الجملة:
=REGEXEXTRACT( ;” ”)
مثال 1. لدينا قائمة من عناوين المواقع. تحتاج إلى استخراج المجالات. هنا التعبير العادي سيساعدنا:
^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)
مثال 2. في قائمة العبارات الرئيسية ، تحتاج إلى البحث عن مفاتيح تحمل علامات تجارية بكلمات "porta" و "port". للبحث عن عبارات تحتوي على أي من هذه الكلمات ، استخدم التعبير العادي:
(?i)(\W|^)(porta|)(\W|$)
كما ترون ، في الجداول يمكنك قص البيانات كما تريدها ، وفهم الصيغ فقط.