Как создать формулы в excel: Работа с формулами в Excel

Работа с формулами в Excel

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

Создание простейших формул в Excel

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

Затем выделите определенную ячейку, заполненную данными, и поставьте нужный арифметический знак («+», «-», «*»,«/» и т.д.). Такие знаки называются операторами формул. Теперь выделите следующую ячейку и повторяйте действия поочередно до тех пор, пока все ячейки, которые требуются, не будут задействованы.

Примеры вычислений в Excel

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

1. Выбираем ячейку, где должна будет отображаться сумма, и ставим там =. Далее выделяем ячейку с количеством товара — ссылка на нее сразу же появляется после знака равенства. После координат ячейки нужно вставить арифметический знак. В нашем случае это будет знак умножения — *. Теперь кликаем по ячейке, где размещаются данные с ценой единицы товара. Арифметическая формула готова.
2. Для просмотра ее результата нажмите клавишу Enter.
3. Чтобы не вводить эту формулу каждый раз для вычисления общей стоимости каждого наименования товара, наведите курсор на правый нижний угол ячейки с результатом и потяните вниз на всю область строк, в которых расположено наименование товара.
4. Формула скопировалась и общая стоимость автоматически рассчиталась для каждого вида товара,согласно данным о его количестве и цене.

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

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

1. Итак, пишем = в первой ячейке столбца «Сумма». Затем открываем скобку, кликаем по первой ячейке в столбце «1 партия», ставим +, щелкаем по первой ячейке в столбце «2 партия». Далее закрываем скобку и ставим *. Кликаем по первой ячейке в столбце «Цена» — так мы получили формулу.
2. Нажимаем Enter, чтобы узнать результат.
3. Так же, как и в прошлый раз, с применением способа перетягивания копируем данную формулу и для других строк таблицы.
4. Нужно заметить, что не обязательно все эти формулы должны располагаться в соседних ячейках или в границах одной таблицы. Они могут находиться в другой таблице или даже на другом листе документа. Программа все равно корректно осуществляет подсчет.

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

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

Для получения результата жмем Enter.

Основные операторы Excel

К основным операторам вычислений, которые применяются в Microsoft Excel, относятся следующие:

• = («знак равенства») – равно;
• + («плюс») – сложение;
• - («минус») – вычитание;
• * («звездочка») – умножение;
• / («наклонная черта») – деление;
• ^ («циркумфлекс») – возведение в степень.

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

Помогла ли вам эта статья?

пошаговая инструкция, особенности и рекомендации

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

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

Как прописать формулу в Excel: начальные понятия

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

Дело в том, что машина ввод сочетания вроде «2 х 2» или вынесение общего компонента за скобки («2+2)5») не понимает. Для этого предусмотрено использование нескольких типов символов, которые представлены в таблице ниже, не считая логических операторов.

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

Сумма и разность

Как в Excel прописать формулу суммы или разности? Итак, начнем с самого простого, когда требуется вычислить сумму. В строку формул (причем, для всех операций) сначала вводится знак равенства, после чего вписывается нужная формула. В случае с обычным калькулятором можно указать для установленной ячейки «=2+2».

Если же суммирование производится для значений, введенных непосредственно в других ячейках (например, A1 и A2), формула приобретает вид «=A1+A2». Нередко для использования дополнительных операторов сумма может заключаться в скобки. Для разности – то же самое, только с минусом вместо плюса.

Когда нужно указать номера ячеек или их диапазон, может применяться специальная команда суммы (в русском варианте «СУММ», в английском – SUM). 2».

Процентные вычисления

С процентами, если не затрагивать сложные вычисления, тоже все просто. Как в Excel прописать формулу с процентами?

Достаточно ввести формулу вида «=A1*5%», после чего вы и получите те самые пять процентов от значения в соответствующей ячейке.

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

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

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

Абсолютные, относительные и смешанные типы ячеек

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

Абсолютная ячейка является неизменной и обозначается, как $A$1, относительная представляет собой ссылку на обычное местоположение (A1), смешанная – есть комбинация ссылок и на абсолютную, и на относительную ячейку ($A1 или A$1). Обычно такие форматы применяются при создании перекрестных ссылок, когда в формулах задействованы данные, находящиеся на разных листах книги или даже в разных файлах.

Формулы ВПР

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

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

