Использование структурированных ссылок в таблицах Excel
При создании таблицы Excel Excel присваивает имя таблице и каждому заголовку столбца в таблице. Можно сделать так, чтобы при добавлении формул эти имена отображались автоматически и ссылки на ячейки в таблице можно было выбрать вместо ввода вручную. Вот пример того, что происходит в Excel:
Прямая ссылка на ячейки
Имена таблицы и столбцов в Excel
Это сочетание имен таблицы и столбца называется структурированной ссылкой. Имена в структурированных ссылках корректируются при добавлении данных в таблицу или их удалении.
Структурированные ссылки также появляются, когда вы создаете формулу вне таблицы Excel, которая ссылается на данные таблицы. Ссылки могут упростить поиск таблиц в крупной книге.
Чтобы добавить структурированные ссылки в формулу, можно щелкнуть ячейки таблицы, на которые нужно сослаться, а не вводить ссылку непосредственно в формуле. Давайте используем следующий пример данных, чтобы ввести формулу, которая автоматически использует структурированные ссылки для расчета суммы комиссии за продажу.
Менеджер по продажам
Сумма продаж
ПроцентКомиссии
ОбъемКомиссии
- Скопируйте пример данных из приведенной выше таблицы, включая заголовки столбцов, и вставьте их в ячейку A1 нового листа Excel.
- Чтобы создать таблицу, выделите любую ячейку в диапазоне данных и нажмите клавиши CTRL+T.
- Установите флажок Моя таблица с заголовками и нажмите кнопку ОК.
- В ячейке E2 введите знак равенства (=) и щелкните ячейку C2. В строке формул после знака равенства появится структурированная ссылка [@[ОбъемПродаж]].
- Введите звездочку (*) непосредственно после закрывающей скобки и щелкните ячейку D2. В строке формул после звездочки появится структурированная ссылка [@[ПроцентКомиссии]].
- Нажмите клавишу ВВОД. Excel автоматически создает вычисляемый столбец и копирует формулу вниз по нему, корректируя ее для каждой строки.
Что произойдет, если я буду использовать прямые ссылки на ячейки?
Если вы введете в вычисляемый столбец прямые ссылки на ячейки, может быть сложнее понять, что вычисляет формула.
- В образце листа щелкните ячейку E2.
- В строке формул введите =C2*D2 и нажмите клавишу ВВОД.
Обратите внимание на то, что хотя Excel копирует формулу вниз по столбцу, структурированные ссылки не используются. Если, например, вы добавите столбец между столбцами C и D, вам придется исправлять формулу.
Как изменить имя таблицы?
При создании таблицы Excel ей назначается имя по умолчанию («Таблица1», «Таблица2» и т. д.), но его можно изменить, чтобы сделать более осмысленным.
- Выберите любую ячейку в таблице, чтобы отобразить вкладку Работа с таблицами >Конструктор на ленте.
- Введите нужное имя в поле Имя таблицы и нажмите клавишу ВВОД.
В этом примере мы используем имя ОтделПродаж.
При выборе имени таблицы соблюдайте такие правила:
- Используйте допустимые символы. Имя всегда должно начинаться с буквы, символа подчеркивания (_) или обратной косой черты (\). Остальная часть имени может включать в себя буквы, цифры, точки и символы подчеркивания. В имени нельзя использовать латинские буквы C, c, R и r, так как они служат для быстрого выделения столбца или строки с активной ячейкой при вводе их в поле Имя или Перейти.
- Не используйте ссылки на ячейки. Имена не могут иметь такой же вид, как ссылки на ячейки, например Z$100 или R1C1.
- Не используйте пробелы для разделения слов. В имени нельзя использовать пробелы. Можно использовать символ подчеркивания (_) и точку (.). Примеры допустимых имен: ОтделПродаж, Налог_на_продажи, Первый.квартал.
- Используйте не более 255 знаков. Имя таблицы может содержать не более 255 знаков.
- Использование уникальных имен таблиц Повторяющиеся имена не допускаются. Excel не различает символы в верхнем и нижнем регистрах в именах, поэтому если вы введете «Продажи», но уже имеете другое имя «SALES» в той же книге, вам будет предложено выбрать уникальное имя.
- Использование идентификатора объекта Если вы планируете использовать сочетание таблиц, сводных таблиц и диаграмм, рекомендуется префиксировать имена с помощью типа объекта. Например, tbl_Sales для таблицы продаж, pt_Sales для сводной таблицы продаж и chrt_Sales для диаграммы продаж или ptchrt_Sales для сводной диаграммы продаж. При этом все имена будут храниться в упорядоченном списке в диспетчере имен.
Правила синтаксиса структурированных ссылок
Вы также можете ввести или изменить структурированные ссылки вручную в формуле, но это поможет понять синтаксис структурированных ссылок. Рассмотрим такую формулу:
В этой формуле используются указанные ниже компоненты структурированной ссылки.
- Имя таблицы:DeptSales — это пользовательское имя таблицы. Он ссылается на данные таблицы без каких-либо строк заголовка или итогов. Вы можете использовать имя таблицы по умолчанию, например Table1, или изменить его, чтобы использовать пользовательское имя.
- Описатель столбцов:[Сумма продаж]и[Сумма комиссии] — это описатели столбцов, которые используют имена столбцов, которые они представляют. Они ссылаются на данные столбца без заголовка столбца или строки итогов. Всегда заключайте описатели в квадратные скобки, как показано ниже.
- Описатель элемента:[#Totals] и [#Data] — это специальные описатели элементов, которые ссылаются на определенные части таблицы, например на строку итогового значения.
- Табличный описатель:[#Totals], [Сумма продаж]] и [[#Data],[Сумма комиссии]] являются табличными описателями, представляющими внешние части структурированной ссылки. Внешние ссылки следуют за именем таблицы и заключают их в квадратные скобки.
- Структурированная ссылка:(DeptSales[[#Totals],[Sales Amount]] и DeptSales[[#Data],[Commission Amount]] представляют собой структурированные ссылки, представленные строкой, которая начинается с имени таблицы и заканчивается описателем столбца.
При создании или изменении структурированных ссылок вручную учитывайте перечисленные ниже правила синтаксиса.
- Заключайте указатели в квадратные скобки. Все указатели таблиц, столбцов и специальных элементов должны быть заключены в парные скобки ([ ]). Указатель, содержащий другие указатели, требует наличия таких же внешних скобок, в которые будут заключены внутренние скобки других указателей. Например: =DeptSales[[Sales Person]:[Region]]
- Все заголовки столбцов — это текстовые строки. Но для них не требуются кавычки, если они используются в структурированной ссылке. Числа или даты, например 2014 или 01.01.2014, также считаются текстовыми строками. Нельзя использовать выражения с заголовками столбцов. Например, выражение ОтделПродажСводкаФГ[[2014]:[2012]] недопустимо.
Заключайте в квадратные скобки заголовки столбцов, содержащие специальные знаки. Если присутствуют специальные знаки, весь заголовок столбца должен быть заключен в скобки, а это означает, что для указателя столбца потребуются двойные скобки. Пример: =ОтделПродажСводкаФГ[[Итого $]]
Дополнительные скобки в формуле нужны при наличии таких специальных знаков:
- TAB
- Канал строки
- Возврат каретки
- Запятая (,)
- Двоеточие (:)
- Точка (.)
- Левая скобка ([)
- Правая скобка (])
- Знак фунта (#)
- Одна кавычка (‘)
- Двойная кавычка («)
- Левая фигурная скобка ( <)
- Правая фигурная скобка (>)
- Знак доллара ($)
- Caret (^)
- Амперсанд (&)
- Звездочка (*)
- Знак «плюс» (+)
- Знак равенства (=)
- Знак минус (-)
- Больше символа (>)
- Меньше символа ( <)
- Знак деления (/)
- При знаке (@)
- Обратная косая черта (\)
- Восклицательный знак (!)
- Левая скобка (()
- Правая скобка ())
- Знак процента (%)
- Вопросительный знак (?)
- Обратный тик (‘)
- Точка с запятой (;)
- Тильда (~)
- Подчеркивание (_)
- Используйте escape-символы для некоторых специальных знаков в заголовках столбцов. Перед некоторыми знаками, имеющими специфическое значение, необходимо ставить одинарную кавычку (‘), которая служит escape-символом. Пример: =ОтделПродажСводкаФГ[‘#Элементов]
Ниже приведен список специальных символов, которым требуется escape-символ (‘) в формуле:
- Левая скобка ([)
- Правая скобка (])
- Знак фунта(#)
- Одна кавычка (‘)
- При знаке (@)
Используйте пробелы для повышения удобочитаемости структурированных ссылок. С помощью пробелов можно повысить удобочитаемость структурированной ссылки. Пример: =ОтделПродаж[ [Продавец]:[Регион] ] или =ОтделПродаж[[#Заголовки], [#Данные], [ПроцентКомиссии]].
Рекомендуется использовать один пробел:
- После первой левой скобки ([)
- Перед последней правой скобкой (]).
- После запятой.
Операторы ссылок
Перечисленные ниже операторы ссылок служат для составления комбинаций из указателей столбцов, что позволяет более гибко задавать диапазоны ячеек.
Эта структурированная ссылка:
Ссылается на:
Диапазон ячеек:
Все ячейки в двух или более смежных столбцах
: (двоеточие) — оператор ссылки
Сочетание двух или более столбцов
, (запятая) — оператор объединения
Пересечение двух или более столбцов
(пробел) — оператор пересечения
Указатели специальных элементов
Чтобы сослаться на определенную часть таблицы, например на строку итогов, в структурированных ссылках можно использовать перечисленные ниже указатели специальных элементов.
Этот указатель специального элемента:
Ссылается на:
Вся таблица, включая заголовки столбцов, данные и итоги (если они есть).
Только строки данных.
Только строка заголовка.
Только строка итога. Если ее нет, будет возвращено значение null.
Только ячейки в той же строке, где располагается формула. Эти указатели нельзя сочетать с другими указателями специальных элементов. Используйте их для установки неявного пересечения в ссылке или для переопределения неявного пересечения и ссылки на отдельные значения из столбца.
Excel автоматически заменяет указатели «#Эта строка» более короткими указателями @ в таблицах, содержащих больше одной строки данных. Но если в таблице только одна строка, Excel не заменяет указатель «#Эта строка», и это может привести к тому, что при добавлении строк вычисления будут возвращать непредвиденные результаты. Чтобы избежать таких проблем при вычислениях, добавьте в таблицу несколько строк, прежде чем использовать формулы со структурированными ссылками.
Определение структурированных ссылок в вычисляемых столбцах
Когда вы создаете вычисляемый столбец, для формулы часто используется структурированная ссылка. Она может быть неопределенной или полностью определенной. Например, чтобы создать вычисляемый столбец с именем Commission Amount, который вычисляет сумму комиссии в долларах, можно использовать следующие формулы:
Тип структурированной ссылки
Перемножает соответствующие значения из текущей строки.
Перемножает соответствующие значения из каждой строки обоих столбцов.
Общее правило таково: если структурированная ссылка используется внутри таблицы, например, при создании вычисляемого столбца, то она может быть неопределенной, но вне таблицы нужно использовать полностью определенную структурированную ссылку.
Примеры использования структурированных ссылок
Ниже приведены примеры использования структурированных ссылок.
Эта структурированная ссылка:
Ссылается на:
Диапазон ячеек:
Все ячейки в столбце «ОбъемПродаж».
Заголовок столбца «ПроцентКомиссии».
Итог столбца «Регион». Если нет строки итогов, будет возвращено значение ноль.
Все ячейки в столбцах «ОбъемПродаж» и «ПроцентКомиссии».
Только данные в столбцах «ПроцентКомиссии» и «ОбъемКомиссии».
Только заголовки столбцов от «Регион» до «ОбъемКомиссии».
Итоги столбцов от «ОбъемПродаж» до «ОбъемКомиссии». Если нет строки итогов, будет возвращено значение null.
Только заголовок и данные столбца «ПроцентКомиссии».
=ОтделПродаж[[#Эта строка], [ОбъемКомиссии]]
Ячейка на пересечении текущей строки и столбца Commission Amount. При использовании в той же строке, что и заголовок или итоговая строка, возвращается ошибка #VALUE! .
Если ввести длинную форму этой структурированной ссылки (#Эта строка) в таблице с несколькими строками данных, Excel автоматически заменит ее укороченной формой (со знаком @). Две эти формы идентичны.
E5 (если текущая строка — 5)
Методы работы со структурированными ссылками
При работе со структурированными ссылками учитывайте следующее.
- Автозаполнение формул может оказаться очень полезным при вводе структурированных ссылок для соблюдения правил синтаксиса. Дополнительные сведения см. в статье Использование автозаполнения формул.
- Решите, следует ли создавать структурированные ссылки для таблиц в полувыборах По умолчанию при создании формулы при щелчке диапазона ячеек в таблице выбирается полуэлемерная ячейка и автоматически вводится структурированная ссылка вместо диапазона ячеек в формуле. Псевдовыбор облегчает ввод структурированной ссылки. Это поведение можно включить или отключить, выбрав или снимите флажок Использовать имена таблиц в формулах проверка в диалоговом окне Параметры > файла >формулы >Работа с формулами.
- Использование книг с внешними ссылками на таблицы Excel в других книгах Если книга содержит внешнюю ссылку на таблицу Excel в другой книге, эта связанная исходная книга должна быть открыта в Excel, чтобы избежать ошибок #REF! в целевой книге, содержащей ссылки. Если сначала открыть целевую книгу и #REF! появятся ошибки, они будут устранены при открытии исходной книги. Если сначала открыть книгу с исходным кодом, коды ошибок не будут отображаться.
- Преобразование диапазона в таблицу и таблицы в диапазон. При преобразовании таблицы в диапазон все ссылки на ячейки изменяются на эквивалентные абсолютные ссылки стиля A1. При преобразовании диапазона в таблицу Excel не изменяет автоматически ссылки на ячейки этого диапазона на эквивалентные структурированные ссылки.
- Отключение заголовков столбцов. Вы можете включить и отключить заголовки столбцов таблицы на вкладке Конструктор таблицы >строке заголовков. Если отключить заголовки столбцов таблицы, структурированные ссылки, использующие имена столбцов, не затрагиваются, и вы по-прежнему можете использовать их в формулах. Структурированные ссылки, ссылающиеся непосредственно на заголовки таблицы (например, =DeptSales[[#Headers], [%Commission]]), приведут к #REF.
- Добавление и удаление столбцов и строк в таблице. Так как диапазоны табличных данных часто меняются, ссылки на ячейки для структурированных ссылок настраиваются автоматически. Например, если вы используете имя таблицы для подсчета всех ячеек в ней, и добавляете строку данных, ссылка на ячейки автоматически меняется.
- Переименование таблицы или столбца. Если переименовать столбец или таблицу, в приложении Excel автоматически изменится название этой таблицы или заголовок столбца, используемые во всех структурированных ссылках книги.
- Перемещение, копирование и заполнение структурированных ссылок Все структурированные ссылки остаются неизменными при копировании или перемещении формулы, которая использует структурированную ссылку.
Примечание: Копирование структурированной ссылки и заполнение структурированной ссылки — это не одно и то же. При копировании все структурированные ссылки остаются неизменными, а при заполнении формулы полностью структурированные ссылки настраивают описатели столбцов, как последовательность, как показано в следующей таблице.
Направление заполнения:
И при заполнении нажимаете :
Выполняется действие:
Vba как ссылаться на умные таблицы
Страницы: 1
Команды VBA для работы с умными таблицами
Пользователь
Сообщений: 95 Регистрация: 22.10.2016
06.12.2016 03:56:13
Здравствуйте. В excel есть команда Отформатировать как таблицу, такие таблицы удобно использовать как небольшие базы данных. Дайте пожалуйста направление поиска, не могу найти команды vba для работы с такими таблицами.
Что нужно: доступ к таблице по имени, добавление строк, удаление строк, определение числа строк и столбцов.
Логика подсказывает, что работа с такими таблицами осуществляется специальными командами, но не могу найти в справочнике нужный объект.
Сам excel определяет отформатированный как таблицу диапазон ячеек в качестве одного объекта и имеет о нем как минимум следующие данные: размер таблицы, размер заголовка, имя таблицы, и хотя бы адрес одного из углов таблицы. И эти данные наверняка доступны через vba.
Изменено: zenija2007 — 07.12.2016 03:14:43 ( Уточнение вопроса )
Vba как ссылаться на умные таблицы
Необходимо делать вычисления, ссылаясь на данные в другом файле. В файле «источник» находится Умная таблица.
Обычная формула работает даже тогда, когда файл «источник» закрыт.
=ВПР(E5;’C:\YandexDisk\Личные папки\008\[Таб1.xlsx]Лист1′!$A:$C;2;0)
Но если в формуле идет ссылка на Умную таблицу, то получается ошибка #ССЫЛКА!
=ВПР(E5;’C:\YandexDisk\Личные папки\008\Таб1.xlsx’!ТАБ1[#Данные];2;0)
Если оба файла открыты, то проблем нет.
Подскажите, пожалуйста, как обойти эту ошибку.
Необходимо делать вычисления, ссылаясь на данные в другом файле. В файле «источник» находится Умная таблица.
Обычная формула работает даже тогда, когда файл «источник» закрыт.
=ВПР(E5;’C:\YandexDisk\Личные папки\008\[Таб1.xlsx]Лист1′!$A:$C;2;0)
Но если в формуле идет ссылка на Умную таблицу, то получается ошибка #ССЫЛКА!
=ВПР(E5;’C:\YandexDisk\Личные папки\008\Таб1.xlsx’!ТАБ1[#Данные];2;0)
Если оба файла открыты, то проблем нет.
Подскажите, пожалуйста, как обойти эту ошибку.
Сообщение отредактировал book — Четверг, 24.08.2023, 18:31
Сообщение Добрый день!
Необходимо делать вычисления, ссылаясь на данные в другом файле. В файле «источник» находится Умная таблица.
Обычная формула работает даже тогда, когда файл «источник» закрыт.
=ВПР(E5;’C:\YandexDisk\Личные папки\008\[Таб1.xlsx]Лист1′!$A:$C;2;0)
Но если в формуле идет ссылка на Умную таблицу, то получается ошибка #ССЫЛКА!
=ВПР(E5;’C:\YandexDisk\Личные папки\008\Таб1.xlsx’!ТАБ1[#Данные];2;0)
Если оба файла открыты, то проблем нет.
Подскажите, пожалуйста, как обойти эту ошибку.
Спасибо. Автор — book
Дата добавления — 24.08.2023 в 18:29
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Замечаний: ±
Excel 2016
Цитата book, 24.08.2023 в 18:29, в сообщении № 1 ( )
если в формуле идет ссылка на Умную таблицу, то получается ошибка
Цитата book, 24.08.2023 в 18:29, в сообщении № 1 ( )
как обойти эту ошибку
Цитата book, 24.08.2023 в 18:29, в сообщении № 1 ( )
Обычная формула работает даже тогда, когда файл «источник» закрыт
Цитата book, 24.08.2023 в 18:29, в сообщении № 1 ( )
если в формуле идет ссылка на Умную таблицу, то получается ошибка
Цитата book, 24.08.2023 в 18:29, в сообщении № 1 ( )
как обойти эту ошибку
Цитата book, 24.08.2023 в 18:29, в сообщении № 1 ( )
Обычная формула работает даже тогда, когда файл «источник» закрыт
Цитата book, 24.08.2023 в 18:29, в сообщении № 1 ( )
если в формуле идет ссылка на Умную таблицу, то получается ошибка
Цитата book, 24.08.2023 в 18:29, в сообщении № 1 ( )
как обойти эту ошибку
Цитата book, 24.08.2023 в 18:29, в сообщении № 1 ( )
Обычная формула работает даже тогда, когда файл «источник» закрыт
Автор — Serge_007
Дата добавления — 25.08.2023 в 09:24
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 151
Замечаний: 0% ±
Excel 2016
Serge_007, спасибо за отклик.
Другими словами: никак, файл источник должен быть всегда открыт. Верно понимаю?
PS
Умную таблицу использую для обращения к ней из Power Query (в других файлах), и чтобы избежать в формулах ссылок на открытые диапазоны ячеек типа $A:$A (файл источник пополняется).
Serge_007, спасибо за отклик.
Другими словами: никак, файл источник должен быть всегда открыт. Верно понимаю?
PS
Умную таблицу использую для обращения к ней из Power Query (в других файлах), и чтобы избежать в формулах ссылок на открытые диапазоны ячеек типа $A:$A (файл источник пополняется). book
Сообщение отредактировал book — Пятница, 25.08.2023, 10:52
Сообщение Serge_007, спасибо за отклик.
Другими словами: никак, файл источник должен быть всегда открыт. Верно понимаю?
PS
Умную таблицу использую для обращения к ней из Power Query (в других файлах), и чтобы избежать в формулах ссылок на открытые диапазоны ячеек типа $A:$A (файл источник пополняется). Автор — book
Дата добавления — 25.08.2023 в 10:52
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Замечаний: ±
Excel 2016
Цитата book, 25.08.2023 в 10:52, в сообщении № 3 ( )
Если и существует такой способ (средствами Excel), то я о нем не знаю
Цитата book, 25.08.2023 в 10:52, в сообщении № 3 ( )
Умную таблицу использую для обращения к ней из Power Query
А почему не сводную? Это же удобнее + сводную можно сразу в рабочий файл выводить (не нужны ссылки на закрытую книгу)
Цитата book, 25.08.2023 в 10:52, в сообщении № 3 ( )
Если и существует такой способ (средствами Excel), то я о нем не знаю
Цитата book, 25.08.2023 в 10:52, в сообщении № 3 ( )
Умную таблицу использую для обращения к ней из Power Query
А почему не сводную? Это же удобнее + сводную можно сразу в рабочий файл выводить (не нужны ссылки на закрытую книгу) Serge_007
Цитата book, 25.08.2023 в 10:52, в сообщении № 3 ( )
Если и существует такой способ (средствами Excel), то я о нем не знаю
Цитата book, 25.08.2023 в 10:52, в сообщении № 3 ( )
Умную таблицу использую для обращения к ней из Power Query
А почему не сводную? Это же удобнее + сводную можно сразу в рабочий файл выводить (не нужны ссылки на закрытую книгу) Автор — Serge_007
Дата добавления — 25.08.2023 в 11:03
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 151
Замечаний: 0% ±
Excel 2016
Цитата Serge_007, 25.08.2023 в 11:03, в сообщении № 4 ( )
А почему не сводную?
Сводная, как мне представляется с моего уровня знания Excel, крайне неудобная вещь в качестве именно промежуточного звена для вычислений, отчетов и т.п. В моей конкретной задаче Умная таблица – есть результат обработки Google Tabs через Power Query. Кроме того, там есть еще дополнительное вычисление, которое я не знаю как реализовать на сводной (но это уже, видимо, требует отдельной темы на форуме).
Цитата Serge_007, 25.08.2023 в 11:03, в сообщении № 4 ( )
А почему не сводную?
Сводная, как мне представляется с моего уровня знания Excel, крайне неудобная вещь в качестве именно промежуточного звена для вычислений, отчетов и т.п. В моей конкретной задаче Умная таблица – есть результат обработки Google Tabs через Power Query. Кроме того, там есть еще дополнительное вычисление, которое я не знаю как реализовать на сводной (но это уже, видимо, требует отдельной темы на форуме). book
Цитата Serge_007, 25.08.2023 в 11:03, в сообщении № 4 ( )
А почему не сводную?
Сводная, как мне представляется с моего уровня знания Excel, крайне неудобная вещь в качестве именно промежуточного звена для вычислений, отчетов и т.п. В моей конкретной задаче Умная таблица – есть результат обработки Google Tabs через Power Query. Кроме того, там есть еще дополнительное вычисление, которое я не знаю как реализовать на сводной (но это уже, видимо, требует отдельной темы на форуме). Автор — book
Дата добавления — 25.08.2023 в 11:24
Группа: Админы
Ранг: Местный житель
Сообщений: 19185
Замечаний: ±
Excel 365 & Mac Excel
Цитата book, 25.08.2023 в 11:24, в сообщении № 5 ( )
Умная таблица – есть результат обработки Google Tabs через Power Query
Цитата book, 25.08.2023 в 10:52, в сообщении № 3 ( )
Умную таблицу использую для обращения к ней из Power Query (в других файлах)
PQ в других файлах может обратиться к умной таблице в источнике напрямую. Не совсем понятно, зачем формулы?
И нужна ли эта умная таблица, если она лишь промежуточное звено между двумя запросами PQ?
Без примера, конечно, сложно советовать
Цитата book, 25.08.2023 в 11:24, в сообщении № 5 ( )
Умная таблица – есть результат обработки Google Tabs через Power Query
Цитата book, 25.08.2023 в 10:52, в сообщении № 3 ( )
Умную таблицу использую для обращения к ней из Power Query (в других файлах)
PQ в других файлах может обратиться к умной таблице в источнике напрямую. Не совсем понятно, зачем формулы?
И нужна ли эта умная таблица, если она лишь промежуточное звено между двумя запросами PQ?
Без примера, конечно, сложно советовать Pelena
Цитата book, 25.08.2023 в 11:24, в сообщении № 5 ( )
Умная таблица – есть результат обработки Google Tabs через Power Query
Цитата book, 25.08.2023 в 10:52, в сообщении № 3 ( )
Умную таблицу использую для обращения к ней из Power Query (в других файлах)
PQ в других файлах может обратиться к умной таблице в источнике напрямую. Не совсем понятно, зачем формулы?
И нужна ли эта умная таблица, если она лишь промежуточное звено между двумя запросами PQ?
Без примера, конечно, сложно советовать Автор — Pelena
Дата добавления — 25.08.2023 в 17:06
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 151
Замечаний: 0% ±
Excel 2016
Pelena, знаю, что, задавая вопрос, в большинстве случаев нужно прикладывать файл, но в этот раз, надеюсь, можно обойтись без него, тем более, что для полноценного примера пришлось бы предоставить три файла.
В общем виде, система, которую я выстроил выглядит так:
1) Гугл Таблица – в нее ежедневно заносятся данные по заказам.
2) Файл Excel, который через PQ «забирает» Гугл Таблицу и строит на ее основе несколько отчетов (Умные таблицы).
3) Ежемесячный файл (создается вручную), в который для каждого заказа из Файла No2 подтягиваются данные по формуле СУММЕСЛИМН.
Формула обращается к Умной Таблице, чтобы избежать ссылок типа $A:$A (хотя при количестве строк около 10,000, м.б. это не так критично?) Также я хотел уйти от ссылок на букву столбца или его номер (для ВПР), предполагая, что столбцы могут появляться новые, а их порядок (но не название») меняться.
Pelena, знаю, что, задавая вопрос, в большинстве случаев нужно прикладывать файл, но в этот раз, надеюсь, можно обойтись без него, тем более, что для полноценного примера пришлось бы предоставить три файла.
В общем виде, система, которую я выстроил выглядит так:
1) Гугл Таблица – в нее ежедневно заносятся данные по заказам.
2) Файл Excel, который через PQ «забирает» Гугл Таблицу и строит на ее основе несколько отчетов (Умные таблицы).
3) Ежемесячный файл (создается вручную), в который для каждого заказа из Файла No2 подтягиваются данные по формуле СУММЕСЛИМН.
Формула обращается к Умной Таблице, чтобы избежать ссылок типа $A:$A (хотя при количестве строк около 10,000, м.б. это не так критично?) Также я хотел уйти от ссылок на букву столбца или его номер (для ВПР), предполагая, что столбцы могут появляться новые, а их порядок (но не название») меняться. book
Сообщение отредактировал book — Суббота, 26.08.2023, 10:32
Сообщение Pelena, знаю, что, задавая вопрос, в большинстве случаев нужно прикладывать файл, но в этот раз, надеюсь, можно обойтись без него, тем более, что для полноценного примера пришлось бы предоставить три файла.
В общем виде, система, которую я выстроил выглядит так:
1) Гугл Таблица – в нее ежедневно заносятся данные по заказам.
2) Файл Excel, который через PQ «забирает» Гугл Таблицу и строит на ее основе несколько отчетов (Умные таблицы).
3) Ежемесячный файл (создается вручную), в который для каждого заказа из Файла No2 подтягиваются данные по формуле СУММЕСЛИМН.
Формула обращается к Умной Таблице, чтобы избежать ссылок типа $A:$A (хотя при количестве строк около 10,000, м.б. это не так критично?) Также я хотел уйти от ссылок на букву столбца или его номер (для ВПР), предполагая, что столбцы могут появляться новые, а их порядок (но не название») меняться. Автор — book
Дата добавления — 26.08.2023 в 10:30
Умные таблицы в Excel
Имеем таблицу, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Ну, хотя бы, для примера — вот такого вида: Размер — от нескольких десятков до нескольких сотен тысяч строк — не важен. Задача — всячески упростить и облегчить себе жизнь, превратив эти ячейки в «умную» таблицу.
Решение
- Созданная Таблицаполучает имяТаблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design) . Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
- Созданная один раз Таблицаавтоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:
- =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
- =Таблица1[#Данные] — ссылка только на данные (без строки заголовка)
- =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
- =Таблица1[#Итоги] — ссылка на строку итогов (если она включена)
- =Таблица1[#Эта строка] — ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] — будет ссылаться на значение НДС из текущей строки таблицы.
P.S.
В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы — называлось Списком и создавалось через меню Данные — Список — Создать список (Data — List — Create list) . Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.