VBA é uma coisa muito útil. Você pode consolidar dados de muitos arquivos e processar grandes quantidades de informações usando algoritmos interessantes. Por exemplo, a
macro Nodupes . Ele usa o erro que ocorre durante a execução do código como uma verificação da presença de um elemento na coleção gerada. Existem macros que usam recursão para formar todas as combinações e permutações possíveis.
Mas eu gostaria de mostrar alguns exemplos de como usar as funções padrão da planilha do Excel.
Muitas vezes, é necessário cercar o jardim de fórmulas para extrair o texto ou os números desejados das células. Isso geralmente acontece devido à apresentação incorreta dos dados. Números são números, texto é texto, não os misture. Datas (e hora) são números com os quais é possível e necessário fazer cálculos. Não confunda 14/03/2018 a partir de 14 de março de 2018. O segundo é apenas um formato de data personalizado [$ -F800], o que vemos na célula e na barra de fórmulas será 14/03/2018. E chega disso.
1. Suponha que você tenha uma tabela com os horários de início e término de alguns eventos, por exemplo, conversas telefônicas.
Na figura, parte da tabela _t2. É necessário calcular o número máximo simultâneo de eventos. Para isso, existe uma fórmula:
=(((_t2[start]>=(_t2[start]))*(_t2[start]<=(_t2[end]));(_t2[start])^0)) =MAX(MMULT((_t2[start]>=TRANSPOSE(_t2[start]))*(_t2[start]<=TRANSPOSE(_t2[end])),ROW(_t2[start])^0))
A fórmula é massiva, a entrada é confirmada pressionando simultaneamente os botões Control, Shift, Enter (CSE). A barra de fórmulas mostrará que a fórmula está entre chaves. MUMNOZH cria uma matriz virtual do tamanho do número de linhas da tabela pelo número de linhas da tabela, nas linhas em que os resultados do cálculo são inseridos, como se a seguinte fórmula fosse inserida nas linhas da tabela (para a segunda linha) = SUMPRODUCT (F ((C $ 2: C2> A2))) / = SUMPRODUCT (N ((C $ 2: C2> A2))) e estenda para a tabela inteira.
2. O mesmo, mas mais complicado. Além do começo e do fim, há o número de unidades ou a potência fornecida / recebida. É necessário determinar a potência máxima em algum momento.
Parte da tabela _t1:
E a própria fórmula:
=((((_t1[start]*1440)<=(1:1436)-1)*((_t1[stop]*1440)>=(1:1436)-1);_t1[power])) =MAX(MMULT((TRANSPOSE(_t1[start]*1440)<=ROW(1:1436)-1)*(TRANSPOSE(_t1[stop]*1440)>=ROW(1:1436)-1),_t1[power]))
Como o tempo é uma fração de uma unidade, multiplicamos os valores pelo número de minutos em um dia para obter números inteiros. E o segundo argumento é MÚLTIPLO - uma coluna de valores de potência. No primeiro caso, uma coluna de unidade foi gerada.
3. À esquerda da tabela estão os valores correspondentes a determinados intervalos. Os valores mensais devem ser calculados. Supõe-se que os valores sejam distribuídos uniformemente dentro de seus intervalos.
A coluna H é o primeiro dia do mês, a coluna I contém as seguintes fórmulas:
=(_tis[sum per day]*((((H3;0)>_tis[end];_tis[end];(H3;0))-(H3>_tis[start];H3;_tis[start])+1)^0,5;)^2) =SUM(_tis[sum per day]*IFERROR((IF(EOMONTH(H3,0)>_tis[end],_tis[end],EOMONTH(H3,0))-IF(H3>_tis[start],H3,_tis[start])+1)^0.5,)^2)
As fórmulas são enormes. Você poderia usar SUMPRODUCT / SUMPRODUCT, mas SE ERRO não funcionar sem uma entrada massiva. Infelizmente, MAX e MIN também não podem formar uma matriz virtual de valores. Portanto, o IF é usado para calcular partes dos intervalos por mês. Como as partes resultantes podem ser negativas, um erro é causado artificialmente (a raiz quadrada de um número negativo leva a isso). Na tabela de origem, foi possível dispensar colunas de duração do intervalo e quantidade diária. A fórmula demoraria um pouco mais.
4. Um pouco divertido. Quantas sextas-feiras felizes pode haver em um ano?
Número de sextas-feiras:
=(((((A:A;B2):(A:A;C2)))=13)*((((A:A;B2):(A:A;C2));2)=5)) =SUMPRODUCT((DAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)))=13)*(WEEKDAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)),2)=5))
Datas das sextas-feiras:
=((15;6;(($A:$A;$B2):($A:$A;$C2))/(((($A:$A;$B2):(A:A;$C2)))=13)/(((($A:$A;$B2):($A:$A;$C2));2)=5);(A$2));"") =IFERROR(AGGREGATE(15,6,ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2))/(DAY(ROW(INDEX($A:$A,$B2):INDEX(A:A,$C2)))=13)/(WEEKDAY(ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2)),2)=5),COLUMN(A$2)),"")
É muito bom (aqui) que a primeira linha da planilha corresponda a 01/01/1901. Portanto, é fácil o suficiente formar uma matriz de datas do ano usando INDEX e STRING e contar apenas a décima terceira sexta-feira.
5. O tamanho da taxa de juros, dependendo da quantidade, é bastante comum. Se você usá-los incorretamente, o cronograma dos valores recebidos será como o gráfico vermelho abaixo:
Para evitar isso, você precisa aplicar a taxa apenas a parte do valor por intervalo. E para intervalos mais baixos, tome quantidades fixas.
Para esses dados de origem:
Formula
=(A3;tbl;2)*(A3-(A3;tbl;1))+(((tbl[limit]<=A3)*(tbl[limit]-(tbl[limit];-1;))*(tbl[rate];-1;);)) =VLOOKUP(A3,tbl,2)*(A3-VLOOKUP(A3,tbl,1))+SUMPRODUCT(IFERROR((tbl[limit]<=A3)*(tbl[limit]-OFFSET(tbl[limit],-1,))*OFFSET(tbl[rate],-1,),))
A fórmula é enorme. Em A3, o valor pelo qual os juros devem ser calculados. Dados iniciais –tbl.
E, finalmente, uma linha de crédito com uma taxa de juros variável. Fórmula para calcular a quantia de juros, dependendo das datas / quantias de parcelas / devoluções.
Histórico de crédito:
Alterar lance:
=(B1=0;(((_d;_s[[date]:[rate]]);)*(B5:B16)*((_d>(A5:A16))*(_d<=A2);;)*1/(365+(((_d);4)=0)))-(((_d;_s[[date]:[rate]]);)*(D5:D16)*((_d>(C5:C16))*(_d<=A2);;)*1/(365+(((_d);4)=0)));) =IF(B1=0,SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(B5:B16)*INDEX((_d>TRANSPOSE(A5:A16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0)))-SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(D5:D16)*INDEX((_d>TRANSPOSE(C5:C16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0))),)
A fórmula é enorme. Conta juros por dias, leva em consideração os anos bissextos.
Obrigado pela atenção!