Excel 2007 - Super Dicas - Cálculos com Datas

>

Uma grande funcionalidade do Excel 2007 é trabalhar com cálculos de Datas. São pelo menos 20 funções disponíveis somente para esta finalidade. Vamos usar duas delas para ensinar como se calcula o total de anos e meses entre uma data inicial e uma data final.

No nosso exercício entramos numa coluna várias datas inciais que simbolizam o nascimento de cada membro de uma facção criminosa denominada "Máfia da Vassoura" e noutra coluna a data atual. A idéia é calcular quantos anos e meses tem cada criminoso.

Não me perguntem de onde tirei este nome, apenas vamos ao exercício...

Figura - 01

A Figura 1 apenas mostra que todos os registros relacionados com Datas foram formatados para que se apresentem como tal.

Na próxima figura apresentamos a função HOJE.

Figura - 02

A função Hoje se atualiza automaticamente de acordo com o relógio do seu computador. Esteja certo de que o seu computador esteja com a Data correta antes de usar esta função. Note que como formatei a célula para se apresentar como Data então simplesmente ao digitar =Hoje() o Excel retornou a data em que eu usei esta planilha. O bom de usar esta função é que os cálculos seguintes sempre estarão bem atualizados.

Vamos agora explicar a função FRAÇÃOANO:

Figura - 03

Na figura 3 acima aparece a função da seguinte forma:
=INT(FRAÇÃOANO($F8;$G8))

As duas variáveis usdas na função se referem a Data Inicial e a Data Final. Com estas duas variáveis a função retornará o número e anos percorridos entre a primeira e a última data. Como o próprio nome da função diz o resultado incluirá a fração de anos também. Por esta razão usei a função INT (Inteiro) para gerar apenas a parte inteira do resultado. Assim conseguimos calcular o número de anos que cada membro da Máfia das Vassouras tem.

Na próxima etapa vamos extrair numa célula à parte somente os meses que faltam para completar a idade dos membros.

Figura - 04

A fórmula da figura 4 é semelhante a anterior com uma diferença:
=INT((FRAÇÃOANO($F8;$G8)-$H8)*12)

Ao invés de pegar somente a parte inteira dos anos eu peguei somente a fração. Note que a função FRAÇÃOANO faz uma operação de deduzir a parte inteira do resultado obtido no cálculo anterior. Desta forma sobrará somente a fração dos anos que será multiplicada por 12 para gerar o número de meses. No final par evitar ter meses fracionados, também apliquei a função INT (Inteiro).

Assim conseguimos calcular quantos anos e meses tem cada membro da Máfia das Vassouras. Note que tem gente de todas as idades demonstrando que o clã tem se mantido por várias gerações, rs.

Um grande abraço e até a próxima Super Dica de Excel 2007 do Tio Ilmo.

Assista a série de Video Aulas de Excel 2007 do Tio Ilmo

Logo do Blog Curso Excel Tio Ilmo

Olá amiguinhos,

Investi algum tempo para desenvolver o novo Logo para o nosso Blog.  Veja abaixo como fiou:

Como todo Logo tem uma história por trás, o nosso Logo faz referência às Super Dicas e Super Cases. Observe que é possível ver três letras na formação do Logo, o C (Cases), o D (Dicas) e o S (Super).

De agora em diante a nossa comunicação incluirá este Logo.

Abrrços,

Excel 2007 - Super Case - Tabela de Jogos Dinâmica - Parte 1 (Definição dos Jogos)

Se você não está familiarizado com os comandos do Excel sugiro antes que conheça as Video-Aulas de Excel do Tio Ilmo e as Super Dicas antes de seguir neste Super Case.

Recentemente nós tratamos de um Super Case de Tabela de Jogos simples, que ensinava truques de como gerar confrontos para um campeonato de 10 (dez) times onde todos os times jogam entre si. Agora nós vamos utilizar esta informação para gerar efetivamente uma tabela de jogos dinâmica, com resultados aleatórios que resultam numa Classificação final para um campeonato de futebol. Nada mal para um ano de Copa do Mundo.

Todas as funções utilizadas neste Super Case, já foram expostas em Super Dicas ou nas Vídeo-aulas, sendo assim não vamos tomar tempo explicando o uso das funções, mas sim explicaremos a metodologia usada para resolver este caso. Para quem não conhece os Super Cases, eles são soluções para casos do dia a dia usando o Excel 2007 como ferramenta de suporte.

Vamos aos trabalhos...

Figura - 01

Clique na figura 1 acima e verá qual o objetivo final deste exercício. Queremos criar uma tabela similar aquelas usadas em campeonatos de futebol, porém com diversos recursos dinâmicos que permitirão simular os resultados dos jogos, além de estabelecer a classificação final dos times em disputa respeitando critérios de desempate no mesmo formato de campeonatos disputados no Brasil.

Fugura - 02

A figura 2 acima mostra a tabela de confrontos entre os times explicada no Supercase Tabela de Jogos.

A primeira missão a ser feita é criar uma lista de confrontos a partir desta tabela o método para criar esta lista de confrontos é o uso do Colar Especial Transpor já exposto numa Super Dica recentemente.

Veja como fica esta primeira parte na figura 3 abaixo:

Figura - 03

Este é o resultado de um Colar Especial Transpor, porém se quiser também pode estabelecer um vínculo desta lista com a tabela de confrontos, que aliás foi isto que fiz neste caso, visto que a lista não é tão grande assim, pois tem um total de 45 confrontos nas 9 rodadas do campeonato. Como tenho um bom conhecimento do sincronismo do Excel no uso de Copiar e Colar, eu não precisei fazer manualmente cada um dos 45 vínculos dos confrontos. Eu fiz apenas os cinco primeiros e depois com copiar e colar simples aliado a uma fixação de célula em coluna o trabalho foi bem facilitado. Estes truques não se explicam se descobrem com o uso contínuo do Excel.

Figura - 04

A figura 4 mostra a próxima etapa que é definir os Jogos em Casa e Fora de Casa. Usei a função EXT.TEXTO para isto, fazendo referência a coluna D onde estão os jogos.

Note que a cada cinco jogos, ou seja, a cada rodada, eu inverto a posição do jogo em Casa e Fora, bastando adequar a fórmula de extração de texto. Esta operação é necessária senão beneficiaríamos alguns times por jogarem muito mais vezes em casa do que os outros. O resultado final vai deixar a metade dos times com 5 jogos em casa e a metade com 4 jogos em casa, sendo que cada time joga um total de nove vezes. Num campeonato oficial de 9 jogos algum critério deve ser definido para que um time jogue mais vezes em casa do que outro, porém no nosso exercício usamos uma regra simples para fechar a gama de jogos da tabela beneficiando 5 dos 10 times com um jogo a mais dentro de casa.

Como este Super Case é longo vamos didiví-lo em partes.

Acompanhe a segunda parte onde trataremos dos Gols marcados e sofridos pelos times usando a função ALEATÓRIO já explicada numa Super Dica recentemente.

Clique Aqui para Próxima

Assista a série de Video Aulas de Excel 2007 do Tio Ilmo

Excel 2007 - Super Case - Tabela de Jogos Dinâmica - Parte 2 (Gols)

Nesta segunda etapa do nosso Super Case Tabela de Jogos Dinâmica, vamos aprender como gerar o resultado dos jogos em Gols marcados e sofridos.

Tanto para os Gols Pró, quanto para os Gols Contra, nós usamos a função ALEATÓRIOENTRE para gerar aleatoriamente o total de gols. Definimos uma variação entre 0 e 6 gols incialmente conforme mostra a figura 5 abaixo:

Figura - 05

Ao usar este intervalo de possíveis gols de 0 a 6, muitos resultados um tanto improváveis apareceram na lista de jogos. Inclusive neste exemplo aparece um sonoro 6x6, além de vários jogos com placares bastante elásticos para o padrão de resultados normais do futebol. Para dar mais realismo a estes resultados criei uma coluna adicional na lista para corrigir um pouco esta discrepância em relação a realidade.

Veja como ficou na figura 6 abaixo:

Figura - 06

Note que criei uma fórmula condicional onde caso o número obtido na simulação anterior for maior ou igual a 4 que deste resultado seja subtraído um número aleatório entre 0 e 3. Isto corrige um pouco a discrepância gerando resultados mais realistas com os placares do Futebol sem deixar que goleadas ocorram de vez emquando. Compare as duas colunas e veja a diferença entre elas.

A função condicional SE usada neste exercício já foi exposta nas Vídeo-Aulas do Tio Ilmo. Não deixe de rever como ela é usada caso tenha dúvida.

Na próxima etapa vamos mostrar como classificamos os resultados entre Vitória, Empate e Derrota e também a definição dos pontos obtidos pelos times conforme o resultado obtido.

Clique Aqui para Próxima Etapa

Assista a série de Video Aulas de Excel 2007 do Tio Ilmo

Excel 2007 - Super Case - Tabela de Jogos Dinâmica - Parte 3 (Classificação dos Resultados e Pontos)

Nesta etapa vamos mostrar como chegamos a classificação dos resultados em Vitória, Empate e Derrota, bem como o cálculo dos Pontos.

A figura 7 mostra como classificamos as Vitórias usando uma função condicional SE.

Figura - 07

Note que a codicional compara o resultado de gols marcados e sofridos dentro de casa. Se o total de gols marcados for maior que os sofridos ele retorna S (Sim) confirmando a vitória, do contrário ele retorna N (Não).

A coluna ID ao lado apenas cria uma identificação do código do time mais o sinal obtido na função da coluna Vitória. Esta identificação vai auxiliar na consolidação final da tabela de resultados. Foi usada uma concatenação para juntar a informação da coluna E com a coluna K.  O primeiro jogo deu "01S" porque o time de código "01" venceu o jogo.

Veja a figura 8 abaixo para saber como definimos a coluna de empate.

Figura - 08

