当前位置：首页 → 问答吧 → 想问一条excel公式问题

想问一条excel公式问题

时间：2014-05-30

来源：互联网

因为宜家经常有份报表要用好多时间处理,想知有冇公式帮到手

例如:
有两栏数字:
A B
1400183 0018
1401597 1597
1400317 0317
0104
1467

当A栏中包含B栏则传回yes,否则no

想知有冇呢条公式,想请教各位师兄

[ 本帖最后由 siu3010 於 2014-5-14 10:28 PM 编辑 ]

作者: siu3010 发布时间: 2014-05-30

=IF(COUNT(FIND(B1,A1)),TRUE,FALSE)

作者: me888 发布时间: 2014-05-30

引用:原帖由 me888 於 2014-5-14 11:01 PM 发表
=IF(COUNT(FIND(B1,A1)),TRUE,FALSE)

师兄,都系唔得,睇下图,明明右边有2222,左边有1402222,但出现false
我发现呢个方法系得,但系只可以系同一行先计到数
因我既公事会系例如:在1万个items中找出包含1400,1401,1402,1403呢4个数字

附件未命名.JPG (97.94 KB)

2014-5-14 11:15 PM

未命名.JPG (97.94 KB)

2014-5-14 11:15 PM

作者: siu3010 发布时间: 2014-05-30

http://club.excelhome.net/forum. ... &threads=thread

作者: me888 发布时间: 2014-05-30

引用:原帖由 siu3010 於 2014-5-14 10:14 PM 发表
因为宜家经常有份报表要用好多时间处理,想知有冇公式帮到手
例如:
有两栏数字:
A B
1400183 0018
1401597 1597
1400317 0317
...

A B C 1 1400183 159x7 yes =IF( SUM( IF( ISERROR( FIND($B$1

B$5,A1) ), 0, 1 ) ) > 0, "yes", "no") 2 1401597 1598 no =IF( SUM( IF( ISERROR( FIND($B$1

B$5,A2) ), 0, 1 ) ) > 0, "yes", "no") 3 1400317 0018 yes =IF( SUM( IF( ISERROR( FIND($B$1

B$5,A3) ), 0, 1 ) ) > 0, "yes", "no") 4 0317 5 0104

C1, C2, C3 are "array formula", illustrated on the right

作者: stevie1 发布时间: 2014-05-30

please replace

with
: $
(no space in between)

作者: stevie1 发布时间: 2014-05-30

引用:原帖由 stevie1 於 2014-5-14 11:30 PM 发表
A B C 1 1400183 159x7 yes =IF( SUM( IF( ISERROR( FIND($B$1

B$5,A1) ), 0, 1 ) ) > 0, "yes", "no") 2 1401597 1598 no =IF( SUM( IF( ISERROR( FIND($B$1

B$5,A2) ), 0, 1 ) ) > 0, "yes", "no") 3 1400317 ...

师兄,唔该你先,但都系唔得

好似图咁,明明b1有1597,但计出错变左no

都系一样要同一行先验证到

附件未命名.JPG (40.65 KB)

2014-5-15 12:17 AM

未命名.JPG (40.65 KB)

2014-5-15 12:17 AM

作者: siu3010 发布时间: 2014-05-30

可以用 vba。

