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

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

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

يحتفظ العمود 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 Forms

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