Excel es una de las herramientas de análisis de datos imprescindibles en el mercado. Pero en realidad, saber cómo usar algunas de estas funciones es suficiente en la mayoría de los casos. En este artículo, compilé 10 funciones fáciles pero ampliamente utilizadas para facilitar el análisis de datos en Excel. ¡Siéntete libre de probarlos!
Tabla de contenido
Sort & Filter
LEFT/RIGHT
Conditional formatting
Remove Duplicates
TRIM
LEN
VLOOKUP
COUNTIFS
Pivot table
SUMPRODUCT
Sort & Filter
Cuando se trata de una gran cantidad de datos, las personas pueden confundirse y no saber por dónde empezar. Primero clasifcar la información te permitirá más claro. Excel le permite ordenar datos en orden ascendente descendente y alfabético, etc.
For example, I extracted some business information from Yellowpages.com. In this spreadsheet, you can see the names of the stores, addresses, phone numbers, open hours, etc. Let’s say we want to rearrange the stores according to their names. Select the first row and choose “Sort & Filter” under the “editing” group, and then you can select the order you want your data to be sorted.
Por ejemplo, extraje información comercial de Yellowpages.com. En esta hoja de cálculo, puede ver los nombres de las tiendas, las direcciones, los números de teléfono, los horarios de atención, etc. Supongamos que queremos reorganizar las tiendas según sus nombres. Seleccione la primera fila y elija "Sort & Filter" en el grupo "edición", y luego puede seleccionar el orden en que desea ordenar sus datos.
LEFT/RIGHT
Many people may not know that Excel allows you to extract the “X” number of characters from the beginning/end of cells. Staying with the Yellowpages example, to break down the phone numbers of businesses, =LEFT(Select cell, 3) can help extract the area code from phone numbers and =RIGHT(Select cell, 4) can get the last 4 digits.
Conditional formatting
Conditional formatting is useful when you want to sort out certain data that is valuable to you, especially when it comes to numbers. Let’s say I want to find out all the flatshare & houseshare in Australia that is under $200. I can first scrape the housing information from Gumtree.com, and then select “conditional formatting” under the “styles” group. There are many formatting options to choose from according to your needs. In this case, we can choose “greater than” and set up criteria for $200, and highlight them in yellow.
Remove Duplicates
It is common to have duplicated values in your spreadsheet. To remove the duplicated info, you can first highlight the duplicated data using the “conditional formatting” function (Optional, this will make sure the duplicates are obvious enough to locate so that we won’t miss any)
Then, use the “remove duplicates” function on the Data tab in the Data Tools section. Make sure you only select the column that contains duplicated values rather than the entire sheet.
TRIM
Sometimes you may find unwanted spaces in your cells, and it would be a huge waste of time to manually delete them. For instance, if the text you have in hand is full of spaces, just like the tweets I extracted from Twitter below (which is usually not the case), you can trim off the excessive part with an easy =TRIM(text) formula.
LEN
It is pretty easy to count words & characters in word documents and google doc, but what about in Excel sheets? LEN is a formula that helps you count the number of characters in cells automatically. When I was trying to determine the correlation between the lengths of YouTube video titles and their popularity, I scraped YouTube channel video information and used =LEN(text) formula to count the characters within a minute.
VLOOKUP
As one of the most frequently used functions, vlookup is popular to search for data associated with a value you enter. To explain how to use it, let’s first take a look at its formula:
=VLOOKUP(lookup_value, table_array,col_index_num, [range_lookup])
(Source)
I know what you are thinking: what is this? It may seem a little confusing at first sight, but once you get the hang of it, it is really easy.
This time, let me take the cryptocurrency market data I got from Yahoo Finance as an example. Vlookup can search for the first column and find the matching value in the second column. Let’s say we want to find the symbol of cryptocurrency matching price 0.0013. First, sort the info in ascending order. Second, select the two columns. Third, enter number “2” since we are trying to fetch data from the second column. Finally, choose FALSE to ensure an exact match of data. As you can see in the gif below, the corresponding value “ATB-USD” is returned.
COUNTIFS
This is another simple but useful Excel formula that is widely used. When it is not feasible to count the cells one by one to find out how many of them meet your criteria, you may consider taking advantage of this formula:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)
To understand it fully, imagine you are trying to figure out the number of male and female employees in each department of your company. To slice out the data with the COUNTIFS formula, you may select the data range and the criteria. The first criteria range is the departments they are in, the second is their gender. In the example shown, the formula would be
=COUNTIFS(F11: F21, F13, G11: G21, G13)
Pivot table
The pivot table gives most people headaches. We all have heard how powerful it is but only a few can use it well. A Pivot table allows you to quickly summarize and analyze large amounts of data in lists and tables by dragging and dropping columns to different rows or columns. The columns can also be re-arranged as you wish.
(Source)
You may check out this video to learn how to create and analyze data with a pivot table.
SUMPRODUCT
When you have a number of items with their quantities and prices in hand and you want to figure out their total sales, you may take advantage of this function. SUMPRODUCT multiples the quantity of each item and its price, and then adds up the sales of each item to deliver the total sales.
