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.

 

[Marcelo Branco]

Use esta fórmula em O3
=SE(ÉNÚM(LOCALIZAR(DIREITA(P3;18);E3));ESQUERDA(P3;LOCALIZAR(" ";P3)-1);"")

ela retorna
Aspirante
porque 869353363815411760 está presente em E3

É isto que vc quer?

M.

 

[Marcelo Branco]

hmm...
Acho que, na verdade, vc quer algo assim na coluna O
Observe que criei mais duas colunas separando o Cargo da ID (número com 18 dígitos) nas colunas R e S (área cinza)

30072021 Testes.xlsx
	O	P	Q	R	S
1
2	Cargo	Cargo ID	Horas Metas	Cargo	ID
3	Aspirante	Aspirante 869353363815411760	30	Aspirante	869353363815411760
4	Capitao de Mar e Guerra	2Tenente 869353363815411761	35	2Tenente	869353363815411761
5	Capitao de Fragata	1Tenente 869353363823816714	40	1Tenente	869353363823816714
6	Tenente Coronel	Capitao 869353363823816715	45	Capitao	869353363823816715
7	Tenente Coronel	Capitao Tenente 869353363823816716	50	Capitao Tenente	869353363823816716
8	Gen Exercito	Capitao de Corveta 869353363823816717	55	Capitao de Corveta	869353363823816717
9	Capitao de Corveta	Capitao de Fragata 869353363823816718	60	Capitao de Fragata	869353363823816718
10	Com Exercito	Capitao de Mar e Guerra 869353363823816719	60	Capitao de Mar e Guerra	869353363823816719
11	Aspirante	Major 869353363823816720	65	Major	869353363823816720
12	Capitao de Corveta	Tenente Coronel 869353363823816721	75	Tenente Coronel	869353363823816721
13	Gen Exercito	Coronel 869353363823816722	80	Coronel	869353363823816722
14	Gen Divisao	Gen Brigada 869353363832180797	125	Gen Brigada	869353363832180797
15	Major	Gen Divisao 869353363832180799	150	Gen Divisao	869353363832180799
16		Gen Exercito 869353363832180801	200	Gen Exercito	869353363832180801
17	2Tenente	Com Exercito 869353363832180805	250	Com Exercito	869353363832180805
Plan5

Cell Formulas
Range	Formula
R3:R17	R3	=LEFT(P3,SEARCH(8,P3)-2)
S3:S17	S3	=RIGHT(P3,18)
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)),"")

A formula verifica se alguma ID existente na coluna E corresponde a alguma da coluna S, Caso sim, retorna o cargo na coluna R.

Espero que seja isto que vc está buscando. Diz aí se estou certo.

M

 

[lu1zuk]

Acho que tem um erro na coluna P

30072021 Testes.xlsx
	P
2	Metas
3	Aspirante 869353363815411760
4	2Tenente 869353363815411760
Plan4

Aspirante e 2Tenente têm o mesmo número.

Acho que deve ser

30072021 Testes.xlsx
	P
1
2	Metas
3	Aspirante 869353363815411760
4	2Tenente 869353363815411761
Plan4

Certo?

M.

Sim sim, isso eu corrigi, eu percebi depois so esqueci de avisar aqui, perdao. Mas sim. Enfim, ainda nao entendo como fazer o q eu procuro, conseguiu compreender com o exemplo que eu dei na minha ultima resposta ?

 

[Marcelo Branco]

Veja se a fórmula que eu usei no post #12 faz o que vc precisa.
Retorna os cargos que existem na célula da coluna E na linha correspondente.

Por exemplo,
em O3 retorna Aspirante porque a ID 869353363815411760 existe em E3
em O4 retorna Capitao de Mar e Guerra porque a ID 869353363823816719 existe em E4
e assim por diante

M.

 

[lu1zuk]

Vou ver

 

[Marcelo Branco]

Se vc precisar a fórmula em Portugues me diga

M.

 

[lu1zuk]

Sim! Funcionou perfeitamente!
Apenas não aprendi muito infelizmente mas resolveu o problema sim, o objetivo era aprender, mas nao estou reclamando, estou mais que contente com a sua ajuda! Muito obrigado manin

Eu passei ela pra portugues, as formulas traduzidas sao bem "literal" com exceção do search kk

 

[lu1zuk]

Melhor eu fazer outro post para essa pergunta? \/ ou tudo bem continuar aqui? Envolve a mesma planilha.
E uma pergunta rapida. Eu vou agora fazer um "se" para todos os cargos no local do "sobe e desce(N)" é so eu fazer um "se" grande
por exemplo:
=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)

Tempo staff teste.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	Usuário	Id	Tempo	Ações	Cargos		Tempo		Mov F	Abrir.Mov	Horas	Total Inteiro	Checagem	Sobe ou Desce?	Cargo	Metas	Horas Metas
3	?ᵛᵘˡᵍᵒ ʀᴀʏ?732587448571265047	732587448571265047	45 Horas 13 Minutos 43 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363677020214, 869353363769290771, 869353363807010852, 869353363815411754, 869353363815411756, 869353363815411760, 870079612863012956, 870330472213921883, 869353363609899038	aaaa	45		1	2	47,7	29	Rebaixar	FALSO	Aspirante	Aspirante 869353363815411760	30	Aspirante	869353363815411760	30
4	ˡᵉᵛⁱ ღ750453670910623895	750453670910623895	41 Horas 32 Minutos 24 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363668598801, 869353363807010852, 869353363815411754, 869353363823816719, 869353764727947324, 869353363609899038	aaaa	41				41	41	Manter	FALSO	Capitao de Mar e Guerra	2Tenente 869353363815411761	35	2Tenente	869353363815411761	35
5	??????495630641396842508	495630641396842508	38 Horas 31 Minutos 43 Segundos		869353363609899039, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363752517681, 869353363769290771, 869353363781853249, 869353363781853251, 869353363781853252, 869353363794444395, 869353363807010852, 869353363807010854, 869353363815411757, 869353363823816718, 870486899452706816, 869353363609899038	aaaa	38				38	38	Manter	FALSO	Capitao de Fragata	1Tenente 869353363823816714	40	1Tenente	869353363823816714	40
6	୨?ᵇᵈᶜ Zøe S2 ᵐᵃᵍⁱᶜ548646519406854340	548646519406854340	37 Horas 42 Minutos 24 Segundos		869353363609899039, 869353363609899040, 869353363635073073, 869353363668598804, 869353363677020212, 869353363677020214, 869353363677020217, 869353363752517681, 869353363769290764, 869353363769290765, 869353363769290769, 869353363769290770, 869353363781853247, 869353363781853249, 869353363781853251, 869353363781853252, 869353363794444390, 869353363794444395, 869353363794444396, 869353363794444397, 869353363807010852, 869353363815411754, 869353363823816721, 869353764727947324, 870079612863012956, 870309889619152906, 870486899452706816, 869353363609899038	aaaa	37				37	37	Manter	FALSO	Tenente Coronel	Capitao 869353363823816715	45	Capitao	869353363823816715	45
7	?ℎ?? ?? ???? s2726176887126229053	726176887126229053	36 Horas 32 Minutos 50 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363677020214, 869353363697983495, 869353363769290763, 869353363769290764, 869353363769290770, 869353363769290771, 869353363781853247, 869353363781853249, 869353363781853250, 869353363794444390, 869353363807010852, 869353363815411757, 869353363823816721, 870309889619152906, 870486899452706816, 869353363609899038	aaaa	36				36	36	Manter	FALSO	Tenente Coronel	Capitao Tenente 869353363823816716	50	Capitao Tenente	869353363823816716	50
8	! astrx Dark痛 s2692778724499783712	692778724499783712	36 Horas 24 Minutos 1 Segundos		869353363609899039, 869353363609899040, 869353363609899042, 869353363635073066, 869353363635073073, 869353363668598799, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020215, 869353363731533895, 869353363769290770, 869353363781853249, 869353363781853250, 869353363781853251, 869353363781853252, 869353363794444389, 869353363794444390, 869353363794444395, 869353363794444396, 869353363794444397, 869353363807010852, 869353363807010853, 869353363815411754, 869353363832180801, 869353363844780126, 869353764727947324, 869957469038870638, 870030413609975880, 870079612863012956, 870309889619152906, 869353363609899038	aaaa	36				36	36	Manter	FALSO	Gen Exercito	Capitao de Corveta 869353363823816717	55	Capitao de Corveta	869353363823816717	55
9	? Frostzin ?☪☁615933064253669399	615933064253669399	36 Horas 19 Minutos 26 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363769290762, 869353363769290771, 869353363781853250, 869353363807010852, 869353363815411755, 869353363815411757, 869353363823816717, 869353363844780131, 869353764727947324, 869928452046864495, 870320793165385728, 870326462220234783, 870330472213921883, 870486899452706816, 869353363609899038	aaaa	36				36	36	Manter	FALSO	Capitao de Corveta	Capitao de Fragata 869353363823816718	60	Capitao de Fragata	869353363823816718	60
10	MarceloXD2709322462408557723650	322462408557723650	36 Horas 17 Minutos 27 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363656020029, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020214, 869353363697983495, 869353363697983496, 869353363752517679, 869353363769290770, 869353363769290771, 869353363781853247, 869353363781853249, 869353363781853250, 869353363781853251, 869353363794444389, 869353363794444390, 869353363794444395, 869353363794444396, 869353363794444397, 869353363807010852, 869353363815411758, 869353363832180805, 869353363844780128, 869353363861536791, 869353764727947324, 870030413609975880, 870309889619152906, 870330472213921883, 870718681196413018, 869353363609899038	aaaa	36				36	36	Manter	FALSO	Com Exercito	Capitao de Mar e Guerra 869353363823816719	60	Capitao de Mar e Guerra	869353363823816719	60
11	☹S U M I D A☹816314934873817125	816314934873817125	35 Horas 41 Minutos 30 Segundos		869353363609899039, 869353363609899040, 869353363635073067, 869353363647651883, 869353363647651889, 869353363668598798, 869353363668598804, 869353363677020212, 869353363677020214, 869353363677020217, 869353363807010852, 869353363815411754, 869353363815411760, 870079612863012956, 869353363609899038	aaaa	35				35	35	Manter	FALSO	Aspirante	Major 869353363823816720	65	Major	869353363823816720	65
12	?????ᵇᶻʸ766755329223426099	766755329223426099	33 Horas 51 Minutos 38 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363677020214, 869353363781853252, 869353363794444390, 869353363807010852, 869353363815411754, 869353363823816717, 869353363609899038	aaaa	33				33	33	Manter	FALSO	Capitao de Corveta	Tenente Coronel 869353363823816721	75	Tenente Coronel	869353363823816721	75
13	krixi451482252103385099	451482252103385099	33 Horas 34 Minutos 41 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363668598801, 869353363677020210, 869353363677020214, 869353363677020217, 869353363697983495, 869353363794444393, 869353363807010852, 869353363807010853, 869353363815411754, 869353363815411759, 869353363832180801, 869353764727947324, 870320793165385728, 869353363609899038	aaaa	33				33	33	Manter	FALSO	Gen Exercito	Coronel 869353363823816722	80	Coronel	869353363823816722	80
14	! Sebastian☁675931948991905804	675931948991905804	33 Horas 24 Minutos 26 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363677020210, 869353363677020214, 869353363731533899, 869353363731533900, 869353363781853252, 869353363807010852, 869353363807010853, 869353363815411754, 869353363832180799, 869353363844780126, 869353363861536791, 869353764727947324, 869353363609899038	aaaa	33				33	33	Manter	FALSO	Gen Divisao	Gen Brigada 869353363832180797	125	Gen Brigada	869353363832180797	125
15	! Srta.Sasori 痛☪772645993728049184	772645993728049184	33 Horas 9 Minutos 4 Segundos		869353363609899039, 869353363609899040, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020217, 869353363807010852, 869353363815411754, 869353363823816720, 869353363609899038	aaaa	33				33	33	Manter	FALSO	Major	Gen Divisao 869353363832180799	150	Gen Divisao	869353363832180799	150
16	! Doutoraa s2 ⋆｡ʚଓ ?540871271710326794	540871271710326794	32 Horas 34 Minutos 57 Segundos		869353363609899039, 869353363609899040, 869353363609899042, 869353363609899044, 869353363635073066, 869353363635073067, 869353363635073068, 869353363635073072, 869353363647651886, 869353363668598798, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020217, 869353363731533894, 869353363752517675, 869353363781853251, 869353363794444389, 869353363794444390, 869353363794444393, 869353363794444395, 869353363794444396, 869353363794444397, 869353363807010852, 869353363807010853, 869353363815411754, 869353363815411759, 869353363874131971, 869353363609899038	aaaa	32				32	32	Manter	FALSO		Gen Exercito 869353363832180801	200	Gen Exercito	869353363832180801	200
17	???665124641924186133	665124641924186133	31 Horas 50 Minutos 15 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363769290770, 869353363769290771, 869353363807010852, 869353363815411754, 869353363815411761, 869957469038870638, 870079612863012956, 870309889619152906, 870320793165385728, 870486899452706816, 869353363609899038	aaaa	31				31	31	Manter	FALSO	2Tenente	Com Exercito 869353363832180805	250	Com Exercito	869353363832180805	250
18	Marcão Macarrão522474484398030868	522474484398030868	31 Horas 35 Minutos 24 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363807010852, 869353363815411757, 869353363815411760, 869887176073175050, 869353363609899038	aaaa	31				31	31	Manter	Sobe	Aspirante
19	! ?HinataQTKiller愛☪☁786731624624029707	786731624624029707	31 Horas 17 Minutos 19 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363668598801, 869353363677020212, 869353363677020214, 869353363769290771, 869353363794444395, 869353363794444397, 869353363807010852, 869353363807010853, 869353363815411757, 869353363815411761, 870079612863012956, 869353363609899038	aaaa	31				31	31	Manter	FALSO	2Tenente
20	! ⛩Kawai409029983604965386	409029983604965386	30 Horas 41 Minutos 29 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363807010852, 869353363815411754, 869353363823816716, 869353363609899038	aaaa	30				30	30	Manter	FALSO	Capitao Tenente
21	! EXA Tobirama S2798384153635979354	798384153635979354	30 Horas 30 Minutos 6 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363752517672, 869353363752517679, 869353363769290763, 869353363769290764, 869353363769290770, 869353363769290771, 869353363781853250, 869353363781853251, 869353363781853252, 869353363794444390, 869353363794444395, 869353363807010852, 869353363807010853, 869353363815411757, 869353363823816720, 869353363832180806, 869957469038870638, 870079612863012956, 870309889619152906, 870320793165385728, 870330472213921883, 870486899452706816, 869353363609899038	aaaa	30				30	30	Manter	FALSO	Major
22	! ʟᴜᴄɪғᴇʀ ᴀɪɴᴅᴀ s2497824042498129930	497824042498129930	30 Horas 26 Minutos 10 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363668598801, 869353363677020210, 869353363677020214, 869353363794444389, 869353363807010852, 869353363815411754, 869353363832180799, 869353764727947324, 870079612863012956, 870486899452706816, 869353363609899038	aaaa	30				30	30	Manter	FALSO	Gen Divisao
23	kizzyᵇᶻʸ496090536512585738	496090536512585738	30 Horas 19 Minutos 9 Segundos		869353363609899039, 869353363609899040, 869353363609899042, 869353363609899044, 869353363635073066, 869353363769290770, 869353363781853249, 869353363781853250, 869353363781853252, 869353363807010852, 869353363815411754, 869353363823816717, 869353764727947324, 870079612863012956, 870309889619152906, 870320793165385728, 870486899452706816, 869353363609899038	aaaa	30				30	30	Manter	FALSO	Capitao de Corveta
24	Mobzera361205292559761409	361205292559761409	29 Horas 59 Minutos 52 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363781853250, 869353363807010852, 869353363815411757, 869353363815411760, 870718681196413018, 869353363609899038	aaaa	29				29	29	Rebaixar	Sobe	Aspirante
25	[£Hyouka]462847933797105671	462847933797105671	29 Horas 4 Minutos 39 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363677020214, 869353363769290771, 869353363794444390, 869353363807010852, 869353363815411754, 869353363815411761, 869353363832180800, 869353363609899038	aaaa	29				29	29	Rebaixar	FALSO	2Tenente
26	Alves667024452990533652	667024452990533652	28 Horas 41 Minutos 21 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073066, 869353363635073067, 869353363677020212, 869353363781853250, 869353363807010852, 869353363815411757, 869353363815411761, 870309889619152906, 869353363609899038	aaaa	28				28	28	Rebaixar	FALSO	2Tenente
27	! Tsu፝֟፟no望?797318512355835944	797318512355835944	28 Horas 9 Minutos 8 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073066, 869353363635073067, 869353363635073068, 869353363647651880, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020216, 869353363697983495, 869353363697983496, 869353363752517675, 869353363794444390, 869353363794444396, 869353363794444397, 869353363807010852, 869353363815411758, 869353363844780128, 869353363874131973, 869353363886710784, 869353363609899038	aaaa	28				28	28	Rebaixar	FALSO
28	é ? ????$ ?574144420124033024	574144420124033024	28 Horas 7 Minutos 17 Segundos		869353363609899039, 869353363609899040, 869353363677020212, 869353363807010852, 869353363815411753, 869353363823816716, 869353363832180800, 869353764727947324, 869887176073175050, 870486899452706816, 869353363609899038	aaaa	28				28	28	Rebaixar	FALSO	Capitao Tenente
Sheet1

Cell Formulas
Range	Formula
K3	K3	=G3+I3*2+J3/3
M3:M28	M3	=IF(L3>=30,"Manter","Rebaixar")
N3:N28	N3	=IF(O3="aspirante",IF(L3>=T3,"Sobe",IF(O3="2Tenente",IF(L3>=T3,"sobe"))))
O3:O28	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)
K4:K28	K4	=(G4)+(I4*2)+(J4/3)
L4:L28	L4	=INT(K4)
B3:B28	B3	=RIGHT(A3,18)
G3:G28	G3	=LEFT(C3,3)

Porem como pode ver existem alguns aspirantes dando falso quando sao verdadeiros, estou completamente bugado.

 

[Marcelo Branco]

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 AGREGAR é 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.

 

[lu1zuk]

Melhor eu fazer outro post para essa pergunta? \/ ou tudo bem continuar aqui? Envolve a mesma planilha.
E uma pergunta rapida. Eu vou agora fazer um "se" para todos os cargos no local do "sobe e desce(N)" é so eu fazer um "se" grande
por exemplo:
=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)

Tempo staff teste.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	Usuário	Id	Tempo	Ações	Cargos		Tempo		Mov F	Abrir.Mov	Horas	Total Inteiro	Checagem	Sobe ou Desce?	Cargo	Metas	Horas Metas
3	?ᵛᵘˡᵍᵒ ʀᴀʏ?732587448571265047	732587448571265047	45 Horas 13 Minutos 43 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363677020214, 869353363769290771, 869353363807010852, 869353363815411754, 869353363815411756, 869353363815411760, 870079612863012956, 870330472213921883, 869353363609899038	aaaa	45		1	2	47,7	29	Rebaixar	FALSO	Aspirante	Aspirante 869353363815411760	30	Aspirante	869353363815411760	30
4	ˡᵉᵛⁱ ღ750453670910623895	750453670910623895	41 Horas 32 Minutos 24 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363668598801, 869353363807010852, 869353363815411754, 869353363823816719, 869353764727947324, 869353363609899038	aaaa	41				41	41	Manter	FALSO	Capitao de Mar e Guerra	2Tenente 869353363815411761	35	2Tenente	869353363815411761	35
5	??????495630641396842508	495630641396842508	38 Horas 31 Minutos 43 Segundos		869353363609899039, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363752517681, 869353363769290771, 869353363781853249, 869353363781853251, 869353363781853252, 869353363794444395, 869353363807010852, 869353363807010854, 869353363815411757, 869353363823816718, 870486899452706816, 869353363609899038	aaaa	38				38	38	Manter	FALSO	Capitao de Fragata	1Tenente 869353363823816714	40	1Tenente	869353363823816714	40
6	୨?ᵇᵈᶜ Zøe S2 ᵐᵃᵍⁱᶜ548646519406854340	548646519406854340	37 Horas 42 Minutos 24 Segundos		869353363609899039, 869353363609899040, 869353363635073073, 869353363668598804, 869353363677020212, 869353363677020214, 869353363677020217, 869353363752517681, 869353363769290764, 869353363769290765, 869353363769290769, 869353363769290770, 869353363781853247, 869353363781853249, 869353363781853251, 869353363781853252, 869353363794444390, 869353363794444395, 869353363794444396, 869353363794444397, 869353363807010852, 869353363815411754, 869353363823816721, 869353764727947324, 870079612863012956, 870309889619152906, 870486899452706816, 869353363609899038	aaaa	37				37	37	Manter	FALSO	Tenente Coronel	Capitao 869353363823816715	45	Capitao	869353363823816715	45
7	?ℎ?? ?? ???? s2726176887126229053	726176887126229053	36 Horas 32 Minutos 50 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363677020214, 869353363697983495, 869353363769290763, 869353363769290764, 869353363769290770, 869353363769290771, 869353363781853247, 869353363781853249, 869353363781853250, 869353363794444390, 869353363807010852, 869353363815411757, 869353363823816721, 870309889619152906, 870486899452706816, 869353363609899038	aaaa	36				36	36	Manter	FALSO	Tenente Coronel	Capitao Tenente 869353363823816716	50	Capitao Tenente	869353363823816716	50
8	! astrx Dark痛 s2692778724499783712	692778724499783712	36 Horas 24 Minutos 1 Segundos		869353363609899039, 869353363609899040, 869353363609899042, 869353363635073066, 869353363635073073, 869353363668598799, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020215, 869353363731533895, 869353363769290770, 869353363781853249, 869353363781853250, 869353363781853251, 869353363781853252, 869353363794444389, 869353363794444390, 869353363794444395, 869353363794444396, 869353363794444397, 869353363807010852, 869353363807010853, 869353363815411754, 869353363832180801, 869353363844780126, 869353764727947324, 869957469038870638, 870030413609975880, 870079612863012956, 870309889619152906, 869353363609899038	aaaa	36				36	36	Manter	FALSO	Gen Exercito	Capitao de Corveta 869353363823816717	55	Capitao de Corveta	869353363823816717	55
9	? Frostzin ?☪☁615933064253669399	615933064253669399	36 Horas 19 Minutos 26 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363769290762, 869353363769290771, 869353363781853250, 869353363807010852, 869353363815411755, 869353363815411757, 869353363823816717, 869353363844780131, 869353764727947324, 869928452046864495, 870320793165385728, 870326462220234783, 870330472213921883, 870486899452706816, 869353363609899038	aaaa	36				36	36	Manter	FALSO	Capitao de Corveta	Capitao de Fragata 869353363823816718	60	Capitao de Fragata	869353363823816718	60
10	MarceloXD2709322462408557723650	322462408557723650	36 Horas 17 Minutos 27 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363656020029, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020214, 869353363697983495, 869353363697983496, 869353363752517679, 869353363769290770, 869353363769290771, 869353363781853247, 869353363781853249, 869353363781853250, 869353363781853251, 869353363794444389, 869353363794444390, 869353363794444395, 869353363794444396, 869353363794444397, 869353363807010852, 869353363815411758, 869353363832180805, 869353363844780128, 869353363861536791, 869353764727947324, 870030413609975880, 870309889619152906, 870330472213921883, 870718681196413018, 869353363609899038	aaaa	36				36	36	Manter	FALSO	Com Exercito	Capitao de Mar e Guerra 869353363823816719	60	Capitao de Mar e Guerra	869353363823816719	60
11	☹S U M I D A☹816314934873817125	816314934873817125	35 Horas 41 Minutos 30 Segundos		869353363609899039, 869353363609899040, 869353363635073067, 869353363647651883, 869353363647651889, 869353363668598798, 869353363668598804, 869353363677020212, 869353363677020214, 869353363677020217, 869353363807010852, 869353363815411754, 869353363815411760, 870079612863012956, 869353363609899038	aaaa	35				35	35	Manter	FALSO	Aspirante	Major 869353363823816720	65	Major	869353363823816720	65
12	?????ᵇᶻʸ766755329223426099	766755329223426099	33 Horas 51 Minutos 38 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363677020214, 869353363781853252, 869353363794444390, 869353363807010852, 869353363815411754, 869353363823816717, 869353363609899038	aaaa	33				33	33	Manter	FALSO	Capitao de Corveta	Tenente Coronel 869353363823816721	75	Tenente Coronel	869353363823816721	75
13	krixi451482252103385099	451482252103385099	33 Horas 34 Minutos 41 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363668598801, 869353363677020210, 869353363677020214, 869353363677020217, 869353363697983495, 869353363794444393, 869353363807010852, 869353363807010853, 869353363815411754, 869353363815411759, 869353363832180801, 869353764727947324, 870320793165385728, 869353363609899038	aaaa	33				33	33	Manter	FALSO	Gen Exercito	Coronel 869353363823816722	80	Coronel	869353363823816722	80
14	! Sebastian☁675931948991905804	675931948991905804	33 Horas 24 Minutos 26 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363677020210, 869353363677020214, 869353363731533899, 869353363731533900, 869353363781853252, 869353363807010852, 869353363807010853, 869353363815411754, 869353363832180799, 869353363844780126, 869353363861536791, 869353764727947324, 869353363609899038	aaaa	33				33	33	Manter	FALSO	Gen Divisao	Gen Brigada 869353363832180797	125	Gen Brigada	869353363832180797	125
15	! Srta.Sasori 痛☪772645993728049184	772645993728049184	33 Horas 9 Minutos 4 Segundos		869353363609899039, 869353363609899040, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020217, 869353363807010852, 869353363815411754, 869353363823816720, 869353363609899038	aaaa	33				33	33	Manter	FALSO	Major	Gen Divisao 869353363832180799	150	Gen Divisao	869353363832180799	150
16	! Doutoraa s2 ⋆｡ʚଓ ?540871271710326794	540871271710326794	32 Horas 34 Minutos 57 Segundos		869353363609899039, 869353363609899040, 869353363609899042, 869353363609899044, 869353363635073066, 869353363635073067, 869353363635073068, 869353363635073072, 869353363647651886, 869353363668598798, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020217, 869353363731533894, 869353363752517675, 869353363781853251, 869353363794444389, 869353363794444390, 869353363794444393, 869353363794444395, 869353363794444396, 869353363794444397, 869353363807010852, 869353363807010853, 869353363815411754, 869353363815411759, 869353363874131971, 869353363609899038	aaaa	32				32	32	Manter	FALSO		Gen Exercito 869353363832180801	200	Gen Exercito	869353363832180801	200
17	???665124641924186133	665124641924186133	31 Horas 50 Minutos 15 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363769290770, 869353363769290771, 869353363807010852, 869353363815411754, 869353363815411761, 869957469038870638, 870079612863012956, 870309889619152906, 870320793165385728, 870486899452706816, 869353363609899038	aaaa	31				31	31	Manter	FALSO	2Tenente	Com Exercito 869353363832180805	250	Com Exercito	869353363832180805	250
18	Marcão Macarrão522474484398030868	522474484398030868	31 Horas 35 Minutos 24 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363807010852, 869353363815411757, 869353363815411760, 869887176073175050, 869353363609899038	aaaa	31				31	31	Manter	Sobe	Aspirante
19	! ?HinataQTKiller愛☪☁786731624624029707	786731624624029707	31 Horas 17 Minutos 19 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363668598801, 869353363677020212, 869353363677020214, 869353363769290771, 869353363794444395, 869353363794444397, 869353363807010852, 869353363807010853, 869353363815411757, 869353363815411761, 870079612863012956, 869353363609899038	aaaa	31				31	31	Manter	FALSO	2Tenente
20	! ⛩Kawai409029983604965386	409029983604965386	30 Horas 41 Minutos 29 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363807010852, 869353363815411754, 869353363823816716, 869353363609899038	aaaa	30				30	30	Manter	FALSO	Capitao Tenente
21	! EXA Tobirama S2798384153635979354	798384153635979354	30 Horas 30 Minutos 6 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363752517672, 869353363752517679, 869353363769290763, 869353363769290764, 869353363769290770, 869353363769290771, 869353363781853250, 869353363781853251, 869353363781853252, 869353363794444390, 869353363794444395, 869353363807010852, 869353363807010853, 869353363815411757, 869353363823816720, 869353363832180806, 869957469038870638, 870079612863012956, 870309889619152906, 870320793165385728, 870330472213921883, 870486899452706816, 869353363609899038	aaaa	30				30	30	Manter	FALSO	Major
22	! ʟᴜᴄɪғᴇʀ ᴀɪɴᴅᴀ s2497824042498129930	497824042498129930	30 Horas 26 Minutos 10 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363668598801, 869353363677020210, 869353363677020214, 869353363794444389, 869353363807010852, 869353363815411754, 869353363832180799, 869353764727947324, 870079612863012956, 870486899452706816, 869353363609899038	aaaa	30				30	30	Manter	FALSO	Gen Divisao
23	kizzyᵇᶻʸ496090536512585738	496090536512585738	30 Horas 19 Minutos 9 Segundos		869353363609899039, 869353363609899040, 869353363609899042, 869353363609899044, 869353363635073066, 869353363769290770, 869353363781853249, 869353363781853250, 869353363781853252, 869353363807010852, 869353363815411754, 869353363823816717, 869353764727947324, 870079612863012956, 870309889619152906, 870320793165385728, 870486899452706816, 869353363609899038	aaaa	30				30	30	Manter	FALSO	Capitao de Corveta
24	Mobzera361205292559761409	361205292559761409	29 Horas 59 Minutos 52 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073067, 869353363635073068, 869353363781853250, 869353363807010852, 869353363815411757, 869353363815411760, 870718681196413018, 869353363609899038	aaaa	29				29	29	Rebaixar	Sobe	Aspirante
25	[£Hyouka]462847933797105671	462847933797105671	29 Horas 4 Minutos 39 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363677020214, 869353363769290771, 869353363794444390, 869353363807010852, 869353363815411754, 869353363815411761, 869353363832180800, 869353363609899038	aaaa	29				29	29	Rebaixar	FALSO	2Tenente
26	Alves667024452990533652	667024452990533652	28 Horas 41 Minutos 21 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073066, 869353363635073067, 869353363677020212, 869353363781853250, 869353363807010852, 869353363815411757, 869353363815411761, 870309889619152906, 869353363609899038	aaaa	28				28	28	Rebaixar	FALSO	2Tenente
27	! Tsu፝֟፟no望?797318512355835944	797318512355835944	28 Horas 9 Minutos 8 Segundos		869353363609899039, 869353363609899040, 869353363609899044, 869353363635073066, 869353363635073067, 869353363635073068, 869353363647651880, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020216, 869353363697983495, 869353363697983496, 869353363752517675, 869353363794444390, 869353363794444396, 869353363794444397, 869353363807010852, 869353363815411758, 869353363844780128, 869353363874131973, 869353363886710784, 869353363609899038	aaaa	28				28	28	Rebaixar	FALSO
28	é ? ????$ ?574144420124033024	574144420124033024	28 Horas 7 Minutos 17 Segundos		869353363609899039, 869353363609899040, 869353363677020212, 869353363807010852, 869353363815411753, 869353363823816716, 869353363832180800, 869353764727947324, 869887176073175050, 870486899452706816, 869353363609899038	aaaa	28				28	28	Rebaixar	FALSO	Capitao Tenente
Sheet1

Cell Formulas
Range	Formula
K3	K3	=G3+I3*2+J3/3
M3:M28	M3	=IF(L3>=30,"Manter","Rebaixar")
N3:N28	N3	=IF(O3="aspirante",IF(L3>=T3,"Sobe",IF(O3="2Tenente",IF(L3>=T3,"sobe"))))
O3:O28	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)
K4:K28	K4	=(G4)+(I4*2)+(J4/3)
L4:L28	L4	=INT(K4)
B3:B28	B3	=RIGHT(A3,18)
G3:G28	G3	=LEFT(C3,3)

Porem como pode ver existem alguns aspirantes dando falso quando sao verdadeiros, estou completamente bugado.

Eu achei o primeiro erro é que quando ele passa para a proxima ele esta trocando os valores de comparação entao tentei fixa-los
usando dessa forma:
=SE(O3="Aspirante";SE(L3>=T$3:T$17;"Sobe";SE(O3="2Tenente";SE(L3>=T$3:T$17;"sobe")))) porem ainda possuo alguns erros

Tempo staff teste.xlsx
	L	M	N	O	P	Q	R	S	T
2	Total Inteiro	Checagem	Sobe ou Desce?	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
18	31	Manter	#VALOR!	Aspirante
19	31	Manter	FALSO	2Tenente
20	30	Manter	FALSO	Capitao Tenente
21	30	Manter	FALSO	Major
22	30	Manter	FALSO	Gen Divisao
23	30	Manter	FALSO	Capitao de Corveta
24	29	Rebaixar	#VALOR!	Aspirante
25	29	Rebaixar	FALSO	2Tenente
26	28	Rebaixar	FALSO	2Tenente
27	28	Rebaixar	FALSO
28	28	Rebaixar	FALSO	Capitao Tenente
29	28	Rebaixar	FALSO	Gen Brigada
30	28	Rebaixar	FALSO	Gen Divisao
31	27	Rebaixar	FALSO	Capitao de Mar e Guerra
32	27	Rebaixar	FALSO	2Tenente
33	27	Rebaixar	FALSO	Capitao de Corveta
34	27	Rebaixar	FALSO	Capitao
35	27	Rebaixar	FALSO	Capitao de Corveta
36	27	Rebaixar	FALSO	Gen Exercito
37	27	Rebaixar	FALSO	Capitao
38	26	Rebaixar	FALSO	Gen Exercito
39	25	Rebaixar	#VALOR!	Aspirante
Sheet1

Cell Formulas
Range	Formula
M3:M39	M3	=IF(L3>=30,"Manter","Rebaixar")
N3:N39	N3	=IF(O3="Aspirante",IF(L3>=T$3:T$17,"Sobe",IF(O3="2Tenente",IF(L3>=T$3:T$17,"sobe"))))
O3:O39	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)
L18:L39,L12:L16,L4:L10	L4	=INT(K4)