Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как предотвратить ввод значений с пробелами в Excel?

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Ошибки в Excel — поиск и обработка ошибок в Excel

Ошибки в Excel лого

Даже небольшое изменение в рабочем листе Excel может привести к ошибкам в других ячейках. Например, вы можете случайно ввести значение в ячейку, которая ранее содержала формулу. Эта простая ошибка может оказать значительное влияние на другие формулы, и вы можете не обнаружить ее, пока не внесете некоторые изменения в электронную таблицу.

Ошибки ввода можно классифицировать несколькими способами:

Синтаксические ошибки: возникают при неправильном синтаксисе формулы. Например, формула содержит неуместные круглые скобки или функция имеет неправильное количество аргументов.

Логические ошибки: В этом случае формула не возвращает ошибку, но имеет логический недостаток, который приводит к неправильному результату вычисления.

Ошибки неправильной ссылки: логика формул верна, но в формуле используется неправильная ссылка на ячейку. В качестве простого примера, диапазон данных, подлежащих обобщению в формуле SUMM, может не содержать всех элементов, подлежащих обобщению.

Семантические ошибки: например, имя функции написано неправильно, и тогда Excel возвращает ошибку #IMEN?

Циклические ошибки: циклические ссылки возникают, когда формула ссылается сама на себя, прямо или косвенно. Циклические ссылки иногда могут быть полезны, но часто являются признаком проблемы.

Если вы вводите формулу массива, то после завершения ввода необходимо нажать Ctrl + Sift + Enter. В противном случае Excel не поймет, как интерпретировать формулы массива, и выдаст ошибку или неверный результат.

Ошибки неполного расчета: В этом случае формулы рассчитываются не полностью. Чтобы пересчитать все формулы, введите Ctrl + Alt + Shift + F9.

Синтаксические ошибки легче всего найти и исправить. Чаще всего вы узнаете, когда формула содержит синтаксическую ошибку. Например, Excel не позволит вам ввести формулу с непоследовательными круглыми скобками. Другие синтаксические ошибки могут привести к появлению в ячейке рабочего листа следующих ошибок.

Ошибка #ДЕЛ/0!

Если вы создали формулу, которая делит на ноль, Excel выдаст ошибку #DEL/0!

Деление на пустую ячейку также приведет к ошибке, поскольку Excel воспринимает пустую ячейку как ноль. Эта проблема часто встречается при создании формулы для данных, которые еще не были введены. D4 растягивается на весь диапазон (=C4/B4).

Ошибка деление на ноль

Эта формула возвращает отношение между столбцами C и B. Поскольку не все данные для данного дня были введены, формула выдала ошибку #DEL/0!

Формулу IF можно использовать для проверки того, являются ли ячейки в столбце B пустыми или нет, и таким образом избежать ошибки:

Пустая ячейка или 0 в B4 приведут к тому, что эта формула вернет пустое значение; в противном случае будет выведено вычисленное значение.

Альтернативой может быть использование функции ELIGIBLE, которая проверяет наличие ошибок. Если выражение C4/B4 не работает, эта формула вернет пустую строку:

Читайте так же:
Как пакетно переименовать несколько имен диапазонов в Excel?

Ошибка #Н/Д

Эта ошибка возникает, когда формула ссылается на ячейку, которая содержит #H/D.

Обычно после формул подстановки (ERP, DGR, SEARCH и INDEX) возвращается ошибка #N/D. Если совпадение не найдено.

Используйте =ESND(), чтобы поймать ошибку и отобразить пустую ячейку.

В Excel 2013 появилась новая функция USND. В предыдущих версиях эта функция была аналогичной:

Ошибка #ИМЯ?

Excel может выдать ошибку #NAME? в следующих случаях:

  • Формула содержит неопределенный диапазон имен
  • Формула содержит текст, который Excel интерпретирует как неопределенный диапазон имен. Например, неправильно написанное имя функции возвращает ошибку #IMYA?
  • Формула содержит текст, не заключенный в кавычки
  • Формула содержит ссылку на диапазон, в которой отсутствует двоеточие между адресами ячеек
  • Формула использует функцию рабочего листа, определенную надстройкой, но эта надстройка не была установлена

Ошибка #ПУСТО!

Ошибка #PUT! возникает, когда формула пытается использовать пересечение между двумя диапазонами, которые на самом деле не пересекаются. Оператором пересечения в Excel является пробел. Формула #EASY возвращает результат! потому что диапазоны не пересекаются.

ошибка пересечения

Ошибка #ЧИСЛО!

Ошибка #Number! возвращается в следующих случаях:

  • В числовой аргумент формулы введено нечисловое значение (например, $1,000 вместо $1,000)
  • В формулу введен недопустимый аргумент (например, =CORN(-12))
  • Функция, использующая итерацию, не может вычислить результат. Примеры функций, использующих итерацию: VSD(), STATE()
  • Формула возвращает слишком большое или слишком маленькое значение. Excel поддерживает значения от -1E-307 до 1E-307.

Ошибка #ССЫЛКА!

Ошибка #RELATED! возникает, когда в формуле используется недопустимая ссылка на ячейку. Ошибка возникает в следующих ситуациях:

  • Столбец или строка, на которые ссылается ячейка формы, были удалены. Например, следующая формула возвращает ошибку, если первая строка или столбец A или B были удалены:
  • Вы удалили рабочий лист, на который ссылается ячейка в формуле. Например, следующая формула вернет ошибку, если лист 1 был удален:
  • Вы скопировали формулу в такое место, где относительная ссылка становится недействительной. Например, если вы скопируете формулу из ячейки A2 в ячейку A1, формула вернет ошибку #RELATION!, потому что она пытается сослаться на несуществующую ячейку.
  • Вы вырезаете ячейку, а затем вставляете ее в ячейку, на которую ссылается формула. В этом случае вы получите ошибку #RELATED!

Ошибка #ЗНАЧ!

Ошибка #NAME! является самой распространенной и возникает в следующих ситуациях:

  • Аргумент функции имеет неверный тип данных, или формула пытается выполнить операцию с использованием неверных данных. Например, когда вы пытаетесь добавить числовое значение к текстовому, формула выдает ошибку
  • Аргументом функции является диапазон, а должно быть одно значение
  • Пользовательские функции электронных таблиц не вычисляются. Чтобы принудительно выполнить новый расчет, нажмите Ctrl + Alt + F9
  • Пользовательская функция рабочего листа пытается выполнить операцию, которая не является допустимой. Например, пользовательская функция не может изменить среду Excel или внести изменения в другие ячейки
  • Вы забыли нажать Ctrl + Shift + Enter при вводе формулы массива
Читайте так же:
Как подсчитать уникальные значения между двумя датами в Excel?

Вам также могут быть интересны следующие статьи

5 комментариев

Ренат, прошу у вас помощи,как у спеца… Создала книгу с кучей страниц и ссылок на разные страницы. Потом необходимо построить диаграммы, а у меня мастер диаграмм почему-то стал неактивен, и никак не могу сделать ни через «Вставку», никак. Может я что-то в настройках сбила? помогите, пожалуйста, что можно проверить.

Людмила, ни разу не сталкивался с проблемой неактивности мастера диаграмм по причине большого количества связей. В любом случае необходимо взглянуть на книгу. Мой ящик — admin@exceltip.ru

Людмила, возможно, что графического помощника больше нет, если книга находится в общем доступе. Если это так, сделайте книгу доступной для монополии, вставьте диаграммы и снова сделайте книгу доступной для совместного использования.

Как предотвратить ввод значений с пробелами в Excel?

У вас есть несколько вариантов ручного ввода данных в Excel. Вводите данные в одну ячейку, несколько ячеек одновременно или весь лист за один раз. Можно вводить числа, текст, даты и время. Для представления данных доступны различные форматы. Кроме того, есть несколько параметров, которые можно настроить для упрощения ввода данных.

В этом разделе не описывается, как использовать форму данных для ввода данных на листе. Дополнительную информацию о работе с формами данных см. в статье Добавление, редактирование, поиск и удаление строк с помощью формы данных.

Важно: Если вы не можете вводить или редактировать данные в рабочем листе, рабочий лист можно защитить, чтобы предотвратить случайное изменение данных. В защищенном рабочем листе можно выбрать ячейки для отображения данных, но нельзя вводить данные в заблокированные ячейки. В большинстве случаев не следует снимать защиту с рабочего листа, пока не будет получено разрешение от пользователя, который его создал. Чтобы снять защиту с листа, нажмите кнопку Снять защиту с листа в группе Редактирование на вкладке Рецензирование. Если при включении защиты листа вы установили пароль, то для снятия защиты листа необходимо сначала ввести этот пароль.

Выделите ячейку на листе.

Нажмите ввод или TAB для ввода числа или текста.

Вы можете ввести данные в новую строку в ячейке, нажав ALT+ENTER после ввода разрыва строки.

На вкладке «Файл» выберите «Параметры».

