ฟังก์ชัน QUERY ให้คุณดึงข้อมูลจากช่วงหรือทั้งแผ่นของข้อมูลโดยใช้คำสั่งการสืบค้นที่ยืดหยุ่น การเรียนรู้วิธีใช้ฟังก์ชัน QUERY ของ Google ชีตช่วยให้คุณเข้าถึงเครื่องมือค้นหาที่มีประสิทธิภาพ
หากคุณเคยเขียนคำสั่ง SQL เพื่อดึงข้อมูลออกจากฐานข้อมูล คุณจะรู้จักฟังก์ชัน QUERY หากคุณไม่มีประสบการณ์ด้านฐานข้อมูล ฟังก์ชัน QUERY ก็ยังเรียนรู้ได้ง่ายมาก
ฟังก์ชัน QUERY คืออะไร
ฟังก์ชั่นมีสามพารามิเตอร์หลัก:
=QUERY(ข้อมูล แบบสอบถาม ส่วนหัว)
พารามิเตอร์เหล่านี้ค่อนข้างตรงไปตรงมา
- Data: ช่วงของเซลล์ที่มีข้อมูลต้นทาง
- Query: ข้อความค้นหาที่อธิบายวิธีดึงสิ่งที่คุณต้องการจากแหล่งข้อมูล
- Headers: อาร์กิวเมนต์ทางเลือกที่ให้คุณรวมส่วนหัวหลายรายการในช่วงต้นทางเป็นส่วนหัวเดียวในแผ่นงานปลายทาง
ความยืดหยุ่นและพลังของฟังก์ชัน QUERY มาจากอาร์กิวเมนต์ Query ดังที่คุณเห็นด้านล่าง
วิธีสร้างสูตร QUERY อย่างง่าย
สูตร QUERY มีประโยชน์อย่างยิ่งเมื่อคุณมีชุดข้อมูลขนาดใหญ่มากซึ่งคุณต้องแยกและกรองข้อมูล
ตัวอย่างต่อไปนี้ใช้สถิติผลการปฏิบัติงานของโรงเรียนมัธยมศึกษาตอนปลายของสหรัฐฯ SAT ในตัวอย่างแรกนี้ คุณจะได้เรียนรู้วิธีเขียนสูตร QUERY ง่ายๆ ที่ส่งกลับโรงเรียนมัธยมทั้งหมดและข้อมูลของโรงเรียนที่มี "นิวยอร์ก" ในชื่อโรงเรียน
-
สร้างแผ่นงานใหม่สำหรับวางผลลัพธ์การสืบค้น ในเซลล์ด้านซ้ายบน ให้พิมพ์ =Query(เมื่อทำเช่นนี้ คุณจะเห็นหน้าต่างป๊อปอัปพร้อมอาร์กิวเมนต์ที่จำเป็นต้องมี ตัวอย่าง และข้อมูลที่เป็นประโยชน์เกี่ยวกับฟังก์ชัน
Image -
ถัดไป สมมติว่าคุณมีข้อมูลต้นฉบับใน Sheet1 ให้กรอกฟังก์ชันดังนี้:
=แบบสอบถาม(Sheet1!A1:F460, "SELECT B, C, D, E, FHERE B LIKE '%New York%'")
สูตรนี้มีอาร์กิวเมนต์ต่อไปนี้:
- ช่วงของเซลล์: ช่วงของข้อมูลใน A1 ถึง F460 ใน Sheet1
- SELECT Statement : คำสั่ง SELECT ที่เรียกข้อมูลใดๆ ในคอลัมน์ B, C, D, E และ F โดยที่คอลัมน์ B มีข้อความที่มีคำว่า "New York" " ในนั้น
Image อักขระ "%" เป็นอักขระตัวแทนที่คุณสามารถใช้ค้นหาส่วนของสตริงหรือตัวเลขในชุดข้อมูลใดก็ได้ การเว้น "%" ไว้ด้านหน้าสตริงจะคืนค่าชื่อโรงเรียนที่ขึ้นต้นด้วยข้อความ "New York"
-
หากคุณต้องการค้นหาชื่อโรงเรียนที่ต้องการจากรายการ คุณสามารถพิมพ์ข้อความค้นหา:
=แบบสอบถาม(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B='New York Harbor High School'")
การใช้ตัวดำเนินการ =จะค้นหาค่าที่ตรงกันทั้งหมด และสามารถใช้เพื่อค้นหาข้อความหรือตัวเลขที่ตรงกันในคอลัมน์ใดก็ได้
Image
เนื่องจากฟังก์ชัน QUERY ของ Google ชีตนั้นเข้าใจง่ายและใช้งานได้ง่ายมาก คุณจึงสามารถดึงข้อมูลใดๆ ออกจากชุดข้อมูลขนาดใหญ่ได้โดยใช้ข้อความค้นหาง่ายๆ เช่นเดียวกับด้านบน
ใช้ฟังก์ชัน QUERY กับตัวดำเนินการเปรียบเทียบ
ตัวดำเนินการเปรียบเทียบช่วยให้คุณใช้ฟังก์ชัน QUERY เพื่อกรองข้อมูลที่ไม่ตรงตามเงื่อนไขออก
คุณมีสิทธิ์เข้าถึงโอเปอเรเตอร์ต่อไปนี้ทั้งหมดในฟังก์ชัน QUERY:
- =: ค่าตรงกับค่าที่ค้นหา
- <: ค่าน้อยกว่าค่าที่ค้นหา
- >: ค่ามากกว่าค่าที่ค้นหา
- <=: ค่าน้อยกว่าหรือเท่ากับค่าที่ค้นหา
- >=: ค่ามากกว่าหรือเท่ากับค่าที่ค้นหา
- และ !=: ค่าการค้นหาและค่าแหล่งที่มาไม่เท่ากัน
ใช้ชุดข้อมูลตัวอย่าง SAT เดียวกันด้านบน มาดูกันว่าโรงเรียนใดมีค่าเฉลี่ยคณิตศาสตร์เฉลี่ยมากกว่า 500 คะแนน
-
ในเซลล์ด้านซ้ายบนของแผ่นงานเปล่า ให้กรอกฟังก์ชัน QUERY ดังนี้:
=แบบสอบถาม(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE E > 500")
สูตรนี้เรียกข้อมูลใดๆ ที่คอลัมน์ E มีค่าที่มากกว่า 500
Image -
คุณยังสามารถรวมตัวดำเนินการเชิงตรรกะ เช่น AND และ OR เพื่อค้นหาหลายเงื่อนไข ตัวอย่างเช่น หากต้องการดึงคะแนนเฉพาะโรงเรียนที่มีผู้สอบมากกว่า 600 คนและมีค่าเฉลี่ยการอ่านที่สำคัญระหว่าง 400 ถึง 600 คุณจะต้องพิมพ์ฟังก์ชัน QUERY ต่อไปนี้:
=แบบสอบถาม(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE C > 600 AND D > 400 AND D < 600")
Image - ตัวดำเนินการเปรียบเทียบและตรรกะช่วยให้คุณดึงข้อมูลจากสเปรดชีตต้นทางได้หลายวิธี ช่วยให้คุณกรองข้อมูลที่สำคัญออกจากชุดข้อมูลขนาดใหญ่ได้
การใช้งานขั้นสูงของฟังก์ชัน QUERY
มีคุณลักษณะอื่นๆ อีกสองสามอย่างที่คุณสามารถเพิ่มลงในฟังก์ชัน QUERY ด้วยคำสั่งเพิ่มเติม คำสั่งเหล่านี้ช่วยให้คุณรวมค่า นับมูลค่า ข้อมูลคำสั่งซื้อ และค้นหาค่าสูงสุดได้
-
การใช้ GROUP ในฟังก์ชัน QUERY ทำให้คุณสามารถรวมค่าต่างๆ ได้หลายแถว ตัวอย่างเช่น คุณสามารถเฉลี่ยเกรดการทดสอบสำหรับนักเรียนแต่ละคนโดยใช้ฟังก์ชัน GROUP เมื่อต้องการทำสิ่งนี้ ให้พิมพ์:
=แบบสอบถาม(Sheet1!A1:B24, "SELECT A, AVG(B) GROUP BY A")
Image -
การใช้ COUNT ในฟังก์ชัน QUERY คุณสามารถนับจำนวนโรงเรียนที่มีคะแนนเฉลี่ยการเขียนมากกว่า 500 โดยใช้ฟังก์ชัน QUERY ต่อไปนี้:
=QUERY(Sheet1!A2:F460, "SELECT B, COUNT (F) GROUP BY B")
Image -
ใช้ ORDER BY ในฟังก์ชัน QUERY คุณสามารถค้นหาโรงเรียนที่มีคะแนนเฉลี่ยทางคณิตศาสตร์สูงสุดและจัดลำดับรายการด้วยคะแนนเหล่านั้น
=QUERY(Sheet1!A2:F460, "SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)")
Image
