פונקציית למבדה באקסל: מדריך מלא ודוגמאות מעשיות

אינפורמטק דיגיטל » Recursos » פונקציית למבדה באקסל: מדריך מלא ודוגמאות מעשיות

פונקציית Lambda באקסל חוללה מהפכה באופן שבו אנו עובדים עם נוסחאות בגיליונות אלקטרוניים של מיקרוסופט. היא מאפשרת לך ליצור פונקציות מותאמות אישית משלך באמצעות שפת הנוסחאות של אקסל בלבד, מבלי לגעת בשורה אחת של VBA או תכנות מסורתי . זה כאילו יש לך את היכולת להוסיף פונקציות מקוריות חדשות, מעוצבות בהתאמה אישית, לתוכנית.

יתר על כן, מספר פונקציות נלוות צצו סביב LAMBDA, כגון BYROW, BYCOL, MAP, SCAN, REDUCE ו-MAKEARRAY , שנועדו לעבוד עם טווחים ומטריצות בצורה גמישה וחזקה הרבה יותר. פונקציות אלו מתנהגות, באופן כללי, כמו לולאות קטנות שחוזרות על הנתונים ומחילות טרנספורמציה המוגדרת על ידי LAMBDA, ופותחות מגוון עצום של אפשרויות לניתוח מתקדם ישירות בגיליון האלקטרוני.

מהי פונקציית LAMBDA באקסל ולמה היא משמשת?

פונקציית Lambda היא כלי המאפשר לך להגדיר פונקציות מותאמות אישית באמצעות נוסחאות Excel בלבד. במקום לתכנת ב-VBA או להסתמך על פקודות מאקרו, תוכל לרכז כל חישוב מורכב בפונקציה אחת לשימוש חוזר, עם פרמטרים משלה ותוצאה סופית ברורה ונקייה.

בפועל, LAMBDA הופכת כל נוסחה לפונקציה שניתן לעשות בה שימוש חוזר כמה פעמים שתרצו. היתרון העיקרי שלה הוא שהיא משתלבת בצורה חלקה עם שאר מנוע החישוב של אקסל וניתן לשלב אותה עם פונקציות סטנדרטיות, הפניות, שמות מוגדרים ומערכים דינמיים.

התחביר הבסיסי כאשר משתמשים בו ישירות בתוך תא הוא:

=LAMBDA(פרמטר1; פרמטר2; …; פרמטרN; חישוב)(ערך1; ערך2; …; ערךN)

במבנה זה, parameter1, parameter2, ..., parameterN הם השמות שאתה נותן למשתנים בתוך הפונקציה, בעוד ש- calculation הוא הנוסחה המשתמשת בפרמטרים אלה כדי לייצר תוצאה. לבסוף, בתוך קבוצת הסוגריים השנייה, אתה מעביר את הערכים בפועל שפרמטרים אלה יקבלו כאשר הפונקציה מופעלת.

אם תשתמשו במנהל השמות של אקסל כדי ליצור פונקציית LAMBDA קבועה, התחביר ישתנה מעט, מכיוון שתגדירו את הפונקציה שם, אך עדיין לא תקראו לה. במקרה כזה, הפורמט יהיה:

=LAMBDA(var1; var2; …; varN; חישוב)

מאוחר יותר תקרא לפונקציה הזו באמצעות השם שנתת לה במנהל השמות, פשוט תקליד את השם והארגומנטים בדיוק כפי שהיית עושה עם SUM, AVERAGE או כל פונקציה סטנדרטית אחרת.

שיטות עבודה מומלצות בעת יצירה ובדיקה של פונקציות למבדה

כשמתחילים לעבוד עם פונקציות למבדה, חשוב לעקוב אחר מספר הנחיות כדי להבטיח שהן מתנהגות כמצופה ולא מבזבזים זמן באיתור שגיאות מורכבות. אחת הדרכים המעשיות ביותר להתחיל היא ליצור ולבדוק את פונקציית למבדה ישירות בתא.

