Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?
Частенько возникает ситуация, когда необходимо сразу несколько ячеек умножить на одно и то же число. Взять хотя бы прибавление процента (НДС и еще какие наценки) — вручную долго. Можно формулами, вроде такой: = A1 *0,1 . Но у формул недостаток — вводить их надо в другую ячейку, а затем уже оперировать этими значениями. Иногда это подходит и является более приемлемым вариантом. Например, когда исходные данные нужно оставить нетронутыми. Но когда необходимо исправить данные прямо в этих же ячейках — формулы использовать не очень удобно. Особенно, если данные разбросаны по разным ячейкам разных столбцов — преобразовывать такие данные просто утомительно. Или другая ситуация: сменить знак для множества чисел. Т.е. из минуса сделать плюс, из плюса минус. Для этого проще всего каждое число умножить на -1.
В этой статье я хочу показать как просто и быстро можно сделать это без особых усилий и затрат. Прямо в исходных ячейках и без всяких макросов.
Для начала необходимо определиться, какое число надо прибавить или вычесть, или на какое число умножить/разделить. Например необходимо сделать наценку для сумм в виде НДС(18%).
Порядок действий (на примере умножения на число):
- в любую пустую ячейку записываем это число(1,18 в случае с НДС);
- копируем ячейку с этим числом ( Ctrl + C );
- выделяем все ячейки, которые надо помножить на это число
- нажимаем Правую кнопку мыши —Специальная вставка (Paste Special) .
- ищем внизу пункт Умножить (Multiply) , выбираем его и нажимаем Ок. Так же можно просто нажать двойным щелчком мыши на пункте Умножить (Multiply) .
Данные умножены на число из скопированной ячейки. Сколько времени это заняло? От силы 10 секунд и то только в первый раз, т.к. по инструкции делали. Далее это будет занимать 5 секунд. Так же из параметров специальной вставки можно заметить, что данные можно не только умножить, но и сложить, вычесть, разделить.
Один немаловажный нюанс: если среди выбранных для умножения/деления/сложения/вычитания ячеек есть формулы — в саму формулу будет добавлено деление на указанное число. Например, если в ячейке записана формула: = A1 + B1 , то после описанных выше манипуляций в этой ячейке будет такая формула: =( A1 + B1 )*1,18
Хитрое применение метода — преобразуем даты и числа, записанные как текст в реальные даты и числа
Плюс у данного метода есть еще одно отличное применение. Допустим есть диапазон чисел, записанных как текст(это с виду число, но записано текстом и формат у ячейки тоже текстовый. Изменение формата ячейки здесь не поможет). Работать с такими числами проблематично — они являются текстом и в большинстве случаев формулы с ними не работают как с числами(СУММ, СУММЕСЛИ и пр.). Часто их можно определить по зеленому треугольничку ошибки в левом верхнем углу ячейки(хотя не всегда — зависит от формата ячейки и от настроек приложения). Реже — по апострофу перед числом: ‘ 456 . Но можно встретить и такое, что ни треугольничка, ни апострофа нет, а число все равно записано текстом. Подобными пакостями славятся выгрузки из 1С. Там сплошь и рядом встречаются числа, которые не числа; даты, которые выглядят как даты, а на деле это текст; форматы разных мастей. И поди разбери. Многие спасаются от этого мартышкиным трудом: выделяем ячейку — F2 — Enter . И они в этом не виноваты — им просто никто не сказал, что можно как-то иначе. Но применив спец.вставку легко числа-текст сделать настоящими числами :
- копируем любую пустую ячейку (лучше вообще из только что созданной книги)
- выделяем все ячейки, которые необходимо преобразовать в число
- нажимаем Правую кнопку мыши —Специальная вставка (Paste Special)
- Находиv внизу пункт Сложить (Add) , выбираем его и жмем Ок. Так же можно просто нажать двойным щелчком мыши на Сложить (Add) .
Быстро разбейте и разнесите таблицу на разные листы
Если вам нужно поделиться только конкретной выборкой из сводного отчёта или аккуратно разделить большую таблицу на части, чтобы они поместились во вложение email, тогда вам потребуется разбить данные. Напр., разбить сводный отчёт о продажах на подотчёты по категориям продуктов. Или разбить длинный список на небольшие перечни с фиксированным числом строк. Вместо утомительной сортировки, копирования и форматирования вручную, вы можете сэкономить время с надстройкой XLTools.
Инструмент «Разбить таблицу» автоматически разнесёт данные одного листа по нескольким листам:
Разнесение данных таблицы или диапазона на отдельные листы
Выбор метода разделения: по значениям столбца или по числу строк
Выбор способа именования листов результата
Сохранение заголовков и форматирования в таблицах результата
Разделение объединённых ячеек с дублированием значений
Перед началом работы добавьте «Разбить таблицу» в Excel
«Разбить таблицу» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.
Как разбить таблицу на несколько листов на основе значений столбца
Вы можете разбить всю таблицу или диапазон, исходя из значений в одном ключевом столбце. Так, данные, относящиеся к каждому уникальному значению в ключевом столбце, будут вынесены на отдельные листы.
Нажмите кнопку Разбить таблицу на панели XLTools Откроется диалоговое окно.
Выберите таблицу или диапазон, который вы хотите разбить, включая заголовок.
Совет: нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.
Если в таблице есть заголовок, он будет продублирован в таблицах результата.
Внимание: для лучшего результата, убедитесь, что в заголовке нет пустых ячеек.
Если в таблице нет заголовка, его также не будет в таблицах результата.
Если в таблице есть заголовок, найдите столбец по его названию в заголовке.
Если в таблице нет заголовка, найдите столбец по его общему буквенному обозначению (A, B, C, т.д.)
Выберите Значение в столбце , чтобы вкладкам присваивались имена по значениям ключевого столбца.
Внимание: если некоторые ячейки в вашем ключевом столбце пустые, пожалуйста, заполните пропуски или используйте другой способ именования листов.
Или, выберите Числовой ряд , чтобы вкладкам назначались имена последовательными числами (1, 2, 3…)
При необходимости, добавьте префикс или суффикс. Они будут повторяться в названии каждой вкладки.
Совет: рекомендуем использовать содержательные префиксы и суффиксы — позже будет проще искать и переключаться между листами.
Нажмите OK Готово. Обработка больших таблиц может занять некоторое время.
В результате: новые листы размещены по порядку сразу после исходного листа. Каждая вкладка содержит таблицу данных, связанных только с конкретным ключевым значением. Исходные данные в сохранности и не подвергались изменениям.

