كيفية استخدام الصيغ مع استجابات نماذج Google في جداول البيانات

عندما يرسل الأشخاص نموذج Google الخاص بك، يتم إدراج صف جديد في ورقة Google التي تخزن ردود النموذج. يحتوي صف جدول البيانات هذا على عمود الطابع الزمني، والتاريخ الفعلي لإرسال النموذج، وتحتوي الأعمدة الأخرى في الورقة على جميع إجابات المستخدم، واحدة لكل عمود.

يمكنك توسيع ورقة نماذج Google لتشمل أيضًا حقول الصيغة ويتم حساب قيم الخلية تلقائيًا عند إضافة صف جديد إلى الورقة بواسطة نموذج Google. على سبيل المثال:

• يمكن أن يكون لديك صيغة رقم تلقائي تقوم بتعيين معرف متزايد تلقائيًا ولكنه متسلسل لكل استجابة للنموذج. يمكن أن يكون مفيدًا عند استخدام نماذج Google لإعداد الفواتير.
• بالنسبة لنماذج طلبات العملاء، يمكن كتابة صيغة في جداول بيانات Google لحساب المبلغ الإجمالي بناءً على اختيار العنصر والبلد (معدلات الضرائب مختلفة) والكمية المحددة في النموذج.
• بالنسبة لنماذج حجوزات الفنادق، يمكن للصيغة حساب إيجار الغرفة تلقائيًا بناءً على تاريخ تسجيل الوصول والمغادرة الذي ملأه العميل في نموذج Google.
• بالنسبة للاختبارات، يمكن للمدرس حساب النتيجة النهائية للطالب تلقائيًا عن طريق مطابقة القيم المدخلة في النموذج مع الإجابات الفعلية وتعيين الدرجات.
• إذا قام المستخدم بإجراء عمليات إرسال متعددة للنماذج، فيمكن أن تساعدك الصيغة في تحديد إجمالي عدد الإدخالات التي قام بها المستخدم بمجرد إرسال النموذج.

صيغ جداول بيانات Google لنماذج Google

في هذا الدليل خطوة بخطوة، ستتعلم كيفية إضافة الصيغ إلى جداول بيانات Google المرتبطة بنماذج Google. سيتم حساب قيم الخلايا المقابلة في صفوف الاستجابة تلقائيًا عند إرسال استجابة جديدة.

للحصول على فهم أفضل لما نحاول تحقيقه، افتح نموذج Google هذا وأرسل ردًا. بعد ذلك، افتح ورقة Google هذه وستجد إجابتك في صف جديد. تتم تعبئة الأعمدة FK تلقائيًا باستخدام الصيغ.

ستستخدم جميع الأمثلة أدناه وظيفة ArrayFormula في جداول بيانات Google، على الرغم من أنه يمكن أيضًا كتابة بعض هذه الأمثلة باستخدام الدالة FILTER وظيفة.

استجابات نموذج الرقم التلقائي بمعرف فريد

افتح ورقة Google التي تخزن استجابات النموذج، وانتقل إلى العمود الفارغ الأول وانسخ الصيغة التالية والصقها في الصف رقم 1 من العمود الفارغ.

=ArrayFormula(
  IFS(
    ROW(A:A)=1, "Invoice ID",
    LEN(A:A)=0, IFERROR(1/0),
    LEN(A:A)>0, LEFT(CONCAT(REPT("0",5), ROW(A:A) -1),6)
  )
)

ال ROW() تقوم الدالة بإرجاع رقم الصف لصف الاستجابة الحالي. يعود 1 للصف الأول في عمود الفاتورة وبالتالي قمنا بتعيين عنوان العمود في الصف الأول. بالنسبة للصفوف اللاحقة، إذا لم يكن العمود الأول من الصف (الطابع الزمني عادةً) فارغًا، فسيتم إنشاء معرف الفاتورة تلقائيًا.

سوف تكون المعرفات مثل 00001, 00002 وهكذا. ما عليك سوى وضع الصيغة في الصف الأول من العمود وتقوم تلقائيًا بملء جميع الصفوف الأخرى في العمود.

ال IFERROR تقوم الدالة بإرجاع الوسيطة الأولى إذا لم تكن قيمة خطأ، وإلا فإنها ترجع الوسيطة الثانية إذا كانت موجودة، أو فارغة إذا كانت الوسيطة الثانية غائبة. لذلك في هذه الحالة 1/0 خطأ وبالتالي فهو يُرجع دائمًا قيمة فارغة.

صيغة حساب التاريخ لنماذج جوجل

يحتوي نموذج Google الخاص بك على حقلين للتاريخ – تاريخ تسجيل الوصول وتاريخ تسجيل المغادرة. قد تختلف أسعار الفنادق في كل موسم، لذلك يكون لديك جدول منفصل في ورقة Google يحتفظ بإيجار الغرفة شهريًا.

يحتفظ العمود C في ورقة Google بالردود الخاصة بتاريخ تسجيل الوصول بينما يقوم العمود D بتخزين تواريخ تسجيل المغادرة.

=ArrayFormula(
    IF(ROW(A:A) = 1,
      "Room Rent",
      IF(NOT(ISBLANK(A:A)),
       (D:D - C:C) *
       VLOOKUP(MONTH(D:D), 'Room Rates'!$B$2:$C$13,2, TRUE),
       ""
      )
   )
)

