Как увеличивать каждое число в excel

Быстро сложим / разделим/ умножим числовые значения из диапазона ячеек на заданное пользователем число. Этот подход позволяет уменьшить или увеличить разрядность чисел в выбранном диапазоне, быстро выделить НДС и пр.

В ряде случаев необходимо изменить уже содержащиеся в таблице числовые значения. Например, имеется таблица стоимости товаров, цены которых указаны в рублях (столбец B ):

Пусть нам необходимо указать цены в столбце B не в рублях, а тысячах, т.е. поделить все значения на тысячу. Конечно, можно в соседнем столбце написать соответствующую формулу, затем скопировать новые значения и потом вставить их как значения в столбец с ценами.

Красивой альтернативой является использование инструмента Специальная вставка ( Главная/ Буфер обмена/ Вставить/ Специальная вставка ). Покажем как быстро разделить на 1000 весь столбец с ценами, используя инструмент Специальная вставка .

• в любой пустой ячейке вводим 1000 (например, в D2 ) и нажимаем ENTER ;
• копируем в Буфер обмена значение ячейки D2 ;
• выделяем диапазон с ценами ( В2:В10 );
• идем в меню Главная/ Буфер обмена/ Вставить/ Специальная вставка ;
• выбираем Разделить в разделе Операция ;
• нажимаем ОК .

Получаем следующее:

Если нужно изменить не все цены из столбца B, то нужно выделить не весь диапазон цен, а только нужные ячейки ( несвязные диапазоны можно выделить , удерживая клавишу CTRL ).

Этот же подход позволяет, к примеру, убрать НДС (18%) из цен. Для этого нужно в ячейку D2 ввести =100/118 , скопировать это значение в Буфер обмена, выбрать Умножить в разделе Операция инструмента Специальная вставка . Другой пример: изменим валюту цены – переведем рубли в доллары по курсу 30 руб./долл. Для этого нужно в ячейку D 2 ввести курс (30), скопировать это значение в Буфер обмена , выбрать Разделить в разделе Операция инструмента Специальная вставка .

В приведенных выше примерах предполагалось, что ячейки с ценами не содержат формул. Если цены в столбце B являются результатом вычисления формул (например =C1*200 ), то после использования инструмента Специальная вставка , формулы будут модифицированы, например так =(C1*200)/1000 (если выбирали Разделить , а в Буфере обмена содержалось число 1000).

(New Easier Version for Excel 2010 and Later)

How to increment a value, number, percent, etc. every so many rows in Excel.

This tutorial teaches you how to build the formula needed to increment a value or, really, do anything every so many rows in Excel.

Section:

The Formula That Knows in Which Row You are in Excel

How to Increment a Value Based on Which Row You are in Excel

Data Set Not on Row 1

Notes

The Formula That Knows in Which Row You are in Excel

This is the meat of the process and it allows you to know that you are in every 3rd or 5th or 25th row of your data set.  For the example below, we want to act on every 3rd row.

This is the function that we will use for this:

=IF(MOD(ROW(),3)=0,"Hi","")

For now, this will just output «Hi» every third row and it looks like this:

To change this to your data set, simply change the 3 to whichever number of rows you want for the increment.  Make sure that your data set starts in row 1 or this won’t work.  If it’s not in row 1, then copy your data set to a new tab where it can start on row 1 (if you can’t do that I’ll show you how to adjust for it below).

How it Works

Let’s break down the formula in case you feel like understanding how it works.  The best way to start with Excel is inside-out.

ROW() this function tells you the current row that the formula is in.  If we are in row 1, it returns a 1, row 2, it returns a 2, etc.

MOD() We use the MOD function to tell us if we are in the third row or not by dividing the current row number, gotten from the ROW function, by the number 3, which is the second argument in the MOD function.  We can change 3 to whatever we want: 5 to do something every 5 rows, 10 to do something every 10 rows, etc.

When the MOD function performs this division, it will return the remainder, if one exists.  If you divide a number by three and there is no remainder, the MOD function will return zero, and you know that we are on the third row again.  If the MOD function returns anything other than a zero, we are not on the third row.  It may seem confusing but it’s a basic math concept; if the current row number is divisible by three without any left-over decimal places, then the function will return a zero.

We then use the IF statement to check if the MOD function equals zero or not.  If it equals 0, then we know we are in the correct row to do something.

If all of this sounds confusing, don’t worry because it IS confusing!  This is a rather complex formula that relies on a couple rarely used functions and some more advanced techniques in Excel.  The more that you use Excel and nest functions, the easier it will become to understand formulas like this.

How to Increment a Value Based on Which Row You are in Excel

Now that we have the formula that will tell us when we need to do the incrementing, we need to do the incrementing.

In this example, I will simply increment a number by one to illustrate how you do this.  However, you could use any type of increment or mathematical equation or function to do whatever you need.

Here is the result we will get:

Column C contains the final formula.

First, you need to fill the first cell in the column with a number or value that you type in by hand.  Here I put 1.  We do this because, otherwise, it requires a much more complicated formula to account for the first row in the data set, when we can just type the 1 in by hand and be done.

Now, we need to figure out what we want to do.  I want to increment this value that is in the first cell by one every three rows.  If we are not in a «third row» then I want to repeat the last number.

To do this, we fill-in the last two arguments for the IF statement that we made above; here is the result:

=IF(MOD(ROW(),3)=0,C1+1,C1)

Remember that this goes into row 2.

From the formula above, we replaced «Hi» with C1+1 and «» with C1.

This is basic IF statement stuff and there really isn’t anything too complex going on here.

Change these cell references to work for your data and that’s it.

As previously mentioned, you can do whatever you want here; you don’t have to simply increment the value; perform any calculations that you need to do here.

Data Set Not on Row 1

If your data set does not start on row 1, you will need to adjust the above formula like this:

=IF(MOD(ROW()-2,3)=0,"Hi","")

Notice the -2.  You have to subtract a value from the ROW() function in order to get this to work when your data set does not start in row 1.

In this case, the data set starts in row 3, so we subtract 2; if it was in row 5, we would subtract 4.  You are basically offsetting the calculation so it will act like it was starting in row 1.

The final formula that we used above would end up like this:

=IF(MOD(ROW()-2,3)=0,C3+1,C3)

Here is what the result looks like in Excel:

Notes

This may seem confusing, but you don’t have to learn how everything works in order to be able to use this for your spreadsheet; simply follow the instructions above and you can customize it for your needs with just a few edits.  However, there is nothing prohibitively difficult here, just nesting a few functions that you may not have previously seen and checking if they equal zero and putting all of that within an IF statement.

Make sure to download the spreadsheet that accompanies this tutorial so you can see these examples in action and change them to suit your needs.

Перейти к содержанию