Referências dinâmicas a outras planilhas com Excel?

Se este post não responder a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Olá amigos,

Hoje me deparei com outra pergunta na internet e resolvi postar a resposta ao nossa amiga aqui no blog. A pergunta dele foi a seguinte: 

Tenho varias planilhas de excel. Cada planilha um nome. Na planilha de controle quero deixar para o usuário escolher de qual planilha vai puxar. Exemplo:

Temos a planilha de 001 até 025. Na formula vou colocar ='C:\Teste\[001.xlsx]Plan1$A$1'
essa será a formula só que quero deixar o usuario escolher de onde ele quer puxar da 001 até a 025. Criei uma lista suspensa dando a opção mas não consigo deixar a formula principal mutável para poder escolher. Existe alguma saida?
A unica solução que achei foi mandar ele puxar todos do 001 a 025(ocultar essa planilha) e depois criar outra planilha deixando o usuario escolher e usando a formula Proc listando a primeira coluna como referencia.
Eu quero eliminar essa planilha total de intermediário para que assim não precise atualizar tudo que ele ta pedindo e apenas os numeros em questão. É claro que esses numeros serão selecionados em outro lugar.

Existe alguma saida?

quero algo tipo
='C:\Teste\[A1]Plan1$A$1'
onde eu possa escolher o arquivo final de onde vai puxar. è possivel?

Vamos usar uma simples macro em VBA?

Vá na guia Desenvolvedor (se esta guia não aparecer no seu Excel, vá em Arquivo > Opções > Personalizar Faixa de opções e marque a opção "Desenvolvedor" na segunda célula).

Em seguida clique em Visual Basic:

Na janela que se abre, de um duplo clique em sua planilha de controle:

Na janela que se abre, copie o código a seguir:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'quando usuário fizer alteração faça:
planilha = Range("A2").Text 'atribui a planilha escolhida a uma variável
Range("A1").Value = "='C:\Teste\[" & planilha & ".xlsx]Plan1'!R1C1" 'insere a variável na fórmula dinâmica
End Sub

Onde está A2 substitua pela célula da caixa suspensa, onde o usuário escolhe. Onde está A1 substitua pela célula da fórmula principal, que será dinâmica, mutável.

Feche a janela. Salve a planilha. 
Toda vez que o usuário alterar a planilha de qual vai puxar, a fórmula é alterada automaticamente.

Não se esqueça de se increver acima ^ e receber as novidades do blog!!

Recomendo também o Curso Avançado de Excel!

Abraços 

Albanir

Como inserir uma linha antes de cada celula específica no Excel?

Se este post não responder a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Olá amigos,

Hoje me deparei com essa pergunta na internet e resolvi postar a resposta ao nosso amigo aqui no nosso blog. A pergunta dele foi a seguinte: 

Tenho uma planilha com 200 municípios e em cada um tenho 4 categorias

ex:

Município 1

COMERCIAL

INDUSTRIAL

PUBLICO

RESIDENCIAL.

Municipio 2

COMERCIAL

INDUSTRIAL

PUBLICO

RESIDENCIAL. (etc)

Preciso inserir uma linha antes de cada "residencial", pois surgiu outra categoria agora. (categora MISTO). Será se há possibilidade?

Obrigado.

A dica é simples. Pode-se fazer um a um é claro, mas também é possível fazer com uma macro em VBA. Como fazer isso?

Vá na guia Desenvolvedor (se esta guia não aparecer no seu Excel, vá em Arquivo > Opções > Personalizar Faixa de opções e marque a opção "Desenvolvedor" na segunda célula).

Em seguida clique em Visual Basic:

Na janela que se abre, clique em Inserir > Módulo:

Na janela que se abre, copie o código a seguir:

Sub MISTO()

Range("A1").Select 'seleciona primeira celula

    Cells.Find(What:="residencial", After:=ActiveCell, LookIn:=xlFormulas, _

        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False, SearchFormat:=False).Activate 'procura residencial

    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'insere a linha

    ActiveCell.FormulaR1C1 = "MISTO" 'escreve MISTO na linha nova

   

