بعض الصيغ في جداول بيانات جوجل التي يجب معرفتها

إذا كنت مُبتدئًا مع جداول بيانات جوجل (Google Sheets) وتعاني من كميات البيانات الكبيرة فهذا هو المكان المناسب لفهم العديد من الوظائف الأساسية فيه.

14 صيغة في جداول بيانات جوجل يجب معرفتها

1. دالة ()COUNTIF للعد الشرطي

جداول بيانات جوجل (Google Sheets) تحتوي بالفعل على صيغة تعمل على حساب الخلايا التي تلبي معايير معينة حسب شروط معينة، الصيغة هي:

(COUNTIF (range, criteria)=)

على سبيل المثال لنفترض أنك تتبع تفاعل منشورات صفحة شركتك على فيس بوك (Facebook) على جداول بيانات جوجل هذه وتريد معرفة عدد المشاركات التي تفاعلت مع المستخدمين أكثر من (1)، وبافتراض أن البيانات موجودة في الخلايا من (E2) إلى (E12)، فإليك الشكل الذي ستبدو عليه صيغة (COUNTIF) في جداول بيانات جوجل: (COUNTIF (E2: E12، “> 1”)=).

2. دالة () TODAY لتحديث التاريخ الحالي

إذا كان بإمكانك تحديث التواريخ في جداول بيانات جوجل (Google Sheets) تلقائيًا فستوفر الكثير من الوقت، مثلًا عند دمج جداول بيانات جوجل مع (Google Calendar) تتم إضافة أي تحديث يتم إجراؤه داخل أوراقك تلقائيًا إلى التقويم، كل ما عليك القيام به هو كتابة دالة بسيطة لجدول بيانات جوجل وتحدث التحديثات في وقت واحد في كل من الجداول والتقويم، إليك صيغة جداول بيانات جوجل لمساعدتك:

(()TODAY=)

يمكنك أيضًا استخدام هذه الصيغة نفسها لإنشاء نطاقات زمنية، لنفترض أنّك تريد إنشاء تقرير يتكون من بيانات من آخر (3) أيام:

سيكون تاريخ الانتهاء الخاص بك هو: (()TODAY=).

وسيكون تاريخ البدء هو: (TODAY () – 3=).

3. دالة () TEXT لتحويل الأرقام إلى عملات

باستخدام صيغة (TEXT)، يمكنك أخذ أي قيمة وإعادة تنسيقها وهناك العديد من الطرق التي يُمكنك من خلالها استخدام هذه الصيغة، وهنا بعض الأمثلة:

• تغيير رقم إلى عملة.

• تغيير رقم ليكون له المزيد من المنازل العشرية.

• تغيير سلسلة التاريخ إلى تنسيق (MM / DD).

لنفترض أن لديك صفًا من الأرقام وتريد تحويلها جميعًا إلى عملات بافتراض أن خليتك الأولى هي (B3) يُمكنك استخدام الصيغة التالية: (TEXT (B3، ”0.00 $)=)، وبمُجرّد الحصول على المُخرجات المطلوبة اسحب الخلية إلى الأسفل لتطبيق الصيغة تلقائيًا على بقية البيانات.

4. دالة () SEARCH للتحقق من القيمة في سلسلة

تسمح لك صيغة جداول بيانات جوجل (Google Sheets) هذه بالتحقق ممّا إذا كانت القيمة موجودة في سلسلة، لنفترض أنّك تُجري تدقيقًا على إستراتيجية المحتوى الخاصة بك وتريد معرفة ما إذا كانت مقابلات المؤسس التي كنت تنشرها على مدونة شركتك تؤدّي أداءً جيدًا، لديك بالفعل جداول بيانات جوجل تتكون من أكثر من (100) مقالة مدونة شيوعًا على مدونة شركتك.

لذلك تريد البحث من خلال هذه القائمة وتحديد عدد مشاركاتك التي تندرج في فئة مقابلات المؤسسين (founder interviews)، إذا كانت الغالبية العظمى منهم تقع ضمن هذه الفئة فهذه علامة على أنّك على المسار الصحيح، إذا لم يكن الأمر كذلك فقد حان الوقت لتجربة نهج مختلف، وها هي الصيغة التي ستستخدمها:

