При вложении одной формулы в другую легко допустить ошибку. Избежать этого поможет средство Excel, позволяющее проследить зависимость значений в одних ячейках от формул и значений, находящихся в других ячейках.
Для определения зависимостей поместите табличный курсор в рассматриваемую ячейку и вызовите команду Сервис/Зависимости/Зависимые ячейки или Влияющие ячейки (рис. 6.25). После этого между зависимыми ячейками появятся стрелки. Они показывают непосредственное влияние содержимого одних ячеек на формирование результата в других ячейках.
По умолчанию ячейки, которые «противоречат» правилам, отмечены зеленым индикатором. Одной из наиболее полезных функций проверки данных является возможность создания раскрывающегося списка, который позволяет пользователям выбирать значение из предопределенного списка. Выпадающие списки позволяют пользователям вводить только данные, соответствующие вашим требованиям.
Выпадающие списки легко создавать и использовать. Но как только вы начнете использовать выпадающие меню в своих электронных таблицах, вы неизбежно столкнетесь с проблемой: как вы можете сделать значения в одном раскрывающемся списке зависимыми от значений в другом? Другими словами, как вы можете создать раскрывающийся список динамических?
Рис. 6.25.
При выполнении команды Влияющие ячейки стрелки зависимостей показывают на ячейки, значения которых влияют на данную ячейку.
Если же вы воспользуетесь командой Зависимые ячейки , то стрелки будут указывать на ячейки, значения которых зависят от данной ячейки.
В случае, когда нужно проследить большое число зависимостей, удобно применить панель Зависимости (рис. 6.26).
Список городов, которые зависят от выбранной страны, список вкусов, которые зависят от типа мороженого, список моделей, которые зависят от производителя, список продуктов, которые зависят от категории. Эти списки называются зависимыми выпадающими списками, поскольку список зависит от другого значения.
Если пользователь выбирает «Фрукты», они видят список фруктов, если они выбирают «Орех», они видят список орехов, и если они выбирают «Овощ», они видят список овощей. Если рабочий лист содержит следующие именованные диапазоны. На первый взгляд вы можете обнаружить, что эта конструкция раздражает или даже бессмысленна.
Рис. 6.26.
На этой панели расположены пять нужных нам кнопок: Влияющие ячейки, Убрать стрелки к влияющим ячейкам, Зависимые ячейки, Убрать стрелки к зависимым ячейкам и Убрать все стрелки (первые пять кнопок слева).
На рис. 6.27 показаны стрелки, которые появляются при выполнении команды Влияющие ячейки . Они указывают, формулы каких ячеек оказывают непосредственное влияние на формулы, находящиеся в анализируемой ячейке. Например, на вычисления в ячейке ВЗ влияет значение ячейки А1. На ячейку С4 влияют значения в ячейках С3 и В3, на которые, в свою очередь, влияет ячейка А1.
Это дает два основных преимущества. Вы можете собрать текстовую ссылку внутри формулы, которая удобна для определенных видов динамических ссылок. Вы можете получать текстовые значения на листе и использовать их как ссылку на ячейку в формуле. В примере на этой странице мы объединим последнюю идею с именованными диапазонами для создания зависимых выпадающих списков.
Как настроить зависимые выпадающие списки
В этом разделе описывается настройка зависимых выпадающих списков, показанных в примере. В этом примере создайте список фруктов, орехов и овощей на листе. Создайте и протестируйте правило проверки данных, чтобы предоставить раскрывающийся список для категории, используя следующую настраиваемую формулу.
Рис. 6.27.
На рис. 6.28 показаны стрелки, которые появляются при выполнении команды Зависимые ячейки. Они указывают, на формулы каких ячеек оказывают влияние формулы или значения, находящиеся в исходной ячейке. Так, ячейка А1 влияет на вычисления в ячейках A1, B1, C1 и т. д, а ячейка ВЗ влияет на вычисления в ячейках С4 и В4.
Примечание: для того, чтобы быть понятным, именованный диапазон «категория» используется только для удобства чтения и удобства - использование именованного диапазона здесь не требуется. Также обратите внимание, что проверка данных со списком отлично работает как с горизонтальным, так и с вертикальным диапазоном - оба будут представлены как вертикальное раскрывающееся меню.
Примечание: подход, который мы здесь делаем, не чувствителен к регистру. Именованные диапазоны не допускают пробелы, поэтому обычным соглашением является использование символов подчеркивания. Чтобы устранить эту проблему, вы можете использовать более надежную формулу для проверки данных. Пример файла приведен ниже: проверьте его, как это работает. Лучший способ узнать - это сделать. Вы использовали выпадающие списки в ячейках в ваших электронных таблицах?
Словосочетание «зависимые ячейки» употребляется в контексте использования формул на листах exel. По определению это такие ячейки, значение которых используется для вычисления других ячеек. Часто приходится работать с созданным кем-то документом. Его владелец размещает формулы по своей задумке, а у вас в каких-то результирующих ячейках выходит ошибка и вы не можете найти причину. Для этого в приложении exel существует инструмент, называющийся «Зависимости»
Настройка сцены: пример
Выбор какой метрики для отображения или сортировки значений путем выбора детализации отображаемых трендов. Выбор даты начала или конечных данных для отображаемых данных. . «Хватит с длинной преамбулой!» - восклицаете вы. Я продан! Скажем, мой первоначальный список выглядит примерно так.
Неверный путь: ввод списка значений
Это действительно все, что мы хотим сделать. Это похоже на хороший способ пойти на все, но на самом деле это хрупкий и рискованный подход, о чем мы поговорим позже. Существует лучший способ, и для создания не требуется много времени, чем описанный мной подход, основанный на картине.
Найти зависимые ячейки в exel 2003
Для наглядности создадим маленькую табличку (рис.1)
рис.1
Для понимания назовем столбцы (первая строка таблицы) по формулам, содержащимся в ячейках.
В ячейке F7 ошибка. Чтобы определить, чем она вызвана, добавляем на панель инструментов блок «Зависимости » (обведен синим).
Первая иконка - влияющие на формулу ячейки. Используя ее, получим вид рис.2
Шаг 1: Таблица на скрытом рабочем листе
Если у вас есть один из них, используйте его. На этом листе выведите список значений, которые вы хотите в раскрывающемся списке. Вы можете завершить работу с несколькими списками на этом листе. Вы можете организовать их любым способом, который имеет смысл. В конце концов, мы все равно скроем этот лист.
Шаг 2: Посмотрите, как обращаться с этими ячейками
Это упрощает сортировку списка. На английском языке алфавитный вид этих трех слов не ставит их в разумном порядке! Вы можете настроить стиль таблицы, если хотите, но это не обязательно. В конечном итоге мы хотим, чтобы раскрывающийся список был «первым столбцом в этой таблице». Но конкретный синтаксис для ссылок на компоненты таблицы может немного запутать, поэтому мы можем обмануть, чтобы точно определить, как обращаться к ячейкам.
рис.2
Точками отмечены ячейки, влияющие на значение, вычисляемое по формуле в клеточке с ошибкой.
Сложнее найти ошибку, когда формула в клеточке включает не только ячейки, содержащие значения, но и содержащие формулы. В нашем примере это столбец H.
Во-первых, активируйте неиспользуемую ячейку и поставьте в ней знак равенства. Затем переместите курсор над заголовком таблицы, пока не появится черная стрелка вниз. И в нашей пустой ячейке мы теперь имеем правильный синтаксис для ссылки на этот столбец.
Все, что нам нужно сделать, это вырезать значение в этой пустой ячейке, чтобы мы имели это в буфере обмена. Теоретически мы можем скрыть этот рабочий лист. На практике мы оставим его невидимым до тех пор, пока у нас не будет построено все, и электронная таблица будет готова для распространения.
Эксель предоставляет возможность увидеть и зависимые ячейки. Это можно сделать:
- По клавише F2 (рис.3)
- Используя панель инструментов, кнопку «зависимые ячейки» рис.4
- По комбинации клавиш Ctrl+] или Ctrl+Shift+]
Находясь в ячейке H4 и использовав функцию F2, получим выделение синим цветом клеточек, которые используются в формулах (влияющие ячейки), а сиреневым цветом - клеточка, являющаяся и влияющей и зависимой.
Создание проверки данных
Теперь вернемся к листу, где мы хотим использовать этот список для нашего раскрывающегося списка. Но вместо того, чтобы вручную вводить список значений для раскрывающегося списка, мы фактически вводим формулу. Итак, логично, это должно работать. В примере, который мы здесь используем, что произойдет, если мы поймем, что наш список неполный? Что произойдет, если мы вдруг осознаем, что нам действительно нужен параметр «Очень высокий» в раскрывающемся списке, и нам нужно «Неизвестное» значение в раскрывающемся списке?
рис.3
рис.4
Чтобы использовать вариант 3 поиска, нужно стать на нужную клеточку, нажать комбинацию клавиш. В результате курсор сместится на ту ячейку, данные которой зависят от искомой.
Это немного неуклюже, но, может быть, и не так уж плохо. Если мы хотим обновить возможные значения на всех трех листах, мы должны перейти на рабочий лист по рабочему листу, выбрав все ячейки с выпадающими списками в них и обновив значения списка. Вот где приходит красота подхода, описанного в этом посте. Используя таблицу на скрытом листе, все, что нам нужно сделать, это обновить одну таблицу! И таблицы имеют приятную особенность авторасширения, когда вы вводите что-то в ячейку, которая примыкает к таблице.
Мы также можем вставить новую строку в таблицу и добавить новое значение в любом месте списка. Это не изменит ни одно из значений, уже выбранных в наших существующих выпадающих списках, но теперь все выпадающие списки, которые ссылаются на эту таблицу, будут иметь обновленный список значений на выбор.
На рис. Показан результат для ячейки B4.
рис. 5
Поиск зависимых ячеек в эксель, версий от 2007
В более поздних версиях приложения эксель работа с формулами вынесена в отдельный блок (выделен синим прямоугольником на рис.5)
Хотя «обновление кучки ячеек, имеющих одинаковые значения списка выпадающих списков», является наиболее привлекательным вариантом для этой техники, есть и другие способы, которые это может пригодиться. Пример 1: Скажем, у нас есть раскрывающийся список выбора даты, который обновляет то, что отображается на кучу диаграмм. Когда мы добавляем данные в электронную таблицу, можно выбрать более возможные даты. Но мы хотим, чтобы выпадающий список был управляемым, поэтому мы хотим предоставить пользователю список последних 12 недель на выбор.
Таблица из 12 строк, в которой мы динамически выясняем, какие самые последние доступные данные, и помещаем их в верхнюю строку. Затем, используя простую формулу, мы заполняем следующие 11 значений в таблице. Если мы используем проверку данных для ссылки на эту таблицу, у нас всегда есть компактный, своевременный список!
рис.6
Для удобства, зависимости формул выделены в отдельный подблок (рис.6).
рис.7
Если формулы связаны в нескольких книгах эксель, то для отображения зависимостей, нужно, чтобы все эти книги были открыты.