Funkcje inżynieryjne w Delphi: Konwersja bazowa i liczby złożone

Rodzina funkcji inżynieryjnych w Excelu wygląda na najprostszą część spisu funkcji. DEC2BIN zamienia liczbę na ciąg binarny. HEX2DEC wykonuje operację odwrotną. IMSUM sumuje dwie liczby zespolone. Każda z nich wygląda jak zwykłe ćwiczenie z formatowania tekstu. Tak jednak nie jest. Za tymi nazwami kryje się 10-bitowe kodowanie uzupełnień do dwójki (two's complement), z którym większość programistów nie miała do czynienia od czasów zajęć z architektury komputerów, format liczb zespolonych zapisany w całości w postaci ciągów znaków oraz operatory bitowe, które po cichu przepełnią 64-bitową liczbę całkowitą, jeśli wykonasz przesunięcie przed sprawdzeniem warunków. Silnik arkusza kalkulacyjnego, który ma dokładnie odwzorowywać Excela, nie może pominąć żadnego z tych szczegółów.

Funkcje te dzielą się na trzy grupy, a każda z nich kryje inną pułapkę. Konwersja bazowa wiąże się z liczbami ujemnymi i progami dla poszczególnych systemów liczbowych. Arytmetyka liczb zespolonych opiera się na parsowaniu i formatowaniu ciągów znaków. Operacje bitowe wymagają pozostania w granicach typu Int64. W tym artykule omówiono każdą z tych grup w postaci zaimplementowanej w HotXLS wraz z wywołaniami arkusza, które faktycznie można zapisać.

Konwersja bazowa i 10-bitowe uzupełnienie do dwójki

Konwersja w przód to część, której wszyscy się spodziewają. DEC2BIN(9) zwraca wartość "1001", a opcjonalny drugi argument uzupełnia wynik wiodącymi zerami do określonej szerokości. Pułapką są wartości ujemne. Excel nie zapisuje znaku minus. Koduje wartość jako dziesięciocyfrowy ciąg uzupełnienia do dwójki w systemie docelowym, przez co wywołanie DEC2BIN(-5,10) zwraca "1111111011", a nie postać ze znakiem. Argument określający pozycje (miejsca) jest ignorowany, gdy wartość jest ujemna, ponieważ kodowanie jest na stałe przypisane do dziesięciu cyfr.

Dziesięć cyfr to stały budżet, a ten budżet określa zakres reprezentowalny dla każdego systemu liczbowego. W systemie dwójkowym wartość przechodząca w połówkę ujemną to 512, a moduł owijania wynosi 1024, stąd ciąg binarny jest traktowany jako ujemny tylko wtedy, gdy ma dokładnie dziesięć znaków i jego wartość wynosi co najmniej 512. Ta sama zasada skaluje się dla innych systemów. System ósemkowy używa progu połówkowego równego 2^29 i pełnego modułu 2^30. System szesnastkowy korzysta z 2^39 i 2^40. Czytnik HotXLS stosuje dokładnie tę regułę: gromadzi cyfry i tylko wtedy, gdy ciąg ma długość dziesięciu znaków, a skumulowana wartość leży na poziomie progu połówkowego lub powyżej niego, odejmuje pełny moduł w celu odzyskania wartości ze znakiem. Ciąg o długości dziewięciu znaków jest zawsze nieujemny, bez względu na wielkość.

Koder stanowi lustrzane odbicie tej reguły. Wartość nieujemna jest konwertowana cyfra po cyfrze i opcjonalnie uzupełniana zerami do żądanej szerokości, a jej wygenerowanie jest odrzucane, jeśli przekracza dodatni sufit systemu lub gdy żądana szerokość jest zbyt mała, by ją pomieścić. Wartość ujemna jest najpierw sprowadzana do właściwego zakresu poprzez dodanie pełnego modułu, co zamienia ją w wartość, której reprezentacja bazowa ma zawsze dziesięć cyfr, a następnie cyfry te są wysyłane wraz z wiodącymi zerami w celu wypełnienia szerokości. Pojedyncze, współdzielone sprawdzenie zakresu — symetryczne dolne i górne granice dla każdego systemu — zapewnia spójność zachowania funkcji DEC2BIN, DEC2OCT i DEC2HEX na ich krańcach.

That leaves the cross-base conversions, the ones such as HEX2BIN and OCT2HEX that change base without passing through decimal in the function name. The implementation does not carry a separate routine for every ordered pair. It parses the input string into a signed decimal value using the source base, then formats that decimal value into the destination base. Decimal is the pivot. One parse routine and one format routine, composed, cover every combination, and because both halves share the same ten-digit signed convention, a negative value survives the trip with its sign intact.

Liczby zespolone to ciągi znaków, więc zadanie polega na parsowaniu

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 i zwraca jeden z nich na wyjściu. Funkcja COMPLEX buduje ciąg z części rzeczywistej i urojonej. Funkcje IMSUM, IMSUB, IMPRODUCT i IMDIV parsują argumenty, wykonują działania na częściach liczbowych i formatują wynik z powrotem do ciągu znaków. Matematyczna część to podstawowa algebra. Trudność tkwi w niezawodnym zamienieniu tekstu na dwie liczby zmiennoprzecinkowe i to właśnie w tym miejscu wewnętrzny parser okazuje się niezastąpiony.

Dwa szczegóły w tym parserze są łatwe do pominięcia. Pierwszym z nich jest sama jednostka urojona. Ciąg "i" oznacza jeden razy i, a nie zero czy błąd, więc gdy współczynnik przed przyrostkiem jest pusty lub stanowi samotny znak plus, parser musi odczytać go jako wartość 1, a samotny minus jako -1. Bez tego wywołanie IMSUM("i","i") przestaje zwracać 2i. Drugi szczegół to kolizja notacji naukowej ze znakiem oddzielającym część rzeczywistą od urojonej. Parser szuka tego separatora, skanując w poszukiwaniu znaku plus lub minus, jednak liczba zapisana jako "1.5E-3" zawiera minus należący do wykładnika. Proces skanowania odmawia zatem traktowania znaku plus lub minus jako separatora, gdy znak bezpośrednio przed nim to e lub E. Bez tego zabezpieczenia część rzeczywista zostałaby rozdzielona w miejscu znaku wykładnika, a parsowanie zakończyłoby się błędem dla całkowicie poprawnych danych.

Sam przyrostek jest zachowywany, a nie normalizowany. Excel akceptuje zarówno i, jak i j, a HotXLS pamięta, która litera została użyta na wejściu, dzięki czemu sformatowany wynik zawiera tę samą literę. Formatowanie stosuje następnie typowe skróty: część urojona równa jeden jest wypisywana jako sam przyrostek, minus jeden jako -i, zerowa część urojona redukuje się do samej części rzeczywistej, a zerowa część rzeczywista pomija wiodący wpis 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 zespolone, w tym IMSQRT, IMEXP, IMLN i IMPOWER, nie działają w układzie współrzędnych prostokątnych. Konwertują one sparsowaną wartość do postaci biegunowej, wykonują operację na module i argumencie, a następnie konwertują ją z powrotem. Pierwiastek kwadratowy dzieli argument na pół i wyznacza pierwiastek z modułu. Potęgowanie mnoży argument i podnosi moduł do potęgi. Dowolne inne podejście wymagałoby ponownego wyprowadzania każdej tożsamości w postaci prostokątnej, co oznacza więcej kodu i mniejszą stabilność numeryczną w pobliżu cięć rozgałęzień (branch cuts).

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

W programie Excel 2013 dodano funkcje BITAND, BITOR, BITXOR, BITLSHIFT i BITRSHIFT. Operandy mają ograniczenia: każdy musi być liczbą całkowitą nieujemną, nie większą niż 2^48 minus 1, a każdy argument ułamkowy lub ujemny powoduje błąd liczbowy. Ten limit jest na tyle duży, że pozwala obsłużyć każdy rzeczywisty zestaw flag, pozostając jednocześnie w zakresie dokładnie reprezentowalnym przez typ double, co jest istotne, ponieważ Excel przekazuje każdy argument liczbowy jako wartość zmiennoprzecinkową.

Funkcje przesunięcia niosą ze sobą jedną regułę kolejności, która może sprawić problemy. Przesunięcie w lewo może dać wartość znacznie większą niż wejściowa i jeśli wykonasz najpierw operację shl, a wynik sprawdzisz dopiero potem, doprowadzisz do przepełnienia typu Int64 i test straci sens. Sprawdzenie musi nastąpić przed przesunięciem. HotXLS porównuje operand z limitem górnym przesuniętym w prawo o wielkość przesunięcia i wykonuje rzeczywiste przesunięcie w lewo tylko wtedy, gdy operand się mieści. Przesunięcie o wielkość przekraczającą 53 bity jest odrzucane, a ujemna wartość przesunięcia po prostu odwraca kierunek, więc BITLSHIFT z ujemnym licznikiem zachowuje się jak przesunięcie w prawo. Ta zasada ma zastosowanie znacznie szerzej niż w tej jednej funkcji: jeśli istnieje zabezpieczenie przed przepełnieniem, musi ono analizować dane wejściowe, a nie wynik, przed którego uszkodzeniem 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 i długa lista innych funkcji dodanych po wersji 2007 współdziałają ze schematem nazewnictwa, który nie ma nic wspólnego z ich obliczeniami, a wiąże się ze sposobem ich zapisywania przez Excel. Oryginalny format binarny arkusza kalkulacyjnego przypisywał każdej wbudowanej funkcji numeryczną pozycję w stałej tabeli. Funkcje dodane po zamrożeniu tej tabeli nie mają takiej pozycji. Aby zapisać taką funkcję w pliku i sprawić, by współczesny Excel ją rozpoznał, nazwa jest zapisywana z prefiksem _xlfn., więc BITAND jest przechowywany na dysku jako _xlfn.BITAND, choć użytkownik wpisuje po prostu BITAND.

Haczyk polega na tym, że reguła ta nie jest jednolita. Niektóre nowsze funkcje otrzymały pozycje w tabeli i są zapisywane bez prefiksu, podczas gdy kilka starszych, ukrytych funkcji jest również zapisywanych bez niego mimo swojego wieku. HotXLS utrzymuje jawną białą listę nazw wymagających prefiksu, dodając go przy zapisie i usuwając przy odczycie, dzięki czemu tekst formuły, który ustawiasz i odczytujesz, to zawsze czysta nazwa widoczna w Excelu. Ustawiasz =BITLSHIFT(5,2), plik przechowuje _xlfn.BITLSHIFT, a zwracana wartość to i tak 20. Prefiks to szczegół zapisu, który nigdy nie powinien przedostawać się do formuł, na których pracujesz w kodzie.

Łączenie wszystkiego w arkuszu kalkulacyjnym

Publiczny obszar dla tych funkcji jest niewielki. Tworzysz TXLSXWorkbook, dodajesz arkusz i albo zapisujesz formułę w komórce poprzez Cells[Row, Col].Formula i przeliczasz arkusz, albo wyznaczasz wartość wyrażenia bezpośrednio za pomocą metody Calculate arkusza, która kompiluje formułę dla tego arkusza i zwraca wartość typu Variant. W powyższych przykładach użyto metody Calculate, ponieważ pokazuje ona wynik pojedynczego wywołania funkcji inżynieryjnej bez narzutu związanego ze stanem arkusza, jednak te same funkcje są obliczane identycznie wewnątrz rzeczywistych formuł komórek przy przeliczaniu skoroszytu.

Tym, o czym należy pamiętać, są sposoby kodowania, a nie same miejsca wywołań. Binarny ciąg znaków reprezentuje wartość ze znakiem tylko przy długości dziesięciu cyfr i tylko po przekroczeniu progu połówkowego dla swojego systemu. Liczba zespolona to tekst, pusty współczynnik urojony oznacza jeden, a parser omija literę e w wykładniku. Przesunięcie w lewo jest weryfikowane przed wykonaniem operacji. Pamiętanie o tych czterech faktach sprawi, że rodzina funkcji inżynieryjnych przestanie być źródłem niespodzianek ze złym znakiem wartości.

Jeśli włączasz własne obliczenia dziedzinowe do tego samego silnika, mechanizm rejestrowania procedury obsługi i zwracania wartości opisano w naszym artykule na temat rozszerzania silnika formuł o własne funkcje. Sposób odwoływania się formuł do innych arkuszy po nazwie przedstawia przewodnik po nazwach zdefiniowanych i formułach międzyarkuszowych. Funkcje inżynieryjne opisane tutaj wchodzą w skład komponentu arkusza kalkulacyjnego HotXLS dla Delphi i C++Builder wraz z interfejsami API do odczytu, zapisu i obliczeń omówionymi w innych częściach tego bloga.