Синтаксис таблицы структурированных ссылок Excel
Я стараюсь избегать использования Excel слишком часто, но когда я делаю это, мне нравится использовать структурированные ссылки, так как они кажутся намного чище при написании.
Если я создам таблицу с именем "table1" со столбцами "col1" и "col2", как я буду ссылаться на первую строку в "col1", используя структурированную ссылку в другой таблице? Я пробовал синтаксис =table1[[#this row],[col1]]и просто получаю ошибку. Есть ли такой синтаксис, как =table1[1,1] or =table1[1,[col1]]? Конечно, это тоже не работает, но что эквивалентно?
Это очень раздражает, так как кажется, что это должно быть просто.
6 ответов
Table1[[#This Row][Column1]] работает, но формула должна находиться в той же строке, что и строка таблицы, на которую вы хотите сослаться.
Для ссылки на первую строку в другом месте используйте либо COUNTIFS(criteria_range1, criteria1 [, criteria_rangen, criterian]) Или немного сложнее SUMIFS() если вам нужны числовые значения вместо счетчиков, как указано в Studgeek:
SUMIFS(sum_range1, criteria_range1, criteria1 [, criteria_rangen, criterian])
Вам, конечно, понадобится уникальный критерий строки, по которому можно выбрать строку. Так, например:
Table1
ID Value Name
1 2 Two
2 4 Four
3 8 Eight
SUMIF(Table1[Value], Table1[ID], 2)... возвращает значение 4 (или ноль, если ID=2 не найден). Если ваше значение не является числовым, то вы, очевидно, не можете использовать этот метод.
Тем не менее, Акун почти достиг реального ответа, но он не зашел достаточно далеко в своем объяснении / примере, ИМО.
INDEX(Table1[Name], 2) возвращает "четверку" INDEX(Table1, 1, 1) возвращает 1
Пытаться
=INDEX(col1,1)
Вы даже можете обратиться к ячейкам в 2-мерной таблице, используя
=INDEX(reference,row_num,column_num)
Хитрость в таких случаях заключается в использовании функции Excel OFFSET:
- Доступ к 1-й строке столбца с именем Column1 в той же таблице:
OFFSET([Column1],0,0,1) - Доступ ко 2-му ряду
OFFSET([Column1],1,0,1)
и т.п.
Конечно, вы можете использовать это, чтобы получить доступ к другой таблице и столбцу, просто добавив префикс к имени таблицы. Например OFFSET(Table2[Column3],4,0,1)получит доступ к 4-й строке столбца "Столбец3" из "Таблицы2"
Вместо INDEX я бы предложил SUMIF. Это позволит вам использовать табличные значения, а не явные номера строк (которые могут сломаться, если вы начнете фильтровать или упорядочивать). Например (по следующей ссылке), это суммирует столбец "Сумма" и включает только те строки, где "Тип" равен "Проверка", а "Счет" - "Утилиты": =SUMIFS(Table1[Amount],Table1[Type],“Check”,Table1[Account], “Utilities”)
Смотрите эту ссылку больше информации: http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx
Кажется, не существует явного способа использования структурированных ссылок на определенные строки в таблице. Как говорит Адриан, вы можете использовать INDEX.
Или вы можете использовать неявное пересечение для ссылки на одну и ту же строку: если table1 находится в строке 5:10, а таблица 2 также в строке 5:10, то использование структурированной ссылки с именами столбцов неявно пересекает одну и ту же строку.
Или вы можете ввести структурированную ссылку в виде формулы многострочного массива (выделите несколько ячеек, введите формулу и используйте Ctrl-shift-Enter) в разных строках, и это будет работать.
Было бы хорошо, если бы таблица могла иметь столбец, обозначенный как первичный ключ (который может быть числовым или строковым), и тогда структурированная ссылка могла бы включать способ ссылки на строку по ее первичному ключу.
Это было бы синтаксическим сахаром вокруг VLOOKUP, но таблица могла бы знать, отсортирован ли он по первичному ключу, и выполнять эффективный поиск только в этом случае. Кажется, что VLOOKUP внедряет в него зло, которое находит неправильную строку, если вы зависите от сортировки, особенно когда в таблицах есть удобный способ сортировки строк.