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

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

يمكنك تمديد ورقة Forms 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 الخاص بك على حقلان للتاريخ-تاريخ تسجيل الوصول وتاريخ تسجيل المغادرة. قد تختلف أسعار الفندق كل موسم ، لذا لديك طاولة منفصلة في ورقة 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

أي مدينة تُعرف باسم Big Apple؟ هذا سؤال قصير الإجابة في نماذج 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 Sheets لاستخراج الاسم الأول من الاسم الكامل واستخدام هذا الحقل لإرسال رسائل بريد إلكتروني مخصصة.

=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 وظيفة لوضع علامة على الإدخالات المكررة بسرعة في جدول بيانات ردودنا. يمكنك أيضًا استخدام التنسيق الشرطي في الأوراق لتسليط الضوء على الصفوف التي هي إدخالات مكررة محتملة.

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

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