Cómo usar EXCEL para contar, hay 67 personas participando en 67 proyectos, y en cada proyecto pueden participar 10 personas ¿Cómo contar en qué proyectos participa cada uno?

Primero pones los nombres en una columna, empezando por A2. Luego copie y pegue la transposición especial en una línea, comenzando en B1.

Puedes definir filas como evaluadores y columnas como revisores. Escriba la ubicación donde ocurre la acción de evaluación como 1 y deje las demás partes en blanco. Por ejemplo, si Zhang San evalúa a Li Si, busque a Li Si en la columna, busque a Zhang San en la fila, escriba 1 donde los dos se cruzan y escriba todo lo demás de la misma manera. Creo que debes ser regular al dividir, así que no debería ser demasiado problema.

Luego ingrese B69:

=B1&"Evaluación"&","&IF(B2=1,$A2,"")&","&IF(B3=1,$ A3,"")&","&SI(B4=1,$A4,"")&","&SI(B5=1,$A5,"")&","&SI(B6=1,$A6, "")&","&SI(B7=1,$A7,"")&","&SI(B8=1,$A8,"")&","&SI(B9=1,$A9,"" )&","&SI(B10=1,$A10,"")&","&SI(B11=1,$A11,"")&","&SI(B12=1,$A12,"")& ","&SI(B13=1,$A13,"")&","&SI(B14=1,$A14,"")&","&SI(B15=1,$A15,"")&", "&SI(B16=1,$A16,"")&","&SI(B17=1,$A17,"")&","&SI(B18=1,$A18,"")&","&SI (B19=1,$A19,"") &","&SI(B20=1,$A20,"")&","&SI(B21=1,$A21,"")&","&SI(B22 =1,$A22,"")&","&SI(B23=1,$A23,"")&","&SI(B24=1,$A24,"")&","&SI(B25=1 ,$A25,"")&","&SI(B26=1,$A26,"")&","&SI(B27=1,$A27,"")&","&SI(B28=1,$ A28,"")&","&SI(B29=1,$A29,"")&","&SI(B30=1,$A30,"")&","&SI(B31=1,$A31, "")&","&SI(B32=1,$A32,"")&","&SI(B33=1,$A33,"")&","&SI(B34=1,$A34,"" )&","&SI(B35=1,$A35,"")&","&SI(B36=1,$A36,"")&","&SI(B37=1,$A37,"")& ","&SI(B38=1,$A38,"")&","&SI(B39=1,$A39,"")&","&SI(B40=1,$A40,"")&", "&SI(B41=1,$A41,"")&","&SI(B42=1,$A42,"")&","&SI(B43=1,$A43,"")&","&SI (B44=1,$A44,"")&","&SI(B45=1,$A45,"")&","&SI(B46=1,$A46,"")&","&SI(B47 =1,$A47,"")&","&SI(B48=1,$A48,"")&","&SI(B49=1,$A49,"")&","&SI(B50=1 ,$A50,"")&","&soy

p;SI(B51=1,$A51,"")&","&SI(B52=1,$A52,"")&","&SI(B53=1,$A53,"")&"," &SI(B54=1,$A54,"")&","&SI(B55=1,$A55,"")&","&SI(B56=1,$A56,"")&","&SI( B57=1,$A67,"")&","&SI(B58=1,$A58,"")&","&SI(B59=1,$A59,"")&","&SI(B60= 1,$A60,"")&","&SI(B61=1,$A61,"")&","&SI(B62=1,$A62,"")&","&SI(B63=1, $A63,"")&","&SI(B64=1,$A64,"")&","&SI(B65=1,$A65,"")&","&SI(B66=1,$A66 ,"")&","&IF(B67=1,$A67,"")&","&IF(B68=1,$A68,"")

Y llenar al revés.

Luego copie la línea 69, cree una nueva tabla, péguela selectivamente en valores numéricos y transpóngala.

Puede haber algunas comas adicionales. Ctrl+F reemplaza .. (dos comas en inglés) con espacios en blanco.

Los resultados de mi prueba son los siguientes:

