Excel сложные формулы: Создание сложных формул в Microsoft Excel

Содержание

Создание сложных формул в Microsoft Excel

Перейти к содержанию

Search for:

Главная » Уроки MS Excel

Автор Антон Андронов На чтение 2 мин Опубликовано 13.01.2015

В этом уроке мы разберем, как создать сложную формулу в 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 не всегда предупреждает об ошибках в формуле, поэтому Вам необходимо самостоятельно проверять все Ваши формулы. Чтобы узнать, как это можно сделать, изучите урок Проверка формул.

Оцените качество статьи. Нам важно ваше мнение:

Adblock
detector

Наглядность в сложных формулах

32348
10.12.2017
Скачать пример

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

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

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

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

комментарии для объяснения сложных или неочевидных фрагментов кода

Нечто похожее можно изобразить и с формулами в Excel.

Переносы и отступы

Для разбиения длинной формулы на несколько отдельных строк, например, по отдельным функциям или аргументам, можно использовать сочетание клавиш ALT+Enter, предварительно установив курсор в строке формул в нужное место. Саму строку формул (начиная с версии Excel 2007) можно спокойно увеличить по высоте, потянув за нижний край:

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

Если же формула или аргумент должен выдавать не число, а текст, то функцию Ч плюсовать уже нельзя — получим ошибку ЗНАЧ, т.к. нельзя складывать числа и текст. Для комментирования текста вместо нее можно использовать функцию ПОВТОР (REPT). Она, по идее, повторяет заданный текст N-ое количество раз, но N мы можем задать равное нулю и приклеить получившуюся пустую строку к нашей формуле с помощью символа склеивания &:

В комплексе, все вышеперечисленное позволяет ощутимо облегчить понимание сложных формул в «тяжелых случаях»:

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

Ссылки по теме

Прятки с формулами

Удобный одновременный просмотр формул и результатов

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

сложных формул в Excel | Примеры сложных формул в Excel

Сложные формулы в Excel (оглавление)

Введение в сложные формулы в Excel
Выбрать функцию в качестве функции поиска
ВПР с ВЫБОРОМ
ЕСЛИ с функцией И и ИЛИ

Excel содержит множество полезных формул; у нас есть категория «Финансы», «Логика», «Текст», «Дата и время», «Поиск и ссылки» и «Математика и триггер». Каждая отдельная формула достаточно хороша для пользователей среднего уровня, когда они сталкиваются с прямыми ситуациями, но по мере того, как вы переходите на следующий уровень, ваши навыки должны повышаться, поэтому в этом отношении применение более сложных формул в Excel играет жизненно важную роль в решении сложные ситуации. Помня об этом, в этой статье мы познакомим вас с некоторыми важными и столь необходимыми сложными формулами. Читать дальше.

Функция выбора Работает в качестве функции поиска

Не многие из вас использовали функцию ВЫБОР, но она также очень полезна в качестве функции поиска.

Вы можете скачать этот шаблон Excel для сложных формул здесь — Шаблон Excel для сложных формул

Например, посмотрите на данные ниже.

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

Создайте одну таблицу, как показано ниже.

Примените функцию ВПР к ячейке D13.

Теперь откройте функцию ВЫБОР в ячейке B14.

Выберите номер ИНДЕКС в качестве ячейки ВПР и сделайте его абсолютной ссылкой.

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

Закройте скобку и нажмите клавишу ввода.

Теперь скопируйте и вставьте формулу в ячейки ниже.

Теперь формула ВЫБОР покажет номера выбранного месяца.

ВПР с функцией ВЫБОР

Мы видели традиционную ВПР, но мы можем совместить ее и с функцией ВЫБОР. Например, посмотрите на данные ниже.

В ячейках от A1 до B11 у нас есть таблица продаж, а в ячейке D2 я создал раскрывающийся список месяцев, а в ячейке E2 нам нужно применить ВПР и получить значение продаж за выбранный месяц. Но проблема с применением ВПР заключается в структуре данных, где искомое значение находится справа от столбца результатов, поэтому ВПР не может получить данные справа налево; в этом случае мы можем использовать функцию ВЫБОР.

Чтобы выбрать массив таблиц, нам нужно открыть здесь функцию ВЫБОР.

В аргументе Номер ИНДЕКС в цветке скобки введите 1 и 2. Теперь выберите первый диапазон в качестве столбца Месяц.

Выберите столбец Продажи в качестве второго диапазона.

Укажите порядковый номер столбца как 2 и поиск диапазона как 0.

Итак, мы получили результат за апрель из таблицы. Таким образом, если у вас нет точной структуры данных, мы можем выбрать функцию ВЫБОР.

ЕСЛИ с функцией И и ИЛИ

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

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

Если Сервису больше 1,5 лет и если отдел либо Продажи, либо Поддержка, то Бонус 1000.
Если что, то Бонус 3000.

Чтобы рассчитать бонус, нам нужно применить операторы ЕСЛИ, И и ИЛИ вместе. Откройте условие ЕСЛИ в ячейке D2.

Нам нужно проверить несколько условий, открыть функцию И.

Теперь следующим логическим является либо продажа, либо поддержка, поэтому для этого открытого условия ИЛИ.

Проверьте, является ли заголовок «Продажи» или «Поддержка».

Итак, если все поставленные условия ИСТИНА, то бонус должен быть 10000.

Если предоставленные логические тесты ЛОЖЬ, то бонус равен 3000.

Закройте скобку и нажмите клавишу Enter ; мы должны получить результат.

Скопируйте и вставьте формулу в ячейки ниже.

Итак, по условию мы получили результат. Если срок службы превышает 1,5 года, а название — «Продажи» или «Поддержка», мы получим 10 000 или 3000.

Сложная формула с подстановочными знаками данные. Это традиционный слоган, но мы по-прежнему можем получать данные, используя значение поиска частиц. Например, если искомое значение — «VIVO» и находится в основной таблице, мы все равно можем сопоставить с помощью подстановочных знаков, если это «VIVO Mobile». Сейчас мы увидим один из примеров; ниже пример данных.

У нас есть таблица поиска в столбце A. В столбце C у нас есть значения поиска; эти значения поиска не совсем совпадают со значениями таблицы поиска. Итак, мы увидим, как применять функцию ВПР с использованием подстановочных знаков.

Сначала откройте функцию ВПР в ячейке D1.

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

Здесь мы применили две звездочки: «*»&C2&»*». Здесь звездочка указывает, что все, что находится между подстановочными знаками, должно совпадать и возвращать соответствующий результат.

Несмотря на то, что у нас было просто «Infosys», символ звездочки соответствовал значению в таблице поиска и возвращал точный результат как «Infosys Ltd».

Точно так же в ячейке D6 мы получили значение ошибки как #ЗНАЧ! Потому что в таблице поиска нет слова «Минтра».

Видео: более сложные формулы в Excel 2013

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

Мы собираемся рассчитать комиссию с продаж на основе чистого дохода.

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

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

Затем я умножаю Чистую прибыль на процент Комиссии в B4, 3%.

Возвращает комиссию.

Давайте посмотрим, как это работает в формуле.

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

Это возвращает чистую прибыль в размере 3200 долларов США.

Затем это умножается на 3%, возвращая комиссию в размере 96 долларов США.

Теперь мы собираемся определить взвешенные баллы для студентов.

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

Тесты 1 и 2, каждый из которых составляет 10%, а промежуточные и заключительные тесты, каждый из которых составляет 40%.

Для этого я умножаю результат теста 1 для Боба на его вес.

Я нажимаю F4 после выбора значения веса в ячейке B3, чтобы при копировании формулы вес для теста 1 всегда оставался в ячейке B3.

Обозначается как Абсолютная ссылка на ячейку .

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

Я выполняю те же шаги для Теста 2 и Финального теста.

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

Вот взвешенные баллы учеников.

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

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

Затем числа складываются вместе, в результате получается 74,5.

Какое значение возвращает эта формула? Во-первых, мы делаем самые внутренние круглые скобки, 5 минус 3 равно 2, в результате чего получается эта формула.

Затем мы делаем оставшиеся скобки, 10 умножить на 2 равно 20 и 1 минус 4 равно -3, в результате чего получается эта формула.

Теперь выполняем умножение и деление слева направо.

6 умножить на 20 будет 120, разделить на 3 будет 40, в результате получится эта формула.

Наконец, мы выполняем сложение и вычитание слева направо. 4 + 40 равно 44, -3 равно 41.

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

Функции и ссылки на ячейки оцениваются и обрабатываются как их результирующие числа.

Excel сложные формулы: Создание сложных формул в Microsoft Excel