Зависимость ячейки от другой ячейки excel

Обновлено: 06.07.2024

Вобщем вот это мне какнить надо воплотить в жизнь.

В принцепе у меня в мыслях было это воплотить через макрос или в basic-е создать программу, чтобы она вставляла данные в книгу exel, но это походу для меня савсем сложно, так что вариант отпал.

_________________________________________________________
Sub сортировка()
'
' сортировка Макрос
'
' Сочетание клавиш: Ctrl+я
'
Application.Goto Reference:="сп"
ActiveWorkbook.Worksheets("Список").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Список").Sort.SortFields.Add Key:=Range( _
"A1:A999"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Список").Sort
.SetRange Range("A1:B999")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
_________________________________________________________

Пожалуй, повторюсь: не нужны в примере никакие макросы! А при вашем знании vba, желание что-то в нем написать вообще более чем странное.
1. прочитайте про выпадающие списки здесь.
2. про сортировку формулами - примеры A-Nik (надеюсь, на этот раз угадал) и KL (XL).

Вы мой файл посмотрели?

Там в диапазонах выделенных желтым цветом, использованы выпадающие списки правил проверки которые:

- динамически расчитывают использованный диапазон базы данных
- показывают уникальные (без повторов) значения
- выдают отсортированный список

Никаких макросов, а только формулы и имена.

Господа, а может и вправду напишем?

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

Учитывая обилие книг по Excel, я тут уже продумал и оригинальный формат издания Он будет аналогичен серии Искусство рисования и живописи - т.е. еженедельно с примерами и обучающими трюками.
Основное отличие - не ориентация на общий проект, а еженедельный прогресс читателя. Срок - пол года. Затем - Excel для профи

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

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

Для чего вообще нужна подобная команда? К примеру, выделенная ячейка содержит формулу:
=СУММ( C6 * E19 ;СУММ(СУММ('Статьи затрат.xls'!C2;'Статьи затрат.xls'!C4;'Статьи затрат.xls'!C6;'Статьи затрат.xls'!C7;'Статьи затрат.xls'!C8);СУММ('Производственная себестоимость'!B4;'Производственная себестоимость'!B5)))
при этом от значения этой ячейки зависят значения еще нескольких ячеек(т.к. изменение значения этой ячейки изменит значение других, т.к. в них формулы ссылаются на эту ячейку), в данном случае это несколько ячеек листа Расчет.
Команда Зависимости ячейки построит наглядную карту зависимостей такой ячейки:

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

Так же есть возможность отобразить ссылки в формуле исключительно на ячейки других листов и книг.

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

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

имя книги, в которой расположена ячейка;
имя листа;
адрес влияющих/зависимых ячеек;
значение влияющей/зависимой ячейки(если ячеек несколько - отображаются все значения ячеек через точку-с-запятой);
формула влияющей/зависимой ячейки(если ячеек несколько - отображаются все формулы ячеек через точку-с-запятой).

Если для ячейки нет влияющих или зависимых ячеек - поле карты зависимостей остается пустым.

Выделить проверяемую ячейку - по нажатии будет активирована книга и лист с ячейкой, для которой была отображена текущая карта зависимостей. Может пригодиться, когда производится изучение зависимостей путем перехода по ячейкам (см. Переход к ячейкам по клику).

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

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

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

Для каждого окна карты зависимостей так же доступна функция выделения цветом зависимых или влияющих ячеек:

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

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

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

Из этого несомненно можно понять большую часть, но если присмотреться к изображению выше, то видно, что стрелка к той же ячейке С8 , на которую влияет выделенная С3 перекрывается стрелками влияющих ячеек C13 , C14:C18 и практически не заметна. А сразу увидеть ячейки с других листов и книг вообще не получится - для этого надо будет сначала дважды щелкнуть мышью на стрелке(которая ведет к значку в виде таблицы) и в появившемся окне выбрать одну( ! ) ссылку. А если ссылок больше 10? Сколько раз надо будет щелкать туда-сюда? Пока будем щелкать уже забудем, что хотели узнать. Так же в этом окне никак не помечены ссылки на закрытые книги - они просто ничем не отличаются от ссылок на доступные источники(открытые книги). О том, что ссылка недоступна узнать можно будет только после того, как попробуем на неё перейти.
И еще нюанс: при отображенных зависимостях одной ячейки отобразить зависимости другой нельзя, пока не уберем стрелки от первой ячейки(нажав Убрать стрелки). Т.е. для просмотра зависимостей второй ячейки, необходимо сначала убрать отображение зависимостей текущей. После чего заново отобразить стрелки через меню. Несколько затратно по времени, особенно если ячеек куча.

