Как составить таблицу функций в Excel

Обновлено: 21.11.2024

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

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

Разделы:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=Sum(Данные о продажах[Продажи])

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

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

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

Вся таблица

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

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

Создайте образец рабочего листа

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

Введите значение, которое хотите найти, в ячейку E2. Формулу можно ввести в любую пустую ячейку на том же листе.

Определения терминов

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

Вся таблица поиска

Значение, которое нужно найти в первом столбце Table_Array.

Lookup_Array
-или-
Lookup_Vector

Диапазон ячеек, содержащий возможные значения поиска.

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

3 (третий столбец в Table_Array)

Result_Array
-или-
Result_Vector

Диапазон, содержащий только одну строку или столбец. Он должен быть того же размера, что и Lookup_Array или Lookup_Vector.

Логическое значение (ИСТИНА или ЛОЖЬ). Если TRUE или опущено, возвращается приблизительное совпадение. Если FALSE, будет найдено точное совпадение.

Это количество столбцов слева или справа, на которое должна ссылаться верхняя левая ячейка результата. Например, «5» в качестве аргумента Offset_Col указывает, что верхняя левая ячейка в ссылке находится на пять столбцов справа от ссылки.Offset_Col может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Функции

ПРОСМОТР()

Функция ПРОСМОТР находит значение в одной строке или столбце и сопоставляет его со значением в той же позиции в другой строке или столбце.

Ниже приведен пример синтаксиса формулы ПРОСМОТР:

=ПРОСМОТР(Искомое_Значение,Искомый_Вектор,Результативный_Вектор)

Следующая формула определяет возраст Марии в образце рабочего листа:

=ПРОСМОТР(E2,A2:A5,C2:C5)

Формула использует значение "Мария" в ячейке E2 и находит "Мария" в векторе поиска (столбец A). Затем формула сопоставляется со значением в той же строке результирующего вектора (столбец C). Поскольку "Мария" находится в строке 4, функция ПРОСМОТР возвращает значение из строки 4 в столбце C (22).

ПРИМЕЧАНИЕ. Функция ПРОСМОТР требует, чтобы таблица была отсортирована.

Для получения дополнительных сведений о функции ПРОСМОТР щелкните следующий номер статьи базы знаний Майкрософт:

ВПР()

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

Ниже приведен пример синтаксиса формулы ВПР:

=VLOOKUP(Lookup_Value,Table_Array,Col_Index_Num,Range_Lookup)

Следующая формула определяет возраст Марии в образце рабочего листа:

=ВПР(E2,A2:C5,3,ЛОЖЬ)

В формуле используется значение "Мария" в ячейке E2, а "Мария" находится в крайнем левом столбце (столбец A). Затем формула сопоставляется со значением в той же строке в Column_Index. В этом примере используется «3» в качестве Column_Index (столбец C). Поскольку "Мария" находится в строке 4, функция ВПР возвращает значение из строки 4 в столбце C (22).

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

ИНДЕКС() и ПОИСКПОЗ()

Вы можете использовать функции ИНДЕКС и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании ПРОСМОТР или ВПР.

Ниже приведен пример синтаксиса, который объединяет ИНДЕКС и ПОИСКПОЗ для получения тех же результатов, что и ПРОСМОТР и ВПР в предыдущих примерах:

=INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)

Следующая формула определяет возраст Марии в образце рабочего листа:

=ИНДЕКС(A2:C5,ПОИСКПОЗ(E2,A2:A5,0),3)

Формула использует значение "Мария" в ячейке E2 и находит "Мария" в столбце A. Затем она сопоставляется со значением в той же строке в столбце C. Поскольку "Мария" находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

СМЕЩ() и ПОИСКПОЗ()

Вы можете использовать функции OFFSET и MATCH вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

Ниже приведен пример синтаксиса, который объединяет OFFSET и MATCH для получения тех же результатов, что и LOOKUP и VLOOKUP:

=OFFSET(top_cell,MATCH(Lookup_Value,Lookup_Array,0),Offset_Col)

Эта формула определяет возраст Марии в образце рабочего листа:

=СМЕЩЕНИЕ(A1,ПОИСКПОЗ(E2,A2:A5,0),2)

Формула использует значение "Мария" в ячейке E2 и находит "Мария" в столбце A. Затем формула сопоставляет значение в той же строке, но на два столбца правее (столбец C). Поскольку "Мария" находится в столбце A, формула возвращает значение в строке 4 столбца C (22).

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

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

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

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

Примечание. Вы можете выполнять вычисления быстрее с таблицами данных и Visual Basic для приложений (VBA). Дополнительные сведения см. в статье Таблицы данных Excel «что, если: более быстрые расчеты с помощью VBA».

Типы анализа возможных вариантов

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

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

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

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

Таблицы данных с одной переменной

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

На следующем рисунке ячейка D2 содержит формулу платежа =PMT(B3/12,B4,-B5), которая относится к входной ячейке B3.

Таблицы данных с двумя переменными

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

На следующем рисунке ячейка C2 содержит формулу платежа =PMT(B3/12,B4,-B5), которая использует две входные ячейки, B3 и B4.

Вычисления таблицы данных

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

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

Выполните следующие действия:

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

Выполните одно из следующих действий:

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

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

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

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

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

На вкладке "Данные" нажмите "Анализ "что, если"" > "Таблица данных" (в группе "Инструменты данных" или "Прогноз" в Excel 2016).

Выполните одно из следующих действий:

Если таблица данных ориентирована на столбцы, введите ссылку на ячейку для входной ячейки в поле Входная ячейка столбца. На рисунке выше ячейка ввода — B3.

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

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

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

Выполните следующие действия

Выполните одно из следующих действий:

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

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

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

На вкладке "Данные" нажмите "Анализ "что если"" > "Таблица данных" (в группе "Инструменты данных" или "Прогноз" в Excel 2016).

Выполните одно из следующих действий:

Если таблица данных ориентирована на столбцы, введите ссылку на ячейку для входной ячейки в поле Ячейка ввода столбца.

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

Выполните следующие действия:

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

В следующем примере, где начальные значения формулы вводятся в ячейки B3, B4 и B5, вы вводите формулу =PMT(B3/12,B4,-B5) в ячейку C2.

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

В этом случае введите разные процентные ставки в ячейки C3, C4 и C5.

Введите второй список в той же строке, что и формула, справа от нее.

Введите условия кредита (в месяцах) в ячейки D2 и E2.

Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых вы хотите вычислить значения (D3:E5).< /p>

В этом случае выберите диапазон C2:E5.

На вкладке "Данные" в группе "Инструменты данных" или "Прогноз" (в Excel 2016) нажмите "Анализ "что, если"" > "Таблица данных" (в группе "Инструменты данных" или "Прогноз" в Excel 2016).

В поле "Ячейка ввода строки" введите ссылку на ячейку ввода для входных значений в строке.
Введите ячейку B4 в поле ввода строки.

В поле Ячейка ввода столбца введите ссылку на ячейку ввода для входных значений в столбце.
Введите B3 в поле ввода столбца.

Пример таблицы данных с двумя переменными

Таблица данных с двумя переменными может показать, как различные комбинации процентных ставок и условий кредита повлияют на ежемесячный платеж по ипотеке. На этом рисунке ячейка C2 содержит формулу платежа =PMT(B3/12,B4,-B5), которая использует две входные ячейки, B3 и B4.

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

Выполните следующие действия, чтобы повысить производительность вычислений:

Нажмите "Файл" > "Параметры" > "Формулы".

В разделе "Параметры расчета" в разделе "Расчет" нажмите "Автоматически, кроме таблиц данных".

Совет. При необходимости на вкладке "Формулы" нажмите стрелку рядом с параметрами расчета, а затем выберите "Автоматические таблицы данных кроме данных" (в группе "Расчет").

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

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

Решатель Excel

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

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

Здесь таблица данных представляет собой диапазон ячеек B2:D8. Вы можете изменить значение в B4, сумму кредита и ежемесячные платежи в столбце D автоматически обновляются. Используя процентную ставку 3,75 %, D2 возвращает ежемесячный платеж в размере 1 042,01 доллара США по следующей формуле: =ПЛТ(C2/12,$B$3,$B$4).

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

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

В этой рабочей книге ячейка D2 содержит формулу платежа =PMT(C2/12,$B$3,$B$4). Ячейка B3 — это переменная ячейка, в которую вы можете вставить другую продолжительность срока (количество ежемесячных периодов оплаты). В ячейку D2 функция ПЛТ подставляет процентную ставку 3,75 %/12, 360 месяцев и кредит в размере 225 000 долларов США, после чего рассчитывает ежемесячный платеж в размере 1 042,01 доллара США.

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

В этой интерактивной рабочей книге ячейка C3 содержит формулу платежа =PMT($B$3/12,$B$2,B4), в которой используются две переменные ячейки, B2 и B3. В ячейку C2 функция ПЛТ подставляет процентную ставку 3,875%/12, 360 месяцев и кредит в размере 225 000 долл. США и рассчитывает ежемесячный платеж в размере 1 058,03 доллара США.

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

Вы всегда можете обратиться к эксперту в техническом сообществе 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. Если у вас другая версия, представление может немного отличаться, но, если не указано иное, функциональность такая же.

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

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

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

Вы также можете добавить столбец таблицы на вкладке "Главная". Просто нажмите на стрелку «Вставка» > «Вставить столбцы таблицы слева».

Введите нужную формулу и нажмите Enter.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В разделе "Параметры автозамены" нажмите "Параметры автозамены".

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

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

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

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

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

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

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

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

Читайте также: