Как писать сложные формулы в Excel
Обновлено: 03.07.2024
В жизни маркетолога наступает момент, когда создание красивых диаграмм с заранее заданным набором данных просто не подходит. И поиск суммы и среднего значения столбца данных вас больше не удовлетворяет. В конце концов — и это действительно неизбежно — вам действительно придется глубоко погрузиться в данные и отсеять […]
В жизни маркетолога наступает момент, когда создание красивых диаграмм с заранее заданным набором данных становится неэффективным. И поиск суммы и среднего значения столбца данных вас больше не удовлетворяет.
В конце концов — и это действительно неизбежно — вам действительно придется глубоко погрузиться в данные и отобрать меньший набор данных или каким-то образом манипулировать им, чтобы он выдал то, что вам нужно. И, какими бы пугающими они ни были, в такие моменты формулы становятся вашим спасательным кругом.
Следует признать, что кривая обучения работе с формулами может быть довольно крутой, но единственный способ сделать их интуитивно понятными — это познакомиться с ними и попрактиковаться.
Формулы Excel Все еще Меня пугают, но я сделал это (вы тоже можете!)
Однако, когда я впервые изучал их, я видел формулу, подобную приведенной ниже, которую я написал, чтобы найти последний каталог в URL-адресе (просто чтобы посмотреть, смогу ли я, а не для какого-либо конкретного использования), и думаю, что я никогда не смогу сделать ничего подобного. Оказывается, я ошибался.
Нажмите, чтобы увеличить изображение
Прежде чем вы сдадитесь, позвольте мне рассказать вам об одном приеме, который я впервые увидел у Билла Джелена (также известного как Мистер Эксель), который значительно упрощает написание формул, даже таких сложных формул, как эта.
Если вы поместите передо мной это отвратительное чудовище без контекста и попросите меня шаг за шагом объяснить, что означает каждая часть этой формулы, я, вероятно, не смогу этого сделать. Не потому, что я скопировал его из Интернета. Я могу честно сказать, что я не получил никакой помощи с ним вообще. И я специально выбрал что-то эзотерическое, чтобы у меня не было соблазна поискать его и проверить себя, чтобы увидеть, сработает ли этот подход для действительно продвинутой формулы. Так оно и было. Как амулет.
Итак, хватит болтать. Позвольте мне показать вам это в действии. Но сначала давайте рассмотрим некоторую терминологию. И не волнуйтесь; мы начнем с гораздо более простой задачи, чем это вуду данных.
Определения
Формула. Формулы предоставляют Excel некие инструкции для вычисления чего-либо. Они всегда начинаются со знака равенства. Оно может быть простым, как =2+2, или невероятно сложным, как показано выше.
Функция. Проще говоря, функции — это формулы, предварительно упакованные в Excel. Я имею в виду, что вы можете создавать свои собственные функции, но большую часть времени вы будете использовать одну из 80 баджиллионов, изначально предлагаемых в Excel (если, конечно, вам не нужна разница в процентах, которую Excel не предлагает… пойди разберись) . В более сложных формулах вы будете использовать несколько функций в одной формуле.
Аргумент. Каждая функция начинается с имени функции, за которым следует набор круглых скобок, например, =СУММ(A3:A67) или =СЦЕПИТЬ(B2,C2,D2). Отдельные элементы внутри круглых скобок, разделенные запятыми, являются аргументами.
Квадратные скобки. Иногда в подсказке или на сайте Microsoft можно увидеть аргументы, заключенные в квадратные скобки. Скобки указывают, что аргумент является необязательным.
Логическое значение: возвращает значение ИСТИНА или ЛОЖЬ.
Вспомогательные клетки: Хорошо, это не настоящий термин; это то, что я называю ячейками, которые вы используете, чтобы разбить сложную формулу на простые для понимания шаги.
Скачать
Если вы хотите загрузить книгу Excel, с которой я работал в следующих примерах, вы можете получить к ней доступ здесь. Я даже включил сумасшедшую формулу, которую вы видите выше. Вы увидите, что на одной вкладке я разбиваю процесс на простые шаги (именно это я и сделал в первую очередь), а затем шаг за шагом со всеми вспомогательными ячейками.
Затем на последней вкладке я заменяю все вспомогательные ячейки содержащимися в них формулами и продолжаю подметать, пока не доберусь до окончательной формулы. Но по какой-то причине я продолжал придумывать одного лишнего персонажа, который мне не был нужен. Я несколько раз проверял свои формулы и пытался повторить шаги.
Наконец, я просто вычел 1 в конце, и все заработало, как и планировалось. В конце концов, это важно, поэтому я согласился.
Задача 1. Извлечение домена из URL-адресов
Допустим, у вас есть список URL-адресов (возможно, обратных ссылок) и вы хотите извлечь только домен. Недавно я сделал это, чтобы использовать этот столбец в сводной таблице для группировки всех целевых URL-адресов, на которые были ссылки из определенного домена.
Нажмите, чтобы увеличить изображение
Итак, как я их извлек? Легкий. Но сначала немного предыстории.
Важные вещи, которые вам нужно понять
Когда вам нужно извлечь строку текста из более длинной строки в Excel, вы должны использовать три основные функции: ВЛЕВО, ВПРАВО и СРЕДНЯЯ. Вы можете повозиться с ЗАМЕНОЙ, которая во многом похожа на функцию «Найти и заменить» в Excel или Word, но мы не будем вдаваться в подробности здесь.
По сути, каждая из этих функций спрашивает вас:
- Из какой ячейки вы хотите извлечь текст
- Сколько символов вы хотите извлечь
С помощью функции MID вы также указываете начальное место, потому что вы тянете из середины строки.
Все это хорошо, но с нашим столбцом URL количество символов, которое нам нужно, чтобы извлечь изменения из URL в URL, поэтому мы не можем использовать статическое число. Вот где на помощь приходит функция ПОИСК.
Небольшое отступление: если вы много возились с формулами, вы можете спросить, почему я не использую функцию НАЙТИ. Что ж, функции ПОИСК и НАЙТИ очень похожи, только функция НАЙТИ более ограничивающая, поскольку она чувствительна к регистру и не поддерживает подстановочные знаки. Хромой. Таким образом, единственный раз, когда я использую FIND, это когда я хочу указать регистр, который в последний раз, когда я делал это, был последним… Арендатор здесь… Да, никогда.
Нууут, возникла проблема.
Плаяс
Итак, для нашей формулы мы собираемся объединить две функции: ВЛЕВО и ПОИСК. Вот синтаксис для каждого:
Хорошо, так что давайте поппин здесь…
Стратегия
Итак, секретный соус заключается в том, что мы собираемся разделить формулу на два этапа. Затем, когда мы закончим, мы объединим их в одну формулу.
Excel помогает вам на каждом этапе пути, выделяя аргумент, над которым вы работаете в данный момент. Вот что мы собираемся использовать для каждого из них:
find_text: «/»
Это говорит о том, что мы ищем косую черту.
within_text: B3
Это первая ячейка, из которой мы собираемся извлечь домен. Когда мы закончим, наведите указатель мыши на правый нижний угол ячейки, чтобы перетащить формулу вниз по столбцу.
start_num: 9
Нам нужно просто выбрать номер, который стоит после последнего / перед доменом. Мы могли бы использовать здесь 8, но если какой-либо из URL-адресов является безопасным, вам понадобится еще один символ. Убедитесь в этом сами.
Итак, окончательная формула выглядит так:
Нажмите, чтобы увеличить изображение.
Это говорит нам о том, в какой позиции находится каждый из символов косой черты, следующих непосредственно за доменом. И мы будем использовать это, чтобы предоставить количество символов для извлечения в функции LEFT.
Вот аргументы, которые мы будем использовать для функции ВЛЕВО:
Итак, окончательная формула выглядит так:
Нажмите, чтобы увеличить изображение.
Теперь все, что нам нужно сделать, это объединить их так, чтобы вместо ссылки на ячейку C3 мы внедрили функцию ПОИСК в окончательную формулу. Для этого просто нажмите клавишу Esc, чтобы выйти из ячейки, в которой вы находитесь, а затем скопируйте функцию ПОИСК в буфер обмена (но не знак =).
Затем снова нажмите клавишу Esc, чтобы выйти из этой ячейки, и вернитесь к ячейке, содержащей окончательную формулу, и замените ссылку C3 на формулу, которую вы только что скопировали из C3. Теперь ваша формула должна выглядеть так:
Нажмите, чтобы увеличить изображение.
Задача 2. Сравните два набора данных
Если вы обнаружите, что в вашей карте сайта есть URL-адреса, которые не были найдены при сканировании Screaming Frog (или любом другом инструменте, который вы используете), есть большая вероятность, что это потерянные или заблокированные страницы. В любом случае, они требуют расследования и, вероятно, не должны быть в вашей карте сайта.
Игры
Для формулы мы будем использовать следующие функции:
ЕСЛИ: ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])
ПОИСКПОЗ: ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления])
Ради экономии времени я не буду вдаваться во все подробности того, что они означают. Каждая из ссылок ведет на страницу сайта Microsoft, на которой объясняется все об этой конкретной функции.
Поскольку эта формула требует еще нескольких шагов, я разобью их на шаги.
Шаг 1. Посмотрите, находится ли URL в C2 где-нибудь в столбце B.
Нажмите, чтобы увеличить изображение.
Шаг 4. Замените каждую ссылку на вспомогательную ячейку формулой внутри этой ячейки (все, кроме знака =). Если вы распределите вспомогательные ячейки слева направо по электронной таблице, как я, начните с крайнего левого края и двигайтесь вправо.
Если вы распределите их по вертикали, начните сверху и двигайтесь вниз к последней написанной вами формуле. Таким образом, вы случайно не пропустите вспомогательные ячейки.
Ресурсы
Полное руководство по Excel для маркетологов – это руководство Distilled по Microsoft Excel для SEO-специалистов. Это отличная отправная точка, если вы хотите начать свой путь, сосредоточившись на функциях, которые маркетологи используют чаще всего.
Если вы действительно хотите стать хардкорщиком, я настоятельно рекомендую канал Билла Джелена на YouTube. Его плейлисты особенно полезны, если вы хотите сконцентрироваться на определенной слабой области. Еще один подкаст, который мне очень нравится, — это канал Майка Гирвина ExcelIsFun на YouTube.
Я также регулярно публикую видеоруководства по Excel в своем блоге.
Заключительные мысли
Если вы не поняли всех нюансов двух формул, с которыми мы работали, не беспокойтесь об этом. Просто усвойте принцип использования клеток-помощников, чтобы проверить свои функции на этом пути и разбить процесс на легко усваиваемые этапы. Раньше я пытался запихнуть все в одну ячейку и запутался в формулах, даже с подсказками.
Теперь мне гораздо удобнее ориентироваться в довольно сложных формулах, но когда они становятся слишком запутанными, я начинаю разбирать их по частям в их собственных ячейках.
Мнения, высказанные в этой статье, принадлежат приглашенному автору и не обязательно принадлежат Search Engine Land. Здесь перечислены штатные авторы.
поиск меню
Урок 3. Сложные формулы
Введение
Простая формула – это математическое выражение с одним оператором, например 7+9. Сложная формула содержит более одного математического оператора, например 5+2*8. Когда в формуле есть более одной операции, порядок операций указывает электронной таблице, какую операцию следует вычислить первой. Чтобы использовать сложные формулы, вам необходимо понимать порядок операций.
Необязательно: загрузите файл примера для этого урока.
Посмотрите видео ниже, чтобы узнать больше о сложных формулах.
Порядок операций
Все программы для работы с электронными таблицами вычисляют формулы на основе следующего порядка операций:
Мнемоника, которая поможет вам запомнить заказ, – это PEMDAS, или "Пожалуйста, извините мою дорогую тетю Салли".
Нажимайте стрелки в слайд-шоу ниже, чтобы узнать больше о том, как порядок операций используется для вычисления сложных формул.
Хотя эта формула может показаться очень сложной, мы можем использовать пошаговый порядок операций, чтобы найти правильный ответ.
Сначала мы начнем с вычисления всего, что заключено в скобки. В этом случае нам нужно вычислить только одно: 6-3=3.
Как видите, формула уже выглядит немного проще. Далее мы посмотрим, есть ли экспоненты. Есть один: 2^2=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.
К любому уравнению можно добавить круглые скобки, чтобы его было легче читать. Хотя это не изменит результат формулы в этом примере, мы могли бы заключить операции умножения в круглые скобки, чтобы уточнить, что они будут вычисляться перед сложением.
В вашей электронной таблице не всегда будет указано, содержит ли ваша формула ошибку, поэтому вы должны проверить все свои формулы. Чтобы узнать, как это сделать, ознакомьтесь с уроком "Перепроверьте свои формулы".
Excel содержит множество полезных формул; у нас есть категория «Финансы», «Логика», «Текст», «Дата и время», «Поиск и ссылки» и «Математика и триггер». Каждая отдельная формула достаточно хороша для пользователей среднего уровня, когда они сталкиваются с прямыми ситуациями, но по мере того, как вы переходите на следующий уровень, ваши навыки должны повышаться, поэтому в этом отношении применение более сложных формул в Excel играет жизненно важную роль в решении сложные ситуации. Помня об этом, в этой статье мы познакомим вас с некоторыми важными и столь необходимыми сложными формулами. Читайте дальше.
Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.
Выбрать функцию для поиска
Не многие из вас использовали функцию ВЫБОР, но она также очень полезна в качестве функции поиска.
У нас есть данные о клиентах за месяц в этой таблице; у нас есть каждый клиентский стол. В этой таблице в ячейке B13 я создал раскрывающийся список месяца, чтобы при изменении этого раскрывающегося списка мы получали итоги за выбранный месяц.
Создайте одну таблицу, как показано ниже.
Выберите номер ИНДЕКС в качестве ячейки ВПР и сделайте его абсолютной ссылкой.
Теперь выберите для каждого аргумента количество продаж за каждый месяц.
Закройте скобку и нажмите клавишу ввода.
Теперь также скопируйте и вставьте формулу в ячейки ниже.
Теперь в формуле ВЫБОР будут отображаться номера выбранного месяца.
ВПР с функцией ВЫБОР
Мы видели традиционную функцию ВПР, но мы также можем совместить ее с функцией ВЫБОР. Например, посмотрите на данные ниже.
Excel Advanced Training (16 курсов, более 23 проектов) 16 онлайн-курсов | 23 практических проекта | 140+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,8 (10 130 оценок)
В ячейках с A1 по B11 у нас есть таблица продаж, а в ячейке D2 я создал раскрывающийся список месяцев, а в ячейке E2 нам нужно применить функцию ВПР и получить значение продаж за выбранный месяц. Но проблема с применением ВПР заключается в структуре данных, где искомое значение находится справа от столбца результатов, поэтому ВПР не может получить данные справа налево; в этом случае мы можем использовать функцию ВЫБОР.
Чтобы выбрать массив таблиц, нам нужно открыть здесь функцию ВЫБОР.
В аргументе "Число ИНДЕКС" в поле "Цветок" скобки вводят 1 и 2. Теперь выберите первый диапазон в качестве столбца "Месяц".
Выберите столбец "Продажи" в качестве второго диапазона.
Укажите индекс столбца как 2 и диапазон поиска как 0.
Итак, мы получили результат за апрель из таблицы. Таким образом, если у вас нет точной структуры данных, мы можем выбрать функцию ВЫБОР.
ЕСЛИ с функцией И и ИЛИ
Логические функции полны логики для вычислений. Взгляните на приведенный ниже пример данных.
Из приведенной выше таблицы нам нужно рассчитать бонус на основе следующих условий.
- Если Услуга предоставляется более 1,5 лет и если отдел продаж или поддержки, то Бонус составляет 1000.
- Если что-то еще, то Бонус 3000.
Чтобы рассчитать бонус, нам нужно применить операторы IF, AND и OR вместе. Откройте условие ЕСЛИ в ячейке D2.
Нам нужно проверить несколько условий, открыть функцию AND.
Теперь следующим логическим решением является либо Продажа, либо Поддержка, поэтому для этого открытого условия ИЛИ.
Проверьте, является ли заголовок "Продажи" или "Поддержка".
Итак, если все поставленные условия ИСТИННЫ, то бонус должен быть 10000.
Если предоставленные логические тесты ложны, то бонус равен 3000.
Закройте скобку и нажмите клавишу Enter; мы должны получить результат.
Скопируйте и вставьте формулу в ячейки ниже.
Итак, по условию мы получили результат. Если сервису больше 1,5 года и его название — «Продажи» или «Поддержка», мы получим 10 000 или 3 000.
Сложная формула с подстановочными знаками
ВПР требует совпадения точного значения поиска для извлечения данных. Это традиционный слоган, но мы по-прежнему можем получать данные, используя значение поиска частиц. Например, если искомое значение — «VIVO» и находится в основной таблице, мы все равно можем сопоставить с помощью подстановочных знаков, если это «VIVO Mobile». Сейчас мы увидим один из примеров; ниже пример данных.
У нас есть таблица поиска в столбце A. В столбце C у нас есть значения поиска; эти значения поиска не совсем совпадают со значениями таблицы поиска. Итак, мы увидим, как применять функцию ВПР с использованием подстановочных знаков.
Сначала откройте функцию ВПР в ячейке D1.
Первый аргумент — это значение поиска. Одна из проблем с искомым значением здесь заключается в том, что у нас нет точного совпадения, поэтому нам нужно заключить это искомое значение со звездочкой до и после искомого значения.
Здесь мы применили две звездочки: «*»&C2&»*». Здесь звездочка указывает, что все, что находится между подстановочными знаками, должно сопоставляться и возвращать соответствующий результат.
Несмотря на то, что у нас было просто «Infosys», символ звездочки соответствовал значению в таблице поиска и возвращал точный результат как «Infosys Ltd».
Рекомендуемые статьи
Это руководство по сложным формулам в Excel. Здесь мы обсудим некоторые из важных и столь необходимых сложных формул с примерами и шаблоном Excel. Вы также можете просмотреть другие предлагаемые нами статьи, чтобы узнать больше –
Давайте составим формулу для расчета учащегося с лучшим средним баллом из таблицы, подобной этой:
Наши данные находятся в столбцах C и D в диапазоне C6:D45.
- Мы можем легко найти имя учащегося со средним баллом, скажем, 3,53, используя формулы поиска, такие как XLOOKUP.
- Мы также можем рассчитать максимальный (лучший) средний балл по формуле MAX.
Когда вы объединяете идеи 1 и 2, точно так же, как вы объединяете LEGO, вы создаете третью формулу, которая получает имя учащегося с самым высоким средним баллом.
Поэтому окончательная формула будет такой:
А теперь вы можете танцевать счастливый танец.
Как насчет второго по величине среднего балла студента?
Мы можем расширить эту идею, чтобы получить имя учащегося со вторым по величине средним баллом. Вместо MAX нам нужно использовать функцию НАИБОЛЬШИЙ. Вот так:
В приведенной выше формуле НАИБОЛЬШИЙ(…, 2) возвращает второй по величине средний средний балл.
Вы можете использовать ту же концепцию, чтобы получить третье по величине или второе по величине значение (используйте SMALL(…,2)).
Способ построения сложных формул LEGO.
Точно так же, как в LEGO есть несколько основных строительных блоков (ну, в наши дни вы можете найти странный блок в форме глазного яблока дракона, который поместится в вашем наборе о Гарри Поттере, но вы поняли мою мысль), мы также можем построить сложный формула из нескольких основных ингредиентов.
Запомните эти советы по строительству…
- Разбейте сложную проблему на небольшие управляемые фрагменты
- Запишите формулы для этих фрагментов.
- Если вам нужно повторить какую-то логику, рассмотрите возможность использования вспомогательных ячеек или новой функции LET().
- Используйте возможности ссылок Excel (абсолютные – $A$1, относительные – A1, смешанные – A$1, $A1 и, наконец, структурные (таблица[столбец]), чтобы писать более короткие и простые формулы
- Попробуйте новые формулы, такие как ФИЛЬТР, УНИКАЛЬНОСТЬ, СОРТИРОВКА, ЛЯМБДА и т. д., чтобы легко получать результаты.
- Уделите время изучению новых методов, таких как диапазоны разброса, лямбда-выражения, формулы на основе типов данных.
- Когда у вас будет время, попробуйте найти другую формулу для решения проблемы
5 примеров построения сложных формул
Если вам это интересно, то обязательно посмотрите мое видео. Он показывает, как писать расширенные формулы в Excel. Смотрите ниже или на моем канале YouTube.
Читайте также: