Простые формулы в эксель примеры: 10 формул Excel, которые пригодятся каждому / TeachMePlease

Совет! Применяя формулу «ПРОИЗВЕД» для определения значения диапазона чисел, можно задать несколько колонок и столбцов. При выборе диапазона =ПРОИЗВЕД(А1:С10), программа выполнит умножение всех значений ячеек в выбранном прямоугольнике. Обозначение диапазона – (А1-А10, В1-В10, С1-С10).

Комбинированные формулы

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

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

Если нужно найти сумму трех чисел и умножить сумму на коэффициенты 1,4 или 1,5, исходя из того, меньше ли итог числа 90 или больше.

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

=ЕСЛИ(СУММ(А1:С1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

Решение задачи с помощью комбинированной формулы

В примере задействованы две функции – ЕСЛИ и СУММ. Первая обладает тремя аргументами:

• условие;
• верно;
• неверно.

В задаче – несколько условий.

Во-первых, сумма ячеек в диапазоне А1:С1 меньше 90.

В случае выполнения условия, что сумма диапазона ячеек будет составлять 88, то программа выполнит указанное действие во 2-ом аргументе функции «ЕСЛИ», а именно в СУММ(А1:С3)*1,4.

Если в данном случае происходит превышение числа 90, то программа вычислит третью функцию – СУММ(А1:С1)*1,5.

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

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

Встроенные функции программы

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

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

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

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

Выбор функции из предлагаемого списка

Эту же операцию можно произвести с помощью комбинации на клавиатуре — Shift+F3 (раньше мы писали о горячих клавишах Excel).

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

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

Мастер функций

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

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

Например, дана задача, найти функцию СУММЕСЛИМН. Для этого нужно зайти в категорию математических функций и там найти нужную.

Выбор функции и заполнение полей

Далее нужно заполнить поля чисел и выбрать условие. Таким же способом можно найти самые различные функции, в том числе «СУММЕСЛИ», «СЧЕТЕСЛИ».

Функция ВПР

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

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

Вычисление ВПР можно проследить на примере, в котором приведен список из фамилий. Задача – по предложенному номеру найти фамилию.

Применение функции ВПР

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

Второй аргумент А1:В10 – это диапазон, в котором осуществляется поиск.

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

Вычисление заданной фамилии с помощью функции ВПР

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

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

Поиск фамилии с пропущенными номерами

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

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

Округление чисел с помощью функций

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

А полученное значение можно использовать при расчетах в других формулах.

Округление числа осуществляется с помощью формулы «ОКРУГЛВВЕРХ». Для этого нужно заполнить ячейку.

Первый аргумент – 76,375, а второй – 0.

Округление числа с помощью формулы

В данном случае округление числа произошло в большую сторону. Чтобы округлить значение в меньшую сторону, следует выбрать функцию «ОКРУГЛВНИЗ».

Округление происходит до целого числа. В нашем случае до 77 или 76.

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

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

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

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

В качестве примера рассмотрим простую формулу.

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

2. Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении.

   В нашем примере введите =1+1.

   Примечания: 

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

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

3. Нажмите клавишу ВВОД (Windows) или Return (Mac).

Рассмотрим другой вариант простой формулы. Введите =5+2*3 в другой ячейке и нажмите клавишу ВВОД или Return. Excel перемножит два последних числа и добавит первое число к результату умножения.

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

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

Примечание: Также в ячейке можно ввести ALT+= (Windows) или ALT+ += (Mac), и Excel автоматически вставит функцию СУММ.

Приведем пример. Чтобы сложить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая непосредственно под столбцом чисел. Затем нажмите кнопку «Автоумма». Формула появится в ячейке B7, а Excel выделит ячейки, которые вы суммируете.

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

Примечания: 

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

• Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.

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

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

Примечание: Чтобы эти формулы выводили результат, выделите их и нажмите клавишу F2, а затем — ВВОД (Windows) или Return (Mac).

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Общие сведения о формулах в Excel

Функция СУММ

Как составить формулу в Excel: гид по Excel таблицам

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

Содержание:

Математические операторы

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

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

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

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

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

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

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

С учетом сказанного можно подвести итог, как правильно составить формулу. Для этого поставьте в нужную ячейку знак равно, а после этого укажите необходимые действия, к примеру, В1+В2 или А1*А2.

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

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

Правила создания формул

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

Приведем пример простой формулы по сложению двух цифр. Алгоритм такой:

1. Выделите ячейку, в которой должен быть итоговый результат (С3).
2. Поставьте знак равно. Учтите, что он появляется не только в ячейке, но и в верхней строке.
3. Укажите адрес секции, которая должна быть первой, к примеру, С1.
4. Поставьте знак, который интересует в конкретной формуле, например, плюс (+).
5. Введите адрес графы для второго числа, к примеру, С2.
6. Проверьте, что у вас получилась надпись = С1+С2, жмите на Ввод.

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

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

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

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

Выделение с помощью мышки

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

1. Выделите секцию в таблице, где будет находиться формула (В3).
2. Поставьте знак равно (=).
3. Жмите на ячейку, которая должна стоять первой в расчетах. Для текущего примера В1. Цифра будет обведена, а номер секции появится в итоговой графе.
4. Укажите действие, которое необходимо сделать. Как вариант, жмите на умножение (*).
5. Выделите ту ячейку, цифра в которой должна стоять второй в формуле (В2). Обратите внимание на то, что необходимый участок будет обведен пунктирной линией.
6. Жмите на Ввод, чтобы система выполнила расчет

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

Редактирование

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

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

• Укажите секцию, где необходимо внести изменения. В нашем случае это С3.
• Перейдите в строку формулы вверху, чтобы внести коррективы. Как вариант, можно нажать прямо на ячейку, чтобы глянуть и внести изменения в формулу.
• Внесите новые данные и обратите внимание, какие ячейки при этом подсвечиваются программой. На этом этапе будьте внимательны, чтобы избежать ошибок.
• Жмите Ввод и подтвердите внесенные изменения. К примеру, если раньше формула имела вид С1+С2, ее можно поменять на С1-С2 или любой другой вариант. Главное, чтобы в указанных ячейках стояли цифры.

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

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

Сложные формулы

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

Здесь необходимо учесть общие правила для математических действий. Они просты.

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

Приведем вариант формулы для ячейки С4. В ней можно указать следующие данные (В2+В3)*0,3.

Это означает, что формула сначала суммирует два показателя, а полученное значение рассчитывается с процентами (30% записывается в виде 0,3).

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

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

Помните, что Excel делает расчет с учетом введенных данных и не предупреждает об ошибках. Проверяйте правильность ввода самостоятельно. Главное — корректно расставить скобки и задать приоритеты вычислений.

Функции программы

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

Синтаксис

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

К примеру, вам надо сложить значения в ячейках В1, В2, В3, В4. СУММ — функция добавляющая значения. При этом формат записи имеет следующий вид.

Сначала ставится знак равно (=). После него идет функция СУММ, а за ней диапазон ячеек (В1:В4).

В программе имеются опции, в которых вообще не указываются аргументы. Если написать СЕГОДНЯ (), приложение вернет день с учетом времени в ОС компьютера.

Основные функции

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

Кратко рассмотрим их названия и особенности:

1. СУММ. С помощью опции можно вычислить сумму двух и больше чисел. К примеру, если записать в качестве адреса (А1:А6), программа просуммирует все цифры в секциях, начиная с А1 по А6. Если указать опцию в формате (А1; А6), расчет будет выполнен только применительно к двум указанным секциям.
2. СЧЕТ. Задача формулы в том, чтобы рассчитать число ячеек с числовыми обозначениями в одном ряду. К примеру, для получения информации о числе ячеек с цифрами между В1 и В20, пропишите такую формулу Excel — = СЧЕТ (В1:В20).
3. СЧЕТ3. В отличие от прошлой опции, здесь учитываются все секции с внесенными данными (не только с цифрами). Плюс в том, что СЧЕТ3 можно использовать для разных типов информации, в том числе указанной в буквенном отображении.
4. ДЛСТР. Задача опции состоит в расчете числа знаков в секции. Но учтите, что система считает все действия, в том числе сделанные пробелы.
5. СЖПРОБЕЛЫ. Цель опции в удалении лишних пробелов. Это полезно, когда информация переносится с других источников, где уже имеется много ненужных пробелов.
6. ВПР. Используется, если нужно найти элементы в таблице или диапазоне по строкам.
7. ЕСЛИ. Опция применяется, если расчет осуществляется с условием «ЕСЛИ» и большим объемом данных с различными сценариями. Применение функции позволяет сравнить значения. Если результат правдивый, программа выполняет какое-то еще действие.
8. МАКС и МИН — определяют наибольший и наименьший параметр из перечня.

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

Правила использования

Для лучшего понимания рассмотрим, как правильно добавлять функцию в Excel. Используем параметр СРЗНАЧ.

Алгоритм действий такой:

1. Жмите на ячейку, где необходимо установить формулу — В11.
2. Пропишите знак равно =, а после укажите название нужной опции СРЗНАЧ.
3. Укажите диапазон секций в круглых скобках (В3:В10).
4. Кликните на Ввод.

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

Применение Автосуммы

Для удобства почти любую опцию можно вставить с помощью Автосуммы. Сделайте следующее:

• Выберите и жмите на секцию, в которую необходимо вбить формулу (С 11).
• В группе Редактирования в разделе Главная отыщите и нажмите на стрелку возле надписи Автосумма. 
• Выберите нужную опцию в появившемся меню, например, Сумма.
• Программа автоматически выбирает диапазон ячеек для суммирования, но эти данные можно задать вручную путем внесения правок в формулу.

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

Комбинированные формулы

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

Рассмотрим ситуацию, когда необходимо просуммировать три числа и умножить их на коэффициент 1,5 или 1,6 в зависимости от того, какое получилось число (больше или меньше 100).

В таком случае запись имеет следующий вид: =ЕСЛИ(СУММ(А2:С2)<100;СУММ(А2:С2)*1,5;СУММ(А2:С2)*1,6).

В приведенной выше формуле используется две опции — ЕСЛИ и СУММА. В первом случае учитывается три результата — условие, правильно или неправильно.

Здесь действуют такие условия:

1. Эксель суммирует числа в ячейках с А2 по С2.
2. Если полученное число меньше 100, тогда параметр умножается на 1,5.
3. Если итоговая цифра превышает 100, в таком случае результат умножается на 1,6.

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

Виды ссылок и их особенности

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

Относительная

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

К примеру, при копировании формулы =В2+С2 из второй строки в третью, запись поменяется на =В3+С3. Это полезно, когда необходимо скопировать вычисление одновременно для разных столбцов.

Алгоритм действий при использовании:

1. Выберите и нажмите на ячейку (секцию), где планируется запись формулы (В3).
2. Пропишите данные для расчета интересующего параметра — =С3*D3.
3. Жмите на ввод. Программа делает расчеты, а итог показывается в выбранной секции.
4. Найдите маркер автоматического заполнения в В3.
5. Нажмите на него и, удерживая левую кнопку мышки, перемести маркер по нужным ячейкам, например, с В3 до В10.
6. После отпускания кнопок формула копируется во все указанные секции. Одновременно с этим выполняются расчеты.

Еще раз убедитесь, что вычисления сделаны корректно.

Абсолютные ссылки

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

Для обозначения абсолютной ссылки в Excel применяется символ доллара ($). В зависимости от варианта расстановки меняется и указание пользователя.

Рассмотрим несколько вариантов:

• $А$3 — строка/столбец не меняется;
• А$3 — срока остается неизменной;
• $А3 — столбец не меняется.

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

Для примера сделаем расчет для ставки налога, введенного в секцию D1. Так как параметр во всех случаях идентичен, внедрим формулу $D$1 в расчет.

Для этого:

1. Укажите ячейку, где будет находиться формула, к примеру, C3.
2. Пропишите вычисление в виде (А3*В3)* $D$1.
3. Нажмите на кнопку Ввод.
4. Найдите маркер автоматического заполнения в С3 (точка справа внизу).
5. Нажмите на него, кликните на левую кнопку мышки и потяните маркер вниз, захватив диапазоны с С4:С14.
6. Отпустите кнопку мышки, после чего формула копируется в ячейки со ссылкой. Во всех случаях делаются автоматические расчеты.

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

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

С уважением, Александр Петренко
специально для проекта proudalenku.ru

Приглашаю вас в мой новый Марафон:

🔹 5 ДНЕЙ — 5 НАВЫКОВ 🔹

Это тест-драйв удаленной профессии, он рассчитан на новичков. Особенно полезен будет тому, кто давно мечтает зарабатывать удаленно, но не знает, с чего начать. Предлагаю начать с марафона!

👉🏻 Чтобы узнать подробности, жмите на кнопку:

Марафон →

До встречи в марафоне!
С уважением, Ольга Филиппова

Excel 2013: простые формулы

Урок 13: Простые формулы

/ ru / excel2013 / Printing-workbooks / content /

Введение

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

Необязательно: Загрузите нашу рабочую тетрадь.) для экспонентов.

Все формулы в Excel должны начинаться со знака равно ( = ). Это связано с тем, что ячейка содержит формулу и вычисляемое значение или равна им.

Что такое ссылки на ячейки

Хотя вы можете создавать простые формулы в Excel вручную (например, = 2 + 2 или = 5 * 5 ), большую часть времени вы будете использовать адресов ячеек для создания формулы. Это известно как создание ссылки на ячейку .Использование ссылок на ячейки гарантирует, что ваши формулы всегда будут точными, потому что вы можете изменить значение ячеек, на которые ссылаются, без необходимости переписывать формулу.

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

Для создания формулы:

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку B3 . Выбор ячейки B3
2. Введите знак равенства (=) . Обратите внимание, как он отображается как в ячейке , так и в формуле бар .

   Ввод знака =

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

Основные формулы и функции Excel с примерами

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

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

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

Основы формул Excel

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

Например, вместо указания каждого значения для суммирования, как в приведенной выше формуле, вы можете использовать функцию СУММ для сложения диапазона ячеек: = СУММ (A2: A4)

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

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

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

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

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

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

СУМ

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

СУММА ( число1 , [число2],…)

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

= СУММ (B2: B6) — складывает значения в ячейках с B2 по B6.

= СУММ (B2, B6) — складывает значения в ячейках B2 и B6.

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

= СУММ (B2: B6) / 5

Для суммирования с условиями используйте функцию СУММЕСЛИ: в 1-м аргументе вы вводите диапазон ячеек, которые нужно проверить на соответствие критериям (A2: A6), во 2-м аргументе — сами критерии (D2), а в последнем аргумент — ячейки для суммирования (B2: B6):

= СУММЕСЛИ (A2: A6, D2, B2: B6)

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

Наконечник. Самый быстрый способ суммирования столбца или строки чисел — выбрать ячейку рядом с числами, которые вы хотите суммировать (ячейка непосредственно под последним значением в столбце или справа от последнего числа в row) и нажмите кнопку AutoSum на вкладке Home в группе Formats . Excel автоматически вставит формулу СУММ.

Полезных ресурсов:

СРЕДНЕЕ

Функция Excel AVERAGE выполняет именно то, что предполагает ее название, т.е.е. находит среднее или среднее арифметическое чисел. Его синтаксис аналогичен SUM:

СРЕДНИЙ (число1, [число2],…)

При более внимательном рассмотрении формулы из предыдущего раздела ( = СУММ (B2: B6) / 5 ), что она на самом деле делает? Суммирует значения в ячейках с B2 по B6, а затем делит результат на 5. И что вы называете сложением группы чисел и последующим делением суммы на количество этих чисел? Ага, в среднем!

Функция Excel AVERAGE выполняет эти вычисления за кулисами.Итак, вместо деления суммы на количество вы можете просто поместить эту формулу в ячейку:

= СРЕДНИЙ (B2: B6)

Для усреднения ячеек на основе условия используйте следующую формулу СРЗНАЧЕСЛИ, где A2: A6 — это диапазон критериев, D3 — это критерии, а B2: B6 — ячейки для усреднения:

= СРЕДНЕЛИ (A2: A6, D3, B2: B6)

Полезных ресурсов:

МАКС. И МИН.

Формулы MAX и MIN в Excel получают наибольшее и наименьшее значение в наборе чисел соответственно.Для нашего набора данных примера формулы будут такими простыми, как:

= МАКС (B2: B6)

= МИН (B2: B6)

Полезных ресурсов:

COUNT & COUNTA

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

СЧЁТ (значение1; [значение2];…)

В то время как функция COUNT работает только с теми ячейками, которые содержат числа, функция COUNTA подсчитывает все ячейки, которые не являются пустыми , независимо от того, содержат ли они числа, даты, время, текст, логические значения ИСТИНА и ЛОЖЬ, ошибки или пустые текстовые строки («»):

COUNTA (значение1; [значение2],…)

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

= СЧЁТ (B: B)

Чтобы подсчитать все непустые ячейки в столбце B, используйте этот:

= СЧЁТ (B: B)

В обеих формулах используется так называемая «ссылка на весь столбец» (B: B), которая относится ко всем ячейкам в столбце B.

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

Полезных ресурсов:

IF

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

ЕСЛИ (логический_тест, [значение_если_ истинно], [значение_если_ ложь])

Например, следующий оператор IF проверяет, выполнен ли заказ (т.е.е. есть значение в столбце C) или нет. Чтобы проверить, не пуста ли ячейка, используйте оператор «не равно» (<>) в сочетании с пустой строкой («»). В результате, если ячейка C2 не пуста, формула возвращает «Да», иначе «Нет»:

= ЕСЛИ (C2 <> "", "Да", "Нет")

Полезных ресурсов:

ОТДЕЛКА

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

Есть несколько способов удалить ненужные пробелы в Excel, самый простой из которых — функция ОБРЕЗАТЬ:

TRIM (текст)

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

= ОБРЕЗАТЬ (A1)

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

Полезных ресурсов:

LEN

Всякий раз, когда вы хотите узнать количество символов в определенной ячейке, используйте ДЛСТР:

LEN (текст)

Хотите узнать, сколько символов в ячейке A2? Просто введите следующую формулу в другую ячейку:

= LEN (A2)

Имейте в виду, что функция ДЛСТР в Excel считает абсолютно все символы , включая пробелы :

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

Полезных ресурсов:

И И ИЛИ

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

• И возвращает ИСТИНА, если все условия соблюдены, в противном случае — ЛОЖЬ.
• ИЛИ возвращает ИСТИНА, если выполняется любое условие , в противном случае — ЛОЖЬ.

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

Например, чтобы проверить результаты теста в столбцах B и C и вернуть «Пройдено», если оба больше 60, в противном случае «Не пройдено», используйте следующую формулу ЕСЛИ со встроенным оператором И:

= ЕСЛИ (И (B2> 60, B2> 60), «прошел», «не прошел»)

Если достаточно иметь только один результат теста, превышающий 60 (тест 1 или тест 2), вставьте оператор ИЛИ:

= ЕСЛИ (ИЛИ (B2> 60, B2> 60), «прошел», «не прошел»)

Полезных ресурсов:

КОНКАТЕНАТ

Если вы хотите взять значения из двух или более ячеек и объединить их в одну ячейку, используйте оператор конкатенации (&) или функцию СЦЕПИТЬ:

СЦЕПИТЬ (текст1, [текст2],…)

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

= СЦЕПИТЬ (A2, B2)

Чтобы разделить объединенные значения пробелом, введите символ пробела («») в списке аргументов:

= СЦЕПИТЬ (A2; ""; B2)

Полезных ресурсов:

СЕГОДНЯ И СЕЙЧАС

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

= СЕГОДНЯ () , чтобы вставить сегодняшнюю дату в ячейку.

= СЕЙЧАС () , чтобы вставить текущую дату и время в ячейку.

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

Полезных ресурсов:

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

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

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

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

Подробные пошаговые инструкции см. В разделе Как копировать формулы в Excel.

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

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

• Выделите все ячейки с формулами.
• Нажмите Ctrl + C, чтобы скопировать выделенные ячейки.
• Щелкните выделение правой кнопкой мыши и выберите Вставить значения > Значения , чтобы вставить вычисленные значения обратно в выбранные ячейки.Или нажмите сочетание клавиш «Специальная вставка»: Shift + F10, а затем V.

Подробные инструкции со снимками экрана см. В разделе «Как заменить формулы на их значения в Excel».

Не заключайте числа в двойные кавычки

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

Например, чтобы проверить значение в ячейке B2 и вернуть 1 для «Пройдено», в противном случае 0, вы поместите следующую формулу, скажем, в C2:

= ЕСЛИ (B2 = «прошел», 1, 0)

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

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

= ЕСЛИ (B2 = «пройти», «1», «0»)

На первый взгляд результат нормальный — тот же столбец единиц и нулей. Однако при более внимательном рассмотрении вы заметите, что по умолчанию результирующие значения в ячейках выровнены по левому краю, то есть это числовые строки, а не числа! Если позже кто-то попытается вычислить эти 1 и 0, он может в конечном итоге выдернуть волосы, пытаясь выяснить, почему 100% правильная формула Sum или Count возвращает только ноль.

Не форматируйте числа в формулах Excel

Запомните это простое правило: числа в формулах Excel следует вводить без какого-либо форматирования, например десятичного разделителя или знака доллара. В Северной Америке и некоторых других странах запятая является разделителем аргументов по умолчанию, а знак доллара ($) используется для создания абсолютных ссылок на ячейки. Использование этих символов в числах может просто свести с ума ваш Excel 🙂 Итак, вместо того, чтобы набирать 2000 долларов, просто введите 2000, а затем отформатируйте выходное значение по своему вкусу, установив собственный числовой формат Excel.

Сопоставьте все открывающие и закрывающие круглые скобки в формулах

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

Убедитесь, что для параметров расчета установлено значение Автоматически

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

Если это не помогает, выполните следующие действия по устранению неполадок: Формулы Excel не работают: исправления и решения.

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

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

Как отображать формулы в Excel

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

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

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

Как отображать формулы в Excel

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

1. Параметр «Показать формулы» на ленте Excel

На листе Excel перейдите на вкладку Формулы > Аудит формул и нажмите кнопку Показать формулы .

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

2. Параметр «Показывать формулы в ячейках вместо результатов»

В Excel 2010, Excel 2013 и Excel 2016 перейдите в Файл > Параметры . В Excel 2007 нажмите Кнопка Office > Параметры Excel .

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

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

3. Ярлык Excel для отображения формул

Самый быстрый способ просмотреть все формулы в электронной таблице Excel — это нажать следующую комбинацию клавиш: Ctrl + `

Клавиша с надстрочным ударением (`) — самая дальняя клавиша слева в ряду с цифровыми клавишами (рядом с клавишей с цифрой 1).

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

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

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

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

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

Почему Excel показывает формулу, а не результат?

Случалось ли вам когда-нибудь вводить формулу в ячейку, нажимать клавишу Enter… а Excel по-прежнему показывает формулу вместо результата? Не волнуйтесь, с вашим Excel все в порядке, и мы исправим эту ошибку в ближайшее время.

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

1. Возможно, вы случайно активировали режим «Показать формулы», щелкнув соответствующую кнопку на ленте или нажав сочетание клавиш CTRL + `.Чтобы вернуть рассчитанные результаты, просто выключите кнопку Показать формулы или снова нажмите CTRL + `.
2. Вы могли случайно ввести пробел или одинарную кавычку (‘) перед знаком равенства в формуле:
   

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

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

Чтобы исправить эту ошибку, выберите ячейку, перейдите на вкладку Home > Number group и установите форматирование ячейки на General и, находясь в ячейке, нажмите F2 и ENTER.

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

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

Учите простые формулы Excel с примерами

Для просмотра этого видео включите JavaScript и рассмотрите возможность обновления до веб-браузера, который поддерживает видео HTML5

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

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

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

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

Пример 1 — Расчет расстояния поездки

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

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

Правильный ответ:
Поскольку расстояние — это произведение скорости и продолжительности, правильная формула будет выглядеть так:
= C4 * C5

Пример 2 — Расчет телефонного счета

В ячейке C37 указаны периодические платежи (фиксированные платежи, которые вы получаете каждый месяц).
В ячейке C39 указывается продолжительность разговора (в минутах) за весь месяц.
В ячейке C40 указана стоимость минуты разговора.
(Раньше так рассчитывались телефонные счета. К счастью, в настоящее время больше не нужно учитывать продолжительность разговора …)

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

Правильный ответ:
Телефонный счет равен периодическим платежам плюс количество минут, умноженное на стоимость минуты, таким образом:
= C37 + C39 * C40

Распространенные ошибки при использовании формул:

Забыл написать знак равенства
Не забывайте начинать его всегда со знака «=», иначе он будет просто записан в ячейку как текст.

Смешивание текстов с числами
Формулы не могут основываться на ячейках, содержащих смешанные числа и текст. Например, если ячейка содержит одно из следующего:
120 фунтов.
60 миль / ч
2,500 долларов США
Тогда формулы не могут использовать эти ячейки.Им нужно, чтобы числа были разделены и записаны в их собственных ячейках.
У вас есть 2 варианта:
A. Напишите текст (знаки или буквы) в соседних ячейках рядом с числами.
B. Напишите только число в ячейке, а затем добавьте специальные знаки или буквы, отформатировав эту ячейку. Это расширенное использование форматирования. Простым примером этого может быть «валютный» формат.