时间戳

首页 > 解决方案 > INDEX returns wrong value and no value

excel - INDEX returns wrong value and no value

问题描述

I'm trying to find matching values and sort them in a column. Column A contains Names and Column B Training Done by person in Column A.

An example of my data can be seen here:

Data


Having that data, I want to get a separate table with two Columns(Name and training). In Column C, I want the names and in Column D I want the training done by who is in Column C. 

=IFERROR(INDEX($B$2:$B$7,SMALL(IF($C2=$A$2:$A$7,ROW($A$2:$A$7)-1,""),ROWS($D$1:D1))),"No training done")

I've tried using the above formula, but it seems to only work for the first entry in the table. I can't figure out what i should change.


This is what I get

Formula result

It shows "No training done" to people who have had training and there's a "0" where it should be "No training done".

This is my expected result:

Result

L.E : Anna did Excel. A mistake on my part in the above picture.
Also, I'm only interested in the Training Column, as I already got the hang of the formula to order the names.

Thank you.

标签： excelexcel-formula

解决方案

=INDEX(Training,AGGREGATE(15,6,1/(C2=Names)*ROW(Names),COUNTIF($C$2:$C2,$C2))-1)

在此处输入图像描述

在这种情况下，我使用了自定义格式的结果：;;"No Training Done";@

但是，如果您不介意更长的公式，您可以使用IF函数No Training Done在公式不返回任何内容的情况下返回语句。

=IF(INDEX(Training,AGGREGATE(15,6,1/(C2=Names)*ROW(Names),COUNTIF($C$2:$C2,$C2))-1)=0,"No Training Done",INDEX(Training,AGGREGATE(15,6,1/(C2=Names)*ROW(Names),COUNTIF($C$2:$C2,$C2))-1))

此外，如果需要，您可以使用整列引用。如果这样做，请删除，-1因为您不必调整标题行。

如果您想使用Power Queryaka Get&Transform，只需将null替换为No Training Done然后按照您想要的顺序对结果进行排序即可。这一切都可以从 PQ Editor GUI 中完成，下面是 M 代码

M-代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}, {"Training", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"No Training Done",Replacer.ReplaceValue,{"Training"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Names", Order.Ascending}})
in
    #"Sorted Rows"

推荐阅读