esProc Desktop and Excel Processing
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.4 General data table operations
Chapter 2 Use Excel Add-in
2.1 Installation and Configuration
Chapter 3 Using the clipboard
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.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.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.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.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.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.6 Accumulate data in each group
7.8 Early-terminated accumulation
7.9 Accumulation for continuous occurrence of a certain condition
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.7 Sort by specified order in which duplicate values exist
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
Chapter 11 Grouping and aggregating
11.2 Group by combination of multiple columns
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.5 Parse and extract numbers
15.7 Take out different types of characters
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
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
Chinese version