Data Manipulation
Vinayak Jadhav
?? Certified Microsoft Power BI Data Analyst Associate?? Certified Microsoft Office Specialist? Non IT Professional Driving Digital Transformation ?? Generate your several Income sources
Data analysis is a challenging task, especially if you don’t have the data manipulation skills. In this article, we will discuss some of the most common data manipulation techniques that can be used in Excel for data analysis.
The power of these techniques will be demonstrated by using some real-life examples. The 9 common data manipulations techniques discussed are:
1) FILTER
2) SORT
3) SORTBY
4) UNIQUE
5) SEQUENCE
6) VLOOKUP
7) XLOOKUP
Each of these techniques will provide you with a better understanding of your data and how it works – from getting your head around different types of visualization to exploring outliers. These simple tricks will not only improve your efficiency, but also make it easier for people who don’t know Excel as well to understand what you’re doing.
1) FILTER
Syntax
=FILTER(array,include,[if_empty])
=FILTER(A1:B6, C1:C6>100) Gets a subset of the cell range in the first input that meets the condition in the second input.
2) SORT
Syntax
=SORT(array,[sort_index],[sort_order],[by_col])
·???????? array?- Range or array to sort.
·???????? sort_index?- [optional] Column index to use for sorting. Default is 1.
·???????? sort_order?- [optional] 1 = Ascending, -1 = Descending. Default is ascending order.
·???????? by_col?- [optional] TRUE = sort by column. FALSE = sort by row. Default is FALSE.
=SORT(A1:E6, 4) Returns the dataset with rows in alphabetical order of the fourth column. Sorts the rows of the data according to values in specified columns.
3) SORTBY
Syntax
=SORTBY(array,by_array,[sort_order],[array/order],...)
·???????? array?- Range or array to sort.
·???????? by_array?- Range or array to sort by.
·???????? sort_order?- [optional] Sort order. 1 = ascending (default), -1 = descending.
·???????? array/order?- [optional] Additional array and sort order pairs.
=SORTBY(A1:E6, D1:D6) Returns the same as the SORT() example. Alternate, more flexible, syntax for sorting. Rather than specifying the column number, you specify an array to sort by.
4) UNIQUE
领英推荐
Syntax
=UNIQUE(array,[by_col],[exactly_once])
·???????? array?- Range or array from which to extract unique values.
·???????? by_col?- [optional] How to compare and extract. By row = FALSE (default); by column = TRUE.
·???????? exactly_once?- [optional] TRUE = values that occur once, FALSE= all unique values (default).
=UNIQUE(A1:A6) Gets a list of unique values from the specified data.
5) SEQUENCE
Syntax
=SEQUENCE(rows,[columns],[start],[step])
·???????? rows?- Number of rows to return.
·???????? columns?- [optional] Number of columns to return.
·???????? start?- [optional] Starting value (defaults to 1).
·???????? step?- [optional] Increment between each value (defaults to 1).
=SEQUENCE(5, 1, 3, 2) Returns 5 rows and 1 column containing the values 3, 5, 7, 9, 11. Generates a sequence of numbers, starting at the specified start value and with the specified step size.
6) VLOOKUP
Syntax
=VLOOKUP(I4,B5:F9,3,FALSE)
VLOOKUP Function : The VLOOKUP function is a premade function in Excel, which allows searches across columns.
It is typed =VLOOKUP and has the following parts:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
7) XLOOKUP - XLOOKUP Function : The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
Syntax
=XLOOKUP(lookup,lookup_array,return_array,[not_found],[match_mode],[search_mode])
·???????? lookup?- The lookup value.
·???????? lookup_array?- The array or range to search.
·???????? return_array?- The array or range to return.
·???????? not_found?- [optional] Value to return if no match found.
·???????? match_mode?- [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
·???????? search_mode?- [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.
?
?
?
AI & Tech Content Creator ????|| AI Enthusiast ?? || Sharing Latest AI Tools ?|| Helping Client's to Grow their Business ?? || DM for Promotion ??|| SDLC || STLC || Software Test Engineer specializing ||
7 个月Data analysis is indeed a complex task, and having strong data manipulation skills is crucial for success. Your newsletter on "Mastering Data Analytics" is a valuable resource for professionals looking to enhance their abilities in this area. Keep up the great work, Vinayak Jadhav!
OK Bo?tjan Dolin?ek