Como sinalizar linhas com o padrão não em branco, em branco, não em branco usando o Excel

Question

Gostaria de sua ajuda com algo que venho tentando descobrir nos últimos dois dias.

Eu tenho dados com milhares de linhas e várias colunas. Eu quero inserir uma coluna com uma fórmula (matriz) que sinaliza todas as linhas que possuem "furos"; ou seja, qualquer linha que tenha uma ou mais colunas em branco entre colunas não vazias, como estes exemplos:

Meuconjuntodedadosrealpodeterentre3e7colunas.Paraoexemploacima(4colunas),tenteiaseguintefórmula:

IF(OR(AND(SUM(B13:C13)=0,COUNT(A13,D13)>1),AND(B13=0,COUNT(A13,C13)>1),AND(C13=0,COUNT(B13,D13)>1)),"Issue","OK") 

Parece funcionar, mas eu preciso de algo que funcione com mais colunas, texto ou dados numéricos e que leve em consideração a possibilidade de outras colunas não segmentadas cercarem os dados. Não me sinto confiante de que posso identificar todos os padrões possíveis (permutações) e depois modificar com precisão essa fórmula. Eu preciso de um método mais robusto para fazer isso.

    

Answer 1

Aqui está uma solução geral. Eu posicionado propositalmente no meio da folha para ilustrar o manuseio de colunas em branco antes ou depois do intervalo.

Euuseialgumascolunasauxiliaresparafacilitarasoluçãoeasoluçãodeproblemas.Asfórmulasficamcomplicadassevocêtentarfazertudoemumafórmula,emborapossaconsolidarissoemumaúnicafórmulaseforumglutãoparapunição.

Emvezdeinserirnovamenteseusdados,useiquatrocolunas(CaF)dosnúmerosde1a4,comdiferentespadrõesdeelementosausentesnaslinhasparaverificarasváriascondições.

Dadosdeentrada

Aprimeiraeaúltimacolunasdedadossãoinseridascomovariáveis,paraquevocêpossausarquantascolunasdesejar,posicionadasemqualquerlugardesejado.EuescolhiarbitrariamenteI1eK1paraarmazenarasprimeiraseúltimasletrasdacoluna.

Identifiqueaprimeiracolunapreenchida

Paracadalinha,vocêprecisaencontraroprimeiroeúltimovalorreal,porque"buracos" são contados apenas entre eles. Eu usei a coluna H para identificar a primeira coluna com um valor na linha. Eu comecei meus dados na linha 3, então o H3 contém:

=MATCH(TRUE,LEN(C3:F3)>0,0)+COLUMN(INDIRECT($I$1&":"&$I$1))-1

EDIT: Note que eu testei isso é o LO Calc, que muda o TRUE nesta fórmula para 1 . No Calc, essa fórmula se torna:

=MATCH(1,LEN(C3:F3)>0,0)+COLUMN(INDIRECT($I$1&":"&$I$1))-1

qual foi a fórmula original nesta resposta, e acontece que o 1 dá indigestão ao Excel. Para o Excel, você precisará da primeira fórmula.

Esta é uma fórmula de matriz, então ela precisa ser digitada usando Ctrl + Deslocar + Enter .

Incorporar a função INDIRECT dentro da função LEN não funciona, então eu tive que codificar o intervalo. Você precisaria modificar o intervalo da coluna se usasse um número diferente de colunas.

A função COLUMN e -1 ajustam a posição inicial do intervalo.

Número de valores, se não houver furos

Coluna I é o número de colunas desde o primeiro valor até o último (o número potencial de valores, se não houver furos). A célula I3 contém:

=LOOKUP(9.99E+307,INDIRECT($I$1&ROW()&":"&$K$1&ROW()))+COLUMN(INDIRECT($I$1&":"&$I$1))-H3

A função LOOKUP identifica a última coluna que contém um valor. Novamente, ele adiciona um ajuste para a localização do intervalo de dados na planilha. Subtrai o número da primeira coluna preenchida do último.

Número de valores preenchidos

A coluna J contém a contagem de valores no intervalo preenchido. A célula J3 contém:

=COUNT(INDIRECT($I$1&ROW()&":"&$K$1&ROW()))

Isso lhe dá o que você precisa para identificar seus buracos.

Identifique linhas com furos

O resultado desejado é o que está na coluna G. A célula G3 contém:

=IF(I3>J3,"hole","")

EDIT: Eu fiz alguma consolidação e consegui isso para uma única fórmula. Você pode usar isso na célula G3:

=LOOKUP(9.99E+307,INDIRECT($I$1&ROW()&":"&$K$1&ROW()))
 +COLUMN(INDIRECT($I$1&":"&$I$1))-MATCH(TRUE,LEN(C3:F3)>0,0)
 -COLUMN(INDIRECT($I$1&":"&$I$1))+1
 >COUNT(INDIRECT($I$1&ROW()&":"&$K$1&ROW()))

Eu adicionei quebras de linha para facilitar a leitura. Se você deseja copiar e colar, você precisará removê-los e o espaço extra.

Esta é uma fórmula de matriz, então você precisa entrar com Ctrl + Deslocar + Enter . O resultado é TRUE ou FALSE para saber se existe um buraco:

    

Answer 2

Eu inventei uma nova fórmula que funciona melhor do que a fórmula original que eu criei. Esta é a nova fórmula:

SUMPRODUCT(--NOT(ISBLANK(OFFSET(B2:E2,0,1))),--ISBLANK(B2:E2),--NOT(ISBLANK(OFFSET(B2:E2,0,-1))))+SUMPRODUCT(--(SUM(C2:D2)=0),--(SUM(B2,E2)>0))

Eu me sinto mais confiante de que ele pega todos os padrões não em branco, em branco {, em branco} e não em branco ao usar 4 colunas. Mas aqui estão as duas principais questões:

• ainda é uma solução específica para um número específico de colunas versus algo que pode funcionar para qualquer número de colunas
• Eu tenho que adicionar colunas em branco para trabalhar com a função de deslocamento. Neste caso, adicionei uma coluna em branco antes e depois do intervalo. Isso pode ser um problema porque normalmente tenho outras colunas que não são de interesse para o problema em questão, localizadas antes e depois do intervalo.

A nova fórmula que eu criei não é uma solução ideal, mas quando comparada à fórmula original, pelo menos, fornece uma estrutura que pode ser mais facilmente ajustada e dimensionada para acomodar mais colunas.

Estou compartilhando esta solução na esperança de motivar os outros a encontrar uma solução mais ideal.