Окно контрольного значения excel что это
Второй способ обнаружения ошибок – Excel отображает в левом верхнем углу ячейки зелёный треугольник (индикатор ошибки). При выборе такой ячейки появляется смарт-тег проверки ошибок.
Для проверки ошибок необходимо выполнить следующие шаги:
1. Выберите лист, который требуется проверить на наличие ошибок.
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Проверка наличия ошибок. Откроется окно диалога Контроль ошибок.
3. В окне диалога Контроль ошибок просмотрите информацию о текущей ошибке в левой части окна.
4. Для просмотра более детального описания ошибки и возможных вариантов её исправления нажмите кнопку Справка по этой ошибке.
a) нажмите кнопку Вычислить, чтобы проверить значение подчёркнутой ссылки. Результат вычислений показан курсивом;
c) Выполняйте указанные действия, пока не будет вычислена каждая часть формулы;
d) Чтобы снова увидеть вычисления, нажмите кнопку Заново;
e) Чтобы завершить вычисления, нажмите кнопку Закрыть.
6. Для изменения формулы в строке формул нажмите кнопку Изменить в строке формул.
7. Если ошибка является несущественной, вы можете нажать кнопку Пропустить ошибку. Помеченная ошибка при последующих проверках будет пропускаться.
9. Доведите до конца проверку ошибок и закройте окно диалога Контроль ошибок.
Для удобства поиска причин ошибок, а также и в некоторых других случаях можно графически отобразить связь между ячейками в формуле.
Для отображения ячеек, входящих в формулу в качестве аргументов, необходимо выделить ячейку с формулой и нажать кнопку Влияющие ячейки в группе Зависимости формул вкладки Формулы.
Один щелчок по кнопке Влияющие ячейки отображает связи с ячейками, непосредственно определяющими результат вычисления. Если в этих ячейках также находятся формулы, то следующий щелчок отображает связи с ячейками, являющимися аргументами этих формул. И так далее.
Для отображения ячеек, в формулы которых входит какая-либо ячейка, её следует выделить и нажать кнопку Зависимые ячейки в группе Зависимости формул вкладки Формулы.
Один щелчок по кнопке Зависимые ячейки отображает связи с ячейками, непосредственно зависящими от выделенной ячейки. Если эти ячейки также влияют на другие ячейки, то следующий щелчок отображает связи с зависимыми ячейками. И так далее.
Связи в пределах текущего листа отображаются синими стрелками. Связи с ячейками других листов и книг отображаются чёрными пунктирными линиями и значком листа. Красные стрелки показывают ячейки, вызывающие ошибки.
Для скрытия стрелок связей следует нажать кнопку Убрать все стрелки в группе Зависимости формул вкладки Формулы. Использование окна контрольных значений.
Если вы работаете с большой таблицей, содержащей формулы, которые ссылаются на различные ячейки текущей таблицы и/или ячейки другого листа, достаточно сложно отследить их взаимосвязи.
В этом случае вашим помощником может выступать панель инструментов Окно контрольного значения.
Окно контрольного значения значительно упрощает анализ, проверку зависимостей и подтверждение вычислений и результатов формул на больших листах. Благодаря окну контрольного значения не требуется постоянно прокручивать лист, когда вы работаете с большой таблицей, переходить к различным его частям.
Добавление ячеек в окно контрольных значений
Для добавления на панель контрольных значений выполните следующие действия:
1. Выделите ячейки, контрольные значения которых нужно поместить на панель.
Чтобы выделить все ячейки листа с формулами, на вкладке Главная в группе Правка нажмите кнопку Найти и выделить и выберите команду Формулы.
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.
4. В окне диалога Добавление контрольного значения проверьте правильность выбранного диапазона и нажмите кнопку Добавить.
Циклические ссылки возникают, когда формула в какой-либо ячейке посредством других ячеек ссылается сама на себя.
Например, ячейка С4 = Е7, Е7 = С11, С11 = С4. В итоге С4 ссылается на С4.
Появление циклических ссылок очень легко определить. При их возникновении или наличии в уже созданной книге Excel сразу же появляется предупреждение о циклической ссылке, которое и описывает суть явления.
Предупреждение, как правило, появляется при первоначальном создании циклической ссылки или открытии книги, содержащей циклические ссылки. Если предупреждение принято, то при дальнейшем возникновении циклических ссылок оно может не появляться.
Циклические ссылки в Excel могут создаваться преднамеренно, для решения тех или иных задач финансового моделирования, а могут возникать случайно, в виде технических ошибок и ошибок в логике построения модели.
В первом случае мы знаем об их наличии, так как сами их предварительно создали, и знаем, зачем они нам нужны.
Найти циклическую ссылку можно несколькими способами. Например, чисто визуально формулы и ячейки, участвующие в образовании циклических ссылок в Excel, отмечаются синими стрелками.
Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.
На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.
Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После её корректировки или удаления – со второй и т. д.
Итак, используя вышеперечисленные инструменты и приёмы, вы сможете облегчить поиск, исправление и предупреждение ошибок в рабочих книгах Excel.
Когда вы работаете с большой моделью, бывает целесообразно отслеживать только значения из наиболее важных ячеек по мере того, как в другие вводятся данные. Эта задача упрощается благодаря элементу Окно контрольного значения. С его помощью можно отслеживать любое количество ячеек независимо от того, какие книга или лист активны в настоящий момент. Эта функция позволяет сэкономить время, избавившись от прокрутки и переключения между вкладками листов и окнами книг. [1]
Рис. 1. Использование окна контрольного значения для отслеживания значений формул с ячейками
Для того чтобы открыть окно контрольного значения, пройдите по меню Формулы –> Зависимости формул –> Окно контрольного значения. Чтобы отслеживать значение ячейки, нажмите в этом окне кнопку Добавить контрольное значение. Откроется диалоговое окно Добавление контрольного значения, в котором следует указать интересующую вас ячейку. В этом окне можно задать сразу несколько ячеек, выделив диапазон либо щелкая на отдельных ячейках, удерживая при этом нажатой клавишу Ctrl (рис. 1).
Для каждой ячейки в окне контрольного значения отображаются названия книги, листа и ячейки (при его наличии), адрес ячейки, актуальное значение и формула (при ее наличии). Excel сохраняет информацию о ячейках в окне контрольного значения даже между сессиями. Если вы закроете книгу, в которой есть ячейки, отслеживаемые в окне контрольного значения, ячейки будут удалены из окна. Однако, как только вы снова откроете эту книгу, ячейки появятся там опять.
Окно контрольного значения является областью задач. Внешний вид этого окна можно настроить, выполнив одно из следующих действий:
- Щелкните на границе окна и потяните ее с помощью мыши, чтобы изменить размер области задач.
- Если перетащить область задач к границе окна с книгой Excel, то оно фиксируется в этом месте, а не остается незакрепленным.
- Можно щелкать на границах заголовков и перетаскивать их, чтобы менять ширину отображаемых столбцов. Если до отказа потянуть границу столбца влево, этот столбец будет скрыт.
- Можно щелкнуть на одном из заголовков, чтобы отсортировать содержимое окна по этому столбцу.
Окно контрольного значения может помочь и при навигации. Если при работе вам придется часто переключаться между различными рабочими листами, добавьте по ячейке с каждого из этих листов. Чтобы активизировать нужную ячейку, просто дважды щелкните на ней кнопкой мыши в окне контрольного значения. К сожалению, в Excel 2013 такая возможность навигации предоставляется только в активной книге. Иными словами, если вы дважды щелкнете в окне контрольного значения на ячейке, относящейся к какой-то другой книге, эта книга не станет активна. Не знаю, сделано это намеренно или является багом Excel 2013.
[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 101, 102.
В данной статье рассмотрим функцию MS Excel, которая называется ЕСЛИ. Данная функция позволяет проверить, соответствует ли содержащиеся в указанной ячейки данные заданному критерию или нет. В зависимости от результата проверки функция выводит заданное значение. Чтобы понять суть и возможности функции ЕСЛИ, рассмотрим примеры с ее использованием.
Функция ЕСЛИ в MS Excel с одним условием.
Функцией ЕСЛИ в Excel, первый пример.
Рассмотрим простой пример применения функции ЕСЛИ в MS Excel. У нас есть столбец №1, в котором содержаться числа от 1 до 10.
Необходимо проверить, соответствует ли числа находящиеся в столбце №1 нашему критерию. Критерий: является ли числа находящиеся в столбце №1 числами, значения которых больше числа 4.
Взываем функцию ЕСЛИ в ячейку С3.
В диалоговом окне Аргументы функции видим три поля для заполнения:
Нажимаем ОК в диалоговом окне Аргументы функции.
Как видим, число содержащиеся в ячейки В3 не является числом, которое больше числа 4. Функция ЕСЛИ вернула значение_если_ложь (неправда). Протянем функцию ЕСЛИ вниз по столбцу, чтобы проверить значения находящиеся в остальных ячейках столбца №1, на соответствие нашему критерию (логическое_выражение).
Как видно из результата, ячейки в диапазоне В7:В12 содержат в себе числа, которые больше числа 4. Функция ЕСЛИ вернула значение_если_истина (правда).
Функция ЕСЛИ в Excel, второй пример.
Рассмотрим еще один пример. Предположим, что у нас есть таблица. В ней фамилии студентов, которые сдавали зачет. Часть студентов сдали зачет, а часть не сдали.
Теперь добавим в нашу таблицу столбец: Допуск к экзамену. Используем функцию ЕСЛИ для того, чтобы определить допущен студент к экзамену или нет. Диалоговое окно Аргументы функции будет выглядеть вот так:
Важно: текстовый критерий в поле логическое_выражение должен быть заключен в кавычки. Иначе функция выдаст ошибку.
Если студен сдал зачет, он допущен к экзамену, если нет, то не допущен. Нажимаем ОК в диалоговом окне Аргументы функции и протягиваем функцию ЕСЛИ вниз, по всему столбцу Допуск к экзамену. Получаем:
Видим, что в зависимости от результатов сдачи зачета, студент допущен к экзамену, либо не допущен.
Обращаю внимание, что при работе с функцией ЕСЛИ, можно использовать разные варианты равенств: больше (>), меньше(<), больше или равно (>=), меньше или равно (<=), равно (=), не равно (<>).
Функция ЕСЛИ в MS Excel с несколькими условиями.
Предположим, что у нас есть таблица с результатами сдачи контрольной работы студентами ВУЗа. Результаты контрольной работы оцениваются в баллах. От 0 до 100. Где все, что выше 90 баллов, отлично. Выше 80 баллов, это хорошо. Выше 70 баллов, это удовлетворительно. Ниже 70 баллов, это плохо.
Формула функции ЕСЛИ будет выглядеть вот так:
Где ячейка С3, первая из проверяемых ячеек в столбце Оценка. Далее функция протянута вниз по столбцу Результат, до конца таблицы.
Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.
Таблица выглядит вот так:
В столбце Результат, в зависимости от количества баллов, функция ЕСЛИ вернула значение, согласно заданному критерию в формуле функции.
Использование операторов «И» и «ИЛИ» в функции ЕСЛИ в MS Excel.
Формула функции ЕСЛИ выглядит вот так:
Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.
Формула функции ЕСЛИ выглядит вот так:
Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.
Как правильно организовать в Excel проверку введенных данных
Гораздо хуже, когда внешне все выглядит нормально, но при этом в данных есть ошибки. В таких случаях пользователь даже не догадываться о наличии ошибок и формирует недостоверные отчеты. А в результате принимаются ошибочные управленческие решения. Что же делать?
НЕДОСТАТКИ СТАНДАРТНЫХ ИНСТРУМЕНТОВ EXCEL
Как вы наверняка знаете, в Excel существует стандартный функционал для контроля вводимых в ячейки данных. Он устанавливается через меню Данные -> Проверка данных . В результате появляется диалоговое окно Проверка вводимых значений c тремя закладками.
На закладке Параметры в поле Тип данных можно выбрать тип данных. В зависимости от введенного типа становятся доступными для заполнения другие поля, например:
- для типа Число , Дата , Время можно задать границы – от и до и проверять на соблюдение условий между, меньше, больше и т.д.;
- для типа Список можно задать список доступных значений;
- для типа Другой можно задать свою собственную формулу, которая будет проверять вводимое значение на соблюдение каких либо условий, в том числе в зависимости от значений в других ячейках.
Внешне сервис проверки данных кажется очень эффективным. Особенно удобно выбирать значение из выпадающего списка, а не вводить его вручную. Тем не менее стандартный функционал Excel позволяет решить проблему контроля вводимых данных лишь частично и у него есть существенные недостатки:
1. Стандартные средства контроля введенных данных могут создать у пользователя вредную иллюзию защищенности от ошибок. Особенно если данные вводили не вы, а кто-то другой, и вы рассчитываете что там все нормально, система отловила ошибки. В едь проверка работает только при ручном вводе данных, но не работает при внесении данных другими способами!
Например, если данные вводятся в ячейку с использованием команд Копировать->Вставить, Копировать->Специальная вставка-Значения или путем «растаскивания» от соседних ячеек, то проверка данных не осуществляется. Также значения в ячейках не контролируются, если они введены в ячейку с помощью макроса (если только контроль не осуществляется в самом макросе).
Таким образом, неверные данные могут попасть в ячейки, многократно тиражированы и Excel это не отследит!
Часто это бывает, когда вы выгружаете данные в таблицы из других программ, например, 1С. Но о собенно неприятно когда ошибки незаметны или малозаметны. Например:
- Значение в ячейке выглядит как число и дата, а на самом деле оно является текстом. Например потому, что перед или после цифр есть пробел или другие непечатные (невидимые) символы.
- Ячейка выглядит пустой, а на самом деле там есть непечатные символы.
- В качестве разделителя целой и дробной части использована точка вместо запятой.
- Введено значение, которое отсутствует в "привязанном" к ячейке выпадающем списке.
Последняя ситуация достаточно распространена. Например, вы ведете реестр платежей от контрагентов, и вводите наименование контрагентов с помощью выпадающего списка, связанного со справочником контрагентов. Спустя некоторое время вы обнаруживаете в наименовании контрагента ошибку и исправляете ее в справочнике. При этом Excel никак не отреагирует на то, что в реестре есть записи со старым (ошибочным) наименованием контрагента. То есть Excel при изменении элементов списка не отлавливает уже введенные данные типа «Список».
2. Стандартные способы проверки могут "тормозить"
Достаточно распространенной проверкой является проверка для типа данных Другой с использованием формул. Это может быть проверка на непревышение планового бюджета по статьям расходов, проверка на уникальность элемента списка и т.п.
В этом случае часто используются «тяжелые» функции СЧЕТЕСЛИ, СЧЕТЕСЛИМН, которые подсчитывают количество или сумму по уже введенным ранее данным и сравнивают, не превысит ли вводимое значение какой-то предел. Всякий раз, когда вы вводите или изменяете значение в ячейке, отрабатывает эта "тяжелая" формула. Если при этом с ячейкой связано много других ячеек, будут пересчитываться и они. Когда вы это делаете и тестируете в таблицах мало данных и задержек не ощущается.
Но по мере роста таблиц Excel может начать «залипать» при вводе данных, иначе говоря не успевать за руками пользователя. И это очень раздражает. Поэтому, использовать проверку вводимых данных с использованием типа Другой и «тяжелыми» формулами нужно очень обдуманно. Я советую вообще исключить использование подобных приемов.
3. Бесполезный инструмент "Обвести неверные данные"
Для проверки правильности введенных значений в Excel есть встроенный инструмент «Обвести неверные данные» , который вызывается через пункты меню - Данные -> Проверка данных -> Обвести неверные данные.
Читайте также: