Formula para colocar determinadas colunas de uma Tabelas, uma debaixo da outra em sequencia, em outro local de outra planilha.

Question

Bom dia.

1-Gostaria de fazer com formulas, nao em VBA, para que aparecesse dados das colunas A,B e C da Tabela de nome Tabela1, em sequencia como mostrado na coluna Q da planilha. Cada conjunto de dados, comecam com um cabecalho como Name A,Name B e Name C.

A quantidades de dados das colunas A,B e C da Tabela(qt mostradas nas celulas H2,K2 e N2), variam conforme escolhe a coluna 1, 2, ou 3 da Tabela, mudando a formula da coluna auxiliares Ax1, Bx1 e Cx1 que e mostrados na linha 24 na formula F4=IF([@1]="A",ROW(),"") de @1 para outros dados como @2 ou @3.

Fiz uma tentativa, mas sem sucesso, mostrados na coluna R da planilha com formulas indicada na linha 28 a 30 da planilha. Na formula, o intervalo indicado como NameA/B/C, foram definidos como dinamicas com formula =OFFSET(Sheet2!$H$4,0,0,Sheet2!$H$2,1), porem tive sucesso so na primeira parte que corresponde a coluna A da Tabela, e as colunas B e C nao deram resultado, por isso coloquei aspas duplas nos intervalos "NameB" e "NameC". Se retirar as aspas como no NameA, dara resultado inesperado. Saberia como resolver esses casos?.

2-Outra coisa que gostaria de saber, e como selecionar as colunas 1,2 e 3 da Tabela, alterando indiretamente os dados na celula A1 da planilha? Sei que se usar numeros em cabecalhos de Tabelas, os numeros sao transformados em strings, entao ja deixei na celula B1 o dados da celula A1 como string com formula mostrada na linha 19 e tentei na formula da linha 24, uma concatencao como =IF("[@"&B1@"]"="A",ROW(),"") sem sucesso.

