blocks|key|1428483|text|该页面上的第二个公式使用分析ToolPak的GCD函数，您可以从工具>插件添加它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428484|=A1/GCD(A1，B1)&":"&B1/GCD(A1，B1)|1428485|这是一个更加数学化的公式，而不是基于文本的操作。|1428486|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

The second formula on that page uses the GCD function of the Analysis ToolPak, you can add it from Tools > Add-Ins.

=A1/GCD(A1,B1)&amp;":"&amp;B1/GCD(A1,B1)

This is a more mathematical formula rather than a text manipulation based on.

blocks|key|1643400|text|你正在寻找最大公约数(GCD)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643401|您可以在VBA中递归地计算它，如下所示：|1643402|Function+GCD(numerator+As+Integer,+denominator+As+Integer)
++If+denominator+=+0+Then
++++GCD+=+numerator
++Else
++++GCD+=+GCD(denominator,+numerator+Mod+denominator)
++End+If
End+Function|code-block|syntax|javascript|1643403|并在工作表中使用它，如下所示：|1643404|+++ColumnA+++ColumnB+++ColumnC
1++33++++++++11++++++++=A1/GCD(A1;+B1)+&+":"+&+B1/GCD(A1;+B1)
2++25+++++++++5++++++++=A2/GCD(A2;+B2)+&+":"+&+B2/GCD(A2;+B2)|1643405|建议将函数调用的结果存储在隐藏列中，并使用此结果来避免每行调用函数两次：|1643406|+++ColumnA+++ColumnB+++ColumnC++++++++ColumnD
1++33++++++++11++++++++=GCD(A1;+B1)+++=A1/C1+&+":"+&+B1/C1
2++25+++++++++5++++++++=GCD(A2;+B2)+++=A2/C2+&+":"+&+B2/C2|1643407|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

You are looking for the greatest common divisor (GCD).

You can calculate it recursively in VBA, like this:

<pre><code>Function GCD(numerator As Integer, denominator As Integer)
 If denominator = 0 Then
 GCD = numerator
 Else
 GCD = GCD(denominator, numerator Mod denominator)
 End If
End Function
</code></pre>

And use it in your sheet like this:

<pre><code> ColumnA ColumnB ColumnC
1 33 11 =A1/GCD(A1; B1) &amp; ":" &amp; B1/GCD(A1; B1)
2 25 5 =A2/GCD(A2; B2) &amp; ":" &amp; B2/GCD(A2; B2)
</code></pre>

It is recommendable to store the result of the function call in a hidden column and use this result to avoid calling the function twice per row:

<pre><code> ColumnA ColumnB ColumnC ColumnD
1 33 11 =GCD(A1; B1) =A1/C1 &amp; ":" &amp; B1/C1
2 25 5 =GCD(A2; B2) =A2/C2 &amp; ":" &amp; B2/C2
</code></pre>

blocks|key|1643478|text|谢谢你。我使用了这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643479|=CONCATENATE((number1/GCD(number1,number2)),":",((number2/GCD(number1,number2))))|code-block|syntax|javascript|1643480|如果你有2007年的版本，这将会很好的工作。|1643481|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Thanks ya'll. I used this:

<pre><code>=CONCATENATE((number1/GCD(number1,number2)),":",((number2/GCD(number1,number2))))
</code></pre>

If you've got 2007 this works great.

blocks|key|1643534|text|我发现这是最简单和最短的，但是我四舍五入到了小数点后零位：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1643535|="1"+&+":"+&+ROUND((A1/B1),0)|offset|length|style|CODE|1643536|注意&前后的空格。|1643537|这意味着"1“和":”被视为整体公式的附加非公式信息。ROUND函数将基本公式A1/B1四舍五入到小数点后0位。你可以试着改成1,2,3...小数位数。|1643538|我希望我说得很清楚了。|1643539|entityMap^0|0|0|T|0|2|1|0|R|5|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|W|8|@$D|X|E|Y|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|Z|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|O|$]]

I found this to be the easiest and the shortest, I however rounded off to zero decimal places:
<code>=&quot;1&quot; &amp; &quot;:&quot; &amp; ROUND((A1/B1),0)</code>
Note the spaces before and after <code>&amp;</code>.
What this means is that &quot;1&quot; and &quot;:&quot; are seen as additional non-formula information to the overall formula. The <code>ROUND</code> function rounds off A1/B1 that is the basic formula to 0 decimal places. you can try changing to 1,2,3... decimal places.
I hope I made this clear.

blocks|key|1428734|text|假设您在D和E单元格中有数据。这是我的朋友'Karthik‘显示fn的最简单的比率。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428735|=ROUND(D7/E7,+2)+&":"+&+(E7/E7)|code-block|syntax|javascript|1428736|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Lets assume you have data in D and E cells.. 
Here is an easiest ratio displaying fn by my frnd 'Karthik'

<pre><code>=ROUND(D7/E7, 2) &amp;":" &amp; (E7/E7)
</code></pre>

blocks|key|1428793|text|在工作中，我们只有Excel+2003可用，这两个公式似乎完美地适用于我：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1428794|=(ROUND(SUM(B3/C3),0))&":1"|code-block|syntax|javascript|1428795|或|1428796|=B3/GCD(B3,C3)&":"&C3/GCD(B3,C3)|1428797|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

At work we only have Excel 2003 available and these two formulas seem to work perfectly for me:

<pre><code>=(ROUND(SUM(B3/C3),0))&amp;":1"
</code></pre>

or

<pre><code>=B3/GCD(B3,C3)&amp;":"&amp;C3/GCD(B3,C3)
</code></pre>

I have an Excel spreadsheet of data like:

<pre><code>ColumnA ColumnB
33 11
25 5
6 4
</code></pre>

What i would like to do is add a third column which shows the ratio of columnA to columnB in the format of A:B. For example:

<pre><code>ColumnA ColumnB Ratio
33 11 3:1
25 5 5:1
6 4 3:2
</code></pre>

Does anyone know of a way to achieve this?

Thanks in advance.

How do I display a ratio in Excel in the format A:B?

我有一个Excel数据电子表格，如下所示：ColumnA ColumnB33        1125        56         4我想要做的是添加第三列，它以A:B的格式显示columnA与columnB的比率。例如：ColumnA ColumnB   Ratio33        11       3:12...

问如何在Excel中以A:B格式显示比率？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Excel中以A:B格式显示比率？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Excel中以A:B格式显示比率？
EN