如何在Excel中基于另一个单元格的值将值重复到行中 [英] How to repeat a value into rows based on another cells value in excel

问题描述

在我的工作表中，我具有以下数据，这些数据使用另一个工作表中的= countif()语句告诉我在某个职位中有多少人:

In my worksheet I have the following data that tells me how many people are in a certain job role using a =countif() statement from another worksheet:

Job Role|Amount of people in the role
Job 1   |            6
Job 2   |            26
Job 3   |            4

现在我要做的是在工作表中使用作业1，作业2和作业3作为该表下方3个单独表的标题，如下所示:

Now I have done is in my worksheet I have used Job 1, Job 2 and Job 3 as headings to 3 separate tables below this table like this:

Job Role|Amount of people in the role
Job 1   |            6
Job 2   |            26
Job 3   |            4


Job 1                   Job 2                    Job3
Name|Job Role           Name|Job Role            Name|Job Role

此刻，我要做的就是根据顶部表中的数量(例如，在Job 1表下)，用工作角色填充3个表中的每个表，我希望工作角色重复6次(我不能只输入一次，因为我希望它能自动工作，以防万一其他员工被添加到我拥有的员工列表中).

All I would like to do at the moment is to populate each of the 3 tables with the job role based on the amount that is in the top table, for example, under the Job 1 table, I would like the job role to repeat 6 times (I cannot just type this in because I want this to work automatically just in case another employee gets added to the employee list I have).

Job 1
Name|Job Role
1   |Job 1
2   |Job 1
3   |Job 1
4   |Job 1
5   |Job 1
6   |Job 1

，对于作业2(将有26个重复行)和作业3(将有4行)相同.任何帮助将不胜感激.

and the same for job 2 (which will have 26 repeated rows) and job 3 which will have 4 rows. Any help would be greatly appreciated.

推荐答案

以防万一有人好奇的我已经解决了这个问题.首先，我发现可以为每个作业1，作业2和作业3表中的每一行数据添加一个ROW ID，并使其不会超过顶部表中所述的人数:

Just in case anybody is curious I have solved this. First of all I figured out that I could add a ROW ID for each row of data in each job 1, job 2 and job 3 tables and make it so that it would not go over the amount of people stated in the top table like this:

       A                 B                     C
1    Job Role|Amount of people in the role |
2    Job 1   |            6                |
3    Job 2   |            26               |
4    Job 3   |            4                |
5
6    Job 1 
7    ID      |Name                          |Job Role        
8    1       |t                             |
9    2       |b                             |
10   3       |a                             |
11   4       |s                             |
12   5       |d                             |
13   6       |f                             |

要使数字升至6，而不是超过该数字，我使用了此公式(单元格A8)在员工被带走或加班的情况下自动为该行提供ID:

To get the number to go to 6 and not go over this, i used this formula (IN CELL A8) to automatically give the row an ID if an employee is taken away or added:

=IF(ROW()-7>B2,"",ROW()-7)

该语句基本上说:如果行号-7比B2高(表中为6)，则留一个空格；如果行号为LOWER，则B2然后返回行号-7 = 1，当下拉菜单将一直持续到6，然后开始留空白，除非添加了Job 1职位的另一名雇员，以防万一，该职位将上升到7.

This statement basically says "if the row number -7 is HIGHER than B2 (6 in the table), then leave a blank space, if the row number is LOWER then B2 then return the row number -7 = 1 and when pulled down this will carry on until 6 and then start to leave blank spaces unless another employee of the Job 1 job role is added which in case case it will go up to 7.

要返回工作角色，当我有ID号时，这很容易，我在单元格C8中使用的语句是:

To return the job role this was pretty easy when I have the ID numbers, the statement I used in cell C8 was:

=IF(A8<>"",A6,"")

这基本上意味着，如果ID列中包含某些内容，则返回职位角色"，这样我就得到了想要的内容:

This basically means that if the ID column has something in it then return the Job Role so I end up with what I want:

Job 1
ID  | Name  |Job Role
1   |   T   |Job 1
2   |   B   |Job 1
3   |   A   |Job 1
4   |   S   |Job 1
5   |   D   |Job 1
6   |   F   |Job 1

这篇关于如何在Excel中基于另一个单元格的值将值重复到行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！