(IF (SEARCH (“/ owner-interviews /”، B2)، “YES”، “”)=)

مرّة أخرى قم بمحاذاة جميع عناوين (URL) لأهم (100) مقالة مدونة من الخلية (B2) فصاعدًا، بعد ذلك انتقل إلى الخلية (C2) واكتب الصيغة أعلاه واسحبها إلى الأسفل لملء بقية الخلايا في نفس العمود، إذا كان عنوان (URL) معين يحتوي على المصطلح مقابلة مع مؤسس (founder interviews) فسترى نعم (Yes) في الخلية المجاورة له، يُمكنك الآن تقييم مدى جودة أداء مقابلات المؤسس الخاصة بك وضبط استراتيجية المحتوى الخاصة بك.

5. دالة () CONCATENATE لإلحاق خلايا متعددة

هل تحتاج إلى دمج محتويات خليتين أو أكثر في خلية ثالثة منفصلة؟ تساعدك وظيفة (CONCATENATE) في جداول بيانات جوجل على القيام بذلك، وها هي الصيغة:

(CONCATENATE (string1, string2, string3, …)=)

يُمكنك أيضًا استخدام تباين من نفس الصيغة لدمج البيانات في الخلايا ودمج التباعد بين البيانات المختلفة، للقيام بذلك قم بإضافة (“”) بينها، على سبيل المثال، ستبدو الصيغة الأولية (CONCATENATE (B3، C3)=) الآن كما يلي: (CONCATENATE (B3، “”، C3)=).

6. دالة () IF للتحقق من شرط التعبير المنطقي

إذا كنت ترغب في اختبار ما إذا كانت حالة ما إذا كانت صحيحة أم خاطئة فستكون وظيفة (IF) الأنسب لك، مبدأ عمل هذه الدالة ينص على أنّه إذا كان الشرط صحيحًا ستقوم الوظيفة بتنفيذ عملية محددة وإذا كان الشرط خاطئًا ستنفذ الوظيفة عملية مختلفة، الصيغة هي كالتالي:

(IF (test، then_true، else_value)=)

• يشير الاختبار (test) إلى التعبير الذي تريد اختباره لمعرفة ما إذا كان صحيحًا أم خطأ.

• يشير (then_true) إلى العملية التي يتم إجراؤها إذا كان الاختبار صحيحًا.

• تتعامل (else_value) مع العملية التي يتم تنفيذها في السيناريو الآخر إذا لم يكن الاختبار صحيحًا.

7. دالة () IFERROR لإرجاع قيمة خطأ الخلية