ההליך הרגיל הוא לכתוב תחילה את הנוסחה המלאה עם ההגדרה של LAMBDA והקריאה באותו ביטוי, כך שתוכלו לראות מיד אם התוצאה היא כמצופה. בדרך זו, תוכלו לזהות שגיאות תחביר או לוגיקה לפני שמירתה כפונקציה בעלת שם.

לדוגמה, מבנה בדיקה טיפוסי מאוד יהיה:

חישוב)(test_values) =LAMBDA();

כדי לבדוק משהו פשוט מאוד כמו חיבור 1 למספר, אפשר להשתמש ב:

=LAMBDA(מספר; מספר + 1)(1)

במקרה זה, הפונקציה תחזיר את הערך 2. זוהי דוגמה פשוטה מאוד, אך היא משמשת להמחשת המכניקה: ראשית מגדירים את הפרמטרים ואת החישוב, ולאחר מכן קוראים לפונקציה הזו תוך העברת הארגומנט המתאים.

המלצה מרכזית למניעת שגיאת #CALC! היא לוודא שפונקציית LAMBDA תמיד מחזירה תוצאה . ניתן להשיג זאת על ידי הכללה ברורה של ביטוי בסוף שמייצר ערך יחיד או מערך, בהתאם לצרכים שלכם. אם אתם רואים את שגיאת #CALC! במהלך הבדיקה, בדקו שהנוסחה אכן מייצרת משהו ש-Excel יכול להציג כתוצאה.

לאחר שבדקתם את פונקציית LAMBDA בתא וראתם שהיא פועלת כהלכה, זה זמן טוב להעביר את הלוגיקה הזו למנהל השמות ולהפוך אותה לפונקציה מותאמת אישית הניתנת לשימוש חוזר על פני כל הגיליון או חוברת העבודה.

הקשר של LAMBDA עם פונקציות המטריצה ​​החדשות

סביב LAMBDA, צצה סדרה של פונקציות מתקדמות, כגון BYROW, BYCOL, MAP, SCAN, REDUCE ו-MAKEARRAY (האחרונה מתורגמת בגרסאות מסוימות כ-ARCHIVOMAKEARRAY), אשר מסתמכות על LAMBDA כדי להחיל טרנספורמציות על טווחים ומטריצות שלמות.

הרעיון הכללי הוא שפונקציות אלו עוברות על טווחי נתונים (לפי שורות, עמודות או אלמנט אחר אלמנט) ועבור כל אלמנט או קבוצת אלמנטים, מבצעות פונקציית למבדה שאתה מגדיר. במילים אחרות, הן פועלות כמו לולאות, אך משולבות בשפת הנוסחאות של אקסל.

זה מאפשר לך לבצע פעולות שבעבר דרשו עמודות עזר, טבלאות ביניים או אפילו פקודות מאקרו, ישירות באמצעות נוסחת מערך אחת שמרחיבה ומחזירה תוצאות עבור כל הטווח בבת אחת.

בין הפונקציות הקשורות ל-LAMBDA, בולטות פונקציות כמו REDUCE, MAP, SCAN, BYCOL, BYROW ו-MAKEARRAY , שלכל אחת מהן מטרה ספציפית: מעבר בין שורות, יישום טרנספורמציות לפי עמודות, צבירת תוצאות, יצירת מטריצות מחושבות מאפס וכו'. משותף לכולן הוא שהן משתמשות ב-LAMBDA כ"מנוע" פנימי, שאליו הן מעבירות ערכים ומצברים תוך כדי תנועה במטריצה.

פונקציית BYROW: מבצעת איטרציה בין שורות והחזרת תוצאות שורה אחר שורה

הפונקציה BYROW משמשת להחלת פונקציית LAMBDA על כל שורה בטווח והחזרת מערך עם ערך אחד עבור כל שורה שעובדה. זוהי דרך יעילה מאוד לחשב סכומי ביניים או סטטיסטיקות שורה אחר שורה מבלי להעתיק נוסחאות אנכית.

