当前位置：首页 > 科技 > 正文

OFFSET函数的应用

首先，认识一下OFFSET函数。

　　从下图说明来认识一下excel教程中OFFSET函数的用法。

　　在C7单元格，输入公式：=SUM(OFFSET(C2,1,2,3,1))，得到结果为18。这个公式是什么意思呢?就是计算C2单元格靠下1 行并靠右2 列的3 行 1 列的区域的和。

　　可以在公式编辑栏，选中OFFSET(C2,1,2,3,1) 部分，按F9键抹黑，得到运算结果为：{3;8;7}，此时公式变为：=SUM({3;8;7})。从上图可以得知，就是利用OFFSET函数来得到一个新的区域，然后使用SUM函数求出这个新区域的和。

　　下面，介绍OFFSET函数的用法。

　　Offset函数主要应用在单元格区域的定位和统计方面，一般做数据透视表定义名称都需要用到Offset函数。Offset函数属于查找与引用类的函数。

　　OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。

　　OFFSET函数的语法是：OFFSET(reference,rows,cols,height,width)，按照中文的说法即是：OFFSET(引用区域,行数,列数,[高度],[宽度])

　　其中的参数意义如下：

　　Reference：作为偏移量参照系的引用区域。Reference必须为对单元格或相连单元格区域的引用;否则，函数 OFFSET 返回错误值 #VALUE!。

　　Rows：相对于偏移量参照系的左上角单元格，上(下)偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数(代表在起始引用的下方)或负数(代表在起始引用的上方)。

　　Cols：相对于偏移量参照系的左上角单元格，左(右)偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数(代表在起始引用的右边)或负数(代表在起始引用的左边)。

　　Height：高度，即所要返回的引用区域的行数。Height 必须为正数。

　　Width：宽度，即所要返回的引用区域的列数。Width 必须为正数。

　　学习使用OFFSET函数需要注意以下几点：

　　第一，如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF!。

　　第二，如果省略 height 或 width，则假设其高度或宽度与 reference 相同。

　　第三，函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。

　　第四，函数 OFFSET 可用于任何需要将引用作为参数的函数。

　　下面，结合几个实例学习OFFSET函数的应用。

　　OFFSET函数通常与其它函数来嵌套使用。下图所示的区域为实例的源数据区域。

　　OFFSET函数应用第一题：如下图所示，计算上图B列姓名下面所有单元格的数量。

　　在F3单元格输入函数：=OFFSET(B2,0,0,COUNTA(B:B)-1)，全选公式，按F9键，得到：={"张三";"王五";"李四";"闻一";"张二";"江八";"张六";"赵六";"钱子";"赵一";"孙六";"周末"}，我们可以数一下个数，得到12个。

　　提示：COUNTA 函数就是返回参数列表中非空值的单元格个数。

　　接下来，在F5单元格输入公式：=COUNTA(OFFSET(B2,0,0,COUNTA(B:B)-1))，得多结果为12。

　　OFFSET函数应用第二题：如下图所示，计算最后的平均成绩。分三种情况：第一，求最后一位、最后三位、最后五位的平均成绩。

　　单击F8单元格，可以看到有一个向下的箭头，单击箭头可以看到有1、3、5三个选项。

　　在F9单元格输入公式：=AVERAGE(OFFSET(C1,COUNTA(C:C)-1,,-F8))，就可以得到F8单元格所显示的平均成绩。

　　公式分析：首先使用OFFSET函数来计算出行数的值，然后使用AVERAGE来除以OFFSET部分得到的值，就得到了最后几个的平均成绩。

　　公式中，其中COUNTA(C:C)部分是统计C列的非空单元格个数为13，从而可以得到COUNTA(C:C)-1的值为12，即从C1单元格向下偏移12行。

　　另外-F8的意思是：减去F8单元格中的值。

　　当改变F8单元格的值，就可以得到其余行数的平均成绩。

　　OFFSET函数应用第三题：如下图所示，要求统计出班级=H3,成绩>=I3的人数。

　　H3和I3单元格的值是条件。单击这两个单元格，右下角有一个向下的三角形，单击此按钮，可以显示选择其余的条件值。比如上图所示，就是统计出班级为3版，成绩在>=70分的人数有几人。

　　本题的统计结果，可以在I5单元格输入公式：=COUNTIF(OFFSET(C1,MATCH(H3,A2:A13,0),,COUNTIF(A2:A13,H3)),">="&I3)，得到结果。

你可能想看：

Text函数：一个能被称为万能函数的存在

Text函数对于小白来说使用频率不高或都没听说过，Text函数将数值转换为按指定数字格式表示的函数。1.显示数字格式常规、数值、小数位补齐等等，可以做到round函数和Int函数的一些功能，有时候我们...

SUMPRODUCT函数自称求和之王，SUMIFS不服气要来PK

=SUMIFS(E,$B$12=G2)*($C$2。只能用LEFT从左边提取字符:$B$12=G2)*(LEFT($C$2。=SUMIFS(E:=SUMPRODUCT($E$2，$B$12=G2)*I...

Excel函数之——SUMPRODUCT函数太强大了

先介绍下SUMPRODUCT函数的基本用法,[array3]，该函数可以有多个参数。每个参数都必须是有相同维度的数组：以下是单条件计数:在使用N()函数“将N()返回的数组中的所有元素，所以就不在需要...

OS/PFS/DSS/DFS-各种生存指标傻傻分不清

因为患者死亡的日期确认没有困难，判断病人是否病情恶化的难度要大得多，发生恶化的定义通常涉及影像学资料（普通X线：没有复发或没有死亡可以反映临床获益？明确是否复发的难度要大得多。结局指标改变为 由特定疾...

【中考数学】金婷——解题研究：利用二次函数图象的性质求出分式函数的最大值

点A、B分别在y轴、x轴的正半轴上．△AOB的两条外角平分线交于点P，P在反比例函数y＝的图像上．PA的延长线交x轴于点C，（1）求∠P的度数及点P的坐标；（3）△AOB的面积是否存在最大值，本题考查...

手把手教你用Stata的Network包实现分类变量网状Meta分析

Stata是一款非常强大的统计和作图软件。网上目前存在的教程多半是用mvmeta包来做网状Meta，本文将介绍用Stata的Network包实现连续性变量的网状Meta分析，2. 将要处理的数据在Ex...

无需注册，免费使用，用它来传1G文件只要1分钟｜MuseTransfer

MuseTransfer是一款无需注册、打开网页即可免费使用的大文件传输工具。上传好的文件默认保留一周，微信登录之后可以查看传输历史，MuseTransfer可以作为无需长期储存的临时大文件云端工具来...

关于百度文字识别怎么找到API_Key和Secret_Key

有些识别文字的软件需要填写这两个key才能识别文字。下面是介绍如何找到这两个key的方法：使用百度文字识别，必须先有一个百度账号，然后进入百度智能云平台，登录账号后，点击立即使用按钮。点击左边的应用列...

Cell Reseach：caspase11切割gasderminD引发“细胞焦亡” - 基因编辑专区 - 生物谷

这些炎性caspase的激活能够促进细胞焦亡事件的发生，炎性caspase究竟是如何调节这些细胞事件至今仍然有待解决。该蛋白的切割能够引发细胞焦亡事件的发生。他们发现这一突变体小鼠不能够正常发生细胞焦...

SEER数据库系列|SEER数据库基本介绍

美国国家癌症研究所（National Cancer Institute）的SEER数据库收集了大约30％的美国人口的癌症诊断。从简单地按人群中器官部位枚举癌症的发展到包括通过组织病理学和分子亚型（由驱...

初中数学：二次函数的线段长问题探究

可转化为线段长类的面积型问题，平行于y轴的线段长的问题，抛物线y＝x2﹣（a+1）x+a与x轴交于A，与y轴负半轴交于点C，E是第三象限内抛物线上的动点；过点E作EF∥AC交抛物线于点F，过E作EG⊥...

「15 干货」专题22.1二次函数的图象和性质(含考点突破)

用待定系数法可求出二次函数的解析式：y=ax²+bx+c(a≠0)，则设所求二次函数为y=ax²+bx+c，将已知条件代入解析式，c的三元一次方程组，解方程组求出a：c的值，y=a(x-h)²+k：若...

第二篇  量子力学传统解释——量子力学中波函数的物理意义

为了使相应的物理解释与实验现象和人的认识逻辑一致，宏观世界实物不能同时既是粒子又是波，数学上描述微观客体波粒二重性的实验事实是容易的，还应有对数学形式所作的诠释性原理或与数学形式相对应的合理的物理模型...

office 2010最新永久激活密钥以及激活方法

许多网友在寻找office 2010激活密钥的过程中，找到office 2010密钥之后不知道怎么激活软件，小编就给大家分享一下office 2010最新的永久密钥以及激活的方法，以上office 2...

WPS Office 2016 绿色精简版|经典版 – zd423

只有本地功能、用户可选恢复功能、不带联网内容和模块、无多余连接按钮等。可以实现办公软件最常用的文字、表格、演示等多种功能。1、精简掉快盘、首页、素材库、模版、更新组件、错误报告、轻办公组件 2、首创去...

具备这几个特点的Offer，坚决不能接

例如一些公司给你Offer，或在合同上不写清楚薪酬问题的公司。底薪、绩效、提成、期权等具体的金额多少，都需要横向对比其他人选能力、薪酬期望、业务需求等。一个公司如果有一两个劳务纠纷是有可能的，一个公司...

初中化学:金属活动性顺序的应用与计算

下列说法正确的是（　　）A．滤渣中一定有Ag、Cu、Fe B．滤液中一定没有AgNO3C．滤液中一定含有Zn（NO3）2、Fe（NO3）2 D．该过程至少发生了三个化学反应解析：A、锌和硝酸银、硝酸铜...

五行、五体、五脏在中医临床的应用

五脏之气外合于皮脉肉筋骨”一、针皮肤治邪在肺，《灵枢·官针》中的毛刺、半刺、直刺均属于刺皮法，刺浮痹于皮肤也”故浅刺皮毛可宣泄皮毛部的邪气以宣肺气”《灵枢·官针》中的络刺、赞刺、豹文刺均属于刺脉法。刺...

【贫穷摄影】关于摄影灯的应用

由于内置闪光灯作用极其有限，经常会遇到逆光、背光等不尽人意的光线条件。有时候我们会考虑使用反光板进行补光，反光板的补光效果也不尽人意。在这个情况下我们就会考虑采用离机闪光灯进行补光，用高于反光板的闪光...

酵母提取物在食品工业中的应用

放0.1%～0.2%的酵母抽提物增强菜肴的鲜美味和厚感，在方便面、米线、红薯粉丝中调料中加入1%～3%酵母抽提物。可使方便面汤汁浓厚自然、味觉丰满、口味鲜美，经油炸后可提高方便面面饼的鲜味和色泽。作为...

夏少农 > 证治经验 一、气阴学说在外科的应用

一、气阴学说在外科的应用。认为劳倦则能伤脾，提出滋阴降火的治疗观点。但历代以气阴二伤作为指导临床的重要理论者。医家多认为阴虚而生内热、血虚而生风邪、阳虚而成内寒，而以益气养阴之法为主以治疗多种外科疾病...

【初二物理】不可见光的应用

3.红外线和紫外线在生活中有哪些应用。把红光以外看不见的光叫做红外线，利用红外线诊断疾病,如果在照相机里装上对红外线敏感的胶片,在红外线照射下，红外线经常用来治疗慢性炎症、慢性感染性伤口、扭挫伤等促进...

29种中草药在水产养殖上的应用

对肝纤维素化有显著的抵抗作用，已表现出对水产动物的安神、镇静、健胃促消化、理气、活血、养精补益、驱虫、杀虫、保健、解毒排毒、抗肿瘤、治肝炎、收敛、抗创伤、抗菌、抑菌、杀灭病毒等多种药理作用，并已研制出...

紫薇——四化的应用

2四化入命宫、官禄、财帛、田宅宫为化入。官禄宫化忌入田宅宫为化入，财帛宫化忌入官禄为化入，财帛宫化忌入田宅宫为化入，3四化入兄、夫、子、疾、迁、奴、福、父等宫位为化出。财帛宫化忌入子女宫为化出，财帛宫...

【心得】浅谈逆向工程在仿制药开发中的应用

从而得出定性配方、原料药的晶型、辅料的含量及制备工艺等要素。逆向工程的应用有助于降低制剂开发的难度、减少处方开发的工作量、缩短研发的时间，主要是重点关注原研药的处方成分、单位重量、外观、原料药晶型、包...

病位辨证和引经药的应用

病位既包括了脏腑、组织等疾病的具体部位，是指能引导药物直达病所而起到引经报使作用的药物。如手足太阳经病的引经药为羌活、藁本;手足少阳经病的引经药为柴胡等。上焦出于胃上口：胸胁是厥阴、少阴分布之所。手少...

单元格函数OFFSET区域值

上一篇
学理财不懂基金“族谱”怎么行

下一篇
校友会2019河南省一流学科排名公布，信息工程大学第1

有话要说...

最新文章

取消
扫码支持 支付码