ฟังก์ชันแลมบ์ดาใน Excel: คู่มือฉบับสมบูรณ์และตัวอย่างการใช้งานจริง

อินฟอร์เมเทค ดิจิตอล » Recursos » ฟังก์ชันแลมบ์ดาใน Excel: คู่มือฉบับสมบูรณ์และตัวอย่างการใช้งานจริง

ฟังก์ชัน Lambda ใน Excelได้ปฏิวัติวิธีการทำงานกับสูตรในสเปรดชีตของ Microsoft มันช่วยให้คุณสร้างฟังก์ชันที่กำหนดเองได้โดยใช้เพียงภาษาของสูตรใน Excel โดยไม่ต้องเขียนโค้ด VBA หรือการเขียนโปรแกรมแบบดั้งเดิม แม้แต่บรรทัดเดียว ราวกับว่าคุณสามารถเพิ่มฟังก์ชันพื้นฐานที่ออกแบบเองได้ลงในโปรแกรม

นอกจากนี้ ยังมีฟังก์ชันที่เกี่ยวข้องอีกมากมายที่เกิดขึ้นรอบๆ LAMBDA เช่นBYROW, BYCOL, MAP, SCAN, REDUCE และ MAKEARRAYซึ่งออกแบบมาเพื่อทำงานกับช่วงข้อมูลและเมทริกซ์ในรูปแบบที่ยืดหยุ่นและทรงพลังยิ่งขึ้น ฟังก์ชันเหล่านี้โดยทั่วไปแล้วจะทำงานคล้ายกับลูปขนาดเล็กที่วนซ้ำผ่านข้อมูลและใช้การแปลงที่กำหนดโดย LAMBDA ซึ่งเปิดโอกาสมากมายสำหรับการวิเคราะห์ขั้นสูงโดยตรงบนสเปรดชีต

ฟังก์ชัน LAMBDA ใน Excel คืออะไร และใช้ทำอะไร?

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

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

รูปแบบไวยากรณ์พื้นฐานเมื่อใช้โดยตรงในเซลล์มีดังนี้:

=LAMBDA(parameter1; parameter2; …; parameterN; calculation)(value1; value2; …; valueN)

ในโครงสร้างนี้parameter1, parameter2, …, parameterNคือชื่อที่คุณตั้งให้กับตัวแปรภายในฟังก์ชัน ในขณะที่calculationคือสูตรที่ใช้พารามิเตอร์เหล่านั้นเพื่อสร้างผลลัพธ์ สุดท้าย ภายในวงเล็บชุดที่สอง คุณจะใส่ค่าจริงที่พารามิเตอร์เหล่านั้นจะมีเมื่อฟังก์ชันถูกเรียกใช้งาน

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

=แลมบ์ดา(var1; var2; …; varN; การคำนวณ)

ต่อมา คุณสามารถเรียกใช้ฟังก์ชันนั้นโดยใช้ชื่อที่คุณตั้งไว้ในตัวจัดการชื่อ โดยพิมพ์ชื่อและอาร์กิวเมนต์เช่นเดียวกับที่คุณทำกับฟังก์ชัน SUM, AVERAGE หรือฟังก์ชันมาตรฐานอื่นๆ

แนวทางปฏิบัติที่ดีที่สุดในการสร้างและทดสอบฟังก์ชัน Lambda

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

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

ตัวอย่างเช่น โครงสร้างการทดสอบทั่วไปอย่างหนึ่งจะเป็นดังนี้:

=LAMBDA(); calculation)(test_values)

ในการตรวจสอบสิ่งง่ายๆ เช่น การบวก 1 เข้ากับตัวเลข คุณสามารถใช้:

=LAMBDA(number; number + 1)(1)

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

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

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

ความสัมพันธ์ของ LAMBDA กับฟังก์ชันเมทริกซ์ใหม่

ฟังก์ชันขั้นสูงหลายอย่างได้เกิดขึ้นรอบๆ LAMBDA เช่นBYROW, BYCOL, MAP, SCAN, REDUCE และ MAKEARRAY (ซึ่งในบางเวอร์ชันแปลเป็น ARCHIVOMAKEARRAY) โดยฟังก์ชันเหล่านี้อาศัย LAMBDA ในการประยุกต์ใช้การแปลงกับช่วงและเมทริกซ์ทั้งหมด

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

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

