Teknik Makale

Yeni Excel Fonksiyonları: _xlfn Ön Eki ve BIFF8 Name Çağrısı

Delphi ile üretilmiş bir çalışma kitabı =GAMMA(5) ve =XIRR(B2:B9,A2:A9) taşır ve Excel onu GAMMA hücresinde #NAME?, XIRR hücresinde ise doğru bir sonuçla açar. HotXLS'te XLSX içinde _xlfn. ön ekini taşıyan ya da BIFF8 .xls'te name tabanlı bir çağrıyla saklanan fonksiyonlar, Excel'in de o şekilde kaydettiğidir — MS-XLSX dilbilgisinden sapan bir liste

Excel neden GAMMA için #NAME? gösterir de XIRR için göstermez?

Formula metni doğrudur; saklama yanlıştır. Bir XLSX çalışma sayfasının içinde bir formül, bir <f> ögesinde düz metindir ve Excel 2007'den sonra eklenen bir fonksiyon _xlfn.NAME olarak yazılır. Ön ek okuyucuya "bu, henüz bilmeyebileceğin bir gömülü fonksiyon" der; böylece eski bir Excel çağrıyı kullanıcı tanımlı bir adla karıştırmadan #NAME? gösterir ve güncel Excel görüntüde ön eki sıyırır, kullanıcılar =GAMMA(5) görür. Ön eki bırakırsanız güncel Excel, GAMMA adında bir tanımlı ad arar, bulamaz ve #NAME? gösterir. XIRR daha eskidir. Analysis ToolPak ile geldi, Excel onu çıplak yazar ve önekli bir _xlfn.XIRR, Excel'in ürettiği token'dan farklı bir token'dır. GAMMA, GAUSS, PHI, SHEET, SHEETS ve XOR hepsi ön ek ister; Excel 2013'ün mühendislik ve karmaşık sayı kümesine eklediği sekiz fonksiyon da (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH ve IMTAN) öyle, eski IMSIN, IMSQRT ve IMSUM ise çıplak kalır. Yalnızca çalışma sayfasında geçerli dizi fonksiyonları ikinci bir ön ek taşır: MS-XLSX §2.2.2'nin FILTER, SORT ve PY için ayırdığı _xlfn._xlws.. Okurken HotXLS her iki ön eki de kaldırır; dolayısıyla Cell.Formula temiz fonksiyon adını döndürür

HotXLS Delphi'de yeni Excel fonksiyonlarının saklama rotaları: GAMMA _xlfn ön ekini taşırken ToolPak'ten XIRR çıplak kalır
ön ek, okuyucunun henüz bilmeyebileceği bir gömülüyü işaretler; güncel Excel onu sıyırır, eski sürümler bir ad hatasıyla cevap verir

Excel hangi fonksiyonlara ön ek koyar da MS-XLSX dilbilgisi onları çıplak bırakır?

MS-XLSX §2.2.2 listeyi ABNF olarak şöyle yazar: future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Harfi harfine okursanız Z.TEST'ten sonraki kapanış parantezi önekli grubu bitirir; dolayısıyla ondan sonraki her şey çıplak bir addır. İkinci bir üretim kuralı, sıradan gömülü fonksiyon listesi, SUM ve VLOOKUP'un yanında CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS ve SWITCH'i içerir. Excel iki konuda da katılmaz:

FonksiyonMS-XLSX dilbilgisinin ona verdiği yerExcel'in yazdığı
NETWORKDAYS.INTL, WORKDAY.INTL_xlfn. grubundan sonra, yani çıplak_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILING_xlfn. grubundan sonra, yani çıplak_xlfn.ISO.CEILING
FORECAST.LINEAR ve FORECAST.ETS ailesi_xlfn. grubundan sonra, yani çıplakÖnekli, örneğin _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHSıradan gömülü fonksiyon listesiÖnekli, örneğin _xlfn.TEXTJOIN
XIRR, XNPV, ERFCfuture-function listesinde değilÇıplak

Dilbilgisinden üretilmiş bir yazıcı bu yüzden NETWORKDAYS.INTL(…)'i ön eksiz üretir ve Excel, yayımlanmış sözdizimine göre doğrulanan bir dosyada #NAME? ile cevap verir. Listeyi kaydedilmiş Excel dosyalarından alın ve ABNF'yi yalnızca bir başlangıç noktası sayın. HotXLS hesapladığı her fonksiyon için bunu yapar; önekli CONCAT da önekli NETWORKDAYS.INTL de Excel'in yazdığı gibi çıkar. Aşağıdaki kod aynı türden formülleri iki biçimde kaydeder ve birini geri okur:

uses
  SysUtils, lxCalc, lxHandle, lxHandleX;

procedure SaveModernFunctions;
var
  XBook, XCheck: TXLSXWorkbook;
  XSheet: TXLSXWorksheet;
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  // XLSX: ön ekler kaydetmede eklenir, yüklemede sıyırılır
  XBook := TXLSXWorkbook.Create;
  try
    XSheet := XBook.Sheets.Add('Calc');
    XSheet.Cells[1, 1].Value := 46000.0;                // bir tarih serial'i
    XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)';       // ToolPak: ön ek yok
    XSheet.Cells[1, 3].Formula := 'GAMMA(5)';          // _xlfn.GAMMA
    XSheet.Cells[1, 4].Formula := 'NETWORKDAYS.INTL(A1,A1+30,11)';
    XSheet.Cells[1, 5].Formula := 'TEXTJOIN("-",TRUE,B1:D1)';
    if XBook.SaveAs('modern.xlsx', xlsxOpenXMLWorkbook) <> lxOk then
      raise Exception.Create('XLSX save failed');
  finally
    XBook.Free;
  end;

  XCheck := TXLSXWorkbook.Create;
  try
    if XCheck.Open('modern.xlsx') <> lxOk then
      raise Exception.Create('XLSX open failed');
    // Beklenen: NETWORKDAYS.INTL(A1,A1+30,11), ön ek zaten kaldırılmış
    Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
  finally
    XCheck.Free;
  end;

  // BIFF8: aynı çağrılar name token'ları ve Ftab 255 girdisine dönüşür
  Book := TXLSWorkbook.Create;
  try
    Sheet := Book.Sheets.Add;
    Sheet.Cells[1, 1].Value := 46000.0;
    Sheet.Cells[1, 2].Formula := '=EDATE(A1,1)';       // add-in EXTERNNAME
    Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)';    // gizli _xlfn NAME
    if Book.SaveAs('modern.xls') <> lxOk then          // varsayılan olarak BIFF8
      raise Exception.Create('XLS save failed');
  finally
    Book.Free;
  end;
end;

Her iki dosya da güncel Excel'de tüm fonksiyonlar yerinde açılmalı. Ön ekleri görmek için paketi bir ZIP olarak açın ve çalışma sayfası parçasının ham <f> metnini listeleyin; modern.xlsx için aşağıdaki rutin EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) ve _xlfn.TEXTJOIN(&quot;-&quot;,TRUE,B1:D1) basmalı. Tırnaklar &quot; çıkar, çünkü rutin XML metnini escape'siz basar; bu bir teşhis için zararsızdır. Herhangi bir üreticinin dosyalarında çalışır; dolayısıyla üçüncü taraf bir çalışma kitabı, sizin Excel kopyanızın desteklediği bir fonksiyonda #NAME? gösterdiğinde en hızlı kontrol budur

uses
  SysUtils, Classes, Zip;

// Bir çalışma sayfası parçasının her <f> ögesini ön eklerle birlikte bas
procedure DumpRawFormulas(const XlsxFile: string;
  const Part: string = 'xl/worksheets/sheet1.xml');
var
  Zip: TZipFile;
  Bytes: TBytes;
  Xml: string;
  P, StartTag, Close: Integer;
begin
  Zip := TZipFile.Create;
  try
    Zip.Open(XlsxFile, zmRead);
    Zip.Read(Part, Bytes);
  finally
    Zip.Free;
  end;
  Xml := TEncoding.UTF8.GetString(Bytes);
  P := 1;
  while True do
  begin
    StartTag := Pos('<f', Xml, P);
    if StartTag = 0 then
      Break;
    P := StartTag + 2;
    if not CharInSet(Xml[P], ['>', ' ', '/']) then
      Continue;                                // <formula>, <font>: hücre formülü değil
    P := Pos('>', Xml, P) + 1;               // t="shared" gibi nitelikleri atla
    if Xml[P - 2] = '/' then
      Continue;                                // paylaşımlı bir formülün boş <f .../>'su
    Close := Pos('</f>', Xml, P);
    if Close = 0 then
      Break;
    Writeln(Copy(Xml, P, Close - P));          // XML-escape'li: &quot; &amp; &lt;
    P := Close + 4;
  end;
end;

Bir BIFF8 .xls dosyası numarası olmayan bir fonksiyonu nasıl çağırır?

BIFF8 formülleri hiçbir fonksiyon adı içermez. Her gömülü fonksiyon, sabit bir fonksiyon tablosundaki (Ftab) bir sayıdır ve bir formül, o sayıyla ptgFunc veya ptgFuncVar saklar. Daha yeni fonksiyonların numarası yoktur. MS-XLS o çağrıları 0x00FF Ftab girdisi, yani "User Defined Function" üzerinden yönlendirir: ilk argümanı gerçek fonksiyona işaret eden bir name token'ı olan, fonksiyon indeksi 255'lik bir ptgFuncVar. Token'da saklanan argüman sayısı kullanıcının yazdığından bir fazladır, çünkü baştaki o ad bir argüman sayılır. Excel hangi name token'ını yazacağına fonksiyonun ailesine göre karar verir:

Fonksiyon ailesiName token'ıAdın gösterdiği şey
Analysis ToolPak fonksiyonları: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameXÇalışma kitabının add-in SUPBOOK'undaki bir EXTERNNAME kaydı
Excel'in _xlfn. ile yazdığı fonksiyonlar: XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgNameTanımı #NAME? olan _xlfn.XLOOKUP (veya _xlfn._xlws.FILTER) adında gizli bir NAME kaydı
Kaydeden Excel kopyasının tanımadığı fonksiyonlarptgNameBoş tanımlı sıradan bir çalışma kitabı NAME kaydı

İlk satırdaki add-in SUPBOOK, bir okuyucunun dış referansları sıralarken karşılaştığı kaydın kendisidir ve BIFF SUPBOOK kayıtlarının dış referansları sınıflandırması, onun öbür türler arasında nerede oturduğunu gösterir. Add-in'e benzeyen her şey bu yoldan gitmez. DATEDIF, USDOLLAR, FINDB ve ISPMT başta olmak üzere bir kısmının yerli Ftab numarası vardır ve düz fonksiyon çağrıları olarak saklanır. Üçüncü satır kırılgan olandır: dosyayı kaydeden Excel'den çok daha yeni bir fonksiyon — örneğin REGEXTEST ve REGEXEXTRACT — tanımsız sıradan bir ad üzerinden çağrıya dönüşür. Boş tanımlı adları çöp diye atan bir okuyucu, formülü artık var olmayan bir ad indeksine işaretçi bırakır ve Excel, yazdığı bütün dosyayı açmayı reddeder. HotXLS böyle bir adı #NAME? tanımıyla tutar ve kaydetmede özgün kaydı yeniden yazar

HotXLS Delphi'de BIFF8 name çağrısı rotaları: ToolPak fonksiyonları ptgNameX'e binerken _xlfn fonksiyonları gizli bir name kaydı ve 255 fonksiyonu üzerinden çağrılır
.xls dosyasındaki bir formül sayılar saklar; yeni bir fonksiyon kullanıcı tanımlı yuvayı ödünç alır ve gidiş dönüşte ayakta kalması gereken bir name kaydına işaret eder

Her ad, ilk çalışma sayfasından önce var olmak zorundadır

Sıralama bir sonraki kısıttır. Bir BIFF8 dosyası global bölümünü — NAME tablosu ve EXTERNSHEET'i — herhangi bir çalışma sayfası alt akışından önce yazar; dolayısıyla bir formülün işaret edeceği her ad, ilk hücre yazılmadan önce biliniyor olmalıdır. Üçüncü sayfanın ortasında XLOOKUP ile karşılaşan bir yazıcı, ihtiyaç duyduğu gizli adı artık ekleyemez. BIFF8'i siz üretiyorsanız global bölümü yazmadan önce her formül kaynağını tarayın: hücre formülleri, çalışma kitabı ve sayfa düzeyindeki adlar, veri doğrulama kuralları ve koşullu biçimlendirme. Boyutlar token'lardan çıkar. Bir name çağrısı argümanların üstüne 9 bayt gelir (5'i ptgName, 4'ü ptgFuncVar), add-in çağrısı 11 (7'si ptgNameX, 4'ü ptgFuncVar); yazmadan önce hesaplanan kayıt uzunlukları, sonradan yazılan baytlarla aynı token seçimini kullanmak zorundadır. HotXLS bu adların hepsini hiçbir şey yazmadan tek geçişte çözer

HotXLS Delphi'de bir BIFF8 dosyasının yazma sırası: global bölümdeki name kayıtları her çalışma sayfasından önce gelir, dolayısıyla formül kaynakları önce taranır
üçüncü sayfanın ortasında karşılaşılan bir ad global bölüme katılamaz; dolayısıyla ilk bayt yazılmadan önce her formül kaynağı taranır

İşlenen sınıfları Excel'in hesapladığını değiştirir

BIFF8 formülündeki her argüman bir referans, değer veya dizi işleneni olarak etiketlenir ve Excel etikete kelimesi kelimesine uyar. AGGREGATE'in üçüncü argümanını değer olarak etiketlerseniz Excel #VALUE! döndürür. SUM(SORTBY(…))'in iç sonucunu değer olarak etiketlerseniz Excel örtülü bir kesişim ekler, SUM(@SORTBY(…)) gösterir ve tek bir hücreyi toplar. Bir yazıcı bu argümanları Excel'in yaptığı gibi etiketlemek zorundadır; bu da her zaman fonksiyonun belgelendirmesinin ima ettiği sınıf değildir: add-in fonksiyon argümanları referans olarak yazılır, dinamik dizi fonksiyonları dizi döndürür ve XLOOKUP, TAKE, DROP, IFS ve SWITCH, bir referansın beklendiği yerde oturduklarında referans döndürür. Yanlış sınıflar dosyayı bozmaz. Cevabı değiştirir

VBA fonksiyonları çalışma kitabını XLSB ikili biçiminde tutar

XLSB üçüncü saklama durumunu ekler. Bir makro çalışma kitabı kendi VBA Function prosedürlerini hücrelerden çağırabilir ve ikili biçim her birini, formülü boş, prosedür olarak işaretlenmiş bir ad olarak beyan eder; hücre sonra onu, BIFF8'deki ptgName artı 255 fonksiyonu kalıbının aynısıyla çağırır. XML biçimlerinin eşdeğeri yoktur. Boş tanımlı bir ad orada geçerli değildir ve #NAME? olarak tanımlı bir ad, aynı adlı VBA fonksiyonunu gizlerdi. HotXLS böyle bir çalışma kitabını beyanlar yerinde olarak XLSB'ye geri kaydeder ve tek bir bayt yazmadan önce başka hiçbir biçime dönüştürmeyi reddeder. Dönüştürmeden önce dönüştürme raporunu isteyin, kaydetme başarısız olduktan sonra değil: TXLSXWorkbook.GetConversionReport, bu çalışma kitapları için desteklenmeyen olarak işaretlenmiş bir VBAFunctionNames girdisi döndürür ve kod bunun üzerinden dallanabilir

uses
  SysUtils, lxCalc, lxDiagnostics, lxHandleX;

procedure ConvertOrKeepBinary(const XlsbFile: string);
var
  Book: TXLSXWorkbook;
  Report: TXLSConversionReport;
  I: Integer;
  Blocked: Boolean;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(XlsbFile) <> lxOk then
      raise Exception.Create('Open failed');
    Report := Book.GetConversionReport(xlsConversionOpenXmlWorkbook);
    try
      Blocked := False;
      for I := 0 to Report.Count - 1 do
        if Report[I].Fidelity = xlsConversionUnsupported then
        begin
          Blocked := True;
          Writeln(string(Report[I].Feature), ': ', string(Report[I].Message));
        end;
    finally
      Report.Free;
    end;
    if Blocked then                               // örn. VBAFunctionNames
      Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
        xlsxExcelBinaryWorkbook)
    else
      Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
  finally
    Book.Free;
  end;
end;

Başka bir aracın çalışma kitabı Excel'in açıkça bildiği bir fonksiyonda #NAME? gösterdiğinde önce her şeyi bırakın DumpRawFormulas çalıştırın: <f> metnindeki çıplak bir NETWORKDAYS.INTL veya TEXTJOIN, teşhisin tamamıdır. Aynı formül metnini HotXLS Delphi Excel component ile Cell.Formula üzerinden atayın; XLSX Excel'in beklediği ön eki alır, çalışma kitabının .xls kaydı ise gizli adı ve 255 fonksiyonlu çağrıyı alır