Buscando e Separados conjunto de numeros.

[lu1zuk]

Ola, tenho uma planilha com relação a contagem de horas e checagem sobre as horas totais em relação a determinados cargos:
Exemplo.

Porem ali em cargos sao diversos "pares" de 18 numeros sequenciados, chamados de ID. Cada 18 numeros corresponde a um suposto id.
Por exemplo:
Os numeros sao o Id so cargo a esquerda, enquanto a direita é a meta de determinado cargo.

Eis o problema/Dor de cabeça. Na primeira imagem na Coluna "cargos" se encontra toda essa informação abaixo.
E cada pessoa possui diversos cargos. Nem sempre a quantidade é a mesma, podendo ter mais ou menos cargos, normalmente os 2 primeiros cargos e o ultimo cargo sao iguais para todos.
"869353363609899039, 869353363609899040, 869353363609899044, 869353363677020214, 869353363769290771, 869353363807010852, 869353363815411754, 869353363815411756, 869353363815411760, 870079612863012956, 870330472213921883, 869353363609899038"

O objetivo: Eu quero poder localizar especificamente o par de 18 numeros correspondente aos cargos das metas.

=EXT.TEXTO(d3;1;LOCALIZAR("869353363815411760";d3))

Eu tentei por meio do comando ext.texto e o localizar mas falhou.

Sou bem novo com excel, so tenho o conhecimento basico e um pouco a mais sobre as "funçoes" do mesmo.
E pelo o que entendi o Extrair ira extrair o conteudo de determinada célula a partir da contagem de "espaços" que for definido em "ext.texto(d3;1)
Quanto o localizar ele vai buscar pelos 18 numeros correspondentes que eu desejo. Como da pra imaginar falhou catastróficamente primeiro porque acredito que essa é a maneira errada.

Entao venho aqui pedi ajuda sobre o que posso fazer para:
Localizar uma entre as sequencias de 18numeros, especificos 18numeros que eu quero, assim eliminando os outros, porem preciso fazer isso com que ele procure 1 por 1.

Fato: A pessoa que possui um cargo de meta (exemplo- aspirante) ele nao possuira nenhum outro cargo alem daquele de meta.
Entao a ideia é buscar no meio de todos aqueles numero com virgula, o par de 18 que combina com os de metas que eu procuro, eles sao 15 cargos de metas.
Entao ele vai buscar no meio de todos os pares um id que corresponda com algum id dos 15 que serão fixos. Tais tipo como um "se", porem o se nao tem essa função de busca, eu acho.

 

[lu1zuk]

Para você entender a fórmula
Em Português
O3 copiada para baixo
=SEERRO(ÍNDICE(R$3:R$17;AGREGAR(15;6;(LIN(R$3:R$17)-LIN(R$3)+1)/ÉNÚM(LOCALIZAR(S$3:S$17;E3));1));"")

A parte mais complicada é a função AGREGAR
Dá uma olhada no Help sobre esta função, ela é muito útil
Tem vários parâmetros, lá vai:
15 --> pegue o menor valor
6 ---> ignore valores de erro

Esta parte (LIN(R$3:R$17)-LIN(R$3)+1) gera um array vertical (conjunto de valores) correspondentes às 15 linhas existentes em R3:R17 , ou seja
{1; 2; 3; ....;14;15}

Este array é dividido por um outro array de mesmo tamanho produzido pela condição
ÉNÚM(LOCALIZAR(S$3:S$17;E3))
que produz um monte de VERDADEIROS ou FALSOS (Verdadeiro quando o valor em R3:R17 é encontrado na célula E3; Falso, em caso contrário)

A divisão dos 2 arrays produz um número de linha (quando a condição acima é Verdadeira) ou um erro #DIV/0! (quando a condição é Falsa)

O último parâmetro de AGRGAR é 1 para que a função retorne o menor (smallest) de todos os valores, ignorando erros (note o 6 = ignore erros)

Finalmente, a função ÍNDICE usa este valor produzido pelo AGREGAR para pegar o Cargo da linha correspondente.

Espero ter sido claro. A fórmula é meio enroladinha, mas dá pra entender

Para ver, passo a passo, o que a fórmula faz, selecione O3 e vá para
Fórmulas > Avaliar Fórmula

Boa sorte!

M.

Simmm a explicação foi muito boa, consegui compreender lendo de primeira, sou muito grato!

 

[Marcelo Branco]

=SE(O3="aspirante";SE(L3>=T3;"Sobe";SE(O3="2Tenente";SE(L3>=T3;"sobe"))))< se corresponde ao aspirante ok, se nao continuar checando pelo proximo na lista ate achar o verdadeiro e preencher com a condição certa. Eis o problema. (Claro so fiz ate o 2 tenente porque estava testando)

Vamos continuar aqui mesmo.
Ainda não entendi bem esta parte. Quando é que "sobe"; quando é que "desce"; qual a lógica?

M.

 

[lu1zuk]

Vamos continuar aqui mesmo.
Ainda não entendi bem esta parte. Quando é que "sobe"; quando é que "desce"; qual a lógica?

M.

A logica é comparar total inteiro(L) com horas metas(Q)
Se cargo(o) igual a aspirante entao agora compare, se total inteiro(L) for maior ou igual a horas metas(Q) correspondente ao cargo(o) feito nessa comparação entao sobe, se cargo(o) for diferente , continue o se, porem agora checando para 2 tenente, e segue adianta para todo ate com exercito

(em relação ao desce, ja esta feito naquela checagem(M) se estiver rebaixar tanto eu posso ignorar visualmente (tipo olhar pra tela e ver , ta rebaixar entao desnecessario fazer ou se)
Ou seja, toda a comparação de sobe ou desce, é apenas para aqueles que estao acima do desce, entao ja vou corrigindo logo para "sobe ou mantem"

Tempo staff teste.xlsx
	L	M	N	O	P	Q	R	S	T
2	Total Inteiro	Checagem	Sobe ou Mantem?	Cargo	Metas	Horas Metas
3	30	Manter	Sobe	Aspirante	Aspirante 869353363815411760	30	Aspirante	869353363815411760	30
4	41	Manter	FALSO	Capitao de Mar e Guerra	2Tenente 869353363815411761	35	2Tenente	869353363815411761	35
5	38	Manter	FALSO	Capitao de Fragata	1Tenente 869353363823816714	40	1Tenente	869353363823816714	40
6	37	Manter	FALSO	Tenente Coronel	Capitao 869353363823816715	45	Capitao	869353363823816715	45
7	36	Manter	FALSO	Tenente Coronel	Capitao Tenente 869353363823816716	50	Capitao Tenente	869353363823816716	50
8	36	Manter	FALSO	Gen Exercito	Capitao de Corveta 869353363823816717	55	Capitao de Corveta	869353363823816717	55
9	36	Manter	FALSO	Capitao de Corveta	Capitao de Fragata 869353363823816718	60	Capitao de Fragata	869353363823816718	60
10	36	Manter	FALSO	Com Exercito	Capitao de Mar e Guerra 869353363823816719	60	Capitao de Mar e Guerra	869353363823816719	60
11	30	Manter	FALSO	Aspirante	Major 869353363823816720	65	Major	869353363823816720	65
12	33	Manter	FALSO	Capitao de Corveta	Tenente Coronel 869353363823816721	75	Tenente Coronel	869353363823816721	75
13	33	Manter	FALSO	Gen Exercito	Coronel 869353363823816722	80	Coronel	869353363823816722	80
14	33	Manter	FALSO	Gen Divisao	Gen Brigada 869353363832180797	125	Gen Brigada	869353363832180797	125
15	33	Manter	FALSO	Major	Gen Divisao 869353363832180799	150	Gen Divisao	869353363832180799	150
16	32	Manter	FALSO		Gen Exercito 869353363832180801	200	Gen Exercito	869353363832180801	200
17	35	Manter	FALSO	2Tenente	Com Exercito 869353363832180805	250	Com Exercito	869353363832180805	250
Sheet1

Cell Formulas
Range	Formula
M3:M17	M3	=IF(L3>=30,"Manter","Rebaixar")
N3:N17	N3	=IF(O3="Aspirante",IF(L3>=T$3:T$17,"Sobe",IF(O3="2Tenente",IF(L3>=T$3:T$17,"sobe"))))
O3:O17	O3	=IFERROR(INDEX(R$3:R$17,AGGREGATE(15,6,(ROW(R$3:R$17)-ROW(R$3)+1)/ISNUMBER(SEARCH(S$3:S$17,E3)),1)),"")
R3:R17	R3	=LEFT(P3,SEARCH(8,P3)-2)
S3:S17	S3	=RIGHT(P3,18)
T3:T17	T3	=INT(Q3)
L12:L16,L4:L10	L4	=INT(K4)

 

[Marcelo Branco]

Experimente esta fórmula em N3
=SE(O3<>"";SE(L3>=ÍNDICE(Q$3:Q$17;CORRESP(O3;R$3:R$17;0));"Sobe";"Desce");"")
copie para baixo

M.

 

[lu1zuk]

Experimente esta fórmula em N3
=SE(O3<>"";SE(L3>=ÍNDICE(Q$3:Q$17;CORRESP(O3;R$3:R$17;0));"Sobe";"Desce");"")
copie para baixo

M.

Funcionou, obrigado!

 

[Marcelo Branco]

Maravilha!

Fico feliz por ajudar.

M.