Как разбить таблицу на несколько листов по заданному числу строк
Вы можете разбить таблицу или диапазон, исходя из желаемого числа строк на листе, напр., разбивать данные после каждых 5 строк. Таким образом, каждые следующие 5 строк будут вынесены на отдельный лист.
Нажмите кнопку Разбить таблицу на панели XLTools Откроется диалоговое окно.
Выберите таблицу или диапазон, который вы хотите разбить, включая заголовок.
Совет: нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.
Если в таблице есть заголовок, он будет продублирован в таблицах результата.
Если в таблице нет заголовка, его также не будет в таблицах результата.
Выберите По числу строк в качестве метода разделения Укажите фиксированное число строк для разделения таблицы.
Выберите Числовой ряд , чтобы вкладкам назначались имена последовательными числами (1, 2, 3…)
При необходимости, добавьте префикс или суффикс. Они будут повторяться на каждой вкладке.
Совет: рекомендуем использовать содержательные префиксы и суффиксы — позже будет проще искать и переключаться между листами.
Нажмите OK Готово. Обработка больших таблиц может занять некоторое время.
В результате: новые листы размещены по порядку сразу после исходного листа. Каждая вкладка содержит таблицу с фиксированным числом строк. Исходные данные в сохранности и не подвергались изменениям.

