Question

如何让EXCEL自动统计数据并生成新内容

比如对于A列中的数据，若值为1,2,3，B列生成1； 若值为4,5,6，B列生成2.....
另外在某单元格统计B中有多少个1，多少个2......

Answer 1

b1
=OR(A1={1,2,3})*1+OR(A1={4,5,6})*2
c1
=COUNTIF(B:B,1)&"个1 "&COUNTIF(B:B,2)&"个2"

追问

那如果我是在新工作表（sheet2）中统计，是否公式要改为
b1=OR(SHEET1!A1={1,2,3})*1+OR(SHEET!A1={4,5,6})*2

c1=COUNTIF(sheet1!A:A,1)&"个1 "&COUNTIF(SHEET1!A:A,2)&"个2"

追答

b1
=OR(sheet1!A1={1,2,3})*1+OR(sheet1!A1={4,5,6})*2
c1
=COUNTIF(sheet1!B:B,1)&"个1 "&COUNTIF(sheet1!B:B,2)&"个2"

追问

公式中的数字能用文字替换吗？
eg：
b1
=OR(sheet1!A1={"1实验","2测试","3方案"})*计算+OR(sheet1!A1={"4机箱","5鼠标","6键盘"})*电脑

追答

b1
=LOOKUP(Sheet1!A1,{"1实验","2测试","3方案","4机箱","5鼠标","6键盘"},{"计算","计算","计算","电脑","电脑","电脑"})