วิธีใช้สูตรกับการตอบกลับแบบฟอร์มของ Google ในชีต

เมื่อมีคนส่งแบบฟอร์ม Google ของคุณ จะมีการแทรกแถวใหม่ใน Google ชีตที่จัดเก็บคำตอบของแบบฟอร์ม แถวสเปรดชีตนี้มีคอลัมน์ Timestamp วันที่จริงที่ส่งแบบฟอร์ม และคอลัมน์อื่นๆ ในแผ่นงานประกอบด้วยคำตอบของผู้ใช้ทั้งหมด หนึ่งคำตอบต่อคอลัมน์

คุณสามารถขยายแผ่นงาน Google ฟอร์มให้รวมฟิลด์สูตรด้วย และค่าของเซลล์จะถูกคำนวณโดยอัตโนมัติทุกครั้งที่ Google ฟอร์มเพิ่มแถวใหม่ลงในแผ่นงาน ตัวอย่างเช่น:

• คุณสามารถมีสูตรตัวเลขอัตโนมัติที่กำหนด ID ที่เพิ่มค่าอัตโนมัติแต่ตามลำดับให้กับทุกการตอบกลับของแบบฟอร์ม อาจมีประโยชน์เมื่อคุณใช้ Google ฟอร์มในการออกใบแจ้งหนี้
• สำหรับแบบฟอร์มการสั่งซื้อของลูกค้า สามารถเขียนสูตรใน Google ชีตเพื่อคำนวณยอดรวมตามการเลือกรายการ ประเทศ (อัตราภาษีแตกต่างกัน) และปริมาณที่เลือกในแบบฟอร์ม
• สำหรับแบบฟอร์มการจองโรงแรม สูตรสามารถคำนวณค่าเช่าห้องโดยอัตโนมัติตามวันที่เช็คอินและเช็คเอาต์ที่ลูกค้ากรอกใน Google ฟอร์ม
• สำหรับแบบทดสอบ ครูสามารถคำนวณคะแนนสุดท้ายของนักเรียนโดยอัตโนมัติโดยจับคู่ค่าที่ป้อนในแบบฟอร์มกับคำตอบจริงและให้คะแนน
• ถ้าผู้ใช้ส่งแบบฟอร์มหลายรายการ สูตรสามารถช่วยคุณกำหนดจำนวนรวมของรายการที่ทำโดยผู้ใช้ทันทีที่ส่งแบบฟอร์ม

สูตร Google ชีตสำหรับ Google ฟอร์ม

ในคำแนะนำทีละขั้นตอนนี้ คุณจะได้เรียนรู้วิธีเพิ่มสูตรใน Google ชีตที่เชื่อมโยงกับ Google ฟอร์ม ค่าเซลล์ที่สอดคล้องกันในแถวการตอบสนองจะถูกคำนวณโดยอัตโนมัติเมื่อมีการส่งคำตอบใหม่

เพื่อให้เข้าใจสิ่งที่เราพยายามบรรลุได้ดีขึ้น ให้เปิด Google ฟอร์มนี้แล้วส่งคำตอบ ถัดไป เปิด Google ชีตนี้แล้วคุณจะพบคำตอบของคุณในแถวใหม่ คอลัมน์ FK ถูกป้อนอัตโนมัติโดยใช้สูตร

ตัวอย่างทั้งหมดด้านล่างจะใช้ฟังก์ชัน ArrayFormula ของ Google ชีต แม้ว่าบางตัวอย่างเหล่านี้สามารถเขียนได้โดยใช้ฟังก์ชัน FILTER

ตอบกลับแบบฟอร์มหมายเลขอัตโนมัติด้วย ID ที่ไม่ซ้ำ

เปิด 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 สำหรับแถวแรกในคอลัมน์ใบแจ้งหนี้ ดังนั้นเราจึงตั้งชื่อคอลัมน์ในแถวแรก สำหรับแถวที่ตามมา หากคอลัมน์แรกของแถว (โดยปกติคือ Timestamp) ไม่ว่างเปล่า ID ใบแจ้งหนี้จะถูกสร้างขึ้นโดยอัตโนมัติ

รหัสจะเป็นเช่น 00001 , 00002 เป็นต้น คุณเพียงแค่ต้องวางสูตรคือแถวแรกของคอลัมน์และจะเติมแถวอื่นๆ ทั้งหมดในคอลัมน์โดยอัตโนมัติ

IFERROR จะคืนค่าอาร์กิวเมนต์แรกหากไม่ใช่ค่าความผิดพลาด มิฉะนั้นจะคืนค่าอาร์กิวเมนต์ที่สองหากมี หรือจะคืนค่าเป็นค่าว่างหากไม่มีอาร์กิวเมนต์ที่สอง ดังนั้นในกรณีนี้ 1/0 จึงเป็นข้อผิดพลาดและจะส่งกลับค่าว่างเสมอ

สูตรคำนวณวันที่สำหรับ Google Forms

Google Form ของคุณมีช่องวันที่สองช่อง ได้แก่ วันที่เช็คอินและวันที่เช็คเอาท์ ราคาโรงแรมอาจแตกต่างกันไปในแต่ละฤดูกาล ดังนั้นคุณจึงมีตารางแยกต่างหากใน 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 ฟอร์ม

เมืองใดได้ชื่อว่าเป็นแอปเปิ้ลลูกใหญ่ นี่เป็นคำถามแบบตอบสั้นๆ ใน 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 ที่สร้างขึ้นได้