Как образом данные копируются на новые листы
Разнесение таблицы по нескольким рабочим листам по сути означает извлечение и копирование данных из исходнго листа на новые листы книги.
Формулы и ссылки на ячейки: Чтобы избежать искажения данных, вместо ссылок на ячейки, функций или формул на исходном листе, в листы результата надстройка XLTools «Разбить таблицу» вставляет их значения.
Форматирование: Надстройка «Разбить таблицу» сохраняет форматирование ячеек и таблиц такими же, как на исходном листе. Это относится к формату ячеек (число, дата, текст, т.д.), ширине столбцов, высоте строк, цвету заливки, т.д. Тем не менее, если к вашей исходной таблице применен стиль, то таблицы результата будут вставлены как диапазоны.
Объединённые ячейки: Если в таблице есть объединённые ячейки, объединение автоматически снимается, а соответствующие значения дублируются.
Как сохранить листы результата отдельными файлами
После разнесения таблицы или диапазона по разным листам, вы можете быстро сохранить эти листы как отдельные файлы с помощью надстройки XLTools Органайзер книг. Она позволяет сохранять листы отдельными файлами, копировать листы в новую книгу и управлять сразу множеством листов.
Any questions or suggestions?
Denis 1) Выгрузка сформированных листов через менеджер не удобна. Когда у меня несколько листов в книге, каждый из которых нужно разделить и сохранить отдельной книгой, действуя по описанной схеме книга засорится листами и станет крайне не удобной. Также это лишнее время. Сделайте пожалуйста возможность выгрузки в отдельные файлы на ряду с формирование листов. 2) В дополнение к п.1. будет актуальной выгрузка создаваемых листов в одну новую книгу. 3) Было бы удобнее иметь возможность выбора из имеющихся столбцов какие использовать в качестве ключа для нарезки. Т.е. при нарезке воспринимать такие столбцы как сцепленные в ключ нарезки 4) Выбор способа переноса формул также актуален. Когда у меня формула ссылается на диапазон, который должен будет выделиться на отдельный лист/книгу, я бы хотел иметь возможность сохранить формулу при разделении таблицы. 5) Чаще всего разбивка на отдельные файлы применяется в связке с групповой рассылкой таких файлов. Добавьте, пожалуйста, к этому инструменту механизм организации групповой рассылки применительно к сформированным файлам. Жду обновлений! Вдохновения вам в разработке и хороших продаж.
Reply Сентябрь 2, 2017 at 00:43
Maria Balobanova Denis, спасибо за подробные предложения! Постараемся реализовать в следующих версиях. Что касается п.1-2, то пока это можно выполнить в Органайзере книг, т.е. после разбивки на листы эти листы можно сохранить как отдельные файлы или скопировать/перенести в новую книгу.
Как разделить столбец в Excel на число 1000 одновременно
Допустим, у нас есть прайс-лист, где цены указаны в миллионах, а нам необходимо перевести эти цифры в тысячи. Например, цена указана за упаковку товара (по 1000 шт. в одной упаковке), а нужно установить цену за 1 штуку.
Первая идея: нужно разделить каждое число на 1000 и округлить до сотых и так целый столбец. Но это все требует времени. Рассмотрим более быстрый способ.
Разделить все цены на 1000 одновременно
Как быстро подготовить прайс с правильными ценами не изменяя по отдельности цену в каждой ячейке?

Выделяем колонку с ценами. Делаем щелчок правой кнопкой мышки, из появившегося контекстного меню выбираем опцию «Формат ячеек». Или нажимаем комбинацию клавиш CTRL+1.
В появившемся окне, вносим свои параметры. На вкладке число, в списке «Числовые форматы» выбираем самый последний пункт «(все форматы)».
В правом поле «Тип:» вводим следующее значение: # ## 0,00.
Обратите внимание на раздел выше поля «Образец». Там будет предварительно отображено, как будут отображаться цены после изменения формата ячеек.

Подтверждаем все изменения кнопкой ОК. Цены изменились и теперь отображаются в тысячах. Программа произвела округление до сотых. Данный способ позволил нам достаточно быстро разделить столбец в Excel на число и округлить до сотых все ячейки одновременно.
Внимание! Такое решение подходит для отображения или печати, но непригодно для вычисления. Формат ячеек помог нам изменить отображение значений в ячейках, а не изменил сами значения. Это легко проверить. Достаточно выделить любую цену и посмотреть, какая цена отображается в строке формул.
Для примера попробуем просуммировать колонку с новыми ценами с помощью функции СУММ. Excel нам выдает цифру 11 884,06, а должна быть 11 884,05.

Поэтому чтобы, например, суммировать такие цены, то здесь одним форматом ячеек не обойтись. В конкретной ситуации разумно применить массив функций.
Вводим в ячейку формулу: =СУММ(ОКРУГЛ(H2:H5/1000;2)).
Внимание! После чего нажимаем CTRL+Enter – это значит, что мы вводим массив функций, а не просто функции.

В строке формул должны отобразится фигурные скобки < >по краям функции. Это четкий знак, что в ячейке выполняется массив функций. В результате получаем истинную сумму.
Будьте внимательны при расчетах в Excel с округлениями, копейка рубль бережет!
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Как разделить на 1000 столбец в excel
29.11.2016 12:46:56
| Цитата |
|---|
| Kopa написал: И да 3000 позиций, как-то лениво |
лениво что?
в любую ячейку записываете 1000. Копируете эту ячейку.
Выделяете сразу все ячейки, деление на 1000 к которым надо добавить. Правая кнопка мыши -Специальная вставка. В группе Операция выбираете Разделить. Ок.
Все, деление на 1000 добавлено во всех формулы выделенных ячеек. Если формул нет — то числа просто будут поделены на 1000.
При этом выделять ячейки для Спец.вставки можно только с формулами: F5 -Выделить -Формулы -Ок.
Изменено: The_Prist — 29.11.2016 12:47:08
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.