For municipios = 1 To 199

    Cells.Find(What:="residencial", After:=ActiveCell, LookIn:=xlFormulas, _

        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False, SearchFormat:=False).Activate 'procura residencial

    Cells.FindNext(After:=ActiveCell).Activate 'procura residencial

    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'insere a linha

    ActiveCell.FormulaR1C1 = "MISTO" 'escreve MISTO na linha nova

Next 'faz isso 200 vezes

End Sub

Aonde está 199 substitua pelo número de cidades menos 1.

Feche a janela. Salve a planilha.

Agora na janela do Excel, vá novamente na guia desenvolvedor, mas agora clique em Macros.

Com a Macro MISTO selecionada, clique em Executar.

Alterações feitas com sucesso! Caso aconteça qualquer erro, feche a planilha sem salvar e verifique as fórmulas, especialmente o número de cidades. 

Não se esqueça de se increver acima ^ e receber as novidades do blog!!

Abraços 

Albanir

Como calcular os juros de pagamento em atraso (ou juros de mora diária) usando o Excel?

Se este post não responder a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Vamos construir uma planilha que contém os princípios para o cálculo de pagamento em atraso.

Vamos usar as fórmulas de soma e subtração e a função SE. Precisamos descrever em cada linha os seguintes campos: Valor da dívida, vencimento, data do pagamento e juros por dia em atraso (Mora). Com essas informações conseguiremos definir qual o valor a ser pago após o vencimento. Veja o modelo:

Temos que descobrir quantos dias estão em atraso, neste caso, 20 dias:

Agora que temos os número de dias em atraso, abrimos uma coluna para descrever quanto de juros devemos cobrar neste período. A fórmula é condicional:

=SE(E2<0;0;E2*D2)

Se "dias de atraso" for menor que zero (ou seja, a pessoa pagou a tempo), a fórmula retorna 0 (zero), não haverá juros. Caso contrário, multiplica a taxa de juros diária pelo número de dias em atraso, neste caso 9,5%:

Quanto isso vale em dinheiro? Multiplica-se essa taxa total pelo valor total. Neste caso, o cliente paga R$ 1,14 a mais:

Podemos ter uma coluna com o valor total:

Assim temos um modelo simples para calcular o atraso de pagamento, quer tenhamos um pequeno negócio, quer seja para controle pessoal.

Abraços

Como deixar um comentário dentro da fórmula do Excel?

Se este post não responder a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Sim, é possível. Para isso utilize a função N do Excel. 

Para você entender, essa função tem quatro aplicações: 
1) converte um valor não numérico em número (não me lembro de nenhum exemplo rsrs...);
2) converte datas em números de série (Ex: =N(A1), onde A1 contém 15/04/2013, a função retorna 41379);
3) converte VERDADEIRO em 1 e FALSO em 0 (Ex: =N(A1), onde A1 contém =10/2=5, a função retorna VERDADEIRO) e;
4) converte qualquer outro valor em 0 (zero) (e aqui está a grande sacada...)

Visto que a função N converte qualquer outro valor em 0 (zero), ela converte qualquer texto em 0 (zero). Assim você pode escrever uma fórmula e somar a função N com textos de dicas de fórmulas dentro da fórmula, um comentário dentro da fórmula do Excel, ou comentários em células do Excel, se desejar. Isto não alterará o valor da fórmula. Veja o exemplo a seguir:

=SOMASE(F3:F12;"algodão";G3:G12), retorna 5.

Agora, vou colocar um comentário explicativo e ver se há alteração:

=SOMASE(F3:F12;"algodão";G3:G12)+N("esta fórmula soma o número de camisetas de algodão disponíveis"), também retorna 5.

Mas "o Excel já disponibiliza uma ferramenta para comentários", talvez diga. Bem, a função N pode ser, na realidade, uma opção para quando não é prático utilizar a ferramenta comentário (Revisão > Novo Comentário). Com essa função, você pode explicar cada aspecto de sua fórmula, quando ela é um pouco longa por exemplo. E a planilha não fica "mais pesada" para calcular, porque a função N, ao detectar que é texto, automaticamente soma 0 (zero) à fórmula.

Mãos a obra amigão! Sabe aquela planilha com várias fórmulas que você já nem lembra pra que serve cada uma? Não cometa novamente este erro! Explique suas fórmulas para que você e outros não se percam futuramente. 