Обычный Выпадающий (раскрывающийся) список отображает только один перечень элементов. Связанный список – это такой выпадающий список , который может отображать разные перечни элементов, в зависимости от значения другой ячейки. Потребность в создании связанных списков (другие названия: связанные диапазоны , динамические списки ) появляется при моделировании иерархических структур данных. Например:

Отдел – Сотрудники отдела . При выборе отдела из списка всех отделов компании, динамически формируется список, содержащий перечень фамилий всех сотрудников этого отдела (двухуровневая иерархия);
Город – Улица – Номер дома . При заполнении адреса проживания можно из списка выбрать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

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

Создание Связанного списка на основе Проверки данных рассмотрим на конкретном примере.

Задача : Имеется перечень Регионов , состоящий из названий четырех регионов. Для каждого Региона имеется свой перечень Стран . Пользователь должен иметь возможность, выбрав определенный Регион , в соседней ячейке выбрать из Выпадающего списка нужную ему Страну из этого Региона .

Таблицу, в которую будут заноситься данные с помощью Связанного списка , разместим на листе Таблица . См. файл примера Связанный_список.xlsx

Список регионов и перечни стран разместим на листе Списки .

Обратите внимание, что названия регионов (диапазон А2:А5 на листе Списки ) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих стран ( В1:Е1 ).

Присвоим имена диапазонам, содержащим Регионы и Страны (т.е. создадим Именованные диапазоны ). Быстрее всего это сделать так:

выделитьячейки А1:Е6 на листе Списки (т.е. диапазон, охватывающий все ячейки с названиями Регионов и Стран );
нажать кнопку «Создать из выделенного фрагмента» (пункт меню Формулы/ Определенные имена/ Создать из выделенного фрагмента );
Убедиться, что стоит только галочка «В строке выше»;
Нажать ОК.

Проверить правильность имени можно через Диспетчер Имен ( Формулы/ Определенные имена/ Диспетчер имен ). Должно быть создано 5 имен.

Можно подкорректировать диапазон у имени Регионы (вместо =списки!$A$2:$A$6 установить =списки!$A$2:$A$5 , чтобы не отображалась последняя пустая строка)

На листе Таблица , для ячеек A 5: A 22 сформируем выпадающий список для выбора Региона .

Теперь сформируем выпадающий список для столбца Страна (это как раз и будет желанный Связанный список ).

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

Тестируем. Выбираем с помощью выпадающего списка в ячейке A 5 Регион – Америка , вызываем связанный список в ячейке B 5 и балдеем – появился список стран для Региона Америка : США, Мексика …

Теперь заполняем следующую строку. Выбираем в ячейке A 6 Регион – Азия , вызываем связанный список в ячейке B 6 и опять балдеем: Китай, Индия …

Необходимо помнить, что в именах нельзя использовать символ пробела. Поэтому, при создании имен, вышеуказанным способом, он будет автоматически заменен на нижнее подчеркивание «_». Например, если вместо Америка (ячейка В1 ) ввести « Северная Америка » (соответственно подкорректировав ячейку А2 ), то после нажатия кнопки Создать из выделенного фрагмента будет создано имя «Северная_Америка». В этом случае формула =ДВССЫЛ(A5) работать не будет, т.к. при выборе региона « Северная Америка » функция ДВССЫЛ() не найдет соответствующего имени. Поэтому формулу можно подкорректировать, чтобы она работала при наличии пробелов в названиях Регионов : =ДВССЫЛ(ПОДСТАВИТЬ(A5;" ";"_")) .

Теперь о недостатках . При создании имен с помощью кнопки меню Создать из выделенного фрагмента, все именованные диапазоны для перечней Стран были созданы одинаковой длины (равной максимальной длине списка для региона Европа (5 значений)). Это привело к тому, что связанные списки для других регионов содержали пустые строки.

Конечно, можно вручную откорректировать диапазоны или даже вместо Именованных диапазонов создать Динамические диапазоны . Но, при большом количестве имен делать это будет достаточно трудоемко. Кроме того, при добавлении новых Регионов придется вручную создавать именованные диапазоны для их Стран .

Чтобы не создавать десятки имен, нужно изменить сам подход при построении Связанного списка . Рассмотрим этот подход в другой статье: Расширяемый Связанный список .

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

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

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

Чтобы создать выпадающий список, выделите ячейку, где он должен появиться (или группу ячеек) и перейдите на вкладку Данные -> Проверка данных.

В появившемся окне укажите тип данных – Список, поставьте галочку рядом со строкой «Список допустимых значений».

Источником данных может быть:

Текст – пишется через точку с запятой «;» и без знака равно «=», например
Материалы;Заработная плата;Амортизация

Ссылки на ячейки:
=$A$1:$A$7

Именованный диапазон:
=ИмяДиапазона

