Preencher automaticamente valores com base em várias dependências no Excel

Question

Eu já tenho três colunas com informações preenchidas. O valor na Coluna C depende de dois identificadores das Colunas A e amp; B (ou seja, George é sempre o vendedor da Apples in America, mas Sarah é a vendedora da Beets).

Eu tenho dados suficientes nas linhas 1-8 que eu quero que as células de 9 em diante para começar a preencher automaticamente as informações necessárias cada vez que eu inserir uma nova linha.

Assim, se eu inserir "Beets" em A9 e "Australia" em B9, então C9 deve puxar o valor "Amy". Eu vou puxar esse valor para baixo para preencher automaticamente o restante da tabela para futuras linhas a serem adicionadas automaticamente.

Eu usei a função INDIRECT (ROW-1) para referenciar toda a coluna acima da célula com a fórmula para obter os dados corretos, se isso faz sentido ...

       A         B         C
1   Product  Country    Contact 
2   Apple    Australia  John
3   Apple    America    George
4   Beets    Australia  Amy
5   Beets    America    Sarah
6   Carrot   Australia  Greg
7   Carrot   America    Belinda
8   Apple    Australia  John 
9   Beets    Australia  _Formula:_

=INDEX((INDIRECT("C2:C"&ROW()-1)),MATCH(A10,IF((INDIRECT("B2:B"&ROW()-1))=B10,(INDIRECT("A2:A"&ROW()-1)))),0)

    

Answer 1

Aqui está uma resposta que parece funcionar:

=VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 2, 2), 2, 0)

Trabalhando de dentro para fora:

• VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0) -1, 0, 2, 2), 2, 0)
MATCH(A9, A$2:A$7, 0) encontra A9 ( Beets ) no intervalo A2:A7 { Apple , Apple , Beets , Beets , Carrot , Carrot } produzindo um valor de índice no intervalo 1-6. Obviamente, porque A2 = A3 , A4 = A5 e A6 = A7 , o valor do índice será sempre 1, 3 ou 5; para Beets , é 3.
• MATCH(A9, A$2:A$7, 0)-1 remapeia o acima para estar no intervalo 0-5 (especificamente, 0, 2 ou 4; para Beets , é 2).
• OFFSET(B$2:C$7, the_above, 0, 2, 2) diz
  • pegue a B2:C7 region,
  • do canto superior esquerdo ( B2 ), desça duas linhas (desde a primeira ocorrência de Beets em A2:A7 está na terceira linha, isso significa que devemos descer duas (3-1) linhas da linha 2) e ir para a direita zero colunas, chegando na célula B4 ,
  • e depois uma região 2 × 2 a partir desse ponto. que nos leva ao intervalo B4:C5 , que é

    Australia   Amy
    America     Sarah

    que é a lista mundial de vendedores de beterraba. Observe que queremos uma região com duas colunas de largura porque queremos associar países a vendedores mas a altura deve ser o número de países (o que acontece de ser dois neste exemplo). Se houvesse sete países, mudaríamos isso para OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0,7, 2)
    (Embora, se houvesse sete países (e três commodities), o bloco introdutório teria 21 linhas de altura, por isso, usaríamos intervalos da linha 2 até a linha 22.
• VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 2, 2) , 2, 0) e VLOOKUP (e seus irmãos, LOOKUP e HLOOKUP ) são, essencialmente, taquigrafia para INDEX + MATCH . Isso pesquisa a primeira coluna (o V significa v ertical) da região / matriz de vendedores de beterraba ( B4:C5 ) para B9 ( Australia ) e, em seguida, retorna o valor da segunda coluna da linha que correspondeu ao valor de pesquisa ( Australia ). Australia está na linha 4. Já que estamos falando sobre as colunas B e C , Coluna C é "a segunda coluna" nesse contexto, então isso nos leva ao cell C4 , que contém Amy .

… qual é o resultado desejado para a linha 9, já que a Amy é a fornecedora australiana de beterraba.