Таблица эксель как найти ошибку между файлами

Функция ЕСЛИОШИБКА

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Функцию ЕСЛИERROR можно использовать для перебора и обработки ошибок в формуле. Если же формула возвращает значение, определяемую формулой, возвращается ошибка; в противном случае возвращается результат формулы.

Синтаксис

ЕСЛИОШИБКА(значение;значение_если_ошибка)

Аргументы функции ЕСЛИОШИБКА описаны ниже.

• значение Обязательный аргумент. Проверяемая на ошибку аргумент.
• value_if_error — обязательный аргумент. Значение, возвращаемая, если формула возвращает ошибку. Вычисляются следующие типы ошибок: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?или #NULL!.

Замечания

• Если значение или value_if_error пустая ячейка, то если ЕСЛИЕROR рассматривает его как пустую строковую строку («»).
• Если значение является формулой массива, то функции ЕСЛИERROR возвращают массив результатов для каждой ячейки в диапазоне, указанном в значении. См. второй пример ниже.

Примеры

Скопируйте данные из таблицы ниже и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.

Единиц продано

=ЕСЛИОШИБКА(A2/B2;»Ошибка при вычислении»)

Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле

=ЕСЛИОШИБКА(A3/B3;»Ошибка при вычислении»)

Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке»

Ошибка при вычислении

=ЕСЛИОШИБКА(A4/B4;»Ошибка при вычислении»)

Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле.

Пример 2

Единиц продано

Ошибка при вычислении