Todas as formulas usadas na planilha estao na linha 19 a 30.  O ROW(Tabela1[#見出し]) significa numero da linha em relacao a planilha do cabecalho da Tabela.

Desde ja agradeco a quem possa ajudar.

Obrigado

Tadao

Answer 1

Boa tarde Tadao,

Inicialmente, aparentemente não existem problemas no seu detalhamento das fórmulas.

Já a utilização do caractere "|" na fórmula da formatação condicional é para evitar problemas com os possíveis nomes colocados na coluna Name (normalmente nomes incomuns). Infelizmente, a respectiva fórmula ainda estava incompleta. Veja abaixo a fórmula correta.

=ÉNÚM(LOCALIZAR("|"&$B2&"|";"|Name A|Name B|Name C|"))

Veja nos  exemplos da próxima figura os possíveis argumentos da função Localizar (texto_procurado e no_texto) e os respectivos resultados:

Note que se existir o nome (não comum) Name Bn e eu não utilizar o caractere "|" no argumento no_texto (Name AName BName C) do comando Localizar, o Excel vai localizar  o respectivo nome (Resultado - 7) e vai coloca-lo posteriormente em Negrito e Itálico. Logo, a utilização do caractere "|" é para evita esse tipo de problema, pois agora eu estou procurando por |Name Bn| e não somente Name Bn e no argumento no_texto eu tenho agora |Name A|Name B|Name C| e não apenas Name AName BName C. Logo, o Excel não vai encontrar o texto_procurado |Name Bn| (Resultado - #VALOR!) e não vai colocar Name BN em Negrito e Itálico.

Note também que  nos exemplos de nomes Name e Ame, o Excel também os colocariam em Negrito e Itálico se na fórmula não for utilizado o caractere "|".

Markmzz

Answer 2

Bom dia Tadao,

Dois itens para reflexões:

1. Eu evito deixar que uma fórmula retorne um erro na Formatação Condicional. Por essa razão que utilizei a função ÉNÚM.
2. Faça alguns testes com as fórmulas do exemplo da próxima figura pressionando a tecla <F9> (tecla para recalculo da pasta de trabalho) várias vezes e observando os resultados apresentados.

Markmzz

Answer 3

Ola Markmzz, obrigado por enviar o arquivo.

Abaixei o arquivo e abri no meu Excel e transformou as formulas automaticamente e esta funcionando como abaixo, onde eu mudei o tamanho das colunas e referenciei da coluna de 1 para 3 mudando a formula das colunas A/B/Cx1 de =IF([@1]="A/B/C",ROW(),"") para =IF([@3]="A/B/C",ROW(),""). Obrigado

Vou verificar onde devo ter errado(talvez uma virgura, ponto ou espaco) e depois eu te informo.

Outra coisa que queria saber sobre a minha segunda duvida da postagem, nao daria para referenciar o 1,2,3 da formula IF([@3]="A/B/C",ROW(),"") na celula B1?... dai mudaria os numeros na celula A1 com ScrollBar ou SpinButton para mudar de colunas 1/2/3, que na realidade sao dias de um calendario que vao de 1 a 31 dias? Ja tentei "[@"  & B1 & "]" e outros.... mas sem sucesso.

Uma observacao que fiz na mensagem anterior que e:

"Desculpe-me mas, na minha postagem, coloquei a posicao do resultado ao lado da Tabela na mesma planilha para poder mostrar melhor quando postar, ma na realidade, preciso colocar essas formulas em outra planilha e outro endereco."

Colei a formula da coluna R da figura acima em varios locais de outra planila e deu resultado como abaixo.

Nao daria para ela fica como absoluto em vez de relativo?

Tadao

Tadao,

Vamos lá:

1. Eu crie uma validação de dados para a célula A1 somente para 3 dias (faça as modificações necessárias para 31 dias). Além disso, fiz uma pequena modificação na sua fórmula da célula G4 somente para 3 dias (faça as modificações necessárias para 31 dias). Verifique e estude com calma cada fórmula.
2. Criei também uma nova planilha com o nome de Result e coloquei nela a fórmula de resultado. Além disso, fiz algumas cópias da referida fórmula e os ajustes necessários para que as cópias continuem funcionando. Verifique e estude com cuidado a fórmula. Infelizmente, algumas partes da fórmula não podem ser absolutas, porém é só fazer os ajustes necessários.

Atualizei o link do arquivo para a nova versão no meu post anterior. Verifique.

Markmzz

Answer 4

Ola Markmzz, obrigado pelo retorno.

1- Entendi a sua sugestao,....so mudar o raciocinio e a formula fica mais compacta,nao?Talvez o que escrevi tinham gordurinhas desnecessarias a mais nao? Obrigado, vou comecar a pensar de forma diferente tambem.

2- Nao entendi muito bem a sua sugestao, voce quis dizer para trocar o nome do titulo da tabela, ja na origem com Validacao de Dados? Essa seria uma boa alternativa,nao?, mas pretendo deixar essa coluna H escondida do usuario e alem do mais nao queria que ele mexesse na Tabela, e penso em deixar protegido para qualquer eventualidade. Outra alternativa seria usar formula de = para uma celula em que ele possa mexer, mas a Tabela nao aceita formulas no cabecario,nao?

Por isso resolvi fazer essa tabela de conversao, dai ele pode escrever em Japones ou Ingles que a formatacao de celula vai funcionar pois usei a funcao Indireto na formatacao de celula como abaixo.

=OR($Z5=INDIRECT($AG$5),$Z5=INDIRECT($AG$6),$Z5=INDIRECT($AG$7),$Z5=INDIRECT($AG$8),$Z5=INDIRECT($AG$8),$Z5=INDIRECT($AG$10))

Aproveitando,..... nao tem alguma forma de configurar para poder aumentar esse campo para colocar a formula na formatacao conndicional?. Quando a formula e muito comprida, fica ruim de ver todo o conteudo.

Agradeco mais uma vez pelas sugestoes e comentarios.

Tadao

Olá Tadao,

1. Ok então.
2. Se o usuário não deve alterar a tabela então deixa como está.
3. Infelizmente, eu não sei como modificar o tamanho da caixa de diálogo da Validação de Dados.

Markmzz

Answer 5

Ola Markmzz, ainda nao consigo ver os resultados conforme figuras abaixo.

Tadao,

Abaixo está o link para o meu arquivo (TabNames-032.xlsx) no OneDrive.

TabNames-032.xlsx (atualizado em 04/01/2018 - 06:57h)

Faça alguns testes com ele e me informe se funcionou.

Markmzz