Формулы excel работа с таблицами: Как использовать вычисляемые столбцы в таблице Excel

Как использовать вычисляемые столбцы в таблице Excel

Excel

Импорт и анализ данных

Таблицы

Таблицы

Как использовать вычисляемые столбцы в таблице Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще…Меньше

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

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

Создание вычисляемого столбца

1. Создайте таблицу. Если вы не знакомы с таблицами Excel, см. статью Общие сведения о таблицах Excel.

2. Вставьте в таблицу новый столбец. Введите данные в столбец справа от таблицы, и Excel автоматически расширит ее. В этом примере мы создали новый столбец, введя «Итог» в ячейке D1.

   Советы: 

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

3. Введите нужную формулу и нажмите клавишу ВВОД.

   В этом случае мы ввели =СУММ(, а затем выбрали столбцы Кв1 и Кв2. В результате Excel создал следующую формулу: =СУММ(Таблица1[@[Кв1]:[Кв2]]). Такие формулы называются формулами со структурированными ссылками, и их можно использовать только в таблицах Excel. Структурированные ссылки позволяют использовать одну и ту же формулу в каждой строке. Обычная формула Excel выглядела бы как =СУММ(B2:C2), и ее было бы необходимо добавить в остальные ячейки путем копирования и вставки или заполнения.

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

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

Примечания: 

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

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

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

• Если вы введили или скопировали формулу в ячейку пустого столбца и не хотите сохранять новый вычисляемого столбца, нажмите кнопку Отменить два раза. Вы также можете дважды нажать клавиши CTRL+Z.

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

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

• При вводе в ячейку вычисляемого столбца данных, отличных от формулы.

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

• org/ListItem»>

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

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

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

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

  Примечание: В этом случае исключение не помечается.

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

Уведомление об ошибке отображается только в том случае, если включена фоновая проверка ошибок. Если вы не видите сообщение об ошибке, откройте Файл > Параметры > Формулы и убедитесь, что флажок Включить фоновую проверку ошибок установлен.

• Если вы используете Excel 2007, нажмите кнопку Office и выберите Параметры Excel > Формулы.

• Если вы используете Mac, в строке меню Excel выберите Параметры > Формулы и списки > Поиск ошибок.

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

• 
  Включение и выключение вычисляемых столбцов

  1. На вкладке Файл нажмите кнопку Параметры.

     Если вы используете Excel 2007, нажмите кнопку Office и выберите Параметры Excel.

  2. Выберите категорию Правописание.

  3. org/ListItem»>

     В разделе Параметры автозамены нажмите кнопку Параметры автозамены

  4. Откройте вкладку Автоформат при вводе.

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

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

  Если вы используете Mac, выберите Excel в главном меню, а затем щелкните Параметры > Формулы и списки > Таблицы и фильтры > Автоматически заполнять формулы.

• 
  Прекращение автоматического создания вычисляемых столбцов

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Общие сведения о таблицах Excel

Форматирование таблицы Excel

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

Данные итогов в таблице Excel

Инструкция как работать с таблицами в Excel (простыми словами)

Автор Амина С. На чтение 11 мин Опубликовано 04.07.2020

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

Отличается работа с таблицами Excel и от построения таблиц в других электронных документах, таких форматов, как Microsoft Word. И все это может несколько напугать новичка. Но, как говорится, не боги горшки обжигали.

Содержание

1. Преимущества таблиц Excel
2. Создание таблицы Excel
3. Базовые особенности работы с таблицами
4. Свойства таблиц
5. Внесение настроек в таблицу
6. Но не все так просто…
7. Некоторые дополнительные особенности таблиц
8. Таблица и именованный диапазон
9. Выводы

Преимущества таблиц Excel

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

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

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

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

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

Создание таблицы Excel

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

1

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

2

Второй – воспользоваться горячими клавишами Ctrl + T. Далее появится небольшое окошко, в котором можно более точно указать диапазон, входящий в таблицу, а также дать Excel понять, что в таблице содержатся заголовки. В качестве них будет выступать первая строка.

3

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

Перед тем, как разбираться в особенностях работы с таблицами, необходимо разобраться, как она устроена в Excel.

Базовые особенности работы с таблицами

Один из самых главных элементов таблицы – ее название. Его можно увидеть во вкладке «Конструктор». Она отображается сразу после того, как будет нажата левая кнопка мыши на любую ячейку, входящую в нее. Название есть, даже если пользователь его не задает. Просто в таком случае по умолчанию дается имя «Таблица 1», «Таблица 2» и другие.

4

Если вы собираетесь использовать сразу несколько таблиц в вашем документе, то рекомендуем дать более понятные имена. В будущем тогда будет значительно проще понять, какая из них за что отвечает. Особенно это важно при работе с Power Query и Power Pivot. Давайте присвоим таблице имя «Отчет».

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

5

Увидеть название таблицы можно и при ручном вводе формулы.

6

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

7

Начинающий пользователь сразу скажет: «Боже, как можно все это выучить»? Но на самом деле, этого не нужно делать, поскольку в ходе набора формулы появляются подсказки. Главное – не забыть открыть квадратную скобку (ее можно найти в английской раскладке там, где у нас находится кнопка «х»).

8

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

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

=Отчет[Продажи]

Простыми словами, ссылка указывает не на какой-то определенный диапазон, а на всю колонку таблицы.

9

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

Свойства таблиц

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

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

10

Если таблица большая, пользователь все равно может увидеть названия после прокрутки диапазона вниз, поскольку названия столбцов на панели координат автоматически переименуются в названия соответствующих колонок.

11

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

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

Вот маленькая демонстрация того, как новая строка автоматически добавляется к таблице.

12

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

То же касается и новых столбцов.

13

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

14

Но и это еще не все. Можно внести определенные изменения в функционал таблицы.

Внесение настроек в таблицу

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

15

Быстро сделать привлекательный дизайн таблицы не составит никакого труда благодаря готовым шаблонам. Их можно найти в группе «Стили таблиц». Если же нужно внести свои изменения в дизайн, это также легко сделать.

16

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

17

Но одна из самых интересных возможностей любой таблицы – срезы.

18

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

19

После этого в отдельной панели появляется перечень уникальных значений определенной колонки.

20

Чтобы применить фильтр, необходимо кликнуть по нужной категории.

21

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

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

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

22

Но не все так просто…

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

1. Нет возможности использовать представления. Простыми словами, нельзя запомнить ряд настроек листа, таких как фильтр, свернутые строки или столбцы и так далее.
2. Нельзя использовать эту книгу одновременно с другим человеком через функцию совместного использования.
3. Возможна лишь вставка окончательных итогов. 
4. В таблице нельзя использовать формулы массивов, что накладывает серьезные ограничения при работе с большими объемами данных.
5. Ячейки в таблице не могут быть объединены. Впрочем, даже в обычном диапазоне эту функцию рекомендуют использовать с осторожностью.
6. Невозможно транспонировать таблицу так, чтобы заголовки располагались в строках. Чтобы это сделать, ее нужно переконвертировать в формат обычного диапазона.

Некоторые дополнительные особенности таблиц

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

Функционал таблиц предусматривает возможность автоматического подсчета итогов. Для этого необходимо выделить нужный столбец + одна ячейка под ним, в которую будет выводиться результат и нажать на кнопку «Сумма» на вкладке «Главная».

2324

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

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

25

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

Таблица и именованный диапазон

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

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

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

= СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

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

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

Выводы

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

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

Оцените качество статьи. Нам важно ваше мнение:

Глава 7 Работа с данными таблицы Excel

Глава 7 Работа
с данными
таблицы Excel

Строки в
списке можно
сортировать
по значениям
ячеек одного
или
нескольких
столбцов.
Строки,
столбцы или
отдельные
ячейки в процессе
сортировки
переупорядочиваются
в соответствии
с заданным
пользователем
порядком
сортировки.
Списки можно
сортировать
в возрастающем
(от 1 до 9, от А до
Я) или
убывающем (от
9 до 1, от Я до А)
порядке.

Сортировка
данных:

1. Выделить
данные в
таблице.

2. Меню
Данные Сортировка

3. В окне Сортировка
диапазона
указать
столбец, по
которому
будет
производиться
сортировка,
затем ее
способ (по
возрастанию
или по
убыванию).

Фильтрация
данных:

Фильтрация
это скрытие
ненужных
данных и
показ нужных.

Фильтры
могут быть
использованы
только для
одного
списка на
листе.

1. Укажите
ячейки в
фильтруемом
списке.

2. Меню Данные
Фильтр Автофильтр.

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

4. Выберите
значение в
списке.

5. Повторите
шаги 3 и 4 для
введения
дополнительных
ограничений
значений в
других
столбцах.

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

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

Анализ
данных

В состав Microsoft Excel
входит набор
средств
анализа
данных (называемый
пакет
анализа),
предназначенный
для решения
сложных
статистических
и инженерных
задач. Для
проведения
анализа
данных с
помощью этих
инструментов
следует
указать
входные
данные и
выбрать
параметры;
анализ будет
проведен с
помощью
подходящей
статистической
или
инженерной
макрофункции,
а результат
будет
помещен в
выходной
диапазон. 

Если
команда Анализ
данных
отсутствует
в меню Сервис,
то
необходимо
запустить
программу
установки Microsoft Excel.
После
установки
пакета
анализа его
необходимо
выбрать и
активизировать
с помощью
команды Настройки.
Для
успешного
применения
процедур
анализа
необходимы
начальные
знания в
области
статистических
и инженерных
расчетов, для
которых эти
инструменты
были
разработаны.

Чтобы
запустить
пакет
анализа:

1.  В меню Сервис
выберите
команду Анализ
данных.

2. В списке Инструменты
анализа
выберите
нужную
строку.

3. Введите
входной и
выходной
диапазоны,
затем
выберите
необходимые
параметры.

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

Создание
диаграммы

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

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

Можно
создать либо
внедренную
диаграмму (то
есть
поместить её
на лист рядом
с таблицей),
либо лист
диаграммы.

С

оздание
диаграммы:

1. Выделите
ячейки,
содержащие
данные,
которые
должны быть
отражены на
диаграмме. 

2. Если
необходимо,
чтобы в
диаграмме
были
отражены и
названия
строк или
столбцов,
выделите
также
содержащие
их ячейки.

3. Меню Вставка
Диаграмма.

4. Следуйте
инструкциям Мастера.

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

Изменение
диаграммы:

1.  Выберите
изменяемую
диаграмму.

2. В меню Диаграмма
выберите
нужную
строку,
например Диапазон
данных.

3. В окне
редактирования
внесите
нужные
изменения.

Вычисления

Автосуммирование

Сумму
значений
выделенных
ячеек можно
ввести
автоматически
с помощью
кнопки      S  Автосуммирование. 

При нажатии
кнопки Автосуммирование
автоматически
предлагается
формула
вычисления,
например =
СУММ(E5:E10). Чтобы
принять
формулу,
нажмите
клавишу Enter.

Синтаксис
формулы

Синтаксисом
формул
называется
порядок, в
котором
вычисляются
значения и
задается
последовательность
вычислений.
Формула
должна
начинаться
со знака
равенства (=),
за которым
следует
набор
вычисляемых
величин. В следующем
примере
представлена
формула,
вычисляющая
разность
между
числами 5 и 1.
Результат
выполнения
отобразится
в ячейке, в
которой
указана
формула.

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

Функции

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

1. Меню
Вставка Функции
f(x).

2. Следуйте
за Мастером
функций.

Помните,
что Excel
посчитает
все числа, но
правильность
расчетов
зависит от
вашего
знания
математики и
правильности
задания
формул.

Сводная
таблица

Сводная
таблица
создается с
помощью Мастера
сводных
таблиц,
используемого
для
размещения и
объединения
анализируемых
данных:

1. Меню
Данные Сводная
таблица.

2. Идите
по шагам Мастера.

Подведение
итогов в
сводной
таблице
производится
с помощью
итоговой
функции (например,
«Сумма», «Кол-во
значений»
или «Среднее»). 

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

В
сводной
таблице
содержатся
поля,
подводящие
итоги
исходных
данных в
нескольких
строках.

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

Продолжим                                                               
Назад

Умные Таблицы Excel – секреты эффективной работы

В MS Excel есть много потрясающих инструментов, о которых большинство пользователей не подозревают или сильно недооценивает. К таковым относятся Таблицы Excel. Вы скажете, что весь Excel – это электронная таблица? Нет. Рабочая область листа – это только множество ячеек. Некоторые из них заполнены, некоторые пустые, но по своей сути и функциональности все они одинаковы.

Таблица Excel – совсем другое. Это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Также встречается под названием «умные таблицы». 

Как создать Таблицу в Excel

В наличии имеется обычный диапазон данных о продажах.

Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

Есть горячая клавиша Ctrl+T.

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

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

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

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

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

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

=СУММ(D2:D8)

то она автоматически переделается в

=Отчет[Продажи]

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

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

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

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

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

5. Новые столбцы также автоматически включатся в Таблицу.

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

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

Настройки Таблицы

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

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

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

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

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

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

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

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

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

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

Множество других секретов Excel вы найдете в онлайн курсе.

Поделиться в социальных сетях:

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

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

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

1.Автозаполнение формул в таблице Excel

Кроме непосредственных обязанностей бухгалтеру могут добавить функции по подготовке коммерческих предложений, расчета договорных цен и прочее. Для выполнения расчетов необходимо применять различные коэффициенты и поправки, а также конвертировать цены. И главное, выполнять все действия быстро и без ущерба обязанностям. Эксель для бухгалтера поможет в подготовке основного документа, который можно выполнять на рабочем листе, а дополнительные расчеты на отдельных. Так, пересчитывая курс, в одной ячейке можно указать цену, во второй курс валюты, а в третьей задать формулу пересчета (= первая ячейка * вторая ячейка), далее нажать Enter и получить цену. В первом листе в нужной ячейке можно поставить “=”, перейти на второй лист и указать третью ячейку с итогом. Опять нажать Enter и получить результат. Если необходимо провести такие расчеты по большому количеству пунктов, где изменяется только цена, то можно воспользоваться фундаментальным приемом Excel — автозаполнение формул, или протягивание. Возможность протягивать формулы — одно из базовых функций программы. Она автоматизирует процесс подсчета данных в таблице, без многократного прописывания одной и той же формулы. Выполнять протягивание формул можно следующим образом. В строке формул ставим равно и ссылку на ячейку из таблицы с исходными данными (=А3). После этого получим просто дублирование значения из таблицы. При протягивании этой ячейки получится копия таблицы с данным, которые будут изменяться соответственно со сменой информации в исходной таблице. Это пример протягивания ячеек без фиксирования диапазонов.

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

2.Подсчет календарных дней

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

Рекомендация: набирайте дату на цифровой части клавиатуры так: 12/10/2016. Программа сама превратит введенные данные в формат даты и получится 12.10.2016. Далее выбираем третью ячейку и жмем “Вставить функцию”, вы можете найти ее по значку ¶x. После нажатия всплывет окно “Мастер функций”. Из списка “Категория” выбираем “Дата и время”, а из списка “Функция”— “ДНЕЙ360” и нажимаем кнопку Ок. В появившемся окне нужно вставить значения начальной и конечной даты. Для этого нужно просто щелкнуть по ячейкам таблицы с этими датами, а в строке “Метод” поставить единицу и нажать Ок. Если итоговое значение отражено не в числовом формате, нужно проверить формат ячейки: щелкнуть правой кнопкой мыши и выбрать из меню “Формат ячейки”, установить “Числовой формат” и нажать Ок. Еще можно выполнить подсчет дней таким способом: в третьей ячейке набрать = ДНЕЙ 360 (В1; В2; 1). В скобках необходимо указать координаты двух первых ячеек с датами, а для метода поставить значение единицы. При расчете процентов за недели можно полученное количество дней разделить на 7. Также к дате можно прибавлять и отнимать любое количество дней. Чтобы это выполнить, нужно в одной ячейке написать дату, во второй разместить знак равенства, затем щелкнуть по ячейке с датой и набрать “+” или “-” и требуемое количество дней.

3.Сортировка данных

Очень удобная функция, которая позволяет разместить данные по возрастанию/убыванию. Также сортировать данные можно и для упорядочивания записей по дате. Для выполнения этого действия необходимо выбрать область, которая требует сортировки. Затем можно нажать кнопку “Сортировка по возрастанию” в верхнем ряду меню “Данные”, ее вы найдете по знаку “АЯ”. Ваши данные разместятся от меньшего к большему по первому выделенному столбцу.

Таблицы Эксель для бухгалтера позволяют сортировать данные, начиная с первого выделенного столбца. Если вы выделили ячейки слева направо, то последовательность будет выполнена в крайнем левом столбце. Если справа налево, то в правом. Если данные нужно сортировать по среднему столбцу, то можно использовать меню “Данные” — пункт “Сортировка” — “Сортировка диапазона”. В разделе “Сортировать по” необходимо выбрать столбец и тип сортировки.

4.Работа с длинными таблицами

Таблицы Excel для бухгалтера — многофункциональный рабочий инструмент, который содержит множество информации для ведения отчетности и выполнения текущих расчетов. При печати таблицы, которая не умещается на один лист, можно разместить ее “шапку” на каждой отдельной страничке, что облегчит поиск необходимых данных. Для этого нужно выбрать в меню “Файл”— “Параметры страницы” и закладку “Лист”. Размещаем курсор на “Сквозные строки” или “Сквозные столбцы” и в таблице кликаем на строки, которые нужно разместить на каждом листе. Также для работы с такими документами можно использовать колонтитулы. В них отмечают необходимые данные, такие как дата, номера листов, имя составителя и прочее. Настройка колонтитулов доступна в “Параметрах страницы” — “Колонтитулы”. Там доступны готовые варианты разметки или возможность добавления собственного. Кроме полезных приемов по работе в Эксель, бухгалтеру необходимо освоить его горячие клавиши.

5.Сопоставление показателей за разные периоды

Часто данные за разные годы, кварталы, месяцы приходится сравнивать. Чтобы их сопоставить, нужно выбрать критерий — уникальный показатель для каждой единицы учета, например, артикул. Для этого используем функцию “ВПР” (“VLOOKUP”), которая к значениям по заданному критерию из первой таблицы подберет соответствующие из второй таблицы.

Чтобы сравнить, например, цены на товары, следуйте алгоритму:

На панели инструментов заходим в функции(“fx”) и выбираем “ВПР”. В появившемся окне указываем для нее аргументы:

• Искомое значение — критерий;
• Таблица — диапазон данных из второй таблицы, среди которых нужно найти соответствующие;
• Номер столбца — диапазон данных, к которым будут подбираться соответствующие;
• Интервальный просмотр — это степень соответствия, нам нужно точное, поэтому выбираем 0.

Копируем формулу до конца столбца. Сообщение ”#Н/Д” значит, что для указанного артикула во второй таблице значений не обнаружено.

Чтобы при копировании формулы вправо-влево и вверх-вниз диапазон данных не менялся, можно использовать символ «$». Для этого его нужно поставить перед названием строки или столбца, а чтобы закрепить ячейку — и там, и там.

6.Поиск показателей, соответствующих условиям

Возможности Excel облегчают поиск нужных данных. Разберем на примере поиска задолженности контрагента. Для этого:

• Рядом с таблицей, в которой будем искать, добавляем таблицу с условиями поиска. Для этого копируем шапку и в соответствующие столбцы вводим критерии поиска, используя знаки сравнения: «>», «
  
• Заходим на вкладку “Данные” и выбираем пункт “Фильтр — Дополнительно”.

В открывшемся окне указываем:

• Исходный диапазон — полностью таблица, в которой будем искать;
• Диапазон условий — таблица с критериями поиска.

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

7.Поиск ошибок в таблицах

Поиск опечаток и ошибок в таблицах работа долгая и скучная. Чтобы ускорить процесс, воспользуйтесь возможностями электронных таблиц Excel: Заходим на вкладку “Данные” и выбираем пункт “Проверка данных”.

В появившемся окне указываем:

• Тип данных — выбираем “список”;
• Источник — выделяем диапазон с образцовым списком.

В первом столбце таблицы появится кнопка “Список”. Ею можно пользоваться, чтобы в дальнейшем вводить данные без ошибок.

Далее выбираем пункт “Проверка данных — Обвести неверные данные” и ячейки, в которых есть опечатки, будут выделены овалом.

8.Формирование сводной таблицы

Подведение итогов за какой-то период — неотъемлемая часть работы бухгалтера. Формирование сводной таблицы с итоговыми показателями значительно упростит дело.

Для этого следуйте инструкции:

• Полностью выделяем диапазон с таблицей, переходим на вкладку “Вставка” и выбираем пункт “Сводная таблица”.
• В появившемся окне выбираем опцию “Поместить отчет на новый лист”.
• Заходим на новую вкладку и выбираем поля для новой таблицы.
• Для подведения итогов, выбираем диапазон данных за нужный период, кликаем правой кнопкой мышки и выбираем пункт “Группировать”.

На заметку!

Чтобы создать сводную таблицу на основе данных из нескольких таблиц, используйте “Мастер сводных таблиц и диаграмм” и выберите пункт “Создать таблицу на основе данных, находящихся в нескольких диапазонах консолидации”.

9.Выбор показателей в пределах лимита

Чтобы отобрать расходы в пределах установленного лимита, нужно выполнить в Excel следующие действия:

• Заходим на вкладку “Файл”, выбираем пункт “Параметры Excel – Надстройки”.
• В открывшемся окне ставим галочку напротив пункта “Поиск решения” и кликаем “Ок”( надстройка “Поиск решения”появится на вкладке “Данные”).

В свободной ячейке рядом с таблицей указываем предельную сумму(S1), а ниже находим S2. Для этого используем формулу “=СУММПРОИЗВ(Диапазон1*Диапазон)”, где: Диапазон1 — столбец со значениями, среди которых мы подбираем нужные; Диапазон2 — столбец, где будет указано, какие значения нужно сложить. Ниже находим разность S1 и S2 по формуле “=ABS(S1-S2)”. Переходим на вкладку “Данные”, выбираем пункт “Поиск решения”.

В открывшемся окне указываем:

• Установить целевую ячейку: ячейка с формулой “=ABS(S1-S2)”;
• Равной: минимальному значению;
• Изменяя ячейки: Диапазон2 — столбец, где будет указано, какие значения нужно сложить; Ограничения: добавляем для Диапазон2 и выбираем опцию “бин” (“двоич”).

Кликаем “Найти решение” или “Выполнить”и сохраняем результат. Напротив нужных сумм увидим единицы, напротив ненужных – нули.

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

10.Определение влияния изменения данных на конечный расчет

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

• Заходим на вкладку “Формулы” и выбираем пункт “Окно контрольного значения”.
• В открывшемся окне выбираем “Добавить контрольное значение” и указываем ячейку с итоговой суммой. Можно добавить сразу несколько контрольных показателей.
• Теперь при переходе на другой лист контрольное окно будет оставаться на экране. При изменении данных на других листах будет сразу видно, как это влияет на итоговые показатели.

11.Функция ЕСЛИ и выпадающий список

Одна из самых востребованных функций в Excel – функция ЕСЛИ. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функцию ЕСЛИ создают по такому синтаксису: ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

Лог_выражение – это условие, которое нужно проверить.

Значение_если_истина – это то, что должно появиться в ячейке, если текст или цифра отвечают указанным требованиям.

Значение_если_ложь – то, что появится в ячейке, когда определится, что данные не соответствуют заданным параметрам.

Пример: =ЕСЛИ(В2>200;”Ок”;”Удалить”)

Функция проверяет ячейку В2 и сравнивает ее с логическим выражением – “>200”. Если истинное значение содержит цифру больше 200, то в столбце С появляется значение “Ок”, если меньше – “Удалить”.

Выпадающий список в Excel можно создать за пару кликов. Для этого необходимо:
1.Открыть таблицу с данными, которые нужно добавить в выпадающий список. Для версии Excel 2003 и более ранних делайте это через меню Вставка — Имя — Присвоить (Insert — Name — Define).

2.Для версий Excel 2007 и моложе нужно кликнуть на вкладку “Формулы”- “Диспетчер имен” и “Создать”. Введите название позиций (например Счета на оплату услуг), которые хотите сгруппировать и подтвердите его через “Ок”. Важно: имя должно быть без пробелов и начинаться с буквы.

3.Выделить ячейки, которые должны стать выпадающим списком. Выберите меню из вкладки “Данные” — “Проверка” (Data — Validation). Из предложенного списка выберите “Тип данных”, введите строку “Источник”= название вашего диапазона Счета на оплату услуг. Готово!

12.Набор горячих клавиш Excel, без которых вам не обойтись

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

F4 — при вводе формулы, регулирует тип ссылок (относительные, фиксированные). Можно использовать для повтора последнего действия.

Shift+F2 — редактирование примечаний

Ctrl+; — ввод текущей даты (для некоторых компьютеров Ctrl+Shift+4)

Ctrl+’ — копирование значений ячейки, находящейся над текущей (для некоторых компьютеров работает комбинация Ctrl+Shift+2)

Alt+F8 — открытие редактора макросов

Alt+= — суммирование диапазона ячеек, находящихся сверху или слева от текущей ячейки

Ctrl+Shift+4 — определяет денежный формат ячейки

Ctrl+Shift+7 — установка внешней границы выделенного диапазона ячеек

Ctrl+Shift+0 — определение общего формата ячейки

Ctrl+Shift+F — комбинация открывает диалоговое окно форматирования ячеек

Ctrl+Shift+L — включение/ отключение фильтра

Ctrl+S — сохранение файла (сохраняйтесь как можно чаще, чтобы не потерять ценные данные).

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

Не останавливайтесь на Excel!
Научитесь автоматизировать подготовку отчетов в программе Power BI на курсе «ACPM: Бизнес-анализ данных в финансах»!

Посмотреть пробный урок бесплатно

Насколько уверенно вы владеете Excel?

знаю несколько инструментов и формул

постоянно изучаю новые фишки программы

новичок в программе, хочу изучить глубже

продвинутый уровень, преподаю Excel

уже перехожу на BI-системы

Формулы: ссылки на данные из других таблиц

PLANS

• Smartsheet
• Pro
• Business
• Enterprise

Права доступа

Who can use this capability?

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

Этот Справочная статья есть в следующих учебных курсах:

Учебный курс

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

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

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

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

Хотите работать с данными в одной таблице? Рекомендуем вместо этого использовать поля сводки по таблице. 

Прежде чем создавать межтабличные ссылки

Готовы приступить к работе с межтабличными формулами? Обратите внимание на следующее.

• Вы должны обладать необходимыми разрешениями. См. следующую диаграмму. 
• Таблица может содержать до 100 отдельных межтабличных ссылок. 
• Диапазон, на который указывает ссылка, может содержать до 100 000 входящих ячеек.
• Ссылки из другой таблицы не поддерживаются следующими функциями: CHILDREN, PARENT, ANCESTORS. Использование ссылки из другой таблицы при работе с этими функциями приведёт к ошибке #UNSUPPORTED CROSS-SHEET FORMULA в ячейке с формулой.

Необходимые разрешения

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

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

Прежде чем создавать ссылки на данные 

Готовы приступить к работе с межтабличными формулами? Обратите внимание на следующее.

• Таблица может содержать до 100 отдельных межтабличных ссылок. 
• Диапазон, на который указывает ссылка, может содержать до 100 000 входящих ячеек.
• Ссылки из другой таблицы не поддерживаются следующими функциями: CHILDREN, PARENT, ANCESTORS. Использование ссылки из другой таблицы при работе с этими функциями приведёт к ошибке #UNSUPPORTED CROSS-SHEET FORMULA в ячейке с формулой. 

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

Остались вопросы?

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

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

Обратиться к Сообществу

Использование формул с таблицами в Excel

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

Во-первых, убедитесь, что ваши данные отформатированы в виде таблицы; если это не так, прочтите это руководство о том, как создавать и управлять таблицами в Excel

Разделы:

Создание и применение формул к таблицам в Excel

Справочные данные в таблице

Справочные данные таблицы вне таблицы

Ссылки на различные разделы таблицы

Заголовки столбцов со специальными символами

Изменение имени таблицы

Примечания

Создание и применение формул к таблицам в Excel

1. Введите знак равенства там, где вы хотите ввести формулу.
2. Теперь мы используем таблицу, поэтому ссылаемся на столбцы по-другому.
   Введите [ и Excel представит список столбцов в вашей таблице, которые вы можете использовать в своей формуле.
   (ссылки на таблицы ВСЕГДА должны начинаться и заканчиваться открывающей и закрывающей скобкой [ ] )
3. Вы можете щелкнуть один из вариантов в раскрывающемся списке, чтобы выбрать весь столбец данных, или вы можете ввести имя столбца ИЛИ, если вы просто хотите сослаться на текущую строку, введите символ @ , а затем имя столбца. Также не забудьте поставить закрывающую скобку ] после имени столбца.
4. Теперь я хочу разделить продажи на цель, чтобы увидеть, какая часть цели была достигнута.
   Я набираю знак деления / , а затем открываю квадратную скобку [ , чтобы сослаться на другой столбец в таблице, а затем @ , чтобы сделать ссылку на ту же строку, а затем набираю имя столбца Цель и затем закрываю кронштейн ]

5. Нажмите Enter, и формула автоматически скопирует всю таблицу.
6. Просто добавьте необходимое форматирование и все.

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

Справочные данные в таблице

Все ссылки на столбцы таблицы ДОЛЖНЫ начинаться и заканчиваться открывающей и закрывающей квадратной скобкой [] . В противном случае это не будет интерпретироваться как ссылка на таблицу.

Ссылка на текущую строку

Ссылка только на данные из текущей строки, той же строки, в которой вы вводите формулу.

Excel может записать это несколькими способами, в том числе: #This Row и @ . Однако при создании формул следует использовать наиболее общую форму, которая приведена ниже:

=[@ имя столбца ]

«Имя столбца» следует заменить именем используемого столбца, но все остальное остается прежним.

Ссылка на весь столбец данных

Ссылка на все данные определенного столбца.

=[имя столбца]

Ссылка на данные таблицы извне таблицы

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

Вот синтаксис:

=ИмяТаблицы[ИмяСтолбца]

Если ваша таблица имеет имена SalesData и вы хотите получить общее количество продаж из столбца Sales , вы должны использовать следующую формулу:

Выберите столбец, который вы хотите суммировать, или просто введите его.

Не забудьте поставить закрывающую скобку после названия столбца.

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

Ссылка на разные части таблицы

Существуют специальные способы ссылки на определенные части таблицы. Каждая ссылка здесь должна начинаться со знака решетки 9.0030 # .

Помните, что все ссылки на таблицы также должны начинаться и заканчиваться открывающей и закрывающей квадратной скобкой [ ] .

Вся таблица

Ссылается на все в таблице, включая данные, заголовки, итоговую строку, все.

=[#Все]

Все строки данных

Ссылается только на строки данных в таблице.

=[#Данные]

Заголовки

Ссылается только на заголовки таблицы.

=[#Заголовки]

Строка итогов

Ссылается только на строку итогов в таблице.

=[#Всего]

Заголовки столбцов со специальными символами

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

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

Изменить имя таблицы

Имя таблицы по умолчанию будет примерно таким: Table1 , и это не очень описательно.

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

1. Выберите ячейку в нужной таблице.
2. Перейдите на вкладку Design , посмотрите на левую сторону и измените значение под Имя таблицы:

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

Примечания

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

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

Использовать вычисляемые столбцы в таблице Excel

Excel

Импорт и анализ данных

Столы

Столы

Использовать вычисляемые столбцы в таблице Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Дополнительно…Меньше

Вычисляемые столбцы в таблицах Excel — отличный инструмент для эффективного ввода формул. Они позволяют вам ввести одну формулу в одну ячейку, а затем эта формула автоматически расширится до остальной части столбца сама по себе. Нет необходимости использовать команды «Заполнить» или «Копировать». Это может невероятно сэкономить время, особенно если у вас много строк. И то же самое происходит, когда вы меняете формулу; изменение также распространится на остальную часть вычисляемого столбца.

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

Создать вычисляемый столбец

1. Создать таблицу. Если вы не знакомы с таблицами Excel, вы можете узнать больше по ссылке: Обзор таблиц Excel.

2. org/ListItem»>

   Вставить новый столбец в таблицу. Вы можете сделать это, введя текст в столбце справа от таблицы, и Excel автоматически расширит таблицу за вас. В этом примере мы создали новый столбец, введя «Итого» в ячейку D1.

   Советы: 

   • Вы также можете добавить столбец таблицы из вкладки Домашняя страница . Просто нажмите на стрелку для Вставка > Вставить столбцы таблицы слева .

3. Введите формулу, которую вы хотите использовать, и нажмите Введите .

   В этом случае мы ввели =sum( , затем выбрали Qtr 1 и Qtr 2 колонки. В результате Excel построил формулу: =СУММ(Таблица1[@[Квартал 1]:[Квартал 2]]) . Это называется формулой структурированной ссылки , которая уникальна для таблиц Excel. Формат структурированной ссылки позволяет таблице использовать одну и ту же формулу для каждой строки. Обычная формула Excel для этого будет =СУММ(B2:C2) , которую затем вам нужно будет скопировать или заполнить до остальных ячеек в столбце

   .

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

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

Примечания:

• org/ListItem»>

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

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

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

• Если вы ввели или скопировали формулу в ячейку пустого столбца и не хотите сохранять новый вычисляемый столбец, дважды щелкните Отменить . Вы также можете нажать Ctrl + Z дважды

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

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

• org/ListItem»>

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

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

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

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

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

• org/ListItem»>

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

  Примечание. Это исключение не отмечено.

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

Уведомление об ошибке появится только в том случае, если у вас включена опция фоновой проверки ошибок. Если вы не видите ошибку, перейдите к File > Options 9.0258 > Формулы > убедитесь, что установлен флажок Включить фоновую проверку ошибок .

• Если вы используете Excel 2007, нажмите кнопку Office , затем Параметры Excel > Формулы .

• org/ListItem»>

  Если вы используете Mac, перейдите к Excel в строке меню, а затем нажмите Настройки > Формулы и списки > Ошибка проверки .

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

• Включение или выключение вычисляемых столбцов

  1. org/ListItem»>

     На вкладке Файл щелкните Параметры .

     Если вы используете Excel 2007, нажмите кнопку Office , затем Параметры Excel .

  2. Нажмите Проверка .

  3. В разделе Параметры автозамены щелкните Параметры автозамены .

  4. Перейдите на вкладку Автоформат при вводе .

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

     Совет:  Вы также можете нажать кнопку Параметры автозамены , которая отображается в столбце таблицы после ввода формулы. Нажмите «Управление параметрами автозамены», а затем снимите флажок «Заполнить формулы в таблицах для создания вычисляемых столбцов», чтобы отключить этот параметр.

  Если вы используете Mac, перейдите к Excel в главном меню, затем Настройки > Формулы и списки > Таблицы и фильтры > Автоматически заполнять формулы .

• Остановить автоматическое создание вычисляемых столбцов

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

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

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. также

Обзор таблиц Excel

Отформатировать таблицу Excel

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

Суммируйте данные в таблице Excel

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

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

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

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

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

org/ItemList»>

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

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

3. Убедитесь, что установлен флажок Моя таблица имеет заголовки , и нажмите OK .

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

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

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

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

6. Нажмите Введите .

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

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

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

1. org/ListItem»>

   В образце рабочего листа щелкните ячейку E2

   .

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

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

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

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

1. Выберите любую ячейку в таблице, чтобы отобразить вкладку Table Tools > Design на ленте.

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

В нашем примере данных мы использовали имя DeptSales .

Используйте следующие правила для имен таблиц:

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

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

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

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

• org/ListItem»>

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

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

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

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

=СУММ(Отдел продаж[[#Итоги],[Сумма продаж]],Отдел продаж[[#Данные],[Сумма комиссии]])

Эта формула имеет следующие структурированные ссылочные компоненты:

• Имя таблицы:
  DeptSales — это пользовательское имя таблицы. Он ссылается на данные таблицы без каких-либо заголовков или итоговых строк. Вы можете использовать имя таблицы по умолчанию, например Table1, или изменить его, чтобы использовать собственное имя.

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

• Спецификатор элемента:
  [#Totals] и [#Data] — это специальные спецификаторы элементов, которые относятся к определенным частям таблицы, например к итоговой строке.

• Спецификатор таблицы:
  [[#Totals],[Sales Amount]] и [[#Data],[Commission Amount]] — это спецификаторы таблицы, представляющие внешние части структурированной ссылки. Внешние ссылки следуют за именем таблицы и заключаются в квадратные скобки.

• org/ListItem»>

  Структурированный номер:
  (DeptSales[[#Totals],[Sales Amount]] и DeptSales[[#Data],[Commission Amount]] — это структурированные ссылки, представленные строкой, которая начинается с имени таблицы и заканчивается спецификатором столбца.

Для создания или редактирования структурированных ссылок вручную используйте следующие правила синтаксиса:

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

• org/ListItem»>

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

Используйте квадратные скобки вокруг заголовков столбцов со специальными символами     Если есть специальные символы, весь заголовок столбца должен быть заключен в квадратные скобки, что означает, что в спецификаторе столбца требуются двойные скобки. Например: =DeptSalesFYSummary[[Общая сумма в долларах США]]

Вот список специальных символов, для которых в формуле нужны дополнительные скобки:

• Вкладка

• org/ListItem»>

  Перевод строки

• Возврат каретки

• Запятая (,)

• Двоеточие (:)

• Период (.)

• Кронштейн левый ([)

• Кронштейн правый (])

• org/ListItem»>

  Знак фунта (#)

• Одинарная кавычка (‘)

• Двойная кавычка («»)

• 9)

• Амперсанд (&)

• Звездочка (*)

• Знак плюс (+)

• org/ListItem»>

  Знак равенства (=)

• Знак минус (-)

• Символ больше (>)

• Символ меньше (<)

• Знак деления (/)

Вот список специальных символов, для которых требуется escape-символ (‘) в формуле:

Используйте пробел для улучшения удобочитаемости в структурированном справочнике     Вы можете использовать символы пробела, чтобы улучшить читаемость структурированной ссылки. Например: =ОтделПродаж[[Продавец]:[Регион] ] или =ОтделПродаж[[#Заголовки], [#Данные], [% Комиссии]]

Рекомендуется использовать один пробел:

Эталонные операторы

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

Спецификаторы специальных позиций

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

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

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

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

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

Вот несколько способов использования структурированных ссылок.

Стратегии работы со структурированными ссылками

При работе со структурированными ссылками учитывайте следующее.

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

• Решите, создавать ли структурированные ссылки для таблиц в полувыборках     По умолчанию при создании формулы щелчок по диапазону ячеек в таблице частично выделяет ячейки и автоматически вводит структурированную ссылку вместо диапазона ячеек в формуле. Такое полувыборочное поведение значительно упрощает ввод структурированной ссылки. Вы можете включить или выключить это поведение, выбрав или сняв Использовать имена таблиц в формулах флажок в диалоговом окне Файл > Параметры > Формулы > Работа с формулами .

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

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

• Отключить заголовки столбцов     Вы можете включать и выключать заголовки столбцов таблицы из таблицы Дизайн > Строка заголовка . Если вы отключите заголовки столбцов таблицы, это не повлияет на структурированные ссылки, в которых используются имена столбцов, и вы по-прежнему сможете использовать их в формулах. Структурированные ссылки, которые ссылаются непосредственно на заголовки таблицы (например, =DeptSales[[#Headers],[%Commission]] ), приведут к #REF.

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

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

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

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

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Связанные темы

Обзор таблиц Excel

Видео: создание и форматирование таблицы Excel

Суммируйте данные в таблице Excel

Формат таблицы Excel

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

Фильтрация данных в диапазоне или таблице

Преобразование таблицы в диапазон

Проблемы совместимости таблиц Excel

Экспорт таблицы Excel в SharePoint

Обзоры формул в Excel

Обзор таблиц Excel — служба поддержки Майкрософт

Excel

Импорт и анализ данных

Столы

Столы

Обзор таблиц Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Больше. ..Меньше

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

Примечание. Таблицы Excel не следует путать с таблицами данных, которые являются частью набора команд анализа «что, если». Дополнительные сведения о таблицах данных см. в разделе Вычисление нескольких результатов с помощью таблицы данных.

Узнайте об элементах таблицы Excel

Таблица может включать следующие элементы:

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

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

• Строки с полосами     Попеременное затенение или полоса в строках помогает лучше различать данные.

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

• org/ListItem»>

  Итоговая строка     После добавления итоговой строки в таблицу Excel предоставляет раскрывающийся список Автосумма для выбора из таких функций, как СУММ, СРЗНАЧ и т. д. Когда вы выберете один из этих параметров, таблица автоматически преобразует их в функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая будет игнорировать строки, которые по умолчанию были скрыты фильтром. Если вы хотите включить в свои расчеты скрытые строки, вы можете изменить аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

  Дополнительные сведения см. также в разделе Суммирование данных в таблице Excel.

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

  Другие способы изменения размера таблицы см. в разделе Изменение размера таблицы путем добавления строк и столбцов.

Создать таблицу

Вы можете создать в электронной таблице столько таблиц, сколько захотите.

Чтобы быстро создать таблицу в Excel, выполните следующие действия:

1. Выберите ячейку или диапазон данных.

2. Выберите Главная > Форматировать как таблицу .

3. Выберите стиль таблицы.

4. org/ListItem»>

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

Также посмотрите видео о создании таблицы в Excel.

Эффективная работа с табличными данными

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

• Использование структурированных ссылок     Вместо использования ссылок на ячейки, таких как A1 и R1C1, вы можете использовать структурированные ссылки, которые ссылаются на имена таблиц в формуле. Дополнительные сведения см. в разделе Использование структурированных ссылок с таблицами Excel.

• org/ListItem»>

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

Экспорт таблицы Excel на сайт SharePoint

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

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. также

Отформатировать таблицу Excel

Проблемы с совместимостью таблиц Excel

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

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

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

• Таблица данных в Excel
• Создать одну таблицу переменных данных
• Создание таблицы данных с двумя переменными
• Таблица данных для оценки нескольких формул
• Таблицы данных Excel — 3 вещи, которые вы должны знать
• Удалить таблицу данных в Excel
• Редактировать результаты таблицы данных
• Пересчитать таблицу данных вручную

Что такое таблица данных в Excel?

В Microsoft Excel 9Таблица данных 0257 — это один из инструментов анализа «что, если», который позволяет опробовать различные входные значения для формул и посмотреть, как изменения в этих значениях влияют на вывод формул.

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

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

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

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

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

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

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

• B8 содержит формулу FV, которая вычисляет конечный баланс.
• B2 — это переменная, которую вы хотите протестировать (начальные инвестиции).

А теперь давайте проведем простой анализ «что, если», чтобы увидеть, каковы будут ваши сбережения через 5 лет, в зависимости от суммы ваших первоначальных инвестиций, в диапазоне от 1000 до 6000 долларов.

Вот шаги для создания таблицы данных с одной переменной:

1. Введите значения переменных либо в один столбец, либо в одну строку. В этом примере мы собираемся создать таблицу данных , ориентированную на столбцы , поэтому мы вводим значения наших переменных в столбце (D3:D8) и оставляем по крайней мере один пустой столбец справа для результатов.
2. Введите формулу в ячейку на одну строку выше и на одну ячейку справа от значений переменных (в нашем случае E2). Или свяжите эту ячейку с формулой в исходном наборе данных (если вы решите изменить формулу в будущем, вам нужно будет обновить только одну ячейку). Мы выбираем последний вариант и вводим эту простую формулу в E2: = B8
   

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

3. Выберите диапазон таблицы данных, включая формулу, ячейки со значениями переменных и пустые ячейки для результатов (D2:E8).
4. Перейдите на вкладку Data > группу Data Tools , нажмите кнопку What-If Analysis , а затем нажмите Data Table…
5. В диалоговом окне Data Table щелкните в ячейке ввода столбца (поскольку наши значения Investment находятся в столбце) и выберите переменную ячейку, указанную в вашей формуле. В этом примере мы выбираем B3, который содержит первоначальную стоимость инвестиций.
6. Нажмите OK , и Excel немедленно заполнит пустые ячейки результатами, соответствующими значению переменной в той же строке.
7. Примените желаемый числовой формат к результатам ( Валюта в нашем случае), и все готово!

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

Таблица данных, ориентированная на строки

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

1. Введите значения переменных в строке, оставив как минимум один пустой столбец слева (для формулы) и одну пустую строку ниже (для результатов). ). В этом примере мы вводим значения переменных в ячейки F3:J3.
2. Введите формулу в ячейку, которая находится на один столбец левее первого значения переменной и на одну ячейку ниже (в нашем случае E4).
3. Создайте таблицу данных, как описано выше, но введите входное значение (B3) в поле Строка ввода :
4. Нажмите OK , и вы получите следующий результат:

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

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

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

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

1. Введите формулу в пустую ячейку или свяжите эту ячейку с исходной формулой. Убедитесь, что у вас достаточно пустых столбцов справа и пустых строк ниже, чтобы вместить значения ваших переменных. Как и прежде, мы связываем ячейку E2 с исходной формулой FV, которая вычисляет баланс: =B8
2. Введите один набор входных значений под формулой в том же столбце (инвестиционные значения в E3:E8).
3. Введите другой набор значений переменных справа от формулы в той же строке (количество лет в F2:h3).

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

4. Выберите весь диапазон таблицы данных, включая формулу, строку и столбец значений переменных, а также ячейки, в которых будут отображаться вычисляемые значения. Мы выбираем диапазон E2:H8.
5. Создайте таблицу данных уже знакомым способом: Данные вкладка > Анализ «что если» кнопка > Таблица данных…
6. В ячейке ввода строки введите ссылку на входную ячейку для значений переменных в строке (в этом примере это ячейка B6, содержащая значение лет ).
7. В поле ввода столбца введите ссылку на входную ячейку для значений переменных в столбце (B3, содержащее значение Initial Investment ).
8. Щелкните OK .
9. При необходимости отформатируйте выходные данные так, как вам нужно (применив формат Валюта в нашем случае), и проанализируйте результаты:

Таблица данных для сравнения нескольких результатов

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

• Справа первой формулы в случае вертикальной таблицы данных , организованной в столбцах
• Под первой формулой в случае таблицы данных по горизонтали , организованной в строки

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

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

1. В ячейке B10 вычислите процентов по этой формуле: =B8-B3
2. Расположите исходные данные таблицы данных, как мы делали ранее: значения переменных в D3:D8 и E2 связаны с B8 (формула Balance ).
3. Добавьте еще один столбец в диапазон таблицы данных (столбец F) и свяжите F2 с B10 ( проценты формула):
4. Выберите диапазон расширенной таблицы данных (D2:F8).
5. Откройте диалоговое окно Data Table , щелкнув вкладку Data > What-If Analysis > Data Table…
6. В поле Column Input Cell введите ячейку ввода (B3) и нажмите OK .

Вуаля, теперь вы можете наблюдать влияние ваших значений переменных на обе формулы:

Таблица данных в Excel — 3 вещи, которые вы должны знать

Чтобы эффективно использовать таблицы данных в Excel, имейте в виду эти 3 простых факта:

1. Для успешного создания таблицы данных входные ячейки должны быть на тот же лист , что и таблица данных.
2. Microsoft Excel использует функцию TABLE(row_input_cell, colum_input_cell) для вычисления результатов таблицы данных:
   • В таблице данных с одной переменной один из аргументов опущен в зависимости от макета (ориентированного на столбцы или на строки). Например, в нашей горизонтальной таблице данных с одной переменной формула имеет вид 9.0085 = ТАБЛИЦА (, B3) , где B3 — ячейка ввода столбца.
   • В таблице данных с двумя переменными оба аргумента на месте. Например, = ТАБЛИЦА(B6, B3) , где B6 — ячейка ввода строки, а B3 — ячейка ввода столбца.

   Функция ТАБЛИЦА введена как формула массива. Чтобы убедиться в этом, выберите любую ячейку с вычисленным значением, посмотрите на строку формул и обратите внимание на {фигурные скобки} вокруг формулы. Однако это не обычная формула массива — вы не можете ввести ее в строку формул и не можете редактировать существующую. Это просто «для галочки».

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

Как удалить таблицу данных в Excel

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

Однако можно легко очистить весь массив от полученных значений. Вот как:

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

Готово! 🙂

Как редактировать результаты таблицы данных

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

1. Выберите все полученные ячейки.
2. Удалить формулу ТАБЛИЦА в строке формул.
3. Введите нужное значение и нажмите Ctrl + Enter.

Это вставит одно и то же значение во все выбранные ячейки:

После того, как формула ТАБЛИЦА исчезнет, ​​прежняя таблица данных станет обычным диапазоном, и вы сможете редактировать любую отдельную ячейку в обычном режиме.

Как пересчитать таблицу данных вручную

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

Для этого перейдите на вкладку Формулы > группу Расчет , нажмите кнопку Параметры расчета , а затем нажмите Автоматически кроме таблиц данных .

Это отключит автоматические расчеты таблицы данных и ускорит пересчет всей книги.

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

Вот как вы создаете и используете таблицу данных в Excel. Чтобы поближе ознакомиться с примерами, обсуждаемыми в этом руководстве, вы можете загрузить образец рабочей книги Excel Data Tables. Я благодарю вас за чтение и буду рад видеть вас снова на следующей неделе!

Вас также может заинтересовать

Структурированные ссылки в таблицах Excel

В этом руководстве объясняются основы структурированных ссылок Excel и приводятся некоторые приемы их использования в реальных формулах.

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

• Что такое структурированные ссылки в Excel?
• Как создать структурированную ссылку в Excel
• Синтаксис ссылки на таблицу Excel
• Структурированные ссылки Excel — примеры формул
• Абсолютные структурированные ссылки в формулах Excel

Структурированная ссылка Excel

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

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

Например, чтобы суммировать значения в ячейках B2:B5, вы используете функцию СУММ с обычной ссылкой на диапазон:

=СУММ(B2:B5)

Чтобы сложить числа в столбце «Продажи» таблицы 1 используется структурированная ссылка:

=СУММ(Таблица1[Продажи])

Основные возможности структурированных ссылок

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

Легко создается

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

Устойчивость и автоматическое обновление

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

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

Можно использовать внутри и вне таблицы

Структурированные ссылки можно использовать в формулах как внутри, так и вне таблицы Excel, что упрощает поиск таблиц в больших книгах.

Автозаполнение формул (вычисляемые столбцы)

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

Как создать структурированную ссылку в Excel

Создать структурированную ссылку в Excel очень просто и интуитивно понятно.

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

Чтобы создать структурированную ссылку, вам нужно сделать следующее:

1. Начните вводить формулу как обычно, начиная со знака равенства (=).
2. Когда дело доходит до первой ссылки, выберите соответствующую ячейку или диапазон ячеек в вашей таблице. Excel подберет имена столбцов и автоматически создаст для вас соответствующую структурированную ссылку.
3. Введите закрывающую скобку и нажмите Enter. Если формула создается внутри таблицы, Excel автоматически заполняет весь столбец той же формулой.

В качестве примера давайте просуммируем количество продаж за 3 месяца в каждой строке нашей типовой таблицы с именем 9.0089 Продажи . Для этого мы набираем =СУММ( в E2, выбираем B2:D2, вводим закрывающую скобку и нажимаем Enter:

В результате весь столбец E автоматически заполняется этой формулой:

=СУММ( Sales[@[Jan]:[Mar]])

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

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

1. После открывающей скобки начните вводить имя таблицы. Когда вы наберете первую букву, Excel покажет все совпадающие имена. При необходимости введите еще пару букв, чтобы сузить список.
2. С помощью клавиш со стрелками выберите имя таблицы в списке.
3. Дважды щелкните выбранное имя или нажмите клавишу TAB, чтобы добавить его в формулу.
4. Введите закрывающую скобку и нажмите Enter.

Например, чтобы найти наибольшее число в нашей таблице образцов, мы начинаем вводить формулу MAX, после открывающей скобки набираем «s», выбираем Sales в списке и нажмите клавишу Tab или дважды щелкните имя.

В результате имеем такую ​​формулу:

=МАКС(Продажи)

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

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

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

В качестве примера разберем следующую формулу, которая складывает итоги столбцов Юг и Север в таблице Регионы :

Ссылка включает три компонента:

Имя таблицы

5

1. Спецификатор элемента
2. Спецификаторы столбцов

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

Имя таблицы

Имя таблицы ссылается только на данные таблицы , без строки заголовка или итоговых строк. Это может быть имя таблицы по умолчанию, например Table1 , или произвольное имя, например Regions . Чтобы дать пользовательское имя вашей таблице, выполните следующие действия.

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

Спецификатор столбца

Спецификатор столбца ссылается на данные в соответствующем столбце без строки заголовка и строки итогов. Спецификатор столбца представлен именем столбца, заключенным в скобки, например. [Юг].

Для ссылки на несколько смежных столбцов используйте оператор диапазона, например [[Юг]:[Восток]].

Спецификатор элемента

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

Спецификатор элемента	Относится к
[#Все]	Вся таблица, включая данные таблицы, заголовки столбцов и итоговую строку.
[#Данные]	Строки данных.
[#Заголовки]	Строка заголовка (заголовки столбцов).
[#Всего]	Итоговая строка. Если итоговой строки нет, возвращается null.
[@имя_столбца]	Текущая строка, т. е. та же строка, что и формула.

Обратите внимание, что знак решетки (#) используется со всеми спецификаторами элементов, кроме текущей строки. Для ссылки на ячейки в той же строке, где вы вводите формулу, Excel использует символ @, за которым следует имя столбца.

Например, чтобы добавить числа в столбцы Юг и Запад текущей строки, используйте следующую формулу:

=СУММ(Регионы[@Юг], Регионы[@Запад])

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

=СУММ(Регионы[@[Продажи на юге]], Регионы[@[Продажи на Западе]])

Операторы структурированных ссылок

Следующие операторы позволяют комбинировать различные спецификаторы и добавлять еще большую гибкость вашим структурированным ссылкам.

Оператор диапазона (двоеточие)

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

Например, приведенная ниже формула суммирует числа во всех столбцах между Юг и Восток .

=СУММ(Регионы[[Юг]:[Восток]])

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

Чтобы сослаться на несмежные столбцы, разделите спецификаторы столбцов запятыми.

Например, вот как можно суммировать строки данных в Юг и Запад колонки.

=СУММ(Регионы[Юг], Регионы[Запад])

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

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

Например, чтобы вернуть значение на пересечении строки Total и столбца West , используйте следующую ссылку:

=Regions[#Totals] Regions[[#All],[West]]

Обратите внимание, что в этом случае требуется спецификатор [#All], поскольку спецификатор столбца не включает итоговую строку. Без него формула вернула бы #NULL!.

Правила синтаксиса ссылок на таблицы

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

1.

Заключите спецификаторы в квадратные скобки

Все спецификаторы столбцов и специальных элементов должны быть заключены в [квадратные скобки].

Спецификатор, содержащий другие спецификаторы, должен быть заключен во внешние скобки. Например, Регионы[[Юг]:[Восток]].

2. Разделяйте внутренние спецификаторы запятыми

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

Например, чтобы вернуть заголовок столбца Юг , вы вводите запятую между [#Заголовки] и [Юг] и заключаете всю эту конструкцию в дополнительный набор квадратных скобок:

=Регионы[[# Заголовки],[Юг]]

3. Не используйте кавычки вокруг заголовков столбцов

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

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

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

Например, для заголовка столбца «Элемент №» спецификатором является [Элемент ‘#].

5. Используйте пробелы, чтобы сделать структурированные ссылки более удобочитаемыми

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

=СРЗНАЧ(Регионы[Юг], Регионы[Запад], Регионы[Север])

Ссылки на таблицы Excel — примеры формул

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

Найти количество строк и столбцов в таблице Excel

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

СТОЛБЦ( таблица )

СТРОКИ( таблица )

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

=СТРОКИ(Продажи)

Чтобы включить заголовок и итоговых строк в подсчет, используйте спецификатор [#ALL]:

=СТРОКИ(Продажи[#094])

На приведенном ниже снимке экрана показаны все формулы в действии:

Подсчет пустых и непустых значений в столбце

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

Для подсчета пробелов в столбце используйте функцию СЧИТАТЬПУСТОТЫ. Чтобы подсчитать непустые ячейки в столбце, используйте функцию COUNTA.

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

Бланки:

= Countblank (Sales [Jan])

не блунки:

= Counta (Sales [Jan])

, чтобы подсчитать некровные ячейки в Visible Rows в отфильтрованную таблицу, используйте функцию ПРОМЕЖУТОЧНЫЙ ИТОГ с номером_функции, установленным на 103:

=ПРОМЕЖУТОЧНЫЙ ИТОГ(103,Продажи[январь])

Сумма в таблице Excel

Самый быстрый способ сложить числа в таблице Excel — включить вариант Итоговая строка. Для этого щелкните правой кнопкой мыши любую ячейку в таблице, выберите Таблица и щелкните Строка итогов . Итоговая строка сразу же появится в конце вашей таблицы.

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

Это вставит формулу ПРОМЕЖУТОЧНЫЙ ИТОГ, которая суммирует значения только в видимых строках. , игнорируя отфильтрованные строки:

=ПРОМЕЖУТОЧНЫЙ ИТОГ(109,[январь])

Обратите внимание, что эта формула работает только в строке Итого . Если вы попытаетесь вручную вставить его в строку данных, это создаст циклическую ссылку и в результате вернет 0. Формула SUM со структурированной ссылкой не будет работать по той же причине:

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

=СУММ(B2:B5)

Вне таблицы формула СУММ со структурированной ссылкой прекрасно работает: функция суммирует значения во всех строках, видимых и скрытых.

Относительные и абсолютные структурированные ссылки в Excel

По умолчанию структурированные ссылки Excel ведут себя следующим образом:

• Несколько столбцов ссылок равны абсолютные и не меняются при копировании формул.
• Ссылки на один столбец являются относительными и изменяются при перетаскивании по столбцам. При копировании/вставке соответствующей командой или сочетаниями клавиш (Ctrl+C и Ctrl+V) они не изменяются.

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

Абсолютная структурированная ссылка на один столбец

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

Относительная ссылка на столбец (по умолчанию)

таблица[столбец]

Абсолютная ссылка на столбец идентификатор столбца символом @:

table[@[column]:[column]]

Чтобы увидеть, как относительные и абсолютные ссылки на таблицы работают на практике, рассмотрим следующий пример.

Предположим, вы хотите сложить данные о продажах определенного продукта за 3 месяца. Для этого мы вводим название целевого продукта в какую-либо ячейку (в нашем случае F2) и используем функцию СУММЕСЛИ, чтобы получить общее количество продаж за январь :

=СУММЕСЛИ(Продажи[Элемент], $F$2, Продажи[ янв])

Проблема в том, что когда мы перетаскиваем формулу вправо для расчета итогов за два других месяца, ссылка [Элемент] изменяется, и формула ломается:

Чтобы исправить это, сделайте ссылку [Элемент] абсолютной, но оставьте [Ян] относительной:

=СУММЕСЛИ(Продажи[[Товар]:[Товар]], $F$2, Продажи[Янв])

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

Относительная структурированная ссылка на несколько столбцов

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