英文:
Create List Based on Highlighted Values
问题
I would like the chemical names of all red highlighted values (which are values above the screening value) to be put into a cell as a list.
例如，如果砷、钙和硝酸盐的值高于筛选值，则我希望一个单元格自动填充=砷、钙和硝酸盐。
不管标有1、2或3的列中的突出显示的值是全部突出显示的还是只有一个，我都希望捕获至少有一个值为红色的化学名称。
英文:
In my example, I would like the chemical names of all red highlighted values (which are values above the screening value) to be put into a cell as a list.
For example, if arsenic, calcium, and nitrate are above the screening value, I would like a cell to auto populate =arsenic, calcium, and nitrate.
It doesn't matter if the highlighted value in columns labeled 1,2, or 3 are all highlighted or only one. I would like to capture any chemical names where at least one value is red.
| Screening<br>Value | 1 | 2 | 3 | ||
|---|---|---|---|---|---|
| Chloroform | ug/L | 1.9 | 0.5 | 1 | |
| Chloromethane | ug/L | 7000 | 0.5 | 2 | |
| 4-Nitrophenol | ug/L | 0.016 | 5.1 | 4.5 | |
| Benzo(a)anthracene | ug/L | 0.03 | 0.3 | 0.27 | |
| Benzo(a)pyrene | ug/L | 0.0071 | 0.2 | 0.2 | 0.18 |
| Benzo(g,h,i)perylene | ug/L | 250000 | 0.51 | 0.45 | |
| Dibenz(a,h)anthracene | ug/L | 70 | 0.51 | 0.45 | |
| Hexachloroethane | ug/L | 0.024 | 3 | 2.7 | |
| N-NitrosodimethyIamine (NDMA) | ng/L | 70 | 14 | 2 | 6.3 |
| N-Nitroso-di-n-propyIamine (NDPA) | ng/L | 140 | 10 | 10 | |
| Phenanthrene | ug/L | 0.025 | 0.2 | 0.18 | |
| Aluminum | ug/L | 1400 | |||
| Arsenic | ug/L | 0.61 | |||
| Calcium | mg/L | 0.0081 | 29 | 30 | |
| Chromium-Hexavalent | ug/L | 1400 | |||
| Cobalt | ug/L | 300 | |||
| Copper | ug/L | 140 | |||
| Iron | ug/L | 11000 | |||
| Manganese | ug/L | 700 | |||
| Nickel | ug/L | 0.00024 | |||
| Sodium | mg/L | 0.0035 | 40 | 26 | |
| Chloride | mg/L | 20000 | 78 | 36 | |
| Nitrate | mg/L | NA | 1.5 | 1 | |
| Nitrite | mg/L | NA | 0.01 | 0.01 | |
| Acetaldehyde | ug/L | NA | 100 | 30 | |
| Formaldehyde | ug/L | NA | 9.1 | 30 | |
| Hydrazine | ug/L | NA | 0.05 | 0.05 |
答案1
得分: 3
Sure, here are the translated parts:
尝试使用 TEXTJOIN( ), FILTER( ) & MMULT( )
• 单元格 H2 中使用的公式：
=TEXTJOIN(", ",,FILTER(A2:A28,MMULT((D2:F28<>"")*(D2:F28>C2:C28),SEQUENCE(COLUMNS(D2:F28),,,0))))
如果只有3列，可以使用以下公式：
• 单元格 H4 中使用的公式：
=TEXTJOIN(", ",,FILTER(A2:A28,MMULT((D2:F28<>"")*(D2:F28>C2:C28),{1;1;1})>0))
或者，您还可以通过在 MMULT( ) 中添加另一个函数 TOCOL( ) 来使其动态化：
=TEXTJOIN(", ",,FILTER(A2:A28,MMULT((D2:F28<>"")*(D2:F28>C2:C28),TOCOL(D1:F1)^0)>0))
另一种替代方法，您不需要 LAMBDA( ) 辅助函数。以下公式确保了如此，以便说的 DROP( ) 始终从范围 C2:F28 中获取第一列，而 TAKE( ) 确保排除第一列并获取其余列。 REPT( ) 返回重复列的文本值范围，TOCOL( ) 将重复的文本数组转换为单列。 UNIQUE( ) 和 TEXTJOIN( ) 完成了工作。
• 单元格 H2 中使用的公式：
=LET(α,C2:F28,
TEXTJOIN(", ",1,UNIQUE(TOCOL(REPT(A2:A28,N(DROP(α,,1)>TAKE(α,,1)))))))
使用 MMULT( )，
=LET(α,C2:F28,TEXTJOIN(",",,FILTER(A2:A28,MMULT(N(DROP(α,,1)>TAKE(α,,1)),{1;1;1}))))
英文:
Try using <kbd>TEXTJOIN( )</kbd>, <kbd>FILTER( )</kbd> & <kbd>MMULT( )</kbd>
• Formula used in cell <kbd>H2</kbd>
=TEXTJOIN(", ",,FILTER(A2:A28,MMULT((D2:F28<>"")*
(D2:F28>C2:C28),SEQUENCE(COLUMNS(D2:F28),,,0))))
If you have only 3 Columns then use this one.
• Formula used in cell <kbd>H4</kbd>
=TEXTJOIN(", ",,FILTER(A2:A28,MMULT((D2:F28<>"")*(D2:F28>C2:C28),{1;1;1})>0))
Or, you can make this dynamic as well by adding another function within the <kbd>MMULT( )</kbd> using <kbd>TOCOL( )</kbd>
=TEXTJOIN(", ",,FILTER(A2:A28,MMULT((D2:F28<>"")*(D2:F28>C2:C28),TOCOL(D1:F1)^0)>0))
Another alternative approach, you don't need a <kbd>LAMBDA( )</kbd> helper functions. The following formula ensures that, so that said <kbd>DROP( )</kbd> ensures to take always the first column from the range <kbd>C2:F28</kbd> while <kbd>TAKE( )</kbd> ensures to exclude the first column and take the rest. <kbd>REPT( )</kbd> returns a range of text values from column repeated, <kbd>TOCOL( )</kbd> transforms the repeated text arrays into a single column. <kbd>UNIQUE( )</kbd> and <kbd>TEXTJOIN( )</kbd> does the finishing work.
• Formula used in cell <kbd>H2</kbd>
=LET(α,C2:F28,
TEXTJOIN(", ",1,UNIQUE(TOCOL(REPT(A2:A28,N(DROP(α,,1)>TAKE(α,,1)))))))
With <kbd>MMULT( )</kbd>,
=LET(α,C2:F28,TEXTJOIN(",",,FILTER(A2:A28,MMULT(N(DROP(α,,1)>TAKE(α,,1)),{1;1;1}))))
答案2
得分: 0
另一种具有两个输入的类似解决方案：
=TEXTJOIN("，", 1, FILTER(A2:A28, BYROW(C2:F28, LAMBDA(x, SUM(N(TAKE(x, , 1) < TAKE(x, , -3)))))))
英文:
Similar yet another solution with 2 inputs:
=TEXTJOIN(", ",1,FILTER(A2:A28,BYROW(C2:F28,LAMBDA(x,SUM(N(TAKE(x,,1)<TAKE(x,,-3)))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论