סריקות Lookup והפניות מעגליות כוזבות ב-HotXLS

שימו =VLOOKUP(A1,B:B,1) בתא בעמודה B ו-Excel מחשב אותה ללא תלונה. מסרו את אותה חוברת למנוע חישוב מחדש מבוסס גרף תלויות וסביר שתקבלו שגיאת הפניה מעגלית, כי הנוסחה תלויה בטווח שמכיל את הנוסחה. HotXLS דיווח בדיוק על כך עד v2.361.98. התיקון אינו מקרה מיוחד לטווחי עמודות שלמות; הוא הבחנה בין שני סוגי קשת תלות שמנוע גיליון נתונים צריך ולגרף מכוון פשוט אין

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

למה Excel מתיר לטווח lookup להכיל את הנוסחה?

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

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

מה גרף תלויות עושה עם אותה נוסחה

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

חלצו תלויות מ-=VLOOKUP(A1,B:B,1) בתא B7 והארגומנט השני מניב טווח שמכיל את B7 עצמו. לגרף יש עכשיו לולאה-עצמית. הדרגה הנכנסת של אותו צומת לעולם לא מגיעה לאפס, ולכן המעבר הטופולוגי לעולם לא יכול לתזמן אותו, ומעבר הרכיבים מסווג אותו כמעגל. המנוע מנמק נכון לגבי הגרף שניתן לו. הגרף הוא המודל השגוי, כי הוא מקודד סוג קשת אחד במקום שבו לגיליון יש שניים

שתי מחלקות קשת, גרף אחד

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

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

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // טווח ה-lookup מכסה את עמודה B, והנוסחה הזאת חיה בו
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // לפני v2.361.98 הענף הזה לא היה בר-השגה עבור הגיליון הזה
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

מה אתם מוותרים עליו בהוצאת קשתות סריקה מהסדור

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

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

סכנת הרגרסיה המסתתרת בתוך התיקון

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

// שדה Boolean חדש ב-record לא מנוהל הופך כל אתר בנייה ידני
// לבאג רדום. שתי אידיומות בטוחות:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // אפסו הכול, ואז מלאו
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // או הגדירו כל שדה, כולל החדש, בכל אתר
  R.LookupScan := False;
end;

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

הבחנה בין מעגל אמיתי לחפיפת סריקה

שום דבר בשינוי הזה לא מחליש את זיהוי המעגלים. =B7+1 ב-B7 עדיין מעגל, שרשרת של שלוש נוסחאות שנסגרת על עצמה עדיין מעגל, ושניהם עדיין מדווחים דרך תוצאת החישוב מחדש כשחברי המעגל שומרים על ערכי המטמון הקודמים שלהם בזמן שהכול מחוץ למעגל נשאר עדכני. מה שהשתנה הוא רק שארגומנט מערך-ה-lookup כבר לא מייצר מעגלים ש-Excel לא רואה

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