Utilizar y dominar tablas dinámicas tanto en excel como en google sheets es una ventaja fenomenal para realizar tus tareas de manera rápida y dinámica.
A continuación te explico como puede aprovechar el uso de tablas dinámicas con Spreadsheets.
Parte uno: Análisis - crear tabla dinámica
Tienes que estar dentro de cualquiera de tus celdas con información.
Dirígete a Insertar, luego clic en Tabla dinámicas, marca la opción de Nueva Hoja y clic en Crear.
Automáticamente se crea un nueva hoja, en la cual encuentras la interface para editar la tabla dinámica, en la parte derecha observamos una ventana "Editor de tablas dinámicas" donde podemos elegir los encabezados y realizar nuestra consultas
Por ejemplo pordemos arrastrar el ingreso a valores y los productos a filas para ver, los ingresos totales por producto.
También podemos arrastrar un campo a columnas, por ejemplo Continente lo coloco en columnas y podemos ver los ingresos totales por producto en relación al continente.
Otra consulta que podemos realizar, o mejor dicho queremos ver las cantidades de mayor a menor, para ello estando en la columna que queremos ordenar, nos dirigimos al editor de tablas dinámicas, seleccionamos el campo que queremos ordenar, en este caso es Producto y, en orden seleccionamos descendente y en ordenar por elegimos SUM de ingresos en suma total.
Con eso podemos visualizar e identificar de forma rápida el producto que mas ingresos tiene y también el más bajo.
Parte dos: Visualización
Una parte importante para interpretar nuestros datos con tablas dinámicas es utilizar las visualizaciones que nos dispone google sheets.
Segmentación de datos
Para aplicar segmentación de datos podemos ir a Datos luego damos clic en Añadir un control de filtros.
Nos pide seleccionar un campo para darle el filtro de segmentación de datos, para ello en mi caso elijo Continente.
Crear grupos de fechas
Arrastramos el campo fecha a la parte de filas de nuestra tabla dinámica, luego damos clic derecho Crear grupo de fechas de tabla dinámica y seleccionamos por ejemplo Trimetre.
Gráficos
Ahora vamos a insertar gráficos dinámicos
Nos vamos a Insertar luego clic en Gráfico
Luego se presenta un grafico en el cual podemos editar su configuración y personalizar el gráfico
Parte tres: Advanced
Creación de campo calculado
Supongamos que queremos calcular las ganancias, sabemos que nuetra data no tiene dicho campo, pero por medio de la tabla dinámica pordemos realizarlo, para eso hacemos clic en Añadir junto a Valores y seleccionamos Campo calculado
En campo calculado 1, ingresamos la función que queremos calcular en este caso en Ingresos - Gastos para obtener la ganancia
Visualización de un dato específico de una celda
Queremos ver los datos respecto a un valor específico de una celda, para ello estando en dicha celda, damos doble clic ó también podemos dar clic derecho y seleccionamos mostrar datos
Con esto, nos presenta una nueva hoja con un nombre automático "Datos1-Piña-Asia" en la cual podemos observar los datos implícitos de dicha celda
Conclusiones
Trabajar con tabla dinámicas te proporciona una ventaja y te ahorra tiempo para intepretar los datos de tus tablas.
In
this blog we'll cover everything you need to know about pivot tables.
First,
we will cover the basics of analysing data using pivot tables, then we go to
the visualization of pivot tables, with slicers, timelines, bar charts, and
finally we will see how we can manipulate advanced pivot tables and do things
like grouping, calculate new fields and more, then get to work.
DATA ANALYSIS WITH PIVOT TABLES
Here we have an excel spreadsheet, which is available in the following link.
As we can see, we are with a data of sales of
natural products, in itself it is about products that are exported.
What we want to know is the total sales or
revenue, which product or products had the best sales
* What
is out total revenue? * What products sell best?
And many other things, which will help us make
better business decisions.
This is typically one of the best practices,
from here we are going to enter a pivot table.
Insert PivotTable
We are going to Insert and then only select
PivotTable with one click.
Here it shows us a window in which it tells us that
we can insert the pivot table in a new spreadsheet or in the existing one, we
select in a new spreadsheet and press OK.
Being here we find the fields of our pivot
table, in itself we head them of our table is shown here, below these is where
we can do our analysis.
If you want to see from different points of
view, you can click on this icon and change it to another view as an example
the vertical or if you want you can return to the default view, similarly you
can also drag the sale and place where you want.
Add variables to values and rows
For example, if we want to see the income of
each product, we simply go to the header, select the income and drag them to
this sum of values field, as you can see it presents us with the sum of sales
of all products, but we want to break them down by product, so we will put it
in the row field, as you can visualize,
It will begin to autocomplete in this area.
Now suppose we want to change the format of
these values.
We can right-click, select number format, then
simply go to number, here we can use the comma separator, we can also reduce
decimals since you want to see only whole numbers, press Accept.
Now if we can visualize the numbers of a business way, we can see that they are in total of my products around 182.
Add variables to columns
Similar to rows, we have the field of columns
where we can drag data, for example, the continent field, and if we want to
delete any, simply select and drag out.
Order values from highest to lowest
Now, how to do, if we want this table to be
ordered from highest to lowest? Well, for this it is as simple as clicking
right, we are going to order, and select from highest to lowest, which
facilitates the visualization and we can quickly identify which product is the
one that presents us with the most income.
View values as proportions
Similarly, if we want the proportions for the
percentage of the total we can also right click,
Can we get the proportions?
I have to show values like, here we have enough
options for us, we select the one that suits us, in my case the percentage of
the total, this could serve us for a pie chart.
Which product are basically the best average sale ?
What if we want to know which products are sold
at the highest price,
Can we find the average sale price?
Which is basically the average sale?
For this, we no longer need the income, we
eliminate it and place instead, the price per kilogram in the Values area, as
we observe we are getting the sum, which is not what I am looking for, so we
can right click and select summarize values as, we also see several options, we
choose average, we can clearly see the best average price.
VISUALS PIVOT TABLES
Now, in the second part, which are the graphs
or visualizations, suppose we want to see the sales of the product by date.
For this, we go back to our pivot table and
remove the averages we had, and place the income, in addition to that we want
to see the dates as we mentioned, we can take the date field and place it in
columns, but as you can see, it is not the best way to interpret.
Filters
So we are
going to remove the date and what we can do here is drag the date field and
place it in filters, now this will allow you to filter specifically by a
specific date, for example, I select a date then press ok.
Timelines
But if we are not convinced, we will leave that
filter configuration, and instead, what we will do is go to PivotTable Analysis
and insert a timeline, select the date field, and press ok. Now we just
accommodate our window.
With this we can select specific times that we
want, such as a specific month, a range or something similar, here instead of
months we can configure quarters and select which quarter to display.
Silcers of data
For
these filtering topics we can also use another great tool such as segmentation. For
example, suppose we want to add another variable that will be the continent,
for this we could add it in the column area or instead we could go to Analyze
PivotTable and select Segmentation (slicer).
In this example, it will be for continent, so we select and give ok.
And this is basically another way to filter our data, I can select one, or several with this icon and also select all by pressing this icon.
Graphics
As the final part of the visualizations are the
graphics, so
first let's remove both filters by pressing delete. Again, within analyse pivot table, I click on
pivot chart, select the default chart, and press OK.
The
good thing about these graphs is that they are dynamic based on what we add or
remove from our pivot table, for example, we add the continent in columns, and
we can see how it is updated automatically, within the graph we can also filter
for example I remove a continent and press OK, and we see how everything is
updated.
ADVANCED PIVOT TABLES
Now let's continue with step 3 which are
advanced pivot table features.
Create new variable - profit
Here we can see the clean pivot table, and we
want to know the profit by product, but we notice that in our table data we do
not have the profit variable, so we have to create this variable, and we can
create it using calculated fields, for them we will only go to analyse pivot
table, we click on this icon, Click on calculated field.
In this window within name, we write profit and
in formula, simply our income minus expenses, for them we select income and by
double clicking we place it inside formulas, as you can see, minus costs,
likewise double click, we insert it. Finally, we click on Accept.
Now we can have the sum of profits per product.
Visualize a especific data from our pivot table
If we want to visualize a specific data from
our data table, for example, we can double click on that data, and we will be
presented with all the data in a new pivot table based on that selected field.
We return to our pivot table.
Grouping option from pivot table
Now we want to find out the earnings by dates, then I simply delete products and
instead add date, and instead of displaying by month we want to see by quarter,
fortunately we can use the grouping option, for this we right click, select
group.
Here we have quite a few grouping options, for
example, we select months and quarters, here we can always collapse and expand,
by right-clicking and selecting expand or collapse.
Add a new data from our data table
Finally let's see what happens if we add more
data in our initial list, for this we go to the control page, we can go to the
end by typing Crtl + down arrow, here I can copy a row and paste it to add a
new sample product Melon.
So we go to the pivot table, remove the date fields and add products to rows and observe that it has not been added.
This is why we have not updated our pivot table, for this we are going to analyze pivot table and click refresh and now if we can see the new product that we have added to our data table, So it's important to update that way every time we add or remove data from our table.
What we have learned is the traditional way of
working with pivot tables, but there is one more tool that many still do not
know.
ANALYZE DATA
Let's go back to our data table and we do not
go to the beginning, within table design we can see in the right corner, there
is a data analysis button, there we simply click on it, basically interprets
our data using artificial intelligence to give us several options as we can
visualize.
CONCLUTION
The pivot table is quite a powerful tool so
feel free to use it as needed.
If you liked it do not forget to give
"like", subscribe and activate the campaign, see you in my next blog.
In the followign video you can see an example of how to manage pivot table
