每张表中有一个常数的Sheet to Sheet Data Transfer [英] Sheet to Sheet Data Transfer with one constant in each sheet
问题描述
工作簿中有两张表。
第1列A-C:RANK |国家商店ID | SALES(从最大到最小)
第2列A-E:区域商店ID |位置|经理|销售| RANK
工作表2将手动输入设置数据:区域商店ID,位置和管理器
想要完成的是第2页自动从sheet1 column-C到SALES sheet2 column-D输入SALES和从sheet1列A到sheet2列-E的国家等级。两张表中的常数将为商店ID;其设置在每行的列-ATP2中,并且在sheet1列-B中随机设置。
是否有一个简单的点击来做这个或需要一些编程?
在表格2中,使用单元格D2中的这个公式来获得销售。
= INDEX(Sheet1!C: C,MATCH(Sheet2!A2,Sheet1!B:B,0))
在E2中使用获得排名
= INDEX(Sheet1!A:A,MATCH(Sheet2!A2,Sheet1!B:B,0 ))
向下复制。
I have a regional store manager that needs help in Excel. We will be comparing regional ranks within the national ranks.
There are two sheets in the workbook.
Sheet 1 columns A-C: RANK | National Store IDs | SALES (largest to smallest)
Sheet 2 columns A-E: Regional Store IDs | Location | Manager | SALES | RANK
Sheet 2 will have manually input set data: Regional Store ID, Location, and manager
What I would like to accomplish is for sheet 2 to automatically enter the SALES from sheet1 column-C to SALES sheet2 column-D and national rank from sheet1 Column-A to sheet2 column-E. The constant in the both sheets will be the Store ID; which is set in column-A sheet2 per row and random in sheet1 column-B.
Is there a simple click to do this or does it require some programming?
On sheet 2 use this formula in cell D2 to get the sales.
=INDEX(Sheet1!C:C,MATCH(Sheet2!A2,Sheet1!B:B,0))
In E2 use this to get the rank
=INDEX(Sheet1!A:A,MATCH(Sheet2!A2,Sheet1!B:B,0))
Copy down.
这篇关于每张表中有一个常数的Sheet to Sheet Data Transfer的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!