Как в эксель работать с формулами: Работа в Экселе с формулами и таблицами для начинающих

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

Опубликовано: Автор: Артём Чёрный

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

Содержание

  1. Как работать с формулами в MS Excel
  2. Работа с простейшими формулами
  3. Примеры вычислений
  4. Использование в качестве калькулятора

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

  • = («знак равно») – равенство, равно. («циркумфлекс») – возводит в степень.

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

Работа с простейшими формулами

Самыми простыми и в то же время самыми частыми формулами являются выражения арифметических действий между двумя ячейками таблицами. Задаются они по следующему алгоритму:

  1. В готовой таблице выделите ту ячейку, куда хотите получить результат вашей формулы.
  2. Поставьте в этой ячейке знак «=». Он отвечает за начало формулы. Также вы можете его поставить не в самой ячейке, а в специальном поле, что расположено сверху. Здесь уже как вам будет самим удобно. Рассмотрим пример формулы a+b.
  3. После знака равно кликните левой кнопкой мыши по ячейки, значение из которой вам нужно взять в качестве первого.
  4. Автоматически установится наименование этой ячейки (латинская буква и номер) после знака равно. Здесь же вам требуется установить оператор, отвечающий за действие с этими ячейками – плюс, минус, равно, делить и т. д.
  5. Нажмите теперь левой кнопкой мыши по той ячейки, которая будет выполнять функции b в нашей формуле.
  6. Чтобы получить результат расчётов, нажмите на клавишу Enter.

По такой инструкции можно делать формулы с несколькими переменными, а не только вида a+b. Например, вы можете сделать по аналогии формулу вида a+b+c, a+b+c+d и т.д.

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

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

  1. Задайте формулу вида a*b, чтобы рассчитать полученную сумму от продажи товара. Вид у формулы в Excel будет таким: =(название ячейки с количеством товара, например, B3)*(название ячейки с ценой, например, C3).
  2. Нажмите Enter, чтобы увидеть полученный результат в ячейки суммы у определённого товара. Учтите, что в формуле не должно быть пустых ячеек. В противном случае вместо результата вы получите просто сообщение об ошибке.
  3. Теперь вам нужно применить эту формулу для всех товаров в таблице, но вот проблема в том, что вписывать её вручную для каждой из позиций не очень удобно. Разработчики программы это предусмотрели. Для того, чтобы формула автоматически применилась к другим позициям, нужно просто выделить ячейку с ней, навести курсор на правый нижний угол ячейки и протянуть его вниз, чтобы захватить все товары.
  4. Формула автоматически скопируется и адаптируется под ячейки ниже, то есть у вас будет подсчитана цена именно за тот товар, который вам нужен.

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

Читайте также:
Поиск и удалени циклических ссылок в Excel (Эксель)
Способы объединения строк в редакторе Excel без потери данных
Умная таблица в Excel (Эксель): создание и использование
Горячие клавиши Excel 2016 — инструкция как пользоваться

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

  1. В ячейку напротив столбца «Сумма» укажите формулу вида: =([название ячейки с количеством первой партии товара, например, B3]+[название ячейки с количеством второй партии товара, например, C3])*[название ячейки с ценой, например, D3].
  2. Нажмите на клавишу Enter, чтобы получить конечный результат вычислений.
  3. По аналогии с прошлой таблицей зажмите правый нижней угол ячейки и проведите его вниз. Все значения в формуле будут подставлены автоматически.

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

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

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

  1. На листе выберите ячейку, в которой собираетесь происходить расчёты.
  2. Выделите эту ячейку и поставьте в ней знак равно.
  3. Пропишите нужные действия. Сюда можно прописать и сложный пример по типу: 444*89+((78-660)*9)/15).
  4. Чтобы получить результат ваших вычислений, нажмите на кнопку Enter на клавиатуре.

В ходе этой статьи мы рассмотрели основные аспекты работы с формулами в табличном редакторе 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 .

Например, ячейка 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. Нажмите кнопку «Вставить функцию».

Появится диалоговое окно «Вставить функцию».

3. Найдите функцию или выберите функцию из категории. Например, выберите СЧЁТЕСЛИ из категории Статистические.

4. Нажмите OK.

Появится диалоговое окно «Аргументы функции».

5. Щелкните в поле Диапазон и выберите диапазон A1:C2.

6. Щелкните в поле Критерии и введите >5.

7. Нажмите OK.

Результат. Функция СЧЁТЕСЛИ подсчитывает количество ячеек, превышающих 5.

Примечание. Вместо использования функции вставки просто введите =СЧЁТЕСЛИ(A1:C2,»>5″). Когда вы дойдете до: =COUNTIF( вместо ввода A1:C2, просто выберите диапазон A1:C2.

Как выполнять вычисления в Excel

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

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

  • Как производить расчеты в Excel
  • Порядок, в котором выполняются вычисления Excel
  • Как изменить порядок вычислений в Excel

Как выполнять расчеты в Excel

Выполнять расчеты в Excel очень просто. Вот как:

  • Введите в ячейку символ равенства (=). Это сообщает Excel, что вы вводите формулу, а не просто числа.
  • Введите уравнение, которое вы хотите рассчитать. Например, чтобы сложить 5 и 7, введите =5+7

    • .

  • Нажмите клавишу Enter, чтобы завершить расчет. Сделанный!

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

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

Эксплуатация Оператор Пример Описание
Дополнение + (плюс) =А1+А2 Складывает числа в ячейках A1 и A2.
Вычитание — (знак минус) =А1-А2 Вычитает число в A2 из числа в A1. 9(1/3) Находит кубический корень из числа в ячейке A1.

Результаты приведенных выше расчетных формул Excel могут выглядеть примерно так:

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

=A2&" "&B2&" "&C2

Пробел (» «) ставится между ячейками для разделения слов:

Вы также можете сравнивать ячейки с помощью логических операторов, таких как «больше» (>), «меньше» (<), «больше или равно» (>=) и «меньше или равно» ( <=). Результатом сравнения являются логические значения ИСТИНА и ЛОЖЬ:

Порядок выполнения вычислений Excel

При выполнении двух и более вычислений в одной формуле Microsoft Excel вычисляет формулу слева направо, в соответствии с порядок операций, показанный в этой таблице: 9) 4 Умножение (*) и деление (/), в зависимости от того, что наступит раньше 5 Сложение (+) и вычитание (-), в зависимости от того, что наступит раньше 6 Конкатенация (&) 7 Сравнение (>, <, >=, <=, =)

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

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

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

Например, вычисление =2*4+7 указывает Excel умножить 2 на 4, а затем добавить 7 к произведению. Результат этого вычисления равен 15. Заключив операцию сложения в круглые скобки =2*(4+7) , вы указываете Excel сначала сложить 4 и 7, а затем умножить сумму на 2. И результат этого расчет 22.

9(1/2)

Технически приведенное выше уравнение говорит Excel возвести 16 в степень 1/2. Но почему мы заключаем 1/2 в скобки? Потому что, если мы этого не сделаем, Excel сначала возведет 16 в степень 1 (перед делением выполняется операция возведения в степень), а затем разделит результат на 2. Поскольку любое число, возведенное в степень 1, является самим числом, мы в итоге получится деление 16 на 2. Напротив, заключая 1/2 в круглые скобки, вы указываете Excel сначала разделить 1 на 2, а затем возвести 16 в степень 0,5.

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