Mu Decheng evaluó a Sun Zhenyuan, Li Fudong, Zhang Weihong, Wang Hao, Xiao Weimin, Zhang Jing, Xu Fei, Wu Guangfa, Guo Biqing, Yao Maojun

Sun Zhenyuan evaluó a Li Fudong y Zhang Weihong, Wang Hao, Xiao Weimin, Zhang Jing, Xu Fei, Wu Guangfa, Guo Biqing, Yao Maojun, Jia Bin,

Li Fudong comentó sobre Zhang Weihong, Wang Hao, Xiao Weimin, Zhang Jing, Xu Fei, Wu Guangfa, Guo Biqing, Yao Maojun, Jia Bin, Bao Wenzhong

Zhang Weihong evaluó a Wang Hao, Xiao Weimin, Zhang Jing, Xu Fei, Wu Guangfa, Guo Biqing, Yao Maojun, Jia Bin, Bao Wenzhong, Zhan Guowei,

Wang Hao evaluaron a Xiao Weimin, Zhang Jing, Xu Fei, Wu Guangfa, Guo Biqing, Yao Maojun, Jia Bin, Bao Wenzhong, Zhan Guowei, Chang Zhixin

Xiao Weimin comentó sobre Zhang Jing, Xu Fei, Wu Guangfa, Guo Biqing, Yao Maojun, Jia Bin, Bao Wenzhong, Zhan Guowei, Chang Zhixin, Chen Yu,

Zhang Jing comentó sobre Xu Fei, Wu Guangfa, Guo Biqing, Yao Maojun, Jia Bin, Bao Wenzhong, Zhan Guowei, Chang Zhixin, Chen Yu, Ning Xiaojian

Xu Fei comentó sobre Wu Guangfa, Guo Biqing, Yao Maojun, Jia Bin, Bao Wenzhong, Zhan Guowei, Chang Zhixin, Chen Yu, Ning Xiaojian, Yao Hongchao,

Wu Guangfa comentó sobre Guo Biqing, Yao Maojun, Jia Bin, Bao Wenzhong, Zhan Guowei, Chang Zhixin, Chen Yu, Ning Xiaojian, Yao Hongchao, Kang Xiudong

Guo Biqing comentó sobre Yao Maojun, Jia Bin, Bao Wenzhong, Zhan Guowei, Chang Zhixin, Chen Yu, Ning Xiaojian , Yao Hongchao, Kang Xiudong, Wei Chunyan,

Yao Maojun evaluó a Jia Bin, Bao Wenzhong, Zhan Guowei, Chang Zhixin, Chen Yu, Ning Xiaojian, Yao Hongchao, Kang Xiudong, Wei Chunyan, Bao Bangjin

Jia Bin evaluó a Bao Wenzhong, Zhan Guowei, Chang Zhixin, Chen Yu, Ning Xiaojian, Yao Hongchao, Kang Xiudong, Wei Chunyan, Bao Bangjin, Liu Caili,

Bao Wenzhong comentó Zhan Guowei, Chang Zhixin, Chen Yu, Ning Xiao Jian, Yao Hongchao, Kang Xiudong, Wei Chunyan, Bao Bangjin, Liu Caili, Ouyang Xiaohui

Zhan Guowei comentó sobre Chang Zhixin, Chen Yu, Ning Xiaojian, Yao Hongchao, Kang Xiudong, Wei Chunyan, Bao Bangjin, Liu Caili, Ouyang Xiaohui, Liu Xiaofeng,

Chang Zhixin evaluaron a Chen Yu, Ning Xiaojian, Yao Hongchao, Kang Xiudong, Wei Chunyan, Bao Bangjin, Liu Caili, Ouyang Xiaohui, Liu Xiaofeng, Xu Xiaoming

Chen Yu evaluó a Ning Xiaojian, Yao Hongchao, Kang Xiudong, Wei Chunyan, Bao Bangjin, Liu Caili, Ouyang Xiaohui, Liu Xiaofeng, Xu Xiaoming, Dou Zhanjie,