התחביר הכללי שלו הוא:

=BYROW(מטריקס; LAMBDA(שורה; ביטוי))

הארגומנט הראשון הוא המערך או הטווח שברצונך לעבור דרכם (לדוגמה, B2:D7), והשני הוא פונקציית LAMBDA שמקבלת כל שורה בטווח זה כפרמטר, אחת אחת. פונקציית LAMBDA מחזירה את הערך שברצונך לשייך לשורה זו (אולי סכום, ממוצע, בדיקה לוגית וכו').

דמיינו שיש לכם טבלת נתונים בטווח B2:D7 ואתם רוצים לקבל סכום ביניים עבור כל שורה. תוכלו לכתוב משהו כזה בתא E2:

=BYROW(B2:D7; LAMBDA(שורה; SUM(שורה)))

התוצאה תהיה וקטור פלט עם ערך אחד לכל שורה במטריצה ​​B2:D7, כאשר כל ערך מייצג את סכום האיברים באותה שורה. בדרך זו, אינך צריך לכתוב SUM שורה אחר שורה: BYROW עושה זאת עבורך ופורש את התוצאה למטה.

פונקציית BYCOL: החלת LAMBDA לפי עמודות

בדומה מאוד ל-BYROW, הפונקציה BYCOL נועדה לעבור על מערך לפי עמודות במקום שורות. היא מחילה פונקציית LAMBDA על כל עמודה בטווח ומחזירה מערך של תוצאות שבו כל אלמנט מתאים לעמודה.

התחביר הטיפוסי שלו הוא:

=BYCOL(מערך; LAMBDA(עמודה; ביטוי))

במקרה זה, הפרמטר שמקבל LAMBDA הוא כל העמודה של המטריצה ​​המעובדת בכל שלב. בדומה ל-BYROW, הפונקציה מחזירה וקטור, אך פונקציה זו נועדה לעבוד עם סכומים או אינדיקטורים לפי עמודה.

בהמשך לדוגמה הקודמת, אם ברצונך לחשב את הממוצע של כל עמודה בטווח B2:D7 , תוכל להציב נוסחה כזו בתא B8:

=BYCOL(B2:D7; LAMBDA(עמודה; ממוצע(עמודה)))

התוצאה תהיה מטריצה ​​עם אותו מספר עמודות כמו B2:D7, כאשר כל מיקום מכיל את הממוצע של אותה עמודה . בדרך זו תקבלו את כל הממוצעים בבת אחת מבלי שתצטרכו לגרור ולשחרר נוסחאות או לדאוג להפניות יחסיות.

פונקציית MAKEARRAY (MAKEARRAYFILE): יצירת מערכים מחושבים

הפונקציה MAKEARRAY (לפעמים מוצגת כ-ARCHIVOMAKEARRAY) מאפשרת לך ליצור מערך חדש לחלוטין על ידי ציון מספר השורות והעמודות, וחישוב כל אלמנט באמצעות פונקציית LAMBDA. היא אינה מתחילה מטווח קיים, אלא בונה את המערך מאפס.

התחביר הכללי שלו הוא:

=MAKEARRAY(שורות; עמודות; LAMBDA(שורה; עמודה; ביטוי))

הארגומנט `rows` מציין כמה שורות יהיו במטריצת הפלט, `columns` מגדיר את מספר העמודות, ופונקציית LAMBDA מקבלת את אינדקסי השורות והעמודות המחושבים בכל איטרציה כפרמטרים. בעזרת מידע זה, ניתן לבנות כמעט כל תבנית מספרית או טקסטואלית.

דוגמה ממחישה מאוד היא יצירת מטריצה ​​שבה כל אלמנט מציין את מיקומו. בכל תא, ניתן לכתוב משהו כזה:

=MAKERRAYFILE(3; 2; LAMBDA(שורה; עמודה; -(שורה ועמודה)))

התוצאה תהיה מטריצה ​​בת 3 שורות על 2 עמודות , שבה כל ערך מייצג שילוב של שורה ועמודה (לדוגמה, 11, 12, 21, 22, 31, 32), לאחר טרנספורמציה בהתאם לחישוב שתזין (במקרה זה, הסימן השלילי שמוחל על שרשור השורות והעמודות).

שימוש מעניין נוסף ב-MAKEARRAY הוא המרת וקטור למערך תוך שליטה בכמות האלמנטים שאתה לוקח. נניח שאתה רוצה ליצור מערך עם 6 הערכים הראשונים של טווח אנכי. ראשית תוכל ליצור מערך של מיקומים באמצעות MAKEARRAYFILE, לקבל את k ערכי המיקום הקטנים ביותר, ולבסוף להשתמש ב-INDEX כדי לאחזר את האלמנטים בפועל מהטווח המקורי.

דוגמה לנוסחה, המשלבת מספר פונקציות, יכולה להיות בעלת מבנה כזה:

=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(שורה; עמודה; -(שורה ועמודה))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))

