שרשראות השוואה, תאים ריקים ו-SUMIF ב-HotXLS לדלפי

‏HotXLS Delphi Component מעריכה את =1<2<3 כ-FALSE, אותה תשובה ש-Excel 16 נותן, כי מאז v2.384.3 ה-parser של הנוסחאות שלה מקפל אופרטורי השוואה משמאל לימין: 1<2 הופך ל-TRUE, ו-TRUE<3 הוא FALSE כי בוליאן מדורג מעל כל מספר. אותה מהדורה משווה אופרנד ריק גם ל-0 וגם ל-"", ומאפשרת ל-SUMIF למתוח טווח חיבור של תא אחד לצורת טווח הקריטריונים שלו. כל אחד מאלה נראה כמו טריוויה עד שחוברת שמחושבת בדלפי חולקת על אותה חוברת בדיוק שנפתחה ב-Excel

חוסר ההסכמה בדרך כלל מתחיל בנוסחה שמישהו כתב מאינטואיציה. מישהו מקליד =0<B2<100 כדי לבדוק שכמות נמצאת בטווח, Excel עונה בשקט FALSE לכל שורה, והגיליון יוצא לדרך עם הבאג הזה אפוי בפנים. למנוע חישוב אין זכות לתקן את כוונת המשתמש; התפקיד שלו הוא להפיק את הערך ש-Excel היה מפיק, כך שהתוצאה הממוטבעת ש-HotXLS כותבת לקובץ תתאים למה ש-Excel מציג אחרי חישוב מחדש. לפני v2.384.3 HotXLS ענתה TRUE על בדיקת הטווח הזאת בכל שורה, שגויה לכיוון השני, ודוח שנוצר על שרת היה מתנגש עם אותו דוח שנפתח על שולחן עבודה

למה =1<2<3 מחזיר FALSE ב-Excel?

‏Excel מחזיר FALSE כי הוא קורא שרשרת של השוואות בתור (1<2)<3, וה-TRUE הפנימי אז מפסיד בתחרות הדירוג מול המספר 3. ה-parser הישן של HotXLS קרא את אותו טקסט בתור 1<(2<3): TXLSSyntax.Parse_expr ב-lxFormula.pas פרסר אופרנד אחד, ראה טוקן השוואה וירד לרדת ל-Parse_expr עבור הצד הימני, מה שהופך את האופרטור ל-right-associative. זה נותן 1<TRUE, ומספר נמצא מתחת לבוליאן, כך שהתוצאה הייתה TRUE. הטעות סימטרית: =3>2>1 הוא TRUE ב-Excel והיה FALSE ב-HotXLS, ו-=1=1=TRUE הוא TRUE ב-Excel והיה FALSE לפני התיקון. הרגרסיה CalculateFormula_ComparisonChainsFoldLeftToRight מעגנת שבע נוסחאות כאלה מול הערכים ש-Excel 16 מחזיר, ומריצה כל אחת דרך שתי ארכיטקטורות המנוע, ה-TXLSWorkbook הקלאסי וה-TXLSXWorkbook הנייטיבי ל-XLSX, עם מתודת ה-Calculate שמתוארת בסקירת מנוע הנוסחאות של HotXLS

const
  Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
    '=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
  // מה ש-Excel 16 מחזיר:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate מעריך מול הגיליון הפעיל ו
    // מחזיר Null כשלחוברת אין בכלל גיליון
    Xlsx.Sheets.Add('Data');
    for i := 0 to High(Formulas) do
      Writeln(Formulas[i], '  classic=', VarToStr(Classic.Calculate(Formulas[i])),
        '  xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
  finally
    Xlsx.Free;
  end;
end;

התיקון הופך את Parse_expr ללולאה באותה צורה ש-Parse_expr1 כבר משמשת עבור +, - ו-&. היא מפרסרת את האופרנד הראשון עם Parse_expr1, וכל עוד הטוקן הבא הוא אחד מ-=, <>, <, >, <= או >=, היא יוצרת צומת השוואה, מצמידה את תוצאת השמאל המצטברת כילד ראשון, מפרסרת את האופרנד הבא עם Parse_expr1 במקום Parse_expr, והופכת את הצומת החדש לתוצאת השמאל לסבב הבא. שני פרטים היו קלים להיכשל בהם בהמרת רקורסיה לאיטרציה, ושניהם נמצאים ברשימות המתחזקים: את הצומת המצטבר צריך למסור הלאה (lChild := Item; Item := nil) בסדר הזה, ונתיב השגיאה צריך לצאת ב-Exit אחרי שחרור הצומת החצי-בנוי במקום ליפול החוצה מהלולאה ולהחזיר עץ תלוש

איך HotXLS מדרגת מספרים, טקסט ובוליאנים בהשוואה?

‏HotXLS מדרגה טיפוסים מעורבים כמו Excel: כל מספר קטן מכל ערך טקסט, וכל ערך טקסט קטן מכל בוליאן. TXLSCalculator.CompareVariants ב-lxCalc.pas מסווג את שני האופרנדים עם GetRetValueType אל ה-enumeration TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), וכששתי הקבוצות שונות הוא פשוט משווה את ה-ordinals שלהן, כך שסדר ההכרזה של ה-enum הזה הוא הכלל בין הטיפוסים. בתוך קבוצה אחת ההשוואה היא הטבעית, עם טוויסט אחד ספציפי ל-Excel עבור טקסט: שתי המחרוזות עוברות קודם דרך lxUpperCase, כך ש-="abc"="ABC" הוא TRUE. הדירוג הזה הוא הסיבה שאי אפשר להסיק על תוצאת השרשרת בליו. TRUE<3 אינו המרה של TRUE ל-1, זו בוליאן שמושווה מול מספר, והבוליאן מנצח. תאריכים הם מספרים סידוריים בעיני המנוע (varDate מסווג כ-xlNumberValue), כך שתאריך תמיד נמצא מתחת לכל טקסט, כולל טקסט שנראה במקרה כמו תאריך

