1 Calculation Examples for Excel ★
0.Basic usages
1.Extract and Fill in
1.7 Extract data from complex row-style text
1.8 Extract data from uncertain rows
1.9 Extract specified cell data
1.10 Extract repeating cell areas
1.11 Extract mixed-style structure
1.12 Extract data in the column direction
1.14 Extract data from XML and json
1.15 Fill in the specified cell
1.17 Fill in column data horizontally
2.Combine and Split
2.18 Combine tables with same columns
2.19 Combine columns and deal with duplicate data
2.20 Combine tables with summarizing duplicate columns
2.21 Combine tables with different columns
2.22 Remove duplicates with combining rows and columns
2.23 Summarize duplicates with combining rows and columns
2.24 Fixed format table alignment summary
2.25 Append and accumulate to a summary table
2.26 Split by category or number of rows
3.Find and Filter
3.27 Find the location of a value
3.28 Filter data by multi-condition
3.31 Find the maximum and minimum
3.32 Choose the top N and last N
3.33 Find and filter by adjacent values
3.34 Find and filter in adjacent intervals
3.35 Find intervals by continuous data
3.36 Filter out categories based on summary value conditions
3.37 Choose one for each category
3.38 Filter with maximum and minimum in a category
3.39 Find adjacent rows in the same category
3.40 Filter by category summary value
4.Calculate cell and Summarize value
4.41 Calculate columns by some columns
4.42 Calculate data by adjacent rows and intervals
4.43 Calculate cumulative value with possible early termination
4.44 Calculate adjacent rows with continuous same data
4.45 Calculate adjacent rows with not continuous same data
4.46 Summarize data with same columns
4.47 Calculate cumulative value in category
4.48 Add a summary column based on columns
4.49 Generate numbers for the same group of data
4.50 Split subtotal values into detail rows
4.51 Fill in the cell value by the position in the category
4.52 Blank the consecutive same data except the first row
4.53 Add a summary row on top of each group
4.54 Summarize data by column direction
5.Set operation and Containing judgment
5.55 Intersection,Union,Difference of simple member
5.56 Intersection,Union,Difference of row-style data
5.57 Intersection,Union,Difference between uncertain numbers of sets
5.58 Set equality and containing judgments
5.59 Set equality and containing judgment with regardless of order
6.Find duplicates, counting and removal
6.60 Find duplicates and counting
6.61 Counting repetitions
6.62 Counting repetitions for uncertain columns
6.63 Deduplicating for row-style Excel
6.64 Deduplicating for simple data
6.65 Deduplicating without changing original orders
6.67 deduplicating with related columns
7.Sorting and ranking
7.69 Aligning in specified order
7.70 Aligning with repeated values in the specified order
7.71 Concatenate members with the same rank
7.72 Sorting in the same category
7.73 Ranking in category
7.74 Shuffle orders
8.Special grouping and aggregation methods
8.75 Every N members as a group
8.76 Grouping with adjacent data
8.77 Grouping by empty or non-empty rows
8.78 Grouping by data interval
8.79 Concatenate the same group of data into text
8.80 Aggregating with uncertain columns
9.Association and comparison
9.81 Handle association by formulas
9.82 Single column associating another table
9.83 Multi-column associating another table
9.84 Single column associating multiple columns of another table
9.85 Interval association in the formula
9.86 Use association table for interval association
9.87 Use a two-dimensional association table
9.88 Use the interval range to look up the association table
9.89 Associate multiple rows of data
9.90 Associate with detail table
10.Convert rows and columns
10.92 Row to fixed column
10.93 Convert row-style table and cross table
10.94 Convert row-column upper classification
10.95 Put data in category horizontally into columns
10.96 Re-categorize or sort the categorized data when it is put into columns
10.97 Convert certain columns of the same row into multiple rows
10.98 Convert every N columns into multiple rows
10.99 Convert categories into columns
10.101 Convert rows and columns in a category
10.102 Reverse order of columns after transposing
11.Expansion and Complement
11.103 Generate continuous intervals
11.104 Convert one row into multiple rows based on the value
11.105 Split a text into multiple rows
11.106 Complement missing parts in continuous values
11.107 Complement a number of blank lines every N rows
11.108 Insert a row after a specific row
11.109 Insert a blank row with data changing
11.110 Expand multiple columns horizontally
11.111 Expand multiple N columns horizontally
12.Processing text
12.113 Split a text into multiple
12.114 Get multiple texts after splitting and concatenating
12.115 Take out numbers from texts
12.116 Take out date from texts
12.117 Split different types of characters from texts
12.119 Parse key-values from texts
13.Calculate date and time
13.120 Summarize date by year and month
13.121 Calculate time repeat interval
13.122 Generate all dates between two dates
13.123 Generate consecutive time points with the same interval
13.124 Generate consecutive Sundays
13.125 The first Friday of a month/quarter/year
14.Math operations and others
14.126 Dynamic association operation
14.127 Generate permutations and combinations
14.128 Calculating expression
