esProc Desktopesproc-desktop.hashnode.dev·Nov 6, 2024#90 — Generate Numbers in Each GroupProblem description & analysis: Here below is the student data table: Task: Now we want to create student IDs for every student. The student ID should be class number + student’s serial number in the class, and the serial number should be two-digit ...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 4, 2024#88 — String Concatenation And AggregationProblem description & analysis: There is a data table: Task: Now we want to concatenate the ID numbers for each group of names by | to form a string, and put the result in column C (to calculate in cell C1). Solution: Use SPL XLL and enter the follo...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 1, 2024#87 — Merge Data of The Same GroupProblem description & analysis: Here below is an annual and quarterly sales data table: Task: In this table, column A is ordered and has duplicate values. Now we want to clear the data from all rows in the same group except the first row. Solution: ...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Oct 31, 2024#86 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Discontinuous (LRR/YOY in The Case of Missing Data)Problem description & analysis: Here below is an annual and quarterly sales data table: Task: In this table, the data for the first quarter of 2020 is missing. When the data of this quarter is used to calculate LRR, skip this quarter directly, and u...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Oct 30, 2024#85 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Continuous (Link Relative Ratio And YOY)Problem description & analysis: Here below is an annual and quarterly sales data table: Task: Now we want to calculate LRR and YOY (to calculate in cell D1). Solution: Use SPL XLL and the code is as follows: A 1\=E(‘A1:C21’) 2\=A1.(Sales-Sal...Discuss·10 likes#Excel-tutorial
esProc Desktopesproc-desktop.hashnode.dev·Oct 29, 2024#84 — Accumulation for Continuous Occurrence of A Certain ConditionProblem description & analysis: There is a statistical table for daily sales: Task: We want to add a column to the right to calculate the cumulative days of consecutive rising (to calculate in cell C1). Solution: Use SPL XLL and the code is as follo...Discussexcel
esProc Desktopesproc-desktop.hashnode.dev·Oct 28, 2024#83 - Early-Terminated AccumulationProblem description & analysis: Here below is an inventory data table: Task: We know that the quantity of this product sold today is 50, and want to calculate the new inventory data (subtract the inventory quantity in turn according to the order in ...Discuss·10 likes#Excel-tutorial
esProc Desktopesproc-desktop.hashnode.dev·Oct 25, 2024#82 — Filter by AccumulationProblem description & analysis: There is a sales data table: Task: Count the date when the sales volume of each person reaches 50,000. Solution: Use SPL XLL and the code is as follows: =spl("=E(?1).group(Name).new(Name,~.select@1(Sales[:0].sum()>500...Discussexcel
esProc Desktopesproc-desktop.hashnode.dev·Oct 23, 2024#80 — Simple AccumulationProblem description & analysis: There is a sales data table: Task: We want to calculate the cumulative sales volume and fill them in column C. Solution: Use SPL XLL and calculate in cell C2 first: =spl("=?1+?2",B2,C1) Then drag C2 down to every rel...Discuss#Excel-tutorial
esProc Desktopesproc-desktop.hashnode.dev·Oct 16, 2024#75 - Find out The Intervals in Which A Certain Condition Occurs ContinuouslyProblem description & analysis: We have a statistical table for daily sales: Task: Find out the date when the sales rises for three consecutive days or more. Solution: Use SPL XLL and enter the following code: A 1 =0 2 =E(‘A1:B32’).group@o(if(Sales...Discussexcel
