Excel中 OFFSET函数和MOD函数的联合使用的简单实例

最新推荐文章于 2025-12-12 12:12:20 发布
原创 最新推荐文章于 2025-12-12 12:12:20 发布 · 587 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#excel

本文通过一个简单的实例，介绍了如何在Excel中利用OFFSET和MOD函数解决动态求品牌下商品销售额占比的问题。当数据按5行一组分组时，OFFSET函数根据MOD函数计算的余数进行偏移，选取特定区域求和，实现下拉填充时正确计算每个品牌的占比。

目录

一、excel数据样例表

二、数据需求 

三、` $ `选取的不足

四、OFFSET( ) 和MOD( ) 解决方法

五、分析总结

一、excel数据样例表

序号日期渠道线上渠道品牌大区门店等级商品销售额销售额占比活动平均到手价
5202023/5/1线上天猫品牌1臻致抗老修复面霜389.230.34112
5212023/5/1线上天猫品牌1立体紧颜精华乳258.560.23231
5222023/5/1线上天猫品牌1光嫩透亮抗老修复精华206.870.18321
5232023/5/1线上天猫品牌1立体充盈滋养面霜156.230.1489
5242023/5/1线上天猫品牌1充盈保湿精华水137.370.1245
5252023/5/1线上天猫品牌2充盈抗氧化眼霜98.560.31543
5262023/5/1线上天猫品牌2晶彩粉霜83.240.26322
5272023/5/1线上天猫品牌2净亮洁面膏54.340.1740
5282023/5/1线上天猫品牌2臻致润彩妆前乳49.120.1576
5292023/5/1线上天猫品牌2琉光炫彩唇膏35.890.1140
5302023/5/1线上京东品牌1臻致抗老修复面霜462.310.34124
5312023/5/1线上京东品牌1立体紧颜精华乳313.450.23215
5322023/5/1线上京东品牌1光嫩透亮抗老修复精华235.690.18307
5332023/5/1线上京东品牌1立体充盈滋养面霜181.230.1399
5342023/5/1线上京东品牌1充盈保湿精华水152.370.1155
5352023/5/1线上京东品牌2充盈抗氧化眼霜112.560.29589
5362023/5/1线上京东品牌2晶彩粉霜99.240.25312
5372023/5/1线上京东品牌2净亮洁面膏76.340.1947
5382023/5/1线上京东品牌2臻致润彩妆前乳59.120.1580
5392023/5/1线上京东品牌2琉光炫彩唇膏45.890.1243
5402023/5/1线下品牌1华东S臻致抗老修复面霜322.450.3264
5412023/5/1线下品牌1华东S立体紧颜精华乳245.780.25321
5422023/5/1线下品牌1华东S光嫩透亮抗老修复精华186.540.19544
5432023/5/1线下品牌1华东S立体充盈滋养面霜129.230.13299
5442023/5/1线下品牌1华东S充盈保湿精华水113.670.1149
5452023/5/1线下品牌1华东B充盈抗氧化眼霜89.450.28435
5462023/5/1线下品牌1华东B晶彩粉霜75.230.24329
5472023/5/1线下品牌1华东B净亮洁面膏62.340.1949
5482023/5/1线下品牌1华东B臻致润彩妆前乳53.120.17231
5492023/5/1线下品牌1华东B琉光炫彩唇膏39.890.1236.9
5502023/5/1线下品牌2华东S臻致抗老修复面霜131.540.2895
5512023/5/1线下品牌2华东S立体紧颜精华乳107.820.23168
5522023/5/1线下品牌2华东S光嫩透亮抗老修复精华92.840.2195
5532023/5/1线下品牌2华东S立体充盈滋养面霜75.450.1684
5542023/5/1线下品牌2华东S充盈保湿精华水62.180.1340
5552023/5/1线下品牌2华东A充盈抗氧化眼霜97.250.2300
5562023/5/1线下品牌2华东A晶彩粉霜73.450.15225
5572023/5/1线下品牌2华东A净亮洁面膏245.780.5152
5582023/5/1线下品牌2华东A臻致润彩妆前乳36.230.0879
5592023/5/1线下品牌2华东A琉光炫彩唇膏29.650.0644
5602023/5/1线下品牌1华北S臻致抗老修复面霜322.450.3264
5612023/5/1线下品牌1华北S立体紧颜精华乳245.780.25321
5622023/5/1线下品牌1华北S光嫩透亮抗老修复精华186.540.19544
5632023/5/1线下品牌1华北S立体充盈滋养面霜129.230.13299
5642023/5/1线下品牌1华北S充盈保湿精华水113.670.1149
5652023/5/1线下品牌1华北B充盈抗氧化眼霜89.450.28435
5662023/5/1线下品牌1华北B晶彩粉霜75.230.24329
5672023/5/1线下品牌1华北B净亮洁面膏62.340.1949
5682023/5/1线下品牌1华北B臻致润彩妆前乳53.120.17231
5692023/5/1线下品牌1华北B琉光炫彩唇膏39.890.1236.9
5702023/5/1线下品牌2华北S臻致抗老修复面霜131.540.2895
5712023/5/1线下品牌2华北S立体紧颜精华乳107.820.23168
5722023/5/1线下品牌2华北S光嫩透亮抗老修复精华92.840.2195
5732023/5/1线下品牌2华北S立体充盈滋养面霜75.450.1684
5742023/5/1线下品牌2华北S充盈保湿精华水62.180.1340
5752023/5/1线下品牌2华北A充盈抗氧化眼霜97.250.2300
5762023/5/1线下品牌2华北A晶彩粉霜73.450.15225
5772023/5/1线下品牌2华北A净亮洁面膏245.780.5152
5782023/5/1线下品牌2华北A臻致润彩妆前乳36.230.0879
5792023/5/1线下品牌2华北A琉光炫彩唇膏29.650.0644

二、数据需求

现在需要求对应品牌下的单独商品的销售额占比

也就是这样的数据需求

品牌1臻致抗老修复面霜389.230.34
品牌1立体紧颜精华乳258.560.23
品牌1光嫩透亮抗老修复精华206.870.18
品牌1立体充盈滋养面霜156.230.14
品牌1充盈保湿精华水137.370.12

三、` $ `选取的不足

        如果就是这样单独的少量数据，则可以使用sum(),然后使用$将区域进行锁死，然后下拉即可计算出结果。但是如果有很多个数据区域，则excel下拉时，下面的数据选区仍然会选用上面锁死区域的求和数据进行占比相除。

四、OFFSET( ) 和MOD( ) 解决方法

        所以为了避免这个情况发生，并且实现需求。

        我们使用offset的行移动，和mod函数的求余对行数进行判断。

        下面给出函数并且做具体解释。

        =ROUND(H520/SUM(OFFSET(H520,-MOD(ROW(H520),5),0,5,0)),2)

        首先看  -MOD(ROW(520),5)这个部分的意思，根据数据需求，我们的品牌是5个一组，所以在行数上对5求余数。

        例如现在是第520行，对5求MOD余数为0，则不需要偏移，然后采用OFFSET水平向下选取5个区域的数据进行sum求和。

        如果是第521行，则余数为1，-MOD将行数向上移一个单位，现在数据框右定位在520行，然后OFFSET继续向下选取5个数据区域进行求和。如此进行推演，这样即可使excel实现快速下拉迅速进行占比计算。

五、分析总结

        以上是函数结合实例的解释，在此我们单独拿出函数，用规范性语言进行解释。

  1. OFFSET(H520, -MOD(ROW(H520), 5), 0, 5, 0): 这部分函数使用OFFSET函数，从H520单元格开始，向上偏移到与当前行号（ROW）取模后余数为0的位置。这个偏移量的目的是找到最近的一个能被5整除的行号，以便获取最近的5个单元格。函数中的参数0, 5, 0表示在垂直方向上不偏移，水平方向上偏移5行，返回的区域大小为5行1列。

  2. SUM(OFFSET(H520, -MOD(ROW(H520), 5), 0, 5, 0)): 这部分函数将上一步中返回的5个单元格的值相加，计算它们的总和。

  3. H520 / SUM(OFFSET(H520, -MOD(ROW(H520), 5), 0, 5, 0)): 这部分将H520单元格的值除以第2步中计算的总和。

  4. ROUND(H520 / SUM(OFFSET(H520, -MOD(ROW(H520), 5), 0, 5, 0)), 2): 最后，使用ROUND函数将第3步中的结果四舍五入到2位小数。

至此我们OFFSET结合MOD的一个小实例就演算完成，因为行变动和分组的原因会导致公式不同，各位大佬们还需具体情况具体分析。

