LAMBDA היא פונקציית העל החדשה של Excel

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

עד עכשיו הדרך היחידה לפתור את הבעיה הייתה מאקרו, כלומר כתיבת פונקציה מוגדרת על ידי המשתמש (UDF = User Defined Function) ב-Visual Basic, שדורשת כישורי תכנות מתאימים ולעיתים לא קלה בכלל. עם זאת, עם העדכונים האחרונים של Office 365, המצב השתנה לטובה - נוספה פונקציית "עטיפה" מיוחדת לאקסל למבדה. בעזרתו, המשימה של יצירת פונקציות משלך נפתרת כעת בקלות ויפה.

בואו נסתכל על עקרון השימוש בו בדוגמה הבאה.

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

שלב 1. כתוב את הנוסחה

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

שלב 2. עטיפה ב-LAMBDA ובדיקה

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

=LAMBDA(משתנה1; משתנה2; ... VariableN ; ביטוי)

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

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

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

שלב 3. צור שם

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

הכל. לאחר לחיצה על OK ניתן להשתמש בפונקציה שנוצרה בכל תא בכל גיליון של חוברת עבודה זו:

השתמש בספרים אחרים

LAMBDA ומערכים דינמיים

פונקציות מותאמות אישית שנוצרו עם פונקציה למבדה לתמוך בהצלחה בעבודה עם מערכים דינמיים חדשים ותפקודיהם (סנן, ייחודי, GRADE) נוסף ל-Microsoft Excel בשנת 2020.

נניח שאנו רוצים ליצור פונקציה חדשה המוגדרת על ידי משתמש שתשווה בין שתי רשימות ותחזיר את ההבדל ביניהן - אותם אלמנטים מהרשימה הראשונה שאינם ברשימה השנייה. עבודת החיים, לא? בעבר, לשם כך השתמשו באחת מהפונקציות א-לה VPR (VLOOKUP), או PivotTables, או שאילתות Power Query. עכשיו אתה יכול לעשות עם נוסחה אחת:

בגרסה האנגלית זה יהיה:

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

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

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

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

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

=LAMBDA(t;d; TRANSPOSE(FILTER.XML(""&תחליף(t;d? "«)&»";"//Y")))

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

ספירה רקורסיבית של תווים

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

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

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

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

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 רובל) במחרוזת ריקה - אנו מקבלים כתוצאת ביניים:

125 רו.

איטרציה 3

הפונקציה קוראת לעצמה שוב, ומקבלת כארגומנט הראשון את מה שנותר מהטקסט שיש לנקות באיטרציה הקודמת (Bsh zkz n 125 ru.), וכארגומנט השני, קבוצת התווים הבלתי נכללים נקטעה על ידי תו אחד נוסף. השמאלי, כלומר "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.," ללא "B" הראשוני. ואז זה שוב לוקח את התו הראשון משמאל (B) מהקבוצה הזו ומסיר אותו מהטקסט - נקבל:

ש זקז נ 125 רו.

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

כאשר כל הדמויות ייגמרו, נצטרך לצאת מהלולאה - התפקיד הזה מבוצע רק על ידי הפונקציה IF (אם), שבו העיצוב שלנו עטוף. אם לא נותרו תווים למחוק (d=””), אז הפונקציה כבר לא אמורה לקרוא לעצמה, אלא פשוט צריכה להחזיר את הטקסט לניקוי (משתנה t) בצורתו הסופית.

איטרציה רקורסיבית של תאים

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

הָהֵן. בתפקוד שלנו רשימת תחליפים יהיו שלושה טיעונים:

1. תא עם טקסט לעיבוד (כתובת מקור)
2. התא הראשון של עמודה עם ערכים לחיפוש מהחיפוש
3. התא הראשון של העמודה עם ערכי החלפה מהחיפוש

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

העברה למטה בכל איטרציה מיושמת על ידי פונקציית אקסל סטנדרטית השלכה (לְקַזֵז), שבמקרה זה יש שלושה ארגומנטים - הטווח המקורי, הסטת שורה (1) והיסט עמודה (0).

ובכן, ברגע שאנו מגיעים לסוף הספרייה (n = ""), עלינו לסיים את הרקורסיה – אנו מפסיקים לקרוא לעצמנו ומציגים את מה שהצטבר לאחר כל ההחלפות במשתנה טקסט המקור t.

זה הכל. בלי פקודות מאקרו מסובכות או שאילתות Power Query - כל המשימה נפתרת על ידי פונקציה אחת.

• כיצד להשתמש בפונקציות המערך הדינמי החדש של Excel: FILTER, SORT, UNIC
• החלפה וניקוי טקסט עם הפונקציה SUBSTITUTE
  
• יצירת פקודות מאקרו ופונקציות מוגדרות על ידי משתמש (UDF) ב-VBA