Как в экселе составить формулу: Функции Excel (по категориям)

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

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

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

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

Ввод

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

  • «*» и «/»;
  • «+» и «-».

    • Меняется последовательность математических действий и при помощи скобок – программа в первую очередь вычисляет значение в скобках.

    Постоянные и абсолютные ссылки

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

    Относительные ссылки помогают «растянуть» одну формулу на любое количество столбцов и строк. Как это работает на практике:

    1. Создать таблицу с нужными данными.

    1. Чтобы найти общую стоимость за каждый товар, нужно количество единиц умножить на цену. Для этого в первую ячейку вписать формулу через знак «=» и нажать Enter.

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

    Абсолютный адрес обозначается знаком «$». Форматы отличаются:

    1. Неизменна строка – A$1.
    2. Неизменен столбец – $A
    3. Неизменны строка и столбец – $A$1.

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

    1. Сначала посчитать общую стоимость. Это делается несколькими способами: обычным сложением всех значений или автосуммой (выделить столбец с одной пустой ячейкой и на вкладке «Главная» справа выбрать опцию «Сумма», либо вкладка «Формулы» – «Автосумма»).

    1. В отдельном столбце разделить стоимость первого товара на общую стоимость. При этом значение общей стоимости сделать абсолютным.

    1. Для получения результата в процентах можно произвести умножение на 100. Однако проще выделить ячейку и выбрать в разделе «Главная» формат в виде значка «%».

    1. С помощью маркера заполнения опустить формулу вниз. В итоге должно получиться 100%.

    Виды формул

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

    Простые

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

    СУММ

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

    =СУММ(значение1;значение2)

    =СУММ(начало_диапазона:конец_диапазона)

    ПРОИЗВЕД

    Перемножает все числа в выделенном диапазоне.

    = ПРОИЗВЕД(начало_диапазона:конец_диапазона)

    ОКРУГЛ

    Помогает произвести округление дробного числа в большую (ОКРУГЛВВЕРХ) или меньшую сторону (ОКРУГЛВНИЗ).

    ВПР

    Это – поиск необходимых данных в таблице или диапазоне по строкам. Рассмотрим функцию на примере поиска сотрудника из списка по коду.

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

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

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

    Альтернативные функции – ИНДЕКС/ПОИСКПОЗ.

    СЦЕПИТЬ/СЦЕП

    Объединение содержимого нескольких ячеек.

    =СЦЕПИТЬ(значение1;значение2) – цельный текст

    =СЦЕПИТЬ(значение1;» «;значение2) – между словами пробел или знак препинания

    КОРЕНЬ

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

    =КОРЕНЬ(ссылка_на_ячейку)

    =КОРЕНЬ(число)

    ПРОПИСН

    Альтернатива Caps Lock для преобразования текста.

    =ПРОПИСН(ссылка_на_ячейку_с_текстом)

    =ПРОПИСН(«текст»)

    СТРОЧН

    Преобразует текст в нижний регистр.

    =СТРОЧН(ссылка_на_ячейку_с_текстом)

    = СТРОЧН(«текст»)

    СЧЁТ

    Подсчитывает количество ячеек с числами.

    =СЧЁТ(диапазон_ячеек)                                                    

    СЖПРОБЕЛЫ

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

    =СЖПРОБЕЛЫ(адрес_ячейки)

    Сложные

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

    Важно! Критерии обязательно нужно брать в кавычки.

    ПСТР

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

    =ПСТР(ссылка_на_ячейку_с_текстом;начальная_числовая_позиция;число_знаков_которое_вытащить)

    ЕСЛИ

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

    =ЕСЛИ(какие_данные_проверяются;если_значение_отвечает_заданному_условию;если_значение_не_отвечает_заданному_условию)

    СУММЕСЛИ

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

    =СУММЕСЛИ(C2:C5;B2:B5;«90»)

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

    СУММЕСЛИМН

    Суммирование чисел на основе нескольких заданных условий.

    =СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)

    Программа посчитала общую сумму зарплат женщин-кассиров.

    По такому же принципу работают функции СЧЁТЕСЛИ, СРЗНАЧЕСЛИ и т.п.

    Комбинированные

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

    Есть задача: найти сумму трех чисел и умножить ее на коэффициент 1,3, если она меньше 80, и на коэффициент 1,6 – если больше 80.

    Решение следующее: =ЕСЛИ(СУММ(А2:С2)

    Комбинированные формулы могут включать 10 и более функций.

    Встроенные

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

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

    Вставка формулы в Excel — пошаговая инструкция и несколько способов

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

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

    Окно вставки функции

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

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

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

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

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

    Комьюнити теперь в Телеграм

    Подпишитесь и будьте в курсе последних IT-новостей

    Подписаться

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

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

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

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

    Ручная вставка формулы в Excel

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

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

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

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

    Вставка математических формул

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

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

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

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

    Переключение между относительными, абсолютными и смешанными ссылками

    Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel Web App Excel 2010 Excel 2007 Дополнительно…Меньше

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

    Например, если скопировать формулу =B4*C4  из ячейки D4 в ячейку D5, формула в ячейке D5 сместится вправо на один столбец и примет вид =B5*C5 . Если вы хотите сохранить исходную ссылку на ячейку в этом примере при ее копировании, сделайте ссылку на ячейку абсолютной, поставив перед столбцами (B и C) и строкой (2) знак доллара ( $ ). Затем, когда вы копируете формулу =$B$4*$C$4  из D4 в D5, формула остается точно такой же.

    Реже вам может понадобиться смешать абсолютные и относительные ссылки на ячейки , поставив перед значением столбца или строки знак доллара, который зафиксирует либо столбец, либо строку (например, $B4 или C$4).

    Чтобы изменить тип ссылки на ячейку:

    1. org/ListItem»>

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

    2. В строке формул выберите ссылку, которую вы хотите изменить.

    3. Нажмите F4 для переключения между типами ссылок.

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

    Для копируемой формулы:

    Если ссылка:

    Меняется на:

    $A$1 (абсолютный столбец и абсолютная строка)

    $A$1 (ссылка абсолютная)

    A$1 (относительный столбец и абсолютная строка)

    C$1 (референс смешанный)

    $A1 (абсолютный столбец и относительная строка)

    $A3 (ссылка смешанная)

    A1 (относительный столбец и относительный ряд)

    C3 (ссылка относительная)

    Формулы и функции в Excel (простое руководство)

    Введите формулу | Редактировать формулу | Приоритет оператора | Копировать/вставить формулу | Функция вставки

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

    Например, ячейка A3 ниже содержит формулу, которая суммирует значение ячейки A2 со значением ячейки A1.

    Например, ячейка A3 ниже содержит функцию СУММ, которая вычисляет сумму диапазона A1:A2.

    Введите формулу

    Чтобы ввести формулу, выполните следующие шаги.

    1. Выберите ячейку.

    2. Чтобы Excel знал, что вы хотите ввести формулу, введите знак равенства (=).

    3. Например, введите формулу A1+A2.

    Совет: вместо ввода A1 и A2 просто выберите ячейку A1 и ячейку A2.

    4. Измените значение ячейки A1 на 3.

    Excel автоматически пересчитает значение ячейки A3. Это одна из самых мощных функций Excel!

    Редактирование формулы

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

    1. Чтобы изменить формулу, щелкните в строке формул и измените формулу.

    2. Нажмите Enter.

    Приоритет оператора

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

    Сначала Excel выполняет умножение (A1 * A2). Затем Excel добавляет к этому результату значение ячейки A3.

    Другой пример:

    Сначала Excel вычисляет часть в скобках (A2+A3). Затем он умножает этот результат на значение ячейки A1.

    Копирование/вставка формулы

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

    1. Введите приведенную ниже формулу в ячейку A4.

    2а. Выберите ячейку A4, щелкните правой кнопкой мыши и выберите «Копировать» (или нажмите CTRL + c). ..

    … затем выберите ячейку B4, щелкните правой кнопкой мыши и выберите «Вставить» в разделе «Параметры вставки» (или нажмите CTRL + v).

    2б. Вы также можете перетащить формулу в ячейку B4. Выберите ячейку A4, щелкните в правом нижнем углу ячейки A4 и перетащите ее в ячейку B4. Это намного проще и дает точно такой же результат!

    Результат. Формула в ячейке B4 ссылается на значения в столбце B.

    Вставить функцию

    Все функции имеют одинаковую структуру. Например, СУММ(A1:A4). Имя этой функции SUM. Часть в скобках (аргументы) означает, что мы даем Excel диапазон A1: A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4. Нелегко запомнить, какую функцию и какие аргументы использовать для каждой задачи. К счастью, функция «Вставить функцию» в Excel поможет вам в этом.

    Чтобы вставить функцию, выполните следующие шаги.

    1. Выберите ячейку.

    2. Нажмите кнопку «Вставить функцию».

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