השוואת טקסט ב-HotXLS: סדר המיון של Excel ב-Delphi

HotXLS Delphi Component משווה שני ערכי טקסט כפי ש-Excel 16 עושה זאת מאז v2.384.67: ללא התחשבות ברישיות, בסדר ה-word sort של ה-locale של משתמש ה-Windows, שהוא מה ש-CompareStringW מחזירה עם הדגל NORM_IGNORECASE. מקפים וגרשיים מדולגים במעבר הראשון ושוברים שוויון בלבד, ולכן ="a-b">"ab" הוא TRUE, בזמן שפיסוק אחר מתמיין לפני ספרות ואותיות, ולכן ="a~b"<"ab" הוא גם TRUE. אותו סדר מניע עכשיו את אופרטורי ההשוואה, את קריטריוני ה-> / <, את מיון הטווחים ואת ה-VLOOKUP

אף אחד לא פותח באג בשם "חוסר התאמה של collation". הדיווחים אומרים ש-COUNTIF(A:A,">M") סופר שתי שורות יותר על השרת מאשר ב-Excel, שרשימת מחירים ששירות הדיווח ממיין שמה את X-100 במקום ש-Excel לא היה בוחר, או ש-VLOOKUP("ABC",...) מחזיר #N/A למרות שהעמודה בבירור מכילה abc. שלושתם נובעים מאותה שאלה: כששני האופרנדים הם טקסט, מי מהם קטן יותר? ל-Excel יש תשובה מדויקת, היא לא זו שרוב הקוד של Delphi נותן, ולפני v2.384.67 HotXLS נתן שלוש תשובות שונות בהתאם לנתיב הקוד ששאל

באיזה כלל Excel משווה שתי מחרוזות טקסט?

Excel משווה טקסט לפי ה-word sort של ה-locale של המשתמש, תוך התעלמות מרישיות. word sort הוא ה-collation המוגדר כברירת מחדל של פונקציות ההשוואה של Windows NLS: אותיות מושוות לפי הסדר הלשוני שלהן ולא לפי ערכי ה-code point שלהן, אותיות מנוקדות יושבות ליד אות הבסיס שלהן, ולשני תווים יש טיפול מיוחד. המקף - והגרש ' מדולגים במעבר הראשון, ולכן co-op ו-coop נוחתים זה לצד זה, ורק כששאר המחרוזות בשוויון הנוכחות שלהם מכריעה את הסדר. כל סימן פיסוק אחר משמעותי ומתמיין לפני ספרות, וספרות מתמיינות לפני אותיות

הטבלה מראה מה זה אומר בפועל, לצד שתי ההשוואות שמפתח Delphi הכי סביר שיגיע אליהן. העמודה של Excel מחזיקה את הפסיקות ש-Excel 16 החזיר עבור IF(A<B,...), ש-HotXLS משחזר מאז v2.384.67

A מול B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" מול "ab"	גדול	קטן	קטן
"a'b" מול "ab"	גדול	קטן	קטן
"a~b" מול "ab"	קטן	גדול	גדול
"a_b" מול "ab"	קטן	קטן	גדול
"ab" מול "AB"	שווה	גדול	שווה
"é" מול "f"	קטן	גדול	גדול
"Z" מול "f"	גדול	קטן	גדול

שתי תוצאות קל לפספס. ראשית, תפקיד שבירת השוויון של המקף אומר ש-="a-b"="ab" הוא FALSE: המחרוזות שכנות קרובות במיון, ובכל זאת אינן שוות. שנית, השוויון מתעלם מרישיות לגמרי, ולכן ab, AB ו-Ab הם אותו מפתח לכל עניין של השוואה. מיון 20 מילות בדיקה עם ה-Range.Sort של Excel נותן a b, a.b, a_b, a~b, a0, a1b, ab / AB / Ab, ab-, a'b, a-b, -ab, ab1, abc, b, e, é, f, Z; בתוך קבוצת ה-ab, המיקום של התו המדולג מכריע

איך קיבעו את סדר הטקסט של Excel?

