21 корисна функція Excel для інтернет-маркетологів

1309

Excel – програма, якою ми користуємося практично кожен день, і про те, як вона полегшує життя більшості користувачів, можна навіть не говорити. Але чим же вона корисна для інтернет-маркетологів? Ми розглянемо 21 функцію Excel і спробуємо відповісти на це питання.

21 корисна функція Excel для інтернет-маркетологів

Перш ніж приступити до огляду, розглянемо значення визначень, які зустрінуться вам в цій статті.

Синтаксис – це формула функції, що починається зі знаку рівності і складається з 2 частин: назви функції і аргументів, що мають певну послідовність і укладені в круглі дужки.

Аргументи функції можуть бути представлені як текстовими, числовими або логічними значеннями, так і посиланнями на комірки або діапазон комірок. Між собою аргументи розділяються крапкою з комою.

Отже, поїхали!

1) ВПР

Функція ВВР дозволяє знайти дані в текстовому рядку таблиці або діапазону і додати їх в іншу таблицю. Абревіатура ВВР розшифровується як «вертикальний перегляд».

Синтаксис

Дана функція складається з 4 аргументів і представлена наступною формулою:

=ВПР(шукане_значення;таблиця;номер_стовпчика;[точність_пошуку])

Розглянемо кожен з аргументів:

  • «Шукане значення» вказують у першому стовпці розглянутого діапазону клітинок. Даний аргумент може бути значення або посилання на комірку.
  • «Таблиця». Група комірок, в якій виконується пошук шуканого значення і повертається. Діапазон клітинок повинен містити шукане значення в першому стовпці і повернуте значення в будь-якому місці.
  • «Номер стовпця». Номер стовпця, що містить значення, що повертається.
  • «Інтервальний перегляд» – необов’язковий аргумент. Це логічний вираз, що визначає, наскільки точне співпадання повинна виявити функція. У зв’язку з цим умовою виділяють 2 функції:
  • ІСТИНА. Ця функція, що вводиться за замовчуванням, шукає найближчим до шуканого значення. Дані першого стовпця повинні бути впорядковані за зростанням або в алфавітному порядку.
  • БРЕХНЯ. Ця функція шукає точне значення в першому стовпці.
  • Приклади

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

    21 корисна функція Excel для інтернет-маркетологів

    Функції потрібно знайти дані, які відповідають значенню «планшет», яке зазначено в окремій клітинці (С3) і виступає в ролі шуканого значення. Аргумент «таблиця» тут – діапазон пошуку A1:B6; номер стовпця, що містить значення, що повертається – «2». В результаті отримуємо наступну формулу: =ВПР(С3;А1:B6;2). Результат – 31325 переглядів в місяць.

    У наступних двох прикладах застосований інтервальний перегляд з двома варіантами функцій: ІСТИНА і БРЕХНЯ.

    21 корисна функція Excel для інтернет-маркетологів
    21 корисна функція Excel для інтернет-маркетологів

    Функція VLOOKUP є однією з найбільш популярних функцій Excel, досить складною для розуміння, але надзвичайно корисною.

    2) ЯКЩО

    ЯКЩО функція виконує перевірку заданих умов, вибираючи один з двох можливих результатів: 1) Якщо порівняння істинно; 2) Якщо порівняння помилково.

    Синтаксис

    Формула функції складається з трьох аргументів і виглядає наступним чином:

    =Если(логічний_вираз;«значення_якщо_істина»; значення_якщо_хибність), де:

    • «логічний вираз» – формула;
    • «якщо значення істина» – значення, при якому логічне вираз;
    • «якщо значення false» – значення, при якому логічне вираження не виконується.

    Приклади

    Розглянемо приклад використання звичайної функції ЯКЩО.

    21 корисна функція Excel для інтернет-маркетологів

    Для того щоб дізнатися, хто з продавців виконав план, а хто ні, потрібно ввести наступну формулу:

    =ЯКЩО(B2>30000;«План виконаний»;«План не виконаний»)

    Логічний вираз тут – формула «B2>30000».

    «Якщо значення істина» – «План виконано».

    «Якщо значення false» – «План не виконаний».

    ЯКЩО вкладені функції

    Крім звичайної функції ЯКЩО, яка видає всього 2 результату – «істина» і «брехня», існують вкладені функції ЯКЩО, видають від 3 до 64 результатів. В даному випадку формула може вміщати в себе декілька функцій.

    21 корисна функція Excel для інтернет-маркетологів

    Вкладені функції досить складні у використанні і часто видають всілякі помилки у формулі, тому рекомендую користуватися ними у виняткових випадках.

    Існує ще один спосіб використання функції ЯКЩО – для перевірки, чи порожня клітинка чи ні. Для цього її можна використовувати разом з функцією ЕПУСТО.

    У цьому випадку формула буде такою: =ЯКЩО(ЕПУСТО(номер комірки);«Порожня»;«Не пуста».

    21 корисна функція Excel для інтернет-маркетологів

    Замість функції ЕПУСТО також можна використовувати іншу формулу: «номер комірки=«» (нічого).

    21 корисна функція Excel для інтернет-маркетологів

    ЯКЩО – одна з найбільш популярних функцій в Excel, проста і зручна у використанні. Вона допомагає визначити істинність тих чи інших значень, одержати результати за різними даними і виявити порожні клітинки, до того ж її можна використовувати в поєднанні з іншими функціями.

    ЯКЩО функція є основою інших формул: SUMIF, COUNTIF, IFERROR СРЕСЛИ. Ми розглянемо три з них – SUMIF, COUNTIF і IFERROR.

    3) SUMIF і SUMIFS

    Функція SUMIF дозволяє підсумовувати дані, що відповідають певній умові, що знаходяться у вказаному діапазоні.

    Синтаксис

    Функція складається з 3 аргументів і має формулу:

    =SUMIF(діапазон;умова;[диапазон_суммирования])

    «Умова» – аргумент, який визначає які саме комірки потрібно підсумувати. Це може бути текст, число, посилання на клітинку або функція. Зверніть увагу на те, що умови з текстом і математичними знаками необхідно брати в лапки.

    «Діапазон сумування» – необов’язковий аргумент, який дозволяє вказати на клітинки, дані яких потрібно підсумувати, якщо вони відрізняються від осередків, що входять в діапазон.

    Приклад

    У наведеному нижче прикладі функція підсумовувала дані запитів, кількість переходів по яким більше 100000.

    21 корисна функція Excel для інтернет-маркетологів

    Якщо потрібно підсумувати осередку у відповідності з кількома умовами, можна скористатися функцією SUMIFS.

    Синтаксис

    Формула цієї функції має наступний вигляд:

    =SUMIFS(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

    «Діапазон умови 1» і «умова 1» – обов’язкові аргументи, решта – додаткові.

    4) COUNTIF і COUNTIFS

    Функція COUNTIF вважає кількість непустих клітинок, які відповідають заданому умові всередині зазначеного діапазону.

    Синтаксис

    Формула функції:

    =COUNTIF(діапазон;критерій)

    «Діапазон» – група комірок, які потрібно підрахувати.

    «Критерій» – умова, згідно з яким вибираються комірки для підрахунку.

    Приклад

    У наведеному прикладі функція підрахувала кількість ключів, кількість переходів по яким більше 100000, – в результаті вийшло 3 ключа.

    21 корисна функція Excel для інтернет-маркетологів

    У функції COUNTIF можна використовувати тільки один критерій. Якщо ж потрібно зробити підрахунок за кількома критеріями, можна застосувати функцію COUNTIFS.

    Синтаксис

    Функція дозволяє підрахувати кількість клітинок, які відповідають декільком умовам. Кожній умові відповідає один варіант діапазону клітинок.

    Формула функції:

    =COUNTIFS(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

    «Діапазон умови 1» і «умова 1» – обов’язкові аргументи, інші ж необов’язкові аргументи. Можна використовувати до 127 пар діапазонів і умов.

    5) IFERROR

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

    Синтаксис

    Функція має 2 аргументу і представлена формулою: =IFERROR(значення;значение_если_ошибка), де:

    • «значення» – формула, яка перевіряється на наявність помилок;
    • «значение_если_ошибка» – значення, що з’являється в комірці в тому разі, якщо обчислення у формулі видало помилку.

    Приклади

    21 корисна функція Excel для інтернет-маркетологів

    Припустимо, що у вас зламався лічильник аналітики, і в комірці, в якій потрібно вказати число відвідувачів, стоїть нуль, а число покупок – 32. Як таке може бути? Функція в даному випадку вказує на помилку і вводить значення, відповідне їй – «перевірити».

    21 корисна функція Excel для інтернет-маркетологів

    6) LEFT

    Функція ЛЕВСИМВ дозволяє виділити необхідну кількість знаків з лівого боку рядка.

    Синтаксис

    Функція складається з 2 аргументів і представлена формулою: =LEFT(текст;[кількість_символів]), де:

    • «текст» – текстовий рядок, який містить знаки, які необхідно вилучити;
    • «число знаків» необов’язковий аргумент, який вказує на кількість видобутих знаків.

    Приклад

    Використання даної функції дозволяє подивитися, як будуть виглядати тайтли до сторінок сайту або статей.

    21 корисна функція Excel для інтернет-маркетологів

    Наприклад, якщо ви хочете, щоб тайтли були максимально лаконічними і складалися з 60 знаків, функція відрахує перші 60 символів і покаже, як буде виглядати той чи інший тайтл. Для цього необхідно скласти формулу: =LEFT(А5;60), де А5 – адреса розглянутої комірки, «60» – кількість отриманих символів.

    7) MID

    Функція MID дозволяє отримати необхідну кількість символів всередині тексту, починаючи з вказаної позиції.

    Синтаксис

    Формула функції складається з 3 аргументів:

    =MID(текст;поч_позиція;кількість_символів).

    «Текст» – рядок, що містить символи, які потрібно вилучити.

    «Початкова позиція» – це позиція знака, з якого починається витягають текст.

    «Число знаків» – кількість видобутих символів.

    Приклад

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

    21 корисна функція Excel для інтернет-маркетологів

    8) UPPER

    Функція UPPER робить всі букви в тексті прописними.

    Синтаксис

    Формула функції:

    =UPPER(текст)

    «Текст» тут – текст або посилання на комірку.

    Приклад

    21 корисна функція Excel для інтернет-маркетологів

    9) LOWER

    Функція LOWER робить всі букви в тексті малими.

    Синтаксис

    Формула функції:

    =LOWER(текст)

    Аргумент «текст» – текст або адресу комірки.

    Приклад

    21 корисна функція Excel для інтернет-маркетологів

    10) MATCH

    Функція MATCH допомагає знайти вказаний елемент в масиві осередків і визначає його положення.

    Синтаксис

    Формула функції:

    =MATCH(шукане_значення;просматриваемый_массив;тип_співпадіння).

    «Шукане значення» і «проглядається масив» – обов’язкові аргументи, «тип зіставлення» – необов’язковий.

    Розглянемо докладніше аргумент «тип зіставлення». Він вказує, яким чином зіставляється знайдене значення з шуканим. Існує 3 типи зіставлення:

    1 – значення менше або дорівнює шуканому (при вказівці даного типу потрібно враховувати, що проглядається масив повинен бути впорядкований за зростанням);

    0 – точне співпадання;

    -1 – найменше значення, яке більше або дорівнює шуканому.

    Приклади

    Розглянемо наступний приклад. Тут я спробувала дізнатися, який із запитів у наведеній таблиці має кількість переходів, що дорівнює або менше 900.

    21 корисна функція Excel для інтернет-маркетологів

    Формула функції тут: =MATCH(900;B2:B6;1). 900 – шукане значення, B2:B6 – проглядається масив, 1 – тип зіставлення (менше або дорівнює шуканому). Результат – «3», тобто третя позиція у зазначеному діапазоні.

    11) LEN

    Функція LEN дозволяє визначити довжину тексту, який міститься в зазначеній комірці.

    Синтаксис

    Формула функції має лише один аргумент – текст (номер комірки):

    =LEN(текст)

    Приклад

    Дану функцію можна використовувати для перевірки довжини символів в description.

    21 корисна функція Excel для інтернет-маркетологів

    12) ЗЧЕПИТИ

    Функція ЗЧЕПИТИ дозволяє об’єднати кілька текстових елементів в один рядок. У формулі для об’єднання елементів зазначаються номери комірок, що містять текст, так і сам текст. Можна вказати до 255 елементів і до 8192 символів.

    Синтаксис

    Для того щоб об’єднати текстові елементи без пробілів, використовуються наступні формули:

    =СЦЕПИТЬ(текст1;текст2;текст3);

    Аргумент «текст» – текст або посилання на комірку.

    Приклади

    У наведеному нижче прикладі введена наступна формула: =СЦЕПИТЬ(A2;B2;C2)

    21 корисна функція Excel для інтернет-маркетологів

    Для того ж, щоб слова в рядку розділені пробілами, у формулу необхідно вставити знаки пробілів в лапках:

    =СЦЕПИТЬ(текст1;« »;текст2;« »;текст3;« »)

    У наступному прикладі функція представлена формулою: =СЦЕПИТЬ(A2;» «;B2;» «;C2)

    21 корисна функція Excel для інтернет-маркетологів

    Існує й інший варіант додавання прогалин у формулу функції – введення слів, укладених в лапки разом із пробілами:

    =СЦЕПИТЬ(«текст1; текст2 »;«текст3 »)

    21 корисна функція Excel для інтернет-маркетологів

    13) PROPER

    Функція PROPER перетворює заголовні букви всіх слів в тексті в прописні (верхній регістр), а всі інші букви – рядкові (нижній регістр).

    Синтаксис

    Функція дуже проста у використанні і представлена короткою формулою, що має лише один аргумент:

    =PROPER(текст)

    Приклад

    Розглянемо приклад, у якому представлені зразки з різними варіантами написання букв. Функція швидко привела їх у читабельне стан.

    21 корисна функція Excel для інтернет-маркетологів

    Цю функцію дуже зручно використовувати при складанні списків з іменами власними, для перетворення текстових елементів, надрукованих малими прописними або різними за розміром літерами.

    14) CLEAN

    Функція CLEAN дозволяє видалити всі недруковані символи з тексту.

    Синтаксис

    Формула даної функції:

    =CLEAN(текст)

    Приклад

    У наведеному прикладі текст у клітинці A1 містить недруковані символи кінця абзацу.

    21 корисна функція Excel для інтернет-маркетологів

    Цю функцію потрібно використовувати в тих випадках, коли текст переноситься в таблицю з інших додатків, що мають знаки, друк яких неможлива в Excel.

    15) TRIM

    Ця функція видаляє всі зайві пробіли між словами.

    Синтаксис

    Формула функції проста: =TRIM(номер_ячейки)

    Приклад

    21 корисна функція Excel для інтернет-маркетологів

    Функція проста і корисна. Єдиний мінус полягає в тому, що вона не розрізняє кордонів слів, і якщо всередині нього стоять прогалини, функція цього не зрозуміє і не видалить їх.

    21 корисна функція Excel для інтернет-маркетологів

    16) ЗНАЙТИ

    Функція find дозволяє виявити шуканий текст всередині текстового рядка і вказує на початкову позицію цього тексту відносно початку показувалася рядка.

    Синтаксис

    Функція ЗНАЙТИ складається з 3 аргументів і представлена формулою:

    =Find(шуканий_текст;шуканий_текст;[поч_позиція]);

    «Початкова позиція» – необов’язковий аргумент, що позначає символ, з якого потрібно почати пошук.

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

    Приклад

    В даному прикладі функція представлена наступною формулою: =FIND(«чай»;A4)

    21 корисна функція Excel для інтернет-маркетологів

    17) ІНДЕКС

    Функція ІНДЕКС дозволяє повертати шукане значення.

    Синтаксис

    Формула функції ІНДЕКС має наступний вигляд:

    =ІНДЕКС(масив; номер_рядка; [номер_стовпчика])

    «Номер стовпця» – необов’язковий аргумент.

    Приклад

    21 корисна функція Excel для інтернет-маркетологів

    Функцію ІНДЕКС можна використовувати разом з функцією MATCH з метою заміни функції ВПР.

    18) EXACT

    Ця функція перевіряє ідентичність двох текстів, і, якщо вони збігаються, видає значення ІСТИНА, якщо ж різняться – значення FALSE.

    Синтаксис

    Формула функції: =EXACT(текст1;текст2)

    Приклад

    21 корисна функція Excel для інтернет-маркетологів

    Пари слів з рядків 1 (A1 та B1) і 2 (A2 і B2) різні за написанням, тому функція видає значення FALSE, а слова з 3-го рядка абсолютно ідентичні, тому визначаються як ІСТИНА.

    Використання функції буде корисно при аналізі великого обсягу інформації з метою виявлення випадків різного написання одних і тих же слів.

    19) АБО

    Логічна функція АБО повертає значення ІСТИНА, якщо хоча б один аргумент у формулі має значення TRUE і FALSE, якщо всі аргументи мають значення FALSE.

    Синтаксис

    Формула функції:

    =АБО(логическое_значение1;[логическое_значение2];…)

    Тут «логічне значення1» – обов’язковий аргумент, інші аргументи – необов’язкові. У формулу можна додавати від 1 до 255 логічних значень.

    Приклад

    Формула в даному прикладі видає значення ІСТИНА, так як 2 з 3 аргументи мають значення ІСТИНА.

    21 корисна функція Excel для інтернет-маркетологів

    20) І

    Функція І повертає значення ІСТИНА, якщо всі аргументи у формулі мають значення TRUE і FALSE, якщо хоча б один з аргументів має значення FALSE.

    Синтаксис

    Функція може містити безліч аргументів і має формулу:

    =І(логическое_значение1;[логическое_значение2];…)

    «Логическое_значение1» – обов’язковий аргумент, інші аргументи – необов’язкові.

    Приклад

    21 корисна функція Excel для інтернет-маркетологів

    У цьому прикладі всі аргументи мають значення ІСТИНА, тому і результат відповідний.

    Функції Та і АБО дуже прості у використанні, але якщо поєднувати їх разом або в комбінації з іншими функціями (ЯКЩО і НЕ), можна вивести більш складні і цікаві формули.

    21) OFFSET

    Функція OFFSET повертає посилання на діапазон, віддалений від комірки або групи комірок на вказану кількість рядків і стовпців.

    Синтаксис

    Функція складається з 5-ти аргументів і представлена наступною формулою:

    =OFFSET(посилання;рядки;стовпці;[висота];[ширина])

    Розглянемо кожен з аргументів:

  • «Посилання». Цей аргумент являє собою посилання на клітинку або діапазон клітинок, від яких обчислюється зсув.
  • «Зсув по рядках». Цей аргумент показує кількість рядків, які необхідно відрахувати, щоб перемістити ліву верхню комірку масиву або одну клітинку в потрібне місце. Значення аргументу може бути позитивним (якщо відлік рядків ведеться вниз) і від’ємним числом (якщо відлік рядків ведеться вгору).
  • «Зсув по колонках». Тут вказується кількість стовпців, які потрібно відрахувати для того, щоб перемістити комірку або групу комірок (вліво або вправо. Ліва верхня клітинка при цьому повинна знаходитися в зазначеному місці. Значення аргументу може бути позитивним (якщо відлік стовпця ведеться вправо) і від’ємним числом (якщо відлік стовпця ведеться вліво).
  • «Висота» – необов’язковий аргумент. Тут вказується кількість рядків у посиланні. Значення повинно бути позитивним числом.
  • «Ширина» – необов’язковий аргумент. Тут вказується число стовпців в посиланні. Значення аргументу повинно бути позитивним числом.
  • Приклад

    Розглянемо приклад використання функції OFFSET, що має наступну формулу: =OFFSET(А4;-2;2).

    21 корисна функція Excel для інтернет-маркетологів

    У цій формулі A4 – посилання на клітинку, від якої обчислюється зсув, С2 – клітинка, на яку посилається осередок А4, а в комірці E2 введена формула з результатом «27» – посилання, що повертається.

    Навіщо інтернет-маркетолога функції Excel?

    Отже, ми розглянули найбільш цікаві та популярні функції Excel. Можуть вони бути корисні інтернет-маркетолога? Безумовно. Вони допоможуть при аналізі даних сторінок сайту, підрахунку кількості символів в тайтлі і description, перетворення тексту, пошуку різних елементів у таблиці. Незважаючи на те, що деякі з представлених функцій дуже прості і зрозумілі, це не зменшує їхньої цінності ні для звичайного користувача, ні для інтернет-маркетолога.