Como analisar grandes dados com o Excel

• Postado por Mirio De Rosa em 26 de junho de 2016 às 8h40.
• Ver Blog

Hoje, discutimos como lidar com grandes conjuntos de dados (grandes dados) com o MS Excel. Este artigo é para comerciantes, como construtores de marcas, agentes de marketing, analistas de negócios e similares, que querem ser práticos com dados, mesmo quando é uma grande quantidade de dados.

Por que se preocupar em lidar com grandes dados?

Se você não é o martelo, você é o prego. Nós, os comerciantes, devemos defender o nosso papel de decisores estratégicos ao manter o controle da função de análise de dados que estamos perdendo para a nova geração de codificadores de software e gerentes de dados. Isso nos obriga a melhorar nossa capacidade de lidar com grandes conjuntos de dados, que podem ter vários benefícios. Talvez o mais atraente do ponto de vista da carreira seja reafirmar nosso valor no novo mundo dos sistemas de TI altamente desenvolvidos, implacavelmente crescentes e muitas vezes inflexíveis, cheios de muitos dados que alguém pensa que poderiam ser muito úteis, se apenas fossem analisados ​​adequadamente.

Nesta medida, muitos departamentos de TI estão empregando Data Architects, Big Data Managers, Data Visualizers e Data Squeezers. Esses programadores, especializados em diferentes tipos de software, são, em alguns casos, já ignorando a colaboração com os comerciantes e indo diretamente no desenvolvimento de aplicativos usados ​​para fins de análise de negócios. Esses caras são os novos concorrentes para o papel do líder empresarial, e eu me pergunto quanto tempo demorará até começar a tomar decisões estratégicas também. Não devemos deixar isso acontecer, a menos que nós gostemos de ser o prego!

Grandes dados práticos

O MS Excel é um aplicativo muito amado, diz alguém por cerca de 750 milhões de usuários. Mas não parece ser o aplicativo apropriado para a análise de grandes conjuntos de dados. Na verdade, o Excel limita o número de linhas em uma planilha a cerca de um milhão; Isso pode parecer muito, mas as fileiras de grandes dados vêm em milhões, bilhões e até mais. Neste ponto, o Excel parece ser de pouca ajuda com a grande análise de dados, mas isso não é verdade. Leia.

Considere que você tem um grande conjunto de dados, como 20 milhões de linhas de visitantes do seu site, ou 200 milhões de linhas de tweets, ou 2 bilhões de linhas de preços diários de opções. Suponha também que você queira investigar esses dados para procurar associações, clusters, tendências, diferenças ou qualquer outra coisa que possa ser de seu interesse. Como você pode analisar esta enorme massa de dados sem usar software críptico e caro gerenciado apenas por usuários experientes?

Bem, você não precisa necessariamente - você pode usar amostras de dados em vez disso. É o mesmo conceito por trás da pesquisa populacional comum :. para investigar as preferências dos homens adultos que vivem nos EUA, você não pede 120 milhões de pessoas; uma amostra aleatória pode fazê-lo. O mesmo conceito se aplica aos registros de dados também, e em ambos os casos há pelo menos três perguntas legítimas a serem feitas:

1. Quantos registros precisamos para ter uma amostra com a qual podemos fazer estimativas precisas?
2. Como extraímos registros aleatórios do conjunto de dados principal?
3. As amostras de grandes conjuntos de dados são confiáveis?

Quão grande é uma amostra confiável de registros?

Para o nosso exemplo, usaremos um banco de dados com 200.184.345 registros contendo dados das ordens de compra de uma linha de produtos de uma determinada empresa durante 12 meses.

Existem várias técnicas de amostragem diferentes. Em termos gerais, eles dividem-se em dois tipos: amostragem aleatória e não aleatória. Técnicas não aleatórias são usadas somente quando não é possível obter uma amostra aleatória. E a   técnica de amostragem aleatória simples é apropriada para aproximar a probabilidade de algo acontecer na população maior, como em nosso exemplo.

Uma amostra de 66.327 registros selecionados aleatoriamente pode aproximar as características subjacentes do conjunto de dados de que ele provém no intervalo de confiança de 99% e nível de erro de 0,5%. Esse tamanho de amostra é definitivamente gerenciável no Excel.

Imagem 1: tamanhos aleatórios de amostra produzidos com a fórmula bernoulliana de acordo com o tamanho da população, o intervalo de confiança e o nível de erro. Tabela produzida com a ferramenta Sample Manager do software MM4XL .

VEJA TAMBÉM:  cálculo de cálculo de tamanho de amostra aleatório fácil e preciso

O nível de confiança nos diz que se extraímos 100 amostras aleatórias de 66.327 registros cada uma da mesma população, 99 amostras podem assumir que reproduzem as características subjacentes do conjunto de dados de que elas vieram. O nível de erro de 0,5% diz que os valores que obtemos devem ser lidos no intervalo de mais ou menos 0,5%, por exemplo, depois de transformar os registros em tabelas de contingência.

Como extrair amostras aleatórias de registros