למה שווה תא ריק בהשוואה?

תא ריק שמשמש אופרנד השוואה שווה ל-0 כשהצד השני הוא מספר, שווה ל-"" כשהצד השני הוא טקסט, ומאז v2.384.53 שווה ל-FALSE כשהצד השני הוא ערך לוגי, כך שעם A1 ריק =A1=0, =A1="" ו-=A1=FALSE כולם TRUE. TXLSCalculator.CompareVarValues, שמשרת את כל ששת אופרטורי ההשוואה, מחליף את הריק לפני קריאה ל-CompareVariants: אם בדיוק אופרנד אחד הוא Null הוא הופך ל-WideString('') כשהשותף שלו מחרוזת, ל-False כשהשותף שלו בוליאן, ול-0 אחרת. שני ריקים עדיין מושווים שווים זה לזה בלי החלפה. נתיב החשבון תמיד הפך ריק ל-0, ולכן =A1+1 נתן 1, אבל CompareVariants משאיר את Null בדרגה הנמוכה משלו, מתחת לכל מספר, ואופרטורי ההשוואה השתמשו בדרגה הזאת ישירות

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 נשאר ריק בכוונה

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: הריק מושווה כ-0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True לפני v2.384.3
end;

השורה האחרונה היא זו שכאבה בפועל. תחת הדירוג הישן, ריק היה קטן מכל מספר, כולל שליליים, כך ש-=IF(A1<0,"overdrawn","ok") סימן כל תא יתרה ריק כחורג, ו-=A1=0 היה FALSE עבור תא שכל משתמש יתאר כאפס. גבול אחד נשאר אחרי v2.384.3: ההחלפה בחרה רק בין 0 למחרוזת הריקה, כך שריק שהושווה מול בוליאן הפך ל-0, שמדורג מתחת גם ל-TRUE וגם ל-FALSE, ו-=A1=FALSE על A1 ריק העריך ל-FALSE. מאז HotXLS 2.384.53 ריק שמושווה מול ערך לוגי מתייחס כ-FALSE בשני המנועים, XLS ו-XLSX, כפי ש-Excel עושה: עם A1 ריק, =A1=FALSE ו-=A1<TRUE מחזירים TRUE ו-=A1=TRUE מחזיר FALSE. זה גם אומר שההשוואה לא מסוגלת להבחין בין ריק ל-FALSE, גם ב-Excel וגם ב-HotXLS; כשגיליון צריך את ההבחנה הזאת, בדקו עם ISBLANK או =A1=""

למה SUMIF עם טווח חיבור של תא אחד החזיר 0?

