VBA e Google Translate


Segue uma implementação de VBA que lê o texto na coluna A, e traduz para o inglês na coluna B.

Ele usa o Google Translate. Basicamente, abre um browser, consulta o Google Translate e retorna valor.

Como é a versão free, tem um limite de consultas, mas para uma lista pequena funciona bem.

Também podem ter erros de automação, porque é como se a macro abrisse um browser de verdade.


Link para download.

Inspirado em https://analystcave.com/excel-google-translate-functionality/


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

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/


Anúncios

Concatenação de strings no Excel

O operador & serve para concatenar strings, nas fórmulas do Excel.

Exemplo. Para concatenar as células A4 e B4, a fórmula é = A4 & B4

É possível usar este dentro de fórmulas mais complexas.

Exemplo.

A partir da tabela da esquerda, quero contar os valores maiores que 15.000.

Fórmula

‘=CONT.SE(H4:H7;”>”&15000)

Note que o critério de contagem é

“>” & 15000

No caso, exatamente igual a “>15000”

Download exemplo.


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

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Identificar valores nulos no Spotfire

Dica para identificar valores nulos numa tabela.

Digamos que temos nulos para a coluna “Nome” de uma tabela.

Basta utilizar a palavra chave is null, como no exemplo a seguir.

If([Nome] is null,1,0)

Isto pode confundir porque cada linguagem tem uma sintaxe diferente.

Vide arquivo no Google Drive.


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

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Dica da Microsoft: preenchimento relâmpago


Quando uma tabela tem um padrão simples, o Excel consegue inferir o restante das linhas.

A opção “Preenchimento relâmpago” faz o trabalho.

Página Inicial -> Edição -> Preenchimento relâmpago

Ou, melhor ainda, utilizar CTRL + E, tecla de atalho para este comando.

É simples e ajuda bastante.

Download de arquivo exemplo no Google Drive.


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

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Sobre o Join no Spotfire


Cruzar bases de dados é chamado de “join”, nas linguagens SQL.

É sempre algo confuso. Espero esclarecer um pouco como se faz isto no Spotfire.

Imagine que tenho duas tabelas.

A primeira com um cadastro de nomes:

E uma segunda com o valor pago por mês:

Há sempre dois problemas em banco de dados: dados inexistentes e dados duplicados.
Temos tantas formas de join por conta disso. Como tratar dados inexistentes? Como tratar dados duplicados? Como tratar ambos?

Nas tabelas acima, “Arnaldo” tem duas linhas na tabela 2.
“Júlia” não aparece no cadastro.

Como cruzar as bases?

Primeiro, importar as mesmas. Chamei a primeira de “Base1” e a segunda de “Base2”, mas talvez fosse melhor chamar de base esquerda e direita.




Depois, inserir -> coluna…

A Base1 aparece na primeira opção, “Adicionar colunas à tabela de dados”
A Base2 eu seleciono, e ela vai aparecer em “Adicionar colunas de:”.
Clicar em “Próximo”

A seguir, dizer qual será a chave para cruzar as colunas.
No caso, o Nome: basta selecionar Nome em ambas colunas e “Combinar Selecionado”.

A próxima tela é o problema.


São os diversos tipos de join.

Mas, antes, selecionar a coluna “Valor”, que é a que vou puxar para a Base1 (ou à esquerda).

Vou começar do mais fácil, independente da ordem que aparece na lista.


Caso 1: Correspondência única da esquerda. O caso mais fácil, por ser igual ao procv do Excel.

Resultado:

Note que puxa o primeiro valor de “Arnaldo” e despreza o segundo valor.

Note também que “Júlia” não aparece, por estar apenas na segunda tabela. É idêntico ao procv.


Case 2: Exterior esquerdo.

Note que a primeira linha, “Arnaldo”, duplica. Constam agora os dois valores.
E “Júlia” não aparece.

É por isso que a pessoa deve saber o que está fazendo, corre o risco de duplicar dados sem querer.

“Exterior esquerdo” é algo como “todos da tabela da esquerda (no caso, Tab1)”.


Caso 3: Exterior completo:

Neste caso, duplica a primeira linha, “Arnaldo”, como antes.

E agora, a “Júlia”, que nem aparece no cadastro, agora faz parte desta lista final.

Por isso o nome “completo”: completo na esquerda e na direita.


Caso 4: Interior

Se “Exterior” é algo como “todos”, “Interior” é algo como “intersecção”.

Agora, só a intersecção de quem está nas duas tabelas aparece. O “Carlos” sumiu, por não estar na base 2. E a “Júlia” sumiu, por não estar na base 1. E continua duplicando o “Arnaldo”.

Casos 5 e 6: tabela à direita.

A ideia é exatamente a mesma que antes, só que invertendo a posição das tabelas.


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

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Representação visual da sequência de Fibonacci

Forgotten Math

A sequência de Fibonacci há tempos fascina os matemáticos, por ter uma regra de formação simples, porém com diversas propriedades surpreendentes.

Esta sequência começa com 1 e 1. Os demais números são a soma dos dois anteriores. Portanto, os próximos números são:

2 = 1 + 1
3 = 2 + 1
5 = 3 + 2
8 = 5 + 3
e assim sucessivamente.

Há uma forma bonita de visualizar esta sequência.
Iniciando com um quadrado de lado 1, coloque outro quadrado idêntico do seu lado.

Como o próximo número é a soma desses dois.

E assim sucessivamente.

1, 1, 2, 3, 5, 8, 13, 21, etc…

Com 10 quadrados:

Criei dois programinhas para plotar esta visualização.

Uma em Javascript D3, que é uma biblioteca fantástica para a parte gráfica. Vide projeto interativo no Github, aqui: https://asgunzi.github.io/Fibonacci/

E outra em Excel – VBA, disponível para download aqui. Print…

Ver o post original 97 mais palavras

Macro para atualizar a base de dados da tabela dinâmica

Como atualizar a fonte de dados de uma tabela dinâmica, para que ela leia certinho o número de linhas da fonte?

Uma forma simples de contornar o problema é colocar um range grande para a entrada da tabela dinâmica, digamos 500 mil linhas, e não ter que se preocupar com isto.  A desvantagem é que isto obriga o Excel a ler tais linhas, mesmo que vazias, aumentando o peso do arquivo.

Outra forma proposta é via macro, download aqui um exemplo.

Primeiro, lê quantas linhas têm na coluna A da Planilha 1 e joga na variável “nl”

Sheets(“Plan1”).Activate

‘Lê quantas linhas tem na coluna a, limitado a 100000 linhas

nl = Application.WorksheetFunction.CountA(Range(“a1:a100000”))

Depois atualiza a fonte de dados.

Há uma série de parâmetros aqui, o nome da tabela dinâmica, o range de dados. Uma forma fácil de obter esses parâmetros é gravando uma macro que atualiza a tabela, e depois apenas modificar o campo “SourceData”.

‘Preencher com o nome da tabela dinamica

‘É necessário também mudar a fonte de dados

‘No caso, “Plan1!R1C1:R” & nl & “C3”, está lendo da linha 1 coluna 1 (R1C1) até a linha nl coluna 3

ActiveSheet.PivotTables(“Tabela dinâmica1″).ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=”Plan1!R1C1:R” & nl & “C3”,   Version:=xlPivotTableVersion15)


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

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/