تعد وظيفة جداول بيانات جوجل (IMPORTRANGE) طريقة سهلة للتعامل مع أخطاء الخلية، لنفترض أنّه لديك خطأ في خلية مثل: (#DIV/0!) يحدث هذا عندما تحاول قسمة رقم على صفر، بافتراض أنّه لديك خلايا أُخرى تشتمل صيغها على البيانات الموجودة في هذه الخلية فإنّ هذا الخطأ يمنع العمليات الحسابية اللاحقة من العمل، للتغلب على ذلك استخدم الدالة (IFERROR) لاستبدال قيم الخطأ بقيمة جديدة تحددها، تكون الصيغة كالتالي:

(IFERROR (value، [value_if_error])=)

• تشير القيمة (value) إلى القيمة المُراد إرجاعها إذا لم تكن القيمة خطأ.

• تشير (value_if_error) إلى القيمة التي تُرجعها الدالة إذا كانت القيمة خطأ، يتم ترك هذا فارغًا بشكل افتراضي.

8. دالة () IMPORTRANGE لاستيراد نطاق من الخلايا

(IMPORTRANGE) هي وظيفة بسيطة لكنها قوية تساعدك على استيراد نطاق من الخلايا من جدول بيانات محدد، الصيغة كالتالي:

(IMPORTRANGE (spreadsheet_url، range_string)=)

ما عليك سوى إدخال عنوان (URL) لجدول البيانات، والسلسلة (مثل A1: D7) التي تريد استيرادها.

9. دالة () IMPORTFEED لاستيراد RSS أو ATOM

دالة (IMPORTFEED) مشابه لـ (IMPORTRANGE) ولكن بدلاً من استيراد نطاق من الخلايا فإنه يستورد موجز (RSS) أو (ATOM)، تكون الصيغة كالتالي:

(IMPORTFEED (url، [query]، [headers]، [num_items])=)

في البداية نضع عنوان (URL) لخلاصة (RSS) أو (ATOM) بما في ذلك البروتوكول يكون إلزامي والباقي اختياري.

10. دالة () GROWTH للتنبؤ بالاتجاه المتزايد

إذا كنت تحاول توقّع الطلب على منتجك ستساعدك وظيفة (GROWTH) على معرفة اتجاه النمو الأسي المثالي وستمنحك بيانات حول اتجاه النمو، إليك الصيغة:

(GROWTH (known_data_y، [known_data_x]، [new_data_x]، [b])=)

• (known_data_y): هي المصفوفة أو النطاق الذي يحتوي على قيم (y) المعروفة بالفعل، تُستخدم هذه القيم لمنحنى يتناسب مع منحنى نمو أسي مثالي.

• (known_data_x): هي قيم المتغيرات المستقلة التي تتوافق مع (known_data_y).

• (new_data_x): هي نقاط البيانات لإرجاع قيم (y) على ملاءمة المنحنى المثالية.

أخيرًا يتم تعيين (b) ليكون (TRUE) أو (FALSE)، إذا كان هذا صحيحًا ستحسب الدالة (b) نظرًا للصيغة الأسية العامة لـ (y = b * m ^ x) وإذا كانت خاطئة فستجبر الدالة (b) على أن تكون (1) وتحسب قيم (m).

11. دالة () SUBSTITUTE لاستبدال النص الحالي

هذه هي الصيغة المناسبة لك إذا كنت ترغب في تغيير نص موجود بنص جديد في سلسلة معينة، إليك الصيغة العامة:

(SUBSTITUTE (text_to_search، search_for، replace_with، [trouble_number])=)

على سبيل المثال إذا كنت ترغب في تغيير كل (http) إلى (https) لجميع عناوين (URL) المذكورة في جدول بيانات جوجل فأنت بحاجة إلى استخدام وظيفة (SUBSTITUTE).

12. دالة () IMPORTXML لاستيراد بيانات الموقع

إذا كنت ترغب في أخذ البيانات من بيانات صفحة ويب معينة إلى جدول بيانات جوجل (Google Spreadsheet) الخاص بك، يمكن أن تأخذك وظيفة النسخ واللصق إلى الأبد للقيام بذلك، بدلاً من ذلك استخدم الصيغة (ImportXML) لسحب المعلومات من أي من حقول (XML) لصفحة الويب، تكون الصيغة العامة كالتالي:

(IMPORTXML (url، xpath_query)=)

13. دالة () REGEXEXTRACT لاستخراج السلاسل الفرعية

إذا كان لديك العديد من الكلمات للبحث عنها من مجموعة ضخمة من البيانات وتعثرت في استخراج المصطلحات التي تريدها على جدول بيانات جوجل الخاصة بك فإن (Regexextract) هي الصيغة المناسبة لك، تكون الصيغة كالتالي:

(REGEXEXTRACT(text, regular_expression))

في هذه الصيغة النص (text) هو نص الإدخال والتعبير العادي (regular_expression) هو الجزء الأول من النص الذي يطابق هذا التعبير (القيمة التي سيتم إرجاعها).

14. دالة () TRANSPOSE لتبديل الصفوف والأعمدة

قد تتلقّى بيانات أوّلية من العملاء وترغب في تنسيقها بالطريقة التي تريد، إذا كانت هناك مجموعة ضخمة من البيانات مُرتّبة حسب الصفوف وترغب في رؤيتها من حيث الأعمدة فسيكون من الصعب نسخها ولصقها، تتيح لك هذه الصيغة (TRANSPOSE) تحويل بيانات الصفوف إلى بيانات عمودية والعكس صحيح، تكون الصيغة كالتالي:

(TRANSPOSE (array))