את סדר הטקסט של Excel זיהו במדידה, לא בתיעוד, כי התיעוד של Excel לא קורא בשם ל-collation. הבדיקה יצרה 4,000 זוגות מחרוזות אקראיים מתוך פיסוק ASCII, ספרות, שני סוגי רישיות, רווחים, é, ß, ä, תווים סיניים, צורות ברוחב מלא ורווח בלתי שובר, באורכים 0 עד 4, כשחצי מהזוגות נבנו ככמעט-זהים זה לזה. Excel 16 חישב IF(A<B,-1,IF(A=B,0,1)) עבור כל זוג, והפסיקות הושוו מול API ההשוואה של Windows עם סטים שונים של דגלים

• NORM_IGNORECASE לבדו (word sort כברירת מחדל, locale של המשתמש): אף אי-התאמה אמיתית. שבעת ההבדלים היחידים היו תאים שכל תוכנם ', ש-Excel בולע בתור תו הקידומת של הטקסט, ולכן הם היו ממצאי דגימה ולא הבדלי collation
• NORM_IGNORECASE עם SORT_STRINGSORT: 41 אי-התאמות. string sort מתייחס למקף ולגרש בתור סמלים רגילים, מה שהוא בדיוק ההתנהגות שאין ל-Excel
• הוספת NORM_IGNOREWIDTH: שגוי בדרך אחרת, כי זה גורם לצורות ברוחב מלא וברוחב חצי של אותה אות להשוות כשוות, ו-Excel מפריד ביניהן

בדיקה שנייה, שנבחרה ידנית, השוותה את כל 190 הזוגות שנשאבו מ-20 מילים מסובכות ואת תוצאת ה-Range.Sort של Excel על אותה עמודה. שניהם הסכימו עם word sort רגיל של NORM_IGNORECASE, ואותן 190 פסיקות בתוספת הסדר הממוין הן עכשיו חלק מחבילת הרגרסיה של HotXLS, שרצה דרך שני המנועים — ה-TXLSWorkbook הקלאסי וה-TXLSXWorkbook הילידי ל-XLSX

מדוע CompareText והשוואה ordinal טועים?

CompareText והשוואה ordinal טועים בסדר של Excel כי הם משווים יחידות UTF-16, וסדר ה-code point שם פיסוק במקומות שרירותיים ביחס לאותיות. המקף הוא U+002D והגרש U+0027, שניהם מתחת לכל אות, ולכן השוואה ordinal קוראת ל-"a-b" קטן מ-"ab" במקום להתייחס למקף בתור שובר שוויון. הטילדה U+007E יושבת מעל כל אות, ולכן "a~b" יוצא גדול יותר, ההפך מ-Excel. ה-CompareText ב-RTL של Delphi מקפל רק את a..z לאותיות גדולות ואז משווה יחידות קוד, מה שמוסיף עיוות שני: הקו התחתון U+005F יושב בין האותיות הגדולות לקטנות, ולכן קיפול לאותיות גדולות מזיז את "a_b" מתחת ל-"ab" אל מעליו. אף אחת מהפונקציות לא יודעת ש-é שייך בין e ל-f

הכלים הרגילים של Delphi נופלים משני צידי הקו:

• CompareStr, האופרטור < למחרוזות ו-TComparer<string>.Default (שקורא ל-CompareStr) הם ordinal ורגישים לרישיות, ולכן TArray.Sort<string> בלי משווה שם את Z לפני f
• CompareText ו-SameText הם ordinal אחרי קיפול רישיות של ASCII בלבד
• AnsiCompareText ו-WideCompareText ב-RTL של Delphi ב-Windows קוראים ל-CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), אותה קריאה שתואמת את Excel. TStringList ממוין עם ברירות המחדל שלו (UseLocale True, CaseSensitive False) עובר דרך AnsiCompareText ולכן מסכים עם Excel גם כן
• ביעדי POSIX ה-RTL של Delphi מנתב את AnsiCompareText דרך ממיין ICU, שהוא אלגוריתם אחר עם כללי פיסוק אחרים, וה-AnsiCompareText של Free Pascal ב-Windows קורא ל-CompareStringA אחרי המרה ל-ANSI code page, מה שמאבד כל תו שה-page הזה לא יכול לייצג

כלומר הפונקציות של ה-RTL שמודעות ל-locale נכונות ב-Windows בזכות המימוש, לא בזכות חוזה, וקוד שצריך את הסדר של Excel מרוויח מקריאה מפורשת ל-API. HotXLS היה עם אותו מיזוג בפנים. אופרטורי ההשוואה העלו את שתי המחרוזות לאותיות גדולות והשוו ערכי קוד, הענפים > / < של פונקציות הקריטריונים השתמשו בהשוואת Variant של Delphi הרגישה לרישיות, וה-VLOOKUP / HLOOKUP השוו טקסט עם אותה השוואת Variant רגישה לרישיות, מה שהסביר מדוע VLOOKUP("ABC",A1:A20,1,FALSE) לא יכל למצוא את abc. מיון הטווח כבר השתמש ב-WideCompareText. שלושה נתיבים, שלושה סדרים

מה השתנה ב-HotXLS v2.384.67?

מאז v2.384.67 ההשוואות טקסט-מול-טקסט בנתיבי החישוב והמיון של HotXLS עוברות דרך פונקציה אחת, XlsCompareText ב-lxStandard.pas, שקוראת ל-CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) ומחסירה את CSTR_EQUAL. הקוראים הם ששת אופרטורי ההשוואה, השוואות איבר-איבר בנוסחאות מערך, הענפים >, <, >= ו-<= של קריטריונים בסגנון COUNTIF ושל פונקציות המסד, VLOOKUP ו-HLOOKUP (מדויק ומקורב), העוזרי הסידור מאחורי פונקציות ה-dynamic array ו-XLOOKUP / XMATCH, ומיון הטווח של שני המנועים. ניתוב מיון הטווח דרך אותה פונקציה מבטיח שסדר המיון וסדר ההשוואה לא יוכלו להתרחק שוב, וזה חשוב כי VLOOKUP מקורב על טקסט משמעותי רק כשהעמודה ממוינת בסדר שבו ה-lookup משווה

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate מחשב מול הגיליון הפעיל
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: המקף רק שובר שוויון
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: השוויון נשבר, אינם שווים
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: פיסוק קודם
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: מתעלמים מרישיות
  finally
    Book.Free;
  end;
end;

השוואות בין-טיפוסים הן כלל נפרד ולא השתנו: כל מספר קטן מכל ערך טקסט וכל ערך טקסט קטן מכל boolean, כמתואר ב-המאמר על שרשראות השוואה, אופרנדים ריקים ו-SUMIF. ה-word sort חל רק כששני האופרנדים טקסט. התאמת wildcards נפרדת גם היא: קריטריון כמו "a*" או "=ab" הוא בדיקת תבנית או שוויון, מכוסה ב-המדריך ל-wildcards של Excel ב-COUNTIF, MATCH ו-DSUM, וה-collation שנדון כאן מכריע רק את אופרטורי הסידור

הדוגמה הבאה טוענת את 20 מילות הבדיקה לעמודה, ממיינת אותה עם TXLSXWorksheet.SortRange, ובודקת ספירת קריטריון ו-lookup. הספירות הן אלה ש-Excel 16 החזיר עבור אותה עמודה

const
  Words: array [0..19] of string = ('ab', 'a-b', 'a~b', 'a_b', 'AB', 'a b',
    'ab1', 'ab-', '-ab', 'abc', 'a''b', 'Ab', 'b', 'a.b', 'a1b', 'a0',
    #$00E9, 'e', 'f', 'Z');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Words');
    for i := 0 to High(Words) do
      Sheet.Cells[i + 1, 1].Value := WideString(Words[i]);

    // Excel 16 על אותה עמודה: 11, 11, 14
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">ab")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,"<a-b")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">=AB")')));

    // היה #N/A לפני v2.384.67: ה-lookup השווה ברגישות לרישיות
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // עמודת מפתח אחת, סדר עולה: a b, a.b, a_b, a~b, a0, a1b, ab, AB, Ab, ...
    Sheet.SortRange(1, 1, 20, 1, [1], [False]);
    for i := 1 to 20 do
      Writeln(VarToStr(Sheet.Cells[i, 1].Value));
  finally
    Book.Free;
  end;
end;

TXLSXWorksheet.SortRange משתמש ב-merge sort יציב, ולכן ab, AB ו-Ab, שמושווים כשווים, שומרים על הסדר היחסי שהיה להם לפני המיון. תאים ריקים הולכים לסוף בשני הכיוונים, כמו ב-Excel

איך מתאימים את סדר המיון של Excel בקוד Delphi משלכם?

כדי להתאים את סדר הטקסט של Excel בקוד Delphi משלכם, קוראים ל-CompareStringW עם LOCALE_USER_DEFAULT ו-NORM_IGNORECASE, ולא מוסיפים SORT_STRINGSORT או NORM_IGNOREWIDTH. ערך ההחזרה אינו תוצאת השוואה חתומה: ה-API מחזיר CSTR_LESS_THAN (1), CSTR_EQUAL (2) או CSTR_GREATER_THAN (3), ו-0 כשהקריאה נכשלת. מחסירים 2 כדי לקבל את המוסכמה הרגילה של שלילי / אפס / חיובי, ובודקים 0 קודם, כי כשל שמתפרש בטעות בתור תוצאה הופך ל-‎-2, "קטן מ" שקט

uses
  Winapi.Windows, System.SysUtils, System.Generics.Defaults,
  System.Generics.Collections;

// סדר הטקסט של Excel: word sort של ה-locale המשתמש, ללא התחשבות ברישיות
function ExcelCompareText(const A, B: string): Integer;
var
  R: Integer;
begin
  R := CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE,
    PWideChar(A), Length(A), PWideChar(B), Length(B));
  if R = 0 then
    RaiseLastOSError;          // 0 הוא כשל, לא תוצאת השוואה
  Result := R - CSTR_EQUAL;    // 1/2/3 ממופים אל -1/0/1
end;

var
  Keys: TArray<string>;
begin
  Keys := ['abc', 'a-b', 'AB', 'a~b', '-ab', 'ab'];
  TArray.Sort<string>(Keys, TComparer<string>.Construct(
    function(const L, R: string): Integer
    begin
      Result := ExcelCompareText(L, R);
    end));
  // a~b, ab / AB (שווים, בכל סדר), a-b, -ab, abc
end;

TArray.Sort אינו יציב, ולכן מפתחות שמושווים כשווים, כמו ab ו-AB, עשויים לצאת בכל סדר; אם הסדר המקורי של מפתחות שווים חשוב, ממיינים מערך אינדקסים עם המיקום המקורי בתור מפתח משני. המקרה ההפוך גם צץ: לפעמים עמודה חייבת שלא ללכת בסדר של Excel, למשל מספרי חלקים שבהם X-100 ו-X100 הם קודים נפרדים וצריכים להתמיין לפי code point. ל-TXLSXWorksheet.SortRange יש overload שמקבל TXLSSortCompareEvent, מתודה בחתימה function(const Left, Right: Variant): Integer of object, ומשתמש בה במקום ההשוואה המובנית

uses
  System.SysUtils, System.Variants, lxStandard, lxHandleX;

type
  TPartNumberOrder = class
    function Compare(const Left, Right: Variant): Integer;
  end;

function TPartNumberOrder.Compare(const Left, Right: Variant): Integer;
begin
  // משווה מותאם מקבל גם תאים ריקים (בתור Null): ממקמים אותם בעצמכם
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal ורגיש לרישיות
end;

var
  Sheet: TXLSXWorksheet;   // גיליון מלא, שורות 2..501, עמודות A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // ממוין לפי עמודה A, בסדר עולה
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

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

מדוע אותה חוברת עבודה עשויה להתמיין אחרת במכונה אחרת?

