Exportar (0) Imprimir
Expandir Tudo

Analisando o espaço em disco com o Excel

Bruno Sonnino

Junho, 2013

Dn262689.060DE5057573180CEC6D227C6D3E2207(pt-br,MSDN.10).png

Introdução

Atualmente, o espaço em disco disponível é muito grande, são comuns discos de até 4 terabytes e não precisamos nos preocupar com o que armazenamos neles. Porém, isto não é completamente verdade, pois:

  • Não importa o tamanho do disco, sempre conseguiremos preenchê-lo.
  • Embora os discos comuns sejam muito grandes, é cada vez mais comum usar discos SSD para boot e estes tem tamanho bastante limitado, devido ao seu custo.
  • Com pouco espaço em disco, a velocidade de acesso diminui, devido à fragmentação.
  • Nossos computadores são extensões de nossa casa e, da mesma maneira que não devemos deixar a casa bagunçada, devemos também arrumar nossos discos de vez em quando.

Uma maneira de gerenciar o espaço em disco é usar ferramentas especializadas, como meu utilitário DiskPiePro (http://www.pcmag.com/article2/0,2817,2354473,00.asp). Embora um utilitário específico seja uma alternativa muito boa, há uma solução muito interessante e flexível para fazer isso: usar o Powershell e o Excel para o gerenciamento.

O Powershell é, segundo a Wikipedia, “um novo prompt de comando do Windows, muito mais poderoso que o cmd.exe, voltado à automatização, via scripts e canalização de objetos por uma sequência de comandos, para manutenção de sistemas por parte de administradores, além de um controle maior do sistema. O Windows Powershell fornece acesso à todas as APIs .NET disponíveis no sistema, além dos objetos COM, e outras APIs Microsoft”. Com ele, você pode acessar facilmente os dados do sistema e, cada vez mais, outras ferramentas expõem suas APIs ao Powershell. Com ele, podemos obter as informações de seu disco e exportá-las para um arquivo csv (Comma-separated-values – valores separados por vírgulas), que pode ser lido e analisado no Excel. Esta é uma solução muito poderosa e flexível, como veremos a seguir.

Criando um script Powershell

Para se trabalhar com o Powershell, inicialmente devemos abrir uma janela Powershell. Para isso abrimos o menu iniciar do Windows e teclamos Powershell. Temos ali as opções para abrir um prompt de comando Powershell ou abrir o Powershell ISE, que é uma IDE para desenvolvimento Powershell. Eu prefiro usar o ISE, pois ele tem uma série de recursos, inclusive o Intellisense, que permite completar os comandos e ver as opções disponíveis de cada comando.

Se você estiver usando o Windows 8, o Powershell ISE fica escondido, você tem que abrir uma janela do Explorer e ir para o diretório c:\windows\system32\WindowsPowerShell\V1.0 e abrir o arquivo Powershell_ise.exe.

Usando o comando Get-ChildItem (por convenção, os comandos Powershell são compostos de um verbo e um nome) podemos obter a lista de arquivos do diretório atual (só para nos situarmos, aqui, o comando Get-ChildItem tem dois aliases, dir e ls – deu para entender o que ele faz, não?). Para obter a lista de arquivos também nos subdiretórios usamos o parâmetro recurse. Para evitar que o comando seja abortado caso algum diretório não seja acessado, usamos o parâmetro ErrorAction. Assim, o comando para obter a lista de arquivos no diretório atual e nos subdiretórios é:

Get-ChildItem -recurse -ErrorAction SilentlyContinue

Até aqui, não fizemos nada que não pudesse ser feito com um simples comando dir, mas uma das coisas que tornam o Powershell poderoso é a possibilidade de compor comandos. Queremos filtrar os nossos arquivos, eliminando aqueles que são muito pequenos, para que não atrapalhem nossa análise. Isto é feito usando a técnica de piping: pegamos a saída de um comando e usamos como entrada no próximo, usando o caractere “|”. O comando para filtragem é o Where-Object. Para filtrar apenas os arquivos com mais de 1MB, usamos o comando:

Where-Object Length -gt 1MB

Esta sintaxe só é válida para o Powershell 3 (se você não tiver o Powershell 3 instalado, pode filtrar usando a sintaxe Where-Object {$_.Length -gt 1MB}). Aqui estamos dizendo que queremos apenas os objetos que tem tamanho superior a 1MB.

O comando Get-ChildItem retorna um conjunto de objetos, correspondentes aos arquivos, com muitas propriedades, e não queremos todas elas (se você quiser ver quais são estas propriedades, use o comando Get-ChildItem | Get-Member), por isso iremos filtrar as propriedades que queremos com o comando Select-Object:

Select-Object -Property Name, Length, DirectoryName, FullName, Extension

Finalmente, iremos exportar os nossos dados para um arquivo csv, com o comando Export-Csv:

Export-Csv LargestFiles.csv –NoTypeInformation -UseCulture

Compondo todos os comandos, podemos criar um pequeno script que obtém todos os arquivos maiores de 1MB e gravar num arquivo csv. O script abaixo faz exatamente isso. Alterei um pouco o script para que possamos parametrizar os dados do diretório, tamanho mínimo e arquivo de saída, assim, nosso comando fica mais flexível:

[CmdletBinding()]
param (
[string]$initialpath = '.',
[int]$minimumsize = 1MB,
[string]$outputfile = 'LargeFilesReport.csv'
)
Get-ChildItem $initialpath -Recurse -ErrorAction "SilentlyContinue" | 
Where-Object {$_.Length -gt $minimumsize} |
Select-Object -Property Name, Length, DirectoryName, FullName, Extension |
Export-Csv $outputfile -NoTypeInformation -UseCulture

Ao salvar o script como Get-LargestFiles.ps1 (lembre-se, por convenção, os scripts Powershell tem um verbo e um nome), podemos chamá-lo para obter todos os arquivos maiores de 10Mb no diretório atual e gravá-lo num arquivo com nome MaioresArquivos.csv com um dos dois comandos:

.\Get-LargestFiles –initialpath . –minimumsize 10MB –outputfile MaioresArquivos.csv

Ou

.\Get-LargestFiles . 10MB MaioresArquivos.csv

Os parâmetros podem vir desordenados, precedidos do nome do parâmetro ou então em ordem, sem o nome do parâmetro. Podemos ainda omitir alguns parâmetros e os valores default serão usados. Se, ao executar este script, você receber um erro de acesso negado, provavelmente é porque, por default, o Powershell restringe acesso a scripts. Você deve então abrir uma janela do Powershell com acesso de administrador e dar o comando Set-ExecutionPolicy RemoteSigned, que permite execução de quaisquer arquivos locais, mas apenas arquivos remotos assinados.

Como você pode ver, o Powershell é muito poderoso, muito mais que uma simples linguagem de script (quem se lembra dos arquivos batch do DOS?) e permite obter muitas informações sobre o sistema, vale a pena conhecê-lo melhor.

Agora que já temos o nosso arquivo csv, vamos analisá-lo com o Excel. Se você tem a extensão csv associada ao Excel, pode abri-lo diretamente do Powershell, com o comando

Invoke-Item .\LargeFilesReport.csv

Este comando chama o Excel e abre nosso arquivo com a lista dos maiores arquivos, para que possamos analisá-lo.

Analisando o arquivo com o Excel

Arrumando o arquivo

Ao abrir o arquivo, os dados são lidos e interpretados, cada dado está em uma coluna. Podemos mudar o tamanho de cada coluna, para poder visualizar os dados. Isto pode ser feito selecionado toda a planilha clicando na borda superior, à esquerda da coluna A e selecionado Formatar/AutoAjuste da Largura da Coluna (Format/AutoFit Column Width), ou então dando um duplo clique na borda superior que divide duas colunas, para ajustar apenas uma coluna, ou ainda arrastando e soltando a borda da coluna que desejamos ajustar.

O próximo passo é classificar a planilha, para que os maiores arquivos apareçam antes. Com a planilha selecionada, clique em Classificar e Filtrar (Sort & Filter) e selecione Personalizar Classificação (Custom Sort). Classifique pela coluna Length, na ordem Do Maior para o Menor.

Em seguida, iremos formatar os dados de tamanho do arquivo. Clique na primeira célula abaixo de Length e tecle [Shift] + [End] + [Seta para baixo]. Isto seleciona todos os dados de tamanho do arquivo. Antes de qualquer coisa, iremos nomear esta faixa, para facilitar o acesso: na barra superior, à esquerda da entrada de dados (onde está o nome da célula), digite a palavra Tamanhos e tecle [Enter]. Isto faz com que os tamanhos dos arquivos possam ser acessados facilmente por este nome.

Queremos mostrar os tamanhos dos arquivos como são mostrados na janela do Explorer, em GB para os arquivos com mais de 1GB, em MB para os com mais de 1MB e com KB para aqueles com mais de 1KB. A primeira idéia que veio à cabeça foi criar uma nova coluna e dividir os valores de tamanho por 1.000, 1.000.000 ou 1.000.000.000, conforme o caso. Porém, há uma maneira mais simples de fazer isso: usando a formatação personalizada. Com os tamanhos selecionados, clique com o botão direito e selecione Formatar Células (Format Cells). Selecione o formato Personalizado (Custom) e entre com o seguinte formato:

[<1000000]0,00." KB";[<1000000000]0,00.." MB";0,00..." GB"

Aqui estamos usando as configurações regionais do Brasil. Se você estiver usando as configurações regionais dos Estados Unidos em seu computador, deve alterar os “.” pelas “,” e vice-versa no formato acima.

Esta formatação é muito diferente do que estamos acostumados, mas ao mesmo tempo, muito poderosa. Vamos explicar: note que o formato tem 3 partes, separadas por “;”. Na primeira, [<1000000]0,00." KB", o Excel verifica se o número é menor que 1.000.000. Se for aplica a formatação 0,00." KB". O “.” ao final do formato faz com que o número seja dividido por 1000. O resultado é mostrado com duas decimais e o string “KB” ao final. A segunda parte é semelhante, usando duas virgulas no formato, para dividir por 1.000.000 e acrescentando o string “MB”. A terceira parte, com três vírgulas, divide por 1.000.000.000 e acrescenta “GB” ao final. Fácil quando se sabe, não?

A primeira pergunta que vem à mente quando estamos gerenciando os nossos discos é quanto espaço os maiores arquivos estão ocupando e quantos arquivos tem mais que 1MB. Insira duas linhas acima das linhas de título e, na célula A1, coloque o título “Total Geral”. Na célula B1, coloque a fórmula =SOMA(Tamanhos) (=SUM(Tamanhos)). Na célula C1, coloque a fórmula =CONT.NÚM(Tamanhos) (=COUNT(Tamanhos)). Para formatar o número de arquivos, clique com o botão direito na célula B1 e selecione Formatar Células (Format Cells) e o formato Personalizado (Custom). Na formatação, digite #.##0 "Arquivos".

Em seguida, deixaremos os títulos fixos. Selecione a célula abaixo do título (A4) e, na guia Exibição (View), clique em Congelar Painéis/Congelar Painéis (Freeze Panes/Freeze Panes). Desta maneira, as três primeiras linhas ficam congeladas e são mostradas, mesmo quando rolamos a tabela para baixo.

Dn262689.5CF1CB6C78549EE0C4E3A7B5FBA7212C(pt-br,MSDN.10).png

Figura 1 – Planilha com formato de tamanho e totais de espaço ocupado e número de arquivos

A nossa formatação inicial será terminada dando uma indicação do tamanho dos arquivos, de modo que possamos localizar facilmente os arquivos que mais nos interessam. Para isso, iremos usar o recurso de Formatação Condicional. Este tipo de formatação permite destacar nossos dados de diversas maneiras, com cores, gráficos ou ícones.

Usaremos a formatação condicional com ícones, usando um ícone vermelho para os maiores de 100MB, amarelo para os maiores de 10MB e verde para os demais. Posicione o cursor na combobox onde colocamos o nome Tamanhos e clique no botão da caixa de seleção e selecione o nome Tamanhos. Com isso, nossa faixa de dados é selecionada, sem que precisemos fazer mais nada. Em seguida, na Guia Página Inicial (Home), selecione a opção Formatação Condicional/Conjunto de Ícones (Conditional Formatting/Icon Sets) e selecione o conjunto com três círculos, verde amarelo e vermelho. Este tipo de formatação coloca ícones verdes nos primeiros 33%, amarelos nos 33% intermediários e vermelhos nos últimos 33%. Embora queiramos algo parecido com isso, ainda não é o que queremos. Assim, clicamos novamente em Formatação Condicional (Conditional Formatting) e selecionamos Gerenciar Regras (Manage Rules). Damos um duplo clique na regra para alterá-la. Inicialmente, alteramos o primeiro ícone para vermelho e o último para verde. Em seguida, mudamos o Tipo dos dois primeiros ícones para Número (Number) e, finalmente, alteramos o valor do primeiro ícone para 100000000 e do segundo ícone para 10000000. Desta maneira, os arquivos com mais de 100MB ficarão com um círculo vermelho, os com tamanhos entre 10 e 100Mb, com um círculo amarelo e os demais, com um círculo verde. Ao clicar OK, vemos algo como a Figura 2.

Dn262689.D55DE1BE27D627783E849768FBA24C5C(pt-br,MSDN.10).png

Figura 2 – Planilha com formatação condicional

Totalizar por Extensões

Uma outra maneira de analisar nosso disco é saber quais as extensões que estão ocupando nosso disco. Para isso, iremos criar uma nova planilha, que chamaremos de “Extensões”. Nesta planilha, colocaremos todas as extensões selecionadas e os totais de arquivos para cada extensão. Nas células A1 a C1 coloque os títulos: “Extensão”, “Tamanho” e “Quantidade”.

Em seguida, copie as extensões da coluna E para a célula A2 da nova planilha. Com as extensões ainda selecionadas, vá para a guia Dados (Data) e selecione a opção Remover Duplicatas (Remove Duplicates). A caixa de diálogo pergunta quais as colunas que quer usar para as duplicatas. Selecione a coluna A e clique em OK. O Excel mostra quantas duplicatas foram removidas.

Para obter o total de espaço ocupado por cada extensão, usamos a função SOMASE (SUMIF). Esta função usa um critério para somar os dados. Se o critério é obedecido, os dados são somados. Se não, nada é somado. Esta função tem três parâmetros:

  • O intervalo de valores – em nosso caso, são as extensões, ou o intervalo de E4 ao final, na primeira planilha
  • O critério, que é a extensão que queremos correspondência, a célula A2
  • O intervalo de soma, que em nosso caso é o intervalo Tamanhos

A fórmula que deve ser colocada na célula B2 é =SOMASE(LargeFilesReport!$E$4:$E$1451;A2;Tamanhos) (=SUMIF(LargeFilesReport!$E$4:$E$1451;A2;Tamanhos)). Copie esta fórmula para as demais extensões, assim você pode ver qual o tamanho ocupado por cada extensão. Para saber a quantidade de arquivos em cada extensão, usamos a função CONT.SE (COUNTIF). Ela é semelhante à função SOMASE, mas tem apenas dois parâmetros, o intervalo e o critério. A fórmula a ser usada é =CONT.SE(LargeFilesReport!E4:E1449;A2) (=COUNTIF(LargeFilesReport!E4:E1449;A2)). Copiando esta fórmula, podemos saber a quantidade de arquivos em cada extensão.

Vamos classificar os dados por tamanho, para saber quais as extensões que ocupam mais espaço. Selecione as três colunas e, na guia Página Inicial (Home), selecione Classificar e Filtrar/Personalizar Classificação (Sort and Filter/Custom Sort). Selecione a classificação por tamanho, do maior para o menor.

Para ilustrar estes dados, iremos colocar um gráfico de pizza que mostra o uso do espaço em disco por extensão. Selecione os dados das duas primeiras colunas (clicando na célula A1 e teclando Shift-End Seta para Baixo e Seta para a Direita) e, na guia Inserir (Insert), selecione um gráfico de Pizza 2D, do tipo Pizza de Pizza (Pie of Pie). Um gráfico semelhante ao da Figura 3 é inserido, permitindo visualizar quais as extensões ocupam mais espaço no meu disco. No caso, vemos que as extensões bak, dll, lib, plg e exe ocupam mais de ¾ do espaço em disco!

Dn262689.20D7F713A0A4D6330263C5350FB6AFBF(pt-br,MSDN.10).png

Figura 3- Tabela de tamanhos por extensão com gráfico

Curva ABC

Agora já temos a nossa lista de arquivos classificada, mas ainda não sabemos quais devemos nos preocupar – para limpar os 11GB, deveríamos excluir todos os 1400 arquivos, e isso dá bastante trabalho. Vamos otimizar nosso trabalho e ver quais os arquivos que realmente importam. Pela regra 80-20 (http://en.wikipedia.org/wiki/Pareto_principle), 80% de nosso espaço em disco deve ser ocupado por apenas 20% dos arquivos. Iremos ver se realmente isso acontece aqui. Para isso iremos criar uma Curva ABC. Para esta curva, criamos uma nova planilha, clicando no símbolo “+” na lista de planilhas e renomeamos para “Curva ABC”. Na primeira planilha, selecionamos o título da coluna FullName e teclamos [Shift] +[End] + [Seta para baixo] para selecionar os nomes e teclamos [Ctrl] + [C] para copiar. Voltamos para a segunda planilha e colamos os dados clicando na célula A1 e teclando [Ctrl] + [V]. Na célula B1, colocamos o título % Acumulado.

A fórmula que iremos criar mostra o percentual acumulado de cada arquivo no total do espaço em disco. Por exemplo, o nosso primeiro arquivo ocupa 3.77GB/11.34GB do disco, ou 33,25% do espaço. O segundo arquivo ocupa 266.67MB/11.34GB do disco, ou 2,35% do espaço, que acumulados ao anterior, dão 35,60% do espaço. Vamos somando os percentuais acumulados nesta coluna e vendo o percentual. Quando ele chegar a 80%, atingimos 80% do espaço ocupado e deixamos de lado os arquivos restantes.

Para criar esta fórmula, vamos na célula B2 da planilha que criamos e usamos a função =SOMA() (=SUM()). Vamos para a primeira planilha e selecionamos a célula B4, teclamos “:” e selecionamos B4 novamente. Como queremos fixar o valor inicial (sempre iremos somar de B4 até a célula atual) ao copiar, colocamos o cursor no primeiro B4 e teclamos F4 para fixar este ponto. O Excel coloca “$” na linha e na coluna, para indicar que este valor é fixo e não será alterado quando copiarmos ele. Fechamos a soma com o parêntese e teclamos “/”. Em seguida, vamos para o valor total, selecionamos ele e teclamos F4, para que não seja alterado na cópia. Nossa fórmula fica:

=SOMA(LargeFilesReport!$B$4:LargeFilesReport!B4)/LargeFilesReport!$B$1

Ao teclar [Enter], o valor referente ao percentual do primeiro arquivo aparece. Para formata-lo como percentual, clicamos com o botão direito, selecionamos Formatar Células (Format Cells) e selecionamos Porcentagem (Percentage). Quando selecionamos uma célula, no canto inferior direito aparece um pequeno quadrado. Podemos arrastar este quadrado para copiar as célula, e é o que faremos agora. Selecione o quadrado e arraste a seleção até o final dos arquivos. Ao soltar, a célula é copiada para toda a lista. Podemos agora verificar onde é que está o nosso limite de 80%. No meu caso, o percentual de 80% é na linha 278, ou seja no 277º arquivo, de um total de 1446, ou seja, 19.16% dos arquivos. Bem próximo de 20%, não?

Podemos complementar esta planilha com um gráfico. Selecione a célula B1 e tecle [Shift] + [End] + [Seta para Baixo] para selecionar os percentuais. No Excel 2013 aparecem as sugestões, selecione a sugestão Gráfico e insira um gráfico de linha. Um gráfico semelhante ao da figura 4 é mostrado.

Dn262689.BA989DB2B6CD278D847F727656B196FE(pt-br,MSDN.10).png

Figura 4 – Planilha com gráfico ABC

Note que eu fiz uma alteração neste gráfico. Quando você adiciona o gráfico, o eixo dos percentuais vai até 120%. Como eu não quero percentuais acima de 100%, eu cliquei no eixo para selecioná-lo, selecionei Formatar Eixo e coloquei o valor máximo em 1.

Podemos finalizar nossa planilha, deixando-a mais flexível, com o recurso de Auto-Filtro. Selecione os dados da primeira planilha e, na guia Dados (Data), clique em Filtro (Filter). Com isso, aparece um botão em cada coluna de título, onde podemos filtrar ou classificar facilmente por qualquer coluna de dados.

Quando usamos o Auto-Filtro, a soma que colocamos na primeira linha não corresponde aos valores filtrados, e sim aos valores totais. Para termos os valores referentes ao filtro, podemos usar a função SUBTOTAL. Na célula A2, coloque o título “Total Filtrado”. Na célula B2, coloque a função =SUBTOTAL(9;Tamanhos). Isto faz com que a célula mostre a soma de tamanhos dos registros filtrados. Na célula C2, coloque a fórmula =SUBTOTAL(3;Tamanhos), para mostrar a quantidade de arquivos no filtro. Agora, quando filtramos os dados, a primeira linha ainda mostra o total referente a todos arquivos, enquanto que a segunda linha mostra o total referente ao filtro. Por exemplo, na figura 4 usei o Auto-Filtro para filtrar todos os arquivos com nome de diretório iniciado por “D:\Projetos”. Assim, posso saber que em minha pasta Projetos tenho 558 arquivos (aproximadamente 1/3 do total), ocupando 6,18GB (um pouco mais da metade do total dos arquivos).

Dn262689.165944CE46BFA0711728F93C14D30ED2(pt-br,MSDN.10).png

Figura 5 – Planilha com dados filtrados por Auto-Filtro

Conclusões

Pudemos ver neste artigo um pouco do poder e da flexibilidade do Excel. Quando combinamos ele com outras ferramentas, como o Powershell, este se multiplica. Diversas ferramentas exportam seus dados para o formato csv, o que permite que sejam importados e analisados pelo Excel.

Vimos aqui que, com a saída dos dados do Powershell no formato csv, pudemos importá-los para o Excel e analisá-los de diversas maneiras. Isto é bastante interessante, pois permite que o usuário personalize os seus dados da maneira que quiser, obtendo relatórios ou gráficos facilmente, sem que seja necessário nenhum tipo de programação especial (você notou que não usamos nenhum tipo de linguagem de programação ou macro, apenas funções do Excel, não é?).

Mostrar:
© 2014 Microsoft