Как вставить число в Excel без изменений
Обновлено: 21.11.2024
Без использования формул вы можете складывать, вычитать, умножать или делить все числа в диапазоне на определенную сумму. Например, в прайс-листе все цены могут быть увеличены на 1,50 доллара США или снижены на 5%.
Это упрощает одновременное изменение сумм на листе с помощью команды "Специальная вставка" на ленте Excel. Просто введите значение в ячейку, затем скопируйте эту ячейку и примените ее значение к другим ячейкам.
ПРИМЕЧАНИЕ. Этот метод перезаписывает исходные числа, поэтому обязательно сделайте резервную копию файла, прежде чем изменять числа с помощью специальной вставки.
Спасибо Т. Валко за предложение этой страницы!
Увеличение числа с помощью специальной вставки
Посмотрите это короткое видео, чтобы увидеть шаги по сложению или умножению с помощью специальной вставки. Письменные инструкции находятся под видео.
Добавить сумму к выбранным номерам
Возможно, вы захотите увеличить все числа в диапазоне на заданную величину. Например, в этом прайс-листе все цены должны быть увеличены на 1,50 доллара. Следующий прием упрощает одновременное повышение цен.
-
В пустую ячейку введите сумму прибавки. В этом примере в ячейку D2 было введено значение 1,50
На листе каждое из выбранных чисел автоматически увеличивается на 1,50 доллара США
Умножение чисел на процент
Возможно, вы захотите увеличить все числа в диапазоне на заданный процент. Например, в этом прайс-листе все цены должны быть увеличены на 5%. Следующий прием упрощает одновременное повышение цен.
Чтобы увеличить в процентах, умножьте на (1 + процент в виде десятичной дроби).
- В этом примере увеличение составляет 5 %.
- Записано в виде десятичной дроби, 5 % равно 0,05.
- Множитель равен 1+0,05, что равно 1,05.
Выполните следующие действия, чтобы повысить текущие цены:
- В пустой ячейке введите сумму увеличения.
- В этом примере в ячейку D8 было введено значение 1,05.
- На снимке экрана ниже выделены ячейки B8:B11.
- Щелкните правой кнопкой мыши одну из выделенных ячеек и выберите "Специальная вставка".
- В диалоговом окне "Специальная вставка" в разделе "Вставка" нажмите "Значения".
- В разделе "Операция" нажмите "Умножить".
- Нажмите "ОК", чтобы применить умножение к выбранным числам.
- Каждое из выбранных чисел автоматически увеличивается на 5 %.
Вычесть сумму из чисел
Возможно, вы захотите уменьшить все числа в диапазоне на заданную величину. Например, в этом прайс-листе все цены должны быть снижены на 1,25 доллара. Следующий прием позволяет легко снизить все цены сразу.
- В пустой ячейке введите сумму уменьшения. В этом примере в ячейку D3 было введено значение 1,25
- Здесь выбраны ячейки B2:B5.
На листе каждое из выбранных чисел автоматически уменьшается на 1,25 доллара США
Разделение чисел с помощью специальной вставки
Посмотрите это короткое видео, чтобы увидеть шаги по делению чисел с помощью команды "Специальная вставка". Письменные инструкции и полная стенограмма видео находятся под видео.
Разделение чисел с помощью специальной вставки
Без использования формул можно быстро разделить набор чисел с помощью команды "Специальная вставка". В этом примере числа делятся на 1000, и результатом является постоянное изменение чисел.
- В пустой ячейке введите сумму, на которую хотите разделить. В этом примере в ячейку F3 было введено число 1000.
- Скопируйте ячейку, содержащую сумму деления.
- Выберите ячейки, содержащие суммы, которые вы хотите разделить. На снимке экрана ниже выбраны ячейки C2:C11.
- На вкладке "Главная" ленты нажмите стрелку на кнопке "Вставить", затем нажмите "Специальная вставка".
Расшифровка видео – Разделяй без формул
Вот полная расшифровка видео: Разделение чисел Excel без формул.
Введение
Сегодня мы рассмотрим функцию, позволяющую постоянно изменять числа путем сложения, вычитания, умножения или деления без использования формулы.
В моей таблице вы можете видеть, что у меня есть некоторые количества, исчисляемые миллионами. Я хочу разделить эти числа на 1000.
Введите значение на листе
Итак, первое, что мы собираемся сделать, это добавить это значение здесь, на листе. Итак, я просто наберу 1000, а затем скопирую это значение. Так что я могу сделать это с помощью Ctrl + C или просто перейти сюда, чтобы увидеть, что это скопировано.
Использовать специальную вставку
Следующий шаг, мы выберем те числа, которые мы хотим разделить на значение, и мы собираемся направиться к ленте для раскрывающегося списка Вставить, так что мы видим специальный Вставить. Когда мы выберем здесь, вы увидите те операции, о которых я говорил ранее.
Итак, у нас есть сложение, вычитание, умножение и деление. Сегодня мы будем использовать разделение. Итак, мы нажмем там и ОК. И теперь вы можете видеть, что все эти числа были навсегда изменены, чтобы быть разделенными на 1000.
Очистить лист
Немного сумбурно, поэтому мы собираемся очень быстро это исправить, просто перейдя на главную ленту, в раздел чисел и выбрав стиль запятой. Здесь вы можете видеть, что все они выровнены, а все десятичные разряды установлены на два.
Последним шагом будет просто удаление этой 1000, и все готово. Просто напоминание. Это постоянное изменение этих значений. Это не формула, которую можно изменить позже.
В Excel вы можете форматировать числа в ячейках для таких вещей, как валюта, проценты, десятичные дроби, даты, номера телефонов или номера социального страхования.
Выберите ячейку или диапазон ячеек.
На вкладке "Главная" выберите "Число" в раскрывающемся списке.
Вы также можете выбрать один из следующих вариантов:Нажмите CTRL + 1 и выберите "Число".
Щелкните правой кнопкой мыши ячейку или диапазон ячеек, выберите «Формат ячеек…» и выберите «Число».
Выберите средство запуска диалогового окна рядом с номером, а затем выберите номер.
Выберите нужный формат.
Числовые форматы
Чтобы просмотреть все доступные числовые форматы, нажмите кнопку вызова диалогового окна рядом с номером на вкладке "Главная" в группе "Число".
Описание
Числовой формат по умолчанию, который Excel применяет при вводе числа. По большей части числа, отформатированные в общем формате, отображаются именно так, как вы их вводите. Однако, если ширина ячейки недостаточна для отображения всего числа, в общем формате числа округляются до десятичных знаков. В общем числовом формате также используется научная (экспоненциальная) запись для больших чисел (12 и более цифр).
Используется для общего отображения чисел. Вы можете указать количество знаков после запятой, которое вы хотите использовать, хотите ли вы использовать разделитель тысяч и как вы хотите отображать отрицательные числа.
Используется для общих денежных значений и отображает символ валюты по умолчанию с цифрами. Вы можете указать количество знаков после запятой, которое вы хотите использовать, хотите ли вы использовать разделитель тысяч и как вы хотите отображать отрицательные числа.
Также используется для денежных значений, но выравнивает символы валюты и десятичные точки чисел в столбце.
Отображает порядковые номера даты и времени в виде значений даты в соответствии с указанным типом и языковым стандартом (местоположением). Форматы даты, начинающиеся со звездочки (*), соответствуют изменениям региональных настроек даты и времени, указанных в панели управления. На форматы без звездочки настройки панели управления не влияют.
Отображает порядковые номера даты и времени в виде значений времени в соответствии с указанным типом и языковым стандартом (местоположением). Форматы времени, начинающиеся со звездочки (*), соответствуют изменениям региональных настроек даты и времени, указанных в панели управления. На форматы без звездочки настройки панели управления не влияют.
Умножает значение ячейки на 100 и отображает результат с символом процента (%). Вы можете указать количество знаков после запятой, которое хотите использовать.
Отображает число в виде дроби в соответствии с указанным типом дроби.
Отображает число в экспоненциальном представлении, заменяя часть числа на E+n, где E (обозначающее Exponent) умножает предыдущее число на 10 в n-й степени. Например, в 2-значном научном формате 12345678901 отображается как 1,23E+10, что равно 1,23, умноженному на 10 в 10-й степени. Вы можете указать количество знаков после запятой, которое хотите использовать.
Обрабатывает содержимое ячейки как текст и отображает содержимое точно так, как вы его вводите, даже если вы вводите числа.
Отображает число в виде почтового индекса (почтового индекса), номера телефона или номера социального страхования.
Позволяет изменить копию существующего кода числового формата. Используйте этот формат для создания пользовательского числового формата, который добавляется в список кодов числовых форматов.Вы можете добавить от 200 до 250 пользовательских числовых форматов, в зависимости от языковой версии Excel, установленной на вашем компьютере. Дополнительные сведения о настраиваемых форматах см. в разделе Создание или удаление настраиваемого числового формата.
Вы можете применять различные форматы к числам, чтобы изменить их внешний вид. Форматы изменяют только способ отображения чисел и не влияют на значения. Например, если вы хотите, чтобы число отображалось в виде валюты, щелкните ячейку с числовым значением > Валюта.
Применение числового формата изменяет только способ отображения числа и не влияет на значения ячеек, которые используются для выполнения вычислений. Фактическое значение можно увидеть в строке формул.
Вот список доступных числовых форматов и способы их использования в Excel для Интернета:
Числовой формат по умолчанию. Если ячейка недостаточно широка, чтобы показать все число, этот формат округляет число. Например, 25,76 отображается как 26.
Кроме того, если число состоит из 12 или более цифр, в Общем формате значение отображается в экспоненциальном представлении.
Работает очень похоже на общий формат, но отличается тем, как он отображает числа с разделителями десятичных разрядов и отрицательные числа. Вот несколько примеров того, как оба формата отображают числа:
Показывает денежный символ с цифрами. Вы можете указать количество знаков после запятой с помощью Увеличить десятичное число или Уменьшить десятичное число.
Также используется для денежных значений, но выравнивает символы валюты и десятичные точки чисел в столбце.
Показывает дату в следующем формате:
Показывает месяц, день и год в следующем формате:
Показывает порядковые номера даты и времени в виде значений времени.
Умножает значение ячейки на 100 и отображает результат с символом процента (%).
Используйте Увеличить десятичное число или Уменьшить десятичное число, чтобы указать нужное количество знаков после запятой.
Показывает число в виде дроби. Например, 0,5 отображается как ½.
Отображает числа в экспоненциальной записи, заменяя часть числа на E+n, где E (экспонента) умножает предыдущее число на 10 в n-й степени. Например, в 2-значном научном формате 12345678901 отображается как 1,23E+10, что равно 1,23, умноженному на 10 в 10-й степени. Чтобы указать количество десятичных разрядов, которые вы хотите использовать, примените Увеличить десятичное число или Уменьшить десятичное число.
Обрабатывает значение ячейки как текст и отображает его точно так, как вы его вводите, даже если вы вводите числа. Узнайте больше о форматировании чисел как текста.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Слава Богу за копирование/вставку, что бы мы были без этого? Некоторым из нас нравится нажимать Ctrl+C/Ctrl+V для всего, включая формулы Excel. Однако есть небольшая проблема — ссылки на ячейки меняются, когда вы копируете ячейки с формулами, что может привести к полной неразберихе. Вот как копировать значения ячеек и формулы без изменения цифр.
Использование специальной вставки
Оглавление
Самый простой способ скопировать только значения ячеек без родительской формулы — использовать параметр "Специальная вставка".
<р>1. Скопируйте нужный диапазон данных с помощью Ctrl+C или Ctrl+X, в зависимости от обстоятельств. Вы также можете выделить диапазон, щелкнуть правой кнопкой мыши и выбрать Копировать.<р>2. В целевом диапазоне щелкните правой кнопкой мыши и выберите "Специальная вставка".
<р>3.Наведите указатель мыши на различные доступные варианты, чтобы найти тот, который вам нужен. В этом случае первая опция второй группы — «Вставить значения». Другие варианты: «Значения и форматирование», «Значения и исходное форматирование» и т. д.
<р>4. Разверните ячейки, чтобы они соответствовали данным.
Перетаскивание с абсолютной ссылкой
Перетаскивание — это способ копирования одного и того же значения ячейки в случае абсолютной ссылки на ячейку. Например, если у вас есть 15 в A1 и 20 в B1,
Вы можете добавить затем, используя =СУММ($A$1+$B$1), а затем перетащите формулу, чтобы повторить ту же формулу в ячейках ниже. Из-за абсолютной ссылки значения не изменятся.
Чтобы перетащить, наведите курсор на правый нижний угол ячейки, пока он не примет вид толстого креста, а затем перетащите его в нужном диапазоне. Он также работает между разными листами и с более сложными формулами. Двойной щелчок по крестику заполняет столбец без перетаскивания до тех пор, пока в левом столбце есть данные.
Обратите внимание, что вы также можете использовать смешанные ссылки для изменения значений столбцов, но не значений строк, и наоборот. Например, $A1+$B1 сохранит тот же столбец, но ссылки на строки будут последовательно увеличиваться при перетаскивании вниз.
Прямое копирование/вставка
Есть более простой вариант, если вы не хотите возиться с громоздким $, используемым в абсолютных ссылках.
<р>1. Нажмите на ячейку, содержащую формулу, которую вы хотите скопировать. <р>2. В строку формул и выделите формулу. Ctrl+C или Ctrl+X, чтобы скопировать или вырезать соответственно.<р>3. Нажмите кнопку Esc, чтобы выйти из строки формул, что предотвращает изменение ссылки.
<р>4. В ячейку назначения и Ctrl+V, чтобы вставить формулу.Копировать/вставить из Sticky Notes или Блокнота
Один из популярных скрытых способов убедиться, что вы копируете только значения, — это сначала скопировать формулу или значения ячейки и вставить их в Sticky Notes или Блокнот. Это лишает текст любого форматирования и динамических ссылок.
Снова скопируйте текст из Notes/Блокнота, а затем скопируйте его в целевые ячейки. К сожалению, это не будет работать с очень большими значениями. Тем не менее, прекрасно работает удаление стилей форматирования ячеек.
Используйте функцию "Найти и заменить"
Для больших диапазонов данных функция «Найти и заменить» является чрезвычайно полезным инструментом. Для начала выделите диапазон ячеек, который хотите скопировать. Нажмите Ctrl+H, чтобы открыть диалоговое окно «Найти и заменить».
В поле «Заменить» замените знак = уникальным символом и примените. Вы можете использовать любой уникальный знак, кроме звездочки или /. Например, допустим, вы используете знак @.
Закройте диалоговое окно "Найти и заменить". Формулы теперь изменены на текстовые строки, которые можно вводить в ячейки назначения с помощью клавиш Ctrl+C и Ctrl+V, как и любой другой текст. После этого выберите целевые ячейки и/или исходные ячейки и нажмите Ctrl+H, чтобы открыть диалоговое окно «Найти и заменить». Выполните обратный процесс и замените уникальный символ знаком равенства, чтобы преобразовать текст в формулы.
Копировать/вставить без форматирования
Иногда вам нужно скопировать текст в ячейке, но не его форматирование. В этом случае вы можете воспользоваться парой простых приемов.
<р>1. Скопируйте текст/диапазон текста в ячейки, из которых вы хотите их получить.2. Выберите параметр «Специальная вставка». Для этого щелкните правой кнопкой мыши ячейку назначения, перейдите к специальной вставке и наведите указатель мыши на возможные параметры, чтобы увидеть, какие из них вам нужны. Варианты включают вставку формулы, вставку форматирования, вставку значения, вставку изображений и т. д.
Это лишь некоторые из (более простых) способов копирования значений ячеек с сохранением задействованных значений.
Некоторые значения Microsoft Excel выглядят как числа, но не складываются. Или вы не можете изменить форматирование чисел Excel — оно всегда остается неизменным. Попробуйте решения в этой статье, чтобы исправить эти неверные номера.
Информацию о проблемах с форматированием дат см. на странице Excel Dates Fix Format.
ПРИМЕЧАНИЕ. Инструкции по замене написанных слов на числа (например, с трех на три) см. в разделе Преобразование слов в числа в Excel.
Похоже на цифры, но не складываются
Если вы копируете данные из другой программы, текстового файла или веб-сайта, Excel может интерпретировать числа как текст. В Excel значения могут выглядеть как числа, но они не действуют как числа и не отображают правильную сумму в формуле СУММ, как показано в таблице ниже.
Кроме того, вы не можете изменить формат чисел — он всегда остается неизменным, независимо от того, как вы пытаетесь форматировать ячейки.
На снимке экрана выше значения в столбце C выглядят как числа, но они не складываются. Есть 3 ячейки с формулами:
- СУММ. В ячейке C7 сумма по формуле сложения равна нулю, вместо того чтобы показывать правильный ответ.
- СЧЕТЧИК: справа, в ячейке F4, в формуле используется функция СЧЕТЧИК. Эта функция подсчитывает все непустые ячейки. Результатом формулы является 4, что показывает, что в этом диапазоне ячеек, C3:C6, есть 4 записи.
- COUNT: Однако функция COUNT в ячейке F5 показывает нулевой результат. Это показывает, что ни одно из значений в ячейках C3:C6 не распознается как число.
Преобразование текста в числа с помощью специальной вставки
Для некоторых "текстовых" номеров проблему можно решить с помощью специальной вставки. Посмотрите этот короткий видеоурок, чтобы увидеть шаги и письменные инструкции ниже. Стенограмма видео приведена ниже на странице.
ПРИМЕЧАНИЕ. Если вам нужно делать это часто, вы можете использовать макрос для автоматизации задачи.
Хронология видео
- 0:00 Введение
- 0:42 Проверка содержимого ячейки
- 1:40 Проверьте панель формул
- 2:00 Исправьте цифры
- 3:08 Получить образец файла
Как исправить текстовые числа
Чтобы исправить числа, которые отображаются как текст, выполните следующие действия:
- Щелкните правой кнопкой мыши пустую ячейку и выберите "Копировать".
- Выберите ячейки, содержащие «текстовые» числа.
- Щелкните правой кнопкой мыши одну из выделенных ячеек и выберите "Специальная вставка".
- Откроется диалоговое окно "Специальная вставка".
- В разделе "Вставить" выберите "Значения".
- В разделе "Операция" выберите "Добавить".
- Нажмите "ОК".
Совет. После исправления чисел можно применить форматирование с помощью команд "Числовой формат" на вкладке "Главная" ленты.
Преобразование текста в числа с помощью VBA
Если вы часто преобразовываете текст в числа, вы можете использовать макрос, подобный показанному ниже.
Сохраните следующий макрос в рабочей книге, которая всегда открыта, например в личной рабочей книге. Затем добавьте этот макрос на панель быстрого доступа или на одну из вкладок на ленте Excel. Чтобы исправить «текстовые» числа, выберите ячейки и нажмите кнопку макроса.
Преобразование дат с заменой всех
Еще один способ исправить проблемные номера. В этом примере числа — это даты, и Excel не распознает их как настоящие даты.
Как показано на снимке экрана ниже, эти даты отформатированы с помощью косой черты в формате даты: м/д/гг
Чтобы исправить "текстовые даты" в этом формате, вы можете попытаться преобразовать их в реальные даты с помощью функции "Найти и заменить" Excel, чтобы заменить косые черты.
Выполните следующие действия, чтобы заменить косые черты, что должно исправить "текстовые" даты:
- Выберите ячейки, содержащие даты.
- На вкладке "Главная" ленты нажмите "Найти и выбрать", затем нажмите "Заменить"
- Или используйте сочетание клавиш Ctrl + H.
- Откроется окно "Найти и заменить"
- Перейдите на вкладку "Заменить", если она еще не выбрана.
- В поле "Найти" введите косую черту: /
- В поле "Заменить на" введите косую черту: /
- Далее нажмите кнопку "Заменить все".
- Когда появится окно сообщения, нажмите "ОК", чтобы подтвердить замену.
- Нажмите кнопку "Закрыть", чтобы закрыть окно "Найти и заменить".
Совет. После того, как даты будут исправлены, вы можете применить к списку другой формат даты. Выберите все ячейки с фиксированной датой и используйте команды «Числовой формат» на вкладке «Главная» ленты.
Исправить скрытые символы
Если вы копируете данные с веб-сайта, эти данные могут содержать скрытые символы, например неразрывный пробел.
В Excel неразрывный пробел имеет код 160 символов, и этот код не исправляется некоторыми методами очистки чисел. Вместо этого вы можете найти этот код символа и заменить его ничем (пустой строкой).
Совет. Если вам нужно часто исправлять скрытые символы, вы можете использовать макрос для автоматизации задачи, например макрос в разделе ниже.
Выполните следующие действия, чтобы удалить скрытые символы с помощью функции "Найти и заменить":
- Выберите ячейки, содержащие числа со скрытыми символами.
- На вкладке "Главная" ленты нажмите "Найти и выбрать" (в крайнем правом углу).
- В раскрывающемся списке нажмите "Заменить".
- Нажмите в поле «Найти».
- Нажмите и удерживайте клавишу Alt, а затем на цифровой клавиатуре введите 0160
- После ввода этого кода в поле "Найти" ничего не появится.
- Для Заменить на оставьте поле пустым
- Скрытые символы с кодом 160 будут заменены ничем (пустая строка)
- Нажмите "Заменить все".
- Нажмите "ОК", чтобы подтвердить замену.
- Закройте окно "Найти и заменить".
Исправить скрытые символы с помощью VBA
Если вам часто нужно удалить скрытый неразрывный пробел, вы можете использовать макрос.
Сохраните следующий макрос в рабочей книге, которая всегда открыта, например в личной рабочей книге. Затем добавьте кнопку на существующую панель инструментов и прикрепите макрос к этой кнопке. Чтобы исправить «текстовые» числа, выберите ячейки и нажмите кнопку на панели инструментов.
Преобразование текста в числа с помощью текста в столбцы
Этот быстрый метод, использующий функцию Excel Text to Columns, может исправить некоторые числа, которые Excel не распознает как действительные числа.
- Выберите ячейки, содержащие числа.
- На вкладке "Данные" нажмите "Текст в столбцы".
- Откроется диалоговое окно мастера преобразования текста в столбцы, в котором показан шаг 1.
- На шаге 1 выберите "С разделителями" в качестве типа файла.
- Затем нажмите кнопку "Готово".
В некоторых случаях это меняет текстовые числа на реальные числа.
Если этот метод не сработал для ваших данных, вы можете попробовать один из других методов на этой странице.
Конвертировать валюту с разными разделителями
Если на листе есть денежная единица в формате, в котором используются другие разделители, используйте команду «Текст в столбцы», чтобы преобразовать значения.
Например, если в данных есть числа, показанные в немецкой валюте — 987,654,32, — ваши настройки Excel могут не распознать их как настоящие числа из-за разделителей в числах.
- В немецкой валюте в качестве разделителя тысяч используется точка, а в качестве десятичного разделителя – запятая.
- В валюте США используется запятая в качестве разделителя тысяч и точка в качестве десятичного разделителя.
Попробуйте выполнить следующие действия, чтобы преобразовать данные из валюты Германии в валюту США – 987 654,32
- Выберите ячейки, содержащие числа.
- На вкладке "Данные" нажмите "Текст в столбцы".
- Откроется диалоговое окно мастера преобразования текста в столбцы, в котором показан шаг 1.
- Дважды нажмите кнопку "Далее" (шаг 1 и шаг 2).
- На шаге 3 нажмите кнопку "Дополнительно".
- В раскрывающемся списке "Десятичный разделитель" выберите разделитель, который в настоящее время используется в значениях — в этом примере "," (запятая).
- В раскрывающемся списке "Разделитель тысяч" выберите разделитель, который в настоящее время используется в значениях, -- "." (точка) в этом примере
- Нажмите "ОК", затем нажмите "Готово".
Преобразование знаков минус в конце
Если вы импортируете числа со знаком минус в конце, вы можете использовать один из следующих методов для преобразования их в отрицательные числа.
Текст в столбцы
С помощью функции "Текст в столбцы" импортированные числа со знаками "минус" в конце можно легко преобразовать в отрицательные числа.
- Выберите ячейки, содержащие числа.
- Выберите «Данные»> «Текст в столбцы».
- Чтобы просмотреть параметр Trailing Minus, нажмите "Далее", затем нажмите "Далее".
- На шаге 3 нажмите кнопку "Дополнительно".
- Установите флажок "Минус в конце для отрицательных чисел", нажмите "ОК".
- Нажмите "Готово".
Примечание. Если установлен флажок "Конечный минус для отрицательных чисел", вы можете нажать "Готово" на шаге 1 мастера "Текст в столбцы".
Преобразование знака минус в конце — формула
Спасибо Бобу Райану из компании Simply Learning Excel, который прислал эту формулу для исправления импортированных чисел со знаками минус в конце.
Выполните следующие действия, чтобы создать формулу, показанную на снимке экрана ниже:
- В этом примере первое число со знаком минус в конце находится в ячейке A1.
- Выберите ячейку B1 и введите следующую формулу:
- =ЕСЛИ(ПРАВО(A1,1)="-",-ЗНАЧ(ЛЕВО(A1,ДЛСТР(A1)-1)),ЗНАЧ(A1))
- Скопируйте формулу до последней строки данных.
В формуле функция ПРАВИЛЬНО возвращает последний символ в ячейке A1.
Если этот символ является знаком минус, функция ЗНАЧ возвращает числовое значение слева от завершающего знака минус.
Знак минус перед функцией ЗНАЧ меняет значение на отрицательное.
Программное преобразование завершающих знаков минус
Во всех версиях Excel можно использовать следующий макрос для преобразования чисел со знаками минус в конце.
Вставить как CSV
При импорте данных вы можете запретить вставку скопированных чисел в виде текста, если вставляете данные в формате CSV.
- Скопируйте данные в другую программу
- Переключиться на Excel
- Нажмите правой кнопкой мыши ячейку, с которой начнется вставка, и выберите "Специальная вставка".
- Выберите «Правка»> «Специальная вставка».
- Выберите CSV, если он есть в списке, и нажмите "ОК".
Расшифровка: исправление чисел, которые не складываются
Вот полная расшифровка показанного выше видеоролика «Исправить номера, которые не добавляются».
Если вы импортируете или копируете данные в Excel, например выписку по счету, иногда цифры складываются неправильно. Посмотрим, как это исправить.
Вот очень небольшой образец банковского счета. У нас есть номера чеков и сумма каждого чека.
Чтобы создать итог, я могу перейти на вкладку "Главная" и справа нажать "Автосумма". И обычно это выбирает любые числа выше, но в этом случае это не так, поэтому я вручную выбираю их и нажимаю Enter, и он показывает ноль. Так что, несмотря на то, что у меня есть сотни долларов, он показывает ноль.
Проверить содержимое ячейки
Я добавлю на этот лист еще пару функций и просто узнаю, что происходит в этих ячейках.
В этой ячейке я собираюсь подсчитать все, что находится в этих ячейках, будь то текст или числа.
Здесь я буду использовать открывающую скобку COUNTA = COUNTA. Затем я выберу ячейки с номерами, закрою квадратные скобки и нажму Enter.
В этих четырех ячейках что-то есть. Но сколько из них имеют номера? Для этого мы используем COUNTA.
В этой ячейке я использую СЧЁТ, и он подсчитывает только числа. Таким образом, COUNT открывает квадратную скобку, снова выберите четыре ячейки, закройте скобку и нажмите Enter.
У нас есть четыре ячейки, в которых что-то есть, но ни в одной из этих ячеек нет чисел.
Проверить панель формул
Если я посмотрю на одну из этих ячеек и посмотрю на строку формул,
Я вижу номер, но перед номером стоит апостроф, который указывает, что это текст, а не число.
Итак, все, что мы откуда-то скачали или скопировали, пришло в виде текста.
Исправьте цифры
Однако это можно быстро исправить. Мы собираемся выбрать пустую ячейку, а затем использовать специальную вставку, чтобы вставить ее поверх этих чисел, и она добавит ноль ко всему, что не повлияет на эти значения, но изменит их с текста на числа. этим простым шагом.
Я выберу пустую ячейку и скопирую. Затем выберите ячейки, которые я хочу исправить, и перейдите к «Вставить», щелкните стрелку раскрывающегося списка и перейдите к «Специальная вставка». А здесь я хочу добавить, поэтому я выберу это. Нажмите "ОК".
А теперь все они заменены числами, и мы можем видеть итог внизу.
Я мог бы отформатировать их так, чтобы они все выровнялись. Я бы тоже отформатировал. И теперь у нас есть правильное и красиво отформатированное значение.
Нажмите здесь, чтобы вернуться к показанному выше видеоролику «Исправить номера, которые не добавляются»
Получить образец файла
Загрузите заархивированный файл с примерами данных и макросов. Заархивированный файл имеет формат xlsm и содержит макросы, поэтому включите их для проверки кода.
Читайте также: