Эксель сложные формулы
Создание сложных формул в Microsoft Excel
Перейти к содержанию
Search for:
Главная » Уроки MS Excel
Автор Антон Андронов На чтение 2 мин Опубликовано
В этом уроке мы разберем, как создать сложную формулу в Excel, а также рассмотрим типичные ошибки, возникающие у начинающих пользователей по невнимательности. Если Вы совсем недавно работаете в Excel, то советуем сначала обратиться к уроку, где мы обсуждали создание простых формул.
Как создать сложную формулу в Excel
В приведенном ниже примере, мы продемонстрируем, каким образом Excel вычисляет сложные формулы, опираясь на порядок выполнения операций.
В данном примере мы хотим вычислить величину налога с продаж за услуги по питанию. Чтобы это осуществить, запишем следующее выражение в ячейке D4: =(D2+D3)*0,075. Эта формула сложит стоимость всех позиций счета, а затем умножит на размер налога с продаж 7,5% (записанный как 0,075).
Excel придерживается порядка действий и сначала складывает значения в скобках: (44.85+39.90)=$84.75. Затем умножает это число на налоговую ставку: $84.75*0.075. Результат вычислений показывает, что налог с продаж составит $6.36.
Чрезвычайно важно вводить сложные формулы с верным порядком действий. Иначе расчеты Excel могут оказаться неточными. В нашем случае при отсутствии скобок, в первую очередь выполняется умножение, и результат будет неверным. Скобки являются лучшим способом определения порядка вычислений в Excel.
Создание сложных формул, используя порядок действий
В примере ниже мы воспользуемся ссылками совместно с количественными данными для создания сложной формулы, которая вычислит полную стоимость по счету за обеспечение питанием. Формула вычислит стоимость каждого пункта меню, а затем сложит все значения вместе.
- Выделите ячейку, которая будет содержать формулу. В нашем примере мы выбрали ячейку C4.
- Введите в нее следующее выражение: =B2*C2+B3*C3 . Действия в формуле будут выполняться в соответствии с правилами порядка, следовательно, первым идет умножение: 2.29*20=45.80 и 3.49*35=122.15. Затем эти значения будут суммированы для вычисления полной стоимости: 45.80+122.15.
- Выполните проверку, затем нажмите Enter на клавиатуре. Формула вычислит и отобразит результат. В нашем случае результат вычислений показывает, что полная стоимость заказа составляет $167.95.
Вы можете добавить скобки в любую формулу, чтобы упростить ее восприятие. Несмотря на то, что в данном примере это не изменит результат вычислений, мы все равно можем заключить умножение в скобки. Этим мы уточним, что оно выполняется до сложения.
Excel не всегда предупреждает об ошибках в формуле, поэтому Вам необходимо самостоятельно проверять все Ваши формулы. Чтобы узнать, как это можно сделать, изучите урок Проверка формул.
Оцените качество статьи. Нам важно ваше мнение:
Adblockdetector
как пользоваться, создавать и изменять
Формулы Excel используют, когда данных очень много. Например, чтобы посчитать сумму нескольких чисел быстрее, чем на калькуляторе. Преимуществ много, поэтому работодатели часто указывают эту программу в требованиях. В конце марта 2022 года 64 225 вакансий на хедхантере содержали формулировки вроде «уверенный пользователь Excel», «работа с формулами в Excel».
Кому важно знать Excel и где выучить основы
Excel нужен бухгалтерам, чтобы вести учет в таблицах. Экономистам, чтобы делать перерасчет цен, анализировать показатели компании. Менеджерам — вести базу клиентов. Аналитикам — строить и проверять гипотезы.
Программу можно освоить самостоятельно, например по статьям в интернете. Но это поможет понять только основные формулы. Если нужны глубокие знания — как строить сложные прогнозы, собирать калькулятор юнит-экономики, — пройдите курсы.
На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой. Кроме Excel вы изучите Metabase, SQL, Power BI, язык программирования Python. Программа подойдет даже тем, у кого совсем нет опыта в анализе и кто не любит математику. Вас ждут живые вебинары, мастер-классы, домашки с разбором, помощь наставников.
Урок из курса «Аналитик данных» в Skypro
Из чего состоит формула в Excel
Основные знаки:
= с него начинают любую формулу;
( ) заключают формулу и ее части;
; применяют, чтобы указать очередность ячеек или действий;
: ставят, чтобы обозначить диапазон ячеек, а не выбирать всё подряд вручную.
![]()
Еще используют символы сравнения:
равенство =
меньше <
больше >
меньше либо равно <=
больше либо равно >=
не равно <>
Основные виды
Все формулы в Excel делятся на простые, сложные и комбинированные. Их можно написать самостоятельно или воспользоваться встроенными.
Простые
Применяют, когда нужно совершить одно простое действие, например сложить или умножить.
✅ СУММ. Складывает несколько чисел. Сумму можно посчитать для нескольких ячеек или целого диапазона.
=СУММ(А1;В1) — для соседних ячеек;
=СУММ(А1;С1;h2) — для определенных ячеек;
=СУММ(А1:Е1) — для диапазона.
Сумма всех чисел в ячейках от А1 до Е1
✅ ПРОИЗВЕД. Умножает числа в соседних, выбранных вручную ячейках или диапазоне.
=ПРОИЗВЕД(А1;В1)
=ПРОИЗВЕД(А1;С1;h2)
=ПРОИЗВЕД(А1:Е1)
Произведение всех чисел в ячейках от А1 до Е1
✅ ОКРУГЛ. Округляет дробное число до целого в большую или меньшую сторону. Укажите ячейку с нужным числом, в качестве второго значения — 0.
=ОКРУГЛВВЕРХ(А1;0) — к большему целому числу;
=ОКРУГЛВНИЗ(А1;0) — к меньшему.
Округление в меньшую сторону
✅ ВПР. Находит данные в таблице или определенном диапазоне.
=ВПР(С1;А1:В6;2)
- С1 — ячейка, в которую выписывают известные данные. В примере это код цвета.
- А1 по В6 — диапазон ячеек. Ищем название цвета по коду.
- 2 — порядковый номер столбца для поиска. В нём указаны названия цвета.
Формула вычислила, какой цвет соответствует коду
✅ СЦЕПИТЬ. Объединяет данные диапазона ячеек, например текст или цифры. Между содержимым ячеек можно добавить пробел, если объединяете слова в предложения.
=СЦЕПИТЬ(А1;В1;С1) — текст без пробелов;
=СЦЕПИТЬ(А1;» «;В1;» «С1) — с пробелами.
Формула объединила три слова в одно предложение
✅ КОРЕНЬ. Вычисляет квадратный корень числа в ячейке.
=КОРЕНЬ(А1)
Квадратный корень числа в ячейке А1
✅ ПРОПИСН. Преобразует текст в верхний регистр, то есть делает буквы заглавными.
=ПРОПИСН(А1:С1)
Формула преобразовала строчные буквы в прописные
✅ СТРОЧН. Переводит текст в нижний регистр, то есть делает из больших букв маленькие.
=СТРОЧН(А2)
✅ СЧЕТ. Считает количество ячеек с числами.
=СЧЕТ(А1:В5)
Формула вычислила, что в диапазоне А1:В5 четыре ячейки с числами
✅ СЖПРОБЕЛЫ. Убирает лишние пробелы. Например, когда переносите текст из другого документа и сомневаетесь, правильно ли там стоят пробелы.
=СЖПРОБЕЛЫ(А1)
Формула удалила двойные и тройные пробелы
Сложные
✅ ПСТР. Выделяет определенное количество знаков в тексте, например одно слово.
=ПСТР(А1;9;5)
- Введите =ПСТР.
- Кликните на ячейку, где нужно выделить знаки.
- Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
- Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 5.
В ячейке А1 формула выделила 5 символов, начиная с 9-го
✅ ЕСЛИ. Анализирует данные по условию. Например, когда нужно сравнить одно с другим.
=ЕСЛИ(A1>25;"больше 25";"меньше или равно 25")
В формуле указали:
- А1 — ячейку с данными;
- >25 — логическое выражение;
- больше 25, меньше или равно 25 — истинное и ложное значения.
Первый результат возвращается, если сравнение истинно. Второй — если ложно.
Число в А1 больше 25. Поэтому формула показывает первый результат — больше 25.
✅ СУММЕСЛИ. Складывает числа, которые соответствуют критерию. Обычно критерий — числовой промежуток или предел.
=СУММЕСЛИ(В2:В5;">10")
В формуле указали:
- В2:В5 — диапазон ячеек;
- >10 — критерий, то есть числа меньше 10 не будут суммироваться.
Число 8 меньше указанного в условии, то есть 10. Поэтому оно не вошло в сумму.
✅ СУММЕСЛИМН. Складывает числа, когда условий несколько. В формуле указывают диапазоны — ячейки, которые нужно учитывать. И условия — содержание подходящих ячеек. Например:
=СУММЕСЛИМН(D2:D6;C2:C6;"сувениры";B2:B6;"ООО ХY")
- D2:D6 — диапазон, из которого суммируем числа;
- C2:C6 — диапазон ячеек для категории;
- сувениры — условие, то есть числа другой категории учитываться не будут;
- B2:B6 — диапазон ячеек для компании;
- ООО XY — условие, то есть числа другой компании учитываться не будут.
Под условия подошли только ячейки D3 и D6: их сумму и вывела формула
Комбинированные
В Excel можно комбинировать несколько функций: сложение, умножение, сравнение и другие. Например, вам нужно найти сумму двух чисел. Если значение больше 65, сумму нужно умножить на 1,5. Если меньше — на 2.
=ЕСЛИ(СУММ(A1;B1)<65;СУММ(A1;B1)*1,5;(СУММ(A1;B1)*2))
То есть если сумма двух чисел в А1 и В1 окажется меньше 65, программа посчитает первое условие — СУММ(А1;В1)*1,5. Больше 65 — Excel задействует второе условие — СУММ(А1;В1)*2.
Сумма в А1 и В1 больше 65, поэтому формула посчитала по второму условию: умножила на 2
Встроенные
Используйте их, если удобнее пользоваться готовыми формулами, а не вписывать вручную.
- Поместите курсор в нужную ячейку.
- Откройте диалоговое окно мастера: нажмите клавиши Shift + F3. Откроется список функций.
- Выберите нужную формулу. Нажмите на нее, затем на «ОК». Откроется окно «Аргументы функций».
- Внесите нужные данные. Например, числа, которые нужно сложить.
Ищите формулу по алфавиту или тематике, выбирайте любую из тех, что использовали недавно
Как скопировать
Если для разных ячеек нужны однотипные действия, например сложить числа не в одной, а в нескольких строках, скопируйте формулу.
- Впишите функцию в ячейку и кликните на нее.
- Наведите курсор на правый нижний угол — курсор примет форму креста.
- Нажмите левую кнопку мыши, удерживайте ее и тяните до нужной ячейки.
- Отпустите кнопку. Появится итог.
Посчитали сумму ячеек в трех строках
Как обозначить постоянную ячейку
Это нужно, чтобы, когда вы протягивали формулу, ссылка на ячейку не смещалась.
- Нажмите на ячейку с формулой.
- Поместите курсор в нужную ячейку и нажмите F4.
- В формуле фрагмент с описанием ячейки приобретет вид $A$1. Если вы протянете формулу, то ссылка на ячейку $A$1 останется на месте.
Как поставить «плюс», «равно» без формулы
Когда нужна не формула, а данные, например +10 °С:
- Кликните правой кнопкой по ячейке.
- Выберите «Формат ячеек».
- Отметьте «Текстовый», нажмите «ОК».
- Поставьте = или +, затем нужное число.
- Нажмите Enter.
Вебинары
Главное о формулах в Excel
- Формула состоит из математических знаков.
Чтобы ее вписать, используют символы = ( ) ; : .
- С помощью простых формул числа складывают, умножают, округляют, извлекают из них квадратный корень. Чтобы отредактировать текст, используют формулы поиска, изменения регистра, удаления лишних пробелов.
- Сложные и комбинированные формулы помогают делать объемные вычисления, когда нужно соблюдать несколько условий.
Формулы Excel: сложные формулы
Урок 3: сложные формулы
/en/excelformulas/simple-formulas/content/
Введение
Простая формула — это математическое выражение с одним оператором, например 7+9 . Сложная формула содержит более одного математического оператора, например 5+2*8 . Когда в формуле есть более одной операции, порядок операций сообщает вашей электронной таблице, какую операцию вычислять первой. Чтобы использовать сложные формулы, вам нужно будет понять порядок операций. 92, например)
A Mnemonic, который может помочь вам, что вы можете запомнить познание. PEMDAS или P аренда E извините M y D ухо A unt S союзник.
Щелкните стрелки в слайд-шоу ниже, чтобы узнать больше о том, как порядок операций используется для вычисления сложных формул. 92=4.
Далее мы решим любое умножение и деление слева направо. Поскольку операция деления предшествует умножению, она вычисляется первой: 3/4=0,75.
Теперь вычислим оставшуюся операцию умножения: 0,75*4=3.
Далее мы вычислим любое сложение или вычитание, снова работая слева направо. Сначала идет сложение: 10+3=13.
Наконец, у нас осталась одна операция вычитания: 13-1=12.
Теперь у нас есть ответ: 12. Это точно такой же результат, который вы получили бы, если бы ввели формулу в электронную таблицу.
Теперь давайте рассмотрим пару примеров, которые показывают, как порядок операций может повлиять на результат.
Использование скобок в формуле может быть очень важным. Из-за порядка операций он может полностью изменить ответ. Давайте попробуем решить ту же задачу, что и выше, но на этот раз мы добавим круглые скобки в последнюю часть.
Создание сложных формул
В приведенном ниже примере мы продемонстрируем сложную формулу, используя порядок операций. Здесь мы хотим рассчитать стоимость налога с продаж для счета за питание. Для этого запишем нашу формулу в виде =(D2+D3)*0,075 в ячейке D4 . Эта формула суммирует цены наших товаров, а затем умножает это значение на налоговую ставку 7,5% (которая записывается как 0,075) для расчета стоимости налога с продаж.
Электронная таблица следует порядку операций и сначала добавляет значения в круглых скобках: (44,85+39,90) = 84,75 долларов США. Затем это значение умножается на налоговую ставку: $84,75*0,075 . Результат покажет, что налог с продаж составляет $6,36 .
Особенно важно вводить сложные формулы с правильным порядком действий. В противном случае электронная таблица не будет точно рассчитывать результаты. В нашем примере, если круглых скобок не включены, сначала вычисляется умножение, и результат неверен. Круглые скобки — лучший способ определить, какие вычисления будут выполняться в формуле первыми.
Чтобы создать сложную формулу с использованием порядка операций:
В приведенном ниже примере мы будем использовать ссылок на ячейки вместе с числовыми значениями , чтобы создать сложную формулу, которая будет вычислять общую стоимость для счета за питание. . Формула рассчитает стоимость каждого пункта меню и сложит эти значения.
- Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку C4 9.0010 .
- Введите формулу . В нашем примере мы введем =B2*C2+B3*C3 . Эта формула будет следовать порядку операций, сначала выполняя умножение: 2,29*20 = 45,80 и 3,49*35 = 122,15 . Затем он сложит эти значения вместе, чтобы вычислить общее количество: 45,80+122,15 .
- Дважды проверьте формулу на точность, затем нажмите Введите на клавиатуре. Формула вычислит и отобразит результат . В нашем примере результат показывает, что общая стоимость заказа составляет 167,95 $ .
Вы можете добавить круглых скобок к любому уравнению, чтобы его было легче читать. Хотя это не изменит результат формулы в этом примере, мы могли бы заключить операции умножения в круглые скобки, чтобы уточнить, что они будут вычисляться перед сложением.
Ваша электронная таблица не всегда будет сообщать вам , если ваша формула содержит ошибку, поэтому вы должны проверить все свои формулы. Чтобы узнать, как это сделать, ознакомьтесь с уроком «Перепроверьте свои формулы».
Вызов!
- Откройте существующую книгу Excel. Если вы хотите, вы можете использовать файл примера для этого урока.
- Создайте сложную формулу, которая будет выполнять сложение перед умножением. Если вы используете пример, создайте формулу в ячейке D6 , которая сначала добавляет 9010 значений ячеек D3 , D4 и D5 , а затем умножает на 0,0075 90. Подсказка: вам нужно подумать о порядке операций, чтобы это работало правильно.
Предыдущий: Простые формулы
Далее:Относительные и абсолютные ссылки на ячейки
/en/excelformulas/relative-and-absolute-cell-references/content/
Excel 2010: Создание сложных формул
Урок 9: Создание сложных формул
/en/excel2010/printing/content/
Создание сложных формулы
Excel — это приложение для работы с электронными таблицами, которое может помочь вам рассчитать и проанализировать числовую информацию для семейного бюджета, финансов компании, запасов и многого другого. Для этого нужно понять сложных формул .
На этом уроке вы научитесь писать сложные формулы в Excel, соблюдая порядок операций. Вы также узнаете о относительных и абсолютных ссылках на ячейки , а также о том, как скопировать и заполнить формулы , содержащий ссылки на ячейки.
Сложные формулы
Простые формулы содержат одну математическую операцию, например 5+5 . Сложные формулы содержат более одной математической операции, например 5+5-2 . Когда в формуле более одной операции, порядок операций говорит нам, какую операцию вычислять первой. Чтобы использовать Excel для расчета сложных формул, вам необходимо понимать порядок операций.
Необязательно: Вы можете загрузить этот пример для дополнительной практики.
Орден операций
Excel рассчитывает формулы на основе следующих Орден операций :
- Операции, заключенные в Sacenses
- Exponential Nextential 110 . , что наступит раньше
- Сложение и вычитание , что наступит раньше
Мнемоника, которая поможет вам запомнить порядок: P аренда E извините M y D ухо A unt S союзник.
Пример 1
В следующем примере показано, как использовать порядок операций для вычисления формулы:
Пример порядка действий
Пример 2
В этом примере мы рассмотрим, как Excel будет вычислять сложную формулу с помощью порядок операций. В выбранной ячейке будет отображаться процент белых проданных семян Pete Lily.
Порядок действий Пример Excel
- Сначала Excel рассчитает количество проданных семян в скобках: (19*1,99)=37,81 семян белой лилии и (33*1,99)=65,67 семян лилии.
- Во-вторых, количество семян Белой Лилии Пита будет разделено на общее количество семян Лилии Пита: 37,81/65,67=0,5758 .
- Наконец, он умножит результат на 100, чтобы получить значение в процентах: .5758*100=57,58 .
Основываясь на этой сложной формуле, результат покажет, что 57,58% от общего числа проданных семян Пит Лили были белыми. Из этого примера видно, что важно вводить сложные формулы с правильным порядком операций. В противном случае Excel не будет точно вычислять результаты.
Чтобы создать сложную формулу, используя порядок операций:
В этом примере мы будем использовать ссылок на ячейки в дополнение к фактическим значениям, чтобы создать сложную формулу, которая будет добавлять налог к заказу питомника.
- Щелкните ячейку, в которой должен появиться результат формулы (например, F11 ).
- Введите знак равенства (=) .
- Введите открывающую скобку , затем щелкните ячейку, содержащую первое значение , которое вы хотите использовать в формуле (например, F4 ).
- Введите первый математический оператор (например, знак сложения).
- Щелкните ячейку, содержащую второе значение вы хотите в формуле ( F5, например), затем введите закрывающую скобку .
- Введите следующий математический оператор (например, знак умножения).
- Введите следующее значение в формулу (например, 0,055 вместо 5,5% налога ).
Ввод формулы
- Нажмите Введите для расчета формулы.
Результаты показывают, что 2,12 доллара — это налог на заказ питомника.
Результат в F11
Excel не всегда будет сообщать вам , если ваша формула содержит ошибку, так что вы должны проверить все свои формулы. Чтобы узнать, как это сделать, вы можете прочитать урок «Перепроверьте свои формулы» из нашего учебника по формулам Excel.
Работа со ссылками на ячейки
Чтобы поддерживать точные формулы, необходимо понимать, как реагируют ссылки на ячейки, когда вы копируете или заполняете их новыми ячейками на листе.
Excel будет интерпретировать ссылки на ячейки как относительные или абсолютные . По умолчанию ссылки на ячейки представляют собой относительных ссылок . При копировании или заполнении они изменяются в зависимости от относительного положения строк и столбцов. Если вы скопируете формулу (=A1+B1) в строку 2, формула изменится на (=A2+B2).
Абсолютные ссылки , с другой стороны, не изменяются при копировании или заполнении и используются, когда вы хотите, чтобы значения оставались прежними.
Относительные ссылки
Относительные ссылки могут сэкономить ваше время, когда вы повторяете один и тот же тип вычислений в нескольких строках или столбцах.
В следующем примере мы создаем формулу со ссылками на ячейки в строке 4 для расчета общей стоимости счетов за электричество и воду за каждый месяц (B4=B2+B3). В ближайшие месяцы мы хотим использовать ту же формулу с относительными ссылками (C2+C3, D2+D3, E2+E3 и т. д.). Для удобства мы можем скопировать формулу из B4 в остальную часть строки 4, и Excel рассчитает стоимость счетов за эти месяцы, используя относительные ссылки.
Чтобы создать и скопировать формулу с использованием относительных ссылок:
- Выберите первую ячейку, в которую вы хотите ввести формулу (например, B4 ).
Выбор ячейки B4
- Введите формулу для расчета нужного значения (например, B2+B3 ).
Ввод формулы в ячейку B4
- Нажмите Введите . Формула будет рассчитана.
Результат в B4
- Выберите ячейку, которую вы хотите скопировать ( B4 ), затем нажмите команду Копировать на вкладке Главная .
- Выберите ячейки, в которые вы хотите вставить формулу, затем нажмите команду Вставить на вкладке Главная . Вы также можете перетащить маркер заполнения, чтобы заполнить ячейки.
Значения, рассчитанные в C4:M4
- Ваша формула копируется в выбранные ячейки как относительная ссылка (C4=C2+C3, D4=D2+D3, E4=E2+E3 и т. д.), и значения вычисляются .
Абсолютные ссылки
В некоторых случаях вы не хотите, чтобы ссылка на ячейку менялась при копировании или заполнении ячеек. Вы можете использовать абсолютную ссылку , чтобы сохранить константу строки и/или столбца в формуле.
Абсолютная ссылка в формуле обозначается добавлением знака доллара ($) перед столбцом и строкой. Если он предшествует столбцу или строке (но не обоим), он называется смешанной ссылкой .
В приведенном ниже примере мы хотим рассчитать налог с продаж для списка продуктов с разными ценами. Мы будем использовать абсолютную ссылку для налога с продаж ($B$1), потому что мы не хотим, чтобы он менялся, поскольку мы копируем формулу вниз по столбцу с различными ценами.
Чтобы создать и скопировать формулу с использованием абсолютной ссылки:
- Выберите первую ячейку, в которую вы хотите ввести формулу (например, C4 ).
Выбор ячейки C4
- Введите знак равенства, а затем щелкните ячейку, содержащую первое значение , которое вы хотите использовать в формуле (например, B4 ).
- Введите первый математический оператор (например, знак умножения).
- Введите знак доллара ($) , затем введите букву столбца ячейки, на которую вы делаете абсолютную ссылку (например, B ).
Ввод формулы
- Введите знак доллара ($) , затем введите номер строки той же ячейки, на которую вы делаете абсолютную ссылку (например, 1 ).
Ввод формулы
- Нажмите Введите для вычисления формулы.
Результат в C4
- Выберите ячейку, которую вы хотите скопировать ( C4 ), затем нажмите команду Копировать на вкладке Главная .
- Выберите ячейки, в которые вы хотите вставить формулу, затем нажмите команду Вставить на вкладке Главная .
Вы также можете перетащить маркер заполнения, чтобы заполнить ячейки.
Значения, рассчитанные в C5:C8
- Ваша формула копируется в выбранные ячейки с использованием абсолютной ссылки (C5=B5*$B$1, C6=B6*$B$1 и т. д.), и вычисляются ваши значения.
При написании формулы вы можете нажать клавишу F4 на клавиатуре, чтобы переключиться между относительными и абсолютными ссылками на ячейки, как показано в видео ниже. Это простой способ быстро вставить абсолютную ссылку.
Вызов!
- Откройте существующую книгу Excel 2010 . Если хотите, можете использовать этот пример.
- Создайте формулу, использующую абсолютную ссылку . Если вы используете пример, рассчитайте налог с продаж в Е4:Е20 . Используйте ячейку C23 в качестве абсолютной ссылки на цену налога с продаж.