Excel-具有IF功能的列表中的模态值 [英] Excel - Modal value in list with IF function

问题描述

我想在列表中找到最常用的值，并按名称匹配最常用的结果.

我知道它围绕着带有模式和匹配功能的索引，其中带有IF功能.但是不能把它弄下来.

原始数据

  USER NAME项目詹姆斯·艾尔(James Ile)平底鞋詹姆斯·艾尔(James Ile)平底鞋迈克尔·潘特·拿铁(Michael Pane Latte)莉莉·威尔克冰镇饮品路易丝·科塞(Louise Coset)百万富翁脆饼露易丝·怀里(Louise Wyli)白色路易丝·威利(Louise Wyli)百万富翁脆饼露易丝·威利(Louise Wyli)英式早餐路易丝·科塞特奶酪吐司路易丝·怀里(Louise Wyli)白色路易丝·科塞特菠萝日落詹姆斯考特浓咖啡 

所需结果

  USER詹姆斯·艾尔(James Ile)平底鞋白色迈克尔·潘特·拿铁(Michael Pane Latte)莉莉·威尔克冰镇饮品路易丝·科塞特奶酪吐司路易丝·威利(Louise Wyli)平白詹姆斯考特浓咖啡 

解决方案

作为Excel公式的发烧友，并考虑到此问题并未吸引很多答复，我尝试结合使用 Mode.Mult 函数，索引和匹配函数.

1. Mode.Mult可从Excel 2010开始使用.模式功能可用于早期版本，但如果模式值有关联，则可能会出错.
2. Mode.Mult函数返回一个垂直数组，该数组是数据数组或数据范围中最频繁出现或重复的值.对于水平数组，请使用 TRANSPOSE(MODE.MULT(number1，number2，...)).如果存在多种模式，则将返回多个结果.由于此函数返回值数组，因此必须将其作为数组公式输入.参数可以是数字或名称，数组或包含数字的引用.如果数组或引用参数包含文本，逻辑值或空单元格，则将忽略这些值；否则，这些值将被忽略.但是，包含的值为零.

   I want to find the most common value in a list, matching the most common outcome by name.

   I know it revolves around an index with mode and match function, with an IF function in there. But can't get it down.

   Raw Data

   USER NAME       Items
   James Ile       Flat White 
   James Ile       Flat White 
   Michael Pane    Latte 
   Lily Wilk       Iced Drinks 
   Louise Coset    Millionaire Shortbread 
   Louise Wyli     Flat White 
   Louise Wyli     Millionaire Shortbread 
   Louise Wyli     English Breakfast 
   Louise Cosett   Cheese Toastie 
   Louise Wyli     Flat White
   Louise Cosett   Pineapple Sunset 
   James Court     Espresso 
   

   Desired Result

   USER    
   James Ile        Flat white
   Michael Pane     Latte
   Lily Wilk        Iced Drinks
   Louise Cosett    Cheese Toastie
   Louise Wyli      Flat white
   James Court      Espresso
   

   解决方案

   Being a Excel Formula's enthusiast and considering that this question has not attracted many responses, I tried to get results by using a combination of Mode.Mult Function, Index and Match Functions.

   1. Mode.Mult is available from Excel 2010 onwards. Mode Function can be used for earlier versions but may give error if mode values have a tie.
   2. Mode.Mult Function Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. For horizontal arrays, use TRANSPOSE(MODE.MULT(number1,number2,...)).This will return more than one result if there are multiple modes. Because this function returns an array of values, it must be entered as an array formula. Arguments can either be numbers or names, arrays, or references that contain numbers.If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.Mode.Mult Function
   3. By using IF conditions Mode.Mult function has been utilized for text strings.

   I have partially succeeded in getting desired results, in the sense if there are more than one occurrence of mode value, it will be indicated in the results. Notwithstanding this I am posting my answer with the hope that more experienced persons will guide and help in overcoming the shortcomings in my attempt.

   Results Array formula in C2 copied down

   =IFERROR(INDEX( $B$2:$B$13,INDEX(MODE.MULT(IF($A$2:$A$13=$A2,MATCH($B$2:$B$13,$B$2:$B$13,0))),ROWS($C2:C2))  ),"")
   

   confirmed with Ctrl+Shift+Enter, not just Enter.

   这篇关于Excel-具有IF功能的列表中的模态值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！