唔用的话：
1. 假设 Column A 无 ";"，当有 A1-A10;column B 系你要搵的
2. 系 C1: concentate (A1,;")
3. 系 C2-C10: concentate (C1,B2,";")
4. D1: find(B1, C1)

经测试，应该 work

作者: pc_chai 发布时间: 2014-05-30

oh. please change your number to "text" format.

作者: stevie1 发布时间: 2014-05-30

both columns A & B, otherwise, my formula won't work.

作者: stevie1 发布时间: 2014-05-30

我又玩…假设
1）只系用columnA尾果四粒位去对columnB
2）columnB啲数冇leading zero的
3）如果columnA系1400183，columnB有18系唔match的，一定要match183

响C1入呢个formula吧

复制内容到剪贴板代码:=IF(ISERROR(MATCH(VALUE(RIGHT(A1,4)),$B$1:$B$6, 0)), "no", "yes")

作者: skww 发布时间: 2014-05-30

[B2] is target code (4no)
[A$1:A$1000] is array area
any cell type array formula then press Ctrl+Shift+Enter creat {...}

before=IFERROR(VLOOKUP(B2,VALUE(RIGHT(A$1:A$1000,4)),1,FALSE)=B2,FALSE)

after={IFERROR(VLOOKUP(B2,VALUE(RIGHT(A$1:A$1000,4)),1,FALSE)=B2,FALSE)}

作者: alee001 发布时间: 2014-05-30

引用:原帖由 pc_chai 於 2014-5-15 09:13 AM 发表
可以用 vba。

唔用的话：
1. 假设 Column A 无 ";"，当有 A1-A10;column B 系你要搵的
2. 系 C1: concentate (A1,;")
3. 系 C2-C10: concentate (C1,B2,";")
4. D1: find(B1, C1)

经测试，应该 work

师兄,唔系好明,我excel新手

作者: siu3010 发布时间: 2014-05-30

引用:原帖由 stevie1 於 2014-5-15 11:23 AM 发表
oh. please change your number to "text" format.

将a栏同b栏改左左文字

都系唔得,正常应该3个result都系yes先岩

附件未命名.JPG (41.76 KB)

2014-5-15 07:44 PM

未命名.JPG (41.76 KB)

2014-5-15 07:44 PM

作者: siu3010 发布时间: 2014-05-30

引用:原帖由 skww 於 2014-5-15 11:25 AM 发表
我又玩…假设
1）只系用columnA尾果四粒位去对columnB
2）columnB啲数冇leading zero的
3）如果columnA系1400183，columnB有18系唔match的，一定要match183

响C1入呢个formula吧=IF(ISERROR(MATCH(VALUE ...

师兄,都系唔得,正常3个result都系yes

附件未命名.JPG (41.01 KB)

2014-5-15 07:46 PM

未命名.JPG (41.01 KB)

2014-5-15 07:46 PM

作者: siu3010 发布时间: 2014-05-30

引用:原帖由 alee001 於 2014-5-15 01:17 PM 发表
is target code (4no)
[A$1:A$1000] is array area
any cell type array formula then press Ctrl+Shift+Enter creat {...}

before=IFERROR(VLOOKUP(B2,VALUE(RIGHT(A$1:A$1000,4)),1,FALSE)=B2,FALSE)

af ...

师兄,变左错误

系咪我打错公式??

附件未命名.JPG (44.65 KB)

2014-5-15 07:48 PM

未命名.JPG (44.65 KB)

2014-5-15 07:48 PM

作者: siu3010 发布时间: 2014-05-30

你想B栏输入几个位code先，将RIGHT(A$1:A$1000,个位)改完就得。

作者: alee001 发布时间: 2014-05-30

引用:原帖由 alee001 於 2014-5-15 10:02 PM 发表
你想B栏输入几个位code先，将RIGHT(A$1:A$1000,个位)改完就得。

B栏会有6位同7位code

最大值7位，咁应该打咩？

作者: siu3010 发布时间: 2014-05-30

引用:原帖由 siu3010 於 2014-5-15 07:44 PM 发表

将a栏同b栏改左左文字

都系唔得,正常应该3个result都系yes先岩

试下用"search" functiion, 在c1打=IF(ISERROR(SEARCH(B1,$A$1

A$3))=TRUE,"no","yes"),应该处理到唔同长度数字

作者: 大愚若智发布时间: 2014-05-30

如果A栏固定7个，B栏就输入想搵A栏的后n个位而公式就要改为...RIGHT(A$1:A$1000,n)...但n唔可多个7
e.g
1401597搵597就改...RIGHT(A$1:A$1000,3)...
1401597搵1597就改...RIGHT(A$1:A$1000,4)...
1401597搵7就改...RIGHT(A$1:A$1000,1)...
1401597搵1401597就改...RIGHT(A$1:A$1000,7)...
如果唔定B栏搵几多位咁就改...RIGHT(A$1:A$1000,LEN(B1))...

[ 本帖最后由 alee001 於 2014-5-16 12:39 AM 编辑 ]

作者: alee001 发布时间: 2014-05-30

引用:原帖由大愚若智於 2014-5-16 12:01 AM 发表

试下用"search" functiion, 在c1打=IF(ISERROR(SEARCH(B1,$A$1

A$3))=TRUE,"no","yes"),应该处理到唔同长度数字

师兄,都系唔得,正常3个RESULT都系YES

附件未命名.jpg (71.79 KB)

2014-5-16 10:37 AM

未命名.jpg (71.79 KB)

2014-5-16 10:37 AM

作者: siu3010 发布时间: 2014-05-30

array formula =IF(COUNT(FIND(b17,$A$1

A$18)),"Yes","No")

作者: mtam 发布时间: 2014-05-30

引用:原帖由 alee001 於 2014-5-16 12:31 AM 发表
如果A栏固定7个，B栏就输入想搵A栏的后n个位而公式就要改为...RIGHT(A$1:A$1000,n)...但n唔可多个7
e.g
1401597搵597就改...RIGHT(A$1:A$1000,3)...
1401597搵1597就改...RIGHT(A$1:A$1000,4)...
1401597搵7就改 ...

师兄,都系唔得,正常3个RESULT都系YES

附件未命名.jpg (93.32 KB)

2014-5-16 10:40 AM

未命名.jpg (93.32 KB)

2014-5-16 10:40 AM

作者: siu3010 发布时间: 2014-05-30

自己试左条公式,但只能做到"一"找"多",做唔到"多"找"多"

如果我唔用$A$1 既话,RESULT又会全部变做NO

附件未命名.jpg (71.09 KB)

2014-5-16 11:40 AM

未命名.jpg (71.09 KB)

2014-5-16 11:40 AM

作者: siu3010 发布时间: 2014-05-30

引用:原帖由 siu3010 於 15/5/2014 19:46 发表

师兄,都系唔得,正常3个result都系yes

因为假设嘅第三点1401597会去搵1597，597系match唔到的

所以你要写好requirement

作者: skww 发布时间: 2014-05-30

分多几个 column 嚟整得唔得？

Col C 做3个位， D 就4个

作者: hum~ 发布时间: 2014-05-30

你改完都未按Ctrl+Shift+Enter列阵公式{...}

作者: alee001 发布时间: 2014-05-30

复制内容到剪贴板代码:=IF(IFERROR(MATCH(VALUE(RIGHT(A1,1)),$B$1:$B$6, 0),0) + IFERROR(MATCH(VALUE(RIGHT(A1,2)),$B$1:$B$6, 0),0) + IFERROR(MATCH(VALUE(RIGHT(A1,3)),$B$1:$B$6, 0),0) + IFERROR(MATCH(VALUE(RIGHT(A1,4)),$B$1:$B$6, 0),0) + IFERROR(MATCH(VALUE(RIGHT(A1,5)),$B$1:$B$6, 0),0) + IFERROR(MATCH(VALUE(RIGHT(A1,6)),$B$1:$B$6, 0),0) + IFERROR(MATCH(VALUE(RIGHT(A1,7)),$B$1:$B$6, 0),0) = 0, "no", "yes")

由尾数起1至7位全都对比了，但不能比较中间…

例如1401597
B栏有
1401597, 401597, 1597, 597, 97, 7 都会回传yes
但如果B栏中有字串中间的 159，15， 4015，…这些是no
B栏如有leading 0，例如0159也是no，因为value()后都变成数值去做比较了

作者: skww 发布时间: 2014-05-30

引用:原帖由 alee001 於 2014-5-16 12:21 PM 发表
你改完都未按Ctrl+Shift+Enter列阵公式{...}

用左,都系唔得

附件未命名.jpg (99.52 KB)

2014-5-16 02:21 PM

未命名.jpg (99.52 KB)

2014-5-16 02:21 PM

作者: siu3010 发布时间: 2014-05-30

引用:原帖由 skww 於 2014-5-16 12:32 PM 发表
=IF(IFERROR(MATCH(VALUE(RIGHT(A1,1)),$B$1

B$6, 0),0) + IFERROR(MATCH(VALUE(RIGHT(A1,2)),$B$1

B$6, 0),0) + IFERROR(MATCH(VALUE(RIGHT(A1,3)),$B$1

B$6, 0),0) + IFERROR(MATCH(VALUE(RIGHT(A1,4)),$B$1

B ...

例如:
A B
LT-1400123 130069
LT-1400124 1400127
LT-1400125 1400126
LT-1400126 1400125
LT-1400127 1400124
LT-1300691 1400123

用A去搜寻B,正常呢度全部RESULT等於YES,呢个就系我想要既公式
A一定要包含B显示既所有数字,例如LT-1400123,如果B只有1400就搵唔到,一定要有1400123先搵到
A既LT-1300691,只要B既全部数字=130069,RESULT都系YES,因为A包含130069

[ 本帖最后由 siu3010 於 2014-5-16 02:36 PM 编辑 ]

作者: siu3010 发布时间: 2014-05-30

引用:原帖由 siu3010 於 16/5/2014 14:32 发表
例如:
A B
LT-1400123 130069
LT-1400124 1400127
LT-1400125 1400126
LT-1400126 1400125
LT-1400127 1400124
LT-1300691 ...

咁样太多combination，用formula好难做，但万变不离其中了…
我之前果条改改唔用right()，用mid()就可以比较中间嘅字串了，但条formula会好长，因为太多款组合
你最好用多几个column做中间人，有错都易啲睇到

作者: skww 发布时间: 2014-05-30

你点按?我得喎!

作者: alee001 发布时间: 2014-05-30

引用:原帖由 alee001 於 2014-5-16 02:46 PM 发表
3346105你点按?我得喎!

应该同你冇分别挂

附件未命名.jpg (90.08 KB)

2014-5-16 05:16 PM

未命名.jpg (90.08 KB)

2014-5-16 05:16 PM

作者: siu3010 发布时间: 2014-05-30

其实除左excel,有冇其他方法可以做到

因为份报表真系好哂时间,但我又有其他野要做,真系唔想哂咁多时间系呢份报表上

作者: siu3010 发布时间: 2014-05-30

引用:用A去搜寻B,正常呢度全部RESULT等於YES,呢个就系我想要既公式
A一定要包含B显示既所有数字,例如LT-1400123,如果B只有1400就搵唔到,一定要有1400123先搵到
A既LT-1300691,只要B既全部数字=130069,RESULT都系YES,因为A包含130069

呢度唔系好明: 1400 同 130069 有乜分别? 点解前者搵唔到, 后者搵到? 新 requirement? 第一帖无讲既?

140012 又搵唔搵到?
14001 呢?

引用:原帖由 siu3010 於 2014-5-16 20:34 发表
其实除左excel,有冇其他方法可以做到

因为份报表真系好哂时间,但我又有其他野要做,真系唔想哂咁多时间系呢份报表上

作者: a8d7e8 发布时间: 2014-05-30

引用:原帖由 a8d7e8 於 2014-5-16 08:54 PM 发表

呢度唔系好明: 1400 同 130069 有乜分别? 点解前者搵唔到, 后者搵到? 新 requirement? 第一帖无讲既?

140012 又搵唔搵到?
14001 呢?

可能我讲得唔清楚,算啦,由佢,我净系想知好似呢张图咁,点样可以3个result都系yes,而当B栏其中一格我打成237时,result会变no

附件未命名.jpg (90.08 KB)

2014-5-16 09:47 PM

未命名.jpg (90.08 KB)

2014-5-16 09:47 PM

作者: siu3010 发布时间: 2014-05-30

如果转咁样会唔会简单d??

当A2包含G2既数字,RESULT = H栏既值

我谂应该系类似vlookup既做法,但呢个太复杂,我唔识写

[ 本帖最后由 siu3010 於 2014-5-16 10:28 PM 编辑 ]

附件未命名.jpg (87.52 KB)

2014-5-16 10:24 PM

未命名.jpg (87.52 KB)

2014-5-16 10:24 PM

作者: siu3010 发布时间: 2014-05-30

你将储存格停系[C2]已输入的公式，后按F2变成编辑状态，再同时按住Ctrl同Shift键，跟住再按Enter键，即可将公式变成{...}，如果咁都唔得建议你上网学吓Excel公式列阵，因为佢Excel必学招式。

作者: alee001 发布时间: 2014-05-30

引用:原帖由 alee001 於 2014-5-16 10:38 PM 发表
你将储存格停系[C2]已输入的公式，后按F2变成编辑状态，再同时按住Ctrl同Shift键，跟住再按Enter键，即可将公式变成{...}，如果咁都唔得建议你上网学吓Excel公式列阵，因为佢Excel必学招式。

我做左啦,就系唔得呀,你见唔到我公式果度已经用左ctrl+shift+enter?

附件未命名.jpg (90.08 KB)

2014-5-16 10:49 PM

未命名.jpg (90.08 KB)

2014-5-16 10:49 PM

作者: siu3010 发布时间: 2014-05-30

可能系我理解能力有问题, 睇唔出你意思.
唔想硬估, 所以先问你.

如果系纯粹搵 sub-string, 假设用 d1 装晒 all b1:b1000 用 concatenate(b1,",",b2,",",b3....etc) 好似有限制, 多得制你搵多几个 column 储又再 concatenate 啦

然后用 search(a1, $d$1) 就可.

引用:原帖由 siu3010 於 2014-5-16 21:47 发表

可能我讲得唔清楚,算啦,由佢,我净系想知好似呢张图咁,点样可以3个result都系yes,而当B栏其中一格我打成237时,result会变no

作者: a8d7e8 发布时间: 2014-05-30

想请教一下, RIGHT function 点先用到 range? 咁样用 range 系咪一定配合 vlookup 先得?

我睇咗呢个教学, 但唔明咁做有乜特别?? 只系睇到条 formula 系有 range, 而唔系 per row.......

引用:原帖由 alee001 於 2014-5-16 22:38 发表
你将储存格停系[C2]已输入的公式，后按F2变成编辑状态，再同时按住Ctrl同Shift键，跟住再按Enter键，即可将公式变成{...}，如果咁都唔得建议你上网学吓Excel公式列阵，因为佢Excel必学招式。

作者: a8d7e8 发布时间: 2014-05-30

呢个紧系简单啲啦.............楼上啲师兄已经做到啦...

引用:原帖由 siu3010 於 2014-5-16 22:24 发表
如果转咁样会唔会简单d??

当A2包含G2既数字,RESULT = H栏既值

我谂应该系类似vlookup既做法,但呢个太复杂,我唔识写

作者: a8d7e8 发布时间: 2014-05-30

唔该哂咁多位先,但我自己已经写到,估唔到上黎问左咁耐,最终我将自己写出黎既公式加以改良已经做到