Excel公式帮助 - 字符 [英] Excel formula help - Characters

问题描述

我有一个包含" 2287 /  10 /  15/5"每个数字代表一个不同的项目。如果我想将每个数字增加X％。有没有一种简单的方法可以做到这一点.....我有超过1000个单元来做到这一点。

To更详细地解释每列代表一个产品。每行代表一个版本。因此，每列将具有相同的％即5％（例如，这将在第1行中）。

行  4 将是具有上述数字的第一个版本...因此，2287将增加X％，10,15和5 ......

然后行  5会有不同的数字，但会在第1行中增加相同的％。

请看下面我的意思样本......

有人可以帮忙吗？

解决方案

解决这个问题的简单方法是建立自定义公式这个。

步骤1：转到要使用此公式的工作簿

步骤2：按ALT + F11

步骤3：在VBA代码屏幕中添加模块

第4步：粘贴以下代码 

函数increaseByPercent（originalAmount作为范围，百分比作为范围，delimeter As String）As Variant 
 Dim originalNumbers As String 
 Dim perCent As Variant 
 originalNumbers = originalAmount.Value 
 perCent = VBA.Trim（percentage.Value）
 Dim numbersArray As Variant 
 numbersArray = VBA.Split（originalNumbers，delimeter，vbTextCompare）
 For Each num in numbersArray 
 newtext = newtext& " " &安培;除界仪和" " &安培; VBA.CInt（VBA.Trim（num））+（VBA.CInt（VBA.Trim（num））* perCent）
下一个
 increaseByPercent = VBA.Mid（newtext，4，Len（newtext） ）
结束功能

如何在工作表中使用此公式 - 在此之后，您可以在工作表中看到如下所示的客户内置公式：

I have a cell that contains " 2287/ 10 / 15 / 5" each number represents a different item. If I want to increase each number by X%. Is there an easy way to do this..... I have over 1000cells to do this in.

To explain in a bit more detail each column represents a product. and each row represents a version. so each column would have the same % ie 5% (this would be in Row 1 for example).

Row 4 would be the first version which has the numbers above...so 2287 would be increased X% as would 10,15 and 5....

then row 5 would have difference numbers but would be increased by the same % in Row 1.

Please see below a sample of what I mean......

Can anyone help?

解决方案

Hi,

Simple way to solve this would be to build a custom formula for this.

Step1 : Go to Workbook where you want to use this formula

Step 2: Press ALT + F11

Step 3 : In the VBA Code screen Add a module

Step 4 : and paste the below code 

Function increaseByPercent(originalAmount As Range, percentage As Range, delimeter As String) As Variant
    Dim originalNumbers As String
    Dim perCent As Variant
    originalNumbers = originalAmount.Value
    perCent = VBA.Trim(percentage.Value)
    Dim numbersArray As Variant
    numbersArray = VBA.Split(originalNumbers, delimeter, , vbTextCompare)
    For Each num In numbersArray
        newtext = newtext & " " & delimeter & " " & VBA.CInt(VBA.Trim(num)) + (VBA.CInt(VBA.Trim(num)) * perCent)
    Next
    increaseByPercent = VBA.Mid(newtext, 4, Len(newtext))
End Function

How to use this formula in your Sheet - After this you can see a custome Built formula in your worksheet like below:

这篇关于Excel公式帮助 - 字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！