A função condicional SE neste caso compara os gols marcados com os gols sofridos e caso sejam iguais retornam como resposta o "S", do contrário o "N". Muito simples de entender.

Vamos a colunda da derrota que é mais interessante:

Figura - 09

Agora usamos um truquezinho esperto pra resolver a coluna de Derrotas. Note que na condicional SE é feita a leitura das colunas de Vitórias e Empates. Se ambas tiverem a resposta "N", logo a coluna derrota será "S". Para isto foi usada dentro da fórmula condicional SE uma concatenação do resultado das colunas Vitória e Derrota criando o código "NN". Se o resultado for verdadeiro o time foi derrotado. Voilá!!!!

Para finalizar vamos aos Pontos. A condicional é relativamente simples mas é composta de duas condicionais uma dentro da outra.

Veja a figura 10:

Figura - 10

A figura 10 mostra a condicional que primeiro analisa se na coluna Vitória tem a letra "S", se for verdadeira lance 3 pontos. Logo em seguida caso a primeria condição não seja atendida é analisada a coluna de Empate e caso esta também não tenha o "S" que confirma o emapate ela retorna Zero que é a pontuação dada pela derrota. Exatamente igual a pontuação usada em campeonatos de futebol no Brasil.

Estas etapa do exercício cobriu os resultados dos times em Casa, mas a mesma operação foi repetida para os resultados Fora de Casa, o que dispensa sua explicação.

Na próxima Etapa deste Super Case vamos demonstrar estes resultados num resumo que já vai dar suporte a tabela final de classificação. Fique ligado.

Clique Aqui para a Próxima Etapa

Assista a série de Video Aulas de Excel 2007 do Tio Ilmo

Excel 2007 - Super Case - Tabela de Jogos Dinâmica - Parte 4 (Resumo dos Resultados)

Nesta etapa vamos usar bastante as funções SOMASE e CONT.SE que já foram explicadas nas Vídeo-Aulas e Super Dicas.

O objetivo agora é montar uma tabela resumida com os resultados consolidados de cada time.

Vamos a figura 11:

Figura - 11

Na coluna Pontos deste Resumo foi usada a função SOMASE para somar os pontos obtidos em casa e fora pelos times. Se você já é familiarizado com esta função não terá dificultade de interpretá-la, pois a única coisa que foi feito de diferente foi somar duas operações numa mesma célula. Caso tenha dúvida como usar a SOMASE recorra as Vídeo-Aulas do Tio Ilmo ou as Super Dicas.

Veja como consolidamos o total de Vitórias de cada time na figura 12 a seguir:

Figura -12

Note que agora vamos usar como base a coluna ID criada no passo anterior para identificar as vitórias na lista de dados. Se você retornar a etapa anterior, vai notar que na coluna ID existe uma junção entre o código do time e o sinal "S" de Vitória ou "N" que diz que não venceu. O primeiro time caso seja o vencedor de uma partida terá o código "01S" e toda vez que a função de contagem encontrar este evento na lista de dados ela vai somar mais uma vitória para este time.

Veja na figura 13 como foi computado o total de empates:

Figura - 13

Note que apesar de simples é bem esperta esta fórmula, pois ela se vale de resultados já disponíveis na tabela para definir o seu resultado por eliminação. Se o total de pontos do primeiro time é 10, logo o total de empates é 10 menos os pontos das vitórias deste time, no caso 3x3=9, já que os empates concedem 1(um) ponto por jogo o resultado será 1 (um) empate. Interessante não?

O total de derrotas também ocorre por eliminação. Se o total de jogos é 9 e o primeiro time da lista teve 3 vitórias e um empate, logo ele sofreu 5 derrotas. Muito básica esta.

Os Gols Pró e Contra seguem a mesma lógica da função condicional usada na coluna de Vitórias e o saldo de gols é a simples diferença entre os gols Pró e Contra. Estas variáveis não precisam de muita explicação, já sua operação é similar a outras já explanadas neste exercício.

Na próxima etapa vamos usar uma Macro simples para copiar esta tabela para uma área livre da planilha e usar o recurso de classificação de Dados do Excel 2007 para ordenar segundo critérios pré definidos qual a classificação dos times após estes resultados.

Clique Aqui para a Próxima Etapa

Assista a série de Video Aulas de Excel 2007 do Tio Ilmo

Excel 2007 - Super Case - Tabela de Jogos Dinâmica - Parte 5 (Classificação dos Times)

Nesta última etapa, vamos usar uma macro simples acionada por um botão para gerar o resultado da classificação dos times no campeonato ao final das 9 partidas.

O uso de Macros básicas foi explanada nas Vídeo-Aulas do Tio Ilmo. Assista a aula específica sobre Macros caso não esteja familiarizado com esta operação.

Fugura - 14

A figura 14 acima, mostra que uma réplica da tabela do Resumo de Resultados a qual foi feita logo abaixo da original, porém a cópia foi feita sem fórmulas e em seguida os dados foram ordenados seguindo os critérios de desempate do campeonato.