‏SUMIF החזירה 0 כי HotXLS צמצמה את האיטרציה לקטן מבין שני הטווחים, בזמן ש-Excel שומר על צורת טווח הקריטריונים ומשתמש בטווח החיבור רק עבור התא השמאלי העליון שלו. לכן =SUMIF(A1:A10,">5",B1) משמעו B1:B10 ב-Excel, נוחות שעליה סומכות הרבה תבניות שנבנו ביד. ה-worker המשותף TXLSCalculator.GetValueItemRange2 כיווץ בעבר את ספירות השורות והעמודות שלו אל אלה של טווח הערכים, מה שהוריד את הדוגמה לבדיקה בודדת של A1 מול B1. v2.384.3 מסיר את הצמצום: הלולאה עוברת עכשיו על טווח הקריטריונים וקוראת כל ערך באותו היסט מהפינה השמאלית העליונה של טווח החיבור. כיוון ש-CalcSumIF ו-CalcAverageIF שתיהן קוראות ל-worker הזה, AVERAGEIF מקבלת את אותו שינוי גודל, וטווח חיבור גדול מטווח הקריטריונים נחתך לצורת הקריטריונים מאותה סיבה. ארגומנט הקריטריון באמצע הוא ארגומנט ממחלקת ערך והשניים החיצוניים ממחלקת הפניה, ההבחנה שמכוסה בהמאמר על חיתוך מרומז ומחלקות ארגומנטים

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    for Row := 1 to 10 do
    begin
      Sheet.Cells[Row, 1].Value := Row;          // עמודת קריטריון: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // סכומים: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // טווח חיבור של תא אחד
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // טווח חיבור מפורש
    if Book.Recalculate = lxOk then
      // גם D1 וגם D2 הם 4000 (600+700+800+900+1000); D1 היה 0 לפני v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

‏INDIRECT ו-YEARFRAC: שני תיקונים שקטים יותר

‏INDIRECT מכבד עכשיו את הארגומנט השני שלו, וטקסט אחרי הפניה תקינה הוא שגיאה במקום להתעלם. עם a1 ב-FALSE הטקסט מפורסר כ-R1C1 מוחלט, כך ש-=INDIRECT("R2C3",FALSE) קורא את C2; הקוד הישן התעלם מהדגל, קרא את "R2" כעמודה R שורה 2, והחזיר בשקט את התא הלא נכון. הדגל נשלח לפי טיפוס ה-variant שלו (בוליאן, מספר או טקסט), כי המרה ישירה של variant מחרוזתי ל-Double מעלה חריגה. טקסט R1C1 יחסי כמו R[1]C[1] מחזיר #REF!, כי ל-INDIRECT אין מוצא של תא-נוסחה לפתור מולו, וטקסט A1 עם תווים גולשים, "B2 junk", מחזיר גם הוא #REF!. YEARFRAC עם basis 0 מפעיל עכשיו את כללי ה-NASD לסוף-פברואר ש-DAYS360 כבר יישם: כששני התאריכים הם היום האחרון של פברואר יום הסיום הופך ל-30, ואז התחלה ביום האחרון של פברואר הופכת ל-30. מ-2024-02-29 עד 2025-02-28 הספירה היא עכשיו 360 ימים, שבר של בדיוק 1, בזמן שה-Days360US הקודם ספר 359

מה התיקונים האלה מבטיחים, ומה היה הלקח?

התנהגות שרשראות ההשוואה מגובה בבדיקה שמשווה את שני המנועים מול ערכים שנמדדו ב-Excel 16, והבדיקה הזאת קיימת כי התיאור הראשון של התיקון היה שגוי. הודעת הגרסה של v2.384.3 אמרה במקור שקיפול משמאל לימין הופך את =1<2<3 ל-TRUE, שזה בדיוק מה שה-parser הימני-אסוציאטיבי הישן הפיק וההפך הגמור ממה שגם Excel וגם הקוד החדש מחזירים. אף אחד לא העריך את הדוגמה; היא נכתבה מהאינטואיציה ש-"1 קטן מ-2 שקטן מ-3". ההודעה תוקנה ובדיקת שבע הנוסחאות נוספה ב-commit המשך, והכלל שיצא מזה תקף לכל מי שמתעד סמנטיקה של גיליונות: הריצו את הדוגמה ב-Excel לפני שאתם רושמים את הערך הצפוי. החלפת האופרנד הריק ושינוי הגודל של SUMIF עוקבים אחרי אותה התנהגות Excel, כולל מקרה ריק-מול-בוליאן מאז v2.384.53, וצבירות מותנות שצריכות גם לדלג על שורות מסוננות או נסתרות עוקבות אחרי הכללים הנפרדים במאמר על שורות נסתרות ב-SUBTOTAL ו-AGGREGATE

‏HotXLS הוא רכיב גיליונות נייטיב ל-Delphi ו-C++Builder שקורא, מחשב מחדש וכותב XLS, XLSX, ODS ו-CSV בלי Excel מותקן, וכללי ההשוואה, הריק וה-SUMIF שמתוארים כאן חיים במנוע החישוב המשותף לשתי ארכיטקטורות החוברות. רשימת הפונקציות המלאה ואפשרויות הרישוי נמצאות בעמוד המוצר של רכיב הגיליונות של HotXLS לדלפי