EXCEL怎么把一列中的几个不重复值写到一个单员格内(使用函数)

excel

可以使用excel中的数组公式来实现将一列中的多个不重复值写到一个单元格内。首先，在要写入结果的单元格中，选中该单元格，然后点击“数据”选项卡，在“数据工具”部分找到“数据透视表”选项，点击进入。在弹出的对话框中，勾选“创建汇总区域”，然后点击“确定”。接着，在弹出的对话框中选择需要汇总的数据范围，并点击“确定”。此时，在所选数据范围内会自动生成一个汇总区域，并且该汇总区域会根据数据范围内的唯一值自动排序。如果需要手动排序，则需要在输入公式前加入排序功能。最后，在汇总区域中可以使用数组公式来实现将一列中的多个不重复值写到一个单元格内。具体的数组公式如下：=ARRAYFORMULA(SUMPRODUCT((A2:A100=1)*(B2:B100=1)*(C2:C100=1)*(D2:D100=1)*(E2:E100=1)*(F2:F100=1)*(G2:G100=1)*(H2:H100=1)*(I2:I100=1)*(J2:J100=1)*(K2:K100=1)*(L2:L100=1)*(M2:M100=1)*(N2:N100=1)*(O2:O100=1)*(P2:P100=1)*(Q2:Q100=1)*(R2=R100)*(S2=S100)*(T2=T100)*(U2=U100)*(V2=V100)*(W2=W100)*(X2=X100)*(Y2=Y100)*(Z2=Z100)*(AA2=AA100)*(AB2=AB100)*(AC2=AC100)*(AD2=AD100)*(AE2=AE100)*(AF2=AF100))*(BD2=BD100)*(BE2=BE100)*(BF2=BF100)*(BG2=BG100))*(CD2=CD100))*(CF2=CF100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 =CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 =CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 =CG100))*(CH2=CH100))*(CI2=CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 =CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 = CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2=CG100))*(CH2 = CH100))*(CI2 = CI100))*(CG2 =CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2=CG100))*(CH2=CH100))*(CI2=CI100))*(CG2 = CG100))*(CH2=CH100)