Top Excel Tips For Data Analysts

clicar na imagem apra seguir o link

clicar na imagem apra seguir o link

Excelentes concelhos para utilização das últimas ferramentas implementadas no Excel.

TIPS FOR DATA CLEANING
1) Change format of numbers from text to numeric
2) Unpivot columns in a data set (Multiple consolidation ranges and Power Query)
3) Merge data from several csv files into a single folder (RDBMerge Add-in and Power Query)
4) Fill empty spaces from content above (Ctrl + Enter trick and Power Query)
DATA ANALYSIS
5) Create auto expandable ranges with Excel Tables (Source for pivots, dropdown lists and formulas)
6) How to do two way lookup with INDEX and MATCH
7) Creating OR criteria within SUMIF/COUNTIF (Combination of SUMPRODUCT and SUMIF/COUNTIF)
8) Counting unique items within PivotTables (Using the Excel Data Model)
DATA VISUALIZATION
9) Quickly visualize trends with Sparklines
10) Create dynamic titles in charts (Use of cell references within chart objects)
11) Dealing with empty cells in charts and sparklines [use NA()]
12) Save time with Quick Analysis

Tags: ,

How to create a slicer in Excel

clicar para seguir o link

clicar para seguir o link

Bom tutorial de como usar umas das novas funcionalidades do Excel

For dashboards and quick filtering, you can’t beat Excel slicers. They’re easy to implement and even easier to use. Here are the basics–plus a few power tips.

Tags:

S-PLUS & R Class Links

clicar na imagem para seguir o link

clicar na imagem para seguir o link

montes de materiais para R e S-PLUS.

S-PLUS & R Class Links

Instructor: Richard Herrington

Why Do We Care To Use the “S” Language?  Does anyone care besides us? The Association for Computing Machinery (ACM) cares

S-Plus

S-PLUS Student Edition Download (Free)

  • Student Edition 6.2 – This version of S-Plus has a 20,000 cell or 1,000 row limitation; is only for educational use; is good for only one year; and is a rather large download (100+ meg).

S-PLUS Free Experimental Libraries and User Contributed Libraries

  • Research Libraries – Includes: S+CorrelatedData (mixed effects generalized linear models), S+Best (B-Spline methods), S+Resample (bootstrap library), S+Bayes (bayesian analysis), S+FDA (functional data analysis).
  • User Contributed Libraries

Tinn-R Script Editor

R

Download Site for the Current Windows Install Binary and R Packages

Web Interfaces to R Web Servers and Example R Scripts

  • R Web Interfaces – Web/browser based interfaces to R script processing on a server
  • Example R Scripts – Some of these scripts run on a server and results are communicated thru a web browser
  • RSS Rweb Server – Link to http:/rss.acs.unt.edu R server

R, R(D)COM and Excel

Tags: , ,

Spreadsheet Addiction

clicar na imagem para seguir o link

clicar na imagem para seguir o link

Um bom e muito completo relato dos defeitos do MS Excel para análise de dados.
Some people will think that the “addiction” in the title is over the top, or at least used metaphorically. It is used literally, and is not an exaggeration.

Addiction is the persistent use of a substance where that use is detrimental to the user. It is not the substance that is the problem — more limited use may be beneficial. It is the extent and circumstances of the use that determine if the behavior is addictive or not.

Spreadsheets are a wonderful invention. They are an excellent tool for what they are good at. The problem is that they are often stretched far beyond their home territory. Dangerous abuse of spreadsheets is only too common.

I know there are many spreadsheets in financial companies that take all night to compute. These are complicated and commonly fail. When such spreadsheets are replaced by code more suited to the task, it is not unusual for the computation time to be cut to a few minutes and the process much easier to understand.

A 2012 example of spreadsheet addiction.

The technology acceptance model holds that there are two main factors that determine the uptake of a technology: the perceived usefulness and the perceived ease-of-use. Perception need not correspond to reality.

The perception of the ease-of-use of spreadsheets is to some extent an illusion. It is dead easy to get an answer from a spreadsheet, however, it is not necessarily easy to get the right answer. Thus the distorted view.

The difficulty of using alternatives to spreadsheets is overestimated by many people. Safety features can give the appearance of difficulty when in fact these are an aid.

The hard way looks easy, the easy way looks hard.

The remainder of this page is divided into the sections:

Spreadsheet Computation
The Treatment Center (Alternatives)
If You Must Persist
Specific Problems with Excel
Additional Links

Tags: , ,

Tutorial para explicar como acrescentar um segundo eixo aos gráficos do Excel

Tutorial para explicar como acrescentar um segundo eixo aos gráficos do Excel

Data Visualization – Banking Case Lab : Microsoft Excel – use Secondary Axis to Create Two Y Axes

25th May, 2014 ·

Analytics Lab

Banking Case

Using Secondary Axis to Create Two Y Axes in Excel


Tags: ,

17 short tutorials all data scientists should read

Excelentes textos fundamentais para cientistas dos dados

Excelentes textos fundamentais para cientistas dos dados

Here’s the list:

Related linkThe Data Science Toolkit

Tags: , , , ,

Excel: Esconder valores duplicados

Tip para esconder valores repetidos usando formatação condicional

Tip para esconder valores repetidos usando formatação condicional

Duplicate values aren’t wrong or bad, but they can be distracting. You probably won’t want to delete them, but you might just want to hide them. For example, the simple sheet below repeats date values in column A. In a reporting scenario, those duplicate dates might be annoying.

Tags: ,

Data Mining for Business Intelligence

Livro completo no google books com boa introdução ao data mining

Livro completo no google books com boa introdução ao data mining

Índice

Tags: , , ,

Esri Maps for Office

software da ESRI para adicionar mapas a Excel e PowerPoint

Map-Enable Your Data

Making a map of your Excel data is as easy as creating a graph or chart. Learn more.

Impact Your Audience

Add interactive maps to your PowerPoint presentations. Learn more.

Share Maps in One Click

Quickly share your maps on the web and with mobile apps. Learn more.

Tags: , ,

uso do @RISK em Português

Vídeos em português para utilização do @RISK

Vídeos em português para utilização do @RISK

Estes tutoriais em vídeo percorrerão uma análise do @RISK com você. Fora a Visão Geral, eles possuem menos de um minuto cada e vão ajudá-lo a começar a usar rapidamente o @RISK. Para começar agora mesmo, veja a “Visão Geral em 2 Minutos”.

» Visualizar todos os vídeos de Inicialização do Uso em fluxo contínuo

Tags: , ,