楼主
liuguansky |
Q：如何根据时间段（上午，下午，晚上）和日期统计血压和脉搏？
效果见下图
以下各解法L列公式在前
解法1：- =TEXT(INDEX(A:A,SMALL(IF($B$4:$J$9<>"",ROW($B$4:$J$9)),(ROW(A1)-1)*3+1)),"m-d")&" "&INDEX($2:$2,,RIGHT(SMALL(IF($B$4:$J$9<>"",ROW($B$4:$J$9)*100+COLUMN($B$4:$J$9)),(ROW(B1)-1)*3+1))*1)
- =IF($L15="","",VLOOKUP(DATEVALUE("2010-"&LEFT($L15,LEN($L15)-3)),$A$4:$J$115,MATCH(RIGHT($L15,2),$A$2:$J$2,)+COLUMN(A1)-1,))
解法2：- =IF(ROW(A1)>COUNT($B$4:$J$9)/3,"",TEXT(INDEX(A:A,SMALL(IF($B$4:$J$9<>"",ROW($B$4:$J$9)),(ROW(A1)-1)*3+1)),"m-d")&" "&INDEX($2:$2,,RIGHT(SMALL(IF($B$4:$J$9<>"",ROW($B$4:$J$9)*100+COLUMN($B$4:$J$9)),(ROW(B1)-1)*3+1))*1))
- =IF($L25="","",VLOOKUP(DATEVALUE("2010-"&LEFT($L25,LEN($L25)-3)),$A$4:$J$115,MATCH(RIGHT($L25,2),$A$2:$J$2,)+COLUMN(A9)-1,))
解法3.1：- =LOOKUP(ROW(A2)-1,MMULT({0,0,0,0,0,0;1,0,0,0,0,0;1,1,0,0,0,0;1,1,1,0,0,0;1,1,1,1,0,0;1,1,1,1,1,0;1,1,1,1,1,1},SUBTOTAL(3,OFFSET($B$3:$J$3,ROW($1:$6),))/3),TEXT($A$4:$A$9,"m-dd"))&" "&INDEX({"上午","下午","晚上"},--LEFT(SMALL(IF(SUBTOTAL(9,OFFSET($B$3:$D$3,ROW($1:$6),{0,1,2}*3))>0,{1,2,3}*10^{0;1;2;3;4;5},9^9),ROW(A2)),1))
- =IF($L35="","",VLOOKUP(DATEVALUE("2010-"&LEFT($L35,LEN($L35)-3)),$A$4:$J$115,MATCH(RIGHT($L35,2),$A$2:$J$2,)+COLUMN(A9)-1,))
解法3.2- =TEXT(INDEX(A:A,MIN(IF(ROW(A1)-1<SUBTOTAL(3,OFFSET($B$4:$J$9,,,ROW($1:$6)))/3,ROW($4:$9)))),"m-dd")&" "&INDEX({"上午","下午","晚上"},--LEFT(SMALL(IF(SUBTOTAL(9,OFFSET($B$3:$D$3,ROW($1:$6),{0,1,2}*3))>0,{1,2,3}*10^{0;1;2;3;4;5},9^9),ROW(A1)),1))
- =IF($L44="","",VLOOKUP(DATEVALUE("2010-"&LEFT($L44,LEN($L44)-3)),$A$4:$J$115,MATCH(RIGHT($L44,2),$A$2:$J$2,)+COLUMN(A18)-1,))
|