Dados móveis do Excel 2010

Question

Eu tenho uma tabela que preciso retirar alguns dados e não tenho muita certeza sobre como fazer isso.

Aqui está um exemplo da tabela.

|   A    +   B    +       C       +   D   +   E    +   F    |
|--------+--------+---------------+-------+--------+--------|
| FNAME  | LNAME  | FULLNAME      | GRADE | EFFORT | CODE   |
|--------+--------+---------------+-------+--------+--------|
| FNAME1 | LNAME1 | FNAME1 LNAME1 | A     |        | GRADE  |
|--------+--------+---------------+-------+--------+--------|
| FNAME1 | LNAME1 | FNAME1 LNAME1 | H     |        | EFFORT |
|--------+--------+---------------+-------+--------+--------|
| FNAME2 | LNAME2 | FNAME2 LNAME2 | C     |        | GRADE  |
|--------+--------+---------------+-------+--------+--------|

O que eu preciso é mover a nota de esforço para a coluna de esforço, mas em uma linha diferente. Exemplo abaixo.

|   A   +   B   +     C     +   D   +   E    +   F    |
|-------+-------+-----------+-------+--------+--------|
| FNAME | LNAME | FULLNAME  | GRADE | EFFORT | CODE   |
|-------+-------+-----------+-------+--------+--------|
| NAME1 | NAME1 | NAMENAME1 | A     |        | GRADE  | ←      To column E here         ←
|-------+-------+-----------+-------+--------+--------|                                 ↑
| NAME1 | NAME1 | NAMENAME1 | H     |        | EFFORT | → Move this Grade from column D ↑
|-------+-------+-----------+-------+--------+--------|

Então, um exemplo do que eu quero ...

|   A    +   B    +       C       +   D   +   E    +   F    |
|--------+--------+---------------+-------+--------+--------|
| FNAME  | LNAME  | FULLNAME      | GRADE | EFFORT | CODE   |
|--------+--------+---------------+-------+--------+--------|
| FNAME1 | LNAME1 | FNAME1 LNAME1 | A     | H      | GRADE  |
|--------+--------+---------------+-------+--------+--------|
| FNAME2 | LNAME2 | FNAME2 LNAME2 | C     |        | GRADE  |
|--------+--------+---------------+-------+--------+--------|

O kicker é, nem todos os alunos têm uma nota de esforço, e se eles não tiverem um, não haverá uma linha para isso. Além disso, às vezes, haverá uma linha extra com comentários, etc ... para que os dados não sejam constantes.

    

Answer 1

Em uma nova planilha, copie os cabeçalhos ( A1:F1 ) e digite 1 em uma coluna auxiliar. Eu suponho que você está usando a coluna G ; ou seja, célula G1 . Digite

=IF(INDEX(Sheet1!A:A, $G2)<>"", INDEX(Sheet1!A:A, $G2), "")

em A2 e arraste / preencha para a direita, para F2 . Em seguida, altere E2 para

=IF(INDEX(Sheet1!F:F, $G2+1)="EFFORT", INDEX(Sheet1!D:D, $G2+1), "")

e digite

=IF(INDEX(Sheet1!F:F, $G1+1)="GRADE", $G1+1, $G1+2)

em G2 . Selecione A2:G2 e arraste / preencha até onde for necessário.

Em seguida, oculte a coluna G , se quiser.

Coluna G é o índice (na nova planilha) da linha em Sheet1 de que estamos (basicamente) desenhando dados de. É mais um que o índice da linha anterior a menos que aponte para linha cujo CÓDIGO é GRADE , Nesse caso, assumimos que é uma linha EFFORT e adicione dois ao anterior (ou seja, para pular a linha EFFORT ). As colunas A - D e F são simplesmente copiadas da mesma coluna em Sheet1 , como indexado pela coluna G . A fórmula na Coluna E (EFFORT) parece ver se o aluno atual é um com duas linhas (então $G2+1 refere-se ao mesmo aluno, com um CÓDIGO de EFFORT ) e, se for o caso, puxa a nota EFFORT da coluna D dessa segunda linha.

Para esses dados:

+   A   +   B    +      C       +   D   +   E    +   F    +
+-------+--------+--------------+-------+--------+--------+
| FNAME | LNAME  |   FULLNAME   | GRADE | EFFORT |  CODE  |
+-------+--------+--------------+-------+--------+--------+
| John  | Smith  | John Smith   | A     |        | GRADE  |
| John  | Smith  | John Smith   | H     |        | EFFORT |
| Mary  | Jones  | Mary Jones   | B     |        | GRADE  |
| Isaac | Newton | Isaac Newton | A+    |        | GRADE  |
| Isaac | Newton | Isaac Newton | C     |        | EFFORT |
+-------+--------+--------------+-------+--------+--------+

Eu tenho esse resultado:

    

Answer 2

Você pode lidar com isso sem pivô.

Com base no seu exemplo, coloque isso na coluna Effort em Sheet2 :

=INDEX(Sheet1!$D$2:$D$10, MATCH(1, (Sheet2!$C2 = Sheet1!$C$2:$C$10) * (Sheet1!$F$2:$F$10 = "EFFORT"), 0))

e pressione Ctrl + Deslocamento + Digite para inserir a fórmula da matriz.

Explicação

Sheet1!$D$2:$D$10 é sua coluna Effort em sua tabela de origem na Planilha1.

(Sheet2!$C2 = Sheet1!$C$2:$C$10) está procurando a correspondência FULLNAME

(Sheet1!$F$2:$F$10 = "EFFORT") está procurando a correspondência CODE , que é "ESFORÇO"

Ambos retornam valor booleano, 0 e 1 . Se ambas as condições corresponderem (encontrar uma linha com NOME E CÓDIGO correspondentes), ela retornará 1 ( 1 * 1 ) para a função MATCH .