精华内容
下载资源
问答
  • 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中,将表格里的一列转换为多行多列的几种方法。例如,以下表格,是一个行业分类表,都放在同一列中。现我们准备把它转为多列。表格里数据除掉标题行行,总共有60列数据,干脆我们就给它转为10行6列...

    今天来说说在Excel中,将表格里的一列转换为多行多列的几种方法。

    例如,以下表格,是一个行业分类表,都放在同一列中。现我们准备把它转为多列。

    540c4b3d16be1324d438a95266bdf476.png

    表格里数据除掉标题行行,总共有60列数据,干脆我们就给它转为10行6列吧。

    那么,一列转多行多列,怎么转换才快速呢?

    动手前,我们先找找规律吧。

    首先,我们要知道,表格单元格数据区域为A2:A61,转为10行6列的表格后,原先的单元格地址对应转换后的表格存放位置,如下:

    7f760617c12614827c0193032973f936.png

    那么问题了,怎么样来调取对应单元格地址的数据呢?

    以下四种方法,让你快速完成将一列转多行。

    ●查找替换法

    ●批量填充法

    ●借助word表格转换法

    ●用indirect函数转换法

    现在分别来说一说这四种方法具体怎么使用。

    一、查找替换法

    1、先做好如下图表底

    a1280f67aad4e375a2620b1d7fb54b72.png

    即在C2单元格输入A2,然后鼠标往右拖动填充至H2单元,使单元格从左往右生成A2、A4……A7数据;

    用同样的方法,在C3:H3单元格从左往左路生成A8、A9……A13数据。

    然后选中C2:H3这两行单元格区域,鼠标往下拖动至H11单元格,单元格即填充完毕。

    2、将“A”替换成“=A”

    表底做好后,我们利用查找替换法将表格里的“A”替换成“=A”。

    e43b4a1d3bf2c18217e41000134efa96.png

    3、将公式转化为数值

    替换后,我们可以看到C2:H11单元格变成是用公式填充。

    1a75e4f64f3bab0033f579194f329cf3.png

    所以,在替换后,我们选中C2:H11单元格区域并复制,然后右键—选择性粘贴,将其粘贴为数值,就完了。

    操作动态图:

    bd1ef8ca48104372e49047921dbd64ea.png

    二、批量填充法

    1、先选取B2:F61单元格区域后,在编辑框里输入公式:=A12,然后按批量填充;

    845ffcfa58a459ce2c5515e38b4fd28d.png

    2、再将填充的公式粘贴为数值;

    3、最后删除多余的行。

    操作动态图:

    721bf87a62b965aa1683e7ed969813a9.png

    技巧提示:因为是单列60行转为10行6列,原先A2:A11这10个单元格保持不变,所以B2:F61里的单元格就从=A12开始填充。

    三、借助word表格转换

    可以将Excel表格里的数据复制粘贴到word里,利用word进行转换,转换后再粘贴回excel表格中。

    操作方法:

    1、将Excel表格里的数据复制粘贴到word文档中,粘贴的时候记得选择【粘贴选项】中的【只保留文本】;

    2、全选粘贴进word文档中的文本,点击word菜单的【插入】—【表格】—【文本转换成表格】;

    ef472cca0f6e712a3e59bcad462cdfc9.png

    3、在弹出的【将文字转换成表格】对话框里,将列数设置为6列,行数10行,最后点击【确定】即可。

    ba8a55c755a135deaaf459f6c9885f70.png

    4、最后,把word文档里转换完的表格复制粘贴到Excel中就完成了。

    操作动态图:

    184fbea8ec3f2f1e506c13b3c0b99f0f.png

    四、用indirect函数转换

    即选择C2:H11单元格区域,在编辑框里输入以下公式:

    =INDIRECT("A"&6*ROW(A1)-5+COLUMN(A1))&""

    然后按填充。

    934cc9e91a0130fe0114cd250efabc46.png

    操作动态图:

    c47a0ff42d35c5b81bda34e707761dc3.png

    用indirect函数转换的方法,我曾写过专门的文章详细介绍此方法,里面还有关于indirect函数的语法。不懂的童鞋,可以再去翻阅以前的文章。

    文章为:

    「Excel技巧」如何利用indirect函数快速将一列转为多行多列排版

    关于Excel一列转多行多列的方法就介绍到这了。你最喜欢哪种方法?

    展开全文
  • 目的:将一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是一窍不通的,直到我舅舅给了一串大概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行,才能和上诉的一列正确对应上,这时我们需要使原有的每行间隔三行。这种苦力活当然不可能一个个增加,必须是批量操作的。
           这种方法可以按照下图的辅助列排序方法,最后一步用的是快捷键排序,没显示出来,这种方法适合每行空多行。


    或者是

         我用的方法是如下建立辅助列,定位空值后插入行,当然还是前两种比较好。



    最后应用到实例中,得到结果

          把之前多行多列变一列得到的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查找区域(第一个...
  • 行子查询(结果集一行多列或者多行多列) 案例:查询员工的编号最小并且工资最高的员工的工资的员工的信息 1.查询最小的员工的编号 select min (employee_id) from employees 2.查询最高的工资 select max(salary) ...
  • 一、问题描述最近需要利用Shell将多行多列文本中某一列,通过指定的分隔符合并成一行。假设需要处理的文本如下:我们主要处理的是,将用户名提取处理,合并成一行,并通过逗号进行分隔。最终的格式如下:"li1","huan...
  • 问题:如何在Matlab中,将图例绘制成多行多列的? 查找资料过程中发现, 1)有的方法要求多个plot中横坐标具有相同的长度 比如:https://blog.csdn.net/xgf415/article/details/56480011 2)高版本中,部分函数...
  • 前段时间我需要一个2行2的RadioGrou
  • 表子查询(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写代码时,如果想要复制多行,然后粘贴。 看到一个方便的操作方法: step1. 将光标移动到要复制的文本开始的地方,按V(是大写V)进入可视模式。 step2. 将光标移动到要复制的文本的结束的地方,按y复制。此时...
  • Python DataFrame一列拆成多列以及一行拆成多行摘要在进行数据分析时,我们经常需要把DataFrame的一列拆成多列或者根据某列把一行拆成多行,这篇文章主要讲解这两个目标的实现。1.读取数据2.将City列转成多列(以‘|'...
  • 之前介绍了提取不重复值得简单方法,今天有位小伙伴问我怎么在多行多列中提取不重复值,下面就给大家分享两种方法。 一、此方法简单快速适用各版本Excel 1、如下图,是某农场的夜班值班表,现在还是那个变态...
  • 用易语言向excel表格中写数据,单个range或者个ranges采用循环方式写入数据,对excel的表格访问太过频繁,速度上有一定的影响,思维上不太直观,特别是要对表格中的个单元格进行更计算后并一次性写入时,就不...
  • excel表格多列数据中查找重复行数据

    千次阅读 2020-07-22 15:08:35
    1、做透视表统计 2、设置显示格式 3、取消分类汇总 4、拷贝到原数据后面 5、排序
  • 可根据关键字符将一分成多行显示,例如 A B 1 1.1.2 拆分成 1 1 1 1 1 2 进行相关的显示
  • 点击DIV CSS查找内容,或登录"问答平台"提交你问题。 CMS学习网() 部分文章来源于互联网,版权归属于原作者。本站所有转载文章言论不代表本站观点,如是侵犯了原作者的权利请发邮件联系站长 (442446040@qq....
  • 一条SQL语句中,如何将从表的多行记录中的某字段值,集合成一个值,中间以逗号隔开?比如: CREATE TABLE master(id SERIAL,hero varchar(50)); CREATE TABLE slave(id serial,master_id int,name varchar(50)); ...
  • Oracle一多行数据拼成一行显示字符Oracle一多行数据拼成一行显示字符 oracle 提供了两个函数WMSYS.WM_CONCAT 和 ListAgg函数. www.2cto.com 先介绍:WMSYS.WM_CO ...sql 查询重复行数据1.查找表中多余的重复...
  • 一、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...
  • 多行子查询返回的也是多行 比较操作符 IN/NOT IN 等于列表的任意一个 ANY/SOME 和子查询返回的某一个值比较 ALL 和子查询返回的所有值比较 体会any和ALL的区别 case1 列子查询,查询location_id 是1200和1400...
  • SQL查询结果部分字段信息重复时,去除重复信息,达到数据分组的效果,类似于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公式向下复制。 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 66,187
精华内容 26,474
关键字:

多行多列查找