Fuçador

Respondendo ao Max - Performance em planilha Excel

2011-07-25T06:49:00.000-07:00

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.

Definido bordas no Excel, via VBA

2011-03-03T07:04:00.001-08:00

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

Excel: Ordenando células via VBA

2011-02-28T05:09:00.001-08:00

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!

Redirecionando páginas em PHP

2011-02-14T14:14:00.000-08:00

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 é:

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.

Gravando fórmulas via VBA em células

2011-01-11T08:57:00.000-08:00

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!

Excel: Adicionar dias úteis a uma data

2010-12-20T03:29:00.000-08:00

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.

Selects em DB2

2008-03-20T09:19:00.000-07:00

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 )

Session State - Compartilhando sessões no ASP.Net

2008-02-15T02:30:00.001-08:00

Session-State

Fonte: http://forums.asp.net/p/7504/7504.aspx#7504

1-Localização do armazenamento:

InProc – a sessão é mantida como objetos ativos no web Server (aspnet_wp.exe)

StateServer – A sessão é serializada e armazenada em memória, em um processo separado (aspnet_state.exe). A vantagem é que o Stateserver pode ser executado em outra máquina

SQLServer – a sessão é serializada e armazenada em um MS-SQLServer.

Performance:

No InProc é mais rápido, mas, quanto mais dados de sessão, mais memória é consumida no webserver, que pode afetar a performance. Além disso, qualquer situação que provoque o restart do aspnet_wp.exe fará com que um novo processo seja criado e, assim, todas as sessões atuais serão perdidas.

StateServer: quando tipos básicos, como string, inteiros, etc. são armazenados, é cerca de 15% mais lento que o método In-Proc. Quanto mais serialização/desserialização for necessária, maior a penalidade na performance. A vantagem é que, rodando em um servidor separado, este pode ser dedicado a essa função (um luxo?).

SQLServer: quando tipos básicos, como string, inteiros, etc. são armazenados, é cerca de 25% mais lento que o método In-Proc. Mesmas considerações para o StateServer. A vantagem desse método é que se o aspnet_wp for reiniciado, os dados das sessões serão preservados (dentro do tempo limite de validade). Pode-se ainda, utilizar um SQL em cluster, para garantir escalabilidade e, combinado com um balanceamento de carga de rede, distribuir as requisições, diminuindo o tempo de resposta.

Prevalece o entendimento de que se deve procurar armazenar as sessões com tipos básicos, visto que estes utilizam um código performático para a serialização, desserialização. Para outros tipos, é utilizado um método binário, que é mais lento, para esta tarefa.

Mais dicas, acesse: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/aspnetsessionstate.asp

Acessando base de dados remotas com linkedserver

2008-02-15T02:28:00.001-08:00

Estou trabalhando em um projeto que tem a necessidade de restringir os acessos de usuários a uma base de dados, dando permissão de leitura e escrita com base em Application Roles.

Até aí tudo bem, elas fucionam e são rápidas. Mas acontece que a aplicação está distribuída em várias bases de dados, em servidores diferentes. Então entra em cena a figura do LinkedServer.

Vou postar aqui um case onde, conectando como um usuário sem restrições totais, aplicando uma Application Role, a partir de uma aplicação, liberar um linkedserver temporariamente para o mesmo usuário e fazer os devidos acessos.

Fácil, depois de fuçar um pouco... Aguardem