利用SUMIF和OFFSET+MATCH进行多条件求和

最新推荐文章于 2024-03-28 12:45:12 发布
最新推荐文章于 2024-03-28 12:45:12 发布
阅读量2.3w 收藏 13
点赞数 3
分类专栏： 函数与公式 文章标签： excel c
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/amulee/article/details/6371315
版权
本文介绍了一个Excel数组公式的使用方法，通过一个具体实例展示了如何利用SUMIF函数进行多维度的数据计算。该公式能够实现对特定条件下的数据快速汇总。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

这个源于兰版的一道题：http://www.excelpx.com/thread-167680-1-2.html
效果如下：


 
利用以下数组公式即可得到结果：
=SUMIF(B2:I2,B2:C2,OFFSET(B2:I2,MATCH(A3:A6,A12:A15,),))

选中B12:B15，输入公式，然后按Ctrl+Shift+Enter确认输入即可。

这个公式充分利用了Excel数组公式多重计算的功能。让我们来看一下这个公式是怎么样进行运算的

这个公式实质上是对SUMIF的后面两个参数，条件和求和区域进行了多重运算。

可以看见，条件是一个一行两列的区域，求和区域是一个四列一行的区域。结果是一个多维度的结果，因为SUMIF会分别对条件和求和区域进行组合后运算。如图所示：