Выполняет проверку на предмет ошибки в формуле в первом аргументе в первом элементе массива (A2/B2 или деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле

Выполняет проверку на предмет ошибки в формуле в первом аргументе во втором элементе массива (A3/B3 или деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке»

Ошибка при вычислении

Выполняет проверку на предмет ошибки в формуле в первом аргументе в третьем элементе массива (A4/B4 или деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле

Примечание. Если у вас есть текущая версия Microsoft 365 ,вы можете ввести формулу в левую верхнюю ячейку диапазона выходных данных, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей. Для этого сначала выберем диапазон вывода, введите формулу в левую верхнюю ячейку диапазона, а затем нажмите CTRL+SHIFT+ВВОД, чтобы подтвердить ее. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Таблица эксель как найти ошибку между файлами

Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически

Вопрос от пользователя

Здравствуйте!

У меня есть одна задачка, и уже третий день ломаю голову — не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут — скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация) .

Заранее благодарю. Андрей, Москва.

Доброго дня всем!

То, что вы описали — относится к довольно популярным задачам, которые относительно просто и быстро решать с помощью Excel. Достаточно загнать в программу две ваши таблицы, и воспользоваться функцией ВПР . О ее работе ниже.

Пример работы с функцией ВПР

В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая.

Две таблицы в Excel — сравниваем первые столбцы

Как это сделать.

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

=ВПР( A2 ; $E$1:$F$7 ; 2 ; ЛОЖЬ )

где:

A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца — то значение можно было бы копировать из 2-го или 3-го столбца);

ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!

Значения из одной таблицы подставлены в другую

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

Сравнение 2 файлов в Excel на предмет различий

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

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

Таких действий множество. Давайте рассмотрим некоторые из них.

С какой целью проводится сравнение файлов Excel

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

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

Таких ситуаций может быть огромное количество. Но давайте перейдем к практике, потому что тема довольно сложная.

Все способы сравнения 2 таблиц в Excel

Хотя тема сложная, но она легкая. Да, не стоит удивляться. Сложная она, потому что складывается из множества частей. Но сами эти части легкие для восприятия и выполнения. Давайте рассмотрим, как можно сравнивать две экселевские таблицы, непосредственно на практике.

Формула равенства и проверка на ложь-истину

Начнем, конечно, с наиболее простого метода. Таким методом сравнение документов возможно, причем в довольно широких пределах. Можно сопоставлять между собой не только текстовые значения, но и числовые. И давайте приведем небольшой пример. Допустим, у нас есть два диапазона с ячейками числового формата. Для этого достаточно просто написать формулу равенства =C2=E2. Если окажется, что они равны, в ячейке будет написано «ИСТИНА». Если же они будут отличаться, то «ЛОЖЬ». После этого нужно данную формулу перенести на весь диапазон с помощью маркера автозаполнения.

Теперь разница видна невооруженным глазом.

Выделение различающихся значений

Также можно сделать так, чтобы значения, которые отличаются между собой, были выделены особенным цветом. Это тоже довольно простая задача. Если вам достаточно найти отличия между двумя диапазонами значений или целыми таблицами, необходимо перейти на вкладку «Главная», и там выбрать пункт «Найти и выделить». Перед тем, как вы ее нажмете, не забудьте выделить набор ячеек, в которых сохраняется информация для сравнения.

В появившемся меню необходимо кликнуть по меню «Выделить группу ячеек…». Далее откроется окошко, в котором нам необходимо в качестве критерия выбрать отличия по строкам.

Сравнение 2 таблиц при помощи условного форматирования

Условное форматирование является очень удобным и, что важно, функциональным методом, позволяющим выбрать цвет, которым будет выделяться отличающееся или такое же значение. Найти этот параметр можно на вкладке «Главная». Там можно найти кнопку с соответствующим названием и в появившемся перечне выбираем «Управление правилами». Появится диспетчер правил, в котором нам надо выбрать меню «Создать правило».

Далее из перечня критериев нам нужно выбрать тот, где написано, что нужно использовать формулу для определения ячеек, которые будут отформатированы особенным образом. В описании правила нужно задать формулу. В нашем случае это =$C2<>$E2, после чего подтверждаем свои действия нажатием кнопки «Формат». После этого задаем внешний вид ячейки и смотрим, нравится ли он, через специальное мини-окошко с образцом.

Если все устраивает, нажимаем кнопку «ОК» и подтверждаем действия.

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

Функция СЧЁТЕСЛИ + правила для сравнения таблиц

Все методы, которые мы описали ранее, удобны для тех формат, формат которых един. Если же предварительно таблицы не были упорядоченными, то лучше всего метод, в котором необходимо сравнивать две таблицы с помощью функции СЧЕТЕСЛИ и правил.

Давайте вообразим, что у нас есть два диапазона с немного различающейся информацией. Перед нами стоит задача сравнивать их и понять, какое значение отличается. Для начала необходимо осуществить его выделение в первом диапазоне и перейти на вкладку «Главная». Там находим уже знакомый нам ранее пункт «Условное форматирование». Создаем правило и в качестве правила задаем использовать формулу.

В этом примере формула такая, как показана на этом скриншоте.

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

Функция ВПР для сравнения 2 таблиц

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

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

Функция ЕСЛИ

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

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

С помощью формулы, которая использует функции ЕСЛИ, ЕСЛИОШИБКА и ПОИСКПОЗ можно перебрать все нужные элементы колонки А, а потом в колонке B. Если оно было обнаружено в колонке B и A, то оно возвращается в соответствующую ячейку.

Макрос VBA

Макрос – это самый сложный, но и наиболее продвинутый метод сравнения двух таблиц. некоторые варианты сравнения вообще невозможны без сценариев VBA. Они позволяют автоматизировать процесс и сэкономить время. Все нужные операции для подготовки данных, если их запрограммировать один раз, будут выполняться и далее.

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

Как провести сравнение файлов в Эксель

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

1. Откройте те файлы, сравнение которых требуется.
2. Откройте вкладку «Вид» – «Окно» – «Вид рядом».

После этого два файла будут открыты одном документе Excel.

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

Условное форматирование для сравнения 2 файлов Эксель

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

Сперва нам необходимо перенести сравниваемые листы в один документ.

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

Далее необходимо выделить все нужные ячейки, чтобы отобразить все различия. Проще всего это сделать, нажав самую верхнюю левую ячейку, после чего нажать на комбинацию клавиш Ctrl + Shift + End.

После этого переходим в окно условного форматирования и создаем новое правило. В качестве критерия используем подходящую в конкретном случае формулу, потом задаем формат.

Сравнение данных в Эксель на разных листах

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

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

С помощью условного форматирования можно осуществлять эффективное сравнение даже если фамилии работников располагаются в различном порядке.

Как сравнить 2 листа в таблице Эксель

Сравнение информации, располагающейся на двух листах, осуществляется с помощью функции ПОИСКПОЗ. В качестве первого ее параметра располагается пара значений, которую нужно искать на том листе, который отвечает за следующий месяц. Простыми словами, март. Мы можем обозначить просматриваемый диапазон, как совокупность ячеек, которые входят в состав именных диапазонов, объединенных попарно.

Так можно осуществить сравнение строк по двум критериям – фамилия и заработная плата. Ну или любым другим, определенным пользователем. Для всех совпадений, которые удалось найти, записывается в ячейке, в которую вводится формула, число. Для Excel такое значение всегда будет истинным. Поэтому для того, чтобы форматирование применялось по отношению к тем ячейкам, которые были различны, необходимо это значение заменить на ЛОЖЬ, воспользовавшись функцией =НЕ().

Средство сравнения электронных таблиц

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

Открыть его можно непосредственно с вкладки «Главная», выбрав пункт «Сравнить файлы».

После этого появится диалоговое окно, в в котором нужно выбрать вторую версию книги. Также можно ввести адрес в интернете, по которому эта книга расположена.

После того, как мы выберем две версии документа, нужно подтвердить свои действия с помощью клавиши ОК.

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

Средство сравнение выглядит, как две таблицы Excel, расположенные рядом друг с другом в рамках одного окна. В зависимости от того, информация была добавлена, удалена или было изменение формулы (а также других видов действий), изменения выделяются различными цветами.

Как интерпретировать результаты сравнения

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

Функция ВПР в Excel

ВПР (англ. вариант – VLOOKUP) – очень удобная и полезная функция особенно для работы с большим объемом данных, поскольку позволяет автоматически сопоставить диапазоны с десятками тысяч наименований. Является разновидностью поиска, но только вертикального (сверху вниз) – извлекает информацию из таблицы или определенного диапазона по строкам. Работает во всех версиях Excel и даже Google Sheets.

Синтаксис

Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Как работает функция?

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

С одним условием

Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.

1. Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
2. В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.

1. Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.

1. Нажать «Enter» – и появится результат поиска.

Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».

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

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

1. В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.

1. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).

1. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «<>», в которые взята вся формула).

Поиск по нескольким столбцам

Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.

1. Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12; ;ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «<>».

1. Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «<>».

Программа сравнивает данные в таблице и, как только определяет точное совпадение, суммирует их.

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12; ;ЛОЖЬ)).

Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.

Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

1. В таблице с зарплатой за март добавить еще один столбец.

1. Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.

1. При помощи маркера заполнения протянуть полученное значение вниз.

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

Для создания раскрывающегося списка:

1. Поставить курсор в ячейку, где он будет располагаться.
2. Перейти в раздел книги «Данные» – «Проверка данных».

1. В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).

1. Нажать «Ок». Отобразится список.

1. В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

1. Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)

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

Ошибки

Когда пользователь ошибается при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #Н/Д, #ЗНАЧ, #ССЫЛКА.

Ошибка #Н/Д появляется, если:

1. Указанный диапазон не содержит искомый элемент.
2. Искомый элемент меньше, чем минимальный в массиве.
3. Задан точный поиск (аргумент «ЛОЖЬ» или 0), а искомого нет в диапазоне.
4. Задан приблизительный поиск (аргумент «ИСТИНА» или 1), но данные не отсортированы по возрастанию.
5. Разный формат (числовой и текстовый) у ячейки, откуда берется искомое, и ячейки с данными первого столбца.
6. В коде есть пробелы или невидимые непечатаемые знаки.
7. Используются значения времени или большие десятичные числа.

