Show Values As في Pivot بـ Delphi: الترتيب والإجمالي الجاري

يضبط HotXLS أنماط "Show Values As" في pivot table من Excel من Delphi عبر خاصيتين على TXLSPivotDataField: ShowDataAs للأنماط الكلاسيكية كالإجمالي الجاري والفرق ونسبة الصف، و ExtendedShowDataAs للترتيب ونسبة الأب والإجمالي الجاري بالنسبة المئوية. ثم يكتب TXLSXWorkbook.MaterializeNativePivotTable الشبكة المحسوبة بالطريقة التي كان سيفعلها Excel. الخاصيتان متنافيتان، والموسَّعة منها تعتمد على URI امتداد يطابقه Excel حساساً لحالة الأحرف

تلك التفصيلة الأخيرة سبب وجود هذه المقالة. جدول pivot بعمود ترتيب يبدو شيئاً صغيراً توليده، ولأول فتح عادةً يكون كذلك. تبدأ المشكلة عندما ينقر مستخدم Refresh في Excel فيعود عمود الترتيب إلى مجموع عادي دون أي تحذير. إتقان Show Values As يعني إتقان مفردتَي XML، إضافة إلى حفنة قواعد رقمية لا يوثّقها Excel في الواجهة أبداً: كيف تترتب التساويات، وماذا تعني نسبة تراكمية لمتوسط، وأي خلية فارغة تصبح فراغاً وأيها خطأ

لماذا خاصيتان لقائمة Excel واحدة؟

يعرض Excel قائمة "Show Values As" واحدة، لكن الملف يخزنها في مكانين مختلفين، فيكشف HotXLS عن خاصيتين. الأنماط الأصلية من Excel 2007 تعيش في خاصية showDataAs لعنصر dataField داخل pivotTableDefinition، مستخدمةً تعداد ECMA-376 المسمى ST_ShowDataAs. أما الأنماط التي أضافها Excel 2010، الترتيب والنسبتان الأبوية والتراكمية، فلم تتسع في ذلك التعداد، فذهبت إلى عنصر x14:dataField بخاصية pivotShowAs، مغلَّفة في كتلة امتداد extLst على حقل البيانات نفسه

TXLSPivotShowDataAs يعكس المجموعة الأولى: xlpsdaNormal و xlpsdaDifference و xlpsdaPercent و xlpsdaPercentDiff و xlpsdaRunTotal و xlpsdaPercentOfRow و xlpsdaPercentOfCol و xlpsdaPercentOfTotal و xlpsdaIndex. و TXLSPivotExtendedShowDataAs يغطي الثانية: xlpesdaPercentOfParent و xlpesdaPercentOfParentRow و xlpesdaPercentOfParentColumn و xlpesdaPercentOfRunningTotal و xlpesdaRankAscending و xlpesdaRankDescending، مع xlpesdaNone بوصفه القيمة المحايدة. حقل البيانات يستخدم إحدى المجموعتين لا كليهما أبداً. إذا تركت ShowDataAs على قيمة غير normal وضبطت نمطاً موسَّعاً أيضاً، يعيد MaterializeNativePivotTable قيمة -1 ويترك الإخراج الموجود كما كان تماماً، الخلايا وحدود الـ pivot شمولاً

BaseField و BaseItem يحملان هدف المقارنة. الـ BaseField فهرس صفري الأساس في قائمة حقول جدول الـ pivot، لا في أسماء ترويسات الأعمدة المصدرية، وهو يقود المقارنات والإجماليات الجارية ونسبة أب مختار والترتيبات. أما BaseItem فهو موضع عنصر صفري الأساس داخل ذلك الحقل، أو أحد الثابتَين xlPivotBaseItemPrevious و xlPivotBaseItemNext المقابلَين لـ "مقارنة بالجار". نِسب الصف الأبوي والعمود الأبوي تتجاهل BaseField تماماً؛ فهي تختار الحقل الأعم داخلياً على المحور المطابق

بناء pivot مرتَّب في Delphi

أقصر طريق عملي هو بناء الـ pivot، وضبط نمط العرض على حقل البيانات، ثم تجسيده قبل الحفظ. المثال أدناه يرتّب المبالغ الإقليمية لكل فئة ترتيباً تنازلياً، فيكون أكبر مبلغ في كل صف ترتيبه 1

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book: TXLSXWorkbook;
  Data, Report: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
  Measure: TXLSPivotDataField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Data.Cells[1, 1].Value := 'Category';
    Data.Cells[1, 2].Value := 'Region';
    Data.Cells[1, 3].Value := 'Amount';
    // ... املأ الصفوف 2..7 ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
    Pivot.AddRowField('Category');     // حقل pivot رقم 0
    Pivot.AddColumnField('Region');    // حقل pivot رقم 1
    Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
    Measure.DisplayName := 'Rank by Region';

    Measure.ShowDataAs := xlpsdaNormal;              // النمط الأساسي يبقى مطفأ
    Measure.ExtendedShowDataAs := xlpesdaRankDescending;
    Measure.BaseField := 1;                          // الترتيب عبر Region

    if Book.MaterializeNativePivotTable(Pivot) <> 1 then
      raise Exception.Create('Pivot display mode was rejected');
    Book.SaveAs('region-rank.xlsx');
  finally
    Book.Free;
  end;
end;

عادتان تجديان نفعاً هنا. الأولى، اضبط ShowDataAs := xlpsdaNormal صراحةً عندما تختار نمطاً موسَّعاً، حتى على حقل طازج، لأن الكود نفسه كثيراً ما يُعاد استخدامه ضد pivot مستورد يحمل حقل بياناته نمطاً أساسياً أصلاً. والثانية، افحص القيمة المعادة. التجسيد المرفوض ذرّي، وهذا جيد، لكنه يعني أيضاً أن فحصاً منسيّاً سيشحن ملفاً حاوياً للشبكة السابقة

BaseField ينجو من الحفظ حتى لو لم تستدعِ MaterializeNativePivotTable قط. يكتب HotXLS خاصية baseField لنسبة الأب والإجمالي الجاري بالنسبة المئوية ووضعَي الترتيب كليهما بمجرد ضبط النمط الموسَّع، فما يزال مصنفاً حُفظ مباشرة بعد التكوين يخبر Excel أي حقل يترتب عبره عند التحديث

ماذا يحدث لـ URI الامتداد؟

كتلة الامتداد التي تحمل pivotShowAs يُعرَّفها الـ URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}، ويقارن Excel تلك السلسلة حساساً لحالة الأحرف أثناء التحديث. لاحظ الـ e الصغيرة في 4e99. GUIDs عادةً غير حساسة لحالة الأحرف وكثير من كتّاب XML يحوّلها إلى أحرف كبيرة، وهذا بالضبط نمط الفشل: ملف فيه 4E99 يفتح في Excel كل خلاياه المخزنة صحيحة، لأن Excel يرسم القيم المخزنة فحسب. لكن بعد RefreshTable لم يعد Excel يعترف بالامتداد، فيُسقط النمط الموسَّع ويعيد حساب العمود بوصفه مجموعاً عادياً. لا نافذة إصلاح، ولا خطأ، مجرد عمود ترتيب صار إجماليات بهدوء

يكتب HotXLS الـ URI الحرفي كما يتوقعه Excel، فلا تعضّك هذه إلا إذا أعاد شيء لاحق كتابة الحزمة، مثلاً خطوة معالجة لاحقة تطبّع حالة أحرف GUID أو تعيد بناء عناصر extLst. الاختبار الذي يمسكها رخيص: افتح الملف المولَّد في Excel، وحدّث الـ pivot، وتأكد أن العمود الموسَّع ما يزال يعرض ترتيبات أو نسباً لا تجميعات خام. اختبار يقرأ الخلايا المخزنة فقط سينجح على ملف معطوب في كل مرة

كيف يرتب Excel التساويات ويرتّب الإجماليات الجارية؟

ترتيب pivot في Excel ترتيب كثيف (dense rank)، ويعيد HotXLS إنتاجه. بمبالغ تنازلية 30 و30 و20، تكون النتيجة 1 و1 و2، لا 1 و1 و3 بأسلوب المنافسة الذي تحصل عليه من RANK.EQ على ورقة عمل. التساوي يُعد قيمة سابقة مميزة واحدة. وإذا احتاج تقرير ترتيباً منافساً، فاحسبه في البيانات المصدرية أو في معادلة ورقة عمل بجوار الـ pivot، لأنه لا ينتجه أي نمط عرض في الـ pivot

الإجماليات الجارية ونسبها ومقارنات السابق أو التالي تتبع الترتيب الذي تظهر به الأعضاء فعلاً، لا ترتيب قائمة عناصر الحقل. يصير ذلك التمييز مهماً متى غيّر مستدعٍ الترتيب بعد بناء المخزن

// صفوف مرتبة تنازلياً: الإجمالي الجاري يتراكم بترتيب العرض
Pivot.Fields[0].SortType := xlpfstDescending;

Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff;   // % فرق عن...
Measure.BaseField := 0;                    // ...حقل Category
Measure.BaseItem := xlPivotBaseItemPrevious; // ...العضو السابق المرئي

if Book.MaterializeNativePivotTable(Pivot) <> 1 then
  raise Exception.Create('Pivot display mode was rejected');

بعد تغيّر SortType إلى xlpfstDescending، يصبح "السابق" هو العضو المعروض أعلاه، ويبدأ الإجمالي الجاري من قمة القائمة المرتبة. تنفيذ يمشي في قائمة العناصر المخزنة بدلاً من ذلك ينتج أرقاماً تبدو معقولة وتخالف Excel لحظة حدّث أحدٌ شيئاً. والأعضاء الطرفية تحتاج عناية أيضاً: عند العضو الأول لا يوجد جار سابق، ولا يقارن نمط النسب العضوَ الطرفي بنفسه إلا هو؛ أما أنماط الفروق فتترك تلك الخلية لسلوك Excel الطرفي الخاص بدلاً من ابتداع صفر

لماذا يمكن لنسبة تراكمية أن تتجاوز 100 بالمئة؟

النسبة المئوية للإجمالي الجاري فوق المتوسطات يمكن أن تتجاوز 100 بالمئة مشروعاً، لأن Excel يراكم تجميعات الأعضاء المعروضة ويقسم على تجميع ليس مجموعها. للجمع، الأمور واحدتان متطابقتان، فيهبط العضو الأخير على 100 بالمئة بالضبط. وللمتوسطات، جمع ثلاثة متوسطات فئات والقسمة على المتوسط الإجمالي ليس كسراً من أي شيء، ويمكن للصف الأخير أن يهبط فوق 100 بالمئة بوضوح. يطابق HotXLS Excel هنا عمداً بدلاً من قصر القيمة، لأن رقماً مقصوراً سيخالف الملف بعد التحديث

المقام يعتمد أيضاً على موضع حقل الأساس. عندما يكون حقل الأساس هو الحقل الأعم داخلياً على محوره، يكون المقام هو التجميع الأصلي لموضع المحور ذاك. وعندما يكون حقل أساس خارجياً في تخطيط متداخل، يكون المقام هو الجمع عبر الأعضاء المطابقة من المحور المتبقي. فُحص الموضعان بشكل مستقل مقابل مخرجات Excel، لأن قاعدة تعمل على مستوى تداخل واحد وتفشل بصمت على الآخر هي خلل الـ pivot الكلاسيكي

النسب المئوية البسيطة تسلك مختلفاً عن التراكمية. نِسب الصف والعمود والإجمالي الكلي تقسم على التجميع الأصلي عند النطاق المعني، بمن في ذلك التجميعات غير الجامعة كالمتوسطات، فنسبة إجمالية على متوسطات هي متوسط الخلية مقسوماً على المتوسط الإجمالي، لا نصيب من مجموع مجمَّع

الفراغات و #NULL! و #DIV/0! في أنماط المقارنة

تميّز مقارنات نسبة-الأساس ثلاث حالات فارغة، وتنتج كل واحدة خلية مختلفة. عندما لا يحوي تقاطع البسط صفوف مصدرية، تعرض الخلية #NULL!. عندما لا يملك عنصر الأساس بيانات عند ذلك التقاطع، تكون الخلية فارغة. وعندما يوجد عنصر الأساس وتجميعه صفر، تعرض الخلية #DIV/0!. الكود الذي يقرأ الشبكة المُجسَّدة ينبغي أن يفحص بـ VarIsEmpty و VarIsError بدلاً من أن يفترض أن كل خلية قيمة تحوي رقماً

