No excel, quero comparar uma data, digamos a coluna A1, com uma lista de datas em uma matriz, A2: F9 e relatar se é um "feriado". Eu tenho todos os feriados na matriz onde os anos estão listados na linha da esquerda e o feriado listado na coluna superior. O primeiro grupo abaixo são todas as datas. Eu tenho isso indo para 2050. O segundo grupo é a matriz onde eu só mostro alguns feriados. Mas eu não quero comparar, digamos que a primeira data de "27/04/2018" no primeiro grupo, e sob feriado, anote se é no segundo grupo abaixo, colocando "Sim" ou "Holiday"
Date day month year Holiday
4/27/2018 Fri Apr 2018 ??
4/28/2018 Sat Apr 2018
4/29/2018 Sun Apr 2018
4/30/2018 Mon Apr 2018
5/1/2018 Tue May 2018
New Years Day Martin Luther King Jr. Presidents Day
2018 1/1/2018 1/20/2018 2/19/2018
2019 1/1/2019 1/18/2019 2/18/2019
2020 1/1/2020 1/17/2020 2/17/2020
2021 1/1/2021 1/16/2021 2/15/2021
2022 1/1/2022 1/15/2022 2/21/2022
... annotate whether its in the second group below by putting "Yes" or "Holiday"
Parece que você simplesmente quer saber se o dia é um feriado ou não.
A fórmula a seguir deve funcionar em seus dados de amostra:
{=IF(OR(A2=$B$10:$D$14),"Yes", "No")}
É uma fórmula de matriz. Não coloque o segundo suporte manualmente, em vez disso, pressione CTRL + SHIFT + ENTER juntos eo suporte aparecerá automaticamente.
Por favor, veja a fórmula na barra de fórmulas na figura a seguir.
Você pode usar Agregar para retornar o número correto da coluna para um ÍNDICE:
=IFERROR(INDEX($J$1:$L$1,AGGREGATE(15,6,(COLUMN($J$2:$L$6)-MIN(COLUMN($J$2:$L$6))+1)/($J$2:$L$6=A2),1)),"")
SevocêdesejaapenasumretornoVERDADEIRO/FALSO,issoéinseridocomoumamatriz:
=OR(A2=$J$2:$L$6)SendoumafórmuladeMatriz,eladeveserconfirmadacomCtrl-Shift-EnteremvezdeEnteraosairdomododeedição.
EleretornaráTRUEsealgumadasdatasnasegundalistacorresponderàquelaqueestásendoreferenciada.
Aqui está uma solução que usa funções mais antigas que devem funcionar no Excel 2007 (na verdade, estou usando o LO Calc e funciona lá).
Cliquenaimagemparaampliar
VocêpediuparamostrarapenasYesouHoliday.Estasoluçãomostraoferiadoreal.Sevocêquiserapenasareferênciagenérica,afórmulaseriaumpoucomaissimples.
AfórmulaemE2(copiadanacoluna)é:
=IFERROR(INDEX($H$1:$J$1,1,MATCH(A2,INDIRECT("H"&MATCH(D2,$G$2:$G$6,1)+1&":J"&MATCH(D2,$G$2:$G$6,1)+1),0)),"")
Vou explicar de dentro para fora.
MATCH(D2,$G$2:$G$6,1)
Isso localiza a linha do ano na matriz Holidays. A linha é relativa ao intervalo da matriz, com os dados começando na linha 2, portanto, precisamos adicionar 1 à linha real na planilha.
INDIRECT("H"&MATCH(D2,$G$2:$G$6,1)+1&":J"&MATCH(D2,$G$2:$G$6,1)+1)
A função INDIRECT cria uma referência ao intervalo em que você procura a data. Para 2018, isso produz uma faixa de H2: J2 (ajuste a fórmula para o intervalo real). Por questões de legibilidade, vou me referir à fórmula acima como apenas INDIRECT () na explicação abaixo.
MATCH(A2,INDIRECT(),0)
Match, em seguida, localiza a coluna nesse intervalo em que a data em A2 está localizada. Produz um erro se a data não for encontrada (ou seja, não é feriado).
Se você quiser apenas um resultado genérico, pode parar por aí e apenas envolvê-lo em um teste IF para verificar uma condição de erro (erro = sem feriado, um resultado sem erro = feriado) e exibir o texto do seu escolha para qualquer resultado.
INDEX($H$1:$J$1,1,MATCH(A2,INDIRECT(),0))
Isso procura a coluna identificada na linha superior que contém os nomes das festas. Vou me referir a isso como apenas INDEX () para facilitar a leitura na explicação abaixo.
IFERROR(INDEX(),"")
Isso envolve o resultado na função IFERROR para apagar a mensagem de erro se não for um feriado.