ในบรรดาฟังก์ชันที่เกี่ยวข้องกับ LAMBDA นั้น ฟังก์ชัน ที่โดดเด่น ได้แก่ REDUCE, MAP, SCAN, BYCOL, BYROW และ MAKEARRAYซึ่งแต่ละฟังก์ชันมีวัตถุประสงค์เฉพาะ เช่น การวนลูปผ่านแถว การแปลงข้อมูลตามคอลัมน์ การสะสมผลลัพธ์ การสร้างเมทริกซ์ที่คำนวณขึ้นใหม่ เป็นต้น สิ่งที่ฟังก์ชันเหล่านี้มีเหมือนกันคือ พวกมันใช้ LAMBDA เป็น "กลไก" ภายใน โดยจะส่งค่าและตัวสะสมเข้าไปในขณะที่เคลื่อนที่ผ่านเมทริกซ์

ฟังก์ชัน BYROW: วนลูปผ่านแถวและส่งคืนผลลัพธ์ทีละแถว

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

รูปแบบไวยากรณ์โดยทั่วไปคือ:

=BYROW(matrix; LAMBDA(row; expression))

อาร์กิวเมนต์แรกคืออาร์เรย์หรือช่วงที่คุณต้องการวนซ้ำ (ตัวอย่างเช่น B2:D7) และอาร์กิวเมนต์ที่สองคือฟังก์ชัน LAMBDA ที่รับแต่ละแถวในช่วงนั้นเป็นพารามิเตอร์ทีละแถว ฟังก์ชัน LAMBDA จะส่งคืนค่าที่คุณต้องการเชื่อมโยงกับแถวนั้น (อาจเป็นผลรวม ค่าเฉลี่ย การตรวจสอบเชิงตรรกะ ฯลฯ)

ลองนึกภาพว่าคุณมีตารางข้อมูลในช่วงเซลล์B2:D7และคุณต้องการหาผลรวมย่อยสำหรับแต่ละแถว คุณสามารถเขียนโค้ดประมาณนี้ในเซลล์ E2 ได้:

=BYROW(B2:D7; LAMBDA(แถว; ผลรวม(แถว)))

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

ฟังก์ชัน BYCOL: ใช้ฟังก์ชัน LAMBDA กับแต่ละคอลัมน์

ฟังก์ชันBYCOL คล้ายกับ BYROW มาก แต่ได้รับการออกแบบมาเพื่อวนซ้ำผ่านอาร์เรย์โดยใช้คอลัมน์แทนแถว โดยจะใช้ฟังก์ชัน LAMBDA กับแต่ละคอลัมน์ในช่วงที่กำหนด และส่งคืนอาร์เรย์ของผลลัพธ์ โดยแต่ละองค์ประกอบจะสอดคล้องกับคอลัมน์นั้นๆ

รูปแบบไวยากรณ์ทั่วไปคือ:

=BYCOL(อาร์เรย์; LAMBDA(คอลัมน์; นิพจน์))

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

จากตัวอย่างก่อนหน้านี้ หากคุณต้องการคำนวณค่าเฉลี่ยของแต่ละคอลัมน์ในช่วงB2:D7คุณสามารถใส่สูตรแบบนี้ลงในเซลล์ B8 ได้:

=BYCOL(B2:D7; LAMBDA(คอลัมน์; AVERAGE(คอลัมน์)))

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

ฟังก์ชัน MAKEARRAY (MAKEARRAYFILE): สร้างอาร์เรย์คำนวณ

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

รูปแบบไวยากรณ์โดยทั่วไปคือ:

=MAKEARRAY(rows; columns; LAMBDA(row; column; expression))

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

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

=MAKEARRAYFILE(3; 2; LAMBDA(row; col; -(row & col)))

ผลลัพธ์ที่ได้จะเป็น เมทริกซ์ ขนาด 3 แถว 2 คอลัมน์โดยแต่ละค่าจะแสดงถึงการรวมกันของแถวและคอลัมน์ (ตัวอย่างเช่น 11, 12, 21, 22, 31, 32) ซึ่งจะถูกแปลงตามการคำนวณที่คุณป้อน (ในกรณีนี้คือการใช้เครื่องหมายลบกับผลรวมของแถวและคอลัมน์)

อีกหนึ่งการใช้งานที่น่าสนใจของ MAKEARRAY คือการแปลงเวกเตอร์เป็นอาร์เรย์โดยควบคุมจำนวนองค์ประกอบที่จะดึงออกมา สมมติว่าคุณต้องการสร้างอาร์เรย์ที่มีค่า 6 ค่าแรกของช่วงแนวตั้ง คุณสามารถสร้างอาร์เรย์ของตำแหน่งด้วย MAKEARRAYFILE ก่อน จากนั้นเลือกค่าตำแหน่งที่เล็กที่สุด k ค่า และสุดท้ายใช้ INDEX เพื่อดึงองค์ประกอบจริงจากช่วงเดิม

