פונקציות הנדסה ב-Delphi: המרת בסיסים, מתמטיקה מרוכבת

משפחת ההנדסה ב-Excel נקראת כמו הפינה הקלה ביותר של הפניות הפונקציות. DEC2BIN הופכת מספר למחרוזת בינארית. HEX2DEC הופכת אותו בחזרה. IMSUM מחברת שני מספרים מרוכבים. כל אחת מהן נראית כמו תרגיל בעיצוב. הן לא. מאחורי השמות הללו מסתתר קידוד משלים לשתיים של עשרה ביטים שרוב המפתחים לא נגעו בו מאז קורס ארכיטקטורת מחשבים, פורמט של מספר מרוכב שחי כולו בתוך מחרוזות, ואופרטורים ברמת הסיביות שיגרמו לגלישה (overflow) שקטה של מספר שלם בן 64 ביטים אם תזיזו לפני שתבדקו. מנוע גיליון אלקטרוני שמשחזר את Excel במדויק אינו יכול לעגל פינות באף אחד מאלה

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

המרת בסיס ומשלים לשתיים של עשרה ביטים

הכיוון הקדמי הוא החלק שכולם מצפים לו. DEC2BIN(9) נותן "1001", וארגומנט שני אופציונלי מוסיף אפסים משמאל לתוצאה לרוחב קבוע. המלכודת היא קלט שלילי. Excel אינו כותב סימן מינוס. הוא מקודד את הערך כמחרוזת משלים לשתיים של עשר ספרות בבסיס היעד, וזו הסיבה ש-DEC2BIN(-5,10) מחזיר "1111111011" במקום משהו עם סימן. ארגומנט המקומות (places) מתעלם ברגע שהערך שלילי, מכיוון שהקידוד כבר מקובע לעשר ספרות

עשר ספרות הן תקציב קבוע, והתקציב הזה קובע את הטווח הניתן לייצוג לכל בסיס. בבינארי הגודל (magnitude) שמתהפך לחצי השלילי הוא 512, ומודולוס העטיפה (wrap modulus) הוא 1024, כך שמחרוזת בינארית מקבלת סימן (signed) רק כאשר היא בדיוק באורך של עשרה תווים וערכה הוא לפחות 512. אותו רעיון גדל עם הבסיס. אוקטלי משתמש בסף חצי של 2^29 ובמודולוס מלא של 2^30. הקסדצימלי משתמש ב-2^39 ו-2^40. קורא ה-HotXLS מיישם בדיוק כלל זה: הוא צובר את הספרות, ורק כאשר המחרוזת ברוחב עשרה תווים והערך המצטבר יושב בסף החצי או מעליו, הוא מפחית את המודולוס המלא כדי לשחזר את הערך עם הסימן. מחרוזת בת תשעה תווים היא תמיד לא שלילית, לא משנה כמה היא גדולה

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

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

מספרים מרוכבים הם מחרוזות, ולכן העבודה היא פענוח

ל-Excel אין סוג נתונים מרוכב. ערך מרוכב הוא המחרוזת "a+bi", וכל פונקציה במשפחת IM לוקחת את המחרוזות הללו פנימה ומחזירה אחת. COMPLEX בונה את המחרוזת מחלק ממשי וחלק מדומה. IMSUM, IMSUB, IMPRODUCT ו-IMDIV מפענחות את הארגומנטים שלהן, מבצעות את האריתמטיקה על החלקים המספריים, ומעצבות את התוצאה בחזרה למחרוזת. העבודה המספרית היא אלגברה של תואר ראשון. הקושי טמון כולו בהפיכת הטקסט לשני מספרים עם נקודה צפה באופן אמין, וזה המקום שבו המפענח הפנימי מרוויח את לחמו

קל לטעות בשני פרטים במפענח זה. הראשון הוא היחידה המדומה החשופה. המחרוזת "i" פירושה אחד כפול i, לא אפס ולא שגיאה, ולכן כאשר המקדם לפני הסיומת ריק או שהוא סימן פלוס בודד, המפענח חייב לקרוא אותו כערך 1, ומינוס בודד כ--1. דלגו על כך ו-IMSUM("i","i") יפסיק להיות 2i. השני הוא כתיב מדעי המתנגש עם הסימן המפריד בין החלק הממשי והמדומה. המפענח מוצא מפריד זה על ידי סריקה אחר פלוס או מינוס, אך מספר הכתוב כ-"1.5E-3" מכיל מינוס השייך למעריך. הסריקה לכן מסרבת להתייחס לפלוס או מינוס כמפריד כאשר התו שמייד לפניו הוא e או E. ללא השמירה הזו החלק הממשי היה נקרע לשניים בסימן המעריך והפענוח היה נכשל על קלט תקין לחלוטין

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

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Engineering');
    // Negative input: a ten-bit two's complement, places argument ignored.
    Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
    // Complex multiply on two "a+bi" strings.
    Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
  finally
    Book.Free;
  end;
end;

