尝试创建一个报告模板,内容如下:
- 如果“未报告”则为空白
- 如果“未检测到”,则“语句 1”
- 如果值介于 10 和 19 之间,则为“语句 2”
- 如果值大于 19,则“语句 3”
=IF(INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0))="Not Reported","",IF(INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0))="None Detected",CONCATENATE(Sheet1!E11," was not detected in the submitted ",INDEX(Sheet1!G12:G42,MATCH(LRN,Sheet1!B12:B42,0))," sample."),IF(AND((INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0)))>=10,(INDEX(Sheet1!E13:E42,MATCH(LRN,Sheet1!B12:B42,0)))<=19),CONCATENATE(Sheet1!E11," was detected below the level of quantitation in the submitted ",INDEX(Sheet1!G12:G42,MATCH(LRN,Sheet1!B12:B42,0))," sample.")), IF(INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0))>19,CONCATENATE("The submitted ",INDEX(Sheet1!G12:G42,MATCH(LRN,Sheet1!B12:B42,0))," sample contained ",TRUNC(INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0)),0)," ",Sheet1!G11," of ",Sheet1!E11,"."))))
我对此还很陌生。任何帮助我都会很感激。谢谢!
答案1
尝试使用下面的方法,您得到了一个,
错误)
的结果。给出其中一个IFs
3 个结果而不是 2 个。
=IF(INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0)) = "Not Reported" ,"", IF(INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0)) = "None Detected" ,CONCATENATE(Sheet1!E11," was not detected in the submitted ",INDEX(Sheet1!G12:G42,MATCH(LRN,Sheet1!B12:B42,0))," sample."), IF(AND((INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0)))>=10, (INDEX(Sheet1!E13:E42,MATCH(LRN,Sheet1!B12:B42,0)))<=19), CONCATENATE(Sheet1!E11," was detected below the level of quantitation in the submitted ",INDEX(Sheet1!G12:G42,MATCH(LRN, Sheet1!B12:B42,0))," sample."), IF(INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0))>19,CONCATENATE("The submitted ",INDEX(Sheet1!G12:G42,MATCH(LRN,Sheet1!B12:B42,0))," sample contained ",TRUNC(INDEX(Sheet1!E12:E42,MATCH(LRN,Sheet1!B12:B42,0)),0)," ",Sheet1!G11," of ",Sheet1!E11,"."))))
并听取 Scotts 的建议,使用辅助列来构建初始列IFs
,一旦一切正常,您就可以随时合并它们。