Excel двссыл при вычислении источник возникает ошибка в excel

Хитрости »

1 Май 2011              197727 просмотров

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

Скачать файл, используемый в видеоуроке:

  Связанные выпадающие списки.xls (216,5 KiB, 3 066 скачиваний)

Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и как его создать. Теперь попробуем разобраться что значит выражение «связанный выпадающий список». Я бы еще назвал такой список зависимым. Т.е. когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка А2. В ней создан выпадающий список со значениями: Овощи, Фрукты, Мясо, Напитки. А в ячейке В2 нам нужен такой список, чтобы значения этого самого списка изменялись в зависимости от того, какое значение мы укажем в ячейке А2 — т.е. список выбранной категории продуктов. Например выбрали в А2 значение Овощи — в В2 появился выпадающий список, содержащий значения: Морковь, Капуста, Картошка, Редиска, Помидоры. Выбрали в А2 Мясо — в В2 появился выпадающий список, содержащий значения: Говядина, Телятина, Свинина, Курица, Индейка. И т.д.

Подготовка
Для начала нам потребуется создать все эти списки. Что-то вроде этого:

Далее для каждого из этих списков необходимо назначить именованный диапазон. Создать можно любым способом из описанных в этой статье. Главное помнить — если сами списки расположено на листе, отличном от того, на котором списки выпадающие — то обязательно создавать именованный диапазон с назначением области действия — Книга.
В приложенном к статье примере диапазоны имеют имена категорий — их можно видеть в заголовках.
Если ваши категории содержат пробел — необходимо заменить его на нижнее подчеркивание (_) или удалить, т.к. в качестве именованного диапазона такое значение не подойдет и ничего в результате не получится.

Создание зависимых списков
В ячейке А2 создаем «список списков» — основной список, на основании значений которого будет создаваться второй список. Этот список может быть создан любым способом (как создать выпадающий список). Назовем его Список категорий.
В ячейке В2 потребуется создать список на основании формулы, хоть по сути и так же, как и остальные: вкладка Данные (Data)—Проверка данных (Data validation)—Список (List). Но теперь вместо прямого указания имени списка необходимо указать ссылку на именованный диапазон, который мы выберем в Списке категорий(ячейка А2), на основании его имени. В этом нам поможет функция ДВССЫЛ (INDIRECT). Просто записываем эту формулу в поле Источник (Source): =ДВССЫЛ($A2)

На что обратить внимание: если вы планируете распространять такой список на столбец, то ссылка должна выглядеть именно так: $A2. Перед цифрой не должно быть знака доллара ($A$2 — неправильно). Иначе зависимый список будет всегда формироваться исключительно на основании значении ячейки А2.

Источник из другой книги
Сами списки товара могут находится и в другой книге. Если книга называется Книга со списком.xls и на Лист1 в ячейке А1 в этой книге находится имя нужного нам списка, то формула будет выглядеть так:
=ДВССЫЛ(«‘[Книга со списком.xls]Лист1’!»&$A$1)
На что обратить внимание: лучше всегда перед именем книги и после имени листа ставить апостроф — ‘. Так вы избежите проблем и недопонимания, если имя листа или книги содержит пробелы и иные специфические символы. В отличии от списков внутри одной книги в данном случае знак доллара должен быть и перед буквой и перед цифрой. В ином случае возможны ошибки (если, конечно, это не было сделано специально с пониманием того, что делалось).

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

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

И ничего с этими ограничениями не поделать при подобном подходе.

Скачать пример

  Tips_Lists_Connect_Validation.xls (26,5 KiB, 18 103 скачиваний)

Так же см.:
Выпадающие списки
Именованные диапазоны
Динамические именованные диапазоны

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Функция ДВССЫЛ возвращает ссылку, которая задана текстовой строкой. К примеру, формула = ДВССЫЛ (А3) аналогична формуле = А3. Но для этой функции ссылка является просто текстовой строкой: ее можно изменять формулами.

Функция применяется для изменения ссылки на ячейку без изменения самой формулы, выведения значений лишь из четных/ нечетных строк, транспонирования таблиц и др.

Синтаксис функции с описанием

Аргументы:

1. Ссылка на текст. Обязательный аргумент, содержащий ссылку на ячейку в формате текстовой строки. Например, =ДВССЫЛ («А1») или =ДВССЫЛ («Лист 2!А3»).
2. А1 – логическое значение для определения типа ссылки. Необязательный аргумент. Если имеет значение ИСТИНА или опущен, то ссылка на текст воспринимается как ссылка типа А1 (адрес ячейки указан явно). Значение ЛОЖЬ – как ссылка в стиле R1C1 (ссылка на ячейку с формулой, отсылающей к другой ячейке).

Почему при работе функции ДВССЫЛ возникают ошибки:

1. Значение аргумента «ссылка на ячейку» является недопустимой ссылкой – ошибка #ССЫЛКА!.
2. Обязательный аргумент ссылается на другую книгу (является внешней ссылкой), которая недоступна (закрыта) – ошибка #ССЫЛКА!. При создании подобных формул внешний источник данных должен быть открыт.
3. Значение обязательного аргумента ссылается на превышающий предел диапазон – ошибка #ССЫЛКА!. Функция может обработать до 1048576 строк или 16384 столбцов.



Функция ДВССЫЛ в Excel: примеры

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

Имеется таблица с данными:

Примеры функции ДВССЫЛ:

Рассмотрим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках расположены однотипные данные (информация об образовании сотрудников фирмы за последние 5 лет).

Нужно на основе имеющихся таблиц составить итоговую таблицу на отдельном листе, собрав данные с пяти листов. Сделаем это с помощью функции ДВССЫЛ.

Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и вправо). Данные с пяти различных листов собираются в итоговую таблицу.

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

Чтобы получить только нечетные записи, используем формулу:

Для выведения четных строк:

Примечание. Функция СИМВОЛ возвращает символ по заданному коду. Код 65 выводит английскую букву A. 66 – B. 67 – С.

Допустим, у пользователя имеется несколько источников данных (в нашем примере – несколько отчетов). Нужно вывести количество сотрудников, основываясь на двух критериях: «Год» и «Образование». Для поиска определенного значения в базе данных подходит функция ВПР.

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

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

Задача: при выборе года и образования в столбце «Количество» должно появляться число сотрудников.

Если мы используем только функцию ВПР, появится ошибка:

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

Исправить положение помогла функция ДВССЫЛ, которая возвращает ссылку, заданную текстовой строкой.

Функции ВПР и ДВССЫЛ в Excel

Теперь формула работает корректно. Для решения подобных задач нужно применять одновременно функции ВПР и ДВССЫЛ в Excel.

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

В отношении двух отчетов сработает комбинация функций ВПР и ЕСЛИ:

Но для наших пяти отчетов применять функцию ЕСЛИ нецелесообразно. Чтобы возвратить диапазон поиска, лучше использовать ДВССЫЛ:

• $A$12 – ссылка с образованием (можно выбирать из выпадающего списка);
• $C11 – ячейка, в которой содержится первая часть названия листа с отчетом (все листы переименованы: «2012_отчет», 2013_отчет» и т.д.);
• _отчет!A3:B10 – общая часть названия всех листов и диапазон с отчетом. Она соединяется со значением в ячейке С11 (&). В результате получается полное имя нужного диапазона.

Таким образом, эти две функции выполняют подобного рода задачи на отлично.