Como gerar um UUID no Excel?

Num banco de dados, é importante ter um registro único como chave, para evitar que dois registros conflitem.

Como eu posso fazer para ter esse identificador (ID) único para cada registro?

Uma forma é ter um controle de IDs: verificar qual o último número já gravado e gerar um novo número. Talvez sequencial, como no exemplo abaixo.

Porém, para casos onde for difícil ou custoso fazer essa verificação, ou a comunicação central online for complicada, é possível utilizar uma técnica de números aleatórios.

É aí que entra o conceito de UUID, identificador universal exclusivo. É um número de 128 bits representado por 32 dígitos hexadecimais, cada um gerado aleatoriamente.

Algo como:

c3d5d666-4050-4bad-8258-6a0c4f8eb701

Há uma série de protocolos para gerar esse número. Apesar de aleatório, é aleatório dentro desses protocolos.

O site a seguir fornece o serviço que gera um UUID, de forma gratuita.

https://www.uuidgenerator.net/

A tabela ficaria assim, para efeito de comparação:

Há chance de duplicação? Sim, há uma probabilidade maior que zero, porém muito pequena: 1 em 2,7 quintilhões. É mais fácil ganhar na mega sena duas vezes seguidas do que dar um conflito de UUIDs.

Em anexo, uma rotina em Excel – VBA que consulta o site citado acima, gera um UUID aleatório e puxa para uma célula. Foi desenvolvida pelo amigo Bruno Cambria, como um componente de um projeto que tocamos.

Um uso possível pode ser gerar N bancos separados, assíncronos, e juntar depois, com chance quase nula de duplicação. Ciência da computação pura.

Link para saber mais.

https://en.wikipedia.org/wiki/Universally_unique_identifier

Bônus: Review do livro Hacking Digital, com boas dicas e cuidados a serem tomados na transformação digital da companhia.

Acesse em https://www.linkedin.com/posts/arnaldogunzi_hacking-digital-best-practices-to-implement-activity-6924321829675241472-73gh

https://ideiasesquecidas.com/

Respostas dos desafios

1 – Preencher números de 1 a 9 nos espaços vazios, e não repetidos.

Uma solução possível: escrever as equações, testar números de 1 a 9 e ficar com aquelas que atendem as equações (vide macro no anexo).

2 – Preencher números primos de modo a atender as multiplicações.

Método: uma forma é decompor o número nos fatores primos.

4147 = 29*11*13.

Daí, continuar a testar: 29 divide 33611, então 29 deve estar na intersecção entre esta linha e coluna.

Dá para escrever uma rotina que decompõe o número, mas é mais fácil usar o Wolfram Alpha. Exemplo:

prime factors of 4147 – Wolfram|Alpha (wolframalpha.com)

Artigo Bônus:

Why Do Chief Data Officers Have Such Short Tenures? – Tom Davenport

Artigo de Tom Daveport, cita alguns desafios do profissional que lida com dados:

– O escopo do trabalho e prioridades são mal definidos desde o início;

– Há uma alta expectativa para o cargo, porém além de implementar Analytics, são necessárias mudanças transformacionais sobre organizações com grande legado. Há enorme necessidade de change management;

Dicas:

– Começar com uma conexão clara com a estratégia de negócios e casos tangíveis;

– Após os projetos piloto, os CDOs devem implementar produtos escaláveis e sustentáveis para agregar valor às unidades de negócio,

– Balancear quick-wins com iniciativas estruturantes;

– Liderar as transformações organizacionais, tomando o cuidado de não ficar relegado a ações de menor relevância;

Veja também:

https://ideiasesquecidas.com/2022/04/14/por-que-o-chief-data-officer-dura-tao-pouco-nas-empresas

Dois desafioszinhos de preencher números

Um mais fácil, outro mais complicado. É possível usar o Excel, resolver no braço ou utilizar algum outro meio para resolver.

Na sexta feira, compilo e envio as respostas.

1)

Preencher nas casas vazias números de 0 a 9, de modo que o mesmo dígito não se repita, e as fórmulas sejam  verdadeiras

Lembrando que multiplicação tem precedência sobre soma e divisão

2) Esse é um pouquinho mais difícil, mas nem tanto. Preencher as casas vazias com números primos, de modo que as multiplicações deem os resultados indicados nas linhas e colunas.

Como plotar o “Gráfico ternário” em Excel?

Primeiro, o que é isso?

É um gráfico na forma de triângulo, a fim de representar visualmente três dimensões  (eu também não conhecia o nome, o amigo Beto Bisogni me explicou).

Digamos que o ponto seja formado pelo trio (20%, 30%, 50%)

Nota: a soma dos três componentes deve dar 100%.

O gráfico a seguir vai mostrar o ponto, segundo as três dimensões de coordenadas.

Basta preencher os pontos a plotar, e clicar no botão. Macros deve estar ativadas.

O gráfico é simplesmente um gráfico de dispersão, com uma transformação dos pontos para ficar nesse formato triangular.

Vide planilha em

https://1drv.ms/x/s!Aumr1P3FaK7joA7TmAktJgKv14mN

Adaptado de: https://github.com/FrancoSivila/TernaryPlot

Ideias técnicas com uma pitada de filosofia
https://ideiasesquecidas.com/

Dicas de Copiar e colar VBA

Em geral, é melhor carregar tudo na memória, trabalhar e voltar tudo de uma vez só.

A cada manipulação de planilha (como colar dados), perdemos eficiência.

Exemplo simples.

Digamos que eu queira copiar valores de A para coluna B.

Um jeito simples é copiar linha a linha.

    n = Application.WorksheetFunction.CountA(Range(“a2:a100000”))

    For i = 2 To n

        Range(“b” & i) = Range(“a” & i)

    Next i

Uma forma extremamente mais eficiente é copiar tudo numa variant, e posteriormente colar ela.

n = Application.WorksheetFunction.CountA(Range(“a2:a100000”))         

‘Copia a tabela inteira para uma variável variant    

tab1 = Range(“a2:a” & 1 + n)         

‘Cola a tabela duma vez só, começando na célula c2    

Range(“c2”).Resize(UBound(tab1, 1), UBound(tab1, 2)) = tab1      

Neste caso específico, a diferença é de 9s, pouco, porém para manipulações mais complexas, a gente pode ganhar muito tempo, da ordem de dezenas de minutos a cada vez que rodar.    

Baixar a planilha aqui, a fim de comparação.

Ideias técnicas com uma pitada de filosofia
https://ideiasesquecidas.com/

Loja e recomendações
https://ideiasesquecidas.com/loja/

Como deixar a execução do seu script VBA em espera…?

(Enviado pelo amigo Wagner Gurgel do Amaral)

Nos nossos projetos em VBA, de vez em quando precisamos fazer uma chamada a aplicações externas para executar algum outro processo.

Porém, o processo externo pode acabar antes ou depois do resto da aplicação VBA. Como garantir a sincronização?

Pensando nisso, a dica de hoje é como fazer o VBA aguardar esse processo externo ser finalizado.

O código abaixo é bem simples e caso necessite também segue aqui (https://1drv.ms/x/s!Aumr1P3FaK7jn39ra16Dtq8ssIQw). O segredo é utilizar a opção (waitOnReturn) no código.

Link para maiores informações: https://docs.microsoft.com/pt-br/office/vba/api/excel.range.run#syntax

Ideias técnicas com uma pitada de filosofia
https://ideiasesquecidas.com/

Como atualizar todas as tabelas dinâmicas do Excel

Segue uma dica escrita pela Mônica Aoki.

Já mostrou dados desatualizados por esquecer de atualizar a tabela dinâmica? Então fique por dentro dessa dica!

Conseguimos atualizar todas as tabelas dinâmicas da planilha apertando apenas um botão:

Como atualizar todas as tabelas dinâmicas do Excel

(Obs. Dados ilustrativos, gerados aleatoriamente)

Mas você quer disponibilizar uma ferramenta para o usuário final e não deseja que ele tenha que fazer nada para atualizar a tabela dinâmica? Conseguimos resolver isso com os “Eventos” do Excel:

1) Clique em Developer > Visual Basic

2) Na janela do Visual Basic, de um duplo clique no nome da sua aba (1). Em seguida selecione a opção “Worksheet” (2) e “Change” (3)

• Cole o seguinte comando dentro da sua função. 

