How do you use the function to automatically display the name of the head of the household in the fr

Since the position of the name of the head of household in your table is not fixed (not the first name of each household), it is not possible to use the formula for batch processing. It is recommended to open the ** you need, enter the VBA machine (alt + f11), select the insert-module, paste the following **, and run it. Note:

**The format must be the same as shown in the image above. If Excel doesn't allow you to run macros, go to Excel's Tools-Options-Security-Macro Completeness and lower the security.

sub Add the name of the head of household ().

dim guanxi as string, xingming as string

c3].select

guanxi =

while guanxi <>""

while guanxi = "Head of household"

xingming = , 1).value, -2).select

xingming

2).select

guanxi =

0).select

guanxi =

wendend sub

Select the blank area in column A and enter the formula:

if(c3="Head of household",b3,"")

Press Ctrl+Enter.

Select the second row, click Filter, and then click the Relationship with Household Head cell to filter, filter the filter condition "Household Head", select the names of all household heads and copy them to the Household Owner Name column.

Enter =if(c2=) directly in cell d2"Head of household",b2,""), then plant the filling, and you're good to go.

D2 input.

lookup(1,0/(($a$2:a2=a2)*(c$2:c2="Head of household")),b$2:b2)

Drop-down formula.

You first list a complete data corresponding to the format on your figure, according to the department and job title** (assuming the table name is sheet1, the department is in column A, the title is in column B, the basic performance is in column C, and the reward performance is in column D, and write out the basic performance and reward performance corresponding to all departments and titles).

Then you can enter the formula array formula in the E2 cell of ** in your figure (that is, the next row of the basic performance), press the shift+ctrl+enter three keys to end the input, pull the copy formula to the F2 cell, and then pull down the copy formula together.

Steps to Resolve:

1.First, build a performance comparison table for the title of each department, and assume that the comparison table is a sheet1 table, as shown in the following figure:

2.Assuming that the table provided by the landlord is sheet2, enter the formula: in e2

sumproduct((sheet1!$a$2:$a$100=$c2)*(sheet1!$b$2:$b$100=$d2)*sheet1!c$2:c$100)

Pull right fill, pull down fill.

First: Use the sumif function:

sumifs(e:e,b:b,h2,c:

c,i2,d:d,j2)=sumifs(f:f,b:

b, h2, c:c, i2, d:d, j2)

Use the vlookup function:

The array formula ends with alt+shift+enter].

The array formula ends with alt+shift+enter].

It is recommended to use the first type, when the data of the array formula is large, the operation will be stuck.

The question typed so many words or don't know what your results are asking for?

It's simple to meet the name, and you can directly use the vlook function nine if you have a list of names.

Yes, take a screenshot of the data area, including the row and column numbers.

Just write it in pinyin order.,This can be modified.。

It has been verified that it is possible to change the formula a1 to t1 in the question, but pay attention to add an absolute reference to the column, i.e. t1 should be changed to $t1, because when the formula is pulled right, t1 will become u1, v1, w1, etcBy adding an absolute reference symbol to a column, t1 will remain unchanged.

With the ton, as the separator, the formula:

trim(mid(substitute($t1,"、",rept(" ",50)),column(a1)*50-49,50))

Take the oblique bar separator, formula:

trim(mid(substitute($t1,"/",rept(" ",50)),column(a1)*50-49,50))