Consulta de fórmula simples do MS Excel

Question

Eu tenho uma planilha do Excel que tem quase 100 colunas com os nomes do cabeçalho da coluna:

Nestaimagem,11valores/nomesdecabeçalhodecoluna,começandodeAparaL.(AestáemB2).Oqueeuqueroéumafórmulaparacolocarnacoluna'Resultados'X,queexibetodososnomesdosCabeçalhosdasColunasquetenhamvalor"1". Você pode ver que o primeiro valor da célula "Resultados" tem A, C, F, H, J porque eles contêm "1".

Eu tentei fazer isso com uma fórmula IF , mas usando dessa forma eu preciso colocar IF para cada valor de coluna, o que será uma tarefa desagradável, já que tenho mais de 100 colunas, então quero um mecanismo para simplesmente selecionar o intervalo e aplicá-lo.

Não é especialista em VBA, mas se tiver que ser a solução, então, dado o código, posso copiar e colar isso.

Qualquer dúvida, avise-me por favor.

    

Answer 1

Acho que isso deve ser suficiente.

A fórmula da linha "a1" é: =CONCATENATE(O2,IF(B2=1,B$1,"")) , que será colada em 2 colunas à direita da última coluna neste exemplo. Copie essa fórmula (com sua referência apropriada) e, em seguida, cole-a em todas as células restantes após a fórmula inicial estar em vigor. Observe o símbolo $, isso é importante para copiar / colar.

=CONCATENATE(O2,IF(B2=1,B$1,""))

- a referência O2 acima, no seu caso, pode ser FW , FX , ... ou AAB dependendo de quantas colunas você tem, é claro.

Em seguida, basta referenciar a última coluna como RESULTADO; mostra na metade inferior da imagem. Neste exemplo: =AA2

Você terá que pular uma coluna. Aqui está uma captura de tela.

    

Answer 2

Você precisa criar uma UDF de função definida pelo usuário no VBA e usá-la como função na coluna X O código é o seguinte, crie um novo módulo em sua pasta de trabalho

Public Function concatcell(Lookupvalue As String, LookupRange As Range, RowNumber As Integer)
Dim i As Long
Dim J As Long e Dim Result As String
Result = "" e J = LookupRange.Columns.Count e For i = 1 To J

If LookupRange.Cells(RowNumber, i) = Lookupvalue Then em Result = Result & LookupRange.Cells(1, i) & ";" em End If em Next i em concatcell = Result em End Function

Em X2, escreva: =concatcell(1,$B$1:$L2,ROW())
arraste-o para baixo
$ B $ 1: $ L2 são as 2 primeiras linhas que mudam para suas referências de dados, mas mantêm $ como é para fazer o intervalo mudar automaticamente (a Fila B contém o rótulo a ser retornado) 1 é o valor que você está procurando. Row () é o número da linha a ser alterado automaticamente à medida que você o arrasta para baixo
Lembre-se de salvar a pasta de trabalho como a macro ativada (para tornar o trabalho da próxima vez que você abri-la)

    

Answer 3

A fórmula que você precisa usar é bastante simples, embora longa. Aqui está a fórmula (que deve ir em N2 e ser arrastada para baixo até onde você tem dados):

=IF(B2>0,$B$1&";","")&IF(C2>0,$C$1&";","")&IF(D2>0,$D$1&";","")&IF(E2>0,$E$1&";","")&IF(F2>0,$F$1&";","")&IF(G2>0,$G$1&";","")&IF(H2>0,$H$1&";","")&IF(I2>0,$I$1&";","")&IF(J2>0,$J$1&";","")&IF(K2>0,$K$1&";","")&IF(L2>0,$L$1&";","")&IF(M2>0,$M$1&";","")

(É um pouco diferente do seu exemplo, porque vejo que você perdeu o cabeçalho D .)

O que ele faz é simples, pois cada coulmn verifica a célula nessa linha se ela tem um valor maior que zero, se true ela traz o nome coulmn com um ponto-e-vírgula, e se não deixa vazio.

Eu apliquei um documento de amostra com a fórmula pronta aqui se você quiser ver link