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

Порядок выполнения действий в формулах Excel

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

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

  Формулы вычисляют значения в определенном порядке. Формула в Excel всегда начинается со знака равно (=). Excel интерпретирует символы после знака равно как формулу. После знака равно вычисляются элементы (операнды), например константы или ссылки на ячейки. Они разделены операторами вычислений. Excel вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.

• Приоритет операторов в формулах Excel

  Если в одной формуле используется несколько операторов, Microsoft Excel выполняет операции в порядке, указанном в приведенной ниже таблице. Если формула содержит операторы с одинаковым приоритетом ( например, если формула содержит операторы умножения и деления), Excel оценивает операторы слева направо.

  Оператор	Описание
  : (двоеточие) (один пробел) , (запятая)	Операторы ссылок
  –	Знак «минус»
  %	Процент
  ^	Возведение в степень
  * и /	Умножение и деление
  + и —	Сложение и вычитание
  &	Объединение двух текстовых строк в одну
  = < > <= >= <>	Операторы сравнения

• org/ListItem»>

  Использование скобок в Excel формулах

  Чтобы изменить порядок выполнения формулы, заключите ее часть, которая должна быть выполнена первой, в скобки. Например, результатом приведенной ниже формулы будет число 11, поскольку в Microsoft Excel умножение выполняется раньше сложения. В данной формуле число 2 умножается на 3, а затем к результату добавляется число 5.

  =5+2*3

  Если же с помощью скобок изменить синтаксис, Microsoft Excel сложит 5 и 2, а затем умножит результат на 3; результатом этих действий будет число 21.

  =(5+2)*3

  В приведенном ниже примере скобки, в которые заключена первая часть формулы, задают следующий порядок вычислений: определяется значение B4+25, после чего полученный результат делится на сумму значений в ячейках D5, E5 и F5.

  =(B4+25)/СУММ(D5:F5)

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

Excel для Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

• Синтаксис функции Excel

  В приведенном ниже примере функции ОКРУГЛ , округленной на число в ячейке A10, показан синтаксис функции.

  1. Structure. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающую круглую скобку, аргументы функции, разделенные запятыми, и закрывающая круглая скобка.

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

  3. аргументы. Аргументы могут быть числами, текстом, логическими значениями, такими как

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

  4. всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.

  Примечание: Вам не нужно вводить функции во все прописные буквы, например = «ОКРУГЛИТЬ», так как Excel автоматически заполнит ввод имени функции после нажатия кнопки «Добавить». Если вы неправильно наводите имя функции, например = СУМА (a1: A10), а не = сумм (a1: A10), Excel вернет #NAME? Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

• Ввод функций Excel

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

  Для упрощения создания и редактирования формул, а также для минимизации ошибок ввода и синтаксиса используйте Автозаполнение формул.

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

• Вложение функций Excel

  В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в следующей формуле используется вложенная функция СРЗНАЧ , а результат сравнивается со значением 50.

  1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

  Допустимые типы вычисляемых значений

      Вложенная функция, используемая в качестве аргумента, должна возвращать соответствующий ему тип данных. Например, если аргумент должен быть логическим, т. е. иметь значение ИСТИНА либо ЛОЖЬ, вложенная функция также должна возвращать логическое значение (ИСТИНА или ЛОЖЬ). В противном случае Excel выдаст ошибку «#ЗНАЧ!».

  <c0>Предельное количество уровней вложенности функций</c0>.    В формулах можно использовать до семи уровней вложенных функций. Если функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функция СРЗНАЧ и функция сумм являются функциями второго уровня, если они используются в качестве аргументов функции если. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня, и т. д.

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

1. Формула ПОИСКА в Excel
2. Расширенные формулы в Excel
3. Формула TRIM в Excel
4. Текстовая формула в Excel

Надежный подход к написанию сложных формул Excel

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

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

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

Формулы Excel

Еще Напугай меня, но я сделал это (ты тоже можешь!)

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

Нажмите, чтобы увеличить изображение

Прежде чем вы сдадитесь, позвольте мне рассказать вам об одном приеме, который я впервые увидел у Билла Джелена (он же Мистер Эксель), который значительно упрощает написание формул — даже таких сложных формул, как эта.