אותה חוברת עבודה יכולה להתמיין אחרת במכונה אחרת כי סדר הטקסט של Excel תלוי ב-locale המשתמש של Windows, ו-HotXLS הולך אחרי התלות הזאת בכוונה. word sort ספציפי לשפה: ה-collation השוודי, למשל, שם את ä אחרי z, בזמן שאנגלית וגרמנית משאירות אותו ליד ה-a. Excel יורש את זה מה-locale שתחתיו הוא רץ, ולכן חוברת שתחושב מחדש על ידי עמית מסטוקהולם יכולה להחזיר COUNTIF(...,">y") אחר מאשר אותו קובץ על שולחן עבודה בשיקגו. HotXLS מעביר LOCALE_USER_DEFAULT כדי שהתוצאות שלו שוות לאלה של Excel באותה מכונה; כל locale קבוע היה גורם ל-HotXLS לחלוק על Excel בכל מכונה עם הגדרה אחרת

שלוש תוצאות מעשיות נובעות מכך ליצירה בצד שרת:

• ה-locale שנספר הוא זה של החשבון שבו התהליך רץ. שירות Windows או application pool של IIS עשויים להשתמש בפורמט אזורי אחר מזה של שולחן העבודה של המפתח, ולכן תוצאות שנצפו ב-IDE אינן אוטומטית מה שהפרודקשן מחשב
• תוצאות נוסחה שנשמרו במטמון בקובץ משקפות את ה-locale של מכונת הייצור. Excel מחשב מחדש עם ה-locale של עצמו, ולכן ערך עשוי להשתנות כשהקובץ נפתח במקום אחר ומחושב מחדש; זו התנהגות של Excel, לא ממצא של HotXLS
• locales חולקים בעיקר על אותיות מנוקדות, על צירופי אותיות ששפות מסוימות מתייחסות אליהם בתור אות אחת, ועל כתבים שאינם לטיניים, ולכן נתוני בדיקה שמוגבלים למילים אנגליות פשוטות לא יגלו את הבעיה

גבול הפלטפורמה פשוט. HotXLS היא ספריית Windows, שנבנית עבור Win32 ו-Win64 עם Delphi ו-C++Builder ועבור יעדי win32 / win64 עם Lazarus ו-Free Pascal, וכל ה-builds האלה קוראים לאותה CompareStringW. אין נתיב collation נפרד שאינו Windows. ה-fallback היחיד הוא לקריאת API שנכשלה: אם CompareStringW מחזירה 0, XlsCompareText משווה את המחרוזות לאחר קיפול רישיות לפי יחידת קוד במקום להעלות חריגה באמצע חישוב מחדש, מה שמשאיר את החישוב רץ אבל כבר לא מבטיח את הסדר של Excel

עזר זריז: השוואת טקסט של Excel ב-HotXLS

• הכלל: word sort של ה-locale של המשתמש עם NORM_IGNORECASE, בלי SORT_STRINGSORT, בלי NORM_IGNOREWIDTH, ב-HotXLS מאז v2.384.67
• - ו-' רק שוברי שוויון: ="a-b">"ab" הוא TRUE ו-="a-b"="ab" הוא FALSE
• פיסוק אחר מתמיין לפני ספרות, ספרות לפני אותיות: ="a~b"<"ab" ו-="a0"<"ab" הם TRUE
• רישיות לעולם לא משנה: ="ABC"="abc" הוא TRUE ו-VLOOKUP("ABC",...) מוצא את abc
• נתיבים מכוסים: אופרטורי השוואה, השוואות מערך, קריטריוני > / <, VLOOKUP / HLOOKUP, סידור dynamic array, SortRange בשני המנועים
• לא מכוסה על ידי הכלל הזה: טיפוסים מעורבים (מספר < טקסט < boolean) וקריטריוני wildcard, שיש להם כללים משלהם
• בקוד Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), בודקים 0, מחסירים CSTR_EQUAL; נמנעים מ-CompareText, CompareStr ו-TComparer<string>.Default כשהתוצאה חייבת להסכים עם Excel
• התוצאות תלויות ב-locale של החשבון שמריץ את הקוד, גם ב-Excel וגם ב-HotXLS

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