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

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

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

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

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

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

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

That leaves the cross-base conversions, the ones such as HEX2BIN and OCT2HEX that change base without passing through decimal in the function name. The implementation does not carry a separate routine for every ordered pair. It parses the input string into a signed decimal value using the source base, then formats that decimal value into the destination base. Decimal is the pivot. One parse routine and one format routine, composed, cover every combination, and because both halves share the same ten-digit signed convention, a negative value survives the trip with its sign intact

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

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

שני פרטים במפענח זה קלים לטעייה. הראשון הוא היחידה המדומה החשופה. המחרוזת "i" פירושה פעם אחת i, לא אפס ולא שגיאה, כך שכאשר המקדם לפני הסיומת ריק או שהוא סימן פלוס בודד, המפענח חייב לקרוא אותו כערך 1, ומינוס בודד כ- -1. דלג על כך ו-IMSUM("i","i") מפסיק להיות 2i. השני הוא כתיב מדעי המתנגש עם הסימן שמפריד בין החלק הממשי והמדומה. המפענח מוצא את המפריד הזה על ידי סריקה של פלוס או מינוס, אך מספר שנכתב כ-"1.5E-3" מכיל מינוס השייך למעריך (exponent). הסריקה מונעת לכן התייחסות לפלוס או מינוס כמפריד כאשר התו מיד לפניו הוא 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 ביניהן, אינן עובדות בקואורדינטות קרטזיות. הן ממירות את הערך המפוענח לצורה קוטבית (polar form), מחילות את הפעולה על המודולוס והארגומנט, וממירות חזרה. שורש ריבועי חוצה את הארגומנט ולוקח את שורש המודולוס. חזקה מכפילה את הארגומנט ומעלה את המודולוס. ביצוע זה בכל דרך אחרת היה דורש גזירה מחדש של כל זהות בצורה קרטזית, שזה גם יותר קוד וגם פחות יציב מבחינה מספרית ליד נקודות ההסתעפות

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

גרסת 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

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

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

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

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

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