В этом варианте сначала через «Мастер функций» используется диапазон значений исходной (первой) таблицы, в поле «Таблица» указывается второй диапазон с фиксацией содержимого (F4), далее указывается номер столбца, а в поле интервального просмотра устанавливается значение «ЛОЖЬ», если действительно при фильтрации действительно нужно получить только точные, а не приблизительные значения. Как правило, такие формулы используются больше в складском или бухгалтерском учете, когда установить какие-то специализированные программные продукты не представляется возможным.

Заключение

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

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

Правила ввода формул в Excel

<<Назад  |  Содержание  |  Далее>>

1. Убедитесь в том, что активна (выделена курсивной рамкой) та ячейка, в которой вы хотите получить результат вычислений.

2. Ввод формулы начинается со знака “=”. Этот знак вводится с клавиатуры.

3. После ввода знака “=” Excel переходит в режим ввода формулы. В этом режиме, при выделении какой-либо ячейки, ее адрес автоматически заносится в формулу. Это позволяет избавить пользователя от необходимости знать адреса ячеек и вводить их в формулу с клавиатуры.

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

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

§        Удобнее вводить знаки операций с правого цифрового блока клавиатуры. Чтобы этот блок работал в нужном режиме, индикатор <Nun Lock> должен быть включен.

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

§        <Enter> завершает ввод формулы, и переводит курсор в следующую ячейку.

§        “Зеленая галочка” на панели ввода формулы завершает ввод формулы, и оставляют курсор в той же ячейке.

Например, если в ячейке D2 должна помещаться разность чисел из ячеек B2 и C2, то после установки курсора на D5 следует указать мышью на B2, ввести с клавиатуры знак “-”, указать мышью на C2 и нажать <Enter> или “зеленую галочку”.

В формулах можно использовать числовые константы (-4,5), ссылки на блоки (D4), (A3:D8), знаки арифметических операций, встроенные функции (СУММ, МАКС, SIN и т.д.)

Таблица 18.

<<Назад  |  Содержание  |  Далее>>

Как создавать формулы в Excel

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

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

Для начала, любая формула Excel начинается со знака равенства (=). Итак, какую бы формулу вы ни собирались написать, начните с ввода = либо в ячейке назначения, либо в строке формул Excel. А теперь давайте подробнее рассмотрим, как можно составлять разные формулы в Excel.

Как составить простую формулу Excel с помощью констант и операторов

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

• Выберите ячейку, в которую вы хотите вывести результат.
• Введите символ равенства (=), а затем введите уравнение, которое вы хотите вычислить.
• Нажмите клавишу Enter, чтобы завершить формулу.Готово!

Вот пример простой формулы вычитания в Excel: = 100-50

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

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

Например, если вы хотите вычесть значение в ячейке B2 из значения в ячейке A2, вы пишете следующую формулу вычитания: = A2-B2

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

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

Как создать формулу Excel, используя определенные имена

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

Самый быстрый способ создать имя в Excel — выбрать ячейку (я) и ввести имя непосредственно в поле имени . Например, вот как вы создаете имя для ячейки A2:

Профессиональный способ определения имени — через вкладку Формулы > Определенные имена группа или сочетание клавиш Ctrl + F3.

В этом примере я создал следующие 2 имени:

• выручка для ячейки A2
• расходы для ячейки B2

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

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

Например, если вы создаете имя 2015_sales для ячеек A2: A100, вы можете найти общее количество этих ячеек, используя следующую формулу SUM: = SUM (2015_sales)

Конечно, вы можете получить тот же результат, передав диапазон в функцию СУММ: = СУММ (A2: A100)

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

Как составлять формулы Excel с помощью функций

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

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

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

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

Создайте формулу в Excel с помощью мастера функций

Если вы еще не очень хорошо знакомы с формулами электронных таблиц Excel, мастер

1. Запустите мастер функций.

Чтобы запустить мастер, нажмите кнопку Вставить функцию на вкладке Формулы > группа Библиотека функций или выберите функцию из одной из категорий:

Также можно нажать кнопку Вставить функцию слева от строки формул.

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

2. Найдите функцию, которую хотите использовать.

Когда появится мастер Insert Function , вы сделаете следующее:

• Если вы знаете имя функции, введите его в поле Search for a function
  и щелкните Go .
• Если вы не уверены, какую именно функцию вам нужно использовать, введите очень краткое описание задачи, которую вы хотите решить, в поле Поиск функции и щелкните Перейти . Например, вы можете ввести что-то вроде этого: « ячеек суммы» или « подсчет пустых ячеек» .
• Если вы знаете, к какой категории принадлежит функция, щелкните маленькую черную стрелку рядом с . Выберите категорию и выберите одну из 13 категорий, перечисленных там.Функции, относящиеся к выбранной категории, появятся в Выберите функцию

Краткое описание выбранной функции можно прочитать прямо под полем Select a function . Если вам нужны дополнительные сведения об этой функции, щелкните ссылку Справка по этой функции

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

3. Укажите аргументы функции.

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

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

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

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

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

4. Заполните формулу.

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

Напишите формулу прямо в ячейке или строке формул

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

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

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

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

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

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

формул Excel с примерами

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

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

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

Формулы Microsoft Excel — основы

В MS Excel формулы — это уравнения, которые выполняют различные вычисления на ваших листах. Хотя за прошедшие годы Microsoft представила несколько новых функций, концепция формул электронной таблицы Excel одинакова во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007 и ниже.

• Все формулы Excel начинаются со знака равенства (=).
• После символа равенства введите либо вычисление , либо функцию . Например, чтобы сложить значения в ячейках с B1 по B5, вы можете:
  • Введите уравнение целиком: = B1 + B2 + B3 + B4 + B5
  • Используйте функцию СУММ: = СУММ (B1: B5)
• Нажмите клавишу Enter, чтобы заполнить формулу. Готово!

Элементы формул Microsoft Excel

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

• Константы — числа или текстовые значения, которые вы вводите непосредственно в формуле, например = 2 * 3.
• Ссылки на ячейки — ссылка на ячейку, содержащую значение, которое вы хотите использовать в формуле Excel, например = СУММ (A1; A2; B5) .

  Чтобы ссылаться на данные в двух или более смежных ячейках, используйте ссылку на диапазон , такую ​​как A1: A5. Например, чтобы суммировать значения во всех ячейках от A1 до A5 включительно, используйте следующую формулу:
  = СУММ (A1: A5) .

• Имена — определенное имя для диапазона ячеек, константы, таблицы или функции, например = SUM (my_name) .
• Функции — стандартные формулы в Excel, которые выполняют вычисления с использованием значений, указанных в их аргументах.
• Операторы — специальные символы, указывающие тип операции или расчета, которые необходимо выполнить.

Операторы в формулах листа Excel

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

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

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

Например, если у вас есть цена товара в ячейке A2 и НДС в ячейке B2, вы можете рассчитать сумму НДС, используя следующую процентную формулу: = A2 * B2

Операторы сравнения в формулах Excel

В формулах Microsoft Excel сравнение или логических , операторов используются для сравнения двух значений.Результатом сравнения всегда является логическое значение ИСТИНА или ЛОЖЬ. В Excel доступны следующие логические операторы:

Например, формула = A1 = B1 возвращает ИСТИНА, если ячейки A1 и B1 содержат одно и то же значение (число, текст или дату), в противном случае - ЛОЖЬ.

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

Оператор объединения текста

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

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

= A1 & "" & B1

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

Тот же результат может быть достигнут с помощью функции СЦЕПИТЬ, и в следующем руководстве объясняются все детали: Как объединить текстовые строки, ячейки и столбцы в Excel.

Справочные операторы в формулах и функциях Excel

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

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

Например, диапазон A1: A00 включает 100 ячеек от A1 до A100. Чтобы найти среднее значение из этих 100 ячеек, используйте следующую формулу: = СРЕДНЕЕ (A1: A00)

Вы также можете ссылаться на весь столбец (A: A) или на всю строку (1: 1).Например, следующая формула находит сумму всех чисел в столбце A: = СУММ (A: A) . Узнайте больше о ссылках на целый столбец и целую строку.

Запятая (, ) - используется для разделения аргументов в формулах электронных таблиц Excel. Например, формула = ЕСЛИ (A1> 0, «хорошо», «плохо») читается следующим образом: если A1 больше нуля, вернуть «хорошо», иначе «плохо».

Итак, если вы пытаетесь создать формулу на своем листе, но Excel не принимает ее и выдает ошибку «неверная формула», перейдите в региональные настройки ( Панель управления> Регион и язык > Дополнительно Settings) и проверьте, какой символ установлен там как List Separator .Это тот символ, который вам нужно использовать для разделения аргументов в формулах Excel.

Пробел - это оператор пересечения, который позволяет получить ячейки, общие для двух указанных вами ссылок. Например, если у вас есть список элементов в столбце A и некоторые связанные данные в других столбцах, вы можете получить значение на пересечении
данного столбца и строки, используя следующую формулу: = B3: D3 C2: C4

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

Типы формул Excel

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

• Простые формулы Excel выполняют всего одну математическую операцию, например = 10 * 5 или = СУММ (A1: A10)
• Сложный (расширенный) Формулы Excel включают более одного вычисления, например = 10 * 5 + 20 или = СУММ (A1: A10) / 2

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

Как создавать формулы в Excel

Как уже упоминалось, любая формула Excel начинается со знака равенства (=). Итак, какую бы формулу вы ни собирались написать, начните с ввода = либо в ячейке назначения, либо в строке формул Excel. А теперь давайте подробнее рассмотрим, как можно составлять разные формулы в Excel.

Как создавать простые формулы в Excel

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

Как создавать расширенные формулы в Excel

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

Создание сложных формул с константами и математическими операторами

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

• Математические операции в скобках
• Степень (экспоненциальные вычисления)
• Умножение и деление, в зависимости от того, что идет первым в формуле
• Сложение и вычитание, в зависимости от того, что наступит раньше в формуле

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

А теперь давайте разберем эти формулы, чтобы увидеть, как Microsoft Excel их вычисляет:

Итоговая формула: = $ B2 * $ D2 + $ B2 * $ D2 * $ C2

• 1-е умножение: $ B2 * $ D2 (цена * кол-во.= сумма)
• 2 ^и и 3 ^и умножения: $ B2 * $ D2 * $ C2 (цена * кол-во *% НДС = сумма НДС)
• Дополнение: сумма + сумма НДС = итого

Формула комиссии: = ($ B2 * $ D2 + $ B2 * $ D2 * $ C2) * 10%

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

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

Формулы Excel с вложенными функциями

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

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

= ОКРУГЛ (СУММ (B2: B6), 0)

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

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

Формулы массива в Excel

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

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

Предположим, у вас есть 2 столбца чисел, столбец A и B. И вы хотите знать, во сколько раз столбец B больше или равен столбцу A, когда значение в столбце B больше 0.

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

= СУММ ((B2: B10> = A2: A10) * (B2: B10> 0))

Примечание. Чтобы правильно ввести формулу массива Excel, вам нужно нажать Ctrl + Shift + Enter вместо обычного ввода Enter.

Чтобы узнать больше о формулах массива Excel, см. Следующие руководства:

Пользовательские функции Excel

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

Такие пользовательские функции называются Пользовательскими функциями (UDF), и они особенно полезны для сложных математических или инженерных расчетов. Как и макросы, пользовательские функции написаны на языке VBA (Visual Basic для приложений). Например, вы можете просмотреть и загрузить пользовательские функции, созданные нашей командой для подсчета и суммирования ячеек по цвету.

Абсолютные, относительные и смешанные ссылки на ячейки в формулах Excel

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

Абсолютная ссылка на ячейку ($ A $ 1) - знак $ перед координатами строки и столбца делает ссылку статической и позволяет копировать формулу без изменения ссылок .

Относительная ссылка на ячейку (A1) - ссылка на ячейку без изменения знака $ в зависимости от относительного положения строк и столбцов в электронной таблице.

Ссылка на смешанную ячейку - может быть 2-х типов:

• Абсолютный столбец и относительная строка ($ A1) - знак $ перед буквой столбца блокирует ссылку на указанный столбец, поэтому столбец никогда не изменяется. Относительная ссылка на строку без знака доллара изменяется в зависимости от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка (A $ 1) - ссылка на строку, заблокированная с помощью $, не изменяется, а ссылка на столбец изменяется.

На следующем изображении показано, как на практике работают разные типы ссылок.

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

Советы и быстрые клавиши для работы с формулами Excel

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

• Для переключения между абсолютными, относительными и смешанными ссылками в формуле используйте клавишу F4, как показано в разделе «Переключение между типами ссылок в Excel».
• Чтобы просмотреть все формулы на листе, нажмите кнопку Показать формулы на вкладке Формулы > Аудит формул или нажмите сочетание клавиш Ctrl + ~.
От
• до измените формулу , нажмите F2, дважды щелкните ячейку или щелкните строку формул.
• Для отладки формул в Excel выберите часть формулы и нажмите F9. Это позволит вам увидеть фактические значения за ссылками на ячейки.
• На скопируйте формулу в все ячейки в столбце , введите формулу в первую ячейку, выберите эту ячейку и наведите курсор на маленький квадрат в правом нижнем углу, пока он не изменится на черный крест (который называется
  дескриптором заполнения). Дважды щелкните этот крестик, и формула будет скопирована через весь столбец.
• В преобразовать формулы в значения , выберите все ячейки с формулами, которые вы хотите преобразовать, нажмите Ctrl + C, чтобы скопировать эти формулы, затем нажмите Shift + F10, затем нажмите V, а затем нажмите Enter. Shift + F10 + V - это ярлык для специальной вставки Excel - значения только . Если вы не уверены, что запомните этот ярлык, просто нажмите обычный ярлык для вставки Ctrl + V, щелкните маленькую стрелку справа от кнопки Вставить , чтобы открыть раскрывающийся список, и выберите Вставить значения .Для получения дополнительной информации см. Как заменить формулы на их значения в Excel.

Формулы Microsoft Excel с примерами

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

Процентная формула Excel

Формулы сумм Excel

Формула подсчета в Excel

Формула среднего в Excel

Формулы даты Excel

• Как преобразовать текст на дату в Excel - несколько формул для преобразования текста на дату.
• Как преобразовать дату Excel в текст - формула, чтобы преобразовать дату в текстовую строку в указанном формате.
Примеры формул
• Excel DATE - как получить серийный номер, представляющий дату, сложить и вычесть даты в Excel, вернуть дату на основе значений в других ячейках, преобразовать текстовую строку в дату, а также несколько расширенных примеров формул Excel DATE.
• Формулы для вычисления дней в Excel (функции WEEKDAY, DAY) - формулы даты для возврата дня недели от даты, получения количества дней в году, определения количества дней между двумя датами и т. Д.
• Как рассчитать месяцы в Excel (функции МЕСЯЦ и МЕСЯЦ) - примеры формулы даты в Excel для извлечения месяца из даты, получения первого и последнего дня месяца, преобразования названия месяца в число и т. Д.
• Расчет номера недели в Excel (функция WEEKNUM) - как использовать формулы даты в Excel, чтобы получить номер недели от даты, преобразовать номер недели в дату, получить номер недели в месяце, значения суммы по номеру недели и т. Д.
• Как складывать и вычитать даты в Excel - формулы даты для сложения и вычитания дней, недель, месяцев и лет.
• Как рассчитать разницу между двумя датами (функция РАЗНДАТ) - Формула даты Excel для расчета разницы между двумя датами в днях, неделях, месяцах или годах.
• Расчет рабочих дней в Excel (WORKDAY и NETWORKDAYS) - использование формулы даты в Excel для расчета рабочих дней с настраиваемыми параметрами выходных и праздничных дней.
• Преобразование даты в год и вычисление возраста по дате рождения - формулы возраста Excel и несколько других формул даты для извлечения года из даты, преобразования даты в месяц и год, определения високосных лет.

Формулы времени в Excel

Формулы Excel для ВПР

Формулы условного форматирования Excel

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

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

Создание формул Power Query в Excel

Примечание: Power Query известен как Get & Transform в Excel 2016.Информация, представленная здесь, применима к обоим. Дополнительные сведения см. В разделе «Получение и преобразование в Excel 2016».

Для создания формул Power Query в Excel можно использовать строку формул редактора запросов или расширенный редактор . Редактор запросов - это инструмент, включенный в Power Query, который позволяет создавать запросы к данным и формулы в Power Query. Для создания этих формул используется язык Power Query Formula Language . Существует множество формул Power Query, которые можно использовать для обнаружения, объединения и уточнения данных.Дополнительные сведения о полном спектре формул Power Query см. В разделе Категории формул Power Query.

Давайте создадим простую формулу, а затем создадим расширенную формулу.

Создайте простую формулу

В качестве простого примера формулы давайте преобразуем текстовое значение в правильный регистр с помощью формулы Text.Proper ().

1. На вкладке ленты POWER QUERY выберите Из других источников > Пустой запрос .

2. В строке формул редактора запросов введите = Text.Proper («текстовое значение») и нажмите . Введите или щелкните значок Enter.

3. Power Query показывает результаты на панели результатов формулы.

4. Чтобы увидеть результат в таблице Excel, выберите Закрыть и загрузить .

Результат на листе будет выглядеть так:

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

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

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

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

Исходная таблица выглядит так:

И вы хотите, чтобы результирующая таблица выглядела так:

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

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

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

• Создайте серию шагов формулы запроса, которые начинаются с оператора let. Обратите внимание, что язык формул Power Query Formula Language чувствителен к регистру.

• Каждый шаг формулы запроса основывается на предыдущем шаге, ссылаясь на шаг по имени.

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

Шаг 1 - Откройте расширенный редактор

1. На вкладке ленты POWER QUERY выберите Из других источников > Пустой запрос .

2. В редакторе запросов выберите Расширенный редактор .

3. Вы увидите расширенный редактор .

Шаг 2. Определите исходный источник

В расширенном редакторе :

1. Используйте оператор let, который назначает Source = Excel.CurrentWorkbook ().В качестве источника данных будет использоваться таблица Excel. Дополнительные сведения о формуле Excel.CurrentWorkbook () см. В разделе Excel.CurrentWorkbook.

2. Назначьте Source результату в результате .

    let  Источник  =
   Excel.CurrentWorkbook () {[Name = "Orders"]} [Содержание]
   в  Источник  

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

4. Для просмотра результатов на листе:

   1. Нажмите Готово .

   2. На ленте редактора запросов щелкните Закрыть и загрузить .

Результат на листе выглядит так:

Шаг 3. Переместите первую строку в заголовки

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

1. Добавьте формулу # "Первая строка как заголовок" = Table.PromoteHeaders () в шаги формулы запроса и укажите источник данных в качестве источника данных. Дополнительные сведения о формуле Table.PromoteHeaders () см. В разделе Table.PromoteHeaders.

2. Назначьте # "Первая строка как заголовок" результату.

    лет
       Источник = Excel.CurrentWorkbook () {[Name = "Orders"]} [Содержание],
         # "Первая строка как заголовок"  = Table.PromoteHeaders ( Источник )
   в
        # "Первая строка как заголовок"  

Результат на листе выглядит так:

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

Чтобы преобразовать каждое значение столбца ProductName в правильный текст, используйте Table.TransformColumns () и обратитесь к шагу формулы запроса «Первая строка как заголовок». Это делается в расширенном редакторе :

1. Добавьте формулу # "Каждое слово с заглавной буквы" = Table.TransformColumns () в шаги формулы запроса и укажите # "Первая строка как заголовок" в качестве источника данных. Дополнительные сведения о формуле Table.TransformColumns () см. В разделе Table.TransformColumns.

2. Присвойте # "Каждое слово с заглавной буквы" для в результате .

 лет
    Source = Excel.CurrentWorkbook () {[Name = "Orders"]} [Содержание],
     # "Первая строка как заголовок"  = Table.PromoteHeaders (Источник),
      # "Каждое слово с заглавной буквы"  = Table.TransformColumns ( # "Первая строка как заголовок" , {{"ProductName", Text.Proper}})
в
      # "Каждое слово с заглавной буквы"  

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

С помощью языка формул Power Query Formula Language вы можете создавать простые и сложные запросы данных для обнаружения, объединения и уточнения данных.Чтобы узнать больше о Power Query, см. Справку Microsoft Power Query для Excel.

Учебное пособие по расширенным формулам Excel

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

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

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

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

• Если операторы
• Суммарные продукты
• ВПР
• Конкатенация
• Сумма Ifs

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

Операторы IF

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

Синтаксис для утверждения будет:

= IF (D2> 250, «Высокий спрос», «Низкий спрос»)

После этого будут получены результаты:

Из приведенного выше Из таблицы мы можем понять, что Sony Xperia и HTC Desire - высокопроизводительный продукт (намного лучше, чем Android: я должен признать, что при создании этих данных я был немного предвзято относился к продуктам Android)

Суммарные продукты

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

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

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

= СУММПРОИЗВ (C2: C6, D2: D6)

C2 и C6 относятся к цене, а D2 / D6 относятся к количеству проданных единиц; имена столбцов меняются в соответствии с данными.

В результате будет получен общий доход в размере 5,44 800 долларов США.

Concatenate

Это громкое слово, да, но не беспокойтесь. Concatenate - это формула для объединения данных в 2 или более разных ячейках в одну ячейку. Предположим, у нас есть список проданных продуктов и вердикт по каждому продукту. Затем Тед хочет связать каждый продукт со своим вердиктом, поэтому здесь мы используем синтаксис:

= Concatenate (A1, «&», B1)

Это также зависит от ячейки, которую вы хотите объединить.

Таким образом, будет получен следующий результат

ВПР

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

В этом случае предположим, что у Теда есть идентификатор продукта для каждого продукта, и ему нужно узнать, что это за продукт (при условии, что у него тысячи предметов)

Ему нужно узнать название продукта с идентификатором продукта '56' .