9,99 €
Mehr erfahren.
- Herausgeber: ResearchFreelance
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
- Veröffentlichungsjahr: 2022
This work stems from the need to develop some Excel sheets to reach the required results.
For example, in the workplace it often happens to have data that without a precise sorting or a filter or processing based on a certain field may seem unusable.
Here are some features of the programs that process spreadsheets such as Excel from Office or Calc from OpenOffice or Calc from LibreOffice, as well as others, can be useful for achieving the result we want to obtain.
In this volume we will explain in a simple and effective way the usefulness of some important functions of the processing of spreadsheets and pivot tables that are essential for any work that focuses on data analysis.
Today it is no longer possible to ignore the use of Excel or other software for processing spreadsheets for both business and leisure purposes. In this work we will dedicate ourselves to the processing of quantitative data for work purposes, taking into consideration various examples that will explain how to solve the problems proposed through subsequent steps that will lead to the solution of the same.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Ähnliche
Olga Maria Stefania Cucaro
Indice dei contenuti
Pivot Tables for everyone
Summary
Introduction
What are Pivot Tables
What Pivot Tables can be used for
Set the data for creating the Tables
Useful Excel functions for setting data
Filters and sorts
Examples of work problems solved with functions and tables
Example No. 1 TOTAL COST FOR TWO-MONTH OCCASIONAL EMPLOYEES
Example No. 2 HOURLY AND TOTAL COST FOR OCCASIONAL EMPLOYEES
Example N. 3 SUMMARY SALARY TABLES BY COMPANY FRAMEWORK
Example No. 4 SUMMARY TABLE OF EMPLOYEE ACCESSORY CHARGES
Example N. 5 SUMMARY TABLE OF AGENT SALES
Relationships, data model and Power Pivot
Relations and data model
Power Pivot
Conclusions
Pivot Tables for everyone
Useful guide for creating Pivot Tables
Of
Olga Maria Stefania Cucaro
© Copyright 2021 by Olga Maria Stefania Cucaro - All rights reserved.
It is not permitted to reproduce, duplicate or send any part of this document electronically or in print. Copying of this document is strictly prohibited. All the programs included in this book are also copyrighted by the author / creator of the work.
The news, information, images and external tables present in this work are used for training purposes only.
Dedicated to:
To my mother who has always believed in me and to my IT professors who made my working life easier
Summary
Introduction 5
What are Pivot Tables 6
What Pivot Tables can be used for 8
Set the data for creating the Tables 9
Useful Excel functions for setting data 9
Filters and sorts 16
Examples of work problems solved with functions and tables 21
Example No. 1 TOTAL COST FOR TWO-MONTH OCCASIONAL EMPLOYEES 21
Example No. 2 HOURLY AND TOTAL COST FOR OCCASIONAL EMPLOYEES 34
Example N. 3 SUMMARY SALARY TABLES BY COMPANY FRAMEWORK 40
Example No. 4 SUMMARY TABLE OF EMPLOYEE ACCESSORY CHARGES 44
Example N. 5 SUMMARY TABLE OF AGENT SALES 53
Relationships, data model and Power Pivot 60
Relations and data model 60
Power Pivot 63
Conclusions 75
Introduction
This work stems from the need to develop some Excel sheets to reach the required results.
For example, in the workplace it often happens to have data that without a precise sorting or a filter or processing based on a certain field may seem unusable.
Here some features of the programs that process spreadsheets such as Excel from Office or Calc from OpenOffice or Calc from LibreOffice, like others, can be useful for achieving the result we want to obtain.
In this volume we will explain in a simple and effective way the usefulness of some important functions of the processing of spreadsheets and pivot tables that are essential for any work that focuses on data analysis.
Today we can no longer ignore the use of Excel or other software for processing spreadsheets for both work and leisure purposes. In this work we will dedicate ourselves to the processing of quantitative data for work purposes, taking into consideration various examples that will explain how to solve the problems proposed through subsequent steps that will lead to their solution.
What are Pivot Tables
Pivot tables are tables organized according to the choices of the operator. This type of tables processes one or more source tables that can be contained in an Excel sheet. The source table must contain the header of each column to allow the creation of a Pivot Table. To insert a Pivot Table go to Insert and click on Pivot Table as in the following figure:
In this source table we have entered for each day of the year the sales made in a company with the details of the agent and the point of sale.
In this example that we will analyze in the next chapters we have created a Pivot table that sums the commissions due to each single agent only for valid contracts. Remember to change the row label when you want to print a report with the obtained pivot tables.
As you can see from the example, you can apply filters to filter the information based on a certain variable, I can also organize the data based on the fields that are selected at the top of the list. The field is inserted in the row label, the data will be grouped in the rows otherwise if I insert the field in the column label they will be grouped in columns. In the space at the bottom left there are the values that can be modified such as sum, count, average and other types of summaries.
With the excel 2016 new features come in such as Power pivot that we will see later.
What Pivot Tables can be used for
As we have seen, pivot tables are useful in grouping and filtering large amounts of raw collected data. This type of tables are used to make sense of the data itself. Thanks to these tables we can create reports and relationships with a complex meaning useful for business purposes.
We will see in the following paragraphs some concrete examples of problems solved with pivot tables. Obviously these tables can group and filter data, but they cannot carry out any elaborations, for this type of result a programming language is required which for Excel could be VBA. The author of this book has written another book on VBA to make the knowledge of Excel simple and complete.
Before inserting a pivot table it is necessary to set the data well using for example Excel functions or doing simple multiplications to have a basis for effective grouping.