Toda esta operação foi feita automaticamente com a Macro acionada pelo botão Ordenar.

Note que somente o conteúdo dos valores foi copiado de tal forma que não sobreponha os títulos da tabela destino que é ligeiramente diferente da origem.

O comando classificar dados do Excel 2007 permite uma série de critérios que devem ser respeitados sequencialmente. Veja na figura 15 abaixo como os critérios foram construídos para que a tabela final ficasse ordenada de tal forma que respeitasse os critérios do campeonato.

Figura - 15

Note que os critérios seguem uma sequência idêntica ao regulamento exigido no campeonato. Se você não estiver familiarizado com o uso do comando Classificar do Excel 2007 assitas as Vídeo-Aulas do Tio Ilmo.

Este é o resultado final do nosso Super Case Tabela de Jogos Dinâmica.

Para retornar as etapas anteriores siga os links abaixo:

Etapa 1
Etapa 2
Etapa 3
Etapa 4


Assista a série de Video Aulas de Excel 2007 do Tio Ilmo

Excel 2007 - Super Dicas - Função Aleatório

A função ALEATÓRIO do Excel é muito utilizada quando você quer obter dados fictícios dentro de uma gama de dados específica. O Excel 2007 provê duas funções para esta operação:

ALEATÓRIO e ALEATÓRIOENTRE

A função ALEATÓRIO na sua configuração padrão retorna um número entre 0 e 1 incluindo as decimais. O formato padrão é assim: ALEATÓRIO( )

Se você fizer a operação ALEATÓRIO( )*10 retornará um número entre 0 e 10, ou seja, o algarismo que você multiplicar por esta função será o teto dos resultados possíveis.

Se sua planilha estiver com Cálculo Automático, sempre que fizer qualquer mudança na planilha o cálculo será refeito gerando outro número. Se quiser fixar um determinado resultado, use a opção de Cálculo Manual e acione o F9 quando quiser mudar resultado. Assim você tem um maior controle sobre os resultados.

Já o ALEATÓRIOENTRE é mais específico e como o nome diz (ENTRE), ele requer duas variáveis para fornecer um resultado, o menor número e o maior número a ser gerado aleatoriamente. O retorno será sempre números inteiros.

Exemplo:
=ALEATÓRIOENTRE(1,15) - vair retornar um número inteiro entre 1 e 15.

O uso destas funções integradas a outras funções do Excel 2007 oferece um excelente recurso de geração de dados aleatórios.

Logo estarei divulgando um Super Case que demonstrará de forma prática como ela pode ser útil em situações do dia a dia.

Até lá...

Excel 2007 - Super Dicas - Colar com Transposição

O comando Colar Especial com Transposição é muito simples de usar, porém muito útil em determindas situações.

A idéia é copiar dados que estejam dispostos em linha (na horizontal) e ao colar colocá-los na disposição de colunas (vertical) e vice e versa.

Vamos a figura 1 para entender melhor:

Figura - 01

Na figura 1 acima a intenção é copiar o conteúdo das células D21:H21 e colar na área D34:D38.
Embora as duas áreas tenham a mesma quantidade de células, uma está disposta na posição horizontal e a outra na vertical. O Colar Especial Transpor resolve esta questão, veja como é feito:

Figura - 02

Observe que na janela de Colar Especial duas condições foram selecionadas, uma é a opção Valores e a outra mais abaixo é a Transpor.

Veja o resultado após confirmar:

Figura - 03

Lembre-se de antes de cofirmar o colar de estar com o cursor posicionado corretamente no destino da cópia.
Veja finalmente que a idéia neste exercício é criar uma lista de dados extraindo da tabela as informações necessárias posicionando verticalmente na lista.

Figura - 04

Criar Listas de dados é particularmente importante para uso em Bancos de Dados.
Continue curtindo as nossas Super Dicas e até a próxima.

Excel 2007 - Super Dicas - Inserir e Editar Imagens

>

Eu sou um cara vidrado em recursos de Computação Gráfica especialmente no campo do 3D. Eu até mantenho um Blog sobre Blender 3D, caso vocês queiram conhecer é o Blender Maniacs. Foi com grata surpresa que descobri que o Excel 2007 tem alguns recursos de edição de imagem dignos de uma simplória comparação com um software de Computação Gráfica.

Então resolvi criar esta Super Dica sobre edição de Imagens dentro do Excel 2007. Você vai ver como é fácil transformar uma simples imagem basicona em algo bem chique dú úrtimo, rs.

Vamos a primeira figura para você entender do que eu estou falando:

Figura - 01

Selecione um lugar na sua planilha para inserir uma imagem e entre no Menu Inserir Imagens e comece o trabalho a partir de uma imagem bem simples. No meu caso estou usando o Logo oficial do Tio Ilmo.

Figura - 02

Agora vamos transformar esta figura em algo mais bunitcho usando exclusivamente o Excel 2007 para isto.

Figura - 03

Mantendo a imagem selecionada no Menu Formatar, com apenas um click em uma das várias opções de Estilos de Imagens disponíveis na Faixa de Opções, você transforma sua imagem tosca e 2D em algo sensacional em 3D. Mas a coisa não pára por aí não. O Excel 2007 te permite editar detalhadamente esta imagem tendo assim total controle sobre o formato, cor, sombra etc...

Acompanhe o próximo passo...

Figura - 04

Bastou mais um clique no Menu Forma de Imagem e ao escolher umas das dezenas de formas, meu logo que era uma pastilha 3D se tornou num incrível raio 3D. Não é sensacional?

Mas a coisa não para por ai...

Figura - 05

Mais um clique numa das várias opções de Borda de Imagens e obtive uma belo efeito de sombra em vermelho. Isto tá ficando show, não é?

Vamo que vamo gente ...

Figura - 06

E pra finalizar ao abrir o Menu de Efeitos de Imagem você vai ter um "OM" (prazer enorme) ao saber que ali estão tantas opções de formatação que vai deixar muito software de edição de imagens com inveja. O Excel 2007 deu um passo gigantesco neste quesito tornando tão fácil a edição de imagens que qualquer artista medíocre como eu vai se sentir o próprio Picasso.

Depois de todas as melhorias feitas em sua imagem, copie a sua arte para um editor básico de Imagens como o Paint do Windows e a salve no formato JPG ou outro que preferir.

Veja como ficou minha imagem final após 2 minutos de edição:

Figura - 07

Valeu pessoal e não deixe de curtir todas as Super Dicas do Excel 2007.

Assista a série de Video Aulas de Excel 2007 do Tio Ilmo

Excel 2007 - Super Dicas - Validação de Dados Dinâmica

Se você é novato por aqui assista antes as Vídeo-Aulas do Tio Ilmo, depois volte rapidamente para detonar esta e outras Super Dicas.

No exercício de hoje é fundamental que você já conheça e use com maestria as Super Dicas a seguir antes de começar este exercício:

Definir Nome com Fórmula Embutida
Funções de Endereçamento
Funções de Texto
Endereço Dinâmico
Validação de Dados Simples
Função Deslocamento
Função Contar Valores
Função Indireto

Não se aventure a seguir este exercício se não tiver pleno conhecimento das Super Dicas acima mencionadas, pois você vai literalmente pirar, rs.

Vamos aos trabalhos:

A idéia neste exercício é dinamizar a validação de dados da célula B11 de tal forma que ao escolher uma unidade da federação em B8 (cuja célula já tem uma validação de dados simples) a lista de cidades possíveis seja dinamicamente escolhida entre as três UFs disponíveis, ou seja, se a UF escolhida for SP que somente as cidades de SP sejam válidas na célula B11 e da mesma forma para as outras duas unidades da federação RJ e MG.

Figura - 01

Na figura 1 é possível ver no Gerenciador de Nomes que criamos três nomes com fórmulas embutidas. COLUNA, Dados e DESLOC_COLUNA.

Vamos expor cada um dos três nomes agora:

Figura - 02

Ao ampliar esta figura 2 você vai notar que uma função gigantesca foi agregada na caixa Refere-se a do nome COLUNA.

Esta função já foi exposta na Super Dica Endereço Dinâmico. Visite esta Super Dica caso tenha dúvidas na interpretação desta tripa de dinossauro,rs.

A função já era grande no exercício original e agora dobramos o seu tamanho para poder colocar o sinal de ":" entre duas delas.

O resultado é uma simples área de colunas com três possibilidades por ser dinâmica: $D:$D, $E:$E e  $F:$F dependendo do que for digitado na célula B8.

Chamamos esta área de COLUNA por que conforme a o resultado, a nossa validação de dados vai se basear nela para limitar as Cidades da UF correspondente.

O segundo nome definido com fórmula é o DESLOC_COLUNA.

Figura - 03

Na figura 3 acima você encontra a função CORRESP usada dentro do nome DESLOC_COLUNA.

Esta função também já foi exposta em detalhes na Super Dica Funções de Endereçamento.

A finalidade aqui é definir quantas casas serão deslocadas à direita deste endereço. O resultado vai depender na informação digitada na célula B8. Poderá ser 0, 1 ou 2.

Finalmente o terceiro nome DADOS tem uma função DESLOC que utiliza na sua composição os nomes COLUNA e DESLOC_COLUNA coforme aparece na figura 4 abaixo:

Figura - 04

A função DESLOC foi exposta em detalhes na Super Dica Função Deslocamento .

O interessante nesta composição é a capacidade do Excel incorporar numa fórmula outras fórmulas complexas e ainda atribuir a ela um simples nome DADOS.

Este nome DADOS agora será utlizado numa validação de dados cujo objetivo é dinamizar os dados válidos da célula B11 que lista as Cidades da UF escolhida na célula B8.

Veja como foi construída a Validação de Dados da Célula B11 na figura 5 abaixo:

Figura - 05

Quem olha na figura 5 o conteúdo da caixa Fonte dos dados vai enxergar apenas a expressão =Dados. Na aparência é algo simples, porém por trás desta expressão tem uma enormidade de fórmulas complexas que creditaram a célula B11 o dinamismo que ela exige.

Agora conforme a informação digitada na célula B8, a Validação de Dados da célula B11 será dinâmica, limitando a esta célula somente as cidades relacionadas com a UF escolhida na célula B8.

É simplesmente fantástica a capacidade do Excel 2007 de resolver problemas complexos com soluções conhecidas. A diferença neste exercício é a maestria usada ao juntar várias funções básicas formando uma "melodia suave e harmônica" onde a "orquestra de funções" foram regidas de forma singular pelo maestro que é você usuário de Excel.

Eu sempre afirmo aos meus alunos, que o segredo não é ser programador de VBA, mas sim um maestro no uso de funções básicas do Excel.

Continue curtindo as nossas Super Dicas e Super Cases.

Excel 2007 - Super Dicas - Função Indireto

Se você é novato por aqui assista antes as Vídeo-Aulas do Tio Ilmo, depois volte rapidamente para detonar esta e outras Super Dicas.

A função INDIRETO como o nome diz tem o objetivo de retornar o conteúdo que uma outra célula faz referência.

Veja a figura 1 para ficar mais clara esta definição:

Figura - 01

Observe que a função direto faz referência a célula D19, cujo conteúdo é um outro endereço, ou seja, $E$8. A célula E8 contém o nome de uma cidade da UF RJ que é Rio de Janeiro.

A função INDIRETO, como o nome diz, buscou indiretamente o conteúdo da célula E8 através da célula D19.

Em resumo é só esta a sua função. O seu uso se torna mais sofisticado quando é usada em conjunto com outros recursos do Excel.

Veja bacana fica esta sequência de recursos do Exel 2007 usando recursos básicos em conjunto:

Primeiro vamos Definir um nome para uma área com fórmula:

Este recurso já foi explicado na Super Dica Definir Nome com Fórmula.

Figura - 02

Na figura 2 está demosntrada a definição do nome RJ para a área onde estão as cidades da UF RJ que é E8:E15.

Agora vamos juntar duas funções para aumentar o dimamismo:

Figura - 03

Na figura 3 acima você nota que a função CONT.VALORES abrigou em seu conteúdo a função INDIRETO que por sua vez usou como endereço um nome definido como "RJ".

A função INDIRETO interpretou o conteúdo do nome "RJ" que é a área E8:E15.

A função CONT.VALORES contou quantos eventos tem nesta área e retornou o resultado 8.

Ainda é possível sofisticar  ainda mais esta operação, mas isto fica para uma próxima Super Dica.

Abraços,

Excel 2007 - Super Dicas - Função Contar Valores

Se você é novato por aqui assista antes as Vídeo-Aulas do Tio Ilmo, depois volte rapidamente para detonar esta e outras Super Dicas.

A função CONT.VALORES do Excel tem como objetivo contar os eventos contidos numa determinada área da sua planilha. Se for definir uma coluna inteira como área o resultado será a contagem de todas as células que tiverem dados naquela coluna. O mesmo vale quando escolher contar o conteúdo de uma linha inteira. Você pode restringir uma área específica da planilha definindo exatamente a área onde está o conteúdo a ser contado. As células da área selecionada que estiverem vazias, não serão contadas.

No nosso exemplo vamos contar todas as células que contenham dados na coluna F de nossa planilha. Veja como fica a função CONT.VALORES na figura 1 abaixo:

Figura - 01

Note que a área se limita a coluna F, ou seja, $F:$F. Não colocamos o número das linhas para que a função conte todos os dados desta coluna. O resultado é 9 (nove) por que somou do título da UF MG até a última cidade da lista (Uberaba) e encontrou 9 eventos.

Se quisermos contar apenas as cidades a operação é simples, basta subtrair um da fórmula.
Veja como ficaria na figura 2 abaixo:

Figura - 02

Ao subtrair 1 (um) do total da contagem o resultado será o total de Cidades desta lista. O interessante desta função é o seu dinamismo, pois se acrescentarmos mais cidades a lista ela aumentará a contagem proporcionalmente aos novos eventos digitados.

Na sequência de nosso exercício, vamos juntar esta função a outras funções para obter uma fórmula complexa. Continue acompanhando as nossas Super Dicas.

 Até lá...

Excel 2007 - Super Dicas - Função Deslocamento

As Super Dicas de Excel 2007 pressupõem que você já tenha algum conhecimento de Excel. Se por acaso você é um usuário iniciante estude as Video Aulas de Exel do Tio Ilmo antes de seguir neste estudo.

A função DESLOC do Excel é classificada com função de Pesquisa e Referência. Isto por que ela busca um valor com base no deslocamento utilizando 5 variáveis.

A primeira variável é a célula de referência, o ponto de partida. Neste exercício será a célula D8.
A segunda variável indica numericamente o deslocamento em linhas.
A terceira variável indica numericamente o deslocamento em colunas.
A quarta variavel indica numericamente a altura deste deslocamento.
A quinta variável indica numericamente a largura deste deslocamento.

