精华内容
下载资源
问答
  • 学习Excel技术,关注微信公众号:excelperfect在VBA代码中,经常要引用单元格数据区域并对其进行操作。然而,如果对数据区域采用“硬编码”地址,那么当该区域大小变化时,必须修改相应的引用该区域的代码。本文整理...

    学习Excel技术,关注微信公众号:

    excelperfect

    VBA代码中,经常要引用单元格数据区域并对其进行操作。然而,如果对数据区域采用“硬编码”地址,那么当该区域大小变化时,必须修改相应的引用该区域的代码。本文整理了可以动态引用数据区域的5种方法,供编写代码时参考。

    方法1:使用UsedRange属性

    工作表对象的UsedRange属性返回一个Range对象,代表工作表中已使用的单元格区域。代码:

    Sub DynamicRange1()   '刷新已使用区域    ActiveSheet.UsedRange   '选择已使用区域    ActiveSheet.UsedRange.SelectEnd Sub

    方法2:使用Ctrl+Shift+向右箭头键/向下箭头键

    模拟使用快捷键Ctrl+Shift+右箭头,然后使用快捷键Ctrl+Shift+向下箭头来选择单元格区域。注意,如果第一行的最后一个单元格或者第一列的最后一个单元格为空,则本方法不会选择到正确的单元格区域。因此,本方法适用于数据区域的第一列在最后一行有值且第一行在最后一列有值的区域。

    Sub DynamicRange2()    Dim wks As Worksheet    Dim lngLastRow As Long    Dim lngLastColumn As Long    Dim FirstCell As Range      '设置工作表和数据区域起始单元格    Set wks = Worksheets("Sheet1")    Set FirstCell = Range("C3")       With wks        '获取数据区域第一列中有数据的最后一行行号        lngLastRow = .Cells(.Rows.Count,FirstCell.Column).End(xlUp).Row        '获取数据区域第一行中有数据的最后一列表号        lngLastColumn = .Cells(FirstCell.Row,.Columns.Count).End(xlToLeft).Column        '选择数据区域        .Range(FirstCell, Cells(lngLastRow,lngLastColumn)).Select    End WithEnd Sub

    方法3:SpecialCells方法——最后一个单元格

    使用SpecialCells方法来查找工作表中包含数据的最后一个单元格。找到后,使用该单元格引用来确定最后的数据行和列。

    Sub DynamicRange3()    Dim wks As Worksheet    Dim lngLastRow As Long    Dim lngLastColumn As Long    Dim FirstCell As Range      '设置工作表和起始单元格    Set wks = Worksheets("Sheet1")    Set FirstCell = Range("C3")      '刷新已使用单元格区域    Worksheets("Sheet1").UsedRange      '找到最后一行和列    With FirstCell.SpecialCells(xlCellTypeLastCell)        lngLastRow = .Row        lngLastColumn = .Column    End With      '选择单元格区域    wks.Range(FirstCell, wks.Cells(lngLastRow,lngLastColumn)).SelectEnd Sub

    方法4:使用CurrentRegion属性

    单元格对象的CurrentRegion属性返回当前单元格所在的数据区域,该区域上下左右都是空行。

    Sub DynamicRange4()    Dim wks As Worksheet    Dim FirstCell As Range      '设置工作表和起始单元格    Set wks = Worksheets("Sheet1")    Set FirstCell = Range("C3")      '选择单元格区域    FirstCell.CurrentRegion.SelectEnd Sub

    方法5:固定列

    很多情况下,工作表数据区域的列是固定的,只是随着数据的添加,行数发生变化。此时,可以使用下面的代码:

    Sub DynamicRange5()    Dim wks As Worksheet    Dim lngLastRow As Long    Dim lngLastColumn As Long    Dim FirstCell As Range      '设置工作表和起始单元格    Set wks = Worksheets("Sheet1")    Set FirstCell = Range("C3")      '刷新已使用单元格区域    Worksheets("Sheet1").UsedRange      '查找最后一行    lngLastRow = wks.Cells.Find("*",_        SearchOrder:=xlByRows, _        SearchDirection:=xlPrevious).Row          '选择单元格区域    wks.Range("C3:E" &lngLastRow).SelectEnd Sub

    上面介绍的方法适用情况稍有不同,你可以根据实际工作表数据来灵活选用。

    27bc8e35168c3d0b08c9360b3445d600.png

    展开全文
  • 创建一个名称来引用C列中的数据,又不希望这个引用区域包含空白单元格,这时创建动态名称可以满足这个需求,根据用户追加或删除数据的结果来自动地调整引用的位置,以达到始终引用非空白单元格区域的结果。...

    Excel创建动态名称自动调整引用位置以引用非空白单元格区域

    时间:2015-03-17   作者:snow   来源:互联网

    创建一个名称来引用C列中的数据,又不希望这个引用区域包含空白单元格,这时创建动态名称可以满足这个需求,根据用户追加或删除数据的结果来自动地调整引用的位置,以达到始终引用非空白单元格区域的结果。

    创建动态名称的步骤大概就是调出“定义名称”对话框,在当前工作薄中的名称位置输入名称,在引用位置处输入公式,确定即可。公式先计算C列中除了列标题以外的非空白单元格的数量,然后以C4单元格为基准开始向下定位,当引用位置发生变化时,B2中的计算结果可以体现这一点。

    1)单击菜单[插入]—[名称]—[定义];
    2)打开“定义名称”对话框,在“在当前工作薄中的名称”文本框中输入“Date”,在“引用位置”文本框中输入公式:=OFFSET(Sheet1!$C$4,,,COUNTA(Sheet1!$C:$C)-1),如下图所示,单击“确定”按钮。

    Excel创建动态名称

    以上公式先计算C列中除了列标题以外的非空白单元格的数量,然后以C4单元格(首个数据单元格)为基准开始向下定位,定位的行数等于刚才计算出来的数据。下面可以在C列以外的单元格中通过计算来验证此名称的引用是否正确,比如在B1中输入公式:=SUM(Date),如下图所示。

    Excel创建动态名称

    如果继续追加记录,名称“Date”的引用位置就会自动地发生改变,B2中的计算结果能够体现这一点,如下图所示。

    Excel创建动态名称

    注意:以上公式只能正确计算不间断的连续数据,如果表格中的数据有空白单元格,那么动态名称的引用位置将发生错误。

    以上便是为大家介绍的有关Excel如何创建动态名称以自动调整引用位置达到始终引用非空白单元格区域的结果,操作简单,比较实用,建议大家将其掌握,以在实战中应用。http://www.topdf.cn/pdftohtml

    展开全文
  • 1 EXCEL动态柱形图,关键词:动态柱形图制作要点:①下拉框、数据验证(数据有效性)②你看到的没错,在看不见的F26:P29单元格区域我藏了些源数据,然后在表格里设置各种引用公式。就像湖面上优雅的天鹅必须在看不见...

    de29c84e05433bb188f8596d284cdc77.png

    今天po一波EXCEL动态图表,感兴趣的小伙伴后台对话框输入关键词可索取源文件。

    1 EXCEL动态柱形图,关键词:动态柱形图

    4e9361412f99552b92b4d46d2a4133b7.gif

    制作要点:

    ①下拉框、数据验证(数据有效性)

    f2873b152d323d39424115eca2dd15ca.png

    ②你看到的没错,在看不见的F26:P29单元格区域我藏了些源数据,然后在表格里设置各种引用公式。

    就像湖面上优雅的天鹅必须在看不见的湖水下用力划水,很多看起来高大上的动态图表,其实也是一步步分拆,一次性设置好公式,图表美化好,以后直接套数据就可以了,说难也不难花点心思就好。

    3895a64d139a753ac3b5ec44d8108a07.png

    姓名B5=OFFSET($F$26,MATCH($B$3,$F$27:$F$29,0),ROW(B5)-4)

    成绩随机C5=RANDBETWEEN(50,100)

    最高D5=(C5=MAX($C$5:$C$14))*C5

    最低E5=(C5=MIN($C$5:$C$14))*C5

    2 EXCEL动态圆环图,关键词:动态圆环图

    2f4150213db0d9223d3c7480ec6ced90.gif

    3 EXCEL百分比圆环图,关键词:百分比圆环图

    26b9edc025cf356a6094c0d4198cc8bf.gif

    4 EXCEL动态组合图,关键词:动态组合图

    a7d788a051aad4b176b701ba66d75c55.gif

    小编有话说:『你必须很努力,才能看起来毫不费力。』只有苦练内功,才能随时信手拈来。

    【原文链接:EXCEL动态图表】

    3f078a7a9f31f1dbbeb237280ecc601b.png
    展开全文
  • Excel VBA实用技巧大全 附书源码

    热门讨论 2010-10-08 18:59:24
    04025利用Offset属性动态引用单元格区域 04026利用Resize属性引用变更为指定大小的单元格区域 04027引用不包括标题行的单元格区域 04028引用不包括标题列的单元格区域 04029引用任意单元格区域的右下角单元格(之一...
  • 直接框选单元格区域可以实现基本的操作需要,如果要实现动态单元格区域引用就无能为力了。这篇文章为朋友们分享一下最强大的单元格区域引用函数→offset函数的基本概念和应用实例。Offset函数自身只是一个单元格...

    在Excel表格输入公式时,单元格区域的引用是必不可少的;直接框选单元格区域可以实现基本的操作需要,如果要实现动态的单元格区域引用就无能为力了。这篇文章为朋友们分享一下最强大的单元格区域引用函数→offset函数的基本概念和应用实例。Offset函数自身只是一个单元格区域引用函数、并没有什么强大的功能。但是配合SUM、MATCH、COUNTIF等函数就可以完成很多很牛的操作。

    一.Offset函数基本概念:

    1.语法:Offset(reference, rows, cols, [height], [width]);

    各参数中文含义:offset(起始区域,向下偏移行数,向右偏移列数,返回的行数,返回的列数)

    2.各参数解释:

    (1)Reference:作为参照的单元格引用(不仅可以是单元格,也可以表示从一个区域开始进行偏移。)

    (2)Rows:向上或向下偏移的行数(向下为正数,向上为负数;如果这个参数省略表示不向上下偏移,即省略时默认为0)

    (3)Cols:向左或向右偏移的列数(向右为正数,向左为负数;如果这个参数省略表示不向左右偏移,即省略时默认为0)

    (4)Height:高度,需要返回的引用的行高, Height 必须为正数。(如果这个参数省略表示引用的高度与第一个参数Reference的高度相同。)

    (5)Width:需要返回的引用的列宽,Width 必须为正数。(如果这个参数省略表示引用的宽度与第一个参数Reference的宽度相同。)

    3.实例演示:

    (1)操作:选择E1:F4单元格输入公式=OFFSET(A1,3,1,4,2)同时按住键盘上的Ctrl+Shift+Enter确定公式。

    (2)解析:以A1单元格为参考,向下移动三行、向右移动一列得到的一个4行2列的单元格区域的引用。

    235dd144140d9940f1bfd33e123689b1.png

    二.应用实例:

    1.与SUM、MATCH函数配合使用实现动态求和。

    f980b2d75f3d03d4310eb77e028816ae.png

    (1)选择E2:F2单元格通过数据验证制作一个“月份”下拉列表;选择G2单元格通过数据验证制作一个“销量”下拉列表。

    (2)在H2单元格输入公式:

    =SUM(OFFSET(A1,MATCH(E2,A2:A10,0),MATCH(G2,B1:C1,0),MATCH(F2,A2:A10,0)-MATCH(E2,A2:A10,0)+1))

    (3)公式解析:

    ①A1单元格作为offset函数的参照单元格。

    ②MATCH(E2,A2:A10,0)查找E2单元格内容在A2:A10单元格区域的位置;例如,E2内容为2月时,返回的结果为2。

    ③MATCH(G2,B1:C1,0)查找G2单元格内容在B1:C1单元格区域的位置:例如,G2内容为实际销量时,返回的结果为2。

    ④MATCH(F2,A2:A10,0)-MATCH(E2,A2:A10,0)+1)中的MATCH(F2,A2:A10,0)查找F2单元格内容在A2:A10单元格区域的位置;例如,F2内容为5月时,返回的结果为5。计算结果5-2+1=4返回的是2月到5月的单元格区域的行数。

    ⑤offset函数的第五个参数省略,默认值与第一个参数A1单元格区域的列数相同、即第五个参数为1。

    ⑥最后使用SUM函数对OFFSET函数返回的单元格区域求和。

    (4)注意事项:

    因为OFFSET函数返回的是一个单元格区域、即返回结果为一个数组,所以一定要同时按住Ctrl+Shift+Enter三键确定公式。

    (5)动态演示:

    1e1ca3c87baca72975bac1619d272274.gif

    2.与COUNTIF、MATCH函数配合使用制作多级联动下拉列表。

    2c09e605bee00f9494910fd9ee80c69c.png

    (1)制作一级下拉菜单:

    选择省份下的单元格区域→切换到数据选项卡→数据验证→切换到设置选项界面→允许下选择“序列”→来源下方的对话框输入“辽宁省,吉林省,黑龙江省”→确定。

    (2)制作二级联动下拉菜单:

    选择市下的单元格区域→切换到数据选项卡→数据验证→切换到设置选项界面→允许下选择“序列”→来源下方的对话框输入下方公式→确定。

    =OFFSET($A$1,MATCH($D2,$A$2:$A$16,0),1,COUNTIF($A$2:$A$16,$D2))

    (3)公式解析:

    ①A1单元格作为offset函数的参照单元格,注意这里行和列的绝对引用。

    ②MATCH($D2,$A$2:$A$16,0)查找D2单元格的省份在A2:A16单元格第一次出现的位置。例如D2单元格内容为“黑龙江省”时,返回的结果为12。这里注意D2单元的只绝对引用列,不绝对引用行。

    ③因为市在省份的后一列,所以要向右偏移一列、即第三个参数为1。

    ④COUNTIF($A$2:$A$16,$D2)统计的是D2单元格的省份在A2:A16单元格区域出现的次数,也就是每个省份对应有几个市、即offset函数第四个参数引用单元格区域的行数。

    ⑤offset函数的第五个参数省略,默认值与第一个参数A1单元格区域的列数相同、即第五个参数为1。

    (4)注意事项:

    ①制作一级下拉菜单时,不同的的选项之间要用英文输入法下的逗号隔开。

    ②制作二级下拉菜单时,注意公式中对单元格的引用方式、不要混淆绝对引用和相对引用。

    (5)动态演示:

    c0a2bbbad5447ad1d0796b62d647dd48.gif

    总结,通过对OFFSET函数的基本概念的讲解和应用实例介绍相信你一定已经掌握这个函数的用法。如果有什么不懂的地方欢迎在评论区留言讨论。

    展开全文
  • EasyShu一开始的架构是将制作好的图表最终返回给用户,不依赖用户工作表的单元格区域引用,可满足图表绘制后的脱离数据源分享传播,无奈用户最强烈的反馈是要求图表与数据保持联动,这一需求实在对EasyShu是一个巨大...
  • 创建Excel动态超链接.rar,通过定义名称,可创建对单元格区域动态引用,若将其作为超链接的对象,则可获得动态的超链接。
  • EasyShu一开始的架构是将制作好的图表最终返回给用户,不依赖用户工作表的单元格区域引用,可满足图表绘制后的脱离数据源分享传播,无奈用户最强烈的反馈是要求图表与数据保持联动,这一需求实在对EasyShu是一个巨大...
  • Excel 动态图表设计

    2019-09-21 23:18:14
    Reference 必须为对单元格或相连单元格区域引用;否则,函数 OFFSET 返回错误值 #VALUE!。 Rows 相对于偏移量参照系的左上角单元格,上(下)偏移的行数。如果使用 5 作为参数 Rows,则说明目标引用区域的...
  • 通过设置数据验证,可以直接引用某个区域作为序列来源,完成下拉列表的效果,利用Offset和Counta函数实现动态引用的特点,实现下拉列表的自动扩展,这样生成的下拉列表中的条目能随数据源数量的增加或是减少而自动...
  • OFFSET(reference, rows,cols,[height], [width]) OFFSET是用来返回一个引用引用可以是单元格或者一个单元格区域的 referrence,rows,cols 确定了引用区域的参考单元格及引用返回区域相对于参考单元格的行列...
  • OFFSET动态图解

    2010-06-11 23:04:27
    Reference 必须为对单元格或相连单元格区域引用;否则,函数 OFFSET 返回错误值 #VALUE!。 Rows 相对于偏移量参照系的左上角单元格,上(下)偏移的行数。如果使用 5 作为参数 Rows,则说明目标引用区域的左上角...
  • reference:对一个或多个单元格区域引用。 row_num:引用中某行的行序号,函数从改行返回一个引用。 column_num:引用中某列的序列号,函数从该列返回一个引用。 area_num:选择引用中的一个区域,并返回该区域中...
  • offset函数是一个引用函数,表示引用某一个单元格或者区域 offset函数是以指定的应用为参考系,通过上下左右偏移得到新的区域引用。返回的引用可以是一个单元格也可以是一个区域。并且可以引用指定行列数的区域。 ...
  • 选中要输入内容的D2:D10单元格区域,数据→数据验证→序列,输入以下公式。=OFFSET($A$2,0,0,COUNTA($A:$A)-1)公式表示以A2作为基点,向下偏移0行,向右偏移0列,新引用的行数为COUNTA函数统计到的A列非空单元格个数...
  • Excel动态图表

    2017-09-20 09:05:00
    动态图表其实一点都不难,真的!...数据源区域就是要作为下拉选择的地区,单元格链接就是为了后面写公式引用的一个单元格。Step 04设置完,点击任一地区,比如内蒙,你会发现出现了一个数字,这个数字就...
  • 涉及函数: 1. MATCH(lookup_value, lookup_array, [match_type])。 lookup_value必需。要在lookup_array中匹配的值。lookup_value参数可以为值(数字、文本或...要搜索的单元格区域。 match_type可选。数字 ...
  • 使用Offset和Counta函数如下图所示可以定义动态名称,这个动态名称可以自适应引用A1单元格所在的连续数据区域动态名称虽然实现了自适应调整引用区域,但是这样定义的名称,无法像普通名称一样使用【数据】选项卡...
  • 而SORT函数可以引用数据区域并将排序结果返回到指定单元格区域中。SORT语法SORT函数的第一参数是必选参数,其它都是可选参数。如果忽略后面的所有可选参数,那么[sort_index]会默认为1,也就是以第一列或第一行作为...
  • Excel---经典动态图表

    2021-04-11 12:37:44
    《王佩丰Excel实战课1800分钟》复习笔记21 ...2、设置自动更新的数据区域:利用公式选择不同条件下的数据区域,将此公式定义名称,方便引用数据区域 3、插入一个空白的图表,手动设置数据源,将第二步的数
  • 而SORT函数可以引用数据区域并将排序结果返回到指定单元格区域中。SORT语法SORT函数的第一参数是必选参数,其它都是可选参数。如果忽略后面的所有可选参数,那么[sort_index]会默认为1,也就是以第一列或第一行作为...
  • Excel-动态环形图

    2019-11-01 14:00:41
    ③右键组合框控件-【设置控件格式】-【控制】,【数据源区域】选择源数据表中的【销售时间】表头下的内容填充,【单元格链接】选择任一空白单元格(如B7),默认为绝对引用,【下拉显示项数】可以根据...
  • 而SORT函数可以引用数据区域并将排序结果返回到指定单元格区域中。SORT语法SORT函数的第一参数是必选参数,其它都是可选参数。如果忽略后面的所有可选参数,那么[sort_index]会默认为1,也就是以第一列或第一行作为...
  • 而SORT函数可以引用数据区域并将排序结果返回到指定单元格区域中。SORT语法SORT函数的第一参数是必选参数,其它都是可选参数。如果忽略后面的所有可选参数,那么[sort_index]会默认为1,也就是以第一列或第一行作为...
  • 王佩丰数据透视表(六到十一讲)

    千次阅读 2018-06-24 15:03:35
    举个例子,在透视表外引用单元格B5,会显示即动态引用常熟区域的彩盒金额,此后如果常熟和彩盒的顺序改变了,该单元格也会动态调整,引用的数据仍然是常熟的彩盒。这个动态引用功能可以在数据透视表工具栏的分析——...
  • Excel图表制作(二):动态图表制作

    千次阅读 2016-01-21 10:01:58
    在图表要表示的数据较多时,可采用动态图的方式按照类别或其他分类方式动态显示不同部分的数据。本部分动态图的制作思路是使用函数和...offset函数是一个引用函数,表示引用某一个单元格或者区域。以指定的应用为参考系
  • 例如,如果公式引用单元格c2,并且更改了c2中包含的值,则公式的结果将自动反映新值。如果公式中值没有被使用引用,则需要编辑公式本身,以便更改公式中使用的值。在公式中使用单元格(或范围)引用时,可以使用三种...
  • 大家好,我是@OFFICE职场办公,专注...语法结构为:INDEX(array, row_num, [column_num])也就是:INDEX(数组区域,行数,列数)Array,也就是数组,是指引用值位于的数据区域row_num,行数,是指引用区域的第几行。co...
  • 首先,在开发工具中点击插入组合框(窗体控件),画出一个框,位置和大小适中即可(稍后调整),在这个框上右键选择设置控件格式,,数据区域选择A2:A7,单元格链接选择H1,注意绝对引用哦,下拉显示项...
  • EXCEL VBA常用技巧

    2010-02-09 22:28:27
    13-4 取得单元格中公式的引用单元格 40 13-5 将单元格中的公式转换为数值 41 技巧14 单元格中的批注 42 14-1 判断单元格是否存在批注 42 14-2 为单元格添加批注 43 14-3 删除单元格中的批注 44 技巧15 合并单元格...

空空如也

空空如也

1 2 3 4 5 6
收藏数 118
精华内容 47
关键字:

动态引用单元格区域