Ошибка число в excel как исправить
Как убрать ошибки #ЗНАЧ! и Н/Д в формулах «Excel»
Ошибки в формулах ExcelЧасто при использовании формул в программе «Excel» из за не корректно введенных значений могут возникать ошибки #ЗНАЧ! и Н/Д в формулах.
В идеале следует вносить информацию для расчетов таким образом, чтобы ошибок не возникало, но бывают случаи, что ошибки должны возникать при определенных условиях в динамично заполняемых файлах и избавиться от них нельзя. Так же нельзя и удалить сами формулы из ячеек.
В таких случаях файл с ошибками выглядит не очень презентабельно и их нужно прятать от посторонних глаз, чтобы не смущать пользователей.
Рассмотрим функцию «Excel» при помощи, которой можно убрать (спрятать) ошибки #ЗНАЧ! и Н/Д в ячейках.
Эта функция обработки ошибок «ЕСЛИОШИБКА()».
Еслиошибка()Как работает функция ЕСЛИОШИБКА()?
Данная функция позволяет заменить, возникающие ошибки, на любое значение.
Например, дефис («-») или звездочку («*»).
Функция ЕСЛИОШИБКА() работает следующим образом:
В поле «ЗНАЧЕНИЕ» вводиться формула, в которой может возникать ошибка.
Поле значениеВ поле «Значение_если_ошибка» вводится то значение, которое присваивается ячейке при возникновении ошибки в формуле.
Значение, если ошибка
Эксель не считает формулы показывает 0. Проблемы с работой формул в Microsoft Excel
Дата: 15 ноября 2018 Категория:Здравствуйте, друзья. Бывало ли у Вас, что, после ввода формулы, в ячейке отображается сама формула вместо результата вычисления? Это немного обескураживает, ведь Вы сделали все правильно, а получили непонятно что. Как заставить программу вычислить формулу в этом случае?
Для начала нужно разобраться с причинами такого поведения, после чего исправлять ситуацию. А причины могут быть такими.
Включено отображение формул
В Экселе есть режим проверки вычислений. Когда он включен, вы видите на листе формулы. Как и зачем применять отображение формул, я рассказывал в . Проверьте, возможно он активирован, тогда отключите. На ленте есть кнопка Формулы – Зависимости формул – Показать формулы . Если она включена – кликните, по ней, чтобы отключить.
Часто показ формул включают случайно, нажав комбинацию клавиш Ctrl+` .
Формула воспринимается программой, как текст
Если Excel считает, что в ячейке текстовая строка, он не будет вычислять значение, а просто выведет на экран содержимое. Причин тому может быть несколько:
Я перечислил все распространенные случаи, когда формула отображается вместо результата. Думаю, Ваш случай будет в приведенном списке, устранить проблему не составит труда. Если не получилось – пишите в комментариях.
Обещает быть очень интересной. Мы рассмотрим некоторые простые приемы быстрого написания формул. Если довести их до автоматизма, Ваша работа будет выполняться проще и быстрее. Подпишитесь на обновления, чтобы не пропустить!
При ошибочных вычислениях, формулы отображают несколько типов ошибок вместо значений. Рассмотрим их на практических примерах в процессе работы формул, которые дали ошибочные результаты вычислений.
Ошибки в формуле Excel отображаемые в ячейках
В данном уроке будут описаны значения ошибок формул, которые могут содержать ячейки. Зная значение каждого кода (например: #ЗНАЧ!, #ДЕЛ/0!, #ЧИСЛО!, #Н/Д!, #ИМЯ!, #ПУСТО!, #ССЫЛКА!) можно легко разобраться, как найти ошибку в формуле и устранить ее.
Как убрать #ДЕЛ/0 в Excel
Как видно при делении на ячейку с пустым значением программа воспринимает как деление на 0. В результате выдает значение: #ДЕЛ/0! В этом можно убедиться и с помощью подсказки.
В других арифметических вычислениях (умножение, суммирование, вычитание) пустая ячейка также является нулевым значением.
Результат ошибочного вычисления – #ЧИСЛО!
Неправильное число: #ЧИСЛО! – это ошибка невозможности выполнить вычисление в формуле.
Несколько практических примеров:
Ошибка: #ЧИСЛО! возникает, когда числовое значение слишком велико или же слишком маленькое. Так же данная ошибка может возникнуть при попытке получить корень с отрицательного числа. Например, =КОРЕНЬ(-25).
В ячейке А1 – слишком большое число (10^1000). Excel не может работать с такими большими числами.
В ячейке А2 – та же проблема с большими числами. Казалось бы, 1000 небольшое число, но при возвращении его факториала получается слишком большое числовое значение, с которым Excel не справиться.
В ячейке А3 – квадратный корень не может быть с отрицательного числа, а программа отобразила данный результат этой же ошибкой.
Как убрать НД в Excel
Значение недоступно: #Н/Д! – значит, что значение является недоступным для формулы:
Записанная формула в B1: =ПОИСКПОЗ(„Максим”; A1:A4) ищет текстовое содержимое «Максим» в диапазоне ячеек A1:A4. Содержимое найдено во второй ячейке A2. Следовательно, функция возвращает результат 2. Вторая формула ищет текстовое содержимое «Андрей», то диапазон A1:A4 не содержит таких значений. Поэтому функция возвращает ошибку #Н/Д (нет данных).
Ошибка #ИМЯ! в Excel
Относиться к категории ошибки в написании функций. Недопустимое имя: #ИМЯ! – значит, что Excel не распознал текста написанного в формуле (название функции =СУМ() ему неизвестно, оно написано с ошибкой). Это результат ошибки синтаксиса при написании имени функции. Например:
Ошибка #ПУСТО! в Excel
Пустое множество: #ПУСТО! – это ошибки оператора пересечения множеств. В Excel существует такое понятие как пересечение множеств. Оно применяется для быстрого получения данных из больших таблиц по запросу точки пересечения вертикального и горизонтального диапазона ячеек. Если диапазоны не пересекаются, программа отображает ошибочное значение – #ПУСТО! Оператором пересечения множеств является одиночный пробел. Им разделяются вертикальные и горизонтальные диапазоны, заданные в аргументах функции.
В данном случаи пересечением диапазонов является ячейка C3 и функция отображает ее значение.
Заданные аргументы в функции: =СУММ(B4:D4 B2:B3) – не образуют пересечение. Следовательно, функция дает значение с ошибкой – #ПУСТО!
#ССЫЛКА! – ошибка ссылок на ячейки Excel
В данном примере ошибка возникал при неправильном копировании формулы. У нас есть 3 диапазона ячеек: A1:A3, B1:B4, C1:C2.
Под первым диапазоном в ячейку A4 вводим суммирующую формулу: =СУММ(A1:A3). А дальше копируем эту же формулу под второй диапазон, в ячейку B5. Формула, как и прежде, суммирует только 3 ячейки B2:B4, минуя значение первой B1.
Когда та же формула была скопирована под третий диапазон, в ячейку C3 функция вернула ошибку #ССЫЛКА! Так как над ячейкой C3 может быть только 2 ячейки а не 3 (как того требовала исходная формула).
Примечание. В данном случае наиболее удобнее под каждым диапазоном перед началом ввода нажать комбинацию горячих клавиш ALT+=. Тогда вставиться функция суммирования и автоматически определит количество суммирующих ячеек.
Как исправить ЗНАЧ в Excel
#ЗНАЧ! – ошибка в значении. Если мы пытаемся сложить число и слово в Excel в результате мы получим ошибку #ЗНАЧ! Интересен тот факт, что если бы мы попытались сложить две ячейки, в которых значение первой число, а второй – текст с помощью функции =СУММ(), то ошибки не возникнет, а текст примет значение 0 при вычислении. Например:
Решетки в ячейке Excel
Ряд решеток вместо значения ячейки ###### – данное значение не является ошибкой. Просто это информация о том, что ширина столбца слишком узкая для того, чтобы вместить корректно отображаемое содержимое ячейки. Нужно просто расширить столбец. Например, сделайте двойной щелчок левой кнопкой мышки на границе заголовков столбцов данной ячейки.
Так решетки (######) вместо значения ячеек можно увидеть при отрицательно дате. Например, мы пытаемся отнять от старой даты новую дату. А в результате вычисления установлен формат ячеек «Дата» (а не «Общий»).
Неправильный формат ячейки так же может отображать вместо значений ряд символов решетки (######).
Как посчитать сумму значений в Excel
Допустим, у вас есть столбец со значениями и вам необходимо узнать их сумму. Самый простой способ – вы можете выделить нужные ячейки и в правом нижнем углу Excel, в строке состояния, вы увидите сумму значений, их количество и даже среднее арифметическое значение.
Если полученную сумму вам необходимо где-то использовать или просто вывести в ячейку, воспользуемся функцией СУММ. Она может работать как в ручном режиме, так и в автоматическом. Выделите нужные ячейки и нажмите кнопку «Автосумма», она находится на вкладке «Главная». Формула с расчетом суммы появится в следующей ячейке ниже выделенных вами. Чтобы вставить с суммой в произвольном месте, выделите лубую свободную ячейку и наберите в ней «=СУММ(» без кавычек, далее мышкой выделите нужный диапазон и нажмите Enter на клавиатуре.
Почему может не считаться сумма в Excel
Есть две наиболее вероятных причины.
1. Неправильный разделитель дробной части. К примеру в Windows стоит запятая, а у в таблице вас точка. Попробуйте заменить знак разделения для пары значений и сложите их. Чтобы увидеть какой знак стоит в системе, зайдите в «Панель управления», выберите «Язык и региональные стандарты» -> «Дополнительные параметры»:
2. Ваши ячейки в текстовом формате и в них кроме цифр есть лишние знаки, к примеру, пробелы. Выделите их и нажмите правую клавишу мыши. Выберите «Формат ячеек» в меню и выставите «Числовой» формат, если он не такой. Проверьте, что кроме цифр в ячейках ничего нет.
Если у вас остались вопросы или вам нужны уточнения – вопрос, наши эксперты оперативно ответят на ваш запрос.
Главная функция программы Excel – работа с формулами. Именно для этого она чаще всего и используется. Иногда случается, что не работают формулы в Excel, в таком случае нужно что-то делать. Именно об этом и пойдет речь далее.
Устраняем проблему
Причин, почему в Excel не работает формула довольно много. Возможно у вас неверно выставлены настройки программы или конкретного диапазона, либо некорректно введена формула.
При протягивании не работают формулы
Суть ошибки в том, что создается несколько столбцов, например, 2, а в третьем должен выводиться результат. Там указывается формула и протягивается вниз, чтобы охватить весь диапазон, но в итоге значение из первой ячейки просто переносится на следующие. В таком случае есть несколько способов, как все исправить:
- Нажмите кнопку F9, чтобы начался пересчет листа.
- Измените формат ячейки на общий. Для этого нужно выделить ее и кликнуть правой кнопкой мыши. Откроется меню, где следует выбрать «Формат ячейки» и изменить на «Общий».
- Откройте параметры вычисления формул и проверьте, чтобы отметки стояли, как на скрине.
Формула не считает
Есть несколько причин, почему формула в экселе не считает:
- Не верный формат ячейки. Для его изменения, нужно выделить нужный диапазон ячеек и выбрать «Формат Ячейки».
- Активирован режим «Показать формулы». Для его отключения нужно на панели сверху перейти в раздел «Формулы», там кликнуть на «Зависимости формул» и отключить «Показать формулы».
- Неверно введена формула или ее синтаксис. В таком случае отображается формула вместо значения. Нужно внимательно ее проверить.
Это несколько основных причин, почему Эксель не считает формулы.
Программа не считает формулу, введённую в ячейку
Такое часто случается из-за того, что установлен текстовый формат ячейки. Как его сменить на другой уже описывалось ранее.
Не происходит подсчет суммы
Есть два варианта, почему приложение Эксель не считает сумму:
- Формат ячеек является тестовым.
- Деактивирован подсчет суммы. Нужно, используя правую кнопку мыши клацнуть на специальной панели и установить отметку на «Сумма».
Вот, почему не считается сумма.
Не верно суммируются данные ячеек
Некоторые пользователи столкнулись с тем, когда Еxcel неправильно считает сумму выделенных ячеек. Зачастую это происходит из-за того, что некоторые ячейки, а то и большая их часть, имеют текстовый формат. Из-за этого суммируются не все данные и результат получается некорректным. Нужно изменить формат на «Общий», чтобы устранить проблему.
Отключен перерасчет
Бывает, что пользователь подсчитал все данные, но при их корректировании в связанных с формулой ячейках итог не меняется. Это означает, что неверно выставленные параметры:
- Откройте параметры программы.
- Там следует перейти в раздел «Формулы». Сверху будет блок «Параметры вычислений», а там параметр «Вычисления в книге», где должна быть выбрана позиция «Автоматически». Если нет, выставляете как следует и нажимаете «ОК».
Проблемы с работой формул в Microsoft Excel
Теперь, если какое-либо значение будет изменено, произойдет пересчет результата.
Из всего этого видно, что существует много разных причин, почему Эксель производит расчет формулы неправильно или вовсе отказывается их считать. Если у пользователя там, где должен быть результат отображается формула, значит формат ячейки установлен текстовый либо в самой формуле допущена ошибка. Возможно активирован режим просмотра. Еще пользователи часто забывают ставить «=» перед началом формулы. Если в связанных ячейка изменяются значения, а результат остается прежним, нужно проверить активировано ли автообновление. В некоторых случая в ячейках с итогами отображается ошибка, тогда следует проверить все значения.
Вы можете сами настроить отображение нулевых значений в ячейке или использовать в таблице набор стандартов форматирования, которые требуют скрывать нулевые значения. Отображать и скрывать нулевые значения можно разными способами.
Скрытие и отображение всех нулевых значений на листе
Выберите Файл > Параметры > Дополнительно .
В группе
Скрытие нулевых значений в выделенных ячейках
Выполняйте эти действия для скрытия нулевых значений в выделенных ячейках с помощью числового формата. Если значение в одной из этих ячеек меняется на ненулевое, оно отобразится в ячейке, а формат его будет аналогичен общему числовому формату.
Отображение скрытых значений .
Выделите ячейки со скрытыми нулевыми значениями.
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек .
Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК .
Скрытие нулевых значений, возвращенных формулой
На вкладке Главная Условное форматирование и выберите "Правила выделения ячеек" > "Равно".
В левом поле введите 0 .
В правом поле выберите Пользовательский формат .
В поле Формат ячейки откройте вкладку Шрифт .
В списке Цвет выберите белый цвет и нажмите кнопку ОК .
Отображение нулей в виде пробелов или тире
Для решения этой задачи воспользуйтесь функцией ЕСЛИ .
Если ячейка содержит нулевые значения, для возврата пустой ячейки используйте формулу, например такую:
ЕСЛИ(A2-A3=0;"";A2-A3)
Вот как читать формулу. Если результат вычисления (A2-A3) равен "0", ничего не отображается, в том числе и "0" (это указывается двойными кавычками ""). В противном случае отображается результат вычисления A2-A3. Если вам нужно не оставлять ячейки пустыми, но отображать не "0", а что-то другое, между двойными кавычками вставьте дефис "-" или другой символ.
Выберите отчет сводной таблицы.
На вкладке Анализ в группе Сводная таблица Параметры и выберите пункт Параметры .
Перейдите на вкладку Разметка и формат
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Выберите Файл > Параметры > Дополнительно .
В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения .
Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения .
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек .
В поле Тип введите 0;-0;;@
Примечания:
Скрытые значения отображаются только в строке формул или в ячейке, если вы редактируете ее содержимое. Эти значения не печатаются.
Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек . Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий . Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число .
Выделите ячейку, содержащую нулевое (0) значение.
На вкладке Главная в группе Стили щелкните стрелку рядом с элементом Условное форматирование , наведите указатель на элемент Правила выделения ячеек и выберите вариант Равно .
В левом поле введите 0 .
В правом поле выберите Пользовательский формат .
В диалоговом окне Формат ячеек откройте вкладку Шрифт .
В поле Цвет выберите белый цвет.
Для выполнения этой задачи используйте функцию ЕСЛИ .
Пример
Копирование примера
Функция ЕСЛИ .
Скрытие нулевых значений в отчете сводной таблицы
На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .
Перейдите на вкладку Разметка и формат , а затем выполните следующие действия.
Изменение способа отображения ошибок. В поле Формат установите флажок Для ошибок отображать . Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
Изменение способа отображения пустых ячеек. Установите флажок Для пустых ячеек отображать . Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Отображение и скрытие всех нулевых значений на листе
Скрытие нулевых значений в выделенных ячейках с помощью числового формата
Эти действия позволяют скрыть нулевые значения в выделенных ячейках. Если значение в одной из ячеек станет ненулевым, его формат будет аналогичен общему числовому формату.
Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
Вы можете нажать клавиши CTRL+1 или на вкладке Главная в группе Ячейки щелкнуть Формат > Формат ячеек .
В поле Тип введите 0;-0;;@
Примечания:
Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования
Выделите ячейку, содержащую нулевое (0) значение.
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите "Правила выделения ячеек" > "Равно".
В левом поле введите 0 .
В правом поле выберите Пользовательский формат .
В диалоговом окне Формат ячеек откройте вкладку Шрифт .
В поле Цвет выберите белый цвет.
Использование формулы для отображения нулей в виде пробелов или тире
Для выполнения этой задачи используйте функцию ЕСЛИ.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
Выделите пример, приведенный в этой статье.
Важно: Не выделяйте заголовки строк или столбцов.
Выделение примера в справке
Нажмите клавиши CTRL+C.
В Excel создайте пустую книгу или лист.
Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе "Зависимости формул" нажмите кнопку Показать формулы .
Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.
Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ .
Скрытие нулевых значений в отчете сводной таблицы
Щелкните отчет сводной таблицы.
На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .
Перейдите на вкладку Разметка и формат , а затем выполните следующие действия.
Изменение способа отображения ошибок. В поле Формат установите флажок Для ошибок отображать . Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
Изменение способа отображения пустых ячеек. Установите флажок Для пустых ячеек отображать . Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
Вставка формул и функций в Numbers на Mac
В формулы можно вставлять ссылки на ячейки, диапазоны ячеек и даже целые столбцы и строки с данными, в том числе ссылки на ячейки в других таблицах и на других листах. Приложение Numbers использует для вычисления формулы значения в ячейках, на которые даны ссылки. Например, если включить в формулу ссылку «А1», она будет указывать на значение в ячейке A1 (в столбце А и в ряду 1).
Примечание. Если в таблице используются категории и Вы добавляете новую строку внутри диапазона ячеек, на которые указывают ссылки, результаты формулы не будут включать новую строку, пока Вы не измените ссылки на ячейки.
Приведенные ниже примеры иллюстрируют использование ссылок на ячейки в формулах.
Если в ссылке указывается диапазон, который содержит более одной ячейки, начальная и конечная ячейки разделяются двоеточием.
Если ссылка указывает на ячейку в другой таблице, ссылка должна содержать имя таблицы (это требование является необязательным, если имя ячейки является уникальным в пределах всех таблиц).
Обратите внимание на то, что имя таблицы и ссылка на ячейку разделяются двойным двоеточием (::). Если в формуле дается ссылка на ячейку из другой таблицы, имя таблицы добавляется в ссылку автоматически.
Если ссылка указывает на ячейку в таблице, которая находится на другом листе, в ссылку необходимо также включить имя листа (это требование является необязательным, если имя ячейки является уникальным в пределах всех листов).
СУММ(Лист 2::Таблица 1::C2:G2)
Имя листа, имя таблицы и ссылка на ячейку разделяются двойными двоеточиями. При выборе ячейки в таблице на другом листе во время построения формулы имя листа и имя таблицы включаются в ссылку автоматически.
Чтобы дать ссылку на столбец, можно использовать букву столбца. В приведенной ниже формуле рассчитывается общая сумма значений всех ячеек в третьем столбце.
Чтобы дать ссылку на строку, можно использовать номер строки. В приведенной ниже формуле рассчитывается общая сумма значений всех ячеек в первой строке.
Чтобы дать ссылку на строку или столбец с заголовком, можно использовать сам заголовок. В приведенной ниже формуле рассчитывается общая сумма значений всех ячеек в строке с заголовком «Доход».
Нижеприведенная формула ссылается на столбец по названию его заголовка «Количество гостей» и на ячейку B2 в таблице на другом листе.
Поиск и исправление ошибок в вычислениях Excel новость от 13.03.2014
Идентификация ошибок осуществляется несколькими способами. Один из них реализуется через отображение кода ошибки в ячейке.
|
Ошибка |
Описание |
|
#Н/Д |
Н/Д – является сокращением термина Неопределённые данные. Помогает предотвратить использование ссылки на пустую ячейку |
|
#ЧИСЛО! |
Функция с числовым аргументом использует неприемлемый аргумент |
|
#ИМЯ? |
Ошибка в написании имени или используется несуществующее имя |
|
#ССЫЛКА! |
Используется ссылка на несуществующую ячейку |
|
#ЗНАЧ! |
Вместо числового или логического значения введён текст, и Excel не может преобразовать его к нужному типу данных |
|
#ДЕЛ/0! |
В качестве делителя используется ссылка на ячейку, в которой содержится нулевое или пустое значение (если ссылкой является пустая ячейка, то её содержимое интерпретируется как ноль) |
|
#ПУСТО! |
Используется ошибочная ссылка на ячейку |
|
############# |
Данные не помещаются в ячейку по ширине, следует увеличить ширину столбца или применить более компактное форматирование |
Второй способ обнаружения ошибок – Excel отображает в левом верхнем углу ячейки зелёный треугольник (индикатор ошибки). При выборе такой ячейки появляется смарт-тег проверки ошибок.
Для проверки ошибок необходимо выполнить следующие шаги:
1. Выберите лист, который требуется проверить на наличие ошибок.
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Проверка наличия ошибок. Откроется окно диалога Контроль ошибок.
3. В окне диалога Контроль ошибок просмотрите информацию о текущей ошибке в левой части окна.
4. Для просмотра более детального описания ошибки и возможных вариантов её исправления нажмите кнопку Справка по этой ошибке.
5. Нажмите кнопку Показать этапы вычисления. MS Excel откроет окно диалога Вычисление формулы, где вы сможете просмотреть значения различных частей вложенной формулы, вычисляемые в порядке расчёта формулы:
a) нажмите кнопку Вычислить, чтобы проверить значение подчёркнутой ссылки. Результат вычислений показан курсивом;
b) если подчёркнутая часть формулы является ссылкой на другую формулу, нажмите кнопку Шаг с заходом, чтобы отобразить другую формулу в поле Вычисление. Нажмите кнопку Шаг с выходом, чтобы вернуться в предыдущую ячейку и формулу;
c) Выполняйте указанные действия, пока не будет вычислена каждая часть формулы;
d) Чтобы снова увидеть вычисления, нажмите кнопку Заново;
e) Чтобы завершить вычисления, нажмите кнопку Закрыть.
6. Для изменения формулы в строке формул нажмите кнопку Изменить в строке формул.
7. Если ошибка является несущественной, вы можете нажать кнопку Пропустить ошибку. Помеченная ошибка при последующих проверках будет пропускаться.
8. Для перехода к следующей ошибке нажмите кнопку Далее. Для возврата к предыдущей – кнопку Назад.
9. Доведите до конца проверку ошибок и закройте окно диалога Контроль ошибок.
Трассировка связей между формулами и ячейками
Для удобства поиска причин ошибок, а также и в некоторых других случаях можно графически отобразить связь между ячейками в формуле.
Для отображения ячеек, входящих в формулу в качестве аргументов, необходимо выделить ячейку с формулой и нажать кнопку Влияющие ячейки в группе Зависимости формул вкладки Формулы.
Один щелчок по кнопке Влияющие ячейки отображает связи с ячейками, непосредственно определяющими результат вычисления. Если в этих ячейках также находятся формулы, то следующий щелчок отображает связи с ячейками, являющимися аргументами этих формул. И так далее.
Для отображения ячеек, в формулы которых входит какая-либо ячейка, её следует выделить и нажать кнопку Зависимые ячейки в группе Зависимости формул вкладки Формулы.
Один щелчок по кнопке Зависимые ячейки отображает связи с
ячейками, непосредственно зависящими от выделенной
ячейки. Если эти
ячейки также влияют на другие
ячейки, то следующий щелчок отображает связи с зависимыми
ячейками. И так далее.
Связи в пределах текущего листа отображаются синими стрелками. Связи с
ячейками других листов и книг отображаются чёрными пунктирными линиями и значком листа. Красные стрелки показывают
ячейки, вызывающие ошибки.
Для скрытия стрелок связей следует нажать кнопку Убрать все стрелки в группе Зависимости формул вкладки Формулы. Использование окна контрольных значений.
Если вы работаете с большой таблицей, содержащей формулы, которые ссылаются на различные ячейки текущей таблицы и/или ячейки другого листа, достаточно сложно отследить их взаимосвязи.
В этом случае вашим помощником может выступать панель инструментов Окно контрольного значения.
Окно контрольного значения значительно упрощает анализ, проверку зависимостей и подтверждение вычислений и результатов формул на больших листах. Благодаря окну контрольного значения не требуется постоянно прокручивать лист, когда вы работаете с большой таблицей, переходить к различным его частям.
Добавление ячеек в окно контрольных значений
Для добавления на панель контрольных значений выполните следующие действия:
1. Выделите ячейки, контрольные значения которых нужно поместить на панель.
Чтобы выделить все ячейки листа с формулами, на вкладке Главная в группе Правка нажмите кнопку Найти и выделить и выберите команду Формулы.
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.
4. В окне диалога Добавление контрольного значения проверьте правильность выбранного диапазона и нажмите кнопку Добавить.
Циклические ссылки
Циклические ссылки возникают, когда формула в какой-либо ячейке посредством других ячеек ссылается сама на себя.
Например, ячейка С4 = Е7, Е7 = С11, С11 = С4. В итоге С4 ссылается на С4.
Появление циклических ссылок очень легко определить. При их возникновении или наличии в уже созданной книге Excel сразу же появляется предупреждение о циклической ссылке, которое и описывает суть явления.
При нажатии на кнопку OK сообщение будет закрыто, а в ячейке, содержащей циклическую ссылку, в большинстве случаев появится 0.
Предупреждение, как правило, появляется при первоначальном создании циклической ссылки или открытии книги, содержащей циклические ссылки. Если предупреждение принято, то при дальнейшем возникновении циклических ссылок оно может не появляться.
Циклические ссылки в Excel могут создаваться преднамеренно, для решения тех или иных задач финансового моделирования, а могут возникать случайно, в виде технических ошибок и ошибок в логике построения модели.
В первом случае мы знаем об их наличии, так как сами их предварительно создали, и знаем, зачем они нам нужны.
Во втором случае мы можем вообще не знать, где они находятся, например, при открытии чужого файла и появлении сообщения о наличии циклических ссылок.
Найти циклическую ссылку можно несколькими способами. Например, чисто визуально формулы и ячейки, участвующие в образовании циклических ссылок в Excel, отмечаются синими стрелками.
Если циклическая ссылка – одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.
Если циклические ссылки есть ещё и на других листах кроме активного, то будет выведено сообщение без указания ячейки.
Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.
На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.
Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После её корректировки или удаления – со второй и т. д.
Итак, используя вышеперечисленные инструменты и приёмы, вы сможете облегчить поиск, исправление и предупреждение ошибок в рабочих книгах Excel.
Успехов!!!
Как исправить в Excel ошибку #ЗНАЧ!
Довольно часто при работе в Excel возникает следующая ситуация: при вычислении большого количества каких-либо значений, в том числе и пустых ячеек в результате на выходе в ячейке получаем #ЗНАЧ!
Обычно это означает, что в какой-то ячейке либо неправильный формат числа, либо есть пробел. Если вычислений немного и они сконцентрированы на одном листе, то ошибочное значение найти не трудно, но если в ячейке высчитываются данные с 10-20 листов, то перебирать их все довольно нудное занятие.
В Exel, как 2003 так и 2007 есть штатный инструмент, позволяющий анализировать правильность вычисления формулы. В Excel 2003 это пункт меню "Сервис - Зависимости формул", а в 2007 это пункт "Формулы".Конечно, можно открыть окно контрольного значения, задать неправильно посчитанную ячейку и вручную сидеть выискивать на каком листе ошибка. А можно выбрать пункт "Показать этапы вычисления" нажав на восклицательный знак рядом с ячейкой, либо через меню "Вычислить формулу"
После нажатия этого пункта появляется окошко с введенной формулой, где ее можно внимательно рассмотреть для поиска ошибки.
Для этого нужно нажимать кнопку "Вычислить", а в окне "Вычисление" будут отображаться введенные значения, вычисляемое значение и следующая ячейка.
Когда приложение найдет на каком этапе появилось ошибочное значение, то об этом будет написано в нижней части окна.
Главное запомнить или заметить на какой ячейке отметилась ошибка. Но если не заметили, то можно нажать кнопку "Заново" и вновь отследить всю цепочку формулы.
Если в ячейке есть пробел, то он отметиться как " " (пустые кавычки). Все, что остается, так это удалить содержимое ячейки, либо поправить значения.
Почему Эксель не считает формулу: 5 решений проблемы
Одной из наиболее востребованных возможностей Excel является работа с формулами. Благодаря данной функции программа самостоятельно производит различного рода расчеты в таблицах. Но иногда случается так, что пользователь вписывает формулу в ячейку, но она не выполняет своего прямого назначения – вычисления результата. Давайте разберемся, с чем это может быть связано, и как решить данную проблему.
Устранение проблем с вычислением
Причины проблем с вычислением формул в Экселе могут быть совершенно разными. Они могут быть обусловлены, как настройками конкретной книги или даже отдельного диапазона ячеек, так и различными ошибками в синтаксисе.
Способ 1: изменение формата ячеек
Одной из самых распространенных причин, по которой Эксель вообще не считает или не правильно считает формулы, является неверно выставленный формат ячеек. Если диапазон имеет текстовый формат, то расчет выражений в нем вообще не производится, то есть, они отображаются как обычный текст. В других случаях, если формат не соответствует сути рассчитываемых данных, результат, выводимый в ячейку, может отображаться некорректно. Давайте выясним, как решить эту проблему.
- Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.
- Для того, чтобы произвести смену формата достаточно кликнуть по данному полю. Откроется список выбора форматирования, где можно выбрать значение, соответствующее сути формулы.
- Но выбор типов формата через ленту не такой обширный, как через специализированное окно. Поэтому лучше применить второй вариант форматирования. Выделяем целевой диапазон. Кликаем по нему правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек». Можно также после выделения диапазона нажать комбинацию клавиш Ctrl+1.
- Открывается окно форматирования. Переходим во вкладку «Число». В блоке «Числовые форматы» выбираем тот формат, который нам нужен. Кроме того, в правой части окна имеется возможность выбрать тип представления конкретного формата. После того, как выбор произведен, кликаем по кнопке «OK», размещенной внизу.
- Выделите поочередно ячейки, в которых функция не считалась, и для пересчета нажмите функциональную клавишу F2.
Теперь будет производиться расчет формулы в стандартном порядке с выводом результата в указанную ячейку.
Способ 2: отключение режима «Показать формулы»
Но возможно причина того, что вместо результатов расчета у вас отображаются выражения, состоит в том, что в программе включен режим «Показать формулы».
- Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.
- После этих действий в ячейках снова вместо синтаксиса функций начнет отображаться результат.
Способ 3: исправление ошибки в синтаксисе
Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».
В таких случаях нужно внимательно пересмотреть синтаксис тех формул, которые неправильно отображаются, и внести в них соответствующие коррективы.
Способ 4: включение пересчета формулы
Бывает и такая ситуация, что формула вроде и отображает значение, но при изменении связанных с ней ячеек сама не меняется, то есть, результат не пересчитывается. Это означает, что у вас неправильно настроены параметры вычислений в данной книге.
- Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».
- Откроется окно параметров. Нужно перейти в раздел «Формулы». В блоке настроек «Параметры вычислений», который расположен в самом верху окна, если в параметре «Вычисления в книге», переключатель не установлен в позицию «Автоматически», то это и есть причина того, что результат вычислений неактуальный. Переставляем переключатель в нужную позицию. После выполнения вышеуказанных настроек для их сохранения в нижней части окна жмем на кнопку «OK».
Теперь все выражения в данной книге будут автоматически пересчитываться при изменении любого связанного значения.
Способ 5: ошибка в формуле
Если же программа все-таки производит расчет, но в результате показывает ошибку, то тут вероятна ситуация, что пользователь просто ошибся при вводе выражения. Ошибочными формулами считаются те, при расчете которых в ячейке появляются следующие значения:
- #ЧИСЛО!;
- #ЗНАЧ!;
- #ПУСТО!;
- #ДЕЛ/0!;
- #Н/Д.
В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие (например, деление на 0).
Если функция сложная, с большим количеством связанных ячеек, то легче проследить вычисления с помощью специального инструмента.
- Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».
- Открывается окно, в котором представляется полный расчет. Нажимаем на кнопку «Вычислить» и просматриваем вычисление пошагово. Ищем ошибку и устраняем её.
Как видим, причины того, что Эксель не считает или не правильно считает формулы, могут быть совершенно различными. Если вместо расчета у пользователя отображается сама функция, тот в этом случае, скорее всего, либо ячейка отформатирована под текст, либо включен режим просмотра выражений. Также, возможна ошибка в синтаксисе (например, наличие пробела перед знаком «=»). В случае если после изменения данных в связанных ячейках результат не обновляется, то тут нужно посмотреть, как настроено автообновление в параметрах книги. Также, нередко вместо корректного результата в ячейке отображается ошибка. Тут нужно просмотреть все значения, на которые ссылается функция. В случае обнаружения ошибки следует устранить её.
Мы рады, что смогли помочь Вам в решении проблемы.Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТFixing Error #####
Microsoft Excel может отображать ошибку ##### в ячейках, если ширина столбца недостаточна для отображения всего содержимого ячеек. Формулы, которые возвращают даты и время как отрицательные значения, также могут показывать ошибку ##### .
Чтобы увеличить ширину столбца, чтобы отображалось все содержимое ячейки, дважды щелкните правую границу заголовка столбца или перетащите ее до нужной ширины.
Вы также можете сделать следующее:
-
Чтобы уменьшить содержимое ячейки, нажмите Инструменты > рядом с Выравнивание, , а затем установите флажок Уменьшить по размеру в диалоговом окне Формат ячеек.
-
Если в числах слишком много знаков после запятой, нажмите Главная > Уменьшить десятичную дробь .
-
Если даты слишком длинные, щелкните вкладку Главная > стрелку рядом с Числовой формат и выберите Короткая дата .
Исправление отрицательных значений даты или времени
Если Excel отображает ошибку ##### из-за отрицательного значения даты или времени в ячейке, выполните следующие действия:
-
Если вы используете систему дат 1900 года, убедитесь, что даты и время являются положительными значениями.
-
Используйте формулу для правильного вычитания дат или сложения или вычитания времени, чтобы избежать отрицательных результатов для значения даты или времени.
-
Измените формат отрицательных результатов формулы, отформатированных как даты или время, на другой числовой формат.
Длинные числа неправильно отображаются в Excel — Office
- Статья
- Время считывания: 2 мин
-
- Крышки:
- Excel 2010, Microsoft Office Excel 2007, Microsoft Office Excel 2003
Оцените свои впечатления
да Нет
Хотите что-нибудь добавить к этому мнению?
Отзыв будет отправлен в Microsoft: когда вы нажмете «Отправить», отзыв будет использован для улучшения продуктов и услуг Microsoft.Политика конфиденциальности.
Представлять на рассмотрение
Спасибо.
В этой статье
Примечание
Имя службы Office 365 ProPlus было изменено на Корпоративные приложения Microsoft 365 . Для получения дополнительной информации об этом изменении, пожалуйста, прочитайте этот пост в блоге.
Симптомы
При вводе длинного числа (например, номера кредитной карты) в ячейку Excel это число отображается неправильно в Excel.Например:
Примечание
Числовой формат по умолчанию в Excel — Общий, поэтому ячейка может отображать до 11 цифр.
Обходной путь для
Чтобы обойти эту проблему, используйте один из следующих методов:
Метод 1. Отформатируйте ячейку как текст
Для этого выполните следующие действия.
-
Щелкните правой кнопкой мыши целевую ячейку и выберите Формат ячеек .
-
На вкладке Номер щелкните Текст , а затем щелкните OK .
-
Затем введите длинное число. (Перед вводом номера следует установить формат ячейки)
-
Если вы не хотите видеть предупреждающие стрелки, щелкните маленькую стрелку, а затем щелкните Игнорировать ошибку .
Метод 2: использование одинарных кавычек
После ввода длинного числа сначала введите в ячейку одинарную кавычку ( '), а затем введите длинное число.
Например, вы вводите '1234567890123456789 , и при нажатии клавиши ВВОД кавычки отображаться не будут.
Статьи по теме:
Форматировать числа как текст в Excel для Mac
Нам важно ваше мнение! Пожалуйста, не стесняйтесь сообщить нам, что вы думаете об этой статье, используя поле для комментариев в нижней части документа. Это позволит нам улучшить содержание. Заранее спасибо!
.как исправить ошибку #ND в ВПР
Проблема несоответствия данных
Мы часто сталкиваемся с ситуацией, когда ошибки не позволяют нам выполнять вычисления в последовательных ячейках Excel.
Это часто бывает при использовании VLOOKUP . Эта функция создает связь между двумя таблицами с помощью общего столбца. Подтягивает значения в ячейки, связывая данные друг с другом.
В приведенном ниже примере мы видим, что в ячейки столбца C (Таблица 1) введен оборот за 2017 год из соседней таблицы (Таблица 2) после номера НПВ.Столбец с NIP (идентификационный номер налогоплательщика) — это столбец, с которым связаны данные. Используется функция ВПР.
То, что он делает в ячейке C2 (первая ячейка с формулой), где мы хотим получить ротацию 2017 года из таблицы 2, можно описать следующим образом: = ВПР (A2; $ E $ 1: $ F $ 20 ;2 ;0) - Поиск значения в ячейке А2, в первом столбце диапазона $E$1:$F$20, если вы найдете это значение (это ИНН. 6960428266), то из таблицы $E$1:$F$20 получить данные из второго столбца таблицы (т.е. оборот 2017)
К сожалению, не все номера НПВ можно найти в таблице 2 и поэтому есть информация о несоответствии данных в виде ошибка #ND .
Это можно исправить, используя функцию ЕСЛИОШИБКА в сочетании с ВПР.
Как легко исправить эту ошибку?
Решение проблемы
- Создав вертикальный поиск, вырежьте его из первой ячейки (С2) в строке формул.
- Вызов мастера функций и выбор функции ЕСЛИ ОШИБКА
- В первом аргументе (Значение) вставьте весь поиск по вертикали
- Во втором аргументе (значение при ошибке - т.е. ЧТО ДЕЛАТЬ, ЕСЛИ НАЙДУ.ВЕРТИКАЛЬНО ВЕРНУТЬ ОШИБКУ) введите значение 0
Суммируя всю операцию в одном предложении, можно сказать. Если функция ВПР возвращает ошибку, введите 0. Для чисел) для текстовых значений «».
Проблема решена
Копируем формулу вниз и все работает нормально
Проблема с ошибкой формулы
Предполагая, что вы планируете использовать формулу: = ВПР (A2, $E $ 1: $F $ 20,0,0) , вы можете совершить очень распространенную ошибку.Распространенная проблема заключается в том, что в диапазоне Table Array отсутствуют знаки доллара, поэтому формула может выглядеть так: = ВПР (A2, E1: F20,0,0) .
Возникнет проблема при копировании формулы вниз. Отсутствие абсолютных адресов приведет к ошибкам в ячейках C2 вплоть до
. .Дата Excel - операции над датами Excel - функция даты для анализа данных в Excel
СОДЕРЖАНИЕ
Как Excel видит даты?
Для Excel дата представляет собой целое число, точнее количество дней, прошедших с 0 января 1900 года. Это, конечно, вымышленная дата, и она соответствует числу 0. 1 января 1900 года — первая дата, которую распознает рабочий лист. Ему соответствует цифра 1, т.е. порядковый номер даты (количество дней, прошедших с даты 0).Последняя дата, поддерживаемая в Excel, — 31 декабря 9999 года. Если, например, ввести в ячейку число 5 и использовать формат даты, дата будет отображаться как 05.01.1900, поскольку с 0 января 1990 года прошло 5 дней.
Если вы хотите правильно ввести дату в ячейку, это можно сделать двумя способами:
• Введите порядковый номер даты, если он вам известен, затем отформатируйте его как дату. Здесь пригодится сочетание клавиш, которое соответствующим образом форматирует данные: CTR+SHIFT+# (чтобы перевернуть эту запись, то есть отформатировать дату в число, используйте сочетание клавиш CTR+SHIFT+~)
Серийный номер Excel
• Вручную введите дату в ячейку.
Вероятно, вы чаще используете второй метод, поэтому, вероятно, заметили, что он вызывает больше проблем
Введите дату
Давайте сначала вручную введем даты в ячейки. Не каждая запись, которую пользователь считает датой, будет датой для Excel. Основой здесь является знание того, какая запись будет распознана как дата. Обратите внимание, по какой стороне выровнен введенный в ячейку текст — если по правой, Excel распознал текст как дату.Однако при дальнейшем выравнивании содержимого ячейки по левому краю Excel распознает только текст, а не дату.
Дата в Excel для левого и правого
Ниже приведен список наиболее часто используемых способов записи даты, распознаваемой электронной таблицей:
• 09/02/2016
• 09/02/16
• 2016-2-9
• 2/2/ 9
• 16/02 / 09
• 9 февраля 2016 г.
• 2016-февраль-09
• 16-2-9
• 09-февраль
• 9 февраля 16
• 9 февраля 2016 г.
• 16 / 2- 9.
Запись даты, не содержащая год, например 09 февраля, интерпретируется как текущий год. Электронная таблица распознает даты интуитивно, но она не эффективна на 100%. Даты, не поддерживаемые Excel, такие как даты ниже 1 января 1900 г. и выше 31 декабря 9999 г., интерпретируются как текст. Подробнее об этой проблеме можно прочитать в статье Финики - проблемы. Следующая запись также не будет считаться датой:
• 09-02-2016
• 9 февраля 2016 г.
• 09/16 февраля.
Место даты
Наверняка, как и большинство, вы привыкли сохранять дату в формате дд.мм.гггг. Однако Excel не поддерживает этот формат. Для него это будет только текст. Если вы действительно хотите, чтобы электронная таблица распознавала вашу запись как дату, вы можете изменить региональные настройки даты. Для этого зайдите в Панель управления, выберите раздел Часы, язык и регион, а затем Сменить клавиатуру или другие способы ввода (в зависимости от системы).
Региональные настройки - дата в excel
На вкладке «Форматы» выберите «Дополнительные настройки», затем вкладку «Дата».В разделе Форматы даты вы можете ввести любой формат, используя обозначение, указанное ниже в поле. Этот формат теперь распознается Excel как дата и является допустимым.
Форматы даты
Написание от 00 до 29 распознается как годы от 2000 года и выше, а запись от 30 до 99 как годы ниже 2000 года. Вы также можете изменить эту интерпретацию в окне «Настройка формата» в разделе «Календарь».
.КАК: найти определенные данные с помощью функции HLOOKUP в Excel
Excel HLOOKUP Функция горизонтального поиска может помочь вам найти конкретную информацию в больших таблицах данных, таких как инвентарные списки или большие списки контактов участников.
ГПР работает так же в функции Excel ВПР . Единственное отличие состоит в том, что ВПР ищет данные в столбцах, а ГПР ищет данные в строках.
01 из 07Активация функции ГПР
Начните с добавления образца данных, показанного на изображении выше, в ячейки с D4 по I5 из рабочего листа. Также рекомендуется добавить заголовки на рабочий лист, чтобы показать, какие данные извлекаются HLOOKUP . В этом руководстве введите следующие заголовки в файл указанной ячейки .
Функция HLOOKUP и данные, извлеченные из базы данных, будут находиться в ячейках справа от этих записей:
- D1: Частичное имя 90 033 E1: Цена 90 036 фунтов стерлингов
Создать шаблон
- Щелкните ячейку E2 , чтобы стать активной ячейкой - здесь мы запустим HLOOKUP для работы.
- Перейдите на вкладку Формула .
- Выберите Search and Reference на ленте, чтобы открыть раскрывающийся список.
- Щелкните HLOOKUP в списке, чтобы вызвать мастер формул .
Данные, которые мы вводим в четыре пустые строки диалогового окна, сделают аргументы HLOOKUP функцией. Эти аргументы говорят вам, какую функцию мы ищем и где ее искать.
02 из 07Значение поиска
Первый аргумент: Inbox_value ; говорит HLOOKUP какой элемент в базе данных мы ищем. Lookup_value e находится в первой строке выбранного диапазона.
Информация, которую возвращает HLOOKUP , всегда поступает из того же столбца базы данных, что и Inbox_value . InboxValue может быть текстовой строкой, логическим значением (только true или false), числом или ссылкой на ячейку значения.
- Нажмите на строку Inbox_value .
- Щелкните ячейку D2 , чтобы добавить ссылку на эту ячейку в строку Box_Value — это ячейка, в которую мы вводим название части, о которой мы ищем информацию.
Стол стола
Аргумент Table_array представляет собой диапазон данных, в котором функция HLOOKUP ищет информацию. Table_array должен содержать не менее двух строк данных в первой строке, которая содержит Inbox_value .
Диапазон массива массивов не обязательно должен включать все строки или даже первую строку базы данных.
- Нажмите на строку Table_array .
- Выделите ячеек с E4 по I5 в электронной таблице, чтобы добавить этот диапазон в строку Table_array .
Порядковый номер строки
Аргумент номера строки индекса ( Row_index_num ) указывает, какая строка Table_array содержит искомые данные. Вот несколько примеров различных данных, введенных для аргумента:
- Если вы введете 1 в порядковый номер строки, HLOOKUP вернет значение в первом столбце в table_array .
- Если номер индекса строки равен 2 , возвращает значение из второй строки в table_array .
- Щелкните строку Row_index_num .
- Введите 2 в эту строку, чтобы указать, что мы хотим, чтобы HLOOKUP возвращал информацию из второй строки массива таблиц.
Поиск диапазона
Диапазон поиска Аргумент является логическим ( ИСТИНА или ЛОЖЬ только ), который указывает, хотите ли вы HLOOKUP найти точное или приблизительное совпадение для значения Lookup_.
- Если TRUE или если этот аргумент опущен: HLOOKUP будет использовать приблизительное совпадение, если не может найти точное совпадение с BoxValue . Если точное совпадение не найдено, HLOOKUP возвращает следующее наибольшее значение, которое меньше Inbox_value .
- Если FALSE : HLOOKUP будет использовать только точное совпадение Inbox_value .Если в первом столбце Table_array есть два или более значения, которые соответствуют InboxValue , используется первое найденное значение.
Если точное совпадение не найдено и # Н/Д , возвращается ошибка.
- Щелкните строку Диапазон поиска .
- Введите слово False в эту строку, чтобы указать, что мы хотим, чтобы HLOOKUP возвращал точное соответствие искомым данным.
- Нажмите Готово.
- Если вы выполнили все шаги этого руководства, у вас уже должна быть завершена функция HLOOKUP в ячейке E2 .
Использование HLOOKUP для загрузки данных
После завершения функции HLOOKUP ее можно использовать для извлечения информации из базы данных. Для этого введите имя элемента, который вы хотите получить, в команду lookup_value и нажмите клавишу ENTER на клавиатуре .
HLOOKUP использует номер индекса строки, чтобы определить, где в данных отображать ячейку E2 .
- Щелкните ячейку E1 в электронной таблице.
- Введите Widgets в ячейку E1 и нажмите клавишу ENTER на клавиатуре.
- Цена виджетов теперь должна отображаться в ячейке E2 .
Тест HLOOKUP продолжается путем ввода других названий деталей в ячейку E1 и сравнения данных, возвращенных в ячейке E2 , с ценами, указанными в ячейках E5–I5 .
07 из 07Excel
Сообщения об ошибках HLOOKUP
При работе в Excel не всегда все работает так, как планировалось. Следующие сообщения об ошибках относятся к HLOOKUP и могут помочь вам решить любые проблемы, с которыми вы столкнулись.
# Ошибка Н/Д:
- Эта ошибка отображается, если искомое значение не найдено в первом столбце таблицы массива.
- Также отображается, если диапазон аргумента массива неточен.
#ССЫЛКА!:
- Эта ошибка отображается, если аргумент номера индекса строки больше, чем количество строк в массиве таблиц.
🥇 Как устранить ошибки ВПР в Excel
У вас возникли проблемы с функцией ВПР в Microsoft Excel? Вот несколько советов по устранению неполадок, которые могут вам помочь.
Есть несколько вещей, которые беспокоят пользователей Microsoft Excel больше, чем мысль об ошибке ВПР. Если вы новичок в Excel, ВПР — одна из самых сложных или, по крайней мере, одна из наименее понятных функций.
Цель ВПР — найти и вернуть данные из другого столбца электронной таблицы Excel. К сожалению, если вы получите неправильную формулу ВПР, Excel выдаст вам ошибку. Давайте рассмотрим некоторые распространенные ошибки ВПР и объясним, как их исправить.
ВПР ограничения
Прежде чем использовать функцию ВПР, обратите внимание, что это не всегда лучший вариант для пользователей Excel.
Во-первых, его нельзя использовать для поиска данных с левой стороны. Он также показывает только первое найденное значение, что означает, что функция ВПР не подходит для областей данных с повторяющимися значениями. Столбец поиска также должен находиться в левой части диапазона данных.
В следующем примере в качестве столбца подстановки используется самый длинный столбец (столбец A ). В области видимости нет повторяющихся значений, а данные поиска (в данном случае данные из столбца B ) находятся в правой части столбца поиска.
Функции ИНДЕКС и ПОИСКПОЗ были бы хорошими вариантами, если какая-либо из этих проблем является проблемой, как и новая функция XLOOKUP в Excel, которая в настоящее время находится в стадии бета-тестирования.
VLOOKUP также требует, чтобы данные были расположены в строках для правильного поиска и возврата данных. HLOOKUP был бы хорошим решением, если бы это было не так.
Существуют некоторые дополнительные ограничения формул ВПР, которые могут вызывать ошибки, которые мы объясним позже.
ВПР и № ошибки Н/Д
Одной из наиболее распространенных ошибок функции ВПР в Excel является # Неприменимо неправильно. Эта ошибка возникает, когда ВПР не находит искомое значение.
Во-первых, ваше искомое значение может быть не в вашей области данных, или вы могли использовать неправильное значение. Если вы не видите ошибки, дважды проверьте значение в формуле ВПР.
Если значение верное, искомое значение не существует.Предполагается, что вы используете функцию ВПР для поиска точных совпадений с аргументом Диапазон поиска , установленным на ИМИТАЦИЯ .
В приведенном выше примере вы ищете один идентификатор учащегося из номеров 104 (в ячейке G4 ) возвращает одну ошибку № неприменимо , поскольку наименьший идентификационный номер в диапазоне равен 105 .
Если Диапазон поиска отсутствует аргумент в конце формулы ПРОСМОТР.VLOOKUP или установлено значение TRUE , то функция VLOOKUP возвращает ошибку #Н/Д, если область данных не отсортирована по возрастанию.
Также вернет ошибку # Н/Д, если искомое значение меньше наименьшего значения в диапазоне.
В приведенном выше примере студенческих билетов значений перемешаны. Несмотря на значение 105 , существующее в ассортименте, ВПР не может выполнить допустимый поиск с аргументом Диапазон поиска , установленным на ИСТИНА из столбца A , не отсортированы в порядке возрастания.
Другими распространенными причинами ошибок # N / A являются использование столбца поиска, который не является самым левым, и использование ссылок на ячейки для значений поиска, которые содержат числа, но отформатированы как текст или содержат лишние символы в виде пробелов.
Устранение ошибок #ЗНАЧ
Ошибка File # VALUE обычно является признаком того, что формула, содержащая функцию ВПР, некорректна.
В большинстве случаев это обычно зависит от ячейки, на которую вы ссылаетесь как на значение поиска.Файл наибольший размер Значение поиска с функцией ВПР: 255 символов .
Если вы имеете дело с ячейками, содержащими более длинные строки, функция ВПР не сможет их обработать.
Единственным решением является замена ВПР комбинированной формулой ИНДЕКС и ПОИСКПОЗ. Например, если столбец содержит ячейку со строкой длиннее 255 символов, для поиска этих данных можно использовать вложенную функцию ПОИСКПОЗ.ПУНКТ в ИНДЕКСЕ.
В следующем примере ИНДЕКС возвращает значение в B4 с использованием диапазона и в столбце A для определения правильной строки. Использует вложенную функцию MATCH для идентификации строки в столбце A (которая содержит 300 символов), которая соответствует ячейке h5 .
В данном случае это ячейка A4 , средний ИНДЕКС возврат 108 (значение B4 ).
90 130
Эта ошибка также появляется, если в формуле используется недопустимая ссылка на ячейку, особенно если вы используете область данных из другой книги.
Ссылки на рабочую книгу должны быть заключены в круглые скобки, чтобы формула работала правильно.
Если вы столкнулись с ошибкой #ARG, дважды проверьте формулу ВПР, чтобы убедиться, что ссылки на ячейки действительны.
#ИМЯ и ВПР
Если ошибка ПРОСМОТРАВЕРТИКАЛЬНО не является ошибкой #ЗНАЧ или ошибкой # Н/Д, это, вероятно, ошибка #ИМЯ неправильно. Прежде чем паниковать, просто подумав об этом, вы можете быть уверены - это самая простая ошибка ВПР для исправления.
Ошибка #ИМЯ возникает при неправильном вводе функции в Excel, будь то функция ВПР или какая-либо другая функция, например СУММ. Нажмите на свою ячейку ВПР и убедитесь, что вы правильно ввели ПРОСМОТР.ПЕРПЕНДИКУЛЯРНО.
Если других проблем нет, функция ВПР будет работать после устранения ошибки.
Использовать другие функции Excel
Это смелое заявление, но такие функции, как ВПР, изменят вашу жизнь. По крайней мере, это изменит вашу профессиональную жизнь, сделав Excel более мощным инструментом анализа данных.
Если вас не устраивает функция ВПР, используйте вместо нее эти лучшие функции Excel.
.
Как автоматически пронумеровать строки в Excel?
Знаете ли вы, что ваша электронная таблица Excel может содержать до 1 048 576 строк? Верно. Теперь представьте, что вы вручную присваиваете номера каждой из этих строк. Это, несомненно, задача, которая может быть разочаровывающей и отнимающей много времени. Во-первых, вы можете делать ошибки и повторять числа, что может усложнить анализ данных и привести к ошибкам в расчетах. И нет ничего более постыдного, чем представить плохо организованный или полный ошибок документ.
Это может заставить вас выглядеть неподготовленным и непрофессиональным.
Хорошо, что Excel может сделать всю тяжелую работу за вас. Вы можете настроить автоматическое заполнение номеров строк и ускорить работу над документом.
Узнайте, как автоматически нумеровать строки в Excel.
Как автоматически нумеровать строки в Excel?
Чтобы создать идеальную электронную таблицу или книгу Excel, вам необходимо правильно вводить данные.Вы хотите, чтобы каждая строка имела уникальный идентификатор , который помогает вам извлекать определенные строки для анализа или представления вашей аудитории.
Нумерация строк — лучший способ создания уникальных идентификаторов.
Если ваш документ не слишком большой, вы можете вручную присвоить числа несколькими нажатиями на клавиатуре. Но это не вариант, если на вашем листе сотни или тысячи строк. Ручная нумерация каждой строки приведет к пустой трате драгоценного времени, которое можно было бы потратить на обработку большего количества элементов технического листа.
К счастью, в Excel есть несколько инструментов, предназначенных для автоматического присвоения номеров. Это может помочь вам сэкономить время и создать хорошо организованный документ, который будет не только точным, но и приятным для глаз.
Давайте посмотрим, как работает каждый из этих инструментов.
Использование маркера заполнения
Маркер заполнения Excel, также известный как маркер заполнения, представляет собой небольшую кнопку со стрелкой, которая появляется в нижнем левом углу активной ячейки.Автозаполнение диапазона ячеек в столбце на основе информации из предыдущих строк.
Дескриптор заполнения работает, идентифицируя шаблон и следуя ему.
Вот как автоматически нумеровать строки в Excel с помощью маркера заполнения:
- Откройте рабочий лист Excel.
- Введите первое значение (1) в первую ячейку нужного столбца.
- Введите второе значение (2) в ячейку сразу под первым.
- Выберите обе ячейки.
- Нажмите и удерживайте ручку заполнения в правом нижнем углу нижней ячейки.
- Аккуратно перетащите маркер вниз, пока не будут выбраны все строки, которым вы хотите присвоить номера.
- Когда вы дойдете до последней строки, отпустите кнопку мыши.
После выполнения этих действий Excel заполнит все ячейки в выбранном столбце порядковыми номерами — от 1 до любого числа.
Использование функции ROW
Функцию маркера заполнения и серии выполнить просто, но они терпят неудачу в одной важной области: автоматическое обновление чисел при добавлении строк на лист или даже при удалении некоторых.
Например, если вы вставите новую строку между строками 3 и 4, новая строка не будет пронумерована. Вам придется форматировать весь столбец и выполнять любую команду заново.
Войдите в функцию ROW и проблема исчезнет!
С помощью функции линии вы можете назначать номера, которые автоматически обновляются при удалении некоторых строк или вставке новых строк.
Вот как использовать эту функцию:
- Щелкните первую ячейку, где начнется автонумерация.
- Введите в ячейку следующую формулу:
= РЯД (A2) - 1
Убедитесь, что линия ссылки заменена соответствующим образом. Мы предположили, что нашей контрольной строкой является A2, но это может быть любая другая строка в вашем файле. В зависимости от того, где вы хотите, чтобы отображались номера строк, это может быть A3, B2 или даже C5.
Если первой нумеруемой ячейкой является A3, формула изменится на = ROW(A3) - 2 . Если это C5, вам следует использовать формулу = ROW (C5) - 4 - . После того, как выбранной ячейке был присвоен номер, наведите указатель мыши на маркер перетаскивания в левом нижнем углу и перетащите его вниз к последней ячейке. в сериале.
Как автоматически нумеровать строки в Excel без перетаскивания
Перетаскивание маркера заполнения вниз до тех пор, пока не будут выбраны все строки, которым вы хотите присвоить номера, идеально подходит для небольших файлов Excel, содержащих всего несколько строк.Если в файле сотни или тысячи строк, перетаскивание может быть немного утомительным и занимать много времени.
К счастью, Excel позволяет автоматически нумеровать строки без использования кнопки перетаскивания: заполнение ряда.
Функция заполнения ряда Excel используется для генерации последовательных значений в указанном диапазоне ячеек. В отличие от функции маркера заполнения, эта функция дает вам гораздо больше контроля. Это дает возможность указать первое значение (которое не обязательно должно быть 1 ), значение шага, а также конечное значение (стоп).
Например, предположим, что начальные, ступенчатые и конечные значения равны соответственно 1, 1 и 10. В этом случае функция Fill Series автоматически заполнит 10 строк в выбранном столбце, начиная с 1 в первой ячейке, 2 во второй ячейке, по 10 в последней ячейке.
Вот как можно автоматически заполнять номера строк в Excel с помощью Fill Series:
- Выберите первую ячейку, которой вы хотите присвоить номер.
- Введите первое значение, например 10, в первую ячейку.
- Нажмите Home в верхней части листа.
- Щелкните Заполнить , а затем выберите Серия в раскрывающемся меню. Это должно открыть плавающий диалог в центре листа.
- В диалоговом окне выберите «Столбцы» в разделе «Серии в».
- В этот момент введите значение шага ( 1 по умолчанию), а затем введите значение остановки в соответствующих местах.
- Нажмите здесь Хорошо
Готово! Все ячейки в выбранном столбце теперь будут иметь уникальные и последовательные серийные номера для легкой идентификации.
Как автоматически пронумеровать отфильтрованные строки в Excel?
Фильтр — это функция, которая позволяет просеивать (или вырезать) данные на основе критериев. Это позволит вам выбрать определенные части рабочего листа и показать только эти ячейки в Excel.
Например, если у вас много повторяющихся данных, вы можете легко отфильтровать все эти строки и оставить только то, что вам нужно.В любой момент времени на экране будут отображаться только неотфильтрованные строки.
При представлении ваших данных фильтрация позволяет вам поделиться тем, что нужно вашей аудитории, не предоставляя слишком много информации. Эта ситуация может запутать и усложнить анализ данных.
Даже после фильтрации данных вы можете добавить на лист нумерацию строк.
Вот как это сделать.
- Фильтруйте данные.
- Выберите первую ячейку, которой вы хотите присвоить номер, а затем введите следующую формулу:
= ПРОМЕЖУТОЧНЫЙ ИТОГ (3, $ B $ 2: B2)
Первый аргумент, 3, указывает Excel на подсчет чисел. В диапазоне.
Второй аргумент, $ B $ 2: B2, — это просто диапазон ячеек, которые вы хотите подсчитать. - Возьмите маркер заполнения (+) в правом нижнем углу ячейки и потяните его вниз, чтобы заполнить все другие ячейки в указанном диапазоне.
Будь организованным
Excel — полезный инструмент для управления данными и выполнения всех видов вычислений. Но это не всегда облегчает жизнь. Одной из задач, которая может занять много времени и разочаровать, является присвоение номеров строкам.
К счастью, есть несколько инструментов, которые помогут вам автоматически назначать номера. Это может быть надежным способом создания хорошо организованного и легко читаемого файла.
Пробовали ли вы выполнять какие-либо функции нумерации Excel, описанные в этой статье? Это сработало?
Дайте нам знать в разделе комментариев ниже.
.
Видео-курс
