Сложные формулы excel: Сложные формулы и простые решения в Excel

Содержание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Использование формул или функций для обработки данных|Documentation

Contents

[

Hide
]

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

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

Aspose.Cells предоставляет класс,Рабочая тетрадь , представляющий файл Excel Microsoft.Рабочая тетрадь класс содержитРабочие листы коллекция, которая обеспечивает доступ к каждому рабочему листу в файле Excel. Рабочий лист представленРабочий лист учебный класс.Рабочий лист класс предоставляетCells коллекция. Каждый элемент вCells коллекция представляет собой объектCellучебный класс.

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

Использование встроенных функций.
Использование дополнительных функций.
Работа с формулами массива.
Создание формулы R1C1.

Использование встроенных функций

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

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

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

 = H7*(1+IF(P7 = $L$3,$M$3, (IF(P7=$L$4,$M$4,0))))

, также поддерживаются в Aspose. Cells. При применении формулы к ячейке всегда начинайте строку со знака равенства (=), как при создании формулы в Microsoft Excel, и используйте запятую (,) для разделения параметров функции.

В приведенном ниже примере сложная формула применяется к первой ячейке рабочего листа.Cells коллекция. В формуле используется встроенныйЕСЛИ Функция предоставлена Aspose.Cells.

Использование дополнительных функций

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

Aspose.Cells предоставляет возможности для регистрации дополнительных функций с использованиемРабочие листы.RegisterAddInFunction(). После этого, когда мы установилиCell.Formula anyFunctionFromAddIn, выходной файл Excel содержит вычисленное значение из функции AddIn.

После этого необходимо загрузить файл XLAM для регистрации функции надстройки в приведенном ниже образце кода. Точно так же можно загрузить выходной файл «test_udf.xlsx» для проверки вывода.

TestUDF.xlam

test_udf.xlsx

Использование формулы массива

Формулы массива — это формулы, которые работают с массивами, а не с отдельными числами, в качестве аргументов функций, составляющих формулу. Когда отображается формула массива, она заключена в фигурные скобки ({}), как показано ниже.

Установка формулы массива в ячейке G2

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

Формулу массива можно применить к ячейке, вызвав методCell учебный класс’setArrayFormula метод.setArrayFormula принимает следующие параметры:

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

Использование формулы R1C1

ПрименитьR1C1 формула стиля ссылки на ячейку сCell учебный класс’setR1C1Формулаимущество.

сложных формул в 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 мы получили значение ошибки как #ЗНАЧ! Потому что в таблице поиска нет слова «Минтра».

Видео: расширенные формулы и ссылки

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

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

Теперь мы можем добавить данные.

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

Мы могли бы отформатировать данные в виде таблицы, но давайте оставим их как есть для этой демонстрации.

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

Выберите ячейку вне данных и введите .1.

Затем подойдите сюда к группе Number и нажмите кнопку % , чтобы отформатировать ячейку в процентах.

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

Теперь мы можем ввести формулу.

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

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

Что ж, совершенно очевидно, что эта сумма не составляет 10 процентов от первых двух столбцов. И причина этого не в том, что я не учел порядок расчета.

Это порядок, в котором Excel вычисляет части формулы.

Вам не нужно запоминать все детали.

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

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

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

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

В первой ячейке формула получает процент буфера от D20.

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

Но почему Excel изменил ссылку на эту ячейку на D21?

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

В первой ячейке формула ссылается на эти ячейки в строке 6 и D20. Когда вы используете AutoFill , Excel изменяет ссылку на ячейку в зависимости от местоположения на строку 7 и D21.

В большинстве случаев это то, что вам нужно.

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

В формуле щелкните D20, а затем введите знак доллара перед буквой D и цифрой 20.

Знак доллара указывает Excel, что формула всегда должна ссылаться на ячейку в столбце D в 20-й строке. Нажмите Ввод.

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

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

Выбрав G6, нажмите Автосумма .

Excel автоматически предполагает, что мы хотим добавить 3 столбца слева.

Итак, нажмите Enter. А потом AutoFill формулу в другие ячейки.

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

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

Сложные формулы excel: Сложные формулы и простые решения в Excel