quinta-feira, 31 de julho de 2008

Funções aninhadas no Excel

Neste artigo vamos mostrar como usar funções aninhadas no Excel.

Primeiro apresentaremos as funções em separado e depois as funções aninhadas.

Usaremos alguns exemplos simples de cada função ( na planilha funções.xls ) somente para demonstrar a sintaxe e a utilidade da função. Muitos dos exemplos foram retirados do próprio Help do Excel.

Mostraremos como usar as funções abaixo e como aninhá-las ( A definição das funções e suas sintaxes foram retiradas do próprio Help do Excel )

COL - Retorna o número de coluna da referência especificada.

Sintaxe

COL(ref)

CONCATENAR - Agrupa várias seqüências de caracteres de texto em uma única seqüência de caracteres de texto.

Sintaxe

CONCATENAR (texto1;texto2; ...)

OBS.: O operador "&" pode ser usado no lugar de CONCATENAR para agrupar itens de texto.

CORRESP - Retorna a posição relativa de um item em uma matriz que coincide com um valor especificado em uma ordem específica. Use CORRESP em vez de uma das funções PROC quando você precisar da posição de um item em um intervalo ao invés do item propriamente dito.

Sintaxe

CORRESP(valor_procurado;matriz_procurada;tipo_correspondência)

OBS .:Tipo_correspondência é o número -1, 0 ou 1. Tipo_correspondência especifica como o Microsoft Excel corresponde a valor_procurado com os valores contidos em matriz_procurada.

  • Se tipo_correspondência for 1, CORRESP localizará o maior valor que for menor do que ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem ascendente: ...-2, -1, 0, 1, 2,...A-Z, FALSO, VERDADEIRO.
  • Se tipo_correspondência for 0, CORRESP localizará o primeiro valor que for exatamente igual a valor_procurado. Matriz_procurada pode ser colocada em qualquer ordem.
  • Se tipo_correspondência for -1, CORRESP localizará o menor valor que seja maior ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem decrescente: VERDADEIRO, FALSO, Z-A,...2, 1, 0, -1, -2,... e assim por diante.
  • Se tipo_correspondência for omitido, será equivalente a 1.

DESLOC - Retorna uma referência para um intervalo, que é um número especificado de linhas e colunas de uma célula ou intervalo de células. A referência retornada pode ser uma única célula ou um intervalo de células. Você pode especificar o número de linhas e de colunas a serem retornadas.

Sintaxe

DESLOC(ref;lins;cols;altura;largura)

OBS.: Altura é a altura, em número de linhas, que se deseja para a referência fornecida. Altura deve ser um número positivo.

Largura é a largura, em número de colunas, que se deseja para a referência fornecida. Largura deve ser um número positivo.

DIREITA - retorna o último caractere ou caracteres em uma seqüência de caracteres de texto com base no número de caracteres especificado por você.

Sintaxe

DIREITA(texto;núm_caract)

OBS.:

  • Núm_caract deve ser maior ou igual a zero.
  • Se núm_caract for maior do que o comprimento do texto, DIREITA retornará todo o texto.
  • Se núm_caract for omitido, será considerado 1.

E - Retornará VERDADEIRO se todos os argumentos forem verdadeiros; retornará FALSO se um ou mais argumentos forem falsos.

Sintaxe

E(lógico1;lógico2; ...)

ÉERROS - Valor se referir a qualquer valor de erro (#N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!), caso o valor se refira a um dos erros acima, a função retornará VERDADEIRO.

Sintaxe

ÉERROS(valor)

ENDEREÇO - Cria um endereço de célula como texto, dados números específicos de linhas e colunas

Sintaxe

ENDEREÇO(núm_linha;núm_col;núm_abs;a1;texto_planilha)

OBS: Núm_abs especifica o tipo de referência a ser retornado.

Núm_abs

Retorna este tipo de referência

1 ou omitido

Absoluta

2

Linha absoluta, coluna relativa

3

Linha relativa, coluna absoluta

4

Relativa

ESQUERDA - retorna o primeiro caractere ou caracteres em uma seqüência de caracteres de texto baseado no número de caracteres especificado por você.

Sintaxe

ESQUERDA(texto;núm_caract)

EXT. TEXTO - retorna um número específico de caracteres da seqüência de caracteres texto, começando na posição especificada, com base no número de caracteres especificado.

Sintaxe

EXT.TEXTO(texto;núm_inicial;núm_caract)

OBS.: Se núm_inicial for menor do que 1, EXT.TEXTO retornará o valor de erro #VALOR!.

INDICE - Retorna um valor ou a referência a um valor dentro de uma tabela ou intervalo. Há duas formas da função ÍNDICE(): matriz e referência. A forma de matriz sempre retorna um valor ou uma matriz de valores; a forma de referência sempre retorna uma referência.

Sintaxe 1

Forma matricial

ÍNDICE(matriz,núm_linha,núm_coluna)

Matriz é um intervalo de células ou uma constante de matriz.

Sintaxe 2

Forma de referência

ÍNDICE(ref;núm_linha;núm_coluna;núm_área)

Ref é uma referência a um ou mais intervalos de célula.

INDIRETO - Retorna a referência especificada por uma seqüência de caracteres de texto. As referências são imediatamente avaliadas para exibir seu conteúdo. Use INDIRETO quando quiser mudar a referência a uma célula em uma fórmula sem mudar a própria fórmula.

Sintaxe

INDIRETO(texto_ref;a1)

OBS.: Quando você cria uma fórmula que refere-se a uma célula, a referência à célula será atualizada se: (1) a célula for movida com o comando Recortar para excluir a célula ou (2) a célula for movida devido à inserção ou exclusão de linhas ou colunas. Se você desejar que a fórmula sempre se refira à mesma célula independentemente de a linha acima da célula ter sido excluída ou a célula ter sido movida, use a função de planilha INDIRETO. Por exemplo, se você quiser que a fórmula sempre se refira à célula A10, use a seguinte sintaxe.

LIN - Retorna o número da linha de uma referência.

Sintaxe

LIN(matriz)

LOCALIZAR - retorna o número do caractere no qual um caractere específico ou uma seqüência de caracteres de texto é encontrado primeiro, começando com núm_inicial. Use LOCALIZAR para determinar o local de um caractere ou uma seqüência de caracteres de texto em outra seqüência para que você possa usar as funções EXT.TEXTO ou MUDAR para alterar o texto.

Sintaxe

LOCALIZAR(texto_procurado;no_texto;núm_inicial)

MOD - Retorna o resto depois da divisão de núm por divisor. O resultado possui o mesmo sinal que divisor.

Sintaxe

Resto(núm,divisor)

OBS.: Se divisor for 0, Resto retornará o valor de erro #DIV/0!.

MUDAR - Substitui parte de uma seqüência de caracteres de texto, com base no número de caracteres especificado, por uma seqüência diferente.

Sintaxe

MUDAR(texto_antigo;núm_inicial;núm_caract;novo_texto)

NUM.CARACT - retorna o número de caracteres em uma seqüência de caracteres de texto.

Sintaxe

NÚM.CARACT(texto)

OU - Retorna VERDADEIRO se qualquer argumento for VERDADEIRO; retorna FALSO se todos os argumentos forem FALSOS.

Sintaxe

OU(lógico1;lógico2;...)

PROC - Retorna um valor de um intervalo de uma linha ou uma coluna ou de uma matriz. A função PROC apresenta duas formas de sintaxe: de vetor e de matriz. A forma de vetor de PROC examina um intervalo de uma linha ou uma coluna (conhecida como vetor) em busca de um valor e retorna um valor da mesma posição em um segundo intervalo de uma linha ou uma coluna. A forma de matriz de PROC examina a primeira linha ou coluna de uma matriz em busca do valor especificado e retorna um valor da mesma posição na última linha ou coluna da matriz.

Sintaxe 1

Forma vetorial

PROC(valor_procurado;vetor_proc;vetor_result)

OBS.:

Os valores em vetor_proc devem ser colocados em ordem ascendente: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADEIRO, caso contrário, PROC pode não fornecer o valor correto. Textos em maiúsculas e minúsculas são equivalentes.

Se PROC não localizar o valor_procurado, ele coincidirá com o maior valor em vetor_proc que for menor ou igual a valor_procurado.

Se valor_procurado for menor do que o menor valor em vetor_proc, PROC fornecerá o valor de erro #N/D.

Sintaxe 2

Forma matricial

PROC(valor_procurado;matriz)

OBS.:

A forma matricial de PROC é muito semelhante às funções PROCH e PROCV. A diferença é que PROCH localiza valor_procurado na primeira linha, PROCV localiza na primeira coluna e PROC localiza conforme as dimensões de matriz.

Se a matriz cobrir uma área que apresente a largura maior do que a altura (mais colunas do que linhas), PROC localiza valor_procurado na primeira linha.

Se a matriz apresentar a altura maior do que a largura (mais linhas do que colunas), PROC localiza na primeira coluna.

PROCH e PROCV permitem que você indexe na vertical ou horizontal, mas PROC sempre seleciona o último valor na linha ou coluna.

PROCH - Localiza um valor específico na linha superior de uma tabela ou matriz de valores e retorna um valor na mesma coluna de uma linha especificada na tabela ou matriz. Use PROCH quando seus valores de comparação estiverem localizados em uma linha ao longo da parte superior de uma tabela de dados e você quiser observar um número específico de linhas mais abaixo. Use PROCV quando os valores de comparação estiverem em uma coluna à esquerda dos dados que você deseja localizar.

O H de PROCH significa "Horizontal."

Sintaxe

PROCH(valor_procurado;matriz_tabela;núm_índice_lin;procurar_intervalo)

OBS.:

  • Se PROCH não localizar valor_procurado, e procurar_intervalo for VERDADEIRO, ela usará o maior valor que é menor do que o valor_procurado.
  • Se o valor_procurado for menor do que o menor valor na primeira linha de matriz_tabela, PROCH retornará o valor de erro #N/D.

PROCURAR - localiza uma seqüência de caracteres de texto (texto_procurado) em outra seqüência (no_texto) e retorna o número da posição inicial de texto_procurado a partir do primeiro caractere de no_texto. Você também pode usar LOCALIZAR para encontrar uma seqüência de caracteres de texto em outra, mas ao contrário de LOCALIZAR, PROCURAR diferencia maiúsculas e minúsculas e não permite caracteres curinga.

Sintaxe

PROCURAR(texto_procurado;no_texto;núm_inicial)

PROCV - Localiza um valor na primeira coluna à esquerda de uma tabela e retorna um valor na mesma linha de uma coluna especificada na tabela. 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.

O V em PROCV significa "Vertical".

Sintaxe

PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo)

OBS.: Se procurar_intervalo for VERDADEIRO, os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem ascendente: ..., -2, -1, 0, 1, 2, ... , A-Z, FALSO, VERDADEIRO; caso contrário, PROCV pode não retornar o valor correto. Se procurar_intervalo for FALSO, matriz_tabela não precisará ser ordenada.

Você pode colocar os valores em ordem ascendente escolhendo o comando Classificar no menu Dados e selecionando Crescente.

Os valores na primeira coluna de matriz_tabela podem ser texto, números ou valores lógicos.

Textos em maiúsculas e minúsculas são equivalentes.

SE - 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.

Sintaxe

SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)

OBS.:

  • É possível aninhar até sete funções SE como argumentos valor_se_verdadeiro e valor_se_falso para construir testes mais elaborados. Consulte o último dos exemplos a seguir.
  • Se o primeiro teste_lógico for FALSO, a segunda instrução SE é avaliada e assim por diante.

SOMASE - Soma células especificadas por um determinado critério.

Sintaxe

SOMASE(intervalo;critérios;intervalo_soma)

SUBSTITUIR - Coloca novo_texto no lugar de texto_antigo em uma seqüência de caracteres de texto. Use SUBSTITUIR quando quiser substituir texto específico em uma seqüência de caracteres de texto; use MUDAR quando quiser substituir qualquer texto que ocorra em um local específico de uma seqüência de caracteres de texto.

Sintaxe

SUBSTITUIR(texto;texto_antigo;novo_texto;núm_da_ocorrência)

ANINHANDO FUNÇÕES

Após a apresentação das funções acima e seus exemplos, vamos ver o que podemos fazer aninhando algumas delas.

Vamos começar com funções mais simples e depois complicar um pouco mais as coisas. Vamos lá!

Para facilitar, utilizaremos ( Nome da área no Excel). Caso queira saber sobre uma função em específico, faça como a figura abaixo:

Nome da área

SE + SOMA ( SE_SOMA)

SE + MOD + LIN + SOMA ( SE_MOD_LIN_SOMA)

EXT.TEXTO + PROCURAR ( EXT.TEXTO_PROCURAR )

PROCV + CONCATENAR (PROCV_CONCATENAR)

SE + PROCV + CONCATENAR + ÉERROS ( SE_PROCV_CONCATENAR_ÉERROS )

=SE(ÉERROS(PROCV($H24&$I24;$I$12:$M$18;3;FALSO));"NÃO ENCONTRADO";PROCV($H24&$I24;$I$12:$M$18;3;FALSO))

SOMA + DESLOC (SOMA_DESLOC )

SE + ÉERROS + ÍNDICE + CORRESP (SE_ÉERROS_ÍNDICE_CORRESP )

=SE(ÉERROS(ÍNDICE($J$13:$M$18;CORRESP($I33;$J$13:$J$17;0);2));0;ÍNDICE($J$13:$M$18;CORRESP($I33;$J$13:$J$17;0);2))

SE + E + ESQUERDA (SE_E_ESQUERDA )

SE + OU + ESQUERDA + DIREITA + SOMA (SE_OU_ESQUERDA_DIREITA_SOMA )

PROCH + SOMA + DESLOC (PROCH_SOMA_DESLOC )

MUDAR + LOCALIZAR + DIREITA (MUDAR_LOCALIZAR_DIREITA )



Planilha de Exemplos AQUI

2 comentários:

Vasco disse...

Necessito somar uma determinada coluna com condicionais de duas outras, ou seja: Se (A1:A20="Vasco") e (B1:B20="Consulta") soma (C1:C20)

pnbastos disse...

Boa noite, estou com um problema no excel, tenho uma celula composta de um numero na celula seguinte tenho que incluir o digito veriticador e não consigo utilizar o =MOD(A1;11, é modulo 11seria possivel me ajudar.
ex. celula A1 = 0001000 codigo
celula B1 = 6 digito

grato pela ajuda.