7 SPL Desktop and Excel processing
Basic usages
Handbook
Desktop and Excel Data Processing Cases
Chapter 1 Reading and writing files and common computing
1.1 Text file
1.2 Excel file
1.3 Files and directories
1.4 General data table operations
Chapter 2 Use Excel Add-in
2.1 Installation and Configuration
2.2 Using spl() function
2.3 Editing SPL code
Chapter 3 Using the clipboard
3.1 Basic usage
3.2 Edit the script at will
3.3 Multiple result data areas
3.4 Multiple source data areas
Chapter 4 Merge Excel files
4.1 Merge by row - same name and number of columns
4.2 Merge by column - same name and number of rows
4.3 Merge by row - different name and number of columns - keep all columns
4.4 Merge by row - different name and number of columns - keep only duplicate columns
4.5 Merge by row - different name and number of columns - keep only columns of the first file
4.6 Merge by column - different name and number of rows - keep all rows
4.7 Merge by column - different name and number of rows - keep only duplicate rows
4.8 Merge by column - different name, number and order of rows - keep only rows of the first file and align the rows
4.9 Merge by row - convert file names to column values - unfixed number of files
4.10 Merge by column - convert file names to column names
4.11 Merge by column - one to many - copy data
4.12 Merge by column - one to many - leave subsequent rows empty
4.13 Merge and de-duplicate by row - duplicate whole row of data
4.14 Merge and de-duplicate by row - duplicate row headers - keep the data that firstly appear
4.15 Merge and de-duplicate by row - duplicate row headers - keep non-null data
4.16 Merge and de-duplicate by row - duplicate row headers - delete all duplicate data
4.17 Merge and de-duplicate by column - duplicate column names - keep data in columns that appear later
4.18 Merge by row and column simultaneously - keep data that firstly appear
4.19 Format conversion - merge multiple card-style files to form one row-based table
4.20 Format conversion - merge multiple primary-sub table files to form two row-based tables
4.21 Aggregate files - same rows and columns
4.22 Aggregate files - merge by row and column simultaneously - aggregate duplicate records
4.23 Aggregate files - aggregate by cell positions - unfixed number of files
4.24 Aggregate files - append and aggregate
4.25 Aggregate files - cumulate and aggregate
4.26 Aggregate files - insert aggregation sheet
Chapter 5 Split Excel file
5.1 Split by row - by number of rows
5.2 Split by row - group by data - split into multiple Sheets
5.3 Split by row - group by data - split into multiple files
5.4 Split by row - segment by data (by filtering condition)
5.5 Split by row - generate one card per row
5.6 Split by row - split multiple cards to make one card generate one file
5.7 Format conversion - split tables with primary-sub relationship into cards
5.8 Split by column - by column - take column name as file name
5.9 Split by column - by column - take column name as Sheet name
5.10 Split by column - merge duplicate rows after splitting
5.11 Split multi-Sheet file into multiple files - unfixed number of Sheets
Chapter 6 Searching, positioning and filtering
6.1 Search for the nth, the nth from last
6.2 Search for top N, last N
6.3 Filter by position
6.4 Search for position of a certain value, take the value by position
6.5 Search for row number that satisfies the condition
6.6 Search for row that satisfies the condition
6.7 Filter by multiple conditions
6.8 Search by adjacent rows
6.9 Take values of adjacent rows in same group (search & filter within adjacent intervals)
6.10 Filter by group’s aggregation value
6.11 Use group’s aggregation value when filtering
6.12 Filter by maximum or minimum value within a group (find out one for each group)
6.13 Find out interval in which a certain condition occurs continuously
Chapter 7 Calculate cell value and aggregation value
7.1 Simple column-wise aggregation
7.2 Conditional aggregation
7.3 Fill aggregation value in the first row of the same group of data
7.4 Split aggregation value and fill them in detail rows
7.5 Simple accumulation
7.6 Accumulate data in each group
7.7 Filter by Accumulation
7.8 Early-terminated accumulation
7.9 Accumulation for continuous occurrence of a certain condition
7.10 Calculate using adjacent row/interval when data of the same group is continuous (link relative ratio and YOY)
7.11 Calculate using adjacent row/interval when data of the same group is discontinuous (LRR/YOY in the case of missing data)
7.12 Merge data of the same group
7.13 String concatenation and aggregation
7.14 Calculate proportion using aggregation information of data of the same group
7.15 Generate number in each group
Chapter 8 Operation on sets and judgment of belongingness
8.1 Intersection, union and difference in the case of simple members - two sets
8.2 Intersection, union and difference in the case of simple members - multiple sets
8.3 Intersection, union and difference in the case of row-based data - two sets - by key column
8.4 Intersection, union and difference in the case of row-based data - two sets - by whole row
8.5 Intersection, union and difference in the case of row-based data - multiple sets
8.6 Judge equality of sets when order is considered
8.7 Judge belongingness of sets when order is considered
8.8 Judge equality of sets when order is ignored
8.9 Judging belongingness of sets when order is ignored
Chapter 9 Judgment, counting and deleting of duplicate data
9.1 Judge duplication of simple members
9.2 Judge duplication of row-based data - by key column
9.3 Judge duplication of row-based data - by whole row
9.4 Count number of repetitions of simple members
9.5 Count number of repetitions of row-based data - by key column
9.6 Count number of repetitions of row-based data - by whole row
9.7 Deduplication of simple data
9.8 Deduplication of row-based data - by key column
9.9 Deduplication of row-based data - by whole row
9.10 Deduplication of simple data - keeping order
9.11 Deduplication of row-based data - by key column - keeping order
9.12 Deduplication of row-based data - by whole row - keeping order
9.13 Filter by number of repetitions
9.14 Delete data that can be paired
Chapter 10 Ranking and Sorting
10.1 Sorting of simple members
10.2 Sorting of row-based data
10.3 Sorting of row-based data - by combination of multiple columns
10.4 Sorting of row-based data - by expression
10.5 Sort in group
10.6 Sort by specified order
10.7 Sort by specified order in which duplicate values exist
10.8 Shuffle the data
10.9 Ranking of simple members
10.10 Ranking of row-based data
10.11 Ranking of row-based data - by combination of multiple columns
10.12 Ranking of row-based data - by expression
10.13 Concatenate members with the same ranking
10.14 Rank in group
Chapter 11 Grouping and aggregating
11.1 Simple grouping
11.2 Group by combination of multiple columns
11.3 Group by expression
11.4 Group by segment
11.5 Enumeration grouping
11.6 Put every N members in a group
11.7 Convert one-dimensional array to two-dimensional array
11.8 Take adjacent data as grouping criteria
11.9 Group when meeting blank row
11.10 Group when meeting non-null value
11.11 Group by interval of data values
11.12 Concatenate data within group into text
11.13 Auto-aggregating in the case of multiple columns - unfixed number
Chapter 12 Association and comparison
12.1 Use formulas to handle association
12.2 Single column association
12.3 Multiple columns association
12.4 Reference multi-column data from association table
12.5 Use formulas to handle interval association
12.6 Use association table to handle interval association
12.7 Use a two-dimensional association table
12.8 Use interval range to perform retroactive searching of association table
12.9 Associate multiple rows of data
12.10 Associate with detail table
12.11 Find changes through comparison
12.12 Dynamic association operation
Chapter 13 Conversion between rows and columns
13.1 Row-to-column conversion for fixed columns
13.2 Convert row-based table to crosstab
13.3 Convert crosstab to row-based table
13.4 Interconversion of upper layer groups for rows and columns - column-to-row
13.5 Interconversion of upper layer groups for rows and columns - row-to-column
13.6 Put data in a group horizontally into columns
13.7 Re-group or sort when filling grouped data into columns
13.8 Convert certain columns of the same row, as group members, to multiple rows
13.9 Convert group formed by every N columns to multiple rows
13.10 Convert groups to columns after grouping
13.11 Rearrange multiple columns into a cross-tab
13.12 Interconversion of rows and columns within a group
13.13 Interconversion of rows and columns in reverse order
Chapter 14 Expand and complement
14.1 Generate continuous array
14.2 Generate continuous array - concatenate results into a string
14.3 Expand one row into multiple rows based on value
14.4 Expand one row into multiple rows after splitting text
14.5 Make up missing parts to make data continuous
14.6 Add several blank rows every N rows
14.7 Insert row after specific row
14.8 Insert blank row when meeting with data change
14.9 Expand into multiple columns horizontally
14.10 Expand into multiple N-column horizontally
14.11 Generate permutations and combinations
Chapter 15 Operations on text
15.1 Split string - separate by comma – automatic parsing of data type
15.2 Split string - separate by carriage return (CR) - automatic parsing of data type
15.3 Split string - separate by multi-character separator
15.4 Concatenate into string
15.5 Parse and extract numbers
15.6 Parse and extract dates
15.7 Take out different types of characters
15.8 Take out words
15.9 Parse and extract Key-Value pair
Chapter 16 Operations on date and time
16.1 Count date by year and month
16.2 Calculate time repeat interval
16.3 Generate a time sequence with the same time interval – one day
16.4 Generate a time sequence with the same time interval – two days
16.5 Generate a time sequence with the same time interval – two hours
16.6 Generate a time sequence with the same time interval – one month
16.7 Generate a time sequence with the same time interval - Sunday
16.8 The first Friday of a certain month/quarter/year
