Групповое изменение числовых значений в 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).


Комментарии
Только для авторизованных пользователей

Аноним, 6 июля 2016 г.
Прекрасно!
Аноним, 15 сентября 2016 г.
класс!!! спасибо))
Аноним, 13 апреля 2017 г.
мозги вскипели!!!!!!!!!!!! есть у кого нибудь охлаждающая жидкость для мозгов😁😁😁😂😂😂
Аноним, 24 апреля 2017 г.
Давно искала способ заменять числа в таблице на определенное значение... Теперь задача быстро обновить прайс при повышении цен у поставщиков решена!!! Огромное спасибо.
Аноним, 24 апреля 2018 г.
Супер! спасибо огромное
Аноним, 29 июля 2020 г.
Почему то специальная вставка вставляет 1000 как текст или текст в кодировке юникод. ничего умножить там нельзя((
Михаил, 29 июля 2020 г.
Специально создал файл примера (присоединил к статье), все работает как описано в статье. Я даже формат ячейки (там где 1000) поменял на текстовый. Все вычислилось, но, правда, весь столбец стал текстовым.
Аноним, 17 августа 2020 г.
Большое спасибо, реально помогли
Аноним, 21 августа 2020 г.
круто, спасибо!
Аноним, 2 сентября 2020 г.
А если в столбце есть пустые ячейки и нужно что бы они так и остались пустыми. Как это сделать? Выделение с помощью клавиш Shift и Ctrlне подходит, очень большооой столбец)))
Михаил, 2 сентября 2020 г.
хороший вопрос. Сначала я думал, что достаточно поставить галочку "Пропускать пустые ячейки" в окне Специальная вставка. Но, почему-то не работает (я делил диапазон на 1000, в пустых ячейках после вставки появлялся 0). Потом с помощью фильтра выбрал непустые ячейки и снова вставил. Эффект тот же. Совет следующий. Создаете столбец в вашей таблицей с формулой =ЕПУСТО(С2). Столбец С - это пусть будет ваш столбец с пустыми ячейками (ИСТИНА будет в строках, где пустые ячейки). Заменяете формулу значениями. Затем выделяете весь столбец С и делаете что нужно. В пустых ячейках появится некое значение. Фильтром по новому столбцу выбираете ИСТИНА, в столбце С удаляете все значения, которые заполнили изначальные пустые ячейки.
Алексей, 21 октября 2020 г.
Подскажите пожалуйста, есть ли возможность автоматического изменения значения ячейки в зависимости от значения в другой ячейки. Например, при заказе 1й единицы товара его цена 100р, а при заказе от 100ед товара, цена меняется на 50р.
Евгений, 22 октября 2020 г.
Да, такое возможно. Вот формула =ЕСЛИ(A1<100;100;50) Количество товара в заказе вводите в ячейку А1
Алексей, 23 октября 2020 г.
Просто лучший, спасибо большое!!!
Алексей, 23 октября 2020 г.
А как сделать несколько условий, например при заказе 200ед цена меняется на 25р?
Михаил, 26 октября 2020 г.
см. в статье https://excel2.ru/articles/funkciya-esli-v-ms-excel-esli про вложенные ЕСЛИ
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved