提取个尾的“尾”组合(Extract the tail "tail" combination)

q771021738

New Member
Joined
May 28, 2019
Messages
13
EXCEL表格:https://1drv.ms/x/s!AiDf3QxUtCLkavUxZeMNKZL7DRM

题目:
①计算结果以H2为首个单元格
②计算过程如下:
(1)在G:G区域,数值取尾
(2)A2:F2区域,尾数分别相加,结果取尾
(3)假设RIGHT(A2 + B2)= RIGHT(G2)则把A2与B2的尾数,同放在一个单元格
③组合去重复:例如G4取尾是0 ,A4:F4区域,尾数相加等于0的,只有4和6,有两个4,因此形成46 46两个组合,最终保留一个46组合就可以
④A2:F2例举:
(1)G2是135取尾是5
(2)在A2:F2中,两个尾相加,然后取尾的,只有右(D2 + E2)=右(G2),D2尾是5,E2尾是0,则最终提取05尾,顺序没要求
(3)由以上,得出符合条件的组合是:“05”
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
what do you expected in H with this set of data?


Book1
ABCDEFG
1
2101102103104105106109
Sheet4
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]101[/TD]
[TD]102[/TD]
[TD]103[/TD]
[TD]104[/TD]
[TD]105[/TD]
[TD]106[/TD]
[TD]109[/TD]
[TD]36[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]






中文解释:
G2取尾是9
A:F区域,任意两个尾数相加的和值,取尾;与G2的尾相同,则把相通的两位数提取
假设出现多个相同的尾数,则保留一个

Google Translate:
G2 tail is 9
A: F area, the sum of any two mantissas, and the tail; the same as the tail of G2, the two-digit number of the same is extracted
Assuming multiple identical mantissas, keep one
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

this is what I came up with but with so many helping rows and columns I doubt that this is what you're after


Book1
ABCDEFGHIJ
1101102103104105106107162534
23456716
3567825
478934
5910
611
Sheet4
Cell Formulas
RangeFormula
B2=RIGHT($A$1)+RIGHT(B1)
C3=RIGHT($B$1)+RIGHT(C1)
D4=RIGHT($C$1)+RIGHT(D1)
E5=RIGHT($D$1)+RIGHT(E1)
F6=RIGHT($E$1)+RIGHT(F1)
G2=IFERROR(ROW(A2)-ROW($A$1)&MATCH(RIGHT($G$1,1)+0,A2:F2,0),"")
H1{=IFERROR(INDEX($G$2:$G$6,SMALL(IF(1-($G$2:$G$6=""),ROW($G$2:$G$6)-ROW($G$2)+1),COLUMN(H:H)-COLUMN($G:$G))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

Try this:-
Data assumed to start "A1"
Code:
[COLOR="Navy"]Sub[/COLOR] MG28May05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Col = 6
Num = Right(Dn.Offset(, 6), 1)
[COLOR="Navy"]For[/COLOR] Ac1 = 0 To 5
    [COLOR="Navy"]For[/COLOR] Ac2 = Ac1 + 1 To 5
        [COLOR="Navy"]If[/COLOR] Val(Right(Dn.Offset(, Ac1), 1) + Val(Right(Dn.Offset(, Ac2), 1))) = Num [COLOR="Navy"]Then[/COLOR]
            Col = Col + 1
            Dn.Offset(, Col) = Right(Dn.Offset(, Ac1), 1) & Right(Dn.Offset(, Ac2), 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac2
[COLOR="Navy"]Next[/COLOR] Ac1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

chaina中文:
目前VBA很懂不是
模拟题目,的英文答案正确的
假设模拟题目一:F区域中数字有102212115,那根据VBA计算结果就是25/25
遇到这种情况,最终重复的只保留一个就可以。
如果能用公式最好,VBA我基本上搞不太懂。
非常感谢你的帮助!

Google Translate:
Currently VBA is not very understanding
Simulation question, the answer is correct
Suppose the simulation problem A: The number in the F area is 102, 212, 115, then the result is 5/25 according to the VBA calculation.
In this case, only one of the last duplicates can be retained.
If I can use the formula best, I basically don't understand VBA.
thank you very much for your help!
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

this is what I came up with but with so many helping rows and columns I doubt that this is what you're after

ABCDEFGHIJ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]107[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]34[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet4

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=RIGHT($A$1)+RIGHT(B1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=RIGHT($B$1)+RIGHT(C1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=RIGHT($C$1)+RIGHT(D1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=RIGHT($D$1)+RIGHT(E1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=RIGHT($E$1)+RIGHT(F1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IFERROR(ROW(A2)-ROW($A$1)&MATCH(RIGHT($G$1,1)+0,A2:F2,0),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]{=IFERROR(INDEX($G$2:$G$6,SMALL(IF(1-($G$2:$G$6=""),ROW($G$2:$G$6)-ROW($G$2)+1),COLUMN(H:H)-COLUMN($G:$G))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Chaina中文:
瞬间感觉国外的朋友特别热心。
首先非常感谢你热心的解答。
公式计算过程与计算结果都没有问题的。
其次,A:G纵向区域,是数据源,所以计算过程只能横向计算。
=IFERROR(ROW(A2)-ROW($A$1)&MATCH(RIGHT($G$1,1)+0,A2:F2,0),"")
我刚才横向测试的时候,这条公式计算结果是15,不清楚哪里出错了。

Google Translate:
Instantly feel that foreign friends are particularly enthusiastic.
First of all, thank you very much for your enthusiastic answer.
There is no problem with the formula calculation process and the calculation results.
Secondly, the A:G vertical area is the data source, so the calculation process can only be calculated horizontally.
=IFERROR(ROW(A2)-ROW($A$1)&MATCH(RIGHT($G$1,1)+0,A2:F2,0),"")
When I was testing horizontally, this formula calculated 15 and it was not clear what went wrong.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top