江湖救急 怎样将excel中多列内容 123456变为1/2/3/4/5/6特定条件下组合
发布网友
共2个回答
热心网友
这种类型并不适合用公式来做，特别是数据量大的时候，运算速度会比较慢，数据量大的时候，要用VBA来做。现在给出的是数组公式，即同时按住Ctrl+Shift+Enter结束公式编辑，会自动生成一对“｛｝”，由于库存号数量是不定的，公式列出的最多5个，再多公式还要加长。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDEX(F:F,SMALL(IF(($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$H$2:$H$1000=A2&B2&C2&D2&E2&H2)*(MATCH($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",$A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",0)=ROW($2:$1000)-1),ROW($2:$1000),4^8),1))&"/"&INDEX(F:F,SMALL(IF(($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$H$2:$H$1000=A2&B2&C2&D2&E2&H2)*(MATCH($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",$A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",0)=ROW($2:$1000)-1),ROW($2:$1000),4^8),2))&"/"&INDEX(F:F,SMALL(IF(($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$H$2:$H$1000=A2&B2&C2&D2&E2&H2)*(MATCH($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",$A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",0)=ROW($2:$1000)-1),ROW($2:$1000),4^8),3))&"/"&INDEX(F:F,SMALL(IF(($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$H$2:$H$1000=A2&B2&C2&D2&E2&H2)*(MATCH($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",$A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",0)=ROW($2:$1000)-1),ROW($2:$1000),4^8),4))&"/"&INDEX(F:F,SMALL(IF(($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$H$2:$H$1000=A2&B2&C2&D2&E2&H2)*(MATCH($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",$A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000&$D$2:$D$1000&$E$2:$E$1000&$F$2:$F$1000&$H$2:$H$1000&"",0)=ROW($2:$1000)-1),ROW($2:$1000),4^8),5)),"//","")&"删","/删",""),"删","")
热心网友
这个要些VBA代码搞定吧，感觉excel的自带函数实现不了，望采纳