Связанные выпадающие списки

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

Создадим выпадающие списки несколькими способами – для разных таблиц с исходными данными.

Способ 1. Названия групп в заголовках столбцов, в строках – элементы групп.

Способ 2. Названия групп – в первом столбце, элементы групп – во втором столбце.

Способ 1. Связанные выпадающие списки из таблицы с группами в заголовках столбцов

Исходные данные: таблица с названиями групп в заголовках столбцов.

Справка:

Форматированная («умная») таблица Excel

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

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

Создать форматированную таблицу просто: выделите диапазон ячеек и перейдите в меню Главная -> Форматировать как таблицу -> выберите понравившийся вид таблицы. Готово – форматированная таблица создана.

Формула ДВССЫЛ

Формула ДВССЫЛ передает значения из ячейки, адрес которой записан в самой формуле в виде текстовой строки.

Например, записываем в ячейке B1 адрес ячейки А1. Формула ДВССЫЛ( B1 ) «увидит», какой адрес записан в ячейке B1, а результатом вычисления формулы будет текст, записанный в ячейке А1. Эту же формулу можно записать, указав адрес ячейки в кавычках – ДВССЫЛ( « А1 » ).

С помощью ДВССЫЛ можно обратиться к ячейке по адресу с помощью других формул, например СЦЕПИТЬ, & или ЕСЛИ и т.д. Так, формула на рисунке ДВССЫЛ( B1 & C1 ) обращается к тексту в ячейке А1. После нажатия Enter в ячейке, где вводилась формула ДВССЫЛ( B1 & C1 ) появится значение из ячейки A1, в нашем случае это «текст».

Пошаговая инструкция по созданию связанных выпадающих списков

Шаг 1. Создайте справочник исходных данных в виде форматированной smart-таблицы.

Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: выберите в меню Главная -> Форматировать как таблицу.

В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.

Столбец таблицы: = Источник[Материалы]

Чтобы появилась такая формула, нажмите равно = и выделите столбец, его имя появится в строке формул.

Шаг 2. Создайте выпадающий список с группами.

Готово! В столбце «группа» появился выпадающий список.

Шаг 3. Создайте выпадающий список со статьями.

Готово! В столбце «статья» появляется только список статей, входящих в группу.

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

Способ 2. Связанные выпадающие списки из таблицы с группами в первом столбце и элементами - во втором

Исходные данные: таблица с названиями групп в первом столбце, элементами групп – во втором столбце.

На самом деле в сети можно найти несколько вариантов реализации этого способа. Но у них у всех есть один недостаток: такой список нужно «администрировать». Потому что таблица должна быть всегда отсортирована по названиям групп – нельзя, чтобы группы располагались произвольно. Если группы будут идти «как попало», то формула, с помощью которой это всё сделано (СМЕЩ) не сработает, и список будет создаваться с ошибкой. Т.е. пользователю нужно все время сортировать первый столбец или добавлять туда данные в алфавитном порядке. А еще потребуется записывать где-то отдельно сами названия групп и это тоже нужно будет делать «вручную».

Мы придумали, как обойти эту проблему: с помощью сводных таблиц , которые будут за нас упорядочивать данные. Благодаря этому постоянное «администрирование» будет заменено на простую процедуру «обновить».

Для создания списков используем форматированные (умные) таблицы, сводные таблицы, формулы СМЕЩ + ПОИСКПОЗ + СЧЁТЗ, СЧЁТЕСЛИ и диспетчер имен.

Справка:

Формула СМЕЩ

СМЕЩ выдает ссылку на диапазон ячеек, находящийся в указанном количестве ячеек от исходной. Ссылка определяется с учетом заданного в формуле размера диапазона – числа строк и столбцов. Другими словами, этой формулой вы можете «сказать» Excel-ю на сколько ячеек он должен отступить и какой диапазон «захватить».

Синтаксис формулы СМЕЩ такой:

СМЕЩ(ссылка ; смещ_по_строкам ; смещ_по_столбцам ; [высота] ; [ширина] ), где

ссылка – ссылка, от которой вычисляется смещение, может быть адресом ячейки или группы ячеек;
смещ_по_строкам – количество строк, которые требуется отсчитать вверх или вниз от начальной ссылки;
смещ_по_столбцам – количество столбцов, которые требуется отсчитать влево или вправо от начальной ссылки;
[высота] – число строк возвращаемой ссылки (необязательный);
[ширина] – число столбцов возвращаемой ссылки (необязательный).

Формула ПОИСКПОЗ

Ищет нужный нам элемент в диапазоне ячеек и выдает его порядковый номер в диапазоне.

Синтаксис ПОИСКПОЗ такой:

ПОИСКПОЗ( искомое_значение ; просматриваемый_массив ; [тип_сопоставления] )

Подробнее про эту формулу можно посмотреть в видеоинструкции: Какая формула лучше ВПР и работает с несколькими критериями

Формула СЧЁТЗ

СЧЁТЗ просто считает количество непустых ячеек в диапазоне.

Формула СЧЁТЕСЛИ

Почти тот же СУММЕСЛИ, только проще – подсчитывает количество значений, соответствующих определенному условию.

Пошаговая инструкция по созданию списков

Шаг 1. Преобразуйте исходные данные в форматированную smart-таблицу.

Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: перейдите в меню Главная -> Форматировать как таблицу.

В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.

Форматированная таблица «статьи» создана.

Создайте первую сводную таблицу с группами статей.
Выделите любую ячейку таблицы с исходными данными, перейдите в меню Вставка -> Сводная таблица. Добавьте сводную таблицу на существующий лист и поместите группы в область строк.

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

Форматируем сводную таблицу со статьями и придаем ей вид справочника.
Выделите любую ячейку таблицы, перейдите на вкладку Конструктор -> Макет отчета -> Показать в табличной форме. У нас получится почти та таблица, которая нам нужна, но в ней автоматом появятся промежуточные суммы. Чтобы их отключить, идем: Промежуточные итоги -> Не показывать промежуточные суммы.

Скройте строку «Общий итог» в обеих таблицах справочников. Перейдите на вкладку Конструктор -> Общие итоги -> Отключить для строк и столбцов.

В итоге получатся два справочника, как на рисунке ниже. Для удобства разместите таблицы рядом на одном листе – с первой строки и в столбцах A, C и D, как на рисунке (это поможет разобраться с формулой СМЕЩ).

Шаг 3. Создайте именованные диапазоны с помощью диспетчера имен.

Откройте диспетчер имен: в меню Формулы -> Диспетчер имен.

- Введите имя «ГруппыСписок» и формулу, которая будет определять диапазон:
  =СМЕЩ($A$1;1;0;СЧЁТЗ($A:$A)-1;1)
Пояснения к формуле:

СМЕЩ ( $A$1 ; 1 ; 0 ; СЧЁТЗ( $A:$A ) – 1 ; 1 ) – определяет адрес ячеек с названиями групп.
- - $A$1 – это первая ячейка в справочнике групп.
  - Следующие цифры 1 ; 0 – это отступ от первой ячейки на 1 строку и 0 столбцов (отступ нужен, потому что в первой ячейке название столбца).
  - СЧЁТЗ( $A:$A ) – 1 Считаем число непустых ячеек в столбце А. Вычитаем -1, потому что название столбца не должно быть в списке.
  - Последнее число 1 в формуле – это количество столбцов.
  Нажмите ОК. Названия листов в формуле появятся сами.
  - Точно так же создайте в диспетчере имен список статей.
    Введите имя ГруппыСтатей, а для диапазона – формулу:
    =СМЕЩ($C$1;ПОИСКПОЗ($G2;$C:$C;0)-1;1;СЧЁТЕСЛИ($C:$C;$G2);1)
  Пояснения к формуле:
  
  СМЕЩ ( $C$1 ; ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 ; 1 ; СЧЁТЕСЛИ( $C:$C ; $G2 ) ; 1 ) – определяет адрес ячеек с названиями статей из группы с помощью ПОИСКПОЗ, которая ищет группы статей.
  - $С$1 – это первая ячейка в столбце с группами.
  - ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 Определяет, на сколько строк нужно отступить от первой ячейки. ПОИСКПОЗ ищет название группы, выбранной в таблице с данными (столбец $G) среди ячеек справочника (столбец $C). В адресе ячейки $G2 не «закрепляем» номер строки с помощью знака $, чтобы формула работала для каждой ячейки в столбце.
  - Следующая цифра 1 – это отступ на 1 столбец вправо, т.е. переходим к столбцу «статьи», откуда нужно брать данные.
  - СЧЁТЕСЛИ( $C:$C ; $G2 ) – считаем число ячеек в столбце $C, в которых названия групп такие же, как в столбце с данными. Здесь тоже не «закрепляем» номер строки у ячейки $G2 с помощью знака $.
  - Последнее число 1 в формуле – это количество столбцов.
  Шаг 4. Создайте выпадающие списки.
  
  Выделите ячейки в столбце «группы», перейдите в меню Данные -> Проверка данных. Задайте тип данных Список, источник =ГруппыСписок.
  
  То же самое – для статей. Тип данных – список, источник =ГруппыСтатьи
  
  Выпадающие списки готовы. Форматированные smart-таблицы позволят «захватить» все данные, а сводные таблицы – избежать ошибок, отсортировать справочник и создать список групп.
  
  Читайте также: