14.10 Expand into multiple N-column horizontally


There is a registration table, which records the entering and leaving time of customers in a certain bathroom on a certain day, and part of the data is as follows:


The task is to count the situation of customers in each one-hour time period from 18:00 to 22:00. If the customer is in the bathroom during a time period, fill in 1, otherwise leave it blank, as shown in the following figure:


Enter in cell F2:

1 =E(‘A2:D15’)
2 =create(${([“Male”,“Female”]*4).concat@c()})
3 =interval@s(time(“00:00”,“HH:mm”),time(“18:00”,“HH:mm”))/86400
4 =A1.run(A2.insert(0),4.run(t1=A3+(~-1)*3600/86400,t2=A3+~*3600/86400,k=if(A1.Sex==“M”,~*2-1,~*2),if(A1.Enter<t2 && A1.Leave>t1,A2(A1.#).field(k,1))))
5 return A2

A2: Create a table sequence using 4 groups of Male and Female as its columns
A3: Convert the start time 18:00 to the value as which Excel store
A4: Loop through each row of A1, and append a new row in A2; loop through 4 time periods, and calculate the start time t1 and end time t2 of each time period; calculate the column number k to be filled in according to the gender of the customer. If the customer is still in the bathroom during a period, then fill in 1 in the kth column of the current row of A2

esProc Desktop and Excel Processing
14.9 Expand into multiple columns horizontally
14.11 Generate permutations and combinations