Data Analysis using Google Sheets (Vlookup,Hlookup,Pivot)
Hi readers of the medium. I haven’t been back in a long time. I’m having some difficulty understanding parametric and non-parametric for regression testing and decided to deepen my knowledge first. If you guys have any knowledge about it, please let me know. Thank you.
This time we will discuss some of the formulas that I use most often in my previous work using Google Sheets or Spread Sheets.
VLOOKUP
First of all we need to open gsheet :
After entering the required data, here are the fields that we must input with the Vlookup formula:
Cell B3 = Cell that we will find the value for
Range $H$2:$I$6 = Range where the table contains information about the value we are looking for
2 = the location of the column value that we are looking for for column B
After the “Ketua” column is filled in, it is necessary to use the IF AND formula to fill in the “Pencapaian” column, where the criteria are PASS : “Nilai” (column D) > 60 and “Kehadiran” (Column E) >10.
USING COLUMN() FORMULA FOR VLOOKUP
Not infrequently we are faced with the problem of changing the column index in the vlookup formula so that we can drag the formula to the side. COLUMN() is the solution to this problem.
Here is the range of data for Organization :
Like the previous Vlookup formula, with the data range for the organization, it can be seen that the previous Vlookup formula used used index number 2. In order to make it easier for us to pull the formula aside, we can go to COLUMN() for index 2, where here the column mentioned is column C which has a value of 3, so to get the number 2, we can subtract the COLUMN() index. After being pulled aside, we will get the vlookup result values for the “Wakil Ketua” column and so on Hlookup.
The formula used in Hlookup is almost the same as Vlookup. It’s just that hlookup reads data in rows, while for Vlookup reads data in columns.
Here’s a simple example of using the Hlookup formula:
PIVOT
The data used here is data that has been downloaded from https://www.kaggle.com/ . Here we will learn how to use pivot with insert-pivot tables in menu.
Block the cells to be pivoted and click insert-pivot table. After that, we can choose new sheet to put the pivot result on a new sheet in gsheet or existing sheet to put the pivot result on an existing sheet.
Here is the initial view after creating the pivot table:
We will create a pivot table from the ID variable to find the values for fixed acidity, alcohol and total sulfur dioxide. We need information about
a. The maximum value of fixed acidity, the maximum value of alcohol, and the percentage of the total sulfur dioxide value.
First we enter the variable Id in rows, then:
Entering variables from the values you want to appear into values. To find the information needed at point a, we can adjust it for each of the variables in the summarize by and show as options.
b. The maximum value of fixed acidity, the maximum value of alcohol, and the percentage of the total sulfur dioxide value for the quality value is 5.
In addition to the table above, we can also add certain conditions in the pivot. As in point b, which only displays data with quality 5 in the following way:
Enter quality in filters, then click on showing all items.
After that we can choose the quality value that we want to display, then the result will be like this:
This means that only Id : 0.1, 2, 4, 5, 6, 10 which has a quality value of 5.c. The maximum value of fixed acidity, the maximum value of alcohol, and the percentage of the total sulfur dioxide value for pH values greater than 3.5.
Just like the previous car we can choose filters but use a variable pH.
After that we can choose filter conditions as follows:
This condition can be changed as needed. The data displayed is data that has a pH value of more than 3.5, namely Id 0.4 and 5.
“Successful people don’t fear failure but understand that it’s necessary to learn and grow from.” -Robert Kiyosaki