kakajiacheng
关注
OFFICE1：EXCEL、WPS表格数据处理，三种多列合并为一列，用法和原理详解。
喵喵喵的博客
07-13 1万+
效果： eg1： 公式： =OFFSET($A$2,MOD(ROW(A2)-2,4),INT((ROW(A2)-2)/4)) eg2： 公式： =OFFSET($A$2,MOD(ROW(A2)-2,3),INT((ROW(A2)-2)/3)) eg3： 公式与eg2相同。 =OFFSET($A$2,MOD(ROW(A2)-2,3),INT((ROW(A2)-2)/3)) 所以，使用方法很简单改变除数即可。 原理： ...
EXCEL：案例练习（一）
2301_76815178的博客
04-10 6684
总结EXCEL中数字函数、循环与重复、随机函数的应用和一些EXCEL综合案例的分享
用offset,int,mod,index,address,indirect等函数实现excel列数据与行、矩阵、转置等的变换
lijil168的专栏
03-29 2866
用offset,int,mod等函数实现excel列数据与矩阵数据的变换，用公式比较方便。注意，本例子矩阵数据是先按列，再按行填充数据。如果想先按行，再按列填充数据，可以再进行装置，也可以修改一下公式即可。 列变矩阵 6行：IF(ROW()>6,"",OFFSET($A$1,(ROW()-1)*1+(COLUMN()-3)*6,0,1,1)) 矩阵变列：OFFSET($C$1,MOD(R...
excel行列互换_excel统计函数：应用广泛的动态统计之王OFFSET（上）
weixin_39935319的博客
10-22 550
编按：哈喽，大家好！今天是部落窝函数课堂的第6课，我们将认识人送外号“动态统计之王”的OFFSET函数！OFFSET函数是一个非常实用的函数，它在下拉菜单、动态图表、动态引用等操作中都具有不可替代的作用。毫不夸张的说Excel表格的高效，有相当一部分的功能来源于OFFSET。今天就跟着小编一起来认识一下它吧！（由于教程篇幅较长，将分为上下两篇，本篇为上篇。）＊＊＊＊＊＊＊＊＊【前言】OFFSET函...
Excel函数 - MOD和ROW函数搭配方法
Lizzy_Fly的博客
02-15 7156
案例公式：=IF(MOD(ROW()+4,7),"",$D3) 场景是因为一个体重记录表，每日显示体重值的曲线趋势，但时间长了，折线图的数字标签增多重叠无法查看。如下图 应需求，则想将体重数值显示变更为每周一显示，即7天为一个周期，显示7天的第一天的值，最后的成果如下图。 那么我们一起来看看新增的辅助列【体重标签】里的公式是如何实现的~ 这个公式一共用到3个函数，从里到外，分别是ROW，MOD，IF 第一个函数是ROW 该函数是取单元格的行数，比如C3单元格，它是在excel的第3行，那么ROW(C3)
excel中OFFSET函数怎么用
09-04
OFFSET函数是Excel中用于动态引用区域的函数，其语法如下： $$ \text{=OFFSET(Reference, Rows, Cols, [Height], [Width])} $$ #### 核心参数详解： 1. **Reference**：起始参照位置（必填） - 示例：`A1` 表示以...
20171219Excel函数(Offset)使用方法记录
u012393592的博客
12-19 1983
1、起因：师妹处理数据，类似于这种总共有4000多行，要求每个站点污染物的均值，手动输入实在是太麻烦了。数据每隔24小时换一个站点记录，所以可以对数据每24行算一次均值，然后就可以下拉了。嘿嘿嘿。主要用到的函数是OFFSET函数，顾名思义就是偏离函数。文档中使用的公式如下：=IF(MOD(ROW()-1,24),"",AVERAGE(OFFSET(S2,-23,-15,24,)))意思是首先进行条件
Excel函数学习笔记
weixin_51707948的博客
08-30 1443
本学习笔记主要参考内容有《跟李锐一起学Excel》……
Excel函数大全完整版实例讲解
Excel函数大全完整版【实例讲解】是一套全面、系统、实用的电子表格函数学习资料，涵盖了Excel中几乎所有常用和高级函数的应用场景与实际操作方法。该资料以“实例讲解”为核心，通过真实案例帮助用户深入理解各类...
EXCEL函数使用大全详解PDF下载
此文档的核心价值在于全面整合了Excel中最常用、最实用的函数类别，包括数学与三角函数、统计函数、逻辑函数、文本函数、日期与时间函数、查找与引用函数、财务函数以及信息函数等，并结合实例讲解其语法结构、参数...
Excel-Offset函数一列或多列数据拆分为多列
weixin_46205156的博客
04-24 4971
本例中起始位置为A1单元格，因为数据源都在一列，并且都是单元格的引用，因此只需要确定行偏移量即可，可以看到公式里只用了两个参数，第一参数固定为A1，第二参数是MOD(COLUMN(A1)+2,3)+ROW(A1)*3-3。这个公式将行号除以3取余数。结果是，每三行，列偏移量循环从0开始，即：第1行的结果是0，第2行的结果是1，第3行的结果是2，然后第4行的结果又回到了0，依此类推。我们希望得到的是新的数据在E，F，G，H，I，J，K，L，M九列。OFFSET(起始位置，行偏移量，列偏移量，高度，宽度)。
下载Excel模板样例。
麦子的博客
12-13 1066
1.首先查询出数据库的数据转化成json格式 如：String totalJson = paymentDayRptService.queryPayDayRtpForExport(); service层中获取到totalJson。 String totalList；; List<PaymentDayRpt> paymentDayRptList = paymentDayRptDao...
excel数据分析案例
热门推荐
G090909的博客
03-24 1万+
背景：现用Python爬取了某求职网站上关于数据分析的数据，我希望知道数据分析是个怎样的职位？它的工资和薪酬是多少？它有哪些特点，需要掌握哪些能力？哪些公司会招聘这样一个岗位？ 1、数据有无缺失值？ 数据的缺失值很大程度上影响分析结果。引起缺失的原因很多，例如技术原因，爬虫没有完全抓去，例如本身的缺失，该岗位的HR没有填写。 如果某一字段缺失数据较多（超过50%），分析过程中要考虑是否删除该...
【Excel】数据透视表—简单数据分析实例
张欣的博客
10-24 1万+
【Excel】数据透视表—数据透视表布局 【Excel】数据透视表—标签合并居中 【Excel】数据透视表—新增一列（字段） 【Excel】数据透视表—按年、季度、月份汇总报表 数据透视表可以帮助我们快速的做一些统计方面的数据分析，也是很方便的。下面给出了五个简单的分析实例： 1. 统计各部门人数占总人数的百分比。主要就是修改生成的数据透视表的【值显示方式】，就不需要我们自己用公式去计算...
offset函数的高级用法_offset函数使用大全
weixin_39633054的博客
11-24 2736
今天雷哥给大家详细讲解下Excel中的offset函数。1.Offset 函数语法=OFFSET(reference, rows, cols, [height], [width])微软官方参数解释：① Reference 必需。 要作为偏移基准的参照。② Rows 必需。 需要左上角单元格引用的向上或向下行数。 rows =5，可指定引用中的左上角单元格为引用下方的 5 行。 Rows 可为正数（...
做RPG游戏时在对话的excel配置表中使用富文本
99的博客
12-11 250
本文介绍了TextMeshPro富文本功能的基本和高级用法。基础功能包括设置颜色（如红色、黄色）、样式（粗体、斜体）和字号大小。高级功能涵盖文字缩放、字符间距、行高调整、对齐方式（左/中/右）、缩进、Unicode特殊符号（如心形、五角星）以及Sprite图标插入。文章还展示了一个完整的任务文本示例，综合运用了多种格式：居中金色标题、灰色描述文字、绿色地点名称、紫色道具奖励以及红色斜体警告提示，生动演示了富文本在游戏UI中的实际应用效果。
Excel如何快速实现单元格隔行填充颜色？
weixin_44454189的博客
12-10 402
最简单的操作方法就是将单元格进行隔行颜色填充，或者隔行更改字体颜色，使每行单元格都用不同颜色标注，这样操作能够减少阅读失误，降低错误率。在新建规则窗口中，选中使用公式确定要设置格式的单元格，在为符合此公式的值设置格式文本框中输入公式：=MOD(ROW(),2)=1，这个公式的整体含义是用当前行的行号除以2的余数是多少。点击格式按钮，在设置单元格格式窗口中，点击填充选项卡，选择需要的颜色进行填充。如果是行号是偶数（双数），能被2整除，那么结果就是0，如果是行号是奇数（单数），不能被2整除，那么结果就是1。
Excel文件合并
最新发布
12-12 344
在日常数据处理工作中，经常需要将多个 Excel 文件合并为一个文件，以提高数据整理效率。传统的合并方式需要手动复制粘贴，操作繁琐且容易出错，特别是在处理大量文件时更是如此。因此，需要一个能够自动化合并 Excel 文件的工具，支持不同的合并方式（按工作表合并、按行合并），并能灵活选择输出路径。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值