Abraços

Como usar uma fórmula do Excel num código VBA?

Se este post não responder a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Olá amigo,

Esta dica pode ser útil especialmente se você quer desenvolver planilhas com aplicações em VBA. Como utilizar as fórmulas que esté acostumado no Excel dentro do ambiente VBA? 

A dica é simples, basta utilizar uma chamada em VBA para fórmulas e depois escrever a fórmula respeitando os parâmetros necessários. Veja o seguinte exemplo:

=PROCV(A1;B1:C10;2;0) 
(Procurar o valor da fórmula A1 dentro da tabela B1:C10, e retornar o valor exato da segunda coluna correspondente)

Ao escrever no Excel, basta utilizar essa notação dentro de uma célula e pronto! Mas com códigos VBA a notação é a seguinte:

Range("célula").FormulaLocal = "=PROCV(A1;E1:F10;2;0)"

Observe que este código utiliza a extensão "FormulaLocal", o que significa que a fórmula é utilizada em português, como talvez esteja acostumado. Mas talvez você encontre o mesmo código em inglês. Por quê?

Range("célula").Formula = "=VLOOKUP(A1,E1:F10,2,0)"

Neste caso, a extensão é "Formula" e o separador dos critérios é vírgula, ao invés de ponto e vírgula. Veja essa lista de conversão do português para o inglês, e vice-versa. É só dar um Ctrl+F na página e encontrar a fórmula que deseja.

Cuidado para não variar entre o inglês e o português. Escolha qual linguagem irá usar e siga um padrão, para o código não travar.

Se este post não respondeu a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Bom desenvolvimento!

Sortear Letras e Números

Se este post não responder a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Olá pessoal,

Hoje vou compartilhar com vocês algo muito útil que aprendi e tem a ver com duas funções do Excel: ALEATÓRIOENTRE e CARACT. 

Com a função ALEATÓRIOENTRE podemos sortear aleatóriamente um número entre um conjunto de números. Seria útil para, por exemplo, gerar uma senha, ou sortear o nome de alguém em determinada lista, fazer cartelas de bingo ou caça-palavras. Os argumentos da função são: inferior e superior, ou seja o primeiro e o último números do intervalo que desejamos sortear. Exite também a função ALEATÓRIO, que automaticamente sorteia números racionais entre 0 e 1.

A função CARACT é interessante. No Excel, as letras também podem ser representadas por números. As letras maiúsculas, por exemplo começam do 65 até 90, enquanto as minúsculas vão do 97 a 122. Ao inserirmos um número desse intervalo na função CARACT, temos como resultado a letra correspondente.

Agora juntando as duas funções, temos a possibilidade de sortear letras! Sim, basta usarmos a função ALEATÓRIOENTRE, sortear um número dentro do intervalo que representa as letras, e inserirmos o retorno na função CARACT. Veja como ficaria:

Se este post não respondeu a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Forte abraço!

Recálculo de Boleto Vencido

Se este post não responder a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Olá amigos,

Encontrei algo na internet que é muito interessante, principalmente para quem é amante de planilhas, além de ser muito útil. Todos nós já tivemos que ir ao banco ou a uma lotérica e enfrentar aquela loooonga fila para pagar um boleto ou uma conta vencida, porque a padaria não recebe mais, ou porque não é mais possível pagarmos pela internet. Pois é.. o fato é que as lotericas e bancos simplesmente refazem o cálculo usando um modelo matemático que gera um novo código para o boleto, com o novo valor a ser pago.

Um internauta conseguiu incluir esse modelo matemático em uma planilha do Excel fácil de ser usada. A pessoa coloca o número do código de barras e "refaz" um novo código de barras a ser pago, no caso de pagamento após o vencimento. Como ele fez isso, é uma propriedade intelectual dele, logo ele está vendendo essa planilha por um valor de R$ 59,90, para download. Para quem acaba atrasando boletos e tem aí um dinheirinho para evitar filas e stress, certamente compensa...

Taí a dica pessoal, e para quem quiser conferir pode clicar no link abaixo:

• Recalculo de boleto vencido

Se este post não respondeu a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.

Abraços