ตัวอย่างของสูตรที่รวมฟังก์ชันหลายฟังก์ชันเข้าด้วยกัน อาจมีโครงสร้างดังนี้:

=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(row; col; -(row & col))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))

ในที่นี้ LET ถูกใช้เพื่อกำหนดชื่อตัวกลาง (arrPos, arrPosF) อาร์เรย์ของตำแหน่งถูกสร้างขึ้นด้วย ARCHIVOMAKEARRAY (3×2) ตำแหน่งที่เล็กที่สุด 6 ตำแหน่งจะถูกเลือกด้วย SMALLEST และ SEQUENCE และสุดท้าย INDEX ถูกใช้เพื่อส่งคืนค่าที่สอดคล้องกันจากช่วง G8:G13 นี่เป็นตัวอย่างที่มีประสิทธิภาพของการรวมฟังก์ชัน LAMBDA และอาร์เรย์แบบไดนามิกเพื่อทำการแปลงที่ซับซ้อนโดยไม่ต้องใช้มาโคร

ฟังก์ชัน MAP: การแปลงทีละองค์ประกอบ

ฟังก์ชันMAPใช้สำหรับวนซ้ำผ่านอาร์เรย์หนึ่งหรือหลายอาร์เรย์พร้อมกัน และส่งคืนอาร์เรย์ใหม่ โดยที่แต่ละองค์ประกอบเอาต์พุตคำนวณโดยการใช้ฟังก์ชัน LAMBDA กับองค์ประกอบอินพุตที่สอดคล้องกัน ฟังก์ชันนี้เทียบเท่ากับฟังก์ชัน "map" แบบคลาสสิกในการเขียนโปรแกรมเชิงฟังก์ชัน

ไวยากรณ์พื้นฐานคือ:

=MAP(matrix1; LAMBDA_or_more_matrices)

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

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

=MAP($A$21:$A$26; LAMBDA(param1; IF(ES.PAR(param1); param1; «-«)))

ในกรณีนี้ MAP จะวิเคราะห์แต่ละองค์ประกอบของ A21:A26 LAMBDA จะตรวจสอบด้วย IS.EVEN ว่าตัวเลขนั้นเป็นเลขคู่หรือไม่ ถ้าเป็นเลขคู่ก็จะคืนค่าตัวเลขนั้น แต่ถ้าไม่ใช่ก็จะคืนค่าเครื่องหมายขีดกลาง ผลลัพธ์ที่ได้คืออาร์เรย์ที่มีขนาดเท่ากับช่วงเดิม แต่มีการแปลงค่าให้กับแต่ละองค์ประกอบแล้ว

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

ฟังก์ชัน SCAN: ผลลัพธ์สะสมและผลลัพธ์ระหว่างทาง

ฟังก์ชันSCANใช้สำหรับตรวจสอบอาร์เรย์โดยการใช้ฟังก์ชัน LAMBDA กับแต่ละค่า และสร้างอาร์เรย์เอาต์พุตที่แสดงค่าระหว่างขั้นตอนทั้งหมดของการสะสมค่า ฟังก์ชันนี้คล้ายกับ REDUCE มาก แต่แทนที่จะส่งคืนเฉพาะผลลัพธ์สุดท้าย ฟังก์ชัน SCAN จะเก็บรักษาค่าในแต่ละขั้นตอนไว้ด้วย

รูปแบบไวยากรณ์โดยทั่วไปคือ:

=SCAN(; อาร์เรย์; LAMBDA(ตัวสะสม; ค่า))

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

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

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

=SCAN(0; A31:A36; LAMBDA(สะสม; param1; สะสม + param1))

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

จากผลรวมสะสมนั้น เราสามารถคำนวณความถี่ร้อยละสะสมได้ง่ายๆ โดยการหารผลรวมสะสมแต่ละรายการด้วยผลรวมทั้งหมด ตัวอย่างเช่น คุณอาจกำหนดผลรวมโดยใช้ฟังก์ชัน SUM ก่อน แล้วจึงใช้ฟังก์ชัน SCAN อีกครั้ง:

=LET(total; SUM(A31:A36); SCAN(0; A31:A36; LAMBDA(accum; param1; (accum + param1)))/total)

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

ฟังก์ชัน REDUCE: ลดค่าให้เหลือค่าสะสมเพียงค่าเดียว

ฟังก์ชันREDUCEก็วนซ้ำผ่านอาร์เรย์โดยใช้ฟังก์ชัน LAMBDA กับแต่ละองค์ประกอบเช่นกัน แต่ต่างจาก SCAN ตรงที่ในที่นี้คุณสนใจเฉพาะผลลัพธ์สุดท้ายของกระบวนการสะสมเท่านั้น กล่าวคือ มันทำการวนซ้ำแบบเดียวกับ SCAN แต่จะส่งคืนเฉพาะค่าสุดท้ายในตัวสะสมเท่านั้น

ไวยากรณ์ของมันคือ:

=ลด(; อาร์เรย์; แลมบ์ดา(ตัวสะสม; ค่า))

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

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

=ลด(0; A1:A6; LAMBDA(สะสม; param1; สะสม + param1))

ในที่นี้ คำสั่ง REDUCE จะวนซ้ำผ่านเซลล์ A1:A6 และในแต่ละขั้นตอนจะอัปเดตค่าสะสมโดยการเพิ่มค่าของเซลล์ปัจจุบัน (param1) เข้าไป เมื่อสิ้นสุดการทำงาน จะส่งคืนค่าเดียวคือผลรวมทั้งหมด หลักการทำงานคล้ายกับการใช้คำสั่ง SUM แต่ด้วย REDUCE คุณสามารถกำหนดตรรกะการสะสมที่ซับซ้อนกว่าได้ ไม่ใช่แค่การบวกแบบง่ายๆ เท่านั้น

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

การสร้างฟังก์ชันแบบกำหนดเองด้วย LAMBDA และ Name Manager

หนึ่งในคุณสมบัติที่ทรงพลังที่สุดของ LAMBDA คือความสามารถในการแปลงสูตรใดๆ ให้เป็นฟังก์ชันของผู้ใช้โดยใช้ตัวจัดการชื่อของ Excel これにより ฟังก์ชันของคุณจะมีชื่อเฉพาะและสามารถใช้งานได้เหมือนกับฟังก์ชันพื้นฐานอื่นๆ ในโปรแกรม

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

ในตัวจัดการชื่อ คุณสร้างชื่อใหม่ (ตัวอย่างเช่น MyVATFunction, MyDiscount, MyWeightedAverage เป็นต้น) และในช่อง "อ้างอิงถึง" คุณป้อน:

=แลมบ์ดา(var1; var2; …; varN; การคำนวณ)

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

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

แง่มุมเชิงปฏิบัติและข้อควรพิจารณาเพิ่มเติม

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

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

นอกจากนี้ การรักษารูปแบบการตั้งชื่อที่สอดคล้องกันสำหรับพารามิเตอร์และฟังก์ชัน ก็มีความสำคัญอย่างยิ่ง การใช้ชื่อที่สื่อความหมายจะช่วยให้คุณเข้าใจตรรกะได้ง่ายขึ้นเมื่อคุณกลับมาดูไฟล์อีกครั้งในอีกหลายเดือนต่อมา หรือเมื่อคนอื่นจำเป็นต้องใช้งานเวิร์กบุ๊กของคุณ พารามิเตอร์ที่มีชื่อว่า amount, rate, dataRow หรือ valuesCol นั้นชัดเจนกว่าการตั้งชื่อเพียงแค่ x หรือ yoa มาก

เกี่ยวกับ ข้อผิดพลาด #CALC!นั้น โดยปกติจะปรากฏขึ้นเมื่อ Excel ไม่สามารถคำนวณนิพจน์อาร์เรย์ได้ หรือเมื่อฟังก์ชัน LAMBDA ไม่ส่งคืนผลลัพธ์ที่ถูกต้อง ตรวจสอบให้แน่ใจเสมอว่าสูตรของคุณมีผลลัพธ์ที่กำหนดไว้อย่างชัดเจน และหากคุณกำลังใช้งานฟังก์ชันอาร์เรย์ ตรวจสอบให้แน่ใจว่ามิติข้อมูลมีความสอดคล้องกัน (ตัวอย่างเช่น ตรวจสอบให้แน่ใจว่าคุณไม่ได้รวมช่วงขนาดที่ไม่เข้ากันโดยไม่มีการแปลงที่เหมาะสม)

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

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