创建到另一个工作簿中已定义名称的外部引用的帮助(Excel)
打开将包含外部引用的工作簿(目标工作簿)和包含要链接到的数据的工作簿(源工作簿)。
在源工作簿中,单击快速访问工具栏上的“保存” 。
选择要在其中创建外部引用的单元格。
键入 =(等号)。如果要对外部引用值执行计算或函数,请键入要置于外部引用之前的运算符或函数。
在“视图”选项卡上的“窗口”组中,单击“切换窗口”,单击源工作簿,然后单击要链接到的单元格所在的工作表。
创建到另一个工作簿中已定义名称的外部引用的帮助(Excel)
打开将包含外部引用的工作簿(目标工作簿)和包含要链接到的数据的工作簿(源工作簿)。
在源工作簿中,单击快速访问工具栏上的“保存” 。
选择要在其中创建外部引用的单元格。
键入 =(等号)。如果要对外部引用值执行计算或函数,请键入要置于外部引用之前的运算符或函数。
在“视图”选项卡上的“窗口”组中,单击“切换窗口”,单击源工作簿,然后单击要链接到的单元格所在的工作表。转载于:https://www.cnblogs.com/heartstill/archive/2011/12/19/2293416.html
Hello,大家好,对于Excel汇聚相同格式的数据可能大家都通过不同的方式做过,比如说:在中基于一个工作簿内部可以实现将相同格式的各个工作表数据汇聚到一个工作表上去,用得最多的是通过数据引用的方式或者高级一点即是通过VBA后台进行操作实现将各个工作表的数据拷贝到一个工作表里边。但是,我们想说的是,如果要从该工作簿隶属的文件夹下外部分散的各个工作簿文件(关键是这些文件中数据条数不尽相同)中如何实现批量汇聚外部相同格式的数据呢?可能很多朋友没有尝试过吧,这就涉及到跨文件的内部数据拷贝了哦!
对于跨文件的操作,可能大家涉及的不太多,有关注我头条作品的粉丝可能发现近几次我都或多或少基于VBA实现了文件的读写操作。搞清楚了VBA的文件读写操作过程原理,其实一切事都不是事了。对于文件的操作无非就是这么三个过程:文件打开-->文件数据读/写-->文件关闭。
下面,我就给大家分享一下我自己通过VBA后台实现的的批量导入外部相同格式数据的技术方法的过程吧,希望对大家有所帮助哦!实现步骤如下:
一、简单的宿主Excel工作簿指定工作表界面设计。
在工作表上插入两个命令按钮,起到很好的交互作用,并设置相应属性;再在宿主工作表做一个数据表头。如下图所示
图1 宿主工作表界面
二、宿主工作簿外部隶属文件夹下相同格式工作簿资源文件及其打开后数据展示。如下图
图2 外部数据工作簿文件资源
图3 全部打开的外部工作簿文件资源数据
三、开发工具VBA后台功能代码全部实现
“Sheet1”代码如下:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If UsedRange.Count - 2 = -1 Then
Sheets(3).Cells(1, 1) = 2 '间接重置跟踪的导入新数据的起始行变量k的数值
End If
End Sub
Private Sub ImporetOutsideDatasBtn_Click()
Call Import_Data_Batched_From_OutSide
End Sub
Private Sub CleanImportedDatasBtn_Click()
Call Clean_Imported_Datas
End Sub
“模块1”代码如下:
Public k As Integer 'k是全局公有变量,用于跟踪下一个文件数据应该导入的起始位置第几行
Public rg As Range '定义一个全局公有区域范围变量rg对象
Public ExcelFileName(65535) As String '定义一个全局公有字符串数组ExcelFileName()
Public times As Integer 'times为跟踪操作的次数
Public max_col_1 As Integer, max_col_2 As Integer
Sub Import_Data_Batched_From_OutSide() '批量导入相同表格格式的Excel外部文件数据
Dim wb As Workbook, curSheet As String, rg1 As Range, rg2 As Range
Set rg = Sheets(1).UsedRange
max_row = rg.Rows.Count
Set rg = Sheets(1).Range("A2:VI65535")
Set rg1 = Sheets(3).Range("2:2")
Set rg2 = Sheets(3).Range("3:3")
If max_row - 2 = -1 Then '由于没有数据的情况下,max_row必然返回1,这里的2是指从第2行开始导入数据
ys = MsgBox("没有数据,是否批量整体导入外部数据?
在不知道引用格式的情况下,通常用以下方式来处理:
1、打开需要进行连接的两个表格;
2、复制表格中所需的数据(单元格/区域);
3、在"选择性粘贴"中将源数据链接粘贴到另一个表格中;
可以发现,格式形如:
- =DDE("soffice","/home/lynx/Desktop/工作相关/物料需求.ods","物料需求.B34")
在OOo帮助文档中关于DDE function的解释如下:
------------------------------------------------------------
DDE
Returns the result of a DDE-based link. If the contents of the linked range or section changes, the returned value will also change. You must reload the spreadsheet or choose Edit - Links to see the updated links. Cross-platform links, for example from a OpenOffice.org installation running on a Windows machine to a document created on a Linux machine, are not allowed.
Syntax
DDE("Server"; "File"; "Range"; Mode)
Server is the name of a server application. OpenOffice.orgapplications have the server name "Soffice".
File is the complete file name, including path specification.
Range is the area containing the data to be evaluated.
Mode is an optional parameter that controls the method by which the DDE server converts its data into numbers.
Mode
Effect
0 or missing
Number format from the "Default" cell style
1
Data are always interpreted in the standard format for US English
2
Data are retrieved as text; no conversion to numbers
------------------------------------------------------------
其二,在OOo中文指南中有介绍,连接为:(http://ooo.pingju.org/wiki/Calc:%E5%BC%95%E7%94%A8%E5%85%B6%E4%BB%96%E6%96%87%E6%A1%A3)
格式:
- ='file:///Path & File Name'#$SheetName.CellName
例如:
- ='file:///home/lynx/Desktop/工作相关/物料需求.ods'#$物料需求.B34
当然方法二比较简单,并且若是在同一个目录地下的excel文件,则可以简写为: ='物料需求.ods'#$物料需求.B34
(
可是我在lotus symphony下怎么就是试不出来第二种方法呢)
转载于:https://blog.51cto.com/lynxz/754375
更新: 2019-02-16 第一次发布,使用INDEX函数 2019-02-21 更新引用公式 2019-02-23 使用OFFSET函数
背景:
想要做一张物料卡表头,内容如图一所示,现在要把数百个物料填入表头,手工录入即容易出错,工作强度又大,效率极低。
那么可以先将物料信息预先录入工作簿的另一张表中,如图二所示,让表头栏自动调用这张表中的数据:
分析
分别将2张表定义为A表和B表,A表的第一行对应B表的第一行,A表的第四行对应B表的第二行,以此类推,则对应关系如下表所示:
A | B |
---|---|
1 | 1 |
4 | 2 |
7 | 3 |
10 | 4 |
13 | 5 |
16 | 6 |
19 | 7 |
22 | 8 |
25 | 9 |
从表中可知A列为差值3的等差数列,要将A列数值与B列数值一一进行对应,可使用下列表达式:1=1+0,4=1+3,7=1+6……
1=1+(1-1),4=1+(2-1)*3,7=1+(3-1)*3,……
得出公式A=1+(B-1)*3,翻转AB在等式两边的位置:B=(A+2)/3
现在我们可以用ROW函数得到A表的行数数值,将之代入到公式中,即可得到B表中相应数据所在的行数,例如在A表的第25行输入公式,经过换算后就可得到B表的第9行数据了。
公式为=(ROW()+2)/3
INDEX函数的第一个参数定义所要引用的数据范围为B表中的A列~D列,将之前所得公式代入第二个参数,得到该单元格对应于B表中的相应行号,第三个参数列号取1,即B表中的A列,如此即可得到编码值。
将公式复制进材料名称栏,将第三个参数列号改为2,即B表中的B列,就可以得到物料名称数据值;改为3则可以得到规格数据值。
重新思考分析过程,A表中为差值3的等差数列,那么将A表中的数除以3,然后向下取整,则可得到0,1,2,3,……连续数,再加1即可得到B表中的对应数值。
那么公式可修改为=INT(ROW()/3)+1
(注:不过这个公式增加了一个取整函数,对资源利用率应该是不如上个公式的)
新学到一个函数OFFSET,以指定的引用为参考值,通过给定的偏移量返回新的引用
如图的表格,每隔8行调用数据,从第三行开始填充数据,可填写如图公式达到目的
OFFSET函数的语法格式:OFFSET(定义引用值,行偏移量,列偏移量,引用多少行,引用多少列)
例如OFFSET(A1,2,3,1,1)
表示从A1单元格向下偏移2行,向右偏移3列所在的单元格,即C3单元格。如果要向上或向左引用,数字为负值。
ROW()/8,除非遇到8的倍数行,否则所得值为一小数值,而且其值的整数位从0开始,达到或超过8的倍数行加1,用这个特性,将预录入编码表的A1为绝对引用值,第一个计算值0引用A1本身,第二个计算值1引用从A1向下偏移1的单元格,以此类推。