记录5列出现5次的每个值的名称 [英] Record the name of each value that appears 5 times in 5 columns

问题描述

我有五列书名。我正在寻找一个公式，将记录第6列中所有5列中的任何图书的名称。

目前，我可以使用以下两列进行操作，但是我无法将其扩展到多个列，并设置了最小阈值。

  = IF（ISERROR（MATCH（A4，$ B $ 4：$ B $ 329,0）），，A4） 
  

如果有人可以帮助，将非常欣赏

数据：

  ABCDE 
 
 Book 1 Book 1 Book 1书3书1 
书4书2书2书23书4 
书6书5书5书14书23 
书9书22书3书2书17 
 Book 3 Book 6 Book 7 Book 6 Book 2 
 Book 7 Book 27 Book 14 Book 15 
 Book 2 Book 6 Book 4 Book 6 
 Book 23 Book 13 Book 66 Book 9 
 Book 34 Book 32 
 Book 17 
 Book 18 
  

预期结果

  ABCDE答案
 
 Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 
 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 
 Book 6 Book 5 Book 5 Book 14 Book 23 
 Book 9 Book 22 Book 3 Book 2 Book 17 
 Book 3 Book 6 Book 7 Book 6 Book 2 
 Book 7 Book 27 Book 14 Book 15 
 Book 2 Book 6 Book 4 Book 6 
 Book 23 Book 13 Book 66 Book 9 
 Book 34 Book 32 
 Book 17 
 Book 18 
  

替代方法是突出显示至少5次的所有值。

解决方案

对于公式：

  = IFERROR（INDEX $ 1：$ E $ 11 AGGREGATE（14,6，ROW（$ A $ 1：$ E $ 11）/（（COUNTIF（$ A $ 1：$ E $ 11 $ A $ 1：$ E $ 11）= COLUMNS（$ A $ 1： $ E $ 11））*（COLUMN（$ A $ 1：$ E $ 11）= 1）），ROW（1：1）），1），）
  

一个警告。每列的标题必须是唯一的。

I have five columns of book names. I am looking for a formula that will record the name of any book that is in all 5 columns in the 6th column.

Currently I am able to do it for two columns using the below but I am having trouble extending it to multiple columns and setting a minimum threshold on it.

=IF(ISERROR(MATCH(A4,$B$4:$B$329,0)),"",A4)

If anyone can help it would be much appreciated

Data:

A             B             C            D            E

Book 1        Book 1        Book 1       Book 3       Book 1
Book 4        Book 2        Book 2       Book 23      Book 4
Book 6        Book 5        Book 5       Book 14      Book 23
Book 9        Book 22       Book 3       Book 2       Book 17
Book 3        Book 6        Book 7       Book 6       Book 2
Book 7                      Book 27      Book 14      Book 15
Book 2                      Book 6       Book 4       Book 6
Book 23                     Book 13      Book 66      Book 9
                            Book 34      Book 32
                                         Book 17
                                         Book 18

Expected Result:

A             B             C            D            E            Answer

Book 1        Book 1        Book 1       Book 3       Book 1       Book 1
Book 4        Book 2        Book 2       Book 23      Book 4       Book 6
Book 6        Book 5        Book 5       Book 14      Book 23
Book 9        Book 22       Book 3       Book 2       Book 17
Book 3        Book 6        Book 7       Book 6       Book 2
Book 7                      Book 27      Book 14      Book 15
Book 2                      Book 6       Book 4       Book 6
Book 23                     Book 13      Book 66      Book 9
                            Book 34      Book 32
                                         Book 17
                                         Book 18

The alternative would be to highlight all values that appear at least 5 times.

解决方案

For a formula:

=IFERROR(INDEX($A$1:$E$11,AGGREGATE(14,6,ROW($A$1:$E$11)/((COUNTIF($A$1:$E$11,$A$1:$E$11)=COLUMNS($A$1:$E$11))*(COLUMN($A$1:$E$11)=1)),ROW(1:1)),1),"")

One caveat. The titles must be unique per column.

这篇关于记录5列出现5次的每个值的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！