Во избежание ошибки #Н/Д, когда ВПР не находит значение, рекомендуется использовать следующую формулу: =ЕСЛИОШИБКА(ВПР(C2;A1:B12;2;ЛОЖЬ);0) – вместо 0 можно написать «не найдено».

Ошибка #ЗНАЧ появляется, если:

1. В качестве номера столбца указано число 0.
2. Длина первого аргумента превышает 255 знаков.

Ошибка #ССЫЛКА появляется, если третий аргумент больше количества столбцов в таблице.

Вместо заключения

В Excel есть улучшенная версия ВПР – функция ПРОСМОТРХ. Считается более простой и удобной в использовании, работает в любом направлении и возвращает точные совпадения по умолчанию.

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

В программах OpenOffice и LibreOffice используется VLOOKUP, ее синтаксис =VLOOKUP(lookupvalue; datatable; columnindex; mode), а работает функция точно так же, как описано выше.

Похожие публикации:

1. Как удалить яндекс браузер из ubuntu
2. Как узнать версию visual studio
3. Как узнать длину линии в автокаде
4. Как узнать код магнитолы мерседес атего 1222

Exceltip

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

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

Опубликовано 02.07.2013 Автор Ренат Лотфуллин

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

Ошибки в формулах делятся на несколько категорий:

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

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

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

Семантические ошибки: Например, название функции написано неправильно, в этом случае Excel вернет ошибку #ИМЯ?

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

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

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

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

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

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

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

Эта формула возвращает отношение значений колонок C к B. Так как не все данные по дням были занесены, формула вернула ошибку #ДЕЛ/0!

Чтобы избежать ошибки, вы можете воспользоваться формулой ЕСЛИ, для проверки, являются ли ячейки колонки B пустыми или нет:

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

Другим подходом является использование функции ЕСЛИОШИБКА, которая проверяет на наличие ошибки. Следующая формула вернет пустую строку, если выражение C4/B4 будет возвращать ошибку:

Ошибка #Н/Д

Ошибка #Н/Д возникает в случаях, когда ячейка, на которую ссылается формула, содержит #Н/Д.

Обычно, ошибка #Н/Д возвращается в результате работы формул подстановки (ВПР, ГПР, ПОИСКПОЗ и ИНДЕКС). В случае, когда совпадение не было найдено.

Чтобы перехватить ошибку и отобразить пустую ячейку, воспользуйтесь функцией =ЕСНД().

Обратите внимание, что функция ЕСНД является новой функцией в Excel 2013. Для совместимости с предыдущими версиями воспользуйтесь аналогом этой функции:

Ошибка #ИМЯ?

Excel может вернуть ошибку #ИМЯ? в следующих случаях:

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

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

Ошибка #ПУСТО! возникает в случае, когда формула пытается использовать пересечение двух диапазонов, которые фактически не пресекаются. Оператором пересечения в Excel является пробел. Следующая формула вернет #ПУСТО!, так как диапазоны не пересекаются.

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

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

• В числовом аргументе формулы введено нечисловое значение (например, $1,000 вместо 1000)
• В формуле введен недопустимый аргумент (например, =КОРЕНЬ(-12))
• Функция, использующая итерацию, не может рассчитать результат. Примеры функций, использующих итерацию: ВСД(), СТАВКА()
• Формула возвращает значение, которое слишком большое или слишком маленькое. Excel поддерживает значения между -1E-307 и 1E-307.

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

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

• Вы удалили колонку или строку, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если первая строка или столбцы A или B были удалены:

• Вы удалили рабочий лист, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если Лист1 был удален:

• Вы скопировали формулу в расположение, где относительная ссылка становится недействительной. Например, при копировании формулы из ячейки A2 в ячейку A1, формула вернет ошибку #ССЫЛКА!, так как она пытается обратиться к несуществующей ячейке.

