Localizando min / max valores na coluna onde a célula corresponde a algo

Question

Eu tenho uma planilha como esta:

  Date      Project#
2012-01-01    130
2012-01-02    153
2012-01-03    153
2012-01-04    130
2012-01-05    130
2012-01-06    130

E eu quero obter as datas mínimas / máximas para aquelas linhas onde o Projecto # corresponde a uma variável, para produzir isto:

Projects    Start date    End date
130         2012-01-01    2012-01-06
153         2012-01-02    2012-01-03

Eu percebi que poderia fazer isso via VLOOKUP e pesquisar em ambas as direções, mas não consigo trabalhar corretamente. Tudo funciona bem, desde que os números do projeto sejam todos agrupados, mas se forem intercalados, como no exemplo, ele não funciona mais. Ele só retorna do agrupamento mais alto, então, em vez disso, eu teria a data de término do projeto 130 como 2012-01-01.

Sou um novato em planilhas, então qualquer ajuda seria muito apreciada.

Obrigado!

    

Answer 1

Prefácio: Eu suponho que você esteja familiarizado com o jeito do Calc de se referir a Células, que é ColumnLetterRowNumber e intervalos são expressos assim: A10:C12 significa todas as células entre A10 e C12, então 3 linhas e três colunas

Ok, suponho que seus dados estejam em A1:B100 (ou quantas linhas você tiver).

Agora, em D1 , insira Projeto nº , em E1 insira Data inicial e em F1 insira Data final e em seguida, liste todos os números de projeto (manualmente) assim:

Date      Project#              Project#   Start date   End date
2012-01-01    130                   130
2012-01-02    153               Project#   Start date   End date
2012-01-03    153                   153
2012-01-04    130
2012-01-05    130
2012-01-06    130

Agora, a fórmula para E2 é =DMIN(A1:B100,1,D1:D2) e, para F2 , é =DMAX(A1:B100,1,D1:D2) . Em seguida, copie as células E2: F2 para as outras linhas.

Infelizmente, é assim que essas operações funcionam.

    

Answer 2

Eu usaria o Data Pilot para criar a tabela dinâmica.

1. Menu Dados
2. Selecione Data Pilot
3. Clique em Iniciar
4. Clique em OK
5. Arraste e solte projetos para campos de linha
6. Arraste e solte Data para campos de dados
7. Clique duas vezes em Data que acabou de adicionar
8. Selecione a função Max
9. Clique em OK

É isso que você está procurando?

    

Answer 3

Eu sei que olhei para isso em setembro, mas eu dei uma boa olhada hoje e cheguei a uma nova ruga. Ele é baseado na resposta de Stefan , mas aborda o problema de “critérios” do banco de dados que exigem duas linhas, tendo duas colunas delas e alternando entre eles:

Aquiestãoasfórmulas:

Por favor, deixe-me saber se isso não está claro.

    

Answer 4

Scott, obrigado pelo seu refinamento - realmente ajudou. Apenas para simplificar a configuração da planilha, tomei as seguintes etapas:

1 - duplique a coluna de dados para a qual eu quero encontrar o máximo e a adicione na parte inferior (para que haja duas vezes mais linhas do que os dados)

2 - na coluna ao lado dos dados duplicados (C no seu exemplo) coloque 1 na célula superior e depois = IF (ISODD (ROW (A2)); C1 + 1; C1) copiado até o final parte inferior dos dados (a etapa 1 facilita a localização da parte inferior)

3 - na próxima coluna colocar = IF (ISODD (ROW (A1)); "Projeto"; OFFSET ($ B $ 1; C1; 0)) e novamente copiar para o final dos dados duplicados - isso produz o nomes de campos alternativos e valores que queremos interrogar

4 - o máximo é então encontrado por = DMAX ($ A $ 1: $ B $ 3437; "Livedate"; OFFSET (D $ 1; LINHA ($ A1) * 2-2; 0; 2; 1)), que dá o máximo em cada linha como no seu exemplo.

Eu tenho muitos dados - 3400 linhas - então, dessa forma, ficou mais fácil construir a planilha. Obrigado pela dica, espero que isso possa ser útil para os outros.