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

20100

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

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

baiii

20100 发表于 2017-4-27 22:45
要不然试一试change chart type，然后选最左下面的Combo，然后再换一下chart type。

...

哈哈，我第一次就是这么做出来的，但是再做的时候就做不出了，line不能衔接好，不知道哪里出了问题

20100

baiii 发表于 2017-4-27 22:58
哈哈，我第一次就是这么做出来的，但是再做的时候就做不出了，line不能衔接好，不知道哪里出了问题 ...

还真是，这个办法好像只能显示连接两个相邻的点，单独的点就显示不出来了

p.s.我把上图改正了，growth的chart type，我用的是Line，不是stacked line。

ExcelMVP

ExcelMVP 发表于 2017-4-25 23:23
这个公式非常完美, 基本把 Text 公式用到极致.

也有其他朋友的解决办法是使用 Text to Column， 把美式 ...

另一种方式使用 Text to Column --> Change Column Date Format,  也能把美式日期转换成澳洲日期.

mfhxilc2

太好了， 终于有人可以问了

下面这个表格， 能不能按照job这一列 （F）来combine第三列 （H）的text?   搞了一下午了， 没搞出来

因为数据很多， 最好不用macro，我用下面的公式总是出错


{=textjoin(",",TRUE,IF($F$2F$17=F31,$H$2H$17," "))}




Job               SAP                 COMMENTS
BG5AS        C-72433        A
BG5AS        C-72433        B
BG5AS        C-72433        C
BG5AS        C-72433        D
BG5AS        C-72433        E
BG5AS        C-72433        F
BG5AS        C-72433        G
BG5AS        C-72433        H
BG57K        C-50213        Tenant Relocation Completed
BG57K        C-50213        Tenant Relocation Completed
BG57K        C-50213        Tenant Relocation Completed
BG57K        C-50213        Tenant Relocation Completed
BG57K        C-50213        Tenant Relocation Completed
BG57K        C-50213        Tenant Relocation Completed
BG1S8        C-71971        Vacant - Development site
BG1S8        C-71971        Vacant - Development site




谢谢啦

mfhxilc2

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

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

简单方法是把两列数据按照你的要求变成四列，同类数据使用一个坐标，你有两类，可以把第二类plot到secondary坐标上，就可以了

mfhxilc2

mfhxilc2 发表于 2017-5-1 16:11
太好了， 终于有人可以问了

下面这个表格， 能不能按照job这一列 （F）来combine第三列 （H）的text?    ...

做了个样本文件， 请Jerry老兄和各位大神帮忙看看，谢谢了


最好能用formula, 不行的话只有手动 或者是macro,

ExcelMVP

mfhxilc2 发表于 2017-5-1 21:47
做了个样本文件， 请Jerry老兄和各位大神帮忙看看，谢谢了

您可以使用PivotTable, put both fields in "Row Labels" area, 然后它将生成如下表 (Tabular Layout, No Subtotal, No Grand Total).

Job                      HNSW COMMENTS
BG1S8              GH
BG1S8              MM
BG57K              A1
BG57K              A2
BG57K              A3
BG57K              A4
BG57K              A5
BG57K              A6
BG5AS              A
BG5AS              B
BG5AS              C
BG5AS              D
BG5AS              E
BG5AS              F
BG5AS             G
BG5AS          H

然后你可以使用 "Transpose" function. 例如 for BG5AS, use =transpose(B10:B17&",")

paramita

baiii 发表于 2017-4-27 22:58
哈哈，我第一次就是这么做出来的，但是再做的时候就做不出了，line不能衔接好，不知道哪里出了问题 ...

端点要重合。或者完全可以画上两条线，不同色的在上面就行了。

mfhxilc2

ExcelMVP 发表于 2017-5-2 08:52
您可以使用PivotTable, put both fields in "Row Labels" area, 然后它将生成如下表 (Tabular Layout, No ...

谢谢Jerry, 终于解决了

ExcelMVP

其实我发现初学 Excel 很容易分不请 Copy, Paste, Cut, Insert 的区别，不是太清楚在不同情况下如何使用这几种不同的功能. 下边有一个小测试，在不能做任何移动 A 列和 D 列的情况下如何 (Swap) 对换 B 列和 C 列.

这是一个很有趣的小问题，请朋友们各抒己见

floodp

select the B column
ctrl-x
select D column
right click selected D column
insert cut cells

ExcelMVP

floodp 发表于 2017-5-10 13:18
select the B column
ctrl-x
select D column

Alternative way is hold down Shift key, drag the right border of Column B, move to the right then put it behind Column C.

doll-bear

我来问大家一个问题吧，如何在一串地址里面找出suburb：
cell value is 115-121 Blaxland Rd, Ryde NSW 2112
How to get the result "Ryde"?
我自己用的是search，len和mid，不知大家有何高见

haroldann

doll-bear 发表于 2017-5-16 10:20
我来问大家一个问题吧，如何在一串地址里面找出suburb：
cell value is 115-121 Blaxland Rd, Ryde NSW 211 ...

按逗号text to columns，然后右边一列按空格text to columns

baiii

doll-bear 发表于 2017-5-16 10:20
我来问大家一个问题吧，如何在一串地址里面找出suburb：
cell value is 115-121 Blaxland Rd, Ryde NSW 211 ...

我也遇到过相同问题，除非你所有的地址都是统一格式的，不然excel套公式分割出来的结果很乱。
如果街道后面都是逗号的话可以直接text to column分割出suburb，state和postcode，然后替换掉数字（postcode)和7个state（大写，都是缩写？），会需要一些手动操作，替换部分可以写个macro。前提是所有地址都能按你的例子输入，我在实际中从来没有碰到过这么好的情况
对于不规则地址或者大数据量，需要一些高级的数据操作应用或者买第三方的地址做匹配

baiii

haroldann 发表于 2017-5-16 10:47
按逗号text to columns，然后右边一列按空格text to columns

如果suburb是两个单词呢？比如xxx north/south，你的方法只能取到xxx

haroldann

haroldann 发表于 2017-5-16 10:47
按逗号text to columns，然后右边一列按空格text to columns

回复完以后发现自己的答案好简单粗暴啊，如果地址有两个逗号的话，比如unit 2, 115-121 Blaxland Rd, Ryde NSW 2112，可能会不行，于是我又想到一个更简单粗暴的，选中全部，查找星号逗号空格，替换不填，可以把整张表只剩下Ryde NSW 2112，再按空格text to columns

haroldann

baiii 发表于 2017-5-16 10:50
如果suburb是两个单词呢？比如xxx north/south，你的方法只能取到xxx

这种情况我也没考虑，那就到这一步用left和len，=LEFT(A1,LEN(A1)-9), 邮编都是四位数，州应该都是3个字母吧？

baiii

haroldann 发表于 2017-5-16 11:08
这种情况我也没考虑，那就到这一步用left和len，=LEFT(A1,LEN(A1)-9), 邮编都是四位数，州应该都是3个字 ...

nope, SA WA NT... ...而且还有工业地址，可不像居住地址这么容易规范的，当然也要取决于提问的朋友的实际需求了。
除非地址都是很规范的（现实是不太可能的），Excel对于地址这种问题没有很好的解决方法的，至少我没遇到过很规范的

haroldann

baiii 发表于 2017-5-16 11:13
nope, SA WA NT... ...而且还有工业地址，可不像居住地址这么容易规范的，当然也要取决于提问的朋友的实 ...

如果州都是两个字母或者3个字母，就用=LEFT(A2,LEN(A2)-8)，然后把空格trim掉吧

规范地址确实很麻烦，excel的公式只能基于源数据本身存在规律，如果没有规律可循就只能手动了，苦

doll-bear

haroldann 发表于 2017-5-16 11:05
回复完以后发现自己的答案好简单粗暴啊，如果地址有两个逗号的话，比如unit 2, 115-121 Blaxland Rd, Ryd ...

谢谢你的回复，觉得这种简单的办法也很好，按照其他朋友说的，地址源本身没有那么规范，用了公式之后还是有可能需要手动的过程。我这个list差不多两百个地址，用哪种方法都差不多时间

doll-bear

baiii 发表于 2017-5-16 10:49
我也遇到过相同问题，除非你所有的地址都是统一格式的，不然excel套公式分割出来的结果很乱。
如果街道后 ...

txt to column方法不错，我是查找逗号的位置再截取。但是就像你说的一样，逗号有可能不只一个，还是不太规范

Goodkeep

求问各位大侠 data是原始数据 ABC三个sheet分别是cardholder 我想问下如何用公式实现把data sheet里面A相关的信息放到sheetA里面 以此类推 多谢啦

ExcelMVP

Goodkeep 发表于 2017-5-17 16:34
求问各位大侠 data是原始数据 ABC三个sheet分别是cardholder 我想问下如何用公式实现把data sheet里面A相关 ...

Create a PivotTable, then put Cardholder in Report Filter, go to PivotTable Options click "Show Report Filter Pages", this will automatically generate pages for different card holder.

ssblack

haroldann 发表于 2017-5-16 12:08
如果州都是两个字母或者3个字母，就用=LEFT(A2,LEN(A2)-8)，然后把空格trim掉吧

规范地址确实很麻烦，ex ...

很有趣的问题也都是很好的方法，我想了想查了查觉得可以用你的replace *，然后提取四位post code look up 第三方suburb list。能想到的问题有如果区前的，不是最后一个，另外不知道有多少区share post code需要两个column手动对比一下。

xiaobaipp

doll-bear 发表于 2017-4-12 10:22
工作中也会用到一些macro，我一般都是google一个solution然后根据自己的情况慢慢改 ...

我也这样哈哈

xiaobaipp

本站不支持任何群建起来了么？

steveking

xiaobaipp 发表于 2017-5-19 12:32
本站不支持任何群建起来了么？

谢谢提醒。本版不支持任何群，请大家在论坛分享。

ExcelMVP

我希望大家可以分享一些你最喜欢的Excel技巧, 如你最喜欢的 Shortcut.

在我的日常工作中最常用的 Shortcut 是 Ctrl + Shift + L, then Alt + Down Arrow, then Space Bar