Как работать с мастером формул excel кратко

Полные сведения о формулах в Excel

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

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

Важно: В этой статье мы обсудим XLOOKUP и ВПР, которые похожи. Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.

Создание формулы, ссылающейся на значения в других ячейках

1. Выделите ячейку.
2. Введите знак равенства » ocpAlert»>Примечание: Формулы в Excel начинаются со знака равенства.

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

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

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

Нажмите клавишу ВВОД. В ячейке с формулой отобразится результат вычисления.

Просмотр формулы

При вводе в ячейку формула также отображается в строке формул.

• Чтобы увидеть формулу в строке формул, выберите ячейку.

Ввод формулы, содержащей встроенную функцию

1. Выделите пустую ячейку.
2. Введите знак равенства «=», а затем — функцию. Например, чтобы получить общий объем продаж, нужно ввести «=СУММ».
3. Введите открывающую круглую скобку «(«.
4. Выделите диапазон ячеек, а затем введите закрывающую круглую скобку «)».
5. Нажмите клавишу ВВОД, чтобы получить результат.

Скачивание книги «Учебник по формулам»

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

Подробные сведения о формулах

Чтобы узнать больше об определенных элементах формулы, просмотрите соответствующие разделы ниже.

Части формулы Excel

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

Части формулы

1. Функции. Функция ПИ() возвращает значение числа пи: 3,142.

2. Ссылки. A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

4. Операторы. Оператор ^ (крышка) применяется для возведения числа в степень, а * (звездочка) — для умножения.

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

Константа представляет собой готовое (не вычисляемое) значение, которое всегда остается неизменным. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. выражение или его значение константами не являются. Если формула в ячейке содержит константы, а не ссылки на другие ячейки (например, имеет вид =30+70+110), значение в такой ячейке изменяется только после редактирования формулы. Обычно лучше помещать такие константы в отдельные ячейки, где их можно будет легко изменить при необходимости, а в формулах использовать ссылки на эти ячейки.

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

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

Стиль ссылок A1 По умолчанию Excel использует стиль ссылок A1, в котором столбцы обозначаются буквами (от A до XFD, не более 16 384 столбцов), а строки — номерами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Ячейка или диапазон	Использование
Ячейка на пересечении столбца A и строки 10	A10
Диапазон ячеек: столбец А, строки 10-20.	A10:A20
Диапазон ячеек: строка 15, столбцы B-E	B15:E15
Все ячейки в строке 5	5:5
Все ячейки в строках с 5 по 10	5:10
Все ячейки в столбце H	H:H
Все ячейки в столбцах с H по J	H:J
Диапазон ячеек: столбцы А-E, строки 10-20	A10:E20

Примечание: Если указанный лист содержит пробелы или числа, необходимо добавить апострофы (‘) до и после имени листа, например =’123′! A1 или =’Январь доход’! A1.

1. Относительные ссылки . Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2. Скопированная формула с относительной ссылкой
2. Абсолютные ссылки . Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1. Скопированная формула с абсолютной ссылкой
3. Смешанные ссылки . Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку имеет вид A$1, B$1 и т. д. Если положение ячейки с формулой изменяется, относительная ссылка меняется, а абсолютная — нет. При копировании или заполнении формулы по строкам и столбцам относительная ссылка автоматически изменяется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в ячейку B3 она автоматически изменяется с =A$1 на =B$1. Скопированная формула со смешанной ссылкой

• При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
• Трехмерные ссылки нельзя использовать в формулах массива.
• Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.

Что происходит при перемещении, копировании, вставке или удалении листов . Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

• Вставка или копирование . Если вставить листы между листами 2 и 6, Microsoft Excel прибавит к сумме содержимое ячеек с A2 по A5 на новых листах.
• Удаление . Если удалить листы между листами 2 и 6, Microsoft Excel не будет использовать их значения в вычислениях.
• Перемещение . Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов.
• Перемещение конечного листа . Если переместить лист 2 или 6 в другое место книги, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.
• Удаление конечного листа . Если удалить лист 2 или 6, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Ссылка	Значение
R[-2]C	относительная ссылка на ячейку, расположенную на две строки выше в том же столбце
R[2]C[2]	Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
R2C2	Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца
R[-1]	Относительная ссылка на строку, расположенную выше текущей ячейки
R	Абсолютная ссылка на текущую строку

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

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

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

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

Синтаксис функции Excel В приведенном ниже примере функции ОКРУГЛ , округленной на число в ячейке A10, показан синтаксис функции. 1. Structure. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающую круглую скобку, аргументы функции, разделенные запятыми, и закрывающая круглая скобка. 2. имя функции. Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите клавиши SHIFT + F3, чтобы открыть диалоговое окно Вставка функции . 3. аргументы. Аргументы могут быть числами, текстом, логическими значениями, такими как Истина или ложь, массивами, значениями ошибок, например #N/a или ссылками на ячейки. Используемый аргумент должен возвращать значение, допустимое для данного аргумента. В качестве аргументов также используются константы, формулы и другие функции. 4. всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.

Примечание: Вам не нужно вводить функции во все прописные буквы, например = «ОКРУГЛИТЬ», так как Excel автоматически заполнит ввод имени функции после нажатия кнопки «Добавить». Если вы неправильно наводите имя функции, например = СУМА (a1: A10), а не = сумм (a1: A10), Excel вернет #NAME? Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

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

Вложение функций Excel В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в следующей формуле используется вложенная функция СРЗНАЧ , а результат сравнивается со значением 50. 1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ. Допустимые типы вычисляемых значений Вложенная функция, используемая в качестве аргумента, должна возвращать соответствующий ему тип данных. Например, если аргумент должен быть логическим, т. е. иметь значение ИСТИНА либо ЛОЖЬ, вложенная функция также должна возвращать логическое значение (ИСТИНА или ЛОЖЬ). В противном случае Excel выдаст ошибку «#ЗНАЧ!». Предельное количество уровней вложенности функций. В формулах можно использовать до семи уровней вложенных функций. Если функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функция СРЗНАЧ и функция сумм являются функциями второго уровня, если они используются в качестве аргументов функции если. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня, и т. д.

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

Тренинг «Мастер Формул» (1 день)

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

Для кого этот тренинг

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

• Ваши задачи в Excel не превышают по сложности «тут сложить, здесь вычесть и залить ячейку желтеньким».
• Вы уверены, что ВПР (VLOOKUP) — самая сложная функция в Excel, а вы ее уже освоили — значит ничему новому вас не научить.
• Вы можете легко объяснить как работает вот эта формула:
  =ИНДЕКС($F$5:$F$22;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ($F$5:$F$22;» <"&$F$5:$F$22);СТРОКА()-4);СЧЁТЕСЛИ($F$5:$F$22;"<"&$F$5:$F$22);0))

После этого тренинга вы.

• Узнаете что такое формулы массива, какие они бывают, в чем их хитрости, плюсы и минусы, как самому создавать ФМ и делать невозможное с их помощью.
• Научитесь находить и извлекать из данных наиболее важные значения (топ клиентов или товаров, типовую корзину и т.п.)
• Овладеете вычислениями итогов по таблице по нескольким условиям, только по видимым ячейкам, через 1-2-3 строчки, без учета нулей и ошибок или в заданном интервале «с . по . «
• Узнаете 8 секретов использования ВПР и как на самом деле правильно использовать функции ВПР, ИНДЕКС, ПОИСКПОЗ и СМЕЩ для поиска и подстановки данных с начала или с конца, точно или приблизительно.
• Как удобно создавать связи между листами и файлами и управлять ими.

Программа тренинга

Модуль 1. Основы Формул Массива

• Определение и способы ввода
• Типы формул массива
• Достоинства и недостатки формул массива
• Полезные трюки при работе с формулами массива

Модуль 2. Анализ предельных значений

• Извлечение топовых элементов (лучшие клиенты, поставщики, товары) с помощью функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ, формул массива и сводных таблиц.
• Извлечение самых часто встречаемых значений (продуктовая корзина, типовой заказ) с помощью функций МОДА, ЧАСТОТА и сводной таблицы с группировкой.

Модуль 3. Выборочный подсчет по условиям

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

Модуль 4. Поиск и подстановка

• Поиск ближайшего значения в диапазоне.
• 8 секретов функции ВПР (VLOOKUP)
• 3 способа создания динамического диапазона с автоподстройкой размеров.
• Извлечение последнего элемента в списке по условию.
• Подстановка из встроенного в формулу массива констант (ВПР без таблицы)
• Двумерная подстановка (ВПР 2D)
• Подстановка по нескольким условиям (ВПР по нескольким условиям)
• Поиск всех найденных значений в списке по условию (ВПР, выдающая сразу все, а не только первое значение)
• Приблизительный поиск текста
• Транспонирование и ресайз таблиц с помощью формул.

Модуль 5. Эффективное управление связями

• Трехмерные ссылки между листами.
• Создание непрямых несбиваемых ссылок с помощью функции ДВССЫЛ.
• Ссылки на «умные таблицы» внутри книги и в других файлах.

Сомневаетесь?

• «Я лучше прочитаю все это в книгах»
  Примерно половину курса в книгах не найти в принципе — не пока таких книг. Для второй половины готовьтесь читать много толстых и дорогих книг, большинство из которых пока есть только на английском (по формулам массива на русском издают очень мало). Да и учиться по книгам может не каждый. И сколько будет стоить ваше время, потраченное на то, чтобы прочитать и освоить весь этот объем?

Бонусы

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

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

Заинтересовались? Хотите получить более подробную информацию о условиях проведения такого тренинга? Тогда выберите ваш вариант:

Функции Excel (по категориям)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета 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 Starter 2010 Еще. Меньше

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

Десять самых популярных функций

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

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

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

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

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

Данная функция применяется для поиска элемента в диапазоне ячеек с последующим выводом относительной позиции этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 7 и 38, то формула =MATCH(7,A1:A3,0) возвращает значение 2, поскольку элемент 7 является вторым в диапазоне.

Эта функция позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента «номер_индекса».

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

Функция РАЗНДАТ вычисляет количество дней, месяцев или лет между двумя датами.

Эта функция возвращает число дней между двумя датами.

Функции НАЙТИ и НАЙТИБ находят вхождение одной текстовой строки в другую. Они возвращают начальную позицию первой текстовой строки относительно первого знака второй.

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

Функции совместимости

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

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

Возвращает интегральную функцию бета-распределения.

Возвращает обратную интегральную функцию указанного бета-распределения.

Возвращает отдельное значение вероятности биномиального распределения.

Возвращает одностороннюю вероятность распределения хи-квадрат.

Возвращает обратное значение односторонней вероятности распределения хи-квадрат.

Возвращает тест на независимость.

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

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

Возвращает ковариацию, среднее произведений парных отклонений.

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

Возвращает экспоненциальное распределение.

Возвращает F-распределение вероятности.

Возвращает обратное значение для F-распределения вероятности.

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

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

Возвращает результат F-теста.

Возвращает обратное значение интегрального гамма-распределения.

Возвращает гипергеометрическое распределение.

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

Возвращает интегральное логарифмическое нормальное распределение.

Возвращает значение моды набора данных.

Возвращает отрицательное биномиальное распределение.

Возвращает нормальное интегральное распределение.

Возвращает обратное значение нормального интегрального распределения.

Возвращает стандартное нормальное интегральное распределение.

Возвращает обратное значение стандартного нормального интегрального распределения.

Возвращает k-ю процентиль для значений диапазона.

Возвращает процентную норму значения в наборе данных.

Возвращает распределение Пуассона.

Возвращает квартиль набора данных.

Возвращает ранг числа в списке чисел.

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

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

Возвращает t-распределение Стьюдента.

Возвращает обратное t-распределение Стьюдента.

Возвращает вероятность, соответствующую проверке по критерию Стьюдента.

Оценивает дисперсию по выборке.

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

Возвращает распределение Вейбулла.

Возвращает одностороннее P-значение z-теста.

Функции кубов

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

Возвращает элемент или кортеж из куба. Используется для проверки существования элемента или кортежа в кубе.

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

Возвращает n-й, или ранжированный, элемент в множестве. Используется для возвращения одного или нескольких элементов в множестве, например лучшего продавца или 10 лучших студентов.

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

Возвращает число элементов в множестве.

Возвращает агрегированное значение из куба.