как пользоваться, создавать и изменять
Формулы Excel используют, когда данных очень много. Например, чтобы посчитать сумму нескольких чисел быстрее, чем на калькуляторе. Преимуществ много, поэтому работодатели часто указывают эту программу в требованиях. В конце марта 2022 года 64 225 вакансий на хедхантере содержали формулировки вроде «уверенный пользователь Excel», «работа с формулами в Excel».
Кому важно знать Excel и где выучить основы
Excel нужен бухгалтерам, чтобы вести учет в таблицах. Экономистам, чтобы делать перерасчет цен, анализировать показатели компании. Менеджерам — вести базу клиентов. Аналитикам — строить и проверять гипотезы.
Программу можно освоить самостоятельно, например по статьям в интернете. Но это поможет понять только основные формулы. Если нужны глубокие знания — как строить сложные прогнозы, собирать калькулятор юнит-экономики, — пройдите курсы.
На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой.
Еще используют символы сравнения:
равенство =
меньше <
больше >
меньше либо равно <=
больше либо равно >=
не равно <>
Основные виды
Все формулы в Excel делятся на простые, сложные и комбинированные. Их можно написать самостоятельно или воспользоваться встроенными.
Простые
Применяют, когда нужно совершить одно простое действие, например сложить или умножить.
✅ СУММ. Складывает несколько чисел. Сумму можно посчитать для нескольких ячеек или целого диапазона.
</p> <p>=СУММ(А1;В1) — для соседних ячеек;</p> <p>=СУММ(А1;С1;h2) — для определенных ячеек;</p> <p>=СУММ(А1:Е1) — для диапазона.</p> <p>
Сумма всех чисел в ячейках от А1 до Е1
✅ ПРОИЗВЕД. Умножает числа в соседних, выбранных вручную ячейках или диапазоне.
</p> <p>=ПРОИЗВЕД(А1;В1)</p> <p>=ПРОИЗВЕД(А1;С1;h2)</p> <p>=ПРОИЗВЕД(А1:Е1)</p> <p>
Произведение всех чисел в ячейках от А1 до Е1
✅ ОКРУГЛ. Округляет дробное число до целого в большую или меньшую сторону. Укажите ячейку с нужным числом, в качестве второго значения — 0.
</p> <p>=ОКРУГЛВВЕРХ(А1;0) — к большему целому числу;</p> <p>=ОКРУГЛВНИЗ(А1;0) — к меньшему.</p> <p>
Округление в меньшую сторону
✅ ВПР. Находит данные в таблице или определенном диапазоне.
</p> <p>=ВПР(С1;А1:В6;2)</p> <p>
- С1 — ячейка, в которую выписывают известные данные. В примере это код цвета.
- А1 по В6 — диапазон ячеек. Ищем название цвета по коду.
- 2 — порядковый номер столбца для поиска. В нём указаны названия цвета.
Формула вычислила, какой цвет соответствует коду
✅ СЦЕПИТЬ. Объединяет данные диапазона ячеек, например текст или цифры. Между содержимым ячеек можно добавить пробел, если объединяете слова в предложения.
</p> <p>=СЦЕПИТЬ(А1;В1;С1) — текст без пробелов;</p> <p>=СЦЕПИТЬ(А1;" ";В1;" "С1) — с пробелами.</p> <p>
Формула объединила три слова в одно предложение
✅ КОРЕНЬ. Вычисляет квадратный корень числа в ячейке.
</p> <p>=КОРЕНЬ(А1)</p> <p>
Квадратный корень числа в ячейке А1
✅ ПРОПИСН. Преобразует текст в верхний регистр, то есть делает буквы заглавными.
</p> <p>=ПРОПИСН(А1:С1)</p> <p>
Формула преобразовала строчные буквы в прописные
✅ СТРОЧН. Переводит текст в нижний регистр, то есть делает из больших букв маленькие.
</p> <p>=СТРОЧН(А2)</p> <p>
✅ СЧЕТ. Считает количество ячеек с числами.
</p> <p>=СЧЕТ(А1:В5)</p> <p>
Формула вычислила, что в диапазоне А1:В5 четыре ячейки с числами
✅ СЖПРОБЕЛЫ. Убирает лишние пробелы. Например, когда переносите текст из другого документа и сомневаетесь, правильно ли там стоят пробелы.
</p> <p>=СЖПРОБЕЛЫ(А1)</p> <p>
Формула удалила двойные и тройные пробелы
Сложные
✅ ПСТР. Выделяет определенное количество знаков в тексте, например одно слово.
</p> <p>=ПСТР(А1;9;5)</p> <p>
- Введите =ПСТР.
- Кликните на ячейку, где нужно выделить знаки.
- Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
- Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 5.
В ячейке А1 формула выделила 5 символов, начиная с 9-го
✅ ЕСЛИ. Анализирует данные по условию. Например, когда нужно сравнить одно с другим.
</p> <p>=ЕСЛИ(A1>25;"больше 25";"меньше или равно 25")</p> <p>
В формуле указали:
- А1 — ячейку с данными;
- >25 — логическое выражение;
- больше 25, меньше или равно 25 — истинное и ложное значения.
Первый результат возвращается, если сравнение истинно. Второй — если ложно.
Число в А1 больше 25. Поэтому формула показывает первый результат — больше 25.
✅ СУММЕСЛИ. Складывает числа, которые соответствуют критерию. Обычно критерий — числовой промежуток или предел.
</p> <p>=СУММЕСЛИ(В2:В5;">10")</p> <p>
В формуле указали:
- В2:В5 — диапазон ячеек;
- >10 — критерий, то есть числа меньше 10 не будут суммироваться.
Число 8 меньше указанного в условии, то есть 10. Поэтому оно не вошло в сумму.
✅ СУММЕСЛИМН. Складывает числа, когда условий несколько. В формуле указывают диапазоны — ячейки, которые нужно учитывать. И условия — содержание подходящих ячеек. Например:
</p> <p>=СУММЕСЛИМН(D2:D6;C2:C6;"сувениры";B2:B6;"ООО ХY")</p> <p>
- D2:D6 — диапазон, из которого суммируем числа;
- C2:C6 — диапазон ячеек для категории;
- сувениры — условие, то есть числа другой категории учитываться не будут;
- B2:B6 — диапазон ячеек для компании;
- ООО XY — условие, то есть числа другой компании учитываться не будут.
Под условия подошли только ячейки D3 и D6: их сумму и вывела формула
Комбинированные
</p> <p>=ЕСЛИ(СУММ(A1;B1)<65;СУММ(A1;B1)*1,5;(СУММ(A1;B1)*2))</p> <p>
То есть если сумма двух чисел в А1 и В1 окажется меньше 65, программа посчитает первое условие — СУММ(А1;В1)*1,5. Больше 65 — Excel задействует второе условие — СУММ(А1;В1)*2.
Сумма в А1 и В1 больше 65, поэтому формула посчитала по второму условию: умножила на 2
Встроенные
Используйте их, если удобнее пользоваться готовыми формулами, а не вписывать вручную.
- Поместите курсор в нужную ячейку.
- Откройте диалоговое окно мастера: нажмите клавиши Shift + F3. Откроется список функций.
- Выберите нужную формулу. Нажмите на нее, затем на «ОК». Откроется окно «Аргументы функций».
- Внесите нужные данные. Например, числа, которые нужно сложить.
Ищите формулу по алфавиту или тематике, выбирайте любую из тех, что использовали недавно
Как скопировать
Если для разных ячеек нужны однотипные действия, например сложить числа не в одной, а в нескольких строках, скопируйте формулу.
- Впишите функцию в ячейку и кликните на нее.
- Наведите курсор на правый нижний угол — курсор примет форму креста.
- Нажмите левую кнопку мыши, удерживайте ее и тяните до нужной ячейки.
- Отпустите кнопку. Появится итог.
Посчитали сумму ячеек в трех строках
Как обозначить постоянную ячейку
Это нужно, чтобы, когда вы протягивали формулу, ссылка на ячейку не смещалась.
- Нажмите на ячейку с формулой.
- Поместите курсор в нужную ячейку и нажмите F4.
- В формуле фрагмент с описанием ячейки приобретет вид $A$1.
Как поставить «плюс», «равно» без формулы
Когда нужна не формула, а данные, например +10 °С:
- Кликните правой кнопкой по ячейке.
- Выберите «Формат ячеек».
- Отметьте «Текстовый», нажмите «ОК».
- Поставьте = или +, затем нужное число.
- Нажмите Enter.
Главное о формулах в Excel
- Формула состоит из математических знаков. Чтобы ее вписать, используют символы = ( ) ; : .
- С помощью простых формул числа складывают, умножают, округляют, извлекают из них квадратный корень. Чтобы отредактировать текст, используют формулы поиска, изменения регистра, удаления лишних пробелов.
- Сложные и комбинированные формулы помогают делать объемные вычисления, когда нужно соблюдать несколько условий.
Сложные формулы в Excel и Google-таблицах за 500 руб., исполнитель Максим (Maximusu) – Kwork
Бесконечные бесплатные правки в рамках технического задания и условий заказа. Платить нужно только за те изменения, которые выходят за рамки первоначального заказа. Подробнее
Maximusu
- 5.0
- (665)
Об этом кворке
Часто бывает, что в рабочей книге нужно сгруппировать, или подтянуть данные в ту или иную ячейку.
Формулой СУММ() владеют наверное все пользователи Excel.
сумме если() — с легкостью применят 90% пользователей.
А вот функция ВПР(), которая является одной из самых незаменимых функций Excel, поставит в тупик уже добрых 3/4 простых смертных.
И как быть, если даже пятиэтажная формула, построенная самыми искушенными из нас, не может удовлетворить наши замыслы?
Создадим свою! И она будет легко читаться и легко применяться. В отличие от шестиэтажного аналога, созданного штатными средствами Excel.
Правда здесь уже нужны навыки программирования. Но зато если кто-то такую формулу создаст, и объяснит правила ее применения, то применять ее можно безо всяких ограничений. И если будет необходимость, то легко переносить и в другие книги.
Именно созданию формул всеми штатными и нештатными способами и посвящен этот кворк.
<p>Часто бывает, что в рабочей книге нужно сгруппировать, или подтянуть данные в ту или иную ячейку.</p><p>Формулой СУММ() владеют наверное все пользователи Excel.</p><p>сумме если() — с легкостью применят 90% пользователей.</p><p>А вот функция ВПР(), которая является одной из самых незаменимых функций Excel, поставит в тупик уже добрых 3/4 простых смертных.</p><p>И как быть, если даже пятиэтажная формула, построенная самыми искушенными из нас, не может удовлетворить наши замыслы?</p><p>Создадим свою! И она будет легко читаться и легко применяться. В отличие от шестиэтажного аналога, созданного штатными средствами Excel.</p><p>Правда здесь уже нужны навыки программирования. Но зато если кто-то такую формулу создаст, и объяснит правила ее применения, то применять ее можно безо всяких ограничений. И если будет необходимость, то легко переносить и в другие книги.</p><p>Именно созданию формул всеми штатными и нештатными способами и посвящен этот кворк.</p>
Программа: Excel
Язык перевода:
Объем услуги в кворке: Одна сложная формула в Excel или Google-таблицах, состоящая не более чем из 2-х встроенных в табличный редактор функций. Математические, экономические, статистические, физические и аналогичные задачи, требующие специфических знаний по таким дисциплинам, в рамках именно данного Кворка также не решаются (друзья, эти дисциплины-не мой конек). Операционная система — Windows — для MS Office, любая для Google-таблиц
Развернуть Свернуть
Гарантия возврата
Средства моментально вернутся на счет,
если что-то пойдет не так. Как это работает?
Расскажите друзьям об этом кворке
Excel: Создание более сложных формул
Урок 14.
Создание более сложных формул/en/excel/intro-to-formulas/content/
Введение
У вас может быть опыт работы с формулами, которые содержат только один оператор, например 7+9 . Более сложные формулы могут содержать несколько математических операторов , например 5+2*8 . Если в формуле более одной операции, порядок операций указывает Excel, какую операцию следует вычислить первой. Чтобы написать формулы, которые дадут вам правильный ответ, вам нужно понять порядок операций. 92, например)
Mnemon P аренда E извините M y D ухо A unt S союзник.
Щелкните стрелки в слайд-шоу ниже, чтобы узнать, как порядок операций используется для вычисления формул в Excel. 92=4.
Далее мы решим любое умножение и деление слева направо. Поскольку операция деления предшествует умножению, она вычисляется первой: 3/4=0,75.
Теперь решим оставшуюся операцию умножения: 0,75*4=3.
Далее мы вычислим любое сложение или вычитание, снова работая слева направо. Сначала идет сложение: 10+3=13.
Наконец, у нас осталась одна операция вычитания: 13-1=12.
Теперь у нас есть ответ: 12. И это точно такой же результат, который вы получите, если введете формулу в Excel.
Создание сложных формул
В приведенном ниже примере мы покажем, как Excel использует порядок операций для решения более сложной формулы. Здесь мы хотим рассчитать стоимость налога с продаж для счета за питание. Для этого запишем нашу формулу в виде =(D3+D4+D5)*0,075 в ячейке 9.0009 Д6 . Эта формула добавит цены наших товаров, а затем умножит это значение на налоговую ставку 7,5% (которая записывается как 0,075), чтобы рассчитать ответ.
Excel следует порядку операций и сначала складывает значения в скобках: (45,80+68,70+159,60) = 274,10 . Затем это значение умножается на налоговую ставку: 274,10*0,075 . Результат покажет, что налог с продаж составляет $20,56 .
Особенно важно соблюдать порядок операций при создании формулы. В противном случае Excel не сможет точно вычислить результаты. В нашем примере, если круглые скобки не включены, сначала вычисляется умножение, и результат неверен. Круглые скобки часто являются лучшим способом определить, какие вычисления будут выполняться в Excel первыми.
Чтобы создать сложную формулу с использованием порядка операций:
В приведенном ниже примере мы будем использовать ссылок на ячейки вместе с числовыми значениями , чтобы создать сложную формулу, которая будет вычислять промежуточный итог для счета за питание. . Формула сначала рассчитает стоимость каждого пункта меню, а затем добавит эти значения.
- Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку C5 .
- Введите формулу . В нашем примере мы введем =B3*C3+B4*C4 . Эта формула будет следовать порядку операций, сначала выполняя умножение: 2,79*35 = 97,65 и 2,29*20 = 45,80 . Затем он добавит эти значения для расчета суммы: 97,65+45,80 .
- Дважды проверьте формулу на точность, затем нажмите Введите на клавиатуре. Формула рассчитает и отобразит результат . В нашем примере результат показывает, что промежуточная сумма заказа составляет 143,45 $ .
Вы можете добавить круглых скобок к любому уравнению, чтобы его было легче читать. Хотя это не изменит результат формулы в этом примере, мы могли бы заключить операции умножения в круглые скобки, чтобы уточнить, что они будут вычисляться перед сложением.
Эксель не всегда сообщит вам , если ваша формула содержит ошибку, поэтому вы должны проверить все свои формулы. Чтобы узнать, как это сделать, вы можете прочитать урок «Перепроверьте свои формулы» из нашего учебника по формулам Excel.
Вызов!
В этом задании вы будете работать с другим счетом-фактурой, как в нашем примере. В счете-фактуре вы найдете сумму налога за заказ, общую сумму заказа и общую сумму заказа, если вам была предоставлена скидка 10%.
- Откройте нашу рабочую тетрадь.
- Щелкните вкладку рабочего листа Challenge в левом нижнем углу рабочей книги.
- В ячейке D7 создайте формулу для расчета налога для счета-фактуры. Используйте ставку налога с продаж 7,5% .
- В ячейке D8 создайте формулу для нахождения суммы заказа. Другими словами, эта формула должна добавить ячейки D3:D7 .
- В ячейке D9 создайте формулу, которая вычисляет итог после 10% скидка. Если вам нужна помощь в понимании того, как уменьшить процент от общей суммы, просмотрите наш урок о скидках, уценках и распродажах.
- Когда вы закончите, ваша электронная таблица должна выглядеть так:
Предыдущий: Введение в формулы
Далее:Относительные и абсолютные ссылки на ячейки
/en/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, но мы не будем вдаваться в подробности.
Таким образом, каждая из этих функций запрашивает у вас:
- Из какой ячейки вы хотите извлечь текст
- Сколько символов вы хотите извлечь
С помощью функции MID вы также указываете начальную точку, потому что вы тянете с середины струны.
Это все хорошо, но с нашим столбцом URL количество символов, которое нам нужно, чтобы извлечь изменения из URL в URL, поэтому мы не можем использовать статическое число. Вот тут и приходит на помощь функция ПОИСК.
Быстро в сторону: Если вы много баловались формулами, вы можете спросить, почему я не использую функцию НАЙТИ. Что ж, функции ПОИСК и НАЙТИ очень похожи, только функция НАЙТИ более ограничивающая, поскольку она чувствительна к регистру и не поддерживает подстановочные знаки. Хромой. Таким образом, только раз, когда я использую FIND, когда я хочу указать регистр, который в последний раз, когда я делал это, был последним … Арендатор здесь … Да, никогда.
Итак, что мы собираемся сделать, так это использовать функцию ПОИСК, чтобы указать количество необходимых нам символов, потому что она возвращает позицию того, что вы просите Excel найти. Если он не находит то, что вы искали, он возвращает неприятную ошибку #N/A, которую мы фактически ИСПОЛЬЗУЕМ в следующем примере. Однако для нашей задачи нам нужна позиция первой косой черты после домена.
Бууут, есть проблема.
Видите эти две косые черты перед доменом (http: // )? Да, они собираются сбить нас с толку. Но не беспокойтесь. Функция SEARCH имеет необязательный аргумент для начального числа (start_num), который мы будем использовать в наших интересах.
The Playas
Итак, для нашей формулы мы собираемся объединить две функции: ВЛЕВО и ПОИСК. Вот синтаксис для каждого:
LEFT(text, [num_chars])
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.) хороший шанс, что это осиротевшие или заблокированные страницы. В любом случае, они требуют расследования и, вероятно, не должны быть в вашей карте сайта.
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-специалистов. Это отличная отправная точка, если вы хотите начать свое путешествие, сосредоточившись на функциях, которые маркетологи используют чаще всего.
Если вы действительно хотите стать хардкорщиком, я настоятельно рекомендую канал Билла Джелена на YouTube.