Если вы поместите передо мной это отвратительное чудовище без контекста и попросите меня шаг за шагом объяснить, что означает каждая часть этой формулы, я, вероятно, не смогу этого сделать. Не потому, что я скопировал его из Интернета. Я могу честно сказать, что я не получил никакой помощи с ним вообще. И я специально выбрал что-то эзотерическое, чтобы у меня не было соблазна поискать его и проверить себя, чтобы увидеть, сработает ли этот подход для действительно продвинутой формулы. Так оно и было. Как магия.

Итак, хватит болтать. Позвольте мне показать вам это в действии. Но сначала давайте рассмотрим некоторую терминологию. И не волнуйтесь; мы начнем с гораздо более простой задачи, чем это вуду данных.

Определения

Формула : Формулы предоставляют Excel некие инструкции для вычисления чего-либо. Они всегда начинаются со знака равенства. Оно может быть простым, как =2+2, или невероятно сложным, как показано выше.

Функция : Проще говоря, функции — это формулы, предварительно упакованные в Excel. Я имею в виду, что вы можете создавать свои собственные функции, но большую часть времени вы будете использовать одну из 80 баджиллионов, изначально предлагаемых в Excel (если, конечно, вам не нужна разница в процентах, которую Excel не предлагает… пойди разберись) . С более сложными формулами, которые вы будете писать, вы будете использовать несколько функций в одной формуле.

Аргумент : Каждая функция начинается с имени функции, за которым следует набор круглых скобок, например, =СУММ(A3:A67) или =СЦЕПИТЬ(B2,C2,D2). Отдельные элементы внутри этих круглых скобок, разделенные запятыми, являются аргументами.

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

Boolean : Возвращает значение TRUE или FALSE.

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

Загрузить

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

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

Наконец, я просто вычел 1 в конце, и все заработало, как и планировалось. В конце концов, это важно, поэтому я пошел с ним.

Задача 1. Извлечение домена из URL-адресов

Допустим, у вас есть список URL-адресов (возможно, обратных ссылок), и вы хотите извлечь только домен. Недавно я сделал это, чтобы использовать этот столбец в сводной таблице для группировки всех целевых URL-адресов, на которые были ссылки из определенного домена.

Например, предположим, что www.linkingsite.com связан с www.mysite.com/landing-page-02 и www.mysite.com/landing-page-45. Две целевые страницы будут отображаться под доменом www.linkingsite.com в моей сводной таблице, как я ее организовал.

Щелкните, чтобы увеличить изображение

Итак, как я их извлек? Легкий. Но сначала немного предыстории.

Важная информация, которую необходимо понять

Когда вам нужно извлечь строку текста из более длинной строки в Excel, вы должны использовать три основные функции: ВЛЕВО, ВПРАВО и СРЕДНЯЯ. Вы можете повозиться с ЗАМЕНОЙ, которая очень похожа на функцию «Найти и заменить» в Excel или Word, но мы не будем вдаваться в подробности.

Таким образом, каждая из этих функций запрашивает у вас:

1. Из какой ячейки вы хотите извлечь текст
2. Сколько символов вы хотите извлечь

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

Это все хорошо, но с нашим столбцом URL-адресов количество символов, которое нам нужно, чтобы извлечь изменения из URL-адреса в URL-адрес, поэтому мы не можем использовать статическое число. Вот тут-то и приходит на помощь функция ПОИСК.

Вкратце: Если вы много баловались формулами, вы можете спросить, почему я не использую функцию НАЙТИ. Что ж, функции ПОИСК и НАЙТИ очень похожи, только функция НАЙТИ более ограничивающая, поскольку она чувствительна к регистру и не поддерживает подстановочные знаки. Хромой. Таким образом, только раз, когда я использую ПОИСК, когда я хочу указать регистр, который в последний раз, когда я делал это, был последним… Арендатор здесь… Да, никогда.

Итак, что мы собираемся сделать, так это использовать функцию ПОИСК, чтобы указать количество необходимых нам символов, потому что она возвращает позицию того, что вы просите Excel найти. Если он не находит то, что вы искали, он возвращает неприятную ошибку #N/A, которую мы фактически ИСПОЛЬЗУЕМ в следующем примере. Однако для нашей задачи нам нужна позиция первой косой черты после домена.

Бууут, есть проблема.

Видите эти две косые черты перед доменом (http: // )? Да, они собираются сбить нас с толку. Но не беспокойтесь. Функция SEARCH имеет необязательный аргумент для начального числа (start_num), который мы будем использовать в наших интересах.

Playas

Итак, для нашей формулы мы собираемся объединить две функции: ВЛЕВО и ПОИСК. Вот синтаксис для каждого:

 LEFT(текст, [число_символов]) 

 SEARCH(find_text,within_text,[start_num]) 

Итак, давайте попробуем…

Стратегия

Итак, секретный соус в том, что мы собираемся разделить формулу на два шага. Затем, когда мы закончим, мы собираемся объединить их в одну формулу.

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

find_text: «/»
Это говорит о том, что мы ищем косую черту.

inside_text: B3
Это первая ячейка, из которой мы собираемся извлечь домен. Когда мы закончим, мы наведем указатель мыши на правый нижний угол ячейки, чтобы перетащить формулу вниз по столбцу.

start_num: 9
Нам нужно просто выбрать номер, который стоит после последнего/до домена. Мы могли бы использовать здесь 8, но если какой-либо из URL-адресов является безопасным, вам понадобится еще один символ. Убедитесь сами.

https://* <– * находится в позиции 9 ^th , где мы хотим начать поиск /, следующего за доменом.

Итоговая формула выглядит так:

Щелкните для увеличения.

 

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

Вот аргументы, которые мы будем использовать для функции LEFT:

text: B3

[num_chars]: C3

Таким образом, окончательная формула выглядит так:

Нажмите, чтобы увеличить изображение.

Теперь все, что нам нужно сделать, это объединить их так, чтобы вместо ссылки на ячейку C3 мы внедрили функцию ПОИСК в окончательную формулу. Для этого просто нажмите клавишу Esc, чтобы выйти из ячейки, в которой вы находитесь, затем скопируйте функцию ПОИСК в буфер обмена (но не знак =).

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

Нажмите, чтобы увеличить изображение.

Задача 2: сравнение двух наборов данных

В моем аудите Google Doc одна из задач, которую я включил, — это проверка на наличие потерянных страниц на сайте (на вкладке «Ссылки»). Один из способов проверить их — сравнить карту сайта вашего сайта со сканированием сайта, которое можно выполнить с помощью такого инструмента, как Screaming Frog. (Раскрытие информации: я не связан с Screaming Frog каким-либо образом.)

Если вы обнаружите, что в вашей карте сайта есть URL-адреса, которые не были найдены при сканировании Screaming Frog (или любом другом инструменте, который вы используете), есть хороший шанс, что это осиротевшие или заблокированные страницы. В любом случае, они требуют расследования и, вероятно, не должны быть в вашей карте сайта.

Playas

Для формулы мы будем использовать следующие функции:

ЕСЛИ: ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

ISNA: ISNA(значение)

MATCHok: MATCHok: , lookup_array, [match_type])

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

Поскольку эта формула требует еще нескольких шагов, я разобью их на шаги.

Шаг 1: Посмотрите, находится ли URL-адрес в C2 где-нибудь в столбце B.

Функция ПОИСКПОЗ — это еще одна функция поиска, предлагаемая Excel, и она особенно универсальна. Но он возвращает номер строки того, что вы ищете. Если он не может его найти, он возвращает ошибку #N/A (которую мы будем использовать).

Нажмите, чтобы увеличить изображение.

Шаг 2: ISNA просто возвращает логическое значение, которое позволяет узнать, является ли значение в ячейке, на которую вы ссылаетесь, ошибкой #Н/Д.

Шаг 3: Напишите оператор IF, который присваивает значение «Сиротский», если URL-адрес не находится в столбце Screaming Frog (другими словами, возвращает ошибку #N/A), и «Найдено», если нашлось.

Шаг 4: Замените каждую ссылку на вспомогательную ячейку формулой внутри этой ячейки (все, кроме =). Если вы распределите свои вспомогательные ячейки слева направо по электронной таблице, как я, начните с крайнего левого и продвигайтесь вправо.

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

Ресурсы

Полное руководство по Excel для маркетологов — это руководство Distilled по Microsoft Excel для SEO-специалистов. Это отличная отправная точка, если вы хотите начать свое путешествие, сосредоточившись на функциях, которые маркетологи используют чаще всего.