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

Формулы EXCEL с примерами — Инструкция по применению

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

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

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

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

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

Содержание:

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

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

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

В программе можно вычислить целый комплекс показателей, в числе которых:

• максимальные и минимальные значения;
• средние показатели;
• проценты;
• критерий Стьюдента и многое другое.

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

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

При этом необходимость вводить дополнительные данные и параметры отпадает.

Как применять простые формулы в программе?

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

Для этого необходимо ввести в одну ячейку одно число, а во вторую – другое.

Например, В Ячейку А1 – число 5, а в ячейку В1 – 3. Для того чтобы в ячейке А3 появилось суммарное значение необходимо ввести формулу:

=СУММ(A1;B1).

Вычисление суммарного значения двух чисел

Определить сумму чисел 5 и 3 может каждый человек, но вводить число в ячейку С1 самостоятельно не нужно, так как в этом и замысел расчета формул.

После введения итог появляется автоматически.

При этом если выбрать ячейку С1, то в верхней строке видна формула расчета.

Если одно из значений изменить, то перерасчет происходит автоматически.

Например, при замене числа 5 в ячейке В1 на число 8, то менять формулу не нужно, программа сама просчитает окончательное значение.

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

Сумма дробных чисел

В Excel можно производить любые арифметические операции: вычитание «-», деление «/», умножение «*» или сложение «+».

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

Любая формула должна начинаться знаком «=».

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

к содержанию ↑

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

В приведенном примере формула =СУММ(A1;B1) позволяет определить сумму двух чисел в ячейках, которые расположены по горизонтали.

Формула начинается со знака «=». Далее задана функция СУММ. Она указывает, что необходимо произвести суммирование заданных значений.

В скобках числятся координаты ячеек. Выбирая ячейки, следует не забывать разделять их знаком «;».

Если нужно найти сумму трех чисел, то формула будет выглядеть следующим образом:

=СУММ(A1;B1;C1).

Формула суммы трех заданных чисел

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

Например,

=СУММ(A1:A10). На рисунке арифметическая операция будет выглядеть следующим образом:

Определение диапазона ячеек для формулы сложения

Также можно определить произведение этих чисел. В формуле вместо функции СУММ необходимо выбрать функцию ПРОИЗВЕД и задать диапазон ячеек.

Формула произведения десяти чисел

к содержанию ↑

Комбинированные формулы

Диапазон ячеек в программе указывается с помощью заданных координат первого и последнего значения. В формуле они разделяются знаком «:».

Кроме того, Excel имеет широкие возможности, поэтому функции здесь можно комбинировать любым способом.

Если нужно найти сумму трех чисел и умножить сумму на коэффициенты 1,4 или 1,5, исходя из того, меньше ли итог числа 90 или больше.

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

=ЕСЛИ(СУММ(А1:С1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

Решение задачи с помощью комбинированной формулы

В примере задействованы две функции – ЕСЛИ и СУММ. Первая обладает тремя аргументами:

• условие;
• верно;
• неверно.

В задаче – несколько условий.

Во-первых, сумма ячеек в диапазоне А1:С1 меньше 90.

В случае выполнения условия, что сумма диапазона ячеек будет составлять 88, то программа выполнит указанное действие во 2-ом аргументе функции «ЕСЛИ», а именно в СУММ(А1:С3)*1,4.

Если в данном случае происходит превышение числа 90, то программа вычислит третью функцию – СУММ(А1:С1)*1,5.

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

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

к содержанию ↑

Встроенные функции программы

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

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

Если выбрать в главном меню раздел «формулы», то здесь сосредоточены все известные функции, в том числе финансовые, инженерные, аналитические.

Для того чтобы выбрать, следует выбрать пункт «вставить функцию».

Выбор функции из предлагаемого списка

Эту же операцию можно произвести с помощью комбинации на клавиатуре — Shift+F3 (раньше мы писали о горячих клавишах Excel).

Если поставить курсор мышки на любую ячейку и нажать на пункт «выбрать функцию», то появляется мастер функций.

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

Мастер функций

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

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

Например, дана задача, найти функцию СУММЕСЛИМН. Для этого нужно зайти в категорию математических функций и там найти нужную.

Выбор функции и заполнение полей

Далее нужно заполнить поля чисел и выбрать условие. Таким же способом можно найти самые различные функции, в том числе «СУММЕСЛИ», «СЧЕТЕСЛИ».

к содержанию ↑

Функция ВПР

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

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

Вычисление ВПР можно проследить на примере, в котором приведен список из фамилий. Задача – по предложенному номеру найти фамилию.

Применение функции ВПР

Формула показывает, что первым аргументом функции является ячейка С1.

Второй аргумент А1:В10 – это диапазон, в котором осуществляется поиск.

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

Вычисление заданной фамилии с помощью функции ВПР

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

Если попробовать найти фамилию из несуществующего номера, то формула не выдаст ошибку, а даст правильный результат.

Поиск фамилии с пропущенными номерами

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

Он имеет только два значения – «ложь» или «истина». Если аргумент не задается, то он устанавливается по умолчанию в позиции «истина».

к содержанию ↑

Округление чисел с помощью функций

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

А полученное значение можно использовать при расчетах в других формулах.

Округление числа осуществляется с помощью формулы «ОКРУГЛВВЕРХ». Для этого нужно заполнить ячейку.

Первый аргумент – 76,375, а второй – 0.

Округление числа с помощью формулы

В данном случае округление числа произошло в большую сторону. Чтобы округлить значение в меньшую сторону, следует выбрать функцию «ОКРУГЛВНИЗ».

Округление происходит до целого числа. В нашем случае до 77 или 76.

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

Наиболее активно программу используют проектировщики, предприниматели, а также студенты.

Вся правда о формулах программы Microsoft Excel 2007

Формулы EXCEL с примерами — Инструкция по применению

как пользоваться, создавать и изменять

Формулы Excel используют, когда данных очень много. Например, чтобы посчитать сумму нескольких чисел быстрее, чем на калькуляторе. Преимуществ много, поэтому работодатели часто указывают эту программу в требованиях. В конце марта 2022 года 64 225 вакансий на хедхантере содержали формулировки вроде «уверенный пользователь Excel», «работа с формулами в Excel».

Кому важно знать Excel и где выучить основы

Excel нужен бухгалтерам, чтобы вести учет в таблицах. Экономистам, чтобы делать перерасчет цен, анализировать показатели компании. Менеджерам — вести базу клиентов. Аналитикам — строить и проверять гипотезы.

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

На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой. Кроме Excel вы изучите Metabase, SQL, Power BI, язык программирования Python.

Еще используют символы сравнения:

равенство =

меньше <

больше >

меньше либо равно <=

больше либо равно >=

не равно <>

Основные виды

Все формулы в 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)

1. Введите =ПСТР.
2. Кликните на ячейку, где нужно выделить знаки.
3. Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
4. Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 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

Встроенные

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

1. Поместите курсор в нужную ячейку.
2. Откройте диалоговое окно мастера: нажмите клавиши Shift + F3. Откроется список функций.
3. Выберите нужную формулу. Нажмите на нее, затем на «ОК». Откроется окно «Аргументы функций».
4. Внесите нужные данные. Например, числа, которые нужно сложить.

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

Как скопировать

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

1. Впишите функцию в ячейку и кликните на нее.
2. Наведите курсор на правый нижний угол — курсор примет форму креста.
3. Нажмите левую кнопку мыши, удерживайте ее и тяните до нужной ячейки.
4. Отпустите кнопку. Появится итог.

Посчитали сумму ячеек в трех строках

Как обозначить постоянную ячейку

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

1. Нажмите на ячейку с формулой.
2. Поместите курсор в нужную ячейку и нажмите F4.
3. В формуле фрагмент с описанием ячейки приобретет вид $A$1. Если вы протянете формулу, то ссылка на ячейку $A$1 останется на месте.

Как поставить «плюс», «равно» без формулы

Когда нужна не формула, а данные, например +10 °С:

1. Кликните правой кнопкой по ячейке.
2. Выберите «Формат ячеек».
3. Отметьте «Текстовый», нажмите «ОК».
4. Поставьте = или +, затем нужное число.
5. Нажмите Enter.

Вебинары

Главное о формулах в Excel

• Формула состоит из математических знаков. Чтобы ее вписать, используют символы = ( ) ; : .
• С помощью простых формул числа складывают, умножают, округляют, извлекают из них квадратный корень. Чтобы отредактировать текст, используют формулы поиска, изменения регистра, удаления лишних пробелов.
• Сложные и комбинированные формулы помогают делать объемные вычисления, когда нужно соблюдать несколько условий.

Формулы Excel с примерами

Легко ли выучить формулы Microsoft Excel? Ага! В этом руководстве объясняются самые основы формул Excel для начинающих с подробными инструкциями по их написанию и использованию. Он также предоставляет ряд расширенных примеров формул для опытных пользователей. Вы будете удивлены, насколько просто на самом деле создавать формулы в Excel.

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

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

• Формулы Microsoft Excel — основы
• Операторы, используемые в формулах электронных таблиц Excel
• Как создавать формулы в Excel
  • Создание простых формул Excel
  • Создание расширенных формул Excel
• Типы ссылок на ячейки (абсолютные, относительные, смешанные)
• Сочетания клавиш и подсказки для формул Excel
• Список формул Excel с примерами

Формулы Microsoft Excel — основы

Формулы в MS Excel — это уравнения, которые выполняют различные вычисления на рабочих листах. Хотя за прошедшие годы Microsoft представила несколько новых функций, концепция формул электронных таблиц Excel одинакова во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.

• Все формулы Excel начинаются со знака равенства (=).
• После символа равенства вы вводите либо вычисление , либо функцию . Например, чтобы сложить значения в ячейках с B1 по B5, вы можете:
  • Введите полное уравнение: =B1+B2+B3+B4+B5
  • Используйте функцию СУММ: =СУММ(B1:B5)
• Нажмите клавишу Enter, чтобы завершить формулу. Сделанный!

Элементы формул Microsoft Excel

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

• Константы — числа или текстовые значения, которые вы вводите непосредственно в формулу, например =2*3.
• Ссылки на ячейки — ссылка на ячейку, содержащую значение, которое вы хотите использовать в формуле Excel, например.

  =СУММ(A1, A2, B5) .

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

  .

  =СУММ(A1:A5) .

• Имена — определенное имя для диапазона ячеек, константы, таблицы или функции, например =СУММ(мое_имя) .
• Функции — предопределенные формулы в Excel, которые выполняют вычисления, используя значения, указанные в их аргументах.
• Операторы — специальные символы, указывающие тип выполняемой операции или вычисления.

Операторы в формулах рабочего листа Excel

Чтобы сообщить Microsoft Excel, какой тип операции вы хотите выполнить в формуле, вы используете специальные символы, которые технически называются операторами . В Excel существует 4 типа операторов:

• Арифметические — для выполнения основных математических операций.
• Сравнение (логическое) — для сравнения значений.
• Конкатенация — для объединения текстовых значений в одну строку.
• Справочник — для создания диапазонов и разделения аргументов в функциях Excel.

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

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

Оператор	Значение	Пример формулы
+ (плюс)	Дополнение	=А2+В2
— (знак минус)	Вычитание Отрицание (изменение знака)	=A2-B2 =-A2 (меняет знак значения в A2)
* (звездочка)	Умножение	=А2*В2
/ (косая черта)	Подразделение	=А2/В2
% (знак процента) 93 (возводит число в A2 в степень 3)

Например, если у вас есть цена товара в ячейке A2 и НДС в ячейке B2, вы можете рассчитать сумму НДС, используя следующую процентную формулу: =A2*B2

Операторы сравнения в формулах Excel

В формулах Microsoft Excel сравнение или логическое , операторы используются для сравнения двух значений. Результатом сравнения всегда является логическое значение ИСТИНА или ЛОЖЬ. В Excel доступны следующие логические операторы:

Оператор сравнения	Значение	Пример формулы
=	равно	=А2=В2
<>	Не равно	=А2<>В2
>	Больше	=А2>В2
<	Менее	=А2<В2
>=	Больше или равно	=А2>=В2
<=	Меньше или равно	=А2<=В2

Например, формула =A1=B1 возвращает значение TRUE, если ячейки A1 и B1 содержат одно и то же значение (число, текст или дату), в противном случае — FALSE.

Дополнительные сведения и примеры использования операторов сравнения в формулах MS Excel см. в следующем руководстве: Логические операторы Excel — равно, не равно, больше, меньше.

Оператор конкатенации текста

Оператор конкатенации текста в Excel представляет собой символ амперсанда ( и ). Вы можете использовать его для объединения двух или более текстовых строк в одну строку.

Например, если у вас есть коды стран в столбце A и номера телефонов в столбце B, вы можете использовать следующую формулу для получения телефонных номеров в сочетании с кодами стран:

=A1&" "&B1

В выше формулы, мы объединяем пробел " " между ними, чтобы сделать числа более читабельными:

Тот же результат может быть достигнут с помощью функции СЦЕПИТЬ, и следующий учебник объясняет все детали: Как объединить текстовые строки, ячейки и столбцы в Excel.

Операторы ссылок в формулах и функциях Excel

Для указания диапазонов в формулах MS Excel и отдельных аргументов в функциях Excel используются следующие операторы.

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

Например, диапазон A1:A00 включает 100 ячеек от A1 до A100. Чтобы найти среднее значение для этих 100 ячеек, используйте следующую формулу:

=СРЗНАЧ(A1:A00)

Вы также можете обратиться ко всему столбцу (A:A) или ко всей строке ( 1:1). Например, следующая формула находит сумму всех чисел в столбце A: =СУММ(A:A) . Узнайте больше о ссылках на целые столбцы и строки.

Запятая ( , ) — используется для разделения аргументов в формулах электронных таблиц Excel. Например, формула =ЕСЛИ(A1>0, "хорошо", "плохо") читается так: если A1 больше нуля, вернуть "хорошо", иначе "плохо".

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

=ЕСЛИ(A1>0; "хорошо"; "плохо")

Итак, если вы пытаетесь создать формулу на листе, но Excel не принимает ее и выдает ошибку "недопустимая формула" , перейдите в региональные настройки ( Панель управления > Регион и язык > Дополнительные настройки) и проверьте, какой символ установлен там как Разделитель списка . Это тот символ, который вам нужно использовать для разделения аргументов в формулах Excel.

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

=B3:D3 C2 :C4

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

Типы формул Excel

Формулы, создаваемые в электронных таблицах Excel, могут быть простыми или сложными:

• Простые Формулы Excel выполняют только одну математическую операцию, например, =10*5 или =СУММ(A1:A10 )
• Комплекс (расширенный) Формулы Excel включают несколько вычислений, например =10*5+20 или =СУММ(A1:A10)/2

Далее в этом руководстве вы найдете подробные инструкции по созданию обоих типов формул электронных таблиц Excel.

Как создавать формулы в Excel

Как уже было сказано, любая формула Excel начинается со знака равенства (=). Итак, какую бы формулу вы ни собирались написать, начните с ввода = либо в ячейке назначения, либо в строке формул Excel. А теперь давайте подробнее рассмотрим, как можно создавать разные формулы в Excel.

Как создавать простые формулы в Excel

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

• Создание простой формулы Excel с использованием констант и операторов
• Напишите формулы, используя ссылки на ячейки
• Создайте формулу Excel, используя определенные имена
• Создание формул Excel с помощью функций

Как создавать расширенные формулы в Excel

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

Создание сложных формул с константами и математическими операторами

Для правильного вычисления сложной формулы Excel определенные операции должны выполняться раньше других. Порядок операций по умолчанию в формулах Excel следующий:

• Математические операции заключены в круглые скобки
• Степень (экспоненциальные вычисления)
• Умножение и деление, в зависимости от того, что наступит раньше в формуле
• Сложение и вычитание, в зависимости от того, что наступит раньше в формуле

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

А теперь давайте разберем эти формулы, чтобы увидеть, как Microsoft Excel их вычисляет:

Итоговая формула: =$B2*$D2+$ B2*$D2*$C2

• 1-е умножение: $B2*$D2 (цена*количество = количество)
• 2 ^nd и 3 ^rd умножение: $B2*$D2*$C2 (цена*кол-во*НДС % = сумма НДС)
• Дополнение: сумма + сумма НДС = всего

Формула комиссии: =($B2*$D2+$B2*$D2*$C2)*10%

расчет в скобках, и получили желаемый результат.

Конечно, ничто не мешает вам умножить сумму, уже рассчитанную в столбце E, на 10%, в этом случае формула сведется к простому расчету =E2*10% . Однако на больших листах имеет смысл писать независимо вычисляемые формулы, чтобы удаление столбца с одной формулой не нарушило другие.

Формулы Excel с вложенными функциями

В формулах Microsoft Excel вложение одной функции в другую означает использование одной функции в качестве аргумента другой функции. В современных версиях Excel 2016, 2013, 2010 и 2010 можно использовать до 64 вложенных функций. В более ранних версиях Excel 2003 и ниже разрешено только до 7 уровней функций.

Вот очень простой пример вложенной формулы Excel, которая включает функцию СУММ для нахождения итога и функцию ОКРУГЛ для округления этого числа до ближайшего целого числа (0 знаков после запятой):

=ОКРУГЛ(СУММ(B2:B6),0)

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

Подробное объяснение синтаксиса вложенных ЕСЛИ и примеры расширенных формул см. в следующем руководстве: Использование вложенных функций ЕСЛИ в Excel.

Формулы массива в Excel

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

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

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

Эта задача требует сравнения два диапазона, и вы можете сделать это, используя следующую формулу массива:

=СУММ((B2:B10>=A2:A10) * (B2:B10>0))

Примечание. Чтобы правильно ввести формулу массива Excel, вам нужно нажать Ctrl+Shift+Enter вместо обычного штриха Enter.

Чтобы узнать больше о формулах массива Excel, см. следующие руководства:

• Формулы массива, функции и константы Excel — объясняются основы формул массива и показано, как использовать константы массива и функции массива.
• Примеры формул массива Excel для начинающих и опытных пользователей — 7 увлекательных примеров расширенных формул массива в Excel.

Определенные пользователем функции Excel

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

Такие пользовательские функции называются Пользовательскими функциями (UDF), и они особенно полезны для сложных математических или инженерных расчетов. Как и макросы, определяемые пользователем функции написаны на языке VBA (Visual Basic для приложений). Например, вы можете просмотреть и загрузить пользовательские функции, созданные нашей командой, для подсчета и суммирования ячеек по цвету.

Абсолютные, относительные и смешанные ссылки на ячейки в формулах Excel

В Excel существует 3 типа ссылок на ячейки: абсолютные ($A$1), относительные (A1) и смешанные ($A1 или A$1). Все три приведенные выше ссылки относятся к одной и той же ячейке, а знак доллара ($) используется только для одной цели — он сообщает Microsoft Excel, следует ли изменять или не изменять ссылки на ячейки, когда формула перемещается или копируется в другие ячейки.

Абсолютная ссылка на ячейку ($A$1) — знак $ перед координатами строки и столбца делает ссылку статической и позволяет копировать формулу без изменения ссылок .

Относительная ссылка на ячейку (A1) — ссылка на ячейку без изменения знака $ в зависимости от относительного положения строк и столбцов в электронной таблице.

Смешанная ссылка на ячейку — может быть 2 типов:

• Абсолютный столбец и относительная строка ($A1) — знак $ перед буквой столбца блокирует ссылку на указанный столбец, поэтому столбец никогда изменения. Относительная ссылка на строку без знака доллара изменяется в зависимости от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка (A$1) — ссылка на строку, заблокированная символом $, не меняется, а изменяется ссылка на столбец.

На следующем рисунке показано, как различные типы ссылок работают на практике.

Дополнительные сведения о ссылке на ячейку Excel и другие примеры формул см. в разделе Зачем использовать $ в формулах Excel.

Советы и сокращения времени для формул Excel

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

• Для переключения между абсолютными, относительными и смешанными ссылками в формуле используйте клавишу F4, как показано в разделе Переключение между типами ссылок в Excel.
• Чтобы просмотреть все формулы на листе, нажмите кнопку Показать формулы на вкладке Формулы > группа Аудит формул или нажмите сочетание клавиш Ctrl+~.
• Чтобы изменить формулу , нажмите клавишу F2, дважды щелкните ячейку или щелкните строку формул.
• до отладьте формулы в Excel, выберите часть формулы и нажмите F9. Это позволит вам увидеть фактические значения за ссылками на ячейки.
• В скопируйте формулу в все ячейки в столбце , введите формулу в первую ячейку, выберите эту ячейку и наведите курсор на маленький квадрат в правом нижнем углу, пока он не изменится на черный крест (который называется
  дескриптор заполнения). Дважды щелкните этот крестик, и вы получите формулу, скопированную через весь столбец.
• Чтобы преобразовать формулы в значения , выберите все ячейки с формулами, которые вы хотите преобразовать, нажмите Ctrl+C, чтобы скопировать эти формулы, затем нажмите Shift+F10, затем нажмите V, а затем нажмите Enter. Shift + F10 + V — это ярлык для специальной вставки Excel — значения только . Если вы не уверены, что запомните этот ярлык, просто нажмите обычное сочетание клавиш для вставки Ctrl+V, щелкните маленькую стрелку справа от кнопки Вставить , чтобы открыть раскрывающийся список, и выберите Вставить значения . Дополнительные сведения см. в разделе Как заменить формулы их значениями в Excel.

Формулы Microsoft Excel с примерами

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

Процентная формула Excel

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

Формулы суммы Excel

• Как суммировать столбец в Excel - 5 способов суммировать столбец в Excel.
• Формула СУММЕСЛИ в Excel - примеры формул для условного суммирования ячеек.
• Формулы СУММЕСЛИМН в Excel — формула суммирования для сложения ячеек на основе более чем одного критерия.
• Excel СУММ в формулах массива — как использовать функцию СУММ в формулах массива для сложения значений с несколькими критериями И.

Формула подсчета в Excel

• Формула Excel для подсчета ячеек с текстом
• Формула для подсчета пустых ячеек (COUNTBLANK)
• Другая формула для подсчета пустых и не пустых ячеек (СЧЁТЕСЛИ)
• Формула СЧЕТЕСЛИ в Excel — примеры формул для условного подсчета ячеек на основе точного и частичного совпадения, подсчета дубликатов и уникальных значений и т. д.
Формула
• СЧЁТЕСЛИМН в Excel — подсчет ячеек с несколькими критериями.

Формула среднего значения в Excel

• Как вычислить среднее значение в Excel - примеры формул функций СРЗНАЧ, СРЗНАЧА, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН.
• Расчет скользящего среднего в Excel - примеры формул для расчета простого скользящего среднего в Excel за последние N дней, недель, месяцев или лет.
• Формула средневзвешенного значения в Excel — два простых способа расчета средневзвешенного значения в Excel с помощью функций СУММ и СУММПРОИЗВ.

Формулы даты Excel

• Как преобразовать текст в дату в Excel - несколько формул для преобразования текста в дату.
• Как преобразовать дату Excel в текст — формула для преобразования даты в текстовую строку в указанном формате.
• Примеры формулы ДАТА в Excel — как получить порядковый номер, представляющий дату, складывать и вычитать даты в Excel, возвращать дату на основе значений в других ячейках, преобразовывать текстовую строку в дату, а также несколько расширенных примеров формулы ДАТА в Excel.
• Функция дня недели (WEEKDAY) — формулы для возврата дня недели по заданной дате, идентификации, фильтрации, подсчета и выделения рабочих и выходных дней и т. д.
• Как рассчитать месяцы в Excel (функции МЕСЯЦ и КОНМЕСЯЦА) - примеры формулы даты Excel для извлечения месяца из даты, получения первого и последнего дня месяца, преобразования названия месяца в число и многое другое.
• Расчет номера недели в Excel (функция WEEKNUM) — как использовать формулы даты в Excel для получения номера недели по дате, преобразования номера недели в дату, получения номера недели в месяце, суммирования значений по номеру недели и т. д.
• Как складывать и вычитать даты в Excel - формулы даты для сложения и вычитания дней, недель, месяцев и лет.
• Как рассчитать разницу между двумя датами (функция РАЗНДАТ) - Формула даты в Excel для расчета разницы между двумя датами в днях, неделях, месяцах или годах.
• Вычисление рабочих дней в Excel (РАБДЕНЬ и ЧИСТРАБДНИ) - использование формулы даты в Excel для расчета рабочих дней с настраиваемыми параметрами выходных и праздничных дней.
• Преобразование даты в год и вычисление возраста по дате рождения — формулы возраста в Excel и несколько других формул дат для извлечения года из даты, преобразования даты в месяц и год, определения високосных лет.

Формулы времени в Excel

• Как использовать функции СЕЙЧАС и ВРЕМЯ для вставки времени - примеры формул для вставки сегодняшней даты и времени в Excel в виде динамического автоматически обновляемого значения и статической неизменной метки времени.
• Преобразование времени в десятичное число, часы, минуты или секунды - множество формул для преобразования времени в десятичное число, часы, минуты или секунды, преобразование текста во время и наоборот.
• Как рассчитать время в Excel — несколько формул для сложения и вычитания времени, расчета разницы во времени, прошедшего времени и многого другого.

Формулы Excel для ВПР

• Учебное пособие по ВПР в Excel для начинающих — объясняются основы функции ВПР в Excel и приводится ряд примеров формул для выполнения ВПР с другого листа и другой книги, формулы ВПР с подстановочными знаками, ВПР с точными и приблизительное совпадение и т. д.
• Как использовать функции ВПР и СУММ или СУММЕСЛИ в Excel — расширенные формулы Excel для поиска и суммирования значений на основе одного или нескольких критериев.
• Расширенные формулы ВПР Excel — примеры двустороннего поиска, вложенного ВПР с несколькими критериями и т. д.
• Формула Vlookup с учетом регистра в Excel - не требует пояснений 🙂

Формулы условного форматирования Excel

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

Что ж, похоже, мы наконец добрались до конца. То, что планировалось как краткое руководство по формулам Excel для начинающих, почти превратилось в объемное руководство, потому что существует так много различных аспектов формул 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), который мы будем использовать в наших интересах.

The Playas

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

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

 SEARCH(find_text,within_text,[start_num]) 

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

Стратегия

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

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

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

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

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

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

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

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

 

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

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

текст: B3

[число_символов]: C3

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

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

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

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

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

Задача 2: Сравнить два набора данных

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

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

Playas

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

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

ISNA: ISNA(value)

MATCHok: MATCHok , lookup_array, [match_type])

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

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

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

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

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

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

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

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

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

Ресурсы

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

Если вы действительно хотите стать хардкорщиком, я настоятельно рекомендую канал Билла Джелена на YouTube. Его плейлисты особенно полезны, если вы хотите сконцентрироваться на определенной слабой области. Еще один подкаст, который мне очень нравится, — это канал Майка Гирвина ExcelIsFun на YouTube.

Я также регулярно публикую видеоуроки по Excel в своем блоге.

Заключительные мысли

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

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