Как использовать именованный столбец в формулах Excel

Я знаю, как сделать именованный диапазон в Excel.

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

Однако формула выглядит так (стоит три строки):

=G2*(10*D2 + 20*E2 + 5*F2)
=G3*(10*D3 + 20*E3 + 5*F3)
=G4*(10*D4 + 20*E4 + 5*F4)

Я хотел бы использовать именованные диапазоны, но не могу найти способ сделать что-то вроде

=Count * (10*var1 + 20*var2 + 5*var3)

где count, var1, var2 и var3 автоматически обновляются, чтобы стать конкретным столбцом текущей строки. Я могу создать именованный диапазон для каждой ячейки, но это бесполезно. Я могу назвать столбец диапазоном, но тогда я не могу найти способ ввести смещение в формулу.

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


excel formula range named
person Jason Coyne    schedule 17.12.2010    source источник

Ответы (9)


arrow_upward
4
arrow_downward

Предположим, у меня есть следующие числа, установленные в столбцах от D до F в строках со 2 по 4:

    D    E    F    G
2   10   15   20
3   1    2    3
4   20   30   40

Теперь предположим, что я хочу, чтобы значение в столбце D было известно как input1, столбец E — как input2, а столбец F — как input3:

В разделе Вставить › Имя › Определить...

input1 RefersTo =OFFSET(Sheet1!$D$2,0,0,COUNT(Sheet1!$D:$D),1)
input2 RefersTo =OFFSET(Sheet1!$E$2,0,0,COUNT(Sheet1!$E:$E),1)
input3 RefersTo =OFFSET(Sheet1!$F$2,0,0,COUNT(Sheet1!$F:$F),1)

Теперь, если я напишу свою формулу в столбце G следующим образом, я должен получить правильные ответы:

G2 =(10*input1+20*input2+30*input3) // 1000
G3 =(10*input1+20*input2+30*input3) // 140
G5 =(10*input1+20*input2+30*input3) // 2000
person Alex P    schedule 17.12.2010
comment
Можно было бы подумать, что после 30 лет разработки Microsoft придумала бы более интуитивный способ решения этой проблемы. - person Jeff Axelrod; 07.11.2012
comment
После 30+4 я не могу найти, как применить этот обходной путь... Как это сделать с помощью Excel 2010? - person Dror; 11.08.2014

arrow_upward
7
arrow_downward

Просто, по крайней мере, при использовании Excel 2010:

  1. назовите свой столбец: выберите полный столбец, введите имя
  2. использовать имя столбца в формуле; Excel объединит указанный столбец с текущей строкой для доступа к одной ячейке.

Используя пример Алекса П :

  1. выберите столбец D, щелкнув заголовок столбца, содержащий D, введите имя input1 в поле имени и нажмите Enter.
  2. повторите для столбцов от E до F, используя input2 и input3 соответственно.
  3. Не определяйте дополнительные имена, определяющие имена input1 [...], как в примере выше!
  4. используйте формулу, как указано в примере выше

Внимание:

Используя таким образом именованные столбцы, вы не сможете получить доступ к любой другой строке, кроме той, в которой находится ваша формула!

По крайней мере, я не знаю о возможности выразить что-то вроде ‹ColName›(row+1)...

person Tillomar    schedule 29.10.2013

arrow_upward
5
arrow_downward

Я бы предложил создать таблицу. Выберите свой диапазон A1:H4, затем перейдите к виджету "Таблицы" > "Создать" > "Вставить таблицу с заголовками" (на Mac). Это пометит A2:H4 как тело таблицы, а A1:H4 как заголовок.

Отсюда вы получаете:

  • Все, что вы поместите в столбец заголовка, автоматически определит имя для этого столбца, например. Count, Radius, Density, Height
  • Затем вы можете написать свою формулу, используя =[@Count]*(10*[@Radius] + 20*[@Density] + 5*[@Height])
  • Когда вы изменяете формулу в ячейке H2, Excel автоматически «скопирует» эту формулу во все ячейки в столбце H. Так что больше никаких случайных несоответствий в формулах.
  • Когда вам нужно добавить еще одну строку, просто щелкните последнюю ячейку (в нашем примере H4) и нажмите Tab. Excel добавляет еще одну строку, а также обязательно «скопирует» вашу формулу в новую строку.
  • Если вам нужна строка итогов, добавьте ее с помощью флажка Строка итогов в виджете Таблицы. Excel автоматически добавляет итоговую строку. Если щелкнуть любую ячейку в итоговой строке, вы можете изменить «формулу итогов» с помощью кнопки «▼▲», например, чтобы вычислить среднее значение вместо суммы столбца.
  • Если у вас есть длинная таблица и прокрутите вниз, чтобы заголовок больше не был виден, Excel автоматически отобразит заголовок столбца вместо имен столбцов (например, Count вместо G).

Я действительно могу порекомендовать видео You Suck at Excel with Joel Spolsky, в котором все это объясняется.

person Benedikt Köppel    schedule 13.10.2016

arrow_upward
2
arrow_downward

Я не полностью просмотрел предыдущие ответы, но я думаю, что это ближе к тому, что искал @Jason Coyne, ОП. Итак, я надеюсь, что получу много голосов. ;-)

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

FWIW, похоже, эта функция доступна с Excel 2007.

Вот скриншот примера:

введите здесь описание изображения

Вы должны увидеть, что формула в E2 — это =[@Count] * (10*[@Var1] + 20*[@Var2] + 5*[@Var3]), что довольно близко к тому, что хотел ввести @jason-coyne.

Мне не нравится, что вас заставляют выбирать стиль (или определять новый, если вы не видите стиль, который вам нравится). Хорошей новостью является то, что вы можете переформатировать ячейки по своему усмотрению, не отменяя «табличную форму».

Настаивает на включении автофильтра. Но автоматический фильтр легко отключить (см. флажок «Кнопка фильтра» в меню «Дизайн инструментов для таблиц»).

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

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

Если вы хотите загрузить образец рабочей книги на скриншоте, вот ссылка: https://filebin.ca/3vfaSDn4NLEA/SampleWorkbook.xlsx

person Mark Moore    schedule 21.03.2018
comment
Избегайте включения внешних ссылок, просто включите набор данных прямо сюда. - person Adriano Martins; 21.03.2018
comment
@ adriano-martins, я не понимаю, как добавить набор данных непосредственно в ответ. Не могли бы вы добавить комментарий или ссылку, чтобы объяснить, как это сделать? FWIW, мне действительно нужно было включить файл XLSX, потому что ключом к реализации того, о чем просил OP, является форматирование таблицы. Поскольку я не уверен, что означает непосредственно присоединенный набор данных (пока), я предполагаю, что он не включает форматирование ячеек. С нетерпением жду возможности узнать, как добавить набор данных непосредственно в ответ. - person Mark Moore; 22.03.2018

arrow_upward
1
arrow_downward

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

Cells(12, Range("MyCol").Column)
person Dror    schedule 11.08.2014

arrow_upward
1
arrow_downward

Добавление к ответу Alex P:

Instead of using 

=OFFSET(Sheet1!$D$2,0,0,COUNT(Sheet1!$D:$D),1)
as the formula for input1, I recommend to use 
=Sheet1!$D$2:INDEX(Sheet1!$D:$D,COUNT(Sheet1"$D:$D))

Он дает тот же результат, но является энергонезависимым, т. е. пересчитывается только при изменении предшествующей ячейки. Это намного лучше в более крупной модели!

person Peter Albert    schedule 16.02.2013

arrow_upward
0
arrow_downward

Возможно, вы сможете использовать функцию row(). Это возвращает текущую строку, в которой вы находитесь. Поэтому в зависимости от макета электронной таблицы вы можете использовать ее следующим образом:

=offset(NamedColumn1, row()-1)

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

person Alex Andronov    schedule 18.12.2010

arrow_upward
0
arrow_downward

Используйте функцию Excel под названием именные ссылки.

Чтобы назвать ячейку или диапазон ячеек

  1. выберите эту ячейку или диапазон ячеек
  2. Введите его имя в поле имени (слева от виджета формулы и имеет имя ячейки)

Вы не можете использовать имена, конфликтующие с именами ячеек, например k0.

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

=pi*radius*radius
person Tim Child    schedule 16.02.2013
comment
Ссылка не работает (К сожалению, страница, которую вы ищете, не может быть найдена.). - person Peter Mortensen; 14.12.2020

arrow_upward
-1
arrow_downward

Я хотел бы предложить небольшое изменение ссылки на ячейку сделано Дрором. Это также будет работать:

Range("MyCol").Rows(12)
person user6453973    schedule 11.06.2016