As estatísticas de amostragem são a solução. Utilizamos a ferramenta  Sample Manager  do  software MM4XL  para quantificar e extrair as amostras utilizadas para este documento. Se você não possui MM4XL, você pode gerar números de registro aleatórios da seguinte maneira:

• Digite no Excel 66.327 vezes a fórmula = RAND () * [Tamanho do conjunto de dados]
• Transforme as fórmulas em valores
• Redonde os números para não decimais
• Verifique se não existem duplicatas [2]
• Classifique o intervalo e você obtém uma lista de números como mostrado na seguinte imagem

Extraia esses números de registro do conjunto de dados principal: Esta é a sua amostra de registros aleatórios. O número 3.076 na célula A22 mostrado acima significa que o número de registro 3.076 do conjunto de dados principal está incluído na amostra. Para reduzir o risco de extrair registros tendenciosos pela falta de aleatoriedade, antes de extrair os registros da amostra, é um bom hábito classificar a lista principal, por exemplo, alfabeticamente pelo primeiro nome da pessoa ou por qualquer outra variável que não seja diretamente relacionado aos valores do (s) objeto (s) da (s) variável (s) do estudo.

Se estivermos prontos para aceitar a aproximação imposta por uma amostra, podemos desfrutar da liberdade de sermos práticos com nossos dados novamente. De fato, embora 66.327 registros possam ser gerenciados bastante bem no Excel, ainda temos uma amostra grande o suficiente para descobrir pequenas áreas de interesse.

Os grandes tamanhos de amostra nos permitem gerenciar conjuntos de dados muito grandes no Excel, um ambiente que a maioria de nós está familiarizado. Mas qual é a confiabilidade de tais amostras na vida real?

As amostras de grandes conjuntos de dados são confiáveis?

A questão-chave é: uma amostra aleatória pode reproduzir com precisão as características subjacentes da população de que é extraído? Para encontrar alguma evidência, nós:

• Mediu várias características de todo o nosso conjunto de dados, a população subjacente.
• Em seguida, extraímos amostras aleatórias do conjunto de dados principal e medimos as mesmas características que para todo o conjunto de dados.
• Finalmente, realizamos vários testes de confirmação, comparando as medidas realizadas nas amostras e no conjunto de dados principal.

A imagem abaixo mostra os campos de nossos registros. Eles podem ser lidos da seguinte forma: O registro número 1 (linha 2) é uma ordem de compra da América do Norte, recebida em setembro de 2007, referente a um único item com preço de USD 13.159 e vendido por US $ 11.800.

A próxima imagem mostra as medidas calculadas nas variáveis ​​individuais. O   valor Média  e Média foi calculado para os campos "Volume", "Vendas" e "Vendas com desconto" (veja o intervalo D1: G3). Contagem  e  Contagem Frequências  foram encontradas para as variáveis ​​"Continente" (ver intervalo I1: K4) e "Mês" (ver alcance M1: O13). Por exemplo, 1.865 na célula E2 representa o número médio de itens em uma ordem de compra. USD 5,841 em G2 significa o preço médio de venda de um item vendido. Em K2, 20.815% é a parcela de itens (produtos) vendidos para a Ásia e, em O2, 8.556% é a parcela de itens vendidos em janeiro de 2008. Vamos testar se as amostras aleatórias podem se aproximar dos valores médios e das freqüências percentuais mostradas em a seguinte imagem.

De acordo com o manual ASTM [1], desenhamos 20 amostras selecionadas aleatoriamente de 66.327 registros cada uma da população de 200.184.345 registros. Para cada amostra, calculamos os mesmos valores mostrados na imagem acima e para cada valor aplicamos uma prova Z para identificar quaisquer valores anômalos nas amostras. Os testes Z foram executados para variáveis ​​contínuas e discretas.

All-in-one, métricas contínuas

A tabela seguinte mostra os resultados do teste para as variáveis ​​"Volume", "Vendas" e "Vendas com desconto". A célula B1, por exemplo, nos informa, em média, uma ordem de compra (um registro) das principais contas de conjuntos de dados para um volume de vendas igual a 1.865 itens com um valor "Vendas médias" de US $ 10'418 e uma "Vendas com desconto médio "Valor de USD 5'841. Para os valores de amostra que partem severamente dos valores de controle na linha 2, a probabilidade é alta de que um teste Z no nível de probabilidade de 99% capture a anomalia.

De acordo com as colunas "Z-Test" da imagem acima, nenhuma amostra mostra valores médios diferentes das médias do conjunto de dados principal. Por exemplo, o "Volume Médio" da Amostra 3 e o do conjunto de dados principal diferem apenas de 0,001 unidades por ordem de compra ou uma diferença de cerca de 0,5%. As outras duas variáveis ​​mostram um cenário semelhante. Isso significa que a Amostra 3 produziu valores bastante precisos no nível global (todos os registros contados em uma única métrica).

Métricas categóricas

We tested the variable “Continent”, which splits into three categories: Asia, Europe and North America. Columns B:D of the following table show the share of orders coming from each continent. The data in row 15 refer to the main dataset. In this case too, the difference between main dataset and samples is quite small, and the Z-Test (columns E:G) shows no evidence of bias, with the exception of slight deviations in Europe for sample 5, 8, 9, and 18-20.

