LAMBDA คือ Super Function ใหม่ของ Excel

ในขณะนี้ Microsoft Excel มีฟังก์ชันเวิร์กชีตเกือบห้าร้อยฟังก์ชันที่พร้อมใช้งานผ่านหน้าต่างตัวช่วยสร้างฟังก์ชัน – ปุ่ม fx ในแถบสูตร นี่เป็นชุดที่ดีมาก แต่อย่างไรก็ตาม ผู้ใช้เกือบทุกคนเกือบทุกคนต้องเผชิญกับสถานการณ์ที่รายการนี้ไม่มีฟังก์ชันที่เขาต้องการ ไม่ช้าก็เร็ว เพียงเพราะไม่มีใน Excel

จนถึงขณะนี้ วิธีเดียวที่จะแก้ปัญหานี้คือมาโคร นั่นคือ การเขียนฟังก์ชันที่ผู้ใช้กำหนดเอง (UDF = User Defined Function) ใน Visual Basic ซึ่งต้องใช้ทักษะการเขียนโปรแกรมที่เหมาะสม และบางครั้งก็ไม่ง่ายเลย อย่างไรก็ตาม ด้วยการอัปเดต Office 365 ล่าสุด สถานการณ์ได้เปลี่ยนไปในทางที่ดีขึ้น – มีการเพิ่มฟังก์ชัน "wrapper" พิเศษใน Excel แลมบ์ดา. ด้วยความช่วยเหลือของมัน ตอนนี้งานในการสร้างฟังก์ชั่นของคุณเองได้รับการแก้ไขอย่างง่ายดายและสวยงาม

ลองดูหลักการใช้งานในตัวอย่างต่อไปนี้

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

ขั้นตอนที่ 1. เขียนสูตร

เริ่มจากความจริงที่ว่าตามปกติเราจะเขียนสูตรในเซลล์ชีตที่คำนวณสิ่งที่เราต้องการ ในกรณีของเลขควอเตอร์ สามารถทำได้ เช่น

ขั้นตอนที่ 2 สรุปใน LAMBDA และการทดสอบ

ตอนนี้ได้เวลาใช้ฟังก์ชัน LAMBDA ใหม่และรวมสูตรของเราไว้ด้วย ไวยากรณ์ของฟังก์ชันมีดังนี้:

=แลมบ์ดา(ตัวแปร1; ตัวแปร2; ... ตัวแปรN ; การแสดงออก)

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

ในกรณีของเรา จะมีตัวแปรเพียงตัวเดียว – วันที่ที่เราคำนวณตัวเลขไตรมาส ลองเรียกตัวแปรของมันว่า d จากนั้นห่อสูตรของเราในฟังก์ชัน แลมบ์ดา และแทนที่ที่อยู่ของเซลล์ A2 ดั้งเดิมด้วยชื่อตัวแปรที่สมมติขึ้น เราได้รับ:

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

ขั้นตอนที่ 3 สร้างชื่อ

ตอนนี้สำหรับส่วนที่ง่ายและสนุก พวกเราเปิด ชื่อผู้จัดการ แถบ สูตร (สูตร — ตัวจัดการชื่อ) และสร้างชื่อใหม่ด้วยปุ่ม สร้างบัญชีตัวแทน (สร้าง). คิดและป้อนชื่อสำหรับฟังก์ชันในอนาคตของเรา (เช่น นมกวาตาลา) และในสนาม ลิงค์ (อ้างอิง) คัดลอกอย่างระมัดระวังจากแถบสูตรและวางฟังก์ชันของเรา แลมบ์ดาโดยไม่มีอาร์กิวเมนต์สุดท้าย (A2) เท่านั้น:

ทุกอย่าง. หลังจากคลิกที่ OK ฟังก์ชันที่สร้างขึ้นสามารถใช้ในเซลล์ใดก็ได้บนแผ่นงานของเวิร์กบุ๊กนี้:

ใช้ในหนังสือเล่มอื่นๆ

LAMBDA และไดนามิกอาร์เรย์

ฟังก์ชันแบบกำหนดเองที่สร้างด้วยฟังก์ชัน แลมบ์ดา รองรับการทำงานกับไดนามิกอาร์เรย์ใหม่และฟังก์ชัน (FILTER, ยูนิค, ระดับการศึกษา) เพิ่มใน Microsoft Excel ในปี 2020

สมมติว่าเราต้องการสร้างฟังก์ชันที่ผู้ใช้กำหนดเองซึ่งจะเปรียบเทียบสองรายการและคืนค่าความแตกต่างระหว่างรายการเหล่านั้น ซึ่งเป็นองค์ประกอบจากรายการแรกที่ไม่ได้อยู่ในรายการที่สอง งานของชีวิตใช่มั้ย? ก่อนหน้านี้ สำหรับสิ่งนี้ พวกเขาใช้ทั้งฟังก์ชัน a la VPR (วีลุคอัพ)หรือ PivotTable หรือคิวรี Power Query ตอนนี้คุณสามารถทำได้ด้วยสูตรเดียว:

ในเวอร์ชันภาษาอังกฤษจะเป็น:

=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)

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

หากแหล่งข้อมูลไม่ธรรมดา แต่เป็นตารางที่ "ฉลาด" ฟังก์ชันของเราจะรับมือได้โดยไม่มีปัญหา:

อีกตัวอย่างหนึ่งคือการแบ่งข้อความแบบไดนามิกโดยแปลงเป็น XML แล้วแยกวิเคราะห์ทีละเซลล์โดยใช้ฟังก์ชัน FILTER.XML ที่เราเพิ่งแยกวิเคราะห์ เพื่อไม่ให้ทำซ้ำสูตรที่ซับซ้อนนี้ด้วยตนเองทุกครั้ง จะง่ายกว่าที่จะห่อด้วย LAMBDA และสร้างช่วงไดนามิกตามนั้น กล่าวคือ ฟังก์ชันใหม่ที่กะทัดรัดและสะดวกสบาย ตั้งชื่อเช่น RAZDTEXT:

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

=แลมบ์ดา(t;d; ทรานสโพส(FILTER.XML(“"&ทดแทน(t;d? "«)&»“;”//จ”)))

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

การแจงนับซ้ำของอักขระ

ตัวอย่างก่อนหน้านี้ทั้งหมดได้แสดงให้เห็นเพียงด้านเดียวที่ชัดเจนที่สุด ด้านของฟังก์ชัน LAMBDA – ใช้เป็น "ตัวห่อหุ้ม" สำหรับการรวมสูตรแบบยาวไว้ในนั้น และทำให้การป้อนข้อมูลง่ายขึ้น อันที่จริงแลมบ์ดามีอีกด้านที่ลึกกว่ามากซึ่งเปลี่ยนเป็นภาษาโปรแกรมที่เกือบจะเต็มเปี่ยม

ความจริงก็คือคุณลักษณะที่สำคัญพื้นฐานของแลมบ์ดาคือความสามารถในการนำไปใช้ใน การเรียกซ้ำ – ตรรกะของการคำนวณ เมื่ออยู่ในขั้นตอนการคำนวณ ฟังก์ชันจะเรียกตัวเอง จากนิสัย อาจฟังดูน่าขนลุก แต่ในการเขียนโปรแกรม การเรียกซ้ำเป็นเรื่องปกติ แม้แต่ในมาโครใน Visual Basic คุณก็สามารถใช้งานได้ และตอนนี้ อย่างที่คุณเห็น มันมาถึง Excel แล้ว ลองทำความเข้าใจเทคนิคนี้ด้วยตัวอย่างที่ใช้งานได้จริง

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

อย่างไรก็ตาม เมื่อเทียบกับตัวอย่างก่อนหน้าที่ไม่เรียกซ้ำ ปัญหาสองข้อรอเราอยู่

1. เราจะต้องคิดชื่อสำหรับฟังก์ชันของเราก่อนจึงจะเริ่มเขียนโค้ดได้ เพราะในนั้นชื่อนี้จะใช้เรียกฟังก์ชันนั้นเองอยู่แล้ว
   
2. การป้อนฟังก์ชันแบบเรียกซ้ำลงในเซลล์และการดีบักโดยการระบุอาร์กิวเมนต์ในวงเล็บหลัง LAMBDA (เหมือนที่เราทำก่อนหน้านี้) จะไม่ทำงาน คุณจะต้องสร้างฟังก์ชั่นทันที "ตั้งแต่เริ่มต้น" ใน ชื่อผู้จัดการ (ผู้จัดการชื่อ).
   

เรามาเรียกฟังก์ชันของเราว่า CLEAN และเราต้องการให้มีสองอาร์กิวเมนต์ – ข้อความที่จะล้างและรายการของอักขระที่แยกออกเป็นสตริงข้อความ:

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

=LAMBDA(t;d;IF(d="”;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))

ในที่นี้ตัวแปร t คือข้อความต้นฉบับที่จะล้าง และ d คือรายการอักขระที่จะลบ

ทุกอย่างทำงานดังนี้:

1 ซ้ำ

ส่วนย่อย SUBSTITUTE(t;LEFT(d);””) ตามที่คุณอาจเดาได้ แทนที่อักขระตัวแรกจากอักขระด้านซ้ายจากชุด d ที่จะลบในข้อความต้นฉบับ t ด้วยสตริงข้อความว่าง เช่น ลบ “ เอ”. จากผลลัพธ์ระดับกลาง เราได้รับ:

Vsh zkz n 125 รูเบิล

2 ซ้ำ

จากนั้นฟังก์ชันจะเรียกตัวเองและเป็นอินพุต (อาร์กิวเมนต์แรก) ที่ได้รับสิ่งที่เหลืออยู่หลังจากทำความสะอาดในขั้นตอนก่อนหน้า และอาร์กิวเมนต์ที่สองคือสตริงของอักขระที่แยกออกซึ่งไม่ได้เริ่มต้นจากอักขระตัวแรก แต่มาจากอักขระตัวที่สอง เช่น "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA ” โดยไม่ต้องขึ้นต้น “A” – ทำได้โดยฟังก์ชัน MID ก่อนหน้านี้ ฟังก์ชันใช้อักขระตัวแรกจากด้านซ้ายของอักขระที่เหลือ (B) และแทนที่ในข้อความที่กำหนด (Zkz n 125 rubles) ด้วยสตริงว่าง - เราได้รับเป็นผลลัพธ์ระดับกลาง:

125 ร.

3 ซ้ำ

ฟังก์ชันเรียกตัวเองอีกครั้ง โดยรับเป็นอาร์กิวเมนต์แรกส่วนที่เหลือของข้อความที่จะล้างในการวนซ้ำครั้งก่อน (Bsh zkz n 125 ru.) และในฐานะอาร์กิวเมนต์ที่สอง ชุดของอักขระที่แยกออกจะถูกตัดทอนด้วยอักขระอีกหนึ่งตัวเพื่อ ทางซ้ายคือ “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.” โดยไม่มีอักษรตัวแรกของ “B” จากนั้นนำอักขระตัวแรกจากด้านซ้าย (B) ออกจากชุดนี้อีกครั้งและลบออกจากข้อความ - เราได้รับ:

sh zkz n 125 รู

และอื่นๆ ฉันหวังว่าคุณคงเข้าใจ ในการทำซ้ำแต่ละครั้ง รายการอักขระที่จะลบจะถูกตัดออกทางด้านซ้าย และเราจะค้นหาและแทนที่อักขระตัวถัดไปจากชุดด้วยช่องว่าง

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

การวนซ้ำของเซลล์

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

เหล่านั้น. ในงานของเรา รายการเปลี่ยน จะมีสามอาร์กิวเมนต์:

1. เซลล์ที่มีข้อความที่จะประมวลผล (ที่อยู่ต้นทาง)
2. เซลล์แรกของคอลัมน์ที่มีค่าเพื่อค้นหาจากการค้นหา
3. เซลล์แรกของคอลัมน์ที่มีค่าแทนที่จากการค้นหา

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

Shift down ที่การวนซ้ำแต่ละครั้งถูกใช้งานโดยฟังก์ชัน excel มาตรฐาน การกำจัด (ออฟเซ็ต)ซึ่งในกรณีนี้มีสามอาร์กิวเมนต์ – ช่วงเดิม, การเลื่อนแถว (1) และการเปลี่ยนคอลัมน์ (0)

ทันทีที่เราไปถึงจุดสิ้นสุดของไดเร็กทอรี (n = “”) เราต้องยุติการเรียกซ้ำ - เราหยุดเรียกตัวเองและแสดงสิ่งที่สะสมหลังจากการแทนที่ทั้งหมดในตัวแปรข้อความต้นทาง t

นั่นคือทั้งหมดที่ ไม่มีแมโครที่ยุ่งยากหรือคิวรี Power Query – งานทั้งหมดได้รับการแก้ไขโดยฟังก์ชันเดียว

• วิธีใช้ฟังก์ชันอาร์เรย์ไดนามิกใหม่ของ Excel: FILTER, SORT, UNIC
• การแทนที่และล้างข้อความด้วยฟังก์ชัน SUBSTITUTE
  
• การสร้างมาโครและฟังก์ชันที่ผู้ใช้กำหนดเอง (UDF) ใน VBA