כאן, LET משמש להגדרת שמות ביניים (arrPos, arrPosF), מערך של מיקומים נבנה באמצעות ARCHIVOMAKERRAY (3×2), 6 המיקומים הקטנים ביותר נבחרים באמצעות SMALLEST ו-SEQUENCE, ולבסוף INDEX משמש להחזרת הערכים המתאימים מהטווח G8:G13. זוהי דוגמה חזקה לאופן שבו ניתן לשלב פונקציות LAMBDA ומערך דינמי כדי לבצע טרנספורמציות מורכבות ללא פקודות מאקרו.

פונקציית MAP: טרנספורמציה אלמנט-אחר-אלמנט

פונקציית MAP משמשת לאיטרציה של מערך אחד או יותר בו זמנית והחזרת מערך חדש שבו כל אלמנט פלט מחושב על ידי החלת פונקציית LAMBDA על אלמנט/אלמנטי הקלט המתאימים. היא שקולה לפונקציית "map" קלאסית בתכנות פונקציונלי.

התחביר הבסיסי הוא:

=MAP(מטריצה1; LAMBDA_או_מטריצות_יותר)

בצורתו הפשוטה ביותר, היא לוקחת מערך יחיד ופונקציית LAMBDA שמקבלת כל ערך מאותו מערך. פונקציית LAMBDA זו משנה את הערך ומחזירה את הגרסה החדשה שתהווה חלק ממערך הפלט, תוך שמירה על אותם ממדים כמו המערך המקורי.

לדוגמה, אם ברצונך לעבור דרך טווח אנכי A21:A26 ולהשאיר את המספר המקורי אם הוא זוגי או מקף אם הוא אי-זוגי, תוכל להשתמש במשהו כמו:

=MAP($A$21:$A$26; LAMBDA(פרמטר1; IF(ES.PAR(פרמטר1); פרמטר1; «-«)))

במקרה זה, MAP מנתח כל אלמנט ב-A21:A26. LAMBDA בודק עם IS.EVEN אם המספר זוגי. אם הוא זוגי, הוא מחזיר את המספר עצמו; אחרת, הוא מחזיר מקף. התוצאה היא מערך באותו גודל כמו הטווח המקורי, אך כאשר הטרנספורמציה מוחלת על כל אלמנט.

גישה זו שימושית מאוד כאשר ברצונך להחיל לוגיקה מותנית, המרת טקסט, נרמול ערכים או כל פעולה פשוטה אחרת, תוך הימנעות מעמודות עזר ונוסחאות חוזרות.

פונקציית SCAN: תוצאות מצטברות ותוצאות ביניים

הפונקציה SCAN משמשת לבדיקת מערך על ידי החלת פונקציית LAMBDA על כל ערך ויצירת מערך פלט המציג את כל הערכים הביניים של תהליך הצבירה. היא דומה מאוד ל-REDUCE, אך במקום להחזיר רק את התוצאה הסופית, היא שומרת על כל שלב.

