Funkcje inżynieryjne w Delphi: Konwersja systemów liczbowych, matematyka zespolona

Rodzina funkcji inżynieryjnych w Excelu wygląda na najprostszą część dokumentacji. DEC2BIN zamienia liczbę na ciąg binarny. HEX2DEC zamienia ją z powrotem. IMSUM dodaje dwie liczby zespolone. Każda z nich wygląda jak ćwiczenie z formatowania. Wcale tak nie jest. Pod tymi nazwami kryje się dziesięciobitowe kodowanie w kodzie uzupełnień do dwóch, z którym większość programistów nie miała styczności od zajęć z architektury komputerów, format liczb zespolonych, który opiera się całkowicie na ciągach znaków, oraz operatory bitowe, które po cichu spowodują przepełnienie 64-bitowej liczby całkowitej, jeśli wykonasz przesunięcie przed sprawdzeniem zakresu. Silnik arkusza kalkulacyjnego, który dokładnie odtwarza zachowanie Excela, nie może pominąć żadnego z tych aspektów

Funkcje te dzielą się na trzy grupy, a każda z nich kryje inną pułapkę. Konwersja między systemami liczbowymi dotyczy liczb ujemnych i progów charakterystycznych dla danej bazy. Arytmetyka liczb zespolonych sprowadza się do parsowania i formatowania ciągów znaków. Operacje bitowe wymagają utrzymania się w granicach typu Int64. Ten artykuł omawia każdą grupę tak, jak implementuje je HotXLS, wykorzystując wywołania funkcji w arkuszu, które faktycznie napisałbyś w kodzie

Konwersja systemów liczbowych i dziesięciobitowy kod uzupełnień do dwóch

Kierunek w przód to część, której wszyscy się spodziewają. DEC2BIN(9) zwraca "1001", a opcjonalny drugi argument dopełnia wynik zerami z lewej strony do określonej szerokości. Pułapką są dane wejściowe w postaci liczb ujemnych. Excel nie zapisuje znaku minus. Koduje wartość jako dziesięcioznakowy ciąg znaków w docelowym systemie liczbowym z zastosowaniem kodu uzupełnień do dwóch. Z tego powodu DEC2BIN(-5,10) zwraca "1111111011" zamiast jakiejkolwiek wartości ze znakiem. Argument określający liczbę miejsc jest ignorowany dla wartości ujemnych, ponieważ ich kodowanie i tak ma ustaloną długość dziesięciu znaków

Dziesięć znaków to stały budżet, który określa reprezentowalny zakres dla każdej z baz. W systemie binarnym wartość progowa, po której następuje przejście w obszar liczb ujemnych, to 512, a moduł przepełnienia to 1024, więc ciąg binarny ma znak (jest traktowany jako ujemny) tylko wtedy, gdy ma dokładnie dziesięć znaków długości, a jego wartość wynosi co najmniej 512. Ta sama zasada skaluje się wraz ze zmianą systemu. System ósemkowy używa progu połówkowego równego 2^29 i pełnego modułu 2^30. System szesnastkowy używa 2^39 i 2^40. Czytnik HotXLS stosuje dokładnie tę samą zasadę: akumuluje cyfry, a dopiero gdy ciąg ma szerokość dziesięciu znaków, a skumulowana wartość znajduje się na poziomie progu połówkowego lub wyżej, odejmuje pełny moduł w celu odzyskania wartości ze znakiem. Ciąg dziewięcioznakowy zawsze reprezentuje wartość nieujemną, bez względu na to, jak duża jest ta wartość

Koder działa na zasadzie lustrzanego odbicia. Wartość nieujemna jest konwertowana cyfra po cyfrze i opcjonalnie uzupełniana zerami do żądanej szerokości; jest odrzucana, jeśli przekracza górny, dodatni limit bazy lub jeśli żądana szerokość jest zbyt mała, by ją pomieścić. Wartość ujemna najpierw jest sprowadzana do właściwego przedziału poprzez dodanie pełnego modułu, co zamienia ją na wartość, której reprezentacja w danym systemie zawsze zajmuje dziesięć znaków, a następnie jej cyfry są emitowane wraz z zerami wiodącymi w celu wypełnienia wymaganej szerokości. Pojedyncze, wspólne sprawdzanie zakresu (symetryczne dolne i górne granice dla każdej bazy) to to, co utrzymuje spójność zachowania funkcji DEC2BIN, DEC2OCT i DEC2HEX w przypadku wartości brzegowych

Pozostają konwersje między innymi bazami (cross-base), takie jak HEX2BIN i OCT2HEX, które zmieniają system bez przechodzenia przez dziesiętny w samej nazwie funkcji. W implementacji nie przewidziano odrębnych podprogramów dla każdej uporządkowanej pary baz. Wejściowy ciąg jest parsowany na dziesiętną wartość całkowitą ze znakiem przy użyciu bazy źródłowej, po czym ta dziesiętna wartość jest formatowana w bazę docelową. System dziesiętny stanowi swego rodzaju oś obrotu (pivot). Jeden podprogram parsujący i jeden formatujący połączone ze sobą pokrywają każdą kombinację. Ponieważ obie połówki procesu konwersji używają tej samej dziesięcioznakowej konwencji dla liczb ze znakiem, wartość ujemna przetrwa tę transformację z zachowanym poprawnym znakiem

Liczby zespolone to ciągi znaków, więc cała praca sprowadza się do parsowania

Excel nie posiada typu danych dla liczb zespolonych. Wartość zespolona to ciąg "a+bi", a każda funkcja z rodziny IM przyjmuje te ciągi na wejściu i zwraca jeden na wyjściu. COMPLEX buduje ciąg z części rzeczywistej i urojonej. IMSUM, IMSUB, IMPRODUCT oraz IMDIV parsują swoje argumenty, wykonują operacje arytmetyczne na numerycznych częściach, a wynik ponownie formatują w ciąg znaków. Sama praca na liczbach to wiedza z zakresu wczesnych lat algebry na studiach. Cała trudność polega na niezawodnym przekształceniu tekstu na dwie zmiennoprzecinkowe liczby, i to tutaj wewnętrzny parser zarabia na swoje utrzymanie

W takim parserze dwa szczegóły łatwo zepsuć. Pierwszym z nich jest sama jednostka urojona. Ciąg "i" oznacza jeden razy i, nie zero i nie błąd, więc gdy współczynnik przed przyrostkiem jest pusty lub jest to pojedynczy znak plus, parser musi odczytać go jako wartość 1, a w przypadku pojedynczego minusa jako -1. Pomiń to, a IMSUM("i","i") przestanie być równe 2i. Drugi problem to notacja naukowa wchodząca w kolizję ze znakiem oddzielającym część rzeczywistą i urojoną. Parser znajduje separator skanując w poszukiwaniu znaku plus lub minus, lecz liczba zapisana jako "1.5E-3" zawiera znak minus należący do wykładnika potęgi. Z tego powodu skanowanie w poszukiwaniu znaku odmawia traktowania plusa lub minusa jako separatora w sytuacji, gdy znak występujący bezpośrednio przed nim to e lub E. Bez tego zabezpieczenia część rzeczywista zostałaby rozdarta na pół tuż przy znaku potęgowania, a proces parsowania zakończyłby się błędem nawet w przypadku idealnie poprawnych danych wejściowych

Sam przyrostek jest zachowywany, a nie normalizowany. Excel akceptuje zarówno i, jak i j, a HotXLS zapamiętuje, którego z nich użyto na wejściu, aby sformatowany wynik zawierał ten sam znak. Formatyzacja stosuje znane, standardowe skróty: jednostka urojona przy jedynce drukuje się wyłącznie jako przyrostek, przy minus jeden jako -i, część urojona o wartości równej zerze sprowadza całe wyrażenie do prostej liczby rzeczywistej, a zerowa część rzeczywista pominie poprzedzające ją 0+

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Engineering');
    // Negative input: a ten-bit two's complement, places argument ignored.
    Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
    // Complex multiply on two "a+bi" strings.
    Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
  finally
    Book.Free;
  end;
end;

Transcendentalne funkcje liczb zespolonych, takie jak m.in. IMSQRT, IMEXP, IMLN i IMPOWER, nie działają we współrzędnych prostokątnych. Zamieniają one sparsowaną wartość na postać biegunową, stosują działanie na module i argumencie liczby (kącie) i wykonują zamianę z powrotem. Pierwiastkowanie dzieli kąt na pół i wyciąga pierwiastek z modułu. Potęgowanie mnoży kąt i podnosi do potęgi wartość modułu. Wykonywanie tego w jakikolwiek inny sposób oznaczałoby ponowne wywodzenie każdej z tożsamości we współrzędnych kartezjańskich, co niesie ze sobą więcej kodu przy niższej stabilności numerycznej w okolicach cięć głównej gałęzi logarytmu zespolonego (branch cuts)

Operatory bitowe i przepełnienie, które musisz sprawdzić jako pierwsze

Wersja programu Excel z 2013 roku wprowadziła BITAND, BITOR, BITXOR, BITLSHIFT i BITRSHIFT. Operandy są objęte ograniczeniami: każdy z nich musi być nieujemną liczbą całkowitą nie większą niż 2^48 minus 1, a wszelkie argumenty dziesiętne bądź ujemne oznaczają błąd wartości. Ograniczenie to jest wystarczająco liberalne, by pokryć każdy realistyczny zbiór flag, trzymając się jednocześnie z dala od granicy zakresu gwarantującego bezstratną, dokładną reprezentację za pomocą typu podwójnej precyzji, co ma ogromne znaczenie ze względu na to, iż Excel przekazuje do obliczeń każdy numeryczny argument jako wartość zmiennoprzecinkową

Funkcje przesunięcia wiążą się z jedną zasadą dotyczącą kolejności, która potrafi naprawdę ukąsić. Przesunięcie w lewo może wygenerować wartość znacznie większą od wejściowej, a jeśli najpierw wykonasz shl, a wynik sprawdzisz dopiero później, wartość Int64 uległa już przepełnieniu i taki test jest bezwartościowy. Sprawdzanie musi nastąpić przed przesunięciem. HotXLS porównuje operand z górnym limitem przesuniętym w prawo o daną liczbę miejsc i wykonuje właściwe przesunięcie w lewo tylko wtedy, gdy operand się w nim zmieści. Wielkość przesunięcia przekraczająca 53 bity jest odrzucana od razu, a przesunięcie ujemne po prostu odwraca kierunek, więc BITLSHIFT z wartością ujemną działa jak przesunięcie w prawo. Ta reguła ma o wiele szersze zastosowanie niż tylko dla tej jednej funkcji: gdy istnieje zabezpieczenie przed przepełnieniem (overflow), musi ono zadziałać na danych wejściowych, nigdy na wyniku, który to zabezpieczenie miało chronić

// Bitwise calls evaluate the same way through Calculate.
Sheet.Cells[3, 1].Value := Sheet.Calculate('=BITAND(13,11)');    // 9
Sheet.Cells[4, 1].Value := Sheet.Calculate('=BITLSHIFT(5,2)');   // 20
Sheet.Cells[5, 1].Value := Sheet.Calculate('=BITRSHIFT(40,3)');  // 5

Przyszłe funkcje i prefiks nazwy _xlfn

Operatory bitowe oraz długa lista innych funkcji dodanych po 2007 roku opierają się na konwencji nazewnictwa, która nie ma nic wspólnego z tym, co obliczają, za to wiąże się bezpośrednio z tym, jak przechowuje je Excel. Oryginalny, binarny format arkusza przypisywał każdej wbudowanej funkcji określony, numeryczny slot w niezmiennej tabeli. Funkcje dodane już po "zamrożeniu" tejże tabeli nie posiadają własnego slota. Aby zapisać taką funkcję w pliku i sprawić, by współczesny Excel potrafił ją rozpoznać, zapisuje się ją wraz z prefiksem _xlfn.. Oznacza to, że funkcja BITAND jest w rzeczywistości zapisywana na dysku jako _xlfn.BITAND, mimo iż użytkownik w trakcie zwykłej pracy z programem wpisuje do arkusza wyłącznie polecenie o nazwie BITAND

Haczyk polega na tym, że zasada ta nie jest jednolita. Niektóre z nowszych funkcji otrzymały własne miejsca w tabeli i są zapisywane w oryginalnej postaci, podczas gdy kilka starszych, ukrytych funkcji również zapisuje się bez prefiksu mimo ich wieku. HotXLS utrzymuje jawną białą listę funkcji (whitelist), określającą które nazwy wymagają przedrostka, po czym dodaje go przy zapisie, a pozbywa się go z powrotem przy odczycie (strips). Oznacza to, że z perspektywy dewelopera wprowadzany na ekranie i odczytywany tekst formuły zawsze składa się z gotowych do zrozumienia, naturalnych, przejrzystych nazw ułatwiających późniejszą sprawną pracę przy narzędziach znanych wprost z interfejsu Excela. Ustawiasz =BITLSHIFT(5,2), plik przechowuje _xlfn.BITLSHIFT, a wartość zawsze powraca z powrotem równa 20. Prefiks jest szczegółem pamięci masowej (storage detail), który nigdy nie powinien przeciekać do formuł, z jakimi pracujesz w swoim kodzie

Składając to wszystko razem w arkuszu kalkulacyjnym

Publiczny interfejs API dla tych funkcji jest niewielki. Tworzysz TXLSXWorkbook, dodajesz arkusz (worksheet) i wpisujesz formułę do komórki za pomocą Cells[Row, Col].Formula w celu ponownego jej przeliczenia, albo wyliczasz wyrażenie bezpośrednio z użyciem metody arkusza Calculate, która kompiluje formułę dla tego arkusza i zwraca wartość typu Variant. Powyższe przykłady korzystają z Calculate, ponieważ ta funkcja łatwo demonstruje rezultat pojedynczego inżynieryjnego wywołania bez budowania dodatkowego, otaczającego go kontekstu i stanu w arkuszu. Mimo to należy pamiętać, że te same funkcje działają identycznie wewnątrz rzeczywistych formuł umieszczonych prosto w komórkach podczas normalnego, rutynowego przeliczania całego skoroszytu (recalculate)

Tym, o czym warto pamiętać, są sposoby kodowania, a nie same miejsca wywołań. Ciąg binarny posiada znak (jest ujemny) tylko wtedy, gdy ma dziesięć znaków i przekracza próg połówkowy przewidziany dla danej bazy. Liczba zespolona to w istocie tekst, w którym pusty współczynnik części urojonej jest liczony jako jeden, a używany wewnętrznie parser bez problemu omija literę e oznaczającą wykładnik. Sprawdzenie przesunięcia bitowego w lewo musi mieć miejsce przed rzeczywistym dokonaniem rzeczonego przesunięcia (shift). Zrozum poprawnie te cztery fakty, a rodzina funkcji inżynieryjnych przestanie być źródłem ciągłych niespodzianek wynikających z błędów związanych choćby o przesunięcie równe o cały jeden bit lub ujemny znak

Jeśli planujesz zintegrować własne, domenowe obliczenia matematyczne z tym samym silnikiem, mechanika rejestrowania procedury obsługi i zwracania wartości została szczegółowo omówiona w naszym artykule na temat rozszerzania silnika formuł o własne, niestandardowe funkcje. Kiedy te same formuły muszą z kolei sięgać do innych arkuszy używając ich nazw, a nie adresów komórek, praktyczny przewodnik o zdefiniowanych nazwach i formułach pomiędzy odrębnymi arkuszami w prosty sposób ukazuje nam to, w jaki sposób takie referencje są tam rozwiązywane. Z kolei wszystkie te opisywane funkcje i formuły z omawianej grupy inżynieryjnej, dostępne są standardowo w pakiecie komponentu arkuszy HotXLS dla programistów Delphi i C++Builder, bezpośrednio obok interfejsów API do odczytu, zapisu oraz obsługi obliczeń opisywanych we wcześniejszych artykułach publikowanych na naszym blogu