Os valores de "Probabilidade" na coluna H: J medem a probabilidade de um valor de amostra ser diferente do mesmo valor do conjunto de dados principal. Por exemplo, 21,1% em B36 é menor do que 20,8% em B35. No entanto, como o primeiro vem de uma amostra, precisamos verificar, de um ponto de vista estatístico, a probabilidade de que a diferença entre os dois valores seja causada por um viés no método de amostragem. Os 95% são um nível comum de aceitação ao lidar com esse tipo de problema. Com pequenos tamanhos de amostra (30), o limite de probabilidade de 90% ainda pode ser usado, embora isso implique maior risco de considerar erroneamente dois valores iguais, quando na verdade eles são diferentes. Por razões de confiabilidade de teste, trabalhamos com o limite de probabilidade de 99%. Em H36, lemos a probabilidade B36 é diferente de B35 é igual a 81%, o início da área onde podem ser encontradas diferenças anômalas. Apenas a parcela da amostra 5 e 19 para a Europa é superior a 90%. Todos os outros valores estão bem longe de uma posição preocupante.

Isso também significa que a parcela das ordens de compra recebidas dos três continentes reproduzidos com amostras aleatórias não mostra evidências de diferenças dramáticas fora dos limites esperados. Isso também pode ser confirmado intuitivamente simplesmente observando os valores médios da amostra na faixa B36: D55 da imagem acima; eles não diferem dramaticamente dos valores populacionais na faixa B35: D35.

Finalmente, testámos a variável "Mês", que se divide em 12 categorias e, portanto, pode gerar resultados mais baixos do Z-Test devido ao tamanho reduzido da amostra por categoria. As colunas B: M na tabela a seguir mostram a probabilidade de que o número amostrado de ordens de compra por mês difira do mesmo valor do conjunto de dados principal. Nenhum valor amostrado é diferente do valor correspondente do conjunto de dados principal com uma probabilidade maior do que 75% e apenas um pequeno número de valores tem uma probabilidade maior do que 70%.

Os resultados dos testes realizados em todas as amostras não encontraram anomalias graves que desencorajariam a aplicação do método descrito neste artigo. A análise de conjuntos de dados grandes por meio de amostras aleatórias produz resultados confiáveis.

Pode provar a confiabilidade deste Experimento por acaso?

Até agora, as amostras aleatórias tiveram um bom desempenho na reprodução das características subjacentes do conjunto de dados de que elas vieram. Para verificar se isso poderia ter acontecido por acaso, repetimos o teste usando duas amostras não aleatórias: a primeira vez que tomamos os primeiros 66,327 registros do conjunto de dados principal e a segunda vez levando os últimos 66,327 registros.

Os resultados em breve: de 42 testes realizados com variáveis ​​discretas e categóricas das duas amostras não aleatórias, apenas três apresentaram valores de Z-Test verdes. Ou seja, esses três valores de amostra não foram julgados diferentes do mesmo valor do conjunto de dados principal. Os restantes 39 valores, no entanto, situam-se em uma região vermelha profunda, o que significa que eles produziram uma representação não confiável dos principais dados.

Estes resultados também suportam a validade da abordagem por meio de amostras aleatórias e confirmam que os resultados do nosso experimento não são por acaso. Portanto, a análise de grandes conjuntos de dados por meio de amostras aleatórias é uma opção legítima e viável.

Fechamento

Este é um ótimo momento para os comerciantes orientados a dados e dados: há uma grande e crescente demanda por análises, mas não há cientistas de dados suficientes disponíveis para atender ainda. As estatísticas e a codificação de software são as duas áreas pelas quais devemos aprofundar nosso conhecimento. Nessa fase, uma nova geração de comerciantes nascerá e esperamos sua contribuição para o implacável mundo em evolução da construção de marca. Ferramentas de software como o MM4XL podem nos ajudar ao longo do caminho porque são escritas para pessoas de negócios e não como estatísticas. Tais ferramentas devem se tornar um componente chave da nossa caixa de ferramentas para gerar percepções de dados e tomar melhores decisões de negócios.

Aproveite a análise de dados importantes com o Excel e leia meus futuros artigos para os tomadores de decisão empresariais baseados em fatos e dados.

Obrigado por ler o meu post. Se você gostou desta publicação, então clique no   botão " Curtir ". Também sinta-se livre para se conectar comigo via LinkedIn . 

Você também pode estar interessado no meu livro  Mapping Markets for Strategic Purposes .

[1]  Manual sobre a Apresentação de Análise de Dados e Análise de Controle , 7ª edição. ASTM International, E11.10 Subcomitê de Amostragem e Análise de Dados.

[2] Meus sinceros agradecimentos a Darwin Hanson, que capturou alguns problemas: (1) RND é uma função VBA, e eu entendi, claro, a função de planilha RAND; e (2) você deve verificar novamente os números aleatórios e livrar-se de duplicatas.