הפונקציות המרוכבות הטרנסצנדנטליות, IMSQRT, IMEXP, IMLN ו-IMPOWER ביניהן, אינן פועלות בקואורדינטות מלבניות. הן ממירות את הערך המפוענח לצורה קוטבית, מחילות את הפעולה על המודולוס והארגומנט, וממירות בחזרה. שורש ריבועי חוצה את הארגומנט ולוקח את השורש של המודולוס. חזקה מכפילה את הארגומנט ומעלה את המודולוס בחזקה. לעשות זאת בכל דרך אחרת היה אומר לגזור מחדש כל זהות בצורה מלבנית, מה שגם אומר יותר קוד וגם פחות יציבות מספרית ליד חתכי הענף (branch cuts)

אופרטורים ברמת הסיביות והגלישה שאתם חייבים לבדוק קודם

Excel 2013 הוסיף את BITAND, BITOR, BITXOR, BITLSHIFT ו-BITRSHIFT. האופרנדים מוגבלים: כל אחד חייב להיות מספר שלם לא שלילי שאינו גדול מ-2^48 פחות 1, וכל ארגומנט שברירי או שלילי הוא שגיאה מספרית. מכסה זו נדיבה מספיק כדי לכסות כל קבוצת דגלים מציאותית תוך הישארות בטוחה בתוך הטווח הניתן לייצוג מדויק של double, וזה חשוב מכיוון ש-Excel מעביר כל ארגומנט מספרי כערך של נקודה צפה

פונקציות ההזזה (shift) נושאות את כלל הסדר היחיד שבאמת נושך. הזזה שמאלה יכולה להפיק ערך גדול בהרבה מהקלט שלה, ואם תבצעו את ה-shl תחילה ותבדקו את התוצאה לאחר מכן כבר חרגתם מ-Int64 והבדיקה חסרת משמעות. הבדיקה חייבת לבוא לפני ההזזה. HotXLS משווה את האופרנד מול התקרה המוזזת ימינה לפי כמות ההזזה, ורק אם האופרנד מתאים הוא מבצע את ההזזה שמאלה בפועל. גודל הזזה שמעבר ל-53 סיביות נדחה על הסף, והזזה שלילית פשוט הופכת כיוון, כך ש-BITLSHIFT עם ספירה שלילית מתנהג כהזזה ימינה. העיקרון חורג הרבה מעבר לפונקציה אחת זו: כאשר קיימת שמירה כדי למנוע גלישה, עליה לרוץ על הקלטים, לעולם לא על התוצאה שהיא נועדה להגן עליה

// Bitwise calls evaluate the same way through Calculate.
Sheet.Cells[3, 1].Value := Sheet.Calculate('=BITAND(13,11)');    // 9
Sheet.Cells[4, 1].Value := Sheet.Calculate('=BITLSHIFT(5,2)');   // 20
Sheet.Cells[5, 1].Value := Sheet.Calculate('=BITRSHIFT(40,3)');  // 5

פונקציות עתידיות והקידומת של השם _xlfn

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

הקאץ' הוא שהכלל אינו אחיד. לכמה פונקציות חדשות יותר ניתנו משבצות טבלה והן נכתבות חשופות, בעוד שכמה פונקציות נסתרות מדור קודם (legacy) נכתבות גם הן ללא קידומת למרות גילן. HotXLS שומר רשימה לבנה (whitelist) מפורשת של אילו שמות זקוקים לקידומת, מוסיף אותה בכתיבה ומסיר אותה בקריאה, כך שטקסט הנוסחה שאתם מגדירים וקוראים בחזרה הוא תמיד השם הנקי הפונה ל-Excel. אתם מגדירים =BITLSHIFT(5,2), הקובץ מחזיק _xlfn.BITLSHIFT, והערך חוזר כ-20 ללא קשר. הקידומת היא פרט אחסון שלעולם לא צריך לדלוף לתוך הנוסחאות שאתם עובדים איתן בקוד

הרכבת הכל בגיליון עבודה

שטח הפנים הציבורי של כל זה הוא קטן. צרו TXLSXWorkbook, הוסיפו גיליון עבודה, וכתבו נוסחה לתא דרך Cells[Row, Col].Formula וחשבו מחדש, או העריכו ביטוי ישירות עם מתודת ה-Calculate של הגיליון האלקטרוני, שמהדרת את הנוסחה מול הגיליון הזה ומחזירה Variant. הדוגמאות לעיל משתמשות ב-Calculate מכיוון שזה מראה את התוצאה של קריאת הנדסה אחת ללא מצב הגיליון שמסביב, אך אותן פונקציות מוערכות באופן זהה בתוך נוסחאות תאים אמיתיות כאשר חוברת העבודה מחושבת מחדש

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

אם אתם מחברים את המתמטיקה של הדומיין שלכם לאותו מנוע, המכניקה של רישום מטפל והחזרת ערכים מכוסים במאמר שלנו על הרחבת מנוע הנוסחאות עם פונקציות מותאמות אישית, וכאשר הנוסחאות הללו צריכות להגיע לגיליונות לפי שם ולא לפי כתובת תא, ההדרכה על שמות מוגדרים ונוסחאות חוצות-גיליונות מראה כיצד ההפניות נפתרות. פונקציות ההנדסה המתוארות כאן נשלחות כחלק מ-רכיב הגיליון האלקטרוני HotXLS עבור Delphi ו-C++Builder, יחד עם ממשקי ה-API של קריאה, כתיבה וחישוב המכוסים במקומות אחרים בבלוג זה