• excel的功能实在是太强大了，我们要认真学习她的每个公式
• excel模糊查找公式 Excel查找公式挑战20171026 (Excel Lookup Formula Challenge 20171026)Here's an Excel Lookup Formula challenge to get your brain fired up. Can you solve it without doing a Google search...

excel模糊查找公式

Here's an Excel Lookup Formula challenge to get your brain fired up. Can you solve it without doing a Google search? The problem details are shown below, and you can download the sample workbook. It has the sample data, and there are solutions too, on a different worksheet.
查找公式挑战 (The Lookup Formula Challenge)
The problem is based on an email question that someone sent to me recently. They had a solution, but it was a long, complicated formula. Could I come up with something that was more efficient? Well who can resist a challenge like that?
该问题基于某人最近发送给我的电子邮件问题。 他们有一个解决方案，但这是一个漫长而复杂的公式。 我能想出更有效的方法吗？ 那么谁能抵抗这样的挑战呢？
In columns A:B, there is a lookup table, with a list of customers and their ID codes.  在A：B列中，有一个查找表，其中包含客户及其ID码的列表。 In column D, there is imported data. There is a customer name in each cell, but it's not in a consistent place.  在D列中，有导入的数据。 每个单元格中都有一个客户名称，但是位置不一致。 Create a formula in column E, to get the correct customer code for each imported record.  在E列中创建一个公式，以获取每个导入记录的正确客户代码。

尝试挑战 (Try the Challenge)
To try the challenge, build your own sample based on the screen shot above, or go to the Excel Sample Files page on my Contextures website, and download my Excel file.
要尝试挑战，请根据上面的屏幕快照构建自己的示例，或者转到Contextures网站上的“ Excel示例文件”页面 ，然后下载我的Excel文件。
In the Functions section, look for FN0052 - Lookup Challenge Solutions
在“功能”部分中，查找FN0052-查找质询解决方案
In the workbook you'll find:
在工作簿中，您将找到：
Instructions  使用说明 challenge sheet  挑战表 sheet with clues  线索表 Solution sheets  解决方案表
What formula did you build? Was is similar to my original formula, or like one of the other, that my newsletter readers submitted?
您建立了什么公式？ 是我的时事通讯读者提交的与我的原始公式类似，还是与其他公式之一相似？
我的挑战解决方案 (My Solution to the Challenge)
I enjoyed solving the formula challenge, and came up with a formula that was much shorter than the original one that was sent to me. But, I figured that the smart people who read my Excel newsletter could find a solution that was better than mine!
我很喜欢解决公式挑战，并且想出了一个比发送给我的原始公式短得多的公式。 但是，我认为阅读我的Excel新闻通讯的聪明人可以找到比我更好的解决方案！
Scroll down if you'd like to see my solution, but stop reading, and come back later if you're still working on the challenge.
如果您想查看我的解决方案，请向下滚动，但请停止阅读，如果您仍在应对挑战，请稍后再回来。
.
。
.
。
.
。
.
。
.
。
.
。
.
。
.
。
.
。
Here is the formula that I used. It is array-entered, with Ctrl+Shift+Enter.
这是我使用的公式。 使用Ctrl + Shift + Enter进行数组输入 。
=INDEX($B$2:$B$4, MATCH(1,IFERROR( SEARCH("*"&$A$2:$A$4&"*",D2,1),0),0))
= INDEX($B$ 2：$B$ 4，MATCH(1，IFERROR(SEARCH(“ *”＆$A$ 2：$A$ 4＆“ *”，D2,1)，0)，0))
For a brief description of how the formula works, look on the SolutionsA sheet in the Lookup Challenge Solutions workbook.
有关公式如何工作的简要说明，请在“ 查找挑战解决方案”工作簿中的SolutionsA表上查找 。
非数组公式 (Non-Array Formulas)
As I expected, other people had better solutions for this problem. Some people used the LOOKUP function in their solutions, and those formulas didn't need to be array-entered. For example:
如我所料，其他人对此问题有更好的解决方案。 有些人在解决方案中使用了LOOKUP函数，而这些公式不需要进行数组输入。 例如：
=IFERROR(LOOKUP(2^15,SEARCH($A$2:$A$4,$D2),$B$2:$B$4),"") = IFERROR(LOOKUP(2 ^ 15，SEARCH($ A $2：$ A $4，$ D2)，$B$ 2：$B$ 4)，“”)
There are a few others on the SolutionsB sheet in the download file. And if you find a different solution, please share it in the comments below.
下载文件中的SolutionsB表上还有其他一些内容。 如果您找到其他解决方案，请在下面的评论中分享。
另一个公式解释 (Another Formula Explained)
Kevin Lehrbass enjoyed the challenge, and made a video to explain his solution. The sound is a bit low, so adjust your speaker, if you can't hear it clearly.
凯文·勒巴斯(Kevin Lehrbass)乐于接受挑战，并制作了一段视频来解释他的解决方案 。 声音有点低，如果听不清楚，请调整扬声器。
Here is Kevin's formula:
这是凯文的公式：
=INDEX($B$2:$B$4, MIN(IF(ISNUMBER(SEARCH($A$2:$A$4,D2)), ROW($A$2:$A$4)-ROW($A$2)+1,"")))
= INDEX($B$ 2：$B$ 4，MIN(IF(ISNUMBER(SEARCH($A$ 2：$A$ 4，D2))，ROW($A$ 2：$A$ 4)-ROW($A$ 2)+1 ，“”)))
Kevin said, "I had to add the ROW - ROW part (in case someone would insert a row above and break my array)."
凯文说：“我必须添加ROW-ROW部分(以防有人在其上方插入一行并破坏我的阵列)。”
In case you missed the download links earlier, here's how you can get the sample file. Go to the Excel Sample Files page on my Contextures website, and download my Excel file. In the Functions section, look for FN0052 - Lookup Challenge Solutions
如果您之前错过了下载链接，可以通过以下方法获取示例文件。 转到我的Contextures网站上的“ Excel示例文件”页面 ，然后下载我的Excel文件。 在“功能”部分中，查找FN0052-查找质询解决方案
___________________
___________________

___________________
___________________

翻译自: https://contexturesblog.com/archives/2017/10/26/excel-lookup-formula-challenge-20171026/

excel模糊查找公式

展开全文
• excel查找一列重复项 列中最后一项的Excel查找公式 (Excel Lookup Formula for Last Item in Column) How can you get the last number in an Excel column? I needed to do that in one of my sample files, so ...

excel查找一列重复项

How can you get the last number in an Excel column? I needed to do that in one of my sample files, so here's the formula that I used. Then, someone asked me how to get the last item (text or number) from a column, so there's a formula for that too.
如何获得Excel列中的最后一个数字？ 我需要在一个示例文件中执行此操作，因此这是我使用的公式。 然后，有人问我如何从列中获取最后一个项目(文本或数字)，因此也有一个公式。
带数字的栏 (Column with Numbers)
I'm updating my Excel Weight Tracker files, which were created long before Excel introduced named tables.
我正在更新我的Excel Weight Tracker文件 ，这些文件是在Excel引入命名表之前创建的。
Switching to tables has made it easier to show a summary on the dashboard. The old dynamic ranges aren't needed now - we can just refer to table columns instead.
切换到表格可以更轻松地在仪表板上显示摘要。 现在不再需要旧的动态范围-我们可以只引用表列。
The WeightData table, shown below, has columns where you enter the date and your weight every week.
如下所示，WeightData表具有用于输入日期和每周体重的列。

There is also a Dashboard sheet in the workbook, and I want the latest weight to show on the Dashboard.
工作簿中还有一个“仪表板”工作表，我希望最新的权重显示在仪表板上。
What formula would you use to show that latest weight on the Dashboard sheet? My formula is below, and you might use a different solution.
您将使用什么公式在“仪表板”表上显示最新重量？ 我的公式如下，您可能使用其他解决方案。
获取最后的号码 (Get the Last Number)
The new dates and weights are entered at the end of the table, so I used the LOOKUP function on the Dashboard, to get the last number from the Weight column.
新的日期和权重在表的末尾输入，因此我使用了仪表板上的LOOKUP函数 ，从“权重”列中获取了最后一个数字。
=LOOKUP(9.99999999999999E+307, WeightData[Wt])
= LOOKUP(9.99999999999999E + 307，WeightData [Wt])

那是多少钱？ (What's That Number?)
According to Excel's specifications, that strange number, 9.99999999999999E+307, is the largest number that can be typed into an Excel cell.
根据Excel的规范，该奇数9.99999999999999E + 307是可在Excel单元格中键入的最大数字。
The LOOKUP function won't find that in the weight column (I hope!), so the formula returns the last number it finds in that column.
LOOKUP函数不会在权重列中找到该数字(我希望！)，因此该公式将返回它在该列中找到的最后一个数字。
To see this formula, and all the others, get one of the Weight Tracker files - either the Pounds/Kilos version, or the Stone version.
要查看此公式以及所有其他公式，请获取一个Weight Tracker文件 -磅/公斤版本或石头版本。
提示–将该号码命名 (Tip – Name That Number)
Instead of putting that confusing number into the formula, you could define a name, using that value.
您可以使用该值定义一个名称 ，而不是将该混乱的数字放入公式中。
Then, put the "friendly" name into the formula, so you don't scare your co-workers.
然后，将“友好”名称放入公式中，这样您就不会吓到同事。
To create the name:
要创建名称：
Copy the number from the formula bar 从编辑栏中复制数字 On Excel's Formula tab, click Define Name 在Excel的“公式”选项卡上，单击“定义名称” Type a name, e.g. XL_Max 输入名称，例如XL_Max Leave the scope as Workbook 将范围保留为工作簿 Type a comment that explains what the number is 输入说明数字的注释 Right-click the Refers To box, and click Paste, then click OK 右键单击“引用到”框，然后单击“粘贴”，然后单击“确定”。

使用定义的名称 (Use the Defined Name)
After you define the name, use it in the formula.
定义名称后，在公式中使用它。
Select the cell with the LOOKUP formula, and click in the Formula bar. 选择具有“查找”公式的单元格，然后单击“公式”栏中。 Select the long number, and replace it with the defined name, XL_Max 选择长号，并将其替换为定义的名称XL_Max Press Enter, to update the formula 按Enter键更新公式

在我的Contextures网站上了解有关Excel名称的更多信息 。
杂项列 (Column with Assorted Items)
In the previous formula, the LOOKUP function get the last number from a column. But how would you write a formula to get the last text or number in a column, which has assorted items, including blank cells and errors?
在上一个公式中， LOOKUP函数从一列中获取最后一个数字 。 但是，您将如何编写一个公式来获取一列中包含各种项目(包括空白单元格和错误)的最后一个文本或数字 ？
样本数据 (The Sample Data)
Here's the sample data, and the result in cell D2 should be "yes".  That's the last entry which either either a number or text.
这是示例数据，单元格D2中的结果应为“是”。 那是数字或文本的最后一个条目。
What lookup formula would you use to get that result? My solution is below the screen shot.
您将使用什么查找公式来获得该结果？ 我的解决方案是在屏幕截图下方。

With values (and blank cells) in cells B1:B6, I used this LOOKUP formula to get the last entry that's text or a number:
对于单元格B1：B6中的值(和空白单元格)，我使用了此LOOKUP公式来获取最后一个文本或数字条目：
=LOOKUP(2,1/(B1:B6<>""),B1:B6)
= LOOKUP(2,1 /(B1：B6 <>“”)，B1：B6)
检查空字符串 (Check for Empty Strings)
Here's how the formula works.
这是公式的工作方式。
First, the formula tests each entry to see if it's "not equal to" an empty string (<>"").
首先，该公式测试每个条目以查看其是否“不等于”一个空字符串(<>“”)。
The result of that test is TRUE (1) or FALSE (0) for each cell in the range.
对于该范围内的每个单元，该测试的结果为TRUE(1)或FALSE(0)。

除以结果 (Divide with the Result)
Then, the number 1 is divided by each TRUE/FALSE.
然后，将数字1除以每个TRUE / FALSE。
TRUE is equal to 1, and 1/1=1 TRUE等于1，且1/1 = 1 FALSE is equal to zero, and 1/0 results in a #DIV/0! error FALSE等于零，1/0产生＃DIV / 0！ 错误 Any cells that contain an error will also return an error in this calculation 任何包含错误的单元格也会在此计算中返回错误

查找查询值 (Find the Lookup Value)
The lookup value is 2, which won't be found, because the highest number is 1.
查找值是2，将不会找到，因为最大的数字是1。
Since it can't find a 2, the formula finds the position of the last number 1, and returns the value ("yes") at that location, in the lookup range.
由于找不到2，因此该公式将找到最后一个数字1的位置，并在查找范围内返回该位置的值(“是”)。
获取样本文件 (Get the Sample File)
To see the first LOOKUP formula, which returns the last number in a column, get my my Excel Weight Tracker files. There are versions for pounds/kilos, or for stone.
要查看第一个LOOKUP公式(返回一列中的最后一个数字)，请获取我的Excel Weight Tracker文件 。 有磅/公斤或石头的版本。

翻译自: https://contexturesblog.com/archives/2019/05/16/excel-lookup-formula-for-last-item-in-column/

excel查找一列重复项

展开全文
• Excel公式-文本查找函数1、查找字符串中指定字符的起始位置SEARCH与SEARCHB函数FIND与FINDB函数2、返回字符串中指定位置的字符MID与MIDB函数3、替换字符串中指定位置的字符REPLACE与REPLACEB函数 1、查找字符串中...


Excel公式-文本查找函数
1、查找字符串中指定字符的起始位置SEARCH与SEARCHB函数FIND与FINDB函数
2、返回字符串中指定位置的字符MID与MIDB函数
3、替换字符串中指定位置的字符REPLACE与REPLACEB函数

1、查找字符串中指定字符的起始位置
SEARCH与SEARCHB函数
1)语法：search(find_text,within_text,[start_num]),其中[start_num]为非必需项 2)示例：返回“hello world world”中“world”首次出现的位置  输出结果：7 3)search与searchb的区别 searchb在遇到中文时，按两个字节计算，数字与字母按一个字节计算； search均按一个字节计算。  输出结果：10  输出结果：12
FIND与FINDB函数
SEARCH与SEARCHB函数大小写不敏感，如果要执行区分大小写的搜索，需使用FIND与FINDB函数。  输出结果：10
2、返回字符串中指定位置的字符
MID与MIDB函数
1）语法：mid(text,start_num,num_chars) 2）示例：返回“hello 世界 World”中从位置6开始的3个字符。  输出结果： 世界 （注：此处“世界”字符串前有一个空格，占据一个字符） 3）MID与MIDB函数区别 midb在遇到中文时，按两个字节计算，数字与字母按一个字节计算； mid均按一个字节计算。  输出结果： 世 （注：此处“世”字符串前有一个空格，占据一个字符）
3、替换字符串中指定位置的字符
REPLACE与REPLACEB函数
1） 语法：replace(old_text,start_num,num_chars,new_text) 2） 示例: 将“hello 世界 World”字符串中第7个字符后的2两个字符替换为“world"  输出结果：hello world World 3） REPLACE与REPLACEB函数 REPLACEB在遇到中文时，按两个字节计算，数字与字母按一个字节计算； REPLACE均按一个字节计算。  输出结果：hello world界 World
展开全文
• 今天要介绍的是，根据查找单元格内容来进行类别匹配，如图所示 MATCH函数 简介 匹配函数,指返回指定数值在指定数组区域中的位置。 用途： (1)确定列表中某个值的位置； (2)对某个输入值进行检验，确定这个值是否...
简介
今天要介绍的是，根据查找单元格内容来进行类别匹配，如图所示
MATCH函数
简介
匹配函数,指返回指定数值在指定数组区域中的位置。 用途： (1)确定列表中某个值的位置； (2)对某个输入值进行检验，确定这个值是否存在某个列表中； (3)判断某列表中是否存在重复数据； (4)定位某一列表中最后一个非空单元格的位置。 查找文本值时，函数 MATCH 不区分大小写字母。
语法：
MATCH(lookup_value, lookup_array, match_type)
参数简单说明数据类型备注lookup_value需要在数据表（lookup_array）中查找的值数值，文本，单元格引用可以包含通配符，*和？，星号可以匹配任何字符序列；问号可以匹配单个字符lookup_array可能包含有所要查找数值的连续的单元格区域一维数组区域必须是某一行或某一列match_type表示查询的指定方式-1、0或1，默认为1
1：查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列：否则，当遇到比lookup_value更大的值时，即时终止查找并返回此值之前小于或等于lookup_value的数值的位置。如果lookup_array的值均小于或等于lookup_value，则返回数组最后一个值的位置；如果lookup_array的值均大于lookup_value，则返回#N/A。  0：查找等于lookup_value的第一个数值，lookup_array按任意顺序排列；  -1：查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列；否则，当遇到比lookup_value更小的值时，即时终止查找并返回此值之前大于或等于lookup_value的数值的位置。如果lookup_array的值均大于或等于lookup_value，则返回数组最后一个值的位置；如果lookup_array的值均小于lookup_value，则返回#N/A。
使用：
查找E列，内容包含A2单元格的行号
=MATCH("*"&A2&"*",$E:$E,0)

逗号匹配
假设单元格内容以逗号区别项目，为了使相似值不互相影响（类似str和string），因此要进行区别匹配
//only 只有一个项目，例如单元格只有str一个字符
=IF(ISERROR(MATCH(A2,$E:$E,0)),"无",MATCH(A2,$E:$E,0))

//begin 项目在第一个位置，例如单元格内容为str，abc
=IF(ISERROR(MATCH(A2&",*",$E:$E,0)),"无",MATCH(A2&",*",$E:$E,0))

//mid 项目在中间位置，例如单元格内容为a，str，b
=IF(ISERROR(MATCH("*,"&A2&",*",$E:$E,0)),"无",MATCH("*,"&A2&",*",$E:$E,0))

//end 项目在末尾，例如单元格内容为a,str
=IF(ISERROR(MATCH("*,"&A2,$E:$E,0)),"无",MATCH("*,"&A2,$E:$E,0))

完整代码
=INDIRECT(ADDRESS(IF(ISERROR(MATCH(A3&",*",$E:$E,0)),IF(ISERROR(MATCH("*,"&A3&",*",$E:$E,0)),IF(ISERROR(MATCH("*,"&A3,$E:$E,0)),IF(ISERROR(MATCH(A3,$E:$E,0)),"无",MATCH(A3,$E:$E,0)),MATCH("*,"&A3,$E:$E,0)),MATCH("*,"&A3&",*",$E:$E,0)),MATCH(A3&",*",$E:$E,0)),CELL("col",$E1)-1))  展开全文 • excel常用函数公式及技巧搜集 从身份证号码中提取出生年月日 从身份证号码中提取出性别 从身份证号码中进行年龄判断 按身份证号号码计算至今天年龄 以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式 按... • 支持合并单元格，支持合并单元格运算公式查找满足条件行对应的数值，提取单元格文字公式、排名公式函数、求值相同的行列的和公式、求值相同的平均值等等。是Excel公式函数入门学习，Excel公式编写的方便工具。 ... • 工具文档，用于查找及学习使用函数公式，避免语法格式错误导致的困惑不解 • 全书共分为16章，其中包括：第1章Excel2010的基本操作、第二章函数和公式的基础知识、第三章财务类函数的应用、第四章逻辑类函数的应用、第五章文本类函数的应用、第六章日期与时间函数的应用、第七章查找与引用... • 如何使用vlookup+excel数组公式 完成逆向查找 • ## EXCEL函数公式集 热门讨论 2010-03-16 03:26:38 excel常用函数公式及技巧搜集 从身份证号码中提取出生年月日 从身份证号码中提取出性别 从身份证号码中进行年龄判断 按身份证号号码计算至今天年龄 以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式 按... • 根据某列查找数据 VLOOKUP Lookup HLOOKUP 使用情况 查看excel的系统帮助就可以了. 我的例子： =VLOOKUP(C4,上月券商!C:D,2,FALSE) 比较二列 =IF(COUNTIF($C:\$C,券商!C9)=0,券商!C9,"") excel处理数据...
• 本书从函数功能、函数格式、参数说明、注意事项、Excel 版本提醒、案例应用、交叉参考7 个方面，全面、细致地介绍了Excel 2016/2013/2010/2007/2003 中公式和函数的使用方法、实际应用和操作技巧。最后3 章还将公式...
• 今天和大家分享一组常用函数公式的使用方法，用心掌握这些函数，工作效率会大大提升。欢迎转发给更多有需要的人。一、相关概念(一)函数语法由函数名+括号+参数组成例： 求和函数：SUM(A1,B2,…) 。参数与参数之 间用...
• 先贴表 　 查第几次出现 只保留第1次出现得值-去重？ 　 查目标出现次数 查目标是否重复 查非空个数 　 　 　 　 countif 　 ... ...
• 公司内部某个部门要进行员工的工作效率的记录，但是要求通过EXCEL进行录入，最后算出综合效率，显示到另外一个表中。 条件： 1、手动录入日期、工号、品番、工时、数量。员工姓名：通过VLOOKUP找工号获得。 2...
•  在Excel中输入函数时是不区分大小写的，无论是大写还是小写，结束函数编辑时，Excel会自动转化为大写。对于函数中的参数，当参数是文本时，要用双引号引起来，单纯的数字不需要加双引号，除非这个数字是文本性数字...
• EXCEL里通配符 Excel 通常有两个模糊搜索通配符：星号"*"和问号"?"。 ?代表一个字符， *代表一个或多个字符，单独的*会代表任意内容 ~* 表示* ~？ 表示？ EXCEL
• 这个需求简单来说，就是从右向左查找字符出现的首个位置，或从...以下是各种公式：（以查找A1单元格中最后一个“@”为例）1.直线思路公式：=FIND("☆",SUBSTITUTE(A1, "@", "☆", LEN(A1)-LEN(SUBSTITUTE(A1, "@", ...
• 第21章 条件格式中使用函数 第22章 数据有效性中使用函数 第23章 透视表函数的综合应用 第24章 图表中使用函数公式
• 《实战技巧精粹：Excel2010函数与公式实战技巧精粹》分为5篇26章，函数导读篇介绍了函数与公式基础知识、数组公式入门、名称的使用等内容；函数常用技巧篇介绍了逻辑与信息函数、文本处理函数、数学计算函数、日期与...
• ExceLint是一个Excel加载项，可以自动在电子表格中查找公式错误。 它是和Microsoft Research的研究产品。 由（Williams College），（ （UMass Amherst /微软研究院）和（ （微软研究院）。 谈ExceLint ，Dan ...
• ## Excel函数公式大全以及用法

万次阅读 多人点赞 2018-09-07 10:25:23
首先得说明下excel如何输入函数公式的方法方法是：单击a4单元格（a4单元格的位置是第A列，第4行交叉的那个单元格，也即是我们彩色实例的那个位置），在键盘上输入 =a1+a2+a3 ，然后按键盘的enter键（回车键）即可...
• 批量插入公式批量修改公式快速查找公式显示公式部分计算结果保护公式隐藏公式显示所有公式把公式转换成值显示另一个单元格的公式把公式表达式转换为结果快速查找公式中的错误1批量输入公式选取要输入的区域，在编辑...
• 查找函数 1. vlookup函数 vlookup(lookup_value,table_array,col_index_num,[range_lookup]) lookup_value表示要查找的对象 table_array表示查找的表格区域 col_index_num表示要查找的数据在table_array区域中处于...
• 对函数需要提升，对函数感兴趣，工作中需要用到Excel函数，想系统掌握函数，提高逻辑分析问题能力，适合工作中任何岗位职业。 15章节，62节课，10小时候函数干货视频教程，从基础到提供系统学习函数，从此之后告别...
• lookup公式用于通过查询值从一个表中查找想要的对应值。例如利用学生的名字从成绩单中查找他对应的分数。 常规用法 Lookup(查询值，查询列表，返回列表） 如图所示AB两列为省份和其对应的代码。在E2位置的公式为=...
• 点击上方△蓝字可关注我们会计人2019职场充电站坚持阅读... Excel最常用快捷键 8大类常用公式 一.数字处理1取绝对值=ABS(数字)2数字取整=INT(数字)3数字四舍五入=ROUND(数字,小数位数)二.判断公式4把公式返回的错误...
• 查找引用函数主要有match、lookup、vlookup、hlookup这4种，这几个常用的... * 主要特点： EXCEL公式类型：查找公式 * 版本号：1.0 * 制作人：xxx * 创建时间：2013-12-3 **/ package EXCEL; import java.util.
• excel如何与VLOOKUP函数相反的左向查找公式.docx

...