Preface

With the advantages of easy-to-use, rich data processing functions, strong chart drawing capability, fast and accurate calculation, etc., Excel has become an indispensable utility software for data processing in daily office work, and very popular among office workers.
Nevertheless, there are still quite a few complex tasks that are difficult to handle with Excel, such as the processing of ordered set, the conversion of data structure, the alternate performing of grouping, filtering and aggregating, the association and comparison between tables, and the merging and splitting of multiple files. Consequently, these complex tasks often make the office workers so head-scratching that they have to work overtime to cope. Although Excel comes with the VBA language, it provides most employees with little substantial help for the reason that VBA language is insufficient in set orientation, relatively high difficulty to learn and high complexity in use.
SPL, as an open-source programming language, provides Excel with add-in and clipboard, and enhances the functions like programming, which has the following characteristics: i)SPL is easy to learn, seamlessly integrated with Excel, and can be used as one function of Excel; ii) SPL provides a wealth of ordered operations, allowing you to easily achieve the positioning and search; iii)SPL provides rich structured data processing functions, which allow you to perform not only various complex calculations but many conversions on data structure; iv) SPL naturally supports the grouped intermediate results, and is able to filter and aggregate the grouped results, thereby allowing you no longer worry about the complex situation that grouping, filtering and aggregating occur alternatively; v) Association and comparison between tables are the forte of SPL, which can be implemented easily, and done efficiently. In terms of other minor functions, SPL has also made a lot of supplements to Excel, such as the data generation with special requirements.
With the help of SPL, the office workers can easily solve the deficiencies of Excel in the functions mentioned above, thereby greatly improving their daily work efficiency.
To learn SPL, please visit: SPL Programming - Preface , where you will find it easy to learn as long as you have a high school education background.

This book collects a large number of Excel problems that may be encountered in your daily work from the Internet, and the number of such problems is up to several hundreds, with a wide coverage. All you have to do is to search for the chapters that interest you and find the appropriate cases and formulas, and you will be able to apply them to your own work after minor modifications.
In short, SPL can help you avoid working overtime on Excel data processing, and make your dream of finishing work in advance come true!

Table of contents

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