A família de engenharia no Excel parece ser o canto mais fácil da referência de funções. DEC2BIN transforma um número numa string binária. HEX2DEC volta a transformá-lo. IMSUM adiciona dois números complexos. Cada um deles parece um exercício de formatação. Não o são. Por trás destes nomes esconde-se uma codificação em complemento para dois de dez bits que a maioria dos programadores não toca desde uma aula de arquitetura de computadores, um formato de número complexo que vive inteiramente dentro de strings e operadores bit a bit que vão transbordar (overflow) silenciosamente um inteiro de 64 bits se deslocar (shift) antes de verificar. Um motor de folha de cálculo que reproduza o Excel com exatidão não pode arredondar nada disto
As funções dividem-se em três grupos e cada grupo esconde uma armadilha diferente. A conversão de base diz respeito a números negativos e limiares por base. A aritmética complexa prende-se com a análise (parsing) e formatação de uma string. As operações bit a bit consistem em permanecer dentro dos limites de Int64. Este artigo percorre cada grupo tal como o HotXLS o implementa, com as chamadas de folha de cálculo que escreveria na realidade
Conversão de base e o complemento para dois de dez bits
A direção de avanço é a parte que todos esperam. DEC2BIN(9) dá "1001", e um segundo argumento opcional preenche o resultado à esquerda com zeros para uma largura fixa. A armadilha é o input negativo. O Excel não escreve um sinal de menos. Codifica o valor como uma string em complemento para dois de dez dígitos na base de destino, que é o motivo pelo qual DEC2BIN(-5,10) devolve "1111111011" em vez de qualquer coisa com um sinal. O argumento de casas decimais (places) é ignorado quando o valor é negativo, porque a codificação já está fixada em dez dígitos
Dez dígitos é um orçamento fixo, e esse orçamento define o intervalo representável por base. Em binário a magnitude que passa para a metade negativa é 512 e o módulo de retorno (wrap modulus) é 1024, pelo que uma string binária só tem sinal quando tem exatamente dez carateres de comprimento e o seu valor é de, pelo menos, 512. A mesma ideia escala com a base. Octal usa um limiar intermédio (half threshold) de 2^29 e um módulo total (full modulus) de 2^30. Hexadecimal usa 2^39 e 2^40. O leitor HotXLS aplica exatamente esta regra: acumula os dígitos e só quando a string tem dez carateres de largura e o valor acumulado se situa no limiar intermédio ou acima dele é que subtrai o módulo total para recuperar o valor com sinal. Uma string de nove carateres é sempre não negativa, por maior que seja
O codificador é a imagem espelhada. Um valor não negativo é convertido dígito a dígito e opcionalmente preenchido com zeros até à largura pretendida, e é rejeitado se exceder o teto positivo da base ou se a largura pretendida for demasiado estreita para o conter. Um valor negativo é primeiro trazido para o intervalo através da adição do módulo total, o que o transforma num valor cuja representação de base tem sempre dez dígitos, e depois os dígitos são emitidos com zeros à esquerda para preencher a largura. A única verificação de limite partilhada, os limites inferior e superior simétricos por base, é o que mantém DEC2BIN, DEC2OCT e DEC2HEX consistentes uns com os outros nas suas extremidades
Isso deixa as conversões entre bases, as que são como HEX2BIN e OCT2HEX que mudam de base sem passarem por decimal no nome da função. A implementação não transporta uma rotina separada para cada par ordenado. Analisa a string de entrada para um valor decimal com sinal usando a base de origem e, de seguida, formata esse valor decimal para a base de destino. O decimal é o pivô. Uma rotina de análise e uma rotina de formato, juntas, cobrem todas as combinações e, como ambas as metades partilham a mesma convenção com sinal de dez dígitos, um valor negativo sobrevive à viagem com o seu sinal intacto
Os números complexos são strings, por isso o trabalho é fazer o parsing
O Excel não tem um tipo de dados complexo. Um valor complexo é a string "a+bi", e todas as funções na família IM recebem essas strings e devolvem uma. COMPLEX constrói a string a partir de uma parte real e uma parte imaginária. IMSUM, IMSUB, IMPRODUCT e IMDIV analisam os seus argumentos, efetuam a aritmética sobre as partes numéricas e formatam o resultado de volta para uma string. O trabalho numérico é álgebra básica (undergraduate algebra). A dificuldade reside inteiramente em transformar o texto em dois números de vírgula flutuante (floating-point numbers) de forma fiável, e é aí que o parser (analisador) interno prova o seu valor
Dois pormenores nesse parser são fáceis de errar. O primeiro é a unidade imaginária simples. A string "i" significa um vezes i, não zero e não um erro, pelo que quando o coeficiente à frente do sufixo está vazio ou é um sinal de mais isolado o parser tem de o ler como o valor 1, e um menos isolado como -1. Salte isso e IMSUM("i","i") deixa de ser 2i. O segundo é a colisão da notação científica com o sinal que separa as partes real e imaginária. O parser encontra esse separador através de uma procura por um sinal de mais ou menos, mas um número escrito como "1.5E-3" contém um sinal de menos que pertence ao expoente. A procura recusa, assim, tratar um sinal de mais ou menos como o separador quando o carácter imediatamente anterior for e ou E. Sem essa salvaguarda a parte real seria rasgada a meio no sinal do expoente e a análise falharia num input perfeitamente válido
O próprio sufixo é preservado em vez de ser normalizado. O Excel aceita tanto i como j, e o HotXLS lembra-se de qual deles foi usado na entrada para que o resultado formatado possua a mesma letra. A formatação aplica, em seguida, as convenções abreviadas (shorthands) habituais: uma parte imaginária de um imprime-se apenas como o sufixo, menos um como -i, uma parte imaginária de zero colapsa para um simples real e uma parte real de zero descarta o 0+ à esquerda
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Engineering');
// Input negativo: complemento para dois de dez bits, argumento places ignorado.
Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
// Multiplicação complexa em duas strings "a+bi".
Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
finally
Book.Free;
end;
end;As funções complexas transcendentes, IMSQRT, IMEXP, IMLN e IMPOWER entre elas, não trabalham em coordenadas retangulares. Elas convertem o valor analisado para a forma polar, aplicam a operação no módulo e argumento e voltam a converter. Uma raiz quadrada reduz o argumento a metade e calcula a raiz do módulo. Uma potência multiplica o argumento e eleva o módulo. Fazer de qualquer outra maneira significaria voltar a deduzir (re-deriving) cada identidade na forma retangular, o que acarreta simultaneamente mais código e menos estabilidade numérica nas imediações dos cortes de ramificação (branch cuts)
Operadores bit a bit e o overflow que tem de verificar primeiro
O Excel 2013 adicionou BITAND, BITOR, BITXOR, BITLSHIFT e BITRSHIFT. Os operandos estão condicionados: cada um tem de ser um número inteiro não negativo não superior a 2^48 menos 1, e qualquer argumento fracionário ou negativo traduz-se num erro numérico. Este limite é suficientemente generoso para cobrir qualquer conjunto de flags (sinalizadores) realista mantendo-se ao mesmo tempo bastante no interior da gama exatamente representável de um double, o que tem importância porque o Excel passa cada argumento numérico como um valor de vírgula flutuante
As funções de deslocamento (shift) contêm a única regra de ordenação que verdadeiramente causa dissabores. Um deslocamento à esquerda (left shift) pode produzir um valor muito superior à sua entrada, e se realizar primeiro o shl e inspecionar o resultado a seguir, o Int64 já terá transbordado e o teste não terá qualquer significado. A verificação tem de ocorrer antes do deslocamento. O HotXLS compara o operando contra o teto deslocado à direita (shifted right) pelo montante do deslocamento, e só efetua o real deslocamento à esquerda se o operando se adequar ao espaço estipulado. Uma magnitude de deslocamento que ultrapasse os 53 bits é prontamente rejeitada, e um deslocamento negativo inverte simplesmente a direção, logo o BITLSHIFT dotado de uma contagem negativa atua efetivamente como um deslocamento à direita (right shift). O princípio aplica-se e expande-se por patamares largamente sobrepostos ao da aludida função isolada: as restrições impostas a nível de código elaboradas visando atalhar as ocorrências passíveis de derivar no sobredimensionamento atinente ao transbordo devem ser, necessariamente, executadas sobre os valores transacionados em sede de entrada (inputs), sem que os expedientes sejam jamais acionados perante os apuramentos resultantes dos processos calculatórios cuja proteção caberia supostamente efetivar (result)
// As chamadas bit a bit são avaliadas da mesma forma através de 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)'); // 5Futuras funções e o prefixo de nome _xlfn
Os operadores bit a bit e uma longa lista de outras adições posteriores a 2007 interagem com um esquema de nomes que não tem nada a ver com o que calculam e tudo a ver com a forma como o Excel as armazena. O formato de folha de cálculo binário original atribuía a cada função incorporada um slot numérico (uma ranhura numérica) numa tabela fixa. As funções inventadas após essa tabela ter sido congelada não têm slot. Para guardar uma função desse género num ficheiro e fazer com que um Excel moderno a reconheça, o nome é escrito com um prefixo _xlfn., de forma a que BITAND seja armazenada como _xlfn.BITAND no disco ainda que o utilizador apenas escreva sempre BITAND
O senão é que a regra não é uniforme. A algumas das funções mais recentes foram atribuídos slots na tabela e são escritas sem prefixo, ao passo que algumas funções ocultas antigas (legacy hidden functions) são também escritas sem um prefixo apesar da sua idade. O HotXLS mantém uma whitelist (lista de permissões) explícita de que nomes precisam do prefixo, adiciona-o aquando da escrita e remove-o aquando da leitura, para que o texto da fórmula que você define (set) e lê de volta (read back) seja sempre o nome limpo virado para o Excel (Excel-facing name). Você define =BITLSHIFT(5,2), o ficheiro guarda _xlfn.BITLSHIFT, e o valor volta como 20, não obstante tudo. O prefixo não passa de um pormenor de armazenamento que nunca deveria transparecer para as fórmulas com que trabalha em código
Juntar tudo numa folha de cálculo
A superfície pública para tudo isto é reduzida. Crie um TXLSXWorkbook, adicione uma worksheet e, das duas, uma: ou escreve uma fórmula numa célula através de Cells[Row, Col].Formula e recalcula, ou avalia uma expressão diretamente com o método Calculate da worksheet, que compila a fórmula face a essa folha e devolve uma Variant. Os exemplos mais acima utilizam Calculate porque mostram o resultado decorrente de uma simples chamada pertencente ao domínio da engenharia abstraída da envolvente da respetiva folha (sheet state), mas em boa verdade é possível aferir resultados perfeitamente equiparados avaliando as estipuladas funções sem destrinças no interior das fórmulas das células reativas ao recálculo do respetivo livro de cálculo
As codificações é que são a parte a ter em mente, não os locais de chamada (call sites). Uma string binária só tem sinal nos dez dígitos e apenas para lá do limiar intermédio para a sua base. Um número complexo é texto, um coeficiente imaginário vazio é um, e o parser passa por cima da letra e de um expoente. Um deslocamento à esquerda (left shift) é verificado antes de se deslocar. Perceba corretamente estes quatro aspetos e a família de engenharia deixa de ser um reduto de surpresas oriundas de discrepâncias de ordem de sinais
Caso se encontre presentemente incumbido de estabelecer ligações tendo em vista a transposição e operacionalização da lógica afeta aos seus próprios domínios matemáticos transacionando os correlativos apuramentos em benefício da incorporação num só motor singular, deve desde já constatar o acompanhamento e os respetivos detalhes abordados sobre a mecânica para o registo do identificador de tratamento em conjugação com a forma subjacente ao desenlace mediante o apuramento de quantias (valores) que encontram devido enquadramento mediante cobertura conferida sob pena do nosso artigo focado em estender o motor de fórmulas com funções personalizadas, sendo que perante cenários a braços com necessidades visando efetuar ligações (referências) ao abrigo de cruzamento relacional entre folhas pelo recurso a apelidações que obviem ao endereço de células a nossa exposição sobre nomes definidos e fórmulas cruzadas entre folhas (cross-sheet) tem o propósito de o elucidar face à forma em que os apontamentos e remissões atuam e culminam na referida resolução. As funções de engenharia tratadas e pormenorizadas na presente exposição formam um todo provido na veste de faceta integrante inserida de corpo e alma pelo respetivo fornecimento inerente à essência do próprio Componente de folha de cálculo HotXLS direcionado em proveito da comunidade Delphi e C++Builder lado a lado num plano de contínua paridade encetada sem menosprezar a totalidade do rol de ferramentas ligadas à escrita à leitura assim como à efetivação do processamento e resolução das quantias atinentes às esferas passíveis de apuramento englobadas mediante as APIs também elas dissecadas noutros capítulos veiculados ao abrigo deste nosso blog