EXCEL:从A列复制单元格 - >只有如果B列= X,但只使用1列 [英] EXCEL: Copy cells from column A -> C ONLY IF column B = X, but only using 1 column
问题描述
我有2列数据:
COL_A COL_B
AAAA PM
BBBB PM
CCCC P
DDDD M
EEEE PM
我想创建一个第三列通过过滤列A给定列B =PM。但是我想通过添加一个附加列(即没有逐步/多列方法)来实现。结果将如下所示:
COL_A COL_B COL_C
AAAA PM AAAA
BBBB PM BBBB
CCCC P EEEE
DDDD M
EEEE PM
a href =https://stackoverflow.com/q/13470007/4581200>需要Excel公式:查看文本字符串第二列中是否出现一列中的文本,讨论(模糊)如何执行此操作
我正在寻找一种(非VBA)方法,只用B过滤A 1行功能(即电子表格中只有1个附加列)
- 换句话说,我不要添加一些函数
= ___(A,B,...)
的列C,然后用一些函数= if(C__。 ..)
基于新的列C等。
我可以这样做吗? >
在 C1 中输入数组公式:
= IFERROR(INDEX($ A $ 1:$ B $ 20,SMALL $ B $ 1:$ B $ 20 =PM,ROW($ B $ 1:$ B $ 20)),ROW(1:1)),1),)
/ pre>
并复制:
必须使用 Ctrl + Shift + 输入 b b
数组公式 kbd>而不仅仅是输入键。
您可以增加公式中的范围以匹配表格大小。 em>
编辑#1:
函数根据行从列中提取数据。 THE SMALL()函数创建一个符合所需条件的行数组。
I have 2 columns of data:
COL_A COL_B AAAA PM BBBB PM CCCC P DDDD M EEEE PM
I want to create a 3rd column by filtering Column A given that Column B = "PM." But I want to do so by only adding that one additional column (i.e., no stepwise/multi-column approach). The result would look like this:
COL_A COL_B COL_C AAAA PM AAAA BBBB PM BBBB CCCC P EEEE DDDD M EEEE PM
The post, Excel formula needed: see if a text in one column appears within a text string second column, discusses (vaguely) how to do this using multiple columns along the way.
I am looking for a (non-VBA) approach to filter A by B using only 1 line of functions (i.e., only 1 additional column in the spreadsheet).
- In other words, I don't want to add column C with some function
=___(A,B,...)
and then column D with some function=if(C__...)
based on the new column C, etc.Can I do this?
解决方案In C1 enter the array formula:
=IFERROR(INDEX($A$1:$B$20,SMALL(IF($B$1:$B$20="PM",ROW($B$1:$B$20)),ROW(1:1)),1),"")
and copy down:
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.
You can increase the range in the formula to match your table size.
EDIT#1:
The INDEX() function pulls data from a column based on row. THE SMALL() function creates an array of rows matching the desired criteria.
这篇关于EXCEL:从A列复制单元格 - >只有如果B列= X,但只使用1列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!