Excel формула в таблице: Функции Excel (по категориям) — Служба поддержки Майкрософт

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

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

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

Примечание:  Формулы в таблицах Word и Outlook — это тип кода поля. Дополнительные сведения о кодах полей см. в разделе См. также.

В этой статье

• 
  Вставка формулы в ячейку таблицы

• 
  Обновление результатов формул

  • 
    Обновление результатов отдельных формул

  • 
    Обновление результатов всех формул в таблице

  • 
    Обновление всех формул в документе

• 
  Примеры: сложение чисел в таблице с помощью позиционных аргументов

• 
  Доступные функции

• 
  Использование закладок и ссылок на ячейки в формуле

  • 
    Ссылки формата RnCn

  • 
    Ссылки формата A1

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

org/ItemList»>

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

2. В разделе Работа с таблицами на вкладке Макет в группе Данные нажмите кнопку Формула.

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

Обновление результатов вычислений

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

Кроме того, поддерживается ручное обновление следующих элементов:

• результатов отдельных формул;

• результатов всех формул в отдельной таблице;

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

Обновление результатов отдельных формул

1. Выделите формулы, которые необходимо обновить. Чтобы выделить несколько формул, удерживайте нажатой клавишу CTRL.

2. Выполните одно из указанных ниже действий.

Обновление результатов всех формул в таблице

Обновление всех формул в документе

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

1. Нажмите клавиши CTRL+A.

2. Нажмите клавишу F9.

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

Позиционные аргументы (LEFT, RIGHT, ABOVE, BELOW) можно использовать для следующих функций:

• org/ListItem»>

  AVERAGE

• COUNT

• MAX

• MIN

• PRODUCT

• SUM

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

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

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

2. В разделе Работа с таблицами на вкладке Макет в группе Данные нажмите кнопку Формула.

3. В диалоговом окне Формула выполните одно из указанных ниже действий.

Местонахождение складываемых чисел	Значение поля Формула
Над ячейкой	=SUM(ABOVE)
Под ячейкой	=SUM(BELOW)
Над ячейкой и под ней	=SUM(ABOVE;BELOW)
Слева от ячейки	=SUM(LEFT)
Справа от ячейки	=SUM(RIGHT)
Слева и справа от ячейки	=SUM(LEFT;RIGHT)
Слева от ячейки и над ней	=SUM(LEFT;ABOVE)
Справа от ячейки и над ней	=SUM(RIGHT;ABOVE)
Слева от ячейки и под ней	=SUM(LEFT;BELOW)
Справа от ячейки и под ней	=SUM(RIGHT,BELOW)

org/ItemList»>

1. Нажмите кнопку «ОК».

Доступные функции

Примечание:  Формулы, в которых используются позиционные аргументы (например, LEFT), не учитывают значения в строке заголовков.

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

Функция	Действие	Пример	Возвращаемое значение
ABS()	Определяет абсолютную величину числа в скобках	=ABS(-22)	22
AND()	Определяет, все ли аргументы внутри скобок являются истинными.	=AND(SUM(LEFT)<10;SUM(ABOVE)>=5)	1, если сумма значений, находящихся слева от формулы (в той же строке), меньше 10 исумма значений, находящихся над формулой (в том же столбце, исключая ячейки заголовка) больше или равна 5; в противном случае — 0.
AVERAGE()	Находит среднее арифметическое значение элементов, указанных в скобках.	=AVERAGE(RIGHT)	Среднее арифметическое всех значений, находящихся справа от ячейки с формулой (в той же строке).
COUNT()	Определяет количество элементов, указанных в скобках.	=COUNT(LEFT)	Количество значений, находящихся слева от ячейки с формулой (в той же строке).
DEFINED()	Устанавливает, определен ли аргумент внутри скобок. Возвращает значение 1, если аргумент определен и вычисляется без ошибок, или 0, если аргумент не определен или возвращает ошибку.	=DEFINED(валовый_доход)	1, если элемент «валовой_доход» определен и вычисляется без ошибок; в противном случае — 0.
FALSE	Не имеет аргументов. Всегда возвращает значение 0.	=FALSE	0
IF()	Вычисляет первый аргумент. Если первый аргумент является истинным, возвращает второй аргумент; если ложным — третий. Примечание:  Необходимо задать все три аргумента.	=IF(SUM(LEFT)>=10;10;0)	10, если сумма значений, находящихся слева от формулы, больше или равна 10; в противном случае — 0.
INT()	Округляет значение в скобках до ближайшего целого числа (меньшего).	=INT(5,67)	5
MAX()	Возвращает наибольшее значение среди элементов, указанных в скобках.	=MAX(ABOVE)	Наибольшее из значений, находящихся в ячейках над формулой (за исключением ячеек строк заголовков).
MIN()	Возвращает наименьшее значение среди элементов, указанных в скобках.	=MIN(ABOVE)	Наименьшее из значений, находящихся в ячейках над формулой (за исключением ячеек строк заголовков).
MOD()	Имеет два аргумента (числа или выражения, результатами которых являются числа). Возвращает остаток от деления первого аргумента на второй. Если остаток равен нулю (0), возвращает значение 0,0.	=MOD(4;2)	0,0
NOT()	Имеет один аргумент. Определяет истинность аргумента. Если аргумент является истинным, возвращает значение 0; если ложным — 1. В основном используется в функции IF.	=NOT(1=1)	0
OR()	Имеет два аргумента. Если хотя бы один из них является истинным, возвращает значение 1. Если оба аргумента являются ложными, возвращает значение 0. В основном используется в функции IF.	=OR(1=1;1=5)	1
PRODUCT()	Вычисляет произведение элементов, указанных в скобках.	=PRODUCT(LEFT)	Произведение всех значений, находящихся в ячейках слева от формулы.
ROUND()	Имеет два аргумента (первый аргумент должен быть числом или выражением, результатом которого является число, второй — целым числом или выражением, результатом которого является целое число). Округляет первый аргумент до заданного вторым аргументом количества десятичных разрядов. Если второй аргумент больше нуля (0), первый аргумент округляется до указанного количества десятичных разрядов (в меньшую сторону). Если второй аргумент равен нулю (0), первый аргумент округляется до ближайшего целого числа (в меньшую сторону). Если второй аргумент меньше нуля, первый аргумент округляется слева от десятичной запятой (в меньшую сторону).	=ROUND(123,456; 2) =ROUND(123,456; 0) =ROUND(123,456; -2)	123,46 123 100
SIGN()	Имеет один аргумент, который должен быть числом или выражением, результатом которого является число. Сравнивает указанный в скобках элемент с нулем (0). Если этот элемент больше нуля, возвращает значение 1; если равен нулю — 0; если меньше нуля — -1.	=SIGN(-11)	-1
SUM()	Вычисляет сумму элементов, указанных в скобках.	=SUM(RIGHT)	Сумма значений, находящихся в ячейках справа от формулы.
TRUE()	Имеет один аргумент. Определяет истинность аргумента. Если аргумент является истинным, возвращает значение 1, если ложным — 0. В основном используется в функции IF.	=TRUE(1=0)	0

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

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

В формуле также можно использовать ссылки на столбцы и строки. Существует два формата записи ссылок: RnCn и A1.

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

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

Для записи ссылки на строку, столбец или ячейку таблицы в формуле можно использовать формат RnCn. Здесь Rn соответствует n-ой строке, а Cn — n-му столбцу. Например, ссылка R1C2 указывает на ячейку в первой строке и втором столбце. Приведенная ниже таблица содержит примеры записи ссылок в таком формате.

Ячейка или диапазон	Формат записи ссылки
Весь столбец	Cn
Всю строку	Rn
Отдельная ячейка	RnCn
Строка, содержащая формулу	R
Столбец с формулой	C
Все ячейки между двумя заданными ячейками	RnCn:RnCn
Ячейка в таблице с закладкой	ИмяЗакладки RnCn
Диапазон ячеек в таблице с закладкой	ИмяЗакладки RnCn:RnCn

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

Для записи ссылки на ячейку, набор или диапазон ячеек можно использовать формат A1. Здесь буква соответствует столбцу, а цифра — строке ячейки. Первый столбец таблицы обозначен буквой «A», а первая строка — цифрой «1». Приведенная ниже таблица содержит примеры записи ссылок в таком формате.

Ячейка или диапазон	Формат записи ссылки
Ячейка в первом столбце и второй строке	A2
Первые две ячейки в первой строке	A1;B1
Все ячейки в первом столбце и первые две ячейки во втором столбце	A1:B2

Последнее обновление: 29. 08.2015

См. также

Коды полей в Word и Outlook

excel — Как при добавлении новых строк в таблицу автоматически включать их значения в формулу?

Вопрос задан
2 года назад

Изменён
1 месяц назад

Просмотрен
1k раз

Есть таблица в excel. Есть формулы,которые считают кол-во часов. Как мне сделать так,чтобы при добавлении строки , формула подсчета включала автоматически данные из добавленной строки?
Сумма считается простой формулой в ячейке . =СУММ(C13:C14) и т.п.

Есть скрин таблицы

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

Вот ссылка на таблицу https://www.dropbox.com/scl/fi/rswrd6ff4z5gea7o2wlda/.xls?dl=0&rlkey=ndgt9ah623k7j2hhj3bhvp6m4

• excel
• vba

18

Определяем положение последнего числа в столбце:

=СУММ(C3:ИНДЕКС(C:C;ПРОСМОТР(9E+307;C3:C1000;СТРОКА(C3:C1000))))

Сложно…

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

Можно задать статичный диапазон, на который не влияет добавление/удаление строк:

=СУММ(C3:ИНДЕКС(C:C;1000)

Но такой вариант не избавляет от лишних вычислений в пустых строках.

Функция СУММЕСЛИ умеет определять используемый диапазон, поэтому просматривать будет ровно столько строк, сколько нужно:

=СУММЕСЛИ(C:C;">0")

Или по значению другого столбца:

 =СУММЕСЛИ(B:B;"модуль1";C:C)

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

Если условия суммирования добавляются (например, значение других столбцов, диапазон дат…), можно использовать более продвнутую сестричку — СУММЕСЛИМН

2

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки

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

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

Что вы будете делать

Вы будете:

1. Добавлять итоги по строкам с помощью функции СУММ.
2. Добавить вычисляемые столбцы. Вы можете сделать это двумя разными способами:

1. Таблицы Excel: вы создаете таблицу с помощью инструментов таблицы Excel. Этот метод обеспечивает лучшую производительность при создании отчетов, но требует более продвинутых навыков работы с Excel. Мы рекомендуем использовать этот подход.

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

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

В этом руководстве используются данные QlikView, которые можно найти в файлах примеров . Вы также можете использовать собственные данные QlikView или Qlik Sense.

О вычислениях

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

В этом примере используются простые формулы Excel, но при необходимости можно вставить более сложные формулы. Вы также можете вставлять формулы Qlik. См.: Формулы Qlik Sense и QlikView .

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

Это первый способ создания вычисляемых столбцов. Вы добавите исходный объект QlikView в узел Таблицы, а затем создадите таблицу Excel.

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

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

1. Создайте новый отчет Excel или откройте существующий.

2. Щелкните правой кнопкой мыши узел Таблицы и выберите Добавить объекты.

3. Выберите диаграмму из списка. Например, добавьте Топ 10 продуктов. Нажмите «ОК».

4. Нажмите + слева, чтобы развернуть узел таблицы Ch284_Table — Top 10 Products.
5. По одному выберите каждый столбец в разделе Ch284_Table — Top 10 Products и снимите флажок «Сохранить исходные форматы».

6. Удерживая нажатой клавишу CTRL, выберите поля таблицы, которые вы хотите добавить на лист.

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

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

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

10. На левой панели щелкните Дополнительно. Перетащите тег удаления строки в ячейку в пустой строке под таблицей.

    Это удалит пустую строку из итогового отчета.

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

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

1. Выберите первую пустую ячейку справа от тегов полей.

2. Вставьте формулу =[@[Общий объем продаж]]-[@[Валовая прибыль]].

   Excel автоматически добавит этот новый столбец в таблицу.

3. Введите заголовок столбца. Например: Расходы.

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

Добавление итогов

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

1. Выберите пустую ячейку под таблицей. Например, Д8.

2. Введите в ячейку формулу СУММ. Диапазон функции СУММ должен включать:

   • Адрес ячейки в строке, содержащей тег открытия уровня.
   • Ячейка, содержащая тег поля.
   • Адрес как минимум одной пустой ячейки непосредственно под ней.

   В этом примере функция будет выглядеть так: =СУММ(D4:7).

3. Щелкните и перетащите эту формулу на E8 и F8.

4. Применить инструменты форматирования Excel к элементам строки.

   Например: Итоговые данные по общему объему продаж и валовой прибыли могут иметь формат «Валюта».

Предварительный просмотр отчета

Сделайте следующее:

1. Щелкните Предварительный просмотр.

   Excel запустит и отобразит ваш отчет.

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

3. Щелкните Сохранить и закрыть, чтобы сохранить шаблон и закрыть Редактор шаблонов.

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

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

Добавление уровня

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

1. Создайте новый отчет Excel или откройте существующий.
2. Щелкните правой кнопкой мыши узел «Уровни» и выберите «Добавить уровни».

3. Выберите диаграмму из списка. Например, добавьте Топ 10 продуктов. Нажмите «ОК».

4. Щелкните узел Ch284_Level — Top 10 Products и перетащите его на три пустые ячейки, выровненные по вертикали.

5. Щелкните + слева, чтобы развернуть узел уровня Ch284_Level — Top 10 Products.
6. По одному выберите каждый столбец в разделе Ch284_Level — Top 10 Products и снимите флажок «Сохранить исходные форматы».

7. Удерживая нажатой клавишу CTRL, выберите поля уровня, которые вы хотите добавить на лист.

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

8. Отформатируйте эти поля по своему усмотрению. Например, общий объем продаж можно отформатировать как валюту.

Добавление формул и заголовков

1. Выберите первую пустую ячейку справа от тегов полей. В этом примере F4.
2. Щелкните ячейку, введите =, затем щелкните ячейку Всего продаж.

3. Введите -, щелкните ячейку «Валовая прибыль» и нажмите «Ввод». Это позволит рассчитать затраты.

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

   Вы можете игнорировать ошибку #ЗНАЧ! в шаблоне. Это происходит потому, что формула Excel ссылается на ячейки, содержащие теги полей.

4. В ячейке G4 введите =F5/D5. Это позволит разделить расходы на продажи.

5. Отформатируйте ячейки формулы по своему усмотрению, используя форматирование Excel.

   Например, затраты в валюте и затраты/продажи в процентах.

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

   Отформатируйте эти заголовки по желанию.

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

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

   1. Адрес ячейки тега поля.
   2. Адрес пустой ячейки под ней.

   В этом примере функция будет выглядеть так: =СУММ(D4:D6).

9. Щелкните и перетащите эту формулу на E8 и F8.

10. Применить инструменты форматирования Excel к элементам строки.

    Например: итоговые данные по общему объему продаж и валовой прибыли могут быть отформатированы как денежная единица.

Предварительный просмотр отчета

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

1. Щелкните Предварительный просмотр.

   Excel запустит и отобразит ваш отчет.

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

3. Щелкните Сохранить и закрыть, чтобы сохранить шаблон и закрыть Редактор шаблонов.

Подробнее

• Отчеты Excel с уровнями
• Отчеты Excel с вложенными уровнями и промежуточными итогами

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

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

Уровень мастерства: Новичок

Смотрите на YouTube и подписывайтесь на наш канал

Загрузите файл Excel

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

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

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

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

Что происходит при создании вычисляемого столбца?

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

Если формула находится справа от таблицы, Excel будет:

1. Расширить таблицу с помощью авторасширения.
2. Заполните формулой все ячейки столбца.

Эти действия можно увидеть в раскрывающемся списке «История отмены».

Отменить автозаполнение

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

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

Маленькая стрелка раскрывающегося списка рядом с кнопкой «Отменить» открывает меню, в котором отображаются последние несколько действий, предпринятых Excel. На этом изображении вы можете видеть, что последним действием Excel было заполнение ячеек в столбце формулой. Таким образом, нажатие кнопки «Отменить» (или ввод Ctrl + Z ) один раз отменит это действие заполнения .

Автозаполнение временно отключается для столбца

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

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

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

Меню параметров автозамены

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

В этом меню есть три пункта.

1. Отменить вычисляемый столбец

Отменить вычисленный столбец выполняет то же самое, что мы видели выше, с помощью Ctrl + Z или Undo.

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

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

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

3. Управление параметрами автозамены

Управление параметрами автозамены открывает окно настроек автозамены, в котором можно включить или отключить вычисляемые столбцы (автозаполнение).

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

1. Перейдите на вкладку File на ленте.
2. Выберите Варианты .
3. Выберите Проверка .
4. Нажмите кнопку Параметры автозамены .
5. Выберите вкладку Автоформат при вводе (если она еще не выбрана).
6. Установите флажок Заполните формулы в таблицах для создания вычисляемых столбцов .
7. Нажал ОК .

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