Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Использование умных таблиц в проверке данных, Умные таблицы, проверка данных
 
#1
22.07.2017 12:33:25
Здравствуйте.

Столкнулся со следующей ситуацией и прошу помочь разобраться.
Есть два набора данных, первый оформлен как умная таблица "Т1", второй - именованный диапазон "Д1". В ячейку H13 вставляю проверку данных (необходим выпадающий список с возможными значениями ячейки). Если в проверке данных такая формула (со ссылкой на именованный диапазон)
Код
=индекс(Д1;;1)
то все работает (как видно в файле примера)
При вставке подобных ссылок, но на таблицу
Код
=индекс(т1;;1)
=индекс(т1[#Данные];;1)
получаем ошибку.

Возможно ли использовать умные таблицы в подобных задачах (а в документе предполагается много проверок данных) или пользоваться только именованными диапазонами?
Изменено: Дмитрийже - 22.07.2017 12:34:39
 
 
 
#2
22.07.2017 12:42:48
Цитата
Дмитрийже написал:
Возможно ли использовать умные таблицы в подобных задачах
Возможно.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#3
22.07.2017 12:59:41
Bema,
Не совсем то, в вашем решении не используется таблица, а просто определен диапазон.
Код
=$B$4:$B$11
Минусы такого решения в том, что при увеличении строк в таблице (они автоматом будут добавляться в умную таблицу) список возможных значений останется неизменным.
Причем, что интересно, на текущем листе (ваш список) автоматически обновляется, а на другом листе (лист 2) уже нет.
В моем случае предполагается использовать подобные комбинации вообще в разных файлах, поэтому важна привязка именно к имени таблицы.
 
 
 
#4
22.07.2017 13:08:21
Цитата
Дмитрийже написал:
список возможных значений останется неизменным.
Вы добавляете данные в умную таблицу и они добавляются в проверке данных.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#5
22.07.2017 13:14:31
Bema,
В вашей формуле, расположенной на том же листе - да, а вот подобная конструкция на листе 2 уже не работает (желтая я ячейка в примере проверка2.xlsx)
 
 
 
#6
22.07.2017 13:17:32
Support MS рекомендует присвоить нужному столбцу Умной таблицы свое Имя, и уже его использовать в Проверке данных
Согласие есть продукт при полном непротивлении сторон
 
 
 
#7
22.07.2017 13:29:21
Sanja,
Насколько я понимаю, в умных таблицах столбцы именуются автоматически и имя прописывается в заголовке.
Код
=Т1[с1]

такая конструкция тоже не проходит.

Статью МС читал, но криво совсем на мой вкус - мы опять абстрагируемся от таблицы и возвращаемся к именованным диапазонам, в таком случае проще всю таблицу использовать как именованный диапазон. Из серьезных минусов такого решения, которые я вижу, надо отслеживать синхронное изменение именованного диапазона при изменении умной таблицы, что не факт.
 
 
 
#8
22.07.2017 13:39:56
Цитата
Дмитрийже написал:
синхронное изменение именованного диапазона при изменении умной таблицы, что не факт.
факт
Согласие есть продукт при полном непротивлении сторон
 
 
 
#9
22.07.2017 13:47:20
Sanja, Bema, Спасибо, ребята, за помощь.

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

Решение:
Код
=ДВССЫЛ("Таблица2[Столбец1]")
Может кому пригодится, поэтому выкладываю пример решения.
 
 
 
#10
22.07.2017 13:49:44
.
Изменено: Sanja - 22.07.2017 13:50:02
Согласие есть продукт при полном непротивлении сторон
 
 
 
#11
19.09.2023 16:32:07
Дмитрийже, Спасибо за помощь. Идеально. От себя добавлю, что таким же образом можно использовать при проверке данные из запросов PQ.
 
 
 
#12
26.02.2024 12:37:48
Цитата
написал:
=ДВССЫЛ("Таблица2[Столбец1]")
Спасибо большое! Идеально подходящее решение!
 
 
 
#13
22.01.2025 20:19:33
Цитата
написал:
Решение:Код=ДВССЫЛ("Таблица2[Столбец1]")Может кому пригодится, поэтому выкладываю пример решения.
Доброго дня!
При решении своей задачи, воспользовался такой же конструкцией, это очень удобно, особенно, когда источником данных для проверки является умная таблица, НО! Что если в этом примере мы захотим источником выбрать Столбец2 Таблицы2:

Код
=ДВССЫЛ("Таблица2[Столбец2]")

К сожалению, мы получим список с пустыми строками!

Кто знает, как этого избежать, не уходя от идеи умной таблицы (важно, удобно для задачи), подскажите пожалуйста.

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

Изменено: Zoizenhover - 22.01.2025 20:25:14
 
 
 
#14
22.01.2025 20:45:28
Цитата
Zoizenhover написал: в этом примере мы захотим источником выбрать Столбец2 Таблицы2
Код
=СМЕЩ($C$3;;;ЧСТРОК(ДВССЫЛ("Таблица2[Столбец2]"))-СЧИТАТЬПУСТОТЫ(ДВССЫЛ("Таблица2[Столбец2]")))
Изменено: Sanja - 23.01.2025 04:38:23
Согласие есть продукт при полном непротивлении сторон
 
 
 
#15
26.01.2025 19:46:26
Спасибо большое за ответ!
Но к сожалению первый аргумент функции СМЕЩ тоже должен быть динамическим,  какой именно столбец нужен  - тоже динамический параметр,
допустим в ячейке A1 находится название нужного столбца умной таблицы, то есть значение выбирается путем проверки данных:
Код
=ДВССЫЛ("Таблица2[#Заголовки]")
пытался задать адрес первой ячейки нужного столбца УТ так:
Код
=ДВССЫЛ(АДРЕС(СТРОКА(Таблица2[#Заголовки])+1;СТОЛБЕЦ(ДВССЫЛ(СЦЕПИТЬ("Таблица2[";A1;"]")));;;"Название листа с этой проклятой таблицей условий"))

И эта конструкция не работает в проверке данных (а просто в ячейке, как формула массива работает)

Проблем с функциями ЧСТРОК и СЧИТАТЬПУСТОТЫ  нет, там я успешно "Сцепляю" название таблицы с её столбцом и количество непустых значений считается верно

РЕШЕНО:

адрес первой ячейки нужного столбца умной таблицы с условиями стоит находить так:

Код
ДВССЫЛ(ЯЧЕЙКА("адрес";ИНДЕКС(ДВССЫЛ(СЦЕПИТЬ("Таблица2[";А1;"]"));1;1))

ИТОГО, формула проверки данных будет выглядеть так:

Код
=СМЕЩ(
ДВССЫЛ(
ЯЧЕЙКА("адрес";ИНДЕКС(ДВССЫЛ(СЦЕПИТЬ("Таблица2[";A1;"]"));1;1)));
;
;
ЧСТРОК(ДВССЫЛ(СЦЕПИТЬ("Таблица2[";A1;"]")))-СЧИТАТЬПУСТОТЫ(ДВССЫЛ(СЦЕПИТЬ("Таблица2[";A1;"]"))
))

Изменено: Zoizenhover - 26.01.2025 21:25:14
 
 
 
Страницы: 1
Читают тему
Наверх