A base de dados do exemplo vai até a coluna “D”. Estou chamando o nome da minha tabela dinâmica (Faturamento) e falando para o excel dar um “Refresh” nela sempre que houver mudança de valor nessas quatro primeiras colunas.

Aqui vocês podem modificar o código para o número de colunas que tem a sua base de dados.

Uma forma alternativa é utilizar activeworkbook.refreshAll, que funciona, porém vai deixar o Excel lento – porque vai atualizar toda a planilha sempre que houver alguma alteração.

Segue planilha para vocês testarem (https://1drv.ms/x/s!Aumr1P3FaK7jn2Ti9JmfnH_8tgIP)

Fica a dica!

Veja também:

https://ferramentasexcelvba.wordpress.com/

https://ideiasesquecidas.com/laboratorio-de-matematica/

Quick sort VBA

Ordenar tabelas pode ser feito jogando numa planilha e usando ordenação por filtros.

Porém, se estivermos trabalhando em VBA na memória, é muito ruim ficar colando dados na planilha só para ordenar.

Uns anos atrás, fiz um quicksort no vba para fazer isso direto em tabelas. Você passa: a tabela, o inicio e o fim, e a coluna a ordenar. Também diz se é crescente ou não. Vide planilha aqui (https://1drv.ms/x/s!Aumr1P3FaK7jn0FQpHettougpiGb)

Sub teste()

Dim tab1 As Variant

Dim i As Integer, j As Integer

ReDim tab1(1 To 1000, 1 To 3)

For i = 1 To 1000

    For j = 1 To 3

        tab1(i, j) = Math.Rnd

    Next j

Next i

‘Ordeno pela coluna 3

QuickSortArray tab1, 1, UBound(tab1, 1), 3, “Crescente”

Range(“a1”).Resize(UBound(tab1, 1), UBound(tab1, 2)) = tab1

End Sub

Colar eficiente – VBA

Um erro comum em VBA é fazer manipulações linha a linha: copiar uma linha da planilha, trabalhar, escrever a linha de volta no Excel.

É extremamente mais eficiente copiar tudo de uma vez, trabalhar e colar tudo.

Para colar a tabela inteira de uma vez, use um comando assim:

Range(“a1”).Resize(UBound(tab1, 1), UBound(tab1, 2)) = tab1

Escolha a célula inicial a colar (no caso, “a1”), depois expanda para as dimensões da tabela (no caso, tab1) e depois colar.

Exemplo aqui (https://1drv.ms/x/s!Aumr1P3FaK7jnz8rsuntJq8VGFce). Veja que é bem eficiente.

Sub colarexemplo()

Dim tab1 As Variant

Dim i  As Integer, j As Integer

ReDim tab1(1 To 1000, 1 To 2333)

For i = 1 To UBound(tab1, 1)

    For j = 1 To UBound(tab1, 2)

        tab1(i, j) = Math.Rnd

    Next j

Next i

Sheets(“Plan1”).Range(“a1”).Resize(UBound(tab1, 1), UBound(tab1, 2)) = tab1

End Sub

Ocultar colunas – VBA

Digamos que eu tenha um dashboard, como o seguinte.

O objetivo é criar uma rotina para mostrar somente as telas que forem clicadas e ocultar as demais.

Ex. Clico no botão “Tela 2”, e a rotina mostra apenas a Tela 2:

Outro exemplo. Clico no botão “Tela 1”, e somente a Tela 1 é mostrada.

1 – Via VBA

Via código, para ocultar as colunas de B a G, é só fazer:

    Columns(“B:G”).EntireColumn.Hidden = True

Para mostrar as colunas de B a G, utilizar False

    Columns(“B:G”).EntireColumn.Hidden = False

E é só adaptar essa lógica para cada caso.

2 – Via agrupar e desagrupar:

Esta é uma funcionalidade 100% Excel, sem código. Selecionar as colunas e via Dados -> Agrupar, vão aparecer uns sinais de + e –, fazendo exatamente a funcionalidade desejada.

Planilha para download: https://1drv.ms/x/s!Aumr1P3FaK7jnzUjn1FYUsDRf6kV

Bônus: O Bingo da AI.

Ideias Analíticas Avançadas

Um mundo mais eficaz através do Analytics

Nova guia (medium.com)