ฟังก์ชันที่มีชื่อ LAMBDA และ XLOOKUP ใน Google ชีต

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

สร้างแลมบ์ดา

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

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

1. คลิกขวาที่เซลล์สูตรใดก็ได้ การดำเนินการเพิ่มเติม > เลือกฟังก์ชันที่มีชื่อ

2. หากคุณมีสูตรที่ซับซ้อนเป็นพิเศษ Google มักจะแนะนำให้คุณ แต่น่าเสียดายที่ทางลัดที่แสดงจะไม่ทำงานหากไม่แนะนำ

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

Google ให้คำอธิบายเกี่ยวกับฟังก์ชันทั้งหมดมากกว่า Excel และฟังก์ชันที่มีชื่อสามารถมีตัวอธิบายได้หลายตัวหากตัวสร้างต้องการ ข้อเสียเปรียบที่สำคัญประการหนึ่งของ Google เมื่อเทียบกับ Excel คือ (ปัจจุบัน) ไม่สนับสนุนอาร์กิวเมนต์ที่เป็นตัวเลือก วิธีแก้ปัญหาแบบยาวคือการมีเงื่อนไข if คำสั่ง (เช่น ถ้าอาร์กิวเมนต์ที่สองบอกว่า 0 แล้วไม่ต้องทำอะไรเพิ่มเติม)

งานนำเข้า

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

8 ตำแหน่งงานที่มีชื่อสูงสุด

นี่คือตัวเลือกอันดับต้น ๆ ของฉันสำหรับสิ่งที่ควรจะง่ายกว่าที่เป็นอยู่ สังเกตว่า XLOOKUP สิ่งที่ฉันจะปล่อยออกไป ฉันอาจจะเขียนจดหมายที่คล้ายกันกับ ดัชนีจับคู่ และ IFERROR.

