Для поиска ошибок Excel предоставляет в распоряжение пользователя вспомогательную функцию, с помощью которой можно графически представить связи между влияющими и зависимыми ячейками.
Отслеживать зависимости удобно с помощью панели инструментов Зависимости (рис. 6.1). Чтобы открыть ее, воспользуйтесь подменюЗависимости менюСервис .
В этом примере проверки зависимых данных, если страна выбрана из первого раскрывающегося списка, в раскрывающемся списке следующего столбца отображаются только города этой страны. Если ни одна страна не выбрана, столбец «Город» отобразит список городов мира в зависимом выпадающем списке.
В этом примере будет книга с двумя листами - лист ввода данных и лист со списками. Вставьте новый лист в книгу и назовите этот лист «Списки». . Выполните следующие шаги, чтобы создать список имен. Эти списки будут содержать элементы для выпадающих списков проверки зависимых данных. В этом примере будет 3 списка.
Рис. 6.1. Панель инструментов Зависимости
Функция отслеживания зависимостей позволяет графически обозначить связи между влияющими и зависимыми ячейками. Рассмотрим простой пример. Пусть в ячейках А1 и А2:А5 содержатся некоторые числа, в ячейке В2 запишем формулу =$B$1*A2, с помощью автозаполнения скопируем эту формулу в ячейки В3:В5. Активизируем ячейку А1 и выберем в меню Сервис командуЗависимости - Зависимые ячейки или щелкнем по кнопке. В таблице появятся линии трассировки со стрелками, исходящими из активной ячейки и указывающими на зависимые ячейки в таблице (рис. 6.2).
Типы продуктов Названия фруктов Названия овощей. . Он будет иметь список категорий продуктов - фрукты и овощи. В главном списке будут элементы с одним словом. Позже в этих словах будут использоваться названия диапазонов. Чтобы создать именованную таблицу для своего списка, выполните следующие действия. Это делает ваш список динамическим, поэтому новые элементы автоматически появятся в ваших списках.
Затем выполните следующие действия, чтобы создать именованный диапазон. Вы будете использовать это имя позже при создании выпадающего списка. Выберите элементы в списке, но не заголовок.
. Затем следуйте инструкциям, чтобы создать отдельную таблицу для каждого типа продукта - один со списком имен фруктов и один со списком растительных имен.
Рис. 6.2. Линии трассировки, показывающие зависимые от А1 ячейки
Также отображаются линии трассировки для влияющих ячеек (при нажатии кнопки ). Кнопкиипредназначены для того, чтобы убрать линии трассировки.
Если ячейка содержит значение ошибки, то с помощью функции трассировки можно быстро обнаружить ее источник.
В столбце «Тип продукта» будет указан простой столбец «Элемент списка» будет использовать формулу для отображения элементов для выбранного типа продукта. Во-первых, вы войдете в заголовки в листе ввода данных и настройте именованную таблицу. Затем вы создадите раскрывающийся список в столбце «Тип продукта».
Создание зависимого выпадающего списка
Затем вы создадите зависимый выпадающий список в столбце «Элемент». Эта ячейка будет иметь формулу для создания раскрывающегося списка проверки данных. Примечание. Чтобы снова проверить зависимый выпадающий список, выполните следующие действия. Если вам нужно, вы можете добавить еще один набор списков выпадающих списков зависимых данных, которые зависят от выбора в первых двух выпадающих списках. Например, выберите страну и регион, затем выберите город в этом регионе.
Использование функций
Excel предоставляет в распоряжение пользователей множество специальных функций, которые можно применять в вычислениях. К особенностям Excel следует отнести широкий набор математических, статистических функций и методов оптимизации. Использование функций позволяет эффективно обрабатывать фрагменты текста, решать самые разнообразные задачи с использованием дат и времени, создавать произвольные условные операции, основанные на логических функциях.
Использование элементов с нелегальными символами
Например, ваш выбор - «Красный фрукт», «Зеленый фрукт» и «Желтый фрукт». Создайте поддерживаемые именованные списки, используя имена одного слова, например. Для второго раскрывающегося списка выберите «Разрешить: список» и используйте формулу, которая удаляет пробелы из имен. Создайте первый именованный диапазон и раскрывающийся список, как описано выше.
. Затем вы создадите таблицу поиска, чтобы соответствовать каждому имени с зависимыми элементами.
Аргументы функции указываются после ее названия в круглых скобках. Скобки вводятся с клавиатуры. Адрес диапазона ячеек, содержимое которых должно использоваться в качестве аргументов функции, вставляется в результате выделения диапазона с помощью мыши.
На формулы с функциями не накладываются ограничения, как на другие формулы. Их можно копировать обычным образом, учитывая тип ссылки (относительная или абсолютная). Допускается использование в качестве аргументов ссылок на диапазоны из других листов и книг.
Идентификация зависимых ячеек
Чтобы создать 3-й зависимый выпадающий список, используйте те же методы, чтобы создавать таблицы поиска и списки элементов. Списки элементов с именами кодов и «Список» добавлены на рабочий лист. В окне проверки данных используйте эту формулу для 3-го падения.
Блокировка изменений при первом выпадении
Используйте один из следующих решений, когда требуется динамический список. Чтобы заблокировать изменения в первом списке, вы можете изменить формулу проверки данных, поэтому список не отображается, если вторая ячейка пуста. Это видео показывает вам, как работает эта техника, и находится ниже видео.
Функция может служить аргументом другой функции. Такие функции называются вложенными. Рассмотрим, как применять вложенные функции.
При этом будет использоваться таблица с данными о температуре воздуха в некоторых городах мира за одну неделю.
Создайте таблицу, изображенную на рис. 6.3.
Определим минимальную температуру за неделю в различных городах, а также максимальное из минимальных значений.
При зависимых выпадающих списках могут возникнуть проблемы, если кто-то вернется в первый список и изменит его. Чтобы заблокировать изменения в первом списке, вы можете изменить формулу проверки данных, поэтому список не работает, если вторая ячейка пуста.
Очистить зависимую ячейку после выбора
Поскольку в этом имени нет диапазона, результатом будет ошибка, и выпадение не будет работать. Теперь падение в первом столбце не будет работать, если элемент был выбран в этой строке. В предыдущем разделе формула проверки данных была разработана для предотвращения выбора из первого раскрывающегося списка, если ячейка справа содержит данные.
Рис. 6.3. Пример использования вложенных функций
Для этого введем в ячейку В12 формулу =МИН (В4:В10). Скопируем эту формулу в ячейки С12 - Е12. В результате применения функции, определяющей минимальное значение, мы установим минимальную температуру в разных городах. Чтобы определить максимальное из минимальных значений, введем в ячейку В13 формулу:
Добавить код события, чтобы очистить клиентскую ячейку
Другой вариант - использовать макрос, чтобы очистить зависимую ячейку, после выбора в первом выпадающем списке. Это предотвратит несоответствие выбора. Вернитесь назад и выберите другой регион, и существующее имя клиента будет очищено. Когда вы выбираете регион, код события очищает ячейку вправо.
Вставьте следующий код в модуль листа. Возможно, вам потребуется изменить номер столбца, чтобы он соответствовал номеру столбца на вашем листе. Частный вспомогательный рабочий лист_Изменить по ошибке Продолжить. С таблицами вы можете иметь столько, сколько хотите на одном листе. В этом случае вы можете иметь записи проверки данных в серии таблиц с одним столбцом на листе, а не иметь их как отдельные столбцы в одной таблице.
МАКС (В12:Е12).
Полностью решить данную задачу можно и с помощью одной-единственной формулы (которую нужно вставить в ячейку В13):
МАКС (МИН (В4:В10); МИН (С4:С10); МИН (D4:D10); МИН (Е4:Е10)).
Формируя вложенные функции, следует учитывать, что первой вычисляется функция во внутренних скобках.
Существует множество задач (например, округление значений), решать которые на много легче, используя вложенные функции. Для округления чисел в Excel 97 предназначена функция ОКРУГЛ, синтаксис которой несколько отличается от синтаксиса уже знакомых нам функций СУММ, МИН и МАКС. Аргументами функции ОКРУГЛ являются число или ссылка и количество десятичных разрядов результата.
Во второй иллюстрации каждый столбец представляет собой таблицу в своем собственном праве. Теперь большое преимущество наличия одной таблицы столбцов - это количество строк в каждой таблице независимо, тогда как каждый столбец объединенной таблицы должен иметь одинаковую длину, так что в некоторых столбцах есть пустые записи.
Когда у нас есть список проверки данных, мы хотим, чтобы он отображал только список допустимых записей и не отображал никаких пустых строк. В одиночных таблицах столбцов мы уже обсуждали тот факт, что каждый из них будет иметь свои собственные размеры, но переменное количество строк.
В процессе работы с функциями следует использовать панель формул. В этом случае аргументы устанавливаются в диалоговом окне, что исключает появление ошибок, вероятность возникновения которых имеет место при вводе функции с клавиатуры.
В случае ошибочного ввода функции (например, пользователь забыл указать обязательный аргумент, количество закрывающих скобок не соответствует количеству открывающих и т.д.) появляется сообщение об ошибке.
Но есть еще одно большое преимущество, и было ли это по дизайну, или просто «причудой» таблиц, и, если вы назовете таблицу таким же, как текст в ячейке заголовка в столбце таблиц, вы можете обратиться к этому напрямую и вернуть диапазон ячеек, принадлежащих этому имени.
Поэтому использование одной таблицы столбцов отвлекает нас от одного уровня использования косвенного, позволяет нам напрямую использовать текст заголовка столбца для обозначения диапазона и дает нам динамический диапазон, который будет включать только действительные записи без каких-либо пустых ячеек.
Иногда мы все ошибаемся, в том числе и при использовании формул в MS Excel. Однако не всегда наши ошибки являются именно ошибками, то есть приводят и рекомендаций о том, как справится с возникшей проблемой. Гораздо хуже, когда никакого предупреждения об ошибках не выводится, а мы явно видим, что результат совершенно не соответствует реальности.
Влияющие и зависимые ячейки в MS Excel
Давайте посмотрим, как использовать эти таблицы в валидации данных - особенно зависимая проверка данных. Сначала давайте рассмотрим случай, когда наш ввод данных находится на листе в обычном списке. Теперь мы должны использовать функцию «Непрямая», а не создавать наш список проверки данных, поскольку мы уже достигли этого, как показано выше, но чтобы предоставить способ наличия списка проверки данных, который воспитывается в любой ячейке, зависит от предыдущего записи в этой строке.
Исправление ошибок возникающих в MS Excel
В других ячейках всегда будет использоваться функция «Непрямая», принимающая в качестве аргумента значение, которое уже было введено в предыдущей ячейке в этой строке. Если ваша проверка данных находится на другой части листа, а не начинается в первом столбце или в первой строке, просто убедитесь, что вы устанавливаете эту относительную косвенную формулу, чтобы всегда ссылаться на ячейку сразу под вашим первым заголовком.
К счастью, в наших руках несколько отличных инструментов для поиска «хитрых» ошибок в формулах MS Excel.
Влияющие и зависимые ячейки в MS Excel
Обычно в итоговых строках таблицы выводится некая сводка данных расположенных в других местах таблицы, а то и результат каких-то промежуточных вычислений. Если таких вычислений довольно много, то ошибиться в одном из них становится проще простого. Впрочем, исправить такую ошибку тоже очень просто… но только при условии, что вы знаете где искать.
Даже если вы используете таблицу для ввода данных, потому что для работы этого метода необходимо использовать косвенный, для работы с структурированными ссылками мало смысла, и вы можете использовать ту же формулу с вашей таблицей. Таким образом, в случае наличия пробелов в любом из ваших данных списка, например.
Поэтому лучше изменить нашу формулу валидации. Если у вас уже есть лист с большим количеством данных, уже введенных в стандартную форму, и вы не хотите вручную создавать каждую из таблиц одного столбца, которые вам нужны для этого метода, тогда код, как показано ниже, создаст все таблицы для вы и убедитесь, что любые пробелы удалены при настройке имен таблиц и записей заголовков в первой строке созданных таблиц.
Именно с этой точки зрения все ячейки в MS Excel разделяются на влияющие и зависимые . Различить и запомнить их просто:
- Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б — это данные влияющие на результат вычисления формулы).
- Зависимые — содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).
Зная какие ячейки влияют на результат и от чего он зависит, мы без труда найдем причину проблемы.
Этот код предполагает, что единственное, что на вашем листе - это записи, которые вы хотите использовать для проверки данных. Ячейки = _ Рабочий лист. Замена диапазона тем, что является ячейкой для первой строки и столбца данных, которые вы хотите преобразовать.
Очевидно, что значения, следующие за Америкой, будут неверными, если только пользователь не пойдет и не внесет каждое из изменений. Поскольку в таблице могут быть дополнительные данные, которые являются числовыми данными и не должны меняться, даже если предыдущие значения изменяются, нам нужно сообщить код, как далеко он должен очистить данные.
Для иллюстрации я подготовил простейшую табличку с данными. В ней есть два условных показателя и коэффициент, а итоговый расчет осуществляется простой плюсовкой обоих показателей с последующим умножение на результат: (Показатель 1 + Показатель 2) х Коэффициент .
Дополнительно я создал ещё одну простую формулу: она умножает наш «Итог» на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6 .
Прецеденты трассировки для диапазона ячеек со вспомогательной формулой
Приведенный ниже код был скопирован на листе ввода данных. В дополнение к очистке записей, готовых к выбору новых значений, код проверяет, было ли введено новое значение в ячейку, и если это так, он выбирает ячейку с одним столбцом справа. Чтобы отслеживать прецеденты для нескольких ячеек, следующие шаги могут помочь вам.
Рекомендуемые инструменты производительности
Снимок экрана. Наконец, вы можете удалить формулу помощника по мере необходимости. Выберите диапазон данных, в котором вы хотите отслеживать прецеденты или иждивенцы. Эта функция динамична, она будет изменена как ваш выбор. Скачать и бесплатно пробную версию! Сегодня мы подробно изучим эту функцию и научимся создавать каскадные выпадающие списки, отображающие выбор в зависимости от значения, выбранного в первом раскрывающемся списке.
Давайте перейдем на вкладку «Формулы» и в группе «Зависимости формул» посмотрим на два крайне полезных в работе инструмента: «Влияющие ячейки» и «Зависимые ячейки».
Определяем влияющие ячейки в Excel. Влияющие они естественно на вычисления происходящие в данной ячейке
Выделяю результирующую ячейку «Итог» и нажимаю кнопку «Влияющие ячейки» . Оп, и на листе MS Excel появляются синие стрелки ведущие от трех используемых в вычислениях ячеек к итоговой формуле. Согласитесь, нагляднее представить себе понятие «влияющая ячейка» невозможно.
А теперь зависимые ячейки. Весь лист теперь как на ладони
Теперь нажимаю (не убирая курсор с ячейки «итоги») кнопку «Зависимые ячейки» и на экране появляется ещё одна стрелка. Она ведет к ячейке «результат с поправкой», то есть той, результат вычислений в которой зависит от текущей.
Теперь я намеренно «порчу» таблицу, внося в исходные данные ошибку — подставляя букву вместо цифры. Мгновение, и я уже точно знаю откуда эта ошибка взялась. Мне даже искать ничего не пришлось — все вполне наглядно и графически красиво.
Ошибка возникшая из-за замены цифры на букву. Excel подсветил «ошибочное» вычисление красной стрелкой
Отключить графику можно в любой момент нажав на кнопку «Убрать стрелки» .
Исправление ошибок возникающих в MS Excel
Впрочем, не всегда ошибка представляется нам так явно, иногда нужно ещё понять какое из промежуточных вычислений приводит к сбою. Есть в MS Excel отличный помощник и для этого.
Снова выделим наш пресловутый «итог» и щелкнем мышью по кнопке «Вычислить формулу» . Открывшееся окно содержит в себе поле в котором записана «проблемная» формула. Пока ничего не обычного, верно? Нажимаем на кнопку «Вычислить» внизу и обращаем внимание как формула в окне начнет меняться — вместо идентификаторов ячеек в ней появятся цифры из соответствующих ячеек.
А вот и ошибка — как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей
Одно нажатие на кнопку — один шаг в вычислениях. Уже на третьем шаге мы наглядно увидим в какой именно части формулы происходят вычисления порождающие ошибку, и теперь без труда сможем их исправить.
Вот и всё. Пользуйтесь этими несложными методами, и без труда «расщелкаете» любую возникшую при вычисления в MS Excel ошибку.
Также вас может заинтересовать.
