ฉันใช้ Google ชีตมาหลายปีแล้ว และฟีเจอร์เหล่านี้ยังคงทำให้ฉันประหลาดใจอยู่

ฉันเติบโตมากับ Microsoft Excel ฉันรู้จักสูตรลับ แป้นพิมพ์ลัด และความพิสดารต่าง ๆ เมื่อฉันย้ายงานประจำวันส่วนใหญ่ไปยัง Google Sheets ฉันคิดว่ามันเป็นเครื่องมือแบบเดียวกันกับการเพิ่มชั้นของความร่วมมือเข้ามา—มีประโยชน์ แต่ไม่ใช่สิ่งที่ฉันจำเป็นต้องศึกษา เรื่องนี้ก็ใช้ได้ดีในช่วงแรก ๆ ตลอดหลายปีที่ผ่านมา ฉันได้ลองค้นหาและฟังก์ชันบางอย่างทำให้ฉันชะงักไป บางอย่างฉันเคยคิดว่ามันซับซ้อนเกินไป ส่วนบางอย่าง ฉันก็ไม่รู้จริง ๆ ว่ามีอยู่

INDEX MATCH คือสิ่งที่ VLOOKUP ควรจะเป็นมาตลอด

ดูค่าจากทุกทิศทาง

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

ผลลัพธ์ก็เพียงค่อย ๆ กลายเป็นผิดพลาด INDEX MATCH หลบเลี่ยงทั้งหมดนั้น INDEX จะส่งค่าที่ตำแหน่งที่กำหนดในช่วงหนึ่ง; MATCH จะหาตำแหน่งของค่าที่คุณค้นหาในอีกช่วงหนึ่ง เมื่อนำมาซ้อนกัน พวกมันจะดึงข้อมูลจากทุกทิศทาง—ซ้าย ขวา ไม่สำคัญ และเพราะคุณกำลังอ้างอิงคอลัมน์เต็มแทนที่จะนับหมายเลขคอลัมน์ การแทรกหรือการลบคอลัมน์จะไม่ทำให้สูตรเสีย

มันยังคงทำงานได้ต่อเนื่อง ไวยากรณ์ใช้เวลาประมาณสิบนาทีในการทำความคุ้นเคย เมื่อเข้าใจแล้ว การกลับไปใช้ VLOOKUP จะรู้สึกเหมือนถอยหลัง INDEX MATCH ช่วยฉันประหยัดเวลาไม่รู้จบในการจับคู่จุดข้อมูลนับพันจากแผ่นและแท็บแยกต่างหากในอาชีพวิศวกรรมและการตลาดของฉัน สำหรับใครก็ตามที่ยังใช้ VLOOKUP เป็นค่าเริ่มต้นโดยเคยชิน (ทั้งใน Sheets และ Excel) INDEX MATCH คุ้มค่าที่จะใช้บ่ายหนึ่งในการเรียนรู้

ปุ่ม "ช่วยฉันเขียนสูตร" นั้นมีประโยชน์จริงๆ

Gemini สูตรไวยากรณ์ทำงานสำหรับคุณหรือไม่

สัญชาตญาณแรกของฉันคือการเพิกเฉยต่อสิ่งนี้โดยสิ้นเชิง ปุ่มที่เขียนสูตรให้คุณฟังดูเหมือนจะสร้างบางสิ่งที่ถูกต้องตามเทคนิคแต่ใช้งานจริงไม่ได้—ใกล้เคียงพอที่ดูเหมือนถูกต้อง แต่ผิดพลาดพอที่จะเสียเวลาของคุณในการแก้ไข หลังจากที่ใช้งานแถบด้านข้าง Gemini ใน Sheets จริง ๆ ฉันก็เปลี่ยนความคิด คุณอธิบายในสิ่งที่คุณต้องการเป็นภาษาอังกฤษง่าย ๆ แล้ว Sheets จะเขียนสูตรให้

ขอให้มันรวมยอดขายในคอลัมน์ C เฉพาะตรงที่คอลัมน์ B เขียนว่า "North" และมันจะสร้างสูตร SUMIF ที่ใช้งานได้พร้อมกับตัวอ้างอิงเซลล์จริงของคุณแล้ว Google ได้เพิ่มความสามารถให้ Gemini สามารถอธิบายสูตรทีละบรรทัด—แต่ละส่วนทำงานอย่างไร เพราะเหตุใดถึงโครงสร้างแบบนั้น—ซึ่งเป็นประโยชน์เมื่อผลลัพธ์เรียกใช้ฟังก์ชันที่คุณไม่ค่อยใช้ สูตรของคุณมีข้อผิดพลาดหรือไม่? Gemini สามารถอ่านข้อความข้อผิดพลาด อธิบายว่าผิดพลาดตรงไหนเป็นภาษาธรรมดา และสร้างเวอร์ชันที่แก้ไขแล้วได้

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

