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”
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

I've rearranged the formula horizontally here

https://drive.google.com/file/d/1xoEl79uoZFXGoa3jnhrtwG-ixZUo4WTY/view?usp=sharing

have a look if the works for you

When I was testing, when I entered new data, less than three digits, an error occurred.
https://drive.google.com/file/d/1a4jddPDg0kjBxx0t479LYH4YoqFHgmXl/view?usp=sharing

Look at this form, this is a friend from China to help.
He used three formulas to calculate, and there is no calculation process for unlocking.
But there is also a problem. When I insert a new column on the left side of column A, the result of the operation will be wrong.
https://drive.google.com/open?id=1JsGBeAHxKKsU629KaCQFaLQOREA19rn1
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

the problem for your test is the 11, as none of the right between A:F added up to 1 so the calculations are correct.
you can see if you change G to 19


Book1
ABCDEFGHIJKLMNOPQ
1101102103104105106102
21011021031041051061031212
31011021031041051061041313
410110210310410510610514231423
510110210310410510610936453645
613107152232218192525
7
Sheet4
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

the problem for your test is the 11, as none of the right between A:F added up to 1 so the calculations are correct.
you can see if you change G to 19

ABCDEFGHIJKLMNOPQ
1101102103104105106102
21011021031041051061031212
31011021031041051061041313
410110210310410510610514231423
510110210310410510610936453645
613107152232218192525
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

If "G6" is changed to 19, the result of "H6" should be 27.
——————————
Because the "G6" tail is 9
"B6+E6"=7+2=9
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

[QUOTE = AlanY; 5285590]你的测试的问题是11,因为A:F之间的任何一个加起来都不是1,所以计算是正确的。
你可以看到你是否将G改为19

一个CdËFGH一世Ĵķ大号中号ñØPQ
1101102103104105106102
21011021031041051061031212
31011021031041051061041313
410110210310410510610514231423
10110210310410510610936453645
613107152232218192525
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

[/引用]

If "G6" is changed to 19, the result of "H6" should be 27.
——————————
Because the "G6" tail is 9
"B6+E6"=7+2=9
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

[QUOTE = AlanY; 5285590]你的测试的问题是11,因为A:F之间的任何一个加起来都不是1,所以计算是正确的。
你可以看到你是否将G改为19

一个CdËFGH一世Ĵķ大号中号ñØPQ
1101102103104105106102
21011021031041051061031212
31011021031041051061041313
410110210310410510610514231423
10110210310410510610936453645
613107152232218192525
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

[/引用]

If "G6" is changed to 19, the result of "H6" should be 27.
——————————
Because the "G6" tail is 9
"B6+E6"=7+2=9
 
Upvote 0
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

ok, try again

https://drive.google.com/drive/folders/0B1h-Y2fdTl08R0dwYTdSb2pHVkU?usp=sharing

ABCDEFGHIJKL
1101102103104105106102
210110210310410510610312
310110210310410510610413
41011021031041051061051423
51011021031041051061093645
6131071522322181972

<tbody>
</tbody>
Sheet4

You are looking at it, the sum of the A:F areas, and finally the tail?
——————————————
I don’t know whether to call your teacher or call your friend.
In short, thank you.
https://drive.google.com/open?id=1a4jddPDg0kjBxx0t479LYH4YoqFHgmXl
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,757
Members
448,295
Latest member
Uzair Tahir Khan

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