התחביר הכללי שלו הוא:

=SCAN(; מערך; LAMBDA(מצבר; ערך))

הארגומנט הראשון, שהוא אופציונלי, הוא הערך ההתחלתי של הפונקציה accumulator (לדוגמה, 0 אם אתם מחברים). הארגומנט השני הוא המערך או הטווח שברצונכם לעבור דרכם. לבסוף, פונקציית LAMBDA מקבלת שני פרמטרים: הפונקציה accumulator (התוצאה החלקית עד לנקודה זו) והערך הנוכחי של המערך שאתם מעבדים.

בכל שלב, SCAN מעריך את ערך ה-LAMBDA, מעדכן את הפונקציה המצטברת ומייצר אלמנט חדש במטריצת הפלט עם הערך המתקבל. בדרך זו, מתקבל רצף של ערכים מצטברים או טרנספורמציות פרוגרסיביות.

דוגמה אופיינית היא חישוב סכום מצטבר (סכום עובר) על פני קבוצת ערכים, ומשם גם קבלת התדירות המצטברת היחסית. דמיינו שיש לכם נתונים ב-A31:A36 ואתם רוצים את הסכום המצטבר המוחלט:

=SCAN(0; A31:A36; LAMBDA(accum; param1; accum + 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, אך מחזירה רק את הערך האחרון ב-accumulator.

התחביר שלה הוא:

=REDUCE(; array; LAMBDA(accumulator; value))

בדיוק כמו ב-SCAN, ה- initial_value קובע את נקודת ההתחלה של ה-accumulator, המטריצה ​​היא הטווח שיש לעבד, ו-LAMBDA מכיל כפרמטרים את ה-accumulator הנוכחי ואת הערך הנקרא באותו רגע.

שימוש אופייני מאוד הוא חישוב סכום מצטבר או פעולה מצטברת שבה מתעניינים רק בתוצאה האחרונה . לדוגמה, כדי לסכם את A1:A6 באמצעות REDUCE ניתן לכתוב:

=REDUCE(0; A1:A6; LAMBDA(accum; param1; accum + param1))‎

כאן, REDUCE מבצע איטרציות דרך A1:A6 ובכל שלב מעדכן את הערך המצטבר על ידי הוספת הערך של התא הנוכחי (param1). בסוף, הוא מחזיר ערך יחיד: הסכום הכולל. מבחינה רעיונית זה דומה לשימוש ב-SUM, אבל עם REDUCE ניתן להגדיר כל לוגיקת צבירה מורכבת יותר, לא רק סכומים פשוטים.

כוחו של REDUCE טמון ביכולתו לעבוד עם התוצאה הקודמת בכל שלב ולהמשיך ליישם פעולות עד להשלמת התהליך. זה מאפשר לך ליישם חישובים מותאמים אישית מתוחכמים שטופלו באופן מסורתי באמצעות לולאות במאקרו.

יצירת פונקציות מותאמות אישית עם LAMBDA ומנהל השמות

אחת התכונות החזקות ביותר של LAMBDA היא היכולת שלה להמיר כל נוסחה לפונקציית משתמש באמצעות מנהל השמות של Excel. זה מאפשר לפונקציה שלך לקבל שם משלה ולהיות בשימוש כמו כל פונקציה מקורית אחרת בתוכנית.

תהליך העבודה האופייני הוא זה: ראשית, בדקו את פונקציית LAMBDA בתא , כולל גם את ההגדרה וגם את הקריאה באמצעות ארגומנטים לדוגמה. לאחר שתוודאו שהיא פועלת כהלכה ומחזירה את התוצאה הצפויה, העתיקו את החלק המתאים להגדרת LAMBDA (ללא הקריאה הסופית) והדביקו אותו במנהל השמות.

במנהל השמות, עליך ליצור שם חדש (לדוגמה, MyVATFunction, MyDiscount, MyWeightedAverage וכו') ובשדה "Refers to" עליך להזין:

=LAMBDA(var1; var2; …; varN; חישוב)

מאותו רגע ואילך, בכל תא בחוברת העבודה שלך תוכל לקרוא לפונקציה על ידי כתיבת שמה כאילו הייתה פונקציה מובנית , תוך העברת ערכי הפרמטרים באותו סדר בו הגדרת אותם.

יש לכך שני יתרונות ברורים: ראשית, זה הופך את הגיליונות האלקטרוניים שלכם לקריאים יותר (במקום לראות נוסחאות ארוכות, אתם רואים פונקציה עם שם תיאורי); שנית, זה מרכז את הלוגיקה במקום אחד. אם תרצו לשנות את החישוב בהמשך, פשוט שנו את ההגדרה במנהל השמות, וכל הנוסחאות המשתמשות בה יעודכנו אוטומטית.

היבטים מעשיים ושיקולים נוספים

כדי למנף באמת את LAMBDA ואת הפונקציות הנלוות אליו, כדאי להבין כמה היבטים מעשיים של התנהגותן ודרישותיהן . ראשית, פונקציות אלו הן חלק מתכונות Excel מודרניות, לכן אתם זקוקים לגרסה שכבר כוללת מערכים דינמיים ואת הפונקציות החדשות יותר, LAMBDA, BYROW, BYCOL ופונקציות אחרות. אלו זמינות בדרך כלל במהדורות האחרונות של Microsoft 365.

נושא רלוונטי נוסף הוא ביצועים : למרות שפונקציות LAMBDA ו-traversal הן עוצמתיות מאוד, אם מיישמים אותן על טווחים עצומים עם לוגיקה מורכבת מאוד, ייתכן שייקח זמן רב יותר לחישוב מחדש של הספר. מומלץ לעצב פונקציות LAMBDA תוך מחשבה על יעילות, להימנע מחישובים מיותרים ולמנף מבנים כמו LET כדי להגדיר ערכי ביניים לשימוש חוזר.

כמו כן, חשוב לשמור על מוסכמה עקבית למתן שמות לפרמטרים ולפונקציות . שימוש בשמות תיאוריים עוזר לך להבין את ההיגיון כשאתה מבקר שוב בקובץ חודשים לאחר מכן או כאשר מישהו אחר צריך לעבוד עם חוברות העבודה שלך. פרמטר בשם amount, rate, dataRow או valuesCol ברור הרבה יותר מאשר פשוט x או yoa.

בנוגע לשגיאת #CALC!, היא מופיעה בדרך כלל כאשר Excel אינו מסוגל לחשב את ביטוי המערך או כאשר הפונקציה LAMBDA אינה מחזירה תוצאה חוקית. יש לוודא תמיד שלנוסחה שלך יש פלט מוגדר היטב, ואם אתה עובד עם פונקציות מערך, שהמידות עקביות (לדוגמה, שאתה לא משלב טווחי גודל לא תואמים ללא טרנספורמציה נכונה).

לבסוף, בעוד ש-LAMBDA מבטל את הצורך ב-VBA במקרים רבים, הוא אינו מחליף אותו לחלוטין. ישנם מצבים בהם אוטומציה של מאקרו נותרה האפשרות הטובה ביותר, אך עבור מספר רב של חישובים מותאמים אישית וטרנספורמציות נתונים, LAMBDA והפונקציות הנלוות אליו מאפשרות לך לשמור את כל העבודה שלך בסביבת הנוסחאות הקונבנציונלית של Excel.

הודות לאפשרויות אלו, לאלו שעובדים מדי יום עם גיליונות אלקטרוניים יש כעת כלים גמישים הרבה יותר לעצב חישובים משלהם, לסכם מידע לפי שורות או עמודות, לחצות מטריצות באופן מלא או חלקי, ליצור סכומים מפורטים וליצור מטריצות חדשות המחושבות תוך כדי תנועה, והכל מבלי לעזוב את שפת הנוסחאות שהם כבר מכירים.