按照如下步骤即可在Excel中批量查找并提取数据(以提取姓名和金额为例):
1、首先在excel中插入一个工作表,在“金额”下方的单元格中输入“=VLOOKUP()”。
2、然后输入第一个参数:lookup_value,要查找的值,即名字下方第一个单元格,在括号中输入D2,即“=VLOOKUP(D2)”
3、然后输入第二个参数:table_array,即查找数据的区域范围,就是A列到B列所有的数据,选中即可,或者也可以输入A:B,即“=VLOOKUP(D2,A:B)”。
4、然后输入VLOOKUP函数第3个参数:col_index_num,即要返回的值在查找区域中的列号,金额位于查找区域的第2列,所以输入:2,即“=VLOOKUP(D2,A:B,2)”。
5、然后输入VLOOKUP函数第4个参数:range_lookup,需要返回值的精确匹配,输入0或者FALSE即可,这里输入0,即“=VLOOKUP(D2,A:B,2,0)”。
6、最后按下回车键,下拉单元格复制公式,批量查找并提取数据就设置完成了。
今天跟大家分享一下Excel如何把符合多条件的多条数据全部提取出来。
材料/工具
Excel
方法
1/7
如图数据,现在想要将所有一年级2班的学生名单及成绩提取出来。
请点击输入图片描述
2/7
在F2单元格输入公式=INDEX(C:C,SMALL(IF(($A$2:$A$36=$F$1)*($B$2:$B$36=$G$1),ROW($2:$36),4^8),ROW(A1)))&""然后按下Ctrl+shift+enter三键结束,最后下拉填充直至出现空白单元格为止。
请点击输入图片描述
3/7
在G2单元格输入公式=INDEX(D:D,SMALL(IF(($A$2:$A$36=$F$1)*($B$2:$B$36=$G$1),ROW($2:$36),4^8),ROW(B1)))&""同样按下Ctrl+shift+enter三键结束,最后下拉填充直至出现空白单元格为止。这样就把1年级1班的学生名单及成绩全部提取出来了。
请点击输入图片描述
4/7
首先是if函数,它是Excel中常用的条件函数,根据指定的条件来判其“真”(TRUE)、“假”(FALSE),根据逻辑计算的真假值,从而返回相应的内容。可以使用函数 IF 对数值和公式进行条件检测。
请点击输入图片描述
5/7
IF(($A$2:$A$36=$F$1)*($B$2:$B$36=$G$1),ROW($2:$36),4^8)在本文中的意思就是当年级为1年级且班级为2班时,返回其对应的行号,否则返回4^8的结果65536。最后得到一个有大量数字组成的内存数据组,为了方便大家理解我将这组数据放到E列中,如下图。
请点击输入图片描述
6/7
SMALL函数和row函数,small函数作用是返回数据第K个最小值,row函数则是返回参数的行号。SMALL(IF(($A$2:$A$36=$F$1)*($B$2:$B$36=$G$1),ROW($2:$36),4^8),ROW(A1))则是依次返回上一步所说的内存数据组的第1,第2,第3……个最小值。在单元格中输入=SMALL($E$2:$E$36,ROW(A1))其返回结果如下图。
请点击输入图片描述
7/7
INDEX函数,它是返回表或区域中的值或值的引用。它的语法结构是INDEX(单元格区域,行号,列号),因此他在本文中的作用就是返回small函数提供的行号所对应的姓名或者成绩。在单元格中输入公式=INDEX(C:C,F2),其返回结果如下图。
请点击输入图片描述
怎样在Excel中每隔N行提取数据没有提具体要求,只能先假设一下,比如提取A1:D100中,从第1行开始,每隔2行(第3行提取一行数据)的数据,即提取A1:D1、A4:D4,等等。公式:
=INDEX($A$1:$D$1000,ROW()*3-2,COLUMN(A:A))
右拖、下拉。
自己根据实际数据修改公式吧。
PS:以后提问题把问题描述准确点,别人帮你设计的公式就直接可用。
方法有很多种,给你一种最简单的。用排序的方式。我以5为例。
在最前面插入一列。(此时A列是新插入的)
在第一个值所在的行(比如第一行),A1输入1,鼠标移动的A1右下角,鼠标变成实心十字,按住Ctrl+左键向下拖动(等差数列填充),填充到5。复制复制A1到A5的内容,选中A列其他部分。右键,选择性粘贴,所有使用源主题的单元。
排序,按A列排序。
原理是A列为1到N的循环数列,再对A列排序或筛选。
采用的是排序的方式,会破坏原有的排序,如果要保留,可以再插入一行,输入形成输入1,向下拉,形成序列号,完成后,对序列列排序,采用筛选,不需要这一步。
最后删除插入的列。
使用MOD函数组合实现隔行提取即可。
间隔n行数据求和通用公式
1.本例中的公式还可以进一步简化如下:
=SUMPRODUCT((MOD(ROW(2:16),3)=2)*A2:F16)
2.如果需要求数据区域每间隔3行的数据之和,则可以使用如下公式:
=SUMPRODUCT((MOD(ROW(A2:F16),4)=MOD(ROW(A2),4))*A2:F16)
如果需要求数据区域每间隔4行的数据之和,则可以使用如下公式:
=SUMPRODUCT((MOD(ROW(A2:F16),5)=MOD(ROW(A2),5))*A2:F16)
由此可以得出求数据区域每间隔n行的数据之和的通用公式为:
=SUMPRODUCT((MOD(ROW(数据区域),n+1)=MOD(数据区域起始行号,n+1))*数据区域)
同理,可以得出求数据区域每间隔n列的数据之和的通用公式为:
=SUMPRODUCT((MOD(COLUMN(数据区域),n+1)=MOD(数据区域起始列号,n+1))*数据区域)
利用每隔10行提取数据
可以利用indirect函数
=indirect("A"&(row(A1)-1)*10+1
筛选I列的“报告编号”再把编号取出
也可以用VBA提取
怎样在excel中每隔n行提取数据工具:office2007
目标:从A列隔行取数
方法:用offset()函数
实例见样表截图
比如隔1行取一个数据:
从输入
=OFFSET($A$1,2*(ROW(A1)-1),,)
公式下拉
隔5行取一个数据:
D2输入
=OFFSET($A$1,5*(ROW(A1)-1),,)
公式下拉
同理,隔N行取一个数据
=OFFSET($A$1,N*(ROW(A1)-1),,)
公式下拉
以上公式可以看到一个规律,只要改变N值,就能实现隔N行取一个数据。
用INDEX函数写公式可以完成。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)