Como posso recuperar dados de uma tabela do Excel com base em um número variável de critérios?

Question

Eu tenho os seguintes dados salariais, por exemplo:

Country  State     2012  2013 -> 2027
=======  =====     ====  ====
China    Other     1000  1100
China    Shanghai  1310  1400
China    Tianjin   1450  1500
India    Orissa    1500  1600

Agora, em outra planilha do Excel, eu gostaria de ter uma resposta para uma das seguintes perguntas:

1. Qual é o salário em Xangai para 2013? (Resposta seria 1400)
2. Qual é o salário na província de Hubei para 2012? (Como não está listado, use "Outro" - 1000)
3. Qual é o salário médio na China para 2013? (Resposta seria 1333)
4. Qual é o maior salário na China em 2012? (Resposta é Tianjin)

Assim como na ordem de prioridade acima, eu gostaria desses números em outra planilha do Excel usando alguma forma de consulta. Eu considerei tabelas dinâmicas, mas eu queria saber se há outra maneira muito melhor e mais eficiente de fazer isso?

Eu imagino que o SQL é adequado para isso, mas não estou bem informado sobre isso. Algumas funcionalidades do Excel são muito preferíveis. Também sugestões sobre um formato apropriado de dados para tais consultas seriam apreciadas.

    

Answer 1

Seu caso de uso é perfeito para as funções do banco de dados do Excel . Para usá-los, primeiro é necessário desmembrar seus dados para que cada linha corresponda a uma observação em seus dados. Isso significa que você terá quatro colunas: Country , State , Year e Salary .

Em seguida, você pode usar DGET () para obter uma entrada específica no banco de dados e DAVERAGE () para obter uma média. Os critérios para funções de banco de dados precisam ser dispostos em pares de células, onde o campo de filtragem fica no topo e o valor a ser filtrado passa por baixo dele. Aqui está um exemplo de trabalho:

AfórmulaparaacélulaI10éamesmaqueparaI9,excetoqueusaDAVERAGEnolugar.

• EmI9insira=IFERROR(DGET($B$4:$E$12,$E$4,$G$4:$I$5),"")
• Em I10 insira =IFERROR(DAVERAGE($B$4:$E$12,$E$4,$G$4:$I$5),"")

O IFERROR() em torno dele garante que o DGET não mostre #NUM! quando você não forneceu valores em todas as três entradas na linha 6. Isso acontece porque não é possível encontrar um único valor para o salário se, por exemplo, você não estiver informando em qual ano filtrar.

    

Answer 2

Eu acho que uma maneira é nomear a coluna - Por exemplo, clique com o botão direito do mouse na célula com o valor 1400 (salário para Xangai para 2013) e define name

Em seguida, em outra célula ou apenas se referir a essa célula - a beleza disso é que não importa se essa célula é movida, ela permanecerá com o conteúdo que você deseja.

    

Answer 3

Com alguma preparação, isso também pode ser feito facilmente no Excel. (Embora possa ser mais fácil e natural com o SQL).

A preparação: use intervalos nomeados como sugerido @Adam - country para o país, state para o estado e também nomeei cada um dos anos neste formato year2012 .

Depois, você pode criar sua folha de respostas

     A              B         C         D
1 salary in     Shanghai    2013    =IFERROR(INDEX(INDIRECT("year"&C1),MATCH(B1,state,0)),INDEX(INDIRECT("year"&C1),MATCH("Other",state,0)))
2 salary in     Hubei       2012    =IFERROR(INDEX(INDIRECT("year"&C2),MATCH(B2,state,0)),INDEX(INDIRECT("year"&C2),MATCH("Other",state,0)))
3 average       China       2013    =AVERAGEIF(country,B3,INDIRECT("year" & C3))
4 highest       China       2012    {=INDEX(state,MATCH(MAX(IF(country=B4,INDIRECT("year"&C4))),INDIRECT("year"&C4),0))}

Cada uma destas linhas é dinâmica, pelo que pode alterar as suas 'perguntas' conforme necessário. O importante é notar que a quarta (maior) fórmula é Array Formula, que requer o uso de CTRL+SHIFT+ENTER ao entrar nela.

p.s. Sua resposta para a 3ª pergunta está errada, o salário médio na China em 2013 deve ser 1333.

    

Answer 4

Concordo com Dave Rook, que é o lugar onde você deve usar o SQL.

Também é possível ser feito inteiramente com fórmulas do Excel (o que não é?). É assim:

Antes de mais nada, você precisará de uma maneira de abordar cada coluna. Você tem duas opções para isso.

1. Primeiro, (como Dave Rook sugeriu) simples e mais "bonito", é definir intervalos nomeados. Apenas certifique-se de que cada intervalo nomeado seja grande o suficiente para conter todos os registros futuros (ou use algum tipo de intervalo nomeado dinâmico). Infelizmente, requer alguns cliques para cada coluna, por isso não é dimensionável quando você tem 100 colunas.

2. A outra opo (que eu prefiro para computaes pesadas) gerar as sequcias de endere do intervalo da coluna usando, e. =ADDRESS(ROW(C3);COLUMN(C3);4;;"sheet1") & ":" & ADDRESS(ROW(C3)-1+$A$2;COLUMN(C3);4) para acessar dados na coluna C com o número de registros armazenados na célula A2 . Em seguida, você abordará o intervalo usando a fórmula INDIRECT() em todos os lugares, quando normalmente você colaria o intervalo nomeado do método 1.

Uma vez que todas as suas colunas (campos) são intervalos nomeados, você pode executar todo o tipo de pesquisa e fazer referência a tarefas neles.

Por exemplo: para identificar o número de registros que contém a palavra "Shanghai", use =MATCH("Shanghai";State;0) , em que State é o intervalo nomeado para a coluna "Estado".

Quando tiver o número do registro (digamos que seja na célula X10 , você pode obter informações sobre o salário com =INDEX(Salary;X10) .

Todos os tipos de tarefas são possíveis em fórmulas do Excel; por exemplo. você pode criar uma lista de valores exclusivos (ou valores que correspondam a determinados critérios), você pode calcular qualquer forma de soma / média. Você pode até ordená-los (sim!).

Por favor, note que na minha localidade eu uso o ponto-e-vírgula ; como separador de argumentos (não vírgula). Os países de língua inglesa geralmente usam , e, nesse caso, para usar minhas fórmulas, você precisaria substituir todos os ; - > , .

    

Answer 5

Vá para esta abordagem vba, deve funcionar sem muito ajustes. O tratamento de erros pode ser atualizado, tho:

Private Sub get_money(byval country as string, byval city as string, byval year as string)
    if country == "" then country == "nope" end if
    if city == "" then city =="nope" end if
    if year == "" then year =="nope" end if
    if autofiltermode = true then autofiltermode = false

    dim all_columns as double
    all_columns = thisworkbook.activesheet.range("A1").currentregion.columns.count
    with thisworkbook.activesheet
        for cell_position = 3 to all_columns
            if .cells(1,cell_position) == year
                year_to_filter = cell_position
                cell_position = all_columns
            end if
        next cell_position
        .Range(.cells(1,1), .cells(1,all_columns)).AutoFilter Field:=1, Criteria1:=country, Operator:=xlFilterValues
        .Range(.cells(1,1), .cells(1,all_columns)).AutoFilter Field:=2, Criteria1:=city, Operator:=xlFilterValues
        .Range(.cells(1,1), .cells(1,all_columns)).AutoFilter Field:=cell_position, Criteria1:=year, Operator:=xlFilterValues 
    end with      
end sub