Somar ou Contar valores entre datas excel

Na maioria das funções do Excel, para fazer uma comparação como por exemplo maior ou menor basta inserir os símbolos ">" ou "<". Porém para as funções CONT.SES e SOMASES não funciona.
Para que estas funções funcionem corretamente, devemos inserir os sinais de maior ou menor entre aspas e unir com a célula onde contem o critério.

Supondo tenhamos datas na coluna A e valores na B, e queremos somar os valores em um intervalo específico de datas.
Em C1 inserimos a data inicial e em C2 a data final. em qualquer outra célula inserimos a seguinte função: = CONT.SES(A:A; ">=" & C1;A:A;"<=" & C2) . Para a soma utilizamos esta função. = SOMASES(B:B;A:A;">="&D1;A:A;"<="&D2) . Lembrando que os valores inicial e final estão inclusos na contagem ou soma, caso queira excluir-los, basta retirar os sinais de " igual " do meio da função.


Acesse a pagina inicial do blog e tenha acesso a todo o conteúdo disponibilizado

Veja a vídeo aula.

Baixe a planilha

Abrir planilha com senha excel VBA

Olá. Vou disponibilizar uma macro para abrir planilhas com senha do excel através de outra planilha.
Basta inserir na macro o caminho e a senha do arquivo.


Sub abre_planilha()
  
         Call Workbooks.Open(Filename:="D:\EXEMPLO\loja 1.xlsx", _
        Password:="123", WriteResPassword:="456", ReadOnly:=True)
    
    End Sub

Onde;
Password é a senha de proteção da planilha a ser aberta;
WriteResPassword é a senha de gravação;
ReadOnly é a forma de abertura da planilha, somente leitura ou leitura e gravação. Use "true" para somente leitura e "false" para leitura e gravação.


Quem ficou com dúvidas favor postar nos comentários.

Acesse a pagina inicial do blog e tenha acesso a todo o conteúdo disponibilizado

Veja a Vídeo Aula

Atualizar dados entre planilhas com senha excel vba

Ola, o codigo abaixo serve para atualizar dados ou links entre planilhas do excel protegidas por senha. Aquela janela chata que fica solicitando a senha toda vez que voçê abre a planilha, elimine-a com o codigo abaixo.

Primeiramente selecione a aba dados, clique em "editar links", na janela que se abre clique em "prompt de inicialização ..." e marque a opção "não exibir alertas e nao atualizar links automáticos"

No editor VBA, insira um comando auto_open e insira o código abaixo.

Sub Atualizar()
'A linha abaixo insere a senha e pressiona enter
Application.SendKeys ("123{ENTER}")
'Atualiza o link com a planilha no caminho abaixo
ActiveWorkbook.UpdateLink Name:="D:\EXEMPLO\Loja 1.xlsx", Type:= _
xlExcelLinks

'Deve ser inserido um comando esc entre cada link, quando temos mais do que um.
SendKeys "{esc}", True

Application.SendKeys ("123{ENTER}")
ActiveWorkbook.UpdateLink Name:="D:\EXEMPLO\Loja 2.xlsx", Type:= _
xlExcelLinks

end sub

Acesse a pagina inicial do blog e tenha acesso a todo o conteúdo disponibilizado

VEJA A VIDEO AULA

Menu Suspenso Excel

Olá. Neste post vou explicar como criar um menu suspenso no excel utilizando o VBA. Este menu é também chamado de menu PopUp, onde podem ser inseridos vários sub menus e FaceId.
Segue duas macros diferentes que geram o menu suspenso, e no fim da página o link para download da planilha pronta.

Menu Suspenso/ Menu PopUp

Sub Jogos()
Application.CommandBars("Cell").Reset

Dim cbc As CommandBarControl

'Oculta todos os comandos do botão direito
For Each cbc In Application.CommandBars("cell").Controls
cbc.Visible = False
Next cbc

