英文:
Recreate a basic table pivot (count of 2 data columns) via formula
问题
我有我的数据在AI和AJ列,如截图所示。
我想要自动化一些流程,因此我希望最终的结果与AM和AN的类型相符(在应用数据透视表后)。
我尝试了很多次,但都失败了。
请帮忙解决我的疑惑。
英文:
I have my data in the column AI and AJ as attached in the screenshot.
I want to automate some process due to which, I want my final outcome as per type AM and AN (after applying pivot).
I tried but failed in many attempts.
Please help and solve my doubt.
答案1
得分: 1
In a new sheet, paste this:
部门 | 标签 | 唯一部门索引 | 唯一部门-标签匹配的索引 | 唯一部门-标签匹配的索引(部门部分) | 唯一部门-标签匹配的索引(标签部分) | 唯一部门的内部行 | 唯一部门-标签匹配的内部行 | 部门(已排序) | 标签(已排序) | 部门-标签计数 | 段落计数 | 段落标签 | 段落标签/点索引 | 行标签 | 标签计数 | 条件格式化 空行 | 条件格式化 总计 | 条件格式化 段落标签 | 条件格式化 点标签 | ||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
配送 | 交付错误 | 值计数 | =COUNTA(A:A) | =IFERROR(MATCH(A2,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A2)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B2))^0),1) | =IF(COUNTIFS($G$2:G2,G2,$H$2:H2,H2)>1,"",G2) | =IF(I2="","",H2) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K2)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K2),COUNTIF($K$2:K2,K2)),"") | =IFERROR(INDEX(A:A,K2),"") | =IFERROR(INDEX(B:B,L2),"") | =IF(M2="","",COUNTIFS(A:A,M2,B:B,N2)) | =AND(M3<>M2,M3<>"")*1 | =OFFSET(M2,-SUM($P$2:P2),0) | =IF(OR(Q2=Q1,AND(Q2<>Q1,Q2="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R1),"",MAX($R$1:R1)+1),Q2) | =IF(AND(Q2="",Q1="",COUNTIF($T$1:T1,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R2),INDEX(INDIRECT($E$13),$R2),R2)) | =IF(T2="Grand Total",SUM($U$1:U1),IF(ISNUMBER($R2),INDEX(INDIRECT($E$14),$R2),"")) | =$T2="" | =$T2="Grand Total" | =AND(NOT(ISNUMBER($R2)),$R2<>"",$R2<>"Grand Total") | =ISNUMBER($R2) | ||||
质量 | 气味异味 | 列1 | =LEFT(CELL("address",A1),FIND("$",CELL("address",A1),2)) | =IFERROR(MATCH(A3,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A3)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B3))^0),1) | =IF(COUNTIFS($G$2:G3,G3,$H$2:H3,H3)>1,"",G3) | =IF(I3="","",H3) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K3)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$ |
英文:
In a new sheet, paste this:
Department | Tag | Index of unique department | Index of unique Department-Tag match | Unique index of Department-Tag match (Department part) | Unique index of Department-Tag match (Tag part) | Internal row of unique department | Internal row of unique Department-Tag match | Department (sorted) | Tag (sorted) | Department-tag count | Paragraph count | Paragraph label | Paragraph label/point index | Row labels | Count of Tag | Conditional formatting Empty row | Conditional formatting Grand Total | Conditional formatting Paragraph label | Conditional formatting Point label | ||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Distribution | Incorrect delivery | Values count | =COUNTA(A:A) | =IFERROR(MATCH(A2,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A2)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B2))^0),1) | =IF(COUNTIFS($G$2:G2,G2,$H$2:H2,H2)>1,"",G2) | =IF(I2="","",H2) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K2)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K2),COUNTIF($K$2:K2,K2)),"") | =IFERROR(INDEX(A:A,K2),"") | =IFERROR(INDEX(B:B,L2),"") | =IF(M2="","",COUNTIFS(A:A,M2,B:B,N2)) | =AND(M3<>M2,M3<>"")*1 | =OFFSET(M2,-SUM($P$2:P2),0) | =IF(OR(Q2=Q1,AND(Q2<>Q1,Q2="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R1),"",MAX($R$1:R1)+1),Q2) | =IF(AND(Q2="",Q1="",COUNTIF($T$1:T1,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R2),INDEX(INDIRECT($E$13),$R2),R2)) | =IF(T2="Grand Total",SUM($U$1:U1),IF(ISNUMBER($R2),INDEX(INDIRECT($E$14),$R2),"")) | =$T2="" | =$T2="Grand Total" | =AND(NOT(ISNUMBER($R2)),$R2<>"",$R2<>"Grand Total") | =ISNUMBER($R2) | ||||
Quality | Smelling | Column 1 | =LEFT(CELL("address",A1),FIND("$",CELL("address",A1),2)) | =IFERROR(MATCH(A3,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A3)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B3))^0),1) | =IF(COUNTIFS($G$2:G3,G3,$H$2:H3,H3)>1,"",G3) | =IF(I3="","",H3) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K3)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K3),COUNTIF($K$2:K3,K3)),"") | =IFERROR(INDEX(A:A,K3),"") | =IFERROR(INDEX(B:B,L3),"") | =IF(M3="","",COUNTIFS(A:A,M3,B:B,N3)) | =AND(M4<>M3,M4<>"")*1 | =OFFSET(M3,-SUM($P$2:P3),0) | =IF(OR(Q3=Q2,AND(Q3<>Q2,Q3="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R2),"",MAX($R$1:R2)+1),Q3) | =IF(AND(Q3="",Q2="",COUNTIF($T$1:T2,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R3),INDEX(INDIRECT($E$13),$R3),R3)) | =IF(T3="Grand Total",SUM($U$1:U2),IF(ISNUMBER($R3),INDEX(INDIRECT($E$14),$R3),"")) | =$T3="" | =$T3="Grand Total" | =AND(NOT(ISNUMBER($R3)),$R3<>"",$R3<>"Grand Total") | =ISNUMBER($R3) | ||||
Quality | Taste issue | Column 2 | =LEFT(CELL("address",B1),FIND("$",CELL("address",B1),2)) | =IFERROR(MATCH(A4,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A4)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B4))^0),1) | =IF(COUNTIFS($G$2:G4,G4,$H$2:H4,H4)>1,"",G4) | =IF(I4="","",H4) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K4)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K4),COUNTIF($K$2:K4,K4)),"") | =IFERROR(INDEX(A:A,K4),"") | =IFERROR(INDEX(B:B,L4),"") | =IF(M4="","",COUNTIFS(A:A,M4,B:B,N4)) | =AND(M5<>M4,M5<>"")*1 | =OFFSET(M4,-SUM($P$2:P4),0) | =IF(OR(Q4=Q3,AND(Q4<>Q3,Q4="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R3),"",MAX($R$1:R3)+1),Q4) | =IF(AND(Q4="",Q3="",COUNTIF($T$1:T3,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R4),INDEX(INDIRECT($E$13),$R4),R4)) | =IF(T4="Grand Total",SUM($U$1:U3),IF(ISNUMBER($R4),INDEX(INDIRECT($E$14),$R4),"")) | =$T4="" | =$T4="Grand Total" | =AND(NOT(ISNUMBER($R4)),$R4<>"",$R4<>"Grand Total") | =ISNUMBER($R4) | ||||
Distribution | Delivery skipped | Column 3 | =LEFT(CELL("address",I2),FIND("$",CELL("address",I2),2)) | =IFERROR(MATCH(A5,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A5)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B5))^0),1) | =IF(COUNTIFS($G$2:G5,G5,$H$2:H5,H5)>1,"",G5) | =IF(I5="","",H5) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K5)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K5),COUNTIF($K$2:K5,K5)),"") | =IFERROR(INDEX(A:A,K5),"") | =IFERROR(INDEX(B:B,L5),"") | =IF(M5="","",COUNTIFS(A:A,M5,B:B,N5)) | =AND(M6<>M5,M6<>"")*1 | =OFFSET(M5,-SUM($P$2:P5),0) | =IF(OR(Q5=Q4,AND(Q5<>Q4,Q5="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R4),"",MAX($R$1:R4)+1),Q5) | =IF(AND(Q5="",Q4="",COUNTIF($T$1:T4,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R5),INDEX(INDIRECT($E$13),$R5),R5)) | =IF(T5="Grand Total",SUM($U$1:U4),IF(ISNUMBER($R5),INDEX(INDIRECT($E$14),$R5),"")) | =$T5="" | =$T5="Grand Total" | =AND(NOT(ISNUMBER($R5)),$R5<>"",$R5<>"Grand Total") | =ISNUMBER($R5) | ||||
Distribution | Late Dely | Column 4 | =LEFT(CELL("address",J2),FIND("$",CELL("address",J2),2)) | =IFERROR(MATCH(A6,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A6)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B6))^0),1) | =IF(COUNTIFS($G$2:G6,G6,$H$2:H6,H6)>1,"",G6) | =IF(I6="","",H6) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K6)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K6),COUNTIF($K$2:K6,K6)),"") | =IFERROR(INDEX(A:A,K6),"") | =IFERROR(INDEX(B:B,L6),"") | =IF(M6="","",COUNTIFS(A:A,M6,B:B,N6)) | =AND(M7<>M6,M7<>"")*1 | =OFFSET(M6,-SUM($P$2:P6),0) | =IF(OR(Q6=Q5,AND(Q6<>Q5,Q6="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R5),"",MAX($R$1:R5)+1),Q6) | =IF(AND(Q6="",Q5="",COUNTIF($T$1:T5,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R6),INDEX(INDIRECT($E$13),$R6),R6)) | =IF(T6="Grand Total",SUM($U$1:U5),IF(ISNUMBER($R6),INDEX(INDIRECT($E$14),$R6),"")) | =$T6="" | =$T6="Grand Total" | =AND(NOT(ISNUMBER($R6)),$R6<>"",$R6<>"Grand Total") | =ISNUMBER($R6) | ||||
CC | Agent issue | Column 5 | =LEFT(CELL("address",N2),FIND("$",CELL("address",N2),2)) | =IFERROR(MATCH(A7,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A7)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B7))^0),1) | =IF(COUNTIFS($G$2:G7,G7,$H$2:H7,H7)>1,"",G7) | =IF(I7="","",H7) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K7)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K7),COUNTIF($K$2:K7,K7)),"") | =IFERROR(INDEX(A:A,K7),"") | =IFERROR(INDEX(B:B,L7),"") | =IF(M7="","",COUNTIFS(A:A,M7,B:B,N7)) | =AND(M8<>M7,M8<>"")*1 | =OFFSET(M7,-SUM($P$2:P7),0) | =IF(OR(Q7=Q6,AND(Q7<>Q6,Q7="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R6),"",MAX($R$1:R6)+1),Q7) | =IF(AND(Q7="",Q6="",COUNTIF($T$1:T6,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R7),INDEX(INDIRECT($E$13),$R7),R7)) | =IF(T7="Grand Total",SUM($U$1:U6),IF(ISNUMBER($R7),INDEX(INDIRECT($E$14),$R7),"")) | =$T7="" | =$T7="Grand Total" | =AND(NOT(ISNUMBER($R7)),$R7<>"",$R7<>"Grand Total") | =ISNUMBER($R7) | ||||
Distribution | Delivery skipped | Column 6 | =LEFT(CELL("address",O2),FIND("$",CELL("address",O2),2)) | =IFERROR(MATCH(A8,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A8)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B8))^0),1) | =IF(COUNTIFS($G$2:G8,G8,$H$2:H8,H8)>1,"",G8) | =IF(I8="","",H8) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K8)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K8),COUNTIF($K$2:K8,K8)),"") | =IFERROR(INDEX(A:A,K8),"") | =IFERROR(INDEX(B:B,L8),"") | =IF(M8="","",COUNTIFS(A:A,M8,B:B,N8)) | =AND(M9<>M8,M9<>"")*1 | =OFFSET(M8,-SUM($P$2:P8),0) | =IF(OR(Q8=Q7,AND(Q8<>Q7,Q8="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R7),"",MAX($R$1:R7)+1),Q8) | =IF(AND(Q8="",Q7="",COUNTIF($T$1:T7,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R8),INDEX(INDIRECT($E$13),$R8),R8)) | =IF(T8="Grand Total",SUM($U$1:U7),IF(ISNUMBER($R8),INDEX(INDIRECT($E$14),$R8),"")) | =$T8="" | =$T8="Grand Total" | =AND(NOT(ISNUMBER($R8)),$R8<>"",$R8<>"Grand Total") | =ISNUMBER($R8) | ||||
Distribution | Outside leakage | Range 1 | =E3&"1:"&E3&$E$2 | =IFERROR(MATCH(A9,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A9)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B9))^0),1) | =IF(COUNTIFS($G$2:G9,G9,$H$2:H9,H9)>1,"",G9) | =IF(I9="","",H9) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K9)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K9),COUNTIF($K$2:K9,K9)),"") | =IFERROR(INDEX(A:A,K9),"") | =IFERROR(INDEX(B:B,L9),"") | =IF(M9="","",COUNTIFS(A:A,M9,B:B,N9)) | =AND(M10<>M9,M10<>"")*1 | =OFFSET(M9,-SUM($P$2:P9),0) | =IF(OR(Q9=Q8,AND(Q9<>Q8,Q9="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R8),"",MAX($R$1:R8)+1),Q9) | =IF(AND(Q9="",Q8="",COUNTIF($T$1:T8,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R9),INDEX(INDIRECT($E$13),$R9),R9)) | =IF(T9="Grand Total",SUM($U$1:U8),IF(ISNUMBER($R9),INDEX(INDIRECT($E$14),$R9),"")) | =$T9="" | =$T9="Grand Total" | =AND(NOT(ISNUMBER($R9)),$R9<>"",$R9<>"Grand Total") | =ISNUMBER($R9) | ||||
Quality | Inside leakage | Range 2 | =E4&"1:"&E4&$E$2 | =IFERROR(MATCH(A10,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A10)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B10))^0),1) | =IF(COUNTIFS($G$2:G10,G10,$H$2:H10,H10)>1,"",G10) | =IF(I10="","",H10) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K10)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K10),COUNTIF($K$2:K10,K10)),"") | =IFERROR(INDEX(A:A,K10),"") | =IFERROR(INDEX(B:B,L10),"") | =IF(M10="","",COUNTIFS(A:A,M10,B:B,N10)) | =AND(M11<>M10,M11<>"")*1 | =OFFSET(M10,-SUM($P$2:P10),0) | =IF(OR(Q10=Q9,AND(Q10<>Q9,Q10="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R9),"",MAX($R$1:R9)+1),Q10) | =IF(AND(Q10="",Q9="",COUNTIF($T$1:T9,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R10),INDEX(INDIRECT($E$13),$R10),R10)) | =IF(T10="Grand Total",SUM($U$1:U9),IF(ISNUMBER($R10),INDEX(INDIRECT($E$14),$R10),"")) | =$T10="" | =$T10="Grand Total" | =AND(NOT(ISNUMBER($R10)),$R10<>"",$R10<>"Grand Total") | =ISNUMBER($R10) | ||||
Quality | Inside leakage | Range 3 | =E5&"2:"&E5&$E$2 | =IFERROR(MATCH(A11,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A11)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B11))^0),1) | =IF(COUNTIFS($G$2:G11,G11,$H$2:H11,H11)>1,"",G11) | =IF(I11="","",H11) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K11)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K11),COUNTIF($K$2:K11,K11)),"") | =IFERROR(INDEX(A:A,K11),"") | =IFERROR(INDEX(B:B,L11),"") | =IF(M11="","",COUNTIFS(A:A,M11,B:B,N11)) | =AND(M12<>M11,M12<>"")*1 | =OFFSET(M11,-SUM($P$2:P11),0) | =IF(OR(Q11=Q10,AND(Q11<>Q10,Q11="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R10),"",MAX($R$1:R10)+1),Q11) | =IF(AND(Q11="",Q10="",COUNTIF($T$1:T10,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R11),INDEX(INDIRECT($E$13),$R11),R11)) | =IF(T11="Grand Total",SUM($U$1:U10),IF(ISNUMBER($R11),INDEX(INDIRECT($E$14),$R11),"")) | =$T11="" | =$T11="Grand Total" | =AND(NOT(ISNUMBER($R11)),$R11<>"",$R11<>"Grand Total") | =ISNUMBER($R11) | ||||
Distribution | Outside leakage | Range 4 | =E6&"2:"&E6&$E$2 | =IFERROR(MATCH(A12,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A12)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B12))^0),1) | =IF(COUNTIFS($G$2:G12,G12,$H$2:H12,H12)>1,"",G12) | =IF(I12="","",H12) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K12)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K12),COUNTIF($K$2:K12,K12)),"") | =IFERROR(INDEX(A:A,K12),"") | =IFERROR(INDEX(B:B,L12),"") | =IF(M12="","",COUNTIFS(A:A,M12,B:B,N12)) | =AND(M13<>M12,M13<>"")*1 | =OFFSET(M12,-SUM($P$2:P12),0) | =IF(OR(Q12=Q11,AND(Q12<>Q11,Q12="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R11),"",MAX($R$1:R11)+1),Q12) | =IF(AND(Q12="",Q11="",COUNTIF($T$1:T11,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R12),INDEX(INDIRECT($E$13),$R12),R12)) | =IF(T12="Grand Total",SUM($U$1:U11),IF(ISNUMBER($R12),INDEX(INDIRECT($E$14),$R12),"")) | =$T12="" | =$T12="Grand Total" | =AND(NOT(ISNUMBER($R12)),$R12<>"",$R12<>"Grand Total") | =ISNUMBER($R12) | ||||
Quality | Inside leakage | Range 5 | =E7&"2:"&E7&$E$2 | =IFERROR(MATCH(A13,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A13)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B13))^0),1) | =IF(COUNTIFS($G$2:G13,G13,$H$2:H13,H13)>1,"",G13) | =IF(I13="","",H13) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K13)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K13),COUNTIF($K$2:K13,K13)),"") | =IFERROR(INDEX(A:A,K13),"") | =IFERROR(INDEX(B:B,L13),"") | =IF(M13="","",COUNTIFS(A:A,M13,B:B,N13)) | =AND(M14<>M13,M14<>"")*1 | =OFFSET(M13,-SUM($P$2:P13),0) | =IF(OR(Q13=Q12,AND(Q13<>Q12,Q13="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R12),"",MAX($R$1:R12)+1),Q13) | =IF(AND(Q13="",Q12="",COUNTIF($T$1:T12,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R13),INDEX(INDIRECT($E$13),$R13),R13)) | =IF(T13="Grand Total",SUM($U$1:U12),IF(ISNUMBER($R13),INDEX(INDIRECT($E$14),$R13),"")) | =$T13="" | =$T13="Grand Total" | =AND(NOT(ISNUMBER($R13)),$R13<>"",$R13<>"Grand Total") | =ISNUMBER($R13) | ||||
Distribution | Outside leakage | Range 6 | =E8&"2:"&E8&$E$2 | =IFERROR(MATCH(A14,INDIRECT($E$9),0),"") | =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A14)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B14))^0),1) | =IF(COUNTIFS($G$2:G14,G14,$H$2:H14,H14)>1,"",G14) | =IF(I14="","",H14) | =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K14)-ROW($K$2)+1),"") | =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K14),COUNTIF($K$2:K14,K14)),"") | =IFERROR(INDEX(A:A,K14),"") | =IFERROR(INDEX(B:B,L14),"") | =IF(M14="","",COUNTIFS(A:A,M14,B:B,N14)) | =AND(M15<>M14,M15<>"")*1 | =OFFSET(M14,-SUM($P$2:P14),0) | =IF(OR(Q14=Q13,AND(Q14<>Q13,Q14="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R13),"",MAX($R$1:R13)+1),Q14) | =IF(AND(Q14="",Q13="",COUNTIF($T$1:T13,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R14),INDEX(INDIRECT($E$13),$R14),R14)) | =IF(T14="Grand Total",SUM($U$1:U13),IF(ISNUMBER($R14),INDEX(INDIRECT($E$14),$R14),"")) | =$T14="" | =$T14="Grand Total" | =AND(NOT(ISNUMBER($R14)),$R14<>"",$R14<>"Grand Total") | =ISNUMBER($R14) | ||||
Formulas count | =COUNTA(G:G) | ||||||||||||||||||||||||
Check | =IF(AND(COUNTIF(T:T,"Grand Total")=1, E16>=E2),"OK","Add more rows") |
You should obtain something like this (formats not included):
Range | Purpose |
---|---|
A:B | The input |
E2 | Counts the number of values in column A |
D3:E14 | Dynamic ranges |
E16 | Counts the number of values in column G |
E17 | Checks if E16 is less than E2; it will tell you if you have to drag down the formulas in range G:U |
G:R | The "behind the scene" formulas; check the headers |
T:U | The result |
W:Z | Formulas for the conditional formatting of T2:U14; you don't need them for the result to be returned, but you can use them to apply conditional formatting to the result |
But, seriously: a pivot table is way better than all of this. No idea why you would go with formulas.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论