Problem description & analysis:
In the following table, value of cell A1 is made up of names of several people and their attendances in four days. For example, c is 1100 expressed in hexadecimal notation, meaning the corresponding person has attendance in the 1st day and the 2nd day and is absent in the 3rd day and the 4th day.
We need to find the number of people who has the attendance in the day input in A2. For example, three people are present in the 1st day and two people are present in the 3rd day.
Solution:
Use SPL XLL to type in the following formula:
=spl("=theDay=shift(1,?2-4),?1.split@c().step(2,2).count(and(bits@h(~),theDay)>0)",A1,A2)
As shown in the picture below:
Explanation:
shift()function performs a shift operation on an integer. step(2,2) gets members at the even positions. bits@h parses a hexadecimal number. The and operator represents and "AND" operation with bits() function, and ~ represents the current member.
Top comments (1)
esProc Desktop is now FREE to download and try the above solution by yourself: scudata.com/download-Desktop
💫Plus there's some information you may be intertested in:
Plugin Installation Method: c.scudata.com/article/1652061135502
References to other rich Excel operation cases:c.raqsoft.com/article/1651916536524
SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...