'Adiciona um sub menu com o nome cartas

Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup).Caption = "Tabuleiro"
With Application.CommandBars("Cell").Controls("&Tabuleiro")

'Adiciona um sub menu com o nome xadrez
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 217
.Caption = "Xadrez"
.OnAction = "Xadrez"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 150
.Caption = "damas"
.OnAction = "damas"
End With
End With


Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup).Caption = "Eletronicos"
With Application.CommandBars("Cell").Controls("Eletronicos")
.BeginGroup = True

With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 59
.Caption = "Vídeo Game"
.OnAction = "Vídeo_Game"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 574
.Caption = "Android"
.OnAction = "Android"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 69
.Caption = "&Windows"
.OnAction = "Windows"
End With
End With

Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup).Caption = "Cartas"
With Application.CommandBars("Cell").Controls("&Cartas")
With .Controls.Add(Type:=msoControlPopup)
.Caption = "&Pretas"


With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 483
.Caption = "Espadas"
.OnAction = "Espadas"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 484
.Caption = "Paus"
.OnAction = "Paus"
End With
End With

' No submenu cartas adiciona o submenu vermelhas
With .Controls.Add(Type:=msoControlPopup)
.Caption = "&Vermelhas"

' No submenu vermelhas adiciona o submenu menor que 5
With .Controls.Add(Type:=msoControlPopup)
.Caption = "Menor que 5"

With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 481
.Caption = "Copas"
.OnAction = "Copasmenor"
End With

With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 482
.Caption = "Ouro"
.OnAction = "Ouromenor"
End With

End With


With .Controls.Add(Type:=msoControlPopup)
.Caption = "Maior que 5"

With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 481
.Caption = "Copas"
.OnAction = "Copasmaior"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 482
.Caption = "Ouro"
.OnAction = "Ouromaior"
End With

End With

End With

End With



        'mostra o menu
       Application.CommandBars("Cell").ShowPopup

'reset do menu
Application.CommandBars("Cell").Reset
               For Each cbc In Application.CommandBars("cell").Controls
                cbc.Visible = True
        Next cbc

End Sub
Sub Xadrez()
MsgBox "Você escolheu Xadrez ", , "Jogos"
End Sub
Sub damas()
MsgBox "Você escolheu damas ", , "Jogos"
End Sub
Sub Vídeo_Game()
MsgBox "Você escolheu Tablet ", , "Jogos"
End Sub
Sub Android()
MsgBox "Você escolheu Android ", , "Jogos"
End Sub
Sub Windows()
MsgBox "Você escolheu Windows ", , "Jogos"
End Sub
Sub Espadas()
MsgBox "Você escolheu Espadas ", , "Jogos"
End Sub
Sub Paus()
MsgBox "Você escolheu Paus ", , "Jogos"
End Sub
Sub Copasmenor()
MsgBox "Você escolheu Copas menor do que 5 ", , "Jogos"
End Sub
Sub Ouromenor()
MsgBox "Você escolheu Ouro menor do que 5 ", , "Jogos"
End Sub
Sub Copasmaior()
MsgBox "Você escolheu Copas maior do que 5 ", , "Jogos"
End Sub
Sub Ouromaior()
MsgBox "Você escolheu Ouro maior do que 5 ", , "Jogos"
End Sub

---------------------------------------------------------------------------------------------------------------


Sub MenuSuspenso()
Application.CommandBars("Cell").Reset

Dim cbc As CommandBarControl

'Oculta todos os comandos do botão direito
For Each cbc In Application.CommandBars("cell").Controls
cbc.Visible = False
Next cbc

'Adiciona um comando ao menu suspenso
With Application.CommandBars("Cell").Controls.Add(Temporary:=True)
.Caption = "Word"
.OnAction = "Word"
.FaceId = 42
End With