Только Excel 2007: нажмите кнопку Microsoft Office и выберите Excel параметры.

В меню Дополнительные параметры выберите Изменить параметры, а затем установите флажок Автоматически вставлять десятичную точку.

В поле Количество десятичных знаков введите положительное число для количества цифр справа от десятичной точки или отрицательное число для количества цифр слева от десятичной точки.

Читайте так же:
Как предотвратить выбор нескольких листов в Excel?

Если ввести 3 в Число десятичных знаков и 2834 в ячейку, появится ответ 2,834. Если ввести -3 в Число десятичных знаков, а затем — 283, значение будет равно 283000.

Введите нужное число в выделенную ячейку.

Изменение настройки Фиксированный десятичный формат не влияет на уже внесенные данные.

Чтобы избежать использования опции Фиксированная десятичная дробь для числа, введите десятичную точку после введения числа.

Выберите ячейку листа.

Введите дату или время, как показано ниже.

При вводе даты используйте точку или дефис в качестве разделителя, например 09/05/2002 или 5-Sep-2002.

Чтобы ввести время суток в 12-часовом формате, введите a или p, отделенные пробелом от значения времени, например 9:00 p. В противном случае значение будет введено как время первой половины дня.

Чтобы ввести текущую дату и время, нажмите CTRL+SHIFT+ ; (точка с запятой).

Вы можете использовать TODAY и DATE для ввода даты или времени, которые остаются неизменными при повторном открытии листа.

Когда вы вводите дату или время в ячейку, она отображается в формате по умолчанию компьютера или в формате, который использовался в ячейке до ввода даты или времени. Формат даты или времени по умолчанию зависит от настроек даты и времени в диалоговом окне настроек языка и региона (Панель управления, Часы, Язык и регион). Если вы измените эти настройки на компьютере, значения даты или времени, которые не были отформатированы с помощью команды Формат ячеек, будут отображаться в соответствии с этими настройками.

Чтобы использовать формат даты или времени по умолчанию, щелкните дату или время и нажмите CTRL+SHIFT+# или CTRL+SHIFT+@.

Выберите ячейки, в которые нужно ввести те же данные. Они не обязательно должны находиться рядом друг с другом.

Нажмите CTRL+ENTER для ввода данных в активной ячейке.

Вы также можете ввести одни и те же данные в несколько ячеек, используя маркер заполнения для автоматического заполнения ячеек листа.

Сделав несколько листов активными одновременно, вы можете ввести новые или изменить существующие данные на одном из листов, и изменения будут применены к тем же ячейкам всех выбранных листов.

Вам нужно щелкнуть ярлык первого листа, содержимое которого вы хотите изменить. Удерживая клавишу CTRL, вы можете щелкнуть ярлыки других листов, с которыми вы хотите синхронизироваться.

Кнопки прокрутки листов

Примечание: Если нужный ярлык листа не виден, используйте кнопки прокрутки листа, чтобы найти его, а затем щелкните. Если ярлыки нужных листов по-прежнему не видны, следует увеличить окно документа.

Выберите ячейку или область активного рабочего листа, где необходимо изменить или ввести данные.

В активной ячейке введите новые данные или отредактируйте существующие, затем нажмите ENTER или TAB для перемещения выделения в следующую ячейку.

Читайте так же:
Как предотвратить копирование и вставку ячейки с выпадающим списком в Excel?

Изменения будут применяться ко всем выбранным листам.

Выполняйте предыдущие шаги до тех пор, пока данные не будут введены или отредактированы.

Чтобы отменить выделение нескольких листов, щелкните один из листов, который не выбран. Если на экране видны только выбранные листы, щелкните правой кнопкой мыши ярлык листа и выберите Разгруппировать листы.

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

Кнопка Новое окно находится в группе Окно вкладки Вид.

Чтобы просмотреть лист, перейдите в новое окно и нажмите на него.

Каждый лист, который вы хотите просмотреть, нужно просматривать одинаково.

В группе Окно на вкладке Вид нажмите Упорядочить все, а затем выберите нужную опцию.

Чтобы просмотреть только листы активной книги, установите флажок Только окна текущей книги в диалоговом окне «Расположение окон».

Ввод данных в Excel упрощается с помощью ряда настроек. Изменения могут затрагивать все книги, все рабочие листы или отдельные ячейки.

Изменение направления нажатия клавиши Enter

По умолчанию, если вы используете клавишу TAB для ввода данных в несколько ячеек подряд, а затем нажимаете Enter в конце строки, курсор переместится в начало следующей строки.

При нажатии клавиши Enter курсор перемещается на одну ячейку вниз, а при нажатии клавиши TAB — на одну ячейку вправо. Хотя изменить направление клавиши TAB невозможно, это можно сделать для клавиши Enter. Изменение этого параметра влияет на весь лист, все другие открытые листы и книги, а также новые книги.

Перейдите на вкладку Параметры на вкладке Файл.

Только Excel 2007: нажмите кнопку Microsoft Office и выберите Excel параметры.

В группе Дополнительно раздела Параметры редактирования установите флажок Перейти к другой ячейке при нажатии Enter и выберите нужное направление из списка Направление.

Изменение ширины колонок

В ячейке может отображаться строка типа #####. Если ячейка содержит число или дату, а ширина столбца недостаточна для отображения всех необходимых символов, происходит следующее. Предположим, например, что в столбце с форматом даты «дд.мм.гггг» содержится дата 31.12.2015. Однако столбец может отображать только шесть символов. В ячейке будет отображаться строка #####. При просмотре содержимого ячейки в текущем формате необходимо увеличить ширину столбца.

Если вы хотите изменить ширину столбца, выберите ячейку.

Нажмите на кнопку Формат в группе Ячейки на вкладке Главная.

Группа "Ячейки" на вкладке "Главная"

Выполните одно из следующих действий, когда вы выделите Размер ячеек.

Можно выбрать команду Автоширина столбца для автоматического изменения размера ячеек под текст.

Читайте так же:
Как переместить данные из столбца в строку или наоборот в Excel?

Чтобы увеличить ширину колонки, выберите команду Ширина и введите нужное значение в поле Ширина колонки.

Примечание: Вы также можете увеличить ширину столбца, изменив формат столбца или отдельной ячейки. Например, вы можете изменить формат даты, чтобы показывать только день и месяц (формат «дд.мм»), например 31.12, или представить число в экспоненциальном формате, например 4E+08.

Передача текста в ячейку

Перетаскивая текст в ячейку, можно отобразить несколько строк текста. Перетаскивание текста в ячейку не влияет на другие ячейки.

Чтобы переместить текст, щелкните ячейку, в которую нужно переместить текст.

Выберите пункт Переместить текстовый элемент в группе Выравнивание на вкладке Главная

Группа "Выравнивание" на вкладке "Главная"

Примечание: Если текст представляет собой одно длинное слово, символы не будут перемещаться; вы можете расширить колонку или уменьшить размер шрифта, чтобы сделать весь текст видимым. Если после включения переноса текста не весь текст виден, возможно, необходимо изменить высоту строки. На вкладке Главная в группе Ячейки нажмите Формат, а затем в разделе Размер ячеек выберите Автоматически регулировать высоту строки.

Чтобы узнать больше о перемещении текста в ячейку, см. раздел Перемещение текста в ячейку.

Изменить формат числа

Excel представляет формат ячейки отдельно от данных в ней. Это различие в отображении может иметь значительные последствия, если данные являются числовыми. Например, округление введенного числа приводит к округлению только отображаемого числа. Вычисления, с другой стороны, используют фактическое значение, хранящееся в ячейке, а не отформатированное число, отображаемое на экране. Поэтому вычисления могут выглядеть некорректно из-за округления в одной или нескольких ячейках.

После ввода чисел в ячейку можно изменить формат, в котором они отображаются.

Выберите ячейку с нужными числами.

Выберите числовой формат, нажав на стрелку рядом с полем Числовой формат на вкладке Главная.

Список "Числовой формат" на вкладке "Главная"

Чтобы выбрать формат из списка доступных форматов, нажмите кнопку Advanced Digital Formats, затем выберите нужный формат из списка Digital Formats.

Форматирование цифр как текста

Если номер не требует вычисления, например, номер телефона, можно использовать текстовый формат и применять его по существу до ввода текста.

Выберите пустой ящик.

На вкладке Главная в группе Число нажмите стрелку рядом с полем Формат числа и выберите Текст.

Список "Числовой формат" на вкладке "Главная"

Введите нужные числа в форматированную ячейку.

Числа, которые были введены до применения форматирования текста к ячейкам, должны быть повторно введены в отформатированные ячейки. Чтобы ускорить ввод чисел в текстовом формате, нажмите клавишу F2, а затем клавишу Enter.

Дополнительные сведения

Сотрудник Excel Tech Community всегда готов ответить на ваши вопросы или оказать помощь в сообществе «Ответы».

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector