Содержание

Как сделать скобку в excel?

Как поставить фигурные скобки в excel

Редактирование формул массива в Excel

​Смотрите также​ вида «5 час.,​ B3:B21, проверяя, совпадают​ изменить формулу в​ и функции​выделяем диапазон​ группу. Массивы бывают​6​ в Excel в​ массива и внести​ раз.​ (=) и могут​ клавиш Ctrl +​

Правила редактирования формул массива

​ следующие статьи:​Внесите необходимые корректировки в​Выделить группу ячеек​ в том числе​В прошлых уроках мы​ 30 мин.» в​ ли они с​ строке формул (или​​ТРАНСП (TRANSPOSE)​​C2:C5​

​одномерные​2250​ ячейке A1. Обязательно​ изменения в формуле​Для вычисления нескольких значений​ содержать встроенные функции​ Shift + Ввод,​Знакомство с формулами массива​ формулу:​

  1. ​. Установите переключатель на​ строки и столбцы,​ разобрали основные понятия​ число минут. Вопрос:​ заданными значениями из​
  2. ​ нажав​это делается на​и закрываем скобку​(элементы массива образуют​
  3. ​Купе​ выберите диапазон ячеек​ массива и добавлять​ с помощью формулы​
  4. ​ Excel.​ чтобы ввести их. ​ в Excel​А затем нажмите комбинацию​
  5. ​ пункт текущий массив​ в диапазон массива.​ и сведения касаемо​ что значат фигурные​​ ячеек G4 и​​F2​

​ раз.​ функции СУММ -​ строку или столбец)​8​ E2: E11, введите​ фигурные скобки необходимо​ массива необходимо ввести​Например эта формула вычисляет​ Формулы массива можно​Многоячеечные формулы массива в​

Выделение массива в Excel

​ клавиш​ и нажмите​Нельзя использовать многоячеечные формулы​ массивов в Excel.​ скобки в середине​ G5. Если совпадения​). Затем необходимо повторить​Допустим, имеем двумерный массив​ в итоге должно​

  1. ​ или​2000​ формулу​ нажать сочетание клавиш​ массив в диапазон​ суммарное значение массив​
  2. ​ использовать для выполнения​​ Excel​​Ctrl+Shift+Enter​ОК​ массива в таблицах,​ ​ В этом уроке​ формулы и как​ нет, то результат​​ ввод измененной формулы​​ ячеек, который хотим​​ получиться так:​​двумерные​

​Создание формулы массива с​​= C2: C11 *​​ Ctrl + Shift​ ячеек, состоящий из​ котировок акций и​​ пятизначные невозможно, такие​​Одноячеечные формулы массива в​

​, чтобы сохранить изменения.​

  • ​.​​ созданных с помощью​​ мы продолжим изучение​ ими правильно пользоваться?​ равенства ноль, если​
  • Как удалить формулу массива

    ​ массива, нажав сочетание​ транспонировать.​чтобы Excel воспринял нашу​(матрица). Легко сообразить,​ несколькими ячейками​ D2: D11​ + Ввод еще​ того же числа​​ поместит результат в​​ как​

    Как отредактировать формулу массива

    ​ Excel​ Формула будет отредактирована.​Текущий массив будет выделен:​ команды​ формул массива, но​ Ссылки приветствуются​ совпадение есть, то​ клавиш​Выделяем диапазон ячеек для​

    ​ формулу как формулу​ что почти в​

    1. ​В образце книги выберите​, и нажмите клавиши​ раз.​ строк или столбцов,​ ячейке рядом с​
    2. ​вычислять число символов в​Массивы констант в Excel​Очень часто возникает необходимость​При помощи комбинации клавиш​Таблица​​ с большим уклоном​​Полосатый жираф алик​ единица. Таким образом​Ctrl + Shift +​
    3. ​ размещения транспонированной таблицы.​ массива жмем не​
    4. ​ любой таблице Excel​ ячейки от E2​​ Ctrl + Shift​​Если необходимо включить новые​ что и аргументы​

    Изменение размеров формулы массива

    ​ кнопкой «Общее значение».​ диапазоне ячеек;​Применение формул массива в​ уменьшить или увеличить​Ctrl+/​.​ на их практическое​: Это массивные константы.​ суммы всех сделок,​ Enter.​

    ​ Поскольку исходный массив​ Enter, как обычно,​ при желании можно​ до E11. В​ + Ввод, чтобы​ данные в формуле​ массива.​Эта формула сначала умножает​суммировать только те числа,​

    ​ Excel​ количество ячеек в​. Для этого выберите​Как видите, все перечисленные​ применение. Итак, как​ То есть, в​

    ​ где заказчик не​Excel также не позволит​ ячеек был 8​ а​ найти один или​

    ​ них будут помещены​ упростить формулы массива.​ массива, ознакомьтесь со​Выделите диапазон ячеек, в​ количество акций (ячейки​ которые отвечают определенным​Подходы к редактированию формул​ формуле массива. Скажу​ любую ячейку массива​ выше правила подчеркивают,​ же изменить уже​

    • ​ формулу подставляется первая​ ANTON и товар​
    • ​ свободно перемещать ячейки,​ строк на 2​
    • ​Ctrl + Shift +​ несколько таких массивов:​
    • ​ результаты.​
    • ​Продавец​ статьей развернуть формулу​
    • ​ который нужно ввести​ B2–I2) на их​

    ​ условиям, например наименьшие​ массива в Excel​
    ​ сразу, что дело​

    ​ и нажмите комбинацию.​

    Создание формулы массива

    ​ что массив –​​ существующую формулу массива​ константа, а потом​ не Boston Crab​ входящие в формулу​ столбца, то надо​ Enter​Формулы массива​Перед вводом формулы всегда​Тип автомобиля​ массива. Вы также​ формулу массива.​ курс (ячейки B3–I3),​ значения в диапазоне​Урок подготовлен для Вас​ это не простое​Самое простое, что Вы​ это одно целое.​ в Excel?​ вторая.​ Meat умножаются на​ массива или добавлять​

    ​ выделить диапазон пустых​Вуаля!​в Excel -​ следует выбирать ячейки​Число проданных единиц​ можете попробовать:​Введите необходимую формулу.​ а затем складывает​ чисел, определенном верхней​ командой сайта office-guru.ru​ и в большинстве​ можете сделать с​ Если не выполнить,​Когда формула массива помещена​Например, из «​ ноль и суммируются​

    ​ новые строки-столбцы-ячейки в​ ячеек размером 2​

    ​Т.е. Excel произвел попарное​ это специальные формулы​ для результатов.​Цена за единицу​Правила изменения формул массива​В формулах массива используется​

    ​ результаты, получая общую​​ и нижней границами;​​Автор: Антон Андронов​ случаев будет проще​

    ​ массивом в Excel​ хотя бы одно​ в одну ячейку,​ «&A1&<"00 ч";"00 м">​ только нужные заказы.​ диапазон формулы массива​ строки на 8​ умножение элементов массивов​ для обработки данных​»Всегда» значит в 100​Итоги продаж​ (это может быть​ синтаксис обычных формул.​ сумму 53 184.​суммировать все​

    Создание формулы массива для вычисления одного результата

    ​Автор: Антон Андронов​ удалить старый массив​ – это удалить​ из вышеперечисленных правил,​ то ее редактирование​

    ​ B2:B5 и C2:C5​ из таких массивов.​ процентах случаев.​Ильина​ кропотливой работой)​ Они все начинаются​ В этом примере​

    ​n​Примечание:​ и создать новый.​ его. Для этого​ Excel не даст​ в Excel обычно​

    ​ час., 30 мин.​ объединяющую скобку?​ A10:H11 в нашем​вводим функцию транспонирования =ТРАНСП(​ и образовал новый​ Формулы массива делятся​Введите следующую формулу. Для​Седан​Удаление формулы массива (можно​ со знака равенства​ используется формула массива,​

    ​-е значения в диапазоне​

    ​Мы стараемся как​Прежде чем удалять старый​ достаточно выделить нужный​​ отредактировать массив и​​ не представляет особой​

    ​ 00 ч»;» 5​​Bagira​ случае)​ ​ массив стоимостей (в​ на две категории​ этого достаточно просто​5​ также нужно нажимать​ (=) и могут​ расположенная только в​ значений.​​ можно оперативнее обеспечивать​​ массив, скопируйте его​ массив и нажать​ выдаст следующее предупреждение:​ сложности. Здесь главное​ час., 30 мин.​: я так поняла,​Вспомните детство, школу, свою​в качестве аргумента функции​ памяти компьютера), а​

    Создание формулы массива для вычисления нескольких результатов

    ​ — те, что​ начать ввод (со​2200​ клавиши Ctrl +​ содержать встроенные функции​ одной ячейке.​В программе Excel существует​ вас актуальными справочными​ формулу как текст,​

    ​ клавишу​Если необходимо изменить формулу​ не забыть закончить​

    ​ что тебе надо​ тетрадку по математике. ​ выделяем наш массив​ затем сложил все​ возвращают одно значение​ знака равенства), и​=C2:C11*D2:D11​

    ​ Shift + Ввод)​ Excel.​Нажмите клавиши CTRL+SHIFT+ВВОД.​ два типа формул​ материалами на вашем​ а затем используйте​

    ​ массива, то первое,​ редактирование комбинацией клавиш​* <60;1>означает, что​​ решить систему уравнений.​​ На обороте тетради​

    ​ ячеек A1:B8​​ элементы этого нового​ и те, что​ формула появится в​Купе​Использование констант массива в​В приведенном ниже примере​Формула автоматически вставляется между​ массива: массива формулы​ языке. Эта страница​ ее в новом​.​​ что нужно сделать​​Ctrl+Shift+Enter​ первый из результатов​ этот символ так​ на обложке было​жмем​ массива.​ дают на выходе​ последней выбранной ячейке.​4​

    ​ формулах массива (они​ формулы однотипных общие​ открывающей и закрывающей​ для вычислений нескольких​ переведена автоматически, поэтому​ массиве. При громоздких​

    ​На рисунке ниже представлена​ – это выделить​.​

    ​ умножается на 60,​ механически не используются​ что? Таблица умножения​Ctrl + Shift +​

    ​Обратите внимание на фигурные​ целый набор (массив)​ Вы также можете​1800​

    ​ могут оказаться очень​ файлы по цене​ фигурными скобками (​

    Попробуйте попрактиковаться

    ​ для создания одного​ ее текст может​ формулах такой подход​ формула массива, которая​ диапазон, в котором​Если же формула многоячеечная,​

    ​ а второй -​ в решении, тут​ вот такого вида:​ Enter​ скобки, появившиеся в​ значений. Рассмотрим их​ ввести ее строке​Егоров​ полезными)​ каждого столбца, формула​< >​

    ​ значения и формулы​ содержать неточности и​ позволит сэкономить уйму​ складывает значения двух​ содержится массив. В​ т.е. возвращает массив,​ на 1. СУММ​​ иной подход.​При помощи формул массива​​и получаем «перевернутый​ формуле — отличительный​ на простых примерах. ​ формул:​

    Excel. Введение в формулы массива

    Ранее я уже посвятил несколько заметок использованию формул массива:

    Полученные комментарии подсказали мне, что пользователи не часто используют формулы массива. Более того мало кто знаком с ними.

    Предлагаю вашему вниманию посвященный массивам фрагмент книги Джона Уокенбаха. Подробное руководство по созданию формул в Excel 2002. – М.: Издательский дом «Вильямс», 2002. – 624 с.

    Скачать заметку в формате Word, примеры в формате Excel

    В Excel массивы могут быть одномерными (их еще называют векторами) или двумерными (матрицы). Размерность массива зависит от количества строк и столбцов в нем. Например, одномерный массив может храниться в диапазоне, состоящем из одной строки (горизонтальный массив) или столбце (вертикальный массив). Двумерный массив хранится в прямоугольном диапазоне ячеек. Excel (в отличие от языка программирования VBA) не поддерживает трехмерные массивы.

    Однако, как будет показано далее, массивы вовсе не обязательно хранить в ячейках. Можно производить операции над массивами, которые существуют только в памяти Excel. Можно применять формулы массива для выполнения операций и получения результатов вычислений. Сама формула массива может находиться во многих ячейках или в одной единственной. Существует два вида формул массива: формула массива, которая возвращает несколько значений, и другая, которая возвращает только одно значение.

    Формула массива, возвращающая несколько значений

    Рис. 1 демонстрирует рабочий лист, на котором производится расчет объема продаж товаров. Для этого вычисляют значение в столбце D (объем продаж каждого товара), используя формулу =В2*С2, а затем копируют ее в расположенные ниже ячейки.

    Рис. 1. Диапазон D2:D6 содержит традиционные формулы произведения (в ячейках D2 и D3 различные формулы, хотя и похожие)

    После копирования этой формулы рабочий лист содержит пять формул в столбце D. Альтернативный путь вычислений – использование единой формулы (формулы массива) для вычисления всех пяти значений в диапазоне D2:D6. Эта единственная формула занимает пять ячеек и возвращает массив из пяти значений. Для создания единой формулы массива нужно произвести следующие операции:

    1. Выделить диапазон, в котором будет сохраняться результат вычислений. В данном случае это диапазон D2:D6.
    2. Ввести формулу: =В2:В6*С2:С6
    3. Обычно после введения формулы нажимается клавиша , но только не в случае формулы массива. Так как введенная формула является формулой массива, нажимается комбинация клавиш .

    Формула добавляется во все пять выделенных ячеек (рис. 2), при этом в строке формул появится следующее выражение:

    Рис. 2. Диапазон D2:D6 содержит формулу массива (в ячейках D2 и D3 одна и та же формула)

    Excel заключил формулу в фигурные скобки. Это говорит о том, что данная формула является формулой массива.

    Эта формула производит вычисления и возвращает массив из пяти элементов. В данном случае формула массива производит операции над двумя другими массивами, оба из которых хранятся в диапазонах ячеек. Значения первого массива хранятся в диапазоне В2:В6, а второго — в С2:С6. Для отображения итогового массива требуется пять ячеек, потому что в одной ячейке нельзя отобразить более чем одно значение. Этим объясняется, зачем нужно выделять пять ячеек перед введением формулы.

    Читать еще:  Как сделать структуру сайта в excel?

    Конечно, эта формула массива возвращает точно такие же значения, что и пять формул, введенных в соответствующие ячейки диапазона D2:D6

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

    • Легко убедиться, что все формулы в диапазоне идентичны.
    • Использование формул массива, возвращающих сразу несколько значений, избавляет от случайных изменений в формуле. Нельзя просто изменить одну ячейку в формуле массива.
    • Использование формул массива почти всегда предотвращает вмешательство начинающих пользователей, которые хотят внести свои изменения в формулы.

    Формула массива, возвращающая одно значение

    Обратимся к рис. 3. Следующая формула массива размещается в одной ячейке – D8:

    Рис. 3. Сумма произведений в виде формулы массива

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

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

    Эта формула оперирует значениями, находящимися в двух массивах. Оба массива хранятся в ячейках. Первый – в диапазоне ячеек В2:В6, второй – C2:С6. Формула перемножает соответствующие значения в этих двух массивах и создает новый массив (который существует только в памяти компьютера). Функция СУММ выполняет операцию над этим новым массивом и возвращает значение суммы его элементов.

    Создание массива констант

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

    Для создания массива констант нужно список его элементов заключить в фигурные скобки. Вот пример горизонтального массива констант, состоящего из пяти элементов: <1;0;1;0;1>. Для создания вертикального массива констант используйте в качестве разделителя двоеточие.

    Формула =СУММ(<1;0;1;0;1>) использует функцию СУММ, аргументом которой является массив констант. Формула возвращает значение суммы элементов массива, равное 3. Отметим, что эта формула использует массив в качестве аргумента, но при этом не является формулой массива. Поэтому не нужно использовать клавиши для ввода формулы.

    В случае непосредственного задания массива (как показано выше) необходимо заключать элементы массива в фигурные скобки. С другой стороны, при вводе формулы массива фигурные скобки не ставятся.

    Пока, возможно, трудно заметить, какие-либо преимущества использования массивов констант. Действительно, формула =СУММ(1;0;1;0;1) возвращает точно такое же значение, как и =СУММ(<1;0;1;0;1>).

    Очевидные преимущества становятся ясны при дальнейшем рассмотрении. Следующая формула использует два массива констант: =СУММ(<1;2;3;4>*<5;6;7;8>). Эта формула создает в памяти новый массив, который состоит из произведения соответствующих элементов двух других массивов: <5;12;21;32>. Созданный массив используется впоследствии в качестве аргумента для функции СУММ, которая возвращает результат вычислений (70). Формула эквивалентна следующей формуле, которая не использует массивы: =СУММ(1*5;2*6;3*7;4*8)

    Можно составлять формулы массивов для разных типов массивов. Например, можно составить формулу, в которой будет одновременно использоваться массив констант и массив, элементы которого хранятся в диапазоне ячеек (рис. 4). Для подсчета суммы значений, которые являются произведением элементов массива в диапазоне Al:D1 и соответствующих элементов массива констант, применяется следующая формула: =<СУММ((A1:D1*<1;2;3;4>)>. Эта формула эквивалентна следующей: =СУММ(А1*1;В1*2;С1*3;D1*4)

    Рис. 4. Сумма произведений массива рабочего листа и массива констант

    Элементы массива констант

    Массивы констант могут состоять из чисел, текста, логических значений (например, ИСТИНА, ЛОЖЬ) и даже значений ошибок, таких как #Н/Д. Числа в массиве могут быть целыми, с десятичной точкой или экспоненциальными. Текст в массиве констант заключается в двойные кавычки (например, » Вторник » ). Массив констант может состоять из элементов разного типа, например:

    Массивы констант не могут включать в себя формулы, функции или другие массивы. Числовые значения не могут включать символы доллара, точки с запятой (точки), круглые скобки или символ процента. Например, неверный массив констант:

    Понятие размерности массива

    Массивы могут быть одномерными или двумерными. Ориентация одномерного массива может быть горизонтальной или вертикальной.

    Одномерные горизонтальные массивы

    Элементы в одномерном горизонтальном массиве разделяются точкой с запятой (запятыми в английской версии программы). Ниже приведен пример одномерного горизонтального массива констант:

    Для отображения этого массива в диапазоне необходимо пять последовательных ячеек в строке. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из одной строки и пяти столбцов. Затем введите: = <1;2;3;4;5>и нажмите (рис. 5).

    Рис. 5. Одномерный горизонтальный массив.

    Обратите внимание, внутренние фигурные скобки обозначают массив констант; внешние фигурные скобки – формулу массива. Если ввести этот массив в горизонтальный диапазон, состоящий более чем из пяти ячеек, то лишние ячейки будут содержать #Н/Д (что указывает на недопустимые значения). Если ввести этот массив в вертикальный диапазон ячеек, то в каждой ячейке диапазона появится первый элемент (1) массива.

    Ниже приведен пример другого горизонтального массива. Он состоит из семи элементов и создает текстовую последовательность: < " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " ; " Пятница " ; " Суббота " ; " Воскресенье " >. Для ввода этого массива выделите семь элементов в строке и введите следующий текст: =< " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " , " Пятница " ; " Суббота " ; " Воскресенье " >. Нажмите .

    Одномерные вертикальные массивы

    Элементы в одномерном вертикальном массиве разделяются двоеточием (точкой с запятой в английской версии программы). Ниже приведен пример вертикального массива констант, состоящего из шести элементов: <10:20:30:40:50:60>. Для отображения этого массива в диапазоне необходимо шесть ячеек в столбце. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из шести строк и одного столбца. Затем введите: = <10:20:30:40:50:60>и нажмите .

    Ниже приведен пример другого вертикального массива, состоящего из четырех элементов:

    Двумерные массивы

    В двумерных массивах используются точки с запятыми (запятые в английской версии программы) для разделения горизонтальных элементов и двоеточия (точки с запятой в английской версии программы) для разделения вертикальных элементов. Ниже приведен пример массива констант размерностью 3×4: <1;2;3;4:5;6;7;8:9;10;11;12>. Для отображения этого массива в диапазоне требуется двенадцать ячеек. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из трех строк и четырех столбцов. Затем введите следующую формулу = <1;2;3;4:5;6;7;8:9;10;11;12>после чего нажмите (рис. 6).

    Рис. 6. Двумерный массив

    Если ввести массив в диапазон, состоящий из большего числа ячеек, Ехсеl отобразит #Н/Д в лишних ячейках (рис. 7).

    Рис. 7. Показано, что получится, если ввести массив 3×4 в диапазон ячеек 10×5.

    Каждая строчка двумерного массива должна содержать одинаковое количество элементов. Например, представленный ниже массив недопустимый, потому что его третья строка состоит только из трех элементов; <1;2;3;4:5;6;7;8:9;10;11>. Excel не даст возможности ввести формулу, в которой присутствует недопустимый формат массива.

    Присвоение имени массивам констант

    Excel позволяет создать массив констант, присвоить ему имя и затем использовать это имя в формулах. Фактически присвоение имени массиву заключается в присвоении имени формуле (рис. 8). Пройдите по меню: Формулы – Диспетчер имен.

    Рис. 8. Показано, как присвоить имя массиву, используя диалоговое окно Создание имени.

    Массиву присвоено имя ДеньНедели, которое соответствует следующему массиву констант: < " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " ; " Пятница " ; " Суббота " ; " Воскресенье " >. Отметим, что в диалоговом окне Создание имени для задания массива необходимо использовать знак равенства (=) перед массивом констант. В противном случае массив будет воспринят как текстовая строка. Также необходимо самостоятельно взять элементы массива в фигурные скобки. Excel в этом случае не вводит скобки автоматически. После присвоения имени массиву, его имя можно использовать в формулах (рис. 9).

    Рис. 9. Ввод поименованного массива в диапазон Al:G1

    Так как элементы массива разделяют точки с запятой, ориентация массива будет горизонтальной. Для создания вертикальных массивов используются двоеточия. Также можно воспользоваться функцией транспонирования для того, что бы вставить горизонтальный массив в вертикальный диапазон (рис. 10). Для этого введите в диапазон семи вертикальных ячеек A1:A7 формулу массива:

    Рис. 10. Ввод горизонтального массива в вертикальный диапазон

    При использовании функции Excel ИНДЕКС можно получить доступ к отдельному элементу массива. Например, ниже приведенная формула возвращает значение «Четверг», четвертого элемента массива ДеньНедели: =ИНДЕКС(ДеньНедели;4)

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

    Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

    Прямая ссылка на ячейки

    Имена таблицы и столбцов в Excel

    Сочетание имен таблицы и столбцов называется структурированной ссылкой. Имена в таких ссылках корректируются при добавлении данных в таблицу и удалении их из нее.

    Структурированные ссылки также появляются, когда вы создаете формулу вне таблицы Excel, которая ссылается на данные таблицы. Ссылки могут упростить поиск таблиц в крупной книге.

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

    Sales (продажи ) Пользователь

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

    Чтобы создать таблицу, выделите любую ячейку в диапазоне данных и нажмите клавиши CTRL + T.

    Убедитесь в том, что флажок таблица с заголовками установлен, и нажмите кнопку ОК.

    В ячейке E2 введите знак равенства (=), а затем щелкните ячейку C2.

    В строке формул после знака равенства появится структурированная ссылка [@[ОбъемПродаж]].

    Введите звездочку (*) сразу после закрывающей скобки и щелкните ячейку D2.

    В строке формул после звездочки появится структурированная ссылка [@[ПроцентКомиссии]].

    Нажмите клавишу ВВОД.

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

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

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

    На листе примера щелкните ячейку E2.

    В строке формул введите = C2 * D2 и нажмите клавишу Ввод.

    Обратите внимание на то, что хотя Excel копирует формулу вниз по столбцу, структурированные ссылки не используются. Если, например, вы добавите столбец между столбцами C и D, вам придется исправлять формулу.

    Как изменить имя таблицы?

    При создании таблицы Excel ей назначается имя по умолчанию («Таблица1», «Таблица2» и т. д.), но его можно изменить, чтобы сделать более осмысленным.

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

    Введите нужное имя в поле имя таблицы и нажмите клавишу Ввод.

    В этом примере мы используем имя ОтделПродаж.

    При выборе имени таблицы соблюдайте такие правила:

    Использование допустимых символов Имя должно начинаться с буквы, знака подчеркивания (_) или обратной косой черты (). Используйте буквы, цифры, точки и знаки подчеркивания для остального имени. Вы не можете использовать «C», «c», «R» или «r» для имени, поскольку они уже назначены как сочетание для выбора столбца или строки активной ячейки при их вводе в поле » имя » или » Перейти «.

    Не используйте ссылки на ячейки Имена не могут совпадать с ссылками на ячейки, например Z $100 или R1C1.

    Не используйте пробелы для разделения слов В имени нельзя использовать пробелы. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (.). Например, ОтделПродаж, Салес_такс или First. Quarter.

    Используйте не более 255 знаков. Имя таблицы может содержать не более 255 знаков.

    Присваивайте таблицам уникальные имена. Повторяющиеся имена запрещены. Excel не делает различий между символами верхнего и нижнего регистра в именах. Например, если в книге уже есть таблица «ПРОДАЖИ», то при попытке присвоить другой таблице имя «Продажи» вам будет предложено выбрать уникальное имя.

    Читать еще:  Как сделать сортировку в excel 2003?

    Использование идентификатора объекта Если вы планируете использовать смешанные таблицы, сводные таблицы и диаграммы, рекомендуется присвоить имена типу объекта. Например: Тбл_салес для таблицы продаж, Пт_салес для сводной таблицы продаж и Чрт_салес для диаграммы продаж или Птчрт_салес для сводной диаграммы продаж. Это позволит сохранять все имена в упорядоченном списке в диспетчере имен.

    Правила синтаксиса структурированных ссылок

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

    В этой формуле используются указанные ниже компоненты структурированной ссылки.

    Имя таблицы: ОтделПродаж — это имя пользовательской таблицы. Он ссылается на табличные данные без заголовков или строк итогов. Вы можете использовать имя таблицы по умолчанию, например Таблица1, или изменить его, чтобы использовать другое имя.

    Указатель столбца: [Сумма продаж]а[Сумма комиссии] — это описатели столбцов, которые используют имена столбцов, которые они представляют. Они ссылаются на данные столбца без заголовка столбца или строки итогов. Всегда заключайте спецификаторы в квадратные скобки, как показано ниже.

    Указатель элемента: [#Totals] и [#Data] — это указатели специальных элементов, которые указывают на определенные части таблицы, такие как строка итогов.

    Указатель таблицы. [[#Итого],[ОбъемПродаж]] и [[#Данные],[ОбъемКомиссии]] — это указатели таблицы, которые представляют внешние части структурированной ссылки. Внешняя часть следует за именем таблицы и заключается в квадратные скобки.

    Структурированная ссылка: (ОтделПродаж [[#Totals]; [сумма продаж]] и отделпродаж [[#Data]; [сумма комиссии]] — структурированные ссылки, представленные в виде строки, начинающейся с имени таблицы и заканчивающейся указателем столбца.

    При создании или изменении структурированных ссылок вручную учитывайте перечисленные ниже правила синтаксиса.

    Использование квадратных скобок для указателей Все описатели таблиц, столбцов и специальных элементов должны быть заключены в парные квадратные скобки ([]). Спецификатор, который содержит другие описатели, требует наличия внешних парных скобок для заключения внутренних парных скобок других описателей. Пример: = ОтделПродаж [[продавец]: [регион]]

    Все заголовки столбцов — это текстовые строки. Но при использовании в структурированной ссылке их не нужно заключать в кавычки. Числа или даты, например 2014 или 01.01.2014, также считаются текстовыми строками. Нельзя использовать выражения с заголовками столбцов. Например, выражение ОтделПродажСводкаФГ[[2014]:[2012]] недопустимо.

    Заключайте в квадратные скобки заголовки столбцов, содержащие специальные знаки. Если присутствуют специальные знаки, весь заголовок столбца должен быть заключен в скобки, а это означает, что для указателя столбца потребуются двойные скобки. Пример: =ОтделПродажСводкаФГ[[Итого $]]

    Дополнительные скобки в формуле нужны при наличии таких специальных знаков:

    левая квадратная скобка ([);

    правая квадратная скобка (]);

    левая фигурная скобка (<);

    правая фигурная скобка (>);

    Операторы ссылок

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

    Эта структурированная ссылка:

    Все ячейки в двух или более смежных столбцах

    : (двоеточие) — оператор ссылки

    Сочетание двух или более столбцов

    , (запятая) — оператор объединения

    Пересечение двух или более столбцов

    (пробел) — оператор пересечения

    Указатели специальных элементов

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

    Этот указатель специального элемента:

    Вся таблица, включая заголовки столбцов, данные и итоги (если они есть).

    Только строки данных.

    Только строка заголовка.

    Только строка итога. Если ее нет, будет возвращено значение null.

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

    Excel автоматически заменяет указатели «#Эта строка» более короткими указателями @ в таблицах, содержащих больше одной строки данных. Но если в таблице только одна строка, Excel не заменяет указатель «#Эта строка», и это может привести к тому, что при добавлении строк вычисления будут возвращать непредвиденные результаты. Чтобы избежать таких проблем при вычислениях, добавьте в таблицу несколько строк, прежде чем использовать формулы со структурированными ссылками.

    Определение структурированных ссылок в вычисляемых столбцах

    Когда вы создаете вычисляемый столбец, для формулы часто используется структурированная ссылка. Она может быть неопределенной или полностью определенной. Например, чтобы создать вычисляемый столбец «ОбъемКомиссии», который вычисляет объем комиссии в рублях, вы можете использовать следующие формулы:

    Тип структурированной ссылки

    Перемножает соответствующие значения из текущей строки.

    Перемножает соответствующие значения из каждой строки обоих столбцов.

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

    Примеры использования структурированных ссылок

    Ниже приведены примеры использования структурированных ссылок.

    Эта структурированная ссылка:

    Все ячейки в столбце «ОбъемПродаж».

    Заголовок столбца «ПроцентКомиссии».

    Итог столбца «Регион». Если нет строки итогов, будет возвращено значение ноль.

    Все ячейки в столбцах «ОбъемПродаж» и «ПроцентКомиссии».

    Только данные в столбцах «ПроцентКомиссии» и «ОбъемКомиссии».

    Только заголовки столбцов от «Регион» до «ОбъемКомиссии».

    Итоги столбцов от «ОбъемПродаж» до «ОбъемКомиссии». Если нет строки итогов, будет возвращено значение null.

    Только заголовок и данные столбца «ПроцентКомиссии».

    =ОтделПродаж[[#Эта строка], [ОбъемКомиссии]]

    Ячейка на пересечении текущей строки и столбца сумма комиссии. При использовании в той же строке, что и строка заголовка или итог, возвращается ошибка #VALUE! .

    Если ввести длинную форму этой структурированной ссылки (#Эта строка) в таблице с несколькими строками данных, Excel автоматически заменит ее укороченной формой (со знаком @). Две эти формы идентичны.

    E5 (если текущая строка — 5)

    Методы работы со структурированными ссылками

    При работе со структурированными ссылками рекомендуется обращать внимание на перечисленные ниже аспекты.

    Использование автозавершения формул. Автозавершение формул может пригодиться при вводе структурированных ссылок для соблюдения правил синтаксиса. Дополнительные сведения см. в статье Использование автозавершения формул.

    Определение способа формирования структурированных ссылок для таблиц в частично выделенном фрагменте По умолчанию при создании формулы щелчок по диапазону ячеек в таблице частично выделяет ячейки и автоматически вводит в формулу структурированную ссылку, а не диапазон ячеек. Это поведение значительно упрощает ввод структурированной ссылки. Вы можете включить или отключить это поведение, установив или сняв флажок использовать имена таблиц в формулах в _гт_ файл _гт_ формулы _гт_ Работа с формулами.

    Использование книг с внешними ссылками на таблицы Excel в других книгах Если книга содержит внешнюю ссылку на таблицу Excel в другой книге, эта связанная исходная книга должна быть открыта в Excel, чтобы избежать #REF . в конечной книге, содержащей ссылки. Если сначала открыть конечную книгу, а #REF! ошибки, они будут устранены, если открыть исходную книгу. Если сначала открыть исходную книгу, вам не будут отображаться коды ошибок.

    Преобразование диапазона в таблицу и таблицы в диапазон При преобразовании таблицы в диапазон все ссылки на ячейки изменяются на эквивалентные абсолютные ссылки на стиль a1. При преобразовании диапазона в таблицу Excel не автоматически изменяет ссылки на ячейки этого диапазона на эквивалентные структурированные ссылки.

    Отключение заголовков столбцов Вы можете включать и выключать заголовки столбцов таблицы с помощью вкладки » конструктор таблиц» _гт_ строки заголовков. Если отключить заголовки столбцов таблицы, структурированные ссылки, использующие имена столбцов, не будут затронуты и их можно использовать в формулах. Структурированные ссылки, которые ссылаются непосредственно на заголовки таблицы (например, = ОтделПродаж [[#Headers], [% комиссионн]]), будут приводить к #REFу.

    Добавление и удаление столбцов и строк в таблице Поскольку диапазоны данных таблицы часто меняются, ссылки на ячейки для структурированных ссылок настраиваются автоматически. Например, если в формуле используется имя таблицы для подсчета количества всех ячеек данных в таблице, а затем добавляется строка данных, автоматически настраивается ссылка на ячейку.

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

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

    Примечание: Копирование структурированной ссылки и заполнение структурированной ссылки — не то же самое. При копировании все структурированные ссылки остаются неизменными, а при заполнении формулы полные структурированные ссылки настраивают указатели столбцов, например ряды, как показано в таблице ниже.

    Многоячеечные формулы массива в Excel

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

    Применение многоячеечной формулы массива

    На рисунке ниже представлена таблица с наименованием товара, его ценой и количеством. В ячейках D2:D6 вычисляется общая стоимость каждого вида товара (с учетом количества).

    В данном примере диапазон D2:D6 содержит пять формул. Многоячеечная формула массива позволяет вычислить тот же самый результат, используя одну единственную формулу. Чтобы воспользоваться формулой массива выполните следующие действия:

    1. Выделите диапазон ячеек, где должны отображаться результаты. В нашем случае это диапазон D2:D6.
    2. Как и при вводе любой формулы в Excel, первым делом необходимо ввести знак равенства.
    3. Выделите первый массив значений. В нашем случае это диапазон с ценами товаров B2:B6.
    4. Введите знак умножения и выделите второй массив значений. В нашем случае это диапазон с количеством товаров C2:C6.
    5. Если бы мы вводили обычную формулу в Excel, то закончили бы ввод нажатием клавиши Enter. Но поскольку это формула массива, то нужно нажать комбинацию клавиш Ctrl+Shift+Enter. Этим мы укажем Excel, что это не обычная формула, а формула массива, и он автоматически заключит ее в фигурные скобки.

    Excel автоматически заключает формулу массива в фигурные скобки. Если Вы вставите скобки вручную, Excel воспримет это выражение как обычный текст.

    1. Обратите внимание, что все ячейки диапазона D2:D6 содержат абсолютно одинаковое выражение. Фигурные скобки, в которые оно заключено, говорят о том, что это формула массива.
    2. Если бы мы при вводе формулы массива выделили меньший диапазон, например, D2:D4, то она вернула бы нам только первые 3 результата:
    3. А если больший диапазон, то в “лишних” ячейках оказалось бы значение #Н/Д (нет данных):

    Когда мы умножаем первый массив на второй, перемножаются их соответствующие элементы (B2 с C2, B3 с C3, B4 с C4 и т.д.). В итоге образуется новый массив, который содержит результаты вычислений. Поэтому, чтобы получить корректный результат, размерности всех трех массивов должны быть соответствующими.

    Преимущества многоячеечных формул массива

    Использовать в Excel единую многоячеечную формулу массива в большинстве случаев предпочтительнее, чем несколько отдельных формул. Рассмотрим основные преимущества, которые она предлагает:

    1. Используя многоячеечную формулу массива, Вы на 100% уверены, что все формулы в рассчитываемом диапазоне введены правильно.
    2. Формула массива в большей степени защищена от случайного изменения, поскольку редактировать можно только весь массив в целом. Если Вы попробуете изменить часть массива, у Вас ничего не получится. Например, при попытке удалить формулу из ячейки D4, Excel выдаст следующее предупреждение:
    3. Вы не сможете вставить новые строки или столбцы в диапазон, где введена формула массива. Чтобы вставить новую строку или столбец, придется заново перезадавать весь массив. Данный пункт можно рассматривать как преимущество, так и недостаток.

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

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

    Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

    Читать еще:  Как файл excel сделать только для чтения?

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

    Прямая ссылка на ячейки

    Имена таблицы и столбцов в Excel

    Сочетание имен таблицы и столбцов называется структурированной ссылкой. Имена в таких ссылках корректируются при добавлении данных в таблицу и удалении их из нее.

    Структурированные ссылки также появляются, когда вы создаете формулу вне таблицы Excel, которая ссылается на данные таблицы. Ссылки могут упростить поиск таблиц в крупной книге.

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

    Sales (продажи ) Пользователь

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

    Чтобы создать таблицу, выделите любую ячейку в диапазоне данных и нажмите клавиши CTRL + T.

    Убедитесь в том, что флажок таблица с заголовками установлен, и нажмите кнопку ОК.

    В ячейке E2 введите знак равенства (=), а затем щелкните ячейку C2.

    В строке формул после знака равенства появится структурированная ссылка [@[ОбъемПродаж]].

    Введите звездочку (*) сразу после закрывающей скобки и щелкните ячейку D2.

    В строке формул после звездочки появится структурированная ссылка [@[ПроцентКомиссии]].

    Нажмите клавишу ВВОД.

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

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

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

    На листе примера щелкните ячейку E2.

    В строке формул введите = C2 * D2 и нажмите клавишу Ввод.

    Обратите внимание на то, что хотя Excel копирует формулу вниз по столбцу, структурированные ссылки не используются. Если, например, вы добавите столбец между столбцами C и D, вам придется исправлять формулу.

    Как изменить имя таблицы?

    При создании таблицы Excel ей назначается имя по умолчанию («Таблица1», «Таблица2» и т. д.), но его можно изменить, чтобы сделать более осмысленным.

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

    Введите нужное имя в поле имя таблицы и нажмите клавишу Ввод.

    В этом примере мы используем имя ОтделПродаж.

    При выборе имени таблицы соблюдайте такие правила:

    Использование допустимых символов Имя должно начинаться с буквы, знака подчеркивания (_) или обратной косой черты (). Используйте буквы, цифры, точки и знаки подчеркивания для остального имени. Вы не можете использовать «C», «c», «R» или «r» для имени, поскольку они уже назначены как сочетание для выбора столбца или строки активной ячейки при их вводе в поле » имя » или » Перейти «.

    Не используйте ссылки на ячейки Имена не могут совпадать с ссылками на ячейки, например Z $100 или R1C1.

    Не используйте пробелы для разделения слов В имени нельзя использовать пробелы. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (.). Например, ОтделПродаж, Салес_такс или First. Quarter.

    Используйте не более 255 знаков. Имя таблицы может содержать не более 255 знаков.

    Присваивайте таблицам уникальные имена. Повторяющиеся имена запрещены. Excel не делает различий между символами верхнего и нижнего регистра в именах. Например, если в книге уже есть таблица «ПРОДАЖИ», то при попытке присвоить другой таблице имя «Продажи» вам будет предложено выбрать уникальное имя.

    Использование идентификатора объекта Если вы планируете использовать смешанные таблицы, сводные таблицы и диаграммы, рекомендуется присвоить имена типу объекта. Например: Тбл_салес для таблицы продаж, Пт_салес для сводной таблицы продаж и Чрт_салес для диаграммы продаж или Птчрт_салес для сводной диаграммы продаж. Это позволит сохранять все имена в упорядоченном списке в диспетчере имен.

    Правила синтаксиса структурированных ссылок

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

    В этой формуле используются указанные ниже компоненты структурированной ссылки.

    Имя таблицы: ОтделПродаж — это имя пользовательской таблицы. Он ссылается на табличные данные без заголовков или строк итогов. Вы можете использовать имя таблицы по умолчанию, например Таблица1, или изменить его, чтобы использовать другое имя.

    Указатель столбца: [Сумма продаж]а[Сумма комиссии] — это описатели столбцов, которые используют имена столбцов, которые они представляют. Они ссылаются на данные столбца без заголовка столбца или строки итогов. Всегда заключайте спецификаторы в квадратные скобки, как показано ниже.

    Указатель элемента: [#Totals] и [#Data] — это указатели специальных элементов, которые указывают на определенные части таблицы, такие как строка итогов.

    Указатель таблицы. [[#Итого],[ОбъемПродаж]] и [[#Данные],[ОбъемКомиссии]] — это указатели таблицы, которые представляют внешние части структурированной ссылки. Внешняя часть следует за именем таблицы и заключается в квадратные скобки.

    Структурированная ссылка: (ОтделПродаж [[#Totals]; [сумма продаж]] и отделпродаж [[#Data]; [сумма комиссии]] — структурированные ссылки, представленные в виде строки, начинающейся с имени таблицы и заканчивающейся указателем столбца.

    При создании или изменении структурированных ссылок вручную учитывайте перечисленные ниже правила синтаксиса.

    Использование квадратных скобок для указателей Все описатели таблиц, столбцов и специальных элементов должны быть заключены в парные квадратные скобки ([]). Спецификатор, который содержит другие описатели, требует наличия внешних парных скобок для заключения внутренних парных скобок других описателей. Пример: = ОтделПродаж [[продавец]: [регион]]

    Все заголовки столбцов — это текстовые строки. Но при использовании в структурированной ссылке их не нужно заключать в кавычки. Числа или даты, например 2014 или 01.01.2014, также считаются текстовыми строками. Нельзя использовать выражения с заголовками столбцов. Например, выражение ОтделПродажСводкаФГ[[2014]:[2012]] недопустимо.

    Заключайте в квадратные скобки заголовки столбцов, содержащие специальные знаки. Если присутствуют специальные знаки, весь заголовок столбца должен быть заключен в скобки, а это означает, что для указателя столбца потребуются двойные скобки. Пример: =ОтделПродажСводкаФГ[[Итого $]]

    Дополнительные скобки в формуле нужны при наличии таких специальных знаков:

    левая квадратная скобка ([);

    правая квадратная скобка (]);

    левая фигурная скобка (<);

    правая фигурная скобка (>);

    Операторы ссылок

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

    Эта структурированная ссылка:

    Все ячейки в двух или более смежных столбцах

    : (двоеточие) — оператор ссылки

    Сочетание двух или более столбцов

    , (запятая) — оператор объединения

    Пересечение двух или более столбцов

    (пробел) — оператор пересечения

    Указатели специальных элементов

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

    Этот указатель специального элемента:

    Вся таблица, включая заголовки столбцов, данные и итоги (если они есть).

    Только строки данных.

    Только строка заголовка.

    Только строка итога. Если ее нет, будет возвращено значение null.

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

    Excel автоматически заменяет указатели «#Эта строка» более короткими указателями @ в таблицах, содержащих больше одной строки данных. Но если в таблице только одна строка, Excel не заменяет указатель «#Эта строка», и это может привести к тому, что при добавлении строк вычисления будут возвращать непредвиденные результаты. Чтобы избежать таких проблем при вычислениях, добавьте в таблицу несколько строк, прежде чем использовать формулы со структурированными ссылками.

    Определение структурированных ссылок в вычисляемых столбцах

    Когда вы создаете вычисляемый столбец, для формулы часто используется структурированная ссылка. Она может быть неопределенной или полностью определенной. Например, чтобы создать вычисляемый столбец «ОбъемКомиссии», который вычисляет объем комиссии в рублях, вы можете использовать следующие формулы:

    Тип структурированной ссылки

    Перемножает соответствующие значения из текущей строки.

    Перемножает соответствующие значения из каждой строки обоих столбцов.

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

    Примеры использования структурированных ссылок

    Ниже приведены примеры использования структурированных ссылок.

    Эта структурированная ссылка:

    Все ячейки в столбце «ОбъемПродаж».

    Заголовок столбца «ПроцентКомиссии».

    Итог столбца «Регион». Если нет строки итогов, будет возвращено значение ноль.

    Все ячейки в столбцах «ОбъемПродаж» и «ПроцентКомиссии».

    Только данные в столбцах «ПроцентКомиссии» и «ОбъемКомиссии».

    Только заголовки столбцов от «Регион» до «ОбъемКомиссии».

    Итоги столбцов от «ОбъемПродаж» до «ОбъемКомиссии». Если нет строки итогов, будет возвращено значение null.

    Только заголовок и данные столбца «ПроцентКомиссии».

    =ОтделПродаж[[#Эта строка], [ОбъемКомиссии]]

    Ячейка на пересечении текущей строки и столбца сумма комиссии. При использовании в той же строке, что и строка заголовка или итог, возвращается ошибка #VALUE! .

    Если ввести длинную форму этой структурированной ссылки (#Эта строка) в таблице с несколькими строками данных, Excel автоматически заменит ее укороченной формой (со знаком @). Две эти формы идентичны.

    E5 (если текущая строка — 5)

    Методы работы со структурированными ссылками

    При работе со структурированными ссылками рекомендуется обращать внимание на перечисленные ниже аспекты.

    Использование автозавершения формул. Автозавершение формул может пригодиться при вводе структурированных ссылок для соблюдения правил синтаксиса. Дополнительные сведения см. в статье Использование автозавершения формул.

    Определение способа формирования структурированных ссылок для таблиц в частично выделенном фрагменте По умолчанию при создании формулы щелчок по диапазону ячеек в таблице частично выделяет ячейки и автоматически вводит в формулу структурированную ссылку, а не диапазон ячеек. Это поведение значительно упрощает ввод структурированной ссылки. Вы можете включить или отключить это поведение, установив или сняв флажок использовать имена таблиц в формулах в _гт_ файл _гт_ формулы _гт_ Работа с формулами.

    Использование книг с внешними ссылками на таблицы Excel в других книгах Если книга содержит внешнюю ссылку на таблицу Excel в другой книге, эта связанная исходная книга должна быть открыта в Excel, чтобы избежать #REF . в конечной книге, содержащей ссылки. Если сначала открыть конечную книгу, а #REF! ошибки, они будут устранены, если открыть исходную книгу. Если сначала открыть исходную книгу, вам не будут отображаться коды ошибок.

    Преобразование диапазона в таблицу и таблицы в диапазон При преобразовании таблицы в диапазон все ссылки на ячейки изменяются на эквивалентные абсолютные ссылки на стиль a1. При преобразовании диапазона в таблицу Excel не автоматически изменяет ссылки на ячейки этого диапазона на эквивалентные структурированные ссылки.

    Отключение заголовков столбцов Вы можете включать и выключать заголовки столбцов таблицы с помощью вкладки » конструктор таблиц» _гт_ строки заголовков. Если отключить заголовки столбцов таблицы, структурированные ссылки, использующие имена столбцов, не будут затронуты и их можно использовать в формулах. Структурированные ссылки, которые ссылаются непосредственно на заголовки таблицы (например, = ОтделПродаж [[#Headers], [% комиссионн]]), будут приводить к #REFу.

    Добавление и удаление столбцов и строк в таблице Поскольку диапазоны данных таблицы часто меняются, ссылки на ячейки для структурированных ссылок настраиваются автоматически. Например, если в формуле используется имя таблицы для подсчета количества всех ячеек данных в таблице, а затем добавляется строка данных, автоматически настраивается ссылка на ячейку.

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

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

    Примечание: Копирование структурированной ссылки и заполнение структурированной ссылки — не то же самое. При копировании все структурированные ссылки остаются неизменными, а при заполнении формулы полные структурированные ссылки настраивают указатели столбцов, например ряды, как показано в таблице ниже.

    Ссылка на основную публикацию
    Adblock
    detector