With Application.CommandBars("Cell").Controls.Add(Temporary:=True)
.Caption = "Acces"
.OnAction = "Acces"
.FaceId = 264
End With
'Adiciona um comando ao menu suspenso
With Application.CommandBars("Cell").Controls.Add(Temporary:=True)
.Caption = "Excel"
.OnAction = "Excel1"
.FaceId = 263
End With

Application.CommandBars("Cell").ShowPopup
Application.CommandBars("Cell").Reset
               For Each cbc In Application.CommandBars("cell").Controls
                cbc.Visible = True
        Next cbc

End Sub
Sub Word()
MsgBox "Voce selecionou Word ", , "MS Office''"
End Sub
Sub Acces()
MsgBox "Voce selecionou Acces ", , "MS Office''"
Sub Excel1()
MsgBox "Voce selecionou Excel ", , "MS Office''"
End Sub

End Sub



Acesse a pagina inicial do blog e tenha acesso a todo o conteúdo disponibilizado


Baixar planilha

Caso o tenha problemas para baixar, solicite por e-mail: atualexcel@gmail.com

PROCV E PROCH

O Excel permite fazer pesquisas baseadas em uma determinada lista de dados, usando determinado argumento para retornar um valor correspondente a ele. Sendo que a procura pode ser feita de duas maneiras diferentes: procura vertical (colunas) ou procura horizontal (linhas)

Para você definirmos qual das duas funções utilizar, obsrvamos na planilha como estão distribuídos os valores que poderão ser o resultado da busca: se os valores esiverem em colunas utilizamos a função PROCV, e se os valores estiverem em linhas utilizamos a função PROCH.

Sintaxes das funções PROCV E PROCH

• =PROCV(valor_procurado; matriz_tabela; num_coluna; procurar_intervalo)
• =PROCH(valor_procurado; matriz_tabela; num_linha; procurar_intervalo)

Onde:

• valor_procurado é o argumento que deseja fornecer como base para a procura ser feita;
• matriz_tabela é o intervalo onde se realizará a pesquisa;
• num_coluna ou num_linha é a coluna ou a linha que se deseja obter o resultado, considerando que as colunas e linhas são contadas a partir do intervalo estipulado em matriz_tabela;
• procurar_intervalo é a precisão da pesquisa, podendo ser exata ou por aproximação do valor desejado.

Sendo que:

• Busca exata = 0
• Busca aproximada = 1

Obs: O valor_procurado deve estar na primeira coluna ou na primeira linha do intervalo, caso contrário a pesquisa retornará erro.

Função PROCV

A função PROCV pesquisa um valor da primeira coluna de uma lista de dados especificado por você em valor_procurado. Ela então procura o número de colunas que você determinou em num_coluna e retorna o valor que encontrar.

No exemplo a seguir temos duas tabelas onde um contém o códio e o preço do produto e na outra temos a descrição e código , e que remos inserir o preço correspondente a cada produto na coluna H.

 

Exemplo:

Na célula H3, inserimos a formula, Usaremos o assistente de função para facilitar, clique em fx ou acesse o menu Inserir e clique no item Função

 

 

valor_procurado: é a célula G3;

matriz_tabela: São as colunas B e C;

num_coluna: é 2, pois o intervalo começa na coluna B, sendo que os valores a serem retornados estão na coluna C, ou seja a segunda coluna da matriz;

procurar_intervalo: é 0 "zero", pois queremos o valor exato da pesquisa.

Clique em OK após finalizar.

VEJA A VIDEO AULA

Clique aqui para baixar a planilha



Função PROCH?
Esta função realiza uma pesquisa horizontalmente, ou seja ela faz a busca de um determinado argumento em linhas. O valor é procurado da primeira linha de uma lista de dados especificado por você em valor_procurado. Ela procura o número de linhas que você determinou em num_linha e retorna o valor que encontrar lá.

Exemplo

Aplicando a função PROCH na célula B12, onde ela irá avaliar a célula B11, e assim irá procurar este valor no intervalo B2:F9, e retornará o valor da linha 8 (Total) correspondente.