Ning Xiaojian evaluó a Yao Hongchao y Kang Xiu Dong, Wei Chunyan, Bao Bangjin, Liu Caili, Ouyang Xiaohui, Liu Xiaofeng, Xu Xiaoming, Dou Zhanjie, Wu Shaohua

Comentó Yao Hongchao sobre Kang Xiudong, Wei Chunyan, Bao Bangjin, Liu Caili, Ouyang Xiaohui, Liu Xiaofeng, Xu Xiaoming, Dou Zhanjie, Wu Shaohua, Li Xiangping,

Kang Xiudong comentó sobre Wei Chunyan, Bao Bangjin, Liu Caili , Ouyang Xiaohui, Liu Xiaofeng, Xu Xiaoming, Dou Zhanjie, Wu Shaohua, Li Xiangping, Zuo Zhonghua

Wei Chunyan evaluó a Bao Bangjin, Liu Caili, Ouyang Xiaohui, Liu Xiaofeng, Xu Xiaoming, Dou Zhanjie, Wu Shaohua, Li Xiangping, Zuo Zhonghua, Xu Guiyong,

Bao Bangjin evaluaron a Liu Caili, Ouyang Xiaohui, Liu Xiaofeng, Xu Xiaoming, Dou Zhanjie, Wu Shaohua, Li Xiangping, Zuo Zhonghua, Xu Guiyong, Chen Hongsheng

Liu Caili comentó sobre Ouyang Xiaohui, Liu Xiaofeng, Xu Xiaoming, Dou Zhanjie, Wu Shaohua, Li Xiangping, Zuo Zhonghua, Xu Guiyong, Chen Hongsheng, Liu Yuanlong,

Ouyang Xiaohui evalúa a Liu Xiaofeng, Xu Xiaoming, Dou Zhanjie, Wu Shaohua, Li Xiangping, Zuo Zhonghua, Xu Guiyong, Chen Hongsheng, Liu Yuanlong, Deng Yundong

Liu Xiaofeng evalúa a Xu Xiaoming, Dou Zhanjie, Wu Shaohua, Li Xiang Ping, Zuo Zhonghua, Xu Guiyong, Chen Hongsheng, Liu Yuanlong, Deng Yundong, Zhang Ren,

Xu Xiaoming comentaron sobre Dou Zhanjie, Wu Shaohua, Li Xiangping, Zuo Zhonghua, Xu Guiyong, Chen Hongsheng, Liu Yuanlong, Deng Yundong, Zhang Ren, Tang Wenhong

Dou Zhanjie evaluó a Wu Shaohua, Li Xiangping, Zuo Zhonghua, Xu Guiyong, Chen Hongsheng, Liu Yuanlong, Deng Yundong, Zhang Ren, Tang Wenhong, Chen Hao ,

Wu Shaohua evaluó a Li Xiangping, Zuo Zhonghua, Xu Guiyong, Chen Hongsheng, Liu Yuanlong, Deng Yundong, Zhang Ren, Tang Wenhong, Chen Hao, Guo Wei

Comentó Li Xiangping sobre Zuo Zhonghua, Xu Guiyong, Chen Hongsheng, Liu Yuanlong, Deng Yundong, Zhang Ren, Tang Wenhong, Chen Hao, Guo Wei, Wang Tong,

Izquierda

Zhonghua evaluó a Xu Guiyong, Chen Hongsheng, Liu Yuanlong, Deng Yundong, Zhang Ren, Tang Wenhong, Chen Hao, Guo Wei, Wang Tong, Ren Li

Xu Guiyong evaluó a Chen Hongsheng, Liu Yuanlong, Deng Yundong, Zhang Ren, Tang Wenhong, Chen Hao, Guo Wei, Wang Tong, Ren Li, Liu Ye,

Chen Hongsheng comentaron sobre Liu Yuanlong, Deng Yundong, Zhang Ren, Tang Wenhong, Chen Hao, Guo Wei, Wang Tong, Ren Li, Liu Ye, Wang Bo

Liu Yuanlong evaluó a Deng Yundong, Zhang Ren, Tang Wenhong, Chen Hao, Guo Wei, Wang Tong, Ren Li, Liu Ye, Wang Bo, Zhu Kailiang ,