Vamos ao exemplo prático:

Parte - 01

A montagem da fórmula indica a célula D8 com nenhum deslocamento nem em linha nem em coluna, ou seja retornou o conteúdo da própria célula D8 que é a cidade de Campinas abaixo da coluna da UF SP.

Figura - 02

Já a figura 2 mostra o deslocamento de linha em 2 posições e o deslocamento em coluna em 2 posições o que resultou no conteúdo da célula F10, a cidade de Uberlandia abaixo da coluna da UF MG.

Se o ponto de partida é a célula D8, dois avanços para a direita levam a célula F8, mais dois avanços para baixo levam a célula F10. Acho que está claro agora.

Figura - 03

Na figura 3 observamos o uso da função DESLOC dentro de uma validação de dados para a célula F17.

Esta validação lista todas as opções que a função DESLOC determinou.

Ponto de partida na célula D8, deslocando zero posições em linhas, 2 posições em coluna e com uma altura de 8 deslocamentos, ou seja, ela vai tomar todas as Cidades da coluna da UF MG e alimentar como opções de dados válidos para serem alimentados na célula F17. Não há deslocamento em Largura neste exemplo.

Note que a função DESLOC proporcionou um nível bem mais sofisticado ao ser usado como parâmetro de FONTE na Validação de Dados.

Nos próximos exercícios vamos sofisticar ainda mais este recurso. Não perca as próximas Super Dicas.

Excel 2007 - Super Dicas - Validação de Dados Simples

Olá amiguinhos,

Se você é novo por aqui conheça as Vídeo-Aulas de Excel do Tio Ilmo primeiro, depois retorne para estudar esta e outras Super Dicas.


Hoje vamos estudar como criar uma validação de dados simples. A idéia é que sejam limitadas as entradas numa determinada célula.

Veja na figura 1 onde se encontra este recurso:

Figura - 01

Observe que no Menu Dados existe um botão na faixa de opções chamado Validação de Dados. Ao clicar neste botão ele abre uma janela com a opção de cadastrar uma validação de dados para impedir que dados não constantes nesta regra sejam inseridos numa determinada célula. No caso queremos limitar a entrada de dados na célula B8 para que somente as três unidades da federação SP,RJ e MG sejam digitadas nesta célula.

Veja na figura 2 como isto é feito a partir desta seleção:

Figura - 02

A primeira coisa a fazer antes de acionar este comando é posicionar o curso na célula que receberá esta validação de dados. Neste caso é a célula B8.

Após clicar no Menu de Validação de Dados aparece uma janela onde se deve fornecer dois critérios de validação na aba Configurações:
1 - Na caixa Permitir é colocado o tipo de validação que neste caso é uma lista.
2 - Na caixa Fonte é colocada a área onde se encontram os dados válidos. Neste caso são os titulos da tabela de Cidades onde aparecem as três UFs, SP, RJ e MG. Esta informação como aparece na figura está na área $D$7:$F$7.

Agora veja o efeito na figura 3 abaixo:

Figura - 03

Note que ao tentar digitar uma informação na célula B8 aparece uma seta à direita da célula que te permite escolher entre as opções de dados válidas. Neste caso são as três UFs válidas, SP,RJ e MG.

Qualquer outra informação que se tentar entrar nesta célula não será aceita, pois este comando limita os dados a estas opções.

Esta é a forma básica de usar uma validação de dados, mas vamos ver como dinamizar esta função do Excel tornando a Validação Dinâmica num próximo exercício.

Assista a série de Video Aulas de Excel 2007 do Tio Ilmo

Excel 2007 - Super Dicas - Endereço Dinâmico

Se você não acompanhou este exercício desde o ínício minha sugestão é que veja a explanação das Super Dicas Funções de Endereçamento e Funçoes de Texto. É fundamental que acompanhe desde o ínicio este exercício para que você tenha plena compreensão deste exercício.

Nesta Super Dica Endereço Dinâmico, vamos juntar todo o aprendizado das duas primeiras Super Dicas e formar o resultado final proposto que é ter um endereço dinâmico.

Vamos a fugura 1:

A junção das quatro funções aprendidas nas Super Dicas anteriores gerou um verdadeiro monstro, uma tripa enorme, uma fórmula de difícil interpretação.

Se você não se sente confortável com fórmulas complexas, sugiro você assistir as vídeo-aulas do Tio Ilmo. Depois que estiver treinado, retorne para este exercício.

Vamos interpretar o monstro,rs:
=EXT.TEXTO(ENDEREÇO(8;4+CORRESP($B$8;$D$7:$F$7;0)-1);1;NÚM.CARACT(ENDEREÇO(8;4+CORRESP($B$8;$D$7:$F$7;0)-1))-2)

Nota-se que a função que abriga todas as outras é a função EXT.TEXTO e por esta razão será a mais difícil de entender. Sendo assim vamos lembrar de como ela é construída:

A função EXT.TEXTO tem 3 variáveis separadas por ponto e vírgula. Vamos ver cada uma destas variáveis separadamente extraindo do estômago do monstro cada parte que ele engoliu:

Variável 1:
ENDEREÇO(8;4+CORRESP($B$8;$D$7:$F$7;0)-1)

A primeira variável indica o endereço a ser pesquisado pela função EXT.TEXTO. Usamos duas funções para dizer qual é este endereço e a razão é simples, dar dinamismo a função. O que poderia ser simplesmente uma célula se tornou um monstrinho dentro do monstrão.

Note que a função ENDEREÇO tem em seu conteúdo duas variáveis, linha e coluna, sendo que a segunda que define o número da coluna foi dinamizada com a função CORRESP.

Como estas funções já foram explicadas nas Super Dicas anteriores, sugiro que você volte até elas para entender como elas funcionam detlhadamente.

Note que este endereço varia de acordo com a informação digitada na célula B8.

Variável 2:
Note que após a primeira separação de ponto e vírgula aparece o número 1, que significa que a extração começa no primeiro caracter do conteúdo do endereço. Esta foi fácil.

Variável 3:
A variável 3 como já foi explicada na Super Dica sobre Funções de Texto indica o último caracter a ser extraído do conteúdo do endereço indicado na variável 1.

NÚM.CARACT(ENDEREÇO(8;4+CORRESP($B$8;$D$7:$F$7;0)-1))-2

A função NÚM.CARACT calcula este número que por sua vez usa duas funções para gerar dinamicamente esta variável, são elas as funções ENDEREÇO e CORRESP já explicadas na Super Dica Funções de Endereçamento.

Recapitule estas três funções para entender este resultado.

Quando fragmentada, a função EXT.TEXTO que virou um monstro neste exercício, fica mais fácil de entender, mesmo que ela seja composta de várias outras funções. Esta é a grande sacada do Excel. Poder juntar várias funções em uma só, proporcionando assim o dinamismo necessário.

O objetico deste endereço dinâmico é gerar a área da coluna que iremos usar numa Validação de Dados de nível mais sofisticado.

Acompanhe a sequência desta série de Super Dicas para entender o final da história.

Excel 2007 - Super Dicas - Funções de Texto

Para dar sequência a série iniciada com a Super Dica Funçoes de Endereçamento vamos agora às Funções de Texto trabalhando com o mesmo exercício.

A primeira delas e mais popular é a EXT.TEXTO. Esta é uma das funções mais úteis na construção de títulos dinâmicos no Excel 2007. A grande vantagem desta função é sua capacidade de extrair parte dos dados de uma determinada célula e juntá-la a outras partes formando um texto dinâmico.

Observe a figura abaixo e vamos a um exercício prático:

Figura - 01

Vamos analisar a montagem destas fórmula:
1 - A primeira variável é o endereço de onde se quer extrair um determinado texto ou parte dele. Neste exemplo vamos buscar o conteúdo da célula D19 que é $E$8.
2 - A segunda variável mostra em qual caracter do conteúdo da célula D19 começa a extração. Neste exemplo vai começar do primeiro caracter, ou seja, do cifrão à esquerda da letra E.
3 - A terceira e última variável é qual o último caracter a ser extraído. Neste exemplo vamos extrair até o segundo caracter, ou seja, a letra E. O restulado será $E.

A ideía nesta fórmula é extrair o começo do resultado constante na célula D19. A intenção final aqui é ter somente a letra da coluna e não o número da linha deste endereço obtido no exercício anterior com as funções ENDEREÇO e CORRESP.

Agora vamos dinamizar um pouco mais...

Veja como usar a função NÚM.CARACT.

Figura - 02

A construção da função NÚM.CARACT é muito simples. Ela simplesmente toma o endereço de uma célula para contar quantos caracteres tem seu conteúdo. Neste caso o conteúdo analisado é o da célula D19 que armazena a informação $E$8. São quatro caracteres. Note que ao final da função NÚM.CARACT eu subtrai 2  e por esta razão o resultado foi 2 ao invés de 4.

Se o conteúdo fosse $EE$8 o resultado seria 3, por que 5-2=3.

Agora vamos juntar as duas funções aprendidas nesta Super Dica.

Veja a figura 3 abaixo:

Figura - 03

Ao juntar as duas funções, a segunda variável da função EXT.TEXTO se tornou dinâmica. A vantagem deste dinamismo é que quando o conteúdo original tiver 5 caracteres ou mais, a extração sairá correta, pois a idéia é extrair os dados da letra do endereço e despresar o número a linha. Quando o endereço da coluna tem duas letras a função também cumpre o seu papel, enquanto sem este dinamismo ela não extrairia as duas letras necessárias para identificar o endereço da coluna. Esta é a razão de juntarmos as duas funções criando este dinamismo.

Note na figura 3 a diferença dos resultados quando usamos a função EXT.TEXTO isolada e quando juntamos as duas funções.

Ainda vamos dar sequência neste exercício com novas funções relacionadas ao mesmo objetivo. Não percam a sequência.