Excel（函数篇）：COUNTIF与CONUTIFS函数、SUMIF与SUMIFS函数、ROUND函数、MATCH与INDEX函数、混合引用与条件格式
storyfull
03-16 1139
Excel（函数篇）：COUNTIF与CONUTIFS函数、SUMIF与SUMIFS函数、ROUND函数、MATCH与INDEX函数、混合引用与条件格式
套用match_Excel在工作中的应用（5）累计区域求和-sum（offset（match（）））
weixin_39817347的博客
12-19 2162
sumoffset此款函数组合乃是excel界的玉清昆仑扇，令我叱咤办公界好多年，看起来特别高大上，今天我就和大家分享下如何使用此套用函数。offset（）不是一个很常见的函数，offset通常用于定位一个区域，因此在财务工作中,通常用于定位需要求和的一个区间，如下图，根据填写月份来变动的求和。如果想只求特定区间的和，比如3-8月怎么求呢？每次都修改sum求和的区间范围也太麻烦了吧选择开始月份，将...
EXCEL函数SUMIFS高级用法:结合MATCH和INDEX快速求和
最新发布
weixin_56581108的博客
03-28 1万+
sumifs与上一章所讲的index和match函数嵌套，实现快速求和以上就是SUMIFS结合MATCH和INDEX嵌套，实现条件求和的方法，希望对你有帮助！
VBA多列条件列_多列求和列_求和.xlsm
02-21
VBA多列条件列_多列求和列_求和.xlsm
【EXCEL】技巧篇1 INDEX、MATCH和SUMIF函数的结合使用，根据条件检索数据并进行条件求和
m0_66482493的博客
12-28 5069
可以用于根据条件检索数据并进行条件求和。比如，在一个包含销售数据的表格中，你可能想要按照特定条件对销售额进行求和。这个公式的含义是：在 A 列中找到匹配 "产品A" 的单元格，然后将相应的销售额（在 B 列中）相加。现在，你想要针对特定的产品（比如产品A）计算销售额的总和。结合，都能根据条件对数据进行求和，只是具体的实现方法不同。： 在数据表中，列A是产品名称，列B是销售额。这个公式与之前的不同之处在于，它首先使用。找到 "产品A" 的位置，然后使用。返回该位置的对应销售额，最后使用。
Excel VBA 代替Sumproduct实现多条件求和
liuxiang860927的专栏
08-26 1万+
因为用函数公式，如果数据量大的话，会影响效率，而且会增加文件本身大小，所以想用vba代码替代。 以下代码用来实现函数公式sumproduct的功能，实现多条件求和。 Private Sub 多条件求和()    Dim d, ar, arr, s$, t$, i&    Set d = CreateObject("Scripting.Dictionary")    ar = Rang
VBA文件批量操作1-12个文件进行数据条件求和
陈表达的博客
02-05 660
文件批量操作
Excel的SUMIF条件求和函数详解与应用
`OFFSET`、`COLUMN`和`ROW`用于处理单元格引用，`MOD`计算余数，`VLOOKUP`和`MATCH`进行查找匹配，以及`LEFT`、`RIGHT`、`MID`等字符串操作函数。还有`REPLACE`、`SUBSTITUTE`用于文本替换，`LEN`计算字符长度，`...
Excel的函数：前言、概念、基本使用（IF+AND+OP、COUNT、IF嵌套、SUMIF、日期函数、ROUND、MOD、Replace、生成间断序号）
YFater的博客
09-04 3414
文章目录前言一.概念1.含义2.常用函数3.公式与函数的同异二.函数基本使用1.IF+AND+OR2.COUNTIF统计3.IF嵌套4.SUMIF 前言 EXCEL中使用频率最高的一个模块就是函数。 思考：一般在EXCEL中函数都是拿来计算的，跟数据分析有什么关系？ 其实在数据分析中最重要的一环不是分析数据，而是在得到数据以后进行的数据清洗。我们在得到任何的数据的时候都不是直接可以拿来使用的，我们需要对数据进行清洗以后，把我们所需的数据拿出来才能够进行分析。在这个过程中，我们一般用的比较多的工具就是EXCE
Excel SUMIF函数实现产品产量逐行累加技巧分享
Excel中的SUMIF函数是进行条件求和的强大工具，当结合OFFSET函数使用时，尤其在需要对多个行或列进行动态范围求和时，能够极大地提高工作效率。在本例中，通过SUMIF函数结合 OFFSET函数的三维引用技巧，实现了对产品...
VBA示例之 求人不如自已动手 多个工作表中指定条件指定范围加总求和
10-26
VBA示例函数之 求人不如自已动手 多个工作表中查找一个范围内符合某个指定条件的项目对应指定范围加总求和 ，供初学者参考，大牛勿进~~~~~~~
《Excel应用大全》示例文件 光盘文件
02-24
第1篇 Excel基本功能 • 冻结窗格 • 名称框定位 • 区域名称显示 • 多表区域设置格式 • 自动填充功能 • 数据的格式化 • 设置数字格式 • 数据类型转换 • 自定义数字格式 • 数据的填充复制 • 数据的查找替换 • 格式查找替换 • 数据中含通配符的查找 • 表格边框设置立体效果 • 如何在Excel自动扩展格式 • 如何在Excel自动套用格式 • 自定义工具栏 • 非连续区域集中打印 • 设置打印标题 第2篇 使用公式和函数 • 使用简单公式计算产品销售利润 • 使用相对和绝对引用计算产品销售额与利润 • 多表区域引用 • 利用混合引用制作九九乘法表 • INDEX函数和SUMIF函数特殊情况下的易失性 • Excel公式结果的检验和验证 • 创建Excel动态名称来实现采购数据的动态引用 • 成绩检索表－Excel名称代替引用 • Excel图表中使用名称 • 相对引用和混合引用定义名称 • 借用“列表”定义动态名称 • Excel工作表复制的名称问题 • 利用文本比较标识首次出现的记录 • 更改数据字母大小写 • 利用MID 函数提取身份证号码中的8 位生日数字 • 使用文本提取函数进行数字分列 • 使用查找函数拆分空格分隔的数据 • 实现EAN-13条码的校验位的算法 • 利用文本查找函数进行模糊查找 • 利用SEARCHB 函数分离全半角字符 • 利用FIND 函数提取连续数字 • 统计开奖号码中不重复数字个数 • 取得零件规格中的最后序号 • 利用TEXT函数格式化设备编号 • 从身份证号码中提取员工的生日信息 • 使用TEXT 函数显示中文格式的年份 • 将数值转换为商业发票中的中文大写金额 • 生成中文大写金额 • 利用CELL 函数取得动态工作表标签名称 • 利用MOD 函数生成数字校验码 • 利用MOD 函数实现培训反馈表中票数求和 • 利用取舍函数生成重复个数的自然数序列 • 按照特定条件进行取舍计算 • 利用取舍函数解决四舍六入问题 • 产生50～100 的随机整数 • 利用随机函数仅生成数字和字母 • 利用随机函数实现考试座位随机编排 • 日计帐中的余额累计 • 计扣个人所得税 • 统计月末考试中大于等于平均分的总分 • 利用CHAR 函数生成A～Z 序列 • 利用DATE 函数生成指定日期 • 利用运算符计算日期相差天数 • 利用日期函数计算公民年龄 • 利用日期函数计算员工退休日期 • 计算上月的天数 • 计算目标日期所在季度的最后一天 • 返回目标日期是全年中的第几天 • 判断是否为闰年 • DATEDIF函数基本用法 • 利用日期函数计算员工工龄 • 计算指定日期的星期值 • 计算上月月末日期的星期 • 指定某月的第几周的天数 • 推算出指定年份的母亲节和父亲节日期 • 计算上个月的考勤天数 • 计算基金赎回入账日期 • 加班时间的累加 • 计算车间工人工时 • 员工加班时间的舍入计算 • 使用VLOOKUP 函数进行员工信息查询 • 利用HLOOKUP 函数查询产品月销售量 • 查找某员工是否登记在员工信息表中 • 判断员工公积金是否重复提缴 • 查询申购基金的申购手续费 • 从成绩明细表中查询学员总成绩 • 利用LOOKUP 函数实现无序查询 • 取得最后非空单元格数值 • 利用行列号函数生成永恒的序号 • 查询员工信息表中所有人事信息 • 利用INDIRECT函数动态统计产品销量 • 利用OFFSET函数产生区域引用 • 利用INDEX函数隔行提取数据 • 多条件组合查询资料 • 利用INDEX函数结合MATCH函数进行向左查找 • 实现根据学员成绩查询等级 • 确定工资单中最后一名员工的位置 • 根据工资表生成工资条 • 利用CHOOSE函数重新生成内存数组 • 利用查找函数进行多表数据标识 • 实现多区域联合判断求值 • VLOOKUP函数实现向左查询 • 从生产配件编号中提取连续的数字编码 • 利用MATCH 函数提取连续数值 • 查询并汇总各产品销售额 • 利用MATCH函数汇总最后3 天的产品销售额 • 利用RANK函数实现按班级进行动态排名 • 利用COUNT函数统计培训反馈表中的平均票数 • 动态引用商品进货明细表 • 统计培训课程表中各部门的授课数 • 统计进货量大于5小于10的型号数量 • 标注各种水果第1次出售日期 • 查询重复输入员工身份证 • COUNTIF函数使用通配符统计示例 • 汇总指定公司的产品季度销量 • 统计二季度手机销量情况 • 利用SUMIF 函数实现多表汇总 • 按区间统计数值个数 • 学员英语成绩分段统计人数 • 计算比赛评分表中频率最高的分值 • 利用极值函数限定销售价格 • 取得不重复的第2 个最低报价 • 统计各科成绩大于等于平均分的总平均分 • 计算体操比赛的选手得分 • 统计年度培训完成情况表 • 在筛选条件下生成连续序号 • 按学员成绩进行排名 • 根据比赛成绩实现中国式排名 • 根据学员成绩的百分比占比划分等级 • 统计购买商品的不重复VIP 用户数 • 商品月度销量统计汇总 • 统计足彩数据中最多连胜场数 • 单利与复利的比较 • 终值系数和现值系数 • 普通年金终值和现值的计算过程 • 预付年金终值和现值的计算过程 • 递延年金终值和现值的计算过程 • 年金终值系数表和年金现值系数表 • 名义利率与实际利率 • 整存整取 • 零存整取(一) • 零存整取(二) • 房屋的价值(一) • 房屋的价值(二) • 买卖房屋的利润率 • 投资经营房屋的利润率 • 多久才能拥有100万(一) • 多久才能拥有100万(二) • 孩子上大学的学费(一) • 孩子上大学的学费(二) • 孩子上大学的学费(三) • 利用Excel财务函数计算住房按揭还款计划 • 相同间隔时间序列的现金流量净现值 • 相同间隔时间序列的现金流量内部收益率 • 不规则时间序列和现金流量的净现值与内部收益率 • 考虑融资成本和再投资收益的内部收益率 • 直线折旧法 • 年限总和折旧法 • 固定余额递减折旧法 • 双倍余额递减折旧法 • 可变余额递减法的累计折旧 • 累计到第2年第3 季度中期的累计折旧 • CONVERT函数的应用 • 使用CONVERT函数应注意正确书写 • 多单元格数组公式计算销售金额 • 单个单元格数组公式计算销售总额 • 两个一维数组的乘法运算 • 两个一维数组的连接运算 • 使用两个一维数组构造二维数组 • 部门评价等级转换 • 使用MMULT 函数计算产品结构指数 • 产生1－10 的自然数垂直序列和水平序列 • 等比例构造一维循环数组 • SUMIF函数对产品产量进行逐行汇总 • 利用MMULT函数实现数组累加 • 将销售明细表中合并单元格填充地区名称 • 将二维数组按列转换为一维数组 • 数组公式实现单条件求和 • 计算一周中内存采购的平均价格 • 统计各品牌的商品销量汇总 • 利用COUNT函数代替SUM 函数统计不重复记录 • 标识零件规格号中的非法数据 • 将中文大写日期转换成日期序列 • 实现按日期和客户名查询数据 • 按条件实现总表拆分到分表 • 按比赛成绩进行降序排列 • 对员工信息表中员工姓名排序 • 从培训课程安排表中提取讲师部门 • 从商品明细表中提取唯一品牌名称 • 根据商品和品牌确定唯一品牌名称 • 多维引用示例的数据来源 • 利用INDIRECT函数返回对多表区域的引用 • 求金额最大的费用项目 • 提取无重复的水果清单 • 计算按原始股折算的某股票最高价格 • 计算用餐记录分摊实例 • 计算最好成绩和的最优成绩实例 • 跨多表条件求和 • 宏表函数取得工作表标签名称 • 标识商品销售表中红色字体的记录 • 按照商品采购表中的数值格式显示查询结果 • 提取指定单元格的公式表达式 • 根据立方体指定参数计算公式结果 • 提取固定字符间隔的零件规格号 第3篇 创建图表和图形 • 图表种类 • 柱形图 • 条形图 • 折线图 • 饼图 • XY散点图 • 面积图 • 圆环图 • 雷达图 • 曲面图 • 气泡图 • 股价图 • 圆柱图、圆柱图和棱锥图 • 自定义图表类型 • 线-柱图 • 两轴折线图 • 对数图 • 组合图 • 创建自定义图表 • 添加误差线 • 设置轴格式 • 分类坐标轴标签 • 空单元格的绘制方式 • 设置单个数据点格式 • 设置透明色 • 多工作表数据作图 • 添加趋势线 • 设置三维图表 • 数字条件格式 • 设置坐标轴的交点 • 使用次坐标轴 • 图表上的文本框 • 图表的组合 • Microsoft Graph图表 • 图表转为图片 • 设置自动筛选 • 添加辅助行 • 定义名称 • 数据透视图 • 使用Excel VBA • 添加水平线 • 添加垂直线 • 复合饼图 • 等高线图 • 直方图 • 排列图 • 控制图 • 动态对比图 • 动态股票图 • XY散点折线图的内插值计算 • 自选图形种类 • 插入自选图形 • 添加文本 • 自选图形绘制线条和连接符 • 旋转和翻转 • 对齐和分布 • 自动靠齐 • 设置叠放次序 • 设置组合 • 设置阴影 • 设置三维效果 • 设置超链接 • 制作室内平面图 • Excel图形 • Excel工作表背景 • 插入图片到Excel工作表 • 图片的调整 • 裁剪图片 • 旋转图片 • 设置图片的透明色 • 插入动态图片 • 制作组织结构图 • 流程图 • 循环图 • 射线图 • 棱锥图 • 维恩图 • 目标图 • 创建艺术字 • 插入剪贴画 第4篇 使用Excel进行数据分析 • 数据列表的实例 • 使用记录单为数据列表命名动态名称 • 数据列表按行排序 • 含有公式的数据排序 • 简单排序的例子 • 自定义排序 • “关系或”条件的高级筛选 • “关系与”条件的高级筛选 • 利用高级筛选选择不重复的记录 • 三个“关系或”条件的高级筛选 • 使用计算条件的Excel高级筛选 • 同时使用“关系与”和“关系或”的Excel高级筛选 • 创建分类汇总 • 列表 • Excel分列功能 • 使用“导入外部数据”功能导入标准工时数据 • Microsoft Query检索数据源 • 销售数据清单 • 定义名称创建动态数据透视表 • 分页显示数据透视表 • 使用数据列表功能创建动态数据透视表 • 按日期或时间项组合 • 按数字项组合 • 指定项组合 • 创建计算字段 • 添加下计算项 • 获取Excel数据透视表数据 • 创建复合范围的Excel数据透视表 • 数据透视图 • 模拟运算表 • 单变量求解 • 规划求解 • 分析工具库 第5篇 使用Excel的高级功能 • 设置单元格数值条件 • 设置公式条件 • 复制删除条件格式 • 应用示例 • 优先顺序 • Excel数据有效性允许的条件 • 定位复制删除Excel数据有效性 • 数据有效性的高级应用 • 数据有效性的高级应用_多表不重复输入 • 分级显示 • 合并计算 第6篇 使用Excel进行协同 • Excel单元格引用建立链接统计奖金总额 • 超链接实例 • 创建Excel动态超链接 • 发布（不）具有交互性的网页_Excel的交互性 • 创建Web查询 • Excel和ASP • Excel与XML数据 • Excel与其他应用程序共享数据 • 使用Excel工作组 第7篇 Excel自动化 • 录制宏 • VBA语言基础 • 与Excel进行交互 • Excel的自定义函数 • 如何操作Excel工作簿、工作表和单元格 • 工作表的Change事件 • 工作表中高亮显示行和列 • 文件保存提醒 • 关闭工作簿是恢复Excel默认设置 • 设置Excel工作簿打开时的界面 • VBA事件激活的顺序 • VBA事件的激活与禁止 • 插入命令按钮 • 使用命令按钮设置单元格格式 • 使用复选框控件制作多选调查问卷 • 使用选项按钮控件制作单项调查问卷 • 使用组合框控件制作调查问卷 • 使用文本框控件快速录入三位数字 • Excel工作簿中插入用户窗体 • 在用户窗体中插入控件 • 为窗体控件添加事件代码 • 用户窗体的QueryClose事件和Terminate事件
Excel函数组合技巧解析：常用高级公式实战
2. **SUM+SUMIF**：这是一个用于多条件求和的组合。SUMIF函数允许根据一个条件求和，而SUM则可以将多个SUMIF的结果相加。在这个例子中，用于统计刘备、关羽、张飞三人的销售总量。 3. **SUM+OFFSET+MATCH**：这个...
Excel之用Sumifs完成多条件多列的求和
热门推荐
keenrob的博客
12-29 4万+
工作场景 有这样一份作业，统计财务部中，级别不为经理的各项社保扣款合计。 由于这是多条件求和，所以要用到sumifs函数，又由于是多列求和，所以也要用到offset函数。 函数组合应用 =SUM(SUMIFS(OFFSET(C1:C25,,{0,1,2,3}),B1:B25,J5,G1:G25,"<>经理")) 这个应用的关键是offset函数。在这个函数组合应用中，C1:C25为reference列，{ }里面的数字，指的是以reference列为基准，向右偏移0列（自身）、1列（D
Excel·VBA数组组合函数、组合求和
hhhhh_51的博客
05-04 1万+
观察每个组合的数字排列规律，可以发现每次最后一个数字排列到尾数7的时候，前面一个数字递增1，后面依次排列到尾数7；且后面的几个数字如果存在依次递增的情况时，前面的一个数字递增1，后面依次排列到尾数7，直至完成21种组合。组合、组合数：从n个不同元素中，任取m(m≤n)个元素并成一组，叫做从n个不同元素中取出m个元素的一个组合；从n个不同元素中取出m(m≤n)个元素的所有组合的个数，叫做从n个不同元素中取出m个元素的组合数。从1-7共7个数字中选出5个数字，共21种组合，如图。
数据挖掘与分析应用1：Excel表数据分析，sum，sumif,sumifs,vlookup,match,index,几个配合使用
weixin_46838716的博客
11-09 2605
数据挖掘与分析应用1：Excel表数据分析，sum，sumif,sumifs,vlookup,match,index,几个配合使用
SUMIFS函数 、MATCH及INDEX函数
weixin_48732479的博客
06-25 4708
SUMIFS函数 基本语法 sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) 　　这个例子统计2014年9月份上半月成都发货平台的发货量，如果想要通过拖动填充柄获得其他平台上半月的发货量，则需要使用绝对引用。 MATCH及INDEX函数 　　两者配合可以实现VLOOKUP函数的功能。具体内容可以参考这篇文章: 一起认识MATCH函数. ...
【VBA】Excel获取所在单元格，将所有sheet对应的单元格的值相加，并输入该单元格
LAM LAB
01-11 796
统一计算当前单元格的所有sheet相加的值，因为导出每堂课的点名名单，对每堂课被点名的学生的分数进行一个求和，两种方式单个、多个。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值