1. อายุ = DATEDIF(start_date, today(), “Y”)
2. IF_CONTAINS = IF(ISERROR(SEARCH(text_to_search, cell_to_look_in)), value_if_false, value_if_true)
3. MONTH_NAME = ข้อความ (วันที่ “mmmm”)
4. WEEKDAY_NAME = TEXT(วันที่ “วันนี้”)
5. ตัวนับ = ARRAYFORMULA (SUM (- ISERROR (ช่วง)))
6. TEXTJOINIF = ARRAYFORMULA(TEXTJOIN(“,””,”TRUE(), IF(Criteria = Criteria_range, Textjoin_range,””)))
7. UNPIVOT = QUERY (ARRAYFORMULA (แยก (แบน (แถว & “|” & คอลัมน์ & “|” & data), “|”))), “เลือก * โดยที่ Col3 ไม่ว่างเปล่า”)
8. QR_GENERATOR = IF (ISBLANK (ลิงก์รูปภาพ), “”, IMAGE (“https://chart.googleapis.com/chart?chs=150×150&cht=qr&chl=” & image_link))

โปรดทราบว่า 5 และ 6 จะต้องลบ wrapper สูตรอาร์เรย์ก่อนที่จะทำงานใน Excel และสองตัวหลังจะไม่ทำงานใน Excel เลย ใน Excel ฉันต้องการเพิ่ม กวางตุ้ง และ Kontunicive ในรายการนี้ แต่มีอยู่ใน Google ชีตโดยค่าเริ่มต้น

ความเข้ากันได้กับ Excel

หากคุณส่งออกไฟล์ Google ชีตเป็นไฟล์ xlsx (คลิก ไฟล์ > ดาวน์โหลด > xlsx) มันจะเก็บ แลมบ์ดา ค่าต่างๆ อยู่ในตัวจัดการชื่อ แต่จะเข้ากันไม่ได้จนกว่าคุณจะทำการแก้ไข Excel จำแนกสิ่งนี้ แลมบ์ดา เนื่องจากเป็นช่วงที่มีชื่อมากกว่าฟังก์ชันที่มีชื่อ จึงไม่สามารถใช้งานได้ ในการแปลง คุณต้องคลิกที่ Formulas > Name Manager คลิก Edit แล้วกด OK คุณไม่ได้ทำการปรับเปลี่ยนจริง ๆ แต่สิ่งนี้จะแจ้งให้แก้ไขปัญหาใน Excel คุณต้องทำทีละอย่างอย่างน่าเสียดาย ฟังก์ชันทั้งหมดที่ใช้ในเวิร์กชีตจะแสดงข้อผิดพลาดจนกว่าจะมีการแก้ไข โปรดทราบด้วยว่าหากฟังก์ชันที่ใช้ไม่อยู่ใน Excel ไฟล์จะแสดงคำเตือนเหล่านี้และจะไม่ทำงาน

การนำเข้าจาก Excel ไปยัง Google ทำงานได้ดีกว่า โดยจะแปลงช่วงและฟังก์ชันที่มีชื่ออย่างถูกต้องเป็นตำแหน่งของตนเอง และเก็บเฉพาะชื่อฟังก์ชัน อาร์กิวเมนต์ และคำจำกัดความของสูตร แต่เพิ่มอักขระ _xlpm ต่อหน้าทุกข้อโต้แย้ง ต่างจาก Excel ตรงที่ฟังก์ชันที่ใช้ในสเปรดชีตจะทำงานต่อไปโดยไม่มีการแก้ไข ไวยากรณ์อาจเปลี่ยนแปลงเล็กน้อยเนื่องจากบางครั้ง Google จะรวมไว้ในไฟล์ ARRAY_CONTRAIN และ/หรือ ARRAYFORMULA พวกเขาทำงานเป็นแบบปลอดภัย ซึ่งไม่ได้หยุดพวกเขาจากการทำงาน แต่บางครั้งสามารถลบออกได้ด้วยตนเอง สิ่งนี้เกี่ยวข้องกับการทำงานของอาร์เรย์ในสองแอปพลิเคชัน สูตรอาร์เรย์บางสูตรทำงานโดยค่าเริ่มต้นใน Excel แต่ในสเปรดชีต สมการต้องรวมอยู่ในไฟล์ เมทริกซ์ ฟังก์ชั่น (แต่เกือบจะยังคงทำงานในลักษณะเดียวกันเสมอ) นี่คือภาพหน้าจอของฟังก์ชันที่นำเข้าใหม่ของฉัน

งานใหม่ในสเปรดชีต

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

• แลมบ์ดา: สร้างฟังก์ชันที่มีชื่ออยู่เบื้องหลัง แต่สามารถใช้ได้อย่างชัดเจนในสเปรดชีตเช่นกัน โดยเฉพาะอย่างยิ่งกับฟังก์ชันตัวช่วยด้านล่าง

เจ็ดต่อไปคือฟังก์ชันตัวช่วยของ LAMBDA (มีอยู่ใน Excel ด้วย) แต่ซับซ้อนกว่า

• BYROW: ใช้ LAMBDA กับแต่ละแถวเพื่อจัดกลุ่มอาร์เรย์ตามแถว
• BYCOL: คล้ายกันแต่กับคอลัมน์
• ลดขนาดลง: ใช้ LAMBDA กับแต่ละค่าในอาร์เรย์เพื่อลดค่าให้เป็นผลลัพธ์สะสม
• สำรวจ: ใช้ LAMBDA กับแต่ละค่าในอาร์เรย์ ตรวจสอบและส่งคืนอาร์เรย์ของค่ากลางในแต่ละขั้นตอน
• makiray: ดำเนินการเลขคณิตโดยใช้ LAMBDA เพื่อส่งคืนอาร์เรย์ด้วยมิติที่ผู้ใช้กำหนด
• แผนที่: สามารถใช้ฟังก์ชัน LAMBDA เพื่อกำหนดค่าที่มีอยู่ให้กับค่าใหม่ของอาร์เรย์

XLOOKUP และ XMATCH

กำหนดโดยทีม Excel ให้เป็นผู้สืบทอดต่อ VLOOKUP ที่เป็นสัญลักษณ์ XLOOKUP มีประสิทธิภาพมากกว่า INDEX MATCH ในขณะที่เขียนได้ง่ายกว่าและใช้งานง่ายกว่า VLOOKUP วิดีโอนี้เปรียบเทียบใน Excel แต่ใช้หลักการเดียวกันกับสเปรดชีต

• XLOOKUP: ตัวเปลี่ยนเกมนี้รับอาร์กิวเมนต์สามอาร์กิวเมนต์และอาร์กิวเมนต์ทางเลือกสามอาร์กิวเมนต์ = XLOOKUP(search_key, input_range, result_range) สามารถค้นหาค่าเฉพาะ (search_key) ในช่วงอินพุตที่ระบุ (โดยปกติคือคอลัมน์) และส่งคืน result_range (คอลัมน์อื่น) ข้อดีบางประการของ VLOOKUP ได้แก่:

ก. ความสามารถในการมองจากขวาไปซ้ายหรือจากซ้ายไปขวา
ข. แทนที่ไฟล์ เฮโลคอป โดยมองจากบนลงล่างหรือจากล่างขึ้นบน
ค. จะมีประสิทธิภาพมากขึ้นหากมีการเพิ่มคอลัมน์ภายในตาราง
ดร.. มันถูกตั้งค่าโดยค่าเริ่มต้นเป็นการจับคู่แบบตรงทั้งหมด
อี รายการที่สี่เป็นตัวเลือก [missing_value] อินพุตเพื่อทดแทนความต้องการ IFERROR
F. รายการที่ห้าและหกใช้สำหรับการจับคู่สัญลักษณ์แทน (โดยใช้ * และ ?) และการจับคู่โดยประมาณ

ยังคงไม่สามารถจัดการกับรายการที่ซ้ำกัน ทำการจับคู่แบบคลุมเครือ (เช่น Charles ถึง Charlie) หรือค้นหาในหลายคอลัมน์

• XMATCH: นี่เป็นเวอร์ชันปรับปรุงเล็กน้อยของฟังก์ชัน MATCH ที่ให้คุณจับคู่สัญลักษณ์แทนหรือค้นหาจากล่างขึ้นบนแทนที่จะเป็นบนลงล่าง
  นี่เป็นการสรุปการปรับปรุงครั้งใหญ่ที่ฉันได้ทำกับ Google ชีตที่ฉันใช้เป็นประจำอยู่แล้ว