Recentemente um amigo, Max, me questionou quanto à problemas de performance no Excel. Qual a capacidade máxima? Qual o tamanho, em MB, máximo para uma planilha?
Fuçando no mundo da web e na minha experiência, o problema de tamanho x performance de uma planilha, embora relacionados, leva-nos a uma dedução:
O tamanho de uma planilha está limitado à capacidade de memória RAM disponível e aos recursos do computador.
Apesar de, no 2007 e 2010, o Excel suportar 1.048.576 linhas por 16.384 colunas, não será somente o tamanho ou o número de linhas que irá trazer problemas de performance, mas, adicionalmente, a quantidade de cálculos que uma planilha possui.
Isso porque, a cada enter em uma célula, o Excel dispara um evento que irá provocar a execução de todas as fórmulas existentes e, enquanto estas não forem concluídas, o Excel irá ficar aguardando.
Geralmente, em grandes planilhas, a melhor dica é limpar a memória de programas que podem ser desligados e tirar o cálculo automático de fórmulas. Isso é feito indo em Arquivo/Opções/Fórmulas/Opções dé Cálculo e marcar em Manual.
Quando desejar atualizar os cálculos, basta pressionar F9.
Claro que acresentar tabelas dinâmicas, gráficos, links para outros workbooks, implicará em mais penalizações na performance.
Follow by Email
segunda-feira, 25 de julho de 2011
quinta-feira, 3 de março de 2011
Definido bordas no Excel, via VBA
Mais uma: para definir as bordas de um conjunto de células, com o VBA.
Nesse exemplo, marcamos uma região e colocamos as bordas externas.
Range("B10:I15").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Nesse exemplo, marcamos uma região e colocamos as bordas externas.
Range("B10:I15").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
segunda-feira, 28 de fevereiro de 2011
Excel: Ordenando células via VBA
Como ordernar uma faixa de célua, usando VBA?
Digamos que temos uma planilha com dados de fornecedores e não sabemos qual é a linha final. Uma vez importado, queremos realizar uma ordenção, pelo nome do fornecedor, que está na coluna 3
Dim wbAtual AS Workbooks
Dim iLinhaF AS integer
set wbAtual = ActiveWorkBook
iLinhaF = wbAtual.WorkSheets("Fornecedores").Cells( Rows.Count, 3).End( xlUp).Row
wbAtual.WorkSheets("Fornecedores").Range("A2:F" & iLinhaF).Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, Matchcase:= False, Orientation:=xlTopBottom
Pronto!
Digamos que temos uma planilha com dados de fornecedores e não sabemos qual é a linha final. Uma vez importado, queremos realizar uma ordenção, pelo nome do fornecedor, que está na coluna 3
Dim wbAtual AS Workbooks
Dim iLinhaF AS integer
set wbAtual = ActiveWorkBook
iLinhaF = wbAtual.WorkSheets("Fornecedores").Cells( Rows.Count, 3).End( xlUp).Row
wbAtual.WorkSheets("Fornecedores").Range("A2:F" & iLinhaF).Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, Matchcase:= False, Orientation:=xlTopBottom
Pronto!
segunda-feira, 14 de fevereiro de 2011
Redirecionando páginas em PHP
Ao tentar redirecionar uma página em php para outra, me deparei com um erro do tipo: Cannot modify header information - headers already sent by"
A solução que eu encontrei na WEB e compartilho com todos é:
A solução que eu encontrei na WEB e compartilho com todos é:
Bem, estou começando com o PHP e, para ajudar a outros que como eu, tem a web como ajuda, resolvi publicar essa solução.
terça-feira, 11 de janeiro de 2011
Gravando fórmulas via VBA em células
Essa dica de hoje me valeu a manhã de trabalho. Estou montando uma planilha que calcula horas-extras. Criei uma rotina em que lê os dados de uma planilha e sai escrevendo-os em outra planilha, totalizando-os ao final, de acordo com o departamento.
No final, me deparei com um erro no seguinte código:
pQuadro.Cells(C16).Formula = "=soma( C" & iLinhaInicio & ":C" & iLinhaAtual - 1 & ")"
O desejo aqui é que ele grave na célula C16 a fórmula: =SOMA(C3:C15) e efetivamente calcule esses valores.
O problema é que o conteúdo de C16 está aparecendo justamente =SOMA(C3:C15) e não o resultado dessa fórmula. Se eu pressiono F2 e enter, aí sim, ele calcula.
Depois de muito navegar, a solução que encontrei ( desculpe-me, perdi o site) é simples!
Basta substituir SOMA por SUM, pois o Excel utiliza fórmulas EXCEL em português, mas o VBA usa fórmulas em INGLÊS. É substituir e funcionar!
Tão simples, mas tão útil, que resolvi postar!
No final, me deparei com um erro no seguinte código:
pQuadro.Cells(C16).Formula = "=soma( C" & iLinhaInicio & ":C" & iLinhaAtual - 1 & ")"
O desejo aqui é que ele grave na célula C16 a fórmula: =SOMA(C3:C15) e efetivamente calcule esses valores.
O problema é que o conteúdo de C16 está aparecendo justamente =SOMA(C3:C15) e não o resultado dessa fórmula. Se eu pressiono F2 e enter, aí sim, ele calcula.
Depois de muito navegar, a solução que encontrei ( desculpe-me, perdi o site) é simples!
Basta substituir SOMA por SUM, pois o Excel utiliza fórmulas EXCEL em português, mas o VBA usa fórmulas em INGLÊS. É substituir e funcionar!
Tão simples, mas tão útil, que resolvi postar!
segunda-feira, 20 de dezembro de 2010
Excel: Adicionar dias úteis a uma data
Recentemente me pediram para auxiliar em um probleminha com datas úteis no excel. Sabemos que a função DIATRABALHOTOTAL nos mostra a diferença, em dias úteis, entre duas datas, mas, e como adicionar quantidade de dias úteis em uma data? Parece fácil, mas não encontrei outra alternativa a não ser criando uma função para isso.
Vamos ao problema:
Dado uma data que cai, por exemplo, no dia 05/03/2011, que dá em um sábado. Desejando-se adicionar 2 dias úteis, a função deverá retornar 10/03/2011, já que 07/03 e 08/03 caem no carnaval, feriado portanto.
Para vencermos esse desafio vamos precisar de:
1-Uma tabela de feriados.
2-Uma tabela para testar
3-Duas funções para calcularmos os dias úteis.
Vamos ao projeto:
1º) Abra um novo arquivo de trabalho do Excel.
2º) Renomeie a Plan1 para Feriados. Em A1 cole o seguinte conteúdo:
12/10/2010 terça-feira Nossa Sr.a Aparecida - Padroeira do Brasil
02/11/2010 terça-feira Finados
15/11/2010 segunda-feira Proclamação da República
25/12/2010 sábado Natal
01/01/2011 sábado Confraternização Universal
07/03/2011 segunda-feira Carnaval
08/03/2011 terça-feira Carnaval
21/04/2011 quinta-feira Tiradentes
22/04/2011 sexta-feira Paixão de Cristo
01/05/2011 domingo Dia do Trabalho
23/06/2011 quinta-feira Corpus Christi
07/09/2011 quarta-feira Independência do Brasil
12/10/2011 quarta-feira Nossa Sr.a Aparecida - Padroeira do Brasil
02/11/2011 quarta-feira Finados
15/11/2011 terça-feira Proclamação da República
25/12/2011 domingo Natal
01/01/2012 domingo Confraternização Universal
20/02/2012 segunda-feira Carnaval
21/02/2012 terça-feira Carnaval
06/04/2012 sexta-feira Paixão de Cristo
21/04/2012 sábado Tiradentes
01/05/2012 terça-feira Dia do Trabalho
07/06/2012 quinta-feira Corpus Christi
07/09/2012 sexta-feira Independência do Brasil
12/10/2012 sexta-feira Nossa Sr.a Aparecida - Padroeira do Brasil
02/11/2012 sexta-feira Finados
15/11/2012 quinta-feira Proclamação da República
25/12/2012 terça-feira Natal
Temos aqui uma pequena faixa de feriados. Em outro post vou colocar a planilha completa.
3º) Vamos renomear essa faixa de células:
1-Selecione de A1 a C28
2-Na barra de endereço, dê um nome para essa faixa: TB_FERIADO
4º) Agora, vamos criar as funções que iremos utilizar na solução.
1-Utilize a combinação das teclas ALT e F11, para acionar o editor de código VBA do Excel.
2-Na tela que abriu, clique com o botão direito em Plan1. Clique em Inserir e, depois, em Módulo.
3-Digite o seguinte código:
Function Eh_Feriado(dData As Date) As Boolean
Dim rng As Variant
Dim sData As String
Dim sNome AS string
sNome = ActiveWorkBook.Name
sData = CDate(dData)
Set rng = Workbooks(sNome).Worksheets("Feriados").Range("A1:A500").Find(sData)
Eh_Feriado = Not (rng Is Nothing)
End Function
4º) Vamos criar outra função, que é a que iremos utilizar comumente:
1-Digite o seguinte código, após a End Function da função anterior:
Function AdicionaDiaUtil(dData As Date, iDias As Integer)
'
' Esta função adicionará iDias úteis em dData
'
Dim iCont As Integer
iCont = 1
While iCont <= iDias
'
' Adiciona 1 dia
'
dData = DateAdd("d", 1, dData)
If Eh_Feriado(dData) Then
iDias = iDias + 1
Else
If (Weekday(dData) = 1) Then
iDias = iDias + 1
ElseIf (Weekday(dData) = 7) Then
iDias = iDias + 1
End If
End If
iCont = iCont + 1
Wend
AdicionaDiaUtil = dData
End Function
Não esqueça de salvar!
5º) Vamos criar uma tabela auxiliar para recuperar o nome do dia da semana, para facilitar os testes:
1-Na planilha Tabelas, coloque a seguinte sequência, a partir de E1:
6º) Pronto, já temos o que precisamos para calcular. Vamos criar uma planilha de teste.
1-Abra a Plan2.
2-Em B5, digite: Data:
3-Em B6, digite: Dia da Semana:
Veja que eu atribui o nome TB_DIADASEMANA para essa faixa de células.
4-Em B8, digite: Dias úteis:
5-Em B9, digite: Próxima data:
6-Em B10, digite: Dia da semana
7º) Agora, vamos colocar o Excel para trabalhar por nós:
1-Na célula C5 digite uma data qualquer. Exemplo: 01/01/2011
2-Na célula C6 digite: =PROCV( DIA.DA.SEMANA( C5); TB_DIADASEMANA;2)´
Veja que ele já recupera o dia da semana para a data informada. Para a data 01/1/2011, retorna sábado.
3-Na célula C8 informe a quantidade de dias úteis desejada para a data testada. Digamos: 2
4-Na célula C9 vamos colocar a função que criamos, passando como parâmetro da data, que está em C6 e a quantidade de dias úteis, que está em C9: =AdicionaDiaUtil(C5;C8)
5-Na célula C10 coloque: =PROCV( DIA.DA.SEMANA( C9); TB_DIADASEMANA;2)
Pronto! Para dia 01/01/2011, adicionando 2 dias úteis a essa data teremos 04/01/2011, senão vejamos:
Dia 01/01/2011 é sábado, além de ser feriado.
Dia 02/01/2011 é domingo, não pode contar como o primeiro dia úti.
Dia 03/01/2011 é segunda, primeiro dia útil.
Dia 04/01/2011 é o segundo dia útil.
Teste com 04/03/2011 e veja o resultado!
No próximo post coloco o arquivo com a tabela de feriado completa.
Dúvidas, sugestões, estou à disposição.
Vamos ao problema:
Dado uma data que cai, por exemplo, no dia 05/03/2011, que dá em um sábado. Desejando-se adicionar 2 dias úteis, a função deverá retornar 10/03/2011, já que 07/03 e 08/03 caem no carnaval, feriado portanto.
Para vencermos esse desafio vamos precisar de:
1-Uma tabela de feriados.
2-Uma tabela para testar
3-Duas funções para calcularmos os dias úteis.
Vamos ao projeto:
1º) Abra um novo arquivo de trabalho do Excel.
2º) Renomeie a Plan1 para Feriados. Em A1 cole o seguinte conteúdo:
12/10/2010 terça-feira Nossa Sr.a Aparecida - Padroeira do Brasil
02/11/2010 terça-feira Finados
15/11/2010 segunda-feira Proclamação da República
25/12/2010 sábado Natal
01/01/2011 sábado Confraternização Universal
07/03/2011 segunda-feira Carnaval
08/03/2011 terça-feira Carnaval
21/04/2011 quinta-feira Tiradentes
22/04/2011 sexta-feira Paixão de Cristo
01/05/2011 domingo Dia do Trabalho
23/06/2011 quinta-feira Corpus Christi
07/09/2011 quarta-feira Independência do Brasil
12/10/2011 quarta-feira Nossa Sr.a Aparecida - Padroeira do Brasil
02/11/2011 quarta-feira Finados
15/11/2011 terça-feira Proclamação da República
25/12/2011 domingo Natal
01/01/2012 domingo Confraternização Universal
20/02/2012 segunda-feira Carnaval
21/02/2012 terça-feira Carnaval
06/04/2012 sexta-feira Paixão de Cristo
21/04/2012 sábado Tiradentes
01/05/2012 terça-feira Dia do Trabalho
07/06/2012 quinta-feira Corpus Christi
07/09/2012 sexta-feira Independência do Brasil
12/10/2012 sexta-feira Nossa Sr.a Aparecida - Padroeira do Brasil
02/11/2012 sexta-feira Finados
15/11/2012 quinta-feira Proclamação da República
25/12/2012 terça-feira Natal
Temos aqui uma pequena faixa de feriados. Em outro post vou colocar a planilha completa.
3º) Vamos renomear essa faixa de células:
1-Selecione de A1 a C28
2-Na barra de endereço, dê um nome para essa faixa: TB_FERIADO
4º) Agora, vamos criar as funções que iremos utilizar na solução.
1-Utilize a combinação das teclas ALT e F11, para acionar o editor de código VBA do Excel.
2-Na tela que abriu, clique com o botão direito em Plan1. Clique em Inserir e, depois, em Módulo.
3-Digite o seguinte código:
Function Eh_Feriado(dData As Date) As Boolean
Dim rng As Variant
Dim sData As String
Dim sNome AS string
sNome = ActiveWorkBook.Name
sData = CDate(dData)
Set rng = Workbooks(sNome).Worksheets("Feriados").Range("A1:A500").Find(sData)
Eh_Feriado = Not (rng Is Nothing)
End Function
4º) Vamos criar outra função, que é a que iremos utilizar comumente:
1-Digite o seguinte código, após a End Function da função anterior:
Function AdicionaDiaUtil(dData As Date, iDias As Integer)
'
' Esta função adicionará iDias úteis em dData
'
Dim iCont As Integer
iCont = 1
While iCont <= iDias
'
' Adiciona 1 dia
'
dData = DateAdd("d", 1, dData)
If Eh_Feriado(dData) Then
iDias = iDias + 1
Else
If (Weekday(dData) = 1) Then
iDias = iDias + 1
ElseIf (Weekday(dData) = 7) Then
iDias = iDias + 1
End If
End If
iCont = iCont + 1
Wend
AdicionaDiaUtil = dData
End Function
Não esqueça de salvar!
5º) Vamos criar uma tabela auxiliar para recuperar o nome do dia da semana, para facilitar os testes:
1-Na planilha Tabelas, coloque a seguinte sequência, a partir de E1:
6º) Pronto, já temos o que precisamos para calcular. Vamos criar uma planilha de teste.
1-Abra a Plan2.
2-Em B5, digite: Data:
3-Em B6, digite: Dia da Semana:
Veja que eu atribui o nome TB_DIADASEMANA para essa faixa de células.
4-Em B8, digite: Dias úteis:
5-Em B9, digite: Próxima data:
6-Em B10, digite: Dia da semana
7º) Agora, vamos colocar o Excel para trabalhar por nós:
1-Na célula C5 digite uma data qualquer. Exemplo: 01/01/2011
2-Na célula C6 digite: =PROCV( DIA.DA.SEMANA( C5); TB_DIADASEMANA;2)´
Veja que ele já recupera o dia da semana para a data informada. Para a data 01/1/2011, retorna sábado.
3-Na célula C8 informe a quantidade de dias úteis desejada para a data testada. Digamos: 2
4-Na célula C9 vamos colocar a função que criamos, passando como parâmetro da data, que está em C6 e a quantidade de dias úteis, que está em C9: =AdicionaDiaUtil(C5;C8)
5-Na célula C10 coloque: =PROCV( DIA.DA.SEMANA( C9); TB_DIADASEMANA;2)
Pronto! Para dia 01/01/2011, adicionando 2 dias úteis a essa data teremos 04/01/2011, senão vejamos:
Dia 01/01/2011 é sábado, além de ser feriado.
Dia 02/01/2011 é domingo, não pode contar como o primeiro dia úti.
Dia 03/01/2011 é segunda, primeiro dia útil.
Dia 04/01/2011 é o segundo dia útil.
Teste com 04/03/2011 e veja o resultado!
No próximo post coloco o arquivo com a tabela de feriado completa.
Dúvidas, sugestões, estou à disposição.
quinta-feira, 20 de março de 2008
Selects em DB2
Bem, recentemente estou trabalhando em uma solução que integra um pouco de SQLServer e um pouco de DB2. Estou apanhando muito para me ambientar neste novo ambiente. Ainda não achei material prático que mostre as diferenças entre SQLServer e DB2. Uma hora eu monto um site que mostra as diferenças... Mas, enquanto isso, vou compartilhar alguns SELECTs complexos que precisei ( e apenhei muito ) montar...
Este SELECT mostra as médias de uns contadores, agrupada por hora, por servidor. Se fosse no SQLServer eu criaria uma tabela temporária com 4 colunas e incluiria os registros nela. Mas o usuário que eu tenho não permite criar tabelas temporárias, então, o jeito foi tentar jogar tudo em uma mesma coluna.
SELECT M.VNOMESERVIDOR,
HOUR(m.VHoraInicio) AS hora ,
------------------------------------------------------------
( SELECT
AVG( M1.VVALORCOLETADOMEDIA ) * 100
FROM
MNT.VIW_ITEMCONSOLIDADO M1
WHERE
M1.VIDITEMMONIT = 124 -- % Processador dotNET
AND M1.VDATAREGISTRO = M.VDATAREGISTRO
AND M1.VNOMESERVIDOR = M.VNOMESERVIDOR
AND HOUR( M1.VHORAINICIO ) = HOUR( M.VHORAINICIO )
) AS Processador_DotNet,
-----------------------------------------------------------
( SELECT
AVG( M1.VVALORCOLETADOMEDIA ) * 100
FROM
MNT.VIW_ITEMCONSOLIDADO M1
WHERE
M1.VIDITEMMONIT = 103 -- % Processador IIS
AND M1.VDATAREGISTRO = M.VDATAREGISTRO
AND M1.VNOMESERVIDOR = M.VNOMESERVIDOR
AND HOUR( M1.VHORAINICIO ) = HOUR( M.VHORAINICIO )
) AS Processador_IIS,
-----------------------------------------------------------
( SELECT
AVG( M1.VVALORCOLETADOMEDIA ) * 100
FROM
MNT.VIW_ITEMCONSOLIDADO M1
WHERE
M1.VIDITEMMONIT = 2 -- % Processador COM+
AND M1.VDATAREGISTRO = M.VDATAREGISTRO
AND M1.VNOMESERVIDOR = M.VNOMESERVIDOR
AND HOUR( M1.VHORAINICIO ) = HOUR( M.VHORAINICIO )
) AS Processador_COMPlus
FROM
MNT.VIW_ITEMCONSOLIDADO M
WHERE
M.VDATAREGISTRO = CURRENT DATE
GROUP BY M.VNOMESERVIDOR , M.VDATAREGISTRO, HOUR( M.VHORAINICIO )
Assinar:
Postagens (Atom)