A família de engenharia no Excel parece o canto mais fácil da referência de funções. DEC2BIN transforma um número em uma string binária. HEX2DEC transforma de volta. IMSUM adiciona dois números complexos. Cada um parece um exercício de formatação. Não são. Por trás desses nomes, encontra-se uma codificação de complemento de dois de dez bits que a maioria dos desenvolvedores não tocou desde as aulas de arquitetura de computadores, um formato de número complexo que vive inteiramente dentro de strings e operadores bit a bit que transbordarão (overflow) silenciosamente um inteiro de 64 bits se você deslocar (shift) antes de verificar. Um motor de planilha que reproduz o Excel exatamente não pode arredondar nada disso
As funções se dividem em três grupos e cada grupo esconde uma armadilha diferente. A conversão de base diz respeito a números negativos e limites por base. A aritmética complexa tem a ver com a análise (parsing) e formatação de uma string. Operações bit a bit tratam de manter-se dentro dos limites do Int64. Este artigo aborda cada grupo conforme o HotXLS o implementa, com as chamadas de planilha que você realmente escreveria
Conversão de base e o complemento de dois de dez bits
A direção direta é a parte que todos esperam. DEC2BIN(9) retorna "1001", e um segundo argumento opcional preenche o resultado à esquerda para uma largura fixa. A armadilha é a entrada negativa. O Excel não escreve um sinal de menos. Ele codifica o valor como uma string de complemento de dois de dez dígitos na base de destino, razão pela qual DEC2BIN(-5,10) retorna "1111111011" em vez de qualquer coisa com um sinal. O argumento de casas decimais é ignorado assim que o valor for 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 muda para a metade negativa é 512, e o módulo de quebra (wrap) é 1024, então uma string binária só tem sinal quando tem exatamente dez caracteres e seu valor é de pelo menos 512. A mesma ideia se ajusta de acordo com a base. O octal usa um meio limite de 2^29 e um módulo total de 2^30. O hexadecimal usa 2^39 e 2^40. O leitor do HotXLS aplica exatamente essa regra: ele acumula os dígitos e, somente quando a string tem dez caracteres de largura e o valor acumulado atinge ou fica acima do meio limite, ele subtrai o módulo total para recuperar o valor com sinal. Uma string de nove caracteres é sempre não negativa, não importa o seu tamanho
O codificador é a imagem refletida. Um valor não negativo é convertido dígito por dígito e, opcionalmente, preenchido com zeros na largura solicitada; ele é rejeitado se transbordar (overflow) o teto positivo da base ou se a largura solicitada for muito estreita para contê-lo. Um valor negativo é primeiro trazido para o limite adicionando o módulo total, o que o transforma em um valor cuja representação de base é sempre de dez dígitos e, em seguida, os dígitos são emitidos com zeros à esquerda para preencher a largura. A única verificação de limite compartilhada, 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, como HEX2BIN e OCT2HEX, que mudam a base sem passar por decimal no nome da função. A implementação não carrega uma rotina separada para cada par ordenado. Ela analisa a string de entrada em um valor decimal com sinal usando a base de origem e, em seguida, formata esse valor decimal para a base de destino. O decimal é o pivô. Uma rotina de análise (parse) e uma de formatação, compostas, cobrem todas as combinações, e, como as duas metades compartilham a mesma convenção com sinal de dez dígitos, um valor negativo sobrevive à viagem com seu sinal intacto
Números complexos são strings, então o trabalho é a análise
O Excel não tem tipo de dados complexo. Um valor complexo é a string "a+bi", e todas as funções da família IM recebem essas strings e devolvem uma. COMPLEX constrói a string a partir de uma parte real e uma imaginária. IMSUM, IMSUB, IMPRODUCT e IMDIV analisam seus argumentos, fazem a aritmética nas partes numéricas e formatam o resultado de volta em uma string. O trabalho numérico é álgebra de graduação. A dificuldade consiste inteiramente em transformar o texto em dois números de ponto flutuante de maneira confiável, e é aí que o analisador interno mostra o seu valor
É fácil errar em dois detalhes nesse analisador. O primeiro é a unidade imaginária pura. A string "i" significa um vezes i, não zero e nem um erro; logo, quando o coeficiente na frente do sufixo estiver vazio ou for apenas um sinal de mais, o analisador deve lê-lo como o valor 1, e um sinal de menos solitário como -1. Ignore isso e IMSUM("i","i") deixa de ser 2i. O segundo é a notação científica que colide com o sinal que separa as partes real e imaginária. O analisador encontra esse separador procurando por um mais ou menos, mas um número escrito como "1.5E-3" contém um menos que pertence ao expoente. O exame se recusa, portanto, a tratar um mais ou menos como o separador quando o caractere imediatamente anterior a ele for e ou E. Sem essa proteção, a parte real seria dividida ao meio no sinal do expoente, e a análise falharia com uma entrada perfeitamente válida
O próprio sufixo é preservado em vez de ser normalizado. O Excel aceita tanto i quanto j, e o HotXLS lembra qual deles a entrada usou para que o resultado formatado leve a mesma letra. A formatação aplica então as abreviações convencionais: uma parte imaginária de um é impressa apenas como o sufixo, menos um como -i, uma parte imaginária de zero colapsa para um número real simples e uma parte real de zero descarta o 0+ inicial
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Engineering');
// Entrada negativa: um complemento de dois de dez bits, argumento de casas decimais 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 transcendentais, entre elas IMSQRT, IMEXP, IMLN e IMPOWER, não funcionam em coordenadas retangulares. Elas convertem o valor analisado para a forma polar, aplicam a operação no módulo e argumento e os convertem de volta. Uma raiz quadrada reduz o argumento pela metade e extrai a raiz do módulo. Uma potência multiplica o argumento e eleva o módulo. Fazer de qualquer outra forma significaria deduzir novamente cada identidade na forma retangular, o que resulta em mais código e menos estabilidade numérica próximo aos cortes de ramificação (branch cuts)
Operadores bit a bit e o transbordamento que você deve verificar primeiro
O Excel 2013 adicionou BITAND, BITOR, BITXOR, BITLSHIFT e BITRSHIFT. Os operandos têm restrições: cada um deve ser um número inteiro não negativo não superior a 2^48 menos 1, e qualquer argumento fracionário ou negativo representa um erro numérico. Esse teto é generoso o suficiente para cobrir qualquer conjunto de flags (sinalizadores) realista, ao mesmo tempo que fica bem dentro do intervalo exatamente representável de um double, o que é importante, pois o Excel repassa todos os argumentos numéricos como um valor de ponto flutuante
As funções de deslocamento possuem a única regra de ordenação que realmente morde. Um deslocamento à esquerda pode produzir um valor muito maior do que sua entrada, e se você executar o shl primeiro e inspecionar o resultado em seguida, já transbordou o Int64 e o teste não fará sentido. A verificação tem que acontecer antes do deslocamento. O HotXLS compara o operando com o teto deslocado para a direita pelo valor de deslocamento e somente se o operando couber, ele executa o verdadeiro deslocamento à esquerda. Uma magnitude de deslocamento além de 53 bits é totalmente rejeitada, e um deslocamento negativo simplesmente inverte a direção; assim, o BITLSHIFT com uma contagem negativa comporta-se como um deslocamento à direita. O princípio generaliza para muito além dessa única função: quando existe uma proteção para evitar transbordamento (overflow), ela tem que ser executada nas entradas, nunca no resultado que deveria proteger
// As chamadas bit a bit são avaliadas da mesma maneira através do 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)'); // 5Funções futuras 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 nomeação que não tem nada a ver com o que eles calculam e tudo a ver com a forma como o Excel os armazena. O formato de planilha binária original atribuía a cada função incorporada um slot numérico em uma tabela fixa. Funções inventadas depois que essa tabela foi congelada não possuem um slot. Para salvar tal função em um arquivo e fazer com que um Excel moderno a reconheça, o nome é escrito com um prefixo _xlfn., de modo que o BITAND seja armazenado como _xlfn.BITAND no disco, mesmo que o usuário só digite BITAND
O problema é que a regra não é uniforme. Algumas funções mais novas receberam slots na tabela e são escritas sem prefixo, enquanto algumas funções antigas ocultas também são escritas sem o prefixo, apesar de sua idade. O HotXLS mantém uma lista de permissões explícita de quais nomes precisam do prefixo, os adiciona ao gravar e os retira ao ler, para que o texto da fórmula que você define e lê seja sempre um nome claro, semelhante ao do Excel. Você define =BITLSHIFT(5,2), o arquivo retém _xlfn.BITLSHIFT, e o valor volta como 20, independentemente de qualquer coisa. O prefixo é um detalhe de armazenamento que nunca deve vazar para as fórmulas com as quais você trabalha em código
Juntando tudo numa planilha
A superfície pública para tudo isso é pequena. Crie um TXLSXWorkbook, adicione uma planilha e então escreva uma fórmula em uma célula por meio de Cells[Row, Col].Formula e recalcule, ou avalie uma expressão diretamente com o método Calculate da planilha, o qual compila a fórmula contra aquela folha e retorna um Variant. Os exemplos acima usam Calculate porque ele exibe o resultado de uma única chamada de engenharia sem o estado em torno da planilha, mas as mesmas funções são avaliadas de modo idêntico dentro de fórmulas de células reais quando a pasta de trabalho é recalculada
As codificações são a parte a se ter em mente, não os locais de chamada. Uma string binária tem sinal somente nos dez dígitos e apenas além do limite intermediário para sua base. Um número complexo é um texto, um coeficiente imaginário vazio equivale a um e o analisador avança sobre o e de um expoente. Um deslocamento à esquerda é verificado antes de fazer o deslocamento. Ao acertar esses quatro fatos, a família de engenharia deixa de ser uma fonte de surpresas de erro de sinal (off-by-a-sign)
Se você estiver ligando sua própria matemática de domínio no mesmo mecanismo, a mecânica de registrar um manipulador e retornar valores é abordada em nosso artigo sobre como estender o mecanismo de fórmula com funções personalizadas, e quando essas fórmulas precisam alcançar entre as planilhas por nome em vez de endereço de célula, o passo a passo sobre nomes definidos e fórmulas de planilhas cruzadas mostra como as referências se resolvem. As funções de engenharia aqui descritas são fornecidas como parte do componente de planilha HotXLS para Delphi e C++Builder, junto com as APIs de leitura, escrita e cálculo abordadas em outras seções deste blog