-
2016-03-04 16:08:16
iamlaosong文
工作表的一个区域中有很多值,我们很容易用min或者max函数找出这个区域的最小值或者最大值,但是,要确定这个值的位置就比较难了,因为match函数或者vlookup(或者hlookup)函数都是对单行或者单列进行操作的。要确定这个值的位置,需要用到数组公式,我们看下面的数组公式:
{=ADDRESS(MAX(IF(A1:R18=MIN(A1:R18),ROW(A1:R18))),MAX(IF(A1:R18=MIN(A1:R18),COLUMN(A1:R18)))) }这个公式返回 A1:R18这个区域内最小值的位置,返回的结果可能类似这样:$H$4,就是这个最小值所在的单元格。下面分析一下这个公式的含义:最外层的address函数是把行列值转换为单元格地址的一般表示方式,address(4,8)=$H$4,所以,公式中的两个max函数返回的是最小值所在位置的最大行号和最大列号,其中if函数就是判断区域内每个单元格是否等于最小值,相等就返回行号或者列号,否则返回false,逐一比较的结果是返回多个值(区域中有多少个单元格就返回多少个值),然后max含返回其中最大的,当然也可以用min函数返回最小的。
上面的公式稍作分解,就可以返回行号:
{=MAX(IF(A1:R18=MIN(A1:R18),ROW(A1:R18)))}
或者返回列号
{=MAX(IF(A1:R18=MIN(A1:R18),COLUMN(A1:R18)))}
所谓的数组公式,就是指定范围内的多个数据逐一进行计算,然后在这些结果中挑选(比如max)或者合成(比如sum)为一个结果返回;或者事先选择多个单元格然后输入数组公式,最后返回多个结果保存在这些单元格中。
最后,数组公式在输入后,要以Ctrl+Shift+Enter来结束公式,这样结束的数组公式看起来比普通公式外面多一组{},也就是花式大括号。
更多相关内容 -
「Excel技巧」Excel快速实现将一行转为多行多列的四种方法
2021-01-17 16:58:09今天来说说在Excel中,将表格里的一列转换为多行多列的几种方法。例如,以下表格,是一个行业分类表,都放在同一列中。现我们准备把它转为多列。表格里数据除掉标题行行,总共有60列数据,干脆我们就给它转为10行6列...今天来说说在Excel中,将表格里的一列转换为多行多列的几种方法。
例如,以下表格,是一个行业分类表,都放在同一列中。现我们准备把它转为多列。
表格里数据除掉标题行行,总共有60列数据,干脆我们就给它转为10行6列吧。
那么,一列转多行多列,怎么转换才快速呢?
动手前,我们先找找规律吧。
首先,我们要知道,表格单元格数据区域为A2:A61,转为10行6列的表格后,原先的单元格地址对应转换后的表格存放位置,如下:
那么问题了,怎么样来调取对应单元格地址的数据呢?
以下四种方法,让你快速完成将一列转多行。
●查找替换法
●批量填充法
●借助word表格转换法
●用indirect函数转换法
现在分别来说一说这四种方法具体怎么使用。
一、查找替换法
1、先做好如下图表底
即在C2单元格输入A2,然后鼠标往右拖动填充至H2单元,使单元格从左往右生成A2、A4……A7数据;
用同样的方法,在C3:H3单元格从左往左路生成A8、A9……A13数据。
然后选中C2:H3这两行单元格区域,鼠标往下拖动至H11单元格,单元格即填充完毕。
2、将“A”替换成“=A”
表底做好后,我们利用查找替换法将表格里的“A”替换成“=A”。
3、将公式转化为数值
替换后,我们可以看到C2:H11单元格变成是用公式填充。
所以,在替换后,我们选中C2:H11单元格区域并复制,然后右键—选择性粘贴,将其粘贴为数值,就完了。
操作动态图:
二、批量填充法
1、先选取B2:F61单元格区域后,在编辑框里输入公式:=A12,然后按批量填充;
2、再将填充的公式粘贴为数值;
3、最后删除多余的行。
操作动态图:
技巧提示:因为是单列60行转为10行6列,原先A2:A11这10个单元格保持不变,所以B2:F61里的单元格就从=A12开始填充。
三、借助word表格转换
可以将Excel表格里的数据复制粘贴到word里,利用word进行转换,转换后再粘贴回excel表格中。
操作方法:
1、将Excel表格里的数据复制粘贴到word文档中,粘贴的时候记得选择【粘贴选项】中的【只保留文本】;
2、全选粘贴进word文档中的文本,点击word菜单的【插入】—【表格】—【文本转换成表格】;
3、在弹出的【将文字转换成表格】对话框里,将列数设置为6列,行数10行,最后点击【确定】即可。
4、最后,把word文档里转换完的表格复制粘贴到Excel中就完成了。
操作动态图:
四、用indirect函数转换
即选择C2:H11单元格区域,在编辑框里输入以下公式:
=INDIRECT("A"&6*ROW(A1)-5+COLUMN(A1))&""
然后按填充。
操作动态图:
用indirect函数转换的方法,我曾写过专门的文章详细介绍此方法,里面还有关于indirect函数的语法。不懂的童鞋,可以再去翻阅以前的文章。
文章为:
「Excel技巧」如何利用indirect函数快速将一列转为多行多列排版
关于Excel一列转多行多列的方法就介绍到这了。你最喜欢哪种方法?
-
EXCEL 怎么把一列数据转换为多行多列数据
2022-01-21 08:50:30目的:将一列120个数据转换为12行10列。 1,首先,在B1格输入“A1”,B2格输入“A13”,然后选中B1、B2,将鼠标移到选中框的右下角(此时鼠标变为“+”形,下同), ...4、找到上方工具栏中的“查找与选择”,单目的:将一列120个数据转换为12行10列。
1,首先,在B1格输入“A1”,B2格输入“A13”,然后选中B1、B2,将鼠标移到选中框的右下角(此时鼠标变为“+”形,下同),
按住左键不放将框下拉至B5处。此时B3、B4、B5格将分别填充“A25”、“A37”、“A49”、“A61、“A73、“A85、“A97、“A109”。
2、继续将鼠标放至右下角,按住右键不放将鼠标右拉至M列处,此时弹出对话框。在对话框中选择“填充序列”
3、将10行12列转置成12行10列
4、找到上方工具栏中的“查找与选择”,单击“替换”。在弹出的对话框中,查找内容中键入"A",替换内容键入"=A",单击"全部替换"。
5、至此完成转换。
-
EXCEL完成多条件多结果查找&多行多列变一列
2015-02-28 20:54:25其实我本来对EXCEL是一窍不通的,直到我舅舅给了一串大概1500多行的药品供应目录让帮他我查找。当他告诉他们平时都是花1~2天人工填写查找的时候,我当时就惊呆了,心想EXCEL设计者肯定已经哭晕在厕所,大家竟然都...其实我本来对EXCEL是一窍不通的,直到我舅舅给了一串大概1500多行的药品供应目录让帮他我查找。当他告诉他们平时都是花1~2天人工填写查找的时候,我当时就惊呆了,心想EXCEL设计者肯定已经哭晕在厕所,大家竟然都不用便捷的公式来完成,于是乎我就开始上手了。
首先应该明确需求,拥有一个包含药品名称、规格、供应商、价格的数据库A,表A如下。从表A中我们可以发现,同一药品,同一规格具有多个供应商,所以这一定是一个多结果查找。
需要根据医院需求的药品清单,补全一下清单表格B,表B如下。从表B中我们可以发现,B列所代表的通用名并不是该行数据的KEY,KEY是序列号,B列、C列、D列只不过是查找条件之一而已,需要根绝名称、剂型、规格等多个条件来查找企业信息、报价信息等。所以这是一个多条件查找过程。
以上我们明确了本次任务是完成多条件多结果查找,首先应该建立一新的工作环境(Work Space),简单的说就是新建一个EXCEL文件,并把两张表导入Sheet1、Sheet2分页中去,这样方便引用,是个良好的习惯。导入方法如下,右击左下角的Sheet1分页,选择移动或复制查找为了解决问题,我们先从简单的方面入手,多条件多结果查找首先需要完成查找的基本功能,查找所用的函数一般是VLOOKUP,意思是在一列(vertical)中找到(Look up)符合条件的第一个值并返回,语法规格为=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)譬如 =VLOOKUP($O4,Sheet3!$H:$I,2,0),其中$O4表示查找内容是O4单元框的内容,查找范围是 Sheet3!$H:$I,即表二的H到I列,2表示查找到相应行之后,返回O列右边第二列的内容,0是查找类型。符号$表示固定,因为随着公式下拉,数字4会依次增加,变为O4,O5,O5,而随着右拉列号会依次增加,变为O4,P4等,符号$的左右就保证右拉时列号O不会改变。多条件查找当查找结果为多个时,我们首先的想法可能是按照C语言,使用&符号,当然,可是肯定行不通的。正统方法是利用数组,这种方法先不理它...另一种方法更直接一些,建立一个辅助列,将多条件化为单条件,设需要满足的条件是E3、F3,我们只需要建立辅助列,令他的值为=E3&F3,满足该辅助列的单一条件就能实现满足多条件的需求。如下图所示例如H列,令H2=A2&C2,建立辅助列,对H列查找就能满足多条件查找的需求。多结果查找但是适用VLOOKUP函数只能返回第一个结果,不符合多结果查找的要求。为了解决多结果查找,我们需要适用 INDEX SMALL IF 组合函数。通过IF条件语句,遍历找到所有符合条件的行,并返回该行的序号=IF(Sheet2!$E2:$E1335=Sheet1!$F3,ROW(Sheet2!$E2:$E1335),4^8)其中Sheet2:$E2:$E1335=Sheet1!$F3是判断条件,在表二的E列中,找到等于表一中F3单元格内容的行,返回行号 ROW(Sheet2!$E2:$E1335),否则返回4^8=65535数值。找到所有符合条件的结果后,将他们排序,并按照行输出(右拉)=INDEX(Sheet2!$G:$G,SMALL(IF(Sheet2!$E2:$E1335=Sheet1!$F3,ROW(Sheet2!$E2:$E1335),4^8),COLUMN(A1)))&""其中SMALL仅仅对所有结果从小到大排序,而INDEX则将结果一一输出,按列输出COLUMN,按行输出则是ROW。先做个小实验,如下图。在A列中查找C1=1的值,D1输入公式=INDEX($B:$B,SMALL(IF($A$1:$A$1000=$C1,ROW($1:$1000),4^8),COLUMN(A1)))&"",输入后按下CRTL+ENTER(使结果为数组),再右拉可以得到结果。
将这种方法运用到药品企业价格查找的实例中去,如下图所示,这里只右拉了3格,最多显示了3家企业,O列为条件查找辅助列,P~R为相应的企业,S~V为相应的价格。至此我们完成了多条件和多结果的查找。收尾工作需要把辅助列删除,并且把得到的结果赋值-选择性粘贴-数值,以防移动后出现没有reference的情况。多行多列变一列开开心心地把这个表格交给我舅舅,准备听几句夸奖的话,结果他说不行啊,这不同企业和价格必须在一列显示啊,这样才方便比对价格作出选择。于是我就愁了,要怎么才能把这种多行多列的数据变为一列呢?解决办法当然是有的。使用OFFSET函数=OFFSET(($U$3),INT((ROW(A1)-1)/4),MOD(ROW(A1)-1,4))其中4代表了多行多列中的列个数,我最后每种药品最多列出4家供应商,对于使用者请按自己情况改变该数字,$U$3是多行多列的最左上角数值。运用到我们得实例中去,其中I~L是多行多列矩阵,在F3中输入公式并下拉,得到一列。每行增加行因为一种药品对应了4家供应商,所以每种药品必须占4行,才能和上诉的一列正确对应上,这时我们需要使原有的每行间隔三行。这种苦力活当然不可能一个个增加,必须是批量操作的。这种方法可以按照下图的辅助列排序方法,最后一步用的是快捷键排序,没显示出来,这种方法适合每行空多行。或者是
最后应用到实例中,得到结果
删除空白行许多没有供给公司和药品序号的行就是完全的空白行,这些影响观看,需要删除,建立辅助列,判断A和F同时为空时,赋值#N/A,CRTL+F替换#N/A为空值,F5定位空值,删除空值,可以达到要求,最终结果如下
-
Oracle 更新多行多列
2019-11-04 23:37:07一使用情况 本情况适用于,表a和表b有唯一关联字段时期关联关系为1对1.且需求为:在关联字段相等情况瞎,用表b的字段值更新表a的字段值。...加油明天会更好,今天我学会了怎么去更新多行多列不是吗... -
Excel 多列条件查找
2022-01-14 19:53:58将多列合并成为一列,用xlookup,vlookup 查找 方法二: sumproduct SUMPRODUCT(($I3:3:3:I36=A4)∗(36=A4)*(36=A4)∗(K3:3:3:K36=B4)∗36=B4)*36=B4)∗L3:3:3:L$36) 解读: $I3:3:3:I$36 : 为A4查找区域(第一个... -
where 后面的行子查询,,,行子查询(结果集一行多列或者多行多列)
2022-02-16 21:56:22行子查询(结果集一行多列或者多行多列) 案例:查询员工的编号最小并且工资最高的员工的工资的员工的信息 1.查询最小的员工的编号 select min (employee_id) from employees 2.查询最高的工资 select max(salary) ... -
利用shell中awk和xargs以及sed将多行多列文本中某一列合并成一行
2021-05-26 05:57:04一、问题描述最近需要利用Shell将多行多列文本中某一列,通过指定的分隔符合并成一行。假设需要处理的文本如下:我们主要处理的是,将用户名提取处理,合并成一行,并通过逗号进行分隔。最终的格式如下:"li1","huan... -
一个figure存在多个legend:多行多列图例
2018-07-18 22:05:54问题:如何在Matlab中,将图例绘制成多行多列的? 查找资料过程中发现, 1)有的方法要求多个plot中横坐标具有相同的长度 比如:https://blog.csdn.net/xgf415/article/details/56480011 2)高版本中,部分函数... -
自定义RadioGroup实现radioButton多行多列
2014-05-08 15:28:50前段时间我需要一个2行2列的RadioGrou -
MySQL 学习笔记(16)— 子查询(单行单列、一行多列、多行多列、 ALL、ANY、SOME 运算符、EXISTS 操作符)
2020-04-12 19:42:33表子查询(Table Subquery):返回一个虚拟表(多行多列)的子查询,行子查询是表子查询的一个特例。 2. 标量子查询 标量子查询的结果就像一个常量一样,可以用于 SELECT 、 WHERE 、 GROUP BY 、 HAVING 以及 ... -
数据由单列或单行编辑成多行多列
2014-05-15 10:16:09以下例子由单列变成多行多列为l原始数据只有一列 -
Excel完成将多行多列数据转化为一列
2021-05-18 15:41:12想要把Excel的数据用Word转成... 然后我们在顶部的菜单栏中找到“替换”,点击它就会弹出一个窗口,我们在查找内容后面粘贴上刚刚复制的内容,是空白的哦,然后再下方的替换为后面我们要输入“^p”,最后点击“全部替.. -
13.子查询返回多行多列的数据
2017-02-17 11:27:15列出公司各个部门的经理的姓名、薪金、部门名称、部门人数、部门平均工资。 步骤1:查找每个部门经理的姓名和薪金。 select ename,sal from emp where job='MANAGER'; 步骤2:连接dept表,查询部门名称。 select... -
gvim 复制/删除多行,列编辑,查找(向上/向下),操作快捷键
2019-12-19 21:46:12用gvim写代码时,如果想要复制多行,然后粘贴。 看到一个方便的操作方法: step1. 将光标移动到要复制的文本开始的地方,按V(是大写V)进入可视模式。 step2. 将光标移动到要复制的文本的结束的地方,按y复制。此时... -
Python DataFrame一列拆成多列以及一行拆成多行
2020-12-03 19:16:08Python DataFrame一列拆成多列以及一行拆成多行摘要在进行数据分析时,我们经常需要把DataFrame的一列拆成多列或者根据某列把一行拆成多行,这篇文章主要讲解这两个目标的实现。1.读取数据2.将City列转成多列(以‘|'... -
Excel如何提取不重复唯一值中级篇(多行多列)
2021-11-06 19:33:54之前介绍了提取不重复值得简单方法,今天有位小伙伴问我怎么在多行多列中提取不重复值,下面就给大家分享两种方法。 一、此方法简单快速适用各版本Excel 1、如下图,是某农场的夜班值班表,现在还是那个变态... -
易语言对象--Excel之单元格区域一次性写入数据(多行多列)
2018-10-29 15:20:46用易语言向excel表格中写数据,单个range或者多个ranges采用循环方式写入数据,对excel的表格访问太过频繁,速度上有一定的影响,思维上不太直观,特别是要对表格中的多个单元格进行更多计算后并一次性写入时,就不... -
excel表格多列数据中查找重复行数据
2020-07-22 15:08:351、做透视表统计 2、设置显示格式 3、取消分类汇总 4、拷贝到原数据后面 5、排序 -
SQL SERVER 根据关键字符,将一列分成多行
2015-06-01 09:46:35可根据关键字符将一列分成多行显示,例如 A B 1 1.1.2 拆分成 1 1 1 1 1 2 进行相关的显示 -
移动端样式小技巧css设置多行省略
2021-06-24 10:44:19点击DIV CSS查找更多内容,或登录"问答平台"提交你问题。 CMS学习网() 部分文章来源于互联网,版权归属于原作者。本站所有转载文章言论不代表本站观点,如是侵犯了原作者的权利请发邮件联系站长 (442446040@qq.... -
kingBase中从表多行变一列
2021-10-26 18:26:55一条SQL语句中,如何将从表的多行记录中的某字段值,集合成一个值,中间以逗号隔开?比如: CREATE TABLE master(id SERIAL,hero varchar(50)); CREATE TABLE slave(id serial,master_id int,name varchar(50)); ... -
sql查询 ,多行数据合并成一行,并且显示合并后某一列的值拼接结果
2021-02-07 12:57:52Oracle一列的多行数据拼成一行显示字符Oracle一列的多行数据拼成一行显示字符 oracle 提供了两个函数WMSYS.WM_CONCAT 和 ListAgg函数. www.2cto.com 先介绍:WMSYS.WM_CO ...sql 查询重复行数据1.查找表中多余的重复... -
Excel表格把一列数据转换为多行多列数据的方法&表单控制的数据选择&excel表格行列互换的快捷方式&构建...
2020-09-01 20:42:25一、Excel表格怎么把一列数据转换为多行多列数据(EXCEL2019) 例如:将一列60个数据转换为5行12列。 1、首先,在B1格输入“源数据 起始数据的单元格代号”,C1格输入“源数据 第二个数据的单元格代号”,然后选中B1... -
用Excel实现按照多列匹配
2020-07-19 21:02:56如果要实现按照多列来进行匹配,则可以运用以下三种方式: 1. SUMIFS(匹配目标列,条件范围1,条件1,条件范围2,条件2...) 例如下表,如果想要查找匹配ITEM为CL,ID为03的NUM,那么输入在目标单元格输入SUMIFS($C... -
MySQL基础--(多行子查询,列子查询,行子查询)
2021-08-07 09:53:11多行子查询返回的也是多行 比较操作符 IN/NOT IN 等于列表的任意一个 ANY/SOME 和子查询返回的某一个值比较 ALL 和子查询返回的所有值比较 体会any和ALL的区别 case1 列子查询,查询location_id 是1200和1400... -
多行相同只显示第一行(分组显示).sql
2020-07-21 14:39:12SQL查询结果部分字段信息重复时,去除重复信息,达到数据分组的效果,类似于EXCEL透视表,对数据库初学者还是有一定的帮助 -
pandas 删除列 多列
2021-09-25 17:02:17使用del和drop方法删除DataFrame中的列,使用drop方法一次删除多列 # 使用del, 一次只能删除一列,不能一次删除多列 # 只能使用 del df['密度'], 不能使用 del df[['密度', '含糖率']] del df['密度'] # del ... -
excel查找并返回多行数据
2018-03-22 09:04:00表二的D列为辅助列,先在该列返回张三在表一中的逐行行号。 D2=MATCH(A$2,OFFSET(Sheet1!A:A,D1,,ROWS(Sheet1!A:A)-D1),)+D1 B2=INDEX(Sheet1!B:B,$D2) B2公式复制到C2 然后将B2:D2公式向下复制。 ...