• Вы вырезаете ячейку и затем вставляете ее в ячейку, на которую ссылается формула. В этом случае будет возвращена ошибка #ССЫЛКА!

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

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

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

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

• Что если отобразить скрытые строки в Excel не работает
• Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами
• Пятый урок курса по основам Excel — Печать в программе
• Шестой урок онлайн курса по основам Excel — Управление рабочим листом
• Четвертый урок курса по основам Excel — Изменение ячеек
• Третий урок курса по основам Excel — Форматирование рабочих листов
• Второй урок обучающего курса по Excel «Основы Excel»
• Запуск новой рубрики — Школа Excel
• Несколько условий ЕСЛИ в Excel
• Как построить график с нормальным распределением в Excel

Рубрика: Основы | Метки: логические функции, функции | 5 комментариев | Permalink

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

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

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

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

спасибо! так оно и было

Добрый день! Подскажите как решить проблему, у коллеги на компе все получается а у меня нет…. Надо завязать одну таблицу на другую, раньше все получалось самым простым способом, т.е. в нужную ячейку ставлю знак =, перехожу во вторую таблицу, выбираю нежное значение, нажимаю Ентер, и в первой таблице отображается нужное значение с адресом на вторую табл., т.е. при изменении значений во второй табл. все меняется автомат в первой. Так вот теперь у меня след.ситуация: два файла Ексель, в первом выбираю нужную ячейку, нажимаю =, перехожу во второй файл выбираю нужное значение, а оно не активно, т.е. не дает выбрать, ячейка не светится, как должна при копировании…..первый раз такое, может что-то в настройках…. Подскажите, пожалуйста.

Как найти ошибку в таблице Excel по формуле

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

Поиск ошибок в Excel формулой

Чтобы определить местонахождение ошибки в таблице с большим количеством строк и столбцов рекомендуем воспользоваться специальной формулой. Для примера покажем формулу, которая умеет легко работать с большими диапазонами ячеек, в пределах A1:Z100.

Для определения локализации первой ошибки на листе относительно строк следует использовать следующую формулу:

Данная формула должна выполняться в массиве, поэтом после ее ввода для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки, как на рисунке.

Таблица с большим объемом данных содержит ошибки, первая из которых находится в диапазоне третей строки листа 3:3.

Как получить адрес ячейки с ошибкой

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

Данная формула так же должна выполняться в массиве, поэтом после ее ввода снова для подтверждения жмем комбинацию клавиш CTRL+SHIFT+Enter.

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

Принцип действия формулы для поиска ошибок:

В первом аргументе функции АДРЕС указываем номер строки, который должен быть возвращен в адресе ячейки содержащей результат действия целой формулы. Номер строки определен предыдущей формулой и является числом 3. Поэтому мы только ссылаемся на ячейку AB2 с первой формулой. Далее с помощью функции ДВССЫЛ определяется ссылка на диапазон, который должен быть найден в соответствии с местом нахождения ошибок. Нет необходимости выполнять поиск по целой таблице нагружая таким образом процессор компьютера излишне отнимая вычислительные ресурсы программы Excel. Нас интересует только третья строка.

С помощью функции ЕОШИБКА проверяется каждая ячейка в диапазоне A3:Z3 на наличие ошибок. На основании полученных результатов в памяти программы создается массив логических значений ИСТИНА и ЛОЖЬ. Следующая функция СТОЛБЕЦ возвращает в память программы второй массив из номеров столбцов с количеством элементов соответствующему количеству столбцов в диапазоне A3:Z3.

Благодаря функции ЕСЛИ в первом массиве логическое значение ИСТИНА заменяется на соответственное числовое значение из второго массива. После чего функция МИН выбирает наименьшее числовое значение первого массива, которое соответствует номеру столбца содержащего первую ошибку. Так как били вычислены номер строки и столбца завершается вычисление формулы функцией АДРЕС. Она уже возвращает текстовым значением готовый адрес ячейки на основе номера столбца и строки указанных в ее аргументах.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры