Soma dos valores de vlookup em várias planilhas

Question

Tenha 12 planilhas do excel em uma pasta de trabalho para cada mês Jan-Dec. Cada folha contém nomes e folhas marcadas para esse mês na mesma linha e valor de soma para cada categoria de licença no final da tabela. Preciso resumir o valor dessas folhas individuais procurando o nome de cada pessoa em todas as folhas. Como eu posso conseguir isso no excel .... Eu tentei soma de vlookup de toda a folha, ele lança erro ou nenhum resultado.

    

Answer 1

Pode ser mais simples usar o SUMIF aqui porque o SUMIF não dará um erro se a pessoa não for encontrada em uma folha específica (embora todas as planilhas precisem existir), então se você tiver as planilhas "jan" até "dec "tente esta fórmula para o total de 12 meses

=SUM(SUMIF(INDIRECT({"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}&"!B8:B110"),B8,INDIRECT({"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}&"!AL8:AL110")))

Você pode encurtar isso usando um intervalo nomeado, por exemplo,

Sheetlist ={"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}

então a fórmula se torna

=SUMPRODUCT(SUMIF(INDIRECT(Sheetlist&"!B8:B110"),B8,INDIRECT(Sheetlist&"!AL8:AL110")))

    

Answer 2

Esse resumo será muito mais fácil de realizar se você consolidar suas planilhas mensais em uma única planilha de banco de dados para o ano. Em seguida, você pode configurar uma Tabela Dinâmica que fará o resumo por pessoa em pouco tempo.

Eu suspeito que sua soma de vlookups está causando um erro porque cada pessoa sai em apenas alguns meses em um ano. A maneira de lidar com isso é envolver as pesquisas nas funções IFERROR. Então, as fórmulas teriam que se parecer com:

 =sum(iferror(vlookup(<person name>,<January sheet name!data range>,<leave column number>,0),0),
      iferror(vlookup(<person name>,<February sheet name!data range>,<leave column number>,0),0),
      etc.
      )

Isso pode ser um pouco menos complicado configurando uma pesquisa para cada mês em colunas individuais e, em seguida, somando os resultados nas colunas.

Outra alternativa com a qual eu não estou muito familiarizado - mas que pode atender às suas necessidades - é o comando Data Consolidate. Uma explicação do comando pode ser encontrada aqui .

Eu recomendo strongmente a abordagem da Tabela Dinâmica.