покупка
Как предотвратить изменение последнего числа на ноль в Excel?
В Excel, как правило, количество вводимых чисел ограничено 15 символами, если числовая строка длиннее 15 символов, и число будет отображаться как 0 с 16-го символа, как показано ниже. Эта проблема должна вызвать проблемы у многих финансовых сотрудников, потому что номера кредитных карт, которые им нужно ввести в листы, обычно длиннее 15 номеров. Здесь я расскажу о приемах, которые помогут предотвратить изменение числа на ноль в Excel.
Остановить изменение последнего числа на 0 с добавлением одинарной кавычки
Чтобы число не изменилось на ноль, вы можете добавить одинарную кавычку перед вводом чисел в ячейку.
Выберите пустую ячейку и введите ‘ в нее, а затем введите числа, и вы увидите, что все числа точно отображаются в ячейке. Смотрите скриншот: \
Остановить изменение последнего числа на 0 с помощью форматирования ячеек
Дополнительно к добавлению одинарных кавычек одну за другой, чтобы число не обнулялось, вы также можете форматировать ячейки как текстовое форматирование перед вводом чисел.
1. Выделите ячейки, в которые нужно ввести числа, состоящие более чем из 15 цифр, и нажмите Ctrl + 1 для отображения Формат ячеек Диалог.
2. Во всплывающем Формат ячеек диалога под Номер регистрации Вкладка и выберите Текст из Категории список. Смотрите скриншот:
3. Затем нажмите OK, а когда вы вводите числа в выбранные ячейки, число больше не будет изменяться на ноль.
Excel: Ссылки на ячейки и книги
Часто при работе с регулярно меняющимися данными, мы вынуждены вручную менять их значения во многих таблицах (отчетах), где они используются для расчета. В такой ситуации ссылки в Excel помогут значительно упростить работу с данными.
К примеру, можно разместить данные о ценах на товары на одном листе, а во всех расчетных таблицах прописать ссылки на ячейки листа с ценами. При изменении цены, необходимо будет изменить данные только на листе цен, на всех других листах, где имеются ссылки, изменение пройдет автоматически.
I. Чтобы создать ссылку на ячейку из другого листа той же книги необходимо:
1. Выделить ячейку, в которую необходимо вставить ссылку.
2. Вставить знак «=»
3. Перейти, с помощью вкладок листов, на лист, из которого будем брать данные.
4. Выделить ячейку с необходимым значением.
Таким образом, мы получим ссылку на ячейку из Листа 2, и в исходной ячейке отобразится нужное значение.
То же самое можно было получить, если вручную ввести в исходную ячейку формулу «=Лист2!В2».
Для заполнения остальных ячеек таблицы, можно протянуть формулу с помощью маркера заполнения,
Теперь при изменении цен на Листе 2 , автоматически будут меняться и значения цен в таблице «Объем продаж».
Если необходимо вставить ссылку на ячейку, находящуюся в текущей книге, но на другом листе, нужно в строке формул ввести ссылку в виде:
= Имя листа ! Адрес ячейки
II. Аналогично можно создать ссылку на ячейку из другой книги.
Первый способ — в ячейку первой книги вносим знак «=», переходим ко второй книге, где выбираем нужную ячейку. Далее нажимаем Enter .
Второй способ — вручную прописать ссылку в ячейку.
Пример ссылки, если книги находятся в одном каталоге: =[Книга1.xlsx]Лист1!A1
=[ Имя книги ] Имя листа! Адрес ячейки
Если книга, на которую вы ссылаетесь, расположена в другом каталоге, то необходимо указать полный путь доступа к ней непосредственно в формуле.
Пример ссылки на ячейку в книге, находящейся в каталоге C:\Мои документы:
=C:\ Мои документы \ [Книга1.xlsx]Лист1!A1
Дата вместо числа в Excel: исправляем разными способами
Наверняка многие пользователи, работая в Экселе, сталкивались с такой проблемой, когда после ввода числа в ячейку, вместо него отображается дата. Давайте разберемся, почему такое происходит, и как решить эту проблему.
Содержание скрыть
- Возвращаем отображение чисел вместо дат
- Метод 1: используем контекстное меню
- Метод 2: пользуемся инструментами на ленте
- Метод 3: жмем горячие клавиши
Возвращаем отображение чисел вместо дат
Смотрите также: “Пропала строка формул в программе Excel: как вернуть”
На самом деле все достаточно просто – вместо числа в ячейке появляется дата по причине того, что для нее задан такой формат (“Дата”). Следовательно, чтобы мы видели именно числовое значение, требуется всего лишь сменить формат на тот, который нам нужен. Справиться с этим можно по-разному.
Метод 1: используем контекстное меню
Данное меню пользуется наибольшей популярностью среди пользователей Excel для решения множества задач. В нашем случае нам оно тоже может помочь.
- Правой кнопкой мыши кликаем по отдельной ячейке или заранее выделенному диапазону ячеек (например, с помощью зажатой левой кнопки мыши). В появившемся меню выбираем команду “Формат ячеек”.
- Мы должны оказаться в окне форматирования во вкладке “Число”. В поле “Числовые форматы” меняем формат “Дата” на “Числовой” (в некоторых случаях, если требуется, можно выбрать “Общий”, “Денежный” или “Текстовый”). Обращаем вниманием на область по надписью “Образец” – именно так будет отображаться содержимое ячейки после применения настроек. По готовности нажимаем OK.
Примечание: некоторые выбранные форматы можно настраивать, например, для числового можно задать количество десятичных знаков, а также включить/отключить разделитель групп разрядов. - Таким образом, нам удалось получить в выбранной ячейке (диапазоне ячеек) конкретное число вместо даты.
Метод 2: пользуемся инструментами на ленте
Этот способ используется реже, чем первый, несмотря на то, что является более простым и реализуется буквально в один клик.
- Встаем в нужную ячейку или выделяем требуемый диапазон элементов.
- Переключаемся во вкладку “Главная”, если находимся в другой, после чего в группе инструментов “Число” щелкаем по стрелке вниз рядом с текущим форматом (в нашем случае – это “Дата”).
- В раскрывшемся списке выбираем формат, который нам нужен (например, “Числовой”).
- Получаем числовые значения вместо дат. Но здесь есть важный нюанс – в отличие от первого метода, возможности детально настроить выбранные форматы у нас нет.
Примечание: Если щелкнуть по пункту “Другие числовые форматы” в конце списка, то откроется окно форматирования, с которым мы уже познакомились ранее.
Метод 3: жмем горячие клавиши
Этот способ используется редко, так как для его реализации нужно запомнить специальную комбинацию клавиш, и по сути, он является альтернативой использованию контекстного меню, позволяя обойтись без него.
Все, что нам нужно сделать – выбрать ячейку (или выделить диапазон), после чего нажать на клавиатуре сочетание Ctrl+1, после чего откроется уже знакомое окно форматирования.
Заключение
Смотрите также: “Как написать римские цифры в Экселе: ручной ввод, вставка символа, функция”
Таким образом, не стоит переживать, если вдруг после ввода числа в какой-либо ячейке таблицы Excel вместо него показывается дата. Все дело в несоответствующем формате, который можно сменить через окно форматирования или инструменты на ленте программы.
Исправление превратившихся в даты чисел в Microsoft Excel
Пользователи регулярно вставляют различные исходные данные на листы в Excel, содержащие числа разных форматов и с разным отделением дробной части от целой. Некоторые из них могут быть считаны алгоритмом как даты и, соответственно, формат ячейки поменяется на соответствующий. Вручную изменение формата ячейки в этом случае не дает никакого результата, поэтому нужно искать другие варианты решения, исправляя превратившиеся в даты числа. Как раз об этом и пойдет речь в рамках данного материала.
Описание сути проблемы
Посмотрите на следующий скриншот. Я специально несколько чисел записал с одинаковым разделителем дробной части от целой, чтобы показать, как Эксель автоматически форматирует их в даты. Такое конвертирование происходит только в том случае, если само число может являться датой. Например, это будет 6.1995 – такое число отобразится как июн.95 и вернуть ему прежний формат уже проблематично.
Связано это с тем, что Excel очень сильно зависит от региональных настроек и может воспринимать разные записи чисел как потенциальные даты, автоматически переводя их в такие. Особенно это касается тех случаев, когда пользователь указывает нестандартный разделитель, то есть использует точку вместо запятой.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостейВариант 1: Предварительное изменение параметров программы
Первый вариант является подготовительным и подойдет в тех случаях, когда вы только собираетесь вставить числа с возможными неправильными разделителями. Суть заключается в изменении параметров правки и выборе своего разделителя, чтобы конвертирование не происходило автоматически. Для решения данной задачи вам понадобится выполнить следующие шаги:
- Если какая-либо книга уже открыта, перейдите на вкладку «Файл», выбрав ее на панели слева.
- В главном окне Microsoft Excel на панели слева щелкните по пункту «Параметры».
- Выберите раздел с настройками «Дополнительно» и снимите галочку с пункта «Использовать системные разделители».
- В качестве разделителя целой и дробной части укажите точку или любой другой знак, который в вашем случае является проблемным. Примените изменения и переходите к импорту чисел, проверяя их на отображение.
Вариант 2: Использование формулы
Далее поговорим о том, как исправить уже вставленные числа, которые подверглись автоматическому конвертированию. Для этого понадобятся вспомогательные ячейки, а также использование простой формулы с несколькими видами вычислений. Вот она:
=--ЕСЛИ(ЯЧЕЙКА("формат";A1)="G"; ПОДСТАВИТЬ(A1;".";","); ТЕКСТ(A1;"М,ГГГГ"))
В этой формуле происходит анализ выбранной ячейки на ее тип. Если он оказывается текстовым, то есть датой, происходит замена точки на запятую с помощью функции ТЕКСТ. Само преобразование обеспечивается двойным умножением на -1, для чего два минуса и стоят в начале формулы. Выглядит это немного непонятно, но работает идеально во всех случаях, что видно на следующем изображении.
Полученные данные можно скопировать и заменить оригиналы, а также формула, как и любые другие, доступна для растягивания. Если значения в ячейке нет, вы получите соответствующее уведомление. В случае, когда число не требует преобразования обратно, оно будет показано в оригинальном виде.
Вариант 3: Создание и активация макроса
В завершение хотелось бы обратиться к среде разработки, встроенной в Microsoft Excel, которая называется Visual Basic. С ее помощью будет создан скрипт, проверяющий данные внутри выбранной ячейки и заменяющий разделитесь на нужный с конвертированием в подходящий формат ячейки. Сам код выглядит следующим образом:
Sub Fix_Numbers_From_Dates() Dim num As Double, cell As Range For Each cell In Selection If Not IsEmpty(cell) Then If cell.NumberFormat = "General" Then num = CDbl(Replace(cell, ".", ",")) Else num = CDbl(Format(cell, "m,yyyy")) End If cell.Clear cell.Value = num End If Next cell End Sub
Для создания макроса с его использованием и дальнейшего применения код нужно будет вставить в модуль и сохранить его для данной книги. Если вы еще не знаете, как осуществляется взаимодействие с Visual Basic, следуйте предложенной инструкции:
- На верхней панели выберите вкладку «Разработчик» и запустите инструмент «Visual Basic».
- Откройте меню «Insert» и выберите из него пункт «Module».
- В окно редактирования модуля вставьте указанный выше код и закройте его.
- Перед выходом из Visual Basic обязательно сохраните изменения, кликнув по значку с изображением шестеренки.
- Остается выделить ячейку для конвертирования, а на той же вкладке «Разработчик» выбрать инструмент управления кодом «Макрос».
- В списке макросов найдите только что созданный и дважды нажмите по нему левой кнопкой мыши, чтобы применить.
- Возвратитесь к таблице и убедитесь в том, что число в ячейке было успешно исправлено на нужный формат. Повторите то же самое с остальными, а для быстрого применения макроса можете использовать сочетание клавиш Alt + F8.
Таким образом вы узнали, что при помощи трех разных вариантов можно быстро исправить числа, поменявшиеся на даты в Excel, а также ограничить их автоматическое преобразование в дальнейшем. Выбор варианта остается за вами, после чего следует только повторить инструкцию.
Читайте также в Комьюнити:
- Как использовать функцию ДЛСТР в Microsoft Excel
- Открытие Microsoft Excel в разных окнах
- Как отобразить день недели по дате в Microsoft Excel