Deng Yundong evaluó a Zhang Ren, Tang Wenhong, Chen Hao, Guo Wei, Wang Tong, Ren Li, Liu Ye, Wang Bo, Zhu Kailiang, Lei Zhengqi

Comentó Zhang Ren sobre Tang Wenhong, Chen Hao, Guo Wei, Wang Tong, Ren Li, Liu Ye, Wang Bo, Zhu Kailiang, Lei Zheng Qi, Wang Rui,

Tang Wenhong evaluó a Chen Hao, Guo Wei, Wang Tong , Ren Li, Liu Ye, Wang Bo, Zhu Kailiang, Lei Zhengqi, Wang Rui, Zhang Yuqiang

Chen Hao evaluó a Guo Wei, Wang Tong, Ren Li, Liu Ye, Wang Bo, Zhu Kailiang, Lei Zhengqi, Wang Rui, Zhang Yuqiang, Sun Chao,

Guo Wei comentó sobre Wang Tong, Ren Li, Liu Ye, Wang Bo, Zhu Kailiang, Lei Zhengqi, Wang Rui, Zhang Yuqiang, Sun Chao, Li Qianhuo

Wang Tong comentó sobre Ren Li, Liu Ye, Wang Bo, Zhu Kailiang, Lei Zhengqi, Wang Rui, Zhang Yuqiang, Sun Chao, Li Qianhuo, Liu Guixia,

Ren Li evalúa a Liu Ye, Wang Bo, Zhu Kailiang, Lei Zhengqi, Wang Rui, Zhang Yuqiang, Sun Chao, Li Qianhuo, Liu Guixia, Wang Shuanghong

Liu Ye evalúa a Wang Bo, Zhu Kailiang, Lei Zhengqi, Wang Rui, Zhang Yuqiang, Sun Chao, Li Yianhuo, Liu Guixia, Wang Shuanghong, Chen Huimin,

Wang Bo comentaron sobre Zhu Kailiang, Lei Zhengqi, Wang Rui, Zhang Yuqiang, Sun Chao, Li Yianhuo, Liu Guixia, Wang Shuanghong, Chen Huimin, Wang Fangju

Zhu Kailiang evaluó a Lei Zhengqi, Wang Rui, Zhang Yuqiang, Sun Chao, Li Qianhuo, Liu Guixia, Wang Shuanghong, Chen Huimin, Wang Fangju, Yan Qiulan ,

Lei Zhengqi evaluó a Wang Rui, Zhang Yuqiang, Sun Chao, Li Qiuhuo, Liu Guixia, Wang Shuanghong, Chen Huimin, Wang Fangju, Yan Qiulan, Tan Jialin

Wang Rui comentó sobre Zhang Yuqiang, Sun Chao, Li Qiuhuo, Liu Guixia, Wang Shuanghong, Chen Huimin, Wang Fangju, Yan Qiulan, Tan Jialin, Nie Wenbiao,

Zhang Yuqiang evaluó a Sun Chao, Li Yianhuo, Liu Guixia, Wang Shuanghong, Chen Huimin, Wang Fangju, Yan Qiulan, Tan Jialin, Nie Wenbiao, Qi Binghuan

Sun Chao evaluó a Li Yianhuo, Liu Guixia, Wang Shuanghong p>Liu Guixia evaluó a Wang Shuanghong, Chen Huimin, Wang Fangju, Yan Qiulan, Tan Jialin, Nie Wenbiao, Qi Binghuan, Chai Shiting, Su Lingyan, Liu Fengyan,

Wang Shuanghong evaluó a Chen Huimin, Wang Fangju, Yan Qiulan, Tan Jialin, Nie Wenbiao, Qi Binghuan, Chai Shiting, Su Lingyan, Liu Fengyan, Wang QinChen Huimin evaluó a Wang Fangju, Yan Qiulan, Tan Jialin, Nie Wenbiao, Qi Binghuan, Chai Shiting, Su Lingyan, Liu Fengyan, Wang Qin, Li Xue,

Wang Fangju evaluaron a Yan Qiulan, Tan Jialin, Nie Wenbiao, Qi Binghuan, Chai Shiting, Su Lingyan, Liu Fengyan, Wang Qin, Li Xue, Zhang Bihuang

Yan Qiulan comentó sobre Tan Jialin, Nie Wenbiao, Qi Binghuan, Chai Shiting, Su Lingyan, Liu Fengyan, Wang Qin, Li Xue, Zhang Bihuang, Jing Bin,

Tan Jialin evalúa a Nie Wenbiao, Qi Binghuan, Chai Shiting, Su Lingyan, Liu Fengyan, Wang Qin, Li Xue, Zhang Bihuang, Jing Bin, Gan Wanfang

Nie Wenbiao evalúa a Qi Binghuan, Chai Shiting, Su Lingyan, Liu Fengyan, Wang Qin, Li Xue, Zhang Bihuang, Jing Bin, Gan Wanfang, Mu Hongli,

Qi Binghuan comentó sobre Chai Shiting, Su Lingyan, Liu Fengyan, Wang Qin, Li Xue, Zhang Bihuang, Jing Bin, Gan Wan Fang, Mu Hongli , Liu Lijuan

Liu Yan comentó sobre Su Lingyan, Liu Fengyan, Wang Qin, Li Xue, Zhang Bihuang, Jing Bin, Gan Wanfang, Mu Hongli, Liu Lijuan, Chai Shiting,

Su Lingyan evalúa a Liu Fengyan, Wang Qin, Li Xue, Zhang Bihuang, Jing Bin, Gan Wanfang, Mu Hongli, Liu Lijuan, Chai Shiting, Han Wenwen

Liu Fengyan evalúa a Mu Decheng, Wang Qin, Li Xue, Zhang Bi Huang, Jing Bin, Gan Wanfang, Mu Hongli, Liu Lijuan,

Chai Shiting, Han Wenwen

Wang Qin comentó sobre Mu Decheng, Sun Zhenyuan, Li Xue, Zhang Bihuang, Jing Bin, Gan Wanfang, Mu Hongli, Liu Lijuan, Chai Shiting, Han Wenwen

Li Xue comentó sobre Mu Decheng, Sun Zhenyuan, Li Fudong, Zhang Bihuang, Jing Bin, Gan Wanfang, Mu Hongli, Liu Lijuan, Chai Shiting, Han Wenwen

Zhang Bihuang comentó sobre Mu Decheng, Sun Zhenyuan , Li Fudong, Zhang Weihong, Jingbin, Gan Wan Fang, Mu Hongli, Liu Lijuan, Chai Shiting, Han Wenwen

Jing Bin comentó sobre Mu Decheng, Sun Zhenyuan, Li Fudong, Zhang Weihong, Wang Hao, Gan Wanfang, Mu Hongli, Liu Lijuan, Chai Shiting, Han Wenwen

Gan Wanfang evaluó a Mu Decheng, Sun Zhenyuan, Li Fudong, Zhang Weihong, Wang Hao, Xiao Weimin, Mu Hongli, Liu Lijuan, Chai Shiting , Han Wenwen

Mu Hongli evaluó a Mu Decheng, Sun Zhenyuan, Li Fudong, Zhang Weihong, Wang Hao, Xiao Weimin, Zhang Jing, Liu Lijuan, Chai Shiting, Han Wenwen

Liu Lijuan comentó sobre Mu Decheng, Sun Zhenyuan, Li Fudong, Zhang Weihong, Wang Hao, Xiao Weimin, Zhang Jing, Xu Fei, Chai Shiting, Han Wenwen

Chai Shiting comentó sobre Mu Decheng, Sun Zhenyuan, Li Fudong, Zhang Weihong, Wang Hao, Xiao Weimin, Zhang Jing, Xu Fei, Wu Guangfa y Han Wenwen

Han Wenwen comentó sobre Mu Decheng, Sun Zhenyuan, Li Fudong, Zhang Weihong, Wang Hao, Xiao Weimin , Zhang Jing, Xu Fei, Wu Guangfa, Guo Biqing,