تستخدم الصيغ VLOOKUP للحصول على أسعار الغرف لتاريخ السفر المحدد في رد النموذج ثم يتم حساب إيجار الغرفة عن طريق ضرب إيجار الغرفة في مدة الإقامة.

ويمكن أيضًا كتابة نفس الصيغة باستخدام IFS بدلاً من VLOOKUP

=ArrayFormula(
    IF(ROW(A:A) = 1,
        "Room Rent",
        IFS(ISBLANK(C:C), "",
           MONTH(C:C) < 2, 299,
           MONTH(C:C) < 5, 499,
           MONTH(C:C) < 9, 699,
           TRUE, 199
        )
    )
)

حساب مبلغ الضريبة على أساس قيمة الفاتورة

في هذا النهج، سوف نستخدم FILTER وظيفة والتي يمكن أن تؤدي إلى صيغة أقل تعقيدا من استخدام الاستخدام IF وظيفة. الجانب السلبي هو أنه يتعين عليك كتابة عنوان العمود في الصف رقم 1 ولصق الصيغ في الصف رقم 2 (لذلك يجب أن تكون هناك استجابة نموذجية واحدة حتى تعمل الصيغة).

=ArrayFormula(FILTER(E2:E, E2:E<>"")*1.35)

نحن هنا نطبق ضريبة 35% على قيمة الفاتورة ويجب إضافة هذه الصيغة في الصف رقم 2 من العمود الذي يحمل عنوان “مبلغ الضريبة” كما هو موضح في لقطة الشاشة.

تعيين نتائج الاختبار في نماذج جوجل

ما هي المدينة المعروفة بالتفاحة الكبيرة؟ هذا سؤال ذو إجابة قصيرة في نماذج Google حتى يتمكن الطلاب من تقديم إجابات مثل نيويورك ومدينة نيويورك ونيويورك وستظل إجاباتهم صحيحة. يجب على المعلم تخصيص 10 نقاط للإجابة الصحيحة.

=ArrayFormula(
    IF(ROW(A:A) = 1,
      "Quiz Score",
      IFS(
        ISBLANK(A:A), "",
        REGEXMATCH(LOWER({B:B}), "new\s?york"), 10,
        {B:B} = "NYC", 10,
        TRUE, 0
      )
    )
)

في هذه الصيغة، نحن نستفيد من IFS وظيفة مثل IF THEN بيان في البرمجة. نحن نستخدم REGEXMATCH لمطابقة القيم مثل New York, New York, newyork دفعة واحدة باستخدام التعبيرات العادية.

ال IFS ترجع الدالة NA إذا لم يكن أي من الشروط صحيحا لذلك نضيف أ TRUE تحقق في النهاية والذي سيتم تقييمه دائمًا true إذا لم يتطابق أي من الشروط السابقة ويعود 0.

إذا كان لديك حقل نموذج يطلب من المستخدم كتابة اسمه بالكامل، فيمكنك استخدام وظيفة جداول بيانات Google لاستخراج الاسم الأول من الاسم الكامل واستخدام هذا الحقل لإرسال رسائل بريد إلكتروني مخصصة.

=ArrayFormula(
  IFS(
    ROW(A:A)=1, "First Name",
    LEN(A:A)=0, IFERROR(1/0),
    LEN(A:A)>0, PROPER(REGEXEXTRACT(B:B, "^[^\s+]+"))
  )
)

لقد استخدمنا RegexExtract الطريقة هنا لجلب السلسلة قبل المسافة الأولى في حقل الاسم. ال PROPER ستقوم الوظيفة بتكبير الحرف الأول من الاسم في حالة قيام المستخدم بإدخال اسمه بأحرف صغيرة.

ابحث عن عمليات إرسال نماذج Google المكررة

إذا كان نموذج Google الخاص بك عبارة عن عناوين بريد إلكتروني جماعية، فيمكنك استخدام هذا الحقل لاكتشاف الردود التي تم إرسالها بواسطة نفس المستخدم عدة مرات بسرعة.

=ArrayFormula(
  IFS(
    ROW(A:A)=1, "Is Duplicate Entry?",
    LEN(A:A)=0, IFERROR(1/0),
    LEN(A:A)>0, IF(COUNTIF(B:B, B:B) > 1, "YES", "")
  )
)

على افتراض أن العمود B يقوم بتخزين عناوين البريد الإلكتروني للمستجيبين للنموذج، يمكننا استخدام COUNTIF وظيفة لوضع علامة بسرعة على الإدخالات المكررة في جدول بيانات الردود لدينا. يمكنك أيضًا استخدام التنسيق الشرطي في جداول البيانات لتمييز الصفوف التي من المحتمل أن تكون إدخالات مكررة.

ردود نموذج البريد الإلكتروني مع قيم الملء التلقائي

يمكنك استخدام Document Studio لإرسال بريد إلكتروني تلقائيًا إلى المستجيبين للنموذج. يتم إرسال البريد الإلكتروني بعد ملء القيم النموذجية تلقائيًا بواسطة ورقة Google. يمكن أيضًا تضمين استجابة النموذج الأصلي والقيم المحسوبة في مستند PDF الذي تم إنشاؤه.