ตารางหมุนซ่อนพลังมากกว่าที่ชื่อบอก

สรุปหลายพันแถวในไม่กี่วินาที

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

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

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

ฟังก์ชัน LET ช่วยทำความสะอาดสูตรที่แย่ที่สุดของฉัน

ตั้งค่าคุณค่าของคุณ หยุดพูดซ้ำ

ใครก็ตามที่ได้รับสูตรสเปรดชีตที่ซับซ้อนและมีชั้นหลายชั้น จะรู้สึกหงุดหงิดเป็นพิเศษกับการพยายามทำความเข้าใจว่าสูตรนั้นทำงานอะไร สูตรที่แย่ที่สุดมักจะอ้างอิงช่วงข้อมูลเดียวกันซ้ำ ๆ—$A$2:$A$500 ปรากฏขึ้นสี่ ห้า หกครั้งในสูตรเดียว หากคุณพลาดหนึ่งช่องตอนอัปเดต ผลลัพธ์จะเบี่ยงเบนไปโดยไม่มีข้อผิดพลาดที่ชัดเจนให้สังเกต

LET แก้ไขสิ่งนี้ที่ต้นทาง คุณกำหนดตัวแปรที่มีชื่อโดยตรงภายในสูตร—มอบชื่อตัวแปรเช่น salesData ให้กับช่วงของคุณครั้งเดียว จากนั้นใช้ชื่อนั้นทุกครั้งที่ต้องการช่วงนั้น อัปเดตเพียงที่เดียว และการอ้างอิงทั้งหมดในสูตรจะอัปเดตตามด้วย นอกจากนี้ยังง่ายกว่าที่จะจับคู่ LET กับการจัดการข้อผิดพลาด การห่อผลลัพธ์ของการค้นหาใน IFNA—แทนที่จะเป็น IFERROR ที่ใช้กันทั่วไป—ช่วยให้คุณควบคุมข้อผิดพลาด #N/A ได้อย่างแม่นยำมากขึ้น โดยเฉพาะโดยไม่ทำให้ปัญหาอื่น ๆ ถูกซ่อนโดยไม่ตั้งใจ

ภายในโครงสร้าง LET การจัดการข้อผิดพลาดโดยตั้งใจประเภทนั้นสามารถอ่านได้ง่ายกว่าการฝัง IFNA ไว้ในสูตรซ้อนที่ซับซ้อนอยู่แล้ว สำหรับใครก็ตามที่ใช้ Excel ร่วมกับ Sheets Power Query ของ Excel สามารถดึงข้อมูลสดโดยตรงจากเว็บโดยไม่ต้องเขียนโค้ด—เป็นช่วงเวลา 'ฉันไม่รู้ว่ามันทำแบบนั้นได้ด้วย' ที่ซ่อนอยู่ในเครื่องมือที่คุณอาจเปิดอยู่แล้ว

Sheets ให้รางวัลกับผู้ที่อยากรู้อยากเห็น—แม้กระทั่งผู้ใช้ที่มีประสบการณ์

INDEX MATCH, การสร้างสูตร Gemini, ตารางสรุป (pivot tables), LET—ไม่มีสิ่งใดเป็นการตั้งค่าที่ซ่อนอยู่หรือวิธีแก้ไขที่ซับซ้อน พวกมันคือเครื่องมือหลักที่ฉันเคยข้ามไปเพราะฉันมีนิสัยที่ใช้งานได้ดีพอ หรือเพิกเฉยเพราะคิดว่าไม่คุ้มค่ากับการเรียนรู้ ส่วนใหญ่แล้วฉันแค่ใช้สิ่งที่รู้จาก Excel เป็นค่าเริ่มต้น แทนที่จะสำรวจว่าฟีเจอร์ของ Sheets สามารถทำอะไรได้บ้างจริงๆ ปรากฏว่ามีสิ่งที่น่าสนใจกว่าที่ฉันเคยคิดเยอะ