Curso Capacitação da Ufms
Anexos
Excel 2007
Curso Capacitação da Ufms
Anexo1
Subtotais
Pode-se destacar duas formas de fazer subtotais, sem uso de tabelas
dinâmicas. A Primeira, você vai fazer dois passos, classificar e depois o
subtotal.
Tomando a tabela abaixo como referência, classifica-se a coluna desejada para
subtotal. A referência para o exemplo será a CIA AÉREA.
Clique em Classificar e Filtrar. Selecione a primeira opção da classificação (de
A a Z). uma nova tela será apresentada para selecionar Expandir a seleção. Se
estiver selecionada a expansão, deixe pois a classificação não será APENAS
para a coluna marcada, mas para toda a tabela. Se não fizer isto apenas a
coluna marcada será ordenada e o restante não alternado ficando toda a
planilha com valores errados. Bem, mas clique em Classificar e toda a tabela
será classificada.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 1
Curso Capacitação da Ufms
Isto feito clique, neste caso, na célula A1 e clique na aba Dados, Subtotal e
uma nova janela se abrirá.
Nesta você selecionará o que deseja para os subtotais. Neste caso quero saber
quantos passageiros por Cia serão embarcados. Leia tudo atentamente antes
de começar a configurar. Então, a primeira opção coloque Cia aérea, usar a
função Somar, pois desejo o número total de passageiros e os subtotais na
coluna Núm.pessoas.
Pronto os subtotais estão criados.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 2
Curso Capacitação da Ufms
Você pode controlar como estes devem aparecer, basta clicar nos indicadores
de no canto esquerdo em cima (1, 2 e 3). São os indicadores de níveis de
subtotal. Se clicar no número 2, para este caso, somente os subtotais
aparecerão.
Se clicar no 2 na altura da linha 7, no símbolo + somente o subtotal da Cia AA
será apresentada com detalhes.
Se clicar no nível 1 somente o total geral aparecerá.
O anexo 2 mostra como podem ser copiados somente os subtotais.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 3
Curso Capacitação da Ufms
Para fazer algo mais completo, deseja-se saber quantos passageiros serão
transportados e em quantas vendas de passagens. Como já foi feito a primeira
parte, número de passageiros, deve ser incluído a segunda parte.
Deve ser configurado da seguinte forma, Cia aérea, ContNúm, Cia aérea,
desmarca Substituir subtotais atuais e OK.
Na célula G8 ainda coloquei um cálculo. Note que agora a planilha tem 5 níveis
de subtotais.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 4
Curso Capacitação da Ufms
Outra alternativa
A outra alternativa parece mais simples, mas oferece menos recursos e
resultados.
Utilizando a mesma planilha, faça o seguinte:
Na aba Inserir, clique em Tabela, ou seja, inserir uma tabela. Esta tabela será
selecionada e você deve confirmar.
Veja a figura acima, o Excel marcou a tabela e identificou que a primeira linha
é um cabeçalho.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 5
Curso Capacitação da Ufms
Clique OK e pronto a tabela foi criada.
Quando criada, uma nova aba também é acrescentada, Design. Esta vai
auxiliar no trabalho com a tabela. Veja que existe uma parte desta que é
chamada de Opções de Estilo de Tabela. Nesta estão selecionadas as opções
Linha de Cabeçalho, Linhas em tiras, que é o efeito de uma linha mais escura
(azul) e outra mais clara (azul claro). Além disto, o cabeçalho tem os filtros.
E os totais?
Marque Linhas de Totais e ao final de cada coluna uma célula será acrescida
com uma seta.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 6
Curso Capacitação da Ufms
Clique nesta você seleciona a operação que deseja fazer, veja abaixo.
Como se deseja o número total de passageiros, uma soma será feita. Veja a
seguir.
Total de 42. Este bate com o anterior.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 7
Curso Capacitação da Ufms
Mas se desejo por Cia aérea deve ser selecionada Cia.
Seleciona-se Cia desejada, Avianca e veja o resultado.
Total 12. Bate também com o anterior.
Você pode notar que teria que fazer isto para todas as Cias aéreas.
Porém posso fazer outras análises simultaneamente.
Na coluna G fiz a média, na coluna I também, na J soma e na K quantos
eventos.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 8
Curso Capacitação da Ufms
Anexo2
Copiar subtotais
Os subtotais não fazer parte normal de uma planilha, são uma parte especial e
momentânea. Logo, para copiar este dados não é simples, mas nada muito
difícil. Veja a seguir a planilha com subtotais.
Acima da planilha do lado esquerdo, tem alguns números (1,2 e 3), sendo que
cada um indica um nível que aparecerá os subtotais. O 3 indica que os dados e
subtotais, para esta configuração em particular, serão apresentados. O 2 indica
que somente os subtotais serão apresentados. Por último o 1 indica que
somente o total geral será apresentado.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 9
Curso Capacitação da Ufms
Como se quer apenas os subtotais, clique no 2.
Selecione a área desejada, A3:B64.
Na
aba
Início,
clique
em
Localizar
e
Selecionar, em
Ir para.
Você
também
pode
pressionar
a
tecla F5.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 10
Curso Capacitação da Ufms
Feito isto aparecerá a janela abaixo. Clique em Especial... e uma nova janela
se abrirar (Ir para especial).
Clique em Somente células visíveis e depois OK. Isto fará que somente as
células que estão visíveis no momento sejam selecionadas. As células abaixo
foram realçadas somente para ficarem visíveis na apresentação da figura, mas
normalmente o realce é discreto.
Pronto, agora está tudo preparado para você pressionar as teclas CTRL+C
(copiar). Ao fazê-lo as células marcadas
ganharão linhas pontilhadas ao redor de
cada uma e você já pode dar o CTRL+V
(colar).
Pode notar que não existe mais os números dos níveis, são células normais.
Agora você pode trabalhar como desejar.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 11
Curso Capacitação da Ufms
Anexo3
Fixa Cabeçalho ou Coluna
Você pode deixar fixo o cabeçalho de uma planilha muito grande, para que
saiba sempre qual a coluna do cabeçalho está mexendo.
Selecione na aba Exibição, Congelar Paineis, Congelar Linha superior. O efeito
está abaixo. Da linha 1 passa para linha 8.
Para retirar na mesma aba selecione Descongelar Painéis.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 12
Curso Capacitação da Ufms
Anexo4
Validação de dados e listas
Baseado na planilha abaixo, foi criada uma lista para validar os dados que
serão inseridos na coluna E, Cia aérea.
Selecione a célula em que vai ser inserida a validação de dados, no caso E2.
Na aba Dados, clique em Validação de Dados, Validação de dados.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 13
Curso Capacitação da Ufms
Depois disto, em Configurações, vá em Critérios de validação, selecione
Permitir, Lista.
A nova configuração parcial fica conforme abaixo.
Coloque em Fonte, =$N$3:$N$14, clique em OK.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 14
Curso Capacitação da Ufms
Como pode ser notado acima, uma seta para baixo é acrescida e ao clicar a
lista com os dados aparecem.
Para estender para as células abaixo, clique na primeira e no canto inferior
direito uma pequena cruz aparecerá e arraste até a última célula desejada.
Pronto, todas as selecionadas vão apresentar a seta para baixo com as opções
da lista, ou seja, validação de dados.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 15
Curso Capacitação da Ufms
Mas e se a lista estiver em outra planilha somente com listas, como fazer a
ligação.
A lista está na planilha chamada de listas, a
sintaxe para conectar o conteúdo de uma
planilha a outra é: =listas!A1:A14, ou seja, =
nome da planilha seguida de um ponto de
exclamação (!) e a(s) célula(s) desejada(s).
Clique Ok e pronto a lista está conectada.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 16
Curso Capacitação da Ufms
Validação de dados com conexão entre planilhas
A validação de dados em lista
precisa de um conjunto de células
para criar a lista.
Contudo na hora de preencher o
item Fonte o sistema não deixa
fazer uma conexão entre planilhas.
Então a forma de fazê-lo é sair da
janela ao lado e simular uma
conexão normal dentro da planilha
desejada.
Seleciono uma célula qualquer na planilha de destino da lista, ou seja onde vai ficar o seletor de
nomes da lista. No caso Plan1.
No caso selecionei a célula C1 da planilha
Plan1. Incluo na área de fórmula o caracter
= e seleciono Plan2, tendo como resultado
=Plan2! e depois seleciono as células que
contém a lista, no caso A1:A5. O resultado
é o seguinte: =Plan2!A1:A5. Neste ponto
pressiono a tecla Enter e pronto fecho a
seleção.
O resultado final é visto abaixo, com o
conteúdo da célula C1 com dos nomes da lista.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 17
Curso Capacitação da Ufms
Agora que já tenho link pronto, seleciono a fórmula, conforme abaixo, e pressiono as teclas
CTRL+C, para copiar a fórmula.
Depois que salvar, pressione a tecla ESC e selecione a primeira célula onde vai ficar a lista.
No item Fonte pressione as teclas CTRL+V para colar o link. Nas versões anteriores a 2007, não
esqueça de colocar $ nos endereços das células (A$1:A$5) para fixar as linhas de endereços.
Para copiar para outras células abaixo, arraste para baixo onde está indicado na primeira seta à
esquerda. Por isto que foi fixado a linha nos endereços das células em Fonte, citado acima. Se for
arrastar para o lado deve fixar as colunas, $A1:$A5. Porém se não quiser preocupação fixe os dois,
$A$1:$A$5.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 18
Curso Capacitação da Ufms
Agora você tem a aba da lista disponível, conforme a seta do meio. A célula onde foi criado o link
entre planilhas já pode ser apagada, a primeira seta à direita.
Anexo5
Funções Lógicas
Fórmula SE1
Agora vamos conhecer uma das funções mais utilizadas todas as funções para um iniciante em
Excel. É a função SE, que Retorna um valor se uma condição que você especificou avaliar como
VERDADEIRO e um outro valor se for avaliado como FALSO. Use SE para conduzir testes
condicionais sobre valores e fórmulas.
OBS: "SE" só trabalha com até 8 condições dentro de seu operador e tal condição poderá ser valor,
texto ou outro caractere de origem binária. Lembrando-se que quando não se trata de valores os
argumento deverão ficar entre "aspas" para que a função retorne o valor verdadeiro.
Sintaxe
=SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)
Teste_lógico é qualquer valor ou expressão que possa ser avaliado como
VERDADEIRO ou FALSO. Por exemplo, A10=100 é uma expressão lógica; se o valor
da célula A10 for igual a 100, a expressão será considerada VERDADEIRA. Caso
contrário, a expressão será considerada FALSA. Esse argumento pode usar qualquer
operador de cálculo de comparação.
Valor_se_verdadeiro é o valor retornado se teste_lógico for VERDADEIRO. Por
exemplo, se esse argumento for a sequência de caracteres de texto "Dentro do
orçamento" e o argumento teste_lógico for considerado VERDADEIRO, a função SE
exibirá o texto "Dentro do orçamento". Se teste_lógico for VERDADEIRO e
valor_se_verdadeiro for vazio, o argumento retornará 0 (zero). Para exibir a palavra,
VERDADEIRO, use o valor lógico VERDADEIRO para esse argumento.
Valor_se_verdadeiro pode ser outra fórmula.
Valor_se_falso é o valor retornado se teste_lógico for FALSO. Por exemplo, se esse
argumento for a sequência de caracteres de texto "Acima do orçamento" e o
argumento teste_lógico for considerado FALSO, a função SE exibirá o texto "Acima do
orçamento". Se teste_lógico for FALSO e valor_se_falso for omitido (ou seja, se não
houver vírgula após valor_se_verdadeiro), o valor lógico FALSO será retornado. Se
teste_lógico for FALSO e valor_se_falso for vazio (ou seja, se houver uma vírgula
após valor_se_verdadeiro seguida do parêntese de fechamento), o valor 0 (zero) será
retornado. Valor_se_falso pode ser outra fórmula.
Obs. Importante: existia uma limitação nos números de SE’s que podiam ser usadas
dentro de outro SE, conhecido como aninhamento. O número máximo na versão 2003
é de 64. Comentário da Microsoft com relação a função:
1
Baseado no site http://www.guiadecompra.com/excel/index.php
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 19
Curso Capacitação da Ufms
“Até 64 funções SE podem ser aninhadas como argumentos valor_se_verdadeiro e valor_se_falso
para criar testes mais elaborados (consulte o Exemplo 3 para obter um exemplo das funções SE.)
Como alternativa, para testar várias condições, considere usar as funções PROC, PROCV, PROCH
ou ESCOLHER. (Consulte o Exemplo 4 para obter um exemplo da função PROC.)
Se algum dos argumentos de SE for matriz (matriz: usada para criar fórmulas únicas que
produzem vários resultados ou que operam em um grupo de argumentos organizados
em linhas e colunas. Um intervalo de matrizes compartilha uma fórmula comum; uma
constante de matriz é um grupo de constantes usado como um argumento.), cada
elemento da matriz será avaliado quando a instrução SE for executada.
Funções E e OU
As funções lógicas E e OU permite fazer comparativos com mais de uma variável. São
muito interessantes com a função SE.
Por exemplo, quando na função SE desejo na minha condição ter mais de uma variável,
posso utilizar uma das funções citadas.
Para compreender melhor a função E, ela inclui todos as variáveis: a=b e c=d e e=f e
g<>h. Isto significa que desejo a=b; ao mesmo tempo c=d; ao mesmo tempo e=f e assim
por
diante.
Função
inclusiva.
A
sintaxe
com
a
função
SE
seria:
se(e(a=b;c=d;e=f;g<>h);verdadeiro;falso)
Já a função OU fica da seguinte forma: a=b ou c=d ou e=f ou g<>h. Isto significa que
desejo a=b; pode ser ou não c=d; pode ser ou não e=f e assim por diante. A sintaxe com
a função SE seria: se(ou(a=b;c=d;e=f;g<>h);verdadeiro;falso)
Funções SE CONJUGADAS DE OUTRAS
O Excel fornece funções adicionais que podem ser usadas para analisar os dados com base em uma
condição. Por exemplo, para contar o número de ocorrências de uma cadeia de texto ou um número
dentro de um intervalo de células, use as funções de planilha CONT.SE ou CONT.SES. Para
calcular uma soma baseada em uma cadeia de texto ou em um número dentro de um intervalo, use
as funções de planilha SOMASE ou SOMASES.”
Vamos usar o mesmo documento, ou seja, a planilha que você usou com a função PROCV.
Acrescente na coluna "E" da tabela empregados que você criou e dê o título Reajuste para esta
coluna. Abaixo Digite a fórmula seguinte:
=SE(C12<=400;C12*10%;SE(C12<=700;C12*9%;8%)).
Copie a fórmula até a célula "E21" e verá que os valores do reajuste salarial foram feitos conforme
a condição que você indicou.
Acrescente mais uma coluna e digite Valor com Reajuste some os dois valores "C + E" e copie a
formula para as outras células.
Ainda podemos colocar na última opção, uma condição diferente, por exemplo, um texto, ou o valor
"0" zero. Tente fazer isso.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 20
Curso Capacitação da Ufms
Se está muito difícil, faça uma planilha separada e coloque apenas uma condição. Um bom exemplo
é fazer uma planilha com modelos de carros dando-lhe os títulos: MARCA, MODELO,
COMBUSTIVEL. No título combustível, faça a fórmula seguinte: =SE(determinada célula for igual
a "Marca X";"álcool";caso contrario"gasolina")
Se você digitou o primeiro nome da marca em "A2" e o primeiro modelo em "B2" a função deverá
estar célula "C2" com a seguinte descrição: =SE(A2="Nome da Marca";"Álcool";"Gasolina"). Seria
como se você fosse viajar e desse uma condição para sua viagem: SE chover "eu não viajo"; SE
chegar uma frente fria "também não viajo"; Caso contrário "Viajarei". É mais ou menos isso.
PROCV (Função PROCV)2
Este artigo descreve a sintaxe da fórmula e o uso da função PROCV no Microsoft Excel. Onde
função é uma fórmula pré-desenvolvida que assume um valor ou vários valores, executa uma
operação e retorna um valor ou vários valores. Use as funções para simplificar e reduzir fórmulas
em uma planilha, especialmente aquelas que executam cálculos longos e complexos.
Descrição
Você pode usar a função PROCV para pesquisar a primeira coluna de um intervalo(intervalo: duas
ou mais células em uma planilha. As células de um intervalo podem ser adjacentes ou não
adjacentes.) de células e, em seguida, retornar um valor de qualquer célula na mesma linha do
intervalo. Por exemplo, suponhamos que você tenha uma lista de funcionários contida no intervalo
A2:C10. Os números de identificação dos funcionários são armazenados na primeira coluna do
intervalo, como mostrado na ilustração a seguir.
2
Help do Excel com comentários próprios.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 21
Curso Capacitação da Ufms
Coluna 1
Coluna 2
Coluna 3
A designação de coluna neste caso é só para a função PROCV, especificamente.
Se souber o número de identificação do funcionário, você poderá usar a função PROCV para
retornar o departamento ou o nome desse funcionário. Para obter o nome do funcionário número 38,
você pode usar a fórmula =PROCV(38, A2:C10, 3, FALSO). Essa fórmula procura o valor 38 na
primeira coluna do intervalo A2:C10 e, em seguida, retorna o valor contido na terceira coluna do
intervalo e na mesma linha do valor procurado ("Nuno Farinha") ("Nuno Farinha").
O V em PROCV significa vertical. Use PROCV em vez de PROCH quando os valores da
comparação estiverem posicionados em uma coluna à esquerda ou à direita dos dados que você
deseja procurar.
Sintaxe
PROCV(valor_procurado, matriz_tabela, núm_índice_coluna, [procurar_intervalo])
A sintaxe da função PROCV tem os seguintes argumentos (argumento: um valor que fornece
informações a uma ação, um evento, um método, uma função ou um procedimento.):
valor_procurado: Obrigatório. O valor a ser procurado na primeira coluna da
tabela ou intervalo. O argumento valor_procurado pode ser um valor ou uma
referência. Se o valor que você fornecer para o argumento valor_procurado for
menor do que o menor valor da primeira coluna do argumento matriz_tabela,
PROCV retornará o valor de erro #N/D.
matriz_tabela: Obrigatório. O intervalo de células que contém os dados. Você
pode usar uma referência a um intervalo (por exemplo, A2:D8) ou um nome de
intervalo. Os valores na primeira coluna de matriz_tabela são os valores
procurados por valor_procurado. Os valores podem ser texto, números ou valores
lógicos. Textos em maiúsculas e minúsculas são equivalentes.
núm_índice_coluna: Obrigatório. O número da coluna no argumento
matriz_tabela do qual o valor correspondente deve ser retornado. Um argumento
núm_índice_coluna de 1 retorna o valor na primeira coluna em matriz_tabela; um
núm_índice_coluna de 2 retorna o valor na segunda coluna em matriz_tabela e
assim por diante.
Se o argumento núm_índice_coluna for:
Menor que 1, PROCV retornará o valor de erro #VALOR!.
Maior do que o número de colunas em matriz_tabela, PROCV retornará o
valor de erro #REF!.
procurar_intervalo: Opcional. Um valor lógico que especifica se você quer que
PROCV localize uma correspondência exata ou aproximada.
Se procurar_intervalo for VERDADEIRO, ou for omitido, uma correspondência
exata ou aproximada será retornada. Se uma correspondência exata não for
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 22
Curso Capacitação da Ufms
localizada, o valor maior mais próximo que seja menor que o valor_procurado
será retornado.
Importante: Se procurar_intervalo for VERDADEIRO, ou for omitido, os valores na primeira
coluna de matriz_tabela deverão ser colocados em ordem ascendente; caso contrário, PROCV
poderá não retornar o valor correto.
Para obter informações, consulte Classificar dados em um intervalo ou tabela.
Se procurar_intervalo for FALSO, os valores na primeira coluna de matriz_tabela não precisarão
ser ordenados.
Se o argumento procurar_intervalo for FALSO, PROCV encontrará somente uma
correspondência exata. Se houver dois ou mais valores na primeira coluna de
matriz_tabela que não coincidem com o valor_procurado, o primeiro valor
encontrado será utilizado. Se nenhuma correspondência exata for localizada, o
valor de erro #N/D será retornado..
Comentários
Ao procurar valores de texto na primeira coluna da matriz_tabela, verifique se os
dados na primeira coluna da matriz_tabela não contenham espaços à esquerda ou
de fim de linha, nem usem de modo inconsistente aspas normais ( ' ou " ) e curvas
( ‘ ou “ ) ou caracteres não imprimíveis. Nesses casos, a função PROCV pode
retornar um valor incorreto ou inesperado.
Para obter informações, consulte a função TIRAR e a função ARRUMAR.
Ao procurar valores de número ou data, certifique-se de que os dados na primeira
coluna da matriz_tabela não estejam armazenados como valores de texto. Nesse
caso, PROCV pode retornar um valor incorreto ou inesperado.
Se procurar_intervalo for FALSO e valor_procurado for texto, você poderá usar
os caracteres curinga — ponto de interrogação (?) e asterisco (*) — em
valor_procurado. Um ponto de interrogação coincide com qualquer caractere
único; um asterisco coincide com qualquer cadeia de caracteres. Se você quiser
localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do
caractere.
Exemplo para não aparecer #erro
Link com a internet
Aula5 avan
SE(É.NÃO.DISP
http://www.cavalcanteassociados.com.br/article.php?id=369
É.NÃO.DISP
Cavalcante Consultores > Biblioteca de Excel > É.NÃO.DISP
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 23
Curso Capacitação da Ufms
O que é?
Quando você está trabalhando com um grande banco de dados, as vezes você se depara com uma
situação onde a célula apresenta um erro #N/D (valor não disponível). A função É.NÃO.DISP tem
com objetivo verificar se um valor é #N/D ou não. Caso a célula em questão faça referência ao erro
#N/D, a função retornará o valor VERDADEIRO, caso contrário, retornará o valor FALSO.
Sintaxe da função É.NÃO.DISP
=É.NÃO.DISP(Valor)
Onde Valor é o valor que se deseja testar.
Exemplo
No exemplo a seguir, temos um pequeno cadastro de telefones dos funcionários de uma empresa
feito no Excel.
Primeiramente, utilizaremos a função PROCV para fazer uma tabela que nos retorne os telefones do
cadastro quando entrarmos com um determinado nome. Quando um telefone não for encontrado,
entraremos com a função É.NÃO.DISP para darmos um tratamento especial a pesquisa.
Observe dois exemplos de pesquisa:
Quando o registro não é encontrado, PROCV devolve o valor #N/D!.
Após isso, utilizaremos a função É.NÃO.DISP: ela devolve VERDADEIRO se seu argumento for
o valor de erro acima, e FALSO caso contrário.
Instrutor: Pedro Ismar M. S. Jr. – [email protected]
Página 24