Usaremos o assistente de função para facilitar, clique em fx ou acesse o menu Inserir e clique no item Função

 




 

 

• valor_procurado: é a célula B11;
• matriz_tabela é o intervalo de B2:F9;
• num_linha: é 8, pois o intervalo começa na linha 2, e os valores a serem retornados estão na linha 9 (Total);
• procurar_intervalo: é 0 "zero", pois queremos o valor exato da pesquisa.

Baixe as planilhas

PROCV:   Clique aqui para baixar a planilha      
                 VEJA A VIDEO AULA

PROCH:   Clique aqui para baixar a planilha
                 VEJA A VIDEO AULA

SOMASES NO EXCEL

Neste post vamos aprender pra que serve o como usar a função SOMASES do excel.

A função SOMASES é utilizada para soma condicionada, onde podemos somar valores com base em critérios pré estabelecidos. Esta função é similar a SOMASE onde pode-se somar valores com base em apenas um critério, na SOMASES podemos utilizar 127 intervalos e critérios.

A sintaxe da função SOMASES tem os seguintes argumentos:

intervalo_soma. Uma células ou um intervalo para somar, incluindo números ou nomes, intervalos ou referências de célula. Valores em branco e de texto são ignorados.
intervalo_critérios1. O primeiro intervalo no qual queremos avaliar os critérios associados.
critérios1. Os critérios no formato de um número, uma expressão, uma referência de célula ou um texto que define quais células no argumento intervalo_critérios1 serão adicionadas.
Os critérios e intervalos citados acima são obrigatórios os demais são opcionais são opcionais.

No exemplo vamos montar uma planilha para controle de faturamento dos pedidos, onde na plan1 temos todos pedidos com códigos, descrição e quantidade. Na plan2 temos os pedidos faturados, também com pedidos, códigos, descrição e quantidade.

De posse da tabela de dados, vamos motar a fórmula, basta inseir na célula F3 e copiar para baixo




Plan1_Todos os pedidos

 

Plan2_Peiddos faturados

SOMASES

 

Segue o link para Download da planilha montada.

Baixar Planilha

Tutorial em video:
https://www.youtube.com/watch?v=8QoJEXXObwI

OPERADORES DO EXCEL

Operadores de cálculos em fórmulas

Os operadores especificam o tipo de cálculo que você deseja efetuar nos elementos de uma fórmula.
O Microsoft Excel inclui quatro tipos diferentes de operadores de cálculo: aritméticos, de comparação, texto e referência.

1 - Os operadores aritméticos efetuam operações matemáticas básicas, como adição, subtração ou
multiplicação, combinam números e produzem resultados numéricos.

Operador aritmético	Significado	Exemplo
+(sinal de adição)	Adição	4+2
– (sinal de subtração)	Subtração	5-3
* (sinal de multiplicação)	Multiplicação	2*2
/ (sinal de divisão)	Divisão	2/2
% (símbolo de percentagem)	Percentagem	60%
^ (sinal de exponenciação)	Exponenciação	5^2

2 - Os operadores de comparação comparam dois valores e retornam um valor lógico VERDADEIRO ou
FALSO

Operador de comparação	Significado	Exemplo
= (sinal de igual)	Igual a	B3=E3
> (sinal de maior )	Maior do que	B3>E3
< (sinal de menor)	Menor do que	B3<E3
>= (sinal de maior ou igual)	Maior ou igual a	B3>=E3
<= (sinal de menor ou igual)	Menor ou igual a	B3<=E3
<> (sinal de diferente)	Diferente de	B3<>E3

3 - O operador de texto combina um ou mais valores de texto para produzir um único texto.

Operador de comparação	Significado	Exemplo
& (E comercial)	Junta ou concatena dois ou mais valores e produz um único texto	"Ho"&"tel" Retorna Hotel

4 - Os operadores de referência combinam intervalos de células para cálculos

Operador de comparação	Significado	Exemplo
: (dois-pontos)	Operador de intervalo, que produz uma referência a todas as células entre duas referências, incluindo as duas referências	B5:B15
; (ponto-e-vírgula)	Operador de união, que combina diversas referências em uma referência	SOMA(B5:B6;B7:B8)
(espaço simples)	Operador de interseção, que produz uma referência a células comuns a duas referências. Neste exemplo, a célula B7 é comum aos dois intervalos.	SOMA(C5:C12 A5:D5)

Um bom entendimento dos operadores citados acima facilita na hora de montar a fórmula no excel.

CRIAR GRÁFICO DINÂMICO NO EXCEL

Criar gráficos no excel pode parecer difícil, mas não é. Nesta postagem vamos aprender a criar um gráfico dinâmico, que se ajusta conforme a quantidade de valores que temos. Esiste várias maneiras de criar um gráfico dinâmico, a mais comum é através de tabela dinâmica. Neste exemplo usarei funções do excel para redimensionar o gráfico automaticamente.

No exemplo farei um gráfico com as despezas mensais durante um ano.

Primeiro precisamos criar a tabela de dados com os meses e os valores.
Os valores serão inseridos no gráfico através da função DESLOC, para facilitar usaremos uma célula vazia fora da tabela de dados e inserimos a função conforme abaixo. Precisamos uma para o eixo x (horizontal) e outra para o eixo y (vertical).


Função desloc

Onde:
Ref: É a referência das células que contém os valores, deve contemplar inclusive as células que ainda não tem valores, mas terão. Precisamos usar referencias fixas, para isso pressionamos a tecla F4.
Para o eixo x selecionamos as células que conterão os 12 meses, para o eixo y selecionamos as células que conterão os valores referentes aos 12 meses.
Lins: Usaremos zero.
Cols: Usaremos zero.
Altura: Se a tabela de valores for montada na horizontal deixamos vazio, se for na vertical, usamos uma formula que faça a contagem dos valores inseridos.
Altura: Se a tabela de valores for montada na vertical deixamos vazio, se for na horizontal, usamos uma formula que faça a contagem dos valores inseridos.

Agora precisamos nomear a serie com os valores, copiamos ela na barra de formulas e na guia Fórmulas clicamos em Gerenciador de nomes, na janela que abrir selecione novo.

 

 

Na janela que abrir inserimos um nome e no campo refere-se a inserimos a formula que criamos. Faremos para o eixo x e o eixo y.

Na guia inserir, inserimos um gráfico, e com o botão direito sobre o mesmo clicamos em selecionar dados. Na janela que abrir clicamos em adicionar.

Nome da série é o título do gráfico, pode ser uma referencia a uma célula ou um texto.
Valores da série: Iniciamos com =  mais o nome da planilha com a extensão, seguida do ponto de exclamação, mais o nome da série do eixo y, no nosso caso: =Gráfico_Dinâmico.xlsx!Eixo_y

Repetimos o procedimento para o eixo horizontal, clicando em editar e inserindo o nome da série do eixo x.

 

 

Ficará desta forma:

 

Basta clicar em ok que o gráfico estará pronto.

Veja que ao adicionarmos valores o gráfico se ajusta automaticamente.

 

 

 

 

 

 

 

Dúvidas, favor inserir nos comentários.

 

 Segue o link para download da planilha com exemplo.

Gráfico_Dinâmico_Excel

Vídeo aula

O QUE É UMA PLANILHA ELETRÔNICA?

É um sistema constituído por uma tabela, composta de linhas e colunas, em que a interseção é chamada de celula, onde pode-se estipular valores, fórmulas e funções ou textos com possibilidades de serem manipuladas, para a obtenção dos resultados desejados.
À medida que se altera um valor da planilha, todos os cálculos que envolvem o valor alterado serão recalculados automaticamente.