النسب المئوية البسيطة أكثر تسامحاً: البسط الغائب يُحسب صفراً هناك. تضيف نِسب الصف الأبوي والعمود الأبوي حالة إضافية، إذ إن pivot بلا حقل على المحور المقابل لا يملك أباً يُقسم عليه، فتكون تلك النتائج فارغة. وتتبع الإجماليات الكلية دلالات كل نمط الخاص أيضاً، وفي ذلك الفراغات على طول المحور الذي يسير فيه مقارنة أو إجمالي جاري؛ صف إجمالي مليء بالفراغات تحت إجمالي جاري نتيجة صحيحة، لا فشل تجسيد

تبديل الأنماط على pivot مستورد

تغيير نمط عرض pivot جاء من مصنف موجود يعمل بالطريقة نفسها، مع علم إضافي واحد. مرّر True بوصفه AAdoptExisting ليتيح لـ MaterializeNativePivotTable أن يتولى الخلايا التي كتبها Excel لذلك الـ pivot أصلاً

Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
  ShowDataAs := xlpsdaNormal;                    // صفّر النمط الأساسي
  ExtendedShowDataAs := xlpesdaPercentOfParentRow; // لا حاجة إلى BaseField
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
  raise Exception.Create('Could not re-materialize the pivot');

عندما يتغير النمط، يزيل HotXLS الخصائص المهجورة showDataAs و pivotShowAs و baseItem من التعريف المُجهَّز، فلا يحمل الملف المحفوظ أبداً نمطاً أساسياً قديماً بجوار نمط موسَّع جديد. خطوة التجهيز نفسها تبقي علمَي compact و outline على حقول البيانات وغير النشطة موافقَين لتخطيط التقرير، وهذا مهم لأن Excel يشتق تسميات البيانات بعد التحديث من تلك الأعلام لا من حقول الصف والعمود وحدهما. وقيم التعدادات غير الصالحة تُرفض أيضاً: صبّ بايت بقيمة 255 في أي من التعدادين يمكن أن يتمدد إشارياً إلى -1 في Delphi، لذا يفحص HotXML كلا الحدَّين الأدنى والأعلى ويعيد -1 دون أن يلمس الشبكة

وإذا كنت تضيف حقولاً محسوبة أو عناصر محسوبة إلى التقرير نفسه، فاقرأ بناء جداول pivot والتحديث بحقول محسوبة في HotXLS أولاً، وللقواعد على مستوى الحقل في XML التي تمنع Excel من إصلاح الملف، انظر صلاحية مخطط حقول pivot في XLSX بـ Delphi

الأسئلة الشائعة

هل يمكن لحقل بيانات في HotXLS استخدام ShowDataAs و ExtendedShowDataAs معاً؟

لا. TXLSPivotDataField.ShowDataAs و ExtendedShowDataAs متنافيان؛ بضبط كليهما يعيد MaterializeNativePivotTable قيمة -1 ويترك الإخراج السابق دون تغيير

لماذا تحول ترتيب الـ pivot إلى مجموع بعد التحديث في Excel؟

على الأرجح أُعيدت كتابة URI الامتداد بحالة أحرف مختلفة. يطابق Excel {E15A36E0-9728-4e99-A89B-3F7291B0FE68} حساساً لحالة الأحرف ويتراجع إلى التجميع العادي بعد RefreshTable عندما لا يتطابق

هل يتخطى ترتيب pivot في HotXLS الأرقام بعد التساوي؟

لا. يتبع HotXLS ترتيب الـ pivot الكثيف لـ Excel، فترتب القيم 30 و30 و20 كـ 1 و1 و2

دعم Show Values As والتجسيد الأصلي لجداول pivot وبقية ميزات XLSX و XLS تأتي ضمن مكوّن جداول HotXLS لـ Delphi لـ Delphi و C++Builder