Excel &VBA交流学习 (一楼更新）

baiii

20100 发表于 2017-4-19 11:57
我们的系统是2016的，用了你说的办法，不太熟练，半自动半手工地做完了，真的非常感谢 。
给你 ...

不客气

一条大鱼

20100 发表于 2017-4-18 20:18
借这个贴也请教各位一个问题，请问是否可以用某个公式把左边的数据格式变成右边的数据格式？

        Jan                Feb         ...

手动+简单公式，不难。
关键是数据量有多大，跨年度就会有重复月份。
会不会有不同月份item不同的情况。

ExcelMVP

我不知道朋友们是否熟悉如何使用 Indirect 公式 in Excel, 这里我有一个有趣的问题, 如何使用Indirect 反转一个 List, 感兴趣的朋友可以尝试一下


Original Data                 Result
A                                    F
B                                    E
C                                    D
D                                    C
E                                    B
F                                    A

yi2005

大虾，加不上你威信，很有兴趣

20100

一条大鱼 发表于 2017-4-20 09:20
手动+简单公式，不难。
关键是数据量有多大，跨年度就会有重复月份。
会不会有不同月份item不同的情况。

一共有7个蔬菜item（我用水果举的例子，蔬菜看起来太枯燥）。
跨度3年，从2014到2016，蔬菜item固定不变，只不过有的月份有销售，有的月份没有销售。
好在数据量不大。

你说的简单公式，是不是index + match？

kikizhu7

ExcelMVP 发表于 2017-4-20 09:27
我不知道朋友们是否熟悉如何使用 Indirect 公式 in Excel, 这里我有一个有趣的问题, 如何使用Indirect 反转 ...

试了一下 不过感觉应该还有更好的方法 感觉我这个有点蠢的 需要多加一个辅助栏 c

A        F        1
B        E        2
C        D        3
D        C        4
E        B        5
F        A        6


‘=INDIRECT("A"&COUNTA(A:A)-C1+1)


楼主微信加不上

ExcelMVP

kikizhu7 发表于 2017-4-20 11:05
试了一下 不过感觉应该还有更好的方法 感觉我这个有点蠢的 需要多加一个辅助栏 c

A        F        1

如果不想多加一个辅助栏, 可以使用 Row() formula

Original Data         Result
A                            F                  =INDIRECT("A"&ROW($A$6)-ROW(1:1)+1)
B                            E
C                            D
D                            C
E                            B
F                            A

yi2005

所以您也是开培训班的了。。。。

david626

这个贴真的不错，楼主是不是能来个每周一题，大家一起来讨论。

kikizhu7

ExcelMVP 发表于 2017-4-20 11:57
不好意思, 微信号打错了, 应该是Jerry20251.

如果不想多加一个辅助栏, 可以使用 Row() formula

谢谢 我之前没有研究过indirect

不知道是不是有什么其他的妙用　

因为这样一来跟ｏｆｆｓｅｔ　和ｉｎｄｅｘ好像没什么不同　？

‘＝INDEX(A:A,COUNTA(A:A)-ROW(A1:A1)+1,1)

’＝OFFSET($A$1,COUNTA(A:A)-ROW(A1:A1),0)

darkjazz

kikizhu7 发表于 2017-4-20 14:38
谢谢 我之前没有研究过indirect

不知道是不是有什么其他的妙用　

我也是用offset 多，现在要看一下indirect 了

kikizhu7

darkjazz 发表于 2017-4-20 14:54
我也是用offset 多，现在要看一下indirect 了

哈哈 我也是 分享一个刚从Chandoo读到的comments

Limitations of INDIRECT Function:

*/ It could slow down your workbook, if used in many formulas.

*/ If the INDIRECT function creates a reference to another workbook, that workbook must be open, or the formula will result in a #REF! error. So if you want to call data from other workbook, firstly open the workbook from which workbook you want to calculate. According to the above example open the workbook Module-1.xlsx

*/ If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error in the version Excel-2007.

and this

One thing missing from all those links is a big warning about the fact that INDIRECT is volatile (along with OFFSET, TODAY, NOW, RAND, RANDBETWEEN and a few other functions).


What that means is that if you have volatile functions in your workbook, any time you make a change anywhere at all on the spreadsheet, Excel recalculates the value of any the volatile functions too. Excel then recalculates every applicable formula downstream of these functions too – regardless of whether anything upstream actually changed or not.


maybe its doesn't worth the time to look into after all?

ExcelMVP

kikizhu7 发表于 2017-4-20 14:38
谢谢 我之前没有研究过indirect

不知道是不是有什么其他的妙用　

哈哈, 你的 Index Formula 用的非常巧妙, 但可以更短一点

Original Data         Result
A                            F                  =INDEX($A$2 : $A$7,COUNTA(A2 : $A$7))
B                            E
C                            D
D                            C
E                            B
F                            A

Indirect 和 Offset 非常不一样. 而且 Indirect 用处更多.

kikizhu7

ExcelMVP 发表于 2017-4-20 16:32
哈哈, 你的 Index Formula 用的非常巧妙, 但可以更短一点

Original Data         Result

恩 这个简单很多 只是不知道怎么解决open end的list  

20100

baiii 发表于 2017-4-19 21:01
不客气

麻烦还要向你请教一个问题。从左边格式转换成右边格式，我通常借助一个region和branch的关系表（region和branch都是固定不变量）vlooup来搭配contract。但有时候一起处理的同一批contract里面的region常常重复，比如contract02和06，04和07（contract和item都是变量），vlookup就不会搭配06和07。如果不写vba的话，excel还有其他什么函数可以解决吗？谢谢！

bhyfire

厉害

baiii

20100 发表于 2017-4-20 20:45
麻烦还要向你请教一个问题。从左边格式转换成右边格式，我通常借助一个region和branch的关系表（region和 ...

没太看懂你什么意思，是vlookup一对多的问题么？
一个loolup value,可以返回多个量，你现在要寻找第二个符合条件的？

20100

baiii 发表于 2017-4-20 21:27
没太看懂你什么意思，是vlookup一对多的问题么？
一个loolup value,可以返回多个量，你现在要寻找第二个 ...

唉，不怪你看不懂我的意思，主要是我那个region和branch关系表并没有起到太大辅助的自动化的作用。
不过你说的一对多倒是提醒了我，我试一试，多谢多谢！

peter266

kikizhu7 发表于 2017-4-20 14:38
谢谢 我之前没有研究过indirect

不知道是不是有什么其他的妙用　

试了下index的方法不错， 即使把A-F转置成横向进行操作也能有正确结果， 其他Indirect与Offset两种就不行。

ExcelMVP

peter266 发表于 2017-4-21 10:43
试了下index的方法不错， 即使把A-F转置成横向进行操作也能有正确结果， 其他Indirect与Offset两种就不行 ...

一般用 Offset 做 Dynamic Named Range， 但是用 Indirect 一样可以Dynamic Named Range.

而且Indirect可以做3-D Sum,这个就比 Offset 要厉害

ExcelMVP

下面是一个日期格式的常见问题. 如果你在跨国公司工作, 大部分时间从海外收到的报告是美国格式的日期, 你如何转换成澳大利亚日期格式 in Excel? 希望朋友们可以提出不同的解决方案, 让我们来看一看谁的解决方案是最好的.

US Date          ----->    AUS Date
1/13/2007                   13/1/2017
1/14/2017                   14/1/2017
1/15/2017                   15/1/2017

floodp

实在没有发散性思维，只好年月日一段一段截。

=DATE(RIGHT(A2,4),LEFT(A2,SEARCH("/",A2)-1),MID(A2,SEARCH("/",A2)+1,SEARCH("/",A2,SEARCH("/",A2)+1)-SEARCH("/",A2)-1))

ExcelMVP

floodp 发表于 2017-4-24 20:42
实在没有发散性思维，只好年月日一段一段截。

=DATE(RIGHT(A2,4),LEFT(A2,SEARCH("/",A2)-1),MID(A2,SEARC ...

这个公式非常完美, 基本把 Text 公式用到极致.

也有其他朋友的解决办法是使用 Text to Column， 把美式日期分开成单独数字， 然后再使用 Date(Year, Month, Day)把它转换成澳洲日期.

Date                               Text to column                        Date Function
1/13/2017                       1     13     2017                       = Date(2017, 1 ,13)

还有更简单的办法, 看大家有没有人知道

kukupants

Lz  有没有 FFT > 4096 sampling data 的 vba code?

ExcelMVP

kukupants 发表于 2017-4-26 11:13
Lz  有没有 FFT > 4096 sampling data 的 vba code?

非常抱歉, 我没有FFT > 4096 sampling data 的 vba code.

如果您需要其他帮助我会尽我所能

baiii

我来问个excel chart的问题啊，下图的数据要转成图表：

第一步，建立双坐标图表，

第二步，highlight的数据在图表上要用不同颜色显示,类似于下面

我成功了一次，但是之后就复制不出了，最大的问题是highlighted的line不能完美衔接，如下

有没有高人能详细叙述下操作过程或者指出我哪里出了问题，拜谢

ExcelMVP

baiii 发表于 2017-4-27 10:35
我来问个excel chart的问题啊，下图的数据要转成图表：

第一步，建立双坐标图表，

选择具体数据点, 右键点击然后Format Data Point ->Line Color -> Solid Line -> change another color

Hope this will help.

sz2008

darkjazz 发表于 2017-4-7 19:10
试了一下，见笑

谢谢！

高手！学习了！

baiii

ExcelMVP 发表于 2017-4-27 11:48
选择具体数据点, 右键点击然后Format Data Point ->Line Color -> Solid Line -> change another color

...

line可以，但是stacked area貌似不行，选Solid Fill的话全换颜色了，我再研究下，可能没选中正确点~
我之前是re-shape数据表格，如下，建立单独的series，但是line的衔接断了，我第一次不知道怎么成功了，后来就不行了

20100

baiii 发表于 2017-4-27 12:15
line可以，但是stacked area貌似不行，选Solid Fill的话全换颜色了，我再研究下，可能没选中正 ...

要不然试一试change chart type，然后选最左下面的Combo，然后再换一下chart type。