Excel-Generate Total para pares bidirecionais

0

Eu tenho pares em meus dados ... A-B é equivalente a B-A. Então, se minha mesa for

A-B 20
B-D 20
C-F 10
E-F 10
G-H 15
B-A 10
F-E 20

Estou procurando uma maneira de resumir esses dados para que eu saiba que

B-D 20
C-F 10
G-H 15
A-B-B-A 30
E-F-F-E 30

O ABBA e o EFFE não são importantes desde que eu saiba o total de A e B e o total de E & F.

    
por Matt 21.11.2013 / 21:58

1 resposta

0

Suponho que você esteja falando sobre custos de viagem de ida e volta onde os custos das pernas individuais são desiguais devido a estradas de sentido único, portagens, altitude, fusos horários, ventos e / ou correntes. Isso não afeta a resposta; apenas ajuda a entender o problema.

Suponho também que seus dados são exibidos como " A-B ", " B-D " etc. na coluna A e os valores numéricos correspondentes (custos) na coluna B (e que todas as outras colunas estão disponíveis).

Defina a célula C1 como =FIND("-", A1) . (É claro que se os seus dados começarem abaixo de Linha 1 , ajuste os números de linha de forma correspondente.) Isso localiza o hífen no designador de rota unidirecional. Veja nota abaixo.

Defina a célula D1 como =RIGHT(A1, LEN(A1)-C1) & "-" & LEFT(A1, C1-1) . Isso gera o caminho inverso; Por exemplo, se A1 for " A-B ", D1 será avaliado como " B-A ". Nota: se os dados da coluna A forem realmente toda a forma (letra única) (hífen) (letra única) , então C será sempre 2, LEN(A1) será sempre 3, e você pode simplificar D1 para =RIGHT(A1, 1) & "-" & LEFT(A1, 1) .

Defina a célula E1 como =IF(A1<D1, A1 & "-" & D1, D1 & "-" & A1) . Isso gera a rota de ida e volta; Por exemplo, E1 será avaliado como " A-B-B-A ". Veja nota abaixo.

Arraste / preencha as fórmulas em Colunas C - E para baixo para cobrir todos os dados. Nota: A coluna E contém uma rota de ida e volta classificada , então E6 também contém “ A-B-B-A ” (não “ B-A-A-B ”).

Classificar por coluna E . (Você pode copiar seus dados primeiro, por exemplo, para outra planilha.) Insira uma linha em branco acima dos dados, se ainda não houver um.

Agora defina F2 para =IF(E2=E3, "", IF(E1=E2, E2, A2)) e G2 para =IF(E1=E2, G1+B2, B2) . Arraste / preencha para cobrir todos os dados. Sua planilha deve ficar assim:

FiltrenacolunaFefiltreosespaçosembranco:

Isso não valida seus dados. Se você tem várias linhas que dizem " P-Q ", essa solução simplesmente adicionará todas e informará a soma como o total de " P-Q-Q-P ".

Se você quiser classificar as rotas de sentido único antes das rotas de ida e volta, essa capacidade pode ser adicionada.

    
por 25.11.2013 / 16:50